SQL SERVER基础

--sql注释
/*多行注释*/
--sql中不区分大小写

use master--存放当前数据库服务器的一些信息（比如：服务器上有哪些数据库，哪些用户名）
go--提交一个批处理，如：建库、建表、删库、删表等一些对象操作


--sysdatabases为master中的一个系统表，存放所有数据库信息
if exists(select * from sysdatabases where name='ly')--判断是否已存在该数据库
drop database ly--如果存在则删除
go

--创建数据库
create database ly 
on primary --默认即为主文件组，primary可省略
(
	name='ly_data',--主数据文件的逻辑名。一个数据库中的多个文件的逻辑名不能重复，是文件在数据库服务器中的唯一标识
	filename='C:\Users\Administrator\Desktop\ly.mdf',
	size=3mb,--文件的初始大小
	maxsize=100mb,--文件的最大值
	filegrowth=1mb
)
log on	--日志文件，做数据维护用的
(
	name='ly_log',
	filename='C:\Users\Administrator\Desktop\ly.ldf',
	size=3mb,
	maxsize=100mb,
	filegrowth=1mb
)
go

--建表
use ly
go
if exists(select * from sysobjects where name='student')
drop table student
go
create table student	--创建学生表
(
	stuid int identity(1,1) primary key,--主键约束
	stuname varchar(20),
	stuno varchar(50) unique not null,--唯一约束+不为空约束
	sex char(2) default('男'),--默认约束
	birthday datetime check(birthday>'1980-1-1' and birthday<'2010-1-1'),
	remark text
)
go

if exists(select * from sysobjects where name='grade')
drop table grade
go 
create table grade
(
	gradeid int identity(1001,1) primary key,
	score float default(0),
	--stuid int references student(stuid),----外键约束,一般不这么用
	stuid int,
	coursename varchar(50)
)
go


--------------对数据的操作----------------------
--增
insert into student(stuname,stuno,sex,birthday,remark) values('小红','10010','女','1991-1-1','这是小红的备注')
insert into student values('小橙','10011','男','1993-1-2','这是小橙的备注')
insert student values('小黄','10114','男','1993-10-21','这是小黄的备注')
insert student values('小a','10119','男','1996-5-3','这是小a的备注')
insert student values('小b','10115','男','1995-2-21','这是小b的备注')
insert student values('小c','10117','男','1993-6-23','这是小c的备注')
insert student values('小ab','10130','男','1993-10-25','这是小ab的备注')
insert student values('小%','10132','男','1989-12-28','这是小%的备注')

select * from student
--删
delete from student where stuno='10013'
--改
update student set sex='女',birthday='2000-2-2' where stuno='10114' 
--查（给字段名别名）
select 学号=stuid,stuname as '姓名',sex 性别 from student 
--------------------------------------
--查询前多少条数据
select top 5 *from student
--去除重复数据
select distinct sex from student
--设置条件
select * from student where not sex='男' and birthday<'1998-1-1'
--in关键字
select * from student where stuid in('1','2')
--模糊查询：关键字like(%表示任意多个字符，_表示任意一个字符)
select * from student where stuname like'%红'
select * from student where stuname like '小_'
--通配符(/和\均可)
select * from student where stuname like '小\%' escape '\'
--随机数
insert into grade(coursename,score,stuid) values('语文',RAND()*100,1)
insert into grade(coursename,score,stuid) values('数学',RAND()*100,1)
insert into grade(coursename,score,stuid) values('英文',RAND()*100,1)
insert into grade(coursename,score,stuid) values('语文',RAND()*100,2)
insert into grade(coursename,score,stuid) values('语文',RAND()*100,4)
insert into grade(coursename,score,stuid) values('语文',RAND()*100,3)
--排序：关键字order by asc(默认) 、desc
select * from grade where coursename='语文' order by score desc
--聚合函数:MAX MIN COUNT SUM AVG
select MAX(score) from grade where coursename='语文'
--分组查询(能查询到的列：分组依据的列、聚合函数操作的结果)
	--按课程名名分组
	select 课程=coursename,总分=sum(score),人数=COUNT(*),AVG(score) as '平均分',MAX(score) 最高分 from grade
	 group by coursename
	 having COUNT(*)>2	--分组的条件关键字一定是having，而非where
	--按学生编号分组
	select 学生编号=stuid,总分=sum(score),人数=COUNT(*),AVG(score) as '平均分',MAX(score) 最高分 from grade
	 group by stuid
--------------------------------------
--多表查询
select * from student,grade where student.stuid=grade.stuid

select a.*,b.*	
from student a,grade b	--给表命别名
 where a.stuid=b.stuid
--------------------------------------

--联接查询
	--inner join=join
	--内联，以a表为基准找b表中对应的数据，
	--找到了连接成一条数据显示出来，找不到的话不显示
	select * from grade a
	inner join student b
	on a.stuid=b.stuid	
	--左联接,以a表为基准找b表中对应的数据，
	--找到了连接成一条数据，找不到与空数据连接成一条显示
	select * from grade a
	left join student b
	on a.stuid=b.stuid	
	--右联接以b表为基准找a表中对应的数据，
	--找到了连接成一条数据，找不到与空数据连接成一条显示
	select * from grade a
	right join student b
	on b.stuid=a.stuid
	--全联接
	select * from grade a
	full join student b
	on a.stuid=b.stuid
	--交叉联接
	select * from grade a
	cross join student b
--------------------------------------
--子查询:把一个查询结果集当做一个表来使用
	--不及格人的信息
	select * from
	(select * from grade where score<60)a
	join student b
	on a.stuid=b.stuid
	--最高分人的信息
	--1.最高分--》最高分对应的id--》id对应的人是谁
	select * from student where stuid in
	(
	select stuid from grade where score in
	(select MAX(score) from grade)
	)
--------------------------------------
--联合查询（结果集必须有且仅有相同的字段，不能有text类型的字段）:union、union all
	--union:在联合时两个结果集若有重复数据，只显示一次
	select stuname,sex,stuno from student where sex='男' 
	union
	select stuname,sex,stuno from student where birthday>'1995-1-1'
	--union all:在联合时两个结果集若有重复数据，都显示
	select stuname,sex,stuno from student where sex='男' 
	union all
	select stuname,sex,stuno from student where birthday>'1995-1-1'

--------------------------------------
--分页
	--①top in
	--3条数据一页查询第二页：即查询不在前3条的前三条数据
select top 3 *from student 
where stuid not in
(select top 3 stuid from student)	
	--②row_num() over()
	--row_num over()函数可以对查询出来的数据按照stuid的升序方式做一个排序
select * from
(select *,ROW_NUMBER() over(order by stuid asc) as 编号 from student
)a
where a.编号>=3 and a.编号<=5
--top in和row_num() over()比较：
--top in:针对表中数据比较少的情况时，分页效率较高
--row_num() over():针对表中数据较多的情况时，分页效率高
--游标分页。。。略