Sql Server数据库基础教学

 

SQL2005教案

(40课时)

第一节课  sql2005的安装,数据库和表的创建以及基本概念

1.图示说明

      

  

 

 


2.基本概念

  数据库(DB,Database):数据库是长期储存在计算机内,有组织,可共享的大量数据的集合.

  数据(Data):数据是数据库中存储的基本对象,它是描述事物的符号记录.数据是信息的载体,信息是数据反映出的一种关系.

  数据库管理系统(DBMS,Database Management System):是数据库的机构,是一种软件,负责数据库中的数据组织,数据操作,数据维护,控制及保护和数据服务等.

  数据库系统(DBS):包含数据,数据库,数据库管理系统,数据管理员等.

3.数据库系统的发展

      人工管理阶段

      文件系统阶段

      数据库系统阶段

4.SQL2005的版本及常用数据库

      SQL2005版本:

    * SQL Server Enterprise Edition:具有企业级功能的SQL Server版本,适用于大型企业以及大型数据库或数据仓库的服务器版本。
    * SQL Server Standard Edition:具有标准功能的SQL Server版本,适用于一般企业的服务器版本。
    * SQL Server Workgroup Edition:自SQL Server 2000开始才有的版本,专为工作组群或部门所设计,适用于较小规模的组织。
    * SQL Server Web Edition:自SQL Server 2008开始才有的版本,专为Web 服务器与Web Hosting所设计,功能上较Workgroup Edition少一些。
    * SQL Server Express Edition:免费的SQL Server版本,适用于小型应用程序或是单机型应用程序,但在功能上有设限,如只能使用一颗处理器,以及最大数据库大小为4GB等。请见SQL Server Express。

     目前常用数据库:

      开源公司的mysql
      微软的mssql我们也经常说是SQL SERVER
      oracle公司的oracle
      SYBASE的powerbuilder
      IBM的DB2
      BORLAND公司的产品interbase,
      美国Informix软件公司的Informix

  数据库,表的创建

1.      创建数据库goods

2.      创建3个表

   

3.      基本概念: 表、行、列、属性、字段、域、实体.

4.      数据库重命名,表结构的修改.

5.      SQL(Structured Query Language-结构化查询语言):是由IBM公司在70年代开发的关系型数据库原型System R的一部分,现在已成为关系型数据库系统通用的查询语言,它是数据库系统的通用语言.SQL语言主要包括数据定义,数据控制,数据操纵和数据查询等功能,其中最重要的是数据查询功能.

6.      数据定义语言DDL(Data Definition Language)

<1>创建数据库

create database goods

         <2>选择数据库

           use goods

          <3>创建表

             create table users(

User_ID int,

 User_Name nvarchar(50),

 Email nvarchar(50),

 Card  nvarchar(50),

 Tell  nvarchar(50),

 Address nvarchar(50))

<4>修改表

 --添加一列

          alter table users

add remark varchar(50)

--修改一列的数据类型

          alter table users

alter column remark varchar

--删除一列

          alter table users

drop column remark    

<5>删除表

drop table Users

<6>删除数据库(注意确定一下当前正在使用的数据库)

drop database t

第二节课  数据处理语句DML(Data Manipulation Language)

1.      插入数据

insert into users(user_id,user_name,user_age,email,card,tell,

address )                                                                                  values(1234,'jodon',35,'jodon123@sina.com','1234567890','138888888','美国') 

注意: 

(1)必须用逗号将各个数据分开,字符型数据要用单引号括起来,且into可以省略。

(2)INTO子句中没有指定列名,则新插入的记录必须在每个属性列上均有值,且VALUES子句中值的排列顺序要和表中各属性列的排列顺序一致。

(3)将VALUES子句中的值按照INTO子句中指定列名的顺序插入到表中。

(4)对于INTO子句中没有出现的列,则新插入的记录在这些列上将取空值(remark).

(5)当插入的数据包含了每一个列,则可以省略列名.如下:

