2015-10-20 SQL 第二次课 (约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

1 、 Primary Key 约束

    SQLServer 中有五种约束， Primary Key 约束、 Foreign Key 约束、 Unique 约束、 Default 约束和 Check 约束.

    在表中常有一列或多列的组合，其值能唯一标识表中的每一行。

    这样的一列或多列成为表的主键(PrimaryKey)。一个表只能有一个主键，而且主键约束中的列不能为空值。只有主键列才能被作为其他表的外键所创建。

   创建主键约束可以右键单击表，选择设计 。

 

   选中要创建主键的列，然后单击上面的小钥匙。

 

    也可以右键需要创建主键的列，然后单击小钥匙。

 

2 、 Foreign Key 约束

    外键约束是用来加强两个表（主表和从表）的一列或多列数据之间的连接的。创建外键约束的顺序是先定义主表的主键，然后定义从表的外键。也就是说只有主表的主键才能被从表用来作为外键使用，被约束的从表中的列可以不是主键，主表限制了从表更新和插入的操作。

   右键单击需要设置外键的列（此时的表是作为从表在外键中出现），选择关系。

 

   接下来点击添加 --> 表和列规范。

 

   在主键表中选择主表和主表的主键列。

 

   设置完后保存即可。

3 、 Unique 约束

    唯一约束确保表中的一列数据没有相同的值。与主键约束类似，唯一约束也强制唯一性，但唯一约束用于非主键的一列或者多列的组合，且一个表可以定义多个唯一约束。

   右键单击要设置的列选择索引 / 键。

 

   然后单击添加按钮。

 

   选择需要设置的列，可以是一列也可以是多列的组合。

 

   关闭并保存设置。

4 、 Default 约束

    若在表中定义了默认值约束，用户在插入新的数据行时，如果该行没有指定数据，那么系统将默认值赋给该列，如果我们不设置默认值，系统默认为 NULL 。

  以学生信息表为例，在表设计器中，为性别 sex 列填写默认值男。

 

5 、 Check 约束

    Check 约束通过逻辑表达式来判断数据的有效性，用来限制输入一列或多列的值的范围。在列中更新数据时，所要输入的内容必须满足 Check 约束的条件，否则将无法正确输入。

   以学生信息表中的 sex 为例，我们要限制 sex 列的值只能为男或女。

 

 

  

关闭并保存设计。

6.选择出年月日

 select year(getdate()) //获取当前的年

select month(getdate()) //获取当前的月

select day(getdate()) //获取当前的天

 select * from employ where month(birthday)=8   // 打印出8月份过生日的员工的所有信息

 select * from employ where year(getdate())-year(birthday)>25// year(getdate())为当前年份，打印出年龄大于25岁的员工的所有信息

 select * from employ where year(birthday)=2008 and month(birthday)=8 and day(birthday)=12 //打印出生日为2008-8-12日的所有员工信息

 select dateadd(yy,100,getdate())//当天加上100年的时间，getdate()也可以换成具体的某一天比如写成:'2108/12/31'

select dateadd(mm,1,getdate())//当天加上1个月的时间

select dateadd(dd,100,getdate())//当天加上100天的时间 

select datediff(yy,getdate(),'2108/12/31')//当天距离2108/12/31还有多少年

select datediff(mm,getdate(),'2108/12/31')

select datediff(dd,getdate(),'2108/12/31')

 7.isnull的用法

 select title,content,isnull(categoryID,0) from news  //为null的categoryID用0显示出来

 8.case用法

//查找categoryID=13的state,并对state进行判断

select state,case

when(state=1)then '待审'

when(state=2)then '已审'

end as pro_state

from category where categoryID=13

 

//查找出低级的多少个，中级的多少个，高级的多少个

select Count(*) as [Count] from category

group by

case

when categoryID<15 then '低级'

when categoryID between 15 and 20 then '中级'

else  '高级'

end

 

//查出category 中的CategoryID,name以及判断每个categoryID 的大小

select CategoryID,name,

case

when categoryID<15 then '低级'

when categoryID between 15 and 20 then '中级'

else  '高级'

end as categoryRange from category

9.Category与新表cr（select结果集作为新表），做左联接的应用

Select c.*, IsNull(cr.Count,0) as [Count]

From Category c

Left Join

(

       select cr.CategoryID,Count(*) as [Count] from CategoryRelative cr

       right Join Entry e ON cr.ChildID = e.ID and e.state <> 2 and e.IsActive = 1

       where cr.blogid = 8785

       Group by cr.CategoryID

) cr ON c.CategoryID = cr.CategoryID

Where BlogID = 8785 and CategoryType = 1 

10.exists的用法 

//查出Category 表中categoryID不等于n.categoryID的c.categoryID,c.[name]

第一种查询语句：select c.categoryID,c.[name] from Category c  left join News n on c.categoryID=n.categoryID where n.categoryID is null  

第二种查询语句：select categoryID,[name] from category where not exists

(

   select * from News where category.categoryID=News.categoryID

)

 

*当News 表中categoryID无大量重复时第一种性能无疑是最好的，反之，第二种性能好。 

11.SQL 语句转换格式函数Cast、Convert

　　CAST和CONVERT都经常被使用。特别提取出来作为一篇文章，方便查找。

　　CAST、CONVERT都可以执行数据类型转换。在大部分情况下，两者执行同样的功能，不同的是CONVERT还提供一些特别的日期格式转换，而CAST没有这个功能。

　　既然CONVERT包括了CAST的所有功能，而且CONVERT还能进行日期转换，那么为什么需要使用CAST呢?实际上，这是为了ANSI/ISO兼容。CAST是ANSI兼容的，而CONVERT则不是。

　　语法：

　　CAST (expression AS data_type [ (length ) ]) 

　　CONVERT (data_type [ ( length ) ] , expression [ , style ]) 

　　示例：

　　SELECT 'AB' + 1    --此语句报错，在将 varchar 值 'AB' 转换成数据类型 int 时失败。

 　　SELECT 'AB' + CAST(1 AS varchar)    --输出 AB1 

　　SELECT 'AB' + CONVERT(varchar,1)    --输出 AB1

　　CAST和CONVERT都能转换时间：

　　SELECT CONVERT(DateTime,'2011-07-11')    --输出 2011-07-11 00:00:00.000

 　　SELECT CAST('2011-07-11' AS DateTime)    --输出 2011-07-11 00:00:00.000

　　但是时间转字符串，CAST没有CONVERT这么多花样：

　　SELECT CONVERT(varchar,GETDATE(),5)    --输出 01-07-13

　　SELECT CONVERT(varchar,GETDATE(),111)  --输出 2013/07/01

　　SELECT CONVERT(varchar,GETDATE(),1)    --输出 07/01/13

 　　SELECT CAST(GETDATE() AS varchar)    --输出 07 1 2013 9:56PM

12.索引：

http://www.cnblogs.com/AK2012/archive/2013/01/04/2844283.html

详细内容可以通过以上网页了解。

表的存储由两部分组成，一部分用来存放数据页面，另一部分存放索引页面。

索引的功能类似百科全书中的目录，使用索引是快速查找数据库中实际信息的一种方法。

索引分为：

聚集索引--------每一个表只能有一个聚集索引，它对应的表按照其索引进行物理排序，对于百科全书，聚集索引就类似于书的页码，按页码顺序保存着百科全书的信息。

非聚集索引-------每一个表可以有多个非聚集索引，对于百科群书，非聚集索引指的是百科全书后面的关键字目录。

13.视图

视图是一种虚拟表，它的使用基本和表的使用类似，主要目的是控制用户所要显示的数据。

创建一个视图：

Eg:

create view CurrentEmployees_vw

as

select categoryID,[name],age from Category

where state=1

 

 

操作方法：

1、右键添加新视图；

2、选择要查询的表格；

3、选择对应的关联字段；

4、保存即可生成视图，即相当于全联接的SQl语句。

14.存储过程

1）优点：在创建时进行编译，以后每次执行存储过程不需要再编译，而一般的SQL语句要每执行一次编译一次（所以一些复杂逻辑的SQL建议写在存储过程里面）；存储过程过重复使用，可减少数据库开发人员的工作量（所以一些经常被调用到的SQL建议写在存储过程里面）；安全性高，可设定只有某些用户才具有指定存储过程的使用权。

 

1)基本语法

