mysql sql 部分

mysql sql 部分"/>

mysql sql 部分

1 基础查询

select 查询列表 from 表名

查询列表可以是：表中的字段、常量值、表达式、函数；

1.1 查询表中的单个字段

select name from user;

查询 user 表中的 name 字段；

1.2 查询表中的多个字段

select user,sex,email from user;

1.3 查询表中的所有字段

select * from user;

1.7 起别名

SELECT 100%98 AS result;
SELECT `name` AS 姓名, `sex` AS 性别 
FROM `user`;

• 100%98 是表达式；
• 如果多表之间出现重复的字段名，通过取别名可以避免重复；

1.8 去重（DISTINCT）

案例：查询员工表中设计到的所有的部门编号；

SELECT DISTINCT department_id 
FROM employees;

1.9 +号的作用

sql 中的 + 号只有运算符的功能，不能作为连接符使用；
案例：查询员工名和姓连接成一个字段，并显示为姓名；

• 不能使用加号进行连接，只能使用 concat 进行拼接；

SELECT CONCAT(last_name, first_name) AS 姓名 
FROM employees;

1.10 显示表的结构

DESC departments;

1.11 IFNULL

• 由于 null 值和所有其它的值进行拼接，结果都是 null，所以需要先进行判断，然后再进行拼接；
• IFNULL(commission_pct, 0) 获取 commission_pct 的值，如果值为 null，则将值设为 0；

SELECTIFNULL(commission_pct, 0) as result,commission_pct
fromemployee// 应用实例
SELECTCONCAT(first_name, last_name, ',', job_id, ',', IFNULL(commission_pct, 0)) AS out_put
FROMemployees;

2 条件查询

select查询列表
from表名
where筛选条件

2.1 按条件表达式筛选

• 条件运算符：> < = != >= <=；

// 查询工资 > 12000 的员工信息
SELECT * 
FROM employees 
WHERE salary > 12000;// 查询部门编号不等于 90 号的员工名和部门编号
SELECT last_name, department_id 
FROM employees
WHEREdepartment_id != 90;

2.2 按逻辑表达式的筛选

• 逻辑运算符：&& || !
• 更推荐：and or not

SELECTlast_name, salary, commission_pct
FROMemployees
WHEREsalary >= 10000
ANDsalary <= 20000;// 查询部门编号不是在 90 到 110 之间，或者工资高于 15000 的员工信息
SELECT*
FROMemployees
WHEREdepartment_id < 90
ORdepartment_id > 110
ORsalary > 15000;

2.3 模糊查询

• 1 like
  • 一般和通配符搭配使用，通配符包括：
  • %：任意多个字符，包含 0 个字符；
  • _：任意单个字符；

// 查询员工名中包含字符 a 的员工信息
SELECT*
FROMemployees
WHERElast_name LIKE '%a%';// 查询员工名中第三个字符为 e 的员工名和工资
SELECTlast_name, salary
FROMemployees
WHERElast_name LIKE '__e%';// 查询员工名中第二个字符为_的员工名
SELECTlast_name, salary
FROMemployees
WHERElast_name LIKE '_\_%';// 使用 ESCAPE 对 $ 进行转义
SELECTlast_name, salary
FROMemployees
WHERElast_name LIKE '_$_%' ESCAPE '$';

• 2 between and
  • 包含临界值，如下面就包含 100 和 200；
  • 临界值颠倒的效果是完全不同的；

// 查询员工编号在 100 到 120 之间的员工信息
SELECT*
FROMemployees
WHEREemployee_id BETWEEN 100 AND 120;

• 3 in
  • 含义：判断某字段的值是否属于 in 列表中的某一项；
  • 特点：in 列表（括号内）的值类型必须统一或兼容（可以相互转换）；
  • 案例：查询员工的工种编号是 IT_PROG、AD_VP 中的一个的员工的名字和工种编号

SELECTlast_name,job_id
FROMemployees
WHEREjob_id IN ('IT_PROG', 'AD_VP')

• 4 is null, is not null
  • 案例：查询有奖金的员工名和奖金率

SELECTlast_name,commission_pct
FROMemployees
WHEREcommission_pct IS NOT NULLSELECTlast_name,commission_pct
FROMemployees
WHEREcommission_pct IS NOT NULL

