mysql基础

1.SQL 包含4部分

数据定义语言（DDL）：DROP、CREATE、ALTER 等语句。
数据操作语言（DML）：INSERT（插入）、UPDATE（修改）、DELETE（删除）语句。
数据查询语言（DQL）：SELECT 语句。
数据控制语言（DCL）: GRANT、REVOKE、COMMIT、ROLLBACK 等语句。

2.数据库访问接口

ODBC
ODBC（Open Database Connectivity，开放数据库互连）为访问不同的 SQL 数据库提供了一个共同的接口。。
JDBC
Java Data Base（JDBC，Java 数据库连接）用于 Java 应用程序连接数据库的标准方法。
ADO.NET
ADO.NET 是微软在 .NET 框架下开发设计的一组用于和数据源进行交互的面向对象类库。
PDO
PDO（PHP Data Object）为 PHP 访问数据库定义了一个轻量级的、一致性的接口

3.MySQL 服务器端实用工具

1. mysqld
   SQL 后台程序（即 MySQL 服务器进程）。该程序必须运行之后，客户端才能通过连接服务器来访问数据库。
2. mysqld_safe
   服务器启动脚本。在 UNIX 和 NewWare 中推荐使用 mysqld_safe 来启动 mysqld 服务器。mysqld_safe 增加了一些安全性，例如，当出现错误时，重启服务器并向错误日志文件中写入运行时间信息。
3. mysql.server
   服务器启动脚本。该脚本用于使用包含为特定级别的、运行启动服务器脚本的、运行目录的系统。它调用 mysqld_safe 来启动 MySQL 服务器。
4. mysqld_multi
   服务器启动脚本，可以启动或停止系统上安装的多个服务器。
5. mysamchk
   用来描述、检查、优化和维护 MyISAM 表的实用工具。
6. mysql.server
   服务器启动脚本。在 UNIX 中的 MySQL 分发版包括 mysql.server 脚本。
7. mysqlbug
   MySQL 缺陷报告脚本。它可以用来向 MySQL 邮件系统发送缺陷报告。
8. mysql_install_db
   该脚本用默认权限创建 MySQL 授予权表。通常只是在系统上首次安装 MySQL 时执行一次。

4.MySQL 客户端实用工具

1. myisampack
   压缩 MyISAM 表以产生更小的只读表的一个工具。
2. mysql
   交互式输入 SQL 语句或从文件经批处理模式执行它们的命令行工具。
3. mysqlacceess
   检查访问主机名、用户名和数据库组合的权限的脚本。
4. mysqladmin
   执行管理操作的客户程序，例如创建或删除数据库、重载授权表、将表刷新到硬盘上以及重新打开日志文件。Mysqladmin 还可以用来检索版本、进程以及服务器的状态信息。
5. mysqlbinlog
   从二进制日志读取语句的工具。在二进制日志文件中包含执行过的语句，可用来帮助系统从崩溃中恢复。
6. mysqlcheck
   检查、修复、分析以及优化表的表维护客户程序。
7. mysqldump
   将 MySQL 数据库转储到一个文件（例如 SQL 语句或 Tab 分隔符文本文件）的客户程序。
8. mysqlhotcopy
   当服务器在运行时，快速备份 MyISAM 或 ISAM 表的工具。
9. mysql import
   使用 LOAD DATA INFILE 将文本文件导入相应的客户程序。
10. mysqlshow
    显示数据库、表、列以及索引相关信息的客户程序。
11. perror
    显示系统或 MySQL 错误代码含义的工具。

5.登录mysql

mysql -h 127.0.0.1 -u root -p

6.Mysql创建数据库

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>] 
[[DEFAULT] COLLATE <校对规则名>];
CREATE DATABASE IF NOT EXISTS mydb
    -> DEFAULT CHARACTER SET utf8
    -> DEFAULT COLLATE utf8_chinese_ci;

7.查看数据库

SHOW DATABASES [LIKE '数据库名'];
SHOW DATABASES;

8.修改数据库

