多表关系
社会中存储需要可以构建成表的数据, 它们形成的表,往往之间存储某种或某些社会关系,
mysql数据库建立表结构就是社会中产生的各种数据, 分门别类管理
但mysql建立的(代码层次的)表之间, 同样需要处理表与表之间的关系
形成了 多对一 | 多对多 | 一对一 三种关系
"""
一对一：丈夫-妻子，用户-身份证，作者-作者详情
一对多：部门-员工，班级-学生，书-出版社
多对多：老师-班级，课程-学生，出版社-作者
"""
# 书 - 出版社 - 作者 - 作者详情 外键分布
# 外键是 建立表与表关联 的字段，通常 一个表的外键 是 另一个表的主键（唯一键也可以）
# 一对一：外键在任何一方都可以，此时外键要设置 唯一键
"""
作者(author)：id，name，sex，age，mobile
作者详情(author_detail)： id，info，address，author_id
----------------------------------------------------
作者(author)：id，name，sex，age，mobile, detail_id
1 Tom 1
2 Bom 2
3 Bob 3
作者详情(author_detail)： id，info，address
1 Tom_info
2 Bom_info
"""
# 一对多：外键必须放在多的一方，此时外键值不唯一
"""
书(book)：id，name，price，publish_id
1 西游记 1
2 东游记 2
3 西厢记 1
4 流浪记 1
出版社(publish)： id，name，address，phone
1 老奶奶出版社
2 小奶奶出版社
"""
# 多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
"""
作者(author)：id, name, age
出版社(publish)：id, name, address
作者与出版社关系表：
id author_id publish_id
1 1 1
2 1 2
3 2 1
4 2 2
"""
约束条件中的外键
重点：外键字段本身可以唯一或不唯一，但是外键关联的字段一定唯一
# 作者(author)：id，name，sex，age，mobile, detail_id
# 作者详情(author_detail)： id，info，address
# 1、外键的 字段名 可以自定义(名字随意)，通常命名规范(关联表_关联字段)
# 2、外键要通过 foreign key 语法建立表与表之间的关联
# 3、foreign key(所在表的外键字段) references 关联表(关联字段)
# eg>：foreign key(detail_id) references author_detail(id)
# 4、级联关系
# 级联更新 on update cascade
# 级联删除 on delete cascade
一对一：无级联关系
# 作者详情(author_detail)： id，info，address
create table author_detail(
id int primary key auto_increment,
info varchar(256),
address varchar(256)
);
# 作者表id，name，sex，age，mobile, detail_id
create table author(
id int primary key auto_increment,
name varchar(64) not null,
mobile char(11) unique not null,
sex enum('男', '女') default '男',
age int default 0,
detail_id int unique not null,
foreign key(detail_id) references author_detail(id)
);
# 必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
###############################################################################
mysql> select * from author_detail;
+----+----------+-------------+
| id | info | address |
+----+----------+-------------+
| 1 | TOM_info | TOM_address |
| 2 | Bob_info | Bob_address |
+----+----------+-------------+
2 rows in set (0.00 sec)
mysql> select * from author;
+----+------+-------------+------+------+-----------+
| id | name | mobile | sex | age | detail_id |
+----+------+-------------+------+------+-----------+
| 1 | Tom | 13344556677 | 男 | 0 | 1 |
| 2 | Bob | 15666882233 | 男 | 0 | 2 |
+----+------+-------------+------+------+-----------+
2 rows in set (0.00 sec)
###############################################################################
# 修改关联表 author
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
# 删除关联表 author
mysql>: delete from author where detail_id=3; # 直接删除
# 修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1; # 无法修改
# 删除被关联表 author_detail
mysql>: delete from author_detail where id=1; # 无法删除
# 没有级联关系下：
# 增加：先增加被关联表记录，再增加关联表记录
# 删除：先删除关联表记录，再删除被关联表记录
# 更新：关联与被关联表都无法完成 关联的外键和主键 数据更新 - (如果被关联表记录没有被绑定，可以修改)
一对一：有级联关系
建表规则：
未存放外键的表被依赖，称之为左表（被关联表）；存放外键的表示依赖表，称之为右表（关联表）；先操作被关联表再操作关联表
mysql>: drop table author;
mysql>: drop table author_detail;
# 作者详情(author_detail)： id，info，address
create table author_detail(
id int primary key auto_increment,
info varchar(256),
address varchar(256)
);
# 作者表id，name，sex，age，mobile, detail_id
create table author(
id int primary key auto_increment,
name varchar(64) not null,
mobile char(11) unique not null,
sex enum('男', '女') default '男',
age int default 0,
detail_id int unique not null,
foreign key(detail_id) references author_detail(id)
on update cascade
on delete cascade
);
# 必须先创建被关联表数据，有关联表外键关联的记录后，关联表才可以创建数据
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1); # 错误
mysql>: insert into author_detail(info,address) values('Tom_info','Tom_address');
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 1);
mysql>: insert into author_detail(info,address) values('Bob_info','Bob_address');
mysql>: insert into author(name,mobile,detail_id) values('Bob','15666882233', 2);
# 修改关联表 author
mysql>: update author set detail_id=3 where detail_id=2; # 失败，3详情不存在
mysql>: update author set detail_id=1 where detail_id=2; # 失败，1详情已被关联
mysql>: insert into author_detail(info,address) values('Tom_info_sup','Tom_address_sup');
mysql>: update author set detail_id=3 where detail_id=2; # 有未被其他数据关联的数据，就可以修改
# 删除关联表 author
mysql>: delete from author where detail_id=3; # 直接删除
# 修改被关联表 author_detail
mysql>: update author_detail set id=10 where id=1; # 级联修改，同步关系关联表外键
# 删除被关联表 author_detail
mysql>: delete from author where detail_id=10; # 可以删除对被关联表无影响
mysql>: insert into author(name,mobile,detail_id) values('Tom','13344556677', 10);
mysql>: delete from author_detail where id=10; # 可以删除，将关联表的记录级联删除掉
一对多
# 一对多：外键必须放在多的一方，此时外键值不唯一
# 出版社(publish)： id，name，address，phone
create table publish(
id int primary key auto_increment,
name varchar(64),
address varchar(256),
phone char(20)
);
# 书(book)：id，name，price，publish_id, author_id
create table book(
id int primary key auto_increment,
name varchar(64) not null,
price decimal(5, 2) default 0,
publish_id int, # 一对多的外键不能设置唯一
foreign key(publish_id) references publish(id)
on update cascade
on delete cascade
);
# 增：先增加被关联表(publish)的数据，再增加关联表(book)的数据
mysql>: insert into publish(name, address, phone) values
('人民出版社', '北京', '010-110'),
('西交大出版社', '西安', '010-119'),
('老男孩出版社', '上海', '010-120');
mysql>: insert into book(name, price, publish_id) values
('西游记', 6.66, 1),
('东游记', 8.66, 1),
('python从入门到入土', 2.66, 2),
('轮程序员修养之道', 3.66, 3),
('好好活着', 88.88, 3);
# 没有被关联的字段，插入依旧错误
mysql>: insert into book(name, price, publish_id) values ('打脸之道', 0.3, 4); # 失败
# 更新：直接更新被关联表的(publish) 主键，关联表(book) 外键 会级联更新
mysql>: update publish set id=10 where id=1;
# 更新：直接更新关联表的(book) 外键，修改的值对应被关联表(publish) 主键 如果存在，可以更新成功，反之失败
mysql>: update book set publish_id=2 where id=4; # 成功
mysql>: update book set publish_id=1 where id=4; # 失败
# 删：
# 删被关联表，关联表会被级联删除
mysql>: delete from publish where id = 2;
# 删关联表，被关联表不会发生变化
mysql>: delete from book where publish_id = 3;
# 假设：书与作者也是 一对多 关系，一个作者可以出版多本书
create table book(
id int primary key auto_increment,
name varchar(64) not null,
price decimal(5, 2) default 0,
publish_id int, # 一对多的外键不能设置唯一
foreign key(publish_id) references publish(id)
on update cascade
on delete cascade
# 建立与作者 一对多 的外键关联
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
多对多
# 多对多：一定要创建第三张表(关系表)，每一个外键值不唯一，看可以多个外键建立联合唯一
mysql>: drop table author;
mysql>: drop table author_detail;
mysql>: drop table book;
mysql>: drop table publish;
# 作者(author)：id, name, age
create table author(
id int primary key auto_increment,
name varchar(64),
age int unsigned default 0
);
# 出版社(publish)：id, name, address
create table publish(
id int primary key auto_increment,
name varchar(64),
address varchar(256)
);
# 作者与出版社关系表：id, author_id, publish_id
create table author_publish(
id int primary key auto_increment,
# 关系表一定有多个外键，关联着多张表
# 关联作者表
author_id int,
foreign key(author_id) references author(id)
on update cascade
on delete cascade,
# 关联出版社表
publish_id int,
foreign key(publish_id) references publish(id)
on update cascade
on delete cascade,
# 建立两个字段的联合唯一
unique(author_id, publish_id)
);
# 注：关系表 关联着 作者 和 出版社 两张表，在表结构上 作者 与 出版社 两表键没有任何关系
# 增：两张被关联表，没有前后关系，但关系表必须在两个表都提供数据后才能进行 关系匹配
mysql>: insert into author(name, age) values('ruakei', 67),('engo', 76),('Lxx', 3);
mysql>: insert into publish(name, address) values('老男孩出版社', '上海'),('小女孩出版社', '北京');
# 操作关系表：
mysql>: insert into author_publish(author_id, publish_id) values(1,1),(1,2),(2,1),(2,2),(3,1);
# 关系表操作：增、删、改，只要两张被关系表有提供对应的操作数据，都可以操作成功，且对两张被关系表没有影响
# 操作两张被关系表：
# 增：不会影响关系表
mysql>: insert into publish(name, address) values('西交大出版社', '西安');
# 改：关系表都会级联更新
mysql>: update publish set id=10 where id=1;
# 删：关系表都会级联删除
mysql>: delete from author where name='ruakei';
多表关系小练习
1、创建一个stu表，字段有：自增主键id，不为空姓名，默认值性别(枚举类型)，无限制身高
create table stu(
id int primary key auto_increment,
name varchar(256) not null,
gender enum('男','女') default '男',
height float(5,2) unsigned
);
2、为stu表依次插入以下三条数据
i）插入一条包含id，name，gender，height四个信息的数据
insert into stu values(1,'tom','男',180.88);
ii）插入一条name，gender，height三个信息的数据
insert into stu(name,gender,height) values('bob','女',155.55);
iii）插入一条只有name信息的数据
insert into stu(name) values('rock');
mysql> select * from stu;
+----+------+--------+--------+
| id | name | gender | height |
+----+------+--------+--------+
| 1 | tom | 男 | 180.88 |
| 2 | bob | 女 | 155.55 |
| 3 | rock | 男 | NULL |
+----+------+--------+--------+
3、创建一张有姓名、年龄的teacher表，在最后添加工资字段，在姓名后添加id主键字段
mysql> create table teacher(
name varchar(256),
id int primary key auto_increment,
age int,
sarlay float(5,2)
);
mysql> desc teacher;
+--------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+----------------+
| name | varchar(256) | YES | | NULL | |
| id | int(11) | NO | PRI | NULL | auto_increment |
| age | int(11) | YES | | NULL | |
| sarlay | float(5,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+----------------+
4、思考：将4中id字段移到到表的最前方，形成最终字段顺序为id、姓名、年龄、工资
mysql> alter table teacher modify id int first;
mysql> desc teacher;
+--------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+--------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| name | varchar(256) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
| sarlay | float(5,2) | YES | | NULL | |
+--------+--------------+------+-----+---------+-------+
5、完成 学生表 与 国家表 的 多对一 表关系的创建，并完成数据测试
mysql> create table country(
id int primary key auto_increment,
name char(16)
);
mysql> create table student(
id int primary key auto_increment,
name varchar(256) not null,
gender enum('男','女') default '男',
country_id int,
foreign key(country_id) references country(id)
on update cascade
on delete cascade
);
#增加数据
mysql> insert into country(name) values('中国');
mysql> insert into student(name,gender,country_id) values('Bob','男',1),('Tom','男',1),('Cob','男',1),('Tob','女',1);
# 更新数据
# 可以对被关联表进行更改
mysql> update country set id=10 where id=1;
mysql> update student set id=1 where id=10;
# 不能对外键直接更改
#错误
mysql> update student set country_id=1 where country_id=10;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`db2`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`country_id`) REFERENCES `country` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)
#删除数据
mysql> delete from student where id=1;
mysql> select * from student;
+----+------+--------+------------+
| id | name | gender | country_id |
+----+------+--------+------------+
| 2 | Tom | 男 | 10 |
| 3 | Cob | 男 | 10 |
| 4 | Tob | 女 | 10 |
+----+------+--------+------------+
mysql> select * from country;
+----+--------+
| id | name |
+----+--------+
| 10 | 中国 |
+----+--------+
mysql> delete from country where id=10;
mysql> select * from student;
Empty set (0.00 sec)
mysql> select * from country;
Empty set (0.00 sec)
6、完成 学生表 与 课程表 的 多对多 表关系的创建，并完成数据测试
create table student(
id int primary key auto_increment,
name varchar(256) not null,
gender enum('男','女') default '男'
);
create table course(
id int primary key auto_increment,
name char(16)
);
create table student_course(
id int primary key auto_increment,
student_id int not null,
foreign key(student_id) references student(id)
on update cascade
on delete cascade,
course_id int not null,
foreign key(course_id) references course(id)
on update cascade
on delete cascade
);
# 增加数据
mysql> insert into student(name,gender) values('Bob','男'),('Tom','男'),('Cob','男'),('Tob','女');
mysql> insert into course(name) values('数学'),('python'),('linux');
mysql> insert into student_course(student_id,course_id) values(1,1),(1,2),(1,3),(2,2),(2,3),(3,3);
# 增加
mysql> insert into student(name,gender) values('Aob','男');
#改
mysql> update student set id=10 where id=1;
mysql> update course set id=20 where id=1;
#删
mysql> delete from student where id=10;
mysql> select * from student_course;
+----+------------+-----------+
| id | student_id | course_id |
+----+------------+-----------+
| 4 | 2 | 2 |
| 5 | 2 | 3 |
| 6 | 3 | 3 |
+----+------------+-----------+
mysql> select * from student;
+----+------+--------+
| id | name | gender |
+----+------+--------+
| 2 | Tom | 男 |
| 3 | Cob | 男 |
| 4 | Tob | 女 |
| 5 | Aob | 男 |
+----+------+--------+
mysql> select * from course;
+----+--------+
| id | name |
+----+--------+
| 2 | python |
| 3 | linux |
| 20 | 数学 |
+----+--------+
7、完成 学生表 与 学生简介表 的 一对一 表关系的创建，并完成数据测试
create table student_info(
id int primary key auto_increment,
info varchar(256),
address varchar(256)
);
create table student(
id int primary key auto_increment,
name varchar(256) not null,
gender enum('男','女') default '男',
info_id int not null,
foreign key(info_id) references student_info(id)
on update cascade
on delete cascade
);
#创建后
################增加数据
mysql> insert into student_info(info,address) values('BOb_info','Bob_address'),('Tom_info','Tom_address');
mysql> insert into student(name,gender,info_id) values('Bob','男',1),('Tom','男',2);
### 修改数据
mysql> update student set id=10 where id=1;
mysql> update student_info set id=10 where id=1;
mysql> select * from student;
+----+------+--------+---------+
| id | name | gender | info_id |
+----+------+--------+---------+
| 2 | Tom | 男 | 2 |
| 10 | Bob | 男 | 10 |
+----+------+--------+---------+
8、将 学生表、国家表、课程表、学生简介表 四个表放在一起考虑表关系，并完成数据的增删测试
#国家表
create table country(
id int primary key auto_increment,
name char(16)
);
#学生简介
create table student_info(
id int primary key auto_increment,
info varchar(256),
address varchar(256)
);
#学生表
create table student(
id int primary key auto_increment,
name varchar(256) not null,
gender enum('男','女') default '男',
info_id int not null,
foreign key(info_id) references student_info(id)
on update cascade
on delete cascade,
country_id int not null,
foreign key(country_id) references country(id)
on update cascade
on delete cascade
);
#课程表
create table course(
id int primary key auto_increment,
name char(16)
);
#多对多学生和课程
create table student_course(
id int primary key auto_increment,
student_id int not null,
foreign key(student_id) references student(id)
on update cascade
on delete cascade,
course_id int not null,
foreign key(course_id) references course(id)
on update cascade
on delete cascade
);
#增加
mysql> insert into country(name) values('中国');
mysql> insert into student_info(info,address) values('BOb_info','Bob_address'),('Tom_info','Tom_address');
mysql> insert into student(name,gender,info_id,country_id) values('Bob','男',1,1),('Tom','男',1,1);
mysql> insert into course(name) values('数学'),('python'),('linux');
mysql> insert into student_course(student_id,course_id) values(1,1),(1,2),(1,3),(2,2),(2,3);
#删除
mysql> delete from course where id=1;
mysql> delete from student where id=1;
mysql> select * from student;
+----+------+--------+---------+------------+
| id | name | gender | info_id | country_id |
+----+------+--------+---------+------------+
| 2 | Tom | 男 | 1 | 1 |
+----+------+--------+---------+------------+
mysql> select * from student_info;
+----+----------+-------------+
| id | info | address |
+----+----------+-------------+
| 1 | BOb_info | Bob_address |
| 2 | Tom_info | Tom_address |
+----+----------+-------------+
mysql> select * from course;
+----+--------+
| id | name |
+----+--------+
| 2 | python |
| 3 | linux |
+----+--------+
mysql> select * from student_course;
+----+------------+-----------+
| id | student_id | course_id |
+----+------------+-----------+
| 4 | 2 | 2 |
| 5 | 2 | 3 |
+----+------------+-----------+
查询
查询语法
select [distinct] 字段1 [[as] 别名], ..., 字段n [[as] 别名] from [库名.]表名
[
where 约束条件
group by 分组依据
having 过滤条件
order by 排序的字段
limit 限制显示的条数
];
注：
1.查表中所有字段用*表示
2.条件的书写规则严格按照语法顺序书写，可以缺省，但不可以错序
3.约束条件的流程：from -> where -> group by -> having -> distinct -> order by -> limit
4.字段可以起别名
5.字段可以直接做运算 select age + 1 'new_age' from emp;
6.分组后的条件均可以使用聚合函数
3.
def from():
return "查询的文件"
def where(file):
return "条件筛选后的结果"
def group_by(res):
return "分组后的结果"
def having(res):
return "再次过滤后的结果"
def distinct(res):
return "去重后的结果"
def order_by(res):
return "排序后的结果"
def limit(res):
return "限制条数后的结果"
def select(from=from, where=null, ..., limit=null):
file = from()
res = where(file) if where else file
res = group_by(res) if group_by else res
...
res = limit(res) if limit else res
return res
select(where=where, group_by=group_by)
单表查询
sql中几乎所有的操作都是单表查询，多表也会连成单边，临时表也会创建视图。
select distinct 字段 from 表 where group by having order by limit
"""
增：
insert [into]
[数据库名.]表名[(字段1[, ..., 字段n])]
values
(数据1[, ..., 数据n])[, ..., (数据1[, ..., 数据n])];
删：
delete from [数据库名.]表名 [条件];
改：
updata [数据库名.]表名 set 字段1=值1[, ..., 字段n=值n] [条件];
查：
select [distinct] 字段1 [[as] 别名1],...,字段n [[as] 别名n] from [数据库名.]表名 [条件];
"""
# 条件：from、where、group by、having、distinct、order by、limit => 层层筛选后的结果
# 注：一条查询语句，可以拥有多种筛选条件，条件的顺序必须按照上方顺序进行逐步筛选，distinct稍有特殊(书写位置)，条件的种类可以不全
# 可以缺失，但不能乱序
去重：distinct
mysql>:
create table t1(
id int,
x int,
y int
);
mysql>: insert into t1 values(1, 1, 1), (2, 1, 2), (3, 2, 2), (4, 2, 2);
mysql>: select distinct * from t1; # 全部数据
mysql>: select distinct x, y from t1; # 结果 1,1 1,2 2,2
mysql>: select distinct y from t1; # 结果 1 2
# 总结：distinct对参与查询的所有字段，整体去重(所查的全部字段的值都相同，才认为是重复数据)
数据准备
CREATE TABLE `emp` (
`id` int(0) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`gender` enum('男','女','未知') NULL DEFAULT '未知',
`age` int(0) NULL DEFAULT 0,
`salary` float NULL DEFAULT 0,
`area` varchar(20) NULL DEFAULT '中国',
`port` varchar(20) DEFAULT '未知',
`dep` varchar(20),
PRIMARY KEY (`id`)
);
INSERT INTO `emp` VALUES
(1, 'yangsir', '男', 42, 10.5, '上海', '浦东', '教职部'),
(2, 'engo', '男', 38, 9.4, '山东', '济南', '教学部'),
(3, 'jerry', '女', 30, 3.0, '江苏', '张家港', '教学部'),
(4, 'tank', '女', 28, 2.4, '广州', '广东', '教学部'),
(5, 'jiboy', '男', 28, 2.4, '江苏', '苏州', '教学部'),
(6, 'zero', '男', 18, 8.8, '中国', '黄浦', '咨询部'),
(7, 'owen', '男', 18, 8.8, '安徽', '宣城', '教学部'),
(8, 'jason', '男', 28, 9.8, '安徽', '巢湖', '教学部'),
(9, 'ying', '女', 36, 1.2, '安徽', '芜湖', '咨询部'),
(10, 'kevin', '男', 36, 5.8, '山东', '济南', '教学部'),
(11, 'monkey', '女', 28, 1.2, '山东', '青岛', '教职部'),
(12, 'san', '男', 30, 9.0, '上海', '浦东', '咨询部'),
(13, 'san1', '男', 30, 6.0, '上海', '浦东', '咨询部'),
(14, 'san2', '男', 30, 6.0, '上海', '浦西', '教学部'),
(15, 'ruakei', '女', 67, 2.501, '上海', '陆家嘴', '教学部');
常用函数
拼接：
concat(字段1,...,字段n)：完成字段的拼接
concat_ws(x, 字段1,...,字段n)：完成字段的拼接，x为连接符
大小写：
lower()：小写
upper()：大写
浮点型操作：
ceil()：向上取整
floor()：向下取整
round()：四舍五入
整型：可以直接运算
mysql>: select name,area,port from emp;
mysql> select name,area,port from emp limit 3;
+---------+--------+-----------+
| name | area | port |
+---------+--------+-----------+
| yangsir | 上海 | 浦东 |
| engo | 山东 | 济南 |
| jerry | 江苏 | 张家港 |
+---------+--------+-----------+
mysql>: select name as 姓名, concat(area,'-',port) 地址 from emp; # 上海-浦东
mysql> select name as 姓名,concat(area,'-',port) 地址 from emp limit 3;
+---------+------------------+
| 姓名 | 地址 |
+---------+------------------+
| yangsir | 上海-浦东 |
| engo | 山东-济南 |
| jerry | 江苏-张家港 |
+---------+------------------+
mysql>: select name as 姓名, concat_ws('-',area,port,dep) 信息 from emp; # 上海-浦东-教职部
mysql> select name as 姓名,concat_ws('-',area,port,dep) 地址 from emp limit 3;
+---------+----------------------------+
| 姓名 | 地址 |
+---------+----------------------------+
| yangsir | 上海-浦东-教职部 |
| engo | 山东-济南-教学部 |
| jerry | 江苏-张家港-教学部 |
+---------+----------------------------+
3 rows in set (0.03 sec)
mysql>: select upper(name) 姓名大写,lower(name) 姓名小写 from emp;
mysql> select upper(name) 大写姓名,lower(name) 小写姓名 from emp limit 3;
+--------------+--------------+
| 大写姓名 | 小写姓名 |
+--------------+--------------+
| YANGSIR | yangsir |
| ENGO | engo |
| JERRY | jerry |
+--------------+--------------+
mysql>: select id,salary,ceil(salary)上薪资,floor(salary)下薪资,round(salary)入薪资 from emp;
mysql> select salary,ceil(salary) 上薪资,floor(salary) 下薪资,round(salary) 四舍五入薪资 from emp limit 3;
+--------+-----------+-----------+--------------------+
| salary | 上薪资 | 下薪资 | 四舍五入薪资 |
+--------+-----------+-----------+--------------------+
| 10.5 | 11 | 10 | 10 |
| 9.4 | 10 | 9 | 9 |
| 3 | 3 | 3 | 3 |
+--------+-----------+-----------+--------------------+
mysql>: select name 姓名, age 旧年龄, age+1 新年龄 from emp;
mysql> select name 姓名,age 旧年龄,age+1 新年龄 from emp limit 3;
+---------+-----------+-----------+
| 姓名 | 旧年龄 | 新年龄 |
+---------+-----------+-----------+
| yangsir | 42 | 43 |
| engo | 38 | 39 |
| jerry | 30 | 31 |
+---------+-----------+-----------+
条件：where
# 多条件协调操作导入：where 奇数 [group by 部门 having 平均薪资] order by [平均]薪资 limit 1
mysql>: select * from emp where id<5 limit 1; # 正常
mysql>: select * from emp limit 1 where id<5; # 异常，条件乱序
# 判断规则
"""
比较符合：> | < | >= | <= | = | !=
区间符合：between 开始 and 结束 | in(自定义容器)
between 10 and 20：10~20
in(10, 20, 30)：10或20或30
逻辑符合：and | or | not
相似符合：like _|%，模糊匹配字符串，_表示一个字符，%表示任意字符
正则符合：regexp 正则语法
"""
mysql>: select * from emp where salary>5;
mysql>: select * from emp where id%2=0;
mysql>: select * from emp where salary between 6 and 9;
mysql>: select * from emp where id in(1, 3, 7, 20);
# _o 某o | __o 某某o | _o% 某o* (*是0~n个任意字符) | %o% *o*
mysql>: select * from emp where name like '%o%';
mysql>: select * from emp where name like '_o%';
mysql>: select * from emp where name like '___o%';
# sql只支持部分正则语法
mysql>: select * from emp where name regexp '.*d'; # 不支持d代表数字，认为d就是普通字符串
mysql>: select * from emp where name regexp '.*[0-9]'; # 支持[]语法
where条件之正则匹配
# why: like完成模糊匹配, 但功能局限, 可以模糊个数, 但不能模糊类型, 正则可以完成类型及个数的模糊匹配
'''
语法：字段 regexp '正则表达式'
注：只支持部分正则语法
'''
# 完成需求:
select * from emp where name regexp '.*[0-9]+.*';
分组与筛选：group by | having
where与having区别
# 表象：在没有分组的情况下，where与having结果相同
# 重点：having可以对 聚合结果 进行筛选
mysql>: select * from emp where salary > 5;
mysql>: select * from emp having salary > 5;
mysql>: select * from emp where id in (5, 10, 15, 20);
mysql>: select * from emp having id in (5, 10, 15, 20);
聚合函数
"""
max()：最大值
min()：最小值
avg()：平均值
sum()：和
count()：记数
group_concat()：组内字段拼接，用来查看组内其他字段
"""
如何分组之解决分组中思考题的过程
# res = select max(salary) '最高薪资', gender from emp where dep='教学部' group by gender;
# select name from emp where (salary 跟 res作比较)
# 一个查询依赖于另一个查询的结果 => 一个查询的结果作为另外一个查询的条件 => 子查询
分组查询 group by
'''
分组：根据字段相同值形成不同的类别，不明确分组其实整个表就为一个默认大组
原因：把以值共性得到的类别作为考虑单位，不再关系单条记录，而且一组记录
结果：只能考虑组内多条数据的聚会结果（聚合函数结果），分组的字段同样是聚合结果，如：组内的最大最小值
'''
# 修改my.ini配置重启mysql服务
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 在sql_mode没有 ONLY_FULL_GROUP_BY 限制下，可以执行，但结果没有意义
# 有 ONLY_FULL_GROUP_BY 限制，报错
mysql>: select * from emp group by dep;
# 分组后，表中数据考虑范围就不是 单条记录，因为每个分组都包含了多条记录，参照分组字段，对每个分组中的 多条记录 统一处理
# eg: 按部门分组，每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资、组里一共有多少人
# 将多条数据统一处理，这种方式就叫 聚合
# 每个部门都有哪些人、最高的薪资、最低的薪资、平均薪资 都称之为 聚合结果 - 聚合函数操作的结果
# 注：参与分组的字段，也归于 聚合结果
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
mysql>: select
dep 部门,
max(age) 最高年龄
from emp group by dep;
# 总结：分组后，查询条件只能为 分组字段 和 聚合函数操作的聚合结果
分组后的having
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
# 最低薪资小于2
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep having min(salary)<2;
# having可以对 聚合结果 再进行筛选，where不可以
排序order by
排序规则
# order by 主排序字段 [asc|desc], 次排序字段1 [asc|desc], ...次排序字段n [asc|desc]
升序：order by 字段名 asc
降序：order by 字段名 desc
多个排序条件：order by 字段名 asc,字段名 desc
未分组状态下
mysql>: select * from emp;
# 按年龄升序
mysql>: select * from emp order by age asc;
# 按薪资降序
mysql>: select * from emp order by salary desc;
# 按薪资降序，如果相同，再按年龄降序
mysql>: select * from emp order by salary desc, age desc;
# 按龄降序，如果相同，再按薪资降序
mysql>: select * from emp order by age desc, salary desc;
分组状态下
mysql>:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep;
# 最高薪资降序
mysql:
select
dep 部门,
group_concat(name) 成员,
max(salary) 最高薪资,
min(salary) 最低薪资,
avg(salary) 平均薪资,
sum(salary) 总薪资,
count(gender) 人数
from emp group by dep
order by 最高薪资 desc;
限制 limit
# 语法：limit 条数 | limit 偏移量,条数
mysql>: select name, salary from emp where salary<8 order by salary desc limit 1;
mysql>: select * from emp limit 5,3; # 先偏移5条满足条件的记录，再查询3条
连表查询
连接
# 连接：将有联系的多张表通过关联(有联系就行，不一定是外键)字段，进行连接，形参一张大表
# 连表查询：在大表的基础上进行查询，就称之为连表查询
# 将表与表建立连接的方式有四种：内连接、左连接、右连接、全连接
一对多数据准备
mysql>: create database db3;
mysql>: use db3;
mysql>:
create table dep(
id int primary key auto_increment,
name varchar(16),
work varchar(16)
);
create table emp(
id int primary key auto_increment,
name varchar(16),
salary float,
dep_id int
);
insert into dep values(1, '市场部', '销售'), (2, '教学部', '授课'), (3, '管理部', '开车');
insert into emp(name, salary, dep_id) values('egon', 3.0, 2),('yanghuhu', 2.0, 2),('sanjiang', 10.0, 1),('owen', 88888.0, 2),('liujie', 8.0, 1),('yingjie', 1.2, 0);
笛卡尔积
# 笛卡尔积: 集合 X{a, b} * Y{o, p, q} => Z{{a, o}, {a, p}, {a, q}, {b, o}, {b, p}, {b, q}}
mysql>: select * from emp, dep;
# 总结：是两张表 记录的所有排列组合，数据没有利用价值
注意: 同时查询两张表形成新的表,可以称之为虚拟表, 原表与表之间可能存在重复字段, 同时使用时需要明确所属表,必要时还需明确所属数据库
内连接
# 关键字：inner join on
# 语法：from A表 inner join B表 on A表.关联字段=B表.关联字段
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp inner join dep on emp.dep_id = dep.id
order by emp.id;
# 总结：只保留两个表有关联的数据
左连接
# 关键字：left join on
# 语法：from 左表 left join 右表 on 左表.关联字段=右表.关联字段
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp left join dep on emp.dep_id = dep.id
order by emp.id;
# 总结：保留左表的全部数据，右表有对应数据直接连表显示，没有对应关系空填充
右连接
# 关键字：right join on
# 语法：from A表 right join B表 on A表.关联字段=B表关联字段
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp right join dep on emp.dep_id = dep.id
order by emp.id;
# 总结：保留右表的全部数据，左表有对应数据直接连表显示，没有对应关系空填充
左右可以相互转化
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp right join dep on emp.dep_id = dep.id
order by emp.id;
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from dep left join emp on emp.dep_id = dep.id
order by emp.id;
# 总结：更换一下左右表的位置，相对应更换左右连接关键字，结果相同
全连接
mysql>:
select
emp.id,emp.name,salary,dep.name,work
from emp left join dep on emp.dep_id = dep.id
union
select
emp.id,emp.name,salary,dep.name,work
from emp right join dep on emp.dep_id = dep.id
order by id;
# 总结：左表右表数据都被保留，彼此有对应关系正常显示，彼此没有对应关系均空填充对方
一对一与一对多情况一致
# 创建一对一 作者与作者详情 表
create table author(
id int,
name varchar(64),
detail_id int
);
create table author_detail(
id int,
phone varchar(11)
);
# 填充数据
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
insert into author_detail values(1, '13344556677'), (2, '14466779988'), (3, '12344332255');
# 内连
select author.id,name,phone from author join author_detail on author.detail_id = author_detail.id order by author.id;
# 全连
select author.id,name,phone from author left join author_detail on author.detail_id = author_detail.id
union
select author.id,name,phone from author right join author_detail on author.detail_id = author_detail.id
order by id;
多对多：两表两表建立连接
# 在一对一基础上，建立 作者与书 的多对多关系关系
# 利用之前的作者表
create table author(
id int,
name varchar(64),
detail_id int
);
insert into author values(1, 'Bob', 1), (2, 'Tom', 2), (3, 'ruakei', 0);
# 创建新的书表
create table book(
id int,
name varchar(64),
price decimal(5,2)
);
insert into book values(1, 'python', 3.66), (2, 'Linux', 2.66), (3, 'Go', 4.66);
# 创建 作者与书 的关系表
create table author_book(
id int,
author_id int,
book_id int
);
# 数据：author-book：1-1,2 2-2,3 3-1,3
insert into author_book values(1,1,1),(2,1,2),(3,2,2),(4,2,3),(5,3,1),(6,3,3);
# 将有关联的表一一建立连接，查询所以自己所需字段
select book.name, book.price, author.name, author_detail.phone from book
join author_book on book.id = author_book.book_id
join author on author_book.author_id = author.id
left join author_detail on author.detail_id = author_detail.id;
表查询练习
1、查询教学部山东人的平均薪资
select avg(salary) from emp
where area="山东"
group by dep
having dep="教学部";
select avg(salary) from emp
where dep="教学部"
group by area
having area="山东";
select avg(salary) from emp
where dep="教学部" and area="山东";
2、查询姓名中包含英文字母n并且居住在上海的人的所有信息
select * from emp
where name like "%n%" and area="上海";
3、查询姓名中包含英文字母n但不包含数字的人的所有信息
select * from emp
where name like "%n%"
and name not regexp '.*[0-9].*';
4、查看各部门的平均年龄并升序排序
select dep, avg(salary) '平均薪资' from emp
group by dep
order by avg(salary) asc;
5、查询各部门中年纪最大的人的姓名与居住地（户籍+区域）
select name, concat(area, '-', port), dep from emp
where (age, dep) in
(
select max(age), dep from emp
group by dep
);
6、查询不同年龄层次平均薪资大于5w组中工资最高者的姓名与薪资
select name, salary from emp
where (salary, age) in
(
select max(salary), age from emp
group by age
having avg(salary) > 5
);
7、查询每一个部门下的员工们及员工职责
select max(dep.name), max(dep.work), group_concat(emp.name) from emp right join dep on emp.dep_id = dep.id group by dep_id;
联合分组
# 数据来源：在单表emp下
# 联合分组：按多个字段综合结果进行分组
# 按 area与port组合后的结果进行分组，只有组合后的结果还一致，才认为是一组
select group_concat(name),area,port from emp group by area,port;
子查询
查询结果辅助另外一条sql的增删改查。
如果查询结果为一条
where 字段 = （select 字段 from 表名）
如果查询结果为多条
where 字段 in （select 字段 from 表名）
where 字段 > all（select 字段 from 表名）
where 字段 > any（select 字段 from 表名）
# 增：insert into 表 select子查询
# 删：delete from 表 条件是select子查询(表不能与delete表相同)
# 查：select 字段 from 表 条件是select子查询
# 改：update 表 set 字段=值 条件是select子查询(表不能与update表相同)
# 数据来源：在单表emp下
# 子查询：将一条查询sql的结果作为另一条sql的条件
# 思考：每个部门最高薪资的那个人所有信息
# 子查询的sql
select dep, max(salary) from emp group by dep;
# 子查询 - 查
select * from emp where (dep, salary) in (select dep, max(salary) from emp group by dep);
# 将子查询转换为一张表
# 创建一个存子查询数据的一张表
create table t1(dep_name varchar(64), max_salary decimal(5,2));
# 子查询 - 增
insert into t1 select dep, max(salary) from emp group by dep;
# 需求
select name, dep_name, salary
from emp join t1
on emp.dep=t1.dep_name and emp.salary=t1.max_salary;
# 子查询 - 改（update更新的表不能 与 子查询select的表同表）
# 每个部门最大薪资+1
update t1 set max_salary=max_salary+1;
# 给t1额外增加一个新部门
insert into t1 values ('打杂部', 100);
# 子查询 - 改
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep from emp);
# 错误：update更新的表 与 子查询select的表 相同
update t1 set max_salary=max_salary+1 where dep_name in (select distinct dep_name from t1);
# 子查询 - 删
delete from t1 where dep_name in (select distinct dep from emp);
# 错误: delete删除的表 与 子查询select的表 相同
delete from t1 where dep_name in (select distinct dep_name from t1);
all与any：区间修饰条件
# 语法规则
# where id in (1, 2, 3) => id是1或2或3
# where id not in (1, 2, 3) => id不是1，2，3
# where salary < all(3, 6, 9) => salary必须小于所有情况（小于最小）
# where salary > all(3, 6, 9) => salary必须大于所有情况（大于最大）
# where salary < any(3, 6, 9) => salary只要小于一种情况（小于最大）
# where salary > any(3, 6, 9) => salary只要大于一种情况（大于最小）
in < > ()
# 案例
select * from emp where salary < all(select salary from emp where id>11);