百木园-与人分享，
就是让自己快乐。
当前位置：百木园 > 野生技术 > 正文

MySQL 笔记

2022-05-11 分类：野生技术 阅读(243) 评论(0)

情景一
数据库概述
基本术语
DB : 数据库 ( Database )
是按照数据结构来组织、存储和管理数据的仓库，其本身看作电子化的文件柜，用户可以对文件中的数据进行增加、删除、修改、查找等操作

DBMS : 数据库管理系统 ( Database Management System )
是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库

DBS : 数据库系统 ( Database System )
由数据库 ( DB ) 、数据库管理系统 ( DBMS ) 、数据库应用程序和用户等组成

DBA : 数据库管理员 ( Database Administrator )
负责数据库的总体信息控制

数据库系统结构图：

数据库存储结构：
数据库是存储和管理数据的仓库，但数据并不能直接存储数据，数据是存储在表中的
在存储数据的过程中一定会用到数据库服务器，所谓的数据库服务器就是指在据算计上安装一个数据库管理程序

什么是 SQL语言
SQL语言就是一种数据库查询语言和程序设计语言
它是一种关系型数据库语言，主要用于管理数据库中的数据，如
存取数据、查询数据、更新数据等

关系型数据库与语言由 4部分 组成：
数据定义语言 ( DDL ) create drop alter
数据操作语言 ( DML ) insert update delete
数据查询语言 ( DQL ) select
数据控制语言 ( DCL ) grant revoke

SQL简介

常用的商用数据库
Oracle
MySQL
MS SQL Server
MongoDB
DB2

数据模型：
1.层次模型 - 树形结构
2.网状模型 - 网络结构
3.关系模型 - 二维表结构
4.面向对象模型 - 对象为单位，对象由属性和方法组成，具有类和继承等特点

MYSQL 关系型数据库
为什么选择 MySQL
开源
跨平台
价格优势
功能强大使用方便

Internet 流行网站架构方式 LAMP ( Linux + Apache + MySQL + PHP )
Linux作为操作系统，Apache作为Web服务器，MySQL作为数据库，PHP作为服务器端脚本解释器

版本
社区版
商业版

基于 Windows 有两种安装：
.msi 的二进制发行版，提供了图形化的安装向导
.zip 的压缩文件

关于版本号：
MySQL-5 ( 主版本号 ) .6 ( 次版本号 ) .16( 修订版本号 )

安装 MYSQL ； 熟悉运行环境
默认端口号 ： 3306

情景二
MySQL常用命令
MySQL 目录结构
MySQL 安装完成后，会在磁盘上生成一个目录，该目录被成为 MySQL 安装目录
MySQL 安装目录中包含了启动文件、配置文件、数据库文件和命令文件
Data目录：用于放置一些日志信息以及数据库
Bin 目录：用于放置一些可执行文件，如mysql.exe
My.ini 文件：mysql数据库中使用的配置文件

MySQL启动和登录
启动和停止 MySQL服务
1.通过 Windows服务管理器启动mysql服务
自动：会随系统一起启动
手动：服务不会随系统一起启动，直到需要时才会被激活
已禁用：服务将不再启动，即使是在需要它时，也不会被启动，除非修改为上面的类型

2。通过DOS命令启动mysql服务
net start mysql
net stop mysql

登录和退出MySQL

  1. 通过DOS命令登录MySQL服务
    命令名 : mysql
    机器名或机器的IP -h
    账户 -u
    密码 -p
    完整命令行： mysql -h localhost -u root -p

2.使用 MySQL Command Line Client 登录 mysql服务

掌握MySQL的常用命令
从服务器获取 MySQL 的状态信息
status 简写为 \\s

选择数据库
use 简写为 \\u 数据库名作参数

执行一个 SQL脚本文件
Source 简写 . 以一个文件名做参数
首先，创建一个sql文件
打开记事本，编写创建数据库的代码：
create database mysql_db;

保存为 .sql文件

使用 show databases;
查看数据库列表
执行sql文件，再次查看有那些数据库

退出MySQL
Quit 或 exit 简写为 \\q

重新配置MySQL
1.通过命令重新配置MySQL
首先登录到 MySQL数据库，在该窗口中使用如下命令：
set character set client = gbk ( 仅针对当前窗口有效，只能暂时改变编码 )

2.通过my.ini文件重新配置MySQL
通过记事本，打开 my.ini文件，进行修改 ( 长期有效 )

情景三
创建数据库
创建数据库是开发应用的起点

使用SQL简单创建“图书”管理数据库
CREATE DATABASE book;

如何创建数据库
创建数据库book
CREATE DATABASE book;

执行结果
mysql>CREATE DATABASE book;
Query OK, 1 row affected

查看数据库
SHOW DATABASES;

查看数据库
使用SQL查看所有数据库
SHOW DATABASES;

使用SQL查看创建好的数据库
SHOW CREATE DATABASE <数据库>;

查看当前数据库
SELECT DATABASE();

修改、指定数据库字符集
查看数据库信息
SHOW CREATE DATABASE book;

修改数据库字符集
ALTER DATABASE book DEFAULT CHARACTER SET gbk;

字符集
Litan1 （安装时默认）
Utf8
GBK

创建一个使用字符集GBK的book1数据库
CREATE DATABASE book1 CHARACTER SET <字符集>;

创建一个使用字符GBK，并带校对规则的book2数据库
CREATE DATABSASE book2 CHARACTER SET <字符集>;
COLLATE <字符集>_bin;

删除数据库
如何删除数据库
删除数据库是将数据库系统中已经存在的数据库删除
删除数据库的基本语法：
DROP DATABASE <数据库>;

删除数据库
删除数据库book1
DROP DATABASE book1;

执行
mysql>DROP DATABASE book1;
Query OK, 0 row affected

查看
SHOW DATABASES;

情景四
基本表
数据库中的数据存储在表中，表是数据库中存储数据的基本单位

MySQL中表的形式像日常使用的Excel，是按行和列的格式排布的
表头——字段名（属性）
行——记录
元组

				列——字段

域——该列的取值范围

记录：表中的一行。不能重复，没有顺序
字段：表中的一列。体现数据的不同性质

域：字段的取值范围，域的特征依赖于字段的特性，包括数据类型、数据长度和其他约束

表实际上是由结构和记录组成的

表的结构就是表中应包含哪些字段以及字段的特性，创建表就是创建表的结构
p.s. 创建表之前一定要设计好表的结构！

数值型数据
数值型数据类型都用于存储不同类型的数字值，有不同的长度或精度

整型数据
整数类型可分为5种：
TINYINT ( 1字节 ) -128~127
SMALLINT ( 2字节 )
MEDIUMINT
INT ( 4字节 ) 10位整数
BIGINT ( 8字节 )
不同整数类型的取值范围可以根据字节数计算出来

浮点数类型和定点数类型
小数由浮点数和定点数表示
浮点数类型有两种：
单精度浮点数类型 FLOAT
双精度浮点类型 DOUBLE

定点数类型只有 DECIMAL类型
其中，DECIMAL(M, D) 中M是长度，定义了总位数，包括小数点右边的位数；
D是小数点右边的位数。

字符型、时间日期型数据类型
常用
char(n) 长度n 固定（n个字节）
varchar(n) 长度 n 位最大值，可根据数据的实际长度变化
text 长度较大，一般用于描述表中\"说明\"、“备注”之类的信息

