独角兽企业重金招聘Python工程师标准>>>

MySQL分支

Percona和MariaDB

MySQL查询过程

注意 ：在进行 MySQL 的优化之前必须要了解的就是 MySQL 的查询过程；

MySQL数据库放在WEB网站后端数据存储，数据库一般用于持久化数据，存储内存或者硬盘上，主要存储在硬盘中。

MySQL主从复制构架根本目标保证网站高可用，当master宕机，能够快速切换到slave库；

MySQL主从复制最大用途为了保证主库和从库数据的完整性，保证数据同步。

MySQL主从同步是异步复制过程，中间同步数据会有延迟，整个过程开启三个线程，分别是master开启IO线程 ，Slave开启IO 和 SQL线程。

还需要在Master上开启bin-log功能，是主从复制最核心组件，二进制日志文件，文件会记录数据库中增、删、改、插入的SQL语句。

从库就可以基于master端bin-log日志来同步数据，读取bing-log日志来同步数据，读取bin-log日志文件中SQL语句，并且把SQL语句放在本地去执行。

Slave库通过I/O线程连接Master，发起数据请求，请求从指定bin-log文件、位置点之后的SQL语句。

Master数据库收到Slave的请求(指定bin-log文件名、position点之后的内容)，以IO线程响应，命令行show processlist查看 , 在master端看到的bin-log dump 线程，将bin-log日志指定位置点的SQL内容dump出来，发送给Slave数据库服务器；

当Slave库收到bin-log日志内容之后，将信息追加存储到Relay-log中继日志，同时会生成master.info文件（记录master端IP、用户名、密码、bin-log文件名、Position点）；

Slave数据库服务器实时启动SQL线程，SQL线程检测relay-log日志中是否有更新，内容有更新的话，解析日志中SQL语句，并且在本地数据库中执行；

SQL线程执行玩SQL语句，那就保证了Slave库和Master数据一致，从而实现主从数据同步 ，执行完的效果，Slave库Master数据保存一致；

Mariadb Bin-log功能

1）Bin-log文件是一个二进制文件,二进制文件,不能直接打开；

2）主要用于存储数据库事务操作的，存储在数据库中各个操作（增、删、改、插入）；

3）如果开启bin-log日志功能之后，bin-log文件记录在数据库中各种修改操作的SQL语句。

4）基于bin-log文件，实现数据库数据增量备份、完整备份，军科院从而基于该备份实现数据还原；

5）Bin-log日志实现MYSQL主从复制，主从复制，Slave端来获取数据，其实是获取bin-log日志中的数据；

MySQL默认配置文件路径：

配置文件：/etc/f

日志文件：/var/log//var/log/mysqld.log

服务启动脚本：/usr/lib/systemd/system/mysqld.service

socket文件：/var/run/mysqld/mysqld.pid

MySQL主从同步、半同步、主主同步、全同步

1）MySQL主从同步是5.5之前的版本默认数据同步方式；

2）从MySQL5.5之后，MySQL数据同步开始引入半同步；

3）MySQL5.7.4以后，对MySQL半同步进一步优化，引入多线程实现数据更高效的传输；

4）MySQL心版本支持全同步，Group同步，普及难度高；

常用的关系型数据库软件：MySQL、mariadb、Oracle、SQL Server、PostgreSQL、DB2等；

注意 ：Myisam：MYSQL5.5之前默认引擎，不支持事务、不支持外部键、表锁、应用于大量的select，查询比较多。MySQL5.5以后不支持./configure编译，使用cmake编译。

MySQL引擎 ： MyISAM、InnoDB、memory、CSV。

MyISAM类型的数据库表强调的是性能，其执行速度比InnoDB类型更快，但不提供事务支持，不支持外键，如果执行大量的SELECT(查询)操作 , MyISAM是更好的选择，支持表锁；

InnoDB提供事务支持事务、外部键、行级锁等高级数据库功能，执行大量的INSERT或UPDATE，处于性能方面的考虑，可以考虑使用InnoDB引擎；

RDBMS(关系数据库管理系统)数据库的特点如下 ：

1、数据以表格的形式出现；

2、每行记录数据的真实内容；

3、每列记录数据真实内容的数据域；

4、无数的行和列组成一张表；

5、若干的表组成一个数据库；

MySQL主从原理解析 ：

1）MySQL主从是异步复制过程，整个过程选哟开启三个线程，分别是Master开启I/O线程 ，Slave开启I/O和SQL线程，同时master必须开启bin-log日志功能。

2）第一次salve start : 以IO线程通过授权用户名和密码连接Master，说请求某个bin-log某个position点之后的内容，master收到请求之后；

3）Master会通过I/O线程bin-log内容返给slave IO线程，Slave服务器收到bin-log文件中的内容之后，存放在本地的relay-log日志中，同行会产生一个master.info文件（存放bin-log文件名称、用户名、密码明文、最后position点）方便下次同步读取配置信息；

4）Slave端有一个SQL线程，SQL线程实时运行，监测relay-log日志文件是否更新，如果有更新的话，就把内容解析成SQL语句。

5）解析成SQL语句，会在本地去执行，执行完的效果保持数据同步。

MySQL数据库常见索引类型 ：普通索引(normal)、唯一索引(unique)、全文索引(full text)、主键索引(primary key)、组合索引等，如下为每个索引的应用场景及区别：

普通索引：normal，使用最广泛；

唯一索引：unique，不允许重复的索引，允许有空值；

全文索引：ful text ，只能用于MyISAM表，FULLTEXT主要用于大量的内容检索；

主键索引： primary key 又称为特殊的唯一索引，不允许有空值；

组合索引：为提高mysql效率可建立组合索引；

MySQL数据库在存储数据时，默认编码latin1，存储中文字符时，在显示或者WEB调用时会显示乱码，为解决该乱码问题，在相应段中加入相应的参数 ：

1、编译vim /etc/f配置文件，在相应段中加入相应的参数：

[client]字段里加入: default-character-set=utf8

[mysqld]字段里加入 : character-set-server=utf8

[mysql]字段里加入 :default-character-set=utf8

字符集修改完毕，重启MySQL服务，即可；

2、MySQL命令行中运行如下指令，

show variables like '%char%';

SET charracter_set_client = utf8;

SET charracter_set_results= utf8;

MySQL数据库表创建各个索引命令，以t1为案例。

主键索引 ：ALTER TABLE t1 ADD PRIMARY KEY ('column');

唯一索引 ：ALTER TABLE t1 ADD UNIQUE('column');

普通索引 ：ALTER TABLE t1 ADD INDEX index_name('column');

全文索引 ：ALTER TABLE t1 ADD FULLTEXT('column');

组合索引 ：ALTER TABLE t1 ADD INDEX index_name('column1','column2','column3',);

MySQL创建用户及授权

grant all on jfedu.* to test@localhost identified by 'pass'; #授权localhost主机通过test用户和pas密码访问本地的jfedu库的所有权限；

grantselect,insert,update,delete on *.* to test@"%" identified by 'pass'; #授权所有主机通过test用户和pass密码访问本地的jfedu库查询、插入、更新、删除权限；

grant all on jfedu.* to test@'192.168.111.118' identified by 'pass'; #授权192.168.111.118主机通过test用户和pass密码访问本地jfedu库的所有权限；

MySQL数据库表删除各个索引命令,以t1表为案例

DROP INDEX index_name ON t1；

ALTER TABLE t1 DROP INDEX index_name;

ALTER TABLE t1 DROP PRIMARY KEY;

MySQL数据库查看表索引：

show index from t1;

show keys from t1;

MySQL数据库索引的缺点 ：

1、MySQL数据库索引虽然能够提高数据库查询速度，但同时会降低更新、删除、插入表的速度，例如如对表进行INSERT、UPDATE、DELETE时，update表MySQL不仅要保存数据，还需要保存更新索引；

2、建立索引会占用磁盘空间，大表上建立了多种组合索引，索引文件的会占用大量的空间。

MySQL数据库慢查询

MySQL数据库慢查询主要用于追踪异常的SQL语句，可以分析出当前程序里那些SQL语句比较耗费资源，慢查询日志则用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL语句，会被记录到慢查询日志中。

MySQL数据库默认没有开启慢查询日志功能，需手动在配置文件或者MySQL命令行中开启，慢查询日志默认写入磁盘中的文件，也可以将慢查询日志写入到数据库表：

查看数据库是否开启查询

show variables like '%slow%';

show variables like '%long_query%';

MySQL慢查询参数详解如下 :

log_slow_queries 关闭慢查询日志功能

log_query_time #慢查询超时间，默认为10s；MySQL5.5以上可以设置微秒；

slow_query_log_file # 关闭慢查询日志；

slow_launch_time #Thread create时间，单位秒，如果thread crete 的时间超过了这个值，该变量slow_launch_time的值会加1；

log-queries-using-indexes 记录来添加索引的SQL语句

开启MySQL慢查询日志方法有两种 ：

（1）、mysql数据库命令执行命令：

set global slow_query_logon;

show variables liek '%slow%';

（2）、编辑f配置文件中添加瑞小安代码：

log-slow-queries = /data/mysql/localhos.log;

long_query_time = 0.01

log-queries-not-using-indexes

慢查询功能开启之后，数据库会自动将执行时间超过设定时间的SQL语句添加至慢查询日志文件中，可以提供慢查询日志文件定位执行慢的SQL，从而对其优化，可以通过msyqldumpslow命令行工具分析日志，相关参数如下：

执行命令mysqldumpslow -h 可以查看命令帮助信息:

MySQL数据库优化

MySQL数据库优化是一项非常重要的工作，而且是一项长期的工作，MySQL优化三分靠配置文件及硬件资源的优化，七分靠SQL语句的优化。

MySQL数据库集体优化包括 ： 配置文件的优化、sql语句的优化、表结构的优化、索引的优化， 而配置的优化包括 ：系统内核优化、硬件资源、内存、CPU、mysql本身配置文件的优化、

硬件上的优化 ：增加内存和提高磁盘读写速度，都可以提高mysql数据库的查询，更新的速度，另一种提高MySQL性能的方式是使用多块磁盘类存储数据，因为可以从多看磁盘上并行读取数据，这样可以提高数据的速度。

MySQL参数的优化 ： 内存中会为mysql保留部分的缓冲区，这些缓冲区可以提高MySQL的速度，缓冲区的大小可以在MySQL的配置文件中进行设置。

附企业级MySQL百万量真实环境配置f内容，可以根据实际清空修改 :

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

user =mysql

server_id = 10

port = 3306

socket = /tmp/mysql.sock

datadir = /data/mysql

old_passwords = 1

lower_case_table_names =1

character-set-server = utf8

default-storage-engine = MYISAM

log-bin = bin.log

log-error = error.log

slow_query_log_file = slow.log

binlog_cache_size = 4M

binglog_format = mixed

max_lbinlog_cache_size = 16M

max_binlog_size = 1G

expire_logs_days = 30

ft_min_word_len = 4

back_log = 512

max_allowed_packet = 64M

max_connections = 4096

max_connect_errors = 100

join_buffer_size = 2M

read_buffer_size = 2M

sort_buffer_size =2M

query_cache_size = 64M

table_open_cache = 10000

thread_cache_size = 256

max_heap_table_size = 64M

tmp_table_size = 64M

thread_stack =192K

thread_concurrency = 24

local-infile =0

skip-show-database

skip-name-resolve

skip-external-locking

MySQL日志文件系统的组成 :

a、错误日志：记录启动、运行或停止mysqld时出现的问题。

b、通用日志：记录建立的客户端连接和执行的语句。

c、更新日志：记录更改数据的语句。该日志在MySQL 5.1中已不再使用。

d、二进制日志：记录所有更改数据的语句。还用于复制。

e、慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

f、Innodb日志：innodb redo log。

Innodb 四大 IO 线程：write thread，read thread，insert buffer thread，redo log thread

master thread 是数据库的主线程，优先级别最高，里面包含 1s 和 10s 对数据库的操作。

page cleaner thread：帮助刷新脏页的线程，5.7 版本可以增加多个。

purge thread ：删除无用 undo 页。默认1个，最大可以调整到 32。

针对 Innodb 存储引擎的三大特性有：两次写，自适应哈希索引，插入缓冲；

MySQL中有六种日志文件，分别是：重做日志（redo log）、回滚日志（undo log）、二进制日志（binlog）、错误日志（errorlog）、慢查询日志（slow query log）、一般查询日志（general log），中继日志（relay log）。

重做日志有一个缓存区Innodb_log_buffer，Innodb_log_buffer的默认大小为8M(这里设置的16M),Innodb存储引擎先将重做日志写入innodb_log_buffer中。使用show variables like 'innodb_log_buffer_size';命令;

通过一条 sql 语句进入数据库的过程：

数据库有两种类型：

（1）关系型数据库； 比如，Mysql ，oracle ，Sybase ，Sql server。

（2）非关系型数据库：比如，MongoDB ， Nosql 以文档形式进行存储，bson格式

注意 ：在 MySQL 8.0 中，默认的密码加密方式是SHA2。

通过以下三种方式将innodb日志缓冲区的日志刷新到磁盘

1，Master Thread 每秒一次执行刷新Innodb_log_buffer到重做日志文件。

2，每个事务提交时会将重做日志刷新到重做日志文件。

3，当重做日志缓存可用空间 少于一半时，重做日志缓存被刷新到重做日志文件

由此可以看出，重做日志通过不止一种方式写入到磁盘，尤其是对于第一种方式，Innodb_log_buffer到重做日志文件是Master Thread线程的定时任务。

缺省情况下，所有日志创建于mysqld数据目录中。

可以通过刷新日志，来强制mysqld来关闭和重新打开日志文件（或者在某些情况下切换到一个新的日志）。

当你执行一个FLUSH LOGS语句或执行mysqladmin flush-logs或mysqladmin refresh时，则日志被老化。

对于存在MySQL复制的情形下，从复制服务器将维护更多日志文件，被称为接替日志。

数据库优化维度有四个：硬件、系统配置、数据库表结构、SQL及索引

优化选择

优化成本：硬件>系统配置>数据库表结构>SQL及索引

优化效果：硬件<系统配置<数据库表结构<SQL及索引

优化工具

数据库层面

检查问题常用工具​​​​​

mysqlmsyqladmin mysql客户端，可进行管理操作mysqlshow 功能强大的查看shell命令show [SESSION | GLOBAL] variables查看数据库参数信息SHOW [SESSION | GLOBAL] STATUS 查看数据库的状态信息information_schema获取元数据的方法SHOW ENGINE INNODB STATUS Innodb引擎的所有状态SHOW PROCESSLIST 查看当前所有连接session状态explain获取查询语句的执行计划show index 查看表的索引信息show index from t;#查看t表的索引​​​slow-log记录慢查询语句mysqldumpslow分析slowlog文件的

不常用但好用的工具

zabbix 监控主机、系统、数据库（部署zabbix监控平台）pt-query-digest 分析慢日志mysqlslap 分析慢日志sysbench压力测试工具mysql profiling 统计数据库整体状态工具Performance Schemamysql性能状态统计的数据workbench 管理、备份、监控、分析、优化工具（比较费资源）

数据库基础操作

1、远程mysql -u 用户名 -p密码 等其他参数

退出 ：quit; 、exit ;

2、基础操作创建数据库 :create database 数据库库名；

显示所有的数据库: show databases;

删除 ： drop databases 数据库名；

创建数据表 ： 指定数据库 ：use 数据库名；

创建新表 ：create table 表名(域名 数据类型 列选项[]);

指定字符集 ： 支持中文在创建新表命令后添加语句：charset=utf8;

3、数据插入及显示插入 ： insert into 表名(列名1，列明2，……) values(数据1 数据2)；

显示 ： select 列名1，列名2，……from表名

4、显示表信息显示所有表

