MS SQL Server学习笔记

2019-04-26 本文已影响0人 jacob_

1 引言

SQL Server是由Microsoft开发的一个关系型数据库系统（Relational Database Management System），现在是世界上最为常用的数据库。
SQL Server 是作为一个服务器的数据库系统使用的，并非桌面系统，比如 MS Access。这说明他的访问量会很大。

2 数据库的创建

2.1 利用ssms（SQL Server Managerment Studio）创建

ssms是一个图形界面，在对象资源管理器中数据库栏右键新建数据库即可。一般接收默认值创建。

2.2 利用Transact-SQL脚本进行创建

点击新建查询输入下列代码

Create Database taskDb
On Primary(
name = 'taskdb',
filename = 'G:\testdb\testdb.mdf',
size = 10MB,
maxsize = 100MB,
filegrowth = 5MB
)
Log On(
name = 'testdb_log',
filename = 'G:\testdb\testdb.ldf',
size = 10MB,
maxsize = 100MB,
filegrowth = 5MB
)

该段代码的意思是：创建一个名叫taskDb的数据库，存储文件放在'G:\testdb\testdb.mdf',初试大小10M，最大100MB，每次文件增长5MB。日志文件同理，放在'G:\testdb\testdb.ldf'。

3 数据库的修改

数据库查看

exec sp_helpdb taskDb;

数据库查看

更新数据库名称

alter database taskDb
modify name = taskDb01;

更新数据库文件大小

alter database taskDb01
modify file(
name = taskDb,
size = 20MB,
maxsize = 50MB,
filegrowth = 10MB
);

这里要注意，taskDb01是指数据库名称，而taskDb是数据库文件名称，不要搞错。

删除数据库

drop database taskDb01

数据库的备份和还原
右键数据库，点任务-选择备份，更换保存目录即可。备份文件后缀为bak
同理，数据库还原时只需要点还原，选择设备还原之前的备份文件即可。
数据库的分离
和数据库的删除类似，但是数据库的分离意义是将其从当前环境分离开来，其主数据文件（mdf）并没有被删除。与其对应，就有数据库的附加。
数据库的附加
通过mdf主数据库文件将数据库附加进来。

4 数据类型

4.1 数字类型

bigInt:8字节（-2⁶³,2⁶³-1）
int:4字节（-2³¹,2³¹-1）
smallint:2字节（-2¹⁵,2¹⁵-1）
tinyint:1字节（0-255）

4.2 时间类型

time:例如（时：分：秒.毫秒）
data:例如（2019-04-27）
smalldatatime:例如（2019-04-27 14：57:00）
datatime:例如（2019-04-27 14：57:00.123456）精确到毫秒

4.3 字符串类型

char [n]:固定长度n(1-8000内的确定的一个)
varchar[n/max]：可变长度n（1-8000内的值都可以）

5 表的创建

过程：创建表，设置主键
同样可以使用GUI操作创建于SQL脚本创建。

create table product(
productId int primary key not null,
productName varchar (25) not null,
price money null
)

6表的修改与删除

添加not null 约束

alter table 表名
alter column 字段名 Int not null

设置主键(字段名是要被添加为主键的那一列)

alter table 表名
add constraint 主键名 primary key(字段名)

更改字段名

exec sp_rename '表名.字段名',
'更改后的字段名','COLUMN'

添加字段名(default表示默认值，当允许为null且没有输入相应数据时会采用该默认值)

alter table 表名
add 字段名 字段类型 default null

7 表的主键与外键

外键表示两个关系之间的相关联系， 以一个关系的外键作为主关键字的表主表，另一个称为从表。
利用GUI添加时，在从表上，右键->关系，在常规里面设置表和列规范。

//对从表table1添加外键uid（列），FK_product 为外键名称,主表是product，对应主表的主键是productId。
alter table table1
add constraint FK_product foreign key(uid)
references product(productId)

8 新增表记录

从其他表copy数据

insert into 表格1（列1,列2）
select 列1,列2
from 表格2;

正常插入

insert into 表格 (列1,列2)
values ('data','time')

