刷题日记-SQL

美团面试和MySQL相关的部分问到了SQL语句的写法，统一研究下。

参考链接：
SQL是什么 (biancheng.net)

基础SQL结构

一个基础的SQL语句结构是

SELECT select_list
[INTO new_table]
FROM table_name
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING search_condition]
[ORDER BY order_expression[ASC|DESC]]

必需的子句只有SELECT子句和FROM子句，其他的子句都是可选的。各子句具体含义如下：

• SELECT子句：指定由查询返回的列。
• INTO子句：将检索结果存储到新表或视图中。
• FROM子句：用于指定引用的列所在的表或视图。如果对象不止一个，那么它们之间必须用逗号分开。
• WHERE子句：指定用于限制返回的行的搜索条件。如果SELECT语句没有WHERE子句，DBMS假设目标表中的所有行都满足搜索条件。
• GROUP BY子句：指定用来放置输出行的组，并且如果SELECT子句<select list>中包含聚合函数，则计算每组的汇总值。
• HAVING子句：指定组或聚合的搜索条件。HAVING通常与GROUP BY子句一起使用。如果不使用GROUP BY子句，HAVING的行为与WHERE子句一样。
• ORDER BY子句：指定结果集的排序。ASC关键字表示升序排列结果，DESC关键字表示降序排列结果。如果没有指定任何一个关键字，那么ASC就是默认的关键字。如果没有ORDER BY子句，DBMS将根据输入表中的数据的存放位置来显示数据。

顺序序号	子句关键词	子句功能
1	SELECT	从指定表中取出指定列的数据
2	FROM	指定要查询操作的表
3	WHERE	用来规定一种选择查询的标准
4	GROUP BY	对结果集进行分组，常与聚合函数一起使用
5	HAVING	返回选取的结果集中行的数目
6	ORDER BY	指定分组的搜寻条件

SQL 实践练习

单表CRUD

表中有字段 id, device_id, gender, age, university, province;

• 查询单列

SELECT device_id FROM  user_profile

user_profile是表名，device_id是列名

• 查询多列
  在 SELECT 后面添加对应查询的列

  Select device_id,gender from user_profile

• 查所有

  Select * from user_profile

别名

别名的常见用法是在检索出的结果中重命名表的列字段（为了符合特定的报表要求或客户需求）

vend_id供应商id、vend_name供应商名称、vend_address供应商地址、vend_city供应商城市

【问题】编写 SQL 语句，从 Vendors 表中检索vend_id、vend_name、vend_address 和 vend_city，将 vend_name重命名为 vname，将 vend_city 重命名为 vcity，将 vend_address重命名为 vaddress，按供应商名称对结果进行升序排序。

select vend_id,
vend_name as vname, 
vend_address as vaddress,
vend_city as vcity
from Vendors order by vname

条件查询

• 去重：distinct

  select distinct prod_id

• 非空：is not null

  where prod_id is not null

• 排序：顺序 ASC 降序 DESC; ASC是默认，DESC得声明

  order by cust_name desc
  order by cust_id ,order_date desc;//一个降序
  order by quantity desc, item_price desc;//两个降序

  数学计算

  
• 等于特定值 : 在Where的位置添加=判定;

  select prod_id,prod_name
  from Products
  where prod_price=9.49 // 添加等于条件

• 大于等于特定值 : 在Where的位置添加>=判定;

  select prod_id,prod_name
  from Products
  where prod_price>=9

• 特定值之间 : 在Where的位置添加>=，<=判定,用AND做并列

  select prod_name, prod_price
  from Products
  where prod_price >= 3 and prod_price <= 6

• 特定值之间（不包含） : 在Where的位置添加between

  select prod_name,prod_price
  from Products 
  where prod_price between 3 and 6
  order by prod_price

• 逻辑包含：在Where的位置添加in,里面的内容用()和''.

  SELECT * 
  FROM Persons
  WHERE LastName IN ('Adams','Carter')

