MySQL基础知识(配合Navicat使用)

最新推荐文章于 2022-12-10 12:40:46 发布
最新推荐文章于 2022-12-10 12:40:46 发布
阅读量1.4k 收藏 36
点赞数 10
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53821599/article/details/119951766
版权

文章目录

数据库的基本概念

数据(Data), 数据库(DataBase，DB), 数据库管理系统(DataBase Management System,DBMS),

数据库系统(DataBase System，DBS), 数据库应用系统(DataBase Application System,DBAS)

关系型数据库：

  • MySQL，Oracle，SqlServer，DB2，SQLlite
  • 通过表和表之间，行和列之间的关系进行数据的存储，例如：学院信息表，考勤表

非关系型数据库：

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

数据库管理系统DBMS：

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

MySQL简介

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

  • MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统)

  • MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

  • MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言

连接数据库

show databases;   -- 查看所有的数据库

mysql> use school;  -- 切换数据库   use 数据库名
Database changed

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

desc student;    -- 显示数据库中所有表的信息

exit;       -- 退出连接

--  代表单行注释
/*
多行注释
*/

数据库xxx语言：

DDL 定义

DML 操作

DQL 查询

DCL 控制

操作数据库

操作数据库(了解)

  1. 创建数据库
CREATE DATABASE [IF NOT EXISTS] westos;
  1. 删除数据库
DROP DATABASE [IF EXISTS] hello;
  1. 使用数据库
USE school;
  1. 查看所有数据库
SHOW DATABASES;

数据库的列类型

数值-

  • tinyint 十分小的数据 1个字节
  • smallint 较小的数据 2个字节
  • mediumint 中等大小的数据 3个字节
  • **int 标准的整形 4个字节 ****
  • bigint 较大的数据 8个字节
  • float 浮点数 4个字节
  • double 浮点数 8个字节
  • decimal 字符串形式的浮点数 金融计算的时候一般使用

字符串

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

时间日期

  • 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来填充

自增：设置自增类型的字段，需要将其设置为主键，否则会报错

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

非空 null not null

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

默认：

  • 设置默认的值，需要加 ’ ’ (英文的)
  • sex，默认值为男，如果不指定该列的值，则会有默认的值

创建数据库表(重点)

-- 注意点，使用英文()，表的名称 和 字段 尽量使用 `` 括起来
-- AUTO_INCREMENT 自增
-- 字符串使用  单括号''  括起来
-- 所有的语句后面加 ,(英文的) ，最后一个不用加
-- PRIMARY　KEY　主键，一般一个表只有一个唯一的主键
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] `表名`(
	'字段名' 列类型 [属性] [索引] [注释],
	'字段名' 列类型 [属性] [索引] [注释],
	.....
	'字段名' 列类型 [属性] [索引] [注释],
)[表类型][字符集设置][注释]

navicat查看创建数据库的语句,数据表的语句和表的结构

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

运行结果：

