Laravel操作mysql数据库(增删改查)例子

Laravel操作mysql数据库包括了对数据库的增加，查询，删除及修改的例子了，同时Laravel多种数据库，包括MySQL、Postgres、SQLite和SQL Server了，我们下面介绍mysql数据库操作例子。

1、连接数据库

Laravel中数据库配置文件为config/database.php，打开该文件，默认内容如下：

<?php

return [
    //默认返回结果集为PHP对象实例
    'fetch' => PDO::FETCH_CLASS,
    //默认数据库连接为mysql，可以在.env文件中修改DB_CONNECTION的值
    'default' => env('DB_CONNECTION', 'mysql'),

    'connections' => [
        //sqlite数据库相关配置
        'sqlite' => [
            'driver' => 'sqlite',
            'database' => storage_path('database.sqlite'),
            'prefix' => '',
        ],
        //mysql数据库相关配置
        'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', 'localhost'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'collation' => 'utf8_unicode_ci',
            'prefix' => '',
            'strict' => false,
        ],
        //Postgres数据库相关配置
        'pgsql' => [
            'driver' => 'pgsql',
            'host' => env('DB_HOST', 'localhost'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
            'schema' => 'public',
        ],
        //SQL Server数据库相关配置
        'sqlsrv' => [
            'driver' => 'sqlsrv',
            'host' => env('DB_HOST', 'localhost'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'charset' => 'utf8',
            'prefix' => '',
        ],

    ],
    //迁移表名称
    'migrations' => 'migrations',
    //Redis数据库相关配置
    'redis' => [

        'cluster' => false,

        'default' => [
            'host' => '127.0.0.1',
            'port' => 6379,
            'database' => 0,
        ],
 
    ],

];
如果要修改数据库配置信息，去修改.env对应值即可。我们实例教程使用的Homestead开发环境默认配置，不做修改。如果你没有使用Homestead，则需要根据本地配置修改相应配置值。

我们在讲Windows上安装Homestead的时候，已经演示过数据库连接测试，以及如何在本地使用Navicat连接Homestead的数据库，这里不再赘述，下面直接进入如何使用数据库进行增删改查。

2、数据库初始化

我们在项目根目录使用Artisan命令运行Laravel自带的迁移生成users表和password_reset表：

对应在数据库中生成三张表：

3、使用DB门面进行增删改查

3.1 插入数据

我们使用DB门面执行原生SQL语句，插入操作使用DB门面的insert方法，代码如下：

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;

use App\Http\Requests;
use App\Http\Controllers\Controller;

use DB;

class TestController extends Controller
{

/**
* Display a listing of the resource.
*
* @return Response
*/
public function index()
{

    DB::insert('insert into users (id, name, email, password) values (?, ?, ? , ? )',
        [1, 'Laravel','laravel@test.com','123']);
    DB::insert('insert into users (id, name, email, password) values (?, ?, ?, ? )',
        [2, 'Academy','academy@test.com','123']);

    }
}
在浏览器中输入http://laravel.app:8000/test，执行成功后在数据库中插入两条记录：

db-insert-data

3.2 查询语句

查询操作使用DB门面的select方法，代码如下：

$user = DB::select('select * from users where id = ?', [1]);
dd($user);
在浏览器地址栏输入http://laravel.app:8000/test，输出内容如下：

可以看到select查询返回的结果是数组。而数组中的每一个元素都是PHP对象。

我们还 可以看到在执行查询的时候使用了参数绑定，以避免SQL注入。除此之外还可以使用命名绑定：

$user = DB::select('select * from users where id = :id', [':id'=>1]);
效果一样。

3.3 更新语句

更新表记录可以使用DB门面的update方法，该方法返回受影响的行数：

$affected = DB::update('update users set name="LaravelAcademy" where name = ?', ['Academy']);
echo $affected;
打印结果为1，如果没有更新任何记录则返回0。

3.4 删除语句

删除表记录使用DB门面的delete方法，和update类似，该方法返回被删除的行数：

$deleted = DB::delete('delete from users');
echo $deleted;
打印结果为2，表数据都被我们删除了。

3.5 通用语句

除了上述这些DML（insert/update/delete）和DQL（select）语句，SQL语句还包括DCL（create/drop等）和DDL（grant等），要运行后者，我们可以调用DB门面的statement方法：

DB::statement('drop table users');
执行完该语句后，数据表users会被删除。

4、监听查询事件
除此之外，我们还可以通过DB门面的listen方法监听查询事件，比如我们在记录日志和调试的时候这会给我们确定问题提供便利，可以在服务提供者的boot方法中注册该监听器，例如我们在AppServiceProvider的boot方法中定义监听器如下：

/**
* 启动所有应用服务
*
* @return void
*/
public function boot()
{
    DB::listen(function($sql, $bindings, $time) {
        echo 'SQL语句执行：'.$sql.'，参数：'.json_encode($bindings).',耗时：'.$time.'ms';
    });
}
这样我们运行如下SQL语句：

DB::insert('insert into users (id, name, email, password) values (?, ?, ? , ? )',
    [3, 'LaravelAcademy','laravel-academy@test.com','123']);
则浏览器会输出如下内容：

SQL语句执行：insert into users (id, name, email, password) values (?, ?, ? , ? )，参数：[3,"LaravelAcademy","laravel-academy@test.com","123"],耗时：1.26ms
5、数据库事务
很多时候，我们需要执行一连串操作，而其中任何一个操作出错则整个流程失败，需要回退重来，这个时候我们就要用到数据库事务。

DB门面提供两种方式支持数据库事务，一种是调用transaction方法然后传入闭包作为参数，我们将需要进行事务操作的逻辑放到闭包函数内：

DB::transaction(function () {
    DB::table('users')->update(['id' => 1]);
    DB::table('posts')->delete();
});
另一种是 beginTransaction、 rollBack和 commit三个方法一起使用从而构建一个完整的事务操作：

DB::beginTransaction();
if($somethingIsFailed){
    DB::rollback();
    return false;
}
DB::commit();
此外，使用DB门面提供的事务还支持查询构建器和Eloquent ORM数据库操作。

本文章来为各位介绍一篇Laravel对数据库增删改查的例子了，这个是基于查询构建器来实现了，具体的如下所示。

获取查询构建器很简单，还是要依赖DB门面，我们使用DB门面的table方法，传入表名，即可获取该表的查询构建器：

$users = DB::table('users');

这样我们就获取到了$users表的查询构建器，实际上，底层返回的是Illuminate\Database\Query\Builder的实例，我们对查询构建器的所有操作都是调用该实例对应类上的方法。下面我们就列举查询构建器的一些常用方法，我们还是沿用上一节创建的$users表做演示说明 。

1、新增数据

使用查询构建器的insert方法即可插入一条/多条数据：

DB::table('users')->insert([
    ['id'=>1,'name'=>'Laravel','email'=>'laravel@test.com','password'=>'123'],
    ['id'=>2,'name'=>'Academy','email'=>'academy@test.com','password'=>'123'],
    ['id'=>3,'name'=>'LaravelAcademy','email'=>'laravel-academy@test.com','password'=>'123']
]);

执行成功后即可在数据表$users中插入三条记录。有时候，我们需要插入记录后获取自增ID，可以使用insertGetId方法：

$insertId = DB::table('users')->insertGetId(
    ['name'=>'Laravel-Academy','email'=>'laravelacademy@test.com','password'=>'456']
);

2、更新数据

更新表记录很简单，使用查询构建器的update方法即可：

$affected = DB::table('users')->where('name','Laravel-Academy')->update(['password'=>'123']);
该方法返回受影响的函数。

3、删除数据

使用delete方法删除表记录，删除方法和更新方法类似，返回被删除的行数：

$deleted = DB::table('users')->where('id', '>', 3)->delete();

如果我们是要删除整个数据表数据，则略去where条件，如果是要清空数据表还要将自增ID置为0，可以使用truncate方法：

DB::table('users')->truncate();

4、基本查询

4.1 获取所有表记录

使用get方法即可获取一张表的所有记录：

$users = DB::table('users')->get();
dd($users);

 

如果是获取指定列的数据，则需要加上select条件：

$users = DB::table('users')->select('name','email')->get();
dd($users);

 

4.2 获取单条记录

获取单条记录需要在查询基础上加上first方法：

$user = DB::table('users')->where('name','Laravel')->first();
dd($user);

 

4.3 分组块获取数据

如果数据库包含多条数据，则一次性获取会极大影响性能，对应地，我们可以调用chunk方法分组块获取数据：

DB::table('users')->chunk(2,function($users){
    foreach($users as $user){
        // if($user->name=='LaravelAcademy')
            // return false;
        echo $user->name.'<br>';
    }
});

这里我们指定每次取两条记录。注释部分的意思是我们可以设定查询退出条件，当达到该条件时，查询退出，不再往下执行。

4.4 获取单列的值

上述方法获取的查询结果都是对象实例/对象实例数组，有时候，我们只是想简单获取单列的值，遍历数组获取指定列的值太麻烦，可以使用lists方法获取列值数组：

$users = DB::table('users')->lists('name');
dd($users);

 

这样我们就可以免去遍历对象数组的麻烦。

4.5 原生表达式

此外，查询构建器还支持原生表达式，我们需要调用DB门面的raw方法来实现：

$users = DB::table('users')->select(DB::raw('name,email'))->where('id','<',3)->get();
dd($users);

如何使用查询构建器实现对数据库的高级查询？比如join、union、添加自定义where条件、排序、分组、分页后面会有介绍。

Laravel处理excel表格也有一个Laravel excel插件了，我们直接使用Laravel excel插件可以进行对excel文件的操作了，下面来看一个Laravel Excel导入导出Excel/CSV 文件例子，具体如

1、简介

Laravel Excel 在 Laravel 5 中集成 PHPOffice 套件中的 PHPExcel，从而方便我们以优雅的、富有表现力的代码实现Excel/CSV文件的导入和导出。

该项目的GitHub地址是：https://github.com/Maatwebsite/Laravel-Excel。

本文我们将在Laravel中使用Laravel Excel简单实现Excel文件的导入和导出。

2、安装&配置

使用Composer安装依赖

首先在Laravel项目根目录下使用Composer安装依赖：

composer require maatwebsite/excel ~2.0.0
安装后的设置

在config/app.php中注册服务提供者到providers数组：

Maatwebsite\Excel\ExcelServiceProvider::class,

同样在config/app.php中注册门面到aliases数组：

'Excel' => Maatwebsite\Excel\Facades\Excel::class,

如果想要对Laravel Excel进行更多的自定义配置，执行如下Artisan命令：

php artisan vendor:publish

执行成功后会在config目录下生成一个配置文件excel.php。

3、导出Excel文件
为了演示Laravel Excel相关功能，我们为本测试创建一个干净的控制器ExcelController.php：

php artisan make:controller ExcelController --plain

然后在routes.php中定义相关路由：

Route::get('excel/export','ExcelController@export');
Route::get('excel/import','ExcelController@import');

接下来我们先在ExcelController.php中定义export方法实现导出功能：

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Http\Requests;
use App\Http\Controllers\Controller;

use Excel;

class ExcelController extends Controller
{
    //Excel文件导出功能 By Laravel学院
    public function export(){
        $cellData = [
            ['学号','姓名','成绩'],
            ['10001','AAAAA','99'],
            ['10002','BBBBB','92'],
            ['10003','CCCCC','95'],
            ['10004','DDDDD','89'],
            ['10005','EEEEE','96'],
        ];
        Excel::create('学生成绩',function($excel) use ($cellData){
            $excel->sheet('score', function($sheet) use ($cellData){
                $sheet->rows($cellData);
            });
        })->export('xls');
    }
}

我们在浏览器中访问http://laravel.app:8000/excel/export，会导出一个名为学生成绩.xls的Excel文件：

使用Laravel Excel导出文件

如果你要导出csv或者xlsx文件，只需将export方法中的参数改成csv或xlsx即可。

如果还要将该Excel文件保存到服务器上，可以使用store方法：

Excel::create('学生成绩',function($excel) use ($cellData){
     $excel->sheet('score', function($sheet) use ($cellData){
         $sheet->rows($cellData);
     });
})->store('xls')->export('xls');
文件默认保存到storage/exports目录下，如果出现文件名中文乱码，将上述代码文件名做如下修改即可：

iconv('UTF-8', 'GBK', '学生成绩')

4、导入Excel文件

我们将刚才保存到服务器上的Excel文件导入进来，导入很简单，使用Excel门面上的load方法即可：

//Excel文件导入功能 By Laravel学院
public function import(){
    $filePath = 'storage/exports/'.iconv('UTF-8', 'GBK', '学生成绩').'.xls';
    Excel::load($filePath, function($reader) {
        $data = $reader->all();
        dd($data);
    });
}

load方法基于项目根路径作为根目录，同样我们对中文进行了转码，否则会提示文件不存在。

在浏览器中访问http://laravel.app:8000/excel/import，页面显示如下：

使用Laravel Excel导入文件

当然，Laravel Excel还有很多其它功能，比如将Blade视图导出为Excel或CSV，以及对导入/导出更加细粒度的控制

php连接sqlserver数据库与连接mysql是有所区别了特别是低版本的的php与mssql数据库，今天我们来看一篇关于ltnmp环境下php连接sqlserver的方法教程，有兴趣的可以进来看看。

1、环境：

centos6.5_x64;

php5.6.11;

Tengine-2.1.0;

FreeTDS-0.95.19;

远程SQLServer：2008;

官网下载：ftp://ftp.freetds.org/pub/freetds/stable/freetds-patched.tar.gz（好像下载不了,技安在下载的时候始终只能下载到10%，然后就停住了，就停住了，就停住了…）。

这是技安费好大力气才下载的，分享出来下载：

0.95.19： freetds-patched.tar.gz

0.95.65： freetds-patched-0.95.65.tar

2、安装freetds。

# tar -zxvf freetds-patched.tar.gz
# cd freetds-0.95.19/
# ./configure --prefix=/usr/local/freetds --with-tdsver=7.3 --enable-msdblib
# make
# sudo make install
# sudo vim /etc/ld.so.conf.d/freetds.conf
  内容为：/usr/local/freetds/lib/
# sudo ldconfig
注：安装的TDS Version的详见官网文档：http://www.freetds.org/userguide/choosingtdsprotocol.htm

下面是截图：

测试是否安装成功：

# /usr/local/freetds/bin/tsql -C

测试连接：

# /usr/local/freetds/bin/tsql -H 服务器IP/域名 -p 端口 -U 用户名 -P 密码
出现下图所示说明已经连接成功：

关于freetds/etc/freetds.conf配置项
很多其他帖子写了需要配置/usr/local/freetds/etc/freetds.conf，其实这个不需要配置。如果配置也可以，配置了PHP就可以调用这个配置项，否则需要PHP代码里指定数据库服务器信息即可。
另外需要注意的是/usr/local/freetds/etc/下的freetds.conf不同于前面/usr/local/freetds/lib/那个freetds.conf。
如果配置了这里，那么PHP页面就可以使用这里的配置，不然PHP页面指定一样可以。

默认是这样的：

# $Id: freetds.conf,v 1.12 2007-12-25 06:02:36 jklowden Exp $
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".
# Global settings are overridden by those in a database
# server specific section
[global]
 # TDS protocol version
; tds version = 4.2
# Whether to write a TDSDUMP file for diagnostic purposes
 # (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff
# Command and connection timeouts
; timeout = 10
; connect timeout = 10
# If you get out-of-memory errors, it may mean that your client
 # is trying to allocate a huge buffer for a TEXT field.
 # Try setting 'text size' to a more reasonable limit
 text size = 64512
# A typical Sybase server
[egServer50]
 host = symachine.domain.com
 port = 5000
 tds version = 5.0
# A typical Microsoft server
[egServer70]
 host = ntmachine.domain.com
 port = 1433
 tds version = 7.0
如果要添加配置可以在最后添加类似如下代码：

[sqlServer2008]
    host = 服务器ip或者域名
    port = 端口
    tds version = 7.3
3、php添加mssql扩展和pdo的pdo_dblib扩展

# 进入php源码目录
# cd php-5.6.11/
# cd ext/mssql/
# phpize
# ./configure --with-php-config=/usr/local/php/bin/php-config --with-mssql=/usr/local/freetds
# make
# sudo make install

# 修改php.ini文件使之支持mssql
# sudo vim /usr/local/php/etc/php.ini
# 增加扩展
# extension=mssql.so
添加pdo_dblib扩展

# cd ext/pdo_dblib/
# phpize
# ./configure --with-php-config=/usr/local/php/bin/php-config --with-pdo-dblib=/usr/local/freetds/
# make
# sudo make install
# 修改php.ini文件使之支持pdo_dblib
# sudo vim /usr/local/php/etc/php.ini
# 增加扩展
# extension=pdo_dblib.so

# 重启php-fpm
# sudo /etc/init.d/php-fpm restart
4、测试php调用SQLServer数据库信息

<?php
header("Content-type: text/html; charset=utf-8");

$msdb=mssql_connect("数据库地址:端口","账号","密码");
if (!$msdb) {
    echo "connect sqlserver error";
    exit;
}

mssql_select_db("数据库名称",$msdb);
$result = mssql_query("SELECT top 5 * FROM 表名称", $msdb);
while($row = mssql_fetch_array($result)) {
    var_dump($row);
}

mssql_free_result($result);
?>
PDO版本测试：

<?php
header("Content-type: text/html; charset=utf-8");
   try {
        $hostname = "数据库IP";
        $port = 端口;
        $dbname = "数据库名";
        $username = "账号";
        $pw = "密码";
        $dbh = new PDO ("dblib:host=$hostname:$port;dbname=$dbname","$username","$pw");
} catch (PDOException $e) {
    echo "Failed to get DB handle: " . $e->getMessage() . "\n";
    exit;
}
 
$stmt = $dbh->prepare("SELECT top 5 * FROM 数据表名");
$stmt->execute();
while ($row = $stmt->fetch()) {
    var_dump($row);
} 
?>

之前说过php跟js的最大的不同点就是能够直接与数据库打交道，存储信息，读取信息，与用户交互。这里总结了php连接数据库的比较全面和靠谱的三个步骤，简称三步曲。

主要包括：

1、php创建数据库与表;

2、php往数据库插入内容;

3、php从数据库里面读取内容到前台显示出来;

首先是创建数据库和表：

<?php
$servername = "localhost";//数据库主机
$username = "root";//数据库用户名
$password = "root";//数据库密码
$dbname = "lyt";//将要创建的数据库名

// 创建连接
$conn = new mysqli($servername, $username, $password);
// 检测连接
if ($conn->connect_error) {die("连接出错: " . $conn->connect_error);}

// 开始创建数据库
$sql = "CREATE DATABASE lyt default character set utf8 collate utf8_general_ci";//红色为设置数据库编码格式，网上很多教程都漏了这一步，导致各种乱码现象
if ($conn->query($sql) === TRUE) {echo "数据库创建成功<br/>";} else {echo "数据库创建失败: " .$conn->error;}
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

// 创建表和字段
$sql = "CREATE TABLE MyGuests(//红色为表名，下面的红色部分为表里面的字段，同样也要设置编码，VARCHAR是字段存储数据的类型
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
product VARCHAR(30) CHARACTER SET utf8 COLLATE utf8_general_ci,
guestname VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci,
phone VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci,
address VARCHAR(50) CHARACTER SET utf8 COLLATE utf8_general_ci,
message VARCHAR(550) CHARACTER SET utf8 COLLATE utf8_general_ci,
reg_date TIMESTAMP
)default character set utf8";
$conn->exec($sql);echo "数据表创建成功";
}
catch(PDOException $e){echo $sql . "<br>" . $e->getMessage();}
?>

