一，存储引擎
1.InnoDB
特点：支持事务、行级锁、外链
事务：在开启事务后，如果事务内的操作不能完成，则会回退至操作前的状态，维护了数据的安全性
行级锁：innodb
同一时间只能由一个人对同一行数据进行修改
同一张表中不同行的记录可以被同时修改
外键：foreign key
Innodb独有
表文件结构：.frm、.ibd(数据)
2.MyIsam
既不支持事务，也不支持外键，其优势是访问速度快，但是表级锁限制了它在读写方面的性能
因此它经常应用于只读或者以读为主的数据场景
表级锁：myisam
同一张表中数据不能被同时修改
表文件结构：.frm、.MYD(数据)、.MYI(索引)
3.blockhole
黑洞存储引擎，只接受但却并不保存数据，对这种引擎的表进行查询通常会返回一个空集
这种表可以应用于DML语句需要发送到从服务器，但是主服务器并不会保留这种数据备份的主从配置中。
表文件结构：.frm
4.memory
特点：
数据都存在内存中
数据存储量小，并对服务器的内存有要求
断电数据消失
memory类型的表访问数据速度非常快，由于他的数据是存放在内存中的，并且默认使用hash索引，memory的缺陷是对表的大小有限制，虽然数据库因为异常终止的化可以正常恢复，但是一旦数据库关闭，存储在内存中的数据都会丢失。
应用场景：记录每个用户的登录状态
表文件结构：.frm
#存储引擎相关sql语句： #查看当前的默认存储引擎: mysql> show variables like "default_storage_engine"; #查询当前数据库支持的存储引擎 mysql> show engines G; #在建表时指定存储引擎 mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB; #也可以使用alter table语句，修改一个已经存在的表的存储引擎。 mysql> alter table ai engine = innodb; #在配置文件中指定默认存储引擎 #my.ini文件 [mysqld] default-storage-engine=INNODB
二，常用数据类型：
1.数值类型
INT：4字节，范围：0~4294967275（无符号）
FLOAT：4字节，(255,30) 单精度，浮点数
DECIMAL：高精度小数，底层是将浮点数转换为浮点数转换为字符串后存储
应用：汇率，利息，对小数精度要求极高的场景
# 整数部分 mysql> create table t5 (id1 int(4),id2 int); # Query OK, 0 rows affected (0.16 sec) # mysql> desc t5; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id1 | int(4) | YES | | NULL | | # | id2 | int(11) | YES | | NULL | | # +-------+---------+------+-----+---------+-------+ # 2 rows in set (0.01 sec) # mysql> insert into t5 values (123,123); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t5; # +------+------+ # | id1 | id2 | # +------+------+ # | 123 | 123 | # +------+------+ # 1 row in set (0.00 sec) mysql> insert into t5 values (12345,12345); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t5; # +-------+-------+ # | id1 | id2 | # +-------+-------+ # | 123 | 123 | # | 12345 | 12345 | # +-------+-------+ # 2 rows in set (0.00 sec) # mysql> insert into t5 values (123,2147483648); # Query OK, 1 row affected, 1 warning (0.02 sec) # mysql> select * from t5; # +-------+------------+ # | id1 | id2 | # +-------+------------+ # | 123 | 123 | # | 12345 | 12345 | # | 123 | 2147483647 | # +-------+------------+ # 3 rows in set (0.00 sec) # 范围测试 有符号和无符号 mysql> create table t6 (id1 int(4),id2 int unsigned); # Query OK, 0 rows affected (0.25 sec) # mysql> insert into t6 values (123,2147483648); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t6; # +------+------------+ # | id1 | id2 | # +------+------------+ # | 123 | 2147483648 | # +------+------------+ # 1 row in set (0.00 sec) # 小数 # 长度约束测试 mysql> create table t7 (f float(10,3),d double(10,3),d2 decimal(10,3)); # Query OK, 0 rows affected (0.20 sec) # mysql> insert into t7 values (1.23456789,2.34567,3.56789); # Query OK, 1 row affected, 1 warning (0.02 sec) # mysql> select * from t7; # +-------+-------+-------+ # | f | d | d2 | # +-------+-------+-------+ # | 1.235 | 2.346 | 3.568 | # +-------+-------+-------+ # 1 row in set (0.00 sec) # 精度测试 mysql> create table t8 (f float(255,30),d double(255,30),d2 decimal(65,30)); # Query OK, 0 rows affected (0.18 sec) mysql> insert into t8 values (1.11111111111111111111111111111111111111111111111111111111111,1.11111111111111111111111111111111111111111111111111111111111,1.11111111111111111111111111111111111111111111111111111111111); # Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t8; # +----------------------------------+----------------------------------+----------------------------------+ # | f | d | d2 | # +----------------------------------+----------------------------------+----------------------------------+ # | 1.111111164093017600000000000000 | 1.111111111111111200000000000000 | 1.111111111111111111111111111111 | # +----------------------------------+----------------------------------+----------------------------------+
2.日期和时间类型
DATE: 3个字节，格式YYYY-MM-DD 月年日
TIME:3个字节，格式HH:MM:SS 时分秒
YEAR:1个字节 格式YYYY 年份值
DATETIME:8个字节 格式YYYY-MM-DD，HH:MM:SS 年月日时分秒
TIMESTAMP:4个字节 格式YYYYMMDD，HHMMSS 混合日期和时间值，时间戳
timestamp时间的下限是19700101080001
timestamp时间的上限是2038011911:14:07
插入数据null，会自动插入当前时间的时间
插入超出范围的值，则会自动插入0000-00-00 00:00:00
#时间和日期部分
mysql> create table t9 (d date,t time,y year,dt datetime,ts timestamp); # Query OK, 0 rows affected (0.18 sec) # mysql> desc t9; # +-------+-----------+------+-----+-------------------+-----------------------------+ # | 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 | # +-------+-----------+------+-----+-------------------+-----------------------------+ # 5 rows in set (0.01 sec) # mysql> insert into t9 values (null,null,null,null,null); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t9; # +------+------+------+------+---------------------+ # | d | t | y | dt | ts | # +------+------+------+------+---------------------+ # | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | # +------+------+------+------+---------------------+ # 1 row in set (0.00 sec) # mysql> insert into t9 values(now(),now(),now(),now(),now()); # Query OK, 1 row affected, 1 warning (0.03 sec) # mysql> select * from t9; # +------------+----------+------+---------------------+---------------------+ # | d | t | y | dt | ts | # +------------+----------+------+---------------------+---------------------+ # | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | # | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | # +------------+----------+------+---------------------+---------------------+ # 2 rows in set (0.00 sec) # mysql> insert into t9 (dt) values (10010101000000); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t9; # +------------+----------+------+---------------------+---------------------+ # | d | t | y | dt | ts | # +------------+----------+------+---------------------+---------------------+ # | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | # | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | # | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 11:30:33 | # +------------+----------+------+---------------------+---------------------+ # 3 rows in set (0.00 sec) # mysql> insert into t9 (ds) values (10010101000000); # ERROR 1054 (42S22): Unknown column 'ds' in 'field list' mysql> insert into t9 (ts) values (10010101000000); # Query OK, 1 row affected, 1 warning (0.03 sec) # mysql> select * from t9; # +------------+----------+------+---------------------+---------------------+ # | d | t | y | dt | ts | # +------------+----------+------+---------------------+---------------------+ # | NULL | NULL | NULL | NULL | 2018-09-29 11:27:29 | # | 2018-09-29 | 11:29:07 | 2018 | 2018-09-29 11:29:07 | 2018-09-29 11:29:07 | # | NULL | NULL | NULL | 1001-01-01 00:00:00 | 2018-09-29 11:30:33 | # | NULL | NULL | NULL | NULL | 0000-00-00 00:00:00 | # +------------+----------+------+---------------------+---------------------+ # 4 rows in set (0.00 sec)
3.字符串类型
特点：
长度超过约定长度则不传
CHAR：定长字符串 长度：0-255字节
特点：定长 浪费磁盘，存取速度非常快
当传入带空格的字符是，隐藏空格
当传入的值不足约定长度是，在底层中会用空格补齐长度，所有会有浪费存储空间
应用场景：
存取频繁，效率要求较高，传入数据长度比较规律的，短数据存储
VARCHAR：变长字符串 长度：0-65535字节
特点：变长，节省磁盘空间，存取速度相对慢
存入什么就是什么
对效率要求相对小
在底层中，会在数据的头部插入1个字节数据的长度
应用场景：
数据长度变化较大，相对较长的数据
mysql> create table t10 (c char(5),vc varchar(5)); # Query OK, 0 rows affected (0.14 sec) # mysql> desc t10; # +-------+------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+------------+------+-----+---------+-------+ # | c | char(5) | YES | | NULL | | # | vc | varchar(5) | YES | | NULL | | # +-------+------------+------+-----+---------+-------+ # 2 rows in set (0.01 sec) # 插入ab，实际上存储中c占用5个字节，vc只占用3个字节，但是我们查询的额时候感知不到 # 因为char类型在查询的时候会默认去掉所有补全的空格 mysql> insert into t10 values ('ab','ab'); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t10; # +------+------+ # | c | vc | # +------+------+ # | ab | ab | # +------+------+ # 1 row in set (0.00 sec) # 插入的数据超过了约束的范围，会截断数据 mysql> insert into t10 values ('abcdef','abcdef'); # Query OK, 1 row affected, 2 warnings (0.02 sec) # mysql> select * from t10; # +-------+-------+ # | c | vc | # +-------+-------+ # | ab | ab | # | abcde | abcde | # +-------+-------+ # 2 rows in set (0.00 sec) # 插入带有空格的数据，查询的时候能看到varchar字段是带空格显示的，char字段仍然在显示的时候去掉了空格 mysql> insert into t10 values ('ab ','ab '); # Query OK, 1 row affected, 1 warning (0.03 sec) # mysql> select * from t10; # +-------+-------+ # | c | vc | # +-------+-------+ # | ab | ab | # | abcde | abcde | # | ab | ab | # +-------+-------+ # 3 rows in set (0.00 sec) # mysql> select concat(c,'+'),concat(vc,'+') from t10; # +---------------+----------------+ # | concat(c,'+') | concat(vc,'+') | # +---------------+----------------+ # | ab+ | ab+ | # | abcde+ | abcde+ | # | ab+ | ab + | # +---------------+----------------+ # 3 rows in set (0.01 sec) #
4.ENUM和SET类型
特点：不符合的规则，不写入
enum：
只允许从值集合中选取单个值，而不能一次取多个值
set：
允许集合中任意选择单个或多个元素进行组合，重复的值自动去重
多选的格式，同一字符串内，逗号隔开
mysql> create table t11 (name varchar(20),sex enum('male','female'),hobby set('抽烟','喝酒','烫头','翻车')); # Query OK, 0 rows affected (0.15 sec) # mysql> desc t11; # +-------+------------------------------------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+------------------------------------------+------+-----+---------+-------+ # | name | varchar(20) | YES | | NULL | | # | sex | enum('male','female') | YES | | NULL | | # | hobby | set('抽烟','喝酒','烫头','翻车') | YES | | NULL | | # +-------+------------------------------------------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # 如果插入的数据不在枚举或者集合范围内，数据无法插入表 mysql> insert into t11 values ('alex','aaaa','bbbb'); # Query OK, 1 row affected, 2 warnings (0.02 sec) # mysql> select * from t11; # +------+------+-------+ # | name | sex | hobby | # +------+------+-------+ # | alex | | | # +------+------+-------+ # 1 row in set (0.00 sec) # 向集合中插入数据，自动去重 mysql> insert into t11 values ('alex','female','抽烟,抽烟,烫头'); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t11; # +------+--------+---------------+ # | name | sex | hobby | # +------+--------+---------------+ # | alex | | | # | alex | female | 抽烟,烫头 | # +------+--------+---------------+ # 2 rows in set (0.00 sec) # 向集合中插入多条数据，不存在的项无法插入 mysql> insert into t11 values ('alex','female','抽烟,抽烟,烫头,打豆豆'); # Query OK, 1 row affected, 1 warning (0.03 sec) # mysql> select * from t11; # +------+--------+---------------+ # | name | sex | hobby | # +------+--------+---------------+ # | alex | | | # | alex | female | 抽烟,烫头 | # | alex | female | 抽烟,烫头 | # +------+--------+---------------+ # 3 rows in set (0.00 sec)
三，约束
#约束 not null # 非空 #default 默认值 #如果不输入就是用默认的数值 unique 唯一： # 唯一可以有一个空 #唯一 + 非空： 一般一起用，但是window可插入一个空格，mac则不能插入空格 #联合唯一 #就是给一个以上的字段设置 唯一约束 primary key # 主键 必须唯一 + 非空 加速查询 每张表只能有一个主键 #auto_increment 只有数字类型才能设置自增 #id自增 #当我们以非空并且唯一的约束为创建一个表的时候，如果我们没有指定主键，那么第一个非空唯一的字段将会被设置为主键 #联合主键 #就是给一个以上的字段设置，唯一非空的主键 foreign key # 外键 Innob独有 # 外键约束 #外表中的一个唯一的字段 #on delete cascade #on ipdate cascade
mysql> create table t12 (id int primary key,name varchar(20) not null,phone char(11) not null unique); # Query OK, 0 rows affected (0.20 sec) # mysql> desc t12; # +-------+-------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+-------------+------+-----+---------+-------+ # | id | int(11) | NO | PRI | NULL | | # | name | varchar(20) | NO | | NULL | | # | phone | char(11) | NO | UNI | NULL | | # +-------+-------------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # mysql> insert into t12 (id,name) values (1,'alex'); # Query OK, 1 row affected, 1 warning (0.03 sec) # mysql> select * from t12; # +----+------+-------+ # | id | name | phone | # +----+------+-------+ # | 1 | alex | | # +----+------+-------+ # 1 row in set (0.00 sec) # mysql> insert into t12 (id,name) values (2,'egon'); # ERROR 1062 (23000): Duplicate entry '' for key 'phone' mysql> insert into t12 (id,name,phone) values (2,'egon','13434345678'); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t12; # +----+------+-------------+ # | id | name | phone | # +----+------+-------------+ # | 1 | alex | | # | 2 | egon | 13434345678 | # +----+------+-------------+ # 2 rows in set (0.00 sec) # 不能输入重复的值 mysql> insert into t12 (name,phone) values ('egon','13434345678'); # ERROR 1062 (23000): Duplicate entry '13434345678' for key 'phone' mysql> insert into t12 (name,phone) values ('egon','13434345679'); # Query OK, 1 row affected, 1 warning (0.02 sec) mysql> select * from t12; # +----+------+-------------+ # | id | name | phone | # +----+------+-------------+ # | 0 | egon | 13434345679 | # | 1 | alex | | # | 2 | egon | 13434345678 | # +----+------+-------------+ # 3 rows in set (0.00 sec) # 主键也不能重复 mysql> insert into t12 (name,phone) values ('egon','13434345677'); # ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY' # 创建部门表 mysql> create table department (id int primary key auto_increment,dep_name varchar(20) not null); # Query OK, 0 rows affected (0.16 sec) # 创建员工表 外键关联部门表中的id字段 mysql> create table staff (sid int primary key auto_increment,sname varchar(20) not null,dep_id int, foreign key(dep_id) references department(id)); # Query OK, 0 rows affected (0.21 sec) mysql> desc staff; # +--------+-------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +--------+-------------+------+-----+---------+----------------+ # | sid | int(11) | NO | PRI | NULL | auto_increment | # | sname | varchar(20) | NO | | NULL | | # | dep_id | int(11) | YES | MUL | NULL | | # +--------+-------------+------+-----+---------+----------------+ # 3 rows in set (0.01 sec) # mysql> show create table staff; # +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ # | Table | Create Table | # +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ # | staff | CREATE TABLE `staff` ( # `sid` int(11) NOT NULL AUTO_INCREMENT, # `sname` varchar(20) NOT NULL, # `dep_id` int(11) DEFAULT NULL, # PRIMARY KEY (`sid`), # KEY `dep_id` (`dep_id`), # CONSTRAINT `staff_ibfk_1` FOREIGN KEY (`dep_id`) REFERENCES `department` (`id`) # ) ENGINE=InnoDB DEFAULT CHARSET=utf8 | # +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ # 1 row in set (0.00 sec)
# 如果我们没有指定主键，那么第一个非空唯一的字段将会被设置成主键 mysql> create table t13 (id int unique not null); # Query OK, 0 rows affected (0.15 sec) # mysql> desc t13; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id | int(11) | NO | PRI | NULL | | # +-------+---------+------+-----+---------+-------+ # 1 row in set (0.01 sec) # # # 非空 + 唯一约束不能插入空值 mysql> insert into t13 values (null); # ERROR 1048 (23000): Column 'id' cannot be null mysql> create table t14 (id1 int unique not null,id2 int unique not null); # Query OK, 0 rows affected (0.17 sec) # mysql> desc t14; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id1 | int(11) | NO | PRI | NULL | | # | id2 | int(11) | NO | UNI | NULL | | # +-------+---------+------+-----+---------+-------+ # 2 rows in set (0.01 sec) # # # 主键不能为空值 mysql> insert into t14 (id1) values (1); # ERROR 1364 (HY000): Field 'id2' doesn't have a default value # 指定主键之后 其他的非空 + 唯一约束都不会再成为主键 mysql> create table t15 (id1 int unique not null,id2 int primary key); # Query OK, 0 rows affected (0.23 sec) # mysql> desc t15; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id1 | int(11) | NO | UNI | NULL | | # | id2 | int(11) | NO | PRI | NULL | | # +-------+---------+------+-----+---------+-------+ # 2 rows in set (0.01 sec) # mysql> insert into t15 values (1,2); # Query OK, 1 row affected (0.03 sec) # mysql> insert into t15 values (2,3); # Query OK, 1 row affected (0.02 sec) # mysql> insert into t15 values (4,4); # Query OK, 1 row affected (0.03 sec) # mysql> insert into t15 values (4,5); # ERROR 1062 (23000): Duplicate entry '4' for key 'id1' # 设置联合主键 mysql> create table t16 (id int,ip char(15),port int ,primary key(ip,port)); # Query OK, 0 rows affected (0.15 sec) # mysql> desc t16; # +-------+----------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+----------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | ip | char(15) | NO | PRI | | | # | port | int(11) | NO | PRI | 0 | | # +-------+----------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # mysql> insert into t16 values (1,'192.168.0.1','9000'); # Query OK, 1 row affected (0.02 sec) # mysql> insert into t16 values (1,'192.168.0.1','9001'); # Query OK, 1 row affected (0.03 sec) # mysql> insert into t16 values (1,'192.168.0.2','9000'); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t16 # -> ; # +------+-------------+------+ # | id | ip | port | # +------+-------------+------+ # | 1 | 192.168.0.1 | 9000 | # | 1 | 192.168.0.1 | 9001 | # | 1 | 192.168.0.2 | 9000 | # +------+-------------+------+ # 3 rows in set (0.00 sec) # mysql> insert into t16 values (2,'192.168.0.2','9000'); # ERROR 1062 (23000): Duplicate entry '192.168.0.2-9000' for key 'PRIMARY' # 设置ip和port两个字段联合唯一 mysql> create table t17 (id int primary key,ip char(15) not null ,port int not null, unique(ip,port)); # Query OK, 0 rows affected (0.17 sec) # mysql> desc t17; # +-------+----------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+----------+------+-----+---------+-------+ # | id | int(11) | NO | PRI | NULL | | # | ip | char(15) | NO | MUL | NULL | | # | port | int(11) | NO | | NULL | | # +-------+----------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # mysql> insert into t17 values (1,'192.168.0.1',9000); # Query OK, 1 row affected (0.03 sec) # mysql> insert into t17 values (2,'192.168.0.1',9000); # ERROR 1062 (23000): Duplicate entry '192.168.0.1-9000' for key 'ip' mysql> insert into t17 values (2,'192.168.0.1',9001); # Query OK, 1 row affected (0.03 sec) mysql> insert into t17 values (3,'192.168.0.2',9001); # Query OK, 1 row affected (0.03 sec) # default 设置默认值 mysql> create table t18 (id int primary key auto_increment,name varchar(20) not null,sex enum('male','female') # -> not null default 'male'); # Query OK, 0 rows affected (0.15 sec) # mysql> desc t18; # +-------+-----------------------+------+-----+---------+----------------+ # | Field | Type | Null | Key | Default | Extra | # +-------+-----------------------+------+-----+---------+----------------+ # | id | int(11) | NO | PRI | NULL | auto_increment | # | name | varchar(20) | NO | | NULL | | # | sex | enum('male','female') | NO | | male | | # +-------+-----------------------+------+-----+---------+----------------+ # 3 rows in set (0.01 sec) # mysql> insert into t18 (name) values ('alex'); # Query OK, 1 row affected (0.03 sec) # mysql> select * from t18; # +----+------+------+ # | id | name | sex | # +----+------+------+ # | 1 | alex | male | # +----+------+------+ # 1 row in set (0.00 sec) # mysql> insert into t18 (name) values ('egon'),('yuan'),('nazha'); # Query OK, 3 rows affected (0.02 sec) # Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from t18; # +----+-------+------+ # | id | name | sex | # +----+-------+------+ # | 1 | alex | male | # | 2 | egon | male | # | 3 | yuan | male | # | 4 | nazha | male | # +----+-------+------+ # 4 rows in set (0.00 sec) # mysql> insert into t18 (name,sex) values ('yanglan','female'); # Query OK, 1 row affected (0.03 sec) mysql> select * from t18; # +----+---------+--------+ # | id | name | sex | # +----+---------+--------+ # | 1 | alex | male | # | 2 | egon | male | # | 3 | yuan | male | # | 4 | nazha | male | # | 5 | yanglan | female | # +----+---------+--------+ # 5 rows in set (0.00 sec) # 外键 只有另一个表中设置了unique的字段才能作为本表的外键 mysql> create table t20 (id int,age int,t19_id int,foreign key(t19_id) references t19(id)); # ERROR 1215 (HY000): Cannot add foreign key constraint mysql> create table t21 (id int unique ,name varchar(20)); # Query OK, 0 rows affected (0.17 sec) # mysql> create table t20 (id int,age int,t_id int,foreign key(t_id) references t21(id)); # Query OK, 0 rows affected (0.21 sec) # mysql> insert into t21 values (1,'python'),(2,'linux'); # Query OK, 2 rows affected (0.02 sec) # 如果一个表找中的字段作为外键对另一个表提供服务，那么默认不能直接删除外表中正在使用的数据 mysql> insert into t20 values (1,18,1); # Query OK, 1 row affected (0.02 sec) # mysql> insert into t20 values (2,38,2); # Query OK, 1 row affected (0.02 sec) # mysql> select * from t21; # +------+--------+ # | id | name | # +------+--------+ # | 1 | python | # | 2 | linux | # +------+--------+ # 2 rows in set (0.00 sec) mysql> delete from t21 where id=1; # ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`db2`.`t20`, CONSTRAINT `t20_ibfk_1` FOREIGN KEY (`t_id`) REFERENCES `t21` (`id`)) # 外键 on delete cascade on update cascade mysql> create table course (cid int primary key auto_increment,cname varchar(20) not null, -> cprice int not null); # Query OK, 0 rows affected (0.16 sec) # mysql> create table student (sid int primary key auto_increment, -> sname varchar(20) not null, -> age int not null, -> course_id int, -> foreign key(course_id) -> references course(cid) -> on delete cascade -> on update cascade); # Query OK, 0 rows affected (0.17 sec) # mysql> insert into course (cname,cprice) values ('python',19800),('linux',15800); # Query OK, 2 rows affected (0.03 sec) # Records: 2 Duplicates: 0 Warnings: 0 # mysql> insert into student (sname,age,course_id) values ('yangzonghe',18,1),('hesihao',88,2); # Query OK, 2 rows affected (0.03 sec) # Records: 2 Duplicates: 0 Warnings: 0 # mysql> delete from course where cid = 1; # Query OK, 1 row affected (0.03 sec) # mysql> select * from student; # +-----+---------+-----+-----------+ # | sid | sname | age | course_id | # +-----+---------+-----+-----------+ # | 2 | hesihao | 88 | 2 | # +-----+---------+-----+-----------+ # 1 row in set (0.00 sec) # mysql> update course set cid = 1 where cid = 2; # Query OK, 1 row affected (0.03 sec) # Rows matched: 1 Changed: 1 Warnings: 0 # mysql> select * from course; # +-----+-------+--------+ # | cid | cname | cprice | # +-----+-------+--------+ # | 1 | linux | 15800 | # +-----+-------+--------+ # 1 row in set (0.00 sec) mysql> select * from student; # +-----+---------+-----+-----------+ # | sid | sname | age | course_id | # +-----+---------+-----+-----------+ # | 2 | hesihao | 88 | 1 | # +-----+---------+-----+-----------+ # 1 row in set (0.00 sec)
# 修改表名 mysql> show tables; # +---------------+ # | Tables_in_db2 | # +---------------+ # | course | # | department | # | staff | # | staff_info | # | student | # | t1 | # | t10 | # | t11 | # | t12 | # | t13 | # | t14 | # | t15 | # | t16 | # | t17 | # | t18 | # | t19 | # | t2 | # | t20 | # | t21 | # | t3 | # | t4 | # | t5 | # | t6 | # | t7 | # | t8 | # | t9 | # +---------------+ # 26 rows in set (0.00 sec) # mysql> alter table t7 rename t77; # Query OK, 0 rows affected (0.13 sec) # mysql> show tables; # +---------------+ # | Tables_in_db2 | # +---------------+ # | course | # | department | # | staff | # | staff_info | # | student | # | t1 | # | t10 | # | t11 | # | t12 | # | t13 | # | t14 | # | t15 | # | t16 | # | t17 | # | t18 | # | t19 | # | t2 | # | t20 | # | t21 | # | t3 | # | t4 | # | t5 | # | t6 | # | t77 | # | t8 | # | t9 | # +---------------+ # 26 rows in set (0.00 sec) mysql> desc t20; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | age | int(11) | YES | | NULL | | # | t_id | int(11) | YES | MUL | NULL | | # +-------+---------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # 增加name字段 mysql> alter table t20 add name varchar(20) not null; # Query OK, 0 rows affected (0.41 sec) # Records: 0 Duplicates: 0 Warnings: 0 mysql> desc t20; # +-------+-------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+-------------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | age | int(11) | YES | | NULL | | # | t_id | int(11) | YES | MUL | NULL | | # | name | varchar(20) | NO | | NULL | | # +-------+-------------+------+-----+---------+-------+ # 4 rows in set (0.01 sec) # 删除name字段 mysql> alter table t20 drop name; # Query OK, 0 rows affected (0.70 sec) # Records: 0 Duplicates: 0 Warnings: 0 # mysql> desc t20; # +-------+---------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+---------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | age | int(11) | YES | | NULL | | # | t_id | int(11) | YES | MUL | NULL | | # +-------+---------+------+-----+---------+-------+ # 3 rows in set (0.01 sec) # 修改字段的位置 mysql> alter table t20 add name varchar(20) not null after id; # Query OK, 0 rows affected (0.27 sec) # Records: 0 Duplicates: 0 Warnings: 0 # mysql> desc t20; # +-------+-------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+-------------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | name | varchar(20) | NO | | NULL | | # | age | int(11) | YES | | NULL | | # | t_id | int(11) | YES | MUL | NULL | | # +-------+-------------+------+-----+---------+-------+ # 4 rows in set (0.01 sec) # # 修改字段的约束 mysql> alter table t20 modify name char(20) not null after age; # Query OK, 2 rows affected (0.48 sec) # Records: 2 Duplicates: 0 Warnings: 0 mysql> desc t20; # +-------+----------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +-------+----------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | age | int(11) | YES | | NULL | | # | name | char(20) | NO | | NULL | | # | t_id | int(11) | YES | MUL | NULL | | # +-------+----------+------+-----+---------+-------+ # 4 rows in set (0.01 sec) # 修改字段名和约束 mysql> alter table t20 change name new_name varchar(20) not null after id; # Query OK, 2 rows affected (0.49 sec) # Records: 2 Duplicates: 0 Warnings: 0 mysql> desc t20; # +----------+-------------+------+-----+---------+-------+ # | Field | Type | Null | Key | Default | Extra | # +----------+-------------+------+-----+---------+-------+ # | id | int(11) | YES | | NULL | | # | new_name | varchar(20) | NO | | NULL | | # | age | int(11) | YES | | NULL | | # | t_id | int(11) | YES | MUL | NULL | | # +----------+-------------+------+-----+---------+-------+ # 4 rows in set (0.01 sec)