数据库查询实例

SQL

CREATE SCHEMA

authorization

CREATE SCHEMA <模式名> AUTHORIZATION <用户名>；

//没有指定 <模式名> 用户名即为模式名

//SCHEMA 类似于命名空间

CREATE SCHEMA "S-T" AUTHORIZATION WANG

CREATE SCHEMA  AUTHORIZATION WANG //没有指定模式名 故模式名为 WANG

create schema

CREATE SCHEMA<模式名>AUTHORIZATION <用户名>[<表定义子句>|<视图定义子句>|<授权定义子句>]；

CREATE SCHEMA TEST AUTHORIZATION ZHANG

CREATE TABLE TAB1(

	COL1 SMALLINT,

	COL2 INT,

	COL3 CHAR(20),

	COL4 NUMERIC(10,3),

	COL5 DECIMAL(5,2)

);

删除

DROP SCHEMA <模式名><CASCADE|RESTRICT>;

CASCADE / RESTRICT 必须选中其中一个

CASCADE 级联 会把该模式中所有数据库对象全部删除

RESTRICT 限制 如果该数据库下方没有定义任何对象，才执行 否则拒绝该条语句的执行

DROP SCHEMA ZHANG CASCADE

create table

CREATE TABLE <表名> (列名 数据类型 [列级完整性条件] [,<表级完整性条件>]);

CREATE TABLE Course (CNO CHAR(4) PRIMARY KEY, CANME CHAR(40) NOT NULL, CPNO CHAR(4),CCREDIT SMALLINT, FOREIGN KEY (CPNO) REFERENCES Coures(CNO) );

表级完整性必须定义在列级完整性后

/表级完整性约束条件 cpno是外码 被参照表是Course 被参照列是CNO/

distinct 去掉表中重复的元素值

select distinct sno

from sc

select Sname,Ssex

from student

where Sdept in(‘CS’)

Order by

order by 某个属性 asc 升序

order by 某个属性 desc 降序

中间放列名 可以加限制 distinct

聚集函数

count()

sum()

avg()

max()

min()

select sum(ccredit)

group by 语句

group by

select column_name,function(column_name)
from table_name
where column_name operator value
group by column_name

该列没有包含在聚合函数或 GROUP BY 子句中

select 类别,sum(数量) as 数量之和，摘要
from a
group by 类别
order by 类别 desc

上述执行完后就会报错

摘要不属于合计函数或者在group by后面

要成功运行

也就要让

select 后面有的属性 要么是在函数里面出现

要么是在 group by 后面出现

也就是说

select *
from sc
group by sno
having min(grade) >= 60

这样的语句过不去，会报错

因为*中包含 cno,sno,grade

但是 cno 并没有包含在刚刚所说的语句中

使用之前需要需要将用到的属性 写在查询前面

select sdept,count( * )
from student
group by Sdept
having count(*) < 100

select cno,count(cno),max(grade),min(grade),avg(grade)
from SC
group by cno

select sno,count(*),max(grade),min(grade),avg(grade),sum(grade)
from SC
group by sno

where

用于 规范子句的查询

having

用于group by 查询 筛选

in

在某个范围中进行查询

like

like ‘%’

用于在where子句中搜索列中的指定模式

select *
from Persons
where city like 'N%'
%用于匹配任意字符 0个或多个
select *
from Persons
where city like '%g'
查询person中以g结尾的城市里的人
_仅匹配1个字符

[!charlist]
[^charlist] 匹配不在字符列中的任何单一字符
select *
from persons
where city like '[ALN]%'
查询persons中以A,L,N开头的城市名称

beween and

真包含 左右两个范围 用于范围限定

and

用于连接两个条件 and的效果

多表查询

select first.Sname,second.Sname
from Student first ,Student second
where first.Sdept = second.Sdept and first.Sno < second.Sno

select first.Sname,second.Sname
from Student first ,Student second
where first.Sdept = second.Sdept and first.Sno < second.Sno