ALTER DATABASE [数据库名] { 
[ DEFAULT ] CHARACTER SET <字符集名> |
[ DEFAULT ] COLLATE <校对规则名>}

9.删除数据库

DROP DATABASE [ IF EXISTS ] <数据库名>
DROP DATABASE mydb；

10.选择数据库

USE <数据库名>
use mydb；

11.存储引擎

功能	MylSAM	MEMORY	InnoDB	Archive
存储限制	256TB	RAM	64TB	None
支持事务	No	No	Yes	No
支持全文索引	Yes	No	No	No
支持树索引	Yes	Yes	Yes	No
支持哈希索引	No	Yes	No	No
支持数据缓存	No	N/A	Yes	No
支持外键	No	No	Yes	No

12.数据类型

1. 数值类型
   整数类型包括 TINYINT、SMALLINT、MEDIUMINT、INT、BIGINT，浮点数类型包括 FLOAT 和 DOUBLE，定点数类型为 DECIMAL。
2. 日期/时间类型
   包括 YEAR、TIME、DATE、DATETIME 和 TIMESTAMP。
3. 字符串类型
   包括 CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM 和 SET 等。
4. 二进制类型
   包括 BIT、BINARY、VARBINARY、TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。

13.整数类型

类型名称	说明	存储需求
TINYINT	很小的整数	1个字节
SMALLINT	小的整数	2个宇节
MEDIUMINT	中等大小的整数	3个字节
INT (INTEGHR)	普通大小的整数	4个字节
BIGINT	大整数	8个字节

14.小数类型

类型名称	说明	存储需求
FLOAT	单精度浮点数	4 个字节
DOUBLE	双精度浮点数	8 个字节
DECIMAL (M, D)，DEC	压缩的“严格”定点数	M+2 个字节

15.日期和时间

类型名称	日期格式	日期范围	存储需求
YEAR	YYYY	1901 ~ 2155	1 个字节
TIME	HH:MM:SS	-838:59:59 ~ 838:59:59	3 个字节
DATE	YYYY-MM-DD	1000-01-01 ~ 9999-12-3	3 个字节
DATETIME	YYYY-MM-DD HH:MM:SS	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59	8 个字节
TIMESTAMP	YYYY-MM-DD HH:MM:SS	1980-01-01 00:00:01 UTC ~ 2040-01-19 03:14:07 UTC	4 个字节

16.字符串类型

类型名称	说明	存储需求
CHAR(M)	固定长度非二进制字符串	M 字节，1<=M<=255
VARCHAR(M)	变长非二进制字符串	L+1字节，在此，L< = M和 1<=M<=255
TINYTEXT	非常小的非二进制字符串	L+1字节，在此，L<2^8
TEXT	小的非二进制字符串	L+2字节，在此，L<2^16
MEDIUMTEXT	中等大小的非二进制字符串	L+3字节，在此，L<2^24
LONGTEXT	大的非二进制字符串	L+4字节，在此，L<2^32
ENUM	枚举类型，只能有一个枚举字符串值	1或2个字节，取决于枚举值的数目 (最大值为65535)

17.二进制类型

类型名称	说明	存储需求
BIT(M)	位字段类型	大约 (M+7)/8 字节
BINARY(M)	固定长度二进制字符串	M 字节
VARBINARY (M)	可变长度二进制字符串	M+1 字节
TINYBLOB (M)	非常小的BLOB	L+1 字节，在此，L<2^8
BLOB (M)	小 BLOB	L+2 字节，在此，L<2^16
MEDIUMBLOB (M)	中等大小的BLOB	L+3 字节，在此，L<2^24
LONGBLOB (M)	非常大的BLOB	L+4 字节，在此，L<2^32

18.创建表

CREATE TABLE <表名> ([表定义选项])[表选项][分区选项];
CREATE TABLE student
    -> (
    -> id INT(11),
    -> name VARCHAR(25),
    -> age INT(11),
    -> goal FLOAT
    -> );

19.修改数据表

