SQL Senior

SQL Senior Part

一、        数据库的设计

1、  数据库设计的三大范式

1）1NF：原子性；

2）2NF：除了满足1NF外，每一列都依赖于主键；

3）3NF：除了满足1NF和2NF外，确保每列都和主键有直接关系；

但是数据库设计的三大范式要和数据库性能结合起来，适当允许少量冗余，才是最合适的数据库设计方案；

2、  设计数据库的步骤

1）  需求分析

2）  概要设计

3）  详细设计

3、  绘制E-R图

1）  实体-关系模型

E-R图是实体关系图，是用来清晰直观地表示实体与实体之间的关系的逻辑图；

椭圆—属性；

矩形—实体；

菱形—关系；

箭头---指向引用的实体；

直线—属性与实体的集；

2）  如何将E-R图转化成表

1）  将各实体转化成表

2）  实体的属性转化成列名

3）  关系转化成主外键

4）  主键设为主键，表示标识列

4、  数据规范化

1）  设计问题：为了避免设计问题，应尽可能避免信息重复、更新异常、插入异常、删除异常；

2）  规范设计：1中介绍的三大范式

3）  规范化与性能的关系

不能仅仅注重三大范式，要适当增加数据冗余，让规范化与性能保持一个最优状态，以达到最佳的设计方案；

二、        数据库的实现

1、  设计中常用的基本SQL语句

Insert

Delete

Update

Select

2、  使用SQL语句创建和删除数据库

1）  创建数据库

If exists (select * from sysdatabases where name =’db_wanglei’)

Drop  database  db_wanglei

Go

 

Create  database  db_wanglei

On primary

(

            Name = ‘db_wanglei_data’,

            Filename=’D:/database/db_wanglei_data.mdf’,

            Growth=15%

)

Log on

(

            Name=’db_wanglei_logData’,

            Filename=’D:/database/db_wanglei_logData’,

            Growth=15%

)

Go

 

2）  删除数据库

If  exists (select  *  from  sysdatabase  where  name =’db_wanglei’)

Drop  database  db_wanglei

go

3、  使用SQL语句创建和删除表

1)       创建表

If  exists (select  *  from sysobjects  where name =’userInfo’)

Drop  table userInfo

go

Create  userInfo

（

           userId  int  primary key  identity (1,1)  not  null,

           username  varchar(20) not null,

    userPwd   varchar(20) not null,

           userEmail           varchar(20) not null,

           userTelephone varchar(20) not null

）

 

3）  删除表

If  exists (select  *  from sysobjects  where name =’userInfo’)

Drop  table userInfo

go

4、  使用SQL语句创建和删除约束

1）  创建约束

Alter  table  tableName  add  constraint  ConstraintName  constraintType  contraintDesciption

Example:

---primary key constraint

Alter   table userInfo

Add constraint PK_userId primary key (userId)

---unique constraint

Alter  table  userInfo

Add  constraint  UQ_userId  unique (userId)

--default constraint

Alter  table userInfo

Add  constraint DF_Address  default (‘北京海淀’)

--check constraint

Alter  table  userInfo

Add  constraint  CK_userEmail  check(userEmail like ‘%@%’)

--foreign key constraint

Alter  table diaryInfo

Add  constraint  FK_userInfo forerign key (diaryUid) references userInfo(userId)

 

2）  删除约束

Alter  table  tableName 

Drop  constraint  constraintName

5、  使用SQL创建登陆

1）  创建登陆账户

Exec sp_grantlogin ‘window域名/账户’

Exec sp_addLog  ‘账户名’ ,‘密码’

 

2）  创建数据库用户

Exec sp_grantdbaccess ‘登陆账户’,’数据库用户’

 

3）  向数据库用户授权

Grant 权限[on 表名] to 用户

 

三、        T-SQL编程

1、  变量的使用

1）  局部变量

Declare  @userName  varchar (20)

Set @userName =’wanglei’

Select  * from  userInfo  where username = @userName

Go

注：局部变量赋值都要使用set关键字来设置；

2）  全局变量

全部变量通常是系统变量,无需声明，可以直接使用；

@@error—错误号

 

@@identity –最后一次插入的标识列值

 

@@Language –返回使用语言的名称

 

@@Max_connections –可以创建的同时连接的最大数目

 

@@RowCount –受上一个SQL语句影响的行数

 

@@ServerName ---本地服务器名称

 

@@Timeticks—当前计算机上每刻度的微妙数

 

@@version ---当前使用SQL版本信息

 

