MySQL常用命令合集

 

一、用户连接、创建、权限、删除

 

1. 连接MySQL操作

mysql -h 主机地址 -u 用户名 -P端口号 -p

使用 SSL 连接

mysql --ssl-ca=ca.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem -h主机地址 -u用户名 -p

 

2. 创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 
  • host 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%.

 

3. 授权

GRANT [all privileges/某个权限] ON databasename.tablename TO 'username'@'host';

如果想让该用户可以授权,用以下命令:

GRANT all privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

 

4. 锁定用户

ALTER USER 'username'@'host' ACCOUNT LOCK;

解锁

ALTER USER 'username'@'host' ACCOUNT UNLOCK;

常见场景:

1 创建读写权限的用户

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON databasename.tablename TO 'username'@'host';

2 创建只读权限用户

GRANT SELECT,SHOW VIEW ON databasename.tablename TO 'username'@'host';

 

4. 设置与更改用户密码

方法一

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

如果是当前登陆用户用

SET PASSWORD = PASSWORD("newpassword"); 

方法二

ALTER USER 'root'@'localhost' IDENTIFIED BY 'password';

 

5. 撤销用户权限

REVOKE [ALL/某个权限] ON databasename.tablename FROM 'username'@'host'; 

某个用户权限可以用命令SHOW GRANTS FOR ‘username’@’host’; 查看.

 

5. 重命名用户

rename user 'old_name'@'host' to 'new_name'@'host';

 

7. 删除用户

DROP USER 'username'@'host';

 

8. 要求使用ssl登陆

# 修改已存在用户 
ALTER USER 'username'@'%' REQUIRE SSL;

# 创建用户
create user username_ssl@'%' identified by 'password' require ssl;

 

刷新权限表

flush privileges;

 

二、数据库与表显示、创建、删除

 

1. 数据库查看&创建&删除

-- 查看数据库
show databases; 

-- 创建库
create database [IF NOT EXISTS] <库名> [character set='utf8'];

-- 删除库
drop database <库名>; 

 

2. 表查看、创建、删除

-- 显示数据表
use <库名>;
show tables;

-- 创建表:
create table 表名 (字段设定列表) [engine=InnoDB] [charset=utf8mb4];

-- 查看创建表的 DDL 语句
show create table <表名>;

-- 显示表结构
desc <表名>;

-- 删除表
drop table [IF EXISTS] <表名>; 

-- 临时表
CREATE TEMPORARY TABLE <表名>(<字段定义>);

e.g.