select a.Sage,b.Ccredit
from student as a , course as b

AS 取别名

is NULL 与=NULL 不相同

NULL 表示未知

如果是什么值都没有给 那么 这个值就是null

查询is null就是去判断这个是不是空值

而 = null 则是去判断这个值和null这个东西是否相等

得到一个未知的结果 所以不会返回什么东西

SELECT * FROM SOME_TABLE WHERE NOT(1 = NULL)

SELECT * FROM SOME_TABLE WHERE 1 = NULL

都不会返回数据

SELECT * FROM SOME_TABLE WHERE 1 IN (1, 2, 3, 4, NULL)

返回true

SELECT * FROM SOME_TABLE WHERE 1 not IN (1, 2, 3, 4, NULL)

返回false

SELECT * FROM SOME_TABLE WHERE 5 IN (1, 2, 3, 4, NULL)

什么都不返回

如何正确理解SQL中的NULL_查询数据库 (sohu.com)

参考链接

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-55GMPweB-1661066986392)(C:\Users\10716\AppData\Roaming\Typora\typora-user-images\image-20220401083657806.png)]

单表链接

select x.cno as ‘当前课程’,y.cpno as ‘先修课的先修课’
from course as y,course as x
where x.Cpno = y.Cno

查询先修课的先修课

最开始写反了

写的是哪些课程是其他课程的先修课的先修课

Join

SQL JOIN (w3school.com.cn)

需要链接两个表，获取信息。

select persons.lastname,persons.firstname,orders.orders,orderNo
from persons
inner join orders
on persons.id_p = orders.id_p
order by persons.lastname

Inner Join

表中存在至少一个匹配时，返回行

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

和join 一致

left join

left join 从左表返回所有的行，即使在右表中没有

select column_name(s)
from table_name1
left join table_name2
on table_name1.column_name = table_name2.column_name

有些数据库需要写 left outer join

right join

right join 从右表返回所有的行，即使在左表中没有相匹配的行。

select column_name(s)
from table_name1
right join table_name2
on table_name.column = table_name2.column_name

有些数据库需要写right outer join

full join

只要其中某个表存在匹配，full join就会返回行

有些数据库需要写full outer join

select column_name(s)
from table_name1
full join table_name2
on table_name1.column_name = table_name2.column_name

Union & Union all

Union 用于合并多个 select 语句的结果集

内部的select 必须拥有相同数量的列。

列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

Union 会默认去重

Union all则不会

select into

用于将数据从一个数据库拷贝到另一个数据库

select *
into persons_backup
from persons

GRANT REVOKE

用于修改访问级别。grant用于向用户授予权限 revoke语句将收回已经授予用户的权限。

grant <>[<>]

on <> <>[<><>]

to <>[<>]

[with grant option]

将对指定操作对象的指定操作权限赋予指定的用户

发出grant语句的可以是数据库管理员，数据库创建者，已拥有该权限的用户。

接受属性的可以是一个或多个具体用户，也可以是public，即全体用户。

如果写了with grant option，则接受该属性的用户可以将该权限开放给其他用户。

不写，则不能传播。

grant select
on table student
to ui

grant all privileges
on table student,course
to u2,u3

grant select
on table sc
to public

grant update(sno),select
on table student
to u4

revoke <>[<>]

on <><>[<><>]

from <>,[<>]

[cascade|restrict];

用于从用户那里回收权限

revoke update(sno)
on table student
from u4

revoke select
on table sc
from public

revoke insert
on table sc
from u5 cascade;

cascade 用于级联回收，如果用户5授权这个操作给了其他用户，那么其他用户的从用户5这获得的操作权限就会被回收。

而restrict 则不会级联回收，会拒绝操作。

创建用户create role<角色名>

grant <角色>[<>]

to <> [<>]

[with admin option]

该语句把角色授予某用户，或者另一个角色，这样一个角色所拥有的权限就是授予它的全部角色的权限总和。

