关于数据库的介绍

1．创建数据库：

createdatabase yh

2.删除数据库：

drop database yh

3.建表：

use yh

createtable student

(

name char(10)notnull,

ID int notnull,

garden char(10)null

)

4.删除表：

drop table student cascade

5.建立索引

use yh

createindex student_index on student(name)

6.SC表按学号升序和课程号降序建唯一索引：

use db_2008

createuniqueindex SCno on SC(Sno ASC,Cno DESC)

7.Course表按课程号升序建唯一索引：createuniqueindex Coucno on Course(Cno)

8.删除索引：

use yh

dropindex student_index

9.设置主键：

Create table Course

( Cnochar(4) primarykey,

Cname char(40),

Cpnochar(4),

Ccreditsmallint,

Foregin key (Cpno) referencesCourse(Cno) );

10.修改表alert

将student表中的sage列的输入类型改为int类型：

use yh

altertable course

altercolumn Cname int

11.增加属性列：

use yh

altertable course

add interest varchar(50)

12.增加课程名称必须取唯一值的约束条件：

use yh

altertable Course addunique(Cname)

13.select 查询语句：

use db_2008

select Sno,Sname from Student

14.查询全体学生的所有属性：

use db_2008

select*from Student

15.查询全体学生的姓名、出生年份:

use db_2008

select Sname,'Year of Birth: ',2004-Sage from Student

16.去掉表中重复的行:

use db_CSharp

selectdistinct Cno from SC

17.查询计算机科学系全体学中年龄为18的学生: use db_CSharp

select Sname

from Student

where Sage=18

18.查询所有年龄在20岁以下的学生姓名及其年龄：use db_CSharp

select Sname,Sage

from Student

where Sage< 20

19.查询年龄在18~21岁.之间的学生的姓名、性别和年龄use db_CSharp

select Sname,Sex,Sage

from Student

where Sage between 18 and 21

20.查询年龄不在20~23岁之间的学生姓名、性别和年龄use db_CSharp

select Sname,sex,Sage

from Student

where Sage notbetween20 and23

21.查询年龄为18 25 27 学生的姓名和性别和学号。use db_CSharp

select Sno,Sname,sex

from Student

where Sage in(18,25,27)

22.查询年龄不为18 25 27 学生的姓名和性别和学号。

use db_CSharp

select Sno,Sname,sex

from Student

where Sage not in(18,25,27)

23.查询学号为201109001的学生的详细情况：

use db_CSharp

select*

from Student

where Sno like'201109001'

24.查询所有姓王学生的姓名、学号和性别：

use db_CSharp

select Sname,Sno,sex

from Student

where Sname like'王%'

25.查询姓"张"且全名为三个汉字的学生的姓名

use db_CSharp

select Sname

from Student

where Sname like'张__%';

26.查询名字中第2个字为"子"字的学生的姓名和学号

use db_CSharp

select Sname,Sno

from Student

where Sname like'_子%';

27.查询所有不姓刘的学生姓名：

use db_CSharp

select Sname,Sno,sex

from Student

where Sname not like'王%';

28.有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号：use db_CSharp

select Sno,Cno

from SC

where Grade is null

29.查所有有成绩的学生学号和课程号：

use db_CSharp

select Sno,Cno

from SC

where Grade is not null

30.查询计算机系年龄在24岁以下的男学生姓名

use db_CSharp

select Sname

from Student

where Sex='男'and Sage<24

31.查询年龄为18 24 27学生的姓名和性别：

use db_CSharp

select Sname,sex

from Student

where sage in( 18,24,27 )

等价于：use db_CSharp

select Sname,sex

from Student

where Sage= 18 or Sage= 24 or Sage= 27

32.查询选修了001号课程的学生的学号及其成绩，查询结果按分数降序排列：use db_CSharp

select Sno,Grade

from SC

where Cno='001'

order by Grade desc;

use db_CSharp

select*

from Student

order by Sage desc

33.查询学生总人数：

use db_CSharp

select COUNT(*)

from Student

34.查询选修了课程的学生人数：

use db_CSharp

select COUNT(distinct Sno) from SC

35.计算1号课程的学生平均成绩：

use db_CSharp

select AVG(Grade)

from SC

where Cno='001'

36.查询选修1号课程的学生最高分数：

use db_CSharp

select MAX(Grade)

from SC

where Cno='001'

37.查询学生200215012选修课程的总学分数：

use db_CSharp

select SUM(Credit)

from SC,Course

where Sno='201109001'and https://www.360docs.net/doc/a84827174.html,o=https://www.360docs.net/doc/a84827174.html,o

38.统计学生成绩表中性别为男的同学的最大年龄、最低年龄、平均年龄和总人数

use db_2008

Select max(sage)as'最大年龄',MIN(sage)as'最小年龄

',COUNT(*)as'总人数',avG(sage)as'平均年龄'

From Student

where Sex='男'

39.统计学生表中最大年龄、最小年龄、平均年龄和平均年龄

use db_2008

Select max(sage)as'最大年龄',MIN(sage)as'最小年龄

',COUNT(*)as'总人数',avG(sage)as'平均年龄'

From Student

groupby Sex

40.统计学生成绩表中每个同学的最大年龄、最小年龄、平均年龄和总人数，22岁以下的学生不参与统计

use db_2008

Select max(sage)as'最大年龄',MIN(sage)as'最小年龄

',COUNT(*)as'总人数',

avG(sage)as'平均年龄'

From Student

where Sage>22

groupby Sex

41.统计学生成绩表中每个同学的最大年龄、最小年龄、平均年龄和总人数并输出平均年龄大于22岁的学生

use db_2008

Select max(sage)as'最大年龄',MIN(sage)as'最小年龄