-- 1.
CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */
-- 2.
-- 逆向操作，先创建一个数据表，然后在查看
CREATE TABLE `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

-- 3.

在这里插入图片描述

数据表的类型

--关于数据库的引擎
/*
INNODB 默认使用
MYISAM 早些年使用
*/
MYISAMINNODB
事务支持不支持支持
数据行锁定不支持支持
外键约束不支持支持
全文索引支持不支持
表空间大小较小较大，约为2倍

常规使用操作：

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

在物理空间存在的位置

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

本质还是文件的存储！

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

  • INNODB 在数据库表中只有一个*.firm文件，以及上级目录下的 ibdata1 文件
  • MYISAM对应文件
    • *.firm 表结构文件
    • *.MYD 数据文件（data）
    • *.MYI 索引文件（index）

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

CHARSET=utf8

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

MySQL的默认编码是latin1,不支持中文

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

character_set_server=utf8

修改删除表

修改

-- 修改表名ALTER TABLE 旧表名 RENAME AS 新表名
ALTER TABLE teacher RENAME AS teacher01

-- 增加表的字段 ALTER TABLE 表名 ADD 字段名 列属性  
ALTER TABLE teacher01 ADD age INT(11)

-- 修改表的字段名  （重命名，修改约束!）
-- ALTER TABLE 表名 MODIFY 字段名 列属性[]
ALTER TABLE teacher01 MODIFY age VARCHAR(11)  -- 修改约束

-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[]
ALTER TABLE teacher01 CHANGE age age1 INT(10)  -- 字段重命名
-- change一般用来字段重命名，modify修改约束

-- 删除表的字段名 ALTER TABLE 表名 DROP 字段名
ALTER TABLE teacher01 DROP age1

删除

-- 删除表（如果表存在再删除）
DROP TABLE [IF EXISTS] teacher01

所有的创建和删除操作尽量加上判断，以免报错

注意点：

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

MySQL数据管理

外键（了解即可）

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

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


-- 学生表的 gradeid 字段  要去引用年级表的  gradeid
-- 定义外键key
-- 给这个外键添加约束  （执行引用） references 引用
CREATE TABLE `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_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` 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(20) NOT NULL COMMENT '年级名称',
	PRIMARY KEY (`gradeid`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;


-- 学生表的 gradeid 字段  要去引用年级表的  gradeid
-- 定义外键key
-- 给这个外键添加约束  （执行引用） references 引用
CREATE TABLE `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 表名 ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列)REFERENCES 哪个表(哪个字段)
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`);

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

最佳实践：

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

DML语言（全部记住）

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

DML语言：数据操作语言

  • insert，update，delete
添加

insert

-- 插入语句
-- insert into 表名([字段1,字段2,....]) values('值1'),('值2'),('值3')...
INSERT INTO `grade` (`gradename`) VALUES('大四')

-- 由于主键自增我们可以省略(如果不写表的字段名，他就会一一匹配)
INSERT INTO `grade` VALUES('大三')  -- 语句报错,没有一一匹配
-- 一般写入插入语句，我们一定要数据和字段一一对应!
-- 插入多个字段
INSERT INTO `grade` (`gradename`)
VALUES('大二'),('大一')

INSERT INTO `student`(`name`) VALUES('张三')

-- 可以同时插入多条语句，VALUSES后面的值，隔开即可VALUES(),(),...
INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES('李四','aaaaaa','男'),('王五','aaabbbba','男')

-- 字段可以省略，但后面的值必须要一一对应，否则报错
INSERT INTO `student` 
VALUES(4,'李四','aaaabb','男','2000-01-01','西安','email')

语法：insert into 表名([字段1,字段2,…]) values(‘值1’),(‘值2’),(‘值3’)…

注意事项：

  1. 字段和字段之间使用英文逗号隔开
  2. 字段可以省略，但后面的值必须要一一对应，否则报错
  3. 可以同时插入多条语句，VALUSES后面的值，隔开即可VALUES(),(),…
修改

update

-- 语法：UPDATE 表名 SET column_name=value WHERE [条件]

-- 修改学员的名字，带了条件
UPDATE `student` SET `name`='狂神' WHERE id=1;

-- 不指定条件的情况下，会改动所有的表
UPDATE `student` SET `name`='sgl';

-- 修改多个属性
UPDATE `student` SET `name`='coco',`email`='2259567490@qq.comm' WHERE id=1;

-- 通过多个条件定位数据
UPDATE `student` SET `name`='长江7号' WHERE `name`='two' AND `sex`='男' AND `address`='西安';

操作符会返回 布尔值

操作符含义范围结果
=等于
<> 或 !=不等于
>
<
>=
<=
BETWEEN … AND …在某个范围闭区间 [x,y]
AND我和你 &&
OR我或你 ||

语法：UPDATE 表名 SET column_name=value,[column_name=value,…] WHERE [条件]

注意：

  • column_name 是数据库的列，尽量带上 ``
  • 条件，筛选的条件，如果没有指定将会修改所有的列
  • value，是一个具体的值，也可以是一个变量
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='长江7号' AND `address`='西安';
-- CURRENT_TIME WHERE 返回当前时间，是一个变量
删除

delete

-- 删除数据 （避免这样写,会全部删除）
DELETE FROM `student`

-- 删除指定数据
DELETE FROM `student` WHERE id=1;

TRUNCATE 命令

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

-- 清空 student 表
TRUNCATE `student`

TRUNCATE 和 delete 的区别

  • 相同点：都能删除数据库，都不会删除表结构
  • 不同点：
    • TRUNCATE：重新设置自增列 计数器回归零，重1开始
    • TRUNCATE 不会影响事务
-- 测试TURUNCATE和delete的区别
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('a'),('b'),('c')
	
	DELETE FROM `test`   -- 不会影响自增
	
	TRUNCATE TABLE `test`  -- 自增会归零，重1开始

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

  • INNODB：自增量会从1开始（存在内存档中，断电即失）
  • MYISAM：继续从上一个自增量开始（存在文件中，不会丢失）

DQL查询数据（非常重要）

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

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…] -- 指定查询记录按一个或多个条件排序
[ LIMIT {[offset,] row_count | row_countoffset offset}]-- 指定查询的记录从哪条至哪条

注意 Where，GroupBy，Having，OrderBy 顺序

查询指定字段
-- 查询全部的学生   SELECT 字段 FROM 表
SELECT * FROM `student`

-- 查询指定字段
SELECT `StudentNo`,`StudentName` FROM `student`

-- 别名 ,给结果起一个名字  AS   可以给字段起别名也可以表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM `student` AS s

-- 函数 CONCAT(a,b)
SELECT CONCAT('姓名:',StudentName) AS 新名字 FROM `student`

语句：select 字段… from 表

起别名：AS 字段名 as 别名 表名 as 别名

-- 别名 ,给结果起一个名字  AS   可以给字段起别名也可以表起别名
SELECT `StudentNo` AS 学号,`StudentName` AS 学生姓名 FROM `student` AS s

去重 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 表

where 条件语句

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

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

条件运算符

运算符语法描述
and &&a and b a && b逻辑与，两个为真，结果为真
or ||a or b a || b逻辑或，一个为真，结果为真
NOT ！not a !a逻辑非，真为假，假为真
-- 查询考试成绩再95-100之间  AND 和 &&
SELECT `studentNo`,`studentResult` FROM `result` -- 字段名也可以不区分大小写

SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE StudentResult>=95 AND StudentResult<=100

SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE StudentResult>=95 && StudentResult<=100

-- 模糊查询（区间）
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE StudentResult BETWEEN 95 AND 100

-- !=  和  NOT
SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE StudentNo!=1000

SELECT `StudentNo`,`StudentResult` FROM `result` 
WHERE NOT StudentNo=1000

模糊查询：比较运算符

运算符语法描述
IS NULLa is null如果操作符为NULL，结果为真
IS NOT NULLa is not null如果操作符不为NULL，结果为真
BETWEENa between b and c若在b和c之间，结果为真
LIKEa like bSQL匹配，如果a匹配b，结果为真
INa in(a1,a2,a2…)假设a在a1或a2…，其中的某一个值，结果为真
-- ============== 模糊查询 ============
-- 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` IN (1001,1002,1003)

-- 查询在北京的同学
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN ('北京市','海南市')

-- =======null 和 not 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
联表查询
操作描述
inner join如果表中至少有一个匹配（公共的，即交集），就返回
right join会从右边返回所有的值，即使左边没有匹配（右边有左边没有，以右边为齐，即返回公共的，加上右边有的）
left join会从左边返回所有的值，即使右边没有匹配（左边有右边没有，以左边为齐，即返回公共的，加上左边有的）

在这里插入图片描述

-- ======================联表查询 join =====================
-- 查询参加了考试的同学（学号，姓名，科目编号，分数）
SELECT * FROM student
SELECT * FROM result

/*思路
1.分析需求，分析查询的字段来自哪些表，（连接查询）
2.确定交叉点（这两个表中哪个数据是相同的）
判断条件：学生表中的  StudentNo = 成绩表中 StudentNo
*/

-- join（连接的表）on（判断条件）连接查询 
-- WHERE  等值查询

-- INNER JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student AS s INNER JOIN result AS r
ON s.StudentNo = r.StudentNo

