python学习之老男孩python全栈第九期_数据库day002知识点总结 —— MySQL数据库day2（全部）

一. 复习
1. MySQL：
- 服务端
- 客户端
2. 通信交流
- 授权
- SQL语句
- 数据库
创建数据库：
create database db1 default charset utf8;
删除数据库：
drop database db1;
- 数据表
创建数据表：
create table tb1(
id int not null auto_increment primary key,
name char(10),
depatment_id int,
constraint fk_l foreign key ('department_id') references 表名('tid')
)engine = innodb default charset = utf8;

- 数据行
增：insert into tb1(name,age) values('alex','18');

删：delete from tb1;
delete from tb1 where id > 10
truncate table tb1;

改：update tb1 set name = 'root' where > 10

查：select * from tb; # * 的效率低
select id,name from tb;

二. 外键的补充
1. 主键：
- 一张表 只能有 一个主键
- 一个主键 不一定 是一列

主键可以这样写：
create table t1(

nid int(11) not null auto_increment,
pid int(11) not null,
num int(11) null,
primery key (nid,pid) # 主键有两列，不常用

)engine = innodb default charset = utf8;

# 主键设置成两列，外键就可以关联两列
create table t2(

id int auto_increment primary key,
name varchar(32),
id1 int,
id2 int,
constraint fk_t1_t2 foreign key ('id1','id2') references t1('nid','pid')

)engine = innodb default charset = utf8;

create table t1(

nid int(11) not null auto_increment,
pid int(11) not null,
num int(11) null,
primery key (nid,pid) # 主键有两列，不常用

)engine = innodb default charset = utf8;

三. 自增列之起始值
show create table t1 # 查看怎么创建的（代码）

show create table t1 \G # 查看怎么创建的（代码）

 

create table t1(

nid int(11) not null auto_increment,
pid int(11) not null,
num int(11) null,
primery key (nid,pid) # 主键有两列，不常用

)engine = innodb auto_increment = 4 default charset = utf8; # 设置auto_increment默认值

alter table t1 auto_increment = 1 # 修改自增的初始值

四. 自增列之步长
1. MySQL：自增步长
基于会话级别：
登录一次可以设置一个步长，不能像sqlServer一样（可以在每个表里面设置步长）

show session variables like 'auto_inc%'; # 查看步长（auto_increment_increment）
set session auto_increment_increment = 2; # 设置会话步长
基于全局级别：（尽量不用）
show global variables like 'auto_inc%'; # 查看全局变量
set global auto_increment_increment = 2;
更改之后，打开一个会话就会默认使用更改之后的步长

2. sqlServer：自增步长
基于表级别：
create table t1(

nid int(11) not null auto_increment,
pid int(11) not null,
num int(11) null,
primery key (nid,pid)

)engine = innodb auto_increment = 4 步长 = 2 default charset = utf8;


create table t2(

nid int(11) not null auto_increment,
pid int(11) not null,
num int(11) null,
primery key (nid,pid)

)engine = innodb auto_increment = 4 步长 = 2 default charset = utf8;

五. 唯一索引（约束不能重复（可以有一个值为空），加速查找）

create table t1(
id int ...,
num int,
xx int,
# unique uq1 (num) # num 是唯一的，不允许重复
unique uq1 (num,xx) # num与xx 联合唯一，两个不能都一样
)

PS：
主键 也不能重复，但不能为空

六. 外键的变种

a. 用户表和部门表：(一对多)
用户： 部门id
1 alex 1
2 root 1
3 egon 2
4 laoyao 3
部门：
1 服务
2 保安
3 公关

b. 用户表和博客表:(一对一)
用户：
1 alex
2 root
3 egon
4 laoyao
博客表(最多4个)：
id 博客地址 用户id （FK() + 唯一索引）
1 /alex3714/ 1
2 /yuanchenqi/ 4

一个用户只能有一个博客园：FK() + 唯一索引 进行约束

