mysql数据库

MySQL数据库

MySQL，使用最常用的结构化查询语言(SQL)进行数据库管理(CURD)

MySQL常用命令

1. 连接到mysql服务器

   mysql -h[服务器地址/IP] -p[端口号] -u<用户名>  -p<密码>
   
   -h：目标数据库服务器地址名称或IP地址，本机可以省略此参数
   -P：访问应用程序端口号（P必须大写），本机可省略
   -u：登录数据库服务器用户名，必须写参数
   -p：登录用户密码，必须写参数
   

2. 查看MySQL数据库信息

   > show databases\g
   > show databases;
   

3. 使用目标数据库

   > use databases_name
   > use mysql; --使用mysql系统数据库
   

4. 查看当前数据库中所有表名称

   > show tables;
   

5. 查看数据字符编码集信息

   > show variables like 'character%';
   

6. 查看命令帮助信息

   > ? 或 \? 或 help
   

7. 看成当前已登录的用户

   > select user(); 或 select current_user;
   

8. 查看数据库表结构信息

   > desc table_name;
   > desc db;
   

关系型数据库

一行一条记录，一列一个字段。

创建MySQL数据库

1. DDL 数据定义语言，主要用来创建修改以及删除数据库对象，包括creat，drop，alter …
2. DML 数据操纵语言,主要用来对表中的数据进行增删改操作,包括insert,update,delete…
3. DQL 数据查询语言,主要用来查询数据库表中业务数据,通常包括selec…
4. DCL 数据控制语言，对数据访问权进行控制，包括授与，回收权限操作，主要包括grant以及revoke
5. TCL 事物控制语言，主要在执行事物是使用实现对事物的控制，包括commit，Rollback以及savepoint

数据库创建

• CREATE DATABASE  if NOT EXISTS dbName
      DEFAULT  CHARACTER  SET 'utf8'
      DEFAULT COLLATE 'utf8_general_ci'
      DEFAULT ENCRYPTION 'Y'
  ;
  

删除数据库

• --如果数据库存在则删除此数据库
  DROP DATABASE if EXISTS dbName; 
  
  --如果存在，则删除testdb 
  DROP DATABASE if EXISTS testdb;
  
  --提交操作
  Commit ;
  
  

MySQL数据库常用数据类型

1. 整型 (unsigned 约束不允许负值，可以使正数的上限提高一倍+1)

   TINYINT(微整形)，SMALLINT(短整型)，MEDIUMINT(中整型) ，INT(普通整型)，BIGINT(大整型)

2. 浮点型

   FLOAT(单精度),DOUBLE(双精度), DECIMAL(精确小数) （M 整个长度，D 小数位数）

3. 字符型

   CHAR （定长）, VARCHAR（变长）, BINARY, BLOB, LONGBLOB, TEXT, LONGTEXT

4. 日期型

   DATE,TIME,YEAR,DATETIME,TIMESTAMP(时间戳)

5. BIT型 位类型 二进制码对应的ASCII码

6. JSON型

   

创建MySQL数据库创建表

1. 创建数据库表原则

   • 考虑遵循关系型数据库设计三大范式
   • 根据业务数据确定表以及表字段（列）
   • 考虑尽量减少数据冗余
   • 命名规范
   • 尽可能使用能够满足存储要求最小的数据类型
   • 考虑数据执行操作的效率

   --使用SQL建立数据库表
   CREATE TABLE 'tableName'(
   'col_name'  TYPE [(len)]   other OPTIONS  COMMENT  '注释说明',
   'col_name2'  TYPE [(len)]   other OPTIONS  COMMENT  '注释说明',
   ...  ...
   )
   --可用可不用
   [
   COLLATE = 'utf8mb4_0900_ai)ci'
   ENGINE = INNODB
   AUTO_INCREMENT  = 13};-- 自动标识主键
   
   ]
   例：
   CREATE TABLE goodsInfo
   (
     id BIGINT  NOT NULL COMMENT 'id 行号,不重复',
     goodsCode VARCHAR(64)  NOT NULL,
     NAME VARCHAR(32) NOT NULL,
     unit CHAR(2) NULL ,
     price DECIMAL(12,2) NULL,
     quantity INT  ,
     buyDate  DATE,
     address VARCHAR(64)
   
   )
   

2. 关系型数据库设计三大范式

   建立冗余较小，结构合理的数据库。

   • 第一范式（确保每列保持原子性）

修改，删除数据库表

Alter table_name<add|modify|drop|rename to|change..>

ALTER TABLE test_tab RENAME TO  testTab; --重命名表
ALTER TABLE test_tab Add[column] describes varchar(1024) null; --添加新列
ALTER TABLE test_tab modify describes varchar(1000)  default '备注信息'   --修改列
ALTER TABLE test_tab change describes beizhu varchar(2000); --重命名列
ALTER TABLE test_tab  DROP column beizhu ;  -- 删除列
drop table test_table;  -- 删除表

数据完整性

关系型数据库对于数据完整性主要通过最基本的三个完整性要求进行约束，以达到保证数据的正确，安全，一致。

1. 实体完整性

   表中的每条记录必须确保是唯一实体数据，为确保记录行有区别，必须为表设置能够明显区分不同记录行字段并确保其值是唯一的，通常这个字段被称作主键或行标识。（主键中的值不允许重复，没有意义的属性）

   • 主键字段值是不允许为空和出现重复的数据值

   • 创建主键约束

     建表的同时创建主键约束
     create table table_name(
         id  varchar(32) not  null primary key,
         .......
         [primary key(id)]
     )
     
     建表后通过修改表添加主键约束
     alter table table_name add constraint  PK_id primary key(id)
     
     自动维护主键
     使用 auto_increment
     create table table_name(
         id  bigint primary key auto_increment,  --自动标识主键
       
     )
     
     --删除主键
     可使用alter table table_name drop constraint PK_id
     

