SQL语句SQL Server

1.创建表

create table student(

sno int,

sname varchar(8),

ssex varchar(2),

sbirth datetime,

sstate bit

);

 

create table sex(

sexno int,

sexname varchar(2),

primary key (sexno)

);

 

create table grade(

sno int,

cno int,

scgrade float

primary key(sno,cno),

foreign key (sno) references student(sno),

foreign key (cno) references course(cno)

);

 

create table grade(

sno int,

cno int,

scgrade float

constraint pk_2 primary key(sno,cno),

constraint fk_1 foreign key (sno) references student(sno),

constraint fk_2 foreign key (cno) references course(cno)

);

 

create table course(

cno int primary key,

cname varchar(20)

);

 

2.修改表

alter table student alter column sno int not null;

alter table student alter column ssex int;

alter table student add constraint pk_1 primary key (sno);

alter table student drop constraint pk_1;

alter table course drop column cgrade;

alter table course add cname varchar(20) not null;

alter table course add cteacher varchar(4);

 

3.插入数据

insert into student values(1,'yuan',2,'1987-08-10',0);

insert into student values(2,'hui',2,'1987-08-10',0);

insert into student values(3,'xian',2,'1987-08-10',0);

insert into student values(4,'yang',1,'1986-10-17',0);

insert into student values(5,'feng',1,'1986-10-17',0);

insert into student values(6,'jiang',1,'1986-10-17',0);

insert into sex values(1,'男');

insert into sex values(2,'女');

insert into grade values(1,1,80);

insert into course values(1,'a','a');

insert into course values(2,'a','a');

insert into course values(3,'a','a');

insert into course values(4,'a','a');

insert into course values(5,'a','a');

insert into grade values(1,2,70);

insert into grade values(1,3,60);

insert into grade values(1,4,50);

insert into grade values(1,5,40);

insert into grade values(2,1,50);

 

4.删除表

drop table student;

drop table grade;

drop table course;

 

5.查询

select * from student;

 

6.删除数据

delete student;

 

7.时间函数datediff的应用

select sbirth,datediff(year,sbirth,getdate()) from student;

select day(sbirth)from student;

 

8.去除重复的行

select distinct ssex from student;

 

9.查询字段去除转义字符

where sname like 'hui\_xian' escape '\';

 

10.排序

select sname,sbirth from student

order by sname desc;

 

11.取查询结果中的部分数据top

select top 50 percent sname,sno,sbirth from student order by sname desc;

 

12.聚集函数的应用

select max(scgrade),avg(scgrade),min(scgrade) from grade

where sno=1;

 

13.GROUP BY的应用

Having子句用于对分组后的结果再进行过滤，它的功能有点像Where子句，但它用于

组而不是单个记录。在Having子句中可以使用聚集函数，但在Where子句中则不能。

Having通常与Group by子句一起使用。

select sno,count(cno) from grade

group by sno 

having count(*)>2;