c. 多对多：
示例1：
用户表(百合网)
相亲记录表
示例2：
用户表
主机表
用户主机关系表

七. SQL语句 数据行 操作补充

create table tb12(
id int not null auto_increment primary key,
name varchar(32),
age int
)engine = innodb default charset = utf8;

1. 增
一个一个增加：insert into tb1 (name,age) values ('alex',11);

增加多个：insert into tb1 (name,age) values ('alex3714',12),('root',18);

把tb1里面的数据添加到tb12中：insert into tb12 (name,age) select name,age from tb1;

2. 删

delete from tb1;
delete from tb1 where id = 2;
delete from tb1 where id != 2;
delete from tb1 where id >= 2;


delete from tb1 where id >= 2 or name = 'alex';

3. 改
update tb1 set name='alex' where id>12 and name='xx';

update tb1 set name='alex',age=19 where id>12 and name='xx';

4. 查

select * from tb1;

select id,name from tb1 where id>10 or name='xxx';

给列名取个别名：select id as nid,name as cname from tb1;
+-----+----------+
| nid | cname |
+-----+----------+
| 1 | alex |
| 2 | alex3714 |
| 3 | root |
+-----+----------+

直接增加一列：select id,name,12 from tb1;
+----+----------+----+
| id | name | 12 |
+----+----------+----+
| 1 | alex | 12 |
| 2 | alex3714 | 12 |
| 3 | root | 12 |
+----+----------+----+

其他：
数据库中不等于有两种：
select id,name from tb1 where id!=10;
select id,name from tb1 where id<>10;

a.条件：
取id=1 or id=5 or id=10:
select * from tb1 where id in (1,5,10);
除了id=1 or id=5 or id=10:
select * from tb1 where id not in (1,5,10);

取5到12区间中的数据（包括5,12）【和python中range(顾头不顾尾)不一样】
select * from tb1 where id between 5 and 12;

select * from tb1 where id in (select id from tb12);

b. 通配符：
a% a后面可以有任意多的字符
a_ a后面只能有一个字符
%a% 中间有a的都能找到

查找name以a结尾的：select * from tb1 where name like '%a';

c. 限制(分页)
查看前10条数据：select * from tb1 limit 10;

查看从第2条数据开始，往后的2条数据：select * from tb1 limit 1,2;
+----+----------+------+
| id | name | age |
+----+----------+------+
| 2 | alex3714 | 12 |
| 3 | root | 18 |
+----+----------+------+


select * from tb1 limit 10 offset 2;
相当于select * from tb1 limit 2,10; (数反过来了)

python中，查看页码(分页)：
page = input('请输入要查看的页码：')
page = int(page)
start_info = (page - 1) * 10
select * from tb1 limit start_info,10;

怎么从后面取10个？ -- 先把顺序倒过来，再取前10
d. 排序：
从小到大排序：select * from tb1 order by id asc;
从大到小排序：select * from tb1 order by id desc;


优先按照age排序，若age相同，则再按id排序：select * from tb1 order by age desc,id desc;


取后面10个数据：select * from tb1 order by id desc limit 10;

e. 分组：

聚合函数：
max、min、count、sum、avg

先创建两个表：
create table department5(
id int auto_increment primary key,
title varchar(32)
)engine=innodb default charset=utf8;

insert into department5(title) values('公关'),('关公'),('关关'),('公公'),('关关共');

create table userinfo5(
id int auto_increment primary key,
name varchar(32),
part_id int,
constraint fk_user_part foreign key (part_id) references department5(id)
)engine=innodb default charset=utf8;

insert into userinfo5(name,part_id) values('alex',3),('egon',1),('wusir',4),('铁锤',2),('小钢炮',2);
根据part_id进行分组：select max(id),part_id from userinfo5 group by part_id;

