DATABASE基本知识点

1、总编号分类号书名作者出版单位单价445501TP3/12数据库导论王强科学出版社17.90445502TP3/12数据库导论王强科学出版社17.90445503TP3/12数据库导论王强科学出版社17.90332211TP5/10计算机基础李伟高等教育出版社18.00112266TP3/12FoxBASE张三电子工业出版社23.60665544TS7/21高等数学刘明高等教育出版社20.00114455TR9/12线性代数孙业北京大学出版社20.80113388TR7/90大学英语胡玲清华大学出版社12.50446601TP4/13数据库基础马凌云人民邮电出版社22.50446602TP4/1

2、3数据库基础马凌云人民邮电出版社22.50446603TP4/13数据库基础马凌云人民邮电出版社22.50449901TP4/14FoxPro大全周虹科学出版社32.70449902TP4/14FoxPro大全周虹科学出版社32.70118801TP4/15计算机网络黄力钧高等教育出版社21.80118802TP4/15计算机网络黄力钧高等教育出版社21.80读者：借书证号单位姓名性别职称地址111信息系王维利女教授1 号楼 424112财会系李立男副教授2 号楼 316113经济系张三男讲师3 号楼 105114信息系周华发男讲师1 号楼 316115信息系赵正义男工程师1 号楼 22411

3、6信息系李明男副教授1 号楼 318117计算机系李小峰男助教1 号楼 214118计算机系许鹏飞男助工1 号楼 216119计算机系刘大龙男教授1 号楼 318120国际贸易李雪男副教授4 号楼 506121国际贸易李爽女讲师4 号楼 510122国际贸易王纯女讲师4 号楼 512123财会系沈小霞女助教2 号楼 202124财会系朱海男讲师2 号楼 210125财会系马英明男副教授2 号楼 212借阅：借书证号总编号借书日期1124455011997-3-191253322111997-2-121114455031997-8-211121122661997-3-14114665544199

