MySQL学习笔记

2、Mysql

MySQL 教程 | 菜鸟教程 (runoob.com)

MySQL是一款关系数据库。 MySQL里面的My 不是 我的 意思。是创始人Michael Widenius 的闺女 My Widenius。
这是My Widenius的照片~

2.1、准备工作

1、启动数据库

要以管理员身份运行命令行窗口，搜索命令提示符-->右键-->更多-->以管理员身份运行

net start mysql服务名   //启动，可以设置开机自动启动，
				       //在我的电脑-管理-服务与应用程序-Mysql服务-启动方式（自动/手动）或者cmd - services.msc
net stop mysql服务名    //关闭

2、登陆数据库

mysql -h主机名 -P端口 -u用户名 -p密码 
mysql --host=ip --user=用户名 --password=密码     //都是两根杠
  • -p密码不要有空格
  • -p后面没有写密码，回车会要求输入密码
  • 如果没有写-h 主机，默认就是本机
  • 如果没有写-P 端口，默认就是3306
  • 在实际工作中，3306一般会修改

3、退出数据库

  1. quit
  2. exit

4、安装目录

  • bin：二进制可执行文件，配置环境变量就是用的这个目录

  • data：数据目录，存放日志和数据文件，数据库就是存放在这个目录

  • include：C语言头信息

  • lib：LIB文件中存放的是函数调用的信息，数据库有静态数据库（.lib文件）和动态数据库（.dll文件）。

  • share：存放mysql错误信息

  • 主目录下的my.ini 是mysql的配置文件，安装时必须设置配置文件，如果没有，则需自己创建。

    例如 D:\mysql-5.7.19-winx64\my.ini

5、MySQL的默认数据库

  • information_schema（信息数据库）

    information_schema 中的表实际上是视图，而不是基本表，因此，文件系统上没有与之相关的文件。它保存了MySQl服务所有数据库的信息。具体MySQL服务有多少个数据库，各个数据库有哪些表，各个表中的字段是什么数据类型，各个表中有哪些索引，各个数据库要什么权限才能访问。

    • SCHEMATA表：提供了当前mysql实例中所有数据库的信息。是show databases的结果取之此表。
    • TABLES表：提供了关于数据库中的表的信息(包括视图)。详细表述了某个表属于哪个schema，表类型，表引擎，创建时间等信息。是show tables from schemaname的　　结果取之此表。
    • COLUMNS表：提供了表中的列信息。详细表述了某张表的所有列以及每个列的信息。是show columns from schemaname.tablename的结果取之此表。
    • STATISTICS表：提供了关于表索引的信息。是show index from schemaname.tablename的结果取之此表。
    • USER_PRIVILEGES(用户权限)表：给出了关于全程权限的信息。该信息源自mysql.user授权表。是非标准表。
    • SCHEMA_PRIVILEGES(方案权限)表：给出了关于方案(数据库)权限的信息。该信息来自mysql.db授权表。是非标准表。
    • TABLE_PRIVILEGES(表权限)表：给出了关于表权限的信息。该信息源自mysql.tables_priv授权表。是非标准表。
    • COLUMN_PRIVILEGES(列权限)表：给出了关于列权限的信息。该信息源自mysql.columns_priv授权表。是非标准表。
    • CHARACTER_SETS(字符集)表：提供了mysql实例可用字符集的信息。是SHOW CHARACTER SET结果集取之此表。
    • COLLATIONS表：提供了关于各字符集的对照信息。
    • COLLATION_CHARACTER_SET_APPLICABILITY表：指明了可用于校对的字符集。这些列等效于SHOW COLLATION的前两个显示字段。
    • TABLE_CONSTRAINTS表：描述了存在约束的表。以及表的约束类型。
    • KEY_COLUMN_USAGE表：描述了具有约束的键列。
    • ROUTINES表：提供了关于存储子程序(存储程序和函数)的信息。此时，ROUTINES表不包含自定义函数(UDF)。名为“mysql.proc name”的列指明了对应于INFORMATION_SCHEMA.ROUTINES表的mysql.proc表列。
    • VIEWS表：给出了关于数据库中的视图的信息。需要有show views权限，否则无法查看视图信息。
    • TRIGGERS表：提供了关于触发程序的信息。必须有super权限才能查看该表

  • mysql（核心数据库）

    • 保存MySQL的权限、参数、对象和状态信息。
    • 如哪些user可以访问这个数据、DB参数、插件、主从

  • performance_schema

    • 主要用于收集数据库服务器性能参数
    • 提供进程等待的详细信息，包括锁、互斥变量、文件信息；
    • 保存历史的事件汇总信息，为提供MySQL服务器性能做出详细的判断；
    • 对于新增和删除监控事件点都非常容易，并可以随意改变mysql服务器的监控周期，例如（CYCLE、MICROSECOND）

  • test

    这是一个空表，测试表，可以删除。

  • sys

    Sys库所有的数据源来自：performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DB的运行情况。

2.2、SQL

SQL: (Structured Query Language) 结构化查询语言, 能够操作所有的关系型数据库(Relational DBMS)。在mysql> 里面书写