Insert into users

values(1234,'jodon',35,'jodon123@sina.com','1234567890',

'138888888','美国')

2.      修改记录

 <1> update users

set user_age=20

      <2>update users

        set user_age=26

        where user_id=1234

      <3>update users

         set user_age=user_age+5

3.      删除记录

      <1>delete

          from users

  where user_id=4321

      <2> delete

          from users

4.  主键约束

<1>

<2> create table users(

User_ID int primary key,

 User_Name nvarchar(50),

 Email nvarchar(50),

 Card  nvarchar(50),

 Tell  nvarchar(50),

 Address nvarchar(50))

5.  上机练习

<1>练习课堂内容

 

<2>建立学生数据库student,并建立四张表:学生基本信息表,课程设置表,选课表,成绩表,并练习所学的SQL语句

第三节课    简单查询语句

1.      查询users表中所有记录的所有字段

       select *--也可以把所有的列都写上

   from users

   注释补充:

   --注释一行

   /*……*/注释一段

2.      查询所有记录的姓名和年龄

       select user_name,user_age

   from users

   别名补充:

        select user_name as 姓名,user_age as 年龄

   from users

    --其中as可以省略

3.      查询年龄超过20的所有记录

       select *

       from users

   where user_age>20-->=  <   <=  <>  !=  =

4.      查询年龄在20和50之间的记录

<1>select *

           from users

       where user_age>=20 and user_age<=50

        <2>select *

           from users

           where user_age between 20 and 50

5.      查询年龄大于30或年龄小于20的记录

       select *

       from users

   where user_age <20 or user_age>30

6.      查询年龄不等于30的记录

<1>select *

           from users

      where user_age <>30--!=

       <2>select *

from users

where not user_age =30

7.      查询年龄不等于20也不等于30的记录

<1> select *

from users

where user_age !=30 and user_age<>20

<2> select *

from users

where not (user_age =30 or user_age=20)

8.      查询年龄等于20或者等于30的记录

<1> select *

from users

where user_age =30 or user_age=20

       <2> select *

from users

where user_age in (20,30)

       上面7也可以用下面方式实现

       select *

       from users

       where user_age not in (20,30)—not 也可以放在user_age之前

 

 

9.      查询users表中有多少条不同的记录

      select distinct *

  from users

10.  查询users表中都有哪些年龄的记录

      select  distinct user_age

  from users

模糊查询(通配符’_’,’%’)

11.  查询所有姓姚且名字为两个字的记录

      select  *

      from users

  where user_name like '姚_'

12.  查询所有姓姚的记录

      select  *

      from users

  where user_name like '姚%'

13.  查找第二个字为“小”的所有记录

      select  *

      from users

  where user_name like '%小%'

14.  查询所有不姓姚的记录

      select  *

      from users

  where user_name like '[^姚]%'

15.  查询所有姓姚或姓王的记录

      select  *

      from users

  where user_name like '[姚,王]%'

16.  查询所有姓姚且第二个字不为大或小的记录

      select  *

      from users

  where user_name like '姚[^大,小]%'

17.             查询姓名不为空的记录

       select  *

       from users

   where user_name is not null

上机练习:

1〉           练习课堂所讲内容

2〉2人一组以student数据库为例互相设计题

第四节课

1.      修改users表,添加新列users_sex(性别)

        alter table users

   add  user_sex nchar(10)

2.      查询出前两条记录

        select top 2 *

   from users

3.      查询users表中的所有记录,并按年龄降序或升序排列

        select *

        from users

   order by user_age asc—asc可以省略不写,默认即为asc

  

        select *

from users

order by user_age desc

4.      查询所有记录,并按年龄升序排列,年龄相同按card降序排列

        select *

        from users

   order by user_age asc , card desc