revoke <>[<>]

on <><>

from <>[<>]

用户可以回收角色的权限，从而修改角色拥有的权限。

/*角色是方便用于管理权限的  做了一层抽象*/
create role r1
/*先把权限赋予角色*/
grant select,update,insert
on table student
to r1
/*再让角色把权限给人*/
grant r1
to w,x,y
/*最后就可以通过对于角色权限的修改影响它所赋予人的权限*/
revoke r1
from w

grant delete
on table student
to r1

revoke select
on table student
from r1

/*创建一个视图 视图是虚表*/
create view cs_student
as
select *
from student
where sdept = 'cs';
/*给用户开放一部分属性*/
grant select
on cs_student
to w;
/*给用户开放所有的属性*/
grant all privileges
on cs_student
to z;
/*开启审计*/
audit alter,update
on sc;
/*关闭审计*/
noaudit alter,update
on sc;

使用CONVERT获取年份等信息

（转）sql server中使用convert来取得datetime数据类型样式 - Judas - 博客园 (cnblogs.com)

cast 类型转换

cast ( 原数据 as 新类型)

cast( convert(varchar(4) , getdate(), 21)  as int)

Exists

--选修了全部课程的学生
select sname
from s,sc as x
where s.sno = x.sno and not exists(
	select cno
    from sc as y
    where x.sno not in (
    	select sno
        from sc as z
        where z.cno = y.cno
    )
)
--至少选修了201215122同学选修的全部课程的学生
select distinct sno
from sc scx
where not exists 
		(
            select *
            from sc scy
            where scy.sno = '201215122' 
            and
            not exists
            (
                select *
                from sc scz
                where scz.sno = scx.sno
                and
                scz.cno = scy.cno
            )
        )

不存在这样的课程y，学生201215122选修了y，而学生x没有选。

查询至少选修了学生201215122选修的全部课程的学生号码

update

update student
set sage = 22
where sno = '201215121';

insert
into dept_age(sdept,avg_age)
select sdept,avg(sage)
from student
group by sdept

delete
from student
where sno = '201215128';

drop

alter table student add newColumn date;
alter table student alter column sage int;
drop table student cascade;
drop table student restrict;

view

create view birthday(sno,sname,sbirth)
as
select sno,sname,2014-sage
from student;

drop view birthday cascade;
drop view birthday;

断言

在SQL Sever里面无法使用

触发器

触发器在SQL sever 里面完全不一样。

格式完全不同

以上两者理解功能就行，不需要记语法格式。

create trigger update _sc_
on s
for update
as
if update(sno)
begin
update sc 
set sno = i.sno 
from sc sc1,deleted d,inserted i
where sc1.sno = d.sno
end;

删除元组，插入新数据。

其实就是级联操作，d旧数据，i新数据。

还有一个是完整性的都没写

select

--查询结果插入新表
select *
into table1
from table2
where
--查询结果插入已经存在的表
insert
into table1
select *
from table2
where
--查询结果跨数据库
insert
into schema1.table1
select *
from schema2.table2
where 
--查询结果加上新字段插入已经存在的表
insert
into table1
select *,null
from table2
where

表级定义主码、列级定义

--对于单属性构成的码两种定义方式
create table student(
sno char(9) primary key,--列级定义主键
sname char(20)
)

create table student(
sno char(9),
sname char(20),
primary key (sno)--表级定义主键
)
--对于多属性构成的码 只能表级约束
create table SC(
	sno char(9) not null,
    cno char(4) not null,
    primary key (sno,cno)--表级定义主键
)
--表级约束与列级约束
1.对于一个数据列建立的约束，称为列级约束
2.对多个数据列建立的约束，称为表级约束
3.列级约束既可以在列定义时声明，也可以在列定义以后声明
4.表记约束只能在列定义以后声明
--约束条件
主键、外键、非空、唯一、default都是约束条件。
主键、外键、唯一既可以作为表级约束，也可作为列级约束；
非空 ，default只有列级约束

