标签：常用 name -- into 指令 emp SQL id select
show processlist;
use db20201107_demo;
CREATE TABLE `city`
(
`city_id` int(11) NOT NULL AUTO_INCREMENT,
`city_name` varchar(50) NOT NULL,
`country_id` int(11) NOT NULL,
PRIMARY KEY (`city_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
CREATE TABLE `country`
(
`country_id` int(11) NOT NULL AUTO_INCREMENT,
`country_name` varchar(100) NOT NULL,
PRIMARY KEY (`country_id`)
) ENGINE = InnoDB
DEFAULT CHARSET = utf8;
insert into `city` (`city_id`, `city_name`, `country_id`)
values (1, '西安', 1);
insert into `city` (`city_id`, `city_name`, `country_id`)
values (2, 'NewYork', 2);
insert into `city` (`city_id`, `city_name`, `country_id`)
values (3, '北京', 1);
insert into `city` (`city_id`, `city_name`, `country_id`)
values (4, '上海', 1);
insert into `country` (`country_id`, `country_name`)
values (1, 'China');
insert into `country` (`country_id`, `country_name`)
values (2, 'America');
insert into `country` (`country_id`, `country_name`)
values (3, 'Japan');
insert into `country` (`country_id`, `country_name`)
values (4, 'UK');
create index inx_city_name on city (city_name);
show index from city;
drop index inx_city_name on city;
# 创建复合索引
# CREATE INDEX idx_name_email_status ON tb_seller(NAME,email,STATUS);
# 创建city_country_view视图
create or replace view city_country_view as
select t.*, c.country_name
from country c,
city t
where c.country_id = t.country_id;
show tables;
show table status;
show create view city_country_view;
drop view city_country_view;
# 存储过程
# CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
# begin
-- SQL语句
#end ;
delimiter $
create procedure pro_test1()
begin
select 'Hello Mysql' ;
end$
delimiter ;
call pro_test1();
-- 查询db_name数据库中的所有的存储过程
# select name from mysql.proc where db='db20201107_demo';
-- 查询存储过程的状态信息
show procedure status;
-- 查询某个存储过程的定义
show create procedure db20201107_demo.pro_test1;
DROP PROCEDURE IF EXISTS pro_test1;
--
delimiter $
create procedure pro_test2()
begin
declare num int default 5;
select num + 10;
end$
delimiter ;
--
call pro_test2();
--
DELIMITER $
CREATE PROCEDURE pro_test3()
BEGIN
DECLARE NAME VARCHAR(20);
SET NAME = 'MYSQL';
SELECT NAME;
END$
DELIMITER ;
--
call pro_test3();
--
DELIMITER $
CREATE PROCEDURE pro_test5()
BEGIN
declare countnum int;
select count(*) into countnum from city;
select countnum;
END$
DELIMITER ;
--
--
call pro_test5();
--
delimiter $
create procedure pro_test6()
begin
declare height int default 175;
declare description varchar(50);
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select description;
end$
delimiter ;
--
call pro_test6();
--
delimiter $
create procedure pro_test6_1(in height int)
begin
declare description varchar(50) default '';
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
select concat('身高 ', height, ' 对应的身材类型为: ', description);
end$
delimiter ;
--
call pro_test6_1(170);
--
delimiter $
create procedure pro_test7(in height int, out description varchar(100))
begin
if height >= 180 then
set description = '身材高挑';
elseif height >= 170 and height < 180 then
set description = '标准身材';
else
set description = '一般身材';
end if;
end$
delimiter ;
--
call pro_test7(170, @description$);
select @description$;
--
delimiter $
create procedure pro_test8(month int)
begin
declare result varchar(20);
case
when month >= 1 and month <= 3 then
set result = '第一季度';
when month >= 4 and month <= 6 then
set result = '第二季度';
when month >= 7 and month <= 9 then
set result = '第三季度';
when month >= 10 and month <= 12 then
set result = '第四季度';
end case;
select concat('您输入的月份为 :', month, ' , 该月份为 : ', result) as content;
end$
delimiter ;
--
call pro_test8(3);
--
delimiter $
create procedure pro_test9(n int)
begin
declare total int default 0;
declare num int default 1;
while num <= n
do
set total = total + num;
set num = num + 1;
end while;
select total;
end$
delimiter ;
--
call pro_test9(10);
--
delimiter $
create procedure pro_test10(n int)
begin
declare total int default 0;
repeat
set total = total + n;
set n = n - 1;
until n = 0
end repeat;
select total;
end$
delimiter ;
--
call pro_test10(10);
--
delimiter $
CREATE PROCEDURE pro_test11(n int)
BEGIN
declare total int default 0;
ins:
LOOP
IF n <= 0 then
leave ins;
END IF;
set total = total + n;
set n = n - 1;
END LOOP ins;
select total;
END$
delimiter ;
--
call pro_test11(10);
--
create table emp
(
id int(11) not null auto_increment,
name varchar(50) not null comment '姓名',
age int(11) comment '年龄',
salary int(11) comment '薪水',
primary key (`id`)
) engine = innodb
default charset = utf8;
insert into emp(id, name, age, salary)
values (null, '金毛狮王', 55, 3800),
(null, '白眉鹰王', 60, 4000),
(null, '青翼蝠王', 38, 2800),
(null, '紫衫龙王', 42, 1800);
--
-- 查询emp表中数据, 并逐行获取进行展示
delimiter $
create procedure pro_test12()
begin
declare e_id int(11);
declare e_name varchar(50);
declare e_age int(11);
declare e_salary int(11);
declare emp_result cursor for select * from emp;
open emp_result;
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=', e_id, ', name=', e_name, ', age=', e_age, ', 薪资为: ', e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=', e_id, ', name=', e_name, ', age=', e_age, ', 薪资为: ', e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=', e_id, ', name=', e_name, ', age=', e_age, ', 薪资为: ', e_salary);
fetch emp_result into e_id,e_name,e_age,e_salary;
select concat('id=', e_id, ', name=', e_name, ', age=', e_age, ', 薪资为: ', e_salary);
close emp_result;
end$
delimiter ;
--
call pro_test12();
-- 通过循环结构 , 获取游标中的数据
DELIMITER $
create procedure pro_test13()
begin
DECLARE id int(11);
DECLARE name varchar(50);
DECLARE age int(11);
DECLARE salary int(11);
DECLARE has_data int default 1;
DECLARE emp_result CURSOR FOR select * from emp;
DECLARE EXIT HANDLER FOR NOT FOUND set has_data = 0;
open emp_result;
repeat
fetch emp_result into id , name , age , salary;
select concat('id为', id, ', name 为', name, ', age为 ', age, ', 薪水为: ', salary);
until has_data = 0
end repeat;
close emp_result;
end$
DELIMITER ;
--
call pro_test13();
-- 存储函数
SET GLOBAL log_bin_trust_function_creators = 1;
--
delimiter $
create function count_city(countryId int)
returns int
begin
declare cnum int;
select count(*) into cnum from city where country_id = countryId;
return cnum;
end$
delimiter ;
-- 调用
select count_city(1);
select count_city(2);
-- 触发器
# 需求
# 通过触发器记录 emp 表的数据变更日志 , 包含增加, 修改 , 删除 ;
# 首先创建一张日志表 :
create table emp_logs
(
id int(11) not null auto_increment,
operation varchar(20) not null comment '操作类型, insert/update/delete',
operate_time datetime not null comment '操作时间',
operate_id int(11) not null comment '操作表的ID',
operate_params varchar(500) comment '操作参数',
primary key (`id`)
) engine = innodb
default charset = utf8;
-- 创建 insert 型触发器，完成插入数据时的日志记录 :
DELIMITER $
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id, operation, operate_time, operate_id, operate_params)
values (null, 'insert', now(), new.id,
concat('插入后(id:', new.id, ', name:', new.name, ', age:', new.age, ', salary:', new.salary, ')'));
end $
DELIMITER ;
-- 创建 update 型触发器，完成更新数据时的日志记录 :
DELIMITER $
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
insert into emp_logs (id, operation, operate_time, operate_id, operate_params)
values (null, 'update', now(), new.id,
concat('修改前(id:', old.id, ', name:', old.name, ', age:', old.age, ', salary:', old.salary, ') , 修改后(id',
new.id, 'name:', new.name, ', age:', new.age, ', salary:', new.salary, ')'));
end $
DELIMITER ;
-- 创建delete 行的触发器 , 完成删除数据时的日志记录 :
DELIMITER $
create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id, operation, operate_time, operate_id, operate_params)
values (null, 'delete', now(), old.id,
concat('删除前(id:', old.id, ', name:', old.name, ', age:', old.age, ', salary:', old.salary, ')'));
end $
DELIMITER ;
-- 测试：
insert into emp(id, name, age, salary)
values (null, '光明左使', 30, 3500);
insert into emp(id, name, age, salary)
values (null, '光明右使', 33, 3200);
update emp
set age = 39
where id = 3;
delete
from emp
where id = 5;
--
drop trigger db20201107_demo.emp_logs_insert_trigger;
drop trigger db20201107_demo.emp_logs_update_trigger;
drop trigger db20201107_demo.emp_logs_delete_trigger;
--
show triggers;
--
show engines;
--
show variables like '%storage_engine%';
-- 事务控制
create table goods_innodb
(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key (id)
) ENGINE = innodb
DEFAULT CHARSET = utf8;
start transaction;
insert into goods_innodb(id, name)
values (null, 'Meta20');
commit;
--
create table country_innodb(
country_id int NOT NULL AUTO_INCREMENT,
country_name varchar(100) NOT NULL,
primary key(country_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table city_innodb(
city_id int NOT NULL AUTO_INCREMENT,
city_name varchar(50) NOT NULL,
country_id int NOT NULL,
primary key(city_id),
key idx_fk_country_id(country_id),
CONSTRAINT `fk_city_country` FOREIGN KEY(country_id) REFERENCES country_innodb(country_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2),(null,'BeiJing',1);
show create table city_innodb;
-- 删除country_id为1 的country数据：
delete from country_innodb where country_id = 1;
-- 更新主表country表的字段 country_id :
update country_innodb set country_id = 100 where country_id = 1;
-- myisam不支持事务
create table goods_myisam(
id int NOT NULL AUTO_INCREMENT,
name varchar(20) NOT NULL,
primary key(id)
)ENGINE=myisam DEFAULT CHARSET=utf8;
start transaction;
insert into goods_myisam values(null,'电脑');
rollback;
--
show session status;
show global status;
show status like 'Com_______';
show processlist;
select connection_id();
explain select * from city;
show status like 'Innodb_rows_%';
--
show create table t_user;
CREATE TABLE `t_role` (
`id` varchar(32) NOT NULL,
`role_name` varchar(255) DEFAULT NULL,
`role_code` varchar(255) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `t_user` (
`id` varchar(32) NOT NULL,
`username` varchar(11) DEFAULT NULL,
`password` varchar(11) DEFAULT NULL,
`nickname` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_user_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `user_role` (
`id` int(11) NOT NULL auto_increment ,
`user_id` varchar(32) DEFAULT NULL,
`role_id` varchar(32) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_ur_user_id` (`user_id`),
KEY `fk_ur_role_id` (`role_id`),
CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into `t_user` (`id`, `username`, `password`, `nickname`) values('1','super','MXMknstqKRe','超级管理员');
insert into `t_user` (`id`, `username`, `password`, `nickname`) values('2','admin','nstqKRe','系统管理员');
insert into `t_user` (`id`, `username`, `password`, `nickname`) values('3','cqm','LF8ZCrW0Cui','test02');
insert into `t_user` (`id`, `username`, `password`, `nickname`) values('4','stu1','ftCpUMZa','学生1');
insert into `t_user` (`id`, `username`, `password`, `nickname`) values('5','stu2','ZKgc18vqm','学生2');
insert into `t_user` (`id`, `username`, `password`, `nickname`) values('6','t1','knstqKRe','老师1');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('5','学生','student','学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('7','老师','teacher','老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('8','教学管理员','teachmanager','教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('9','管理员','admin','管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`) VALUES('10','超级管理员','super','超级管理员');
INSERT INTO user_role(id,user_id,role_id) VALUES(NULL, '1', '5'),(NULL, '1', '7'),(NULL, '2', '8'),(NULL, '3', '9'),(NULL, '4', '8'),(NULL, '5', '10') ;
--
# 1） id 相同表示加载表的顺序是从上到下。
explain select * from t_role r, t_user u, user_role ur where r.id = ur.role_id and u.id = ur.user_id;
# 2） id 不同id值越大，优先级越高，越先被执行。
EXPLAIN SELECT * FROM t_role WHERE id = (SELECT role_id FROM user_role WHERE user_id = (SELECT id FROM t_user WHERE username = 'stu1'));
# 3） id 有相同，也有不同，同时存在。id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
EXPLAIN SELECT * FROM t_role r , (SELECT * FROM user_role ur WHERE ur.`user_id` = '2') a WHERE r.id = a.role_id;
--
select @@have_profiling;
select @@profiling;
set profiling=1;
show databases;
use db20201107_demo;
show tables;
select * from city where city_id < 5;
select count(*) from city;
show profiles;
show profile for query 75;
show profile cpu for query 75;
--
# MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。
#
# 打开trace ， 设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。
SET optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_max_mem_size=1000000;
select * from city where city_id < 4;
# 最后， 检查information_schema.optimizer_trace就可以知道MySQL是如何执行SQL的 ：
select * from information_schema.optimizer_trace;
--
create table `tb_seller` (
`sellerid` varchar (100),
`name` varchar (100),
`nickname` varchar (50),
`password` varchar (60),
`status` varchar (1),
`address` varchar (100),
`createtime` datetime,
primary key(`sellerid`)
)engine=innodb default charset=utf8mb4;
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba','阿里巴巴','阿里小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu','百度科技有限公司','百度小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei','华为科技有限公司','华为小店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('cqm','传智播客教育科技有限公司','传智播客','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima','黑马程序员','黑马程序员','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji','罗技科技有限公司','罗技小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo','OPPO科技有限公司','OPPO官方旗舰店','e10adc3949ba59abbe56e057f20f883e','0','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm','掌趣科技股份有限公司','掌趣小店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu','千度科技','千度小店','e10adc3949ba59abbe56e057f20f883e','2','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina','新浪科技有限公司','新浪官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi','小米科技','小米官方旗舰店','e10adc3949ba59abbe56e057f20f883e','1','西安市','2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia','宜家家居','宜家家居旗舰店','e10adc3949ba59abbe56e057f20f883e','1','北京市','2088-01-01 12:00:00');
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
# 1). 全值匹配 ，对索引中所有列都指定具体值。
# 该情况下，索引生效，执行效率高。
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
# 2) 最左前缀法则
# 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。
# 匹配最左前缀法则，走索引：
explain select * from tb_seller where name='小米科技';
explain select * from tb_seller where name='小米科技' and status='1';
explain select * from tb_seller where name='小米科技' and status='1' and address='北京市';
# 违法最左前缀法则 ， 索引失效：
explain select * from tb_seller where status='1';
# 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效：
explain select * from tb_seller where name='小米科技' and address='北京市';
# 3) 范围查询右边的列，不能使用索引 。 根据前面的两个字段name ， status 查询是走索引的， 但是最后一个条件address 没有用到索引。
explain select * from tb_seller where name='小米科技' and status>'1' and address='北京市';
# 4) 不要在索引列上进行运算操作， 索引将失效。
explain select * from tb_seller where substring(name,3,2)='科技';
# 5) 字符串不加单引号，造成索引失效
explain select * from tb_seller where name='小米科技' and status='1';
# 由于在查询时，没有对字符串加单引号，MySQL的查询优化器，会自动的进行类型转换，造成索引失效。
explain select * from tb_seller where name='小米科技' and status=1;
# 6) 尽量使用覆盖索引，避免select * 尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select * 。
# 7) 用or分割开的条件， 如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
# 示例，name字段是索引列 ， 而createtime不是索引列，中间是or进行连接是不走索引的 ：
explain select * from tb_seller where name='黑马程序员' and createtime = '2088-01-01 12:00:00';
explain select * from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00';
# 8) 以%开头的Like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
explain select * from tb_seller where name like '%黑马程序员';
explain select * from tb_seller where name like '黑马程序员%';
explain select * from tb_seller where name like '黑马%程序员';
# 解决方案: 通过覆盖索引来解决
explain select sellerid from tb_seller where name like '%科技%';
# 9) 如果MySQL评估使用索引比全表更慢，则不使用索引。
# 10) is NULL ， is NOT NULL有时索引失效。
explain select * from tb_seller where name is null;
explain select * from tb_seller where name is not null;
# 11) in 走索引， not in 索引失效。
explain select * from tb_seller where name in ('阿里巴巴','百度科技有限公司');
explain select * from tb_seller where name not in ('阿里巴巴','百度科技有限公司');
# 12) 单列索引和复合索引。
show status like 'Handler_read%';
show global status like 'Handler_read%';
--
CREATE TABLE `emp` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`age` int(3) NOT NULL,
`salary` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert into `emp` (`id`, `name`, `age`, `salary`) values('1','Tom','25','2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2','Jerry','30','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3','Luci','25','2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4','Jay','36','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5','Tom2','21','2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6','Jerry2','31','3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7','Luci2','26','2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8','Jay2','33','3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9','Tom3','23','2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10','Jerry3','32','3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11','Luci3','26','2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12','Jay3','37','4500');
create index idx_emp_age_salary on emp(age,salary);
explain select * from emp order by age desc;
explain select * from emp order by age asc;
explain select id from emp order by age desc;
explain select id, age from emp order by age asc;
--
show variables like 'max_length_for_sort_data';
show variables like 'sort_buffer_size';
--
# 1 查看当前的MySQL数据库是否支持查询缓存：
SHOW VARIABLES LIKE 'have_query_cache';
# 2 查看当前MySQL是否开启了查询缓存 ：
SHOW VARIABLES LIKE 'query_cache_type';
# 3 查看查询缓存的占用大小 ：
SHOW VARIABLES LIKE 'query_cache_size';
# 4 查看查询缓存的状态变量：
SHOW STATUS LIKE 'Qcache%';
--
CREATE TABLE `tb_book` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
`publish_time` DATE DEFAULT NULL,
`status` CHAR(1) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'java编程思想','2088-08-01','1');
INSERT INTO tb_book (id, name, publish_time, status) VALUES(NULL,'solr编程思想','2088-08-08','0');
CREATE TABLE `tb_user` (
`id` INT(11) auto_increment,
`name` VARCHAR(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=myisam DEFAULT CHARSET=utf8 ;
INSERT INTO tb_user (id, name) VALUES(NULL,'令狐冲');
INSERT INTO tb_user (id, name) VALUES(NULL,'田伯光');
# 客户端 一 ：
# 1）获得tb_book 表的读锁
lock tables tb_book read;
# 2） 执行查询操作
select * from tb_book;
select name from tb_seller;
insert into tb_book values(null,'Mysql高级','2088-01-01','1');
lock tables tb_book write;
update tb_book set name = 'java编程思想（第二版）' where id = 1;
show open tables;
show status like 'Table_locks%';
# Mysql 的数据库的默认隔离级别为 Repeatable read ， 查看方式：
show variables like 'tx_isolation';
--
# InnoDB 实现了以下两种类型的行锁。
# - 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
# - 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能
# 再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。
# 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；
# 对于普通SELECT语句，InnoDB不会加任何锁；
#
# 可以通过以下语句显示给记录集加共享锁或排他锁 。
# 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
# 排他锁（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE
--
unlock tables;
create table test_innodb_lock(
id int(11),
name varchar(16),
sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);
set autocommit =0;
select * from test_innodb_lock;
select * from test_innodb_lock where id=3;
update test_innodb_lock set name='A1' where id=3;
commit;
show index from test_innodb_lock ;
show status like 'innodb_row_lock%';
select * from emp where name regexp '^T';
select * from emp where name regexp '2$';
select * from emp where name regexp '[uvw]';
# mysql -uroot -p123456 db20201107_demo -e "select * from tb_book";
# mysqldump -uroot -p123456 db20201107_demo tb_book --add-drop-database --add-drop-table > a
# mysqldump -uroot -p123456 db20201107_demo city -T;
# mysqlshow -uroot -p123456 --count;
# mysqlshow -uroot -p123456 test --count;
# mysqlshow -uroot -p123456 test book --count;
# show variables like 'log_error%';
# mysqlbinlog log-file;
show variables like '%long_query_time%';
show status like 'Innodb_rows_%';
delete from tb_book where id=3;
update tb_book set status=1 where id=20;
show create table book;
CREATE TABLE `book` (
`id` int(10) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`author` varchar(20) DEFAULT NULL,
`publish` varchar(20) DEFAULT NULL,
`pages` int(10) DEFAULT NULL,
`price` float(10,2) DEFAULT NULL,
`bookcaseid` int(10) DEFAULT NULL,
`abled` int(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `FK_ieh6qsxp6q7oydadktc9oc8t2` (`bookcaseid`)
) ENGINE=InnoDB AUTO_INCREMENT=129 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
select * from book;
insert into book values(1,'解忧杂货店','东野圭吾','电子工业出版社',102,27.3,9,1),
(2,'追风筝的人','卡勒德·胡赛尼','中信出版社',330,26,1,1),
(3,'人间失格','太宰治','作家出版社',150,17.3,1,1),
(4,'这就是二十四节气','高春香','电子工业出版社',220,59,3,1),
(5,'白夜行','东野圭吾','南海出版公司',300,27.3,4,1),
(6,'摆渡人','克莱儿·麦克福尔','百花洲文艺出版社',225,22.8,1,1),
(7,'暖暖心绘本','米拦弗特毕','湖南少儿出版社',168,131.6,5,1),
(8,'天才在左疯子在右','高铭','北京联合出版公司',330,27.5,6,1),
(9,'我们仨','杨绛','生活.读书.新知三联书店',89,17.2,7,1),
(10,'活着','余华','作家出版社',100,100,6,1),
(11,'水浒传','施耐庵','三联出版社',300,50,1,1),
(12,'三国演义','罗贯中','三联出版社',300,50,2,1),
(13,'红楼梦','曹雪芹','三联出版社',300,50,5,1),
(14,'西游记','吴承恩','三联出版社',300,60,3,1);
truncate table book;
select version();
use test;
CREATE TABLE employees
(
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees (emp_id)
);
INSERT INTO employees
VALUES (1, 'CEO', NULL),
(2, 'Manager A', 1),
(3, 'Employee 1', 2),
(4, 'Employee 2', 2),
(5, 'Manager B', 1),
(6, 'Employee 3', 5);
INSERT INTO employees
VALUES (7, 'Employee 4', 2),
(8, 'Employee 5', 2),
(9, 'Employee 6', 5),
(10, 'Employee 7', 5),
(11, 'Manager C', 1),
(12, 'Employee 8', 11),
(13, 'Employee 9', 11),
(14, 'Employee 10', 11),
(15, 'Manager D', 1),
(16, 'Employee 11', 15),
(17, 'Employee 12', 15),
(18, 'Employee 13', 15),
(19, 'Manager E', 1),
(20, 'Employee 14', 19),
(21, 'Employee 15', 19),
(22, 'Employee 16', 2),
(23, 'Employee 17', 5),
(24, 'Employee 18', 11),
(25, 'Employee 19', 15),
(26, 'Employee 20', 19),
(27, 'Employee 21', 19);
-- 定义一个名为org_structure的递归公共表表达式(CTE)，用于构建组织结构层次
WITH RECURSIVE org_structure As (
-- 初始化:选取公司最高级别(CEO)的员工信息
SELECT emp_id, emp_name, manager_id, 1 As level, CONCAT('', emp_id) as path
FROM employees
WHERE emp_name = 'CEO'
UNION ALL
-- 递归部分:通过J0IN操作连接employees表和已生成的org_structure表，获取下一级别的员工信息
SELECT e.emp_id, e.emp_name, e.manager_id, os.level + 1, CONCAT(os.path, ',', e.emp_id)
FROM employees e
JOIN org_structure os ON e.manager_id = os.emp_id
)
-- 最终查询结果:从org_structure CTE中选择需要展示的字段，并按照层级(level)和员工ID(emp_id)排序
SELECT emp_id, emp_name, manager_id, level, path
FROM org_structure
ORDER BY level, emp_id;
标签：常用,
name,
--,
into,
指令,
emp,
SQL,
id,
select
From： https://www.cnblogs.com/smalldong/p/18614795