显示表结构 ：desc/describe 表名；

删除表 ： drop table 表名；

5、数据插入及显示插入 ： insert into 表名(列名1，列名2……) values (数据1,数据2……)

6、自增序列的设置：数据类型必须为int,列的定义后附加关键字 auto_incre,ment使用primary key设置其唯一性：alter table 表名 auto_increment =0

7、更新update 表名 set 列名1=值1,列名2=值2，……where条件表达式；

8、删除delete from 表名 where 条件表达式；

truncate table 表名； #删除所有的数据

9、查询select列表1,列名2，…… from 表名[条件表达式];条件检索 : where birth>='1980-10-10';

模糊检索：where nam like '李%'

NULL条件 ：where birth is NULL;

多条件结合:where sex='1' and birth is not NULL;

结果排序:order by sex asc(升),birth desc(降)；

指定区间 ：order by birth desc limit 2;排序后取前几top 2 排名靠前两位的

案例：

把姓名为“张三”，改成“李四”（误更改数据）：

mysql > update t1 set name='李四' where id = 1;

查询一小时内的历史数据。

SELECT * FROM t FOR SYSTEM_TIME BETWEEN (NOW() - INTERVAL 1 HOUR) AND NOW();

查询一段时间内的历史数据。

SELECT * FROM t1 FOR SYSTEM_TIME FROM '-05-15 00:00:00' TO '-05-15 14:00:00';

查询所有历史数据。

SELECT * FROM t1 FOR SYSTEM_TIME ALL;

恢复历史数据

找到了历史数据“张三”，只需把它导出来做恢复即可。

SELECT id,name FROM t1 FOR SYSTEM_TIME ALL where id = 1 AND name =

'张三' into outfile '/tmp/t1.sql' \

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

FIELDS TERMINATED BY ',' —— 字段的分隔符

OPTIONALLY ENCLOSED BY '"' —— 字符串带双引号

导入恢复。

load data infile '/tmp/t1.sql' replace into table t1 \

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' \

(id,name);

10、修改表的列结构改变列的数据类型 ：alter talble 表名 modify 列名 数据类型；

追加新列：alter table 表名 add 列名 数据类型;

改变列的位置：alter table 表名 modify 列名1 数据类型after 列名2；

改变列名与类型：alter table 表名 change 列名——改前 to 列名_改后；

删除列：alter table 表名 drop lie名；

11、复制表和修改表表的列构造+数据复制 : create table 新表名 select * from 旧表名；

列构造 ： create table 新表名 like 旧表名；

数据复制 ：insert into 表名 select * from 含数据的表；

12、显示/查看数据库具体信息

用来显示授权用户的安全权限：SHOW GRANTS；

用来显示数据库服务器或警告信息：SHOW ERRORS 或者SHOW WARNINGS；

用于显示创建数据库时的创建语句：SHOW CREATE DATABASE customers；

获取当前所选的数据库中所有可用的表：SHOW TABLES；

获取表中所有列的信息：SHOW COLUMNS FROM tableName；同时DESCRIBE语句有相同的效果：DESCRIBE tableName；

获取服务器元数据

MySQL 的数字列类型

类型名涵义

TINYINT一个很小的整数

SMALLINT 一个小整数

MEDIUMINT 一个中等大小整数

INT、INTEGER一个正常大小整数

BIGINT一个大整数

FLOAT一个小(单精密)浮点数字

DOUBLE一个正常大小(双精密)浮点数字

DOUBLE PRECISION

REAL

DECIMAL 一个未压缩(unpack)的浮点数字， “未

NUMERIC 压缩”意味着数字作为一个字符串被存储

CHAR 一个定长字符串

VARCHAR 一个变长字符串

TINYBLOB 最大长度为 255(2^8-1)个字符的

TINYTEXT BLOB 或TEXT 列

BLOB 最大长度为 65535(2^16-1)个字符的

TEXT BLOB 或TEXT 列

MEDIUMBLOB 最大长度为16777215(2^24-1)个字符

MEDIUMTEXT的 BLOB 或TEXT 列；

LONGBLOB 最大长度为4294967295(2^32-1)个字

LONGTEXT 符的 BLOB 或TEXT 列；

ENUM('value1','value2',... 枚举：列只能赋值为某个枚举成员或

NULL

)

SET('value1','value2',...) 集合：列可以赋值为多个集合成员或

NULL

MySQL的数字列类型有两种：

整型 MySQL 提供了五种整型 tinyint、samllint、mediumint、int和bigin。整数列可以用 unsigned禁用负数值。浮点型 MySQL提供了三种浮点型，float、double和 decimal。

MySQL提供的字符串类型包括 CHAR、VARCHAR、BLOB、TEXT、ENUM和 SET

mysql数据库使用总结

本文主要记录一些mysql日常使用的命令，供以后查询。

1.更改root密码

mysqladmin -uroot password 'yourpassword'

修改密码：mysqladmin -u USER -p password PASSWORD（修改密码会提示输入原密码）

刷新：flush privileges

1,给用户配置初始密码123456：

mysqladmin -u root -password 123456

2,修改root用户密码为 abc123

mysqladmin -u root -p123456 password abc123

3,如果想去掉密码：

mysqladmin -u root -pabc123 password

4,root连接数据库有密码和无密码：

mysql -u root(-uroot) -p

mysql

5.root查看用户密码及权限

select user,host,password from mysql.user;

select * from mysql.db;

mysql账号权限添加：

mysql -uroot -p

mysql>insert into mysql.user(Host,User,Password) values('%', 'userrw', password('userrw'));

mysql>flush privileges;#刷新权限

创建一个可以从任何地方连接到服务器的一个超管账户，必须分配一个密码

mysql>grant allon *.* to 'user_name'@'%' identified by 'password';#赋予全部权限

格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”

创建主从账号：

grant replication slave on *.* to 'repl'@'192.168.56.%' identified by 'repl';

flush privileges;

删除授权：

mysql> revoke all privileges on *.* from root@”%”;

mysql> delete from user where user=”root” and host=”%”;

mysql> flush privileges;

重命名表:

mysql > alter table t1 rename t2;

备份：

mysqldump -hhostname -uusername -ppassword databasename > backup.sql;

恢复：

mysql -hhostname -uusername -ppassword databasename< backup.sql;

mysql>grantselect,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file on *.* to 'userrw'@'%' identified by 'userrw';

mysql>flush privileges;

#mysql 5.7以上版本添加mysql.user账号方法（password字段改为了authentication_string）：

CREATE USER 'user'@'localhost' IDENTIFIED BY 'userrw';

CREATE USER 'userro'@'localhost' IDENTIFIED BY 'userro';

grant all privileges on db.* to 'userrw'@'localhost';

grant all privileges on db.* to 'userro'@'localhost';

改数据库、表和列的字符集

更改数据库、表和列的字符集和排序规则属性以使用utf8mb4而不是utf8.

# For each database:ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;# For each table:ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;# For each column:ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;# (Don’t blindly copy-paste this! The exact statement depends on the column type, maximum length, and other properties. The above line is just an example for a `VARCHAR` column.)

在MySQL配置文件(/etc/f)修改字符集为utf8mb4:

[client]default-character-set = utf8mb4[mysql]default-character-set = utf8mb4[mysqld]character-set-client-handshake = FALSEcharacter-set-server = utf8mb4collation-server = utf8mb4_unicode_ci

mysql>SET NAMES utf8mb4; # just to emphasize that the connection charset is set to `utf8mb4`

查看设置的字符集是否成功。

mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';+--------------------------+--------------------+| Variable_name | Value |+--------------------------+--------------------+| character_set_client| utf8mb4 || character_set_connection | utf8mb4 || character_set_database | utf8mb4 || character_set_filesystem | binary || character_set_results | utf8mb4 || character_set_server| utf8mb4 || character_set_system| utf8|| collation_connection| utf8mb4_unicode_ci || collation_database | utf8mb4_unicode_ci || collation_server | utf8mb4_unicode_ci |+--------------------------+--------------------+

修复和优化所有表

对要修复和优化的每个表运行以下MySQL查询：

# For each tableREPAIR TABLE table_name;OPTIMIZE TABLE table_name;

幸运的是，这可以很容易地使用命令行一次完成。mysqlcheck效用：

$ mysqlcheck -u root -p --auto-repair --optimize --all-databases

这将提示根用户的密码，在此之后，所有数据库中的所有表都将被修复和优化。

MySQL 修改最大连接数

修改方式有两种

1.命令行修改

进入mysql后，set GLOBAL max_connections=1024;即可立即生效，但是博主没有使用这种方式，因为这种方法治标不治本，一点重启mysql，最大连接数又会变回151

2.修改配置，然后重启

vi /etc/f加入max_connections=1024，然后重启mysql即可。

重启后，很遗憾，max_connections变成了214，这就很诡异了。我把max_connections又分别设置成500和213，实际的max_connections分别是214和213。也就是说，在这台服务器上，max_connections最大只能是234，猜测是因为操作系统的限制导致max_connections最大只能为213。max_connections依托于操作系统，Linux系统必要时需要增加open-files-limit。修改max_connections竟然要修改操作系统最大文件描述符。

vi/usr/lib/systemd/system/mysqld.service加入

LimitNOFILE=50000

重启MySQL

=

将 MySQL 8.0 中已有的SHA2密码修改为SHA1的模式。

# 更新用户的密码加密方式为之前版本的方式mysql> ALTER USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY 'password';# 刷新权限mysql> FLUSH PRIVILEGES;

注：如果没有特殊的原因，建议使用更安全的新加密方式。

在 MySQL 8.0 用之前版本授权语句创建用户。

mysql> GRANT ALL PRIVILEGES ON *.* TO `mike`@`%` IDENTIFIED BY '000000' WITH GRANT OPTION;ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IDENTIFIED BY '000000' WITH GRANT OPTION' at line 1

在 MySQL 8.0 版本中正确授权语句。

mysql> CREATE USER 'mike'@'%' IDENTIFIED BY '000000';mysql> GRANT ALL ON *.* TO 'mike'@'%' WITH GRANT OPTION;

密码过期时间管理

并在 MySQL 5.7.4 版本中改进了用户密码过期时间这个特性。现在可以通过一个全局变量default_password_lifetime来设置一个全局的自动密码过期策略。

default_password_lifetime其默认值为 0，表示禁用自动密码过期。default_password_lifetime的值如是是正整数 N ，则表示允许的设置密码生存周期 为 N，单位为天 。

default_password_lifetime全局密码到期策略默认为永久，不过期。

mysql> show variables like 'default_password_lifetime';+---------------------------+-------+| Variable_name | Value |+---------------------------+-------+| default_password_lifetime | 0 |+---------------------------+-------+1 row in set (0.00 sec)

如果你要建立一个全局策略，让所有用户的密码的使用期限为六个月，可在服务端配置文件 f 中修改default_password_lifetime配置项的值为 180。

[mysqld]default_password_lifetime=180

如果你要恢复全局策略，让所有用户的密码永不过期，可在服务端配置文件 f 中修改default_password_lifetime配置项的值为 0。

[mysqld]default_password_lifetime=0

default_password_lifetime参数是支持永久动态设置的，你也可以用以下命令在 MySQL 命令行下直接设置生效。

# 设置默认密码过期策略为 180 天后过期mysql> SET PERSIST default_password_lifetime = 180;# 设置默认密码过期策略为永不过期mysql> SET PERSIST default_password_lifetime = 0;# MySQL 8.0 永久动态修改参数会保存在配置文件 mysqld-f 中，保存的格式为JSON串。$ cat /var/lib/mysql/mysqld-f{ "Version" : 1 , "mysql_server" : { "default_password_lifetime" : { "Value" : "180" , "Metadata" : { "Timestamp" : 1525663928688419 , "User" : "root" , "Host" : "" } } } }

创建和修改带有密码过期时间的用户示例

创建或修改一个用户的密码过期时间为 90 天。

mysql> CREATE USER 'mike'@'%' IDENTIFIED BY '000000' PASSWORD EXPIRE INTERVAL 90 DAY;mysql> ALTER USER `mike`@`%` PASSWORD EXPIRE INTERVAL 90 DAY;

创建或修改一个用户的密码过期时间为永不过期。

mysql> CREATE USER 'mike'@'%' PASSWORD EXPIRE NEVER;mysql> ALTER USER 'mike'@'%' PASSWORD EXPIRE NEVER;

创建或修改一个遵循全局到期策略的用户。

mysql> CREATE USER 'mike'@'%' PASSWORD EXPIRE DEFAULT;mysql> ALTER USER 'mike'@'%' PASSWORD EXPIRE DEFAULT;

查看用户的密码过期时间。

mysql> select user,host,password_last_changed,password_lifetime,password_expired from mysql.user;+------------------+-----------+-----------------------+-------------------+------------------+| user | host | password_last_changed | password_lifetime | password_expired |+------------------+-----------+-----------------------+-------------------+------------------+| mike | %| -05-07 11:13:39 | 90 | N || root | %| -05-04 16:46:05 | NULL | N || mysql.infoschema | localhost | -05-04 16:45:55 | NULL | N || mysql.session | localhost | -05-04 16:45:55 | NULL | N || mysql.sys | localhost | -05-04 16:45:55 | NULL | N || root | localhost | -05-04 16:46:05 | NULL | N |+------------------+-----------+-----------------------+-------------------+------------------+6 rows in set (0.00 sec)

用户管理

创建用户

create user '用户名'@'IP地址' identified by '密码';

删除用户

drop user '用户名'@'IP地址';

修改用户

rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';

修改密码

set password for '用户名'@'IP地址' = Password('新密码');

注：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

创建用户

create user '用户名'@'IP地址' identified by '密码';

删除用户

drop user '用户名'@'IP地址';

修改用户

rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';

修改密码

set password for '用户名'@'IP地址' = Password('新密码');

注：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

权限管理

mysql对于权限这块有以下限制：

all privileges：除grant外的所有权限select：仅查权限select,insert：查和插入权限...usage：无访问权限alter：使用alter tablealter routine：使用alter procedure和drop procedurecreate：使用create tablecreate routine：使用create procedurecreate temporary tables：使用create temporary tablescreate user：使用create user、drop user、rename user和revoke all privilegescreate view：使用create viewdelete：使用deletedrop：使用drop tableexecute：使用call和存储过程file：使用select into outfile 和 load data infilegrant option：使用grant 和 revokeindex：使用indexinsert：使用insertlock tables：使用lock tableprocess：使用show full processlistselect：使用selectshow databases：使用show databasesshow view：使用show viewupdate：使用updatereload：使用flushshutdown：使用mysqladmin shutdown(关闭MySQL)super：使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆replication client：服务器位置的访问replication slave：由复制从属使用

查看权限：

show grants for '用户'@'IP地址'

2、授权

grant 权限 on 数据库.表 to '用户'@'IP地址'

3、取消授权

revoke 权限 on 数据库.表 from '用户名'@'IP地址'

授权实例如下：

grant all privileges on db1.tb1 TO '用户名'@'IP'grant select on db1.* TO '用户名'@'IP'grant select,insert on *.* TO '用户名'@'IP'revoke select on db1.tb1 from '用户名'@'IP'

2.远程登陆mysql服务器

mysql -uroot -p -h192.168.137.10 -P3306

3.查询数据库/显示数据库列表

show databases;

缺省有两个数据库：mysql和test。 mysql库存放着mysql的系统和用户权限信息，我们改密码和新增用户，实际上就是对这个库进行操作。

4.进入某个数据库

use databasename;#切换数据库

show tables; #查看数据库全部表

select*from表名; # 查看表所有内容

select * from test01 limit 10； #查看前十行。

select Host,User,Password from user;#查看Host,User,Password 三个字段

select * from test01 where name="xiao_111"; #在test01中查看xiao

select * from test01 where name="xiao_111" and age=21;

