SQL 增删改查

baihb1024

已于 2024-09-26 11:51:28 修改

阅读量316

点赞数

分类专栏： DB 文章标签： mysql java 数据库

于 2023-06-26 15:15:45 首次发布

本文链接：https://blog.csdn.net/baihaibo1024/article/details/131397354

版权

DB 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

一、新增

1. 全列插入

insert into students values (1, 'zhangsan', 'xian', '2018-8-8');

2. 部分列插入

insert into students(name, address, birthday) values ('lisi', 'xian', '2019-9-9');

3. 部分列多行插入

insert into students(name) values ('zhangsan'),('lisi'),('wangwu');

4. 根据联合主键插入，存在则更新

insert into t_user(id, name, address, is_delete) values (1, 'cnbai', 'xian', 0) on conflict (id, is_delete) do update set name = excluded.name, address = excluded.address;

二、修改

1. 更新语句

update students set gender = 0, name = 'zhangsan' where id = 5;

2. 某字段加减特定值再更新

update students set age = (age + 100) where id = 1;

3. 特殊更新

-- 将用户表 user 里 name 为空字符串("") 的用户的 status 改成 0
update user set status = 0 where id in (select t.id from (select id from user where name = '') as t );

在 Mysql 里，不能先 select 一个表的记录，再按此条件进行更新和删除同一个表的记录，只能将 select 得到的结果，通过中间表 select 一遍

4. 替换字段数据

-- 将用户表 user 里 id = 1 的数据中，字段为 name 的值中所有的 '张' 改为 'zhangzhangzhang'
update t_user set name = replace(name, '张', 'zhangzhangzhang') where id = '1';

（原）eq：
id			 name
1			 张三张四
update后：
id			 name
1		     zhangzhangzhang三zhangzhangzhang四

5. 插入替换数据

-- 将 id = 1 的 name 字段数据改为 bai
replace into t_user VALUES(1, 'bai');

6. 两表联合更新

-- 将学生表 student 中 name 值赋给 user 表，条件为 u.id=s.userId
update t_user u left join t_student s on u.id = s.userId set u.name = s.name;

三、删除

-- delete from 表名 where 条件
delete from students where id = 5;

四、查询

1）、普通查询

1. 查询一张表

 select * from 表名

2. 查询指定字段

select 字段1，字段2，字段3….from 表名

3. `WHERE` 条件查询

select * from t_studect where id = 1
select * from t_student where age > 22

4. 带 `IN` 关键字查询

select * from t_student where age in (21,23)
select * from t_student where age not in (21,23)

5. 带 `BETWEEN AND` 的范围查询

-- 21 <= age <= 29 
select * from t_student where age between 21 and 29

-- age < 21 且 age > 29
select * from t_student where age not between 21 and 29

6. 带 `LIKE` 的模糊查询

“%” 代表任意字符
“_” 代表单个字符

select * from t_student where stuName like '张三'
-- 使用 CONCAT 拼接
select * from t_student where stuName like CONCAT('张三', '%')
-- 含有张三的任意字符
select * from t_student where stuName like '%张三%'
-- 姓张并且"名"是一个字
select * from t_student where stuName like '张_'

7. 空值查询

select * from t_user where name is null
select * from t_user where name is not null

8. 带 `AND` 的多条件查询

select * from t_student where gradeName = '一年级' and age = 23
select * from t_student where birthday >= '2020-02-02' and birthday <= '2022-02-02'

9. 带 `OR` 的多条件查询

select * from t_student where gradeName = '一年级' or age = 23

10. `DISTINCT` 去重复查询

select distinct 字段名 from 表名
select distinct * from 表名

11. 对查询结果排序 `ORDER BY`

 -- 降序，从大到小
 select * from t_student order by age desc;

 -- 升序，asc 默认可以不写
 select * from t_student order by age asc;

 -- 先以 name 升序，再根据每个 name 分组以 age 倒序
 select * from t_student order by name , age desc;

 -- 先以 name 倒序，再根据每个 name 分组以 age 倒序
 select * from t_student order by name desc , age desc;

ORDER BY 后面使用 CASE 语句

 -- 先根据column1的值进行排序：如果column1的值为'A'，则排序值为1；如果为'B'，则排序值为2；否则排序值为3。然后，根据column2的值升序
SELECT
  * 
FROM
  t_user
ORDER BY
CASE
	WHEN column1 = '1' THEN 1 
	WHEN column1 = '2' THEN 2 
	ELSE 3 