5.      查询users表中男女各多少人

        select count(*)

        from users

   group by user_sex  

   注意:使用分组的时候,查询的列必须出现在聚合函数中或者必须出现在group by 后面,下面的写法是错误的:

        select user_name,user_sex

        from users

   group by  user_sex

6.      查询users表中男女各多少人,且只有当人数超过3人才输出信息

        select count(*)

         from users

   group by user_sex having count(*)>3

7.      查询users表中最大的年龄是多少

        select max(user_age)

   from users

8.      查询users表中最小的年龄是多少

        select min(user_age)

   from users

9.      求users表中所有人年龄的总和

        select sum(user_age)

from users

10.  求users表中所有人的平均年龄

        select avg(user_age)

   from users

      连接查询  

1.      笛卡尔积

        select *

   from ware,shopping

2.      等值连接

        select *

        from ware,shopping

   where ware_code=code

  

        select *

        from ware join shopping

   on ware_code=code

3.      不等值连接

            select *

         from ware,shopping

where ware_code<>code

 

        select *

        from ware  join shopping

  on ware_code<>code

4.      查询所有已销售商品的名称,进价,销售价,销售数量和日期

        select name,startprice,saleprice,shopping.amount,shopping.date

        from ware join shopping

   on ware_code=code

   注意:当连接的表中出现相同列名时,必须加表名来区分

5.      查询所有已销售商品的名称,进价,销售价,销售数量和盈利额

        select  name,startprice,saleprice,shopping.amount,

            shopping.amount*(saleprice-startprice) 盈利

        from ware join shopping

   on ware_code=code

6.  查询所有已销售商品且盈利的名称,进价,销售价,销售数量和盈利额

     select name,startprice,saleprice,shopping.amount,

     shopping.amount*(saleprice-startprice) as 盈利

         from ware join shopping

         on ware_code=code

    where saleprice-startprice>0

7.      自然连接(特殊的等值连接,去掉了重复的列)

        select ware.*,shopping.amount,shopping.saleprice,shopping.date

        from ware inner join shopping

   on ware_code=code

   补充:inner join 也叫内连接,inner也可以省略不写,即默认为内连接

第五节课

      外连接

1.      左外连接

         select *

         from ware left join shopping

    on ware_code=code

2.      右外连接

         select *

         from ware right join shopping

    on ware_code=code

3.      全外连接

         select *

from ware full join shopping

on ware_code=code

4.      自连接

<1>查找年龄比姚明小的所有记录

            select a.*

            from users as a,users as b

   where a.user_age<b.user_age and b.user_name='姚明'

<2>查找年龄不比姚明大的所有记录

   错误写法:

            select a.*

            from users as a,users as b

   where a.user_age<=b.user_age and b.user_name='姚明'

   正确写法:

            select a.*

            from users as a,users as b

   where a.user_name<>b.user_name and a.user_age<=b.user_age

   and  b.user_name='姚明'

5.      交叉连接

         select *

   from ware cross join shopping

   说明:其实就是笛卡儿积

6.      集合查询

<1>并(union)

            select ware_code,date

            from ware

            union

            select code,date

   from shopping

--注意:所查询的目标列必须相同

<2>交(intersect)

           select ware_code

           from ware

           intersect

           select code

           from shopping

<3>差(minus)—说明:SQL2005不支持minus运算,oracle支持

第六节课  复习

        以student数据库为例复习前面所讲内容

 

第七节课  子查询—嵌套查询(所有的链接查询都可以用子查询实现)

1.      查询出王小明的所有成绩

步骤:

<1>查询出王小明的学号

   select  stu_id

   from stu

   where stu_name=’王小明’

<2>查询出上面所查询出的学号所对应的课程成绩

   select  s_score

   from   score

   where  s_id=stu_id

子查询:

   select s_core

   from score

   where s_id=

   (select stu_id                          

from stu

where stu_name=’王小明’)

按照处理方式分为相关子查询和无关子查询

相关子查询:是一种子查询和外层查询相互交叉的数据检索方法(一般不用)

