数据库基础二
存储引擎
- InnoDB 支持事务，外键，行级锁
事务：一个最小的不可再分的工作单元；通常一个事务对应一个完整的业务
事务四大特性：
1.原子性(A)：事务是最小单位，不可再分 2.一致性(C)：事务要求所有的DML语句操作的时候， 必须保证同时成功或者同时失败 3.隔离性(I)：事务A和事务B之间具有隔离性 4.持久性(D)：是事务的保证，事务终结的标志(内存的数据持久到硬盘文件中)
关于事务的一些术语
开启事务：Start Transaction 事务结束：End Transaction 提交事务：Commit Transaction 回滚事务：Rollback Transaction
-
MyIsam 表级锁，支持全文索引
只查询多，修改少的时候使用
myisam，同一张表中的数据不能被同时修改 -
memory 每个用户的登录状态
数据都存在内存中，读取速度快
数据量小 并对服务器的内存有要求
断电消失 -
blackhole 黑洞
放进去的所有数据都不存
sql语句
- 创建表
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)
- 查看表结构
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 | |
+-------+-----------------------+------+-----+---------+-------+
- 插入数据
指定列名插入数据
mysql> insert into staff_info (id,name,age,sex,phone,job) values (1,'aa',83,'female','13651054608','IT');
Query OK, 1 row affected (0.02 sec)
插入单行数据
mysql> insert into staff_info values (2,'xm',82,'female','13651054608','IT');
Query OK, 1 row affected (0.02 sec)
插入多行数据
mysql> insert into staff_info values
-> (2,'ww',26,'male','13304320533','Tearcher'),
-> (3,'ls',25,'male','13332353222','IT'),
-> (4,'zs',40,'male','13332353333','IT');
- 查询数据
# 查看所有列的数据
# mysql> select * from staff_info;
# +------+----------+------+--------+-------------+----------+
# | id | name | age | sex | phone | job |
# +------+----------+------+--------+-------------+----------+
# | 1 | xm | 82 | female | 13651054608 | IT |
# | 2 | ww | 26 | male | 13304320533 | Tearcher |
# | 3 | ls | 25 | male | 13332353222 | IT |
# | 4 | zs | 40 | male | 13332353333 | IT |
# +------+----------+------+--------+-------------+----------+
- 查看指定列的数据
# mysql> select name,age from staff_info;
# +----------+------+
# | name | age |
# +----------+------+
# | xm | 82 |
# | ww | 26 |
# | ls | 25 |
# | zs | 40 |
# +----------+------+
# 4 rows in set (0.00 sec)
数据类型
常用 int float 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 |
# +----------------------------------+----------------------------------+----------------------------------+
时间类型
常用 date 描述年月日 datetime 描述年月日时分秒
- timestamp字段默认不为空
# 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)
#
- datetime 和 timestamp的范围控制
# 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>
# 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)
字符串类型
CHAR 0-255字节 定长字符串
# 定长 浪费磁盘 存取速度非常快
VARCHAR 0-65535 字节 变长字符串
# 变长 节省磁盘空间 存取速度相对慢
char(5) ' abc' 5 'abcde' 5
# 这一列数据的长度变化小 手机号 身份证号 学号
# 频繁存取、对效率要求高
# 短数据
varchar(5) '3abc' 4 '5abcde' 6
# 这一列的数据长度变化大 name 描述信息
# 对效率要求相对小
# 相对长
# 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)
枚举和集合类型
枚举 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 非空 unique 唯一 : 唯一可以有一个空 唯一 + 非空 primary key 主键 唯一+非空 加速查询 每张表只能有一个主键 foreign key 外键 Innodb
# primary key
# 唯一+非空 每张表只能有一个主键
# not null 非空
# unique 唯一 : 唯一可以有一个空
# not null + 唯一
# 有一个可以为空了
# 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)
完整性约束补充
not null 非空
# default 默认值
# 如果不输入就是用默认的实质
unique 唯一 : 唯一可以有一个空
# auto_increment 只有数字类型才能设置自增
# 联合唯一
# 就是给一个以上的字段设置 唯一约束
primary key 主键 唯一+非空 加速查询 每张表只能有一个主键
# 当我们以非空并且唯一的约束来创建一个表的时候，
# 如果我们没有指定主键，那么第一个非空唯一的字段将会被设置成主键
# 联合主键
# 就是给一个以上的字段设置 唯一非空约束
foreign key 外键 Innodb
# 外表中的一个唯一字段
# on delete cascade
# on update cascade
# course : cid,cname,cprice
# student : sid,sname,age,course_id
# 如果我们没有指定主键，那么第一个非空唯一的字段将会被设置成主键
# 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>
# 设置联合主键
# 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'
# mysql>
# 设置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)
表结构的修改
语法：
1. 修改表名
ALTER TABLE 表名
RENAME 新表名;
2. 增加字段
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…],
ADD 字段名 数据类型 [完整性约束条件…];
3. 删除字段
ALTER TABLE 表名
DROP 字段名;
4. 修改字段
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名
CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];
5.修改字段排列顺序/在增加的时候指定字段位置
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
ADD 字段名 数据类型 [完整性约束条件…] AFTER 字段名;
ALTER TABLE 表名
CHANGE 字段名 旧字段名 新字段名 新数据类型 [完整性约束条件…] FIRST;
ALTER TABLE 表名
MODIFY 字段名 数据类型 [完整性约束条件…] AFTER 字段名;