MySQL

MySQL-5.7数据库管理命令1

1.数据库服务相关命令12

1.1.数据库服务设置登录密码12

1.1.1.Linux命令行：mysqladmin -u用户信息 password "密码信息" 例：mysqladmin -uroot password "oldboy123"12

1.2.数据库服务修改登录密码12

1.2.1.Linux命令行：mysqladmin -u用户信息 -p password "新密码信息" 例：mysqladmin -uroot -p password "oldboy1234" PS：回车后输入的是原密码信息13

1.3.登录数据库时方式13

1.3.1.Linux命令行：mysql -u用户信息 -p密码信息 -h登录方式 例：mysql -uroot -poldboy123 -h172.16.1.5113

1.4.登录数据库服务13

1.4.1.Linux命令行：mysql -u用户信息 -p密码信息 例：mysql -uroot -poldboy12313

1.5.退出数据库服务13

1.5.1.数据库命令行：quit14

1.6.查询数据库生产表的存储引擎14

1.6.1.select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');14

1.7.压测数据库14

1.7.1.mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='780P'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose14

1.7.1.1.--defaults-file=/etc/my.cnf：指定配置文件15

1.7.1.2.--iterations=1：压测命令执行一次15

1.7.1.3.--concurrency=100 ： 模拟同时100会话连接15

1.7.1.4.--create-schema='test' ： 操作的库是谁15

1.7.1.5.--query="select * from test.t100w where k2='780P'" ：做了什么操作15

1.7.1.6.--number-of-queries=2000 ： 一共做了多少次查询15

1.7.1.7.-verbose：显示详细信息15

1.8.数据库安全初始化 PS:只限于MariaDB15

1.8.1.mysql_secure_installation15

1.8.1.1.初始化第一步：确认root用户密码16

1.8.1.1.1.Enter current password for root (enter for none):16

1.8.1.2.初始化第二步：设置root用户密码16

1.8.1.2.1.Set root password? [Y/n]16

1.8.1.3.初始化第三步：是否移除匿名用户16

1.8.1.3.1.Remove anonymous users? [Y/n] y16

1.8.1.4.初始化第四步：是否允许root用户远程登录16

1.8.1.4.1.Disallow root login remotely? [Y/n] y16

1.8.1.5.初始化第五步：是否移除测试数据库16

1.8.1.5.1.Remove test database and access to it? [Y/n] y17

1.8.1.6.初始化第六步：是否重新刷新授权表17

1.8.1.6.1.Reload privilege tables now? [Y/n] y17

2.数据库命令语法17

2.1.SQL语句语法17

2.2.SQL语句特殊符号含义17

2.2.1.* ：表示所有18

2.2.2.\G ：表示表信息竖行显示18

3.数据库默认查看18

3.1.查看正在运行服务器线程的列表：show processlist;18

3.2.查看数据库支持的字符集18

3.2.1.show charset;18

3.3.查看字符集校对规则18

3.3.1.show collation;19

3.4.查看存储引擎转态19

3.4.1.show engine innodb status\G;19

4.用户相关命令19

4.1.创建用户：create user '用户名'@'登录方式' identified by '登录方式'; 例：create user 'oldboy'@'10.0.0.%' identified by '123';19

4.2.授权(创建)用户管理某数据库：grant 授权权限信息 on 数据库信息.数据库内表信息 to '用户信息'@'允许连接数据库白名单信息' identified by '密码信息'; 例：grant all on wordpress.* to 'wordpress'@'172.16.1.%' identified by 'oldboy123';19

4.3.授权一个用户为超级管理员20

4.4.查看数据库管理用户是否创建成功：select user,host from mysql.user;20

4.5.删除用户：drop user 用户名称@'登录方式' 例：drop user wordpass@'localhost';20

4.6.修改用户：alter user 用户名@'用户登录方式' identified by '修改用户的密码'; 例：alter user oldboy@'10.0.0.%' identified by '456';20

