我的数据库知识精选（三）

如何用sql语句将某列设为主键：

 先执行
alter table 表名alter column 列名 int not null
执行下一句前提是表中的lie列没有空值才可以

再执行
alter table 表名 add constraint PK_主键约束 primary key （lie）

create tabledepart(

dno intprimarykey,

dname char(10),

)

create tableworker(

wno int,

wanme char(8),

wsex char(2),

wbir datetime,

wiscom char(2),

wjobdate datetime,

dno int,

primary key(wno),

foreign key(dno)referencesdepart(dno)

)

 

create tablesalary(

wno int,

sdate datetime,

sal decimal(6,1),

primary key(wno,sdate),

foreign key(wno)referencesworker(wno)

)

insert intodepartvalues(

3,'市场部'

)

 

insert intoworkervalues(

7,'程西','女','06/10/1980','否','07/10/2002',1

)

insert intosalaryvalues(

7,'02/04/2008',755.8

)

alter tabledepartadd dnum int

select *fromdepart

select *fromworker

update departsetdnum =2 where dno= 1

update departsetdnum =0 where dno= 2

update departsetdnum =1 where dno= 3

 

update departsetdnum =(selectcount(*) fromworker

                     where worker.dno=depart.dno

                        group by worker.dno);

 

declare @name varchar(10)

declare @age int

select @name=sname,@age=sagefromstudent where sno='200215121'

select @nameas姓名,@ageas年龄

 

declare @grade smallint

select  @grade=gradefromsc where sno='200215121'andcno='1'

select @gradeas成绩

 

 

/*为worker表创建一个更新触发器update_worker，要求当更新worker表

中职工的部门号时，自动修改dapart表中相关部门的“部门人数”*/

CREATE TRIGGERtrig_update_worker

ON worker

FOR UPDATE

AS

     update depart set dnum=dnum-1

     from deleted d

     where depart.dno=d.dno

    

     update depart  set dnum=dnum+1

     from inserted i

     where depart.dno=i.dno

GO

 

update workersetdno='2'wherewno ='1'

 

drop TRIGGERtrig_update_worker

select *fromworker

select *fromdepart

CREATE TRIGGERtrig_update_worker

ON worker

FOR UPDATE

AS

     update depart set dnum=dnum-1

     from deleted d

     where depart.dno=d.dno

     update depart  set dnum=dnum+1

     from inserted i

     where depart.dno=i.dno

GO

 

update workersetdno='2'wherewno ='1'

 

drop TRIGGERtrig_update_worker

select *fromworker

select *fromdepart

CREATE TRIGGERtrig_update_worker

ON worker

FOR UPDATE

AS

     update depart set dnum=dnum-1

     from deleted d

     where depart.dno=d.dno

    

     update depart  set dnum=dnum+1

     from inserted i

     where depart.dno=i.dno

GO

 

update workersetdno='2'wherewno ='1'

 

drop TRIGGERtrig_update_worker

select *fromworker

select *fromdepart

 

 

 

CREATE TRIGGERtrig_update_worker

ON worker

FOR UPDATE

AS

     update depart set dnum=dnum-1

     from deleted d

     where depart.dno=d.dno

    

     update depart  set dnum=dnum+1

     from inserted i

     where depart.dno=i.dno

GO

 

update workersetdno='2'wherewno ='1'

drop TRIGGERtrig_update_worker

 

delete fromworkerwhere dno='3'

 

 

create indexindex_salaryon salary(wnoasc,sdatedesc)

 

update workersetdno='1'wherewno='7'

 

create viewdepart_totalsalary

as

select depart.dnameas'部门名',sum(salary.sal)as'总工资'

from salary,worker,depart

where salary.wno=worker.wnoanddepart.dno=worker.dno

group by(depart.dname)

 

alter tablesalaryadd constraint CK_salary check (sal<9999andsal>0)

 

select wsex,avg(sal)

from worker,salary

where worker.wno=salary.wno

group bywsex

 

创建存储过程，功能：根据职工姓名查询该职工的详细信息。

 

CREATE PROCEDUREpro_getInfo@workNamevarchar(8)

AS

begin

    select wanme,wsex,dname,sal,sdate,wjobdate,wbir,wiscomfromsalary,worker,depart

    where salary.wno=worker.wnoanddepart.dno=worker.dno

                               andwanme=@workName

end

drop PROCEDUREpro_getInfo

exec pro_getInfo'李华'

/*查看一号工人的最高工资和平均工资（两种方法）*/

select wno,max(sal)as最高工资 ,AVG(sal)as平均工资

from salary

where wno='1'

group bywno

/*或者*/

select max(sal)as最高工资 ,AVG(sal)as平均工资

from salary

where wno='1'

 

 

 

 

 

//增加与删除列

altertable student drop column berthday

altertable student add  birthday date

//更改属性的数据类型

altertable j alter column city char(10)

 

ALTER TABLE scADD  check(grade<=150)

 

ALTER TABLEstudent add constraint def_ssex default '男' FOR  ssex

ALTER TABLEstudent add constraint che_sdept check (sdept in('CS','MA','IS'))

alter tablestudent add constraint uni_sname unique(sname)

alter table scadd constraint fk_S_c foreign key(sno) references student(sno)

 

alter tablestudent NOCHECK constraint che_sdept

alter tablestudent CHECK constraint che_sdept

 

create indexstuname on student(sno desc)

