SQLServer数据库 #

#1. 为什么需要数据库

1.1 数据的存储方式 #

  1. 大脑记忆
  2. 记录在纸上
  3. 存放在计算机的内存里
  4. 存放成硬盘上的文件

    1.2 数据库的优势 #

  5. 可以存储大量数据，查询非常方便
  6. 可以保证数据的一致性和完整性
  7. 方便共享和传播
  8. 通过数据分析可以获得不同维度的有用信息

2. 数据库的基本概念 #

2.1 实体 #

只要是在客观世界存在的、可以被描述出来的都是实体

2.2 数据库(DB) #

3. 数据库的安装 #

3.1 安装要求 #

3.2 安装过程 #

3.3 服务的启动方式 #

  1. 通过操作系统的服务启动
  2. SQL Server Management Studio中启动

    3.4 连接服务器 #

    Alt text

    3.5 数据库 #

    3.5.1 系统数据库 #

  3. master 记录数据库系统信息
  4. model 系统创建所有数据库的模板
  5. msdb Sqlserver代理用于执行报警、作业调度等自动化任务
  6. tempdb 用于保存临时对象和中间结果集的工作空间，每次启动都会重新生成

    3.5.2 用户数据库 #

    就是用户自行建立的数据库

    3.6 数据库权限 #

    3.6.1 登录名 #

    用以登录SQL Server数据库服务器，仅仅能登录进去，但不具备任何权限

    3.6.2 数据库用户 #

    登录成功后可以访问具体的用户数据库

    3.6.3 建立登录名 #

  7. 新建可以登录服务器的登录名，与此同时还可以设置此登录名对应的服务器角色，映射为数据库的用户并设置数据库权限
  8. 如果创建用户名的时候没有创建用户，则可以在数据库中单独创建用户并关联登录名。同一个数据库中只能有一个用户关联同一个登录名

    sa 账户属于SQL Server自带的超级管理员用户，新建用户名后应该把sa禁用或删除掉以保证服务器的安全 Alt text

4. 数据库物理操作 #

4.1 数据库物理文件组成 #

一个数据库至少包含一个数据库文件和一个事务日志文件

  1. 数据文件 .mdf或.ndf
  2. 日志文件.ldf

4.2 创建数据库 #

###需求说明：

4.3 收缩数据库 #

如果因为删除数据而导致数据库空间空闲太多的情况，这可以就需要减少分配给数据库文件和事务日志文件的磁盘空间以免浪费磁盘空间。 Alt text

4.4. 分离和附加数据库 #

如果要移动数据库到其它服务器上的时候可以使用分离和附加数据库

4.4.1 分享数据库 #

Alt text

4.4.2 附加数据库 #

Alt text

4.5. 备份数据库 #

为防止数据丢失或损坏我们可以定期备份数据库，以便在需要的时候还原

4.5.1 备份 #

Alt text

4.5.2 还原 #

Alt text

5. 数据表 #

5.1 数据完整性 #

为了实现数据完整性，需要检验数据库表中的每行和每列数据是否符合要求 在创建表的时候，应该保证以后的数据输入是正确的，错误的数据不允许输入

5.1.1 域完整性 #

5.1.1.1 数据类型 #

不同的字段需要设置为各种合适的类型，比如年龄就是整数类型 Alt text

请大家思考 电话号码、性别、工资分别应该用什么数据类型存储?

5.1.1.2 检查约束 #

通过指定一些表达式从而让列的值符合某个定义

规定学生年龄必须大于0岁并且少于100岁

5.1.1.3 默认值 #

默认值是指如果用户没有指定值的情况下会记录的此字段指定一个提供一个预先设定的值

可以把居住地默认值设置为北京

5.1.1.4 非空约束 #

我们可以指定某个字段不能不输入，必须提供一个非空的值

姓名字段不能为空

5.1.2 实体完整性 #

5.1.2.1 主键约束 #

5.1.2.1.1 主键 #

表中一列或者几列组合的值能用来唯一标识表中的每一行，这样的列或者列组合称为表的主键，主键表的数据不同重复。

如果两列或者多列组合起来唯一标识表中的每一行，则该主键又称为"组合键"

主键的选择标准

  1. 最少性 尽量选择单个键作为主键
  2. 稳定性 ，由于主键是用来在两个表间建立联接的，所以不能经常更新，最好就不更新
    5.1.2.1.2 外键 #
    成绩表中的学生ID应该在学生表中是存在的 我们应该让成绩表中的ID只能引用学生表中的ID，它们的值应该是一一对应的，也就是说成绩表中的ID是成绩表中的外键，对应学生表的主键 ，这样就可以保证数据的引用完整性

    5.1.3 唯一约束 #

    唯一约束是指某个字段值是唯一的，在所有的记录中不能有重复的值.

    学生的身份证号可以设置为唯一约束

5.1.4 标识列 #

5.1.3 引用完整性 #

5.1.3.1 外键约束 #

一个表的外键必须引用另一个表的主键,比如成绩表中的学生ID会引用学生表的主键，课程ID会引用成绩表的主键

