SQL语句实录--Mysql

SQL语句实录–Mysql

SQL（结构化查询语言）是用于访问和操作数据库中的数据的标准数据库编程语言。 SQL是关系数据库系统的标准语言。所有关系数据库管理系统(RDMS)，如MySQL、MS Access、Oracle、Sybase、Informix、Postgres和SQL Server都使用SQL作为它们的标准数据库语言。 ----W3CSchool. 😃😐

---

---

前言

SQL 指结构化查询语言，全称是 Structured Query Language（最初由IBM开发）。支持对数据库表table的全方位操作：增insert、删delete、改update、查select。

User表结构：uId为自增字段
User表数据：

---

一、基础的操作语句规范

1、查询语句

select *或表内字段名 from 数据表名 where   条件语句;

条件语句：可以是一般的某字段的定值，也可以是另一个查询语句，也可以是某值的范围等等；涉及多个条件时用and 或 or 连接。
根据上述的User表来做几个示例：
1.1 查询姓名为张三的个人信息：select * from user where uName = "张三;

1.2 查询身高在170-180的用户信息：select * from user where uHeight between 170 and 180;

2、插入语句

insert into 数据表名 (字段1名,字段2名,...) 
values(字段1值,字段2值,...),(字段1值,字段2值,...),...;

其中字段名和值必须相互对应。若插入的是全部的字段，则(字段1名,字段2名,…)可以不写，但必须保证values值必须与表字段排列对应。
根据上述的User表做几个示例：
2.1 插入一条数据 uName = 小二 uPhone = 12345678910 ：insert into user (uName,uPhone) values(“小二”,12345678910);
2.2 插入全部字段信息时：insert into user values(6,12345678911,“jack”,1,150,45,‘2022-08-31’); 此时主键也必须给出，不建议使用这种插入方式。

3、删除语句

delete from 数据表名 where 条件语句;

条件语句：可以是一般的某字段的定值，也可以是另一个查询语句，也可以是某值的范围等等；涉及多个条件时用and 或 or 连接。
根据上述的user表做如下示例：
删除uId = 4的用户：delete from user where uId = 4; 执行后，该条记录被删除。

4、修改语句

update 数据表名 set 字段名1 = 值 , 字段2 = 值,…where 条件语句;
条件语句：可以是一般的某字段的定值，也可以是另一个查询语句，也可以是某值的范围等等；涉及多个条件时用and 或 or 连接。
根据上述的user表做如下实例：update user set uName = “浩辰” where uId = 4;
再次查询：select * from user where uId = 4;

小结：

基本的数据库表的四大操作语法：增删改查如上，看着不复杂，工作中查询的复杂程度相对较高些，因为涉及到的sql函数、多表连接查询、排序，分组等；这也使得查询语句的编写很重要，一个优化的sql会大大提高执行效率。 撸起袖子加油干。

二、SQL常用函数合集

sql中用到的日期格式：年-月-日 小时:分钟:秒 2022-02-22 22:22:22 😃

1、获取当前时间、日期

①curdate()

此方法可以获取到当前的年-月-日。
可以当作查询结果的字段 ，也可以用到条件语句中去。

—> 例如： select curdate(); 结果是当前时间：2022-09-01
根据上述的user表做如下实例：
—>修改uId = 1的用户的出生日期为当前时间：update user set uBirth = curdate() where uId = 1;
查询：select * from user where uId = 1;

②now()

获取当前的时间点，包括日期，时间精确到秒级别。
可以当作查询结果的字段 ，也可以用到条件语句中去。

例如：select now();

③sysdate()

同now(),获取当前日期+时间点

例如：select SYSDATE();

④date(表示时间的字符串)

提取日期或日期时间表达式中的日期部分

例如：select date(‘2022-09-01 12:55’); 结果是：2022-09-01

⑤curtime()

获取当前时间，即HH:MM:SS     时-分-秒

例如：select curtime(); 结果是：14:25:24

2、对日期的“四则运算”

①date_add(date,interval num type)

向给定时间增加一定的时间间隔  date为日期格式 ，num为数字、 
type为增加的类型 ：day month year hour minute,second...

例如：select date_add(“2022-05-22 12:15”,interval 15 minute); 结果是 2022-05-22 12:30:00