-- RIGHT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s RIGHT JOIN result r
ON s.StudentNo = r.StudentNo

-- LEFT JOIN
SELECT s.StudentNo,StudentName,SubjectNo,StudentResult
FROM student s LEFT JOIN result r
ON s.StudentNo = r.StudentNo


-- 查询缺考的同学
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 r.StudentNo = s.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo

总结：

  1. 我们要查询哪些数据 select…
  2. 从哪几个表中查 FROM 表 XXX Join 连接的表 ON 交叉条件
  3. 假设存在查询多张表，慢慢来，先查询两张表，再慢慢增加
FROM a left join b
FROM a right join b

自连接

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

父类

pidcategoryName
2信息技术
3软件开发
5美术技术

子类

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

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

父类子类
信息技术办公信息
软件开发数据库
软件开发web开发
美术技术ps技术
-- 查询父子关系
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目'
FROM `category` AS a,`category` AS b
WHERE a.categoryid = b.pid  -- 父类的categoryid对应子类的pid（即相同，不同表达）

练习：

-- 查询学员所属的年级（学号，姓名，年级名称）
SELECT StudentNo ,StudentName,GradeName
FROM student AS s
INNER JOIN grade AS g
ON s.GradeID = g.GradeID

-- 查询科目所属年级（科目名称，年级名称）
SELECT SubjectName,GradeName 
FROM `subject` sub
INNER JOIN grade gra
ON sub.GradeID = gra.GradeID

-- 查询了参加高等数学 考试的同学信息：学号，学生姓名，科目名，分数
SELECT s.StudentNo,StudentName,subjectName,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` AS sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubjectName = '高等数学'
分页和排序

排序

-- ==============分页limit和排序order by==================
-- 排序：降序DESC  升序ASC
-- ORDER BY 哪个字段排序 怎么排
-- 查询结果根据 成绩 降序DESC  升序ASC

SELECT s.StudentNo,StudentName,subjectName,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` AS sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubjectName = '高等数学'
ORDER BY StudentResult DESC

分页

-- 分页 语法：limit 当前页，页面的大小
SELECT s.StudentNo,StudentName,subjectName,StudentResult
FROM student AS s
INNER JOIN result AS r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` AS sub
ON sub.SubjectNo = r.SubjectNo
WHERE SubjectName = '高等数学'
ORDER BY StudentResult DESC
LIMIT 5,5

-- 第一页 limit 0,5   (1-1)*5,5
-- 第二页 limit 5,5   (2-1)*5,5
-- 第三页 limit 10,5  (3-1)*5,5
-- 第N页 limit (n-1)*pageSize,pageSize
-- 【pageSize：页面当前大小】
-- 【(n-1)*pageSize：起始值】
-- 【n：当前页】
-- 【页面总数/页面大小 = 总页数】

练习：

-- 查询高等英语  课程成绩排名前三的学生，并且分数要大于80的学生信息（学号，姓名，课程名称，分数）

SELECT stu.StudentNo,StudentName,SubjectName,StudentResult
FROM student AS stu
INNER JOIN `result` AS res
ON stu.StudentNo = res.StudentNo
INNER JOIN `subject` AS sub
ON res.SubjectNo = sub.SubjectNo
WHERE SubjectName = '高等英语'
ORDER BY StudentResult DESC
LIMIT 0,3
子查询

where（这个值是计算出来的）

本质：在where语句中嵌套一个子查询语句 where（select * from)

-- =================WHERE================
-- 查询高等英语的所有考试结果（学号，课程编号，成绩），降序排列
-- 方式一：连接查询
SELECT StudentNo,r.SubjectNo,StudentResult
FROM result r 
INNER JOIN `subject` s
ON r.SubjectNo = s.SubjectNo
WHERE SubjectName = '高等英语'
ORDER BY StudentResult DESC

-- 方式二：使用子查询(有里及外)
SELECT StudentNo,SubjectNo,StudentResult
FROM result 
-- where查看所有高等数学的学号
WHERE SubjectNo = (
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '高等英语'
)
ORDER BY StudentResult DESC

-- ============================================

-- 查询课程为  高等英语 且 分数不小于80的同学的学号和姓名
SELECT DISTINCT r.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE StudentResult >=80 AND SubjectName = '高等英语'

-- 分数不小于80分的学生的学号和姓名
SELECT DISTINCT s.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult >=80

-- 在这个基础上增加一个科目，高等英语
-- 查询高等英语 的编号
SELECT DISTINCT s.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
WHERE StudentResult >=80 AND SubjectNo = (
SELECT SubjectNo FROM `subject`
WHERE SubjectName = '高等英语'
)

-- 改造（由里及外）
SELECT StudentNo,StudentName FROM student WHERE StudentNo IN (
	SELECT StudentNo FROM result WHERE StudentResult >=80 AND SubjectNo = (
		SELECT SubjectNo FROM `subject`WHERE SubjectName = '高等英语'
		)
	)
分组和过滤
-- 查询不同课程的平均分，最高分，最低分
-- 核心：（根据不同的课程分组）
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 平均分>70

MySQL函数

常用函数
-- ======函数=====

-- 数学函数
SELECT ABS(-8)  -- 绝对值
SELECT ceiling(9.5)  -- 向上取整
SELECT FLOOR(9.5) -- 向下取整
SELECT RAND()  -- 返回一个 0~1 之间的随机数
SELECT SIGN(10) -- 判断一个数的符号  0返回0   负数返回-1  正数返回1

-- 字符串函数
SELECT CHAR_LENGTH('即使再完美，也会犯错')  -- 字符串的长度
SELECT CONCAT('我','爱','你们')  -- 拼接字符串
SELECT INSERT('我爱编程',1,2,'超级热爱') -- 查询  从某个位置开始替换某个长度
SELECT LOWER('SGL')  -- 转换小写字母
SELECT UPPER('sgl')  -- 转换大写字母
SELECT INSTR('sgl','g')  -- 返回第一次出现的子串的索引
SELECT REPLACE('坚持就能成功','坚持','努力')  -- 替换出现的指定字符串
SELECT SUBSTR('坚持就能成功',1,2)  -- 返回指定的子字符串（源字符串，截取的位置，截取的长度）


-- 查询姓  施的同学   姓名改为 邹
SELECT * FROM student 
WHERE StudentName LIKE '施%'

SELECT REPLACE(StudentName,'施','时') FROM student 
WHERE StudentName LIKE '施%'


-- 时间和日期函数
SELECT CURRENT_DATE()  -- 获取当前日期
SELECT CURDATE()   -- 获取当前日期
SELECT NOW()  -- 获取当前的时间
SELECT LOCALTIME() -- 本地时间
SELECT SYSDATE()  -- 获取系统时间

SELECT YEAR(NOW())  -- 年
SELECT MONTH(NOW()) -- 月
SELECT DAY(NOW())  -- 日
SELECT HOUR(NOW())  -- 时
SELECT MINUTE(NOW())  -- 分
SELECT SECOND(NOW()) -- 秒

-- 系统
SELECT SYSTEM_USER()  
SELECT USER()
SELECT VERSION()
聚合函数
函数名称描述
count()计数
sum()求和
avg()平均数
max()最大值
min()最小值
-- =======聚合函数======
-- 都能够统计表中的数据  查询多少个记录，就用count

SELECT COUNT(`StudentNo`) FROM student  -- count(字段) 会忽略所有的null值
SELECT COUNT(*) FROM student   -- count(*) 不会忽略null值  本质：计算行数
SELECT COUNT(1) FROM result   -- count(1) 不会忽略null值  本质：计算行数

SELECT SUM(`StudentResult`) AS 总和 FROM result
SELECT AVG(`StudentResult`) AS 平均数 FROM result
SELECT MAX(`StudentResult`) AS 最大值 FROM result
SELECT MIN(`StudentResult`)AS 最小值 FROM result
数据库级别的MD5加密（了解即可）

MD5信息摘要算法（英语：MD5 Message-Digest Algorithm）,MD5不可逆

-- =====测试MD5加密=====
DROP TABLE  text

CREATE TABLE `text`(
	`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 text 
VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE `text` SET pwd=MD5(pwd) WHERE id = 1

UPDATE `text` SET pwd=MD5(pwd)  -- 加密全部的密码

-- 插入的时候加密
INSERT INTO text VALUES(4,'sgl',MD5('123456'))

-- 如何校验：将用户传递进来的密码，进行md5加密，然后比对加密后的密码
SELECT * FROM text WHERE `name` = 'sgl' AND pwd=MD5('123456')

事务（重要）

原则：ACID原则 原子性，一致性，隔离性，持久性

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

隔离导致的一些问题

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

  2. 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同。

  3. 虚读(幻读)：是指在一个事务内读取到了别的事务插入的数据，导致前后读取数量总量不一致。

事务原理图(简单描述）：

在这里插入图片描述

执行事务

-- =========事务=========

-- MySQL 是默认开启事务自动提交
SET autocommit = 0  -- 关闭
SET autocommit = 1  -- 开启（默认）

-- 手动处理事务 
START autocommit = 0 -- 关闭自动提交

-- 事务开启 transaction 
SET TRANSACTION  -- 标记一个事务的开始，从这个开始之后，sql  都在同一个事务内

INSERT xx
INSERT xx

-- 提交：持久化（成功）
COMMIT
-- 回滚：回到原来的样子 （失败）
ROLLBACK

-- 事务结束
SET autocommit = 1  -- 开启自动提交

练习：

-- 转账
-- COLLATE 核对
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci
USE shop
DROP TABLE account
CREATE TABLE `account` (
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(20) 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',1000.00)

-- 模拟转账：事务
SET autocommit = 0;  -- 关闭自动提交
START TRANSACTION  -- 开启一个事务
 
UPDATE account SET money=money-500 WHERE `name` = 'A'  -- A减500
UPDATE account SET money=money+500 WHERE `name` = 'B'  -- B加500

COMMIT  -- 提交事务
ROLLBACK  -- 回滚事务

SET autocommit = 1  -- 恢复默认值

索引

简述：索引（index）是帮助MySQL高效获取数据的数据结构；索引的本质：索引是数据结构

索引的分类

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

  1. 主键索引（primary key）

    • 唯一的标识，主键不可重复，只能有一个列作为主键

  2. 唯一索引（unique key）

    • 避免出现重复的列，唯一索引可以重复，多个列都可以标识唯一索引

  3. 常规索引（key/index）

    • 默认的，用key或index设置

  4. 全文索引（fulltext)

    • 快速定位数据
-- 索引的使用
-- 1.在创建表的时候给字段添加索引
-- 2.创建完毕后，添加索引

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

-- 添加一个全文索引   (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `StudentName`(`StudentName`)

-- EXPLAIN 分析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 '用户昵称',
`eamil` 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=utf8 COMMENT='app用户表'

DROP TABLE app_user
TRUNCATE `app_user`

-- 插入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`,`eamil`,`phone`,`gender`,`password`,`age`)
		VALUES(CONCAT('用户',i),'2259567490@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*10));
		SET i=i+1;
	END WHILE;
	RETURN i;
	END;
SELECT mock_data() -- 执行此函数 生成一百万条数据


INSERT INTO app_user(`name`,`eamil`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('用户1'),'2259567490@qq.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100))

SELECT * FROM app_user WHERE `name` = '用户99999'  -- 0.494秒

-- 命名 id_表名_字段名
-- 创建索引的方式：CREATE INDEX 索引名 ON 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`)

SELECT * FROM app_user WHERE `name` = '用户99999' -- 0.026秒

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999'

执行语句：

EXPLAIN 分析sql执行的状况

EXPLAIN SELECT * FROM app_user WHERE `name` = '用户99999'

结果有无索引的区别：

有索引：查询一列

在这里插入图片描述

无索引：查询992269列

在这里插入图片描述

创建索引的方式：

-- 1.方式一
-- 命名 id_表名_字段名
-- 创建索引的方式：CREATE INDEX 索引名 ON 表名(字段名)
CREATE INDEX id_app_user_name ON app_user(`name`)
-- 1.方式二
-- 添加一个全文索引   (索引名) 列名
ALTER TABLE school.student ADD FULLTEXT INDEX `StudentName`(`StudentName`)
索引原则
  • 索引不是越多越好
  • 不要对进程变动的数据加索引
  • 小数据量的表不需要加索引
  • 索引一般加在常用查询的字段上

MySQL索引背后的原则：

连接：CodingLabs

权限管理和备份

用户管理

SQL命令操作

用户表：mysql.user

在这里插入图片描述

本质：读这张表进行增删改查

-- 创建用户 CREATE USER 用户名 IDENTIFIED [WITH 插件] BY '密码';
-- CREATE USER 用户名 IDENTIFIED BY '密码';
CREATE USER `kuangshen` IDENTIFIED  BY '123456';

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

-- 修改指定用户密码
SET PASSWORD FOR `kuangshen` = PASSWORD('654321')

-- 重命名  RENAME USER 原来的名字 TO 旧名字
RENAME USER  kuangshen TO kuangshen02

-- 用户授权 ALL privileges 全部的权限，库，表
-- ALL PRIVILEGES 除了给别人权限，其他都能看
GRANT ALL PRIVILEGES ON *.* TO kuangshen02
GRANT ALTER ON `school`.* TO 'kuangshen02'@'%'
GRANT ALTER ON `school`.`app_user` TO 'kuangshen02'@'%'

-- 查询权限
SHOW GRANTS FOR kuangshen02  -- 查看指定用户权限GRANT ALL PRIVILEGES ON *.* TO 'kuangshen02'@'%'

-- root用户权限 GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION
SHOW GRANTS FOR root@localhost  

-- 撤销权限 REVOKE 哪些权限 ，在哪个库撤销，给谁撤销
REVOKE ALL PRIVILEGES ON *.* FROM kuangshen02
REVOKE ALTER ON `school`.* FROM 'kuangshen02'@'%'
REVOKE ALTER ON `school`.`app_user` FROM 'kuangshen02'@'%'

-- 删除用户
DROP USER kuangshen02
DROP USER sgl@127.0.0.1
MySQL备份

作用：保证数据不丢失；数据转移

MySQL数据库备份的方式：

  • 直接拷贝物理文件
  • 在命令行导出 mysqlump 命令行使用
# mysqldump -h主机 -u用户 -p密码 数据库 表名 >物理磁盘位置\文件名
mysqldump -hlocalhost -uroot -p123456 school student >F:\a.sql
# mysqldump -h主机 -u用户 -p密码 数据库 表名1 表名2 表名2 ... >物理磁盘位置\文件名
mysqldump -hlocalhost -uroot -p123456 school student result >F:\b.sql
# mysqldump -h主机 -u用户 -p密码 数据库 >物理磁盘位置\文件名
mysqldump -hlocalhost -uroot -p123456 school >F:\c.sql

# 导入
#登陆的情况下，切换到指定的数据库
# source 备份文件
source F:\a.sql

#不登陆，导入
mysql -u用户 -p密码 数据库 <备份文件
  • 在Navicat这种可视化工具中手动导出

规范数据库设计

设计数据库的步骤

略略…

三大范式

规范数据库的设计

  • 第一范式（1NF）

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

  • 第二范式（2NF）

前提：满足第一范式

每张表只描述一件事情

  • 第三范式（3NF）

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

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

规范性和性能的问题

略略…想了解再自己查询

JDBC（***）

数据库驱动

在这里插入图片描述

程序通过 数据库驱动，和数据库打交道

JDBC

Java数据库连接，（Java Database Connectivity，简称JDBC）是Java语言中用来规范客户端程序如何来访问数据库的应用程序接口，提供了诸如查询和更新数据库中数据的方法。JDBC也是Sun Microsystems的商标。我们通常说的JDBC是面向关系型数据库的。

连接方式

各种数据库使用JDBC连接的方式：

⒈Oracle8/8i/9i数据库（thin模式）

⒉DB2数据库

⒊Sql Server7.0/2000数据库

⒋Sybase数据库

⒌Informix数据库

⒍MySQL数据库

⒎PostgreSQL数据库

⒏access数据库直连用ODBC的

当访问一个远程数据库时，JDBC将利用Internet文件的编址方案和一个看起来很像网页地址（统一资源定位器URL）的文件名。

JDBC为程序员指定了一组在编写SQL请求时使用的面向对象的类。还有一组附加的类描述了JDBC驱动API。能映射成Java数据类型的最普通的SQL数据类型都是支持的。这个API提供了微软事务服务器请求的执行支持以及提交和回滚到事务开始的能力。

主要用途

与数据库建立连接

发送 SQL 语句

处理结果

建立连接

建立连接的五大步骤：

1加载（注册）数据库

2 建立链接

3 执行SQL语句

4 处理结果集

5 关闭数据库

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,'zhangsan','123456','zs@sina.com','2001-05-26'),(2,'lisi','123456','lisi@sina.com','2001-09-16'),(3,'wangwu','123456','ww@sina.com','2000-08-26')

  1. 创建一个普通的项目

  2. 导入数据库驱动

