laravel 创建数据表

配置

文件 .env
DB_CONNECTION=mysql
DB_HOST=localhost
DB_PORT=3306
DB_DATABASE=xxx
DB_USERNAME=root
DB_PASSWORD=xxx

文件 config/database.php
'mysql' => [
  'driver' => 'mysql',
  'host' => env('DB_HOST', 'localhost'),
  'port' => env('DB_PORT', '3306'),
  'database' => env('DB_DATABASE', 'xxx'),
  'username' => env('DB_USERNAME', 'root'),
  'password' => env('DB_PASSWORD', 'xxx'),
  'charset' => 'utf8',
  'collation' => 'utf8_unicode_ci',
  'prefix' => '',
  'strict' => false,
  'engine' => null,
],

创建表

# 生成表迁移文件
$ php artisan make:migration create_table_user201812101044
# 其中 create_table_user201812101044 是本次生成的文件名，可随意命名
# 修改生成的 xxx_create_table_user201812101044.php 文件
public function up()
{
    Schema::create('user', function (Blueprint $table) {    // 建立数据表 user
        $table->string('id')->unique();			                // id 列，唯一
        $table->string('password');                         // password
        $table->string('email')->nullable();						    // email
        $table->string('phone')->nullable();						    // phone
        $table->string('ext', 5000)->nullable();            // 扩展字段，其他信息
        $table->timestamps();                               // 自动生成时间戳记录创建更新时间
    });
}
# 创建表
$ php artisan migrate
# 进 mysql 查看表是否他那成功
mysql> desc user;
+------------+---------------+------+-----+---------+-------+
| Field      | Type          | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| id         | varchar(255)  | NO   | PRI | NULL    |       |
| password   | varchar(255)  | NO   |     | NULL    |       |
| email      | varchar(255)  | YES  |     | NULL    |       |
| phone      | varchar(255)  | YES  |     | NULL    |       |
| ext        | varchar(5000) | YES  |     | NULL    |       |
| created_at | timestamp     | YES  |     | NULL    |       |
| updated_at | timestamp     | YES  |     | NULL    |       |
+------------+---------------+------+-----+---------+-------+

说明

1 2	每个迁移文件只能使用一次，如果想修改表字段或新增表字段怎么办？见文档: https://laravel.com/docs/5.7/migrations#modifying-columns

附常用 sql 命令

# 查看所有数据库
$ show databases;
# 创建数据库
$ create database db_xxx;
# 使用数据库
$ use db_xxx;

# 查看所有数据表
$ show tables;
# 创建表
$ create table table_xxx;
# 或
$ create table if not exists `table_xxx`(
    `id` varchar(30) not null, 
    `username` varchar(30), 
    `age` int(3), 
    `gender` int(1), 
    `info` varchar(5000), 
    `ext` varchar(5000),
    primary key (`id`)
) engine=InnoDB default charset=utf8;
# 删除表
$ drop table table_xxx;

# 查看表结构
$ desc table_xxx;
# 或
$ select * from information_schema.columns where table_name="table_xxx";

# 增
$ insert into table_xxx values('W120', 'frostbelt', 0, 0, '{"time_arrive":1544154818,"time_leave":1544154908}','');
# 删
$ delete from table_xxx where id='W120';
# 改
$ update table_xxx set age=18,gender=1 where username='frostbelt';
# 查
$ select * from table_xxx;
# 清空
$ truncate table table_xxx;