【中字】SQL进阶教程 | 史上最易懂SQL教程!10小时零基础成长SQL大师!!_哔哩哔哩_bilibili
MySQL Workbench 8.0 设置按Tab键自动填充大写关键字
Edit→Preferences→SQL Editor→Query Editor→勾选use UPPERCASE keywords on completion
课程数据库获取:
- 打开课程官网:https://codewithmosh.com/p/complete-sql-mastery;
- 下拉下拉下拉,至Course Curriculum,找到Getting Started→ 5- Creating the Databases,点击preview;
- 试听课程下方有Download- SQL Course Materials.zip,下载就可以了!
P10如何在筛选数据时结合多条搜索条件
逻辑运算符的优先级NOT>AND>OR,可以使用括号改变顺序,会让代码更易于理解
SELECT *
FROM Customers
WHERE birth_date > '1990-01-01' OR points > 1000 AND state = 'VA'
这个地方发现state不全是VA,是因为OR连接的前后两块满足任意一个即可,所以要么满足出生在1990-01-01之后,要么满足points>1000并且state是VA
练习
SELECT *
FROM order_items
WHERE order_id = 6 AND quantity * unit_price > 30
P11使用SQL进行IN运算符的编写
SELECT *
FROM Customers
WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
简便写法
SELECT *
FROM Customers
WHERE state IN ('VA', 'FL', 'GA')
SELECT *
FROM Customers
WHERE state NOT IN ('VA', 'FL', 'GA')
练习
SELECT *
FROM products
WHERE quantity_in_stock IN (49, 38, 72)
P12在SQL中使用BETWEEN运算符
获取积分介于1000-3000之间的顾客
SELECT *
FROM customers
WHERE points >= 1000 AND points <= 3000
每当你要将一个属性同一个范围值相比较,可以使用BETWEEN运算符,代码也变得短而精
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
这个范围值是包含临界值的,也就是积分会大于等于1000
练习
SELECT *
FROM customers
WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'
P13使用LIKE运算符检索遵循特定字符串模式的行
例如,只想要获取姓氏以B开头的顾客
SELECT *
FROM customers
WHERE last_name LIKE 'b%'
%可以表示任意字符数,不一定在末尾,在任何地方都可以
SELECT *
FROM customers
WHERE last_name LIKE '%b%'
下划线代表一个字符,所以下面这种写法表示姓氏长度正好为2个字符串,并且第2个字符是y
SELECT *
FROM customers
WHERE last_name LIKE '_y'
练习
SELECT *
FROM customers
WHERE address LIKE '%trail%' OR
address LIKE '%avenue%'
SELECT *
FROM customers
WHERE phone NOT LIKE '%9'
P14运算符REGEXP 搜索字符
正则表达式的缩写regular expression,正则表达式在搜索字符串时极其强大,允许我们搜寻更复杂的模式
SELECT *
FROM customers
-- WHERE last_name LIKE '%field%'
WHERE last_name REGEXP 'field'
^符号表示字符串开头,例如 姓氏必须以field开头
WHERE last_name REGEXP '^field'
$符合表示字符串结尾,例如 姓氏必须以field结尾
WHERE last_name REGEXP 'field$'
| 表示多个搜寻模式,比如我们想查询一些顾客,他们的姓氏包含field或者mac
WHERE last_name REGEXP 'field|mac'
我们想寻找顾客姓氏里面有field mac或者rose
WHERE last_name REGEXP 'field|mac|rose'
姓氏需要以field开头,或者包含mac或者rose
WHERE last_name REGEXP '^field|mac|rose'
字母e前要有g或i,这时需要用到 []
WHERE last_name REGEXP '[gi]e'
也可以提供一个字符范围,在e前面有从a到h的任意字母
WHERE last_name REGEXP '[a-h]e'
用法总结
-- ^ beginning
-- $ end
-- | logical or
-- [abcd]
-- [a-f]
练习
SELECT *
FROM customers
-- WHERE first_name REGEXP 'elka|ambur'
-- WHERE last_name REGEXP 'EY$|ON$'
-- WHERE last_name REGEXP '^MY|SE'
-- WHERE last_name REGEXP 'b[ru]'
WHERE last_name REGEXP 'br|bu'