B站链接
第二章 1-选择语句
如何定位到数据库在第一章
选择项
USE sql_store; #定位
SELECT * #选择所有的项
FROM customers
选择其中某一项
USE sql_store;
SELECT *
FROM customers
WHERE customer_id = 1
根据某一个标准排序
sql中--表示注释
USE sql_store;
SELECT *
FROM customers
-- WHERE customer_id = 1 #注释
ORDER BY first_name
可以选择用什么语句,但是顺序必须是 SELECT, FROM, WHERE, ORDER BY, LIMIT
我的理解:SELECT是选列,FROM是选表格,WHERE是选行,ORDER BY是顺序
SQL中换行没有作用,不用在意缩进什么的
2-选择子句
选择一些列,也可以添加列,对添加的列进行命名
SELECT
first_name,
last_name,
points,
(points + 10) * 100 AS discount_factor
FROM customers
对列命名的方式:
可以直接,得到如上图
(points + 10) * 100 AS discount_factor
如若想在命名中加空格,可以使用单引号
(points + 10) * 100 AS 'discount factor'
选择某一列并去重
用DISTINCT语句
SELECT DISTINCT state
FROM customers
3-where子句
判断运算符:<, >, <=, >=, =, !=或<>
SELECT *
FROM customers
WHERE birth_date > '1990-01-01' -- 年月日
-- WHERE state = 'VA'
-- WHERE state <> 'VA'
-- points > 3000
日期可以直接用'年-月-日'表示。so powerful!
4- AND, OR, NOT运算符
优先级:先AND,后OR
SELECT *
FROM customers
WHERE birth_date > '1990-01-01' OR
points > 1000 AND state = 'VA'
SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points) > 1000
SELECT *
FROM order_items
WHERE order_id = 6 AND quantity * unit_price > 30
5- IN运算符
表示集合,可以用IN, 对应也可以用NOT IN
SELECT *
FROM customers
WHERE state IN ('VA', 'FL', 'GA')
-- 等价于 WHERE state = 'VA' or state = 'GA' or state = 'FL'
-- WHERE state NOT IN ('VA', 'FL', 'GA')
数字可以不用单引号
SELECT *
FROM products
WHERE quantity_in_stock IN (49,38,72)
6- BETWEEN运算符
表示区间;对于数值包括左右端点,对于日期有左无右。
SELECT *
FROM customers
WHERE points BETWEEN 1000 AND 3000
-- WHERE birth_date BETWEEN '1990-01-01' AND '2000-01-01'
7- LIKE运算符
%:表示任意长度的字符
_:表示一个字符(长度为1);严格要求了字符数
SELECT *
FROM customers
WHERE last_name LIKE 'b%' -- 首字母为b的
-- WHERE last_name LIKE '%b%' 含有b的
SELECT *
FROM customers
WHERE last_name LIKE 'b____y' --首字母b,尾字母y,且中间有4个其余字母的
-- WHERE last_name LIKE '_y' 尾字母y,且之前有一个字母的(一共严格要求2个字母)
和逻辑符号的结合
SELECT *
FROM customers
WHERE address LIKE '%TRAIL%' OR
address LIKE 'AVENUE'
-- WHERE phone NOT LIKE '%9%'
8- REGEXP运算符
正则表达式,高级搜索
'a': 含有a; '^a':以a开头;'a$':以a结尾;'a|b|c':含有a或者b或者c;[ab]c:ab中的任意一个在c之前,即包含ac或bc的;[a-g]:从a到g的所有字母的简写
注意:不能在‘’里面随便加空格!
SELECT *
FROM customers
WHERE last_name REGEXP '^field|mac|rose'
-- WHERE last_name REGEXP '[gim]e'
-- WHERE last_name REGEXP '[a-h]e'
9- IS NULL运算符
根据空值来选
IS NULL;IS NOT NULL
SELECT *
FROM customers
WHERE phone IS NULL
-- WHERE phone IS NOT NULL
10- ORDER BY子句
ORDER BY:根据某一列排序,默认升序;在前面的列为优先排序准则列
DESC:表示降序
SELECT *
FROM customers
ORDER BY state, first_name DESC
还可以在“后台排序”
SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY points, first_name DESC
可以用数字表示哪一列,但是不推荐,最好用列名来指定
SELECT first_name, last_name, 10 AS points
FROM customers
ORDER BY 1, 2 --根绝第一列(first_name)和第二列(last_name)排序
-- ORDER BY quantity * unit_price DESC 也可以是运算后
11- LIMIT子句
选定那些范围内的行
LIMIT 3:选定前3行
LIMIT 6,3:跳过前6行后选前3行,即7-9行
SELECT *
FROM customers
WHERE first_name REGEXP 'e'
ORDER BY points DESC
LIMIT 2, 3