1、对于笔记1的补充
phpstudy中MySQL默认不是严格模式，将Mysql设置成严格模式
打开my.ini，在sql-mode的值中，添加STRICT_TRANS_TABLES
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
2、数据类型
MySQL中的数据类型是强类型
2.1、数值型
1、整型
| 整形 | 占用字节数 | 范围 |
|---|---|---|
| tinyint | 1 | -128~127 |
| smallint | 2 | -32768~32767 |
| mediumint | 3 | -8388608~8388607 |
| int | 4 | -2147483648~2147483647 |
| bigint | 8 | -9223372036854775808~9223372036854775807 |
选择的范围尽可能小，范围越小占用资源越少
mysql> create table stu1(
-> id tinyint, # 范围要尽可能小，范围越小，占用空间越少
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.02 sec)
-- 超出范围会报错（严格模式下）
mysql> insert into stu1 values (128,'tom');
ERROR 1264 (22003): Out of range value for column 'id' at row 1
无符号整型（unsigned）无符号整型就是没有负数，无符号整数是整数的两倍（可查看手册）
mysql> create table stu2(
-> id tinyint unsigned # 无符号整数
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu2 values (128);
Query OK, 1 row affected (0.00 sec)
整形支持显示宽度，显示宽度是最小的显示位数，如int(11)表示整形最少用11位表示，如果不够位数用0填充。显示宽度默认不起作用，必须结合zerofill才起作用
mysql> create table stu4(
-> id int(5),
-> num int(5) zerofill # 添加前导0,int(5)显示宽度是5
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu4 values (12,12);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu4;
+------+-------+
| id | num |
+------+-------+
| 12 | 00012 |
+------+-------+
1 row in set (0.00 sec)
小结：
1、范围要尽可能小，范围越小，占用空间越少
2、无符号整数是整数的两倍
3、整形支持显示宽度，显示宽带是最小的显示位数，必须结合zerofill才起作用
2、浮点数
| 浮点型 | 占用字节数 | 范围 |
|---|---|---|
| float（单精度型） | 4 | -3.4E+38~3.4E+38 |
| double（双精度型） | 8 | -1.8E+308~1.8E+308 |
浮点型的声明：float(M，D) double(M，D)
M:总位数 D:小数位数
例题：
mysql> create table stu5(
-> num1 float(5,2), -- 浮点数
-> num2 double(6,1) -- 双精度数
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu5 values (3.1415,12.96);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu5;
+------+------+
| num1 | num2 |
+------+------+
| 3.14 | 13.0 |
+------+------+
1 row in set (0.00 sec)
MySQL浮点数支持科学计数法
mysql> create table stu6(
-> num float # 不指定位数，默认是小数点后面6位 double默认是17位
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> insert into stu6 values (5E2),(6E-2); # 插入科学计数法
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu6;
+------+
| num |
+------+
| 500 |
| 0.06 |
+------+
2 rows in set (0.00 sec)
浮点数精度会丢失
mysql> insert into stu6 values(99.999999999);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu6;
+------+
| num |
+------+
| 100 |
+------+
小结：
1、浮点数有单精度和双精度
2、浮点数支持科学计数法
3、浮点数精度会丢失
3、小数（定点数）
原理：将整数部分和小数部分分开存储
语法：
decimal(M,D)
mysql> create table stu8(
-> num decimal(20,9) # 存放定点数
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stu8 values(12.999999999);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu8;
+--------------+
| num |
+--------------+
| 12.999999999 |
+--------------+
1 row in set (0.00 sec)
小结：
1、decimal是变长的，大致是每9个数字用4个字节存储，整数和小数分开计算。M最大是65，D最大是30，默认是（10，2）
2、定点和浮点都支持无符号、显示宽度0填充。
2.2 字符型
在数据库中没有字符串概念，只有字符，所以数据库中只能用单引号
| 数据类型 | 描述 |
|---|---|
| char | 定长字符，最大可以到255 |
| varchar | 可变长度字符，最大可以到65535 |
| tinytext | 28–1 =255 |
| text | 216–1 =65535 |
| mediumtext | 224–1 |
| longtext | 232–1 |
char(4)：存放4个字符，中英文一样。
varchar(L)实现变长机制，需要额外的空间来记录数据真实的长度
L的理论长度是65536，但事实上达不到，因为有的字符的多字节字符，所以L达不到65536
text系列的类型在表中存储的是地址，占用大小大约10个字节
一个记录的所有字段的总长度也不能超过65535个字节。
小结：
1、char是定长，var是变长
2、char最大值是255，varchar最大值是65535，具体要看字符编码
3、text系列在表中存储的是地址
4、一条记录的总长度不能超过65535
5、utf8 1字符=3个字节
6、gbk 1字符=2个字节
2.3、枚举（enum）
从集合中选择一个值作为数据（单选）
mysql> create table stu12(
-> name varchar(20),
-> sex enum('男','女','保密') # 枚举
-> );
Query OK, 0 rows affected (0.06 sec)
-- 插入的枚举值只能是枚举中提供的选项
mysql> insert into stu12 values ('tom','男');
Query OK, 1 row affected (0.00 sec)
-- 报错，只能插入男、女、保密
mysql> insert into stu12 values ('tom','不告诉你');
ERROR 1265 (01000): Data truncated for column 'sex' at row 1
枚举值是通过整形数字来管理的，第一个值是1，第二个值是2，以此类推，枚举值在数据库存储的是整形数字。
mysql> insert into stu12 values ('berry',2); -- 插入数字
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu12;
+-------+------+
| name | sex |
+-------+------+
| tom | 男 |
| berry | 女 |
+-------+------+
mysql> select * from stu12 where sex=2; -- 2表示第二个枚举值
+-------+------+
| name | sex |
+-------+------+
| berry | 女 |
+-------+------+
1 row in set (0.00 sec)
枚举优点：
1、限制值
2、节省空间
3、运行速度快（整形比字符串运行速度快）
思考：已知枚举占用两个字节，所以最多可以有多少个枚举值？
答：2字节=16位，216=65536，范围是（0-65535），由于枚举从1开始，所以枚举值最多有65535个
2.4、集合(set)
从集合中选择一些值作为数据（多选）
mysql> create table stu13(
-> name varchar(20),
-> hobby set('爬山','读书','游泳','烫头') -- 集合
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> insert into stu13 values ('tom','爬山');
mysql> insert into stu13 values ('Berry','爬山,游泳');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu13 values ('Berry','游泳,爬山'); -- 插入的顺序不一样，但显示的顺序一样
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu13;
+-------+-----------+
| name | hobby |
+-------+-----------+
| tom | 爬山 |
| Berry | 爬山,游泳 |
| Berry | 爬山,游泳 |
+-------+-----------+
3 rows in set (0.00 sec)
集合和枚举一样，也为每个集合元素分配一个固定值，分配方式是从前往后按2的0、1、2、…次方，转换成二进制后只有一位是1，其他都是0。
'爬山','读书','游泳','烫头'
1 2 4 8
mysql> select hobby+0 from stu13;
+---------+
| hobby+0 |
+---------+
| 1 |
| 5 |
| 5 |
+---------+
mysql> insert into stu13 values ('rose',15);
Query OK, 1 row affected (0.00 sec)
已知集合类型占8个字节，那么集合中最多有多少选项
答：有64个选项。
2.5 日期时间型
| 数据类型 | 描述 |
|---|---|
| datetime (掌握) | 日期时间，占用8个字节 |
| date | 日期 占用3个字节 |
| time | 时间 占用3个字节 |
| year | 年份，占用1个字节 |
| timestamp | 时间戳，占用4个字节 |
1、datetime和date
datetime格式：年-月-日 小时:分钟:秒。支持的范围是’1000-01-01 00:00:00’到’9999-12-31
23:59:59’。
mysql> create table stu14(
-> t1 datetime, -- 日期时间
-> t2 date -- 日期
-> );
Query OK, 0 rows affected (0.05 sec)
-- 插入测试数据
mysql> insert into stu14 values ('2019-01-15 12:12:12','2019-01-15 12:12:12');
Query OK, 1 row affected, 1 warning (0.00 sec)
-- 查询
mysql> select * from stu14;
+---------------------+------------+
| t1 | t2 |
+---------------------+------------+
| 2019-01-15 12:12:12 | 2019-01-15 |
+---------------------+------------+
1 row in set (0.00 sec)
2、timestamp（时间戳）
datetime类型和timestamp类型表现上是一样的，他们的区别在于：
datetime从1000到9999，而timestamp从1970年~2038年（原因在于timestamp占用4个字节，和整形的范围一样，2038年01月19日11:14:07以后的秒数就超过了4个字节的长度）
mysql> create table stu15(
-> t1 timestamp
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> insert into stu15 values ('2038-01-19 11:14:07');
Query OK, 1 row affected (0.00 sec)
3、year
只能表示1901~2155之间的年份，因为只占用1个字节，只能表示255个数
mysql> create table stu16(
-> y1 year
-> );
Query OK, 0 rows affected (0.08 sec)
mysql> insert into stu16 values (2155);
Query OK, 1 row affected (0.00 sec)
4、time
可以表示时间，也可以表示时间间隔。范围是：-838:59:59~838:59:59
mysql> create table stu17(
-> t1 time
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> insert into stu17 values ('12:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('212:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('-212:12:12');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu17 values ('839:00:00'); -- 报错
ERROR 1292 (22007): Incorrect time value: '839:00:00' for column 't1' at row 1
-- time支持以天的方式来表示时间间隔
mysql> insert into stu17 values ('10 10:25:25'); -- 10天10小时25分25秒
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu17;
+------------+
| t1 |
+------------+
| 12:12:12 |
| 212:12:12 |
| -212:12:12 |
| 250:25:25 |
+------------+
4 rows in set (0.00 sec)
2.6 Boolean
MySQL不支持布尔型，true和false在数据库中对应的是1和0
mysql> create table stu18(
-> flag boolean
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc stu18;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| flag | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into stu18 values (true),(false);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from stu18;
+------+
| flag |
+------+
| 1 |
| 0 |
+------+
2 rows in set (0.00 sec
小结：
boolean型在MySQL中对应的是tinyint。
2.7 练习题
电话号码一般使用什么数据类型存储? varchar
手机号码用什么数据类型 char
性别一般使用什么数据类型存储? char tinyint enum
年龄信息一般使用什么数据类型存储? tinyint
照片信息一般使用什么数据类型存储? binary
薪水一般使用什么数据类型存储? decimal
3、列属性
3.1 是否为空（null|not null）
null表示字段值可以为null
not null字段值不能为空
练习
学员姓名允许为空吗? not null
家庭地址允许为空吗? not null
电子邮件信息允许为空吗? null
考试成绩允许为空吗? null
3.2 默认值（default）
如果一个字段没有插入值，可以默认插入一个指定的值
mysql> create table stu19(
-> name varchar(20) not null default '姓名不详',
-> addr varchar(50) not null default '地址不详'
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> insert into stu19(name) values ('tom');
Query OK, 1 row affected (0.00 sec)
mysql> insert into stu19 values (default,default);
Query OK, 1 row affected (0.00 sec)
mysql> select * from stu19;
+----------+----------+
| name | addr |
+----------+----------+
| tom | 地址不详 |
| 姓名不详 | 地址不详 |
+----------+----------+
2 rows in set (0.00 sec)
小结：
default关键字用来插入默认值
3.3 自动增长 （auto_increment)
字段值从1开始，每次递增1，自动增长的值就不会有重复，适合用来生成唯一的id。在MySQL中只要是自动增长列必须是主键
3.4 主键 （primary key）
主键概念：唯一标识表中的记录的一个或一组列称为主键
特点：
1、不能重复、不能为空
2、一个表只能有一个主键
作用：
1、保证数据完整性
2、加快查询速度
选择主键的原则
最少性：尽量选择单个键作为主键
稳定性：尽量选择数值更新少的列作为主键
比如：学号，姓名，地址 这三个字段都不重复，选哪个做主键
选学号，因为学号最稳定
练习：
-- 创建主键方法一
mysql> create table stu20(
-> id int auto_increment primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.04 sec)
-- 创建主键方法二
mysql> create table stu21(
-> id int auto_increment,
-> name varchar(20),
-> primary key(id)
-> );
Query OK, 0 rows affected (0.02 sec)
组合键
mysql> create table stu22(
-> classname varchar(20),
-> stuname varchar(20),
-> primary key(classname,stuname) -- 创建组合键
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc stu22;
+-----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| classname | varchar(20) | NO | PRI | | |
| stuname | varchar(20) | NO | PRI | | |
+-----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
通过更改表添加主键
mysql> create table stu23(
-> id int,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
-- 添加主键
mysql> alter table stu23 add primary key(id);
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除主键
mysql> alter table stu23 drop primary key;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
插入数据
mysql> create table stu25(
-> id tinyint unsigned auto_increment primary key,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.05 sec)
-- 插入数据
mysql> insert into stu25 values (3,'tom'); -- 可以直接插入数字
Query OK, 1 row affected (0.06 sec)
-- 自动增长列可以插入null,让列的值自动递增
mysql> insert into stu25 values (null,'berry');
Query OK, 1 row affected (0.00 sec)
小结：
1、只要是auto_increment必须是主键，但是主键不一定是auto_increment
2、主键特点是不能重复不能为空
3、一个表只能有一个主键，但是一个主键可以有多个字段组成
4、自动增长列通过插入null值让其递增
5、自动增长列的数据被删除，默认不再重复使用。truncate table删除数据后，再次插入从1开始
练习
1、 在主键列输入的数值，允许为空吗? 不可以
2、一个表可以有多个主键吗? 不可以
3、在一个学校数据库中，如果一个学校内允许重名的学员，但是一个班级内不允许学员重名，可以组合班级和姓名两个字段一起来作为主键吗？ 对
4、标识列（自动增长列）允许为字符数据类型吗？ 不允许
5、一个自动增长列中，插入3行，删除2行，插入3行，删除2行，插入3行，删除2行，再次插入是多少？ 10
3.5 唯一键（unique）
| 键 | 区别 |
|---|---|
| 主键 | 1、不能重复，不能为空 2、一个表只能有一个主键 |
| 唯一键 | 1、不能重刻，可以为空 2、一个表可以有多个唯一键 |
例题
-- 创建表的时候创建唯一键
mysql> create table stu26(
-> id int auto_increment primary key,
-> name varchar(20) unique -- 唯一键
-> );
Query OK, 0 rows affected (0.05 sec)
-- 方法二
mysql> create table stu27(
-> id int primary key,
-> name varchar(20),
-> unique(name)
-> );
Query OK, 0 rows affected (0.05 sec)
unique 或 unique key 是一样的
通过修改表添加唯一键
-- 将name设为唯一键
mysql> alter table stu28 add unique(name);
-- 将name,addr设为唯一键
mysql> alter table stu28 add unique(name),add unique(addr);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc stu28;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | UNI | NULL | |
| addr | varchar(20) | YES | UNI | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)
通过show create table 查看唯一键的名字
通过唯一键的名字删除唯一键
mysql> alter table stu28 drop index name;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
3.6 备注（comment）
说明性文本
mysql> create table stu29(
-> id int primary key comment '学号',
-> name varchar(20) not null comment '姓名'
-> );
Query OK, 0 rows affected (0.03 sec)
注意：备注属于SQL代码的一部分
4、SQL注释
-- 单行注释
# 单行注释
/*
多行注释
*/
5、数据完整性
5.1 数据完整性包括
1、实体完整性
1、主键约束
2、唯一约束
3、标识列（自动增长）
2、域完整性
1、数据类型约束
2、非空约束
3、默认值约束
3、引用完整性
外键约束
4、自定义完整性
1、存储过程
2、触发器
5.2 主表和从表
- 主表中没有的记录，从表不允许插入
- 从表中有的记录，主表中不允许删除
- 删除主表前，先删除子表
1.5.3 外键（foreign key）
外键：从表中的公共字段
-- 创建表的时候添加外键
drop table if exists stuinfo;
create table stuinfo(
id tinyint primary key,
name varchar(20)
)engine=innodb;
drop table if exists stuscore;
create table stuscore(
sid tinyint primary key,
score tinyint unsigned,
foreign key(sid) references stuinfo(id) -- 创建外键
)engine=innodb;
-- 通过修改表的时候添加外键
语法：alter table 从表 add foreign key(公共字段) references 主表(公共字段)
drop table if exists stuinfo;
create table stuinfo(
id tinyint primary key,
name varchar(20)
)engine=innodb;
drop table if exists stuscore;
create table stuscore(
sid tinyint primary key,
score tinyint unsigned
)engine=innodb;
alter table stuscore add foreign key (sid) references stuinfo(id)
删除外键
通过外键的名字删除外键
-- 删除外键
mysql> alter table stuscore drop foreign key `stuscore_ibfk_1`;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
小结：
1、只有innodb才能支持外键
2、公共字段的名字可以不一样，但是数据类型要一样
5.4 三种外键操作
1、 严格限制（参见主表和从表）
2、 置空操作（set null）：如果主表记录删除，或关联字段更新，则从表外键字段被设置为null。
3、 级联操作（cascade）：如果主表记录删除，则从表记录也被删除。主表更新，从表外键字段也更新。
语法：foreign key (外键字段) references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]。
一般说删除时置空，更新时级联。
drop table if exists stuinfo;
create table stuinfo(
id tinyint primary key comment '学号，主键',
name varchar(20) comment '姓名'
)engine=innodb;
drop table if exists stuscore;
create table stuscore(
id int auto_increment primary key comment '主键',
sid tinyint comment '学号，外键',
score tinyint unsigned comment '成绩',
foreign key(sid) references stuinfo(id) on delete set null on update cascade
)engine=innodb;
小结：
置空、级联操作中外键不能是从表的主键
1100
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
