开发者社区> 吞吞吐吐的> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

SQL基础

简介:
+关注继续查看

@check约束

例:创建Users,要求电子邮件必须包括“@”

     解:create table users

         (uid int primary key ,uname char(32),ueamil char(32)

Usex bit ,check (ueamil char(32) like ‘%@%’))

@默认约束

例:要求性别默认为1 ,性别不能为空

    解:create table users

        (uid int primary key ,uname char(32),ueamil char(32)

Usex bit default ‘1’not null ,check (ueamil char(32) like ‘%@%’))

@表中字段的增加、修改、删除

例:创建一个数据库student,并建表course在表中加一个meno字段,删除credit字段,并修改c_name字段类型。

   解:create database student

       Create table course

       (c_number char(10) not null,

C_name char(30) ,hours int, credit real)

Alter table course add meno char(10)

Alter table course drop column credit

Alter table course alter column c_name char(32)

@创建索引

例:为表users创建唯一索引,字段为uname,索引名为i_name

       解:create unique index i_name

           On users (uname)

例:为表users创建聚集索引,字段为name,索引茗为s_name

       解:create clustered index s_name

            On users(name)

例:为表users创建非聚集索引,字段为name,索引名n_name

        解:create nonclustered index n_name

            On users(name)

例:为表users创建一个复合索引,字段为uname和usex,索引名为i_name_sex

        解:create index i_name_sex

            On users(uname,usex)

例:删除索引n_name

        解:drop index.n_name

一、(建表)使用Transdct-SQL语句创建表

语法:creat table [表名]

      (字段1 pximary key,字段2,

…字段n)

例:在数据库factory中建立部门表depart,字段部门号,类型int;部门名,类型char,长度为10。部门号为主键。

           解:ues factory

               go

               create table depart

              (部门号.int pximary key,

部门名.char10))

二(查询)使用select语句查询数据库

all 指查询所有结果

distinct 指在查询结果中不显示重复值

top n 指在结果中显示前n个值

top n﹪指在结果中显示前n﹪个值

* 指结果中显示所有值

  常用函数:sum([distinct∣all]<列名>)计算某列的和

            avg([distinct∣all]<列名>)计算某列的平均值

            count([distinct∣all]<列名>)计算某列值的个数

            count([distinct∣all]<*>)计算记录的个数

            max([distinct∣all]<列名>)计算某列的最大值

            min([distinct∣all]<列名>)计算某列的最小值

例1 查寻表select中所有记录

            解select * from select

  2 查询表select前2条记录

            解:select top 2 *from select

  3 查询所有版块中名称、点击率、帖子数

            解:select 名称,点击率,帖子数from section

   4 查询所有版块帖子数,不含重复值

            解:select distinct 帖子数 from section

   5统计所有版块中帖子总数

            解:select sum(帖子数)from section

三.关键字where的用法

       比较运算符号: 等于=    大于>  小于<    大于等于>=      小于等于<=

                     不等于<>    不大于!>      不小于!<       不等于!=

例:查询表section中点击率不低于100,并且帖子数不为0的版块。

         解select * from section

             where 点击率>=100

             and 点击率<>0

四、关键字between的用法

     例:查询表section中点击率在50到100之间的版块

             解:select * from section

                 where 点击率 between 50 and 100

五、关键字in的用法

       例:从users表中查询生日在3月,5月,7月出生的信息

               解:select * from users

                   where month(出生日期)in357

六、关键字like的用法

%  表示任何类型字符    如刘% 指姓刘的人

_  表示任何单个字符      如刘_ 指单名刘姓的人

[] 指定范围或集合的任何单个字符

[^] 不属指定范围或集合的任何单字符

     例:查找author表中区号为415的电话

           解:select photo from authors

               where photo like ‘415%’

七、关键字null的用法  【null表示未知】

         例:在表title中查出预付款少于5000或预付款未知的书

                解:select 书号,预付款 from title

                    where 预付款<5000 or 预付款 is null

