SQL升级操作-各种

目录

别名

嵌套查询

多表连接（join）

SQL函数

---

别名

SELECT column_name AS alias_name FROM table_name; 列名别名

SELECT column_name FROM table_name AS alias_name; 表名别名

嵌套查询

• 嵌套查询的意思是，一个查询语句(select-from-where)查询语句块可以嵌套在另外一个查询块的where子句中，称为嵌套查询。其中外层查询也称为父查询，主查询。内层查询也称子查询，从查询。

查询存在于A表而不存在于B表的id。嵌套查询
select a.id from tbl_score a where a.id not in(select b.id from tbl_class b);
 这里not in 可以替换成其他查询的条件

查询成绩都大于80分的学生,先查询小于80的，并用distinct去除重复，然后选择不在这列中的，即为都大于80
select distinct name from tbl_score a where name not in(select distinct name from tbl_score where score <=80);

列出各个部门中工资高于本部门的平均工资的员工数和部门号，并按部门号排序
select count(*), a.deptid from emp a,(select deptd,avg(salary) from emp group by deptid ) b where a.deptid=b.deptid and a.salary>b.avgsal group by deptid order by a.deptid;

列出各个班级中分数高于本班级的平均工资的学生数和班级，并按班级排序
select count(*), a.class from tbl_score a, (select class, avg(score1) avgscore from tbl_score group by class) b where a.class = b.class and a.score1 > b.avgscore group by a.class order by a.class

多表连接（join）

• join: 一般有约束关系

select * from tbl_1 as b1 inner join tbl_2 as b2 on b1.id = b2.id; 内连接，指定b1与b2的相关联（一般都有内外键约束关系），只返回两表都满足条件的记录。

select * from tbl_1 as b1 left join tbl_2 as b2 on b1.id = b2.id; 左连接，约束关系相同，但是即使右表中没有匹配，也从左表返回所有满足条件的行。

select * from tbl_1 as b1 left join tbl_2 as b2 on b1.id = b2.id; 右连接，约束关系相同，但是即使左表中没有匹配，也从右表返回所有满足条件的行。

SQL函数

• avg(column):求列均值， 可以加条件

select avg(score1) as avg_score from tbl_score

• count(column)：计数，可以加条件

-- 查询所有记录的条数
select count(*) from tbl_score;

-- 查询tbl_score 表中 class列中不为空的记录的条数
select count(class) from tbl_score;

-- 查询tbl_score表中 class列中不重复的记录条数
select count(distinct class) from tbl_score;

-- 查询tbl_score表中 当class='2班'时，score1列中不重复的记录条数。（可以加条件）
select count(score1) from tbl_score where class='2班'

• first|last(column)：返回指定的列中第一个记录的值，mysql 为limit 1 通常和order by column asc|desc 联合使用。

mysql 语法，选取成绩最高的那条记录。
select * from tbl_score order by score1 desc limit 1;

• max(column):返回指定列的最大值。min(column):返回指定列的最小值

select max(score1) from tbl_score;

• sum(column):返回数值列的数值总和。

select sum(score1) from tbl_score

• group by :用于结合聚合函数，根据一个或多个列对结果进行分组（一组=一条记录，将这组内的某列结果聚合到一起）。

根据班级算总分并分组（相同班级为一组，计算总分），取总分数最高的哪一个班级记录（对总分desc排序）
select class, sum(score1) as sum_score from tbl_score group by class order by sum_score desc limit 1

group by 语句的多表连接，思路是先多表连接，然后在进行group by 分组
SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

• having: 解决where 无法对聚合函数有判断作用（语法错误）

select class, sum(score1) as sum_score from tbl_score group by class having sum_score > '98'

筛选出xh学号记录大于2的学号 去除不满足记录数的学号
select xh, count(class) as num from tbl_score group by xh having num>2