MySQL-常用命令大全

登录MySQL方法

1.单实例MySQL登录的方法

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

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

设置变量

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

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

chmod 700 /data/3306/mysql
chmod 700 /server/scripts/bak.sh

删除命令行记录

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

多实例MySQL本地登录

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

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

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

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

mysql

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

■ 在命令行修改登录提示

mysql> prompt \u@abcdocker \r:\m:\s->
PROMPT set to '\u@abcdocker \r:\m:\s->'
root@abcdocker 05:03:09->
root@abcdocker 05:03:11->
root@abcdocker 05:03:12->

配置文件修改登录提示符

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

[mysql]
prompt=\\u@oldboy \\r:\\m:\\s->
MySQL help帮助
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> SHOW DATABASES LIKE '%MY%'; 模糊匹配
mysql> show databases like 'd3306';
mysql> show databases like '%3306';
mysql> show databases like '%33%';
更多参数help show

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

killall mysqld
pkill mysqld
killall -9 mysqld
mysqld: no process killed
kill -9 pid

■ 最好使用优雅停止

mysqladmin -uroot -poldboy123 shutdown

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

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

企业实战题7：

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

MySQL数据库安全策略介绍

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

truncalt table test和delete from test；区别

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

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

mysqladmin -u root password ‘oldboy’ #没有密码的情况下
mysqladmin -uroot -p oldboy password oldboy123 -S /data/3306/mysql.sock

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

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

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

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

mysql> update mysql.user set password=oldboy456 where user='root' and host='localhost';
这样设置是不可以登录的

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

mysql> update mysql.user set password=password('oldboy456') where user='root' and host='localhost';

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

mysql> select user,host,password from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | password |
+------+-----------+-------------------------------------------+
| root | localhost | *7DB922C59F217871B8165D72BEC8ED731A0EFFA1 |
| root | db01 | |
| root | 127.0.0.1 | |
| root | ::1 | |
| | localhost | |
| | db01 | |
+------+-----------+-------------------------------------------+
6 rows in set (0.00 sec)
Rows matched: 1 Changed: 1 Warnings:
0需要查看是否成功，出现Changed代表成功

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

mysql> flush privileges;

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

set password=password('oldboy123');

MySQL密码丢失如何找回？

■ 单实例

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

■ 多实例

/data/3306/mysql stop 无法停止
killall mysqld
mysqld_safe --defaults-file=/data/3306/my.cnf --skip-grant-tables --user=mysql &
update mysql.user set password=password('oldboy456') where user='root' and host='localhost';
flush privileges;
mysql 登录
mysqladmin -uroot -poldboy shutdown
/etc/init.d/mysqld start

SQL结构化查询语言

什么是SQL？

SQL，英文全称Structured Query Language，中文意思是结构化查询语言，它是一种对关系型数据库中的数据进行定义和操作的语言方法，是大多数关系数据库管理系统所支持的工业标准语言。

结构化查询语言SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统，同时用sql作为MySQL逻辑备份文件的扩展名。结构化查询语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。他不要求用户指定对数据存放方法，也不需要用户了解具体的数据存放方式。

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

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

命令讲解

■ 创建数据库

mysql> create database abcdocker;

■ 查看创建库的语句

