-- 将 fruits.f_name 和 suppliers.s_city创建为了一个新字段连在一起来计算查询
select fruits.f_name as name ,suppliers.s_city as city from suppliers left join fruits on fruits.s_id = suppliers.s_id where 1=1 and  CONCAT(fruits.f_name,'-',suppliers.s_city) = 'apple-Tianjin' ;

-- 员工的部门和名称组合为新字段
select CONCAT_WS('-',dept.d_name,employee.e_name) as name from employee left join dept on dept.d_no = employee.dept_no 

-- 计算表中数量,最小值,最大值,平均值,平均值,累加值
select count(*) as count_value ,MIN(f_price) as min_value,MAX(f_price) as max_value,AVG(f_price) as avg_value ,SUM(f_price) as sum_value from fruits

SELECT c_name,GROUP_CONCAT(grade), sum(grade) as total_grade ,avg(grade) as avg_grade FROM score GROUP BY c_name

SELECT
	`USER_NAME`,
	LENGTH( `USER_NAME` ) AS name_length,
	CHAR_LENGTH( USER_NAME ) AS string_length,
	LOWER( USER_NAME ) AS LOWER_NAME,
	UPPER( USER_NAME ) AS UPPER_NAME,
	REPLACE ( USER_NAME, '0', 'O' ) AS REPLACE_NAME,
	SUBSTRING( USER_NAME, '0', 5 ) AS SUBSTRING_NAME,
	TRIM( `USER_NAME` ) AS TRIM_NAME 
FROM
	`t_user` 
	LIMIT 2,40

select ABS(-22.4) as ABS_VALUE,CEIL(1.4)as CEIL_VALUE,ROUND(1.49)as ROUND_VALUE,FLOOR(1.4) as FLOOR_VALUE,POW(2,3) AS POW_VALUE,RAND()*10 AS RAND_VALUE,MOD(8,3) AS MOD_VALUE ;

SELECT
	ABS( `f_price` ) AS ABS_VALUE,
	CEIL( `f_price` ) AS CEIL_VALUE,
	ROUND( `f_price` ) AS ROUND_VALUE,
	FLOOR( `f_price` ) AS FLOOR_VALUE,
	POW( `f_price`, 3 ) AS POW_VALUE,
	RAND( ) * `f_price` AS RAND_VALUE,
	MOD ( `f_price`, 3 ) AS MOD_VALUE 
FROM
	`fruits`;

-- 使用临时日期函数来创建查询 基本全部用到了上面的函数

SELECT
	CURDATE( ) AS CURDATE,
	CURTIME( ) AS CURTIME,
	NOW( ) AS NOW,
	DATE_ADD( NOW( ), INTERVAL 1 MONTH ) AS DATE_ADD_ONE_MONTH,
	DATE_SUB( NOW( ), INTERVAL 1 MONTH ) AS DATE_SUB_ONE_MONTH,
	UNIX_TIMESTAMP( ) AS UNIX_TIMESTAMP,
	DATE_FORMAT( NOW( ), '%Y-%m-%d %H-%i-%S' ) AS DATE_FORMAT;

-- 创建表