• 5 安全等于 <=>
  • 案例2：查询工资为 12000 的员工；
  • 用的不多；

SELECTlast_name,commission_pct
FROMemployees
WHEREsalary <=> 12000;

3 排序查询

语法：

• desc 降序
• asc 升序

SELECT查询列表
FROM表
WHERE筛选条件
ORDER BY排序列表 [asc|desc]SELECT*
FROMemployees
ORDER BYsalary DESC

结合表达式的搜索与排序

SELECT*,salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROMemployees
ORDER BYsalary * 12 * (1 + IFNULL(commission_pct, 0)) DESC// 按别名进行排序
SELECT*,salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROMemployees
ORDER BY年薪 DESC

先按工资进行升序排序，然后再根据员工编号进行降序排序

SELECT*
FROMemployees
ORDER BYsalary ASC,employee_id DESC

ORDER BY 一般放在查询语句的最后面，limit 子句除外；

SELECTlast_name, salary
FROM employees
WHEREsalary NOT BETWEEN 8000	AND 17000
ORDER BYsalary DESC

4 常见函数

• 1 功能：类似于 java 的方法，将一组逻辑语句封装在方法题中，对外暴露方法名
• 2 调用：select 函数名(实参列表) [form 表]
• 3 分类：
  • 单行函数，concat、length、ifnull 等
  • 分组函数，做统计使用

4.1 字符函数

• 1 length

• 2 concat 拼接字符串

  SELECT CONCAT(last_name, '_', first_name) FROM employess
  

• 3 upper、lower

• 4 substr
  或字符串中指定位数的字符

  SELECT SUBSTR('asdfghj', 4, 8) out_putSELECTCONCAT(UPPER(SUBSTR(last_name, 1, 1)), SUBSTR(last_name, 2, LENGTH(last_name))) 名,last_name
  FROMemployees
  

• 5 inster

  • 用于返回后一个字符串在前一个字符串中的起始索引；
  • 找不到则返回 0；

  SELECT INSTR('abcdefg', 'def') AS out_put
  

• 6 trim 去除前后字符串；

  • 默认情况下是清除空格；
  • 可以选择清除其它的；

  SELECT TRIM('a' FROM 'abba') AS out_put
  

• 7 replace 替换

  SELECT REPLACE('abcdef','abc','def') AS out_put
  

4.2 数学函数

• 1 round 四舍五入；

  SELECT ROUND(-1.557);
  // 四舍五入到小数点第二位
  SELECT ROUND(-1.557, 2);
  

• 2 ceil 向上取整；

• 3 floor 向下取整；

• 4 mod 取余

  SELECT MOD(10, 3)
  

4.3 日期函数

• 1 字符串转日期

  SELECT *
  FROM employees 
  WHERE hiredate = STR_TO_DATE('4-3 1992', '%m-%d %Y')
  

• 2 日期转字符串

  SELECT *, DATE_FORMAT(hiredate, '%Y年%m月%d日') AS 入职时间
  FROM employees 
  WHERE hiredate = STR_TO_DATE('4-3 1992', '%m-%d %Y')
  

4.4 流程控制函数

• 1 if 函数：

  SELECTlast_name, commission_pct, IF(commission_pct IS NULL, '没奖金', '有奖金') 备注
  FROMemployees
  

• 2 case 函数，第一种：

  • 类似于 switch case

  case 要判断的字段或表达式
  when 常量1 then 要显示的值1或语句1
  ...
  else 要显示的值n或语句n
  endSELECTsalary AS 原始工资, department_id,CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salary
  END AS 新工资
  FROMemployees
  

• 3 case 函数，第二种：

  case
  when 条件1 then 要显示的值1或语句1
  ...
  else 要显示的值n或语句n
  endSELECT salary,CASEWHEN salary>20000 THEN 'A'WHEN salary>15000 THEN 'B'WHEN salary>10000 THEN 'C'ELSE 'D'END AS 工资级别
  FROMemployees
  

5 分组函数

5.1 简单计算函数使用

sum 求和，avg 平均，max 最大，min 最小，count 计算个数

