SQL的初步学习（二）（以MySQL为例）

目录

一、SQL 基础语法

3.DQL（数据查询语言）

单表查询 

（1）基本查询

查询多个字段：

查询所有字段（通配符）：

设置别名：

 去除重复记录：

练习题：

 （2）分组查询

基础语法

常用的聚合函数

注意

练习题：

（3）分组查询

基础语法

where与having区别

 注意

 练习题：

 （4）排序查询

基础语法

排序方式

 练习题：

 （5）分页查询

基础语法

注意

多表查询

 （1）连接查询

a.内连接

b.外连接

 （2）子查询

标量子查询

列子查询

行子查询

表子查询

4.TCL（事务控制语言）

概念 

注意

 主要语法

---

一、SQL 基础语法

3.DQL（数据查询语言）

常用比较运算符

 常用逻辑运算符

常用函数

字符串函数：

CONCAT(str1, str2, ...);-- 连接字符串。
LENGTH(str);-- 返回字符串的长度。
UPPER(str);-- 转换为大写。
LOWER(str);-- 转换为小写。
SUBSTRING(str, start, length);--提取子字符串。
TRIM(str);-- 去除字符串两端的空格。
REPLACE(str, find_string, replace_with);-- 替换字符串
LEFT(str, length);-- 从左侧提取字符串
RIGHT(str, length);-- 从右侧提取字符串

数值函数：

ROUND(number, decimals);-- 四舍五入。
CEILING(number);-- 向上取整。
FLOOR(number);-- 向下取整。
ABS(number);-- 绝对值。
SQRT(number);-- 平方根。
RAND();-- 随机数（0.0 到 1.0）。

日期和时间函数：

NOW();-- 当前日期和时间。
CURDATE();-- 当前日期。
CURTIME();-- 当前时间。
DATE(datetime);-- 提取日期部分。
TIME(datetime);-- 提取时间部分。
YEAR(date);-- 提取年份。
MONTH(date);-- 提取月份。
DAY(date);-- 提取日期。
DATE_ADD(date, INTERVAL value unit);-- 日期加操作。
DATE_SUB(date, INTERVAL value unit);-- 日期减操作。
DATEDIFF(date1, date2);-- 计算日期差。
STR_TO_DATE(str, format);-- 字符串转换为日期。
DATE_FORMAT(date, format);-- 日期格式化。

主要语法

select 字段列表 from 表名 where 条件列表;

单表查询 

（1）基本查询

查询多个字段：

select 字段1,字段2,字段3... from 表名;

查询所有字段（通配符）：

select * from 表名;

设置别名：

as关键字可以省略，若别名中有空格等特殊字符需用单引号包裹别名。

select 字段1 [as 别名1],字段2 [as 别名2] from 表名;

未设置别名前

设置别名后

 

 去除重复记录：

distinct可以放在需要去除重复的字段前，修饰这个字段。

select distinct 字段列表 from 表名;

练习题：

1757. 可回收且低脂的产品https://leetcode.cn/problems/recyclable-and-low-fat-products/

select product_id 
from Products 
where low_fats = 'Y' and recyclable = 'Y';

 584. 寻找用户推荐人https://leetcode.cn/problems/find-customer-referee/

select name
from Customer
where referee_id != 2 or referee_id is null;

595. 大的国家https://leetcode.cn/problems/big-countries/

SELECT name,population,area 
FROM World
WHERE area>=3000000 OR population >=25000000;

 （2）分组查询

基础语法

select 聚合函数(字段列表) from 表名;

常用的聚合函数

注意

 

练习题：

1211. 查询结果的质量和占比https://leetcode.cn/problems/queries-quality-and-percentage/

SELECT query_name,
ROUND(AVG(rating*1.0/position),2) AS quality,-- round函数为四舍五入函数
ROUND(AVG(IF(rating<3,1.0,0.0))*100,2) AS poor_query_percentage-- if函数后续会讲
FROM Queries
GROUP BY query_name;

（3）分组查询

基础语法

group by后可以接多个字段，分组时依次按字段分组。

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

where与having区别

1.执行时机不同:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。
2.判断条件不同:where不能对聚合函数进行判断，而having可以。

 注意

 练习题：

2356. 每位教师所教授的科目种类的数量https://leetcode.cn/problems/number-of-unique-subjects-taught-by-each-teacher/

select teacher_id,count(distinct subject_id) as cnt
from Teacher
group by teacher_id;

 （4）排序查询

基础语法

order by后可以接多个字段，排序时当前面的字段全部相等时按该字段排序。不写排序方式默认是升序排序。

select 字段列表 from [where 条件列表][group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2...;

排序方式

 练习题：

620. 有趣的电影https://leetcode.cn/problems/not-boring-movies/

SELECT id,movie,description,rating
FROM cinema
WHERE id%2=1 AND description!='boring'
ORDER BY rating DESC;-- 降序排序

 （5）分页查询

基础语法

select 字段列表 from 表名 limit 起始索引,查询记录数;

注意

多表查询

 

 （1）连接查询

a.内连接

相当于查询两表交集的部分数据。

基础语法

 隐式内连接

select 字段列表 from 表1,表2 where 条件...;

显式内连接

inner可以省略不写。

select 字段列表 from 表1 [inner] join 表2 on 连接条件...;

练习题：

1068. 产品销售分析 Ihttps://leetcode.cn/problems/product-sales-analysis-i/

select p.product_name,s.year,s.price
from Sales s,Product p  -- 给表名取别名，只是方便写而已
where s.product_id=p.product_id;

 或者

select p.product_name,s.year,s.price
from Sales s inner join Product p on  -- 给表名取别名，只是方便写而已
s.product_id=p.product_id;

b.外连接

左连接时以左边的表为基础，去连接右边的表；而右连接恰好相反。

outer可以省略不写。

左外连接

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

右外连接

select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;

练习题

1378. 使用唯一标识码替换员工IDhttps://leetcode.cn/problems/replace-employee-id-with-the-unique-identifier/

select d.unique_id,e.name 
from Employees e left join EmployeeUNI d 
on e.id=d.id;

1581. 进店却未进行过交易的顾客https://leetcode.cn/problems/customer-who-visited-but-did-not-make-any-transactions/ 

select v.customer_id,count(*) as count_no_trans
from Visits v left outer join Transactions t 
on v.visit_id=t.visit_id 
where t.amount is null and t.transaction_id is null
group by v.customer_id;

 （2）子查询

SQL语句中嵌套select语句，称为嵌套查询，又称子查询。

子查询外部的语句可以是insert/update/delete/select 的任何一个，最常见的是 select。

select * from t1 where column1 =(select column1 from t2 ...);

标量子查询

返回单个值 (一个行，一个列)。

列子查询

 返回单列多行 (一列，多行)。

行子查询

 返回单行多列 (一行，多列)。

表子查询

 返回多行多列，结果集可以看作是一个虚拟表 (多行，多列)。 练习题：

1174. 即时食物配送 IIhttps://leetcode.cn/problems/immediate-food-delivery-ii/

select 
round(avg(if(order_date=customer_pref_delivery_date,1.0,0.0))*100,2) as immediate_percentage
from Delivery 
where(customer_id,order_date)in(select customer_id,min(order_date)from Deliverygroup by customer_id
);

4.TCL（事务控制语言）

概念 

事务 是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。 

注意

默认MySQL的事务是自动提交的，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务。

 主要语法

start transaction;-- 开启事务... -- 一些SQL语言操作commit;-- 提交事务rollback； -- 操作失败后，使用以回滚事务