SQL初级阶段笔记
- DataBase Management Stystem(数据库管理系统)简称：DBSM；虽然DBSM并不等于数据库，但行业内通常将DBSM称为数据库，所以一般来说数据库就指的是DBSM。
- 简单来讲DBSM就是我们知道的：MYSQL、MSSQLServer、DB2、Oracle、Access、Sybase等。
- SQL<>SQLServer<>MSSQLServer。/*注意这里的"<>"代表不等于*/。
- 主键分为业务主键和逻辑主键。
所谓的业务主键：使用有业务意义的字段做主键。
所谓的逻辑主键：使用没有任何业务意义的字段做主键。（完全给程序看的）
5. 主键的设置要求：
1. 不会重复的列；
2. 推荐用逻辑主键作为主键；
6. 为什么要使用主键：
一个表中可以没有主键，但是会非常难以处理，因此没有特殊理由，表中要设置主键。
7. 何为外键：
比如说：我要进货，可能会进不同厂家的货物，由于在同一厂家我不一定只进一类东西，因此当我记录账单的时候，必然要记录厂家的地址、电话等，对于不同类 货物且同一厂家进行记录时，还要每次都要重复的写厂家地址等，在数据库中我们可以用一个代号将一些重复的数据进行替代，这样也减少了存储空间的使用，对于这 个代号来说，它是重复要写地址所在表中的主键，且又是主表中的外键！
8. 打开Microsoft SQL Server Management Studio之后的连接如下图：
服务器名称填写方式：计算机全称\实例名 或者 .\实例名 或者 还可以直接输入"(local)"进行连接。这样就可以通过本地验证进行连接
9. 新建数据库：
在连接后的“对象之源管理器”中，我们可以看到“数据库”，然后右键点击数据库，再出现的菜单栏中选中“新建数据库(N)”然后如下图所示：
在上边只要填写数据库名称就可以了！然后点击确定就OK了！
10. 新建表：
展开你新建的数据库，右键点击“表”，再出现的菜单栏中选中"新建表(N)",然后就可以添加字段也就是（列），并对数据类型进行设置，相关设置我会在后边写 出。
也可以用SQL语句建立新表：
CREATE TABLE 表名(字段 类型名 是否为Null,字段 类型名 是否为Null,字段 类型名 是否为Null.............)
例如：CREATE TABLE Preson(ID int NOT NULL,Name nvarchar(50),Age int)
这样就建立了如下表：
| ID | Name | Age |
删除表：
drop table 表名。
11. 常见的数据类型有：
1. 字符串数据类型
char
此数据类型可存储1~8000个定长字符串，字符串长度在创建时指定；如未指定，默认为char(1)。每个字符占用1byte存储空间。
nchar
此数据类型可存储1~4000个定长Unicode字符串，字符串长度在创建时指定；如未指定，默认为nchar(1)。每个字符占用2bytes存储空间。
varchar
此数据类型可存储最大值为8000个字符的可变长字符串。可变长字符串的最大长度在创建时指定，如varchar(50)，每个字符占用1byte存储空间。
nvarchar
此数据类型可存储最大值为4000个字符可变长Unicode字符串。可变长Unicode字符串的最大长度在创建时指定，如nvarchar(50)，每个字符占用 2bytes存储空间。
text
此数据类型可存储最大值为2147483647个字符的变长文本，并且无需指定其初始值，每个字符占用1byte存储空间，一般用来存储大段的文章。text数 据类型实际上是一个Large Object数据类型， 默认情况下，此类型的数据不是存储在数据行内，而是存储于独立的Large Object数据页上。另 外，text数据类型不能做为函数、存储过程或触发器中的参数来用。
ntext
同text数据类型，只不过存储的是最大值为1073741823个字符的Unicode变长文本，每个字符占用1byte存储空间。
说明：无论使用哪种字符串数据类型，字符串值必须放在引号内，推荐使用单引号。
一般情况下我是这样区别
varchar和nvarchar的区别：
nvarchar中可以有像“汉字”、“日语”等文字，但varchar不允许。
varchar(10)和char(10)
char(10)不管你的字符够不够10位，我都会将空间给你留下。
varchar(10)你有几个我给你几个，但前提是不能超过10。
注意：电话号码用的是字符型。
2. 数值数据类型
bit
此数据类型存储值为0或1的二进制字段。占用1byte存储空间。
tinyint
此数据类型存储0~255的整数，占用1byte存储空间。
smallint
此数据类型存储-32768~32767的整数，占用2bytes存储空间。
int
此数据类型存储-2147483648~2147483647的整数，占用4bytes存储空间。
bigint
此数据类型存储-9223372036854775808~9223372036854775807的整数，占用8bytes存储空间。
decimal/numeric
这两个数据类型功能相同，均为存储精度可变的浮点值。但推荐采用decimal，因其存储的数据“更有说明性”。此种数据类型由两个值来确定 decimal(p,s)，p为精度，s为标量，如decimal(3,2)，其中数值2为小数的位数，那么decimal(3,2)可用来存储如1.28这样的浮点数。此种数据类型占用的存储空 间取决于精度值p。p为1~9，占用5bytes存储空间；p为10~19，占用9bytes存储空间；p为20~28，占用13bytes存储空间；p为29~38，占用17bytes存储空 间。
float
此数据类型存储1~53的可变精度的浮点值，精度表示为float(n)，n表示科学记数法的尾数，取值范围为-1.79E+308~-2.23E-308的负数和2.23E- 308~1.79E+308的正数。其存储空间由精度值 决定，n为1~24，占用4bytes存储空间；n为25~53，占用8bytes存储空间。
real
此数据类型存储-3.40E+38~-1.18E-38的负数和1.18E~3.40E+38的正数。占用4bytes存储空间。
smallmoney
此数据类型存储-214748.3648~214748.3647的货币值，精确到小数后4位。占用4bytes存储空间。
money
此数据类型存储-922337203685477.5808~922337203685477.5807的货币值，精确到小数后4位。占用8bytes存储空间。
3. 日期和时间数据类型
smalldatetime
此数据类型存储从1900年1月1日到2079年6月6日的日期。占用4btyes存储空间。
datetime
此数据类型存储从1753年1月1日到9999年12月31日的日期。占用8bytes存储空间。
4. 二进制数据类型
binary
此数据类型存储1~8000个字符的二进制数据，其指定长度即为占用的存储空间。
varbinary
此数据类型存储可变长的二进制数据，可在创建时指定其具体长度，也可不指定。
5. 其它数据类型
rowversion/timestamp
这两种数据类型功能一样，但Microsoft建议在任何情况下尽可能地指明rowversion而不是timestamp，因为rowversion更加准确地反应了数据类型的 真实性质。timestamp数据类型跟时间完全无 关，它表明数据库中的数据修改发生的相对顺序。不要在键（尤其是主键）中使用timestamp 列，因为每次修改行时，timestamp值都会更改。当指定数据类型为rowversion或timestamp，那么 SQL Server会在对表的插入或删除等 更新操作时自动生成一个新值，并把这个新值放在合适的字段里。此类型数据占用8bytes存储空间。
uniqueidentifier
此类型数据存储二进制值，其作用与全局唯一标识符(GUID)一样。GUID 主要用于在有多个节点、多台计算机的网络中，分配必须具有唯一性的标识符。 占用16bytes存储空间。
12. 主键的设置：
右键点击如图所示中的位置，在弹出来的菜单中选中设置主键就OK了。当想取消将其作为主键的时候右键点击，选中“删除主键”就OK了。
有一些类型是可以设置主键，但有些不能能够设置主键。比如说：nvarchar(max)就不能、而nvarchar(50)可以！
一般有两个设置主键是合理的（int(digint)、uniqueidentifier）
13.字段的添加和删除：
在上图12中，对应的“列命”、“属性类型”、“允许Null值”中对应填写就可以。若保存后，想进行修改，就右键点击你新建的表——》设计(G)——》然后就可以填写 了！
不知大家注意了没有：主键不能为空！
14. 在表中填充数据：
右键点击你新建的表——》编辑前200行(E)——》然后就可以填写了！
15. SQL语句：是和DBMS交谈专用的语句。
注意：SQL字符串用单引号；
SQL语句大小写不敏感；
SQL语句主要分：DDL(数据定义语言)Create Table 、Drop Table等（改变表的结构）
DML(数据操作语言)Select、Insert等（不改变表的结构）
16. 填充表或者插入数据：
insert into 表名(字段1，字段2，字段3.) values(值1，值2，值3) 例如：insert into person1(id,Name,Age) values(1,'泰山北斗@阿龙',21)
17. 如何解决开发中系统自动添加排序，也就是说，避免主键的重复 ----- 设置标识(zhi)规范：
右键点击表--》设计--》如下图所示：将”否“改成“是”
标识规范修改不成的解决办法：
工具--》选项--》Designs--》将”阻止保存要求创建表的更改“前面的对号去掉。
如下图：
18. Guid的newid（）方法：
newid()目的是产生不相同的随机数。
19. int 自增长字段和Guid的区别：
INT自增长字段的优点：占用空间小、无序开发人员干预 、易读、
缺点：效率低；
Guid的优点：效率高、数据导入导出方便；
缺点：占用空间大、不易读；
一般来讲，大多数都使用Guid。
若果使用int 自增字段，则在insert中可以省略对应的字段。
20. insert可以省略表名后的列名，但不推荐使用。
insert into Person0(FNumber,FName,FAge,Fsalary) values('liqianlong','李乾龙',21,2.15)
或者 insert into Person0 values('liqianlong','李乾龙',21,2.15)也可以。
insert into 就是向表中插入数据
21. update -- 更新数据：
update 表名 Set 列1名=值，列2名=值.........
更新的时候注意中文前面要加”N“
update Person set Age="23"
where Age>20;
上面两句话的意思是：将20以上的Age更新为23
22. 运算符：
+、 —、 *、 /、=等于() 、<>（不等于） or （或者）、 and （并且） 、not（非）
21. delete的使用：
一般和from一起使用。例如：
delete from Person where Age>10; /*清理表中年龄大于10的数据！*/
23. Delete和Drop的区别：
delete是将表中数据清理了，并没有删除表；
Drop是直接将表删除掉；
24. 用SQL设置主键
例如：create table Person0(FNumber varchar(20),FName varchar(20),FAge int,FSalary numeric(10,2),PRIMARY key(FNumber))
上面的primary key就是主键的意思，在SQL中是通过这样来设置主键的。对于numeric(10,2)来讲，10是精度（小数点左右两端都包括），2是小数位数。
25. select * from 表名
1. 例如：select * from Person0
where FAge>20;
注意：上句表示从表Person中塞选FAge 大于20的数据。select意思是“选择、挑选”
2. 也可以选择展示的列：
例如：select FNumber,FAge from Person0
where FAge>20;
3. 利用AS 为各个字段起一个别名：
例如：select FName as 姓名，FAge as 年龄 from Person0
注意：这里的“姓名”、“年龄”可以不加单引号。
26. select 的另外用法：
检索与任何表不关联的语句：
例如： select newid()【取得随机数】 或者 select 1+1 或者 select getdata()【取得当前时间】或者 select @@version【查询SQL Server版本】等。
也可以：select newid() as 随机数,1+1 as 列1,getdata() as 当前时间；
select count(*) from Person0 /*读出表的数据个数*/
select max(FAge) from Person0 /*读出FAge最大的那行数据*/
select min(FAge) from Person0 /* 读出FAge最小的那行数据*/
select avg(FAge) from Person0 /* 读出平均年龄*/
select sum(FAge) from Person0 /*读出所有年龄的和*/
以上的select 都可以加上条件 where。
27. 通过order by对数据进行排序：
例如：select *from Person0
order by FAge;
注意：默认是升序。即（ASC）虽然默认为升序，可以不写，但尽量写上，有利于代码的读取。
降序：即（DESC）
例如：select *from Person0
order by FAge ASC; /*升序*/
select *from Person0
order by FAge CESC; /*降序*/
有时候想实现这么一种情况：“当年龄相同的时候，我想让其按照工资进行排序，这样我们应该怎么做？？”如下：
select *from Person0
where FAge>20 /*这是我自己加的条件，原本没有*/
order by FAge ASC,FSalary DESC;
注意：where 不能放在order by 的后面。
28. 假如我们下查询一"L"开头的用户的信息，我们就不能通过以前的方法来查询了，因此就需要“通配符”
通配符分为：单字符通配符：半角下划线“_”,它匹配单个出现的字符
多字符通配符：半角百分号“%”,它匹配任意次数（零个或多个）出现的任意字符。不如说：“k%”代表以k开头的、任意长度的字符串；
再比如：检索姓名中 包含字母n的员工信息：select *from Person where FName like '%n%'注意：结尾是n的也会被搜索到。
29. 需要注意的一点是：在SQL中的NULL代表的是“不知道”，并不是“没有”。
select ''+'123' /*结果是123*/
select null+'123' /*结果是null*/
30. select *from Person0
where FName=null;【将其标记为A】和
select *from Person0
where FName is null;【将其标记为B】的区别
A中的 FName=null的意思是搜索姓名叫做null的一行数据，即使有一组数据的FName为null，那么它也代表着有一组数据的FName不知道，并不是这组数据的 FName叫做null；
B中我们使用is来搜索FName是null的数据。
31. 查找年龄为20,21,26,的数据有两种方法：
select * from Person0
where FAge=20 or FAge=21 or FAge=26;
或者
select *from Person0
where FAge in (20,21,26)
32. 查找年龄在20和30之间的数据：
select *from Person0
where FAge > 20 and FAge <30;
或者
select *from Person0
where FAge between 20 and 30;
33. 通过某一字段进行分组，并统计各个阶段的数据个数：
例如：下列是通过年龄进行分组：
select FAge,count(*) From Person0
Group by FAge;
注意：select FAge,FName,count(*) From Person0
Group by FAge;
这样是错误的，根据正常理解，上句话应解释为：“通过FAge进行分组并统计人数，然后展示FName，但由于在同一年龄阶段，每个人有各自的姓名，因此无法将姓 名展示出来。”
但是，select FAge,FName,count(*) From Person0
Group by FAge,FName;这样是正确的。
这句话解释为：“将同名同姓的分为一个组，并显示数据。”
咱们再看看这句话：
select FAge,max(Fsalary),count(*) from Person0;
group by FAge;
这句话是正确的。因为它取的是最大的工资进行显示的。注意：这里的“Fsalary”代表工资。
总结：没有出现在Group by 句中的列，是不能出现在select语句后的列名列表中。
34. where 和 having的区别：
where 是对原始的数据进行过滤的，而having是对分组后的信息进行过滤的，并且能用的列和select 語句中的列要一樣！
举个例子：
select FAge,count(*) from Person0
where Fsalary>2000
group by FAge;
这句话是将工资大于2000的年龄进行分组的。
但注意：
select FAge,count(*) from Person0
where count(*)>1
group by FAge;
这句话的本意是想：“通过年龄进行分组，分完之后，然后将数据大于1的组展示出来。”，可是前面说过，where 是对原始数据进行过滤的。因此要先使用count(*)>1，这个条件，它的前 提是要根据年龄进行分组之后进行操作，所以在这不能用where而应该用having
在这里要注意：where在聚合函数（group by ）之前使用，而having在之后使用。
所以：要是想使用having就应该这样写：
select FAge,count(*) From Person0
group by FAge
having count(*)>1;
同理：这样是不正确的：
select FAge,count(*) from Person0
group by FAge
having Falary>2000;
35. 显示有序列中的前三行：
例如： select top 3 *from Person0
order by Fsalary DESC;
复习：这里的order by 是将数据通过某个字段进行排序。
DESC是降序，ASC是升序
36. 显示有序列中除前5列之外的前三列：
select top 3 *from Person0
where FNumber not in (select top 5 FNumber from Person0 order by Fsalary DESC )
order by Fsalary DESC;
利用这种方式可以进行分页显示。
37. 修改表用alter table:
例如：alter table Person0 add FSubCompany varchar(20);
alter table Person0 add FPemterny varchar(20);
给表添加两个字段。
38. 去重复用“distinct”
例如：
select distinct FDepartment from Person0;
注意：distinct 不是针对于某一个字段的，而是面向整行。
拓展：
select distinct FDepartment ,FSubCompany from Person0
这句话是去除相同FDepartment 且相同FSubCompany 的数据的。
39. 将两张表的相同类型字段合成一个表展现用“union”：
例如：select FName,FAge from Person0
union
select FName,FAge from Person1
注意：对应类型相同，对应个数相同，字段名可以不一样。
还应注意一点就是：如果对应项重复，它会自动去掉，因此你要是不想让其去掉重复项，则应该在union后面加上all.
40. 数字函数：
ABS() - - - - - - - - - - - - -取绝对值 例如：select ABS(-1)
ceiling() --------------------舍入到最大整数 例如：2.89--3 -3.61---3 向上走。ceiling的意思是“天花板”
Floor()-----------------------舍入到最小整数 例如：3.33--3 -3.61---4 向下走。floor的意思是“地板”
round()------------------------四舍五入 例如：select round(数值，精度)--------select round(-2.123,2)---(-2.120)
41. 字符串函数：
LEN()--------------------计算字符串长度
lower()------------------转小写
upper()------------------转大写
ltrim()-------------------字符串左侧的空格去掉。
Rtrim()------------------字符串右侧的空格去掉
ltrim(Rtrim(' b '))----去掉两边的空格。
substring('dfjks',2,3)------从第二位开始，长度为3的字符。注意：这里的第二位是是“f”
42. 日期函数：
1. getdate()---------------取当前日期
2. dateadd(datepart,number,date)--------计算增加以后的日期
其中：date为待计算的日期；
number为增量；
datepart为计量单位
datepart可选值：
| 取值 | 别名 | 说明 |
| year | yy.yyyy | 年份 |
| quarter | qq.q | 季度 |
| month | mm.m | 月份 |
| dayofyear | dy.y | 当年度的第几天 |
| day | dd.d | 日 |
| week | wk.ww | 当年度的第几周 |
| weekday | dw.w | 星期几 |
| hour | hh | 小时 |
| minute | mi.n | 分 |
| second | ss.s | 秒 |
| millisecond | ms | 毫秒 |
例如：select dateadd(day,3,getdate())----------------------在当前的基础上加三天
select dateadd(day,-3,getdate())-------倒退三天。
3. datediff(datepart,startdate,enddate)-------------------计算两个日期的差额
datepart -------- 计量单位
startdate -------- 开始时间
enddate --------- 结束时间
差额=结束时间-开始时间
4. datepart(datepart,date) ----------返回一个日期特定的部分
例如：
select datepart(year,getdate());
综合运用：
统计不同工龄的员工个数：
首先定义也个入职时间FInDate字段
select datediff(year,FInDate,getdate()) as '工龄',count(*) as '个数' from Person0
group by datediff(year,FInDate,getdate());
根据入职时间进行分组：
select datepart(year,FInDate) AS '入职年份',count(*) from Person0
group by datepart(year,FInDate);
43. 类型转换函数：
cast(expression AS data_type)
convert(data_type ,expression)
expression ----------- 表达式
data_type ------------ 数据类型
例如:
select cast('123' as int ),cast('2008-08-08'as datetime)
或者是：
select convert(int,'123'),convert(datetime,'2009-09-09')
44. 空值处理函数：
ISNULL(expression, value) 如果expression不为空就返回expression,否则返回value.
例如：
ISNULL(FName,'佚名') as 姓名 from Person0;
45. case函数用法一：
单值判断，相当于swith case
CASE expression
when value1 then return value1
when value2 then return value2
when value3 then return value3
when value4 then return value4
else default return value
end
例如：
select FName, 注意：这里的“逗号”不能省略
(
case FLevel
when 1 then '普通用户'
when 2 then '会员用户'
when 3 then 'VIP用户'
else '位置客户类型'
end
)as '客户类型'
from T_Customer
再例如：
select FName,
(
case
when Fsalary<2000 then '低等收入'
when Fsalary>2000 and Fsalary<3000 then '中等收入'
else '高等收入'
end
)as 收入水平
from T_Employee;
46. 注释：
· 在SQL语句中用“--”作为注释。例如：--这是我的SQL语句
47. 索引的创建及要求：
方法：在表设计器中点击右键，选择（索引/键）-添加-在列中选择索引包含的列。
注意：使用索引能够提高查询的效率，但索引也是占据空间的，而添加、更新、删除数据的时候也需要同步更新索引，因此会降低Insert、 Update、Delete的速度。因此只在经常检索的字段上（Where）创建索引（Index）。
即使创建了索引，仍然有可能全表扫描，比如：like、函数、类型转换等。
48. 两个表中存在一定的关系，这时我们应该使用join进行连接
例如：
CREATE table [T_Customers](
[Id] [int] not null,
[Name] [nvarchar](50) collate Chinese_PRC_CI_AS null,
[Age] [int] null
);
insert into [T_Customers] ([Id],[Name],[Age]) values (1,N'tom',10)
insert into [T_Customers] ([Id],[Name],[Age]) values (2,N'Jerry',15)
insert into [T_Customers] ([Id],[Name],[Age]) values (3,N'john',22)
insert into [T_Customers] ([Id],[Name],[Age]) values (4,N'lily',18)
insert into [T_Customers] ([Id],[Name],[Age]) values (5,N'lucy',18)
create table [T_Orders](
[Id] [int] not null,
[BillNo] [nvarchar](50) collate Chinese_PRC_CI_AS NULL,
[CustomerId] [int] null
);
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (1,N'001',1)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (2,N'002',1)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (3,N'003',3)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (4,N'004',2)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (5,N'005',2)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (6,N'006',5)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (7,N'007',4)
insert into [T_Orders] ([Id] ,[BillNo] ,[CustomerId] ) values (8,N'008',5)
select o.BillNo,c.Name,c.Age
from T_Orders as o join T_Customers as c on o.CustomerId=c.Id
49. 子查询：
将一个查询语句作为一个结果集供其他SQL语句使用。就像使用普通的表一样，被当做结果集的语句称为子查询。
所以使用表的地方几乎都可以使用子查询来代替。
例如：select *from (select *from T2 where FAge<30)
50. 示例：
select *from Person0
where Age in(23,26)
上述语句的意思是选择年龄为23或者26的数据进行显示。注意这里的in.
51. 给显示数据添加行号：
使用：row_number() over(order by Fsalary DESC) as rowenum
例如：
select row_number() over(order by Fsalary DESC) as rowenum,FNumber,FName,FSalary,FAge from Person0;
这里注意：row_number()时开窗函数，只能出现在select 或order by语句中。在这里我想主要强调一下开窗函数不能在where中使用。
就说明要是我们想将行号大于3小于5的数据显示出来，则就没办法了。
所以我们就可以使用子查询进行搜索。那么如何进行搜索呢，请看下面的例子：
select(
select row_number() over(order by Fsalary DESC) as rownumber ,FName,FAge from Person0
) as e1
where e1.rownumber>=3 and e1.rownumber<5;
52. Chinese_PRC_CS_AI_WS
前半部份：指UNICODE字符集，Chinese_PRC_指针对大陆简体字UNICODE的排序规则。
排序规则的后半部份即后缀 含义：
_BIN 二进制排序
_CI(CS) 是否区分大小写，CI不区分，CS区分
_AI(AS) 是否区分重音，AI不区分，AS区分
_KI(KS) 是否区分假名类型,KI不区分，KS区分
_WI(WS) 是否区分宽度 WI不区分，WS区分
区分大小写:如果想让比较将大写字母和小写字母视为不等，请选择该选项。
区分重音:如果想让比较将重音和非重音字母视为不等，请选择该选项。如果选择该选项，
比较还将重音不同的字母视为不等。
区分假名:如果想让比较将片假名和平假名日语音节视为不等，请选择该选项。
区分宽度:如果想让比较将半角字符和全角字符视为不等，请选择该选项