②date_sub(date,interval number type)

向给定时间减去一定的时间间隔 date为日期格式 ，num为数字、type为减去的类型 ：day month year hour minute,second…
例如：**select date_sub(“2022-05-22 12:15”,interval 16 minute);**结果是：2022-05-22 11:59:00

③datediff(date1,date2)

计算出两个日期之间的天数,注意date1 >= date2 否则计算出为负值。

例如：
select datediff(“2022-08-09”,“2022-06-05”); 结果是：65
select datediff(“2022-06-05”,“2022-08-09”); 结果是：-65

④TIMESTAMPDIFF(type,date1,date2)-计算年龄

将date2 与date1以type为单位做差，可用于计算年龄
其中date2 > date1 否则是负值
type:   year,month,day,hour,minute,second.

以开篇的user表做实例： uId = 4,
select timestampdiff(year,uBirth,curdate()) from user where uId = 4;

⑤YEARWEEK(date)-获取年数和周数

通过该方法，可以获取到指定日期是该年的第几周以及所在年

例如：
select yearweek(“2022-01-01”); 结果是：202152 为何是2021年的第52周，因为计算方式是按照美国的周日~周六来计算的,而2022-01-01是延续在2021年的最后一周内，所以显示为21年的最后一周。
一般情况下，此方法计算出的结果会多一；所以须减一
select yearweek(“2022-06-09”,1); 结果如下：

3、日期格式互相转换

时间戳是指格林威治时间自1970年1月1日(00:00:00 GMT)至当前时间的总秒数。
10位时间戳精确到了秒
13位时间戳精确到了毫秒

①基本的格式变换data_format(date,format)

format表示即将改变的格式
format:%Y-%m-%d %h:%i:%s
date是日期形式的字符串

②日期转时间戳-UNIX_TIMESTAMP(date)-秒级别

date是日期形式的字符串
此精确到了秒级别

例如：
select UNIX_TIMESTAMP(“2022-06-05”);结果是:1654358400
select UNIX_TIMESTAMP(“2022-06-05 15:25:22”);结果是：1654413922
select UNIX_TIMESTAMP(date_format(“2022-06-05 15:25:22”,“%Y-%m-%d”)); 结果是：1654358400 此结果与第一个相同，我们将日期去除了时分秒。

③时间戳转日期-FROM_UNIXTIME(timestamp/1000,format)

timestamp是时间戳格式
其中format代表的日期转化的格式
format:%Y-%m-%d %h:%i:%s

例如：（如果是10位的时间戳，这里不必除以1000）
select from_unixtime(1654358469000/1000,“%Y-%m-%d”);结果是：2022-06-05

4、常用到的函数

在工作中经常用到的便捷函数如：
比较大小的函数：max(字段名) 、min(字段名)
计算函数：sum(字段名)、avg(字段名)、count(字段名)、length(字段名)
除此之外，还可以在字段名前加上唯一性标识 distinct ；也可以是一个查询语句or条件语句。

①、获取最大值-max(字段名/条件语句/查询)

此函数用于从查询出的列表中获取到某个字段的最大值，但它不会锁定该字段所在的记录。

select max(uHeight) as maxHeight from user;//获得user表中最大的身高值

②、获取最小值-min(字段名/条件语句/查询)

此函数用于从查询出的列表中获取到某个字段的最大值，但它不会锁定该字段所在的记录。

select min(uHeight) as minHeight from user;//获得user表中最小的身高值。

③、获取平均值-avg(字段名/条件语句/查询)

可获取到查询结果中某字段的平均值；

select avg(uHeight) as avgHeight from user where uGender = 0;//获取表中男性的平均身高。

④、获取总和-sum(字段名/条件语句/查询)

可用于求总和

select sum(uHeight) from user;//获取身高总和
select sum(uGender=1 or 0) from user;//用于查询女性的数量

⑤、获取记录个数-count(字段名/条件语句/查询)

获取满足条件的记录数

select count(*) from user;//查询表的总记录数。
select count(uHeight > 170 or null ) ,uGender from user group by uGender;//获取身高大于170的男女用户个数。

三、条件语句

1、if语句

