高级数据库完整版知识.docx

数据库里新建文件夹(红色部分是必须的,因为保障服务器安全,默认禁用了一些功能,用外围启动xp_cmdshell):exec sp_configureshow advanced options,1reconfigureexec sp_configurexp_cmdshell,1reconfiguregoexec xp_cmdshellmd C:UsersAdministratorDesktop数据库1,no_outputGO -(建文件夹)创建数据库(创建数据库时,主文件和日志文件的逻辑名不能相同):create database NetBarDB1on(name=NetBar1_mdf,filename=C:UsersAdministratorDesktop数据库NetBar1_mdf.mdf,size=3mb,maxsize=100mb,filegrowth=1mb)log on(name=NetBar1_ldf,filename=C:UsersAdministratorDesktop数据库NetBar1_ldf.ldf,size=3mb,maxsize=100mb,filegrowth=1mb)删除数据库if exists(select * from sys.sysdatabases where name=数据库名)drop database 数据库名go创建表(红色字体是必备要素):create table 表名(列名 数据类型 null 各种约束)增加列:alter table 表名(add 列名 数据类型 null 各种约束)修改列的属性:alter table 表名(alter column 列名 数据类型 null 各种约束)删除列:alter table 表名(drop column 列名)添加约束语法： alter table 表名 add constraint 约束名称 约束类型 约束内容例子:主键约束:ALTER TABLE personInfoadd constraint pk_personInfo_id Primary Key (id)GO检查约束:ALTER TABLE personInfoadd constraint ck_personInfo_id check(stuAge between 15 and 20)GO默认值约束:alter table stuInfoadd constraint df_stuInfo default(地址不详!) for addressgo唯一约束:alter table stuInfoadd constraint uq_stuInfo unique(stuAge)go外键约束:alter table stuMark(外表,主键要为主键)add constraint fk_stuMark_stuInfo foreign key(stuId)(外键)references stuInfo(stuId)(主键)alter table 表名drop constraint 约束名go变量分为： 局部变量： 局部变量必须以标记作为前缀 ，如age 局部变量的使用也是先声明，再赋值 局部变量赋值用set或select 全局变量： 全局变量必须以标记 作为前缀，如version 全局变量由系统定义和维护，我们只能读取，不能修改全局变量的值 声明变量(declare 变量名 数据库的数据类型):declare name nvarchar(10)赋值(set 变量名=值):set name=louisselect password from customerInfo where customer=nameexec xp_cmdshell dos命令 ,no_outputif 条件(在sql相等时用一个=)begin sql执行语句EndWhile 条件beginsql执行语句endcase是可以判断而执行sql语句,when就是多个sql语句分支(功能相同),then后面的数据类型要一致select stuName,stuAge,等级=casewhen stuAge between 18 and 20 then 年轻when stuAge between 21 and 29 then 青年when stuAge between 30 and 38 then 中年else 其它endfrom stuInfo存储过程(相当于c语言的函数,只是存在于一时),存储过程也可以传出数值,用参数用output存储过程和表格都是属于objects,与数据库不相同Exec 是执行存储过程,后面不加gocreat proc 名字参数(可以外部传入,也可以给初始值)assql语句go -上面的只是存入,没有使用exec 名字 -使用该存储过程例子:-带参数的存储过程,与输出参数的存储过程,传参数时,参数顺序需要匹配,if exists(select * from sysobjects where name=proc_studb)drop proc proc_studbgocreate proc proc_studbcount int output,ls float=60,ws float=50asprint 及格的人的姓名,成绩:select stuName,wscore,lscore from stuInfo inner join stumark on stuinfo.stuId=stumark.stuIdwhere wscore=ws and lscore=lsprint 不及格人数:select count=COUNT(*) from stumarkwhere not (wscore=ws and lscore=ls)go事务(最突出:完整性)开始事务：begin transaction提交事务：commit transaction回滚事务：rollback transaction 一错则无法执行.错误时可执行事务回滚:不执行整段代码ROLLBACK TRANSACTION(事务回滚),return也是可以让代码执行停止.正确可执行:COMMIT TRANSACTION 例子: -FOREIGN KEY 约束fk_stuMark_stuInfo冲突。该冲突发生于数据库stuDB，表dbo.stuInfo, column stuId。declare error int set error=0BEGIN TRANSACTION update stumark set stuId=100 where stuId=1 set error=error+ERRORupdate stumark set stuId=1 where stuId=2set error=error+ERRORif error0beginprint 错误! print errorrollback transactionendelsebeginprint 正确!commit transactionendgouse stuDBgo在有事务时,要给事务进行判断时,需要定义一个error累加全局变量error,然后进行判断.事务可以嵌套在存储过程里面!视图查询出来就是一张虚拟的表(包含一张或多张表的列组的数据集),创建视图时,只能用到查询的语法(查询语句可用级联)!视图可以增删改查,但是增删改用得比较少,原因是容易出错,所以经常用视图用来查询!创建视图:Create view 视图名(view_)参数块As查询块Goif exists(select * from sysobjects where name=view_stuDB)drop view view_stuDBgocreate view view_stuDBasselect smid as 考号,stuName as 姓名,wscore as 笔试成绩,lscore as 机试成绩 from stuInfo inner join stumark on stuInfo.stuId=stumark.stuIdgoselect * from view_stuD索引分为4种:主键索引:一般创建主键约束后自动添加,无法删除索引;聚集索引:顺序排列;非聚集索引:无序排列;唯一索引:创建唯一约束时自动创建唯一索引(唯一,非聚集索引),没创建唯一约束而只是创