MySQL学习

MySQL数据库

1. 初始MySQL

JavaEE：企业级Java开发

  • 前端：页面渲染（展示）

  • 后台：连接点（连接数据库JDBC，连接前端以控制视图跳转和给前端传递数据）

  • 数据库：存储数据（txt，Excel，word）

1.1 什么是数据库

DB，DataBase

定义：按照数据结构来组织、存储和管理数据的仓库。

作用：存储数据，管理数据

1.2 数据库分类

  • 关系型数据库（SQL）：行列

    通过表和表之间、行和列之间的关系进行数据的存储

    eg. MySQL, Oracle, Sql Server, DB2, SQLlite……

  • 非关系型数据库（noSQL，Not Only SQL）：键值对

    对象存储，通过对象自身的属性来决定

    eg. Redis, MongoDB……

1.3 DBMS 数据库管理系统

  • 数据库的管理软件，能够科学有效地管理数据。维护和获取数据。

  • MySQL，一种关系型数据库管理系统（RDBMS）。

1.4 MySQL简介

版本：5.7（稳定），8.0（最新）

安装建议：尽量不要使用exe安装，尽可能使用压缩包安装。

安装参考链接：https://www.cnblogs.com/java-learning-xx/p/13892997.html

MySQL启动：net start mysql（以管理员身份运行）

MySQL关闭：net stop mysql

连接数据库：mysql -u root -p123456

断开数据库：exit

1.5 安装SQLyog

  • 安装-->注册-->打开连接数据库

  • 创建数据库school

 

 

 

 　　　

  • 创建表student

  • 查看表，添加记录

1.6 连接数据库

SQL：结构化查询语言(Structured Query Language)

常用命令：

D:\Environment\mysql-5.7.32\bin>mysql -uroot -p123456       -- 命令行连接数据库
mysql> update mysql.user set authentication_string=password('123456') where user='root' and Host='localhost';                   -- 修改用户密码
mysql> flush privileges;            -- 刷新权限
​
----------------------------------------------
​
mysql> show databases;              -- 查看所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
5 rows in set (0.00 sec)
​
mysql> use school;                  -- 切换数据库（use 数据库名）
Database changed
​
mysql> show tables;                 -- 查看数据库中所有的表
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)
​
mysql> describe student;            -- 显示数据库中某表的信息
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(10)      | NO   | PRI | NULL    |       |
| name  | varchar(100) | NO   |     | NULL    |       |
| age   | int(2)       | NO   |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
​
mysql> create database BTS;         -- 创建一个数据库
Query OK, 1 row affected (0.00 sec)
​
mysql> exit                         -- 退出连接
Bye
​
-- SQL单行注释
/*
SQL多行注释
*/

 

DDL：数据库定义语言

DML：数据库操作语言

DQL：数据库查询语言

DCL：数据库控制语言

2. 操作数据库

操作数据库 > 操作数据库中的表 > 操作数据库中表的数据

SQL不区分大小写！！！

2.1 操作数据库

  • 创建数据库

    CREATE DATABASE [IF NOT EXISTS] BTS

  • 删除数据库

    DROP DATABASE [IF EXISTS] BTS

  • 使用数据库

    -- 如果表名或者字段名是一个特殊的关键字，需要被``包裹
USE `school`

  • 查看数据库

    SHOW DATABASES

2.2 数据库的列类型

数值

  • tinyint：十分小的数据 1个字节

  • smallint：较小的数据 2个字节

  • mediumint：中等大小的数据 3个字节

  • int：标准的整数 4个字节

  • bigint：较大的数据 8个字节

  • float：浮点数 4个字节

  • double：浮点数 8个字节

  • decimal：字符串形式的浮点数（一般金融计算的时候使用）

字符串

  • char：固定大小的字符串 0~255

  • varchat：可变字符串 0~65535

  • tinytext：微型文本 2^8-1

  • text：文本串 2^16-1（保存大文本）

时间日期

  • date：日期格式 YYYY-MM-DD

  • time：时间格式 HH: mm: ss

  • datetime：日期时间格式 YYYY-MM-DD HH: mm: ss

  • timestamp：时间戳 1970.1.1到现在的毫秒数

  • year：年份表示

null

  • 未知/没有值

  • 不要使用NULL进行运算，结果一定为NULL！！！

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

  • Unsigned：无符号整数，声明了该列则表示非负；

  • zorefill：零填充，不足的位数用零填充；

  • 自增：通常用于设计唯一的主键，可以自定义自增的起始值和步长；

  • 非空：设置为非空时，不给它赋值则会报错，默认为NULL；

  • 默认：设置默认值，不指定该列的值时使用默认值。

每个表必须存在的5个字段（拓展）：

id（主键）、`version`（乐观锁）、is_delete（伪删除）、gmt_create（创建时间）、gmt_update（修改时间）

2.4 创建数据库表

示例：

-- 学号，姓名，密码，性别，出生日期，家庭住址，邮箱
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `names` 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

 