2. 域完整性

   字段的限制(例如年龄不能为负)，

   • 使用检查约束

     alter table tab_name add constraint constraint_name check (colu_name <options>)
     
     alter table tab_name add constraint ck_age check (age>14);
     alter table tab_name add constraint ck_age2 check (age>=14 and age < 20);
     alter table tab_name add constraint ck_age3 check (age between 18 and 45);
     alter table tab_name add constraint ck_age4 check (age<> -1); -- <> or !=   不等于号
     alter table tab_name add constraint ck_sex check (sex='男' or sex = '女');
     alter table tab_name add constraint ck_birth check (birth between '1900-1-1' and '2020-1-1');
     
     --模糊限制 % 表示任意字符任意长度  - 表示一个字符
     alter table tab_name add constraint ck_naem  check (name like '% . %');
     
     alter table tab_name add constraint ck_account check (account like '1001%');
     alter table tab_name add constraint ck_addre check (addre in('本市','河北','怀柔','保定')); not in
     alter table tab_name add constraint ck_identity  unique  (identity);  --唯一约束
     

3. 参照完整性

   两个表之间关联关系的一种数据约束，如学生和成绩的关系。

   • 创建外键约束

     create table emp(
       depid bigint not null,
         foreign  key(depid) references testtable(id)
     )
     
     
     
     alter table emp add constraint   fk_depid foreign key(depid)  references testtable(id);
     

关系型数据库中表之间的关系可以存在三种关系

1. 一对多
2. 一对一
3. 多对多

添加修改和删除(DML)

1. insert

   insert [into] table_name[(colu1,colu2,colu3....)] values (val1,val2,val3)  --括号里面是字段名称以及属性值	
   
   insert into goodsType (id,code,name,shortName,status) values(default,'GTC1002349448496','食品安全','AQSP',1);
   

   转义字符 单引号：\’ 双引号：\‘’ 正斜线：\\ 回车符：\r 或 \n Tab键： \t

2. update

   Update  table_name Set colu1 = val1[,colu2 = val2.....]  [where expression]
   

3. delete

   delete from table_name [where expression]
   

4. where

   1. 运算符号

      =   等于（where id = 23）
      !=  不等于（age != 45 或 age <> 45）
      >,<,>=,<=  
      and  并且 （where id = 12 and  age <> 15）
      or   或者  (where sex = '男' or age >= 18)
      between and 从x至y之间 （where birth between '1980-1-1' and '2022-1-1'）
      in   在X范围内（where address in（'北京'，'上海','长沙'）    --not in 相反
      like  模糊匹配（where name like '张%'） --not like 相反
      is null 是空的 （where email is null  and age > 45）   --is not null 相反
      

查询

select基本查询

select <*(表示所有信息)|cols1,cols2....>  from  table_name;

select * from  testdb; -- 查询testdb表中所有数据

select id, name ,age,sex birth from testdb  -- 查询指定字段

select distinct sex from testdb  -- 去除重复的查询数据

使用where语句查询

where 子句在select语句中将限制被查询的数据，where子句后表达式必须计算得到布尔值，只有符合where结果为真的数据才会被返回

select * from testdb where age >= 18

select * from testdb  where birth between  '1990-01-01'  and '1999-01-01'

select name , age from testdb  where name like '张%'   and sex = '男'

select name from  testdb where sex = '女'  and address in ('本市','保定')

查询排序

• mysql中使用order by 实现查询结果的排序，order by 子句通常在查询语句的最后面，必须为order by 设置排序依据字段，mysql也支持多字段排序
• mysql使用asc（升序） 和DESC（降序）为参与排序的字段设置排序规则，ASC是默认的自然排序可以省略，DESC必须显示
• mysql还支持以字段在表结构中的位置索引来设定，以简化复杂字段的书写麻烦

order by  age  -- 按照age升序排列查询结果
order by age DESc  -- 按照降序排列查询结果
Order By age DESC，height -- age按照降序排列，height 按照升序排列
order by  1,2   -- 按照列的索引排列

常用聚合函数

count([distinct] exper ) 统计 exper 表达式不为空的条目数 ，返回bigint ，distinct 用来去除重复数据 ，无匹配返回0

Avg([distinct] exper ) 统计 exper 表达式不为空的平均数 ，返回实数 ，distinct 用来去除重复数据 ，对于非数值表达式avg无意义，结果为0；

Sum([distinct] exper ) 统计 exper 表达式不为空的总和 ，distinct 用来去除重复数据 ，对于非数值表达式sum无意义，结果为0

max()[distinct] exper ) 统计 exper 表达式不为空的最大值，distinct 用来去除重复数据

min()[distinct] exper ) 统计 exper 表达式不为空的最大值，distinct 用来去除重复数据

SELECT COUNT(*) '学生数量' FROM student WHERE cid = 1;
SELECT avg(grade)  FROM score;
SELECT sum(grade)  FROM score;
SELECT max(grade) , min(grade) FROM score;

分组聚合

select [polyFun1,polyFun2,polyFUn3.....]  from  table_name [where ....]
group by col1[,col2....] [order by]

select  sum(grade)  总分 from score  group by stuid;

分组聚合筛选统计查询

select [polyFun1,polyFun2,polyFUn3.....]  from  table_name [where ....]
group by col1[,col2....] [having exper] [order by]



SELECT subid ,  AVG(g.score)
FROM grade g
GROUP BY g.subid
HAVING  AVG(g.score) < 170

• having 子句用来在使用Group by 分组聚合中的条件筛选
• having 只能对参与分子的字段或者聚合结果进行操作，未参与聚合的字段且未使用聚合函数不能出现在having的表达式中
• having 后的表达式满足为真的聚合结果才会被返回

高级查询

查询分页

目的是保证数据加载速度和避免内存过载的必要处理机制

limit 使用其后的1个或2个数字实现限制符合条件的数据行
limit m  限制返回最多m条记录
limit i, m 从i索引行开始，限制最多返回m条记录


limit 分页公式：
开始索引——页码数*每页数量-每页数量
加载行数——每页数量


select * from person limit 0,5  -- 第一页（1*5-5,5）
select * from person limit 5,5  -- 第二页（2*5-5,5）
select * from person limit 10,5 -- 第二页（3*5-5,5）

表连接查询

是根据业务场景组合多个数据库表信息显示最终数据的查询机制

