##### 10.3.2.2 约束 约束： - not null 某一个字段不能为空 - default 给某个字段设置默认值 - unique 设置一个字段不能重复 - auto_increment 设置某一个int类型的字段 自动增加 - primary key 设置一个字段非空且不能重复 - 外键关联的那张表中的字段必须unique - 级联操作：on update cascade on delete cascade - foreign key 外键 - unsigned 只能是正数 not null和default： ```python create table t1( id int not null, name char(8) not null, gender enum('male','female') not null default 'male' ); ``` unique： ```python create table t2( id int unique, username char(4) unique, password char(8) not null ); ``` 联合唯一 ```python create table t3( in int, ip char(15), server char(15), port int, unique(ip,port) ); ``` 自增 auto_increment - 自增字段必须是数字且必须是唯一的 ```python create table t4( id int unique auto_increment, username char(4), password char(4) ); ``` primary key 主键 - 一张表只能设置一个主键 - 一张表最好设置一个主键（默认规范） - 约束这个字段非空（not null）且唯一（unique） ```python create table t5( id int(4) not null unique, #第一个非空且唯一的字段会被默认定义为主键 name char(12) not null unique )； ``` ```python #手动设置 create table t5( id int(4) primary key, #第一个非空且唯一的字段会被默认定义为主键 name char(12) not null unique )； ``` 联合主键 ```python create table t3( in int, ip char(15), server char(15), port int, unique(ip,port), primary key(id,ip) ); ``` 外键 foreign key 涉及到两张表 ```python create table t6( id int primary key auto_increment, age int, gender enum('male','female'), salary float(8,2), hire date date, post_id int, foreign key (post_id) references post(pid) ); #关联的键是不允许修改和删除的 create table pid( pid int primary key, phone cahr(11) ); ``` 级联删除和级联更新 ```python create table t6( id int primary key auto_increment, age int, gender enum('male','female'), salary float(8,2), hire date date, post_id int, foreign key (post_id) references post(pid) on update cascade on delete cascade ); #现在就可以一起删除个更新了 create table pid( pid int primary key, phone cahr(11) ); ``` ##### 10.3.2.3 修改表 什么时候用表修改： - 创建项目之前 - 项目开发、运行过程中 alter table 表名 add 添加字段 - alter table 表名 add 字段名 数据类型（宽度） 约束 alter table 表名 drop 删除字段 - alter table 表名 drop 字段名 alter table 表名 modify 修改已经存在的字段的宽度、约束 - alter table 表名 modify name char(4) not null alter table 表名 change 修改已经存在的字段的类型宽度、约束和字段名字 - alter table 表名 change name new_name varchar(12) not null 调整字段名位置 - alter table 表名 modify age int not null after id - alter table 表名 modify age int not null first - alter table 表名 modify name char(4) not null first/after name #创建的时候设置添加的位置 两张表中的数据之间的关系： 多对一： - 多个学生都是同一个班级 - 学生版表 关联 班级表 - 学生是多 班级是一 一对一： - 客户关系表：手机号 招生老师 上次联系的时间 备注信息 - 学生表：姓名 入学日期 缴费日期 结业 - 后出现的一张表中的数据作为外键，并且要约束这个外键是唯一的 多对多： 产生第三张表，把两个关联关系的字段作为第三张表的外键 - 书 - 作者 #### 10.3.3 数据的增删改查 增删改查： - 增加 insert - insert into 表名 values(值。。。。); - insert into 表名(字段名，字段名) values(值。。。。); - insert into 表名(字段名，字段名) values(值。。。。),(值。。。。) #写入多行数据; - 删除 delete - delete from 表名 where 条件; - 修改 update - update 表名 set 字段=新的值 where 条件; - 查询 select - select * from 表; - select 字段,字段.. from 表; - select distinct 字段,字段.. from 表; #按查出来的字段去重 - select 字段*5 from 表; #做运算 - select 字段*5 as 新名字 from 表; #对运算后的结果重新命名 #### 10.3.4 单表查询 where语句 比较运算 > < <= >= != <> ```python select * from 表名 where 字段名>1000 or 字段名=100 ``` 范围筛选： - 多选一 - select * from employee where 字段名 in (100,200,4000)； - 在一个模糊的范围内 - 在一个数值区间里 - select * from employee where salary between 1000 and 20000； - 字符串的模糊查询 - select * from employee where name like '程%' #匹配后面的一个或者多个字符； - select * from employee where name like '程_' #_匹配后面的一个字符，可以多次用； - 正则匹配 regexp - select * from employee where 字段 regexp ''正则表达式''； - 逻辑运算 - 条件拼接 - and - or - not - select * from 表名 where employee（字段名）not in (100,200,4000)； - 身份运算符 关于null is null/is not null - 查看某给字段中的数据是否为null - select * from employee where post_comment is not null #### 10.3.5 单表查询group by 分组 - 会把在group by后面的这个字段，这个字段中的每一个不同的项都保存下来 - 并且把这一项的值归为一组，只显示该分组的第一个 聚合： - count(字段) 统计这个字段有多少项 - sum(字段) 统计这个字段对应点的数值的和 - avg(字段) 统计这个字段对应的数值的平均值 - min(字段) - max(字段) 分组聚合： - 应用场景：统计各个部门的人数 - select post,count(*) from employee group by post #select后面跟着哪个字段就会显示哪个字段的内容 #### 10.3.6 单表查询having语句 和order by - 过滤 组 #过滤总是跟group一起用的 - select post from employee group by post having count(*)>3 #找人数大于3的部门 - 执行顺序：先分组 在筛选 order by 排序： - select * from employee order by age； #升序 - select * from employee order by age desc； #降序 - select * from employee order by age，salary desc； #先以age升序，在age相同的情况下依照salary降序 - select * from employee order by age limit 3； #取年龄最小的三个 - select * from employee order by age limit 10,5； #从第10个开始，取五5个 ### 10.4 pymysql ```python import pymysql conn = pymysql.connect(host='127.0.0.1',user='root',password='123',database='day40') cur = conn.cursor(pymysql.cursors.DictCursor) #数据库操作符（游标） 括号里的可以帮助生成字典 cur.execute('insert into employee(emp_name,sex,age,hire_date)' 'values ("刘佳"，“，"male",40,20190808)') #这边的双引号一定要注意 ret = cur.fetchone() #取一个 ret = cur.fetchmany(5) #取五个 ret = cur.fetchalll() #取所有 print(ret) conn.commit() conn.close() ``` ### 10.5 多表查询 两张表是怎么连在一起的 - select * from emp,department; 连表查询(效率高)： - 连接的语法：select 字段 from 表1 xxx join 表2 on 表1.字段 = 表2.字段； - 把两张表连在一起查询 - 内连接 inner join - select * from emp inner join department on emp.dep_id = department.id; #两个表条件不匹配的项是不会出现在连表当中的 - 外连接 - 左外链接 left join # 永远显示全量的左表中的数据 - select * from emp left join department on emp.dep_id = department.id; - 右外连接 right join 永远显示全量的右表中的数据 - select * from emp right join department on emp.dep_id = department.id; - 全外连接 - select * from emp left join department on emp.dep_id = department.id union select * from emp right join department on emp.dep_id = department.id; 子查询(效率低)： 找到技术部门所有人的姓名 - 先找到部门表技术部门的部门id - select id from department where name = '技术' - 再找到emp表中部门id = 200 - select name from emp where dep_id = 200 #第一种 分两步查询先得到id 在查姓名 - select name from emp where dep_id = (select id from dapartment where name = '技术')； #第二种直接将两步拼接起来