END, 
column2 DESC;

-- 先根据有效期和无效期进行排序，有效期中再根据有效期时长由短及长进行排序，失效期则根据年龄倒序排序
SELECT
  *
FROM
  t_user
ORDER BY
CASE
	WHEN effective_time > NOW() THEN -- 和当前时间比较，超过则表示有效
	0 -- 有效期
	ELSE 1 -- 无效期
END,
CASE
	WHEN effective_time > NOW() THEN
	TIMEDIFF(effective_time,NOW()) -- 有效期时长（升序，由短到长）
	ELSE NULL -- 无效期不考虑此字段
END,
CASE
	WHEN effective_time <= NOW() THEN
	age -- 无效期按年龄排序
	ELSE NULL 
END DESC -- 按年龄倒序
LIMIT 10 OFFSET 0

12. 分页查询 `LIMIT`

-- 从 0 开始，获取 3 条数据
SELECT * FROM students LIMIT 0, 3;

-- 第 pageNum 页， 取 pageSize 条数据 -> pageNum 从 1 开始
SELECT * FROM students LIMIT {pageSize} OFFSET {(pageNum - 1) * pageSize}

13. 带 `EXIST` 关键字查询

-- 查询部门编号小于30的所有员工信息
-- select * from emp where deptno in (select deptno from dept where deptno < 30);

select * from emp e where exists (
	select * from dept d where deptno < 30 and d.deptno = e.deptno;
);

----------------------------------------------------------------------------------------------------------------------------
1. 首先执行外查询 select * from emp e，然后取出第一行数据，将数据中的部门编号传给内查询
2. 内查询执行 select * from dept d where deptno < 30 and d.deptno = e.deptno; 看是否查询到结果.
   查询到，则返回 true，否则返回 false；比如传来的是30，则不满足 deptno < 30 and d.deptno = 30，返回 false
3. 内查询返回 true，则该行数据保留，作为结果显示；反之，返回 false，则不作结果显示

14. `REGEXP` 正则匹配查询

 -- 正则表达式匹配，找出名字中包含 张三、李四或王五 的数据
 select * from t_student where name regexp ('张三|李四|王五')

15. 随机查询

-- 随机查询 20 条数据 - Mysql
select * from t_user order by RAND() limit 20

-- 随机查询 20 条数据 - Postgre
select * from t_user order by RANDOM() limit 20

16. FIND_IN_SET

-- 查询用户表中 角色ID 为 4 的数据
select * from t_user where FIND_IN_SET('4', roleIds)

17. LENGTH 条件判断

-- 查询地址长度大于 9 的数据
select * from t_user where LENGTH(address) > 9

18. LEFT 结果截取

-- 查询地址，并截取长度为 3 的数据
select LEFT(address, 3) from t_user

2）、分组查询

group by 单字段

select * from user;

name	 age	  gender
张三		 18			男
李四		 18			男
王五		 22			男
赵六		 24			女
田七	 	 36			女

select gender from user group by gender;

gender
  男
  女

group by 多字段

select gender, age, count(*) as num from user group by gender, age;

先以 gender 的值进行分组,分为 ->  第一组: 1-3   第二组: 4-5
后对两个分组以 age 进行分组,每组又可以分为两组

gender    age			num
  男      18			 2
  男      22			 1
  女      24			 1
  女      36		 	 1

group by + group_concat()

group_concat(字段名) 根据分组结果，使用 group_concat() 来放置每一个分组中某字段的集合

select gender,group_concat(name) from user group by gender;

gender		  group_concat(name)
  女			 赵六,田七
  男			 张三,李四,王五

group by + 聚合函数

-- 分别统计性别 男/女 的人的年龄平均值
select gender,avg(age) from user group by gender;

gender	     avg(age)
  男		 19.3333
  女		 30.0000

group by + having

having 条件表达式：用来过滤分组结果
having 作用和 where 类似，但 having 只能用于 group by 而 where 是用来过滤表数据

select gender,count(*) from user group by gender having count(*) > 2;

gender	   count(*)
 男			 3

group by + with rollup

with rollup 的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果

select gender,count(*) from user group by gender with rollup;

gender		 count(*)
  女		   2
  男		   3
 NULL          5

select gender,group_concat(age) from user group by gender with rollup;

gender		group_concat(age)
 女			    24,36
 男			    18,18,22
