python学习day42笔记
约束条件
unsigned
作用：设置无符号
tinyint是-128~127，如果设置了unsigned，范围就变成了0~255
例子：
create table t1(id int unsigned,name varchar(4));
insert into t1 values(-10,'egon');
# ERROR 1264 (22003): Out of range value for column 'id' at row 1
# 报错，意思就是必须是正数
用处：如果用不到负数，可以扩大正数方面存储值的范围
zerofill
作用：未填充满时用0填充至满
int默认是十位，如果设置了zerofill，填入没达到十位的数时会在其前方填入0直到填满
例子：
create table t3(id int zerofill);
insert into t3 values(5);
select * from t3;
# +------------+
# | id |
# +------------+
# | 0000000005 |
# +------------+
很少会用到这个方法
not null
作用：填写值不能为空
默认设置如果填入为空时自动填入NULL，设置not null后，填入的值不能为空，可以从desc看到表结构中的Default查看到输入为空时自动填入的值
insert into t1 values();
# 存入值null
create table t4(name varchar(4) not null);
insert into t4 values();
# ERROR 1364 (HY000): Field 'name' doesn't have a default value
# 报错，name格式没存入值
空字符串和null的区别
空字符串和null并不是同一个东西
create table t4(name varchar(4));
insert into t4 values();
# 存入值NULL
insert into t4 values('');
# 无存入值，是一片空
会出现的问题：
存入值时是会显示null和空白的区别的，但是用其他软件查看的时候都显示的是一样的，如果用not null取值的话会有一部分取不出来
default
作用：设置默认值
在某个格式后设置默认值，如果未输入值则直接使用默认值
create table t5(id int,name varchar(16),gender enum('male','female','other') default 'male');
insert into t5 (id,name) values(1,'egon');
# 存入值1,'egon','male'
unique
作用：限制单列里数据的唯一性
设置数据时加上unique后，这一列的每个数据都是独特的，这一列内不能有一样的数据存在
create table t6 (id int,name varchar(16) unique);
insert into t6 values(1,'egon');
insert into t6 values(2,'egon');
# ERROR 1062 (23000): Duplicate entry 'egon' for key 'name'
# 报错，你name里已经有egon了
作用：限制多列里数据的唯一性
如下设置unique(格式1,格式2)，如果这两个一起重复了就不能存入，如果只是单个重复了则可以存入
create table t7(id int,host varchar(32),port int,unique(host,port));
insert into t7 values(1,'127.0.0.1',3306);
insert into t7 values(1,'127.0.0.1',3307);
insert into t7 values(1,'127.0.0.1',3306);
# ERROR 1062 (23000): Duplicate entry '127.0.0.2-3306' for key 'host'
# 报错，'127.0.0.2-3306'这两个值共同存在了
primary key
主键
1.从限制角度来说，主键相当于非空且唯一
id int primary key == id int not null unique
create table t8(id int primary key);
insert into t8 values();
# ERROR 1364 (HY000): Field 'id' doesn't have a default value
# 没有默认值就直接报错，因为primary有非空的特性
insert into t8 values(1);
insert into t8 values(1);
# ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
# primary条目的1重复，有唯一性
2.InnoDB存储引擎规定一张表中必须有一个主键
# 为什么之前的创建表没有指定主键也能创建成功？
InnoDB引擎自动用一个隐藏的字段创建了一个主键，隐藏意味着看不到摸不着，也不能用。
3.主键的功能
查询速度快：主键本质也是一种索引
auto_increment
给主键使用的，自增，每次增加1
create table t9(id int primary key auto_increment,name varchar(16));
desc t9;
# +-------+-------------+------+-----+---------+----------------+
# | Field | Type | Null | Key | Default | Extra |
# +-------+-------------+------+-----+---------+----------------+
# | id | int(11) | NO | PRI | NULL | auto_increment |
# | name | varchar(16) | YES | | NULL | |
# +-------+-------------+------+-----+---------+----------------+
insert into t9(name) values('egon');
# 存入id默认自增，初始为0，写一个增加1
select * from t9;
# +----+------+
# | id | name |
# +----+------+
# | 1 | egon |
# +----+------+
结论：id类字段的标准写法↓
id int primary key auto_crement
insert into t9(name) values('tom');
delete from t9 where id = 2;
insert into t9(name) values('tom');
select * from t9;
# +----+------+
# | id | name |
# +----+------+
# | 1 | egon |
# | 3 | tom |
# +----+------+
中间自增的主键不会因为少了一个id就重新使用这个id，会一直往下自增
清空表数据
1.delete from t9; # 没有指定则全部清空
select * from t9;
# Empty set (0.00 sec)
# 里面没有值
2.truncate table t9; # 也是全部清空，只不过更加彻底
select * from t9;
# Empty set (0.00 sec)
# 里面没有值
insert into t9(name) values('egon');
select * from t9;
# +----+------+
# | id | name |
# +----+------+
# | 1 | egon |
# +----+------+
# 会连同自增一起清空
# 以后清空表数据，更推荐使用truncate
'''binlog 恢复数据(运维掌握，我们可会可不会)'''
外键
外键前期准备
例子：
公司里人员的表格，字段分别有：员工id，姓名，所处部门，负责的工作
如果公司里有1000个人在同一个部门内，而这个部门改名了且换了工作，此时公司人员的大表格则需要每个人的所处部门和负责的工作都进行修改
此时就可以使用外键，将部门和负责的工作从公司人员的大表格中分离开来，单独组成一个表格，然后公司人员的大表格中就可以直接输入与部门工作相对应的id进行对接，如果有部门的改动就只需要改动部门工作相关的表格就可以全部改动了
需要了解的是：
人员与部门之间的关系是，人员只能有一个部门，部门可以有多个人员，所以这是一对多的关系
在数据表中一般不存中文
表关系判断
一对一
一对多
多对多
'''一对多'''
站在不同的角度去判断
1.一个部门可以有多个用户 # 可以
2.一个用户可以有多个部门 # 不可以
结论：只要是一个可以，一个不可以，那他们就是一对多的关系
为什么不是多对一，因为MySQL关系表中没有多对一的说法
'''多对多'''
站在不同的角度去判断
1.一本书可以有多个作者 # 可以
2.一个作者可以写多本书 # 可以
结论：只要两个都可以，那他们就是多对多的关系
'''一对一'''
站在不同的角度去判断
1.一个人可以有多个个人信息 # 不可以
2.一个个人信息可以是多个人 # 不可以
结论：只要两个都不可以，那他们就是一对一的关系
SQL语句实现表关系
关键字：
foreign key(关联的z名) references 被关联的表(id)
'''
创建带有表关系的表
1.先创建基表
2.再添加外键关系
'''
1.实现一对多，一对多的外键要建在多的一方
create table dep(id int primary key auto_increment,name varchar(32),descript varchar(64));
# 要先创建不带外键的表
create table userinfo(id int primary key auto_increment,username varchar(16),salary decimal(8,2),dep_id int,foreign key(dep_id) references dep(id));
insert into dep values('技术部','搞技术');
insert into dep values('讲师部','讲课');
insert into dep values('外交部','公关');
# 输入也要先输入不带外键的表
insert into userinfo(username,salary,dep_id) values('egon',10000,1);
# 使用的外键id必须是关联的表内有的id，不然报错
2.实现多对多
# 多对多一定要创建第三张表
# 先创建基表
create table book(id int primary key auto_increment,title varchar(16),price int);
create table author(id int primary key auto_increment,name varchar(16),phone int);
# 再创建第三张表进行关联
create table book2author(id int primary key auto_increment,book_id int,author_id int,foreign key (book_id) references author(id)on update cascade on delete cascade, foreign key (author_id) references book(id) on update cascade on delete cascade);
insert into author(name,phone) values('egon',110);
insert into book(title,price) values('egon历险记',100);
insert into book2author(book_id,author_id) values(1,1);
# 将编号1的书与编号1的作者进行绑定
3.实现一对一
# 用于将用户不常用的信息择出来，放入另一个表中，一一对应
# 外键建在任何一个表中都可以，但是推荐建在查询频率高的表中
# 表的id由于只是经过主键限制，所以还是可以重复使用同一个id，需要使用unique使其保证唯一
create table author_detail(id int primary key auto_increment,phone varchar(16));
create table author(id int primary key auto_increment,name varchar(16),author_detail_id int unique,foreign key(author_detail_id) references author_detail(id));
级联更新级联删除
关键字：
on update cascade # 级联更新
on delete cascade # 级联删除
不带外键的表的id不能删除、修改，如果非要修改，只能一荣共荣一损俱损，一起消失或一起修改
create table dep_1(id int primary key auto_increment,name varchar(32),descript varchar(64));
create table userinfo_1(id int primary key auto_increment,username varchar(16),salary decimal(8,2),dep_id int,foreign key(dep_id) references dep_1(id) on update cascade on delete cascade);
insert into dep_1(name,descript) values('技术部','搞技术');
insert into dep_1(name,descript) values('外交部','搞外交');
insert into dep_1(name,descript) values('讲师部','搞讲课');
insert into userinfo_1(username,salary,dep_id) values('egon',10000,3);
update dep_1 set id=200 where id=3;
select * from userinfo_1;
# +----+----------+----------+--------+
# | id | username | salary | dep_id |
# +----+----------+----------+--------+
# | 1 | egon | 10000.00 | 200 |
# +----+----------+----------+--------+
查询关键字之where
1.查找id大于等于3小于等于6的数据
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;
# 关键字between，顾头也顾尾
2.查询薪资是20000或者是18000或者是17000的
select * from emp where salary = 20000 or salary = 17000 or salary = 18000;
select * from emp where salary in (20000,17000,18000);
# 关键词in，查找的值满足in后面括号的条件
# 题外话，not可以取反，所以not in就是查找不满足后面括号的条件
# not in不走索引
'''
模糊查询
关键字
like
关键符号
%:匹配任意个数的任意字符
_:匹配单个个数的任意字符
'''
3.查询姓名中带有字母o的员工姓名和薪资
select name,salary from emp where name like '%o%'；
# 两个%的解释：如果前面带%表示是o结尾的，在后面带%表示是o开头的，两头都加表示任意一个地方带有o都可以查询
# like后开头带%，不走索引
4.查询姓名由四个字符组成的员工姓名和薪资
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
# 内置函数char_length，相当于python中给字符串专用的len
5.查询id小于3或者大于6的数据
select * from emp where id<3 or id>6;
select * from emp where id not between 3 and 6;
6.查询薪资不是20000或者是18000或者是17000的数据
select * from emp where salary not in (20000,17000,18000);
7.查询岗位描述为空的员工名与岗位名
# null不能用等号，只能用is
select * from emp where post_comment is null;
# 需要注意，null和空字符串并不是一回事，两者不一样，查null查不到空字符串，反之亦如此
查询关键字之group by分组
分组：将单个的个体按照指定的条件分成一个个整体
'''
分组之后默认只能直接获取到分组的依据
其他字段无法再直接获取(可以间接获取)
就是说只能直接获取到你按照什么分组的，不能直接获取到其他字段
'''
1.查询每个部门的最高薪资
select name,post,max(salary) from emp group by post;
2.查询每个部门的最低薪资
select name,post,min(salary) from emp group by post;
3.查询每个部门的平均薪资
select name,post,avg(salary) from emp group by post;
4.查询每个部门的人数
select post,count(post) from emp group by post;
5.每个部门的月工资总和
select post,sum(salary) from emp group by post;
'''
可以给字段起别名(as还可以给表起别名)
1.在字段后+as+空格+'别名'
2.在字段后+空格+'别名' # 不建议使用
select post,sum(salary) as '月工资总和' from emp group by post;
'''
聚合函数
分组之后频繁需要使用的
max() # 最大值
min() # 最小值
sum() # 求和
count() # 计数
avg() # 平均值
concat() # 拼接
group_concat() # 分组之后拼接
concat_ws() # 将除第一个字符以外的拼接，第一个字符卡在每个字符中间
学习用数据
create table emp(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);
#插入记录
#三个部门：教学，销售，运营
insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部
('tom','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tony','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jack','female',18,'20110211','teacher',9000,401,1),
('jenny','male',18,'19000301','teacher',30000,401,1),
('sank','male',48,'20101111','teacher',10000,401,1),
('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('呵呵','female',38,'20101101','sale',2000.35,402,2),
('西西','female',18,'20110312','sale',1000.37,402,2),
('乐乐','female',18,'20160513','sale',3000.29,402,2),
('拉拉','female',28,'20170127','sale',4000.33,402,2),
('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3);
https://www.wanxiangsucai.com/read/CV9902
