Mysql学习

叁次

已于 2024-06-12 22:27:52 修改

阅读量624

点赞数 15

文章标签： mysql 学习数据库

于 2024-06-03 15:20:13 首次发布

本文链接：https://blog.csdn.net/qq_41488067/article/details/130718490

版权

初识MySQL

前端（页面：展示，数据）；后台（连接点：连接数据库JDBC，链接前端（控制，控制试图跳转，和给前端传递数据））；数据库（存数据，TXT，Excel，Word），数据库（DB,Database）是按照数据结构来组织、存储和管理数据的仓库。

数据库管理系统（DBMS）分为关系型和非关系型。MySQL就是关系型数据库管理系统（RDBMS），所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。

MySQL 是开源的，目前隶属于 Oracle 旗下产品。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。

RDBMS的一些术语：：

数据库: 数据库是一些关联表的集合。
数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。值(value): 行的具体信息, 每个值必须与该列的数据类型相同。
冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
外键：外键用于关联两个表。
复合键：复合键（组合键）将多个列组成一个索引键，一般用于复合索引
键：键的值在当前列中具有唯一性
索引：使用索引可快速访问数据表中的特定信息，索引是数据库中由一列或多列的值进行排序的结构。相当于书籍的目录。
10.参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

关系型数据库管理系统的特点：

数据以表格的形式出现
每行为各种记录名称
每列为记录名称所对应的数据域
许多的行和列组成一张表单
若干的表单组成database

连接和管理数据库：

打开命令行窗口，输入mysql -u root -p，再输入密码即可连接数据库；
以上命令执行后，登录成功输出结果如下:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 8.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

在登录成功后会出现 mysql> 命令提示窗口，你可以在上面执行任何 SQL 语句。所有的语句以‘;’结尾
退出 mysql> 命令提示窗口可以使用 exit 命令，如下所示：mysql> exit； Bye
查看所有的数据库：show databases；
切换数据库 use 数据库名
查看数据库中所有的表：show tables；
显示数据库中所有的表的信息：describe 表名
创建一个数据库：create database 数据库名；
单行注释：--；多行注释：/**/

操作数据库

#创建数据库
mysql> CREATE DATABASE IF NOT EXISTS westos;
Query OK, 1 row affected

#删除数据库
mysql> DROP DATABASE IF EXISTS westos;
Query OK, 0 rows affected

#使用数据库,``在tab键上面，如果你的表名或者字段是一个特殊字符，就需要带``
mysql> USE `school`;
Database changed
# 查看数据库
SHOW DATABASES --查看所有数据库
mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| school             |
| sql_study          |
| sys                |
| sys_authentication |
| test               |
| world              |
+--------------------+
10 rows in set
#查询表单
mysql> SELECT `name` FROM student;
+------+
| name |
+------+
| yss  |
+------+
1 row in set

数据库的列类型

MySQL 支持所有标准 SQL 数值数据类型。这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC)，以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。作为 SQL 标准的扩展，MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。

数值
- tinyint：十分小的整数数据、1个字节
- smallint：较小的整数数据、2个字节
- mediumint：中等大小的整数数据、3个字节
- int：大的整数数据、4个字节
- BIGINT：极大的整数数据、8个字节
- float：单精度浮点数、4个字节
- double：双精度浮点数、8个字节
- decimal：字符串形式的浮点数、金融计算
字符串
- char：字符串固定大小的 0~255
- varchar：可变字符串、0~65535、常用的变量
- tinychar：微变文本、2^8-1
- text：文本串、2^16-1、保存大文本
时间日期（java.util.Date）
- date：YYYY-MM-DD，日期
- time： HH:mm:ss，时间格式
- datetime：YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp：时间戳，1970.1.1到现在的毫秒数。
- year：年份表示
null
- 没有值，未知
- 注意，不要使用NULL进行运算，结果为null。

数据库的字段属性（重点）

每个表，都必须存在以下五个字段：
id 主键；version乐观锁；is_delete伪删除；gmt_create创建时间；gmt_update修改时间

Unsigned：

无符号的整数
声明了该列不能声明为负数

zerofill：

0填充的
不足的位数，使用0来填充

自增：

通常理解为自增，自动在上一条记录的基础上+1（默认）
通常用来设计唯一的主键~index，必须是整数类型
可以自定义设计主键自增的起始值和步长

非空：

假设设置为not null，如果不给它赋值，就会报错
NULL，如果不填写值，默认就是null

默认：

设置默认的值
sex，默认为男。

创建数据库表

MySQL 创建数据表

创建MySQL数据表需要以下信息：
表名
表字段名
定义每个表字段

#注意：表字段名名使用``,默认、备注等使用''。
#下面实例是创建一个student表，包括id、name、pwd、sex、birthday、address、email七列。
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名', 
    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
    `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
    `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL，在操作数据库时如果输入该字段的数据为NULL ，就会报错。
AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。
ENGINE 设置存储引擎，CHARSET 设置编码。

通过命令提示符创建表

mysql> CREATE TABLE runoob_tbl(
    -> runoob_id INT NOT NULL AUTO_INCREMENT,
    -> runoob_title VARCHAR(100) NOT NULL,
    -> runoob_author VARCHAR(40) NOT NULL,
    -> submission_date DATE,
    -> PRIMARY KEY ( runoob_id )
    ->  )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected

mysql>

注意：-> 是换行符标识，MySQL命令终止符为分号 ; 。

常用命令

#查看创建数据库的语句
mysql> SHOW CREATE DATABASE school;
+----------+---------------------+
| Database | Create Database                                                                                    |
+----------+---------------------+
| school   | CREATE DATABASE `school` 
+----------+---------------------+
1 row in set (0.04 sec)

