表的关系与查询
程序员文章站
2023-11-09 08:08:34
三种关系: 多对多 ; 多对一 ; 一对一 . 找出俩张表之间的关系 表的三种关系 一对多(多对一) :例 书和出版社 关联方式:foreign key 多对多: 例 作者和书籍 (一个作者可以写多本书,一本书也可以有多个作者.) 关联方式: foreign key+一张新表 一对一 例 :用户和博 ......
三种关系: 多对多 ; 多对一 ; 一对一 .
找出俩张表之间的关系
分析步骤： #1、先站在左表的角度去找 是否左表的多条记录可以对应右表的一条记录，如果是，则证明左表的一个字段foreign key 右表一个字段（通常是id） #2、再站在右表的角度去找 是否右表的多条记录可以对应左表的一条记录，如果是，则证明右表的一个字段foreign key 左表一个字段（通常是id） #3、总结： #多对一： 如果只有步骤1成立，则是左表多对一右表 如果只有步骤2成立，则是右表多对一左表 #多对多 如果步骤1和2同时成立，则证明这两张表时一个双向的多对一，即多对多,需要定义一个这两张表的关系表来专门存放二者的关系 #一对一: 如果1和2都不成立，而是左表的一条记录唯一对应右表的一条记录，反之亦然。这种情况很简单，就是在左表foreign key右表的基础上，将左表的外键字段设置成unique即可
表的三种关系
一对多(多对一) :例 书和出版社
关联方式:foreign key
create table press(
id int primary key auto_increment,
name varchar(20)
);
create table book(
id int primary key auto_increment,
name varchar(20),
press_id int not null,
constraint fk_book_press foreign key(press_id) references press(id)
on delete cascade
on update cascade
);
# 先往被关联表中插入记录
insert into press(name) values
('北京工业地雷出版社'),
('人民音乐不好听出版社'),
('知识产权没有用出版社')
;
# 再往关联表中插入记录
insert into book(name,press_id) values
('九阳神功',1),
('九阴真经',2),
('九阴白骨爪',2),
('独孤九剑',3),
('降龙十巴掌',2),
('葵花宝典',3)
;
查询结果：
mysql> select * from book;
+----+-----------------+----------+
| id | name | press_id |
+----+-----------------+----------+
| 1 | 九阳神功 | 1 |
| 2 | 九阴真经 | 2 |
| 3 | 九阴白骨爪 | 2 |
| 4 | 独孤九剑 | 3 |
| 5 | 降龙十巴掌 | 2 |
| 6 | 葵花宝典 | 3 |
+----+-----------------+----------+
rows in set (0.00 sec)
mysql> select * from press;
+----+--------------------------------+
| id | name |
+----+--------------------------------+
| 1 | 北京工业地雷出版社 |
| 2 | 人民音乐不好听出版社 |
| 3 | 知识产权没有用出版社 |
+----+--------------------------------+
rows in set (0.00 sec)
多对多: 例 作者和书籍 (一个作者可以写多本书,一本书也可以有多个作者.)
关联方式: foreign key+一张新表
# 创建被关联表author表，在之前的book表的关系已创建的情况下:
create table author(
id int primary key auto_increment,
name varchar(20)
);
#这张表就存放了author表和book表的关系，即查询二者的关系查这表就可以了
create table author2book(
id int not null unique auto_increment,
author_id int not null,
book_id int not null,
constraint fk_author foreign key(author_id) references author(id)
on delete cascade
on update cascade,
constraint fk_book foreign key(book_id) references book(id)
on delete cascade
on update cascade,
primary key(author_id,book_id)
);
#插入四个作者，id依次排开
insert into author(name) values('egon'),('alex'),('wusir'),('yuanhao');
# 每个作者的代表作
egon: 九阳神功、九阴真经、九阴白骨爪、独孤九剑、降龙十巴掌、葵花宝典
alex: 九阳神功、葵花宝典
wusir:独孤九剑、降龙十巴掌、葵花宝典
yuanhao:九阳神功
# 在author2book表中插入相应的数据
insert into author2book(author_id,book_id) values
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(2,1),
(2,6),
(3,4),
(3,5),
(3,6),
(4,1)
;
# 现在就可以查author2book对应的作者和书的关系了
mysql> select * from author2book;
+----+-----------+---------+
| id | author_id | book_id |
+----+-----------+---------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 1 | 4 |
| 5 | 1 | 5 |
| 6 | 1 | 6 |
| 7 | 2 | 1 |
| 8 | 2 | 6 |
| 9 | 3 | 4 |
| 10 | 3 | 5 |
| 11 | 3 | 6 |
| 12 | 4 | 1 |
+----+-----------+---------+
rows in set (0.00 sec)
一对一 例 :用户和博客(一个用户只能注册一个博客.)
#例如： 一个用户只能注册一个博客
#两张表： 用户表 （user）和 博客表(blog)
# 创建用户表
create table user(
id int primary key auto_increment,
name varchar(20)
);
# 创建博客表
create table blog(
id int primary key auto_increment,
url varchar(100),
user_id int unique,
constraint fk_user foreign key(user_id) references user(id)
on delete cascade
on update cascade
);
#插入用户表中的记录
insert into user(name) values
('alex'),
('wusir'),
('egon'),
('xiaoma')
;
# 插入博客表的记录
insert into blog(url,user_id) values
('http://www.cnblog/alex',1),
('http://www.cnblog/wusir',2),
('http://www.cnblog/egon',3),
('http://www.cnblog/xiaoma',4)
;
# 查询wusir的博客地址
select url from blog where user_id=2;
单表查询:
语法:
一、单表查询的语法
select 字段1,字段2... from 表名(select * from 表名 #一般不用 * )
where 条件(第一次筛选)
group by field(根据某条件分组)
having 筛选(二次筛选,应在分组之后进行)
order by field(排序)
limit 限制条数
二、关键字的执行优先级（重点）
重点中的重点：关键字的执行优先级
from
where
group by
having
select
distinct(去重)
order by
limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
#创建表,设置字段的约束条件
create table employee(
id int primary key auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int,#一个部门一个屋
depart_id int
);
# 查看表结构
mysql> desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| field | type | null | key | default | extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | no | pri | null | auto_increment |
| emp_name | varchar(20) | no | | null | |
| sex | enum('male','female') | no | | male | |
| age | int(3) unsigned | no | | 28 | |
| hire_date | date | no | | null | |
| post | varchar(50) | yes | | null | |
| post_comment | varchar(100) | yes | | null | |
| salart | double(15,2) | yes | | null | |
| office | int(11) | yes | | null | |
| depart_id | int(11) | yes | | null | |
+--------------+-----------------------+------+-----+---------+----------------+
rows in set (0.08 sec)
#插入记录
#三个部门：教学，销售，运营
insert into employee(name ,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('xiaomage','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
where 约束
where子句中可以使用 1.比较运算符：>、<、>=、<=、<>、!= 2.between 80 and 100 ：值在80到100之间 3.in(80,90,100)值是80或90或100 4.like 'xiaomagepattern': pattern可以是%或者_ %小时任意多字符，_表示一个字符
'xaiomage%' 'xiaomage_' 'xiaomage' 5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and / or / not
例:
#1 :单条件查询
mysql> select id,emp_name from employee where id > 5;
+----+------------+
| id | emp_name |
+----+------------+
| 6 | jingliyang |
| 7 | jinxin |
| 8 | xiaomage |
| 9 | 歪歪 |
| 10 | 丫丫 |
| 11 | 丁丁 |
| 12 | 星星 |
| 13 | 格格 |
| 14 | 张野 |
| 15 | 程咬金 |
| 16 | 程咬银 |
| 17 | 程咬铜 |
| 18 | 程咬铁 |
#2 多条件查询
mysql> select emp_name from employee where post='teacher' and salary>10000;
+----------+
| emp_name |
+----------+
| alex |
| jinxin |
+----------+
#3.关键字between and
select name,salary from employee
where salary between 10000 and 20000;
select name,salary from employee
where salary not between 10000 and 20000;
#注意''是空字符串，不是null
select name,post_comment from employee where post_comment='';
ps：
执行
update employee set post_comment='' where id=2;
再用上条查看，就会有结果了
#5:关键字in集合查询
mysql> select name,salary from employee where salary=3000 or salary=3500 or salary=4000 or salary=9000 ;
+------------+---------+
| name | salary |
+------------+---------+
| yuanhao | 3500.00 |
| jingliyang | 9000.00 |
+------------+---------+
rows in set (0.00 sec)
mysql> select name,salary from employee where salary in (3000,3500,4000,9000) ;
+------------+---------+
| name | salary |
+------------+---------+
| yuanhao | 3500.00 |
| jingliyang | 9000.00 |
+------------+---------+
mysql> select name,salary from employee where salary not in (3000,3500,4000,9000) ;
+-----------+------------+
| name | salary |
+-----------+------------+
| egon | 7300.33 |
| alex | 1000000.31 |
| wupeiqi | 8300.00 |
| liwenzhou | 2100.00 |
| jinxin | 30000.00 |
| xiaomage | 10000.00 |
| 歪歪 | 3000.13 |
| 丫丫 | 2000.35 |
| 丁丁 | 1000.37 |
| 星星 | 3000.29 |
| 格格 | 4000.33 |
| 张野 | 10000.13 |
| 程咬金 | 20000.00 |
| 程咬银 | 19000.00 |
| 程咬铜 | 18000.00 |
| 程咬铁 | 17000.00 |
+-----------+------------+
rows in set (0.00 sec)
#6:关键字like模糊查询
通配符’%’
mysql> select * from employee where name like 'jin%';
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jingliyang | female | 18 | 2011-02-11 | teacher | null | 9000.00 | 401 | 1 |
| 7 | jinxin | male | 18 | 1900-03-01 | teacher | null | 30000.00 | 401 | 1 |
+----+------------+--------+-----+------------+---------+--------------+----------+--------+-----------+
rows in set (0.00 sec)
通配符'_'
mysql> select age from employee where name like 'ale_';
+-----+
| age |
+-----+
| 78 |
+-----+
row in set (0.00 sec)
练习：
1. 查看岗位是teacher的员工姓名、年龄
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
4. 查看岗位描述不为null的员工信息
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
#对应的sql语句
select name,age from employee where post = 'teacher';
select name,age from employee where post='teacher' and age > 30;
select name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
select * from employee where post_comment is not null;
select name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
select name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
select name,salary*12 from employee where post='teacher' and name like 'jin%';
group by 分组查询
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
注意: 在使用group by 查询时,只是筛选组中的第一条数据,但是我们常常
想知道多条数据.
mysql> select * from employee group by post; +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 14 | 张野 | male | 28 | 2016-03-11 | operation | null | 10000.13 | 403 | 3 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | null | 3000.13 | 402 | 2 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 老男孩驻沙河办事处外交大使 | null | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.00 sec) #由于没有设置only_full_group_by,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的 如果想分组，则必须要设置全局的sql的模式为only_full_group_by mysql> set global sql_mode='only_full_group_by'; query ok, 0 rows affected (0.00 sec) #查看mysql 5.7默认的sql_mode如下：
mysql> exit;#设置成功后，一定要退出，然后重新登录方可生效 bye
通过group by 分组后,只能查询当前字段,如果想查看组内信息,需要借助
聚合函数
mysql> select * from emp group by post;# 报错 error 1054 (42s22): unknown column 'post' in 'group statement' mysql> select post from employee group by post; +-----------------------------------------+ | post | +-----------------------------------------+ | operation | | sale | | teacher | | 老男孩驻沙河办事处外交大使 | +-----------------------------------------+ rows in set (0.00 sec)
聚合函数
max()求最大值 min()求最小值 avg()求平均值 sum() 求和 count() 求总个数 #强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 # 每个部门有多少个员工 select post,count(id) from employee group by post; # 每个部门的最高薪水 select post,max(salary) from employee group by post; # 每个部门的最低薪水 select post,min(salary) from employee group by post; # 每个部门的平均薪水 select post,avg(salary) from employee group by post; # 每个部门的所有薪水 select post,sum(age) from employee group by post;
having过滤
having与where不一样的地方在于 #！！！执行优先级从高到低：where > group by > having #1. where 发生在分组group by之前，因而where中可以有任意字段，但是绝对不能使用聚合函数。 #2. having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
示例:
验证： mysql> select * from employee where salary>1000000; +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | +----+------+------+-----+------------+---------+--------------+------------+--------+-----------+ row in set (0.00 sec) mysql> select * from employee having salary>1000000; error 1463 (42000): non-grouping field 'salary' is used in having clause # 必须使用group by才能使用group_concat()函数，将所有的name值连接 mysql> select post,group_concat(name) from emp group by post having salary > 10000; ##错误，分组后无法直接取到salary字段 error 1054 (42s22): unknown column 'post' in 'field list'
练习:
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 2. 查询各岗位平均薪资大于10000的岗位名、平均工资 3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
答案:
# 题1： mysql> select post,group_concat(name),count(id) from employee group by post; +-----------------------------------------+-----------------------------------------------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+-----------------------------------------------------------+-----------+ | operation | 程咬铁,程咬铜,程咬银,程咬金,张野 | 5 | | sale | 格格,星星,丁丁,丫丫,歪歪 | 5 | | teacher | xiaomage,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex | 7 | | 沙河办事处外交大使 | egon | 1 | +-----------------------------------------+-----------------------------------------------------------+-----------+ rows in set (0.00 sec) mysql> select post,group_concat(name),count(id) from employee group by post having count(id)<2; +-----------------------------------------+--------------------+-----------+ | post | group_concat(name) | count(id) | +-----------------------------------------+--------------------+-----------+ | 沙河办事处外交大使 | egon | 1 | +-----------------------------------------+--------------------+-----------+ row in set (0.00 sec) #题2： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000; +-----------+---------------+ | post | avg(salary) | +-----------+---------------+ | operation | 16800.026000 | | teacher | 151842.901429 | +-----------+---------------+ rows in set (0.00 sec) #题3： mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000; +-----------+--------------+ | post | avg(salary) | +-----------+--------------+ | operation | 16800.026000 | +-----------+--------------+ row in set (0.00 sec)
order by 查询顺序
按单列排序
select * from employee order by age;
select * from employee order by age asc;
select * from employee order by age desc;
按多列排序:先按照age升序排序，如果年纪相同，则按照id降序
select * from employee
order by age asc,
id desc;
示例:
验证多列排序： select * from employee order by age asc,id desc; mysql> select * from employee order by age asc,id desc; +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ | 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | null | 17000.00 | 403 | 3 | | 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | null | 18000.00 | 403 | 3 | | 16 | 程咬银 | female | 18 | 2013-03-11 | operation | null | 19000.00 | 403 | 3 | | 15 | 程咬金 | male | 18 | 1997-03-12 | operation | null | 20000.00 | 403 | 3 | | 12 | 星星 | female | 18 | 2016-05-13 | sale | null | 3000.29 | 402 | 2 | | 11 | 丁丁 | female | 18 | 2011-03-12 | sale | null | 1000.37 | 402 | 2 | | 7 | jinxin | male | 18 | 1900-03-01 | teacher | null | 30000.00 | 401 | 1 | | 6 | jingliyang | female | 18 | 2011-02-11 | teacher | null | 9000.00 | 401 | 1 | | 1 | egon | male | 18 | 2017-03-01 | 沙河办事处外交大使 | null | 7300.33 | 401 | 1 | | 14 | 张野 | male | 28 | 2016-03-11 | operation | null | 10000.13 | 403 | 3 | | 13 | 格格 | female | 28 | 2017-01-27 | sale | null | 4000.33 | 402 | 2 | | 5 | liwenzhou | male | 28 | 2012-11-01 | teacher | null | 2100.00 | 401 | 1 | | 10 | 丫丫 | female | 38 | 2010-11-01 | sale | null | 2000.35 | 402 | 2 | | 9 | 歪歪 | female | 48 | 2015-03-11 | sale | null | 3000.13 | 402 | 2 | | 8 | xiaomage | male | 48 | 2010-11-11 | teacher | null | 10000.00 | 401 | 1 | | 4 | yuanhao | male | 73 | 2014-07-01 | teacher | null | 3500.00 | 401 | 1 | | 2 | alex | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | wupeiqi | male | 81 | 2013-03-05 | teacher | null | 8300.00 | 401 | 1 | +----+------------+--------+-----+------------+-----------------------------------------+--------------+------------+--------+-----------+ rows in set (0.01 sec) mysql>
limit 限制查询的记录:
示例：
select * from employee order by salary desc
limit 3; #默认初始位置为0
select * from employee order by salary desc
limit 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
select * from employee order by salary desc
limit 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条