关于数据库2 DQL

最新推荐文章于 2024-07-11 16:26:04 发布

leslie_h10u

最新推荐文章于 2024-07-11 16:26:04 发布

阅读量239

点赞数

分类专栏： sql 备忘笔记文章标签： mysql sql 数据库

本文链接：https://blog.csdn.net/weixin_46012558/article/details/107924004

版权

备忘笔记同时被 2 个专栏收录

5 篇文章 0 订阅

订阅专栏

sql

3 篇文章 0 订阅

订阅专栏

3、DQL (data query language)

3.1基础查询

3.1.1、查询表中的单个字段

SELECT 
	last_name 
FROM 
	employees;

3.1.2、查询表中的多个字段

SELECT 
  last_name,
  email,
  salary 
FROM
  employees ;

3.1.3、查询表中的所有字段

SELECT 
  * 
FROM
  employees ;

3.1.4、查询常量值表达式函数

SELECT 100;
SELECT 100*98;
SELECT version();

3.1.5、起别名

SELECT 
	100%98 AS '结果'; --便于理解，如果要查询的字段有重名的情况，使用别名可以区分
	
SELECT last_name 姓; --AS 可以省略用空格代替

3.1.6、去重

SELECT DISTINCT  --使用distinct去重
  `department_id` 
FROM
  employees ;

3.1.7、关于 + /concat

**例：**查询员工的first_name和last_name，并连接成一个字段

SELECT 
  CONCAT(first_name, last_name) AS 姓名 
FROM
  employees ; 
  /*使用函数concat 连接
    sql中，+只能运算*/

3.2、条件查询

SELECT 
	查询列表
FROM 
	表名
WHERE
	筛选条件;

3.2.1、按条件表达式筛选

**条件运算符：> < = !=(<>) >= <= **

例1：查询工资>12000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE salary > 12000 ;

例2:查询部门编号不等于90的员工名和部门编号

SELECT 
 first_name,
 department_id 
FROM
 employees 
WHERE department_id <> 90 ;

3.2.2、按逻辑表达式筛选

&& || ！ and or not

例1：查询工资10000-20000之间的员工名，工资，奖金

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

案例2：查询部门编号不在90-110或者工资高于15000的员工信息

SELECT 
  * 
FROM
  employees 
WHERE department_id < 90 || department_id > 110 || salary > 15000 ;

3.2.3、模糊查询

like between and in is null

like 案例1：查询员工名中包含a 的员工信息

SELECT 
  * 
FROM
  employees 
WHERE last_name LIKE '%a%' ;
/*一般和通配符搭配使用 
	%-->任意多个字符 包含0个
	_ -->任意单个字符
	escape '$'-->转义*/

案例2：查询员工名字中第三个字符为e 第五个字符为a 的员工名和工资

SELECT 
  `first_name`,
  `salary` 
FROM
  employees 
WHERE `first_name` LIKE '__e_a%' ;

between/and (包含临界值)

案例：查询员工编导在100-120之间的员工信息

SELECT 
  * 
FROM
  employees 
WHERE `employee_id` BETWEEN 100 AND 200 ;

案例：查询员工的工种编号为IT_PROT, AD_VP,AD_PRES中的一个的员工名和工种编号

SELECT 
  `first_name`,
  `job_id` 
FROM
  employees 
WHERE `job_id` IN ('IT_PROT', 'AD_VP', 'AD_PRES') ;

is null（=运算符，不能判断null值）

案例：查询没有奖金的员工名和奖金率

SELECT 
  `first_name`,
  `commission_pct` 
FROM
  employees 
WHERE `commission_pct` IS NULL ;

安全等于 <=>可以判断null值也可以判断普通类型，但是可读性较低不推荐

3.3排序查询

SELECT 
	*
FROM
	table
WHERE
	条件 ORDER BY 排序列表 [asc/desc]

案例：查询员工信息，要求工资从高到底排序

SELECT 
 * 
FROM
 employees 
ORDER BY  salary DESC ;

3.4、常见函数

调用：select 函数名（实参列表）【from 表】；

3.4.1 单行函数

字符函数

length() —> 获取参数值的字节个数

concat() —> 拼接字符串

select concat(last_name,'_',firstname) from employees;

upper/lower —> 大小写

select concat(upper(last_name),'_',lower(first_name)) as 姓名 from employees;

substring —> 截取

sql索引都是从1开始的

select substr('123456',4) out_put;--从4开始到结束的所有字符

select substr('123456',1,3) out_put;-- 从1 开始截取， 字符长度为3