格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
    `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
    ……
    `字段名` 列类型 [属性] [索引] [注释]
)[表类型] [字符集设置] [注释]

 

常用查看命令：

-- 查看创建数据库的语句
SHOW CREATE DATABASE school
-- 查看创建表的语句
SHOW CREATE TABLE student
-- 显示表的结构
DESC student

 

2.5 数据表的类型

关于数据库引擎：

  • INNODB 默认使用

  • MYISAM 早期使用

 MYISAMINNODB
事务支持 不支持 支持
数据行锁定 不支持 支持
外键约束 不支持 支持
全文索引 支持 不支持
表空间的大小 较小 较大，约为2倍
优点 节约空间，速度较快 安全性高，多表多用户操作

数据表在物理空间存在的位置：

所有数据库文件都存储在data目录下，一个文件夹就对应一个数据库，本质上还是文件的存储！

MySQL引擎在物理文件上的区别（了解）：

  • INNODB：在数据库文件夹中只有一个*.frm文件，以及上级目录下的ibdata1文件；

  • MYISAM：

    • *.frm：表结构的定义文件；

    • *.MYD：数据文件（data）；

    • *.MYI：索引文件（index）。

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

MySQL的默认编码时Latin1，不支持中文。须在创表时指定字符集编码为utf8，或在my.ini配置文件中设置默认的编码（character-set-server=utf8，不建议使用，会使得移植性变差）。

2.6 修改/删除表

修改表

-- 1. 修改表名
-- ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher2

-- 2. 增加表的字段
-- ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE teacher2 ADD age INT(3)

-- 3. 修改表的字段（重命名/修改约束）
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[]
ALTER TABLE teacher2 CHANGE age age1 INT(3)     -- 字段重命名
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher2 MODIFY age1 VARCHAR(3)        -- 修改约束

-- 4. 删除表的字段
-- ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher2 DROP age1

 

删除表

-- 删除表
-- DROP TABLE [IF EXISTS] 表名
DROP TABLE IF EXISTS teacher2

 

注：

  • 所有的创建和删除操作尽量加上判断，以免报错！

  • 字段名使用``包裹；

  • --和/**/表示注释；

  • SQL关键字大小写不敏感，建议书写时使用小写；

  • 所有的符号全部使用英文，不然会报错。

3. DML管理数据

3.1 外键（了解）

方式一 创建表时增加约束（麻烦复杂）

-- 年级代号，年级名称
CREATE TABLE IF NOT EXISTS `grade` (
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
    `gradename` VARCHAR(10) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学号，姓名，密码，性别，出生日期，学生年级，家庭住址，邮箱
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `names` 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 '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '学生年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`),
    KEY `FK_gradeid` (`gradeid`),                        -- 定义外键key
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)        -- 添加约束
)ENGINE=INNODB DEFAULT CHARSET=utf8

 

方式二 创建表后添加约束