9 查询表记录

查前多少行数据。

select Top 行数 列名
from 表名

模糊查询的方式

模糊查询

select * from role
where name like '%管理员'

需要注意的是，'%'的作用是匹配，这样利用'%管理员'可以查找到商品管理员、超级管理员、系统管理员等。

select * from role
where uid between 0 and 5

查询uid在0-5之间的数据，不一定要int类型，char也可以，例如'2019-04-29'and'2019-05-01'.例如下面这个

select * from role
where time between '2019-04-01' and getdata();

select * from role
where uid not between 0 and 5

查询uid不在0-5之间的数据

子查询IN表达式

select * from user1
where userid in (1,3)

查询在集合（1,3）的数据

select * from user1
where userid  not in (1,3)

查询不在集合（1,3）的数据

select * from user1
where username in (select username from user2)

查询username出现在user2表的数据

子查询EXIST表达式

select a.userid from user1 as a
where exists (select * from userinfo as b where (a.userid = b.uid) and b.uid = 1)

这句话的意思是①把user1取别名a②条件限制语句，选择满足a.userid = b.uid并且b.uid=1的字段③需要注意的是:exists返回的是一个Bool值（其检查子查询是否至少会返回一行数据），若返回true则选择这行数据。

结果排序
需要注意的是

select * from user1
order by userid,name desc

其意义在于，先按userid升序排序，若有userid相同的，再按照name降序排序。

10 关联查询

交叉关联（inner join）:只返回两个表中连接字段相等的行

select * from table1
inner join table2
on table1.字段号 = table2.字段号

左关联（left join）:返回包括左表中的所有记录和右表中连接字段相等的记录

select * from table1
left join table2
on table1.字段号 = table2.字段号

右关联（right join）：返回包括右表中的所有记录和左表中连接字段相等的记录

select * from table1
right join table2
on table1.字段号 = table2.字段号

例子如下：

class表

student表
关联查询

select * from student
inner join class
on student.ClassNo = class.ClassNo

关联查询结果

select * from student
left join class
on student.ClassNo = class.ClassNo

左关联查询结果

11 聚合函数（AVG/SUM）

AVG：顾名思义，求平均值。返回组中各值的平均值，忽略null。

select avg(字段名)
from 表名

SUM:返回组中各值的和，忽略null。

select sum(字段名)
from 表名

例如：

select sum(score) as sum_score from students

表

结果

12 聚合函数（MIN/MAX）

和前面的avg和sum没什么区别（但要记住忽略Null），就直接给出示例代码：

select min/max(字段名)
from 表名

13 LEN()函数

返回指定字符串表达式的字符数
其中不包含尾随空格
若要返回用于表示表达式的字节数，使用DATALENGTH()函数

select *,len(grade) as len_grade from class

结果如下：

len函数

select *,datalength(grade) as len_grade from class

一个字符两个字节：

datalength()

14 随机数的产生

执行select rand()，可以得到一个随机小数（小于1），类似0.5555
执行select floor(rand() * 10),首先rand() * 10得到一个小于10的随机数，floor()函数返回小于等于该数的整数。
执行select ceiling(rand() * 10),首先rand() * 10得到一个小于10的随机数，floor()函数返回大于等于该数的整数。

15 getdate()与getutcdate()

getdate():返回当前数据库系统时间值，返回类型为datatime

getdate()
getutcdate():返回当前国际标准时间值，返回类型为datatime

getutcdate()

16 CONVERT函数

将日期转换为新数据类型的通用函数
可以用不同的格式显示日期时间数据

格式
格式
示例代码如下其中varchar(10)代表返回类型为varchar()且长度为10,110代表styleID，上图以给出：

select convert(varchar(10),getdate(),110)

结果如下：

convert()

17 DATEDIFF与DATEADD函数

17.1DATEDIFF

返回两个日期之间的天数
datediff(datepart,startdate,enddate),datepart：若为day，即求两个日期间的天数差异；若为month则为月份数差异。

17.2DATEADD

在日期中添加或减去制定的时间间隔
DATEADD(datepart,number,date)
示例结果如下：