select * from test01 where like name="%xiao_111%" and age=21;

搜索延迟，没有添加索引，重复执行一次命令。

创建数据库:

create database db1 DEFAULT CHARSET utf8 COLLATE utf8_general_ci; # utf8编码create database db1 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci; # gbk编码

查看某一个数据表，如表user

show createtable mysql.user;

或者show create table user;

或show create table mysql.user\G;（表的详细结构）

select curtime(); #返回当前的系统时间；

select now(); #返回当前的系统日期和时间；

show status like '%lock%';# 查询锁状态；

show engine innodb status\Gshow master status\G# 查看show engine innodb status\G 查看最近死锁信息；show slave master 用于提供有关从属服务器线程的关键参数的信息

show variables like '%max_connection%';#查看数据库连接数

set global max_connctions=500; #设置连接数为500；

set global innodb_stats_on_metadata=0 #设置并发处理数量为0

show processlist显示的状态里面，update表示正在insert ，updating表示正在delete，Updating才是表示正在update

查看表MySQL数据库的远程访问user的用户及密码

select host,user,password from mysql.user;

kill SESSION_ID;# 杀掉有问题的session；

select * from 表名#查询表中的内容

show engines \G #查询该数据库支持的存储引擎类型 ss ； \g 、\G与”；“效果一样，

show variables like 'have%'; #查询MySQL支持的存储引擎；

show variables like 'storage_engine'; #查询默认存储引擎； 修改默认的引擎在my.ini配置文件中修改，将"mysqld部分存在efault-storage-engine=INNODB"更改为"default-engine=MyISAM"。然后重启服务修改生效。

show create table user \G; #查看user表的存储引擎以及表的内容

alter table test rename text #将test表改成text表。

将在表user中增加一个没有完整性约束的phone字段

alter table user modify name varchar(30); #修改字段数据类型为varchar(30);

增加无完整性约束条件的字段

alter table user add phone varchar(20);#在user表中增加一个没有完整性约束条件约束的phone字段

修改字段的排列位置

将user表中的name字段 修改为该表的第一个字段。

alter table user modify name varchar(30) first;

删除字段

从user表中删除id字段

alter table user drop id;

表的第一个位置增加字段

在user表中第一个位置增加num字段，并设置num字段为主键

alter table user add num int(8) primary key first;

表指定位置之后增加位置

在user表的phone字段后增加address字段，并设置address字段为非空。

alter table user add address varchar(30) not null after phone;

更改表的存储引擎

将user表的存储引擎改为MyISAM.

alter table user ENGINE=MyISAM

show create table user \G;#查看修改的结果

show index from t; 使用show语句查看t表上的索引：

showcreatetablet\G使用show create table语句查看索引：

增加有完整性约束条件的字段

将在user表中增加一个有非空约束的age字段，其数据类型为INT(4).

alter table user add age int(4) not null;

表的第一个位置增加字段

将在user表中第一个位置增加num字段，并设置num字段为主键。增加num字段，并设置num字段为主键。

alter table user add num int(8) primary key first;

删除字段

将user表中删除id字段

alter table user drop id;

常见报错

Multiple primary key defined

多主键定义

Duplicate column name 'num'

重复列名“num”

查看当前所在数据库

select database();

注意：如果一个用户没有一个表的任何权限，表将不在 SHOW TABLES 或 mysqlshowdb_name 中的输出中显示。

5.删除表中数据

命令:delete from 表名 where 表达式

例如:删除表 MyClass 中编号为 1 的记录

mysql> delete from MyClass where id=1;

修改表中数据

命令:update 表名 set 字段=新值,… where 条件

mysql> update MyClass set name='Mary' where id=1;

在表中增加字段

命令:alter table 表名 add 字段 类型 其他;

例如:在表 MyClass 中添加了一个字段 passtest,类型为 int(4),默认值为 0

mysql> alter table MyClass add passtest int(4) default '0'

更改表名

命令:rename table 原表名 to 新表名;

例如:在表 MyClass 名字更改为 YouClass

mysql> rename table MyClass to YouClass;

更新字段内容

命令:update 表名 set 字段名 = 新内容

update 表名 set 字段名 = replace(字段名, '旧内容', '新内容');

例如:文章前面加入 4 个空格

update article set content=concat(' ', content);

删除pad列上的histogram:

mysql > analyze table test drophistogram on pad;

ANALYZE TABLE sbtest1 DROP HISTOGRAM ON pad;

直方图统计信息存储于InnoDB数据词典中，可以通过information_schema表来获取。

mysql>show create table information_schema.column_statistics\G

root@information_schema 05:34:49>SHOW CREATE TABLE INFORMATION_SCHEMA.COLUMN_STATISTICS\G*************************** 1. row ***************************View: COLUMN_STATISTICSCreate View: CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `COLUMN_STATISTICS` AS select `mysql`.`column_statistics`.`schema_name` AS `SCHEMA_NAME`,`mysql`.`column_statistics`.`table_name` AS `TABLE_NAME`,`mysql`.`column_statistics`.`column_name` AS `COLUMN_NAME`,`mysql`.`column_statistics`.`histogram` AS `HISTOGRAM` from `mysql`.`column_statistics` where can_access_table(`mysql`.`column_statistics`.`schema_name`,`mysql`.`column_statistics`.`table_name`)character_set_client: utf8collation_connection: utf8_general_ci1 row in set (0.00 sec)

从column_statistics表的定义可以看到，有一个名为mysql.column_statistics系统表，但被隐藏了，没有对外暴露。

查询表上的直方图信息

>SELECT JSON_PRETTY(HISTOGRAM) FROM INFORMATION_SCHEMA.COLUMN_STATISTICS WHERE SCHEMA_NAME='sb1' AND TABLE_NAME = 'sbtest1'\G

将全局和session级别long_query_time设置为1.

> set global long_query_time=1;

> set session long_query_time=1;

用 ALTER TABLE 语句修改表的结构

增加列alter table tbl_name add col_name type例如,给表增加一列weightmysql>alter table pet add weight int;删除列alter table tbl_name drop col_name例如，删除列weight：mysql>alter table pet drop weight;改变列alter table tbl_name modify col_name type

例如，改变weight 的类型：

mysql> alter table pet modify weight samllint;另一种方法是：alter table tbl_name change old_col_name col_name type例如：mysql> alter table pet change weight weight samllint;给列更名mysql>alter table pet change weight wei;给表更名alter table tbl_name rename new_tbl例如，把 pet 表更名为 animalmysql>alter table pet rename animal;

6.查看某个表全部字段

desc slow_log;

show create table slow_log\G; #查看表的类型（不仅可以显示表信息，还可以显示建表语句）

7.查看当前用户

select user();#查询当前用户

select User from mysql.user;root#用户登录，查看所有用户

8.增加用户

mysql -uroot -pmysql;#连接到本机上的 MySQL。

mysql -h 127.0.0.1 -uroot -pmysql; #连接到远程主机上的 MYSQL。

修改新密码

在终端输入：

mysql -u用户名 -p密码，回车进入Mysql。> use mysql;> update user set password=PASSWORD('新密码') where user='用户名';> flush privileges; #更新权限> quit; #退出

增加新用户

格式:

grant select on 数据库.* to 用户名@登录主机 identified by '密码'

例子 ：

例 1 :增加一个用户 test1 密码为 abc,让他可以在任何主机上登录,并对所有数据库有

查询、插入、修改、删除的权限。首先用以 root 用户连入 MySQL,然后键入以下命令:

mysql>grant select,insert,update,delete on *.* to root@localhost identified by 'mysql';

或者

grant all privileges on *.* to root@localhost identified by 'mysql';

然后刷新权限设置。

flush privileges;

例 2:如果你不想 root 有密码操作数据库“mydb”里的数据表,可以再打一个命令将密码消掉。

grant select,insert,update,delete on mydb.* to root@localhost identified by '';

删除用户

hadoop@ubuntu:~$ mysql -u用户名 -p密码mysql>delete from user where user='用户名' and host='localhost';mysql>flush privileges;//删除用户的数据库mysql>drop database dbname;

9.创建新数据库（可以指定字符集）

create database db1 charset utf8;

创建GBK字符集数据库mingongge并查看完整创建语句 :create database mingongge default charset gbk collate gbk_chinese_ci;

创建用户mingongge使用之可以管理数据库mingongge :grant all on mingongge.* to 'mingongge'@'localhost' identified by 'mingongge';

查看创建用户mingongge的权限 :show grants for mingongge@localhost;

创建数据库：create database db01;

查看当前数据库有哪此用户 :select user from mysql.user;

使用/进入数据库： use db01;

创建用户：create user guest@localhost identified by '123456'；

授权：grant select on mysql.* to guest@localhost identified by '123456';

grant select,insert,update,delete on *.* to user1@localhost Identified by "password1"; --此命令也可以作为修改密码的命令

创建用户并all权限给在test_gbk库所有表，密码‘123456’ #grant all on test_gbk.* to 'testuser'@'localhost' identified by '123456';

刷新权限，使权限生效 :flush privileges;

查看用户有哪些权限 :show grants for 'testuser'@'localhost';

收回权限 :revoke insert,update,select,delete on test_gbk.* from 'testuser'@'localhost';

10.创建新表

mysql >use student;

mysql > create table t1 (`id` int(4), `name` char(40));

mysql> show create table t1\G; #

##通过现有的表创建新表

mysql> create table T2 as select * from T1;

查看建表结构及表结构的SQL语句

desc test;

show create table test\G

插入一条数据“1,mingongge”

insert into test values('1','mingongge');

再批量插入2行数据“2,民工哥”,“3,mingonggeedu”

insert into test values('2','民工哥'),('3','mingonggeedu');

删除students表中的 id 为 3 的行: delete from students where id=3;

删除所有年龄小于 21 岁的数据: delete from students where age<20;

删除表中的所有数据: delete from students;

查询名字为mingongge的记录

select * from test where name = 'mingongge';

把数据id等于1的名字mingongge更改为mgg

update test set name = 'mgg' where id = '1';

把数据id等于2的age年龄更改为22（岁）

update test set age= '22' where id = '2';

update 语句可用来修改表中的数据, 简单来说基本的使用形式为:

update 表名称 set 列名称=新值 where 更新条件;

以下是在表 students 中的实例 :

将 id 为 5 的手机号改为默认的-: update students settel=default where id=5;

将所有人的年龄增加 1: update students set age=age+1;

将手机号为 13288097888 的姓名改为 "小明", 年龄改为 19: update students setname="小明", age=19 wheretel="13288097888";

在字段name前插入age字段，类型tinyint(2)

alter table test add age tinyint(2) after id;

不退出数据库,完成备份mingongge数据库

#system mysqldump -uroot -ppassword -B mingongge >/root/mingongge_bak.sql

实例 ：

在数据库里，创建一个新的表

创建数据库 ： create database students;

使用数据库 ： use students;

创建student111表格 ： create table student111(id int, name varchar(20),age int,phone char(11),height float(3,2));

查看student111 表的表结构 : desc student111;

下面的test表中创建了3个重复索引，在ID列上建了主键、唯一索引、普通索引。

create table test(

id int not null PRIMARY KEY,

A int not null,

B int not null,

UNIQUE(ID),

INDEX(ID)

)engine=innodb;

在student111表里面插入5条数据

insert student111 values(1,'li',21,'11011011011',1.72);

insert student111 values(2,'li',23,'11011011012',1.98);

insert student111 values(3,'li',42,'11011011013',1.60);

insert student111 values(4,'li',25,'11011011014',1.78);

insert student111 values(5,'li',28,'11011011015',1.89);

将student111表名修改为student_details

rename table student111 to student_details;

将student_details中的height字段删除

alter table student_details drop height;

在student_details里添加一个新字段，字段名为gender,并增加两条数据。

alter table student_details add gender char(5);

update student_details set gender='woman' where id = 1;

update student_details set gender='woman' where id = 2;

删除student_details表

dropstudent_details;

创建数据库表（create table 表名(内容)）

create table teacher01(id int,name varchar(20),salary float(4,2));

插入数据 ：(insert into 表名 value(内容))

group by 分组

GROUP BY子句的真正作用在于与各种聚合函数配合使用。它用来对查询出来的数据进行分组。

create table t_order(id int primary key,product varchar(20),price float(8,2));

insert into t_order values(1,'xiaomi', 1000);

insert into t_order values(2,'xiaomi',1100);

insert into t_order values(3,'huawei',2200);

insert into t_order values(4,'apple',8200);

1.对订单表中商品归类后，显示每一类商品的总价

select product,sum(price) from t_order GROUP BY product;

查询每一类商品总价格大于3000的商品

select product,sum(price) as total from t_order GROUP BY productHAVINGtotal>3000;

WHERE是在分组(group by)前进行条件过滤，

HAVING子句是在分组(group by)后进行条件过滤，

WHERE子句中不能使用聚合函数,HAVING子句可以使用聚合函数。

11.删除表t和mingongge数据库并查看

drop table test;

show tables;

drop database mingongge;

show databases;

12.清空表数据

truncate table db1.t1;

删除test表中的所有数据，并查看

delete from test;

select * from test;

13.更改表的某一行数据

update db1.t1 set name='aaa' where id=1;

14.查询表数据

select * from mysql.db; //查询该表中的所有字段

select count(*) from mysql.user; //count(*)表示表中有多少行

select db,user from mysql.db; //查询表中的多个字段

select * from mysql.db where host like '10.0.%'; #在查询语句中可以使用万能匹配 “%”

15.清空数据库中的所有表（数据库名是eab12）

mysql -N -s information_schema -e "SELECT CONCAT('TRUNCATE TABLE ',TABLE_NAME,';') FROM TABLES WHERE TABLE_SCHEMA='eab12'" | mysql -f eab12

16.查看数据库版本

select version();

select batabase();#查看当前连接的数据库，相当于pwd

select user();#查看当前连接数据库的用户,相当于whoami

查看MySQL字符集

mysql> show variables like '%character%'；

character_set_server 默认的内部操作字符集

character_set_client 客户端来源数据使用的字符集

character_set_connection 连接层字符集

character_set_results 查询结果字符集

character_set_database当前选中数据库的默认字符集

character_set_system 系统元数据(字段名等)字符集

查看数据库编码

SHOW CREATE DATABASE db_name;

mysql> show create database wl;

查看表字符集编码

SHOW CREATE TABLE tbl_name;

查看字段字符集编码

mysql>show full columns from tbl_name

设置字符集编码（设置character_set_client、character_set_connection、character_set_results）;

SET NAMES xxx； 例如 ： set names gbk;

mysql> show variables like '%char%'; #查看现有字符集编码，设置好了，使用这个命令查看效果；

修改数据库字符集（只能改变后续创建表，无法改变已创建表）

ALTER DATABASE DATABASENAME CHARACTER SET XXX;

修改表的字符集

ALTER TABLE TABLENAME CHARACTER SET XXX；(只能改变后续添加数据)

ALTER TABLE TABLENAME CONVERT TO CHARACTER SET XXX;（全部重新写入）

修改列字符集

ALTER TABLE tbl_name CHANGE c_name c_name CHARACTER SET character_name [COLLATE …];

mysql>SHOW FULL COLUMNS FROM emp;

mysql>ALTER TABLE emp CHANGE ename ename VARCHAR(10) CHARACTER SET utf8;

mysql>SHOW FULL COLUMNS FROM emp;

17.查看数据库状态

show status; 当前会话状态

show global status;全局数据库状态

show slave status\G; 查看主从数据库状态信息

show tables;显示当前库所有表

Show Databases; 显示所有数据库

18.查询数据库参数

show variables;

19.修改数据库参数

show variables like 'max_connect%';

set global max_connect_errors = 1000;（重启数据库会失效，要在配置文件中修改）

20.查看当前数据库队列

show processlist;