@@Transcount ---当前打开的事务数

 

 

2、  输出语句的编写

Print  toBeOutString

 

3、  批处理

可以提高程序的执行效率，结束标志是Go；

4、  逻辑控制语句的编写

1）  IF-ELSE

在这里和Java中的if-else语法基本一样，只不过原来的括号换成了begin-end；而且功能一样。

 

 

2）  WHILE

While 是用于执行循环操作，和Java中的类似；

 

3）  CASE

 

注：case后面必须有end结束，而且在两个when之间不能有逗号；

四、        高级查询

1、  简单子查询

 

注：子查询和运算符连用时，必须保证子查询的返回值不能超过一个；

2、  IN和NOT IN子查询

In后面加的是范围，是一组值的罗列，而后面紧跟的这组值不仅仅是int类型，什么类型都可以；

以varchar类型举例：

 

而not in 则恰恰相反是查询不在罗列的值之内的数据；

 

注：in后面的子查询可以返回多条记录而不像比较运算符；

3、  EXISTS和NOT EXISTS子查询

EXISTS和 NOT EXISTS多结合if-else进行逻辑设计，判断是否存在，返回类型为bit类型；

If  exists (查询条件)

---查询到执行代码

Else

---未查询到所执行的代码

 

五、        事务、索引以及视图

1、  事务

1）  为什么需要事务

银行转账是个例子，由此可见事务的重要性，不容忽视；

2）  什么是事务

我理解的事务就是“一荣俱荣 一损俱损”，多条语句要么执行，要么回滚；

3）  事务的特征

原子性

一致性

隔离性

持久性

4）  如何创建事务

Begin  transaction

---事务代码

事务的例子：

 

2、  索引

1）  为什么要使用索引

之所以使用索引，是因为索引能够在查询时高效执行，就如同我们在使用新华字典在查找字一样，快速，提高数据库的性能！

2）  什么是索引

索引就是在数据库中，某个表中一列或者若干列值的集合和相应的指向表中物理标示这些物理标示这些值的数据页的逻辑指针清单，使用索引可以提高执行效率，改善数据库的性能；

换句话说，索引就是数据查询的路由；而索引页就是指类似于字典的目录页，它是用来存放行的地址指针；

3）  如何创建索引

索引的分类：

     主键索引：当用户在定义一个主键后，会自动创建一个主键索引；

     唯一索引：不允许有两行是相同的索引值；

     聚集索引：它是指表中物理顺序和逻辑顺序相同，而且一个表只能包含一个聚集索引；

     非聚集索引：它和聚集索引的区别是一个表中可以创建多个；

4）  如何创建索引

Create  index  index_name

On  table （column_name）

 

3、  视图

1）  为什么使用视图

使用视图便于查看一个表或多个表的数据，而且便于数据的部分隐藏；

2）  什么是视图

视图就是一张虚拟表，它通常是由一张或多张数据库表的某些列和在一起创建的；便于直观地将数据展示给用户，开发数据更容易，便于应用程序的维护，限制数据的检索更容易；

3）  如何创建视图

Create   view

As

Select 语句

 

六、        存储过程

1、  什么是存储过程

存储过程就相当于C语言中的函数，便于结构化执行，模板化设计大大提高了执行的效率，减少网络流量（请求时只需要发送一条请求函数调用的指令就可以，而不需要一条一条将数据发送到服务器端），安全机制高（类似于Java里定义的封装一样，这样可以调用某个存储过程直接执行某项功能，而不需要了解其内部的具体实现）。

2、  常用的系统存储过程

Exec sp_databases

Exec sp_helpdb

Exec sp_renamedb

Exec sp_tables

Exec sp_password

Exec sp_stored_procedures

 

3、  自定义存储过程

1）  创建不带参数的存储过程

 

2）  创建带参数的存储过程

 

3）  创建带返回值的存储过程

 

七、        触发器

1、  为什么需要触发器

在多表之间执行特殊的业务规则或保持复杂的数据逻辑关系。

2、  触发器的定义

它是一种特殊的存储过程，同时也具有事务的功能，它能够在对表进行插入、更新或删除操作时自动执行的存储过程；

3、  如何创建触发器

1）  创建INSERT触发器

当向表中插入数据时触发，自动执行触发器所定义的sql语句；

2）  创建DELETE触发器：当对表执行delete操作时，自动执行触发器所定义的sql语句；

3）  创建UPDATE触发器：当更新表中记录时触发，自动执行触发器所定义的sql语句；

八、        锁机制

单独拿出来和大家共享；