• 本地连接到数据库

C:\Program Files\MySQL\MySQL Server 5.7\bin>mysql -u root -p Enter password: **

  • 创建数据库

create database xiexie

  • 查询数据库版本

select Version();

  • 显示时间

SELECT *,FROM_UNIXTIME(createTime/1000, ‘%Y-%m-%d %H:%i:%s’)

  • 查询注意

SELECT * FROM ibd_cm.task_job where jobCode = “201705150944-1” 如果把双引号去掉会查不出来

  • 执行计划 explain +任意sql语句

explain SELECT * FROM ibd_cm.task_job where jobCode = “201705150944-1”

  • 数据库时间

SELECT LEFT(FROM_UNIXTIME(UNIX_TIMESTAMP(‘2017-06-26 00：00：00’)), 10) date, (UNIX_TIMESTAMP(‘2017-06-26 00：00：00’)), (UNIX_TIMESTAMP(‘2017-06-26’)), FROM_UNIXTIME(‘1498406400’);

  • 数据库函数 SELECT CONCAT(id, ‘-‘, jobCode) ‘工单Id+工单号’, if(planFinishTime >= finishTime, ‘正常’, if(left(planFinishTime, 7) = left(finishTime, 7), ‘新增延迟’, ‘历史延迟’)) ‘交付状态’, if (0>1,0,1) ‘test’ FROM task_job;

  • 数据库命令 show table status; – 显示当前库所有表的状态 show processlist; – 显示连接数 select * from information_schema.processlist where db = ‘ibd_cm’;

  • 索引 where语句的=后面值需要和存储类型保持一致(字符串或者数字),否则索引会无效.

  • 日期函数 DATE_ADD(‘2017-07-27 10:00:00’, INTERVAL - 600 MINUTE)

  • 下载文件 sz -e ibd_cm_20170818_1736.sql.gz

  • 组内数据合并

select id, group_concat(mainFieldName) from gather_product_info where jobId = 342749810820006912 group by hbaseId; 267623 良品,不良,工序进度,废品 267630 废品,工序进度,不良,良品 267640 废品,工序进度,不良,良品 267641 良品,工序进度 267643 良品,工序进度

  • mysql运行测试

1)A join B 和 B join A 运行先后顺序是否相同 : 顺序不同,一般情况下业务场景两种方式不能转换 2)Between 是否使用索引 :
对于range类型，选择性小于17%了，就会走全表扫描，而非range。 MySQL只有对以下操作符才使用索引：<，<=，=，>，>=，BETWEEN，IN，以及xx%的LIKE。 3)A join B 会不会在联结的时候使用B的索引,如果联结项有多个,如何合理使用索引 对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的。

  • 创建表的语句 show create table sys.test;

  • 表状态展示 show table status;

SQL语句优化 不需要考虑的因素:存储引擎,数据吞吐量,锁机制,CPU带宽等外部物理因素,更新删除等操作,基准测试,服务器总体负荷 需要考虑的因素:取出数据的流程和效率(磁盘IO内容,数据运算)

  • 索引注意 select jobId, procedureId from gather_data_info where jobId = 361896323175656192 没有使用索引 select jobId, procedureId from gather_data_info where jobId = ‘361896323175656192’ 使用索引

  • unique类似与primary

特别是当索引是组合索引的时候

如果组合是唯一的,并且需要根据唯一值查询

那么可以把他们设为主键primary或者unique

原来一个1G大小的表

使用index索引时,经常导致慢查询,耗时2秒左右,遇忙时更有达到5秒的

改用unique之后,查询耗时在0.0003秒 基本可以忽略不计

  • if 语句 if(b.procedureName IS NULL ,c.templateName ,b.procedureName)

  • 表之间交换数据 INSERT ignore INTO job_procedure_status (jobId, procedureId, procStatus) (select jobId, procedureId, procStatus from procedure_process_state )

  • 数据库导出

mysqldump -h rm-bp1lny29wth884d69.mysql.rds.aliyuncs.com -u user01 -p -d ibd_cm > /tmp/sqlxx.sql

  • String 不为空 SELECT * FROM gather_data_info WHERE serialNumber<>’’ 或者 NOT ISNULL(serialNumber)

  • 日期保存 MySql的时间类型有 Java中与之对应的时间类型 date java.sql.Date Datetime java.sql.Timestamp/java.util.Date Timestamp java.sql.Timestamp Time java.sql.Time Year java.sql.Date

  • 查询数据库总行数 select table_name,table_rows from information_schema.tables where TABLE_SCHEMA = ‘ibd_cm’ order by table_rows desc;

数据库总条数: 491w. 1G硬盘占用,单表最大100w 每天以 1%的速度增加