show create database oldboy;
+----------+-----------------------------------------------------------------+
| Database | Create Database|
+----------+-----------------------------------------------------------------+
| abcdocker | CREATE DATABASE `oldboy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

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

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

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

mysql> create database abcdocker; #默认数据库配置，相当于创建拉丁字符集数据库
mysql> create database abcdocker_gbk character set gbk collate gbk_chinese_ci;创建gbk格式文件
mysql> help create database
mysql> show character set; #查看字符集
mysql> show create database oldboy_gbk;
+------------+--------------------------------------------------------------------+
| Database| Create Database|
+------------+--------------------------------------------------------------------+
| abcdocker_gbk | CREATE DATABASE `oldboy_gbk` /*!40100 DEFAULT CHARACTER SET gbk */ |
+------------+--------------------------------------------------------------------+
1 row in set (0.00 sec)

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

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

■ 查看数据库

show databases;
select database(); #相当于pwd
select user(); #查看当前用户
select version(); #查看当前版本
()可以说是函数

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

use oldboy #相当于cd
show tables #查看表
or
show tables from wordpress #查看wordpress库下的表文件

■ 删除用户

drop user 'root'@'::1';
如果drop删除不了（一般reshuffle符号或大写）可以用下面方式删除（以root，用户为例）
delete from mysql.user where user=’root’ and host=’oldboy’;
flush privileges;

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

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

CREATE USER 'jeffrey'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL ON db1.* TO 'jeffrey'@'localhost';
GRANT SELECT ON db2.invoice TO 'jeffrey'@'localhost';
GRANT USAGE ON *.* TO 'jeffrey'@'localhost' WITH MAX_QUERIES_PER_HOUR 90;

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

grant命令简单语法如下：
grant all privileges on dbname.* to username@localhost identified by ‘passwd’;

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

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

grant select,insert,update,create,drop on blog.* to blog@localhost identified by ‘123’

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

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

■ 普通环境：

本机：lnmp，lamp环境数据库授权
grant all privileges ON blog.* to blog@localhost identified by ‘123456’
应用服务器和数据库服务器不在一个主机上授权；
grant all privileges ON blog.* to blog@10.0.0.% identified by ‘123’
严格的授权：重视安全，忽略了方便；
grant select,insert,update,delete ON blog.* to blog@10.0.0.% identified by ‘123’
生产环境从库（只读）用户的授权；
grant select ON blog.* to blog@10.0.0.% identified by ‘123’
查看授权用户oldboy的具体的授权权限
show grants for ‘oldboy’@’localhost’;

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

grant all on test.* to abcdocker@localhost identified by 'abcdocker123';

■ 第一种：授权用户

grant all on test.* to oldboy@127.0.0.% identified by ‘oldboy123’
show grants for oldboy@’127.0.0.%’; 查看授权用户
+-------------------------------------------------------------------------------------------------------------+
| Grants for root@127.0.0.1|
+-------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'127.0.0.1' |
+-------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

■ 第二种：授权方法

create user bbs@'172.16.1.1/255.255.255.0' identified by '123456'；
先授权可以登录的
mysql> show grants for bbs@'172.16.1.1/255.255.255.0';
mysql> grant select on wordpress.* to bbs@'172.16.1.1/255.255.255.0';

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

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

grant all on *.* to‘test@10.0.0.%’identified by ‘test123’;

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

grant all on *.* to test@’10.0.0.0/255.255.255.0’ identified by ‘test123’;

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

create user test@’10.0.0.%’ identified by ‘test123’;
再对用户授权指定权限和管理库表
grant all on *.* to test@10.0.0.0/255.255.255.0

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

flush privileges;

数据库远程登录

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

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

grant all privileges on *.* to root@'127.0.0.1' identified by 'oldboy123' with grant option;
只需要在最后输入with grant option　

回收用户权限

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

显示库的信息

mysql> show create database oldboy\G

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

创建表

建表的基本命令语法：

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

建表语句

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

create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL
);　

查看创建表的语句

mysql> show create table student;
create table student( #create table表示创建表的固定关键字,student为表名
id int(4) not null, #学号列,数字类型，长度为4，不能为空值
name char(20) not null, #名字列，定长字符类型，长度20，不能为空
age tinyint(2) NOT NULL default '0', #年龄列，很小的数字类型，长度为2，不能为空，默认为0值
dept varchar(16) default NULL #系别列，变长字符类型，长度16，默认为空。
ENGINE=lnnoDB DEFAULT CHARSET=latinl #引擎和字符集，引擎默认为InnoDB，字符集，继承库的latinl
);

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

 

create table student(
id int(4) not null,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL
)ENGINE=InnoDB DEFAULT CHARSET=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) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',

user表用的是CHAR

生产场景案例：

某sns产品生产正式建表语句

use sns;
set names gbk;
CREATE TABLE `subject_comment_manager` (
`subject_comment_manager_id` bigint(12) NOT NULL auto_increment COMMENT '主键',
`subject_type` tinyint(2) NOT NULL COMMENT '素材类型',
`subject_primary_key` varchar(255) NOT NULL COMMENT '素材的主键',
`subject_title` varchar(255) NOT NULL COMMENT '素材的名称',
`edit_user_nick` varchar(64) default NULL COMMENT '修改人',
`edit_user_time` timestamp NULL default NULL COMMENT '修改时间',
`edit_comment` varchar(255) default NULL COMMENT '修改的理由',
`state` tinyint(1) NOT NULL default '1' COMMENT '0代表关闭，1代表正常',
PRIMARY KEY (`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=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;　

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

索引就象书的目录一样，如果在字段上建立了索引，那么以索引为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。

创建主键索引
查询数据库，按主键查询是最快的，每个表只有一个主键列，但是可以用多个普通索引列。主键列要求列的所有内容必须唯一，而普通索引不要求内容必须唯一
主键就类似我们在学校学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。
首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引。