#查看student数据表的定义语句
mysql> SHOW CREATE TABLE student;
+---------+------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |
+---------+------------------+
| student | CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` varchar(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `birthday` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(100) DEFAULT NULL COMMENT '家庭住址',
  `email` varchar(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+------------------------------+
1 row in set (0.07 sec)

#显示表的结构
mysql> desc student;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(4)       | NO   | PRI | NULL    | auto_increment |
| name     | varchar(30)  | NO   |     | 匿名    |                |
| pwd      | varchar(20)  | NO   |     | 123456  |                |
| sex      | varchar(2)   | NO   |     | 女      |                |
| birthday | datetime     | YES  |     | NULL    |                |
| address  | varchar(100) | YES  |     | NULL    |                |
| email    | varchar(50)  | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
7 rows in set (0.03 sec)

数据表的类型，存储引擎INNODB和MYISAM

INNODB：默认使用；事务支持、数据行锁定、外键约束、不支持全文索引、不支持全文索引、表空间较大，约为myisam的2倍。（安全性高、事务的处理、多表多用户操作）
MYISAM：早些年使用；无事务支持、不支持数据行锁定、无外键约束、支持全文索引、表空间较小。（节约空间、速度较快）
存储位置：所有的数据库文件都存储在data目录下；MySQL引擎在物理文件上的区别：
- INNODB 在数据库表中只有一个*.frm文件，以及上级目录下的ibdata文件
- MYISAM的对应文件有：.frm表结构的定义文件、.MYD数据文件（data）、.MYI索引文件（index）

设置数据库表的字符集编码

CHARSET = utf-8；
#不设置的话，MySQL默认编码为Latin1，不支持中文
#也可以在my.ini中配置默认的编码
character-set-server = utf8;

修改和删除数据库

修改

#修改表名：ALTER TABLE 旧表名 RENAME AS 新表名
mysql> ALTER TABLE student RENAME AS student1;
Query OK, 0 rows affected (0.02 sec)

#增加表的字段：ALTER TABLE 表名 ADD 字段名 列属性
mysql> ALTER TABLE student1 ADD age INT(11);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

#修改表的字段约束：ALTER TABLE 表名 MODIFY 字段名 列属性
mysql> ALTER TABLE student1 MODIFY age VARCHAR(11);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#字段重命名：ALTER TABLE 表名 CHANGE 字段名 列属性（与原来相同）
mysql> ALTER TABLE student1 CHANGE age1 INT(1);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#删除表的字段：ALTER TABLE 表名 DROP 字段名 
mysql> ALTER TABLE student1 DROP age1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除

#删除表
mysql>DROP TABLE IF EXISTS teacher1;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

#所有的创建和删除操作尽量加上判断，以免报错。
#字段名用``包裹，SQL关键字不敏感。
#注释--/**/
#SQL关键字大小写不敏感，建议小写

MySQL数据管理

外键(了解)

外键：外键用于关联两个表。
删除有外键关系的表，必须先删除引用别人的表（从表），再删除被引用的表（主表）

在这里插入图片描述
方式一：

方式二：创建表成功后，再添加外键约束

以上操作都是物理外键，数据库级别的外键，不建议使用！（避免数据库过多造成困扰），想使用外键，最好是用程序实现。

DML语言

数据库意义：数据存储，数据管理
DML语言：数据操作语言

添加

#插入语句（添加）
#insert into 表名（[字段1，字段2，字段3]）values（'值1'，'值2','值3'，....）
insert into `grade`(`gradename`)values('大四')

#由于主键自增，可以省略；一般写插入语句，一定要数据和字段一一对应
insert into `grade`values('大四')
#插入多个字段
insert into `grade`(`gradename`)values('大一')('大四')

以下为向MySQL数据表插入数据通用的 INSERT INTO SQL语法：

INSERT INTO table_name ( field1, field2,...fieldN )
                       VALUES
                       ( value1, value2,...valueN );

注意：字段和字段之间用英文逗号隔开；字段是可以省略的，但是后面的值必须要一一对应；可以插入多条数据，values后面的值，需要使用逗号隔开即可

修改

以下是 UPDATE 命令修改 MySQL 数据表数据的通用 SQL 语法：

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

#实例
mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3;
Query OK, 1 rows affected (0.01 sec)

注意：你可以同时更新一个或多个字段。你可以在 WHERE 子句中指定任何条件。你可以在一个单独表中同时更新数据。
当你需要更新数据表中指定行的数据时 WHERE 子句是非常有用的。

删除

以下是 SQL DELETE 语句从 MySQL 数据表中删除数据的通用语法：

#删除指定数据
DELETE FROM table_name [WHERE Clause]

#实例：
#删除 id 为 3 的行: 
delete from students where id=3;
#删除所有年龄小于 21 岁的数据: 
delete from students where age<20;

注意：如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。你可以在 WHERE 子句中指定任何条件
您可以在单个表中一次性删除记录。
delete，drop，truncate 都有删除表的作用，区别在于：
1、delete 和 truncate 仅仅删除表数据，drop 连表数据和表结构一起删除，打个比方，delete是单杀，truncate 是团灭，drop 是把电脑摔了。 truncate 重新设置自增列，不会影响设置。
2、delete 是 DML语句，操作完以后如果没有不想提交事务还可以回滚，truncate 和 drop 是 DDL语句，操作完马上生效，不能回滚，打个比方，delete 是发微信说分手，后悔还可以撤回，truncate 和 drop是直接扇耳光说滚，不能反悔。
3、执行的速度上，drop>truncate>delete，打个比方，drop 是神舟火箭，truncate 是和谐号动车，delete 是自行车。

DQL查询数据（最重点）

DQL

所有的查询操作都用它‘Select’
简单的查询，复杂的查询它都能做·
数据库中最核心的语言，最重要的语句
使用频率最高的语句

指定查询字段

以下为在MySQL数据库中查询数据通用的 SELECT 语法：

-- 数据库里的表达式：文本值，列，NULL，函数，计算表达式，变量。。
SELECT column_name,column_name
FROM table_name
[WHERE Clause1]
[GROUP BY REASON]  --指定结果按照哪几个字段来分组
[HAVING Clause2]   --过滤分组的记录必须满足的次要条件
[LIMIT N][ OFFSET M]

-- 选择所有列的所有行
SELECT * FROM users;
-- 选择特定列的所有行
SELECT username, email FROM users;
-- 添加 WHERE 子句，选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;
-- 添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;
-- 添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;
-- 添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;

#别名，给结果起一个名字 AS; 可以给字段起别名，也可以给表起别名
SELECT `studentNo`AS 学号,`studentname`AS 学生姓名 from student_tbl AS s;

#合并函数concat(a，b)
#参数说明：A,B：需要被合并的两个参数，可以为文本字符串，也可以为数字，亦可以是字符，还可以是两个字段。
#用法：用于将两个字符串合并为目标字符串，构建新的一列。
SELECT CONCAT('姓名:',studentname) AS 新名字 FROM student

where条件字句

作用：检索数据中符合条件的值
搜索的条件由一个或多个表达式组成结果为布尔值。

逻辑运算符

|  运算符    |       语法          |   描述      |
| and   &&   | a and b    a&&b     |  逻辑与     |
| or    ||   | a or b     a||b     |  逻辑或     |
| not    !   | not a      !a       |  逻辑非     |
-- 尽量使用英文字母

-- 以下是 SQL SELECT 语句使用 WHERE 子句从数据表中读取数据的通用语法：
SELECT column1, column2, ...
FROM table_name
WHERE condition;
-- column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
-- table_name 是你要从中查询数据的表的名称。
-- WHERE condition 是用于指定过滤条件的子句。

#实例：查询数据表 runoob_tbl:
select * from runoob_tbl;
#实例：读取 runoob_tbl 表中 runoob_author 字段值为 Sanjay 的所有记录：
SELECT * from runoob_tbl WHERE runoob_author='卷积';

#MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 你可以使用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。
-- 实例
mysql> SELECT * from runoob_tbl WHERE BINARY runoob_author='runoob.com';
Empty set (0.01 sec)

模糊查询：比较运算符

|  运算符    |       语法            |   描述                       |
| IS NULL    |     a is null        |  如果操作符为NULL，结果为真   |
| IS NULL    |   a is not null      |  如果操作符不为NULL，结果为真 |
| BETWEEN    |    a between b       |   若a在b和c之间，结果为真     |
|  LIKE      |      a like b        |  SQL匹配，如果a匹配b，结果为真|
|  IN        | a in (a1,a2,a3...)   |  如果a在a1，a2..其中的某一个值中，结果为真|
-- 尽量使用英文字母

WHERE 子句中可以使用等号 = 来设定获取数据的条件，如 “author = ‘ab.COM’”。
但是有时候我们需要获取 author 字段含有 “COM” 字符的所有记录，这时我们就需要在 WHERE 子句中使用 LIKE 子句。
LIKE 子句是在 MySQL 中用于在 WHERE 子句中进行模糊匹配的关键字。它通常与通配符一起使用，用于搜索符合某种模式的字符串。LIKE 子句中使用百分号 %字符来表示任意字符，类似于UNIX或正则表达式中的星号 *。如果没有使用百分号 %, LIKE 子句与等号 = 的效果是一样的。

-- 以下是 SQL SELECT 语句使用 LIKE 子句从数据表中读取数据的通用语法：
SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

#  % 通配符表示零个或多个字符。例如，'a%' 匹配以字母 'a' 开头的任何字符串。
SELECT * FROM customers WHERE last_name LIKE 'S%';
# _ 通配符表示一个字符。例如，'_r%' 匹配第二个字母为 'r' 的任何字符串。
SELECT * FROM products WHERE product_name LIKE '_a%';
-- 不区分大小写的匹配：
SELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;
#以上 SQL 语句将选择姓氏以 'smi' 开头的所有员工，不区分大小写。
#LIKE 子句提供了强大的模糊搜索能力，可以根据不同的模式和需求进行定制。

#要检查某列是否为 NULL，可以使用 IS NULL 或 IS NOT NULL 条件。
SELECT * FROM employees WHERE department_id IS NULL;
SELECT * FROM employees WHERE department_id IS NOT NULL;

#使用 COALESCE 函数处理 NULL；COALESCE 函数可以用于替换为 NULL 的值，它接受多个参数，返回参数列表中的第一个非 NULL 值：
SELECT product_name, COALESCE(stock_quantity, 0) AS actual_quantity
FROM products;
#以上 SQL 语句中，如果 stock_quantity 列为 NULL，则 COALESCE 将返回 0。

联表查询

在真正的应用中经常需要从多个数据表中读取数据。使用 MySQL 的 JOIN 在两个或多个表中查询数据。

JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

# INNER JOIN 返回两个表中满足连接条件的匹配行，以下是 INNER JOIN 语句的基本语法：
SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
#参数说明：
#column1, column2, ... 是你要选择的列的名称，如果使用 * 表示选择所有列。
#table1, table2 是要连接的两个表的名称。
#table1.column_name = table2.column_name 是连接条件，指定了两个表中用于匹配的列。

# 实例：
# 1.简单的 INNER JOIN：
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id;
# join on(条件判断)是连接查询，where是等值查询

# 2.使用表别名：
SELECT o.order_id, c.customer_name
FROM orders AS o
INNER JOIN customers AS c ON o.customer_id = c.customer_id;
# 3.多表 INNER JOIN：
SELECT orders.order_id, customers.customer_name, products.product_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.product_id;
# 4.使用 WHERE 子句进行过滤：
SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date >= '2023-01-01';

#LEFT JOIN 返回左表的所有行，并包括右表中匹配的行，如果右表中没有匹配的行，将返回 NULL 值，以下是 LEFT JOIN 语句的基本语法：
SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

# 1. 简单的 LEFT JOIN：
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;
#以上 SQL 语句将选择客户表中的客户 ID 和客户名称，并包括左表 customers 中的所有行，以及匹配的订单 ID（如果有的话）。
# 2. 使用表别名：
SELECT c.customer_id, c.customer_name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON c.customer_id = o.customer_id;
#以上 SQL 语句使用表别名 c 和 o 分别代替 customers 和 orders 表的名称。
# 3. 多表 LEFT JOIN：
SELECT customers.customer_id, customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
LEFT JOIN order_items ON orders.order_id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.product_id;
# 以上 SQL 语句连接了 customers、orders、order_items 和 products 四个表，并选择了客户 ID、客户名称、订单 ID 和产品名称。左连接保证了即使在 order_items 或 products 中没有匹配的行，仍然会返回客户和订单的信息。
# 4. 使用 WHERE 子句进行过滤：
SELECT customers.customer_id, customers.customer_name, orders.order_id
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
WHERE orders.order_date >= '2023-01-01' OR orders.order_id IS NULL;

#LEFT JOIN 是一种常用的连接类型，尤其在需要返回左表中所有行的情况下。当右表中没有匹配的行时，相关列将显示为 NULL。在使用 LEFT JOIN 时，请确保理解连接条件并根据需求过滤结果。

#RIGHT JOIN 返回右表的所有行，并包括左表中匹配的行，如果左表中没有匹配的行，将返回 NULL 值，以下是 RIGHT JOIN 语句的基本语法：：
SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

#以下是一个简单的 RIGHT JOIN 实例：
SELECT customers.customer_id, orders.order_id
FROM customers
RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
#以上 SQL 语句将选择右表 orders 中的所有订单 ID，并包括左表 customers 中匹配的客户 ID。如果在 customers 表中没有匹配的客户 ID，相关列将显示为 NULL。
# 在开发过程中中，RIGHT JOIN 并不经常使用，因为它可以用 LEFT JOIN 和表的顺序交换来实现相同的效果。例如，上面的查询可以通过使用 LEFT JOIN 改写为：
SELECT customers.customer_id, orders.order_id
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.customer_id;
#以上 SQL 语句返回相同的结果，因为 LEFT JOIN 与 RIGHT JOIN 是对称的。

自连接（了解）

自己的表和自己的表连接，核心：一张表拆为两个一样的表，分为父类和子类。

分页和排序

排序

如果我们需要对读取的数据进行排序，我们就可以使用 MySQL 的 ORDER BY 子句来设定你想按哪个字段哪种方式来进行排序，再返回搜索结果。
MySQL ORDER BY(排序) 语句可以按照一个或多个列的值进行升序（ASC）或降序（DESC）排序。
作用：缓解数据库压力，给人更好的体验。/还有一种瀑布流

--以下是 SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据：
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
--ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ... 是用于指定排序顺序的子句。ASC 表示升序（默认），DESC 表示降序。

分页

要检索查询返回的行的一部分，使用LIMIT和OFFSET子句。

--语法：
SELECT column_list
FROM table1
ORDER BY column_list
LIMIT row_count1，row_count2 OFFSET offset;
-- 在这个语法中，row_count1为起始页，默认为0，row_count2确定将返回的行数。OFFSET子句在开始返回行之前跳过偏移行。 OFFSET子句是可选的。 如果同时使用LIMIT和OFFSET子句，OFFSET会在LIMIT约束行数之前先跳过偏移行。
--在使用LIMIT子句时，使用ORDER BY子句确保返回的行按指定顺序非常重要。

子查询

where（值是固定的，计算出来的）
本质：在where语句中嵌套一个子查询

--查询 数据结构 的所有考试结果(学号，科学编号，成绩)，降序排列
# 方法一：
SELECT `Studentno`,`r.subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subjet` s
ON r.subjectno = s.subjectno
WHERE subjectname = '数据结构'
ORDER BY studentresult DESC

#方法二：使用子查询（由里及外）
SELECT `Studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE subjectname = (
         SELECT `subjectno` FROM `subject`
         WHERE subjectname = '数据结构'
         )