ALTER TABLE <表名> [修改选项]
{ ADD COLUMN <列名> <类型>
| CHANGE COLUMN <旧列名> <新列名> <新列类型>
| ALTER COLUMN <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
| MODIFY COLUMN <列名> <类型>
| DROP COLUMN <列名>
| RENAME TO <新表名> }

 ALTER TABLE student
    -> ADD COLUMN address VARCHAR(50);

20.删除数据库表

DROP TABLE [IF EXISTS] 表名1 [ ,表名2, 表名3 ...]
DROP TABLE student;

21.主键

CREATE TABLE student
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> age INT(11),
    -> goal FLOAT
    -> );

22.外键

 CREATE TABLE teacher
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(25),
    -> deptId INT(11),
    -> salary FLOAT,
    -> CONSTRAINT fk_emp_student
    -> FOREIGN KEY(deptId) REFERENCES student(id)
    -> );

23.默认值

<字段名> <数据类型> DEFAULT <默认值>;

24.查看表约束

SHOW CREATE TABLE <数据表名>;

25.查询表格

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

select * from student;

26.mysql去重

SELECT DISTINCT <字段名> FROM <表名>;

27.设置别名

<表名> [AS] <别名>
SELECT stu.name,stu.age FROM student AS stu;

28.限制查询条数

<LIMIT> [<位置偏移量>,] <行数>
SELECT * FROM student LIMIT 4;

29.对查询结果进行排序

ORDER BY {<列名> | <表达式> | <位置>} [ASC|DESC]
SELECT * FROM student ORDER BY age;

30.mysql条件查询

WHERE <查询条件> {<判定运算1>，<判定运算2>，…}
SELECT * FROM student where age>22;

31.mysql常用运算符

算术运算符

算术运算符 说明

+	加法运算
-	减法运算
*	乘法运算
/	除法运算，返回商
%	求余运算，返回余数

比较运算符

比较运算符	说明
=	等于
<	小于
<=	小于等于
>	大于
>=	大于等于
<=>	安全的等于，不会返回 UNKNOWN
<> 或!=	不等于
IS NULL 或 ISNULL	判断一个值是否为 NULL
IS NOT NULL	判断一个值是否不为 NULL
LEAST	当有两个或多个参数时，返回最小值
GREATEST	当有两个或多个参数时，返回最大值
BETWEEN AND	判断一个值是否落在两个值之间
IN	判断一个值是IN列表中的任意一个值
NOT IN	判断一个值不是IN列表中的任意一个值
LIKE	通配符匹配
REGEXP	正则表达式匹配

逻辑运算符

逻辑运算符	说明
NOT 或者 !	逻辑非
AND 或者 &&	逻辑与
OR 或者 ||	逻辑或
XOR	逻辑异或

位运算符

位运算符	说明
|	按位或
&	按位与
^	按位异或
<<	按位左移
>>	按位右移
~	按位取反，反转所有比特

32.内连接 显示的记录是由两个表中的不同列值组成的新记录

SELECT <列名1，列名2 …>
FROM <表名1> INNER JOIN <表名2> [ ON子句]
SELECT id,name,age,salary
   -> FROM teacher,student
   -> WHERE student.id=teacher.id;

33.外连接

SELECT name,salary
    -> FROM student s
    -> LEFT OUTER JOIN teacher d
    -> ON s.id = d.id;

左外连接又称为左连接，在 FROM 子句中使用关键字 LEFT OUTER JOIN 或者 LEFT JOIN，用于接收该关键字左表（基表）的所有行，并用这些行与该关键字右表（参考表）中的行进行匹配，即匹配左表中的每一行及右表中符合条件的行。
在左外连接的结果集中，除了匹配的行之外，还包括左表中有但在右表中不匹配的行，对于这样的行，从右表中选择的列的值被设置为 NULL，即左外连接的结果集中的 NULL 值表示右表中没有找到与左表相符的记录。

SELECT name,salary
    -> FROM students s
    -> RIGHT OUTER JOIN teacher d
    -> ON s.id = d.id;

