欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

SQL语句SQL Server 博客分类: SQL Server SQLserver数据库语句 

程序员文章站 2024-03-01 15:26:04
...

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;