4、7-10-211201144551997-11-21201188011997-10-181194466031997-12-121124499011997-10-231154499021997-8-211181188011997-9-10create TABLE图书 (/*创建表 “图书 ” */总编号 CHAR(6) primary key,分类号 CHAR(6),书名 VARCHAR(20),作者 VARCHAR(6),出版单位varchaR(20),单价 numeric(4,2);/*ALTER TABLE图书 ALTER COLUMN单价 NUMERIC(6,2);用于更改数据类型/*D

5、ELETE from图书 restrict（有限制） /caseade（无限制） */*SELECT * FROM图书 */*/create TABLE读者 (借书证号CHAR(3) primary key,单位 varCHAR(10),姓名 VARCHAR(6),性别 CHAR(2),职称 varchaR(6),地址 varchar(20);/*创建表 “读者 ” */create TABLE借阅 (/*创建表 “借阅 ” */借书证号CHAR(3),总编号 CHAR(6),借书日期DATE);/*找出姓李的读者姓名和所在单位。*/Select读者. 姓名 , 单位From读者Where读者

6、 . 姓名 like '李 %'/* 列出图书库中所有藏书的书名及出版单位。*/Select distinct "书名 "," 出版单位 "From图书/* 查找高等教育出版社的所有图书及单价，结果按单价降序排序。*/Select Distinct "书名 "," 单价 "From图书Where "出版单位 "='高等教育出版社 'Order by "单价 " desc/* 查找价格介于元和元之间的图书种类，结果按出版单位和单价升序排序。*/S

7、elect distinct "分类号 ","出版单位 ","单价 "From图书Where " 单价 " between 10 and 20Order by "出版单位 ","单价 "/* 查找书名以计算机打头的所有图书和作者。*/Select distinct "分类号 ","书名 "," 作者 "From图书Where " 书名 " like '计算机 %'/* 检索同时借阅

8、了总编号为和两本书的借书证号。*/Select distinct "借书证号 "From借阅Where "借书证号 "=(select "借书证号 " from借阅 where "总编号 "='112266') and" 总编号 "='449901'/* 查找所有借了书的读者的姓名及所在单位。*/Select distinct "姓名 ","单位 "From借阅, 读者Where借阅 . 借书证号 =读者 . 借书证号/*

9、找出李某所借图书的所有图书的书名及借书日期。*/Select "书名 ","借书日期 ","姓名 "From 图书 , 借阅 , 读者Where借阅 . 总编号 =图书 . 总编号 and读者 . 借书证号 =借阅 . 借书证号Group by读者 . 姓名 having "姓名 "='李 %'/* 查询年月以后借书的读者借书证号、姓名和单位。*/Select distinct读者.借书证号 , 姓名, 单位From读者, 借阅Where datepart(year,借书日期 )>=1997

10、and datepart(month,借书日期 )>=10and读者 . 借书证号 =借阅 . 借书证号select distinct读者 . 借书证号 , 姓名 , 单位 from读者 , 借阅 where读者 . 借书证号 =借阅 . 借书证号 and借书日期 in(select借书日期 from借阅 where datepart(year,借书日期)>=1997 and datepart(month,借书日期 )>=10);/* 找出借阅了 FoxPro 大全一书的借书证号。 */Select distinct借阅 . 借书证号From借阅Where借阅 . 总编号 i

11、n(select图书 . 总编号from图书where "书名 "='FoxPro大全 ')/* 找出与赵正义在同一天借书的读者姓名、所在单位及借书日期。*/Select "姓名 ","单位 ","借书日期 "From读者, 借阅Where "借书日期 " in(select "借书日期 "From借阅Where借书证号 =(select "借书证号 "from "读者 "where "姓名 "=&#

12、39; 赵正义 ')/* 查询年月以后没有借书的读者借书证号、姓名及单位。*/Select distinct借书证号 , 姓名 , 单位From读者exceptSelect distinct读者.借书证号 , 姓名, 单位From读者, 借阅WhereDATEDIFF(M,1997-7-1,借书日期 )>=0 and借阅 . 借书证号 = 读者 . 借书证号 and 借阅 .借书证号 =读者 . 借书证号 ;datepart(year,借书日期 )>=1997 and datepart(month,借书日期 )>=7DATEDIFF(M,1997-7-1,借书日期 )

13、>=0 and借阅 . 借书证号 = 读者 . 借书证号或者select distinct借书证号 , 姓名 , 单位 from读者 where借书证号 not in(select distinct读者 .借书证号from读者 ,借阅where读者 .借书证号=借阅 .借书证号and借书日期in(select借书日期from借阅where datepart(year,借书日期)>=1997 and datepart(month,借书日期)>=7);/*求科学出版社图书的最高单价、最低单价、平均单价。Select max("单价 ") as最高单价 , mi

14、n("单价 ") as*/最低单价, avg("单价 ") as平均单价From图书Where "出版单位"='科学出版社'/*求信息系当前借阅图书的读者人次数。Select count(读者 . 借书证号 ) as*/借阅人次From读者Where "单位 "='信息系 '/*求出各个出版社图书的最高价格、最低价格和册数。Select "出版单位 " as出版单位 ,max("单价 ") as*/最高价格, min("单价 &quo

15、t;) as最低价格,count("书名 ") as书名From 图书Group by "出版单位 "/* 找出藏书中各个出版单位的册数、价值总额。*/Select "出版单位 " as出版单位 ,count("书名 ") as书名 ,sum("单价 ") as单价From 图书Group by "出版单位 "书上题目S表（供应商）s1精益20天津s2盛锡10北京s3东方红30北京s4丰泰盛20天津s5为民30上海P表（零件）p1红12p2绿17p3螺丝刀蓝14p4螺丝刀红1

16、4p5凸轮蓝40p6齿轮红30J 表（工程项目）j1三建北京j2一汽长春j3弹簧厂天津j4造船厂天津j5机车厂唐山j6无线电厂常州j7半导体厂南京SPJ 表（供应情况）s1p1j1200s1p1j3100s1p1j4700s1p2j2100s2p3j1400s2p3j2200s2p3j4500s2p3j5400s2p5j1400s2p5j2100s3p1j1200s3p3j1200s4p5j1100s4p6j3300s4p6j4200S5p2j4100s5p3j1200s5p6j2200S5p6j4500/*建表 */create table s(sno char(2) primary key

17、,sname varchar(6) unique,status char(2),city char(4);create table p(pno char(2) primary key,pname varchar(6),color char(2),weight smallint);create table j(jno char(2) primary key,jname varchar(10),city char(4);create table spj(sno char(2),pno char(2),jno char(2),qty intforeign key(sno) references s(

18、sno),foreign key(pno) references p(pno),foreign key(jno) references j(jno)/*供应工程 j1 零件的供应商号码sno*/select distinct spj.snofrom spjwhere spj.jno='j1'/*供应工程 j1 零件 p1 的供应商号码sno*/select distinct spj.snofrom spjwhere spj.jno='j1' and spj.pno='p1'/*供应工程 j1 零件为红色的供应商号码sno*/select dist

19、inct spj.snofrom spjwhere spj.jno='j1' and spj.pno in(select p.pno from p where p.color='红')/*求没有使用天津供应商生产的红色零件的工程号select distinct jnofrom spjexceptselect spj.jnofrom spj,p,swheres.city='天津 'andp.pnoin(selects.sno=spj.sno and spj.pno=p.pnojno*/pnofromp wherecolor='红')

20、and/*求至少用了供应商s1 所供应的全部零件的工程号jno*/select distinct jnofrom spj xwhere not exists(select * from spj y where y.sno='s1' and notexists(select * from spj z where z.jno=x.jno andz.sno=y.sno);select distinct jnofrom spjwhere (select * from spj y where y.sno='s1')/*找出所有供应商的姓名和所在城市*/select sna

21、me,cityfrom s/*找出所有零件的名称、颜色、重量*/select pname,color,weightfrom p/*找出使用供应商s1 所供应零件的工程号吗*/select distinct jnofrom spjwhere sno='s1'/*找出工程项目 j2 使用的各种零件的名称及其数量*/select distinct pname,COUNT(p.pno)from spj,pwhere spj.jno='j2' and p.pno=spj.pnogroup by pname/*找出上海厂商供应的所有零件号码*/select distinct

22、 spj.pnofrom spjwhere spj.sno=(select s.sno from s where city='上海 ')/*找出使用上海产的零件的工程名称*/select distinct jnamefrom j,spj,swhere s.city='上海 ' and j.jno=spj.jno and s.sno=spj.sno/*找出没有使用天津产的零件的工程号码*/select jnofrom jexceptselect j.jnofrom j,spj,swhere s.city='天津 ' and s.sno=spj.sn

23、o and j.jno=spj.jno/*把全部红色零件的颜色改成蓝色*/update pset color='蓝'where color='红 '/*由 s5 供给 j4 的零件 p6 改为由 s3 供应 */update spjset sno='s3'where sno='s5' and jno='j4' and pno='p6'/*从供应商关系中删除s2 的记录并从供应情况关系中删除相应记录*/deletefrom swhere sno='s2'deletefrom spjwh

24、ere sno='s2'/*请将 (s2,j6,p4,200)插入供应情况关系*/insert into spj values('s2','p4','j6','200')上课题目/*以下查询调用 db_stu*/use db_stu/*以下查询整个表*/SELECT *FROM t_base/*以下查询表的 s_name ， s_num*/SELECT s_name,s_numFROM t_base/*以下查询所有 张三 的信息 */SELECT *FROM t_base where s_name='张三

25、'/*以下查询所有 李四 的信息 */SELECT *FROM t_base where s_name='李四 '/*以下查询 s_num 为 的信息 */FROM t_base where s_num=1000000003/*以下查询 c_num 为 的信息 */SELECT *FROM t_base where c_num=0002/*以下查询 s_name 为 张三 且 c_num 为 的信息 */SELECT *FROM t_base where s_name='张三 ' and c_num=0001/*以下查询所有 张三 或 李四 的信息 */

26、SELECT *FROM t_base where s_name='张三 ' or s_name='李四 '/*以下查询所有 s_name为 张三 或 李四 的且 c_num 为 */SELECT *FROM t_base where (s_name='张三 ' or s_name='李四 ') and c_num=0001/*以下查询所有 c_num 为 的且 s_name 为张三 或 李四 */SELECT *FROM t_base where c_num=0001 and (s_name='张三 ' or s

27、_name='李四 ')/*以下查询表的首行 */SELECT top 1 *FROM t_base/*以下查询表的首两行 */SELECT top 2 *FROM t_base/*以下查询按 id 从小到大排列 */SELECT *FROM t_baseorder by id/*以下查询按 s_name 从小到大排列 */SELECT *FROM t_baseorder by s_name/*以下查询按 s_name 从大到小排列 */SELECT *FROM t_baseorder by s_name desc/*以下查询按 id 从大到小排列 */SELECT *FROM

28、 t_baseorder by id desc/*以下查询按所有字段逐项查询*/SELECT id,s_name,s_num,c_num,s_add ,mobile,email,t_meno FROM t_base/*以下查询按除 t_meno 外所有字段逐项查询*/SELECT id,s_name,s_num,c_num,s_add ,mobile,emailFROM t_base/*以下查询对所有字段去重*/SELECT distinct s_name,id,s_num,c_num,s_add ,mobile,emailFROM t_base/*以下查询对 s_name 去重 */SELE

29、CT distinct s_nameFROM t_base/*以下查询对 s_name ， id 去重 */SELECT distinct s_name,idFROM t_base/*以下查询对 s_name 去重取 s_name 的较大 id 输出 */SELECT *FROM t_basewhere id in(select max(id) from t_base group by s_name)/*以下查询对 s_name 去重取 s_name 的较小 id 输出 */SELECT *FROM t_basewhere id in(select min(id) from t_base gr

30、oup by s_name)/*以下查询对 s_name 去重取 c_num 的较大 id 输出 */SELECT *FROM t_basewhere id in(select max(id) from t_base group by c_num)/*以下查询对 s_name 去重取 c_num 的较小 id 输出 */SELECT *FROM t_basewhere id in(select min(id) from t_base group by c_num)/*以下在t_class里按 cls_num加入 t_base，展开临时表t_base.c_num*/SELECT *FROM db

31、o.t_class INNER JOIN/*以下在 t_class里按 cls_num加入 t_base里按 cls_dpt加入 t_dpt后的t_class.cls_num，展开临时表t_base.c_num*/SELECT *FROM dbo.t_class INNER JOIN/*/dbo.t_dpt ON dbo.t_class.cls_dpt = dbo.t_dpt.dpt_num/*找出理学院所有的学生*/SELECT *from V_base where dpt_name='理学院 '/*找出理学院的所有班级的嵌套查询*/select *from t_class

32、where cls_dpt=(select dpt_num from t_dpt where dpt_name='理学院 ')/*理学院所有学生的嵌套查询 */select *from t_base where c_num in(select cls_num from t_class where cls_dpt=(select dpt_num from t_dpt where dpt_name='理学院 ') )/*插入 */*id是标识 */insertinto t_base(s_name,s_id,s_num,c_num,s_add,mobile,email

33、,t_meno)values('张三','420648913843511337','1000000001','0001','','#39;,'','')insertinto t_base(s_name,s_id,s_num,c_num,s_add,mobile,email,t_meno)values('张三','420648913843511337','1000000001'+1,'0001

34、9;,'','#39;,'','')declare t int /*/set t=1while t<500000begininsertinto t_base(s_name,s_id,s_num,c_num,s_add,mobile,email,t_meno)values('张三set t=t+1end/* 以下代码实现查询表中信息*/SELECT *FROM t_base/* 声明一时间变量显示查询数据的时间*/declare st datetimeset st=getdate()SELECT *FR

35、OM t_baseselect datediff(s,st,getdate()as ' 查询时间 ' /* 计算出 t_base 中 id 的个数 */select count(id) from t_base/* 计算出 V_base 中 id 的个数 */select count(id) from V_base/* 声明一时间变量显示查询数据的时间*/declare st datetimeset st=getdate()SELECT *FROM V_baseselect datediff(s,st,getdate()as '查询时间'/* 查询从视图里查询姓名

36、，学号，班级，部门等信息的时间declare st datetimeset st=getdate()SELECT s_name,s_num,cls_name,cls_dptFROM V_baseselect datediff(s,st,getdate()as '查询时间'*/*把所有姓名为张三改为张五*/update t_base set s_name ='张五 ' where s_name='张三 '/*学号为的姓名改为李四，班级改为*/update t_base set s_name ='李四 ',c_num='000

37、1' where s_num='1000000022'/*张五的地址、邮箱清空，并查看结果*/update t_base set s_add=' ',email=' 'where s_name='/*李四的地址、邮箱设为null，并查看结果 */update t_base set s_add= null ,email= null where s_name='SELECT *from t_base where s_name='张五 '张五 '李四 '/*updatea,b,setb.c_mum

38、=0001 */*新建表 lesson*/a.c_num=.,b.c_num=.wherea.c_num=b.c_numandif exists (select * from dbo.sysobjects where id =object_id(N'dbo.lesson') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table dbo.lessonGOCREATE TABLE dbo.lesson (le_id varchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,

39、 le_name varchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL) ON PRIMARYGO/*新建表 score*/if exists (select * from dbo.sysobjects where id =object_id(N'dbo.score') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table dbo.scoreGOCREATE TABLE dbo.score (sc_id int IDENTITY (1, 1) NOT NULL ,s

40、_num varchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL , ls_name varchar (50) COLLATE Chinese_PRC_CI_AS NOT NULL, sc_score int COLLATE Chinese_PRC_CI_AS NOT NULL) ON PRIMARYGOdeclare t0 int,t1 char(10),t2 int,t3 char(5),t varchar(50)set t0=1while t0<=(select max(id) from t_base)beginset t1=(select

41、s_num from t_base where id=t0)/*记录单个学号*/set t2=1while t2<=(select max(ls_id) from lesson)beginset t3=(select ls_num from lesson where ls_id=t2) /*记录单个课程号 */set t='80'/*初始化成绩*/insert into score(s_num,ls_name,sc_score)values(t1,t3,t)set t2=t2+1endset t0=t0+1end/* 清空表 score*/*delete scoredel

42、ete from score*/*drop table score删除表 score */declare t0 int,t1 char(10),t2 int,t3 char(5),t intset t0=1while t0<=(select max(id) from t_base)beginset t1=(select s_num from t_base where id=t0)/*记录单个学号*/set t2=1while t2<=(select max(ls_id) from lesson)beginset t3=(select ls_num from lesson where

43、 ls_id=t2) /*记录单个课程号 */set t=(select cast(floor(rand()*101) as int)/*初始化成绩*/insert into score(s_num,ls_name,sc_score)values(t1,t3,t)set t2=t2+1endset t0=t0+1end/*select floor(rand()*101)产生 100随机数 (带一位小数)*/*select cast(floor(rand()*101) as int)产生 100随机数( 不带小)*/select avg(sc_score) from score /*求平均成绩*

44、/*求出成绩高于平均成绩的总人数*/select COUNT(distinct s_name) from V1_base where sc_score>(select AVG(sc_score) from V1_base);select count(distinct t1.s_num) from score t1 where t1.sc_score>= (select avg(t2.sc_score) from score t2 where t1.ls_name=t2.ls_name);/*求出成绩高于平均成绩的总人次*/selectCOUNT(s_name)fromV1_base

45、 wheresc_score>(selectAVG(sc_score)from V1_base);select count(*) from score t1 where t1.sc_score>=(select avg(t2.sc_score) from score t2 where t1.ls_name=t2.ls_name);select s_name,lesson.ls_name from score t1,t_base,lesson wheret1.ls_name=lesson.ls_num and t1.s_num=t_base.s_num andsc_score>=(select avg(sc_score) from score t2 wheret1.ls_name=t2.ls_name);/*求出平均成绩最高的课程*/selecttop 1 ls_namefromV1_base groupby ls_nameorder byAVG(sc_score)desc/*将成绩在 30 间的成绩加分