• 逻辑与关系：在Where的位置添加AND做并列,文本用""引住

  select vend_name
  from Vendors
  where vend_country="USA" and vend_state="CA"

模糊匹配

LIKE 关键字

1. %： 匹配任意字符
2. _：匹配单个字符
3. 一般 link 关键字只用来匹配字段类型为字符串的

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中包含 toy 一词的产品名称

select prod_name ,prod_desc
from Products
where prod_desc like '%toy%' //用''括起来

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中未出现 toy 一词的产品，最后按”产品名称“对结果进行排序。

select prod_name,prod_desc
from Products
where prod_desc not like '%toy' //是 not like
order by prod_name

• not like ‘toy%’，即选出不包含“toy”（前后均无内容了）及“toyXXXX”的内容，不符合要求
• not like ‘%toy’，即选出不包含“toy”（前后均无内容了）及“XXXXtoy”的内容，能满足示例要求
• 最科学的应该是 not like ‘%toy%’

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作，但对于这个挑战题，请使用 AND 和两个 LIKE 比较。

select prod_name,prod_desc 
from Products 
where prod_desc like '%carrots%' and prod_desc like '%toy%';

• $toy$ 符号只是运用在前端网页提交信息的时候的EL表达式${};而mysql中没有这个模糊查询的符号

【问题】编写 SQL 语句，从 Products 表中检索产品名称（prod_name）和描述（prod_desc），仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示：只需要用带有三个 % 符号的 LIKE 即可。

select prod_name,prod_desc 
from Products 
where prod_desc LIKE "%toy%carrots%";

函数

一般函数

【问题】编写 SQL语句，从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示：可以乘以 0.9，得到原价的 90%（即 10%的折扣）
【方法】在select中就计算: prod_price*0.9

select prod_id,prod_price,prod_price*0.9 as sale_price
from Products

字符串函数

【问题】编写 SQL 语句，返回顾客 ID（cust_id）、顾客名称（cust_name）和登录名（user_login），其中登录名全部为大写字母，并由顾客联系人的前两个字符（cust_contact）和其所在城市的前三个字符（cust_city）组成。提示：需要使用函数、拼接和别名。

select cust_id, cust_name,
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from Customers

upper()              将字母转化为大写，
lower()              将字母转化为小写，
concat()             将字符串连接起来，
left(2022,2)         截取左边两个字符，结果是20，
right(2022,2)        截取右边两个字符，结果是22，
substring(name,5,3)  截取name这个字段，从第5个字符开始，只截取之后的3个字符

日期函数

【问题】编写 SQL 语句，返回 2020 年 1 月的所有订单的订单号（order_num）和订单日期（order_date），并按订单日期升序排序

select order_num,order_date 
from Orders
where year(order_date) = 2020 and month(order_date) = 1
order by order_date

• 单独取年是year，单独取月是month
• 在between中使用日期时，SQL会默认将date格式转换为datetime格式，具体操作就是为date格式后面添加” 00:00:00”按照上面逻辑就成了between “2020-01-01 00:00:00” and “2020-01-31 00:00:00，2020-01-31这一天实际上是没有被囊括进去的应改为 between “2020-01-01” and “2020-02-01”
• 可返回正确结果：手写精确到秒的

  select * from Orders where order_date between "2020-01-01 00:00:00" and "2020-01-31 23:59:59";

  统计运算

  OrderItems表代表售出的产品，quantity代表售出商品数量。
  【问题】编写 SQL 语句，确定已售出产品的总数。

  select sum(quantity) as items_ordered
  from OrderItems

• sum求和,avg求平均值,max求最大值,min求最小值,count是计数

多表CRUD

涉及到多表的CRUD比较麻烦，基础是两个表的笛卡尔积和过滤