右外连接又称为右连接，在 FROM 子句中使用 RIGHT OUTER JOIN 或者 RIGHT JOIN。与左外连接相反，右外连接以右表为基表，连接方法和左外连接相同。在右外连接的结果集中，除了匹配的行外，还包括右表中有但在左表中不匹配的行，对于这样的行，从左表中选择的值被设置为 NULL。

34.子查询中常用的运算符

IN子查询 <表达式> [NOT] IN <子查询>
比较运算符子查询 <表达式> {= | < | > | >= | <= | <=> | < > | != }
{ ALL | SOME | ANY} <子查询>
EXIST子查询 EXIST <子查询>

SELECT id FROM students
    -> WHERE id IN
    -> (SELECT id
    -> FROM teacher
    -> );

35.分组查询

GROUP BY { <列名> | <表达式> | <位置> } [ASC | DESC]
SELECT id,GROUP_CONCAT(name) AS names
    -> FROM student
    -> GROUP BY age;

36.指定过滤条件

HAVING <条件>
HAVING 子句和 WHERE 子句非常相似，HAVING 子句支持 WHERE 子句中所有的操作符和语法，但是两者存在几点差异：
WHERE 子句主要用于过滤数据行，而 HAVING 子句主要用于过滤分组，即 HAVING 子句基于分组的聚合值而不是特定行的值来过滤数据，主要用来过滤分组。
WHERE 子句不可以包含聚合函数，HAVING 子句中的条件可以包含聚合函数。
HAVING 子句是在数据分组后进行过滤，WHERE 子句会在数据分组前进行过滤。WHERE 子句排除的行不包含在分组中，可能会影响 HAVING 子句基于这些值过滤掉的分组。

SELECT id,GROUP_CONCAT(name) AS names
    -> FROM tudent
    -> GROUP BY id
    -> HAVING COUNT(age)>1;

37.正则表达式匹配

选项 说明 例子 匹配值示例

^	匹配文本的开始字符	'^b' 匹配以字母 b 开头 的字符串	book、big、banana、 bike
$	匹配文本的结束字符	'st$’ 匹配以 st 结尾的字 符串	test、resist、persist
.	匹配任何单个字符	'b.t’ 匹配任何 b 和 t 之间有一个字符	bit、bat、but、bite
*	匹配零个或多个在它前面的字 符	'f*n’ 匹配字符 n 前面有 任意个字符 f	fn、fan、faan、abcn
+	匹配前面的字符 1 次或多次	'ba+’ 匹配以 b 开头，后 面至少紧跟一个 a	ba、bay、bare、battle
<字符串>	匹配包含指定字符的文本	'fa’	fan、afa、faad
[字符集合]	匹配字符集合中的任何一个字 符	'[xz]'匹配 x 或者 z	dizzy、zebra、x-ray、 extra
[^]	匹配不在括号中的任何字符	'[^abc]’ 匹配任何不包 含 a、b 或 c 的字符串	desk、fox、f8ke
字符串{n,}	匹配前面的字符串至少 n 次	b{2} 匹配 2 个或更多 的 b	bbb、 bbbb、 bbbbbbb
字符串
{n,m}	匹配前面的字符串至少 n 次， 至多 m 次	b{2,4} 匹配最少 2 个， 最多 4 个 b	bbb、 bbbb

 SELECT * FROM student
    -> WHERE name REGEXP '^C';

38.插入数据

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];
INSERT INTO <表名>
SET <列名1> = <值1>,
        <列名2> = <值2>,
INSERT INTO student
    -> (name,age)
    -> VALUES('zhangsan',24);

39.修改数据

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]
UPDATE student
    -> SET age=24 where id=1;

40.删除表数据

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
 DELETE FROM student
    -> WHERE id=4;

41.视图

视图是一个虚拟表，其内容由查询定义。同真实表一样，视图包含一系列带有名称的列和行数据，但视图并不是数据库真实存储的数据表。

42.创建视图

CREATE VIEW <视图名> AS <SELECT语句>
CREATE VIEW view_students_info
    -> AS SELECT * FROM student;

