MySQL

转载

mb5ff592736e0cf 2021-10-08 09:48:00

文章标签 sql java 数据数据库 mysql 文章分类 Java 后端开发

MySQL

1.初识MySQL

MySQL_java
MySQL_sql_02

MySQL_数据库_03
MySQL_mysql_04
MySQL_数据_05
MySQL_java_06

mysql安装

mysql安装方法
MySQL_sql_07
MySQL_数据库_08

sqlyog安装及使用

MySQL_mysql_09

MySQL_java_10

MySQL_数据_11
MySQL_数据_12
MySQL_mysql_13
MySQL_sql_14
MySQL_sql_15
MySQL_java_16

连接数据库，命令行

mysql -uroot -p123456 --连接数据库

MySQL_数据_17

--修改用户密码
update mysql.user set authentication_string=password('123456') where user='root' and Host = 'localhost'; 
flush privileges; --刷新权限

-- 所有的语句都使用；结尾
show databases; -- 查询所有数据库

MySQL_数据_18

use school; --切换数据库

MySQL_数据库_19

show tables; --查看数据库中所有的表

MySQL_java_20

describe student; --显示表中所有信息

MySQL_sql_21

create database school2; -- 创建数据库

MySQL_mysql_22

exit --退出连接
-- sql本来的注释
/*
多行注释
*/

MySQL_java_23
MySQL_java_24

2.操作数据库

MySQL_mysql_25
mysql数据库不区分大小写

操作数据库

MySQL_sql_26

CREATE DATABASE IF NOT EXISTS school3;--创建数据库
DROP DATABASE IF EXISTS school2;--删除数据库
USE `school`；--切换数据库

SELECT `user` FROM student; --查询表中的字段

MySQL_数据库_27

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

MySQL_mysql_28

数据库的列数据类型

数值

整数：

tinyint 1字节
smallint 2字节
mediumint 3字节
int 4字节常用 java int
bigint 8字节

小数：

float 4字节
double 8字节
decimal 字符串形式的浮点数，金融计算使用

字符串

char 0-255 固定长度
varchar 0-65535 可变长字符串常用 java 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

数据库的字段属性

Unsigned:

无符号的整数
勾选表示该列不能为负数

Zerofill:

0填充
不足的位数用0填充：例如int（3）5 变成 005

自增：

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

非空：
null 和 not null

勾选便设置为not null 便必须赋值
不勾选默认为null,可以为空

默认：

设置默认值
例如：sex 默认值男

-- 每一个表都必须存在以下5个字段：
id -- 主键
`version` -- 乐观锁
is_delete -- 伪删除
gmt_create -- 创建时间
gmt_update -- 修改时间

创建数据库表，sql

CREATE TABLE IF NOT EXISTS `student2`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
`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

MySQL_sql_30

常用命令

-- 1.创建数据库的语句
SHOW CREATE DATABASE school; 
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
-- 2.创建表的语句
SHOW CREATE TABLE student1; 
CREATE TABLE `student1` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` VARCHAR(10) NOT NULL COMMENT '学生姓名',
  `sex` CHAR(2) NOT NULL DEFAULT '男' COMMENT '学生性别',
  PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
-- 3.查看表的结构
DESC student;

数据库引擎

INNODB:默认使用
MYISAM:早些年使用

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

MySQL_java_31
MySQL_mysql_32

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

CHARSET=utf8;

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

修改和删除表

修改：

-- 修改表
-- 修改表名
ALTER TABLE `student` RENAME `student0`; 
-- 增加表的字段
ALTER TABLE `student0` ADD `email` VARCHAR(50);
-- 修改表的字段（重命名，修改约束）
ALTER TABLE `student0` MODIFY `age` VARCHAR(5);
ALTER TABLE `student0` CHANGE `age` `age1` INT(10);
-- 删除表的字段
ALTER TABLE `student0` DROP `age1`;

删除：

-- 删除表
DROP TABLE IF EXISTS `student0`;

3.MYSQL的数据管理

外键了解即可

MySQL_mysql_33
方式一：

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

CREATE TABLE `student0` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `gradeid` INT(10) NOT NULL COMMENT '年级id',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

MySQL_sql_34
MySQL_数据_35
方式二：

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