-- 年级代号，年级名称
CREATE TABLE IF NOT EXISTS `grade` (
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级',
    `gradename` VARCHAR(10) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- 学号，姓名，密码，性别，出生日期，学生年级，家庭住址，邮箱
CREATE TABLE IF NOT EXISTS `student` (
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `names` 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 '出生日期',
    `gradeid` INT(10) NOT NULL COMMENT '学生年级',
    `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭地址',
    `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
    PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- ALTER TABLE 表名
-- ADD CONSTRAINT 约束名 FOREIGN KEY(作为外键的列) REFERENCES 被引用的表(被引用的字段);
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);
-- 此处student为从表（引用），grade为主表（被引用）

 

以上操作都是物理外键，是数据库级别的外键，不建议使用（避免数据库过多造成困扰，了解即可）

策略：

  • 数据库就是单纯的表，只用于存储数据，即只有行（数据）和列（字段）；

  • 想要使用多张表的数据/使用外键，利用程序来实现（不得使用外键和级联，一切外键概念必须在应用层解决）。

3.2 DML语言（重点）

数据库存在的意义：数据存储 + 数据管理

insert/update/delete

3.3 添加

-- 插入语句（添加）
-- 语法：INSERT INTO 表名([字段名1,字段名2，字段名3……]) VALUES('值1','值2','值3'……)[('值1','值2','值3'……)……]
-- 主键设置为自增，可以忽略
INSERT INTO `grade`(`gradename`) VALUES('研二')
-- 插入多个字段
INSERT INTO `grade`(`gradename`) VALUES('研三'),('博一')
-- 省略字段时，自动匹配，但需保证数据和字段一一对应
INSERT INTO `grade` VALUES(4,'研一')

 

3.4 修改

-- 修改语句
-- 指定条件，只修改符合条件的记录；不指定条件，则会修改表中所有的记录
-- 语法：UPDATE 表名 SET 字段名=值[,字段名2=值2,……] WHERE [条件]
UPDATE `grade` SET `gradename`='博二' WHERE `gradeid` = 1

 

条件：WHERE 子句（返回布尔值）

操作符含义
= 等于
<>或!= 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN……AND…… 在某个范围内
AND 且
OR 或

3.5 删除

DELETE命令

-- 删除数据
-- 语法：DELETE FROM 表名 [WHERE 条件]
DELETE FROM `grade` WHERE `gradeid`=1        -- 不写条件则会全部删除

 

TRUNCATE命令

作用：完全清空一个数据库表，表的结构和索引约束保持不变！

-- 清空表
-- 语法：TRUNCATE 表名
TRUNCATE `student`

 

DELETE和TRUNCATE的区别：

  • 相同点：都能删除数据，且不删除表结构；

  • 不同点：

    • TRUNCATE重新设置 自增列，计数器会归零；

    • TRUNCATE不会影响事务。

4. DQL 查询数据（重点）

4.1 DQL

Data Query Language：数据查询语言

  • 所有的查询操作都使用它 Select；

  • 简单或者复杂的查询都能实现；

  • 数据库中最核心、最重要、使用频率最高的语言。

SELECT语法

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
    [left | right | inner join table_name2]  -- 联合查询
    [WHERE ...]  -- 指定结果需满足的条件
    [GROUP BY ...]  -- 指定结果按照哪几个字段来分组
    [HAVING]  -- 过滤分组的记录必须满足的次要条件
    [ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
    [LIMIT {[offset,]row_count | row_countOFFSET offset}];
    --  指定查询的记录从哪条至哪条

 

4.2 指定查询字段

-- 查询表中所有的数据列结果 , 采用 **" \* "** 符号; 但是效率低，不推荐 .
 
-- 查询所有学生信息
SELECT * FROM student;
 
-- 查询指定列(学号 , 姓名)
SELECT `studentno`,`studentname` FROM student;

 

AS 别名

-- 为列取别名(当然as关键词可以省略)
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student;
 
-- 使用as也可以为表取别名
SELECT `studentno` AS 学号,`studentname` AS 姓名 FROM student AS s;
 
-- 使用as,为查询结果取一个新名字
-- CONCAT()函数拼接字符串
SELECT CONCAT('姓名:',studentname) AS 新姓名 FROM student;

 

DISDINCT 去重

作用：去除SELECT查询出来的结果中重复的记录，重复的数据只显示一条。

-- # 查看哪些同学参加了考试(学号)  去除重复项
SELECT * FROM result; -- 查看考试成绩
SELECT `studentno` FROM result; --  查看哪些同学参加了考试
SELECT DISTINCT `studentno` FROM result; -- 了解:DISTINCT 去除重复项 , (默认是ALL)

 

数据库的列

-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长（变量）
SELECT VERSION(); -- 查询版本号（函数）
SELECT 100*3-1 AS 计算结果; -- 用于计算（表达式）
 
-- 学员考试成绩集体提分一分查看
SELECT `studentno`,`StudentResult`+1 AS '提分后' FROM result;

 

数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量……

4.3 WHERE语句

作用：检索数据中 符合条件 的记录。

搜索条件可由一个或多个逻辑表达式组成 , 结果一般为一个布尔值。

逻辑操作符

操作符语法描述
and或&& a and b 或 a&&b 逻辑与
or或|| a or b 或 a || b 逻辑或
not或！ not a 或 !a 逻辑非

尽量使用英文字母，而不是符号！

-- 满足条件的查询(where)
SELECT `Studentno`,`StudentResult` FROM result;
 
-- 查询考试成绩在95-100之间的
SELECT `Studentno`,`StudentResult`
FROM result
WHERE `StudentResult`>=95 AND `StudentResult`<=100;
 
-- AND也可以写成 &&
SELECT `Studentno`,`StudentResult`
FROM result
WHERE `StudentResult`>=95 && `StudentResult`<=100;
 
-- 模糊查询(对应的词:精确查询)
SELECT `Studentno`,`StudentResult`
FROM result
WHERE `StudentResult` BETWEEN 95 AND 100;
 
-- 除了1000号同学,要其他同学的成绩
SELECT `studentno`,`studentresult`
FROM result
WHERE `studentno`!=1000;
 
-- 使用NOT
SELECT `studentno`,`studentresult`
FROM result
WHERE NOT `studentno`=1000;

 

模糊查询：本质是比较运算符

操作符语法描述
IS NULL a is null 如果为NULL，结果为真
IS NOT NULL a is not null 如果不为NULL，结果为真
BETWEEN AND a between b and c 如果a在b和c之间，结果为真
LIKE a like b 如果a匹配b，结果为真
IN a in (a1, a2, a3……) 如果a在a1，a2……其中某个值之中，结果为真 
-- 模糊查询 between and \ like \ in \ null
 
-- =====================LIKE========================
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符)  _ (代表一个字符)
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘%';
 
-- 查询姓刘的同学,后面只有一个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘_';
 
-- 查询姓刘的同学,后面只有两个字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '刘__';
 
-- 查询姓名中含有 嘉 字的
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentname` LIKE '%嘉%';
 
-- 查询姓名中含有特殊字符的需要使用转义符号 '\'
-- 自定义转义符关键字:  ESCAPE ':'

-- =====================IN========================
-- 查询具体的一个或多个值，不与%或_通配符结合使用
-- 查询学号为1000,1001,1002的学生姓名
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno` IN (1000,1001,1002);
-- 查询地址在北京,南京,河南洛阳的学生
SELECT `studentno`,`studentname`,`address` FROM `student`
WHERE `address` IN ('北京','南京','河南洛阳');

-- ======================NULL=======================
-- 查询没有填写出生日期的同学（null）
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
SELECT `studentname` FROM `student`
WHERE `BornDate` IS NULL;
-- 查询填写了出生日期的同学（不为空）
SELECT `studentname` FROM `student`
WHERE `BornDate` IS NOT NULL;
-- 查询没有写家庭住址的同学（空字符串不等于null）
SELECT `studentname` FROM `student`
WHERE `Address`='' OR `Address` IS NULL;

 

4.4 联表查询

JOIN 对比

 

 

操作描述
INNER JOIN 如果表中至少有一个匹配，就返回行
LEFT JOIN 会从左表中返回所有的值，即使右表中没有匹配
RIGHT JOIN 会从右表中返回所有的值，即使左表中没有匹配

 

/*
连接查询
    如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
    查询两个表中的结果集中的交集
外连接 outer join
    左外连接 left join
        (以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
    右外连接 right join
        (以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
        
等值连接和非等值连接
自连接
*/
 
-- 查询参加了考试的同学信息(学号,学生姓名,科目编号,分数)
SELECT * FROM student;
SELECT * FROM result;
 
/*思路:
1. 分析需求,确定查询的列来源于两个类(student  result)，连接查询
2. 确定使用哪种连接查询（7种）？
*/
-- 内连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student AS s
INNER JOIN result AS r
ON r.studentno = s.studentno
 
-- 右连接(也可实现)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
 
-- 等值连接
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s , result r
WHERE r.studentno = s.studentno
 
-- 左连接 (查询了所有同学,不考试的也会查出来)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
 
-- 查一下缺考的同学(左连接应用场景)
SELECT s.studentno,studentname,subjectno,StudentResult
FROM student s
LEFT JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult IS NULL
 
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
RIGHT JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno

 

ON和WHERE的区别：

  • ON（连接查询）是先筛选后关联；

  • WHERE（等值查询）是先关联后筛选。

自连接（了解）

自己的表和自己的表连接，核心思想是把一张表拆为两张一样的表即可。

-- 例1：需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中，查询父栏目名称和其他子栏目名称
-- 创建一个表
CREATE TABLE `category` (
  `categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
  `pid` INT(10) NOT NULL COMMENT '父id',
  `catagoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
  PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

-- 插入数据
INSERT INTO `category` (`categoryid`,`pid`,`catagoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');

-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT p.`catagoryName` AS '父栏目',s.`catagoryName` AS '子栏目'
FROM `category` AS p,`category` AS s
WHERE p.`categoryid`=s.`pid`

-- 例2：
-- 思考题:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON sub.subjectno = r.subjectno
 
-- 查询学员及所属的年级(学号,学生姓名,年级名)
SELECT studentno AS 学号,studentname AS 学生姓名,gradename AS 年级名称
FROM student s
INNER JOIN grade g
ON s.`GradeId` = g.`GradeID`
 
-- 查询科目及所属的年级(科目名称,年级名称)
SELECT subjectname AS 科目名称,gradename AS 年级名称
FROM SUBJECT sub
INNER JOIN grade g
ON sub.gradeid = g.gradeid
 
-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'

 

4.5 分页和排序

排序 ORDER BY

语法：

  • ORDER BY 语句用于根据指定的列对结果集进行排序；

  • ORDER BY 语句默认按照ASC升序对记录进行排序，降序DESC。

-- 查询 数据库结构-1 的所有考试结果(学号 学生姓名 科目名称 成绩)，并按 成绩降序 排序
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC

 

分页 LIMIT

语法：SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset 好处：用户体验，网络传输，查询压力 推导： 第一页 : limit 0,5 第二页 : limit 5,5 第三页 : limit 10,5 ...... 第N页 : limit (pageNo-1)*pageSize, pageSize [pageNo:页码,pageSize:单页面显示条数]

-- 每页显示5条数据
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='数据库结构-1'
ORDER BY StudentResult DESC , studentno
LIMIT 0,5
 
-- 查询 JAVA第一学年 课程成绩前10名并且分数大于80的学生信息(学号,姓名,课程名,分数)
SELECT s.studentno,studentname,subjectname,StudentResult
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno
WHERE subjectname='JAVA第一学年' AND StudentResult>=80
ORDER BY StudentResult DESC
LIMIT 0,10

 

4.6 子查询

  • 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句;

  • 嵌套查询可由多个子查询组成,求解的方式是由里及外;

  • 子查询返回的结果一般都是集合,故而建议使用IN关键字。

-- 查询 数据库结构-1 的所有考试结果（学号，科目编号，成绩），并且成绩降序排列
-- 方法一：使用连接查询
SELECT studentNo,r.subjectNo,StudentResult
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo`
WHERE subjectName = '数据库结构-1'
ORDER BY studentResult DESC;
 
-- 方法二：使用子查询（执行顺序：由里及外）
-- 先从subject表中查询 数据库结构-1 的科目编号，再根据科目编号从result表中查询考试结果
SELECT studentNo,subjectNo,StudentResult
FROM result
WHERE subjectNo=(
    SELECT subjectNo FROM `subject`
    WHERE subjectName = '数据库结构-1'
)
ORDER BY studentResult DESC;
 
-- 查询课程为 高等数学-2 且分数不小于80分的学生的学号和姓名
-- 方法一：使用连接查询
SELECT s.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE subjectName = '高等数学-2' AND StudentResult>=80
 
-- 方法二：使用连接查询+子查询
-- 分数不小于80分的学生的学号和姓名
SELECT r.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80
 
-- 在上面SQL基础上，添加需求：课程为 高等数学-2
SELECT r.studentNo,studentName
FROM student s
INNER JOIN result r
ON s.`StudentNo`=r.`StudentNo`
WHERE StudentResult>=80 AND subjectNo=(
    SELECT subjectNo
    FROM `subject`
    WHERE subjectName = '高等数学-2'
)
 
-- 方法三：使用子查询
-- 分步写简单sql语句，然后将其嵌套起来
SELECT studentNo,studentName
FROM student
WHERE studentNo IN(
    SELECT studentNo
    FROM result
    WHERE StudentResult>=80 AND subjectNo=(
        SELECT subjectNo
        FROM `subject`
        WHERE subjectName = '高等数学-2'
    )
)
 
/*
练习题目：
    查 C语言-1 的前5名学生的成绩信息（学号，姓名，分数）
    使用子查询，查询郭靖同学所在的年级名称
*/

 

4.7 分组和过滤

-- 查询不同课程的平均分，最高分，最低分
-- 前提：根据不同的课程进行分组
SELECT subjectname,AVG(studentresult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result AS r
INNER JOIN `subject` AS s
ON r.subjectno = s.subjectno
GROUP BY r.subjectno
HAVING 平均分>80;

 

  • WHERE：写在group by前面；

  • HAVING：放在分组后面的筛选，因为having是从前面筛选的字段再筛选，而where是从数据表中的>字段直接进行的筛选的

5. MySQL函数

5.1 常用函数

-- 常用函数
SELECT ABS(-8)        -- 绝对值
SELECT CEILING(9.9)    -- 向上取整
SELECT FLOOR(9.9)    -- 向下取整
SELECT RAND()        -- 随机数（0~1）
SELECT SIGN(-5)        -- 返回参数的符号

-- 字符串函数
SELECT CHAR_LENGTH('BTS FOREVER!')        -- 字符串长度
SELECT CONCAT('b','t','s')            -- 拼接字符串
SELECT INSERT('hello world!',1,5,'goodbye')     -- 插入，替换
SELECT UPPER('bts')                -- 转大写
SELECT LOWER('BTS')                -- 转小写
SELECT INSTR('bts','s')                -- 返回子串第一次出现的索引
SELECT REPLACE('hello world!','hello','goodbye')-- 替换
SELECT SUBSTR('hello world!',7,5)        -- 截取子串
SELECT REVERSE('hello')                -- 反转字符串

-- 时间和日期函数
SELECT CURRENT_DATE()        -- 获取当前日期
SELECT CURDATE()        -- 获取当前日期
SELECT NOW()            -- 获取当前日期和时间
SELECT LOCALTIME()        -- 获取当前日期和时间
SELECT SYSDATE()        -- 获取当前日期和时间
 
-- 获取年月日,时分秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- 系统信息函数
SELECT VERSION()        -- 版本
SELECT USER()            -- 用户

 

5.2 聚合函数（常用）

COUNT(), SUM(), AVG(), MAX(), MIN()……

-- 从含义上讲，count(1) 与 count(*) 都表示对全部数据行的查询。
-- count(字段) 会统计该字段在表中出现的次数，忽略字段为null的情况。即不统计字段为null的记录；
-- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null的记录；
-- count(1) 用1代表代码行，在统计结果的时候，包含字段为null的记录 。
SELECT COUNT(studentname) FROM student;
SELECT COUNT(*) FROM student;
SELECT COUNT(1) FROM student;  /*推荐*/
 
SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;

 

6. 事务

什么是事务（Transaction）

事务就是将一组SQL语句放在同一批次内去执行；如果一个SQL语句出错，则该批次内的所有SQL语句都将被取消执行；MySQL事务处理只支持InnoDB和BDB数据表类型。

事务的ACID原则

  • 原子性（Atomicity）：要么都成功，要么都失败

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

  • 一致性（Consistency）：事务前后的数据完整性要保证一致

    一个事务可以封装状态改变（除非它是一个只读的）。事务必须始终保持系统处于一致的状态，不管在任何给定的时间事务有多少。

    也就是说：如果事务是并发多个，系统也必须如同串行事务一样操作。其主要特征是保护性和不变性(Preserving an Invariant)，以转账案例为例，假设有五个账户，每个账户余额是100元，那么五个账户总额是500元，如果在这个5个账户之间同时发生多个转账，无论并发多少个，比如在A与B账户之间转账5元，在C与D账户之间转账10元，在B与E之间转账15元，五个账户总额也应该还是500元，这就是保护性和不变性。

  • 隔离性（Isolation）：并发访问时相互隔离

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

  • 持久性（Durability）：事务一旦提交就不可逆，被持久化到数据库中

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

事务之间互相影响

  • 脏读

    一个事务读取了另外一个事务未提交的数据。

  • 不可重复读

    指在一个事务内读取表中的某一行数据，多次读取结果不同。

  • 虚读（幻读）

    指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

执行事务

关闭事务自动提交 --> 开启一个事务 --> 成功则提交，失败则回滚 --> 开启事务自动提交

-- mysql是默认开启事务自动提交的
SET autocommit = 0        -- 关闭
SET autocommit = 1        -- 开启（默认）

-- 手动处理事务
SET autocommit = 0        -- 关闭事务自动提交
-- 事务开启
START TRANSACTION        -- 标记一个事务的开始，之后的sql语句都在同一事务内
-- 此处编写事务内的sql语句

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到之前的状态（失败）
ROLLBACK

-- 事务结束
SET autocommit = 1        -- 开启事务自动提交

-- 保存点（了解即可）
SAVEPOINT 保存点名称            -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名称    -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称        -- 撤销保存点

 

模拟转账案例

-- 转账案例：A向B转账50
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop

CREATE TABLE `account`(
  `id` INT(3) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `money` DECIMAL(9,2) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE = INNODB DEFAULT CHARSET = utf8

INSERT INTO `account`(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- 模拟事务
SET autocommit = 0;
START TRANSACTION;

UPDATE `account` SET money = money - 50 WHERE `name` = 'A';
UPDATE `account` SET money = money + 50 WHERE `name` = 'B';

COMMIT;
ROLLBACK;

SET autocommit = 1;

 

四种隔离级别？？？

7. 索引

参考链接：CodingLabs - MySQL索引背后的数据结构及算法原理

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构。

7.1 索引的分类

  • 主键索引 PRIMARY KEY

    作用：某一个属性组能唯一标识一条记录，主键不可重复（只能有一个列作为主键）

  • 唯一索引 UNIQUE KEY

    作用：避免同一个表中某数据列中的值重复，唯一索引可以重复（多个列都可以标识为唯一索引）

  • 常规索引 KEY/INDEX

    作用：快速定位特定数据

  • 全文索引 FullText

    作用：快速定位特定数据

基础语法：

/*
#方法一：创建表时
    　　CREATE TABLE 表名 (
                字段名1  数据类型 [完整性约束条件…],
                字段名2  数据类型 [完整性约束条件…],
                [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
                [索引名]  (字段名[(长度)]  [ASC |DESC])
                );
#方法二：CREATE在已存在的表上创建索引
        CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名
                     ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
#方法三：ALTER TABLE在已存在的表上创建索引
        ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                             索引名 (字段名[(长度)]  [ASC |DESC]) ;
                            
                            
#删除索引：DROP INDEX 索引名 ON 表名字;
#删除主键索引: ALTER TABLE 表名 DROP PRIMARY KEY;
#显示索引信息: SHOW INDEX FROM student;
*/
 
/*增加全文索引*/
ALTER TABLE `school`.`student` ADD FULLTEXT INDEX `studentname` (`StudentName`);
 
/*EXPLAIN : 分析SQL语句执行性能*/
EXPLAIN SELECT * FROM student WHERE studentno='1000';
 
/*使用全文索引*/
-- 全文搜索通过 MATCH() 函数完成。
-- 搜索字符串作为 against() 的参数被给定。搜索以忽略字母大小写的方式执行。对于表中的每个记录行，MATCH() 返回一个相关性值。即，在搜索字符串与记录行在 MATCH() 列表中指定的列的文本之间的相似性尺度。
EXPLAIN SELECT *FROM student WHERE MATCH(studentname) AGAINST('love');
 
/*
开始之前，先说一下全文索引的版本、存储引擎、数据类型的支持情况
MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
测试或使用全文索引时，要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
*/

 

7.2 测试索引

USE school;
-- 建表
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用户表' 

-- 插入数据
DELIMITER $$
CREATE PROCEDURE test() # 创建存储过程 学习while循环的用法
BEGIN
    DECLARE COUNT INT DEFAULT 0;
    DECLARE SUM INT DEFAULT 100000;
    WHILE COUNT < SUM DO
    INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
    VALUES(CONCAT('用户', COUNT), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
        SET COUNT = COUNT + 1;
    END WHILE;
END $$
DELIMITER ;

CALL test();

-- 无索引时
SELECT * FROM app_user WHERE `name` = '用户9999';    -- 耗时0.037 sec
SELECT * FROM app_user WHERE `name` = '用户9999';    -- 耗时0.037 sec
SELECT * FROM app_user WHERE `name` = '用户9999';    -- 耗时0.035 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';    -- rows=99396

-- 创建索引
CREATE INDEX id_app_user_name ON app_user(`name`);

-- 使用索引时
SELECT * FROM app_user WHERE `name` = '用户9999';    -- 耗时0 sec
SELECT * FROM app_user WHERE `name` = '用户9999';    -- 耗时0 sec
SELECT * FROM app_user WHERE `name` = '用户9999';    -- 耗时0 sec
EXPLAIN SELECT * FROM app_user WHERE `name` = '用户9999';    -- rows=1

 

索引准则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引

  • 小数据量的表建议不要加索引

  • 索引一般应加在查找条件的字段

8. 权限管理和备份

8.1 用户管理

SQLyog可视化管理

用户管理器 --> 删除用户/添加新用户

基本命令

/* 用户和权限管理 */
用户信息表：mysql.user
 
-- 刷新权限
FLUSH PRIVILEGES
 
-- 增加用户  CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
    - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
    - 只能创建用户，不能赋予权限。
    - 用户名，注意引号：如 'user_name'@'192.168.1.1'
    - 密码也需引号，纯数字密码也要加引号
    - 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
 
-- 重命名用户  RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
 
-- 设置密码
SET PASSWORD = PASSWORD('密码')    -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码')    -- 为指定用户设置密码
 
-- 删除用户  DROP USER kuangshen2
DROP USER 用户名
 
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
    - all privileges 表示所有权限
    - *.* 表示所有库的所有表
    - 库名.表名 表示某库下面的某表
 
-- 查看权限   SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
    -- 查看当前用户权限
    SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR CURRENT_USER();
 
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名    -- 撤销所有权限

 

测试

：

-- 创建用户
CREATE USER JungKook IDENTIFIED BY '123456'

-- 修改当前用户密码
SET PASSWORD = PASSWORD('123456')

-- 修改指定用户密码
SET PASSWORD FOR JungKook = PASSWORD('111111')

-- 重命名用户
RENAME USER JungKook TO Cooky

-- 用户授权
-- ALL PRIVILEGES，该权限拥有除授权外的所有权限
GRANT ALL PRIVILEGES ON *.* TO Cooky

-- 查询权限
SHOW GRANTS FOR Cooky
SHOW GRANTS FOR root@localhost

-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM Cooky

-- 删除用户
DROP USER Cooky

 

8.2 MySQL备份

数据库备份必要性

  • 保证重要数据不丢失

  • 数据转移

MySQL数据库备份方法

  • 直接拷贝数据库文件和相关配置文件（mysql安装目录下的data文件）

  • 数据库管理工具，如SQLyog

    • 在想要导出的表或数据库中右键，然后选择备份或导出

      image-20201209094702451

  • 使用命令行导出，mysqldump备份工具

    # mysqldump -h主机 -u用户 -p密码 数据库 表 >物理磁盘位置/文件名
C:\Windows\system32>mysqldump -hlocalhost -uroot -p123456 shop account >E:\Java\Note\a.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 导出多张表
# mysqldump -h主机 -u用户 -p密码 数据库 表1 表2 表3 >物理磁盘位置/文件名

# 导出数据库
# # mysqldump -h主机 -u用户 -p密码 数据库 >物理磁盘位置/文件名

# 导入sql
# 方式一：需先登录
# 1. 登录，切换到指定数据库
C:\Windows\system32>mysql -uroot -p123456
mysql> use school
# 2. 导入文件，source 物理磁盘位置/文件名
mysql> source E:\Java\Note\a.sql

# 方式二：导入时登录
mysql -uroot -p123456 数据库< 物理磁盘位置/文件名

     

9. 规范数据库设计

9.1 为什么要规范数据库设计

当数据库比较复杂的时候，设计数据库十分必要！！！

糟糕的数据库设计 :

  • 数据冗余，存储空间浪费

  • 数据更新和插入的异常

  • 程序性能差

良好的数据库设计 :

  • 节省数据的存储空间

  • 能够保证数据的完整性

  • 方便进行数据库应用系统的开发

需求分析 --> 概要设计

9.2 三大范式

数据不规范的问题：

  • 信息重复

  • 更新异常

  • 插入异常：无法正常显示信息

  • 删除异常：丢失有效的信息

第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。

确保每列保持原子性

第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）

第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

确保数据表中的每一列数据都和主键直接相关，而不能间接相关

参考链接：https://www.cnblogs.com/wsg25/p/9615100.html

10. JDBC（重点）

10.1 数据库驱动

应用程序无法直接与数据库进行交互，需要借由数据库厂商提供的数据库驱动使用数据库。

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。

包：java.sql, javax.sql, mysql-connector-java-5.1.47.jar

10.2 JDBC测试

创建数据库

-- 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, 'zhangsan', '111', 'zs@qq.com', '1998-01-01'),
(2, 'lisi', '222', 'ls@qq.com', '1998-02-03'),
(3, 'wangwu', '333', 'ww@qq.com', '1998-05-05');

 

访问数据库

package com.wang.mysql;

import java.sql.*;

// 我的第一个JDBC程序
public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1. 加载驱动
        /*
        DriverManager.registerDriver(new com.mysql.jdbc.Driver());
        Driver类中有一个静态代码块，在类加载的时候会注册驱动
         */
        Class.forName("com.mysql.jdbc.Driver");

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

        // 3. 连接成功后，创建数据库对象（connection代表数据库）
        Connection connection = DriverManager.getConnection(url, username, password);
        /*
        connection.commit();        // 事务提交
        connection.rollback();      // 事务回滚
        connection.getAutoCommit(); // 事务自动提交
         */


        // 4. 创建执行SQL的对象
        Statement statement = connection.createStatement();
        /*
        statement.executeQuery(sql);    // 查询操作，返回 ResultSet
        statement.execute();            // 可以执行任何SQL
        statement.executeUpdate();      // 更新、插入、删除，返回一个受影响的行数
        statement.executeBatch();       // 执行多条SQL语句
         */

        // 5. 使用 执行SQL的对象 去执行SQL，可能需要查看返回结果
        String sql = "SELECT * FROM users";

        ResultSet resultSet = statement.executeQuery(sql);

        while (resultSet.next()){
            System.out.println("=========================");
            System.out.println("id = " + resultSet.getObject("id"));
            System.out.println("name = " + resultSet.getObject("name"));
            System.out.println("password = " + resultSet.getObject("password"));
            System.out.println("email = " + resultSet.getObject("email"));
            System.out.println("birthday = " + resultSet.getObject("birthday"));
        }
        /*
        获得指定的数据类型：
        resultSet.getObject();  // 在不知道列类型的情况下使用
        resultSet.getInt();
        resultSet.getFloat();
        resultSet.getDate();
        resultSet.getString();
         */

        /*
        遍历：
        resultSet.beforeFirst();
        resultSet.afterLast();
        resultSet.next();
        resultSet.previous();
        resultSet.absolute(row);
         */

        // 6. 释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}
/*结果：
=========================
id = 1
name = zhangsan
password = 111
email = zs@qq.com
birthday = 1998-01-01
=========================
id = 2
name = lisi
password = 222
email = ls@qq.com
birthday = 1998-02-03
=========================
id = 3
name = wangwu
password = 333
email = ww@qq.com
birthday = 1998-05-05
*/

 

步骤：

  1. 加载驱动；

  2. 连接数据库 DriverManager；

  3. 获得执行SQL的对象 Statement；

  4. 获得返回的结果集；

  5. 释放连接。

10.3 statement对象

JDBC中的statement对象用于向数据库发送SQL语句，若想对数据库进行增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

  • executeUpdate()方法：用于向数据库发送增、删、改的SQL语句，此方法执行完成之后将会返回一个整数（增删改语句导致数据库数据发生变化的行数）；

  • executeQuery()方法：用于向数据库发送查询语句，返回代表查询结果的ResultSet对象。

案例分析

编写工具类：

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

 

package com.wang.mysql.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.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 (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

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

    // 3. 释放连接
    public static void releaseConnection(Connection conn, Statement st, ResultSet re){
        if (re != null){
            try {
                re.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();
            }
        }
    }
}

 

测试 - Insert

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection();   // 获取数据库连接
            st = conn.createStatement();        // 获得SQL的执行对象

            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`)" +
                    "VALUES(4, 'chenliu', '444', 'cl@qq.com', '1998-07-10');";

            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("Insert Successfully!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}

 

测试 - Delete

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

        Connection conn = null;
        Statement st = null;
        ResultSet re = 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("Delete Successfully!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}

 

测试 - Update

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection();   // 获取数据库连接
            st = conn.createStatement();        // 获得SQL的执行对象

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

            int i = st.executeUpdate(sql);
            if (i > 0){
                System.out.println("Update Successfully!");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}

 

测试 - Select

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

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

        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection();   // 获取数据库连接
            st = conn.createStatement();        // 获得SQL的执行对象

            String sql = "SELECT * FROM users WHERE id=1";

            re = st.executeQuery(sql);          // 返回结果集
            while (re.next()){
                System.out.println("name: " + re.getString("name"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}

 

SQL注入问题

SQL注入是将Web页面的原URL、表单域或数据包输入的参数，修改拼接成SQL语句，传递给Web服务器，进而传给数据库服务器以执行数据库命令。

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class SqlInject {
    public static void main(String[] args) {
        // 正常登录
        // login("JungKook", "111");

        // SQL注入攻击
        // login(" ' or '1=1", "111");
        login(" ' or '1=1", " ' or '1=1");
    }

    public static void login(String username, String password){
        Connection conn = null;
        Statement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection();   // 获取数据库连接
            st = conn.createStatement();        // 获得SQL的执行对象

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

            re = st.executeQuery(sql);
            while (re.next()){
                System.out.println("=====================");
                System.out.println(re.getString("name"));
                System.out.println(re.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}
/* 结果：
=====================
JungKook
111
=====================
lisi
222
=====================
wangwu
333
*/

 

10.5 PreparedStatement对象

可以防止SQL注入；效率更好！

Insert

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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

        Connection conn = null;
        PreparedStatement st = null;

        try {
            conn = JdbcUtils.getConnection();

            // 与createStatement()的区别部分
            // 使用占位符?
            String sql = "INSERT INTO users(`id`,`name`,`password`,`email`,`birthday`) VALUES(?,?,?,?,?)";
            st = conn.prepareStatement(sql);        // 预编译SQL，先写入SQL，但不执行
            // 手动给参数赋值
            st.setInt(1, 4);
            st.setString(2, "chenliu");
            st.setString(3, "444");
            st.setString(4, "cl@qq.com");
            // 注意: 数据库时间     sql.Date    java.sql.Date(时间戳)
            //      Java         utils.Date   Date().getTime()获得时间戳
            st.setDate(5, new java.sql.Date(new Date().getTime()));

            // 执行
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("Insert Successfully!");
            }

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

 

Delete

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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

        try {
            conn = JdbcUtils.getConnection();

            // 与createStatement()的区别部分
            // 使用占位符?
            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("Delete Successfully!");
            }

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

 

Update

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Date;

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

        try {
            conn = JdbcUtils.getConnection();

            // 与createStatement()的区别部分
            // 使用占位符?
            String sql = "UPDATE users SET `name`=? WHERE id=?";
            st = conn.prepareStatement(sql);        // 预编译SQL，先写入SQL，但不执行
            // 手动给参数赋值
            st.setString(1, "Cooky");
            st.setInt(2, 1);

            // 执行
            int i = st.executeUpdate();
            if (i > 0){
                System.out.println("Update Successfully!");
            }

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

 

Select

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

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

        try {
            conn = JdbcUtils.getConnection();

            String sql = "SELECT * FROM users WHERE `name`=?";
            st = conn.prepareStatement(sql);
            st.setString(1, "Cooky");
            re = st.executeQuery();
            while (re.next()){
                System.out.println(re.getString("name"));
                System.out.println(re.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}

 

SQL注入

PreparedStatement

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils;

import java.sql.*;

public class SqlInjectPrepared {
    public static void main(String[] args) {
        // 正常登录
        // login("Cooky", "111");

        // SQL注入攻击
        // login("' ' or '1=1'", "111");
        login("' ' or '1=1'", "' ' or '1=1'");
    }

    public static void login(String username, String password){
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet re = null;

        try {
            conn = JdbcUtils.getConnection();   // 获取数据库连接
            String sql = "SELECT * FROM users WHERE `name`=? AND `password`=?";
            st = conn.prepareStatement(sql);        // 获得SQL的执行对象
            st.setString(1, username);
            st.setString(2, password);
            re = st.executeQuery();
            while (re.next()){
                System.out.println("=====================");
                System.out.println(re.getString("name"));
                System.out.println(re.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils.releaseConnection(conn, st, re);
        }
    }
}

 

10.6 使用IDEA连接数据库

 

 

10.7 JDBC操作事务

基本语句

// 关闭数据库的自动提交，会自动开启事务
conn.setAutoCommit(false);
// 业务完毕，提交事务
conn.commit();
// 业务失败（默认会回滚），显式回滚事务
conn.rollback();

 

10.8 DBCP/C3P0连接池

开源数据源：DBCP、C3P0、Druid（阿里巴巴）

使用这些数据库连接池后，项目开发中就不需要编写数据库连接代码了！

DBCP

jar包：commons-dbcp2-2.8.0.jar，commons-pool2-2.9.0.jar，commons-logging-1.2.jar（dbcp2以后需要此jar包）

属性文件

# 连接设置
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456

# 初始化连接
initialSize=10

# 最大连接数量
maxActive=50

# 最大空闲连接
maxIdle=50

# 最小空闲连接
minIdle=5

# 超时等待时间，以毫秒为单位
maxWait=60000

connectionProperties=useUnicode=true;characterEncoding=utf8
defaultAutoCommit=true
defaultReadOnly=
defaultTransactionIsolation=READ_UNCOMMITTED

 

工具类

package com.wang.mysql.utils;

import org.apache.commons.dbcp2.BasicDataSourceFactory;

import javax.sql.DataSource;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils_DBCP {

    private static DataSource dataSource = null;

    static {
        try{
            InputStream in = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(in);

            // 1. 创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    // 2. 获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // 3. 释放连接
    public static void releaseConnection(Connection conn, Statement st, ResultSet re){
        if (re != null){
            try {
                re.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();
            }
        }
    }
}

 

测试

package com.wang.mysql;

import com.wang.mysql.utils.JdbcUtils_DBCP;

import java.sql.*;

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

        try {
            conn = JdbcUtils_DBCP.getConnection();      // 修改为DBCP的工具类

            String sql = "SELECT * FROM users WHERE `name`=?";
            st = conn.prepareStatement(sql);
            st.setString(1, "Cooky");
            re = st.executeQuery();
            while (re.next()){
                System.out.println(re.getString("name"));
                System.out.println(re.getString("password"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            JdbcUtils_DBCP.releaseConnection(conn, st, re);      // 修改为DBCP的工具类
        }
    }
}

 

C3P0与上述类似（jar包：c3p0，mchange-commons-java）。

附：狂神b站视频链接

posted @ 2021-03-23 23:37  甜了酒了果  阅读(76)  评论(0)  编辑  收藏  举报