多表创建视图

mysql> create view v_match
    -> as 
    -> select a.PLAYERNO,a.NAME,MATCHNO,WON,LOST,c.TEAMNO,c.DIVISION
    -> from 
    -> PLAYERS a,MATCHES b,TEAMS c
    -> where a.PLAYERNO=b.PLAYERNO and b.TEAMNO=c.TEAMNO;

43.修改视图

 ALTER VIEW view_students_info
    -> AS SELECT id,name
    -> FROM teacher;

44.删除视图

DROP VIEW <视图名1> [ , <视图名2> …]
DROP VIEW IF EXISTS view_students_info;

45.函数

自定义函数是一种与存储过程十分相似的过程式数据库对象。它与存储过程一样，都是由 SQL 语句和过程式语句组成的代码片段，并且可以被应用程序和其他 SQL 语句调用。

自定义函数与存储过程之间存在几点区别：
自定义函数不能拥有输出参数，这是因为自定义函数自身就是输出参数；而存储过程可以拥有输出参数。
自定义函数中必须包含一条 RETURN 语句，而这条特殊的 SQL 语句不允许包含于存储过程中。
可以直接对自定义函数进行调用而不需要使用 CALL 语句，而对存储过程的调用需要使用 CALL 语句。

CREATE FUNCTION <函数名> ( [ <参数1> <类型1> [ , <参数2> <类型2>] ] … )
  RETURNS <类型>
  <函数主体>
CREATE FUNCTION StuNameById()
    -> RETURNS VARCHAR(45)
    -> RETURN
    -> (SELECT name FROM student
    -> WHERE id=1);

成功创建自定义函数后，就可以如同调用系统内置函数一样，使用关键字 SELECT 调用用户自定义的函数，语法格式为：

SELECT <自定义函数名> ([<参数> [,...]])
SELECT StuNameById();

修改自定义函数
可以使用 ALTER FUNCTION 语句来修改自定义函数的某些相关特征。若要修改自定义函数的内容，则需要先删除该自定义函数，然后重新创建。
删除自定义函数

DROP FUNCTION [ IF EXISTS ] <自定义函数名>

46.创建存储过程

CREATE PROCEDURE <过程名> ( [过程参数[,…] ] ) <过程体>
[过程参数[,…] ] 格式
[ IN | OUT | INOUT ] <参数名> <类型>
CREATE PROCEDURE ShowStuName()
    -> BEGIN
    -> SELECT * FROM student;
    -> END //

47.修改存储过程

ALTER PROCEDURE 存储过程名 [ 特征 ... ]

48.删除存储过程

DROP { PROCEDURE | FUNCTION } [ IF EXISTS ] <过程名>
DROP PROCEDURE ShowStuName;

50.创建触发器

CREATE <触发器名> < BEFORE | AFTER >
<INSERT | UPDATE | DELETE >
ON <表名> FOR EACH Row<触发器主体>

SELECT * FROM tb_emp8;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(22) | YES  | UNI | NULL    |       |
| deptId | int(11)     | NO   | MUL | NULL    |       |
| salary | float       | YES  |     | 0       |       |
+--------+-------------+------+-----+---------+-------+
CREATE TRIGGER SumOfSalary
    -> BEFORE INSERT ON tb_emp8
    -> FOR EACH ROW
    -> SET @sum=@sum+NEW.salary;
mysql> INSERT INTO tb_emp8
    -> VALUES(1,'A',1,1000),(2,'B',1,500);
Query OK, 2 rows affected (0.09 sec)
Records: 2  Duplicates: 0  Warnings: 0
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 1500 |
+------+
1 row in set (0.03 sec)

51.修改删除触发器

DROP TRIGGER [ IF EXISTS ] [数据库名] <触发器名>

52.索引

为什么要使用索引

索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1. 顺序访问

顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
2) 索引访问

索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。

例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。
索引的分类

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。

1. B-树索引

B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：
叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。

基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。

