mysql减数命令_MySQL学习笔记

作者：王浩博 李长兴

数据库mysql学习笔记

优化阶段：如何提高数据库效率、如索引，分表等

部署阶段：如何搭建真实的环境系统，如服务器集群，负载均衡

基础阶段：数据库的基本操作(增删改查)，以及一些高级操作(视图、触发器、函数、存储过程等)

1.什么是数据库

数据库：database，存储数据的仓库。

数据库：高效的存储和处理数据的介质(介质主要是两种：磁盘和内存RAM)

2.数据库的分类

数据库基于存储介质的不同：进行了分类，分为两类：关系型数据库(SQL)和非关系型数据库(NoSQL：Not Only SQL,不是关系型的数据库都叫做非关系型数据库)

3.不同的数据库阵营中的产品有哪些

关系型数据库

大型：Oracle，DB2

中型：SQL_SERVER,Mysql

小型：access

非关系型数据库：memcahed，MongoDB，redis(同步到磁盘)

4.两种数据库阵营的区别

关系型数据库：安全(保存到磁盘基本不可能丢失)容易理解，比较浪费空间(二维表)

非关系型数据库：效率高，不安全(断电丢失)

关系型数据库:

1.什么是关系型数据库

关系型数据库：是一种建立在关系模型(数学模型)上的数据库。

关系模型：一种所谓建立在关系上的模型，关系模型包含三个方面：

数据结构：数据存储的问题，二维表(有行和列)

操作指令集合：所有的SQL语句

完整性约束：表内数据的约束(字段与字段)，表与表之间的约束(外键)

2.关系型数据库的设计

关系型数据库：从需要存储的数据需求中分析，如果是一类数据(实体)应该设计成一张二维表：表是由表头(字段名：用来规定数据的名字)和数据部分组成(实际存储的数据单元)

二维表：行和列

表头字段名1字段名2

数据单元数据1数据2

以实际案例来进行处理：分析一个教学系统，教师负责教学，教学生，在教室教学生。

找出系统中所存在的实体：讲师表，学生表，班级表

找出实体中应该存在的数据信息

讲师：姓名，性别，年龄，工资

学生：姓名，性别，学号，学科

班级：班级名字，教室编号

关系型数据库：维护实体内部，实体与实体中间的联系

实体内部联系：每个学生都有姓名，性别，学号，学科信息

姓名性别学号学科年龄

陈明男Itcast0001PHP20

陈晶晶女Itcast0002JAVA18

王威男Itcast0003UI19

第二行的所有字段，都是在描述陈明这个学生(内部联系)；第二列只能放性别(内部约束)

关系型数据库的特点之一：如果表中对应的某个字段没有值(数据)，但是系统依然要分配空间：关系型数据库 比较浪费空间

实体与实体之间的联系：每个学生肯定属于某个班级，每个班级一定有多个学生(一对多)

学生表

姓名性别学号学科年龄

陈明男Itcast0001PHP20

陈晶晶女Itcast0002JAVA18

王威男Itcast0003UI19

班级表

班级名称教师编号

PHP001B205

JAVA001A203

解决方案：在学生表中增加一个班级字段来指向班级(必须能够唯一的找到一个班级信息)

姓名性别学号学科年龄班级名称

陈明男Itcast0001PHP20PHP001

陈晶晶女Itcast0002JAVA18PHP001

王威男Itcast0003UI19JAVA001

学生实体与班级实体的关联关系：实体与实体之间的关系

关键字说明

数据库:database

数据库系统：DBS(database system)：是一种虚拟系统，将多种内容关联起来的称呼

DBS=DBMS+DB

DBMS：database management system ，数据管理系统，专门管理数据库

DBA:：database administrator，数据库管理员

行/记录：row/record ：本质是一个东西：都是指表中的一行(一条记录)：行是从结构角度出发，记录是从数据角度出发

列/字段:column/field，本质是一个东西

SQL

SQL ：structured query language ：结构化查询语言(数据以查询为主：99%是在进行查询操作)

SQL 分为三个部分

DDL：data definition language ：数据定义语言，用来维护数据的结构(数据库，表)，代表指令：create drop alter 等

DML：data manipulation language ：数据操作语言，用来数据进行操作(数据表中的内容)，代表指令：insert ，delete，update等；其中DML内部又单独进行了一个分类：DDL(data query language：数据查询语言，如select)

DCL：data control language ，数据控制语言，主要是负责权限管理(用户)，代表指令：grant，revoke等

SQL是关系型数据的操作指令，SQL是一种约束，但不强制(类似W3C)不同的数据库产品(如ORacle，mysql)可能内部会有细微的区别

Mysql数据库

Mysql：数据库是一种c/s结构的软件：客户端/服务器。若想访问服务器必须通过客户端(服务器一直运行，客户端在需要使用的时候运行)。

交互方式

1.客户端连接认证：连接服务器，认证身份

2.发送SQL指令

3.服务器接受SQL指令：处理SQL指令：返回操作结果

4.客户端接受结果：显示结果

5.断开连接(释放资源：服务器并发限制):exit /quit/ \q

Mysql服务器对象

没有办法完全了解服务器内部的内容；只能粗略的去分析数据库服务器的内部的结构。

将mysql服务器内部对象分成了四层：系统(DBMS)->数据库(DB)->数据表*(table)->字段(filed)

SQL基本操作

基本操作：CRUD

将SQL的基本操作根据操作对象进行分类：分为三类：库操作、表操作(字段)、数据操作

库操作

对数据库的增删改查

1.新增数据库

基本语法

Create database 数据库名字 [库选项]

库选项：用来约束数据库，分为两个选项

字符集设定 ： charset/character set 具体字符集(数据存储的编码格式)：常用字符集：GBK和UTF8

校对集设定：collate 具体校对集(数据比较的规则)

--双中划线：注释(单行注释)，也可以使用#号

--创建数据库

Create database mydatabase charset utf8;

其中数据库名字不能用关键字(已经被使用的字符)或者保留字(将来可能会用到的)

--创建关键字数据库

Create database database charset utf8;

如果非要使用关键字或者保留字，那么必须使用反引号(esc键下面的键在英文状态下的输出``)

--创建关键字数据库

Create database database charset utf8;

中文数据库是可以的，但是有前提条件，保证服务器能够识别(建议不用)

--创建中文数据库

Create database 中国 charset utf8;

如果有乱码，解决方案:告诉服务器当前中文的字符集什么

Set names gbk;

当创建数据库SQL语句执行之后，发生了什么

1.在数据库系统中，增加了对应的数据库信息

2.会在保存数据的文件夹下：data目录，创建一个对应数据库名字的文件夹

3.每个数据库下都有一个opt文件：保存了库选项

查看数据库

1.查看所有数据库

-- 查看所有数据库

Show databases;

2.查看指定部分的数据库：模糊查询

Show databases like ‘pattern’; --pattern 是匹配模式

%：表示匹配多个字符

_:表示匹配单 个字符

-- 创建数据库

Create database informationtest charset utf8;

-- 查看以information_开始的数据库 :需要被转义

Show databases like ‘information_%’;

Show databases like ‘information%’; -- 相当于information%

3.查看数据库的创建语句：show create database 数据库名字；

-- 查看数据库创建语句

Show create database mydatabase;

Show create database database; -- 关键字需要使用反引号

更新数据库

数据库名字不可以修改(不安全)

数据库的修改仅限库选项，字符集和校对集(校对集依赖字符集)

Alter database 数据库名字[库选项]；

Charset /character set [=] 字符集

Collate 校对集

-- 修改数据库informationtest的字符集

Alter database informationtest charset GBK ;

删除数据库

所有的操作中：删除是最简单的

Drop database 数据库名字

-- 删除数据库

Drop database informationtest ;

当执行数据库删除语句后，发生了什么

1.在数据库内部看不到对应的数据库

2.在对应的数据库存储的文件夹内：数据库名字对应的文件夹也被删除(级联删除：里面的数据全部删除)

注意：不要随意删除数据库，应该先进行备份后操作，(删除不可逆)

表操作

表与字段是密不可分的

新增数据表

Create table 表名[if not exists](

字段名字 数据类型 ，

字段名字 数据类型 --最后一行不需要逗号

)[表选项]；

If not exists ： 如果表明不存在，那么就创建，否则不执行穿件代码；检查功能

表选项： 控制表的表现

字符集： charset/character set 具体字符集； --保证表中数据存储的字符集

校对集： collate 具体校对集；

存储引擎：engine 具体的存储引擎(innodb和myisam)

方案一：显式指定表所属的数据库

创建表

Create table if not exists mydatabase.student(

Name varchar(10),

Gender varchar(10),

Number varchar(10),

Age int

)charset utf8;

方案二：隐式的指定表所属的数据库，先进入某个数据库环境，然后这样创建的表自动归属到某个指定的数据库

进入数据库环境：use 数据库名字 ；

Use mydatabase;

-- 创建表

Create table class(

Name varchar(10),

Room varchar(10)

)charset utf8;

当创建数据表SQL指令执行的时候到底发生了什么？

1.指定数据库下已经存在对应的表

2.在数据库对应文件下，会产生对应表的结构文件(跟存储引擎有关系)

查看数据表

数据库能查看的方式，表都能查看

1.查看所有的表：show tables ;

2.查看部分表： 模糊匹配： show tables like ‘pattern’;

-- 查看以s结尾的表

Show tables like ‘%s’;

3.查看表的创建语句： show create table 表明;

-- 查看表创建语句

Show create table class\g;

Show create table class\G; -- 将查到的结构90度变成纵向

4.查看表结构：查看表中的字段信息

Desc/describe/show columns from 表名;

-- 查看表结构

Desc class;

Describe class;

Show columns from class ;

修改数据表

表本身存在，还包含字段：表的修改分为两个部分：修改表本身和修改字段

修改表本身

表本身可以修改：表名和表选项

修改表名：rename 老表名 to 新表名;

-- 重命名表：student表->my_student(取数据库名字的前两个字母)

Rename table student to my_student;

修改表选项：字符集，校对集和存储引擎

Alter table 表名 表选项 [=] 值

-- 修改表选项 ：字符集

Alter table my_student charset = GBK;

修改字段

字段操作很多：新增、修改、重命名、删除

新增字段

Alter table 表名 add [column] 字段名 数据类型 [列属性][位置];

位置：字段名可以存放表中的任意位置

First：第一个位置

After：在哪个字段之后：after字段；默认的是在最后一个字段之后

-- 给学生表增加ID放到第一个位置

Alter table my_student

Add column id int

First; -- mysql 会自动寻找分号作自动结束符

修改字段：通常是修改属性或者数据类型

Alter table 表名 modify 字段名 数据类型[属性][位置];

-- 将学生表中的number 学号字段变成固定长度，并且放到第二位(id)之后

Alter table my_student

Modify number char (10) after id ;

重命名字段

Alter table 表名 change 旧字段 新字段名 数据类型 [属性][类型];

-- 修改学生表中的gender字段为sex

Alter table my_student

Change gender sex varchar(10);

删除字段

Alter table 表名 drop 字段名;

-- 删除学生表中的age字段

Alter table my_student drop age;

小心：如果表中已存在数据，那么删除字段会删除该字段的所有数据(不可逆)

删除数据表

Drop table 表名1,表名2......; --可以一次性删除多张表

-- 删除数据表

Drop table class;

当删除数据表的指令执行之后发生了什么？

1.在表空间中，没有了指定的表(数据也没有了)

2.在数据对应的文件夹下，表对应的文件(与存储引擎有关)也会被删除

注意：删除有危险，操作需谨慎，删除不可逆

数据操作

新增数据

有两种方案

方案1：给全表字段插入数据，不需要指定字段列表：要求数据的值出现的殊勋必须与表中设计的字段出现的顺序一致；凡是非数值数据，都需要使用引号(建议是单引号)包裹

