Warning

todo…

Tip

终端中sql语句后面都可以加上\G (在;分号前面),这样会显示一行一行,而不是表格.

1 常用函数

-- 获取一个uuid
select uuid();
-- 当前时间
select now();

2 配置相关

2.1 查看mysql相关信息

-- 进入mysql控制台后 (mysql -uroot .. 命令连接后的界面) 输入 \s 后回车
\s
--------------
mysql  Ver 5.7.41-0ubuntu0.20.04.2 for Linux on x86_64 ((Ubuntu))

Connection id:      3
Current database:   test
Current pager:      stdout
Using outfile:      ''
Using delimiter:    ;
Server version:     5.7.41 MySQL Community Server (GPL)
Protocol version:   10
Connection:     192.168.1.100 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    latin1
Conn.  characterset:    latin1
TCP port:       3306
Binary data as:     Hexadecimal
Uptime:         43 min 23 sec
--------------

2.2 查看会话与全局变量

-- 进入mysql后 查看会话变量
select @@autocommit;
-- 查看全局
select @@global.autocommit;
-- 我们平常设置变量 ,下面的只对当前会话有效
-- 其实是省略了set session autocommit=1; 中的session
set autocommit=1;

-- 这样设置后,我们查看select @@autocommit 发现还是1,
-- global只对以后的会话有效,对当前无效,当前的还是保持原来的
set global autocommit=0;
-- 模糊查看变量
show variables like "%log%";

2.3 查看字符集

show character set like '%utf%';
+---------+------------------+--------------------+--------+
| Charset | Description      | Default collation  | Maxlen |
+---------+------------------+--------------------+--------+
| utf8    | UTF-8 Unicode    | utf8_general_ci    |      3 |
| utf8mb4 | UTF-8 Unicode    | utf8mb4_general_ci |      4 |
| utf16   | UTF-16 Unicode   | utf16_general_ci   |      4 |
| utf16le | UTF-16LE Unicode | utf16le_general_ci |      4 |
| utf32   | UTF-32 Unicode   | utf32_general_ci   |      4 |
+---------+------------------+--------------------+--------+

2.4 隔离级别

show variables like '%tx%';

3 权限设置

-- 角色,mysql中没有角色,用的一种叫代理的方式  这个只有5.7支持
-- 官方叫 role like 机制
show grants;
create user 'dba'@'127.0.0.1';
create user 'xyz'@'::1';
grant proxy on 'dba'@'127.0.0.1' to 'xyz'@'::1';
grant select on mysql.* to 'dba'@'127.0.0.1';

-- 查看该用户的权限
show grants for 'dba'@'127.0.0.1';

-- 可以查看到
select * from mysql.proxies_priv;

4 database

-- mysql中database与schema 是一模一样的.
-- 其他数据库可能是一个database有多个schema.
-- mysql估计是为了和其他数据库保持一致,好理解所以有了schema
-- 下面2个操作结果一样
show databases;
show schemas;

-- 选择database,进行查询时,需要先选择你的db
use database_name;

-- 每一个数据库对应data dir目录里的是一个文件夹.但是information_schema 不在里面
use information_schema;
-- 我们发现这些个表的引擎都是memory,所以..没有
show table status;

5 table

5.1 查看创建信息

-- 查看当前db下的所有表信息
show table status;
-- 某个表的一些信息
show table status like 'table_name';
-- 显示创建表的语句
show create table table_name;

5.2 索引

-- 删除主键
alter table table_name drop primary key;
-- 删除一般索引
alter table table_name drop index index_name;

5.3 临时表

Caution
  1. 创建的临时表是基于会话的,你另外开一个会话,你看不到你创建的临时表
  2. 临时表可以与实体表重名
  3. 在有重名的情况下, 你select * from a实际查询的是临时表,而不是实体表
  • 创建临时表
create temporary table  tmp_a (id int);
查看临时表的默认存储引擎
show variables like '%default_tmp%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| default_tmp_storage_engine | InnoDB |
+----------------------------+--------+
查看 临时表的状态
-- 可以看到系统里有创建了多少个临时表
show global status like '%tmp_tables%';
-- 当前会话里的情况
show status like '%tmp_tables%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Created_tmp_tables | 6     |
+--------------------+-------+
表结构存放目录
show variables like 'tmpdir';
-- 如果你是在mysql所在服务器 用的mysql命令连接可以直接使用如下命令查看
-- 可以看到.frm的文件
-- 退出当前mysql 会话, 这个表空间文件会删除的.
system ls /tmp
临时表表结构文件命名方式
  • 临时表的表结构文件.frm 命名方式是 #sql{进程id}_{线程id}_序列号
  • 线程id 通过 show processlist 的ID列获得.
表空间的存放目录
show variables like "%data%";
+---------------------------------------+------------------------+
| Variable_name                         | Value                  |
+---------------------------------------+------------------------+
2| datadir                               | /var/lib/mysql/        |
| innodb_data_file_path                 | ibdata1:12M:autoextend |
| innodb_data_home_dir                  |                        |
1| innodb_temp_data_file_path            | ibtmp1:12M:autoextend  |
+---------------------------------------+------------------------+
1
创建的文件名是 ibtmp1,放在 innodb_data_home_dir 目录下
2
如果 innodb_data_home_dir 是空的, 就放到datadir 下 
-- 可以看到
system ls /var/lib/mysql/ibtmp1

5.4 增

5.5 删

5.6 改

5.7 查

6 奇技

6.1 导出csv,带字段名

# 默认导出没有标题. 所以用union 来完成效果
mysql -uroot -proot test -e "select 'a','b','c' from dual union select a,b,c from test  into outfile '/tmp/x.csv' CHARACTER SET gbk fields terminated by ',' optionally enclosed by '\"' lines terminated by '\r\n'; "
Tip
  • optionally enclosed by ‘"’ 这样 字段内容有, 号的 就不会被分成2个了.
  • secure_file_priv=/tmp/ 这个参数影响 导出文件的位置,以及是否可导出
  • 如果 上面的参数为空, 去 /tmp/xxxxx-mariadb/tmp 看看

7 null

注意
  1. 任何值与null进行=比较,得到的都是null
  2. 用is 来判断
select null=1;
select 1=null;
select null in (1,null);
Back to top