MySQL数据库

1.数据库（DataBase）

        数据库是长期存储；有类型，数据与数据之间的关系；大多数数据库都是文件系统的，即存储在数据库中的数据实际是存储在磁盘文件中的；多个应用程序可以通过数据库实现数据的共享。关系型数据库：采用关系模型以行和列的行书存储数据并记录数据与数据之间的关系，可以建立表格与表格之间的关联来维护数据与数据之间的关系。非关系型数据库：采用键值对模型才存储数据，只完成数据的记录，不会记录数据与数据之间的关系。在非关系型数据库中基于特定的存储格式来解决一些大数据应用的难题。（NoSQL,Not only SQL）非关系型数据库产品：面向检索的列式存储（BigTable）；面向高并发的缓存存储key-value（Redis）,面向海量数据访问的文档存储（MongoDB）。数据库管理系统（Database Management System，DBMS），是位于用户与操作系统之间的一层数据管理软件。数据库系统管理员（DBA）负责数据创建使用和维护的专门人员。数据库系统（DBS）数据实际是存储在数据文件中，进行组织存储和查询的。

 2.MySQL Command line Client和Navicat的使用

        连接数据库：输入用户密码。连接MySQL输入密码即可，如果密码错误或MySQL没有启动，则命令行闪退。输入‘exit’指令即可退出当前doc窗口，关闭MySQL。

 3.SQL 分类

        DDL（Data Definition Language数据定义语言）：完成队数据库对象（数据库、数据表、视图、索引等）的创建、删除、修改；DDML（Data Manipulation Language数据操纵语言）：完成数据表中数据的添加删除修改操作；DQL（Data Query Language数据查询语言）将数据表中的数据查询出来；DCL（Data Control Language数据控制语言）完成对事务管理等控制性操作。

4.MySQL的基本语法

        关键词不区分大小写；每条SQL指令结束后都以分号;结束；SQL之间可以不限制换行，可以以空格进行分割。

        创建数据库：create database  文件名    例如：create database db_test;  显示数据库列表：show database   create database db_test if not exists如果不存在则创建，若存在则不创建。create database db_test character utf8指定编码格式。修改数据库名称： alter database db_test character set utf8 /gbk。删除数据库：会删除当前数据库中所有的数据表以及数据表中的数据：drop database is exists  db_test  然后进行show database。使用切换数据库：use db_test。

        创建数据表：一个表格是由多列组成，表格中的每一类都为表格的一个字段。查询数据表：show tables；查询表结构：desc <tableName> 删除数据表drop table students；修改数据表：desc students；alter table students rename to stus；添加字段alter table <tableName> add <columnName> varchar(200)；修改字段的列表和类型alter table <tableName> change <oldColumnName> <newColumnName> <type>，只修改列（字段）类型：alter table <tableName> modify <columnName> <newType>。删除列字段：alter table stus drop <columnName>

 5.数据类型

        数值类型；字符类型：存储字符序列的类型；日期类型：使用字符串来存储时间

        字段约束：创建数据表时，对数据表的列队数据限制性要求。保证数据的有效性；保证数据的完整性；保证数据的正确性。字段常见约束：非空约束（not null限制此列的值必须有）；唯一约束（unique 再数据表中的数据不能重复）；主键约束（primary key非空+约束，唯一标识数据表中的数据）；外键约束（foreign key 建立不同表之间的关联）。

    

         删除主键约束：alter table books drop primary key；创建表之后添加主键约束：alter table books modify book_isbn char(4) primary key；定义主键自动增长：auto_increment，只有int类型的字段才可，当数据删除之后，会在原删除的基础上继续添加，自动增长的数据也不会重复生成，保证唯一性，不保证连续性。联合主键：将数据表中的多列组合在一起设置为表的主键，当一个数据表中没有一个明确的字段可以作为主键时，可以额外添加一个ID字段作为主键。外键约束？？？

