数据库复习整理
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1.关系模型的三类完整性规则:实体完整性,参照完整性,用户定义的完整性规则。 1.1,数据库是指长期储存在计算机内,有组织的、可共享的大量数据的集合。数据库中的数据按照一定的数据模型组织、描述和存储, 较小的冗余度、具有较高的数据独立性和易扩展性,并可为各种用户专享。 1.2,数据库可以使用多种类型的系统模型:层次模型,网状模型,关系模型。、 1.3,创建数据库: create database stusystem完整备份 on ( name=stusystem_DAT, filename='E:\SQL SERVER FILE\stusystem\stu.mdf', size=20MB, maxsize=40MB filegrowth=5% ), ( name=stusystem_DAT1, filename='E:\SQL SERVER FILE\stusystem\stu.ndf', size=20MB, maxsize=40MB filegrowth=5% ), ( name =stusystem_LOG, filename='E:\SQL SERVER FILE\stusystem\stu.ldf', size=10MB, maxsize=20MB filegrowth=1MB ) 1.5,创建表: create table student( stuid int primary key, stunumber char(6) union not null, stuname varchar(20) not null, stupassword varchar(20) default '123456' not null, stusex char(2) not null, stubirthday datetime not null, claid int not null, constraint stu_cla foreign key (claid) references class(claid) ) 1.6,修改某一列的数据类型: alter table student alter column stuname char(15) 添加或删除表列: alter table student add stuaddress varchar(30) alter table student drop column stuaddress 1.7,使用insert插入数据: insert into student values(6,'200406','唐晓阳','txy19851225','男','1985-12-25',2) 或 insert into student(stuid ,stunumber,stuname,stupassword,stusex,stubirthday,claid) values(6,'200406','唐晓阳','txy19851225','男','1985-12-25',2) 1.8,使用insert...select插入数据: insert newstudent select stuid,stunumber,stuname,stusex,stubirthday from student 1.9,更新行: update student set stupassword='net123456' where claid in( select claid from class where claname = '.net 班' or claname = 'java 班' ) 2.SQL server 默认带有四个系统数据库: master:存储系统级信息,如果没有,数据库无法启动,包含登录账户信息,配置参数等, model:创建所有数据库模板,msdb:用于计划警报和作业,tempdb:保存临时表和临时存储过程. 3.主要数据文件 mdf,次要数据文件 ldf,事务日志文件 ldf。 4.子查询:使用in关键字 select * from student where claid in(select claid from student where stuname='tangxiaoyang') 使用exists关键字: select * from subject where exists (select * from cla_sub where cla_sub.subid=subject.subid and claid in (select claid from class where claname ='java班')) 5.标量值函数: create function getname (@stunumber char(6)) returns varchar(20) with encryption as begin declare @stuname varchar(20) select @stuname = stuname from student where stunumber=@stunumber return @stuname end 执行该函数:select dbo.getname('200401')as '学生姓名'; 6.内联表值函数: create function getstudent(@claid int) returns table with encryption as return select * from student where claid=@claid 执行函数:select * from getstudent(1) 7.事物的特性:原子性,一致性,隔离性,持久性。 8.创建存储过程: create procedure pro_test1 as select stuid,stuname,stusex from student where stuid = 1 执行存储过程:exec pro_test1 9.带输入参数的存储过程: create procedure pro_test2 @stuid_in int as select stuid,stuname,stusex from student where stuid = @stuid_in 执行:exec pro_test2 3 多个参数:exec pro_test2 @stuname ='wangxiojing',@stuid=3 10.带输出参数的存储过程: create procedure pro_test3 @stuid_in int , @stuid_out int output, @stuname_out varchar(10) output, @stusex_out char(2) output as select @stuid_out = stuid, @stuname_out = stuname, @stusex_out = stusex from student where stuid = @stuid_in 执行: declare @stuid_out int ,@stuname_out varchar(10),@stusex_out char(2) exec pro_test3 1,@stuid_out output, @stuname_out output, @stusex_out output 11.创建触发器: insert触发器: create trigger add_student on student with encryption after insert as if(select stusex from inserted)not in('男','女') begin print '性别不规范,请核对!' rollback transaction end instead of 触发器: create trigger remove_student on student with encryption instead of delete as begin delete from achievement where stuid in(select stuid from deleted) delete from student where stuid in(select stuid from deleted) end DDL触发器: create trigger protect_table on database with encryption for alter_table,drop_table as begin print '不能对数据库中的表进行删除或修改操作!' rollback end 禁用和启用嵌套: exec sp_configure 'nested trigger',0 禁用 exec sp_configure 'nested trigger',1 启用 递归触发器: 直接递归 间接递归 12.sql server 安全机制:客户机安全机制,网络传输的安全机制(两种加密方式:数据加密和备份加密), 实例级别安全机制(标准sqlserver登录,集成Windows登录),数据库级别安全机制,对象级别安全机制 用户访问客户机,通过网络传输登录服务器,然后访问数据库,访问数据库对象许可权。 13.使用命令创建登录名和数据库用户: create login newlogin with password='password123456' create user newuser for login newlogin 14.权限的三种语句: 对象权限: 授予对象权限: grant delete on teacher to xiaoqi 撤销对象权限: revoke delete on teacher from xiaoqi 拒绝对象权限: deny delete on teacher from xiaoqi 语句权限: 授予语句权限: grant create table to xiaoqi 撤销语句权限: revoke create table from xiaoqi 拒绝语句权限: deny create table from xiaoqi 15.数据备份类型:完整备份,差异备份,事务日志备份,文件或文件组备份 16.创建备份设备:exec sp_addumpdevice 'disk','backup','E:\蔡莎莎\sqlserver\backup.bak' 17.执行完整备份: backup database stusystem to 学生信息管理系统 with init name='stusystem完整备份' 执行差异备份: backup database stusystem to 学生信息管理系统 with noinit, differental, name='stusystem差异备份' 执行日志备份: backup log stusystem to 学生信息管理系统 with noinit name = 'stusystem日志备份' description='this is transaction backup of stusystem on disk' 18.恢复模式:完整恢复模式,大容量日志恢复模式,简单恢复模式。 19.还原数据库备份: 还原完整数据库备份: restore database stusystem from 学生信息管理系统 with file = 1,norecovery 还原第一个事务日志: restore log stusystem from 学生信息管理系统 with file=3,norecovery 还原第二个事务日志,并且恢复数据库。 restore log stusystem from 学生信息管理系统 with file = 3,recovery