21.创建普通用户并授权给某个数据库

grant all on databasename.* to 'user1'@'localhost' identified by '123456';

22.插入一行数据

insert into db1.t1 values (1, 'abc');19.

23.删除数据库

drop database db1;

24.数据库备份

mysqldump -uroot -p'yourpassword' mysql >/tmp/mysql.sql

25.数据库恢复

mysql -uroot -p'yourpassword' mysql </tmp/mysql.sql

26.新建普通用户

CREATE USER name IDENTIFIED BY 'ssapdrow';

27.更改普通用户密码

SET PASSWORD FOR name=PASSWORD('fdddfd');

28.查看name用户权限

SHOW GRANTS FOR name;

29.脚本中执行mysql命令

mysql -uuser -ppasswd -e"show databases"

echo "show databases"|mysql -uuser -ppassword

以下是执行大量mysql语句采用的方式

mysql -uuser -hhostname -ppasswd <<EOF

mysql语句

EOF

mysql> -- 下面我们另外添加一个新的 root 用户, 密码为空, 只允许 192.168.1.100 连接

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;

mysql> -- @'192.168.1.100'可以替换为@‘%’就可任意ip访问，当然我们也可以直接用 UPDATE 更新 root 用户 Host, 但不推荐, SQL如下:

mysql> -- UPDATE user SET Host='192.168.1.100' WHERE User='root' AND Host='localhost' LIMIT 1;

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

30、授所有权：

grant all privileges on icsdb1.* to iccard@'%' Identified by 'iccard' WITH GRANT OPTION;（其他ip访问）

grant all privileges on icsdb1.* to iccard@'localhost' Identified by 'iccard' WITH GRANT OPTION;（本机访问）

31、显示库中的数据表：

use mysql;

show tables;

32、显示数据表的结构：

show tables; #列出数据库中所有的表

describe 表名; #获取表结构

desc 表名； #获取表结构

show columns from 表名; #获取表结构

33、建库与删库：

create database 库名;

drop database 库名;

34、建表：

use 库名;

create table 表名(字段列表);

drop table 表名;

35、清空表中记录：

delete from 表名;

36、显示表中的记录：

select * from 表名;

37、mysql导出数据库

>mysqldump -h localhost -u dev01 -p ecmall>~/1127_ecmall.sql

导出数据表

38、MySQL最大连接数设置

MySQL的最大连接数默认是100

客户端登录：mysql -uusername -ppassword

设置新的最大连接数为200：mysql> set GLOBAL max_connections=200

显示当前运行的Query：mysql> show processlist

显示当前状态：mysql> show status

退出客户端：mysql> exit

查看当前最大连接数：mysqladmin -uusername -ppassword variables |find "max_con"

39、按字符集导出

$mysqldump -u root -p dbname --default-character-set=gbk > a.sql;

40、单独设置某个数据库字符集：

alter database testdb character set utf8;

41、查看mysql支持的编码：

show character set;

42、查看系统的字符集和排序方式的设定可以通过下面的两条命令：

mysql> SHOW VARIABLES LIKE ''character_set_%'';

43、导出数据

$ mysqldump -u root -p dbname --default-character-set=gbk > base_user.sql;

备份数据

单库备份

44、#mysqldump -uroot -p test >/download/testbak_$(date +%F).sql

# ll /download/#查看生成的以日期为名字的数据库

#egrep -v "^--|\*|^$" /download/testbak_-12-12.sql#查看备份文件内容

#mysqldump -uroot -p -B test >/download/testbak_$(date +%F)_b.sql

-B参数的作用一目了然，就是当我们的数据库丢失时，可以直接用此备份文件进行恢复，无需再重新建库、建表，然后再进行数据恢复的操作

压缩备份

#mysqldump -uroot -p -B test|gzip >/download/testbak_$(date +%F).sql.gz

#ll /download/testbak_-12-12.sql.gz

多库备份

#mysqldump -uroot -p -B test mysql|gzip >/download/testbak_$(date +%F).sql01.gz

#mysqldump -uroot -p -B --events test mysql|gzip >/download/testbak_$(date +%F).sql02.gz#-B忽略备份过程的提示信息 ， 此备份方法不常用

# ll /download/testbak_-12-12.sql02.gz

多库备份时就需要进行多次单库备份的操作

#mysqldump -uroot -p -B test|gzip >/download/testbackup_$(date +%F).sql.gz

#mysqldump -uroot -p -B --events mysql|gzip >/download/mysqlbak_$(date +%F).sql.gz

# ll /download/

单表备份

分库备份是为了恢复数据库时方便操作，但是同样面临问题，如果是某个库中的某一个表有损坏，但又不有全库进行恢复，所以实际生产中常用的是分库、分表进行备份，这样数据也备份了，恢复时也好操作。

#mysqldump -uroot -p -B test test >/download/test_testbak_$(date +%F).sql

#egrep -v "#|^$|\*" /download/test_testbak_-12-12.sql

45、创建一个innodb GBK表test,字段id int(4)和name varchar(16)

create table test (

id int(4),

name varchar(16)

)ENGINE=innodb DEFAULT CHARSET=gbk

46、 用 Alter Table 语句创建与删除索引

为了给现有的表增加一个索引，可使用 ALTER TABLE 或 CREATE INDEX 语句。

ALTER TABLE 最常用，因为可用它来创建普通索引、UNIQUE 索引或 PRIMARY KEY

索引，如：

ALTER TABLE tbl_name ADD INDEX index_name (column_list)；

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)；

ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column_list)；

其中 tbl_name 是要增加索引的表名，而 column_list 指出对哪些列进行索引。一个

(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。如果索引由不止一列组成，各列名之间用逗号分隔。索引名 index_name 是可选的，因此可以不写它，MySQL 将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改，因此可以在同时创建多个索引。

同样，也可以用 ALTER TABLE语句产出列的索引：

ALTER TABLE tbl_name DROP INDEX index_name；

ALTER TABLE tbl_name DROP PRIMARY KEY；

增加索引之前和之后

建这样的索引，以加速表的检

索速度：

mysql> ALTER TABLE student

-> ADD PRIMARY KEY(id),

-> ADD INDEX mark(english,Chinese,history);

注意 ：记住，使用 PRIMARY索引的列，必须是一个具有 NOT NULL属性的列，如果你愿意产看创建的索引的情况，可以使用SHOW INDEX 语句：mysql> SHOW INDEX FROM student;

再使用 ALTER TABLE 语句删除索引，删除索引需要知道索引的名字，你可以通过

SHOW INDEX 语句得到：

mysql> ALTER TABLE student DROP PRIMARY KEY,

-> DROP INDEX mark;

再产看表中的索引，其语句和输出为：

mysql> SHOW INDEX FROM student;

Empty set (0.01 sec)

用 CREATE\DROP INDEX 创建索引

利用 ALTER TABLE 语句创建索引（MySQL通常在内部将 CREATE INDEX 映射到 ALTER TABLE）。该语句创建索引的语法如下：

CREATE UNIQUE INDEX index_name ON tbl_name (column_list)

CREATE INDEX index_name ON tbl_name (column_list)

tbl_name、index_name 和 column_list 具有与 ALTER TABLE 语句中相同的含义。这里索引名不可选。很明显，CREATE INDEX 可对表增加普通索引或 UNIQUE 索引，不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

可利用 DROP INDEX 语句来删除索引。类似于 CREATE INDEX 语句，DROP INDEX通常在内部作为一条 ALTER TABLE 语句处理，

删除索引语句的语法如下：

DROP INDEX index_name ON tbl_name

由于 CREATE INDEX 不能创建 PRIMARY索引，需要创建一个多列索引。

ysql> CREATE INDEX mark ON student(english,chinese,history);

同样的检查 student 表，可知：

mysql> SHOW INDEX FROM student;

使用下面的语句删除索引：

mysql> DROP INDEX mark ON student;

在创建表时指定索引

在发布 CREATE TABLE 语句时为新表创建索引，

在定义表列的语句部分指定索引创建子句，如下所示：

CREATE TABLE tbl_name

(

INDEX index_name (column_list),

KEY index_name (column_list),

UNIQUE index_name (column_list),

PRIMARY KEY index_name (column_list),

)

增加 PRIMARY KEY 创建一个单列的 PRIMARY

KEY 索引，如下所示：

CREATE TABLE tbl_name

(

i INT NOT NULL PRIMARY KEY

)

该语句等价于以下的语句:

CREATE TABLE tbl_name

(

i INT NOT NULL,

PRIMARY KEY (i)

);

对索引列指定了 NOT NULL。如果是 ISAM 表，这是必须的，

因为不能对可能包含 NULL 值的列进行索引。如果是MyISAM 表，索引列可以为 NULL，只要该索引不是 PRIMARY KEY 索引即可。

在 CREATE TBALE语句中可以某个串列的前缀进行索引（列值的最左边 n 个字符）

如果对某个串列的前缀进行索引，应用 column_list 说明符表示该列的语法为

col_name(n) 而不用col_name。例如，下面第一条语句创建了一个具有两个 CHAR 列的表

和一个由这两列组成的索引。第二条语句类似，但只对每个列的前缀进行索引：

CREATE TABLE tbl_name

(

name CHAR(30),

address CHAR(60),

INDEX (name,address)

)

CREATE TABLE tbl_name

(

name CHAR(30),

address CHAR(60),

INDEX (name(10),address(20))

);

47、增加与删除列

mysql> alter table T1 add age int(4) not null;

mysql> alter table T1 drop age

48、更新表里的数据

mysql> update T1 set age=25 where name='zhang';

mysql> update T1 set age=23 where name='li';

49、删除数据

mysql> delete from T1 where age='22';

建索引与删除

mysql> create index indexT1 on T1(name(10));

mysql> drop index indexT1 on T1;

50、创建主键

mysql> alter table T1 add primary key(name);

mysql> desc T1;

51、创建与删除视图

mysql> create view t1view as select name from T1;

mysql> select * from t1view;

删除视图

mysql> drop view t1view;

mysql> select * from t1view;

ERROR 1146 (42S02): Table 'student.t1view' doesn't exist

###提示此视图不存在

查看视图

show table status like '视图名' \G; #查看视图基本信息

showcreate view视图名 \G； #查看视图详细信息

select * from information_schema.views \G; #查询 information_schema数据库下的views表；

CREATE OR REPLACE VIW#语句修改视图

显示department_view1有3个属性，分别是department、function、localtion属性。

truncate 删除数据，导致自动清空自增ID，前端返回报错 not found。

这个问题的出现，就要考虑下 truncate 和 delete 的区别了。

看下实验演练：

首先先创建一张表

CREATE TABLE `t` (`a` int(11) NOT NULL AUTO_INCREMENT,`b` varchar(20) DEFAULT NULL,PRIMARY KEY (`a`),KEY `b` (`b`)) ENGINE=InnoDB AUTO_INCREMENT=300 DEFAULT CHARSET=utf8

先用 delete 进行删除全表信息，再插入新值。

结果发现 truncate 把自增初始值重置了，自增属性从1开始记录了。当前端用主键id进行查询时，就会报没有这条数据的错误。

个人建议不要使用 truncate 对表进行删除操作，虽然可以回收表空间，但是会涉及自增属性问题。这些坑，我们不要轻易钻进去。

52、查找表里面的数据

表名：person_tbl )

查找name字段中以'st'为开头的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

查找name字段中以'ok'为结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

查找name字段中包含'mar'字符串的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：

mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

53、事务测试

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN开始一个事务

ROLLBACK事务回滚

COMMIT事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0禁止自动提交

SET AUTOCOMMIT=1开启自动提交

mysql> use RUNOOB;

Database changed

mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表

Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;

Empty set (0.01 sec)

mysql> begin;# 开始事务

Query OK, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test value(5);

Query OK, 1 rows affected (0.01 sec)

mysql> insert into runoob_transaction_test value(6);

Query OK, 1 rows affected (0.00 sec)

mysql> commit;#提交事务

Query OK, 0 rows affected (0.01 sec)

mysql> select * from runoob_transaction_test;

+------+

| id |

+------+

| 5 |

| 6 |

+------+

2 rows in set (0.01 sec)

mysql> begin;# 开始事务

Query OK, 0 rows affected (0.00 sec)

mysql> insert into runoob_transaction_test values(7);

Query OK, 1 rows affected (0.00 sec)

mysql> rollback; # 回滚

Query OK, 0 rows affected (0.00 sec)

mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入

+------+

| id |

+------+

| 5 |

| 6 |

+------+

2 rows in set (0.01 sec)

mysql>

54、MySQL ALTER命令

当我们需要修改数据表名或者修改数据表字段时，就需要使用到MySQL ALTER命令。

先创建一张表，表名为：testalter_tbl。

mysql> use RUNOOB;

Database changed

mysql> create table testalter_tbl

-> (

-> i INT,

-> c CHAR(1)

-> );

Query OK, 0 rows affected (0.05 sec)

mysql> SHOW COLUMNS FROM testalter_tbl;

+-------+---------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-------+---------+------+-----+---------+-------+

| i| int(11) | YES | | NULL ||

| c| char(1) | YES | | NULL ||

+-------+---------+------+-----+---------+-------+

2 rows in set (0.00 sec)

删除，添加或修改表字段

如下命令使用了 ALTER 命令及 DROP 子句来删除以上创建表的 i 字段：

mysql> ALTER TABLE testalter_tbl DROP i;

如果数据表中只剩余一个字段则无法使用DROP来删除字段。

MySQL 中使用 ADD 子句来向数据表中添加列，如下实例在表testalter_tbl 中添加 i 字段，并定义数据类型:

mysql> ALTER TABLE testalter_tbl ADD i INT;

执行以上命令后，i 字段会自动添加到数据表字段的末尾。

mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c| char(1) | YES || NULL | || i| int(11) | YES || NULL | |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)

如果你需要指定新增字段的位置，可以使用MySQL提供的关键字 FIRST (设定位第一列)， AFTER 字段名（设定位于某个字段之后）。

尝试以下 ALTER TABLE 语句, 在执行成功后，使用 SHOW COLUMNS 查看表结构的变化：

ALTER TABLE testalter_tbl DROP i;ALTER TABLE testalter_tbl ADD i INT FIRST;ALTER TABLE testalter_tbl DROP i;ALTER TABLE testalter_tbl ADD i INT AFTER c;

FIRST 和 AFTER 关键字可用于 ADD 与 MODIFY 子句，所以如果你想重置数据表字段的位置就需要先使用 DROP 删除字段然后使用 ADD 来添加字段并设置位置。

修改字段类型及名称

如果需要修改字段类型及名称, 你可以在ALTER命令中使用 MODIFY 或 CHANGE 子句 。

例如，把字段 c 的类型从 CHAR(1) 改为 CHAR(10)，可以执行以下命令:

mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

使用 CHANGE 子句, 语法有很大的不同。 在 CHANGE 关键字之后，紧跟着的是你要修改的字段名，然后指定新字段名及类型。尝试如下实例：

mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;

mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE 对 Null 值和默认值的影响

当你修改字段时，你可以指定是否包含值或者是否设置默认值。

以下实例，指定字段j 为 NOT NULL 且默认值为100 。

mysql> ALTER TABLE testalter_tbl -> MODIFY j BIGINT NOT NULL DEFAULT 100;

如果你不设置默认值，MySQL会自动设置该字段默认为 NULL。

修改字段默认值

你可以使用ALTER来修改字段的默认值，尝试以下实例：

mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c| char(1) | YES || NULL | || i| int(11) | YES || 1000 | |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)

你也可以使用 ALTER 命令及DROP子句来删除字段的默认值，如下实例：

mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;mysql> SHOW COLUMNS FROM testalter_tbl;+-------+---------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+---------+------+-----+---------+-------+| c| char(1) | YES || NULL | || i| int(11) | YES || NULL | |+-------+---------+------+-----+---------+-------+2 rows in set (0.00 sec)Changing a Table Type:

