數(shù)據(jù)庫(kù)操作練習(xí)

1、/*實(shí)驗(yàn)數(shù)據(jù)庫(kù)操作*/-創(chuàng)建數(shù)據(jù)庫(kù)create database testON(NAME = test_data, FILENAME = 'D:testtest_data.mdf',SIZE = 10MB,MAXSIZE = UNLIMITED,FILEGROWTH = 1MB )LOG ON(NAME = test_log, FILENAME = 'D:testtest_log.ldf',SIZE = 1MB,MAXSIZE = 5MB,FILEGROWTH = 10% )-查看數(shù)據(jù)庫(kù)屬性exec sp_helpdb test-刪除數(shù)據(jù)庫(kù)drop databa

2、se test/*實(shí)驗(yàn)表操作*/USE testCREATE TABLE student-學(xué)生表(st_id nVarChar(9) primary key NOT NULL,-學(xué)生學(xué)號(hào)st_nm nVarChar(8) NOT NULL,-學(xué)生姓名st_sex nVarChar(2) NULL,-學(xué)生性別st_birth datetime NULL,-出生日期st_score int NULL,-入學(xué)成績(jī)st_date datetime NULL,-入學(xué)日期st_from nVarChar(20) NULL,-學(xué)生來源st_dpid nVarChar(2) NULL,-所在系編號(hào)st_mnt

3、 tinyint NULL-學(xué)生職務(wù))CREATE TABLE couse-課程表(cs_id nVarChar(4) primary key NOT NULL ,-課程編號(hào)cs_nm nVarChar(20) NOT NULL ,-課程名稱cs_tm int NULL ,-課程學(xué)時(shí)cs_sc int NULL-課程學(xué)分)insert into couse values('C002','高等數(shù)學(xué)','60','0')insert into couse values('1001','大學(xué)英語(yǔ)',

4、9;60','3')insert into couse values('1002','數(shù)據(jù)結(jié)構(gòu)','56','2')CREATE TABLE slt_couse-選課表(cs_id nVarChar(4) constraint fk_cs_id foreign key(cs_id) references couse(cs_id) NOT NULL ,-課程編號(hào)st_id nVarChar(9) constraint fk_st_id foreign key(st_id) references student

5、(st_id) NOT NULL ,-學(xué)生編號(hào)score int NULL ,-課程成績(jī)sltdate datetime NULL,-選課日期PRIMARY KEY(cs_id,st_id)CREATE TABLE dept-院系表(dp_id nVarChar(2) NOT NULL ,-系編號(hào)dp_nm nVarChar(20)NOT NULL ,-院系名稱dp_drtnVarChar(8) NULL ,-院系主任dp_telnVarChar(12) NULL-聯(lián)系電話)-操作.5：為“dept”表添加“dp_count”列（數(shù)據(jù)類型為nvarchar，長(zhǎng)度為，允許為空）ALTER TAB

6、LE dept ADD dp_count nvarchar(3) NULL-操作.6：修改“dept”表的“dp_count”列數(shù)據(jù)類型為intALTER TABLE dept ALTER COLUMN dp_count INT-操作.7：刪除“dept”表的“dp_count”列ALTER TABLE dept DROP COLUMN dp_count-操作.8：刪除“dept”表DROP TABLE dept INSERT INTO student VALUES('070201002','張三','男','1994-5-11'

7、,'95','2013-9-1','湖南長(zhǎng)沙','5','1')INSERT INTO couse VALUES('C001','數(shù)據(jù)庫(kù)','72','4')insert into slt_couse values('C002','070201002','90','2014-1-1')INSERT INTO slt_couse VALUES('C001','0702

8、01002','85','2013-9-13')INSERT INTO dept VALUES('5','軟件學(xué)院','李靜云',)/*實(shí)驗(yàn)數(shù)據(jù)完整性*/-操作.1：將student表中的st_sex列屬性更改為NOT NULLalter table student alter column st_sex nVarChar(2) NOT NULL-操作.2：將student表中的st_from列默認(rèn)值設(shè)置為“陜西省”alter table student add constraint default_fro

9、m default '陜西省' for st_from/*操作.3：創(chuàng)建默認(rèn)值對(duì)象df_today為當(dāng)前日期，并將其綁定到slt_couse表中的sltdate列，然后取消綁定，最后刪除默認(rèn)值對(duì)象df_today。*/create default df_today as getdate()exec sp_bindefault df_today, 'slt_couse.sltdate' exec sp_unbindefault 'slt_couse.sltdate' drop default df_today-操作.4：將slt_couse表中的s

10、core列的檢查約束設(shè)置為>=0且<=100alter table slt_couse add constraint chk_score check(score>=0 and score<=100)/*操作.5：創(chuàng)建規(guī)則約束對(duì)象rl_sex，用于檢查性別的取值僅限于“男”和“女”，并將其綁定到student表中的st_sex列，然后取消綁定，最后刪除規(guī)則約束對(duì)象rl_sex*/create rule rl_sex as chksex in('男','女')exec sp_bindrule rl_sex, 'student.st_s

11、ex'exec sp_unbindrule 'student.st_sex'drop rule rl_sex-操作.6：將dept表中的dp_id列設(shè)置為主鍵alter table dept add constraint pk_dp_id primary key(dp_id)-操作.7：將dept表中的dp_nm列設(shè)置為唯一性約束alter table dept add constraint un_dp_nm unique(dp_nm)-操作.8：向slt_couse表中添加標(biāo)識(shí)列id，第行默認(rèn)值為，相鄰兩個(gè)標(biāo)識(shí)列間的增量為ALTER TABLE slt_couse A

12、DD id INT IDENTITY(1,1) NOT NULL-操作.9：被參照表為dept，參照表為studentalter table student add constraint fk_id foreign key(st_dpid) REFERENCES dept(dp_id)/*實(shí)驗(yàn)數(shù)據(jù)更新*/-操作.1：向dept表插入一條記錄，系號(hào)，系名自動(dòng)控制系，系主任為李其余，電話INSERT INTO dept VALUES('11', '自動(dòng)控制系', '李其余', '81234567')/*操作.2：向student表插入

13、一條記錄，學(xué)號(hào)，姓名為王小五，性別為男，出生日期為年月日，系號(hào)為，其余字段為NULL或默認(rèn)值*/INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)VALUES ('070201001', '王小五', '男', '1990.9.9', '11' )INSERT INTO student(st_id, st_nm, st_sex, st_birth, st_dpid)VALUES ('070201003', '張倩'

14、, '女', '1994.9.9', '11' )-操作.3：向couse表插入一條記錄，課程號(hào)，課程名為操作系統(tǒng)，其余字段為NULL或默認(rèn)值INSERT INTO couse(cs_id, cs_nm) VALUES ('1234', '操作系統(tǒng)')-操作.4：向slt_couse表插入一條記錄，課程號(hào)，學(xué)名，其余字段為NULL或默認(rèn)值INSERT INTO slt_couse(cs_id,st_id) VALUES ('1234', '070201001')-操作.5：修改stu

15、dent表記錄，將王小五的入學(xué)成績(jī)改為update student set st_score='85' where st_nm='王小五'-操作.6：修改couse表記錄，將所有記錄的學(xué)分改為，學(xué)時(shí)改為update couse set cs_tm='64',cs_sc='4'-操作.7：修改slt_couse表記錄，將課程號(hào)為，學(xué)名為的記錄的成績(jī)改為update slt_couse set score='77'where cs_id='1234' and st_id='070201001&#

16、39;-操作.8：刪除slt_couse表記錄，將課程號(hào)為，學(xué)名為的記錄刪除delete from slt_couse where cs_id='1234' and st_id='070201001'-操作.9：刪除couse表記錄，將課程號(hào)為的記錄刪除delete from couse where cs_id='1234'/*實(shí)驗(yàn)數(shù)據(jù)查詢（）簡(jiǎn)單查詢*/-操作.1：查詢所有系的信息select * from dept-操作.2：查詢所有的課程號(hào)與課程名稱select cs_id,cs_nm from couse-操作.3：在查詢student表

17、時(shí)使用列表達(dá)式：入學(xué)成績(jī)+400select st_id,st_nm,st_score,st_score+400 as '新成績(jī)' from student-操作.4：使用AS關(guān)鍵字為dept表中屬性指定列名：系號(hào)、系名、系主任、聯(lián)系電話select dp_id as '系號(hào)',dp_nm as '系名',dp_drt as '系主任',dp_tel as '聯(lián)系電話' from dept-操作.5：使用"="號(hào)為couse表中屬性指定列名：課程號(hào)、課程名、學(xué)時(shí)(=cs_sc*16)、學(xué)分sel

18、ect '課程號(hào)'=cs_id,'課程名'=cs_nm,'學(xué)時(shí)'=cs_tm*16,'學(xué)分'=cs_scfrom couse-操作.6：查詢dept表的系號(hào)、系名和系主任，向查詢結(jié)果中插入說明列：系號(hào)、系名和系主任select '系號(hào)：',dp_id,'系名：',dp_nm,'系主任：',dp_drtfrom dept-操作.7：顯示所有學(xué)生的學(xué)號(hào)、姓名、性別和入學(xué)年份select st_id,st_nm,st_sex,datepart(YY,st_date) as '入學(xué)年

19、份' from student-操作.8：顯示所有學(xué)生學(xué)號(hào)、姓名、性別和班級(jí)(學(xué)號(hào)前位)select st_id,st_nm,st_sex,LEFT(st_id,6) as 'class' from student-(7)消除查詢結(jié)果中的重復(fù)項(xiàng)-操作.9：顯示所有學(xué)生班級(jí)select distinct LEFT(st_id,6) as 'class' from student-操作.10：顯示前條學(xué)生記錄信息select top 5 * from student-操作.11：顯示前%條學(xué)生記錄信息select top 25 percent * from

20、student-操作.12：顯示前n條學(xué)生記錄信息，n為局部變量declare n intset n = 5 select top (n)* from student/*實(shí)驗(yàn)數(shù)據(jù)查詢（）條件查詢*/*1使用關(guān)系表達(dá)式表示查詢條件*/-操作.1：查詢dept表中系號(hào)為的院系信息select * from dept where dp_id='11'-操作.2：查詢student表中系的學(xué)生學(xué)號(hào)、姓名、性別和所在系編號(hào)select st_id,st_nm,st_sex,st_dpid from student where st_dpid='11'-操作.3：查詢stu

21、dent表中年及以后入學(xué)的學(xué)生信息select * from student where datepart(yy,st_date)>=2008-操作.4：在查詢student表班學(xué)生的學(xué)號(hào)、姓名、性別和入學(xué)成績(jī)-測(cè)試數(shù)據(jù)INSERT INTO student VALUES('080808001','李四','男','1993-5-11','95','2012-9-1','湖南長(zhǎng)沙','5','2')select st_id,st_nm,st_se

22、x,st_score from student where LEFT(st_id,6)='080808'/*2使用邏輯表達(dá)式表示查詢條件*/-操作.5：查詢student表中非系的學(xué)生信息select * from student where not st_dpid='11'-操作.6：查詢選修了號(hào)課程且成績(jī)?cè)谝韵碌膶W(xué)生學(xué)號(hào)select st_id from slt_couse where cs_id='1002' and score<'60'-操作.7：查詢年入學(xué)的系所有男生信息select * from student