无关子查询:子查询中不包含对外层查询的任何引用。

2.      查询所有选修了编号为C123课程的学生姓名

select name

from stu

where stu_id in

(select c_id

 from xunke

 where kc_id=’N123’)

        单行子查询—子查询返回的是一行,因此可以可以把子查询作为一个常量对待

3.      查询身份证号为4105221996212的学生都所选课程的编号

select  kc_id

from xuanke

where  c_id=

(select  stu_id

 from stu

 where code=’4105221996212’)

注意:如果子查询的结果不是单个值,则系统会提示错误。

4.      查询年龄最大的学生的所有信息

select  *

from   stu

where  stu_age=

(select  max(stu_age)

 from stu)

5.      查询年龄比张三小的所有学生信息

select  *

from  stu

where  stu_age<

(select stu_age

 from stu

 where stu_name=’张三’)

        多列子查询:如果子查询返回多个列的数据,就称为多列子查询。(一般很少应用)

6.      查询男生中年龄最大的学生的学号和姓名

select  stu_id, stu_name

from  stu

where  (stu_id, stu_age) in

(select stu_id, max(stu_age)

 from stu

 group by stu_sex)

        多行子查询:查询返回单列多行数据的子查询称为多行子查询。

7.      查询所有有成绩的学生信息

select  *

from  stu

where  stu_id  in

(select  s_id

 from  score

 where  not s_fenshu is null)

8.      查询所有选修了C语言的学生信息

select  *

from stu

where  stu_id  in

(select  s_id

 from  course, xuanke

 where  course.kc_id=xunanke.kc_id and  course.name=’C语言’)

9.      查询所有没有选修C语言的学生信息

select  *

from stu

where  stu_id  not  in

(select  s_id

 from  course, xuanke

 where  course.kc_id=xunanke.kc_id and  course.name=’C语言’)

10.   查询年龄不小于所有女同学的男同学的学号和姓名

select  stu_id, stu_name

from  stu

where  stu_sex=’男’  and  stu_age>=any

(selelct  stu_age

 from  stu

 where  stu_sex=’女’)

11.  查询年龄比所有女同学都大的男同学的学号和姓名

select  stu_id, stu_name

from  stu

where  stu_sex=’男’  and  stu_age>all

(select  stu_age

 from  stu

 where  stu_sex=’女’)

        exists使用:存在检查—即只要子查询返回的结果不为空,则认为是真,反之认为是假。(有可能子查询和外层查询没有任何关系)

12.    select  *

              from  stu

              where  exists

              (select  *

               from   score)

 

             select  *

              from  stu

              where  not  exists

              (select  *

               from   score)

        多层嵌套:

13.    查询选修了C语言的学生信息

select  *

      from  stu

      where  stu_id=

      (select  xuanke.id

       from   xuanke

       where  kc_id=

       (select  kc_id

        from  course

        where  kc_name=’C语言’))

相同表结构之间拷贝数据

14.   insert  into  stu1

values(select * from stu2)

15.   上机练习.                                                                                                                                                                                                                                                       

第八节课  T-SQL

1.      变量

->全局变量:由系统定义和维护,名称以两个@字符开始;

->局部变量:由用户定义和赋值,名称以单个@字符开始;

《1》全局变量

     --使用全局变量记录SQL SERVER 服务器活动状态,对用户来讲是只读的。

 常用全局变量举例:

 @@ERROR--这个变量包含当前连接发生的最后一次错误的代码。在执行的语句没有错误时,@@ERROR变量的值是0。

               select @@error

from student—先故意写错表名查询,然后再去掉from直接查询

 @@ROWCOUNT—存储最近查询的记录数。    

select *

from stu

 

select @@rowcount

 @@VERSION—数据库版本信息

     select @@VERSION

《2》局部变量

     声明: DECLARE    变量名称    数据类型

     declare  @count  int, @name  char(10)

     赋值:SET  变量名称=变量值

     set  @count=0

 

     declare  @max_age  int

     select  @max_age=max(stu_age)

     from  stu