instr —> 查找第一次出现的位置返回索引,如果不存在，返回0

select instr('1234567','1') as out_put;

trim —> 去前后空格

—>去掉前后自定义字符

select trim('  123   ') as out_put;

select trim('a' from 'aaaaa123aaaaaaa') as out_put;

lpad —> 用指定字符实现左填充指定长度

rpad —>右填充

select lpad('123',10,'*') as out_put;

select rpad('123',10,'*') as out_put;

replace —> 替换

select replace('张无忌喜欢周芷若','周芷若','赵敏') as out_put;

数学函数

round —>四舍五入

select round(1.65);
select round(1.658,2);--小数点后保留两位

ceil —>向上取整,返回大于等于该参数的最小整数

select ceil(1.65);--	2
select ceil(1.00);--	1

floor —>向下取整

select floor(1.65);--	1

truncate —> 截断

select truncate(1.65,1)--	1.6

mod —>取余

a-a/b*b

select mod(10,3);-- 1

日期函数

now 返回当前系统日期+时间
```
select now();
```
curdate 返回当前日期，不包含时间
```
select curdate();
```
curtime 当前时间不包含日期

获取指定部分，年月日小时分钟秒
```
select year(now());

select month(now());
select monthname(now());
```
str_to_date 将日期格式的字符，转换成指定格式的日期

date_formate 将日期转换成字符
```
str_to_date('9-13-2020','%m-%d-%Y')

date_formate('2018/6/6','%Y年%m月%d日')
```
1 %Y 四位的年份
2 %y 两位年份
3 %m 月份 01 02
4 %c 月份 1 2
5 %d 日 01 02
6 %H 小时 24H
7 %h 小时 12H
8 %i 分钟 00 01
9 %s 秒 01 02

1	%Y	四位的年份
2	%y	两位年份
3	%m	月份 01 02
4	%c	月份 1 2
5	%d	日 01 02
6	%H	小时 24H
7	%h	小时 12H
8	%i	分钟 00 01
9	%s	秒 01 02

流程控制函数

if函数：if-else效果

select if(10>5,'大','小');

case函数：

1 switch case 效果

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

案例：查询员工工资。要求:部门号30则显示工资为 1.1倍，:部门号40则显示工资为 1.2倍，其他为原工资

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

2.类似于多重if

case
when 条件1 then 显示的值/表达式
when 条件2 then 显示的值/表达式
when 条件2 then 显示的值/表达式
...
else 显示的值/表达式
end

案例：查询工资，如果>20000,显示A级，如果>15000,显示B级，如果>10000,显示C级，否则显示D

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

3.4.2 分组函数（用作统计使用）

包括 sum 求和， avg 平均值，max,min,count 计算个数

简单使用

select sum(salary) from employees;
SELECT AVG(salary) from employees;
SELECT MAX(salary) from employees;
SELECT MIN(salary) from employees;
SELECT COUNT(salary) from employees;

SELECT SUM(salary),round(AVG(salary)，2) from employees;

参数支持哪些类型
- sum,avg 处理数值型
- max, min,count 处理任何类型
是否忽略null
和distinct搭配（去重）

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

count函数详细介绍

select count(*) from employees;--行数

和分组函数一起查询的字段有限制

select avg(salary),employee_id from employees;
--后面查询employee_id 的部分没有意义。

SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) AS difference FROM employees;
--最大 和 最小入职天数 相差多少天

SELECT COUNT(*) FROM employees WHERE department_id =90;
--部门ID 为90的员工个数

3.5 分组查询

分组查询的筛选条件分为两类，分组前筛选/分组后筛选（数据源不同）

select 分组函数 ，列（要求出现在group by后面）
from 表
where 筛选条件
group by 分组的列表
order by asc/desc;

3.5.1 添加分组前筛选

查询每个工种的最高工资

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

查询邮箱中包含a,每个部门的平均工资

SELECT 
  AVG(salary),
  department_id 
FROM
  employees 
WHERE email LIKE '%a%' --添加筛选条件
GROUP BY department_id ;

查询每个领导手下有奖金员工的最高工资

SELECT 
  MAX(salary),
  manager_id 
FROM
  employees 
WHERE `commission_pct` IS NOT NULL 
GROUP BY manager_id ;

3.5.2 添加分组后筛选

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

SELECT 
  COUNT(*),
  department_id 
FROM
  employees 
GROUP BY department_Id 
HAVING COUNT(*) > 2 ;

查询每个工种有奖金的员工的最高工资>12000 的工种编号和最高工资

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

3.5.3 按表达式或函数分组