23、where DATEPART(YY,st_date)=2007 and st_dpid='11' and st_sex='男'-操作.8：查詢系和系的學(xué)生信息select * from student where st_dpid='11' or st_dpid='12'-操作.9：查詢系和系所有年入學(xué)的學(xué)生信息select * from student where (st_dpid='11' or st_dpid='12')and DATEPART(YY,st_date)=2007/*使用LIKE關(guān)

24、鍵字進(jìn)行模糊查詢*/-操作.10：查詢所有“計(jì)算機(jī)”開頭的課程信息-測(cè)試數(shù)據(jù)insert into couse values('C003','計(jì)算機(jī)基礎(chǔ)','72','3')select * from couse where cs_nm like '計(jì)算機(jī)%'-操作.11：查詢所有由三個(gè)字組成的“王”姓學(xué)生信息select * from student where st_nm like '王_'-操作.12：查詢所有課程名中包含“信息”的課程信息-測(cè)試數(shù)據(jù)insert into couse valu

25、es('C004','信息化教學(xué)研究','70','3')select * from couse where cs_nm like '%信息%'-操作.13：查詢學(xué)生姓名介于王姓到張姓的信息select * from student where st_nm like '王&nbhy;張%'/*使用BetweenAnd關(guān)鍵字進(jìn)行查詢*/-操作.14：查詢?cè)?7.1到.6.30之間出生的學(xué)生信息-測(cè)試數(shù)據(jù)insert into student values('090901001'

26、,'林夕','女','1990.5.3','55','2009-9-1','湖南益陽(yáng)','5','2')select st_id,st_nm,st_birth,st_sex,st_from from student where st_birth between '1989.7.1' and '1990.6.30'-操作.15：查詢選修了號(hào)課程且成績(jī)?cè)诘街g的學(xué)生選課信息-測(cè)試數(shù)據(jù)insert into slt_couse values(

27、'1001','090901001','76','2012-11-2')select * from slt_couse where (score between '60' and '80')and cs_id='1001'/*使用IN關(guān)鍵字進(jìn)行查詢*/-操作.16：查詢系、系、系的學(xué)生信息select * from student where st_dpid in('11','12','13')-操作.17：查詢所有張，王，李，趙姓的學(xué)

