MySQL数据库笔记狂神说笔记详解

本文主要是介绍MySQL数据库笔记狂神说笔记详解，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

1、初始MySQL

javaEE：企业级java开发 Web

前端（页面：展示，数据！）

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

数据库（存数据，Txt，Excel，word）

1.1、为什么学习数据库

1. 岗位需求
2. 现在的世界，大数据时代 得数据者得天下
3. 被迫需求：存数据
4. 数据库是所有软件体系中最核心得存在 DBA

1.2、什么是数据库

数据库（DB，DataBase）

概念:数据仓库，软件，安装在操作系统（window，linux，mac，…）之上！SQL，可以存储大量得数据，500万数据

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

1.3、数据库分类

关系型数据库：（SQL）

• MySQL，Oracle，Sql Server，DB2 , SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：（NoSQL）Not Only

• Redis,MongDB
• 非关系型数据库，对象存储，通过对象的自身的属性来决定

DBMS(数据库管理系统)

• 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据；
• MySQL ，数据库管理系统

1.4、MySQL简介

MySQL是一个关系型数据库管理系统

前世：瑞典MySQL AB 公司

今生：属于Oracle旗下产品

MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。

开源的数据库软件

体积小、速度快、总体拥有成本低，招人成本比较低，所有人必须会

中小型网站、或者大型网站、集群！

官网：https://www.Mysql.com

1.5、安装MySQL

安装建议：

1. 尽量不要使用exe

2. 尽可能使用压缩包安装

安装操作步骤（MySQL官网下载）

1. 下载后得到zip压缩包

2. 解压到自己想解压得目录

3. 添加环境变量

   1. 我的电脑->属性->高级->环境变量

   2. 选择PATH，在其后面添加：你的mysql安装文件下的bin文件夹

3. 在解压后的文件夹下创建my.ini 配置文件

4. 编辑my.ini文件，注意替换路径位置

  ```
  [mysqld]
  # 目录要换成自己的
  basedir=D:\MySQL\mysql-8.0.25\
  datader=D:\MySQL\mysql-8.0.25\data\      #8.0版本的不用写这一行
  port=3306
  skip-grant-tables      #8.0版本的也不用写这一行
  ```

5. 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录。

  然后输入mysqld -install（安装mysql）

6. 再输入 mysqld --initialize-insecure --user=mysql 初始化数据文件

7. 然后再次启动mysql 然后用命令 mysql -u root -p进入mysql管理界面(密码可为空)

8. 进入界面后更改root密码

   update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost';
   

    alter user 'root'@'localhost' identified with mysql_native_password by '123456';      #8.0版本用这个命令修改密码，123456为修改的秘密，可以自己修改
   

  (最后输入flush privileges; 刷新权限)

9. 修改my.ini文件删除最后一句skip-grant-tables（8.0版本不需要这一步。上面已经删除过了）

10. 输入exit退出，在重启mysql即可正常使用

    net stop mysql

    net start mysql
    

11. 连接上测试出现以下结果就安装好了

    启动服务

​ 输入密码

1.6、安装SQLyog

无脑安装就完事了

1. 安装

2. 注册

3. 打开

4. 新建一个数据库school

每一个sqlyog的执行操作，本质就是对应一个sql，可以在历史记录中查看

5. 新建一张表student

   字段: id,name,age
   

6. 查看表、添加数据
   

1.7、连接数据库

命令行连接

mysql -u root -p  --连接数据库(--单行注释)
flush privileges;  --刷新权限

--------------------------------
 -- 所有的语句都使用 ; 结尾
 show databases;	--查看所有的数据库
 use school         --切换数据库  use  数据库名
Database changed
 
show tables; 		--查看数据库中所有的表 
describe student;	--显示数据库中所有的表的信息
 
create database li;	--创建一个数据库 create database 数据库名
 
exit;	--退出连接
-- 单行注释（SQL本来的注释）
/*
多行注释（sql的多行注释）
*/

数据库 xxx 语言 CRUD 增删改查！

DDL 数据库定义语言

DML 数据库操作管理语言

DQL 数据库查询语言

DCL 数据库控制语言

2、操作数据库

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

MySQL不区分大小写

2.1、操作数据库（了解）

1、创建数据库

CREATE DATABASE [IF NOT EXISTS] li; --[]可选  {}必选
CREATE DATABASE `数据库名` CHARACTER SET utf8 COLLATE utf8_general_ci

2、删除数据库

DROP DATABASE [IF EXISTS] LI;

3、使用数据库

USE school --如果表名或者字段名是一个特殊字符，就需要带``(tab键的上面)

4、查找数据库

SHOW DATABASES  --查看所有数据库

• 可以对照sqlyog可视化历史记录查看sql
• 固定的语法或关键字必须要强行记住！

2.2、数据库的列(数据)类型

数值

• tinyint 十分小的数据 1个字节
• smallint 较小的数据 2个字节
• mediumint 中等大小的数据 3个字节
• int 标准的整数 4个字节 常用的
• bigint 较大的数据 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串形式的浮点数 金融计算的时候一般使用decimal DECIMAL(P,D);(p是有效数字的长度、D是小数点后的位数)

字符串

• char 字符串固定大小的 0~255
• varchar 可变字符串 0~65535 常用的变量 String
• tinytext 微型文本 2^8-1
• text 文本串 2^16-1 保存大文本

时间日期

• data YYYY-MM-DD,日期格式
• time HH:mm:ss 时间格式
• datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
• timestamp 时间戳 1970.1.1 到现在的毫秒数 较为常用
• year 年份表示

null

• 没有值，未知
• 注意不要使用null进行运算，结果为null

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

