数据库命令大全-

1、数据库命令大全2008-11-13 20:35-载入语句:用于转存数据库/*EXEC sp_attach_db dbname = '学生成绩管理系统',filename1 = 'E:Microsoft SQL ServerMSSQL$WEBDATASERVERData学生成绩管理系统_Data.mdf',filename2 = 'E:Microsoft SQL ServerMSSQL$WEBDATASERVERData学生成绩管理系统_Log.ldf'*/-数据定义语言:DDL(create,alter,drop/*create database

2、 webserver -创建数据库drop database webserver -删除数据库create database stusystem -创建数据库use stusystem -打开数据库create table student(sno nvarchar(12primary key -创建基本表create table course(cno nvarchar(12primary key -创建基本表create table SC( -创建基本表sno nvarchar(12,cno nvarchar(12 default'1',grade int,primary ke

3、y(sno,cno,foreign key(snoreferences student(sno,foreign key(cnoreferences course(cno,check(grade between 0 and 100-修改基本表alter table student add sname nvarchar(50,sage nvarchar(12-增加新列alter table student alter column sage int -修改列定义alter table student drop column sage -删除列create table age(sage int-创建

4、表 drop table age -删除表truncate table sc-删除表中所有行(=delete from sccreate unique index scindex on SC(sno asc,cno desc-按sno升序,cno降序建立唯一索引drop index SC.scindex -删除索引create view 成绩总表 as select.where.with check option -创建视图(虚表 drop view 成绩总表 -删除视图*/-数据控制语言: DCL(grant,revoke/*-权限设置:select 、update、insert、delet

5、egrant update(sno,select on student to happy with grant option -给用户happy 授权并可传播该权限revoke select on student from happy cascade -给用户happy卸权*/-数据操纵语言:DML(select,update,insert,delete-SELECT-use 学生成绩管理系统 -打开数据库-select * from 学生表A /* 检索语句 */-select 学号,姓名,性别 from 学生表A -投影操作(针对列-where 性别='女' -选择操作(针

6、对行-where 姓名='陈真明' -等值查询-where 姓名 like '陈%' -模糊查询(右模糊-where 姓名 like '%林%' -模糊查询(左右模糊-where 姓名 like '%云' -模糊查询(左模糊-where 姓名 like '%' -模糊查询(全模糊-where 姓名 like '林%' and 性别='女'-where 姓名 like '陈%' and 性别='女' or 性别='男'-where 姓名

7、like '陈%' and (性别='女' or 性别='男'/*第一题:检索所有姓“刘”的男生和所有姓名里包含“林”字的女生。*/select 学号,姓名,性别 from 学生表Awhere 姓名 like '刘%' and 性别='男'or 姓名 like '%林%' and 性别='女'/*第二题:检索所有姓“陈”和姓“张”的男生记录。*/select 学号,姓名,性别 from 学生表Awhere (姓名 like '陈%' or 姓名 like '张

8、%' and 性别='男'-聚合函数-聚合函数/*select count(* as 成绩总数 from 成绩表 -计数select avg(成绩 as 总平均 from 成绩表 -求平均值select max(成绩 as 最高分 from 成绩表 -求最大值select min(成绩 as 最低分 from 成绩表 -求最小值select sum(成绩 as 总分数 from 成绩表 -求和*/*select count(* as 成绩总条数,avg(成绩 as 总平均,max(成绩 as 最高分, min(成绩 as 最低分,sum(成绩 as 总分数from 成绩

9、表*/*第三题:求所有籍贯为“福清”的人数。*/-select count(* from 学生表B where 籍贯='福清'/*-排序-select * from 成绩表where 学期='第一学期' and 类别='考试科'and 科目编号='300001'-order by 成绩 ASC -升序(默认值order by 成绩 DESC -降序*/-取前n个-/*select top 5 * from 学生表A -top n:前n条order by 性别 DESC,姓名 ASC -多字段排序*/*第四题:检索第四学期考试科中成

10、绩排在前10名的成绩记录。*/*select top 10 * from 成绩表where 学期='第四学期' and 类别='考试科'order by 成绩 DESC*/*第五题:检索各学期考试科目的成绩记录,要求各学期内的成绩按从高到低排列。*/*select * from 成绩表where 类别='考试科'order by 学期,成绩 DESC*/-分组-/*select 学期,类别,avg(成绩 as 平均成绩 from 成绩表group by 学期,类别 -分组order by 学期,类别 -排序*/*-统计各个班级的男女生人数sele

11、ct 班级编号,性别,count(* as 人数 from 学生表Agroup by 班级编号,性别order by 班级编号*/-分组条件-查看第五学期各个类别各门科目的平均成绩在80分及-以上的记录/*select 学期,类别,科目编号,avg(成绩 as 平均分from 成绩表where 学期='第五学期'group by 学期,类别,科目编号having avg(成绩>=80 -and 学期='第五学期'order by 学期,类别,科目编号*/*分组条件规则:当条件表达式有包含聚合函数时,一般把条件放于having语句中,其它情况一般放于wher

12、e语句中。*/-1、统计各个民族各个籍贯的学生人数在2人及以上的人数信息。-(民族,籍贯,人数/*select 民族,籍贯,count(* as 学生人数 from 学生表Bgroup by 民族,籍贯having count(*>=2order by 民族,籍贯*/-2、统计第一、二学期内各考查科目的平均分在75分及以上的信息。-(学期,类别,科目编号,平均分/*select 学期,类别,科目编号,avg(成绩 as 平均分 from 成绩表where (学期='第一学期' or 学期='第二学期' and 类别='考查科' group

13、by 学期,类别,科目编号having avg(成绩>=75order by 学期,类别,科目编号*/-3、检索在第六学期考试中,平均分排在前3名的相关信息。-(学期,平均成绩/*select top 3 avg(成绩 as 平均分,学期 from 成绩表where 学期='第六学期'group by 学期order by avg(成绩 desc*/-4、检索第三学期中各考试科目最高分在90以上的相关信息。-(学期,类别,科目编号,最高分/*select 学期,类别,科目编号,max(成绩 as 最高分 from 成绩表where 学期='第三学期' an

14、d 类别='考试科'group by 学期,类别,科目编号having max(成绩>=90order by 学期,类别,科目编号*/-5、检索各学期中各科目的不及格人数信息。-(学期,科目编号,不及格人数/*select 学期,科目编号,count(* as 不及格人数 from 成绩表where 成绩<60group by 学期,科目编号order by 学期,科目编号*/-多表查询- -多表查询use 学生成绩管理系统-检索所有籍贯为南平的姓“林”的学生记录/*select 学生表A.学号,姓名,性别,籍贯,通信地址from 学生表A,学生表Bwhere 学生

15、表A.学号=学生表B.学号 -等值条件and 籍贯='南平' and 姓名 like '林%'*/-可视化创建视图- /*select * from 学生表 -学生表:视图where 性别='男'*/-代码创建视图- /*create view 成绩总表 -创建视图asselect 学生表.学号,姓名,性别,通信地址,学期,科目名称,类别,成绩from 学生表,科目表,成绩表where 学生表.学号=成绩表.学号and 科目表.科目编号=成绩表.科目编号*/-检索第一学期英语科目不及格的女生的学号、姓名、性别、-通信地址、学期、科目名称、类别、成

16、绩/*select * from 成绩总表where 学期='第一学期' and 科目名称='英语'and 性别='女' and 成绩<60*/-成绩总表-视图查询-6、检索各学期内所有英语有补考的女生记录/*select * from 成绩总表where 科目名称='英语' and 性别='女' and 成绩<60*/-7、统计各学期内各考试科目的最高分、最低分和平均分。/*select 学期,科目名称,max(成绩 as 最高分,min(成绩 as 最低分,avg(成绩 as 平均分from 成绩总

17、表where 类别='考试科'group by 学期,科目名称order by 学期,科目名称-8、统计各英语考试中男、女生的平均分。/*select 学期,科目名称,性别,avg(成绩 as 平均分from 成绩总表where 科目名称='英语'group by 学期,科目名称,性别order by 学期,科目名称,性别*/-9、列出所有英语科目有过补考的男生姓名/*select distinct 姓名 from 成绩总表where 科目名称='英语' and 成绩<60 and 性别='男'*/*-同解select 姓名

18、 from 成绩总表where 科目名称='英语' and 成绩<60 and 性别='男'group by 姓名*/-10、列出补考次数最多的学生的学号、姓名、性别、补考次数。/*select top 1 学号,姓名,性别,count(* as 补考次数from 成绩总表where 成绩<60group by 学号,姓名,性别order by count(* DESC*/-子查询- -子查询-检索所有姓“林”的学生的成绩记录。/*select * from 成绩表where 学号 in(select 学号 from 学生表Awhere 姓名 lik

19、e '林%'*/-创建字段select *, 成绩+30 as 附加分 from 成绩表where 学号 in(select 学号 from 学生表Awhere 姓名 like '林%'*/-INSERT,DELETE,UPDATE-insert into <表名> (<属性列1>,<属性列2>. values(<常量1>,<常量2>. delete from <表名> where <条件>update <表名> set <列名>=<表达式>,

20、<列名>=<表达式>.where <条件>use 学生成绩管理系统-select * into 学生表C from 学生表 -创建新表-select * into 成绩表C from 成绩总表 -创建新表-insert into 学生表C(学号,姓名,性别 values('1','张三','男' -插入操作-insert into 学生表C values('2','李四','女','南平','' -插入操作-delete from

21、学生表C where 学号='1' -删除操作-delete from 学生表C where 学号 in('1','2' -删除操作-update 学生表C set 性别='男' where 姓名='李小苹'-更新操作-select * from 学生表C/*update 成绩表C set 成绩=成绩+10where 学期='第二学期' and 性别='女' and 科目名称='英语'and 姓名='薛明真'select * from 成绩表Cwher

22、e 学期='第二学期' and 性别='女' and 科目名称='英语'and 姓名='薛明真'*/-1、将第一学期里所有女生的英语成绩加20分。-(要求最高分不能超过100分/*update 成绩表C set 成绩=成绩+20where 学期='第一学期' and 性别='女' and 科目名称='英语'update 成绩表C set 成绩=100where 学期='第一学期' and 性别='女' and 科目名称='英语'and

23、成绩>100select * from 成绩表Cwhere 学期='第一学期' and 性别='女' and 科目名称='英语'*/-自身连接-查询每一门课的间接选修课select T1.Cno,T2.Cpno from Course T1,course T2where T1.Cpno=T2.Cno -(本例中,需要为Course表取两个别名.比如,一个用T1,另一个用T2-并操作UNION的使用-查询计算机科学系的学生及年龄不大于19岁的学生select * from Student where Sdept='CS'UNI

24、ONselect * from student where Sage<=19-(用union合并起来的,系统会自动去掉重复元组 -事务控制/*事务:1.自动提交事务2.显式事务(begin mit3.set implicit_transactions on-打开隐式事务set implicit_transactions off-关闭隐式事务4.save transaction abc 设置保存点abc*/declare k int -定义局部变量 -全局变量-set k=0 -给变量赋值begin transactionupdate 成绩表C set 成绩=成绩-kwhere 学号='0404004' and 学期='第一学期' and 科目名称='英语'if(rowcount=0rollback transaction -事务回滚elseupdate 成绩表C set 成绩=成绩+kwhere 学号='0404005' and 学期='第一学期' and 科目名称='英语'if(rowcount=0rollback transaction -事务回滚elsecommit transaction -提交事务select * fro