4.7.查看用户权限：show grants for '用户名'@'登录方式'; 例：show grants for 'oldboy'@'10.0.0.%';20

4.8.回收用户权限：revoke 要收回的权限 on 库信息.表信息 from '用户信息'@'登录方式'; 例：revoke delete on oldboy.* from 'oldboy'@'10.0.0.%';20

5.数据库相关命令20

5.1.使用数据库命令：use+库名称； 例：use mysql;21

5.2.查看所有数据库信息：show databases;21

5.3.查看建库语句21

5.3.1.show create databases 库名称; 例：show create database oldboy;21

5.4.查看数据库表所有列信息：desc mysql.表信息; 例：desc mysql.user;21

5.5.创建数据库命令：create database 数据库名称; 例：create database db01;21

5.6.创建数据库指定字符集：create database 数据库名称 charset 字符集信息; 例：create database school charset utf8;21

5.7.创建库并指定字符集及校对规则：create database 库名称 charset 字符集 collate 校对规则； 例：create database oldboy charset utf8mb4 collate utf8mb4_bin;22

5.8.修改库字符集22

5.8.1.alter database 库名称 charset 想要改为的字符集； 例：alter database oldboy charset utf8;22

5.9.删除数据库：drop database 数据库名称; 例：drop database db01;22

6.表相关命令22

6.1.查看库内的所有表信息：show tables;22

6.2.查看表的所有列信息22

6.2.1.desc 表名称; 例desc stu;23

6.3.查看建表语句23

6.3.1.show create table 表名称; 例：show create table stu;23

6.4.创建一个表的复制23

6.4.1.create table 创建表名称 like 原表名称; 例：create table stubak like stu；23

6.5.创建表24

6.5.1.create table 表名称( 列一名称 数据类型 表属性 comment '注释信息'， 列二名称 数据类型 表属性 comment '注释信息', 列三名称 数据类型 表属性 comment '注释信息', ... )engine=存储引擎 charset=字符集 comment '学生表'；24

