建立数据库

 

1. CREATE DATABASE DB_Student

建立表

 

1. CREATE TABLE Student
2.  
3.  (Sno CHAR(9) PRIMARY KEY,--主码
4.  
5.  Sname CHAR(20) UNIQUE,--唯一值
6.  
7.  Ssex CHAR(2),
8.  
9.  Sage SMALLINT,
10.  
11.  Sdept CHAR(20)
12.  
13.  );
14.  
15. CREATE TABLE Course
16.  
17.  (Cno CHAR(4) PRIMARY KEY,
18.  
19.  Cname char(40),
20.  
21.  Cpno CHAR(4),
22.  
23.  Ccredit SMALLINT,
24.  
25. FOREIGN KEY (Cpno) REFERENCES Course(Cno)
26.  
27.  );
28.  
29. CREATE TABLE SC
30.  
31.  (Sno CHAR(9),
32.  
33.  Cno CHAR(4),
34.  
35.  Grade SMALLINT,
36.  
37. PRIMARY KEY (Sno,Cno),
38.  
39. FOREIGN KEY (Sno) REFERENCES Student(Sno),--外码
40.  
41. FOREIGN KEY (Cno) REFERENCES Course(Cno)
42.  
43.  );

数据结构图

 

 

表操作

 

1. alter table Student add S_entrance date--增加列
2.  
3. alter table student alter column Sage int--修改字段类型
4.  
5. alter table course add unique (Cname)--增加唯一性约束
6.  
7. drop table Student--删除基本表
8.  
9. drop table student cascade--删除基本表及相关依赖对象

创建索引

 

 

1. drop index stusname

 

查询数据

基础查询

 

1. select sno,sname from student
2.  
3. select sname,sno,sdept from student
4.  
5. select sname,2004-sage from student
6.  
7. select sname,'Year of Birth:',2004-sage, lower(sdept) from student--查询结果第二列是一个算数表达式
8.  
9. select sname name,'Year of Birth:' BIRTH,2004-sage birthday,LOWER(sdept) department from student--LOWER()小写字母
10.  
11. select sno from sc
12.  
13. select distinct sno from sc--消除重复行
14.  
15. select sno from sc
16.  
17. select all sno from sc
18.  
19. select sname from student where sqept='CS'
20.  
21. --=、>、<、>=、<=、!=、<>、!>、!< 比较的运算符
22.  
23. select sname,sage from student where sage<20
24.  
25. select distinct sno from sc where sage<20
26.  
27.  
28.  
29. select sname,sdept,sage from student where sage between 20 and 23
30.  
31. select sname,sdept,sage from student where sage not between 20 and 23
32.  
33.  
34.  
35. select sname,ssex from student where sdept in ('CS','MA','IS')
36.  
37. select sname,sage from student where sdept not in('CS','MA','IS')
38.  
39.  
40.  
41. select * from student where sno like '200215121'
42.  
43. select * from student where sno='200215121'

--字符匹配

 

1. --% 任意长度字符串，_ 任意单个字符，ESCAPE 转义字符
2.  
3. select sname,sno,ssex from student where sname like '刘%'
4.  
5. select sname from student where sname like '欧阳__'
6.  
7. select sname,sno from student where sname like '__阳%'
8.  
9. select sname,sno,ssex from student where sname not like '刘%'
10.  
11. select cno,ccredit from course where cname like 'DB\_design' escape '\'
12.  
13. select * from course where cname like 'DB\_%i__' escape '\'
14.  
15. select sno,cno from sc where grade is null --null 空值
16.  
17. select sno,cno from sc where grade is not null
18.  
19. select sname from student where sdept='CS' and sage<20
20.  
21. select sname,sage from studnet where sdept='CS' or sdept='MA' or sdept='IS'
22.  
23. select sno,grade from sc where cno='3' order by grade desc -- order by 排序
24.  
25. select * from student order by sdept,sage desc --空值最大

--聚集函数

 

1. select count(*) from student -- count() 行数
2.  
3. select count(distinct sno) from sc
4.  
5. select avg(grade) from sc where cno='1' -- avg() 平均数
6.  
7. select max(grade) from sc where cno='1' -- max() 最大值
8.  
9. select sum(Ccredit) from sc,course where sno='200215012' and sc.cno=course.cno -- sum() 总数