2.      运算符

《1》   算术运算符

+  -   *   /   %

《2》   比较运算符(关系运算符)

>  <  =  >=  <=  <>  !=

《3》   逻辑运算符

and  or   not

&   |    ~    ^

3.      函数

《1》   系统函数

DB_NAME:  获得当前使用的数据库名

            select db_name()

HOST_NAME:  获得服务器主机名

            select host_name()

USER_NAME:  获得用户名称

           select user_name()

           select user_name(2)

USER_ID:  获得用户id

           select user_id()

《2》   日期函数

GETDATE:  获得系统日期

            select getdate()

DATEADD:  在给定日期的指定部位上增加指定的值

select dateadd(day,2,getdate())              

       DATEDIFF: 计算给定两个日期在指定部位上的差值

           select datediff(day,'2011/1/1',getdate())      

       YEAR:获得给定日期的年份

               select year(getdate())

       MONTH:获得给定日期的月份

               select month(getdate())

       DAY: 获得给定日期的日

                           select day(getdate())

《3》   字符串函数

基本函数

UPPER:小写转大写

        select upper('abc')

         LOWER:大写转小写

                 select lower('ABC')

         SPACE:添加空格

                 select '姚'+space(2)+'名'

         REPLACE:替换字符

                 select replace('abbccdd','c','Q')

         STUFF:在给定的字符串中删除指定位置开始的指定长度,并在此位置插入给定的新字符串

                 select stuff('abcdef',2,3,'123456')

         LTRIM:删除给定字符串左边的空格

                 select ltrim('    abc   d  ');

         RTRIM:删除给定字符串右边的空格

                 select rtrim('    abc   d  ');

       查找函数

         CHARINDEX:从指定位置开始查询指定的字符串,返回该字符串的位置编号

                   select charindex('bc','abcdefg',1);

         PATINDEX:(和上面的一样,但支持通配符

select patindex('_b%','abcdefg')

       长度和分析函数

         DATALENGTH:求给定字符串的长度,或者是给定数据所占空间值

                        select  datalength('123')

                     select  datalength(123)

         SUBSTRING:取子字符串

                      select  substring('abcdefg',2,4)

         RIGHT:从给定字符串有变取指定长度子字符串

select right('abcdefg',4)

       转换函数        

CHAR:求指定asc码值对应的字符

       select char(97)

         STR:把给定整数转换成字符串

               select str(12345) 

《4》   数学函数

       ABS:求绝对值

                select abs(-2)

CEILING:返回大于或等于给定数字的最小整数

          select ceiling(3.56)

       FLOOR:返回小于或等于给定数字的最大整数

                 select floor(3.56)

       ROUND:

                select round(3.56478,3)

select round(4321,-3)

《5》   用户自定义函数

定义:create  function

修改:alter   function

删除:drop   function

 

create  function  函数名称(参数名称 as 数据类型)

returns  返回数据类型

[as]

begin

函数内容

return  表达式

                   end

                   举例1:定义一个用来计算员工奖金的函数,并使用该函数输出员工的奖金

                   create function bonus(@salary as money)

                   returns money

                   as

                   begin

                        return (@salary *0.3)

                   end

                   --输出

                   select  姓名,dbo.bonus(工资) as 奖金

                   from 员工表

                   举例2:

                   create function guest.test()

returns int

as

begin

                     return 1+2;

end

 

select guest.test()

举例3:

create function dbo.uptable(@age as int)

returns table

as

return (select stu_id '学号',stu_name '姓名',stu_age '年龄'

from stu

where stu_age=@age)

 

select *

from dbo.uptable(44)

4.      上机练习

第九节课  T-SQL

        语句块:

        Begin

            语句块

        End

        流程控制语句:

1. If…else…

If  条件表达式

 begin

语句块

             end

            Else

             begin

                语句块

              end

          举例1:

           declare @age int

select @age=max(sage) from student

if(@age>80)

  begin

              select @age

  end

else

  begin

               select '无'

            end

          举例2

           declare @name varchar(20)

select @name='林冲'

if exists(select * from student where sname='林冲')

            begin

               select *

               from student

               where sname='林冲'

            end

else

  begin

               print '没有'+@name+'这个人'

  end

2. While语句

While  条件表达式

  Begin

       语句块

  End

举例:计算1到100之间的整数和

declare @a int,@sum int

set @a=1

set @sum=0

while @a<=100

begin

              set @sum=@sum+@a

              set @a=@a+1

end

print '1到的100和为:'+cast(@sum as varchar)

3. Waitfor  delay  ‘时间’ | time ‘时间’

作用:指定其后的语句在某一时刻或在一段时间间隔后继续执行。

说明:《1》delay: 等待一个指定的时间间隔,最长为24小时

     《2》time:  等待一个指定的时间。

     《3》时间:指定等待的时间必须为datetime类型,不能包括日期

举例1:延迟6秒后,执行如下查询

waitfor delay '00:00:06'

select *

from student

举例2:在指定的时间执行如下查询

waitfor time '16:31:00'

select *

from student

4. goto

5. case表达式
case表达式可以计算多个条件,并将其中一个符合条件的结果表达式返回。case表达式的语法为:
case  字段名或变量名
when  逻辑表达式1  then  结果表达式1
when  逻辑表达式2  then  结果表达式2
when  逻辑表达式3  then  结果表达式3

end

   举例:

           select stu_name,stu_age,性别=

case stu_sex

    when '男' then '男士'

    when '女' then '女士'

end

from student

6. Return

作用:无条件地从过程、批或语句块中退出,其之后的语句不在执行

7. 上机练习       

第十节课  数据的完整性

        1.数据的完整性

         <1>实体完整性:要求表中的所有行都有唯一的标识符,即主关键字,因此,

表中不会出现完全相同的两行。

         <2>域完整性:把属性值限定在一定的范围内,即不能取任意值。

         <3>参照完整性:属于表间的规则,即不能删除和其它参照表有关联的数据

         <4>用户定义的完整性:用户定义的一些特殊规则.

        2.约束

         <1>不为空约束  not null

         <2>主键约束    primary

         <3>check约束  

         <4>unique约束

         举例1:

         create table stu(

stu_id int primary key check(stu_id>0),

stu_name varchar(6) not null,

stu_age int check(stu_age>=0 and stu_age<1000))

         举例2:

         create table stu(

stu_id int  check(stu_id>0),

stu_name varchar(6) not null,

stu_age int check(stu_age>=0 and stu_age<1000)

primary key(stu_id),

unique(stu_name))

         举例3:

         create table stu(

stu_id int  check(stu_id>0),

stu_name varchar(6) not null,

stu_age int check(stu_age>=0 and stu_age<1000),

stu_sex varchar(6) default '男',

primary key(stu_id),

unique(stu_name))

        3.索引

          <1>概念:索引是与表或视图关联的磁盘上结构,可以加快从表或视图中检索行的速度。索引包含由表或视图中的一列或多列生成的键。这些键存储在一个结构(B 树)中,使 SQL Server 可以快速有效地查找与键值关联的行。

          <2>创建索引:

              create index stu_stu_id on stu(stu_id)

         说明:

             对于用户来讲,只负责创建索引,至于如何来执行所以则有系统来决定;索引的创建会加快数据的检,因此,通常在唯一列,主键或经常查询的列设置索引。       

第十一节课  事务

1、  事务的概念和模式

概念:数据库事务(Database Transaction) ,是指作为单个逻辑工作单元执行的一系列操作。通过将一组相关操作组合为一个要么全部成功要么全部失败的单元。

性质:

《1》   原子性:即不可分割的操作序列。

《2》   一致性:即数据的一致性。

《3》   隔离性:即独立性,不依赖于其它任何事务。

《4》   持久性:即当事务提交后,操作才会完成,否则取消全部操作。

         模式:显示模式(用户定义事务),隐式模式,自动模式

2、  事务的commit和rollback

Begin transaction:  事务的开始

Commit:  事务的提交,它是SQL的一个保存命令

Rollback:  它是SQL的一个撤销命令,将数据库的状态撤销到上一个commit点。

3、  事务的隔离级别

《1》   脏读取:一个事务读取了另一个并行事务未提交的数据。

《2》   不可重复读取:一个事务多次读取某一行,但读取到的值不一致

《3》   虚读:一个事务重新执行一个查询,返回的记录中包含了因为其它最近提交的事务而产生的新纪录。

4、  事务的开始、保存点、终止

《1》begin transaction mysaction—事务名可以省略,transaction可以简写为tran

 update stu

 set stu_age=30

where stu_id=123

commit transaction mysaction

      《2》begin transaction mysaction

 update stu

set stu_age=50

 where stu_id=123

save tran it--设置保存点

            delete

            from stu

rollback tran it

commit transaction mysaction

select *

from stu

5、  锁定

《1》   共享锁

《2》   排它锁

《3》   修改锁

《4》   意向锁

《5》   架构锁

《6》   大容量更新锁

说明:设置这么多锁的目的其实就是为了避免死锁.

第十二节课  视图

《1》   概述:视图是由基于一个或多个表上的查询所定义的虚拟表。

《2》   优点:简化查询,简化结构,数据的安全性,直接汇总结果,数据完整性,便于数据的交互操作。

《3》   缺点:性能时高时低,数据更新受限。

《4》   创建视图:

举例1:(基于单表)

create view view_stu

as

select stu_name,stu_age,stu_sex

from stu

 

create view view_stu(姓名,年龄,性别)

as

select stu_name,stu_age,stu_sex

from stu

 

create view view_stu(性别,平均年龄)

as

select stu_sex,avg(stu_age)

from stu

group by stu_sex

 

举例2:(基于多表)

create view view_ss

as

select stu_name,score.*

from stu,score

where stu_id=s_id

 

create view view_ss

as

select stu_name,score.*

from stu join score

on stu_id=s_id

 

create view view_mm

as

select *

from stu

where stu_id in (

select s_id

from score

)

《5》   修改视图

              alter view view_stu

as

select stu_name,stu_age

from stu

《6》   删除视图

       drop view view_stu

《7》   通过视图管理数据(插入,修改,删除)

说明:通过视图操作数据有很大的局限性,总体原则是在表本身上可以执行的操作在视图上才有可能执行,如果在表本身都无法执行,那在视图上肯定不能执行。

《8》   查看视图信息

               sp_help view_stu

 

sp_helptext view_stu

第十三节课  存储过程

1.      概念:存储过程(Stored Procedure)是一组为了完成特定功能的SQL 语句集,经编译后存储在数据库。

2.      分类:系统存储过程和用户自定义存储过程:系统过程主要存储在master 数据库中并以sp_为前缀

3.      优点:

《1》.存储过程只在创造时进行编译,以后每次执行存储过程都不需再重新编译,而一般SQL语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。  

  《2》.当对数据库进行复杂操作时(如对多个表进行Update,Insert,Query,Delete时),可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。  

《3》.存储过程可以重复使用,可减少数据库开发人员的工作量  

   《4》.安全性高,可设定只有某些用户才具有对指定存储过程的使用权

4.      创建存储过程

《1》   语法格式:

      CREATE PROCEDURE存储过程名

  [(参数#1,…参数#1024)]

  AS 程序行

  其中存储过程名不能超过128个字。每个存储过程中最多设定1024个参数

举例1:

                    create procedure query_test

as

select *

from stu

 

execute query_test

           举例2:create proc query_book

@id nvarchar(50)

as

select *

from book

where 编号=@id

 

exec query_book '001'

举例3:

                    create procedure insert_test

@param1 int,@param2 int,@param3 varchar(6),@param4 int,@param5 varchar(6)

as

insert into stu values(@param1,@param2,@param3,@param4,@param5)

 

execute insert_test 22,555,'李艳',27,'女'

            带返回值:

            举例4:

                   create proc query_test

@out money output—[with encryption]加密

as

select @out=sum(价格)

from book

 

declare @t money

exec query_test @t output

select @t

print @t

5.      存储过程的缺点:

《1》   查找错误难

《2》   移植性不好

《3》   维护性麻烦

 

第十四节课  触发器

1.    概念:触发器是一种特殊类型的存储过程,它不同于我们前面介绍过的存储过程。触发器主要是通过事件进行触发而被执行的,而存储过程可以通过存储过程名字而被直接调用。

2.      功能:触发器的主要作用就是其能够实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外,触发器还有其它许多不同的功能:

《1》 强化约束

《2》   跟踪变化

《3》   级联运行,即可以引用其他表的列,这也是和check约束的区别。

《4》   存储过程的调用

3.      创建触发器(在创建数据库,修改数据库,删除数据库时不能执行触发器)

举例1:

create trigger mytrig

on stu

for delete

as print ' 删除stu表中的数据'

注意:select不能创建触发器

 

create trigger mytrig

on stu

for insert

as print ' 在stu表中插入的数据'

 

create trigger mytrig

on stu

for update

as print ' 更新stu表中的数据'

4.      删除触发器

   drop trigger mytrig—[,……n]

5.      触发器的启用和禁用

启用:  alter trigger mytrig enable

禁用: alter trigger mytrig disable

启用所有触发器:alter trigger stu enable all trigger

禁用所有触发器:alter trigger stu disable all trigger

6.      触发器高级应用

            create trigger myupdatetrig

on stu

for update

as

if update(stu_id)

begin

raiserror('不能修改此属性',10,1)—-返回错误信息,并设定一个系统标志记录已发生的一个错误。

rollback transaction—取消触发触发器的动作

end

           说明:

             语法
RAISERROR ( { msg_id ¦ msg_str } { , severity , state }

              参数
msg_id

存储于 sysmessages 表中的用户定义的错误信息。用户定义错误信息的错误号应大于 50,000。由特殊消息产生的错误是第 50,000 号。

msg_str

是一条特殊消息,其格式与 C 语言中使用的 PRINTF 格式样式相似。此错误信息最多可包含 400 个字符。

              severity

用户定义的与消息关联的严重级别。用户可以使用从 0 到 18 之间的严重级别。19 到 25 之间的严重级别只能由 sysadmin 固定服务器角色成员使用。

              state

从 1 到 127 的任意整数,表示有关错误调用状态的信息。state 的负值默认为 1。

第十五节课  游标

1.            概念:游标就是游动的光标,是一种能从包括多条数据记录的结果集中每次提取一条记录的机制,并以临时表的形式存放在内存中。

2.            声明游标

举例1:

declare mycursor cursor

for

select *

from stu

举例2:

declare mycursor cursor

for

select *

from stu

for read only--只读游标

                 举例3:

                   declare mycursor cursor

for

select *

from stu

FOR UPDATE    --更新游标

 

3.            打开游标

   open mycursor

4.            提取数据

举例1:--游标的结果集只有一条记录

declare mycursor cursor

for

select stu_name

from stu

where stu_id=123

open mycursor

declare @name varchar(6)

fetch mycursor

into @name                

  举例2:--游标的结果集有多条记录

    

5.            关闭游标

                     CLOSE mycursor    --关闭游标

DEALLOCATE mycursor    --释放游标

6.             

第十六节课  SQL安全性和优化

第十七节课  权限管理

第十八节课  数据库及服务器管理及维护

展开阅读全文

没有更多推荐了,返回首页