MySQL的单表多表查询
1.单表查询
#单表查询语法 select <字段1，字段2....> from <表名> where <表达式> group by field 分组 having 筛选 order by fileld 排序 limit 限制条数
#关键字的优先级
from>where>group by>having>select>distinct>orderby>limit
#说明
1.from #找到表
2.where #通过where指定的约束条件，去表中提取记录
3.group by #进行分组，如果没有group by，则按整体为一组
4.having #将分组的结果进行过滤
5.select #执行select
6.distinct #去重
7.order by #进行排序
8.limit #限制结果的显示条数
#查看帮助：help select
mysql> help select Name: 'SELECT' Description: Syntax: SELECT [ALL | DISTINCT | DISTINCTROW ] [HIGH_PRIORITY] [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [SQL_BUFFER_RESULT] [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS] select_expr [, select_expr] ... [into_option] [FROM table_references [PARTITION partition_list]] [WHERE where_condition] [GROUP BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]] [HAVING where_condition] [ORDER BY {col_name | expr | position} [ASC | DESC], ...] [LIMIT {[offset,] row_count | row_count OFFSET offset}] [PROCEDURE procedure_name(argument_list)] [into_option] [FOR UPDATE | LOCK IN SHARE MODE] into_option: { INTO OUTFILE 'file_name' [CHARACTER SET charset_name] export_options | INTO DUMPFILE 'file_name' | INTO var_name [, var_name] ... }
1.1.where约束条件
#where约束条件中可以使用
#1.比较运算符：>、<、 >=、 <>、!=
#例子：
#1.查看id大于5的员工姓名 mysql> select id,name from staff where id > 5; #2.查看id小于且等于10的员工姓名 mysql> select id,name from staff where id <= 10;
#2.between 10 and 20 ：值在10到20之间
#例子：
#1.查看工资在1万到2万之间的员工 mysql> select name,salary from staff where salary between 10000 and 20000;
#2.查看工资不在1万到2万之间的员工 mysql> select name,salary from staff where salary not between 10000 and 20000;
#3.in(10,20,30) :值是10或20或30
#例子：
#1.查看工资是3000，或5000，或4000，或9000的员工 mysql> select name,salary from staff where salary=3000 or salary=5000 or salary=4000 or salary=9000; #2.查看工资不是3000，3500，4000的员工 mysql> select name,staff from staff where salary not in(3000,3500,4000);
#4.like 'fdfdsf': parttern可以是%或_。 %表示任意多字符，_表示一个字符
#例子：
#查看wu后面有任意字符的列 mysql> select * from staff where name like 'wu%';
#5.逻辑运算符，在多个条件直接可以使用逻辑运算符 and(且) or(或) not(不)
#例子：
#1.查看岗位是IT部门，且薪资在10000或9000或30000的员工姓名、年龄 mysql> select name,age,salary from staff where post='IT' and salary=10000 or salary=9000 or salary=30000; #2.查看岗位是IT部门，且薪资不在11000的员工姓名、年龄 mysql> select name,age,salary from staff where post='IT' and not salary=110000
#练习
1. 查看岗位是teacher的员工姓名、年龄 mysql> select name,age from staff where post='teacher'; 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄 mysql> select name,age from staff where post='teacher' and age > 30; 3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资 mysql> select name,age,salary from staff where post='teacher' and salary betweeen 9000 and 10000 4. 查看岗位描述不为NULL的员工信息 mysql> select * from staff where post_comment is not null; 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资 mysql> select name,age,salary from staff where post='teacher' and salary=10000 or salary=9000 or salary=30000; 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资 mysql> select name,age,salary from staff where post='teacher' and not salary=110000 or salary=9000 or salary=30000 7. 查看岗位是teacher且名字是wu开头的员工姓名、年薪 mysql> select name,salary*12 from staff where post='teacher' and name like 'wu%';
1.2.group by分组查询
#分组：指的是将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或性别分组等
#注意1：分组是发生在where之后，即分组是基于where之后得到的记录而进行的
#注意2：进行分组后，如group by post,只能查看post字段，如果要查看组内信息，需要借助聚合函数
#为何要进行分组呢？
1.获取每个部门的员工数 2.获取每个部门的最高工资 3.获取男生人数和女生人数
#提示：如果先分组，必须要把全局的sql模块改为ONLY_FULL_GROUP_BY
#修改方法：
1.登录进去改mysql>set global sql_mode='ONLY_FULL_GROUP_BY';
2.进入/etc/my.cnf配置文件设置sql_mode='ONLY_FULL_GROUP_BY'
#设置之后查看
mysql> select @@global.sql_mode; +--------------------+ | @@global.sql_mode | +--------------------+ | ONLY_FULL_GROUP_BY | +--------------------+ 1 row in set (0.00 sec)
#设置分组之后只能查看当前字段，如果看全部字段就会报错，需要借助聚合函数查看
mysql> select * from staff group by post; #报错 mysql> select post from staff group by post; #查看当前字段不报错
#聚合函数
#提示：聚合函数聚合的是组的内容，如果没有分组，默认为一组
1.max() #求最大值 2.min() #求最小值 3.avg() #求平均值 4.sum() #求和 5.count() #求总个数 6.group_concat() #查字段
#例子：
#1.查询每个部门有多少个员工 select post,count(id) from staff group by post; #2.查询每个部门的最高薪水 select post,max(salary) from staff group by post; #3.查询每个部门的最低薪水 select post,min(salary) from staff group by post; #4.查询每个部门的平均薪水 select post,avg(salary) from staff group by post; #5.查询每个部门的所有薪水 select post,sum(salary) from staff group by post;
#练习
1. 查询岗位名以及岗位包含的所有员工名字 mysql> select post,group_concat(name) from staff group by post; 2. 查询岗位名以及各岗位内包含的员工个数 mysql> select post,count(1) from staff group by post; 3. 查询公司内男员工和女员工的个数 mysql> select group_concat(sex) from staff; 4. 查询岗位名以及各岗位的平均薪资 mysql> select post,avg(salary) from staff group by post; 5. 查询岗位名以及各岗位的最高薪资 mysql> select post,max(salary) from staff group by post; 6. 查询岗位名以及各岗位的最低薪资 mysql> select post,min(salary) from staff group by post;
1.3.having过滤
#注意点：
1.执行优先级从高到低：where > group by > having 2.where发生在分组group by之前，因而where中可以有任意字段，但是不能使用聚合函数 3.having发生在分组group by之后，因而having中可以使用分组的字段，无法直接取到其他字段，可以使用聚合函数
#例子：
1.过滤工资大于10000的员工 mysql> select * from staff having salary>10000;
#练习
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 select post,group_concat(name),count(1) from staff group by post having count(1) < 2; 2. 查询各岗位平均薪资大于10000的岗位名、平均工资 select post,avg(salary) from staff group by post having avg(salary) > 10000; 3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 select post,avg(salary) from staff group by post having avg(salary) between 10000 and 20000;
1.4.order by查询排序
#提示：asc:升序，desc:降序
#按单列排序(默认是升序)
#例子：
#1.按年龄大小排序(默认是asc升序) mysql> select id,name,age from employee order by age; #2.按年龄排序，desc倒序，倒过来排 mysql> select id,name,age from employee order by age desc;
#按多列排序
#例子：
#先按照age(年龄)升序排序，如果年纪相同，则按照id降序 mysql> select id,name,age from staff order by age asc,id desc;
1.5.limit限制查询的记录条数
#例子：
#1.查看id列按升序排序前3列，默认从0开始，查询出第一条 mysql> select id,name from staff order by id asc limit 3; +----+---------+ | id | name | +----+---------+ | 1 | guo | | 2 | ke | | 3 | wu | +----+---------+ 3 rows in set (0.00 sec) #2.查看第1到5列 mysql> select id,name from staff order by id asc limit 0,5; +----+-----------+ | id | name | +----+-----------+ | 1 | guo | | 2 | ke | | 3 | wu | | 4 | yan | | 5 | liu | +----+-----------+ 5 rows in set (0.00 sec) ##从0开始，先查询出第一条，然后包含这一条在内往后查5条，也就是1-5 #3.查看6-10列 mysql> select id,name from staff order by id asc limit 5,5; +----+------------+ | id | name | +----+------------+ | 6 | jing | | 7 | xin | | 8 | xiae | | 9 | 歪个 | | 10 | 丫的 | +----+------------+ 5 rows in set (0.00 sec) #从5开始，即先查询出第6条，然后包含在这一条在内让后查5条，也就是6-10
2.多表查询
#多表查询的语法
SELECT 字段列表 FROM 表1 INNER|LEFT|RIGHT JOIN 表2 ON 表1.字段 = 表2.字段;
#数据准备：准备两张表，部门表（department）、员工表（employee）
create table department( id int, name varchar(20) ); create table employee( id int primary key auto_increment, name varchar(20), sex enum('male','female') not null default 'male', age int, dep_id int ); #插入数据 insert into department values (200,'技术'), (201,'人力资源'), (202,'销售'), (203,'运营'); insert into employee(name,sex,age,dep_id) values ('guo','male',18,200), ('ke','female',48,201), ('wu','male',38,201), ('liu','female',28,202), ('zhang','male',18,200), ('xiao','female',18,204) ; #查看表结构 mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ 4 rows in set (0.00 sec) mysql> select * from employee; +----+-------+--------+------+--------+ | id | name | sex | age | dep_id | +----+-------+--------+------+--------+ | 1 | guo | male | 18 | 200 | | 2 | ke | female | 48 | 201 | | 3 | wu | male | 38 | 201 | | 4 | liu | female | 28 | 202 | | 5 | zhang | male | 18 | 200 | | 6 | xiao | female | 18 | 204 | +----+-------+--------+------+--------+ 6 rows in set (0.00 sec)
#提示：观察两张表，发现department表中id=203部门在employee中没有对应的员工，发现employee中id=6的员工在department表中没有对应关系
#查看两个表的交叉连接
mysql> select * from employee,department
2.1.外链接操作
#包括：内连接、左连接、右连接、全外连接
#1.内连接：符合条件查询，只连接匹配的行
#查询员工对应的部门
#找两张表共有的部分 mysql> select * from employee inner join department on employee.dep_id = department.id; +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | guo | male | 18 | 200 | 200 | 技术 | | 2 | ke | female | 48 | 201 | 201 | 人力资源 | | 3 | wu | male | 38 | 201 | 201 | 人力资源 | | 4 | liu | female | 28 | 202 | 202 | 销售 | | 5 | zhang | male | 18 | 200 | 200 | 技术 | +----+-------+--------+------+--------+------+--------------+ 5 rows in set (0.00 sec)
#2.左连接：优先显示左边记录
#找出左表所有员工信息，包括没有部门的员工
mysql> select * from employee left join department on employee.dep_id = department.id; +----+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+-------+--------+------+--------+------+--------------+ | 1 | guo | male | 18 | 200 | 200 | 技术 | | 5 | zhang | male | 18 | 200 | 200 | 技术 | | 2 | ke | female | 48 | 201 | 201 | 人力资源 | | 3 | wu | male | 38 | 201 | 201 | 人力资源 | | 4 | liu | female | 28 | 202 | 202 | 销售 | | 6 | xiao | female | 18 | 204 | NULL | NULL | +----+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec)
#3.右连接：优先显示右边记录
#以右表为准，找出所有部门信息，包括没有员工的部门
mysql> select * from employee right join department on employee.dep_id = department.id; +------+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-------+--------+------+--------+------+--------------+ | 1 | guo | male | 18 | 200 | 200 | 技术 | | 2 | ke | female | 48 | 201 | 201 | 人力资源 | | 3 | wu | male | 38 | 201 | 201 | 人力资源 | | 4 | liu | female | 28 | 202 | 202 | 销售 | | 5 | zhang | male | 18 | 200 | 200 | 技术 | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+-------+--------+------+--------+------+--------------+ 6 rows in set (0.00 sec)
#4.全外连接：使用union连接
#显示左右两个表全部记录
mysql> select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id; +------+-------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+-------+--------+------+--------+------+--------------+ | 1 | guo | male | 18 | 200 | 200 | 技术 | | 5 | zhang | male | 18 | 200 | 200 | 技术 | | 2 | ke | female | 48 | 201 | 201 | 人力资源 | | 3 | wu | male | 38 | 201 | 201 | 人力资源 | | 4 | liu | female | 28 | 202 | 202 | 销售 | | 6 | xiao | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+-------+--------+------+--------+------+--------------+ 7 rows in set (0.00 sec)
2.2.符合条件查询
#例一：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25，即找出年龄大于25岁的员工以及员工所在的部门
mysql> select employee.name,department.name from employee,department where employee.dep_id = department.id and age > 25; +------+--------------+ | name | name | +------+--------------+ | ke | 人力资源 | | wu | 人力资源 | | liu | 销售 | +------+--------------+ 3 rows in set (0.01 sec)
#例二：以内连接的方式查询employee和department表，并且以age字段的升序方式显示。
mysql> select employee.name,department.name from employee,department where employee.dep_id = department.id and age > 25 order by age asc; +------+--------------+ | name | name | +------+--------------+ | liu | 销售 | | wu | 人力资源 | | ke | 人力资源 | +------+--------------+ 3 rows in set (0.00 sec)
2.3.子查询
1.子查询是将一个查询语句嵌套到另一个查询语句中 2.内层查询语句的查询结果，可以为外层查询语句提供查询条件 3.子查询中可以包含：IN,NOT IN,ANY,ALL,EXISTS和NOT EXISTS等关键字 4.还可以包含比较运算符：=,!=,>,<等
#1.带in关键字的子查询
#例子：
#1.查询平均年龄在25岁以上的部门名 #思路：看到查平均年龄就是要分组，然后先查询出员工表里面大于25岁的，再作为结果给外层查询语句作为条件 mysql> select id,name from department where id in (select dep_id from employee group by dep_id having avg(age) > 25); +------+--------------+ | id | name | +------+--------------+ | 201 | 人力资源 | | 202 | 销售 | +------+--------------+ 2 rows in set (0.01 sec) #2.查看技术部员工姓名 #思路；先查部门表找出技术部，再作为条件查员工表, mysql> select name from employee where dep_id in (select id from department where name='技术'); +-------+ | name | +-------+ | guo | | zhang | +-------+ 2 rows in set (0.00 sec) #3.查看不足1人的部门名 mysql> select name from department where id not in(select dep_id from employee grroup by dep_id); +--------+ | name | +--------+ | 运营 | +--------+ 1 row in set (0.00 sec)
#2.带比较运算符的字查询
#例子：
#1.查询大于所有人平均年龄的员工名与年龄 #思路：先查询出所有人的平均年龄，然后再和原来的员工表进行比较 mysql> select name,age from employee where age > (select avg(age) from employee); +------+------+ | name | age | +------+------+ | ke | 48 | | wu | 38 | +------+------+ 2 rows in set (0.00 sec) #2.查询大于部门内平均年龄的员工名、年龄 思路： （1）先对员工表(employee)中的人员分组(group by)，查询出dep_id以及平均年龄。 (2)将查出的结果作为临时表,再对根据临时表的dep_id和employee的dep_id作为筛选条件将employee表和临时表进行内连接。 (3)最后再将employee员工的年龄是大于平均年龄的员工名字和年龄筛选。 mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
#3.带EXISTS关键字的子查询
#exists关键字表示存在，使用exists关键字时，内层查询不返回查询的记录，而是返回一个真假值，True或False，当返回True时，外层查询语句将进行查询，反之不进行查询
#例子：
#1.查询部门表里面有id=200的才会查询出外层的 mysql> select * from employee where exists (select id from department where id = '200'); +----+-------+--------+------+--------+ | id | name | sex | age | dep_id | +----+-------+--------+------+--------+ | 1 | guo | male | 18 | 200 | | 2 | ke | female | 48 | 201 | | 3 | wu | male | 38 | 201 | | 4 | liu | female | 28 | 202 | | 5 | zhang | male | 18 | 200 | | 6 | xiao | female | 18 | 204 | +----+-------+--------+------+--------+ 6 rows in set (0.00 sec) #2.如果查询部门表里面没有id=204的，所有不会查询外层的 mysql> select * from employee where exists (select id from department where id =204); Empty set (0.00 sec)