Insert into 表名 values(值列表)[(值列表)]; --可以一次插入多条记录

-- 插入数据

Insert into my_student values(1,’itcast0001’,’Jim’,’male’),

(2,’itcast0002’,’Hanmeimei’,’female’);

方案2：给部分字段插入数据，需要选定字段列表；字段列表出现的顺序与字段的顺序无关；

但是值列表的顺序必须与选定的字段的顺序一致

Insert into 表名(字段列表)values(值列表)[(值列表)];

-- 插入数据： 指定字段列表

Insert into my_student (number,sex,name,id) values (‘itcast0003,’’male’,’Tom’,’3’),

(‘itcast0004’,’female’,’Lily’,’3’);

查看数据

Select */字段列表 from 表名 [where 条件];

-- 查看所有数据

Select * from my_student;

-- 查看指定字段,指定条件的数据

Select id,number,sex,name from my_student where id = 1;

更新数据

Update 表名 set 字段 = 值 [where 条件]; -- 建议都有where；要不就是更新全部

-- 更新数据

Update my_student set sex = ‘female’ where name = ‘Jim’;

更新不一定会成功；没有要真正更新的数据

零行受影响。

删除数据

删除是不可逆的：谨慎删除

Delete from 表名 [where 条件]；

-- 删除数据

Delete from my_student where sex = ‘male’;

中文数据问题

中文数据问题本质是字符集问题

计算机只识别二进制；人类更多的是识别符号；需要有个二进制与字符的对应关系(字符集)

-- 插入中文数据

Insert into my_student values (‘5’,’itcast0005’,’张越’,’男’);

客服端向服务器插入中文数据：没有成功

的原因： 乱码代表的是‘张越’在当前编码(字符集)下对应的二进制编码转换成的十六进制：两个汉字==>四个字节(GBK)

报错：服务器没有识别对应的四个字节：服务器认为数据是UTF8，一个汉字有三个字节；读取三个字节转换成汉字(失败)，剩余的在读三个字节(不够)，最终失败。

所有数据库服务器认为(表现)的一些特性都是通过服务器的变量来保存；系先读取自己的变量，看看应该怎么表现

查看服务器到底识别哪些字符集

Show character set ;

基本上：服务器是万能的，什么字符集都支持

既然服务器识别这么多，总有一种服务器默认的跟客户端打交道的字符集

-- 查看服务器默认的对外处理的字符集

Show variables like ‘character_set%’;

问题根源：客户端数据只能是GBK，而服务器认为是utf8；矛盾产生

解决方案：改变服务器，默认的接受字符集为GBK

-- 修改服务器认为的客户端数据的字符集为gbk

Set character_set_client = gbk;

插入成功

查看数据发现乱码

原因：数据来源是服务器，解析数据是客户端(客户端只识别gbk；只会两个字节一个汉字)；

但是事实服务器给的数据utf8，三个字节一个汉字 乱码

解决方案：修改服务器给客户端的数据字符集为gbk

-- 修改服务器给定数据字符集为gbk

Set character_set_results = gbk;

Set 变量 = 值；修改只是会话级别(当前客户端，当次连接有效，关闭失效)

设置服务端对客户段字符集的认识；可以使用快捷方式：set names 字符集

Set names gbk ====》character_set_client，character_set_results,character_set_connection

-- 快捷设置字符集

Set names gbk;

Connection 连接层：是字符集转变的中间者，如果统一效率更高，不统一也没关系。

校对集问题

校对集：数据比较的方式

校对集有三种格式

_bin:binary,二进制比较，取出二进制位，一位一位的比较，区分大小写

_cs：case sensitive ， 大小写敏感，区分大小写

_ci：case insensitice，大小写不敏感，不区分大小写

查看数据库所支持的校对集

-- 查看所有校对集

Show collation;

校对集应用：只有当数据产生比较的时候，校对集才会生效

对比使用utf8的_bin 和 ci

-- 创建表使用不同的校对集

Create table my_collate_bin(

Name char(1)

)charset utf8 collate utf8_bin;

-- 创建表使用不同的校对集

Create table my_collate_ci(

Name char(1)

)charset utf8 collate utf8_general_ci;

插入数据

Insert into my_collate_bin values(‘a’),(‘A’),(‘B’),(‘b’);

Insert into my_collate_ci values(‘a’),(‘A’),(‘B’),(‘b’);

比较：根据某个字段进行排序：order by 字段名 [asc desc]； asc 升序，desc 降序，默认是升序

-- 排序查找

Select * from my_collate_ci order by name;

Select * from my_collate_bin order by name;

校对集：必须在没有数据之前声明好，如果有了数据在进行校对集的修改，则无效。

-- 有数据后修改校对集

Alter table my_collate_ci collate = utf8_bin;

Web乱码问题

动态网站有三部分构成：浏览器，Apache服务器，数据库服务器，三部分都有自己的字符集(中文)，数据需要在三个部分之间来回传递，很容易产生乱码。

如何解决乱码问题：统一编码(三码合一)

但事实上不可能；浏览器是用户管理(根本不可能控制)，但是必须要解决这些问题

数据类型(列类型)

所谓的数据类型：对数据进行统一的分类，从系统的角度出发是为了能够使用统一的方式进行管理；更好的利用有限的空间。

SQL 中将数据类型分为了三大类：数值类型，字符串类型和时间类型

数值型

数值型数据：都是数值

系统将数值型分为整数型和小数型

整数型

存放整型数据: 在SQL中因为更多的要考虑如何节省磁盘空间，所以系统又将整型细分为了5类：

Tinyint ：迷你整型发，使用一个字节存储，表示的状态最多为256种(常用)

Smallint：小整型，使用2个字节存储，表示的状态最多为65536种

Mediumint：中整型 使用3个字符存储

Int：标准整型，使用4个自己存储(常用)

Bigint：大整型，使用8个字节存储

-- 创建整型表

Create table my_int(

Int_1 tinyint,

Int_2 smallint,

Int_3 int,

Int_4 bigint

)charset utf8;

-- 插入数据

Insert into my_int values (100,100,100,100); -- 插入有效数据

Insert into my_int values (‘a’,’b’,’199’,’f’); -- 无效数据：类型限定

SQL中数值类型全部都是默认有符号，分正负

有时候需要使用无符号类型，需要给数据限定 int unsigned --无符号：从0开始

-- 给表增加一个无符号类型

Alter table my_int add int_5 tinyint unsigned; -- 无符号类型

-- 插入数据

Insert into my_int values (127,1000,1000,10000,255);

查看表结构的时候，发现每个字段的数据类型之后都会自带一个括号，里面有指定的数字

显示宽度：没有特别含义：只是默认的告诉用户可以显示的形式而已，实际上用户是可以控制的，这种控制不会改变数据本身的大小。

Alter table my_int add int_6 tinyint(1) unsigned; -- 指定显示宽度为1

显示宽度的意义：在于数据不够显示宽度的时候，会自动让数据变成对应的显示宽度：通常需要搭配一个前导0来增加宽度，不改变值大小：zerofill(零填充)

Alter table my_int add int_7 tinyint(2) zerofill; -- 显示宽度为2,0填充

零填充会导致数值自动变成无符号

零填充-显示宽度

Insert into my_int values (1,1,1,1,1,1,1);

Insert into my_int values (100,100,100,100,100,100,100);

零填充的意义(显示宽度)：保证数据格式

小数型

小整型：带有小数点或者超出整型的数值类型。

SQL中：将小数型细分为两种:浮点型和定点型

浮点型：小数点浮动，精度有限，而且会丢失精度

定点型：小数点固定，精度固定，不会丢失精度

浮点型数据是一种精度型数据：因为超出指定范围之后，会丢失精度(自动四舍五入)

浮点型：理论分为两种精度

Float：单精度，占用4个字节存储数据，精度范围大概为7位左右

Double：双精度，占用8个字节存储数据，精度方位大概为15位左右

创建浮点数表：浮点的使用方式，直接float表示没有小数部分，float(MD)：M代表总长度D代表小数部分长度，整数部分长度为M-D

浮点表

Create table my_float(

F1 float,

F2 float(10,2), -- 10 位在精度范围之外

F3 float(6,2) -- 6 位在精度范围之内

)charset utf8；

插入数据：可以是直接小数，也可以是科学计数法

-- 插入数据

Insert into my_float values (1000.10,1000.10,100.10); -- 符合条件

Insert into my_float values (1234567890,12345678.90,1234.56); -- 符合条件

Insert into my_float values (3e38,01e7,1234.56);

Insert into my_float values (999999,999999.99,999.99) -- 最大值

浮点型数据的插入：整型部分是不能超出长度的，但是小数部分可以超出长度(系统会自动四舍五入)

Insert into my_float values (123456,1234.123456789,123.987654321); -- 小数部分

Insert into my_float values (123456,1234.12,12345.56); -- 整数部分超出

数据结果：浮点数一定会进行四舍五入(超出精度范围)：浮点数如果是因为系统进位导致整数部分超出指定的长度，那么系统也是允许的。

定点型

定点型：绝对的保证整数部分不会被四舍五入(不会丢失精度)，小数部分有可能(理论小数部分不会丢失精度)

创建定点数表：以浮点数作为对比

-- 创建定点数

Create table my_decimal(

F1 float(10,2);

D1 decimal (10,2)

)charset utf8;

插入数据：定点数的整数部分一定不能超出长度(进位不可以)，小数部分的长度可以随意操作(系统自动四舍五入)

-- 插入数据

Insert into my_decimal values (12345678.90,12345678.90); -- 有效数据

Insert into my_decimal values(1234.123456,1234.1234356); -- 小数部分超出

浮点数如果进位导致长度溢出没有问题，但是定点数不行

Insert into my_decimal values (99999999.99,9999999.99); -- 没有问题

Insert into my_decimal values (99999999.99,9999999.999); -- 进位超出范围

查看数据效果

时间日期类型

Datetime 时间日期 格式是 YYYY-mm-dd HH：ii：ss表示的范围从1000到9999年都有0值

0000-00-00 00:00:00

Data ：日期就是datatime中的data部分

Time：时间(段)：指定的某个区间之间， 时间到时间

Timestamp ：时间戳，并不是时间戳，只是从1970年开始的YYYY-mm-dd HH：ii：ss格式与datatime完全一致

Year：年份 两种形式year(2)和year(4)

-- 创建时间日期表

Create table my_data(

D1 datetime,

D2 date,

D3 time,

D4 timestamp,

D5 year

)charset utf8;

插入数据：时间time可以是负数，而且可以是很大的负数。Year可以使用2位数插入，也可以使用4位数

-- 插入数据

Insert into my_data values (‘2015-9-28 11:50:36’,’2015-9-28’,’11:50:36’,‘2015-9-28 11:50:36’,2015);

-- 时间使用负数

Insert into my_data values (‘2015-9-28 11:50:36’,’2015-9-28’,’-11:50:36’,‘2015-9-28 11:50:36’,2015);

Insert into my_data values (‘2015-9-28 11:50:36’,’2015-9-28’,’-211:50:36’,‘2015-9-28 11:50:36’,2015);

Insert into my_data values (‘2015-9-28 11:50:36’,’2015-9-28’,’-2 11:50:36’,‘2015-9-28 11:50:36’,2015); -- 2过去两天；48

-- year 可以使用2 位数或者4位数

Insert into my_data values (‘2015-9-28 11:50:36’,’2015-9-28’,’11:50:36’,‘2015-9-28 11:50:36’,69);

Insert into my_data values (‘2015-9-28 11:50:36’,’2015-9-28’,’11:50:36’,‘2015-9-28 11:50:36’,70);

Timestamp 字段： 只要当前所在的记录被更新，该字段一定会自动更新成当前时间。

-- timestamp ： 修改记录

Update my_data set d1 = ‘2015-09-11 11:11:11’ where year = 2069;

字符串类型

在SQL中，将字符串类型分成了6类：char varchar text blob enum 和 set