CREATE TABLE
    USER
    (
        id INT NOT NULL AUTO_INCREMENT,
        stu_id INT NOT NULL,
        name VARCHAR(30) NOT NULL,
        phone VARCHAR(20),
        address VARCHAR(30) NOT NULL,
        age INT NOT NULL,
        PRIMARY KEY (id),
        UNIQUE KEY `un_stu_id` (stu_id),
        KEY `idx_name` (`name`) USING BTREE
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

注意: 为 text 等建立索引时要指定长度 e.g. KEY idx_text (f_text(64))

 

三、表复制、备份还原及清除

 

1. 复制表结构

含有主键等信息的完整表结构:

 CREATE table 新表名 LIKE book;

只有表结构,没有主键等信息:

create table 新表名 select * from books;
或
create table 新表名 as(select * from book);
或
create table 新表名 select * from books where1=2;

注: create table <t> select … 会造成索引丢失

 

2. 将旧表中的数据灌入新表

INSERT INTO <新表> SELECT * FROM <旧表>;

 

3. 显示创建表的DDL语句

show create table <表名>;

 

4. 清空表数据

truncate table <表名>;

 

5. 备份数据库

备份单个库

shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -u root -p <database_name> >database_name.sql

备份一个表

shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -u root -p <database_name> <table_name> > table_name.sql

备份多个库

shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -u username -p --databases <dbname1> <dbname2> > Backup.sql

备份所有库

shell> mysqldump --single-transaction --master-data=2 --default-character-set=utf8 -A -u root -p > back.sql

忽略某些库表

--ignore-table=performance_schema.* --ignore-table=information_schema.* --ignore-table=sys.* --ignore-table=test.*

带上压缩

shell> mysqldump -A | gzip >> backup.sql.gz

 

6. 还原数据库

shell> mysql -u root -p -f [database_name] < backup.sql

 

7. 从备份文件抽取数据

提取某个库的所有数据

shell> sed -n '/^-- Current Database: `test_restore`/,/^-- Current Database:/p' mysql_back.sql

只提取建表语句

shell> sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `test1`/!d;q' mysql_back.sql

只提取数据

shell> grep -i 'INSERT INTO `test1`' mysql_back.sql

提取所有建库表语句

shell> grep -iv 'INSERT INTO `' mysql_back.sql

 

8. 导出数据

<select语句> into outfile "dest_file";

 

9. 导入数据

load data infile "<file_name>" into table <table_name>;

 

四、修改表的列与表名

 

1. 给列更名

alter table <表名称> change <旧字段名称> <新字段名称>

 

2. 给表更名

alter table <旧表名称> rename <新表名称>

 

3. 修改某个表的字段类型及指定为空或非空

alter table <表名称> change <字段名称> <字段名称> <字段类型> [not null];
alter table <表名称> modify <字段名称> <字段类型> [not null];

 

4. 增加一个字段(一列)

alter table 表名称 add column 字段名称 字段类型;

加在某个字段后面

alter table 表名称 add column 字段名称 字段类型 after 某个字段;

加在最前

alter table 表名称 add column 字段名称 字段类型 first;

 

5. 更改一个字段名字(也可以改变类型和默认值)

alter table <表名称> change <原字段名称> <新字段名称 字段类型>; 

 

6. 改变一个字段的默认值

alter table 表名称 alter 字段名称 set default 值;

该方法不会锁表

 

7. 改变一个字段的数据类型

alter table <表名称> change column <字段名称> <字段名称> <字段类型>;

 

8. 删除字段

alter table <表名称> drop column <列名>;

 

五 查询表

SELECT [DISTINCT] <字段名称,用逗号隔开/*>

FROM <left_table> [<join_type> JOIN <right_table> ON <连接条件>]

WHERE <where条件>

GROUP BY <分组字段>

HAVING <筛选条件>

ORDER BY <排序条件> [desc/asc]

LIMIT n[, m]

 

1. GROUP BY 与聚合函数 使用注意点

1 在不使用聚合函数的时候,group by 子句中必须包含所有的列,否则会报错

正确: select name,age from test group by name,age; //和 select 一样

2 在 group by 子句中不要加上聚合函数处的列名

 

2. having

SQL 标准
要求 having 必须引用 group 子句中的列或者用聚合函数处理过
后的列。

mysql 对这一标准进行了一些扩展,它允许 having 引
用 select 中检索的列和外部查询中的列。

having 中用到的条件要
么在 group by 中出现,要么在 select 的列中出现,要么在外查
询中出现。

 

3. from

from 子查询时要给数据表指定一个别名。from (select ..) [as] 别名 where…

 

4. union

select 语句 union [all] select 语句

union 会去重

 

5. join 外连接查询

select * from tableA A [left、right] join tableB B on A.id = B.id

 

6. join 交叉连接

select * from tableA,tableB
select * from tableA cross join tableB

逗号与 cross join 区别是逗号不能使用 on

结果会有 n * n 条记录(笛卡尔乘积)

 

7. join 内连接

select * from tableA A inner join tableB B on A.id = B.id
select * from tableA A inner join tableB B using(id)

using(字段) 可以合并相同字段,并且符合 A.id = B.id

内连接在没有条件时和交叉连接没有区别。

STRAIGHT_JOIN 可以手动指定驱动表

 

六 索引的创建、删除和查看

 

1. 创建索引

方法一

-- 普通索引
ALTER TABLE 表名称 ADD INDEX index_name (column_list)
-- 唯一索引
ALTER TABLE 表名称 ADD UNIQUE (column_list)
-- 主键索引
ALTER TABLE 表名称 ADD PRIMARY KEY (column_list)

方法二

CREATE INDEX index_name ON 表名称 (column_list)
CREATE UNIQUE INDEX index_name ON 表名称 (column_list)

column_list 指出对哪些列进行索引,多列时各列之间用逗号分隔。
索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。
另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。

 

2. 删除索引

-- 删除索引
DROP INDEX index_name ON 表名称;
ALTER TABLE 表名称 DROP INDEX index_name;
-- 删除主键
ALTER TABLE 表名称 DROP PRIMARY KEY;

 

3. 查看索引

show index from 表名称;
show keys from 表名称;

 

4. 手动选择索引

  • USE INDEX : 向优化器提示如何选择索引
  • IGNORE INDEX : 忽略索引
  • FORCE INDEX : 强制使用索引
select * from tableA USE INDEX (key1, key2) where key1=1 and key2=2

 

七 外键

 

1. 增加外键

建表时

constraint 外键名 foreign key(外键字段) refere