Mysql入门及高级用法

数据库管理

创建数据库

CREATE DATABASE IF NOT EXISTS `demo_db` default charset utf8 COLLATE utf8_general_ci;

建表

create table t_test
(
id INT(11),
name VARCHAR(25),
deptId INT(11),
salary FLOAT
);

字段维护

增加字段

-- 在指定字段之后添加字段
alter table iot_tower_data add column driver_id_card_no varchar(18) COMMENT '驾驶员身份证号' after driver_name; 

用户管理

创建用户

-- demo
CREATE USER demo IDENTIFIED  WITH mysql_native_password BY 'demo@jrsoft.cc';
grant all privileges on `demo_db`.* to 'demo'@'%';
grant all on `demo_db`.* to 'demo'@'%';
grant execute on `demo_db`.* to 'demo'@'%';
-- 如果只给部分权限,建议只给部分权限
grant select, insert, update, delete, create, alter, execute on `demo_db`.* to 'demo'@'%';
-- 取消用户的drop权限，避免删库，注意必须重启mysql或者重新建立连接才会生效
revoke drop on `demo_db`.* from 'demo'@'%'; 
FLUSH PRIVILEGES;

查询用户

SELECT User, Host FROM mysql.user;

删除用户

drop user demo;

登录

mysql -u root -p

高级查询

时间维度

参考：MYsql 查询 查询当前月份的数据

1、查询当天的数据
select * from order_1 where 
to_days(order_time)=to_days(now());

2、查询昨天的数据
select * from order_1 where 
to_days(now())-to_days(order_time) =1;

3、查询最近7天的数据（包括今天一共7天）
select * from order_1 where 
date_sub(curdate(),interval 7 day) < date(order_time);

4、查询最近30天的数据（包括今天一共30天）
select * from order_1 where 
date_sub(curdate(),interval 30 day) < date(order_time);

5、查询当月（本月）的数据
select * from order_1 where 
date_format(curdate(),'%y%m') = date_format(order_time,'%y%m');

6、查询上个月的数据
select * from order_1 where
period_diff(date_format(now(),'%Y%m'),date_format(order_time,'%Y%m'))=1;

7、查询本季度的数据
select * from order_1 where
quarter(order_time) = quarter(now());

8、查询上季度的数据
select * from order_1 where
quarter(order_time) =quarter(date_sub(curdate(),interval 1 quarter));

9、查询当年（今年）的数据
select * from order_1 where
year(order_time) = year(now());

10、查询去年的数据
select * from order_1 where
year(order_time) = year(date_sub(now(),interval 1 year));

11、查询当前这周的数据 
select * from order_1 where
yearweek(date_format(order_time, '%Y-%m-%d'),1) = yearweek(now(),1);
 
12、查询上周的数据
select * from order_1 where
yearweek(date_format(order_time, '%Y-%m-%d'),1) = yearweek(now(),1)-1;
 
13、查询距离当前现在6个月的数据
select * from order_1 where 
order_time between date_sub(now(),interval 6 month) and now();
查询当前这周的数据
SELECT name,submittime FROM enterprise WHEREYEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());

查询上周的数据
SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;

查询当前月份的数据
select name,submittime from enterprise   where date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)

查询距离当前现在6个月的数据
select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

查询上个月的数据
select name,submittime from enterprise   where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)

select * from `user` where DATE_FORMAT(pudate,’%Y%m’) = DATE_FORMAT(CURDATE(),’%Y%m’) ;

select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = WEEKOFYEAR(now())

