专栏首页运维录运维常用 mysql 语句

运维常用 mysql 语句

开始之前


上周隔壁专题推送最后一篇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;

小结


最后来总结下文章中的知识点

  • 项目性质或环境不同,所需要的 mysql 语句也可能不同。
  • 按使用频率来分类,分别为 操作数据表,数据导入/出,操作数据库。

本文分享自微信公众号 - 运维录(gh_70d95b8f5f7c),作者:东南

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-12-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Nginx 缓存服务器(番外)动态 upstream

    在更新应用镜像(图中的App1)版本后,部分静态资源抛出HTTP 502状态码。先来看下 nginx缓存服务器日志,重点在"Host is unreachabl...

    用户1560186
  • mysql 数据导入与导出

    在日常的运维工作中除了频繁操作基本的SQL语句外,还有另外一种场景,就是数据的导出与导入操作。

    用户1560186
  • Nginx Porxy反向代理

    我这里有个例子,早期的WEB服务器使用的是HTTP/1.0协议,是不支持keep-alive功能的,为了解决这个问题当时主流的解决方案是使用nginx做反向代理...

    用户1560186
  • MySQL联表查询的索引使用

    一共3张表knowledge, knowledge_question, knowledge_answer,数据在6000~10000之间。

    十毛
  • Kotlin中级(6)- - - Kotlin类之的继承.md

    因为Any这个类只是给我们提供了equals、hashcode、toString三个方法,我们可以看看Any这个类的源码实现

    Hankkin
  • 果断收藏!156页PPT全景展现全球区块链发展(附完整版下载手册)

    【编者按】美国加密货币报道媒体CoinDesk近期发布“全球区块链现状报告”,深入研究了快速发展的加密货币行业及其底层技术,该报告覆盖了公共区块链、企业区块链、...

    华章科技
  • 京东网络接入体系解密之高性能四层网关DLVS

    DLVS诞生之初 在SLB这块,京东用户接入系统提供四层负载均衡服务和应用层负载均衡服务,对于公网流量接入部分,和很多公司一样采用的是四层和应用层负载相结合的架...

    用户1263954
  • SAP CRM switchable settype处理逻辑

    When you open PROD model in Genil component editor via tcode GENIL_MODEL_BROWSER...

    Jerry Wang
  • Vue-开发工具的安装

    xing.org1^
  • Javaweb之Filter案例练习-自动登录问题和MD5加密

    前面已经完成了Filter的自动登录,但是有问题,我们在web.xml中Filter的url-mapping中配置的规则是/*, 也就是这个网站的所有请求都拦截...

    凯哥Java

扫码关注云+社区

领取腾讯云代金券