存储引擎和表的操作(mysql中的数据类型、完整性约束)
一、存储引擎 1、概念 MySQL中的数据用各种不同的技术存储在文件（或者内存）中。这些技术中的每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。 通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。 2、mysql支持的存储引擎 mysql5.6支持的存储引擎包括InnoDB、MyISAM、MEMORY、BLACKHOLE、CSV、FEDERATED、MRG_MYISAM、ARCHIVE、PERFORMANCE_SCHEMA。 其中NDB和InnoDB提供事务安全表，其他存储引擎都是非事务安全表。 3、各种存储引擎的特性 并发性：某些应用程序比其他应用程序具有很多的颗粒级锁定要求（如行级锁定）。 事务支持：并非所有的应用程序都需要事务，但对的确需要事务的应用程序来说，有着定义良好的需求，如ACID兼容等。 引用完整性：通过DDL定义的外键，服务器需要强制保持关联数据库的引用完整性。 物理存储：它包括各种各样的事项，从表和索引的总的页大小，到存储数据所需的格式，到物理磁盘。 索引支持：不同的应用程序倾向于采用不同的索引策略，每种存储引擎通常有自己的编制索引方法，但某些索引方法（如B-tree索引）对几乎所有的存储引擎来说是共同的。 内存高速缓冲：与其他应用程序相比，不同的应用程序对某些内存高速缓冲策略的响应更好，因此，尽管某些内存高速缓冲对所有存储引擎来说是共同的（如用于用户连接的高速缓冲，MySQL的高速查询高速缓冲等），其他高速缓冲策略仅当使用特殊的存储引擎时才唯一定义。 性能帮助：包括针对并行操作的多I/O线程，线程并发性，数据库检查点，成批插入处理等。 其他目标特性：可能包括对地理空间操作的支持，对特定数据处理操作的安全限制等。 4、事务，行级锁，表级锁的解释 1.事务：一个最小的不可再分的工作单元，通常一个事务对应一个完整的业务 一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。 比如：一个转账的场景小明转账给小红，如果转账的过程中数据库挂了，那么小明和小红的数据都不会发生改变 开启事务 更新小明的数据：他的钱-200 更新小红的数据：她的钱+200 关闭事务 如果在小明转账但是小红还没收到账的中途，数据库挂了： 开启事务 更新小明的数据：他的钱-200 这个时候数据库挂了 更新小红的数据：她的钱+200 关闭事务 那么小明的钱不会-200，小红的钱也不会+200，好像没发生转账这件事一样 2.行级锁：不同行可以同时修改，同一行不能同时修改 msyql同一张表中不同行的记录可以被同时修改 但是同一张表中同一行的记录不能被同时修改 3.表级锁：不同表可以同时修改，同一张表不能同时修改 5、存储引擎机制 1.InnoDB：支持事务、行级锁、外键,保持事务的完整性，在修改数据的效率比较快 MySql 5.6 版本默认的存储引擎。InnoDB 是一个事务安全的存储引擎，它具备提交、回滚以及崩溃恢复的功能以保护用户数据。InnoDB 的行级别锁定以及 Oracle 风格的一致性无锁读提升了它的多用户并发数以及性能。 InnoDB 将用户数据存储在聚集索引中以减少基于主键的普通查询所带来的 I/O 开销。为了保证数据的完整性，InnoDB 还支持外键约束。 2.MyISAM：表级锁，查询速度快，但是插入和修改效率慢 MyISAM既不支持事务、也不支持外键、其优势是访问速度快，但是表级别的锁定限制了它在读写负载方面的性能，因此它经常应用于只读或者以读为主的数据场景。 3.Memory：数据都存在内存中，处理数据的速度快，但是对内存要求高，重启服务和断电消失 在内存中存储所有数据，应用于对非关键数据由快速查找的场景。Memory类型的表访问数据非常快，因为它的数据是存放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失， 长用于数据量小的数据库，并对服务器的内存有要求，一般应用于每个用户的登录状态。 4.BLACKHOLE：放进去的所有数据都不会存储，但有一个日志记录着插入的数据，利用日志分流数据 黑洞存储引擎，类似于 Unix 的 /dev/null，Archive 只接收但却并不保存数据。对这种引擎的表的查询常常返回一个空集。这种表可以应用于 DML 语句需要发送到从服务器，但主服务器并不会保留这种数据的备份的主从配置中。 5.CSV 它的表真的是以逗号分隔的文本文件。CSV 表允许你以 CSV 格式导入导出数据，以相同的读和写的格式和脚本和应用交互数据。由于 CSV 表没有索引，你最好是在普通操作中将数据放在 InnoDB 表里，只有在导入或导出阶段使用一下 CSV 表。 6.NDB (又名 NDBCLUSTER)——这种集群数据引擎尤其适合于需要最高程度的正常运行时间和可用性的应用。注意：NDB 存储引擎在标准 MySql 5.6 版本里并不被支持。目前能够支持 MySql 集群的版本有：基于 MySql 5.1 的 MySQL Cluster NDB 7.1；基于 MySql 5.5 的 MySQL Cluster NDB 7.2；基于 MySql 5.6 的 MySQL Cluster NDB 7.3。同样基于 MySql 5.6 的 MySQL Cluster NDB 7.4 目前正处于研发阶段。 7.Merge 允许 MySql DBA 或开发者将一系列相同的 MyISAM 表进行分组，并把它们作为一个对象进行引用。适用于超大规模数据场景，如数据仓库。 8.Federated 提供了从多个物理机上联接不同的 MySql 服务器来创建一个逻辑数据库的能力。适用于分布式或者数据市场的场景。 了解一下BLACKHOLE机制(了解即可)：6、常用的存储引擎及适用场景 InnoDB 用于事务处理应用程序，支持外键和行级锁。如果应用对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包括很多更新和删除操作，那么InnoDB存储引擎是比较合适的。 InnoDB除了有效的降低由删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似计费系统或者财务系统等对数据准确要求性比较高的系统都是合适的选择。 MyISAM 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不高，那么可以选择这个存储引擎。 Memory 将所有的数据保存在内存中，在需要快速定位记录和其他类似数据的环境下，可以提供极快的访问。Memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的话数据可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。 7、存储相关sql语句 1.查看当前的默认存储引擎: mysql> show variables like "default_storage_engine"; 2.查询当前数据库支持的存储引擎 mysql> show engines \G; 8、指定存储引擎建表 1.在建表时指定 mysql> create table t1(id int,name varchar(20)) ENGINE=MyISAM; mysql> create table country(id int,cname varchar(50)) ENGINE=InnoDB; 2.也可以使用alter table语句，修改一个已经存在的表的存储引擎。 mysql> alter table country engine = MyISAM; 9、在配置文件中指定 #my.ini文件 [mysqld] default-storage-engine=INNODB 二、表的基本操作 1、创建表语法： create table 表名(字段名1 类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3 类型[(宽度) 约束条件]); #注意： 1. 在同一张表中，字段名是不能相同 2. 字段名和类型是必须的 3. 宽度和约束条件可选 mysql> create table staff_info (id int, name varchar(20), age int,sex enum('female','male'), phone char(11), job varchar(20)); Query OK, 0 rows affected (0.20 sec) 2、查看表结构 mysql> show tables; 查看当前库有什么表 mysql> desc 表名; 查看当前的表结构 mysql> describe 表名; 查看当前的表结构 mysql> show create table 表名 \G; 查看当前表更详细的信息 mysql> desc staff_info; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(11) | YES | | NULL | | | sex | enum('female','male') | YES | | NULL | | | phone | char(11) | YES | | NULL | | | job | varchar(20) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ 3、 插入数据 指定列名插入数据 mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'xiaoming',18,'male','13561054328','IT'); 插入单行数据 mysql> insert into staff_info values (1,'xiaoming',18,'male','13561054328','IT'); 插入多行数据 mysql> insert into staff_info values -> (2,'xiaohong',17,'female','13323387673','Tearcher'), -> (3,'mingge',19,'male','13563098375','IT'); 4、查询数据 查看所有列的数据 mysql> select * from staff_info; 查看指定列的数据 mysql> select name,age from staff_info; 5、例1： mysql> create table test (id int,name varchar(20),age int(4)); # 创建表 mysql> show tables; # 查看所有表 +---------------+ | Tables_in_db1 | +---------------+ | test | +---------------+ mysql> desc test; # 查看test这个表的字段信息 +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | | age | int(4) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ mysql> insert into test (id,name,age) values (1,'xiaoming',18); # 插入一条记录 Query OK, 1 row affected (0.10 sec) mysql> insert into test values (2,'xiaohong',17); # 插入一条记录 Query OK, 1 row affected (0.30 sec) mysql> select * from test; # 查看这个表的所有内容 +------+----------+------+ | id | name | age | +------+----------+------+ | 1 | xiaoming | 18 | | 2 | xiaohong | 17 | +------+----------+------+ 2 rows in set (0.05 sec) 三、mysql中的数据类型 1、数值类型
| 类型 | 大小 | 范围（有符号） | 范围（无符号）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+2 double(65,30) |
依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
介绍常用的类型： 整型：int\integer: 有符号时的范围：(-2147483648，2147483647) --> 2**31 无符号时的范围：(0，4294967295) --> 2**32 给字段添加一个unsigned表示无符号，默认是有符号的 计算范围：int占4个字节，每个字节用8位二进制表示的，共4*8=32位二进制数，而每位二进制可以表示两种情况，故无符号时的范围是：2**32，
有符号时，用一位二进制表示符号，故有符号的范围是：2**31，其他数据类型的范围也是类似此算法。 小数： float： 单精度小数，不是很精确 double： 双精度小数，不是很精确，但是比float精确一点 DECIMAL：非常精确的小数，对数据要求很精确的时候使用，比如汇率，利息等。 int整数示例 # 创建表一个是默认宽度的int，一个是指定宽度的int(5) mysql> create table int_table (num1 int,num2 int(5)); # 向t1中插入数据1,2 mysql> insert into int_table values(1,2); # 此时并没有异常 mysql> select * from int_table; +------+------+ | num1 | num2 | +------+------+ | 1 | 2 | +------+------+ # 继续插入比宽度更大的值，num2仍然显示了正确的数值，没有受到宽度限制的影响 mysql> insert into int_table values(123456,1234567); mysql> select * from int_table; +--------+---------+ | num1 | num2 | +--------+---------+ | 1 | 2 | | 123456 | 1234567 | +--------+---------+ # 修改num1字段 给字段添加一个unsigned表示无符号 mysql> alter table int_table modify num1 int unsigned; mysql> desc int_table; +-------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+-------+ | num1 | int(10) unsigned | YES | | NULL | | | num2 | int(5) | YES | | NULL | | +-------+------------------+------+-----+---------+-------+ # 当给num1添加的数据大于2147483647时，可以顺利插入 mysql> insert into int_table values (2147483648,2147483647); Query OK, 1 row affected (0.07 sec) # 当给num2添加的数据大于2147483647时，会报错 mysql> insert into int_table values (2147483648,2147483648); ERROR 1264 (22003): Out of range value for column 'num2' at row 1 # 总结：
int类型，存储的值只要不超过int的范围(有符号和无符号的范围不同)，指不指定宽度都是一样的。
小数示例 # 创建表的三个字段分别为float，double和decimal参数表示一共显示5位，小数部分占2位 mysql> create table test_table(num1 float(5,2),num2 double(5,2),num3 decimal(5,2)); # 向表中插入3.14，结果正常 mysql> insert into test_table values(3.14,3.14,3.14); Query OK, 1 row affected (0.11 sec) mysql> select * from test_table; +------+------+------+ | num1 | num2 | num3 | +------+------+------+ | 3.14 | 3.14 | 3.14 | +------+------+------+ 1 row in set (0.00 sec) # 向表中插入3.141，会发现1都被截断了 mysql> insert into test_table values(3.141,3.141,3.141); Query OK, 1 row affected, 1 warning (0.06 sec) mysql> select * from test_table; +------+------+------+ | num1 | num2 | num3 | +------+------+------+ | 3.14 | 3.14 | 3.14 | | 3.14 | 3.14 | 3.14 | +------+------+------+ 2 rows in set (0.00 sec) # 向表中插入3.145发现数据虽然被截断，但是遵循了四舍五入的规则 mysql> insert into test_table values(3.145,3.145,3.145); Query OK, 1 row affected, 1 warning (0.10 sec) mysql> select * from test_table; +------+------+------+ | num1 | num2 | num3 | +------+------+------+ | 3.14 | 3.14 | 3.14 | | 3.14 | 3.14 | 3.14 | | 3.15 | 3.15 | 3.15 | +------+------+------+ 3 rows in set (0.00 sec) # 建新表去掉参数约束 mysql> create table test2_table(num1 float,num2 double,num3 decimal); Query OK, 0 rows affected (0.63 sec) # 分别插入3.145 mysql> insert into test2_table values(3.145,3.145,3.145); Query OK, 1 row affected, 1 warning (0.06 sec) # 发现decimal默认值是(10,0)的整数 mysql> select * from test2_table; +-------+-------+------+ | num1 | num2 | num3 | +-------+-------+------+ | 3.145 | 3.145 | 3 | +-------+-------+------+ 1 row in set (0.00 sec) # 当对小数位没有约束的时候，输入超长的小数，会发现float没有double精确，但是实际上两个都不完全准确(用更长的数字试试) mysql> insert into test2_table values(3.14159612345,3.14159612345,3.14159612345); Query OK, 1 row affected, 1 warning (0.09 sec) mysql> select * from test2_table; +--------+---------------+------+ | num1 | num2 | num3 | +--------+---------------+------+ | 3.145 | 3.145 | 3 | | 3.1416 | 3.14159612345 | 3 | +--------+---------------+------+ 2 rows in set (0.00 sec) 2、日期和时间类型 表示时间值的日期和时间类型为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 描述年月日 1000-01-01/9999-12-31 time 描述时分秒 datetime 描述年月日时分秒 1000-01-01 00:00：00/9999-12-31 23:59:59 timestamp 描述年月日时分秒，字段默认不为空 1970-01-01 00:00:00/2038 timestamp默认不为空，默认是当前时间，范围比datetime小 mysql> create table t1 (d date,t time,y year,dt datetime,ts timestamp); mysql> desc t1; +-------+-----------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------+------+-----+-------------------+-----------------------------+ | d | date | YES | | NULL | | | t | time | YES | | NULL | | | y | year(4) | YES | | NULL | | | dt | datetime | YES | | NULL | | | ts | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-------+-----------+------+-----+-------------------+-----------------------------+ mysql> insert into t1 values (null,null,null,null,null); mysql> select * from t1; +------+------+------+------+---------------------+ | d | t | y | dt | ts | +------+------+------+------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 | +------+------+------+------+---------------------+
# 插入当期时间 mysql> insert into t1 values(now(),now(),now(),now(),now()); # 每种数据类型表示的时间格式 mysql> select * from t1; +------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 | | 2018-09-29 | 12:12:40 | 2018 | 2018-09-29 12:12:40 | 2018-09-29 12:12:40 | +------------+----------+------+---------------------+---------------------+ # datetime 和 timestamp的范围控制 mysql> insert into t1 (dt) values (10010101000000); mysql> select * from t1; +------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 | | 2018-09-29 | 12:12:40 | 2018 | 2018-09-29 12:12:40 | 2018-09-29 12:12:40 | | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 12:13:14 | +------------+----------+------+---------------------+---------------------+ # 超出了timestamp的范围，就使用它的"零"值 mysql> insert into t1(ts) values (10010101000000); mysql> select * from t1; +------------+----------+------+---------------------+---------------------+ | d | t | y | dt | ts | +------------+----------+------+---------------------+---------------------+ | NULL | NULL | NULL | NULL | 2018-09-29 12:12:30 | | 2018-09-29 | 12:12:40 | 2018 | 2018-09-29 12:12:40 | 2018-09-29 12:12:40 | | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 12:13:14 | | NULL | NULL | NULL | NULL | 0000-00-00 00:00:00 | +------------+----------+------+---------------------+---------------------+ 3、字符串类型
| 类型 | 大小 | 用途 |
|---|---|---|
| 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字节 | 极大文本数据 |
CHAR 0-255字节 定长字符串 定长 浪费磁盘 存取速度非常快 VARCHAR 0-65535 字节 变长字符串 变长 节省磁盘空间 存取速度相对慢
例如： char(5)：
定长的，无论写多少字节都是你设置的那个长度 'abcde':5个字节 'abc':会变成' abc' 会在前面补两个空格，还是5个字节 适用于数据的长度变化小或者不变化的场景：手机号 身份证号 学号 频繁存取、对效率要求高 短数据
varchar(5)：
不定长，但是会在你输入的字节前加上长度信息，即总是多一个字节 'abc' ：会变成'3abc' 3是你输入的字节abc的长度，但是显示的时候我们看不到这个3，但存储的时候总字节是4 'abcde' 会变成'5abcde' 5是你输入的字节abcde的长度，但是显示的时候我们看不到这个5，但存储的时候总字节是6 适用于数据长度变化大的场景：name 描述信息 对效率要求相对小 数据相对较长 例子：
mysql> create table t10 (c char(5),vc varchar(5)); mysql> desc t10; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c | char(5) | YES | | NULL | | | vc | varchar(5) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ # 插入ab，实际上存储中c占用5个字节，vc只占用3个字节，但是我们查询的时候感知不到 # 因为char类型在查询的时候会默认去掉所有补全的空格 mysql> insert into t10 values ('ab','ab'); mysql> select * from t10; +------+------+ | c | vc | +------+------+ | ab | ab | +------+------+ # 插入的数据超过了约束的范围，会截断数据 mysql> insert into t10 values ('abcdef','abcdef'); mysql> select * from t10; +-------+-------+ | c | vc | +-------+-------+ | ab | ab | | abcde | abcde | +-------+-------+ # 插入带有空格的数据，查询的时候能看到varchar字段是带空格显示的，char字段仍然在显示的时候去掉了空格 mysql> insert into t10 values ('ab ','ab '); mysql> select * from t10; +-------+-------+ | c | vc | +-------+-------+ | ab | ab | | abcde | abcde | | ab | ab | +-------+-------+ # 用concat连接查看结果 mysql> select concat(c,'+'),concat(vc,'+') from t10; +---------------+----------------+ | concat(c,'+') | concat(vc,'+') | +---------------+----------------+ | ab+ | ab+ | | abcde+ | abcde+ | | ab+ | ab + | +---------------+----------------+ 4、ENUM和SET类型
| 类型 | 大小 | 用途 |
| 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 单选 集合 set 多选 mysql> create table t11 (name varchar(20),sex enum('male','female'),hobby set('抽烟','喝酒','烫头','翻车')); mysql> desc t11; +-------+------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------------------------------+------+-----+---------+-------+ | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | hobby | set('抽烟','喝酒','烫头','翻车') | YES | | NULL | | +-------+------------------------------------------+------+-----+---------+-------+ # 如果插入的数据不在枚举或者集合范围内，数据无法插入表 mysql> insert into t11 values ('ming','aaaa','bbbb'); mysql> select * from t11; +------+------+-------+ | name | sex | hobby | +------+------+-------+ | ming | | | +------+------+-------+ # 向集合中插入数据，自动去重 mysql> insert into t11 values ('ming','female','抽烟,抽烟,烫头'); mysql> select * from t11; +------+--------+---------------+ | name | sex | hobby | +------+--------+---------------+ | ming | | | | ming | female | 抽烟,烫头 | +------+--------+---------------+ # 向集合中插入多条数据，不存在的项无法插入 mysql> insert into t11 values ('ming','female','抽烟,抽烟,烫头,打架'); mysql> select * from t11; +------+--------+---------------+ | name | sex | hobby | +------+--------+---------------+ | ming | | | | ming | female | 抽烟,烫头 | | ming | female | 抽烟,烫头 | +------+--------+---------------+ 四、完整性约束 为了防止不符合规范的数据进入数据库，在用户对数据进行插入、修改、删除等操作时，DBMS自动按照一定的约束条件对数据进行监测，使不符合规范的数据不能进入数据库，以确保数据库中存储的数据正确、有效、相容。 约束条件与数据类型的宽度一样，都是可选参数，主要分为以下几种： NOT NULL ：非空约束，指定某列不能为空； UNIQUE : 唯一约束，指定某列或者几列组合不能重复 PRIMARY KEY ：主键，指定该列的值可以唯一地标识该列记录 FOREIGN KEY ：外键，指定该行记录从属于主表中的一条记录，主要用于参照完整性 not null：非空 default：设置了非空约束的字段才能设置默认值，如果不输入就使用默认的值 unique：唯一 auto_increment：只有唯一且数字类型的字段才能设置自增
联合唯一 unique(字段1，字段2...) 就是给一个以上的字段设置 唯一约束 primary key：主键--> 唯一+非空 加速查询 每张表只能有一个主键 当我们以唯一且非空的约束来创建一个字段的时候， 如果我们没有指定主键，那么第一个唯一且非空的字段将会被设置成主键
如果主动设置了主键，那么唯一且非空的字段不会成为主键
联合主键 primary key(字段1，字段2...) 就是给一个以上的字段设置 唯一非空约束 foreign key：外键--> 只有另一个表中设置了unique的字段才能关联本表的外键 表类型必须是innodb存储引擎 on delete cascade --> 连级删除 on update cascade --> 连级跟新 1、非空：NOT NULL mysql> create table t3 (id int not null); mysql> desc t3; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | +-------+---------+------+-----+---------+-------+ # 插入非空，显示错误 mysql> insert into t3 values(null); ERROR 1048 (23000): Column 'id' cannot be null # 插入123，正常插入 mysql> insert into t3 values(123); Query OK, 1 row affected (0.08 sec) DEFAULT：在设置非空之后，如果这一列经常有重复的内容输入，为了方便插入，可以设置默认值 创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值 mysql> create table t4 (id int not null,sex enum('male','female') not null default 'male'); mysql> describe t4; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | +-------+-----------------------+------+-----+---------+-------+ # 只向id字段添加值，会发现sex字段会使用默认值填充 mysql> insert into t4 (id) values (1); mysql> select * from t4; +----+------+ | id | sex | +----+------+ | 1 | male | +----+------+ # id字段不能为空，所以不能单独向sex字段填充值 mysql> insert into t4 (sex) values('female'); ERROR 1364 (HY000): Field 'id' doesn't have a default value # 向id，sex中分别填充数据，sex的填充数据会覆盖默认值 mysql> insert into t4 (id,sex) values(2,'female'); mysql> select * from t4; +----+--------+ | id | sex | +----+--------+ | 1 | male | | 2 | female | +----+--------+ 2、UNIQUE：唯一 方法一：在定义字段的时候约束 mysql> create table t1(id int unique,name varchar(20)); mysql> desc t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 方法二：最后使用unique约束字段 mysql> create table t2(id int,name varchar(20),unique(id)); mysql> desc t2; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | UNI | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ # auto_increment 只有唯一且数字类型才能设置自增 mysql> create table t3(id int auto_increment,name varchar(20)); ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key mysql> create table t3(id int unique auto_increment,name varchar(20)); Query OK, 0 rows affected (0.39 sec) mysql> insert into t3(name) values('a'),('b'); mysql> select * from t3; +----+------+ | id | name | +----+------+ | 1 | a | | 2 | b | +----+------+ # 联合唯一：就是给一个以上的字段设置 唯一非空约束，即单独的字段可以不唯一，但是联合在一起的组合必须是唯一的 mysql> create table t4(id int unique auto_increment, ip varchar(15) not null, port int not null, unique(ip,port)); # 联合唯一 mysql> insert into t4 (ip,port) values('192.168.0.1',8000); Query OK, 1 row affected (0.07 sec) mysql> insert into t4 (ip,port) values('192.168.0.1',8001); # ip相同，端口不同，可以插入 Query OK, 1 row affected (0.09 sec) mysql> insert into t4 (ip,port) values('192.168.0.2',8001); # ip不同，端口相同，可以插入 Query OK, 1 row affected (0.08 sec) mysql> insert into t4 (ip,port) values('192.168.0.2',8001); # ip相同，端口相同，不可以插入 ERROR 1062 (23000): Duplicate entry '192.168.0.2-8001' for key 'ip' mysql> select * from t4; +----+-------------+------+ | id | ip | port | +----+-------------+------+ | 1 | 192.168.0.1 | 8000 | | 2 | 192.168.0.1 | 8001 | | 3 | 192.168.0.2 | 8001 | +----+-------------+------+ 3、PRIMARY KEY：主键--非空且唯一(主键也可以设置AUTO_INCREMENT自增的，因为主键也是唯一的) mysql> create table t5 (id int not null unique); # 若没有主键，那么把id设置为非空且唯一后，自动成为主键 mysql> desc t5; # 可以看到id Key的属性被设置为PRI(primary主键) +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ # 也可以自己直接设置主键 mysql> create table t6 (num int primary key); mysql> desc t6; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | num | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ # 注意：若指定主键之后其他的非空 + 唯一约束都不会再成为主键 mysql> create table t7 (id1 int unique not null,id2 int primary key); mysql> desc t7; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id1 | int(11) | NO | UNI | NULL | | | id2 | int(11) | NO | PRI | NULL | | +-------+---------+------+-----+---------+-------+ # 联合主键--把一个以上的字段设置成主键，即单独的字段可以不唯一，但是联合在一起的组合是主键，必须是唯一且非空的 mysql> create table t8(ip varchar(15),port char(5),primary key(ip,port)); # 设置成联合主键 mysql> desc t8; # 可以看到ip和port都显示是主键PRI +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ip | varchar(15) | NO | PRI | | | | port | char(5) | NO | PRI | | | +-------+-------------+------+-----+---------+-------+ mysql> insert into t8 values('192.168.0.1','9000'); Query OK, 1 row affected (0.05 sec) mysql> insert into t8 values('192.168.0.1','9001'); # ip相同，端口不同，可以插入 Query OK, 1 row affected (0.15 sec) mysql> insert into t8 values('192.168.0.2','9001'); # ip不同，端口相同，可以插入 Query OK, 1 row affected (0.10 sec) mysql> insert into t8 values('192.168.0.2','9001'); # ip相同，端口相同，不可以插入 ERROR 1062 (23000): Duplicate entry '192.168.0.2-9001' for key 'PRIMARY' mysql> select * from t8; +-------------+------+ | ip | port | +-------------+------+ | 192.168.0.1 | 9000 | | 192.168.0.1 | 9001 | | 192.168.0.2 | 9001 | +-------------+------+ 4、FOREIGN KEY：外键--只有另一个表中设置了unique的字段才能关联本表的外键 mysql> create table t9 (id int unique,course varchar(20)); # 先设置表t9的id是唯一的 mysql> create table t10 (id int,age int,t9_id int,foreign key(t9_id) references t9(id)); # 在表t10指定t9_id作为外键关联到表t9的id字段 mysql> desc t10; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | YES | | NULL | | | t9_id | int(11) | YES | MUL | NULL | | +-------+---------+------+-----+---------+-------+ # 注意：如果一个表中的字段是另外一个表的外键，即这个表的某个字段对外表提供服务，那么默认不能直接删除外表中正在使用的数据 # 比如上面的例子，t10表的外键t9_id关联着t9表的id，那么t9不能直接删除自己的id字段，因为t9的id字段t10在使用着， # 如果要删除，那么要先删除t10的外键后才能删除t9的id，这样就很麻烦，那么就需要使用： # on delete cascade # 连级删除 # on update cascade # 连级更新 # 表示当t9删除自己id字段的时候，t10会跟着删除相应的外键 例如： mysql> create table course (cid int primary key auto_increment,cname varchar(20) not null); # 创建course表 # 创建student表，外键是course_id关联course表的cid，并设置连级删除，连级跟新 mysql> create table student (sid int primary key auto_increment, -> sname varchar(20) not null, -> course_id int, -> foreign key(course_id) references course(cid) on delete cascade on update cascade); # 学生表 mysql> select * from student; +-----+------------+-----------+ | sid | sname | course_id | +-----+------------+-----------+ | 1 | zhangsange | 1 | | 2 | lisihao | 2 | +-----+------------+-----------+ # 课程表 mysql> select * from course; +-----+--------+ | cid | cname | +-----+--------+ | 1 | python | | 2 | linux | +-----+--------+ # 删除课程表的python课，学生表也跟着删除了 mysql> delete from course where cid = 1; mysql> select * from student; +-----+---------+-----------+ | sid | sname | course_id | +-----+---------+-----------+ | 2 | lisihao | 2 | +-----+---------+-----------+ # 跟新课程表的linux课id，学生表也跟着跟新了 mysql> update course set cid = 1 where cid = 2; mysql> select * from student; +-----+---------+-----------+ | sid | sname | course_id | +-----+---------+-----------+ | 2 | lisihao | 1 | +-----+---------+-----------+ 五、修改表结构 1、语法： 1. 修改表名 ALTER TABLE 表名 RENAME 新表名; 2. 增加字段 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…]; 3. 删除字段 ALTER TABLE 表名 DROP 字段名; 4. 修改字段：modify 修改数据类型和约束，change修改字段名并重新定义数据类型和约束 ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…]; ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…]; 5.修改字段排列顺序/在增加的时候指定字段位置 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] FIRST; # 放在首位 ALTER TABLE 表名 ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名; # 在某个字段后 ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST; ALTER TABLE 表名 MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名; 6.增加/删除约束 ALTER TABLE 表名 ADD PRIMARY KEY (字段); ALTER TABLE 表名 ADD UNIQUE (字段); ALTER TABLE 表名 DROP PRIMARY KEY; 2、例子 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 | | +-------+-----------------------+------+-----+---------+-------+ # 表重命名 mysql> alter table staff_info rename staff; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | 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 | | +-------+-----------------------+------+-----+---------+-------+ # 删除age列 mysql> alter table staff drop age; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 添加age列，并将此列放在id后面 mysql> alter table staff add age int Not null after id; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | NO | | NULL | | | name | varchar(50) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 修改name的宽度 mysql> alter table staff modify name varchar(20); mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | NO | | NULL | | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 修改name列的字段名 mysql> alter table staff change name sname varchar(25) not null; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sname | varchar(25) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 修改sname列的位置 mysql> alter table staff modify sname varchar(25) after id; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 创建自增id主键 mysql> alter table staff modify id int primary key auto_increment; mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ # 删除主键，但是删除一个自增主键会报错 mysql> alter table staff drop primary key; ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key # 需要先去掉主键的自增约束，然后再删除主键约束 mysql> alter table staff modify id int; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ mysql> alter table staff drop primary key; Query OK, 3 rows affected (0.98 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | sname | varchar(25) | YES | | NULL | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 添加联合主键 mysql> alter table staff add primary key (id,sname); mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | PRI | 0 | | | sname | varchar(25) | NO | PRI | | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 删除联合主键 mysql> alter table staff drop primary key; mysql> desc staff; +-------+-----------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+-------+ | id | int(11) | NO | | 0 | | | sname | varchar(25) | NO | | | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+-------+ # 为id添加自增属性(自增需要字段是唯一的) mysql> alter table staff modify id int unique auto_increment; mysql> desc staff; +-------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(25) | NO | | | | | age | int(11) | NO | | NULL | | | sex | enum('male','female') | YES | | NULL | | | phone | bigint(11) | YES | | NULL | | | job | varchar(11) | YES | | NULL | | +-------+-----------------------+------+-----+---------+----------------+ 六、删除表 DROP TABLE 表名;
浙公网安备 33010602011771号