select * from user  where MONTH(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = MONTH(now())

select * from [user] where YEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = YEAR(now())
and MONTH(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = MONTH(now())

select * from [user]  where pudate between 上月最后一天 and 下月第一天 

TIMESTAMPDIFF

SELECT TIMESTAMPDIFF(SECOND,'2012-10-01','2013-01-13'); # 8985600
SELECT TIMESTAMPDIFF(MINUTE,'2012-10-01','2013-01-13'); # 149760
SELECT TIMESTAMPDIFF(HOUR,'2012-10-01','2013-01-13'); # 2496
SELECT TIMESTAMPDIFF(DAY,'2012-10-01','2013-01-13'); # 104
SELECT TIMESTAMPDIFF(WEEK,'2012-10-01','2013-01-13'); # 14
SELECT TIMESTAMPDIFF(MONTH,'2012-10-01','2013-01-13'); # 3
SELECT TIMESTAMPDIFF(QUARTER,'2012-10-01','2013-01-13'); # 1
SELECT TIMESTAMPDIFF(YEAR,'2012-10-01','2013-01-13'); # 0

DATEDIFF

datediff
 　　语法：传入两个日期参数，比较DAY天数，第一个参数减去第二个参数的天数值。

SELECT DATEDIFF('2013-01-13','2012-10-01'); # 104

高效分页查询-利用延迟关联或者子查询优化超多分页场景。

说明：MySQL 并不是跳过 offset 行，而是取 offset+N 行，然后返回放弃前 offset 行，返回 N 行，那当offset 特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行 SQL改写。
正例：先快速定位需要获取的 id 段，然后再关联：

SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 LIMIT 100000,20 ) as t2 where t1.id=t2.id
SELECT t1.* FROM 表 1 as t1, (select id from 表 1 where 条件 order by createTime desc LIMIT 100000,20 ) as t2 where t1.id=t2.id order by createTime desc 

if exists用法

创建表

CREATE TABLE IF NOT EXISTS KaTeX parse error: Expected group after '_' at position 16: {templateTable}_̲{suffix} LIKE ${templateTable}

如何不存在则插入

## date_sub(curdate(),interval 1 day) == '2023-03-10'表示前一天
INSERT INTO iot_xx (id, project_id,date,create_time)
        select UUID(),#{projectId},date_sub(curdate(),interval 1 day),now() from (
                                                                                      select ROUND(IFNULL(AVG(quality),0),0)quality  from iot_xx where project_id = #{projectId} and to_days(now())-to_days(create_time) =1
                                                                                    )a
        WHERE NOT EXISTS (
                SELECT * FROM iot_xx WHERE project_id = #{projectId} AND date = date_sub(curdate(),interval 1 day)
        );
## 第二种
insert into t_user (id, name, age)
select #{id,jdbcType=BIGINT}, #{name,jdbcType=VARCHAR}, #{age,jdbcType=INTEGER}
from dual where not exists(
    select id from t_user where name = #{name,jdbcType=VARCHAR}
);

配置主从同步开启binlog

docker下配置

在宿主机的/usr/local/mysql/conf中的my.cnf后面添加如下

# 指定binlog文件名
log-bin=/var/lib/mysql/mysql-heima-bin
# 指定需要记录的库
binlog-do-db=heima

重启mysql

docker restart mysql

创建同步用户

create user canal@'%' IDENTIFIED with mysql_native_password by 'canal';
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT, SUPER ON *.* TO 'canal'@'%';
FLUSH PRIVILEGES;

测试设置是否成功，在mysql控制台，输入如下命令

show master status;

常见场景

查询重复

select id from test where  timestamp_ in  (
				select timestamp_ from test group by device_code,timestamp_ having count(*)>1
				) 
				and id not in (
					select min(id) from test group by device_code,timestamp_ having count(*)>1
				)

删除重复

delete from  test where id in 
(
		select a.id from(
			select id from test where  timestamp_ in  (
				select timestamp_ from test group by device_code,timestamp_ having count(*)>1
				) 
				and id not in (
					select min(id) from test group by device_code,timestamp_ having count(*)>1
				)
		)a
)

docker安装

安装5.7

拉取镜像

docker pull mysql:5.7

创建容器

docker run -it --name mysql57 -d -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 

启动

docker run -p 3316:3306 --name mysql57 \
-v /usr/local/mysql57/conf:/etc/mysql/conf.d \
-v /usr/local/mysql57/logs:/logs \
-v /usr/local/mysql57/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=slx@edevp.cn \
docker.io/library/mysql:5.7 \
--lower_case_table_names=1

拷贝配置文件

# 拷贝my.cnf
docker cp mysql57:/etc/mysql/my.cnf /usr/local/mysql57/conf/
# 再次启动
docker restart mysql57