SELECT SUM(salary) FROM employees WHERE department_id=50
SELECT AVG(salary) FROM employees WHERE department_id=50
SELECT MIN(salary) FROM employees WHERE department_id=50
SELECT MAX(salary) FROM employees WHERE department_id=50
SELECT COUNT(employee_id) FROM employees WHERE department_id=50
SELECT COUNT(employee_id), AVG(salary), MAX(salary) FROM employees WHERE department_id=50

5.2 参数支持数据类型

• 1 COUNT 只统计不为空的个数；
• 2 sum、avg 用于处理数值型；
• 3 max、min、count 可以处理任何类型；
• 4 均忽略 NULL 值；

5.3 与 distinct 搭配

distinct 用于去重；

SELECT SUM(DISTINCT salary), SUM(salary) FROM employees;

5.4 count 函数的详细介绍

• 1 统计行数

  SELECT COUNT(*) FROM employees
  

5.5 GROUP BY 分组查询

SELECT  分组函数, 列
FROM  表
WHERE  筛选条件
GROUP BY  分组的对应列
ORDER BY  子句

5.5.1 简单分组

• 1 每个工种最高工资

SELECT MAX(salary), job_id
FROM employees
GROUP BY job_id

• 2 查询每个位置上的部门个数

SELECT COUNT(*), location_id
FROM departments
GROUP BY location_id

5.5.2 分组前筛选

• 1 查询邮箱中包含 a 字符的，每个部门的平均工资

SELECT AVG(salary), department_id
FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id

5.5.3 分组后筛选

HAVING：用于查询分组后结果集；

• 1 查询哪个部门的员工个数大于 2

SELECT department_id, COUNT(*) AS num
FROM employees
GROUP BY department_id
HAVING num > 2

• 2 查询每个有奖金，且高于12000的工种的工种编号和工资

SELECT job_id, MAX(salary) AS heighest
FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY job_id
HAVING heighest > 12000

5.5.4 按表达式或函数分组

按员工姓名长度分组，筛选员工个数大于 5 的长度分组，并按长度升序排序

SELECT LENGTH(last_name) AS name_length, COUNT(*) AS num
FROM employees
GROUP BY LENGTH(last_name)
HAVING num > 5
ORDER BY name_length

5.5.5 按多个字段分组

查询每个部门每个工种的员工的平均工资，并按工资升序排序

SELECT LENGTH(last_name) AS name_length, COUNT(*) AS num
FROM employees
GROUP BY LENGTH(last_name)
HAVING num > 5
ORDER BY name_length

6 连接查询

• sql99 语法

select 查询列表
from 表1 别名 [连接类型:inner/left/right/full/cross]
join 表2 别名
on 连接条件
[where 筛选条件]
[group by]
[having]
[order by]

6.1 sql92 内连接

6.1.1 等值连接

• 1 女生对应的男生名

SELECT name, boyName
FROM beauty, boys
WHERE beauty.boyfriend_id = boys.id

• 2 查询员工名和对应的部门名

SELECT last_name, department_name
FROM employees, departments
WHERE employees.department_id=departments.department_id

• 3 两表存在同名列的处理

SELECT last_name, employees.job_id, job_title
FROM employees, jobs
WHERE employees.job_id=jobs.job_id

• 4 带其它搜索条件

SELECT last_name, department_name, commission_pct*salary as commission
FROM employees, departments
WHERE employees.department_id=departments.department_idAND employeesmission_pct IS NOT NULLSELECT department_name, city
FROM departments, locations
WHERE departments.location_id = locations.location_idAND SUBSTR(locations.city,2,1)='o'

• 5 带分组
  • 查询每个城市的部门个数

  SELECT city, COUNT(*) AS department_count
  FROM departments, locations
  WHERE departments.location_id=locations.location_id
  GROUP BY city
  

  • 查询有奖金的每个部门的部门名和部门的领导编号和该部门的最低工资

  SELECT department_name, departments.manager_id, MIN(salary)
  FROM employees, departments
  WHERE departments.department_id=employees.department_idAND commission_pct IS NOT NULL
  GROUP BY department_name
  

• 6 三表连接
  • 查询员工名、部门名和所在城市

SELECT last_name, department_name, city, salary
FROM employees, departments, locations
WHERE employees.department_id=departments.department_idAND departments.location_id=locations.location_id
ORDER BY employees.salary

