数据库（Sql server语言）（一）

例题：（不介绍创建和插入）

star表      

首先先来讲下group by语句：

group by的使用条件

使用了分组子句一定有聚合操作，但是执行聚合操作不一定要有分组子句

如：求男同学的人数，此时聚合值只有一个，因此不必分组

同一个聚合操作的值有多个时，才必须使用分组子句

如：求每一年龄的学生人数，此时聚合值有多个，与年龄有关，必须分组。

（1）

select sno,avg(grade)
from sc
where avg(grade)>=88
group by sno

 （2）

select sno,avg(grade)
from sc
group by sno
having avg(grade)>=88

where子句与having子句

where子句作用于基图或视图，从中选择满足条件的元组

where子句中不能用聚集函数作为条件表达式，所以第（1）个是错的，第（2）个才是对的

having语句作用于组，从中选择满足条件的组 

●查询每个组合的名称及其成员个数

select g.name,count(*) 成员个数
from star s,stargroup g
where s.gid = g.gid
group by g.name

 

 如果不写where s.gid=g,gid会出现成员个数重复

所以一般语句为：where...group by.....having....order by

重点：嵌套语句：带有in，比较字符串，any（some）或者all，exists的子查询

不相关查询：子查询的查询条件不依赖于父循环

●由里向外逐层处理：每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查询条件

相关查询：子查询的查询条件依赖于父循环

●首先取外层查询中表的第一个元组，根据他与内层查询相关的属性值处理内层循环，若where子句返回真值，则取此元组放入结果表

●然后再取外层表的下一个元组

●重复这一过程直到外层表全部检查完为止

注：子查询不能用order by

常见的是不相关查询：不相关子查询和自连接

查询与”刘晨“在同一个系学习的学生

select sno,sname,sdept
from student
where sdept in
(select sdept
from student
where sname='刘晨')

select s1.sno,s1.sname,s1.sdept
from student s1,student s2
where s1.sdept=s2.sdept
and s2.sname='刘晨';

两者返回值相同，但是多数情况下，不相关查询较为复杂，很难优化，尽量能用连接就用连接实现

●查询身高最高的团员姓名及其身高

select name,height from star
where height = (select max(height) from star)

●查询每个组合的名称，及其平均年龄

重点：year（getdate()）

select g,name '团名',avg(year(getdate())-byear) '平均年龄'
from star s,stargroup g
where s.gid=g.gid
group by g.name

 

●查询团员的最高身高，最矮身高以及最高身高与最矮身高的身高差

select
(select max(height) from star)"最高’
(select min(height) from star)'最矮”
(select max(height) from star) -(select min(height) from star)最大身高差

●按照身高降序列出首尔出生的团员的姓名，所属团名，出生年份，身高

升序：ASC，降序：DESC，默认值为升序，可以不写，对于空值，排序时显示的次序由具体系统实现来决定

select s.name,g.name,byear,height
from star s,stargroup g
where s.gid=g.gid
and born='首尔'
order by 4 desc

 ●查询哪些国家或城市只出了一个团员

group...by...

select born'只有一个团员的国家或城市’
from star
group by born
having count(*)=1

●查询团员中不同姓及其人数

substring（name，1，1）：截取第一个字

substring（name，1，2）：从第一个字开始截，截两个

注：substring是从1开始的

select substring (name, 1, 1) '姓’,count(*)'人数'
from star
group by substring (name,1,1)
select substring(name,1,1)'姓’，count(*)'人数'
from star
group by substring(name,1,1)

●查询姓’金‘的团员是姓’李‘的团员多少倍

like '金'

select
(select count(*) from star where name like'金%)"姓金的人数”
select count (*)
from star where name like'李%' 姓李的人数
select count (*)
from star where name like'金%')/(select count(*)
from star where name
like'李%)，姓金的人数是姓李的人数的倍数

注：如果字符串本身就包含%，_,那么就要用转义字符‘\’,跟在'\'的字符不再具有通配符的含义，转义为普通的%，_

a%b:表示以a开头，以b结尾的任意长度字符串

a_b:表示以a开头，以b结尾的长度为3的字符串

补充：

select name,sex
from student
where sdept like'cs' or sdept like'ma' and sex like'女'

select name,sex
from student
where sdept='cs' or sdept='ma' and sex='女'

 如果like后面的匹配字符串不含通配符，可以用=运算代替like，用！=或<>代替not like

not的优先级比and高，and的优先级比or高，括号可以改变优先级 ,要用括号括起来，否则先查询and

查询（cs）或（ma）的女生的姓名和性别

select name,sex
from student
where (sdept='cs' or sdept='ma') and sex='女'

查询（cs）的学生或（ma）的女生的姓名和性别 

select name,sex
from student
where sdept='cs' or (sdept='ma' and sex='女')

●查询首尔出生的团员最少的组合名称

select top 1 gid

select name from stargroup
where gid in (select top 1 gid
from star
where born ='首尔
group by gid
order by
count(*))