SQL server语法(2)
小案例
找攻击力
select * from sanguo where attack >= 70 and attack <= 90;
select * from sanguo where attack between 77 and 86;
select * from 表名 where 列名 between 最小值 and 最大值;
找国家
select * from sanguo where country = '吴国' or country = '魏国';
select * from sanguo where country in ('吴国','魏国')
按照性别分组
select gender as '性别',count(1) as '人数' ,max(attack) as '最强战力' from sanguo group by gander
分组
错误示范:
select * from sanguo group by country
正确示范:
select country from sanguo group by country
select 列名 from 表名 group by 列名
分组后只有country ,组的名称只是country,没有name id 等等
select country,count(1) from sanguo group by cointry;
最大攻击力
select country,count(1),max(attack) from sanguo group by country
max 最大
min 最小
sum 总和
as 起别名
avg 平均
select country as 国家 ,count(1) as 人数,max(attack) as 最大攻击力 ,min(attack) as 最小攻击力 ,sum(attack) as 小组总战力,avg(attack) as 平均战力 from sanguo group by country
把所有的数据分为一组
求这个组有多少个成员
count(1)
count(*)
count(列)
这三个的区别
count(1) 和 count(*) 效果是一样的
count(列) 有区别 如果有 数据有 null 的话显示的是0
结论:count(列)是不会算上null 的
count(1) 的性能最高
count(1) 计算机不会去读他
count(*) 计算机要去读他
所以count(1) 性能高
分组筛选
having
having 条件
x select gender as '性别',count(1) as '人数' ,max(attack) as '最强战力' from sanguo group by gander having count(1)<6
having 一定是要写在后面的,放在前面的话,因为都还没有开始分组所以不能筛选
having 不可以 代替 where
where 不可以 代替 having
不可以互相替代
先过滤在分组
在这些数据里在分组
连表查询
这样的话查不出来,因为 没有关联
连接
select * from student inner join teacher on student.tid = teacher.id
select * from 查询的表名 inner join 连接的表名 on 查询的表名.数据 = 连接的表名.数据
只显示学生的 id , 学生姓名 老师姓名
错误释放:
正确释放:
指定列显示
select student.id, student.name , teacher.name from student inner join teacher on student.tid = teacher.id
select 表名.要显示的列名 from 查询的表名 inner join 连接的表名 on 查询的表名.数据 = 连接的表名.数据
三表的连表
再连接班级
select student.id, student.name , teacher.name from student inner join teacher on student.tid = teacher.id inner join Class1 on student.cid = class1.id
select * from student inner join teacher on student.tid = teacher.id inner join Class on student.cid = Class1.id ;
要分清出需要的列
需要那个列 就把 * 改成 显示的列.显示的列名
select student.id as 编号,student.name as 姓名 ,teacher.id as 老师编号, teacher.name as 老师姓名 , Class1.name as 班级 from student inner join teacher on student.tid = teacher.id inner join Class on student.cid = Class1.id ;
左连接vs右连接
左连接
把 inner 改成 left 就是左连接
select * from student left join teacher on student.tid = teacher.id
右连接
右连接就是把left 改 成 right