实体完整性（主属性非空）

定义：若属性A是基本关系R的主属性，则属性A不能取空值。

说明：

• 实体完整性规则是针对基本关系而言的。一个基本表通常对于现实世界的一个实体集
• 现实世界中的实体是可区分的，即它们具有某种唯一性标识
• 关系模型中以主码作为唯一性标识
• 主码中的属性即 主属性 不能取空值

参照完整性（外码为null 或者在域中）

定义：参照完整性主要是定义外码，将一个关系的主码放在另一个关系中，作为该关系的属性。外码的取值有两种情况，一种为空，另外一种就是被参照表的主码的域。

• 取空值

• 为之前关系中的主码的值

  说明：

  关系R和S不一定是不同的关系

  目标关系S的主码K和参照完整性的外码F必须定义在同一个（组）域上

  外码并不一定要与相应的主码同名

用户定义完整性

任何数据库都一定要支持实体完整性和参照完整性

但是有时用户有自定义约束的需求。比如性别只有男和女，年龄的限制等。

这种针对于某一具体关系数据库的约束条件称为用户定义的完整性，它反映某一具体应用所涉及的数据必须满足的语意要求。

外键设置中的cascade no action restrict set null

--cascade
在父表上update/delete 记录时，同步update/delete子表的匹配记录
--set null
在父表上update/delete 记录时，将子表上匹配记录的列设为null。要注意子表的外键列不能为not null  
--no action
如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作  
--restrict
同no action, 都是立即检查外键约束
--set default
父表有变更时,子表将外键列设置成一个默认的值 但Innodb不能识别

alter 修改约束

一： 修改表信息

1.修改表名

alter table test_a rename to sys_app;

2.修改表注释

alter table sys_application
comment '系统信息表';

二：修改字段信息

1.修改字段类型和注释

alter table sys_application 
modify 
column app_name varchar(20)
COMMENT '应用的名称';

2.修改字段类型

alter table sys_application 
modify 
column app_name text;

3.单独修改字段注释

目前没发现有单独修改字段注释的命令语句。

4.设置字段允许为空

alter table sys_application 
modify 
column description varchar(255) null 
COMMENT '应用描述';

5.增加一个字段，设好数据类型，且不为空，添加注释

alert table sys_application 
add url varchar(255) not null 
comment '应用访问地址';

6.增删加主键

alter table t_app 
add aid int(5) not null ,
add primary key (aid);

alter table table_name 
drop primary key;

7.增加自增主键

alter table t_app 
add aid int(5) not null auto_increment ,
add primary key (aid);

8.修改为自增主键

alter table t_app 
modify 
column aid int(5) auto_increment ;

9.修改字段名字(要重新指定该字段的类型)

alter table t_app 
change name app_name varchar(20) not null;

10.删除字段

alter table t_app 
drop aid;

11.在某个字段后增加字段

alter table t_app 
add column gateway_id int not null default 0 AFTER aid； #(在哪个字段后面添加)

12.调整字段顺序

alter table t_app 
change gateway_id gateway_id int not null after aid ; #(注意gateway_id出现了2次)

为约束命名 删除约束

create table S2(
sno numeric(6)
constraint C1 check (sno between 90000 and 99999),
sname char(20)
constraint C2 not null,
sage numeric (3)
constraint C3 check(sage < 30),
ssex char(2)
constraint C4 check(ssex in ('男','女')),
constraint StudentKey primary key (sno)--主码约束
)
alter table S2
drop C1

deleted inserted

//deleted
Deleted 表用于存储 DELETE 和 UPDATE 语句所影响的行的复本。
在执行 DELETE 或 UPDATE 语句时，行从触发器表中删除，并传输到 deleted 表中。
Deleted 表和触发器表通常没有相同的行。
//inserted
Inserted 表用于存储 INSERT 和 UPDATE 语句所影响的行的副本。
在一个插入或更新事务处理中，新建行被同时添加到 inserted 表和触发器表中。
Inserted 表中的行是触发器表中新行的副本。
//
1.插入操作（Insert） 
Inserted表有数据，Deleted表无数据 

