一。基本语句
docker安装:docker run -d -p3306:3306 -eMYSQL_ROOT_PASSWORD=root mysql:5.7
id自增:
表:`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
PRIMARY KEY (`id`),
KEY `core_wire_serial_number` (`core_wire_serial_number`)
实体类加注解
@Id
@Column(name = "id")
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
时间当前时间 `updated_at` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '修改数据时间',
//1.查看版本
select VERSION();
//2.数据库增删改查
show databases default character set utf8;
create database day15
drop database day15;
//3.表增删改查
show tables;
drop table student;
alter table student add column gender varchar(2); //1)添加字段
alter table student modify column remark varchar(100); //2)修改字段类型
alter table student change column remark remark2 varchar(100) //3)修改字段名称
alter table student drop column a,drop column b; //4)删除字段
alter table student rename to student2; //5)修改表名
二。高级语句
1.判断语句
//case
select case status when 1 then '成功' when 2 then '失败' else '其他' end from user
case c.epm_type when 1 THEN '直流快充' when 2 THEN '交流快充' else '交流慢充' END as epm_type
//if
select username,if(sex=1,'男','女') as sex from user;
//ELT
ELT(N,str1,str2,str3,...)
如果 N = 1,返回 str1,如果N = 2,返回 str2,等等。如果 N 小于 1 或大于参数的数量,返回 NULL。
2.拼接语句
concat("满",pps_ps_full_amount,"减",pps_ps_subtract_amount)
3统计语句
一。先确定需要哪些表的哪些字段
如何确定需要哪些表和字段? 举例下拉框选择充电站,显示直流充电桩有多少个,交流充电桩都有多少个
1.充电站的id,2.直流交流(充电桩表的电量类型),3.充电站和充电桩的关系表
二。根据第一步的分析正常关联查询出数据
三。改版第二步为统计
如何改版?
1.几种不同类型就写几条count count没有数据为0,而sum没有数据为null
2.group by 选择的站点
-- 正常查需要的表和字段
SELECT
b.oss_id, -- 站点ID,
b.oss_code, -- 站点编码,
case c.epm_type when 1 THEN '直流快充' when 2 THEN '交流快充' else '交流慢充' END as epm_type -- 电桩类型,
FROM
org_station_electric_pile a INNER JOIN org_station_set b ON b.oss_id = a.osep_station_id INNER JOIN electric_pile_management c ON c.epm_id = a.osep_electric_pile_id
-- 改版成统计
SELECT
b.oss_id, -- 站点ID,
b.oss_code, -- 站点编码,
count( case c.epm_type when 1 then 1 end ) as 直流,
count( case c.epm_type when 1 then 0 else 1 end ) as 交流
FROM
org_station_electric_pile a INNER JOIN org_station_set b ON b.oss_id = a.osep_station_id INNER JOIN electric_pile_management c ON c.epm_id = a.osep_electric_pile_id
GROUP BY oss_id
-- 另外一种格式 由宇哥演变
select
(select count(pps_id) from promotion_push_set where pps_id = a.pum_promotion_push_id) as total,
(select count(pps_id) from promotion_push_set where pps_id = a.pum_promotion_push_id and pps_valid_end_time-pps_valid_start_time <=3 ) as overdueSoonCount
from promotion_use_manage a
4.时间判断
to_days(now()) - to_days(`pps_receive_end_time`) >0
5.分页查询 Limit offset
一个参数(表示取的数量)
select* from article LIMIT 3 表示直接取前三条数据
两个参数 (跳过的数量,取的数量)
select* from article LIMIT 1,3 就是跳过1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数
offset和limit组合使用
select * from article LIMIT 3 OFFSET 1 表示跳过第1条数据,从第2条数据开始取,取3条数据,也就是取2,3,4三条数据
三。优化
1.面试答如何优化
优化效果和成本
硬件>系统配置>数据库表结构>sql及索引
1.硬件性能优化
2.mysql服务器优化,由Windows改为Linux
3.sql语句本身优化
4.反范式化设计数据库
5.索引优化
6.分库分表 主从表
2.三大范式
1.字段都只有单一属性
2.每个表都只有一个业务主键
3.在范式二的基础上,相处非主键对主键的依赖
3.慢查询
慢查询:配置文件,把一些耗费时间长的语句记录在日志中
慢查询分析工具:mysqlDumpSlow,根据耗费时间排序
1.概念说明: 找出执行语句长的sql语句
2.慢查询工具位置 安装目录下/bin/mysqldumpslow.pl
3.慢查询配置文件
位置 安装目录下/my.ini
配置 # General and Slow logging.语句下的内容
4.慢查询工具的执行语句
perl mysqldumpslow.pl -s t -t 5 C:\ProgramData\MySQL\MySQL Server 5.7\Data\LAPTOP-G6VG7U9O-slow.log
4.索引
1.什么是索引?
答:索引是帮助快速获取数据 排好序的数据结构
2.都有哪些数据结构?
答: 二叉树 红黑树 B树 B+Tree,mqsql索引使用了B+Tree的数据结构
3.各个数据结构特点
二叉树:右边大于左边的数
红黑树:又叫二叉平衡树,当右边的值很多时就会旋转出一个节点 (一个节点存放一个数)
B树:
B加数:扩展节点,一个节点可以存放多个数(以此降低数的高度)
1.索引:
普通索引:一个索引只包含一个列
唯一索引:也是普通索引,但索引列的值必须唯一,允许有空值
复合索引:一个索引包含多个列
聚簇索引: 索引和数据存放在一起的
非聚簇索引:索引和数据分开存放
2.建表时加索引
3.索引语句
查看索引: show index from table_name
创建索引:create [unique] index indexNme on mytable(columnname(length));
alter table 表名 add [unique] index [indexName] on (columnname(length))
删除索引: drop index [indexName] on mytable;
4.判断sql语句是否使用到了索引
explain + sql语句 key字段为null表示没有使用到,key_len越大表示索引使用效果越好
5.十个SQL语句优化策略
葵花宝典:
全值匹配我最爱,最左前缀要遵循
索引列上少计算,范围之后全失效
覆盖索引不写*, like百分写最后
varchar引号不可丢,不等空值还有or
5.控制字段唯一
PRIMARY KEY (`od_id`),
UNIQUE KEY `od_code` (`od_code`) USING BTREE
四。mysql的引擎
InnoDB / MyIsam / Memory / Blackhole(黑洞引擎)
五。系统演变
读多写少 用缓存
写多读少 用对列
两个日期相差天数
DATEDIFF(now(),ppe_create_time) > 90
把秒转为00:00:00 时分秒格式
concat(
concat(if(char_length(b.ocod_real_time div 3600) >=2,b.ocod_real_time div 3600,concat('0',b.ocod_real_time div 3600) ),':'),
concat(if(char_length(b.ocod_real_time mod 3600 div 60)>=2 , b.ocod_real_time mod 3600 div 60 ,concat('0',b.ocod_real_time mod 3600 div 60)),':'),
if(char_length(b.ocod_real_time mod 60)>=2 ,b.ocod_real_time mod 60 ,concat('0',b.ocod_real_time mod 60))
) as realTime,
SELECT concat(concat(10000 div 3600,'时'),concat(10000 mod 3600 div 60,'分'),concat(10000 mod 60,'秒')) as times FROM DUAL