数据库编程

use z

create table student(

xuehao smallint primary key,

name nchar(10),

sex nchar(2),

age smallint,

xi nchar(10)

)

create table book(

shuhao smallint primary key,

shuming nchar(10),

author nchar(10),

price float(2),

press nchar(10),

stock smallint

)

create table borrow(

xuehao smallint,

shuhao smallint,

number smallint,

date1date,

primary key(xuehao,shuhao),

foreign key(xuehao)references student(xuehao),

foreign key(shuhao)references book(shuhao)

)

create table huanshu(

xuehao smallint,

shuhao smallint,

number smallint,

date1date,

fine float(2),

primary key(xuehao,shuhao),

foreign key(xuehao,shuhao)references borrow(xuehao,shuhao) )

Insert into student

values(2012,'张三','男',15,'计算机')

insert into book

values(1,'书名','作者',15.78,'邮电',100)

insert into borrow

values(2012,1,2,'2015-5-5')

insert into huanshu

values(2012,1,2,'2015-5-6',15.48)

修改每列

use s

update student

set ssex='女'

选择修改

use s

update student

Set sdept='电工系'

where sname='张立 '

区间修改

use s

update student

Set ssex='男'

where sage between 17 and 21

数字加法

use s

update student

Set sage=sage+5

where ssex='男'

Set 只能跟列名，set= 后可以跟列名表达式

Where 后可以跟列名或列名表达式

use s

update student

Set sdept='微电系'

where sage+5>25

删除

use s

delete from student

where sno='201215128'

删除

use z

delete from borrow

where 2016-year(date1)>=1

use z

delete from borrow

where getdate()-year(date1)>=1// getdate()获取当前日期

delete from borrow

where year(getdate())-year(date1)>=1//year(getdate())获取当前年查找显示学号姓名这两列

use s

select sno'学号',sname'姓名'

from student

查找显示学号姓名这两列

use s

select sno,sname

from student

use s

select sname'姓名',year(sbrith)'出生年份'

from student

select sname'姓名',2016-year(sbrith)'出生年份'

from student

lower()全部小写

upper()全部大写

use s

select sname'姓名',upper(sdept)

from student

use s

select distinct sno//判断重复有无必要无必要就删除

form sc

use s

select sname

from student

where sdept='CS'

use s

select sno

from sc

where grade<60

查找学号学生成绩小于60

select sname,ssex,sno

from student

where sage between 20 and 23

查找区间

use s

select sname,ssex

from student

where sdept in('CS','MA','电工系')

where sdept='CS'or sdept='MA'or sdept='电工系'

当选择显示多个时用in

And, or,not多重条件,要求每个条件都是完整的条件表达式

Like 后如果跟的是不带通配符的字符串，那么like就等于=

use s

select sno,sname,ssex

from student

where sname like'张%'

where sname='张立'

use s

select*

from sc

order by grade desc//添加desc是降序排序，不添加是升序排序

order by grade desc sno//加了sno 当grade 出现下相等的话考虑sno的值order by grade desc sno desc //两者都是降序

where sno='1001' //where放在from之后

order by grade desc sno desc//查询sno=‘1001’的数据然后在排序

use s

select count(*)//统计行数

from student

select count(sno)//去数多少个sno元素

select count(distinct sno)//distinct消除相同的人数

select sum(grade)//算总和

select avg(grade)//算平均分

select min(grade)//元素中最小值

//选择统计元素个数

use s

select count(cno)

from sc

where sno=201215121