数据库期末sql基础语句的复习

/*创建一个数据库用来期末复习*/
create database  复习
on
(
 name=复习,
filename='D:\数据库\复习.mdf',
size=50,
filegrowth=2

)
log on
(
 name=复习_log,
 filename='D:\数据库\复习_log.ldf'
size=25,
filegrowth=3
)
/*创建数据表*/
create table student
( sno  char(9) primary key,
  sname  char(20) unique,
  ssex   char(2) ,
  sage  smallint,
  sdept  char(20)

)


creat table course
(
 cno  char(4) primary key,
 cname  char(40) ,
 cpno  char(4),
 ccredit  smallint,
 Foreign key(cpno) references course(cno)

)


create table sc
(
 sno char(9),
 cno char(4),
 grade smallint,
 primary key(sno,cno),
 foreign key(sno) references student(sno),
 foreign key(cno) references course(con)

)

/*其中学号不能为空，值是唯一的，并且姓名取值也唯一。/*
create table student
(
 sno char(9)not null unique,
 sname cahr(20) unique,
 ssex char(2),
 sage int(4),
 sdept char(20)

)
/*数据定义，add ,alter column,drop column  */
alter table student
add s_entrance data


alter table student
alter column sage int


alter table course
add unique (cname)

drop table student

/*数据操纵*/
insert into student
values('200215121','张宪泉','男',20,'cs')

insert into sc(sno,cno)
values('200215122','张三')
insert into sc
values('200215122','李四',null)

updata student 
set sage=22
where sno='200215121'

updata student
set sage=sage+1

updata sc
set grade=0
where 'cs'=( select sdept
            from student
	    where student.sno=sc.sno
            )

delete from student
where sno='200215121'



delete from sc

delete from sc
where cno='2'
/*数据查询*/
select sno, sname 
from student
select sno sname sdept
from student
select *
from student

select sname ,2013-sage
from student

select sname,sage
from student
where sage<20

select sname,sage
from student
where not sage  >20

select  sname,sdept,sage
from student
where sage between 20 and 23


select sname,sdept,sage
from student
where sage>20 and sage<23

select sname,sdept,sage
from student
where not between 20 and 23

select sname,sdept,sage
form student
where age<20 or age>23

/*单表查询*/
select sname,ssex
from student
where sdept in('cs','ma','is')

select sname,ssex
from student
where sdept='cs',or sdept='is',or sdept='ma'

select sname,ssex
from student
where sdept not in('cs'.,'is','ma')

sdept<>cs

/*字符匹配*/
select *
from student
where sno='200215121'

select *
from student
where sno like '200215121'


select sname,sno,ssex
from student
where sno like '刘%'

select sname
from student
where sname like '欧阳__'

select sname,sno,ssex
from student
where not like '刘%'

select sname,sno
from student
where sname like '_阳%'

/*使用换码字符将通配符转义为普通字符*/
select cno,ccredit
from course
where sname like 'DB\_Design' ESCAPE '\'

select *
from course
where sname like 'DB\_%i__' ESCAPE'\'


/* “IS NULL” 不能用 “= NULL” 代替*/
select sno,cno
from sc
where grade is null

select sname
from student
where sdept='cs'and sage<20

/*排序*/

select sno,grade
from sc
where cno='3'
order by grade desc

select *
from student
order by sdept,sage desc

/*集函数*/
select count(*)
from student

select count(distinct sno)
from sc

select avg(grade)
from sc
where cno='1'

select max(grade)
from sc
where cn='2'

select cno,count(sno)
from sc
group by cno


select sno
from sc
group by sno
having count(*)>3


select sno,count(*)
from sc
where grade>90
group by sno
having count(*)>3


/*多表连接查询*/
select student.sno,sname,ssex,sage,sdept,cno,grade
from student,sc
where student.sno=sc.sno

select student.sno,sname,cno,grade
from student,sc,course
where student.sno=sc.sno
    and sc.cno =cours.cno
select student.*,sc.*
from student,sc
where student.sno=sc.sno


select first.cno,second.cpno
from first course,second course
where firse.cno=second.cpno


select sno,sname,sage
from student
where sage<any(select sage
                from student
		where sdept='cs'
               )
	       sdept<>'cs'
/*存取控制*/

grant select
on student
to s1

revoke update(sno)
on student
from s1

grant view cs_student
as
select *
from student 
where sdept='cs'

grant select
on cs_student
to 张三

/*完整性约束*/

create table student
( sno char(9)not null,
  sname char(20)not null,
  ssex char(4)check (ssex in('男','女')),
  sage smallint,
  sdept char(15),
  primary key(sno)
)


create table sc
( sno char(9)not null,
  cno char(10) not null,
  grade smallint check(grade>=0 and grade=<100)
primary key(sno,cno)
)

create table student
( sno char(9)primary key,
 sname char(20)not null,
 ssex char(2),
 sage smallint,
 sdept char(40),
 check(ssex='女'or sname not like 'MS.%')
)