关于mysql

1、外键：外键约束不能跨引擎使用，它是为了保证数据的参照完整性
创建外键：CONSTRAINT 外键名称[xxx] FOREIGN KEY(本表中字段名xx) REFERENCES 主表名[被子表外键所依赖表的名称xx]（id主键列）
例如：

crate table tb_emp1
(
	id int(11) primary key auto_increment,//自增
	name varchar(25) not null,//非空约束
	empId int(11) default 111,//默认约束
	grade varchar(25) unique,//唯一性约束
	constraint fk_emp1 foreign key(empId) references tb_emp2(id)
	);

查看表详细结构：show create table xx;
查看表结构:desc xx表名；
修改表名：alter table xx原表名 rename xxx现表名
修改字段数据类型：alter table tb_emp1 modify name varchar(30);
修改字段名：alter table tb_emp1 change grade gradename varchar(25);
添加新字段：alter table tb_emp1 add classname varchar(25);
删除字段：alter table tb_emp1 drop classname;
删除表：drop table is exits tb_emp1;

对于自增主键的理解：在MySQL 5.7的系统中，对于自增主键的分配规则，是由InnoDB数据字典内部的一个计数器来决定的，该计数器只在内存中维护，并不会持久化到磁盘，数据库重启时会按数据库中最大数来处理。在MySQL 8.0中自增变量已经持久化了，每次计数器变化都会将其写入重做日志中，数据库重启时，innoDB会从重做日志的信息来初始化计数器内存中的值。

注意：select '2' = 2; 返回的是1说明2者是相等的，若用字符串和数字进行相等比较，则MySQL自动将字符串转换为数字。其中<=>可以用来判断NULL,<>与!= 、<=、<、>=、>不能用来判断空值NULL。
IS NULL 和ISNULL检验一个值是否为null，IS NOT NULL检验一个值是否为非null。

SELECT NULL IS NULL, ISNULL(NULL), ISNULL(10),  10 IS NOT NULL;
//值分别为1 1  0 1```

