查询SQL

概述

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