MySQL基础(二)——DQL
视频学习来源:MySQL_基础+高级篇- 数据库 -sql -mysql教程_mysql视频_mysql入门_尚硅谷;
作者:木子六日;
MySQL版本:5.7.33;
基础查询
USE myemployees;
# 基础查询
# 查询单个字段
SELECT
last_name
FROM
employees;
# 查询多个字段
SELECT
last_name,
first_name,
email
FROM
employees;
# 查询所有字段
SELECT
*
FROM
employees;
# 查询常量值
SELECT
100;
SELECT
'LJJ';
# 查询表达式
SELECT
100 + 58;
# 查询函数
SELECT
VERSION();
# 起别名
SELECT
VERSION() AS 版本;
SELECT
last_name AS 姓,
first_name AS 名
FROM
employees;
/*其实as也可以不写的*/
SELECT
last_name 姓,
first_name 名
FROM
employees;
# 如果把别名中含有特殊字符或关键字,用引号引起来即可
SELECT
salary AS "out put"
FROM
employees;
# 去重(给需要去重的字段加上distinct关键字修饰)
SELECT DISTINCT
department_id
FROM
employees;
# +号的作用
/*
在msyql中,+仅仅作为运算符,不能够连接字符;
*/
SELECT
8 + 9;
#结果为17
SELECT
'34' + 6;
#如果有字符型,会将字符转为数值后再进行加法运算,结果为40
SELECT
'ljj' + 3;
#如果字符无法转化为数值型,就会转化为0,结果为3
SELECT
'ljj' + 'ljj';
#结果为0
SELECT
NULL + 34;
#如果有null值,结果永远为null
条件查询
USE myemployees;
# 条件查询
# >,<,>=,<=,<>,!=
SELECT
*
FROM
employees
WHERE
salary > 15000;
SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <> 90;
# and,or,not
SELECT
last_name,
department_id,
commission_pct
FROM
employees
WHERE
NOT (
department_id >= 90
AND department_id <= 110
)
OR salary > 15000;
#like
# %表示任意多个字符(可以是0个)
# _表示任意单个字符
# 对%和_的转义使用\即可
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '%a%';
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '__n_l%';
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_\_%';
# 如果我们不希望使用\来进行转义,也可以使用escape关键字来自定义转义字符
SELECT
last_name
FROM
employees
WHERE
last_name LIKE '_b_%' ESCAPE 'b';
# between and
/*
可以使sql更加简洁;
是包含左右区间的;
左小右大;
*/
SELECT
last_name,
salary
FROM
employees
WHERE
salary BETWEEN 12000
AND 20000;
# in
SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN (
'IT_PROG',
'AD_VP',
'AD_PRES'
);
# is null(判断null值不能使用 = null来进判断)
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NULL;
# is not null
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct IS NOT NULL;
# <=>
# 这是安全等于,=无法判断null,但是<=>可以判断null
SELECT
last_name,
commission_pct
FROM
employees
WHERE
commission_pct <=> NULL;
排序查询
USE myemployees;
# 排序查询
/*
asc 升序
desc 降序
默认是升序
order by 一般都是放在最后面的,除了limit以外
*/
SELECT
salary
FROM
employees
ORDER BY
salary DESC;
# 查询部门编号大于90并且按照入职先后顺序进行排序
SELECT
*
FROM
employees
WHERE
department_id > 90
ORDER BY
hiredate;
# 也可以按照表达式进行排序,比如以下sql是按照年薪排序
SELECT
*
FROM
employees
ORDER BY
12 * salary * (1 + IFNULL(commission_pct, 0)) DESC;
# order by后面是支持别名的
SELECT
last_name,
12 * salary * (1 + IFNULL(commission_pct, 0)) AS money
FROM
employees
ORDER BY
money DESC;
# 也可以按照函数进行排序
SELECT
last_name
FROM
employees
ORDER BY
LENGTH(last_name);
# 也可以按照多个字段进行排序,比如先按照薪水降序,如果薪水相同就按照员工编号排序
SELECT
salary,
employee_id
FROM
employees
ORDER BY
salary DESC,
employee_id;
分组查询
USE myemployees;
# 分组查询
# 如果筛选既可以分组前进行也可以分组后进行,那么最好分组前进行,效率会高一些
# 案例一:查询每个工种的最高工资
SELECT
job_id AS 工种,
MAX(salary) AS 最高工资
FROM
employees
GROUP BY
job_id;
# 案例二:查询每个地方的部门个数
SELECT
location_id AS 位置,
count(*)
FROM
departments
GROUP BY
location_id;
# 案例三:查询邮箱中包含a字符的每个部门的平均工资(分组前筛选,直接加where)
SELECT
department_id AS 部门,
AVG(salary) AS 邮箱中包含a的部门平均工资
FROM
employees
WHERE
email LIKE '%a%'
GROUP BY
department_id;
# 案例四:查询那个部门的员工个数大于2(分组后删选,使用having)
SELECT
department_id AS 部门id,
COUNT(*) AS 员工人数
FROM
employees
GROUP BY
department_id
HAVING
员工人数 > 2;
# 案例五:查询每个工种的有奖金的员工的最高工资>12000的工种编号和最高工资
SELECT
job_id AS 工种编号,
MAX(salary) AS 最高工资
FROM
employees
WHERE
commission_pct IS NOT NULL
GROUP BY
job_id
HAVING
最高工资 > 12000;
# 按函数进行分组
# 案例六:按照员工姓名长度分组,统计员工个数,筛选出个数大于5的
SELECT
LENGTH(last_name) AS 姓名长度,
COUNT(*) AS 员工个数
FROM
employees
GROUP BY
姓名长度
HAVING
员工个数 > 5;
# 按多个字段进行分组
# 案例七: 按照每个部门的每个工种进行分组,求平均工资,并按照工资降序
SELECT
department_id,
job_id,
AVG(salary) AS 平均薪资
FROM
employees
GROUP BY
department_id,
job_id
ORDER BY
平均薪资 DESC;
sql92语法的等值连接
USE myemployees;
# sql92语法:等值连接
# 案例一:查询员工名、工种号、工种名
SELECT
e.last_name,
e.job_id,
j.job_title
FROM
employees AS e,
jobs AS j
WHERE
e.job_id = j.job_id;
# 案例二:查询每个城市的部门个数
SELECT
city,
count(*)
FROM
locations l,
departments d
WHERE
l.location_id = d.location_id
GROUP BY
city;
# 案例三:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
d.department_name,
d.manager_id,
MIN(e.salary)
FROM
departments d,
employees e
WHERE
e.department_id = d.department_id
AND e.commission_pct IS NOT NULL
GROUP BY
d.department_name,
d.manager_id;
# 案例四:查询每个工种的工种名和员工个数,并按照员工个数降序
SELECT
j.job_title 工种,
count(*) 员工个数
FROM
jobs j,
employees e
WHERE
j.job_id = e.job_id
GROUP BY
j.job_title
ORDER BY
员工个数 DESC;
sql92语法的非等值连接和自连接
# sql92语法的非等值连接与自连接
# 非等值连接案例:查询员工的工资级别
SELECT
e.first_name,
e.salary,
j.grade_level
FROM
employees e,
job_grades j
WHERE
e.salary BETWEEN j.lowest_sal
AND j.highest_sal;
# 自连接案例:查询员工名和他的上级的员工名
SELECT
e1.last_name AS 员工,
e2.last_name 上级
FROM
employees e1,
employees e2
WHERE
e1.manager_id = e2.employee_id;
sql99语法的内连接
USE myemployees;
# sql99语法还是6用的更多一点,这个更重要一些
# 内连接,其实就是92语法中的等值连接、非等值连接和自连接的总称
# 这里把前面92语法的案例用99语法再写一遍
# inner可以省略
# 案例一:查询员工名、工种号、工种名
SELECT
e.last_name,
e.job_id,
j.job_title
FROM
employees e
INNER JOIN jobs j ON e.job_id = j.job_id;
# 案例二:查询每个城市的部门个数
SELECT
l.city,
count(*)
FROM
locations l
INNER JOIN departments d ON l.location_id = d.location_id
GROUP BY
l.city #案例三:查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资
SELECT
d.department_name,
d.manager_id,
MIN(e.salary)
FROM
employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE
e.commission_pct IS NOT NULL
GROUP BY
d.department_name,
d.manager_id;
# 案例四:查询每个工种的工种名和员工个数,并按照员工个数降序
SELECT
j.job_title,
COUNT(*)
FROM
jobs j
INNER JOIN employees e ON j.job_id = e.job_id
GROUP BY
j.job_title
ORDER BY
COUNT(*) DESC;
# 案例五:查询员工的工资级别
SELECT
e.last_name,
e.salary,
j.grade_level
FROM
employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal
AND j.highest_sal;
# 案例六:查询员工名和他的上级的员工名
SELECT
e1.last_name,
e2.last_name
FROM
employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
sql99语法的外连接
USE myemployees;
# 外连接
/*
分为主表和从表,左外连接left左边是主表,右外连接right右边是主表;
外连接会查询所有主表记录,从表再通过连接条件和主表匹配,没匹配上就填null;
其结果相当于内连接的查询结果加上未匹配上的主表结果;
mysql不支持全外连接
这里提一嘴交叉连接,cross join,就是把两张表排列组合一下,没啥卵用
*/
# 案例一:查询所有没有男朋友的人
SELECT
beauty. NAME
FROM
beauty
LEFT JOIN boys ON beauty.boyfriend_id = boys.id
WHERE
boys.id IS NULL;
# 上面的案例改成右外连接的写法就是
SELECT
beauty. NAME
FROM
boys
RIGHT JOIN beauty ON beauty.boyfriend_id = boys.id
WHERE
boys.id IS NOT NULL;
# 案例二:查询那个部门没有员工
SELECT
d.department_name
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE
e.employee_id IS NULL;
子查询
USE myemployees;
# 子查询
# 子查询都放在小括号里面
# 将子查询放到where或having后面
# 案例一:谁的工资比Abel高?
SELECT
e.last_name
FROM
employees e
WHERE
e.salary > (
SELECT
salary
FROM
employees
WHERE
last_name = 'Abel'
);
# 案例二:查询job_id与141号员工相同,薪水比143号员工多的员工的姓名、job_id和工资
SELECT
e.last_name,
e.job_id,
e.salary
FROM
employees e
WHERE
e.job_id = (
SELECT
job_id
FROM
employees
WHERE
employee_id = 141
)
AND e.salary > (
SELECT
salary
FROM
employees
WHERE
employee_id = 143
);
# 案例三:查询公司工资最少的员工的姓名、job_id和薪水
SELECT
e.last_name,
e.job_id,
e.salary
FROM
employees e
WHERE
e.salary = (
SELECT
MIN(salary)
FROM
employees
);
# 案例四:查询最低工资大于50号部门的最低工资的部门和其最低工资
SELECT
e.department_id,
MIN(e.salary)
FROM
employees e
GROUP BY
department_id
HAVING
MIN(e.salary) > (
SELECT
MIN(salary)
FROM
employees
WHERE
department_id = 50
);
# 案例五:查询location_id是1400或1700的部门的所有员工
SELECT
e.last_name
FROM
employees e
WHERE
e.department_id IN (
SELECT
d.department_id
FROM
departments d
WHERE
d.location_id IN (1400, 1700)
);
# 上面的这个案例也可以用内连接做(用连接做往往比子查询效率高一些)
SELECT
e.last_name
FROM
employees e
JOIN departments d ON e.department_id = d.department_id
WHERE
d.location_id IN (1400, 1700);
# 案例六:查询员工编号最小并且工资最高的员工信息
SELECT
*
FROM
employees e
WHERE
(e.employee_id, e.salary) = (
SELECT
MIN(employee_id),
MAX(salary)
FROM
employees
) # 将子查询放到from后面,注意放在select后面的子查询其结果必须是一行一列
# 案例七:查询部门信息以及每个部门的员工个数
SELECT
d.*, (
SELECT
count(*)
FROM
employees e
WHERE
e.department_id = d.department_id
) 员工个数
FROM
departments d;
# 案例八:查询每个部门平均工资及其工资等级
SELECT
ag.部门id,
ag.avg_sal,
j.grade_level
FROM
(
SELECT
AVG(e.salary) avg_sal,
e.department_id 部门id
FROM
employees e
GROUP BY
e.department_id
) ag
JOIN job_grades j ON ag.avg_sal BETWEEN j.lowest_sal
AND j.highest_sal;
s # 放在exists后面的子查询(exist表示子查询是否有结果)
# 案例九:查询所有有员工的部门名
SELECT
d.department_name
FROM
departments d
WHERE
EXISTS (
SELECT
*
FROM
employees e
WHERE
e.department_id = d.department_id
);
分页查询
USE myemployees;
# 分页查询(一般都会放到最后)
# 案例一:查询前五条员工信息
SELECT
*
FROM
employees
LIMIT 0,
5;
# 或者(偏移量为0时可以省略)
SELECT
*
FROM
employees
LIMIT 5;
#案例二:查询第5条到第24条员工信息
SELECT
*
FROM
employees
LIMIT 4,
20;
联合查询
USE myemployees;
# 联合查询
SELECT
*
FROM
employees
WHERE
employee_id > 50
AND department_id < 1000;
# 上面这条语句用联合查询改写就是
SELECT
*
FROM
employees
WHERE
employee_id > 50
UNION
SELECT
*
FROM
employees
WHERE
department_id < 1000;
# 是不是感觉毫无意义?
# union联合查询主要是用在不同的表里面的,比如:
SELECT
*
FROM
t_cn
UNION
SELECT
*
FROM
t_ua;
# 联合查询的要求与特点:
/*
1.查询出来的列数要一样;
2.字段类型最好一样;
3.默认去重,如果不希望去重,可以使用union all;
*/
SELECT
*
FROM
t_cn
UNION ALL
SELECT
*
FROM
t_ua;