第一章、MySQL入门
MySQL 是使用 C/C++ 语言开发的开源 RDBMS(关系数据库管理系统)，它能够安全高效地存储和管理数据。
关系型数据库
底层是以二维表的及其之间的关系所组成的数据库，即是关系型数据库。例如：
ID
姓名
年龄
1
张三
18
2
李四
20
3
王五
22
名称解释
1、数据库服务器
数据库服务器其实就是一个软件，比如我们安装的mysql软件（或者mariadb软件）
mysql服务器软件需要安装在服务器硬件上（就是一台计算机）才可以让外界来访问
2、数据库
在mysql服务器中，可以创建很多的数据库（database）
通常情况下，一个web站点对应一个数据库
3、数据表
在数据库（database）中，可以创建很多张表（table）
通常情况下，一张表用于保存一类数据，例如网站中的所有用户信息会保存在一张表中，所有商品信息会保存在另一张表中。
4、表记录
在数据表（table）中，可以插入很多条表记录
MySQL第二章、管理数据库和表
当我们连接到MySQL服务器后，可以发现它自带了四个数据库
其中test数据库是空的，可以使用做一些测试。但是其他3个数据库里保存的都是和MySQL系统相关的一些数据（如用户数据、表元数据、权限数据、函数、触发器等），如果改动将影响MySQL的使用，因此不要动！！
元数据：描述数据的数据
一、创建、访问、修改和删除数据库
1、创建数据库
-- 查看mysql服务器中所有数据库
show databases;
-- 进入数据库
use test;
-- 查看当前库中的所有表
show tables;
提示: mysql数据库不区分大小写
创建数据库语法：
-- 创建mydb1库
create database [if not exists] mydb1 charset utf8;
这里如果不用charset指定数据库的编码，则默认编码为latin1
-- 查看、进入mydb1库
show databases;
use mydb1;
2、访问数据库
创建好数据库之后，我们就需要在数据库中执行一系列对数据的操作了，如在数据库中创建表，往数据库中表内插入数据等等。那首先我们就需要先进入到指定的数据库中，再进行操作。
语法：USE 数据库名
3、修改数据库
例如我们在创建数据库时忘记了指定数据的默认字符集，则可以通过alter语句修改指定的数据库。
语法：
alterdatabase数据库名 charset 字符集
需求：将数据库product_management1的字符集改为utf-8
然后修改数据库的默认字符集：
4、删除数据库
语法：
drop database [if exists] 数据库名
练习：
- 创建数据库school_system来保存学生管理系统所需的数据
- 查看当前服务器中的所有数据库来验证数据库是否创建成功
- 通过查看建库语句来查看数据库的默认字符集
- 如果字符集不是utf-8，则修改数据库字符集为utf-8来保证可以插入中文数据
- 进入数据库
- 删除数据库
- 查看是否删除成功
二、表的创建和管理
创建表时，如我们想创建一张用户表， 需要定义表的列，列的定义包括列名+数据类型
1、常用数据类型：
数值类型
MySQL中支持多种整型，其实很大程度上是相同的，只是存储值的大小范围不同而已。
其次是浮点类型即：float和double类型
float：4字节单精度浮点类型，相对于java中的float
double：8字节双精度浮点类型，相对于java中的double
字符串类型
1、char(n)
定长字符串，最长255个字符。n表示字符数，例如：
-- 创建user表, 指定用户名为char类型，字符长度不超过10
create table user(
username **char(10)**,
...
);
所谓的定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间会用空格填充。（这样会浪费空间）
2、varchar(n)
变长字符串，最长不超过 65535个字节，n表示字符数，
一般超过255个字节，会使用text类型，例如：
-- 创建user表, 指定用户名为varchar类型，长度不超过10
create table user(
username **varchar(10)**
);
所谓的不定长，是当插入的值长度小于指定的长度10（上面指定的）时, 剩余的空间可以留给别的数据使用。（节省空间）
3****、大文本（长文本）类型
最长65535个字节，一般超过255个字符列的会使用text。
**-- 创建user表, **
create table user(
resume text
);
另，text也分多种，其中bigtext存储数据的长度约为4GB。
- 总结： char、varchar、text都可以表示字符串类型，其区别在于：
(1)char在保存数 据时, 如果存入的字符串长度小于指定的长度n,后面会用空格补全。
(2)varchar和text保存数据时, 按数据的真实长度存储, 剩余的空间可以留给别的数据用.
(3)char会造成空间浪费(不足指定长度的会用空格补全), 但是由于不需要计算数据的长度, 因此速度更快。（即以空间换时间）
(4)varchar和text是节省了空间，但是存储的速度不如char快，因为要计算数据的实际长度。（即以时间换空间）
日期类型
1****、date：年月日
2****、time：时分秒
3****、datetime：年月日 时分秒
4****、timestamp：时间戳(实际存储的是一个时间毫秒值)，与datetime存储日期格式相同。
timestamp最大表示2037年，而datetime范围是1000~9999
timestamp在插入数据、修改数据时，可以自动更新成系统当前时间
2、创建表
语法：
createtable [if not exists] 表名 (<列1的定义>,<列2>......)
列的定义： 列名 数据类型 [约束 default默认值 [auto_increment]]
在上述语法中：
l DEFAULT 是可选子句，用于指定列的默认值。
l AUTO_INCREMENT 是用于自动生成列的下一个值的步进值。
例1：创建表product存储商品信息
列有：
*pro_id （商品id*且不允许为空）
pro_name****（商品名称，不允许为空）
pro_num****（商品剩余数量，不允许为空）
price（商品价格，允许为空，如果不输入则默认价格为2****）
**例2：创建表customer存储客户信息 **
列有：
cus_id****（客户ID，不允许为空）
cus_name****（客户姓名，不允许为空）
address****（住址，可为空）
创建了表后，可以使用 DESCRIBE /desc语句查看关于表中所有列的详细信息。
语法： DESCRIBE 表名 [列名| ‘value’]
value 是可选元素，表示包含模式的字符串，以用于搜索符合该模式的列名。
MySQL 支持两种通配符：_（下划线）和 %（百分号）。
_ 字符代表单个字符，% 代表一组字符。
例如：
3、约束
有时我们需要对某一列的数据添加其他的约束，来限制用户随意地插入数据。
MySQL 支持可对表中的列定义的以下约束：
非空约束
主键约束 （唯一且非空）
唯一约束（不能有重复的数据）
外键约束
如创建用户表user
- User_id 主键自增
- User_name 非空
- Nick_name 不能重复
- User_age
- Birthday
主键约束
唯一且非空，用来唯一地标识一条数据。
创建表时应用主键约束的语法是：
定义列时就加上主键约束：
列名 数据类型 primarykey
primary key (列名)
例如：
或者
在列的定义最后加上：
例如：
唯一约束
唯一约束用于强制非主键列的唯一性，只是约束数据唯一，所以数据可以为空。
可以向表添加多个唯一约束。 但是，表只能有一个主键。
语法：
在列定义时添加 unique
或
在列定义最后加上 unique(列名)
例如，要求商品不能出现相同的名称。
例如：为客户名称加上唯一约束
外键约束
以下图为例
其中订单表中的cus_id应该参考customer表中的cus_id数据，因此customer表称为主表或被引用表。订单表称为事务表或引用表。
订单表中的pro_id也应该参考product表中的pro_id,所以product表称为主表或被引用表。订单表称为事务表或引用表。
当一个表中的数据依赖于另一个表中的数据时，可使用外键约束维护这些表中数据的一致性。 表中的外键总是引用其他表中的主键列。
Customer_Order 表只能包含 Customers 表中存储其信息的客户的订单,此外，如果需要从 Customers 表（主表）中删除客户的记录，还应确保在 Customer_Order 表（事务表）不存在任何相关记录。 因此，在从 Customers 表中删除客户的详细信息之前，应删除该客户所下的所有订单。 外键约束确保 Customers 表和Customer_Order 表中所存储的数据之间不存在不一致情况。
创建订单表：
或者：
上述语句创建了 Customers_Order 表，并在 Products_ID 列上应用外键约束 fk1， 在 Customers_ID 列上应用外键约束 fk2。
定义约束的过程中，可以指定约束名。 如果不指定名称，则 MySQL 服务器自动向约束分配名称。
三、操作表数据
上述内容我们只是研究了表结构，还没有往表中插入任何数据。接下来我们就来学习如何操作表中的数据，其实就是增删改查，其中的重点也是难点就是查询数据。
1、插入数据
INSERT 语句
语法：
insert into <表名> values
实现往表中插入数据
实现往表的指定列中插入数据
[通过set插入数据]
例如：
有表user：
2、更新表
用update语句来修改已有的数据。
语法：
UPDATE <表名> SET 要修改的列 where子句
例如，要将 ID 为 2 的学生年龄改为23：
练习：
将student表中姓名为张三的年龄数据改为20
将student表中id为1的学生的出生日期改为2020年3月20日
将id为2的学生数据的姓名改为’小小’
3、删除数据
通过delete和truncate语句可以删除不想要的数据。
DELETE 语句
语法： DELETE FROM <表名> [where子句]
例1：删除姓名为张三的记录
例2：删除id为2的记录
例3：删除全部记录
TRUNCATE 语句
truncate 语句用于删除表中的所有记录。 TRUNCATE 语句的语法是：
TRUNCATE [TABLE] <表名>
例如，要使用 TRUNCATE 语句删除 stu表中的所有记录：
TRUNCATE 语句和不带 WHERE 子句的 DELETE FROM 语句类似，因为两个语句都删除指定表中的所有数据，但不会影响表的结构。 但是，这两个语句之间有一些差异。
下表列出了 TRUNCATE 和 DELETE FROM 语句之间的一些区别。
Truncate语句
Delete from 语句（不加where过滤）
它删除表并重新创建表
它逐个删除所有记录
执行速度快
执行速度较慢
将auto_increment列的值重置为初始值
不会重置自增量
不能包含在事务中（无法回滚）
可包含在事务中（可回滚恢复删除的数据）
四、表结构的修改与表的删除
更改表
我们在创建表时就定义好了表结构，如果想更改表的结构（一般不会），如添加一列，修改列的数据类型，或添加/删除列约束等等，可以使用alter table语句。
语法：
alter table 表名
添加列
add [column] 列定义 [first|after列名]
如商品表中加上商品类别category 这一列。
添加约束
add [constraint 约束名] 约束(列名)
修改列
如我们想要为商品名称一列设置默认值为手机：
删除列
需求：从 Products 表中删除 Category 列：
重命名表
需求：将表的名称从 Products 更改为 Items。
删除表
当我们不需要一个表时，可以从数据库删除它，表可以与其中的数据以及所有关联的数据库对象（例如：其索引、触发器、约束和权限）一起删除。语法：drop table 表名
例如：
如果表被其他表引用，则必须先删除引用表或相应的外键约束，然后再删除被引用表。
MySQL第三章、数据查询
一、检索数据
1、查询数据select
查询所有列
指定查询的列
语法：select [distinct] 指定列 from表名 where子句
通过以下SQL语句创建员工表并实现：
查询指定列
查询所有列（通过*或指定所有列实现）
用distinct去重
查询的同时为列起别名（as关键字可省略）
创建并进入数据库mydb1
DROPDATABASEif****EXISTSmydb1;
CREATE****DATABASE mydb1 CHARSET****UTF8;
USE mydb1;
创建员工表并插入数据
dropTABLEif****existsemp;
CREATE****TABLEemp(idINTPRIMARYKEY,emp_nameVARCHAR(20)NOT****NULL,jobVARCHAR(10),top_idINT,hdateDATE,salDOUBLE,bonusdouble,dept_idINT);
INSERT****INTOempVALUES
(1,'张三','软件工程师',3,'2002-03-26',5000,null,1),
(2,'张三三','软件工程师',3,'2005-04-27',7000,700,1),
(3,'李四','软件工程师',7,'2001-07-12',9000,500,1),
(4,'王五','销售',6,'2010-03-15',1400,500,2),
(5,'赵六','销售',6,'2011-05-03',3000,1000,2),
(6,'ABC','销售',7,'2021-06-27',10000,500,2),
(7,'小七','总经理',NULL,'1999-05-20',1000,500,NULL);
实现：
1、查询所有数据
2、查询所有数据，但是只想显示id，姓名和职位
但是，表中的列名不是描述性的，可用as为列取别名
关于distinct去重
2、where子句
有时数据很多，我们就需要通过where子句过滤来获得我们想要的数据
例如：查询id为1的员工数据
查询职位为软件工程师的员工id和姓名
查询薪资大于7000的员工信息
where子句提供了各种运算符来实现数据的过滤，有逻辑运算符、比较运算符和算术运算符。当我们想要获取特定数量的数据，如要实现分页查询，则可用limit来实现
运算符 = <> > >= < <= is null is not null
运算符
OR(||)
只要有一个为true则结果为true。
如：查询职位为销售或薪资大于5000的员工
AND(&&)
只要有一个为false则结果为false。
如要查询职位为软件工程师并且薪资大于5000的员工
NOT(!)
取反
如查询职业不是销售的员工
不等于 <> / !=
BETWEEN x AND y
在x和y之间，包含x和y
例如：查询薪资在[5000,6000]间的员工信息
LIKE
模糊查询
现有员工数据：
如我们想查询所有姓张的员工：
通配符
描述
%（百分号）
匹配多个字符
_（下划线）
匹配单个字符
假如我们想查询姓张的员工，且名字为两个字
IN
in后跟多个值的集合
如：查询id为1或3的数据
3、算术运算符
运算符
描述
示例
- /
加减乘除(返回精确商值)
select 7/2 ; 显示3.5000
DIV
除法运算
select 7 div 2; 显示3
%
取模，余数运算
Select 7 % 2 ;显示1
例如，查询在2010-1-1前入职的员工，给他们的薪资涨1000,显示员工ID，姓名，涨后薪资
4、LIMIT 子句
限制返回的行数，一般用来实现分页查询。
语法： limit start_index,count
可以不指定start_index，则默认起始索引值为0
假设目前我们页面大小为3，则limit (当前页-1)*count,count
则第一页数据：
第二页数据：
二、使用函数来自定义结果集
1、字符串函数
例如我们想将员工的姓名和职位抽取出来并连接成一个字符串，则可以通过函数实现。
MySQL提供了很多用来操作字符串的函数，如
函数名
实例
描述
SUBSTRING
SELECT****SUBSTRING('ABCDEF',2,4);
返回BCDE,两个数字表示从第二个字符开始截取4个字符
CONCAT
SELECT****CONCAT('abc','de');
返回连接后的字符串abcde
例如，查询所有员工数据，并将员工的姓名和职位连接成一个字符串显示，起列别名为员工标识：
2、日期函数
可以用日期函数来操作数据中的日期和时间数据。
例如：查询员工入职了几年：
函数
示例
描述
curdate
SELECT****CURDATE();
获取当前日期
year
SELECT****YEAR('2008-8-24');
返回年份2008
例如：查询员工入职了几年：
3、数学函数
可以使用数学函数对数值执行各种算术运算。
下表列出了 MySQL 服务器提供的算术函数。
函数
示例
描述
round
SELECT****ROUND(12.05,1);
四舍五入并保留一位小数得12.1
4、聚合函数
在执行时，聚合函数将汇总一列或一组列的值，并生成一个值。聚合函数又称为分组函数。
例如，查询数据的个数：
下表列出了可用于计算汇总值的聚合函数。
函数
示例
描述
Avg(列名)
SELECT****AVG(sal)FROMemp;
工资的平均数为6000.0000
Sum(列名)
SELECT****SUM(sal)FROMemp;
薪资总和为30000
Min(列名)
SELECT****MIN(sal)FROMemp;
薪资最小值为5000
Max(列名)
SELECT****MAX(sal)FROMemp;
薪资最大值为9000
Count(列名)
SELECT****COUNT(topid)FROMemp;
数量为4
三、排序和分组数据
1、数据排序
ORDER BY 子句的语法是： select语句 where子句 order by 列名 asc|desc
例如:以薪资排序
默认为asc升序，可指定降序desc
2、分组数据
GROUP BY 子句的语法是： SELECT 语句 where子句 group by 列名 having子句
例如，查询每个部门的最高薪资：
对分组后的数据进行过滤不能用where，要用having
HAVING 子句
where和having都用于筛选过滤，但是：
where用于在分组之前进行筛选, having用于在分组之后进行筛选
并且where中不能使用列别名, having中可以使用列别名
如：按部门分组查询每个部门的平均薪资且平均薪资超过6000的部门信息，显示部门id和每个部门的平均薪资
where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!!
SQL语句的书写顺序：
select...
from...
where...
group by...
having...
order by...
...
SQL语句的执行顺序：
from... -- 确定要查询的是哪张表 (定义表别名)
where... -- 从整张表的数据中进行筛选过滤
group by... -- 根据指定的列进行分组
having... ---分组后进行筛选过滤
select... -- 确定要显示哪些列 (定义列别名)
order by... -- 根据指定的列进行排序
...
第四章、联接查询和子查询
表关系
一对多
一对多，反过来就是多对一，以班级和学生为例：
(1)一个班级中可能会有多个学生(1~*)
(2)一个学生只能属于一个班级(11)，两者合并结果还是1*
因此，班级表和学生表是一对多的关系
对于一对多的两张表，可以在多的一方添加列，保存一的一方的主键，从而保存两张表之间的关系
一对一
以班级和教室为例：
(1)一个班级对应一个教室(1~1)
(2)一个教室也只对应一个班级(11)，两者合并结果还是11
因此，班级表和教室表是一对一的关系
对于一对一关系的两张表，可以在任意一张表中添加列，保存另一张表的主键，从而保存两张表之间的关系
多对多
以学生和老师为例：
(1)一个学生对应多个老师(1~*)
(2)一个老师也对应多个学生(1)，两者合并结果是*
因此，学生表和老师表是多对多的关系
对于多对多的关系，可以拆分成两张一对多的关系，无法在两张表中添加列保存关系，但我们可以添加一张第三方的表（专门保存两张表的关系），保存两张表的主键，从而保存两张表的关系。
一、使用联接查询数据
创建并进入数据库mydb1
CREATE****DATABASE mydb1 CHARSET****UTF8;
USE mydb1;
创建部门表并插入数据
CREATE****TABLEdept(dept_idINTPRIMARYKEY,dept_nameVARCHAR(20));
INSERT****INTOdeptVALUES(1,'软件部'),(2,'销售部'),(3,'人事部');
SELECT*FROMdept;
创建员工表并插入数据
drop****tableemp;
CREATE****TABLEemp(idINTPRIMARYKEY,emp_nameVARCHAR(20)NOT****NULL,jobVARCHAR(10),top_idINT,hdateDATE,salDOUBLE,bonusdouble,dept_idINT);
INSERT****INTOempVALUES
(1,'张三','软件工程师',3,'2002-03-26',5000,null,1),
(2,'张三三','软件工程师',3,'2005-04-27',7000,700,1),
(3,'李四','软件工程师',7,'2001-07-12',9000,500,1),
(4,'王五','销售',6,'2010-03-15',1400,500,2),
(5,'赵六','销售',6,'2011-05-03',3000,1000,2),
(6,'ABC','销售',7,'2021-06-27',10000,500,2),
(7,'小七','总经理',NULL,'1999-05-20',1000,500,NULL);
根据以上SQL语句创建出员工表和部门表
需求：1、查询所有员工数据，显示员工id，姓名，职位和部门名称
如果我们想查询员工的id，姓名，职位和部门名称，该怎么办呢？
只查询一张表显然已经满足不了我们的需求了，此时就需要进行多表关联查询。
可以使用以下类型的联接：
1、使用内联接
SELECT要查询的列名from表1 ,表2 where关联条件
注意如果列名重复，则要用表名.列名
例如：查询员工的id，姓名，职位和部门名称
使用自联接
自己和自己做联接查询。例如：
（自连接查询）列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名
-- 先查询员工表（emp e1）
select name, topid from emp e1;
-- 再查询上级表（还是员工表，emp e2）
select id, name from emp e2;
-- 最后查询员工及其员工的直接上级
select e1.name,e1.topid, e2.id, e2.name
from emp e1, emp e2
where e1.topid = e2.id;
2、外连接查询
左外连接查询
显示左侧表中的所有记录，如果在右侧表中没有对应的记录，则显示为null
语法：
select ...
from a left****joinb **on **(a.id=b.xid)
右外连接查询
-- 查询所有部门和部门下的员工，如果部门下没有员工，显示null
二、使用子查询查询数据
子查询就是一个查询的结果作为另一个查询的条件。
出现在其他语句内部的select语句，称为子查询或内查询。
嵌套其他子查询的外部查询称为外查询或主查询。
如：需要查询薪资比张三高的员工id、姓名、职位和薪资
1、多行子查询（选）
=========IN,any,ALL
1、查询奖金和王五或赵六相同的员工数据
SELECTbonusFROMempWHEREemp_nameIN('王五','赵六');
SELECTFROMempWHEREbonusIN*(SELECTbonusFROMempWHEREemp_nameIN('王五','赵六'));
2、查询薪资大于李四和王五的员工数据
子查询:SELECTsalFROMempWHEREemp_nameIN('李四','王五');
SELECTFROMemp*wheresal>ALL(SELECTsalFROMempWHEREemp_nameIN**('李四','王五'));
3、查询薪资大于李四或王五的员工数据
SELECTFROMemp*wheresal>any(SELECTsalFROMempWHEREemp_nameIN**('李四','王五'));
2、相关子查询exists（选）
EXISTS/NOT EXISTS 后的子查询，此关键字用来判断结果是否存在，
语法 exists(完整的查询语句)，结果： 1 或 0
如：查询有员工的部门名
子查询+非等值连接
查询每个部门平均工资的工资等级
创建并进入数据库mydb1
CREATE****DATABASE mydb1 CHARSET****UTF8;
USE mydb1;
创建部门表并插入数据
CREATE****TABLEdept(dept_idINTPRIMARYKEY,dept_nameVARCHAR(20));
INSERT****INTOdeptVALUES(1,'软件部'),(2,'销售部'),(3,'人事部');
SELECT*FROMdept;
创建员工表并插入数据
drop****tableemp;
CREATE****TABLEemp(idINTPRIMARYKEY,emp_nameVARCHAR(20)NOT****NULL,jobVARCHAR(10),top_idINT,hdateDATE,salDOUBLE,bonusdouble,dept_idINT);
INSERT****INTOempVALUES
(1,'张三','软件工程师',3,'2002-03-26',5000,null,1),
(2,'张三三','软件工程师',3,'2005-04-27',7000,700,1),
(3,'李四','软件工程师',7,'2001-07-12',9000,500,1),
(4,'王五','销售',6,'2010-03-15',1400,500,2),
(5,'赵六','销售',6,'2011-05-03',3000,1000,2),
(6,'ABC','销售',7,'2021-06-27',10000,500,2),
(7,'小七','总经理',NULL,'1999-05-20',1000,500,NULL);
创建薪资等级表
create****tablejob_grade(grade_levelchar(1),lowest_salint,highest_salint);
insert****intojob_gradevalues('A',1000,2999),('B',3000,5999),('C',6000,9999),('D',10000,14999),('E',15000,24999);
select*fromjob_grade;
查询每个部门平均工资的工资等级:
先按部门分组，求出部门的平均工资
拿着结果和工资等级表进行关联查询，求出每个部门平均薪资的薪资等级。
SQL：
SELECTdept_sal.,grade_level*FROM(SELECTdept_id,AVG(sal)avg_salFROMempWHEREdept_id ISNOTNULLGROUPBYdept_id)dept_sal,job_grade
WHEREavg_salBETWEENlowest_salANDhighest_sal;
注意：子查询结果充当的表必须起别名。
第五章、实现视图和索引
一、创建和管理索引
- 索引是帮助MySQL高效获取数据的排好序的数据结构。
- 键的索引会影响到where查找过滤和order by排序。
- 而索引导致数据查询快，更新慢，因为更新不只要更新数据，索引的指向也得跟着变。
索引是帮助MySQL高效获取数据的排好序的数据结构
如果没有索引，MySQL就需要全表扫描，多次IO效率低。
每个节点存排序的数据+对应数据的地址。
1、了解索引类型
我们已经了解了索引是一种排好序的数据结构，提高了我们查找数据和为数据排序的效率。那么MySQL提供了几种索引类型。
主键、外键、唯一（对应的是主键约束、外键约束、唯一约束）
常规索引（添加常规索引的列，并不要求对应列是否重复或为空）
全文索引（要求对应列数据必须为字符串数据类型，对应列也可接受重复或 NULL 值）
一张表建的索引最好不要超过5个
2、创建索引
创建常规索引
在表定义内创建常规索引的语法是：
CREATE TABLE 表名(... index 索引名(索引列1 [,索引列2]));
创建学生表stu，字段有主键id字段，学生姓名stu_name 字段，年龄age字段。请在创建表stu的同时为学生姓名这一字段创建索引，索引名为idx_stu_name
Create index 索引名 on 表名(字段)
Drop index 索引名 on 表名
SHOWINDEXFROMstu;
例如：
CREATE****TABLEstu(
idINTPRIMARYKEY,
stu_nameVARCHAR(20),
INDEXidx_stu_name(stu_name)
);
SHOWINDEXFROMstu;
- create index 索引名 on 表名(索引列1 [,索引列2] );
- alter 表名 add index 索引名 on (索引列1 [,索引列2]);
在上图中：
Table： 此字段指定在其上创建索引的表的名称。
Non_unique： 如果索引列可以包含重复值，则此字段为 1，否则为 0。
Key_name： 此字段指定索引的名称。 主键索引的名称始终为 PRIMARY。
Seq_in_index： 此字段指定索引列的序列号，指定依据为创建表时给出的列的序列号。 列的序列值从 1 开始。
Column_name： 此字段指定索引列的名称。
Collation： 此字段指定是否对索引列中的值进行排序。 它可以包含 A 或NULL 值。 A 指定将以索引列的升序执行排序，NULL 指定不能执行排序。
创建全文索引
创建表时创建全文索引的语法是：
- CREATE TABLE 表名( ... fulltext index 索引名 (索引列)) ;
- alter table 表名 add fulltext index 索引名(列);
- create FULLTEXT INDEX 索引名 on 表(索引列);
CREATE****TABLEft(copy****TEXT,FULLTEXT(copy));
INSERT****INTOft(copy)VALUES('Once upon a time'),
('There was a wicked witch'),('Who ate everybody up');
可在char,varchar,或text类型的列创建全文索引。不过大容量的数据表生成全文索引是一个非常消耗时间和硬盘空间的做法。
全文索引的搜索用 match(索引列)AGAINST('关键词');
3、删除索引
- drop index 索引名 on 表名
- ALTER TABLE <table_name> DROP INDEX <index_name>
查看： show index from表名
二、创建和管理视图
视图其实就是虚拟表，和普通表一样去使用。
是MySQL5.1版本的新特性，是通过普通表动态生成的数据。它只保存SQL逻辑，不保存查询结果。
应用场景：
- 查询结果被频繁地使用
- 查询结果使用的SQL语句较复杂
如我们需要频繁地了解到各部门平均薪资的薪资等级。
selectdept_avg_sal.,grade_level*from
(selectdept_id,avg(sal)sal_avgfromempwheredept_idisnotnullgroupbydept_id)dept_avg_sal,job_grade
wheresal_avgbetweenlowest_salandhighest_sal;
可以将此查询语句封装成一个视图。
语法： create view 视图名 as select语句
会将select语句的结果封装成一个视图。
创建视图：
CREATE****VIEWv1AS
selectdept_avg_sal.,grade_level*from
(selectdept_id,avg(sal)sal_avgfromempwheredept_idisnotnullgroupbydept_id)dept_avg_sal,job_grade
wheresal_avgbetweenlowest_salandhighest_sal;
查看视图：
SHOWCREATEVIEWv1;
使用视图：
SELECT*FROMv1;
修改视图：alter****view视图名as新的查询语句
ALTER****VIEWv1ASSELECTdistinctemp_name,salFROMemp;
SELECT*FROMv1;
删除视图：drop****VIEW视图名,视图名...;
drop****viewv1;
但是注意视图的数据是动态生成的：
我们每次使用视图，都会先执行视图创建时的select语句，将此视图创建出来后再使用
视图的更新
如果视图对应的select语句很简单，如不存在distinct，分组，函数，子查询等等，则可以对视图做更新（增删改）操作，且对视图的更新会影响原始表。但是需要注意的是，视图的出现就是为了查询更方便，我们只会对视图进行select查询操作，不会去进行更新。
不能在视图上创建触发器和索引。
MySQL第六章
一、存储过程
1.作用：用于多次执行一组复合语句
语法：CREATE PROCEDURE 语句
CREATE PROCEDURE 过程名 ([proc_parameter[参数,...]])
BEGIN
复合语句
END
案例: 在数据库中创建名为 NewStu 的存储过程
准备环境：
create****databaseschoolCHARSET****utf8;
useschool;
create****tablestu(stu_idINTprimarykey,stu_namevarchar(20));
insert****intostuvalues(1,'tom');
insert****intostuvalues(2,'jack');
insert****intostuvalues(3,'mary');
select*fromstu;
执行结果：
实现案例：在数据库中创建名为 NewStu 的存储过程
delimiter //
create procedure NewStu()
begin
insert into stu values(4,'lucy');
select * from stu;
end //
delimiter ;
SQL语句详解：
执行存储过程：要执行存储过程，需要使用 CALL 语句。
语法：
CALL 存储过程名 ([parameter[,...]])
例如：
call NewStu;
执行结果：
二、创建参数化的存储过程
创建存储过程的语法：CREATE PROCEDURE 语句
CREATE PROCEDURE 过程名 ([proc_parameter[参数名类型,...]])
BEGIN
复合语句
END
a.参数用于在运行时将值传递给存储过程。
b.可以向存储过程传递以下类型的参数：IN,OUT,INOUT
1) IN
此关键字允许用户向存储过程传递值，也称为输入参数
案例：指定 id和name参数是输入参数。
delimiter //
create procedure NewStu2(id int,name varchar(20))
begin
insert into stu values(id,name);
select * from stu;
end //
delimiter ;
执行此有参数的存储过程：
call NewStu2(5,'zs')
执行结果：
2）OUT
用于将值从过程回传至调用方。 它也称为输出参数。
案例：通过存储过程GetMax，获取id的最大值
delimiter @
create procedure GetMax(out max int)
begin
select max(id) into max from stu;
end @
delimiter ;
这里结束符号改成@是为了告诉大家这个是可以自己定义的
执行存储过程来拿到这个值：
call GetMax(@b);
select @b;
SQL语句详解：
执行结果：
3）INOUT
用于将值传递给过程以及从过程返回值，可充当 IN 和 OUT 参数。
案例：求一个数的平方。
delimiter //
create procedure square(inout num int)
begin
set num=num*num;
end //
delimiter ;
set @result=5;
call square(@result);
select @result;
执行结果：
但是INOUT尽量少用，要传入参数就用IN,要拿到输出的结果就用OUT
总结：
存储过程-多次执行一组复合语句
存储过程可以定义参数-类型有IN , OUT , INOUT
MySQL第七章、实现触发器和事务
一、触发器
触发器可被视为预编译并存储在数据库中的小程序
在相应表上执行 UPDATE、DELETE 或者 INSERT 之类的 DML 语句时，将自动执行它
1、创建触发器的语法：
create trigger触发器名称激活触发器的时间指定执行的操作on表名for each row触发器要执行的复合语句
根据触发器的激活时间，可以分为：BEFORE 触发器、AFTER 触发器
2.创建 BEFORE 触发器：
- 触发器：在执行相应的 DML 语句之前激活。 - INSERT、UPDATE 或者 DELETE 事件类型创建。
创建 BEFORE DELETE 触发器
BEFORE DELETE 触发器在删除与该触发器关联的表中每一个记录之前执行
举例：每当从stu表中删除记录时，将一行添加到 delete_stu 表，其中包含 stu的id值 和 ‘delete’ 标记（对删除的学生id做一个记录）
create table stu_audit (stu_id int primary key,audit_action varchar(10)) charset utf8;
create trigger stu_delete before delete on stu
for each row
insert into stu_audit values(old.id,'delete');
delete from stu where id=1;
select * from stu_audit;
3.创建 AFTER 触发
AFTER 触发器：在执行相应的 DML 语句之后激活。
可针对任何 INSERT、UPDATE 或者 DELETE 事件类型创建
创建 AFTER INSERT 触发器
create trigger触发器名称激活触发器的时间指定执行的操作on表名for each row触发器要执行的复合语句
AFTER INSERT 触发器在将每个记录插入关联表中之后激活
表关系：
需求：在对产品下了订单时，减少产品的库存数量
create trigger on_order after insert on cus_order
for each row
update products set number=number-new.order_number
where product_id=new.product_id;
insert into cus_order values(1002,2,4,2);
select * from products;
执行结果：
引用旧值和新值
表中与触发器关联的列可通过 OLD 和 NEW 关键字引用。
OLD.col_name 引用更新或删除前现有行的列。
NEW.col_name 引用要插入的新行或更新后的现有行的列。
触发事件决定在相应触发器代码中是允许使用 OLD 还是 NEW 关键字
5.删除触发器：
语法：
DROP TRIGGER [IF EXISTS] [数据库名称.]trigger_name
例如：删除在 production_management_system 数据库上定义的 on_order 触发器
DROP TRIGGER [IF EXISTS] production_management_system.on_order;
二、事务
在 MySQL 中，可以通过实现事务来确保数据完整性。
事务表示封装为一个工作逻辑单元的一系列数据操作语句。
事务有四个属性，这些属性通常称为 ACID（原子性、一致性、隔离性和持久性）属性。
1.事务的ACID属性如下：
原子性：
事务中的所有操作都是一个整体，不可以再分割
其中的所有SQL语句，要么全都执行成功， 要么全都执行失败
一致性：事务前后的业务数据总和是保持一致的
隔离性：多个事务之间是相互隔离开来的,一个事务不会访问到另外一个事务正在执行中的操作。
隔离性等级高, 可以保证事务之间完全隔离, 否则可能会出现, 事务之间并不完全隔离的情况!
持久性：如果事务一旦提交(事务中的SQL语句全部都执行成功了), 修改后的数据就真的被保存到了数据库中
为实现 ACID 属性的需求，MySQL 提供了以下功能
2、实现事务
为支持事务，MySQL 提供了 START TRANSACTION、COMMIT 和 ROLLBACK 语句。
START TRANSACTION 语句开始事务。
COMMIT 语句将更改提交给数据库。
ROLLBACK 取消语句所作的任何更改，并将数据库返回到其最近的稳定状态。
事务实现过程
举例：
start transaction;
insert into customer values(6,'赵六');
insert into cus_order values(1006,1,6,1);
rollback;
select * from customer;
select * from cus_order;
执行结果：
3.autocommit：
autocommit：是必须为每个会话设置的会话变量，默认为 1。
可通过以下语句启用和禁用：
SET autocommit = {0 | 1}
默认情况下autocommit变量的值为1，也就是开启自动提交事务~~
4.还原事务：ROLLBACK 语句
ROLLBACK 语句将显式或隐式事务回滚到事务的开始或事务的保存点。
保存点用于将事务划分为较小的部分。
保存点允许您丢弃较长事务的一部分，而不是回滚整个事务
使用保存点：SAVEPOINT 语句：仅回滚事务的一部分。
可以为保存点提供名称以标记事务中到该处为止可以回滚语句的点
标记保存点的语法：
SAVEPOINT保存点名;
回滚到保存点的语法：
ROLLBACK TO SAVEPOINT保存点的名字;
举例：
start transaction;
insert into customer value(7,'小七');
insert into customer value(8,'小八');
savepoint sav1;
update customer set customer_name='zl' where customer_id='6';
savepoint sav2;
rollback to sav1;
select * from customer;
#delete from customer where customer_id in(7,8);
5.设置隔离级别：
MySQL 支持以下隔离级别：
READUNCOMMITTED 隔离级别
READCOMMITTED 隔离级别
REPEATABLEREAD 隔离级别
SERIALIZABLE 隔离级别
设置隔离级别：
语法：
SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL隔离级别；
global:此语句将应用于之后的所有session，而当前session不受影响
session：此语句将应用于当前session内之后的所有事务
如果什么都不写，则此语句将应用于当前session内的下一个还未开始的事务
举例：
set transaction isolation level read committed;
select @@tx_isolation;
MySQL八、九章数据的导入和导出及用户、权限管理
学习目标：导出数据，导入数据，管理用户账户
一、数据的导入和导出
1、导出数据
导出数据：
SELECT 语句的 INTO OUTFILE 变量允许您执行该操作。
它可用于将完整的数据库或数据库的特定部分备份到输出文件
语法：
select语句 into outfile ‘指定文件’
[
fields：针对字段的
terminated by xxx:字段间的分隔符
enclosed by xxx：指用于分隔文本文件中值的字符
escaped by xxx：指用于转义文件中存在的任何特殊的字符
lines：指用于标识文本文件中行的开始和结束的分隔符
starting by：指标识文本文件中行的开始的字符
terminated by：指指示行的结束的字符。默认值是\n,表示换行符
]
例如：
select * from stu into outfile 'e:\stu7.txt'
fields terminated by ','
enclosed by '|'
lines starting by '$$'
terminated by '##';
导出二进制数据：
有时，您需要导出包含 MS Word 文件或图像文件之类二进制数据的一个记录或列。
MySQL 允许在 SELECT 语句中使用 INTO DUMPFILE 选项实现这一点。
语法：
查询语句 into dumpfile ‘文件名称’;
例如：
select name from stu where id=1 into DUMPFILE 'e:/pic';
找到此文件并打开：
2、导入数据
使用 mysqlimport
mysqlimport -u用户名 -p 数据库名称 指定文件
三、数据的备份和恢复
备份数据
备份一个数据库
举例：创建 school 数据库的备份
mysqldump –u root -p school > e:\school.sql
备份数据库中的特定表：
例如：创建school 数据库的 stu表的备份
mysqldump –u root –p school stu > e:\stu.sql
备份多个数据库：
mysqldump –u root –p --databases school test> e:\school_test.sql
要备份 MySQL 服务器中的所有数据库：
mysqldump -uroot -p --all-databases > all.sql
恢复数据
使用mysql命令
语法：
mysql -uroot -p 准备好的数据库 < 指定文件
例如：通过school.sql 转储文件，重新加载 school 数据库中的数据。
注意：数据库school和文件school.sql都必须存在
mysql –u root –p school< e:/school.sql
使用source命令
也可登录mysql后使用source关键字重新加载数据：
source e:/school_test.sql;
source命令可用于通过从 mysql 命令提示符执行 sql 文件中存储的 SQL 语句来将数据导入 MySQL 表。
要使用source 命令，需要决定包含 MySQL 语句和命令的文件的路径和文件名。
source 指定文件;
四、管理用户账户
1.创建用户
CREATE USER 语句：用于创建用户账户
语法：
create****USER'用户名'identified****by[PASSWORD]'密码'
指定主机：'用户名'@'IP地址'
create****USER'用户名'identified****by[PASSWORD]'密码'
指定主机：'用户名'@'IP地址'
1、创建一个用户用户名为zs，密码为zs
CREATE****USER'zs'IDENTIFIED****BY'zs'
新创建的用户，权限很少。
2、创建用户ls并指定主机IP为192.168.2.3,不过不指定则默认为%也就是任意主机
CREATE****USER'ls'@'192.168.2.3'IDENTIFIED****BY 'ls';
希望在创建用户时密码不明文显示
3、创建用户ww指定其主机为本机IP(localhost)，密码为ww但要密文显示
先给密码ww加密
SELECT****PASSWORD('ww');
*192064D30D2DA90C00E499A4719A4ABFA62A9BE7
CREATE****USER'ww'@'localhost'IDENTIFIEDBYPASSWORD'*192064D30D2DA90C00E499A4719A4ABFA62A9BE7';
4、创建用户zl且不设置密码
CREATE****USER'zl';
2.重命名用户账户
RENAME USER 语句：用于重命名用户账户。
语法：
rename user旧用户名 to新用户名
======重命名用户（用户名和主机）
语法：rename****user旧用户名to新用户名
将'ls'@'192.168.2.3'重命名为ls@localhost
RENAME****USER'ls'@'192.168.2.3'TO'ls'@'localhost';
SELECT*FROMmysql.user;
3.更改密码：
MySQL 允许您修改与用户账户关联的密码，或者为没有分配任何密码的账户分配新密码。
语法：
set password [for 用户] = password(密码);
如果不指定用户则为当前登录用户修改密码
====修改用户的密码
语法：set****password [for用户]=PASSWORD(密码);
将用户'ls'@'localhost'的密码改为ls2
SETPASSWORDFOR'ls'@'localhost'=PASSWORD('ls2');
4.删除用户
DROP****USER'zl';
DELETE****FROMmysql.userWHEREUSER='zs';
flush****privileges;
SELECT*FROMmysql.user;
五、管理用户权限
1.向用户账户分配权限，可使用 GRANT 语句。
语法：
grant 授权类型 on 数据库[.表名] to 用户名@host identified by ‘密码’;
关于授权类型：
all privileges:所有权限、select、delete、update、create、drop
====为用户授权
语法：grant授权类型on数据库[.表名]to用户名@hostidentifiedBY'密码';
授权类型：all****privileges:所有权限、select、delete、update、create、drop
如将数据库school中表customer的所有权限赋予zs用户
GRANTallPRIVILEGES****ONschool.customerTOzsIDENTIFIED****BY'zs';
将所有数据库的所有权限赋予用户ww
GRANTALLPRIVILEGES***ON.TOww@localhostidentifiedBYPASSWORD*'*192064D30D2DA90C00E499A4719A4ABFA62A9BE7';
2.查看授权信息：
SHOW GRANTS 语句
可用于查看关于用户账户以及为其分配的权限的信息。
语法：
SHOW GRANTS FOR 用户名
语法：SHOWGRANTSFOR用户;
SHOWGRANTSFOR'ww'@'localhost';
3.撤销权限：
语法：
Revoke 权限类型 on 数据库 from用户名
例如：
====撤回权限
语法：Revoke权限类型on数据库FROM用户名
REVOKE ALLPRIVILEGESON*.*from'ww'@'localhost';
版权归原作者 夏天就是麻辣小龙虾 所有， 如有侵权，请联系我们删除。