MySQL命令大全(2018.11.22阿星归档)

mysql

登录MySQL方法

1.单实例MySQL登录的方法

mysql#刚装完系统无密码情况登录方式，不需要密码mysql-uroot#刚装完系统无密码的情况登录mysql-uroot-p#这里标准的dba命令行登录mysql-uroot-poldboy#非脚本里一般不这样用，密码明文会泄露密码

2.适合多实例防止密码泄露的方法
设置变量

HISTCONTROL=ignorespacemysql-uroot-poldboy-S/data/3306/mysql.sock前面加空格将不记录登录信息

给启动脚本以及备份脚本等加700权限，用户和组改为root

chmod700/data/3306/mysqlchmod700/server/scripts/bak.sh

删除命令行记录

history-d历史命令序号history-c清除所有清除之后在root家目录里面还会有记录cat~/.bash.history

多实例MySQL本地登录

mysql-uroot-p-S/data/3306/mysql.sockmysql-uroot-p-S/data/3307/mysql.sock提示：多实例通过mysql的-S命令指定不同的sock文件登录不同的服务中

注意：多实例的远程连接无需指定sock路径

mysql-uroot-p-h127.0.0.1-P3307-h指定IP地址，-P指定端口号

登录后默认提示符是：mysql >这个提示符可以更改，就像linux命令行提示符一样

mysql

为了防止误操作，可以把提示符标记为测试环境，也可以写入配置永久生效

■ 在命令行修改登录提示

mysql>prompt\u@abcdocker\r:\m:\s->PROMPTsetto'\u@abcdocker\r:\m:\s->'root@abcdocker05:03:09->root@abcdocker05:03:11->root@abcdocker05:03:12->

配置文件修改登录提示符

在my.cnf配置中【mysql】模块下添加如下内容（注意，不是【mysqld】）保存后，无需重启mysql，退出当前session，重新登录即可

[mysql]prompt=\\u@oldboy\\r:\\m:\\s->MySQLhelp帮助Type'help;'or'\h'forhelp.Type'\c'toclearthecurrentinputstatement.mysql>SHOWDATABASESLIKE'%MY%';模糊匹配mysql>showdatabaseslike'd3306';mysql>showdatabaseslike'%3306';mysql>showdatabaseslike'%33%';更多参数helpshow

■ 强制关闭数据库的方法（慎用）

killallmysqldpkillmysqldkillall-9mysqldmysqld:noprocesskilledkill-9pid

■ 最好使用优雅停止

mysqladmin-uroot-poldboy123shutdown

■ 野蛮粗鲁杀死数据库导致故障企业案例：

http://oldboy.blog.51cto.com/2561410/1431161http://oldboy.blog.51cto.com/2561410/1431172

企业实战题7：

开发mysql多实例启动脚本：已知mysql多实例启动命令为：mysqld_safe–defaults-file=/data/3306/my.cnf&停止命令为：mysqladmin-uroot-poldboy123-S/data/3306/mysql.sockshutdown请完成mysql多实例启动启动脚本的编写要求：用函数，case语句、if语句等实现。

相关地址：
开发MySQL多实例启动脚本~

老男孩Shell企业面试题30道 [答案]

MySQL数据库安全策略介绍

为root设置比较复杂的密码删除无用的mysql库内的用户账号，只留root@localhost删除默认的test数据库删除用户的时候，授权的权限尽量最小，允许访问的主机范围最小化针对mysql数据库的用户处理，还有更严格的做法，例如删除root用户，添加新的管理员用户

truncalt table test和delete from test；区别

■truncate table test；速度更快。直接清空对应数据的物理文件。
■delete from test；速度慢，逻辑清除，按行删

为管理员root用户设置密码方法

mysqladmin-urootpassword‘oldboy’#没有密码的情况下mysqladmin-uroot-poldboypasswordoldboy123-S/data/3306/mysql.sock

适合多实例更改密码，强调，以上命令的是命令行执行，而不是进入root

■ 修改管理员root密码法一：
linux命令修改法

mysqladmin-uroot-poldboypassword‘oldboy123’#原密码，新密码mysqladmin-uroot-poldboypasswordoldboy456-S/data/3306/mysql.sock适合多实例

■ 修改管理员root密码法二：****sql
语句修改法

mysql>updatemysql.usersetpassword=oldboy456whereuser='root'andhost='localhost';这样设置是不可以登录的

这样设置的密码不可以使用，需要加密

mysql>updatemysql.usersetpassword=password('oldboy456')whereuser='root'andhost='localhost';

结果：如果不使用password这个变量 下面的密码将会是明文，明文我们无法登录

