SQL Server基础

use master 

create database step2_unit2 --创建主文件和日志文件
on primary
(
	Name='step2_unit2',	--逻辑名称,
	FileName='D:\SQLServerData\step2_unit2.mdf',	--物理名称,
	size=8mb,	--初始容量大小,
	filegrowth=8mb,	--每次增长100M,
	filegrowth=20%,	--	每次增长20%,
	maxsize=200mb	--文件最大值，不写就是不限制
);
--创建次数据文件
alter database step2_unit2
add file
(
	Name='step2_unit3_ndf',	--逻辑名称,
	FileName='D:\SQLServerData\step2_unit3.ndf',	--物理名称,
	size=8mb,	--初始容量大小,
	filegrowth=20%,	--	每次增长20%,
	maxsize=200mb	--文件最大值，不写就是不限制
);

--简写方式创建数据库
create database step2_unit2;

--删除数据库（在删除数据库时，一定要切换到别的数据库，不能时当前正在使用的数据库）
drop database step2_unit2;

--切换数据库
use step2_unit2;

--查看数据库信息
exec sp_helpdb step2_unit2;

--修改数据库
execute sp_renamedb 'aaa','step2_unit2';

--查看数据库信息
exec sp_helpdb step2_unit2;

--修改数据库
execute sp_renamedb 'aaa','step2_unit2';

--备份数据库
backup database step2_unit2 to disk='D:\step2_unit2_bak.bak' with format;

--换完数据库
restore database step2_unit2 from disk='D:\step2_unit2_bak.bak';

--如果数据库已经存在，需要替换
restore database step2_unit2 from disk='D:\step2_unit2_bak.bak' with replace;

--附加与分离
--分离：不删除数据库文件，但是可以把你从DBMS中移除掉
execute sp_detach_db 'step2_unit2'; 

--附加数据库
execute sp_attach_db 'step2_unit2','D:\SQLServerData\step2_unit2.mdf';

1、Character字符串：

数据类型	描述	存储
char(n)	固定长度的字符串。最多8000个字符。	n
varchar(n)	可变长度的字符串。最多8000个字符。
varchar(max)	可变长度的字符串。最多1,073,741,824个字符。
text	可变长度的字符串。最多2G字符数据。

Char与varchar对比

1. CHAR的长度是不可变的，而varchar的长度是可变的，也就是说，定义一个char[10]和varchar【10】，如果存进去的时’ABCD’，那么char所占的长度依然时10，除了字符’ABCD’外，后面跟六个空格，而varchar的长度为8，取数据的时候，char类型的要用trim()去掉多余的空格，而varchar类型是不需要的。
2. char的存取速度要比varchar快得多，因为其长度固定，方便程序的存储与查找；但是char为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可以说是以空间换取时间效率，而varchar则是以空间效率为首位的。
3. char的存储方式是，一个英文字符（ASCII）占用1个字节，一个汉字占用两个字节；而varchar的存储方式是，一个英文字符占用2个字节，一个汉字也占用2个字节。
4. 两者的存储数据都是非Unicode的字符数据。

2、Unicode字符串：

数据类型	描述	存储
nchar(n)	固定长度的Unicode数据。最多4000个字符。
nvarchar(n)	可变长度的Unicode数据。最多4000个字符。
nvarchar(max)	可变长度的Unicode数据。最多536,870,912个字符。
ntext	可变长度的Unicode数据。最多2G字符数据。

char(n)类型
将int ASCII代码转换为字符的字符串函数。参数n是介于0和255之间的整数。如果整数表达式不在此范围内，将返回NULL值。

nchar(n)类型
包含n个字符的固定长度Unicode字符数据。N的值必须介于1与4000之间。存储大小为n字节的两倍。nchar在SQL-92中的同义词为national char和national character。

nvarchar(n)类型
包含n个字符的可变长度Unicode字符数据。n的值必须介于1与4000之间。字节的存储大小是所输入字符个数的两倍。所输入的数据字符长度可以为零。

nchar和nvarchar
nchar是固定长度Unicode数据的数据类型，nvarchar是可变长度Unicode数据的数据类型，二者均使用UNICODE UCS-2字符集。

3、Binary类型：

