数据库期末复习--（SQL sever）数据库

前置概念

数据库一般分为：关系型数据库和非关系型数据库。
数据库的内部构造是一颗B树(balanced tree)。
三级模式：外模式，模式，内模式。
第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。（简而言之：表中元素不可再分，比如:“课程”就不是一个原子式，”课程“可以分为课程编号，课程介绍，课程名称等等)。
第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。(简而言之：再满足1NF的前提下，表中每个非主属性元素都完全依赖于主属性。例如：主属性位(姓名，学号)的一个表，某非主属”性别“，能根据学号来直接判断这个人的性别是什么，而不需要”姓名“进行补充，那么这样的表是不满足2NF的)。
第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.（简而言之：表中元素不存在依赖传递。比如有一个表：学号，系号，系名，学号->系号，系号->系名，这样形成了依赖传递，所以不满足3NF）。
ps:满足三范式的数据库不一定是好的数据库，三范式只是提供了设计数据库的一个模式，一个优秀的数据库需要根据实际需求来制定，例如，如果需求的这个数据库时间要求比较紧，空间比较宽松，大可以放弃三范式来保证效率。

创建数据库创建数据库

create database mydb on ( --数据文件，第一个数据文件为主文件，后缀.mdf name = my_datatbase_data,--逻辑名 filename = 'D:\数据库练习\my_database_data.mdf',--文件名 size = 10MB,--数据库初始大小 maxsize = 100MB,--最大大小 filegrowth = 10% -- 文件的增长容量) log on ( --日志文件，后缀.ldf name = my_database_log, filename = 'D:\数据库练习\my_database_log.ldf', size = 5MB, maxsize = 50MB, filegrowth = 5MB )

修改数据库略
删除数据库

drop database mydb

用户权限及角色概念

权限分为对象权限，语句权限。（对象权限指对表内数据的操作的权限（例如update,delete,select）,语句权限指出对象权限的其他权限，例如(drop,create)）
角色指：拥有某些权限的一类人。比如我作为学生，有吃饭，睡觉的权限，却没有权限改自己的考试成绩。学生就是一种角色。

权限分配

--创建一个密码为123，登录名为vistor，基于mydb数据库的登陆 create login visitor with password = '123', default_database = mydb; --从登录名为vistor的数据库中创建数据库用户dbuser create user dbuser from login visitor; --赋予他建表的权限（语句权限无需指定表） grant create table to dbuser; --赋予在t0上插入和查询的权限（需要指定表，一条语句只能指定一个表） grant insert,select on t0 to dbuser; --从dbuser上收回建表的权限 revoke createtable from dbuser; --创建角色 create role student; --删除角色 drop role student; --删除数据库用户 drop user dbuser; --删除登陆帐好 drop login visitor;

表操作概念常用数据类型：

数据类型	数据范围	用途
bit	[0,1]	表示逻辑
int	[ ? 2 31 , 2 31 ? 1 ] [-2^{31},2^{31} - 1] [?231,231?1]	表示32位整数
bigint	[ ? 2 63 , 2 63 ? 1 ] [-2^{63},2^{63} - 1] [?263,263?1]	表示64位整数
float	略	表示浮点数
money	略	表示货币
char(n)	1<=n<=8000	表示定长小字符串
varchar(n)	按实际空间分配，最大8000	表示变长小字符串
varchar(max)	非常大	表示文本类型，例如一本小说就可以用这个存储
date	略	表示日期
binary、varbinary、varbinary(max)	略，和char类似	表示二进制,例如音频视频之类的不易被处理的文件。

建表：

--创建一个由学号,姓名，性别构成的表 create table emp( empid varchar(20), empname varchar(20), empgender char(10) ); -- 插入三条数据 insert into emp values('001','张三','男'); insert into emp(empid) values('004'); insert into emp(empname,empgender) values('尹志平','男'); -- 修改表中行 --有where指定，就指改变指定的，没有就全部改变 update emp set empgender = '女' where empname = '张三'; update emp set empgender = '男' --修改表中列,增加一列 alter emp add empscore int; --删除，和update原理一样 delete from emp where empname = '尹志平'; --删表 drop table emp;

事务概念：

