首页 > 数据库 >PL/SQL 基础用法

PL/SQL 基础用法

时间:2023-10-30 17:45:38浏览次数:31  
标签:show emp1 用法 PL SQL table tb id select

PL/SQL
基础用法

数据库相关

show databases;
show table status;
show open tables;
show create database leetcode;
create database my_test;
show databases;
drop database my_test;

数据库引擎

select distinct engine
from information_schema.TABLES;

create database learn;
use learn;
show tables;
drop table if exists tb_emp1;
create table tb_emp1
(
id int(11) comment '员工编号',
name varchar(25) comment '员工名称',
depId int(11) comment '部门',
salary float comment '工资'
);
describe tb_emp1;

/·栏位 就是columns
加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
· 删去一个栏位: DROP "栏位 1"
· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"
/
alter table tb_emp1
drop column salary;
alter table tb_emp1
add column salary int(11) not null;

alter table tb_emp1
modify salary int(11);
alter table tb_emp1
modify salary int(11);
alter table tb_emp1
change depId depId int(2) not null;

显示所有约束

select *
from information_schema.TABLE_CONSTRAINTS
where TABLE_NAME = 'tb_emp1';
show create table tb_emp1;

charu

insert into tb_emp1(id, name, depId, salary)
values (1, 'xsj', 1, 0);
select *
from tb_emp1;
describe tb_emp1;

添加约束

alter table tb_emp1
add constraint tb_id primary key (id);
show create table tb_emp1;
update tb_emp1
set salary=2000;

alter table tb_emp1
add constraint tb_name_unique unique (name);
alter table tb_emp1
add constraint ch_salary check ( salary > 1800 );
alter table tb_emp1
drop check ch_salary;
alter table tb_emp1
add check (salary < 90000);

alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)

/唯一性是index
not null 是约束
check 是check
/

alter table tb_emp1
drop index tb_name_unique;

alter table tb_emp1
change salary salary float not null;

删除主键

alter table tb_emp1
drop primary key;

alter table tb_emp1
drop check tb_emp1_chk_1;

放在最后边

show index from tb_emp1;
show create table tb_emp1;

desc tb_emp1;

命令行里可以

show create table tb_emp1 \G;

alter table tb_emp1 rename to tb1;
show tables;
alter table tb1
change name name2 varchar(40) unique;
alter table tb1
drop index tb_name_unique;

放在最后边

show index from tb1;
show create table tb1;
desc tb1;

在某个字段后边插入一个新列

alter table tb1
add column name varchar(12) after name2;
alter table tb1
drop column name;
desc tb1;
show engines;

外键

create table if not exists tclass
(
gradeId int(3) not null auto_increment primary key,
name varchar(128) not null unique
);
insert into tclass
values (null, '1-1班'),
(null, '1-2班'),
(null, '1-3班');
select *
from tclass;
create table if not exists student
(
id int(5) not null primary key auto_increment,
name varchar(128) not null,
class int(3) not null,
age int(3),
constraint check ( age > 0 ),
key classId (class),
constraint f_class_id foreign key (class) references tclass (gradeId)
);
insert into student
values (null, 'llz', 3, 1);
insert into student
values (null, 'xsj', 1, 28);
insert into student
values (null, 'xt', 2, 25);
select *
from student;
alter table student
modify id int(11) unsigned auto_increment;

show index from student;
show create table student;
alter table student
drop foreign key f_class_id;
alter table student
drop index classId;

create index classId on student (class);

alter table student
add constraint f_class_id foreign key (class) references tclass (gradeId)
on delete cascade on update no action;
select *
from student;
select *
from tclass;

禁用外键

set foreign_key_checks = 0;
set foreign_key_checks = 1;
show tables;
select *
from tb1;
alter table tb1
modify salary float(4, 2);

update tb1
set tb1.salary=20.1272
where id = 1;

alter table tb1
drop check ch_salary;

alter table tb1
modify tax decimal(5, 4) default 0;
alter table tb1
modify depId int(2);
alter table tb1
modify id int(11) auto_increment;
select *
from tb1;
desc tb1;
insert into tb1 (id, name2, depid, salary, tax)
values (null, 'bjj2', 9999, 32.11, 2.343);

alter table tb1
add column y year;
select *
from tb1;
update tb1
set y='0'
where id = 1;
update tb1
set y=21
where id = 2;
update tb1
set y='21'
where id = 4;
update tb1
set y='2177'
where id = 5;
alter table tb1
add column date datetime;