数据类型	描述	存储
bit	允许0,1或NULL
binary(n)	固定长度的二进制数据。最多8000字节
varbinary(n)	可变长度的二进制数据。最多8000字节
varbinary(max)	可变长度的二进制数据。最多2G字节
image	可变长度的二进制数据。最多2G

4、Number类型：

数据类型	描述	存储
tinyint	允许从0到255的所有数字	1字节
smallint	允许从-32768到32767的所有数字	2字节
int	允许从-2147,483,648到2147,483,647的所有数字	4字节
bigint	允许从-2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)的所有数字	8字节
smallmoney	介于-2147,483,648和2147,483,647之间的所有货币数据	4字节
money	介于-2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807)之间的所有货币数据	8字节
float(n)	从-1.79E + 308到1.79E + 308的浮动精度数字数据。参数n指示该字段保存4字节还是8字节。float（24）baocun 4字节，而float(53)保存8字节。n的默认值是53。	4字节或8字节
real	从-3.4E + 38到3.4E + 38的浮动精度数字数据。	4字节

5、Date类型

数据类型	描述	存储
datetime	从1753年1月1日到9999年12月31日，精度为3.33毫秒	8字节
datetime2	从1753年1月1日到9999年12月31日，精度为100纳秒	6~8字节
smalldatetime	从1900年1月1日到2079年6月6日，精度为1分钟	4字节
date	仅存储日期。从0001年1月1日到9999年12月31日	3字节
time	仅存储时间。精度为100纳秒	3~5字节
datetimeoffset	与datetiem2相同，外加时区偏移	8~10字节
timestamp	存储唯一的数字，每当创建或秀嘎某行时，该数字会更新。timestamp基于内部时钟，不对应真是时间。每个表只能有一个timestamp变量。	3~5字节

6、其他数据类型：

数据类型	描述
sql_variant	存储最多8000字节不同数据类型的数据，除了text、ntext以及timestamp
uniqueidentifier	存储全局标识符（GUID）
xml	存储XML格式化数据，最多2G
cursor	存储对用于数据库操作的指针的引用
table	存储结果集，共稍后处理

--创建表
use step2_unit2;
go
create table Student
(
	Id int,
	StudentNo char(11),
	Phone char(11),
	Sex	nchar(4)
);

--修改表格，添加姓名字段，varchar(20)
alter table Student add NickName varchar(20);

--修改字段类型，NickName更改为nvarchar（30）
alter table Student alter column NickName nvarchar(30);

--删除字段
alter table Student drop column Phone;

1. 主键约束
主键只能有一个，但是可以由1~n个字段组成，多个字段组成的主键叫做复合主键。

--创建主键
--方式一：
create table Student1
(
	Id int constraint PK_Id primary key not null,	--主键约束（行级主键约束）	自定义主键约束名称
	NickName nvarchar(15),	-- unicode
	StudentNo char(11),	-- 学号
	Sex nchar(2),
	Account varchar(20)	-- 账号
);
--方式二：
create table Student2
(
	Id int,
	NickName nvarchar(15),	-- unicode
	StudentNo char(11),	-- 学号
	Sex nchar(2),
	Account varchar(20),	-- 账号
	[Password] varchar(50)	-- 密码
	constraint PK_Id primary key(Id)		-- 主键约束（表级主键约束）	自定义主键约束名称
);

--方式三：
create table Student2
(
	Id int,
	NickName nvarchar(15),	-- unicode
	StudentNo char(11),	-- 学号
	Sex nchar(2),
	Account varchar(20),	-- 账号
	[Password] varchar(50)	-- 密码
);
alter table Student2 
add constraint pk_Student2 primary key(Id);

2、外键约束

--外键约束
create table Category
(
	Id int constraint pk_Category primary key not null,
	CategoryName varchar(20),
);
--方式一：
create table Product
(
	Id int constraint pk_Product primary key not null,
	CategoryId int constraint fk_CategoryId foreign key references Category(Id),	--	外键（行级），商品分类
	ProductName varchar(30),
	price decimal(10,2)
);
--方式二：
create table Product
(
	Id int constraint pk_Product primary key not null,
	CategoryId int,
	ProductName varchar(30),
	price decimal(10,2),
	constraint fk_CategoryId foreign key(CategoryId) references Category(Id)	--	表级外键，商品分类
);