mysql>selectuser,host,passwordfrommysql.user;+------+-----------+-------------------------------------------+|user|host|password|+------+-----------+-------------------------------------------+|root|localhost|*7DB922C59F217871B8165D72BEC8ED731A0EFFA1||root|db01|||root|127.0.0.1|||root|::1||||localhost||||db01||+------+-----------+-------------------------------------------+6rowsinset(0.00sec)Rowsmatched:1Changed:1Warnings:0需要查看是否成功，出现Changed代表成功

设置完成之后我们需要刷新才可以登录

mysql>flushprivileges;

修改管理员root（所有）密码法三

setpassword=password('oldboy123');

MySQL密码丢失如何找回？

■ 单实例

a、/etc/init.d/mysqldstopb、mysqld_safe--skip-grant-tables--user=mysql&c、mysqld、修改完密码重启e、/etc/init.d/mysqldrestart

■ 多实例

/data/3306/mysqlstop无法停止killallmysqldmysqld_safe--defaults-file=/data/3306/my.cnf--skip-grant-tables--user=mysql&updatemysql.usersetpassword=password('oldboy456')whereuser='root'andhost='localhost';flushprivileges;mysql登录mysqladmin-uroot-poldboyshutdown/etc/init.d/mysqldstart

SQL结构化查询语言

什么是SQL？

SQL，英文全称StructuredQueryLanguage，中文意思是结构化查询语言，它是一种对关系型数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准语言。结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时用sql作为MySQL逻辑备份文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。他不要求用户指定对数据存放方法，也不需要用户了解具体的数据存放方式。

小结：SQL语句最常见的分类一般就是3类

DDL（DataDefinitionLanguage）——数据定义语言（CREATE，ALTER，DROP）管理基础数据，例如：库，表DCL（DataControlLanguage）——数据控制语言（GRANT，REVOKE，COMMIT，ROLLBACK）用户授权，权限回收，数据提交回滚等DML（DataManipulationLanguage）——数据操作语言（SELECT，INSERT，DELETE,UPDATE）针对数据库里的表里的数据进行操作，记录

命令讲解

■ 创建数据库

mysql>createdatabaseabcdocker;

■ 查看创建库的语句

showcreatedatabaseoldboy;+----------+-----------------------------------------------------------------+|Database|CreateDatabase|+----------+-----------------------------------------------------------------+|abcdocker|CREATEDATABASE`oldboy`/*!40100DEFAULTCHARACTERSETutf8*/|+----------+-----------------------------------------------------------------+1rowinset(0.00sec)

编译的时候指定了utf8所以这里显示utf8

1.网站程序字符集2.客户端的字符集3.服务器端字符集4.linux客户端字符集5.以上都要统一，否则会出现中文乱码

■ 创建不同字符集格式的数据库命令

mysql>createdatabaseabcdocker;#默认数据库配置，相当于创建拉丁字符集数据库mysql>createdatabaseabcdocker_gbkcharactersetgbkcollategbk_chinese_ci;创建gbk格式文件mysql>helpcreatedatabasemysql>showcharacterset;#查看字符集mysql>showcreatedatabaseoldboy_gbk;+------------+--------------------------------------------------------------------+|Database|CreateDatabase|+------------+--------------------------------------------------------------------+|abcdocker_gbk|CREATEDATABASE`oldboy_gbk`/*!40100DEFAULTCHARACTERSETgbk*/|+------------+--------------------------------------------------------------------+1rowinset(0.00sec)

如果编译的时候指定了特定的字符集，则以后创建对应字符集的数据库就不需要指定字符集

-DDEFAULT_CHARSET=utf8\-DDEFAULT_COLLATION=utf8_general_ci\-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii\提示：二进制软件包，安装的数据库字符集默认latinl

■ 查看数据库

showdatabases;selectdatabase();#相当于pwdselectuser();#查看当前用户selectversion();#查看当前版本()可以说是函数

■ 当前数据库包含的表信息

useoldboy#相当于cdshowtables#查看表orshowtablesfromwordpress#查看wordpress库下的表文件

■ 删除用户

dropuser'root'@'::1';如果drop删除不了（一般reshuffle符号或大写）可以用下面方式删除（以root，用户为例）deletefrommysql.userwhereuser=’root’andhost=’oldboy’;flushprivileges;

创建MySQL用户及赋予用户权限

1.通过在mysql中输入helpe grant得到如下信息

CREATEUSER'jeffrey'@'localhost'IDENTIFIEDBY'mypass';GRANTALLONdb1.*TO'jeffrey'@'localhost';GRANTSELECTONdb2.invoiceTO'jeffrey'@'localhost';GRANTUSAGEON*.*TO'jeffrey'@'localhost'WITHMAX_QUERIES_PER_HOUR90;