Char类型 定长字符串

在磁盘(二维表)在定义结构的时候就已经确定了数据的存储长度

Char(L)：L代表length 可以存储的长度，单位为字符，最大的长度可以为255；

Char(4)：在utf8下需要4*3=12个字节

变长字符串：varchar 在分配空间的时候，按照最大的空间分配：但是实际上最终用了最终根据具体放入数据来确定

Varchar(L)L代表字符长度，理论长度是65536个字符，但是会多出，1到2个字节来确定存储的实际长度，但实际上如果实际长度超过255，既不用定长也不用变长，使用文本字符串text

Varchar(10)的确存了10个汉字，utf8环境103+1=31

存储了3个汉字 33+1=10字节

定长与变长的存储实际空间(utf8)

实际存储数据Char(4)Varchar(4)Char占用字节Varchar(占用)

ABCDABCDABCD43=1243+1=13

AAA43=1213+1=4

ABCDE× ×数据超过长度数据超过长度

如何选择定长或者变长字符串呢？

定长的磁盘空间比较浪费，但是效率高；如果数据基本上确定长度都一样，就使用定长

如：身份证，手机号等

变长的磁盘空间比较节省，但是效率低；如果数据不能确定长度(不同数据有变化)如姓名，地址等

文本字符串

如果数据量非常大，通常说超过255个字符就会使用文本字符串

文本字符串根据存储的数据的格式进行：text和blob

Text:存储文字(二进制数据实际实际上都是存储路径)

Blob：存储二进制数据(通常不用)

枚举字符串

枚举：enum 事先将所有可能出现的结果都设计好，实际上存储的数据必须是规定好的数据中的一个

枚举的使用方式

定义：enum(可能出现的元素列表)；如enum(‘男’‘女’‘保密’)

使用：存储数据，只能存储上面定义好的数据

-- 创建枚举表

Create table my_enum(

Gender enum(‘男’,’女’,’保密’)

)charset utf8;

加入数据：作用之一：规范数据格式：数据只能是规定的数据中的一个

-- 插入数据

Insert into my_enum values (‘男’),(‘保密’);

-- 错误数据

Insert into my_enum values(‘female’);

作用之二：节省存储空间(枚举有一个别名)枚举实际存储的是数值不是字符串本身。

在mysql中系统也是自动转换数据格式的，证明字段存储的数据是数值：

将护具取出来+0就可以判断出原来的数据存的到底是字符串还是数值：如果是字符串最终结果永远为0否则就是其他值

-- 将字段结果取出来进行+0运算

Select gender + 0 , gender from my_enum;

找出了枚举元素的实际规律，按照元素出现的顺序，从1开始编号

枚举原理：枚举在进行数据规范的时候(定义的时候)系统会自动建立一个数字与枚举元素的对应关系(关系放到日志中)：然后在进行数据插入的时候，系统自动将字符串换成对应的数字存储，然后在进行数据提取的时候，系统自动减数值换成对应的字符串显示

因为枚举实际存储的是数值，所以可以直接插入数值

-- 数值插入

Insert into my_enum values (1),(2);

集合字符串

集合跟枚举很类似，实际存储的是数值，而不是字符串

集合使用方式：

定义：set(元素列表)

使用：可以使用元素列表中的元素(多个)，使用逗号分隔

-- 创建集合表

Create table my_set(

Hobby set (‘篮球’,’足球,’乒乓球’,’羽毛球’,’排球’,’台球’,’网球’,’棒球’)

-- 足球 台球 网球

-- 集合中：每一个元素都是对应的一个二进制位，被选中位1，没有则为0，最后反过来

-- 0 1 0 0 0 1 1 0

-- 反过来 01100010=98

)charset utf8;

插入数据可以使用多个元素字符串组合，也可以直接插入数值

-- 插入数据

Insert into my_set values (‘足球,台球,网球’);

Insert into my_set values(3); -- 3=1+2=篮球+足球

查看数据：数值+数据查看

-- 查看集合数据

Select hobby + 0 , hobby from my_set;

集合中每一个元素对应的就是一个二进制位

集合中元素的顺序都没有关系，因为系统会自动匹配顺序

-- 颠倒元素顺序的插入

集合的强大在于能够规范数据和节省空间

Mysql记录长度

Mysql中规定：任何一条记录最长不超过不超过65535个字节，(varchar永远达不到理论值)

Varchar实际存储长度能达到多少呢？看字符集编码

Utf8下的varchar的实际顶配

21844字符

Gbk下的varchar的实际顶配

32766字符

-- 求出 varchar 在utf8和gbk下的实际最大值

Create table my_utf8(

Name varchar(65535)

)charset utf8;

Create table my_gbk(

Name varchar(65535)

)charset gbk;

Create table my_utf8(

Name varchar(21844) -- 21844*3 +2 =65535 +2 = 65534

)charset utf8;

Create table my_gbk(

Name varchar(32766) -- 327676*2 +2 = 65532+2=65534

)charset gbk;

想用完整的65535个字节长度

增加tinyint字段

Create table my_utf81(

Age tinyint, -- 1

Name varchar(21844) -- 21844*3 +2 =65535 +2 = 65534

)charset utf8;

Create table my_gbk1(

Age tinyint, -- 2

Name varchar(32766) -- 327676*2 +2 = 65532+2=65534

)charset gbk;

Mysql 记录中：如果有任何一个字段允许为空，那么系统会自动从整个记录中保留一个字节来存储NULL，多条记录字段也是一个字节来存储NULL(若想释放NULL所占用的字节，必须保证所有的字段都不允许为空)

-- 释放NULL

Create table my_utf82(

Age tinyint not NULL, -- 1

Name varchar(21844) not NULL -- 21844*3 +2 =65535 +2 = 65534

)charset utf8;

Create table my_gbk2(

Age tinyint not NULL, -- 2

Name varchar(32766) not NULL -- 327676*2 +2 = 65532+2=65534

)charset gbk;

Mysql中text文本字符串，不占用记录长度，额外存储，但是text文本字符串也是属于记录的一般部分：一定需要占据记录的部分长度：10个字节(保存数据的地址以及长度)

-- text 占用十个字节长度

Create table my_utf83(

Name varchar(21841) not NULL,

Content text not NULL

)charset utf8;

列属性

列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要一些额外的约束，来更加保证数据的合法性

列属性有很多：NULL NOT NULL ，default，primary key ， unique key，auto_increment,comment

空属性

两个值：NULL，NOT NULL

虽然默认的，数据库基本字段都为空，但是实际上在真实开发的时候，尽可能的要保证所有的数据都不应该为空；空数据没有意义，空数据没有办法参与运算

-- 创建一个实际案例表：班级表(名字，教室)

Create table my_class(

Name varchar(20) NOT NULL,

Room varchar(20) NULL

)charset utf8;

列描述

列描述：comment，描述，没有实际意义，是专门用来描述字段，会根据表创建语句保存；用来给程序员(数据库管理员)来进行了解的

-- 创建表

Create table my_teacher(

Name varchar(20) not null comment ‘姓名’,

Money decimal(10,2) not null comment ‘工资’

)charset utf8;

默认值

默认值：某一种数据会经常性的出现某个具体的值，可以在一开始就指定好；在需要真实数据的时候，用户可以选择性的不填，

-- 默认值

Create table my_default(

Name varchar(20) not null,

Age tinyint unsigned default 0,

Gender enum(‘男’,’女’,’保密’) default ‘男’

)charset utf8;

默认值的生效，使用，在数据进行插入的时候，不给字段赋值；

-- 插入数据

Insert into my_default (name) values(‘高强’);

想要使用默认值，可以不一定去指定列表，故意不使用字段列表，可以使用default关键字代替值

Insert into my_default values(‘饭’,18,default);

字段属性

主键

主键：primary key 主要的键 一张表中只有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复，这种称之为主键

一张表只能最多一个主键

增加主键

SQL操作中有多种方式增加主键大体分为三种

1.在创建表的时候直接在字段之后跟primary key关键字(主键本身不允许为空)

-- 增加主键

Create table my_pri1(

Name varchar(20) not null comment ‘姓名’,

Number char(10) primary key comment ‘学号’

)charset utf8;

优点：非常直接：缺点：只能使用一个字段作为主键

2.在创建表的时候，在所有字段之后，使用 primary key(主键列表)，来创建主键(如果有多个字段作为主键，可以是复合主键)

-- 复合主键

Create table my_pri2

(

Number char(20) comment ‘学号’,

Course char(10) comment ‘课程代码’,

Score tinyint unsigned default 60 comment ‘成绩’,

-- 增加主键限制 ： 学号和课程号应该是对应的，具有唯一性

Primary key(number,course)

)charset utf8;

3.当表已经创建好之后，再次额外的追加主键，可以通过修改表字段属性，也可以直接追加。

alter table 表名 add primary key (字段列表)；

-- 追加主键

Create table my_pri3

(

Course char(10) not null comment ‘课程编号’,

Name varchar(10) not null comment ‘课程名字’

)charset utf8;

Alter table my_pri3 modify course char(10) primary key comment ‘课程编号’；

Alter table my_pri3 add primary key(course);

前提：表中字段对应的数据本身是独立的(不重复)

主键约束

主键对应的字段中的数据不允许重复，一旦重复数据操作失败(增和改)

-- 向pri1插入数据

Insert into my_pri1 values (

‘古天乐’,’itcast0001’

),(‘刘德华’,’itcast0002’);

Insert into mypri2 values (‘itcast0001’,’39010001’,90),(‘itcast0001’,’39010002’,’85’),(‘itcast0002’,’39010001’,’90’);

-- 主键冲突(重复)

Insert into my_pri1 values (‘刘辉’,’itcast0002’); -- 不可以：主键冲突

Insert into my_pri2 values (‘itcast0001’,’39010001’,100); -- 不可以；冲突

主键更新 &删除主键

没有办法更新主键：主键必须先删除，才能增加

Alter table 表名 drop primary key；

-- 删除主键

Alter table my_pri1 drop primary key ;

主键分类

实际创建表的过程中，很少使用真实业务数据作为主键字段(业务主键，如学号，课程号)；大部分的时候是使用逻辑性的字段(字段没有业务含义，值是什么都没有关系)，将这种字段主键称之为逻辑主键。

Create table my_student(

Id int primary key auto_increment comment ‘逻辑增长：自增长’, --逻辑主键

Number char(10) not null comment ‘学号’,

Name varchar (10) not null

);

自动增长

自增长，当对应的字段，不给治或者说给默认值，或者给null的时候，会自动的被系统出发系统会从单签字段中已有的最大值再进行+1操作得到一个新的不同的字段

自增长通常是跟主键搭配

自增长特点 ：auto_increment

1.任何一个字段要做自增长必须前提是本身是一个索引(key一栏有值)

2.自增长字段必须是数字(整型)

3.一张表最多只能有一个自增长

-- 自增长

Create table my_auto

(

Id int primary key auto_increment comment ‘自动增长’,

Name varchar (10) not null

)charset utf8;

自增长使用

当自增长被给定为null或者默认值的时候会出发自增长

-- 出发自增长

Insert into my_auto(name) values (‘小猪猪’);

Insert into my_auto values (null,’猪’);

Insert into my_auto values (default,’张三’);

自增长的开始值默认是一，每次加一

自增长如果对应的字段输入了值，那么自增长失效，但是下次还是能够正确的自增长(从最大值+1)

如何确定下一次是什么时候自增长呢?可以通过查看表创建语句看到

修改自增长

自增长如果是涉及到字段改变：必须先删除自增长，后增加(一张表只能有一个自增长)

修改当前自增长已经存在的值：修改只能比当前已有的自增的最大值大，不能小(小不生效)

Alter table 表名 auto_increment = 值;

-- 修改表选项的值

Alter table my_auto auto_increment = 4; -- 向下修改

Alter table my_auto auto_increment = 10 ; -- 向上修改

