数据库作业

建表
use cpxs
create table cp
(产品编号 char(4) primary key,
产品名称 varchar (16),
单价 int ,
库存量 smallint
)
create table xs
(商人编号 char(4) primary key,
姓名 varchar (16),
性别 char(2),
销售地区 varchar(20)
)
create table cp_xs
(产品编号 char(4) primary key(产品编号,商人编号),
商人编号 char(4) ,
销售量 int,
总销售额 int
)




use cpxs
insert into cp
values ('0203','冰箱',2500,100)
go


use cpxs
insert into cp
values ('0301','彩电',2800,50)
go



use cpxs
insert into cp
values ('0204','空调',2600,100)
go


use cpxs
insert into cp
values ('0201','洗衣机',3500,200)
go





use cpxs
insert into xs
values ('01','刘明','男','河南')
go


use cpxs
insert into xs
values ('02','刘小','女','河北')
go

use cpxs
insert into xs
values ('03','李三','男','湖北')
go

use cpxs
insert into xs
values ('04','张兰','女','北京')
go

alter table cp_xs
drop column 总销售额



use cpxs
insert into cp_xs
values ('0203','01',100)
go


use cpxs
insert into cp_xs
values ('0203','02',100)
go

use cpxs
insert into cp_xs
values ('0201','01',100)
go


use cpxs
insert into cp_xs
values ('0201','03',100)
go


use cpxs
insert into cp_xs
values ('0204','04',100)
go


use cpxs
insert into cp_xs
values ('0301','03',100)
go

update cp
set 单价=单价*0.8


delete from cp
where 单价<50
******************************************************************************************
group by 子句


1 group by 分组子句



select 专业名
from xs
group by 专业名



select 专业名 ,count(*) as '学生数'
from xs
group by 专业名

select 课程号 ,avg(成绩) as '平均成绩',count(学号) as '选修人数'
from xs_kc
group by 课程号


（结果集，包含每个专业的男生，每个专业的女生，每个专业总人数，级学生总人数）

select 专业名 ,性别,count(*) as '学生数'
from xs
group by 专业名,性别 （与上一题对比，若没有 rollup 则不能形成汇总行）






select 课程名 ,专业名,avg(成绩) as '平均成绩'
from xs_kc,kc,xs
where kc.课程号 =xs_kc.课程号 and xs.学号=xs_kc.学号
group by 课程名,专业名
with rollup





select 专业名,性别,count(*)as'人数'
from xs
group by 专业名,性别
with cube

（with cube 与 with rollup 的区别在于 前者）

select 专业名,性别,count(*)as'人数'
from xs
group by 专业名,性别
with rollup





select 课程名,专业名,avg(成绩)as'平均成绩'
from kc,xs_kc,xs
where xs.学号 =xs_kc.学号 and kc.课程号 = xs_kc.课程号
group by 课程名,专业名
with cube

************

*****************************************************************************
连接
1 自然连接 （在目标列中去除相同的字段名）

select xs.*,xs_kc.课程号,xs_kc.成绩
from xs,xs_kc
where xs.学号= xs_kc.学号



select xs.*,课程号,成绩
from xs,xs_kc
where xs.学号= xs_kc.学号 (字段名唯一)


2 多表连接 （所需要的字段来自两个以上的表）


select 姓名,成绩
from xs,xs_kc
where xs.学号=xs_kc.学号 and 课程号= '101' and 成绩 >=81

select xs.学号,姓名,课程名,成绩
from xs,kc,xs_kc
where xs.学号=xs_kc.学号 and kc.课程号 =xs_kc.课程号
and

*******************************************************************************************

默认约束

1 建表时插入默认值

create table xs3
(姓名 char(20) primary key,
专业名 char (20) constraint aaa default '计算机'
)



2 修改表时插入默认值

alter table xs2
add 备注 char(20) constraint bbb default '在读' with values


alter table xs2
add 备注 char(20) constraint bbb default '在读' /* 加了 with values 表示在老记录和新纪录中都为默认值 如果没加这在则在新记录中都为null */


3 默认值约束的删除

alter table xs2
drop constraint bbb


4 绑定列


create default ccc as 1
go
exec sp_bindefault 'ccc','xs2.性别'


5 删除绑定


exec sp_unbindefault 'xs2.性别'


6 删除默认约束


drop constraint ccc

*****************************************************************************************
视图


1 建立视图
create view jj
as
select *from xs
where 专业名='计算机'


select * from
jj

2 更新视图


(1)建立两张表
create table xs1
(学号 char(6)primary key check (学号 between '084001'and'084050'),
姓名 char(8)not null,
专业名 char(10) null,
性别 bit not null,
总学分 tinyint null
)
go
create table xs2
(学号 char(6)primary key check (学号 between '083001'and'083050'),
姓名 char(8)not null,
专业名 char(10) null,
性别 bit not null,
总学分 tinyint null
)

alter table xs1
add 备注 char (5) null
alter table xs1
drop column 备注

（2）插入内容
insert into xs1 values('084001','王进','外语',1,40)
insert into xs1 values('084002','郭明','外语',1,38)
insert into xs2 values('083001','李玉','物理',0,38)
insert into xs2 values('083002','刘明','物理',1,38)


create view xs12_view
as
select * from xs1 union all
select * from xs2


select * from xs12_view


3 向视图中插入记录


insert into jj values ('081115','刘明义','计算机',1,'1989-3-2',50,null)
select * from xs



**********************************************************************************
索引
1 简单建立索引
create index aaa on xs1(姓名) /*唯一索引一定是聚集索引*/




**************************************************

************************************
数据完整性

/* 1 创建规则 使学号一默认递增*/


create table xs4
(
学号 decimal(8,0) identity(00000001,1),
姓名 char(10),
专业名 char(10)
)


/* 2 创建规则 */
create rule aaa as @bbb between 1000 and 2000

create rule ee as @bbb like '%王%'



3 绑定
exec sp_bindrule 'aaa','cp.单价'

exec sp_bindrule 'ee','xs4.姓名'
4 解绑定
exec sp_unbindrule 'cp.单价'
exec sp_unbindrule 'xs4.姓名'

drop rule ee


5 建立主表和从表


create table xs123
(
学号 int not null constraint hh primary key,
姓名 char(10),
专业名 char(10)
)


create table xs456
(
学号 int not null foreign key references xs123(学号) ,
姓名 char(10),
专业名 char(10)
)


1 创建存储过程



create procedure student_info
as
select a.学号,姓名,课程名,成绩,t.学分
from xs a inner join xs_kc b
on a.学号=b.学号 inner join kc t
on b.课程号=t.课程号



exec student_info /* 执行存储过程 */


drop procedure student_info



2 使用带参数的存储过程




create procedure student_info1
@name char(8),@cname char(16)
as
select a.学号,姓名,课程名,成绩,t.学分
from xs a inner join xs_kc b
on a.学号=b.学号 inner join kc t
on b.课程号=t.课程号
where a.姓名=@name and t.课程名=@cname


exec student_info1 '王林','计算机'





3使用带有通配符参数的存储过程



create procedure st_info
@name varchar(30)='刘%'
as
select a.学号,a.姓名,c.课程名,b.成绩
from xs a inner join xs_kc b
on a.学号=b.学号 inner join kc c
on c.课程号=b.课程号
where 姓名 like @name

exec st_info