MySQL8.0随笔（1）

一、SQL语言基础

SQL语法特点：

二、数据库管理系统DBMS（数据库软件）

1、关系型数据库（RDBMS）

2、非关系型数据库（NoSQL） 

 

 三、MySQL的简介

MySQL的特点：

四、数据库基本操作

注意：对表（结构）和数据库的删除用drop，对表中数据删除用delete。

注意：若修改列的操作（不包括修改列数据），即在修改表结构，需要alter table 表名 + 列操作

          若修改列数据（即表中数据），可直接操作（insert 、delete、update）

注意：修改表结构包括：修改列的操作（不包括修改列数据），添加（删除）约束

（一）数据库操作之DDL（定义）

1、对数据库进行操作：

 2、对表结构的常用操作：

（1）创建表：

 （2）数据类型（补充）：

    数值类型：int；

    字符串类型：double，float；

    日期类型：Date（年月日）、time（时分秒）、DATETIME（年月日时分秒）、TIMESTAMP（时间戳：年月日时分秒，有时区）。

（3）对表结构的其他操作

 （4）修改表结构（对列操作，即修改了表结构）：

 注意：若修改列的操作，即在修改表结构，需要alter table 表名 + 列操作

（二）DML：对表的数据进行更新（增删改）

插入insert，删除delete，更新update

 1、插入数据insert：

 2、修改数据update：

 3、删除数据delete：

 （三）MYSQL约束constraint（表中数据）

约束分类：

 1、主键约束primary key

（1）主键约束的概念

 （2）创建表中主键的操作

a.单列主键：

方法1：在定义字段的同时指定主键

方法2：在定义字段后再指定主键

 b.联合主键：

 （3）修改表结构中的主键：

创建单列主键、联合主键：

注意：在创建表之后定义主键约束，属于修改表结构，用alter table 表名 + 操作

（4）删除主键（注意：属于修改表结构了）：

 2、自增长约束auto_increment：

（1）自增长的初始值默认从1开始：

 （2）指定自增长的初始值：

 （3）delete和truncate清空数据时的自增长

 3、非空约束的创建和删除：

 

注意：非空约束不可以NULL；但可以 'NULL' 和 ' ' （表字符串）

 4、唯一约束unique：

 注意：唯一约束，可以添加多个NULL

 5、默认值约束default：

 6、零填充约束，eg：int共10位，不够位补0

 7、约束格式总结小技巧：

  添加约束格式——为:alter table 表名 + 以下格式

      添加非空约束和默认值约束——格式为：modify 列 类型  + 关键字

      添加零填充约束——格式为：modify 列 类型 

      添加唯一约束——格式为：add  + 关键字（列）

      添加自增长约束——格式为：直接关键字 = 值

（四）数据库操作之DQL（基本查询）

1、简单查询之取别名和去掉重复值

2、运算符

①简单运算符     + - * / %

②比较运算符：

 ③逻辑运算符：

异或：相同为0，不同为1；

④位运算符(二进制)(了解即可)

3、补充知识点：

like    %为匹配任意字符     _表示匹配单个字符   eg：若like '_字'即表示只有两个字   eg：like '%字%'

null：null与null都不相同，所以null为不固定值；数值中有null与其他值进行比较时，结果为null

4、排序查询order by

注意：排序order by放在查询语句最后面，limit子句除外。