分组和过滤

分组是使用数据库时必须处理的最重要任务之一。要将行分组，请使用GROUP BY子句。 GROUP BY子句是SELECT语句的可选子句，它根据指定列中的匹配值将行组合成组，每组返回一行。经常将GROUP BY与MIN，MAX，AVG，SUM或COUNT等聚合函数结合使用，以计算为每个分组提供信息的度量。

以下是GROUP BY子句的语法：

SELECT
    column1,
    column2,
    AGGREGATE_FUNCTION (column3)
FROM
    table1
GROUP BY
    column1,
    column2;

要指定分组的条件，就要使用HAVING子句。 HAVING子句通常与SELECT语句中的GROUP BY子句一起使用。如果使用带GROUP BY子句的HAVING子句，HAVING子句的行为类似于WHERE子句。

以下是HAVING子句的语法：

SELECT
    column1,
    column2,
    AGGREGATE_FUNCTION (column3)
FROM
    table1
GROUP BY
    column1,
    column2
HAVING
    group_condition;
#请注意，HAVING子句紧跟在GROUP BY子句之后出现。
#HAVING与WHERE
--在通过GROUP BY子句将行汇总到分组之前，WHERE子句将条件应用于各个行。 但是，HAVING子句在将行分组到组之后将条件应用于组。因此，需要注意的是，在GROUP BY子句之前应用WHERE子句之后应用HAVING子句。

