create table dept(deptno char(4) not null primary key, dname Varchar(10) not null)
b.人员表
create table person(p_no char(6) not null primary key, p_name Varchar(10) not null, sex char(2) not null, birthdate datetime null, prof varchar(10) null, deptno char(4) not null)
/* constraint deptno_fk foreign key (deptno) references dept(deptno))*/ c.月薪表
create table salary ( p_no char(6) not null primary key, base decimal(5,1) null,
bonus decimal(5,1) null check(bonus>=50), fact as base+bonus, month int not null,
constraint p_no_key foreign key (p_no) references person(p_no))
3.创建视图
在基本person表上创建员工视图，包括工号、姓名、性别、职称和部门代码等字段。 create view PersonView as
select p_no 工号,p_name 姓名,sex 性别,prof 职称,deptno 部门代码 from person
想想为什么以下语句有误？
select p_no,p_name from personview 正确
select 工号,姓名 from personview
select * from personview 4.创建索引
a.在人员表的姓名列上创建一个单列索引name_sort
1
create index name_sort on person(p_name)
b.在人员表的出生日期列和姓名列上创建一个组合索引birth_name
create index birth_name on person(birthdate,p_name)
c.在人员表的姓名列上创建一个惟一索引u_name_sort
create unique index u_name_sort on person(p_name)
d.在月薪表的实发列上创建一个聚簇索引fact_idx
create clustered index fact_idx on salary(fact)
drop table salary
create table salary ( p_no char(6) not null ,
base decimal(5,1) null,
bonus decimal(5,1) null check(bonus>=50), fact as base+bonus, month int not null,
constraint p_no_key foreign key (p_no) references person(p_no))
create clustered index fact_idx on salary(fact)
drop table salary
create table salary ( p_no char(6) not null constraint p_k_salary primary key, base decimal(5,1) null,
bonus decimal(5,1) null check(bonus>=50), fact as base+bonus, month int not null,
constraint p_no_key foreign key (p_no) references person(p_no))
alter table salary drop p_k_salary
create clustered index fact_idx on salary(fact)
2
5.删除索引
删除月薪表上的索引fact_idx drop index salary.fact_idx
alter table salary add constraint p_k_salary primary key (p_no)
6.利用SQL语句向表person、salary和dept中插入数据。
insert into person values('000001','王云','男',1973-4-7,'中级','0001') drop table person
drop table salary
drop table person
create table person(p_no char(6) not null primary key, p_name Varchar(10) not null, sex char(2) not null, birthdate datetime null, prof varchar(10) null,
deptno char(4) not null constraint f_k_person foreign key references dept(deptno))
insert into person values('000001','王云','男',1973-4-7,'中级','0001') drop table person
create table person(p_no char(6) not null primary key, p_name Varchar(10) not null, sex char(2) not null, birthdate datetime null, prof varchar(10) null, deptno char(4) not null )
alter table person add constraint f_k_person foreign key(deptno) references dept(deptno) insert into person values('000001','王云','男',1973-4-7,'中级','0001')
create table salary ( p_no char(6) not null primary key, base decimal(5,1) null,
bonus decimal(5,1) null check(bonus>=50), fact as base+bonus, month int not null,
constraint p_no_key foreign key (p_no) references person(p_no))
insert into person values('000001','王云','男',1973-4-7,'中级','0001')
3
因此只能先向部门表中插入记录
insert into dept values('0001','人事部')
insert into dept values('0002','财务部')
insert into dept values('0003','市场部')
select * from dept
再向人员表中插入记录
insert into person values('000001','王云','男',1973-4-7,'中级','0001') insert into person values('000002','谢志文','男',1975-2-4,'中级','0001') insert into person values('000003','李浩然','男',1970-8-25,'高级','0002') insert into person values('000004','苗晓玲','女',1979-8-6,'初级','0002') insert into person values('000005','梁玉琼','女',1970-8-25,'中级','0003') insert into person values('000006','罗向东','男',1979-5-11,'初级','0003') insert into person values('000007','肖家庆','男',1963-7-14,'高级','0003')
select * from person
最后向月薪表中插入记录
insert into salary(p_no,base,bonus,month) values('000001',2100,300,'1')
create table salary ( p_no char(6) not null primary key, base decimal(5,1) null,
bonus decimal(5,1) null check(bonus>=50), fact as base+bonus, month int not null,
constraint p_no_key foreign key (p_no) references person(p_no))
insert into salary(p_no,base,bonus,month) values('000001',2100,300,'1') insert into salary(p_no,base,bonus,month) values('000002',1800,300,'1') insert into salary(p_no,base,bonus,month) values('000003',2800,280,'1') insert into salary(p_no,base,bonus,month) values('000004',2500,250,'1') insert into salary(p_no,base,bonus,month) values('000005',2300,275,'1') insert into salary(p_no,base,bonus,month) values('000006',1750,130,'1') insert into salary(p_no,base,bonus,month) values('000007',2400,210,'1')
select * from salary
4
7.用SQL语句修改表中的数据
要求：将salary 表中工号为000006的员工工资增加为1800元，奖金增加为160元 update salary set base=1800,bonus=160 where p_no='000006' select * from salary
8.用SQL语句删除表中的数据
要求：删除person表中工号为000010的员工数据。
delete from person where p_no='000010'
9.更新视图
要求：将员工视图personview中姓名为王云的员工职称改为高级 update personview set 职称='高级' where 姓名='王云' select * from personview 10.向视图中插入数据
要求：向视图personview总插入一行数据('000008','刘美萍','女','中级','0002') insert into personview values('000008','刘美萍','女','中级','0002') 11.删除视图
要求：将视图personview删除 drop view personview
select * from personview
12.利用SQL语句查询person表中所有的数据。 select * from person 13.条件查询
要求：
a. 查询person表中所有不重复的职称。 select distinct prof from person
看看区别
select prof from person
b.查询person表中职称为中级的所有员工的数据。
select * from person where prof='中级'
c.查询person表中具有高级职称的男员工信息。
select * from person where sex='男'and prof='高级'
d.查询person表中姓名为王云、谢志文或罗向东的员工数据。
select * from person where p_name in ('王云','谢志文','罗向东')
5
select * from person where p_name='王云' or p_name='谢志文' or p_name='罗向东' 14.使用order by排序
要求：利用SQL语句将工号在000003和000008之间的员工的月收入按实发工资升序排序 select * from salary
where p_no between '000003'and '000008' order by fact
15.带表达式的查询
要求：利用SQL语句查询工号为000002的员工的基本工资增加2倍，奖金增加1.5 倍后的实际收入。
select p_no 工号,base*2+bonus*1.5 实际收入 from salary
where p_no='000002'
验证一下
select * from salary
16.利用SQL语句查询各部门的实发工资总额。 select person.deptno 部门, sum(fact) 实发总额 from person,salary
where person.p_no=salary.p_no group by person.deptno
17.利用SQL语句查询1月份发放奖金平均数大于200元的部门，并从低到高排序。
select person.deptno 部门, avg(bonus) 一月平均奖金 from person,salary
where person.p_no=salary.p_no and month='1' group by person.deptno having avg(bonus)>200
18.表的等值与非等值连接
要求：利用SQL语句查询人事部所有员工的信息。 select person.*,dept.dname from person,dept
where person.deptno=dept.deptno and dept.dname='人事部'
19.表的自然连接
要求：利用SQL语句查询person表中职称为中级的员工工资信息
select person.p_name,salary.fact
6
from person,salary
where person.p_no=salary.p_no and prof='中级'
20.表的外连接查询
要求：利用SQL语句查询每个员工1月份的工资和奖金。
select p_name 员工姓名,base 基本工资,bonus 奖金,fact 实发工资,month 月份 from person left outer join salary
on person.p_no=salary.p_no and month='1'
select p_name 员工姓名,base 基本工资,bonus 奖金,fact 实发工资,month 月份 from person A left outer join salary B on A.p_no=B.p_no and month='1'
select p_name 员工姓名,base 基本工资,bonus 奖金,fact 实发工资,month 月份 from person as A left outer join salary as B on A.p_no=B.p_no and month='1'
21.单值子查询
要求： 查询比工号为000005的员工实发工资高的所有员工信息。
select p_name 员工姓名, fact 实发工资 from person,salary
where person.p_no=salary.p_no and fact>all( select fact
from salary
where p_no='000005')
select * from person
where person.p_no in
(select p_no from salary
where fact>all( select fact from salary
where p_no='000005') ) /*分析嵌套层次及执行过程*/
22.多值子查询
7
查询比部门0003所有员工实发工资高的所有员工信息。
select p_name as 员工姓名, fact 实发工资 from person,salary
where person.p_no=salary.p_no and fact>all( select fact from salary
where deptno='0003')
/*错误的，想想为什么？*/
select p_name as 员工姓名, fact 实发工资 from person,salary
where person.p_no=salary.p_no and fact>all( select fact from salary
where p_no in ( select p_no from person
where deptno='0003'))
select * from salary
23.相关子查询
要求：查询实发工资比平均工资高的员工代码及实发。 select p_no 员工代码, fact 实发工资 from salary where fact>all(select avg(fact) from salary)
分析：
select p_name 员工姓名 from person
where exists (select * from salary where p_no=person.p_no and fact> all(select avg(fact) from salary) )
8
分析：
select p_name 员工姓名, fact 实发工资 from person,salary
where person.p_no=salary.p_no and fact>all( select avg(fact) from salary) 24.使用union查询
要求：使用SQL语句分别查询具有高、中级职称的职工姓名，合并输出。
select p_name 职工姓名, prof 职称 from person
where prof='高级' union
select p_name 职工姓名, prof 职称 from person
where prof='中级'
T-SQL编程
25.自定义类型数据的使用。
要求：编写T-SQL语句，定义一个数据类型d_no,将其长度的那个一为2B,并以此来重新定义dept表。
sp_addtype d_no,'char(2)','not null ' go
create table dept3 ( deptno d_no,
dname varchar(10) not null)
26.创建函数
要求：创建函数Check_Pno，检测给定的员工号是否存在，如果存在返回0，否则返回-1.
create function Check_Pno(@Pno char(6))
returns integer as
begin
declare @num int
if exists (select p_no from person where @pno=p_no) select @num=0
9
else
select @num=-1 return @num end go
27.调用函数
要求：调用check_pno,如果返回值是0，则向salary插入一行该员工的工资记录。 delete from salary where p_no='000007' declare @num int
select @num=dbo.Check_Pno('000008') if @num=0
insert into salary values('000007',2200,280,'1')
28.创建存储过程proc_age,计算工号为000001的员工实际年龄。 create proc proc_age @code char(6),@age int output as
declare @birth varchar(4),@today varchar(4) select @birth=datename(year,birthdate) from person
where p_no=@code
select @today=datename(year,getdate())
select @age=convert(int,@today)-convert(int,@birth)
29.调用存储过程
要求：调用存储过程proc_age,计算工号为000001的员工的实际年龄 proc_age '000001',0
30.流控制语言的使用。
要求：用流控制语言统计表中男、女职工各自总人数。
/* 不能通过，需要用游标
declare @row_count int ,@male_count int ,@female_count int select @male_count=0,@female_count=0 select @row_count=count(*) from person while(@row_count>0) begin
begin transaction
if (person.sex='男') set @male_count=@male_count+1
10
else set @female_count=@female_count+1 set @row_count=@row_count-1 commit transaction end
print '男职工共有'+CAST(@male_count as char(6))+'人！' print '女职工共有'+CAST(@female_count as char(6))+'人！' */
正确程序：
deallocate aa
declare @male_count int ,@female_count int select @male_count=0,@female_count=0
declare @sexx char(2)
declare aa cursor for select sex from person
open aa
FETCH NEXT FROM aa into @sexx
while(@@FETCH_STATUS = 0) begin
if (@sexx='男')
set @male_count=@male_count+1 else
set @female_count=@female_count+1 FETCH NEXT FROM aa into @sexx end
print '男职工共有'+CAST(@male_count as char(6))+'人！' print '女职工共有'+CAST(@female_count as char(6))+'人！'
数据库完整性实验
31 利用T-SQL语句，在表定义时指定默认、创建默认、查看默认和删除默认。 要求：
a.删除表salary中的列month,然后修改表salary并增加列month,并将其默认值设为2。
alter table salary drop column month
11
go
alter table salary add month smallint not null default 2
b.创建默认prof_default 并与prof列绑定。
create default prof_default as '高级' go
exec sp_bindefault prof_default ,'person.prof'
c.查看默认prof_default
sp_help prof_default
d.将默认prof_default 接触绑定，然后将它删除。
execute sp_unbindefault 'person.prof' go
drop default prof_default
32.创建规则、删除规则。
12
因篇幅问题不能全部显示，请点此查看更多更全内容