数据库知识总结

数据库知识总结

数据库基础

1.SQL语言包括 数据定义、数据操纵(Data Manipulation)、数据控制(Data Control)

• 数据定义：Create Table, Alter Table, Drop Table, Create/Drop Index等

• 数据操纵：select, insert, update, delete

• 数据控制：grant, revoke

2.SQL常用命令：

CREATE TABLE Student( 
    ID NUMBER PRIMARY KEY,
    NAME VARCHAR2(50) NOT NULL
);//建表

CREATE VIEW view_name AS SELECT * FROM table_name;//建视图

CREATE UNIQUE INDEX index_name ON table_name(col_name);//建索引

INSERT INTO table_name (column1,column2,…) values(exp1,exp2,…);//插入

INSERT INTO view_name (column1,column2,…) values(exp1,exp2,…);//插入视图实际影响表

UPDATE table_name SET name = 'zhangsan' condition;//更新数据

DELETE FROM table_name WHERE condition;//删除

GRANT (select,delete,…) ON (对象) TO USER_NAME [WITH GRANT OPTION];//授权

REVOKE (权限表) ON(对象) FROM USER_NAME [WITH REVOKE OPTION] //撤权

SELECT E.NAME, S.NAME 
FROM EMPLOYEE E S 
WHERE E.SUPERName = S.Name;//列出工作人员及其领导的名字

3.事务：是一系列的数据库操作，是数据库应用的基本逻辑单位。

• 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。

• 一致性：事务的执行使得数据库从一种正确状态转换成另一种正确状态。

• 隔离性：在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何其他事务。

• 持久性：事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后有了其他故障，事务的处理结果也会得到保存。

4.锁：共享锁、互斥锁

两段锁协议：阶段１：加锁阶段 阶段２：解锁阶段

5.死锁：事务循环等待数据锁，则会死锁。

死锁处理：预防死锁协议，死锁恢复机制

6.存储过程：存储过程就是编译好了的一些sql语句。

• 存储过程因为SQL语句已经预编绎过了，因此运行的速度比较快。

• 可保证数据的安全性和完整性。通过存储过程可以使没有权限的用户在控制之下间接地存取数据库，从而保证数据的安全。通过存储过程可以使相关的动作在一起发生，从而可以维护数据库的完整性。

• 可以降低网络的通信量。存储过程主要是在服务器上运行，减少对客户机的压力。

• 存储过程可以接受参数、输出参数、返回单个或多个结果集以及返回值。可以向程序返回错误原因

• 存储过程可以包含程序流、逻辑以及对数据库的查询。同时可以实体封装和隐藏了数据逻辑。

7.触发器： 当满足触发器条件，则系统自动执行触发器的触发体。

• 触发时间：before, after
• 触发事件：insert, update, delete
• 触发类型：行触发、语句触发

8.内联接,外联接区别？

• 内连接是保证两个表中所有的行都要满足连接条件，而外连接则不然。

• 在外连接中，某些不满条件的列也会显示出来，也就是说，只限制其中一个表的行，而不限制另一个表的行。分左连接、右连接、全连接三种。

SQL实用例句

• 找出表ppp里面num最小的数，不能使用min函数。

select * from ppp where num <= all(select num from ppp);

或者

select top 1 num from ppp order by num;

• 找出表ppp里面最小的数，可以使用min函数。

select * from ppp where num = (select Min(num) from ppp);

• 选择表ppp2中num重复的记录。

select * from ppp2
where num in (select num from ppp2 group by num having (count(num) > 1));

• 写出复制表、拷贝表和四表联查的SQL语句。

复制表（只复制结构，源表名：A，新表名：B）

select * into B from A where 1 = 0;

拷贝表（拷贝数据，源表名：A，新表名：B）

select * into B from A; 

四表联查

select * from A,B,C,D where 关联条件;

• 在SQL Server中如何用SQL语句建立一张临时表？

create table #Temp(字段1 类型,字段2 类型...); //临时表要在表名前面加一个#

• 有数据表A，有一个字段LASTUPDATETIME,是最后更新的时间，如果要查最近更新过的记录，如何写SQL语句？

select * from A where LASTUPDATETIME = (select max(LASTUPDATETIME) from A);

• 有一个数据表，只有一个表，包含着1000个记录，你能想出一种解决方案来把第5到第7行的记录取出来吗？不要使用航标和索引。

建立数据库

declare @i int
set @i=1
create table #T(userid int)
while (@i<=10)
begin
insert into #T
select @i

set @i=@i+1
end

select userid from
(
    select top 3 userid from (select top 7 userid from #T order by
     userid)Ta order by userid desc
) TB order by userid

删除数据库

drop table #T

提取数据

select top 3 userid from T where userid not in (select top 4 userid from
 T order by userid) order by userid

或者

select top 7 userid from T where userid > ANY (select top 4 userid from
    T order by userid) order by userid

或者

select top 7 userid from T where userid > ALL (select top 4 userid from
    T order by userid) order by userid

• 要查数据表中第30到40条记录，有字段ID，但是ID并不连续。如何写SQL语句？

select * from tb a
where exists (select 1 from (
    select top 10 id from (select top 40 id from tb order by id desc) as c
    ) as b where b.id = a.id)