查询列
//movies 表名 *代表所有信息
SELECT title,year FROM movies; //只查询title和year信息
SELECT * FROM movies where year>=2000 and year<=2010; //年份在2000-2010年之间
//模糊查询 %通配符
SELECT * FROM movies where title like "WALL-%"; //title 是WALL-开头的
SELECT * FROM movies where title like "%John Lasseter%"; //所有John Lasseter导演的电影
//DISTINCT去重 通过Limit选取部分结果 ORDER BY year desc 降序 asc 升序（默认）
SELECT DISTINCT Director FROM movies; //只显示导演（去重）
SELECT * FROM movies ORDER BY year desc limit 4; //列出按上映年份最新上线的4部电影
//limit 若是两个参数就是分页 第一个值为页数第二个为条数
SELECT * FROM movies ORDER BY title limit 2,5;
//OFFSET 指定从哪里开始剪，用 LIMIT 指定剪下多少长度
SELECT * FROM movies ORDER BY title limit 5 OFFSET 2;
//INNER JOIN another_table （要连接的表）ON mytable.id = another_table.id
SELECT * FROM movies INNER JOIN Boxoffice ON Movies.id = Boxoffice.Movie_id where Boxoffice.International_sales>Boxoffice.Domestic_sales; //找到所有国际销售额比国内销售大的电影
SELECT Director,International_sales FROM movies INNER JOIN Boxoffice ON Movies.id = Boxoffice.Movie_id order by Boxoffice.International_sales desc limit 1;每部电影按国际销售额比较,排名最靠前的导演是谁,国际销量多少(查询指定字短)
//INNER JOIN 只会保留两个表都存在的数据
//A 连接 B, LEFT JOIN保留A的所有行，不管有没有能匹配上B 反过来 RIGHT JOIN则保留所有B里的行。
//FULL JOIN 不管有没有匹配上，同时保留A和B里的所有行
SELECT distinct Building_name FROM employees left JOIN Buildings on employees.Building=Buildings.Building_name where Building; //找到所有有雇员的办公室(buildings)名字
//表名后面跟一个字短可以代替表名 对比不同的字短可以不加表名，同一字短不能比对
SELECT distinct Role,Building_name FROM Buildings b
left JOIN employees e on Building=Building_name;
//where Building is not null Building字短为null的不返回
//group by Building 分组（指定字短去重）
//sum(Capacity) Capacity字短数据累加
SELECT Building,sum(Capacity) FROM employees e
left JOIN Buildings b on Building=Building_name
where Building is not null
group by Building;
