MySQL还能这样玩---第一篇之你所不知道的命令

MySQL还能这样玩---第一篇之你所不知道的命令

• 命令篇
  • DDL---表定义相关命令操作
    • show create table 表名 ---查看创建表的SQL语句
    • alter table 表名 modify 列名 列定义---修改列的类型
    • alter table 表名 add 列名 列类型 ---增加新列
    • alter table 表名 drop 列名---删除某列字段
    • later table 表名 change 旧列名 新列名 列的类型 ---修改表的列名，列定义
    • 修改字段排列顺序 --- first / after
    • 更改表名---alter table 旧表名 rename 新表名
  • DML---记录增删改查相关操作
    • 批量插入 --- insert into table (age,name) values("1","dhy"),("2","xpy"),("3","dhy_xpy")
    • 多表更新 ---- update t1,t2 set t1.name="123",t2.name="321" where t1.id=t2.id;
    • 多表删除 ---delete dp,xp from dhy_peo dp,xpy_peo xp where dp.id=xp.id;
    • 对分类聚合后的结果进行汇总--- with rollup
• 获取帮助
• 查询元数据信息
  • 常用视图

本篇文章重点在于对MySQL中不常用的骚操作进行整理

命令篇

DDL—表定义相关命令操作

show create table 表名 —查看创建表的SQL语句

“\G”的含义是使得记录能够按照字段竖向排列，以更好的显示内容较长的记录

\G和;都可以作为结束的标志符号

alter table 表名 modify 列名 列定义—修改列的类型

alter table 表名 add 列名 列类型 —增加新列

alter table 表名 drop 列名—删除某列字段

later table 表名 change 旧列名 新列名 列的类型 —修改表的列名，列定义

change和modify都可以修改表的定义，但是change后面需要写两次列名，但是change的优点在于修改列名称，modify则不能。

修改字段排列顺序 — first / after

上面介绍的字段增加和修改语法(ADD/CHANGE/MODIFY)中，都有一个可选项 first/after column_name ，这个选项可以用来修改字段在表中的位置，ADD增加的新字段默认在表的最后位置，而CHANGE/MODIFY默认都不会改变字段位置。

新增一个sex字段在name后面

alter peo add sex int(1) NOT NULL after name;

修改dhy_age到sex前面

alter table peo modify dhy_age int(1) NOT NULL after id;

CHANGE/FIRST/AFTER column都是mysql在标准sql上的拓展，其他数据库上不一定适用

更改表名—alter table 旧表名 rename 新表名

alter table peo rename dhy_peo;

DML—记录增删改查相关操作

批量插入 — insert into table (age,name) values(“1”,“dhy”),(“2”,“xpy”),(“3”,“dhy_xpy”)

insert into dhy_peo (dhy_age,name,sex) values(18,"123",1),(19,"321",0);

这个特性使得mysql在插入大量数据的情况下，可以节省网络开销，提高插入效率。

多表更新 ---- update t1,t2 set t1.name=“123”,t2.name=“321” where t1.id=t2.id;

 update dhy_peo dp , xpy_peo xp set dp.name="dhylikexpy" ,xp.name="xpylikedhy" where dp.id=xp.id;

多表更新的语法更多的用于根据一个表的字段动态更新另一个表的字段。

多表删除 —delete dp,xp from dhy_peo dp,xpy_peo xp where dp.id=xp.id;

delete dp,xp from dhy_peo dp,xpy_peo xp where dp.id=xp.id;

对分类聚合后的结果进行汇总— with rollup

按照名字分组，统计每组的人数，在对所有组的人数之和进行汇总

SELECT NAME,COUNT(1) FROM xpy_peo GROUP BY NAME WITH ROLLUP;

获取帮助

如果我们对mysql中某个指令的具体使用不清楚，可以通过下面的方式，快速查询:

? 命令名

查询元数据信息

元数据指的是数据的数据，比如: 表名，列名，列类型，索引名等的各种属性名称。

mysql将这些元数据信息，存储在information_schema数据库里面。

这是一个虚拟数据库，物理上并不存在相关的目录和文件，库里show tables显示的各种表也不是实际存在的物理表，都是视图。

需求: 1.删除test数据库下面的dhy_peo表 2.将test数据库下面所有存储引擎为myisam的表，改为innodb

通过下面的两条sql语句,可以获取到需要的SQL语句

SELECT CONCAT("drop table test.",table_name,";") FROM TABLES WHERE table_schema="test" AND table_name LIKE "dhy_peo";

SELECT CONCAT('alter table test1.',table_name,' engine=innodb;') FROM
TABLES WHERE table_schema='test' AND ENGINE='innodb';

常用视图

这里列出的常用视图都是information_schema数据库里面的表

• SCHEMATA: 提供关于当前MYSQL实例中所有数据库信息。

• TABLES：提供数据库中表的信息

还有一些信息大家可以自行查看

• COLUMNS:提供了表中的列信息.

对当前数据库下该表的每类字段，相关属性进行存储

• statistics: 存储了表索引信息