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

灰太狼 2023-10-18 20:49 45阅读 0赞

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'@'%';

\# 查看定时任务开关  
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;