前置概念
- 資料庫一般分為:關系型資料庫和非關系型資料庫。
- 資料庫的内部構造是一顆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 create table 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 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觸發器
太難了,略。。。。