mysql sql 部分

最新推荐文章于 2024-11-14 09:38:36 发布

qq_42680313

最新推荐文章于 2024-11-14 09:38:36 发布

阅读量199

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/qq_42680313/article/details/108684108

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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；

SELECT
  IFNULL(commission_pct, 0) as result,
  commission_pct
from
  employee

// 应用实例
SELECT
  CONCAT(first_name, last_name, ',', job_id, ',', IFNULL(commission_pct, 0)) AS out_put
FROM
  employees;

2 条件查询

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

2.1 按条件表达式筛选

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

// 查询工资 > 12000 的员工信息
SELECT 
  * 
FROM 
  employees 
WHERE 
  salary > 12000;

// 查询部门编号不等于 90 号的员工名和部门编号
SELECT 
  last_name, department_id 
FROM 
  employees
WHERE
  department_id != 90;

2.2 按逻辑表达式的筛选

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

SELECT
  last_name, salary, commission_pct
FROM
  employees
WHERE
  salary >= 10000
AND
  salary <= 20000;

// 查询部门编号不是在 90 到 110 之间，或者工资高于 15000 的员工信息
SELECT
  *
FROM
  employees
WHERE
  department_id < 90
OR
  department_id > 110
OR
  salary > 15000;

2.3 模糊查询

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

// 查询员工名中包含字符 a 的员工信息
SELECT
  *
FROM
  employees
WHERE
  last_name LIKE '%a%';

// 查询员工名中第三个字符为 e 的员工名和工资
SELECT
  last_name, salary
FROM
  employees
WHERE
  last_name LIKE '__e%';

// 查询员工名中第二个字符为_的员工名
SELECT
  last_name, salary
FROM
  employees
WHERE
  last_name LIKE '_\_%';

// 使用 ESCAPE 对 $ 进行转义
SELECT
  last_name, salary
FROM
  employees
WHERE
  last_name LIKE '_$_%' ESCAPE '$';

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

// 查询员工编号在 100 到 120 之间的员工信息
SELECT
  *
FROM
  employees
WHERE
  employee_id BETWEEN 100 AND 120;

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

SELECT
  last_name,
  job_id
FROM
  employees
WHERE
  job_id IN ('IT_PROG', 'AD_VP')

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

SELECT
  last_name,
	commission_pct
FROM
  employees
WHERE
  commission_pct IS NOT NULL

SELECT
  last_name,
	commission_pct
FROM
  employees
WHERE
  commission_pct IS NOT NULL

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

SELECT
  last_name,
	commission_pct
FROM
  employees
WHERE
  salary <=> 12000;

3 排序查询

语法：

desc 降序
asc 升序

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

SELECT
  *
FROM
  employees
ORDER BY
  salary DESC

结合表达式的搜索与排序

SELECT
  *,
  salary * 12 * (1 + IFNULL(commission_pct, 0)) 年薪
FROM
  employees
ORDER BY
  salary * 12 * (1 + IFNULL(commission_pct, 0)) DESC

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

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

SELECT
  *
FROM
  employees
ORDER BY
  salary ASC,
	employee_id DESC

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

SELECT
  last_name, salary
FROM 
  employees
WHERE
  salary NOT BETWEEN 8000	AND 17000
ORDER BY
  salary 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_put

SELECT
  CONCAT(UPPER(SUBSTR(last_name, 1, 1)), SUBSTR(last_name, 2, LENGTH(last_name))) 名,
  last_name
FROM
  employees

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 函数：

SELECT
  last_name, commission_pct, IF(commission_pct IS NULL, '没奖金', '有奖金') 备注
FROM
  employees

2 case 函数，第一种：

类似于 switch case

case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1
...
else 要显示的值n或语句n
end

SELECT
  salary AS 原始工资, department_id,
  CASE department_id
  WHEN 30 THEN salary*1.1
  WHEN 40 THEN salary*1.2
  WHEN 50 THEN salary*1.3
  ELSE salary
END AS 新工资
FROM
  employees

3 case 函数，第二种：

case
when 条件1 then 要显示的值1或语句1
...
else 要显示的值n或语句n
end

SELECT salary,
  CASE
  WHEN salary>20000 THEN 'A'
  WHEN salary>15000 THEN 'B'
  WHEN salary>10000 THEN 'C'
  ELSE 'D'
  END AS 工资级别
FROM
  employees

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_id
  AND employees.commission_pct IS NOT NULL

SELECT department_name, city
FROM departments, locations
WHERE departments.location_id = locations.location_id
  AND 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_id
  AND 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_id
  AND 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.salary
      BETWEEN job_grades.lowest_sal
      AND 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_id
  AND departments.location_id=locations.location_id
	AND locations.city='Toronto'

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

SELECT last_name, department_name, locations.location_id, city, commission_pct
FROM employees, departments, locations
WHERE commission_pct IS NOT NULL
	AND 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

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 salary
  FROM employees
  WHERE last_name='Abel'
);

2 多子查询

SELECT last_name, job_id, salary
FROM employees
WHERE job_id=(
  SELECT job_id 
  FROM employees
  WHERE employee_id=141
)
AND salary>(
  SELECT salary 
  FROM employees
  WHERE 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 employees
  WHERE 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_id
	FROM departments
	WHERE location_id IN(1400, 1700)
)

7.1.3 行子查询（多列多行）

很少使用

7.2 select 后面

1 查询各部门的员工数

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

SELECT (
  SELECT departments.department_name
	FROM departments
	WHERE 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_id
  FROM employees
  GROUP 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;

qq_42680313

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

专栏目录