MySQL 数据库

作者: 潜心之力 | 来源:发表于2019-03-08 20:56 被阅读0次

一、数据库操作

CREATE DATABASE IF NOT EXISTS db 
DEFAULT CHARSET utf8 COLLATE utf8_general_ci; -> 创建数据库
DROP DATABASE db; -> 删除数据库
USE db; -> 使用数据库
show *; -> 查询各种数据库信息,*指其他语句,如databases等.
select version() from dual; -> 查询数据库版本

CREATE TABLE IF NOT EXISTS `wjx_table`( -> 创建表
   `id` INT UNSIGNED AUTO_INCREMENT, -> 自动递增
   `name` VARCHAR(100) DEFAULT 'wjx', -> 定义默认约束
   `foreign_id` INT(10) NOT NULL,  -> 定义非空约束
   `comment` varchar(100) COMMENT 'remark', -> 定义备注
   PRIMARY KEY (`id`), -> 定义主键约束
   UNIQUE (`name`), -> 定义唯一约束
   FOREIGN KEY (`foreign_id`), REFERENCES foreign_table(id) -> 定义外键约束
   CHECK (id > 0) -> 定义限制约束
   )ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT 'table_remark';
DROP TABLE wjx_table; -> 删除表
UNSIGNED:将数字类型无符号化
ZEROFILL:当字段值的长度小于定义的长度时,会在值前面补全0。

INSERT INTO table_name (nameN,nameM) VALUES (valueN,valueM); -> 插入
UPDATE table_name SET nameN=valueN,nameM=valueM; -> 更新
DELETE FROM table_name; -> 删除
SELECT * FROM table_name; -> 查询
SELECT * INTO table_backup FROM table; -> 备份
SELECT * INTO table IN 'table_backup.mdb' FROM table -> 拷贝

CREATE UNIQUE(可选) INDEX index_name 
ON table_name (columnN,columnM ASC|DESC(可选)); -> 创建索引

alter_sql = ALTER table table_name; -> 修改表的语句片段
create_constraint_name = CONSTRAINT constraint_name; -> 创建约束别名

add_pk = ADD create_constraint_name(可选,定义别名) PRIMARY KEY (id);
drop_pk = DROP PRIMARY KEY constraint_name(可选,主键别名);

add_fk = ADD create_constraint_name(可选,定义别名) FOREIGN KEY (id) REFERENCES foreign_table(id);
drop_fk = DROP FOREIGN KEY constraint_name(可选,外键别名);

add_check = ADD create_constraint_name(可选,定义别名) CHECK (id>0 AND id<100);
drop_check = DROP CHECK constraint_name(可选,外键别名);

add_unique = ADD create_constraint_name(可选,定义别名) UNIQUE(columnN,columnM); 
drop_unique = DROP INDEX constraint_name(可选,外键别名);

add_default = ALTER column_name SET DEFAULT 'wjx';
drop_defalut = ALTER column_name DROP DEFAULT;

alter_sql + add_*; -> 添加一个约束
alter_sql + drop_*; -> 撤销一个约束

add_column = ADD column_name datatype(size) COMMENT '' FIRST|AFTER column_name(可选); -> 添加列
drop_column = DROP COLUMN column_name; -> 删除列
column_increment_reset = AUTO_INCREMENT=100; -> 修改自动递增的起始值
column_increment_add = change ocn ncn datatype(size) AUTO_INCREMENT; -> 修改为自增列
table_rename = RENAME TO table_new_name; -> 修改表名
column_rename  = change old_column_name new_column_name datatype(size); -> 字段重命名
modify_type = modify column_name datatype(size); -> 修改列的数据类型
modify_binany = modify column_name datatype(size) binany; -> 数据区分大小写
alter_type = ALTER COLUMN column_name datatype(size); -> 修改列的数据类型
alter_sql + * ; -> 执行上述语句

临时表可用于消除多表查询时产生的笛卡尔积,只保留查询的结果集
select temporary.* from (select a.name,b.age) as temporary -> 创建临时表

truncate table `tablename`; -> 重置自增值
alter table `tablename` auto_increment=1; -> 重置自增值

alter table table_name drop id; -> 自增ID重新排序
alter table table_name add id int(11) not null first auto_increment;
alter table table_name modify column id int(11) not null auto_increment;

巧妙设计字段:
1、根据需要存储数据的大小,合理选择数据类型(char、varchar)
2、根据数据的需要,合理设计字段(datatype)的长度(size)
3、长度的大小影响查询数据库的效率,长度越小,效率越快

PREPARE alias FROM 'SELECT * FROM provisionally WHERE id = ?'; -> 预编译
set @id = 1; -> 定义预编译的入参
EXECUTE alias USING @id; -> 执行语句将变量传入到编译完成的语句
DEALLOCATE|DROP PREPARE alias; -> 释放资源

主表:存在主键与其它表关联,并作为表中的唯一标识  -> 无外键
从表:以主表的主键为外键的表,通过外键与主表进行关联  -> 有外键
主键:保持表中每条数据的唯一性  -> 主表或从表
外键:保持外键表中数据的一致性和完整性  -> 从表
外键四大属性
CASCADE:父表进行更新或删除操作,子表对关联数据进行更新或删除.
SET NULL:父表进行更新或删除操作,子表会将外键字段所在列的值设为null.
RESTRICT:删除父表记录时,如果子表中有和父表关联的记录,则父表的记录不能被删除.
NO ACTION:执行效果和RESTRICT类似.