2.通过grant命令创建用户并授权

grant命令简单语法如下：grantallprivilegesondbname.*tousername@localhostidentifiedby‘passwd’;

说明：上述命令是授权localhost主机上通过用户username管理dbname数据库的所有权限，密码为passwd，其中username，dbname，passwd可根据业务的情况修改

　　对于web连接用户授权尽量采用最小化原则，很多开源软件都是web界面安装，因此在安装期间除了select,insert,update,delete4个权限外，还需要create,drop等比较危险的权限。

grantselect,insert,update,create,droponblog.*toblog@localhostidentifiedby‘123’

　　常规情况下授权select,insert,update,delete4个权限即可，有的源软件，例如discuz,bbs还需要create,drop等比较危险的权限。生成数据库表后，要收回create，drop授权

生产环境针对主库（写入主读为辅）用户的授权；

■ 普通环境：

本机：lnmp，lamp环境数据库授权grantallprivilegesONblog.*toblog@localhostidentifiedby‘123456’应用服务器和数据库服务器不在一个主机上授权；grantallprivilegesONblog.*toblog@10.0.0.%identifiedby‘123’严格的授权：重视安全，忽略了方便；grantselect,insert,update,deleteONblog.*toblog@10.0.0.%identifiedby‘123’生产环境从库（只读）用户的授权；grantselectONblog.*toblog@10.0.0.%identifiedby‘123’查看授权用户oldboy的具体的授权权限showgrantsfor‘oldboy’@’localhost’;

案例1.创建abcdocker用户，对test库具备所有权限，允许从localhost主机登录，密码是abcdocker123

grantallontest.*toabcdocker@localhostidentifiedby'abcdocker123';

■ 第一种：授权用户

grantallontest.*tooldboy@127.0.0.%identifiedby‘oldboy123’showgrantsforoldboy@’127.0.0.%’;查看授权用户+-------------------------------------------------------------------------------------------------------------+|Grantsforroot@127.0.0.1|+-------------------------------------------------------------------------------------------------------------+|GRANTUSAGEON*.*TO'root'@'127.0.0.1'IDENTIFIEDBYPASSWORD'*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'||GRANTALLPRIVILEGESON`test`.*TO'root'@'127.0.0.1'|+-------------------------------------------------------------------------------------------------------------+2rowsinset(0.00sec)

■ 第二种：授权方法

createuserbbs@'172.16.1.1/255.255.255.0'identifiedby'123456'；先授权可以登录的mysql>showgrantsforbbs@'172.16.1.1/255.255.255.0';mysql>grantselectonwordpress.*tobbs@'172.16.1.1/255.255.255.0';

授权局域网主机连接远程数据库

a.一条命令百分号匹配法

grantallon*.*to‘test@10.0.0.%’identifiedby‘test123’;

b、一条命令子网掩码配置法

grantallon*.*totest@’10.0.0.0/255.255.255.0’identifiedby‘test123’;

c、两条命令实现
先创建用户并设置密码；

createusertest@’10.0.0.%’identifiedby‘test123’;再对用户授权指定权限和管理库表grantallon*.*totest@10.0.0.0/255.255.255.0

最后记得上述每条grant命令都要刷新权限

flushprivileges

数据库远程登录

mysql-uwordpress-poldboy123-h172.16.1.51-P3306-h指定IP地址，-P指定服务端口号

创建类似于root系列的管理员用户，可以创建下级用户的用户

grantallprivilegeson*.*toroot@'127.0.0.1'identifiedby'oldboy123'withgrantoption;只需要在最后输入withgrantoption

回收用户权限

REVOKEINSERTON*.*FROM'jeffrey'@'localhost';

显示库的信息

mysql>showcreatedatabaseoldboy\G

MySQL包含关系
数据库服务器>数据库（多个实例）>多个库>多个表>多个字段行列（数据）

创建表

建表的基本命令语法：

createtable<表名>（<字段名1><类型n>);提示：其中createtable是关键字，不能更改，但是大小写可以变化

建表语句

下面是人工写法设计的建表语句例子，表名student

createtablestudent(idint(4)notnull,namechar(20)notnull,agetinyint(2)NOTNULLdefault'0',deptvarchar(16)defaultNULL);

查看创建表的语句

