目录
1、初识MySQL
JavaEE : 企业级Java开发 Wed
前段（页面：展示数据）
后台（连接点：连接数据库JDBC，连接前段（控制，控制视图跳转，给前端传递数据））
数据库（存数据，Txt，Excel，word）
-
只会写代码 ↓（一般程序员），需要学好数据库 ↑
-
操作系统，数据结构与算法！（不错的程序员）
-
离散数据，数字电路，体系结构，编译原理 + 实战经验 （优秀程序员）
1.1、为什么需要学数据库
- 岗位需求
- 现在的世界，大数据时代~，得数据库者得天下
- 被迫需求：存数据
- 数据库是所有软件体系中最核心的存在DBA
1.2、什么是数据库
数据库（DB ，DataBase）
概念：数据仓库，软件，安装在操作系统（window , linux , mac …）之上！SQL ， 可以存储大量的数据。500万以上需要优化
作用：存储数据，管理数据
1.3、数据库分类
关系型数据库：（SQL）
- MySQL，Oracle，Sql Server，DB2，SQLite
- 通过表与表之间，行和列之间的关系进行数据的存储， 学院信息表 <—> 考勤表
非关系型数据库：（NoSQL） Not Only
-
Redis，MongDB
-
非关系型数据库，对象存储，通过对象的自身的属性来决定
DBMS（数据库管理系统）
- 数据库的管理软件，科学有效的管理我们的数据。维护和获取数据
- MySQL，数据库管理系统
为什么要说这个呢?
因为我们要学习的MySQL应该算是一个数据库管理系统.
1.4、MySQL 简介
-
概念 : 是现在流行的开源的,免费的 关系型数据库
历史 : 由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下产品。
特点 :
- 免费 , 开源数据库
- 小巧 , 功能齐全
- 使用便捷
- 可运行于Windows或Linux操作系统
- 可适用于中小型甚至大型网站应用
1.5、安装MySQL
安装建议：
1.尽量不要exe，会进入注册表
2.尽可能使用压缩包安装
这里建议大家使用压缩版,安装快,方便.不复杂.
软件下载
mysql5.7 64位下载地址:
https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.19-winx64.zip
电脑是64位的就下载使用64位版本的！
- 解压到自己电脑的环境目录包下Environment
- 配置环境变量，我的电脑 -> 属性 -> 高级 -> 环境变量 -> PATH
- 在安装目录中，新建 mysql 配置文件 my.ini
[mysqld]
# 目录要换成自己的，data后面\不知道写不写
basedir=D:\Environment\mysql-5.7.34\
datadir=D:\Environment\mysql-5.7.34\data
port=3306
skip-grant-tables
- 启动管理员模式下的CMD，并将路径切换至mysql下的bin目录，然后输入mysqld -install（安装mysql）
cd /d D:\Environment\mysql-5.7.34\bin文件地址
mysqld -install
-
初始化数据文件
mysqld --initialize-insecure --user=mysql -
启动mysql
net start mysql -
进入mysql管理界面：-u（用户名），-p（密码，不可以有空格）由于my.ini中skip-grant-tables程序跳过了密码
mysql -u root -p -
进入mysql 通过命令行修改密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -
刷新权限
flush privileges; -
修改 my.ini文件注释最后一句#skip-grant-tables
-
关闭mysql
exit退出net stop mysql停止服务 -
重启mysql
net start mysql登录mysql -u root -p123456输入账号密码
连接上测试出现以下结果就安装好了
如果安装失败
清空服务sc delete mysql，然后重装
1.6、安装SQLyog
1、SQLyog的下载
链接：https://www.aliyundrive.com/s/11ZuFqB1z4E
提取码：63fu
2、SQLyog的安装
双击下载程序，可以选择默认安装.
64位的系统建议装X64的版本，下载文件是64位的版本。
3、 注册激活
注册信息：
Name:
tangbohu (用户名随意)
License Key：
| Professional: | 8e053a86-cdd3-48ed-b5fe-94c51b3d343c |
|---|---|
| Enterprise: | a46683b0-d3ec-4c16-8e3c-198d25a7fb52 |
| Ultimate: | 60c1b896-7c22-4405-9f46-a6bce776ab36 |
4、打开连接数据库
新建一个数据库 school
每一个sqlyog的执行操作，本质就是对应了一个sql，可以在软件的历史记录中查看
新建一张表 student
填写表信息
1.7、连接数据库
命令行连接！
mysql -u root -p123456 -- 连接数据库
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; -- 修改用户密码
flush privileges; -- 刷新权限
---------------------------------
--所有的语句都使用 ; 结尾
show databases; -- 查看所有的数据库
mysql> use school; --切换数据库 use 数据库名
Database changed
show tables; -- 查看数据库中所有的表
describe student; -- 显示数据库中所有的表的信息
create database westos; -- 创建一个数据库westos
----------------------------
exit --退出连接
ctrl+c --强行终止
-- 单行注释（SQL的本来的注释）
/*
多行注释
*/
数据库 xxx 语音 CRUD 增删改查 CV 程序猿 API程序猿 CRUD 程序猿
DDL 定义
DML 操作
DQL 查询
DCL 控制
2、操作数据库
操作数据库>操作数据库中的表>操作数据库中表的数据
MySQLd的关键字不区分大小写
2.1、操作数据库
-
创建数据库
CREATE DATABASE IF NOT EXISTS westos; -
删除数据库，删除表
DROP DATABASE IF EXISTS westos; DROP TABLE IF EXISTS student; -
使用数据库
-- tab 键的上面，如果你的表名或字段名是一个特殊字符，需要带 `` USE `school` -
查看数据库
SHOW DATABASES;
2.2、数据库的列类型
数值类型
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小 3个字节
- int 标准的整数 4个字节（常用）
- 输入位数为显示宽度，4位 1 = 0001
- bigint 较大的数据 8个字节
- float 浮点数 4个字节
- double 浮点数 8个字节 （精度问题）
- decimal 字符串形式的浮点数，金融计算的时候，一般用
字符串类型
- char 字符串固定大小 0-255
- varchar 可变字符串 0-65535（常用 String）
- tinytext 微型文本 2^8 - 1
- text 文本串 2^16 - 1 (保存大文本)
日期和时间型数值类型
java.util.Date
- date YYYY-MM-DD 日期
- time HH:mm:ss 时间格式
- datetime YYYY-MM-DD HH:mm:ss 最常用的时间格式
- timestamp 时间戳 1970.1.1到现在的毫秒数
- year 年份表示
null
- 理解为 "没有值" 或 "未知值"
- 不要用NULL进行算术运算 , 结果仍为NULL
2.3、数据库的字段类型（重点）
unsigned
- 无符号的整数
- 声明该列不能声明负数
zerofill
- 0填充的
- 不足位数用0 填充，10的长度（1 = 0000000001 ）
自增 Auto_increment
- 通常理解为自增，自动在上一条记录的基础上+1（默认）
- 通常用来设计唯一的主键 index，必须是整数类似
- 可以自定义设置主键自增的起始值和步长
非空 NULL not Null
- 假设设置为 not null，如何不给他赋值，就会报错
- NULL 如果不填写，默认为NULL
默认 default
- 设置默认的值！
- sex，默认值为 男 ，如果不指定该列的值，则会有默认的值！
拓展：
2.4、 创建数据库表（重点）
--目标:创建一个schoo1数据库
--创建学生表 (列 , 字段) 使用SQL 创建
--学号int，登录密码varchar(20)，姓名、性别varchar(2)，出生日期(datatime)，家庭住址，emai1
--注意点，使用英文()，表的名称和字段尽量使用括起来
-- AUTO_ INCREMENT 自增
--COMMENT 注释
--字符串使用单引号括起来!
--所有的语句后面加，(英文的)，最后一个不用加
-- PRIMARY KEY 主键，一般 一个表只有一个唯一 的主键!
CREATE DATABASE school
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
格式
CREATE TABLE [IF NOT EXISTS] `表名`（
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
.......
`字段名` 列类型 [属性] [索引] [注释]
）[表类型] [表的字符集设置] [注释]
常用命令（可以用这个反推创建数据表语句）
SHOW CREATE DATABASE school – 查看创建数据库的语句
SHOW CREATE TABLE student – 查看student数据表的定义语句
DESC student – 显示表的结构
2.5、数据表的类型
关于数据库引擎
- INNODB 默认使用
- MYISAM 早些年使用
INNODB和MYISAM的区别：
| MYISAM | INNODB | |
|---|---|---|
| 事务支持 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间的大小 | 较小 | 较大（约为2倍） |
常规使用操作：
- MYISAM 节约空间，速度较快，
- INNODB 安全性高，事务处理，多表多用户操作（外键约束）
在物理空间存在的位置
所有的数据库文件都存在data目录下，一个文件夹就对应一个数据库
本质还是文件的存储
MySQL 引擎在物理文件上的区别
-
innoDB 在数据库表中，只有一个*.frm文件，以及上级目录下的ibdata1文件
-
MYISAM 对应的文件
- *.frm - 表结构的定义文件
- *. MYD - 数据文件（data）
- *.MYI - 索引文件（index）
设置数据库字符集编码
CHARTSET=UTF8
不设置的话，会是mysql默认的字符集编码
MySQL 的默认编码是Latin1，不支持中文
可以在my.ini中配置默认的编码
character-set-server=utf8
2.6、修改删除表
修改ALTER TABLE
--修改表名 ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE student RENAME AS student1
--增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性
ALTER TABLE student1 ADD age INT(11)
--修改表的字段（重命名，修改约束）
ALTER TABLE 表名 MODIFY 字段名 列属性 [ ] –------ 修改约束 MODIFY
ALTER TABLE student1 MODIFY age VARCHAR(11)......
ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性 [ ] –------ 字段重命名 CHANGE
ALTER TABLE student1 CHANGE age age1 INT(1).......
--删除表的字段 ALTER TABLE 表名 DROP 字段名
ALTER TABLE student1 DROP age1
删除
-
删除表（如果表存在再删除）
DROP TABLE （IF EXISTS） student1所有的创建和删除操作尽量加上判断，以免报错
注意点：
- `` 字段名，使用这个包裹
- 注释 – /**/
- sql 关键字大小写不敏感，建议写小写
- 所有的符号全部用英文
3、MySQL数据管理
3.1、外键（了解）
外键概念
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。
在实际操作中，将一个表的值放入第二个表来表示关联，所使用的值是第一个表的主键值(在必要时可包括复合主键值)。此时，第二个表中保存这些值的属性称为外键(foreign key)。
外键作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。使两张表形成关联，外键只能引用外表中的列的值或使用空值。
创建外键
方式一：在创建表的时候，增加约束（麻烦，比较复杂）
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) NOT NULL COMMENT '年级名称',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
-- 学生表的 gradeid 字段 要去引用年级表的gradeid
-- 定义外键KEY
-- 给这个外键添加约束（执行引用） references 引用
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 '出生日期',
`gradeid` INT(10) NOT NULL COMMENT '学生年级',
`address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`),
KEY `FK_gardeid` (`gradeid`),
CONSTRAINT `FK_gardeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
方式二： 创建表成功后，添加外键
CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
`gradename` VARCHAR(50) 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 '学号',
`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 '出生日期',
`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 `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);
-- ALTER TABLE `表` ADD CONSTRAINT `约束名` FOREIGN KEY（`作为外键的列`） `引用到哪个表`(`的哪个字段`);
删除外键
操作：删除 grade 表，发现报错
注意：删除有外键关系的表的时候，必须先删除引用的表（从表），再删除被引用的表（主表）
-- 删除外键
ALTER TABLE student DROP FOREIGN KEY FK_gradeid;
-- 发现执行完上面的,索引还在,所以还要删除索引
-- 注:这个索引是建立外键的时候默认生成的
ALTER TABLE student DROP INDEX FK_gradeid;
以上的操作都是物理外键，数据库级别外键，不建议使用。（避免数据库过多造成困扰，这里了解即可）
最佳实践
-
数据库就是单纯的表，只用来存数据，只有行（数据）和列（字段）
-
我们想使用多张表的数据，想使用外键（程序去实现）
3.2、DML语言（全记住）
数据库意义：数据存储，数据管理
管理数据库数据方法：
- 通过SQLyog等管理工具管理数据库数据
- 通过DML语句管理数据库数据
DML语言 ：数据操作语言
- insert (添加数据语句)
- updata (更新数据语句)
- delete (删除数据语句)
3.3、添加 insert
插入语句（添加）
INSERT INTO grade(gradename) VALUES('大四')
-
主键自增我们是否可以省略（报错）
INSERT INTO`grade`VALUES('大三') -
如何不写表的字段，他会一一匹配
INSERT INTO`grade`(`gradeid`,`gradename`) VALUES ('大三','null')
一般写插入语句，我们一定要数据和字段一一对应
-
插入多个字段
INSERT INTO grade(gradename) VALUES ('大二'),('大一'); INSERT INTO student(name) VALUES ('张三') INSERT INTO student(name,pwd,sex) VALUES ('张三','aaaaa','男') INSERT INTO student(name,pwd,sex) VALUES ('李四','aaaaa','男'),('王五','23232','男')
语法：-- insert into 表名（[字段一], [字段二]）values(‘值1’),(‘值2’)
注意事项：
-
字段和字段之间用 英文逗号 隔开
-
字段可以省略，但是后面的值必须一一对应，必须全部设置
INSERT INTO student VALUES (5,'李四','aaaaa','男','2000-01-01',1,'西安','email') -
可以同时插入多条数据，VALUES 后面的值需要使用（逗号）隔开即可
values(),(),…
3.4 、修改 update
语法：
update 修改谁（条件） set 原来的值 = 新值
-- 修改学员名字，带了简介的
UPDATE `student` SET `name`='石硕' WHERE id =1;
-- 不指定条件的情况下，会改动所有表
UPDATE `student` SET `name`='c陈宇凡'
-- 修改多个属性，逗号隔开
UPDATE `student` SET `name`='石硕'，email = '[email protected]' WHERE id =1;
-- 通过多个条件定位数据
UPDATE student SET `name` = '石硕666',`email` = '[email protected]' WHERE `name` = '石硕66' AND `sex` = '男'
语法：
-- 条件：
where 子句 运算符 （id 等于 某个值，）（大于某个值，）（在某个区间内修改…）
-- 语法：
UPDATE 表名 set column_name(列) = value,[column_name = value,…] where [条件]
-- 例子
UPDATE student SET `name` = '石硕爹爹' WHERE id !=2
UPDATE student SET `name` = '石硕爸爸' WHERE id < 3
UPDATE student SET birthday = '1999-12-08' WHERE id <5 AND id>2
UPDATE student SET birthday = CURRENT_TIME, `name` = '陈宇凡儿子' WHERE 1<=id<=5
UPDATE student SET pwd = '7596' WHERE id BETWEEN 2 AND 5
操作符返回布尔值
| 操作符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5 = 6 | false |
| <> 或 ！= | 不等于 | 5 <> 6 | true |
| > | 大于 | 5 > 6 | false |
| < | 小于 | 5 < 6 | true |
| >= | 大于等于 | 6，7 >= 6 | true |
| <= | 小于等于 | 5，6 <= 6 | true |
| BETWEEN ** and ** | 在某个范围内 | [2 , 5] 3 | true |
| AND | 我和你 && | 5 > 1 and 1 > 2 | false |
| OR | 我或你 | 5 > 1 or 1 > 2 | true |
注意：
-
column_name 是数据库的列，尽量带上``
-
条件，是筛选的条件，如果没有指定，则会修改所有的列
-
value 是一个具体的值，也可以是一个变量 CURRENT_TIME时间
UPDATE `student` SET `birthday`= CURRENT_TIME WHERE `name`='长江' AND SEX = '女' -
多个设置的属性之间，使用英文逗号隔开
3.5 、删除 delete
delete命令
语法：delete from 表名 [where 条件]
-- 删除数据 (避免这样写，会全部删除)
DELETE FROM `student`
-- 删除指定
DELETE FROM `student` where id= 1
TRUNCATE 命令
作用：完全清空一个数据库，表的结构和索引约束不会变
-- 清空 student 表
TRUNCATE 'student'
delete 和 TRUNCATE 区别
- 相同点： 都能删除数据，都不会删除表结构
- 不同
- TRUNCATE 重新设置自增列 计数器会归零
- DELETE 不会影响事务
-- 测试delete 和 truncate 区别
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
DELETE FROM `test` -- 不会影响自增 再次输入1、2、3，id是从4开始（类似于删除）
TRUNCATE TABLE `test` -- 自增会归零（类似于初始化）
了解即可：Ddelete删除的问题，重启数据库，现象
- innoDB引擎 自增列会从1开始（存在内存当中，断电即失）
- MyISAM引擎 继续从上一个自增量开始（存在文件中，不会丢失）
4、DQL查询数据（最重点）
-- 测试数据
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;
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,'123456','张伟',0,2,'13800001234','北京朝阳','1980-1-1','[email protected]','123456198001011234'),
(1001,'123456','赵强',1,3,'13800002222','广东深圳','1990-1-1','[email protected]','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);
-- 插入年级数据
INSERT INTO `grade` (`gradeid`,`gradename`) VALUES(1,'大一'),(2,'大二'),(3,'大三'),(4,'大四'),(5,'预科班');
-- 插入科目数据
INSERT INTO `subject`(`subjectno`,`subjectname`,`classhour`,`gradeid`)VALUES
(1,'高等数学-1',110,1),
(2,'高等数学-2',110,2),
(3,'高等数学-3',100,3),
(4,'高等数学-4',130,4),
(5,'C语言-1',110,1),
(6,'C语言-2',110,2),
(7,'C语言-3',100,3),
(8,'C语言-4',130,4),
(9,'Java程序设计-1',110,1),
(10,'Java程序设计-2',110,2),
(11,'Java程序设计-3',100,3),
(12,'Java程序设计-4',130,4),
(13,'数据库结构-1',110,1),
(14,'数据库结构-2',110,2),
(15,'数据库结构-3',100,3),
(16,'数据库结构-4',130,4),
(17,'C#基础',130,1);
4.1、DQL
( Data Query Language :数据查询语言 )
- 所有的查询操作都用它 Select
- 简单的查询，复杂的查询它都能做
- 数据库中最核心的语言，最重要的语句
- 使用频率最高的语言
注意：
- 多个程序运行报错时，可以一段一段运行，由于运行顺序是cpu随机选择的，可能出现还未创建表格就已经开始写字段了，所以报错
Select 完整语法：
SELECT语句的完整语法为：
SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[WITH OWNERACCESS OPTION]
说明：
用中括号([])括起来的部分表示是可选的，用大括号({})括起来的部分是表示必须从中选择其中的一个。
4.2、指定查询字段
-- 查询全部的学生 查询 SELECT 字段 FROM 表
SELECT * FROM student
-- 查询指定字段 such as
SELECT `StudentNo`,`StudentName` FROM student
-- 别名AS，给结果起一个名字 AS 可以给字段起别名 也可以给表起别名
SELECT `StudentNo` AS 学号,`StudentName`AS 学生姓名 FROM student AS S
-- 函数 Concat(a,b)
SELECT CONCAT('姓名：',StudentName) AS 新名字 FROM student
-- 测试
SELECT CONCAT('姓名：',`studentname`) AS 学生姓名 FROM student
3.
4.
语法： SELECT 字段 … FROM 表
有时候，列名字不是那么见名知意。我们起别名 AS （字段名 AS 别名） （表名 AS 别名）
去重 distinct
作用：去除select语句查询出来的结果中重复的语句，重复的语句只显示一条
-- 查询一下有哪些同学参加了考试，成绩
SELECT * FROM result -- 查询全部的考试成绩
-- 查询有哪些同学参加了考试
SELECT `studentNo` FROM result
-- 发现重复数据，去重
SELECT DISTINCT `studentNo` FROM result
数据库的列（表达式）
-- 查询系统版本（函数）
SELECT VERSION()
-- 用来计算（表达式）
SELECT 100*3-1 AS 计算结果 ！
-- 查询自增的步长（变量）
SELECT @@auto_increment_increment
-- 学员考试成绩+1 分 查看
SELECT `StudentNo`,`StudentResult`+1 AS '提分后' FROM `result`
数据库中的表达式： 文本值，列，Null , 函数，计算表达式，系统变量…
select 表达式 from 表
4.3、where 条件子句
作用：检索数据中的符合条件的值
搜索的条件由一个或者多个表达式组成！结果：布尔值
| 运算符 | 语法 | 描述 |
|---|---|---|
| and && | a and b ， a && b | 逻辑 与，两个都为真，结果为真 |
| or || | a or b ， a || b | 逻辑 或，其中一个为真，则结果为真 |
| Not ! | not a ， ! a | 逻辑 非，真为假，假为真！ |
尽量使用英文
-- 查询所有学生，成绩
SELECT `StduentNo`,`StudentResult` FROM result
-- 查询考试成绩在95分到100分之间
WHERE StudentResult >=95 AND StudentResult<=100
-- 查询考试成绩为85或70的同学
SELECT studentresult AS 成绩 FROM result
WHERE studentresult = 85 || studentresult = 70
-- 模糊查询（区间）
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- 除了1000号学生之外的同学成绩
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentNo != 1000 -- 多个条件可用 and 相连
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 (a1,a2,a3…) | 假设 a 在 a1,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 '刘__';
-- 查询名字中间有嘉字的同学 %嘉%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%嘉%';
===================IN( 具体的一个或者多个值，不能用 % )===========================
-- 查询1001 1002 1003 学员信息
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1001
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1002
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo = 1003
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);
-- 查询在北京的学生
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('安徽','河南洛阳');
===================NULL，NOT NULL===================================
-- 查询地址为空的学生 null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL
-- 查询有出生日期的同学 不为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;
-- 查询没有出生日期的同学 为空
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NULL;
4.4、联表查询
JOIN 对比
7中jion理论
======================联表查询 join ==============================
-- 查询参加考试的同学 （学号，姓名，考试编号，分数）
SELECT * FROM student
SELECT * FROM result
/*
1. 分析需求，分析查询的字段来自哪些表 （多个表就需要连接查询）
2.确定使用哪种连接查询？ 总共7种
3.确定交叉点（这两个表中哪个数据是相同的）
判断的条件： 学生表中 studentNo = 成绩表中 studentNo
*/
-- join on 连接查询 join(连接的表) on(判断的条件)
-- where 等值查询
SELECT s.studentNo,studentName,SubjectNo,StudentResult -- 查询，交叉内容需要明确目标表
FROM student AS s
INNER JOIN result AS r -- 查询 student 并连接 result
WHERE s.studentNo = r.studentNo -- where 等值查询
-- Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result r -- AS 可以省略
ON s.studentNo = r.studentNo
-- LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
| 操作 | 描述 |
|---|---|
| Inner join | 如果表中至少有一个匹配，就返回行 （交叉） |
| left join | 也会从左表中返回所有的值，即使右表中没有匹配 |
| right jion | 也会从右表中返回所有的值，即使左表中没有匹配， |
-- 查询缺考的同学
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.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 r.SubjectNo=sub.SubjectNo
-- 我要查询哪些数据 SELECT ....
-- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
-- 假设存在一中多张表查询，先查询两章表，然后再慢慢增加
--FROM a LEFT JOIN b 左为准
--FROM a RIGHT JOIN b 右为准
思路
- 我要查询哪些数据 SELECT …
- 从哪几个表中查 FROM 表 xxx JOIN 连接的表 ON 交叉条件
- 假设存在一中多张表查询，先查询两章表，然后再慢慢增加
4.5、自连接
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 自连接数据库
DROP TABLE IF EXISTS `category`
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','软件开发'),
('5','1','美术设计'),
('4','3','数据库'),
('8','2','办公信息'),
('6','3','web开发'),
('7','5','ps技术');
自己的表跟自己的表连接，核心：一张表拆为两张一样的表
父类:
| categoryid | categoryName |
|---|---|
| 2 | 信息技术 |
| 3 | 软件开发 |
| 5 | 美术设计 |
子类
| pid | categoryid | categoryName |
|---|---|---|
| 3 | 4 | 数据库 |
| 2 | 8 | 办公信息 |
| 3 | 6 | web开发 |
| 5 | 7 | ps技术 |
操作：查询父类对应子类关系
| 父类 | 子类 |
|---|---|
| 信息技术 | 办公信息 |
| 软件开发 | 数据库 |
| 软件开发 | web开发 |
| 美术设计 | ps技术 |
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.`categoryname` AS 父栏目,b.`categoryname` AS 子栏目
FROM category AS a,category AS b
WHERE a.`categoryid` = b.`pid`
结果：
练习：:查询参加了考试的同学信息(学号,学生姓名,科目名,分数)
-- 查询学员所属的年级（学号，学生的姓名，年级）
SELECT `studentNo`,`studentName`,`gradeName` -- 查询的内容
FROM student s -- 查询的表
INNER JOIN `grade` g -- 连接的表
ON s.`GradeId`=g.`GradeId`
-- 查询科目所属的年纪 （科目名称，年纪名称）
SELECT `studentName`, `GradeName`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`GradeID` = g.`GradeID`
-- 查询参加了'高等数学-1'的同学信息：学号，姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` AS s
RIGHT JOIN result AS r
ON s.`studentno` = r.`studentno`
INNER JOIN `subject` AS sub
ON r.subjectno = sub.subjectno
WHERE subjectname = '高等数学-1'
4.6、分页和排序
排序 order by
-- ============================分页 limit 和排序order by=================
/*============== 排序 ================
语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
*/
-- 排序： 升序ASC 降序 DESC
-- 查询了参加 数据库结构-1 考试的同学信息： 学号，学生名字，科目名，分数
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON r.`StudentNo` = r.`StudentNo`
INNER JOIN `subject` sub
ON r.`StudentNo` = sub.`StudentNo`
WHERE subjectName = '数据库结构-1'
ORDER BY StudentResult DESC -- 指定排序字段 升序ASC 降序 DESC
SELECT xx
FROM xx
JOIN xx
WHERE xx
ORDER BY xx
ASC || DESC
‘
分页 limit
-- 为什么要分页？
-- 缓解数据库压力，给人的体验更好
-- 还有另外一种不分页的 瀑布流
-- 分页，每页显示五条数据
-- 语法： limit 起始值，页面的大小
-- 网页应用：当前总的页数，页面的大小
-- limit 0,5 1-5
-- limit 1,5 2-6
-- limit 6,5 第二页的数据
-- 网页应用 ： 当前页 ，总的页数 ，页面的大小
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='数据结构-1'
ORDER BY StudentResult ASC
LIMIT 0,5
-- 第一页 limit 0,5
-- 第二页 limit 5,5
-- 第三页 limit 10,5
-- 第N页 limit （n-1）* pageSize,pageSize
-- [pageSize 代表页面大小]
-- （n-1）* pageSize ：起始值
-- n : 当前页
-- 数据总数/页面大小 = 总页数
分页公式：
- 第一页 limit 0,5
- 第二页 limit 5,5
- 第三页 limit 10,5
- 第N页 limit （n-1）* pageSize,pageSize
- [pageSize 代表页面大小]
- （n-1）* pageSize ：起始值
- n : 当前页
- 数据总数/页面大小 = 总页数
语法：limit ( 查询起始下标，pagesize )
Practice：
-- 查询 JAVA第一学年 课程成绩排名前十的学生，并且分数要大于80 的学生信息（学号，名称，课程名称，分数）
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON sub.`subjectNo` = r.`subjectNo`
WHERE subjectName='JAVA第一学年' AND StudentResult >= 80
ORDER BY StudentResult DESC
LIMIT 0,10
4.7、子查询和嵌套查询
where (求这个值是计算出来的)
本质：在where语句中嵌套一个子查询语
子查询
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
-- =========================== where =========================
-- 1.查询 '高等数学-1'的所有考试结果（学号，科目编号，成绩），降序排列
-- 方式一： 连接查询
SELECT `StudentNo`,`SubjectName`,`StudentResult`
FROM result AS r
INNER JOIN `subject` AS sub
ON r.`subjectno` = sub.`subjectno`
WHERE subjectname = '高等数学-1'
ORDER BY subjectname DESC
-- 方式二：使用子查询(由里及外)
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE SubjectNo = (
SELECT `subjectno` FROM `subject`
WHERE `subjectname`='高等数学-1'
)
ORDER BY StudentResult DESC
-- ================1 .联表查询======================
-- 查询课程为 高等数学-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 studentresult >=80 AND subjectname = '高等数学-1'
-- =====================2. 子查询=======================
-- 分数不少于80分的学生的学号和姓名
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80
-- 在这个基础上 增加一个科目 ，高等数学-2
SELECT DISTINCT s.studentno,studentname
FROM student s
INNER JOIN result r
ON r.studentno = s.studentno
WHERE StudentResult>=80 AND subjectno =(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
-- 再改造 (由里即外)
SELECT StudentNo,StudentName FROM student -- 一张表不需要明确 s.StudentNo
WHERE StudentNo IN(
SELECT StudentNo FROM `result`
WHERE StudentResult >80 AND subjectno =(
SELECT subjectno FROM `subject`
WHERE subjectname = '高等数学-1'
)
)
4.8、分组和过滤
GROUP BY 和 HAVING
GROUP BY 字段 -- 通过什么字段来分组
HAVING '次要条件' -- 过滤分组的记录必须满足的次要条件
-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心：（根据不同的课程分组）
SELECT `SubjectName`,AVG(StudentResult) AS 平均分,MAX(StudentResult) AS 最高分,MIN(StudentResult) AS 最低分
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo = sub.SubjectNo
GROUP BY r.SubjectNo -- 通过什么字段来分组
HAVING 平均分 >80 -- 过滤分组的记录必须满足的次要条件
注意：
HAVING 过滤分组的记录必须满足的次要条件
4.9、Select小结
5、MySQL函数
5.1 、常用函数
MySQL 5.7 参考手册:https://dev.mysql.com/doc/refman/5.7/en/built-in-function-reference.html
数学运算
SELECT ABS(-8) -- 绝对值（8）
SELECT CEILING(9.4) -- 向上取整（10）
SELECT FLOOR(9.4) -- 向下取整（9）
SELECT RAND() -- 返回0-1随机数
SELECT SIGN(-10) -- 判断一个数的符号 0=0 负数返回-1 正数返回1
字符串函数
SELECT CHAR_LENGTH(‘2323232’) -- 返回字符串长度
SELECT CONCAT(‘我’,‘233’) -- 拼接字符串
SELECT INSERT(‘java’,1,2,‘cccc’) -- 从某个位置开始替换某个长度（第1个开始替换2个）
SELECT UPPER(‘abc’) -- 转大写
SELECT LOWER(‘ABC’) -- 转小写
SELECT INSTR（‘kuangshen’,‘h’） -- 返回指定值索引
SELECT REPLACE(‘坚持就能成功’,‘坚持’,‘努力’) -- 替换指定字符串
SELECT SUBSTR(‘坚持就能成功’,3,2) -- 返回指定的字符串（第3个开始返回2个）
SELECT REVERSE(‘坚持就能成功’) -- 反转字符串
查询姓 周 的同学 ，改成 邹=
SELECT REPLACE(studentname,‘周’,‘邹’) FROM student
WHERE studentname LIKE ‘周%’
时间跟日期函数（记住）
SELECT CURRENT_DATE() -- 获取当前日期
SELECT CURDATE() -- 获取当前日期
SELECT NOW() -- 获取当前日期和时间
SELECT LOCATIME() -- 获取本地日期时间
SELECT SYSDATE() -- 获取系统时间
年月日时分秒
SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())
系统
SELECT SYSTEM_USER() -- 返回当前系统用户
SELECT USER() -- 返回当前系统用户
SELECT VERSION() -- 返回版本号
5.2 、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| COUNT() | 计数 |
| SUM() | 求和 |
| AVG() | 平均值 |
| MAX() | 最大值 |
| MIN() | 最小值 |
| ... | ... |
-- 都能够统计 表中的数据
SELECT COUNT(`BornDate`) FROM student; -- Count（指定字段），会忽略该字段所有的 null 值
-- 本质都是计算行数，但是 * 是读取每行所有内容，1 只读取一个内容，相比较 1 会更快
SELECT COUNT(*) FROM student; -- Count（*），不会忽略 null值
SELECT COUNT(1) FROM student; -- 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
count1和count*的区别
count（1）和count（）之间没有区别，因为count（）count（1）都不会去过滤空值。
1、如果列为主键，count(列名)效率优于count，如果列不为主键，count(1)效率优于count(列名)，如果表中存在主键，count(主键列名)效率最优 ，如果表中只有一列，则count(*)效率最优，如果表有多列，且不存在主键，则count(1)效率优于c
2、count(1)，其实就是计算一共有多少符合条件的行。1并不是表示第一个字段，而是表示一个固定值。其实就可以想成表中有这么一个字段，这个字段就是固定值1，count(1)，就是计算一共有多少个1。
3、count(*)，执行时会把星号翻译成字段的具体名字，效果也是一样的，不过多了一个翻译的动作，比固定值的方式效率稍微低一些。
5.3、数据库级别的MD5加密(扩展)
什么是MD5
主要增强算法复杂度不可逆性。
MD5不可逆，具体的MD5是一样的
MD5破解原理，背后有一个字典，将以前比较简单的 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,'张三','123456'),(2,'李四','123456'),(3,'王五','123456')
-- 加密
UPDATE testmd5 SET pwd=MD5(pwd) WHERE id =1
UPDATE testmd5 SET pwd=MD5(pwd) -- 加密全部
-- 插入时加密
INSERT INTO testmd5 VALUES(4,'小明',MD5('123456'))
INSERT INTO testmd5 VALUES(5,'红',MD5('123466'))
-- 如何校验，将用户传递过来的密码，进行MD5加密，然后对比加密后的值
SELECT * FROM testmd5 WHERE `name`='红' AND pwd=MD5('123466')
运行结果：
6、事务(ACID)(重点)
6.1 、什么是事务(重点)
事务ACID详解：https://blog.csdn.net/dengjili/article/details/82468576/
什么是事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错,则该批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
事务管理（ACID）原则
谈到事务一般都是以下四点: （原子一致持久隔离）
- 原子性（Atomicity）
要么都成功，要么都失败
- 一致性（Consistency）
事务前后的数据最终完整性要保持一致
- 持久性（Durability）–事务提交
事务没有提交，恢复到原样
事务一旦提交就不可逆转，被持久化到数据库中
- 隔离性
多个用户并发访问数据库时，数据库为每一个用户开启的事物，不能被其他事务的操作数据所干扰，事务之间要相互隔离
隔离产生的问题
- 脏读：
指一个事务读取了另外一个事务未提交的数据。
- 不可重复读：
在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）
- 虚读(幻读)
是指在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。
（一般是行影响，多了一行）
执行事务
-- mysql 自动开启事务提交
SET autocommit=0 -- 关闭
SET autocommit=1 -- 开启（默认的）
-- 手动处理事务
SET autocommit =0 -- 关闭自动提交
-- 事务开启
START TRANSACTION -- 标记一个事务的开始，从这个之后的SQP都在同一个事务内
INSERT XX
INSERT XX
-- 提交 ： 持久化(提交一个事务给数据库)
COMMIT
-- 回滚： 将事务回滚,数据回到本次事务的初始状态
ROLLBACK
-- 事务结束
SET autocommit = 1 -- 还原MySQL数据库的自动提交
-- 保存点
SAVEPOINT 保存点名称 -- 设置一个事务的保存点
ROLLBACK TO SAVEPOINT 保存点名 -- 回滚到保存点
RELEASE SAVEPOINT 保存点 -- 撤销保存点
模拟场景
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
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),('B',10000)
-- 模拟转账：事务
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION -- 开启事务（一组事务）
UPDATE account SET money = money-500 WHERE `name` = 'A' -- A 转账给B
UPDATE account SET money = money+500 WHERE `name` = 'B' -- B 收到钱
COMMIT ; -- 提交事务
ROLLBACK ; -- 回滚
SET autocommit=1 -- 恢复默认值
7、索引
https://blog.codinglabs.org/articles/theory-of-mysql-index.html
MySQL官方对索引的定义：索引（Index）
索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
7.1、索引的分类
- 主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，只能有一个列作为主键
- 唯一索引（UNIQUE KEY）
- 避免重复的列出现， 唯一索引可以重复，多个列都可以标识唯一索引
- 常规索引（KEY/INDEX）
- 默认的，index,key关键字来设置
- 全文索引（FULLTEXT）
- 在特点的数据库引擎下才有，MyISAM 和 InnoDB
- 快速定位数据
主键索引（PRIMARY KEY）
主键 : 某一个属性组能唯一标识一条记录
特点 :
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
唯一索引（UNIQUE KEY）
作用 : 避免同一个表中某数据列中的值重复
与主键索引的区别
- 主键索引只能有一个
- 唯一索引可能有多个
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- PRIMARY KEY(`GradeID`)
-- 或 UNIQUE KEY `GradeID` (`GradeID`) 唯一索引
)
常规索引（KEY/INDEX）
作用 : 快速定位特定数据
注意 :
- index 和 key 关键字都可以设置常规索引
- 应加在查询找条件的字段
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
全文索引（FULLTEXT）
百度搜索：全文索引
作用 : 快速定位特定数据
注意 :
- 只能用于MyISAM 和 InnoDB类型的数据表
- 只能用于CHAR , VARCHAR , TEXT数据列类型
- 适合大型数据集
- 只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
/*
#方法一：创建表时
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 版本、存储引擎和数据类型是否支持全文索引。
*/
拓展：测试索引
建表app_user：
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用户表'
批量插入数据：100w
DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
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`, `password`, `age`)
VALUES(CONCAT('用户', i), '[email protected]', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
7.2、索引效率测试
explain
explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
作用
1、表的读取顺序
2、数据读取操作的操作类型
3、哪些索引可以使用
4、哪些索引被实际使用
5、表之间的引用
6、每张表有多少行被优化器查询
explain用法：
explain+SQL语句即可！
无索引
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时 2.745 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 2.053 sec
SELECT * FROM app_user WHERE name = '用户9999';
创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
测试普通索引
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999' -- 0.001 sec
SELECT * FROM app_user WHERE name = '用户9999'; -- 0.107 sec
删除索引
-- ALTER TABLE `数据库名`.`表名` DROP INDEX `索引名`;
ALTER TABLE `school`.`app_user` DROP INDEX `id_app_user_name`;
7.3、索引的原则
索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表建议不要加索引
- 索引一般应加在查找条件的字段
索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
-- 不同的存储引擎支持的索引类型也不一样
InnoDB -- 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM -- 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory -- 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB -- 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive -- 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
8、权限管理和备份
8.1、用户管理
SQLyog 可视化管理
使用SQLyog 创建用户，并授予权限演示，命令操作
用户表：mysql.user
本质：对这张表进行，增删改查
/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY '密码' -- 密码(字符串)
/*
- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
- 只能创建用户，不能赋予权限。
- 用户名，注意引号：如 'user_name'@'192.168.1.1'
- 密码也需引号，纯数字密码也要加引号
- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
*/
-- 重命名用户 rename user 原名字 to 新名字
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 分配权限/添加用户
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();
-- 查询权限
SHOW GRANTS FOR chenyufan; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost;
-- ROOT用户权限：GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` WITH GRANT OPTION
-- WITH GRANT OPTION 也可以给别人授权
-- 用户授权 ALL PRIVILEGES 全部的权限 库，表
-- ALL PRIVILEGES 除了给别人授权，其他都能干
GRANT ALL PRIVILEGES ON *.* TO chenyufan
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
权限解释
-- 权限列表
ALL [PRIVILEGES] -- 设置除GRANT OPTION之外的所有简单权限
ALTER -- 允许使用ALTER TABLE
ALTER ROUTINE -- 更改或取消已存储的子程序
CREATE -- 允许使用CREATE TABLE
CREATE ROUTINE -- 创建已存储的子程序
CREATE TEMPORARY TABLES -- 允许使用CREATE TEMPORARY TABLE
CREATE USER -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW -- 允许使用CREATE VIEW
DELETE -- 允许使用DELETE
DROP -- 允许使用DROP TABLE
EXECUTE -- 允许用户运行已存储的子程序
FILE -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX -- 允许使用CREATE INDEX和DROP INDEX
INSERT -- 允许使用INSERT
LOCK TABLES -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS -- 允许使用SHOW FULL PROCESSLIST
REFERENCES -- 未被实施
RELOAD -- 允许使用FLUSH
REPLICATION CLIENT -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT -- 允许使用SELECT
SHOW DATABASES -- 显示所有数据库
SHOW VIEW -- 允许使用SHOW CREATE VIEW
SHUTDOWN -- 允许使用mysqladmin shutdown
SUPER -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE -- 允许使用UPDATE
USAGE -- “无权限”的同义词
GRANT OPTION -- 允许授予权限
8.2、MySQL备份
数据库备份必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份的方式
- 直接拷贝物理文件
- 在SQLyog这种可视化工具中手动导出
- 在想要导出的表或者库中，右键选择备份和导出
- 使用命令行导出 mysqldump 命令行使用
-- 导出
1. 导出一张表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表名 > 物流磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 shool student >D:/a.spl
2. 导出多张表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 表1 表2 表3 > 物流磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 shool student result >D:/b.spl
3. 导出所有表
# mysqldump -h 主机 -u 用户名 -p密码 数据库 > 物流磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 shool >D:/c.spl
4. 导出一个库
-- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
# 导入表
# 登录后，切换到指定的数据库
# source 备份文件
-- 导入
1. 在登录mysql的情况下：-- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
作用 :
- 转储数据库
- 搜集数据库进行备份
- 将数据转移到另一个SQL服务器,不一定是MySQL服务器
9、规范数据库设计
9.1、为什么要设计数据库
当数据库比较复杂时我们需要设计数据库
糟糕的数据库设计 :
- 数据冗余,存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计 :
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用系统的开发
软件项目开发周期中数据库设计 :
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
设计数据库步骤
-
收集信息
-
- 与该系统有关人员进行交流 , 座谈 , 充分了解用户需求 , 理解数据库需要完成的任务.
-
标识实体[Entity]
-
- 标识数据库要管理的关键对象或实体,实体一般是名词
-
标识每个实体需要存储的详细信息[Attribute]
-
标识实体之间的关系[Relationship]
9.2、三大范式
问题 : 为什么需要数据规范化?
不合规范的表设计会导致的问题：
-
信息重复
-
更新异常
-
插入异常
-
- 无法正确表示信息
-
删除异常
-
- 丢失有效信息
三大范式
- 第一范式 (1st NF)
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
- 第二范式(2nd NF)
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。
第二范式要求每个表只描述一件事情
- 第三范式(3rd NF)
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式.
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范化和性能的关系
作用：
为满足某种商业目标 , 数据库性能比规范化数据库更重要
在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
10、JDBC（重点）
10.1、数据库驱动
驱动：声卡，显卡，数据库
我们的程序会通过数据库驱动，和数据库打交道！
但是如果每一种数据库就要写一种驱动，那会产生大量的工作，所以出现出现了JDBC
10.2、JDBC
SUN 公司为了简化开发人员的（对数据库的统一）操作，提供了一个(Java操作数据库的)规范 俗称 JDBC
这些规范的实现由具体的厂商去做
对于开发人员来说，我们只需要掌握JDBC的接口操作即可
需要使用的包：
- java.sql
- javax.sql
- 还需要导入数据库驱动包 mysql-connector-java-5.1.47.jar
10.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','[email protected]','1980-12-04'),
(2,'lisi','123456','[email protected]','1981-12-04'),
(3,'wangwu','123456','[email protected]','1979-12-04')
- 创建一个普通项目
- 导入数据库驱动
3.编写测试代码
//我的第一个JDBC程序
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class JdbcFirstDemo {
public static void main(String[] args) throws Exception {
//1. 加载驱动
Class.forName("com.mysql.jdbc.Driver");//固定写法
//2. 用户信息和url
//useUnicode=true&characterEncoding=utf8&&useSSL=true
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
String name = "root";
String password = "123456";
//3. 连接成功，返回数据库对象 connection代表数据库
Connection connection= DriverManager.getConnection(url,name,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"));
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"));
}
//6. 释放连接
resultSet.close();
statement.close();
connection.close();
}
}
步骤总结：
- 加载驱动
- 连接数据库 DriverManager
- 获取执行SQL的对象 Statement
- 获得返回的结果集
- 释放连接
10.4、分析JDBC代码
DriverManager
//推荐使用第二种，第二种中已经包含第一种
//mysql 驱动 5.1.6 以后可以无需 Class.forName(“com.mysql.jdbc.Driver”);
//DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Class.forName("com.mysql.jdbc.Driver");//固定写法
Connection connection= DriverManager.getConnection(url,name,password);
//connection代表数据库，数据库对象
//数据库设置自动提交
//事务提交
//事务回滚
connection.rollback();
connection.commit();
connection.setAutoCommit();
URL
String url ="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//mysql 默认3306
//协议://主机地址:端口号/数据库名？参数1&参数2&参数3
/*
characterEncoding=utf8&useSSL=true
字面意思是：使用Unicode字符集并且设置字符编码为utf-8
我所理解的是：通过这段代码来设置数据库的字符集为Unicode、编码规则为utf-8。假设项目本身的字符集和编码规则为gbk。当往数据库存数据时，项目中的数据通过gbk编码成字节，再通过数据库的utf-8解码成字符串。通过这样的过程将数据存到数据库中。从数据库取数据反之即可。
*/
//Oracle -- 1521
//jdbc:oralce:thin:@localhost:1521:sid
在数据库链接后面加上useSSL=false的参数！
jdbc:mysql://ip:端口号/数据库名?useUnicode=true&characterEncoding=UTF-8&useSSL=false
参数说明：
- useUnicode=true意思是使用unicode字符集；
- characterEncoding=UTF-8意识是使用UTF-8的编码；
- useSSL=false关闭SSL验证（这也是导致报错问题的关键，不加上该参数会默认进行SSL验证）
statement 执行SQL的对象
statement 和 PrepareStatement 都是执行SQL的对象
String sql="SELECT * FROM users";//编写Sql
statement.executeQuery(); //查询操作，返回 ResultSet
statement.execute(); //执行任何SQL （执行所有SQL所以效率相对来说低）
statement.executeUpdate(); //更新，插入，删除，都是用这个。返回一个受影响的行数
ResultSet 查询的结果集：封装了所以的查询结果
获得指定的数据类型
ResultSet resultSet = statement.executeQuery(sql);//返回的结果集,结果集中封装了我们全部查询的结果
resultSet.getObject();//在不知道列类型下使用
resultSet.getString();//如果知道则指定使用
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
遍历,指针
resultSet.next(); //移动到下一个数据
resultSet.afterLast();//移动到最后
resultSet.beforeFirst();//移动到最前面
resultSet.previous();//移动到前一行
resultSet.absolute(row);//移动到指定行
释放内存
resultSet.close();
statement.close();
connection.close();
//耗资源
10.5、了解statement对象
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可
- Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sq|语句， executeUpdate执行完后， 将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
- Statement.executeQuery方法用于向数据库发生查询语句，executeQuery方法返回代表查询结果的ResultSet对象
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作：
Statement statement = connection.createStatement();
String sql = "delete from user where id =1";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("删除成功");
}
CURD操作-update
使用executeUpdate(String sql)方法完成数据修改操作，示例操作：
Statement statement = connection.createStatement();
String sql = "update user set name ='' where name = ''";
int num = statement.executeUpdate(sql);
if(num>0){
System.out.println("修改成功");
}
CURD操作-read
使用executeQuery(String sql)方法完成数据查询操作，示例操作：
Statement statement = connection.createStatement();
String sql = "select * from user where id =1";
ResultSet rs= statement.executeQuery(sql);
if(rs.next()){
System.out.println("");
}
10.6、优化JDBC程序
代码实现
-
提取工具类
-
配置类
一般会把配置类单独写个db.properties
driver=com.mysql.jdbc.Driver
url =jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
name = root
password = 123456
1.创建工具类
package com.shi.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
public static void main(String[] args) {
System.out.println(url+"-------"+username+"----------"+password);
}
private static String driver =null;
private static String url = null;;
private static String username = null;
private static String password = null;
static {
try{
//db.properties 在src下，可以直接通过反射获得
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("name");
password=properties.getProperty("password");
//1.驱动只用加载一次
// Class.forName(driver);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnection() throws Exception{
return DriverManager.getConnection(url, username, password);
}
//3.释放资源
public static void release(Connection conn, 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 (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
2.编写增删改的方法 exectueUpdate
2.添加
package com.shi.lesson02;
import com.shi.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) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();//获取连接
st = conn.createStatement();//获取SQL执行对象
String sql = "INSERT INTO users(id,`NAME`,`PASSWORD`,`email`,`birthday`) " +
"VALUES(7,'kuangshen','123456','[email protected]','2020-01-01')";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
3.删除
-
package com.shi.lesson02; import com.shi.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) throws SQLException { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection();//获取连接 st = conn.createStatement();//获取SQL执行对象 String sql = "DELETE FROM users WHERE id = 7"; int i = st.executeUpdate(sql); if (i > 0) { System.out.println("删除成功"); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); } } } package com.shi.lesson02; import com.shi.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) throws SQLException { Connection conn = null; Statement st = null; ResultSet rs = null; try { conn = JdbcUtils.getConnection();//获取连接 st = conn.createStatement();//获取SQL执行对象 String sql = "DELETE FROM users WHERE id = 7"; int i = st.executeUpdate(sql); if (i > 0) { System.out.println("删除成功"); } } catch (Exception e) { e.printStackTrace(); } finally { JdbcUtils.release(conn,st,rs); } } }
4.修改
package com.shi.lesson02;
import com.shi.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) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
st = conn.createStatement();
String sql = "UPDATE users SET `name` = '石硕' WHERE id = 7";
int i = st.executeUpdate(sql);
if (i>0){
System.out.println("修改成功");
}
} catch (Exception e) {
e.printStackTrace();
} finally {
JdbcUtils.release(conn,st,rs);
}
}
}
5.查
package com.shi.lesson02;
import com.shi.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) throws SQLException {
Connection conn = null;
Statement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();;//获取连接
st = conn.createStatement();//获取SQL执行对象
String sql = "SELECT * FROM users WHERE id = 1";
rs = st.executeQuery(sql);//查询完毕返回结果集
if (rs.next()) {
System.out.println(rs.getString("Name"));//查询字段name且id=1
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.7、SQL注入问题
sql存在漏洞，会被攻击导致数据泄露 SQL会被拼接 or
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static com.kuang.lesson02.utils.JdbcUtils.getConnection;
public class SQL注入 {
public static void main(String[] args) {
//login("kuangshen","123456"); 正常登录
//SQL注入，获得所有信息
login("' or '1=1","' or '1=1"); //技巧，漏洞
}
public static void login(String username,String password){
Connection conn =null;
Statement st = null;
ResultSet rs =null;
try {
conn = JdbcUtils.getConnection();//获取连接
st = conn.createStatement();//获取SQL执行对象
//SELECT * FROM users WHERE 'Name' = 'kuangshen' AND 'password' = '123456'
//SELECT * FROM users WHERE 'Name' = '' or '1=1' AND 'password' = '' or '1=1'
String sql = "select * from users where `NAME`='"+ username +"' AND `PASSWORD`='"+ password +"'" ;
rs=st.executeQuery(sql);//查询完毕返回结果集
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.8、PreparedStatement对象
prepared (有准备的)
PreparedStatement 可以防止SQL注入 ，效率更高
1、添加
package com.shi.lesson02;
import com.shi.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st =null; //Statement子类
try {
conn = JdbcUtils.getConnection();
//区别
//使用 ? 占位符代替参数
String sql = "insert into `users`(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
st = conn.prepareStatement(sql);//预编译sql，先写sql然后不执行
//手动赋值，1代表下标（第一个？），4代表插入的值
st.setInt(1,3);
st.setString(2,"qing");
st.setString(3,"987654321");
st.setString(4,"[email protected]");
//注意点导包： sql.Date()在数据库中 java.sql.Date()，但是无法直接获取
// util.Date 在Java中 是 new Deta().getTime()获得时间戳
// 用数据库的转换java的时间戳
st.setDate(5,new java.sql.Date(new Date().getTime()));
//
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("插入成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
2、删除
package com.shi.lesson02;
import com.shi.utils.JdbcUtils;
import java.sql.*;
import java.util.Date;
public class TestSelect {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st =null; //Statement子类
try {
conn = JdbcUtils.getConnection();
//区别
//使用 ? 占位符代替参数
String sql = "delete from `users` where id=?";
st = conn.prepareStatement(sql);//预编译sql，先写sql然后不执行
//手动赋值，1代表下标（第一个？），4代表插入的值
st.setInt(1,4);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("删除成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,null);
}
}
}
3、改（更新
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st =null; //Statement子类
try {
conn = JdbcUtils.getConnection();
//区别
//使用 ? 占位符代替参数
String sql = "update users set'NAME'=? where id=?"
st = conn.prepareStatement(sql);//预编译sql，先写sql然后不执行
//手动赋值，1代表下标（第一个？），4代表插入的值
st.setString(1,"狂神")
st.setInt(2,1);
//执行
int i = st.executeUpdate();
if (i>0){
System.out.println("更新成功");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(comm,st,null);
}
}
}
4、查询
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st =null; //Statement子类
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
//区别
//使用 ? 占位符代替参数
String sql = "select * from users where id=?"
st = conn.prepareStatement(sql);//预编译sql，先写sql然后不执行
//手动赋值，1代表下标（第一个？），4代表插入的值
st.setString(1,1)
//执行
int i = st.executeQuery();
if (i>0){
System.out.println(rs.getString("NAME"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(comm,st,rs);
}
}
}
5、SQL注入
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import static com.kuang.lesson02.utils.JdbcUtils.getConnection;
public class SQL注入 {
public static void main(String[] args) {
//login("kuangshen","123456"); 正常登录
//SQL注入，获得所有信息
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();//获取连接
//prepareStatement() 防止 SQL 注入的本质，就是把传递进来的参数当做字符
//假设其中存在转义字符，比如说 ' 会被直接转义
String sql = "select * from users where `NAME`=? and 'PASSWORD'=?"; //Mybatis
st = conn.prepareStatement();//获取SQL执行对象
st.setString(1,username);
st.setString(2,password);
rs=st.executeQuery();
while (rs.next()){
System.out.println(rs.getString("NAME"));
System.out.println(rs.getString("password"));
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
prepareStatement() 防止 SQL 注入的本质，就是把传递进来的参数当做字符
假设其中存在转义字符，比如说 ’ 会被直接转义
10.9、使用IDEA连接数据库
注意：提前导入工具包
- 选择数据库
连接不上，查看原因
- 查看表信息：双击数据库目录打开
- 更新数据：输入需要更改的信息，点击上方
DB保存 - 编写sql代码的地方
10.10、事务
事务的概述 （ps：按ctrl键点击跳转
要么都成功，要么都失败
ACID原则
原子性：要么全部完成，要么都不完成
一致性：结果总数不变
隔离性：多个进程互不干扰
持久性：一旦提交不可逆，持久化到数据库了
隔离性的问题
脏读： 一个事务读取了另一个没有提交的事务
不可重复读：在同一个事务内，重复读取表中的数据，表发生了改变
虚读（幻读）：在一个事务内，读取到了别人插入的数据，导致前后读出来的结果不一致
代码实现
- 开启事务
conn.setAutoCommit(false); - 一组业务执行完毕，提交事务
- 可以在catch语句中显示的定义回滚语句，但是默认失败也会回滚
import com.kuang.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Action {
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-100 where name = 'A'";
st =conn.prepareStatement(sql);
st.executeUpdate();
// int x = 1/0; 故意报错
String sql2 = "update account set money = money+100 where name = 'B'";
st=conn.prepareStatement(sql2);
st.executeUpdate();
//业务完毕，提交事务
conn.commit();
System.out.println("操作成功");
} catch (Exception e) {
//如果不写，若失败，也会回滚
try {
conn.rollback();//如果失败则回滚事务
} catch (SQLException e1) {
e1.printStackTrace();
}
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10.8、数据库连接池
数据库连接–执行完毕–释放
连接–释放 （十分浪费资源）
池化技术： 准备一些预先的资源，过来就连接预先准备好的
常用连接数：10
常用连接数：10
- 最少连接数：10
- 最大连接数 ： 15 业务最高承载上限
- 排队等待，等待超时：100ms
编写连接池，实现一个接口 DateSource
开源数据源实现(拿来即用)
-
DBCP
-
C3P0
-
Druid：阿里巴巴
使用了这些数据库连接池之后，我们在项目开发中就不需要编写连接数据库的代码了
DBCP
需要用到的jar包commons-dbcp-1.4.jar 和 commons-pool-1.6.jar
DBCP拥有自己的配置文件，数据名字都是固定的，不能随便修改
配置文件:
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true
name=root
password=123456
#<!-- 初始化连接 -->
initialSize=10
#最大连接数量
maxActive=50
#<!-- 最大空闲连接 -->
maxIdle=20
#<!-- 最小空闲连接 -->
minIdle=5
#<!-- 超时等待时间以毫秒为单位 6000毫秒/1000等于60秒 -->
maxWait=60000
#JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：【属性名=property;】
#注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
connectionProperties=useUnicode=true;characterEncoding=UTF8
#指定由连接池所创建的连接的自动提交（auto-commit）状态。
defaultAutoCommit=true
#driver default 指定由连接池所创建的连接的只读（read-only）状态。
#如果没有设置该值，则“setReadOnly”方法将不被调用。（某些驱动并不支持只读模式，如：Informix）
defaultReadOnly=
#driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
#可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
defaultTransactionIsolation=READ_UNCOMMITTED
创建DBCP工具类，只需要修改两步
package com.shi.lesson05;
import com.shi.utils.JdbcUtils;
import org.apache.commons.dbcp.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{
//db.properties 在src下，可以直接通过反射获得
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties properties = new Properties();
properties.load(in);
//创建数据源， BasicDataSourceFactory工厂模式-> 创建对象
dataSource = BasicDataSourceFactory.createDataSource(properties);
} catch (Exception e) {
e.printStackTrace();
}
}
//2.获取连接
public static Connection getConnection() throws Exception{
return dataSource.getConnection();
}
//3.释放资源
public static void release(Connection conn, 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 (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
然后将工具类换成JdbcUtils_DBCP就可以了
使用只需要激活conn = JdbcUtils_DBCP.getConnection();
C3P0
需要的jar包：c3p0-0.9.5.5.jar 和 mchange-commons-java-0.2.19.jar
C3P0拥有自己的配置文件，可以添加不同的数据源
创建C3P0工具类
只需要改一个位置
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static ComboPooledDataSource dataSource = null;
static {
try{
//1.配置方法分两种
//代码版配置 (不建议使用这种)
//dataSource = new ComboPooledDataSource();
//dataSource.setDriverClass();
//dataSource.setUser();
//...
//配置文件写法
dataSource = new ComboPooledDataSource("MySQL");
} catch (Exception e) {
e.printStackTrace();
}
}
//获取连接
public static Connection getConnection() throws Exception{
// 这是固定的端口，不需要修改
return dataSource.getConnection();
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet rs) throws SQLException {
if(rs!=null){
try{
rs.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
if (st!=null){
try{
st.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
if(conn!=null){
try{
conn.close();
}catch (SQLException e) {
e.printStackTrace();
}
}
}
}
结论
无论使用什么数据源，本质是不变的，DateSource接口不会变，方法就不会变
扩展：Apache 软件基金会
标签：--,数据库,MySQL,student,sql,NULL,SELECT From： https://www.cnblogs.com/Nuff5a1d/p/17201910.html