5.2.3 添加约束 # 

主键约束
alter table student add constraint pk_idcard primary key(id)
唯一约束
alter table student add constraint uq_idcard unique(idcard)
默认约束
alter table student add constraint df_address default('地址不详') for address
检查约束
alter table student add constraint ck_score check(score between 0 and 100)
外键约束
alter table score add constraint fk_sid foreign key(sid) references student(id)
删除约束
alter table student drop constraint df_address

6. SQL #

6.1 SQL简介 #

6.1.1 SQL是什么 #

Structured Query Language：结构化查询语言

6.1.2 为什么要用SQL #

  1. 使用SQL Server Management Studio操作数据库不方便
  2. 我们需要通过应用程序去操作数据库

    6.1.2 什么是T-SQL #

    Transact-SQL 是SQL 的增强版，对SQL功能进行了扩充，增加了如变量说明、流程控制和功能函数等

6.2 SQL组成 #

6.2.1 DDL（data definition language）是数据定义语言 #

主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

6.2.2 DML（data manipulation language）是数据操纵语言 #

它们是SELECT、UPDATE、INSERT、DELETE`，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

6.2.3 DCL（DataControlLanguage）是数据库控制语言 #

是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句

6.3 SQL运算符 #

是一种符号，它是用来进行列间或者变量之间的比较和数学运算的

6.3.1 算术运算符 #

运算符 说明
+ 加运算，求两个数或表达式相加的和，如1+1
- 减少减运算，求两个数或表达式相减的差，如4-1
* 乘运算，求两个数或表达式相乘的积，如2*2
/ 除运算，求两个数或表达式相除的商，如6/4的值为1
% 取模运算，求两个数或表达式相除的余数，如：6%4的值为2

6.3.2 赋值运算符 #

运算符 说明
= 把一个数或变量或表达式赋值给另一变量，如：name='zhufengpeixun'

6.3.3 逻辑运算符 #

运算符 说明
AND 当且仅当两个布尔表达式都为true时，返回TRUE
OR 当且仅当两个布尔表达式都为false，返回FALSE
NOT 布尔表达式的值取反

6.3.4 比较运算符 #

运算符 说明
= 等于
> 大于
< 小于
<> 不等于
>= 大于等于
<= 小于等于
!= 不等于

6.4 查询窗口 #

Alt text

6.5 数据操作语言 #

6.5.1 插入数据行 #

6.5.1.1 语法 # 

 INSERT  [INTO]  表名  [(列名)] VALUES  (值列表)

6.5.1.2 示例 # 

INSERT INTO [school].[dbo].[student]
(name,idcard,age,city)
VALUES
('欧阳锋','410787',30,'白驼山')
GO

6.5.1.3 注意 #

6.5.2 插入多行数据 #

6.5.2.1 SELECT INTO #

通过SELECT INTO语句将现有表中的数据添加到新表中

6.5.2.1.1 语法 # 

SELECT (列名) INTO <表名> FROM <源表名>

6.5.2.1.1 示例 # 

SELECT id,name,idcard,age,city
INTO student_bak
FROM student

注意: 此语句只能执行一次

6.5.2.2 INSERT SELECT #

将现有表中的数据添加到已存在的表中

6.5.2.2.1 语法 # 

 INSERT INTO <表名>(列名) SELECT <列名> FROM <源表名>

6.5.2.2.1 示例 # 

INSERT INTO student_bak
SELECT name,idcard,age,city
FROM student

注意: student_bak 表必须预先建立好，并且有对应的列名

6.5.3 更新数据 #

更新表中的数据

6.5.3.1 语法 # 

UPDATE 表名 SET 列名 = 更新值 [WHERE 更新条件]

6.5.3.2 示例 # 

UPDATE [school].[dbo].[student]
   SET age = 40,city = '上海'
WHERE id=7

注意: 更多多列时用逗号隔开，一定要加更新条件以免错误更新

6.5.3.3 练习 #

  1. 多个联合条件使用 AND id=7 and idcard='410787'
  2. 判断某字段是否为空 email is null or email = ''

6.5.4 删除数据 #

删除表中的数据

6.5.4.1 语法 # 

DELETE [FROM]  表名 [WHERE <删除条件>]

6.5.4.2 示例 # 

DELETE FROM [school].[dbo].[student]
      WHERE IDENT_CURRENT=7

Delete语句是对整行进行操作，因此不需要提供列名 如果要删除主表数据，则要先删除子表记录

6.5.5 TRUNCATE 截断表 #

截断整 个表中的数据

6.5.3.1 语法 # 

TRUNCATE  TABLE  表名

6.5.3.2 示例 # 

TRUNCATE TABLE student

数据全部清空,但表结构、列、约束等不被改动 不能用于有外键约束引用的表 标识列重新开始编号 因为要删除的数据不会写入日志，数据也不能恢复，所以工作中请尽量不要使用此命令

6.5.6 导出数据 #

Alt text

6.5.7 导入数据 #

6.6 数据查询 #

6.6.1 查询 #

6.6.1.1 语法 # 

SELECT    <列名> 
FROM      <表名> 
[WHERE    <查询条件表达式>] 
[ORDER BY <排序的列名>[ASC或DESC]]

6.6.1.2 示例 # 

SELECT id,name,idcard,age,city
FROM student
WHERE home= ‘北京’ 
ORDER BY id asc

Alt text

6.6.1.3 注意 # 

SELECT top 20 PERCENT id,name,age,city,'中国' as country
FROM student

按照一定的百分比返回查询行数可以得到表中大致有多少数据行

6.6.1.4 练习 #

Alt text

  1. 查询山东省的同学全部信息
  2. 查询山东省的男同学的全部信息
  3. 查询没有填写电子邮箱的同学的姓名和身份证号
  4. 查询生日晚于1998年1月1号并且年龄小于20岁的女同学的姓名和生日信息

6.6.1.5 单列排序 #

Alt text Alt text Alt text

ORDER BY

  1. 把学生的分数按升序排列
    select * from school.dbo.score order by grade asc;
  2. 把学生的分数按降序排列
    select * from school.dbo.score order by grade desc;
  3. 课程编号按升序，成绩按降序
    select * from school.dbo.score order by course_id asc, grade desc;

    6.6.1.6 SQL Server函数 #

    6.6.1.6.1 字符串函数 #
    Alt text
    6.6.1.6.2 日期函数 #
    Alt text Alt text
6.6.1.6.3 数学函数 #

Alt text

6.6.1.6.4 系统函数 #

Alt text

6.6.1.6.5 练习 #
  1. 把学生表中的邮箱地址全部由小写改为大写
    update student set email = UPPER(email)
  2. 字段level中的数据是 1-1、1-2、2-3之类的，现在要求先按前半部分升序排列，再按后半部分降序排列
    select level from stu
order by 
convert(int,left(level,CHARINDEX('-',level)-1)),
convert(int,right(level,LEN(level)-CHARINDEX('-',level))) desc
  3. 查询年龄超过18岁的男学生
    select * from school.dbo.student
where DATEDIFF(YY,birthday,GETDATE())>=18
  4. 查询出生月份是9月的山东省同学
    select *from school.dbo.student
where DATEPART(M,birthday)=9 and province = '山东省'
  5. 查询生日是今天的学生姓名
    select * from school.dbo.student
where DATEPART(D,birthday) = DATEPART(D,GETDATE())
  6. 查询ID为3的同学邮箱域名
    select RIGHT(email, LEN(email) - CHARINDEX('@',email) )
from school.dbo.student where id=3

6.6.1.7 模糊查询 #

就是查询的条件是模糊的，不是特别明确的

6.6.1.7.1 通配符 #

代替一个或多个真正的字符，与LIKE 关键字一起使用 Alt text

6.6.1.7.2 BETWEEN #

查询某一列在指定的规范内的记录，包括两个边界

select * from score where grade between 80 and 100
6.6.1.7.3 IN #

查询某一列中的值在列出的内容列表中

select * from student where city in ('北京','上海','广东')

6.6.1.8 什么是聚合函数 #

对一组值进行计算，并返回计算后的值，一般用来统计数据

6.6.1.8.1 SUM #

累加所有行的值

计算ID=1的学生的的总分
select SUM(grade)  as '总分' from score where student_id = 1;
6.6.1.8.2 AVG #

计算所有行的平均值

计算ID=1的学生的的平均分
select AVG(grade) as '平均分' from score where student_id = 1;
6.6.1.8.3 MAX、MIN #

计算所有行的平均值

计算ID=1的学生的的平均分
select AVG(grade) as '平均分',MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;
6.6.1.8.4 AVG #

计算所有行的平均值

select AVG(grade) as '平均分',MAX(grade) 最高分,MIN(grade) 最低分 from score where student_id = 1;
6.6.1.8.5 COUNT #

计算学生总数

select COUNT(*) from student;

6.6.1.9 分组 #

分组查询就是按某列的值进行分组，相同的值分成一组，然后可以对此组内进行求平均、求和等计算 Alt text

6.6.1.9.1 语法 # 
SELECT  FROM  <表名>  
WHERE  <条件>
GROUP BY <分组字段>

SELECT列表中只能包含：

6.6.1.9.2 练习 #
6.6.1.10.2 练习 #

6.6.1.11 多表连接 #

多表联接查询实际上是通过各个表之间共同列的关联性来查询数据的，它是关系数据库查询最主要的特征

6.6.1.11.1 内联接 #

内联接就是通过比较运算符匹配两个表中的列值

语法

SELECT
FROM     表1
INNER JOIN     表2
ON     表1.字段1 = 表2.字段2

INNER JOIN 关键字后面是关联表，ON关键字后是表间联接字段的关系

select student.name,course.name,grade from student inner join score on student.id = score.student_id inner join course on score.course_id = course.id; ```

6.6.1.11.2 左内联接 #

左外联接是以左表为基础的，左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录。右表记录不足的地方均为NULL

6.6.1.11.2 右内联接 #

右外连接的原理与左外连接相同,右表逐条去匹配记录；否则NULL填充