mysql>showcreatetablestudent;createtablestudent(#createtable表示创建表的固定关键字,student为表名idint(4)notnull,#学号列,数字类型，长度为4，不能为空值namechar(20)notnull,#名字列，定长字符类型，长度20，不能为空agetinyint(2)NOTNULLdefault'0',#年龄列，很小的数字类型，长度为2，不能为空，默认为0值deptvarchar(16)defaultNULL#系别列，变长字符类型，长度16，默认为空。ENGINE=lnnoDBDEFAULTCHARSET=latinl#引擎和字符集，引擎默认为InnoDB，字符集，继承库的latinl);

student表的直观显示，可以用下面表格显示。

createtablestudent(idint(4)notnull,namechar(20)notnull,agetinyint(2)NOTNULLdefault'0',deptvarchar(16)defaultNULL)ENGINE=InnoDBDEFAULTCHARSET=latinl;

需要注意的事：MySQL5.1和MySQL5.5
环境的默认建表语句中的引擎的不同，如果希望控制引擎，就要在建表语句里显示的指定引擎建表；

MySQL5.1以及默认引擎为MyISAM，MySQL5.5以后默认引擎为InnoDB

MySQL表的字段类型

1） 数字类型

2）日期和时间类型（DATE 日期类型：支持的范围是1000-01-01到9999-12-31。MySQL以YYYY-MM-DD格式来显示DATE值，但是允许你使用字符串或数字把值赋给DATE列）

3）字符串类型

1、 INT[(M)]型：正常大小整数类型

2、 CHAR(M)型：定长字符串类型，当存储时，总是用空格填满右边到指定的长度

3、 VARCHAR型：变长字符串类型

有关MySQL字段类型详细内容，可以参考MySQL手册

1． INT[(M)]型：正常大小整数类型

2． DOUBLE[M,D] [ZEROFILL]型：正常大小（双精密）浮点数字类型

3． DATE 日期类型：支持的范围是1000-01-01到9999-12-31.MySQL以YYY-MM-DD格式来显示DATE值，但是允许你使用字符串给数字把值赋给DATE列

4． CHAR(M)型：定长字符串类型，当存储时，总是是用空格填满右边到指定的长度

5． BLOB TEXT类型，最大长度65535（2^16-1）个字符

6． VARCHAR型：变长字符串类型

下面的图说明了CHAR和VARCHAR之间的差别：

解释：例如，VARCHAR(10)列可以容纳最大长度为10的字符串。实际存储需求是字符串（L）的长度，加上一个记录字符串长度的字节。对于字符串’abcd’，L是4，存储需要5个字节。

小结

■ char定长，不够的用空格补全，浪费存储空间，查询速度快，多数系统表字段都是定长
■ varchar变长，查询速度慢

例子：mysql.user用的就是定长

`Password`char(41)CHARACTERSETlatin1COLLATElatin1_binNOTNULLDEFAULT'',

user表用的是CHAR

生产场景案例：
某sns产品生产正式建表语句

usesns;setnamesgbk;CREATETABLE`subject_comment_manager`(`subject_comment_manager_id`bigint(12)NOTNULLauto_incrementCOMMENT'主键',`subject_type`tinyint(2)NOTNULLCOMMENT'素材类型',`subject_primary_key`varchar(255)NOTNULLCOMMENT'素材的主键',`subject_title`varchar(255)NOTNULLCOMMENT'素材的名称',`edit_user_nick`varchar(64)defaultNULLCOMMENT'修改人',`edit_user_time`timestampNULLdefaultNULLCOMMENT'修改时间',`edit_comment`varchar(255)defaultNULLCOMMENT'修改的理由',`state`tinyint(1)NOTNULLdefault'1'COMMENT'0代表关闭，1代表正常',PRIMARYKEY(`subject_comment_manager_id`),KEY`IDX_PRIMARYKEY`(`subject_primary_key`(32)),#<==括号内的32表示对前32个字符做前缀索引。KEY`IDX_SUBJECT_TITLE`(`subject_title`(32))KEY`index_nick_type`(`edit_user_nick`(32),`subject_type`)#<==联合索引，此行为新加的，用于给大家讲解的。实际表语句内没有此行。)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;

为表的字段创建索引* * * * * *

索引就象书的目录一样，如果在字段上建立了索引，那么以索引为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。创建主键索引查询数据库，按主键查询是最快的，每个表只有一个主键列，但是可以用多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引不要求内容必须唯一主键就类似我们在学校学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引。为表的字段创建索引*****数据库的索引就象书的目录一样，如果在字段上建立了索引，那么多以索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。创建主键索引查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列的所有内容必须唯一，而普通索引列不要求内容必须唯一。主键就类似我们在学习学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引。

建立主键索引的方法：

（1）在建表示，可以增加建立主键索引的句子如下：

droptablestudent;createtablestudent(idint(4)notnullAUTO_INCREMENT,namechar(20)notnull,agetinyint(2)NOTNULLdefault'0',deptvarchar(16)defaultNULL,primarykey(id),KEYindex_name(name));

提示：
■ primary key(id)<==主键
■ KEY index_name(name)<==name字段普通索引

（2）mysql> desc student; 查看刚刚创建的表结构。

+-------+-------------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+-------------+------+-----+---------+----------------+|id|int(4)|NO|PRI|NULL|auto_increment||name|char(20)|NO|MUL|NULL|||age|tinyint(2)|NO||0|||dept|varchar(16)|YES||NULL||+-------+-------------+------+-----+---------+----------------+

PRL为主键的标示，MUL为普通索引的表示
auto_increnment代表数据自增

利用alter命令修改id列为自增主键值

altertablestudentchangeididintprimarykeyauto_increment;

创建的表的时候，可以指定

mysql>createtablestudent(idint(4)notnullAUTO_INCREMENT,namechar(20)notnull,agetinyint(2)NOTNULLdefault'0',deptvarchar(16)defaultNULL,primarykey(id),KEYindex_name(name));

提示：

KEYindex_name(name)<==name字段普通索引优化：在唯一值多的列上建索引查询效率高还可以自定义自增的长度EBGUBE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=utf8;

查看索引（可能会不清晰）【前提都需要进入库】

mysql>showindexfromstudent;

查看索引\G可以查看的更详细

mysql>showindexfromstudent\G***************************1.row***************************主建一个表只能有一个Table:studentNon_unique:0Key_name:PRIMARYSeq_in_index:1Column_name:idCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREE表类型Comment:Index_comment:***************************2.row***************************索引一个表可以有多个Table:studentNon_unique:1Key_name:index_name名字Seq_in_index:1Column_name:name列Collation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREE类型Comment:Index_comment:2rowsinset(0.00sec)

查看表结构

descstudent；

建表后利用alter增加普通索引，删除建表时创建的index_name索引、

altertablestudentdropindexindex_name;

删除索引

altertablestudentdropindexindex_name;

操作实践：

给name创建索引，也可以按照上述进行指定字节

createindexindex_nameonstudent(name);

查看索引

mysql>showindexfromstudent;

创建语句

createindexindex_ageonstudent(name(8));

showindexfromstudent\G查看表位置表明显示***************************3.row***************************　　Table:student　　Non_unique:1Key_name:index_age　　　Seq_in_index:1　　Column_name:age　　Collation:A　　Cardinality:0Sub_part:8Packed:NULLNull:YESIndex_type:BTREEComment:Index_comment:3rowsinset(0.00sec)

为表的多个字段创建联合索引

　　如果查询数据的条件是多列时，我们可以为多个查询的列创建联合索引，甚至，可以为多个列的前n个字符创建联合索引，演示如下：

创建联合索引，和单个索引不冲突

mysql>createindexind_name_deptonstudent(name,dept);

联合索引作用，查询更快

mysql>showindexfromstudent\G***************************1.row***************************Table:studentNon_unique:0Key_name:PRIMARYSeq_in_index:1Column_name:idCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:***************************2.row***************************Table:studentNon_unique:1Key_name:index_nameSeq_in_index:1Column_name:nameCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:***************************3.row***************************Table:studentNon_unique:1Key_name:index_ageSeq_in_index:1Column_name:nameCollation:ACardinality:0Sub_part:8Packed:NULLNull:Index_type:BTREEComment:Index_comment:***************************4.row***************************Table:studentNon_unique:1Key_name:ind_name_deptSeq_in_index:1Column_name:nameCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:Index_type:BTREEComment:Index_comment:***************************5.row***************************Table:studentNon_unique:1Key_name:ind_name_deptSeq_in_index:2Column_name:deptCollation:ACardinality:0Sub_part:NULLPacked:NULLNull:YESIndex_type:BTREEComment:Index_comment:5rowsinset(0.00sec)

创建联合索引，并指定值大小

createindexind_name_deptonstudent(name(8),dept(10));name前8个字符，dept前10个字符***************************2.row***************************Table:studentNon_unique:1Key_name:index_deptSeq_in_index:1Column_name:nameCollation:ACardinality:0Sub_part:8Packed:NULLNull:Index_type:BTREEComment:Index_comment:***************************3.row***************************Table:studentNon_unique:1Key_name:index_deptSeq_in_index:2Column_name:deptCollation:ACardinality:0Sub_part:10Packed:NULLNull:YESIndex_type:BTREEComment:Index_comment:3rowsinset(0.00sec)

提示：尽量在唯一值多的大表上建立索引。

什么时候创建联合索引？

只有程序用这两个条件查询，采用联合索引，这个主要是看开发。
提示：按条件列查询数据时，联合索引是由前缀生效特性的

创建唯一索引（非主键）

createuniqueindexuni_ind_nameonstudent(name);

索引小结：

创建主键索引altertablestudentchageididintprimarykeyauto_increment;删除主键索引（主键列不能自增）altertablestudentdropprimarykey;创建普通索引altertablestudentaddindexindex_dept(dept);根据的前n个字符创建索引createindexindex_deptonstudent(dept(8));根据多个列创建联合索引createindexindex_name_deptonstudent(name,dept);创建唯一索引createuniqueindexuni_ind_nameonstudent(name);删除普通索引与唯一索引altertablestudentdropindexindex_dept;dropindexindex_deptonstudent;

索引列的创建及生效条件

问题1、既然索引可以加快查询速度，那么就给所有的列加索引吧？

解答：因为索引不但占用系统空间，而且更新数据时还需要维护索引数据的，因此索引是一把双刃剑，并不是越多越好，例如：数十到几百行的小表上无需建立索引，插入更新频繁，读取比较少的需要少建立索引

问题2、需要在哪些列上创建索引才能加快查询速度呢？

selectuser,hostfrommysql.userwherepassword=…..,索引一定要创建在where后的条件列上，而不是select后的选择数据的列上。另外，我们要尽量选择在唯一值多的大表上的列建立索引，例如，男女生性别列唯一值少，不适合建立索引。

查看唯一值数量

selectcount(distinctuser)frommysql.user;

唯一值就是相同的数量，例如查询user那么相同的user就是唯一值

mysql>selectcount(distinctuser)frommysql.user;+----------------------+|count(distinctuser)|+----------------------+|7|+----------------------+1rowinset(0.07sec)

用户列表，根据上放进行解释

mysql>selectuser,hostfrommysql.user;+-----------+---------------------------+|user|host|+-----------+---------------------------+|cyh|10.1.1.%||root|127.0.0.1||bbs|172.16.1.1/255.255.255.0||wordpress|192.168.1.%||oldboy|192.168.1.%/255.255.255.0||abc|localhost||blog|localhost||oldboy|localhost||root|localhost|+-----------+---------------------------+9rowsinset(0.00sec)

创建索引的基本知识小结：

■ 索引类似书籍的目录，会加快查询数据的速度
■ 要在表的列（字段）上创建索引
■ 索引会加快查询速度，但是也会影响更新的速度，因为更新要在维护索引数据
■ 索引列并不是越多越好，要在频繁查询的表语句where后的条件列上创建索引
■ 小表或重复值很多的列上可以不建索引，要在大表以及重复值少的条件上创建索引
■ 多个列联合索引有前缀生效特性
■ 当字段内容前N个字符已经接近唯一时，可以对字段的前N个字符创建索引
■ 索引从工作方式区别，有主键，唯一，普通索引
■ 索引类型有BTREE(默认)和hash（适合做缓存（内存数据库））等。

主键索引和唯一索引的区别

(1)对于主键/unique constraintoracle/sqlserver/mysql等都会自动建立唯一索引；
(2)主键不一定只包含一个字段，所以如果你在主键的其中一个字段建唯一索引还是必要的；
(3)主健可作外健，唯一索引不可；
(4)主健不可为空，唯一索引可；
(5)主健也可是多个字段的组合；
(6)主键与唯一索引不同的是：
a.有not null属性；
b.每个表只能有一个。

往表中插入数据

● 命令语法

insertinto<表名>[(<字段名1>[..<字段名n>])]values(值1)[，(值n)]

● 建立一个简单的测试表test

CREATETABLE`test`(`id`int(4)NOTNULLAUTO_INCREMENT,`name`char(20)NOTNULL,PRIMARYKEY(`id`));mysql>desctest;+-------+----------+------+-----+---------+----------------+|Field|Type|Null|Key|Default|Extra|+-------+----------+------+-----+---------+----------------+|id|int(4)|NO|PRI|NULL|auto_increment||name|char(20)|NO||NULL||+-------+----------+------+-----+---------+----------------+

● 插入值

insertintotest(id,name)values(1,'oldboy');insertintotest(id,name)values(1,'oldboy');

● 查询

mysql>select*fromtest;+----+--------+|id|name|+----+--------+|1|oldboy|+----+--------+1rowinset(0.00sec)

● 第二种方法：

id列可以不指定，不指定就自己增长

insertintotest(name)values('oldgirl');mysql>select*fromtest;+----+---------+|id|name|+----+---------+|1|oldboy||2|oldgirl|+----+---------+2rowsinset(0.00sec)

● 第三种方法，可以不指定列，后面按照循序插入

mysql>insertintotestvalues(3,'inca');QueryOK,1rowaffected(0.00sec)mysql>select*fromtest;+----+---------+|id|name|+----+---------+|1|oldboy||2|oldgirl||3|inca|+----+---------+3rowsinset(0.00sec)

● 批量插入：

mysql>insertintotestvalues(4,'zuma'),(5,'kaka');QueryOK,2rowsaffected(0.00sec)Records:2Duplicates:0Warnings:0mysql>select*fromtest;+----+---------+|id|name|+----+---------+|1|oldboy||2|oldgirl||3|inca||4|zuma||5|kaka|+----+---------+5rowsinset(0.00sec)

清空所有值

mysql>truncatetabletest;QueryOK,0rowsaffected(0.00sec)mysql>select*fromtest;Emptyset(0.00sec)

一条命令解决以上所有配置

mysql>insertintotestvalues(1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');QueryOK,5rowsaffected(0.01sec)Records:5Duplicates:0Warnings:0mysql>select*fromtest;+----+---------+|id|name|+----+---------+|1|oldboy||2|oldgirl||3|inca||4|zuma||5|kaka|+----+---------+5rowsinset(0.00sec)

查询数据

查询表的所有数据行

1.命令句法：
select<字段1,字段2，…>frin
<表名>where<表达式>

其中，select,from,where是不能随便改的，是关键字，支持大小写

2.列：查看表test中所有数据

a．进入指定库后查询
如果不进入库可以使用

mysql>select*fromoldboy.test;mysql>selectuser,host,passwordfrommysql.user;

+-----------+---------------------------+-------------------------------------------+|user|host|password|+-----------+---------------------------+-------------------------------------------+|root|localhost|*7495041D24E489A0096DCFA036B166446FDDD992||root|127.0.0.1|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||wordpress|192.168.1.%|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||cyh|10.1.1.%|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||bbs|172.16.1.1/255.255.255.0|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||blog|localhost|*7495041D24E489A0096DCFA036B166446FDDD992||oldboy|localhost|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||oldboy|192.168.1.%/255.255.255.0|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9||abc|localhost|*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9|+-----------+---------------------------+-------------------------------------------+

只查询前2行内容

mysql>select*fromtestlimit2;+----+---------+|id|name|+----+---------+|1|oldboy||2|oldgirl|+----+---------+2rowsinset(0.00sec)

从第二条开始查，查找2个

mysql>select*fromtestlimit1,2;+----+---------+|id|name|+----+---------+|2|oldgirl||3|inca|+----+---------+2rowsinset(0.00sec)

按照条件查询

mysql>select*fromtestwhereid=1;+----+--------+|id|name|+----+--------+|1|oldboy|+----+--------+1rowinset(0.00sec)

提示：mysql> select * from test where name='abcdocker';
<==查询字符串要加引号

mysql>select*fromtestwherename='abcdocker';+----+--------+|id|name|+----+--------+|1|abcdocker|+----+--------+1rowinset(0.00sec)

提示：查找字符串类型的条件的值要带单引号，数字值不带引号。

查询多个条件

mysql>select*fromtestwherename='oldgirl'andid=2;+----+---------+|id|name|+----+---------+|2|oldgirl|+----+---------+1rowinset(0.00sec)

范围查询

mysql>select*fromtestwhereid>2;+----+------+|id|name|+----+------+|3|inca||4|zuma||5|kaka|+----+------+3rowsinset(0.00sec)mysql>select*fromtestwhereid>2andid<5;+----+------+|id|name|+----+------+|3|inca||4|zuma|+----+------+2rowsinset(0.35sec)

或者的意思

mysql>select*fromtestwhereid>2orid<5;+----+---------+|id|name|+----+---------+|1|abcdocker||2|oldgirl||3|inca||4|zuma||5|kaka|+----+---------+5rowsinset(0.34sec)

排序

什么都不加相当于升序

mysql>select*fromtest;相当于mysql>select*fromtestorderbyidasc;写法倒序mysql>select*fromtestorderbyiddesc;倒序

例子：创建学生表

droptablestudent;createtablestudent(Snoint(10)NOTNULLCOMMENT'学号',Snamevarchar(16)NOTNULLCOMMENT'姓名',Ssexchar(2)NOTNULLCOMMENT'性别',Sagetinyint(2)NOTNULLdefault'0'COMMENT'学生年龄',Sdeptvarchar(16)defaultNULLCOMMENT'学生所在系别',PRIMARYKEY(Sno),keyindex_Sname(Sname))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=latin1;

插入内容

createtablecourse(Cnoint(10)NOTNULLCOMMENT'课程号',Cnamevarchar(64)NOTNULLCOMMENT'课程名',Ccredittinyint(2)NOTNULLCOMMENT'学分',PRIMARYKEY(Cno))ENGINE=InnoDBAUTO_INCREMENT=1DEFAULTCHARSET=latin1;

插入内容

CREATETABLE`SC`(SCidint(12)NOTNULLauto_incrementCOMMENT'主键',`Cno`int(10)NOTNULLCOMMENT'课程号',`Sno`int(10)NOTNULLCOMMENT'学号',`Grade`tinyint(2)NOTNULLCOMMENT'学生成绩',PRIMARYKEY(`SCid`))ENGINE=InnoDBDEFAULTCHARSET=latin1;

插入内容

INSERTINTOcoursevalues(1001,'Linux中高级运维',3);INSERTINTOcoursevalues(1002,'Linux高级架构师',5);INSERTINTOcoursevalues(1003,'MySQL高级Dba',4);INSERTINTOcoursevalues(1004,'Python运维开发',4);INSERTINTOcoursevalues(1005,'Javaweb开发',3);

插入内容

INSERTINTOSC(Sno,Cno,Grade)values(0001,1001,4);INSERTINTOSC(Sno,Cno,Grade)values(0001,1002,3);INSERTINTOSC(Sno,Cno,Grade)values(0001,1003,1);INSERTINTOSC(Sno,Cno,Grade)values(0001,1004,6);INSERTINTOSC(Sno,Cno,Grade)values(0002,1001,3);INSERTINTOSC(Sno,Cno,Grade)values(0002,1002,2);INSERTINTOSC(Sno,Cno,Grade)values(0002,1003,2);INSERTINTOSC(Sno,Cno,Grade)values(0002,1004,8);INSERTINTOSC(Sno,Cno,Grade)values(0003,1001,4);INSERTINTOSC(Sno,Cno,Grade)values(0003,1002,4);INSERTINTOSC(Sno,Cno,Grade)values(0003,1003,2);INSERTINTOSC(Sno,Cno,Grade)values(0003,1004,8);INSERTINTOSC(Sno,Cno,Grade)values(0004,1001,1);INSERTINTOSC(Sno,Cno,Grade)values(0004,1002,1);INSERTINTOSC(Sno,Cno,Grade)values(0004,1003,2);INSERTINTOSC(Sno,Cno,Grade)values(0004,1004,3);INSERTINTOSC(Sno,Cno,Grade)values(0005,1001,5);INSERTINTOSC(Sno,Cno,Grade)values(0005,1002,3);INSERTINTOSC(Sno,Cno,Grade)values(0005,1003,2);INSERTINTOSC(Sno,Cno,Grade)values(0005,1004,9);

检查，查看表格式或者表内容

mysql>descSC;orselect*fromSC;

优化

sql语句优化

explain查看是否含有建立索引的语句mysql>explainselect*fromtestwherename='oldboy';在一个语句前面加上explain相当于模拟查询

创建索引

mysql>createindexindex_nameontest(name);|name|char(20)|NO|MUL|NULL|mysql>explainselect*fromtestwherename='oldboy'\G***************************1.row***************************id:1select_type:SIMPLEtable:testtype:refpossible_keys:index_namekey:index_namekey_len:60ref:constrows:1查询行数，表示当前只查询了1行Extra:Usingwhere;Usingindex1rowinset(0.00sec)

原图：

mysql>explainselect*fromtestwherename='oldboy'\G***************************1.row***************************id:1select_type:SIMPLEtable:testtype:ALLpossible_keys:NULLkey:NULLkey_len:NULLref:NULLrows:5Extra:Usingwhere1rowinset(0.00sec)

帮助

mysql>helpexplain包含EXPLAIN的用法

使用explain命令优化SQL语句（select语句）的基本流程

■ 抓慢查询SQL语法方法

每隔2,秒输入mysql>SHOWFULLPROCESSLIST;如果出现2次说明是慢查询mysql>SHOWFULLPROCESSLIST;

■ 分析慢查询日志

配置参数记录慢查询语句log_query_time=2log_queries_not_using_indexeslog-slow-queries=/data/3306/slow.log

■ 对需要建索引的条件列建立索引
大表不能高峰期建立索引，300万条记录（如果访问已经慢了，可以直接创建）

■ 分析慢查询SQL的工具mysqlala(每天早上发邮件)
切割慢查询日志，去重分析后发给大家，如果并发太大可以按小时，去重。

1）mv然后flush进程2）cp复制，然后利用>清空。3）定时任务mv/data/3306/slow.log/opt/$(date+%F)_slow.logmysqladmin-uroot-poldboy-S/data/3306/mysql.sockflush-logs

★