CREATE TABLE `temp_date` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` float(14,2) DEFAULT NULL COMMENT 'age',
  `name` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL COMMENT '生日',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='临时用户表';

 -- 存储过程创建数据

-- 创建生成指定范围的随机数字
DROP PROCEDURE IF EXISTS proc_random_num;
DELIMITER ;

CREATE PROCEDURE proc_random_num(in custom_start_value double(12,2),in custom_end_value double(12,2),out result_value double(12,2))
BEGIN
  DECLARE start_value_p1 double(12,2) DEFAULT 0;
  DECLARE end_value_p1 double(12,2) DEFAULT 0;
  DECLARE num double DEFAULT 0;
  DECLARE num_value double DEFAULT 0;
  DECLARE range_value_t  double DEFAULT 0;
  DECLARE random_value_t  double DEFAULT 0;

  set `start_value_p1`  =  `custom_start_value`  ;
  set `end_value_p1`  = `custom_end_value`  ;
	set range_value_t = `custom_end_value` -  `custom_start_value` ;
  set random_value_t = RAND();
	set num_value =  `custom_start_value` + ROUND(random_value_t * range_value_t) ;
	set result_value = num_value ;
--   SELECT start_value_p1,`end_value_p1` ;
END ;



-- 创建生成指定范围的随机日期


DROP PROCEDURE IF EXISTS proc_random_date;--如果存在此存储过程则删掉
DELIMITER ;
CREATE PROCEDURE proc_random_date(in custom_start_year int(20),in custom_end_year int(20),in custom_start_month int(20),in custom_end_month int(20),in custom_start_day int(20),in custom_end_day int(20),out result_string LONGTEXT)
BEGIN
    DECLARE a1 int default 0;
    DECLARE a2 int default 0;
    DECLARE a3 int default 0;
		DECLARE string_value LONGTEXT DEFAULT '' ;
		-- 调用生成指定范围的数字存储过程
		CALL  proc_random_num(`custom_start_year` , `custom_end_year` , a1) ;
		CALL  proc_random_num(`custom_start_month` , `custom_end_month` , a2) ;
		CALL  proc_random_num(`custom_start_day` , `custom_end_day` , a3) ;
		set string_value = CONCAT_WS( '-', a1 , a2 , a3) ;
		set result_string = string_value ;
END ;
CALL proc_random_date(1890,2022,1,12,1,31,@result);
select @result;

-- 插入指定数量的日期

-- 创建插入数据的存储过程


DROP PROCEDURE IF EXISTS proc_insert_into_temp_date;--如果存在此存储过程则删掉
DELIMITER ;
CREATE PROCEDURE proc_insert_into_temp_date(in custom_value int(20))
BEGIN
    DECLARE i INT DEFAULT 1;
		DECLARE num_value INT DEFAULT 0;
		DECLARE date_value_param VARCHAR(255) ;
		SET num_value = custom_value ;
    WHILE i<=num_value DO
				CALL proc_random_date(1890,2022,1,12,1,31,date_value_param);
        INSERT INTO temp_date(`name`,`age`,`birthday`) VALUES(MD5(UUID()),CEILING(RAND()*100),date_value_param);
        SET i = i+1;
    END WHILE;
END ;

set @result_num = 1000;
CALL proc_insert_into_temp_date(@result_num);

-- 完成数据准备

-- 开始查询

SELECT
	DATE_ADD( `birthday`, INTERVAL 1 MONTH ) AS DATE_ADD_ONE_MONTH,
	DATE_SUB( `birthday`, INTERVAL 1 MONTH ) AS DATE_SUB_ONE_MONTH,
	DATE_FORMAT( `birthday`, '%Y-%m-%d %H-%i-%S' ) AS DATE_FORMAT 
FROM
	temp_date limit 1,30

select IF ( 0.5 > rand(),'大于','小于') as if_value , rand() as rand_value ;

set @input = ROUND(RAND()*10) ;

select case @input 
when 0 then '其它' 
when 1 then '星期一' 
when 2 then '星期二' 
when 3 then '星期三' 
when 4 then '星期四' 
when 5 then '星期五' 
when 6 then '星期六' 
else '星期天' end  as 'column1',@input as value_;


-- 如果 expr1 = expr2 返回 NULL  然后expr1为null那么同样返回null
select NULLIF(1,1) as v1, NULLIF(2,1) as v2  ,NULLIF(null,3) as v3,NULLIF(4,null) as v4,NULLIF(null,null) as v5;


-- IFNULL(expr1,expr2) 如果expr1不是 NULL， 则IFNULL()返回 expr1；否则返回 expr2。
select IFNULL(1,0) as v1 ,IFNULL(null,0) as v2 ;   

select concat(tb_stu.department,'-',tb_stu.name) as name from student as tb_stu;

-- 不适用联结查询的方法
select c_name ,grade from score where c_name = (select c_name from score where grade = 98 )

-- 使用联结的查询方法 (清晰很多)
select tb1.c_name ,tb1.grade from score tb1, score tb2 where 1=1 and  tb1.c_name = tb2.c_name  and tb2.grade = 98;

 -- fruits 中s_id和suppliers中s_id是相同的列名称

 select fruits.*,suppliers.s_name as s_name , suppliers.s_city as s_city from fruits ,suppliers where suppliers.s_id = fruits.s_id

select customers.cust_id,orders.order_num from customers left outer join orders on customers.cust_id = orders.cust_id

select count(id)as count,age,birthday from temp_date group by age ;
select count(id)as count,age,birthday from temp_date group by age having age > 20 and count > 10 ;

-- 取满足条件的

SELECT
	* 
FROM
	tb_school_fraction_info 
WHERE
	1 = 1 
	AND EXISTS ( SELECT * FROM tb_school_fraction_info_base WHERE tb_school_fraction_info_base.school = tb_school_fraction_info.school ) 
GROUP BY
	school 
ORDER BY
	school 
	LIMIT 100;
	
-- 取不满足条件的数据	
	
SELECT
	* 
FROM
	tb_school_fraction_info 
WHERE
	1 = 1 
	AND NOT EXISTS ( SELECT * FROM tb_school_fraction_info_base WHERE tb_school_fraction_info_base.school = tb_school_fraction_info.school AND tb_school_fraction_info_base.id > 2000 ) 
GROUP BY
	school 
ORDER BY
	school 
	LIMIT 100;

mysqld --ngram_token_size=2

[mysqld] 
ngram_token_size=2

n=1: '生', '日', '快', '乐' 
n=2: '生日', '日快', '快乐' 
n=3: '生日快', '日快乐' 
n=4: '生日快乐'

-- 1:创建包含FULLTEXT(全文索引)的表
CREATE TABLE tb_posts (
  id int(11) NOT NULL AUTO_INCREMENT,
  pro_date datetime DEFAULT NULL,
  price_remark varchar(255) DEFAULT NULL,
  title varchar(255) NOT NULL,
  post_content text,
	gmt_created datetime DEFAULT CURRENT_TIMESTAMP,
  gmt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  FULLTEXT KEY post_content (post_content)
);

-- 2:通过 alter table 的方式来添加

ALTER TABLE tb_posts ADD FULLTEXT INDEX ft_index (post_content) WITH PARSER ngram;

-- 3、直接通过create index的方式

CREATE FULLTEXT INDEX ft_index ON tb_posts (post_content) WITH PARSER ngram;

-- 普通短语搜索

SELECT * FROM tb_posts
WHERE MATCH (post_content)
AGAINST ('天津' );

-- 自然语言模式
-- 包含北京关键词
SELECT * FROM tb_posts
WHERE MATCH (post_content)
AGAINST ('北京' IN NATURAL LANGUAGE MODE);

-- 布尔模式
-- 必须包含
SELECT * FROM tb_posts
WHERE MATCH (post_content)
AGAINST ('无锡' IN  BOOLEAN MODE);

-- 通配符搜索
-- 匹配包含北京的字符串前缀开头的数据
SELECT * FROM tb_posts
WHERE MATCH (post_content)
AGAINST ('北京*' );

-- 如果通配符中的前缀术语长于ngram令牌大小，MySQL将把前缀术语转换为ngram短语并忽略通配符运算符
SELECT * FROM tb_posts
WHERE MATCH (post_content)
AGAINST ('北京顺鑫石门国际*' );

-- Create a new stopword table

CREATE TABLE my_stopwords(value VARCHAR(30)) ENGINE = INNODB;


-- Insert stopwords (for simplicity, a single stopword is used in this example)

INSERT INTO my_stopwords(value) VALUES ('Ishmael');


-- Create the table

 CREATE TABLE opening_lines (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
opening_line TEXT(500),
author VARCHAR(200),
title VARCHAR(200)
) ENGINE=InnoDB;


-- Insert data into the table

INSERT INTO opening_lines(opening_line,author,title) VALUES
('Call me Ishmael.','Herman Melville','Moby-Dick'),
('A screaming comes across the sky.','Thomas Pynchon','Gravity\'s Rainbow'),
('I am an invisible man.','Ralph Ellison','Invisible Man'),
('Where now? Who now? When now?','Samuel Beckett','The Unnamable'),
('It was love at first sight.','Joseph Heller','Catch-22'),
('All this happened, more or less.','Kurt Vonnegut','Slaughterhouse-Five'),
('Mrs. Dalloway said she would buy the flowers herself.','Virginia Woolf','Mrs. Dalloway'),
('It was a pleasure to burn.','Ray Bradbury','Fahrenheit 451');


-- Set the innodb_ft_server_stopword_table option to the new stopword table

SET GLOBAL innodb_ft_server_stopword_table = 'test/my_stopwords';


-- Create the full-text index (which rebuilds the table if no FTS_DOC_ID column is defined)

CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);

SET GLOBAL innodb_ft_aux_table='test/opening_lines';

-- 创建表

-- ----------------------------
-- Table structure for user_column_row
-- ----------------------------
DROP TABLE IF EXISTS `user_column_row`;
CREATE TABLE `user_column_row`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '姓名',
  `features` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '特征',
  `value` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '数值',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user_column_row
-- ----------------------------
INSERT INTO `user_column_row` VALUES (2, '小明', '身高', '172.00');
INSERT INTO `user_column_row` VALUES (3, '小明', '体重', '48kg');
INSERT INTO `user_column_row` VALUES (4, '小明', '年龄', '23');
INSERT INTO `user_column_row` VALUES (5, '小红', '身高', '161.00');
INSERT INTO `user_column_row` VALUES (6, '小红', '体重', '40kg');
INSERT INTO `user_column_row` VALUES (7, '小红', '年龄', '19');
INSERT INTO `user_column_row` VALUES (8, '小花', '身高', '153.00');
INSERT INTO `user_column_row` VALUES (9, '小花', '体重', '42kg');
INSERT INTO `user_column_row` VALUES (10, '小花', '年龄', '17');
INSERT INTO `user_column_row` VALUES (11, '小明', '身高', '172.33');
INSERT INTO `user_column_row` VALUES (12, '小军', '身高', NULL);
INSERT INTO `user_column_row` VALUES (13, '小军', '体重', NULL);
INSERT INTO `user_column_row` VALUES (14, '小军', '年龄', NULL);
INSERT INTO `user_column_row` VALUES (15, '小胖', NULL, NULL);
INSERT INTO `user_column_row` VALUES (16, '小胖', NULL, NULL);
INSERT INTO `user_column_row` VALUES (17, '小胖', NULL, NULL);

CREATE TABLE `user2` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '名称',
  `age` double(11,2) DEFAULT NULL COMMENT '年龄',
  `height` double(11,2) DEFAULT NULL COMMENT '身高',
  `weight` double(11,2) DEFAULT NULL COMMENT '体重',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;


-- ----------------------------
-- Table structure for user2
-- ----------------------------
DROP TABLE IF EXISTS `user2`;
CREATE TABLE `user2`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '名称',
  `age` double(11, 2) NULL DEFAULT NULL COMMENT '年龄',
  `height` double(11, 2) NULL DEFAULT NULL COMMENT '身高',
  `weight` double(11, 2) NULL DEFAULT NULL COMMENT '体重',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user2
-- ----------------------------
INSERT INTO `user2` VALUES (2, '小明', 22.00, 48.00, 178.40);
INSERT INTO `user2` VALUES (3, '小胖', 25.00, 50.00, 168.00);

select  id , name ,'年龄' as features,age as value  from user2  union 

select  id , name ,'体重' as features,weight as value  from user2 union

select  id , name ,'身高' as features,height as value  from user2 

-- 这里面有一个常设值 比如 '年龄' as features , '体重' as features  直接挂到列上的  除了这个就没有其它难点了

-- 创建行锁条件
-- 1、表中创建索引， select 。。。 where   字段（必须是索引）  不然行锁就无效。
-- 2、必须要有事务，这样才是 行锁（排他锁）
-- 3、在select  语句后面 加 上    FOR UPDATE；

start transaction ;
select age,birthday from temp_date where 1=1 and age = 60 for update;
-- 休眠5秒
select SLEEP(5);
commit ;

LOCK TABLES table_name [READ | WRITE]
-- 可将表的名称放在LOCK TABLES关键字后面，后跟一个锁类型。 MySQL提供两种锁类型：READ和WRITE

UNLOCK TABLES;

lock table temp_date read;
insert into temp_date(age,birthday) values(102,'2008-04-25') ;

-- 结果
lock table temp_date read
> OK
> 时间: 0s


insert into temp_date(age,birthday) values(102,'2008-04-25')
> 1099 - Table 'temp_date' was locked with a READ lock and can't be updated
> 时间: 0s

-- 继续
select age,birthday  from temp_date;
-- 可以看到 READ锁  是可以读取数据的
-- 释放锁
unlock tables ;

-- 加锁
lock table temp_date write;
insert into temp_date(age,birthday) values(102,'2008-04-25') ;
-- 测试是否还可继续读(从其他会话查询,因为当前会话连接是有锁的)
select age,birthday  from temp_date; 
-- 发现已经被阻塞了
-- 释放锁
unlock tables ;

CREATE TABLE `t_transaction` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `num` bigint DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- 开启一个事务
start TRANSACTION ;
-- 或者使用(非标准sql)
begin ;
insert into t_transaction( `name` ,`num`) values('abc',ROUND(RAND()*100));
-- 事务结束，插入成功
COMMIT ;

START TRANSACTION ;

BEGIN ;

insert into t_transaction( `name` ,`num`) values('kk',ROUND(RAND()*100));
ROLLBACK ;
-- 不会插入一条数据

START TRANSACTION ;

BEGIN ;
insert into t_transaction( `name` ,`num`) values('s1x',ROUND(RAND()*100));
-- 设置回滚点1
savepoint a_point_1;
insert into t_transaction( `name` ,`num`) values('s1',ROUND(RAND()*100));
-- 回滚到指定点
ROLLBACK  to a_point_1;
commit ;

set tx_isolation = ''
-- 设置隔离级别

select @@global.sql_mode

set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'

CREATE TABLE `temp_date` (
  `id` int NOT NULL AUTO_INCREMENT,
  `age` float(14,2) DEFAULT NULL COMMENT 'age',
  `name` varchar(255) DEFAULT NULL,
  `birthday` date DEFAULT NULL COMMENT '生日',
  `gmt_created` datetime DEFAULT CURRENT_TIMESTAMP,
  `gmt_modified` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='临时用户表';

-- 正常操作

SELECT id,age,birthday FROM temp_date ;

-- 奇奇怪怪的东西出来了

SELECT id,age,birthday FROM (SELECT id,age,birthday FROM temp_date ) tb_table ;
-- 注意上面tb_table还必须得加上 否则就报错误 Every derived table must have its own alias 相当于派生表了

-- 相当于FROM 实际可以跟上虚拟的派生表

SELECT 
 r_tab.id,
 r_tab.age,
 r_tab.birthday,
 r_tab.diff ,
 r_tab.diff_content 
FROM

(
SELECT tb_r.id, tb_r.age, tb_r.birthday, (tb_r.age - tb_y.age)as diff ,(CONCAT_WS('-',tb_r.age,tb_y.age))as diff_content 
 FROM 

(
SELECT tab.age,tab.id,(@rownum := @rownum + 1) AS rownum,tab.birthday FROM  temp_date tab,(SELECT @rownum := 0) r_tx
) tb_r 

LEFT JOIN

(
SELECT tab.age,tab.id,(@INDEX_NUM := @INDEX_NUM + 1) AS rownum,tab.birthday FROM  temp_date tab,(SELECT @INDEX_NUM := 0) r_ty
) tb_y

on tb_r.rownum = tb_y.rownum + 1
) r_tab

-- tb_r 和 tb_y比较后形成新的衍生或者派生表 r_tab  然后再把 r_tab 数据查出来

SELECT tb_r.id, tb_r.age, tb_r.birthday, (tb_r.age - tb_y.age)as diff ,(CONCAT_WS('-',tb_r.age,tb_y.age))as diff_content 
 FROM 

(
SELECT tab.age,tab.id,(@rownum := @rownum + 1) AS rownum,tab.birthday FROM  temp_date tab,(SELECT @rownum := 0) r_tx
) tb_r 

LEFT JOIN

(
SELECT tab.age,tab.id,(@INDEX_NUM := @INDEX_NUM + 1) AS rownum,tab.birthday FROM  temp_date tab,(SELECT @INDEX_NUM := 0) r_ty
) tb_y

on tb_r.rownum = tb_y.rownum + 1

-- tb_r 和 tb_y 直接作比较得出数据就可以了 (然后这里用left join就是以tb_r为主)

SELECT tb_r.id, tb_r.age, tb_r.birthday,(tb_r.age + tb_y.age)as add_v, (tb_r.age - tb_y.age)as sub_v , (tb_r.age * tb_y.age)as mul_v,(tb_r.age / tb_y.age)as div_v
 FROM 

(
SELECT tab.age,tab.id,(@rownum := @rownum + 1) AS rownum,tab.birthday FROM  temp_date tab,(SELECT @rownum := 0) r_tx
) tb_r 

LEFT JOIN

(
SELECT tab.age,tab.id,(@INDEX_NUM := @INDEX_NUM + 1) AS rownum,tab.birthday FROM  temp_date tab,(SELECT @INDEX_NUM := 0) r_ty
) tb_y

on tb_r.rownum = tb_y.rownum + 1
-- 利用上面的例子实现加减乘除