表操作
MySQL中的存储引擎
MySQL中的数据用各种不同的技术（方式）存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。
这些不同的技术以及配套的相关功能（数据的存储方式）在MySQL中被称作存储引擎(也称作表类型)。 可通过
show engines查看存储引擎。MySQL中常用存储引擎
innodb
是MySQL 5.6以上版本默认的存储方式，将数据分为两个文件存储，一个是表结构 ，一个是树形结构+数据。具有以下特点：
row-level locking 行级锁
table-level locking 表级锁
foreign keys 外键约束
树tree - 加速查询（树形结构（数据+树）+表结构）
transaction 事务，是保证数据的安全，数据的完整性而设置的概念
并发 修改 删除操作的效率比较高
持久化存储
myisam
MySQL 5.5以下版本的默认存储方式，将数据分为三个文件存储，一个是表结构 ，一个是树形结构，另一个是数据。具有以下特征：
table-level locking 表级锁
读 插入操作的效率高一些
持久化存储
树tree - 加速查询（树形结构+数据+表结构）
create table myisam_t (id int,name char(18)) engine=myisammemory
基于hash，将所有的数据保存在内存中，可提供极快的访问。缺点是对表的大小有限制，一旦数据库关闭数据就会丢失。
create table memory_t (id int,name char(18) engine=memory)
存储引擎相关sql语句
查看当前的默认存储引擎：
show variables like "default_storage_engine";查询当前数据库支持的存储引擎：
show engines \G;建表时的指令：
create table ai(id bigint(12),name varchar(200)) engine=myisam; create table country(id int(4),cname varchar(50)) engine=innodb;修改一个已经存在的表的存储引擎：
alter table ai engine = innodb;也可以在配置文件中指定：
# my.ini文件 [mysqld] default-storage-engine=INNODBMySQL的工作流程
表
介绍
表就相当于文件，表中一条记录就相当于文件的一行内容。
id,name,age,sex,phone,job称为字段，其余的，一行内容称为一条记录
创建表
# 语法 create table 表名( 字段名1 类型[(宽度) 约束条件], 字段名1 类型[(宽度) 约束条件], 字段名1 类型[(宽度) 约束条件], ); #注意： 1. 在同一张表中，字段名是不能相同 2. 宽度和约束条件可选 3. 字段名和类型是必须的查看表结构
使用
desc 表名:mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | age | int(3) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+使用
show create table 表名\G;:mysql> show create table staff_info\G; *************************** 1. row *************************** Table: staff_info Create Table: CREATE TABLE `staff_info` ( `id` int(11) DEFAULT NULL, `name` varchar(50) DEFAULT NULL, `age` int(3) DEFAULT NULL, `sex` enum('male','female') DEFAULT NULL, `phone` bigint(11) DEFAULT NULL, `job` varchar(11) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.01 sec)数据类型
数值类型
MySQL支持所有标准的sql数值数据类型。
类型 大小 范围（有符号） 范围（无符号）unsigned约束 用途 TINYINT 1 字节 (-128，127) (0，255) 小整数值 SMALLINT 2 字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3 字节 (-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4 字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8 字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4 字节float(255,30) (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 字节double(255,30) (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2double(65,30) 依赖于M和D的值 依赖于M和D的值 小数值 其中最常用的是tinyint、int、float。
# 创建一个表，默认宽度的tinyint、默认宽度的int、无符号的tingyint、无符号的int mysql> create table int_t1( -> ti tinyint, -> i int, -> tiun tinyint unsigned, -> iun int unsigned -> ); Query OK, 0 rows affected (0.70 sec) # 插入超过长度的数值，并不会报错，写入的是能写入的最大值 mysql> insert into int_t1 values(123456789,123456789,123456789,123456789); Query OK, 1 row affected, 2 warnings (0.48 sec) mysql> select * from int_t1; +------+-----------+------+-----------+ | ti | i | tiun | iun | +------+-----------+------+-----------+ | 127 | 123456789 | 255 | 123456789 | +------+-----------+------+-----------+ 1 row in set (0.00 sec) # 插入复数也不会报错，无符号的项并不能被写入 mysql> insert into int_t1 values(-123456789,-123456789,-123456789,-123456789); Query OK, 1 row affected, 3 warnings (0.03 sec) mysql> select * from int_t1; +------+------------+------+-----------+ | ti | i | tiun | iun | +------+------------+------+-----------+ | 127 | 123456789 | 255 | 123456789 | | -128 | -123456789 | 0 | 0 | +------+------------+------+-----------+ 2 rows in set (0.00 sec)# 创建一个默认长度的float和double以及限制长度的float和double mysql> create table fd_t1( -> f float, -> d double, -> f2 float(5,2), -> d2 double(5,2) -> ); Query OK, 0 rows affected (0.83 sec) # 插入超过限制长度的数，发现限制长度的float和double都会对余下的数进行四舍五入 mysql> insert into fd_t1 values(123.45678,123.45678,123.45678,123.45678); Query OK, 1 row affected (0.48 sec) mysql> select * from fd_t1; +---------+-------------------+--------+--------+ | f | d | f2 | d2 | +---------+-------------------+--------+--------+ | 123.457 | 123.45678 | 123.46 | 123.46 | +---------+-------------------+--------+--------+ 2 rows in set (0.00 sec)# 创建一个默认长度的decimal和限制小数位为20位的decimal mysql> create table dec_t1( -> dec1 decimal, -> dec2 decimal(30,20) -> ); Query OK, 0 rows affected (0.78 sec) # 插入数字发现decimal默认小数位为零，限制小数位为20后插入数字若不足则会进行补全 mysql> insert into dec_t1 values(123.12345678912345,123.12345678912345); Query OK, 1 row affected, 1 warning (0.48 sec) mysql> select * from dec_t1; +------+--------------------------+ | dec1 | dec2 | +------+--------------------------+ | 123 | 123.12345678912345000000 | +------+--------------------------+ 1 row in set (0.00 sec)修改字段类型：
alter table 表名 modify 条件 新类型日期和时间类型
MySQL中表示日期和时间的类型有datetime、date、timestamp、time和year。
每个时间类型有一个有效范围和一个“零”值，当指定不合法的MySQL不能表示的值时使用“零”值。
类型 大小 (字节) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 年月日 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时分秒 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 年月日时分秒 TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值，时间戳 # 创建一个含有date(日期)、time（时间）、datetime（年月日时间）的表 mysql> create table t4(d date,t time,dt datetime); Query OK, 0 rows affected (0.44 sec) mysql> desc t4; +-------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+----------+------+-----+---------+-------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | dt | datetime | YES | | NULL | | +-------+----------+------+-----+---------+-------+ 3 rows in set (0.01 sec) # 通过now()函数插入当前时间 mysql> insert into t4 values(now(),now(),now()); Query OK, 1 row affected, 1 warning (0.16 sec) mysql> select * from t4; +------------+----------+---------------------+ | d | t | dt | +------------+----------+---------------------+ | 2019-07-30 | 16:37:58 | 2019-07-30 16:37:58 | +------------+----------+---------------------+ 1 row in set (0.00 sec)timestamp：
# 创建一个含有timestamp（时间戳）的表 mysql> create table t2 (ts timestamp); Query OK, 0 rows affected (0.44 sec) # 可以看到默认值是current_timestamp mysql> desc t2; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ 1 row in set (0.02 sec) # 当插入值为空时，会自动插入当前时间的时间 mysql> insert into t2 values(null); Query OK, 1 row affected (0.17 sec) mysql> select * from t2; +---------------------+ | ts | +---------------------+ | 2019-07-30 16:46:57 | +---------------------+ 1 row in set (0.00 sec) # 手动添加一列，默认值为'0000-00-00 00:00:00' mysql> alter table t2 add ts1 timestamp; Query OK, 0 rows affected (0.89 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2 \G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `ts1` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> select * from t2; +---------------------+---------------------+ | ts | ts1 | +---------------------+---------------------+ | 2019-07-30 16:46:57 | 0000-00-00 00:00:00 | +---------------------+---------------------+ 1 row in set (0.00 sec) # 手动修改新的列默认值为当前时间 mysql> alter table t2 modify ts1 timestamp default current_timestamp; Query OK, 0 rows affected (0.12 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table t2 \G; *************************** 1. row *************************** Table: t2 Create Table: CREATE TABLE `t2` ( `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `ts1` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) ERROR: No query specified mysql> insert into t2 values(null,null); Query OK, 1 row affected (0.14 sec) mysql> select * from t2; +---------------------+---------------------+ | ts | ts1 | +---------------------+---------------------+ | 2019-07-30 16:46:57 | 0000-00-00 00:00:00 | | 2019-07-30 17:25:56 | 2019-07-30 17:25:56 | +---------------------+---------------------+ 2 rows in set (0.00 sec) # timestamp时间的下限是19700101080001；上限是20380119111407 mysql> insert into t2 values(19700101080001,'2038-01-19 11:14:07'); Query OK, 1 row affected (0.12 sec) mysql> select * from t2; +---------------------+---------------------+ | ts | ts1 | +---------------------+---------------------+ | 2019-07-30 16:46:57 | 0000-00-00 00:00:00 | | 2019-07-30 17:25:56 | 2019-07-30 17:25:56 | | 1970-01-01 08:00:01 | 2038-01-19 11:14:07 | +---------------------+---------------------+ 3 rows in set (0.02 sec) mysql> insert into t2 values(null,'2038-01-19 11:14:08'); Query OK, 1 row affected, 1 warning (0.03 sec) mysql> select * from t2; +---------------------+---------------------+ | ts | ts1 | +---------------------+---------------------+ | 2019-07-30 16:46:57 | 0000-00-00 00:00:00 | | 2019-07-30 17:25:56 | 2019-07-30 17:25:56 | | 1970-01-01 08:00:01 | 2038-01-19 11:14:07 | | 2019-07-30 17:32:30 | 0000-00-00 00:00:00 | +---------------------+---------------------+ 4 rows in set (0.00 sec)可以使datetime具有timestamp含有默认值且能自动更新的特点：
mysql> create table t13(d date,t datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP); Query OK, 0 rows affected (0.35 sec) mysql> insert into t13(d) values (20190416); Query OK, 1 row affected (0.13 sec) mysql> select * from t13; +------------+---------------------+ | d | t | +------------+---------------------+ | 2019-04-16 | 2019-07-30 20:37:31 | +------------+---------------------+ 1 row in set (0.00 sec)字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
常用的有char、varchar。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
char列的长度固定为创建表时声明的长度,范围(0-255);而varchar的值是可变长字符串范围(0-65535)。
char：定长，速度快，占用空间大大
varchar：变长，速度慢，占用空间小
类型 大小 用途 CHAR 0-255字节 定长字符串 VARCHAR 0-65535 字节 变长字符串 TINYBLOB 0-255字节 不超过 255 个字符的二进制字符串 TINYTEXT 0-255字节 短文本字符串 BLOB 0-65 535字节 二进制形式的长文本数据 TEXT 0-65 535字节 长文本数据 MEDIUMBLOB 0-16 777 215字节 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215字节 中等长度文本数据 LONGBLOB 0-4 294 967 295字节 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295字节 极大文本数据 # 创建一个表，含有限制长度为4的varchar和char mysql> create table t5 (v varchar(4),c char(4)); Query OK, 0 rows affected (0.37 sec) # char数据类型会去掉末尾空格 mysql> insert into t5 values('ab ','ab '); Query OK, 1 row affected (0.13 sec) mysql> select * from t5; +------+------+ | v | c | +------+------+ | ab | ab | +------+------+ 1 row in set (0.00 sec) # 对查询结果计算的长度 mysql> select length(v),length(c) from t5; +-----------+-----------+ | length(v) | length(c) | +-----------+-----------+ | 4 | 2 | +-----------+-----------+ 1 row in set (0.11 sec) # 拼上一个加号会更清楚 mysql> select concat(v,'+'),concat(c,'+') from t5; +---------------+---------------+ | concat(v,'+') | concat(c,'+') | +---------------+---------------+ | ab + | ab+ | +---------------+---------------+ 1 row in set (0.10 sec) # 当存储的长度超出定义的长度，会截断 mysql> insert into t5 values('abcde','abcde'); Query OK, 1 row affected, 2 warnings (0.13 sec) mysql> select * from t5; +------+------+ | v | c | +------+------+ | ab | ab | | abcd | abcd | +------+------+ 2 rows in set (0.00 sec)当存储类似手机号码/身份证号码以及用户名/密码这样，长度固定或有一定范围弹性的字符时使用char；当存储类似评论等范围较大时使用varchar。
enum和set类型
enum即枚举类型，它的取值范围需要在创建表时通过枚举方式显示。enum只允许从值集合中选取单个值，而不能一次取多个值。
set类型可以允许值集合中任意选择1或多个元素进行组合。对超出范围的内容不允许写入，重复的值将进行自动去重。
类型 大小 用途 ENUM 对1-255个成员的枚举需要1个字节存储;对于255-65535个成员，需要2个字节存储;最多允许65535个成员。 单选：选择性别 SET 1-8个成员的集合，占1个字节9-16个成员的集合，占2个字节17-24个成员的集合，占3个字节25-32个成员的集合，占4个字节33-64个成员的集合，占8个字节 多选：兴趣爱好 # enum 单选 mysql> create table t6 (name char(20),gender enum('female','male')); Query OK, 0 rows affected (0.32 sec) mysql> insert into t6 values('皮皮寒','male'); Query OK, 1 row affected (0.12 sec) mysql> select * from t6; +-----------+--------+ | name | gender | +-----------+--------+ | 皮皮寒 | male | +-----------+--------+ 1 row in set (0.00 sec) # 同时插入'male,female'，不会写入，不属于'male,female'的也不会写入 mysql> insert into t6 values('皮皮寒','male,female'); Query OK, 1 row affected, 1 warning (0.13 sec) mysql> select * from t6; +-----------+--------+ | name | gender | +-----------+--------+ | 皮皮寒 | male | | 皮皮寒 | | +-----------+--------+ 2 rows in set (0.00 sec)# set 多选 mysql> create table t7 (name char(20),hobby set('唱','跳','rap','篮球')); Query OK, 0 rows affected (0.35 sec) mysql> insert into t7 values('皮皮寒','唱,跳'); Query OK, 1 row affected (0.14 sec) mysql> select * from t7; +-----------+---------+ | name | hobby | +-----------+---------+ | 皮皮寒 | 唱,跳 | +-----------+---------+ 1 row in set (0.00 sec) # 自带去重功能，不能选择不属于set中的项 mysql> insert into t7 values('皮皮寒','唱,跳,唱'); Query OK, 1 row affected (0.13 sec) mysql> select * from t7; +-----------+---------+ | name | hobby | +-----------+---------+ | 皮皮寒 | 唱,跳 | | 皮皮寒 | 唱,跳 | +-----------+---------+ 2 rows in set (0.00 sec)
表的完整性约束
为防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行检测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。
约束条件主要分为以下几类：
not null：非空约束，指定某列不能为空；
unique：唯一约束，指定某列或者几列组合不能重复
primary key：主键，指定该列的值可以唯一地标识该列记录
foreign key：外键，指定该列记录从属于主表中的一条记录，主要用于参照完整性
not null
not null 不可空
null 可空
# 创建一个id不能为空的表 mysql> use day38 Database changed mysql> create table t8 (id int not null); Query OK, 0 rows affected (0.30 sec) mysql> desc t8; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec) # 不能向id插入空元素 mysql> insert into t8 values(null); ERROR 1048 (23000): Column 'id' cannot be null mysql> insert into t8 values(1); Query OK, 1 row affected (0.15 sec)default：默认值，创建列时可以指定默认值，当插入数据时如果为主动设置，则自动添加默认值。
# 创建一个含有非空id和非空默认值为222的id2的表 mysql> create table t9 (id int not null,id2 int not null default 222); Query OK, 0 rows affected (0.38 sec) mysql> desc t9; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | id2 | int(11) | NO | | 222 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.02 sec) # 当更改id内容时，id2自动写入了默认值222 mysql> insert into t9 (id) values (111); Query OK, 1 row affected (0.14 sec) mysql> select * from t9; +-----+-----+ | id | id2 | +-----+-----+ | 111 | 222 | +-----+-----+ 1 row in set (0.00 sec) # 向id和id2填充数字，id2的默认值会被覆盖 mysql> insert into t9 values(112,113); Query OK, 1 row affected (0.12 sec) mysql> select * from t9; +-----+-----+ | id | id2 | +-----+-----+ | 111 | 222 | | 112 | 113 | +-----+-----+ 2 rows in set (0.00 sec)设置严格模式： 不支持对not null字段插入null值 不支持对自增长字段插入”值 不支持text字段有默认值 直接在mysql中生效(重启失效): mysql>set sql_mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"; 配置文件添加(永久生效)： sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"unique
唯一约束，指定某列或者几列组合不能重复。
mysql> create table t10(id int,name varchar(20) unique,comment varchar(100)); # mysql> create table t10(id int,name varchar(20),comment varchar(100),unique(name));也可以写成这样 Query OK, 0 rows affected (0.44 sec) mysql> insert into t10 values(1,'IT','技术'); Query OK, 1 row affected (0.03 sec) # 当插入相同的name时会报错 mysql> insert into t10 values(1,'IT','技术'); ERROR 1062 (23000): Duplicate entry 'IT' for key 'name'非空与unique结合：
mysql> create table t11 (id int not null unique); Query OK, 0 rows affected (0.35 sec) mysql> desc t11; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ 1 row in set (0.01 sec)如果一个表中没有设置primary key 主键，那么第一个设置非空+唯一的字段会被设置成主键；一张表中只能有一个主键。
# 创建一个表，其中host与port都是非空+唯一 mysql> create table service( -> id int primary key auto_increment,#设置主键，作用=非空+唯一 -> name varchar(20), -> host varchar(15) not null, -> port int not null, -> unique(host,port) # 联合唯一，只有当这两项同时重复才会报错 -> ); Query OK, 0 rows affected (0.39 sec) mysql> insert into service values -> (1,'nginx','192.168.0.10',80),# 由于联合唯一，port单独重复也可以写入 -> (2,'haproxy','192.168.0.20',80), -> (3,'mysql','192.168.0.30',3306); Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from service; +----+---------+--------------+------+ | id | name | host | port | +----+---------+--------------+------+ | 1 | nginx | 192.168.0.10 | 80 | | 2 | haproxy | 192.168.0.20 | 80 | | 3 | mysql | 192.168.0.30 | 3306 | +----+---------+--------------+------+ 3 rows in set (0.00 sec) # 当host与port同时重复时就会报错 mysql> insert into service(name,host,port) values('nginx','192.168.0.10',80); ERROR 1062 (23000): Duplicate entry '192.168.0.10-80' for key 'host'primary key
主键是为了保证表中的每一条数据的该字段都是表格中的唯一值。即它是用来独一无二的确认一个表格中的每一行数据。
主键可以包含一个字段或多个字段。当主键包含多个栏位时，称为组合键，也可以叫联合主键。主键可以在建置新表格时设定 (运用 create table 语句)，或是以改变现有的表格架构方式设定 (运用 alter table)。
主键必须唯一，主键值非空；可以是单一字段，也可以是多字段。
单字段主键
# 方法一：not null unique mysql> create table t1(id int not null unique, -> name varchar(20) not null unique); Query OK, 0 rows affected (0.78 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | NO | UNI | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.13 sec) # 方法二：在某个字段后加primary key mysql> create table t1(id int primary key,name varchar(20)); Query OK, 0 rows affected (0.73 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # 方法三：在所有字段后单独定义primary key mysql> create table t1(id int ,name varchar(20),primary key(id)); Query OK, 0 rows affected (0.65 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) # 方法四：给已经建成的表添加主键约束 mysql> create table t1(id int,name varchar(20)); Query OK, 0 rows affected (0.60 sec) mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> alter table t1 modify id int primary key; Query OK, 0 rows affected (1.29 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.01 sec)多字段主键
多个字段做主键时，表示多个字段组合在一起的内容必须唯一。
# 当多个字段联合主键时,只有当两个字段都重复时才会报错 mysql> create table t2( -> ip varchar(15), -> port char(5), -> service_name varchar(10) not null, -> primary key(ip,port) -> ); Query OK, 0 rows affected (0.84 sec) mysql> desc t2; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | | | | port | char(5) | NO | PRI | | | | service_name | varchar(10) | NO | | NULL | | +--------------+-------------+------+-----+---------+-------+ 3 rows in set (0.01 sec) mysql> insert into t2 values -> ('192.168.45.10','3306','mysql'), -> ('192.168.45.11','3306','weixin'); Query OK, 2 rows affected (0.42 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into t2 values -> ('192.168.45.10','3306','sougou'); ERROR 1062 (23000): Duplicate entry '192.168.45.10-3306' for key 'PRIMARY'auto_increment
约束字段为自动增长，被约束的字段必须同时被key约束
- 只能操作数字
- 自带非空属性
- 只能对unique字段进行设置
- 不受删除影响（使用
truncate table t1删除，会直接从零开始）
# 不指定id，则自动增长（也可以指定id） mysql> create table t3( -> id int primary key auto_increment, -> name varchar(20), -> sex enum('男','女') default '男' -> ); Query OK, 0 rows affected (0.73 sec) mysql> desc t3; +-------+-------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('男','女') | YES | | 男 | | +-------+-------------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into t3(name) values -> ('皮皮寒'), -> ('铁憨憨'); Query OK, 2 rows affected (0.47 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> select * from t3; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 1 | 皮皮寒 | 男 | | 2 | 铁憨憨 | 男 | +----+-----------+------+ 2 rows in set (0.00 sec) # 使用delete删除后，在插入值，该字段还会延续之前的序号增长 mysql> delete from t3; Query OK, 2 rows affected (0.57 sec) mysql> insert into t3(name) values('碎小梦'); Query OK, 1 row affected (0.39 sec) mysql> select * from t3; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 3 | 碎小梦 | 男 | +----+-----------+------+ 1 row in set (0.00 sec) # 使用truncate可直接清空，从零开始 mysql> truncate t3; Query OK, 0 rows affected (0.74 sec) mysql> insert into t3(name) values('皮皮寒'); Query OK, 1 row affected (0.39 sec) mysql> select * from t3; +----+-----------+------+ | id | name | sex | +----+-----------+------+ | 1 | 皮皮寒 | 男 | +----+-----------+------+ 1 row in set (0.00 sec)
foreign key
外键约束：对应外表中的字段至少是unique的，推荐使用主键作为关联字段
同过设置外键可以将两个或多个表关联起来
# 创建父表 mysql> create table department( -> id int primary key, -> name varchar(20) not null); Query OK, 0 rows affected (0.58 sec) # 创建子表，关联父表 mysql> create table employee1( -> id int primary key, -> name varchar(20) not null, -> dpt_id int, -> foreign key(dpt_id) -> references department(id) # 关联父表主键id -> on delete cascade # 级联删除 -> on update cascade); # 级联更新 Query OK, 0 rows affected (0.80 sec) # 先往父表中插入记录 mysql> insert into department values(1,'教质部'),(2,'技术部'),(3,'人力资源部'); Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 # 再往子表中插入记录 mysql> insert into employee1 values -> (1,'dama',1), -> (2,'debang',1), -> (3,'jiawen',2), -> (4,'yasuo',3), -> (5,'lakesi',2), -> (6,'yi',3); Query OK, 6 rows affected (0.54 sec) Records: 6 Duplicates: 0 Warnings: 0 # 删除父表的记录，子表跟着删除 mysql> delete from department where id=2; Query OK, 1 row affected (0.58 sec) mysql> select * from employee1; +----+--------+--------+ | id | name | dpt_id | +----+--------+--------+ | 1 | dama | 1 | | 2 | debang | 1 | | 4 | yasuo | 3 | | 6 | yi | 3 | +----+--------+--------+ 4 rows in set (0.00 sec) # 更新父表，子表中对应记录跟着改 mysql> update department set id=2 where id=3; Query OK, 1 row affected (0.48 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from employee1; +----+--------+--------+ | id | name | dpt_id | +----+--------+--------+ | 1 | dama | 1 | | 2 | debang | 1 | | 4 | yasuo | 2 | | 6 | yi | 2 | +----+--------+--------+ 4 rows in set (0.00 sec)
修改表结构
# 语法： # 1. 修改表名 alter table 表名 rename 新表名; # 2. 增加字段 alter table 表名 add 字段名 数据类型（完整性约束条件）; # 3. 删除字段 alter table 表名 drop 字段名; # 4. 修改字段 alter table 表名 change 旧字段名 新字段名 旧数据类型（完整性约束条件）; change 旧字段名 新字段名 新数据类型（完整性约束条件）; modify 旧字段名 数据类型 （完整性约束条件）; # 5. 修改字段排列顺序/在增加的时候指定字段位置 alter table 表名 add 字段名 数据类型（完整性约束条件） first；# 添加在第一列 add 字段名 数据类型（完整性约束条件） after 字段名; # 添加在此字段之后 change与modify同理alter操作非空和唯一：
create table t(id int unique,name char(10) not null); #去掉null约束 alter table t modify name char(10) null; # 添加null约束 alter table t modify name char(10) not null; # 去掉unique约束 alter table t drop index id; # 添加unique约束 alter table t modify id int unique; alter处理null和unique约束alter操作主键：
# 先删除主键 alter table table_test drop primary key; # 然后再增加主键 alter table table_test add primary key(id); # 注:在增加主键之前,必须先把反复的id删除掉。为表添加外键：
# 为book表添加外键 alter table book add constraint fk_id foreign key(press_id) references press(id); # 删除外键 alter table book drop foreign key fk_id;表与表的关系
一对多（或多对一）：例如校区表与班级表
校区表 一个校区可以有多个班级
班级表 一个班级不可能对应多个校区
校区表：校区id 校区名称 校区城市 校区地址
班级表：班级id 班级名称 开班日期 班主任 校区id
多（foreign key）关联一张表：班级表创建foreign key关联校区表的校区id字段
mysql> create table school(id int primary key,sname varchar(20)); Query OK, 0 rows affected (0.92 sec) mysql> desc school; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | sname | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.02 sec) mysql> create table class(id int primary key,cname varchar(20),start char(8), -> sch_id int not null, -> foreign key(sch_id) references school(id) on delete cascade on update cascade); Query OK, 0 rows affected (0.83 sec) mysql> desc class; +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | | cname | varchar(20) | YES | | NULL | | | start | char(8) | YES | | NULL | | | sch_id | int(11) | NO | MUL | NULL | | +--------+-------------+------+-----+---------+-------+ 4 rows in set (0.02 sec) mysql> insert into school values(1,'北京校区'),(2,'深圳校区'),(3,'上海校区'); Query OK, 3 rows affected (0.47 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into class values(1,'三年一班','20180701',1),(2,'三年二班','20180701',1),(3,'四年一班','20180701',2),(4,'四年二班','20180701',3); Query OK, 4 rows affected (0.55 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from school; +----+--------------+ | id | sname | +----+--------------+ | 1 | 北京校区 | | 2 | 深圳校区 | | 3 | 上海校区 | +----+--------------+ 3 rows in set (0.00 sec) mysql> select * from class; +----+--------------+----------+--------+ | id | cname | start | sch_id | +----+--------------+----------+--------+ | 1 | 三年一班 | 20180701 | 1 | | 2 | 三年二班 | 20180701 | 1 | | 3 | 四年一班 | 20180701 | 2 | | 4 | 四年二班 | 20180701 | 3 | +----+--------------+----------+--------+ 4 rows in set (0.00 sec)多对多：例如作者和书
一个作者可以写多本书
一本书也可以有多个作者
双向的一对多，即多对多。要形成对应关系需创建第三张表：一个字段是外键关联另一个表的主键，另一个字段外键关联另一张表的主键。
mysql> create table author( -> id int primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.34 sec) mysql> create table book( -> id int primary key auto_increment, -> name varchar(20) -> ); Query OK, 0 rows affected (0.36 sec) mysql> insert into author(name) values ('江南'),('今何在'); Query OK, 2 rows affected (0.13 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> insert into book(name) values ('九州'),('龙族'),('悟空传'); Query OK, 3 rows affected (0.13 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> create table author_book( -> id int not null unique auto_increment, -> author_id int not null, -> book_id int not null, -> constraint fk_author foreign key(author_id) references author(id) -> on delete cascade -> on update cascade, -> constraint fk_book foreign key(book_id) references book(id) -> on delete cascade -> on update cascade, -> primary key(author_id,book_id) -> ); Query OK, 0 rows affected (0.45 sec) mysql> insert into author_book(author_id,book_id) values -> (1,1), -> (1,2), -> (2,1), -> (2,3); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> desc author_book; +-----------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+---------+------+-----+---------+----------------+ | id | int(11) | NO | UNI | NULL | auto_increment | | author_id | int(11) | NO | PRI | NULL | | | book_id | int(11) | NO | PRI | NULL | | +-----------+---------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> select * from book; +----+-----------+ | id | name | +----+-----------+ | 1 | 九州 | | 2 | 龙族 | | 3 | 悟空传 | +----+-----------+ 3 rows in set (0.00 sec) mysql> select * from author; +----+-----------+ | id | name | +----+-----------+ | 1 | 江南 | | 2 | 今何在 | +----+-----------+ 2 rows in set (0.00 sec) mysql> select * from author_book; +----+-----------+---------+ | id | author_id | book_id | +----+-----------+---------+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 | 2 | 1 | | 4 | 2 | 3 | +----+-----------+---------+ 4 rows in set (0.00 sec)一对一
关联方式：foreign key + unique
mysql> create table customer( -> id int primary key auto_increment, -> name varchar(20) not null, -> qq varchar(10) not null, -> phone char(16) not null -> ); Query OK, 0 rows affected (0.26 sec) mysql> create table student( -> id int primary key auto_increment, -> class_name varchar(20) not null, -> customer_id int unique, -> foreign key(customer_id) references customer(id) -> on delete cascade -> on update cascade -> ); Query OK, 0 rows affected (0.54 sec) mysql> insert into customer(name,qq,phone) values -> ('皮皮寒','31811231','13811341220'), -> ('铁憨憨','123123123','15238383838'), -> ('碎小梦','283818181','18617171717'); Query OK, 3 rows affected (0.15 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into student(class_name,customer_id) values -> ('龙吟',1), -> ('铁衣',2), -> ('碎梦',3); Query OK, 3 rows affected (0.14 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from student; +----+------------+-------------+ | id | class_name | customer_id | +----+------------+-------------+ | 1 | 龙吟 | 1 | | 2 | 铁衣 | 2 | | 3 | 碎梦 | 3 | +----+------------+-------------+ 3 rows in set (0.00 sec)
扫一扫
4219
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