八、(分组)关键字group by 的用法   【group by 指分组】

         例:统计users表中男女人数,并按性别分组。

                 解:select 性别,count*from users

                      group by 性别

九、关键字having的用法。  【通常与group by连用】

         例:在表title中查询截止目前销售超过40000的出版商。

                 解select 出版商,sum(销售额) from title

                     group by 出版商

                     having sum(出版额)>40000

十、(排序)关键字order by的用法。  【order by指排序】

        asc  指升序

        desc 指降序

      例:将发帖表中topic记录按点击率由低到高排序。

                解: select *from topic

                     order by 点击率 asc

十一、关键字compute的用法    【用于生成统计结果】

        例:查询section表中所有版块点击率及平均点击率。

                 解:select 版号,版名,点击率 from section

                     compute avg(点击率)

十二、连接查询        【交叉连接cross join   内连接inner join  外连接outer join】

         例1:【内连接inner join】显示所有职工的职工号、姓名、部门名和工资,并按部门名顺序排列

                解:select worker.职工号,worker.姓名,部门名,工资

from worker join depart

on worker.部门号=depart.部门号

join salary on worker.职工号=salary. 职工号

order by 部门名

          例2:【左外连接lift outer join】采用左向外联接。查找200001班所有学生的

学号、姓名及他们选修课程号,同时也列出该班没选课的学生的学号、姓名。

               解:select student_info.学号, 姓名,result_info.课程号

from  student_info 

left outer join result_info

                   on student_info.学号= result_info.学号

where 班级号='200001'

            例3:【右外连接right outer join】采用右向外联接。查找所有学生的学号,姓名及他们选修课程号,同时也列出没选课的学生的学号、姓名。

                 解:select student_info.学号, 姓名, result_info.课程号

                     from result_info 

right outer join student_info

on student_info.学号= result_info.学号

            例4:【交叉查询cross join】列出学生所有可能的选课情况。

                  解:select学号,姓名,课程号,课程名

from student_info

cross join course_info

十三、联合查询       【union的用法】

十四、嵌套查询【子查询】

            例1:【一般子查询】使用子查询来查询孙华所在的部门。

 

                   解:select 部门名 from depart

where 部门号=

(select部门号from worker

where姓名=‘孙华’)

             例2:【in或not in的子查询】查找选修了课程号为13的课程的学生情况

                    解select * from student_info

where student_id

inselect student_id from result_info

where课程号=13)

              例3:【比较运算的子查询all】查询年龄最小的论坛用户的用户的编号、昵称、出生日期和电子邮箱。

                  解:select 编号, 昵称, 出生日期, 电子邮箱l

from Users

where 出生日期 >= all (select出生日期fromUsers )

              例4:【比较运算子查询any】查询除年龄最小的论坛用户外的所有用户的编号、昵称、出生日期和电子邮箱。

                 解:select编号, 昵称,出生日期, 电子邮箱

from Users

where 出生日期 > any(select 出生日期 from Users )

十五【插入数据】、使用insert语句插入数据  【into】

              例:将一行添加到 Pubs数据库的titles 表中,以指定书名BU9876、类型Creating Web Pages、出版商business及价格29.99的值:

              解:insert into titles(书名,类型, 出版商, 价格)

values ('BU9876', 'Creating Web Pages', 'business', '29.99')

十六【更新数据】、使用update更新数据    【set】(where为条件若没有则全部更新)

              例:修改Pubs数据库的titles 表中书号为'BU9876'的书名为“网页制作”。

                  解:update titels

set title=’网页制作

where书号=’BU9876’

十七【删除数据】、使用delete语句删除数据(where为条件若没有则全部删除)

              例:删除pubs数据库titles表中书号为'BU9876'的记录。

                   解:delete from titles

where 书号=’BU9876’