NUll		    24,36,18,18,22

3）、聚合函数

1. 总数

count(*) 表示计算总行数，括号中写星与列名，结果是相同的

select count(*) from students;

2. 最大值

max(列) 表示求此列的最大值

select max(id) from students;

3. 最小值

min(列) 表示求此列的最小值

select min(id) from students;

4. 求和

sum(列) 表示求此列的和

select sum(age) from students;

5. 平均值

avg(列) 表示求此列的平均值

select avg(age) from students;

4）、合并去重

1. union

使用 union 关键字，数据库系统会将所有的查询结果合并到一起，然后去掉相同的记录

select id,username as name from t_user union select id,videoName as name from t_video

2. union all

使用 union all，不会去除掉重复的记录

select id,username as name from t_user union all select id,videoName as name from t_video

3. 数据库去重

在 MYSQL 里，不能先 select 一个表的记录，再按此条件进行更新和删除同一个表的记录
解决办法：将 select 得到的结果，通过中间表 select 一遍

delete from user where id not in (select t.max_id from (select max(id) as max_id from user group by id) as t );

五、特殊查询

1）、特殊查询语句

1. 查询今天、昨天、近7天、近30天、一个月内、上一月数据

查询当天的所有数据

 SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW()) = 0 

 or

 SELECT * FROM 表名 WHERE to_days(时间字段名) = to_days(now());

查询昨天的所有数据

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=-1

or

SELECT * FROM 表名 WHERE TO_DAYS(NOW()) - TO_DAYS(时间字段名) = 1

查询未来第n天的所有数据

-- 当 n 为负数时，表示过去第 n 天的数据
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())=n

查询未来n天内所有数据

SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<n AND DATEDIFF(字段,NOW())>=0

查询出今天，年月日

SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY)

查询几个小时之前的时间

SELECT DATE_SUB(NOW(), INTERVAL 5 HOUR)

查询近7天数据（超出当前时间也会统计）

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(时间字段名)

查询近半年数据（超出当前时间也会统计）

SELECT * FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 5 MONTH) <= date(时间字段名)

查询本月数据

SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

查询上一月数据

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

查询过去n天内所有数据

-- 包含当天
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<=0 AND DATEDIFF(字段,NOW())>-n
-- 不包含当天
SELECT * FROM 表名 WHERE DATEDIFF(字段,NOW())<0 AND DATEDIFF(字段,NOW())>-n

2. 按日、月、年、周分组查询数据

按日查询

SELECT 字段名 FROM 表名 GROUP BY DATE_FORMAT(time,'%d')

按月查询

SELECT 字段名 FROM 表名 GROUP BY DATE_FORMAT(time,'%m')

按年查询

SELECT 字段名 FROM 表名 GROUP BY DATE_FORMAT(time,'%Y')

按周查询

SELECT 字段名 FROM 表名 GROUP BY DATE_FORMAT(time,'%u')

3. 查询每日新增用户数

七天内（不是连续七天，为 null 不显示）

SELECT count(*) FROM 表名 WHERE DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= 字段名 GROUP BY DATE_FORMAT(字段名, '%Y %m %d')

一周内（连续七天，为 null 显示 0, 字段为 datetime 类型）.

select a.click_date,ifnull(b.count,0) as count
from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (
  select date( 字段名 ) as datetime, count(*) as count
  from 表名
  group by date( 字段名 )
) b on a.click_date = b.datetime;

一周内（连续七天，为 null 显示 0, 字段为时间戳类型）

select a.click_date,ifnull(b.count,0) as count
from (
    SELECT curdate() as click_date
    union all
    SELECT date_sub(curdate(), interval 1 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 2 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 3 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 4 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 5 day) as click_date
    union all
    SELECT date_sub(curdate(), interval 6 day) as click_date
) a left join (
  select date(FROM_UNIXTIME(字段名/1000,'%Y%m%d')) as datetime, count(*) as count
  from 表名
  group by date(FROM_UNIXTIME(字段名/1000,'%Y%m%d'))
) b on a.click_date = b.datetime;

4. 查询大于平均年龄的学生

select * from student where age > (select avg(age) from students);

5. 查找班级年龄最大,身高最高的学生

select * from student where (height,age) = (select max(height),max(age) from students);

6. 分组查询每组最新的一条数据

select * from user

id		    name		address		    date
1			张三1		 北京			2021-12-21
2			张三2		 北京			2021-12-22
3			张三3		 北京			2021-12-23

