MySQL5.7学习笔记
1.MySQL连接方式
TCP/IP方式（远程、本地）
Socket（仅本地） mysql -uroot -p -S /tmp/mysql.sock
2.查看连接会话
mysql> show processlist; +----+------+-----------+------+---------+------+----------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------+------------------+ | 2 | root | localhost | NULL | Query | 0 | starting | show processlist | | 3 | root | localhost | NULL | Sleep | 15 | | NULL | +----+------+-----------+------+---------+------+----------+------------------+ 2 rows in set (0.00 sec)
3.逻辑结构物理机构
逻辑结构 ： 库、表
物理机构：.frm文件存储表结构 .ibd存储数据记录和索引
默认64个连续的page称为一个区，为了存储表多个区构成一个段（我们又把表称为段"非分区表"）
mysqld的三层结构：
（1）连接层：提供连接协议，用户验证，提供连接线程
（2）SQL层：SQL语法和SQL_MOD,语义和权限，解析，优化器，
（3）存储引擎层：从磁盘读取数据
4.用户和权限管理
用户的定义： 用户名@'白名单' json@'%' json@'192.168.0.%' json@'192.168.%' json@'10.10.10.0/255.255.255.0'
用户的操作：
mysql> create user json@'192.168.43.0' identified by '123'; Query OK, 0 rows affected (0.00 sec)
mysql> select host,user from mysql.user;
+--------------+-----------+
| host | user |
+--------------+-----------+
| % | root |
| 192.168.43.0 | json |
| localhost | json2 |
| localhost | mysql.sys |
| localhost | root |
+--------------+-----------+
5 rows in set (0.00 sec)
修改用户密码
mysql> alter user json@'192.168.43.0' identified by '1234'; Query OK, 0 rows affected (0.01 sec)
删除用户
mysql> drop user json@'192.168.43.0'; Query OK, 0 rows affected (0.01 sec)
权限管理
常见的权限有：
ALL: 所有权限
SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS,
FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES,
LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW,
SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE
with grant option 给别人授权的权限
授权语句：
grant 权限 on 作用目标 to 用户 identified by 密码 with grant option;
mysql> grant all on *.* to json@'%' identified by '1234'; Query OK, 0 rows affected, 1 warning (0.01 sec)
权限回收
mysql> create user work@'192.168.43.0' identified by '123'; Query OK, 0 rows affected (0.00 sec) mysql> create database worknode; Query OK, 1 row affected (0.05 sec) mysql> grant select ,insert ,update on worknode.* to work@192.168.43.0 identified by '123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show grants for work@192.168.43.0; +-----------------------------------------------------------------------+ | Grants for work@192.168.43.0 | +-----------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'work'@'192.168.43.0' | | GRANT SELECT, INSERT, UPDATE ON `worknode`.* TO 'work'@'192.168.43.0' | +-----------------------------------------------------------------------+ 2 rows in set (0.02 sec)
mysql> revoke update on worknode.* from work@192.168.43.0;
Query OK, 0 rows affected (0.00 sec)
5.MySQL的配置文件
配置文件模版：
#服务器端 [mysqld] user=mysql basedir=/application/mysql datadir=/data/mysql/data socket=/tmp/mysql.sock server_id=6 port=3306 #客户端配置 [mysql] socket=/tmp/mysql.sock
配置文件的读取顺序：
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 从右至左依次读取
[root@#localhost data]# mysqld --help --verbose| grep my.cnf /etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
6.Mysql多实例的搭建
6.1创建配置文件
[root@#localhost ~]# mkdir -p /data/330{7,8,9}/data [root@#localhost ~]# vi /data/3307/my.cnf [mysqld] basedir=/application/mysql datadir=/data/3307/data socket=/data/3307/mysql.sock log_error=/data/3307/mysql.log server_id=7 port=3307 log_bin=/data/3307/mysql.log [root@#localhost ~]# vi /data/3308/my.cnf [mysqld] basedir=/application/mysql datadir=/data/3308/data socket=/data/3308/mysql.sock log_error=/data/3308/mysql.log server_id=8 port=3308 log_bin=/data/3308/mysql.log [root@#localhost ~]# vi /data/3309/my.cnf [mysqld] basedir=/application/mysql datadir=/data/3309/data socket=/data/3309/mysql.sock log_error=/data/3309/mysql.log server_id=9 port=3309 log_bin=/data/3309/mysql.log
6.2初始化三套数据库
[root@#localhost ~]# mv /etc/my.cnf /etc/my.cnf.bak [root@#localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/application/mysql [root@#localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/application/mysql [root@#localhost ~]# mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/application/mysql
6.3修改启动脚本
[root@#localhost ~]# cd /etc/systemd/system [root@#localhost system]# cp mysqld.service mysqld3307.service [root@#localhost system]# cp mysqld.service mysqld3308.service [root@#localhost system]# cp mysqld.service mysqld3309.service [root@#localhost system]# vi mysqld3307.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf [root@#localhost system]# vi mysqld3308.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf [root@#localhost system]# vi mysqld3309.service ExecStart=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
6.4启动数据库
[root@#localhost system]# chown -R mysql.mysql /data/* [root@#localhost system]# systemctl start mysqld3307 [root@#localhost system]# systemctl start mysqld3308 [root@#localhost system]# systemctl start mysqld3309
查看启动情况
[root@#localhost system]# netstat -nlp | grep mysqld tcp6 0 0 :::3307 :::* LISTEN 7096/mysqld tcp6 0 0 :::3308 :::* LISTEN 6921/mysqld tcp6 0 0 :::3309 :::* LISTEN 6959/mysqld unix 2 [ ACC ] STREAM LISTENING 85539 6959/mysqld /data/3309/mysql.sock unix 2 [ ACC ] STREAM LISTENING 87340 7096/mysqld /data/3307/mysql.sock unix 2 [ ACC ] STREAM LISTENING 85298 6921/mysqld /data/3308/mysql.sock
6.5连接其中一个数据库
[root@#localhost system]# mysql -S /data/3307/mysql.sock Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.16-log MySQL Community Server (GPL) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)
7.MySQL内置功能
7.1 -e参数的使用方法
[root@#localhost ~]# mysql -uroot -p -S /tmp/mysql.sock -e "show databases" Enter password: +--------------------+ | Database | +--------------------+ | information_schema | | care | | care2 | | mysql | | performance_schema | | sys | | worknode | +--------------------+
7.2 > 导入数据
[root@#localhost ~]# mysql -uroot -p -S /tmp/mysql.sock >/root/hello.sql
8.SQL语言
DDL：数据定义语言 DCL：数据控制语言 DML：数据操作语言
8.1字符集
查看字符集： mysql> show charset; 查看排序规则： mysql> show collation;
校对规则：校对规则（Collation）也可以称为排序规则，是指在同一个字符集内字符之间的比较规则。字符集和校对规则是一对多的关系，每个字符集都有一个默认的校对规则。字符集和校对规则相辅相成，相互依赖关联。
简单来说，字符集用来定义 MySQL 存储字符串的方式，校对规则用来定义 MySQL 比较字符串的方式。
_ci(不区分大小写)_cs(区分大小写)或_bin(二进制文件)结尾对于MySQL中那些字符类型的列，如VARCHAR，CHAR，TEXT类型的列，都需要有一个COLLATE类型来告知MySQL如何对该列进行排序和比较。
COLLATE会影响到ORDER BY语句的顺序，会影响到WHERE条件中大于小于号筛选出来的结果，会影响DISTINCT、GROUP BY、HAVING语句的查询结果。
另外，MySQL建索引的时候，如果索引列是字符类型，也会影响索引创建，总之，凡是涉及到字符类型比较或排序的地方，都会和COLLATE有关。
COLLATE通常是和数据编码（CHARSET）相关的，一般来说每种CHARSET都有多种它所支持的COLLATE，并且每种CHARSET都指定一种COLLATE为默认值。例如Latin1编码的默认COLLATE为latin1_swedish_ci，GBK编码的默认COLLATE为gbk_chinese_ci，utf8mb4编码的默认值为utf8mb4_general_ci。
很多COLLATE都带有_ci字样，这是Case Insensitive的缩写，即大小写无关，也就是说"A"和"a"在排序和比较的时候是一视同仁的。selection * from table1 where field1="a"同样可以把field1为"A"的值选出来。与此同时，对于那些_cs后缀的COLLATE，则是Case Sensitive，即大小写敏感的。
8.2数据类型介绍
整数类型 ： 极小整数 tinyint 0-255 ；较小整数类型 smallint ;中等整数类型 mediumint； 常规整数类型 int
浮点型： 小型单精度 float ； 常规双精度 double
字符串类型： 定长字符类型 char (100) 不管存储的字符串多长，都立即分配100个 字符的空间，剩余空间用空格填充
变长字符类型 varchar 按需分配磁盘类型，每次存储数据之前都要计算字符长度，
如何选择字符类型：
1，少于255字符，定长的列值，选择char
2，大于255字符，变长的字符串，选择varchar
枚举类型： enum
时间类型： date 2022-07-11
time 时分秒
datetime 2022-07-11 21:45:02
timestamp 2022-07-11 21:45:02 （1970-2038
8.3 DDL语句
创建数据库：
CREATE database care3 charset utf8mb4;
CREATE database care4 charset utf8mb4 COLLATE utf8mb4_bin;
建库规范： 名称使用小写，库名不能以数字开头，库名不能是数据库关键字，必须设置字符集。
列属性： comment 注释 auto_increment 自增长列
创建表： CREATE TABLE guo( id int PRIMARY KEY NOT NULL auto_increment COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', age TINYINT UNSIGNED NOT NULL COMMENT '年龄', gender enum('m','n') NOT NULL DEFAULT 'm' COMMENT '性别', stime datetime NOT NULL DEFAULT now() COMMENT '时间' )ENGINE INNODB CHARSET utf8mb4;
建表规范： 小写字母，不能以数字开头，不能用关键字
查看库中的表： mysql> show tables; +--------------------+ | Tables_in_worknode | +--------------------+ | guo | +--------------------+ 1 row in set (0.01 sec) mysql> show create table guo\G *************************** 1. row *************************** Table: guo Create Table: CREATE TABLE `guo` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号', `sname` varchar(255) NOT NULL COMMENT '姓名', `age` tinyint(3) unsigned NOT NULL COMMENT '年龄', `gender` enum('m','n') NOT NULL DEFAULT 'm' COMMENT '性别', `stime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '时间', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.01 sec)
创建一个表结构一样的表
mysql> create table rui like guo; Query OK, 0 rows affected (0.04 sec) mysql> mysql> create table li as select * from guo where 1=2; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0
给表添加字段：
mysql> alter table guo add qq varchar(20) unique not null comment 'qq号'; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table guo add vx varchar(20) not null after sname;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table guo add num int not null comment '身份证号' first;
Query OK, 0 rows affected (0.14 sec)
Records: 0 Duplicates: 0 Warnings: 0
删除字段：
mysql> alter table guo drop num; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
修改列：
mysql> alter table guo modify sname varchar(60); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table guo change age ages varchar(50) not null; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
8.4 DCL
mysql> select user,host from mysql.user; +---------------+--------------+ | user | host | +---------------+--------------+ | json2 | % | | root | % | | replicate | 192.168.43.3 | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+--------------+ 6 rows in set (0.00 sec) mysql> grant select on *.* to json2@'%'; Query OK, 0 rows affected (0.02 sec) mysql> revoke select on *.* from json2@'%'; Query OK, 0 rows affected (0.02 sec)
mysql> select user,host from mysql.user; +---------------+--------------+ | user | host | +---------------+--------------+ | json2 | % | | root | % | | replicate | 192.168.43.3 | | mysql.session | localhost | | mysql.sys | localhost | | root | localhost | +---------------+--------------+ 6 rows in set (0.00 sec) mysql> grant select on *.* to json2@'%'; Query OK, 0 rows affected (0.02 sec) mysql> revoke select on *.* from json2@'%'; Query OK, 0 rows affected (0.02 sec)
8.5DML
insert updata delete
mysql> insert into guo values(1,'guo','32','m',now()); Query OK, 1 row affected (0.01 sec) mysql> insert into guo values(2,'li','33','n',now()),(3,'rui','34','m',now()); Query OK, 2 rows affected (0.04 sec) Records: 2 Duplicates: 0 Warnings: 0
8.6 DQL
select 单独使用
mysql> select @@port; +--------+ | @@port | +--------+ | 3306 | +--------+ 1 row in set (0.00 sec) mysql> select @@basedir; +---------------------+ | @@basedir | +---------------------+ | /application/mysql/ | +---------------------+ 1 row in set (0.00 sec) mysql> select @@datadir; +-------------------+ | @@datadir | +-------------------+ | /data/mysql/data/ | +-------------------+ 1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 6 |
+-------------+
1 row in set (0.00 sec)
select 自带函数
mysql> select database();
+------------+
| database() |
+------------+
| worknode |
+------------+
1 row in set (0.00 sec)
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2022-07-13 10:18:21 |
+---------------------+
1 row in set (0.00 sec)
创建实验表，表源自oracle的实验库，oracle中varchar2，mysql中没有，oracle有number类型，mysql中是int类型
CREATE TABLE EMP (EMPNO INT NOT NULL, ENAME VARCHAR(10), JOB VARCHAR(9), MGR INT, HIREDATE DATE, SAL INT, COMM INT, DEPTNO INT);
mysql> CREATE TABLE DEPT(DEPTNO int PRIMARY KEY,DNAME VARCHAR(14),LOC VARCHAR(13));
Query OK, 0 rows affected (0.05 sec)
插入数据
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,'1980-01-01',800,NULL,20); INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-01-01',1600,300,30); INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1982-01-01',1250,500,30); INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1983-01-01',2975,NULL,20); INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1984-01-01',1250,1400,30); INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1985-01-01',2850,NULL,30); INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1986-01-01',2450,NULL,10); INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-01-01',3000,NULL,20); INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1988-01-01',5000,NULL,10); INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1989-01-01',1500,0,30); INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1990-01-01',1100,NULL,20); INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1991-01-01',950,NULL,30); INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1992-01-01',3000,NULL,20); INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1993-01-01',1300,NULL,10);
INSERT INTO DEPT VALUES
(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
(40,'OPERATIONS','BOSTON');
select 查找
mysql> select * from DEPT; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.01 sec)
like语句，前后出现%的语句不走索引，性能极差
mysql> select * from EMP where ENAME like '%MI%'; +-------+--------+-------+------+------------+------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+-------+------+------------+------+------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-01-01 | 800 | NULL | 20 | | 7369 | SMITH | CLERK | 7902 | 1980-01-01 | 800 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1993-01-01 | 1300 | NULL | 10 | +-------+--------+-------+------+------------+------+------+--------+ 3 rows in set (0.01 sec)
group by 子句
mysql> select sum(sal),deptno from EMP group by deptno;
+----------+--------+
| sum(sal) | deptno |
+----------+--------+
| 8750 | 10 |
| 11675 | 20 |
| 9400 | 30 |
+----------+--------+
3 rows in set (0.00 sec)
列转行group_concat
mysql> select group_concat(ENAME),DEPTNO from EMP group by DEPTNO; +--------------------------------------+--------+ | group_concat(ENAME) | DEPTNO | +--------------------------------------+--------+ | CLARK,KING,MILLER | 10 | | SMITH,SMITH,JONES,SCOTT,ADAMS,FORD | 20 | | ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES | 30 | +--------------------------------------+--------+ 3 rows in set (0.00 sec)
连接函数concat
mysql> select concat("names:", group_concat(ENAME)),DEPTNO from EMP group by DEPTNO; +--------------------------------------------+--------+ | concat("names:", group_concat(ENAME)) | DEPTNO | +--------------------------------------------+--------+ | names:CLARK,KING,MILLER | 10 | | names:SMITH,SMITH,JONES,SCOTT,ADAMS,FORD | 20 | | names:ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES | 30 | +--------------------------------------------+--------+ 3 rows in set (0.01 sec)
having子句
mysql> select sum(sal),DEPTNO from EMP group by DEPTNO having sum(sal)>9000; +----------+--------+ | sum(sal) | DEPTNO | +----------+--------+ | 11675 | 20 | | 9400 | 30 | +----------+--------+ 2 rows in set (0.01 sec)
order by 子句
mysql> select sum(sal),deptno from EMP group by deptno having sum(sal)>9000 order by sum(sal); +----------+--------+ | sum(sal) | deptno | +----------+--------+ | 9400 | 30 | | 11675 | 20 | +----------+--------+ 2 rows in set (0.00 sec)
limit子句
mysql> select * from EMP limit 3,3; +-------+--------+----------+------+------------+------+------+--------+ | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | +-------+--------+----------+------+------------+------+------+--------+ | 7521 | WARD | SALESMAN | 7698 | 1982-01-01 | 1250 | 500 | 30 | | 7566 | JONES | MANAGER | 7839 | 1983-01-01 | 2975 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1984-01-01 | 1250 | 1400 | 30 | +-------+--------+----------+------+------------+------+------+--------+ 3 rows in set (0.00 sec)
多表联合查询
mysql> select e.ENAME,e.DEPTNO,d.DNAME from EMP e,DEPT d where e.DEPTNO=d.DEPTNO; +--------+--------+------------+ | ENAME | DEPTNO | DNAME | +--------+--------+------------+ | SMITH | 20 | RESEARCH | | SMITH | 20 | RESEARCH | | ALLEN | 30 | SALES | | WARD | 30 | SALES |
mysql> select e.ENAME,e.DEPTNO,d.DNAME from EMP e join DEPT d on e.DEPTNO=d.DEPTNO; +--------+--------+------------+ | ENAME | DEPTNO | DNAME | +--------+--------+------------+ | SMITH | 20 | RESEARCH | | SMITH | 20 | RESEARCH |
测试库及表创建
mysql> create database school charset utf8; Query OK, 1 row affected (0.00 sec) mysql> use school Database changed mysql> CREATE TABLE student( -> sno INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', -> sname VARCHAR(20) NOT NULL COMMENT '姓名', -> sage TINYINT UNSIGNED NOT NULL COMMENT '年龄', -> ssex ENUM('f','m') NOT NULL DEFAULT 'm' COMMENT '性别' -> )ENGINE=INNODB CHARSET=utf8; Query OK, 0 rows affected (0.04 sec) mysql> CREATE TABLE course( -> cno INT NOT NULL PRIMARY KEY COMMENT '课程编号', -> cname VARCHAR(20) NOT NULL COMMENT '课程名字', -> tno INT NOT NULL COMMENT '教师编号' -> )ENGINE=INNODB CHARSET utf8; Query OK, 0 rows affected (0.02 sec) mysql> CREATE TABLE sc ( -> sno INT NOT NULL COMMENT '学号', -> cno INT NOT NULL COMMENT '课程编号', -> score INT NOT NULL DEFAULT 0 COMMENT '成绩' -> )ENGINE=INNODB CHARSET=utf8; Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE teacher( -> tno INT NOT NULL PRIMARY KEY COMMENT '教师编号', -> tname VARCHAR(20) NOT NULL COMMENT '教师名字' -> )ENGINE=INNODB CHARSET utf8; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO student(sno,sname,sage,ssex) -> VALUES (1,'zhang3',18,'m'); Query OK, 1 row affected (0.01 sec) mysql> mysql> INSERT INTO student(sno,sname,sage,ssex) -> VALUES -> (2,'zhang4',18,'m'), -> (3,'li4',18,'m'), -> (4,'wang5',19,'f'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO student -> VALUES -> (5,'zh4',18,'m'), -> (6,'zhao4',18,'m'), -> (7,'ma6',19,'f'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO student(sname,sage,ssex) -> VALUES -> ('oldboy',20,'m'), -> ('oldgirl',20,'f'), -> ('oldp',25,'m'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> mysql> INSERT INTO teacher(tno,tname) VALUES -> (101,'oldboy'), -> (102,'hesw'), -> (103,'oldguo'); Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO course(cno,cname,tno) -> VALUES -> (1001,'linux',101), -> (1002,'python',102), -> (1003,'mysql',103); Query OK, 3 rows affected (0.07 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO sc(sno,cno,score) -> VALUES -> (1,1001,80), -> (1,1002,59), -> (2,1002,90), -> (2,1003,100), -> (3,1001,99), -> (3,1003,40), -> (4,1001,79), -> (4,1002,61), -> (4,1003,99), -> (5,1003,40), -> (6,1001,89), -> (6,1003,77), -> (7,1001,67), -> (7,1003,82), -> (8,1001,70), -> (9,1003,80), -> (10,1003,96); Query OK, 17 rows affected (0.03 sec) Records: 17 Duplicates: 0 Warnings: 0
多表联合查询：
mysql> select s.sname,count(c.cno) from student s join sc c on s.sno=c.sno where s.sno=1; +--------+--------------+ | sname | count(c.cno) | +--------+--------------+ | zhang3 | 2 | +--------+--------------+ 1 row in set (0.00 sec)
三张表联合查询：
mysql> select s.sname,c.cno,co.cname from student s join sc c on s.sno=c.sno join course co on co.cno=c.cno where s.sname='zhang3'; +--------+------+--------+ | sname | cno | cname | +--------+------+--------+ | zhang3 | 1001 | linux | | zhang3 | 1002 | python | +--------+------+--------+ 2 rows in set (0.00 sec)
将查询结果列转行
mysql> select s.sname,group_concat(co.cname) from student s join sc c on s.sno=c.sno join course co on co.cno=c.cno where s.sname='zhang3' group by s.sname; +--------+------------------------+ | sname | group_concat(co.cname) | +--------+------------------------+ | zhang3 | linux,python | +--------+------------------------+ 1 row in set (0.00 sec)
四表联合查询：
mysql> select teacher.tname,group_concat(student.sname) from teacher join course on teacher.tno=course.tno join sc on courrse.cno=sc.cno join student on
sc.sno=student.sno where tname='oldguo' group by tname; +--------+---------------------------------------------+ | tname | group_concat(student.sname) | +--------+---------------------------------------------+ | oldguo | zhang4,li4,wang5,zh4,zhao4,ma6,oldgirl,oldp | +--------+---------------------------------------------+ 1 row in set (0.00 sec)
多表联合查询求平均值：
mysql> select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.ccno=sc.cno where teacher.tname='oldboy'; +--------+---------------+ | tname | avg(sc.score) | +--------+---------------+ | oldboy | 80.6667 | +--------+---------------+ 1 row in set (0.00 sec)
多表联合查询：
mysql> select teacher.tname,avg(sc.score) from teacher join course on teacher.tno=course.tno join sc on course.cno=sc.cno group by teacher.tname order by avg(sc.score); +--------+---------------+ | tname | avg(sc.score) | +--------+---------------+ | hesw | 70.0000 | | oldguo | 76.7500 | | oldboy | 80.6667 | +--------+---------------+ 3 rows in set (0.00 sec)
8.7元数据获取
mysql> use information_schema; Database changed mysql> select table_name,table_schema from tables where table_name='student'; +------------+--------------+ | table_name | table_schema | +------------+--------------+ | student | school | +------------+--------------+ 1 row in set (0.00 sec)
8.8使用CONCAT拼接语句
select concat("mysqldump -uroot -pPassword@123 ",table_schema," ",table_name,">/bak/",table_schema,"_",table_name,".sql") from tables;
8.9show 语句
mysql> show databases; mysql> show tables; mysql> show create database school; mysql> show create table sc; mysql> show processlist; mysql> show charset; mysql> show collation; mysql> show grants for json; 查看用户的授权 mysql> show variables like '%%'; mysql> show engines; mysql> show index from mysql.user; mysql> show engine innodb status;
9.索引
索引类似一本书的目录，起到优化查询的作用。
9.1索引的分类
根据底层数据结构的划分 ： 哈希索引，B数索引
根据索引字段个数划分：单值索引、 多值索引即复合索引
根据是否在主键上建立的索引进行划分:主键索引、二级索引即辅助索引
根据数据与索引的存储关联性划分：1. 聚簇索引， Innodb存储引擎的主键索引为聚簇索引，二级索引为非聚簇索引；非聚簇索引，Myisam 存储引擎的索引为非聚簇索引；
其他分类：唯一索引、全文索引
Btree索引在功能上的分类：
（1） 辅助索引
提取所有列的所有值，进行排序；
将排好序的值均匀放在叶子节点，进一步生成枝节点和根节点
在叶子节点的值都会对应存储主键ID
（2）聚集索引
MySQL会自动选择主键列作为聚集索引列，没有主键会选择唯一键，
9.2查看索引
desc查看表，KEY列就是索引。
mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+-----------------------+-------+ | Host | char(60) | NO | PRI | | | | User | char(32) | NO | PRI | | |
PRI ： 主键索引
MUL ： 辅助索引
InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。
UNI ：唯一索引
show index命令查看索引
mysql> show index from mysql.user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | Host | A | NULL | NULL | NULL | | BTREE | | | | user | 0 | PRIMARY | 2 | User | A | 8 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.01 sec)
9.3创建索引
创建索引的时候会锁表；
mysql> alter table sc add index ind_sc(sno); Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc sc; +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | sno | int(11) | NO | MUL | NULL | | | cno | int(11) | NO | | NULL | | | score | int(11) | NO | | 0 | | +-------+---------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
创建多列的联合索引
mysql> alter table student add index ind_s_s(sname,sage); Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc student; +-------+---------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+---------------------+------+-----+---------+----------------+ | sno | int(11) | NO | PRI | NULL | auto_increment | | sname | varchar(20) | NO | MUL | NULL | | | sage | tinyint(3) unsigned | NO | | NULL | | | ssex | enum('f','m') | NO | | m | | +-------+---------------------+------+-----+---------+----------------+ 4 rows in set (0.02 sec)
创建唯一索引：
mysql> alter table course add unique index ind_nq(cname); Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc course; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | cno | int(11) | NO | PRI | NULL | | | cname | varchar(20) | NO | UNI | NULL | | | tno | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
删除索引：
mysql> alter table course drop index ind_nq; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0
9.4执行计划分析
获取执行计划：用desc命令获取
mysql> desc select * from student where sno=2; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | student | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
用explan命令获取
mysql> explain select * from student where sno=2; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | student | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
分析执行计划：
table：表名
type:
全表扫描： ALL
索引扫描：index，range，ref，eq_ref,const(system),NULL
index:全索引扫描；
mysql> explain select * from student; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 10 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
range：索引范围扫描（< > <= >= between and ,or）
mysql> explain select * from student where sno>5; +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.01 sec)
ref: 辅助索引等值查询
mysql> explain select * from student where sname='li4' union all select * from student where sname='zhao4'; +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | PRIMARY | student | NULL | ref | ind_s_s | ind_s_s | 62 | const | 1 | 100.00 | NULL | | 2 | UNION | student | NULL | ref | ind_s_s | ind_s_s | 62 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec)
eq_ref:多表连接查询时候，子表的主键作为或者唯一列作为连接条件
const(system):主键或者唯一键的等值查询：
mysql> explain select * from student where sno=1; +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | student | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
null：没有工作
mysql> explain select * from student where sno=1111; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ 1 row in set, 1 warning (0.00 sec)
possible_keys:可能会 用到的索引;
key:最终选择的索引；
key_len:索引的覆盖长度；越长越好。
Extra：对子句查询，应用联合索引
9.5 MySQL性能排查
应急性的慢：突然hang住，处理过程：
1. show processlist：找出影响性能的语句；
2.explain分析SQL执行计划，有没有走索引，索引的执行情况
3.建索引，改语句。
一段时间慢， 处理过程：
1.记录慢日志slowlog，分析slowlog；
2.explain分析SQL执行计划，有没有走索引，索引执行计划；
3.建索引，改语句。
9.6索引规范
索引创建规范：
1.建表必须有主键，一般是无关列，自增长；
2.最好使用唯一值多的列作为联合索引的前导列；
3.列值较长的索引列，建议使用前缀索引；
4.降低索引条目，不常使用的索引要清理，不要创建没有用的索引；
5.索引的维护要避开业务繁忙期；
6.小表不要建索引；
10.MySQL存储引擎
存储引擎相当于Linux的文件系统，只不过比文件系统功能强大。
存储引擎功能： 数据读写，数据安全和一致性，提高性能，热备份，自动故障恢复，高可用；
存储引擎的种类：
查看语法：show engines;
传统引擎MyISAM存储引擎缺点：表级别锁，在高并发的时候，会有很高的锁等待；不支持事务，在断电的时候，会有可能丢失数据。
10.1 Innodb核心特性
1.支持事务；
2.支持行级锁；
3.MVCC并发控制
4.支持外键；
5.自动故障恢复；
6.热备份；
7.多线程复制
10.2 存储引擎的查看
mysql> select @@default_storage_engine; +--------------------------+ | @@default_storage_engine | +--------------------------+ | InnoDB | +--------------------------+ 1 row in set (0.00 sec)
10.3修改表的存储引擎
mysql> alter table d4 engine=innodb; Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0
该命令还可以用来整理碎片。
10.4存储引擎物理文件
[root@#localhost data]# pwd /data/mysql/data [root@#localhost data]# ls auto.cnf care4 ibdata1 ibtmp1 #localhost.pid school care2 employtest ib_logfile0 #localhost.localdomain.err mysql sys care3 ib_buffer_pool ib_logfile1 localhost.localdomain.err performance_schema worknode
ibdata1 ：系统数据字典信息（统计信息），UNDO表空间等信息；
ib_logfile0~1 :REDO日志
ibtmp1:临时表空间磁盘位置，存储临时表
.frm：存储表的列信息
.ibd：存储表数据和索引
5.6版本的共享表空间：
https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html
5.7innodb数据结构：
查看表空间：
mysql> select @@innodb_data_file_path; +-------------------------+ | @@innodb_data_file_path | +-------------------------+ | ibdata1:12M:autoextend | +-------------------------+ 1 row in set (0.01 sec)
通过复制方式迁移数据
这一步会清楚原来的数据文件，记得备份，
mysql> alter table t4 discard tablespace; Query OK, 0 rows affected (0.08 sec)
然后复制数据文件到物理盘；
mysql> alter table t4 import tablespace;
10.5 事务特性
Atomic（原子性）：所有语句作为一个单元，全部执行成功或者全部取消，不能出现中间状态；
Consistent（一致性）：如果数据库在事务开始时候处于一致状态，则在执行该事务期间将保留一致状态；
Isolate（隔离性）：事务之间不互相影响；
Durable（持久性）：事务结束后，所做的所有更改都会准确的记录在数据库中，所做的更改不会丢失。
10.6事务的生命周期
1.开启事务
mysql> begin; Query OK, 0 rows affected (0.01 sec)
2.标准的事务语句 insert update delete
mysql> insert into student values(99,'ming',33,'m'); Query OK, 1 row affected (0.00 sec)
3.事务的结束
提交：commit；
回滚：rollback；
mysql> rollback; Query OK, 0 rows affected (0.03 sec)
自动提交
mysql> select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+ 1 row in set (0.00 sec)
隐式提交的情况
1、 begin开启一个事务，执行事务语句， 在没有提交的情况下，有执行一个begin语句，此时上一个事务隐式提交
2、set命令也会触发隐式提交；
3.DDL语句：alter create drop
4.DCL语句：grant ， revoke ， set password
5.锁定语句lock tables，unlock tables
10.7 事务的ACID如何保证
redo log：重做日志，ib_logfile0~1；
redo_log buffer:redo内存区域；
ibd：数据行和索引；
data buffer poll：数据和索引缓冲区；
LSN：日志序列号，ibd，redo log，data buffer poll，redo buffer都有日志序列号，MySQL每次启动都会对比数据磁盘页和redolog的LSN，两者一致才能启动；
WAL：日志优先写，write ahead log 的方式实现持久化，日志优先于数据写入磁盘；
脏页：内存脏页，内存中发生了修改，没有写入到磁盘之前，我们把内存页称之为脏页；
CKPT：checkpoint,检查点，将脏页数据刷到磁盘的动作；
TXID：事务号，Innodb会为每个事务生成一个事务号，伴随着着整个事务；
CSR自动故障恢复机制
事务日志-----redo重做日志 ，记录了内存数据页的变化，
（1）记录了内存数据页的变化；
（2）提供快速的持久化功能（WAL）；
（3）CSR过程中实现前滚操作（磁盘页和redo日志LSN一致）；
reod的刷新策略：
（1）commit；
（2）刷新当前事务的redo buffer到磁盘；
undo回滚日志：
1)数据修改前的备份，主要是保证用户的读一致性。
2)在事务修改数据时产生。
3)至少保存到事务结束
作用：在ACDI中，主要保证A，同时对CI也有一定的功效，主要记录数据修改之前的状态；
1)回滚(rolllback)操作
2)实现读一致性与闪回查询
3)从失败的事物中还原数据
4)非正常停机后的实例恢复
10.8锁
实现了事务之间的隔离功能。
Innodb实现的是行级锁；
10.9隔离级别
RU：读未提交，可脏读；
RC：读已提交，可能出现幻读，可防止脏读；
RR：可重复读，防止幻读
SR：可串行化，可以防止死锁
查询隔离级别：
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
一、实验RU模式，READ-UNCOMMITTED,修改配置文件，transaction_isolation=read-uncommitted，然后重启数据库。
mysql> select @@tx_isolation; +------------------+ | @@tx_isolation | +------------------+ | READ-UNCOMMITTED | +------------------+ 1 row in set (0.00 sec)
第一个窗口执行
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update student set sage=98 where sno=1; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
第二个窗口查询，得到的结果是刚才改过的结果，可脏读
mysql> select * from student; +-----+---------+------+------+ | sno | sname | sage | ssex | +-----+---------+------+------+ | 1 | zhang3 | 98 | m |
二、实验RC，修改配置文件，transaction_isolation=read-committed，然后重启数据库。
mysql> select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec)
第一个窗口执行：
mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> update student set sage=33 where sno=1; Query OK, 1 row affected (0.01 sec) mysql> select * from student; +-----+---------+------+------+ | sno | sname | sage | ssex | +-----+---------+------+------+ | 1 | zhang3 | 33 | m |
第二个窗口执行：
mysql> select * from student; +-----+---------+------+------+ | sno | sname | sage | ssex | +-----+---------+------+------+ | 1 | zhang3 | 200 | m |
将第一个窗口提交
mysql> commit; Query OK, 0 rows affected (0.01 sec)
在第二个窗口查询
mysql> select * from student; +-----+---------+------+------+ | sno | sname | sage | ssex | +-----+---------+------+------+ | 1 | zhang3 | 33 | m |
三、实验RR级别，系统默认就是这种级别，REPEATABLE-READ
RU会出现脏读，RC会出现不可重复读，还有幻读，RR通过MVCC解决了不可重复读，但是会出现幻读现象
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
10.10 Innodb核心参数
innodb_file_per_table=1 独立表空间还是共享表空间，1代表独立表空间模式；
innodb_data_file_path=ibdata1:512M:idbdata2:512M
innodb_flush_log_at_trx_commit=1 同时redo日志写入和数据写入磁盘
innodb_max_dirty_pages_pct=75脏页刷写策略
