概述
Paste_Image.png
1.简单查询
- 列选择
SELECT * FROM films#选择所有列
SELECT country,language FROM films#选择两列
- 选择不重复
SELECT DISTINCT country from films#查询国家（不重复）
- 计数
COUNT不统计空值
SELECT count(*) from films
SELECT count(distict country) from films
2.条件查询
- 简单条件
SELECT * FROM films where gross > 100000
#条件符号包括 >, >=, = ,<= ,<
- 复合语句
SELECT * FROM films WHERE gross > 10000 AND language = 'English'
SELECT * FROM films WHERE gross > 10000 AND (language = 'English' or language = 'French')
#注意上面的括号
- 区间判断
BETWEEN AND语句包括两端
SELECT * FROM films WHERE gross BETWEEN 1000 AND 10000
#BETWEEN与其它条件配合不需要区别AND
- 集合判断
SELECT * FROM films WHERE language in ('French','English')
- NULL
NULL空值这里代表是没有值
SELECT * FROM films WHERE language is NULL
SELECT * FROM films WHERE language is Not Null
- 模糊匹配
SELECT * FROM films WHERE name Like 'A%'
#以A开头的name
SELECT * FROM films WHERE name Like '_A%'
#首字符为任意的name
3.聚合
- 聚合函数
常用函数avg,min,max,sum（这些都只能用在字段上）
SELECT min(duration) FROM films
- AS语句
在SQL语句中进行算术运算！
select title,(gross-budget) as net_profit from films
在SQL的算术运算中要注意浮点数问题，比如6/10和6/10.0可能（MYSQL上没有差别）是不一样的。
4.排序分组与联合
- 单列排序
SELECT * FROM films order by name
