基本操作
–创建测试表
CREATE TABLE TEST(
ID INT;
);
ADD支持多列,CHANGE/DROP需要在每列前添加关键字,逗号隔开,'COLUMN’可有可无
添加多列
ALTER TABLE TEST ADD (C1 CHAR(1),C2 CHAR(1));
ALTER TABLE TEST ADD COLUMN (C1 CHAR(1),C2 CHAR(1));
ALTER TABLE TEST ADD C1 CHAR(1),ADD C2 CHAR(1);
新增字段-单个
ALTER TABLE PEOPLE ADD COLUMN NAME VARCHAR(100) DEFAULT NULL COMMENT '姓名'
新增字段-多个
ALTER TABLE IP_DOMAIN_LOCAL ADD (
`AUDIT_STATUS` INT(10) DEFAULT NULL COMMENT '审核状态:0:未审核;1:违规;2:正常;3:已通知;4:已处理',
`AUDIT_TIME` DATETIME DEFAULT NULL COMMENT '审核时间',
`AUDIT_USER` VARCHAR(50) DEFAULT NULL COMMENT '审核人',
`INFORM_USER` VARCHAR(50) DEFAULT NULL COMMENT '通知人',
`DISPOSE_USER` VARCHAR(50) DEFAULT NULL COMMENT '处理人'
)
修改多列
ALTER TABLE 表名 CHANGE 原字段名 新字段名 字段的类型;
ALTER TABLE TEST CHANGE C1 C3 CHAR(1),CHANGE C2 C4 CHAR(1); --正确
ALTER TABLE TEST CHANGE COLUMN C1 C3 CHAR(1),CHANGE COLUMN C2 C4 CHAR(1); --正确
--NAME关键字作为字段名,重命名需要加反引号(`)
ALTER TABLE TABLE_NAME CHANGE `NAME` FIELD_NAME VARCHAR(50);
ALTER TABLE TEST CHANGE (C1 C3 CHAR(1),C2 C4 CHAR(1)); --错误
修改表的字段类型
ALTER TABLE 表名 MODIFY COLUMN 字段名 字段类型定义;
删除多列
ALTER TABLE TEST DROP C1,DROP C2; --正确
ALTER TABLE TEST DROP COLUMN C1,DROP COLUMN C2; --正确
删除字段-单个
ALTER TABLE UC_ACCOUNT_HISTORY DROP COLUMN GESTURE;
删除字段-多个
ALTER TABLE UC_ACCOUNT_HISTORY
DROP COLUMN GESTURE,
DROP COLUMN GESTURE_TIME,
DROP COLUMN FINGER_PRINT,
DROP COLUMN FINGER_PRINT_TIME,
DROP COLUMN FACE_IMAGE_ID,
DROP COLUMN FACE_IMAGE_TIME,
DROP COLUMN TOUCH_TYPE;
ALTER TABLE TEST DROP C1,C2; --错误
ALTER TABLE TEST DROP (C1,C2); --错误
删除表的数据
DELETE FROM 表名;
TRUNCATE TABLE 表名;
表的重命名
ALTER TABLE 原表名 RENAME 现表名;
调整表的顺序:
ALTER TABLE `USER_MOVEMENT_LOG` CHANGE `GATEWAYID` `GATEWAYID` INT NOT NULL DEFAULT 0 AFTER REGIONID
修改表主键
ALTER TABLE uc_account_bak DROP PRIMARY KEY ,ADD PRIMARY KEY ( id );
关于mysql索引相关
MySQL 判断某个值是否纯数字
column REGEXP '[^0-9.]' = 0 //为0表示true,1表示false
eg:
select *,seller_user REGEXP '[^0-9.]' seller_user_type
from table_name
having seller_user_type = 0
Mysql 如何查询表名中包含某字段的表
Mysql链接数管理
运维拓展执行语句
-- 多少链接
show global status like 'Thread%';
-- 非交互式超时 如JDBC程序
show global variables like 'wait_timeout%';
-- 交互式超时 如数据库工具
show global variables like 'interactive_timeout%';
-- 连接数、并发数
show global variables like 'max_connections';
-- 缓存
show variables like 'query_cache%';
-- parser 词法语法
-- pre processor 语法词法正确但语义不对
-- optimize 数据库查询优化器的艺术原理解析与mysql性能.pdf
-- 性能优化之profiles sql诊断工具
show variables like '%prof%';
set profiling=1;
show profiles;
show profile for query 10;
-- 查询次数
show global status like 'com_select';
-- 性能优化之explain
explain select * from monitor_app_a where id in (2,3,4);
-- 详细json信息 cost成本
explain format=json select * from monitor_app_a where id in (2,3,4);
-- 性能优化之 optimizer_trace
show global variables like '%optimizer%';
show variables like 'optimizer_trace';
set optimizer_trace = 'enabled=on';
select user,host from mysql.user;
-- strps : join_preparation(sql准备阶段) - join_optimization(sql优化阶段) - join_execution(sql执行阶段)
-- 不知道路径先 show variables like 'datadir';
-- 注意客户端工具哪怕开启steps 也是null
select * from information_schema.OPTIMIZER_TRACE \G;
-- 存储引擎
show engines\G
-- 面试会问 特性比较
-- innodb 服务状态 在深入就是索引、事务、 缓存池 分别可一个小时
show engine innodb status;\G
-- 磁盘IO 块 页 大小
show global status like '%innodb_page_size%';
-- 缓存区 刷脏
show variables like 'innodb_buffer_pool%';
-- redo log 重做
show variables like 'innodb_log%';
-- \Data目录下的ib_logfile0 和 1固定大小,满则触发buffer_pool
-- 整体架构图 https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
show global status like '%undo_%';
show global variables like '%undo_%';
-- bin log ; my.cnf 中配置 log-bin=mysql-bin
show variables like '%bin%';
show variables like '%log_bin%';
-- 默认存放在 mysql/data目录下
show binary logs;
show binlog events in 'mysql-bin.000005';
-- 慢日志相关
show variables like 'slow_query%';
-- 查看虚拟机安装路径
show variables like 'datadir';
-- 登录mysql 查看my.conf所在路径
mysql --help | grep my.cnf
-- 多长时间为慢sql
show variables like 'long_query_time';
-- 到目录查看慢日志人间
vi slow.log
-- 通过mysqldumpslow解析慢日志
mysqldumpslow --help
-- 统计查询最慢的十条select语句 slow-query.log
mysqldumpslow -s t -t 5 -g 'select' /var/lib/mysql/slow-query.log