把这段代码塞到php文件里面，通过访问这个php页面就可以创建一个lyt的数据库，数据库里面有MyGuests表，表里面有id，product，guestname，phone，address，message，reg_date字段，但是里面还是空空如也，我们要往里面插入数据，比如说把客户填写的一个表单数据提交到数据库，就要用以下php代码了：

<?php
$servername = "localhost";//数据库主机
$username = "root";//数据库用户名
$password = "root";//数据库密码
$dbname = "lyt";//将要创建的数据库名
// 创建连接
$conn = mysqli_connect($servername, $username, $password, $dbname);
if (!$conn) {
die("数据库连接失败: " . mysqli_connect_error());
}
mysqli_query($conn,"SET NAMES UTF8");//红色为设置数据库编码格式，网上很多教程都漏了这一步，导致各种乱码以及错误现象
$sql = "INSERT INTO MyGuests (product, guestname, phone, address, message)
VALUES ('".$_POST['pro']."','".$_POST['gname']."', '".$_POST['phone']."', '".$_POST['addr']."', '".$_POST['mess']."')";//蓝色部分分别为表单input的name值，注意表单类型必须为post。

if (mysqli_query($conn, $sql)) {
echo "提交成功";
} else {
echo "错误: " . $sql . "<br>" . mysqli_error($conn);
}
?>