5、聚合函数(针对一列的）

固定公式：select 聚合函数 from 表 where 条件；

注意：where后面不能加聚合函数，但having后可以加聚合函数。

注意：聚合函数对null即空值，视而不见，相当于没有。

6、分组查询：

 注意：

    ①统计各个分类商品的个数，即是分组之后的每一组的商品的个数；

    ②若分成四类，即表临时分成了四个表，之后在每个表中进行查询，且只能查询分组字段和聚合函数。   

注意：having为在分组之后的条件进行筛选。

注意：分组也可以跟多个字段，但多个字段都相同才能分到一组eg：group by city，province

7、分页查询limit，放在最后

8、insert into select

9、SQL语句的书写顺序和执行顺序

10、正则表达式：

例子如下：

 

 

 

 （五）外键约束

注意：外键约束对于增删改有影响，对查无影响

1、特点：

2、创建外键约束：

①方法一：创建表时创建外键

 注意：

 ②方法二：修改表结构时创建外键

3、有外键的插入数据：

 注意主表中主键没有的值，外键不可以插入此值。

4、有外键的删除数据：

5、删除外键约束：

 注意：删除外键约束，只是删除了外键约束，但是该字段还在

 （六）多表查询（重点）

多表关系：一对一，一对多，多对多

1、交叉连接查询：会出现冗余数据

格式：select * from 表1(，表2，表3)；

2、内连接查询：取交集

格式：

 操作：

 注意：on 后还有其他条件 则用and

3、外连接查询：

 操作：

①左外连接 操作：

 ②多表  左外连接  操作：

 ③右外连接 操作：

 ④满外连接 操作：

 

4、子查询（重要）：

①单行单列，即一个值，用   =(.....)   >(.....)   <(.....) 

②多行单列，即多个值，用   in(......)

 ③一行多列，即 正常查；多行多列，即用   关联查询

5、子查询关键字：

①ALL

②ANY(SOME)

 ③IN

 ④exists

 注意：

6、自关联查询：

 （七）MYSQL函数

MySQL函数：聚合函数，数学函数，字符串函数，日期函数，控制流函数、窗口函数(只在8.0以上适用)

1、聚合函数之group_contact(.....)

2、数学函数

注意：数学函数max() ，它的()中应为表达式或字段,不能为3,4,5这种

3、字符串函数

4、日期函数

5、控制流函数

①if逻辑判断语句：

案例如下： 

②case when语句：

 案例1：

案例2：

6、窗口函数（开窗函数）(8.0新增)

 (1)序号函数：

row_number():少

rank():

案例（注意）：

注意：where写在子查询的后面；因为如果不弄子查询，则执行顺序为from where select，在where rn<=3时，rn还没有，因为rn在select里，所以不行。

(2)开窗聚合函数

sum(),avg(),max()等开窗聚合函数

①每一组的薪资按照分组依次进行累加

②每组总共的薪资（没有order by）

③从开头一直加到当前行

④从我向上3行加到当前行

⑤从我向上前3行到后1行，包括当前行的累加和 

 ⑥从当前行一直加到最后

(3)分布函数

①cume_dist()

②precent_rank()

(4)前后函数

 

(5)头尾函数：截止到目前位置的第一个或最后一个

(6)其他函数

①nth_value()：截止到目前位置的、、、

②ntile(n)分组

若除不尽，则先紧着前面分组，eg：前边2，2，后边,1,1

五、MySQL的视图view

1、视图的介绍和作用：

2、创建视图：

注意：使用视图的时候，首先要到原表中去查询数据，每次都如此，所以原图修改，视图也修改。

3、修改视图

4、更新视图

注意：更新视图还是在更新原表中的数据，但是不是所有的数据都可更新

5、重命名视图和删除视图

六、MySQL的存储procudure

1、存储过程的定义和调用

2、声明变量，给变量赋值set，输出变量的值    (局部变量：begin和end之间起作用)

3、给局部变量赋值：select xx into xxx  from 表 where 条件；     

4、用户变量   @变量名  不用声明，使用即声明，在当前的会话(连接)中起作用

5、全局变量  @@.global.变量名   在整个mysql中起作用

6、会话变量  @@.session.变量名  在当前的连接(会话)中起作用

与全局变量一样

7、传入参数 in ：从调用者传值给存储函数   传入的值不可以在内部修改

①一个传入参数

②两个传入参数

8、传出参数 out ：从存储内部传值给调用者

①一个传出参数

②两个传出参数

9、传入传出参数 inout ：传入的值可以在内部修改

①一个 传入传出inout

②两个 传入传出inout

10、流程控制  if  

①案例1：成绩

调用方法①：

调用方法②：

②案例2：

11、流程控制 case

① 格式：

②方法一：支付方式案例

③方法二：成绩案例

12、流程控制 while循环

13、leave跳出整个循环，必须加标签，相当于break

14、iterate跳出本次循环，进入下次循环，相当于continue

15、流程控制  repeat循环

16、流程控制  loop循环

17、游标：返回结果集(一行)

 

18、游标异常处理  handle句柄             顺序不能改变

七、MySQL的存储函数

1、格式

2、创建存储函数，没有参数的

3、创建存储函数，有参数的

八、MySQL的触发器trigger：(了解，用的不多)一个表的一行数据变动，另一个表来跟着变动

1、触发器的概念

2、触发器的特性：

3、创建触发器的格式：

4、操作：

5、触发器之 new 和 old 关键字

6、查看触发器和删除触发器

 查看触发器：show triggers;

 删除触发器：drop trigger  [if exiest]  触发器名；

7、触发器注意事项

九、MySQL的索引★★

1、单列索引之普通索引

①创建普通索引

②查看索引

③删除索引

2、单列索引之唯一索引     （列中唯一，可以为空）

创建唯一索引：

3、单列索引之主键索引    (自动创建，唯一且不为空)

主键索引需要查看语句，才能看的到。

4、组合索引(多个列构成的索引)

①创建组合索引的普通索引

②创建组合索引的唯一索引

③优先最左匹配

 注意：索引不是创建了就能用的，需要符合条件才能用到索引，如上图。

5、全文索引fulltext(了解)：类似搜索引擎，搜索关键字

注意：truncate table 表名；//清空表中数据