--方式三：
create table Product
(
	Id int constraint pk_Product primary key not null,
	CategoryId int,
	ProductName varchar(30),
	price decimal(10,2)
);
alter table Product
add constraint fk_CategoryId foreign key(CategoryId) references Category(Id);

3、唯一约束
可以为空，但是注意有一个为空

--唯一约束可以为空，但是只能有一个为空
create table Student
(
	Id int primary key,
	StudentNo char(11) unique	--唯一约束
);

4、检查约束

create table Student
(
	Id int primary key,
	NickName nvarchar(15),	--unicode
	StudentNo char(11),	--学号
	Sex nchar(2) check(Sex in('男','女')),
	Account varchar(20) check (len(Account) between 6 and 16),	--账号
	[Password]	varchar(50) check([Password] like '[A-Z]'),	--密码
	Age tinyint check (Age>18),
	AddTime datetime default getdate()		--getdate()获取系统当前时间 --默认值约束
);
--所有的字段，如果没有特殊要求，建议大家设置为非空，这样有利于数据的检索

5、默认值约束

6、非空约束
not null

create database DBStudent
on primary
(
	Name='DBStudent',
	filename='D:\SQLServerData.mdf',
	size=8MB,
	filegrowth=20%,
	maxsize=200MB
);

use DBStudent;
create table Student
(
	stu_id int constraint PK_Student primary key ,
	stu_name nvarchar(30) not null,
	stu_tel char(11),
	stu_sex varchar check(stu_sex in('男','女')) default '男',
	stu_address nvarchar(50) default '北京',
	stu_age int constraint stu_age_check check(stu_age between 14 and 40)
);
alter table Student drop constraint stu_age_check;
alter table Student drop column stu_age;

--添加数据
insert into Student(Id,NickName,StudentNo,Sex,Account,[Password]) 
values (3,'狗子','1003','男','USER2','123456');

--添加多行
insert into Student(Id,NickName,StudentNo,Sex,Account,[Password]) 
values 
(4,'狗子','1004','男','qqq','123456'),
(5,'铁柱','1005','女','ss','123456'),
(6,'小鱼','1006','男','fgf','123456'),
(7,'铁锤','1007','女','jjj','123456')

--简写的添加
insert into Student values(8,'猴子','1008','女','user4','123456');

--查看数据
select * from Student;

--修改数据
--update 表名 set 要修改的字段=值 where 条件
update Student set StudentNo=1000 where Id=1;

--修改多个字段
update Student set Id=10,NickName='王五',Sex='女' where Id=4;

--删除数据（删除所有数据）
delete from Student

--按条件删除
delete from Student where Id=10;

drop table Student

--主键生成
--主键自增
create table Student
(
	Id int primary key identity,	-- 主键约束（行级主键约束）按1自增
	NickName nvarchar(15),	-- uniocde
	StudentNo char(11),	-- 学号
	Sex nchar(2),
	Account varchar(20),	-- 账号
	[Password] varchar(50)	--密码
);

insert Student(NickName,StudentNo,Sex,Account,[Password])
values 
('张三','1000','男','USER1','123456'),
('李四','1001','女','USER2','123456'),
('王五','1002','男','USER3','123456')

--delete 删除的时候，自增的种子没有恢复
delete from Student

--truncate 删除的时候，自增的种子恢复	真正的清空数据，不能接条件
truncate table Student;
select * from Student

--主键自增
create table Student
(
	Id int primary key identity(2,2),	-- 主键约束（行级主键约束）从2开始，按2自增
	NickName nvarchar(15),	-- uniocde
	StudentNo char(11),	-- 学号
	Sex nchar(2),
	Account varchar(20),	-- 账号
	[Password] varchar(50)	--密码
);