Eg:

create proc procCategorySelect

(

  @name varchar(200),

  @age  int

)

as

select * from Category where [name]=@name and age=@age

1)  查看结果exec procCategorySelect 'xudads',11

2)       更改/删除存储过程

ALTER proc      //修改存储过程

Drop proc      //删除存储过程

 

4）流控制语句

Eg:

create proc procNewsInsert

(

   @creatTime datetime             //设置一个CratTime的变量,执行时加入实参

)

as

if datediff(dd,@creatTime,getdate())>7  /*creatTime距离当天大于天*/

begin;

select * from News

end;

else

begin;

select top 3* from News

end;

 

执行：exec procNewsInsert '2009-05-10 11:36:43.810'

 

结果：

 

 15. 触发器

触发器是一种特殊的存储过程，它是在数据在进行增、删、改的时候同时进行的操作，相当于一个事件。

新建一个触发器：

 

写的脚本：

Eg1:

create trigger trigCategoryDelete

   ON  Category

   after delete /* 有三种INSERT,DELETE,UPDATE*/

AS

begin

    select * from deleted /*在删除后同时查出删除后的内容*/

end

GO

 执行：

 

 Eg2:

create trigger trigCategoryDelete

   ON  Category

   instead of delete /* instead of，表示当执行删除的时候这个删除语句被begin下面的语句所代替*/