书写规范：if(条件,if语句或值1,if语句或值2')   其用法与C语言中的条件表达式相似，
表示如果条件满足，则为值1，否则为值2。
if中可以嵌套if

例如：以上述user表做实例：uGender字段 0表示男，1表示女，查询时可以直接判别
select uName, if(uGender=0,‘男’,‘女’) as sex from user;
结果如下：

2、case语句–判别输出

 书写规范：case 字段名  when 值1 then 想要的结果1   
 when 值2 then 想要的结果2     when ......    end 
 代表如果字段为值1时我们将结果为结果1，如果为值 2时，结果为结果2......

① 可用于一个字段多意时(用0 1 2 3 赋值表示)，这时可以讨论字段含义：
例如：以上述user表做实例，其中性别字段用0表示男，用1表示女
select case uGender when 0 then ‘男’ when 1 then ‘女’ end as sex;//查询结果同上
② 也可用于两级判断赋值： case when 字段满足某个条件 then 赋值 else 赋值 end//比如获取查询结果中某个字段为null时赋值为1，不为null时赋值为0
case when name is null then 1 else 0 end ;//即name为null时为1，否则为0

四、别名-AS

若你想给查询到的字段起别名的时候，使用as关键字。
给数据表另起别名可以直接写在其后  （select * from user u where u.uGender = 0）

书写规范 ：select uName as userName from user; 既是给name字段命名为userName。

五、数据处理

1、数据排序-order by

书写规范：order by 字段名 type;
应放于语句的末尾，type有两种，一为升序ASC，一为降序DESC，默认为升序；
排序方式根据字段属性排列，如果是数值型就比大小，如果是字符型就按照字符的比较方式分辨大小。
多个字段同时排序时：order by 字段1 type,字段2 type......

示例：select * from user order by uId;//这是查询到的数据就按照uId的大小升序排列。
放于查询语句的最后

2、数据分组-group by

书写规范：group by 字段1 ,字段2......
将字段重复的分在一个组，通常用于求相同项的某值的总和、字段拼接等。
也可用于去除某字段重复的记录，只保留一个想要的即可。
对多个字段同时分组时，根据书写的先后顺序，在前者分组的基础上，再根据后者分组。

搭配拼接：select group_concat(uName separator ‘,’) as names from user group by uGender;//根据性别分组，并将性别一致的用户名字以逗号隔开拼接在一起。

六、关键字exists 、not exists 、left join 、right join 、inner join

exists：表示如果子查询返回的结果集中至少有一条满足条件，则其返回结果为true，否则为false。
not exists：与exists相反，如果子查询返回结果中没有满足条件的，则其返回结果为true，否则false。
如果子查询数据量大时，会导致慢查询。

inner join：内连接查询，返回两个表中满足连接条件的交集。只有当连接条件匹配时，才会返回结果。
left join：左连接，返回左表中的所有记录以及右表中满足连接条件的记录，
			如果右表中没有匹配的记录，则右表的字段将为NULL。
right join：右连接，返回右表中的所有记录以及左表中满足连接条件的记录，
			如果左表中没有匹配的记录，则左表的字段将为NULL。	

-- 需求描述：查询商品表product中已经有卖出的商品信息，订单表orders；两表关联字段product_id
--exists
select p.product_name,p.product_amount,p.product_id
from product p
where exists(
	select 1 from orders o where o.product_id = p.product_id
)

-- not exists
select p.product_name,p.product_amount,p.product_id
from product p
where not exists(
	select 1 from orders o where o.product_id != p.product_id
)

七、带条件的更新语句（常用与初始化数据）

1、简单的修改语句

update product set product_name = "名牌包包" where product_code = "mpbb";

2、带复杂条件的更新语句

update product p
join orders o on o.product_code = p.product_code
set product_desc = "有销量啦！"
where p.delete_flag = '0';

-- join中带子查询
update product p
join (
	-- 可以加子查询语句
	select o.product_code from orders where **省略**
) t on t.product_code = p.product_code
set product_desc = "有销量啦！"
where p.delete_flag = '0';

总结

对数据库处理得当可使得数据的再处理更加简洁，
写本文的用意在于分享积累，希望博友们多多批评。😃😃😃😃