mysql学习笔记

mysql语句规范
启动停止服务
mysql 登录
参数说明
登录命令
创建用户
修改提示符
mysql 常用命令
数据库相关
创建数据库
修改数据库
删除数据库
存储引擎
默认的存储引擎
数据类型
表的操作
概念
创建表
查看表结构
查看表的详细定义
删除表
修改表
修改表名
增加字段
删除字段
修改字段
表的约束
完整性约束
索引
创建和查看索引
创建和查看普通索引
创建表时创建普通索引
在已经存在的表上创建普通索引
通过SQL语句ALTER TABLE 创建普通索引
唯一索引
创建表时创建唯一索引
在已经存在的表上创建唯一索引
通过ALTER TABLE 创建唯一索引
全文索引
创建表时创建全文索引
已经存在的表上创建全文索引
通过ALTER TABLE 创建全文索引
创建多列索引
创建表时创建多列索引
在已经存在的表创建多列索引
通过ALTER TABLE 创建多列索引
删除索引
视图
为什么使用视图
创建视图
查看视图
删除视图
修改视图
触发器
创建触发器
创建包含多条执行语句的触发器
查看触发器
删除触发器
数据操作（容易忘的）
避免重复数据 DISTINCT
运算数据查询 （+ - * / DIV % MOD）
统计函数
GROUP 分组
限定分组查询(HAVING)
多表查询
笛卡尔积
连接
内连接（INNER JOIN）
自然连接
等值连接
不等连接
外连接（OUTER JOIN）
左外连接
右外连接
全外连接
合并查询数据记录
子查询
返回结果为单行单列和单行多列的子查询
返回结果为多行单列子查询
返回结果为多行多列子查询
运算符
MYSQL常用函数
字符串函数
数值函数
日期和时间函数
存储过程
事务
MySQL事务控制语句
事务隔离级别
READ-UNCOMMITED(读取未提交内容)
READ-COMMITED（读取提交内容）
REPEATABLE-READ(可重复读)
Seraializable(可串行化)
InnoDB锁机制
锁的类型
锁粒度
MySQL安全机制
创建普通用户
执行CREATE USER语句场景用户
执行INSERT 语句来创建用户
执行GRANT 语句来创建用户
修改密码
使用root用户修改密码
root用户修改自己的密码
root用户修改其他用户密码
删除普通帐号
权限管理
查看用户权限
收回用户权限
MySQL日志管理
二进制日志
启动二进制日志
查看二进制日志
停止二进制日志
删除二进制日志
错误日志
启动错误日志
查看错误日志
删除错误日志
通用查询日志
启动通用查询日志
查看通用查询日志
停止通用查询日志
删除通用查询日志
慢查询日志
启动慢查询日志
查看慢查询日志
分析慢查询日志
停止慢查询日志
删除慢查询日志
MySQL备份
通过备份数据文件备份数据库
通过mysqldump实现数据备份
通过复制数据文件还原数据库
通过命令还原数据库
数据库表导出到文本文件
执行 SELECT … INTO OUTFILE 导出到文本文件
执行命令mysqldump导出到文本
实现文本文件导入到数据库表

---

mysql语句规范

• 关键字与函数名称全部大写
• 数据库名称，表名称，字段名称小写
• SQL语句必须以分号结尾

---

启动停止服务

windows 启动mysql服务

net start mysql

windows 停止mysql服务

net stop mysql   

---

mysql 登录

参数说明

-D， –database=name 打开指定数据库
–delimiter = name 指定分隔符
-h, –host=name 服务器名称
-p, –password[=name] 密码
-P, –port=# 端口号
–prompt=name 设置提示符
-u, –user=name 用户名
-v, –version 输出版本信息并退出

登录命令

本地mysql 登录 如果端口没修改 那就不需要-P 连接远程 使用 -h

mysql -uroot -p -P3306

创建用户

···sql
create user 'username'@'%' identified by 'password';