为表的字段创建索引*****
数据库的索引就象书的目录一样，如果在字段上建立了索引，那么多以索引列为查询条件时可以加快查询数据的速度，这是mysql优化的重要内容之一。

创建主键索引      
查询数据库，按主键查询是最快的，每个表只能有一个主键列，但是可以有多个普通索引列。主键列的所有内容必须唯一，而普通索引列不要求内容必须唯一。
主键就类似我们在学习学习时的学号一样，班级内是唯一的，整个表的每一条记录的主键值在表内都是唯一的，用来唯一标识一条记录。
首先，无论建立主键索引还是普通索引，都要在表的对应列上创建，可以对单列创建索引，也可以对多列创建索引。

建立主键索引的方法：

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

drop table student;
create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL,
primary key(id),
KEY index_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列为自增主键值

alter table student change id id int primary key auto_increment;

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

mysql> create table student(
id int(4) not null AUTO_INCREMENT,
name char(20) not null,
age tinyint(2) NOT NULL default '0',
dept varchar(16) default NULL,
primary key(id),
KEY index_name(name)
);

提示：

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

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

mysql> show index from student;

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

mysql> show index from student\G
*************************** 1. row *************************** 主建 一个表只能有一个
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE 表类型
Comment:
Index_comment:
*************************** 2. row *************************** 索引 一个表可以有多个
Table: student
Non_unique: 1
Key_name: index_name 名字
Seq_in_index: 1
Column_name: name 列
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE 类型
Comment:
Index_comment:
2 rows in set (0.00 sec)

查看表结构

desc student；

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

alter table student drop index index_name;

删除索引

alter table student drop index index_name;

操作实践：

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

create index index_name on student(name);　

查看索引

mysql> show index from student;

创建语句

create index index_age on student(name(8));

　　

show index from student\G
查看 表 位置表明 显示
*************************** 3. row ***************************
　　Table: student
　　Non_unique: 1
Key_name: index_age
　　　 Seq_in_index: 1
　　Column_name: age
　　Collation: A
　　Cardinality: 0
Sub_part: 8
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)

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

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

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

mysql> create index ind_name_dept on student(name,dept);　

联合索引作用，查询更快

mysql> show index from student\G
*************************** 1. row ***************************
Table: student
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_age
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 4. row ***************************
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 5. row ***************************
Table: student
Non_unique: 1
Key_name: ind_name_dept
Seq_in_index: 2
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
5 rows in set (0.00 sec)

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

create index ind_name_dept on student(name(8),dept(10));
name 前8个字符，dept 前10个字符
*************************** 2. row ***************************
Table: student
Non_unique: 1
Key_name: index_dept
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
Sub_part: 8
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 3. row ***************************
Table: student
Non_unique: 1
Key_name: index_dept
Seq_in_index: 2
Column_name: dept
Collation: A
Cardinality: 0
Sub_part: 10
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
3 rows in set (0.00 sec)

　　

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

什么时候创建联合索引？

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

创建唯一索引（非主键）

create unique index uni_ind_name on student(name);

索引小结：

创建主键索引
alter table student chage id id int primary key auto_increment;
删除主键索引（主键列不能自增）
alter table student drop primary key;
创建普通索引
alter table student add index index_dept(dept);
根据的前n个字符创建索引
create index index_dept on student(dept(8));
根据多个列创建联合索引
create index index_name_dept on student(name,dept);
创建唯一索引
create unique index uni_ind_name on student(name);
删除普通索引与唯一索引
alter table student drop index index_dept;
drop index index_dept on student;

索引列的创建及生效条件

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

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

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

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

查看唯一值数量

select count(distinct user) from mysql.user;

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

mysql> select count(distinct user) from mysql.user;
+----------------------+
| count(distinct user) |
+----------------------+
| 7 |
+----------------------+
1 row in set (0.07 sec)

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

mysql> select user,host from mysql.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 |
+-----------+---------------------------+
9 rows in set (0.00 sec)

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

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

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

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

 

往表中插入数据

● 命令语法

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

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

CREATE TABLE `test` (
`id` int(4) NOT NULL AUTO_INCREMENT,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ;
mysql> desc test;
+-------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | char(20) | NO | | NULL| |
+-------+----------+------+-----+---------+----------------+

● 插入值

insert into test(id,name) values(1,'oldboy');
insert into test(id,name) values(1,'oldboy');

● 查询

mysql> select * from test;
+----+--------+
| id | name |
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.00 sec)

● 第二种方法：

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

insert into test(name) values('oldgirl');
mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)

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

