##### 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 = '技术')； #第二种直接将两步拼接起来
浙公网安备 33010602011771号