select datediff(day,'2019-04-01','2019-05-01')
select datediff(month,'2019-04-01','2019-05-01')
select datediff(minute,'2019-04-01','2019-05-01')
select datediff(second,'2019-04-01','2019-05-01')
select dateadd(day,30,'2019-04-01')
select dateadd(month,5,'2019-04-01')

代码对应结果
主要注意的是，小日期在前，大日期在后

与convert函数结合

select dateadd(day,5,'2019-05-01')
select convert(varchar(10),dateadd(day,5,'2019-05-01'),120)

结果对比

18 DATEPART函数

返回日期时间的单独部分，比如年、月、日、小时、分钟等。
返回类型为int
datename同理，但返回varchar类型
day():获取当前日期对应的那个值，例如select day(getdate())
month():获取当前日期对应的那个值，例如select month(getdate())
year():获取当前日期对应的那个值，例如select year(getdate())

select month(getdate())
select DATEPART(month,getdate())
select datename(month,getdate())

这里可以看出datepart与datename的结果区别

代码结果

19 CHARINDEX函数

返回字符或者字符串在另一个字符串中的起始位置
CHARINDEX(expression 1,expression 2[,start_index])，从expression 2的start_index开始寻找expression 1，返回其位置，若没有，则返回0。
PATINDEX（'%BC%','ABCD'）返回结果也是2，但效率低于charindex

select charindex('cc','aabbccdd')
select charindex('cc','aabbccdd',6)
select patindex('cc','aabbccdd')
select patindex('%cc%','aabbccdd')
--以a开头--
select patindex('aa%','aabbccdd')
--以d结尾
select patindex('%dd','aabbccdd')
select *,charindex('年',Grade) as index_grade from te.dbo.class
where id = 1

结果

20 Stuff函数

用于删除指定长度的字符串，并可以在制定的起点处插入另一组字符。
返回类型是一个字符串
stuff(列名,开始位置,长度,替代字符串)

select stuff('aabbccdd',5,2,'')
select stuff('aabbccdd',5,2,'eegg')
select *,stuff(grade,2,1,'nian') from te.dbo.class 
where id = 3

结果如下：

代码结果

21 SUBSTRING函数

用于截取指定长度的字符串
Substring(expression,start,length)

select substring('abcdefg',3,5)
select *,substring(grade,2,2) as substring_grade from te.dbo.class
where id = 3

结果如下：

代码结果

22 LEFT()/RIGHT()

left()：返回从字符串左边开始指定个数的字符
right()：返回从字符串右边开始指定个数的字符

select *,left(grade,2) from te.dbo.class
where id = 3

结果如下：

代码如下

23 LTRIM()/RTRIM()

ltrim():删除起始空格
rtrim():删除尾空格

24 UPPER()/LOWER()

25 REPLACE()

replace(string,pattern,replace)：将string中的pattern转化为replace

26 REPLICATE（）

replicate(string,times):让string重复times次

27 SPACE()

space(Num):产生num个空格

27 REVERSE()

reverse(String):反转String

27 CAST()

cast(expression as data_type):将expression转化为data_type类型

select 'abc' + cast(1 as varchar(5))

28 CASE()

直接上代码：

--简单CASE函数
select *,case Sex 
when '男' then 'man'
else 'woman'
end from te.dbo.student
--搜索CASE函数
select *,case 
when studentno>1506 then 'no1'
when studentno>1505 and studentno<1507 then 'no2'
else 'no3' end
from te.dbo.student

结果如下：

简单CASE
搜索CASE

29 Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+

利用group by + having count(),表示先按email分组，同样的分为一组；再通过having count()>1，表示每组行数大于1时才输出.就是说从表中选出同样的email重复出现大于一次的数据

select Email
from Person
group by Email
having count(*) > 1

29 Employees Earning More Than Their Managers

The Employee table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+
Given the Employee table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.

+----------+
| Employee |
+----------+
| Joe      |
+----------+

解决方法如下：

select a.name as Employee
from employee as a,employee as b
where a.managerid = b.id
and a.salary > b.salary