6.1.2 非等值连接

• 查询员工工资和工资级别

SELECT last_name, salary, job_grades.grade_level
FROM employees, job_grades
WHERE employees.salaryBETWEEN job_grades.lowest_salAND job_grades.highest_sal

6.1.3 自连接

SELECT e.employee_id, e.last_name, m.employee_id AS manager_id, m.last_name AS manager_name
FROM employees AS e, employees AS m
WHERE e.manager_id=m.employee_id

6.1.4 作业

• 1 选择 city 在 toronto 工作的员工的 last_name, job_id, department_id, department_name, city

SELECT last_name, job_id, employees.department_id, department_name, locations.city AS city
FROM employees, departments, locations
WHERE employees.department_id=departments.department_idAND departments.location_id=locations.location_idAND locations.city='Toronto'

• 2 选择所有有奖金的员工的

SELECT last_name, department_name, locations.location_id, city, commission_pct
FROM employees, departments, locations
WHERE commission_pct IS NOT NULLAND departments.location_id=locations.location_id

6.2 sql99 内连接

select 查询列表
from 表1 别名
inner join 表2 别名
on 连接条件

6.2.1 等值连接

inner 可以省略

• 1 查询员工和部门名

SELECT last_name, department_name
FROM employees
INNER JOIN departments
ON employees.department_id=departments.department_id

• 2 查询名字中包含 e 的员工名和工种名

SELECT last_name, job_title
FROM employees
INNER JOIN jobs
ON employees.job_id=jobs.job_id
WHERE employees.last_name LIKE '%e%'

• 3 查询部门个数大于 3 的城市名和部门个数

SELECT COUNT(*) AS department_count, city
FROM departments
INNER JOIN locations
ON departments.location_id=locations.location_id
GROUP BY departments.location_id
HAVING department_count > 3

• 4

SELECT department_name, COUNT(*) AS empolyee_count
FROM employees
INNER JOIN departments
ON employees.department_id=departments.department_id
GROUP BY employees.department_id
HAVING empolyee_count > 3
ORDER BY empolyee_count DESC

• 5 三表连接

SELECT last_name, department_name, job_title
FROM employees
INNER JOIN departments ON employees.department_id=departments.department_id
INNER JOIN jobs ON employees.job_id=jobs.job_id
ORDER BY department_name DESC

6.2.2 非等值连接

• 1 查询员工工资级别

SELECT last_name, employee_id, salary, grade_level
FROM employees
INNER JOIN job_grades
ON employees.salary 
BETWEEN job_grades.lowest_sal 
AND job_grades.highest_sal

• 2 查询员工个数大于 2 的工资级别，并降序排列

SELECT employee_id, salary, grade_level, COUNT(*) AS employee_count
FROM employees
INNER JOIN job_grades
ON employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal
GROUP BY grade_level
HAVING employee_count > 2
ORDER BY employee_count DESC

6.2.3 自连接

• 1 查询员工名字和上级名字

SELECT e.last_name AS employee_name, m.last_name AS manager_name
FROM employees AS e
INNER JOIN employees AS m
ON e.manager_id=m.employee_id
WHERE e.last_name LIKE '%k%'

6.2 sql99 外连接

• 外连接用于查询一个表中存在，但在另一个表中不存在的数据；
• 特点：
  • 外连接的查询结果为主表中的所有记录
  • 左外连接，left join 左边的是主表；
  • 右外连接，right join 右边的是主表；

6.2.1 左外连接

• 查询男朋友不再男神表的女神名

SELECT beauty.name, boys.*
FROM beauty
LEFT JOIN boys
ON beauty.boyfriend_id = boys.id
WHERE boys.id IS NULL

• 查询没有员工的部门

SELECT employees.department_id, departments.department_name, employee_id
FROM departments 
LEFT JOIN employees
ON departments.department_id=employees.department_id
WHERE employee_id IS NULL

6.2.2 右外连接

• 查询男朋友不再男神表的女神名

SELECT beauty.name, boys.*
FROM boys
RIGHT JOIN beauty
ON beauty.boyfriend_id = boys.id
WHERE boys.id IS NULL

7 子查询