2.删除操作（Delete） 
Inserted表无数据，Deleted表有数据 

3.更新操作（Update） 
Inserted表有数据（新数据），Deleted表有数据（旧数据）

//Instead Of触发器与After触发器的工作流程是不一样
1.After触发器是在SQL SERVER服务器接到
    执行SQL语句请求之后，
    先建立Inserted和Updated临时表
    然后在更改物理表上的数据
    最后才激活触发器程序
    
2.Instead Of触发器在SQL SERVER服务器接到
    执行SQL语句请求，
    建立Inserted和Updated临时表后
    就激活了Instead Of触发器程序
    
    至于SQL语句的请求如何操作数据就不在管了，把执行权全权叫给了Instead Of触发器。

--级联修改
create trigger update_sc
on s
for update(sno)
begin
update sc2
set sno = i.sno
from sc2,deleted as d,inserted as i
where sc2.sno = d.sno

update s
set sno = '002'
where sno = '001';

select * from s
select * from sc2
--级联删除
create trigger delete_sc
on s
for delete
as
delete sc2
from sc2,deleted as d
where sc2.sno = d.sno

--在有约束的情况下 进行修改操作
create trigger update_sc 
on s
instead of update   --触发器的逻辑 对于所有的更新 如果当前的更新了 就把这个修改为更新的值
as 
begin 
	--先向s里面插入数据 防止违反外键约束
	insert
	into s
	select *
	from inserted
	--再在sc2表中更新数据
	update sc2
	set sno = i.sno 
	from sc2,
	deleted as d,inserted as i
	where sc2.sno = d.sno
	--最后把该删的删了
	delete S
	from deleted
	where s.sno = deleted.sno
end 

关系数据理论

R<U,F>

原本为R<U,D,DOM,F>

R为关系名，符号化的元组语义

U为一组属性

D为属性组U中的属性所来自的域

DOM为属性到域的映射

F为属性组U上的一组数据依赖

由于D,DOM和模式设计关系不大，因此简化成了R<U,F>。

1NF

每一个分量必须是不可分的数据项，满足了这个条件的关系模式就属于第一范式。

数据依赖

定义：一个关系内部属性与属性之间的一种约束关系。

理解：看成映射也许也行。就是属性和属性之间的映射。

描述：这种约束关系是通过属性间值的相等与否体现出来的数据间相关联系。

它是现实世界属性间相互联系的抽象，是数据的内在性质，是语义的体现。

函数依赖（function dependency）

定义：R（U）是属性集U上的关系模式，X,Y都是U的子集。对于R（U）的任意一个可能的关系r，r中不可能存在两个元组在X上的属性值相等，而在Y上的属性值不等，则称X函数确定Y，或者Y函数依赖于X，记作X->Y;

理解：类似于函数的定义，但是在这里是一种属性对一种属性。

描述：函数依赖与其他依赖一样是语义范畴的概念，只能根据语义来确定一个函数依赖。设计者也可以对现实世界做强制要求。

相关术语

X->Y,Y不是X的真子集，X->Y是非平凡的函数依赖。

X->Y，Y是X的真子集，X->Y的平凡函数依赖。对于任意一关系模式，平凡函数依赖都是存在的。

若X->Y,则X称为这个函数依赖的决定属性组，也称决定因素。

若X->Y,Y->X,则记作X<–>Y。

若Y不函数依赖于X,则记作X-|>Y；

完全函数依赖

在函数依赖的基础上，对于X的任意一个真子集，都不存在这个真子集函数依赖于Y，那么则称Y对X完全函数依赖。

记为X-F>Y;

部分函数依赖

存在真子集，函数依赖于Y，那么就是部分函数依赖。

记为X-P>Y;

