SQL server语法（2）

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

扩展

删除一个表结构