//BETWEEN AND运算符
```sql
SELECT 4 BETWEEN 2 AND 5;
//返回值为 1

//LEAST运算符
SELECT least(2,0), least(20,NULL);
//返回为0 NULL,返回最小值，有NULL值存在返回NULL值
//GREATEST返回最大值，有NULL值返回NULL值

//IN （判断操作数是否为IN列表中的值，有则返回1）、NOT IN 运算符
SELECT 2 IN (1,2,4,'thank');
//值为0反之NOT IN一致，操作数或者列表中有一方为NULL的情况下返回结果都为NULL

//LIKE运算符_只能匹配一个字符，%能匹配多个

逻辑运算符：

//所有逻辑运算符的求值结果均为TRUE、FALSE、NULL
//NOT或者!、AND或者&&、OR或者|、XOR逻辑异或
//NOT表示操作数为0时返回值为1，非零值返回值为0，为NULL时为NULL，NOT和！优先级不同，NOT优先级低于"+".!优先级高于"+"运算
SELECT NOT 10, NOT(1-1),NOT -5,NOT NULL,NOT 1+1;
//返回值为 0,1,0,NULL,O

SELECT  !10 .!(1-1).  !-5. ! NULL, ! 1+1;
//返回值0, 1, 0, NULL, 1

//AND或者&& 当所有操作数均为非0值并且不等于NULL时，结果为1，当一个或多个操作数为0时，所得结果为0，其余都为NULL;

//OR或者||:当两个操作数均为非NULL值且任意一个操作数为非零值时结果为1，否则结果为0，当有一个操作数为NULL且另一个操作数为非零值时结果为1，两个操作数均为NULL所得结果为NULL

位运算符：

//按位或|、  按位与&、  按位异或^、   按位左移<<、  按位右移>>、  按位取反~

MySQL函数：

//绝对值函数 ABS（）
//平方根函数 SORT ()
//求余函数 MOD (X,Y)返回x被y除后的余数
//获取整数 CEIL()  CEILING() FLOOR()
//获取随机数 RAND(),当RAND(X)中的有x且一样时，产生随机数一样
//四舍五入 ROUND(X)返回最接近x的整数，对x进行四舍五入 ROUND(-1.67) =-2,ROUND(1.14) = 1
ROUND(X,Y)//返回最接近x的值，其值保留到小数点y位，y为负值则保留x到小数点左边y位 如ROUND(232.38,-1) =230 ,ROUND(232.38,-2) = 200
TRUNCATE(X,Y)//返回被社区至小数点后y位的数字x，若y为负数则如TRUNCATE(12.11,-1) = 10

//字符串函数
CHAR_LENGTH(str)//返回值为字符串str所包含的字符个数。一个多字节字符算一个单字符
CONCAT(S1,S2,...)//合并字符串函数返回结果为连接上参数的字符串，有一个参数为NULL,则返回值为NULL
CONCAT_WS(X,S1,S2,...)//x是其他参数分隔符，如CONCAT_WS('-','1S','2S') = 1S-2S
INSERT(S1,X,LEN,S2)//替换字符串，返回字符串S1,其子字符串起始于X位置和被字符串S2取代的LEN字符,如果x超过字符串长度或者为负数，返回原有字符串。如SELECT('Quest',2,4,'What') = QWhat,INSERT('Qusest',-1,4,'What') = Quest
LEFT(S,N)和RIGHT(S,N)//获取指定长度的字符串，left返回字符串左边的n个字符，right反之
LTRIM(S)//返回左侧空格字符被删除的字符串
RTRIM(S)//返回右侧空格字符被删除的字符串
TRIM(S)//返回删除两侧空格的字符串
SELECT CONCAT('(',TRIM('  BOOK '),')');=(BOOK)
TRIM(S1 FROM S) //删除字符串S中两端所有的子字符串S1
REPLACE(S,S1,S2)//使用字符串S2替代S中所有的字符串S1
SUBSTRING(S,N,LEN)//从字符串S返回一个长度与LEN字符相同的子字符串，起始位置N如SELECT SUBSTRING('BREALFAST',5)=KFAST,SUBSTRING{'BREAKFAST',5,3)=KFA,SUBSTRING('LUNCH',-3)=NCH从右边算起
RECERSE(S)//字符串逆序


//日期函数
CURDATE()和CURENT_DATE()//获取当前日期和获取当前时间
CURRENT_TIMESTAMP()和LOCALITIME()和NOW()和SYSDATE()//返回当前日期和时间值
MONTH(DATE)和MONTHNAME(DATE)//返回对应日期月份
DAYNAME(DATE)和DAYOFWEEK(DATE)和WEEKDAY(DATE)//获取星期

//条件判断函数
IF(expr,v1,v2)//expr为true返回值为v1反之v2
IFNULL(v1,v2)//如v1不为NULL则返回v1,否则返回v2，如IFNULL(NULL,10)=10,IF(1,2)=1
CASE expr WHEN v1 THEN r1 [WHEN V2 THEN r2]...[ELSE rn+1] END//如果expr值等于某个vn，则返回对应位置THEN后面的结果，所有值都不相等，则返回ELSE后面的rn+1 如 SELECT CASE 2 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END;=two

//改变数据类型的函数
CAST(X,AS type)和CONVERT(X,type)//将一个类型的值转换为另一个类型的值,可转换的type有BINARY、CHAR(n)、DATE、TIME、DATETIME、DECIMAL、SIGNED、UNDIGNED

//MySQL 8.0新特性窗口函数 后续在写

查询语句

SELECT <字段列表> 
	FROM <表>
	WHERE<表达式> #在分组之前选择记录
	[GROUP BY]
	[HAVING #在数据分组之后来进行过滤]
	[ORDER BY]
	[LIMIT]

带BETWEEN AND的范围查询：

SELECT XX FROM XX WHERE XX NOT BETWEEN 1 AND 2

分组查询：对数据按照某个字段或者多个字段进行分组，使用GROUP BY函数来对数据分组，而通常与HAVING<条件表达式>来配合使用。且通常和集合函数一起使用如MAX()、MIN()、COUNT()、SUM()、AVG()；

SELECT XX ,COUNT(*) AS TOTAL FROM FRUIT GROUP BY ID WITH ROLLUP;//加上WITH ROLLUP可以计算上面所有数值之和

集合函数：

COUNT(*).COUNT(1)计算表中总行数，不忽略某列中有空值，COUNT（字段名）计算指定列行数，忽略空值的行

连接查询：
1、内连接：

INNER JOIN 使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组成新的记录，也就是说在内连接查询中，只有满足条件的记录才能出现在结果关系中。
如果在一个连接查询中，涉及的两个表都是同一个表，这种查询称为自连接查询。自连接是一种特殊的内连接，它是指相互连接的表在物理上为同一张表，但在逻辑上分为两张表

2、外连接查询：

外连接查询将查询多个表中相关联的行，内连接时，返回查询结果集合中仅是符合查询条件和连接条件的行。有时候需要包含没有关联行中数据，即返回查询结果集合中不仅包含符合连接条件的行，还包括左表、右表或两个边接表中的所有数据行。

3、子查询：

子查询指一个查询语句嵌套在另一个查询语句内部的查询。在SELECT子句中先计算子查询，子查询结果作为外层另一个查询的过滤条件。

ALL关键字的子查询，使用ALL时需要同时满足素有内层查询的条件
> SELECT num1 FROM t1 WHERE num1 > ALL (SELECT num2 FROM t2);返回t1表中比t2表中num2都大的值。

//ANY、SOME关键字表示满足其中任一条件，它门允许创建一个表达式对子查询的返回值列表进行比较只要满足内层子查询的任一个比较条件，就返回一个结果作为外层查询的条件。
> 如SELECT aa   FROM t2  aa > ANY (SELECT bb from t1);返回t2表中所有bb列数据，在将aa与之比较，满足条件则符合查询结果。
带EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，则EXISTS结果返回为true，此时外层查询语句进行查询，反之没有查询到任何行，则返回的结果是false，此时外层语句将不进行查询。
SELECT * FROM t1 WHERE EXISTS 
(SELECT a1 FROM t2 WHERE id = 0)

合并查询结果：

//利用UNION关键字，可以对出多条SELECT语句，并将他们的结果组合成单个结果集。合并时。两个表列数和数据类型必须相同。各个SELECT语句之间使用UNION或UNION ALL关键字分隔。UNION不使用ALL，执行的时候删除重复的记录，使用不删除重复行，也不对结果自动排序。
SELECT column FROM t1 UNION SELECT column From t2

关于别名:表别名只在执行查询的时候使用，并不在返回结果中显示，而列别名定义之后，将返回给客户端显示。

正则表达式：

SELECT * FROM t1 WHERE name REGEXP '^b';//字符^匹配以特定字符或字符串开头的记录
SELECT * FROM t1 WHERE name REGEXP 'y$';//查询以特定字符或字符串结尾的记录
SELECT * FROM t1 WHERE name REGEXP 'a.g';//用符号“.”来替代字符串中的任意一个字符
SELECT * FROM t1 WHERE name REGEXP '^ba*';//星号* 匹配前面的字符任意多次包括0次，+号匹配前面的字符至少一次。
SELECT * FROM t1 WHERE name REGEXP 'on'或 'on|ap';//正则表达式可以匹配指定字符串，只要将这个字符串在查询文本中即可，如要匹配多个字符串，多个字符串之间使用分配符'|'隔开。
SELECT * FROM t1 WHERE id REGEXP '[456]';//方括号指定一个字符集合，只匹配其中任何一个字符 如=124、125、45而 [4-6]表示集合区间所有数字
SELECT * FROM t1 WHERE id REGEXP '[^a-e1-2]';//可组合使用，[^字符集合]匹配不在指定集合中的任何字符，a到e和数字1到2以外的记录。b5,l2,t1
SELECT * FROM t1 WHERE name REGEXP 'x{2,}';//字符串{n}，表示至少匹配n次前面的字符，字符串{n,m}表示匹配前面的字符串不少于n次，不多于m次

索引：

索引用于快速找出某个列中有一特定值的行。不使用索引MySQL必须从第一条记录开始读完整个表，直到找出相关的行。如果查询的列中有一个索引，MySQL能快速到达某个位置去搜寻数据。它是对数据库表中一列或者多列的值进行排序的一种结构，是一个单独的、存储在磁盘上的数据库结构，索引是在存储引擎中实现的，MySQL中索引的存储类型有两种即BTREE和HASH，MyISAM和InnoDB存储引擎只支持BTREE索引，MEMORY/HEAP存储引擎可以支持HASH和BTREE。

CREATE TABLE table_name [col_name data_type] [UNIQUE唯一索引|FULLTEXT全文索引|SPATIAL空间索引][INDEX|KEY都用来创建索引][index_name指定索引名称](col_name创建索引的列名[length表示索引长度只有字符串类型字段才能指定长度])[ASC|DESC指定索引值存储]:
//在创建约束（主键、外键、唯一性）时，在定义约束的同时相当于在指定列上创建了一个索引。

IF语句：

IF val IS NULL 
	THEN SELECT 'val is NULL';
	ELSE SELECT 'val is not NULL';
END IF;

CASE语句：

//条件判断语句，有两种格式
1、CASE: case_expr//表示条件判断的表达式，决定哪个when子句会被执行
	WHEN when_value THEN statement_list
		[WHEN when_value THEN statement_list] ...
		[ELSE statement_list]
END CASE

2、CASE
	 WHEN val is NULL THEN SELECT 'val is NULL';
	 WHEN val < 0 THEN SELECT 'val is less than 0';
	 WHEN val > 0 THEN SELECT 'val is geater than 0';
	 ELSE SELECT 'val is 0';
	 END CASE;

LOOP语句：

//使用loop进行循环操作，id<10将重复执行，>=10跳出循环
DECLARE id INT DEFAULT 0;
add_loop: LOOP
SET id = id +1;
	IF id >= 10 THEN LEAVE add_loop;
	END IF;
END LOOP add_loop;

LEAVE语句：
用来退出任何被标注的流程控制构造

注：在information_schema数据库下的Routines表中，存储所有的存储过程和函数的定义。

视图：

数据库中的视图是一个虚拟表，同真实的表一样，视图包含一系列带有名称的行和列数据。行和列数据来自定义视图查询所引用的表，并且在引用视图是动态生成。
优点：
1、简单化：可以使用经常查询的操作定义为视图，简化操作
2、安全性：通过视图用户只能查询和修改他们所能见到的数据。
3、逻辑数据独立性：视图可帮助用户屏蔽真实表结构变化带来的影响。

//创建视图
CREATE VIEW view_name[(column_list)]//字段
as SELECT_statement//查询语句

MySQL触发器：

MySQL的触发器和存储过程一样都是嵌入到MySQL的一段程序。触发器是由事件来触发某个操作，这些事件包括INSERT、UPDATE、和DELETE语句。如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

//创建触发器
CREATE TRIGGER trigger_name trigger_time(BEFORE|AFTER) 
trigger_event (insert |update|delete) ON table_name 
FOR EACH ROW 
trigger_stmt(触发器执行语句) | BEGIN 触发器执行语句多条 END

MySQL日志：

MySQL日志记录了MySQL数据库日常操作和错误信息。主要分为四类错误日志（MySQL服务启动运行停止出现的问题）、查询日志（记录建立的客户端连接和执行的语句）、二进制日志（记录所有更改数据的语句，可以用于数据复制）、慢查询日志（记录所有执行事件超过long_query_time的所有查询或不使用索引的查询）

MySQL数据库复制（MySQL自带功能，不需要借助第三方工具）操作大致可以分为3个步骤：

1、主服务器master将数据的改变记录到二进制日志（binary log）中，是一个异步的复制过程
2、从服务器salve将主服务master的binary log events复制到它的中继日志（relay log）中
3、从服务器salve重做中继日志中的事件，将数据的改变应用到自己数据库中。

由于项目读和写都由一台数据库承担，压力大数据库服务器磁盘损坏则数据丢失，单点故障。现在可以将写操作（insert update delete）放在master（主库），在数据同步到从库（slave）,而读操作（select）则放在从库， 主从复制。也就是读写分离

实现步骤：
1、提前准备好两台服务器，分别安装MySQL并启动服务成功
配置主库 master
修改MySQL数据库配置文件/etc/my.cnf 
[mysqld]
log-bin=mysql-bin #[必须]启用二进制日志
server-id=100 #[必须]服务器唯一ID
重启MySQL服务
systemctl restart mysqld
登录数据库，执行
GRANT REPLICATION SLAVE ON *.* to 'name'@'%' identified by 'Root@123456';
创建一个名叫name，密码是Root@123456的用户,并且授予该用户REPLICATION SLAVE权限。常用于建立复制时所需要用户权限，也就是slave必须给master授权具有该权限的用户，才能通过该用户复制。


配置从库 slave
修改MySQL数据库配置文件/etc/my.cnf
[mysqld]
server-id=101 #[必须]服务器唯一ID
登录数据库，执行
change master to master_host = '192.168.xxx.xxx',master_user='name',master_password='Root@123456',master_log_file='mysql-bin.000001'[文件名],master_log_pos=439[文件位置];
start slave;
show slave status;查看从数据库状态

读写分离案例：

背景：面对日益增加的系统访问量，数据库的吞吐量面临着巨大瓶颈。对于同一时刻有大量并发读操作和较少写操作类型的应用系统来说，将数据库拆分为主库和从库，主库负责处理事务性的增删改操作，从库负责处理查询操作，能够有效的避免由数据更新导致的行锁，使得整个系统的查询性能得到极大改善。
Sharding-JDBC:
定位为轻量级Java框架，在Java的JDBC层提供的额外服务。它使用客户端直连数据库，以jar包形式提供服务，无需额外部署和依赖，可理解为增强版的JDBC驱动，完全兼容JDBC和各种ORM框架，使用它可以在程序中轻松实现数据库读写分离。
-适用于任何基于JDBC的ORM框架，如：JPA，Hibernate,Mybatis,Spring JDBC Template 或者直接使用JDBC。
-支持任何第三方的数据库连接池，如：DBCP,C3P0,BoneCP,Druid,HikariCP等。
-支持任意实现JDBC规范的数据库。目前支持MySQL,Oracle,SQLServer,PostgreSQL以及任何遵循SQL92标准的数据库。

使用Sharding-JDBC实现读写分离步骤：
1、导入maven坐标
2、在配置文件中配置读写分离规则
spring.shardingsphere.datasource.names= master,slave
//主数据源
spring.shardingsphere.datasource.master
	.type = com.alibaba.druid.pool.DruidDataSource
	.driver-class-name = com.mysql.cj.jdbc.Driver
	.url=数据库路径
	.username= 
	.password=
//从数据源
spring.shardingsphere.datasource.slave
  .type = com.alibaba.druid.pool.DruidDataSource
	.driver-class-name = com.mysql.cj.jdbc.Driver
	.url=数据库路径
	.username= 
	.password=
//读写分离配置
spring.shardingsphere.masterslave.
  //负载均衡 - 轮询
  load-balance-algorithm-type:round_robin
  //最终数据源名称
  name:dataSource
  //主库数据源名称
  mater-data-source-name:master
  //从数据源名称列表，多个逗号间隔
  slave-data-source-names:slave

3、在配置文件中配置**允许bean定义覆盖**配置项
spring.main.allow-bean-definition-overriding=true

存储引擎：

MySQL服务器由SQL层和存储引擎层构成。SQL层的主要功能包括权限判断、SQL解析功能和查询缓存处理等，存储引擎层完成底层数据库数据存储操作。
InnoDB存储引擎：事务性数据库存储引擎，支持事务安全表ACID，支持行锁定和外键。
MyISAM基于ISAM存储引擎，并对其进行扩展，拥有较高的插入、查询速度，但不支持事务。
MEMORY存储引擎：将表中的数据存储到内存中，为查询和引用其他表数据提供较快速访问。

上述节选自MySQL从入门到精通一书