数据库多表查询、常见函数、子查询、分页查询和DML语言

一、多表查询

#join连接
– 内连接 [inner] join on
– 外连接
• 左外连接 left [outer] join on
• 右外连接 right [outer] join on
#外连接：案例1：查询哪个部门没有员工
#左外
SELECT d.,e.employee_id
FROM t_mysql_departments d
LEFT OUTER JOIN t_mysql_employees e
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

#右外
SELECT d.,e.employee_id
FROM t_mysql_employees e
RIGHT OUTER JOIN t_mysql_departments d
ON d.department_id = e.department_id
WHERE e.employee_id IS NULL;

#内连接：自连接
#查询员工的名字、上级的名字
SELECT e.last_name,m.last_name
FROM t_mysql_employees e
JOIN t_mysql_employees m
ON e.manager_id= m.employee_id;

#等值连接
#案例：查询 每个工种 的 工种名和员工的个数，并且 按员工个数降序
SELECT job_title,COUNT()
FROM t_mysql_employees e,t_mysql_jobs j
WHERE e.job_id=j.job_id
GROUP BY job_title
ORDER BY COUNT() DESC;

#非等值连接
#案例1：查询员工的工资和工资级别
SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal
AND g.grade_level=‘A’;

#多表查询
#1.显示所有员工的姓名，部门号和部门名称。
#USE myemployees;
SELECT last_name,d.department_id,department_name
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.department_id = d.department_id;

#2.查询90号部门员工的job_id和90号部门的location_id
SELECT job_id,location_id
FROM t_mysql_employees e,t_mysql_departments d
WHERE e.department_id=d.department_id
AND e.department_id=90;

#3. 选择所有有奖金的员工的
last_name , department_name , location_id , city
SELECT last_name , department_name , l.location_id , city
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND e.commission_pct IS NOT NULL;

#4.选择city在Toronto工作的员工的
#last_name , job_id , department_id , department_name
SELECT last_name , job_id , d.department_id , department_name
FROM t_mysql_employees e,t_mysql_departments d ,t_mysql_locations l
WHERE e.department_id = d.department_id
AND d.location_id=l.location_id
AND city = ‘Toronto’;

#5.查询每个工种、每个部门的部门名、工种名和最低工资
SELECT department_name,job_title,MIN(salary) 最低工资
FROM t_mysql_employees e,t_mysql_departments d,t_mysql_jobs j
WHERE e.department_id=d.department_id
AND e.job_id=j.job_id
GROUP BY department_name,job_title;

#6.查询每个国家下的部门个数大于2的国家编号
SELECT country_id,COUNT(*) 部门个数
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.location_id=l.location_id
GROUP BY country_id
HAVING 部门个数>2;

#7、选择指定员工的姓名，员工号，以及他的管理者的姓名和员工号，结果类似于下面的格式
#employees Emp# manager Mgr#
#kochhar 101 king 100
SELECT e.last_name employees,e.employee_id “Emp#”,m.last_name manager,m.employee_id “Mgr#”
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.manager_id = m.employee_id
AND e.last_name=‘kochhar’;

二、常见函数

1、字符函数

作用	函数	结果
转小写	LOWER(‘SQL Course’)	sql course
转大写	UPPER(‘SQL Course’)	SQL COURSE
拼接	CONCAT(‘Hello’, ‘World’)	HelloWorld
截取	SUBSTR(‘HelloWorld’,1,5)	Hello
长度	LENGTH(‘HelloWorld’)	10
字符出现索引值	INSTR(‘HelloWorld’, ‘W’)	6
字符截取后半段	TRIM(‘H’ FROM ‘HelloWorld’)	elloWorld
字符替换	REPLACE(‘abcd’,‘b’,‘m’)	amcd

2、数字函数

作用	函数	结果
四舍五入	ROUND(45.926, 2)	45.93
截断	TRUNC(45.926, 2)	45.92
求余	MOD(1600, 300)	100

3、日期函数

作用	函数	结果
获取当前日期	now()
将日期格式的字符转换成指定格式的日期	STR_TO_DATE(‘9-13-1999’,’%m-%d-%Y’)	1999-09-13
将日期转换成字符	DATE_FORMAT(‘2018/6/6’,‘%Y年%m月%d日’)	2018年06月06日

#1. 显示系统时间(注：日期+时间)
SELECT NOW();

#2. 查询员工号，姓名，工资，以及工资提高百分之20%后的结果（new salary）
SELECT employee_id,last_name,salary,salary1.2 “new salary”
FROM t_mysql_employees;

#3. 将员工的姓名按首字母排序，并写出姓名的长度（length）
SELECT LENGTH(last_name) 长度,SUBSTR(last_name,1,1) 首字符,last_name
FROM t_mysql_employees
ORDER BY 首字符;

