DQL语句（下）与DML语句

最新推荐文章于 2021-09-29 17:09:04 发布

你该有人捧着

最新推荐文章于 2021-09-29 17:09:04 发布

阅读量223

点赞数 1

文章标签： mysql

本文链接：https://blog.csdn.net/qq_46052939/article/details/107835771

版权

一、多表查询

1. 就等值连接的基础上加过滤：

where 等值连接 and 过滤条件

示例：查询有奖金的员工名、部门名
在这里插入图片描述

2.加分组：

where 等值连接 group by 你查询的字段

示例：查询每个城市的部门个数
在这里插入图片描述

3.多表连接：

where 等值连接一 and 等值连接二

示例：查询员工名、部门名和所在的城市
在这里插入图片描述
4.自连接：
示例：查询员工表中员工名和其上级的名称

joio链接

内链接：
基本效果与等值连接一样。。。

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

示例：在这里插入图片描述
2.外连接：
应用场景：用于查询一个表中有，另一个表没有的记录
特点：
1、外连接的查询结果为主表中的所有记录
如果从表中有和它匹配的，则显示匹配的值
如果从表中没有和它匹配的，则显示null
外连接查询结果=内连接结果+主表中有而从表没有的记录
2、左外连接，left join左边的是主表
右外连接，right join右边的是主表
3、左外和右外交换两个表的顺序，可以实现同样的效果
4、全外连接=内连接的结果+表1中有但表2没有的+表2中有但表1没有的

select 查询列表
from 表1 别名
（left/right）outer join 表2 别名
on 连接条件;

左外连接示例：
在这里插入图片描述
右外连接示例：

二、常见函数

概念：类似于java的方法，将一组逻辑语句封装在方法体中，对外暴露方法名
好处：1、隐藏了实现细节 2、提高代码的重用性
调用：select 函数名(实参列表) 【from 表】;
特点：
①叫什么（函数名）
②干什么（函数功能）
分类：
1、单行函数
如 concat、length、ifnull等
2、分组函数(具体使用在分组查询中)
功能：做统计使用，又称为统计函数、聚合函数、组函数

1. 字符函数

#1.length 获取参数值的字节个数
SELECT LENGTH('john');
SELECT LENGTH('张三丰hahaha');

#2.concat 拼接字符串
SELECT CONCAT(last_name,'_',first_name) 姓名 FROM employees;

#3.upper、lower
SELECT UPPER('john');
SELECT LOWER('joHn');
#示例：将姓变大写，名变小写，然后拼接
SELECT CONCAT(UPPER(last_name),LOWER(first_name))  姓名 FROM employees;

#4.substr、substring
#注意：索引从1开始
#截取从指定索引处后面所有字符
SELECT SUBSTR('李莫愁爱上了陆展元',7)  out_put;
#截取从指定索引处指定字符长度的字符
SELECT SUBSTR('李莫愁爱上了陆展元',1,3) out_put;

#5.instr 返回子串第一次出现的索引，如果找不到返回0
SELECT INSTR('杨不殷六侠悔爱上了殷六侠','殷八侠') AS out_put;

#6.trim
#默认去除去后空格
SELECT LENGTH(TRIM('    张翠山    ')) AS out_put;
#去除前后指定字符
SELECT TRIM('aa' FROM 'aaaaaaaaa张aaaaaaaaaaaa翠山aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa')  AS out_put;

#7.lpad 用指定的字符实现左填充指定长度
SELECT LPAD('殷素素',12,'*') AS out_put;
#8.rpad 用指定的字符实现右填充指定长度
SELECT RPAD('殷素素',12,'ab') AS out_put;

#9.replace 替换
#用赵敏替换周芷诺
SELECT REPLACE('周芷若周芷若周芷若周芷若张无忌爱上了周芷若','周芷若','赵敏') AS out_put;

2.数字函数

#round 四舍五入
SELECT ROUND(-1.55);
SELECT ROUND(1.567,2);

#ceil 向上取整,返回>=该参数的最小整数
SELECT CEIL(-1.02);
#floor 向下取整，返回<=该参数的最大整数
SELECT FLOOR(-9.99);

#truncate 截断
#结果为1.6
SELECT TRUNCATE(1.69999,1);

#mod取余
/*
mod(a,b) ：  a-a/b*b
mod(-10,-3):-10- (-10)/(-3)*（-3）=-1
*/
SELECT MOD(10,-3);
SELECT 10%3;

3.日期函数

#now 返回当前系统日期+时间
SELECT NOW();
#curdate 返回当前系统日期，不包含时间
SELECT CURDATE();
#curtime 返回当前时间，不包含日期
SELECT CURTIME();

#可以获取指定的部分，年、月、日、小时、分钟、秒
SELECT YEAR(NOW()) 年;
SELECT YEAR('1998-1-1') 年;
SELECT MONTH(NOW()) 月;
SELECT MONTHNAME(NOW()) 月;

#str_to_date 将字符通过指定的格式转换成日期
SELECT STR_TO_DATE('1998-3-2','%Y-%c-%d') AS out_put;
#date_format 将日期转换成字符
SELECT DATE_FORMAT(NOW(),'%y年%m月%d日') AS out_put;

示例：str_to_date 将字符通过指定的格式转换成日期
在这里插入图片描述
#date_format 将日期转换成字符