4			李四3		 西安			2021-12-23
5			李四2		 西安			2021-12-22
6			李四1		 西安			2021-12-21

7			王五2		 咸阳			2021-12-22
8			王五1		 咸阳			2021-12-21
9			王五3		 咸阳			2021-12-23

平常我们会进行按照时间倒叙排列然后进行分组，获取每个地址的最新记录，sql如下：

SELECT * FROM ( SELECT * FROM user ORDER BY date DESC ) a GROUP BY address;

但是查询结果却不是我们想要的：

-- 要得到的结果是 id 为 3/4/9 的记录，上面的查询得到的却是 1/4/7 , 取的是每组按 id 排序第一条
id		    name		address		    date
1			张三1		 北京			2021-12-21
4			李四3		 西安			2021-12-23
7			王五2		 咸阳			2021-12-22

我们需要通过 max() 函数获取最新的时间和对应的地址（因为需要按照地址分组），然后作为一张表和原来的数据进行联查，但是不能同时地点和时间都相同（同时相同就不能作为排序和分组的条件，否则无法区分两条数据）

SELECT t.* FROM (SELECT address,max(date) as date FROM user GROUP BY address) a 
 LEFT JOIN user t ON t.address = a.address and t.date = a.date
 
id		    name		address		    date
3			张三3		 北京			2021-12-23
4			李四3		 西安			2021-12-23
9			王五3		 咸阳			2021-12-23

2）、查询结果处理

1. IF 函数(查询结果替换)

注：PostgreSql 不支持此函数，类似判断需要使用 CASE 语句

-- IF(expr,v1,v2)： 如果表达式 expr 成立，那么返回 v1，否则返回 v2

-- 对 t_user 进行查询, 如果 status = -1，就赋值为 0，其他值不改变
SELECT IF(status = -1, 0, status) as status FROM t_user;

2. CASE 语句(查询结果替换)

简单 Case 函数

-- 对 t_user 进行查询，如果 name = 1 -> 张三；name = 2 -> 李四；其他 -> 王五
SELECT
	CASE name
	WHEN 1 THEN '张三'
	WHEN 2 THEN '李四'
	ELSE '王五' END
FROM t_user;

Case 搜索函数

-- 对 t_user 进行查询，如果 50 > age > 18 -> 成人；age <= 18 -> 未成年人；其他 -> 老年人
SELECT
	CASE 
	WHEN age <= 18 THEN '未成年人'
	WHEN 50 > age > 18 THEN '成人'
	ELSE '老年人' END AS ageGroup
FROM t_user;

使用 MyBatis 编写 SQL 时使用某些符号（如：< ）会报错，需要使用 <![CDATA[]]

SELECT
<![CDATA[ CASE WHEN age <= 18 THEN '未成年人' WHEN 50 > age > 18 THEN '成人' ELSE '老年人' END AS ageGroup ]]>
FROM t_user;

3. COALESCE语句(查询结果替换)

-- 在 t_user 中 age 字段，如果 age 为 null，就变为 0
SELECT COALESCE(age, 0) FROM t_user;

-- 在 t_user 中 age 字段，如果 age 为 null，就查询 num，如果 num 为 null，就变为 0
SELECT COALESCE(age, num, 0) FROM t_user;

-- 字段个数不限，但是每个字段的类型必须相同，默认值类型也必须相同
COALESCE(字段名, 字段名, ... , 默认值)

4. 查询结果切割

-- www
SELECT substring_index('www.baidu.com', '.', 1);

-- www.baidu
SELECT substring_index('www.baidu.com', '.', 2);

-- com
SELECT substring_index('www.baidu.com', '.', -1);

-- baidu.com
SELECT substring_index('www.baidu.com', '.', -2);

-- baidu
SELECT substring_index(substring_index('www.baidu.com', '.', -2), '.', 1);

5. 日期类型转换

CAST( 字段名 as 转换的类型 )

-- 2015-11-03 15:31:26
select cast(create_time as char) as format_time from table;

-- 2015-11-03 15:31:26
select cast(create_time as datetime) as format_time from table;

-- 2015-11-03
select cast(create_time as date) as format_time from table;

-- 20151103153126
select cast(create_time as signed) as format_time from table;

DATE_FORMAT(）

-- 2015年11月03日
SELECT DATE_FORMAT(create_time, '%Y年%m月%d日') FROM table