MySQL函数

常用函数

数字函数：

ABS(x) 返回x的绝对值
FLOOR(x) 向下取整，返回小于或等于 x 的最大整数。　例子：小于或等于 1.5 的整数：SELECT FLOOR(1.5) – 返回1
CEILING(x) 向下取整，返回大于或等于 x 的最小整数　例子：SELECT CEILING(1.5); – 返回2
GREATEST(expr1, expr2, expr3, …) 返回列表中的最大值。例子：返回以下数字列表中的最大值：SELECT GREATEST(3, 12, 34, 8, 25); – 34
LEAST(expr1, expr2, expr3, …) 返回列表中的最小值。例子：返回以下数字列表中的最小值：SELECT LEAST(3, 12, 34, 8, 25); – 3
MOD(x,y) 返回 x 除以 y 以后的余数　例子：5 除于 2 的余数：SELECT MOD(5,2) – 1
RAND() 返回 0 到 1 的随机数　　例子：SELECT RAND() --0.93099315644334
ROUND(x [,y]) 返回离 x 最近的整数，可选参数 y 表示要四舍五入的小数位数，如果省略，则返回整数。例子1：SELECT ROUND(1.23456) --1；例子2：SELECT ROUND(345.156, 2) – 345.16
SIGN(x) 返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1　例子：SELECT SIGN(-10) – (-1)

字符串函数：

CHAR_LENGTH(s) 返回字符串 s 的字符数例子：返回字符串 RUNOOB 的字符数 : SELECT CHAR_LENGTH(“RUNOOB”) AS LengthOfString;
CONCAT(s1,s2…sn) 字符串 s1,s2 等多个字符串合并为一个字符串例子：合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", “Facebook”) AS ConcatenatedString;
INSERT(s1,x,len,s2) 字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串。例子：从字符串第一个位置开始的 6 个字符替换为 runoob：SELECT INSERT(“google.com”, 1, 6, “runoob”); – 输出：runoob.com
LOWER(s) 将字符串 s 的所有字母变成小写字母。例子：字符串 RUNOOB 转换为小写：SELECT LOWER(‘RUNOOB’) – runoob
UPPER(s) 将字符串 s 的所有字母变成大写字母。例子：字符串 runoob 转换为小写：SELECT UPPER(‘runoob’) – RUNOOB
INSTR(s,y) 返回第一次在s中出现y的索引。
REPLACE(s,s1,s2) 将字符串 s2 替代字符串 s 中的字符串 s1。例子：将字符串 abc 中的字符 a 替换为字符 x：SELECT REPLACE(‘abc’,‘a’,‘x’) --xbc
REVERSE(s) 将字符串s的顺序反过来。例子：将字符串 abc 的顺序反过来：SELECT REVERSE(‘abc’) – cba
SUBSTR(s, start, length) 从字符串 s 的 start 位置截取长度为 length 的子字符串。例子：从字符串 RUNOOB 中的第 2 个位置截取 3个字符：SELECT SUBSTR(“RUNOOB”, 2, 3) AS ExtractString; – UNO

日期函数

