MySQL笔记整理

最新推荐文章于 2023-02-25 20:24:29 发布
最新推荐文章于 2023-02-25 20:24:29 发布
阅读量314 收藏
点赞数 1
文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_48171371/article/details/110641296
版权

一、认识MySQL

1、认识

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。

MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个性化的需要对其进行修改。

MySQL因为其速度、可靠性和适应性而备受关注。大多数人都认为在不需要事务化处理的情况下，MySQL是管理内容最好的选择。

（百度百科）

2、下载

目前：5.7版本用的人比较多且较为稳定

下载地址

尽量下载ZIP压缩包，解压后配置一下就能用了，删除也方便，如果是安装的删除就比较麻烦了。

在这里插入图片描述

有位博主安装教程还不错 教程

二、操作数据库

1.操作数据库 （了解）

1.1 创建

-- CREATE DATABASE [IF NOT EXISTS] 数据库名;
CREATE DATABASE [IF NOT EXISTS] student;

1.2 删除

-- DROP DATABASE [IF EXISTS] 数据库名;
DROP DATABASE [IF EXISTS] student;

1.3 使用数据库

-- USE `数据库名`;
USE `school`;

1.4 查看数据库

SHOW DATABASES -- 查看所有的数据库

2.列类型

2.1 数值

类型名说明字节数
tinyint十分小的数据1
smallint较小的数据2
mediumint中等大小的数据3
int标准的整数4
bigint较大的数据8
float单精度浮点数4
double双精度浮点数8
decimal字符串类型的浮点数（金融计算时候使用）不定

2.2 字符串

类型名说明范围
char字符串固定小小的0-255
varchar可变字符串0-65535
tinytest微型文本2^8-1
test文本串(保存大文本)2^16 -1

2.3 时间日期

类型名格式说明
dateYYYY-MM-DD日期格式
timeHH:mm:ss时间格式
datetimeYYYY-MM-DD HH:mm:ss最常用的时间格式
timestamp（时间戳）1970.1.1到现在的毫秒数
year表示年份

2.4 null

没有值，未知

不要用NULL进行运算，结果为NULL

3.字段类型

3.1 Unsigned

  • 无符号整数
  • 声明了该列的值就不能为负数（若为负数，则全部自动变成0）

3.2 zerofill

  • 0填充的
  • 不足位数，使用0来填充
  • 例如 int(3)，若插入数据为1，则自动变成 001

注意：int(3) 并不是指定int的长度，而是指定填充的位数

3.3 自增

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

3.4 非空 NULL not null

  • 如果设置为NOT NULL，如果没有给它赋值，则会报错！
  • NULL，如果不填写值，默认值就是NULL

3.5 默认

  • 设置默认的值！

4.创建数据库的表（重点）

/*
CREATE TABLE [IF NOT EXISTS] `表名`(
  `字段名` 列属性 [约束],
  `字段名` 列属性 [约束],
  `字段名` 列属性 [约束],
  [PRIMARY KEY(`主键字段名`)]
)[约束];
*/
-- COMMENT '注释'
-- DEFAULT '默认值'
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;

5.引擎（了解）

MYISAMINNODB
事务支持不支持支持
数据行锁定（跟锁相关）不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为MYISAM的2倍

常规使用操作：

  • MYISAM 节约空间，速度较快
  • INNODB 安全性高，事务的处理，多表多用户的操作

5.修改表字段

5.1 查看表结构

-- SHOW CREATE DATABASE 数据库名;
SHOW CREATE DATABASE school; -- 查看创建数据库的语句
--SHOW CREATE TABLE `表名`;
SHOW CREATE TABLE `student`;-- 查看创建表的语句

-- DESC `表名`; 
DESC `student`; -- 查看表结构，等价于 describe `student`;

5.2 修改

-- 修改表名：ALTER TABLE `旧表名` RENAME AS `新表名`;
ALTER TABLE `teacher` RENAME AS `teach`;
-- 增加表的字段：ALTER TABLE `表名` ADD 字段名 列类型[];
ALTER TABLE `teach` ADD age INT(3);

-- 修改表的字段（重命名，修改约束）
-- ALTER TABLE `表名` MODIFY 字段名 列属性[]; 
ALTER TABLE `teach` MODIFY age VARCHAR(11); -- 修改约束
-- ALTER TABLE `表名` CHANGE 旧字段名字 新字段名字 列属性[]
ALTER TABLE `teach` CHANGE age age1 INT(1); -- 字段重命名
-- 结论
-- change用来字段重命名，不能修改字段类型和约束
-- modify不用来字段重命名，只能修改字段类型和约束

-- 删除表的字段：
-- ALTER TABLE `表名` DROP 字段名;
ALTER TABLE `teach` DROP age1;

5.3 删除

-- 删除表
-- DROP TABLE [IF EXISTS] 表名;
DROP TABLE IF EXISTS teach;

注意点：

`` 字段名，必须用这个包裹起来！

注释： – /**/

sql关键字大小写不敏感，建议用小写！

所有符号都用英文

三、MySQL数据管理

1、外键（了解）

外键名一般为 FK_ 开头

方式一：在创建表时，增加约束（较为麻烦）
/*
KEY `外键名`(`引用表的字段名`),    -- 定义一个外键
CONSTRAINT `外键名` FOREIGN KEY(`引用表的字段名`) REFERENCES `被引用表名`(`被引用表字段名`)  -- 添加约束
*/

--删除表
DROP TABLE IF EXISTS `student`;
DROP TABLE IF EXISTS `grade`;