select now();
insert into tb1 (date)
values (datediff(now(), 1));

select *
from tb1;

alter table tb1
add column time time;
select *
from tb1
where id = 8;
insert into tb1 (time)
values ('041123');

desc tb1;
select curdate();
select current_time();
select current_timestamp();
select now();

select pow(2, 8);

create table if not exists chars
(
id int not null primary key auto_increment,
c1 char(2),
c2 varchar(255),
t1 tinytext,
t2 text(65535)
);
desc chars;
insert into chars
values (null, '12', 'sldjkjdddd', 'ss', 'mediumtext');
select *
from chars;
select length(c1)
from chars;

show tables;
desc tb1;
alter table tb1
modify date datetime default now();
insert into tb1(tax)
values (2.3);
select *
from tb1;

select mod(10, 3);
select mod(-10, 3);
select rem(10, -3);
select -10 % 3;
drop function rem;
create function rem(x int, y int)
returns int
DETERMINISTIC
BEGIN
return x - sign(x / y) * floor(abs(x / y)) * y;
end;
select rem(10, 3);
select rem(-16, 3);
select mod(-16, -3);

运算符

select null = null n, 1 = 1 a, 1 = 2 c, 1 <=> 1 d, null <=> null f;
select 1 <= 2, 'goo' < 'goo0';
select isnull(null);
select power(1.05, 10);

select least(1, 2, 3, 4, -3);
select greatest(2, 3, 4, 422);
select null and null and 1, 2 and 1, 1 and 0;
select 1 or null, null or null, 0 or null, 0 or 1;
select 1 XOr 1, 2 xor 1, 0 xor 3, null xor 1, null xor null;
select binary 'a' = 'A', 'a' = 'A';

select 4 && 8, -2 || NULL, NULL XOR 0, 0 XOR 1, !2;

1,1,1,1,1,0,1

select 36 > 27, 15 >= 8, 40 < 50, 15 <= 15, NULL <=> NULL, NULL <=> 1, 5 <=> 5;

select rand(), rand(10);
select round(1.23), round(122.33332, -1);
select round(1.23), round(-1.3532, 2);

show function code rem ;

show variables like '%with-debug%';
select degrees(pi());
select radians(90);
select round(pi(), 25);
select length('我么') xx;
select char_length('我们') x;

为什么varcahr(6)的可以插入6个汉字,或者六个字符。

show tables;
select length(cl)
from chars;
alter table chars
add column cl varchar(6);
update chars
set cl='我们都是好孩'
where id = 1;
update chars
set cl='123456'
where id = 1;
select lpad('xsj', 1, '-');
select trim('ss' from 'ssswess');

select trim('-' from repeat('123-', 3));
select strcmp('xy', 'xz');
select locate('j', 'xsj');
select instr('xsj', 'j');
select from_unixtime(123);
select unix_timestamp();
select extract(year_month from now());
select date_add(now(), interval 1 second);
select date_add(now(), interval 1 minute);
select date_add(now(), interval 1 day);
select date_add(now(), interval 1 month);
select addtime(now(), 1), now();
select ifnull(2, 1);
select version();
select connection_id();
show processlist;
show full processlist;
show schemas;
select user();
select charset('');
select last_insert_id();
select md5('123456');
select md5('123456');
select sha('123456');
select sha('123456');
select sha2('123456', 224);
select conv(10, 10, 8);

lock

select get_lock('lock1', 10);
select is_free_lock('lock1');
select is_free_lock('lock2');
select is_used_lock('lock1');
select is_used_lock('lock2');

select release_lock('lock1');
show tables;

select benchmark(12, 'select * from salary');
select benchmark(10000, sha(rand()));

转换

select cast(100 as char(2));
select cast('2020-01-01' as date);
select current_user();

函数

select 18 / 5, 19 % 5, mod(19, 5), degrees(pi() / 4), pow(9, 4), round(pi(), 2);
select char_length('hello'), length('he llo'), length('我们') we;

字符串

select substr('Nice to meet you!', 9, 4),
repeat('Cheer', 3),
reverse('voo'),
concat_ws(' ', 'we', 'are', 'good');

日期

show processlist;
select SHA('MYSQL');
select MD5('MYSQL'), conv(100, 10, 8), format(3.21222, 4);

循环

