1.SQL-查库语言
敢查库,能统计
table(类)/columns(属性)/rows id
一行一个对象
3..三个工具:
SQLBolt
Excel
金老师手册
例:
select id,title,year from movies limit 3;
select * from movies limit3;
假设 movies表 100w个数据
select col,col,col
from table
where 条件
task1:
电影名字清单
select name from movies
select * FROM movies WHERE 1;
1表示无条件
task2 电影名字+年份
select title,year
FROM movies
where 1;
task 3 1995-2000年的电影
select ID,Title,year
FROM movies
WHERE year BETWEEN 1995 AND 2000;
select ID,Title,year
FROM movies
WHERE year >= 1995 AND year<=2000;
select ID,Title,year
FROM movies
WHERE year IN (1995,1996,1997,1998,1999,2000);
task4 要toy story系列电影
like 模糊匹配
SELECT id,title
FROM movies
WHERE tile LIKE "%toy story%";
task5:要某一个导演的电影,并排序
SELECT id,title
FROM movies
WHERE Director ="hulaoshi"
ORDERED BY year DESC limit 3;
ASC升序,DESC降序
task6:按照每个导演的电影的数量统计排序
SELECT Director COUNT(Director)AS count
FROM movies
WHERE 1
GROUP BY Director(分组)
ORDER BY Count DESC,Director ASC LIMIT3;(考虑相等的情况下)
注:Count列是新建的
按导演分组:根据导演列的导演名的重复项变无,分组后计数
搞定上面后再排序
COUNT是SELECT内的函数
需要先分组再COUNT么?应该是的,否则无意义了
SELECT City,Population
FROM Nort_american_cities
WHRER Country="Canda"
SELECT *
FROM Nort_american_cities
WHRER Country="America"
ORDER by Latitude DESC;
(默认升序排列)
?
SLECT city
FROM Nort_american_cities
WHRER Longtitude<
ORDER by Longtitude DESC;
?
连表:
SLECT *
FROM movies LEFT JOIN BOXO ON movies.id=BOXO.movie_id
WHERE 1;