create table grades(
    stu_num char(8),
    course_id int,
    score int,
    primary key(stu_num,course_id)    /*联合主键写法*/
);

 6.数据操作语言DML

        插入数据：insert into <tableName>(columnName) values(value1,value2...);向数据表中指定的列添加操作，不允许为空的列必须添加数据，列的数据可以跟字段初始设置不同，但value中的值要与columnName的值相对应。 删除数据：delete form <tableName> where conditions，若删除语句中没有where子句，则表示删除当前数据表中所有数据。修改数据：update <tableName> set columnName=value [where conditions] 。查询数据：单表查询：select columnName1[, columnName2,columnName3...] from <tableName>特定列查询；select * from <tableName>使用*代替所有列的查询，但是不建议写。LIKE子句，使用like进行模糊查询，select * from <tableName> where columnName like 'reg'，在like关键字后的reg表达式中，%表示任意多个字符（%o%包含字母o的），_表示任意一个字符（_o%，第二个字母为o的）。where子句：and 与关键字、or或关键字、not取反关键字。对查询结果的处理：声明查询结果的指定列：对数据表中查询的记录的列进行一定的运算之后显示出来：select stu_name,2021-stu_age from stus出城年份=当前年份-年龄。也可以给字段起别名使用as关键字select stu_name,2021-stu_age as stu_birth_year from stus，从查询结果中将重复的记录消除distinct：select distinct stu_age from stus。排序（order by）将查询的结果按照指定列的值进行升序或降序排列，asc按照升序，desc按照降序排列。聚合函数：count函数统计满足条件的指定字段的内容，max()函数，获取最大的内容，min()函数获取最小值，sum()查询满足条件的列的值的总和，avg()求平均值，计算指定列的平均值。日期函数：可以通过字符串类型赋值，其格式必须为(yyyy-MM-dd hh:mm:ss)若想获取当前系统时间添加到日期类型的列，可以用now()或者sysdate()。字符串函数：concat：拼接多列；upper将字段的值转换成大写，lower将指定列的值转换成小写。分组查询：将数据表中的记录按照指定类进行。select *分组字段/聚合函数 from 表名 [where条件] group by 分组列名 [having条件]。分页查询：select ... from...where... limit param1 param2。两个参数分别代表：第一条数据的索引，查询记录的条数。select ... from...where... limit (pageNum-1)*pageSize,pageSize，pageNum表示查询页码，pageSize表示每页显示的条数。

 7.数据表的关联关系

        关系型数据库中，不仅可以存储数据，还可以存储数据与数据之间的关系。数据表的关联关系，在某一个数据表中添加外键建立起与另一个表的联系。

         数据与数据之间的关系：一对一关联；一对多关联；多对一关联；多对多关联。

   在添加外键时设置级联修改和级联删除，测试级联：

8.笛卡尔积

        笛卡尔积指的是：A集合&B集合，使用A中每个记录一次关联B中每个记录，笛卡尔积的总数=A总数*B总数，若执行select ... from tableName1 inner join tableName2会获取；两个数据表的笛卡尔积。因此可以添加where条件语句进行链接过滤条件，步骤为：先生成笛卡尔积，再过滤数据效率比较低，例如select * from students inner join classes where students.cid = classes.class_id,换一种方式则为select * from students inner join classes on students.cid = classes.class_id使用on关键字，先判断连接条件是否成立再进行连接。左连接：select * from leftTable LEFT join rightTable on 匹配条件[where 条件]，显示左表所有数据，如果在右表中存在与左表记录满足匹配条件的数据则进行匹配，若右表中不存在匹配数据则显示为null。右连接则相反，显示右表中的数据。。。子查询返回的结果是一个值（单行多列），条件使用IN：select * from students where cid IN (select class_id from classes where class_name LIKE 'Java% ' );

9.存储过程

        SQL存储过程是指能够完成指定功能的SQL指令进行封装，编译之后存储在数据库服务器上，并为之命名，客户端可通过名字直接调用该指令集。存储的优点：SQL指令无需客户端编写，通过网络传送，可以节省网络开销，同时比吗SQL指令在网络传输过程中被恶意篡改保证安全性；通过变异并创建保存在数据库中，执行过程无需重复进行编译操作，提升性能；其支持流程控制语句分支，循环，可以实现更为复杂的业务。存储过程的缺点：存储过程是根据不同的数据库进行编译创建并存储在数据库中，当需要切换到其他数据库产品时，需要重新编写对于新数据库的存储过程；存储过程受限于收据库产品，需要高性能优化会成为一个问题；在互联网项目中，需要高并发访问，会增加数据库的连接执行时间 。

存储过程是有输入参数和输出参数的，存储过程创建语法如下所示。

create procedure <proc_name>([IN/OUT args])
begin
    ---SQL 
end;
create procedure proc_test([IN a int,IN b int,OUT c int])
begin
    SET c = a + b