修改数据表类型，可以使用 ALTER 命令及 TYPE 子句来完成。尝试以下实例，我们将表 testalter_tbl 的类型修改为 MYISAM ：

注意：查看数据表类型可以使用 SHOW TABLE STATUS 语句。

mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;mysql> SHOW TABLE STATUS LIKE 'testalter_tbl'\G*************************** 1. row ****************Name: testalter_tblType: MyISAMRow_format: FixedRows: 0Avg_row_length: 0Data_length: 0Max_data_length: 25769803775Index_length: 1024Data_free: 0Auto_increment: NULLCreate_time: -06-03 08:04:36Update_time: -06-03 08:04:36Check_time: NULLCreate_options:Comment:1 row in set (0.00 sec)

修改表名

如果需要修改数据表的名称，可以在 ALTER TABLE 语句中使用 RENAME 子句来实现。

尝试以下实例将数据表 testalter_tbl 重命名为 alter_tbl：

mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;

注意 ：

alter其他用途：

修改存储引擎：修改为myisam

alter table tableName engine=myisam;

删除外键约束：keyName是外键别名

alter table tableName drop foreign key keyName;

修改字段的相对位置：这里name1为想要修改的字段，type1为该字段原来类型，first和after二选一，这应该显而易见，first放在第一位，after放在name2字段后面

alter table tableName modify name1 type1 first|after name2;

55、MySQL 索引

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

首先创建一个表：create table t1 (id int primary key,username varchar(20),password varchar(20));

创建索引

索引名一般是：表名_字段名

创建单个索引的语法：CREATE INDEX 索引名 on 表名（字段名）

CREATE UNIQUE INDEX indexName ON mytable(username(length)),

修改表结构

ALTER table mytable ADD UNIQUE [indexName] (username(length))

创建表的时候直接指定

CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );

给id创建索引：CREATE INDEX t1_id on t1(id);

给username和password创建联合索引：CREATE index t1_username_password ON t1(username,password);

其中index还可以替换成unique，primary key，分别代表唯一索引和主键索引;

注意 :索引列的基数越大，数据区分度越高，索引的效果越好。

避免创建过多的索引，索引会额外占用磁盘空间，降低写操作效率。

主键尽可能选择较短的数据类型，可以有效减少索引的磁盘占用提高查询效率。

一个表只能有一个主键索引，但是可以有多个唯一索引。

主键索引一定是唯一索引，唯一索引不是主键索引。

联合索引：将多个列组合在一起创建索引，可以覆盖多个列。（也叫复合索引，组合索引）

外键索引：只有InnoDB类型的表才可以使用外键索引，保证数据的一致性、完整性、和实现级联操作（基本不用）。

全文索引：MySQL自带的全文索引只能用于MyISAM，并且只能对英文进行全文检索 （基本不用）

删除索引

删除指定表中指定名称的索引，语法为

ALTER TABLE table_nameDROP INDEX index_name;DROP INDEX [indexName] ON mytable;

例如删除名称为idx_cust_name的索引，其SQL语句为：

ALTER TABLE customers

DROP INDEX idx_cust_name;

修改索引

在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作。

--先删除

ALTER TABLE user

DROP INDEX idx_user_username;

--再以修改后的内容创建同名索引

CREATE INDEX idx_cust_name ON customers (cust_name(8));

查看索引

在MySQL中，要查看某个数据库表中的索引也非常简单，只需要使用以下两个命令中的任意一种即可。

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name

SHOW INDEX FROM table_name [FROM db_name]

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀

SHOW INDEX FROM [db_name.]table_name

使用ALTER 命令添加和删除索引

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。ALTER TABLE tbl_name ADD INDEX index_name (column_list):添加普通索引，索引值可出现多次。ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name; \G........

56、MySQL 临时表

MySQL 临时表在我们需要保存一些临时数据时是非常有用的。临时表只在当前连接可见，当关闭连接时，Mysql会自动删除表并释放所有空间。

实例

mysql> CREATE TEMPORARY TABLE SalesSummary (-> product_name VARCHAR(50) NOT NULL-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO SalesSummary-> (product_name, total_sales, avg_unit_price, total_units_sold)-> VALUES-> ('cucumber', 100.25, 90, 2);mysql> SELECT * FROM SalesSummary;+--------------+-------------+----------------+------------------+| product_name | total_sales | avg_unit_price | total_units_sold |+--------------+-------------+----------------+------------------+| cucumber|100.25 |90.00 |2 |+--------------+-------------+----------------+------------------+1 row in set (0.00 sec)

当你使用SHOW TABLES命令显示数据表列表时，你将无法看到 SalesSummary表。

如果你退出当前MySQL会话，再使用SELECT命令来读取原先创建的临时表数据，那你会发现数据库中没有该表的存在，因为在你退出时该临时表已经被销毁了。

删除MySQL 临时表

默认情况下，当你断开与数据库的连接后，临时表就会自动被销毁。当然你也可以在当前MySQL会话使用DROP TABLE命令来手动删除临时表。

手动删除临时表的实例：

mysql> CREATE TEMPORARY TABLE SalesSummary (-> product_name VARCHAR(50) NOT NULL-> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00-> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00-> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0);Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO SalesSummary-> (product_name, total_sales, avg_unit_price, total_units_sold)-> VALUES-> ('cucumber', 100.25, 90, 2);mysql> SELECT * FROM SalesSummary;+--------------+-------------+----------------+------------------+| product_name | total_sales | avg_unit_price | total_units_sold |+--------------+-------------+----------------+------------------+| cucumber|100.25 |90.00 |2 |+--------------+-------------+----------------+------------------+1 row in set (0.00 sec)mysql> DROP TABLE SalesSummary;mysql> SELECT * FROM SalesSummary;ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist

57、MySQL 复制表

完全的复制MySQL的数据表，包括表的结构，索引，默认值等。 如果仅仅使用CREATE TABLE ... SELECT命令，是无法实现的。

完整的复制MySQL数据表，步骤如下：

使用SHOW CREATE TABLE命令获取创建数据表(CREATE TABLE) 语句，该语句包含了原数据表的结构，索引等。复制以下命令显示的SQL语句，修改数据表名，并执行SQL语句，通过以上命令 将完全的复制数据表结构。如果你想复制表的内容，你就可以使用INSERT INTO ... SELECT语句来实现。

实例

尝试以下实例来复制表 runoob_tbl 。

步骤一：

获取数据表的完整结构。

mysql> SHOW CREATE TABLE runoob_tbl \G;*************************** 1. row ***************************Table: runoob_tblCreate Table: CREATE TABLE `runoob_tbl` (`runoob_id` int(11) NOT NULL auto_increment,`runoob_title` varchar(100) NOT NULL default '',`runoob_author` varchar(40) NOT NULL default '',`submission_date` date default NULL,PRIMARY KEY (`runoob_id`),UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)) ENGINE=InnoDB 1 row in set (0.00 sec)ERROR:No query specified

步骤二：

修改SQL语句的数据表名，并执行SQL语句。

mysql> CREATE TABLE `clone_tbl` (-> `runoob_id` int(11) NOT NULL auto_increment,-> `runoob_title` varchar(100) NOT NULL default '',-> `runoob_author` varchar(40) NOT NULL default '',-> `submission_date` date default NULL,-> PRIMARY KEY (`runoob_id`),-> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)-> ) ENGINE=InnoDB;Query OK, 0 rows affected (1.80 sec)

步骤三：

执行完第二步骤后，你将在数据库中创建新的克隆表 clone_tbl。 如果你想拷贝数据表的数据你可以使用INSERT INTO... SELECT语句来实现。

mysql> INSERT INTO clone_tbl (runoob_id,-> runoob_title,-> runoob_author,-> submission_date)-> SELECT runoob_id,runoob_title,-> runoob_author,submission_date-> FROM runoob_tbl;Query OK, 3 rows affected (0.07 sec)Records: 3 Duplicates: 0 Warnings: 0

执行以上步骤后，你将完整的复制表，包括表结构及表数据。

另一种完整复制表的方法 :

CREATE TABLE targetTable LIKE sourceTable;INSERT INTO targetTable SELECT * FROM sourceTable;

其他:

可以拷贝一个表中其中的一些字段:

CREATE TABLE newadmin AS(SELECT username, password FROM admin)

可以将新建的表的字段改名:

CREATE TABLE newadmin AS( SELECT id, username AS uname, password AS pass FROM admin)

可以拷贝一部分数据:

CREATE TABLE newadmin AS(SELECT * FROM admin WHERE LEFT(username,1) = 's')

可以在创建表的同时定义表中的字段信息:

CREATE TABLE newadmin(id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY)AS(SELECT * FROM admin)

注意 ：

区分下mysql复制表的两种方式。

第一、只复制表结构到新表

create table 新表 select * from 旧表 where 1=2

或者

create table 新表 like 旧表

第二、复制表结构及数据到新表

create table新表 select * from 旧表

58、MySQL 序列使用

mySQL 序列是一组整数：1, 2, 3, ...，由于一张数据表只能有一个字段自增主键， 如果你想实现其他字段也实现自动增加，就可以使用MySQL序列来实现。

使用 AUTO_INCREMENT

MySQL 中最简单使用序列的方法就是使用 MySQL AUTO_INCREMENT 来定义列。

实例

以下实例中创建了数据表 insect， insect 表中 id 无需指定值可实现自动增长。

mysql> CREATE TABLE insect-> (-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> PRIMARY KEY (id),-> name VARCHAR(30) NOT NULL, # type of insect-> date DATE NOT NULL, # date collected-> origin VARCHAR(30) NOT NULL # where collected);Query OK, 0 rows affected (0.02 sec)mysql> INSERT INTO insect (id,name,date,origin) VALUES-> (NULL,'housefly','2001-09-10','kitchen'),-> (NULL,'millipede','2001-09-10','driveway'),-> (NULL,'grasshopper','2001-09-10','front yard');Query OK, 3 rows affected (0.02 sec)Records: 3 Duplicates: 0 Warnings: 0mysql> SELECT * FROM insect ORDER BY id;+----+-------------+------------+------------+| id | name | date | origin|+----+-------------+------------+------------+| 1 | housefly | 2001-09-10 | kitchen || 2 | millipede | 2001-09-10 | driveway || 3 | grasshopper | 2001-09-10 | front yard |+----+-------------+------------+------------+3 rows in set (0.00 sec)

获取AUTO_INCREMENT值

在MySQL的客户端中你可以使用 SQL中的LAST_INSERT_ID( ) 函数来获取最后的插入表中的自增列的值。

重置序列

如果你删除了数据表中的多条记录，并希望对剩下数据的AUTO_INCREMENT列进行重新排列，那么你可以通过删除自增的列，然后重新添加来实现。 不过该操作要非常小心，如果在删除的同时又有新记录添加，有可能会出现数据混乱。操作如下所示：

mysql> ALTER TABLE insect DROP id;mysql> ALTER TABLE insect-> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,-> ADD PRIMARY KEY (id);

设置序列的开始值

一般情况下序列的开始值为1，但如果你需要指定一个开始值100，那我们可以通过以下语句来实现：

mysql> CREATE TABLE insect-> (-> id INT UNSIGNED NOT NULL AUTO_INCREMENT,-> PRIMARY KEY (id),-> name VARCHAR(30) NOT NULL, -> date DATE NOT NULL,-> origin VARCHAR(30) NOT NULL)engine=innodb auto_increment=100 charset=utf8;

或者你也可以在表创建成功后，通过以下语句来实现：

mysql> ALTER TABLE t AUTO_INCREMENT = 100;

59、MySQL 处理重复数据

防止表中出现重复数据

在 MySQL 数据表中设置指定的字段为PRIMARY KEY（主键）或者UNIQUE（唯一）索引来保证数据的唯一性。

实例：下表中无索引及主键，所以该表允许出现多条重复记录。

CREATE TABLE person_tbl(first_name CHAR(20),last_name CHAR(20),sex CHAR(10));

如果想设置表中字段 first_name，last_name 数据不能重复，可以设置双主键模式来设置数据的唯一性， 如果设置了双主键，那么那个键的默认值不能为 NULL，可设置为 NOT NULL。如下所示：

CREATE TABLE person_tbl(first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),PRIMARY KEY (last_name, first_name));

如果设置了唯一索引，那么在插入重复数据时，SQL 语句将无法执行成功,并抛出错。

INSERT IGNORE INTO 与 INSERT INTO 的区别就是 INSERT IGNORE 会忽略数据库中已经存在的数据，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。这样就可以保留数据库中已经存在数据，达到在间隙中插入数据的目的。

以下实例使用了 INSERT IGNORE INTO，执行后不会出错，也不会向数据表中插入重复数据：

mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)-> VALUES( 'Jay', 'Thomas');Query OK, 1 row affected (0.00 sec)mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)-> VALUES( 'Jay', 'Thomas');Query OK, 0 rows affected (0.00 sec)

INSERT IGNORE INTO 当插入数据时，在设置了记录的唯一性后，如果插入重复数据，将不返回错误，只以警告形式返回。 而 REPLACE INTO如果存在 primary 或 unique 相同的记录，则先删除掉。再插入新记录。

另一种设置数据的唯一性方法是添加一个 UNIQUE 索引，如下所示：

CREATE TABLE person_tbl(first_name CHAR(20) NOT NULL,last_name CHAR(20) NOT NULL,sex CHAR(10),UNIQUE (last_name, first_name));

统计重复数据

以下将统计表中 first_name 和 last_name的重复记录数：

mysql> SELECT COUNT(*) as repetitions, last_name, first_name-> FROM person_tbl-> GROUP BY last_name, first_name-> HAVING repetitions > 1;

以上查询语句将返回 person_tbl 表中重复的记录数。 一般情况下，查询重复的值，请执行以下操作：

确定哪一列包含的值可能会重复。在列选择列表使用COUNT(*)列出的那些列。在GROUP BY子句中列出的列。HAVING子句设置重复数大于1。

过滤重复数据

如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据。

mysql> SELECT DISTINCT last_name, first_name-> FROM person_tbl;

也可以使用 GROUP BY 来读取数据表中不重复的数据：

mysql> SELECT last_name, first_name-> FROM person_tbl-> GROUP BY (last_name, first_name);

删除重复数据

如果你想删除数据表中的重复数据，你可以使用以下的SQL语句：

mysql> CREATE TABLE tmp SELECT last_name, first_name, sex FROM person_tbl GROUP BY (last_name, first_name, sex);mysql> DROP TABLE person_tbl;mysql> ALTER TABLE tmp RENAME TO person_tbl;

当然也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：

mysql> ALTER IGNORE TABLE person_tbl-> ADD PRIMARY KEY (last_name, first_name);

select 列名1，count（1） as count from 表名group by 列名1having count>1 and 其他条件select 列名1，列名2，count（1） as count from 表名group by 列名1，列名2 having count>1 and 其他条件

原理：先按照要查询出现重复数据的列，进行分组查询。count > 1 代表出现 2 次或 2 次以上。

示例：

/*查询重复数据*/select serialnum,cdate,count(*) as count from m_8_customer_temp_0820bak group by serialnum,cdate having count>1 and cdate>='-08-20 00:00:00';

60、MySQL 排序我们知道从 MySQL 表中使用 SQL SELECT 语句来读取：

MySQL 拼音排序

如果字符集采用的是 gbk(汉字编码字符集)，直接在查询语句后边添加 ORDER BY：

SELECT * FROM runoob_tblORDER BY runoob_title;

如果字符集采用的是 utf8(万国码)，需要先对字段进行转码然后排序：

SELECT * FROM runoob_tblORDER BY CONVERT(runoob_title using gbk);

61、创建数据表 runoob_test_tbl

mysql> use RUNOOB;