mysql> insert into test values(3,'inca');
Query OK, 1 row affected (0.00 sec)
mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca|
+----+---------+
3 rows in set (0.00 sec)

● 批量插入：

mysql> insert into test values(4,'zuma'),(5,'kaka');
Query OK, 2 rows affected (0.00 sec)
Records: 2Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma|
| 5 | kaka|
+----+---------+
5 rows in set (0.00 sec)

清空所有值

mysql> truncate table test;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test;
Empty set (0.00 sec)

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

mysql> insert into test values (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');
Query OK, 5 rows affected (0.01 sec)
Records: 5Duplicates: 0 Warnings: 0
mysql> select * from test;
+----+---------+
| id | name|
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)

查询数据

查询表的所有数据行

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

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

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

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

mysql> select * from oldboy.test;
mysql> select user,host,password from mysql.user;

只查询前2行内容

mysql> select * from test limit 2;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
+----+---------+
2 rows in set (0.00 sec)

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

mysql> select * from test limit 1,2;
+----+---------+
| id | name|
+----+---------+
| 2 | oldgirl |
| 3 | inca |
+----+---------+
2 rows in set (0.00 sec)

按照条件查询

mysql> select * from test where id=1;
+----+--------+
| id | name|
+----+--------+
| 1 | oldboy |
+----+--------+
1 row in set (0.00 sec)

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

mysql> select * from test where name='abcdocker';
+----+--------+
| id | name |
+----+--------+
| 1 | abcdocker |
+----+--------+
1 row in set (0.00 sec)

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

查询多个条件

mysql> select * from test where name='oldgirl' and id=2;
+----+---------+
| id | name |
+----+---------+
| 2 | oldgirl |
+----+---------+
1 row in set (0.00 sec)

范围查询

mysql> select * from test where id>2;
+----+------+
| id | name |
+----+------+
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+------+
3 rows in set (0.00 sec)
mysql> select * from test where id>2 and id<5;
+----+------+
| id | name |
+----+------+
| 3 | inca |
| 4 | zuma |
+----+------+
2 rows in set (0.35 sec)

　　

或者的意思

mysql> select * from test where id>2 or id<5;
+----+---------+
| id | name |
+----+---------+
| 1 | abcdocker |
| 2 | oldgirl |
| 3 | inca |
| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.34 sec)

排序

什么都不加相当于升序

mysql> select * from test;
相当于
mysql> select * from test order by id asc; 写法
倒序
mysql> select * from test order by id desc; 倒序

例子：创建学生表

drop table student;
create table student(
Sno int(10) NOT NULL COMMENT '学号',
Sname varchar(16) NOT NULL COMMENT '姓名',
Ssex char(2) NOT NULL COMMENT '性别',
Sage tinyint(2) NOT NULL default '0' COMMENT '学生年龄',
Sdept varchar(16) default NULL COMMENT '学生所在系别',
PRIMARY KEY (Sno) ,
key index_Sname (Sname)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

插入内容

create table course(
Cno int(10) NOT NULL COMMENT '课程号',
Cname varchar(64) NOT NULL COMMENT '课程名',
Ccredit tinyint(2) NOT NULL COMMENT '学分',
PRIMARY KEY (Cno)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

插入内容

CREATE TABLE `SC` (
SCid int(12) NOT NULL auto_increment COMMENT '主键',
`Cno` int(10) NOT NULL COMMENT '课程号',
`Sno` int(10) NOT NULL COMMENT '学号',
`Grade` tinyint(2) NOT NULL COMMENT '学生成绩',
PRIMARY KEY (`SCid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入内容

INSERT INTO course values(1001,'Linux中高级运维',3);
INSERT INTO course values(1002,'Linux高级架构师',5);
INSERT INTO course values(1003,'MySQL高级Dba',4);
INSERT INTO course values(1004,'Python运维开发',4);
INSERT INTO course values(1005,'Java web开发',3);

插入内容

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

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

mysql>desc SC; or select * from SC;

优化

sql语句优化

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

 创建索引

mysql> create index index_name on test(name);
| name| char(20) | NO | MUL | NULL |
mysql> explain select * from test where name='oldboy'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ref
possible_keys: index_name
key: index_name
key_len: 60
ref: const
rows: 1 查询行数，表示当前只查询了1行
Extra: Using where; Using index
1 row in set (0.00 sec)

原图：

mysql> explain select * from test where name='oldboy'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: test
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5
Extra: Using where
1 row in set (0.00 sec)

帮助

mysql>
help explain 包含EXPLAIN的用法

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

■ 抓慢查询SQL语法方法

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

■ 分析慢查询日志

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

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

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

 

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