向上修改才生效

为什么自增长是从1开始，为什么每次都是自增1呢？

所有系统的表现如字符集，校对集，都是由系统的内部的变量进行控制的，

查看自增长对应的变量 show variables like ‘auto_increment%’;

可以修改变量实现不同的效果，修改是对整个数据修改，而不是单张表：(修改是会话级)

Set auto_increment_increment=5; -- 一次自增5

删除自增长

自增长是字段的一个属性，可以通过modify来进行修改(保证字段没有auto_increment即可)

Alter table 表名 modify 字段 类型

-- 删除自增长

Alter table my_auto modify id int primary key; -- 错误：主键理论是单独存在的

Alter table my_auto modify id int; -- 有主键的时候不需要再加主键了

唯一键

一张表中有很多字段需要具有唯一性，数据不能重复：但是一张表中只有一个主键：唯一键(unique key )就可以解决表中有多个字段需要唯一性约束问题

唯一键的本质与主键差不多，唯一键默认的允许自动为空。而且可以多个为空(空字段不参与唯一性比较)

增加唯一性

基本与主键差不多的三种方案

方案1：创建表的时候，字段之后直接跟 unique unique key

-- 创建表

Create table mu_unique1(

Number char(10) unique ,

Name varchar(20) not null

)charset utf8;

方案2：在所有的字段之后增加unique key (字段列表)-- 复合唯一键

Create table my_unique2(

Number char(10) not null,

Name varchar(20) not null,

Unique key(name)

)charset utf8;

方案3：在创建表之后创建为唯一键

Create table my_unique3(

Id int primary key auto_increment,

Number char(10) not null

)charset utf8;

-- 追加唯一键

Alter table my_unique3 add unique key (number);

唯一键约束

唯一键与主键本质相同，唯一区别就是唯一键默认允许为空，而且是多个为空

-- 插入数据

Insert into mu_unique1 values(null,’增光’),(‘itcast0001’,星星’),(‘’null,’帅帅’);

如果唯一键不允许为空，与主键的约束作用是一致的

更新唯一键和删除唯一键

先 删除后新增(唯一键可以有多个，可以不删除)

删除唯一键

Alter table 表名 drop unique key; -- 错误：唯一键有多个

Alter table 表名 drop index 索引名字 -- 唯一键默认的使用字段名作为索引名字

-- 删除唯一键

Alter table my_unique3 drop index number;

索引

几乎所有的索引都建立在字段上

索引：系统根据某种算法，将已有的数据(未来可能新增的数据)，单独建立一个文件：文件能够实现快速的匹配数据，并且能够快速的找到对应表中的记录

索引的意义：

1.提升查询数据的效率

2.约束数据的有效性(唯一性等)

增加索引的前提条件：索引本身会产生索引文件(有时候有可能比数据文件还大)会非常耗费磁盘空间

如果某个字段需要作为查询条件经常使用，那么可以使用索引(一定会想办法增加)

如果某个字段需要进行数据的有效性约束，也可能使用索引(主键，唯一键)

Mysql的多种索引

1.主键索引 primary key

2.唯一索引 unique key

3.全文索引 fulltext index

4.普通索引 index

全文索引：针对文章内部的关键字进行索引

全文索引最大的问题：在于如何确定关键字

英文很容易：英文单词与单词之间有空间

中文很难：没有空格，而且中文可以各种随意组合(分词，sphinx)

关系

将实体与实体的关系，反应到最终数据库表的设计上，将关系分为三种：一对一，一对多(多对一)和多对多，所有的关系都是表与表之间的关系

一对一

一对一：一张表的一条记录只能与另外一条记录进行对应，反之亦然

学生表：姓名，性别，年龄，体重，身高，婚姻状况，籍贯，家庭地址，紧急联系人

Id(P)姓名性别年龄身高婚姻状况籍贯家庭地址紧急联系人体重

表设计成以上这种形式：符合要求，其中姓名，性别，年龄，身高体重属于常用数据，但是婚姻籍贯住址联系人属于不常用的数据，如果每次查询所有数据，不常用的数据就会影响效率，实际又不用

解决方案：将常用和不常用的信息分离存储，分成两张表

常用信息表

Id(P)姓名性别年龄身高体重

不常用信息表，保证不常用信息与常用信息一定能够对应上：找一个具有唯一性(确定记录)的字段来共同连接两张表

婚姻籍贯住址联系人Id(p)

一个常用表中的一条记录，永远只能在一张不常用表匹配一条记录，反过来一个不常用表中的一条记录在常用表中也只能匹配一条记录，一对一的关系。

一对多

一对多：一张表中的一条记录可以对应另外一张表中的多条记录，但是返过来，另外一张表中的一条记录只能对应第一张表的一条记录，这种关系就是一对多或者多对一的关系

母亲与孩子的关系：母亲与孩子两个实体

母亲表

Id(P)名字年龄性别

孩子表

Id(P)名字年龄性别妈妈Id(P)

妈妈表主键

以上关系，一个妈妈可以在孩子表中找到多条记录，或者一条，但是一个孩子只能一个妈妈，是一种典型的一对多的关系

但是以上设计解决了实体设计表问题，但是没有解决关系问题，孩子找不出妈妈，妈妈也找不到孩子。

解决方案：在某一张表中增加一个字段，能够找到另一张表中的记录；应该在孩子表中增加一个字段指向妈妈表；因为孩子表的记录只能匹配到一条妈妈表的记录

多对多

一张表中(A)的一条记录能够对应另外一张表(B)中的多条记录，同时B表找中的一条记录也能对应A表中的多条记录，多对多的关系

老师教学：老师和学生

老师表

T_ID(p)姓名性别

1A男

2B女

学生表

S_ID(p)姓名性别

1张三男

2小芳女

中间关系表：老师与学生的关系

IDT_ID(老师)S_ID(学生)

1A张三

2B小芳

3B张三

以上设计方案：实现了实体的设计，但是没有维护实体的关系

一个老师教过多个学生，一个学生也被多个老师教过

解决方案：在学生表中增加老师字段；不管在哪张表中都会出现一个问题，该字段要保存多个数据，而且是与其他表有关系的字段，不符合表的设计规范，增加一张新表，专门维护两张表之间的关系

增加中间表之后：中间表与老师表形成了一对多的关系：而且中间表是多表，维护了能够唯一找到一表的关系；同样的学生表与中间表也是一对多的关系，一对多的关系可以匹配到关联表之间的数据

学生找老师：找出学生id->中间表找出匹配记录(多条)->老师表匹配(一条)

老师找学生：找出老师id->中间表找出匹配记录(多条)->学生表匹配(一条)

范式

Normal format，是一种离散数学中都知识，是为了解决一种数据的存储与优化的问题，保证数据的存储之后，凡是能够通过关系寻找出来的数据，坚决不再重复存储，其终极目标是为了减少数据的冗余。

范式：是一种分层结构的规范，分为六层：每一层都比上一层更加严格，若要满足下一层范式，前提示满足上一层范式

六层范式 ：1NF，2NF，3NF.....6NF,1NF是最底层，要求最低6NF最高层最严格

Mysql属于关系型数据库，有空间浪费，也是致力于节省存储空间，与范式所有解决的问题不谋而合，在设计数据库的时候，会利用范式来指导设计。

但是数据库不但是要解决空间问题，要保证效率问题，范式只是为了解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现：一半情况下，只有前三种范式需要满足

范式在数据库的设计当中是有指导意义：但是不是强制规范

1NF

第一范式，在设计表存储数据的时候，如果表中设计的字段存储的数据，在取出来使用之前，还需要额外的处理(拆分)，那么说表的设计部满足第一范式，第一范式要求字段的数据具有原子性：不可再分。

上表设计不存在问题：但是如果需求是将数据查出来之后，要求显示一个老师从什么时候山课到什么时候结课，需要将代课时间进行拆分：不符合1NF，数据不具有原子性，可以在拆分

解决方案，将代课时间分为两个字段就能解决问题

2NF

第二范式：在数据表设计的过程中，如果有复合主键(多字段主键)，且表中有字段并不是有整个主键来确定，而是依赖主键的某个字段(主键的部分)，存在字段历来主键的部分的问题，称之为部分依赖，第二范式就是要解决表设计不允许出现部分依赖

以上表中，因为讲师无法作为独立主键，需要集合班级才能作为主键(复合主键：一个老师在一个班永远只带一个阶段的课)；代课时间，开始和结束字段都与当前的代课主键(讲师和班级)；但是性别并不依赖班级，教师不依赖讲师；性别只依赖讲师，教师只依赖班级，出现了性别和教师依赖主键中的一部分，部分依赖，不符合第二范式

解决方案：1.可以将性别与讲师单独单独成表，班级和教室也单独成表；

解决方案：2取消复合主键，使用逻辑主键

Id =讲师+班级

3NF

要满足第三范式，必须满足第二范式

第三范式，理论上讲，应该一张表中的所有字段都应该直接依赖主键(逻辑主键：代表的是业务主键。无传递依赖)，如果表设计中存在一个字段，并直接依赖主键，而是通过某个非主键字段依赖，最终实现依赖主键，把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖。

讲师带课表

以上设计方案中：性别依赖讲师存在，讲师依赖主键，教师依赖班级，班级依赖主键，性别和教师都存在传递依赖。

解决方案：将存在传递依赖的字段，以及依赖的字段本身单独取出，形成一个单独的表，然后在需要对应的信息的时候，使用对应的实体表的主键加进来。

讲师代课表

讲师表 班级表

讲师表：id=讲师 班级表中：id = 班级

逆规范化

有时候，在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲，的确可以获取到想要的数据，但是就是效率低一点，会刻意的在某些表中，不去保存另外一张表的主键(逻辑主键)而是直接保存想要的数据信息，这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询(效率低)，但是会导致数据冗余

如讲师代课信息表

逆规范化：磁盘利用率与效率的对抗

数据高级操作

数据操作：增删改查

新增数据

标准语法

Insert into 表名 [(字段列表)]values(值列表)；

在数据插入的时候，假设主键对应的值已经存在：插入一定会失败！

主键冲突

当主键存在冲突的时候(duplicate key)可以选择性的进行处理，更新和替换

主键冲突：更新操作

Insert into 表名[(字段列表：包含主键)] values(值列表)on duplicate key update 字段=新值

-- 插入数据

Insert into my_class values (‘PHP0810’,’B205’);

Insert into my_class values (‘PHP0810’,’B203’);

-- 主键冲突：更新

Insert into my_class values (‘PHP0810’,’B203’) on duplicate key update room = ‘B203’

主键冲突：替换

Insert into 表名 [(字段列表：包含主键)] values (值列表)

-- 主键冲突 ：替换

Replace into my_class values (‘PHP0710’,’A203’);

蠕虫复制

从已有的数据中获取数据，然后将数据有进行新增操作：数据成倍增加。

表创建的高级操作，从已有已有表创建新表(复制表结构)

Create table 表名 like 数据库.表名;

-- 复制创建表

Create table my_copy like my_gbk;

蠕虫复制：先查出数据，然后将查出的数据新增一遍