日期与时间类型
MySQL提供了表示日期和时间的数据类型
YEAR 年
DATA 年/月/日
TIME
DATETIME
TIMESTAMP 时间戳，记录系统当前时间

其他类型
对于图片、视频、音频等，MySQL提供了表示二进制大数据的数据类型
BLOB

情景五
创建基本表
CREATE TABLE <表>
(
字段1, 数据类型[约束条件],
字段2, 数据类型[约束条件],
...
字段n, 数据类型[约束条件],

);

查看数据表
查看数据表
SHOW CREATE TABLE <表>;

查看所有表
SHOW TABLES;

查看表结构
DESCRIBE <表>;

P.S. 关键字 DESCRIBE 可以缩写为 DESC

情景六
表的维护和修改
修改表名
ALTER TABLE <旧表> RENAME [TO] <新表>;

P.S. [to] 是可选的

修改数据类型
ALTER TABLE <表> MODIFY <字段> <数据类型>;

添加字段
ALTER TABLE <表> ADD <新字段> <数据类型>
[约束条件] [first|after <已存在字段>];

字段名排列顺序
ALTER TABLE <表名> change <旧字段名> <新字段名> <数据类型>;

P.S. 新数据类型不能留空，即使修改字符的数据类型相同也必须设置

删除字符、删除表

删除字段
ALTER TABLE <表> DROP <字段>;

删除表
DROP TABLE <表>;

温馨提示：删除表时，可能会与其他表存在关联

情景七
表记录的插入
创建好数据库和数据表后，想要操作数据表中的数据，首先得保证表中存在数据
MySQL使用 insert语句 向数据库添加数据

向数据表中添加新的记录应该包含表的所有字段，即 为该表的所有字段添加数据，
为该表所有字段添加 insert语句

insert 语句中 指定所有字段名

INSERT INTO <表>(<字段1>, <字段2>,...)

VALUES(<值1>, <值2>,...); # 字段和值要匹配：顺序和数据类型匹配

insert 语句不指定字段名 ; 插入所有的字段,给出的值需与定义时保持一致

INSERT INT <表> VALUES(<值1>, <值2>,...);

省略表名插入一条记录
MySQL中，也可以在 insert语句 中只向 部分字段 中添加值

insert语句还有一种语法格式，可以为表中指定的字段或者全部字段添加数据
INSERT INTO <表>
SET <字段1> = <值1>[, <字段2> = <值2>,......]; #最后一组不需要逗号

同时插入多条记录
有时，需要一次向表中插入多条记录
在MySQL中提供了一条insert语句同时添加多条记录的功能
INSERT INTO <表>[(<字段1>, <字段2>,...)]

VALUES(<值1>, <值2>,...), (<值1>, <值2>,...),
...
(<值1>, <值2>,...);

情景八
表记录的修改和删除
修改数据
UPDATE <表> SET <字段1> = <值1>, <字段2> = <值2>
[WHERE <条件表达式>]; # 指定更新数据需要的条件

注意：没有使用 WHERE 子句，整个表格的所有记录都会更新

删除记录
DELETE FROM <表> [WHERE <条件表达式>];

注意：需要使用WHERE子句，来指定删除记录的条件

删除表中所有记录
TRUNCATE [TABLE]<表>;

注意：删除所有记录前，应该备份
CREATE TABLE <备份表>
SELECT * FROM <表>;

比较

  1. DELETE语句 是DML语句，TRUNCATE语句通常被认为是DDL语句

  2. DELETE语句后可以跟WHERE子句中的条件表达式只删除满足条件的部分记录，而TRUNCATE语句只能用于删除表中的所有记录

  3. 使用 TRUNCATE 语句删除表中的数据，再向表中添加记录时，自动增加字段的默认初始值重新由1开始；使用DELETE语句删除表中的所有记录，再向表中添加记录时，自动增加字段的值为删除该字段的最大值加一

4.使用DELETE语句时，每删除一条记录都会在日志中记录，而使用TRUNCATE语句时，不会在日志中记录删除的内容，因此TRUNCATE语句的执行效率比DELETE语句高

情景九
表单无条件查询
第一部分：复习与回顾
一、什么是SQL？
SQL : Structured Query Language，结构化查询语言，是关系数据库管理系统的标准语言
二、SQL 分类：

  1. DML，数据操作语言，用于检索或者修改数据
    增、删、改、查

  2. DDL，数据定义语言，用于定义数据的结构
    创建、修改、删除

  3. DCL，数据控制语言，用于定义数据库用户的权限
    授权、回收
    举例
    SELECT * FROM emp; # DQL
    CREATE TABLE s # DDL

第二部分：带着问题学习新知

  1. SELECT 语句的语法格式
    SELECT <>
    FROM <>
    ...
    ORDER BY <> ASC|DESC
    LIMIT n

  2. 单表无条件查询使用 通配符* 查询所有列和指定列

  3. distinct 消除查询结果中的重复项

  4. LIMIT top 限制查询结果的数量

  5. 改变查询显示结果的列标题
    <原名> <新名> | <原名> AS <新名>

  6. 如何对查询结果进行排序 ORDER BY