例子：（sno,cno）-> grade,(sno,cno)->sdept。前一个为完全函数依赖，后一个为部分函数依赖。

因为sno 就可以 sno -> sdept;

传递函数依赖

定义：在R（U）中，如果X->Y，（Y不是X的真子集），Y-|>X，Y->Z（z不是y的真子集）则称Z对X传递函数依赖

加上条件Y-|>X，是为了防止是X->Z；而不是传递函数依赖了。

候选码

设K为R（U，F）中的属性或者属性组合，若U完全函数依赖于K，则K为R的候选码。

U是属性，F是数据依赖。

如果U函数依赖于K，则K为超码，候选码是特殊的超码，即候选码的超集一定是超码，候选码的任何一个真子集一定不是超码。

若候选码多于一个，则选择其中一个作为主码。

包含在任何一个候选码中的属性称为主属性；

不包含在任何候选码中的属性称为非主属性/非码属性；

最简单的情况，单个属性是码，最极端的情况，整个属性组是码，称为全码。

例子：S（sno,sdept,sage）中单个属性sno是码；

SC（SNO,CNO,GRADE）中属性组合（sno，cno）是码

R（P,W,A）是全码

主属性和非主属性

包含在任意一个候选码里面的属性组，都是主属性。没有包含任意一个码的属性组，就是非主属性。

外码

关系模式R中属性或属性组X并非R的码，但X是另一个关系模式的码，则称X是R的外部码。

规范化

一个低一级范式的关系模式通过模式分解可以转换为若干个高一级范式的关系模式的集合。

2NF

R属于1NF，且每一个非主属性完全函数依赖于任何一个候选码，则R属于2NF；

这部分可以参考书上的那个例子。

3NF

设关系模式R<U,F>属于1NF,若R中不存在这样的码X，属性组Y及非主属性Z,有Z传递函数依赖于X，则其属于3NF;

这个是否定的定义。

BCNF

关系模式R<U,F>属于1NF，若X非平凡函数依赖于Y时，X必含有码，则R属于BCNF.

关系模式中每一个决定因素都含有码。决定因素见之前。

多值依赖(muti-valued dependency MVD)

$$X\to \to Y\iff \forall 关系r于R(U)中,给定一组值(x,z),\exists Y仅与x有关而与z无关$$

从定义上去考虑，还是极其的抽象，不过也亏这些人能考虑到这些。

简单来说，以树的观点看，x能到y，z，但是y，z之间是没有关系的。因此就会对于一组给定的x，z，存在y只与x有关，但是与z无关。但是如果我想去增加一组数据，或者是增加一本书籍，就会出现插入异常。

而且数据冗余大，多次存储重复的数据。还有一种定义法更为抽象，而且是赋值，不是相等。

在R(U)的任一关系r中，如果存在元组t，s使得t[X]=s[X]，那么就必然存在元组w，v∈r，（w，v可以与s，t相同）, 使得w[X]=v[X]=t[X]，而w[Y]=t[Y]，w[Z]=s[Z]，v[Y]=s[Y]，v[Z]=t[Z]；

（即交换s，t元组的Y值所得的两个新元组必在r中则Y多值依赖于X，记为X→→Y。这里X，Y是U的子集，Z=U-X-Y）

并且是全集相减。

参考书上的例子。

缺点：增加一个属性值，需要插入多个元组。删除、修改同理。

性质：

1. 多值依赖具有对称性。X->->Y,X->->Z。
2. 多值依赖有传递性，X->->Y,Y->->Z,X->->Z-Y；
3. 函数依赖是多值依赖的特殊情况。
4. X->->Y,X->->Z,X->->YZ
5. X->->Y,X->->Z,X->->Y∩Z
6. X->->Y,X->->Z,X->->Z-Y,X->->Y-Z；

4NF

定义：

关系模式R属于1NF,如果对于R的每个非平凡的多值依赖X->->Y,（Y不是X的真子集），X都有码，则其属于4NF；

查询实例