Insert into 表名 [(字段列表)] select 字段列表 /* from 数据表名

-- 蠕虫复制

Insert into my_copy select * from my_collate_ci;

Insert into my_copy select * from my_copy;

蠕虫复制的意义：

1.从已有表拷贝数据到新表中；

2.可以迅速的让表中的数据膨胀到一定的数量级：测试表的压力以及效率；

高级更新数据

基本语法

Update 表名 set 字段 = 值 [where条件];

高级新增语法

Update 表名 set 字段 = 值[where 条件][limit 更新数量];

-- 更新部分a》c

Update my_copy set name = ‘c’ where name = ‘a’ limit 3;

删除数据

与更新数据类似：可以通过limit来限制数量

Delete from 表名 [where 条件] [limit 数量]

-- 删除数据：限制记录数为10

Delete from my_copy where name =’b’ limit 10;

删除：如果表中存在主键自增长。那么当删除之后，自增长不会还原。

思路：数据的删除是不会改变表结构。只能删除表之后重建表

Truncate 表名; -- 先删除该表，后新增该表。

-- 清空表：重置自增长

Truncate my_auto;

查询数据

基本语法

Select 字段列表/* from表名 [where 条件]

Select 选项

Select [select 选项] 字段列表 [字段别名]/* from 数据源 [where 条件子句]

[group by 子句] [having 子句][Oder by子句][limit 子句];

All：默认的，保留所有的结构

Distinct ：去重，查出来的结果。将重复给去除(所有字段都相同)

-- select 选项

Select * from my_copy;

Select all * from my_copy;

-- 去重

Select distinct * from my_copy;

-- 去重只能全部去重，是查出来之后再去重。

字段别名

字段别名：当数据查询出来的时候，有时候名字并不一定满足需求(多表查询的时候会有同名字段)，需要对字段进行重命名，别名

语法

字段名 [as]别名；

-- 字段别名

Select id,number as 学号,sex 性别from my_student;

数据源

数据源：数据的来源，关系型数据库的来源都是数据表：本质上只要保证数据类似二维表，最终都可以以作为数据源。

数据源分为多种：单表数据源，多表数据源，查询语句

单表数据源：select * from 表名；

多表数据源： select * from 表名1，表名2.....;

-- 多表数据源

Select * from my_student ,my_class;

从一张表中取出一张表中，其另外一张表中匹配所有记录，而且全部保留(记录数和字段数)

将这种结果称之为：笛卡尔积(交叉连接)：笛卡尔积没什么乱用，所以应该尽量避免。

子查询：数据的来源是一条查询语句(查询语句的结果是二维表)

Select * from (select 语句) as 表名;

-- 子查询

Select * from (select * from my_student) as s;

Where子句

Where子句：用来判断数据，筛选数据

Where子句返回的结果：0或者1代表 false1代表true.

判断条件：运算符：>,=,!=,<>,=,like,between and,in/not in

逻辑运算符：&&(and)(or)！(not)

Where原理：是唯一直接从磁盘获取数据时候就开始判断的条件：ongoing磁盘取出一条记录，开始进行where判断，判断的结果如果成立保存到内存，如果失败就直接放弃。

-- 增加age和height字段

Alter table my_student add age tinyint unsigned;

Alter table my_student add height tinyint unsigned;

-- 增加值：rand取得一个0到1之间的随机数,floor,向下取整

Update my_student set age=floor(rand()20+20),height = floor(rand()20 +170);

条件查询1：要求找出学生id为1或者3或者5的学生

-- 找出学生id为1或者3或者5的学生

Select * from my_student where id =1 || id=3 || id=5;

Select * from my_student where id in (1,3,5); -- 落在集合中

条件查询2：查出区间落在170，180身高之间的学生

-- 找出身高在170到180之间的学生

Select * from my_student where height >= 170 and height <=180;

Select * from my_student where height between 170 and 180;

Select * from my_student where height between 180 and 170;

Between 本身是闭区间，between左边的值必须小于或者等于右边的值

Select * from my_student where 1; -- 所有条件都满足

Group by 子句

分组的意思，根据某个字段分组(相同的放一组，不同的分到不同一组)

基本语法：

Group by 某个字段

-- 根据性别分组

Select * from my_student group by sex;

分组的意义是为了统计数据(按组统计按分组字段进行数据统计)

SQL提供了一系列的统计函数

Count()；统计分组后的记录数，每一组有多少记录数

Max()：统计每组中的最大值

MIN()：统计最小值

AVG()：统计平均值

SUM：统计和

-- 分组统计：身高高矮，年龄平均，总年龄

Select sex ,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex;

Count 函数：里面可以使用两种参数，*代表统计记录，字段名代表统计对应的字段(null不统计)

分组会自动排序：根据分组字段：默认升序

Group by 字段 [asc|desc]； -- 对分组的结果然后合并之后的整个结果进行排序

-- 分组统计：身高高矮，年龄平均，总年龄

Select sex ,count(*),max(height),min(height),avg(age),sum(age) from my_student group by sex desc;

多字段分组：先根据一个字段进行分组，然后对分组后的结果再次按照其他字段进行分组

-- 多字段分组：先班级后男女

Select c_id ,sex ,count(*) from my_student group by c_id ,sex ; -- 多字段排序

有一个函数：可以对分组的结果中的某字段，进行字符串连接(保留该组所有的某个字段)；

Group_concat(字段)

-- 多字段分组：先班级后男女

Select c_id ,sex ,count(*) , group_concat(name) from my_student group by c_id ,sex ; -- 多字段排序

回溯统计:with rollup;

任何一个分组后的结果都会有一个小组，最后都需要向上级分组进行汇报统计：根据当前分组的字段 。这就是回溯统计，回溯统计的时候会将分组字段置空

-- 统计

Select c_id ,count() from my_student group by c_id;

-- 回溯统计

Select c_id ,count() from my_student group by c_id with rollup;

-- 多字段分组回溯统计

Select c_id ,sex ,count() , group_concat(name) from my_student group by c_id ,sex ;

Select c_id ,sex ,count() , group_concat(name) from my_student group by c_id ,sex with rollup ;

多字段回溯：考虑第一层分组会有一次回溯，第二次分组要看第一次分组的组数，组数是多少，回溯就是多少，然后加上第一次回溯会就可。

Having 子句

Having 子句：与where子句一样：进行条件判断

Where 是针对磁盘数据进行判断；进入到内存之后，会进行分组操作，分组结果就需要having在处理了。

Having能做where能做的几乎所有事情，但是where 却不能做having能做的很多事情

1.分组统计的结果或者统计函数都只有having能做

-- 求出所有班班级人数大于等于2的学生

Select c_id,count() from my_student group by c_id having count() >= 2;

分组是在where查询之后，所有要操作分组之后的数据就只能有having

2.Having 能使用字段别名，where则不能；where是从磁盘取数据，而名字只能是字段名，别名是在取出数据之后

-- 求出所有班班级人数大于等于2的学生

Select c_id,count(*) as total from my_student group by c_id having total >= 2;

Where，不管怎么样都不能使用别名。

Order by子句

Orderby ：排序，根据某个字段进行升序或者降序排序，依赖校对集

使用语法

Order by 字段名[asc|desc]; --asc是升序是默认的，desc是降序

-- 排序

Select * from my_student group by c_id;

Select * from my_student order by c_id;

分组是为了统计，排序是为了排序。

排序可以进行多字段排序：先根据某个字段进行排序，然后排序好的内部，再按照某个数据进行再次排序；

-- 多字段排序：先班级后性别

Select * from my_student order by c_id ,sex desc;

Limit 子句

是一种限制结果的语句；限制数量

Limit 有两种使用方式

方案1：只用来限制长度(数据量)：limit 数据量;

-- 查询学生：前两个

Select * from my_student limit 2;

方案2：限制起始位置，限制数量：limit 起始位置，长度；

-- 查询学生：前两个

Select * from my_student limit 0,2; -- 记录数是从0开始编号

Select * from my_student limit 2,2; -- 记录数是从2开始编号

Select * from my_student limit 1,2; -- 记录数是从1开始编号

Limit方案2主要是用来实现数据的分页；为用户节省时间，提高服务器的响应效率，减少资源的浪费。

对用户来说，可以点击的分页按钮：1,2,3....

对于服务器来说，根据用户选择的页码来获取不同的数据：limit offset，length

分页功能：

Length：每页显示的数据量：基本不变

Offset：offset=(页码-1)*每页显示量

连接查询

需求：要求查出所有的学生，而且显示学生所在的班级信息。

连接查询：多张表(可以大于两张)进行记录的连接(按照某个指定的条件进行数据拼接)；

最终结果：记录数有可能变化，字段数一定会增加(至少两张表的合并)

连接查询的意义：在用户查看数据的时候，需要显示的数据来自多张表。

连接查询：join 使用方式：左表 join 右表

左表：在join关键字左边的表

右表：在join关键字右边的表

连接查询分类

SQL将连接查询分为四类：内连接，外连接，自然连接和交叉连接。

交叉连接

交叉连接：cross join，从一张表中循环取出每一条记录，每一条记录都去另一张表中进行匹配，匹配一定保留(没有条件匹配)，而连接本身字段就会增加，所以导致最终的结果叫做，笛卡尔积

基本语法：左表 cross join 右表 =====from左表，右表

-- 交叉连接

Select * from my_student cross join my_class;

-- my_student cross join my_class; 是数据源，先连接后查

笛卡尔积没有意义：就应该尽量避免(交叉连接没用)

交叉连接存在的价值：保证连接这种结构的完整性

内连接

内连接： [inner] join ，从左表中取出每一条记录，去右表中的所有的记录进行匹配，匹配必须是某个条件在左表中与右边中相同才会保留结果，否则不保留

基本语法

左表 [inner] join 右表 on 左表.字段 = 右表.字段； on表示连接条件：条件字段就是代表相同的业务含义(如my_student.c_id和my_class.id)

-- 内连接

Select * from my_student inner join My_class on my_student.c_id = my_class.id;

Select * from my_student inner join My_class on c_id = my_class.id;

Select * from my_student inner join My_class on c_id = id; -- 两张表都有id字段

字段别名以及表别名的使用：在查询数据的时候，不同表有同名字段，这个时候需要加上表名才能区分而表明太长，通常使用别名

-- 字段和表别名

Select s.*,c.name as 课程名,c.room 教室号 from my_student s inner join My_class c on s.c_id = c.id;

数据源在查询之前，null不能匹配

内连接可以没有连接条件：没有on 之后的内容，这个时候系统会保留所有结果，会形成层笛卡尔积

内连接还可以使用where代替on

-- 字段和表别名

Select s.*,c.name as 课程名,c.room 教室号 from my_student s inner join My_class c where s.c_id = c.id;

通常不用where，但是没有on的效率高。

外连接

外连接：outer join ，以某张表为主，取出里面所有记录，然后每条与另外一条表进行连接：不管能不能匹配上条件，最终都会保留：能匹配，正确保留；不能匹配，其他表的字段都置空NULL;

外连接分为两种：是以某张表为主，有主表

Left join ：左外连接，以左表为主表

Right join ：右外连接，以右表为主表

基本语法：左表 left/right join 右表 on 左表.字段 = 右表.字段;

-- 左连接

Select s.*,c.name as 课程名,c.room 教室号 from my_student s left join My_class c on s.c_id = c.id;

最终记录数至少不少于左表已有的记录数

副表中没有可以匹配的记录：最终被置空(id=5的记录)

右连接：

-- 左连接

Select s.*,c.name as 课程名,c.room 教室号 from my_student s right join My_class c on s.c_id = c.id;

虽然左右连接会有主副关系，但是显示的结果：永远左表的数据在左边，右表数据在右边

左连接和右连接可以互相转换。

自然连接

自然连接： natural join ，自然连接，就是自动匹配连接条件：系统以字段名字作为匹配模式(同名字段作为匹配条件，多个同名字段都作为条件)。

自然连接：可以分为自然内连接，和自然外连接。

自然内连接：左表 natural join 右表

-- 自然内连接

Select * from my_student natural join my_class;

自然外连接：左表 natural left/right 右表;

-- 自然外连接

Select * from my_student natural left join my_class;

其实，对应的内连接和外连接都可以模拟自然连接，使用同名连接，合并字段

左表 left/right/inner/join 右表 using(字段名)； -- 使用同名字段作为连接条件；

-- 外连接模拟自然外连接 ：using

外键

外键：foreign key，外面的键(键不在自己表中)，如果一张表中有一个字段(非主键)指向另外一张表的主键，那么将该字段称之为外键，

增加外键

外键可以在创建表的时候或者创建表之后增加。(但是要考虑数据的问题)

一张表可以有多个外键

在创建表的时候增加外键：在所有表的字段之后，使用foreign key(外键字段)references 外部表(主键字段)

-- 创建外键

Create table my_foreign1(

Id int primary key auto_increment,

Name varchar(20) not null comment ‘学生姓名’,

c_id int comment ‘班级id’,

-- 增加外键

Foreign key (c_id) references my_class(id)

)charset utf8;

在新增表之后再增加外键，修改表结构

Alter table 表名 add[constraint 外键名字] foreign key(外键字段) references 父表(主键字段);

-- 创建一个表

Create table my_foreign2(

Id int primary key auto_increment,

Name varchar(20) not null comment ‘学生姓名’,

c_id int comment ‘班级id’,

)charset utf8;

-- 增加外键

Alter table my_foreign2 add constraint student_class_1 foreign key (c_id) references my_class (id);

修改外键&删除外键

外键不可修改：只能先删除后新增

删除外键

Alter table 表名 drop foreign key 外键名; -- 一张表中可以有多个外键，但是名字不能相同

-- 删除外键

Alter table my_foreign1 drop foreign key my_foreign1_ibfk_1;

外键的作用

默认的作用有两点：一个对父表，一个对子表(外键字段所在的表)

对子表约束：子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的匹配的时候，操作会失败(约束子表数据操作)

-- 插入数据：外键字段在父表中不存在

Insert into my_foreign2 values (null,’小明’,4); -- 没有4班级

不能新增或者更新一个子行(子表中的行)，c_id引用了my_class中的id：而4在my_class中不存在

对父表约束：父表数据进行写操作(删和改：都必须涉及到主键本身)，如果对应的主键在子表中已经被数据引用，那么不允许操作

Insert into my_foreign2 values (null,’小明’,1);

Insert into my_foreign2 values (null,’中明’,2);

Insert into my_foreign2 values (null,’大明’,2);

-- 更新父表记录

Update my_class set id = 3 where id =1; -- 失败 ： id=1的记录已经被学生引用了

Update my_class set id = 4 where id =3; -- 成功 ： id=3的记录没有学生引用

外键条件

1.外键要存在：首先必须保证表的存储引擎是innodb(默认存储引擎)：如果不是innodb存储引擎，那么外键可以创建成功，但是没有约束效果。

2.外键字段的字段类型(列类型)必须与父表的主键类型一致

3.一张表中的外键名字不能重复。任何外键都创建失败都不会提示错误位置。

4.增加外键的字段(数据已经存在)必须保证数据与父表主键对应

-- 插入数据

Insert into my_foreign1 values (null,’马小六’,3);

-- 增加外键

Alter table my_foreign1 add foreign key(c_id) references my_class(id);

数据已经存在：在新增外键的时候，无法改变不能匹配数据的事实。

外键约束

所谓外键约束，就是指外键的作用

之前所讲的外键作用，是默认的作用；其实可以通过对外键的需求，进行定制操作

外键约束有三种约束模式，都是针对父表的约束

Distinct：严格模式(默认的)，父表不能删除或者更新一个已经被子表数据引用的记录

Cascade：级联模式：父表的操作，对应子表关联的数据也跟着操作

Set null：置空模式：父表的操作之后，子表对应的数据(外键字段)被置空

通常的一个合理的做法(约束模式)，删除的时候子表置空 ，更新的时候子表级联操作

指定模式的语法

Foreign key (外键字段)references 父表(主键字段) on delete 模式(set null) on update 模式(cascade)

-- 创建外键：指定模式：删除置空，更新级联

Create table my_foreign3(

Id int primary key auto_increment,

Name varchar(20) not null,

C_id int,

-- 增加外键

Foreign key (c_id)

-- 引用表

References my_class(id)

-- 指定删除模式

On delete set null

-- 指定更新模式

On Update cascade

)charset utf8;

Insert into my_foreign3 values (null,’刘备’,1);

更新操作级联更新

删除操作置空

-- 删除父表主键

Delete from my_class where id =2;

删除置空的前提条件：外键字段允许为空(如果不满足条件，外键无法创建)

降低了程序对数据的可控性：通常在实际开发中，很少使用外键来处理

联合查询

联合查询：将多次查询(多条select语句)，在记录上进行拼接(字段不会增加，记录会增加)

基本语法

多条select语句构成：每一条select语句获取的字段数必须严格一致但是字段类型无关

Select 语句1

Union[union 选项]

Select 语句2........

Union 选项与select选项一样有两个

All：保留所有

Distinct：去重：默认的

-- 联合查询

Select * from my_class

Union -- 默认去重

Select * from my_class;

-- 联合查询

Select * from my_class

Union all

Select * from my_class;

联合查询只要求字段一样：与数据类型无关

-- 联合查询

Select id , c_name , room from my_class

Union all

Select name , number, id from my_student;

联合查询意义：

1.查询统一张表但是需求不同，如查询学生信息，男生身高升序，女生身高降序

2.多表查询：多张表的结构是完全一样的，保存的数据(结构)也是一样的。

在联合查询中，order by 不能直接使用，需要对查询语句使用括号才行

-- 需求男生升序，女生降序(年龄)

(Select * from my_student where sex = ‘男’ order by age asc)

Union

(Select * from my_student where sex =’女’ Order by age desc);

若要order by 生效：必须搭配limit，limit使用限定的最大数即可

-- 需求男生升序，女生降序(年龄)

(Select * from my_student where sex = ‘男’ order by age asc limit 9999999)

Union

(Select * from my_student where sex =’女’ Order by age desc limit 99999999);

子查询

Sub query，查询是在某个查询结果之上进行的(一条select语句内部包含了另一条select语句)

子查询分类

子查询有两种分类方式：按位置分类；按结果分类

按位置分类：子查询(select语句)在外部查询(select语句)中出现的位置

From子查询：在from之后

Where子查询：在where之后

Exists子查询：在exists之后

按结果分类：根据子查询得到的数据进行分类(理论上任何一个子查询得到的结果都可以理解为二维表)

标量子查询：子查询得到的结果是一行一列

列子查询：子查询得到的结果是一列多行

行子查询：子查询得到的结果是多列一行(多行多列)

上面几个子查询在where之后

表子查询：子查询得到的结果是多行多列(出现的位置是在from之后)

标量子查询

需求：知道班级名字为php0710，想获得该班的所有学生

1.确定数据源：获取所有的学生

Select * from my_student where c_id = ?;

2.获取班级ID；可以通过班级名字确定

Select id from my_class where c_name = ‘PHP0710’; -- id一定只有一个值(一行一列)

-- 标量子查询

Select * from my_student where c_id in (Select id from my_class where c_name = ‘PHP0710’);

In /= 都可行

列子查询

需求：查询所有在读班级的学生(班级表中存在的班级)

1.确定数据源：学生

Select * from my_student where c_id in (?);

2.确定有效班级的id；所有班级id

Select id from my_class;

-- 列子查询

Select * from my_student where c_id in (Select id from my_class);

列子查询返回的结果会比较多：一列多行，需要使用in作为条件匹配：其实在mysql中还有几个类似的条件：all，some，any

any====in =其中一个即可

Any ===== some any跟some是一样的

=all ==== 全部

-- any ,some,all

Select * from my_student where c_id =any(select id from my_class);

Select * from my_student where c_id =some(select id from my_class);

Select * from my_student where c_id =all(select id from my_class);

否定结果：

-- any ,some,all

Select * from my_student where c_id !=any(select id from my_class);

Select * from my_student where c_id !=some(select id from my_class);

Select * from my_student where c_id !=all(select id from my_class);

行子查询

行子查询：返回的结果可以是多行多列(一行多列)

要求查询真个学生中，年龄最大且身高最高的学生

1.确定数据源

Select * from my_student where age = ？ and height = ?;

2.确定最大的年龄和最高的身高;

Select max(age),max(height) from my_student;

Select * from my_student where age = (Select max(age)from my_student

) and height = (Select max(height) from my_student);

行子查询：需要构造行元素：行元素由多个字段构成

-- (age，height)称之为行元素

Select * from my_student where (age ,height)= (Select max(age),max(height) from my_student

) ;

表子查询

表子查询： 子查询返回的结果是多行多列的二维表：子查询返回的结果是当做二维表来使用

需求：找出每个班中最高的一个学生

1.确定数据源：先将学生按照身高降序排序

Select * from my_student order by height desc;

2.从每个班中选出第一个学生

Select * from my_student group by c_id; -- 每班选出第一个学生

表子查询：from子查询：得到的结果作为from的数据源

-- 表子查询

Select * from (select * from my_student order by height desc) as student group by c_id;

Exists子查询

是否存在的意思，exists子查询就是用来判断某些条件是或否满足(跨表)，exists是接在where之后，其返回的结果只有0和1

需求：查询所有的学生，前提条件是班级存在

1.确定数据源

Select * from my_student where ？;

2.确定条件是否满足

Exists(Select * from my_class); -- 是否成立

-- Exists 子查询

Select * from my_student where exists(Select * from my_class);

-- Exists 子查询

Select * from my_student where exists(Select * from my_class where id = 2);

视图

视图view，是一种有结构(有行有列)但是没有结果(结构中不真实存在的数据)的虚拟表，虚拟表的结构来源不是自己定义，而是从对应的基表中产生(视图的数据来源)

创建视图

基本语法

Create view 视图名字 as select 语句; -- select 语句可以是普通查询，可以是连接查询，可以是联合查询，可以是子查询

-- 创建单表视图：基表只有一个

Create view my_v1 as select * from my_student;

Create view my_v2 as select * from my_class;

-- 创建多表视图，基表来源至少两个

Create view my_v3 as select * from my_student s left join my_class c on s.c_id = c.id; -- id 重复

Create view my_v3 as select s.*,c.c_name ,c.room from my_student s left join my_class c on s.c_id = c.id;

查看视图

查看视图：查看视图的结构

视图是一张虚拟表，表的所有查看方式都适用于视图：show tables[like]/desc 视图名

视图比表还是有一个关键字的区别：view查看表(视图)的创建语句的时候，可以使用view关键字

-- 查看视图创建语句

Show create view my_v3 \G;

视图一旦创建L系统会在视图对应的数据库文件下创建一个结构文件

视图的使用

使用视图主要是为了查询，将视图当做表一样查询即可

-- 视图使用

Select * from my_v1;

视图的执行：其实本质就是执行封装的select语句。

修改视图

视图本身不可修改，但是视图的来源是可以修改的。

修改视图：修改视图本身的来源语句(select语句)

Alter view 视图名字 as 新的select 语句

-- 修改视图

Alter view my_v1 as

Select id,name,age,sex,height,c_id from my_student;

删除视图

Drop view 视图名字

视图的意义

1.视图可以节省SQL语句，将一条复杂的查询语句使用视图进行保存：以后可以直接对视图进行操作

2.数据安全：视图操作是主要针对查询，如果对视图结构进行处理(删除)，不会影响基表数据(相对安全)

3.视图往往是在大项目中使用，而且是多系统使用：可以对外提供有效的数据，但是隐藏关键(无用数据)：数据安全，隐私安全。

4.视图可以对外提供友好型：不同的视图提供不同的数据，对外好像是专门设计

5.视图可以更好的进行权限控制

视图数据操作

视图的确可以进行数据写操作的：但是有很多限制

将数据直接在视图上操作

新增数据

数据新增就是直接对视图进行新增

1.多表视图不能新增数据

2.可以向单表数据插入数据：但是视图中包含的基表中所有字段必须是不能为空(或者没有默认值)

-- 单表视图插入数据：但是视图不包含所有不允许为空的字段(number)

Insert into my_v1 values (null,’张三丰’,150,’男’,180,1);

视图中不包含number字段但是基表中有改字段，视图没有提供数据，基表只用默认数据null

但是该字段不能为null，所以不能插入1

3.视图是可以向基表插入数据的

-- 单表数据插入数据

Insert into my_v2 values (2,’PHP0326’,’D306’);

删除数据

多表视图不能删除数据：

-- 多表视图删除数据

Delete from my_v3 where id =1;

单表视图可以删除

-- 单表数据删除数据

Delete from my_v2 where id = 4;

更新数据

理论上无论是单表视图还是多表视图都可以更新数据

-- 多表数据更新

Update my_v3 set c_id =1 where id =5;

更新限制：with check option ，如果对视图在新增的时候，限定了某个字段有限制；那么在对视图进行数据更新操作时，系统会进行验证，要保证更新之后，数据依然可以被视图查询出来，否则不被更新。

-- 视图：age字段限制更新

Create view my_v4 as select * from my_student where age >30 with check option;-- 表示视图的数据来源都是大于30岁的，with check option决定通过视图更新的时候不能将已经得到的数据age>30 的改成小于30的。

-- 将视图可以查到的数据改成小于30

Update my_v4 set age = 29 where id =5;

-- 可以修改数据让数据可以查到

Update my_v4 set age = 33 where id =5;

-- 获取所有班级中最高的学生

Create view my_v5 as select * from my_student order by height desc;

视图算法

视图算法：系统对视图以及外部查询视图的select语句的一种解析方式

视图算法分为三种

Undefined：未定义(默认的)，这不是一种实际使用算法，是一种推卸责任的算法；告诉系统视图没有定义算法，系统自己看着办。

Temptable：临时表算法：系统应该先执行视图的select语句，后执行外部语句

Merge：合并算法：系统应该先将视图对应的select语句与外部查询视图的select语句进行合并，然后执行(效率高：常态)

算法指定：在创建语句的时候

Create algorithm = 指定算法 view 视图名字 as select 语句;

-- 指定算法为临时表

Create algorithm = temptable view my_v6 as select * from my_student order by height desc;

视图算法选择：如果视图的select语句中会包含一个条件(查询语句(五子句))而且很有可能顺序比外部的查询语句要靠后，一定要使用算法，其他情况可以不用指定(默认即可)

数据的备份与还原

备份：将当前已有的数据或者记录保留

还原：将已经保留的数据恢复到对应的表中

为什么要做备份和还原？

1.防止数据丢失：被盗，误操作

2.保护数据的记录

数据备份还原的方式有很多种：数据表备份、单表数据备份、SQL备份、增量备份

数据表备份

不需要通过SQL来备份，直接进入到数据库文件夹复制对应的表结构和数据文件以后还原的时候，直接将备份的内容放进去即可。

数据表备份有前提条件：根据不同的存储引擎有不同的区别。

存储引擎：mysql数据存储的方式：主要是innodb和myisam

对比myidam和innodb：数据存储方式

Innodb：只有表结构，数据全部存储到ibdate1文件中

Myisam：表数据和索引全部单独分开存储

-- 创建myisam表

Create table my_myisam(

Id int

)charset utf8 engine = myisam;

Frm是结构

Myd是数据

Myi是索引

这种文件备份通常适用于myisam存储引擎，直接复制这三个文件即可。Innodb不行。

单表数据备份

每次只能备份一张表；只能备份数据(表结构不能备份)

通常使用：将表中的数据进行导出到文件

备份：从表中选出一部分数据保存到外部的文件中(outfile)

Select */字段列表 into outfile 文件所在路径 from 数据源; -- 前提外部文件不存在

