单表查询
简单查询
select * from 表名; 查看表的全部数据
select 字段 from 表名; 查看单个字段数据
select 字段,字段,字段 from 表名; 查看多个字段数据
select distinct 字段 from 表名; 对查出来的字段进行去重
select name,money*12 from 表名; 字段可以进行四则运算
select concat(字段,'字符串',字段) from 表名; 把数据以字段字符串字段的形式输出
输出的字段名很长，可以在from前加新字段名或 as 新字段名
select concat_ws('分隔符','字符串',字段,字段)简洁字段名 from 表名;
把数据以字符串分隔符字段分隔符字段的形式输出
case 类似于if
select
(
case
when 条件 then
结果
when name = 'Messi' then
concat(name,'_the best player')
else
concat(name, '_great player')
end
) as new_name
from
表名;
如果字段name是Messi，输出梅西是世界上最好的球员
否则输出name是优秀的球员
where约束
select * from 表 where 条件
范围查询
> < >= <= = !=/<>
between a and b
in (1,2,3,4) n选1
模糊查询
like
% 一个百分号代表任意长度的任意字符
'a%'
'%ing'
'%a%'
_ 一个下划线代表一个任意字符
'a__'
regexp
'^a'
'd+'
is is not
is null
is not null
逻辑运算
and
or
not
分组 group by
根据某个重复率较高的字段进行的
这个字段有多少种可能就分为多少组
能做到去重
一旦分组了就不能对具体某一条数据进行操作了，永远都是考虑这组xxx
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
group_concat : 只用来做最终的显示,不能作为中间结果操作其他数据
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(emp_name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(emp_name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
聚合
99.99%的情况都是和分组一起用的
如果没有和分组一起用,默认一整张表是一组
count(id) / count(*) 计数 :每个组对应几条数据
max 求最大值: 这个组中某字段的最大值
min 求最大值: 这个组中某字段的最小值
avg 求平均值
sum 求和值
过滤条件 having
就是一个对组进行筛选的条件
HAVING与WHERE不一样的地方在于
执行优先级从高到低：where > group by > having
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
查询排序 order by
order by 字段 对字段进行排序，默认升序
order by 字段 asc 对字段进行升序排列
order by 字段 desc 对字段进行降序排列
order by 字段1,字段2 两个字段升序排列
order by 字段 asc,字段2 desc 字段升序排列，字段2降序排列
限制查询的记录数 limit
1.显示分页 limit m,n 表示从m+1开始,取n条 limit 0,6 表示从1开始取6条 limit 6,6 表示从7开始取6条 limit 12,6 表示从13开始取6条 limit 18,6 表示从19开始取6条 2.取前n名 limit n m默认为0 跟order by一起用 limit n offset m :从m+1开始,取n条
多表查询
建表 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 ('egon','male',18,200), ('alex','female',48,201), ('wupeiqi','male',38,201), ('yuanhao','female',28,202), ('liwenzhou','male',18,200), ('jingliyang','female',18,204) ;
连表查询
把两张表连在一起，先计算两张表的笛卡尔积，再根据用户给出的条件进行筛选
原来是两张表，现在拼成一张表，所有的操作都像操作一张表一样
1 交叉连接：不适用任何匹配条件。生成笛卡尔积
mysql> select * from employee,department; +----+------------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+------------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 201 | 人力资源 | | 5 | liwenzhou | male | 18 | 200 | 202 | 销售 | | 5 | liwenzhou | male | 18 | 200 | 203 | 运营 | | 6 | jingliyang | female | 18 | 204 | 200 | 技术 | | 6 | jingliyang | female | 18 | 204 | 201 | 人力资源 | | 6 | jingliyang | female | 18 | 204 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | 203 | 运营 | +----+------------+--------+------+--------+------+--------------+
2 内连接：只连接匹配的行
找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果 department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
只显示两张表中互相匹配的项，其它不匹配的不显示 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+-----------+------+--------+--------------+ | id | name | age | sex | name | +----+-----------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | liwenzhou | 18 | male | 技术 | +----+-----------+------+--------+--------------+ 上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
3 外链接之左连接：优先显示左表全部记录
以左表为准，即找出所有员工信息，当然包括没有部门的员工 本质就是：在内连接的基础上增加左边有右边没有的结果
不管左表中是否匹配上，都会显示所有内容 mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+------------+--------------+ | id | name | depart_name | +----+------------+--------------+ | 1 | egon | 技术 | | 5 | liwenzhou | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | jingliyang | NULL | +----+------------+--------------+
4 外链接之右连接：优先显示右表全部记录
以右表为准，即找出所有部门信息，包括没有员工的部门 本质就是：在内连接的基础上增加右边有左边没有的结果
不管右表中是否匹配上，都会显示所有内容 mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+-----------+--------------+ | id | name | depart_name | +------+-----------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | liwenzhou | 技术 | | NULL | NULL | 运营 | +------+-----------+--------------+
5 全外连接：显示左右两个表全部记录
全外连接：在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 注意：mysql不支持全外连接 full JOIN 强调：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 | egon | male | 18 | 200 | 200 | 技术 | | 5 | liwenzhou | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | jingliyang | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+------------+--------+------+--------+------+--------------+ 注意 union与union all的区别：union会去掉相同的纪录
符合条件连接查询
示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;
示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
子查询
子查询是将一个查询语句嵌套在另一个查询语句中
内层语句的查询结果，可以为外层查询语句提供查询条件
子查询可以包含in、not in、exists等关键字和比较运算符
1 带IN关键字的子查询
查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
2 带比较运算符的子查询
比较运算符：=、!=、>、>=、<、<=、<> 查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from emp where age > (select avg(age) from emp); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ 2 rows in set (0.00 sec) 查询大于部门内平均年龄的员工名、年龄 select t1.name,t1.age from emp t1 inner join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
3 带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
department表中存在dept_id=203，Ture
mysql> select * from employee
-> where exists
-> (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
department表中存在dept_id=205，False
mysql> select * from employee
-> where exists
-> (select id from department where id=204);
Empty set (0.00 sec)