Database changed

mysql> create table runoob_test_tbl

-> (

-> runoob_author varchar(40) NOT NULL,

-> runoob_count INT -> );

Query OK, 0 rows affected (0.05 sec)

mysql> insertintorunoob_test_tbl (runoob_author, runoob_count) values ('RUNOOB', 20);

mysql> insertinto runoob_test_tbl (runoob_author, runoob_count) values ('菜鸟教程', NULL);

mysql> insertinto runoob_test_tbl (runoob_author, runoob_count) values ('Google', NULL);

mysql> insertinto runoob_test_tbl (runoob_author, runoob_count) values ('FK', 20);

mysql> select* from runoob_test_tbl;

+------------------+-------------------+

| runoob_author | runoob_count |

+---------------+---------------------+

| RUNOOB| 20|

| 菜鸟教程| NULL |

| Google | NULL |

| FK | 20 |

+------------------+------------------+

4 rows in set (0.01 sec)

查找数据表中 runoob_test_tbl 列是否为 NULL，必须使用IS NULL和IS NOT NULL，如下实例：

mysql> select* fromrunoob_test_tbl WHERE runoob_count IS NULL;

+----------------+----------------+

| runoob_author | runoob_count |

+-----------------+----------------+

| 菜鸟教程 | NULL |

| Google | NULL |

+---------------+------------------+

2 rows in set (0.01 sec)

mysql> select* from runoob_test_tbl WHERE runoob_count IS NOT NULL;

+----------------+-----------------+

| runoob_author | runoob_count |

+----------------+----------------+

| RUNOOB | 20 |

| FK | 20 |

+----------------+---------------+

2 rows in set (0.01 sec)

62、MySQL 及 SQL 注入

SQL注入，就是通过把SQL命令插入到Web表单递交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。

我们永远不要信任用户的输入，我们必须认定用户输入的数据都是不安全的，我们都需要对用户输入的数据进行过滤处理。

实例中，输入的用户名必须为字母、数字及下划线的组合，且用户名长度为 8 到 20 个字符之间：

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches)){$result = mysqli_query($conn, "SELECT * FROM users WHERE username=$matches[0]");}else {echo "username 输入异常";}

让我们看下在没有过滤特殊字符时，出现的SQL情况：

// 设定$name 中插入了我们不需要的SQL语句$name = "Qadir'; DELETE FROM users;";mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

没有对 $name 的变量进行过滤，$name 中插入了我们不需要的SQL语句，将删除 users 表中的所有数据。

在PHP中的 mysqli_query() 是不允许执行多个 SQL 语句的，但是在 SQLite 和 PostgreSQL 是可以同时执行多条SQL语句的，所以我们对这些用户的数据需要进行严格的验证。

63、MySQL 导出数据

使用 SELECT ... INTO OUTFILE 语句导出数据

以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中 :

mysql> SELECT * FROM runoob_tbl -> INTO OUTFILE '/tmp/runoob.txt';

你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'-> LINES TERMINATED BY '\r\n';

在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'LINES TERMINATED BY '\n'FROM test_table;

导出表作为原始数据

mysqldump是 mysql 用于转存储数据库的实用程序。它主要产生一个 SQL 脚本，其中包含从头重新创建数据库所必需的命令 CREATE TABLE INSERT 等。

使用mysqldump导出数据需要使用--tab选项来指定导出文件指定的目录，该目标必须是可写的。

以下实例将数据表 runoob_tbl 导出到 /tmp 目录中：

$ mysqldump -u root -p --no-create-info \--tab=/tmp RUNOOB runoob_tblpassword ******

导出 SQL 格式的数据

导出 SQL 格式的数据到指定文件，如下所示：

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txtpassword ******

以上命令创建的文件内容如下：

-- MySQL dump 8.23---- Host: localhost Database: RUNOOB----------------------------------------------------------- Server version 3.23.58---- Table structure for table `runoob_tbl`--CREATE TABLE runoob_tbl (runoob_id int(11) NOT NULL auto_increment,runoob_title varchar(100) NOT NULL default '',runoob_author varchar(40) NOT NULL default '',submission_date date default NULL,PRIMARY KEY (runoob_id),UNIQUE KEY AUTHOR_INDEX (runoob_author)) TYPE=MyISAM;---- Dumping data for table `runoob_tbl`--INSERT INTO runoob_tbl VALUES (1,'Learn PHP','John Poul','-05-24');INSERT INTO runoob_tbl VALUES (2,'Learn MySQL','Abdul S','-05-24');INSERT INTO runoob_tbl VALUES (3,'JAVA Tutorial','Sanjay','-05-06');

如果你需要导出整个数据库的数据，可以使用以下命令：

$ mysqldump -u root -p RUNOOB > database_dump.txtpassword ******

如果需要备份所有数据库，可以使用以下命令：

$ mysqldump -u root -p --all-databases > database_dump.txtpassword ******

--all-databases 选项在 MySQL 3.23.12 及以后版本加入。

该方法可用于实现数据库的备份策略。

将数据表及数据库拷贝至其他主机

在源主机上执行以下命令，将数据备份至 dump.txt 文件中:

$ mysqldump -u root -p database_name table_name > dump.txtpassword *****

如果完整备份数据库，则无需使用特定的表名称。

如果你需要将备份的数据库导入到MySQL服务器中，可以使用以下命令，使用以下命令你需要确认数据库已经创建：

$ mysql -u root -p database_name < dump.txtpassword *****

你也可以使用以下命令将导出的数据直接导入到远程的服务器上，但请确保两台服务器是相通的，是可以相互访问的：

$ mysqldump -u root -p database_name \| mysql -h other- database_name

以上命令中使用了管道来将导出的数据导入到指定的远程主机上。

a、导出数据库(没有数据)

mysqldump -uroot -p123456 --opt --add-drop-table --add-drop-database -d -B db >db.nodata.sql//-d no data;

mysqldump -uroot -p123456 --opt --add-drop-table --add-drop-database -B db >db.sql//struct and data

b、查询数据库中记录不为空的表

use information_schema;

select * from TABLES where TABLE_ROWS>0 AND TABLE_SCHEMA='db';

mysqldump -uroot -p123456db version >db-version.sql//导出db数据库version表的结构和数据

d、ｍｙｓｑｌ跨库查询(连库查询)

select subscriber.username,subscriber.password, member.email from db.subscriber,db2.member where subscriber.username=member.id and member.book_id='193' limit 20;

select subscriber.username,subscriber.password, member.email,member.book_id from db.subscriber,db2.member where subscriber.username=member.id and member.book_id='193';

64、MySQL 导入数据

1、mysql 命令导入

使用 mysql 命令导入语法格式为：

mysql -u用户名 -p密码 < 要导入的数据库数据(runoob.sql)

实例：

# mysql -uroot -p123456 < runoob.sql

以上命令将将备份的整个数据库 runoob.sql 导入。

2、source 命令导入

source 命令导入数据库需要先登录到数库终端：

mysql> create database abc;# 创建数据库mysql> use abc; # 使用已创建的数据库 mysql> set names utf8; # 设置编码mysql> source /home/abc/abc.sql # 导入备份数据库

3、使用 LOAD DATA 导入数据

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

如果指定LOCAL关键词，则表明从客户主机上按路径读取文件。如果没有指定，则文件在服务器上按路径读取文件。

能明确地在LOAD DATA语句中指出列值的分隔符和行尾标记，但是默认标记是定位符和换行符。

两个命令的 FIELDS 和 LINES 子句的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。

如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、[OPTIONALLY] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl-> FIELDS TERMINATED BY ':'-> LINES TERMINATED BY '\r\n';

LOAD DATA 默认情况下是按照数据文件中列的顺序插入数据的，如果数据文件中的列与插入表中的列不一致，则需要指定列的顺序。

如，在数据文件中的列顺序是 a,b,c，但在插入表的列顺序为b,c,a，则数据导入语法如下：

mysql> LOAD DATA LOCAL INFILE 'dump.txt' -> INTO TABLE mytbl (b, c, a);

4、使用 mysqlimport 导入数据

mysqlimport客户端提供了LOAD DATA INFILEQL语句的一个命令行接口。mysqlimport的大多数选项直接对应LOAD DATA INFILE子句。

从文件 dump.txt 中将数据导入到 mytbl 数据表中, 可以使用以下命令：

$ mysqlimport -u root -p --local database_name dump.txtpassword *****

mysqlimport命令可以指定选项来设置指定格式,命令语句格式如下：

$ mysqlimport -u root -p --local --fields-terminated-by=":" \--lines-terminated-by="\r\n" database_name dump.txtpassword *****

mysqlimport 语句中使用 --columns 选项来设置列的顺序：

$ mysqlimport -u root -p --local --columns=b,c,a \database_name dump.txtpassword *****

/mysql/mysql-database-import.html

65、创建存储过程

执行存储过程

使用CALL子句执行存储过程，CALL子句接受存储过程的名称以及需要传递的参数。

CALL ordertotal(1,TRUE,@total);

SELECT @total;

如果存储过程中定义了OUT类型的输入参数，那么在执行存储过程时需要传入变量，如这里@total，并且变量都是用@开始的。如果存储过程中没有参数的话，就用空圆括号表示即可，CALL ordertotal()；

删除存储过程

删除存储过程，可以使用DROP PROCEDURE子句。如DROP PROCEDURE ordertotal；

查询存储过程

1、显示创建一个存储过程的语句，可以使用SHOW CREATE PROCEDURE。如SHOW CREATE PROCEDURE ordertotal；

2、查询所有存储过程的状态，如果在定义存储过程中使用COMMENT添加注释，可以查看。同时可以LIKE进行过滤结果。如SHOW PROCEDURE STATUS LIKE '%order%'

66、事务处理

什么是事务？

事务处理是用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。事务处理是一种机制，用来管理必须成批执行的MySQL操作，它们要么时作为整体执行或者完全不执行。

关键概念：

事务：是指一组SQL语句；

回退：是指撤销指定的SQL语句的过程；

提交：指将未存储的SQL语句的结果写入数据库表中；

保留点：指事务处理中设置的临时占位符，可以对它发布回退。

如何创建执行事务？

START TRANSACTION;

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);

SELECT * FROM customers;

SAVEPOINT insertinto;

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);

ROLLBACK TO insertinto;

执行结果为：插入数据('1',5,18)有效，因为，只会从保留点SAFEPOINT之后开始回退，也就是说保留点SAFEPOINT之前的SQL语句执行的结果仍然有效。

有这样一些细节：

START TRANSACTION用来表示下面的SQL语句集为一段事务；

SAFEPOINT用于指定保留点insertinto；

ROLLBACK TO表示从指定保留点开始回退，也就是说保留点之前的SQL语句执行结果依然有效。如果仅仅使用ROLLBACK进行回退的话就表示从STAET TRANSACTION之后所有的SQL语句执行效果都会撤销。

MySQL提交（写或保存）操作是自动进行的，这称之为隐含提交。但是在事务处理块中，提交不会隐含进行，要使用COMMIT子句进行提交。如：

START TRANSACTION;

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('1',5,18);

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);

COMMIT;

采用COMMIT提交事务，如果两条SQL语句都执行成功，才会将数据都写入表中。

67、触发器

什么是触发器？

当某条SQL语句发生时，自动执行某些其他的SQL语句的时候就需要使用到触发器。触发器只能响应：DELETE、INSERT、UPDATE这三个特定操作。

MySQL创建触发器首先要创建触发器中待存储的表，然后再设定触发器被激活的时刻，最后在满足定义条件时触发，并执行触发器中定义的语句集合

如何创建触发器？创建触发器时需要给出最重要的四条信息：

全局唯一的触发器名；

触发器关联的表；

触发器在何时执行（操作执行之前或者之后）；

触发器应该响应的活动（DELETE、INSERT或者UPDATE）。

创建触发器的语法如下：

由于触发器只能响应特定的三种类型的操作，因此可创建的触发器也就三种类型:INSERT触发器，DELETE触发器以及UPDATE触发器。

/*创建触发器*/

CREATE TRIGGER insertcustomers AFTER INSERT ON customers

FOR EACH ROW SELECT NEW.cust_id INTO @newinsertid;

/*执行触发器*/

INSERT INTO customers (cust_name,item_price,item_quantity) VALUES ('2',5,18);

SELECT @newinsertid;

INSERT | UPDATE | DELETE

表示触发事件，用于指定激活触发器的语句的种类

INSERT：将新行插入表时激活触发器

DELETE： 从表中删除某一行数据时激活触发器

UPDATE：更改表中某一行数据时激活触发器

注意 ：

INSERT触发器

使用createtrigger来创建触发器；

after insert表明在插入行数据之后，触发器才会执行特征操作；

for eachrow表示对插入的每一行数据，触发器都起作用；

针对insert触发器，可以使用虚拟表new，来使用刚插入的行数据。比如例子中，selectNEW.cust_id into@newinsertid表示将新插入的行数据的id赋值给变量@newinsertid。

DELETE触发器

DELETE触发器在DELETE语句执行之前或者之后，需要知道以下两点：

在DELETE触发器代码内，可以引用一个名为OLD的虚拟表，来访问被删除的行；

OLD表中的数据只能读，不能被更新，而在INSERT触发器中，就可以通过NEW来更新被插入的行数据。

例如，针对customers表，当删除一行数据时，返回被删除数据的cust_id以及cust_name：

/*创建DELETE触发器*/

DELIMITER //

CREATE TRIGGER insertcustomers AFTER DELETE ON customers

FOR EACH ROW

BEGIN

SELECT OLD.cust_name INTO @deletecustname;

SELECT OLD.cust_id INTO @deletecustid;

END //

/*调用DELETE触发器*/

DELETE FROM customers WHERE cust_id = 3;

SELECT @deletecustname;

SELECT @deletecustid;

基本上与创建INSERT触发器一样，只不过在DELETE触发器中只能使用OLD来访问被删除的行数据。

UPDATE触发器

UPDATE触发器在UPDATE语句执行之前或者之后执行，需要知道一下几点：

在BEFORE UPDATE触发器中可以使用NEW和OLD来访问数据，而在AFTER UPDATE触发器中使用NEW来访问数据会报错，只能使用OLD来访问数据；

在BEFORE UPDATE触发器中，NEW中的值可以被改变，即允许更改将用于UPDATE的数据；

OLD中的行数据只能读，不能被更新。

一个UPDATE触发器示例如下：

/*创建UPDATE触发器*/

DELIMITER //

CREATE TRIGGER insertcustomers BEFORE UPDATE ON customers

FOR EACH ROW

BEGIN

SELECT NEW.cust_name INTO @beforeupdate;

SET NEW.cust_name = 'reset_name';

SELECT OLD.cust_name INTO @afterupdate;

END //

/*调用UPDATE触发器*/

UPDATE customers SET cust_name = 'happy' WHERE cust_id = 5;

SELECT @beforeupdate;

SELECT @afterupdate;

输出为@beforeupdate为‘happay’，而@afterupdate为'reset_name'。有这样一些细节：

NEW虚拟表中的数据可以更改，如这里采用SET NEW.cust_name = 'reset_name';，将待更新的cust_name由“happy”变成了“reset_name”；

在BEFORE UPDATE触发器中可以使用NEW和OLD来访问数据，而在AFTER UPDATE触发器中使用NEW来访问数据会报错。

删除触发器？

删除触发器，可以使用 DROP TRIGGER语句，比如DROP TRIGGER insertcustomers。触发器不能更新或者覆盖，如果要修改触发器，必须删除这个触发器。

68、索引使用策略及优化

1、创建索引

在经常查询而不经常增删改操作的字段加索引。

order by与group by后应直接使用字段，而且字段应该是索引字段。

一个表上的索引不应该超过6个。

索引字段的长度固定，且长度较短。

