注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

简约男人

简约,不能简单

 
 
 

日志

 
 
关于我

一个过分渴望被理解的人其实就是一个软弱的人, 勇往直前的力量来自斩钉截铁的决心,不是来自别人的理解.

网易考拉推荐

SQL基本语法示例  

2009-10-03 12:34:47|  分类: 数据库 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

创建表

Student

Create table student

(

 sno char(7) not null Primary Key,

 Sname varchar(20) not null,

 Ssex char(2) not null check(ssex in(‘男’,’女’)) default ‘男’,

 Sage smallint check(sage>14 and sage<65) not null,

 Clno char(5) references class(clno) not null

)

course

create table course

(

 cno int not null primary key,

 cname varchar(46) not null,

 credit int not null,

)

class

create table class

(

 clno char(5) not null primary key,

 speciality  varchar(46) not null,

 inyear date not null,

 number int not null,

 monitor char(7) default null,

)

grade

(

 sno char(7) not null,

 cno int not null,

 gmark int default null,

 primary key(sno,cno),

)

class表

00311 计算机软件 2000 120 2000101 19 

00312 计算机应用 2000 140 2000103 20 

01311 计算机软件 2001 221 2001103 20 

course表

1 数据库        4

2 离散数学      3

3 管理信息系统  2

4 操作系统      4 

5 数据结构      4

6 数据处理      2 

7 C语言        4 

student表

2000101 李  勇 男 20 00311

2000102 刘诗晨 女 19 00311

2000103 王一鸣 男 20 00312

2000104 张婷婷 女 20 00312

2001101 李勇敏 女 19 01311

2001102 贾向东 男 22 01311

2001103 陈宝玉 男 20 01311

2001104 张逸凡 男 21 01311

2001105 肖  尧 男 19 01311

 

grade 表中的内容

2000101 1 92.0

2000101 3 88.0

2000101 5 86.0

2000102 1 78.0

2000102 6 55.0

2000103 1 72.0

2000103 3 65.0

2000103 5 78.0

2000104 1 54.0

2000104 6 83.0

2001101 2 70.0

2001101 4 65.0

2001102 2 80.0

2001102 4 90.0

2001102 6 83.0

2001103 1 76.0

2001103 2 85.0

2001103 3 75.0

2001103 4 76.0

2001103 5 64.0

2001103 6 56.0

2001103 7 72.0

 

 

 

表操作语句

drop table class

alter table student

alter table drop column  number

alter column sage int

约束创建与更新

alter table student

add constraint df_sex default '男' for ssex,

add constraint rightclno Foreign Key(clno) references class(clno),

add constraint rightsex check(ssex in ('男','女')),

add constraint rightinyaer check(inyear>1985and inyear<2020);

alter table grade

add constraint pk_stu primary key(sno,cno)

alter table grade

drop constraint pk_stu

索引

create cluster index ix_stu

on student (sno desc)

/*cluster表示为聚族索引,一最多创一个*/

create unique index ix_grade

on grde (sno,cno desc)

/*unique表示每一索引值对应表中为一纪录*/

drop index  grade.ix_grade

更新

update grade

set gmark=100

where sno='2003112'and cno=2

insert into student

values('2000101','李勇','男',20,'00311')

delete from student

where sname='李勇'

查询

/*选修了所有课程的学生*/

select sname

  from student s

  where not exists(select *

                 from course c

                 where not exists (select *

                                 from grade

                                 where sno = s.sno

                                   and cno = c.cno))

                                  

/*和李勇又完全一样选修的学生*/                                

select s.sname ,g.cno from student s ,grade g

where s.sno=g.sno

and not exists(

select cno from grade

where

s.sno=grade.sno

and

cno not in(select cno from grade where sno=(select sno from student where sname='李勇')) )                                 

/*每个学生的选修信息*/

select s.sno,s.sname,g.cno from grade g,student s

where g.sno=s.sno

order by s.sno ,g.cno

 

/*每个学生选修的课程的门数*/

select distinct s.sname, count(distinct g.cno) 课程数 from student s, grade g

where g.sno=s.sno

group by s.sname 

/*李勇选修的课程的门数*/

select distinct g.sno 学号, count(distinct g.cno) 课程数 from grade g

where g.sno=(select sno from student where sname='李勇' )

group by g.sno

触发器

CREATE TRIGGER updateclno ON [dbo].[student]

FOR INSERT

AS

declare @clno char(1)

select  @clno=clno from instered

update class

set number=number+1

where clno=@clno

insert into student

values('2001105','lvbu','&366;',19,'01311')

delete from student

where sno='2001105'

select * from class

update  class

set monitor='2000102'

where clno='00312'

CREATE TRIGGER updateclno ON [dbo].[student]

FOR INSERT

AS

declare @clno char(5)

select  @clno=clno from inserted

update class

set number=number+1

where clno=@clno

CREATE TRIGGER updateclno1 ON [dbo].[student]

FOR INSERT

AS

declare @clno char(5)

select  @clno=clno from deleted

update class

set number=number-1

where clno=@clno

CREATE TRIGGER [updatecheck] ON [dbo].[class]

FOR UPDATE

AS

declare @sno  char(7), @clno1 char(5) , @clno2 char(5)

select  @sno=monitor from inerted;

select @clno1=clno from student

where sno=@sno;

select @clno2=clno from deleted;

if @clno1!=@clno2

select 'student'+@sno+'not in class'+@clno1

if @clno1=@clno2

select 'successfully'

存储过程

CREATE PROCEDURE   insert_on_student_update_class @sno char(7),@sname varchar(20),@ssex char(2),@sage smallint,@clno char(5)

AS

if @clno not in (select clno from class)

select '不存在班级'+@clno

if @clno in (select clno from class)

insert into student

values(@sno,@sname,@ssex,@sage,@clno)

update class

set number=number+1

where clno=@clno

select 'successfully'

GO

  评论这张
 
阅读(525)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017