6.5.1.1.CREATE TABLE stu( id INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号', sname VARCHAR(255) NOT NULL COMMENT '姓名', sage TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄', sgender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别' , sfz CHAR(18) NOT NULL UNIQUE COMMENT '身份证', intime TIMESTAMP NOT NULL DEFAULT NOW() COMMENT '入学时间' ) ENGINE=INNODB CHARSET=utf8 COMMENT '学生表';24

6.6.修改表 （会对表进行锁定）25

6.6.1.在表中添加列25

6.6.1.1.alter table 表名称 add column 列名称 表属性; 例：alter table stu add column qq varchar(20) not null unique comment 'qq号';25

6.6.2.在表中某列后插入列25

6.6.2.1.alter table 表名称 add 列名称 表属性 after 插入列; 例：alter table stu add wechat varchar(64) not null unique comment '微信号' after sname;26

6.6.3.在第一列插入列26

6.6.3.1.alter table 表名称 add 列名称 表属性 first; 例：alter table stu add num int not null comment '编号' first;26

6.6.4.删除列26

6.6.4.1.alter table 表名称 drop 列名称; 例：alter table stu drop num;26

6.6.5.修改表名26

6.6.5.1.alter table 原表名 rename to 修改成的表名; 例：alter table student rename to st;27

6.6.6.修改引擎27

6.6.6.1.alter table t1 engine=修改后引擎; 例：alter table t1 engine=innodb;27

6.6.7.修改字符集27

6.6.7.1.alter table t2 charset=修改后字符集; 例：alter table t2 charset=utf8mb4;27

6.6.8.修改数据类型27

6.6.8.1.alter table 表名称 modify 列名称 表属性; 例：alter table stu modify sname varchar(128) not null;28

6.6.9.修改列名称及数据类型28

6.6.9.1.alter table 表名称 change 原列名称 改为列名称 表属性； 例：alter table stu change sgender sg char(1) not null default 'n';28

6.7.删除表28

6.7.1.drop table 表名称； 例：drop table stu;28

修改表规范： 1、使用追加的方式添加列 2、修改列属性，尽量使用modify语句 3、修改表定义，建议在业务不繁忙期间进行 尽量采用pt-osc或者gh-ost工具减少业务影响( 修改表28

（会对表进行锁定）)28

7.数据相关命令28

7.1.表中数据相关命令（DML） 对表中的数据行进行增、删、改29

7.1.1.在表中插入数据29

7.1.1.1.在表中录入一行数据29

7.1.1.1.1.方法一：标准insert语句30

insert into 表名称(列1名称,列2名称) values ('列1数据','列2数据','列3数据',...);30

7.1.1.1.2.方法二：简略inert语句30

insert into 表名称 values ('数据1','数据2','数据3',...);30

7.1.1.2.针对性录入数据31

7.1.1.2.1.insert into 表名称(列1名称,列2名称) values ('数据1','数据2','数据3',...);31

INSERT INTO stu(sname,sfz) VALUES ('w5','34445788');31

7.1.1.3.同时录入多行数据31

7.1.1.3.1.insert into 表名称(列1名称,列2名称) values ('数据1','数据2','数据3',...), ('数据1','数据2','数据3',...), ('数据1','数据2','数据3',...);32

INSERT INTO stu(sname,sfz) VALUES ('w55','3444578d8'), ('m6','1212313'), ('aa','123213123123');32

7.1.2.修改表中数据32

7.1.2.1.update 表名称 set 修改列名称='修改数据' where 唯一能确定该行数据信息; 例：UPDATE stu SET sname='zhao4' WHERE id=2;33

7.1.2.1.1.PS：update语句必须要加where，否则会全表更新33

7.1.3.删除表33

7.1.3.1.全表删除33

7.1.3.1.1.delete from 表名称; 例：delete from stu;33

7.1.3.2.删除指定行33

7.1.3.2.1.delete from 表名称 where 行信息; 例：delete from stu where id=3;34

7.1.3.3.删除数据页（表段不删除）34

7.1.3.3.1.truncate table 表名称; 例：truncate table stu;34

7.1.3.4.伪删除数据行34

7.1.3.4.1.第一步：添加状态列34

ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 comment '状态列,0为删除,1位存在';35

7.1.3.4.2.第二步：UPDATE 替代 DELETE35

UPDATE stu SET state=0 WHERE id=6;35

7.1.3.4.3.第三步：业务语句查询,只查询状态为1的列35

SELECT * FROM stu WHERE state=1;35

7.2.查询表中数据命令（DQL） 命令：select35

7.2.1.单独使用35

7.2.1.1.select @@参数名称36

7.2.1.1.1.查看端口：SELECT @@port;36

7.2.1.1.2.查看软件目录：SELECT @@basedir;36

7.2.1.1.3.查看数据目录：SELECT @@datadir;36

7.2.1.2.select 函数();36

7.2.1.2.1.查看当前时间：SELECT NOW();36

7.2.1.2.2.查看当前库：SELECT DATABASE();36

7.2.1.2.3.查看当前用户：SELECT USER();36

7.2.1.2.4.查看当前版本：select version();36

7.2.1.2.5.拼接字符串：select concat("hello world");37

多行显示：select concat (user,"@",host) from mysql.user;37

一行显示：select group_concat(user,"@",host) from mysql.user;37

内置函数查询： https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg(select 函数();)37

查看MySQL中所有参数： show variables\G; 模糊查询MySQL参数： show variables like '%trx%'\G;( select @@参数名称 )37

7.2.2.select 多子句执行顺序37

7.2.2.1.select 列 from 表 where 条件 group by 列 having 条件 order by 列 limit 条件37

7.2.3.单表子句-from37

7.2.3.1.横行显示表的具体信息：select 列信息 from 库信息.表信息； 例：select * from mysql.user38

7.2.3.2.竖行显示表的具体信息：select 列信息 from 库信息.表信息\G; 例：select * from mysql.user\G;38

7.2.3.3.只显示表的某几列信息：select 列1名称,列2名称 from 库信息.表信息； 例：select user,host from mysql.user;38

7.2.4.单表子句-where SELECT 列1,列2 FROM 表名称 WHERE 列名称='查询信息';38

7.2.4.1.where配合等值查询38

7.2.4.1.1.查询中国(CHN)所有城市信息： select * from city where countrycode='chn';38

7.2.4.2.where配合比较操作符(> < >= ！= having --> order by --> limit44

7.2.7.2.使用示例44

7.2.7.2.1.查看中国所有的城市，并按人口数进行排序(从大到小) select * from city where countrycode='chn' order by population desc;45

7.2.7.2.2.统计中国各个省的总人口数量大于5000000的省，按照总人口从大到小排序 select district,sum(population) from city where countrycode='chn' group by district having sum(population)>5000000 order by sum(population) desc;45

7.2.8.单表字句 - limit45

7.2.8.1.语句执行逻辑45

7.2.8.1.1.select --> from --> where --> group by --> having --> order by --> limit46

7.2.8.2.使用示例46

7.2.8.2.1.统计中国各个省的总人口数量，按照总人口从大到小排序，只显示前三名 select district,sum(population) from city where countrycode='chn' group by district order by sum(population) desc limit 3;46

limit N,M --> 跳过N行，一共显示M行 limit 5,5(统计中国各个省的总人口数量，按照总人口从大到小排序，只显示前三名46

select district,sum(population) from city46

where countrycode='chn'46

group by district46

order by sum(population) desc46

limit 3;)46

7.2.9.联合查询- union all46

7.2.9.1.查询中国或美国城市信息 select * from city where countrycode='chn' union all select * from city where countrycode='usa';47

说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能 UNION 去重复 UNION ALL 不去重复(查询中国或美国城市信息47

select * from city where countrycode='chn'47

union all47

select * from city where countrycode='usa';)47

7.2.10.多表连接查询47

7.2.10.1.类型47

7.2.10.1.1.笛卡尔48

7.2.10.1.2.内连接（ join）48

查询世界上人口数量小于100人的城市名，国家名和国土面积 select city.name,country.name,country.surfacearea from city join country on city.countrycode=country.code where city.population=85 then student.sname end), GROUP_CONCAT(case when sc.score>=70 and sc.score=60 and sc.score数据库服务相关命令数据库服务设置登录密码Linux命令行：mysqladmin -u用户信息 password "密码信息" 例：mysqladmin -uroot password "oldboy123"

最后一列不加逗号

insert into 表名称(列1名称,列2名称)values('列1数据','列2数据','列3数据',...);

INSERT INTO stu(id,sname,sage,sg,sfz,intime) VALUES(1,'zs',18,'m','123456',NOW());

insert into 表名称values('数据1','数据2','数据3',...);

INSERT INTO stu VALUES(2,'ls',18,'m','1234567',NOW());

INSERT INTO stu(sname,sfz)VALUES ('w5','34445788');

INSERT INTO stu(sname,sfz)VALUES ('w55','3444578d8'),('m6','1212313'),('aa','123213123123');

ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 comment '状态列,0为删除,1位存在';

UPDATE stu SET state=0 WHERE id=6;

SELECT * FROM stu WHERE state=1;

多行显示：select concat (user,"@",host) from mysql.user;一行显示：select group_concat(user,"@",host) from mysql.user;内置函数查询：https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg(select 函数();)查看MySQL中所有参数：show variables\G;模糊查询MySQL参数：show variables like '%trx%'\G;( select @@参数名称 )

where配合比较操作符(> < >= ！= 查询世界上少于100人的城市：select * from city where population中国人口数量大于500wselect * from city where countrycode='chn' and population>'5000000';中国或美国城市信息select * from city where countrycode='chn' or countrycode='usa';

where配合模糊查询查询省的名字前面带guang开头的select * from city where district like 'guang%';

PS：注意:%不能放在前面,因为不走索引

where配合in语句中国或美国城市信息select * from city where countrycode in ('chn','usa');

where配合between and查询世界上人口数量大于100w小于200w的城市信息select * from city where population between '1000000' and '2000000';

group by + 常用聚合函数select 列名称,聚合函数 from 表名称 group by 列名称;作用根据 by后面的条件进行分组，方便统计，by后面跟一个列或多个列

group by 分组功能原理1. 按照分组条件进行排序2. 进行分组列的去重复3. 聚合函数将其他列的结果进行聚合。

常用聚合函数max()：最大值min()：最小值avg()：平均值sum()：总和count()：统计数量group_concat()：列转行distinct()：去重复

使用示例统计世界上每个国家的总人口数select countrycode,sum(population) from city group by countrycode;统计中国各个省的总人口数量select district,sum(population) from city where countrycode='chn' group by district;统计世界上每个国家的城市数量select countrycode,count(name) from city group by countrycode;显示带“gunag”的省所有城市信息select distinct(district),group_concat(name) from city where district like 'guang%' group by district;统计中国每个省的城市个数及城市名列表select district,group_concat(name),count(name) from city where countrycode='chn' group by district;

单表子句-having语句执行逻辑where|group|having

使用示例统计中国每个省的总人口数，只打印总人口数小于100wselect district,sum(population) from city where countrycode='chn' group by district having sum(population)语句执行逻辑select --> from --> where --> group by --> having --> order by --> limit

使用示例查看中国所有的城市，并按人口数进行排序(从大到小)select * from city where countrycode='chn' order by population desc;统计中国各个省的总人口数量大于5000000的省，按照总人口从大到小排序select district,sum(population) from citywhere countrycode='chn'group by district having sum(population)>5000000order by sum(population) desc;

单表字句 - limit语句执行逻辑select --> from --> where --> group by --> having --> order by --> limit

使用示例统计中国各个省的总人口数量，按照总人口从大到小排序，只显示前三名select district,sum(population) from citywhere countrycode='chn'group by district order by sum(population) desclimit 3;limit N,M --> 跳过N行，一共显示M行limit 5,5(统计中国各个省的总人口数量，按照总人口从大到小排序，只显示前三名select district,sum(population) from citywhere countrycode='chn'group by district order by sum(population) desclimit 3;)

联合查询- union all查询中国或美国城市信息select * from city where countrycode='chn' union all select * from city where countrycode='usa';说明:一般情况下,我们会将 IN 或者 OR 语句 改写成 UNION ALL,来提高性能UNION 去重复UNION ALL 不去重复(查询中国或美国城市信息select * from city where countrycode='chn' union all select * from city where countrycode='usa';)

多表连接查询类型笛卡尔内连接（ join）

查询世界上人口数量小于100人的城市名，国家名和国土面积select city.name,country.name,country.surfaceareafrom city join country on city.countrycode=country.codewhere city.population

left join

左表所有数据+右表满足条件的数据，不满足的用null填充作用

差集

left joinwhere 右表条件 in null

select a.sname from (select student.snamefrom teacher join courseon teacher.tno=course.tnojoin scon course.cno=sc.cno join studenton sc.sno=student.snowhere teacher.tname = 'hesw') as aleft join (select student.sname from teacher join courseon teacher.tno=course.tnojoin scon course.cno=sc.cno join studenton sc.sno=student.snowhere teacher.tname = 'oldguo') as bon a.sname=b.snamewhere b.sname is null 可以优化 not in !=强制驱动表

优化时用强行指定驱动表

select *from teacherleft join course on teacher.tno=course.tno where course.cno is not null ; 结果集小的表作为驱动表night join

套路1. 找所有相关表2. 找直接或间接关联条件3. 使用join将关联表进行连接4. 罗列所有其他查询条件

扩展case when 条件 then 输出 end

统计每门课程:优秀(85分以上),良好(70-85),一般(60-70),不及格(小于60)的学生列表course.cname , GROUP_CONCAT(case when sc.score>=85 then student.sname end),GROUP_CONCAT(case when sc.score>=70 and sc.score=60 and sc.score列别名 列别名只能在 having 、order by 子句中调用查询世界上人口数量小于100人的城市名，国家名和国土面积select city.name as 城市名, country.name as 国家名, country.surfacearea as 国土面积, city.population as 城市人口 from city join country on city.countrycode=country.code where city.name='shenyang';

表别名功能： 表别名设置之后，覆盖到整个查询查询世界上人口数量小于100人的城市名，国家名和国土面积select a.name as 城市名, b.name as 国家名, b.surfacearea as 国土面积, a.population as 城市人口 from city as a join country as bon a.countrycode=b.code where a.name='shenyang';

元数据获取show语句help show; #查看所有show语句show databases; # 查询所有库名show tables; # 查询当前库的所有表名 show tables from world; # 查询world库下的所有表名show create database world; # 查询world建库语句show create table city; # 当前库下的city表建表语句show create table world.city; # world库下的建表语句 show privileges; # 数据库中所有权限show engines; # 数据库中支持的存储引擎show grants for root@'localhost'# 查询某个用户权限show charset; # 查询数据库字符集支持show collation; # 查询所有校对规则的支持show variables like '%trx%'; # 查询数据库参数 show status like 'com_%'; # 查询数据库的状态show proceslisst; # 查询所有会话信息show engine innodb status; # 查询innodb引擎相关的状态show binary logs; # 查询二进制日志文件信息show binlog events in 'xxx'; # 查看二进制日志事件show master status ; # 当前正在使用的二进制日志信息show slave status\G; # 查看主从状态相关信息show slave hosts; # 查看从库主机信息

通过系统表查询元数据mysql

权限表

userdb统计信息表

innodb_index_statsinnodb_table_stats

IS(information_schema)

对象属性tables columnsPS性能有关SYS性能有关tables 数据库中所有表元数据desc tables;table_schema 表所在的库table_name 表名engine 存储引擎table_rows 表行数avg_row_length 平均行长度index_length 索引长度data_free 碎片的大小作用统计资产例子1 ：每个业务库表的个数和名称select table_schema,count(*),group_concat(table_name)from information_schema.tableswhere table_schema not in ('mysql','information_schema','performance_schema','sys')group by table_schema;例子2： 统计每个库的数据量select table_schema,SUM(table_rows*avg_row_length+index_length) as total_Bfrom information_schema.tableswhere table_schema not in ('mysql','information_schema','performance_schema','sys')group by table_schema;例子3： 查询业务表中，所有不是InnoDB引擎的表select table_schema,table_namefrom information_schema.tableswhere table_schema not in ('mysql','information_schema','performance_schema','sys')and engine!='innodb';命令拼接例子1：批量修改表存储引擎alter table oldboy.t1 engine=innodb;select concat("alter table ",table_schema,".",table_name," engine=innodb;")from information_schema.tableswhere table_schema not in ('mysql','information_schema','performance_schema','sys')and engine!='innodb' into outfile '/tmp/alter.sql';columnsdelete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.truncate: DDL操作,对与表段中的数据页进行清空,速度快.(表中数据相关命令（DML）对表中的数据行进行增、删、改)建库规范：1、库名不能大写2、库名不能以数字开头3、建库时必须添加字符集4、库名要与业务有关5、生产中禁用普通用户的“drop databases”权限(数据库相关命令)建表规范：1、表名小写2. 不能是数字开头3. 注意字符集和存储引擎4. 表名和业务有关5. 选择合适的数据类型6. 每个列都要有注释7. 每个列设置为非空，无法保证非空，用0来填充。(表相关命令)