表的水平拆分(表数据拆分):数据存储在多张相同结构的表中
1、单表数据量过大查询效率降低，拆分可避免表高负载的性能瓶颈
2、例:流水记录按年月拆分[order_202001、order_202002、order_202003 ...]
表的垂直拆分(表结构拆分):数据列存储在不同的表,表与表之间通过关联形成一对一关系
1、常用的查询字段放在主表(parent.id)
2、少用的查询字段放在子表(child.id、parent_id)
3、大字段(text、blob)放在子表(child.id、parent_id)

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` MODIFY `', COLUMN_NAME, '` ', DATA_TYPE,
 '(', CHARACTER_MAXIMUM_LENGTH, ') CHARACTER SET UTF8 COLLATE utf8_general_ci', 
(CASE WHEN IS_NULLABLE = 'NO' THEN ' NOT NULL' ELSE '' END),
 ' COMMENT \'',COLUMN_COMMENT,'\';') as 'sql'
FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = 'dbname' AND DATA_TYPE = 'varchar'
AND (CHARACTER_SET_NAME != 'utf8' OR COLLATION_NAME != 'utf8_general_ci');

select table_name,`engine` from information_schema.tables 
where table_schema = 'dbname'; -> 查询某个数据库表的引擎

二、条件运算符

+  加法:select a+b;
-  减法:select a-b;
*  乘法:select a*b;
/ or DIV 除法:select a / b | a DIV b;
% or MOD 取余:select a % b | a MOD b;

=  等于:select a=b;
<> or != 不等于:select a!=b | a<>b;
>  大于:select a>b; -> Java转义(&gt;)
<  小于:select a<b; -> Java转义(&lt;)
>=  大于等于:select a>=b;
<=  小于等于:select a<=b;
BETWEEN  介于两值间:select a between 1 and 10;
NOT BETWEEN  不介于两值间:select a not between 1 and 10;
IN  多个选择条件:select a in(a,b,c,d,e);
NOT IN  不符合多个条件:select a not in(a,b,c,d,e);
<=>  比较两个值是否同时为NULL,成立返回1,否则返回0:select a <=> b;
LIKE  模糊匹配:select a like '_a%';
REGEXP or RLIKE  正则表达式:select a EXGEXP|RLIKE ^a$;
IS NULL  为空:select a is null;
IS NOT NULL 不为空:select a is not null;

NOT or ! 非:select a != b;
AND or & 与:select a and b | a && b; -> Java转义(&amp;)
OR or |  或:select a or b | a || b;
AND OR 联用:select * from db where condition and (condition or condition);
XOR or ^  异或(两个值不相同为1,相同为0):select a^b;
<<  按位右移:select 3 << 1; -> 3的二进制是11,右移1位,即1,结果为1
>>  按位左移:select 3 >> 1; -> 3的二进制是11,左移1位,即111,结果为6
~   按位取反:select ~a;

%  匹配任意长度任意字符
_  匹配单个任意字符
[] 匹配括号里单个任意字符

三、子条件

where      判断条件:select * from table where 1=1
having     条件的延伸,作用于分组,WHERE关键字不能与聚合函数一起使用,如:SUM,AVG,COUNT,MAX,MIN
例:select id from table group by condition HAVING COUNT(*)>10; -> 分组条件过滤用HAVING
例:select count(*) as num from table HAVING num>10; -> 筛选条件为临时列时用HAVING
limit      数量限制:select * from table limit 0,10; -> offset,limit
offset     位置偏移:select * from table offset 10; -> limit ~ offset ~
distinct   除去重复:select distinct * from table;
all        保留重复:select all * from table;
as         别名:select a as A;
union      合并查询结果去重: select * from table_a union select * from table_b
union all  合并查询结果留重: select * from table_a union all select * from table_b
order by   排序:select * from table order by columnN,columnM ASC(升)|DESC(降)
group by   分组:select * from table group by columnN,columnM
with rollup分组基础上进行统计(avg|sum|count):select avg(columnN) from table group by columnM with rollup; -> 不能再使用order_by
inner join 两表交集:select a.* b.* from table_a a inner join table_b b on a.id = b.id
left join  左表为主:select a.* b.* from table_a a left join table_b b on a.id = b.id
right join 右表为主:select a.* b.* from table_a a right join table_b b on a.id = b.id
exists     存在:select * from a where exists (select b.id from b where a.id=b.id); -> 返回TRUE则放入结果集
not exists 不存在:select * from a where not exists(select b.id from b where a.id=b.id); -> 返回FALSE则不放入结果集

INNER和LEFT|RIGHT使用的区别:
INNER:查询表与表之间的关联数据,需要两表的条件同时成立
LEFT|RIGHT:主表的操作能否执行通过从表的数据来确定

EXISTS和IN使用的区别:
IN:子查询的结果集少，主查询表大且有索引
EXISTS:主查询的结果集少，子查询表大且有索引

不使用JOIN方式进行多表查询
select su.username,sr.name FROM sb_user su,sb_role sr,sb_user_role sur
where sur.user_id = su.id and sur.role_id = sr.id

条件分支用法
CASE TOGGLE
  WHEN 0 THEN '关'
  WHEN 1 THEN '开'
  ELSE NULL
END

IF boolean then
ELSEIF boolean then
ELSE
END IF;

四、内置函数

语法:select function(column_name) as aliases from table_name
AVG(column_name); -> 返回指定列的平均值
ABS(column_name); -> 返回指定列的绝对值
CEIL(column_name); -> 返回大于或等于指定列的最小整数
FLOOR(column_name); -> 返回小于或等于指定列的最大整数
GREATEST(a,...,z); -> 返回a-z中的最大值
LEAST(a,...,z); -> 返回a-z中的最小值
CAST('2019-01-01' AS DATETIME); -> 转换数据类型
CONVERT('2019-01-01',DATETIME); -> 转换数据类型
CONVERT('wjx' USING utf8); -> 转换数据编码
COUNT(column_name); -> 返回指定列不为NULL的数目,结果集为空时返回零
COUNT(*); -> 返回表中的记录数,*会自动优化指定到某个字段(索引)
COUNT(DISTINCT column_name); -> 返回指定列的不同值的数目
例:COUNT("主键") > COUNT(1) > COUNT("非主键") -> 执行效率
CONCAT(STR_N,...,STR_M); -> 返回多个字符串连接后的值
CONCAT_WS(separator,STR_N,...,STR_M); -> 可指定连接字符串的分隔符
例:CONCAT_WS('.',STR_N,STR_M);
GROUP_CONCAT(column_name); -> 返回指定列的结果集转换成字符串
例:GROUP_CONCAT(DISTINCT id order by id desc SEPARATOR '.');
FIRST(column_name); -> 返回指定列的第一个记录的值
LAST(column_name); -> 返回指定列的最后一个记录的值
MAX(column_name); -> 返回指定列的最大记录值
MIN(column_name); -> 返回指定列的最小记录值
MOD(X,Y); -> 返回X除以Y的余数
POW(X,Y)|POWER(X,Y); -> 返回X的Y次方
SQRT(X); -> 返回X的平方根
PI(); -> 返回圆周率π
SUM(column_name); -> 返回指定列的总值,常和GROUP BY连用,如分组后每个组的总值
UCASE(column_name); -> 返回指定列的值并转换为大写
LCASE(column_name); -> 返回指定列的值并转换为小写
UPPER(column_name); -> 返回指定列的值并转换为大写
LOWER(column_name); -> 返回指定列的值并转换为小写
MID(column_name,start,length); -> 返回指定列的指定长度字符
LEN(column_name); -> 返回指定列的值的文本长度
POUND(column_name); -> 返回指定列最接近的整数
ROUND(column_name,n); -> 返回指定列保留N位小数的值(四舍五入)
TRUNCATE(column_name,n); -> 返回指定列保留N位小数的值(不四舍五入)
NOW(); -> 返回当前的日期和时间
FORMAT(column_name,n); -> 返回指定列的格式化后的值,保留N位小数
DATE_FORMAT(date,format); -> 日期格式化
RAND(); -> 生成0~1的随机数,可用在随机排序
IF(boolean and/or boolean,"true","false"); -> 可判断多条件,成立返回"true",否则返回"false"
IFNULL(column_name,default_value); -> 判断指定列的值是否为空,是则设置默认值
NULLIF("STR1","STR2"); -> 两个字符串相等返回null,否则返回STR1
ISNULL(expression); -> 判断表达式是否为空,成立返回1,不成立返回0
CONV(15,10,2); -> 将10进制的数字15转变为2进制
COALESCE(a,...,z); -> 在a-z中取出一个不为空的值,如果所有值为空,返回null
SIGN(column_name); -> 返回指定列的符号,负数返回-1,0返回0,正数返回1
UNIX_TIMESTAMP('2019-01-01'|NOW()); -> 将指定时间转换成时间戳
FROM_UNIXTIME(1546272000); -> 将指定时间戳转换成日期格式
CURTIME(); -> 返回当前的时分秒
CURRENT_TIME(); -> 同上等价
CURRENT_TIMESTAMP(); -> 返回当前的时间
LOCALTIME(); -> 同上等价
LOCALTIMESTAMP(); -> 同上等价
DATEDIFF('2019-01-01','2020-01-01'); -> 计算日期之间的天数
DATE('2019-01-01 00:00:00'); -> 返回字符串中的日期
DATE_ADD(NOW(),INTERVAL 1 TYPE); -> 日期加运算
DATE_SUB(NOW(),INTERVAL 1 TYPE); -> 日期减运算
TYPE:MICROSECOND|SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|QUARTER|YEAR
PERIOD_ADD(P,N); -> 增加N个月至周期P,P格式:YYMM|YYYYMM
例:PERIOD_ADD(201901,12); -> 202001
PERIOD_DIFF(P1,P2); -> 返回两个周期间的月数,P格式:YYMM|YYYYMM
例:PERIOD_DIFF(DATE_FORMAT('2020-01-01','%Y%m'),DATE_FORMAT('2019-01-01','%Y%m')); -> 12
WEEK(date); -> 返回日期在一年中的第几周
例:WEEK('2019-01-01'); -> 0
WEEKDAY(date); -> 返回日期在一周中的第几日(0~6)
例:date_sub(curdate(),INTERVAL WEEKDAY(curdate()) + 1 DAY); -> 获取周的第一日
例:date_sub(curdate(),INTERVAL WEEKDAY(curdate()) - 5 DAY); -> 获取周的最后一日
DAY(date); -> 返回日期在一月中的第几日
例:DAY('2019-01-01'); -> 1
MONTH(date); -> 返回日期在一年中的第几月
例:MONTH('2019-01-01'); -> 1
YEAR(date); -> 返回日期的年份
例:YEAR('2019-01-01'); -> 2019
QUARTER(date); -> 返回日期在一年中的第几季度(1~4)
例:QUARTER('2019-01-01'); -> 1
TO_DAYS(date); -> 日期转换为天数
例:TO_DAYS(now()) - TO_DAYS(date) = 0; -> 今天,正数是以前,负数是未来
LAST_DAY(date); -> 返回月份最后的日期
例:LAST_DAY(NOW()); -> 2019-01-31
REPLACE(column_name,by_replaced_char,replaced_char);
例:REPLACE(unix_timestamp(current_timestamp(3)),'.',''); -> 毫秒时间戳
LTRIM(column_name); -> 去除文本左侧空格
RTRIM(column_name); -> 去除文本右侧空格
TRIM(column_name); -> 去除文本左右两侧空格
SPACE(n); -> 返回生成的N个空格
SUBSTRING_INDEX(string,separate,index); -> 字符串通过分隔符取出某段文本
例:SUBSTRING_INDEX('www.baidu.com','.',1); -> www(正数从左往右)
例:SUBSTRING_INDEX('www.baidu.com','.',-1); -> com(负数从右往左)
例:SUBSTRING_INDEX(SUBSTRING_INDEX('www.baidu.com','.',2),'.',-1); -> baidu
SUBSTRING(column_name,index,length); -> 取出字符串中的部分文本
SUBSTR(column_name, index, length); -> 同上等价,1为起始坐标
MID(column_name,index,length); -> 同上等价
REPEAT(column_name,n); -> 返回字符串重复N次的结果
REVERSE(column_name); -> 返回字符串反转的结果
LEFT(column_name,n); -> 返回字符串左侧的N个字符
RIGHT(column_name,n); -> 返回字符串右侧的N个字符
FIND_IN_SET(column_name,set); -> 等价IN关键字取字符串范围
例:FIND_IN_SET(name,'w,j,x'); -> 集合中存在列值则返回该值的下标否则返回零

TIP:聚合函数是用来对分组后的数据做统计运算的

<select id="selectAvgNumber" resultType="java.lang.Double">
    select avg(level) from user 
</select>

日期符号	格式
%S %s	两位数字形式的秒
%i	两位数字的分
%H	两位数字的时(24)
%h %I	两位数字的时(12)
%d	两位数字的日
%e	一位数字的日
%m	两位数字的月
%c	一位数字的月
%Y	四位数字的年
%y	两位数字的年
2019-01-01	%Y-%m-%d

类型转换	描述
BINARY	二进制
CHAR	字符型
DATETIME	日期时间
DATE	日期
TIME	时间
DECIMAL	浮点型
SIGNED	有符号整型
UNSIGNED	无符号整型

五、视图

视图是一张虚拟的数据表，它的数据是从一张或多张表中查询得到的结果，操作视图的方式和操作数据库表的方式一样，视图简化了表数据的查询方式，提升了查询效率，视图屏蔽了表结构带来的影响，无需理会多表间的逻辑关系，视图可以控制表中特定字段的更新操作，提高了数据库的系统安全，常用于固定的多表组合查询。

创建视图
create or replace view view_name(id,name) as
select a.id,b.name from a
inner join b on a.condition = b.condition
with check option;

查询视图
select * from view_name;

查看视图
desc view_name;
show create view view_name;

更新视图
update view_name set name='' where id = 1

删除视图
drop view if exists view_name;

六、存储过程

存储过程是可编程的函数，是一段特定功能的SQL语句，在数据库层面上实现了代码的封装与重用，具有灵活的运用性质。存储过程有三种模式，IN为传入模式，变量作为传参传入存储过程，即使在存储过程内部更改传参的值，外部变量的值也不会随之发生变化，OUT为传出模式，传入的变量接收存储过程处理结果的值，外部变量的值随之发生变化，INOUT为传入传出模式，是上面两种模式的结合体。会话变量使用SET声明，作用于整个会话，局部变量使用DECLARE声明，作用于BEGIN与END之间的语句块。

set @variable=1; -> 定义会话变量,只对当前连接的客户端生效
set GLOBAL all = 'all'; -> 定义全局变量,对所有连接的客户端生效

create procedure procedure_name_in(in param int)
begin 
    declare a int default 0; -> 定义临时变量
    declare b int default 0;
    set a=1,b=9; -> 设置变量的值
    set param=5;
  select (a+b)*param;
end
call procedure_name_in(@variable);
select @variable; -> 数值不会发生变化

create procedure procedure_name_out(out result int)
begin 
  select count(*) into result from a; -> 为变量赋值
  set @variable = (select count(*) from a); -> 为变量赋值
end
call procedure_name_out(@variable);
select @variable; -> 数值发生变化

create procedure procedure_name_inout(inout pr int)
begin 
  set pr = pr + pr;
end
call procedure_name_inout(@variable);
select @variable; -> 数值发生变化

drop procedure if exists procedure_name; -> 删除存储过程

统计个人信用卡的消费情况

DROP PROCEDURE CONSUME;

CREATE PROCEDURE CONSUME(in type int,in startDate varchar(20),in endDate varchar(20))
BEGIN
    DROP TABLE IF EXISTS provisionally;
    CREATE TEMPORARY TABLE provisionally
    (
        type int(11),
        money int(11),
        date varchar(20)
    );
    
    INSERT INTO provisionally(type,money,date)
    SELECT type,SUM(money),date FROM card
    GROUP BY date,type;
    
    set @sql = 'select * from provisionally where (? is null or provisionally.type = ?) ';
    if startDate is not null and endDate is not null then 
        set @sql = CONCAT(@sql,'and provisionally.date BETWEEN ? and ?');
    end if;
    
    set @type=type,@startDate=startDate,@endDate=endDate;
    PREPARE alias from @sql;
    EXECUTE alias USING @type,@type,@startDate,@endDate;
    DEALLOCATE PREPARE alias;
END

CALL CONSUME(null,'2018-01-01','2019-01-01');

DROP PROCEDURE CONSUME;

CREATE PROCEDURE CONSUME(in year int)
BEGIN
    DECLARE january INT DEFAULT 0;
    DECLARE february INT DEFAULT 0;
    DECLARE march INT DEFAULT 0;
    DECLARE april INT DEFAULT 0;
    DECLARE may INT DEFAULT 0;
    DECLARE june INT DEFAULT 0;
    DECLARE july INT DEFAULT 0;
    DECLARE august INT DEFAULT 0;
    DECLARE september INT DEFAULT 0;
    DECLARE october INT DEFAULT 0;
    DECLARE november INT DEFAULT 0;
    DECLARE december INT DEFAULT 0;
    
    DROP TABLE IF EXISTS provisionally;
    CREATE TEMPORARY TABLE provisionally
    (
        january int(11),
        february int(11),
        march int(11),
        april int(11),
        may int(11),
        june int(11),
        july int(11),
        august int(11),
        september int(11),
        october int(11),
        november int(11),
        december int(11)
    );
    
    SELECT IFNULL(SUM(money),0) INTO january FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 01;
    SELECT IFNULL(SUM(money),0) INTO february FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 02;
    SELECT IFNULL(SUM(money),0) INTO march FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 03;
    SELECT IFNULL(SUM(money),0) INTO april FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 04;
    SELECT IFNULL(SUM(money),0) INTO may FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 05;
    SELECT IFNULL(SUM(money),0) INTO june FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 06;
    SELECT IFNULL(SUM(money),0) INTO july FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 07;
    SELECT IFNULL(SUM(money),0) INTO august FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 08;
    SELECT IFNULL(SUM(money),0) INTO september FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 09;
    SELECT IFNULL(SUM(money),0) INTO october FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 10;
    SELECT IFNULL(SUM(money),0) INTO november FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 11;
    SELECT IFNULL(SUM(money),0) INTO december FROM card WHERE LEFT(day,4)=year AND SUBSTRING(day,6,2) = 12;

    INSERT INTO provisionally (january,february,march,april,may,june,july,august,september,october,november,december)
    VALUES (january,february,march,april,may,june,july,august,september,october,november,december);
    
    SELECT * FROM provisionally;
END

CALL CONSUME('2019');

统计课程以日历的形式呈现

DROP PROCEDURE COURSE;

CREATE PROCEDURE COURSE(in month varchar(10))
BEGIN
 DECLARE startDate VARCHAR(10);
 DECLARE endDate VARCHAR(10);
 DECLARE separation INT(11);
 
 set startDate = CONCAT(month,'-01');
 set endDate = LAST_DAY(startDate);
 set separation = ABS(DATEDIFF(startDate,endDate));

 DROP TABLE IF EXISTS provisionally;
 CREATE TEMPORARY TABLE provisionally
 (
  name varchar(20) COMMENT '课程名称',
  address varchar(20) COMMENT '课室地址',
  date varchar(20) COMMENT '上课日期'
 );
 
 WHILE separation >= 0 DO
  INSERT INTO provisionally(name,address,date)
  SELECT name,address,startDate FROM course
  WHERE startDate BETWEEN start_date AND end_date;
  SET startDate = DATE_ADD(startDate,INTERVAL 1 DAY);
  SET separation = separation - 1;
 END WHILE;
 
 SELECT * FROM provisionally;
END

CALL COURSE('2019-01');

构建动态列，需要锁方法，避免死锁

CREATE PROCEDURE Trends()
BEGIN
    DECLARE startIndex int default 1;
    DECLARE endIndex int default 0;
    
    DROP TABLE IF EXISTS provisionally;
    CREATE TEMPORARY TABLE provisionally
    (
        name varchar(64)
    );
    insert into provisionally (name)
    select name from student;
    
    DROP TABLE IF EXISTS provisionally1;
    CREATE TEMPORARY TABLE provisionally1
    (
        id int(11) AUTO_INCREMENT PRIMARY KEY,
        course_id int(11),
        course_name varchar(64)
    );
    insert into provisionally1 (course_id,course_name)
    select id,name from course;
    
    set endIndex = (select count(*) from provisionally1);
    
    WHILE startIndex <= endIndex DO
      set @column = CONCAT('attr',startIndex);
      set @sql = CONCAT('alter table provisionally add column ',@column,' int(11)');
      PREPARE alias from @sql;
      EXECUTE alias;
      DEALLOCATE PREPARE alias;

      set @sql = CONCAT('update provisionally set ',@column,' = (select count(*) from course_student cs
      inner join provisionally1 on cs.course_id = provisionally1.course_id 
      where cs.name = provisionally.name and provisionally1.id = ',startIndex,')');
        
      PREPARE alias from @sql;
      EXECUTE alias;
      DEALLOCATE PREPARE alias;
        
      set startIndex = startIndex + 1;
    END WHILE;

    select * from provisionally;
END

七、索引

索引可以保证数据库表中每行数据的唯一性，可以加速表与表间的连接，可以减少表的检索行数以大大提升数据库的查询效率。索引的存储占用了磁盘空间，增删改操作都会动态地维护索引。

hash：单条查询快，范围查询慢。btree：数据深度层级多时优先使用。
聚集索引：逻辑和物理存储位置都连续，每张表都有唯一的聚集索引，如果表中存在主键，主键就是聚集索引，如果表中不存在主键并存在非空唯一索引，该索引就是聚集索引，如果以上条件都不符合，则生成一个隐藏自增的主键作为聚集索引。
非聚集索引：逻辑上连续，物理存储位置不连续，通过索引节点的数据指针获取数据的存储位置。每张表允许有多个非聚集索引，除了聚集索引外的其它索引都是非聚集索引。
使用场景：主键、外键、WHERE条件、ORDER-BY条件、经常查询的列(覆盖)
不合适场景：数据分布均匀的列(0.1)、经常修改的列

普通索引(INDEX)
CREATE INDEX index_name on TABLE_NAME (column_name);
ALTER TABLE TABLE_NAME ADD INDEX index_name (column_name);

唯一索引(UNIQUE INDEX),要求列的值唯一,允许空值
CREATE UNIQUE INDEX index_name ON TABLE_NAME (column_name);
ALTER TABLE TABLE_NAME ADD UNIQUE INDEX index_name (column_name);

主键索引(PRIMARY KEY),要求列的值唯一,不允许空值,与自增属性(AUTO_INCREMENT)一起使用
alter table table_name add constraint primary key (id);

全文索引(仅MyISAM存储引擎支持)
CREATE FULLTEXT INDEX index_name ON TABLE_NAME (column_name);
ALTER TABLE TABLE_NAME ADD FULLTEXT INDEX index_name (column_name);
例:select * from table where match(column) against('wjx')
('string' in natural language mode); -> 默认是自然语言
('+string*' in boolean mode)； -> 布尔

+ 列中必须包含该关键词,接在关键词的前面
- 列中必须不包含该关键词,接在关键词的前面
> 提高关键词的相关性,接在关键词的前面
< 降低关键词的相关性,接在关键词的前面
* 通配符,接在关键词的后面

全文索引的搜索关键字必须介于最小和最大长度之间,否则失效
show variables like '%ft%'; -> 查看全文索引文本搜索长度的变量
variable_name = variable; -> 修改变量的值
repair table table_name quick; -> 改完变量修复索引

组合索引,多个列联合作为一个索引
CREATE INDEX index_name ON TABLE_NAME (column1,column2,column3);
ALTER TABLE TABLE_NAME ADD INDEX index_name (column1,column2,column3);
解析:创建了column1、column1,column2、column1,column2,column3的三个索引,
第一个组合索引将影响表数据的存储顺序,依据最左匹配原则,条件中必须包含column1才能命中索引,
遇到范围查找[colunm1(between...and、in、like、>、<)],后面的索引会失效(column2、column3)
遇到跨条件索引查找(column1、column3),后面的索引会失效(column2、column3)

删除索引
DORP INDEX index_name ON TABLE_NAME;

索引失效的使用场景

索引列上聚合运算，如：WEEK(build_date) = WEEK(NOW())
索引列上模糊查找，如：like '%key%'(失效) -> like 'key%'(生效)，age != 20(失效) -> (age > 20 and age < 20)(生效)
索引列上类型转换，如：name = 123(失效) -> name = '123'(生效)
索引列上或运算，如：name = 'wjx' or age = 20，name和age必须同时建立索引才能命中索引，否则索引失效。
索引列上非运算，如：age != 20(失效) -> age > 20 or age < 20
索引列上空校验，如： name is null , name is not null

MyISAM存储引擎

特点：访问速度快，不支持事务和外键，数据表以INSERT和SELECT操作为主可以选用该引擎，是MySQL的5.5版本前的默认引擎。
磁盘存储：假设数据表名为table，table.frm存放表结构，table.MYD存放表数据，table.MYI存放表索引。

MRG_MYISAM存储引擎

特点：能把一组结构相同的MyISAM数据表联合为一个单元对外提供CRUD操作，轻松实现MyISAM数据表水平分表的方案。

create table table1(
  id int(11) primary key AUTO_INCREMENT,
  name varchar(64)
)engine= MyISAM;

create table table2(
  id int(11) primary key AUTO_INCREMENT,
  name varchar(64)
)engine= MyISAM;

create table table3(
  id int(11) primary key AUTO_INCREMENT,
  name varchar(64)
)engine= MRG_MYISAM UNION=(table1, table2) INSERT_METHOD=LAST;

INSERT_METHOD: 0不允许插入表，FIRST插入到UNION第一张表，LAST插入到UNION最后一张表

InnoDB存储引擎

特点：支持事务和外键，数据表适合各种CRUD操作。
事务操作：开始事务(begin;)，设置保存点(savepoint point_name;)，回滚至保存点(rollback to point_name;)，回滚事务(rollback;)，提交事务(commit;)。
事务隔离级别：原子性，事务是不可分割的最小单位，事务执行前后的总结果不变。一致性，事务执行前后数据库的完整性约束没有被破坏。隔离性，不同事务间的操作相互不受影响。持久性，事务提交后，结果将更新到数据表，不可撤回提交。
磁盘存储：段(256M)、区(1M)、页(16K)、行，数据结构是B+Tree。

查询执行计划：explain select * from table

关键字	描述
id	值越大优先级越高，相同级别从上往下执行
select_type	查询类型，SIMPLE：不包含UNION或子查询，PRIMARY：最外层查询，UNION：紧接UNION后的查询，DEPENDENT UNION：紧接UNION后的查询依赖外层条件，UNION RESULT：合并查询的结果，SUBQUERY：第一个子查询，DEPENDENT SUBQUERY：第一个子查询依赖外层条件，DERIVED：FROM后接子查询用临时表存放结果
table	数据来源表
partitions	数据是否以分区存放，有则输出分区的信息
type(*)	访问类型，system（系统表）> const（WHERE条件唯一索引） > eq_ref（JOIN条件唯一索引） > ref（普通索引） > fulltext（全文索引） > ref_or_null（索引或空） > index_merge（索引合并） > unique_subquery（子查询唯一索引） > index_subquery（子查询普通索引） > range（范围查找） > index（全索引扫描） > all（全表扫描）
possible_keys	查询中可能使用到的索引
key	查询中实际使用到的索引
key_len	查询中实际使用到的索引长度，如：int = 4
ref	WHERE子句中列的查找范围，如：id = 1，数值1就是范围，由于是常量，此时输出const。
rows	预计获取结果集所需要扫描表的行数
filtered	Server层过滤后剩余的数据占存储引擎查出的数据的百分比
extra(*)	额外信息，Distinct：发现匹配行后，停止搜索相同的行。Using index：数据列从覆盖索引中读取无需回表。Using index for group-by：分组条件使用覆盖索引。Using filesort：使用内部排序，通过为排序列建立索引或[order by null]优化。Using temporary：使用临时表，除分组(group by)外通过连表(join)消除。Using where：索引没有覆盖查询列，需要通过记录行回表获取列信息。Using join buffer (Block Nested Loop) ：连表使用缓存，通过为连表条件建立索引优化。Using index condition：WHERE子句中使用了索引。

八、备份与恢复

C: -> 备份(backup.bat)

CD C:\Program Files\MySQL\MySQL Server 5.7\bin

SET "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"

mysqldump -uroot -p123456 --host=localhost --port=3306 wjx > D:\WJX%Ymd%.sql

C: -> 恢复(restore.cmd)

CD C:\Program Files\MySQL\MySQL Server 5.7\bin

SET "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"

mysql -uroot -p123456 wjx < D:\WJX%Ymd%.sql

WINDOWS系统的定期计划

控制面板 -> 管理工具 -> 任务计划程序 -> 创建基本任务

MYSQL服务

WIN+R，输入services.msc确定，在服务列表中找到MYSQL57。
WIN+R，输入cmd确定，输入net start mysql57启动服务。
WIN+R，输入cmd确定，输入net stop mysql57关闭服务。

安装多个数据库

主数据库：首次安装数据库使用安装包依照安装教程步骤完成操作。
从数据库：把主数据库的磁盘文件从D盘复制到F盘，D:\Program Files\MySQL Server 5.7和D:\ProgramData\MySQL\MySQL Server 5.7到F:\Program Files\MySQL Server 5.7和F:\ProgramData\MySQL\MySQL Server 5.7，这里包括MySQL软件的存放路径和数据存放路径。
配置文件：复制D:\ProgramData\MySQL\MySQL Server 5.7\my.ini到F:\Program Files\MySQL Server 5.7\my.ini，文件只需修改下列配置。

[client]
port=3307

[mysqld]
port=3307
basedir="F:/Program Files/MySQL/MySQL Server 5.7/"
datadir="F:/ProgramData/MySQL/MySQL Server 5.7\Data"
server-id=2

UUID：每个MySQL都有唯一的ID，修改从数据库的UUID来确保唯一性，F:\ProgramData\MySQL\MySQL Server 5.7\Data\auto.cnf。
注册服务：命令行操作，f: -> cd F:\Program Files\MySQL Server 5.7\bin -> .\mysqld --install mysql58 -> net start mysql58 -> .\mysql -uroot -p -> 123456 -> 完成数据库登录。

主从复制（master-slave）

主数据库完成下列操作（MySQL57）

修改配置：编辑D:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件，在[mysqld]标签下写入行server-id=1和log-bin=mysql-bin和binlog-do-db=master，其中master是作为同步的数据库。
进入界面：打开MySQL提供的CommandLineClient，在主数据库完成下列指令。

create user 'wjx'@'localhost' identified by '123456'; -> 创建账号
grant replication slave on *.* to 'wjx'@'localhost'; -> 分配权限
flush privileges; -> 刷新权限
show master status; -> 查看状态

master-status

从数据库完成下列操作（MySQL58）

修改配置：编辑F:\ProgramData\MySQL\MySQL Server 5.7\my.ini文件，在[mysqld]标签下写入行server_id=2和log-bin=mysql-bin和replicate-do-db=slave，其中slave是作为同步的数据库。

change master to master_host='localhost',master_user='wjx',
master_password='123456',master_log_file='mysql-bin.000002',
master_log_pos=436; -> 关联主数据库
stop slave; || reset master; -> 关联失败时可重置状态

show slave status \G -> 查看状态
start slave; -> 开启主从复制
stop slave; -> 关闭主从复制

slave-status

主从测试：首先手动同步主从数据库，其次对主数据库进行CRUD操作，最后观察从数据库的信息，如实时更新了证明主从复制配置已经生效了。

异常收集，Slave_Io_Running：No

stop slave;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
show slave status \G

异常收集，Slave_SQL_Running：No

stop slave;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=0;
show slave status \G

读写分离（MySQL-Proxy）

下载安装：从官网下载压缩包，解压到目录D:\Program Files\MySQL\mysql-proxy-0.8.5。
代理配置：创建文件并写入下列配置，D:\Program Files\MySQL\mysql-proxy-0.8.5\mysql-proxy.conf。

[mysql-proxy]
admin-username=root
admin-password=123456
admin-lua-script="D:\Program Files\MySQL\mysql-proxy-0.8.5\lib\mysql-proxy\lua\admin.lua"
proxy-backend-addresses=localhost:3306
proxy-read-only-backend-addresses=localhost:3307
proxy-lua-script="D:\Program Files\MySQL\mysql-proxy-0.8.5\share\doc\mysql-proxy\rw-splitting.lua"
log-file="D:\Program Files\MySQL\mysql-proxy-0.8.5\mysql-proxy.log"
log-level=debug
daemon=true
keepalive=true

运行日志：创建日志文件，D:\Program Files\MySQL\mysql-proxy-0.8.5\mysql-proxy.log。
代理启动：创建文件并写入下列配置，D:\Program Files\MySQL\mysql-proxy-0.8.5\bin\start.bat，运行后结果为 (critical) plugin proxy 0.8.5 started。

mysql-proxy -P localhost:3305 
--defaults-file="D:\Program Files\MySQL\mysql-proxy-0.8.5\mysql-proxy.conf"

九、SQL赏析

问题:数据分组后排序失效
解决:先将需要分组的数据用临时表存储,后在临时表的查询语句加上LIMIT关键字
select temporary.* from (select a.*,b.* from a 
inner join b on a.condition = b.condition
order by a.condition asc,b.condition desc limit 9999) temporary
group by temporary.condition order by temporary.condition desc

select id,parent_id from table_a
union all -> 两条语句的结果集合并
select id,parent_id from table_b

select a.name,(select b.age from b where b.aid = a.id) from a; -> 嵌入子查询

select a.name,b.age from a -> 动态连表
left join b on a.id = b.aid and b.age = ＃{age}

子查询多行多列
select a.* from a where (a.id,a.age) = (select b.id,b.age from b);

某个条件有多种情况成立
select * from a where a.age = 20 and (a.id = 1 or a.level = 1);

统计每个课程学生年龄为20和非20的数量
select a.num,b.num from
(select c.id,count(*) as num from curriculum c
inner join student s on c.student_id = s.id
where s.age = 20 group by c.id) as a
left join 
(select c.id,count(*) as num from curriculum c
inner join student s on c.student_id = s.id
where s.age != 20 group by c.id) as b
on a.id = b.id

读取两张表的数据并写入形成一张新表
insert into table_a(name,age) select table_b.name,table_c.age
from table_b inner join table_c on table_b.aid = table_c.aid

把一张表的数据更新至另一张表
update table_a a inner join table_b b
on a.id = b.aid set a.name = b.name
where condition -> 设置可更新的条件

update table_a a set -> 嵌套子查询
a.name = (select b.name from table_b b where b.aid=a.id)

数据更新时会锁表,子查询当前表遭拒绝,创建临时表解决
update table_a a set a.name =
(select name from (select aa.name from table_a aa) as `temporary`)

更新当前表分组后的数据
update table_a a inner join 
(select a.id a_id,a.name a_name,b.id b_id,b.name b_name from table_a a
inner join table_b b on a.cid = b.cid group by a.id having count(*) = 1) provisionally
on a.id = provisionally.a_id set a.name = provisionally.b_name

用另一张表作为删除条件
delete a from a left join b on a.bid = b.id where b.id is null