MySql使用sql新建库 表 注释 授予用户super权限

创建数据库

create database `test_db`
default character set utf8 collate utf8_general_ci;

use test_db;

create table user (
    id int auto_increment primary key comment '主键自增',
    nick_name varchar(64) comment '昵称',
    phone char(11) comment '手机号',
    address varchar(128) comment '地址',
    sex int(1) default '1' comment '1.男2.女',
    email varchar(256) comment '邮箱',
    login_name varchar(64) comment '登陆名',
    password char(32) comment '密码',
    create_date timestamp default current_timestamp comment '创建时间',
    update_date timestamp default current_timestamp comment '修改时间',
    create_user int comment '创建人',
    update_user int comment '修改人',
    create_name varchar(64) comment '创建用户名',
    update_name varchar(64) comment '修改用户名',
    status int(1) comment '1.正常2.锁定0.作废'
) comment = '用户表'

-- 创建数据库，用户，并且赋权
create database IF NOT EXISTS  ke_dev character set 'utf8' DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;

create user 'ke_dev'@'%' IDENTIFIED by 'ke_dev';

GRANT all ON ke_dev.* TO 'ke_dev'@'%';

-- 设置自增id起始值

ALTER TABLE user AUTO_INCREMENT=10000;

# 查看定时任务开关
show variables like '%event_sche%';

# 打开定时任务
set global event_scheduler=1;

select date_format(DATE_SUB( NOW( ), INTERVAL 7 DAY), '%Y%m%d') from dual

创建存储过程

CREATE PROCEDURE `proc_del_bizlog`()
begin
delete from test_bizlog where log_time <=date_format(DATE_SUB( NOW( ), INTERVAL 7 DAY), '%Y%m%d');
END

-- 查看当前是否已开启事件计划（调度器）有3种方法：
SHOW VARIABLES LIKE 'event_scheduler';
 SHOW PROCESSLIST;

SELECT * FROM information_schema.EVENTS;

alter event event_del_log on completion preserve enable; -- 开启定时任务
alter event event_del_log on completion preserve disable; -- 关闭定时任务

创建定时任务

create event event_del_log
on schedule EVERY 3 minute
on COMPLETION PRESERVE DISABLE
do call proc_del_bizlog();

# 创建存储过程
DELIMITER //
create procedure proc_del_bizlog()
begin
delete from test_bizlog where log_time <=date_format(DATE_SUB( NOW( ), INTERVAL 7 DAY), '%Y%m%d');
END//
delimiter ;

# 创建定时任务
create event test_proce
on schedule every 10 second
on completion preserve disable
do call down_goods();

SELECT event_name,event_definition,interval_value,interval_field,status FROM information_schema.EVENTS;

alter event test_proce on completion preserve enable; //开启定时任务
alter event test_proce on completion preserve disable;//关闭定时任务

on schedule every 1 second //每秒执行1次
on schedule every 2 minute //每两分钟执行1次
on schedule every 3 day //每3天执行1次

on schedule at current_timestamp()+interval 5 day //5天后执行
on schedule at current_timestamp()+interval 10 minute //10分钟后执行
on schedule at '2016-10-01 21:50:00' //在2016年10月1日，晚上9点50执行

on schedule every 1 day starts current_timestamp()+interval 5 day ends current_timestamp()+interval 1 month //5天后开始每天# # # -- -- 都执行执行到下个月底

on schedule every 1 day ends current_timestamp()+interval 5 day //从现在起每天执行，执行5天

# 查看mysql用户权限
SHOW GRANTS FOR 'test'

# 创建定时任务需要授予用户super权限
grant super  on *.* to 'test'@'%' IDENTIFIED by 'test';

revoke super  on *.* from 'test'@'%';

flush privileges;

SELECT * from mysql.user where user = 'test';

GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'%' WITH GRANT OPTION

创建视图

CREATE
 VIEW v_image_org_user_album AS
 select ob.oid,ob.uid,u.name,p.pname,p.price,od.quantity from order_basic ob,order_details od,user u,product p
 where ob.oid = od.oid and ob.uid = u.uid and od.pid = p.pid

select * from v_image_org_user_album;

增删改查常用操作