with recursive cte(n) as (
select 1
union
select n + 1
from cte
where n < 8
)
select *
from cte;
show tables;
use leetcode;

类似connect by

select *
from t_areainfo;
with recursive father as (
select id, name, parentId from t_areainfo where id = 26
union all
select g.id, g.name, g.parentId from t_areainfo g
inner join father f on f.parentId = g.id
)
select group_concat( name order by id separator '-') from father;
用户权限

查询

select * from sales for update ;
select * from sales ;
show databases ;
show tables;

插入

insert into sales (id, year, country, product, profit)
values (20,2020,'china','computer',100);

distinct ,先distinct 然后在limit

select * from sales order by id limit 1,1;
select distinct year from sales limit 1,1;
select distinct year from sales limit 1 offset 1;

uuid

select upper(replace(uuid(),'-',''));
select uuid();

if null

select if(product='Phone','mobile',product) from sales;

设置id

update sales set ids=uuid();

dense,窗口函数

select country,profit, dense_rank() over (partition by country order by profit) rk,id
from sales;

select country,profit, row_number() over (partition by country order by profit) rk,id
from sales;

select country,profit, rank() over (partition by country order by profit) rk,id
from sales;

新建用户

select * from mysql.user;
select Host,User,authentication_string from mysql.user ;

use mysql;

use leetcode;

create user xsj@'localhost' identified by '123456';
create user xsj2@'localhost' identified by '123456';
create user xsj3@'localhost' identified by '123456';

flush privileges ;

直接插入数据库的方式新建用户,密码没有加密

insert into mysql.user(Host,User,authentication_string,ssl_cipher,x509_issuer,x509_subject)
values ('localhost','shengjie','123456','','','');
flush privileges ;

SHOW GRANTS FOR 'root'@'localhost';

drop user xsj2@'localhost';
drop user xsj3@'localhost';
drop user shengjie@'localhost';
flush privileges;

给用户赋予权限

grant insert,update,select ON leetcode.*
To xsj@'localhost' identified by 'shengjie';
show databases ;
grant select, insert, update, delete on . to shengjie@'%' identified by '123456';

select host,User,authentication_string
from mysql.user where User like 'x%';

create user a1 identified by '123',a2 identified by '123';
drop user a1,a2;

create user xsj@'localhost' identified by '123456';
flush privileges;

with grant option 可以将自己的权限赋予别

grant select,update,insert on leetcode.* to xsj@'localhost' with grant option ;

这个是只能在本地连接把

create user leet@'localhost' identified by 'leet';
grant select,update,insert on leetcode.* to leet@'localhost' ;

修改密码

set password for xsj@'localhost' = 'shengjie';
flush privileges ;

set password for leet@'localhost' ='123456';
select host,User,authentication_string from mysql.user;

显示用户权限

show grants for leet@'localhost';
show grants for root@'%';

grant update,select,insert,delete on leetcode.* to leet@'localhost' with grant option ;
show grants for leet@'localhost';

删除权限

revoke update on leetcode.* from leet@'localhost';
flush privileges;
revoke all privileges on leetcode.* from leet@'localhost';
revoke grant option on leetcode.* from leet@'localhost';
revoke all privileges ,grant option from leet@'localhost';

时间日期

select now() +10;
select date_add(now(),interval 7 year );
select date_add(now(),interval 7 hour );
select datediff(date_add(now(),interval 7 day ),now());
select year(now());
select date (now());
select timestamp (now());
select time(now());
select date_format(now(),'%Y-%m-%d %H:%i:%S');

group,group_concat

select country,group_concat(product,'-',year) from sales group by country with rollup ;
select country,group_concat(product,'-',year) from sales group by country ;

select ifnull(year,1),ifnull(country,1),group_concat(year),group_concat(country) from sales group by year,country with rollup;

视图 关键词as

create view sales_group3 as select year,country,group_concat(country) from sales group by year, country;

select * from sales_group3;

describe sales_group;
show tables ;

drop view sales_group3;
select * from sales_group;

普通索引

create index sales_id on sales(id);

唯一索引

CREATE UNIQUE INDEX index_id ON sales(id);

create index country_4 on sales(country(4));

显示索引

show index from sales;
drop index index_id on sales;

EXPLAIN select * from sales where country like 'c%';
EXPLAIN select * from sales where id=2;
EXPLAIN select now();
select now();
SHOW VARIABLES LIKE 'character%';
SHOW VARIABLES LIKE 'collation_server';
select Host,User from mysql.user;