--分组

 

1. select cno,count(sno) from sc group by cno
2.  
3. select sno from sc group by sno having count(*) >3 --having 给出选择组的条件

--连接查询

 

1. select student. *,SC.* FROM STUDENT,SC where student.sno=sc.sno
2.  
3. select student.sno,sname,ssex,sage,sdept,cno,grade from student,sc where student.sno =sc.sno
4.  
5. select first.cno,second.cpno from course first,course second fwhere first.cpno=second.cno -- 自身连接
6.  
7. select student.sno,sname,ssex,sage,sdept,cno,grade from student left out join sc in (student.sno=sc.sno)--外连接
8.  
9. --from student left out join sc using (sno)
10.  
11. select student.sno,sname from student,sc where student.sno=sc.sno and sc.cno='2' and sc.grade>90
12.  
13. select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno
14.  
15. select sname from student where sno in (select sno from sc shere con='2')
16.  
17. select sdept from student where sname='刘晨'
18.  
19. select sno.sname,sdept from student where sdept='CS'

--嵌套查询

 

1. select sno,sname,sdept from student where sdept in (select sdept from studnet where sname='刘晨')
2.  
3. select sno,sname,sdept from student where sdept in ('CS')
4.  
5. select s1.sno,s1.sname,s1.sdept from student s1,student s2 where s1.sdept =s2.sdept and s2.sname='刘晨'
6.  
7. select sno,sname from student where sno in (select sno from sc where cno in(select cno from course where cname='信息系统'))
8.  
9. select student.sno,sname from student ,sc,course where student.sno=sc.sno and sc.cno =course.cno and course.cname='信息系统'

--内查询的结果是一个值，因此可以用=代替in

1. select sno,sname,sdept from student where sdpet=(se3lect sdept from studnet where sname='刘晨')
2.  
3. select sno,sname,sdept from student where(select sdept from student where sname='刘晨')=sdept
4.  
5. select sno,cno from sc x where grade >=(select avg(grade) from sc y where y.sno=x.sno)
6.  
7. select avg(grade) from sc y where y.sno='200215121'
8.  
9. select sno,cno from sc x where grade>=88
10.  
11. select sname,sage from student where sage <ANY (SELECT sage from student where sdept='CS') and sdept <>'CS'
12.  
13. select sname,sage from student where sage<(select max(sage) from student where sdept='CS') and sdept <> 'CS'
14.  
15. select sname,sage from student where sage < all (select sage from student where sdept ='CS')
16.  
17. select sname,sage from student where sage<(select min(sage) from student where sdept='CS') and sdept <>'CS'
18.  
19. select sname from student where exists(select * from sc where sno=student.sno and cno='1')
20.  
21. select sname from student where not exists (select * from sc where sno=student.sno and cno='1')
22.  
23. select sno.sname,sdept from student s1 where exists(select * from studetn s2 where s2.sdept=s1.sdept and s2.sname='刘晨')
24.  
25. select sname from student where not exists (select * from course where not exists(select * from sc where sno=student.sno and cno=course.cno))
26.  
27. select distinct sno frome sc scx where not exists (select * from sc scy where scy.sno='200215122' and not exists(select * from sc scz where scz.sno=scx.sno and scz.cno=scy.cno))

集合查询

 

1. select * from student where sdept ='CS' union select * from student where sage<=19 --union并操作
2.  
3. select sno from sc where cno='1' union select sno from sc where sc where cno='2'
4.  
5. select * from student where sdept='cs' intersect select * from student where sage<=19 --intersect 交操作
6.  
7. select * from student where sdept='cs' and sage<=19
8.  
9. select sno from sc where cno='1' intersect select sno from sc where cno='2'
10.  
11. select sno from sc where cno='1' and sno in (select so from sc where cno='2')
12.  
13. select * from student where sdept='cs' except select * from student where sage<='19' --except 差操作
14.  
15. select * from student where sdept ='cs' and sage>19

插入数据

 