AS

begin

    declare @id int

select @id=id from deleted

delete news where caID=@id

delete category where id=@id

end

GO

 

执行：

delete from category where id=5 /*这样就可以删除category里面id=5的东东的同时删除news里面id=5的东东了 */

16.数据库备份与还原

一、SQL数据库的备份：

1、依次打开 开始菜单 → 程序 → Microsoft SQL Server 2008 → SQL Server Management Studio → 数据库：Dsideal_school_db既是我们需要备份的学籍数据库

 

图（1）

2、选择要备份的数据库“Dsideal_school_db”，点击鼠标右键 → 任务 → 备份，如图（2）：

 

图（2）

3、在打开的“备份数据库 —Dsideal_school_db”对话框中，先点击删除，然后点击“添加”，如图（3）：

 

图（3）

4、在弹出的“选择备份目标”对话框中，如图（4）

 

图（4）

5、选择好备份的路径（如D盘的江西-学籍-数据库备份文件夹中），文件类型选择“所有文件”，“文件名”那个位置填写上您要备份的数据库的名字（最好在您备份的数据库的名字后面加上日期，以方便以后查找），之后连续点击“确定”按钮即可完成数据库的备份操作，如图（5）：

 

图（5

二、SQL数据库的还原：

1、选择您要还原的数据库“Dsideal_school_db”，点击鼠标右键 → 任务 → 还原 → 数据库，如图（6）：

 

图（6）

2、在出现的“还原数据库 — school_web_db”对话框中选择“源设备”，然后点击后面的“…”按钮，如图（7）：

 

图（7）

3、在出现的“指定备份”对话框中，点击“添加”按钮，如图（8）：

 

图（8）

4、找到数据库备份的路径，选择您所要还原的数据库“Dsideal_school_db”（注意：文件类型选择所有文件），然后连续“两次”点击“确定”按钮，如图（9）：

 

图（9）

5、在出现的“还原数据库 — Dsideal_school_db”对话框中，勾选上“选择用户还原的备份集”下的数据库前的复选框，如图（10）

 

图（10）

6、然后选择“选项”，勾选上“覆盖现有数据库”，如图（11）：

 

图（11）

三、还原数据库问题解决方案

 

在还原数据库“Dsideal_school_db”时，有时会遇见上图中的问题“因为数据库正在使用，所以无法获得对数据库的独占访问权”，此时我们可以按照以下步骤解决此问题

1、右键数据库“Dsideal_school_db”，然后选择“属性”，如下图

 

2、在出现的“数据库属性 — Dsideal_school_db ”对话框中，选择“选项”，在“其他选项”下的“状态”中找到“限制访问”。选择“SINGLE_USER”，点击“确定”按钮，如下图：

 

3、按照正常还原数据库的步骤，还原数据库。