触发器

create trigger ids_trigger
before insert on sales
for each ROW
BEGIN
SET new.ids=REPLACE(UUID(),'-','');
END;

备份

use seasons;
show tables;
use leetcode;

select * from sales;

select * from sales into outfile 'my.txt';
SHOW VARIABLES LIKE 'log_error';
SHOW VARIABLES LIKE 'log_bin';
SHOW binary logs;
SHOW VARIABLES LIKE '%general%';
数据库约束

数据库相关

show databases ;
show table status ;
show open tables ;
show create database leetcode;
create database my_test;
show databases;
drop database my_test;

数据库引擎

select distinct engine from information_schema.TABLES;

create database learn;
use learn;
show tables;
drop table if exists tb_emp1;
create table tb_emp1(
id int(11) comment '员工编号',
name varchar(25) comment '员工名称',
depId int(11) comment '部门',
salary float comment '工资');
describe tb_emp1;

/·栏位 就是columns
加一个栏位: ADD "栏位 1" "栏位 1 资料种类"
· 删去一个栏位: DROP "栏位 1"
· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"
· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"
/
alter table tb_emp1 drop column salary;
alter table tb_emp1 add column salary int(11) not null;

alter table tb_emp1 modify salary int(11);
alter table tb_emp1 modify salary int(11);
alter table tb_emp1 change depId depId int(2) not null ;

显示所有约束

select * from information_schema.TABLE_CONSTRAINTS where TABLE_NAME='tb_emp1';
show create table tb_emp1;

charu

insert into tb_emp1(id,name,depId,salary)values (1,'xsj',1,0);
select * from tb_emp1;
describe tb_emp1;

添加约束

alter table tb_emp1 add constraint tb_id primary key (id);
show create table tb_emp1;
update tb_emp1 set salary=2000;

alter table tb_emp1 add constraint tb_name_unique unique(name) ;
alter table tb_emp1 add constraint ch_salary check ( salary>1800 );
alter table tb_emp1 drop check ch_salary;
alter table tb_emp1 add check(salary<90000);

alter table 表名 add constraint 约束名 foreign key(关联字段) references 主表(关联字段)

/唯一性是index
not null 是约束
check 是check
/

alter table tb_emp1 drop index tb_name_unique;

alter table tb_emp1 change salary salary float not null ;

删除主键

alter table tb_emp1 drop primary key ;

alter table tb_emp1 drop check tb_emp1_chk_1;

放在最后边

show index from tb_emp1;
show create table tb_emp1;
存储过程

正则表达式

show tables;
create table if not exists reg
(
name varchar(100) not null
);
select *
from reg;
/insert into reg values ('xxs');
insert into reg values ('xxs,a,b,d,c,,ds,d,');
insert into reg values ('xxs238sd');
insert into reg values ('37943');
insert into reg values ('2.31');
insert into reg values ('2.31d');
insert into reg values ('2.2.1');
/

2***d结尾的。

select *
from reg
where regexp_like(name, '^2.*d$');

不等于1,2,3

select *
from reg
where regexp_like(name, '[^123]');

select *
from reg
where regexp_like(name, '[^0-9.]');
select *
from reg
where regexp_like(name, '[a-z]+');
select *
from reg
where regexp_like(name, ',');
select *
from reg
where regexp_like(name, '2\..*?');
select name, regexp_like(name, '(2{1})')
from reg;
select *
from reg
where regexp_like(name, '[0-9]{2,3}');

select name, regexp_instr(name, '[,]+', 1, 2)
from reg;

select name, regexp_replace(name, 'x', 'a')
from reg;

select 'Bn' REGEXP '^Ba*n';

select 'pix' REGEXP '^(pi|apa)$';

-> 0(表示不匹配)

select substr('xsjsss', 3, 2);

select name
from reg;

select *
from reg
where regexp_like(name, '2|,');
select name, regexp_substr(name, '[^,]+', 1, 1)
from reg;
select *
from reg;

select *
from reg
where regexp_like(name, '.2.d$');

循环查找

DROP TABLE IF EXISTS t_areainfo;
CREATE TABLE t_areainfo
(
id int(11) NOT null AUTO_INCREMENT,
level int(11) DEFAULT '0',
name varchar(255) DEFAULT '0',
parentId int(11) DEFAULT '0',
status int(11) DEFAULT '0',
PRIMARY KEY (id)
) ENGINE = InnoDB
AUTO_INCREMENT = 65
DEFAULT CHARSET = utf8;