视图应用(课件

十八【创建视图】、使用create view创建视图      【as】

              例:利用表user创建一个新的视图,命名为v-user。要求显示级别大于3的用户昵称、性别、登记和备注信息。

                    解:create view v-uesr

                      as

                      select 昵称,性别,登记,备注信息,作者编号

                      from user

                      where 作者编号>=3

十九【查看视图】、使用系统储存过程查看视图信息。  

             sp_help      显示视图所有者和创建时间

             sp_helptext   显示视图的源代码

             sp_depends   显示视图引用的对象清单

         例:查看视图v_uerr的源代码。

                 解:sp_helptext v_uesr

二十【修改视图】、使用alter修改视图。

       例:修改toptopic。要求显示点击率大于1000的信息。

                解:alter view toptopic

                    as

                    select section.板块,user.作者,topic.点击率

                    from sectiontopicuser

                    where topic.tuid=user.tuid and topic.tsid=section.tsid

                    and topic.点击率>1000

二十一【重命名视图】、使用sp_rename重命名视图。

         例:将视图toptopic重命名为hoptopic。

                解:sp_rename’toptopic’,’ hoptopic’

二十二【插入视图数据】、使用insert向视图插入数据。

二十三【更新视图数据】、使用update更新视图数据。

二十四【删除视图数据】、使用delete删除视图数据。

二十五【删除视图】、使用drop删除视图。

         例:删除视图section

                 解:drop view section

数据库管理(课件)

二十六【创建备份设备】、使用sp_addumpdevice创建备份设备。   【后缀.bak】

           例:在c盘上创建永久备份文件mybackupfile。

               解:exec sp_addumpdevice disk‘,’mybackupfile ‘,c\mybackupfile.bak

      【注:c:\mybackupfile.bak提前建立】

二十七【备份】、使用backup备份数据库或文件。 

           例1【整个备份】:将数据库bbDB整个备份到myback中。

                 解:backup database bbDB to myback

           例2【差异备份】:对数据库bbDB进行差异备份,新备份集追加到设备myback。

                   解:backup database bbDB tomybackwith differential noinit

           例3【文件备份】备份数据库bbDB中文件bbDB_data

                    解:backup database bbDB

file=bbDB_datato myback with noinit

           例4【事务日志备份】备份数据库中bbDB的事物日志到myback。

                    解:backup log bbDB to myback

二十八【恢复数据库】、使用restore恢复数据库。

            例1【恢复整个数据库】从myback中对数据库bbDB第一个完全备份中恢复。

                    解:restore database bbDB

from myback

with file=1norecovery

             例2【恢复差异备份】还原差异备份。

                    解:restore database bbDB

 from myback

with file=2norecovery

              例3【恢复数据文件】还原数据库bbDB数据库中文件bbDB_date。

                      解:restore database bbDB

                          file=bbDB_date

                          from myback

                          with noreconery

              例4【恢复事物日志】还原数据库bbDB中的事务日志。

                       解:restore log bbDB from myback

                           with recovery 

 

transact_SQL程序设计(课件

二十九、全局变量

         @@connections:返回自上次启动SQL Server 以来连接或试图连接的次数。

@@langid:返回当前所使用语言的本地语言标识符(ID)。

@@max_connections:返回SQL Server 上允许的同时用户连接的最大数。

@@servername:返回运行SQL Server 的本地服务器名称。

@@version:返回SQL Server当前安装的日期、版本和处理器类型。

          例1显示到当前日期和时间为止试图登录SQL的次数。

                  解:select getdate()as ‘当前登录日期和时间’

                      @@connections as ‘企图登录次数’

           例2、显示SQL server 安装的日期、版本和处理类型。

                  解:select @@version as SQL server基本信息’

三十、局部变量   【declare命令 set和select赋值】

       语法:--定义局部变量@xm和@bmh------ declare @xm char(8),@bmh int

             给 @ xm和@ bmh赋值----------- set @xm = ‘孙华'

select@ bmh = 部门号  

from  worker

                                          where 姓名= @xm

                 显示@bmh的值---------------- select @bmh as 部门号

            例1:创建一个局部变量,统计pubs表中jobs的记录个数。

                            解:declare @count int

                                select @count=count*

                                from jobs

                                select @count asjobs表的记录个数’

           例2:创建局部变量,求出jobs表中最低日工资的最小值和最高日工资的最大值。

                   解:declare @max real@min real

                       select @max=max(最高日工资),@min(最低日工资)

                       from jobs

                      select @max as‘最高日工资’,@min as‘最低日工资’   

三十一、运算符  

          语法1【算术运算符】【ExpResult】【+(加)、-(减)、*(乘)、/(除)和%(模)】

          --定义局部变量-----------------declare @ExpResult real

          --赋值局部变量-----------------set @ExpResult=1+3*7%2

          --显示局部变量赋值结果-----select '表达式计算结果'= @ExpResult

          语法2【赋值运算符】

          语法3【位运算符】

  --定义局部变量------declare @ExpResult1 int, @ExpResult2 int, @ExpResult3 int

 --赋值局部变量------ select  @ExpResult1=17&11,

                          @ExpResult2=17|11,

                          @ExpResult3=17^11

   --显示局部变量赋值结果-- select @ExpResult1  AS '按位与运算的结果',

                               @ExpResult2   AS '按位或运算的结果',

                               @ExpResult3   AS '按位异或运算的结果'

           语法3【比较运算符】

           语法4【逻辑运算符】【AND(与)、OR(或)、NOT(非)】

           语法5【字符串运算符】

     --定义局部变量----------------------declary @Course char(50)

 利用字符串连接符号给局部变量赋值set @Course=‘SQL Server’+‘ 2000’

 -显示局部变量赋值结果-----------------select @Course AS '字符串连接运算结果'

            语法6【理解运算符】

三十二、内置函数

    【字符串函数】

ASCII:返回字符表达式最左端字符的 ASCII代码值。

CHAR:返回相同ASCII代码值的字符。

LEFT:返回从字符串左边开始指定个数的字符。

RIGHT:返回字符串中从右边开始指定个数的字符。

STUFF:删除指定长度的字符并在指定的起始点插入另一组字符。

SUBSTRING:返回指定表达式的一部分。

UPPER:返回将小写字符数据转换为大写的字符表达式。

例1:利用字符串函数对于“   GOOD  MORNING  ”进行处理。

           解:--利用RTRIMLTRIM去掉左右两端的空格

              declare  @S char(20)

Set @S=rtrim(ltrim(“   GOOD  MORNING  ”))

select ‘==‘@S +‘==‘

              --利用SUBSTRING求得GOOD

             select substring(@S ,1,4) 

例2:显示字符A的ASCII码。

           解:select ASCII(‘A’) as AASCII码’

【时间和日期函数】

day:返回指定日期的天数。

getdate ( ):返回当前日期和时间。

month:返回指定日期的月份。

year:返回指定日期的年份。

datediff:日期之差。

    例1:返回系统当前日期,以及当前的年份,月份和天数

          解:select getdate() as '当前日期 ',

              year(getdate()) as '当前年份',

              month (getdate()) as '当前月份',

             day(getdate()) as '当前天数

    例2:显示今天距2003年1月21日相隔的天数。

           解:select datediffday,‘2003-1-21’,getdate())as ‘相隔天数’

【数学函数】

sqrt :返回给定表达式的平方根。

ceiling :返回大于或等于所给数字表达式的最小整数。

floor  : 返回小于或等于所给数字表达式的最大整数。

round : 返回数字表达式并四舍五入为指定的长度或精度。

          例:找出大于等于36.89的最小整数和小于等于36.89的最大整数和四舍五入36.89到一位小数。

                 解:select ceiling36.89asceiling36.89)的值’

                          floor36.89asfloor36.89)的值’

                          round36.891asround36.891)的值’

                 <结果 37     36      36.9>