原来：
+----+--------+---------+
| id | name | part_id |
+----+--------+---------+
| 1 | alex | 3 |
| 2 | egon | 1 |
| 3 | wusir | 4 |
| 4 | 铁锤 | 2 |
| 5 | 小钢炮 | 2 |
+----+--------+---------+
现在：
+---------+---------+
| max(id) | part_id |
+---------+---------+
| 2 | 1 |
| 5 | 2 |
| 1 | 3 |
| 3 | 4 |
+---------+---------+

select count(id),part_id from userinfo5 group by part_id;
每个部门有多少个人
+-----------+---------+
| count(id) | part_id |
+-----------+---------+
| 1 | 1 |
| 2 | 2 |
| 1 | 3 |
| 1 | 4 |
+-----------+---------+

如果对于聚合函数的结果进行二次筛选，必须进行having
where 后面不能出现聚合函数的结果
select count(id) as id,part_id from userinfo5 group by part_id having count(id)>1;

select count(id) from userinfo5; # 计算总共有多少条数据
+-----------+
| count(id) |
+-----------+
| 5 |
+-----------+

f. 连表操作

select * from userinfo5,department5 where userinfo5.part_id=department5.id;
+----+--------+---------+----+-------+
| id | name | part_id | id | title |
+----+--------+---------+----+-------+
| 1 | alex | 3 | 3 | 关关 |
| 2 | egon | 1 | 1 | 公关 |
| 3 | wusir | 4 | 4 | 公公 |
| 4 | 铁锤 | 2 | 2 | 关公 |
| 5 | 小钢炮 | 2 | 2 | 关公 |
+----+--------+---------+----+-------+

推荐写法：
select * from userinfo5 left join department5 on userinfo5.part_id=department5.id;

select * from department5 left join userinfo5 on userinfo5.part_id=department5.id;相当于下面那种写法
# userinfo5左边全部显示
+----+--------+---------+------+-------+
| id | name | part_id | id | title |
+----+--------+---------+------+-------+
| 1 | alex | 3 | 3 | 关关 |
| 2 | egon | 1 | 1 | 公关 |
| 3 | wusir | 4 | 4 | 公公 |
| 4 | 铁锤 | 2 | 2 | 关公 |
| 5 | 小钢炮 | 2 | 2 | 关公 |
+----+--------+---------+------+-------+

select * from userinfo5 right join department5 on userinfo5.part_id=department5.id;
# department5右边全部显示
+------+--------+---------+----+--------+
| id | name | part_id | id | title |
+------+--------+---------+----+--------+
| 2 | egon | 1 | 1 | 公关 |
| 4 | 铁锤 | 2 | 2 | 关公 |
| 5 | 小钢炮 | 2 | 2 | 关公 |
| 1 | alex | 3 | 3 | 关关 |
| 3 | wusir | 4 | 4 | 公公 |
| NULL | NULL | NULL | 5 | 关关共 |
+------+--------+---------+----+--------+


连接多个表：
select * from userinfo5
left join department5 on userinfo5.part_id=department5.id
left join department6 on userinfo5.part_id=department6.id;

连接表的时候如果列名重复，就要带上表名：
select score.sid,student.sid from score
left join student on score.student_id = student.sid

left join course on score.corse_id = course.cid

left join class on student.class_id = class.cid

left join teacher on course.tearch_id = teacher.tid;


inner join:(连表之后若有null的值，就把这一行隐藏掉)
select * from userinfo5 inner join department5 on userinfo5.part_id=department5.id;
+----+--------+---------+----+-------+
| id | name | part_id | id | title |
+----+--------+---------+----+-------+
| 1 | alex | 3 | 3 | 关关 |
| 2 | egon | 1 | 1 | 公关 |
| 3 | wusir | 4 | 4 | 公公 |
| 4 | 铁锤 | 2 | 2 | 关公 |
| 5 | 小钢炮 | 2 | 2 | 关公 |
+----+--------+---------+----+-------+