MS SQL Server学习笔记

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字节(-263,263-1)
  • int:4字节(-231,231-1)
  • smallint:2字节(-215,215-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 查询表记录

查前多少行数据。

  • Top
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
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
  • 序言:七十年代末,一起剥皮案震惊了整个滨河市,随后出现的几起案子,更是在滨河造成了极大的恐慌,老刑警刘岩,带你破解...
    沈念sama阅读 205,033评论 6 478
  • 序言:滨河连续发生了三起死亡事件,死亡现场离奇诡异,居然都是意外死亡,警方通过查阅死者的电脑和手机,发现死者居然都...
    沈念sama阅读 87,725评论 2 381
  • 文/潘晓璐 我一进店门,熙熙楼的掌柜王于贵愁眉苦脸地迎上来,“玉大人,你说我怎么就摊上这事。” “怎么了?”我有些...
    开封第一讲书人阅读 151,473评论 0 338
  • 文/不坏的土叔 我叫张陵,是天一观的道长。 经常有香客问我,道长,这世上最难降的妖魔是什么? 我笑而不...
    开封第一讲书人阅读 54,846评论 1 277
  • 正文 为了忘掉前任,我火速办了婚礼,结果婚礼上,老公的妹妹穿的比我还像新娘。我一直安慰自己,他们只是感情好,可当我...
    茶点故事阅读 63,848评论 5 368
  • 文/花漫 我一把揭开白布。 她就那样静静地躺着,像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上,一...
    开封第一讲书人阅读 48,691评论 1 282
  • 那天,我揣着相机与录音,去河边找鬼。 笑死,一个胖子当着我的面吹牛,可吹牛的内容都是我干的。 我是一名探鬼主播,决...
    沈念sama阅读 38,053评论 3 399
  • 文/苍兰香墨 我猛地睁开眼,长吁一口气:“原来是场噩梦啊……” “哼!你这毒妇竟也来了?” 一声冷哼从身侧响起,我...
    开封第一讲书人阅读 36,700评论 0 258
  • 序言:老挝万荣一对情侣失踪,失踪者是张志新(化名)和其女友刘颖,没想到半个月后,有当地人在树林里发现了一具尸体,经...
    沈念sama阅读 42,856评论 1 300
  • 正文 独居荒郊野岭守林人离奇死亡,尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,676评论 2 323
  • 正文 我和宋清朗相恋三年,在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,787评论 1 333
  • 序言:一个原本活蹦乱跳的男人离奇死亡,死状恐怖,灵堂内的尸体忽然破棺而出,到底是诈尸还是另有隐情,我是刑警宁泽,带...
    沈念sama阅读 33,430评论 4 321
  • 正文 年R本政府宣布,位于F岛的核电站,受9级特大地震影响,放射性物质发生泄漏。R本人自食恶果不足惜,却给世界环境...
    茶点故事阅读 39,034评论 3 307
  • 文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹,春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,990评论 0 19
  • 文/苍兰香墨 我抬头看了看天上的太阳。三九已至,却和暖如春,着一层夹袄步出监牢的瞬间,已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,218评论 1 260
  • 我被黑心中介骗来泰国打工, 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留,地道东北人。 一个月前我还...
    沈念sama阅读 45,174评论 2 352
  • 正文 我出身青楼,却偏偏与公主长得像,于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子,可洞房花烛夜当晚...
    茶点故事阅读 42,526评论 2 343

推荐阅读更多精彩内容