maven仓库下载mysql-connector-java-5.1.47.jar

创建一个lib目录，将 mysql-connector-java-5.1.47.jar导入（粘贴复制即可），导入后：右键lib，点击Add as Library…

在这里插入图片描述

  1. 编写测试代码
package com.sgl.lession01;

import java.sql.*;

public class JdbcFirstDemo {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //1.加载/注册数据库驱动

        Class.forName("com.mysql.jdbc.Driver"); //固定写法，加载驱动

        //用户信息和url
        String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
        String username = "root";
        String password = "123456";

        //2.通过DriverManager获取数据库来连接  Connection  代表数据库
        Connection connection = DriverManager.getConnection(url, username, password);

        //3.Connection对象获取Statement对象
        Statement statement = connection.createStatement();

        //4.使用statement执行sql语句
        String sql = "SELECT * FROM users";
        ResultSet resultSet = statement.executeQuery(sql);//返回的结果集，结果集中封装了我们全部的查询结果

        //5.操作ResultSet结果集
        while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id")+"|"+
                    "name="+resultSet.getObject("NAME")+"|"+
                    "pwd="+resultSet.getObject("PASSWORD")+"|"+
                    "email="+resultSet.getObject("email")+"|"+
                    "birthday="+resultSet.getObject("birthday"));
        }
        //6.释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