end;
--调用存储过程
set @m = 0
call pro_test(3,2,@m)
--显示变量值
select @m from dual

        局部变量是存储在存储过程中且在开始位置declare <attr_name> <type> [default value];局部变量定义语法，局部变量只能在局部使用，外部无法访问。用户变量相当于全局变量，会存储在dual中，通过select @attrName from dual进行查询，可以使用set进行定义，但必须以@开头。将查询结果赋值给变量，使用select...into...给变量赋值。用户变量使用的注意事项：用户变量在多个存储过程中可以共享，用户变量过多会导致程序难以维护。存储过程的三个参数：输入参数IN:传递数据给存储过程中的参数，输出参数OUT：将存储过程中产生的数据返回给过程的调用者，输入输出参数INOUT。

         存储过程中的流程控制：分支语句if-then-else，case，while，repeat，注意repeat需要设置上线值否则会一直执行，陷入死循环。loop循环语句

 存储过程管理：查询：存储过程是隶属于某个数据库的，即当存储过程创建在某个数据库之后，只能在当前数据库中调用次存储过程，即查询某个数据库中的存储过程。修改：修改存储过程的特征，删除：使用drop删除数据库中的对象、数据库、数据表、列、存储过程、视图、触发器、索引等，使用delete删除数据表中的数据

--根据数据库名查询当前数据库的存储过程
show procedure status where db='db_test1';
--查询数据库的存储过程的细节
show create procedure db_test2.proc_test1;
--修改存储过程的特征
alter procedure <proc_name> 特征1 [特征2 特征3...]
--删除存储过程
drop procedure proc_test1

 

 存储过程中相关借书案例：

参数1：a输入参数，学号

 存储过程的游标：

        为什么使用游标？创建一个存储过程时需要查询多条数据可以使用游标对依次取出查询结果集中每一条数据，对其进行遍历。游标的使用步骤：声明游标；declare cursor_name CURSOR FOR select_statement，示例如下。FETCH表示提取，游标要结合循环语句进行使用。

 测试游标：每条结果之间以逗号的形式隔开，同一条数据以~的形式连接。

 10.触发器 trigger

        特殊的存储过程，能够完成也定的、存储在数据库服务器上的SQL片段，但无需调用，当对数据表中的数据执行DML操作时自动触发这个SQL片段，无需手动调用。但MySQL只有执行insert\deldete\update时才能触发触发器的执行。触发器分为行级触发器和语句触发器，只要操作一条记录就使触发器执行一次。

案例说明：向学生信息表添加、删除、修改学生信息时，使用触发器进行日志记录

创建触发器，定义触发时间，定义触发操作，定义触发表名，触发器的操作。

        触发器用于监听数据表中的增删改查操作，其通常处理一些DML关联操作，可以使用NEW和OLD关键字在触发器中获取触发这个触发器的DML操作。 new：在触发器中用于获取insert操作添加的数据，update操作修改后的数据；OLD：在触发器中用于获取delete操作删除的数据，update操作修改前的数据。

         触发器的优点：触发器是自动执行的，对触发器相关的表执行响应的DML操作时立即执行；触发器可以实现表中数据的级联操作，有利于保障数据的完整性；触发器可以对DML操作的数据进行更为复杂的合法性校验。触发器的缺点：使用触发器实现的业务逻辑如果出现问题将难以定位，后期维护困难；大量使用触发器容易导致代码结构杂乱，增加了程序的复杂性；当触发器操作的数据量比较大时，执行的效率会大大降低。使用建议：互联网项目尽量避免触发器，对于并发量不大的项目选择使用存储过程，但互联网项目中不提倡使用存储过程，因为存储过程是将业务的实现逻辑交个数据库进行处理，一则增加了数据库的负载，二则不利于数据库的迁移。

 11.视图

        视图是指：数据库中一张表或多张表根据指定条件查询出的得出数据构造的出的虚拟表。视图的作用：安全性：将公开数据生成视图，授权用户访问视图，用户通过查询视图就可以获取数据表中公开数据，而对于隐藏数据则对用户隐藏；简单性：多表连接查询可通过视图直接查询，操作更便捷。

          视图数据的特性：视图是虚拟表，查询视图的数据是来源于数据库的，当对视图进行操作时，若数据表中添加或删除数据时，若这个数据满足创建视图时查询语句，则视图中更新相应的变化。若在视图中添加或删除数据，数据将会添加或删除到原数据表中。查询视图结构，修改视图；删除视图，删除数据表时会同时删除数据表中数据，删除视图时不会影响原数据表中的数据。

 12.索引

        数据库是用来存储数据的，数据的提取可以使用索引进行提取，数据表中数据的查询速度会随着数据量的增长而变慢，从而导致响应用户请求的速度变慢--用户体验变差。索引就是提高用户查询速率的，将数据表中某一列或某几列的值取出来构造成便于查找的结构进行存储，生成数据表的目录，当进行查询时，则现在目录中进行查找的到对应的数据地址，然后再到数据表中根据地址快速回去数据记录，避免全表扫描。

         索引的分类：根据创建索引的列的不同可以分为：主键索引：在数据表的主键字段创建的索引，必须使用primary key修饰，每张表只能有一个主键，将阻断声明为主键约束会自动在主键创建索引；唯一索引：数据表中为一列创建的索引unique，此列所有值只能出现一次；普通索引：在普通字段上创建索引，没有唯一性限制；组合索引：两个及以上字段联合起来创建的索引。

        索引的使用：当根据创建索引的列进行数据查询时会自动使用索引。优点：索引大大降低了数据库在执行查询操作时扫描的数据，避免服务器排序，将随机的IO变成顺序IO。缺点：当数据表中数据发生DML操作时，索引也需要更新，索引文件也会占用磁盘空间。注意事项：数据表中数据不多时，全表扫描可能更快，不要使用索引；数据量很大但是DML操作很频繁时，不建议使用索引；不要在数据重复度高的列上创建索引；创建索引后要注意查询SQL语句的编写，避免索引失效。