(每一个表，都必须存在以下五个字段！未来做项目用的，表示一个记录存在的意义

id 主键

version 乐观锁

is_delete 伪删除

gmt_create 创建时间

gmt_update 修改时间）

Unsigned：

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

zerofill：

• 0填充的（不能是负数）
• 不足的位数，使用0来填充 int(3) , 5 -->005

自增(auto_increment)：

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

**非空：**NULL not null

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

默认：

• 设置默认的值！
• 如果不指定该列的值，则会有默认的值！

2.4、创建数据库表（重点）

-- 目标：创建一个school数据库-- 创建学生表(列、字段)  使用SQL创建-- 学号int  登录密码varchar(20),性别(2),出生如期(datatime),家庭住址，email-- 注意点：使用(),表的名称和字段尽量使用 ``括起来-- AUTO_INCREMENT  自增-- 字符串使用 '' 单引号括起来-- 所有语句后面加 , (英文的), 最后一个不用加-- PRIMARY KEY 主键，一般一个表只有一个唯一的主键，建议写在最后面CREATE TABLE IF NOT EXISTS `student` (    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',    `birthday` DATETIME DEFAULT NULL COMMENT '生日',    `address` VARCHAR(20) DEFAULT NULL COMMENT '家庭住址',    `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',    PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8

格式

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

常用命令

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

2.5、数据表的类型

-- 关于数据库引擎/*INNODB  默认使用MYISAM  早些年使用*/

	MYISAM	INNODB
事物支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大、是MYISAM 2倍

常规使用操作:

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

在物理空间存在的位置

所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库

本质还是文件的存储

MySQL 引擎在物理文件上的区别

• InnoDB 在数据库表中只有一个 *.frm文件，以及上级目录下的ibdata1文件
• MYISAM对应的文件
  • *.frm 表结构的定义文件
  • *.MYD 数据文件(data)
  • *MYI 索引文件(index)

色湖之数据库表的字符集编码

CHARSET=utf8

不设置的话，会是mysql默认的字符集编码 （不支持中文！）

MySQL的默认编码是Latin1，不支持中文

在my.ini中配置默认的编码

character-set-server=utf8

2.6、修改删除表

修改

-- 修改表名：ALTER TABLE 旧表名  RENAME AS 新表名ALTER TABLE teacher RENAME AS teacher1-- 增加表的字段 ALTER TABLE  表名  ADD 字段名 列类型ALTER TABLE teacher ADD age INT(11)-- 修改表的字段  -- ALTER TABLE 表名 MODIFY 字段名 列类型[]ALTER TABLE teacher1 MODIFY age VARCHAR(10) -- 修改约束-- ALTER TABLE 表名 CHANGE 旧名字  新名字 列类型[]ALTER TABLE teacher1 CHANGE age age1 INT(1) -- 字段重命名-- 删除表的字段： ALTER TABLE 表名 DROP 字段名ALTER TABLE teacher1 DROP age1

删除

-- 删除表(如果表存在再删除)DROP TABLE IF EXISTS treacher1

注意点：

• `` 字段名，使用这个符号包裹
• 注释 – /**/
• sql 关键字大小写不敏感，建议写小写
• 所有的符号都用英文

3、MySQL数据管理

3.1、外键(了解即可)

方式一、在创建表的时候，增加约束（麻烦复杂）

CREATE TABLE IF NOT EXISTS `grade` (`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',`gradename` VARCHAR(10) NOT NULL COMMENT '年级姓名',PRIMARY KEY(`gradeid`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 学生表的gradeid字段 要去引用年级表的 gradeid-- 定义外键key-- KEY `FK_外键名` (`外键名`)-- 给这个外键添加约束（执行引用） references 引用-- CONSTRAINT `FK_外键名` FOREIGN KEY (`外键名`) REFERENCES `外键的表名`(`外键名`)CREATE TABLE IF NOT EXISTS `student` (    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',    `gradeid` INT(10) NOT NULL COMMENT '学生的年级',    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',    `birthday` DATETIME DEFAULT NULL COMMENT '生日',    `address` VARCHAR(20) DEFAULT NULL COMMENT '家庭住址',    `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',    PRIMARY KEY(`id`),    KEY `FK_gradeid` (`gradeid`),    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 '年级id',`gradename` VARCHAR(10) NOT NULL COMMENT '年级姓名',PRIMARY KEY(`gradeid`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 学生表的gradeid字段 要去引用年级表的 gradeid-- 定义外键key-- KEY `FK_外键名` (`外键名`)-- 给这个外键添加约束（执行引用） references 引用-- CONSTRAINT `FK_外键名` FOREIGN KEY (`外键名`) REFERENCES `外键的表名`(`外键名`)CREATE TABLE IF NOT EXISTS `student` (    `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',    `name` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',    `sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',    `gradeid` INT(10) NOT NULL COMMENT '学生的年级',    `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',    `birthday` DATETIME DEFAULT NULL COMMENT '生日',    `address` VARCHAR(20) DEFAULT NULL COMMENT '家庭住址',    `email` VARCHAR(20) DEFAULT NULL COMMENT '邮箱',    PRIMARY KEY(`id`) )ENGINE=INNODB DEFAULT CHARSET=utf8-- 创建表的时候没有外键关系ALTER TABLE studentADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);-- ALTER TABLE 表 ADD CONSTRAINT `FK_外键名` FOREIGN KEY (`外键名`) REFERENCES `外键的表名(被引用的表)`(`外键名`)

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

最佳实践

• 数据库就是单纯的表，只用来存数据，只有行(数据) 和列(字段)
• 我们想使用多张表的数据，想使用外键（用程序去实现）

3.2、DML语言(全部记住)

**数据库意义：**数据存储，数据管理

DML语言：数据操作语言

• insert
• update
• delete

3.3、添加

insert

-- 插入语句（添加）-- insert into `表名`([`字段1`,`字段2`,`字段3`]) values('值1'),('值2'),('值3',...)INSERT INTO `grade`(`gradename`) VALUES('大一')-- 由于主键自增我们可以省略给主键插入的语句，（如果不写表的字段，他就会一一匹配所有字段）-- 一般插入语句，我们要数据和字段一一对应！-- 插入多个字段，用逗号隔开INSERT INTO `grade`(`gradename`)VALUES('大二'),('大三')-- 给student表插入多个数据-- 1.给一个字段插入一个数据INSERT INTO `student`(`name`)VALUES('张三')-- 2.给两个字段插入两个数据INSERT INTO `student`(`name`,`sex`)VALUES('李四','女')-- 3.给四个字段插入四个数据INSERT INTO `student`(`name`,`sex`,`pwd`,`birthday`)VALUES('王五','男','5efwgw','2018-1-9')-- 4.给一个字段插入两个数据 (),(),...INSERT INTO `student`(`name`)VALUES('李1'),('李2')-- 5.给多个字段插入多个数据 -- 每一个要插入的字段(插入多个数据),都对应着一个括号: values('','','',...)  括号的值也要一一对应-- 一个字段要插入n个数据，就对应n个括号INSERT INTO `student`(`name`,`sex`,`pwd`,`address`)VALUES('李3','女','jfow','深圳'),('李4','男','whjgona','南昌'),('李5','女','wghio','广州')

语法：insert into `表名`([`字段1`,`字段2`,`字段3`]) values('值1'),('值2'),('值3',...)

注意事项：

1. 字段和字段之间使用英文逗号隔开
2. 字段是可以省略的，但是后面的值必须要一一对应，不能少
3. 可以同时插入多条数据，values后面的值，需要使用 , 隔开即可VALUES(),(),...
4. 每一个要插入的字段(多个字段),都对应着一个括号: values(’’,’’,’’,…) 括号的值也要一一对应
5. 一个字段要插入多个数据，就对应多个括号

3.4、修改

update 修改谁（条件） set 原来的值 = 新值

-- 修改语句-- 修改学员的名字，带了条件 WHERE id = 1UPDATE `student` SET `name`='玛丽' WHERE id = 1;-- 不指定条件的情况下，会改动所有的表的字段值UPDATE `student` SET `name`='java'-- 修改多个属性 用逗号隔开UPDATE `student` SET `name`='jack',`email`='4125255@qq.com' WHERE id = 1;-- 语法：-- UPDATE `表名` SET `字段名`='value',[`字段名`='value',...] where [条件]

条件：where 字句 运算符 id = 某个值，> 某个值，在某个区间内修改

操作符会返回布尔值

操作符	含义	范围	结果
=	相等	5=6	false
<>或!=	不等于	5<>6	true
>	大于	5>6	false
<	小于	5<6	true
<=	小于等于	5<=6	true
>=	大于等于	5>=6	false
BETWEEN … AND …	在某个范围内，闭合区间	[2,5]	true
AND	相当于 &&	5>1 and1>2	false
OR	相当于 ||	5>1 or 1>2	true

-- 通过多个条件定位修改数据UPDATE `student` SET`name`='李四' WHERE `name`='java' AND `sex`='男';

语法：UPDATE `表名` SET `字段名`='value',[`字段名`='value',...] where [条件]

注意：

• 字段名是数据的列，尽量带上``
• 条件，筛选的条件，如果没有指定，则会修改所有的值
• value，是一个具体的值，也可以是一个变量
• 多个设置的属性之间，使用英文逗号隔开

-- 使用BETWEEN ... AND ... 修改语句 语法 WHERE 字段名 BETWEEN 'value1' AND 'value2';UPDATE `student` SET`pwd`='999999'WHERE `id` BETWEEN '2' AND '6';-- 使用<> 修改语句 语法：WHERE 字段名 <> 'value';UPDATE `student` SET`name`='张三',`sex`='男'WHERE `name` <> '李6';-- 使用 AND 修改语句 语法：WHERE 条件1 AND 条件2UPDATE `student` SET`name`='李四'WHERE `sex`='男' AND `id` BETWEEN '2' AND '5';-- 使用OR 修改语句 语法：WHERE 条件1 OR 条件2;UPDATE `student` SET`sex`='女'WHERE `name`='李四' OR `name`='李6';

3.5、删除

delete

语法：delete from 表名 [where 条件]

-- 删除指定数据DELETE FROM `student` WHERE id=1;-- 字符串类型的值要用''

TRUNCATE

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

-- 清空student表TRUNCATE `student`

delete 和 truncate 的区别

• 相同点：都能删除数据，都不会删除表结构
• 不同点：
  • truncate 重新设置自增列 计数器会归零
  • truncate 不会影响事务

-- 测试delete 和 truncate 的区别CREATE TABLE `test`(`id` INT(2) NOT NULL AUTO_INCREMENT,`color` VARCHAR(10) NOT NULL,PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO `test`(`color`)VALUES('1'),('2'),('3')DELETE FROM `test`  -- 不会影响自增TRUNCATE `test`   -- 自增会归零

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

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

4、DQL查询数据（最重点）

（Data Query Language ：数据查询语言）

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

Select完整的语法：

SELECT语法

[left | right | inner join table_name]  --联合查询[where ...]  	-- 指定结果满足的条件[group by ...]  -- 指定结果按照哪几个字段来分组[having]    -- 过滤分组的记录必须满足的次要条件[order by ...]  -- 指定查询记录按一个或多个条件排序[limit {[offset,]row_count | row_countOFFSET offset}];-- 指定查询的记录从哪条至哪条-- 以上语法顺序不能排错、顺序很重要

4.2、指定查询字段

-- 查询全部的学生  SELECT * FROM 表SELECT * FROM student-- 查询指定的字段  SELECT 字段,[字段2],[字段3] FROM 表SELECT `studentno`,`studentname` FROM student-- 给查询的字段结果起一个别名   AS 可以给字段起别名，也可以给表起别名SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM student-- 函数 Concat(a,b)（拼接字符串）SELECT  CONCAT('姓名:',`studentname`) AS 新名字 FROM student

语法：SELECT 字段,[字段2],[字段3] FROM 表

有的时候，列的名字不是那么的见名知意，可以起别名 AS 字段 as 别名 表 as 别名

去重 distinct

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

-- 查询一下有哪些同学参加了考试，成绩SELECT * FROM result	-- 查询全部的考试成绩SELECT `studentno` FROM result	-- 查询有哪些同学参加了考试SELECT DISTINCT `studentno` FROM result	-- 发现重复数据，distinct去重

数据库的列（表达式）

SELECT VERSION()    -- 查询系统版本（函数）SELECT 100*6-9 AS 计算结果	-- 用来计算（表达式）SELECT @@auto_increment_increment   -- 查询自增的步长（变量）-- 查询学员的考试成绩SELECT `studentno`,`studentresult` FROM result-- 所有学院的考试成绩 + 1SELECT `studentno`,`studentresult`+1 AS 提分后的成绩 FROM result

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

select 表达式 from 表

4.3、where 条件子句

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

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

逻辑运算符

运算符	语法	描述
and &&	a and b a&&b	逻辑与，两个为真，则为真
or ||		逻辑或，一个为真，则为真
Not !		逻辑非，真为假，假为真

-- -------where----- ---- 查询所有学员的成绩SELECT `studentno`,`studentresult` FROM `result`-- 查询成绩在70到90之间的学生-- andSELECT `studentno`,`studentresult` FROM `result`WHERE `studentresult`>=70 AND `studentresult`<=90;-- &&SELECT `studentno`,`studentresult` FROM `result`WHERE `studentresult`>=70 && `studentresult`<=90;-- 查询除了1000号之外的学生的成绩-- !SELECT `studentno`,`studentresult` FROM `result`WHERE `studentno` !=1000;-- NotSELECT `studentno`,`studentresult` FROM `result`WHERE NOT `studentno` =1000;

模糊查询：比较运算符

运算符	语法	描述
is null	a is null	如果操作符null，则为真
is not null	a is not null	如果操作符为 not null ，则为真
between	a between b and c	若a在b和c之间，则为真
like	a like b	SQL匹配，如果a匹配b，则结果为真
in	a in(a2,a3,a4…)	假设a在a2,a3…其中的某一个值中，则结果为真

-- -------模糊查询----- ---- 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 '张__';-- 查询1000，1001，1002号同学 用 betweenSELECT `studentno`,`studentname` FROM `student`WHERE `studentno` BETWEEN 1000 AND 1002;-- 查询1000，1001，1002号同学 -- 用 in (具体的一个或者多个值)SELECT `studentno`,`studentname` FROM `student`WHERE `studentno` IN(1000,1001,1002);-- 查询地址在南昌或者天津的同学  SELECT `studentno`,`studentname` FROM `student`WHERE `address` IN('南昌','天津');-- 查询出生日期为空的同学SELECT `studentno`,`studentname` FROM `student`WHERE `borndate` IS NULL;-- 查询出生日期不为空的同学SELECT `studentno`,`studentname` FROM `student`WHERE `borndate` IS NOT NULL;

4.4、联表查询

JOIN

-- =======联表查询=========-- 查询参加了考试的同学(学号、姓名、科目编号、分数)SELECT * FROM studentSELECT * FROM result/*思路1. 分析需求，分析查询哪些字段，这些字段来自哪些表，（两张表以上，联表查询）2. 确定使用哪种联结查询？共有7种确定交叉点(这两张表种的哪个数据是相同的)判断的条件：学生表中的  studentno = 成绩表中的 studentno*/-- join on （链接的条件） 链接查询-- where  等值查询-- INNER JOINSELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS s -- 左表INNER JOIN `result` AS r -- 右表ON s.`studentno`=r.`studentno`-- RIGHT JOINSELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS s  -- 左表RIGHT JOIN `result` AS r  -- 右表ON s.`studentno`=r.`studentno`-- LEFT JOINSELECT s.`studentno`,`studentname`,`subjectno`,`studentresult`FROM `student` AS s  -- 左表LEFT JOIN `result` AS r  -- 右表ON s.`studentno`=r.`studentno`-- 哪张表在上面，哪张就是左表

操作	描述
Inner Join	根据查询的条件返回所需要的数据（有条件的膨胀/扩张返回数据）（实则是并集）
Left Join	返回左表中有的全部数据(右表中没有的) （左表是主表，右表是从表）
Right Join	返回右表中有的全部数据(左表中没有的) （右表是主表，左表是从表）

-- ========联表查询 多张表查询=========-- 查询了参加考试的同学信息：学号，学生姓名，科目名，分数SELECT s1.`studentno`,`studentname`,`SubjectName`,`studentresult`FROM student s1RIGHT JOIN result rON s1.studentno=r.studentnoINNER JOIN `subject` s2ON s2.subjectno=r.subjectno-- 需要查询的哪些数据  select ....字段-- 从哪张表中查询  from  表名 as 别名 -- 用哪种查询方式链接其他的表，根据实际需求来用(7种)  查询方式   表名 as 别名-- 找到交叉点   别名.字段 = 别名.字段-- 过滤筛选的条件  where ...-- 如果多张表查询 --> 继续链接其他表来完成实际的需求...

-- 查询学员所属的年级(学号，学生的姓名，年级名称)SELECT `studentno`,`studentname`,`gradename`FROM `student` sINNER JOIN `grade` gON s.`gradeid`=g.`gradeid`-- 查询科目所属的年级(科目编号，科目名称，年级名称)SELECT `SubjectNo`,`SubjectName`,`gradename`FROM `subject` sINNER JOIN `grade` gWHERE s.`GradeID`=g.`gradeid`-- 查询了参加 高等数学-2 考试的同学的信息：学号，学生姓名，科目名，分数SELECT s.`studentno`,`studentname`,`SubjectName`,`studentresult`FROM `student` sINNER JOIN `subject` subON s.`gradeid`=sub.`GradeID`INNER JOIN `result` rON s.`studentno`=r.`studentno`WHERE sub.`SubjectName`='高等数学-2'

自连接

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可

父类表

categoryid	pid	cateoryName
2	1	信息技术
3	1	软件开发
5	1	美术设计

子类表

categoryid	pid	categoryName
4	3	数据库
6	3	web开发
7	5	ps技术
8	2	办公信息

-- 查询父子信息  把一张表拆分为父类表a和子类表b-- 子类表中的pid=父类表中的categoryidSELECT a.`categoryName` AS 父类, b.`categoryName` AS 子类FROM `category` AS a,`category` AS bWHERE a.`categoryid`=b.`pid`

查询结果为：

4.5、分页和排序

排序 order by

-- 排序：升序ASC ，降序DESC-- ORDER BY 通过哪个字段排序 升序或降序(DESC、ASC)-- 查询的结果根据成绩降序、升序排序SELECT s.`studentno`,`studentname`,`SubjectName`,`studentresult`FROM `student` sINNER JOIN `subject` subON s.`gradeid`=sub.`GradeID`INNER JOIN `result` rON s.`studentno`=r.`studentno`WHERE sub.`SubjectName`='高等数学-2'ORDER BY `studentresult` ASC

分页 limit

-- 为什么要分页？-- 缓解数据库压力，给人的体验更好-- 分页，每页只显示5条数据-- 第一页   limit 0,5   (1-1)*5-- 第二页   limit 5,5   (2-1)*5-- 第三页   limit 10,5   (3-1)*5-- 第N页   limit  (n-1)*pageSize   pageSize-- 【pageSize：页面的数据大小】-- 【(n-1)*pageSize：起始数据的值】-- 【n：当前页】-- 【数据总数/页面大小 = 总页数】SELECT s.`studentno`,`studentname`,`SubjectName`,`studentresult`FROM `student` sINNER JOIN `subject` subON s.`gradeid`=sub.`GradeID`INNER JOIN `result` rON s.`studentno`=r.`studentno`WHERE sub.`SubjectName`='高等数学-2'ORDER BY `studentresult` ASCLIMIT 0,5

语法：limit(查询的起始下标,pageSize)

练习

-- 查询JAVA第一学年 课程成绩排名前十的学生，并且分数要大于80 的学生信息(学号、姓名、课程名称、分数)SELECT s.`studentno`,`studentname`,`SubjectName`,`studentresult`FROM `student` sINNER JOIN `subject` subON s.`gradeid`=sub.`GradeID`INNER JOIN `result` rON s.`studentno`=r.`studentno`WHERE `SubjectName`='JAVA第一学年'ORDER BY `studentresult` DESCLIMIT 0,10

4.6、子查询

where(这个值是计算出来的)

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

-- ==========where========-- 查询科目为高等数学-2的分数不小于80分的学生的学号和姓名-- 方式一：使用联表查询 SELECT s.`studentno`,`studentname` FROM `student` s INNER JOIN `result` r ON s.`studentno`=r.`studentno` INNER JOIN `subject` sub ON r.`subjectno`=sub.`subjectno` WHERE `SubjectName`='高等数学-2' AND `studentresult`>80 ORDER BY `studentresult` DESC-- 方式二：子查询 SELECT s.`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'  ) ORDER BY `studentresult` DESC  -- 方式三  嵌套   查询少部分符合条件的同学 可以用 in （有里及外） 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名同学的成绩的信息(学号、姓名、分数)-- 方法一：联表查询SELECT s.`studentno`,`studentname`,`studentresult`FROM `student` sINNER JOIN `result` rON s.`studentno`=r.`studentno`INNER JOIN `subject` subON r.`subjectno`=sub.`subjectno`WHERE `SubjectName` = 'C语言-1'ORDER BY `studentresult` DESCLIMIT 0,5-- 方法二：子查询SELECT s.`studentno`,`studentname`,`studentresult`FROM `student` sINNER JOIN `result` rON s.`studentno`=r.`studentno`WHERE `subjectno` = (SELECT `subjectno` FROM `subject` WHERE `SubjectName` = 'C语言-1')ORDER BY `studentresult` DESCLIMIT 0,5-- 方法三：嵌套(此方法显示不出学生的成绩，所以排序不了)SELECT `studentno`,`studentname` FROM `student` WHERE `studentno` IN (SELECT `studentno` FROM `result` WHERE `SubjectNo` =(SELECT `SubjectNo` FROM `subject` WHERE `SubjectName` = 'C语言-1'	))

4.7、分组和过滤

group by having

-- 查询不同课程的平均分、最高分、最低分，平均分大于80分-- 先查询再分组(根据不同的课程分组)SELECT `SubjectName`,AVG(`studentresult`) AS 平均分,MAX(`studentresult`) AS 最高分,MIN(`studentresult`) AS 最低分FROM `subject` subINNER JOIN `result` rON sub.`subjectno`=r.`subjectno`GROUP BY sub.`SubjectName` -- 通过实际的需求来分组HAVING 平均分>80 -- 过滤分组的记录必须满足的次要条件

5、MySQL函数

5.1、常用函数

-- =============常用函数========-- 数学运算SELECT ABS(-8) -- 绝对值SELECT CEILING(6.6)  -- 向上取整SELECT FLOOR(6.6) -- 向下取整SELECT RAND()  -- 返回一个0~1的随机数SELECT SIGN(10) -- 判断一个数的符号 0-0 负数返回-1  正数返回1-- 字符串函数SELECT CHAR_LENGTH('自律使人优秀') -- 字符串长度SELECT CONCAT('我','爱','JAVA')  -- 拼接字符串SELECT INSERT() -- 从某个位置开始替换某个长度SELECT LOWER() -- 转小写SELECT UPPER() -- 转大写SELECT INSTR() -- 返回字符第一次出现的索引SELECT REPLACE() -- 替换出现的指定字符SELECT SUBSTR() -- 返回指定的字符串(原字符串、截取的位置、截取的长度)SELECT REVERSE() -- 反转字符串-- 时间和日期函数(记住)SELECT CURRENT_DATE() -- 获取当前日期SELECT CURDATE() -- 获取当前日期SELECT NOW()  -- 获取当前时间SELECT LOCALTIME() -- 本地时间SELECT SYSDATE() -- 系统时间SELECT YEAR(NOW) -- 获取当前日期的年份SELECT SYSTEM_USER()  -- 获取当前用户SELECT USER()SELECT VERSION()

5.2、聚合函数（常用）

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

-- ==================聚合函数=============SELECT COUNT(studentname) FROM student; -- count(指定字段)，会忽略null值SELECT COUNT(*) FROM student; -- count(*)  不会忽略null值SELECT COUNT(1) FROM result; -- count(1)  不会忽略null值SELECT SUM(`studentresult`) AS 总和 FROM `result`SELECT AVG(`studentresult`) AS 平均分 FROM `result`SELECT MAX(`studentresult`) AS 最高分 FROM `result`SELECT MIN(`studentresult`) AS 最低分 FROM `result`

5.3、数据库级别的MD5加密(扩展)

MD5：主要增强算法复杂度和不可逆，具体的值的md5是一样的

MD5破解网站的原理，背后有一个字典，MD5加密后的值 加密前的值

-- =========测试MD5 加密=========CREATE TABLE `testmd5`(   `id` INT(2) NOT NULL AUTO_INCREMENT,   `name` VARCHAR(10) NOT NULL,   `pwd` VARCHAR(50) NOT NULL,   PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 明文密码INSERT INTO `testmd5`(`name`,`pwd`)VALUES('小明','123456'),('王二','123456'),('李五','123456')-- 加密全部的密码UPDATE `testmd5` SET `pwd`=MD5(pwd)-- 插入的时候加密INSERT INTO `testmd5`(`name`,`pwd`)VALUES('张三',MD5('123456'))-- 如何校验：将用户传递进来的密码：进行md5加密，然后比对加密后的值SELECT * FROM `testmd5` WHERE `name`='张三' AND pwd=MD5('123456')

6、事务

6.1、什么是事物

要么都成功，要么都失败

1、SQL执行 A 给 B 转账

2、SQL执行 B 收到 A 转账

两条语句要么都成功，要么都失败

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

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

原子性（atomicity)

要么都成功，要么都失败

一致性（consistency)

事物前后得数据完整性要保证一致

持久性（durability）

事物一旦提交则不可逆，被持久化到数据库中

隔离性（isolation）

事物得隔离性是多个用户并发访问数据库时，数据库为每一个用户开启得事务，不能被其他事务的操作数据所干预，事务之间要相互隔离

隔离所导致的一些问题

脏读：

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

不可重复读：

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

虚读(幻读)

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

执行事务

-- ============事务=============SET autocommit = 0 -- 关闭SET autocommit = 1 -- 开启（默认的）-- 手动处理事务-- 先关闭自动提交SET autocommit = 0-- 事务开启START TRANSACTION -- 标记一个事务的开始，从这个之后的sql 都在同一个事务内INSERT xxINSERT xx  -- 要么同时成功，要么同时失败-- 提交：成功了就持久化到数据库COMMIT-- 回滚：失败了就回到初始样子ROLLBACK-- 事务结束 SET autocommit = 1 -- 开启自动提交SAVEPOINT -- 设置一个事务的保存点(存档)ROLLBACK  TO SAVEPOINT -- 回滚到保存点(读档)RELEASE SAVEPOINT -- 撤销保存点（删档）

模拟场景

-- ========模拟转账==========-- 创建一个shop数据库CREATE DATABASE `shop` CHARACTER SET utf8 COLLATE utf8_general_ci-- 使用数据库USE `shop`-- 创建一张account表CREATE TABLE `account`(	`id` INT(3) NOT NULL AUTO_INCREMENT COMMENT '账户id',	`name` VARCHAR(10) NOT NULL COMMENT '账户名字',	`money` DECIMAL(10,2) NOT NULL COMMENT '账户余额',	PRIMARY KEY(`id`))ENGINE=INNODB DEFAULT CHARSET=utf8-- 插入数据INSERT INTO `account`(`name`,`money`)VALUES('A','2000.00'),('B','10000.00')-- 模拟转账:事务-- 关闭事务自动提交SET autocommit = 0;-- 开启一个事务START TRANSACTIONUPDATE `account` SET `money`=`money`-500 WHERE `name`='A' -- A-500UPDATE `account` SET `money`=`money`+500 WHERE `name`='B' -- B+500COMMIT; -- 提交事务，成功持久化到数据库ROLLBACK; -- 回滚，回到原始的样子-- 开启默认的自动提交SET autocommit=1;

7、索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效的获取数据的数据结构

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

7.1、索引的分类

在一个表中，主键索引只能有一个，唯一索引可以有多个

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

-- 索引的使用-- 1.在创建表的时候给字段增加索引-- 2.创建完毕后，增加索引-- 显示所有的索引信息SHOW INDEX FROM 表名-- 添加索引CREATE INDEX[需要添加的索引类型] 索引名 ON 表名(字段)-- 增加一个全文索引ALTER TABLE 表名 ADD INDEX[需要添加的索引类型] 索引名(列名)-- 删除索引DROP INDEX 索引名 ON 表名-- 删除主键索引ALTER TABLE 表名 DROP primary key-- explain 分信息sql执行的状况

7.2、测试索引

索引在小数据量的时候，用户不大，但是在大数据的时候区别十分明显

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

8、权限管理和备份

8.1、用户管理

-- 创建用户CREATE USER 用户名 IDENTIFIED BY '密码'-- 修改密码（修改当前用户密码）SET PASSWORD = PASSWORD('密码')-- 修改密码（修改指定用户密码）SET PASSWORD FOR 用户名 = PASSWORD('密码')-- 重命名RENAME USER 原来的名字 TO 新的名字-- 用户授权             库.名 *.*  所有的库所有的表-- ALL PRIVILEGES 除了不能给别人授权，其他都有权力干GRANT ALL PRIVILEGES ON *.* TO 用户名-- 查询权限 SHOW GRANT FOR 用户名 -- 指定用户查询SHOW GRANT FOR root@localhost -- 查询root管理员-- root用户权限：GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION-- 撤销用户权限 revoke 哪些权限，在哪个库撤销，给谁撤销REVOKE ALL PRIVILEGES ON *.* FROM 用户名-- 删除用户DROP USER 用户

8.2、MySQL备份

为什么要备份：

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

MySQL数据库备份的方式

• 直接拷贝物理文件在Sqlyog这种可视化工具中手动导出

• 使用命令行 mysqldump 命令行使用

  # mysqldump -h主机 -u用户名 -p密码 数据库 表名 > 物理磁盘位置/文件名mysqldump -hlocalhost -uroot -p123456 school student >d:/1.sql# mysqldump -h主机 -u用户名 -p密码 数据库 表1 表2 表3 > 物理磁盘的位置/文件名# 导入# 登录的情况下，切换到指定的数据库，再导入表# mysql -uroot -p123456# source 备份文件
  

9、规范数据库设计

9.1、为什么需要设计

当数据库比较复杂的时候，我们就需要设计了

糟糕的数据库设计：

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

良好的数据库设计：

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

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

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

设计数据库的步骤：

• 收集信息，分析需求

  • 表1（字段1，字段2…）
  • 表2（字段1，字段2…）
  • 表3（字段1，字段2…）
  • …

• 标识实体（把需求落地到每个字段）

• 标识实体之间的关系

  • 表1 关联 表2
  • 表2关联 表3
  • … …

9.2、三大范式

为什么需要数据规范化？

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

三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

确保数据库中的每一列都和主键相关，而不能只与主键的某一部分相关

每一张表只描述一件事情

第三范式（3NF）

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

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

（规范数据库的设计）

规范性和性能的问题

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

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

10、JDBC（重点）

10.1、数据库驱动

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

10.2、JDBC

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

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

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

java.sql

javax.sql

还需要导入一个数据库驱动包

10.3、第一个JDBC程序

创建测试数据库

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

1. 创建一个普通项目

2. 导入数据库驱动

3. 编写测试代码

   package com.li.lesson01;import java.sql.*;//我的第一个JDBC程序public class JdbcFirstDemo {    public static void main(String[] args) throws ClassNotFoundException, SQLException {        //1. 加载驱动        Class.forName("com.mysql.cj.jdbc.Driver");//固定写法，加载驱动        //2. 用户信息和url        //userUnicode=true&characterEncoding=utf8&userSSL=true        //支持中文编码      & 设置中文字符集为utf8   & 使用安全的连接        String url = "jdbc:mysql://localhost:3306/jdbcstudy?userUnicode=true&characterEncoding=utf8&userSSL=true";        String username = "root";        String password = "123456";        //3. 连接成功，数据库对象        //DriverManager.getConnection()        //Connection 代表数据库        Connection connection = DriverManager.getConnection(url, username, password);        //4. 执行SQL的对象        //Statement 执行sql的对象        Statement statement = connection.createStatement();        // 5. 执行SQL的对象去执行SQL，可能存在结果，查看返回结果        String sql = "SELECT * FROM `users`";        //执行sql     返回的结果集，结果集封装了我们全部的查询出来的结果        ResultSet resultSet = statement.executeQuery(sql);        //循环查看里面是否有数据，存在下一个数据        while (resultSet.next()) {            System.out.println("id=" + resultSet.getObject("id"));//不知道数据的类型就用getObject            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. 加载驱动

   • Class.forName(“com.mysql.cj.jdbcDriver”);

2. 用户信息和url mysql默认的端口号3306 Oracle是1521

   • url=“协议://主机地址:端口号/数据库名?userUnicode=true&characterEncoding=utf8&userSSL=true”;
   • user
   • password

3. 连接数据库，返回数据库对象

   • connection 代表数据库

     • 数据库自动提交
     • 事务提交、事务回滚

   • DriverManager.getConnection(url,user,password)

4. 获得执行sql的对象 Statement 执行SQL的对象 PrepareStatement 执行sql的对象

   • connection.getStatement

5. 执行sql的对象去执行sql

   • sql = “SECLECT * FROM 表名”;//编写SQL
   • statement.executeQuery(sql);//返回结果集resultSet，封装了所有的查询结果
   • statement.executeUpdate();//更新、插入、删除都是用这个，返回一个受影响的行数

6. 获得数据，查看是否存在下一个数据

   • resultSet 可以获得指定的数据类型

   • while(resultSet.next()) {}

7. 关闭连接，释放资源，先开后关

10.4、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

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

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

代码实现

1. 提取工具类

   package com.li.lesson02.utils;import java.io.InputStream;import java.sql.*;import java.util.Properties;public class JdbcUtils {    private static String url;    private static String user;    private static String password;    static {        try {            //获得类的加载器对象，再获得资源流            InputStream is = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");            //创建properties对象            Properties properties = new Properties();            //load()加载properties文件            properties.load(is);            //再调用getProperty()方法通过键获取对应的值            String driver = properties.getProperty("driver");            url = properties.getProperty("url");            user = properties.getProperty("user");            password = properties.getProperty("password");            //获得加载驱动，驱动只需要加载一次            Class.forName(driver);        } catch (Exception e) {            e.printStackTrace();        }    }    //连接数据库，返回数据库对象    public static Connection getConnection() throws SQLException {         return DriverManager.getConnection(url, user, password);    }    //关闭连接，释放资源    public static void release(ResultSet rs, Statement st,Connection conn){        try {            if (rs != null) {                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

      package com.li.lesson02;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;public class TestInsert {    public static void main(String[] args) {        Connection conn = null;        Statement st = null;        try {            conn = JdbcUtils.getConnection();//连接数据库，获得数据库对象            st = conn.createStatement();//获得执行sql对象            //执行sql            String sql = "INSERT INTO `users`(`id`,`name`,`password`,`email`,`birthday`)" +                    "VALUES(4,'wangli','123456','423958798@qq.com','2019-09-13')";            int i = st.executeUpdate(sql);//执行sql            if (i>0){                System.out.println("插入成功");            }        } catch (SQLException e) {            e.printStackTrace();        } finally {            //关闭连接，释放资源            JdbcUtils.release(null,st,conn);        }    }}
      

      package com.li.lesson02;import com.li.lesson02.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 rs = null;        try {            conn = JdbcUtils.getConnection();//获取数据库对象            st = conn.createStatement();//获得执行sql的对象            String sql = "DELETE FROM `users` WHERE id=4";            int i = st.executeUpdate(sql);//执行sql            if (i>0){                System.out.println("删除成功");            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {            JdbcUtils.release(rs,st,conn);        }    }}
      

      package com.li.lesson02;import com.li.lesson02.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 rs = null;        try {            conn = JdbcUtils.getConnection();//连接数据库            st = conn.createStatement();//执行sql对象            String sql = "UPDATE `users` SET `id`=4 WHERE id=1";            int i = st.executeUpdate(sql);            if (i>0){                System.out.println("修改成功");            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {            JdbcUtils.release(rs,st,conn);        }    }}
      

      查 executeQuery

      package com.li.lesson02;import com.li.lesson02.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 rs = null;        try {            conn = JdbcUtils.getConnection();//获得数据库对象            st = conn.createStatement();            String sql = "SELECT * FROM `users`";            rs = st.executeQuery(sql);//查询            while (rs.next()){                System.out.println("id=" + rs.getInt("id"));                System.out.println("name=" + rs.getString("name"));                System.out.println("password=" + rs.getString("password"));                System.out.println("email=" + rs.getString("email"));                System.out.println("birth=" + rs.getDate("birthday"));                System.out.println("==============================================");            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {            JdbcUtils.release(rs,st,conn);        }    }}
      

SQL注入问题

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

package com.li.lesson02;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;public class SQLInjection {    public static void main(String[] args) {        //login("lisi","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();            //SELECT * FROM `users` WHERE `name`='lisi' AND `password`='123456'            //SELECT * FROM `users` WHERE `name`=''or '1=1' AND `password`='123456'            String sql = "SELECT * FROM `users` WHERE `name`='" + username+"'" + "AND`password`='"+password+"'";            rs = st.executeQuery(sql);//查询            while (rs.next()){                System.out.println("id=" + rs.getInt("id"));                System.out.println("name=" + rs.getString("name"));                System.out.println("password=" + rs.getString("password"));                System.out.println("email=" + rs.getString("email"));                System.out.println("birth=" + rs.getDate("birthday"));                System.out.println("==============================================");            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {            JdbcUtils.release(rs,st,conn);        }    }}

10.5、PreparedStatement对象

PreparedStatement可以放止SQL注入，效率更好！

1、增

package com.li.lesson03;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.text.SimpleDateFormat;import java.util.Date;import java.sql.PreparedStatement;import java.sql.SQLException;public class TestInsert {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement st = null;        try {            conn = JdbcUtils.getConnection();//连接数据库,获得数据库对象            //区别            //使用？占位符代理参数            String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values (?,?,?,?,?)";            st = conn.prepareStatement(sql);//预编译，先写sql，然后不执行            //手动给参数赋值            st.setInt(1,1);            st.setString(2,"liliu");            st.setString(3,"154894");            st.setString(4,"32536@qq.com");            //注意点：sql.Date   数据库用的      java.sql.Date()            //      util.Date   java用的      new Date().getTime() 获得时间戳            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(null,st,conn);        }    }}

2、删

package com.li.lesson03;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;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);//预编译            st.setInt(1,1);//手动给参数赋值            //执行            int i = st.executeUpdate();            if (i>0){                System.out.println("删除成功");            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {        }    }}

3、改

package com.li.lesson03;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.SQLException;public class TestUpdate {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement st = null;        try {            conn = JdbcUtils.getConnection();            String sql = "UPDATE users set `id`=?,`name`=? where `id`=?";            st = conn.prepareStatement(sql);            st.setInt(1,5);            st.setString(2,"耳机");            st.setInt(3,2);            int i = st.executeUpdate();            if (i>0){                System.out.println("修改成功");            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {        }    }}

4、查

package com.li.lesson03;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;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 `name` from users where id=?";            st = conn.prepareStatement(sql);            st.setInt(1,5);            rs = st.executeQuery();            while (rs.next()){                System.out.println("name=" + rs.getString("name"));            }        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {            JdbcUtils.release(rs,st,conn);        }    }}

10.6、使用IDEA连接数据库

10.6、事务

代码实现事务

package com.li.lesson03;import com.li.lesson02.utils.JdbcUtils;import java.sql.Connection;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;public class TestTransaction {    public static void main(String[] args) {        Connection conn = null;        PreparedStatement st = null;        ResultSet rs = null;        try {            conn = JdbcUtils.getConnection();            //关闭数据库的自动提交事务，自动会开启事务            conn.setAutoCommit(false);            String sql1 = "UPDATE account SET money=money-500 WHERE NAME = 'A'";            st = conn.prepareStatement(sql1);            st.executeUpdate();            String sql2 = "UPDATE account SET money=money+500 WHERE NAME = 'B'";            st = conn.prepareStatement(sql2);            st.executeUpdate();            //业务完毕，提交事务            conn.commit();            System.out.println("提交成功");        } catch (SQLException throwables) {            throwables.printStackTrace();        }        try {            conn.rollback();//失败，自动回滚事务        } catch (SQLException throwables) {            throwables.printStackTrace();        } finally {            JdbcUtils.release(rs,st,conn);        }    }}

10.7、数据库连接池

数据库连接----执行完毕—释放

连接–释放 十分浪费资源

池化技术：准备一些预先的资源，过来就连接预先准备好的

编写连接池，实现一个接口 DataSource

开源数据源实现(拿来即用)

DBCP

C3P0

Druid：阿里巴巴

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

DBCP

需要用到的jar包

commons-dbcp2-2.8.0.jar、commons-pool2-2.10.0.jar、commons-logging-1.2.jar(报错无法初始化类的时候可以导入这个依赖包)

C3P0

需要导入的jar包

c3p0-0.9.5.5.jar、mchange-commons-java-0.2.19.jar

结论

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

这篇关于MySQL数据库笔记狂神说笔记详解的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！