B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：
查询必须从索引的最左边的列开始。
查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
存储引擎不能使用索引中范围条件右边的列。
2) 哈希索引

哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。

哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。

HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：
MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。
不能使用 HASH 索引排序。
HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

1. 普通索引

普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
2) 唯一性索引

唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。

创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。
3) 主键索引

主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。
注意：主键是数据库考察的重点。注意每个表只能有一个主键。
4) 空间索引

空间索引主要用于地理空间数据类型 GEOMETRY。
5) 全文索引

全文索引只能在 VARCHAR 或 TEXT 类型的列上创建，并且只能在 MyISAM 表中创建。

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。
单列索引就是索引只包含原表的一个列。
组合索引也称为复合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。
提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。
为了提高索引的应用性能，MySQL中的索引可以根据具体应用采用不同的索引策略。这些索引策略所对应的索引类型有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引等。
索引的使用原则和注意事项

虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
注意：索引可以在一些情况下加速查询，但是在某些情况下，会降低效率。
索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则：
在经常需要搜索的列上建立索引，可以加快搜索的速度。
在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。

与此对应，在某些应用场合下建立索引不能提高 MySQL 的工作效率，甚至在一定程度上还带来负面效应，降低了数据库的工作效率，一般来说不适合创建索引的环境如下：
对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。

53.创建索引

1. 使用 CREATE INDEX 语句

CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])

2. 使用 CREATE TABLE 语句

CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

3. 使用 ALTER TABLE 语句

ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
 CREATE TABLE tb_stu_info
    -> (
    -> id INT NOT NULL,
    -> name CHAR(45) DEFAULT NULL,
    -> age INT DEFAULT NULL,
    -> INDEX(name)
    -> );

54.修改或删除索引

1. 使用 DROP INDEX 语句

DROP INDEX <索引名> ON <表名>

2. 使用 ALTER TABLE 语句
   根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。
   DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
   DROP INDEX index_name：表示删除名称为 index_name 的索引。
   DROP FOREIGN KEY fk_symbol：表示删除外键。

55.创建用户

CREATE USER <用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
CREATE USER 'user'@'localhost'
    -> IDENTIFIED BY 'passwd';

56.修改用户

修改用户账号

RENAME USER <旧用户> TO <新用户>

修改用户口令

SET PASSWORD [ FOR <用户名> ] =
{
    PASSWORD('新明文口令')
    | OLD_PASSWORD('旧明文口令')
    | '加密口令值'
}

57.删除用户

DROP USER <用户名1> [ , <用户名2> ]…
DROP USER user;

58.授予用户权限

GRANT
<权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]
ON <对象> <权限级别> TO <用户>
其中<用户>的格式：
<用户名> [ IDENTIFIED ] BY [ PASSWORD ] <口令>
[ WITH GRANT OPTION]
| MAX_QUERIES_PER_HOUR <次数>
| MAX_UPDATES_PER_HOUR <次数>
| MAX_CONNECTIONS_PER_HOUR <次数>
| MAX_USER_CONNECTIONS <次数>

语法说明如下：

1. <列名>

可选项。用于指定权限要授予给表中哪些具体的列。
2) ON 子句

用于指定权限授予的对象和级别，如在 ON 关键字后面给出要授予权限的数据库名或表名等。
3) <权限级别>

用于指定权限的级别。可以授予的权限有如下几组：
列权限，和表中的一个具体列相关。例如，可以使用 UPDATE 语句更新表 students 中 student_name 列的值的权限。
表权限，和一个具体表中的所有数据相关。例如，可以使用 SELECT 语句查询表 students 的所有数据的权限。
数据库权限，和一个具体的数据库中的所有表相关。例如，可以在已有的数据库 mytest 中创建新表的权限。
用户权限，和 MySQL 中所有的数据库相关。例如，可以删除已有的数据库或者创建一个新的数据库的权限。