CREATE TABLE `student0` (
  `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学生id',
  `name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '学生姓名',
  `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
  `sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `gradeid` INT(10) NOT NULL COMMENT '年级id',
  `birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
  `address` VARCHAR(100) DEFAULT NULL COMMENT '家庭住址',
  `email` VARCHAR(50) DEFAULT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

ALTER TABLE `student0`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

MySQL_mysql_36

DML语言

MySQL_java_37

添加

-- 插入语句，添加
-- insert into 表名(字段1，字段2，字段3,...) values('值1'，'值2'，'值3',...);
-- 主键自增可以省略
-- 语句太长可以分行写，只在行末加;
INSERT INTO `grade` (`gradename`) VALUES ('大四');
INSERT INTO `grade` (`gradename`) VALUES ('大三'),('大二');
INSERT INTO `student1`(`name`,`sex`)
VALUES('小芳','女'),('Anna','女');
INSERT INTO `student1` VALUES('7','小芳','女');

修改

-- 修改学员的名字,指定条件
UPDATE `student0` SET `name`='mingmao' WHERE `id`=1;
-- 修改学员的名字,不指定条件,会改动所有数据
UPDATE `student0` SET `name`='mingmao';
-- 修改多个属性
UPDATE `student0` SET `name`='mingmao0',`email`='2792178110@qq.com' WHERE `id`=2;

条件：where子句

操作符	含义	范围	结果
=	等于	5=6	false
<>或!=	不等于	5<>6	true
>	大于
<	小于
>=
<=
between...and...	在某个范围内	between 2 and 5	[2,5]
and		5>1 and 1>2	false
or		5>1 or 1>2	true

-- 通过多个条件定位数据
UPDATE `student0` SET `name`='mingmao123' WHERE `name`='mingmao' AND `sex`='女';

删除

delete命令：

-- 删除指定数据
DELETE FROM `student0` WHERE `id`=1;
-- 完全清空一个数据表的数据，表的结构和索引不变
TRUNCATE `student0`;

MySQL_java_38
MySQL_mysql_39

4.DQL查询数据

DQL

MySQL_java_40

指定查询字段

MySQL_数据_41

CREATE DATABASE IF NOT EXISTS `school`;
-- 创建一个school数据库
USE `school`;
-- 创建年级表
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 '年级编号',
    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 '考试成绩',
    PRIMARY KEY `subjectno` (`subjectno`)
)ENGINE = INNODB DEFAULT CHARSET = utf8;
-- 创建学生表
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;
-- 插入学生数据 其余自行添加 这里只添加了2行
INSERT INTO `student` (`studentno`,`loginpwd`,`studentname`,`sex`,`gradeid`,`phone`,`address`,`borndate`,`email`,`identitycard`)
VALUES
(1000,123456,'张伟',0,2,13800001234,'北京朝阳','1980-1-1','text123@qq.com',123456198001011234),
(1001,123456,'赵强',1,3,13800002222,'广东深圳','1990-1-1','text111@qq.com',123456199001011233);
-- 插入成绩数据  这里仅插入了一组，其余自行添加
INSERT INTO `result`(`studentno`,`subjectno`,`examdate`,`studentresult`)
VALUES
(1000,1,'2013-11-11 16:00:00',85),
(1000,2,'2013-11-12 16:00:00',70),
(1000,3,'2013-11-11 09:00:00',68),
(1000,4,'2013-11-13 16:00:00',98),
(1000,5,'2013-11-14 16:00:00',58);
-- 插入年级数据
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);

分段执行，然后出现如下四个表：
MySQL_java_42

-- 查询全部数据
-- 查询全部的学生
SELECT * FROM `student`;
-- 查询全部成绩
SELECT * FROM `result`;

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

-- 使用别名，对查询出的字段和表重命名
SELECT `studentno` AS 学号,`studentname` AS 学生姓名 FROM `student` AS 学生表;

-- 函数
-- 拼接字符串
SELECT CONCAT('姓名：',`studentname`) AS 学生姓名 FROM `student`;

去重

-- 查询一下哪些同学参加了考试，即有成绩
SELECT * FROM `result`; -- 查询全部的考试成绩
SELECT `studentno` FROM `result`; -- 查询哪些同学参加了考试
SELECT DISTINCT `studentno` FROM `result`;-- 去重查询哪些同学参加了考试

数据库的列（表达式）

-- 查询系统版本
SELECT VERSION(); -- 5.7.35-log 函数
-- 用来计算
SELECT 200*3-98 AS '计算结果'; -- 502 表达式
-- 查询自增的步长
SELECT @@auto_increment_increment; -- 1 变量

-- 学员考试成绩+10查看
SELECT `studentno`,`studentresult`+10 AS '提10分后成绩' FROM `result`;

where条件子句

检索数据中符合条件的值
逻辑运算符：

运算符	语法	描述
and &&	a and b 或者 a&&b	逻辑与
or \|\|	a or b 或者 a\|\|b	逻辑或
not !	not a 或者 !a	逻辑非

-- 查询所有学生的成绩
SELECT `studentno`,`studentresult` FROM `result`;
-- 查询成绩在95-100之间的学生成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult`>=95 AND `studentresult`<=100;
-- 查询成绩在95-100之间的学生成绩,模糊查询
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentresult` BETWEEN 80 AND 100;
-- 查询除了1000号学生之外的学生的成绩
SELECT `studentno`,`studentresult` FROM `result`
WHERE `studentno`!=1000; -- 写法一
SELECT `studentno`,`studentresult` FROM `result`
WHERE NOT `studentno`=1000; -- 写法二

模糊查询:比较运算符

运算符	语法	描述
is null	a is null	如果a为null，结果为true
is not null	a is not null	如果a不为null，结果为true
between...and...	a between b and c	如果a在b和c之间，结果为true
like	a like b	SQL匹配，如果a匹配到b,结果为true
in	a in (a1,a2,a3,...)	如果a在列表中，结果为true

-- 模糊查询

-- 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 
-- 查询学号为1000，1001的学员信息
SELECT * FROM `student`
WHERE `studentno` IN (1000,1001);
-- 查询在北京朝阳的学生
SELECT * FROM `student`
WHERE `address` IN ('北京朝阳');

-- null 和 not null
-- 查询地址非空的学员
SELECT * FROM `student`
WHERE `address` IS NOT NULL;
-- 查询姓名为空的学员
SELECT * FROM `student`
WHERE `studentname`='' OR `studentname` IS NULL;

联表查询

MySQL_java_43

MySQL_数据库_44

/*
1.分析需要哪些表？
2.确定需要哪种连接？7种
3.确定交叉点，这两个表中哪个数据是相同的
4.判断的条件 学生表 `studentno`=成绩表 `studentno`
*/
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
-- inner join
/*
select s.`studentno`,`studentname`,`subjectno`,`studentresult` 
from `student` as s
inner join `result` as r
where s.`studentno`=r.`studentno`;
*/
-- right join
/*
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `student` s -- 省略了as，下同
right JOIN `result` r
on s.`studentno`=r.`studentno`;
*/
-- left join
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `student` s -- 省略了as，下同
LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`;
-- 没参加考试的学生也查出来了

操作	描述
inner join	如果表中至少有一个匹配，就返回行
right join	会从右表中返回所有的值，即使左表中没有匹配
left join	会从左表中返回所有的值，即使右表中没有匹配

-- 查询缺考的同学
SELECT s.`studentno`,`studentname`,`subjectno`,`studentresult` 
FROM `student` s LEFT JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE `studentresult` IS NULL;

-- 查询参加考试的学生信息，学号，姓名，科目，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`;

练习：

-- 查询学生所属的年级，学号，姓名，年级
SELECT `studentno`,`studentname`,`gradename`
FROM `student` s
INNER JOIN `grade` g
ON s.`gradeid`=g.`gradeid`;
-- 查询科目所属的年级,科目，年级
SELECT `subjectname`,`gradename`
FROM `subject` sub
INNER JOIN `grade` g
ON sub.`gradeid`=g.`gradeid`;
-- 查询参加了高等数学-1和高等数学-2考试的学生信息，学号，姓名，科目名，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE sub.`subjectname`='高等数学-1' OR sub.`subjectname`='高等数学-2';

自连接

自己的表和自己的表连接，一张表拆为两张一样的表。

-- 创建表
CREATE TABLE IF NOT EXISTS `category` (
`categoryid` INT ( 3 ) NOT NULL COMMENT 'id',
`pid` INT ( 3 ) NOT NULL COMMENT '父id 没有父则为 1',
`categoryname` VARCHAR ( 10 ) NOT NULL COMMENT '种类名字',
PRIMARY KEY ( `categoryid` ) 
) ENGINE = INNODB CHARSET = utf8 COLLATE = utf8_general_ci;
-- 插入数据
INSERT INTO `category` ( `categoryid`, `pid`, `categoryname` )
VALUES
( 2, 1, '信息技术'),
( 3, 1, '软件开发'),
( 5, 1, '美术设计' ),
( 4, 3, '数据库' ),
( 6, 3, 'web开发' ),
( 7, 5, 'ps技术' );

MySQL_sql_45
父类表：

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

子类表：

pid	categoryid	categoryname
3	4	数据库
3	6	web开发
5	7	ps技术

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

父类	子类
软件开发	数据库
软件开发	web开发
美术设计	ps技术

-- 查询父子信息
SELECT p.`categoryname` AS '父栏目',s.`categoryname` AS '子栏目'
FROM `category` AS p,`category` AS s
WHERE p.`categoryid`=s.`pid`;

分页和排序

排序：

-- 分页和排序
-- 排序：升序asc和降序desc
-- 查询参加了高等数学-1和高等数学-2考试的学生信息，学号，姓名，科目名，分数，
-- 按分数升序排列
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE sub.`subjectname`='高等数学-1' OR sub.`subjectname`='高等数学-2'
ORDER BY `studentresult` ASC; -- 升序

分页：

-- 查询参加了高等数学-1和高等数学-2考试的学生信息，学号，姓名，科目名，分数，
-- 按分数升序排列
-- 分页，每页只显示1条数据 语法：limit 起始数据 页面的大小
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE sub.`subjectname`='高等数学-1' OR sub.`subjectname`='高等数学-2'
ORDER BY `studentresult` ASC -- 升序
LIMIT 0,1; -- 当前页是第1页，起始数据第0条，每页1条数据
-- limit 1,1; -- 当前页是第2页，起始数据第1条，每页1条数据
/*
网页：当前页，每页数据，总页数
当前页第n页，起始数据（n-1）*m,每页数据m,总页数 数据总数/m
*/

练习：

-- 查询 高等数学-1 学校排名前10，并且分数>80分的学生成绩，学号，姓名，科目，分数
SELECT s.`studentno`,`studentname`,`subjectname`,`studentresult`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
LEFT JOIN `subject` sub
ON sub.`subjectno`=r.`subjectno`
WHERE `subjectname`='高等数学-1' AND `studentresult`>80
ORDER BY `studentresult` DESC
LIMIT 0,10;

子查询和嵌套

-- where语句中嵌套子查询语句
-- 1.查询 高等数学-1 的所有考试结果，学号，科目编号，成绩，降序排列
-- 方式一：使用连接查询
SELECT `studentno`,r.`subjectno`,`studentresult`
FROM `result` r
INNER JOIN `subject` sub
ON r.`subjectno`=sub.`subjectno`
WHERE `subjectname`='高等数学-1'
ORDER BY `studentresult` DESC;
-- 方式二：使用子查询(由里及外)
SELECT `studentno`,`subjectno`,`studentresult`
FROM `result` 
WHERE`subjectno`=(
	SELECT `subjectno` FROM `subject` 
	WHERE `subjectname`='高等数学-1'
)
ORDER BY `studentresult` DESC;
-- 2.查询分数不小于80分的学生的学号和姓名
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
	SELECT `studentno` FROM `result` 
	WHERE `studentresult`>=80
);
-- 3.查询 高等数学-1 分数不小于80分的学生的学号和姓名
SELECT s.`studentno`,`studentname`
FROM `student` s
RIGHT JOIN `result` r
ON s.`studentno`=r.`studentno`
WHERE r.`studentresult`>=80 AND `subjectno`=(
	SELECT `subjectno` FROM `subject` 
	WHERE `subjectname`='高等数学-1'
);
-- 嵌套
-- 4.查询 高等数学-1 分数不小于80分的学生的学号和姓名
SELECT `studentno`,`studentname`
FROM `student`
WHERE `studentno` IN (
	SELECT `studentno` FROM `result` 
	WHERE `studentresult`>=80 AND `subjectno` = (
		SELECT `subjectno` FROM `subject`
		WHERE `subjectname`='高等数学-1'
	)
);

mysql函数

常用函数

-- 数学运算
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('fhjacdku2ed6789'); -- 返回字符串长度 15
SELECT CONCAT('abc','123'); -- 连接字符串 abc123
SELECT INSERT('我喜欢编程',2,2,'超级热爱'); -- 插入，替换 从第2 个字符开始替换，替换掉2个字符 我超级热爱编程
SELECT UPPER('sfhjjkDFG'); -- 转换成大写 SFHJJKDFG
SELECT LOWER('sfhjjkDFG'); -- 转换成小写 sfhjjkdfg
SELECT INSTR('sdfghjkl','gh'); -- 返回某字符串在字符串出现的位置 4
SELECT REPLACE('坚持就能成功','坚持','努力'); -- 替换字符串 努力就能成功
SELECT SUBSTR('坚持就能成功',2,4); -- 截取子字符串 持就能成 从第2个开始，截取4个字
SELECT REVERSE('sdfghjk'); -- 反转字符串 kjhgfds

-- 查询姓 张 的同学，改为姓 王
SELECT REPLACE(`studentname`,'张','王') FROM `student`
WHERE `studentname` LIKE '张%';

-- 时间和日期函数
SELECT CURRENT_DATE(); -- 获取当前日期 2021-10-02
SELECT CURDATE(); -- 获取当前日期 2021-10-02
SELECT NOW(); -- 获取当前时间 2021-10-02 13:08:14
SELECT LOCALTIME(); -- 获取本地时间 2021-10-02 13:08:14
SELECT SYSDATE(); -- 获取系统时间 2021-10-02 13:09:58
SELECT YEAR(NOW()); -- 年 2021
SELECT MONTH(NOW()); -- 月 10
SELECT DAY(NOW());-- 日 2
SELECT HOUR(NOW());-- 时 13
SELECT MINUTE(NOW());-- 分 13
SELECT SECOND(NOW());-- 秒 26

-- 系统
SELECT SYSTEM_USER(); -- 系统用户 root@localhost
SELECT USER(); -- 系统用户 root@localhost
SELECT VERSION(); -- 版本 5.7.35-log

聚合函数（常用）

函数名称	描述
count()	计数
sum()	求和
avg()	平均值
max()	最大值
min()	最小值

-- 聚合函数
-- 查询表中有多少条记录
SELECT COUNT(`studentno`) FROM `student`; -- 2 2个学生学号,null值不计算在内
SELECT COUNT(*) FROM `student`; -- 2 2个学生学号，null值计算在内
SELECT COUNT(1) FROM `student`; -- 2 2个学生学号，null值计算在内
-- 计算学生成绩的总和
SELECT SUM(`studentresult`) AS '成绩总和' FROM `result`; -- 379
-- 计算学生成绩的平均分
SELECT AVG(`studentresult`) AS '成绩平均分' FROM `result`; -- 75.8
-- 计算学生成绩的最高分
SELECT MAX(`studentresult`) AS '成绩最高分' FROM `result`; -- 98
-- 计算学生成绩的最低分
SELECT MIN(`studentresult`) AS '成绩最低分' FROM `result`; -- 58

-- 查询某门课程的平均分，最高分，最低分
SELECT AVG(`studentresult`) AS '平均分',MAX(`studentresult`) AS '最高分',MIN(`studentresult`) AS '最低分' 
FROM `result` r
INNER JOIN `subject` s
ON r.`subjectno`=s.`subjectno`
WHERE `subjectname`='高等数学-1';

分组与过滤

-- 分组与过滤
-- 查询不同课程的平均分，最高分，最低分，平均分大于60分的结果
SELECT `subjectname` AS '科目名称',AVG(`studentresult`) AS '平均分',MAX(`studentresult`) AS '最高分',MIN(`studentresult`) AS '最低分' 
FROM `result` r
INNER JOIN `subject` s
ON r.`subjectno`=s.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均分>=60;

数据库级别的MD5加密

MySQL_数据库_46

-- 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; -- 加密id=1的密码
UPDATE `testmd5` SET pwd=MD5(pwd); -- 加密所有密码,此时id=1的被加密了两次

-- 插入的时候加密
INSERT INTO `testmd5` VALUES(4,'小明',MD5('abc456'));

-- 校验：将用户传递的密码进行md5加密，比对加密后的值
SELECT * FROM `testmd5` WHERE `pwd`=MD5('123456');

select小结

MySQL_sql_47

6.事务

什么是事务

MySQL_数据库_48
MySQL_数据_49
MySQL_sql_50

执行事务

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

-- 手动处理事务
SET autocommit=0;
-- 事务开启
START TRANSACTION; -- 标记一个事务的开始,从这个开始的sql都在同一个事务内
INSERT xx
INSERT xx
-- 提交：持久化(成功)
COMMIT
-- 回滚：回到原来的样子（失败）
ROLLBACK
-- 事务结束
SET autocommit=1;
-- 设置事务的保存点
SAVEPOINT 保存点名
-- 回滚到保存点
ROLLBACK TO SAVEPOINT 保存点名
-- 撤销保存点
RELEASE SAVEPOINT 保存点名

模拟场景

-- 转账
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
USE shop;

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

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

-- 模拟转账：事务
SET autocommit=0;
START TRANSACTION;
UPDATE `account` SET `money`=`money`-500 WHERE `name`='A';
UPDATE `account` SET `money`=`money`+500 WHERE `name`='B';
COMMIT;
ROLLBACK;
SET autocommit=1;

7.索引

MySQL_数据库_51

索引的分类

MySQL_数据库_52

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

-- 显示所有的索引信息
SHOW INDEX FROM `student`;

-- 增加一个索引
ALTER TABLE school.`student` ADD FULLTEXT INDEX `studentname`(`studentname`);

-- 分析sql执行的状况
EXPLAIN SELECT * FROM `student`; 
EXPLAIN SELECT * FROM `student` WHERE MATCH(`studentname`) AGAINST('张');

测试索引

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

-- 插入100万条数据
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),'2792178110@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(); -- 1000000

SELECT * FROM `app_user`; -- 0.018 sec
SELECT * FROM `app_user` WHERE `name`='用户9999'; -- 0.602 sec
SELECT * FROM `app_user` WHERE `name`='用户999999'; -- 0.635 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户999999'; -- 992660rows

-- CREATE INDEX 索引名 ON 表（字段）
CREATE INDEX id_app_user_name ON `app_user`(`name`);

SELECT * FROM `app_user` WHERE `name`='用户999999'; -- 0.013 sec
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户999999'; -- 1rows

索引原则

MySQL_数据_53
MySQL_数据库_54

8.数据库权限管理和备份

用户管理

sqlyog可视化管理
MySQL_数据_55
MySQL_数据_56
MySQL_mysql_57
MySQL_sql_58
MySQL_数据库_59
MySQL_mysql_60
sql命令操作：

-- `mysql`-`user` 增删改查
-- 创建用户
CREATE USER mingmao IDENTIFIED BY '123456';
-- 修改密码,当前用户
SET PASSWORD=PASSWORD('123456');
-- 修改密码，指定用户
SET PASSWORD FOR mingmao =PASSWORD('123456');
-- 用户重命名
RENAME USER mingmao TO mingmao0;
-- 用户授权 库.表
-- 除了给其他用户授权，其他权限都有
GRANT ALL PRIVILEGES ON *.* TO mingmao0; -- 全部库的全部表
-- 查看权限
SHOW GRANTS FOR mingmao0; -- 查看指定用户的权限
SHOW GRANTS FOR root@localhost; -- 查看主机权限
-- 撤销权限
REVOKE ALL PRIVILEGES ON *.* FROM mingmao0;
-- 删除用户
DROP USER mingmao0;

数据备份

MySQL_mysql_61
sqlyog备份
导出：
MySQL_java_62
导入：
MySQL_mysql_63
命令行操作：

# 导出一张表
mysqldump -hlocalhost -uroot -p123456 school student >d:/a.sql
# 导出多张表
mysqldump -hlocalhost -uroot -p123456 school student result >d:/a.sql
# 导出数据库
mysqldump -hlocalhost -uroot -p123456 school >d:/a.sql
# 导入
mysql -uroot -p123456
use school;
source d:/a.sql

9.数据库的规约，三大范式

数据库设计

MySQL_sql_64
MySQL_sql_65
MySQL_sql_66
MySQL_sql_67
MySQL_数据_68
MySQL_sql_69
MySQL_数据库_70
MySQL_java_71
MySQL_mysql_72
MySQL_mysql_73

三大范式

MySQL_mysql_74
MySQL_sql_75
MySQL_sql_76

10.JDBC

数据库驱动

MySQL_mysql_77

JDBC

MySQL_java_78
MySQL_数据_79
MySQL_java_80
数据库驱动包
MySQL_mysql_81
MySQL_数据库_82
MySQL_数据库_83

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

MySQL_java_84

package com.mingmao.test1;

import java.sql.*;

public class JdbcTest01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        // 2.用户信息和url
        String url="jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username="root";
        String password="123456";
        // 3.连接成功,数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        // 4. 执行sql的对象
        Statement statement = connection.createStatement();
        // 5.执行sql的对象，执行sql,可能存在结果，查看返回结果
        String sql="SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println("id="+resultSet.getObject("id"));
            System.out.println("name="+resultSet.getObject("name"));
            System.out.println("password="+resultSet.getObject("password"));
            System.out.println("email="+resultSet.getObject("email"));
            System.out.println("birthday="+resultSet.getObject("birthday"));
        }
        // 6.释放链接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

MySQL_数据库_85
MySQL_数据库_86
MySQL_mysql_87
MySQL_数据_88
MySQL_数据库_89
MySQL_java_90

statement对象

MySQL_数据_91
MySQL_sql_92
MySQL_mysql_93
MySQL_数据_94

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

package com.mingmao.test2.utils;

import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.Properties;

public class JdbcUtils {
    private static String driver=null;
    private static String url=null;
    private static String username=null;
    private static String password=null;

    static{
        try{
            // 获得 db.properties中的内容
            InputStream resourceAsStream = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);

            // 读取内容
            driver=properties.getProperty("driver");
            url=properties.getProperty("url");
            username=properties.getProperty("username");
            password=properties.getProperty("password");

            // 1.驱动只用加载一次
            Class.forName(driver);
        } catch (IOException | ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(url, username, password);
    }
    // 释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.mingmao.test2;

import com.mingmao.test2.utils.JdbcUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class testInsert {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {
            // 1.获取数据库连接，创建数据库对象
            connection = JdbcUtils.getConnection();
            // 2. 执行sql的对象
            statement = connection.createStatement();
            // 3.执行sql,插入数据
            String sql="INSERT INTO `users`"+"VALUES (5,'小明','123456','xiaoming@qq.com','2010-01-01')";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }

    }
}

package com.mingmao.test2;

import com.mingmao.test2.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {
            // 1.获取数据库连接，创建数据库对象
            connection = JdbcUtils.getConnection();
            // 2. 执行sql的对象
            statement = connection.createStatement();
            // 3.执行sql,删除数据
            String sql="DELETE FROM `users` WHERE `id`=4";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

package com.mingmao.test2;

import com.mingmao.test2.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {
            // 1.获取数据库连接，创建数据库对象
            connection = JdbcUtils.getConnection();
            // 2. 执行sql的对象
            statement = connection.createStatement();
            // 3.执行sql,更新数据
            String sql="UPDATE `users` SET `name`='mingmao' WHERE `id`=2";
            int i = statement.executeUpdate(sql);
            if(i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

package com.mingmao.test2;

import com.mingmao.test2.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {
            // 1.获取数据库连接，创建数据库对象
            connection = JdbcUtils.getConnection();
            // 2. 执行sql的对象
            statement = connection.createStatement();
            // 3.执行sql,查询数据
            String sql="SELECT * FROM `users` WHERE `id`=2";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

sql注入

MySQL_sql_95

package com.mingmao.test2;

import com.mingmao.test2.utils.JdbcUtils;

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

public class SQL注入 {
    public static void main(String[] args) {
        //login("zhansan","123456"); 正常登录
        login("'or '1=1","'or'1=1");// 查处所有用户，不安全

    }
    // 登录业务
    public static void login(String username,String password){
        Connection connection=null;
        Statement statement=null;
        ResultSet resultSet=null;

        try {
            // 1.获取数据库连接，创建数据库对象
            connection = JdbcUtils.getConnection();
            // 2. 执行sql的对象
            statement = connection.createStatement();
            // 3.执行sql,查询数据
            String sql="SELECT * FROM `users` WHERE `name`='"+username+"' AND `password`='"+password+"'";
            resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}

preparedstatement对象

MySQL_sql_96

package com.mingmao.test3;

import com.mingmao.test2.utils.JdbcUtils;

import java.sql.*;

public class TestInsert {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection = JdbcUtils.getConnection();
            // 使用?占位符代替参数
            String sql="INSERT INTO `users` VALUES (?,?,?,?,?)";
            //预编译sql,先写sql，不执行
            preparedStatement = connection.prepareStatement(sql);
            // 手动给参数赋值
            preparedStatement.setInt(1,5);// id=5
            preparedStatement.setString(2,"小明");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"xiaoming@qq.com");
            //preparedStatement.setString(5,"2010-01-01");
            preparedStatement.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            //执行
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

package com.mingmao.test3;

import com.mingmao.test2.utils.JdbcUtils;

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

public class TestDelete {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection = JdbcUtils.getConnection();
            // 使用?占位符代替参数
            String sql="DELETE FROM `users` WHERE `id`=?";
            //预编译sql,先写sql，不执行
            preparedStatement = connection.prepareStatement(sql);
            // 手动给参数赋值
            preparedStatement.setInt(1,5);// id=5
            //执行
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("删除成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

package com.mingmao.test3;

import com.mingmao.test2.utils.JdbcUtils;

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

public class TestUpdate {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection = JdbcUtils.getConnection();
            // 使用?占位符代替参数
            String sql="UPDATE `users` SET `name`=? WHERE `id`=?";
            //预编译sql,先写sql，不执行
            preparedStatement = connection.prepareStatement(sql);
            // 手动给参数赋值
            preparedStatement.setString(1,"mingmao");
            preparedStatement.setInt(2,4);// id=4
            //执行
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("更新成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }
    }
}

package com.mingmao.test3;

import com.mingmao.test2.utils.JdbcUtils;

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

public class TestSelect {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;
        try {
            connection = JdbcUtils.getConnection();
            // 使用?占位符代替参数
            String sql="SELECT * FROM `users` WHERE `id`=?";
            //预编译sql,先写sql，不执行
            preparedStatement = connection.prepareStatement(sql);
            // 手动给参数赋值
            preparedStatement.setInt(1,4);// id=4
            //执行
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

package com.mingmao.test3;

import com.mingmao.test2.utils.JdbcUtils;

import java.sql.*;

public class SQL注入 {
    public static void main(String[] args) {
         login("zhansan","123456"); // 正常登录
       // login("'' or 1=1","123456");// 什么都查不出来，安全
    }
    // 登录业务
    public static void login(String username,String password){
        Connection connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            connection = JdbcUtils.getConnection();
            String sql="SELECT * FROM `users` WHERE `name`=? AND `password`=?";
            preparedStatement =connection.prepareStatement(sql);
            preparedStatement.setString(1,username);
            preparedStatement.setString(2,password);
            resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                System.out.println(resultSet.getString("name"));
                System.out.println(resultSet.getString("password"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

使用IDEA连接数据库

MySQL_sql_97
MySQL_java_98
MySQL_数据库_99
MySQL_数据_100
MySQL_mysql_101
MySQL_sql_102
MySQL_java_103
MySQL_数据_104
MySQL_sql_105
MySQL_java_106

JDBC操作事务

MySQL_mysql_107

package com.mingmao.test4;

import com.mingmao.test2.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 connection=null;
        PreparedStatement preparedStatement=null;
        ResultSet resultSet=null;

        try {
            connection=JdbcUtils.getConnection();
            //关闭事务的自动提交功能,并自动开启事务，无需手动开启
            connection.setAutoCommit(false);

            String sql1="update account set money=money-500 where NAME='A'";
            preparedStatement=connection.prepareStatement(sql1);
            preparedStatement.executeUpdate();

            //int x=1/0; // 添加此语句使事务执行失败
            String sql2="update account set money=money+500 where NAME='B'";
            preparedStatement=connection.prepareStatement(sql2);
            preparedStatement.executeUpdate();

            //业务完毕，提交事务
            connection.commit();
            System.out.println("成功！");
        } catch (SQLException e) {
            try {
                connection.rollback();//如果失败则回滚事务,此处可省略，默认失败会回滚
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }
    }
}

数据库连接池

MySQL_java_108
MySQL_数据_109
MySQL_mysql_110

DBCP

MySQL_java_111

#DBCP数据源中定义好的名字
driverClassName=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false
username=root
password=123456
#初始化连接数
initialSize=10
#最大连接数
maxActive=50
#最大空闲连接
maxIdle=20
#最小空闲连接
minIdle=5
#最长等待超时时间 以毫秒为单位
maxWait=60000

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

package com.mingmao.test5.utils;

import org.apache.commons.dbcp2.BasicDataSource;
import org.apache.commons.dbcp2.BasicDataSourceFactory;

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

public class JdbcUtils_DBCP {
    private static BasicDataSource dataSource=null;

    static{
        try {
            // 获得 dbcpconfig.properties中的内容
            InputStream resourceAsStream = JdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
            Properties properties = new Properties();
            properties.load(resourceAsStream);
            //创建数据源
            dataSource = BasicDataSourceFactory.createDataSource(properties);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    //获取连接
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }
    // 释放资源
    public static void release(Connection connection, Statement statement, ResultSet resultSet){
        if(resultSet!=null){
            try {
                resultSet.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(statement!=null){
            try {
                statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(connection!=null){
            try {
                connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

package com.mingmao.test5;

import com.mingmao.test5.utils.JdbcUtils_DBCP;

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

public class TestDBCP {
    public static void main(String[] args) {
        Connection connection=null;
        PreparedStatement preparedStatement=null;

        try {
            connection = JdbcUtils_DBCP.getConnection();
            // 使用?占位符代替参数
            String sql="INSERT INTO `users` VALUES (?,?,?,?,?)";
            //预编译sql,先写sql，不执行
            preparedStatement = connection.prepareStatement(sql);
            // 手动给参数赋值
            preparedStatement.setInt(1,5);// id=5
            preparedStatement.setString(2,"小明");
            preparedStatement.setString(3,"123456");
            preparedStatement.setString(4,"xiaoming@qq.com");
            //preparedStatement.setString(5,"2010-01-01");
            preparedStatement.setDate(5,new java.sql.Date(new java.util.Date().getTime()));
            //执行
            int i = preparedStatement.executeUpdate();
            if(i>0){
                System.out.println("插入成功！");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils_DBCP.release(connection,preparedStatement,null);
        }
    }
}

C3P0

MySQL_sql_112
未成功

学习视频

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。

上一篇：JavaWeb01概述

下一篇：***CSS里有哪些常见的块级元素和行内元素以及其区别？

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯

MySQL

MySQL

1.初识MySQL

mysql安装

sqlyog安装及使用

连接数据库，命令行

2.操作数据库

操作数据库

数据库的列数据类型

数值

字符串

时间和日期

null

数据库的字段属性

创建数据库表，sql

常用命令

数据库引擎

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

修改和删除表

3.MYSQL的数据管理

外键 了解即可

DML语言

添加

修改

删除

4.DQL查询数据

DQL

指定查询字段

去重

数据库的列（表达式）

where条件子句

模糊查询:比较运算符

联表查询

自连接

分页和排序

子查询和嵌套

mysql函数

常用函数

聚合函数（常用）

分组与过滤

数据库级别的MD5加密

select小结

什么是事务

执行事务

模拟场景

索引的分类

测试索引

索引原则

用户管理

数据备份

数据库设计

三大范式

数据库驱动

JDBC

第一个JDBC程序

statement对象

sql注入

preparedstatement对象

使用IDEA连接数据库

JDBC操作事务

数据库连接池

DBCP

C3P0

学习视频

51CTO博客

外键了解即可