1. select 查询
- select 查询某些属性列(specific columns)的语法
SELECT column(列的名称),another_column,… FROM mytable(表的名称)
- select查询所有列
SELECT * FROM mytable(表名)
要添加没有的列 “内容” as 自定义列名称
SELECT id,title,'美国'as country FROM movies;
2.条件查询(constraints)
由于现实中,单单一列的数据可能有上万条,之前的SQL语句很可能运行时会崩溃。
所以我们只需要下载我们需要的数据就好了,为了更精确的查询出特定的数据,我们用下面这个SQL语法:SELECT 查询的WHERE 子句,其中,WHERE后加一个限定条件condition
条件查询语法 – WHERE
用来筛选数字类型的属性
SELECT column,another_column,… FROM mytable WHERE condition AND/OR another_condition AND/OR
注:condition描述属性列的
关键字 AND 和 OR 可以组装多个条件,
解释:
- =,!=,<,<=,>,>=一般在比较大小时使用
- between 一般用来确定的范围
- in 一般用来枚举
条件查询语法 – LIKE(模糊查询) 和 %(通配符)
SELECT * FROM movies where (title like 'Cars%' or title like 'Toy Story%') and year >=2000 and director = 'John Lasseter'
3.查询结果Filtering过滤和sorting排序
DISTINCT 关键字用来指定某个或某些属性列唯一返回(删除重复的行)。
选取出唯一的结果
SELECT DISTINCTcolumn,another_column,…
FROM mytable
WHERE condition(s);
GOURP BY 返回唯一的行,不过会对具有相同的属性值的行统计求和。
ORDER BY col_name 让结果按一个或多个属性列做排序
结果排序(ordered results)
SELECT column,another_column,…
FROM mytable
WHERE condition(s)
ORDER BY column ASC(升序)/DESC(降序);
LIMIT 和 OFFSET 子句通常和ORDER BY 语句一起使用
LIMIT 指定只返回多少行结果
OFFSET 指定从哪一行开始返回
limited查询
SELECT column,another_column,…
FROM mytable
WHERE condition(s)
ORDER BY column ASC/DESC
LIMIT num_limit OFFSET num_offset;
一般LIMIT和OFFSET在SQL的其他部分都执行完之后,再执行
- 按导演名字母升序,如果导演名相同按年份降序,取前10部电影给我
select * from movies
order by director asc,year desc #前面是按照director升序排列,逗号,后表示如果前面有相同的,按照year降序排列
limit 10 offset 0
- 1
- 2
- 3
- 4
- 列出所有在Chicago西部的城市,从西到东排序(包括所有字段),类似的,如果要求的某一条件也是表中的某一值,可以再次使用select语句调出
SELECT * FROM north_american_cities
where longitude <(select longitude from North_american_cities where city like "Chicago")
#括号中的数据是题中的要求,用select语句从表中调取
- 1
- 2
4.用JOINs进行多表联合查询
- 数据库范式(normalization)是数据表设计的规范,在范式规范下,数据库里每个表存储的重复数据降到最小,同时在数据库范式下,表和表之间不在有很强的数据耦合性,可以独立的增长。
- 主键(primary key) 是唯一标识一条数据的,不会重复。
- 借助主键(其他唯一性的属性也可以),我们可以把两个表中具有相同的主键ID的数据连接起来。用INNER JOIN连接。
用INNER JOIN连接表的语法
SELECT column,another_table_column,…
FROM mytable (primary table)
INNER JOIN another_table(需要连接的表) ON mytable.id = another_table.id (两个表连成一条)
WHERE condition(s)
ORDER BY column,… ASC/DESC
LIMIT num_limit OFFSET num_offset;
- 通过ON条件描述的关联关系;INNER JOIN 先将两个表数据连接到一起
- 两个表中如果通过ID互相找不到的数据将会舍弃
- 此时,连接表后的数据可以看成两个表的合并,SQL的其他语句会在这个合并基础上继续执行。
SELECT * FROM movies inner join boxoffice on movies.id = boxoffice.movie_id
5.外连接(OUTER JOINs)
- INNER JOIN 只保留两个表都存在的数据(两张表的交集)
- 左连接 LEFT JOIN
- 有连接 RIGHT JOIN
- 全连接 FULL JOIN
- 以上连接方式都会保留不能匹配的行
用LEFT/RIGHT/FULL JOINs做多表查询
SELECT column,another_column,…
FROM mytables
INNER/LEFT/RIGHT/FULL JOIN another_table ON mytable.id=another_table.matching_id
WHERE condition(s)
ORDER BY column,… ASC/DESC
LIMIT num_limit OFFSET num_offset;
- 表A连接B:
- LEFT JOIN 保留A的所有行
- RIGHT JOIN 保留B的所有行
- FULL JOIN 不管是否匹配,同时保留A和B的所有行
- 注意:如果某一行在另一个表不存在,会用NULL来填充结果数据。所以再用这三个JOIN时,需要单独处理NULL。
自学SQL部分练习题分析
- 找到所有有雇员的办公室(buildings)名字
SELECT distinct building_name FROM employees left join buildings on employees.building = buildings.building_name
分析:这段语句,①先以employees表作为主表,使用left join将buildings表连接,同时以办公室名字作为主键连接。这里使用employees作为主表和left join关键字,可以没有雇员的办公室过滤掉;②因为要找的是办公室,经过上述的过滤,剩下的都是有雇员的办公室,但是有重复存在,所以使用distinct关键字,过滤掉重复。③结果中还有NULL存在,这里还没学到怎么处理NULL,但是有雇员的办公室名字已经全部打印出来了,暂时不影响结果。
- 找到所有办公室里的所有角色(包含没有雇员的),并做唯一输出(DISTINCT)
SELECT distinct role,building_name FROM buildings left join employees on buildings.building_name = employees.building
分析:题中要求找所有办公室的所有角色,因为这道题要求只能使用LEFT JOIN关键字,所以这里我选用buildings作为主表,防止部分办公室被过滤掉。
- 找到所有有雇员的办公室(buildings)和对应的容量
SELECT distinct building,capacity FROM employees left join buildings on employees.building=buildings.building_name where building not like "null"
分析:① 题目要求所有有雇员的,所以雇员必须全部被包含,没有雇员的办公室可以被过滤。② where语句处理了null
6.关于特殊关键字NULLs
- 在数据库中,NULL表达的是“无”的概念,或者说没有东西
- 如果某个字段没有填写到数据库,很可能出现NULL
- 常见的方式是为字段设置默认值,如:数字的默认值设置为0;字符串设置为“ ”
- 但在一些NULL表示它本来含义的场景,需要注意是否设置默认值还是保持NULL(如:当计算一些行的平均值的时候,如果是0会参与计算,导致平均值差错,如果是NULL则不会参与计算)
- IS NULL 某个字段等于NULL
- IS NOT NULL 某个字段是不等于NULL
在查询条件中处理NULL
SELECT column,another_column,…
FROM mytable
WHERE column IS/IS NOT NULL
AND/OR another_condition
AND/OR …;
7.在查询中使用表达式
包含表达式的例子
SELECT particle_speed/2.0 AS half_particle_speed(对结果做了一个除2)
FROM physics_data
WHERE ABS(particle-position)*10.0 > 500(条件要求这个属性绝对值乘以10大于500)
- 每一种数据库(MySQL,SQLserver)都有自己的一套函数,包含常用的数字,字符串,时间等处理过程,具体需要看相关文档
- 当我们用表示对col属性计算时,为了解决表达式如果很长则很难一下子读懂的问题,使用AS 关键字,来给表达式取一个别名。
AS使用别名
SELECT col_expression AS expr_description,…
FROM mytable
- AS不仅用在表达式别名上,普通的属性列甚至表都可以取一个别名。
属性列和表取别名的例子
SELECT column AS better_column_name,…
FROM a_long_widgets_table_name AS mywidgets
INNER JOIN widget_sales ON mywidgets.id =widget_sales.widget_id;
- 列出所有偶数年份的电影,需要电影ID,名字和年份
SELECT id,title,year FROM movies where year%2=0
分析:这个地方的陌生点就是怎么求余数,上网查了有的使用MOD(x,y),尝试之后,没反应,试了下ptyhon中的运算符%,成功了。以后实际操作中,还得看运行环境的有关函数的文档。
- John Lasseter导演的每部电影每分钟值多少钱,告诉我最高的3个电影名和价值就可以
SELECT title,(domestic_sales+international_sales)/length_minutes as price_minute FROM movies left join boxoffice on movies.id = boxoffice.movie_id
where director like "John Lasseter"
order by price_minute desc
limit 3
- 1
- 2
- 3
分析:① 先将两张表连接起来;② 筛选导演是JOHN LASSETER的电影;③ 按每分钟价值降序排列,(每分钟价值还没计算,在SELECT关键字后写出表达式,as price_minute);④ 选前三个 limit 3
- 电影名最长的3部电影和他们的总销量是多少
SELECT title,length(title)as length_title,(domestic_sales+international_sales) as total_sales FROM movies
left join boxoffice on movies.id = boxoffice.movie_id
order by length_title desc
limit 3
- 1
- 2
- 3
小结:SQL计算字符串长度的函数length().
8.在查询中进行统计
*用SQL对数据进行统计,SQL默认支持以组统计表达式,他们可以完成数据统计,如:计数,求平均数
对全部结果数据做统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description,…
FROM mytable
WHERE constraint_expression;
- 如果不指明如何分组,那么统计函数将对查询结果全部数据进行统计。每一个统计也可以向之前用AS来取一个别名,增加其可读性。
常见统计函数
Function | Description |
---|---|
COUNT(*), COUNT(column) | 计数,COUNT(*)统计数据行数,COUNT(column)统计column非NULL的行数 |
MIN(column) | 找column最小的一行 |
MAX(column) | 找column最大的一行 |
AVG(column) | 对column所有行取平均数 |
SUM(column) | 对column所有行求和 |
分组统计
- GROUP BY 数据分组语法可以按某个col_name 对数据进行分组,如GROUP BY year 指对数据按年份分组,相同年份的分到一个组里。
- 如果把统计函数和GROUP BY结合,统计结果就是对分组内的数据统计。
- GROUP BY 分组结果的数据条数,就是分组数量,如:GROUP BY year,全部数据里有几年,就返回几条数据,不管是否应用了统计函数。
用分组的方式统计
SELECT AGG_FUNC(column_or_expression) AS aggregate_description,…
FROM mytable
WHERE constraint_expression
GROUP BY column;
- 每栋办公室按人数排名,不要统计无办公室的雇员
SELECT building,count(building) FROM employees
where building is not null #过滤掉无办公室雇员的数据
group by building; #用办公室分组
- 1
- 2
- 就职1,3,5,7年的人分别占总人数的百分比率是多少(给出年份和比率"50%" 记为 50)
SELECT years_employed,count(*)*100/(select count(*) from employees) as rating FROM employees
where years_employed in (1,3,5,7)
group by years_employed
- 1
- 2
分析:注意分母(select count(*) from employees) 的写法。
- 在 GROUP BY分组语法中,一般是数据库先对数据做WHERE,然后对结果做分组。但是如果我们要对分组完的数据再筛选除几条怎么办?
- HAVING语法可以对分组之后的数据再做SELECT筛选
用HAVING进行筛选
SELECT group_by_column, AGG_FUNC(column_expression) AS aggregate_result_alias,…
FROM mytable
WHERE condition
GROUP BY column
HAVING group_condition
- 按角色分组算出每个角色按有办公室和没办公室的统计人数(列出角色,数量,有无办公室,注意一个角色如果部分有办公室,部分没有需分开统计)
SELECT role,count(role),building from employees
where building is not null
group by role
union
select role,count(role),building from employees
where building is null
group by role
order by building asc
- 1
- 2
- 3
- 4
- 5
- 6
- 7
待解决:这串代码已经显示出了题目的要求结果,可还是没有通过,待以后精进之后再解决。
回来解决:是否可以采用OR?
- 按角色和就职年份统计人数,年份按0-3,3-6,6-9这种阶梯分组,最后按角色+阶梯分组排序
- 同上
9. 查询执行顺序
完整的SELECT查询
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
JOIN another_table
ON mytable.column = another_table.column
WHERE constraint_expression
GROUP BY column
HAVING constraint_expression
ORDER BY column ASC/DESC
LIMIT count OFFSET COUNT;
- 按导演分组计算销售总额,求出平均销售额冠军(统计结果过滤掉只有单部电影的导演,列出导演名,总销量,电影数量,平均销量)
SELECT director,
sum(domestic_sales+international_sales) as total_sales,
count(title) as total_movies,
sum(domestic_sales+international_sales)/count(title)as ave_sales
from movies
left join boxoffice on movies.id = boxoffice.movie_id
group by director
having count(title)>1
order by ave_sales