查询成绩在95-99 （真包含）的选课记录的学号、课程号与成绩

select sno, cno, grade
from sc
where grade between 95 and 99

查询年龄既不是18岁 20岁 也不是24岁的学生的姓名和性别

select sname, ssex
from student
where sage <> 18 and sage <> 20 and sage <> 24

select sname, ssex
from student
where sage not in (18, 20, 24)

查询课程名中第二个字为“据”的课程的课程号，课程名和学分

select cno, cname, ccredit
from course
where cname like '_据%'

查询没有先行课的课程号和课程名

select cno, cname
from course
where cpno is null /*此处必须为is null 才是判断先行课为空*/

查询有学生选的课程的门数

select count(distinct cno)
from sc
where sno is not null

查询全体学生的最小年龄

select min(sage)
from student

查询有选课记录的同学的学号和他相应的选课门数

select sno, count(sno)
from sc
group by sno

查询每门课的先修课的先修课

--x是当前的课程 y是先修课程 所以y.cpno就是先修课的先修课
select x.cno, y.cpno
from course as x, course as y
where x.cpno = y.cno

查询与李勇年龄相同的学生的学号、姓名、年龄和所在系

select sno, sname, sage, sdept
from student
where sage in (
	select s.sage
    from student as s
    where s.sname = '李勇'
)

查询每个学生超过他选修课程平均成绩的学号和课程号

select x.sno, x.cno
from sc as x
where grade > (
	select avg(grade)
    from sc as y
    where x.sno = y.sno
)

查询每门课程超过它平均分的那些选修记录的学号和课程号

select x.sno, x.cno
from sc as x
where grade > (
	select avg(grade)
    from sc as y
    where x.cno = y.cno
)

求选修了各门课的人数及平均成绩

select count(sno), avg(grade)
from sc
group by cno

求选修课程在2门及以上且都及格的学生号及总平均分

select sno, avg(grade)
from sc
group by sno
having count(cno) >= 2 and 
	sno not in (
		select sno
        from sc as y
        where y.grade < 60
	)
	
select sno, avg(grade)
from sc
group by sno
having count(sno) >= 2 and
	sno not in (
    	select sno
        from sc as y
        where y.grade < 60
    )
  
/*
上述两种唯一的区别就在于count的是sno 还是cno
这两种从运行角度来说 其实是一样的
但是如果允许cno为null 那么上述两份代码运行结果就会不一样
第一份代码实际上查找的是 分类后 这个学生有多少条记录
第二份代码查找的是 分类后 这个学生所选课程的数量 课程为null自然跳过了 如果还需要限制课程之间不一样 则需要写成 count(distinct cno)
*/

求2012级学生中选修课程在2门以上且都及格的学生号及总平均分

select sno, avg(grade)
from sc
group by sno
having sno like '2012%'
	and count(sno) >= 2
	and sno not in (
    	select sno
        from sc as y
        where y.grade < 60
    )
order by avg(grade) desc

查询计算机系与电子工程系不同职称的教师的tname和proof

select x.tname, x.proof, y.tname, y.proof
from (select tname, proof from teacher where x.dept = 'CS') as x(tname,proof), 
(select tname, proof from teacher where y.dept = 'ES') as y(tname,proof)
where x.proof <> y.proof

select distinct x.tname,x.proof,y.tname,y.proof
from teacher as x,teacher as y
where x.dept = 'CS' and  exists (
select y.proof
where y.dept = 'ES' and x.proof <> y.proof
)

查询选修编号为3-105课程且成绩至少高于选修编号为3-245课程的同学的cno,sno和degree，并按degree从高到低排序

select distinct cno, score.sno, degree
from score, student
where cno = '3-105'
	and degree > (
    	select min(degree)
    	from score
    	where cno = '3-245'
    )
order by degree desc

/*以前写错了 多查了一个student表
两个表进行了连接 所以最后的结果会出现重复 应该去重
但是这题其实不需要查student表 直接去掉就好了
*/