DATE() 获取当前日期，或者从日期或日期时间表达式中提取日期值。例子：SELECT DATE(“2024-05-15”); -> 2024-05-15
DATEDIFF(d1,d2) 计算日期 d1->d2 之间相隔的天数。例子：SELECT DATEDIFF(‘2001-01-01’,‘2001-02-02’) -> -32
NOW() 返回当前日期和时间。例子：SELECT NOW() -> 2018-09-19 20:57:43
LOCALTIME() 返回当前本地日期和时间. 例子：SELECT LOCALTIME() -> 2024-05-15 20:29:43
SYSDATE() 返回系统当前日期和时间。例子：SELECT SYSDATE() -> 2024-05-15 20:39:43

系统函数

SYSTEM_USER() 系统用户
USER()用户
VERSION() 版本

聚合函数（常用）

函数名称	描述
COUNT()	计数
SUM()	求和
AVG()	平均值
MAX()	最大值
MIN()	最小值

count(*) 和 count(1)和count(列名)区别:

count()包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。
列名为主键，count(列名)会比count(1)快。列名不为主键，count(1)会比count(列名)快。
如果表多个列并且没有主键，则 count（1）的执行效率优于 count（）。
如果有主键，则 select count（主键）的执行效率是最优的。
如果表只有一个字段，则 select count（*）最优。

数据库级别的MD5加密（扩展）

什么是MD5
MD5信息摘要算法，一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。
主要增强算法复杂度和不可逆性。MD5不可逆，具体值的MD5是一样的。MD5算法因其普遍、稳定、快速的特点，仍广泛应用于普通数据的加密保护领域

-- =====================测试MD5加密=======================
-- MD5信息摘要算法
CREATE TABLE `testMD5`(
 `id` INT(4) NOT NULL,
 `name` VARCHAR(10) NOT NULL, 
 `pwd` VARCHAR(50) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB  DEFAULT CHARSET=utf8

-- 明文密码
INSERT INTO `testmd5` VALUES (1,'张三','123456'),(2,'旺角','123456'),(3,'李四','123456'),(4,'王五','123456')

-- 加密
UPDATE `testmd5` SET pwd =MD5(pwd)  WHERE id=1  

-- 在插入的时候加密
INSERT INTO `testmd5` VALUES (6,'huawei',MD5(123456))
INSERT INTO `testmd5` VALUES (7,'oppo',MD5(123456789))

-- 如何校验  将用户传递进来的密码，进行MD5加密，然后比对加密后的值 (传递进来的值一样，加密后的值也一样)

-- 接收用户传来的用户名和密码
SELECT * FROM `testmd5` WHERE `name`='oppo' AND pwd=MD5('123456789')

事务

什么是事务

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
在 MySQL 中，事务是一组SQL语句的执行，它们被视为一个单独的工作单元。
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理 insert、update、delete 语句。

一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

脏读：在一个事务内读取了另一个事务未提交的数据，导致前后读取不一致。
幻读（虚读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
不可重复读：在事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

事务处理

MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT 来实现：
BEGIN 或 START TRANSACTION：开用于开始一个事务。
ROLLBACK：事务回滚，取消之前的更改。
COMMIT：事务确认，提交事务，使更改永久生效。
2、直接用 SET 来改变 MySQL 的自动提交模式:
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交

补充：SAVEPOINT – 用于在事务中设置保存点，以便稍后能够回滚到该点：SAVEPOINT savepoint_name;
ROLLBACK TO SAVEPOINT – 用于回滚到之前设置的保存点：ROLLBACK TO SAVEPOINT savepoint_name;
RELEASE SAVEPOINT – 撤销保存点。

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 创建数据表
Query OK, 0 rows affected (0.04 sec)
 
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)

mysql> SAVEPOINT savepoint_name;    // 声明一个 savepoint
Query OK, 1 rows affected (0.00 sec)

mysql> ROLLBACK TO savepoint_name;  // 回滚到savepoint
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 开始事务
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滚
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因为回滚所以数据没有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

索引

MySQL 索引是一种数据结构，用于加快数据库查询的速度和性能。

索引的分类

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列为主键
唯一索引（UNIQUE KEY）
- 避免重复的列出现，唯一索引可以重复
常规索引（KEY/INDEX）
- 默认的，通过关键字来设置
全文索引（FullText）
- 在特定的数据库引擎下才有，MyLSAM
- 快速定位数据

索引的使用

-- 使用 CREATE INDEX 语句可以创建普通索引。普通索引是最常见的索引类型，用于加速对表中数据的查询。
--CREATE INDEX 的语法：
CREATE INDEX index_name
ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

-- 可以使用 ALTER TABLE 命令可以在已有的表中创建索引。ALTER TABLE 允许你修改表的结构，包括添加、修改或删除索引。
--ALTER TABLE 创建索引的语法：
ALTER TABLE table_name
ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

--可以使用 DROP INDEX 语句来删除索引。DROP INDEX 的语法：
DROP INDEX index_name ON table_name;

--显示所有的索引信息
SHOW INDEX FROM name1

测试索引

