sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

时间:2022-12-29 14:04:04

 Primary Key 约束

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

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

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

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 Foreign Key 约束

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

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 

   设置完后保存即可。

 

 Unique 约束

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

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

   然后单击添加按钮。

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

   关闭并保存设置。

 Default 约束

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

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 Check 约束

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

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

  sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 

关闭并保存设计。

 

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

 

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

操作方法:

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'

 

结果:

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 15. 触发器

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

新建一个触发器:

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

写的脚本:

Eg1:

create trigger trigCategoryDelete

   ON  Category

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

AS

begin

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

end

GO

 执行:

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

 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既是我们需要备份的学籍数据库

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(1)

2、选择要备份的数据库“Dsideal_school_db”,点击鼠标右键 → 任务 → 备份,如图(2):

sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原) 

图(2)

3、在打开的“备份数据库 —Dsideal_school_db”对话框中,先点击删除,然后点击“添加”,如图(3):

sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原) 

图(3)

4、在弹出的“选择备份目标”对话框中,如图(4)

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(4)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(5

二、SQL数据库的还原

1、选择您要还原的数据库“Dsideal_school_db”,点击鼠标右键 → 任务 → 还原 → 数据库,如图(6):

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(6)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(7)

3、在出现的“指定备份”对话框中,点击“添加”按钮,如图(8):

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(8)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(9)

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

图(10)

6、然后选择“选项”,勾选上“覆盖现有数据库”,如图(11):

sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原) 

图(11)

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

sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原) 

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

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

sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原) 

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

 sql2(约束、日期、isnull、case、exists、cast\convert、索引、视图、存储过程、触发器、备份与还原)

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