-- 单表数据备份

Select * into outfile ‘E:/MySQL/’ student.text from my_student;

高级备份：自己制定字段和行处理方式

Select */字段列表 into outfile 文件所在路径 fields 字段 处理 lines 行处理 from 数据源;

Fields：字段处理

Enclosed by ：字段使用什么内容包裹，默认是‘ ’ 空字符串

Terminated by：字段以什么结束，默认是‘\t’，tab键

Escaped by：特殊符号用什么方式处理，默认是‘\’，使用反斜杠转移

Lines：行处理

Starting by ：每行以什么开始，默认是‘ ’空字符串

Terminated by：字段以什么结束，默认是‘\r\n’，换行符

-- 指定备份方式

Select * into outfile ‘E:/MySQL/’ student.text

-- 字段处理

Fields enclosed by ‘”’ -- 数据使用双引号包裹

Terminated by ‘|’

-- 行处理

Lines

Starting by ‘START:’

From my_class;

数据还原

将一个在外部保存的数据恢复到表中(如果表结构不存在，那么sorry不可行)

Load data infile 文件所在路径 into table 表名[字段列表)] fields 字段处理 lines 行处理;

-- 如何备份就要如何还原

Load data infile ‘E:/MySQL/’ student.text into table my_class;

SQL备份

