SQL基础

最新推荐文章于 2024-07-09 17:10:02 发布

weixin_34220179

最新推荐文章于 2024-07-09 17:10:02 发布

阅读量322

点赞数

文章标签：数据库

@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

create table depart

（部门号.int pximary key,

部门名.char（10））

二（查询）使用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（出生日期）in（3，5，7）

六、关键字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

in（select 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

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

from user

where 作者编号>=3

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

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

sp_helptext 显示视图的源代码

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

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

解：sp_helptext v_uesr

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

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

解：alter view toptopic

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

from section，topic，user

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 tomyback。with differential ，noinit

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

解：backup database bbDB

file=‘bbDB_data’to myback with noinit

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

解：backup log bbDB to myback。

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

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

解：restore database bbDB

from myback

with file=1，norecovery

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

解：restore database bbDB

from myback

with file=2，norecovery

例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 as‘jobs表的记录个数’

例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 ”进行处理。

解：--利用RTRIM和LTRIM去掉左右两端的空格

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 ‘A的ASCII码’

【时间和日期函数】

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

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

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

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

datediff：日期之差。

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

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

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

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

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

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

解：select datediff（day，‘2003-1-21’，getdate（））as ‘相隔天数’

【数学函数】

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

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

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

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

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

解：select ceiling（36.89）as‘ceiling（36.89）的值’

floor（36.89）as‘floor（36.89）的值’

round（36.89，1）as‘round（36.89，1）的值’

<结果 37 36 36.9>

【转换函数】【cast convert】

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

语法1【标量函数】

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

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

returns char(8)

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

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=@M—1

end

print @S

储存过程（课件）

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

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

解：【创建】create proc p_zg

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

【执行】exec p_zg

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

解【创建】create proc p_bm

（ @bmh int,@bmm char(10)）

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)

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)='男')

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

declare @topic char(255)

select @topic =TTOPIC

from inserted

if @topic =' '

begin

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

rollback transaction

end

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

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

四十二、delete触发器

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

解：create trigger tr_topic_delete1

on topic

for delete

declare @N int

section @N=tisid from deleted

update section

set STopicCount = StopicCount-1

where sid=@N

执行 delete topic

where tid=5

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

解：create trigger tr_zg

on worker

for delete

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 'c的ascii码'

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

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))

insert into worker

(职工号,姓名,性别)

values

(@zgh,@xm,@xb)

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

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

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

解：create proc dworker

(@zgh int)

delete from worker

where 职工号=@zgh

删除职工号为15的记录

exec dworker 15

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

解：create proc SWorker

(@xm char(8))

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

from worker,depart

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

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

exec SWorker '孙华'

4、显示存储过程delworker。

解：sp_help delworker

5、删除存储过程delworker。

解：drop proc delworker

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

解: create trigger depart_insert

on depart

for insert

declare @bmn char(10)

select @bmn=部门名 from depart

if @bmn=''

begin

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

rollback transaction

end

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

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

解：create trigger worker_delete

on worker

for delete

declare @zgh int

select @zgh=职工号 from worker

delete salary

where 职工号=@zgh

执行 delete from worker

where 职工号=15

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

解：drop trigger depart_insert

weixin_34220179

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