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.字段号
例子如下:
关联查询
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
结果如下:
select *,datalength(grade) as len_grade from class
一个字符两个字节:
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
-
getutcdate():返回当前国际标准时间值,返回类型为datatime
16 CONVERT函数
- 将日期转换为新数据类型的通用函数
-
可以用不同的格式显示日期时间数据
- 示例代码如下其中varchar(10)代表返回类型为varchar()且长度为10,110代表styleID,上图以给出:
select convert(varchar(10),getdate(),110)
-
结果如下:
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
-
结果如下:
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