select *
from t_areainfo
where parentId = 4
or id = 4
order by id, parentId;

通过当前id获取父类

DROP FUNCTION IF EXISTS queryChildrenAreaInfo;

CREATE FUNCTION queryChildrenAreaInfo(areaId INT)
RETURNS VARCHAR(4000)
DETERMINISTIC
BEGIN
DECLARE sTemp VARCHAR(4000);
DECLARE sTempChd VARCHAR(4000);
SET sTemp = '$';
SET sTempChd = cast(areaId as char);
WHILE sTempChd is not NULL
DO
SET sTemp = CONCAT(sTemp, ',', sTempChd);
SELECT group_concat(id) INTO sTempChd FROM t_areainfo where FIND_IN_SET(parentId, sTempChd) > 0;
END WHILE;
return sTemp;
END;

select concat('a',',','b');

select cast(1 as char);

创建存储过程。

select queryChildrenAreaInfo(4);

select *
from t_areainfo
where FIND_IN_SET(id, queryChildrenAreaInfo(4));

drop function if exists test;
create function test()
returns varchar(100)
DETERMINISTIC
begin
return (select group_concat(name) name from reg where name like 'x%');
end;

select test() name;

创建一个存储函数

drop function if exists test_pro;
create function test_pro()
returns int
DETERMINISTIC
BEGIN
declare ids int default 1;
select id into ids from sales where id = 2;
set ids = 10;
return ids;
end;
select test_pro() ids;

loop循环

drop function if exists test_add;

create function test_add(max int)
returns int
DETERMINISTIC
BEGIN
declare num int default 0;
declare sum int default 0;
add_num:
loop
set num = num + 1;
set sum = sum + num;
if num >= max then
leave add_num;
end if;
end loop add_num;
return sum;
end;

select test_add(12);

for 循环

create function findParent(chdId int)
returns varchar(4000)
DETERMINISTIC
BEGIN
declare allId varchar(4000);
declare nowIds varchar(4000);
set allId = '$';
set nowIds = cast(chdId as char);
while nowIds is not null
do
set allId = concat(allId, ',', nowIds);
select group_concat(parentId) into nowIds from t_areainfo where find_in_set(id, nowIds);
end while;
return allid;
end;
select findParent(26);
select * from t_areainfo;
select replace(group_concat(name),',','-') addr from t_areainfo where find_in_set(id,findParent(5))
order by id;
其他
show tables;
select '1' where find_in_set('c','a,b,n');
insert into reg values (0);
select * from reg;
select * from reg where name!='0';
select cast('2.2.3' as float );
select locate('a','bsada');
select replace(' sldj sd ',' ','');
select CHAR_LENGTH('sld中文');
select character_length('sl中午');
select concat('a','b');
select concat_ws('-','a','b','c','d');
select format(2932472.13,1);
select round(323423.323212,4);
select left('foijfldsfj',3);
select concat('a',space(10),'w');
select curdate();
select curtime();
select current_timestamp();
select adddate(now(),-7);
select addtime(now(),100);
select dayofweek(now());
select dayofweek(date('2020-10-10'));
select date('2020-01-10 12:12:12');
select sysdate();
select CONNECTION_ID();
select database();
select current_user();
select LAST_INSERT_ID();

标准格式化

select minute(date_format('2020-01-01 23:10:12','%Y-%m-%d %T'));
select date_format('2020-01-01 23:10:12','%Y-%m-%d %T');
select date_format(now(),'%Y-%m-%d %r');
1.mod 和rem的区别。

首先先说fix和floor取整的区别。floor取整时向负无穷取,fix向零取。也就是当为正数时两个没有区别。负数时,fix比floor大1。

再说mod=floor(x/y),rem=fix(x/y)。

一、left join

1、left join on

on条件是在生成临时表时使用的条件,它不管on中的条件是否为真,都会返回左边表中的记录。

2、left join on and

(1)如果and语句是对左表进行过滤的,那么不管真假都不起任何作用。

(2)如果and语句是对右表过滤的,那么左表所有记录都返回,右表筛选以后再与左表连接返回。

3、left join on where

where条件是在临时表生成好后,再对临时表进行过滤的条件。这时已经没有left join的含义(必须返回左边表的记录)了。