对应地，在 GRANT 语句中可用于指定权限级别的值有以下几类格式：
：表示当前数据库中的所有表。
.：表示所有数据库中的所有表。
db_name.：表示某个数据库中的所有表，db_name 指定数据库名。
db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。
tbl_name：表示某个表或视图，tbl_name 指定表名或视图名。
db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。
TO 子句：用来设定用户口令，以及指定被赋予权限的用户 user。若在 TO 子句中给系统中存在的用户指定口令，则新密码会将原密码覆盖；如果权限被授予给一个不存在的用户，MySQL 会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户指定口令。

GRANT语句中的<权限类型>的使用说明如下：

1. 授予数据库权限时，<权限类型>可以指定为以下值：

SELECT：表示授予用户可以使用 SELECT 语句访问特定数据库中所有表和视图的权限。
INSERT：表示授予用户可以使用 INSERT 语句向特定数据库中所有表添加数据行的权限。
DELETE：表示授予用户可以使用 DELETE 语句删除特定数据库中所有表的数据行的权限。
UPDATE：表示授予用户可以使用 UPDATE 语句更新特定数据库中所有数据表的值的权限。
REFERENCES：表示授予用户可以创建指向特定的数据库中的表外键的权限。
CREATE：表示授权用户可以使用 CREATE TABLE 语句在特定数据库中创建新表的权限。
ALTER：表示授予用户可以使用 ALTER TABLE 语句修改特定数据库中所有数据表的权限。
SHOW VIEW：表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
CREATE ROUTINE：表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
ALTER ROUTINE：表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
INDEX：表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
DROP：表示授予用户可以删除特定数据库中所有表和视图的权限。
CREATE TEMPORARY TABLES：表示授予用户可以在特定数据库中创建临时表的权限。
CREATE VIEW：表示授予用户可以在特定数据库中创建新的视图的权限。
EXECUTE ROUTINE：表示授予用户可以调用特定数据库的存储过程和存储函数的权限。
LOCK TABLES：表示授予用户可以锁定特定数据库的已有数据表的权限。
ALL 或 ALL PRIVILEGES：表示以上所有权限。
2) 授予表权限时，<权限类型>可以指定为以下值：

SELECT：授予用户可以使用 SELECT 语句进行访问特定表的权限。
INSERT：授予用户可以使用 INSERT 语句向一个特定表中添加数据行的权限。
DELETE：授予用户可以使用 DELETE 语句从一个特定表中删除数据行的权限。
DROP：授予用户可以删除数据表的权限。
UPDATE：授予用户可以使用 UPDATE 语句更新特定数据表的权限。
ALTER：授予用户可以使用 ALTER TABLE 语句修改数据表的权限。
REFERENCES：授予用户可以创建一个外键来参照特定数据表的权限。
CREATE：授予用户可以使用特定的名字创建一个数据表的权限。
INDEX：授予用户可以在表上定义索引的权限。
ALL 或 ALL PRIVILEGES：所有的权限名。
3) 授予列权限时，<权限类型>的值只能指定为 SELECT、INSERT 和 UPDATE，同时权限的后面需要加上列名列表 column-list。

4. 最有效率的权限是用户权限。

授予用户权限时，<权限类型>除了可以指定为授予数据库权限时的所有值之外，还可以是下面这些值：
CREATE USER：表示授予用户可以创建和删除新用户的权限。
SHOW DATABASES：表示授予用户可以使用 SHOW DATABASES 语句查看所有已有的数据库的定义的权限。

【实例】使用 GRANT 语句创建一个新的用户 testUser，密码为 testPwd。用户 testUser 对所有的数据有查询、插入权限，并授予 GRANT 权限。输入的 SQL 语句和执行过程如下所示。

mysql> GRANT SELECT,INSERT ON *.*
    -> TO 'testUser'@'localhost'
    -> IDENTIFIED BY 'testPwd'
    -> WITH GRANT OPTION;
Query OK, 0 rows affected, 1 warning (0.05 sec)

59.删除用户权限

1. 第一种：

REVOKE <权限类型> [ ( <列名> ) ] [ , <权限类型> [ ( <列名> ) ] ]…