-- 测试索引 建表并用函数插入100万条数据
CREATE TABLE `app_user`(
`id` BIGINT(20) UNSIGNED not null auto_increment,
`name` VARCHAR(50) DEFAULT '' COMMENT '用户昵称',
`email` VARCHAR(50) not null COMMENT '用户邮箱',
`phone` VARCHAR(20) DEFAULT '' COMMENT '手机号',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT '性别 0男  1女',
`password` VARCHAR(100) NOT NULL COMMENT '密码',
`age` TINYINT(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP null DEFAULT CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
PRIMARY key(`id`)
)ENGINE = innodb DEFAULT charset = utf8mb4 comment = 'app用户表'

set global log_bin_trust_function_creators=TRUE;
-- 插入100万条数据
DELIMITER $$
CREATE FUNCTION mock_data_version2()
returns int 
begin
	DECLARE num int DEFAULT 1000000;
	DECLARE i int DEFAULT 0;
	WHILE i<num DO
	  INSERT into `app_user` (`name`,email,phone,gender,`password`,age)VALUES    (CONCAT('用户',i),'123456@qq.com',
		CONCAT('13',floor(RAND()*((999999999-100000000)+100000000))),
		FLOOR(RAND()*2),
		UUID(),
		FLOOR(RAND()*100));
	set i = i+1;
END WHILE;
RETURN i;
end;
SELECT mock_data_version2()

-- 测试索引作用
SELECT * FROM app_user WHERE `name` = '用户99999'  16秒
create index id_user_app_name on app_user(`name`)
SELECT * FROM app_user WHERE `name` = '用户99999'  0.1秒
# 索引在小数据量的时候，作用不大，但是在大数据的时候，区别十分明显

索引原则

索引不是越多越好
不要对进程变动数据加索引
小数据量的表不需要加索引
索引一般加在常用来查询的字段
b树：innoDB的默认数据结构

权限管理和备份

用户管理

创建/删除用户

-- 创建用户 create user 用户名 identified by ‘密码’
CREATE USER w1IDENTIFIED BY '123456'

-- 删除用户
DROP USER w1
-- 修改密码（修改当前用户密码）
SET PASSWORD = PASSWORD('666666')
-- 修改密码（修改指定用户密码）
SET PASSWORD FOR w1 = PASSWORD('123456')
-- 重命名 rename user 旧名 to 新名
RENAME USER w1 TO w2

授/撤权用户

-- 用户授权 all privilege 全部权限, 库.表
GRANT ALL PRIVILEGES ON *.* TO w1
-- 这个被授'所有'权力的用户.没有更改其他用户的权限

-- 权限查询
SHOW GRANTS FOR w1	-- 查看指定用户的权限
SHOW GRANTS FOR root@localhost	-- 查看root权限

-- root用户权限如下 
-- GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION

-- 撤销权限 remove 哪个权限 ,哪个库撤销,给谁撤销
REMOVE ALL PRIVILEGES ON *.* FROM w1

权限解释

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER    -- 允许使用ALTER TABLE
ALTER ROUTINE    -- 更改或取消已存储的子程序
CREATE    -- 允许使用CREATE TABLE
CREATE ROUTINE    -- 创建已存储的子程序
CREATE TEMPORARY TABLES        -- 允许使用CREATE TEMPORARY TABLE
CREATE USER        -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW        -- 允许使用CREATE VIEW
DELETE    -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE        -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX     -- 允许使用CREATE INDEX和DROP INDEX
INSERT    -- 允许使用INSERT
LOCK TABLES        -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES    -- 未被实施
RELOAD    -- 允许使用FLUSH
REPLICATION CLIENT    -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE    -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT    -- 允许使用SELECT
SHOW DATABASES    -- 显示所有数据库
SHOW VIEW    -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER    -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE    -- 允许使用UPDATE
USAGE    -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限
 
/* 表维护 */
 
-- 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
-- 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
-- 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name]

备份

为什么要备份：
保障重要数据不丢失
保障数据转移的安全性

MySQL数据库备份的方式

直接拷贝物理文件
在例如SQLyog 这种可视化工具中手动导出
使用命令行（cmd）导出 mysqldump

规范数据库设计（三大范式）

为什么需要设计

当数据库比较复杂的时，就需要设计
糟糕的数据库设计：
数据太大，浪费空间
数据库插入、删除很麻烦，容易造成异常
程序的性能差
良好的数据库设计：
节约内存空间
保障数据库的完整性
方便我们开发系统
软件开发中，关于数据库的设计
分析需求：分析业务和需要处理的数据库需求
概要设计：设计关系图 E-R图

设计数据库的步骤（以个人博客为例）：

收集信息，分析需求
用户表（用户登录注销、用户的个人信息，写博客，创建分类）
分类表（文章分类，谁创建的）
文章表（文章的信息）
友情链接表（友情链接的信息）
自定义表（系统信息，某个关键的字，或者一些主字段）
标识实体（把需求落地到每个字段）
标识实体之间的关系
写博客 user --> blog
创建分类 user --> category
关注 user --> user
友情链接 links
评论 user --> user -->blog

三大范式

为什么需要数据规范化？

信息重复
更新异常
插入异常
- 无法正常显示信息
删除异常
- 丢失有效信息

第一范式(1NF)

要求数据库的每一列都是不可分割的原子数据项；原子性：保证每一列不可再分
在这里插入图片描述

将家庭信息拆为家庭人口和户籍学校信息拆为学历和所在年级

第二范式(2NF)

在第一范式的基础上，每张表只描述一件事情
在这里插入图片描述
在上图所示的情况中，同一个订单中可能包含不同的产品，因此主键必须是“订单号”和“产品号”联合组成，

但可以发现，产品数量、产品折扣、产品价格与“订单号”和“产品号”都相关，但是订单金额和订单时间仅与“订单号”相关，与“产品号”无关，

这样就不满足第二范式的要求，调整如下，需分成两个表：
在这里插入图片描述

第三范式(3NF)

在第二范式的基础上，确保数据库表中的每一列数据都和主键直接相关，而不能间接相关
在这里插入图片描述
上表中，所有属性都完全依赖于学号，所以满足第二范式，但是“班主任性别”和“班主任年龄”直接依赖的是“班主任姓名”，

而不是主键“学号”，所以需做如下调整：
在这里插入图片描述

规范性和性能的问题（鱼和熊掌不可兼得）

关联查询的表不得超过三张表（阿里规范）

考虑商业化的需求和目标（成本、用户体验）数据库的性能更加重要
在规范性能的问题的时候，需要适当的考虑一下规范性
故意给某些表增加一些冗余的字段（从多表查询中变为单表查询）
故意增加一些计算列（从大数据量降低为小数据量的查询：索引）

JDBC(重点)

我们的程序会通过数据库驱动，和数据库打交道

JDBC是什么

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。
SUN公司为了简化开发人员的（对数据库的统一）操作，提供一个（Java操作数据库的）规范，俗称JDBC
这些规范的实现由具体的厂商去做！
对于开发人员来说，我们只需掌握JDBC接口操作即可！
java.sql
javax.sql
还需要导入一个数据库驱动包mysql-connector-java-【版本号】.jar

第一个JDBC程序

创建测试数据库

CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE `users`(
	id INT PRIMARY KEY,
	NAME VARCHAR(40),
	PASSWORD VARCHAR(40),
	email VARCHAR(60),
	birthday DATE
);
INSERT INTO `users`(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04')

1、创建一个普通项目

2、导入数据库驱动

新建一个lib目录
在这里插入图片描述
将mysql-connector-java-5.1.47.jar粘贴至lib目录中

将lib目录参加为库

3、编写测试代码

package JDBClesson;

import java.sql.*;

public class JDBCFirstDemo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {
        //1、加载驱动
        Class.forName("com.mysql.jdbc.Driver");     //固定写法，加载驱动

        //2、用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "123456";

        //3、连接成功，数据库对象 Connection 代表数据库
        Connection connection = DriverManager.getConnection(url,username,password);


        //4、执行SQL的对象 Statement 执行sql的对象
        Statement statement = connection.createStatement();

        //5、执行SQL的对象 去 执行SQL，可能存在结果，查看返回结果
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);  //返回的结果集，结果集中封装了我们全部的查询出来的结果

        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("NAME"));
            System.out.println("pwd="+resultSet.getObject("PASSWORD"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birth="+resultSet.getObject("birthday"));
            System.out.println("----------------------------------------");
        }
        //6、释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

步骤总结:
1.加载驱动
2.连接数据库 DriverManager
3.获得执行sql的对象 Statement
4.获得返回的结果集
5.释放连接

对象解释

Driver

Class.forNmae("com.mysql.jdbc.Driver")	//固定写法,加载驱动

Connection connection = DriverManager.getConnection(url,username,password);

//connection 代表数据库 
//数据库层面的代码可以通过connection.代码 实现
//自设置动提交\事务提交\事务回滚
connection.setAutoCommit();
connection.commit();
connection.rollback();

url

String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&useSSL=true";

//	 mysql默认端口号--3306
//   jdbc:mysql://主机地址:端口号/数据库名?参数1&参数2&参数3

Statement 执行SQL的对象; PrepareStatement 执行SQL的对象

String sql = "SELECT * FROM users";	//编写SQL

statement.executeQuery();	//查询操作 返回 ResultSet
statement.execute();	//执行任何SQL
statement.executeUpdate();		//更新\插入\删除,都用这个,返回一个受影响的行数

ResultSet：查询的结果集，封装了所有的查询结果

//获得指定的数据类型
ResultSet resultSet = statement.executeQuery(sql); 

resultSet.getObject();	//在不知道类型的情况下使用,如果知道类型就使用指定类型
resultSet.getString();	
resultSet.getInt();	
resultSet.getFloat();	
resultSet.getDate();
...

//遍历,指针
resultSet.beforeFirst();	//光标移动到最前面
resultSet.afterLast();		//光标移动到最后面
resultSet.next();		//光标移动到下一个数据
resultSet.previous();	//光标移动到前一行
resultSet.absolute(row);	//光标移动到指定行

释放资源

resultSet.close();
statement.close();
connection.close();
//耗资源，一定要关闭,不关闭会影响资源使用

statement对象

JDBC中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。

CRUD操作- create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作：

Statement st = conn.createStatement();
String sql = "insert into user(...,) values(...,)";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("插入成功");
}

CRUD操作- delete

使用executeUpdate(String sql)方法完成数据删除操作，示例操作：

Statement st = conn.createStatement();
String sql = "delete from user where id=1";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("删除成功");
}

CRUD操作- update

使用executeUpdate(String sql)方法完成数据修改操作，示例操作：

Statement st = conn.createStatement();
String sql = "update user set name='' where name=''";
int num = st.executeUpdate(sql);
if(num>0){
    System.out.println("修改成功");
}

CRUD操作- read

Statement.executeQuery方法用于向数据库发送查询语句，

Statement st = conn.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
    //更加获取列的数据类型,分别调用rs的相应方法映射到Java对象中
}

代码实现增删改查

1、提取工具类

创建db.properties文件

driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456

创建一个JdbcUtils类，将db.properties文件内容导入

public class JdbcUtils {

    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;

    static {

        try{
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);

            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

            //1、驱动只用加载一次
            Class.forName(driver);
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return  DriverManager.getConnection(url, username, password);
    }

    //释放资源
    public static void release(Connection conn, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
    }

}

2、编写增删改的方法 executeUpdate
增 (测试)

public class TestInsert {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();   //获取数据库连接
            st=conn.createStatement();      //获取sql的执行对象

            String sql="INSERT INTO users(id,`name`,`password`,`email`,`birthday`) VALUES(4,'woodwhale','123456','1966890773@qq.com','2021-02-17')";

            int i= st.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

删（测试）

public class TestDelete {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();   //获取数据库连接
            st=conn.createStatement();      //获取sql的执行对象

            String sql="DELETE FROM users WHERE id=4";