(1)此时相当于inner join on

(2)此时on后的条件用来生成左右表关联的临时表,where后的条件对临时表中的记录进行过滤。

4、inner join on and 和 inner join on where

无区别,不管是对左表还是右表进行筛选,on and 和 on where都会对生成的临时表进行过滤。

二、group by

select xxx_name, count(*) as counter
from table_y
where where_condition -- 分组前过滤条件
group by xxx_name --分组名称
having having_condition -- 对分组后的数据进行过滤,因为分组后的数据都是与分组有关的。
order by zzz
分布式
1.启动mysql 容器
docker run --name mysql-lates --restart always
-v /etc/timezone:/etc/timezone
-v /etc/localtime:/etc/localtime
-p 3306:3306 -p 33060:33060
-e MYSQL_ROOT_PASSWORD=shengjie
-e MYSQL_ROOT_HOST='%'
-e LANG=C.UTF-8 -d mysql:latest
创建数据库

create database seasons
创建数据表

是否需要用powerdesigner设计呢?
2.用idea 连接mysql
其实在idea 里写代码挺方便的。
3.数据库设置
3.1 设置时区
查看linux时区
date -R

设置时区
tzselect
然后一步步选择就好了。

设置好tzselect后,一定要重写/etc/localtime
rm /etc/localtime
ln -sf /usr/share/zoneinfo/Asia/Shanghai /etc/localtime

进入容器

docker exec -it mysql8 bash

到目标目录

cd /etc/mysql

追加到文件末尾

echo "default-time-zone = '+08:00'">>my.cnf

重启mysql

然后连接上mysql用select NOW(); 查看是否设置成功

cd /etc/mysql
cp my.cnf my.cnf.back
echo "character-set-server=utf8">> my.cnf

停止mysql服务

mysqladmin -uroot -proot shutdown

启动

mysqld

a、注意理解mysqld与mysql。mysqld为mysql服务器或者称之为服务器上的实例,用于提供客户端访问。mysql为客户端访问管理工具。
b、可以通过三种方式启动mysql服务器(mysqld,mysqld_safe,service mysql start)
c、对于mysqld_safe,service mysql start方式,总是通过调用mysqld_safe来启动mysqld进程,以防止mysqld意外宕掉而提供重启服务。
d、可以通过mysqladmin shutdown以及service mysql stop来终止mysql服务器
备份
导出所有数据库
mysqldump -u username -P --all-databases>filename.sql

导出指定数据库

mysqldump -u username -P -databases d1 d2 d3>filename.sql

导出某个表

mysqldump -u username -P d1 t1,t2>filename.sql

daoru all

mysql -uroot -p < ss.sql
msyql -uroot -p databsesname <ss.sql

my.cnf设置错误日志路径
log-error=dir/{filename}

查询错误日志路径

SHOW VARIABLES LIKE 'log_error';

删除错误日志

mysqladmin -uroot -p flush-logs

查看日志

mysqlbinlog filename.number
4.一个可以解决市区和编码问题

Docker file for date and locale set

VERSION 0.0.3

Author: bolingcavalry

基础镜像

FROM centos:7

作者

MAINTAINER BolingCavalry [email protected]

定义时区参数

ENV TZ=Asia/Shanghai

设置时区

RUN ln -snf /usr/share/zoneinfo/$TZ /etc/localtime && echo '$TZ' > /etc/timezone

安装必要应用

RUN yum -y install kde-l10n-Chinese glibc-common

设置编码

RUN localedef -c -f UTF-8 -i zh_CN zh_CN.utf8

设置环境变量

ENV LC_ALL zh_CN.utf8
5、配置备份
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL

Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

Custom config should go here

!includedir /etc/mysql/conf.d/
default-time-zone = '+08:00'
character-set-server=utf8
server-id=152
log-bin=mysql-bin
binlog-do-db=oauth
查看masate状态
show master status;
查看日志内容
show binlog events in ''

-- 恢复数据方法如下:
mysqlbinlog mysql-bin.000009 --start-position 154 --stop-position 755 | mysql -uroot -p mytest

下面是binlog日志恢复中一些常用的参数

--start-datetime:从二进制日志中读取指定等于时间戳或者晚于本地计算机的时间

--stop-datetime:从二进制日志中读取指定小于时间戳或者等于本地计算机的时间 取值和上述一样

--start-position:从二进制日志中读取指定position 事件位置作为开始。