create table Student
(
	Id uniqueidentifier primary key,	-- 此时不用用自增，uniqueidentifier唯一标识符
	NickName nvarchar(15),	-- uniocde
	StudentNo char(11),	-- 学号
	Sex nchar(2),
	Account varchar(20),	-- 账号
	[Password] varchar(50)	--密码
);
--这个时候，一定要添加主键，因为这时候的主键并不是自增的
--newid()；唯一标识符，在单击上面不会重复（并发量小的时候）
insert into Student(Id,NickName,StudentNo,Sex,Account,[Password]) 
values 
(NEWID(),'狗子','1004','男','qqq','123456'),
(NEWID(),'铁柱','1005','女','ss','123456'),
(NEWID(),'小鱼','1006','男','fgf','123456'),
(NEWID(),'铁锤','1007','女','jjj','123456')

--联级删除，联级更新(基于外键的)
create table Category
(
	Id int primary key identity,
	[Name] varchar(30),
);

create table Product
(
	Id int primary key identity,
	ProductName nvarchar(30),
	CategoryId int constraint FK_Product foreign key references Category(Id) on delete cascade on update cascade	--联级删除，联级更新

);

--一定要先添加外键
insert into Category values('家具'),('电器'),('水果')
go
insert into Product values
('杯子',1),('被子',1),
('电饭煲',2),('电磁炉',2),
('苹果',3),('香蕉',3)

select * from Category
select * from Product

--联级之前，不能直接删
delete from Category where [Name]='家具'
--如果要删，得先把数据关联的数据全部删除
delete from Product where CategoryId=1;

--联级删除


--第六章	简单查询
--学生表
create table StudentInfo
(
	stuId char(10) constraint PK_StudentInfo primary key,
	stuName varchar(20),
	ClassId int,	--班级编号，逻辑外键，并不是真正的外键约束
	stuPhone char(11),	--电话号码
	stuSex char(4),	--性别
	stuBirthday datetime	--生日
);
go
--班级表
create table classInfo
(
	Id int primary key identity,	--班级的主键
	Name varchar(30),	--班级名称
	College varchar(20)	--学院
);
go
--成绩表
create table StudentScore
(
	Id int primary key identity,	--成绩主键
	stuId char(20),	--学生外键，逻辑外键
	CourseName varchar(20),	--课程名
	theoryScore int,	--理论成绩
	skillScore int	--技能成绩
);

insert into dbo.StudentInfo(stuId,stuName,ClassId,stuPhone,stuSex,stuBirthday) 
values
('180325011','任我行',5,'13823204456','男','1999-09-09'),
('180325012','张三',4,'13823204452','女','1998-08-08'),
('180325013','李四',2,'18899236483','男','1997-07-07'),
('180325014','王五',1,'13582575386','女','1998-08-08'),
('180325015','帅天行',5,'13823204542','男','1998-09-09'),
('180325016','叶星辰',5,'17621430587','男','1998-05-05'),
('180325017','赵日天',0,'13675102796','男','1997-07-15')
go

insert into dbo.classInfo([Name],College) values
('软件技术1班','计算机系'),
('会计1班','经理管理系'),
('会计2班','经济管理系'),
('欧美软件外包班','计算机系'),
('会计3班','经济管理系');
go

insert into dbo.StudentScore(stuId,CourseName,theoryScore,skillScore)
values
('180325011','会计从业',80,90),
('180325011','C#入门编程',99,100),
('180325012','SQLServer编程',70,75),
('180325013','会计从业',93,80),
('180325014','C#高级编程',99,99),
('180325015','会计从业',74,40),
('180325015','C#入门编程',80,90);

--1、如何查看表中所有数据？
--查看学生表
--select：查询
--*代表表中所有的列
select * from StudentInfo;
--*号，在数据库优化的章节中，不建议使用*号，因为系统要解析这个*号，需要一点点时间
--实际开发中，如果字段过多，我们查询时，之查出业务中所需要的字段
select stuName,stuId from StudentInfo;
--查询班级表，执行
select Id,Name,College from classInfo

--2、如何查询指定几个字段的值？
--查询学生的姓名，性别，生日，班级
select stuName,stuSex,stuBirthday,ClassId from StudentInfo

--3、如何给字段取别名？（可以省略as）
--把学生表中所有的字段都取别名
select 
stuId as '主键',
stuName as '姓名',
ClassId as '课程编号', 
stuPhone as '手机号',
stuSex as '性别',
stuBirthday as '生日' from StudentInfo