事务属性有：原子性，一致性，隔离性，持久性。
事务的隔离级别有：未提交读，提交读，可重复读，可串行读
SQLSserver事务模式：自动提交事务模式，显式事务模式，隐式事务模式。

事务的使用

begin transaction --开始显式事务 commit transaction --结束事务

表约束概念：

主键（primary key）：唯一非空且唯一确定某个元素，即主属性，如果有多个元素能确定主属性，一般选择易于排序的数据，会自动创建聚类索引
外键 (foreign key)：在另一个表充当主键，比如，课程号在课程表作为主键，在学生表中就可以作为外键。
唯一约束(unique)：数据唯一，会自动创建索引。
检查约束(check(xxxx))：即条件性约束，当元素满足某个条件才能更新。比如性别只能是男或女。
非空约束(not null)：非空。

创建

create table figure( fno int primary key identity(1,1), fname varchar(20) not null, fnickname varchar(20) unique, depno varchar(20) foreign key references dep(depno) ); create table dep( depno varchar(20) primary key, depname varchar(20));

figure表

fno(主键，初始为1，且自增1)	fname （非空）	fnickname(唯一)	depno(外键)
1	尹志平	龙骑士	1
2	小龙女	姑姑	2
3	杨过	过儿	2

dep表

depno	depname
1	全真教
2	古墓派

单表查询常用函数 sum(),max(),avg(),min(),count()-- 求和，求最大值.平均值，最小值，计数
单表查询

select * from emp; --全部查询 select * from emp where empname = '尹志平' -- 指定查询 select * from emp order by empscore desc/asc --根据分数降序/升序查找 select depno,max(empscore) from emp group by depno --查询每个部门的最大分数（按部门编号分组）

连接查询和子查询连接查询：两个表直接进行连接的查询（只说内连接），比如我想知道我想通过figure表的depno知道depname,那么一个表肯定不行，我们可以通过连接查询完成。
子查询：将查询结果作为上一个查询的条件，比如说我想知道班上的最高分的信息，我们先查到最高分，然后将最高分作为条件去找这个人。
连接查询和子查询

--连接查询 select depname from figure join dep onfigure.depno = dep.depno; --子查询 select * from emp where empscore = (select max(empscore) from emp) --

索引和视图索引的实质给数据库一个搜索的方向(将数据有顺序的存放在B树中)。
视图的实质是把查询打包成一个函数，需要时直接调用。
视图属于三级模式中的外模式
索引分为唯一索引(唯一约束自带)，聚集索引(主键自带，且只能有一个)，非聚集索引。

--在表emp上对empname列创建索引 create index index_01 on emp(empname); --创建视图view_01 将查询全部emp的元素的查询指令打包 create view view_01 as select * from emp;

SQL基本程序设计 sql作为一种数据库专有的编程语言也有自己的程序设计方式，但是sql语言一般较为繁琐，只能实行简单的逻辑，一般繁琐的逻辑实现还是交给后台（java，c++，go等）来完成。
实现10!

declare @i int ,@res int select @i = 1,@res = 1 while(@i <= 10) begin select @res = @res * @i select @i = @i + 1 end select @res;

*定义游标，通过游标来读取figure中行，要求输出姓名(比较难) 略
存储过程和函数存储过程和函数只在定义和使用上有细小的差别
存储过程实现A+B

--创建 create proc sp_add @a int ,@b int,@res int output as begin select @res = @a + @b end --调用 declare @a int,@b int,@res int select @a = 3,@b = 4 exec sp_add @a,@b,@res output select @res;

函数实现A+B

--创建 create function func_add(@a int,@b int) returns int as begin declare @res int select @res = @a + @b return @res end --调用 declare @res int,@a int,@b int select @a = 3,@b = 4 select @res = dbo.func_add(@a,@b) select @res

触发器触发器：顾名思义，通过触发某个事件来执行的，比如设定只能在8：00-17：00修改数据库，那么当5：00修改数据的时候就会触发触发器然后阻止这次的行动。
DML触发器：在对表内数据进行操作时触发（类似对象权限）
DDL触发器：（类似语句权限）
创建一个DML触发器 【数据库期末复习--（SQL sever）】太难了，略。。。。