--stop-position:从二进制日志中读取指定position 事件位置作为事件截至

标签:show,emp1,用法,PL,SQL,table,tb,id,select
From: https://www.cnblogs.com/yffs68169/p/17798409.html

相关文章

  • 【MySQL】基础篇-约束
    1.基础知识1.1为什么需要约束?为了保证数据的完整性!1.2什么叫约束?对表中字段的限制。1.3约束的分类:角度1:约束的字段的个数单列约束vs多列约束角度2:约束的作用范围列级约束:将此约束声明在对应字段的后面表级约束:在表中所有字段都声明完,在所有字段的后面声明的约束角度3:约束的......
  • 深入理解 MySQL 中的锁和MVCC机制
    文章目录锁:数据访问的保护者1.了解锁的基本概念2.锁的使用场景3.示例:MySQL中的锁MVCC:多版本并发控制1.MVCC的工作原理2.MVCC的优点3.示例:MySQL中的MVCC如何选择合适的锁和MVCC1.确定隔离级别2.避免过度使用锁3.监控和调优结语......
  • centos7:安装python3.6.8:安装uvicorn、fastapi、pymysql:指定国内的pypi镜像源
    yuminstallpython3python3-develgccmakelibaio-develpip3install-ihttp://mirrors.aliyun.com/pypi/simple--trusted-hostmirrors.aliyun.com"uvicorn[standard]"==0.16.0pymysqlfastapi 关键点:因为centos7的软件仓库中,python3的版本比较低:python3-3.6.8-1......
  • switch-case的基本用法
    switch-case语句:我们在常常需要做出选择,选择之后往往产生不同的结果,在c语言里类似功能的语句还有if-else级联,但今天要讲的switch-case将显得更加简洁和逻辑清晰。一般格式:switch(控制表达式){case常量:语句.......case常量:......
  • Element Plus el-tree懒加载默认选中
    百度上试了很多方法,设置default-expanded-keys不生效,最后使用了下面的方法,亲测有效constloadNode=async(node:Node,resolve:(data:AreaType[])=>void)=>{if(node.level===0){const{data}=awaitgetRegionList(areaOptions)if(!props.special)......
  • com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Public Key
    问题:连接MySQL数据库时抛出异常信息:com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException:PublicKeyRetrievalisnotallowed一开始aplication.yml配置如下所示:spring:application:name:service-provider-sentinel9999datasource:driver-cl......
  • 28、Flink 的SQL之DROP 、ALTER 、INSERT 、ANALYZE 语句
    文章目录Flink系列文章一、DROP1、DROPCATALOG2、DROPDATABASE3、DROPTABLE4、DROPVIEW5、DROPFUNCTION6、droptable示例二、alter1、ALTERDATABASE2、ALTERTABLE1)、建表2)、ADD1、增加单列示例2、增加watermark列3)、MODIFY1、修改列2、修改水印4)、DROP5)、RENAME6)、SET7)、......
  • PostgreSQL pgBackRest 是最好的PG备份工具 ? (深入一些疑难问题 2)
    每天感悟突然想愤怒的时候,或别人认为你应该愤怒的时候,你不在愤怒,而是能理智的或冰冷的分析出为什么,原因是什么,并且尝试把自己放到他的位置去看明白原理或根本,恭喜你又升级了上次在安装和简单的实现了备份后,产生了很多的问题,基于这些问题我们持续的开始针对pgbackrest进行研究和发现......
  • PostgreSQL pgBackRest 是最好的PG备份工具 ? (小试牛刀 1)
    之前备份的工具一直在使用PGRMAN,潮流变化了,现在最新最推崇的PG备份软件是pgBackRest,今天来探究一下到底为什么他是目前最推崇的备份软件。根据GITHUB的介绍,pgbackrest目标是一个可信赖的容易使用的备份和恢复工具和一体化的解决方案,针对大型的数据库和多负载的情况下的选择,目前写......
  • MySQL系列:binlog日志详解(参数、操作、GTID、优化、故障演练)
    目录简介作用系统参数--log_bin--server_id--binlog_format--sync-binlog(双一标准)--gtid-mode(gtid)--enforce-gtid-consistency(gtid)--expire-logs-day(优化参数)--binlog_cache_size(优化参数)--max_binlog_cache_size(优化参数)--max_binlog_size(优化参数)sql_log_bin日志操作开启日......