28、生的學(xué)號(hào)、姓名、性別select st_id,st_nm,st_sex from student where LEFT(st_nm,1) in('張','王','李','趙')/*使用NOT NULL關(guān)鍵字進(jìn)行查詢*/-操作.18：查詢所有生源為非空的學(xué)生信息select *from student where st_from is not null-操作.19：查詢選修了號(hào)課程且成績(jī)?yōu)榭盏膶W(xué)生選課信息select * from slt_couse where cs_id='1001' and score is n

29、ull/*實(shí)驗(yàn)數(shù)據(jù)查詢（）查詢排序與查詢結(jié)果存儲(chǔ)*/-操作.1：查詢課程信息，按課程名稱降序排序select * from couse order by cs_nm desc-操作.2：查詢選修了號(hào)課程成績(jī)非空的學(xué)生學(xué)號(hào)和成績(jī)，并按成績(jī)降序排序select st_id,score from slt_couse where cs_id='1001' and score is not null order by score desc-操作.3：查詢系學(xué)生學(xué)號(hào)、姓名和年齡，按年齡升序排序select st_id,st_nm,datediff(yy,st_birth,GETDATE()

30、 as 'age' from student where st_dpid='11' order by age asc-操作.4：查詢學(xué)生信息，按姓名升序排序，再按系號(hào)降序排序select * from student order by st_nm asc, st_dpid desc-操作.5：創(chuàng)建學(xué)生表副本student01，僅保留學(xué)生學(xué)號(hào)、姓名和性別select st_id,st_nm,st_sex into student01 from studentselect * from student01-操作.6：查詢陜西籍學(xué)生，將結(jié)果保存在新表st_shanxi

31、select * into st_shanxi from studentwhere st_from='陜西省'-操作.7：查詢選修了號(hào)課程學(xué)生的選課信息，按學(xué)號(hào)升序排序，將結(jié)果保存在新表slt1001select * into slt1001 from slt_couse where cs_id='1001' order by st_id asc-操作.8：用局部變量stage保存學(xué)生張三的年齡declare stage intselect stage =DATEDIFF(YY,st_birth,GETDATE() from studentwhere st_nm

32、='張三'-操作.9：用局部變量name和stscore保存班按學(xué)號(hào)排序后最后一個(gè)學(xué)生的姓名和入學(xué)成績(jī)declare name nVarChar(8), stscore int select name=st_nm,stscore=st_score from student where LEFT(st_id,6) ='070101' order by st_id /*實(shí)驗(yàn)數(shù)據(jù)查詢（）查詢統(tǒng)計(jì)與匯總*/ -操作.1：查詢課程總數(shù)select COUNT(*) as '課程總數(shù)' from couse-操作.2：查詢選修號(hào)課程的學(xué)生人數(shù)select C

33、OUNT(st_id) as '學(xué)生人數(shù)' from slt_couse where cs_id='1001'-操作.3：查詢被選修課程的數(shù)量select COUNT(cs_id) from slt_couse-操作.4：查詢選修班學(xué)生的平均入學(xué)成績(jī)select AVG(st_score) as '平均入學(xué)成績(jī)' from student where LEFT(st_id,6)='070101'-操作.5：查詢號(hào)學(xué)生選修課程的數(shù)量、總分以及平均分select COUNT(cs_id) as '課程數(shù)量',SUM(s

34、core) as '總分',AVG(score) as '平均分' from slt_couse where st_id='070101001'-操作.6：查詢選修號(hào)課程的學(xué)生人數(shù)、最高分、最低分和平均分select COUNT(*) as '學(xué)生人數(shù)',MAX(score) as '最高分', MIN(score) as '最低分',AVG(score) as '平均分' from slt_couse where cs_id='1001'-操作.7：求各個(gè)課程號(hào)和相

35、應(yīng)的選課人數(shù)select cs_id,COUNT(cs_id) from slt_couse group by cs_id-操作.8：統(tǒng)計(jì)各班人數(shù)select LEFT(st_id,6) as '班級(jí)',COUNT(st_id) as '人數(shù)' from studentgroup by LEFT(st_id,6) -操作.9：依次按班級(jí)、系號(hào)對(duì)學(xué)生進(jìn)行分類統(tǒng)計(jì)人數(shù)、入學(xué)平均分select LEFT(st_id,6) as '班級(jí)',st_dpid as '系號(hào)' ,COUNT(st_id) as '人數(shù)',AVG(

36、st_score) as '入學(xué)平均分' from student group by LEFT(st_id,6),st_dpid-操作.10：查詢選修了均分在以上的課程號(hào)及均分select cs_id as '課程編號(hào)',AVG(score) as '均分' from slt_cousegroup by cs_id having AVG(score)>75-操作.11：查詢選修了門以上課程的學(xué)生學(xué)號(hào)select st_id from slt_couse group by st_id having COUNT(*)>2-操作.12：明細(xì)

37、匯總年齡<20的學(xué)生，并匯總學(xué)生數(shù)量、平均年齡select st_nm,DATEDIFF(YY,st_birth,GETDATE() as 'age' from student where DATEDIFF(YY,st_birth,GETDATE()<20 compute count(st_nm),AVG(DATEDIFF(YY,st_birth,GETDATE()-操作.13：按班級(jí)明細(xì)匯總成績(jī)<85分的學(xué)生，匯總學(xué)生數(shù)、均分select st_nm,LEFT(st_id,6) as '班級(jí)',st_score from studentwhe

38、re st_score<85 order by 班級(jí) compute count(st_nm),avg(st_score) by 班級(jí)/*實(shí)驗(yàn)數(shù)據(jù)查詢（）連接查詢*/-操作.1：用SQL Server形式連接查詢學(xué)生學(xué)號(hào)、姓名、性別及其所選課程編號(hào)select a.st_id,a.st_nm,a.st_sex,b.cs_id from student a,slt_couse bwhere a.st_id=b.st_id -操作.2：用ANSI形式連接查詢學(xué)生學(xué)號(hào)、姓名、性別及其所選課程編號(hào)select a.st_id,a.st_nm,a.st_sex,b.cs_id from stude

39、nt a join slt_couse bon a.st_id=b.st_id -操作.3：用SQL Server形式連接查詢學(xué)生學(xué)號(hào)、姓名及其所選課程名稱及成績(jī)select a.st_id,a.st_nm,b.cs_nm,c.score from student a,couse b,slt_couse cwhere a.st_id=c.st_id and b.cs_id=c.cs_id-操作.4：用ANSI形式連接查詢學(xué)生學(xué)號(hào)、姓名及其所選課程名稱及成績(jī)select a.st_id,a.st_nm,b.cs_nm,c.score from student a join slt_couse c

40、on a.st_id=c.st_id join couse b on b.cs_id=c.cs_id-操作.5：查詢選修了課程的學(xué)生學(xué)號(hào)、姓名及課程成績(jī)select a.st_id,a.st_nm,b.score from student a,slt_couse bwhere a.st_id=b.st_idand b.cs_id='1002'-操作.6：查詢選修了“數(shù)據(jù)結(jié)構(gòu)”課程的學(xué)生學(xué)號(hào)、姓名及課程成績(jī)select a.st_id,a.st_nm,c.score from student a,couse b,slt_couse cwhere a.st_id=c.st_id a

41、nd b.cs_id=c.cs_id and b.cs_nm='數(shù)據(jù)結(jié)構(gòu)'-操作.7：用左外連接查詢沒有選修任何課程的學(xué)生學(xué)號(hào)、姓名select a.st_id,a.st_nm from student a left outer join slt_couse b on a.st_id=b.st_id where b.cs_id is null-操作.8：用右外連接查詢選修各個(gè)課程的學(xué)生學(xué)號(hào)select a.st_id,b.cs_id from slt_couse a right outer join couse b on a.cs_id=b.cs_id/*實(shí)驗(yàn)數(shù)據(jù)查詢（）子查詢

42、*/-操作.1：用子查詢對(duì)各班人數(shù)進(jìn)行查詢（新增列）select distinct LEFT(a.st_id,6) as '班級(jí)','人數(shù)'=(select COUNT(st_id)from student b where LEFT(a.st_id,6)=LEFT(b.st_id,6) from student a -操作.2：用子查詢對(duì)各課程的選課人數(shù)進(jìn)行查詢（新增列）select distinct a.cs_id ,'人數(shù)'=(select COUNT(st_id)from slt_couse b where a.cs_id=b.cs_id)

43、 from slt_couse a -操作.3：查詢選修了課程成績(jī)不及格的學(xué)生的學(xué)號(hào)、姓名和性別，并按姓名升序排序-通過子查詢實(shí)現(xiàn)：使用IN關(guān)鍵字select st_id,st_nm,st_sex from student where st_id in(select st_id from slt_couse where cs_id='1002' and score<60)order by st_nm asc-通過子查詢實(shí)現(xiàn)：使用比較運(yùn)算符select st_id,st_nm,st_sex from student a where(select score from slt

44、_couse b where a.st_id=b.st_id and cs_id='1002')<60-操作.4：查詢“東方紅”同學(xué)所在班的學(xué)生信息，并按姓名降序排序-通過子查詢實(shí)現(xiàn)：IN運(yùn)算符select st_id,st_nm,st_sex from student where LEFT(st_id,6) in(selectLEFT(st_id,6) from student where st_nm='東方紅')order by st_nm desc-通過自連接查詢實(shí)現(xiàn)：JOINselect a1.st_id,a1.st_nm,a1.st_sex from student a1 join student a2 onLEFT(a1.st_id,6)=LEFT(a2.st_id,6) where a2.st_nm='東方紅' order by st_nm desc-操作.5：查詢其它班比班某一學(xué)生的號(hào)課程成績(jī)高的學(xué)生信息（ANY/ALL）select * from slt_couse where score > any(select score from slt_couse where cs_id='1002' and