#单表查询语法
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] ...
}
#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:分组是发生在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.执行优先级从高到低: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;
#提示: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.查看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
#多表查询的语法
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
#包括:内连接、左连接、右连接、全外连接
#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)
#例一:以内连接的方式查询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)
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)