一、初识MyAQL
数据库操作的常用指令
net start mysql57 -- 开启服务
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gITCQuEO-1635928016905)(MySQL.assets/image-20211028102420227.png)]
net stop mysql57 -- 关闭服务
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ybCoRsN3-1635928016907)(MySQL.assets/image-20211028102530270.png)]
mysql -uroot -proot -- 进入mysql
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-w4KwR6fA-1635928016909)(MySQL.assets/image-20211028102659670.png)]
show databases; -- 展示所有的数据库
每输入完一个指令都必须要用;结尾
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XWUy1Yoh-1635928016911)(MySQL.assets/image-20211028103316278.png)]
use student -- 切换数据库，使用use命令
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-PF3F5OpH-1635928016914)(MySQL.assets/image-20211028103507867.png)]
show tables; -- 查看该数据库中所有的表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AQC4bKBs-1635928016915)(MySQL.assets/image-20211028103657073.png)]
describe student; -- 显示该表中所以信息
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-cPbSh7JZ-1635928016917)(MySQL.assets/image-20211028103943806.png)]
create database text; -- 创建一个数据库
show databases;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LOnUjCkK-1635928016918)(MySQL.assets/image-20211028104421048.png)]
exit; -- 退出链接
-- 表示单行注释
/*
多行注释
*/
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i0EMmElk-1635928016920)(MySQL.assets/image-20211028104747891.png)]
二、操作数据库
对数据库的操作 --> 对数据库中表的操作 --> 对表中字段的操作
1、结构化语句查询分类
| 名称 | 解释 | 命令 |
|---|---|---|
| DDL （数据定义语言） | 定义和管理数据对象，如数据库，数据表等 | CREATE、DROP、ALTER |
| DML （数据操作语言） | 用于操作数据库对象中所包含的数据 | INSERT、UPDATE、DELETE |
| DQL （数据查询语言） | 用于查询数据库数据 | SELECT |
| DCL （数据控制语言） | 用于管理数据库的语言，包括管理权限及数据更改 | GRANT、commit、rollback |
2、操作数据库(了解)
sql语句不区分大小写
1.1、创建数据库
create database [if not exit] test1;
1.2、删除数据库
drop database [if exit]test1;
1.3、进入数据库
use test1; -- 当数据库名，表名，字段名是特殊字符时+``
1.4、查看所以数据库
show databases;
3、数据库的列类型
数值型
-
整形
- tinyint 十分小的数据 1个字节
- smallint 较小的数据 2个字节
- mediumint 中等大小的数据 3个字节
- int 标准的整数 4个字节
- bigint 较大的数据 8个字节
-
浮点型
-
float 浮点数 4个字节
-
double 浮点数 8个字节
-
decimal 字符串形式的浮点数 一般在金融计算时使用
-
字符串
-
char 字符串固定大小 0~255
-
varchar 可变字符串 0~65535 用于常用变量
-
tinytext 微型文本 2^8-1
-
**text 文本串 2^16-1 ** 用于保存大文本
时间日期
对应java.util.Data类
- data YYYY-MM-DD 日期格式
- time HH: mm: ss 时间格式
- datatime YYYY-MM-DD HH: mm: ss 最常用的时间格式
- **timestamp 时间戳 1970.1.1 到现在的毫秒数！ 也较为常用 **
- year 年份表示
null
-
没有值，未知
-
注意，不要使用null进行运算，如果算了，结果为null
4、数据库的字段属性(重点)
4.1、UnSigne(无符号)
- 无符号的
- 声明该数据列不允许出现负数
4.2、ZEROFILL
- 0填充的
- 不足位数用0来填充，如int(3),5则为005
4.3、Auto_InCrement(自增)
- 自动增长的，每添加一条数据，自动在上一个记录上加1
- 通常用于设置主键，且为 整数类型
4.4、NULL和NOT NULL
- 默认为NULL，即没有插入该列数值
- 如果设置为NOT NULL,则该列必须要有值
4.5、DEFAULT(默认值)
- 默认的
- 用于设置默认值
- 例如,性别字段,默认为"男" , 若无指定该列的值 , 则默认值为"男"的值
5、创建数据库表(重点)
-- 目标 : 创建一个school数据库
-- 创建学生表(列,字段)
-- 学号int 登录密码varchar(20) 姓名,性别varchar(2),出生日期(datatime),家庭住
址,email
-- 创建表之前 , 一定要先选择数据库
CREATE TABLE IF NOT EXISTS `student` (
`id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` varchar(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` varchar(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` datetime DEFAULT NULL COMMENT '生日',
`address` varchar(100) DEFAULT NULL COMMENT '地址',
`email` varchar(50) DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
总结
-- 创建表的一般格式
create table [判断] `表名`(
`字段名` 列类型 [属性] [索引] [注释],
`字段名` 列类型 [属性] [索引] [注释],
......
`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]
常用的语句
show create database school; -- 查看数据库定义
show create table student; -- 查看数据表定义
desc student; -- 显示表结构
6、数据表的类型
设置数据表的类型
- MyISAM : 节约空间及相应速度
- InnoDB : 安全性 , 事务处理及多用户操作数据表
| 名称 | MyISAM | InnoDB |
|---|---|---|
| 事务处理 | 不支持 | 支持 |
| 数据行锁定 | 不支持 | 支持 |
| 外键约束 | 不支持 | 支持 |
| 全文索引 | 支持 | 不支持 |
| 表空间大小 | 教小 | 较大，约 2 倍！ |
数据表的存储位置
-
MySQL数据表以文件方式存放在磁盘中
- 包括表文件 , 数据文件 , 以及数据库的选项文件
- 位置 : Mysql安装目录\data\下存放数据表 . 目录名对应数据库名 , 该目录下文件名对应数据表
-
注意：
- InnoDB类型数据表只有一个 *.frm文件 , 以及上一级目录的ibdata1文件
- MyISAM类型数据表对应三个文件 :
- *.frm --表结构定义文件
- *.MYD --数据文件(data)
- *.MYI --索引文件(index)
设置表数据字符集
我们可为数据库,数据表,数据列设定不同的字符集，设定方法 :
-
create table 表名()charset = utf8; -
还可以根据MySQL数据库配置文件my.ini中进行参数设定
7、修改和删除表
7.1、修改表
-- 修改表名
-- alter table 旧表名 rename as 新表名;
alter table teacher rename as teacher1;
-- 给表增加字段
-- alter table 表名 add 字段名 列属性;
alter table teacher1 add name varchar(11);
-- 修改字段
-- 修改约束：alter table 表名 modify 字段名 新的列属性;
alter table teacher1 modify name int(11);
-- 修改字段名：alter table 表名 change 旧字段名 新字段名 [列属性];
alter table teacher1 change name name1 varchar(11);
-- 删除字段
-- alter table 表名 drop 字段名;
alter table teacher1 drop name1;
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mTzfFkL1-1635928016921)(MySQL.assets/image-20211028204737701.png)]
7.2、删除表
-- drop table if exists 表名;(如果表存在再删除)
drop table if exists teacher1;
一般在创建和删除表时进行一个判断，以免程序报错
三、MySQL的数据管理
1、外键（了解即可）
外键的作用
保持数据一致性，完整性，主要目的是控制存储在外键表中的数据,约束。 使两张表形成关联，外键只能引用外表中的列的值或使用空值。
创建外键
-- 创建外键的方式一 : 创建子表同时创建外键
-- 年级表 (id\年级名称)
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 `student` (
`studentno` INT(4) NOT NULL COMMENT '学号',
`studentname` VARCHAR(20) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`sex` TINYINT(1) DEFAULT '1' COMMENT '性别',
`gradeid` INT(10) DEFAULT NULL COMMENT '年级',
`phoneNum` VARCHAR(50) NOT NULL COMMENT '手机',
`address` VARCHAR(255) DEFAULT NULL COMMENT '地址',
`borndate` DATETIME DEFAULT NULL COMMENT '生日',
`email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
`idCard` VARCHAR(18) DEFAULT NULL COMMENT '身份证号',
PRIMARY KEY (`studentno`),
KEY `FK_gradeid` (`gradeid`),
CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 创建外键方式二 : 创建子表完毕后,修改子表添加外键
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`
(`gradeid`);
总结 :
- 删除具有主外键关系的表时 , 要先删子表 , 后删主表
- 以上操作都是物理外键，数据库级别的外键，我们不建议使用！
- 数据库就是单纯的表，只用来存储数据，只有行(数据)和列(字段)
- 我们想使用外键(用程序去实现)
2、DML语言（全部记住）
**数据库的意义：**数据存储，数据管理
DML语言：数据操作语言
- insert 添加
- update 修改
- delete 删除
2.1、添加（insert）
语法:
insert into `表名`(`字段1`,`字段2`...)
values('值1','值2'...),('值1','值2'...)...;
注意：
- 字段或值之间用英文逗号隔开
- 字段1,字段2…’ 该部分可省略 , 但添加的值务必与表结构,数据列,顺序相对应,且数量一致 .
- 可同时插入多条数据 , values 后用英文逗号隔开
insert into `student`(`name`,`sex`,`email`)
-> values('曹昕怡','女','1036619617@qq.com'),('张楠','男','hua321sb@qq.com');
2.2、修改（update）
语法：
update `表名`
set `字段名1`='值1',`字段名2`='值2',...
where -- 条件判断
注意事项：
- 条件，筛选的条件，如果没有指定，则会修改所有列
- ‘值’，是一个具体的值，也可以是一个变量
update `student`
-> set `birthday`=current_time -- 这里的current_time是变量
-> where `id`=1;
测试：
-- 将id为1的数据的name改为狂神
update `student`
-> set `name`='狂神'
-> where `id`=1;
-- 将id为1的数据sex改为女并且email改为123@qq.com
update `student`
-> set `sex`='女',`email`='123@qq.com'
-> where `id`=1;
where条件子句
| 运算符 | 含义 | 范围 | 结果 |
|---|---|---|---|
| = | 等于 | 5=6 | false |
| <> 或 ！= | 不等于 | 5!=6 | true |
| > | 大于 | 5>6 | false |
| < | 小于 | 5<6 | true |
| >= | 大于等于 | 5>=6 | false |
| <= | 小于等于 | 5<=6 | true |
| between | 在某个范围之间 | BETWEEN 5 AND 10 | |
| and （&&） | 并且 | 5 > 1 AND 1 > 2 | false |
| or （||） | 或 | 5 > 1 OR 1 > 2 | true |
update `student`
-> set `name`='华哥'
-> where `sex`='男' and `address`='西安';
-- 把表中性别是男并且住址在西安的那一行的姓名改成华哥
2.3、删除（delete）
delete命令
-- 语法:
-- delete from `表名` where 筛选条件;
-- 删除id=1的数据
delete from `student`
-> where `id`=1;
**注意：**如果不指定筛选条件，则删除该表所有列的数据
truncate命令
作用：用于完全清空表数据 , 但表结构 , 索引 , 约束等不变 ;
-- 新建一个测试表
create table `test`(
-> `id` int(4) not null auto_increment,
-> `coll` varchar(20) not null,
-> primary key(`id`)
-> )engine=innodb default charset=utf8;
-- 填入数据
create into `test`(`coll`)
-> values ('w1'),('w2'),('w3');
-- 删除表中所有数据
truncate table test;
语法
truncate table 表名;
注意：delete和turncate的区别与联系
- 相同：都能删除数据，不能删除表结构，但truncate速度更快
- 不同：
- 使用truncate table会重置auto_increment计数器
- 使用turncate table不会对事务有影响
四、DQL查询数据(最重点)
1、DQL
(Data Query Language:数据查询语言)
- 所有查询操作都用它 Select
- 简单的查询，复杂的查询他都能做~
- 数据库中最核心、最重要的语言
- 使用频率最高的语言
select 语法
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2] -- 联合查询
[WHERE ...] -- 指定结果需满足的条件
[GROUP BY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过滤分组的记录必须满足的次要条件
[ORDER BY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
2、指定查询字段
-- 查询学生表的全部信息
select * from `student`;
-- 查询指定字段信息
select `studentno`,`studentname` from `student`;
-- 别名，可以给字段起个别名 或者 表起个别名
select `studentno` as 学号,`studentname` as 姓名 from `student` as a
-- 函数 concat(a,b) 将a,b字符串连接
select concat('姓名是：',`studentname`) as 新名字 from `student`
语法：select 字段,... from 表
有时候，列名不是那么的见名知意，我们起别名 as 字段名 as 别名 / 表名 as 别名
去重 distinct
作用：去除select查询出来的重复的数据。
-- 查询成绩表中有几个人参加了考试
select distinct `studentno` from `result`;
数据库的列（表达式）
-- selcet查询中可以使用表达式
SELECT @@auto_increment_increment; -- 查询自增步长
SELECT version(); -- 查询版本号
SELECT 100*3-1 AS 计算结果; -- 表达式
-- 学员考试成绩集体提分一分查看
SELECT studentno,studentresult+1 AS '提分后' FROM result;
数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量…
语法：select 表达式 from 表
3、Where条件子句
作用：用于检索数据表中符合条件的记录
搜索条件可由一个或多个逻辑表达式 组成 ，结果一般为布尔值
逻辑操作符
| 操作符名称 | 语法 | 描述 |
|---|---|---|
| AND 或 && | a AND b 或 a && b | 逻辑与，同时为真结果才为真 |
| OR 或 || | a OR b 或 a||b | 逻辑或，只要一个为真，则结果为真 |
| NOT 或 ！ | NOT a 或 ！a | 逻辑非，若操作数为假，则结果为真！ |
测试
-- 查询考试成绩在80-100之间
select `studentno`,`studentresult`
-> from `result`
-> where `studentresult`>=80 and `studentresult`<=100;
-- and也可以写成&&
-- 模糊查询
select `studentno`,`studentresult`
-> from `result`
-> where `studentresult` between 80 and 100;
-- 查询除1000号同学以外，其他同学的成绩
select `studentno`,`studentresult`
-> from `result`
-> where `studentno`!=1000;
-- 使用not
select `studentno`,`studentresult`
-> from `result`
-> where not `studentno`=1000;
模糊查询：比较操作符
| 操作符名称 | 语法 | 描述 |
|---|---|---|
| IS NULL | a IS NULL | 若操作符为NULL，则结果为真 |
| IS NOT NULL | a IS NOT NULL | 若操作符不为NULL，则结果为真 |
| between | a BETWEEN b AND c | 若 a 范围在 b 与 c 之间，则结果为真 |
| like | a LIKE b | SQL 模式匹配，若a匹配b，则结果为真 |
| in | a IN (a1，a2，a3， ) | 若 a 等于 a1,a2. 中的某一个，则结果为真 |
测试：
-- 模糊查询 between and \ like \ in \ null
-- =============================================
-- LIKE
-- =============================================
-- 查询姓刘的同学的学号及姓名
-- like结合使用的通配符 : % (代表0到任意个字符) _ (一个字符)
-- 查询学生表里姓张的同学
select `studentno`,`studentname`
-> from `student`
-> where `studentname` like '张%';
-- 查询学生表里名字有一个少字的同学
select `studentno`,`studentname`
-> from `student`
-> where `studentname` like '%少%';
-- 查询学生表里姓任且只有 两个字的同学
select `studentno`,`studentname`
-> from `student`
-> where `studentname` like '任_';
-- 查询学生表里家住在陕西的同学
select `studentno`,`studentname`
-> from `student`
-> where `address` like '陕西%';
-- =============================================
-- IN
-- =============================================
-- 查询学号为1000,1001,1002的学生姓名
select `studentno`,`studentname`
-> from `student`
-> where `studentno` in('1000','1001','1002');
-- =============================================
-- NULL 空
-- =============================================
-- 查询出生日期没有填写的同学
-- 不能直接写=NULL , 这是代表错误的 , 用 is null
-- 查询地址不为空的学生
select `studentno`,`studentname`
-> from `student`
-> where `address` is not null;
4、联表查询
join 对比
| 操作符名称 | 描述 |
|---|---|
| inner join | 如果表中有至少一个匹配，则返回行 |
| left join | 即使右表中没有匹配，也从左表中返回所有的行 |
| right join | 即使左表中没有匹配，也从右表中返回所有的行 |
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-LgI6lkEu-1635928016923)(MySQL.assets/image-20211030174734220.png)]
测试：
/*
连接查询
如需要多张数据表的数据进行查询,则可通过连接运算符实现多个查询
内连接 inner join
查询两个表中的结果集中的交集
外连接 outer join
左外连接 left join
(以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充)
右外连接 right join
(以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充)
等值连接和非等值连接
自连接
*/
-- 查询参加了考试的同学信息(学生姓名,科目名称,分数)
/*思路:
(1):分析需求,确定查询的列来源于三个类,student subject result,连接查询
(2):确定使用哪种连接查询?(内连接)
*/
select studentname as '姓名',subjectname as '科目',studentresult as '期末成绩'
-> from student as l
-> right join result as r -- 右连接
-> on l.studentno=r.studentno
-> inner join `subject` as s
-> on r.subjectno=s.subjectno; -- 内连接
-- 查出未参加考试的学生的姓名
select `studentname`
-> from `student` as l
-> left join `result` as r
-> on l.`studentno`=r.`studentno`
-> where `studentresult` is null;
内连接和外连接的区别：
- 左连接会读取左边数据表的全部数据，即使右边数据表没有对应数据。(如果两个表中数据有相同部分，只显示一个)
- 右连接会读取右边数据表的全部数据，即使左边数据表没有对应数据。(如果两个表中数据有相同部分，只显示一个)
- inner join 获取的就是两个表中的交集部分
自连接
/*
自连接
数据表与自身进行连接
需求:从一个包含栏目ID , 栏目名称和父栏目ID的表中
查询父栏目名称和其他子栏目名称
*/
-- 创建一个表
CREATE TABLE `category` (
`categoryid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主题id',
`pid` INT(10) NOT NULL COMMENT '父id',
`categoryName` VARCHAR(50) NOT NULL COMMENT '主题名字',
PRIMARY KEY (`categoryid`)
) ENGINE=INNODB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8
-- 插入数据
INSERT INTO `category` (`categoryid`, `pid`, `categoryName`)
VALUES('2','1','信息技术'),
('3','1','软件开发'),
('4','3','数据库'),
('5','1','美术设计'),
('6','3','web开发'),
('7','5','ps技术'),
('8','2','办公信息');
-- 编写SQL语句,将栏目的父子关系呈现出来 (父栏目名称,子栏目名称)
-- 核心思想:把一张表看成两张一模一样的表,然后将这两张表连接查询(自连接)
SELECT a.categoryName AS '父栏目',b.categoryName AS '子栏目'
FROM category AS a,category AS b
WHERE a.`categoryid`=b.`pid`
5、分页和排序
测试：
/*============== 排序 ================
语法 : ORDER BY
ORDER BY 语句用于根据指定的列对结果集进行排序。
ORDER BY 语句默认按照ASC升序对记录进行排序。
如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。
*/
-- 查询高等数学 的所有考试结果(学号 学生姓名 科目名称 成绩)
-- 按成绩降序排序
select l.`studentno` as '学号',`studentname` as '姓名',`subjectname` as '科目',`studentresult` as '成绩'
from `student` as l
right join `result` as r
on l.`studentno`=r.`studentno`
left join `subject` as s
on r.`subjectno`=s.`subjectno`
where `subjectname` like '高等%'
order by `studentresult` desc;
语法：order by 需要排序字段 desc or asc
/*============== 分页 ================
推导:
第一页 : limit 0,5
第二页 : limit 5,5
第三页 : limit 10,5
......
第N页 : limit (pageNo-1)*pageSzie,pageSzie
[pageNo:页码,pageSize:单页面显示条数]
*/
-- 每行显示2条数据
select l.`studentno` as '学号',`studentname` as '姓名',`subjectname` as '科目',`studentresult` as '成绩'
-> from `student` as l
-> right join `result` as r
-> on l.`studentno`=r.`studentno`
-> left join `subject` as s
-> on r.`subjectno`=s.`subjectno`
-> where `subjectname` like '高等%'
-> order by `studentresult` desc
-> limit 0,2;
语法：limit(查询起始的下标,pageSize)
6、子查询
/*============== 子查询 ================
什么是子查询?
在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句
嵌套查询可由多个子查询组成,求解的方式是由里及外;
子查询返回的结果一般都是集合,故而建议使用IN关键字;
*/
练习：
-- 课程为 高等数学-2且分数不小于80分的学生的学号和姓名
SELECT studentno,studentname FROM student WHERE studentno IN(
SELECT studentno FROM result WHERE StudentResult>=80 AND subjectno=(
SELECT subjectno FROM `subject` WHERE subjectname = '高等数学-2'
)
)
7、分组和过滤
查询不同课程的平均分,最高分,最低分,且平均分必须大于80
select `subjectname` as '科目名称',avg(studentresult) as '平均分',max(`studentresult`) as '最高分',min(`studentresult`) as '最低分'
from `result` as l
inner join `subject` as r
on l.`subjectno`=r.`subjectno`
group by `subjectname`
having 平均分>=80;
五、MySQL函数
1、常用函数（不是很常用）
1.1、数学函数
select abs(-8); -- 绝对值
select ceiling(9.4); -- 向上取整
select floor(9.4); -- 向下取整
select rand(); -- 随机数,返回一个0-1之间的随机数
select sign(0); -- 符号函数: 负数返回-1,正数返回1,0返回0
1.2、字符串函数
select char_length('cascsac'); -- 返回字符串包含的字符数
select cancat('i','love','you'); -- 合并字符串,参数可以有多个
select insert('我是你的父亲',4,1,'亲爱的'); -- 替换字符串,从某个位置开始替换某个长度 我是你的亲爱的父亲
select lower('KuangShen'); -- 转化为小写
select upper('kuangshen')l -- 转化为大写
SELECT LEFT('hello,world',5); /*从左边截取*/
SELECT RIGHT('hello,world',5); /*从右边截取*/
SELECT REPLACE('狂神说坚持就能成功','坚持','努力'); /*替换字符串*/
SELECT SUBSTR('狂神说坚持就能成功',4,6); /*截取字符串,开始和长度*/
SELECT REVERSE('狂神说坚持就能成功'); /*反转
-- 查询姓周的同学,改成邹
SELECT replace(studentname,'周','邹') AS 新名字
FROM student WHERE studentname LIKE '周%';
1.3、时间和日期函数
-- 记住一个就好 SELECT NOW();
SELECT CURRENT_DATE(); /*获取当前日期*/
SELECT CURDATE(); /*获取当前日期*/
SELECT NOW(); /*获取当前日期和时间*/
SELECT LOCALTIME(); /*获取当前日期和时间*/
SELECT SYSDATE(); /*获取当前日期和时间*/
-- 获取年月日,时分秒
SELECT YEAR(NOW());
SELECT MONTH(NOW());
SELECT DAY(NOW());
SELECT HOUR(NOW());
SELECT MINUTE(NOW());
SELECT SECOND(NOW());
1.4、系统信息函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
2、聚合函数（常用）
| 函数名称 | 描述 |
|---|---|
| count() | 返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】 |
| SUM() | 返回数字字段或表达式列作统计，返回一列的总和。 |
| AVG() | 通常为数值字段或表达列作统计，返回一列的平均值 |
| MAX() | 可以为数值字段，字符字段或表达式列作统计，返回最大的值。 |
| MIN() | 可以为数值字段，字符字段或表达式列作统计，返回最小的值。 |
-- count()的使用
SELECT COUNT(studentname) FROM student; -- 查找studentname列的数据个数
SELECT COUNT(*) FROM student; -- 查找整张表的数据个数
SELECT COUNT(1) FROM student; /*推荐*/
三者的区别：
- count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null的记录
- count(*) 包括了所有的列，相当于行数，在统计结果的时候，包含字段为null 的记录；
- count(1) 用1代表代码行，在统计结果的时候，包含字段为null 的记录 。
/*
下面它们之间的一些对比：
1）在表没有主键时，count(1)比count(*)快
2）有主键时，主键作为计算条件，count(主键)效率最高；
3）若表格只有一个字段，则count(*)效率较高。
*/
SELECT SUM(StudentResult) AS 总和 FROM result;
SELECT AVG(StudentResult) AS 平均分 FROM result;
SELECT MAX(StudentResult) AS 最高分 FROM result;
SELECT MIN(StudentResult) AS 最低分 FROM result;
-- 练习：查询不同课程的平均分,最高分,最低分,且平均分必须大于80
select `subjectname` as '科目名称',avg(studentresult) as '平均分',max(`studentresult`) as '最高分',min(`studentresult`) as '最低分'
from `result` as l
inner join `subject` as r
on l.`subjectno`=r.`subjectno`
group by `subjectname`
having 平均分>=80;
3、数据库级别的MD5加密（扩展）
3.1、简介
MD5即Message-Digest Algorithm 5（信息-摘要算法5），用于确保信息传输完整一致。是计算机广泛使用的杂凑算法之一（又译摘要算法、哈希算法），主流编程语言普遍已有MD5实现。将数据（如汉 字）运算为另一固定长度值，是杂凑算法的基础原理，MD5的前身有MD2、MD3和MD4。
3.2、实现数据加密
新建一个表testmd5
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,'kuangshen','123456'),(2,'qinjiang','456789')
插入一条加密的数据
insert into testmd5 values(3,'renshaohua',md5('123456'));
查询登录用户信息（md5对比使用，查看用户输入加密后的密码进行比对）
select * from testmd5 where name='renshaohua' and pwd=md5('123456');
六、事务
1、概述
什么是事务
- 事务就是将一组SQL语句放在同一批次内去执行
- 如果一个SQL语句出错，则此批次内的所有SQL都将被取消执行
- MySQL事务处理只支持InnoDB和BDB数据表类型
事务原则：ACID原则 原子性 、一致性、隔离性、持久性
原子性（Atomicity）：
要么都成功，要么都失败。
一致性（Consistency）：
事务前后的数据完整性要保持一致。
隔离性（Isolation）：
事务的隔离性是多个用户并发访问数据库时，数据库为每个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离。
持久性（Durability）：
事物一旦提交不可逆，被持久化到数据库中
隔离所导致的一些问题
- **脏读：**指一个事务读取到了另外一个事务未提交的数据
- **不可重复读：**在一个事务内读取表中的某一行数据，多次读取结果不同
- **虚读(幻读)：**在一个事务内读取到了别的事务插入的数据，导致前后读取不一致
2、事务实现
基本语法：
-- 使用set语句来改变自动提交模式
SET autocommit = 0; /*关闭*/
SET autocommit = 1; /*开启*/
-- 注意:
-- 1.MySQL中默认是自动提交
-- 2.使用事务时应先关闭自动提交
-- 开始一个事务,标记事务的起始点
start transaction
-- 提交一个事务给数据库
commit
-- 将事务回滚,数据回到本次事务的初始状态
rollback
-- 还原MySQL数据库的自动提交
SET autocommit =1;
-- 保存点(了解即可)
SAVEPOINT 保存点名称 -- 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 -- 回滚到保存点
RELEASE SAVEPOINT 保存点名称 -- 删除保存点
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ECwQ1Uf2-1635928016924)(MySQL.assets/image-20211031164231139.png)]
3、测试题目
/*
课堂测试题目
A在线买一款价格为500元商品,网上银行转账.
A的银行卡余额为2000,然后给商家B支付500.
商家B一开始的银行卡余额为10000
创建数据库shop和创建表account并插入2条数据
*/
-- 创建数据库
CREATE DATABASE `shop`CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `shop`;
-- 创建表 DECIMAL(小数点前有几位,小数点后有几位)
CREATE TABLE `account` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(32) NOT NULL,
`cash` DECIMAL(9,2) NOT NULL, PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
-- 给表中添加两组数据
INSERT INTO account (`name`,`cash`)
VALUES('A',2000.00),('B',10000.00)
-- 转账实现
SET autocommit = 0; -- 关闭自动提交
START TRANSACTION; -- 开始一个事务,标记事务的起始点
UPDATE account SET cash=cash-500 WHERE `name`='A';
UPDATE account SET cash=cash+500 WHERE `name`='B';
COMMIT; -- 提交事务
# rollback;
SET autocommit = 1; -- 恢复自动提交
七、索引
1、索引的分类
1.1、索引的作用
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的连接 , 实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时 , 可以显著减少分组和排序的时间
- 全文检索字段进行搜索优化.
1.2、分类
- 主键索引（Primary Key）
- 唯一索引（Unique）
- 常规索引（Index）
- 全文索引 （FullText）
2、主键索引（Primary Key）
主键：某一个属性组能唯一标识一条记录
特点：
- 最常见的索引类型
- 确保数据记录的唯一性
- 确定特定数据记录在数据库中的位置
3、唯一索引（Unique）
作用：避免同一个表中某数据列中值得重复
与主键索引的区别：
- 主键索引只能有一个
- 唯一索引可以有多个
CREATE TABLE `Grade`(
`GradeID` INT(11) AUTO_INCREMENT PRIMARYKEY,
`GradeName` VARCHAR(32) NOT NULL UNIQUE
-- 或 UNIQUE KEY `GradeID` (`GradeID`)
)
4、常规索引（Index）
作用：快速定位特定数据
注意：
- index和key关键字都可以设置常规索引
- 不宜添加太多常规索引,影响数据的插入,删除和修改操作
-- 创建表时添加
CREATE TABLE `result`(
-- 省略一些代码
INDEX/KEY `ind` (`studentNo`,`subjectNo`) -- 创建表时添加
)
-- 创建后添加
ALTER TABLE `result` ADD INDEX `ind`(`studentNo`,`subjectNo`);
5、全文索引（FullText）
作用：快速定位特定数据
注意：
- 只能用于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 版本、存储引擎和数据类型是否支持全文索引。
*/
关于 EXPLAIN ： https://blog.csdn.net/jiadajing267/article/details/81269067
6、测试索引（拓展）
建表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), '24736743@qq.com', 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();
索引效率测试
- 无索引测试
SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';
EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 992759 -- 一共查了992759次
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
- 有索引测试
-- 创建索引
CREATE INDEX idx_app_user_name ON app_user(name);
mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: app_user
partitions: NULL
type: ref
possible_keys: idx_app_user_name
key: idx_app_user_name
key_len: 203
ref: const
rows: 1 -- 一共查了1次
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
7、索引准则
- 索引不是越多越好
- 不要对经常变动的数据加索引
- 小数据量的表一般不加索引
- 一般给查找条件的字段加索引
8、索引的数据结构
-- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
/*
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 等索引；
*/
关于索引的本质：http://blog.codinglabs.org/articles/theory-of-mysql-index.html
八、权限管理
1、用户管理
基本命令
/* 用户和权限管理 */ ------------------
用户信息表：mysql.user
-- 刷新权限
FLUSH PRIVILEGES
-- 增加用户 CREATE USER kuangshen IDENTIFIED BY '123456'
CREATE USER 用户名 IDENTIFIED BY [PASSWORD] 密码(字符串)
-- 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
-- 只能创建用户，不能赋予权限。
-- 用户名，注意引号：如 'user_name'@'192.168.1.1'
-- 密码也需引号，纯数字密码也要加引号
-- 要在纯文本中指定密码，需忽略PASSWORD关键词。要把密码指定为由PASSWORD()函数返回的混编值，需包含关键字PASSWORD
-- 重命名用户 RENAME USER kuangshen TO kuangshen2
RENAME USER old_user TO new_user
-- 设置密码
SET PASSWORD = PASSWORD('密码') -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码
-- 删除用户 DROP USER kuangshen2
DROP USER 用户名
-- 分配权限/添加用户
GRANT 权限列表 ON 表名 TO 用户名 [IDENTIFIED BY [PASSWORD] 'password']
-- all privileges 表示所有权限
-- *.* 表示所有库的所有表
-- 库名.表名 表示某库下面的某表
-- 查看权限 SHOW GRANTS FOR root@localhost;
SHOW GRANTS FOR 用户名
-- 查看当前用户权限
SHOW GRANTS; 或 SHOW GRANTS FOR CURRENT_USER; 或 SHOW GRANTS FOR
CURRENT_USER();
-- 撤消权限
REVOKE 权限列表 ON 表名 FROM 用户名
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 用户名 -- 撤销所有权限
2、MySQL备份
数据库备份的必要性
- 保证重要数据不丢失
- 数据转移
MySQL数据库备份方法
- mysqldump备份工具
- 数据库管理可视化工具
- 直接拷贝数据库文件和相关配置文件
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W6UB6Xzs-1635928016926)(MySQL.assets/image-20211031200605759.png)]
-- 导出
1. 导出一张表 -- mysqldump -uroot -p123456 school student >D:/a.sql
mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表 -- mysqldump -uroot -p123456 school student result >D:/a.sql
mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表 -- mysqldump -uroot -p123456 school >D:/a.sql
mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库 -- mysqldump -uroot -p123456 -B school >D:/a.sql
mysqldump -u用户名 -p密码 -B 库名 > 文件名(D:/a.sql)
-- 导入
1. 在登录mysql的情况下： -- source D:/a.sql
source 备份文件
2. 在不登录的情况下
mysql -u用户名 -p密码 库名 < 备份文件
九、规范化数据库设计
1、为什么需要数据库设计
糟糕的数据库设计：
- 数据沉余，存储空间浪费
- 数据更新和插入的异常
- 程序性能差
良好的数据库设计：
- 节省数据的存储空间
- 能够保证数据的完整性
- 方便进行数据库应用的系统开发
软件项目开发周期中的数据库设计：
- 需求分析阶段: 分析客户的业务和数据处理需求
- 概要设计阶段:设计数据库的E-R模型图 , 确认需求信息的正确和完整.
2、三大范式
第一范式
第一范式的目标是确保每列的原子性,如果每列都是不可再分的最小数据单元,则满足第一范式
第二范式
第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一 范式（1NF）。
第二范式要求每个表只描述一件事情
第三范式
如果一个关系满足第二范式,并且除了主键以外的其他列都不传递依赖于主键列,则满足第三范式. 第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
规范化和性能的关系：
为满足某种商业目标 , 数据库性能比规范化数据库更重要在数据规范化的同时 , 要综合考虑数据库的性能
通过在给定的表中添加额外的字段,以大量减少需要从中搜索信息所需的时间
通过在给定的表中插入计算列,以方便查询
十、JDBC（重点）
1、数据库驱动
2、JDBC介绍
SUN公司为了简化、统一对数据库的操作，定义了一套Java操作数据库的规范（接口），称之为JDBC。 这套接口由数据库厂商去实现，这样，开发人员只需要学习jdbc接口，并通过jdbc加载具体的驱动，就 可以操作数据库。
JDBC全称为：Java Data Base Connectivity（java数据库连接），它主要由接口组成。组成JDBC的２个包：java.sql、javax.sql
开发JDBC应用需要以上2个包的支持外，还需要导入相应JDBC的数据库实现(即数据库驱动)。
3、数据库驱动的下载与导入
3.1、下载
https://downloads.mysql.com/archives/c-j/
解压之后
3.2、导入
- 先建立lib文件夹
- 将jar包复制进去
- 在右击lib文件夹 --> Add as Library -->OK
导入成功的标志：
导入的jar包有下拉菜单
4、编写JDBC程序
搭建实验环境
CREATE DATABASE jdbcStudy CHARACTER SET utf8 COLLATE utf8_general_ci;
USE jdbcStudy;
CREATE TABLE users(
id INT PRIMARY KEY,
NAME VARCHAR(40),
PASSWORD VARCHAR(40),
email VARCHAR(60),
birthday DATE
);
INSERT INTO users(id,NAME,PASSWORD,email,birthday)
VALUES(1,'zhansan','123456','zs@sina.com','1980-12-04'),
(2,'lisi','123456','lisi@sina.com','1981-12-04'),
(3,'wangwu','123456','wangwu@sina.com','1979-12-04');
新建一个java工程，并导入数据驱动
编写 程序从user表中读取数据，并打印在命令行窗口
package com.hua.lesson01;
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 {
//要连接的数据库URL
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
//连接的数据库时使用的用户名
String username = "root";
//连接的数据库时使用的密码
String password = "123456";
//1.加载驱动
Class.forName("com.mysql.jdbc.Driver");//推荐使用这种方式来加载驱动
//2.获取与数据库的链接
Connection conn = DriverManager.getConnection(url,username,password);
//3.获取用于向数据库发送sql语句的statement
Statement st = conn.createStatement();
String sql = "select * from users";
//4.向数据库发sql,并获取代表结果集的resultset
ResultSet rs = st.executeQuery(sql);
//5.取出结果集的数据
while(rs.next()){
System.out.println("id=" + rs.getObject("id"));
System.out.println("name=" + rs.getObject("name"));
System.out.println("password=" + rs.getObject("password"));
System.out.println("email=" + rs.getObject("email"));
System.out.println("birthday=" + rs.getObject("birthday"));
}
//6.关闭链接，释放资源
rs.close();
st.close();
conn.close();
}
}
5、对象说明
DriverManager类的讲解
Jdbc程序中的DriverManager用于加载驱动，并创建与数据库的链接，这个API的常用方法：
// 加载驱动
Class.forName("com.mysql.jdbc.Driver");
//获取与数据库的链接
Connection conn = DriverManager.getConnection(url,username,password);
URL讲解
String url = "jdbc:mysql://localhost:3306/jdbcStudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
// mysql -- 3306
// 协议:主机地址:端口号/数据库名?参数1&参数2&参数3
Connection类讲解
这个类可以完成所有 数据库层面的工作，比如事务相关的，它代表着数据库
connection.getAutoCommit()//事务的自动提交
connection.commit();//确认
connection.rollback();//回滚
Statement类的讲解 执行SQL的对象
Jdbc程序中的Statement对象用于向数据库发送SQL语句， Statement对象常用方法：
- executeQuery(String sql) ：用于向数据发送查询语句。
- executeUpdate(String sql)：用于向数据库发送insert、update或delete语句
- execute(String sql)：用于向数据库发送任意sql语句
- addBatch(String sql) ：把多条sql语句放到一个批处理中
- executeBatch()：向数据库发送一批sql语句执行。
ResultSet类讲解 查询的结果集 ：封装了 所有的 查询结果
获得指定的数据类型
resultSet.getObject(); //获得全部类型
//获得数据库指定类型
resultSet.getInt();
resultSet.getDate();
resultSet.getFloat();
resultSet.getString();
...
ResultSet还提供了对结果集进行滚动的方法：
- next()：移动到下一行
- Previous()：移动到前一行
- absolute(int row)：移动到指定行
- beforeFirst()：移动resultSet的最前面。
- afterLast() ：移动到resultSet的最后面。
释放资源
Jdbc程序运行完后，切记要释放程序在运行过程中，创建的那些与数据库进行交互的对象，这些对象通 常是ResultSet, Statement和Connection对象，特别是Connection对象，它是非常稀有的资源，用完后必须马上释放，如果Connection不能及时、正确的关闭，极易导致系统宕机。Connection的使用原则 是尽量晚创建，尽量早的释放。
6、statement对象
Jdbc中的statement对象用于向数据库发送SQL语句，想完成对数据库的增删改查，只需要通过这个对象，向数据库发送增删改查语句即可
Statement对象的executeUpdate方法，用于向数据库发送增、删、改的sql语句，executeUpdate执行 完后，将会返回一个整数（即增删改语句导致了数据库几行数据发生了变化）。
Statement.executeQuery方法用于向数据库发送查询语句，executeQuery方法返回代表查询结果的ResultSet对象。
CRUD操作-create
使用executeUpdate(String sql)方法完成数据添加操作，示例操作：
Stratement st = coon.createStatement;
String sql = "insert into user(...) values(...)";
int num = st.executeUpdate(sql);
if(num>0){
System.out.println("插入成功!!!");
}
CRUD操作-delete
使用executeUpdate(String sql)方法完成对数据库删除操作，示例操作：
Stratement 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)方法完成对数据库修改的操作，示例操作：
Stratement st = cnno.createStatement;
String sql = "update `user` set name='' where name=''";
int num=st.executeUpdate(sql);
if(num>0){
System.out.println("修改成功!!!");
}
CRUD操作-select
使用executeQuery(String sql)方法完成数据库查询操作，示例操作：
Statement st = coon.createStatement();
String sql = "select * from user where id=1";
ResultSet rs = st.executeQuery(sql);
while(rs.next()){
//根据获取列的数据类型，分别调用rs的相应的方法映射到java对象中
}
SQL注入问题
通过巧妙的技巧来拼接字符串，造成SQL短路，从而获取数据库数据
package com.hua.lesson02;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class Sql注入 {
public static void main(String[] args) {
System.out.println("请输入用户名，密码");
Scanner scanner = new Scanner(System.in);
Scanner scanner1 = new Scanner(System.in);
String name = scanner.nextLine();
String password = scanner1.nextLine();
login(name,password); //lohin( or'1=1,123456)
scanner.close();
}
public static void login(String name, String password){
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
st = coon.createStatement();
String sql = "select `name`,`password` from users where `name`='"+name+"' and `password`='"+password+"'";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("用户名:"+rs.getString("NAME"));
System.out.println("密码:"+rs.getString("PASSWORD"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(coon,st,rs);
}
}
}
7、使用Jdbc对数据库增删改查
1、新建一个lesson02的包
2、在src目录下创建一个db.properties文件：
/*
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcStudy?
useUnicode=true&characterEncoding=utf8&useSSL=true
username=root
password=123456
*/
3、在lesson02下建一个utils包，创建一个类JdbcUtils
package com.hua.lesson02.utils;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;
public class JdbcUtils {
private static String driver = null;
private static String url = null;
private static String username = null;
private static String password = null;
static {
try {
//读取db.properties文件中的数据库连接信息
InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("dp.properties");
Properties prop = new Properties();
prop.load(in);
//获取数据库连接驱动
driver = prop.getProperty("driver");
//获取数据库连接URL地址
url = prop.getProperty("url");
//获取数据库连接用户名
username = prop.getProperty("username");
//获取数据库连接密码
password = prop.getProperty("password");
//加载数据库驱动
Class.forName(driver);
}catch (Exception e){
throw new ExceptionInInitializerError(e);
}
}
//获取数据库连接对象
public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(url,username,password);
}
//释放资源
public static void release(Connection conn, Statement st, ResultSet rs){
if (rs!=null){
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
rs = null;
}
if (st!=null){
try {
st.close();
} catch (SQLException e) {
e.printStackTrace();
}
st = null;
}
if (conn!=null){
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
conn = null;
}
}
}
使用statement对象完成对数据库的CRUD操作
1、插入一条数据
package com.hua.lesson02;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class InsertTest {
public static void main(String[] args) {
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
//获取数据库连接
coon = JdbcUtils.getConnection();
//通过coon对象获取负责执行SQL命令的Statement对象
st = coon.createStatement();
String sql ="insert into users(id,name,password,email,birthday) values(4,'任少华','123456','1036619617@qq.com','1999-01-26')";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("插入成功!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
//Sql执行完成之后释放相关资源
JdbcUtils.release(coon,st,rs);
}
}
}
2、更新一条信息
package com.hua.lesson02;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
public class UpdateTest {
public static void main(String[] args) {
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
st = coon.createStatement();
String sql = "update users set name='renshoahua' where id=1";
int sum = st.executeUpdate(sql);
if (sum>0){
System.out.println("更新成功!!!");
}
} catch (Exception e) {
e.printStackTrace();
}finally {
JdbcUtils.release(coon,st,rs);
}
}
}
3、删除一条信息
package com.hua.lesson02;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class DeleteTest {
public static void main(String[] args) {
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
st = coon.createStatement();
String sql = "delete from users where id=4";
int num = st.executeUpdate(sql);
if (num>0){
System.out.println("删除成功!!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(coon,st,rs);
}
}
}
4、查看一条信息
package com.hua.lesson02;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class SelectTest {
public static void main(String[] args) {
Connection coon = null;
Statement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
st = coon.createStatement();
String sql = "select NAME from users where id=1";
rs = st.executeQuery(sql);
while (rs.next()){
System.out.println("名字："+rs.getString("NAME"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(coon,st,rs);
}
}
}
8、PreparedStatement对象
PreperedStatement是Statement的子类，它的实例对象可以通过调用Connection.preparedStatement()方法获得，相对于Statement对象而言：PreperedStatement可以避 免SQL注入的问题。
Statement会使数据库频繁编译SQL，可能造成数据库缓冲区溢出。
PreparedStatement可对SQL进行预编译，从而提高数据库的执行效率。并且PreperedStatement对于sql中的参数，允许使用占位符的形式进行替换，简化sql语句的编写。
使用PreparedStatement对象完成数据库的CRUD操作
1、插入数据
package com.hua.lesson03;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class InsertTest {
public static void main(String[] args) {
Connection coon = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
//要执行的SQL命令，SQL中的参数使用?作为占位符
String sql = "insert into users(`id`,`name`,`password`,`email`,`birthday`) values(?,?,?,?,?)";
st = coon.prepareStatement(sql);
//为SQL语句中的参数赋值，注意，索引是从1开始的
st.setInt(1,5);
st.setString(2,"曹昕怡");
st.setString(3,"123456");
st.setString(4,"1036619617@qq.com");
st.setString(5,"2000-01-01");
int num = st.executeUpdate();
if (num>0){
System.out.println("插入成功!!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(coon,st,rs);
}
}
}
2、删除一条数据
package com.hua.lesson03;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class DeleteTest {
public static void main(String[] args) {
Connection coon = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
coon = JdbcUtils.getConnection();
String sql = "delete from users where id=?";
st = coon.prepareStatement(sql);
st.setInt(1,4);
int num = st.executeUpdate();
if(num>0){
System.out.println("删除成功!!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(coon,st,rs);
}
}
}
3、更新一条数据
package com.hua.lesson03;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class UpdateTest {
public static void main(String[] args) {
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "update users set name =? where id=?";
st = conn.prepareStatement(sql);
st.setString(1,"任少华");
st.setInt(2,5);
int num = st.executeUpdate();
if (num>0){
System.out.println("更新成功!!!");
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
4、查看一条数据
package com.hua.lesson03;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class SelectTest {
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("名字:"+rs.getString("name"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
避免SQL注入
package com.hua.lesson03;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class Sql注入 {
public static void main(String[] args) {
System.out.println("请输入用户名: ");
Scanner scanner1 = new Scanner(System.in);
String name = scanner1.nextLine();
System.out.println("请输入密码: ");
Scanner scanner2 = new Scanner(System.in);
String password = scanner2.nextLine();
login(name,password);
}
public static void login(String name,String password){
Connection conn = null;
PreparedStatement st = null;
ResultSet rs = null;
try {
conn = JdbcUtils.getConnection();
String sql = "select `name`,`password` from users where `name`=? and `password`=?";
st = conn.prepareStatement(sql);
st.setString(1,name);//避免注入的重点代码
st.setString(2,password);
rs = st.executeQuery();
while (rs.next()){
System.out.println("姓名: "+rs.getString("name"));
System.out.println("密码: "+rs.getString("password"));
}
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
**原理：**执行的时候参数会用引号包起来，并把参数中的引号作为转义字符，从而避免了参数也作为条件 的一部分
9、事务
概念
事务指逻辑上的一组操作，组成这组操作的各个单元，要不全部成功，要不全部不成功
ACID原则 隔离问题
在大纲六已经讲过了
代码测试
/*创建账户表*/
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(40),
money FLOAT
);
/*插入测试数据*/
insert into account(name,money) values('A',1000);
insert into account(name,money) values('B',1000);
insert into account(name,money) values('C',1000);
当Jdbc程序向数据库获得一个Connection对象时，默认情况下这个Connection对象会自动向数据库提交 在它上面发送的SQL语句。若想关闭这种默认提交方式，让多条SQL在一个事务中执行，可使用下列的JDBC控制事务语句:
- Connection.setAutoCommit(false);//开启事务(start transaction)
- Connection.rollback();// 回 滚 事 务 (rollback)
- Connection.commit();//提交事务(commit)
编写程序
1、模拟转账成功的业务场景
package com.hua.lesson04;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
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`-? where `name`=?";
st = conn.prepareStatement(sql1);
st.setInt(1,500);
st.setString(2,"A");
st.executeUpdate();
String sql2 = "update `account` set `money`=`money`+? where `name`=?";
st = conn.prepareStatement(sql2);
st.setInt(1,500);
st.setString(2,"B");
st.executeUpdate();
conn.commit();
System.out.println("转账成功!!");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
2、模拟转账过程中出现异常导致有一部分SQL执行失败后让数据库自动回滚事务
package com.hua.lesson04;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
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`-? where `name`=?";
st = conn.prepareStatement(sql1);
st.setInt(1,500);
st.setString(2,"A");
st.executeUpdate();
int x = 1/0;//错误代码
String sql2 = "update `account` set `money`=`money`+? where `name`=?";
st = conn.prepareStatement(sql2);
st.setInt(1,500);
st.setString(2,"B");
st.executeUpdate();
conn.commit();
System.out.println("转账成功!!");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10、数据库连接池
看资料的10.8数据库连接池，这里就不做笔记了，没运行起来
n;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
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`-? where `name`=?";
st = conn.prepareStatement(sql1);
st.setInt(1,500);
st.setString(2,"A");
st.executeUpdate();
String sql2 = "update `account` set `money`=`money`+? where `name`=?";
st = conn.prepareStatement(sql2);
st.setInt(1,500);
st.setString(2,"B");
st.executeUpdate();
conn.commit();
System.out.println("转账成功!!");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
2、模拟转账过程中出现异常导致有一部分SQL执行失败后让数据库自动回滚事务
```java
package com.hua.lesson04;
import com.hua.lesson02.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class TestTransaction1 {
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`-? where `name`=?";
st = conn.prepareStatement(sql1);
st.setInt(1,500);
st.setString(2,"A");
st.executeUpdate();
int x = 1/0;//错误代码
String sql2 = "update `account` set `money`=`money`+? where `name`=?";
st = conn.prepareStatement(sql2);
st.setInt(1,500);
st.setString(2,"B");
st.executeUpdate();
conn.commit();
System.out.println("转账成功!!");
} catch (SQLException e) {
e.printStackTrace();
}finally {
JdbcUtils.release(conn,st,rs);
}
}
}
10、数据库连接池
看资料的10.8数据库连接池，这里就不做笔记了，没运行起来
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