#4. 做一个查询，产生下面的结果
#<last_name> earns monthly but wants <salary3>
#Dream Salary
#King earns 24000 monthly but wants 72000
SELECT CONCAT(last_name,’ earns ‘,salary,’ monthly but wants ',salary*3) AS “Dream Salary”
FROM t_mysql_employees
WHERE salary=24000;

#5. 使用case-when，按照下面的条件：
#job grade
#AD_PRES A
#ST_MAN B
#IT_PROG C
#SA_REP D
#ST_CLERK E
#产生下面的结果
#Last_name Job_id Grade
#king AD_PRES A
SELECT last_name,job_id AS job,
CASE job_id
WHEN ‘AD_PRES’ THEN ‘A’
WHEN ‘ST_MAN’ THEN ‘B’
WHEN ‘IT_PROG’ THEN ‘C’
WHEN ‘SA_PRE’ THEN ‘D’
WHEN ‘ST_CLERK’ THEN ‘E’
END AS Grade
FROM t_mysql_employees
WHERE job_id = ‘AD_PRES’;

三、子查询

含义：
出现在其他语句中的select语句，称为子查询或内查询
外部的查询语句，称为主查询或外查询
分类：
按子查询出现的位置：
select后面：
仅仅支持标量子查询
from后面：
支持表子查询
where或having后面：★
标量子查询（单行） √
列子查询 （多行） √
行子查询
exists后面（相关子查询）
表子查询
按结果集的行列数不同：
标量子查询（结果集只有一行一列）
列子查询（结果集只有一列多行）
行子查询（结果集有一行多列）
表子查询（结果集一般为多行多列）
#1. 查询和Zlotkey相同部门的员工姓名和工资
#①查询Zlotkey的部门
SELECT department_id
FROM t_mysql_employees
WHERE last_name = ‘Zlotkey’

#②查询部门号=①的姓名和工资
SELECT last_name,salary
FROM t_mysql_employees
WHERE department_id = (
SELECT department_id
FROM t_mysql_employees
WHERE last_name = ‘Zlotkey’
)

#2.查询工资比公司平均工资高的员工的员工号，姓名和工资。
#①查询平均工资
SELECT AVG(salary)
FROM t_mysql_employees

#②查询工资>①的员工号，姓名和工资。
SELECT last_name,employee_id,salary
FROM t_mysql_employees
WHERE salary>(
SELECT AVG(salary)
FROM t_mysql_employees
);

#3.查询各部门中工资比本部门平均工资高的员工的员工号, 姓名和工资
#①查询各部门的平均工资
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id

#②连接①结果集和employees表，进行筛选
SELECT employee_id,last_name,salary,e.department_id
FROM t_mysql_employees e
INNER JOIN (
SELECT AVG(salary) ag,department_id
FROM t_mysql_employees
GROUP BY department_id
) ag_dep
ON e.department_id = ag_dep.department_id
WHERE salary>ag_dep.ag ;

#4. 查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
#①查询姓名中包含字母u的员工的部门
SELECT DISTINCT department_id
FROM t_mysql_employees
WHERE last_name LIKE ‘%u%’

#②查询部门号=①中的任意一个的员工号和姓名
SELECT last_name,employee_id
FROM t_mysql_employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM t_mysql_employees
WHERE last_name LIKE ‘%u%’
);

#5. 查询在部门的location_id为1700的部门工作的员工的员工号
#①查询location_id为1700的部门
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id = 1700

#②查询部门号=①中的任意一个的员工号
SELECT employee_id
FROM t_mysql_employees
WHERE department_id =ANY(
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id = 1700
);

#6.查询管理者是King的员工姓名和工资
#①查询姓名为king的员工编号
SELECT employee_id
FROM t_mysql_employees
WHERE last_name = ‘K_ing’

#②查询哪个员工的manager_id = ①
SELECT last_name,salary
FROM t_mysql_employees
WHERE manager_id IN(
SELECT employee_id
FROM t_mysql_employees
WHERE last_name = ‘K_ing’
);

#7.查询工资最高的员工的姓名，要求first_name和last_name显示为一列，列名为 姓.名
#①查询最高工资
SELECT MAX(salary)
FROM t_mysql_employees

#②查询工资=①的姓.名
SELECT CONCAT(first_name,last_name) “姓.名”
FROM t_mysql_employees
WHERE salary=(
SELECT MAX(salary)
FROM t_mysql_employees
);

四、分页查询

limit 【offset,】size;
应用场景：当要显示的数据，一页显示不全，需要分页提交sql请求
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit 【offset,】size;
offset要显示条目的起始索引（起始索引从0开始）
size 要显示的条目个数
特点：
①limit语句放在查询语句的最后
②公式
要显示的页数 page，每页的条目数size
#案例1：查询前五条员工信息
SELECT * FROM t_mysql_employees LIMIT 0,5;
SELECT * FROM t_mysql_employees LIMIT 5;

#案例2：查询第11条——第25条
SELECT * FROM t_mysql_employees LIMIT 10,15;

#案例3：有奖金的员工信息，并且工资较高的前10名显示出来
SELECT
*
FROM
t_mysql_employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 10 ;

五、DML语言

数据操纵语言) 可以在下列条件下执行: – 向表中插入数据
– 修改现存数据
– 删除现存数据
• 事务是由完成若干项工作的DML语句组成的
#插入数据
#隐式方式: 在列名表中省略该列的值。
INSERT INTO t_mysql_departments (department_id, department_name )
VALUES (30, ‘Purchasing’);
#显示方式: 在VALUES 子句中指定空值。
INSERT INTO t_mysql_departments
VALUES (100, ‘Finance’, NULL, NULL);
#拷贝
CREATE TABLE map
as
SELECT *
FROM t_mysql_boys WHERE 1=2
INSERT INTO map
SELECT *
FROM t_mysql_boys

#修改
#案例 1：修改张无忌的女朋友的手机号为114
UPDATE t_mysql_boys bo
INNER JOIN t_mysql_beauty b ON bo.id=b.boyfriend_id
SET b.phone=‘119’,bo.userCP=1000
WHERE bo.boyName=‘张无忌’;


#删除数据
#案例：删除张无忌的女朋友的信息
DELETE b
FROM t_mysql_beauty b
INNER JOIN t_mysql_boys bo ON b.boyfriend_id = bo.id
WHERE bo.boyName=‘张无忌’;
#案例：删除黄晓明的信息以及他女朋友的信息
DELETE b,bo
FROM t_mysql_beauty b
INNER JOIN t_mysql_boys bo ON b.boyfriend_id=bo.id
WHERE bo.boyName=‘黄晓明’;