备份是SQL语句：系统会对表结构以及数据进行处理，变成对应的SQL语句，然后进行备份：还原的时候只要执行SQL指令即可(主要针对是表结构)

备份：mysql没有提供备份指令：需要利用mysql提供的软件：MySQLdump.exe

MySQLdump.exe也是一种客户端，需要操作服务器，必须连接认证

Mysqldump/MySQLdump.exe -h Pup 数据库名字 [数据表名字1 数据表名字2.....]

外部文件目录(建议使用.sql)

-- SQL备份

MySQLdump -uroot -p mydatabase my_student > E:/MySQL/student.sql

整库备份

Mysqldump/MySQLdump.exe -h Pup 数据库名字 > 外部文件目录(建议使用.sql)

-- 整库备份

MySQLdump -uroot -p mydatabase > E:/MySQL/mydatabase.sql

SQL还原数据

两种方式

1.使用mysql.exe客户段还原

Mysql.exe/mysql -hPup 数据库名字 < 备份文件

-- 还原数据：mysql客户端还原

Mysql -u root -p mydatabase < E:/MySQL/mydatabase.sql[student.sql]

2.使用sql指令备份

Source 备份文件所在路径

Source E:/MySQL/student.sql;

SQL备份优缺点

1.优点，可以备份结构

2.缺点，会浪费空间(额外增加SQL指令)

增量备份

不是针对数据或者SQL指令进行备份：是针对mysql服务器的日志文件进行备份

增量备份：指定时间段开始进行备份，备份数据不会重复，不会浪费空间，而且所有的操作都会被备份。(大项目都用增量备份)

需求：有一张银行账户表，有A用户给B用户转账：A用户先减少，B账户增加，但是A操作完了之后断电了，

解决方案：A减少钱，但是不要修改数据表，B收到钱之后，同时修改数据表

事务安全

事务：一系列要发生的连续的操作只针对数据不针对结构

事务安全：一种保护连贯操作同时满足(实现)的一种机制

事务安全的意义：保证数据安全的完整性

-- 创建账户表

Create table my_account(

Number char(16) not null unique ,

Name varchar(20) not null,

Money decimal (10,2) default 0.0

)charset utf8;

-- 插入数据

Insert into my_account values

(‘0000000000000001’,’张三’,1000),

(‘0000000000000002’,’李四’,2000);

事务操作

事务操作分为两种：自动事务(默认的)，手动事务

1.开启事务：告诉系统一下所有操作(写)不要直接写入到数据表，先存放到事务日志

Start transaction

-- 事务安全

-- 开启视图

Start transaction;

2.进行事务操作：一系列的操作

A)李四账户减少

Update my_account set money=money - 1000 where id = 2;

B)张三账户增加

Update my_account set money = money +1000 where id =1;

3.关闭事务：选择性的将日志文件中的操作的结果保存到数据表(同步)或者说直接清空事务日志(原来操作全部清空)

A)提交事务：同步数据表(操作成功)：commit

B)回滚事务：直接清空日志表(操作失败)：rollback

-- 提交事务

Commit

事务原理

事务操作原理：事务开启之后，所有的操作都会临时保存到事务日志，事务日志只有在得到commit命令才会同步到数据表，其他任何情况都会清空(rollback，断电，断开连接)

回滚点

回滚点：在某个成功的操作完成之后，后续的操作有可能成功有可能失败，但是不管成功还是失败，前面操作都已经成功：可以在当前成功的位置，设置一个点：可以供后续失败操作回到该位置，而不是返回所有操作，这个点称之为回滚点

设置回滚点语法：savepoint回滚点名字；

回到回滚点语法：rollback to 回滚点名字；

-- 回滚点操作

--开启事务

Start transaction;

-- 设置回滚点-- 事务处理1：张三加钱

Update my_account set money = money + 10000 where id = 1;

Savepoint sp1;

-- 银行扣税

Update my_account set money = money - 100000.05 where id =2; -- 错误

-- 回滚到回滚点

Rollback to sp1;

Update my_account set money = money - 100000.05 where id =1;

-- 查看结果

Select * from my_account;

-- 提交

Commit;

自动事务处理

在mysql中：默认的都是自动事务处理，用户操作完会立即同步到数据表中

自动事务：系统通过autocommit变量控制

Show variables like ‘autocommit’;

关闭自动提交set autocommit =0/off;

再次直接写操作

Update my_account set money = money + 10000 where id =2;

自动关闭之后需要手动来选择处理：commit提交，rollback回滚；

改回自动提交set autocommit =1/on;

事务特性

事务有四大特性：ACID

A:atomic原子性，事务的整个操作是一个整体，不可分割，要不全部成功，要么全部失败；

C：consistency一致性，事务操作的前后，数据表中的数据没有变化

I：isolation，隔离性，事务操作是相互隔离不受影响的。

D：durability，持久性，数据一旦提交，不可改变，永久的改变数据表数据

锁机制：innodb默认是行锁，但是如果是在事务的操作过程中，没有使用到索引，那么系统会自动全表索引数据，自动升级为表锁

行锁：只有当前行被锁住，别的用户不能操作

表锁：整张表被锁住，别的用户都不能操作

变量

变量分为两种：系统变量和自定义变量

系统变量

系统定义好的变量：大部分时候用户根本不需要使用系统变量：系统变量是用来控制服务器的表现的：如：autocommit，auto_increment等

查看系统变量

Show variables; -- 查看系统变量

-- 查看所有系统变量

Show variables;

查看具体变量值：任何一个有数据返回的内容都是由select查看

Select @@变量名;

-- 查看系统变量值

Select @@version,@@autocommit;

修改系统变量

分为两种方式：会话级别和全局级别

会话级别：临时修改，当前客户端档次连接有效

Set 变量名 = 值

Set @@变量名= 值

-- 修改会话级别变量

Set autocommit=0

全局级别：一次修改，永久修改(对所有客户端都生效)

Set global 变量名 = 值

-- 修改全局级别变量

Set global autocommit = 0;

如果其他客户端当前已经连上服务器，那么当次修改无效，要退出重新登录才会生效

自定义变量

系统为了区分系统变量，规定用户自定义变量必须使用一个@符号

Set @变量名 = 值

-- 定义自定义变量

Set @name = 3330;

自定义变量也是类似系统变量查看

Select @变量名

Select @name;

在mysql中‘=’会默认的当做比较符号处理(很多地方)，mysql为了区分比较和赋值的概念，重新定义了一个新的赋值符号：=

-- 定义变量

Set @age:=18;

Mysql允许从数据表中获取数据，然后赋值给变量：两种方式

1.边赋值，边查看结果

Select @变量名 := 字段名 from 数据源; -- 从字段中取值赋值给变量名,如果使用=会变成比较

-- 从表中获取数据赋值给变量 最后一条数据

Select @name := name from my_student;

2.只有赋值不看结果：要求很严格：数据记录最多只允许获取一条：mysql不支持数zu

Select 字段列表 from 表名 into 变量列表;

只允许获取一条数据

Select name,age from my_student where id = 6 into @name,@age;

所有自定义的变量都是会话级别：当前客户端当次连接有效(变量不区分数据库)用户级别

需求：有两张表：一张订单表，一张商品表，每生成一个订单，意味着商品的库存要减少

触发器

触发器：tigger，事先为某张表绑定好一段代码，当表中某些内容发生改变时(增删改)

