前置概念

  1. 数据库一般分为:关系型数据库和非关系型数据库。
  2. 数据库的内部构造是一颗B树(balanced tree)。
  3. 三级模式:外模式,模式,内模式。
  4. 第一范式:当关系模式R的所有属性都不能在分解为更基本的数据单位时,称R是满足第一范式的,简记为1NF。(简而言之:表中元素不可再分,比如:“课程”就不是一个原子式,”课程“可以分为课程编号,课程介绍,课程名称等等)。
  5. 第二范式:如果关系模式R满足第一范式,并且R得所有非主属性都完全依赖于R的每一个候选关键属性,称R满足第二范式,简记为2NF。(简而言之:再满足1NF的前提下,表中每个非主属性元素都完全依赖于主属性。例如:主属性位(姓名,学号)的一个表,某非主属”性别“,能根据学号来直接判断这个人的性别是什么,而不需要”姓名“进行补充,那么这样的表是不满足2NF的)。
  6. 第三范式:设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

用户权限及角色

概念
  1. 权限分为对象权限,语句权限。(对象权限指对表内数据的操作的权限(例如update,delete,select),语句权限指出对象权限的其他权限,例如(drop,create))
  2. 角色指:拥有某些权限的一类人。比如我作为学生,有吃饭,睡觉的权限,却没有权限改自己的考试成绩。学生就是一种角色。
权限分配
--创建一个密码为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 create  table from dbuser;
--创建角色
create role student;
--删除角色
drop role student;
--删除数据库用户
drop user dbuser;
--删除登陆帐好
drop login visitor;

表操作

概念

常用数据类型:

数据类型

数据范围

用途

bit

[0,1]

表示逻辑

int

表示32位整数

bigint

表示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;

事务

概念:
  1. 事务属性有:原子性,一致性,隔离性,持久性。
  2. 事务的隔离级别有:未提交读,提交读,可重复读,可串行读
  3. SQLSserver事务模式:自动提交事务模式,显式事务模式,隐式事务模式。
事务的使用
begin transaction --开始显式事务
commit transaction --结束事务

表约束

概念:
  1. 主键(primary key):唯一非空且唯一确定某个元素,即主属性,如果有多个元素能确定主属性,一般选择易于排序的数据,会自动创建聚类索引
  2. 外键 (foreign key):在另一个表充当主键,比如,课程号在课程表作为主键,在学生表中就可以作为外键。
  3. 唯一约束(unique):数据唯一,会自动创建索引。
  4. 检查约束(check(xxxx)):即条件性约束,当元素满足某个条件才能更新。比如性别只能是男或女。
  5. 非空约束(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 on  figure.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触发器

太难了,略。。。。