索引字段重复不能过多，如果某个字段为主键，那么这个字段不用设为索引。

在过滤性高的字段上加索引。

2、使用索引注意事项

使用like关键字时，前置%会导致索引失效。

使用null值会被自动从索引中排除，索引一般不会建立在有空值的列上。

使用or关键字时，or左右字段如果存在一个没有索引，有索引字段也会失效。

使用!=操作符时，将放弃使用索引。因为范围不确定，使用索引效率不高，会被引擎自动改为全表扫描。

不要在索引字段进行运算。

在使用复合索引时，最左前缀原则，查询时必须使用索引的第一个字段，否则索引失效；并且应尽量让字段顺序与索引顺序一致。

避免隐式转换，定义的数据类型与传入的数据类型保持一致。

69、修改MySQL默认字符集

修改/etc/f以下位置

[mysqld]

character-set-server=utf8

collation-server=utf8_general_ci

[client]

default-character-set=utf8

修改MYSQL默认类型为MYISAM

修改/etc/f文件，在[mysqld]下增加一句，同理可修改类型为INNODB 。

default-storage-engine=MYISAM

>show global variables like '%timeout'#查看数据库相关的时间。

70、使用 mysqladmin 工具创建和删除

在命令行环境下可以使用 mysqladmin 创建和删除数据库。

创建数据库：

shell> mysqladmin create db_name

删除数据库：

shell> mysqladmin drop db_name

如果出现下面的错误：

mysqladmin: connect to server at 'localhost' failed

error: 'Access denied for user: 'root@localhost' (Using password: YES)'

表示你需要一个可以正常连接的用户，请指定-u -p 选项，具体方法与 3.2 节介绍相同，在第七章中你将会学到用户授权的知识。

shell>mysqladmin -u root -p shutdown

Enter Password:***********

shell>mysqladmin -u root -p shutdown

Enter Password:***********

输入修改过的密码即可。

71、开启慢查询日志

方法一 ：

// 查询是否开启慢查询 ON OR OFF

show variables like 'log_slow_queries'

// 查询是否开启慢查询日志 ON OR OFF

show variables like 'show_query_log'

// 指定慢查询日志的存储位置 目录要具有写权限

set global show_query_log_file = '/home/mysql/sql_log/mysql-slow.log'

// 把没有使用索引的sql记录到慢查询日志中 如果table的数据量小，不建议开启该条设置，因为会记录所有没有用到索引的查询，会给日志填充很多垃圾信息

set global log_queries_not_using_indexes = on;

// 超过1秒钟查询记录到慢查询日志中

set global long_query_time = 1;

方法二 ：

通过my.ini开启慢查询日志(原理和用命令一样)，不过配置完成需要重启MySQL目录要具有写权限

log-slow-queries = '/home/mysql/sql_log/mysql-slow.log'

// 在windows的情况下需要绝对路径

log-slow-queries = 'C:/Program Files/MySQL/MySQL Server 5.5/log/mysql-slow.log'

long_query_time = 1

log-queries-not-using-indexes = on

慢查询日志所包含的内容

// 执行SQL的主机信息：ROOT用户在本地所执行的查询

# User@Host: root['root'] @ localhost []

// SQL的执行信息 查询时间、锁定时间、发送行数、扫描行数

# Query_time:0.000024 Lock_time:0.0000000 Rows_sent:0 Rows_examined:0

// SQL执行时间 时间戳

# SET timestamp = 1402389328;

// SQL的内容

# select CONCAT('storage engine:',@@storage_engine) as INFO;

慢查询日志分析工具

工具1：mysqldumpslow(官方工具) 一般集成在MySQL安装包中

直接使用mysqldumpslow命令即可使用

工具2：pt-query-digest工具

输入命令系统提示没有的话可以查找安装一下

工具3：自己写脚本将日志中的语句列出来，通过网页的形式查看

72、配置主从同步

#启动端口为3506的实例

[mysql@app ~]$ mysqld_safe --defaults-file=/data/inst3506/data3506/f &

[mysql@app ~]$ msyql -P3506

slave@localhost[(none)]> show variables like 'server_id';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| server_id | 3506 |

+---------------+-------+

#为从库添加指向主库的相关配置信息，该命令会生成及修改备库上的master.info及relay-log.info文件

slave@localhost[(none)]> CHANGE MASTER TO MASTER_HOST='192.168.163.130',

-> MASTER_USER='repl',

-> MASTER_PASSWORD='repl',

-> MASTER_PORT=3406,

-> MASTER_LOG_FILE='inst3406bin.000001',

-> MASTER_LOG_POS=0;

Query OK, 0 rows affected, 2 warnings (0.04 sec)

#出现了2个warnings，查看一下

slave@localhost[(none)]> show warnings \G

*************************** 1. row ******

#此时查看从库的状态信息

Slave_IO_Running: No #IO线程没有运行

Slave_SQL_Running: No #SQL线程没有运行

可以看到从库上的2个线程，一个是用于I/O线程，用于连接到主库请求主库发送binlog，一个是用于执行SQL的SQL线程。

slave@localhost[(none)]> start slave;#启动slave

robin@localhost[(none)]> show slave status\G#再次查看slave的状态

slave@localhost[(none)]> show processlist\G #

73、修改mysql的密码：

1：如下命令，在初次开启mysql服务后，会提示给用户：

#mysqladmin -u root password '123';

如果已经有密码存在，要修改的话：

#mysqladmin -u root -p password 'new_pwd';

之后输入旧密码回车即可

2. setpasswordfor'root'@'主机名'=password('密码');

3.alter user'root'@'localhost' identified by '123';

4.grant all on*.* to 'username'@'localhost'identified by 'password';

注意：grant select on *.* to dba@localhost; dba可以查询MySQL中所有数据库中的表。

grant all on*.* to dba@localhost; dba可以

管理MySQL中的所有数据库。

74、mysql远程访问修改密码：

1.执行如下命令设置MySQL数据库的远程访问。

mysql> grant all on *.* to 'root'@'%' identified by '111111';

mysql> flush privileges;

2.修改db.propertie文件的密码。

2.1修改 /mnt/datapool/msconf/db.propertie中的host的password，改成新password。

2.2修改当前类似云盘这类程序的配置db.properties文件修改密码，同上面1的操作差不多。

/Loongdisk/managerservice/db.properties的将host的password，改成新password。

75、授权

创建一个可以从任何地方连接到服务器的一个超管账户，必须分配一个密码mysql> grant all privileges on *.* to 'user_name'@'localhost' identified by 'password' ;格式：grant select on 数据库.* to 用户名@登录主机 identified by “密码”删除授权：mysql> revoke all privileges on *.* from root@”%”;mysql> delete from user where user=”root” and host=”%”;mysql> flush privileges;重命名表:mysql > alter table t1 rename t2;备份：mysqldump -hhostname -uusername -ppassword databasename > backup.sql;恢复：mysql -hhostname -uusername -ppassword databasename< backup.sql;

76、mysql表内容操作

1、增

insertinto表(列名,列名...)values(值,值,...)

insertinto表(列名,列名...)values(值,值,...),(值,值,值...)

insertinto表(列名,列名...)select(列名,列名...)from表

例：

insertintotab1(name,email)values('zhangyanlin','zhangyanlin8851@')

2、删

deletefrom表# 删除表里全部数据

deletefrom表whereid＝1andname＝'zhangyanlin'# 删除ID =1 和name='zhangyanlin' 那一行数据

3、改

update 表 set name ＝ 'zhangyanlin' where id>1

4、查

select*from表

select*from表whereid>1

selectnid,name,genderasggfrom表whereid>1

77、条件语句

条件判断where

通配符like

限制limit

排序asc，desc

分组group by

78、修改表

添加列： alter table 表名 add 列名 类型删除列： alter table 表名 drop column 列名修改列：alter table 表名 modify column 列名 类型; -- 类型alter table 表名 change 原列名 新列名 类型; -- 列名，类型添加主键：alter table 表名 add primary key(列名);删除主键：alter table 表名 drop primary key;alter table 表名 modify 列名 int, drop primary key;添加外键： alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);删除外键： alter table 表名 drop foreign key 外键名称修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

对于上述这些操作是不是看起来很麻烦，很浪费时间，别慌！有专门的软件能提供这些功能，操作起来非常简单，这个软件名字叫Navicat Premium ，大家自行在网上下载，练练手，但是下面的即将讲到表内容操作还是建议自己写命令来进行

分析工具

性能，结构和数据分析工具

Anemometer– 一个 SQL 慢查询监控器。

innodb-ruby– 一个对 InooDB 格式文件的解析器，用于 Ruby 语言。

innotop– 一个具备多种特性和可扩展性的 MySQL 版 ‘top’ 工具。

pstop– 一个针对 MySQL 的类 top 程序，用于收集，汇总以及展示来自 performance_schema 的信息。

mysql-statsd– 一个收集 MySQL 信息的 Python 守护进程，并通过 StatsD 发送到 Graphite。

备份

备份/存储/恢复 工具

MyDumper– 逻辑的，并行的 MySQL 备份/转储工具。

MySQLDumper– 基于 web 的开源备份工具-对于共享虚拟主机非常有用。

mysqldump-secure– 将加密，压缩，日志，黑名单和 Nagios 监控一体化的 mysqldump 安全脚本。

Percona Xtrabackup– 针对 MySQL 的一个开源热备份实用程序——在服务器的备份期间不会锁定你的数据库。

性能测试

给你的服务器进行压测的工具

iibench-mysql-基于 Java 的 MySQL/Percona/MariaDB 索引进行插入性能测试工具。

Sysbench– 一个模块化，跨平台以及多线程的性能测试工具。

79、慢查询日志

有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行sql命令查看相关变量，常用的sql如下：

-- 查看慢查询配置

-- slow_query_log 慢查询日志是否开启

-- slow_query_log_file 的值是记录的慢查询日志到文件中

-- long_query_time 指定了慢查询的阈值

-- log_queries_not_using_indexes 是否记录所有没有利用索引的查询

SHOW VARIABLES LIKE '%quer%';

-- 查看慢查询是日志还是表的形式

SHOW VARIABLES LIKE 'log_output'

-- 查看慢查询的数量

SHOW GLOBAL STATUS LIKE 'slow_queries';

mysqldumpslow的工具十分简单，我主要用到的是参数如下：

-t:限制输出的行数，我一般取前十条就够了

-s:根据什么来排序默认是平均查询时间at，我还经常用到c查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有t查询时间，查看那个语句特别卡。

-v:输出详细信息

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.-11-20-0500

80、

常见问题 ：

1、delete，drop，truncate 都有删除表的作用，区别在于：

1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete 是单杀，truncate 是团灭，drop 是把电脑摔了。2、delete 是 DML 语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL 语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop 是直接扇耳光说滚，不能反悔。3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

2、like 匹配/模糊匹配，会与%和_结合使用。

'%a'//以a结尾的数据'a%'//以a开头的数据'%a%' //含有a的数据'_a_' //三位且中间字母是a的'_a'//两位且结尾字母是a的'a_'//两位且开头字母是a的

查询以 java 字段开头的信息。

SELECT * FROM position WHERE name LIKE 'java%';

查询包含 java 字段的信息。

SELECT * FROM position WHERE name LIKE '%java%';

查询以 java 字段结尾的信息。

SELECT * FROM position WHERE name LIKE '%java';

3、UNION 语句：用于将不同表中相同列中查询的数据展示出来；（不包括重复数据）

UNION ALL 语句：用于将不同表中相同列中查询的数据展示出来；（包括重复数据）

使用形式如下：

SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称；SELECT 列名称 FROM 表名称 UNION ALL SELECT 列名称 FROM 表名称 ORDER BY 列名称；

4、group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：

SELECT name FROM employee_tbl GROUP BY name;

返回的结果集就是所有员工的名字。

分组后的条件使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by ，例如：

SELECT name ,sum(*) FROM employee_tbl WHERE id<>1 GROUP BY name HAVING sum(*)>5 ORDER BY sum(*) DESC;

5、直接切还到某一个数据库 ：mysql -u -pxxxxxxx -D db1(数据库) -o

6、MYSQL返回值的意思 ？

允许NULL值，则说明在插入行数据时允许不给出该列的值，而NOT NULL则表示在插入或者更新该列数据，必须明确给出该列的值；

DEFAULT表示该列的默认值，在插入行数据时，若没有给出该列的值就会使用其指定的默认值；

PRIMARY KEY用于指定主键，主键可以指定一列数据，而可以由多列数据组合构成，如PRIMARY KEY(cust_id,cust_name)；

ENGINE用于指定引擎类型。常见的引擎类型有这些：

（1）InnoDB是一个支持可靠的事务处理的引擎，但是不支持全文本搜索；

（2）MyISAM是一个性能极高的引擎，它支持全文本搜索，但是不支持事务处理；

（3）MEMORY在功能上等同于MyISAM，但由于数据存储在内存中，速度很快（特别适合于临时表）；

在创建表的时候可以使用FOREIGN KEY来创建外键，即一个表中的指FOREIGN KEY向另一个表中PRIMARY KEY。外键FOREIGN KEY用于约束破坏表的联结动作，保证两个表的数据完整性。同时也能防止非法数据插入外键列，因为该列值必须指向另一个表的主键。

1、FROM：对FROM左边的表和右边的表计算笛卡尔积，产生虚表VT1；

2、ON：对虚拟表VT1进行ON筛选，只有那些符合<join_condition>条件的行才会被记录在虚拟表VT2中；

3、JOIN：如果是OUT JOIN，那么将保留表中（如左表或者右表）未匹配的行作为外部行添加到虚拟表VT2中，从而产生虚拟表VT3；

4、WHERE：对虚拟表VT3进行WHERE条件过滤，只有符合<where_condition>的记录才会被放入到虚拟表VT4；

5、GROUP BY：根据GROUP BY子句中的列，对虚拟表VT4进行分组操作，产生虚拟表VT5；

6、CUBE|ROLLUP：对虚拟表VT5进行CUBE或者ROLLUP操作，产生虚拟表VT6；

7、HAVING：对虚拟表VT6进行HAVING条件过滤，只有符合<having_condition>的记录才会被插入到虚拟表VT7中；

8、SELECT：执行SELECT操作，选择指定的列，插入到虚拟表VT8中；

9、DISTINCT：对虚拟表VT8中的记录进行去重，产生虚拟表VT9；

10、ORDER BY：将虚拟表VT9中的记录按照<order_by_list>进行排序操作，产生虚拟表VT10；

11、LIMIT：取出指定行的记录，产生虚拟表VT11，并将结果返回。

12、IN类型，表示传递给存储过程；

13、OUT类型，表示存储过程返回的结果，在调用存储过程时需要传入@开始的变量；

14、INOUT类型，表示在存储过程中可以传入和传出；

15、DECLARE用来声明一个变量，如这里的total，taxrate。注意MySQL中定义变量时都是变量名在前，数据类型在后。

16、存储过程具体逻辑写在BEGIN END之间；

17、将值赋给变量使用INTO关键字；

7、删除表数据

1、如果从表中删除数据的话，可以使用DELETE子句。DELETE FROM customers WHERE cust_id = 10086；删除的数据必定是表中行数据，而不是某一列。因此，与UPDATE子句相比，DELETE子句并不需要指定是哪一列，而仅仅只需要指定具体的表名即可；

2、注意：如果不添加WHERE指定条件的话，会将整个表中所有行数据全部删除。另外，DELETE只是删除表中的数据，而不会删除表结构信息；

3、如果想删除表中全部的数据，可以使用TRUNCATE，比DELETE删除效率更高。

8、创建数据库 索引 的方法 ：

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，而组合索引，即一个索引包含多个列。

创建索引

创建索引有两种方式，一种是直接利用create index进行创建，另外一种则是通过修改表结构来进行添加，则是利用ALTER TABLE语句。