• 两表连接查询：对两表求积（笛卡尔积）并用==ON==条件和连接连接类型进行过滤形成中间表；然后根据==WHERE==条件过滤中间表的记录，并根据==SELECT==指定的列返回查询结果。
• 多表连接查询：先对==第一个和第二个表==按照两表连接做查询，然后用==查询结果和第三个表==做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果。

  ON条件： 是过滤两个链接表笛卡尔积形成中间表的约束条件。
  WHERE条件：
  在有ON条件的SELECT语句中是过滤中间表的约束条件。
  在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。
  在两表或多表连接中是限制连接形成最终中间表的返回结果的约束。
  从这里可以看出，将WHERE条件移入ON后面是不恰当的。
  推荐的做法是：ON只进行连接操作，WHERE只过滤中间表的记录。(链接只用on)

基本语法是：

SELECT 字段1,字段2,字段3,…… FROM 表名1 INNER JOIN 表名2 ON 关联条件；
等同于
SELECT 字段1,字段2,字段3,…… FROM 表名1,表名2 WHERE 关联条件；

连接运算符

交叉连接(CROSS JOIN)

内外链接的基础，反馈笛卡尔积。有两种，显式的和隐式的。不带ON子句，返回的是两表的乘积

隐式的交叉连接，没有CROSS JOIN

SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME  
FROM ORDERS O , CUSTOMERS C  
WHERE O.ID=1;

显式的交叉连接，使用CROSS JOIN

select * 
from ex1 e cross join ex2 d;

内连接(INNER JOIN)

内连接（INNER JOIN）有两种，显式的和隐式的，返回连接表中符合连接条件和查询条件的数据行。
内连接使用比较运算符（包括=、>、<、<>、>=、<=、!> 和!<）进行表间的比较操作，查询与连接条件相匹配的数据。根据所使用的比较方式不同，内连接分为等值连接、自然连接和自连接三种。

语句1：隐式的内连接，没有INNER JOIN，形成的中间表为两个表的笛卡尔积。

//两个表分别是 ORDERS,CUSTOMERS
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME  
FROM CUSTOMERS C,ORDERS O  //此处用到隐式内连接
WHERE C.ID=O.CUSTOMER_ID;

语句2：显示的内连接，一般称为内连接，有INNER JOIN，形成的中间表为两个表经过ON条件过滤后的笛卡尔积。

SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME  
FROM CUSTOMERS C INNER JOIN ORDERS O 
ON C.ID=O.CUSTOMER_ID;

等值连接

使用”=”关系将表连接起来的查询，其查询结果中列出被连接表中的所有列，包括其中的重复列。

SELECT PM_ACT_JOB_RLS.*, PM_ACT_RLS.*
FROM PM_ACT_JOB_RLS INNER JOIN PM_ACT_RLS 
ON PM_ACT_JOB_RLS.RlsPK = PM_ACT_RLS.RlsPK

自然连接

等值连接中去掉重复的列，形成的连接。

说真的，这种连接查询没有存在的价值，既然是SQL2标准中定义的，就给出个例子看看吧。自然连接无需指定连接列，SQL会检查两个表中是否相同名称的列，且假设他们在连接条件中使用，并且在连接条件中仅包含一个连接列。不允许使用ON语句，不允许指定显示列，显示列只能用*表示。对于每种连接类型（除了交叉连接外），均可指定NATURAL。

SELECT
PM_ACT_JOB_RLS.JobPK, PM_ACT_RLS.RlsPK, RlsName 
FROM 
PM_ACT_JOB_RLS 
Natural INNER JOIN PM_ACT_RLS ON PM_ACT_JOB_RLS.RlsPK = PM_ACT_RLS.RlsPK // Natural

外连接(OUTER JOIN)

