SQL Server 数据库常用语句

--创建数据库
create database xinxi

--创建表
use xinxi
create table department
(
  depno int primary key,
  depname nvarchar(30),
  loc nvarchar(30)
)

create table student
(
  Id int primary key,
  sname nvarchar(50) NOT NULL,
  address nvarchar(50) NOT NULL,
  sex char(2) NOT NULL,
  score int ,
  entertime datetime,
  depno int foreign key references department(depno)
)

--插入记录
insert into department values(1,'数学系','南京')
insert into department values(2,'计算机系','北京')
insert into department values(3,'物理系','香港')
insert into department values(4,'化学系','澳门')
 
insert into student values(23,'张三','桐城','男',40,'2008-6-8',1)
insert into student values(3,'李四','桐城','男',45,'2009-5-7',1)
insert into student values(56,'王五','巢湖','男',57,'2008-7-3',4)
insert into student values(42,'陈六','桐城','男',10,'2007-9-10',2)
insert into student values(7,'田七','宿州','女',80,'2009-5-7',2)
insert into student values(61,'赵八','六安','女',60,'2011-12-27',3)
insert into student values(62,'周九','马鞍山','男',100,'2010-2-24',3)
--insert into student (Id,sname,address,sex,depno) values(56,'钱文十','巢湖','男',4)

--选择查询
select * from student
select * from department

--查询所有列
select * from student where score<60
--查询部分列
select sname,address,depno from student where score<60
--消除重复项
select distinct depno from student

select sname,score*8 总成绩 from student
--查找2009.1.1后入学的学生
select *from student where entertime>'2009-1-1'

select sname from student where score>60 and score<80
select sname from student where score between 60 and 80
--查找姓陈的学生的姓名和成绩
select sname,score from student where sname like '陈%'
--查找第二个字 为“文”的学生的姓名和成绩
select sname,score from student where sname like '_文%'

select sname,score from student where score in (70,80)
--升序降序排列
select * from student order by score asc
select * from student order by score desc
select * from student order by entertime asc
select * from student order by depno asc,score desc
--显示最高分和最低分
select sname,score from student where score=(select min(score)from student)
--平均分和总分
select avg(score) 平均分,sum(score) 总分 from student
--共计多少学生
select count(*) from student
--统计不同部门的平均分
select avg(score),depno,max(score) from student group by depno
--找出平均分低于60的部门
select avg(score),depno from student group by depno having avg(score)<60

--多表查询
select * from student,department where student.depno=department.depno
select sname,loc,student.depno from student,department
where student.depno=department.depno
--取别名
select * from student s,department d
where s.depno=d.depno order by depname asc
--子查询
--与陈六同一部门的所有学生,单行子查询
select * from student
where depno=(select depno from student where sname='陈六')
--得到各个部门的平均分
select avg(score),depno from student group by depno
--显示高于部门平均分的学生信息
select s.sname,s.score,tem.myavg,s.depno from student s,
(select avg(score) myavg,depno from student group by depno) tem
where s.depno=tem.depno and s.score>tem.myavg
--显示第一个至第四个入学的学生信息
select top 4 * from student order by entertime
--显示第3个至第5个入学的学生信息
select top 3 * from student where sname not in
(select top 2 sname from student order by entertime )
order by entertime
--显示3个人的平均分
select avg(score) from student where Id in (select top 3 Id from student)

--删除一张表
drop table student
drop table department

--修改记录
update student set score=100,address='南京' where Id=42
update student set score=score+5 where score<60
update student set entertime='2009-1-8' where sname='陈六'

--删除记录
delete from student where score=10