三、单表无条件查询
SELECE语句
SELECE [DISTINCT] * | {<字段1>, <字段2>, ..., <字段n>} # “ * ”表示表中的所有字段 | <字段>查询的表中的指定字段；“ DISTINCT ” 可选，用于剔除查询结果中的重复的数据
FROM <表> # 从制动的表中查询数据
[WHERE 条件表达式1] # “ WHERE ”可选参数，用于指定查询条件
[GROUP BY <字段> [HAVING <表达式2>]] # “ GROUP BY ” 可选，用于将查询结果按照指定字段进行分组； “ HAVING ” 可选，用于分组后的结果进行过滤
[ORDER BY <字段> [ASC | DESC] # “ ORDER BY ” 可选，用于查询结果按照指定字段进行排序。排序方式由参数ASC(升序排序) 或参数DESC 控制(降序排序)，默认升序
[LIMIT [OFFSET] <记录>] # “ LIMIT ” 可选，用于限制查询结果的数量。第一个参数“OFFSET”表示偏移量(如果偏移量为0则从查询结果的第一条记录开始类推)，如果不指定OFFSET，其默认值为0。第二个参数“记录数”表示返回查询的条数

查询所有字段
查询表中所有字段的数据，使用\" * \" 统配符查询数据的语法格式
SELECE * FROM <表>;

指定字段
查询指定字段是指在 SELETE语句 的字段列表中指定要查询的字段，仅针对部分字段
SELECE <字段1>, ..., <字段n>
FROM <表>;

带计算的列
SELECT <字段> * <数字>, ...
FROM <表>

消除重复项的查询
SELECE DISTINCT <字段>
FROM <表>;

DISTINCT作用于多个字段
SELECE DISTINCT <字段1>, <字段2>, ...

只有关键字后指定的多个字段都相同，才会被认作重复记录

限制输出结果集的查询
SELECE <字段1>, <字段2>, ...
FROM <表>
LIMIT [OFFSET, ] <记录> # 记录表示返回结果的条数

LIMIT可跟两个参数，“ OFFSET ”，可选，表示偏移量，如果偏移量为0则查询结果从第一条记录开始如果不指定默认值为0

“记录数”表示查询记录的数目

SQL Serve 使用 top关键字

查询排序
从表中查询出来的结果可能是无序的，或不是用户所需要的，为了使查询结果满足用户的需求，可以使用 ORDER BY 对查询结果进行排序
SELECE <字段1>, <字段2>, ...
FROM <表>
ORDER BY <字段1> [ASC|DESC],
<字段2> [ASC|DESC],
...

你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。
默认情况下，它是按升序排列。

改变查询显示结果的列标题
在查询结果时，如果表名或字段名很长使用起来不太方便，这时可以为表和字段取一个别名，这个别名可以替代其指定的表和字段

改变格式

SELECE <字段> [AS] <别名> [, 字段 [AS] 别名, ...]
FROM <表>;

情景十
一、作业中的问题

  1. 表单的组成
    SELECT 1+2;
    3

SELECT DATABASE(); # 显示所有数据库
SELECT user() # 取得当前用户

MySQL 最小

SQL Server

  1. 求年龄
    两个年份相减： 当前年份-出生日期的年份
    2020-birthday
    2020-year(birthday)
    Year(now())-year(birthday)

SELECT <字段>, Year(now())-year(birthday)
FROM <表>;

year # 提取日期中的年份
mouth # 提取月份
day # 提取日期

system() # 提取系统时间 MySQL
now() # 提取系统时间 MySQL
getdate() # 提取系统时间 SQL Server

  1. 表达式的书写
    score # 表示成绩
    score < 60 # 不及格
    score = score+10 # 提高10分
    score=60 # 60分

数值直接表示，不需要加引号

二、表单条件查询
Where子句
SELECE * [<字段1>, <字段2>, ...]
FROM <表>
WHERE <条件> # 文字需要加单引号\'\'

关系运算符
= 等于
<> 不等于
!= 不等于
< 小于
<= 小于等于

大于
= 大于等于

范围判断
用来查询在某一指定范围内的数据行，范围内的数据行，包含两端的值
小值在前，大值在后
BETWEEN..AND.. NOT BETWEEN..AND..
SELECT * [<字段1>, <字段2>, ...]
FROM <表>
WHERE <字段> [NOT] BETWEEN <值1> AND <值2> # 数值小在前，数值大在后

列表判断
判断是否在指定集合中
SELECE * [<字段1>, <字段2>, ...]
FROM <表>
WHERE <字段> [NOT] IN (<值1>, <值2>, ...)

逻辑运算符
使用逻辑表达符，可以将多个查询条件连接起来
AND(与) 全真则真，一假则假
OR(或) 一真则真，全假则假
NOT(非) 否定其后表达式
SELECE * [<字段1>, <字段2>, ... ]
FROM <表>
WHERE <条件1> <逻辑运算>

情景十一
第一部分：作业中的问题

  1. limit子句
    limit可以跟随 1个参数，表示起始位置(从第几条记录开始，MySQL从中位序从0开始)
    2个参数，表示记录的个数

实际上，第一个参数是可以缺省的，默认从0开始
LIMIT 3 = LIMIT 0, 3

  1. 逻辑运算符的优先级
    not and or (由高到低)

第二部分
模糊查询
带LIKE关键字的查询——判断两个字符串是否匹配
SELECT * | {<字段1>, <字段2>, ...}
FROM <表>
WHERE <字段> [NOT] LIKE \'匹配字符\';

通配符
LIKE语法格式中的“匹配字符串”指定用来匹配的字符串，其值可以是一个普通字符串，也可以是包含百分号(%)和下划线(_)的通配字符串

百分号和下划线统称为通配符，它们在通配符中有特殊含义

百分号(%)通配符
以匹配任意的字符串，包括空字符串，任意多个，可以是0个

下划线(_)通配符
下划线通配符只匹配单个字符，如果要匹配多个字符串，需要使用多个下划线通配符

空值查询
判断某些列是否是NULL值或非空值
SELECT * | <字段1>, <字段2>, ...
FROM <表>
WHERE <字段> IS [NOT] NULL;

NULL # 表示不确定的值，与0不同，不是空字符
=0 / ! =0 # 与0比较

空值一定不能用 = 或 !=

情景十二 数据库设计——概念模型ER图
一、数据库设计步骤
1.需求分析—— 现实世界（数据库设计的第一步，整个设计的基础）
业务流程——得到数据流图、数据字典描述系统
信息需求
处理需求
安全性与完整性要求
需求分许不充分，会导致整个数据库重新返工

2.概念结构设计——信息世界（面向用户面向客观世界的模型是现实世界到信息世界的第一次抽象）

常用的工具为：E-R模型——E-R图

概念模型是与具体的DBMS无关的

	系统分析	  数据库设计

显示世界事物及联系 ⇨ 信息世界概念模式 ⇨ 计算机世界数据模型
信息化 数据化

3.逻辑结构设计——机器世界（按计算机系统的观点对数据建模，用于数据库管理系统的实现，是对现实世界的第二次抽象）

逻辑模型是具体的和DBMS有关联的

数据库逻辑模型：
层次模型
网状模型
关系模型
对象对象模型

4.物理结构设计——数据库的存储规划（物理设备上的存储结构与存取方法与给定的计算机系统相关
确定数据的存储结构
确定数据的存取方法

5.数据库实施——投入运行后，还需要DBA进行运行和维护（运用DBMS提供的数据语言及宿主语言，建立数据库，编制与调试应用程序，组织数据入库，进行试运行）

数据库运行与维护：通常由DBA完成，包括：
数据库的备份与恢复
数据库的安全性与完整性控制
数据库性能的监督、分析和改进
数据库的重组和重构造

二、概念模型
1.信息世界的基本概念
(1)实体(Entity)：现实世界中存在的可以区分的事物
(2)属性(Attribute)：描述实体的特征，与具体的事物有关
(3)关键字(Key)：唯一可以标识出这个实体的属性或是属性的集合
(4)实体型(Entity Typr)：用实体名和属性名集合来抽象描述同类实体
(5)实体集(Entity Set)：实体的集合
(6)联系(Relationship)：实体间或实体内部属性之间的关联，两个实体之间的联系
实体(Entity)：客观存在并且可以相互区别的事物成为实体。实体可以是具体的事物，也可以是抽象的事件。
属性(Attribute)：描述实体的特征称为属性。一个实体可以用若干个属性来描述。
码(key)：唯一标识符的属性或属性集
域(Domain)：属性的取值范围
实体性(Entity Type)：具有相同属性的实体必然具有相同的特征和性质，用实体名及其属性名的集合抽象和刻画同类实体。
实体集(Entity Set)：同类实体的集合称为实体集
联系(Relationship)：在现实世界中，事物内部以及事物之间是有联系的，这些联系在信息世界中，事物内部以及事物之间是有联系的，这些联系在信息世界中反应为实体(型)内部的联系和实体(型)之间的联系。实体内部的联系通常指组成实体的各属性之间的联系。实体内部的联系通常是指组成实体的各属性之间的联系；实体之间的联系通常是指不同实体集之间的联系。

2.概念模型的表示方法
ER图的绘制方法
概念模型的表示方法有很多，最常见的方法为实体-联系方法，简称E-R方法，该方法使用E-R图来描述现实世界的概念模型。
E-R方法 (Entity-Relationship Approach)
E-R图 (Entity-Relationship Diagram)

(1)两个实体之间的联系类型通常有三种：
一对一联系：从一端的实体集中一个实体到另一端只能找到唯一的实体与之对应，反之亦然
一对多联系：从一段的实体集中一个实体到另一端找到多个实体与之对应，相反从另一端中的一个实体只能找到一个相对应
多对多联系：从一端的实体集中集中一个实体到另一端找到多个实体与之对应，反之亦然

(2)ER图的绘制方法
实体：用矩形表示，在矩形内写实体名
属性：用椭圆内注明联系
联系：用菱形表示，在菱形框内部写联系名。

局部ER图设计
构造E-R图的方法
1.确定实体
2.除去重复的实体
3.确定实体结构
4.确定主关键字
5.定义联系

情景十三 数据库设计——合并局部ER图
一、数据库设计过程
ER图设计步骤
对数据进行抽象并设计局部ER图
将各局部ER图进行合并，形成初步ER图
消除不必要的冗余，形成基本ER图

设计局部ER图：对需求分析阶段收集到的数据分类、组织，形成实体型、属性和码，确定实体型之间的练习类型。

两个准则：
属性是不可分的数据项
属性不能与其他实体型有联系

二、冲突
合并局部ER图：在合并中通常有这三类冲突。
属性冲突，属性域冲突即属性值的类型、取值范围不同。
列的问题
命名冲突，同名异义或异名同义
结构冲突，同一对象在不同局部应用中有不同抽象或同一实体在不同局部ER图中的属性个数和排列顺序不同。
同一个对象中属性或实体的差异

三、冗余
消除掉不必要的冗余：
冗余的数据，由基本数据导出的数据
冗余的联系，由其他联系导出的联系
并不是所有的冗余都要消除，有时为了提高效率是可以允许冗余的存在，要根据用户的整体需求决定

情景十四 关系模型
〇、复习
1.需求分析——现实世界
业务流程
得到数据流图、数据字典描述系统

2.概念设计——信息世界
概念设计与具体的DBMS无关
常用的ER模型，ER图

3.逻辑设计——机器世界
逻辑模型与具体的DBMS有关联
数据库逻辑数据模型：层次模型、网状模型、关系模型、面对对象模型

4.物理模型
数据库的存储规划

5.数据库的实施
入库、试运营

6.数据库的运行和维护
投入运营后，并没有结束，DBA进行转储、恢复、安全性控制、性能监督等

一、关系模型
1.数据结构——关系、二维表
2.数据集合——数据的插入、删除、修改、查询(增删改)
3.数据完整性
(1)实体完整性
(2)域完整性
(3)参照完整性

二、关系术语
关系(relation)：一个关系对应着一张二维表
元组(tuple)：表中的一行就是一个元组，也称记录
属性(attribute)：表中的一列称为一个属性，也称字段
域(domain)：列的值就是属性值，属性值的取值范围为域
关系模式：对关系的描述称为关系模式，表示为，关系名(属性1, 属性2, ..., 属性n)
候选键或候选码：关系中能唯一标识该关系的元组的多个属性或属性集
拿出一个属性(属性的集合)，就能唯一标识出该元组
一个关系模式中可以拥有多个候选键
主键或主码：在候选键中能唯一标识该关系的元组的多个属性或属性集
每个关系中只能有一个主键
主属性和非主属性：包含在任何一个候选键中的属性为主属性，不包含任何一个候选键中的属性称为非主属性
外键或外码：关系中的某个属性或属性集时别的关系的主键，称为关系的外键

三、概念模型
一个实体集转换为一个关系模式。实体集的名称为关系名，实体集的属性称为关系的属性，实体集的码就是关系的主码

将 1:1 联系转换为关系模型 / 与任意一端对应的关系模式合并
方案一：单独为一个关系模式
实体名(矩形)作为关系名，将每个集合的主键写到一起
方案二：将一端合并到另一端 (推荐)
实体名(矩形)作为关系名，把其中一个集合的全部元素写出，把另一个表的主键添加到集合的后面

将 1:n 联系转换为独立的关系模式 / 与n端对应的关系模式
方案一：独立的关系模式
实体名(矩形)作为关系名，将每个集合的主键写到一起
方案二：将一端合并到多端(推荐)
实体名(矩形)作为关系名，把其中一个集合的全部元素写出，把另一个表的主键添加到集合的后面

将 m:n联系转换为独立的关系模式
联系(菱形)作为关系名，将每个添加主键，后面添加联系的

具有相同码的关系模式可以合并
实体集之中的实体之间有关联，将一端添加到多端中
实体名(矩形)作为关系名，写出实体中的全部属性，后面添加其他实体集的主键

将多元联系转换为独立的关系模式
联系(菱形)作为关系名，先写出所有实体集的主键然后添加与联系相连的属性

1:1，将一端的码合并到另一端；1：n，将1端的码合并到多端；m:n，形成新的关系模式，将两端的码与联系本身的属性合在一起。

情景十六 实体完整性和域完整性
一、数据完整性
1、实体完整性——对行的要求
每个实体的每一条记录都是唯一的
不能有一模一样的两条记录
 
对应的约束：primary key unique(主键约束，唯一约束)

2、域完整性——对列的要求
域—属性值的取值范围
 
对应的约束：not null default check(非空默认检查)

3、参照完整性----两个表之间的关系

二、约束
1、主键约束 PRIMARY KEY
建表时创建主键约束
后期加入主键约束

2、唯一约束 UNIQUE

(前2个实现实体完整性)

3、非空约束 NOT NULL(列的值)
4、默认约束 DEFAULT
5、检查约束 CHECK

(后3个实现域完整性)

三、操作—在已创建的表中设置约束
1、主键约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 PRIMARY KEY(字段名);

练习：对book表设置主键约束(对borrow表设置主键约束，谁是该表的主键？)
Alter table borrow
Add constraint PK_br primary key(r_id,b_id);

注意：一个表有且只有一个主键，可以是单一属性，也可以是多个属性。

2、唯一性约束
ALTER TABLE 表名
ADD CONSTRAINT 约束名 UNIQUE(字段名);

将reader表的phone设置为唯一约束
Alter table reader
Add constraint un_r unique(phone);

主键约束和唯一约束的区别是主键约束的字段不允许为NULL，而唯一性约束的字段可以为NULL。

主键约束一个表中只能有一个，而唯一约束一个表中可以有多个。

3、非空约束
SQL Server的语法：
ALTER TABLE  表名
ALTER column  列名 数据类型  NOT NULL;

将reader表的r_name设置为非空
Alter table reader
Alter column r_name varchar(16) not null;

MySQL的语法：
ALTER TABLE  表名
MODIFY column  列名 数据类型  NOT NULL

例如：
Alter table reader
MODIFY column r_name varchar(16) not null;

4、默认值约束
SQL sever的语法格式
ALTER TABLE  表名
ADD constraint  约束名 DEFAULT(‘默认值’)  for 列名;

将reader表的sex默认值设置为‘男’
Alter table reader
Add constraint de_sex default(\'男\') for sex;

MySQL语法：

5、检查约束(SQL Server)
ALTER TABLE  表名
ADD constraint  约束名 CHECK(表达式);

将reader表的性别设置检查约束，值仅能为‘男’或“女”。
Alter table reader
Add constraint ck_sex check(sex=\'男\' or sex=\'女\');

情景十七 参照完整性
〇、复习
1.实体完整性——对行的要求
每个实体的每一条记录都是唯一的，即不能出现两条相同的数据
对应的约束：primary key 、unique

2.域完整性——对列的要求
域是指属性值的取值范围
对应的约束：not null 、default 、check

3.参照完整性——两个表之间的关系
主表和从表的关系：从表某个字段要依赖主表的主键。
外键(外码)：关系中的某个属性或属性集时别的关系的主键，称为关系的外键

一、外键约束 FOREIGN KEY
语法格式：
ALTER TABLE 表名
ADD CONSTRAINT 名字 FOREIGN KEY(字段名) REFERENCES 主表(主键);

二、操作——在已经创建的表中设置外键约束
首先要保证主表的主键设置完成，才能设置外键约束

三、外键约束的作用
参照完整性将防止用户执行下列非法操作
在主表中没有关联的记录时，将记录添加或更改到相关表中
更改主表中的值，导致相关表中产生孤立记录
从主表中删除记录，但仍然会存在与该记录匹配的相关记录

四、管理约束
1.查看约束
SHOW CREATE TABLE 表名;

2.删除约束
(1)删除主键约束
ALTER TABLE 表名
DROP PRIMARY KEY;

(2)删除外键约束
ALTER TABLE 表名
DROP FOREIGN KEY 约束名;

(3)删除唯一约束
唯一约束也是约束，删除唯一约束，即删除索引
DROP INDEX 约束名或索引名 ON 表名;

SQL Sever
1.查看约束
EXEC sp_helpconstraint 表名

2.删除约束
ALTER TABLE 表名
DROP CONSTRAINT 约束名

情景十八 创建表的同时建立约束
一、创建表的同时创建约束
创建顺序：先主表，后从表

二、约束类型
1.列级约束
对某一列设置的约束：可以有primary key、not null、default、check
列名 数据类型 NOT NULL
列名 数据类型 NOT NULL PRIMARY KEY 或 PRIMARY KEY (列名)
列名 数据类型 UNIQUE 或 CONSTRAINT 约束名 UNIQUE (列名)

2.表级约束
对多个列或者多个表设置的约束：可以有primary key、check、foreign key
列名 数据类型 DEFAULT \'默认值\'
列名 数据类型 CHECK (表达式)
列名 数据类型 FOREIGN KEY(外键) REFERENCES 主表 (主键)

三、级联
当有了外键约束，表与表之间就建立了联系。删除主表的记录，如果从表有相关联的记录时，这时，不能直接删除记录。必须先删除从表记录，再删除主表记录。

CASCADE——删除包含与已删除键值有参照关系的所有记录
在建立外键时添加ON DELETE CASCADE

四、小结
创建约束时，分为两种情况：
1.在已经创建完成的表中创建约束
2.在创建表的同时创建约束

情景十九 复杂查询——聚合函数的使用
一、常用的聚合函数
常用的统计函数有6个：
1、COUNT（*）：统计记录个数
2、COUNT（列名）：按列名统计非空记录个数
3、SUM（列名）：求和
4、AVG（列名）：求平均值
5、MAX（列名）：求最大值
6、MIN（列名）：求最小值

二、使用聚合函数对“教务管理”数据库进行统计
1、查询学生总数。
Select count(*) 学生数,count(学号) AS 学生总数
From 学生;

2、查询有几个专业。
SELECT count(distinct 专业)
FROM 学生;

3、在选课表中查询成绩的最高分、最低分。
Select max(成绩) 最高分,min(成绩) 最低分
From 选课;

4、在选课表中查询成绩的成绩的总分、平均分。
Select sum(成绩),avg(成绩)
FROM 选课;

5、查询选修了课程的学生人数。
Select count(distinct 学号)
From 选课;

6、计算C01号课程的学生平均成绩。
SELECT avg(成绩)
FROM 选课
WHERE 课程号=\'c01\';

7、查询选修了C01号课程的学生最高分和最低分。
SELECT max(成绩),min(成绩)
FROM 选课
WHERE 课程号=\'c01\';

8、查询学号为“20050101”的学生的总成绩及平均成绩。
SELECT sum(成绩),avg(成绩)
FROM 选课
WHERE 学号=\'20160101\';

9、查询有考试成绩的学生人数。
Select count(distinct 学号)
From 选课
Where 成绩 is not null;

二、使用聚合函数的注意事项
（1）除了COUNT（*）函数外，其他的统计函数都不考虑返回值或表达式为NULL的情况。集合函数
（2）统计函数只能出现在目标列表达式、ORDER BY子句、HAVING子句中，不能出现在WHERE子句和GROUP BY 子句中。
（3）默认对所有的返回行进行统计，包括重复的行；如果要统计不重复的行信息，则可以使用DISTINCT选项。
（4）如果对查询结果进行了分组，则统计函数的作用范围为各个组，否则统计函数作用于整个查询结果。

情景二十——分组
一、分组
关键字： GROUP BY 字段名
显示组内所有的字段：GROUP_CONCAT(列)

GROUP BY 和 聚合函数一起使用
1.在表中统计出版社出版图书的平均价格和数量
SELECT publish 出版社,COUNT(*) 数量, AVG(price) 平均价格
FROM book
GROUP BY publish;

2.统计表中各种类型的图书的数量
SELECT b_type, count(*)
FROM book
GROUP BY b_type;

3.统计表中每个系的读者人数
SELECT dept, COUNT(*)
FROM reader
GROUP BY dept;

GROUP BY 与 HAVING 子句一起使用
4.查询表中平均价格在20元以上的出版社
SELECT publish 出版社,count(*) 数量,AVG(price) 平均价格
FROM book
HAVING AVG(price)>20;

5.查询表中平均价格在20元并且图书数量大于3的图书
SELECT publish 出版社,COUNT() 数量,AVG(price) 平均价格
FROM book
GROUP BY publish
HAVING AVG(price)>20 AND COUNT()>=3;

6.统计每个系读者的人数并且只显示读者人数大于3的记录
SELECT dept, count()
FROM reader
GROUP BY dept
HAVING COUNT()>=3;

7.检索表中价格在30元以上的图书信息，并且按照价格降序输出
SELECT *
FROM book
WHERE price>30
ORDER BY price desc;

8.检索borrow表 2017年借出的图书信息，要求借出日期升序输出
SELECT *
FROM borrow
WHERE year(outdate)=2017
ORDER BY outdate ASC;

SELECT *
FROM borrow
WHERE outdate like\'2017%\'
ORDER BY outdate ASC;

9.检索表中所有的图书信息，按出版社升序排列输出，出版社相同价格的图书继续按照价格降序输出
SELECT *
FROM book
ORDER BY publish asc, price desc;

10.在\"book\"表中查询图书类型，平均价格在20元以上并且图书数量在两本以上的图书出版社，要求查询结果按平均价格的降序排序
SELECT *
FROM book
WHERE b_type=\'计算机类\';

SELECT publish, AVG(price), COUNT(*)
FROM book
WHERE b_type=\'计算机类\'
GROUP BY publish;

SELECT publish, AVG(price), COUNT()
FROM book
WHERE b_type=\'计算机类\'
HAVING AVG(price)>=20 AND COUNT()>=2;

SELECT publish, AVG(price), COUNT()
FROM book
WHERE b_type=\'计算机类\'
GROUP BY publish
HAVING AVG(price)>=20 AND COUNT()
ORDER BY AVG(price) DESC;

注意：
WHERE 是执行 GROUP BY操作之前进行的过滤，表示全部数据中筛选出部分符合条件的数据
在WHERE子句中不能使用统计函数
HAVING 是在 GROUP BY 分组之后的再次过滤，可以使用统计函数

三、总结
SELECT 列 FROM 表 [WHERE 条件 ] [GROUP BY 字段 ] [HAVING 条件 ] [ORDER BY 字段 ] [LIMIT 记录 ]

SELECT 目标列表    --字段名，计算式（sal*12）,聚合函数
FROM 表名         --从哪个表查询，可以是多个表
[WHERE 查询条件]
[GROUP BY 字段名]    --按某个字段进行分组
[HAVING 查询条件]     --对分组的结果再次筛选
[ORDER BY 字段名]  --对查询结果按字段进行升序（降序）排序
[LIMIT 偏移，记录数]    --输出指定的记录

情景二十一——链接查询
一、基本概念
当需要提取数据的完整信息时，需要同时从多个表中获取相关的数据。这种从多个表中提取数据的操作，称为多表查询。

二、笛卡尔积
笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员

1.使用交叉链接查询
SELECT *
FROM book,borrow;

消除无意义数据，找关联字段——两表均有 b_id
关联条件：两者相等，Book.B_id = borrow.b_id

SELECT b_name, r_id, outdate, indate
FROM book, borrow
WHERE Book.B_id = borrow.b_id;

-- 别名
SELECT b_name, r_id, outdate, indate
FROM book B, borrow bo
WHERE B.B_id = bo.b_id;

三、内连接
2.查询\"杰哥\"的借阅，显示姓名、图书编号和借阅日期
首先，确定关联几个表：reader r 和 borrow bo
接着，找出关联字段和关联条件：r.r_id = bo.r_id
最后写代码

SELECT r.r_name, bo.b_id, bo.outdate
FROM reader r, borrow bo
WHERE r.r_id = bo.r_id AND r_name=\'杰哥\'

3.通过表book、reader和borrow查询借书人的姓名、图书、名称和借阅时间
确定表：book b, reader r, borrow bo
确定关联字段和条件：b.b_id = bo.b_id AND r.r_id = bo.r_id
写代码

SELECT r.r_name, b.b_name, bo.outdate
FROM book b, reader r, borrow bo
WHERE b.b_id = bo.b_id AND r.r_id = bo.r_id

4.查阅同时借阅了图书编号为 ‘2737’ 和 ‘2738’ 的学生编号
首先确定表：
对 borrow表 做了2遍查询，逻辑上的两张表，此时称为 自链接，必须起别名
borrow bo1 borrow bo2

接着：确定关联字段以及关联条件
bo1.r_id = bo2.r_id

最后写代码：
SELECT bo1.r_id
FROM borrow bo1, borrow bo2
WHERE bo1.r_id = bo2.r_id AND bo1.b_id=2737 AND bo2.b_id=2738;

5.从 ‘’ 和 ‘’ 两个数据表中查询所有读者的借书情况，查询结果显示读者姓名、所在系部、图书编号、借阅时间
首先：确定表 borrow bo reader r
接着：关联字段和关联条件 r.r_id=bo.r_id
最后：写代码
SELECT r.r_name, r.dept, bo.b_id, bo.outdate
FROM borrow bo, reader r
WHERE r.r_id = bo.r_id;

四、外连接
类型
左外连接
表 left join 表2 on 条件

右外连接
表 right join 表2 on 条件

全外连接
表 full join 表2 on 条件

6.使用左外连接从 ‘borrow’ 和 ‘reader’ 两个数据表中查询所有读者的借书情况，查询结果显示读者姓名、所在系部、图书编号、借阅时间，没有借阅记录的读者也要显示出来
SELECT r.r_name,r.dept,bo.b_id,bo.outdate
FROM reader r left join borrow bo on r.r_id=bo.r_id;

7.使用右外连接从 ‘borrow’ 和 ’reader‘ 两个数据表中查询所有读者的借书情况，查询结果显示读者姓名、系部、图书编号、借阅时间，没有记录的读者也要显示出来
SELECT r.r_name,r.dept,bo.b_id,bo.outdate
FROM borrow bo right join reader r on r.r_id=bo.r_id;

8.使用完全外连接从 ‘borrow’ 和 ‘book’ 两个数据表中查询所有的借书信息及图书信息，包括未借出的图书。内连接的另一种语法：
SELECT r.r_name,r.dept,bo.b_id,bo.outdate
FROM borrow bo join reader r on r.r_id=bo.r_id;

五、注意事项
进行多表查询时，经常为表起别名（一旦起别名就不能使用原名）
可以使用表别名作为前缀用来限定列
通过使用表别名可以提高性能
使用表别名可以区分来自不同表但是名字相同的列
只要表关联，肯定又关联字段用于消除笛卡尔积，只是这种关联字段需要根据情况使用不相同的限定符
等值连接
非等值连接

情景二十二——子查询
一、子查询概念
嵌套在其他SQL语句中的SELECT语句，也成为内部查询

执行过程：由内向外，先处理子查询，再将子查询的返回值结果用于父语句(外部语句)的执行

范例：查询与张山在同一个系学习的学生的姓名和所在系
主查询：谁和张山在一个系
子查询：张山的系部编号时多少？

第一步：查询张山的系编号
SELECT 系编号
FROM 学生
WHERE 姓名=\'张山\'

第二步：查询系编号是 d10 的学生的姓名和所在系
SELECT 姓名,系编号
FROM 学生
WHERE 系编号=\'d10\'

将上面两步合起来：
SELECT 姓名,系编号
FROM 学生
WHERE 系编号=(SELECT 系编号
FROM 学生
WHERE 姓名=\'张山\')

查找次数为：6+6 ，12次

一般可以在 WHERE子句、HAVING子句、FROM子句中使用子查询
根据返回结果的不同，嵌套子查询可以分为返回单个值，返回值列表

二、返回单个值
查询出生日期最晚的学生的姓名、性别和出生日期，排序思路：
SELECT 姓名，性别，出生日期
FROM 学生
ORDER BY 出生日期 DESC
LIMIT 1;

子查询：

第一步，查询出生日期最晚的

SELECT max(出生日期)
FROM 学生;

第二步，查找谁的出生日期是该值，说明谁的年龄就是最小的

SELECT 姓名,性别,出生日期
FROM 学生
WHERE 出生日期=(SELECT MAX(出生日期)
FROM 学生);

分组：
SELECT 姓名,性别,出生日期
FROM 学生
GROUP BY 性别
HAVING 出生日期=MAX(出生日期);

查询所有年龄大于平均年龄的学生姓名
第一步：求平均值
SELECT avg(year(now()-year(出生日期))
FROM 学生;

第二步：年龄大于该值 的学生姓名
SELECT 姓名
FROM 学生
WHERE year(now())-year(出生日期)
(SELECT avg(year(now())-year(出生日期))
FROM 学生)

查询与王武在同一个专业的学生
第一步：查询王武的专业
SELECT 专业
FROM 学生
WHERE 专业=(SELECT 专业
FROM 学生
WHERE 姓名=\'王武\');

第二步：查询专业是该专业的学生信息。
Select *
From 学生
Where 专业=( Select 专业
From 学生
Where 姓名=\'王武\') ;

三、返回多个值(值列表)
IN运算符
查询所有选修了 C01 号 课程的学生姓名
第一步：查询所有选修了 C01 号 课程的学生姓名
SELECT 学号
FROM 选课
WHERE 课程号=\'C01\'

第二步：从学生表中查询是这些学号的学生的姓名
SELECT 姓名
FROM 学生
WHERE 学号 IN(SELECT 学号
FROM 选课
WHERE 课程号=\'C01\');

用IN操作符改写查询与张山在同一个系学习的学生
SELECT 系编号 in (SELECT 系编号
FROM 学生
WHERE 姓名=\'张山\');

查询选修了课程的学生姓名、专业
第一步：查询选课中的学号
SELECT DISTINCT 学号
FROM 选课

第二步：从学生表中查学号是这些学生的姓名和专业
SELECT 姓名,专业
FROM 学生
WHERE 学号 in(SELECT DISTINCT 学号
FROM 选课);

查询没选修课的学生姓名、专业
SELECT 姓名,专业
FROM 学生
WHERE 学号 NOT IN(SELECT DISTINCT 学号
FROM 选课);

选修高数的学生学号和姓名
第一步：查询高数的课程号
Select 课程号
From 课程
Where 课程名=\'高数\';

第二步：查选修了该课程的学号
Select 学号
From 选课
Where 课程号=( Select 课程号
From 课程
Where 课程名=\'高数\');

第三步：查询这些学生的学号和姓名
   Select 学号,姓名
   From 学生
Where 学号 in(Select 学号
From 选课
Where 课程号=( Select 课程号
From 课程
Where 课程名=\'高数\'));

NOT IN 运算符
查询时领导的员工的姓名(HR数据库)
第一步：查询mgr是什么？
Select distinct mgr
From emp;

第二步：查询员工表，看empno在不在该集合中，显示这样的姓名
Select ename
From emp
Where empno in(Select distinct mgr
From emp);

查询不是领导的员工的姓名(HR数据库)
NOT IN操作：如果子查询中有NULL，则不会查询出任何的结果。
Select ename
From emp
Where empno not in(Select distinct mgr
From emp
Where mgr is not null);

NOT IN 操作：如果子查询中有NULL，则不会查询任何结果

ANY 运算符
查询其他专业中计算机网络专业某一学生年龄的学生姓名和年龄
SELECT 姓名,year(now())-year(出生日期) 年龄
FROM 学生
WHERE 专业!=\'计算机网络\' AND
year(now())-year(出生日期) in (select year(now())-year(出生日期)
FROM 学生 WHERE 专业=\'计算机网络\');

现在，将IN运算符替换成: >ANY、<ANY、=ANY

  1. =ANY:与in等价
    Select 姓名,year(now())-year(出生日期) 年龄
    From 学生
    Where 专业!=\'计算机网络\' and
    year(now())-year(出生日期) =any(select year(now())-year(出生日期)
    From 学生 where 专业=\'计算机网络\');

  2. ANY:大于最小值
    Select 姓名,year(now())-year(出生日期) 年龄
    From 学生
    Where 专业!=\'计算机网络\' and
    year(now())-year(出生日期) >any(select year(now())-year(出生日期)
    From 学生 where 专业=\'计算机网络\');

  3. <ANY:小于最大值
    Select 姓名,year(now())-year(出生日期) 年龄
    From 学生
    Where 专业!=\'计算机网络\' and
    year(now())-year(出生日期) <any(select year(now())-year(出生日期)
    From 学生 where 专业=\'计算机网络\');

查询其他专业中比计算机网络专业某一学生年龄小(大)的学生、姓名和年龄——ALL运算符
与每个内容相匹配，有两种形式：

  1. ALL: 大于最大值
    SELECT 姓名,YEAR(NOW())-YEAR(出生日期) 年龄
    FROM 学生
    WHERE 专业!=\'计算机网络\' AND
    YEAR(NOW())-YEAR(出生日期) >ALL(SELECT YEAR(NOW())-YEAR(出生日期)
    FROM 学生 WHERE 专业=\'计算机网络\');

  2. <ALL: 小于最小值
    SELECT 姓名, YEAR(NOW())-YEAR(出生日期) 年龄
    FROM 学生
    WHERE 专业!=\'计算机网络\' AND
    YEAR(NOW())-YEAR(出生日期) <all(SELECT YEAR(NOW())-YEAR(出生日期)
    FROM 学生 WHERE 专业=\'计算机网络\');

二十三——利用子查询进行更新操作
数据的更新操作：增加、修改、删除数据
考虑到学生表以后还要继续使用，可以先将学生表复制一份：
CREATE TABLE xuesheng
AS SELECT * FROM 学生;

一、数据增加
INSERT INTO 表名称[(字段1,字段2,…)]
VALUES(值1,值2,…)

利用子查询插入数据

范例：把平均成绩大于80分的学生的学号和平均成绩存入另一个已知的基本表S_GRADE(SNO，
AVG_GRADE)中。（假设该表已创建）
Create table s_grade
( sno int not null,
 Avg_grade float
);

把平均成绩大于80分的学生的学号和平均成绩
Select 学号,avg(成绩)
From 选课
Group by 学号
Having avg(成绩)>80;

插入：
Insert into s_grade
 Select 学号,avg(成绩)
From 选课
Group by 学号
Having avg(成绩)>80;

二、数据修改
UPDATE　表名称
SET　更新字段1=更新值1，更新字段2=更新值2,...

[WHERE　更新条件]

范例：将计算机网络专业全体学生的成绩提高5分。
最终改什么？改选课表的成绩
UPDATE 选课
SET 成绩=成绩+5
WHERE 学号 in (select 学号
From 学生
Where 专业=\'计算机网络\');

将英语成绩不及格的改为60分。
Update 选课
Set 成绩=60
Where 成绩<60 and 课程号=(select 课程号
From 课程
Where 课程名=\'英语\');

三、数据删除
DELETE　FROM　表名称
[WHERE　删除条件]

在系统开发时，一般在删除操作之前，先给出一个确认的提示框，以防止用户误删除。
 
在DELETE中使用子查询

范例：删除“王武”的所有成绩记录。
明确删除哪个表中的记录？
DELETE FROM 选课
WHERE 学号 in(select 学号
From 学生
Where 姓名=\'王武\');

删除“数控”专业的学生所有成绩记录。

明确删除哪个表中的记录？
Delete from 选课
Where 学号 in ( select 学号
From 学生
Where 专业=\'数控\');

二十四——子查询返回值为单行单列

二十五——子查询返回值为多行单列

二十六——索引
索引的概念与类型
索引是一个单独的、物理的数据结构，在这个结构中保存了索引值及其相应记录的物理地址，并且按照索引值进行排序。
数据库的索引是对数据表中一列或多列的值进行排序后的一种结构，作用是提高表中数据的查询速度

索引类型
（1）按索引值是否惟一
惟一索引     unique
非惟一索引
（2）索引基于的列数
单列索引
复合索引

创建索引
CREATE INDEX 索引名称 ON 表名称(列名称);

唯一关键字UNIQUE，默认情况就是非惟一索引

查看索引
Show index from 表名;

索引不是越多越好？
占存储空间
表如果需要频繁更新，先删除索引，再更新

删除索引
DROP INDEX 索引名称 ON 表名;

五、创建索引的原则
索引是占存储空间的，并且需要系统进行维护，换言之，增加了系统开销。因此，是否创建索引和创建什么样的索引需要遵循一定的原则。

1、适合创建索引：
表中数据量很大
要查询的结果集很小，占表中数据的2%--4%
经常用来做WHERE条件中的列或多表连接的列
查询列的数据范围分布很广
查询列中包含大量的NULL值

2、不适合创建索引：
数据量很小的表
查询中不常用来作为查询条件的列
频繁更新的表
索引列作为表达式的一部分被使用，则创建索引是没有效果的。例如查询条件是sal*12>20000，此时在sal上创建索引没有效果
查询条件有单行函数

六、索引和约束
（1）创建主键约束的时候自动创建唯一性索引
Alter table emp
Add constraint pk_empno primary key(empno);

（2）对于外键列，MYSQL将自动创建索引

二十七——视图
视图是从一个或多个表中提取出来的数据的一种表现形式，是一个命名的查询，用于改变基础表中数据的显示。

一、创建视图
创建视图的语法：
CREATE VIEW 视图名称 AS 子查询;

二、删除视图
删除视图的语法：
DROP VIEW 视图名称;

三、修改视图
修改视图的语法：
CREATE OR REPLACE 视图名称 AS 子查询;

二十八——事务和锁
一、事务的概念
事务，也称工作单元，是由一个或多个SQL语句组成的操作序列，这些SQL语句作为一个完整的工作单元，要么全部执行成功，要么全部执行失败。

二、事务开启
START TRANSACTION; // 开启事务
... // 多条SQL语句构成事务，此时并没有真正提交到数据库中即没真正执行
COMMIT; // 提交事务，即上面的SQL语句真正生效
ROLLBACK; // 回滚事务，取消事务，但只能在commit前才有效

三、事务的特性 ( ACID )

  1. 原子性 ( 不可分隔 )
    指一个事务必须被视为一个不可分隔的最小工作单位，只有事务中所有的数据库操作都执行成功，才算整个事务执行成功，事务中如果有任何一个SQL语句执行失败，已经执行成功的SQL语句也必须撤销，数据库的状态退回到执行事务前的状态。

  2. 一致性
    指事物将数据库从一种状态转变为下一种一致的状态。
    例如，表中有一个字段为姓名，具有唯一性约束，即姓名不能重复，如果一个事物对姓名进行了修改，使姓名变得不唯一了，这就破坏了事物的一致性要求，如果事务中的某个动作失败了，系统可以自动撤销事物，返回起始化状态

  3. 隔离性
    隔离性还可以称为并发控制、可串行化、锁等，当多个用户并发访问数据库时，数据库为每一个用户开启的事物，不能被其他事物的操作数据开锁干扰，多个并发事务之间要相互隔离

  4. 持久性
    事务一旦提交，所作的修改就会永久保存到数据库中，即使数据库发生故障也不应该有任何影响。需要注意的是，事务的持久性不能做到100%的持久，只能从事务本身的角度来保证永久性，而一些外部原因导致数据库发生故障，比如硬盘损坏，那么所有提交的数据可能都会丢失。

四、锁
实例分析：
1）如果两个事务并发的修改：必须加锁。
2）如果两个事务并发的查询：不需要加锁
3）如果一个事务修改，另一个事务查询：看具体的应用情境，设置隔离级别

1）脏读：一个事务读取到另一个事务未提交的数据
隔离级别：
Read uncommitted  --不做任何隔离
Read committed    --可以防止脏读

查询当前隔离级别：
Select @@tx_isolation;
设置当前的隔离级别：
Set  transaction isolation level 级别;

2）不可重复读：一个事务多次读取同一条记录，读取的结果不相同（一个事务读取到另一个事务已经提交的数据）
隔离级别：
Repeatable read   --可以防止脏读、不可重复读

3）幻读（虚读）：一个事务多次查询整表的数据，由于其他事务新增（删除）记录造成多次查询出的记录条数不同（一个事务读取到另一个事务已经提交的事务）
隔离级别：
Serializable   --串行化，所有问题都没有，但性能非常低

五、隔离级别
数据库的使用者自己根据情况决定是否隔离，mysql提供了四种隔离级别：

  1. READ UNCOMMITTED
    READ UNCOMMITTED (未读提交) 是事务中级别最低的，该级别下的事务可以读取到另一个事务中未提及的数据，也被称作脏读(Dirty Read)，这是相当危险的。由于该级别最低，在实际开发中避免不了任何情况，所以一般很少使用。

  2. READ COMMITTED
    大多数的数据库默认的隔离级别就是 READ COMMITTED(读提交)该级别下的事务只能读取到其他事务已经提交的数据，可以避免脏读，但不能避免重复读和幻读的情况

重复读就是在事务内重复读取了别的线程已经提交的数据，但两次读取的结构不一致，原因是查询的过程中其他事务做更新的操作
幻读指一个事务内两次查询中数据条数不一致，原因是查询的过程中其他事务做了添加操作。这两种情况并不算错误，但有些是不符合实际需求的

  1. REPEATABLE READ
    REPEATABLE READ(可重复读)是MySQL默认的事务隔离级别，它可以避免脏读、不可重复读的问题，确保同一事物的多个实例在并发读取数据时，会看到同样的数据行。但理论上，该级别会出现幻读的情况，不过MySQL的存储引擎通过多版本并发控制级制解决了该问题，因此该级别是可以避免幻读的

  2. SERIALIZABLE
    SERIALIZABLE(可串行化)是事务的最高隔离级别，它会强制对事务进行排序，使之不会发生冲突，从而可以解决脏读、幻读、重复读的问题。实际上，就是在每个读的数据行上加锁。这个级别可能会导致大量的超时现象和锁竞争，实际运用中很少是使用

Mysql默认是repeatable read这个隔离级别。

如何选择隔离级别？
我们应该在使用数据库的时候，根据自己想要防止的问题，选择一个能够想要防止的问题的隔离级别中性能尽量高的一个。

二十九——创建存储过程及变量的使用
一、什么是存储过程
存储过程是一条或多条SQL语句的集合，它将这些语句封装起来成为一个代码块，以便重复使用，这样就可以大大减少数据库开发人员的工作量。

二、创建存储过程
Create procedure 存储过程名(参数列表)
Begin
     ……
     ……
End

mysql默认结束符是；
所以需要人工修改结束符：
Delimiter //

三、调用存储过程
语法格式：
Call 存储过程名(参数列表)
Call p1()

四、变量的使用
局部变量—以字母、下划线、数字组成   server sql @开头
全局变量—以@开头                   server sql@@开头

1、 如何定义一个变量
语法格式：
Declare 变量名 类型 [default 默认值];

2、 为变量赋值
方法一：
Set 变量名=表达式;

方法二：
利用查询方式，将查询结果值赋给变量
Select 列名 into 变量名 from 表名 where 条件;

三十——流程控制
一、选择结构
1、IF语句
语法结构：
IF 条件表达式 THEN SQL语句
ELSEIF 条件表达式 THEN SQL语句
ELSE SQL语句
END IF;

2、CASE语句
语法格式：
CASE 表达式
WHEN 值1 THEN SQL语句1
[WHEN 值2 THEN SQL语句2]
……
[ELSE SQL语句n]
END CASE;

二、循环结构
1、LOOP 语句和 LEAVE 语句
Loop实现的是无条件的跳转
标签名：
LOOP
语句
END LOOP 标签名;

在写循环时，循环变量、循环体、循环条件这些要考虑清楚

2、ITERATE 语句

3、REPEAT语句    直到型循环，先执行，后判断条件
语法格式：
REPEAT
语句
UNTIL 条件表达式
END REPEAT;

4、WHILE语句    当型循环，先判断循环条件，再执行
语法格式：
WHILE　条件表达式　DO
语句
END WHILE;

来源：https://www.cnblogs.com/turbospace/p/16254914.html
本站部分图文来源于网络，如有侵权请联系删除。

未经允许不得转载：百木园 » MySQL 笔记
标签：

相关推荐

  • 暂无文章

热门文章