参考朱双印的mysql 专题 http://www.zsythink.net/archives/category/%e5%ad%98%e5%82%a8/mysql/page/5/
本博客仅为笔者个人参考学习使用，推荐通过学习朱双印的mysql专题来全面学习
本篇包含的内容
- 启动mysql或者停止 mysql
- 服务器登录mysql
- 变量相关
- 用户管理
- 密码管理
- 授权管理
- 库管理
- 表管理
（一）启动mysql或停止mysql的常用语句如下：如果 MySQL 是用 RPM 包安装的， 则启动和关闭 MySQL 服务过程如下。
1.启动服务
service mysqld start
2.重启服务
service mysqld restart
3.关闭服务：
service mysqld stop
如果不是通过rpm包的方式安装的mysql，启动和关闭 MySQL 服务命令如下
1.启动服务：
/usr/bin/mysqld_safe &
2.关闭服务
以下命令是root用户还不存在密码的情况下
mysqladmin -uroot shutdown
如果root用户已经设置了密码，需要加上-p参数
mysqladmin -uroot shutdown -p
（二）服务器登陆mysql
使用root用户连接数据库
mysql -u root -p123123
指定登陆数据库的哪个 database -D 接数据库名称
mysql -u root -D test -p123123
在本地连接到mysql时，可以指定通过哪个套接字文件连接到数据库，通过-S指定套接字位置。
mysql -u root -p -S /var/lib/mysql/mysql.sock
连接到远程的哪一台主机的mysql上，也可以指定端口，-h指定mysql主机，-P大写，指定mysql服务对应的端口，连接的前提是已经授权当前客户端的IP地址能够连接到数据库。
mysql -u root -p -h 192.168.1.103 -P 3306
连接数据库的同时执行对应命令，并且返回命令对应的结果，并不会进入mysql提示符。
示例如下，表示连接数据库的同时，执行-e选项后面跟随的sql语句，并且返回语句执行的结果
mysql -u root -p123123 -e 'use mysql; select user,host,password from user;' mysql -uroot -p123123 -e 'create database if not exists testdb; show databases;'
连接数据库的常用选项
--host=host_name, -h host_name：服务端地址；
--user=user_name, -u user_name：用户名；
--port=port_num, -P port_num：服务端端口；
--socket=path, -S path
--database=db_name, -D db_name：
--compress, -C：数据压缩传输
--execute=statement, -e statement：非交互模式执行SQL语句；
--vertical, -E：查询结果纵向显示；
--protocol={TCP|SOCKET|PIPE|MEMORY}：
巧用help命令
例如
mysql> help create Many help items for your request exist. To make a more specific request, please type 'help <item>', where <item> is one of the following topics: CREATE DATABASE CREATE EVENT CREATE FUNCTION CREATE FUNCTION UDF CREATE INDEX CREATE LOGFILE GROUP CREATE PROCEDURE CREATE SCHEMA CREATE SERVER CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW SHOW SHOW CREATE DATABASE SHOW CREATE EVENT SHOW CREATE FUNCTION SHOW CREATE PROCEDURE SHOW CREATE SCHEMA SHOW CREATE TABLE SHOW CREATE USER SPATIAL INDEXES
继续help
mysql> help CREATE DATABASE Name: 'CREATE DATABASE' Description: Syntax: CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ... create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. URL: https://dev.mysql.com/doc/refman/5.7/en/create-database.html mysql>
（三）变量相关
mysql中变量可以分为系统变量与状态变量。
-
系统变量配置了mysql的运行环境属性。
-
状态变量显示了mysql运行过程中的状态信息。
而系统变量从作用域划分，又可以分为全局变量和会话变量。
- 全局级别的变量：golbal variables，作用域为整个mysql服务器。
- 会话级别的变量：session variables，作用域为当前会话。
查看全局级别的系统变量值
show global variables G;
从下图可以看出，我通过上述命令，能够查询出419个系统变量。
mysql/mariadb知识点总结（4）：数据库变量（参数）管理
根据变量名查看变量值，比如我想查看所有跟"版本"相关的属性值
show global variables like '%version%' ;
比如想要查看字符集相关的全局设置。
show global variables where variable_name like 'character%';
比如想要查看某些日志相关是否开启或关闭。
show global variables where variable_name like '%log%' and value='off';
如下语句查看会话级别的参数值
show session variables;
使用方法与全局的没有任何区别，只是将global关键字改为了session关键字。
注意：有些变量只存在于全局级别中，有些变量只存在于会话级别中。
如果不指明global或者session，则表示查看会话级别的变量值，如下语句表示显示会话级别的变量。
show variables;
可以查询mysql运行中的统计信息或状态数据
show status;
同理，也能够通过like过滤出我们想要查找的变量名称。
而且状态变量也分为全局级别与会话级别。
设置系统变量
- SET GLOBAL var_name = value;
- SET SESSION var_name = value;
不指定级别表示默认为设置会话级别的变量。
SET var_name = value;
运行时修改的变量并非永久有效，重启后将会失效；
运行时修改global级别的变量：对修改之前建立的会话没有影响，仅对修改后新建立的会话有效；
运行时修改session级别的变量：仅对当前会话有效，且立即生效；
如果想要使设定永久有效，需要设置配置文件。
但是在不确定的情况下，不要随便修改配置文件，否则将可能会出现无法预计的问题。
（四）用户管理
mysql的用户账号格式，如下:
username@host
上述格式表示，username对应的用户能够通过哪个host登录mysql。
host：此mysql用户能够通过哪些客户端主机IP登录当前服务器上的mysql服务,对于mysql来说，host可以为主机名，也可以为IP地址，但是，mysql认为主机名和IP地址属于不同的主机；
我们也可以使用通配符，描述客户端所在的host，常用通配符如下：
_：表示任意单个字符
%：表示任意长度的任意字符
使用通配符匹配主机的示例如下：
root@'10.1.%.%'
上述配置表示root用户可以通过10.1这个网段远程登录。
查询用户
mysql> use mysql; Database changed mysql> select user, host ,authentication_string from user; +---------------+-----------+-------------------------------------------+ | user | host | authentication_string | +---------------+-----------+-------------------------------------------+ | root | localhost | *68B8046836482ABB712A73AC6E4FA2FCC0EB3D86 | | mysql.session | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | mysql.sys | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | | root | % | *68B8046836482ABB712A73AC6E4FA2FCC0EB3D86 | | mindoc | % | *F067DE6ECF71403B54DD96AD0D688FE4B7F21191 | | mindoc | 127.0.0.1 | *F067DE6ECF71403B54DD96AD0D688FE4B7F21191 | +---------------+-----------+-------------------------------------------+ 6 rows in set (0.00 sec)
创建用户
第一种方式
create user 'test_user'@'%' identified by '123456';
第二种方式，通过授权，如果授权的用户不存在，会自动建立用户
grant all on database.* to test_user@'%' identified by '123456';
删除用户
- 通过drop方法，例如 DROP USER test_user@'%';
- 通过直接删除mysql.user表中对应用户的记录（不推荐，因为删除不干净，很多授权相关的没有清除）
重命名用户
重命名后，密码不变，授权还在，只是用户名改变而已
rename user test_user to NewName;
（五）密码管理
方法一) 使用mysqladmin设置密码
mysqladmin -utest_user -p123456 password 234567
方法二）使用root用户登录以后，使用SET PASSWORD命令设置用户密码
#已近用root登录mysql SET PASSWORD FOR 'test_user'@'%' = PASSWORD('123456');
方法三）在mysql5.7中设置，老版本，不再展示, 此方法需要刷新privileges
update mysql.user set authentication_string=password('234567') where user='test_user';
flush privileges;
忘记root密码
a) 停止当前mysql进程
service mysql restart mysql -uroot -p #ps -ef | grep mysql 看看进程是否正常启动，用新的密码登录，
#ps -ef | grep mysql 确保的确进程停止
b) mysql进程停止后，使用如下命令启动mysql，可以绕过用户验证。
mysqld_safe --skip-grant-tables &
c) 完成上述步骤后，使用如下命令登录数据库
mysql -uroot
d) 登录后使用之前说过的方法，修改root用户密码即可。
UPDATE mysql.user SET password=PASSWORD("new password") WHERE user='root';
e) 刷新后退出
FLUSH PRIVILEGES;
f) 停止grant skip状态的mysql,之后使用新的密码正常启动MySQL，
（六）授权管理
常用授权语句的语法如下：
GRANT ALL [PRIVILEGES] ON db.tbl TO 'username'@'host' IDENTIFIED BY 'password';
#privileges可以省略
例如
授权 grant all privileges on test_db.* to test_user@localhost identified by '123456'; grant all privileges on test_db.* to test_user@'%' identified by '123456'; grant all privileges on test_db.* to test_user@127.0.0.1 identified by '123456'; grant all on test_db.* to test_user@127.0.0.1 identified by '123456'; grant all on test_db.* to test_user@'192.168.%.%' identified by '123456'; FLUSH PRIVILEGES
授权某些权限
grant select on *.* to 'select_only_user'@'%' IDENTIFIED BY 'your_password'; grant insert,select,update on test_db.test_tbl to someone@'localhost' identified by '123456'; grant 权限列表 on 数据库名.数据表名 to '用户名'@'主机' identified by '密码';
with grant option是指允许用户dba，传递其拥有的权限给其他的用户
grant all privileges on test_db.* to test_user@'%' identified by '123456' with grant option;
将同样的权限同时授权于多个用户
grant select on test_db.* to test_user01@localhost,test_user02@localhost;
只对某张表的某个字段授权
grant select (name,age) on test_db.students to test_user@localhost;
查看用户的授权
-
show grants for test_user@%;数据库的角度查看授权
-
SELECT * FROM mysql.db where Db='test_db';
删除授权
建议先查看所拥有的的权限，再revoke
如
show grants for 'test_user'@'%'; #根据show的结果来进行下一步 revoke all on test_db.* from 'test_user'@'%';
（七）库管理
创建数据库
1、在其他关系型数据库中，database与schema是有区别的，但是在mysql 5.0.2以后，我们在创建数据库时可以不加区分的使用database和schema，我们可以将create schema理解为 create dataabse的代名词。
2、if not exists表示在对应的数据库不存在的时候才会创建，if not exists被中括号括起来，表示此项为可选，建议在sql脚本中使用create命令创建数据库时加入此项，以免对应名称的数据库已经存在导致sql脚本终止。
3、create_specification表示我们可以在创建数据库时指定对应的数据库规范，规范具体内容见4和5。
4、我们可以在创建数据库时指定数据库的字符集，使用character set对应字符集名称即可指定使用什么字符集，如果使用了default关键字，那么这个数据库中创建的所有表默认都会继承这个数据库的字符集，default为可选选项，如果你不知道存在哪些字符集，可以使用"show character set;"命令查看所有可用字符集，针对数据库有数据库级别的字符集，针对表有表级别的字符集，针对客户端与服务端一次会话，又有会话级别的连接字符集，当我们使用status命令时，可以看到当前连接、客户端、数据库、以及服务器级别的字符集的值，如果我们在创建数据库的时候没有指定数据库的字符集，那么数据库默认继承服务器级别的字符集。
5、我们可以在创建数据库时指定数据库的排序规则，比如有多个汉字，我们是以笔画多少排序呢？还是以汉字对应的拼音首字母的顺序排序呢？还是以笔画的顺序排序呢？这种就可以理解为排序规则，同一种字符集下可能有多种排序规则，但是一种排序规则只能对应于一种字符集，也就是说，当你指定了一种排序规则，则不用再指定对应的字符集，因为这种排序规则肯定是针对某个字符集制定的排序规则，所以，如果给定了排序规则，则不要同时指明字符集，每一个字符集都有默认的排序规则，你可以只指定字符集，不指定排序规则，代表使用字符集默认的排序规则，使用"show collation;"命令可以查看所有可用的排序方式，以及排序方式对应的字符集，default为可选指令，作用与4中的default的作用相同。
create database if not exists testdb default character set utf16;
查看所有数据库
show databases;
列出创建对应数据库的sql语句：
show create database gogs;
查看可用的字符集的命令：
show character set;
查看排序方式的命令：
show collation;
查看当前数据库与当前连接的概要信息：
用status命令查看数据的基本信息
1 当前链接的id号
2 当前选择的使用的数据库
3 当前链接的登录用户
4 是否使用了ssl
7 当前会话的行终结符是分号
8 当前mysql版本为mariadb分支
9 当前mysql服务器的版本号
10协议版本
11使用的链接类型，通过本机的套接字文件进行连接
12服务器使用的字符类型
13当前数据库使用的字符类型
14当前mysql客户端使用的字符类型
15当前链接使用的字符类型
16套接字文件路径
17mysql数据库的启动时长
修改数据库
修改数据库的基本语法如下：
ALTER {DATABASE | SCHEMA} [db_name] alter_specification ...
alter_specification:
[DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
修改数据库无非就是修改数据库的字符集，或者修改字符集的排序规则，字符集与排序规则的概念已经在创建数据库的命令中描述过，此处不再赘述，但是需要注意的是，修改字符集与修改排序规则是"二选一"的，原因在创建数据库的命令中同样描述过，所以直接给出示例语句如下。
修改数据库的字符集。
使用如下语句查看字符集。
show variables like 'character%';
如果字符集与我们期望的字符集不相符，则使用如下语句修改数据库字符集。
alter database testdb character set utf8;
修改数据库的字符集，并设置为默认字符集，数据库中的表都会继承此字符集。
alter database testdb default character set utf8;
删除数据库的基本语法如下，注意，删除操作无法恢复：
DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
drop database if exists testdb;
（八）表管理
想要列出当前数据库上的所有表
show tables;
想要查看当前库中所有表的具体属性信息
show table statusG;
*************************** 31. row *************************** Name: user Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 7 Avg_row_length: 124 Data_length: 868 Max_data_length: 281474976710655 Index_length: 4096 Data_free: 0 Auto_increment: NULL Create_time: 2019-12-07 14:16:40 Update_time: 2020-04-20 23:15:39 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges 31 rows in set (0.00 sec)
查看某张表的状态
mysql> show table status like 'user'G; *************************** 1. row *************************** Name: user Engine: MyISAM Version: 10 Row_format: Dynamic Rows: 7 Avg_row_length: 124 Data_length: 868 Max_data_length: 281474976710655 Index_length: 4096 Data_free: 0 Auto_increment: NULL Create_time: 2019-12-07 14:16:40 Update_time: 2020-04-20 23:15:39 Check_time: NULL Collation: utf8_bin Checksum: NULL Create_options: Comment: Users and global privileges 1 row in set (0.00 sec)
查看表结构的对应语句如下：
desc table_name;
如果想要查看某张表被创建时对应的sql语句，可以使用如下语法
show create table table_nameG;
创建表
CREATE TABLE [IF NOT EXISTS] tbl_name(create_definition,...)
前半部分为create table 很容易理解，CREATE TABLE [IF NOT EXISTS] tbl_name
后半部分为(create_definition,...)，首先要注意的是，create_definition需要被括号括起来。
create_definition中的定义分为如下几类：
- 字段定义
- 表级别约束定义
- 索引定义
下面做具体的解释
1) 字段定义，字段也叫column，column_name column_defination
a)通过column_name指明字段名称
b)通过 column_defination指定字段定义。
-
字段定义
- 常用的可选约束设置, NOT NULL | NULL] [DEFAULT default_value] [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY] [COMMENT 'string']
- not null表示对应字段不能为空，不设置默认为null，表示对应字段可以为空
- DEFAULT default_value 表示设置字段默认值
- AUTO_INCREMENT 表示对应字段使用自动增长，一个表中只有一个字段能被设置为自动增长，而且这个字段必须被定义为key（或者索引），mysql默认也会认为"自动增长的键字段"为主键字段。所以，结合着一个表中只能有一个主键的定义，auto_increment往往只针对于主键字段进行设置，因为一个表中如果已经存在主键，当我们对非主键的键字段定义auto_increment时会报错，因为一个表中只能有一个主键，mysql又默认认为自动增长的键字段为主键，这样就会产生冲突，mysql认为这样会产生两个主键，所以，我们往往只对主键字段设置atuo_increment
- UNIQUE [KEY]此设置可省，如果显示设置，表示从字段级别设置当前字段为唯一键，key省，我们还可以通过表级别对当前字段进行唯一约束的定义。
- [PRIMARY] KEY 此设置可省，如果显示设置，表示从字段级别设置当前字段为主键，与UNIQUE [KEY]不同，UNIQUE [KEY]中的key可省，而 [PRIMARY] KEY中的 PRIMARY可省，KEY不可省，只写key就代表设置当前字段为primary key，注意，如果在字段级别设置当前字段为主键，则表示主键只包含当前字段一个字段，如果需要设置包含多个字段的主键，则必须在"表级别的约束定义"中设置主键。
- [COMMENT 'string']设置字段的描述信息，此设置可省。
-
表级别约束定义（key定义）
- PRIMARY KEY(col1[,col2, ....]) 用于定义主键，一个表中只能有一个主键，一个主键可以包含多个字段。
- UNIQUE KEY (col1[,col2, ....]) 用于定义唯一键，一个表中可以有多个唯一键。
- FOREIGN KEY 用于定义外键
- CHECK(expr) 用于定义检查性约束
- 当我们创建key时，即相当于创建了index。
-
索引定义（index定义）：
- {INDEX|KEY} 我们可以使用index或key关键字创建索引，注意，如果key写在此位置，与index相同，表示定义索引，而不是定义key。
- {FULLTEXT|SPATIAL} 如果我们的数据存储引擎为myisam，那么它还有两个特殊的索引，全文索引和空间索引。innodb引擎不支持全文索引和空间索引，但是在某些分支的新版本中，貌似已经有支持空间索引的innodb出现了，我们不进行细究。
创建表 示例：
- 如下示例表示创建一个表，表中有两个字段，id与name，id的数据类型为int，name的数据类型为varchar，长度为60.
红线标注的每一行，都能看做是一个"create_definition"而且下图中的create_definition都属于字段定义。
- 如下示例表示使用字段定义，对字段进行约束，此处将id字段设置为了主键，并且限制name字段不能为空，而且对name字段添加了注释，这些都属于在字段定义级别对字段进行了约束或设定，主键约束，非空约束，注意，在字段级别设置主键表示主键只包含当前字段，如果想要设置联合主键，必须在表级别定义主键
- 除了像上一个示例中那样，能直接在字段定义时就指定主键 ，也可以在表级别的约束定义中定义约束，示例如下
- 联合主键，上述示例中，在创建表时只定义了单一主键，在创建表时，我们也可以在表级别定义联合主键。
- 定义外键，我们可以在创建表时定义外键，如下示例表示test1表中的tid字段引用了test2表中的id字段作为外键。
注意，如下两个示例都表示在创建数据表时创建ind_name索引,此时key与index关键字都表示创建索引。
其他示例
下面演示，从一个现有的表中提取数据，并把数据放到新表中
比如，使用如下方法，将students表中的stuid,name,age三个字段中的数据取出，并填充到创建的新表testtb2当中，但是请注意，通过这种方式将testtb2创建完成后，只能将对应的数据填充过去，但是表的字段中的约束以及表的索引则无法复制过去，如果需要对应的约束，需要手动指明。
当然，我们也可以将students表的数据全部查询出来，然后创建一张新表，将students表中的数据全部存入新表中，这样就相当于复制表，但是只是复制了students表中的数据，字段的约束以及表的索引会丢失。
我们也可以只复制表结构，比如，我们创建一个新表，表结构与students表完全一致，也就是说，新表的所有字段都与students表一致，而且字段的约束也都相同，表中存在的索引也都相同。
所以，单纯的完全的复制表结构，可以使用如下语句。
上述描述与举例中，我们简单的描述了创建表、复制表、复制表结构的示例。
其实我们在创建表的时候，还可以加上表选项，表选项是可省的，表选项也可以有多个，注意，表选项的位置在(create_definition)的括号外面。
CREATE TABLE tbl_name [IF NOT EXISTS] (create_definition,...) [table_options]
表选项
- engine选项 ，指定创建的表使用哪种存储引擎
如下示例，表选项指定了当前表使用InnoDB存储引擎，并且设置了当前表自动增长的字段从2开始增长，以及针对当前创建的表设定了对应的字符集utf8。
表选项有很多，更多语法使用help create table命令参考帮助。
删除表
删除表比较简单，但是非常危险，如果不是确定要删而且必须要删，请勿随意删除，数据无价，请勿手贱，此处没有回收站。
删除表的语句如下，可以删除指定的单张表，也可以一次删除多张表，表之间用逗号隔开。
修改表
修改表的基本语法如下
ALTER TABLE tbl_name [alter_specification [, alter_specification] ...]
常用语句
修改表名、重命名表,将表test1改名为test2
alter table test1 rename as test2;
添加字段
为表添加字段，只添加字段，不指定字段的任何其他属性，如下语句表示为ttt表添加age字段。
alter table ttt add column age int;
修改表的字段时，column可省，不指定被操作对象时，默认为操作列。省略column后如下。
alter table ttt add age int;
为表添加字段，同时为添加的字段设定相应的约束。
alter table ttt add age int not null default 0; alter table ttt add column age int not null default 0;
为表添加字段，同时指定新添加字段在表中的位置，如下语句表示将新添加的id字段设置为表中的第一个字段。
alter table ttt add id int first;
为表添加字段，同时指定新添加字段在表中的位置，如下语句表示将新添加的age字段添加到name字段的后面。
alter table ttt add column age int after name;
删除字段
删除包含数据的字段时需要三思，珍爱数据，请勿手欠。
如下语句表示从tt表总删除stuname字段。
alter table tt drop stuname;
修改字段
修改字段名称
如下语句表示将testtable表中的name字段重命名为name1，name字段的数据类型为char（5），即使只是重命名，我们也需要在新名称后指定原字段的数据类型，否则会报错。
alter table testtable change name name1 char(5);
修改字段类型
比如将age字段从int类型改为char类型
alter table testtable change age age char(10);
或者使用modify来改变字段类型，modify不能用于修改字段名称，示例如下。
alter table testtable modify age int;