ON <对象类型> <权限名> FROM <用户1> [ , <用户2> ]…
2) 第二种：

REVOKE ALL PRIVILEGES, GRANT OPTION
FROM user <用户1> [ , <用户2> ]…

mysql> REVOKE INSERT ON *.*
    -> FROM 'testUser'@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT Host,User,Select_priv,Insert_priv,Grant_priv
    -> FROM mysql.user
    -> WHERE User='testUser';
+-----------+----------+-------------+-------------+------------+
| Host      | User     | Select_priv | Insert_priv | Grant_priv |
+-----------+----------+-------------+-------------+------------+
| localhost | testUser | Y           | N           | Y          |
+-----------+----------+-------------+-------------+------------+
1 row in set (0.00 sec)

60.事务

事务具有 4 个特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持续性（Durability）。这 4 个特性简称为 ACID 特性。

1. 原子性
   事务中的操作要么全部执行，要么全都不执行。
2. 一致性
   事务开始之前，数据库处于一致性的状态；事务结束后，数据库必须仍处于一致性状态。数据库一致性的定义是由用户负责的。例如，在银行转账中，用户可以定义转账前后两个账户金额之和保持不变。
3. 隔离性
   系统必须保证事务不受其他并发执行事务的影响，即当多个事务同时运行时，各事务之间相互隔离，不可互相干扰。
4. 持久性
   一个已完成的事务对数据所做的任何变动在系统中是永久有效的，即使该事务产生的修改不正确，错误也将一直保持。

61.数据库备份

mysqldump -u root -h host -p dbname > backdb.sql

62.mysql恢复

source backdb.sql

63.MySQL主从复制

1).拉去MySQL5.7镜像
docker pull mysql:5.7
2).用docker构建两个MySQL数据库mydbtest_mysql,yxdata2_mysql
docker run -itd --name mydbtest_mysql -p 3307:3306 -e MYSQL_ROOT_PASSWORD=mydbtest23456 mysql
docker run -itd --name yxdata2_mysql -p 3308:3306 -e MYSQL_ROOT_PASSWORD=mydbtest23456 mysql
3).MySQL master节点处理
3.1)从容器内复制到主机配置文件
docker cp e6363ae53ca0:/etc/mysql/mysql.conf.d/mysqld.cnf  /home/ganzongxin/wsh
3.2)修改配置文件
server-id=1
log_bin=master-bin
log_bin_index=master-bin.index
#binlog_do_db=yx_data
3.3)复制修改好的配置文件到容器
docker cp /home/ganzongxin/wsh/mysqld.cnf e6363ae53ca0:/etc/mysql/mysql.conf.d/mysqld.cnf
3.4)在master主机配置slave节点权限
grant replication slave on *.* to masterbackup@'%' identified by 'mydbtest23456';
3.5)重启master mysql服务器
service mysql restart
3.6)查看主服务器状态以备从服务器使用
show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
4)配置slave服务器
4.1) 修改配置文件
server-id=2
relay-log=slave-relay-bin
relay-log-index=slave-relay-bin.index
#replicate-do-db=yx_data
4.2) 重启slave服务器
service mysql restart
4.3) 配置连接master服务器
change master to master_host='10.42.94.198',master_port=3307,master_user='masterbackup',master_password='mydbtest23456',master_log_file='master-bin.000001',master_log_pos=154
4.4) 启动slave节点
start slave;

跳过复制错误
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;


4.5) 查看状态
show slave status\G
4.6) Slave_IO_Running和Slave_SQL_Running都为yes，则表示同步成功。
创建表格测试
create table test (id int,colum int);
insert into test values (1,2);

注意：一定要有数据库才行




change master to? master_host='master_ip', master_user='user',?master_password='pwd',? master_port=3306,?master_log_file=localhost-bin.000094',? master_log_pos=33622483?;

CHANGE MASTER TO MASTER_LOG_FILE='master-bin.000002', MASTER_LOG_POS=0;

一般是事务回滚造成的：
解决办法：
mysql> stop slave ;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave ;

## 64.mysql常用函数