允许结果：

在这里插入图片描述

步骤总结：

  1. 加载并注册数据库驱动 DriverManager
Class.forName("com.mysql.jdbc.Driver"); //建议使用
或
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
  1. 通过DriverManager获取数据库来连接 Connection 代表数据库：数据库设置自动提交，事务提交，事务回滚
Connection connection = DriverManager.getConnection(url, username, password);

​ Connection 代表数据库：数据库设置自动提交，事务提交，事务回滚

connection.setAutoCommit();//自动提交
connection.commit();//事务提交
connection.rollback();//事务回滚
  1. Connection对象获取Statement对象
Statement statement = connection.createStatement();
  1. 使用statement执行sql语句
ResultSet resultSet = statement.executeQuery(sql)
  1. 操作ResultSet结果集

获得指定的数据类型

resultSet.getObject()  //在不知道列类型的情况下使用

//如果知道列的类型就使用指定的类型
resultSet.getString();
resultSet.getInt();
resultSet.getFloat();
resultSet.getDate();
resultSet.getObject();
while (resultSet.next()){
            System.out.println("id="+resultSet.getObject("id")+"|"+
                    "name="+resultSet.getObject("NAME")+"|"+
                    "pwd="+resultSet.getObject("PASSWORD")+"|"+
                    "email="+resultSet.getObject("email")+"|"+
                    "birthday="+resultSet.getObject("birthday"));
                    }

遍历：

resultSet.beforeFirst();   //移动到最前面
resultSet.afterLast();    //移动到最后面
resultSet.next();     //移动到下一个数据
resultSet.previous();    //移动到前一行
resultSet.absolute(int row);  //移动到指定行
  1. 关闭连接,释放资源
resultSet.close();
statement.close();
connection.close();
  • URl
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=false";
//协议(jdbc:mysql)://主机名(localhost):端口号(3306)/数据库名(jdbcstudy)?固定格式(useUnicode=true&characterEncoding=utf8&useSSL=false)   连接数据库的地址
String username = "root";  // 登录数据库的用户名
String password = "123456";//登录数据库的密码


 String sql = "SELECT * FROM users";//数据库的查询语句
Statement对象

jdbc中的statement对象用于向数据库发送SQL语句，想要完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可

statement对象的executeUpdate方法，用于向数据库发送增删改的sql语句，executeUpdate执行完后，将会返回一个整数（即增删改语句导致了数据库几行数据发送了变化）

statement.executeQuery()方法用于向数据库发送查询的sql语句，executeQuery方法返回代表查询结果的ResultSet对象

CURD操作-create

使用executeUpdate(String sql)方法完成数据库添加操作

statement st = conn.createStatement();
String sql ="insert into users(...) values(...)";
int num = st.executeUpdate(sql);
if (num>0){
    System.out.println("插入成功");
}

CRUD操作-delete

使用executeUpdate(String sql)方法完成数据库删除操作

statement st = conn.createStatement();
String sql ="delete from users where id=1";
int num = st.executeUpdate(sql);
if (num>0){
    System.out.println("删除成功");
}

CRUD操作-update

使用executeUpdate(String sql)方法完成数据库修改操作

statement st = conn.createStatement();
String sql ="update users set name='' where name=''";
int num = st.executeUpdate(sql);
if (num>0){
    System.out.println("修改成功");
}

CRUD操作-read

使用executeQuery(String sql)方法完成数据库查询操作

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

具体代码：

增删改的方法都是executeUpdate

配置文件db.properties

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

定义一个JdbcUtils类

package com.sgl.lession02.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 {
            InputStream in = JdbcUtils.class.getClassLoader().getResourceAsStream("db.properties");
            Properties properties = new Properties();
            properties.load(in);
            driver = properties.getProperty("driver");
            url = properties.getProperty("url");
            username = properties.getProperty("username");
            password = properties.getProperty("password");

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

        } catch (IOException 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 throwables) {
                throwables.printStackTrace();
            }
        }
        if (statement!=null){
            try {
                statement.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        if (connection!=null){
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }

    }
}

插入：

package com.sgl.lession02;

import com.sgl.lession02.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 {
            connection = JdbcUtils.getConnection(); //.通过DriverManager获取数据库来连接  Connection  代表数据库
            statement = connection.createStatement();//Connection对象获取Statement对象
            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)\n" +
                    "VALUES(4,'sgl','123456','2256955@qq.com','2000-08-26')";  //使用statement执行sql语句
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("插入成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement, null);
        }
    }
    /*重复插入会报错：id为主键
    Duplicate entry '4' for key 'PRIMARY'
     */
}

删除：

package com.sgl.lession02;

import com.sgl.lession02.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 {
            connection = JdbcUtils.getConnection(); //.通过DriverManager获取数据库来连接  Connection  代表数据库
            statement = connection.createStatement();//Connection对象获取Statement对象
            String sql = "DELETE FROM users WHERE id=4";  //使用statement执行sql语句
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("删除成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement, null);
        }
    }
}

修改：

package com.sgl.lession02;

import com.sgl.lession02.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 {
            connection = JdbcUtils.getConnection(); //.通过DriverManager获取数据库来连接  Connection  代表数据库
            statement = connection.createStatement();//Connection对象获取Statement对象
            String sql = "UPDATE users SET `NAME`='sgl'  WHERE id=1";  //使用statement执行sql语句
            int i = statement.executeUpdate(sql);
            if (i>0){
                System.out.println("修改成功");
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement, null);
        }
    }
}

查询：

使用的executeQuery

package com.sgl.lession02;

import com.sgl.lession02.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 {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            String sql = "select * from users where id =1";
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }
    }
}
SQL注入问题

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

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

SQL注入百度百科

https://baike.baidu.com/item/sql%E6%B3%A8%E5%85%A5/150289?fr=aladdin

