基础是很重要的，以下通过三个表学生表S,课程表C,学生课程表SC
来举例说明SQL的操作:
create table S
(
S# char(8),
SNAME varchar(20) not null,
AGE tinyint,
SEX bit default 1,
DNAME char(10),
primary key(S#),
check(SEX =0 or SEX = 1)
)
create table C
(
C# char(8),
CNAME varchar(8) not null,
CREDIT tinyint,
PC# char(8),
primary key(C#),
foreign key(PC#) references C(C#)
)
create table SC
(
S# char(8),
C# char(8),
GRADE tinyint,
primary key (S#,C#),
foreign key(S#) references S(S#) on delete cascade,
foreign key(C#) references C(C#) on delete cascade,
check((GRADE is null) or (GRADE between 0 and 100))
)
SELECT colid,name,xtype,length,xusertype,offset
FROM syscolumns
WHERE id = object_id('s')
SELECT SNAME,CNAME
FROM S,C,SC
WHERE S.S# = SC.S# AND C.C# = SC.C#
--列出每个学生的姓名及其所选修的课程名
SELECT SNAME,CNAME
FROM S,C,SC
WHERE S.S# = SC.S#
AND C.C# = SC.C#
--给出所有学生的学号,姓名以及出生年份
SELECT S# as '学号',SNAME as '姓名',2005 - AGE as '出生年份'
FROM S
--找出选修了'002'课程并且其成绩比'0002'学生选修该门课程成绩高的学生学号
SELECT Y.S#
FROM SC as X,SC as Y
WHERE X.S# = '0002'
and X.C# ='002'
and Y.C# ='002'
and Y.GRADE > X.GRADE
--按学号升序输出SC关系,对应同一个学生的选课元组按课程号降序排列
SELECT S#,C#,GRADE
FROM SC
ORDER BY S# asc,C# desc
--列出所有成绩都及了格的学生的平均成绩
SELECT S#,avg(GRADE)
FROM SC
GROUP BY S#
HAVING min(GRADE) >= 60
--列出每个学生所有及格了的课程的平均成绩
SELECT S#,avg(GRADE)
FROM SC
WHERE GRADE >= 20
GROUP BY S#
--列出选修了001课程的学生的学号及姓名
SELECT S#,SNAME
FROM S
WHERE S# in
(
SELECT S#
FROM SC
WHERE C# = '001'
)
--找出平均成绩最高的学生号
SELECT S#
FROM SC
GROUP BY S#
HAVING AVG(GRADE) >= all
(
SELECT AVG(GRADE)
FROM SC
GROUP BY S#
)
--<> some 运算符与not in 有所不同,<> some表示不等于a或不等于b或不等于c
--而not in 表示不等于a且不等于b且不等于c,所以<>all 与 not in 意义相同
--列出选修了全部课程的学生姓名
--本查询等价于任意课程,所求学生选之 <=> 不存在任何一门课程,所求学生没有选之
SELECT SNAME
FROM S
WHERE not exists
(
SELECT *
FROM C
WHERE not exists
(
SELECT *
FROM SC
WHERE SC.C# = C.C#
AND SC.S# = S.S#
)
)
--插入功能
--插入单条元组
INSERT INTO S values('s5','王郦',25,0,'计算机系')
--将平均成绩大于30的学生加入到excellent表中
create table excellent
(
S# char(8),
GRADE tinyint
)
INSERT INTO excellent(S#,GRADE)
SELECT S#,AVG(GRADE)
FROM SC
GROUP BY S#
HAVING AVG(GRADE) > 30
--从S表中删除平均成绩<50的记录
DELETE FROM S
WHERE S# in
(
SELECT S#
FROM SC
GROUP BY S#
HAVING AVG(GRADE) < 60
)
--将lixiaojian同学选修001课程的成绩改为该门课程的平均成绩
UPDATE SC
SET GRADE =
(
SELECT avg(GRADE)
FROM SC
WHERE C# = '001'
)
WHERE S# =
(
SELECT S#
FROM S
WHERE SNAME ='lixiaojian'
)
AND C# = '001'
--常见错误的写法
--求成绩最高的学生
SELECT S#
FROM SC
WHERE GRADE = max(GRADE)
--错误的原因:
--聚合不应出现在 WHERE 子句中，除非该聚合位于 HAVING 子句或选择列表所包含的子查询中，
--并且要对其进行聚合的列是外部引用。
--求成绩最高的学生的学好及其成绩
SELECT S#,max(GRADE)
FROM SC
--错误的原因:
--列 'SC.S#' 在选择列表中无效，因为该列未包含在聚合函数中，并且没有 GROUP BY 子句。
--求选修了课程的学生人数
SELECT count(S#)
FROM SC
--错误的原因:
--SQL的返回结果是缺省保留重复值的,需要用distinct关键字来去除
SELECT count(distinct S# )
FROM SC
--列出同时选修了001和002课程的学号
SELECT S#
FROM SC
WHERE SC.C# = '001' AND SC.C#='002'
--错误的原因:
--一个课程号不可能同时等于两个不同的值
--正确的写法是
SELECT S#
FROM SC
WHERE SC.C# = '001'
AND S# in
(
SELECT S#
FROM SC
WHERE C# = '002'
)
