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 | 关公 |
+----+--------+---------+----+-------+

posted @ 2018-08-09 00:30  李培冠  阅读(219)  评论(0编辑  收藏  举报