drop table if exists fruits;
drop table if exists suppliers;
create TABLE suppliers
(
id INT not null,
name varchar(25),
PRIMARY KEY (id)
);
create TABLE fruits
(
id INT not NULL,
name varchar(25),
suppliersID INT,
price float,
PRIMARY KEY (id),
constraint fk_fruits_suppliers foreign key (suppliersID) references suppliers(id)
);
insert into suppliers(id, name) values(1, 'sunrise1'), (2, 'sunrise2'), (3, 'sunrise3');
INSERT into fruits(id, name, price, suppliersID)
VALUES(1, 'apple', 1.266, 1), (2, 'banana', 2.556, 1), (3, 'grape', 3.444486, 1);
-- select * from fruits;
DROP procedure if EXISTS AvgFruitPrice;
create procedure AvgFruitPrice()
BEGIN
SELECT AVG(price) from fruits;
END;
-- call AvgFruitPrice();
DROP procedure if EXISTS Proc;
create procedure Proc()
BEGIN
select * from fruits;
end;
-- call Proc();
DROP PROCEDURE if exists countproc;
delimiter //
create procedure CountProc(OUT param1 int)
BEGIN
SELECT count(*) into param1 from fruits;
END//
delimiter ;
-- CALL countproc(@countprocresult);
-- select @countprocresult;
drop procedure if exists countproc1;
delimiter //
create procedure countproc1(in pid int, out count int)
BEGIN
declare var1 int;
set var1 = pid;
select count(*) into count from fruits where id = var1;
END//
delimiter ;
-- call countproc1(1, @countproc1result);
-- select @countproc1result;
drop function if exists NameByZip;
create function NameByZip()
RETURNS varchar(25)
return (select name from suppliers where id = 1);
-- SELECT namebyzip();
-- show variables like '%tran%';
-- show variables like '%auto%';
-- show variables like '%log%';
-- show variables like '%isolation%';
drop procedure if exists handlerDemo;
delimiter //
create procedure handlerDemo()
BEGIN
declare continue handler for SQLEXCEPTION set @x2 = 1;
set @x=1;
insert into test VALUES(1);
SET @x=2;
insert into test values(1);
SET @x=3;
insert into test VALUES(1);
END //
delimiter ;
-- call handlerDemo();
-- select @x, @x2;
drop procedure if exists CursorDemo;
delimiter //
create procedure CursorDemo()
BEGIN
declare name1, names1 VARCHAR(500) DEFAULT '';
declare price1, priceSum FLOAT DEFAULT 0;
declare cursor_fruits CURSOR for select name, price from fruits;
-- declare CONTINUE HANDLER for sqlexception set @info='err';
-- declare CONTINUE HANDLER for 1329 set @info='err';
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET name1 = null;
open cursor_fruits;
FETCH cursor_fruits into name1, price1;
while name1 is not null
do
-- if name1 in ('banana', 'grape')
-- then
-- SET names1 = CONCAT(names1, ',', name1);
-- SET priceSum = priceSum + price1;
-- end if;
case name1
when 'banana' then set names1=CONCAT(names1, ' b:', name1);
when 'grape' then set names1=CONCAT(names1, ' g:', name1);
else set names1 = names1;
end case;
FETCH cursor_fruits into name1, price1;
end WHILE;
close cursor_fruits;
set @co = 0, @ind = 0;
addloop: LOOP
set @co = @co + 1;
set @ind = @ind + 1;
if @co = 10
then
leave addloop;
end if;
if @ind <= 5
then
iterate addloop;
end if;
select @ind;
end loop addloop;
select name1, names1, price1, priceSum, @co;
END //
delimiter ;
-- CALL cursordemo();
drop function if EXISTS testMultiSelect;
create function testMultiSelect()
RETURNS VARCHAR(100)
RETURN (SELECT name from fruits limit 1);
-- SELECT testMultiSelect();
-- show procedure status like 'cursordemo';
-- show create procedure cursordemo;
-- show function status like 'testMultiSelect';
-- show create function testMultiSelect;
-- alter function testMultiSelect
-- comment 'test function'
-- RETURNS VARCHAR(50)
-- RETURN (SELECT name from suppliers limit 1);
-- select * from information_schema.routines where ROUTINE_SCHEMA = 'test_db';
drop table if exists sch;
create table sch
(
id int not null PRIMARY KEY,
name VARCHAR(50) not null,
glass VARCHAR(50) not null
);
insert into sch(id, name, glass) values(1, 'xiaoming', 'glass1'),(2, 'xiaojun', 'glass2'),(3, 'xiaojun', 'glass3');
-- select * from sch;
drop function if exists count_sch;
create function count_sch()
returns int
RETURN (SELECT count(*) as c from sch);
-- select count_sch();
drop procedure if EXISTS count_sch_proc;
create procedure count_sch_proc(out pCount int)
BEGIN
declare cid int default 0;
DECLARE cursor_sch cursor for select id from sch;
declare CONTINUE HANDLER FOR SQLSTATE '02000' SET cid = null;
set @idSum = 0;
OPEN cursor_sch;
fetch cursor_sch into cid;
WHILE cid is not NULL
DO
set @idSum = @idSum + cid;
fetch cursor_sch into cid;
end while;
select @idSum;
SELECT count_sch() into pCount;
END;
-- call count_sch_proc(@result);
-- select @result, @idSum;
drop table if EXISTS student;
create table student
(
id int,
name varchar(40),
PRIMARY KEY (id)
);
drop table if EXISTS stu_info;
create table stu_info
(
id int,
glass VARCHAR(40),
address varchar(90),
PRIMARY KEY (id)
);
drop table if EXISTS t;
create table t(quantity int, price float);
insert into t(quantity, price) values(1, 10),(2, 20),(3, 30);
-- select * from t;
drop view if EXISTS view_t;
create view view_t(qty, price, total) as SELECT quantity, price, (quantity * price) from t with local check option;
-- select * from view_t;
-- INSERT into view_t(qty, price) values(4, 100);
-- select * from view_t;
-- update view_t set total = 1;
-- update view_t set price = 1;
insert into student(id, name) values(1, '1'), (2, '2'), (3, '3');
insert into stu_info(id, glass, address) values(1, '11', '111'),(2, '22', '222'),(3, '33', '333'),(4, '44', '444');
-- select * from student;
-- select * from stu_info;
drop view if EXISTS view_stu_glass;
create view view_stu_glass(id, name, glass) as
select student.id, student.name, stu_info.glass
from student LEFT JOIN stu_info ON student.id = stu_info.id;
-- select * from view_stu_glass;
-- insert into view_stu_glass(id, name, glass) values(5, '5', '555');
-- select * from view_stu_glass;
-- describe view_stu_glass;
-- CREATE or replace view view_stu_glass as select * from stu_info;
-- select * from view_stu_glass;
--
-- alter view view_stu_glass as select * from student;
-- select * from view_stu_glass;
-- show function status;
-- show table status like 'view_stu_glass';
-- show table status like 'student';
-- show table status like 'stu_info';
-- show create view view_stu_glass;
-- select * from information_schema.VIEWS;
-- SELECT * FROM information_schema.USER_PRIVILEGES;
-- select * from `performance_schema`.users;
-- select * from mysql.`user`;
-- show create view view_t;
drop table if EXISTS sign;
drop table if EXISTS stu_mark;
drop table if EXISTS stu;
create table stu
(
id int,
name VARCHAR(20),
addr VARCHAR(50),
tel VARCHAR(50),
PRIMARY KEY (id)
);
create table sign
(
id int,
name VARCHAR(20),
sch VARCHAR(50),
sign_sch VARCHAR(50),
PRIMARY key (id),
constraint fk_sign_stu FOREIGN KEY (id) references stu(id)
);
create table stu_mark
(
id INT,
name VARCHAR(20),
mark INT not null,
PRIMARY KEY (id)
-- ,constraint fk_stu_mark_stu foreign key (id) references stu(id)
);
insert into stu(id, name, addr, tel)
VALUES(1, 'name1', 'addr1', 'tel1'),(2, 'name2', 'addr2', 'tel2'),(3, 'name3', 'addr3', 'tel3');
insert into sign(id, name, sch, sign_sch)
VALUES(1, 'name1', 'sch1', 'sign_sch1'),(2, 'name2', 'sch2', 'sign_sch2'),(3, 'name3', 'sch3', 'sign_sch3');
insert into stu_mark(id, name, mark)
values(1, 'name1', 10),(2, 'name2', 20),(3, 'name3', 30),(4, 'name4', 40);
drop view if EXISTS view_beida;
create view view_beida(id, name, mark, sch)
as select stu.id, stu.name, stu_mark.mark, sign.sch from stu
LEFT JOIN sign ON stu.id = sign.id
RIGHT JOIN stu_mark ON stu_mark.id = stu.id
where
stu_mark.mark > 20;
-- select * from view_beida;
-- set global max_connections=600;
-- show VARIABLES like '%conn%';
-- show VARIABLES like '%timeout%';
-- show status like '%conn%';
-- show PROCESSLIST;
drop table if exists char_test;
create TABLE char_test
(
c CHAR(10),
v VARCHAR(10)
);
INSERT into char_test(c, v) values('1', '1'),(' 1', ' 1'),(' 1 ', ' 1 '),('1 ', '1 ');
SELECT * from char_test;
drop table if exists char_test_new;
create table char_test_new like char_test;
-- alter table char_test_new engine=myisam;
insert into char_test_new select * from char_test;
select * from char_test_new;
optimize table char_test_new;
use sunrisereport2;
SELECT financeTypeName, SUM(revenue) AS revenue, SUM(quantity) AS quantity
FROM
(
SELECT t_a.articID,
GROUP_CONCAT(t_b.name)
,GROUP_CONCAT(t_d.name)
,GROUP_CONCAT(t_e.financeTypeName) AS financeTypeName
,SUM(revenue) AS revenue
,SUM(quantity) AS quantity
,COUNT(revenue) c FROM
(
SELECT articID
,SUM(revenue) AS revenue
,SUM(quantity) AS quantity
FROM articsalestotal
WHERE date >= '2018-04-01' AND date < '2018-05-01'
GROUP BY articID
LIMIT 100000
) t_a
LEFT JOIN artic t_b
ON t_a.articID = t_b.abi_id
LEFT JOIN articandbrandrelationship t_c
ON t_a.articID = t_c.articID
LEFT JOIN brand t_d
ON t_c.brandID = t_d.id
LEFT JOIN brandandfinancetype t_e
ON t_d.id = t_e.brandID
GROUP BY t_a.articID
ORDER BY revenue DESC
LIMIT 100000) t_Res
GROUP by financeTypeName;
show VARIABLES like 'optimizer_switch';
set optimizer_switch = 'mrr=on,mrr_cost_based=on';
'
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on,
index_condition_pushdown=on,
mrr=on,
mrr_cost_based=off,
block_nested_loop=on,
batched_key_access=off,
materialization=on,
semijoin=on,
loosescan=on,
firstmatch=on,
duplicateweedout=on,
subquery_materialization_cost_based=on,
use_index_extensions=on,
condition_fanout_filter=on,
derived_merge=on';
EXPLAIN
SELECT articID
,SUM(revenue) AS revenue
,SUM(quantity) AS quantity
FROM articsalestotal
WHERE date >= '2016-01-01' AND date < '2018-01-01'
GROUP BY articID
LIMIT 10000,50;
SELECT t_a.articID
,SUM(revenue) AS revenue
,SUM(quantity) AS quantity
FROM articsalestotal t_a
INNER JOIN
(select articID from articsalestotal
WHERE date >= '2016-01-01' AND date < '2018-01-01'
GROUP BY articID
LIMIT 10000,50) t_b
ON t_a.articID = t_b.articID
GROUP BY t_a.articID
LIMIT 100000;
DESCRIBE origin_saledetials;
explain SELECT articID,
date,
Sum(beginAmount) AS endAmount,
Sum(beginStock) AS endStock,
SUM(onYearsAgoStockAmount) AS onYearsAgoStockAmount
FROM
Articstocktotal
WHERE date > '2018/1/1' AND date <= '2018/9/1'
GROUP BY articID, date
ORDER BY endAmount DESC;
explain SELECT t_a.articID,
date,
Sum(beginAmount) AS endAmount,
Sum(beginStock) AS endStock,
SUM(onYearsAgoStockAmount) AS onYearsAgoStockAmount
FROM
Articstocktotal t_a
inner JOIN
(SELECT id
FROM
Articstocktotal
WHERE date > '2018/1/1' AND date <= '2018/9/1') t_b
ON t_a.id = t_b.id
GROUP BY t_a.articID, t_a.date
ORDER BY endAmount DESC;
show index from Articstocktotal;
explain SELECT articID
FROM
Articstocktotal
WHERE date > '2018/1/1' AND date <= '2018/9/1';
explain SELECT articID
FROM
Articstocktotal WHERE id = 5656999;
flush STATUS;
show STATUS where variable_name like 'create%' or variable_name like 'handler%';
show variables like 'prof%';
set profiling_history_size=50;
use sunrisereport2;
SELECT articID,
date,
Sum(beginAmount) AS endAmount,
Sum(beginStock) AS endStock,
SUM(onYearsAgoStockAmount) AS onYearsAgoStockAmount
FROM
Articstocktotal
WHERE date > '2018/1/1' AND date <= '2018/9/1'
GROUP BY articID, date
ORDER BY endAmount DESC;
show PROFILES;
show profile for query 276;
check table Articstocktotal
repair table Articstocktotal
show VARIABLES like '%long%';
show index from Articstocktotal;
analyze table Articstocktotal;
show table status like 'Articstocktotal';
show VARIABLES like '%persistent%';
select SQL_NO_CACHE * from Articstocktotal;
show status like 'last_query_cost';
-- user
select * from mysql.user;
-- db
select * from mysql.db;
-- tables
select * from mysql.tables_priv;
-- columns
select * from mysql.columns_priv;
-- procedure
select * from mysql.procs_priv;
-- 两条语句等效
create user 'sunrise'@'%' identified by 'Abcd1234';
create user 'sunrise' identified by 'Abcd1234';
-- 登录无需密码
create user 'sunrise';
-- 非明文密码 *32DEBF5D479D0E1D840F0E2D3C0337DE8792B690
select password('Abcd1234');
create user 'sunrise_report' identified by PASSWORD '*32DEBF5D479D0E1D840F0E2D3C0337DE8792B690';
-- 删除用户
drop user 'sunrise_report'
-- 创建授权
grant select,update ON sunrisereport1.* to 'sunrise_report'@'%' IDENTIFIED BY password '*32DEBF5D479D0E1D840F0E2D3C0337DE8792B690';
-- 非明文密码 *4AD47E08DAE2BD4F0977EED5D23DC901359DF617
select password('abcd1234');
-- update password
update mysql.user set authentication_string='*4AD47E08DAE2BD4F0977EED5D23DC901359DF617' where USER='sunrise_report'
show variables like 'have_openssl';
-- 数据目录
show variables like 'datadir';
-- 读写线程数
show variables like 'innodb_%_io_threads';
-- 清空 undolog 线程数
show variables like 'innodb_purge_threads';
-- 缓存池大小
show variables like 'innodb_buffer_pool_size';
-- 每页大小
show variables like 'innodb_page_size';
-- 缓存池个数
show variables like 'innodb_buffer_pool_instances';
show ENGINE innodb status;
show engines;
show engine innodb status;
-- 全文索引
use test_db;
drop table if EXISTS fts_a;
create table fts_a
(
FTS_DOC_ID bigint UNSIGNED auto_increment not null,
customerID int,
body Text,
PRIMARY key(FTS_DOC_ID)
,FULLTEXT index (body)
);
insert into fts_a select null, 1, 'pease porridge in the pot';
insert into fts_a select null, 2, 'pease porridge hot, pease porridge cold';
insert into fts_a select null, 3, 'nine days old';
insert into fts_a select null, 4, 'some like it hot, some like it cold';
insert into fts_a select null, 5, 'some like it in the pot';
insert into fts_a select null, 6, 'nine days old';
insert into fts_a select null, 7, 'i like code days';
insert into fts_a select null, 8, '我喜欢编程';
create fulltext index idx_fts on fts_a(body);
-- ''
show variables like 'innodb_ft_aux_table';
set global innodb_ft_aux_table='test_db/fts_a';
select * from information_schema.INNODB_FT_INDEX_TABLE;
DELETE from fts_a where fts_doc_id=7;
select * from information_schema.INNODB_FT_DELETED;
-- OFF 只删除倒排序中该文档的分词信息
show variables like 'innodb_optimize_fulltext_only';
set global innodb_optimize_fulltext_only=ON;
select * from information_schema.INNODB_FT_DELETED;
optimize table fts_a;
select * from information_schema.INNODB_FT_BEING_DELETED;
-- 已经删除的 id 不允许再次插入(下面语句会抛出异常)
-- insert into fts_a select 7, 7, 'I like this days';
-- stopword 不需要进行分词统计的单词(默认的不统计的分词)
select * from information_schema.INNODB_FT_DEFAULT_STOPWORD;
-- 设置不统计的分词表(可以设置)
show variables like 'innodb_ft_server_stopword_table';
-- 1 SIMPLE fts_a ALL 7 14.29 Using where
EXPLAIN SELECT * from fts_a where body like '%days%';
SELECT * from fts_a where body like '%days%';
-- 1 SIMPLE fts_a fulltext body body 0 const 1 100 Using where; Ft_hints: sorted
explain select * from fts_a where match(body) AGAINST('porridge' in NATURAL language mode);
-- 结果会对相关性排序
select * from fts_a where match(body) AGAINST('porridge' in NATURAL language mode);
-- 统计(统计信息部排序)
select count(if(match(body) against('porridge'), 1, null)) from fts_a;
-- 相关性统计(计算相关性)
select *, match(body) AGAINST('porridge' in NATURAL language mode) revenue from fts_a;
-- 有 并且 没有
select * from fts_a where MATCH(body) against('+pease -hot' in boolean mode);
-- 既有 又有
select * from fts_a where match(body) against('+pease +hot' in boolean mode);
-- 有 或者 有
select * from fts_a where match(body) against('pease hot' in boolean mode);
-- pease pot 之间小于 30 个字符
select * from fts_a where match(body) against('"pease pot" @30' in boolean mode);
select * from fts_a where match(body) against('"pease pot" @5' in boolean mode);
-- 包含 like 或者 pot,如果有port 提升相关性
select * from fts_a where match(body) against('like >pot' in boolean mode);
select *, match(body) against('like >pot' in boolean mode) from fts_a;
select *, match(body) against('like pot' in boolean mode) from fts_a;
-- 包含 like 或者 pot 或者 some, 如果有port 提升相关性,如果有some减少相关性(负相关性 != 0 也会出现在结果中)
select * from fts_a where match(body) AGAINST('like >hot <some' in boolean mode);
select *, match(body) against('like hot some' in boolean mode) from fts_a;
select *, match(body) against('like >hot <some' in boolean mode) from fts_a;
-- 以 po 开头
select * from fts_a where MATCH(body) against('po*' in boolean mode);
select *, MATCH(body) against('po*' in boolean mode) from fts_a;
-- 短语
select * from fts_a where match(body) against('"like hot"' in boolean mode);
-- 非短语(没有 " ")
SELECT * from fts_a where match(body) AGAINST('"like hot" like hot' in boolean mode);
-- query expansion
drop table if exists articles;
create table articles
(
id int unsigned not null auto_increment,
title VARCHAR(50),
body text,
primary key (id),
fulltext index (title, body)
)engine = innodb;
insert into articles (title, body)
values('mysql tutorial', 'dbms stands for database...')
,('how to use mysql well', 'after you when through a...')
,('optimizing mysql', 'in this tutorial we will show...')
,('1001 mysql tricks', '1. never run mysql as root. 2...')
,('mysql vs. yoursql', 'in the following database comparison...')
,('mysql security', 'when configured properly, mysql ...')
,('tuning db2', 'for ibm database ...')
,('ibm history', 'db2 history for ibm');
select * from articles where MATCH(title, body) AGAINST('database');
select * from articles where match(title, body) against('database' with query expansion);
select *, match(title, body) against('database' with query expansion) from articles;
-- 最小分词长度(中文建议 4)
show variables like 'ft_min_word_len';
-- [SQL]set ft_min_word_len = 2;
-- [Err] 1238 - Variable 'ft_min_word_len' is a read only variable
set ft_min_word_len = 2;
第七章 事务
999
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