',COUNT(*)as'总人数',avG(sage)as'平均年龄'

From Student

groupby Sno

having avG(sage)>22

42.使用compute子句对所有学生的人数进行明细汇总

use db_2008

select*

from SC

compute count(sno)

43.使用compute by子句按照学号对所有学生的人数进行明细汇总use db_2008

select*

from student

orderby sage

compute count(Sno)by sage

44.查询每个学生及其选修课程的情况

use db_2008

select Student.*,SC.*

from Student,SC

where Student.Sno=SC.Sno

或者：use db_2008

select Student.*,SC.*

from Student join SC on Student.Sno=SC.Sno也可以实现45.用自然连接完成

use db_2008

select student.sno,Sname,sex,Sage,Cno,Grade from Student,SC

where Student.Sno=SC.Sno

46.查询每一门课的间接先修课（即先修课的先修课）

use db_2008

https://www.360docs.net/doc/a84827174.html,o,second.Cpno

from Course first,Course second

wherefirst. Cpno=https://www.360docs.net/doc/a84827174.html,o

47.查询每个学生及其选修课程的情况左外连接

use db_2008

select Student.Sno,Sname,Sex,Sage,Cno,Grade from Student leftjoin SC on (Student.Sno=SC.Sno)

48.查询选修1号课程且成绩在90分以上的所有学生

use db_2008

select Student.Sno,Sname

from Student,SC

where Student.Sno=SC.Sno and

https://www.360docs.net/doc/a84827174.html,o='001'and SC.Grade> 90;

49.查询每个学生的学号、姓名、选修的课程名及成绩

use db_2008

select Student.Sno,Sname,Cname,Grade

from Student,SC,Course

where Student.Sno=SC.Sno and https://www.360docs.net/doc/a84827174.html,o=https://www.360docs.net/doc/a84827174.html,o;

50.嵌套查询

use db_2008

select Sname,Sno from Student where Sno in

(select Sno from SC where Cno='001')

51.查询与“刘晨”在同一个性别的学生

use db_2008

select Sno,Sname,Sdept

from Student

where Sdept in

(select Sdept

from Student

where Sname='王子');

用自身查找也能完成查询

use db_2008

SELECT S1.Sno,S1.Sname,S1.Sdept from StudentS1,StudentS2 where S1.Sdept=S2.Sdept and S2.Sname='王子';

52.查询选修了课程名为“数据结构”的学生学号和姓名

use db_2008

select Sno,Sname from Student where Sno in( select Sno from SC where Cno in(

select Cno from Course where Cname='数据结构'

)

);

use db_2008

select student.sno,Sname

from Student,SC,Course

where Student.Sno=SC.Sno and

https://www.360docs.net/doc/a84827174.html,o=https://www.360docs.net/doc/a84827174.html,o and

https://www.360docs.net/doc/a84827174.html,ame='数据结构'

用连接查询实现

53.查询与“刘晨”在同一性别的学生 (用=代替in)

use db_2008

select Sno,Sname,Sdept

from Student

where Sdept=

(select Sdept

from Student

where Sname='王子')

54.找出每个学生超过他选修课程平均成绩的课程号

use db_2008

select Sno,Cno

from SCx

where Grade>=(select AVG(Grade)

from SCy

where y.Sno=x.Sno);

55.查询其他系中比软件学院某一学生年龄小的学生姓名和年龄

use db_2008

select Sname,Sage,Sdept

from Student

where Sage

from Student

where Sdept='软件学院')

and Sdept<>'软件学院';

56. 查询其他系中比软件学院某一学生年龄小的学生姓名和年龄（用聚集函数实现）

use db_2008

select Sname,Sage,Sdept

from Student

where Sage<(select MAX(Sage)

from Student

where Sdept='软件学院')

and Sdept<>'软件学院';

57.查询其他系中比数学院所有学生年龄都小的学生姓名及年龄

use db_2008

select Sname,Sage,Sdept

from Student

where Sage

from Student

where Sdept='数学院')

and Sdept<>'数学院';

也可以用聚集函数实现use db_2008

select Sname,Sage,Sdept

from Student

where Sage<(select MIN(Sage)

from Student

where Sdept='数学院')

and Sdept<>'数学院';

58.查询所有选修了001号课程的学生姓名用exists

（查询所有没有选修了001号课程的学生姓名用not exists）use db_2008

select Sname

from Student

where exists

(select*

from SC

where sc.Sno=Student.Sno and Cno='001')

59.查询所有选修了001号课程的学生姓名用exists 用连接运算

use db_2008

select Sname

from Student,SC

where Student.Sno=SC.Sno and https://www.360docs.net/doc/a84827174.html,o='001';

60.查询与“王子”在同一个系学习的学生可以用带EXISTS谓词的子查询替换

use db_2008

select Sno,Sname,Sdept from StudentS1where EXISTS

(select*from StudentS2where S2.Sdept=S1.Sdept

and S2.Sname='王子');

61.查询选修了全部课程的学生姓名。

use db_2008

select Sname from Student where exists

(select*from Course where exists

(select*from SC where Sno=Student.Sno and Cno=https://www.360docs.net/doc/a84827174.html,o) )

62.学生2011090002选修了y，而学生x没有选。

use db_2008

selectdistinct Sno from SCSCX where notexists

(select*from SCSCY where SCY.Sno=' 201109002'

andnotexists

(select*

from SCSCZ

where SCZ.Sno=SCX.Sno and https://www.360docs.net/doc/a84827174.html,o=https://www.360docs.net/doc/a84827174.html,o));

63.查询计算机科学系的学生及年龄不大于19岁的学生

use db_2008

select*

from Student

where Sdept='数据结构'

union

select*

from Student

where Sage<=19

方法2：

use db_2008

selectdistinct*