msq 查询语句

查询列

//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;