            int i= st.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

改（测试）

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn=null;
        Statement st=null;
        ResultSet rs=null;
        try {
            conn = JdbcUtils.getConnection();   //获取数据库连接
            st=conn.createStatement();      //获取sql的执行对象

            String sql="UPDATE users SET `name`='woodwhale' WHERE id=1";

            int i= st.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

3、查询 executeQuery

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            //SQL
            String sql = "select * from users where id=1";

            rs = st.executeQuery(sql);  //查询完毕会返回一个结果集

            while(rs.next()){
                System.out.println(rs.getString("name")); //查询`name`列
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

SQL注入

sql存在漏洞，会被攻击导致数据泄露

SQL注入测试：

public class SQLinjection {
    public static void main(String[] args) {
        // login("woodwhale","123456"); 正常登录
        login(" ' or '1=1 "," ' or '1=1"); //拼接登录，恶意盗取数据

    }

    //登录业务
    public static void login(String username,String password){
        Connection conn = null;        Statement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();
            st = conn.createStatement();

            //SQL
            String sql = "SELECT * FROM users WHERE `name`='"+username+"' AND `password`='"+password+"'";

            rs = st.executeQuery(sql);  //查询完毕会返回一个结果集

            while(rs.next()){
                System.out.println(rs.getString("name"));
                System.out.println(rs.getString("password"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}
//SQL注入本质就是拼接登录！

PreparedStatement对象

为了防止SQL注入，我们引入PreparedStatement对象，与前面的Statement对象作比较。
PreparedStatement 可以防止SQL注入，效率更高！
防注入本质：把传递进来的参数当作字符；假设其中存在转义字符，比如说 ` 会被直接转义

增

public class TestTnsert {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();
            //区别，preparedstatement需要预编译
            //使用？ 占位符代替参数
            String sql ="insert into users(id,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";

            st = conn.prepareStatement(sql);   //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4); //id
            st.setString(2,"die");
            st.setString(3,"123456");
            st.setString(4,"1234567@qq.com");

            //注意点：sql.Date(数据库用) 和 util.Date(java用)
            st.setDate(5,new java.sql.Date(new Date().getTime())); //获得时间戳

            //执行
           int i = st.executeUpdate();
           if(i>0){
               System.out.println("插入成功");
           }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

删

public class TestDelete {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符代替参数
            String sql ="delete from users where id=?";

            st = conn.prepareStatement(sql);   //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setInt(1,4);

            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

改

public class TestUpdate {
    public static void main(String[] args) {

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            //区别
            //使用？ 占位符代替参数
            String sql ="update users set `name`=? where id=?";

            st = conn.prepareStatement(sql);   //预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            st.setString(1,"木鲸");
            st.setInt(2,1);


            //执行
            int i = st.executeUpdate();
            if(i>0){
                System.out.println("更改成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,null);
        }
    }
}

查

public class TestSelect {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "select * from users where id=?";  //编写sql

            st = conn.prepareStatement(sql);    //预编译

            st.setInt(1,1); //传递参数

            //执行
            rs = st.executeQuery();
            if(rs.next()){
                System.out.println(rs.getString("name"));
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

JDBC事务操作

ACID原则
原子性、一致性、隔离性、持久性

事务操作步骤：

1、关闭数据库的自动提交,会自动开启事务
2、写一组事务
3、事务完毕，提交事务
4、如果失败，则会默认回滚（也可以选择写rollback）

//事务测试
public class TestTransaction {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;

        try {
            conn = JdbcUtils.getConnection();

            //1、关闭数据库的自动提交,自动会开启事务
            conn.setAutoCommit(false); //关闭自动提交，开启事务

            //2、写一组事务
            String sql1 ="update account set money = money-100 where `name`='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 ="update account set money = money+100 where `name`='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //3、事务完毕，提交事务
            conn.commit();
            System.out.println("成功");

        } catch (SQLException throwables) {
            try {
                //4、如果失败，则会默认回滚
                conn.rollback();    //如果失败则回滚事务
            } catch (SQLException e) {
                e.printStackTrace();
            }
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(conn,st,rs);
        }
    }
}

数据库连接池

数据库连接 — 执行完毕 — 释放
连接 — 释放十分浪费系统资源
池化技术：准备一些预先的资源，过来就连接预先准备好的

编写连接池，实现一个接口 DataSource
连接池问题：最小连接数，最大连接数–>等待超时

开源数据源实现：

DBCP
C3P0
Druid：阿里巴巴

使用了这些数据库连接池之后,我们在项目开发中就不需要编写连接数据库的代码了。

无论使用什么数据源，本质是一样的，datasource接口不会变，方法就不会变。

DBCP

DBCP(DataBase connection pool),数据库连接池。是 apache 上的一个 java 连接池项目，也是 tomcat 使用的连接池组件。单独使用dbcp需要2个包：commons-dbcp.jar,commons-pool.jar由于建立数据库连接是一个非常耗时耗资源的行为，所以通过连接池预先同数据库建立一些连接，放在内存中，应用程序需要建立数据库连接时直接到连接池中申请一个就行，用完后再放回去。
Tomcat 的连接池正是采用该连接池来实现的。该数据库连接池既可以与应用服务器整合使用，也可由应用程序独立使用。Tomcat通过JAR包tomcat-dbcp.jar集成了连接池DBCP的功能，通过在context.xml中配置连接池的方式引入DBCP连接池，也可以在server.xml文件中配置全局的连接池。

参数	默认值	描述
initialSize	0	初始化连接:连接池启动时创建的初始化连接数量,1.2版本后支持
maxTotal	8	最大活动连接:连接池在同一时间能够分配的最大活动连接的数量, 如果设置为非正数则表示不限制. dbcp1.X为maxActive
maxIdle	8	最大空闲连接:连接池中容许保持空闲状态的最大连接数量,超过的空闲连接将被释放,如果设置为负数表示不限制
minIdle	0	最小空闲连接:连接池中容许保持空闲状态的最小连接数量,低于这个数量将创建新的连接,如果设置为0则不创建
maxWaitMillis	无限	最大等待时间:当没有可用连接时,连接池等待连接被归还的最大时间(以毫秒计数),超过时间则抛出异常 ,如果设置为-1表示无限等待 dbcp1.X为maxWait

C3P0

C3P0 是一个开源的 JDBC 数据库连接池库，它提供了高度灵活和高性能的连接池实现。C3P0 可以与各种关系型数据库一起使用，包括 MySQL、Oracle、SQL Server 等。它的名称 “C3P0” 实际上代表 “Connect3 Pool”，意味着它可以管理多个数据库连接。

C3P0 的主要特点包括：

支持连接池的连接重用和管理。
可以配置连接池的各项参数，以满足不同性能需求。
具备连接超时、闲置连接的回收、异常处理等功能。
提供了JMX 支持，可以通过 JMX 监控和管理连接池。支持 JDBC3 和 JDBC4。

要使用 C3P0 数据库连接池，首先需要在项目中引入 C3P0 的相关依赖。如果使用 Maven，可以在 pom.xml 文件中添加以下依赖：

<dependency>
    <groupId>com.mchange</groupId>
    <artifactId>c3p0</artifactId>
    <version>0.9.5.5</version>
</dependency>

创建 C3P0 连接池配置

创建一个 C3P0 连接池的配置对象。C3P0 提供了ComboPooledDataSource 类来实现这一功能。以下是一个简单的配置示例：

import com.mchange.v2.c3p0.ComboPooledDataSource;

public class C3P0Config {
    public static ComboPooledDataSource createDataSource() {
        ComboPooledDataSource dataSource = new ComboPooledDataSource();
        dataSource.setJdbcUrl("jdbc:mysql://localhost:3306/mydatabase");
        dataSource.setUser("root");
        dataSource.setPassword("password");
        // 其他配置参数...
        return dataSource;
    }
}

获取数据库连接
一旦配置了 C3P0 数据库连接池，就可以从中获取数据库连接。以下是一个获取连接的示例：

import com.mchange.v2.c3p0.ComboPooledDataSource;

import java.sql.Connection;
import java.sql.SQLException;

public class Main {
    public static void main(String[] args) {
        ComboPooledDataSource dataSource = C3P0Config.createDataSource();
        try {
            // 从连接池获取数据库连接
		    Connection connection = dataSource.getConnection();
		    
		    // 在此处执行数据库操作
		    
		    // 使用完连接后记得关闭
		    connection.close();
		} catch (SQLException e) {
		    e.printStackTrace();
		}
	}
}

叁次

关注

15
点赞
踩
10

收藏

觉得还不错? 一键收藏
0
评论
Mysql学习

前端（页面：展示，数据）；后台（连接点：连接数据库JDBC，链接前端（控制，控制试图跳转，和给前端传递数据））；数据库（存数据，TXT，Excel，Word），数据库（DB,Database）是按照数据结构来组织、存储和管理数据的仓库。数据库管理系统（DBMS）分为关系型和非关系型。MySQL就是关系型数据库管理系统（RDBMS），所谓的关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。MySQL 是开源的，目前隶属于 Oracle 旗下产品。
复制链接

扫一扫