[UNIQUE|FULLTEXT|SPATIAL] 其中括号中的这三个关键字表示创建的索引类型，它们分别表示唯一索引、全文索引、空间索引三种不同的索引类型。如果我们不指定任何关键字，则默认为普通索引。

注意 ：在MySQL中，有两种不同形式的索引——BTREE索引和HASH索引。在存储引擎为MyISAM和InnoDB的表中只能使用BTREE，其默认值就是BTREE；在存储引擎为MEMORY或者HEAP的表中可以使用HASH和BTREE两种类型的索引，其默认值为HASH。

9、一个版本为12.1.0.2的Oracle数据库，每到晚上总会不定时地主机CPU持续到100%，应用同时会创建大量的数据到数据库中。当时应急方案是把这种相关的等待时间全部批量Kill掉，因为这些系统是在比较核心的库里，基本上每个系统被Kill掉的进程有几千个。

着手重点分析问题，通过ASH分析发现，出现这个异常等待是因为一个很简单的语句——SELECT USER FROM SYS.DUAL。

之后就通过这个语句来一步步关联， 看到底是哪个地方调用的，结果发现是在一个应用用户的登录TRIGGER中的用户判断步骤。这个USER是Oracle的内部函数，但就是这么简单的一个语句，就让整个库都Hang住了。

通过ASH发现该语句在我们恢复应用前有重新加载的过程。当时怀疑是硬件导致的，就通过这种方式去分析，结果发现是在晚上10点时被Oracle的自动任务做了一个统计信息的自动收集，收集完后，又因为它是一个登录的Trigger，用户在不断登录，在做登录解析时这个语句就没办法解析，所以才导致用户源源不断地卡在那里。而应用是需要新建连接的，新建的连接又无法进到库里面，就会导致连接数越来越多，全都卡在那里。最后通过锁定dual表统

计信息的收集来从根本上解决这个问题。

10、一个数据库是从10.2.0.5.X升级到10.2.0.5.18版本，升级后会不定时出现cursor:pin相关的一些等待。其实出现cursor:pin是很正常的，因为这个数据库的负载比较高，变化也较高，但问题是它是在升级之后出现的。运营认为这是升级之后出现的异常，我们就开始着手分析问题的原因。

第二个问题是在应急时发现的，有时异常出现时，某个库里有些语句的执行次数会特别高，甚至15min能达到上亿次，这对于一个正常的业务系统来说，出现这么高的执行频率是不正常的。

之后就去分析这些问题，发现这两个问题有相同的一些点，比如语句中间出现了个函数调用；比如说这个情况下，A表如果访问的数据量较大时，这些函数就有可能被调用很多次。

有一个语句，它执行一次可能会出现十几万次的函数调用。如果在调用的过程中，关联的那张表的执行计划发生了变化，比如说A表走了一个全程扫描，那可能会出现几千万次的函数调用。

总结了一些关于通过什么样的方法去快速定位、是否是函数调用导致的看法。在10g之前确实没有什么好的办法，因为它里面没有一个显示的关联，就可能通过代码去扫描，去找对应的语句。在11g后会比较简单一些，通过AS值相关的TOP LEVEL SQL ID就可以直接关联到是哪个语句调的函数导致的问题。

这里还有一个问题是函数调用。因为它调用的函数可能都是特别快的，但次数有会比较高，性能波动可能带来比较大的影响。之前我们有一个案例就发生在月底高峰，我们当时发现某个数据库中会出现很多CBC的等待，后来又发现有一个小表被频繁访问，那个小表就100多行数据，但可能它相关的语句每隔15min就调用了上千万次。

其实这么高的并发下，出现这种CBC的等待是很正常的。不过因为它只有100多行数据，且都集中在一个数据块里，所以才导致这个数据块特别热，就会一直出现这种CBC的等待。

方案：建一个PCTFREE99的索引，把表所有列的数据都包含进去，确保每个索引块里面只保留了一行数据，变相地把这100多行数据分到100多个块里。

做了这个操作后，CBC相关的问题被解决了，也顺利地撑过了业务高峰期，但第二天月初的报表发现又掉坑里了。

对于大表的分表改造就是先同步历史数据级改造，后做一个数据增量。

11、数据库CPU使用率频频100%又没有明显TOPSQL？

这次故障处理的过程反思可以发现，其实不少的隐患，在日常的巡检，SQL审核中都可以被发现，但到了故障现场，可能由于环境的复杂，被放大后出很多衍生故障，分析定位起来则麻烦不少。日常的巡检，SQL审核多花些精力，定期更新自己的知识库，这些时间最终都是高回报的。

1、100% HIGH CPU风险

100% CPU使用率的风险是大部分系统都存在的，最难受的是发生了HIGH CPU后，可能主机SSH都无法访问。这种风险建议在CPU_COUNT层面做调整，该参数默认值是使用全部的CPU资源，考虑对其进行调整为逻辑CPU-2（单机数据库）。

RAC架构下同一集群有多个数据库的结合具体情况考虑实例CPU隔离，参考support文档（1362445.1）。

resource manager在处理整体使用率上不太好用，业务上AP、TP混合的话，可以通过RM调整业务的优先级，保障重点业务。报表拖垮了交易系统的问题，可以通过RM来实施保障。

2、LGWR日志同步性能隐患

高CPU使用率（一般超过80%）或CPU个数过多情况下，LGWR性能可能受CPU调度影响较大，建议`_high_priority_processes` 设置LGWR。该参数调整需要重启数据库，建议规划后集中调整。

12、无法将该字符添加到数据库中：

ERROR 1366: Incorrect string value: '\xF0\x9D\x8C\x86' for column 'text' at row 1

SQL语句：

INSERT INTO `test`.`new_table` (`ID`, `text`) VALUES ('1', '?');

我试着运行这个命令

use test;ALTER TABLE new_table CHANGE text text VARCHAR(191) CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;

输出显示“0行受影响”。

0 row(s) affected Records: 0 Duplicates: 0 Warnings: 0

我猜utf8mb4不适用于我的行。

但当我运行以下命令时：

ALTER DATABASE test CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;1 row(s) affected

还会发生什么？

最新情况：找到了！我失踪了：

SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;

13、删除安装的所有的 mysql相关软件包

find / -name "mysql*" -exec rm -rf {} \;

14、yum无法安装MySQL数据库，

解决办法 ：

1、ping 查看网络情况，无法上网，先检测DNS配置

cat /etc/resolv.conf,发现有DNS,

再添加一个DNS地址，8.8.8.8,还是无法上网。

/etc/init.d/network restart #重启网卡

再查看DNS配置文件,#cat/etc/resolv.conf ，查看IP信息， #ifconfig，查看有没有设置的DNS

2、还是没有ping通，#cat /etc/sysconfig/network-scripts/ifcfg-eth0查看网关有没有配置。使用route -n再次查看网关设置超过，

#sestaus 查看防火墙状态

/etc/init.d/iptables stop

没有网关，添加一个，0.0.0.0表示外网的任意地址 ，应射到到网关地址192.168.149.2，从dev eth0出去。

15、如何清空MySQL日志

#cat /dev/null > /var/log/mariadb/mariadb.log

mysql5.5启动命令

#/usr/local/mysql55/bin/mysql_safe --user=mysql

查看日志

#tailn -nf 100 /var/log/mariadb/mariadb.log

#cat/var/log/mariadb/mariadb.log

常见问题 ：

查看有没有这目录

ll /var/run/mariadb/

创建目录

mysql5.5启动命令

#/usr/local/mysql55/bin/mysql_safe --user=mysql

16、MySQL读写分离

1）为什么保证MySQL主、从数据一致？

2）用户访问WEB网站，注册了用户名和密码，xiaowagn,123456;

3）用户注册成功之后，WEB将数据信息写入主库，此时从库如果没有同步主库用户和密码信息；

4）使用xiaowang用户和密码登录，WEB读取MYSQL读库，WEB会返回无此用户，提示重新注册；

5）MYSQL主备数据就要严格保持一致，避免发生上面的情况。

17、MYSQL主从

18、

19、

20、

21、

22、

参考连接：CPU使用率频频100%又没有明显TOPSQL？换个思路突破 ：https://mp./s/XaSvbsaYy9lGqgr37ysynA

链接 ：

MySQL 优化实施方案：https://mp./s/-lru6FAhkXTo7gLCCfWlyg

新手MySQL工程师必备命令速查手册 :https://mp./s/87BoE2-0mW_3qALyNSpiTw

MySQL监控工具Percona监控工具初探:https://www.centos.bz//01/percona%E7%9B%91%E6%8E%A7%E5%B7%A5%E5%85%B7%E5%88%9D%E6%8E%A2/

/shenqz/p/6962493.html

MariaDB 10.3首推系统版本表，误删数据不用跑路了！:https://mp./s/NJip_E3iVFVnp0golFz1Ig

企业面试题｜最常问的MySQL面试题集合（一）：https://mp./s?__biz=MzI0MDQ4MTM5NQ==&mid=2247486211&idx=1&sn=c8bbf47e3dd892443142ba9b33c37321&chksm=e91b6e1fde6ce7095709efd81614c72fcde19b00524e680a65458b25a181c73b227daa150506&scene=21#wechat_redirect

企业面试题｜最常问的MySQL面试题集合（二）：https://mp./s?__biz=MzI0MDQ4MTM5NQ==&mid=2247486284&idx=1&sn=5f8ed7d5985d7feb202bdcbd3343125c&chksm=e91b6e50de6ce746831e2744188a30d99d6729be7f1344ef4c5b6add4a2b456c1acf8f4a5f58&scene=21#wechat_redirect

参考链接 :

mysql数据库基础命令（一） :https://mp./s?__biz=MzI0MDQ4MTM5NQ==&mid=2247484584&idx=1&sn=3bf48189c571bacc1859ef72922ff6eb&chksm=e91b61b4de6ce8a2e2fb2908c8d401e01159780b4786224abbdccb6889f00bd77bfc45ec3f06&scene=21#wechat_redirect

MySQL 8.0优化器都有哪些新特性？ :https://mp./s/Hs8QXzXcluSPk9ktWkqbbw

MySQL 基础操作： /special/template/5b2f5f7de4b01a7cb45db105#map

: /mysql/mysql-database-info.html

面试中有哪些经典的数据库问题？ :https://mp./s/ns9T02-zsO9I3tdG3XJbcw

MySQL到底有多少种日志类型需要我们记住的！ :https://mp./s/yGytaV7owibajI04Z7rHNw

MySQL中update修改数据与原数据相同会再次执行吗？ :https://mp./s/1KJasUM5UcJ_WL_LzLtfrw

数据库怎么分库分表，垂直？水平？ ：https://mp./s/MMau4yMwxPTFnVEKpDHYpg

史上更全的MySQL高性能优化实战总结！ ：https://mp./s/Uib99Ogmjbs3Vym2vkXw5g

步步深入MySQL：架构->查询执行流程->SQL解析顺序！ ：https://mp./s/8W5KQKuHyJd9BQDwu0uY5Q

企业中MySQL主流高可用架构实战三部曲之MHA ：/sumongodb/1951495

mysql怎么创建触发器 ：https://mp./s/LouhSOdCTxSKRhHNuw701Q

怎样在 Ubuntu Linux 上安装 MySQL | Linux 中国 ：https://mp./s/NqrCwc9qcUbslAWdwqWyuw

MySQL用户和权限管理 ：https://mp./s/rqLLPw8_yHPudT-Kgtjc4A

MySQL数据库之连接查询 ： https://mp./s/oEec6yyabulQ9pDwMA8PQg

推荐一款支持 SQL/NoSQL 数据库的通用命令行工具 USQL ：https://mp./s/NmOqYtxS1taYmvGPstVrKw

MySQL 超级入门教程（内含资源福利） ：https://mp./s/2BT5JqCA_WV5OnlRC6-h-A

MySQL精选 | 枚举类型ENUM的DDL变更测试 :https://mp./s/xpVmB07JgSRE2Nat3lbtoA

MySQL史上最全性能优化方式 :https://mp./s/FqiGwfNWWposdqAC0zFofw

技术核心 | MySQL性能结构优化原理 ：https://mp./s/qrK8EdlZMfeReSAKpGnfJA

MySQL DBA基本知识点梳理和查询优化 ：https://mp./s/CdF4iCK72WUOl8c5wny0-Q

MySQL DBA必备工具使用的6大锦囊妙计 ：/sumongodb/1955184

企业主流MySQL高可用集群架构三部曲之PXC ：/sumongodb/1956086

MySQL主从延迟解决方案荐：/sumongodb/1958723

利用binlog2sql快速闪回误删除数据 - 别拿豆包不当干粮荐：/sumongodb/2046073

MySQL数据库之连接查询 ：https://mp./s/oEec6yyabulQ9pDwMA8PQg

支撑百万并发的数据库架构如何设计？ ：https://mp./s?__biz=MzUyNDkzNzczNQ==&mid=2247485858&idx=1&sn=823bdb7634e90c6775545930142d3582&chksm=fa24f6cacd537fdc01fcf8fc35374f113291a8f583bde4f482439b6764d55ab6c4f2bc71936b&scene=21#wechat_redirect

基于MySQL 5.7多源复制及Keepalived搭建三节点高可用架构：/tag/mysql%e9%ab%98%e5%8f%af%e7%94%a8

MySQL 备份和恢复 : /mysql_backup_and_recover

为什么你的SQL执行很慢:/p/96c5afdb84c6

找到MySQL服务器发生SWAP罪魁祸首 ： //11/30/mysql-faq-find-who-cause-mysql-swap.shtml

一次非常有意思的SQL优化经历:从30248.271s到0.001s：/p/9fab9266be84

巧用这19条MySQL优化，效率至少提高3倍 ：https://mp./s?__biz=MzUyNDkzNzczNQ==&mid=2247485791&idx=1&sn=b9f4097226c9d4ea13e0f1855bbe8b7b&chksm=fa24f637cd537f211ddec965f3b0ed3e3ebc0b03623dfec3f0b917298ea332d6c4968c407902&scene=21#wechat_redirect

MySQL优化 ：/tag/mysql%e4%bc%98%e5%8c%96

[MySQL优化案例]系列 — RAND()优化 ：//07/04/mysql-optimization-case-rand-optimize.shtml

[MySQL FAQ]系列 — 线上环境到底要不要开启query cache：//09/05/mysql-faq-why-close-query-cache.shtml

[MySQL FAQ]系列 — 为什么InnoDB表要建议用自增列做主键：//09/14/mysql-faq-why-innodb-table-using-autoinc-int-as-pk.shtml

优化案例 | 分区表场景下的SQL优化：//04/11/mysql-optimize-with-many-partitions.shtml

优化系列 | DELETE子查询改写优化 ：//06/29/mysql-optimization-rewrite-delete-subquery-to-join.shtml

重装上阵 | 最方便可靠的MySQL f生成工具 ：//03/06/new-my-cnf-generator-reload.shtml

比较全面的MySQL优化参考（下篇） ：//05/29/mysql-optimization-reference-2.shtml

tcpcopy，模拟在线MySQL压力测试的好帮手

MySQL 备份和恢复思路：/mysql_backup_and_recover

[MySQL FAQ]系列 — 为什么要关闭query cache，如何关闭：//03/27/mysql-faq-why-should-we-disable-query-cache.shtml

[MySQL优化案例]系列 — slave延迟很大优化方法 ：//04/12/mysql-optimization-case-howto-resolve-slave-delay.shtml

优化系列 | 实例解析MySQL性能瓶颈排查定位 ：/tag/%e4%bc%98%e5%8c%96

迁移Zabbix数据库到TokuDB：/tag/zabbix

如果觉得《CentOS下MySQL数据库常用命令总结 （资源）》对你有帮助，请点赞、收藏，并留下你的观点哦！