CREATE TABLE `customer` (
  `mid` char(5) NOT NULL COMMENT '成员ID（主键）',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `birth` date DEFAULT NULL COMMENT '生日',
  `sex` char(6) DEFAULT NULL COMMENT '性别：女：1 男：0',
  PRIMARY KEY (`mid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user` (
  `uid` char(6) NOT NULL COMMENT '成员ID（主键）',
  `zip` char(8) DEFAULT NULL COMMENT '邮编',
  `address` varchar(255) DEFAULT NULL COMMENT '住址',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `order_basic` (
  `oid` char(15) NOT NULL COMMENT '订单ID（主键）',
  `odate` date DEFAULT NULL COMMENT '发单时期',
  `uid` char(6) DEFAULT NULL COMMENT '用户ID',
  `memo` varchar(255) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `order_details` (
  `oid` char(15) NOT NULL COMMENT '订单ID（主键）',
  `pid` char(9) NOT NULL COMMENT '产品ID(主键)',
  `quantity`int DEFAULT NULL COMMENT '数量',
  PRIMARY KEY (`oid`,`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `product` (
  `pid` char(9) NOT NULL COMMENT '产品ID（主键）',
  `pname` varchar(255) DEFAULT NULL COMMENT '产品名',
  `price` float(10,1) DEFAULT NULL COMMENT '价格',
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO `test`.`user` (`uid`, `zip`, `address`, `name`) VALUES ('100001', '10000000', '北京市朝阳区110号', '张三');
INSERT INTO `test`.`user` (`uid`, `zip`, `address`, `name`) VALUES ('100002', '10000010', '北京市海淀区111号', '李四');
INSERT INTO `test`.`user` (`uid`, `zip`, `address`, `name`) VALUES ('100003', '10000020', '北京市海淀区112号', '王五');
INSERT INTO `test`.`product` (`pid`, `pname`, `price`) VALUES ('100000001', '电脑', 4999);
INSERT INTO `test`.`product` (`pid`, `pname`, `price`) VALUES ('100000002', '手机', 2999);
INSERT INTO `test`.`product` (`pid`, `pname`, `price`) VALUES ('100000003', '显示器', 2999);
INSERT INTO `test`.`product` (`pid`, `pname`, `price`) VALUES ('100000004', 'CPU', 855);
INSERT INTO `test`.`order_basic` (`oid`, `odate`, `uid`, `memo`) VALUES ('100000000000001', '2023-04-22', '100001', '订单1备注');
INSERT INTO `test`.`order_basic` (`oid`, `odate`, `uid`, `memo`) VALUES ('100000000000002', '2023-04-22', '100002', '订单2备注');
INSERT INTO `test`.`order_details` (`oid`, `pid`, `quantity`) VALUES ('100000000000001', '100000001', 2);
INSERT INTO `test`.`order_details` (`oid`, `pid`, `quantity`) VALUES ('100000000000001', '100000002', 1);
INSERT INTO `test`.`order_details` (`oid`, `pid`, `quantity`) VALUES ('100000000000002', '100000003', 1);
INSERT INTO `test`.`order_details` (`oid`, `pid`, `quantity`) VALUES ('100000000000002', '100000004', 1);

 INSERT INTO `test`.`customer` (`mid`, `name`, `sex`) VALUES ('10001', '张三', '1');
 INSERT INTO `test`.`customer` (`mid`, `name`, `sex`) VALUES ('10002', '李四', '2');
 
 select * from customer where sex = 1 and birth is null;
 
 select od.oid,sum(price) from (
select od.oid,p.price*od.quantity as price from order_details od left join product p on p.pid = od.pid
) as od
group by od.oid;

select * from product where price > (
select avg(price) from product);

INSERT INTO `test`.`order_details` (`oid`, `pid`, `quantity`) VALUES ('100000000000001', '100000003', 1);
INSERT INTO `test`.`order_details` (`oid`, `pid`, `quantity`) VALUES ('100000000000001', '100000004', 2);

select p.pname,u.name from product p, user u

-- 查询订购过所有订单的用户
select ob.uid from order_details od left join  order_basic ob on od.oid = ob.oid
group by ob.uid
having count(od.pid) = (select count(*) from product)

select * from user where uid not in (
select uid from order_basic group by uid)

-- Mysql导入导出命令（如果没设置环境变量，需要在mysql的bin目录下执行，不带表名则导出整个库）

mysqldump -uXX账号XX -h 127.0.0.1 -pXX密码XX XX库名XX XX表名XX > XX导出文件名XX.sql

-- 导入命令

mysql -uXXXX -h 127.0.0.1 -p XX库名XX < XXXX.sql

查看mysql版本

SELECT VERSION();

-- JAVA问题修复：Public Key Retrieval is not allowed

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

连接数相关

-- 查看所有连接进程
SHOW PROCESSLIST;

show full processlist 

-- 查看已经连接的线程数
show status like 'Threads_connected';

-- 查看最大连接数
SHOW VARIABLES LIKE 'max_connections';

show status;
--修改连接数
-- 方法一：修改配置文件。推荐方法一 进入MySQL安装目录 打开MySQL配置文件 my.ini 或 my.cnf查找 max_connections=100 修改为 max_connections=1000 服务里重起MySQL即可.
-- 方法二：命令行修改。
set GLOBAL max_connections=200;

常见问题处理

Lock wait timeout exceeded; try restarting transaction

mysql 提示[40001][1205] Lock wait timeout exceeded; try restarting transaction

意味着一个事务在等待获取锁的时候超时了。这通常是由于某个事务持有锁的时间过长，而其他事务需要这个锁来继续执行，但由于等待时间过长，所以被中断。

使用 SHOW ENGINE INNODB STATUS 命令来查看死锁信息

• innodb_trx 当前运行的所有事务
• innodb_locks 当前出现的锁
• innodb_lock_waits 锁等待的对应关系

select * from information_schema.innodb_trx

可以查找执行线程用时比较久sql

• SELECT * from information_schema.`PROCESSLIST` WHERE Time > 1000 ORDER BY TIME desc;

• kill [PROCESSID]

这里说下博主自己的处理方法，造成这个问题的原因猜测是数据量过大，导致insert或update超时，原本想通过修改表名，新建该表来优化操作，不过后续修改表名的语句也一直loading，后续不得以创建了临时表来恢复此业务，大家有更好的处理方法也可以评论区一起交流~