按员工姓名的长度分组，查询每一组员工的个数，筛选员工个数>5 的有哪些

SELECT 
  COUNT(*),
  LENGTH(last_name)
   
FROM
  employees 
GROUP BY LENGTH(last_name) 
HAVING COUNT(*)>5;

3.5.4 按多个字段分组

查询每个部门每个工种的员工的平均工资

SELECT 
  AVG(salary),
  department_id,
  job_id 
FROM
  employees 
GROUP BY department_id,
  job_id ;

3.5.5 添加排序

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

SELECT 
  AVG(salary),
  department_id,
  job_id 
FROM
  employees 
GROUP BY department_id,
  job_id 
ORDER BY AVG(salary) ASC ;

3.6 连接查询（多表查询）

3.6.1笛卡尔乘积

表1有M行，表2有N行，结果M*N行

发生原因：没有有效的连接条件

添加有效的连接条件就可以避免笛卡尔乘积

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

3.6.2 分类

sql92标准（支持内连接）

sql99标准（推荐）

内连接（等值连接，非等值连接，自连接）、外连接（左外连接，右外连接，全外连接）、交叉连接

3.6.2.1 sql92标准— 等值连接

查询女生名和对应男生名

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

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

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

查询有奖金的员工名和对应的部门名

SELECT 
  last_name,
  department_name 
FROM
  employees,
  departments 
WHERE departments.`department_id` = employees.`department_id` 
AND employees.commission_pct IS NOT NULL;

多表等值连接为多表的交集部分/N个表连接至少需要N-1个连接条件/顺序没有要求

3.6.2.2 sql92— 非等值连接

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

SELECT 
  salary,
  grade_level 
FROM
  employees e,
  job_grades g 
WHERE salary BETWEEN g.`lowest_sal` 
  AND g.`highest_sal` ;

3.6.2.3 sql92—自连接

查询员工名和上级的名称

SELECT 
  e.`employee_id`,
  e.`last_name`,
  m.`employee_id`,
  m.`last_name` 
FROM
  employees e,
  employees m 
WHERE e.`employee_id` = m.`employee_id` ;

3.6.2.4 sql99

语法：

select 查询列表
from 表1 别名  连接类型
join 表二 别名 
on  连接条件


/*连接类型：
内连接:inner
外连接：左外：left (outer)
		右外： right (outer)
		全外： full (outer)
交叉类型：cross*/

3.6.2.5 sql99 —内连接

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

分类：等值，非等值，自连接

等值