系统会自动触发代码，执行

触发器：事件类型，触发时间，触发对象

事件类型：增删改，三种类型insert delete update

触发时间：前后，before after

触发对象：表中的每一条记录(行)

一张表表中只能拥有一种触发时间的一种类型的触发器：最多一张表能有6个触发器

创建触发器

在mysql高级结构中：没有大括号，都是对应的字符符号代替

触发器基本语法

-- 临时修改语句结束符

Delimiter 自定义符号：后续代码中只有碰到自定义符号才算结束符

Create trigger 触发器名字 触发时间 事件类型 on 表名 for each row

Begin -- 代表左大括号：开始

-- 里面就是触发器的内容，每行内容都必须使用语句结束符：分号

End -- 代表右大括号：结束

-- 语句结束符

自定义符号

-- 将临时修改修正过来

Delimiter;

-- 创建表

Create table my_goods(

Id int primary key auto_increment,

Name varchar(20) not null,

Price decimal(10,2) default 1,

Inv int

)charset utf8;

Insert into my_goods values(null,’iphonex’,5288,100),(null,’米8’,3200,200);

Create table my_order(

Id int primary key auto_increment,

G_id int not null ,

G_number int

)charset utf8;

-- 触发器：订单生成一个，库存减少

-- 临时修改语句结束符

Delimiter $$

Create trigger after_order after insert on my_order for each row

Begin

-- 触发器内容开始

Update my_goods set inv = inv -1 where id = 2;

End

-- 结束触发器

$$

-- 修改临时语句结束符

Delimiter ;

查看触发器

查看所有触发器或者模糊匹配

Show triggers [like ‘patter’];

-- 查看所有触发器

Show triggers

-- 查看触发器创建语句

Show create trigger after_order\G

所有的触发器都会保存到一张表中：information_schema.triggers;

使用触发器

触发器：不需要手动调用，而是当某种情况发生时候自动触发(订单里面插入数据之后)

-- 插入数据

Insert into my_order values (null,1,2);

修改触发器&删除触发器

触发器不能修改，只能先删除，后新增

Drop trigger 触发器名字;

触发器记录

是指不管触发器是否触发了，只要当某种操作准备执行，系统就会将当前要操作的记录的当前状态和即将执行之后的新的状态给分别保留下来，供触发器使用：其中，要操作的当前状态保存到old中，操作之后的可能心态保存到new。

Old代表的是旧记录 ，new代表的是新记录

删除的时候是没有new的；插入的时候是没有old的

Old和new都是代表记录本身：任何一条记录除了数据，还有字段名字

使用方式：old字段名/new字段名(假设发生之后的结果)

-- 触发器：订单生成一个，库存减少

-- 临时修改语句结束符

Delimiter $$

Create trigger after_order after insert on my_order for each row

Begin

-- 触发器内容开始：新增一条订单，old没有，new代表新的订单记录

Update my_goods set inv = inv -new.g_number where id = new.g_id;

End

-- 结束触发器

$$

-- 修改临时语句结束符

Delimiter ;

触发器处理正确

代码执行结构

三种结构有三种：顺序结构，分支结构和循环结构

分支结构

分支结构：实现准备多个代码块，按照条件选择新执行某段代码。

在mysql中只有if分支

基本语法

If 条件判断 then

-- 满足条件要执行的代码；

Else

-- 不满足条件要执行的代码；

End if

触发器结合if分支：判断商品库存是否足够，不够不能生成商品订单

-- 触发器：订单生成之前要判断库存是否满足

-- 修改语句结束符

Delimiter %%

Create trigger before_order before insert on my_order for each row

Begin

-- 判断商库存是否足够

-- 获取库存：商品库存在表中

Select inv from my_goods where id=new.g_id into @inv;

-- 比较库存

If @inv < new.g_number then

-- 库存不够，触发器没有提供一个能够阻止事件的能力(暴力解决)

Insert into XXX values (XXXX);

End if;

End

%%

-- 改回语句结束符

Delimiter ;

-- 插入数据

Insert into my_order values (null,1,1000000);

循环结构

循环结构：某段代码在指定条件下重复循环

While循环(没有for循环)

While 条件判断 do

-- 满足条件要执行的代码

-- 变更循环条件

End while;

循环控制：在循环内部进行循环的判断和控制

Mysql中没有对应的continue和break但是有替代品。

Iterate：迭代：类似continue，后面代码不执行，循环重新来过

Leave：离开，类似break，整个循环接收

使用方法：itrate/leave 循环名字；

-- 定义循环名字

循环名字：while 条件 do

-- 循环体

-- 循环控制

Leave/iterate 循环名字；

End while;

函数

函数：将一段代码块分装到一个结构中，在需要执行的代码块的时候，调用结构执行即可(代码复用)

函数分为两类：系统函数和定义函数

系统函数

系统定义好函数，直接调用即可

任何函数都有返回值，因此函数的调用是通过select调用

Mysql中字符串基本操作单位(最常见的是字符)

Substring：字符串截取(字符为单位)

Char_length 字符长度

Length 字节长度

-- 定义两个变量

Set @cn = ‘世界你好’;

Set @en=’hello world’;

-- 字符串截取

Select substring(@cn,1,1);

Select substring(@en,1,1);

-- 字符串长度

Select char_length(@cn),char_length(@en),length(@cn),length(@en);

Instr:判断字符串是否在某个具体的字符串中存在，存在返回位置

-- 字符串寻找

Select instr (@cn,’界’),instr(@en,’ll’),instr(@cn,’拜拜’);

Lpad:左填充，将字符串按照某个指定的填充方式填充到指定长度(字符单位)

-- 字符串左填充

Select lpad(@cn,20,’欢迎’),lpad(@en,20,’hello’);

Insert 替换，找到目标位置指定长度的字符串，替换成目标字符串

-- 字符串替换

Select insert(@en,3,3,’y’),@en;

Strcmp：compare字符串比较

-- 字符串的比较

Set @f =’hello’;

Set @s=’hey’;

Set @z=’HEY’; -- 不区分大小写

Select strcmp(@f,@s),strcmp(@s,@z),strcmp(@s,@f);

自定义函数

函数要素：函数名，参数列表(形参和实参)，返回值，函数体(作用域)

创建函数：

创建语法

Create function 函数名([形参列表]) returns 数据类型 -- 规定要返回的数据类型

Begin

-- 函数体

-- 返回值， return类型(指定数据类型)

End

-- 创建函数

Create function display1() returns int

Return 100;

自定义函数与系统函数的调用方式是一样：select 函数名([函数实参])

Select display1();

查看函数

查看所有函数：show function status[like ‘pattern’]

-- 查看所有函数

Show function status\G

只有在数据库下才能被调用，其他数据库不能调用

查看函数的创建语句：show create function 函数名;

-- 查看函数创建语句

Show create function display1\G

修改函数&删除函数

函数只能先删除后新增，不能修改

Drop function 函数名；

Drop function display1;

函数参数

参数分为两种：定义时的参数叫做形参，调用时的参数叫实参

形参：必须指定数据类型

Function 函数名(形参名字 字段类型)returns 数据类型

-- 做函数：计算1-x之间的和

Delimiter $$

Create function display1(int_1 int) returns int

Begin

-- 定义条件变量

Set @i=1; -- @定义的变量是全局变量，没有的是局部变量

Set @res = 0; -- 保存结果

-- 循环求和

While @i <= int_1 do

-- 求和：任何变量要修改必须使用set关键字

-- mysql中没有+=，没有++

Set @res = @res + @i;

-- 修改循环变量

Set @i = @i +1;

End while;

-- 返回值

Return @res;

End

$$ -- 函数结束

Delimiter ;

在函数内部使用@定义的变量在函数外部也可以访问

作用域

Mysql中的作用域域js中的作用域完全一样

全局变量可以在任何地方使用；局部变量只能在内部使用

全局变量：使用set关键字定义，使用@标志

局部变量：使用declare关键字声明，没有@符号，所有局部变量的声明，必须在函数开始之前定义好

-- 求和：1-x之间的和要求5的倍数不加

Delimiter $$

Create function display2(int_1 int) returns int

Begin

-- 声明变量：循环变量，结果变量

Declare i int default 1;

Declare res int default 0; -- 定义局部变量可以有属性

-- 循环判断

Mywhile:While i <= int_1 do

-- 相加：判断

If i % 5 = 0 then

-- 修改循环条件跳出

Set i = i + 1;

-- 不符合条件：循环重新来过

Iterate mywhile;

End if;

-- 相加

Set res = res + i;

-- 改变循环变量

Set i = i + 1;

End while;

-- 返回结果

Return res;

End

$$

Delimiter ;

存储过程

存储过程简称过程：，procedure，是一种处理数据的方式，是一种没有返回值得函数

创建过程

Create procedure 过程名字([参数列表])

Begin

-- 过程体

End

-- 创建存储过程

Create procedure Pro1 () -- 假设过程中需要显示数据：使用select

Select * from my_student;

查看过程

函数的查看方式完全适用于过程：关键字换成procedure

查看所有过程

Show procedure status [like pattern]

-- 查看一个过程

Show procedure status like ‘Pro%’\G

查看创建语句

Show create procedure 过程名

-- 查看创建过程语句

Show create procedure Pro1;

调用过程

过程没有返回值：select 不能访问

过程有专有的调用关键字

Call Pro1();

修改过程&删除过程

过程只能删除，不能修改

Drop procedure 过程名字;

-- 删除过程

Drop procedure Pro1;

过程参数

函数的参数需要数据类型指定，过程比函数更严格

过程有自己都类型限定，三种类型

In：数据只是从外部传入给内部使用(值传递)：可以数值可以是变量

Out：只允许过程内部使用(不用外部数据)，给外部使用的：(引用传递，外部的数据会被先清空才会进入到内部)：只能是变量

Inout：外部可以在内部使用，内部修改也可以外部使用：典型的引用传递：只能穿变量

基本使用

Create procedure 过程名(in 形参名字 数据类型，out 形参名字 数据类型，inout 形参名字 数据类型)

-- 过程参数

Delimiter $$

Create procedure Pro1(in int_1 int,out int_2 int,inout int_3 int)

Begin

-- 先查看三个变量

Select int_1, int_2,int_3; -- int_2的值一定是null

End

$$

Delimiter ;

调用：out和inout类型的参数必须传入变量，而不是数据

正确调用：传入变量

Set @int_1 = 1;

Set @int_2 = 2;

Set @int_3 = 3;

Select @int_1,@int_2,@int_3;

Call pro1(@int_1,@int_2,@int_3);

Select @int_1,@int_2,@int_3;

存储过程对于变量的操作(返回)是滞后的：是在存储过程调用结束的时候，才会重新将内部的修改的值赋值给外部传入的全局变量。

-- 存储过程例子

Delimiter $$

Create procedure pro2(in int_1 int,out int_2 int,inout int_3 int)

Begin

-- 先查看三个变量

Select int_1,int_2,int_3; -- 三个都是局部变量

Set int_1 = 10;

Set int_2 = 100;

Set int_3 = 1000;

-- 查看局部变量

Select int_1,int_2,int_3;

-- 查看全局变量

Select @int_1,@int_2,@int_3;

-- 修改全局变量

Set @int_1 = 111;

Set @int_2 = 222;

Set @int_3 = 333;

-- 查看全局变量

Select @int_1,@int_2,@int_3;

End

$$

Delimiter ;

测试传入数据123

Set @int_1 = 1;

Set @int_2 = 2;

Set @int_3 = 3;

Call pro2(@int_1,@int_2,@int_3);

-- 说明局部变量与全局变量无关

在存储过程调用结束之后，out和inout会将过程内部对应的局部变量的值重新返回给对应的传入的全局变量

最后：在存储过程调用结束之后，系统会将局部变量重新返回给全局变量(out和inout)

参考博客：https://blog.csdn.net/qq_39130650