MSSQL 简单练习回顾

这段时间，报了浦软培训的.NET，现在整理回顾下，算是个小小总结吧 为了便于操作，我没有在多个数据库间切换数据库实例，以一个总的数据库实例 test_demo为源进行的相关操作，代码的注释根据我的理解，并结合相关文献进行的解说，如果哪位前辈觉得注释的解说不妥，可给予纠正，谢谢。现在不废话了，上代码   1 ---练习  2 use master  3 /***********************************************  4   1、创建数据库  5 ************************************************/  6 ---数据库创建前的检测  7 if exists(select * from sys.sysdatabases where name='test_demo')  8 drop database test_demo  --删除已有的数据库test_demo  9 create database test_demo  --开始创建新数据库test_demo 10 on PRimary  --默认就属于primary 主文件组，可省略 ,但必须有on 11 ( 12     name="test_demo",  --主数据文件的逻辑名 13     filename="C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/test_demo.mdf", --主数据文件的物理名 14     size=10mb,  --主数据文件的初始大小 15     filegrowth=10% --主数据文件的增长率(可以为百分比,也可为实数) 16 ) 17 ) 18 log on --以下为日志文件描述,同上 19 ( 20     name="test_name", 21     filename="C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/test_demo_log.ldf", 22     size=1mb, 23     filegrowth=10% 24 ) 25 --检查数据库是否创建成功 26 select * from sys.sysdatabases where name='test_demo' 27  28  29  30 /*********************************************** 31   2、创建表 32 ************************************************/ 33 use test_demo    34 create table stuInfo   --创建学生信息表 stuInfo 35 ( 36     stuName varchar(20) not null, 37     stuNo char(6) not null,  --学号 38     stuAge int not null, 39     stuId numeric(18,0) null, --身份证号，小数位为0 40     stuSeat smallint identity(1,1), --座位号，自动递增     41     stuAddress text 42 ) 43 go 44 create table stuMarks  --创建学生成绩表  45 ( 46     examNo char(7) not null, --考号 47     stuNo char(6) not null, --学号 48     writtenExam int not null, --笔记成绩 49     labExam int not null --机试成绩 50 ) 51 go 52  53 --检测数据库表stuinfo和stumarks的存在情况 54 use studb 55 select * from sys.sysobjects where name= 'stuinfo'  56 select * from sys.sysobjects where name = 'stumarks' 57  58  59  60 /*********************************************** 61   3、为表 stuInfo 和stuMarks添加约束 62 ************************************************/ 63 /* 64 语法： 65        alter  table tab_name 66        add constraint 约束名 约束类型 具体的约束说明    67 备注： 68      主键约束（Primary Key Constraint）：要求主键列数据唯一，并且不允许为空 69      唯一约束（Unique Constraint）：要求该列唯一，允许为空，但只能出现一个空值。 70      检查约束（Check Constraint）：某列取值范围限制、格式限制等，如有关年龄的约束 71      默认约束（Default Constraint）：某列的默认值，如我们的男性学员较多，性别默认为“男” 72      外键约束（Foreign Key Constraint）：用于两表间建立关系，需要指定引用主表的那列  73 总结：主键约束和惟一月虽然都强调了数据的惟一性， 74       但主键约束强调的是数据主体，惟一约束则强调的是某一列 75 */ 76 alter table stuInfo 77 add constraint pk_stuNo primary key(stuNo) --为学生学号添加主键约束 78  79 alter table stuInfo 80 add constraint uq_stuId unique(stuId) --为学生身份证添加惟一约束 81  82 alter table stuInfo 83 add constraint df_stuAddress default('地址不详') for stuAddress --为地址添加默认约束，如果地址不详。默认为“地址不详” 84  85 alter table stuInfo 86 add constraint ck_stuAge check(stuAge between 15 and 40) --为学生年龄添加年龄检查约束，要求年龄在15-40 87  88 alter table stuMarks 89 add constraint fk_stuMarks foreign key(stuNo) references stuInfo(stuNo) --为学生成绩添加外键约束（主表stuInfo和从表stuMarks建立关系，关键字段为stuNo） 90  91 exec sp_helpconstraint stuinfo  --查看当前表stuinf的约束情况 92 exec sp_helpconstraint stumarks --查看当前表stumarks的约束情况 93  94  95 /*********************************************** 96   4、插入相关数据 97 ************************************************/ 98 insert into stuinfo values('张三',001,25,420621198906254567,'湖北襄阳') 99 insert into stuinfo values('李四',002,25,420621198906121554,'上海')100 insert into stuinfo values('王五',003,34,420621198003166548,'北京')101 insert into stuinfo values('赵强',004,23,420621199105148756,'湖北武汉')102 insert into stuinfo values('钱海',005,22,420621199208154582,'江苏苏州')103 insert into stuinfo values('周国',006,30,420621198409265148,'江苏南京')104 insert into stuinfo values('孙坚',007,33,420621198304261855,'')105 106 insert into stumarks values(001,1,50.6,70.9)107 insert into stumarks values(022,2,64.5,84.5)108 insert into stumarks values(031,3,46.6,45.9)109 insert into stumarks values(023,4,95.7,51.9)110 insert into stumarks values(043,5,52.5,84.9)111 insert into stumarks values(015,6,94.6,76.9)112 insert into stumarks values(006,7,86.5,84.6)113 114 --查询数据插入情况115 select * from stuinfo116 select * from stuMarks117 118 /***********************************************119   5、为表stuInfo和stuMarks创建视图120 ************************************************/121 /*122  视图是一张虚拟表，可以方便不同用户的查询，提高数据的安全性，筛选特定的数据行。123     视图的用途：124              筛选表中的行125              防止未经许可的用户访问敏感数据126              降低数据库的复杂程度127              将多个物理数据库抽象为一个逻辑数据库128 */129 if exists(select * from sys.sysobjects where name='view_stuInfo_stuMarks')130 drop view view_stuInfo_stuMarks131 go132 create view view_stuInfo_stuMarks 133 as134 select '姓名'=stuName, '学号'=stuInfo.stuNo,'考试号'= stuMarks.ExamNo,135    '笔记成绩'=writtenExam,'机试成绩'=labExam,136    '平均分'=(writtenExam +labExam)/2137 from stuInfo  left join stuMarks 138   on stuInfo.stuNo = stuMarks.stuNo139 go140 141 select * from view_stuInfo_stuMarks --使用视图142 143 144 --使用相关的条件查询145 --求出平均分数146 use test_demo147 --获取每个学员的总成绩148 select a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',(b.writtenExam+b.labExam) as '总成绩'149  from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo150  order by '总成绩' desc151 152 /*153   总结:154      当使用group by条件语句进行筛选时，select项的字段中，除使用聚合函数进行统计的字段外，其他需要select出的字段名必须出现在group by分组条件中155 */156 select a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',sum(b.writtenExam+b.labExam) as '总成绩',(b.writtenExam+b.labExam)/2 as '平均成绩'157  from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo158 group by a.stuName,a.stuNo,b.ExamNo,b.writtenExam,b.labExam159  order by '总成绩' desc160 161 162 --根据学员总的平均成绩和每个学员的总成绩，判断是否及格，划线为150分163 declare @Avg1 float164 --获取当前学员总的平均成绩165 select @Avg1=avg(writtenExam+labexam) from stuInfo as a, stuMarks as b 166   where a.stuNo=b.stuNo 167  168 select  a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',(b.writtenExam+b.labExam) as '总成绩', @avg1 as '及格分数',169 (170 case171   when (b.writtenExam+b.labExam)<@avg1 then '不及格'172         when (b.writtenExam+b.labExam)>=@avg1 then '及格'173 end174 ) as '及格情况'175 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo176 order by '总成绩' desc177 178 179 declare @Avg float   --声明变量@AVG用于存储学员总的平均成绩180 --获取当前学员总的平均成绩181 select @Avg=avg(writtenExam+labexam) from stuInfo as a, stuMarks as b 182   where a.stuNo=b.stuNo 183 184 select  a.stuName as '学生', a.stuNo as '学号',b.ExamNo as '考号', b.writtenExam as '笔试成绩',b.labExam as '机试成绩',sum(b.writtenExam+b.labExam) as '总成绩', @avg as '及格分数',185 (186 case187   when (b.writtenExam+b.labExam)<@avg then '不及格'188         when (b.writtenExam+b.labExam)>=@avg then '及格'189 end190 ) as '及格情况'191 from stuInfo as a inner join stuMarks as b on a.stuNo = b.stuNo192 group by a.stuName,a.stuNo,b.ExamNo, b.writtenExam ,b.labExam 193 order by '总成绩' desc194 195 /***********************************************196   6、触发器 triggers197                     触发器（trigger）是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，比如当对一个表进行操作（ insert，delete， update）时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ，USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。198 触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用，而是在用户执行Transact-SQL语句时自动触发执行。199 200  说明：模拟银行银行存款事件，用户的取款和存款操作对储蓄用户账目相关信息的更新    201  练习步骤：202              01、创建bank表和transInfo表203                 02、创建触发器204 ************************************************/205 use test_demo206 --创建表bank和 transInfo207 create table triggers_bank  --创建账户存款表bank208 (209   customerName nvarchar(255) not null,210         cardID varchar(255) not null unique,211         currentMoney money    212 )213 create table triggers_transInfo  --创建账户交易表transInfo,用户记录账户操作事件日志214 (215    transDate datetime not null,216             cardID varchar(255) not null,217          transType nvarchar(50) not null,218             transMoney money 219 )220 221 --向表中插入相关测试数据222 insert into triggers_bank values('张三','1001001',1000.000)223 insert into triggers_bank values('李四','1001002',1.000)224 insert into triggers_transinfo values(getDate(),'1001001','支取',100.00)225 --查询数据是否插入成功226 select * f