2.数据检索|模糊查询|数据排序|数据分组| 常见问题0726

 

难点====分组查询group by...having

        在分组查询中，查询的列名

        必须出现在group by后或者在聚合函数中

        --查询平均工资大于两千块钱的部门

        select department_id,avg(wages)

        from employee

        where department_id  is not null

        group by department_id

        having avg(wages)>2000

 

1. 数据检索

l       Top Distinct

l       Top 获取前几条数据

l       获得年纪最小的5个学生

l       获得年纪最大的10%的学生

l       Distinct 去除重复数据

l       select distinct sName from student

l       select distinct sName,sAge from student

l       DISTINCT是对整个结果集进行数据重复处理的，而不是针对某一个列

--Top distinct

select top 5 * from student

order by sAge

--获取前10%的学生

select top 10 percent sName,sAge

from student

 

select distinct sName from student

2.聚合函数

l       SQL聚合函数：MAX（最大值）、MIN（最小值）、AVG （平均值）、SUM （和）、COUNT（数量）

l       平均成绩select avg(english) from score

l       男学生出生日期的最大值和最小值：SELECT select max(sBirthday),min(sBirthday) from student where sSex='男'

聚合函数

        --avg() 平均值

        --sum   求和

        --count 求行数

        --min   求最小值

        --max   求最大值

select max(english)from score

select * from score

 

select sum(english)/count(*)from score

select count(*)from student where sSex='男'

 

select sName,sAge,sSex from student

where sAge between 20 and 30 and sSex='男'

带条件查询

        select 列 from 表 where 条件

        between...and...

•           select sName,sAge from student where sClassId=1 or sClassId=2 or sClassId=3

•         select sName,sAge from student where sClassId in (1,2,3)

 

带条件的查询-模糊查询

查询所有姓张的同学

Select * from student where left(sName,1)=‘张‘   看上去很美,如果改成查询名字中带亮的学生怎么做？

换一种做法 like 

l       Select  * from student where sName like ‘张%’    会吧所有姓张的都查询到，现在我想查询姓张并且名字是一个字的学生？

l       Select  * from student where sName like ‘%亮%’

通配符 %多字符匹配的通配符，它匹配任意次数（零或多个）出现的任意字符

通配符_ 单字符匹配，它匹配单个出现的字符

[] 只匹配一个字符  并且这个字符必须是[]范围内的    [0-9]  [a-z][abc]

--模糊查询

select left ('123',1)

select * from Student where left(sName,1)='张'

 

select * from Student where sName like '张%'

select * from Student where sName like '%亮%'

 

空值处理

数据库中，一个列如果没有指定值，那么值就为null，这个null和C#中的null，数据库中的null表示“不知道”，而不是表示没有。因此select null+1结果是null，因为“不知道”加1的结果还是“不知道”。

select * from score where english = null ；

select * from score where english != null ；都没有任何返回结果，因为数据库也“不知道”。

SQL中使用is null、is not null来进行空值判断： select * from score where english is null ； select * from score where english is not null ；

--null

select null+123

select * from score where english is not null

 

数据排序

ORDER BY子句位于SELECT语句的末尾，它允许指定按照一个列或者多个列进行排序，还可以指定排序方式是升序（从小到大排列，ASC）还是降序（从大到小排列，DESC）。

按照年龄升序排序所有学生信息的列表：SELECT * FROM  Student ORDER BY sAge ASC

按照英语成绩从大到小排序，如果英语成绩相同则按照数学成绩从大到小排序 ：SELECT * FROM  Score ORDER BY english DESC,math DESC

ORDER BY子句要放到WHERE子句之后 ： SELECT * FROM  Score where english>=60 and math>=60 ORDER BY english DESC,math DESC

--order by

select top 2 * from student

order by sAge desc

 

select * from student

order by sName desc

 

select * from score

order by english asc,math desc

 

select * from score

where english>60 and math>60

order by english desc,math asc

数据分组

l       出现聚合函数后要加group by 字句

l       select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息

l       按照班级进行分组统计各个班级的人数： select sClassId,count(sName) from student group by sClassId

l       GROUP BY子句必须放到WHERE语句的之后

l       没有出现在GROUP BY子句中的列是不能放到SELECT语句后的列名列表中的 （聚合函数中除外）

l       错误： select sClassId,count(sName),sAge from student group by sClassId（因为sAge没有出现在group by 之后）