1. 内连接（等值连接）

   内连接使用sql标准的 inner join on 关键字构建连接查询语句

   select  .....
   from ...
   inner join  ...
   on (条件)
   
   
   select d.id 部门编号 , d.name 部门名称 ,e.id 员工id ,e.name  员工姓名,birth 生日
   from department d   inner  join  employee  e on  d.id = e.did  ;
   
   

2. 外连接查询

   保证外表中的数据必须返回，无论是否满足与其他表的连接条件。

   左外连接 left outer join

   右外连接 right outer join

   outer 关键字可以省略

   select  d.id ,d.name , e.id ,e.name 
   from  department d 
   left outer
   join employee e  on d.id = e.did;
   

3. 交叉连接

   使用Cross join 构建交叉连接语句；mysql中不支持权连接（Full join）， 可使用cross join 无条件实现笛卡尔积结果

   select d.id 部门编号 , d.name 部门名称 ,e.id 员工id ,e.name  员工姓名,birth 生日
   from department d   cross  join  employee  e on  d.id = e.did  ;
   

4. 连接查询使用using子句

   使用using（colu1,colu2,coluN…）在连接查询进行了扩展。括号内的列名必须是需要表中都具有的同名字段，连接查询中将返回这些相同名称记录组合。

   mysql  v8版本使用using对连接进行扩展
   select  d.id ,d.name , e.id ,e.name 
   from  department d 
   left outer
   join employee e  
   using (name);
   

5. 多表连接查询

   -- left outer 连接
   select d.name ,e.name,s.should 应发,s.actual 实发,s.deduct 扣款 , concat(s.year) 年份 ,s.moth 月份
   from deparment d 
   left  join employee e  on  d.id = e.did
   left  join salary s on  s.eid = e.id
   order by s.months,d.name;
   
   
   -- 内连接
   select d.name ,e.name,s.should 应发,s.actual 实发,s.deduct 扣款 , concat(s.year) 年份 ,s.moth 月份
   from deparment d inner  join (employee e  ,salary s)
   on d.id = e.did and s.eid = e.id
   order by s.months,d.name;
   
   
   -- cross 连接
   select d.name ,e.name,s.should 应发,s.actual 实发,s.deduct 扣款 , concat(s.year) 年份 ,s.moth 月份
   from deparment d  cross  join (employee e cross join salary s)
   on d.id = e.did and s.eid = e.id
   order by s.months,d.name;
   

子查询

• 子查询是一个select查询语句中内嵌的select语句，常做为外部select语句的查询参考条件，子查询也被称为内嵌查询。

• 查询顺序由内向外，效率相对较低

• 子查询通常需要建立临时表并在查询操作结束后删除临时表，子查询通常效率比较低

  子查询语法
  
  select  。。。。 from  (select   ...... from (select .... from table_name) table_name)
  

最基本的单值标量子查询

select  upper((select 'black  and   blue'))   小写变大写
lower()  大写变小写

子查询比较应用

子查询常用的方式就是查询比较，通常在比较中使用如下运算符：

= > < >= <= <> != <=>

select   name  
from  employee  e  where  e.id = (
select  s.eid  
    from salary s  where s.years = 2019 and  s.moths = 9  and  s.deduct <>0
    order  by  s.deduct
    desc limit 1
)


select  name  from  employee e where e.mail <=>
(select e2.mail from  employee e2 limit 1)

子查询中使用any，all，some

子查询中比较运算中使用any ， all， some 可以返回多条记录

select  name  from  employee where  id = any (select  eid  from  salary)

select  name  from  employee where  id in (select  eid  from  salary)
in是标准sql子查询形式，any是mysql特征

子查询中使用exists

exists标识T是否存在X当中，如果存在则返回true

select name from employee e where exists (select * from  salary  s  where  e.id = s.eid)  -- 有薪水记录的人


select name from employee e where not exists (select * from  salary  s where  e.id = s.eid) -- 没有薪水记录的人

联合查询

联合查询需要满足N个结果都具有相同的字段的数量才能进行合并，但并不要求对应的字段一定具有相同的数据类型和长度。

union ， union all ，union distinct

select   ....
union [all|distinct] select ...    
-- all(所有数据)  |  distinct(去除重复数据)   不能同时使用。  


[union [all | distinct] select ....]

distinct 是显示将完全重复的记录行去重而保留唯一，使用union隐式去重，使用all则不去重复记录



-- union常量演示
select  100,'李连杰'  union  select  'ling' ,90
SELECT 1 f1,2 f2 UNION  SELECT 23,15; -- 查询结果集字段数量必须保证相同的

-- 使用union 默认隐式去重（distinct）
SELECT 'admain'  姓名  , 18   'age' UNION  SELECT 'admain'  姓名  , 18   'age';

-- 若需要完全显示则使用union  all 
SELECT 'admain'  姓名  , 18   'age' UNION ALL   SELECT 'admain'  姓名  , 18   'age';

预定义字符串函数

预定义日期时间函数

预定义数学函数

预定义AES加密函数概述

相对安全通常使用：CFB包括（CFB1，CFB8，CFB128）

设置块加密模式 set block_encryption_mode = ‘aes-[128|256] - [ecb|cbc|cfb…]’

预定义MD5加密函数应用

MD5(password)对给定的password密码文本执行不可逆加密，返回32个十进制的数字字符串

insert  into  users(name,password)  valus ('superking',MD5('king990_$'));-- 加密


select name  from users where name = 'admin'  and  password = MD5('king990_$'); -- 核对密码

select  password usres where name = 'superking';

预定义RANDOM_BYTES加密函数应用

RANDOM_BYTES（len） 利用ssl随机生成len(1-1024)字节长度的二进制字符串，通常在做为AES_ENCRYPT和AES_DECRYPT函数向量使用，其len应在16之内，大于此上限值将被忽略。

