11111

数据库是存储、维护和管理数据的集合。

常见的数据库有：甲骨文(Oracle)，MySQL，DB2，Microsoft SQL Server

初始化：mysqld --initialize --console

安装mysql：mysqld --install [服务名]

            DLL

DDL用于库、表、列

            DDL操作数据库

创建 create database [数据库名] character  set [ 编码方式]  collate [ 排序规则]

查看 show create database [ 数据库名]

修改 alter database [ 数据库名]  character set [ 编码方式]

删除 drop database [ 数据库名]

切换  use [ 数据库名]

          DDL操作表

  创建表      CREATE TABLE 表名 (

                        列名1 数据类型 [ 约束 ],

                        列名2 数据类型 [ 约束 ],

                        列名n 数据类型 [ 约束 ]

                   )

查看字段 SHOW TABLES;

增加 alter table [ 表名]  add [ 新列名] [新的数据类型]

修改 alter table [ 表名]  change [ 旧列名] [新列名] [新的数据类型]

删除 alter table [ 表名]  drop [ 列名]

修改表名 alter table [ 旧表名]  rename [ 新表名]

查看细节 show create table [ 表名]

修改字符集 alter table [ 表名]  character set [ 编码方式]

          DML

DML是对表中数据进行增删改查 

插入 insert into 表名 ( 列名 ) values( 数据值 );

修改  UPDATE 表名 SET 列名 1= 列值 1, 列名 2= 列值 2 ... WHERE 列名 = 值

          查询

查询所有列 SELECT * FROM stu;

查询指定列 SELECT sid, sname, age FROM stu

条件查询就是在查询时给出 WHERE 子句，在 WHERE 子句中可以使用如下运算符及关键字：

=(等于)、!=(不等于)、<>(不等于)、<()小于、<=(小于等于)、>(大于)、>=(大于等于); BETWEEN…AND； IN(set)； IS NULL(为空值)； AND(和)；OR(或)； NOT(否)；

SELECT * FROM [表名] WHERE [列名] [运算符/关键字] [条件]

模糊查询 : 列名 like ' 表达式 ' // 表达式必须是字符串

                通配符: _(下划线 ): 任意一个字符    %：任意 0~n 个字符 ,' 张 %'

去重 SELECT DISTINCT sal FROM emp;

运算  SELECT *,sal+comm FROM emp; (若 列有很多记录的值为 NULL ，因为任何东西与 NULL 相加结果还是 NULL ，所以结算结果可能会出现 NULL 。下 面使用了把 NULL 转换成数值 0 的函数 IFNULL)

添加别名  SELECT *, sal+IFNULL(comm,0) AS total FROM emp  （as可忽略）

          排序

语法： order by 列名 asc/desc  asc 升序 desc 降序 默认不写的话是升序 

SELECT * FROM [表名] ORDER BY [列名] [asc(升)/desc(降)];

多列排序：当前面列值相同时，才会按照后面的值排序

         聚合函数

聚合函数是用来做纵向运算的函数：

COUNT( 列名 ) ：统计指定列不为 NULL 的记录行数；

        查询 emp表中记录数    SELECT COUNT(*) AS cnt FROM emp;

MAX( 列名 ) ：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；

        SELECT MAX([列名]) FROM [表名];

MIN( 列名 ) ：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；

        SELECT MIN([列名]) FROM [表名];

SUM( 列名 ) ：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为 0 ；

        单 SELECT SUM([列名]) FROM [表名];

        多 SELECT SUM([列名]+IFNULL([含null列名],0)) FROM [表名];

AVG( 列名 ) ：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为 0 ；

        SELECT AVG([列名]) FROM [表名];

          分组查询