13.数据库事务

        数据库事务：将完成特定业务的多个数据库DML操作步骤称为一个事务，事务就是完成同一个业务的多个DML操作。事务的特性：原子性Atomicity：一个事务中的多个DML操作，要么同时执行成功，要么同时执行失败。一致性 Consistency：事务执行之前和事务执行之后数据库中的数据是一致的，完整性和一致性不能被破坏；隔离性Isolation：数据库允许多个事务同时执行，多个并行事务之间不能相互影响；持久性Durability：事务完成之后对数据的操作是永久的。

        MySQL事务管理：开启事务start transaction；事务回滚rollback，若在执行过程中任何位置出现异常则执行事务回滚，若没有异常则提交事务；提交事务commit

         事务隔离级别：数据库允许多个事务进行，多个事务之间是隔离的、相互独立的，如果事务之间不相互隔离并且操作一致数据时，会导致数据的一致性发生改变，出现问题。读取未提交的事务：事务2可以读取事务1中未提交的数据，则出现“脏读”。读已提交：事务2只能读取事务1已经提交的数据，可能导致不可重复度“虚读”，比如事务2第一次读取商品价格是3.00，但此时事务1进行修改价格并提交成功，事务2再次读取时间将读到的数据是2.5；可重复读：事务2执行一次查询后，在事务结束之前其他事务不能修改对应的数据，避免了不可重复读“虚读”，但有可能导致幻读。幻读：事务2对数据表中的数据进行修改然后查询，但是查询之前事务1向数据表中新增了数据，导致事务2以为修改了所有的数据，但却查询出于其修改不一致的数据。串行化：即序列化，同时只允许一个事务对数据表进行操作，避免了“脏读”、“虚读”、“幻读”。

        设置数据库事务隔离级别：设置数据库默认的事务隔离级别来控制事务之间的隔离性，也可以通过客户端与数据库的连接设置事务的隔离性。MySQL默认的隔离级别为：可重复读。查看MySQL默认的隔离级别：在8.0.3之前select @@tx_isolation，在8.0.3之后为select @@transaction_isolation；设置默认隔离级别：set session transaction isolation  level <read committed>设置为读已提交。

14.数据库设计

        数据库设计流程：根据应用系统的功能，分析数据实体即存储数据对象；提取实体的数据项，即数据的属性；根据数据库设计三范式规范实体的数据项（若不满足三范式可能会导致数据的冗余，从而引起数据维护困难，破坏数据的一致性问题）；绘制E-R图，即实体与实体之间的关系；数据库建模，使用三线图进行数据表设计或使用PowerDesigner或PDMan进行数据库建模；建库建表，编写SQL指令，创建数据库数据表；添加测试数据，SQL测试。

        数据库设计案例：图书管理系统。

        实体与数据项：

       学生：学号、姓名、性别、年龄、院系编号、院系名称、院系说明.....

        类别：类别ID、类别名称、类别描述

        图书：图书ID、图书名称、图书作者、图书封面、图书价格、图书库存......

        借书记录：记录ID、学号、图书编号、数量、是否归还、借书日期、还书日期

        管理员：管理员ID、登录名、登录密码、员工编号、员工姓名、员工联系方式（手机，qq，邮箱）

         数据库设计三范式：第一范式：数据表中的字段不可再分；第二范式：不存在非关键字段对关键字段的部分依赖，可以将每个关键字段列出或关键字段的组合也列出，依次检查每个非关键字段。第三范式：不存在非关键字段之间的依赖传递，可将非关键字段分别作为主键，依次检查所有非关键字段的依赖关系。

        绘制E-R图：entity-Relationship实体关系图，一对一，一对多，多对多的关系。