刷题日记-SQL
美团面试和MySQL相关的部分问到了SQL语句的写法,统一研究下。
基础SQL结构
一个基础的SQL语句结构是
1 | SELECT select_list |
必需的子句只有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;
- 查询单列
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
别名
别名的常见用法是在检索出的结果中重命名表的列字段(为了符合特定的报表要求或客户需求)
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 | select vend_id, |
条件查询
- 去重:distinct
1
select distinct prod_id
- 非空:is not null
1
where prod_id is not null
- 排序:顺序 ASC 降序 DESC; ASC是默认,DESC得声明
1
2
3order by cust_name desc
order by cust_id ,order_date desc;//一个降序
order by quantity desc, item_price desc;//两个降序数学计算
- 等于特定值 : 在
Where
的位置添加=
判定;1
2
3select prod_id,prod_name
from Products
where prod_price=9.49 // 添加等于条件 - 大于等于特定值 : 在
Where
的位置添加>=
判定;1
2
3select prod_id,prod_name
from Products
where prod_price>=9 - 特定值之间 : 在
Where
的位置添加>=
,<=
判定,用AND
做并列1
2
3select prod_name, prod_price
from Products
where prod_price >= 3 and prod_price <= 6 - 特定值之间(不包含) : 在
Where
的位置添加between
1
2
3
4select prod_name,prod_price
from Products
where prod_price between 3 and 6
order by prod_price - 逻辑包含:在
Where
的位置添加in
,里面的内容用()
和''
.1
2
3SELECT *
FROM Persons
WHERE LastName IN ('Adams','Carter') - 逻辑与关系:在
Where
的位置添加AND
做并列,文本用""
引住1
2
3select vend_name
from Vendors
where vend_country="USA" and vend_state="CA"
模糊匹配
LIKE 关键字
%
: 匹配任意字符_
:匹配单个字符- 一般 link 关键字只用来匹配字段类型为字符串的
【问题】编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中包含 toy 一词的产品名称
1 | select prod_name ,prod_desc |
【问题】编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中未出现 toy 一词的产品,最后按”产品名称“对结果进行排序。
1 | select prod_name,prod_desc |
- not like ‘toy%’,即选出不包含“toy”(前后均无内容了)及“toyXXXX”的内容,不符合要求
- not like ‘%toy’,即选出不包含“toy”(前后均无内容了)及“XXXXtoy”的内容,能满足示例要求
- 最科学的应该是 not like ‘%toy%’
【问题】编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回描述中同时出现 toy 和 carrots 的产品。有好几种方法可以执行此操作,但对于这个挑战题,请使用 AND 和两个 LIKE 比较。
1 | select prod_name,prod_desc |
$toy$
符号只是运用在前端网页提交信息的时候的EL表达式${};而mysql中没有这个模糊查询的符号
【问题】编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示:只需要用带有三个 % 符号的 LIKE 即可。
1 | select prod_name,prod_desc |
函数
一般函数
【问题】编写 SQL语句,从 Products 表中返回 prod_id、prod_price 和 sale_price。sale_price 是一个包含促销价格的计算字段。提示:可以乘以 0.9,得到原价的 90%(即 10%的折扣)
【方法】在select
中就计算: prod_price*0.9
1 | select prod_id,prod_price,prod_price*0.9 as sale_price |
字符串函数
【问题】编写 SQL 语句,返回顾客 ID(cust_id)、顾客名称(cust_name)和登录名(user_login),其中登录名全部为大写字母,并由顾客联系人的前两个字符(cust_contact)和其所在城市的前三个字符(cust_city)组成。提示:需要使用函数、拼接和别名。
1 | select cust_id, cust_name, |
1 | upper() 将字母转化为大写, |
日期函数
【问题】编写 SQL 语句,返回 2020 年 1 月的所有订单的订单号(order_num)和订单日期(order_date),并按订单日期升序排序
1 | select order_num,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
2select 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 | SELECT 字段1,字段2,字段3,…… FROM 表名1 INNER JOIN 表名2 ON 关联条件; |
连接运算符
交叉连接(CROSS JOIN)
内外链接的基础,反馈笛卡尔积。有两种,显式的和隐式的。不带ON子句,返回的是两表的乘积
隐式的交叉连接,没有CROSS JOIN
1 | SELECT O.ID, O.ORDER_NUMBER, C.ID, C.NAME |
显式的交叉连接,使用CROSS JOIN
1 | select * |
内连接(INNER JOIN)
内连接(INNER JOIN)有两种,显式的和隐式的,返回连接表中符合连接条件和查询条件的数据行。
内连接使用比较运算符(包括=、>、<、<>、>=、<=、!> 和!<)进行表间的比较操作,查询与连接条件相匹配的数据。根据所使用的比较方式不同,内连接分为等值连接、自然连接和自连接三种。
语句1:隐式的内连接,没有INNER JOIN,形成的中间表为两个表的笛卡尔积。
1 | //两个表分别是 ORDERS,CUSTOMERS |
语句2:显示的内连接,一般称为内连接,有INNER JOIN,形成的中间表为两个表经过ON条件过滤后的笛卡尔积。
1 | SELECT O.ID,O.ORDER_NUMBER,C.ID,C.NAME |
等值连接
使用”=”关系将表连接起来的查询,其查询结果中列出被连接表中的所有列,包括其中的重复列。
1 | SELECT PM_ACT_JOB_RLS.*, PM_ACT_RLS.* |
自然连接
等值连接中去掉重复的列,形成的连接。
说真的,这种连接查询没有存在的价值,既然是SQL2标准中定义的,就给出个例子看看吧。自然连接无需指定连接列,SQL会检查两个表中是否相同名称的列,且假设他们在连接条件中使用,并且在连接条件中仅包含一个连接列。不允许使用ON语句,不允许指定显示列,显示列只能用*
表示。对于每种连接类型(除了交叉连接外),均可指定NATURAL。
1 | SELECT |
外连接(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;右外连接
右连接的结果不仅包含满足连接条件的数据行,还包括右表的不满足连接条件的数据行。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;完全外连接
!!! 注意:MySQL是不支持全外的连接的 !!!
完全外连接的结果不仅包含满足连接条件的数据行,还包括左表和右表中不满足连接条件的数据行。
1 | // full join |
但是可以MySQL通过左外和右外求合集来获取全外连接的查询结果。 用LEFT JOIN
+UNION
+RIGHT JOIN
。
1 | SELECT O.ID,O.ORDER_NUMBER,O.CUSTOMER_ID,C.ID,C.NAME |
- 内连接、外连接的列数 = 左表列数+右表列数
- 内连接、外连接可以使用别名
- 自然连接的列数 = 左表列数+右表列数-左表和右表中名字相同的列数(去除重复列)
- 自然连接 不能 使用别名,不需要on子句
- 自然连接要求连接的两个表 必须 要有相同的列名
举例
1) 内连接
1 | select a.*,b.* |
结果是
id | name | id | job | parent_id |
---|---|---|---|---|
1 | 张3 | 1 | 23 | 1 |
2 | 李四 | 2 | 34 | 2 |
2)左连接
1 | select a.*,b.* |
结果是
id | name | id | job | parent_id |
---|---|---|---|---|
1 | 张3 | 1 | 23 | 1 |
2 | 李四 | 2 | 34 | 2 |
3 | 王武 |
3) 右连接
1 | select a.*,b.* |
结果是
id | name | id | job | parent_id |
---|---|---|---|---|
1 | 张3 | 1 | 23 | 1 |
2 | 李四 | 2 | 34 | 2 |
3 | 34 | 4 |
4) 完全连接
1 | select a.*,b.* |
结果是
id | name | id | job | parent_id |
---|---|---|---|---|
1 | 张3 | 1 | 23 | 1 |
2 | 李四 | 2 | 34 | 2 |
3 | 34 | 4 | ||
3 | 王武 |
三个表做左外链接:
1 | SELECT * |
总结
- 内、外连接的区别
仅从定义上看,就可以清晰的分辨出了。
内连接:指连接结果仅包含符合连接条件的行,参与连接的两个表都应该符合连接条件。也就是,返回2个表中完全符合条件的记录,结果集中每个表的字段内容均来自各自的表。
外连接:连接结果不仅包含符合连接条件的行,同时也包含自身不符合条件的行。包括左外连接、右外连接和全外连接。也就是,返回2个表中完全符合条件的记录,再加上2个表中各自的记录,结果集中的字段只有一个表中有记录时,另一个表中的字段均使用空值null填写。
- 两表连接查询时,选择哪种查询的依据
连接查询是SQL查询的核心,连接查询的连接类型选择依据实际需求。如果选择不当,非但不能提高查询效率,反而会带来一些逻辑错误或者性能低下。
1、 查两表关联列相等的数据用内连接。
2、 Col_L是Col_R的子集时用右外连接。
3、 Col_R是Col_L的子集时用左外连接。
4、 Col_R和Col_L彼此有交集但彼此互不为子集时候用全外。
5、 求差操作的时候用联合查询。
多个表查询的时候,这些不同的连接类型可以写到一块。例如:
1 | SELECT T1.C1,T2.CX,T3.CY |
多表连接的一个示范