当需要分组查询时需要使用 GROUP BY 子句，这说明要使用部分来分组

        如果查询语句中有分组操作，则 select 后面能添加的只能是聚合函数和被分组的列名

         SELECT [列名1], SUM([列名2) FROM [表名] GROUP BY [列名1];

         HAVING子句

HVAING子句是用来对分组后的数据进行筛选的，需要和GROUP BY子句配合

例： SELECT deptno, SUM(sal) FROM emp GROUP BY deptno HAVING SUM(sal) > 9000;

          LIMIT

LIMIT 用来限定查询结果的起始行，以及总行数。

        limit 开始下标, 显示条数； // 开始下标从 0 开始

        limit 显示条数； // 表示默认从 0 开始获取数据

                注意：起始行从 0 开始，即第一行开始

        SELECT * FROM [表名] LIMIT 0, 5;

           分页查询

若一页记录为 10 条，希望查看第 3 页记录应该怎么查呢？

        limit (pageindex-1)*pagesize,pagesize;

        pageIndex 页码值 pageSize 每页显示条数

                查询语句书写顺序：select – from- where- groupby- having- order by-limit

                查询语句执行顺序：from - where -group by -having - select - order by-limit

          数据库完整性

用来保证存放到数据库中的数据是有效的 , 即数据的有效性和准确性

确保数据的完整性 = 在创建表时给表中添加约束

        完整性的分类：

                实体完整性(行完整性 ):

                域完整性(列完整性):

                引用完整性(关联表完整性 ):

                主键约束：primary key

                唯一约束：unique [key]

                非空约束：not null

                默认约束：default

                自动增长：auto_increment

                外键约束: foreign key

          多表查询

多表之间的关系由多表约束：外键列来维护

        多表之间的关系分为：

                一对多/多对一

                多对多

                一对一

合并结果集 :UNION （去除重复记录） 、 UNION ALL（不去除重复记录）

SELECT* FROM t1 UNION（UNION ALL） SELECT * FROM t2；

 连接查询

 内连接 [INNER] JOIN ON

 外连接 OUTER JOIN ON

 左外连接 LEFT [OUTER] JOIN

        是先查询出左表（即以左表为主），然后查询右表，右表中满足条件的显示出来，不满足条            件的显示NULL

 右外连接 RIGHT [OUTER] JOIN

         是先把右表中所有记录都查询出来，然后左表满足条件的显示，不满足显示NULL

 全外连接（ MySQL 不支持） FULL JOIN

          子查询

子查询也称嵌套查询， 即 SELECT 中包含 SELECT ，如果一条语句中存在两个，或两个以上 SELECT

l 子查询结果集的形式：

         select * from emp e natural join dept d; a. 单行单列（用于条件）

        单行多列（用于条件）

        多行单列（用于条件）

        多行多列（用于表）

        例：

               SELECT * FROM emp WHERE sal > (SELECT sal FROM emp WHERE                ename='JONES');

          视图

视图（ VIEW ）是一张虚拟存在的表，本身没有数据，数据来自于创建视图时所查询的表，视图本身实际上就是一 条select 语句，存储的是 select 语句的查询结果集

视图中不能包含同名的列

视图的优点：简单，安全，数据独立

        创建视图

        -- 创建视图 create view 视图名称 as sql 语句

        create view view01 as select * from stu;

        -- 查询视图中的数据

        select * from view01;

        修改视图

        ALTER

        <查看处理算法，可选值为： {MERGE | TEMPTABLE | UNDEFINED}>

        VIEW

        <视图名 >

        AS

        <select语句 >

        ALTER VIEW view01 as select * from emp1;

        查看视图

         show tables; -- 查看全部表包括视图

        查看视图创建语句

        show create view 视图名;

        删除视图

        drop view 视图名 ;

视图重点

1视图表可以像普通表一样进行查询

2视图的sql如果是没有连表关系时【单表】可以允许insert，insert并非是视图，而是数据来源表，    如果这个视图中有连表则不允许insert 注入insert到的不是视图而是原表，除了视图使用原表字段    外，原表其他字段内容为null,其他字段需要允许插入null才可以

3视图是允许进行修改，修改字段后会映射到数据来源表的对应字段 必须是简单连表/单表才可修      改，复杂连表不行

4视图的sql如果是没有连表关系时【单表】可以允许delete，delete并非是视图，而是数据来源表     的对应数据，如果这个视图中有连表则不允许delete

连表修改

-- 创建一个连表视图

create view view02 as select emp1.empno,emp1.ename,dept1.dname,dept1.loc from emp1,dept1 where

dept1.deptno = emp1.deptno;

-- 使用 update 进行连表修改

UPDATE view02 SET ename=' 朱六 ' where empno=1006;

select * from emp1;

select * from view02;

        存储过程和函数概念

无参存储过程

存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合【流程】，可以通过调用执行这些存储过程和

函数，可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是

有好处的。

（1）. 定义方式稍微不同，函数关键词是FUNCTION，存储过程是PROCEDURE

（2）. 存储过程是无返回值，函数是有返回值

（3）. 调用方式不同，存储过程是"call 存储过程名()"调用，函数是在sql语句通过"函数名()"调用

DELIMITER <自定义分隔符，一般用&&，可以自己改>

CREATE PROCEDURE <自定义存储过程名>(<形参列表>)

BEGIN

/*存储过程内容*/

END<自定义分隔符，一般用&&，和上面的DELIMITER 后定义的相同>

DELIMITER ; /*将分隔符换回 ';' */

DELIMITER $$

-- 创建一个mmyprocedure01的存储过程，参数目前无参数

CREATE PROCEDURE myprocedure01()

有参存储过程

查看存储过程

删除存储过程

3.2.4 存储函数的操作

创建存储函数

-- 存储过程从此处开始

BEGIN

# 定义你的存储过程流程，就是一堆语句

SELECT * from emp1;

SELECT * from dept1;

-- 存储过程在此处结束

END $$

-- 将结束符换回来

DELIMITER ;

-- 调用存储过程

call myprocedure01();

DELIMITER $$

-- 创建一个mmyprocedure01的存储过程，参数目前无参数

CREATE PROCEDURE myprocedure02(IN sename VARCHAR(50), sdname VARCHAR(14))

-- 存储过程从此处开始

BEGIN

#定义你的存储过程流程，就是一堆语句

SELECT * from emp1 where ename = sename;

SELECT * from dept1 where dname = sdname;

-- 存储过程在此处结束

END $$

-- 将结束符换回来

DELIMITER ;

-- 调用存储过程 实际参数

call myprocedure02('张三','服务部');

-- 查询存储过程的状态信息，可以配合where语句使用

SHOW PROCEDURE STATUS;

-- 查询某个存储过程的定义

SHOW CREATE PROCEDURE 存储过程名;

-- 建议加 if exists 是验证是否存在，如果存在才会执行删除，不存在不会报错

DROP PROCEDURE IF EXISTS 存储过程名

这里有一个关键点：

DELIMITER <自定义分隔符，一般用&&，可以自己改>

CREATE FUNCTION <函数名>(<参数列表>)

RETURNS <定义返回值类型【INT,VARCHAR...】>

BEGIN

...

RETURN <具体的数据，类型要与RETURNS后定义的相匹配>

END<自定义分隔符，一般用&&，和上面的DELIMITER 后定义的相同>

DELIMITER ; /*将分隔符换回 ';' */

-- 存储函数 外层DELIMITER可有可没有

DELIMITER $$

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT

DETERMINISTIC

BEGIN

DECLARE result INT;

SET result = a + b;

RETURN result;

END $$

DELIMITER;

或者

CREATE FUNCTION add_numbers(a INT, b INT) RETURNS INT

DETERMINISTIC

BEGIN

DECLARE result INT;

SET result = a + b;

RETURN result;

END;

-- 调用存储函数

select add_numbers(1,2);

DETERMINISTIC（确定性）： 这个参数指示函数是否是确定性的。确定性函数意味着对于给定的输入，函数总是产生相同的输出，而不考虑调用时的上下文或环境。换句话说，如果函数对于相同的参数输入，总是返回相同的结果，则可以将其定义为确定性函数。这对于结果的缓存和查询优化非常有用。如果不确定函数是否满足这个条件，最好不要将其标记为 DETERMINISTIC。

NO SQL（无 SQL）： 这个参数指示函数是否访问数据库。如果函数没有在执行期间访问数据库，就可以将其标记为NO SQL。这包括不仅限于不对数据库进行写操作，也不执行查询操作。例如，一些数学函数或字符串处理函数可能不需要访问数据库。

READS SQL DATA（读取 SQL 数据）： 这个参数指示函数是否读取数据库中的数据。如果函数执行期间会读取数据库中的数据，但不进行写操作，就可以将其标记为 READS SQL DATA。这意味着函数在执行期间只读取数据，而不修改数据。例如，一个只执行 SELECT 查询的函数就可以被定义为 READS SQL DATA

存储引擎 事务

存储引擎

1、MySQL中的数据用各种不同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。

2、存储引擎是MySQ将数据存储在文件系统中的存储方式或者存储格式。

3、存储引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作。

4、MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

常用的存储引擎有MylSAM ，InnoDB(mysql默认存储引擎)

MylSAM介绍

1、MylSAM不支持事务，也不支持外键约束，只支持全文索引，数据文件和索引文件是分开保存的。

2、访问速度快，对事务完整性没有要求。

3、MylSAM适合查询、插入为主的应用。

4、 MylSAM在磁盘.上存储成三个文件，文件名和表名都相同，但是扩展名分别为：frm文件存储表结构的定义，数据文件的扩展名为.MYD (MYData)，索引文件的扩展名是.MYI (MYIndex)

MylSAM的特点

1、表级锁定形式，数据在更新时锁定整个表。

2、数据库在读写过程中相互阻塞：会在数据写入的过程阻塞用户数据的读取也会在数据读取的过程中阻塞用户的数据写入

3、数据单独写入或读取，速度过程较快且占用资源相对少。

MyISAM表支持3种不同的存储格式

（1）静态（固定长度）表

静态表是默认的存储格式。静态表中的字段都是非可变字段，这样每个记录都是固定长度的，这种存储方式的优点是存储非常迅速，容易缓存，出现故障容易恢复;缺点是占用的空间通常比动态表多。

（2）动态表

动态表包含可变字段，记录不是固定长度的，这样存储的优点是占用空间较少，但是频繁的更新、删除记录会产生碎片，需要定期执行OPTIMIZE TABLE语句或myisamchk-r命令来改善性能，并且出现故障的时候恢复相对比较困难（因为会产生磁盘碎片，而且存储空间不是连续的）。

（3）压缩表

压缩表由 myisamchk 工具创建，占据非常小的空间，因为每条记录都是被单独压缩的，所以只有非常小的访问开支。（压缩的过程中会占用CPU性能）

MyISAM使用的生产场景举例公司业务不需要事务的支持 单方面读取或写入数据比较多的业务 MylSAM存储引擎数据读写都比较频繁场景不适合（因为读写是互相阻塞的） 使用读写并发访问相对较低的业务 数据修改相对较少的业务 对数据业务-致性要求不是非常高的业务 服务器硬件资源相对比较差（MyISAM占用资源相对少）

InnoDB介绍

1、支持事务，支持4个事务隔离级别

2、MySQL从5.5.5版本开始，默认的存储引擎为InnoDB

3、读写阻塞与事务隔离级别相关

4、能非常高效的缓存索引和数据

5、表与主键以簇的方式存储 BTREE

6、支持分区、表空间，类似oracle数据库

7、支持外键约束，5.5前不支持全文索引，5.5后支持全文索引

8、对硬件资源要求还是比较高的场合

9、行级锁定，但是全表扫描仍然会是表级锁定，如

update table set a=1 where user like '%zhang%';

小贴士：

使用like进行模糊查询时，会进行全表扫描，锁定整个表。

对没有创建索引的字段进行查询，也会进行全表扫描锁定整个表。

使用索引进行查询，则是行级锁定。

InnoDB的特点

InnoDB中不保存表的行数，如 select count() from table; 时，InnoDB需要扫描一遍整个表来计算有多少行，但MyISAM只要简单的读出保存好的行数即可。需要注意的是当count()语句包含where条件时MyISAM也需要扫描整个表。

对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立组合索引。

delete清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM则会重建表。

InnoDB适用生产场景分析

业务需要事务的支持。

行级锁定对高并发有很好的适应能力，但需确保查询是通过索引来完成。

业务数据更新较为频繁的场景。

如：论坛，微博等。业务数据一致性要求较高。

如：银行业务。 硬件设备内存较大，利用InnoDB较好的缓存能力来提高内存利用率，减少磁盘IO的压力。

MyISAM和InnoDB的区别：

MyISAM：不支持事务和外键约束，占用空间较小，访问速度快，表级锁定，适用于不需要事务处理、单独写入或查询的应用场景。（写入和查询不一起使用的场景）

InnoDB：支持事务处理、外键约束、占用空间比MyISAM 大，支持行级锁定，读写开发能力较好，适用于需要事务处理、读写频繁的应用场景。

企业选择存储引擎依据

1、需要考虑每个存储引擎提供了哪些不同的核心功能及应用场景。

2、支持的字段和数据类型

所有引擎都支持通用的数据类型 但不是所有的弓|擎都支持其它的字段类型，如二进制对象. 3、锁定类型：不同的存储引擎支持不同级别的锁定

表锁定：MyISAM 支持

行锁定：InnoDB 支持

小贴士： 

在 MySQL 中，存储引擎的锁定类型指的是该存储引擎用于管理并发访问的锁定机制。锁定是数据库管理系统用来控制对数据的访问的一种机制，在多个并发用户同时访问数据库时确保数据的一致性和完整性。

1. MyISAM： MyISAM 存储引擎使用表级锁定（Table-level Locking）。这意味着当一个用户对表执行写操作时，会锁定整个表，导致其他用户无法同时对同一表进行写操作，但允许其他用户进行读操作。这可能会导致并发性能瓶颈，特别是在具有高并发写入操作的环境中。

2. InnoDB： InnoDB 存储引擎使用行级锁定（Row-level Locking）。这意味着它可以更细粒度地控制对数据的访问，只锁定需要修改的行，而不是整个表。这种机制允许多个用户同时对同一表进行读写操作，提高了并发性能和吞吐量。此外，InnoDB 还支持事务（Transactions）和行级锁定的事务隔离级别（Transaction Isolation Levels），从而提供了更高的数据完整性和一致性。

3. Memory（或 Heap）： Memory 存储引擎将数据存储在内存中，它使用表级锁定。与 MyISAM 类似，当一个用户对表执行写操作时，会锁定整个表。这种存储引擎适用于需要快速读写速度和临时数据存储的场景，但不适用于大型数据或长期存储。

4. 其他存储引擎： 其他存储引擎如 Archive、CSV、Blackhole 等也可能采用不同的锁定类型，具体取决于存储引擎的设计和实现。

查看系统支持的存储引擎

show engines\G;

查看数据表使用的存储引擎

show table status like ' 表名 '\G;

show table status like 'stu'\G;

修改表存储引擎

alter table 表名 engine= 存储引擎 ;

alter table stu engine=InnoDB;

          事务

事务代码

        start transaction; -- 开启事务

        -- 事务过程

        update b1 set money = money - 100 where name = ' 张辽 ' ;

        update b1 set money = money + 100 where name = ' 马超 ' ;

        commit; -- 提交事务

        rollback; -- 回滚事务 回滚事务是表示如果这个事务没有全部执行完成那么就回到原始状态         一旦提交事务commit后就不能回滚了

事务概念

事务（Transaction）指的是一个操作序列，该操作序列中的多个操作要么都做，要么都不做，是一个不可分割的工作单位，是数据库环境中的逻辑工作单位，由DBMS（数据库管理系统）中的事务管理子系统负责事务的处理。

目前常用的存储引擎有InnoDB（MySQL5.5以后默认的存储引擎）和MyISAM（MySQL5.5之前默认的存储引擎），其中InnoDB支持事务处理机制，而MyISAM不支持。

事务特性

事务处理可以确保除非事务性序列内的所有操作都成功完成，否则不会永久更新面向数据的资源。通过将一组相关操作组合为一个要么全部成功要么全部失败的序列，可以简化错误恢复并使应用程序更加可靠。 但并不是所有的操作序列都可以称为事务，这是因为一个操作序列要成为事务，必须满足事务的原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性（Durability）。这四个特性简称为ACID特性。

（1）原子性：原子是自然界最小的颗粒，具有不可再分的特性。事务中的所有操作可以看做一个原子，事务是应用中不可再分的最小的逻辑执行体。 使用事务对数据进行修改的操作序列，要么全部执行，要么全不执行。通常，某个事务中的操作都具有共同的目标，并且是相互依赖的。如果数据库系统只执行这些操作中的一部分，则可能会破坏事务的总体目标，而原子性消除了系统只处理部分操作的可能性。

（2）一致性：一致性是指事务执行的结果必须使数据库从一个一致性状态，变到另一个一致性状态。当数据库中只包含事务成功提交的结果时，数据库处于一致性状态。一致性是通过原子性来保证的。 例如：在转账时，只有保证转出和转入的金额一致才能构成事务。也就是说事务发生前和发生后，数据的总额依然匹配。

（3）隔离性：隔离性是指各个事务的执行互不干扰，任意一个事务的内部操作对其他并发的事务，都是隔离的。也就是说：并发执行的事务之间既不能看到对方的中间状态，也不能相互影响。

（4）持久性 持久性指事务一旦提交，对数据所做的任何改变，都要记录到永久存储器中，通常是保存进物理数据库，即使数据库出现故障，提交的数据也应该能够恢复。但如果是由于外部原因导致的数据库故障，如硬盘被损坏，那么之前提交的数据则有可能会丢失。

事务并发问题

1.脏读（Dirty read）

当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。

2.不可重复读 （Unrepeatableread）

指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。

1. 幻读 （Phantom read）
2. 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

事务隔离级别

事务的隔离级别用于决定如何控制并发用户读写数据的操作。数据库是允许多用户并发访问的，如果多个用户同时

开启事务并对同一数据进行读写操作的话，有可能会出现脏读、不可重复读和幻读问题，所以MySQL中提供了四种隔离级别来解决上述问题。

事务的隔离级别从低到高依次为READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ以及SERIALIZABLE，隔离级别越低，越能支持高并发的数据库操作。

事务总结

在MySQL中，事务通常用于以下场景：

1、订单处理：当用户下订单时，我们需要确保库存数量、金额等信息的正确性。如果出现错误，我们需要回滚订单，撤销库存的更改。

2、银行转账：当进行银行转账操作时，我们需要确保账户余额的正确性。如果出现错误，我们需要回滚转账操作，撤销资金的更改。

3、数据更新：当我们需要更新多个表中的数据时，我们可以使用事务来确保数据的一致性。例如，我们先更新一个表中的数据，然后再更新另一个表中的数据。如果在这个过程中出现了错误，我们可以回滚整个事务，撤销所有的更改。