• 内连接只返回满足连接条件的数据行
• 外连接不只列出与连接条件相匹配的行，而是列出左表（左外连接时）、右表（右外连接时）或两个表（全外连接时）中所有符合搜索条件的数据行。
  外连接分为左外连接、右外链接、全外连接三种。

  左外连接

  左连接的结果不仅包含满足连接条件的数据行，还包括左表的不满足连接条件的数据行。

  // left join
  select e.empno,e.ename,e.deptno,d.deptno,d.dname 
  from ex1 e left join ex2 d 
  on e.deptno=d.deptno;

  

  右外连接

  右连接的结果不仅包含满足连接条件的数据行，还包括右表的不满足连接条件的数据行。

  // right join
  select e.empno,e.ename,e.deptno,d.deptno,d.dname 
  from ex1 e right join ex2 d 
  on e.deptno=d.deptno;

  

  完全外连接

!!! 注意：MySQL是不支持全外的连接的 !!!
完全外连接的结果不仅包含满足连接条件的数据行，还包括左表和右表中不满足连接条件的数据行。

// full join
select e.empno,e.ename,e.deptno,d.deptno,d.dname 
from ex1 e full join ex2 d 
on e.deptno=d.deptno;

但是可以MySQL通过左外和右外求合集来获取全外连接的查询结果。 用LEFT JOIN+UNION+RIGHT JOIN。

SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME  
FROM ORDERS O LEFT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID  
UNION
SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME  
FROM ORDERS O RIGHT OUTER JOIN CUSTOMERS C ON C.ID=O.CUSTOMER_ID;

---

• 内连接、外连接的列数 = 左表列数+右表列数
• 内连接、外连接可以使用别名
• 自然连接的列数 = 左表列数+右表列数-左表和右表中名字相同的列数（去除重复列）
• 自然连接 不能 使用别名，不需要on子句
• 自然连接要求连接的两个表 必须 要有相同的列名

---

举例

1） 内连接 

select a.*,b.* 
from a inner join b on a.id=b.parent_id   

结果是 

id	name	id	job	parent_id
1	张3	1	23	1
2	李四	2	34	2

2）左连接 

select a.*,b.* 
from a left join b on a.id=b.parent_id   

结果是 

id	name	id	job	parent_id
1	张3	1	23	1
2	李四	2	34	2
3	王武

3） 右连接 

select a.*,b.* 
from a right join b on a.id=b.parent_id   

结果是 

id	name	id	job	parent_id
1	张3	1	23	1
2	李四	2	34	2
		3	34	4

4） 完全连接 

select a.*,b.* 
from a full join b on a.id=b.parent_id   

结果是 

id	name	id	job	parent_id
1	张3	1	23	1
2	李四	2	34	2
		3	34	4
3	王武

三个表做左外链接:

SELECT *
FROM TESTA 
LEFT OUTER JOIN TESTB 
ON TESTA.A=TESTB.A
LEFT OUTER JOIN TESTC
ON TESTA.A=TESTC.A

总结

1. 内、外连接的区别

仅从定义上看，就可以清晰的分辨出了。

• 内连接：指连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件。也就是，返回2个表中完全符合条件的记录，结果集中每个表的字段内容均来自各自的表。

• 外连接：连接结果不仅包含符合连接条件的行，同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。也就是，返回2个表中完全符合条件的记录，再加上2个表中各自的记录，结果集中的字段只有一个表中有记录时，另一个表中的字段均使用空值null填写。

2. 两表连接查询时，选择哪种查询的依据

连接查询是SQL查询的核心，连接查询的连接类型选择依据实际需求。如果选择不当，非但不能提高查询效率，反而会带来一些逻辑错误或者性能低下。

1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
5、 求差操作的时候用联合查询。

多个表查询的时候，这些不同的连接类型可以写到一块。例如：

SELECT T1.C1,T2.CX,T3.CY  
FROM TAB1 T1  
INNER JOIN TAB2 T2 ON (T1.C1=T2.C2)  
INNER JOIN TAB3 T3 ON (T1.C1=T2.C3)  
LEFT OUTER JOIN TAB4 ON(T2.C2=T3.C3);  
WHERE T1.X >T3.Y;

多表连接的一个示范