把这段代码扔到一个form.php文件里面，作为表单的处理文件，表单上通过引入这个文件，如：

<form name="form" method="post" action="form.php">

这样提交表单的时候就会把表单里面填写的数据插入到数据库。除了表单你也可以手动指定插入一些数据，上面的那部分代码写成死的就行了，如：

VALUES ('哈哈','你好', '我很好', '你呢?', '我不好!')";通过这一步，数据库总算有内容了。可是光有内容不行啊，我该怎么看到这些数据呢?我总不会去数据库里面看吧，还是要把数据库内容在前端表现出来才方便些，那么就要php从数据库里面读取数据了，代码如下：

$conn = mysql_connect("localhost","root","root");//连接数据库，请填写你自己的用户名密码
mysql_select_db("lyt");//选择MYSQL数据库
mysql_query("set names utf8");//设置为utf8编码
$result = mysql_query("select * from myguests",$conn); //执行SQL查询指令,myguests为表名
echo "<table border=1 style='margin:0 auto;'><tr>";
while($field = mysql_fetch_field($result)){//使用while输出表头
echo "<td> ".$field->name." </td>";
}
echo"</tr>";
while($rows = mysql_fetch_row($result)){//使用while遍历所有记录，并显示在表格的tr中
echo "<tr>";
for($i = 0; $i < count($rows); $i++)
echo "<td> ".$rows[$i]."</td>";
}
echo "</tr></table>";

把这段代码扔到另一个php文件里面，通过访问这个php文件可以把指定的数据库，数据库表里面的内容以表格形式呈现出来。

其实这三段代码都用到了同一小块代码：

$servername = "localhost";//数据库主机
$username = "root";//数据库用户名
$password = "root";//数据库密码
$dbname = "lyt";//将要创建的数据库名

可以把这小段代码另存为另一php文件，通过include 'data.php';引入，这样方便修改。