/* 查询数据库 ‘mammothcode’ 所有表注释 */
SELECT TABLE_NAME,TABLE_COMMENT FROM information_schema.TABLES WHERE table_schema='mammothcode';
/* 查询数据库 ‘mammothcode’ 下表 ‘t_adminuser’ 所有字段注释 */
SELECT COLUMN_NAME,column_comment FROM INFORMATION_SCHEMA.Columns WHERE table_name='t_adminuser' AND table_schema='mammothcode'
一次性查询数据库 "mammothcode" 下表注释以及对应表字段注释
SELECT t.TABLE_NAME,t.TABLE_COMMENT,c.COLUMN_NAME,c.COLUMN_TYPE,c.COLUMN_COMMENT FROM information_schema.TABLES t,INFORMATION_SCHEMA.Columns c WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`='mammothcode'
数据库索引查询
SELECT * FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = '数据库名';
# 设置当前用户密码
set password for root@localhost=password('Myroot123!');
# 重置自增值
alter table table_name auto_increment=1;
删除数据库表
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='django_demo';
查询数据库变量
# 查询是否大小写敏感
show variables like '%case%';
# 字符集设置
show VARIABLES like 'character%';
修改表注释
ALTER TABLE `table_name` COMMENT = '招采药品差比价分组表 ';
# 删除表
SELECT CONCAT('drop table ',table_name,';') FROM information_schema.`TABLES` WHERE table_schema='db_name';
# 修改表注释
ALTER TABLE `bidprcu_hi_list_b` COMMENT = '表注释';
# 删除表字段
ALTER TABLE `bidprcu_drug_diffpric_grp_x` DROP COLUMN `PROV_CODE`;
# 添加字段
ALTER TABLE `bidprcu_cert_b` ADD COLUMN `var` varchar(255) NOT NULL COMMENT 'ddd';
# 修改字段
ALTER TABLE `bidprcu_cert_b` MODIFY COLUMN `HOST_PK` varchar(10) NULL COMMENT '宿主主键';
# 修改字段默认值
alter table table_name alter column table_field_name set default "";
# 查看sql_mode
select @@global.sql_mode;
#查询最大连接数
show variables like '%max_connections%';
数据库查询结构
SELECT t.TABLE_NAME as 'table_name',t.TABLE_COMMENT as 'table_comment',c.COLUMN_NAME as 'column_name',
c.COLUMN_TYPE as 'column_type',c.COLUMN_COMMENT as 'column_comment',c.IS_NULLABLE as 'is_null',
c.data_type as 'data_type',c.column_key as 'pri_key'
FROM information_schema.TABLES t, INFORMATION_SCHEMA.Columns c
WHERE c.TABLE_NAME=t.TABLE_NAME AND t.`TABLE_SCHEMA`='db_table'
# 创建用户名
CREATE USER 'data_org_read'@'%' IDENTIFIED BY 'adsfverfdfgbbg';
# 添加权限 revoke 收回权限
GRANT SELECT ON databasename.* TO 'data_org_read'@'%';
flush privileges;
# 创建数据库表
CREATE DATABASE `stand_local_20200707` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
mysqldump 导出数据
# 导出全库数据库表结构和数据
mysqldump -h地址 -P端口 -u用戶名 -p密码 数据库名 > 脚本名;
# 导出全库表结构
mysqldump -h地址 -P端口 -u用戶名 -p密码 -d 数据库名 > 脚本名;
# 导出指定库表的数据（不含表结构）
mysqldump -h地址 -P端口 -u用戶名 -p密码 -t 数据库名 表名1 表名2 表名3 > 脚本名;
# 导出指定库表的数据
mysqldump -h地址 -P端口 -u用戶名 -p密码 -t 数据库名 表名1 --where="查询条件" > 脚本名;
# 限制导出的数据行数
mysqldump -uroot -p database --where="true limit 10000" > db.sql
# mysqldump 导出数据
--extended-insert=false true 存在重复数据时保错不执行，false 存在重复数据不报错执行
--add-drop-table 每个create语句之前增加一个 drop table
-C，--compress 是否开启压缩
-c, --complete-insert 使用完整的 insert 语句
-e, --extended-insert 使用全新的多行insert 语句
-n，--no-create-db 只导出数据，而不添加CREATE DATABASE 语句。
-t, --no-create-info 不写入创建表信息，只导出数据。
-d, --no-data 不写入表的任何信息，不导出任何数据
-w, --where='where-condition' 加入条件导出表数据。
-q，--quick 不缓冲查询，直接导出到标准输出，--skip-quick
--skip-add-locks 导入数据时跳过表加锁步骤 --add-locks 导入数据时表加锁
--comments 添加注释 --skip-comments 取消注释
--max_allowed_packet=10240
--tz-utc 输出带时区
--log-error 输出错误日志信息
--set-charset，设置字符集 --skip-set-charset 跳过字符集设置
# 命令行执行命令，则运行sql文件
source ***.sql 文件
# mysql 时间时区问题 时间
show VARIABLES like '%time_zone%';
# 查看最大的包数量通信缓冲区的最大长度为50M
show global variables like 'max_allowed_packet';
# 设置当前会话
set time_zone = '+8:00';
# 全局设置时区为东八区
set global time_zone = '+8:00';
flush privileges;
# | gzip 压缩文件，方便传输
mysqldump --set-gtid-purged=off db1 | gzip > all.sql.gz
# 导出文件示例
mysqldump -hhost_db -P3306 -u用户 -p密码 --extended-insert=false -c -e -t 数据库名 库表名> 文件名.sql
# 是否支持批量导出
--extended-insert=true
# 测试导入时间
time mysql -uroot -pxxx -e " source test.sql"
插入数据存在则更新
INSERT INTO TABLENAME (id,likes) VALUES (1,18) ON DUPLICATE KEY UPDATE likes=likes+1;
统计数据库表每个表的数据行数
select sum(table_rows),table_name from information_schema.tables where TABLE_SCHEMA = "数据库名" group by table_name order by table_rows desc ;
清空缓存
reset query cache;
mysql 日志
日志类型 写入日志的信息
1 错误日志 记录在启动，运行或停止mysqld时遇到的问题
2 通用查询日志 记录建立的客户端连接和执行的语句
3 二进制日志 记录更改数据的语句
4 中继日志 从复制主服务器接收的数据更改
5 慢查询日志 记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
6 DDL日志（元数据日志） 元数据操作由DDL语句执行
mysql binlog 是事务安全型的日志，主要目的是数据复制和数据恢复。启用binlog，通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项：开启binlog会有1%的性能损耗
开启binlog,mysql的 my.cnf 配置文件
server_id=1918
log_bin=mysql-bin
binlog_format=Mixed
expire_logs_days=30
常用命令
# 是否启用binlog日志
show variables like 'log_bin';
# 查看详细的日志配置信息
show global variables like '%log%';
# mysql数据存储目录
show variables like '%dir%';
# 查看binlog的目录
show global variables like "%log_bin%";
# 查看当前服务器使用的biglog文件及大小
show binary logs;
# 查看主服务器使用的biglog文件及大小
# 查看最新一个binlog日志文件名称和Position
show master status;
# 事件查询命令
# IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] ：偏移量(不指定就是0)
# row_count ：查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
show binlog events in 'mysql-bin.000003'
# 查看 binlog 内容
show binlog events;
# 设置binlog文件保存事件，过期删除，单位天
set global expire_log_days=3;
# 删除当前的binlog文件
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';
# 删除指定日志文件
purge master logs to 'master.000003';
创建数据库
CREATE DATABASE `database_name` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
SELECT concat(TABLE_COMMENT,' ',upper(TABLE_NAME)) FROM information_schema.TABLES WHERE table_schema='data_base' AND table_name = 'table_name'
执行计划
explain select update_time from tb_user where update_time = DATE_FORMAT(now(), '%Y-%m-%d')
mysql 字符串长度
基础类型
1. char （M个字节，0 <=M<= 255）
2. varchar （L+1个字节，其中L<=M 且 0 <=M<= 65535）
3. text （L+2个字节，其中L< 216）
char为定长，varchar，text为变长
数据的检索效率是：char > varchar > text
## 文本类型
5. TINYTEXT 最大长度是 255 (2^8 – 1) 个字符。
6. text 最大长度是 65535 (2^16 – 1) 个字符。
7. MEDIUMTEXT 最大长度是 16777215 (2^24 – 1) 个字符。
8. LONGTEXT 最大长度是 4294967295 (2^32 – 1) 个字符。
网友评论