-- 被引用表
CREATE TABLE `grade`(
  `id` INT(5) NOT NULL AUTO_INCREMENT COMMENT '年级id',
  `name` VARCHAR(10) COMMENT '姓名',
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

-- 引用表
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `gid` INT(5) NOT NULL COMMENT '年级id',
    `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
    PRIMARY KEY(`id`),
    KEY `FK_gid`(`gid`),
    CONSTRAINT `FK_gid` FOREIGN KEY(`gid`) REFERENCES `grade`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
方式二：创建表成功后，增加约束
CREATE TABLE `grade`(
  `id` INT(5) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10),
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS `student`(
    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
    `gid` INT(5) NOT NULL,
    `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;
-- 表中删除添加外键语句
ALTER TABLE `student` 
ADD CONSTRAINT `KF_gid` FOREIGN KEY(`gid`) REFERENCES `grade`(`id`);

/* 语法
ALTER TABLE `外键所在的表` 
ADD CONSTRAINT `外键名` FOREIGN KEY(`外键字段`) REFERENCES `被引用表`(`主键字段`);
*/

在这里插入图片描述

添加外键后

在这里插入图片描述

Cannot add foreign key constraint的可能原因

1.主键和外键的数据类型、数据长度或约束不同

2.某个表里已经有记录了

3.两个表的引擎不一样，查看表引擎：show table status from 数据库名 where name=‘表名’;

4.外键不能为主键

5.被参考的字段必须为主键

以上操作都是物理外键，是数据库级别的外键，不建议使用！（避免数据库太多造成困扰，例如删表）

最佳实现：

  • 数据库就是单纯的表，只有行（数据）和列（字段）
  • 当多张表项使用外键时，可以用程序去实现

2、DML语言（全部记住）

DML语言：数据操作语言

  • Insert
  • update
  • dalete
-- 用到的表
CREATE TABLE `grade`(
  `id` INT(5) NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(10),
  PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

2.1 添加

INSERT INTO 表名([字段2,字段2,字段3,…]) VALUES(‘值1’,‘值1’,‘值1’,…),(‘值1’,‘值1’,‘值1’,…),…;

-- 插入指定字段的值（其他字段必须可以为NULL或者有默认值或自增）
INSERT INTO `grade`(`name`) VALUES('大四');

-- 插入值且省略字段（此时必须与表中的字段一一对应且顺序一致）
INSERT INTO `grade` VALUES('2','大三');

-- 插入多行的值
INSERT INTO `grade` 
VALUES('3','大二'),
      ('4','大一');

2.2 修改

UPDATE 表名 SET column_name1=‘value1’[,SET column_name2=‘value2’,…] WHERE [筛选条件];

-- 修改所有行的值
UPDATE `grade` SET `name`='研一';

-- 修改指定行的值
UPDATE `grade` SET `name`='研一' WHERE `id`=1;

重点：筛选条件

2.3 删除

DELETE FROM 表名 WHERE [筛选条件];

-- 删除所有数据(不同于清空表，不建议使用)
-- 例如删除后自增没有还原。删除前表的最后一个自增为10，删除后插入第一个数据，该值变成11，而不是从1开始。
DELETE FROM `grade`;

-- 删除指定行（一个元组）
DELETE FROM `grade` WHERE id='5';

TRUNCATE 命令

TRUNCATE TABLE 表名;(该表不能被引用)

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

-- 清空grade表
TRUNCATE `grade`;

delete 和 truncate 的区别

  • 相同：都能删除数据，都不会删除表的结构
  • 不同：
    • TRUNCATE 重新设置 自增列 计数器会归零
    • TRUNCATE 不会影响事务
CREATE TABLE `test`(
  `id` INT(3) AUTO_INCREMENT,
  `coll` VARCHAR(5) NOT NULL,
  PRIMARY KEY(`id`)
);
INSERT INTO `test`(`coll`) VALUES('aa'),('bb'),('cc'); --插入完查看表

DELETE FROM `test`; -- 删除完重新执行插入，再查看表

TRUNCATE TABLE `test`;-- 清空完重新执行插入，再查看表

了解：DELETE删除的问题,重启数据库，现象：

  • InnoDB 自增列会从1开始（存在内存当中，断电即失）
  • MyISAM 继续从上一个子增量开始（存在文件中，不会丢失）

四、DQL查询数据（核心！）

1、DQL

(Data Query Language: 数据查找语言)

  • 所有的查询操作都用它。SELECT
  • 数据库中最核心的语言，最重要的语句

SELECT完整语法

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

-- 注意[]代表可选 {}代表必选

用到的数据库以及表

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;-- 创建学生表
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `loginpwd` VARCHAR(20) DEFAULT NULL,
    `studentname` VARCHAR(20) DEFAULT NULL COMMENT '学生姓名',
    `sex` TINYINT(1) DEFAULT NULL COMMENT '性别，0或1',
    `gradeid` INT(11) DEFAULT NULL COMMENT '年级编号',
    `phone` VARCHAR(50) NOT NULL COMMENT '联系电话，允许为空',
    `address` VARCHAR(255) NOT NULL COMMENT '地址，允许为空',
    `borndate` DATETIME DEFAULT NULL COMMENT '出生时间',
    `email` VARCHAR (50) NOT NULL COMMENT '邮箱账号允许为空',
    `identitycard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
    PRIMARY KEY (`studentno`),
    UNIQUE KEY `identitycard`(`identitycard`),
    KEY `email` (`email`)
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `grade`;
CREATE TABLE `grade`(
	`gradeid` INT(11) NOT NULL AUTO_INCREMENT COMMENT '年级编号',
  `gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
    PRIMARY KEY (`gradeid`)
) ENGINE=INNODB AUTO_INCREMENT = 6 DEFAULT CHARSET = utf8;

-- 创建科目表
DROP TABLE IF EXISTS `subject`;
CREATE TABLE `subject`(
	`subjectno`INT(11) NOT NULL AUTO_INCREMENT COMMENT '课程编号',
    `subjectname` VARCHAR(50) DEFAULT NULL COMMENT '课程名称',
    `classhour` INT(4) DEFAULT NULL COMMENT '学时',
    `gradeid` INT(4) DEFAULT NULL COMMENT '年级编号',
    PRIMARY KEY (`subjectno`)
)ENGINE = INNODB AUTO_INCREMENT = 19 DEFAULT CHARSET = utf8;

-- 创建成绩表
DROP TABLE IF EXISTS `result`;
CREATE TABLE `result`(
	`studentno` INT(4) NOT NULL COMMENT '学号',
    `subjectno` INT(4) NOT NULL COMMENT '课程编号',
    `examdate` DATETIME NOT NULL COMMENT '考试日期',
    `studentresult` INT (4) NOT NULL COMMENT '考试成绩',
    KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','text123@qq.com','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','text111@qq.com','123456199001011233');

-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);

2、指定查询字段

SELECT 字段1,… from 表

-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');

-- ------------------------------------------------------------------------------------------
-- 查询所有
SELECT * FROM `student`;

-- 查询指定字段
SELECT `studentno`,`studentname` FROM `student`;

-- 给查询结果起别名（表也可以起别名）
SELECT `studentno` AS 姓名,`studentname` AS 学号 FROM `student`;

-- 函数
SELECT CONCAT('姓名：',`studentname`) AS 新名字 FROM `student`;

去重

DISTINCT 关键字

SELECT DISTINCT `studentno` FROM `result`;

其他好玩的

-- 查看系统版本(函数)
SELECT VERSION();

-- 用来计算（表达式）
SELECT 100*3-5 AS 计算结果;

-- 计算步长(变量)
SELECT @@auto_increment_increment;

-- 所有学生成绩 +1分
SELECT `studentno`,`studentresult`+1 AS 加分后 FROM `result`;

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

3、where 条件子句

作用：检索数据中符合条件的值

搜索的条件是由一个或多个表达式组成！结果为布尔值

逻辑运算符

运算符语法描述
and &&a and b a&&b逻辑与，两个都是真，结果为真
or ||a or b a||b逻辑或，其中一个为真，结果为真
Not !not a ! a逻辑非，真为假，假为真

尽量使用英文字母

-- 查询成绩在95-100之间
SELECT studentno,studentresult FROM result WHERE studentresult>=95 AND studentresult <=100;
SELECT studentno,studentresult FROM result WHERE studentresult>=95 && studentresult <=100;
-- 模糊查询
SELECT studentno,studentresult FROM result WHERE studentresult BETWEEN 95 AND 100;

-- or 与 and tongli

-- 查询学号不等于1000的学生
-- 1.查询学号等于1000的学生
SELECT `studentno` FROM student WHERE `studentno`=1000;
-- 2.条件子句结果取反
SELECT `studentno` FROM student WHERE NOT `studentno`=1000;

模糊查询

运算符语法描述
IS NULLa IS NULL如果a为NULL，结果为真
IS NOT NULLa IS NOT NULL如果a不是NULL，结果为真
BETWEEN ANDa BETWEEN b AND ca在[b,c]内，结果为真
LIKEa LIKE ‘b’SQL匹配，如果a匹配到b，则结果为真
INa in (a1,a2,a3,…)a在{a1，a2，a3…}中任意一个，则结果真
-- IS NOT NULL
SELECT * FROM `subject`
WHERE `subjectname` IS NOT NULL;

-- IS NULL;
SELECT * FROM `subject`
WHERE `subjectname` IS NULL;

-- BETWEEN AND
SELECT * FROM `subject`
WHERE `subjectno` BETWEEN 1 AND 2;

-- LIKE  通配符 %表示0到任意个字符   _表示一个字符
SELECT * FROM `subject`
WHERE `subjectname` LIKE '高等数学%';
SELECT * FROM `subject`
WHERE `subjectname` LIKE '_等数学-1';

-- IN
SELECT * FROM `subject`
WHERE `subjectno` IN(1,2);

4、连表查询

在这里插入图片描述

思路

  • 分析需求，分析查询的字段来自哪几张表（连接查询）

  • 确定使用哪种连接查询（7种）

  • 确定交叉点（两个表种哪些数据是相同的，可以当作用来连接条件的）

  • 判断条件 [表1.]字段1 = [表2.]字段2

    (当有字段名一样是，需给表起别名，并且字段的使用都必须指明是哪张表！)

-- 查询学生学号、姓名和成绩
SELECT `studentno` 学号,`studentname` 姓名,`studentresult` 成绩
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`;
-- 报错：Column 'studentno' in field list is ambiguous（因为两张表都有'studentno'，所以必须指定是哪张表的）

-- 正确写法：
SELECT s.`studentno` 学号,`studentname` 姓名,`studentresult` 成绩
FROM `student` s
LEFT JOIN `result` r
ON s.`studentno` = r.`studentno`;
操作描述
Inner join如果表中至少有一个匹配，就返回行
left join返回左表中所有的行；如果右表有匹配则连接，若无则值为NULL
right join返回右表中所有的行；如果左表有匹配则连接，若无则值为NULL

自连接（了解）

自己的表和自己的表连接，核心：把一张表拆成两张表

用到的表

CREATE TABLE category (
	categoryid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题ID',
	pid INT(10) NOT NULL COMMENT '父ID',
	categoryName VARCHAR(50) NOT NULL COMMENT '主题名字',
	PRIMARY KEY(categoryid)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET = utf8;

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

父表（pid为1，即pid!=categoryid）

categoryidcategoryName
2信息技术
3软件开发
5美术设计

子表(pid=categoryid)

pidcategoryidcategoryName
34数据库
28办公信息
36web开发
57ps技术

操作：查询父类对应的子类关系

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术设计ps技术
SELECT a.`categoryName` 父表,b.`categoryName` 子表
FROM `category` a,`category` b
WHERE a.`categoryid` = b.`pid`;

5、分页和排序

排序
-- 排序：升序ASC  降序DESC
-- ORDER BY 通过那个字段排序  怎么排
-- 用到的表在  四、1
-- 按成绩降序排序
SELECT * FROM result
ORDER BY `studentresult` DESC;

分页

为什么要分页？

1.缓解数据库压力

2.给人更好的体验（瀑布流：一直往下刷刷不完）

-- limit 起始位置,数据条数
-- 第一页 limit 0,3  0 = (1-1)*pageSize
-- 第二页 limit 3,3  3 = (2-1)*pageSize
-- 第n页  limit x,3  x = (n-1)*pageSize
-- [pageSize 每页数据的行数]
-- [limit x,y 是从x+1位置取y条数据（mysql索引从1开始）]
-- [数据总数/pageSize = 总页面数]

-- 按成绩降序排序
SELECT * FROM result
LIMIT 0,3;

SELECT * FROM result
LIMIT 3,6;

6、子查询

where 字段 = (select语句)

本质：在where语句中嵌套一个子查询语句

-- 用到的表在  四、1
-- 查询课程为 高等数学-1 且分数不小于80分的同学
-- 方式一
SELECT s.`studentno`,`studentresult`,`studentname`,`subjectname`
FROM `result` r
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `studentresult`>=80 AND `subjectname`='高等数学-1';

-- 方式二
-- 分数不小于80分的同学
SELECT s.`studentno`,`studentresult`,`studentname`
FROM `result` r
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80;
-- 在上面的基础上增加 查询课程为 高等数学-1
SELECT s.`studentno`,`studentresult`,`studentname`
FROM `result` r
INNER JOIN `student` s
ON r.`studentno`=s.`studentno`
WHERE `studentresult`>=80 AND r.`subjectno` = (
	SELECT `subjectno` FROM `subject` 
	WHERE `subjectname`='高等数学-1'
);

-- 方式三：
SELECT `studentno`,`studentname` FROM `student`
WHERE `studentno`=(
	SELECT `studentno` FROM `result`
	WHERE `studentresult`>=80 AND `subjectno` =(
		SELECT `subjectno` FROM `subject`
		WHERE `subjectname`='高等数学-1'
	)
);

-- 子查询中表的字段无法显示在结果集上！

五、MySQL函数

[MySQL官网 函数]https://dev.mysql.com/doc/refman/8.0/en/sql-function-reference.html)

1、常用函数（并不常用）

-- 数学运算
SELECT ABS(-5); -- 返回绝对值
SELECT CEILING(5.1); -- 向上取整
SELECT FLOOR(5.9);  -- 向下取整
SELECT RAND(); -- 返回一个[0,1) 之间的随机数
SELECT SIGN(-10); -- 判断一个数的符号 0返回0，整数返回1，负数返回-1

-- 字符串函数
SELECT CHAR_LENGTH('Hello World!'); -- 字符串长度
SELECT CONCAT('Hello ','World!'); -- 拼接字符串
SELECT INSERT('Hello world!',7,5,'SQL'); -- 插入，从某个位置开始插入长度
SELECT LOWER('Hello World!'); -- 变成小写
SELECT UPPER('Hello World!'); -- 变成大写
SELECT INSTR('Hello World!','or'); -- 返回子串第一次出现的索引
SELECT REPLACE('Hello World!','World','sql'); -- 将指定的字符串用另外一个字符串替换
SELECT SUBSTR('Hello World!',6); -- 返回指定子串（4个函数重载）
SELECT REVERSE('Hello World!'); -- 反转字符串
-- 例子：查询姓张的同学的名字，并把张改为璋
SELECT REPLACE(`studentname`,'张','璋') 'name'
FROM student
WHERE `studentname` LIKE '张%';

-- 时间日期(记住)
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());

2、聚合函数（常用）

函数名称描述
COUNT()计数
SUM()求和
AVG()求平均值
MAX()求最大值
MIN()求最小值
……
SELECT COUNT(`studentno`) FROM `result`; -- count(字段) 如果值为null会忽略
SELECT COUNT(*) FROM `result`; -- 不会忽略null值 本质：计算行数
SELECT COUNT(1) FROM `result`; -- 不会忽略null值 本质：计算行数

SELECT `subjectname`,COUNT(`subjectname`)
FROM `result` r 
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
GROUP BY `subjectname` -- 分组
HAVING AVG(`studentresult`)>80 -- 分组后的筛选

拓展：使用MD5加密

  • MD5信息摘要算法（英语：MD5 Message-Digest Algorithm），一种被广泛使用的密码散列函数，可以产生出一个128位（16字节）的散列值（hash value），用于确保信息传输完整一致。

  • 1996年后该算法被证实存在弱点，可以被加以破解，对于需要高度安全性的数据，专家一般建议改用其他算法，如SHA-2。2004年，证实MD5算法无法防止碰撞（collision），因此不适用于安全性认证，如SSL公开密钥认证或是数字签名等用途。

网上一些MD5密码破解实际上都是利用数据字典。

-- 创建表并插入数据
CREATE TABLE `testmd5`(
	`id` INT(4) NOT NULL,
	`name` VARCHAR(20) NOT NULL,
	`pwd` VARCHAR(50) NOT NULL,
	PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO `testmd5` VALUES(1,'zhangsan','123456'),(2,'lisi','123asa6'),(3,'wangwu','4sad26');

-- 此时数据是明文的（即未加密）
-- 使用md5加密
UPDATE `testmd5` SET `pwd` = MD5(pwd);

-- 在插入数据时使用md5加密

INSERT INTO `testmd5` VALUES(6,'wanglihong',MD5('123.com'));

-- 此算法一般的不可破解的
-- 当用户传入密码后，将该密码进行加密后与原来数据库中加密后的密码比较，即可知道密码是否正确
SELECT * FROM `testmd5` WHERE `pwd`=MD5('123.com');

六、事务

1、什么是事务

要么都成功，要么都失败

一组SQL：

  • SQL执行 A给B转账 A 1000 -->200 B 200
  • 中间如果出现程序中断（如果不是事务）：A少了200，而B没有收到。所以需要解决该问题
  • SQL执行 B收到A的钱 A 800 --> B 400

将一组SQL放在一个批次中去执行

事务原则：ACID原则 原子性、一致性、隔离性、持久性（脏读、幻读…）

参考博客

原子性（Atomicity）
原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
一致性（Consistency）
事务前后数据的完整性必须保持一致。
隔离性（Isolation）
事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。
持久性（Durability）
持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

隔离导致的一些问题

脏读：

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

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

页面统计查询值

虚读(幻读)

是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）

七、索引

学习参考博客

MySQL官方对索引的定义为：索引(index)是帮助MySQL高效获取数据的数据结构

提取句子主干，就可以得到索引的本质：索引是数据结构

1、索引的分类

  • 主键索引(Primary Key)
    • 唯一的标识，主键不可重复，只能有一个列为主键
  • 唯一索引(Unique Key)
    • 避免重复的列出现，唯一索引可以重复，多个列都可以标识唯一索引
  • 常规索引(Key|Index)
    • 默认的，index，key关键字来设置
  • 全文索引(FullText)
    • 在特定的数据库引擎下才有，MyISAM
    • 快速定位数据

基础语法

-- 索引的使用
-- 1、在创建表的时候给字段增加索引
-- 2、创建表完成后，增加索引

-- 显示所有的索引信息
SHOW INDEX FROM `表名`

-- 增加一个全文索引
ALTER TABLE `表名` ADD FULLTEXT `索引名`(`字段名`);

-- EXPLAIN 分析sql执行情况
EXPLAIN SELECT * FROM `表名`; -- 非全文索引

2、测试索引

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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COMMENT = 'app用户表';

-- 插入100万数据.
DELIMITER $$
-- set global log_bin_trust_function_creators=TRUE;
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
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`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
    SET i=i+1;
  END WHILE;
RETURN i;
END;
SELECT mock_data() -- 执行此函数 生成一百万条数据

--  ================== 测试 ===================
-- 创建索引前
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.056 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.079 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.053 sec

-- 创建索引后
CREATE INDEX id_app_user_name ON `app_user`(`name`); -- 9.018 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.001 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0 sec

小数据量不用索引

3、索引原则

  • 索引不是越多越好
  • 不要对经常变动的数据加索引
  • 小数据量的表不用加索引
  • 索引一般加在常用来查询的字段

索引的数据结构

参考博客参考2(底层)

八、权限管理和备份

1、用户管理

SQLyog可视化操作

在这里插入图片描述

命令行操作

-- 创建用户
CREATE USER cc IDENTIFIED BY '123456';

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

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

-- 重命名 RENAME USER FOR 旧名 TO 新名;
RENAME USER cc TO dd;

-- 用户授权
-- ALL PRIVILEGES 除了传播权限，其他都有
-- [with grant option] 传播权限
GRANT ALL PRIVILEGES ON *.* TO dd;

-- 撤销权限 REVOKE 哪些权限 在哪个库 给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM dd;

-- 删除用户
DROP USER dd;

2、备份SQL

为什么要备份？

  • 保证重要的数据不丢失
  • 数据转移

MySQL数据库备份的方式

  • 直接拷贝物理文件（MySQL的data文件）

  • 在SQLyog等可视化软件工具种手动导出

    • 在想要导出的表或库中，右键，选择备份/导出，选择备份数据库，转存到sql
    • 再根据需要选择
    • 在这里插入图片描述

  • 使用命令行导出 mysqldump命令

# 导出表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/a.sql

# mysqldump -h 主机 -u 用户名 -p密码 数据库 表1 表2 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student result >D:/b.sql

# 导出数据库
# mysqldump -h 主机 -u 用户名 -p密码 数据库 > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql

# 导入表
# 登录的情况下，切换到指定的数据库
# source 备份文件
source d:/a.sql;

# 不登录
mysql -u用户名 -p密码 库名<备份文件

九、规范数据库设计

1、为什么需要设计

当数据库比较复杂的时候，外面就需要设计

糟糕的数据库设计：

  • 数据冗余，浪费空间
  • 数据库插入和删除都会很麻烦，异常【屏蔽使用物理外键】
  • 程序的性能差

良好的数据库设计：

  • 节省内存空间
  • 保证数据库的完整性
  • 方便我们开发系统

软件开发中，关于数据库的设计：

  • 需求分析：分析业务和需要处理的数据库的需求
  • 概要设计：设计关系图E-R图

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

  • 收集信息，分析需求
    • 用户表(用户登录注销，用户的个人信息，写博客，创建分类)
    • 分类表（文章分类，谁创建的）
    • 文章表（文章的信息）
    • 评论表
    • 友链表（友链信息）
    • 自定义表(心痛信息，某个关键的字，或者一些主字段) key:value
  • 标识实体（把需求落地到每个字段）
  • 标识实体之间的关系
    • 写博客：user --> blog
    • 创建分类：user --> category
    • 关注：user —> user
    • 友链：links
    • 评论：user-user-blog
/*
SQLyog Ultimate v10.00 Beta1
MySQL - 5.7.19 : Database - blog
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`blog` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `blog`;

/*Table structure for table `blog` */

DROP TABLE IF EXISTS `blog`;

CREATE TABLE `blog` (
  `id` int(10) NOT NULL COMMENT '文章的唯一标识',
  `title` varchar(100) NOT NULL COMMENT '文章标题',
  `author_id` int(10) NOT NULL COMMENT '编写文章的用户id',
  `category_id` int(10) NOT NULL COMMENT '文章分类',
  `content` text NOT NULL COMMENT '文章内容',
  `create_time` datetime NOT NULL COMMENT '创建时间',
  `update_time` datetime NOT NULL COMMENT '修改时间',
  `love` int(10) DEFAULT NULL COMMENT '喜欢',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `blog` */

/*Table structure for table `category` */

DROP TABLE IF EXISTS `category`;

CREATE TABLE `category` (
  `id` int(10) NOT NULL COMMENT '分类id',
  `category_name` varchar(30) NOT NULL COMMENT '分类标题',
  `create_name_id` int(10) NOT NULL COMMENT '创建用户的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `category` */

/*Table structure for table `comment` */

DROP TABLE IF EXISTS `comment`;

CREATE TABLE `comment` (
  `id` int(10) NOT NULL COMMENT '评论id',
  `blog_id` int(10) NOT NULL COMMENT '所属文章',
  `user_id` int(10) NOT NULL COMMENT '评论人',
  `content` varchar(2000) NOT NULL COMMENT '评论的内容',
  `create_time` datetime NOT NULL COMMENT '评论时间',
  `user_id_parent` int(10) NOT NULL COMMENT '回复人的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `comment` */

/*Table structure for table `links` */

DROP TABLE IF EXISTS `links`;

CREATE TABLE `links` (
  `id` int(10) NOT NULL COMMENT '友链id',
  `links` varchar(50) NOT NULL COMMENT '网站名称',
  `href` varchar(2000) NOT NULL COMMENT '网站链接地址',
  `sort` int(10) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `links` */

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(10) NOT NULL COMMENT '用户唯一id',
  `username` varchar(60) NOT NULL COMMENT '用户名',
  `password` varchar(60) NOT NULL COMMENT '用户密码',
  `sex` varchar(2) NOT NULL COMMENT '性别',
  `age` int(3) DEFAULT NULL COMMENT '年龄',
  `sign` varchar(200) DEFAULT NULL COMMENT '签名',
  `open_id` varchar(1000) NOT NULL COMMENT '微信id',
  `avatar` varchar(1000) NOT NULL COMMENT '头像链接地址',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `user` */

/*Table structure for table `user_follow` */

DROP TABLE IF EXISTS `user_follow`;

CREATE TABLE `user_follow` (
  `id` int(10) NOT NULL COMMENT '唯一标识',
  `user_id` int(10) NOT NULL COMMENT '被关注人的id',
  `follow_id` int(10) NOT NULL COMMENT '关注人的id',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `user_follow` */

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

2、三大范式

为什么需要数据规范化

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

三大范式

详细学习博客

第一范式（1NF）

原子性：保证每一列不可再分

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

第三范式（3NF）

前提：满足第一范式和第二范式

第三范式需要确保数据表中的每一项数据和主键直接相关，而不能间接相关。

（规范数据库的设计）

规范性和性能的问题

关联查询的表不得超过三张

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

十、JDBC（重点）

1、数据库驱动

驱动：声卡、显卡、数据库

在这里插入图片描述

我们的程序通过数据库驱动操作数据库！

2、JDBC

SUN公司为了简化开发人员的(对数据库的统一)操作，提供了一个(Java操作数据库的)规范，俗称JDBC

这些规范的实现由具体的厂商去做

对于开发人员来说，只需要掌握JDBC接口操作即可

在这里插入图片描述

用到的包

java.sql

javax.sql

导入一个数据库驱动的包:mysql-connector-java-5.1.47.jar

Maven仓库下载驱动

在这里插入图片描述

3、第一个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')

3.1 IDEA创建一个普通Java项目

3.2 在该项目下新建一个lib目录

右键该项目，New --> Directory

把mysql驱动包复制到lib目录下

右键lib目录，选择Add As Library…

在这里插入图片描述

3.3 测试代码

新建一个JdbcFirstDemo的类

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.连接信息，连接数据库(获取连接对象)
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
        String username = "root";
        String password = "root";

        Connection connection = DriverManager.getConnection(url, username, password);
        //3.获得数据库对象
        Statement statement = connection.createStatement();
        //4.对数据库执行CRUD操作
        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();
        }
        //5.释放资源
        resultSet.close();
        statement.close();
        connection.close();
    }
}

查询结果：

在这里插入图片描述

步骤总结

//1.加载驱动 
Class.forName("驱动名")
//2.编写连接信息（url，username，pwd），连接数据库(获取连接对象)
Connection connection = DriverManager.getConnection(url, username, password);
//3.获得数据库对象
Statement statement = connection.createStatement();
//4.对数据库执行CRUD操作
String sql = "SELECT * FROM `users`;";
ResultSet resultSet = statement.executeQuery(sql);
//[遍历结果集]
while (resultSet.next()){
     resultSet.getObject("字段名"));
     }
//5.释放资源
resultSet.close();
statement.close();
connection.close();

4、JDBC对象解释

DriverManager

// 第一种写法不建议使用，因为会注册两次驱动（源码）
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");

在这里插入图片描述

URL

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

// mysql -- 3306
//主协议:子协议://主机地址:端口号/数据库名?参数1&参数2&参数3

//oracle -- 1521
//jdbc:oracle:thin:@localhost:1521:sid
// 数据库中没有库的概念，只有表

Connection

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

//connection 代表数据库
//处理事务
connection.commit();
connection.rollback();
connection.getAutoCommit();

Statement & PrepareStatment

// 执行SQL的对象
String sql = "SELECT * FROM `users`;";//编写sql语句
ResultSet resultSet = statement.executeQuery(sql); //查询操作，返回ResultSet
statement.execute(sql); //执行任何SQL
statement.executeUpdate(sql); //更新，插入，修改都是用这个，返回一个受影响的行数

ResultSet

// 查询的结果集
resultSet.getObject("字段名"); //不知道列类型的时候使用
resultSet.getInt(int i); // 知道列类型的时候使用
resultSet.getInt("字段名");

// 遍历（指针）
resultSet.next();//移到下一条记录
resultSet.beforeFirst();//移到第一条记录
resultSet.afterLast();//移到最后一条记录
resultSet.previous();//移动到前一行
resultSet.absolute(int row);//移动到指定行

必须释放资源

resultSet.close();
statement.close();
connection.close();

5、JDBC提取工具类

配置文件

src目录下新建db.properties文件

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

编写工具类

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");
            //驱动只需要加载一次
            Class.forName(driver);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    public static Connection getConnection() throws SQLException {
         return DriverManager.getConnection(url, username, password);
    }
    public static void release(Connection con, Statement st, ResultSet rs){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(st!=null){
            try {
                st.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(con!=null){
            try {
                con.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

测试插入

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        Statement statement = connection.createStatement();
        String sql = "INSERT INTO `users` VALUES('4','huihui','456123','123@qq.com','2020-12-6')";
        int i = statement.executeUpdate(sql);
        if(i>0){
            System.out.println("插入成功");
        }
        JdbcUtils.release(connection,statement,null);
    }
}

6、SQL注入

通过拼接sql语句进行攻击（本来只能用账户密码登录，sql注入之后可以查到所有用户的账户密码）

public class SqlAttack {
    public static void main(String[] args) throws Exception {
        //login("zhansan","123456"); 正常查询
        // SELECT * FROM `users` WHERE `name`='' OR 1=1 OR '1' AND `password`='';
        login("'or 1=1 or '1","1");//sql注入，把所有用户都查出来
    }
    public static void login(String username,String password) throws Exception {
        Connection connection = JdbcUtils.getConnection();
        Statement statement = connection.createStatement();
        // SELECT * FROM `users` WHERE `name`='zhansan' AND `password`='123456';
        String sql = "SELECT * FROM `users` WHERE `name`='"+username+"'AND `password`='"+password +"'";
        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();
        }
        JdbcUtils.release(connection,statement,resultSet);
    }
}

7、PreparedStatement

可以有效防止SQL注入：把传递进来的参数全部变成字符串，而转义符全部忽略

查询

public class TestSelect {
    public static void main(String[] args) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        // 问号？表示占位符
        String sql = "select * from `users` where id = ?";
        //预编译SQL语句
        PreparedStatement pstm = connection.prepareStatement(sql);
        //传递参数
        pstm.setInt(1,1);

        // 执行sql
        ResultSet rs = pstm.executeQuery();
        while(rs.next()){
            System.out.println(rs.getString("name"));
        }
        JdbcUtils.release(connection, pstm, rs);
    }
}

插入

public class TestInsert {
    public static void main(String[] args) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        // 问号？表示占位符
        String sql = "insert into `users` values(?,?,?,?,?)";
        //预编译SQL语句
        PreparedStatement st = connection.prepareStatement(sql);
        //传递参数
        st.setInt(1,5);
        st.setString(2,"Allen");
        st.setString(3,"1230");
        st.setString(4,"abc@qq.com");
        // 穿入的Date对象是 java.sql包下的
        // 该对象需要一个时间戳参数，用java.util包下的Date传递该参数
        st.setDate(5,new Date(new java.util.Date().getTime()));
        int i = st.executeUpdate();
        if(i>0){
            System.out.println("插入成功");
        }
        // 执行sql
        JdbcUtils.release(connection, st, null);
    }
}

// 删除和更新也类似，只需要更改sql语句和传入参数即可

IDEA 连接数据库

点击左小角小窗口图标，右侧出现Database边框

在这里插入图片描述

在这里插入图片描述

1、打开mysql连接

在这里插入图片描述

2、输入用户名和密码，测试连接

在这里插入图片描述

PS：如果连接失败，可能是时区的问题

在这里插入图片描述

3、选择想看的数据库
在这里插入图片描述

4、写sql语句

在这里插入图片描述

5、其他操作

在这里插入图片描述

事务

connection.setAutoCommit(false);//开启事务
connection.commit(); //提交事务
// 默认失败会自动回滚，也可以在catch中显示定义

数据库连接池

为什么要用连接池？

如果每次执行sql语句都需要连接和释放数据库连接，那么这样会很耗费资源，所以这时候就需要用到连接池！

连接池：实现建立好一些连接，放在一个地方。需要与数据库连接时就从池子里取出，用完就放回去，这样就能提高效率！

学习博客

完结撒花！！！

我要手撕源码
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL 笔记整理
m0_60652886的博客
04-01 353
一、SQL语句 DQL：语句查询 select（查） DML：对表数据进行增删改 creat delete update (增删改） DDL：对表结构进行增删改 TCL：事务控制 DCL：授权、撤销权限 二、MySQL语句or命令 1.语句： 2.命令： 查看数据库: show databases; 创建数据库: creat database + 数据库名字 使用数据库: use + 数据库名字 ...
MySQL 基础篇笔记
qq_50249256的博客
07-31 1021
所以事务过程中每次快照查询数据都是一样的，即使中途有其他事务插入了新记录，是查询不出来这条数据的。例如，varchar(50) 定义了一个最大长度为 50 的字符串，如果插入的字符串只有 10 个字符，则实际存储的字符串为 10 个字符和一个字符串结束字符。在这种情况下，如果事物回滚或者修改了这些数据，那么读取这些脏数据的事务所处理的数据就是不准确的。m表示列的长度，范围是 0～255 个字符。varchar 的最大实际长度由最长的行的大小和使用的字符集确定，而实际占用的空间为字符串的实际长度加 1。
MYSQL笔记整理
JAY_jzj的博客
12-11 360
MySQL 1. 关系型数据库管理系统的介绍(RDBMS) 关系型数据库管理系统可以分为: 关系型数据库服务端软件 关系型数据库客户端软件 关系型数据库服务端软件: 主要负责管理不同的数据库，而每个数据库里面会有一系列数据文件，数据文件是用来存储数据的, 其实数据库就是一系列数据文件的集合。 关系型数据库客户端软件: 主要负责和关系型数据库服务端软件进行通信(通信语言...
MySql笔记整理mysq思维导图mysql完整知识库整理分享给需要的同学
最新发布
08-15
MySql笔记整理mysq思维导图mysql完整知识库整理分享给需要的同学
MySQL笔记整理.doc
07-12
数据库，简而言之可视为电子化的文件柜——存储电子文件...这里有我整理的一些关于数据库的笔记（MySQL），其中包括了DDL、DML、DQL、DCL等知识，内容相对基础，适合小白用于复习使用，或者参照数据库视频教学等学习。
mysql笔记整理思维导图加Word文档加pdf版
08-25
这份笔记涵盖了MySQL的基础知识到高级特性的理解与应用，帮助你在期末备考时系统性地学习和复习。 首先，让我们从基础开始。MySQL的核心概念包括数据库、表、字段和记录。数据库是存储数据的容器，表则是数据的结构...
MySQL笔记整理+练习所用数据库
10-23
MySQL+自学
mysql笔记整理详细记录
05-10
mysql整理
Mysql笔记整理
qq_40305923的博客
05-26 1790
一、如何使用终端操作数据库 1.如何登陆数据库服务器 mysql -uroot -p123456 2.如何查询数据库服务器中所有的数据库 show databases; 3.如何选中某一个数据库进行操作 use XXX;(某小数据库名称) select * from XXX（该数据库里表的名称）; //查询语句 select * from XXX where ID=1; //选中该表里的ID（字段）=1的记录 4.如何退出数据库服务器 exit; 5.如何在数据库服务器中创建我
MySQL[笔记整理]
SwaeLeeUknow的博客
08-11 206
MySQL笔记整理 MySQL 基础部分1[Select]->基础部分一 MySQL基础部分2[条件查询]->基础部分二 MySQL基础部分3[排序查询]->基础部分三 MySQL基础部分4[常见函数]->基础部分四 MySQL前4小节回顾->小节回顾 MySQL基础部分5[分组查询]-基础部分五 MySQL基础部分6[连接查询]->基础部分六 MySQL基础部分7[子查询]->基础部分七 MySQL基础部分8[分页查询]->基础部分八 M
Oracle中使用 PL/SQL Developer插入批量数据
chuiyuanying8914的博客
12-17 319
不是使用多条insert into语句 insertall intoTable1(col1,col2,col3)values(1,2,3) intoTable1values(4,5,6) intoTable2(col4,col5,col6)...
mysql整理笔记
kelvin
12-22 461
mysql安装： 安装： wget https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.15-1.el7.aarch64.rpm-bundle.tar sudo rpm -Uvh mysql80-community-release-el7-2.noarch.rpm yum install  -y  mysql-community-server &nbsp.
MySQL整理笔记
qq_51595322的博客
09-17 142
一、数据库的定义 数据库(DataBase,DB)，常见的数据库有:MySQL、Oracle、MS sqlServer。 数据管理系统(DataBaeseManagement,DBMS):用于管理数据库中的数据，可以对数据库中的数据进行增删改查。 SQL语言：结构化查询语言。 执行步骤：DBMS -> 执行 -> SQL -> 操作 -> MySQL 二、MySQL的基础知识 ① 注：端口号(port)：...
mysql笔记（汇总）
m0_73158362的博客
02-25 407
数据定义语言数据库操作查询所有数据库：SHOW DATABASES;查询当前数据库：SELECT DATABASE();创建数据库：CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];删除数据库：DROP DATABASE [ IF EXISTS ] 数据库名;使用数据库：USE 数据库名;注意事项UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集表操作。
Mysql学习笔记整理
C8WATER
01-27 608
Mysql是工作中经常用到的关系型数据库管理系统, 在这里做个学习笔记的总结, 篇幅较长, 采用持续更新的方式. //2019/01/27 更新=========================&gt;&gt;&gt;&gt;  安装 基于CentOS7.3的安装方式     ● 下载rpm包: wget https://dev.mysql.com/get/mysql80-communit...
Mysql的学习笔记整理
qq_38409944的博客
07-13 2197
忘记密码如何进入MYSQL ？ mysql.ini中配置： mysql -u root -p 就可以无密码进入mysql 注意： 1. 记住执行完语句后一定要加 ; 表示语句结束。 2. 列名和表名的要用反斜杠 ESC下面那个符号 为了防止和关键字重复造成错误。 3. 数据库管理： 查询所有数据库： mysql&amp;amp;gt; show databases; 创建数据库： m...
"MySQL笔记整理，详细可读，共17章完整笔记，值得推荐！
MySQL笔记整理了MySQL数据库的相关知识，共包括17章完整的笔记。在第一章中，介绍了MySQL数据库的基本概念，包括数据库的定义和数据库的作用。数据库是用来存储数据的地方，与普通文件存储相比，使用数据库可以永久...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值