--1.查询员工名和部门名
SELECT 
  last_name,
  department_name 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` ;
   
--2.查询名字中带有e的员工名和工种名   （筛选）

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

/*3.查询部门个数>3的 城市名，和部门个数（分组+筛选）*/
SELECT 
  COUNT(*),
  city 部门个数
FROM
  departments d 
  INNER JOIN locations l 
  ON d.`location_id`=l.`location_id`
  GROUP BY city
  HAVING COUNT(*)>3;

/*4.查询哪个部门的员工个数>3的部门名和员工个数，并按个数降序排序*/
SELECT 
  COUNT(*),
  department_name 
FROM
  employees e 
  JOIN departments d 
    ON e.`department_id` = d.`department_id` 
GROUP BY department_name 
HAVING COUNT(*) > 3 
ORDER BY COUNT(*) DESC ;

 /*5.查询员工名，部门名，工种名，按部门名排序*/
SELECT 
  last_name,
  department_name,
  job_title 
FROM
  employees e 
  INNER JOIN departments d 
    ON e.`department_id` = d.`department_id` 
  INNER JOIN jobs j 
    ON e.`job_id` = j.`job_id` 
GROUP BY job_title 
ORDER BY `job_title` ASC ;

非等值连接

/*查询员工的工资级别*/
SELECT 
  salary,
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades g 
    ON e.`salary` BETWEEN lowest_sal 
    AND highest_sal ;
    
/*查询工资级别的个数>20的个数，并按工资级别排序*/
SELECT 
  COUNT(*),
  salary,
  grade_level 
FROM
  employees e 
  INNER JOIN job_grades j 
    ON e.salary BETWEEN j.lowest_sal 
    AND j.highest_sal 
GROUP BY grade_level 
HAVING COUNT(*) > 20 
ORDER BY grade_level ASC ;

自连接

/*查询员工的名字 和上级的名字*/
SELECT 
  e.last_name,
  m.last_name 
FROM
  employees e 
  INNER JOIN employees m 
    ON e.`manager_id` = m.`employee_id` ;

3.6.2.6 sql99—左右外连接

/*查询没有男朋友的女生名*/
SELECT 
  b.`name` 
FROM
  beauty b 
  LEFT OUTER JOIN boys bo 
    ON b.`boyfriend_id` = bo.`id` 
WHERE bo.`boyName` IS NULL ;

/*查询 哪个部门没有员工*/
SELECT 
  d.*,
  e.employee_id 
FROM
  departments d 
  LEFT OUTER JOIN employees e 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL ;

SELECT 
  d.*,
  e.employee_id 
FROM
  employees e 
  RIGHT OUTER JOIN departments d 
    ON d.`department_id` = e.`department_id` 
WHERE e.`employee_id` IS NULL

3.7 子查询

出现在其他语句中的select语句，成为子查询或内查询

外部的查询语句，成为主查询或者外查询

分类：按出现位置分类{select后面，from后面，where/having后面，exists后面}

按结果集的行列数不同{标量子查询（结果集只有一行一列）、列子查询（结果集一列多行）、行子查询（结果集一行多列）、

表子查询（多行多列）}

select后面只支持标量子查询

3.7.1 where后的标量子查询使用

1.子查询都在小括号内，

2.一般放在条件右侧，

3.标量子查询一般搭配单行操作符使用（> < = <> >= <=)

4.列子查询，一般搭配多行操作符（IN ANY/SOME ALL)

**标量子查询（单行子查询） where **

 /*谁的工资比Abel高*/
SELECT 
  * 
FROM
  employees 
WHERE 
  salary>(SELECT 
    salary 
  FROM
    employees 
  WHERE last_name = 'Abel') ;
  

/*返回job_id与141员工相同，salary比143号员工多的员工姓名，job_id,和工资*/
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) ;


/*返回工资最少的员工的last_name,job_id,salary*/
SELECT 
  last_name,
  job_id,
  salary 
FROM
  employees 
WHERE salary = 
  (SELECT 
    MIN(salary) 
  FROM
    employees) ;

子查询的having子句

/*查询最低工资 大于50号部门最低工资的部门id,和其最低工资*/
SELECT 
  department_id,
  MIN(salary) 
FROM
  employees 
GROUP BY department_id 
HAVING MIN(salary) > 
  (SELECT 
    MIN(salary) 
  FROM
    employees 
  WHERE department_id = 50);

3.7.2 where 后的列子查询（多行子查询）

返回多行，使用多行比较操作符（IN/NOT IN 等于列表中的任意一个、ANY/SOME 和子查询返回的某一个值比较、ALL 和子查询返回的所有值比较）

/*返回location_id是1400或1700的部门中，所有员工姓名*/
SELECT 
  last_name 
FROM
  employees 
WHERE department_id IN 
  (SELECT DISTINCT 
    department_id 
  FROM
    departments 
  WHERE location_id IN (1400, 1700));
  
  
  /*返回其他部门中比job_id为'IT_PROG'部门任意工资低的员工的员工号，姓名，job_id和salary*/
SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ANY 
  (SELECT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;


/*返回其他部门中比job_id为'IT_PROG'部门所有工资低的员工的员工号，姓名，job_id和salary*/
SELECT 
  last_name,
  employee_id,
  job_id,
  salary 
FROM
  employees 
WHERE salary < ALL
  (SELECT 
    salary 
  FROM
    employees 
  WHERE job_id = 'IT_PROG') 
  AND job_id <> 'IT_PROG' ;

3.7.3 where后的行子查询（多行多列）

/*查询员工编号最小并且工资最高的*/
SELECT 
  * 
FROM
  employees 
WHERE employee_id = 
  (SELECT 
    MIN(employee_id) 
  FROM
    employees) 
  AND salary =
  (SELECT 
    MAX(salary) 
  FROM
    employees) ;

3.7.4 select 后的子查询

/*查询每个部门的员工人数*/
SELECT 
  d.*,
  (SELECT 
    COUNT(*) 
  FROM
    employees e 
  WHERE e.department_id = d.`department_id`) 个数 
FROM
  departments d ;

/*查询员工号=102的部门名*/
SELECT (
	SELECT department_name
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部门;
-----------------------------

leslie_h10u

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
关于数据库2 DQL

3、DQL (data query language)3.1基础查询3.1.1、查询表中的单个字段SELECT last_name FROM employees;3.1.2、查询表中的多个字段SELECT last_name, email, salary FROM employees ;3.1.3、查询表中的所有字段SELECT * FROM employees ;3.1.4、查询常量值表达式函数SELECT 100;SELECT 10
复制链接

扫一扫