分组统计
-
group by--分组
语法结构为
SELECT vend_id,COUNT(*) FROM products(表名) GROUP BY vend_id(执行分组的列名);
SSELECT vend_id,COUNT(* ) FROM products(表名) GROUP BY vend_id(执行分组的列名) HAVING COUNT(*) > 2(分组过后数据筛选条件);
其中用到了having来进行数据筛选,having和where的区别在于having用于group by的过滤,而where用于分组前的过滤
order by--排序
用于对数据的排序分为升序(默认为升序)和降序(加desc),语法结构为
SSELECT vend_id,COUNT(* ) FROM products(表名)
GROUP BY vend_id(执行分组的列名)
HAVING COUNT(*) > 2(分组过后数据筛选条件)
ORDER BY COUNT(*)(执行排序的列名)
DESC(加desc为降序) -
limit--分页
SSELECT vend_id,COUNT(* ) FROM products(表名)
GROUP BY vend_id(执行分组的列名)
HAVING COUNT(*) > 2(分组过后数据筛选条件)
ORDER BY COUNT(*)(执行排序的列名)
DESC(加desc为降序)
LIMIT 0 ,3(0表示从第0位置向下一个位置显示,3表示显示三个记录) -
查询语句顺序
1.select
2.from
3.where
4.group by
5.having
6.order by
7.limit
子查询
-
嵌套在查询中的查询
语法结构为
SELECT cust_name FROM customers WHERE cust_id
IN(SELECT order_num FROM orderitems WHERE prod_id = "TNT2")
(in后边的查询为第一个select查询的条件由单纯的条件变为一个语句)
连接查询
Paste_Image.png-
内连接和等值连接
等值查询
> SELECT ts.id AS 'stuid',stu_name,tc.id AS 'class_id',class_name
> FROM t_student AS ts,t_class AS tc
> WHERE ts.class_id = tc.id
(ts为表一名字,tc为表二名字)
内连接 inner join
> SELECT ts.id AS ' stuid',stu_name,tc.id AS 'class_id',class_name
> FROM t_student AS ts
> INNER JOIN t_class AS tc
> ON ts.class_id = tc.id
(ts为表一名字,tc为表二名字)
- 左外链接
> SELECT ts.id AS ' stuid',stu_name,tc.id AS 'class_id',class_name
> FROM t_student AS ts
> LEFT JOIN t_class AS tc
> ON ts.class_id = tc.id
(ts为表一名字,tc为表二名字)
左连接会显示左表的所有数据
- 右外连接
> SELECT ts.id AS ' stuid',stu_name,tc.id AS 'class_id',class_name
> FROM t_student AS ts
> RIGHT JOIN t_class AS tc
> ON ts.class_id = tc.id
(ts为表一名字,tc为表二名字)
右连接会显示右表所有信息
-
组合查询
1.union必须由两条或两条以上的select语句组成,语句之间使用union分割
2.union的每个查询必须包含相同的列,表达式或聚合函数
3.列的数据类型必须兼容:类型不必完全相同,但是必须是相互可以转换的
4.union查询会自动去除重复的行,如果不需要此特性,可以使用union all
5.对union结果进行排序,order by语句必须在最后一条select语句之后
SELECT vend_id FROM vendors
UNION ALL
SELECT vend_id FROM products;
网友评论