【转换函数】【cast   convert】

三十三、自定义函数  【create function】

        语法1【标量函数】

           例:编写一个函数,输入职工号后得到该职工的姓名,职工号作为函数的输入参数。

              解:<定义函数>create function fn_zgxm(@zgh int)

returns  char(8)

as

                            begin

                            declare  @Name char(8)

                            set @Name=(select 姓名from worker

                                        where 职工号= @zgh )

                           return @Name

end

             查出职工号为1的职工姓名

               <调用函数>exec dbo.fn_zgxm(1)

      语法2【内嵌表函数】

           例:编写一个函数,根据部门号,得到部门职工的情况。

                 解:<定义函数>create function fn_zg (@bmh int)

returns table

as

return

select *from worker

where 部门号= @bmh

              --返回职工号为1的职工的情况

<调用函数>Select * from fn_zg(1)

三十四、使用if …else语句。

      例:编写程序,判断孙华的工资是否大于等于2000元,如果是 ,显示“工资高”;否则显示“工资低”。

          解:declare @GZ decimal(6,1)

Select @GZ=工资

from salary

where 姓名='孙华'

if @GZ>=2000

print '工资高'

else

print '工资低'

三十五、使用case语句。

       例1:将factory库的worker表中的孙华的部门号转换为相应的部门名。规则:1-财务处,2-人事处,3-市场部。

             解:select 职工号,姓名,