package com.sgl.lession02;

import com.sgl.lession02.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("'or'1=1","'or'1=1");
    }

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

        try {
            connection = JdbcUtils.getConnection();
            statement = connection.createStatement();
            //SELECT * FROM users WHERE `NAME`='sgl' 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+"'";
            resultSet = statement.executeQuery(sql);

            while (resultSet.next()){
                System.out.println(resultSet.getString("NAME"));
                System.out.println(resultSet.getString("PASSWORD"));
                System.out.println("==============");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,statement,resultSet);
        }

    }
}
PreparedStatement对象

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

  1. 插入
package com.sgl.lession02;

import com.sgl.lession02.utils.JdbcUtils;
import java.util.Date;
import java.sql.*;

public class TestInsert02 {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //区别
            // 使用?  占位符代替参数
            String sql = "INSERT INTO users(`id`,`NAME`,`PASSWORD`,`email`,`birthday`) values(?,?,?,?,?)";

            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            preparedStatement.setInt(1,4);
            preparedStatement.setString(2,"sgl");
            preparedStatement.setString(3,"123321");
            preparedStatement.setString(4,"2259567490@qq.com");
            //sql.Date     数据库  java.sql.Date()
            //util.Date    Java   new Date().getTime() 获得时间戳
            preparedStatement.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("插入成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}
  1. 删除
package com.sgl.lession02;

import com.sgl.lession02.utils.JdbcUtils;

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

public class TestDelete02 {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //区别
            // 使用?  占位符代替参数
            String sql = "DELETE FROM users WHERE id=?";

            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            preparedStatement.setInt(1,4);

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("删除成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}
  1. 更新
package com.sgl.lession02;

import com.sgl.lession02.utils.JdbcUtils;

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

public class TestUpdate02 {
    public static void main(String[] args) {

        Connection connection = null;
        PreparedStatement preparedStatement =null;
        ResultSet resultSet = null;

        try {
            connection = JdbcUtils.getConnection();
            //区别
            // 使用?  占位符代替参数
            String sql = "UPDATE users SET `NAME`=?  WHERE id=?";

            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            preparedStatement.setString(1,"coco");
            preparedStatement.setInt(2,1);

            //执行
            int i = preparedStatement.executeUpdate();
            if (i>0){
                System.out.println("修改成功");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,null);
        }

    }
}
  1. 查询
package com.sgl.lession02;

import com.sgl.lession02.utils.JdbcUtils;

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

public class TestSelect02 {
    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=?";

            preparedStatement = connection.prepareStatement(sql);//预编译SQL，先写sql，然后不执行

            //手动给参数赋值
            preparedStatement.setInt(1,1);//传入参数

            //执行
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                System.out.println(resultSet.getObject("NAME"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }

    }
}

SQL注入问题

package com.sgl.lession02;
import com.sgl.lession02.utils.JdbcUtils;

import java.sql.*;

public class SQL02 {
    public static void main(String[] args) {

        login("coco","123456");
        //login("'or'1=1","'or'1=1");   //运行没有结果，PreparedStatement防止SQL注入问题
    }

    //登录业务
    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"));
                System.out.println("=========");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JdbcUtils.release(connection,preparedStatement,resultSet);
        }

    }
}
等慢慢
关注
  • 10
    点赞
  • 踩
  • 36
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论
使用MD5对数据进行加密
05-31
MD5的典型应用是对一段Message(字节串)产生fingerprint(指纹)，以防止被“篡改”。举个例子，你将一段话写在一个叫 readme.txt文件中，并对这个readme.txt产生一个MD5的值并记录在案，然后你可以传播这个文件给别人，别人如果修改了文件中的任何内容，你对这个文件重新计算MD5时就会发现（两个MD5值不相同）。如果再有一个第三方的认证机构，用MD5还可以防止文件作者的“抵赖”，这就是所谓的数字签名应用。 　　所以，要遇到了md5密码的问题，比较好的办法是：你可以用这个系统中的md5()函数重新设一个密码，如admin，把生成的一串密码覆盖原来的就行了。(功能很强大)
Navicat 中mysql查询使用占位符
每天进步一点点儿
10-10 2638
在navicat中，写了一堆sql，里面有同一个字段，这个时候可以使用占位符号，点击运行的时候，填写变量值，即可全都替换陈需要的值，节省一个个去替换的时间。 变量用：[$变量名] 如： select * from score where score=[$bame]; select * from student where uname=[$bame]; 点击执行，输入变量值即可： ...
软件测试：navicat-mysql基础知识
qq_40468470的博客
05-28 2763
一、数据库的操作 1、新建数据库 2、打开数据库 右键或者双击就可以了。 3、删除数据库 右键–>删除数据库 4、修改数据库 右键–>数据库属性 二、数据类型 1、常用的数据类型 整数：int 小数：decimal 字符串：varchar 日期时间：datatime 2、约束条件 主键：物理上储存的顺序（主键唯一、不能为空，所以允许空值的勾得去掉，不然不能新建或保存，还可以选择下面注释中得自动递增节省工作量） 非空：此字段不允许填空值 唯一：此字段不允许重复 默认值：当不填写时会使用默认值，
MySQL知识点：自学入门，Navicat,使用
m0_62597198的博客
07-13 256
要确定时可以正常去使用的，点击图标有灰色变为有颜色就是可以正常使用数据库的语句 创建一个表 利用SQL语句为数据库表添加数据` 查询 查询所有字段 select* 。例如：select*+from+xxx。但是这种方法用于查询大量数据太费时间 使用时可以直接查询 select+检索的内容 修改 注解：id就是序号，别的或许会重名，但是序号不会...
mybatis拼接符和占位符的区别
Vectory0213的博客
08-06 1577
占位符: 1.#{}占位符可以用来设置参数，如果传进来的是基本类型，也就是(string,long,double,int,boolean,float等)，那么#{}里面的变量名可以随意写，什么abc,xxx等等，这个名字和传进来的参数名可以不一致。 2.如果传进来的是pojo类型，那么#{}中的变量名必须是pojo的属性名，可以写成属性名,也可以写属性名.属性名。 拼接符: 3.注意：#{...
MySQL数据库安装和Navicat for MySQL配合使用教程
09-09
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB 公司开发，目前属于 Oracle 旗下公司。这篇文章主要介绍了MySQL数据库安装和Navicat for MySQL配合使用,需要的朋友可以参考下
MySQL数据库：使用NAVICAT工具更新数据.pptx
05-29
使用NAVICAT工具更新数据 课程目标 1）掌握 ——使用navicat工具进行数据插入的方法； 2）掌握 ——使用navicat工具进行数据修改的方法； 3）掌握 ——使用navicat工具进行数据删除的方法。 使用navicat工具插入数据...
使用navicat将csv文件导入mysql
09-09
主要为大家详细介绍了使用navicat将csv文件导入mysql，具有一定的参考价值，感兴趣的小伙伴们可以参考一下
navicat for mysql
12-11
Navicat for mysql是绿色破解版。 Navicat是一套快速、可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉...
Navicat for MySQL
06-08
Navicat for MySQL是一套管理和开发MySQL或MariaDB的理想解决方案，支持单一程序，可同时连接到MySQL和MariaDB。这个功能齐备的前端软件为数据库管理、开发和维护提供了直观而强大的图形界面，给MySQL或MariaDB新手...
MySQL 本可以用Workbench，为什么还要Navicat
qfzhaohan的博客
11-16 6371
初学MySQL的时候所有演示题都是直接用Workbench操作的，后来发现很多课程都是要下载Navicat，而Navicat只有15天免费试用。 免费的 MySQL Workbench 和 收费的 Navicat 的用户量差不多，但 Workbench 在仅支持 MySQL 的情况下，能和 Navicat 使用人数差不多，可以说 Workbench 的占有率在 MySQL 使用者中更高。 先说结论：这两个软件，大面上基本没有区别，功能大同小异。不要费尽心思到处找对比。从免费的用起，在实践中感受差异。
SQL Server Management Studio 显示所有字段的说明，navicat中显示所有表的注释
atzqtzq的博客
12-26 2239
1.SQL Server Management Studio 显示所有字段的说明 1.1关闭SQL Server Management Studio 1.2打开注册表 1.3找到字段修改值后，重新打开SQL Server Management Studio HKEY_CURRENT_USER\Software\Microsoft\SQL Server Management Studio\18.0_IsoShell\DataProject 修改字段：SSVPropViewColumnsSQL70 .
MySQL常用命令、Navicat使用教程
m0_61634551的博客
04-01 1563
MySQL常用命令
安装并配置 MySQL和MySQL 的基本使用
weixin_66591252的博客
08-18 406
可视化的 MySQL 管理工具，通过它，可以方便的操作存储在 MySQL Server 中的数据。：多种数据库软件的集成工具，专门用来提供数据存储和服务的软件。1 .了解需要安装哪些 MySQL 相关的软件。这两个软件，就能满足开发的需要了。5. 新建查询 sql 语句区域。对于开发人员来说，只需要安装。navicat 的基本使用。......
Mysql安装配置和Mysql使用六千字详解！！
qq_62958114的博客
12-10 1716
数据库基本操作详解，你想学的增删改查都在里面！！！，约束和联合查询详细讲解
Mysql数据库和navicat
sound_source的博客
11-09 2556
1.navicat中的每一个操作，在本质上都对应了一个sql，你可以在工具下面找到历史日志进行查看 2.基础的sql语句 1.查看数据库：show databases; 2.切换数据库：use 数据库名 3.查看数据库中所有的表：show tables； 4.显示表中所有的信息：describe 数据库表名； 5.创建一个数据库create database 表名； 6.退出连接：exit;(强制退出ctrl+c) 7.sql本身的注释：单行注释：-- ， 多行注释：/* */ 操作数据库的语句 1
MySQL篇（四）数据库的列类型
小倪喜欢吃蛋糕的博客
11-13 1361
数据库的列类型 数值类型 字符串 日期和时间 null值 理解为 “没有值” 或 “未知值” 不要用NULL进行算术运算 , 结果仍为NULL 数据字段属性 UnSigned 无符号的 声明该数据列不允许负数 . ZEROFILL 0填充的 不足位数的用0来填充 , 如int(3),5则为005 Auto_InCrement 自动增长的 , 每添加一条数据 , 自动在上一个记录数上加 1(默认) 通常用于设置主键 , 且为整数类型 可定义起始值和步长 当前表设置步长(AUTO_INCREMENT=10
MySQL全程study navicat编程 idea编程
qq_43162004的博客
04-18 582
#什么是数据库？什么是数据库管理系统？什么是SQL？他们之间的关系是什么？ 数据库：DataBase，简称DB。按照一定格式存储数据的一些文件的组合。顾名思义：存储数据的仓库，实际上就是一堆文件。这些文件中存储了具有特定格式的数据。 数据库管理系统：DataBaseMangement System，简称MBMS。数据库管理系统是专门用来管理数据库中的数据的，数据库管理系统可以对数据库中的数据进行增删改查。 常见的数据库管理系统：MySQL、Oracle、MS SqlServer、DB2、sybase等
MySQL基础（五）：图形化客户端Navicat的介绍和使用教程、使用 Navicat 创建数据库并向表中添加数据
汪雯琦的博客
03-12 4046
文章目录图形化客户端Navicat1. Navicat的介绍2. Navicat的使用3. 小结 图形化客户端Navicat 学习目标 能够使用 Navicat 创建数据库并向表中添加数据 1. Navicat的介绍 Navicat 是一套快速、可靠并价格适宜的数据库管理工具, 适用于三种平台: Windows、mac OS 及 Linux。 可以用来对本机或远程的 MySQL、SQL Se...
MySQL的安装与Navicat的使用 自我小结
最新发布
06-08
好的，我会为你简单介绍MySQL的安装和Navicat的使用。 MySQL是一款流行的开源关系型数据库管理系统，可以在各种操作系统上运行。它具有高可靠性、高性能和可扩展性等优点，被广泛应用于Web应用程序的开发中。 安装...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

等慢慢

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值