--4、distinct的用法？多个字段的用法？
--distinct：出去重复项
select distinct ClassId from StudentInfo
select distinct stuSex from StudentInfo
--指的是两个字段组合在一起，不会重复
select distinct stuSex,ClassId from StudentInfo

--这两个结果集为什么会一样？
select stuId,CourseName from StudentScore
--distinct 后面跟着几个字段，表示 除去这几个字段（组合在一起）重复的意思
select distinct stuId,CourseName from StudentScore

--这样写会去除重复吗？不会；因为主键不会重复
select distinct * from StudentInfo

--5、top的用法？
--取前3条数据
--top：前.....条
select top 3 * from StudentInfo

--6、top...percent(百分比)？
--查询前30%的学生数据
--从这个故事告诉我们，数据没有半条
select top 50 percent * from StudentInfo

--7、查询年龄大于20岁的？
--year：年
--年龄=当前年份 - 生日
select *,(year(GETDATE())-year(stuBirthday)) as '年龄' 
from StudentInfo where (year(GETDATE())-year(stuBirthday))>20

--查询学生的，姓名，性别，年龄	所有字段取别名
--	年龄 = 当前年份 - 生日所在年份
select stuName as 姓名,stuSex as 性别,(year(GETDATE())-year(stuBirthday)) as '年龄' from StudentInfo

--8、查询95后的女士
--95后：1995~~1999
select * from dbo.StudentInfo where year(stuBirthday) between 1995 and 1999 and stuSex='女'

--9、查询姓李的学生信息
--like：模糊查询，中文意思是：像...
select * from StudentInfo where stuName like '李%'

--10、列出技能成绩大于90分的成绩单
select *  from dbo.StudentScore where skillScore>90

--11、查询课程包含“SQLServer”的成绩单
select theoryScore,skillScore from dbo.StudentScore where CourseName like '%SQL%'

--12、查询每个学生不同的成绩列表
select distinct stuId,skillScore from StudentScore

--13、查询年龄大于20岁前3条学生的姓名，年龄，所有字段取别名
select top 3 stuName as 姓名,year(GETDATE())-year(stuBirthday) as 年龄 from StudentInfo where year(GETDATE())-year(stuBirthday)>20

--第七章 条件查询
--1、select ..from 表名[条件] order by 要排序的字段 asc/desc
--asc:升序
--desc:降序
--将成绩从高到低进行排序
select * from StudentScore order by (theoryScore + skillScore) desc;
--查询学生表，按姓名升序显示
select * from StudentInfo order by stuName asc;	--asc可以省略
--2、多字段如何排序？
--语法：select...from 表名 [条件] order by 第一要排序的字段asc/desc,第二要排序的字段asc/desc
--查询成绩表，先按照学生编号从低到高排序，再按成绩从高到低显示
select * from StudentScore order by stuId asc,(theoryScore + skillScore) desc;
--查询学生信息，先按班级进行升序，再按生日进行降序
select * from StudentInfo order by ClassId asc,stuBirthday desc;
--3、如何使用关系表达式查询（大于，小于，等于）？
--查询，班级编号在1，3，5，7里的学生信息
select * from StudentInfo where ClassId in(1,3,5,7);
--查询班级编号	不是偶数的学生信息
select * from StudentInfo where (ClassId % 2)=1
--查询技能成绩大于90分的成绩信息
select * from StudentScore where theoryScore>90 and skillScore>90;
--4、如何使用between...and的语法？
--between...and；在...之间，应用范围：数字类型
--查询学生，年龄在20-30之间学生信息
select * from StudentInfo where year(GETDATE())-year(stuBirthday) between 20 and 30;
--查询技能成绩在60-80之间的成绩信息
--写法一：
select * from StudentScore where skillScore between 60 and 80;
--写法二：
select * from StudentScore where skillScore>=60 and skillScore<=80;

第七单元 条件查询，分组查询
2、聚合查询
如何统计个数，平均分，最大数，最小数，求和？

函数	字段类型	描述
Min(字段)	数字类型	最小值
Max(字段)	数字类型	最大值
Sum(字段)	数字类型	总和
count(字段)	无限制	个数
count(distinct字段)	无限制	去重后，求个数
avg(字段)	数字类型	平均数