case 部门号

                when 1 then '财务处'

                when 2 then '人事处'

                when 3 then '市场部'

                end

                from worker

where  姓名='孙华'

        例2:编写程序查询学号为20000101的各学生各门课程的成绩,并将成绩 转换为不同的等级。

              解:select course_no, result,

case

                 when result>89 then '优秀'

                 when result>79 then '良好'

                 when result>69 then'中等'

                 when result>59 then '及格'

                 else '不及格'

end

     from result_info

where  student_ID=20000101

三十六、使用while…continue…break语句。

      例1:使用WHILE语句求1+2+3……+100的和。

   解:【设变量@S存放和,初始值为0;@i初始值为1

1 判断@i的值是否大于100,如果大于则直接转到5,否则执行2;

2将@i的值加到@S中,即 @S的值为@S+@i;

3 @i加1;

4 转到1继续执行;

5 输出@S】

       declare @S int, @i  int

select @S=0,@i=1

while @i<=100

begin

       set@S=@S+@i

     set @i=@i+1

 end

print@S

   例2:使用while语句求10的阶乘。

       解: 设@S为10的阶乘存放,初始值为1;@M为变量,初始值为10。

            declare @S int@M int

            select @S=1@M=10

            while @M>0

            begin

            set @S=@S*@M

            set @M=@M1

            end

            print @S