1 出现在其它语句中的 select 语句称为子查询或内查询；
2 外部的查询语句称为主查询或外查询；
3 子查询优先于主查询；

7.1 where 或 having 后面

• 1 子查询放在小括号内；
• 2 子查询一般放在条件的右侧；

7.1.1 标量子查询（单行子查询）

• 一般搭配单行操作符（> < >= <= !=）使用；
• 1 谁的工资比 Abel 高？

SELECT *
FROM employees
WHERE salary > (SELECT salaryFROM employeesWHERE last_name='Abel'
);

• 2 多子查询

SELECT last_name, job_id, salary
FROM employees
WHERE job_id=(SELECT job_id FROM employeesWHERE employee_id=141
)
AND salary>(SELECT salary FROM employeesWHERE employee_id=143
)

• 3 查询最低工资大于 50 号部门最低工资的部门 id 和其最低工资

SELECT employees.department_id, MIN(salary)
FROM employees
GROUP BY employees.department_id
HAVING MIN(salary)>(SELECT MIN(salary)FROM employeesWHERE employees.department_id=50
)

7.1.2 列子查询（多行子查询）

一般搭配多行操作符使用，IN/NOT IN、ANY/SOME、ALL；

• 返回 location_id 是 1400 或 1700 的部门中的所有员工姓名

SELECT last_name, department_name, location_id
FROM employees
LEFT JOIN departments
ON employees.department_id=departments.department_id
WHERE departments.location_id IN (1400, 1700)SELECT last_name
FROM employees
WHERE department_id IN(SELECT department_idFROM departmentsWHERE location_id IN(1400, 1700)
)

7.1.3 行子查询（多列多行）

很少使用

7.2 select 后面

• 1 查询各部门的员工数

SELECT departments.*, (SELECT COUNT(*)FROM employeesWHERE employees.department_id=departments.department_id
) AS count
FROM departments

• 2

SELECT (SELECT departments.department_nameFROM departmentsWHERE departments.department_id=employees.department_id
) AS department_name, employees.department_id
FROM employees
WHERE employees.employee_id=102

7.3 from 后面

• 将查询的结果集视为一张表，必须起别名；
• 1 查询各部门平均工资等级

SELECT department_avg.*, job_grades.grade_level
FROM (SELECT AVG(salary) avg, department_idFROM employeesGROUP BY department_id
) department_avg
INNER JOIN job_grades
ON department_avg.avg BETWEEN job_grades.lowest_sal AND job_grades.highest_sal

8 分页查询

select 查询列表
from 表
[join type 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段]
limit offest, size

• 1 查询前五条员工信息

SELECT * FROM employees LIMIT 0, 5

• 2 查询第 11 到 15 条

SELECT * FROM employees LIMIT 10, 15

• 3 有奖金的员工信息，并工资较高的前十名

SELECT employee_id, salary, last_name
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC
LIMIT 0, 10

测试题

• 1 查询年龄 > 18 的所有学生的姓名和年级名称

SELECT student.studentname, student.majorid, major.majorname, (YEAR(NOW())-DATE_FORMAT(student.borndate,'%Y')) AS age, borndate
FROM student
LEFT JOIN major
ON major.majorid=student.majorid
WHERE (YEAR(NOW())-DATE_FORMAT(student.borndate,'%Y')) > 18

• 2 学生最小年龄大于 20 的年级

SELECT MIN((YEAR(NOW())-DATE_FORMAT(student.borndate,'%Y'))) AS min_age, major.majorname
FROM student
LEFT JOIN major
ON major.majorid=student.majorid
GROUP BY student.majorid
HAVING min_age > 20

9 联合查询

将多条查询语句结果合并成一个结果；
场景：来自于多个表，且没有连接关系的查询需求；
特点：

• 1 要求多条查询语句的查询列数是一致的；
• 2 要求多条查询语句的查询的每一列的类型和顺序应一致；
• 3 UNION 会进行去重，而 UNION ALL 不会；

// 语法：
查询语句 1
union
查询语句 2
union
...

• 1 查询部门编号 > 90 或邮箱包含 a 的员工信息；

SELECT * FROM employees WHERE email LIKE '%a%'
UNION
SELECT * FROM employees WHERE department_id>90;