SET block_encrypt_mode = 'aes-128-cfb8'; -- 设置系统变量
insert into users(name,pwd) values ('admin',AES_ENCRYPT('super_890',SHA1('my_encrypt_key'),random_bytes(8))；

预定义if函数应用

CASE_END 语句 greatest 和leat函数应用

Mysql窗口函数

窗口函数支持累计分部值；统计排名；数据分区的多种统计排名格式。

函数名	描述
ROW_NUMBER()	当前行在其分区内的行号，连续
CUME_DIST()	累积分布函数
RANK()	当前行在其分区内的排名，有间隔
DENSE_RANK()	当前行在其分区内的排名，没有间隔
PERCENT_RANK（）	百分比等级值

• ROW_NUMBER()

  此函数返回当前行在其分区内的编号整数值，行号的范围从1到分区行（最大行）数。order by 影响行的编号顺序。此函数为相同行分配不重复的行号，没有order by ，行编号是不确定的。

  select  f1,f2,....ROW_NUMBER()  over  w ....
  from  TABLE_name  ...
  window w AS([partition  by  f_name |  order by f_name ...])
  

• Rank()

  返回当前行在其分区内有间隔（跳跃）等级排名。相同统计值则等级也相同。此函数与order by 一起使用 ，将分区行按要求规则排序

  select  f1,f2,....rank()  over  w ....
  from  TABLE_name  ...
  window w AS([partition  by  f_name |  order by f_name ...])
  

• DENSE_RANK()

  返回当前行在其分区内的无间隔（不跳跃）等级排名。相同统计值则等级值也相同。此函数与order by 一起使用，将分区行按要求规则排序。

  select  f1,f2,....DENSE_RANK()  over  w ....
  from  TABLE_name  ...
  window w AS([partition  by  f_name |  order by f_name ...])
  

• PERCENT_RANK（）

  返回小于当前行中的值（不包括最高值）的分区值百分比。返回值得范围为0到1（首行值永远为0，末行值永远为1），表示行相对排名，根据此公式计算，其中排名是行排名，行是分区行数；此函数与order by 一起使用，将分区行按要求规则排序。

  select  f1,f2,....PERCENT_RANK（）  over  w ....
  from  TABLE_name  ...
  window w AS([partition  by  f_name |  order by f_name ...])
  

专用窗口函数有: rank、 dense_ rank、row_ number
基本语法: < 窗口函数> over (partition by < 用于分组的列名 > order by < 用于排序的列名>)
over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写，则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算;

如果不为空,则支持以下4种语法来设置窗口。

1. partition by子句:窗口按照哪些字段进行分组，窗口函数在不同的分组上分别执行
2. order by子句:按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号
3. frame子句: frame 是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用
4. window_ name:给窗口指定-一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读。

json数据类型函数应用

1. json_array 函数，将n个用逗号分隔有效数据转换为json 数组返回。如果未给定参数返回[ ],如果给定参数null 则返回[null]。

   select  json_array("king",34,'1990-12-2300',1);  -- ["king",34,'1990-12-2300',1]
   select json_array(); --[ ]
   select json_array(null); -- [null]
   

2. json_array_append(jsonStr,expr,append)

   将append数据按照expr表达式规则追加到jsonStr JSON数据中，返回JSON格式数据。表达式expr使用字符串描述，

   表达式中使用$元符号对jsonStr进行引用。

   select json_array_append('[{"name":"king","age":23},"day","year"]','$',250);
   -- 追加到末尾[{"age":23,"name":"king"},"day","year",250]
   
   select json_array_append('[{"name":"king","age":23},"day","year"]','$[0]',250);
   -- 追加到第一个元素末尾[{"age":23,"name":"king"},250,"day","year"]
   
   select json_array_append('{"篮球":6,"排球":3,"arr":[1,2,3]}','$.arr',245);
   -- 追加到对象数组{"arr":[1,2,3,245],"篮球":6,"排球":3}
   
   select json_array_append('[{"id":123},[[1,2],[3,4],[5,6]],{"name":"令狐冲"}]','$[1][1]',7);
   -- 追加到嵌套数组末尾[{"id":123},[[1,2],[3,4,7],[5,6]],{"name":"令狐冲"}]
   
   

3. json_array_insert

   json_array_insert 函数与json_array_append用法基本类似，不同之处在于插入到目标位置

   select json_array_insert('[{"name":"king","age":23},"day","year"]','$[0]',250);
   -- [250,{"name":"king","age":23},"day","year"]
   
   select json_array_insert('[{"name":"king","age":23},"day","year"]','$[90]',250);
   -- [{"name":"king","age":23},"day","year",250]
   

4. json_contains(jsonStr,data,expr)

   json_contains函数用来按照expr规则判断在jsonStr中是否包含data json数据，返回1（包含） 或0（不包含），此函数只对值类型起作用

   select  json_contains ('{"name":"admin","sex":"男","sex":23}','23','$.age'); -- 结果为1
   
   select  json_contains ('{"name":"admin","sex":"男","sex":23}','24','$.age'); -- 结果为0
   

5. json_extract

   json_extract(jsonStr,expr0,expr1,expr2,…) 从返回jsonStr中返回后面N个表达式描述的数据，如果只有一个结果则返回实际数据类型，如果包含多个值，则以json数组形式进行返回，如果找不到则返回null。

   select json_extract('[23,12,"for",45,"2019-12-12"]','$[0]');  -- 返回23
   
   select json_extract('[23,12,"for",45,"2019-12-12"]','$[10]');  -- 返回null
   
   select json_extract('[23,12,"for",45,"2019-12-12"]','$[0]','$[3]');  -- 返回[23,45]
   
   select json_extract('[10,20,[30,40]]','$[2][*]');  -- 返回[30,40]
   
   select json_array_append(json_extract('[23,12,"for",45,"2019-12-12"]','$[0]','$[3]','$',100);
                            -- [23,45,100]
   

6. json_insert

   json_insert(jsonStr,p1,v1,p2,v2,…)按照路径（p1,p2）和值（v1,v2）描述添加到jsonStr文档中，如果设置了存在的路径将被忽略，如果不存在则添加到jsonStr中

   select  json_insert('{"a":1,"b":[2,3]}','$.a',10,'$.c',"true");   
   -- {"a":1,"b":[2,3],"c":"true"};
   
   select  json_insert('{"a":1,"b":[2,3]}','$.a',10,'$.c',cast("true" as json));   
   -- {"a":1,"b":[2,3],"c":true};
   
   select json_insert('{"a":1,"b":[2,3]}','$.a',10,'$.a',"true",'$.m',679);
   -- {"a":1,"b":[2,3],"m":679};
   
   -- 利用json_insert 更新json数据
   update   table_name  set  字段名 = json_insert(字段名,'$[0].birth','1999-11-27')
   where id = 6;
   
   

7. json_keys

8. json_length(jsonStr,expr)

9. json_merge_patch 合并json数据原来的json数据会被覆盖

10. json_merge_preserve(jsonStr,newJson)

json_merge_preserve(jsonStr,newJson）对给定的jsonStr与newJson进行合并，并返回合并后的json结果，如果给定的newJson为null，则返回null。

11. json_object(k1,v1,[k2,v2,…])

12. json_remove(jsonStr,path[,path…])

    json_remove(jsonStr,path[,path…])从jsonStr中删除path所描述的json数据并返回结果，如果path为null则返回null。

13. json_replace(jsonStr,path,val[,path,val])

    json_replace(jsonStr,path,val[,path,val]) 按照path描述替换jsonStr数据中的数据值，任何给定不存在的path将被忽略。

14. json_set(jsonStr,path,val[,path,val])

    json_set(jsonStr,path,val[,path,val]) 按照path描述设置jsonStr数据中心对应的数据值，任何给定不存在的path将被添加到jsonStr中。

```mysql
update  table_name set details = json_set(details,'$[0].sex',1,'$[0].birth','1999-12-25')
where id = 6;
```

15. json_value(jsonStr,path[returning typee])

    json_value(jsonStr,path)此函数用来按照path描述从jsonStr中获取值，返回默认以varchar类型（512）形式返回，若path不存在则返回null，returning type 可选语句限定返回类型。

    （float ， double ， decimal ， signed ， unsigned， date，time，datetime，year，char,json）

```mysql
select json_value('{"name":"Admin","sex":1,"age":-23}','$.age'  returning  unsigend);  -- null

-- 操作目标表中的json类型数据字段
select json_value(datails,'$[0].birth'  returning   date)  from  books;
```

16. json_type(jsonStr,[path])

    json_type(jsonStr,[path])返回一个utf8mb4字符串，标识json值的类型（如对象，数组或标量类型等）

    

17. json_valid(jsonStr)

    json_valid(jsonStr)此函数用来检验给定的数据是否是有效的JSON类型，如果是则返回1，否则返回0，任何给定的参数为null则返回null。

18. value menber of (jsonArray)

存储过程与自定义函数

存储过程基本语法

存储过程简称过程，是实现数据库复杂业务数据处理的最主要编程方式，存储过程属于数据库中的对象，他是一组根据业务定义的一段整体代码逻辑语句块；存储过程是预编译的语句集，只在定义正确后执行一次编译便可被多次重复调用，而不必向普通的sql语句在执行前都需要语法校验，从而提高了执行的效率。

create 
   [definer = user]  -- 定义者，
   procedure  sp_name([proc_parameter[,...]])   -- 可定义多个参数，参数之间用逗号分隔
   [characteristic...]
   routine_body  -- 存储过程主体

delimiter //  -- 设置自定义结束符
create 
	definer = 'root@localhost'
	procedure sp_countQuantity(in para_age  integer)
comment ' 一个简单的存储过程，统计年龄大于给定参数的记录数量'
language  SQL
reads sql data
begin
   select  count(*)  from employee where age > para_age
end //


-- 调用存储过程
call procedure_name[(参数1，参数2.。。。)]
call sp0; -- 无参数可以省略()

MySql编程核心基础语法逻辑

Beging End 语句块

• begin end 是mysql编程中的语句块体，表示一段业务逻辑的开始和结束，同时也代表例程中的一块作用域，在begin和end之间可以定义本地使用的声明标识，begin和end可以理解为java语言中的{}语义。

  CREATE  PROCEDURE sp0()
  begin  
  	declare  dec_name  varchar(12); -- 定义了一个本地变量
  end
  

使用Declare 声明本地变量

/*声明本地变量*/
declare  var_name datatype[(len)] [default value];
declare  var_name1,var_name2 datatype[(len)]; -- 同时类型声明多个
/*使用set 赋值*/
set var_name = value ;
set var_name = other_var;
set var_name1 = value,var_name2 = value;
/*使用into赋值*/
select max(name)  into var_name from users where id = 12;-- 必须保证查询结果唯一

逻辑条件语句

1. 使用if条件语句实现逻辑判断

   mysql中通常使用if—elseif----else 语句完成逻辑条件判断，if—elseif----else通常可以出现在sql语句块存储过程以及函数定义中，此语句块与if()预定义函数是不同的。

   if boolean_expr
   	then statment_list...
   	[elseif  boolean_expr
                then statment_list]...
       [else statement_list]
   end if
   

逻辑循环语句

1. mysql编程中提供三种循环逻辑结构语句实现对目标sql语句的重复执行分别为

   • while … do 循环结构

   

   [begin_label:] while  boolean_expr
   do
   	statement_list ............;
   	other statement_list..........;
   	
   end while[end_label]
   

   • loop…leave 循环结构

   

   • repeat…until 循环结构

   

使用condition handler signal 进行异常处理

1. 使用declare 声明condition

   声明 condition
   declare  condition_name condition for condition_value
   condition_value : {
   
   mysql_error_code |sqlstate[value] sqlstate_value
   }
   
   mysql_error_code:任意mysql预定义的整型错误码
   sqlstate[value] sqlstate_value  ：SQLSTATE 值的5个字符的字符串文本
   注： condition 应有一个Handler 进行引用；condition必须定义在handler之前，除mysql预定义的错误码以外，mysql允许用户自定义1-999的错误码消息
   
   

2. 使用declare 声明Handler

   声明 handler 
   declare  handler_action   handler  for  condition_value
   [,condition_value]....statement
   handler_action:
   {
   	continue
   	|exit
   	|undo
   }
   condition_value:
   {
   	mysql_err_code
   	|sqlstate[value] sqlstate_value
   	|condition_name
   	|sqlwarning  -- 以01为前缀的sqlstate值的缩写
   	|not found -- 以02为前缀的sqlstate值类缩写，这与游标有关
   	|sqlexception  -- 不是以00，01，02为前缀sqlstate值的缩写
   }
   

编写应用存储过程

-- 带inout类型参数的存储过程
delimiter  $$ -- 设置结束标记符
CREATE 
	PROCEDURE  proc_ues_inout(INOUT args  VARCHAR(64)) 
COMMENT '根据传入的员工姓名获取其电信号码'
BEGIN
	SELECT  tel  INTO args  FROM employee WHERE NAME = args;
END $$


SET @args = '王秀秀';
CALL proc_ues_inout(@args);
SELECT IFNULL(@args,'没有电话号码') AS 电话号码;

编写应用自定义函数

create  funtion [if not exists]  function_name([args_list])
returns {string | integer |real | decimal}
deterministic
function_body




create  function say_hello()
returns varchar(64)
deterministic
begin
 return "hello,mysql";
 
end $$

select  say_hello();

Mysql视图

视图是虚拟表，可以用来更新数据，但是一般不建议。

create   [or  replace]
[algorithm = {undefined | merge |temptable}]   -- 算法
[definer = user@host]current_user   -- 定义者
[SQL security{definer | invoker}]
view  view_name [(column_list)]
as select_statement
[with [casaded | local ] check  option]

提示：视图隶属于当前数据库，如果创建的视图想属于其他数据库则如下操作：

create  view  db_name.view_name as select * from  db_name.tab_name;

创建视图的限制

1. select 语句不能引用系统变量以及用户定义的变量
2. 存储程序中，select 语句不能引用参数或局部变量。
3. select语句不能引用已准备好的语句参数
4. 不能引用任何不存在的表或视图，如果创建视图后，删除了定义所引用的表或视图，则使用该视图会导致错误
5. 不能引用临时表，也不能创建临时视图
6. 不能将触发器与视图关联
7. select语句中列名的别名根据64个字符的最大列长度（而不是256个字符的最大别名长度）进行检查。

with [cascaded |local ] check option

Mysql 索引（index）

Mysql索引类型

mysql中的索引是存在存储引擎级实现的，所以不同的存储引擎对索引支持也不是完全相同的，Mysql 提供了4类引擎和不同的数据库引擎

show命令显示当前Mysql 数据库服务器支持的所有的引擎类型

show engines

创建索引语法

CREATE   [UNIQUE | FULLTEXT |  SPATIAL]  INDEX
index_name[index_type]  on tbl_name(key_part,....)
[index_option]  -- 索引选项
[algorithm_option | lock_option] ...  -- 算法

创建表时定义索引

-- 创建表时直接创建索引
create  table  users(
	id  bigint  not  null,
    name varchar(64)  null,
    address  varchar(128)  null,
    birth date  null,
    primary  key (id),-- 设置主键列索引(聚集索引，主键索引)
    index  index_address(address) invisible  -- 创建address列上创建单值索引
  
)

利用alter table 添加索引

alter  table table_name  add index
index_name(column_name,...)
[
    visible  | invisible
    comment '注释内容'
    using btree | hash
];

-- 修改表时添加索引
alter table users  add index  index_birth(birth) using  btree visible;
alter table  users add key index_name(name)  using  btree  visible comment 'name字段添加索引';

visible 和invisible 的作用

修改已存在索引的可见性：
alter  table  table_name  alter index  index_name  visible  | invisible;

查看某索引的可见性:
select  is_visible  from information_schema.statisticss  s
where s.index_name = 'actual_index_name_label';

查看和删除索引

-- 查看目标数据库表上的索引信息
show  index  from  db_name.table_name[where...]
show index from table_name from db_name [where...]


-- 从目标数据库表上删除索引
drop  index index_name on db_name.table_name

Explain SQL 分析器

Explain 是mysql中的sql语句分析器，Explain 适用于select，delete ， insert ，replace 和update 等dml 语句，Explain 提供mysql执行语句信息。
Explain 为select 语句中使用的每个表返回一行信息，按照mysql在处理语句时读取的顺序列出目标表的查询执行信息语法。

Explain  select  count(*)  from  department  d    inner   join  employee   e  on   d.id = e.did ; -- 将返回两条sql执行信息

Explain 输出信息列

​ Extra 额外的解析sql信息

Explain select_type 列

Explain type(access_type) 列

列前缀索引

create  index  index_motto on person(motto(10))  using  btree  invisible
-- 在person表上以motto列前10个字符创建列前缀索引

复合（多列）索引

创建一个索引时为索引指定多个列的形式就是复合索引也叫多列索引。Mysql中最多使用16个列创建复合索引。

Mysql对于复合索引采用索引最左侧前缀组合优化方式实现复合索引的搜索，如果搜索筛选条件不满足最左前缀则不使用复合索引进行查询。

复合索引最左侧前缀组合

• 如Tab表中（c1,c2,c3）建立复合索引，则’c1’ 或 'c1,c2’及’c1,c2,c3’组合条件上有索引搜索功能。

  select *  from Tab  where  c1 = v1;
  select * from Tab where c1 = v1  and c2 = v2;
  
  
  select  *  from  Tab  where  c2 = v2;  -- 不符合
  select * from  Tab where c2 = v2  and  c3 = v3;  --  不使用索引搜索
  

建立复合索引

create   index  mul_birth_h_w  on  test_index( height,weight,birth );

ExPLAIN  select *  from test_index  as  t where  t.height   = 167  and t.weight = 65; -- 可利用索引
ExPLAIN  select *  from test_index  as  t where t.weight = 65   and t.height   = 167; -- 不会利用索引

-- 在height建立单列索引
EXPLAIN  select * from test_index  as t  ignore index(mul_birth_h_w) where  t.height   = 167 ;-- 忽略索引
EXPLAIN  select * from test_index  as t  use index(mul_birth_h_w) where  t.height   = 167 ;

多值索引

多值索引示例

-- 基于json数据类型列建立多值索引
create  index   mul_value_index  on  peoples((cast(peoinfo->'$.health'  as  unsigned  array));

全文索引

创建全文索引语法

create   fulltext   index index_name  on  table_name(key_part1[,key_part2..])  
with  parser  ngram
other_characteristic;


-- 不推荐执行效率低
create table table_name(
	id  bigint ,
    descs  varchar(1000),
    contents  text,
fulltext key ft_contents(contents) with parser  ngram
 )
 
 Alter  table table_name  add fulltext  index ft_descs(descs)
 invisible with  parser  ngram;

全文索引注意的问题

全文索引的搜索模式

1. 自然语言搜索

2. 布尔类型搜索

3. 全文搜索忽略

搜索关键字语义符号

-- innodb  长度忽略和停止关键字
select * from fulltexttable  where  match(t1)
against('be')

HASH索引

create  index  hash_add_birth  on users(address,birth)
using  hash;

数据库事物处理

事物(TRANSACTION) 是数据库操作过程中独立的，不可分割的最小执行单元。事物处理的目的主要在于确保最终业务数据的正确性和有效性，最大限度避免脏数据的出现。

数据库事物具有以下四个特征（AICD）:

1. 原子性（表示要么全部执行，要么全部取消，返回到原来的值）
2. 隔离性（保证事务之间相互的独立性，一个事务的执行不受其他事务的干扰，隔离性主要解决并发对数据的访问问题）
3. 一致性（事务执行的结果必须是使数据库从一个一致性状态到另一个一致性状态，一致性也是业务数据最终正确的体现，更是事务的最终目的）
4. 持久性（事务执行完成后（提交commit 或回滚rollback），对底层库的影响是永久的，最终的）

start transaction
[with  consistent  snapshot, -- 具有一致性快照（Lnnodb）
 | read write, -- 读写（默认）  支持数据的读写操作
| read  only ,];  -- 只读  提供对数据的读取，拒绝更改数据
begin  [work];
transaction_statements....
commit [work] [and[no]chain] [[no]release]; -- 提交事务
rollback [work][and[no]chain] [[no]release]; -- 撤销事务
set autocommint = {0|1|on|off}; -- 设置提交模式

撤销事务到savepoint

保存点是事务执行过程中某个节点，此节点是在条件满足情况下Rollback语句回滚的标识，使用rollback‘撤销事务到某个savepoint是部分撤销事务的行为，保存点之前的操作是有效的，在事务结束后将提交有效数据永久存储到底层库，根据需要可设置多个保存点

....
savepoint  save_label0;
savepoint  save_label1;
.....
rollback  to  savepont save_label0;

事务隔离级别

Mysql InnoDB事务处理引擎提供了符合SQL1992标准的四个事务隔离级别：

• read uncommitted （未提交读）
• read committed（读已提交）
• repeatable read （可重复读）（默认）
• serializable （序列化/串行化）

级别严格性由低到高

read uncommitted ——> read committed —> repeatable read ----> serializable

级别越高性能相对越低

• 在实际成产环境中处理数据业务时，如何选择事务的隔离级别应根据实际处理数据的需求而做出针对性的处理。
• 通常对于要求数据一致性比较宽松的业务，可以选择效率比较高，事务隔离级别比较低的方式，如read committed甚至是read uncommitted
• 对于数据一致性要求比较严格的数据业务处理，通常可以选择效率相对较低，事务隔离级别偏高的方式repeatable read
• 诸如对于一些较少但是数据一致性要求极其苛刻的数据业务处理，可以选择事务隔离级别最严格的serializable，但是应能够接收器处理数据效率比较低的性能。

事务并发产生的问题

• 脏读：一个事务在执行中读取到另一个事务的未提交数据
• 不可重复读：一个事务在执行中多次读取同一数据返回不同结果
• 幻读：一个事务在执行中修改某些数据后，再次读取这些数据发现某些数据为修改。

并发问题与事务级别的关系

事务隔离级别	脏读	不可重复读	幻读
read uncommitted 读未提交	是	是	是
read committed 不可重复读	否	是	是
repeatable read （可重复读）	否	否	是
serializable （序列化/串行化）	否	否	否

Mysql锁定机制

Mysql数据库中的事务在执行中会根据事务模式或对整表加锁，或对某条欲操作的数据进行加锁，以保证能够更安全地协调其他可能并发的事务处理数据。

这些不同的锁可以共享或独占资源，又或者某个锁定排斥其他事务对表或者表中的行进行加锁。因此事务和锁有着不可分割的关系。

另外，即使在不使用事务是也可以通过合理使用这些锁而达到安全处理数据的目的。

InnoDB引擎锁定类型

shared Locks （共享锁）

允许当前事务读取一行数据，此锁可以由多个事务共同持有作用在一个记录行，也就是多个事务同时在一个记录行上加共享锁
如当事务A获取了R行的共享锁，事务B也可以立即获得R行的共享锁，因此叫共享锁（锁兼容）。但是事务B无法立即获取R行的排他锁，若想获取则必须等待A释放R行的共享锁。（锁等待时间取决于系统变量lock_wait_timeout的值（秒））
获取共享锁语句：
select * from table_name  where id = n lock in share  mode;

-- 查看系统默认锁等待超时时间
show  variables   like 'lock_wait_timeout';

-- 锁定表
lock  tables  table_name  read;

unlock tables; -- 释放表

Exclusive Locks （独占/排他锁）

允许持有锁的事务更新或删除行，不允许其他事务再向此数据表添加共享锁或排它锁。若想获取锁定则必须等待排他锁被释放。
innoDB 事务引擎对delete，update，insert自动加排他锁，select语句如下方式获取排他锁。

select语句如下方式获取排他锁
select * from table_name where id = n for update;

事务A事务B	共享锁	排他锁
共享锁	兼容	不兼容
排他锁	不兼容	不兼容

Intention Locks （意向锁）

意向锁是表级锁，用于指示事务稍后对表中的行需要那种类型的锁（共享或排他）。有两种类型的意向锁；意向共享锁（IS）表示事务打算在表中的各个行上设置共享锁。意向独占锁（IX）表示事务对表中的各行设置独占锁。
mysql按照以下规则实现意向锁机制。在事务可以获取表中某一行共享锁之前，必须首先获取表中的IS锁或更强的锁，在事务可以获取表中某一行独占锁之前，他必须首先获取表上的IX锁，在InnoDB中，意向锁是自动维护的，用户不能对其进行操作，意向锁实际是对锁性进行了优化。

Insert Intention Locks（插入意向锁）

是在插入行之前由insert自动设置的一种间隙锁。含义是表示插入的意图，如果插入到同一索引间隙中的多个事务没有插入到间隙中的同一位置，它们是无需等待的，假设存在值为4和7的索引记录。分别尝试插入值5和6的单独事务，在获得插入行的独占锁之前，每个事务都使用插入意向锁锁定4和7之间的间隙，这是运行的，如果2个事务都预将插入5则就需要等待，其一进行等待。
select  * from  tab where id > 4 and id < 7 for uodate;
insert......

AUTO-INC Locks （自增锁）

AUTO-INC锁是一种特殊的表级锁，由插入到具有auto_increment 自增量字段的表中的事务自动应用
在最简单的情况下，如果一个事务正向表中插入值，则任何其他任务都必须等待当前事务向该表中插入值完成，以便能够达到第一个事务插入的行具有连续的主键值。

Record Locks （记录锁）

作用在索引上的锁排他锁。例如select c1  from t where c1 = 10 for update;防止任何其他事务插入，更新或删除t.c1值为10的行。
记录锁始终锁定索引索引记录，即使表中没有索引。对于上面的语句InnoDB也会创建一个隐藏的聚集索引，并使用该索引进行记录锁定，执行完成后隐式索引被删除。

锁定表lock table

lock  tables 
 table_name [as alias]  lock_type[,tbl_name[as alias] lock_type]...
 lock_type:{
 read [local]
 | [LOW_priority] write
  }
  unlock  tables

Mysql编程中游标的使用（Cursor）

mysql数据库中的游标是通过在程序中使用declare关键字声明的一种特殊的编程数据类型。游标类似于java语言中通过集合建立的迭代器。由标的建立必须基于对某个表或视图的查询动作之上，从而使游标指向查询结果。
通过游标可以获取当前游标所指向的当前结果的行记录，游标获取结果集记录行是由上至下进行推移的，不能跳跃也不能向后推移。
要想获取游标数据必须先打开游标，在使用完游标后应显示关闭释放游标资源，游标可以应用在任何使用Declare 的语句块中

游标的定义和使用

declare  var_cur   cursor  for  select ....(复合要求的查询语句);  -- 定义游标
Open cursor_var; -- 打开游标准备使用
Fetch[next  from] cursor_var  into  var_label; -- 使用游标赋值
Close cursor_var;  -- 关闭游标

触发器的作用

mysql数据库中触发器（Trigger）是一种在满足触发条件时自动执行的代码集，触发器与存储过程及函数最大的不同在于存储过程，函数必须手动显示调用，而触发器是在满足条件时自动调用的。

触发器定义

create  [definer = user]  -- 定义者
trigger [if not exists] trigger_name
trigger_time{before|after} -- 触发器调用的时间
trigger_event {insert | update | delete} -- 触发器触发条件
ON tbl_name for each row -- 标识行级触发器
[trigger_order {preceds|follows}] other_tigger_name  -- 同类型同条件触发器执行顺序
trigger_body -- 触发器主体

触发器不能与临时表或视图关联，触发主体内不能包含返回输出语句及使用事务处理

查看，删除触发器

触发器信息存储在information_schema数据库中的triggers表中，可通过查询此表或是用show命令获取触发器信息

-- 通过triggers表获取信息
select * from information_schema.'triggers' where .....
-- 显示触发器的创建信息
show create trigger_name
-- 删除触发器
drop trigger if exists trigger_name

触发器类型与触发工作条件

Mysql中的触发器按照事件动作可以分为以下三种类型:

1. insert 触发器：在当前表执行添加记录行时触发器开始工作
2. update触发器：在当前表上执行更新操作时触发器开始工作
3. delete触发器：在当前表上执行删除操作时触发器开始工作
4. 按照时间可以分为以下两种类型：
   1. before 触发器：当事件动作发生前触发器被触发
   2. after 触发器： 当事件动作发生后触发器被触发

在触发器主体代码中使用关键字：

new.column_name 访问记录行的新列值

old.column_name访问记录行的旧列值

insert触发器的使用

Create 
[definer = user]
trigger trigger_name
{before | after}
insert 
on tbl_name  for each row
[precedes | follows] other_tigger_name
trigger_body -- 触发器主体


如果想终止激活触发器工作的动作继续执行可以使用signal引发sqlstate ‘HY000’ set message_text = value 实现

CREATE TRIGGER trig_insert_nationalinfo BEFORE INSERT ON nationalinfo FOR EACH ROW 
BEGIN
	DECLARE errmess VARCHAR(64) DEFAULT '存在重复的国家名称,禁止插入';-- 记录错误消息
	DECLARE vcount int DEFAULT 0;-- 记录是否有重复的国家名称
	
	SELECT COUNT(*) INTO vcount FROM nationalinfo WHERE country = NEW.country;
	
	if vcount then 
		SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = errmess;
	END if;
	-- SELECT 'ok';-- 不允许有返回语句
END $$

Update触发器

Create 
[definer = user]
trigger trigger_name
{before | after}
update
on tbl_name  for each row
[precedes | follows] other_tigger_name
trigger_body -- 触发器主体

CREATE TRIGGER trig_before_update_nationalinfo BEFORE UPDATE ON 
nationalinfo FOR EACH ROW 
BEGIN 
	DECLARE err_text VARCHAR(64) DEFAULT '不允许修改country字段值为"中国"或者landarea字段小于等于0';-- 错误提示文本
	if OLD.country = '中国' OR NEW.landArea <= 0 then  
		SIGNAL SQLSTATE '02000' 
		SET MESSAGE_TEXT = err_text;
	END if;
END $$

delete触发器

Create 
[definer = user]
trigger trigger_name
{before | after}
delete
on tbl_name  for each row
[precedes | follows] other_tigger_name
trigger_body -- 触发器主体



CREATE TRIGGER trig_before_delete_nationalinfo BEFORE DELETE ON nationalinfo 
FOR EACH ROW 
BEGIN 
	INSERT INTO nationalinfobackup VALUES(OLD.id,OLD.country,OLD.landArea,OLD.population,
			OLD.continent);
END $$