1. insert into student(sno,sname,ssex,sdept,sage) values('200215128','陈东','男','IS','18')
2.  
3. insert into student values('200215126','张成敏','男','18','cs')
4.  
5. insert into sc(sno,cno) values('200215128','1')
6.  
7. insert into sc values('200215128','1',null)

更新数据

 

1. create table dept_age(sdept char(15) avg_agea smallint)
2.  
3. insert into dept_age(sdept,avg_age) select sdept,avg(sage) from student group by sdept
4.  
5. update student set sage=22 where sno='200215121'
6.  
7. update student set sage=sage+1
8.  
9. update sc set grade=0 where 'cs'=(select sdept from student where student.sno=sc.sno)
10.  
11. update is_student set sname='刘晨' where sno='200215122'
12.  
13. update student set sname='刘晨' where sno='200212122' and sdept='IS'
14.  
15. delete from student where sno='200215128'
16.  
17. delete from is_student where sno='200215129'
18.  
19. delete from student where sno='200215129' and sdept='IS'
20.  
21. delete from sc
22.  
23. delete fro sc where 'cs'=(select sdept from student where student.sno=sc.sno)

删除操作

 

1. delete from Student where Sno=’200215128’
2.  
3. delete from SC
4.  
5. delete from SC where ‘cs’ = (select Sdept from Student where Student.Sno=SC.Sno)

创建视图

 

1. create view is_student
2. as
3. select sno,sname,sage from student where sdpet='IS'
4.  
5. create view is_student
6.  
7. as
8.  
9. select sno,sname,sage from student where sdept='IS' with check option
10.  
11. create view is_s1(sno,sname,grade)
12.  
13. as
14.  
15. select student.sno,sname,grade from student,sc where student,sc where sdept=='IS' and student.sno=sc.sno and sc.cno='1'
16.  
17. create view is_s2
18.  
19. as
20.  
21. select sno,sname,grade from is_s1 where grade>=90
22.  
23. create view bt_s(sno,sname,sbirth)
24.  
25. as
26.  
27. select sno,sname,2004-sage from student
28.  
29. create view s_g(sno,gavg)
30.  
31. as
32.  
33. select sno,avg(grade) from sc group by sno
34.  
35. create view f_student(f_sno,name,sex,age,dept)
36.  
37. as
38.  
39. select * from student where ssex='女'

删除视图

 

1. drop view is_s1 cascade
2.  
3.  
4.  
5. select sno,sage from is_student where sage<20
6.  
7. select sno,sage from student where sdept='IS' and sage<20
8.  
9. select is_sutdent.sno,sname from is_student,sc where is_student.sno=sc.sno and sc.cno='1'
10.  
11. select * from s_g where gavg>=90

分组

 

1. select sno,avg(grade) from sc group by sno
2.  
3. select sno,avg(grade) from sc where avg(grade)>=90 group by sno
4.  
5. select sno,avg(grade) from sc group by sno having avg(grade) >=90

存储过程

 

1. CREATE PROCEDURE Insert_pass
2.  
3. @pass NVARCHAR(50)
4.  
5. AS
6.  
7. BEGIN
8.  
9. DECLARE @count INT
10.  
11.  
12.  
13. SELECT @count = (SELECT Count(*)
14.  
15. FROM list
16.  
17. WHERE pass = @pass)
18.  
19.  
20.  
21. IF @count = 0
22.  
23. BEGIN
24.  
25. INSERT INTO list
26.  
27.  (pass)
28.  
29. VALUES (@pass)
30.  
31. END
32.  
33. END

执行存储过程

 

1. call procedure Insert_pass(2011)

删除存储过程

 

1. drop procedure Insert_pass()

触发器

 

1. CREATE TRIGGER insert_table1
2.  
3. ON table1
4.  
5. FOR INSERT, DELETE
6.  
7. AS
8.  
9. BEGIN
10.  
11. UPDATE table2
12.  
13. SET count = (SELECT Count(*)
14.  
15. FROM table1)
16.  
17. WHERE id = 1;
18.  
19. END;

游标：游标是系统为用户开设的一个数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个名字。用户可以通过游标逐一获取记录，并赋值给主变量，交由主语言进一步处理。