建表及简单增删改查语法

--注释
/*
多行注释
*/
--使用master数据库
--master代表这个数据库服务器的相关信息
use master
go
--提交一个批处理
--数据库的多个操作是到go这个地方就提交的服务器的
--在建库建表，删除库删除表时加上go
--数据库中的所有数据库的信息
--select * from sysdatabases
--select * from sys.database_files
--创建数据库
--删除表


if exists( select * from sysdatabases where name='n1204')


drop database N1204


use N1204
go


create database N1204
on
(
name='n1204',--逻辑名
size=5mb,--初始大小
fileName='f:\\2013.01.21\\n1204.mdf',--数据文件的路径
maxsize=100mb,--最大大小
filegrowth=2mb--增长率
)
log on
(
name='n1204_log',--逻辑名
size=5mb,--初始大小
fileName='f:\\2013.01.21\\n1204_log.ldf',--数据文件的路径
filegrowth=10%--增长率
)
go


--创建表************************
--学生表student(stuNo,stuName,sex,birthday,remark)
--约束的目的：确保数据的完整性
--在每一个数据库中都有一张表SysObjects，记录的是这数据库的所有对象
if exists(select * from sysobjects where name='student')
drop table stduent
go
create table student
(
--identity自增长，基数1001，每次增长1
stuId int identity(1001,1) primary key,--设置为主键约束
stuNo varchar(20) unique not null,--唯一约束
stuName varchar(20) not null,
sex char(2) default('男'),--默认为男
birthday datetime check(birthday>'1949-10-1'),--检查约束生日在1949-10-1之后
remark text


)
go
--插入数据
insert into student values('f100307050151','奥特曼坑爹','男','1991-05-13','无')
insert into student values('f100307050152','奥特曼买鸟','男','1991-05-13','无')
insert into student values('f100307050153','奥特曼买鸡','男','1991-05-13','无')
insert into student values('f100307050154','奥特曼只买TP','男','1991-05-13','无')
select  * from student
--标准的写法
insert into student(stuName,birthday,stuNo)values('洋洋','1991-05-03','f1003070156')
--创建分数表grade(gradeId,result,courseName,stuId)
if exists(select * from sysobjects where name='grade')
drop table grade
go


create table grade
(
gradeId int identity (2001,1) primary key,
result float check(result>0 and result<100),
courseName varchar(50) not null,
stuId varchar(20) references student(stuno),
)
go
select * from grade
insert into grade values(90,'英语','f100307050151')
--删除操作
delete from student where stuNo='f100307050153'
--两表有主外键，做数据的删除时，先删除外键表中的数据再删除主表中的数据
delete from grade where stuId='f100307050151'
delete from student where stuNo='f100307050151'
--修改操作
update student 
set stuName='奥特曼坑爹',remark='三号学生'
where stuNo='f100307050152'
--区间查询
select stuname as '学生姓名',stuno as '学号'
from student
where birthday between '1991-1-1' and '1995-1-1'


--多条件查询
--性别为女，并且生日大于1992-1-1
select *
from student
where sex='女'and birthday>1992-1-1
--and并,or或,not非,
select * from student
order by birthday desc --desc降序,asc升序


--模糊查询
select * from student where stuname like '%TP'--   %代表任意多个字符,_代表一个任意字符


--前多少条数据
select top 2 * from student


--去重复项
select distinct (sex)
from student


--in 字句
select * from student 
where stuId in(1008,1007,1004)


--聚合函数*****




select SUM(stuId)总分 ,MAX(stuId)最高分,MIN(stuId)最低分,AVG(stuId)平均,
count(stuId)记录数
 --聚合函数做查询时不能取表中的某列值
from student 


--分组查询
--分组能拿到的列有聚合函数统计的列与被分组的列
select SUM(result)组总分,COUNT(stuId)组成员数,stuId
from grade
group by stuId --按stuid分组
having SUM(result)>150--分组中加条件用having
--***************************************************
--子查询
--性别为女的人的分数查询出来
select * from grade where stuId in (select stuId from student where sex='女')
--把最低分的人的信息查询出来
select *from student where stuId in(
select stuid from grade where result in
(select MIN(result)
from grade))


--分页查询*******************************************
--每页显示两条数据,查询第二页
select * from student
--不在前两条的前两条
select top 2* from student
where stuId not in
(select top 4 stuId from student)
--查询表中的第31到40条记录
select top 10 *from student
where stuId not in

(select top 30 stuId from student)

简单示例：

--2，查询选修所有课程的学生信息
select stuid from grade group by stuid having count(*)=(select count (distinct courseName) from grade)
--3, 将所有女生成绩的语文成绩 + 10
update grade set result=result+10 
where courseName='语文'and stuid in
(select stuid from student where sex='女')




--4，查询学生id， 姓名 ，总成绩，平均成绩
select a.stuName,b.* from student a
join
(select avg(result)'平均成绩',sum(result)'总成绩',stuid from grade group by stuid)b
on a.stuid=b.stuid
--1，每科成绩的第一名,最后一名(一条sql)
select b.stuName,a.courseName,a.最高分 fs from student b join grade c on b.stuid=c.stuid
join 
(
select max(result)'最高分',min(result)'最低分',courseName from grade group by courseName
)a
on c.courseName=a.courseName and c.result=a.最高分
union
select b.stuName,a.courseName,a.最低分 fs from student b join grade c on b.stuid=c.stuid
join 
(
select max(result)'最高分',min(result)'最低分',courseName from grade group by courseName
)a
on c.courseName=a.courseName and c.result=a.最低分




--5.每科成绩的前三名


select * from grade a where result in 
(
select top 3 result from grade where courseName=a.courseName order by result desc
)
order by courseName desc,result desc
--6.删除学生表中的重复人的信息。（重复的人的信息只保留一条)
delete from student where stuid not in
(
select min(stuid) from student group by stuName
)


思考:
--查询 行转列
--学生id 名称 语文 数学 英语
--1000   张三 30   20   30
select stuid '学生id',
语文=sum(case courseName when '语文'then result else 0 end),
数学=sum(case courseName when '高数'then result else 0 end),
英语=sum(case courseName when '英语'then result else 0 end)
 from grade group by stuid