l       正确： select sClassId,count(sName),avg(sAge) from student group by sClassId

--group by sClassId

--分组就是把sClassId相同值的那些行，合并为一行

--当有“每个”的含义的时候使用分组

 

--每个班有多少学生

select count(*),sClassId from student

group by sClassId

--第一个问题，使用group by后，select之后的列必须出现在group by字句中或者聚合函数中

select count(sId) as '个数',sClassId from student

where sSex='男'

group by sClassId

--出错，聚合函数不能出现在where字句中

select count(sId),sClassId,avg(sAge) from student

where avg(sAge)>20

group by sClassId

Having语句

Having 是Group By的条件对分组后的数据进行筛选

在Where中不能使用聚合函数，必须使用Having，Having要位于Group By之后，

查询班级人数超过三个人的班级

select sClassId,count(sName) from student group by sClassId having count(sName)>3

注意Having中不能使用未参与分组的列，Having不能替代where。作用不一样，Having是对组进行过滤。

--求平均年龄超过岁的那些班

--having对分组过后的数据进行筛选(和where的作用不同)

select count(sId),sClassId,avg(sAge)from student

group by sClassId

having avg(sAge)>20

select sClassId,count(sName) from student group by sClassId having sAge>30

//错，having是对分组后信息的过滤，能用的列和select中能用的列是一样。

//having无法代替where。

--求人数超过人的班

select count(sId),sClassId from student

group by sClassId

having count(sId)>5

--分组练习

--求男生和女生分别有多少人

select count(sId),sSex from student

group by sSex

 

--求每个班有多少男生

select count(sId),sClassId from student

where sSex='男'

group by sClassId

 

--求平均年龄小于的那些班

select count(sId),sClassId,avg(sAge)from student

group by sClassId

having avg(sAge)<22

 

以上都是要掌握的，下面是要了解的

l       联合结果集

简单的结果集联合：

select tName,tSex from teacher union

select sName,sSex from student

基本的原则：每个结果集必须有相同的列数；每个结果集的列必须类型相容。

select tName,tSex,-1 from teacher union

select sName,sSex,sClassId from student

 

    8、同时插入多条语句

        把查询到的结果插入到现有表中

            insert into 现有表

            select 字段

            from 表

        把现有表的数据插入到一个新表 

            select 字段

            into 新表

            from 表

        --把现有表的数据插入到新表（表不能存在）

        --select * into newStudent from student

        --把现有表的数据复制到一个已存在的表

        --insert into backupStudent select * from students

       

    9、合并查询union 去除重复项

        union all 不去除重复项 所以效率高

        一次插入多条数据

        insert into Score(studentId,english,math)

        select 1,80,100 union

        select 2,60,80

l       Union all

•         select tName,tSex from teacher union

•         select sName,sSex from student

UNION合并两个查询结果集，并且将其中完全重复的数据行合并为一条

•         select tName,tSex from teacher union all

•         select sName,sSex from student

Union因为要进行重复值扫描，所以效率低，因此如果不是确定要合并重复行，那么就用UNION ALL

 

--union

--列的个数一致，类型一致

--排序去除重复数据

select tName,tSex,-1 from teacher union

select sName,sSex,sClassId from student

--union all

--直接连接两个结果集

select tName,tSex from teacher union all

select sName,sSex from student

--要求在一个表格中查询出学生的英语最高成绩、最低成绩、平均成绩

select '最高成绩',max(english) from score union all

select '最低成绩',min(english) from score union all

select '平均成绩',avg(english) from score

 

 

--查询每位老师的信息，包括姓名、工资，并且在最后加上平均工资和最高工资

select tName,tSalary from teacher union all

select '平均工资',avg(tSalary) from teacher union all

select '最高工资',max(tSalary) from teacher

 

--一次插入多条数据

insert into Score(studentId,english,math)

select 1,80,100 union

select 2,60,80 union

select 3,50,59 union

select 4,66,89 union

select 5,59,100

 

select * from score

 

--把现有表中的数据插入到新表中(newStudent表不能存在)

--去除表中重复数据

select * into newStudent from student

select * from newStudent

 

--把现有表的数据复制到一个已存在的表

truncate table newStudent

insert into newStudent select sName, sAge, sNo, sBirthday, sClassId, sSex, sIntime, sPhone from student