4.其它函数

SELECT VERSION();//查版本
SELECT DATABASE();//查数据库
SELECT USER();//查用户

5.流程控制函数

#条件满足,执行语句一(大) 不满足执行语句二(小)
SELECT IF(10<5,'大','小');

2.case函数的使用一： switch case 的效果
case 要判断的字段或表达式
when 常量1 then 要显示的值1或语句1;
when 常量2 then 要显示的值2或语句2;
…
else 要显示的值n或语句n;
end

SELECT salary 原始工资,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;

case 函数的使用二：类似于多重if
case
when 条件1 then 要显示的值1或语句1
when 条件2 then 要显示的值2或语句2
。。。
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;

三、子查询

含义：
出现在其他语句中的内部的select语句，外部的查询语句为主查询或者外查询
分类：
按子查询位置：
select后面/from后面/where或having后面/exists后面
按结果集行列数：
标量子查询（结果集为一行一列）
列子查询（只有一列多行）
行子查询（只有一行多列）
表子查询（多行多列）

其实就是在sql语句内嵌套sql语句：
以一或多个sql语句作为条件进行过滤：
示例：谁的工资比 Abel 高?
在这里插入图片描述
#查询员工的姓名，job_id 和工资，要求job_id=141并且salary>143的salary；

# (二) 放在select 后面的子查询
## 查询每个部门的员工个数
SELECT d.*,(
 SELECT COUNT(*)
 FROM employees AS e
 WHERE e.department_id=d.department_id
) 个数
FROM departments AS d;

#(三) 放在from后面
/*
将子查询的结果放在表中，必须起别名
*/
## 查询每个部门的平均工资的工资等级
SELECT ag.*,grade_level
FROM (
	SELECT department_id,AVG(salary) AS avg_sa
	FROM employees 
	GROUP BY department_id
) AS ag
JOIN job_grades AS jg
ON ag.avg_sa BETWEEN jg.lowest_sal AND jg.highest_sal;
## 查询工资比本部门工资高的员工信息
SELECT employee_id,last_name,salary,ag.*
FROM employees AS e
JOIN (
 SELECT department_id,AVG(salary) AS ag_sa
 FROM employees 
 GROUP BY department_id) ag
ON e.`department_id`=ag.department_id
WHERE salary> ag.ag_sa;


# (四) exists后面(相关子查询)
/*
语法：
 exists(完整的查询语句)
 返回布尔值（0/1）
*/
## 查询有员工的部门名
SELECT department_name
FROM departments AS d 
WHERE EXISTS(
 SELECT * FROM employees AS e
 WHERE e.`department_id`=d.`department_id`
);

四、分页查询（LIMIT）

语法：

	原有查询+limit startIndex,offset

！！！注意事项：
1、原始查询的记录数<offset，那么查询的结果必然是原始查询的记录数
2、startIndex=0，那么可以省略，limit offset

示例：查询前五条员工信息
在这里插入图片描述
查询第11条—第25条

配合DQL的所有语句来拿取您想要的数据！！

DML语句

DML(data manipulation language)：
它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

一、数据的插入
语法：

INSERT INTO beauty(字段1，字段2,…)
VALUES(值1，值2,…);

特点：
1、字段和值列表必须一一对应
2、字符型和日期型必须用单引号引起来
3、字段的顺序可以和表中字段的顺序不一致
4、不可以为NULL的字段，必须插入值，比如说主键id；
可以为NULL的字段，可以不用插入值，使用NULL来代替，或字段名和值都不写
5、字段和值的个数必须一致
6、字段列表可以省略，默认所有列，而且顺序和表中的列的顺序一致

#方式一：insert into …values…
示例：
在这里插入图片描述

插入空值：

在这里插入图片描述

#方式二：insert into…set
在这里插入图片描述

附加：拷贝：
示例：新建一个表拷贝其它表的数据

拷贝部分数据：

二、数据的修改

#更新语句 update

#语法：

UPDATE 表名 SET 字段名=新值,字段名=新值
[WHERE 条件];

在这里插入图片描述

在这里插入图片描述
三、数据的删除
#删除的方式一：
#语法：

DELETE FROM 表 [WHERE 条件]

在这里插入图片描述

#删除的方式二：
#语法：
TRUNCATE TABLE 表名

#二种删除方式的区别【面试题】

1、TRUNCATE不能加WHERE条件，而DELETE可以加WHERE
2、TRUNCATE效率较高
3、TRUNCATE不能删除多表，而DELETE可以
4、删除带标识列字段的表时，TRUNCATE 删除后再插入，标识列的值从1开始
DELETE删除后再插入，标识列的值从断点处开始
5、TRUNCATE删除后，不能回滚；DELETE删除可以回滚

你该有人捧着

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
2
评论
DQL语句（下）与DML语句

一、多表查询1. 就等值连接的基础上加过滤：where 等值连接 and 过滤条件示例：查询有奖金的员工名、部门名2.加分组：where 等值连接 group by 你查询的字段示例：查询每个城市的部门个数3.多表连接：where 等值连接一 and 等值连接二示例：查询员工名、部门名和所在的城市4.自连接：示例：查询员工表中员工名和其上级的名称joio链接内链接：基本效果与等值连接一样。。。select 查询列表from 表1 别名inner
复制链接

扫一扫