1、通用语法

  1. SQL语句可以单行或者多行书写，以分号结尾。

  2. 可使用空格和缩进提高语句的可读性

  3. Mysql数据库的SQL语句不区分大小写，关键字建议用大写。

  4. 有三种注释：

    快捷键： 注释shift+ctrl+c 取消注释shift+ctrl+r

    • 单行注释：-- 注释内容 或者 #注释内容 ，#注释是MySQL特有的注释方式，可以不加空格
    • 多行注释：*/* 注释 /

2、SQL分类

  1. DDL（Data Definition Language）数据定义语言

    用来定义数据库对象：数据库，表，列等。关键字：CREATE,DROP,ALTER等

  2. DML（Data Manipulation Language）数据操作语言

    用来对数据库中表的数据进行增删改。关键字：INSERT,DELETE,UPDATE等

  3. DQL（Data Query Language）数据查询语言（难点）

    用来查询数据库中表的记录（数据）。关键字：SELECT,WHERE等

  4. DCL（Data Control Language）数据控制语言（了解）

    用来定义数据库的访问权限和安全级别以及创建用户。关键字：GRANT,REVOKE等

DDL

（Data Definition Language）数据定义语言

1、操作数据库：CRUD

[ ] 表示可写可不写的语句

  1. C(Create):创建

    CREATE DATABASE 数据库名称;  #创建数据库，使用默认字符集utf8，默认字符集校对准则utf8_general_ci (不区分大小写)
CREATE DATABASE IF NOT EXISTS 数据库名; #先检查是否有该数据库再创建，如果不检查，重名会报错
CREATE DATABASE [IF NOT EXISTS] 数据库名 [CHARACTER SET utf8] [COLLATE utf8_general_ci]
    #先检查，再创建，设置字符集为utf8,设置字符集校对准则utf8_general_ci (不区分大小写)
    
[默认]CHARACTER SET utf8
[默认]COLLATE utf8_general_ci          --(utf8_bin区分大小写)

#当在该数据库创建表时，字符集和校对规则与数据库的一致

  2. R(Retrieve):查询

    SHOW DATABASES;  #查询所有数据库的名称
SHOW CREATE DATABASE 数据库名称; #查看某个数据库的创建语句，包括字符集等

  3. U(Update):修改

    ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称  #修改数据库字符集

  4. D(Delete):删除

    DROP DATABASE [IF EXISTS] 数据库名称；

  5. 使用数据库

    SELECT DATABASE(); #有括号，查询当前正在使用的数据库名称
USE 数据库名称;
2、操作表

  1. C(Create):创建

    1. 创建表

      CREATE TABLE 表名(                 #列名就是表头
    列名1 数据类型1,
    列名2 数据类型2,
    ...
    列名n 数据类型n    #最后一列不需要逗号
);

    2. 数据类型

      SQL 数据类型 (w3school.com.cn)

      数据类型 描述
      INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中size规定最大位数。
      DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定size最大位数。在 d 规定小数点右侧的最大位数。
      DATE() 日期。格式：YYYY-MM-DD 注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
      DATETIME() 日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
      TIMESTAMP() 时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS 注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
      注意：即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
      CHAR(size) 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中size指定字符个数。最多 255 个字符。 例如: zhangsan 八个字符 张三 2个字符
      VARCHAR(size) 保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中size指定字符串的字符个数。但是最多保存65535 个字节。 UTF8编码最大21844字符 1-3个字节用于记录大小
      如果表的编码是UTF8 varchar(size) 那么size <= (65535-3)/3 = 21844
      如果表的编码是GBK varchar(size) 那么size <= (65535-3)/2 = 32766
      查询速度：CHAR > VARCHAR
      • utf8 英文1字节 中文3字节
      • unicode-ucs-2 英文、中文都是2字节
      • gbk 英文1字节，中文2字节

  2. R(Retrieve):查询

    SHOW TABLES; #查询某个数据库中所有的表名称
DESC 表名;   #查询表结构

  3. U(Update):修改

    • 修改表名

      ALTER TABLE 表名 RENAME TO 新的表名;   -- 方法1
RENAME TABLE 表名 TO 新表名;           -- 方法2

    • 修改表的字符集

      ALTER TABLE 表名 CHATACTER SET 字符集名称;

    • 添加一列

      ALTER TABLE 表名 ADD [COLUMN] 列名 数据类型;

#如果想在一个已经建好的表中添加一列，可以用诸如：
alter table TABLE_NAME add [column] NEW_COLUMN_NAME varchar(20) not null;

#这条语句会向已有的表中加入新的一列，这一列在表的最后一列位置。如果我们希望添加在指定的一列，可以用：
alter table TABLE_NAME add [column] NEW_COLUMN_NAME varchar(20) not null after COLUMN_NAME;

#注意，上面这个命令的意思是说添加新列到某一列后面。如果想添加到第一列的话，可以用：
alter table TABLE_NAME add [column] NEW_COLUMN_NAME varchar(20) not null first;

    • 修改列名和类型

      ALTER TABLE 表名 CHANGE 列名 新列名 新的数据类型; #同时改列名和数据类型
ALTER TABLE 表名 MODIFY 列名 新的数据类型; #只改数据类型

    • 删除列

      ALTER TABLE 表名 DROP 列名;

  4. D(Delete):删除

    DROP TABLE [IF EXISTS] 表名; #删除表，需要进入相应数据库
DML

（Data Manipulation Language）数据操作语言，增删改表中的数据

1、添加数据
INSERT INTO 表名(列名1，列名2，...列名n) VALUES(值1，值2，...值n); #列名和值要相对应
INSERT INTO 表名 VALUES(值1，值2，...值n); #给所有列添加值，同样要对应

注意：除了数字类型，其他类型需要使用引号(单双都可以)

2、删除数据
DELETE FROM 表名 [WHERE 条件];   #例如：DELETE FROM stu WHERE ID=1;

注意：如果不加条件，则删除表中所有记录，非常危险！

​ 如果真要删除所有记录

DELETE FROM 表名；#不推荐使用。有多少条记录就会执行多少次删除操作，效率低
TRUNCATE TABLE 表名；#推荐使用。删除表，然后再创建一个一模一样的空表，效率高
3、修改数据
UPDATE 表名 SET 列名1=值1, 列名2=值2,...列名n=值n [WHERE 条件];

注意：如果不加条件，则会将表中所有记录全部修改，非常危险！

DQL（重点）

（Data Query Language）数据查询语言

1、语法
SELECT [DISTINCT] 字段列表 [[AS] 别名]   #DISTINCT表示去除重复的结果集,  AS可用空格替代
    FROM 表名列表 
    WHERE 条件列表 
    GROUP BY 分组字段 
    ORDER BY 排序
    HAVING 分组之后的条件限定 
    LIMIT 分页限定;
2、基础查询
SELECT English, Math, English + IFNULL(Math,0) [AS] 总分 FROM class;
#查询班级里英语、数学、总分

  1. 多个字段查询

    SELECT 字段名1，字段名2，... FROM 表名;  #如果查询所有字段，则可以用*来替代字段列表。

  2. 去除重复

    DISTINCT

  3. 计算列

    • 一般可以使用四则运算计算一些列的值。(一般只会进行数值型的计算)
    • IFNULL(表达式1，表达式2) 有NULL参与的计算，计算结果都为NULL
      • 表达式1：哪个字段需要判断是否为NULL
      • 表达式2：如果该字段为NULL则将其替换为表达式2

  4. 起别名

    字段/表达式 [AS] 别名   #AS可用空格替代
3、条件查询where

  1. WHERE 条件 条件并不一定带比较运算符，当不带运算符时，会执行一个隐式转换。当 0 时转化为 false，1 转化为 true。

  2. 运算符

    下面的运算符可以在 WHERE 子句中使用：

    运算符 描述
    = 等于
    <> 或 != 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
    > 大于
    < 小于
    >= 大于等于
    <= 小于等于
    BETWEEN x AND y 在[x，y]之间的值。x,y可以是表达式。 Select * from emp where sal between 1500 and 3000;
    LIKE (重点讲) 模糊查询。Select * from emp where ename like 'M%';
    IN 指定针对某个列的多个可能值 Select * from emp where age IN (19,22,27);
    AND 或 && 并且 Select * from emp where sal >= 1500 AND sal <= 3000; 与BETWEEN...AND...等效
    OR 或 || 或者
    NOT 或 ! 非
    IS NULL 查询空值。Select * from emp where comm is null; NULL值不能用=或!=来判断
    IS NOT NULL 查询不为空值。Select * from emp where comm is not null;

    LIKE(模糊查询)：

    1. 占位符

      • _ : 单个任意字符
      • % : 多个任意字符

    2. 使用举例

       Select * from emp where `name` = 'M%' ; #表示查询信息为M开头的。
 Select * from emp where `name` = '%M%'; #表示查询包含M的所有内容。
 Select * from emp where `name` = '%M_'; #表示查询以M在倒数第二位的所有内容。
4、排序查询order by

  1. 基本语法：

    • order by 子句
    • order by 排序字段1 排序方式1，排序字段2 排序方式2

  2. 排序方式：

    • ASC: 升序，默认的

    • DESC: 降序

      SELECT * FROM student ORDER BY math [ASC]; #升序
SELECT * FROM student ORDER BY math DESC;  #降序
SELECT * FROM student ORDER BY math [ASC], english [ASC];#如果数学成绩一样，则按英语成绩升序排列
SELECT * FROM student ORDER BY math [ASC], english DESC;#如果数学成绩一样，则按英语成绩降序排列

    • 如果有多个排序条件，则当前边的条件值一样时，才会判断第二条件。

5、聚合函数

聚合函数：将一列数据作为一个整体，进行纵向计算。

注意：聚合函数的计算，会排除 NULL 值，解决方案有两种

  • 选择非空的列进行计算。 COUNT(主键)
  • IFNULL函数

  1. count：计算个数

    SELECT COUNT(name) FROM student; #计算有多少个学生，会排除name=NULL的人
SELECT COUNT(IFNULL(english,0)) FROM student; #这样就不会排除英语成绩为NULL的人了
SELECT COUNT(id) FROM student;  #若id为主键，主键不能为空

  2. max：计算最大值

    SELECT MAX(math) FROM student;#会排除NULL值进行计算

  3. min：计算最小值

    SELECT MIN(math) FROM student;#会排除NULL值进行计算

  4. sum：求和

    SELECT SUM(math) FROM student;#会排除NULL值进行计算

  5. avg：计算平均值

    SELECT AVG(math) FROM student;#会排除NULL值进行计算
6、分组查询group by

  1. 语法

    SELECT 查询的字段 FROM 表名 GROUP BY 分组的字段

  2. 注意：

    • 分组之后想要查询的字段可以是：分组的字段、聚合函数

      SELECT sex,AVG(math),COUNT(id) FROM student GROUP BY sex;#显示性别，数学平均分，人数

    • where和having的区别？

      • where： 分组之前对分组条件进行限定，后面不可以跟聚合函数
      • having： 分组之后对查询结果进行过滤，可以跟聚合函数
      SELECT sex,AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;
#数学分数高于70才参与分组
SELECT sex,AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数 > 2;
#只显示分组人数大于2的组，后面的COUNT(id)可以用已定义的别名替换，AS省略
7、分页查询limit

查询结果太多时可以使用分页操作，便于显示

  1. 语法

    LIMIT 开始的索引，每页查询的条数；
SELECT * FROM student LIMIT 0,3; #第一页，每页三条数据
SELECT * FROM student LIMIT 3,3; #第二页，每页三条数据
#设当前的页码为n，每页显示的条数为m，则开始的索引为 startIndex = (n-1)*m
SELECT * FROM student LIMIT startIndex,m; #第n页，每页m条数据

  2. 公式：开始的索引 = （当前的页码 - 1）* 每页显示的条数

  3. 分页操作是一个“方言”，LIMIT 这个语句只能在MySQL中使用

DCL

（Data Control Language）数据控制语言

数据库管理员（Database Administrator，简称 DBA），是从事管理和维护数据库管理系统(DBMS)的相关工作人员的统称，属于运维工程师的一个分支，主要负责业务数据库从设计、测试到部署交付的全生命周期管理。DBA 的核心目标是保证数据库管理系统的稳定性、安全性、完整性和高性能。

1、管理用户

MySQL用户管理 (biancheng.net)

  1. 添加用户

    -- 先要进入mysql数据库
CREATE USE 'user_name'@'host_name' [IDENTIFIED BY '密码'];
-- 如果在创建的过程中，只给出了用户名，而没指定主机名，那么主机名默认为“%”，表示一组主机，即对所有主机开放权限。
-- IDENTIFIED BY用于指定用户密码。新用户可以没有初始密码，若该用户不设密码，可省略此子句。

    使用 CREATE USER 语句时应注意以下几点：

    • CREATE USER 语句可以不指定初始密码。但是从安全的角度来说，不推荐这种做法。
    • 使用 CREATE USER 语句必须拥有 mysql 数据库的 INSERT 权限或全局 CREATE USER 权限。
    • 使用 CREATE USER 语句创建一个用户后，MySQL 会在 mysql 数据库的 user 表中添加一条新记录。
    • CREATE USER 语句可以同时创建多个用户，多个用户用逗号隔开。

  2. 删除用户

    DROP USER 'user_name'@'host_name';  -- user_name + host_name 才叫一个完整的用户

  3. 修改用户密码

    1. 修改自己密码

      SET PASSWORD = PASSWORD('密码');

      问题：MySQL中忘记了root用户密码怎么办？

      1. (管理员身份)cmd > net stop mysql 停止MySQL的服务
      2. 使用无验证方式启动MySQL服务：mysqld --skip-grant-tables ，保持这个cmd窗口存在
      3. 打开一个新的cmd窗口，直接输入mysql回车
      4. 进入mysql数据库，修改user中的数据，关闭两个窗口
      5. 在任务管理器中，进程--> mysqld.exe --> 结束进程

    2. 修改他人密码（需要有修改用户密码的权限）

      SET PASSWORD FOR 'user_name'@'host_name' = PASSWORD('新密码');
-- 也可以按照修改数据的语句来改表中的数据
UPDATE USER SET authentication_string = PASSWORD('新密码') WHERE USER = 'user_name';
      • PASSWORD( ) 其实是一个加密函数，表示使用哈希值设置密码
      • MySQL 5.7 的 user 表中的密码字段从 Password 变成了 authentication_string，
      • 如果使用的是 MySQL 5.7 之前的版本，将 authentication_string 字段替换成 Password 即可。

  4. 查询用户

    1. 切换到MySQL数据库

      USE mysql;

    2. 查询user表

      SELECT * FROM USER;     -- 通配符%表示可以在任意主机登陆数据库
2、管理权限

首先进入mysql数据库

  1. 查询权限

    SHOW GRANTS FOR 'user_name'@'host_name';

  2. 授予权限

    在 MySQL 中，拥有 GRANT 权限的用户才可以执行 GRANT 语句，其语法格式如下：

    GRANT priv_type [(column_list)] ON database.table
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user[IDENTIFIED BY [PASSWORD] 'password']] ...
[WITH with_option [with_option]...]

    其中：

    • priv_type 参数表示权限类型，可以同时授予多个权限，由于权限类型太多，不在此罗列；
    • columns_list 参数表示权限作用于哪些列上，省略该参数时，表示作用于整个表；
    • database.table 用于指定权限的级别；
    • user 参数表示用户账户，格式是'username'@'hostname'；
    • IDENTIFIED BY 参数用来为用户设置密码；
    • password 参数是用户的新密码。

    WITH 关键字后面带有一个或多个 with_option 参数。这个参数有 5 个选项，详细介绍如下：

    • GRANT OPTION：被授权的用户可以将这些权限赋予给别的用户；
    • MAX_QUERIES_PER_HOUR count：设置每个小时可以允许执行 count 次查询；
    • MAX_UPDATES_PER_HOUR count：设置每个小时可以允许执行 count 次更新；
    • MAX_CONNECTIONS_PER_HOUR count：设置每小时可以建立 count 个连接;
    • MAX_USER_CONNECTIONS count：设置单个用户可以同时具有的 count 个连接。

    database.table，在 GRANT 语句中可用于指定权限级别的值有以下几类格式：

    • *：表示当前数据库中的所有表。
    • *.*：表示所有数据库中的所有表。
    • db_name.*：表示某个数据库中的所有表，db_name 指定数据库名。
    • db_name.tbl_name：表示某个数据库中的某个表或视图，db_name 指定数据库名，tbl_name 指定表名或视图名。
    • db_name.routine_name：表示某个数据库中的某个存储过程或函数，routine_name 指定存储过程名或函数名。
    • TO 子句：如果权限被授予给一个不存在的用户，MySQL 会自动执行一条 CREATE USER 语句来创建这个用户，但同时必须为该用户设置密码。

  3. 删除权限

    1. 第一种

      删除用户某些特定的权限，语法格式如下：

      REVOKE priv_type [(column_list)]...
ON database.table
FROM user [, user]...

      REVOKE 语句中的参数与 GRANT 语句的参数意思相同。其中：

      • priv_type 参数表示权限的类型；
      • column_list 参数表示权限作用于哪些列上，没有该参数时作用于整个表上；
      • user 参数由用户名和主机名构成，格式为 ‘username'@'hostname'。

    2. 第二种

      删除特定用户的所有权限，语法格式如下：

      REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

      删除用户权限需要注意以下几点：

      • REVOKE 语法和 GRANT 语句的语法格式相似，但具有相反的效果。
      • 要使用 REVOKE 语句，必须拥有 MySQL 数据库的全局 CREATE USER 权限或 UPDATE 权限。

3、约束

约束是对表中的数据进行限定，保证数据的正确性、有效性和完整性。比如禁止添加一条人名为NULL的数据

分类：

  1. 非空约束：not null
  2. 唯一约束：unique
  3. 主键约束：primary key
  4. 外键约束：foreign key
非空约束 not null

某一列的值不能为null

1、在创建表时，添加约束
CREATE TABLE stu(
	id INT,
    `name` VARCHAR(20) NOT NULL # name为非空
);  #当添加name=null的数据时，会报错，Field 'name' doesn't have a default value
2、在创建表后，添加约束
ALTER TABLE stu MODIFY `name` VARCHAR(20) NOT NULL;
3、删除约束
ALTER TABLE stu MODIFY `name` VARCHAR(20); #不指定not null即为删除了非空约束
4、注意

在创建表后，能添加非空约束的前提是该字段的值都不为NULL

唯一约束 unique

某一列的值不能重复

1、在创建表时，添加约束
CREATE TABLE stu(
	id INT,
    phone_num CHAR(11) UNIQUE #每个人的手机号唯一
);
2、在创建表后，添加约束
ALTER TABLE stu MODIFY phone_num CHAR(11) UNIQUE; #能添加唯一约束的前提是该字段的值没有重复
3、删除约束(特殊)
#正确方式
ALTER TABLE stu DROP INDEX phone_num; #与删除非空约束不同
#错误方式
ALTER TABLE stu MODIFY phone_num CHAR(11);
4、注意
  1. 唯一约束可以有NULL值，且可以有多条记录为NULL，前提是没有设置非空
  2. 在创建表后，能添加唯一约束的前提是，该字段的值没有重复
  3. 删除唯一约束使用 DROP INDEX 字段名
主键约束 primary key

非空且唯一,一张表只能有一个字段为主键, 是表中记录的唯一标识，效果等效为unique not null

1、在创建表时，添加主键

  • 方法1：直接在字段后指定

    CREATE TABLE stu(
	id INT PRIMARY KEY, #给id添加主键约束
    `name` VARCHAR(20)
); 
#当添加id为重复或者NULL的数据时，会报错，Field 'name' doesn't have a default value

  • 方法2：在表定义的最后写 primary key(列名)

    CREATE TABLE stu(
	id INT, 
    `name` VARCHAR(20),
    PRIMARY KEY(id)  #给id添加主键约束
);
2、在创建表后，添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
3、删除主键(特殊)
#正确方式
ALTER TABLE stu DROP PRIMARY KEY; #不用指定哪个字段，因为主键唯一
#错误方式
ALTER TABLE stu MODIFY id INT;
4、复合主键

一张表最多只能有一个主键，但可以是复合主键(比如 id + name)

CREATE TABLE stu(
	id INT, 
    `name` VARCHAR(20),
    math DOUBLE(4,1), #最大位数为4，小数点后最多1位
    PRIMARY KEY(id,'name') #id和name做成复合主键，当id和name都相同时才叫重复，但是id和name都不能为NULL
);
5、注意
  1. 创建表后，能添加主键约束的前提是，该字段的值不重复且非NULL
  2. 删除主键约束使用 DROP PRIMARY KEY，不加字段名
  3. 可以使用复合主键，复合主键的所有字段都相同时才叫重复，但是每一个字段都不能为NULL。
外键约束 foreign key

用于定义从表和主表之间的关系

1、创建表时，添加外键
#创建主表
CREATE TABLE my_class(
	id INT PRIMARY KEY,                       -- 班级编号
    `name` VARCHAR(32) NOT NULL DEFAULT ''    -- 默认值为空字符串
);

#创建从表
CREATE TABLE my_stu(
	id INT PRIMARY KEY,                       -- 学生编号
    `name` VARCHAR(32) NOT NULL DEFAULT '',   -- 默认值为空字符串
    class_id INT,                             -- 学生所在班级编号
    #指定外键关系
    [CONSTRAINT 外键名称] FOREIGN KEY (class_id) REFERENCES my_class(id)
);
#外键名称一般命名为：从表名_主表名_fk     fk是外键的缩写
2、创建表后，添加外键
ALTER TABLE 从表 ADD [CONSTRAINT 外键名称] FOREIGN KEY (从表字段) REFERENCES 主表(字段);
3、删除外键
ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;     -- 是外键名称不是外键字段
4、级联操作（更新、删除）

如果父表试图UPDATE或者DELETE任何子表中存在或匹配的外键值，最终动作取决于外键约束定义中的ON UPDATE和ON DELETE选项。InnoDB支持5种不同的动作，默认的动作为ON DELETE RESTRICT ON UPDATE RESTRICT 。

  1. CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新子表中匹配的行。ON DELETE CANSCADE和ON UPDATE CANSCADE都被InnoDB所支持。

  2. SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效。ON DELETE SET NULL和ON UPDATE SET SET NULL都被InnoDB所支持。

  3. NO ACTION: InnoDB拒绝删除或者更新父表。

  4. RESTRICT: 拒绝删除或者更新父表，使用时替换CASCADE的位置，指定RESTRICT（或者NO ACTION）和忽略ON DELETE或者ON UPDATE选项的效果是一样的。

  5. SET DEFAULT: InnoDB目前不支持。

  6. 添加级联

    • 创建表时，添加级联

      #创建从表
CREATE TABLE my_stu(
	id INT PRIMARY KEY,                       -- 学生编号
    `name` VARCHAR(32) NOT NULL DEFAULT '',   -- 默认值为空字符串
    class_id INT,                             -- 学生所在班级编号
    #指定外键关系
    [CONSTRAINT 外键名称] FOREIGN KEY (class_id) REFERENCES my_class(id) 
    					[ON UPDATE CASCADE] [ON DELETE CASCADE]
);

    • 创建表后，添加级联

      #如果添加级联的时候已经有外键约束，则需先删除外键，再同时添加外键和级联。
#ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;
ALTER TABLE 表名 ADD [CONSTRAINT 外键名称] FOREIGN KEY (外键字段) REFERENCES 主表(字段) 
[ON UPDATE CASCADE] [ON DELETE CASCADE];

  7. 删除级联(外键)

    ALTER TABLE 从表 DROP FOREIGN KEY 外键名称;#取消级联只能通过删除外键实现

  8. 级联操作只能在主表

    • ON UPDATE CASCADE：主表修改级联的数据会导致从表数据修改
    • ON DELETE CASCADE：主表删除级联的数据会导致从表数据删除
    • 从表的级联数据修改(其中不符合外键的修改会失败)或者删除不会影响主表
5、注意

  1. 外键指向的主表的字段，要求是primary key 或者是 unique

  2. 表的类型是innoDB，这样的表才支持外键

    InnoDB：是MySQL的数据库引擎之一，现为MySQL的默认存储引擎，支持了ACID兼容的事务（Transaction）功能。

  3. 外键字段的类型要和主键字段类型一致(长度可以不同)

  4. 外键字段的值，必须在主键字段中出现过，或者为NULL（前提是从表外键字段允许为NULL）

  5. 删除主表数据时，需要先把从表中有关联的数据改变或删除，确保该条数据与从表数据无关联，才可以删除；添加主表数据无约束。

  6. 删除从表数据无约束；添加从表数据有约束。

  7. 级联操作的前提是有外键约束，在从表设置级联，在主表操作级联。

  8. 级联有弊端，主表操作会影响从表，需慎重使用。

4、自增长

如果某一列是数值类型的，使用auto_increment 可以来完成自动增长

1、创建表时，添加自增长
CREATE TABLE stu(
	id INT PRIMARY KEY AUTO_INCREMENT, #给id添加主键约束,并设置自增长
    'name' VARCHAR(20)
); 
INSERT INTO stu VALUES(NULL,'Jack');# id=1, name=Jack,自增长默认从1开始
INSERT INTO stu VALUES(NULL,'Mary');# id=2, name=Mary
INSERT INTO stu VALUES(10,'Smith'); # id=10,name=Smith ,也可以手动委派值,自增长开始值也会变
INSERT INTO stu VALUES(NULL,'Tom');# id=11, name=Tom
2、创建表后，添加自增长
ALTER TABLE stu MODIFY id [PRIMARY KEY] INT AUTO_INCREMENT;#如无主键，则需添加主键或者唯一约束
3、删除自增长
ALTER TABLE stu MODIFY id INT; #只删除了自增长，主键不能通过这种方式删除，需要通过drop primary key
4、设置自增长开始值
ALTER TABLE stu AUTO_INCREMENT = 开始值;

注意：

  1. 一般来说自增长是跟primary key配合使用的
  2. 自增长也可以单独使用 【但是需要配合一个unique】
  3. 自增长修饰的字段为数值型，一般为整数，很少使用小数
  4. 自增长默认从1开始，也可以自定义自增长的开始值
  5. 如果添加数据时，给自增长字段指定的有值，则以指定的值为准；如果指定了自增长，一般来说，就按照自增长的规则来添加数据。

2.3、数据库的设计

1、多表之间的关系

分类

  • 一对一：人和身份证号。很少用，因为可以做成同一张表

    实现：可以在任意一方添加有唯一约束unique的外键指向另一方的主键。

  • 一对多：部门和员工

    实现：在多张从表建立 外键 对应一张主表

  • 多对多：学生和课程

    实现：需要借助一张中间表，这中间表至少有两个字段，分别为两张表的主键，这两个字段分别作为外键指向两张主表的主键，

    再把中间表的两个字段做成复合主键。

2、三大范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。

范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

1、第一范式(1NF)

确保每列保持原子性，比如一个班级可以分为班级号和班主任

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

​ 原表格

学号 姓名 班号 班主任 课程 分数
1 张无忌 102 张三丰 数学 95
1 张无忌 102 张三丰 英语 23
3 任我行 113 风清扬 物理 77
4 杨过 107 小龙女 生物 86

存在的问题：

  1. 存在非常严重的数据冗余问题。例如：姓名、班号、班主任
  2. 在数据添加时存在问题，新添加的数据有可能存在数据不合法的情况。 例如：添加新开的班级和班主任，但是暂时没有招收学生
  3. 删除数据时也存在问题。例如：张无忌毕业了，删除张无忌的数据后，该班级的信息也消失了
2、第二范式(2NF)

消除部分依赖

在1NF的基础上，非主属性必须完全依赖于主属性，消除非主属性对主属性的部分依赖。

概念

  • 函数依赖：A--->B，如果通过A属性(组)的值，可以确定唯一B属性的值，则称B依赖于A。

    例如：姓名、班号、班主任都可以由学号确定，而课程和分数不能确定。

  • 完全函数依赖：A--->B，如果A是一个属性组，则B属性值的确定需要依赖于A属性组中所有的属性值。

    例如：（学号，课程）为属性组，则分数需要由学号和课程共同确定。而其他字段则可以只由学号确定（部分函数依赖）。

  • 部分函数依赖：A--->B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中部分的属性值。

    例如：（学号，课程）为属性组，班主任只需要由学号确定。

  • 传递函数依赖：A--->B，B--->C，如果通过A属性(组)的值，可以唯一确定B属性(组)的值，再通过B属性(组)的值可以唯一确定C属性的值。

    例如：学号--->班号--->班主任

  • 码(用作主键)：如果在一张表中，一个属性(组)，被其他所有属性所完全依赖，则称这个属性(组)为该表的码。

    例如：该表的码为（学号，课程名称）或者（姓名，课程名称）

    • 主属性：码属性组中的所有属性
    • 非主属性：码属性组之外的属性
表格拆分

​ 学生表

学号 姓名 班号 班主任
1 张无忌 102 张三丰
1 张无忌 102 张三丰
3 任我行 113 风清扬
4 杨过 107 小龙女

​ 选课表

学号 课程 分数
1 数学 95
1 英语 23
3 物理 77
4 生物 86

存在的问题：

  1. 存在非常严重的数据冗余问题。例如：姓名、班号、班主任
  2. 在数据添加时存在问题，新添加的数据有可能存在数据不合法的情况。 例如：添加新开的班级和班主任，但是暂时没有招收学生
  3. 删除数据时也存在问题。例如：张无忌毕业了，删除张无忌的数据后，该班级的信息也消失了
3、第三范式(3NF)

消除传递依赖， 学号--->班号--->班主任

在2NF的基础上，任何非主属性不依赖于其他非主属性。确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

​ 学生表

学号 姓名 班号
1 张无忌 102
1 张无忌 102
3 任我行 113
4 杨过 107

​ 选课表

学号 课程 分数
1 数学 95
1 英语 23
3 物理 77
4 生物 86

​ 班级表

班号 班主任
102 张三丰
113 风清扬
107 小龙女

存在的问题：

  1. 存在非常严重的数据冗余问题。例如：姓名、班号、班主任

  2. 在数据添加时存在问题，新添加的数据有可能存在数据不合法的情况。 例如：添加新开的班级和班主任，但是暂时没有招收学生

    可以单独添加班级表中的数据

  3. 删除数据时也存在问题。例如：张无忌毕业了，删除张无忌的数据后，该班级的信息也消失了

    可以单独删除学生表和选课表中的数据

2.4、数据库的备份和还原

1、命令行

备份数据库

在DOS执行，不是mysql中

//备份一个数据库[表]
mysqldump -u用户名 -p密码 dbname [tbname ...] > filename.sql    //filename包括了路径
//也可以是txt文件，但是建议sql，容易识别这是个数据库文件，文件中其实就是一些SQL语句

//备份多个数据库
mysqldump -u用户名 -p密码 -B 数据库1 数据库2 数据库n > filename.sql //写法1
mysqldump -u用户名 -p密码 --databases 数据库1 数据库2 数据库n > filename.sql  //写法2
mysqldump -u用户名 -p密码 --all-databases > filename.sql    //备份所有数据库
  • dbname：表示需要备份的数据库名称；
  • tbname：表示数据库中需要备份的表，可以指定多个表。省略该参数时，会备份整个数据库；
还原数据库

方法1：

  1. 登陆数据库

  2. 创建数据库

    CREATE DATABASE 数据库;

  3. 使用数据库

    USE 数据库;

  4. 执行source文件

    SOURCE filename.sql

方法2：

  1. 打开filename.sql，在顶部手动添加创建数据库和使用数据库的语句

    CREATE DATABASE db_test;
USE db_test;

  2. 在DOS中

    mysql -u用户名 -p密码 [dbname] < filename.sql //如果指定数据库名称，指定的数据库名不存在将会报错；

2、图形化工具（SQLyog）

右键数据库 ---> 备份/导出 ---> 备份数据库，转储到SQL

2.5、多表查询

1、笛卡尔集

在多表查询中，如果同时查询表A、表B而不加查询条件，A X B所形成的集合就叫笛卡尔集。

注意: 查询条件不能少于表的个数-1,不然会出现笛卡尔集 , 多表查询需要消除其中的无用数据.

2、多表查询的分类

1、内连接查询
  • 从哪些表中查询数据
  • 查询条件是什么
  • 查询哪些字段
1、隐式内连接

使用where条件来消除无用数据

SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.dept_id = dept.id;

语句优化，可读性好

SELECT
	t1.name,		 #员工姓名
	t1.gender,		 #员工性别
	t2.name			 #部门名称
FROM				 
	emp t1, 		 #别名
	dept t2    	     
WHERE
	t1.dept_id = t2.id;
2、显式内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件；

SELECT
	t1.name,		 #员工姓名
	t1.gender,		 #员工性别
	t2.name			 #部门名称
FROM				 
	emp t1, 		 #别名
[INNER] JOIN
	dept t2          	     
ON
	t1.dept_id = t2.id;
2、外连接查询

​ my_stu

id name class_id
2019210236 赵宏韬 2
2019210237 肖南海 2
2019210238 王宣程 3
2019210239 魏鑫 4
2019210240 黄俊凯 1

​ my_class

id name
1 通信工程
2 电子信息工程
3 信息工程
1、左外链接
  • 表1 为基表，表2 为参考表。左连接查询时，可以查询出 表1 中的所有记录和 表2 中匹配连接条件的记录。
  • 如果 表1 的某行在 表2 中没有匹配行，那么在返回结果中，属于 表2 的字段值均为空值（NULL）。
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件；

SELECT
	t1.id AS '学号',
	t1.name AS '姓名',
	t2.id AS '班级'
FROM
	my_stu AS t1
LEFT JOIN
	my_class AS t2
ON
	t1.class_id = t2.id;

​ 左外链接查询表

学号 姓名 班级
2019210236 赵宏韬 2
2019210237 肖南海 2
2019210238 王宣程 3
2019210239 魏鑫 （NULL）
2019210240 黄俊凯 1
2、右外链接

与左外连接同理

SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件；
3、子查询

WHERE <表达式> <操作符> (子查询)

其中，操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。

EXISTS 表达式返回 true或者false，外层查询收到true返回所有记录, false则一条记录都不返回，这与 = 不同。

1、概念

将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。

外层的 SELECT 查询称为父查询，圆括号中嵌入的查询称为子查询（子查询必须放在圆括号内）。

MySQL 在处理上例的 SELECT 语句时，执行流程为：先执行子查询，再执行父查询。

2、基本演示

子查询在 WHERE 中的语法格式如下：

-- 查询工资最高的员工信息
-- 1 查询最高工资是多少？  假如是9000
SELECT MAX(salary) FROM emp;
-- 2 查询工资为9000的员工信息
SELECT * FROM emp WHERE salary = 9000;

-- 用子查询合并语句
SELECT * FROM emp WHERE salary = (SELECT MAX(salary) FROM emp);
3、子查询的不同情况

  1. 子查询的结果是单行单列的 (一个确定值)

    查询结果可以作为条件，当作一个确定值，使用运算符( > >= < <= = != )进行判断。

  2. 子查询的结果是多行单列的 (数组)

    查询结果可以当作一个数组。使用运算符 IN 进行判断。

  3. 子查询的结果是多行多列的 (子表)

    查询结果可以当作一张虚拟表，而且可以起别名。再用这张表进行下一步查询

4、自连接

在同一张表的连接查询，把同一张表分别取两个别名，当成两张表查询

如果列名不明确，可以指定列的别名

-- 综合题
-- 需求:显示公司员工名字和他上级的名字,没有上级的也需要查询,公司员工的信息都在同一张表
-- 显示公司员工名字和他上级的名字,自连接
-- 没有上级的也需要查询,左外连接
SELECT
	worker.name [AS] '职员名',
	boss.name [AS] '上级名'
FROM
	emp [AS] worker
LEFT [OUTER] JOIN
	emp [AS] boss
ON
	worker.mgr = boss.empno;
5、合并查询

有时候在实际应用中，为了合并多个select语句的结构，可以使用集合操作符union 和 union all

1、union

该操作符用于取得两个结果集的并集。当使用该操作符时，会自动去掉结果集中的重复行。

2、union all

与union类似，但是不会取消重复行

SELECT ename,sal,job FROM emp WHERE sal > 2500;         -- 结果集1
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';	-- 结果集2
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal > 2500;
UNION          -- 去重
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

SELECT ename,sal,job FROM emp WHERE sal > 2500;
UNION ALL      -- 不去重
SELECT ename,sal,job FROM emp WHERE job = 'MANAGER';

2.6、事务

1、事务的基本介绍

1、概念

事务用于保证数据的一致性，它由一组相关的DML语句(增删改，不包括查)组成，该组的DML语句要么全部成功，要么全部失败。

例如：张三给李四转账500元

  1. 查询张三账户余额是否有500元
  2. 张三账户余额 - 500元
  3. 李四账户余额 + 500元
2、操作
  1. 开启事务 start transaction
  2. 设置保存点 savepoint
  3. 回滚至指定保存点 rollback to
  4. 回滚至起点 rollback
  5. 提交事务 commit ：确认事务的变化，结束事务，删除所有保存点，释放锁，数据生效，不能回滚，其他会话[连接]可以查看到事务变化后的新数据[所有数据正式生效]
-- 开始事务
START TRANSACTION;

-- 执行DML操作
#此处为DML语句

-- 设置保存点
SAVEPOINT a;
#此处为DML语句

-- 设置保存点
SAVEPOINT b;
#此处为DML语句

...

-- 回退到b
[ROLLBACK TO b;]

-- 回退到a
[ROLLBACK TO a;]

-- 回退到事务开始
[ROLLBACK;]

-- 提交事务
COMMIT;
3、细节
  1. 如果不开始事务，默认情况下，MySQL是自动提交的，不能回滚；Oracle是手动提交的，可以回滚。
  2. 如果开始一个事务，且没有创建保存点。可以执行rollback，默认回退到事务开始的状态。
  3. 在事务中(还未提交)，可以创建多个保存点。
  4. 在事务还没有提交前，可以选择回退到哪个保存点。
  5. MySQL事务机制需要innoDB的存储引擎才可以使用，myisam不行。
  6. 查看事务的默认提交方式：select @@autocommit; -- 1代表自动提交 0代表手动提交
  7. 开始一个事务有两种方式
    1. start transaction；
    2. set autocommit = off ；或者 set @@autocommit = 0；

2、事务的四大特征ACID（常见面试题）

  1. 原子性(Atomicity)

    整个事务中的所有操作，要么全部完成，要么全部不完成，不可能停滞在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

  2. 一致性(Correspondence)

    在事务开始之前和事务结束以后，数据库的完整性约束没有被破坏。

  3. 隔离性(Isolation)

    隔离状态执行事务，使它们好像是系统在给定时间内执行的唯一操作。如果有两个事务，运行在相同的时间内，执行 相同的功能，事务的隔离性将确保每一事务在系统中认为只有该事务在使用系统。这种属性有时称为串行化，为了防止事务操作间的混淆， 必须串行化或序列化请 求，使得在同一时间仅有一个请求用于同一数据。

  4. 持久性(Durability)

    在事务完成以后，该事务所对数据库所作的更改便持久的保存在数据库之中，并不会被回滚。

3、事务的隔离级别（了解）

彻底搞懂 MySQL 事务的隔离级别-阿里云开发者社区 (aliyun.com)

1、事务并发可能出现的情况
① 脏读（Dirty Read）

一个事务读到了另一个未提交事务修改过的数据。

② 不可重复读（Non-Repeatable Read）

在同一个事务中，两次读取到的数据不一样。一个事务只能读到另一个已经提交的事务修改过的数据，并且其他事务每对该数据进行一次修改并提交后，该事务都能查询得到最新值。（不可重复读在读未提交和读已提交隔离级别都可能会出现）

③ 幻读/虚读（Phantom）

一个事务先根据某些条件查询出一些记录，之后另一个事务又向表中插入了符合这些条件的记录，原先的事务再次按照该条件查询时，能把另一个事务插入的记录也读出来。（幻读在读未提交、读已提交、可重复读隔离级别都可能会出现）

2、事务的隔离级别

MySQL的事务隔离级别一共有四个，分别是读未提交、读已提交、可重复读以及可串行化。

MySQL的隔离级别的作用就是让事务之间互相隔离，互不影响，这样可以保证事务的一致性。

隔离级别比较：可串行化>可重复读>读已提交>读未提交

隔离级别对性能的影响比较：可串行化>可重复读>读已提交>读未提交

由此看出，隔离级别越高，所需要消耗的MySQL性能越大（如事务并发严重性），为了平衡二者，一般建议设置的隔离级别为可重复读，MySQL默认的隔离级别也是可重复读。

① 读未提交（READ UNCOMMITTED）
  • 在读未提交隔离级别下，事务A可以读取到事务B修改过但未提交的数据。
  • 可能发生脏读、不可重复读和幻读问题，一般很少使用此隔离级别。
② 读已提交（READ COMMITTED）
  • 在读已提交隔离级别下，事务A只能在事务B修改过并且已提交后才能读取到事务B修改的数据。
  • 读已提交隔离级别解决了脏读的问题，但可能发生不可重复读和幻读问题，一般很少使用此隔离级别。
  • Oracle的默认隔离级别
③ 可重复读（REPEATABLE READ）
  • 在可重复读隔离级别下，事务A只能在事务B修改过数据并提交后，自己(A)也提交事务后，才能读取到事务B修改的数据。
  • 可重复读隔离级别解决了脏读和不可重复读的问题，但可能发生幻读问题。
  • MySQL的默认隔离级别

​ 提问：为什么上了写锁（写操作），别的事务还可以读操作？
​ 答： 因为InnoDB有MVCC机制（多版本并发控制），可以使用快照读，而不会被阻塞。

④ 可串行化（SERIALIZABLE）
  • 各种问题（脏读、不可重复读、幻读）都不会发生，通过加锁实现（读锁和写锁）。
脏读 不可重复读 幻读
读未提交 可能 可能 可能
读已提交 不会 可能 可能
可重复读 不会 不会 可能
可串行化 不会 不会 不会
3、查询和设置隔离级别

  1. 查询当前会话隔离级别（2种方式）

    mysql> SHOW VARIABLES LIKE 'tx_isolation';
mysql> SELECT @@tx_isolation;

  2. 设置隔离级别

    设置隔离级别后要重新登陆MySQL才生效

    SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL level;
-- 其中level有4种值：
level: {
     REPEATABLE READ
   | READ COMMITTED
   | READ UNCOMMITTED
   | SERIALIZABLE
}
    关键词：GLOBAL
    SET GLOBAL TRANSACTION ISOLATION LEVEL level;
-- 只对执行完该语句之后产生的会话起作用
-- 当前已经存在的会话无效
    关键词：SESSION
    SET SESSION TRANSACTION ISOLATION LEVEL level;
-- 对当前会话的所有后续的事务有效
-- 该语句可以在已经开启的事务中间执行，但不会影响当前正在执行的事务
-- 如果在事务之间执行，则对后续的事务有效。
    无关键词
    SET TRANSACTION ISOLATION LEVEL level;
-- 只对当前会话中下一个即将开启的事务有效
-- 下一个事务执行完后，后续事务将恢复到之前的隔离级别
-- 该语句不能在已经开启的事务中间执行，会报错的

2.7、JDBC

Java DataBase Connectivity Java数据库连接

1、简介

JDBC本质：

其实是官方（sun公司）定义的一套操作所有关系型数据库的规则，即接口。各个数据库厂商去实现这套接口，提供数据库驱动jar包。我们可以使用这套接口（JDBC）编程，真正执行的代码是驱动jar包中的实现类。

举例：

  • 官方接口：Person接口

  • MySQL实现类：Worker类

  • 用户使用接口：

    Person p = new Worker(); 
p.eat();          //真正调用的是 实现类Worker 里面的方法，多态

2、执行步骤

  1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar
    1. 复制mysql-connector-java-5.1.37-bin.jar到项目的libs目录下
    2. 右键-->Add As Library
  2. 注册驱动
  3. 获取数据库连接对象 Connection
  4. 定义sql
  5. 获取执行sql语句的对象 Statement
  6. 执行sql，接受返回结果
  7. 处理结果
  8. 释放资源
public class JdbcDemo1 {
    public static void main(String[] args) throws Exception {
        //1、注册驱动
        Class.forName("com.mysql.jdbc.Driver");
        
        //2、获取数据库连接对象
        Connection connection = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/db_test", "root", "whyzazn");
        
        //3、定义sql语句
        String sql = "update my_stu set class_id = 1 where id = 2019210239";
        
        //4、获取执行sql语句的对象  Statement
        Statement statement = connection.createStatement();
        
        //5、执行sql
        int count = statement.executeUpdate(sql);
        
        //6、处理结果
        System.out.println(count);
        
        //7、释放资源,先开启，后释放原则
        statement.close();
        connection.close();
    }
}

这其实是一份不太规范的代码，可以进行如下细节优化

public class JdbcDemo2 {
    public static void main(String[] args) {
        //提升作用域，不然释放资源的时候finally块中无法使用try块的变量
        Statement statement = null;
        Connection connection = null;
        try {
            //1、注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2、定义sql
            String sql = "update my_stu set class_id = 7 where id = 2019210239";
            //3、获取Connection对象
            connection = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/db_test", "root", "whyzazn");
            //4、获取执行sql的对象
            statement = connection.createStatement();
            //5、执行sql
            int lines = statement.executeUpdate(sql);   //影响的行数
            //6、处理结果
            System.out.println("影响的行数为：" + lines);
            if (lines > 0){
                System.out.println("更新数据成功！");
            } else {
                System.out.println("更新数据失败！");
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7、释放资源
            //避免空指针异常
            if (statement != null){
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

3、各个对象功能详解

1、DriverManager

驱动管理对象

① 注册驱动

作用：告诉程序该使用哪一个数据库驱动jar

//1、注册驱动
Class.forName("com.mysql.jdbc.Driver");   
//类加载会执行静态代码块中的注册驱动方法
static void registerDriver(Driver driver)   //注册与给定的驱动程序 DriverManager 。

通过查看源码发现：在com.mysql.jdbc.Driver类中存在静态代码块，随着类加载自动执行。

static {
    try {
        java.sql.DriverManager.registerDriver(new Driver());    //真正注册驱动的代码
    } catch (SQLException E) {
        throw new RuntimeException("Can't register driver!");
    }
}

注意：MySQL 5之后的驱动jar包可以省略注册驱动的步骤。

在这个文件中：libs\mysql-connector-java-5.1.37-bin.jar\META-INF\services\java.sql.Driver

com.mysql.jdbc.Driver  //如果没有显示注册驱动（），这句话会帮你注册驱动     
com.mysql.fabric.jdbc.FabricMySQLDriver
② 获取数据库连接

方法

//2、获取数据库连接对象
Connection connection = DriverManager.getConnection(String url, String user, String password);
//DriverManager中的静态方法
static Connection getConnection(String url, String user, String password)

参数

  • url：指定连接的路径

    • 语法：jdbc:mysql://ip地址(域名):端口号/数据库名称

    • 例子：jdbc:mysql://localhost:3306/db3

    • 细节：如果连接的是本机mysql服务器，并且mysql服务默认端口是3306，
      则url可以简写为：jdbc:mysql:///数据库名称，即ip:端口可以省略

  • user：用户名

  • password：密码

2、Connection

数据库连接对象

① 获取执行sql的对象
  • Statement createStatement()
  • PreparedStatement prepareStatement(String sql)
② 管理事务
  • 开启事务：setAutoCommit(boolean autoCommit) 调用该方法设置参数为false，即开启事务
  • 提交事务：commit()
  • 回滚事务：rollback()
3、Statement

执行sql的对象, 但是存在SQL注入风险， 实际一般使用 PreparedStatement

执行SQL的方法有：

  • boolean execute(String sql) ：可以执行任意的sql 了解，很少用
  • int executeUpdate(String sql)
    • 执行DML（insert、update、delete）语句、DDL(create，alter、drop)语句。
    • 返回值：影响的行数
      • 如果执行DML语句：返回值 > 0的则执行成功；反之，则失败。
      • 如果执行DDL语句：返回值则是0。（很少用）
  • ResultSet executeQuery(String sql) ：执行DQL（select)语句
4、ResultSet

结果集对象,封装查询结果

id name balance
1 Smith 3000
2 Jack 5000
3 Rose 4000

取得结果集对象时，指针指向结果集的第一行（表头）

//执行sql语句，DQL
ResultSet resultSet = statement.executeQuery(sql);
//循环遍历结果集
while(resultSet.next()){
    //获取数据，每次只能获取一个字段
    int id = resultSet.getInt(1);      //获取第一列的字段
    String name = resultSet.getString("name");  //获取列名为name的字段
    double balance = resultSet.getDouble(3);   

    System.out.println(id + "---" + name + "---" + balance);
}
//释放资源的顺序，先开启，后释放原则，即resultSet.close() --> statement.close() --> connection.close()

使用步骤：
1. 游标向下移动一行
2. 判断是否有数据
3. 获取数据

相关方法：

  1. boolean next(): 游标尝试向下移动一行，如果成功，则返回true，如果不成功(已是最后一行)，则返回false.

  2. getXxx(参数): 获取数据

    • Xxx：代表数据类型 如： int getInt() , String getString()

      • 参数：

        1. int：代表列的编号, 从1开始，获取第几列的字段 如： getString(1)
        2. String：代表列名称。 如： getDouble("balance")

作业：定义一个方法，查询db_test库中my_stu表的数据将其封装为对象，然后装载集合，返回。

/**
 * 封装my_stu表数据的JavaBean,必须有无参构造函数
 */
