一、mysql基本管理
1.启动mysql
/etc/init.d/mysqld是一个shell启动脚本，启动后最终会调用mysqld_safe脚本，最后调用mysqld服务启动mysql,如下:
/etc/init.d/mysqld start 或/usr/local/mysql/bin/mysqld_safe &
root 26221 1 0 08:55 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid
mysql 27071 26221 0 08:55 ? 00:02:26 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/mysql-error.log --open-files-limit=65535 --pid-file=/data/mysql/mysql.pid --socket=/tmp/mysql.sock --port=3306
2.关闭mysql:
mysqladmin -uroot shutdown -p123 平滑关闭服务
/etc/init.d/mysqld stop 实际是调用上面的命令
Kill –USR2 `cat path/pid` 直接杀死pid来关闭服务，不推荐
3.连接数据库
mysql -uroot -p123 -D test -S /var/lib/mysql/mysql.sock -h 127.0.0.1 -P 3306
选项：
-u 指定用户
-p 指定用户密码
-h 指定服务器地址
-P 指定端口号
-S 指定SOCK路径
-D 指定数据库名，直接连接到数据库
-C 数据传输压缩
-e 非交互式执行SQL语句
4.数据库交互式命令
\? 帮助信息
\c 清除当前输入语句
\r 重新连接到服务器，可选数据库和主机
\d 设置语句分隔符。
\e 使用$ EDITOR编辑命令。
\G 发送命令到mysql server，垂直显示结果。
\g 发送命令到mysql服务器。
\h 显示此帮助。
\n 禁用寻呼机，打印到stdout。
\t 不要写入outfile。
\P 设置PAGER [to_pager]。通过PAGER打印查询结果。
\p 打印当前命令。
\R 更改您的mysql提示符。
\q 退出mysql。
\# 重建完成哈希。
\. 执行SQL脚本文件。获取文件名作为参数。
\s 从服务器获取状态信息。
\！ 执行系统shell命令。
\T 设置outfile [to_outfile]。将所有内容附加到给定的文件中。
\u 使用另一个数据库。将数据库名称作为参数。
\C 切换到另一个字符集。可能需要处理具有多字节字符集的binlog。
\W 每个声明后显示警告。
\w 每个语句后都不要显示警告。
\x 清除会话上下文。
二、mysql数据库变量管理
mysql中变量可分为系统变量与状态变量，系统变量配置了mysql的运行环境属性，状态变量显示了msyql运行过程中的状态信息，而系统变量从作用域划分，又可以分为全局变量和会话变量。
系统变量：
全局级别的变量：global variables，作用域为整个msyql服务器
会话级别的变量：session variables,作用域为当前会话。
状态变量：
状态变量也分为全局级别与会话级别，但是用户无法设置状态变量，只能查看。
1.全局系统变量查询
全局系统变量查询：
show global variables \G;
查看字符集相关的全部设置：
show global variables like 'character%';
查看日志配置是否开启或关闭：
show global variables where variable_name like '%log%' and value='off';
2.会话系统变量查询
show session variables;
当前会话字符集查询
show session variables like 'character%';
注意：有些变量只存在于全局级别中，而有些变量只存在于会话级别中，如果不指明global或session，则表示查看会话级别的变量值。
3.使用select查看单个全局系统变量或会话系统变量
select @@global.character_set_server; #查看系统字符集变量
select @@session.wait_timeout; #查看当前会话超时
如不指定global或session，@@var_name表示优先从会话级别获取变量值。
4.改变变量的值
mysql> select @@session.wait_timeout;
+------------------------+
| @@session.wait_timeout |
+------------------------+
| 28800 |
+------------------------+
1 row in set (0.00 sec)
mysql> set @@session.wait_timeout=14400;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@session.wait_timeout;
+------------------------+
| @@session.wait_timeout |
+------------------------+
| 14400 |
+------------------------+
1 row in set (0.00 sec)
set global var_name=value;
set @@global.var_name=value;
set session var_name=value;
set @@session.var_name=value;
不指定级别表示默认设置会话级别的变量
状态变量对应用户来说是只读的，所以只改变系统变量的值。
在修改系统变量的值时并非永久生效的，重启后将失效。
运行时修改global级别的变量，对修改之前建立的会话没有影响，仅对修改后新建的会话有效。
运行时修改session级别的变量，仅对当前会话有效，且立即生效。
如果要使设定永久生效，需要设置配置文件。
三、用户帐户管理
1.查看当前库中的用户
select user,host,authentication_string from mysql.user;
+------------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------------+-----------+-------------------------------------------+
| root | localhost | *AC241830FFDDC8943AB31CBD47D758E79F7953EA |
注意：从5.7以后密码字段改为authentication_string
host：表示用户能够通过哪些客户端主机IP登录到当前服务器上的mysql服务，host可以为主机名，也可以为IP地址，但是mysql认为主机名和Ip地址属于不同的主机；
可以使用通配符：
_ 表示任意单个字符
% 表示任意长度的任意字符
2.创建用户
1)使用create user命令创建用户
mysql> create user 'linux'@'localhost';
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'linuxse'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
2)使用grant授权用户时自动创建用户
grant all on *.* to 'zzz'@'localhost' identified d by '123';
3.删除用户
注意使用delete删除用户时，不会删除用户授权，所以下次再创建相同的用户时就会报错，可以使用drop删除用户授权，重新创建。
mysql> delete from mysql.user where user='zzz' and host='localhost';
Query OK, 1 row affected (0.00 sec)
mysql> create user 'zzz'@'localhost';
ERROR 1396 (HY000): Operation CREATE USER failed for 'zzz'@'localhost'
mysql> drop user zzz@localhost;
Query OK, 0 rows affected (0.01 sec)
mysql> create user 'zzz'@'localhost';
Query OK, 0 rows affected (0.01 sec)
4.重命名用户
rename user OldName to NewName; 指定旧和新用户名
mysql> select user,host from mysql.user;
+------------+-----------+
| user | host |
+------------+-----------+
| linuxse | % |
| mysql.sys | localhost |
| root | localhost |
+------------+-----------+
8 rows in set (0.00 sec)
mysql> rename user linuxse to aaaa;
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host from mysql.user;
+------------+-----------+
| user | host |
+------------+-----------+
| aaaa | % |
| mysql.sys | localhost |
| root | localhost |
+------------+-----------+
8 rows in set (0.00 sec)
5.修改用户密码
1)系统命令中修改密码
mysqladmin -uzhang -p123 password 123
如果没有密码就不用指定密码
mysqladmin -uzhang password 123
2)登录到mysql修改密码
set password for 'zhang'@'%'=password('123');
flush privileges;
3)修改表字段修改密码，注意5.7以后密码字段为authentication_string
mysql> update mysql.user set authentication_string=password('456') where user='zhang' and host='%';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
4)忘记密码修改，重置
停止数据库
/etc/init.d/mysqld stop
绕过用户验证启动mysql
/usr/local/mysql/bin/mysqld_safe --skip-grant-tables &
登录mysql:
mysql -uroot
修改用户密码：
mysql> update mysql.user set authentication_string=password('456') where user='root' and host='localhost';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 1
刷新后退出：
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql>\q
重新正常启动
mysqladmin-uroot -p123 shutdown
ps-ef|grep -v grep|grep mysql
/etc/init.d/mysqldstart
四、mysql授权管理
Create: 建立新的数据库或数据表。
Delete: 删除表的记录。
Drop: 删除数据表或数据库。
INDEX: 建立或删除索引。
Insert: 增加表的记录。
Select: 显示/搜索表的记录。
Update: 修改表中已存在的记录。
全局管理MySQL用户权限：
file: 在MySQL服务器上读写文件。
PROCESS: 显示或杀死属于其它用户的服务线程。
RELOAD: 重载访问控制表，刷新日志等。
SHUTDOWN: 关闭MySQL服务。
特别的权限：
ALL: 允许做任何事(和root一样)。
USAGE: 只允许登录--其它什么也不允许做。
1、用户权限
授权语法：
grant all [privileges] on db.table to 'username'@'host' identified by 'password'
授权zhang用户对所有库所有表所有权限，并设置密码，允许在任何地方登录
mysql> grant all privileges on *.* to 'zhang'@'%' identified by '123.com';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
授权zzz用户对zabbix库中的所有表有insert,delete,update,select权限：
mysql> grant insert,delete,update,select on zabbix.* to zzz@'localhost';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
授权用户远程登录时，必须使用SSL建立会话：
mysql> grant usage on *.* to 'xin'@'%' require ssl;
Query OK, 0 rows affected, 1 warning (0.01 sec)
撤销强制使用SSL建立会话的限制：
mysql> grant usage on *.* to 'xin'@'%' require none;
使用with grant option使授权用户也拥有授予其他用户同样的权限：
授权用户xin有select权限，同时它可以授权别的用户同样的权限。
grant select on *.* to 'xin'@'%' with grant optiontion;
其他限制：
MAX_QUERIES_PER_HOUR：限制用户每个小时执行的查询语句次数
MAX_UPDATES_PER_HOUR：限制用户每小时执行的更新语句次数
MAX_CONNECTIONS_PER_HOUR：限制用户每小时连接数据库的次数
MAX_USER_CONNECTIONS:限制用户使用当前帐号同时连接服务器的连接数
grant select on *.* to 'xin'@'%' with max_queries_per_hour 20;
grant select on *.* to 'xin'@'%' with max_updates_per_hour 10;
grant select on *.* to 'xin'@'%' with max_connections_per_hour 15;
grant select on *.* to 'xin'@'%' with max_user_connections 2;
上面的数据改为0表示不限制。
2.查看授权
show grants for xin@'%';
从数据库中查看用户授权：
语法：select * from mysql.db where Db="数据库名"
select * from mysql.db where Db='mysql'\G
3.删除授权
语法：revoke 权限 on 数据库.表 from 用户@host;
删除用户创建视图权限：
revoke create view on *.* from zhang@'%';
五、库管理语句
1.创建数据库
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_specification] ...
create_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
在其他关系型数据库中，database与schema是有区别的，但在mysql 5.0.2以后，我们创建的数据库时可以不加区分的使用database和schema
if not exists表示在对应的数据库不存在的时候才会创建，它是可选的。
create_specitification表示我们可以在创建数据库时指定对应的数据库规范：
可以指定数据库的字符集，使用character set对应的字符集名称，可以指定数据库字符集，如果使用了default关键字，那么这个数据库中创建的所有表默认都继承这个数据库的字符集，可以使用"show character set"命令查看所有可用的字符集，同时我们还可以指定创建数据库时指定数据库的排序规则，排序规则是针对字符集的，每个字符集都有自己默认的排序规则，使用"show collation"命令可以查看所有可用的排序方式，以及排序对应的字符集，default为可选指令，与指定字符集作用相同。
创建testdb数据库：
mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)
如果testdb数据库不存在，则创建：
mysql> create database if not exists testdb;
Query OK, 1 row affected, 1 warning (0.00 sec)
如果testdb数据库不存在，则创建，并指定其默认字符集为utf8：
mysql> create database if not exists testdb default character set utf8;
Query OK, 1 row affected, 1 warning (0.01 sec)
创建testdb数据库，指定字符集为utf8并指定排序方式：
create database if not exists testdb default character set utf8 collate utf8_general_ci;
2.查询数据库
查看所有存在的数据库：
show databases;
查看数据库语句结构：
show create database testdb;
查看可用的字符集：
show character set;
查看字符集排序方式：
show collation;
查看当前数据库与当前连接的概要信息：
root@mysql 11:50:50>use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
遇到上面的提示信息，表示mysql要预读数据库信息，如果数据过大会导致连接变慢，可以在连接数据时指定-A连接关闭此功能，也可以在配置文件my.cnf的客户端中添加字段：no-auto-rehash重新加载，关闭预读功能。
Database changed
root@mysql 11:56:20>status
--------------
mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
Connection id: 1315 #当前连接的ID号
Current database: mysql #当前选中使用的数据库
Current user: root@localhost #当前连接的登录用户
SSL: Not in use #是否使用了ssl
Current pager: stdout #
Using outfile: ''
Using delimiter: ; #当前会话行终结符
Server version: 5.7.16-log Source distribution #版本号
Protocol version: 10 #协议版本
Connection: Localhost via UNIX socket #使用的连接类型，通过本机的套接字文件进行连接
Server characterset: utf8 #服务器使用的字符集
Db characterset: utf8 #数据库使用的字符集
Client characterset: utf8 #客户端使用的字符集
Conn. characterset: utf8 #当前连接使用的字符集
UNIX socket: /tmp/mysql.sock #套接字文件路径
Uptime: 23 hours 26 min 5 sec #数据库的启动时长
Threads: 17 Questions: 54562 Slow queries: 0 Opens: 1920 Flush tables: 1 Open tables: 99 Queries per second avg: 0.646
--------------
3.修改数据库
ALTER {DATABASE | SCHEMA} [db_name]
alter_specification ...
ALTER {DATABASE | SCHEMA} db_name
UPGRADE DATA DIRECTORY NAME
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name
| [DEFAULT] COLLATE [=] collation_name
查看字符集：
show variables like 'character%';
修改数据库字符集：
alter database testdb character set gbk;
alter database testdb default character set gbk;
4.删除数据库
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
删除testdb数据库：
drop database if exists testdb;
六、表管理语句
1.查看表
use mysql
show tables;
查看表的具体属性信息：
show table status\G
指定查看某张表的属性：
show table status like 'user'\G
查看表结构语句：
desc table_name
desc user\G
查看表的创建语句：
show create table user\G
2.创建表
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
auto_increment设置字段为自增长，此字段必须被定义为key或者索引。默认只设置字段主键为自增
primary key(col1[,col2,...])用于定义主键，一个表中只能有一个主键，一个主键可以包含多个字段。
unique key(col1[col2,...])用于定义唯一键，一个表中可以有多个唯一键。
foreign key用于定义外键
chech(expr)用于定义检查性约束
index ind_name(id)|key ind_name(id)可以使用两种方法创建索引
fulltext|spatial如果数据库存储引擎为myisam可以创建全文索引和空间索引，innodb引擎不支持全文索引和空间索引。
实例：
创建表：
创建数据库指定字符集和排序方式：
root@mysql 01:20:53>create database test default character set utf8 collate utf8_general_ci;
Query OK, 1 row affected (0.01 sec)
root@mysql 01:22:47>use test;
创建test表指定字段id和name的类型分别为int和varchare
root@mysql 01:23:12>create table test1(
-> id int(11),
-> name varchar(58)
-> );
Query OK, 0 rows affected (0.02 sec)
在当前字段设置主键，并设置字段不能为空，添加注释字段：
root@mysql 01:31:20>create table test2(
-> id int(11) key,
-> name varchar(58) not null comment 'student name'
-> );
Query OK, 0 rows affected (0.02 sec)
在表级别定义主键：
root@mysql 01:32:28>create table test3(
-> id int(11),
-> name varchar(34) not null comment 'sutdent name',
-> primary key(id)
-> );
Query OK, 0 rows affected (0.01 sec)
创建联合主键：
root@mysql 01:34:28>create table test4(id int(10),name varchar(34) not null,primary key(id,name));
Query OK, 0 rows affected (0.02 sec)
创建索引，可以使用index ind_name()和key ind_name()两种方法创建索引：
root@mysql 01:36:24>create table test5(id int(11) auto_increment,name varchar(58) not null,primary key(id),index ind_name(name));
Query OK, 0 rows affected (0.03 sec)
root@mysql 01:41:39>create table test6(id int(11) auto_increment,name varchar(58) not null,primary key(id),key ind_name(name));
Query OK, 0 rows affected (0.02 sec)
root@mysql 01:43:44>create table test7(id int(11) auto_increment,name varchar(58) not null,primary key(id),index(name));
Query OK, 0 rows affected (0.03 sec)
复制表数据：
还可以根据查询的数据创建一个新表，将数据填充到创建的表中，需要注意字段中的约束无法复制过去：
root@mysql 01:49:31>desc test7;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(58) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
root@mysql 01:49:38>select * from test7;
+----+-------+
| id | name |
+----+-------+
| 4 | haha |
| 3 | hehe |
| 1 | zhang |
| 2 | zhang |
+----+-------+
4 rows in set (0.00 sec)
root@mysql 01:49:56>create table test8 select id,name from test7;
Query OK, 4 rows affected (0.03 sec)
Records: 4 Duplicates: 0 Warnings: 0
root@mysql 01:51:26>desc test8;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
| name | varchar(58) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
root@mysql 01:51:39>select * from test8;
+----+-------+
| id | name |
+----+-------+
| 4 | haha |
| 3 | hehe |
| 1 | zhang |
| 2 | zhang |
+----+-------+
4 rows in set (0.00 sec)
复制表结构：
使用like来将test5的表结构包括字段约束和索引都复制到创建的新表中test9：
root@mysql 01:57:19>desc test5;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(58) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
root@mysql 01:57:26>create table test9 like test5;
Query OK, 0 rows affected (0.03 sec)
root@mysql 01:57:53>desc test9;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(58) | NO | MUL | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
使用engine选项还指定数据引擎，还可以指定自增长和字符集等：
root@mysql 01:58:04>create table test10(id int(11) primary key auto_increment,name varchar(34) not null,index(name))engine=InnoDB auto_increment=2 default charset=utf8;
Query OK, 0 rows affected (0.03 sec)
其他信息可查看帮助信息：
root@mysql 02:06:41>help create table;
3.删除表
drop table table_name;
drop table if exists table_name1,table_name2;
4.修改表
重命名表名：
alter table test1 rename sa test2;
添加字段：
alter table test add column age int(11);
修改表的字段，可以省略column：
alter table test add age int(11);
为表添加字段，同时为添加字段设定约束：
alter table test add age int not null default 0;
alter table test column age int not null default 0;
为表添加字段，同时指定添加字段在表中的位置，下面为添加字段为第一个字段：
alter table test add id int first;
指定添加字段的位置为name字段的后面：
alter table test add column age int after name;
删除字段
alter table test drop name;
重命名字段，修改字段名称
alter table test change name name1 varchar(48);
修改字段类型
alter table test change age age char(10);
alter table test modify age int;
本文转自 80后小菜鸟 51CTO博客，原文链接:http://blog.51cto.com/zhangxinqi/1927980
