0%

MySQL笔记(1)--基础语句

发表于 更新于 分类于 Valine：

SELECT 子句

从一张表（customers）中选取某些列

1
2
3
4
5
6
SELECT
    first_name, 
    last_name, 
    points, 
    points * 10 + 100 AS "discount factor"   -- 可以进行数学运算,AS来rename
FROM customers
1
2
SELECT DISTINCT state -- DISTINCT 只保留重复项中的一项
FROM customers

WHERE 子句

过滤作用，根据条件选择records

(各个WHERE子句只是提供不同的示例，运行时选择一个WHERE子句，其余注释掉，下文同)

1
2
3
4
5
SELECT *
FROM customers
WHERE state = 'va' -- va大小写一样的
WHERE state <> 'va' -- not in VA
WHERE birth_date > '1990-01-01' -- born after 1990/01/01

运算符有：

1
2
3
4
5
6
>
>=
<
<=
=
(not equal)  !=  or  <>

AND, OR, NOT 运算符

1
2
3
SELECT *
FROM customers
WHERE NOT (birth_date > '1990-01-01' OR points > 1000)

AND优先级高于OR

IN 运算符

1
2
3
4
5
6
7
8
9
10
SELECT *
FROM customers
-- WHERE state = 'VA' OR state = 'GA' OR state = 'FL'
-- 用IN改写：
WHERE state IN ('VA', 'GA', 'FL')
-- WHERE state NOT IN ('VA', 'GA', 'FL') -- 返回不在这些州的 records


-- 这样是错的：WHERE state = 'VA' OR 'GA' OR 'FL' 
-- 因为我们不能用OR连接布尔表达式state = 'VA'和字符串'GA'

BETWEEN 运算符

1
2
3
4
5
SELECT *
FROM customers
-- WHERE points >= 1000 AND points <=3000
-- 改写为：
WHERE points BETWEEN 1000 AND 3000
1
2
3
4
-- 也可用于非数值
SELECT *
FROM customers
WHERE birth_date BETWEEN "1990-01-01" AND "2000-01-01"

LIKE 运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 字符串模式匹配
SELECT *
FROM customers
WHERE last_name LIKE 'b%' 
-- 得到last name以'B'或'b'开头的records

WHERE last_name LIKE 'brush%' 
-- 得到last name以“brush”开头的records

WHERE last_name LIKE '%b%' 
-- 表示'b'前后可以有任意字符数('b'在开头、中间、结尾出现皆可)

WHERE last_name LIKE '%y' 
-- 得到last name以'y'结尾的records

WHERE last_name LIKE '_____y' 
-- 得到last name以'y'结尾且只有6个字符('_'填补表示任意字符)的records

WHERE last_name LIKE 'b____y' 
-- 运行后得到last name以'b'开头以'y'结尾且只有6个字符的record(本例中为'Boagey')
1
2
3
/***SUMMARY**/ 
% any number of characters
_ single character

REGEXP 运算符

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- regular expression (REGEXP) 正则表达式
SELECT *
FROM customers

WHERE last_name LIKE '%field%'
-- WHERE last_name REGEXP 'field'

WHERE last_name REGEXP '^field' 
-- last name must starts with "field"

WHERE last_name REGEXP 'field$' 
-- last name must ends with "field"

/* use '|' for multiple search pattern */ 
WHERE last_name REGEXP 'field|mac|rose' 
-- last name must contains "field" or "mac" or "rose"

WHERE last_name REGEXP 'field$|mac|rose' 
-- last name must ends with "field" or contains "mac"/"rose"

WHERE last_name REGEXP '[gim]e' 
-- last name must contains "ge"/"ie"/"me"

WHERE last_name REGEXP 'e[gim]' 
-- last name must contains "eg"/"ei"/"em"

WHERE last_name REGEXP '[a-h]e'
-- the same as WHERE last_name REGEXP '[abcdefgh]e'
1
2
3
4
5
^ 	beginning
$ 	end
| 	logical or
[abcd]
[a-f]

IS NULL 运算符

1
2
3
4
SELECT *
FROM customers
WHERE phone IS NULL  -- 返回没有phone(该字段为NULL)的records
WHERE phone IS NOT NULL -- phone不为空

ORDER BY 子句

默认是按照主键（primary key）排序，使用ORDER BY 子句可以指定排序的键

1
2
3
4
SELECT *
FROM customers
ORDER BY first_name        -- 根据first_name字段值升序排序
ORDER BY first_name DESC   -- 根据first_name字段值降序排序
1
2
3
4
SELECT *
FROM customers
ORDER BY state DESC, first_name 
--先根据state降序排列，state相同的根据first_name升序排列
1
2
3
4
-- birth_date不在选出来的列里，still work
SELECT first_name, last_name
FROM customers
ORDER BY birth_date

LIMIT 子句

1
2
3
4
5
SELECT *
FROM customers
LIMIT 3 		-- 返回前3个records
LIMIT 300 		-- 300 > #record, 返回全部records
LIMIT 6, 3  	-- 跳过前6个records，返回第7，8，9个records