储存过程(课件

三十七、创建储存过程      【create proc】

      例1:创建一个存储过程用于查找factory库Worker表中职工的职工号,姓名,出生日期信息。

            解:【创建】create proc p_zg

 as

 select 职工号,姓名,出生日期 FROM WORKER

go

               【执行】exec p_zg

      例2:【含参数】创建一个存储过程,用于向DEPART表中插入记录。

           解【创建】create proc p_bm

 @bmh int,@bmm char(10)

as

insert into depart

values(@bmh, @bmm)

             【执行】exec p_bm 4,’后勤部’  或者 exec p_bm @bmh=4,@bmm=‘后勤部

     例3:【使用 output 参数创建带有输出参数的存储过程】下面的存储过程根据职工号返回两个参数@xm和@sr,分别代表职工的姓名和出生日期。

         解:create proc P_XS

( @zgh int,@xm char(8) output,

@sr datetime  output)

as

select @xm=姓名, @sr =出生日期

from worker

where 职工号=@zgh

        执行该存储过程,查询职工号为5的职工姓名和出生日期。

              declare @zgxm char(8)

declare @csrq datetime

exec P_XS 5,@zgxmoutput,@csrq output

select '姓名'=@zgxm,'出生日期'=@csrq

      例4:【输入默认值】创建一个向worker插入记录的存储过程,其中性别的默认值为’男’。

          解:create proc P_W

(@ZGH int,@XM char(8),@XB char(2)='')

as

insert into worker

(职工号,姓名,性别)

values(@ZGH,@XM,@XB)

   执行插入职工号为20姓名为张三:  

 exec p_w 20,'张三'

三十八、查看存储过程

        sp_help  procedure_name :  显示存储过程的所有者和创建时间

        sp_helptext  procedure_name:显示存储过程的源代码

  sp_depends  procedure_name:显示该存储过程引用的对象清单

三十九、修改存储过程

           sp_rename 旧名称, 新名称

四十、删除存储过程

           drop proc 名称

触发器(课件

四十一、insert触发器

        例:当发帖时,假设我们有这样的业务规则:如果用户未录入帖子标题,则该帖不能发表。

          解:create trigger tr_topic_insert2

on topic

for insert

as

declare @topic char(255)

select @topic =TTOPIC

from inserted

if @topic =' '

begin

              raiserror ('请录入帖子标题', 16, 1)

              rollback transaction

end

go

         执行insert into TOPIC(TSID, Ttopic, Tcontents, Ttime, TUID)

values(2,' ', '【计划路线】 山场---大镜山水库左侧--凤凰山', getdate(), 3)

四十二、delete触发器

      例:建立一个触发器,当用户删除某张帖子时,自动将版块表section中相关版块的帖子总数减一。

          解:create trigger tr_topic_delete1

on topic

for delete

as

declare @N int

section @N=tisid from deleted

update section

set STopicCount = StopicCount-1

where sid=@N

go

      执行  delete topic

where tid=5

   例:在表worker上创建一个触发器,当删除职工记录时同时删除salary表中对应职工的工资记录。

         解:create trigger tr_zg

on worker

for delete

as

             declare @N int

             select @N=职工号 from deleted

             delete salary

            where 职工号=@N

      执行  delete from worker

where 职工号=15

四十三、update触发器   instead of 触发器

四十四、查看触发器

sp_help  trigger_name :  显示触发器的所有者和创建时间

sp_helptext  trigger_name:显示触发器的源代码

sp_helptrigger  table_name:显示某个表定义的触发器清单

sp_depends  trigger_name:显示该触发器引用的对象清单

四十五、修改触发器

    【重命名】sp_rename old_trigger_name, new_trigger_name

四十五、删除触发器

     drop trigger trigger_name

SQL数据库查询练习

一、单表查询

1、显示所有职工的基本信息。

      select * from worker

2、查询所有职工所属部门的部门号,不显示重复的部门号。

       select distinct (部门号)from worker

3、求出所有职工的人数。

       select count(*)from worker

4、列出最高工和最低工资。

       select max(工资),min(工资)from salary

5、列出职工的平均工资和总工资。

       select avg(工资),sum(工资)from salary

6、创建一个只有职工号、姓名和参加工作的新表,名为工作日期表。

       select 职工号,姓名,参加工作

       into 工作日期表 from worker

7、显示所有女职工的年龄

       select 职工号,姓名,year(getdate())-year(出生日期)from worker

       where 性别=‘女’

8、列出所有姓刘的职工的职工号、姓名和出生日期。

       select 职工号,姓名,出生日期from worker

       where 姓名 like ‘刘%’

9、列出1960年以前出生的职工的姓名、参加工作日期。

        select 姓名,参加工作from worker

        where 出生日期<1960

10、列出工资在1000-2000之间的所有职工姓名。

        select 姓名 from salary

        where 工资 between 1000 and 2000

11、列出所有陈姓和李姓的职工姓名。

         select 姓名 from worker

         where 姓名 like‘[陈,李]%’

12、列出所有部门号为2和3的职工号、姓名、党员否。

         select 职工号,姓名,党员否from worker

         where 部门号 in (2,3)

13、将职工表worker中的职工按出生的先后顺序排序。

         select * from worker

         order by 出生日期 asc

14、显示工资最高的前3名职工的职工号和姓名。

         select top 3职工号,姓名from salary

         order by 工资 desc

15、求出各部门党员的人数。

       select count (*)from worker

        where 党员否='1'

16、统计各部门的工资和平均工资

        select 工资 from salary compute avg(工资)

17、列出总人数大于4的部门号和总人数。

        select 部门号,ount(*)from worker

        group by 部门号

        having count(*)>4

二、多表查询

1、列出每名职工的职工号、姓名和部门名。

       select worker.姓名,worker.职工号,depart.部门名

       from worker,depart

       where worker.部门号=depart.部门号

2、列出市场部的所有女职工的姓名和党员否。

       select worker.姓名,worker.党员否,depart.部门名

       from worker,depart

       where worker.部门号=depart.部门号

       and 性别=‘女’and 部门名=‘市场部’

3、显示所有职工的姓名、部门名和工资数。

        select worker.姓名,depart,部门名,salary.工资

        from worker,depart,salary

        where worker.部门号 =depart.部门号,

        and worker.姓名=salary.姓名

4、*显示所有职工的职工号、姓名、部门名和工资,并按部门名顺序排列。

         select worker.职工号,worker.姓名,depart.部门名,salary.工资

         from worker ,depart,salary

         where worker.部门号=depart.部门号

         and worker.姓名=salary.姓名

         order by 部门名

5、*显示各部门名和该部门的所有职工平均工资。

         select depart.部门名,avg(工资)

         from depart,salary,worker

         where depart.部门号=worker.部门号

         and worker.姓名=salary.姓名

         group by 部门名

6、*显示所有平均工资高于1200的部门名和对应的平均工资。

         select depart.部门名,avg(工资)

         from depart,salary

         group by 部门名

         having avg(工资)>1200

*1、查询刘欣所在的部门。

        select 部门名 from worker,depart

       where worker.部门号=depart.部门号

      and姓名=‘刘欣’

   或者

     select 部门名 from depart

     where 部门号=(select 部门号 from worker where 姓 名='刘欣')

*2、查询部门名为财务处的职工的情况。

          select *

         from worker,depart

         where worker.部门号=depart.部门号

         and 部门名='财务处'

   或者

        select * from worker

        where 部门号=(select 部门号

        from depart where 部门名=‘财务处’)

   T-SQL程序设计练习

一、变量的使用

1.执行下列语句,记录SQL Server 安装的日期、版本和处理器类型。

select @@version

2.定义两个变量分别存放salary表中的职工最高工资和最低工资。最后显示这两个变量的值。

解:declare @max real,@min real

select @max=max(工资),@min=min(工资)

from salary

select @max as '最高工资',

@min as '最低工资'

1. 执行下列语句,记录服务器端计算机名称、数据库用户名

解:select HOST_NAME(),USER_NAME()

2. 记录下列语句的运行结果

解:select substring('GOOD MORNING',6,4)  

3. 记录下列语句的运行结果

解:select round(123.456,2)

4.显示字符C的ASCII码

select ascii('a') as 'cascii'

5.显示ASCII码为99的字符

解:select char(99) as 'ascii对应的字符'

6.显示当前系统日期的年份、月份和天数

解:select getdate() as '当前日期',

year(getdate()) as '当前年份',

month(getdate()) as '当前月份',

day(getdate()) as '当前天数'

三、用户自定义函数使用

1.编写一个函数,输入职工号,得到职工的出生日期。(查职工号为1的出生日期)

 解:(编写函数)create function fn_csrq(@zgh int)

returns datetime

as

 begin

declare @csrq datetime

set @csrq=(select 出生日期 from worker

where 职工号=@zgh)

return @csrq

end

(调用函数)select dbo.fn_csrq(1)

2.*编写一个函数,根据部门号,得到该部门职工的职工号和姓名。

 解:create function fn_zg(@bmh int)

returns table

as return

select * from worker

where 部门号=@bmh

执行  select * from fn_zg(2)

四、流程控制语句的使用

1查询worker表中职工的职工号,姓名,性别,当性别为男,输出’Male’,当性别为女,输出’Female’。

 解:select 职工号,姓名,性别,

case 性别

when ''then 'Male'

when ''then 'Female'

end as '性别'

from worker

2.查询salary表中职工的工资情况,当工资高于2000时,输出“高”;当工资在1000-2000之间时,输出“中等”;当工资低于1000时,输出“低”

解:select 职工号,姓名,

case

when 工资>2000 then ''

when 工资>1000 then '中等'

else ''

end as '工资等级'

from salary

3.用while语句计算1+1/2+1/3+......+1/10的值。

                  解:declare @S float@M float

                      select @S=0@M=1

                      while @M<=10

                      begin

                      set @S=@S+1/@M

                      set @M=@M+1

                      end

                      print @S

4.统计男、女职工的人数,若男职工人数高于女职工,则显示“男职工多”,否则显示“女职工多”。

 解:declare @N1 int,@N2 int

select @N1=count(*)

from worker

where 性别=''

select @N2=count(*)

from worker

where 性别=''

if @N1>@N2

print '男职工多'

else

if @N1<@N2

print'女职工多'

存储过程

1、创建一个为worker表添加职工记录的存储过程addworker。然后执行该存储过程,向worker表中添加一条记录。

解:create proc adworker

(@zgh int,@xm char(8),@xb char(2))

as

insert into worker

(职工号,姓名,性别)

values

(@zgh,@xm,@xb)

         添加职工号为20,姓名为呈西,性别为女的记录

                exec adworker 20,'呈西','女'

2、创建一个存储过程dworker删除worker表中指定职工号的记录。然后执行该存储过程,删除职工号为15的记录。

:create proc dworker

(@zgh int)

as

delete from worker

where 职工号=@zgh

        删除职工号为15的记录

               exec dworker 15

3.创建一个存储过程SWorker,根据职工的姓名查询对应的性别,部门名。

 解:create proc SWorker

(@xm char(8))

as

select worker.姓名,worker.性别,depart.部门名

from worker,depart

where worker.部门号=depart.部门号

go

查找孙华的性别及所在的部门名

        exec SWorker '孙华'

4、显示存储过程delworker。

           解:sp_help delworker

5、删除存储过程delworker。

           解:drop proc delworker

6、在表depart上创建一个触发器depart_insert,如果插入的记录的部门名为空时,则该记录不能插入。然后插入一条部门名为空的记录进行验证。

 解: create trigger depart_insert

on depart

for insert

as

declare @bmn char(10)

select @bmn=部门名 from depart

if @bmn=''

begin

raiserror ('请输入部门名',16,1)

rollback transaction

end

go

       执行  insert into depart(部门号,部门名) values(9,'')

7、在表worker上创建一个触发器worker_delete,当删除职工记录时同时删除salary表中对应职工的工资记录。然在表worker中插入一条记录进行验证。

 解:create trigger worker_delete

on worker

for delete

as

declare @zgh int

select @zgh=职工号 from worker

delete salary

where 职工号=@zgh

        执行  delete from worker

where 职工号=15

8、删除6题中创建的触发器。

解:drop trigger depart_insert

分类: SQL
本文转自左正博客园博客,原文链接:http://www.cnblogs.com/soundcode/archive/2012/06/20/2556448.html,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Sql 基础问题
Ref Projection and Selection联结查询的原理(笛卡尔积)设计 MySQL 数据表的时候一般都有一列为自增 ID,这样设计原因是什么,有什么好处?
664 0
sqlServer存储过程
1、创建存储过程报错:     'CREATE/ALTER PROCEDURE' 必须是查询批次中的第一个语句。 解决方法: use databaseName 后面要加上一句: GO ...
817 0
SQL Server基础之<存储过程>
原文:SQL Server基础之   简单来说,存储过程就是一条或者多条sql语句的集合,可视为批处理文件,但是其作用不仅限于批处理。本篇主要介绍变量的使用,存储过程和存储函数的创建,调用,查看,修改以及删除操作。
1462 0
SQLSERVER存储过程语法详解
SQL SERVER存储过程语法: Create PROC [ EDURE ] procedure_name [ ; number ]     [ { @parameter data_type }         [ VARYING ] [ = default ] [ OUTPUT ]     ] [ ,...n ]   [ WITH     { RECOMPILE | ENCRY
1653 0
文章
问答
文章排行榜
最热
最新
相关电子书
更多
用SQL做数据分析
立即下载
SQL Server云化思考与实践
立即下载
SQL Sever迁移PG经验
立即下载