create uniqueindex coucname on course(cname asc)

createCLUSTERED index scsno on sc(sno)

drop indexstuname on student

create indexspjno on spj(sno asc,pno asc,jno desc)

 

 

 

 

 

5. 创建触发器，当插入交易记录时，实现自动更新账户余额的功能。建表语句如下：

create tablecount(

    id char(3)primarykey,

    balance int)

insert intocountvalues('001', 1000)

create tabletraninfo(

  id char(3)foreignkey references count(id),

  amount int)

/*---创建触发器---*/

create triggerautoupdate_balance

on traninfo

for insert

as

     update count  set balance=balance +i.amount

     from inserted i

     where count.id=i.id

   

    练习：删除交易信息时，要求自动备份被删除的数据到表backupTable中 。

    //事先没有创建backupTable中表不报错？？？

CREATE TRIGGERtrig_delete_transInfo

ON traninfo

 FOR DELETE

   AS

     print '开始备份数据，请稍后……'

     IF NOT EXISTS(SELECT*FROMsysobjects WHERE name='backupTable')

        SELECT *INTO backupTableFROMdeleted /*--从deleted表中获取被删除的数据*/

     ELSE

        INSERT INTO backupTable SELECT *FROMdeleted

     print '备份数据成功，备份表中得数据为：'

     SELECT *FROM backupTable

GO

 

练习：跟踪用户的交易，交易金额超过元，则取消交易，并给出错误提示。

在bank表上创建UPDATE触发器

l 修改前的数据可以从deleted表中获取

l 修改后的数据可以从inserted表中获取

CREATE TRIGGERtrig_update_bank

ON bank

FOR UPDATE

AS

DECLARE @beforeMoney MONEY,@afterMoneyMONEY

/*从deleted表中获取交易前的余额，从inserted表中获取交易后的余额*/

SELECT @beforeMoney=currentMoneyFROMdeleted

SELECT @afterMoney=currentMoneyFROMinserted

IF ABS(@afterMoney-@beforeMoney)>20000/*交易金额是否>2万*/

BEGIN

print '交易金额:'+convert(varchar(8),

ABS(@afterMoney-@beforeMoney))

RAISERROR ('每笔交易不能超过万元，交易失败',16,1)

ROLLBACK TRANSACTION /*回滚事务，撤销交易*/

END

GO

 

 

 

查询学生信息，如果学生人数多于人，则只显示前名，否则显示所有学生信息。

DECLARE @num int

SELECT @num=count(*) fromstudent

print '学生人数:'+convert(varchar(5),@num)

IF (@num>10)

  BEGIN

    print '前五名学生信息'

    SELECTTOP 5* FROM student  ORDERBYsno DESC

  END

ELSE

  BEGIN

    print '所有学生信息'

    SELECT  * FROM student

  END

 

 

 

 

查询“”号课程的平均成绩，如果平均成绩大于等于，

则显示前名的成绩，否则显示后名的成绩。

 

DECLARE @avg int

SELECT @avg=avg(grade) fromsc group by cnohavingcno='1'

print '平均成绩:'+convert(varchar(5),@avg)

IF (@avg>=70)

  BEGIN

    print '前五名学生成绩'

    SELECT TOP 5 * FROMsc ORDER BY grade DESC

  END

ELSE

  BEGIN

    print '后五名学生成绩'

    SELECT  top 5 * FROMscORDER BY grade aSC

  END

 

 

得到最高成绩和最高平均成绩

SELECT TOP 1gradeFROM sc

select top 1AVG(grade)as平均成绩from sc groupbysno

 

 

create tableTableIndex(

    ID intidentity(1,1),

    DataValue decimal(18,2))

/*---向TestIndex数据库表中插入条数据---*/

declare @id int

declare @data numeric(15,8)

set @id= 0

while(1=1)

  begin

    set @data= rand()

    insert into  TableIndex(DataValue)values(@data)

    set @id= @id + 1

    if(@id>200)

       break

end

 

/*检查“”号课程是否有不及格（分及格）的学生。

如有，每人加分，直至所有学生选“”号课程成绩均及格。*/

update scsetgrade =51 where sno='200215125' and cno ='1'

 

declare @grd int

select @grd=grade from sc wherecno='1'

while(@grd<60)

  begin

        update sc setgrade=grade+2wherecno ='1'and grade<100

        select @grd = gradefromsc where cno='1' 

end

 

/*返回指定姓名的学生姓名、课程名和考试成绩*/

CREATE PROCEDUREsp_stuInfo@snamevarchar(50)='张%'

AS

begin

  PRINT  @sname+'同学的考试成绩单如下：'

  SELECT sname,cname,grade

  FROM  student,course,sc

  WHERE student.sno=sc.snoandcourse.cno=sc.cno

                           and  sname LIKE  @sname

end

 

exec  sp_stuInfo'张立'

 

/*创建存储过程，查看指定课程及格学生的成绩信息，并返回及格的人数。

要求及格分数线作为参数由用户输入，默认为。

*/

CREATEPROCEDUREsp_stuGradInfo@cnovarchar(2),@gradeLineint=60

AS declare

@num int

begin

    select *from sc where cno=@cnoandgrade>=@gradeLine

    select @num=COUNT(*)fromsc where cno=@cnoandgrade>=@gradeLine

    select @numas 一号课程及格人数

end

 

exec  sp_stuGradInfo'1',75

drop PROCEDUREsp_stuGradInfo