创建的用户还没有任何权限 % 代表可以远程访问 ，可以指定localhost 只运行本地访问，或者直接指定ip，只运行指定的ip访问    
授权：    
```sql
grant all privileges on *.* to username@'%' identified by 'password'

授权所有用户的所有表

修改密码，mysql5.7 user表没有password字段 使用如下语句修改密码：

update mysql.user set authentication_string=password('newpassword') where user='username';

修改提示符

进入mysql 命令模式 修改提示符 prompt localhost> 可以修改左边的提示符

– prompt 提示符
\D 完整日期
\d 当前数据库
\h 服务器名称
\u 当前用户
例如 ： prompt \d@\u> 如果在本机root用户 则提示符为localhost@root>

---

mysql 常用命令

SELECT VERSION(); –显示当前mysql服务器版本
SELECT NOW(); – 显示当前日期
SELECT USER(); –显示当前用户

---

数据库相关

创建数据库

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset_name
{} 必填 []选填
IF NOT EXISTS 数据库不存在则建库，否则就啥事没有（其实有警告信息，但是没返回给客户端）
DEFAULT CHARACTER SET 指定数据库字符集 如果不指定 则 使用数据库服务器的默认字符集

来个简单的创建语句

CREATE DATABASE t1;

怎么知道创建成功了呢？查看当前服务器下数据库列表

SHOW DATABASES;

当建库的指定的数据库名称已经存在，则会报错，添加 IF NOT EXISTS 语句，将不会报错，会有警告

 CREATE DATABASE  IF NOT EXISTS t1;

查询警告信息使用语句

SHOW WARNINGS;

字符集默认使用数据库服务器的，通过语句

SHOW CREATE DATABASE t1;

查看建库的时候的语句以及字符集,建库的时候也可以自己指定字符集

CREATE DATABASE IF NOT EXISTS t2 CHARACTER SET gbk;

上面的语句指定GBK字符集，可以通过show create database t2 查看确认

---

修改数据库

ALTER {DATABASE | SCHEMA} [db_name] [DEFAULT] CHARACTER SET [=] charset_name
修改数据库的字符集
例如：

alter database t2 character set = utf8;

删除数据库

DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
删除数据库 当删除不存在的数据库会报错

简单删除：

drop database t1;

使用IF EXISTS

drop database if exists t1;

---

存储引擎

存储引擎指定了表的类型，即如何存储和索引数据，是否支持事务等，同时存储引擎也决定了表在计算机中的存储方式。
通过语句

SHOW ENGINES;

来查看当前MySQL数据库支持的存储引擎
或者使用下面的语句也可以查看支持的存储引擎

SHOW VARIABLES LIKE 'have%';

默认的存储引擎

• 查询默认存储引擎

  SHOW VARIABLES LIKE 'storage_engine%';

• 修改默认存储引擎
  修改配置文件(不同版本的mysql可能配置名不同),必须重启后生效

  default-storage-engine=INNODB

• 选择存储引擎

特性	MyISAM	InnoDB	MEMORY
存储限制	有	64TB	有
事务安全	不支持	支持	不支持
锁机制	表锁	行锁	表锁
B树索引	支持	支持	支持
哈希索引	不支持	不支持	支持
全文索引	支持	不支持	不支持
集群索引	不支持	支持	支持
数据缓存	未知	支持	支持
索引缓存	支持	支持	支持
数据可压缩	支持	不支持	不支持
空间使用	低	高	N/A
内存使用	低	高	中等
批量插入速度	高	低	高
支持外键	不支持	支持	不支持

数据类型

数据类型	字节	范围	说明
TINYINT	1	自己算
SMALLINT	2	同上
MEDIUMINT	3	同上
INT	4	同上
BIGINT	8	同上
FLOAT	4
DOUBLE	8
DEC(M,D)	M+2	与DOUBLE相同	精确度高，一般存金额啥的
BIT(M)	1~8
DATE	4	1000-01-01 ~ 9999-12-31	日期
DATETIME	8	1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP	4	19700101080001 ~ 2038年的某个时刻	时间戳
TIME	3
YEAR	1		年份
CHAR(M)	M	0 <=M<=255	定长字符串，不管插入的实际长度多少，都是固定长度
VARCHAR(M)	M	0<=M<=65535	可变字符串
TINYTEXT	0~255		值的长度为+2个字节
TEXT	0-65535		值的长度为+2个字节
MEDIUMTEXT	0~167772150		值的长度为+3个字节
LONGTEXT	0~4294967295		值的长度为+4个字节
BINARY(M)	M		允许的长度为0~M
VARBINARY(M)	M		少量的二进制数据可以使用这个类型存储
TINYBLOB	0~255		存二进制数据用的譬如图片啥的
BLOB	0~2E16
MEDIUMBLOB	0~2E24
LONGBLOB	0~2E32

---

表的操作

概念

表是包含数据库中所有数据的数据库对象。
表中的数据库对象包含列，索引和触发器：

• 列（Columns）
  也称属性列，在创建具体表时，必须指定列的名字和数据类型。
• 索引（Indexes）
  是根据是定的数据库列建立起来的顺序，提供了快速访问数据的途径，且可监督表的数据，使其索引所指向的列中的数据不重复。
• 触发器（Triggers）
  是指用户定义的事务命令的集合，当对表的数据进行插入，更新或者删除的时候这组命令就会自动执行，可以用来确保数据的完整性和安全性。

创建表

CREATE TABLE table_name (
属性名 数据类型，
属性名 数据类型，
.
.
.
属性名 数据类型
)

简单建表语句示例：

CREATE TABLE t_dept (
    deptno INT,
    dname VARCHAR(20),
    loc VARCHAR(40)
);

查看表结构

DESCRIBE t_dept;

查看表的详细定义

就是看建表使用的语句

SHOW CREATE TABLE table_name;

SHOW CREATE TABLE t_dept;

删除表

DROP TALBE table_name;

DROP TABLE t_dept;

修改表

修改表名

ALTER TABLE table_name RENAME [TO] new_table_name;

ALTER TABLE t_dept RENAME tab_dept;

增加字段

1. 在表的最后一个位置增加字段

   ALTER TABLE table_name
   ADD 属性名 属性类型;

ALTER TABLE t_dept ADD descri VARCHAR(20);

1. 在表的第一个位置增加字段

   ALTER TABLE table_name ADD 属性名 属性类型 FIRST；

ALTER TABLE t_dept ADD descri VARCHAR(20) FIRST;

1. 在表的指定字段之后添加字段

   ALTER TABLE table_name ADD 属性名 属性类型 AFTER 属性名;

ALTER TABLE t_dept ADD descri VARCHAR(20) AFTER deptno;

删除字段

ALTER TABLE table_name DROP 属性名;

ALTER TABLE t_dept DROP deptno;

修改字段

1. 修改字段类型

   ALTER TABLE table_name MODIFY 属性名 属性字段;

ALTER TABLE t_dept MODIFY deptno VARCHAR(20);

1. 修改字段名字

   ALTER TABLE table_name CHANGE 旧属性名 新属性名 旧数据类型;

ALTER TABLE t_dept CHANGE loc location VARCHAR(40);

1. 同时修改字段的名字和类型

   ALTER TABLE table_name CHANGE 旧属性名 新属性名 新类型;

ALTER TABLE t_dept CHANGE loc location VARCHAR(20);

表的约束

完整性约束

完整性约束关键字	含义
NOT NULL	约束字段不能为空
DEFAULT	字段默认值
UNIQUE KEY(UK)	约束字段唯一
PRIMARY KEY(PK)	字段为主键 记录唯一标识
AUTO_INCREMENT	自动增长
FOREIGN KEY(FK)	外键

1. 非空约束（NOT NULL）

   CREATE TABLE table_name (
   属性名 数据类型 NOT NULL，
   。。。
   );

CREATE TABLE t_dept (
    deptno INT(20) NOT NULL,
    dname VARCHAR(20),
    loc VARCHAR(40)
);

1. 默认值（DEFAULT）

   CREATE TABLE table_name (
   属性名 数据类型 DEFAULT 默认值,
   …
   );

CREATE TALBE t_dept(
        deptno INT NOT NULL,
        dname VARCHAR(20) DEFAULT 'luo',
        loc VARCHAR(40)
);

1. 唯一约束（UNIQUE, UK）

   CREATE TABLE table_name (
   属性名 数据类型 UNIQUE ,
   。。。
   );

CREATE TABLE t_dept(
        deptno INT ,
        dname VARCHAR(20) UNIQUE,
        loc VARCHAR(40)
);

给UK约束设置一个名字

CREATE TABLE t_dept(
        deptno INT,
        dname VARCHAR(20),
        loc VARCHAR(40),
        CONSTRAINT uk_dname UNIQUE(dname)
);

1. 主键（PRIMARY KEY, PK）
   单字段主键

   CREATE TABLE table_name (
   属性名 字段类型 PRIMARY KEY，
   …
   );

CREATE TABLE t_dept (
        deptno INT PRIMARY KEY,
        dname VARCHAR(20),
        loc VARCHAR(40)
);    

给PK约束设置一个名字。

CREATE TABLE t_dept(
        deptno INT ,
        dname VARCHAR(20),
        loc VARCHAR(40),
        CONSTRAINT pk_dname PRIMARY KEY(dname)
);

多字段主键

CREATE TABLE table_name (
属性名 字段类型,
…
[CONSTRAINT 约束名] PRIMARY KEY (属性名，属性名…)
);

CREATE TABLE t_dept(
        deptno INT,
        dname VARCHAR(20),
        loc VARCHAR(40),
        CONSTRAINT pk_dname_deptno PRIMARY KEY(deptno, dname)
);

1. 设置自动增加（AUTO_INCREMENT）

   CREATE TABLE table_name (
   属性名 数据类型 AUTO_INCREMENT,
   …
   );

CREATE TALBE t_dept (
    deptno INT PRIMARY KEY AUTO_INCREMENT,
    dname VARCHAR(20),
    loc VARCHAR(40)
);

1. 外键约束（FOREIGN KEY, FK）

   CREATE TALBE table_name (
   属性名 数据类型,
   。。。
   CONSTRAINT 外键约束名 FOREIGN KEY(属性名） REFERENCES 表名(属性名)
   );

CREATE TABLE t_dept (
    deptno INT PRIMARY KEY,
    dname VARCHAR(20),
    loc VARCHAR(40)
);
CREATE TABLE t_employee(
    empno INT PRIMARY KEY,
    ename VARCHAR(20),
    job VARCHAR(40),
    mgr INT,
    hiredate DATE,
    sal DOUBLE(10,2),
    comm DOUBLE(10,2),
    deptno INT,
    CONSTRANIT fk_deptno FOREIGN KEY(deptno) REFERENCES t_dept(deptno)
);

索引

通过索引对象，可以快速查询到数据库对象表中的特定记录，是一种提高性能的常用方式。
MySQL支持6种索引：

• 普通索引
• 唯一索引
• 全文索引
• 单列索引
• 多列索引
• 空间索引

索引的创建有利有弊，创建索引可以提高查询速度，但是过多创建索引则会占据许多磁盘空间，因此在创建索引之前，必须权衡利弊。
一下情况适合创建索引：

• 经常被查询的字段，即在WHERE 子句中出现的字段
• 在分组的字段，即在GROUP BY字句中出现的字段
• 存在依赖关系的子表和父表之间的联合查询，即主键或外键字段
• 设置唯一完整性约束的字段

以下情况不适合创建索引：

• 在查询中很少被使用的字段
• 拥有许多重复值的字段

创建和查看索引

创建和查看普通索引

创建表时创建普通索引

CREATE TABLE table_name(
属性名 数据类型,
…
属性名 数据类型,
INDEX|KEY [索引名] (属性名1[(长度)] [ASC|DESC])
);

上述语句 INDEX 或 KEY 用来指定字段为索引

CREATE TABLE t_dept(
    deptno INT ,
    dname VARCHAR(20),
    loc VARCHAR(40),
    INDEX index_deptno(deptno)
);

为了校验表t_dept 中索引是否被使用 执行sql语句EXPLAIN

EXPLAIN
    SELECT * FROM t_dept WHERE deptno = 1;

在已经存在的表上创建普通索引

CREATE IDNEX 索引名 ON 表名(属性名 [(长度)] [ASC|DESC]);

CREATE INDEX index_deptno ON t_dept (deptno);

通过SQL语句ALTER TABLE 创建普通索引

ALTER TABLE table_name ADD INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

ALTER TABLE t_depte ADD INDEX index_deptno (deptno);

唯一索引

创建表时创建唯一索引

CREATE TABLE table_name (
属性名 数据类型,
…
UNIQUE INDEX|KEY [索引名] (属性名 [(长度)] [ASC|DESC])
);

CREATE TABLE t_dept(
    deptno INT UNIQUE,
    dname VARCHAR(20),
    loc VARCHAR(40),
    UNIQUE INDEX index_deptno(deptno)
);

在已经存在的表上创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC|DESC]);

CREATE UNIQUE INDEX index_deptno ON t_dept (deptno);

通过ALTER TABLE 创建唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX|KEY 索引名(属性名 [(长度)] [ASC|DESC]);

ALTER TABLE t_dept ADD UNIQUE INDEX index_deptno (deptno);

全文索引

存储引擎MyISAM 才支持全文索引。

创建表时创建全文索引

CREATE TABLE table_name (
属性名 数据类型,
…
FULLTEXT INDEX|KEY [索引名] (属性名 [(长度)] [ASC|DESC])
);

CREATE TABLE t_dept(
        deptno INT,
        dname VARCHAR(20),
        loc VARCHAR(40),
        FULLTEXT INDEX index_loc (loc)
) ENGINE = MyISAM;

已经存在的表上创建全文索引

CREATE FULLTEXT INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC|DESC]);

CREATE FULLTEXT INDEX index_loc ON t_dept (loc);

通过ALTER TABLE 创建全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX|KEY 索引名 (属性名 [(长度)] [ASC|DESC]);

ALTER TABLE t_dept ADD FULLTEXT INDEX index_loc (loc);

创建多列索引

创建表时创建多列索引

CREATE TABLE table_name (
属性名 数据类型,
…
INDEX|KEY [索引名] (属性名1 [(长度)] [ASC|DESC], 属性名n [(长度)] [ASC|DESC])
);

CREATE TABLE t_dept (
        deptno INT,
        dname VARCHAR(20),
        loc VARCHAR(40),
        KEY  index_dname_loc (dname, loc)
);

在已经存在的表创建多列索引

CREATE INDEX 索引名 ON 表名 (属性名1 [(长度)] [ASC|DESC], 属性名n [(长度)] [ASC|DESC])

CREATE INDEX index_dname_loc ON t_dept (dname, loc);

通过ALTER TABLE 创建多列索引

ALTER TABLE table_name ADD KEY|INDEX 索引名 (属性名1 [(长度)] [ASC|DESC], 属性名n [(长度)] [ASC|DESC])

ALTER TABLE t_dept ADD INDEX index_dname_loc (dname,loc);

---

删除索引

语法为：

DROP INDEX index_name ON table_name;

DROP INDEX index_dname_loc ON t_dept;

---

视图

为什么使用视图

数据库关于数据的查询有时候非常复杂，例如表连接，子查询等。当这种查询需要重复使用时，很难保证次次都编写正确，降低了数据库的实用性。
还有，有时候要求只能操作部分字段而不是全部字段，提高SQL语句的复用性和表操作的安全性。
视图的特点如下：

• 视图的列可以来自不同的表，是表的抽象和在逻辑意义上建立的新关系。
• 视图是由基本表（实表）产生的表（虚表）。
• 视图的建立和删除不影响基本表。
• 对视图内容的更新（添加，删除和修改）直接影响基本表。
• 当视图来自多个基本表时，不允许添加和删除数据。

创建视图

CREATE VIEW view_name AS 查询语句;

CREATE VIEW view_selectproduct
    AS 
        SELECT id, name 
            FROM t_product;

查看视图

1. SHOW TABLES
   SHOW TABLES 可以查看表和视图。
2. SHOW TABLES STATUS

   SHOW TABLES STATUS [FROM db_name] [LIKE 'pattern'];

与上面一样，可以显示表和视图的详细信息。

1. SHOW CREATE VIEW语句查看视图定义信息

SHOW CREATE VIEW view_selectproduct;

1. DESCRIBE | DESC 语句查看视图设计信息

   DESCRIBE | DESC viewname;

2. 通过系统表查看系统信息

USE information_schema;
SELECT * 
    FROM views
        WHERE table_name = 'view_selectproduct';

删除视图

DROP VIEW view_name [, view_name] …;

DROP VIEW view_selectproduct;

修改视图

1. CREATE OR REPLACE VIEW 修改视图

CREATE OR REPLACE VIEW view_selectproduct
    AS 
        SELECT name
            FROM t_product;

1. ALTER 语句修改视图

   ALTER VIEW viewname AS 查询语句;

ALTER VIEW view_selectproduct
    AS 
        SELECT name
            FROM t_product;

触发器

触发器的执行不是程序调用，也不是手工启动，而是事件触发。

创建触发器

CREATE TRIGGER trigger_name
BEFORE | AFTRE trigger_event
ON table_name FOR EACH ROW trigger_stmt;

上述语句中，trigger_name 表示触发器名字，BEFORE AFTER 指定触发器执行实际， trigger_event 标识触发事件，包含 DELETE , INSERT, UPDATE语句，table_name 标识触发事件的表名字，FOR EACH ROW 标识任何一条记录上的操作满足触发条件都会触发触发器。

CREATE TRIGGER tri_diarytime
    BEFORE INSERT
        ON t_dept FOR EACH ROW
            INSERT INTO t_diary VALUES(NULL, 't_dept', now());

创建包含多条执行语句的触发器

CREATE TRIGGER trigger_name
BEFORE | AFTER trigger_event
ON table_name FOR EACH ROW
BEGIN
trigger_stmt
END;

在MySQL中，一般情况下使用;座位语句的结束符号，可以在创建触发器时，需要用到;符号座位执行语句的结束符，为了解决该问题，可以使用关键字DELIMITER 暂时修改结束符号。

DELIMITER $$
CREATE TRIGGER tri_diarytime2
    AFTER INSERT
        ON t_dept FOR EACH ROW
            BEGIN
                INSERT INTO t_diary VALUES(NULL, 't_dept', now());
                INSERT INTO t_diary VALUES(NULL, 't_dept', now());
            END
            $$
DELIMITER ;

查看触发器

1. SHOW TRIGGERS查看触发器
2. 查看系统表triggers

USE information_schema;
SELECT * FROM triggers;

删除触发器

1. DROP TRIGGER 删除

DROP TRIGGER tri_diarytime;

数据操作（容易忘的）

避免重复数据 DISTINCT

SELECT DISTINCT job
    FROM t_employee;

运算数据查询 （+ - * / DIV % MOD）

SELECT ename, sal * 12 
    FROM t_employee;

统计函数

• COUNT
• AVG
• SUM
• MAX
• MIN

GROUP 分组

分组的字段一定要有重复值，否则就没有意义，分组一般与统计函数一起使用。

限定分组查询(HAVING)

SELECT deptno, AVG(sal) average, GROUP_CONCAT(ename) enames, COUNT(ename) number
    FROM t_employee
    GROUP BY deptno
    HAVING AVG(sal) > 2000;

GROUP_CONCAT 函数显示每个ename。

---

多表查询

笛卡尔积

笛卡尔积就是没有连接条件表关系返回结果，如果表1有3个记录，表2有7个记录，那么笛卡尔积后，有21个记录

连接

内连接（INNER JOIN）

内链接就是在表关系建立笛卡尔积数据记录中，保留表关系所有匹配的数据记录，舍弃不匹配的记录。
可以分为自然连接，等值连接，不等连接。

sql示例：

SELECT e.empno, e.ename employeename, e.sal, e.job, l.ename loadername
    FROM t_employee e INNER JOIN t_employee l
        ON e.mgr = l.empno;

自然连接

自然连接就是表关系笛卡尔积中，首先根据表关系中相同的字段自动进行记录匹配，然后去掉重复字段，会自动判断相同名称字段，然后进行数据值的匹配。

等值连接

这个好理解，根据等值条件连接

不等连接

那就是不等咯

外连接（OUTER JOIN）

外连接就是在笛卡尔积中，不仅保留所有匹配的记录，还保留部分不匹配记录。

语法：

SELECT field1, field2,…fieldn
FROM join_table_name1
LEFT|RIGTH|FULL [OUTER] JOIN join_tablename2
ON join_condition;

左外连接

保留匹配记录和全部左表记录

右外连接

保留匹配记录和全部右表记录

全外连接

保留匹配记录和左右两表全部记录

合并查询数据记录

UNION | UNION ALL;

子查询

MySQL中虽然可以使用多表查询数据记录，但是却不推荐使用，这是因为连接查询的性能很差，因此出现了连接查询的替代者子查询

返回结果为单行单列和单行多列的子查询

1. 返回结果为单行单列的子查询

SELECT * 
    FROM t_employee
    WHERE sal > (
        SELECT sal
            FROM t_employee
            WHERE ename = 'SMITH');

1. 单行多列子查询

SELECT ename, sal, job 
    FROM t_employee
    WHERE (sal, job) = (
        SELECT sal, job
            FROM t_employee
            WHERE ename = 'SMITH');

返回结果为多行单列子查询

1. 带有关键字IN 的子查询

SELECT *
    FROM t_employee
    WHERE deptno IN (
        SELECT deptno
            FROM t_dept
);
SELECT *
    FROM t_employee
    WHERE deptno NOT IN (
        SELECT deptno
            FROM t_dept
);

1. 带有关键字ANY的子查询
   有三种匹配方式：

• =ANY
  功能和IN一样

• ANY (>=)
  大于任意一个，也就是比里面最小的大就行了

• <ANY(<=)
  小于任意一个，也就是比最大的小就行了

SELECT ename, sal
    FROM t_employee
    WHERE sal >ANY(
        SELECT sal 
            FROM t_employee
            WHERE job='MANAGER'
);

1. 带有关键字ALL的子查询
   匹配方式有：

• ALL(>=)

• <ALL(<=)

SELECT ename, sal
    FROM t_employee
    WHERE sal>=ALL
    SELECT sal
        FROM t_employee
        WHERE job='MANAGER'
);

1. 带有关键字EXISTS的子查询

SELECT *
    FROM t_dept c
    WHERE NOT EXIST(
        SELECT * 
        FROM t_employee
        WHERE deptno = c.deptno);
SELECT *
    FROM t_dept c
    WHERE EXIST(
        SELECT * 
        FROM t_employee
        WHERE deptno = c.deptno);

返回结果为多行多列子查询

一般被当作一张临时表的方式处理。

运算符

运算符	描述	表达式形式
+	加法	x1+x2+..+xn
-	减法	x1- x2
*	乘法	x1 * x2
/(DIV)	除法	x1 / x2 x1 DIV x2
%(MOD)	求余	x1 % x2 x1 MOD x2
>	大于	x1 > x2
<	小于	x1 < x2
= <=>	等于	x1 = x2 x1 <=> x2
!= <>	不等于	x1!= x2 x1 <> x2
=	大于等于	x1 >= x2
<=	小于等于	x1 <= x2
BETWEEN AND	存在与指定范围	x1 BETWEEN m AND n
IS NULL	为空	x1 IN NULL
IN	存在与指定集合	x1 IN (value1, value2…)
LIKE	通配符匹配	x1 LIKE expression
REGEXP	正则表达式匹配	x1 REGEXP regularexpression
AND &&	与	x1 AND x2
OR	或	x1 OR x2
NOT	非	NOT x1
XOR	异或	x1 XOR x2

MYSQL常用函数

字符串函数

函数	功能
CONCAT(str1, str2,…strn)
LOWER(str)	小写
UPPER(str)	大写
LEFT(str, x)	取str 最左边的x个字符
RIGHT(str, x)	取str 最右边的x个字符
LPAD(str, n, pad)	向str左边填充 pad 直到n个字符
RPAD(str, n , pad)	想str右边填充pad 直到n个字符
LTRIM(str)	去掉str 左边空格
RTRIM(str)	去掉str右边空格
REPEAT(str, x)	返回str重复 x次的结果
TRIM(str)	去掉两边空格
SUBSTRING(str, x, y)	截取str从位置x 长度为y的字符串
CONCAT_WS(sep, s1, s2..sn)	合并字符串 指定分隔符，当第一个参数为null返回null，当后面参数有null，忽略null

数值函数

函数	功能
ABS(x)	x的绝对值
CEIL(x)	返回大于x的最大整数
FLOOR(x)	返回小于x的最大整数
MOD(x,y)	x 模 y的值
RAND()	0~1内的随机数
ROUND(x, y)	x 四舍五入后y位小数
TRUNCATE(x, y)	返回x截断为y位小数

日期和时间函数

函数	功能
CURDATE()	当前日期
CURTIME()	当前时间
NOW()	当前日期和时间
UNIX_TIMESTAMP(date)	获取date的unix时间戳
FROM_UNIXTIME()	获取UNIX 时间戳的日期值
WEEK(date)	date的一年中的第几周
YEAR(date)	date 的年
HOUR(time)	time中的小时
MINUTE(time)	time中的分钟
MONTHNAME(date)	date中的月份

存储过程

存储过程和函数可以简单理解为一条或者多条SQL语句的集合。
创建存储过程语法：

CREATE PROCEDURE procedure_name([procedure_parameters, […])
[characteristic…] routine_body

procedure_name 为存储过程名， procedure_parameters 存储过程参数 characteristic 存储过程特性 routine_body 存储过程SQL语句代码，用BEGIN…END来标记开始结束。

事务

支持事务的存储引擎有InnoDB和BDB。
InnoDB支持ACID事务，行级锁和高并发。为了支持事务InnoDB存储引擎引入了与事务处理相关的UNDO日志和REDO日志，同时，事务依赖于MySQL提供的锁机制。

1. REDO日志
   事务执行时需要将执行的事务写入到日志文件里面，对应的文件为REDO日志，当每条SQL进行数据库更新操作时候，首先将REDO日志写入到日志缓冲区。当客户端执行COMMIT命令提交时，日志缓冲区的内容将被刷新到磁盘，日志缓冲区的刷新方式为时间间隔，通过innodb_flush_log_at_trx_commit控制。
   REDO日志对应磁盘上的ib_logfileN文件，默认为5MB，建议设置为512MB以便容纳较大事务。
2. UNDO日志
   主要用于事务异常时候的数据回滚，具体内容就是复制事务前的数据库内容到UNDO缓冲区。然后在合适的时间刷新到磁盘。

MySQL事务控制语句

MySQL中可以用BEGIN开始事务，COMMIT结束事务，ROLLBACK回滚事务。
通过SET AUTOCOMMIT, START TRANSACTION, COMMIT ROLLBACK等语句支持本地事务。

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE]
SET AUTOCOMMIT = [0 | 1]

-- 查看MySQL隔离级别
SHOW VARIABLES LIKE 'tx_isolation';
-- 开启一个事务
BEGIN;
-- 更新一条记录
UPDATE test_1 SET username = 'test' WHERE id = 1;
-- 提交事务
COMMIT;
-- 开启另一个事务
BEGIN;
-- 更新记录
UPDATE test_1 SET username = 'petter' WHERE id = 1;
-- 回滚
ROLLBACK

事务隔离级别

SQL标准定义了4种隔离级别，指定了事务中哪些数据改变其他事务可见，哪些数据改变其他事务不可见。低级别的隔离级别可以支持更高的并发处理，占用的系统资源更少。
事务隔离级别可以使用一下语句设置。

-- 未提交读
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-- 提交读
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 可重复读
SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- 可串行化
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

READ-UNCOMMITED(读取未提交内容)

看名字就知道了，所以事务都可以看到其他未提交事务的执行结果，此隔离级别很少使用。

READ-COMMITED（读取提交内容）

这个大多数数据库系统默认的隔离级别，但不是MySQL默认的隔离级别。
定义：一个事务从开始到提交前所做的任何修改都是不可见的，事务只能看到已经提交事务所做的改变。
会有不可重复读的问题。
也就是一个事务同一条查询语句查询了两次，在这两次查询期间，有其他事务修改了这些数据，所以，这两次查询结果在同一个事务中是不一样的。

REPEATABLE-READ(可重复读)

这是MySQL的默认事务隔离级别。能确保同一事务的多个实例在缤纷发读取数据时，会看到同样的数据行。
但是会有幻读的问题。
在一次事务的两次查询中数据笔数不一致。
第一个事务对表的全部数据进行修改，同事第二个事务插入了一行新的数据，那么就会发生第一个事务结束后发现表中还有没有修改的数据行。

Seraializable(可串行化)

这是最高隔离界别，通过强制事务排序，使之不可能互相冲突，从而解决幻读，简言之，就是在每个读取的数据行上加上共享锁实现，这个级别可能会导致大量超时现象和锁竞争。

InnoDB锁机制

锁的类型

1. 共享锁S
   也就是读锁
2. 排他锁X
   写锁
3. 意向锁
   表锁 ，分为意向共享锁和意向排他锁。

锁粒度

锁的粒度主要分为表锁和行锁。
表锁管理开销最小，并发最小，MyISAM存储引擎使用该锁机制。
行锁可以支持最大的并发，InnoDB存储引擎使用该锁机制。

MySQL安全机制

在具体开发应用中，不应该使用root账户，而应该创建一系列的用户账户。

创建普通用户

执行CREATE USER语句场景用户

语法形式如下：

CREATE USER username [IDENTIFIED BY [PASSWORD] 'password' ]
[,username [IDENTIFIED BY [PASSWORD] 'password' ]]
…

上述语句中，关键字USER用来设置用户帐号名字，IDENTIFIED BY 用来设置密码 值username是用户帐号 有用户名和主机名构成

-- 创建一个用户名为test 密码为123456 的本机用户
CREATE USER 'test'@'localhost' IDENTIFIED BY '123456';

执行INSERT 语句来创建用户

语法：

INSERT INTO user(Host, User, Password) VALUES ('hostname', 'username', PASSWORD('password');

注意：在具体操作的时候由于mysql.user中字段ssl_cipher, x509_issuer,x509_subject没有默认值，所以还需要设置这些字段的值。
注意：mysql5.7 中password字段已经改名了。

-- 作用是一样的
INSERT INTO user(Host, User, Password, ssl_cipher, x509_issuer, x509_subject)
    VALUES('localhost', 'test', PASSWORD('123456'), '', '', '');
FLUSH PRIVILEGES;

执行GRANT 语句来创建用户

语法：

GRANT priv_type ON databasename.tablename
TO username [IDENTIFIED BY [PASSWORD] 'password']
[,username [IDENTIFIED BY [PASSWORD] 'password']]
…

参数priv_type标识设置用户的权限：databasename.tablename 标识权限的范围

GRANT SELECT ON company.t_dept 
    TO 'test'@'localhost' IDENTIFIED BY '123456';    

修改密码

使用root用户修改密码

root用户修改自己的密码

1. mysqladmin

mysqladmin -u username -p password "new_password"

1. SET 命令

-- 必须连接到mysql
SET PASSWORD=PASSWORD('new_password');

1. 更新系统表

UPDATE user SET password = PASSWORD('new_password')
    WHERE user = 'root' AND host = 'localhost';

root用户修改其他用户密码

1. GRANT命令
2. SET 命令

SET PASSWORD FOR
    'username'@'hostname' = PASSWORD('new_password');

1. 更新系统表

UPDATE user SET password = PASSWORD('new_password')
    WHERE user = 'test' AND host = 'localhost';

删除普通帐号

1. DROP USER 语句

DROP USER 'test'@'localhost';

1. 删除系统表记录

DELETE FROM user 
    WHERE user = 'test' AND host = 'localhost';

权限管理

通过GRANT 来实现

GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[,user [IDENTIFIED BY [PASSWORD] 'password']]
…
[WITH with-option[with_option] …]

priv_type 标识权限类型
column_list 标识权限作用的字段 当省略时候，表示作用整个表
database.table 表示数据库中的某个表
user 表示用户 有用户名和主机名构成
IDENTIFIED BY 用来设置密码
with_option 值只能是下面5个中的：

• GRANT OPTION
  被授权的用户可以将权限授权给其他用户
• MAX_QUERIES_PER_HOUR count
  设置每小时可以执行count 次查询
• MAX_UPDATES_PER_HOUR count
  设置每个小时可以执行 count 次更新
• MAX_CONNECTIONS_PER_HOUR count
  设置每个小时可以建立 count 次连接
• MAX_USER_CONNECTIONS count
  单个用户可以同时具有count 个连接

查看用户权限

SHOW GRANTS FOR user

收回用户权限

REVOKE priv_type [(column_list)] ON database.table
FROM user [IDENTIFIED BY [PASSWORD] 'password']

---

MySQL日志管理

mysql支持的日志

• 二进制日志
  以二进制形式记录数据库的各种操作，不记录查询语句。
• 错误日志
  记录MySQL服务器启动，关闭，运行时出错等信息
• 通用查询日志
  记录MySQL服务器启动，关闭信息，客户端连接信息，更新数据记录SQL语句和查询数据记录SQL语句
• 慢查询日志
  记录执行时间超过指定时间的各种操作，通过工具分析慢查询日志可以定位MySQL服务器性能瓶颈所在。

二进制日志

记录了DDL和DML语句

启动二进制日志

修改配置文件

[mysqld]
log-bin[=dir\[filename]]

需要注意的是5.7版本配置二进制日志需要配server_id
如果配置绝对路径还有些问题，导致启动不了
二进制日志默认在mysql安装目录的data目录下filename.number number依次递增.

查看二进制日志

mysqlbinlog filename.number

停止二进制日志

有时候在执行一些sql语句的时候需要停止二进制日志

-- 停止二进制日志
SET SQL_LOG_BIN = 0;
--开启二进制日志
SET SQL_LOG_BIN = 1;

删除二进制日志

• RESET MASTER 删除所有二进制日志
• PURGE MASTER LOGS TO filename.number 删除编号小于number的二进制日志
• PURGE MASTER LOGS BEFORE 'yyyy-mm-dd hh:MM:ss' 删除指定时间前的二进制日志

错误日志

启动错误日志

错误日志默认开启的，在data目录下.err结尾的文件就是错误日志。可以在配置文件中配置

[mysqld]
log_error = filename

查看错误日志

日志是文本格式的，直接打开查看

删除错误日志

-- 会创建一个新的错误日志，然后将旧的错误日志更名为filename.err-old
mysqladmin -u root -p flush-logs

通用查询日志

用来记录所有操作。

启动通用查询日志

在配置文件中

[mysqld]
-- 不同版本的配置名不一样~
log = filename

或者执行语句：

SET GLOBAL general_log = on;

查看通用查询日志

文本编辑器查看~

停止通用查询日志

set global genral_log = off;

删除通用查询日志

mysqladmin -u root -p flush-logs

慢查询日志

启动慢查询日志

配置文件

[mysqld]
# 存储路径
log-slow-queries = filename
# 时间，默认10 s
long-query-time = n

或者语句

set global slow_query_log = on;
set global long_query_time = 3;

查看慢查询日志

文本。。

分析慢查询日志

对应工具为mysqldumpslow.pl
常用参数为
-s 指定排序参数 可选的有 al 平均锁定时间 ar 平均返回记录数 at 平均查询时间
-t 只显示指定行数

停止慢查询日志

set global slow_query_log=off;

删除慢查询日志

mysqladmin -uroot -p flush-logs

MySQL备份

通过备份数据文件备份数据库

MySQL服务器中的数据文件是基于磁盘的文本文件，所以，最简单，最直接的备份操作是将数据文件复制出来，由于MySQL运行期间，文件一直是打开状态，因此只能停止服务器后才能备份。
这中方式不适用与InnoDB存储引擎，适用与MyISAM存储引擎。

通过mysqldump实现数据备份

• 备份一个数据库

-- 如果没有table参数，则备份整个数据库
mysqldump -u username -p dbname    
    table1, table2...tablen
    > backupname.sql

• 备份多个数据库

mysqldump -u username -p --databases
    dbname1, dbname2,...dbnamen
    > backupname.sql

• 备份所有数据库

mysqldump -u username -p --all -databases > backupname.sql

通过复制数据文件还原数据库

版本一样才能还原

通过命令还原数据库

mysqldump -u username -p [dbname] < backupname.sql

数据库表导出到文本文件

执行 SELECT … INTO OUTFILE 导出到文本文件

SELECT [file_name] FROM table_name
    [WHERE condition]
    INTO OUTFILE 'file_name' [OPTION];

OPTION设置响应选项：

• FIELDS TERMINATED BY 'string'
  用来设置字段的分隔符，默认为 '\t'。
• FIELDS ENCLOSED BY 'char'
  用来设置字段值前后的符号，默认情况下不使用任何符号
• FIELDS OPTIONALLY ENCLOSED BY 'char'
  用来设置 CHAR,VARCHAR, TEXT 等字段值的前后符号，默认不使用任何符号
• FIELES ESCAPED BY 'char'
  用来设置转义字符符号，默认为 '\'
• LINES STARTING BY 'char'
  用来设置每行开头的符号，默认不使用任何符号
• LINES TERMINATED BY 'string'
  用来设置每行结束符号，默认使用'\n';

执行命令mysqldump导出到文本

mysqldump -u root -ppassword -T file_directory dbname table_name [option];

option选项有：

• –fields-terminated-by=string
  用来设置字段的分隔符，默认为 '\t'。
• –fields-enclosed-by=char
  用来设置字段值前后的符号，默认情况下不使用任何符号
• –fields-optionally-enclosed-by=char
  用来设置 CHAR,VARCHAR, TEXT 等字段值的前后符号，默认不使用任何符号
• –lines-terminated-by=string
  用来设置每行结束符号，默认使用'\n';

实现文本文件导入到数据库表

1. 执行 LOAD DATA INFILE 命令导入到数据库

LOAD DATA [LOCAL] INFILE file_name INTO TABLE table_name [OPTION];

OPTION设置：

• FIELDS TERMINATED BY 'string'
  用来设置字段的分隔符，默认为 '\t'。
• FIELDS ENCLOSED BY 'char'
  用来设置字段值前后的符号，默认情况下不使用任何符号
• FIELDS OPTIONALLY ENCLOSED BY 'char'
  用来设置 CHAR,VARCHAR, TEXT 等字段值的前后符号，默认不使用任何符号
• FIELES ESCAPED BY 'char'
  用来设置转义字符符号，默认为 '\'
• LINES STARTING BY 'char'
  用来设置每行开头的符号，默认不使用任何符号
• LINES TERMINATED BY 'string'
  用来设置每行结束符号，默认使用'\n';
• IGNORE n LINES
  用来忽略文件的前n行

• SET column=expr
  用来设置列的转换条件。

1. 执行命令mysqlimport 实现导入

mysqlimport -u root -pPassword [--local] dbname file_name [OPTION]

option选项有：

• –fields-terminated-by=string
  用来设置字段的分隔符，默认为 '\t'。
• –fields-enclosed-by=char
  用来设置字段值前后的符号，默认情况下不使用任何符号
• –fields-optionally-enclosed-by=char
  用来设置 CHAR,VARCHAR, TEXT 等字段值的前后符号，默认不使用任何符号
• –lines-terminated-by=string
  用来设置每行结束符号，默认使用'\n';
• –ignore-lines=n
  用来忽略前n行