• Mysql 常用命令整理


    注意

    本文章所有 sql 均测试成功,基于 mysql 8.0.18 版本。
    // 2022-05-03
    因为触发器与储存过程总是听说影响mysql性能,所以没有收录。
    因为不同数据库的储存过程不同,当应用切换数据库的时候就会导致很多问题。

    DDL 数据定义语言

    -- 查看所有数据库
    show databases;
    -- 查看匹配的数据库
    show databases like '%mysql%';
    -- 查看创建数据库语句
    show create database test_mysql ;
    -- 进入数据库
    use test_mysql ;
    -- 查看当前使用的数据库
    select database();
    -- 创建数据库
    create database test_mysql;
    -- 创建数据库指定字符集和排序规则,如果这个数据库不存在
    create database if not exists test_mysql default character set utf8mb4 default collate utf8mb4_general_ci;
    -- 删除数据库
    drop database test_mysql;
    
    -- mysql 8.0 的存储引擎对比:https://blog.csdn.net/seagal890/article/details/82055219
    -- 查看所有支持的引擎(mysql8默认innodb)
    show engines;
    
    -- 查看当前数据库下面的所有数据表
    show tables;
    
    -- 查看建表语句
    show create table test_mysql ;
    -- 查看表结构
    desc test_mysql;
    -- 创建空表
    create table test_mysql;
    -- 索引参考 https://blog.csdn.net/qq_41573234/article/details/80250279
    -- 创建表并添加字段,设置索引以及设置表引擎与字符集,如果这个数据表不存在就执行
    create table if not exists test_mysql(
        -- 主键自增不为空
    	id int(10) primary key not null auto_increment,
    	name varchar(10) null default "",
    	title varchar(10) null default "",
    	age tinyint(1) not null
    	-- 指定主键(可选,此处设置上面就不需要设置主键,但需要指定自增!!)
    	-- primary key (id) 
    	-- 设置普通索引(可选)
    	-- title(title),
    	-- 指定唯一联合索引(可以指定单列/多列)
    	-- unique name_age (name, age),
    	-- 全文索引只能作用在  char、varchar、text、类型的字段上。创建全文索引需要使用  fulltext  参数进行约束。
    	-- fulltext key title(title)
    	-- 单列索引(可选)
    	-- index title(title),
    	-- 多列索引(可选)
    	-- index name_title(name,title)
    	-- 索引还可以指定字段大小,比如字段长20 可以设置 字段(10) 来索引字段前10长度
    	-- 还可以在索引设置 字段 asc 进行索引排序设置
    ) engine=innodb default charset=utf8mb4;
    -- 修改表引擎
    alter table test_mysql engine=innodb;
    -- 添加字段(最后)
    alter table remove_filed add name varchar(12);
    -- 指定位置添加字段 (表头)
    alter table remove_filed add id int(10) first;
    -- 指定位置添加字段(指定字段之后)
    alter table remove_filed add age int(1) after name;
    -- mysql给已经存在的主键添加自增:https://blog.csdn.net/weixin_42134712/article/details/110394441
    -- mysql auto_increment:主键自增长:http://c.biancheng.net/view/7624.html
    -- mysql 创建表并设置主键自增:https://blog.csdn.net/myxx520/article/details/5130249
    -- 主键自增
    alter table test_mysql  modify id int(10) not null auto_increment;
    -- 修改数据类型
    alter table remove_filed modify name int(10);
    -- 修改字段
    alter table remove_filed change id id varchar(12);
    -- 删除字段
    alter table remove_filed drop id;
    -- 删除表
    drop table test_mysql;
    -- 清空表(不建议,无法恢复)
    truncate test_mysql;
    
    -- mysql查看索引(show index):http://c.biancheng.net/view/7364.html
    -- 查看指定表的所有索引
    show index from test_mysql;
    -- alter 创建普通索引单列/多列
    alter table test_mysql add index name(name);
    -- alter 创建唯一索引单列/多列
    alter table test_mysql add unique index name(name);
    -- alter 创建主键索引
    alter table test_mysql add primary key(id);
    -- create 创建普通索引(只能普通索引或unique索引)单列/多列
    create index name on test_mysql (name);
    -- create 创建唯一索引(只能普通索引或unique索引)单列/多列
    create unique index name on test_mysql (name);
    -- mysql——删除索引:https://blog.csdn.net/qq_41573234/article/details/80328281
    -- 删除索引(删除指定列时,会自动的删除索引中的那列,当索引的所有列被删除,则索引自动删除)
    alter table test_mysql drop index name;
    -- 删除索引(删除指定列时,会自动的删除索引中的那列,当索引的所有列被删除,则索引自动删除)
    drop index name on test_mysql;
    
    -- mysql视图的作用(详细):https://blog.csdn.net/hxnlyw/article/details/81669964
    -- 创建视图(单表)
    create view view_name as select * from test_mysql ;
    -- 创建视图(指定字段名)
    create view view_name (id,title) as select id,title from test_mysql ;
    -- 创建视图(字段名可以设置别名,按照顺序映射,如title字段映射到真实表中的name字段)
    create view view_name (id,title) as select id,name from test_mysql ;
    -- 创建视图(多表)
    create view view_name as select test_mysql.id,test_mysql1.name from test_mysql join test_mysql1 on test_mysql.id = test_mysql1.id;
    -- 查看视图字段(与查看表语句一样)
    desc view_name ;
    -- 查看视图创建语句(与查看表创建一样)
    show create view view_name ;
    -- 修改视图
    alter view view_name (id,name) as select id,name from test_mysql ;
    -- 删除视图
    drop view view_name ;
    
    -- mysql 事务:https://www.runoob.com/mysql/mysql-transaction.html
    -- 事务(innodb引擎才支持事务)
    -- 事务处理可以用来维护数据库的完整性,保证成批的 sql 语句要么全部执行,要么全部不执行。
    -- 事务用来管理 insert,update,delete 语句
    -- mysql设置事务自动提交(开启和关闭):http://c.biancheng.net/view/7291.html#
    -- 查看事务自动提交模式
    show variables like 'autocommit';
    -- 开启事务
    begin;
    -- 回滚事务
    rollback;
    -- 提交事务
    commit;
    
    

    DML 数据操作语言

    -- mysql insert:插入数据(添加数据):http://c.biancheng.net/view/2574.html
    -- mysql dml(数据操作语言) 详解: https://www.knowledgedict.com/tutorial/mysql-dml.html
    -- 插入数据(单行)
    insert into test_mysql.test_mysql (name, age, title) values("王五",11,"员工");
    -- 插入数据(多行,逗号分割)
    insert into test_mysql.test_mysql (name, age, title) values("王五",11,"员工"),("王五",11,"员工"),("王五",11,"员工"),("王五",11,"员工");
    -- 插入数据(不指定字段名,但是数据顺序要对应数据库中的字段)
    insert into test_mysql.test_mysql values (11,"王五","员工",22);
    -- 快速复制表数据
    insert into test_mysql.test1_mysql select * from test_mysql ; 
    -- 快速复制表数据并指定条件和设置字段值
    insert into test_mysql.test_mysql (name, age, title) select name, age, "老板" from test_mysql.test_mysql where age > 10 ; 
    
    -- 更新字段(多个字段通过逗号分隔)
    update test_mysql.test_mysql set title = "老板" where id = 3;
    -- 更新字段(更新指定数量数据行于按照顺序进行更新)
    update test_mysql.test_mysql set title = "老板" where id = 3 order by id desc limit 2;
    -- 关联更新(只更新单表)
    update test_mysql,test1_mysql set test_mysql.title = test1_mysql.title where  test_mysql.name = test1_mysql.name;
    -- 关联更新(只更新单表,支持多种关联与子查询)
    update test_mysql left join test1_mysql on test_mysql.name = test1_mysql.name set test_mysql.age = test1_mysql.age;  
    -- 多表更新(更新多表,支持多种关联)
    update test_mysql left join test1_mysql on test_mysql.name = test1_mysql.name set test_mysql.age = test1_mysql.age, test1_mysql.title  = test_mysql .title ;  
    
    -- mysql关联删除:https://blog.csdn.net/weixin_30596023/article/details/95859484
    -- 删除数据
    delete from test_mysql.test_mysql where id = 3;
    -- 关联删除(只删除单表)
    delete test_mysql from test_mysql,test1_mysql where test_mysql.id = test1_mysql.id and test_mysql.id >10;
    -- 关联删除(删除多表)
    delete test_mysql,test1_mysql from test_mysql right join test1_mysql on test_mysql.id = test1_mysql.id where test1_mysql.id < 10;
    

    DQL 数据查询语言

    -- 单表查询,查询数据量
    select count(1) from test_mysql;
    -- 单表查询,查询指定字段最大值
    select max(age) from test_mysql;
    -- 单表查询,查询指定字段和
    select sum(age) from test_mysql;
    -- 单表查询,查询指定字段平均值
    select avg(age) from test_mysql;
    
    -- 单表查询
    select * from test_mysql;
    -- 单表查询,指定查询数量 x,y 只填写一个默认为 0,y
    select id,name from test_mysql limit 1;
    -- 单表查询,按照 id 排序,默认 asc
    select id,name from test_mysql order by id desc;
    -- 单表查询, group by
    -- select id,name,age from test_mysql where age > 20 group by name;
    select count(id),name from test_mysql group by name;
    -- sql having 子句:https://www.w3school.com.cn/sql/sql_having.asp
    -- 单表查询,通过 having 对聚合字段进行 where
    -- select max(age) as max_age,title from test_mysql where max_age > 20 group by title;
    select max(age) as max_age,title from test_mysql group by title having max_age > 20 ;
    
    -- 单表查询,别名
    select name as username from test_mysql;
    -- 单表查询,去重,支持多个字段
    select distinct name from test_mysql;
    -- 单表查询,指定字段条件
    select id,name from test_mysql where id > 20;
    -- 单表查询,区间查询,也可以是时间区间
    select id,name from test_mysql where id between 10 and 20;
    -- 单表查询,in
    select id,name from test_mysql where id in (10,20);
    -- 单表查询,模糊查询 % 匹配任意字符,_ 匹配一个字符
    select id,name from test_mysql where name like "%_五%";
    -- 单表查询,is null,is not null
    select id,name from test_mysql where name is null;
    
    -- 子查询 作为单独表查询 test_mysql 并将子查询结果别名为 new_test_mysql
    select * from (select * from test_mysql ) as new_test_mysql  where age > 10;
    -- 子查询 作为条件集查询 test_mysql 并将子查询结果别名为 new_test_mysql
    select * from test_mysql  where id in (select id from test_mysql );
    
    -- 关联查询 inner left right 
    select test_mysql.id,test1_mysql.name from test_mysql left join test1_mysql on test1_mysql.id = test_mysql.id;
    -- 关联查询(表名设置别名) inner left right 
    select t.id,t1.name from test_mysql as t left join test1_mysql as t1 on t1.id = t.id;
    -- sql数据库多表查询之 where和INNER JOIN:https://blog.csdn.net/weixin_44839345/article/details/88847921
    -- where 实现关联(不建议的操作)
    -- select t.id,t1.name from test_mysql as t,test1_mysql as t1 where t.id = t1.id;
    

    DCL 数据控制语言

    -- 查看所有用户 https://blog.csdn.net/team39/article/details/89413015
    select * from mysql.user;
    
    -- mysql用户管理:添加用户、授权、删除用户 https://www.cnblogs.com/chanshuyi/p/mysql_user_mng.html
    -- mysql 创建用户命令-grant:https://www.cnblogs.com/58top/p/7799500.html
    -- 用户管理
    -- 创建用户指定访问域名以及设置密码
    -- create user 'xiaqiuchu'@'127.0.0.1' identified by '12345678';
    -- create user 'xiaqiuchu'@'%' identified by '12345678';
    create user 'xiaqiuchu'@'localhost' identified by '12345678';
    -- 创建用户并授予指定权限,以及设置密码
    grant all privileges on * . * to 'xiaqiuchu'@'localhost' identified by ‘123456’ ;
    -- mysql 8.0修改密码:https://www.cnblogs.com/chloneda/p/12449819.html
    -- 更改指定用户密码
    alter user 'xiaqiuchu'@'localhost' identified with mysql_native_password by '00000000';
    -- 更改指定用户 host 信息,如:远程访问,本地访问等
    update mysql.user set host="127.0.0.1" where user="xiaqiuchu" and host="localhost";
    -- mysql基础:dcl语句(包含权限信息设置):https://www.cnblogs.com/rohn/p/11722515.html
    -- 授权可以很细粒度,不止权限,还有链接信息很多很多。
    -- 给用户授权 test_mysql 数据库中 test_mysql 表的查询与插入权限(可以多个用户与多个权限)
    -- grant select, insert on test_mysql.test_mysql to 'xiaqiuchu'@'localhost';
    -- 给用户授权所有数据库与所有表的所有权限
    -- grant all on *.* to 'xiaqiuchu'@'localhost';
    -- mysql:mysql数据库修改用户权限(远程访问权限、操作权限):https://blog.csdn.net/m0_37482190/article/details/86624096
    -- 给用户添加授权/更新权限 test_mysql 数据库中所有表的 查询与更新权限(如果是当前用户需要刷新 flush privileges;)
    grant select,update on test_mysql.* to 'xiaqiuchu'@'localhost';
    -- 查看指定用户权限
    show grants for 'xiaqiuchu'@'localhost';
    -- 删除用户
    drop user 'xiaqiuchu'@'127.0.0.1';
    -- 修改信息后刷新缓存/权限/刷新数据库
    flush privileges;
    
    

    其他

    -- 分析sql执行
    explain select id,name from test_mysql;
    
    
    -- mysql查看数据库相关信息: https://www.cnblogs.com/jiangxiaobo/p/6110647.html
    -- MySql中的变量定义:https://www.cnblogs.com/gavin110-lgy/p/5772577.html
    -- 查看全局变量
    show global variables;
    -- Mysql show variables系统变量详解:https://www.cnblogs.com/cqliyongqiang/p/9515813.html
    -- 查看会话变量
    show session variables; -- show variables;
    -- 查看指定变量(必须知道变量全名) global 全局、session 会话
    select @@global.sql_mode;
    select @@session.sql_mode;
    -- 设置/使用用户变量 :https://blog.csdn.net/jiang1986829/article/details/47313861
    set @a=1;
    -- 获取用户变量
    select @a;
    -- 查看指定变量:连接数设置
    -- show session variables like '%max_connections%';
    -- show variables like '%max_connections%';
    show global variables like '%max_connections%';
    -- 查看指定变量:最大连接数
    show global status like 'Max_used_connections';
    -- 查看字符集 服务器级别、数据库级别、表级别、列级别
    show variables like 'character%';
    -- 查看大小写规则设置
    show variables like "%lower_case_table_names%";
    -- 查看模式:宽松模式 vs 严格模式
    show variables like "%sql_mode%";
    
    
    -- 查看进程
    show processlist;
    -- 关闭指定进程
    kill 进程ID号
     -- 查看是否锁表
    SHOW ENGINE INNODB STATUS;
    -- 退出命令行
    quit;
    
    
    -- mysql中profile的使用:https://www.jianshu.com/p/8f12163337e8
    -- 查看是否开启保存最近n条 sql 查询耗时及相关配置(不建议线上环境使用,默认关闭,使用完毕后也建议关闭)
    show variables like '%profiling%';
    -- 查看是否开启保存最近n条sql执行时间 1开启 0关闭
    select @@profiling;
    -- 打开sql执行时间记录
    set profiling=1;
    -- 关闭sql执行时间记录
    set profiling=0;
    -- 查看最近n条 sql 执行时间
    show profiles;
    
    
    

    推荐学习

    mysql 函数:http://c.biancheng.net/mysql/function/
    mysql 锁:https://www.cnblogs.com/jojop/p/13982679.html
    EXPLAIN用法和结果分析 :https://blog.csdn.net/why15732625998/article/details/80388236
    MySQL索引背后的数据结构及算法原理: http://blog.codinglabs.org/articles/theory-of-mysql-index.html
    MySQL支持的索引类型(B-Tree索引、hash索引):https://blog.csdn.net/z_ryan/article/details/82322418
    mysqldump导出数据:https://www.jianshu.com/p/c3d8366326c1

  • 相关阅读:
    JAVA 异常
    JAVA 接口的基本语法
    JAVA 访问权限
    Linux shell 函数应用示例02
    Linux shell 函数应用示例01
    Linux shell while循环语句
    Linux shell 中断循环语句
    Linux shell for循环结构
    测试用例基本概念
    软件测试原则
  • 原文地址:https://www.cnblogs.com/xiaqiuchu/p/15955695.html
Copyright © 2020-2023  润新知