1.多字段排序用逗号隔开
对多个字段排序的时候,同样满足默认ASC
select cust_id , order_num from Orders order by cust_id , order_date desc
select cust_id , order_num from Orders order by cust_id asc , order_date desc
这俩是一样的,即先按cust_id
升序(默认),再按order_date
降序
多字段用逗号隔开,而非and
2.varchar类型加’单引号’,数字型可加可不加
-
prod_price是double类型的,下面两种写法都一样,在SQL中,数字类型都可以不加单引号
select prod_id, prod_name from Products where prod_price = '9.49' select prod_id, prod_name from Products where prod_price = 9.49
-
而另一个案例中,这俩字段都是varchar类型,所以必须加单引号
select vend_name from Vendors where vend_country = 'USA' and vend_state = 'CA'
-
只要是varchar类型,无论是在哪个地方出现(例如in),都要加单引号
select order_num, prod_id, quantity from OrderItems where quantity >= 100 and prod_id in ('BR01','BR02','BR03')
3.加不加`反引号`问题
4.对于字符串加’单引号’还是"双引号"都一样
对于字符串来说都一样
例如如下两种写法都是一样的
select prod_name, prod_desc from Products where prod_desc not like "%toy%"
select prod_name, prod_desc from Products where prod_desc not like '%toy%'
5.未出现的词、模糊匹配: not like及多种写法
NOT LIKE及其同义写法
SELECT
prod_name, prod_desc
FROM
Products
WHERE
prod_desc NOT LIKE '%toy%'
# prod_desc NOT REGEXP 'toy' --正则匹配,只要匹配到toy就算,加个NOT就是不取
# LOCATE('toy', prod_desc) = 0
# INSTR(prod_desc, 'toy') = 0
# POSITION('toy' IN prod_desc) = 0
5.1 LOCATE函数(缺点:不走索引)
参考链接
MySQL中的LOCATE()函数用于查找字符串中子字符串的位置。它将返回字符串中子字符串首次出现的位置。如果字符串中不存在子字符串,则它将返回0。在字符串中搜索子字符串的位置时,它不会执行区分大小写的搜索。
语法:
1)locate(substr,str)
2)locate(substr,str,pos)
1.解释:返回目标字符串substr在字符串集str中第一次出现的位置
2.解释:返回目标字符串substr在字符串集str的截取范围为从第pos位到字符串末尾是否出现,以及在整个str中第一次出现的位置;其实是两个步骤,1.先判断在截取字符串中是否出现,若出现则返回在整个字符串第一次出现的位置,若未出现,则返回0
5.2 INSTR函数(缺点:不走索引)
https://www.codenong.com/cs107014294/
5.3 POSITION函数(缺点:不走索引)
https://www.codenong.com/cs107014294/
6.同时存在、同时出现:like及正则写法
6.1 like写法
SELECT
prod_name,
prod_desc
FROM
Products
WHERE
prod_desc like '%toy%'
and prod_desc like '%carrots%'
6.2 正则写法
SELECT
prod_name,
prod_desc
FROM
Products
WHERE
prod_desc REGEXP '.*toy.*'
and prod_desc REGEXP '.*carrots.*'
或者是
select prod_name,prod_desc
from Products
where prod_desc regexp "(.*toy.*carrots.*)|(.*carrots.*toy.*)"
7.按顺序同时出现
题目链接
【问题】编写 SQL 语句,从 Products 表中检索产品名称(prod_name)和描述(prod_desc),仅返回在描述中以先后顺序同时出现 toy 和 carrots 的产品。提示:只需要用带有三个 % 符号的 LIKE 即可。
7.1 like写法按顺序用%
select prod_name, prod_desc from Products where prod_desc like '%toy%carrots%'
7.2 正则写法用.
select prod_name, prod_desc from Products where prod_desc regexp 'toy.carrots'
8.排序时数字序号的含义就是第几列
order by 2
就是按select的第二列升序排序
9.字符串截取、拼接函数:顾客登录名
9.1 substring函数
这道题有三个关键点:
- 1.截取函数:substring()
用法:SUBSTRING(str ,n ,m):返回字符串str从第n个字符截取到第m个字符(区别于数组索引); - 2.拼接函数:concat()
用法:select concat(A,B) - 3.大写函数UPPER()
示例:
select cust_id,cust_name,
upper(concat(substring(cust_contact,1,2),substring(cust_city,1,3)))
as user_login
from Customers
9.2 left函数
select cust_id,cust_name,upper(concat(left(cust_contact,2),left(cust_city,3))) as user_login from Customers;
MySQL LEFT() 函数从指定字符串的左侧返回指定数量的字符组成的字符串。如果想从字符串的右侧提取字符,请使用 RIGHT() 函数。
9.3 mid、substr都是substring的同义词
MID(str, pos, len) 等同于 SUBSTRING(str, pos, len) 和 SUBSTR(str, pos, len) 函数。
10.注意取某个时间范围内的边界:某一个月内的订单
查2020年1月的订单
题目链接
10.1大于小于 和 between and
-
左闭右开
select * from Orders where order_date >= '2020-01-01' and order_date < '2020-02-01' order by order_date;
-
而如果是用between and的话就是闭区间,这会导致右侧的范围不够灵活,所以尽可能用大于小于,比如下面这两种写法都是错的,
--1. 2020-01-31 零时刻之后的数据取不到 select * from Orders where order_date between '2020-01-01' and '2020-01-31' order by order_date; --2. 会错误的取到 2020-02-01零时刻的数据 select * from Orders where order_date between '2020-01-01' and '2020-02-01' order by order_date;
10.2 like写法
select order_num, order_date
from Orders
where order_date like "2020-01%"
order by order_date;
10.3 year 和 month函数
select order_num, order_date
from Orders
where Year(order_date)='2020' and Month(order_date)='01'
order by order_date;
10.4 date_fomat函数
select order_num, order_date
from Orders
where date_format(order_date, '%Y%m') = '202001'
order by order_date;
11.取范围内最大值
有max(xxx)
和 order by xxx desc limit 1
两种写法
12.子查询和联表都能筛选条件
-
两张表我一般喜欢用子查询,看着比较清楚,MySQL5.7之后对子查询的优化已经做的相当好了
select cust_id from Orders where order_num in (select order_num from OrderItems where item_price >= 10) select cust_id from Orders join OrderItems on OrderItems.order_num = Orders.order_num and OrderItems.item_price >= 10
-
三张及以上的表我喜欢用多个join
select cust_email from Customers join Orders on Orders.cust_id = Customers.cust_id join OrderItems on OrderItems.order_num = Orders.order_num and OrderItems.prod_id = 'BR01'
13.隐式内连接
显式内连接:
select cust_email from Customers
join Orders on Orders.cust_id = Customers.cust_id
join OrderItems on OrderItems.order_num = Orders.order_num and OrderItems.prod_id = 'BR01'
隐式内连接:
select c.cust_email
from OrderItems a, Orders b, Customers c
where a.order_num = b.order_num
and b.cust_id = c.cust_id
and a.prod_id = 'BR01'