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

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

基础SQL结构

一个基础的SQL语句结构是

1
2
3
4
5
6
7
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

user_profile

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

  • 查询单列
1
SELECT device_id FROM  user_profile

user_profile是表名,device_id是列名

  • 查询多列
    在 SELECT 后面添加对应查询的列
    1
    Select device_id,gender from user_profile
  • 查所有
    1
    Select * from user_profile

别名

别名的常见用法是在检索出的结果中重命名表的列字段(为了符合特定的报表要求或客户需求)
Vendors表
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,按供应商名称对结果进行升序排序。

1
2
3
4
5
select vend_id,
vend_name as vname,
vend_address as vaddress,
vend_city as vcity
from Vendors order by vname

条件查询

  • 去重:distinct
    1
    select distinct prod_id
  • 非空:is not null
    1
    where prod_id is not null
  • 排序:顺序 ASC 降序 DESC; ASC是默认,DESC得声明
    1
    2
    3
    order by cust_name desc
    order by cust_id ,order_date desc;//一个降序
    order by quantity desc, item_price desc;//两个降序

    数学计算

    Products表
  • 等于特定值 : 在Where的位置添加=判定;
    1
    2
    3
    select prod_id,prod_name
    from Products
    where prod_price=9.49 // 添加等于条件
  • 大于等于特定值 : 在Where的位置添加>=判定;
    1
    2
    3
    select prod_id,prod_name
    from Products
    where prod_price>=9
  • 特定值之间 : 在Where的位置添加>=<=判定,用AND做并列
    1
    2
    3
    select prod_name, prod_price
    from Products
    where prod_price >= 3 and prod_price <= 6
  • 特定值之间(不包含) : 在Where的位置添加between
    1
    2
    3
    4
    select prod_name,prod_price
    from Products
    where prod_price between 3 and 6
    order by prod_price
  • 逻辑包含:在Where的位置添加in,里面的内容用()''.
    1
    2
    3
    SELECT * 
    FROM Persons
    WHERE LastName IN ('Adams','Carter')
  • 逻辑与关系:在Where的位置添加AND做并列,文本用""引住
    1
    2
    3
    select vend_name
    from Vendors
    where vend_country="USA" and vend_state="CA"

模糊匹配

LIKE 关键字

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

Products表

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

1
2
3
select prod_name ,prod_desc
from Products
where prod_desc like '%toy%' //''括起来

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

1
2
3
4
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 比较。

1
2
3
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 即可。

1
2
3
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

1
2
select prod_id,prod_price,prod_price*0.9 as sale_price
from Products
字符串函数

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

1
2
3
select cust_id, cust_name,
upper(concat(substring(cust_contact, 1, 2), substring(cust_city, 1, 3))) as user_login
from Customers
1
2
3
4
5
6
upper()              将字母转化为大写,
lower() 将字母转化为小写,
concat() 将字符串连接起来,
left(2022,2) 截取左边两个字符,结果是20,
right(2022,2) 截取右边两个字符,结果是22,
substring(name,5,3) 截取name这个字段,从第5个字符开始,只截取之后的3个字符

日期函数

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

1
2
3
4
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”
  • 可返回正确结果:手写精确到秒的
    1
    select * from Orders where order_date between "2020-01-01 00:00:00" and "2020-01-31 23:59:59";

    统计运算

    OrderItems表代表售出的产品,quantity代表售出商品数量。
    【问题】编写 SQL 语句,确定已售出产品的总数。
    1
    2
    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)

基本语法是:

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

连接运算符

交叉连接(CROSS JOIN)

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

隐式的交叉连接,没有CROSS JOIN
1
2
3
SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME  
FROM ORDERS O , CUSTOMERS C
WHERE O.ID=1;
显式的交叉连接,使用CROSS JOIN
1
2
select * 
from ex1 e cross join ex2 d;

左表有16行数据,右表有5行数据,任意连接即有16*5=80行数据。

内连接(INNER JOIN)

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

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

1
2
3
4
//两个表分别是 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条件过滤后的笛卡尔积。

1
2
3
SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME  
FROM CUSTOMERS C INNER JOIN ORDERS O
ON C.ID=O.CUSTOMER_ID;
等值连接

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

1
2
3
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。

1
2
3
4
5
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)
  • 内连接只返回满足连接条件的数据行
  • 外连接不只列出与连接条件相匹配的行,而是列出左表(左外连接时)、右表(右外连接时)或两个表(全外连接时)中所有符合搜索条件的数据行
    外连接分为左外连接、右外链接、全外连接三种。
    左外连接
    左连接的结果不仅包含满足连接条件的数据行,还包括左表的不满足连接条件的数据行。
    1
    2
    3
    4
    // 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;
    满足连接条件的有14行,左表中不满足连接条件的有2行,故左连接的查询结果应有16行。
    右外连接
    右连接的结果不仅包含满足连接条件的数据行,还包括右表的不满足连接条件的数据行。
    1
    2
    3
    4
    // 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;
    满足连接条件的有14行,右表中有2行不满足连接条件,故右连接的查询结果应有16行。
    完全外连接

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

1
2
3
4
// 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

1
2
3
4
5
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**
1) 内连接 

1
2
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)左连接 

1
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) 右连接 

1
2
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) 完全连接 

1
2
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 王武

**示例2**
三个表做左外链接:

1
2
3
4
5
6
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填写。

  1. 两表连接查询时,选择哪种查询的依据

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

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

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

1
2
3
4
5
6
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;

多表连接的一个示范