运维常用 mysql 语句

用户1560186

发布于 2019-12-19 16:11:55

1.4K0

发布于 2019-12-19 16:11:55

文章被收录于专栏：运维录运维录

开始之前

上周隔壁专题推送最后一篇Nginx文章(公众号出门左转 Nginx专题) ，从本周开始每周一推送MySql主题文章(Docker 主题每周四不变)，文章内容均为运维方向相关，聚沙成塔，集腋成裘，第一篇文章讲一下运维工作中经常使用的 MySql 语句。

数据库基本操作

# 创建库
create database test;
# 也可以创建时指定字符集
create database test character set utf8;

# 列出全部的库
show databases;

# 进入库
use test;

# 删除库
# 数据无价，注意备份数据
drop database test;

数据表基本操作

# 创建表
create table table1(
    `id` int(6) NOT NULL AUTO_INCREMENT,
    `c1` int(5) DEFAULT NULL,
    `c2` int(6) DEFAULT NULL,
    `c3` varchar(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 列出全部表(当前库)
show tables;

# 表结构
desc table1;
# 也可以使用 show 语句查看表结构
show create table table1;

# 复制表
# 只包括表结构、索引，不含数据
create table table2 like table1;
# 数据需要使用 insert语句完成复制
insert into table2 select * from table1;

# 查询表
select * from table2;
# *代表表中的所有列，也可以指定列的名字
select c1,c3,c4 from table1;

# 删除表
# 数据无价，注意备份数据
drop table table2;

向表中插入数据

功能: insert into 语句用于向表中插入数据。

语法:

insert into table_name values (v1, v2,....);
insert into table_name (列1, 列2,...) values (v1, v2,....);

示例:

# 创建表 table3
create table table3 like table1;

# 插入数据
insert into table3 value(1,100,20,'F7');

# 或者指定要插入数据的列
insert into table3(id,c3) values(2,'B6');

修改表中的数据

功能: update 语句用于修改表中的数据。

语法:

update table_name set feild_1=value1;
update table_name set feild_1=v_1, feild_2=v_2;
update table_name set feild_1=new [where feild_1=v1];

条件:where 子句是可选的，默认不指定的话，表中的每个记录都被更新。

示例:

# 上个示例中只在id,c3 列插入了数据，c1,c2列为NULL值。
select * from table3;
+----+------+------+------+
| id | c1   | c2   | c3   |
+----+------+------+------+
|  1 |  100 |   20 | F7   |
|  2 | NULL | NULL | B6   |
+----+------+------+------+
2 rows in set (0.00 sec)

# 接下来使用update 语句更新 c1,c2 列中的数据。
update table3 set c1=110, c2=20 where id=2;

删除表中的数据

功能: delete 语句用于删除表中的数据。

语法:

delete from table_name [where feild=value];

条件:where 子句指定哪些记录应该删除。注意如果不使用where将会删除表中所有的数据。

示例:

# 数据无价，注意备份数据
# 插入一条数据,id=3
insert into table3 values(3,120,20,'A1');

# where子语句指定了id范围，这里为 id=2。
delete from table3 where id>1 and id<3;

# where子语句指定逻辑条件，id=2已删除，除这里为id=3。
delete from table3 where id=2 or id=3;

# 慎用不带where条件的delete语句，将删除整个表的数据。
delete from table3;
# 由于仅剩一条数据，等同于下面的语句。
delete from table3 where id=1;

更改表结构

功能: alter table 语句用于更改表结构，例如删除、添加、修改表的字段。

示例: 以 table3 表为例。

# 添加字段(多个列使用","隔开)
alter table table3 add `sn` varchar(50) DEFAULT NULL;

# 添加字段的时候指定此字段的位置
# FIRST表示增加此列为第一个列，AFTER表示增加在某个列之后
alter table table3 add `c4` varchar(11) DEFAULT NULL after `c3`;

# 删除字段
alter table table3 drop column `sn`;
# 如果删除表中多个字段使用","隔开
# alter table table3 drop column `sn`, drop column `c4`;

# 修改字段长度
alter table table3 modify column `c3` varchar(20);

# 修改字段类型
alter table table3 modify column `c3` int(9);

# 修改字段名称
alter table table3 change `c3` `sn` int(20);

# 修改表的字符集
alter table table3 convert to character set utf8;

# 添加普通索引
# alter table table_name add index index_name(`column`)
alter table table3 add index index_sn(`sn`);
  
# 添加主键索引
alter table `table_name` add primary key(`column`)

where 子句

用于在初始表中筛选条件。它是一个约束声明，用于约束数据，在返回结果集之前起作用。

示例:

# 插入3条数据
insert into table3 values(1,100,10,10001);
insert into table3 values(2,200,20,10002);
insert into table3 values(3,300,30,10003);

# 这两条语句等价
select * from table3 where id > 1 and id < 4
select * from table3 where id between 2 and 3;

# 匹配查询
select * from table3 where sn like '1000%';

# 更多运算符    
=     # 等于
!=    # 不等于(<>)
>     # 大于
<     # 小于
>=    # 大于或等于
<=    # 小于或等于
between   # 介于一个包含范围内
like      # 搜索匹配的模式

in 子句

示例:

# 删除 user_id 为 '201705190002', '201705190003'
delete from table_name where user_id in ('201705190002', '201705190003');

# 查询 user 为 root 与 red。
select user,host from mysql.user where user in ('root', 'red');
+------+-----------+
| user | host      |
+------+-----------+
| root | %         |
| red  | localhost |
+------+-----------+
2 rows in set (0.00 sec)
注意：这里一定要将字符串用单引号''标注起来；

set 语句

SET 语句可以设置各种不同的变量类型，这些变量可以影响着服务器或者客户端的参数。

系统变量在 SET 语法中可以以 var_name 的方式来引用。

变量名前面用 GLOBAL 或 @@global. 来表示这是一个全局变量。

变量名前面用 SESSION , @@session. 或 @@ 来表示这是一个会话变量。

LOCAL 和 @@local. 其实 SESSION 和 @@session. 属同义。

如果变量名前面没有附加的修饰词的话，那么 SET 只修改会话变量。

示例

# show 最大连接数
show variables like 'max_con%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10    |
| max_connections    | 1000  |
+--------------------+-------+
2 rows in set (0.00 sec)

# 设置最大连接数(临时，重启失效)
set GLOBAL max_connections=2000;  
set GLOBAL max_connect_errors=1000;

# 设置SQL模式
set GLOBAL sql_mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# 设置慢查询
SET GLOBAL long_query_time = 5

# 关闭慢查询  
SET GLOBAL slow_query_log = 'OFF'

rename table语句

功能: rename table语句用于修改表名。

语法: rename table table_name to new_table_name;

示例:

# 注意，数据无价，注意备份数据。
show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| x_user          |
+-----------------+
1 row in set (0.00 sec)

rename table x_user to user;

show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| user            |
+-----------------+
1 row in set (0.00 sec)

当你执行 RENAME 时，不能有任何锁定的表或活动的事务。你同样也必须有对原初表的 ALTER 和 DROP 权限，以及对新表的 CREATE 和 INSERT 权限。

如果在多表更名中，MySQL 遭遇到任何错误，它将对所有被更名的表进行倒退更名，将每件事物退回到最初状态。

into outfile 语句

示例: select 查询结果写入到文件

select username,id from users into outfile '/tmp/user.txt';

注意：写入目录，需要写入权限。

load data infile 语句

示例：sqlmap 读取 /etc/passwd

LOAD DATA INFILE '/etc/passwd' INTO TABLE user FIELDS TERMINATED BY 'fuck' (ssl_cipher);

授权语句

# 创建数据库
create database `redmine-db` character set utf8;

# 授权用户
grant all privileges on `redmine-db`.* to `redmine-user`@'%' \
identified by 'password' with grant option;

# 刷新权限  
flush privileges;

# 查看用户权限
show grants for `redmine-user`;

# 赋予权限
# redmine用户已经拥有 redmine库全部权限了
grant select on `redmine-db`.*  to red2;

# 回收权限
revoke select on `redmine-db`.* from red2;

# 只读权限用户
grant select on `redmine-db`.* to 'red2'@'%' \
identified by 'password' with grant option;  

# 删除用户
drop user red2@'%';

# 刷新权限
flush privileges;