三种注释
## 注释1
-- 注释2
/* 注释3 */
语法要点
- SQL 语句不区分大小写,但是数据库表名、列名和值是否区分,依赖于具体的 DBMS 以及配置。
- 多条 SQL 语句必须以分号(;)分隔。
- 处理 SQL 语句时,所有空格都被忽略。SQL 语句可以写成一行,也可以分写为多行。
SELECT
按顺序返回指定列,*返回所有的列
可在select语句中进行加、减、乘、除和余数计算
SELECT first_name, last_name, points, points*10+10
FROM customers
select后面列太多可以拆分
SELECT
first_name,
last_name,
points,
points*10+10
FROM customers
as可以给列和结果一个描述性名称,若名称中间有空格,需要在两侧加上单引号或双引号。
SELECT
first_name,
last_name,
points,
points*10+10 AS discount_factor
FROM customers
distinct可以删除重复项
SELECT state
FROM customers
SELECT DISTINCT state
FROM customers
WHERE
用于条件筛选,比较运算符 >, >=, <, <=, =, !=(或 <> )
单个条件
SELECT *
FROM customers
where points > 3000
SELECT *
FROM customers
where state = 'VA'
SELECT *
FROM customers
where birth_date > '1990-01-01'
多个条件
and, or,not
and优先级高于or,也可以使用()来限定优先级
SELECT *
FROM customers
where birth_date >= '1990-01-01' OR points >1000 AND state ='VA'
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 )
IN / NOT IN
SELECT *
FROM customers
where state = 'VA' OR state = 'GA' OR state = 'FL'
## 错误写法 where state = 'VA' OR 'GA' OR 'FL'
布尔运算是逻辑运算,可以用in语句替代
SELECT *
FROM customers
where state IN ('VA', 'GA', 'FL')
SELECT *
FROM customers
where state NOT IN ('VA', 'GA', 'FL')
BETWEEN
SELECT *
FROM customers
where points BETWEEN 1000 AND 3000
LIKE
%表示任意字符数(包含0字符),_表示一个字符
SELECT *
FROM customers
WHERE address LIKE '%trail%' OR address LIKE '%avenue%'
SELECT *
FROM customers
where last_name LIKE 'b%'
SELECT *
FROM customers
where last_name LIKE '_____y'
REGEXP
(regular expression)
^表示开头含有,$表示结尾含有,没有加符号表示全部字段中含有
|表示逻辑或
SELECT *
FROM customers
WHERE last_name REGEXP 'field'
-- 等同于 WHERE last_name LIKE '%field%'
SELECT *
FROM customers
WHERE last_name REGEXP 'field$|mac|rose'
-- 姓氏中以field结尾或者含有mac或者含有rose的人
[ ]表示字符范围
SELECT *
FROM customers
WHERE last_name REGEXP '[gim]e'
-- 姓氏中含有ge, ie或me的
- 表示一个范围
SELECT *
FROM customers
WHERE last_name REGEXP '[a-h]e'
-- 姓氏中含有ae, be,ce,de,ee,fe,ge或he的
IS NULL
搜索缺失了属性的记录
SELECT *
FROM customers
WHERE phone IS NULL
ORDER BY
默认按主键排序,从表中设置可以查看更改。
在MySQL中,排序并不一定要以select的内容进行排序,可以用表中的任何一列排序,哪怕没有select选中。
SELECT *
FROM customers
ORDER BY first_name
desc为降序
SELECT *
FROM customers
ORDER BY first_name DESC
多级排序,可以直接列名称,也可以为算数表达式
SELECT *
FROM customers
ORDER BY state DESC, first_name
-- 先按state降序排序,state一样则按first_name排序
还可以用数字表示列(注意:谨慎使用,因为如果增添或删减了列可能会对排序造成影响)
SELECT *
FROM customers
ORDER BY 1, 2
LIMIT
限制查询返回的记录数
SELECT *
FROM customers
LIMIT 3
若有两个数,第一个数表示偏移量
SELECT *
FROM customers
LIMIT 6, 3
-- 6为offset,跳过前6条记录然后获取3个记录
语句顺序
SELECT *
FROM customers
WHERE state='VA'
ORDER BY points DESC
LIMIT 15