public class Stu {
    private int id;
    private String name;
    private int class_id;
    
    //无参构造函数、Getter、Setter、toString()
}

/**
 * 定义一个方法，查询db_test库中my_stu表的数据将其封装为对象，然后装载集合，返回。
 */
public class JdbcDemo3 {
    public static void main(String[] args) {
        List<Stu> list = new JdbcDemo3().findAll();
        System.out.println(list);
    }
    
    /**
     * 查询所有stu对象的方法
     * @return
     */
    public List<Stu> findAll(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Stu> stuList = null;

        try {
            //1 注册驱动
            Class.forName("com.mysql.jdbc.Driver");
            //2 获取connection对象
            connection = DriverManager.getConnection(
                    "jdbc:mysql:///db_test","root","whyzazn");
            //3 获取statement对象
            statement = connection.createStatement();
            //4 定义sql语句
            String sql = "select * from my_stu";
            //5 执行sql语句，DQL
            resultSet = statement.executeQuery(sql);
            //6 遍历结果集，封装为对象
            Stu stu = null;//把引用写在循环外面，避免重复创建引用
            stuList = new ArrayList<>();
            while(resultSet.next()){
                //List中装的是对象，每一次都要创建一个新对象加入
                stu = new Stu();
                stu.setId(resultSet.getInt("id"));
                stu.setName(resultSet.getString("name"));
                stu.setClass_id(resultSet.getInt(3));
                //将封装对象加入集合
                stuList.add(stu);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7 释放资源
            if (resultSet != null){
                try {
                    resultSet.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (connection != null){
                try {
                    connection.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
        return stuList;
    }
}
5、PreparedStatement

执行SQL的对象，其中的SQL是预编译的，参数使用 ? 作为占位符

① Statement对象存在的SQL注入问题

在拼接sql时，有一些sql的特殊关键字参与字符串的拼接。会造成安全性问题

例如：用户输入账号密码时，用户名随便输入，密码：a' or 'a' = 'a

当执行用户名密码判断时:

select * from user where username = 'fhdsjkf' and password = 'a' or 'a' = 'a' 
-- false && false || true = true , 形成了万能密码
② 解决SQL注入问题

使用PreparedStatement对象

步骤：

  1. 导入驱动jar包 mysql-connector-java-5.1.37-bin.jar

  2. 注册驱动

  3. 获取数据库连接对象 connection

  4. 定义sql

    注意：sql的参数使用？作为占位符。

    select * from user where username = ? and password = ?;

  5. 获取执行sql语句的对象

    PreparedStatement preparedStatement = connection.prepareStatement(String sql); //需要传sql

  6. 给？赋值

    方法： setXxx(index, value)

    • index：？的位置，编号 从1 开始
    • value：？的值
    preparedStatement.setString(1,username);  //给第一个 ？ 赋值
preparedStatement.setString(2,password);  //给第二个 ？ 赋值

  7. 执行sql，接受返回结果，不需要传递sql语句

    ResultSet resultSet = preparedStatement.executeQuery();

  8. 处理结果

  9. 释放资源

注意：后期都会使用PreparedStatement来完成增删改查的所有操作

	1. 可以防止SQL注入
	2. 效率更高

2.8、JDBCUtils

抽取JDBC工具类，目的是简化书写

1、分析

  1. 静态代码块中，注册驱动和读取配置文件，只需加载一次

  2. 抽取获取连接对象Connection的方法

    • 需求：不想传递参数（麻烦），还得保证工具类的通用性。
    • 解决：配置文件
      jdbc.properties
      url=
      user=
      password=

  3. 抽取一个方法释放资源

2、代码实现

1、编写工具类
public class JDBCUtils {
    private static String url;
    private static String user;
    private static String password;
    private static String driver;
    /**
     * 文件的读取，只需要读取一次即可拿到这些值。使用静态代码块
     */
    static {

        try {
            //1. 创建Properties集合类。
            Properties pro = new Properties();

            //获取src路径下的文件的方式--->ClassLoader 类加载器
            ClassLoader classLoader = JDBCUtils.class.getClassLoader();
            URL resource = classLoader.getResource("jdbc.properties");
            String path = resource.getPath(); //得到绝对路径

            //2. 加载文件
            pro.load(new FileReader(path));

            //3. 获取数据，赋值
            url = pro.getProperty("url");
            user = pro.getProperty("user");
            password = pro.getProperty("password");
            driver = pro.getProperty("driver");

            //4. 注册驱动
            Class.forName(driver);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取连接,通过配置文件获取参数
     * @return 连接对象
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url,user,password);
    }

    /**
     * 释放资源
     * @param statement
     * @param connection
     */
    public static void close(Statement statement, Connection connection){
        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 释放资源
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
2、使用工具类
public class JdbcDemo4 {
    public static void main(String[] args) {
        List<Stu> list = new JdbcDemo4().findAll();
        System.out.println(list);
    }
    /**
     * 演示JDBCUtils
     * @return
     */
    public List<Stu> findAll(){
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Stu> stuList = null;

        try {
            //1 注册驱动
            //2 获取connection对象
            connection = JDBCUtils.getConnection();
            //3 获取statement对象
            statement = connection.createStatement();
            //4 定义sql语句
            String sql = "select * from my_stu";
            //5 执行sql语句，DQL
            resultSet = statement.executeQuery(sql);
            //6 遍历结果集，封装为对象
            Stu stu = null;
            stuList = new ArrayList<>();
            while(resultSet.next()){
                //List中装的是对象，每一次都要创建一个新对象加入
                stu = new Stu();
                stu.setId(resultSet.getInt("id"));
                stu.setName(resultSet.getString("name"));
                stu.setClass_id(resultSet.getInt(3));
                //将封装对象加入集合
                stuList.add(stu);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //7 释放资源
			JDBCUtils.close(resultSet,statement,connection);
        }
        return stuList;
    }
}

3、练习

需求：

  1. 通过键盘录入用户名和密码

  2. 判断用户是否登录成功, 防止sql注入（PreparedStatement）

    • select * from user where username = ? and password = ?;
    • 如果这个sql有查询结果，则成功；反之，则失败

步骤：

  1. 创建数据库表 user

    CREATE TABLE `user`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`username` VARCHAR(20) NOT NULL,
	`password` VARCHAR(32) NOT NULL
	);

INSERT INTO `user` VALUES(NULL,'张三','zhangsan');
INSERT INTO `user` VALUES(NULL,'李四','lisi');

  2. 代码实现

    public class JdbcDemo6 {
    public static void main(String[] args) {
        //1、键盘录入，接收用户名和密码
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入用户名：");
        String username = scanner.nextLine();
        System.out.println("请输入密码：");
        String password = scanner.nextLine();
        //2、验证账号合法性
        boolean flag = new JdbcDemo6().check(username, password);
        if (flag){
            System.out.println("登陆成功");
        } else {
            System.out.println("登陆失败");
        }

    }

    /**
     * 验证用户名和密码的方法
     * @param username
     * @param password
     * @return
     */
    public boolean check(String username, String password){
        if (username == null || password == null){
            return false;
        }
        //连接数据库判断是否登陆成功
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            //获取connection对象
            connection = JDBCUtils.getConnection();
            String sql = "select * from user where username = ? and password = ?";
            //获取preparedStatement对象
            preparedStatement = connection.prepareStatement(sql);
            //给sql的 ? 赋值
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            //获取查询结果
            resultSet = preparedStatement.executeQuery();
            return resultSet.next();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //关闭连接
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return false;
    }
}

2.9、JDBC控制事务

  1. 事务：一个包含多个步骤的业务操作。如果这个业务操作被事务管理，则这多个步骤要么同时成功，要么同时失败。
  2. 操作：
    1. 开启事务
    2. 提交事务
    3. 回滚事务
  3. 使用Connection对象来管理事务
    • 开启事务：setAutoCommit(boolean autoCommit) ：调用该方法设置参数为false，即开启事务
      • 在执行sql之前开启事务
    • 提交事务：commit()
      • 当所有sql都执行完提交事务
    • 回滚事务：rollback()
      • 在catch中回滚事务
  4. 代码实现
public class JdbcDemo7 {
    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement preparedStatement1 = null;
        PreparedStatement preparedStatement2 = null;

        try {
            //1.获取连接
            connection = JDBCUtils.getConnection();
            //开启事务
            connection.setAutoCommit(false);

            //2.定义sql
            //2.1 张三 - 500
            String sql1 = "update bank set balance = balance - ? where id = ?";
            //2.2 李四 + 500
            String sql2 = "update bank set balance = balance + ? where id = ?";
            //3.获取执行sql对象
            preparedStatement1 = connection.prepareStatement(sql1);
            preparedStatement2 = connection.prepareStatement(sql2);
            //4. 设置参数
            preparedStatement1.setDouble(1, 500);
            preparedStatement1.setInt(2, 1);

            preparedStatement2.setDouble(1, 500);
            preparedStatement2.setInt(2, 2);
            //5.执行sql
            preparedStatement1.executeUpdate();  //张三-500
            preparedStatement2.executeUpdate();  //李四+500
            //提交事务
            connection.commit();
        } catch (Exception e) {
            //捕捉到异常后，进行事务回滚
            try {
                if (connection != null) {
                    connection.rollback();
                }
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JDBCUtils.close(preparedStatement1, connection);
            JDBCUtils.close(preparedStatement2, null);
        }
    }
}

2.10、数据库连接池

1、简介

JDBC传统模式开发存在的主要问题

  • 时间和内存资源消耗巨大

    普通的JDBC数据库连接使用DriverManager来获取，每次向数据库建立连接的时候都要将Connection加载到内存中，再根据JDBC代码（或配置文件）中的用户名和密码进行验证其正确性。这一过程一般会花费0.05~1s,一旦需要数据库连接的时候就必须向数据库请求一个，执行完后再断开连接。显然，如果同一个数据库在同一时间有数十人甚至上百人请求连接势必会占用大量的系统资源，严重的会导致服务器崩溃。

  • 有内存泄漏的风险

    因为每一次数据库连接使用完后都需要断开连接，但如果程序出现异常致使连接未能及时关闭，这样就可能导致内存泄漏，最终只能以重启数据库的方法来解决；另外使用传统JDBC模式开发不能控制需要创建的连接数，系统一般会将资源大量分出给连接以防止资源不够用，如果连接数超出一定数量也会有极大的可能导致内存泄漏。　　　　

数据库连接池概念
  • 其实就是一个容器(集合)，存放数据库连接的容器。
  • 当系统初始化好后，容器被创建，容器中会申请一些连接对象，当用户来访问数据库时，从容器中获取连接对象。
  • 当请求连接超过最大连接数时，会将多余的请求加入缓冲队列。
  • 用户访问完之后，会将连接对象归还给容器。
好处

  • 资源的高效利用

    由于数据库连接得以重用，避免了频繁创建，释放连接引起的大量性能开销，减小了系统资源消耗的同时也提高了系统运行环境的平稳性。

  • 更快的系统反应速度

    数据库连接池在初始化过程中，往往已经创建了若干数据库连接置于连接池中备用。此时连接的初始化工作均已完成。对于业务请求处理而言，直接利用现有可用连接可以避免数据库在连接初始化和释放过程所需的时间开销，从而减少了系统的响应时间，提高了系统的反应速度。

  • 减少了资源独占的风险

    新的资源分配手段对于多应用共享同一数据库的系统而言，可在应用层通过数据库连接池的配置实现对某一应用最大可用数据库连接数的限制，避免了应用独占所有数据库资源的风险。

  • 统一的连接管理，避免数据库连接泄露

    在实现较为完善的数据库连接池时，可根据预先的占用超时设定，强制回收被占用连接，从而避免了常规数据库连接操作中可能出现的资源泄露。

实现
  1. 标准接口：javax.sql包下的DataSource，该接口由驱动程序供应商去实现。
  2. 通过DataSource对象访问的驱动程序不会向DriverManager注册自己。 而是通过查找操作检索DataSource对象，然后用于创建Connection对象。 通过基本实现，通过DataSource对象获得的连接与通过DriverManager工具获得的连接相同。
    • 获取连接：getConnection()
    • 归还连接：Connection.close()。如果连接对象Connection是从连接池中获取的，那么调用Connection.close()方法，则不会再关闭连接了。而是归还连接
  3. 一般我们不去实现它，有数据库厂商来实现
    1. C3P0：数据库连接池技术
    2. Druid：数据库连接池实现技术，由阿里巴巴提供的

2、C3P0

步骤：

  1. 导入jar包 (两个)：

    • c3p0-0.9.5.2.jar
    • mchange-commons-java-0.2.12.jar

    不要忘记导入数据库驱动jar包

  2. 定义配置文件：

    • 指定名称： c3p0.properties 或者 c3p0-config.xml
    • 路径：直接将文件放在src目录下即可。

    满足以上两个条件之后，程序会自动读取配置文件。

  3. 创建核心对象 数据库连接池对象 ComboPooledDataSource

  4. 获取连接： getConnection

  5. 归还连接: connection.close() 并不会关闭连接

代码：

//1 创建数据库连接池对象,指定使用哪个配置,如果不传参数，则使用默认配置
    DataSource dataSource = new ComboPooledDataSource("xiao");
//2 获取连接对象
    Connection connection = dataSource.getConnection();
	...
//3 归还连接
    connection.close();

3、Druid德鲁伊

步骤：
1. 导入jar包 druid-1.0.9.jar
1. 定义配置文件：
* 是properties形式的
* 可以叫任意名称，可以放在任意目录下
3. 加载配置文件。Properties
4. 获取数据库连接池对象：通过工厂来来获取 DruidDataSourceFactory
5. 获取连接：getConnection
6. 归还连接: connection.close() 并不会关闭连接

代码：

//1 加载配置文件
    Properties properties = new Properties();
	//方法1：使用文件流
    properties.load(new FileInputStream("src//druid.properties"));
	//方法2：使用类加载器
	InputStream is = DruidDemo1.class.getClassLoader().getResourceAsStream("druid.properties");
    properties.load(is);
//2 获取连接池对象
	DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
//3 获取connection对象
	Connection connection = dataSource.getConnection();
	...
//4 归还连接
    connection.close();

4、Druid_JDBCUtils

定义工具类

	1. 定义一个类 JDBCUtils
	2. 提供静态代码块加载配置文件，初始化连接池对象
	1. 提供方法
1. 获取连接方法：通过数据库连接池获取连接
2. 释放资源
3. 获取连接池的方法
	4. 代码实现

public class Druid_JDBCUtils {
    // 定义成员变量DataSource
    private static DataSource dataSource;
    static {

        try {
            // 加载配置文件
            Properties properties = new Properties();
            properties.load(new FileInputStream("src//druid.properties"));
            // 获取DataSource对象
            dataSource = DruidDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 获取Connection对象
     * @return
     * @throws SQLException
     */
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    /***
     * 获取DataSource对象
     * @return
     */
    public static DataSource getDataSource(){
        return dataSource;
    }

    /**
     * 释放资源
     * @param statement
     * @param connection
     */
    public static void close(Statement statement,Connection connection){
        close(null,statement,connection);
    }

    /**
     * 释放资源
     * @param resultSet
     * @param statement
     * @param connection
     */
    public static void close(ResultSet resultSet, Statement statement, Connection connection){
        if (resultSet != null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (connection != connection){
            try {
                connection.close();//归还连接
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

2.11、Spring JDBC

JDBC已经能够满足大部分用户最基本的需求，但是在使用JDBC时，必须自己来管理数据库资源如：获取PreparedStatement，设置SQL语句参数，关闭连接等步骤。

JdbcTemplate是Spring对JDBC的封装，目的是使JDBC更加易于使用。JdbcTemplate是Spring的一部分。JdbcTemplate处理了资源的建立和释放。他帮助我们避免一些常见的错误，比如忘了总要关闭连接。他运行核心的JDBC工作流，如Statement的建立和执行，而我们只需要提供SQL语句和提取结果。

使用JdbcTemplate步骤：

  1. 导入jar包(5个)

    commons-logging-1.2.jar
spring-beans-5.0.0.RELEASE.jar
spring-core-5.0.0.RELEASE.jar
spring-jdbc-5.0.0.RELEASE.jar
spring-tx-5.0.0.RELEASE.jar

  2. 创建JdbcTemplate对象。依赖于数据源DataSource

    JdbcTemplate template = new JdbcTemplate(ds);

  3. 调用JdbcTemplate的方法来完成CRUD的操作

    • update():执行DML语句。增、删、改语句

      • jdbc默认返回的是匹配的行数，就算修改为原先一样的数据，也会返回1；
      • MySQL返回的是影响的行数，修改为原先一样的数据，返回值为0。
      • queryForMap():查询结果将结果集封装为map集合，将列名作为key，将值作为value 将这条记录封装为一个map集合
      • 注意：这个方法查询的结果集长度只能是1
      • queryForList():查询结果将结果集封装为list集合
      • 注意：将每一条记录封装为一个Map集合，再将Map集合装载到List集合中
      • query():查询结果，将结果封装为JavaBean对象

      query的参数：RowMapper

      • 一般我们使用BeanPropertyRowMapper实现类。可以完成数据到JavaBean的自动封装

      • new BeanPropertyRowMapper<类型>(类型.class)

      • queryForObject()：查询结果，将结果封装为对象

      • 一般用于聚合函数的查询

  4. 练习

public class JdbcTemplateDemo1 {

    //1 获取JdbcTemplate对象
    private JdbcTemplate template = new JdbcTemplate(Druid_JDBCUtils.getDataSource());
    //2 调用方法

    //需求：
    //1. 修改1号数据的 salary 为 10000
    @Test
    public void test1(){
        String sql1 = "update user set password = ? where id = ?";
        int cnt = template.update(sql1,"ZHANGSAN",1);
        System.out.println(cnt);
    }

    //2. 添加一条记录
    @Test
    public void test2(){
        String sql2 = "insert into user values(?,?,?)";
        int cnt = template.update(sql2,3,"王五","wangwu");
        System.out.println(cnt);
    }

    //3. 删除刚才添加的记录
    @Test
    public void test3(){
        String sql3 = "delete from user where id = ?";
        int cnt = template.update(sql3,3);
        System.out.println(cnt);
    }

    //4. 查询id为1的记录，将其封装为Map集合
    @Test
    public void test4(){
        String sql4 = "select * from user where id = ?";
        Map<String, Object> map = template.queryForMap(sql4, 1);
        System.out.println(map);
    }

    //5. 查询所有记录，将其封装为List
    @Test
    public void test5(){
        String sql5 = "select * from user";
        List<Map<String, Object>> maps = template.queryForList(sql5);
        for (Map<String, Object> map : maps) {
            System.out.println(map);
        }
    }

    //6. 查询所有记录，将其封装为User对象的List集合
    //方法1：自己写实现类
    @Test
    public void test6_1(){
        String sql6 = "select * from user";
        //匿名内部类
        List<User> list = template.query(sql6, new RowMapper<User>() {

            @Override
            public User mapRow(ResultSet resultSet, int i) throws SQLException {
                User user = new User();
                Integer id = resultSet.getInt(1);
                String name = resultSet.getString(2);
                String password = resultSet.getString(3);
                user.setId(id);
                user.setName(name);
                user.setPassword(password);
                return user;
            }
        });

        for (User user : list) {
            System.out.println(user);
        }
    }

    //方法2：调用现成的实现类，传入泛型和字节码文件类型
    @Test
    public void test6_2(){
        String sql6 = "select * from user";
        List<User> list = template.query(sql6, new BeanPropertyRowMapper<User>(User.class));
        for (User user : list) {
            System.out.println(user);
        }
    }

    //7. 查询总记录数
    @Test
    public void test7(){
        String sql7 = "select count(id) from user";
        Integer total = template.queryForObject(sql7, Integer.class);
        System.out.println(total);
    }

}
posted @   猫的心情  阅读(109)  评论(0)  编辑  收藏  举报
相关博文：
· JavaWeb学习笔记
· Redis学习(详细版)
· Mysql笔记
· MySQL基础
· MySQL基础
阅读排行：
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· 没有Manus邀请码？试试免邀请码的MGX或者开源的OpenManus吧
· 园子的第一款AI主题卫衣上架——"HELLO! HOW CAN I ASSIST YOU TODAY
· 【自荐】一款简洁、开源的在线白板工具 Drawnix
点击右上角即可分享
微信分享提示