MySQL数据库

最新推荐文章于 2024-10-02 00:27:17 发布
最新推荐文章于 2024-10-02 00:27:17 发布
阅读量326 收藏
点赞数
分类专栏： 数据库 文章标签： 数据库 java jdbc 入门 database
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_42511420/article/details/122249494
版权

MySQL数据库

1、数据库

1.1、什么是数据库

数据库（DataBase ，DB）

概念：按照数据结构来组织、存储和管理数据的仓库。是一个长期存储在计算机内的、有组织的、可共享的、统一管理的大量数据的集合。

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

1.2、数据库种类

1.关系型数据库

采用关系模型来组织数据结构的数据库。最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织。

常见的关系型数据库：MySQL，Oracle，Sql Server

2.非关系型数据库

是一种数据结构化存储方法的集合通过对象自身的属性决定，可以是文档或者键值对等

常见的非关系型数据库：Redis，MongDB

2、MySQL介绍

2.1、MySQL简介

MySQL是一个关系型数据库管理系统。，MySQL是最好的 RDBMS (Relational Database Management System，关系数据库管理系统) 应用软件之一。具有体积小、速度快、总体拥有成本低，开放源码等特点。

2.2、MySQL基础命令

1.连接数据库

mysql -uroot -proot
eixt;	-- 退出连接

-- 注释
/* 
	多行注释
*/

2.操作数据库

show databases; -- 查看所有数据库
use test；	    -- 切换数据库 use 数据库名
create database newdb;  -- 新建库 create database 库名

3.操作表

show tables;	-- 查看数据库下所有表
describe test;	-- 查看表信息  describe 表名; 简写desc 表名;

3、操作数据库

3.1、创建数据库

CREATE DATABASE [IF NOT EXISTS] test
-- CREATE DATABASE 创建库名
-- 如果加IF NOT EXISTS 表示判断库名是否存在，如果不存在则创建数据库，反之存在就不创建。避免SQL报错

3.2、删除数据库

DROP DATABASE IF EXISTS test
-- CREATE DATABASE 删除库名
-- 如果加IF EXISTS 表示判断库名是否存在，如果存在则删除数据库，反之不存在就不操作。避免SQL报错

3.3、使用数据库

USE test
-- USE 库名

3.4、查看数据库

SHOW DATABASE
-- 查看所有的数据库

4、数据字段

4.1、数值

名称含义字节长度备注
tinyint很小的数据1
smallint较小的数据2
mediumint中等大小的数据3
int整数4
bigint较大数据8
float单精度浮点数4
double双精度浮点数8
decimal字符串型浮点数金融计算，避免精度问题使用

4.2、字符串

名称含义字节长度备注
char固定字符串0~255char(n)在定义时可以通过 n 设置字符的个数
varchar可变长度字符串0~2^16-1(65535)varchar(n) 在定义时可以通过 n 设置字符的个数
tinytext小型文本2^8-1
text文本2^16-1

4.3、时期类型

名称含义格式
date日期YYYY-MM-DD
time时间HH:mm:ss
datetime日期时间YYYY-MM-DD HH:mm:ss
timestamp时间戳毫秒数
tear年份YYYY

4.5、字段属性

1.Unsigned

  • 无符号数
  • 添加该属性后不能填入负数

2.zreofill

  • 填充零
  • 不足的位数，使用0进行填充

3.自增

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

4.非空

  • 设置之后，如果不进行赋值，数据无法插入或修改，会进行报错。
  • 如果不设置非空，不填写值默认是NULL

5.默认

  • 设置默认值

5、数据表

5.1、数据表类型

1.引擎

INNODB：默认使用。

MYISAM：5.5之前的。

MYISAMINNODB
事务不支持支持
数据行锁定不支持（支持表锁）支持
外键约束不支持支持
全文检索支持支持（5.6.4）
表空间较小较大
  • MYISAM 节约空间，速度快
  • INNODB 安全性高，事务处理，多表多用户操作

2.编码

不设置情况下，将会是MySQL的默认编码Latin1，不支持中文。

修改编码

方式一：（推荐 提高通用性）

创建表时设置编码

CHARSET=utf8  -- 设置UTF-8编码

方式二：

在my.ini修改默认编码

character-set-server=utf8

5.2、创建数据库表

格式

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

举例：

-- AUTO_INCREMENT 自增
-- PRIMARY KEY 主键

CREATE TABLE IF NOT EXISTS `student` (
	`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 '出生日期',
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

5.3、修改删除表

-- 修改数据库表名
ALTER TABLE student RENAME AS stu;

-- 增加表的字段 ALTER TABLE 表名 ADD 新增字段名 字段属性;
ALTER TABLE stu ADD sex VARCHAR(3);

-- 修改表的字段 （重命名，修改约束!）
-- ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE stu MODIFY age INT(10); -- 修改约束

-- ALTER TABLE 表名 CHANGE 旧类名 新列名 [列属性];
ALTER TABLE stu CHANGE age agenew VARCHAR(2); -- 重命名

-- 删除表字段
-- ALTER TABLE 表名 DROP 字段名;
ALTER TABLE stu DROP sex;

-- 删除表
-- DROP TABLE [条件] 表名
DROP TABLE IF EXISTS stu

5.4、补充

sql 大小写不敏感

SHOW CREATE DATABASE test;  -- 查看数据库创建的语句

SHOW CREATE TABLE student; 	-- 查看表创建的语句

DESC student;				-- 查看表结构

6、MySQL数据管理

6.1、外键

表的外键是另一表的主键, 外键可以有重复的, 可以是空值。是用来和其他表建立联系用的，一个表可以有多个外键。

但是删除外键关系的主表时，必须先删除从表（引用者），才能进行删除。

1.在建表时创建外键

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

-- 学生表的gradeid 字段 引用年级表的gradeid字段
-- 学生表
CREATE TABLE IF NOT EXISTS `student` (
	`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 '学生年级',
	PRIMARY KEY (`id`),
	-- 定义外键key
	-- 给这个外键添加约束(执行引用) references 引用表(引用字段)
	KEY `FK_gradeid` (`gradeid`),
	CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

2.添加外键关系

-- ALTER TABLE 表  ADD CONSTRAINT 约束名 FOREIGN KEY (作为外键的列) REFERENCES 引用表(引用字段)

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

ps

以上是创建的物理外键，数据库级别。

因此参考阿里JAVA规范 得出： 不得使用外键与级联，一切外键概念必须在应用层解决。

6.2、DML语言

DML语言：数据操作语言

  • insert
  • update
  • delete

6.3、插入

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

-- 插入语句(添加数据)
-- 插入语句要实现 数据与字段一一对应
-- insert into 表名 ([字段名1,字段名2]) values ('值1','值2')
INSERT INTO `grade` (`gradename`) VALUES ('一年级')

-- 插入多个值
INSERT INTO `grade` (`gradename`) VALUES ('二年级'),('三年级')

INSERT INTO `student` (`name`,`pwd`,`sex`,`gradeid`) VALUES ('张三','aaa','男',1),('李四','bbb','男人',2)

6.4、更新

语法：update 表名 set 列名=值,类名=值 where [条件]

-- update 表名 set column_name=value,[column_name=value,...] where [条件]
-- 不指定条件的情况下，会修改所有的记录：
UPDATE `student` SET `name`='三', `pwd`='666' WHERE `id`=1 AND `sex`='男' ;
操作符含义实例结果
=等于
<> 或 !=不等于
>大于
<小于
>=大于等于
<=小于等于
BETWEEN …AND范围内BETWEEN 2 AND 52到5之间
AND和id>2 and id<52到5之间
OR或id>5 or id<2大于5的 和小于2的

6.5、删除

1. delete

语法：DELETE FROM 表名 [WHERE 条件]

如果不加条件将会全表清除

-- DELETE FROM 表名 [WHERE 条件]  
DELETE FROM `student` WHERE `id`=1;

delete删除的问题，重启数据库

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

2.truncate

完全清空一个数据库表，表结构，索引与约束不会改变。

语法：TRUNCATE 表名

-- 清空表
TRUNCATE `student`

3.delete与truncate区别

相同点：都能删除数据，都不会删除表结构。
不同点：

  • truncate重新设置自增列，自增计数器会归零。
  • truncate不会影响事务。
  • delete删除不会重新设置自增列。

7、DQL查询数据

SELECT[ALL|DISTINCT]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[LIMIT]{row_count | row_countOFFSET offset}

7.1、简单查询

语法：SELECT 字段 FROM 表

-- SELECT 字段 FROM 表;
-- 查询全部学生
SELECT * FROM student;

-- 查询全部学生的学号与姓名
SELECT `studentno`,`studentname` FROM student;

-- 为查询结果设置别名
SELECT `studentno` AS 学号,`studentname`AS 姓名 FROM student AS stu;
-- 也可以忽略AS
SELECT `studentno`  学号,`studentname` 姓名 FROM student stu;

-- 拼接函数
SELECT CONCAT('姓名：',studentname) AS 新名字 FROM student;

7.2、去重

作用：去除SELECT查询出结果中重复的数据

-- 去除studentno重复项
SELECT DISTINCT `studentno`  FROM result;

7.3、数据库的列（表达式）

-- 查询数据库版本
select version();
-- 查询计算表达式
select 100*3-1 as 计算结果;
-- 查询自增的步长
select @@auto_increment_increment;
-- 数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量...select 表达式 from 表名；

7.4、where条件子句

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

1.逻辑运算符
运算符语法描述
and &&a and b,a&&b逻辑与，两个都为真，结果为真
ora or b逻辑或，其中一个为真，结果为真
not !not a,!a逻辑非，真为假，假为真
-- AND
SELECT studentno,studentresult FROM result WHERE studentresult > 95 && studentresult < 100;
SELECT studentno,studentresult FROM result WHERE studentresult > 95 AND studentresult < 100;
SELECT studentno,studentresult FROM result WHERE studentresult BETWEEN 95 AND 100;

-- NOT
SELECT studentno,studentresult FROM result WHERE NOT studentno = 1000;
SELECT studentno,studentresult FROM result WHERE studentno != 1000;
2. 比较运算符
运算符语法描述
IS NULLa is null如果操作符为null,结果为真
IS NOT NULLa is not null如果操作符为null,结果为假
BETWEENa between b and c若a在b与c之间，则结果为真
Likea like ba 符合b，则结果为真
INa in (a1,a2,a3…)a是a1，a2，a3其中的一个值，结果为真
-- LIKE 使用 %（代表0到任意字符） _（一个字符）

-- 姓刘的同学
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘%';

-- 姓刘的同学，名字只有一个字
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '刘_';

-- 名字中包含新这个字
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname LIKE '%新%';

-- in (具体的一个或多个值)
SELECT `studentno`,`studentname` FROM `student`
WHERE studentno IN (1000,1001);

-- 空字符串 与空值

-- 没有姓名的记录
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname IS NULL OR studentname='';

-- 姓名不为空的信息
SELECT `studentno`,`studentname` FROM `student`
WHERE studentname IS NOT NULL ;

7.5、联表查询

img

操作描述使用描述
left join会从左表中返回所有的值，即使右表中没有匹配FROM a LEFT JOIN b以a表为基准
right jion会从右表中返回所有的值，即使左表中没有匹配FROM a RIGHT JOIN b以b表为基准
inner join如果表中至少有一个匹配，就返回行FROM a INNER JOIN b
-- 模板
-- select 显示字段 FROM 表名 连接方式 JOIN 连接表名 on 交叉条件
-- 				FROM a INNER JOIN b
-- 				FROM a RIGHT JOIN b
-- 				FROM a LEFT JOIN b

-- 例子

-- 内连接
SELECT  s.studentno,studentname,subjectno,studentresult
FROM student s
INNER JOIN result r
ON s.studentno = r.studentno

-- 右连接
SELECT  s.studentno,studentname,subjectno,studentresult
FROM student s
RIGHT 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

--  三表连查
SELECT  s.studentno,studentname,subjectname,studentresult
FROM student s
RIGHT JOIN result r
ON s.studentno = r.studentno
INNER JOIN `subject` sub
ON r.subjectno = sub.subjectno

自连接

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

7.6、分页与排序

1.排序

语法： ORDER BY 字段名 排序方式

排序方式：

  • ASC ：升序，从小到大排序（默认方式）
  • DESC ：降序，从大到小排序
-- 排序 order ASC 升序， DESC 降序 。默认ASC，升序
-- 语法： ORDER BY 字段名 排序方式

SELECT * FROM result 
ORDER BY studentresult DESC;

2.分页

语法：imit (起始值,页面的大小)

-- 分页
-- 语法： limit 起始值,页面的大小
-- LIMIT 0,3;  0~2	（1-1）*3~1*3
-- LIMIT 3,3;  3~6	（2-1）*3~2*3
-- LIMIT 6,3;  6~9	（3-1）*3~3*3
-- LIMIT n*3,3;  	 (n-1)*3~n*3
-- 总页数 [数据总数/页面大小]有余数则加一
SELECT *  FROM result 
LIMIT 0,2;

7.7、子查询

本质：把一个查询的结果在另一个查询中使用就叫做子查询

-- 查询 高等数学-1的所有考试结果，降序排序
-- 
-- 联表查询
SELECT `studentno`,`subjectname`,`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 r 
WHERE `subjectno` IN (
SELECT `subjectno` FROM `subject` 
WHERE `subjectname` =  '高等数学-1'
)
ORDER BY `studentresult` DESC

7.8、函数

1.常用函数

数学运算

-- 数学运算
SELECT ABS(-10)				-- 绝对值
SELECT CEILING(9.1)			-- 向上取整
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    upper('wo') # 转大写字母
select    lower('WO') # 转小写字母
select    instr('sunyiwenlong','y') # 返回第一次出现的索引，索引从1开始
select    replace（'坚持就能成功','坚持','努力'） # 替换出现的指定字符串
select    substr('我们都一样',2,4) # 从第二个开始截取4个。结果是‘们都一’
select    reverse('hello') # 反转字符串

时间和日期

-- 时间和日期
select current_date() # 获取当前日期
select now() # 获取当前时间
select localtime() # 获取本地时间
select sysdate() # 获取系统时间

系统

-- 系统
select system_user() # 获取系统当前登录用户（mysql登录用户）
select user() # 获取系统当前登录用户的简写
select version() # 获取系统版本

2.聚合函数

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

统计表中数据量

-- count(字段) :会忽略所有的null值
select count(name) from student

-- count(*) :不会忽略null,本质是计算行数
select count(*) from student

-- count(1) :不会忽略null,本质是计算行数
select count(1) from student

应用聚合函数

-- 聚合函数应用
SELECT SUM(`studentresult`) AS 总和 FROM result
SELECT AVG(`studentresult`) AS 平均分 FROM result
SELECT MAX(`studentresult`) AS 最高分 FROM result
SELECT MIN(`studentresult`) AS 最低分 FROM result

7.9、分组和过滤

分组后的过滤条件要使用 HAVING （因为where 的执行顺序在GROUP BY前 ）

公式 GROUP BY 分组字段 HAVING 条件

-- 分组
SELECT `subjectname`,AVG(`studentresult`)AS 平均分,MIN(`studentresult`),MAX(`studentresult`)
FROM result r
INNER JOIN `subject` sub
ON r.`subjectno` = sub.`subjectno`
GROUP BY r.`subjectno`
HAVING 平均分>80

8、事务

8.1、什么是事务

定义：事务就是一个对数据库操作的序列，是一个不可分割的工作单位，要不这个序列里面的操作全部执行，要不全部不执行。

8.2 ACID

1. 原子性(Atomicity)

事务中的所有操作作为一个整体像原子一样不可分割，要么全部成功,要么全部失败。

2. 一致性(Consistency)

事务的执行结果必须使数据库从一个一致性状态到另一个一致性状态。

一致性状态是指:

  1. 系统的状态满足数据的完整性约束(主码,参照完整性,check约束等)
  2. 系统的状态反应数据库本应描述的现实世界的真实状态,比如转账前后两个账户的金额总和应该保持不变

3. 隔离性(Isolation)

并发执行的事务不会相互影响,其对数据库的影响和它们串行执行时一样。比如多个用户同时往一个账户转账,最后账户的结果应该和他们按先后次序转账的结果一样。

4. 持久性(Durability)

事务一旦提交,其对数据库的更新就是持久的。任何事务或系统故障都不会导致数据丢失。

8.3、常见的并发异常

1. 脏读

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

2. 不可重复读

不可重复读是指一个事务对同一数据的读取结果前后不一致。

3. 幻读

幻读是指事务读取某个范围的数据时，因为其他事务的操作导致前后两次读取的结果不一致

4. 脏写

脏写是指事务回滚了其他事务对数据项的已提交修改

8.4、事务实现

1. 事务设置

-- mysql 默认开启事务	自动提交
SET autocommit = 0 /* 关闭自动提交*/
SET autocommit = 1 /* 开启自动提交（默认的）*/

2.手动处理事务

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

-- 事务内容
INSERT xx

-- 提交: 持久化（成功）
COMMIT

-- 回滚: 回到原始状态（失败）
ROLLBACK


-- 事务结束

SAVEPOINT 保存点名  		-- 设置一个保存点
ROLLBACK TO SAVEPOINT 保存点名	-- 回滚到保存点
RELEASE SAVEPOINT 保存点名	-- 撤销保存点

9、索引

索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

9.1、索引分类

  • 主键索引（PRIMARY KEY）
    • 唯一的标识，主键不可重复
  • 唯一索引（UNIQUE KEY）
    • 避免重复行数据出现，可以有多个列标记为唯一索引
  • 常规索引（KEY/INDEX）
    • 默认的
  • 全文索引（FullText）
    • 快速定位数据（不是所有数据库都支持）

9.2、索引操作

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

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

-- 增加索引															[索引名]				(列名)
ALTER TABLE student ADD FULLTEXT INDEX studentnamefull (studentname);

-- 创建简单索引
-- CREATE INDEX 索引名 on 表名(字段名)
CREATE INDEX index_name ON table_name (column_name) 

-- 创建唯一索引
-- CREATE UNIQUE INDEX 索引名 on 表名(字段名)
CREATE UNIQUE INDEX index_name ON table_name (column_name)

-- EXPLAIN分析sql执行的情况
EXPLAIN SELECT * FROM student

-- 删除索引
-- ALTER TABLE 表名 DROP INDEX 索引名
ALTER TABLE table_name DROP INDEX index_name

9.3、测试索引

准备数据

-- app用户表
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 NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 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`,`eamil`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com','123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
	RETURN i;
END;

SELECT mock_data() -- 执行此函数 生成一百万条数据

9.4、索引原则

  • 索引不是越多越好

  • 不要对经常变动的数据加索引。

  • 小数据量的表不需要加索引。

  • 索引一般加在常用来查询的字段上

10、权限管理与备份

10.1、用户管理

方式：navicat用户管理。SQL命令操作管理。
用户表：mysql.user
本质：读这张表进行增删改查

# 创建用户
create user 用户名 identified by '密码';
/*<用户名>
	指定创建用户账号，格式为 'user_name'@'host_name'。这里user_name是用户名，host_name为主机名，即用户连接 MySQL 时所在主机的名字。若在创建的过程中，只给出了账户的用户名，而没指定主机名，则主机名默认为“%”，表示任意ip。 
*/
# 修改当前用户密码
set password = password('123456')；
# 修改指定用户密码
set password for 用户名 = password('123456')；

# 8.0以上版本 修改用户密码
alter user 'root'@'localhost' identified by '设置的新密码';
# 刷新权限表：
flush privileges;

# 重命名
rename user 原名字 to 新名字；
# 用户授权，(all privilieges 全部的权限  on 库.表)给用户授予所有权限（除了给别人授权）
grant all privilieges on *.* to 用户名；
# 查看指定用户的权限
show grants for 用户名；
# 查看root用户的权限
show grants for root@localhost；
# 撤销权限，撤销该用户所有表的所有权限
revoke all privilieges on *.* from 用户名；
# 删除该用户
drop user 用户名；

10.2、数据库备份

为什么备份：

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

MySQL数据库备份方式

  • 直接拷贝物理文件。
  • 在可视化工具手动导出。
  • 使用命令行导出 mysqldump 命令行使用。

  1. 导出备份

     -- mysqldump    -h 主机    -u 用户名    -p 密码    数据库 表名1 > 物理磁盘位置/文件名 
 mysqldump    -hlocalhost    -uroot    -p123456    school student > D:/1.sql 
 -- mysqldump    -h 主机    -u 用户名    -p 密码    数据库 表名1 表名2 表名3 > 物理磁盘位置/文件名 
 mysqldump    -hlocalhost    -uroot    -p123456    school student result > D:/1.sql 
 -- mysqldump    -h 主机    -u 用户名    -p 密码    数据库 > 物理磁盘位置/文件名 
 mysqldump    -hlocalhost    -uroot    -p123456    school > D:/1.sql

  2. 导入备份

    # 登录的情况下，切换到指定的数据库。（导入数据库时，不用切换到数据库）
# source 备份文件
source d:/a.sql
# 没有登录的情况下
mysql  -u用户名 -p密码 库名< 备份文件

11、规范数据库设计

11.1、为什么要设计

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

糟糕的数据库设计：
  • 数据冗余，浪费空间（如：数据字段大量重复等）
  • 数据库插入和删除都会麻烦、异常（如：使用物理外键）
  • 程序的性能差
良好的数据库设计：
  • 节约内存空间
  • 保证数据库的完整性
  • 方便我们开发系统
软件开发中，关于数据库的设计：
  • 分析需求：分析业务和需要处理的数据库的需求
  • 概要设计：设计关系图E-R图

11.2、设计步骤

  1. 收集信息，分析需求
  2. 标识实体（把需求落地到字段）
  3. 标识实体之间的关系

11.3、三大范式

第一范式（1NF）

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

第二范式（2NF）

前提：满足第一范式

每张表只描述一件事

第三范式（3NF）

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

确保数据库中的每一列数据都和主键直接相关，二不是间接相关。

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

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

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

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

12、JDBC

12.1、数据库驱动

应用程序通过驱动连接数据库。MySQL驱动、Oracle驱动

12.2、JDBC

Java操作数据库的规范。由数据库层面统一遵守，程序统一使用。

12.3、第一个JDBC程序

1. 准备测试数据库

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','1980-12-04'),
('2','lisi','123456','lisi@sina.com','1981-12-04'),
('3','wangwu','123456','wangwu@sina.com','1979-12-04');

2. 创建项目，添加数据库驱动

IDEA中需要将驱动添加为库

3. 编写代码

  1. 加载驱动
  2. 连接数据库DriverManager
  3. 获取sql的执行对象statement
  4. 写sql
  5. 执行sql
  6. 处理结果集
  7. 释放连接

4.实例

package com.test01;

import java.sql.*;

public class JdbcDemo01 {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
//        1.加载驱动5.6后驱动名变了
        Class.forName("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding-utf8&useSSL=true";
        String username="root";
        String pasword="root";
//        连接成功，返回数据库对象
        Connection connection = DriverManager.getConnection(url, username, pasword);
//        创建执行sql对象
        Statement statement = connection.createStatement();
//        编写执行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("email"+resultSet.getObject("email"));
        }
//        释放连接
        resultSet.close();
        statement.close();
        connection.close();
    }
}

12.4、解释

DriverManager

//        1.加载驱动类
        Class.forName("com.mysql.cj.jdbc.Driver");
//        2.用户信息和url
        String url = "jdbc:mysql://127.0.0.1:3306/jdbcstudy?useUnicode=true&characterEncoding-utf8&useSSL=true";
        String username="root";
        String pasword="root";
//        3.连接成功，返回数据库对象
        Connection connection = DriverManager.getConnection(url, username, pasword);

Connection

//        连接成功，返回数据库对象
        Connection connection = DriverManager.getConnection(url, username, pasword);
//        事务回滚
        connection.rollback();
//        事务提交
        connection.commit();
//        数据库设置自动提交
        connection.setAutoCommit(true);

Statement

//        创建执行sql对象
        Statement statement = connection.createStatement();
//        编写执行sql
        String sql="SELECT * from users;";
//		  执行sql
        ResultSet resultSet = statement.executeQuery(sql);
/*
//        执行sql
        statement.execute();
//        执行查询 返回ResultSet
        statement.executeQuery();
//        执行更新，返回受影响行数
        statement.executeUpdate();
*/

ResultSet

封装返回的查询结果

ResultSet resultSet = statement.executeQuery(sql);

resultSet.getObject();//通用
resultSet.getInt();
resultSet.getString();//等等


//移动到第一行
resultSet.beforeFirst();
//移动到最后一行
resultSet.afterLast();
//前移一行
resultSet.previous();
//后移一行
resultSet.next();
//移动到指定行
resultSet.absolute(row);

12.5、statement对象

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

发送增删改查语句即可。

statement对象executeUpdate方法用于完成数据删除操作。执行后还会返回一个整数（为执行的增删改语句导致

数据库内数据改变的行数）。

statement对象executeQuery方法执行查询语句，返回结果为ResultSet对象。

CRUD操作 – create

使用executeUpdate（String sql）方法完成数据添加操作，示例操作：

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

CRUD操作 – delete

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

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

CRUD操作 – update

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

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

CRUD操作 – read

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

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

实例

package com.zrz.test02;

import com.zrz.test02.utils.JdbcUtils;

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

public class TestInster {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = JdbcUtils.getConnection();//获取数据连接
            st = con.createStatement();//获得sql执行对象
            String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES('4','test','123456','ss@sina.com','1980-12-04')";
//            int i = st.executeUpdate(sql);
//            if(i>0){
//                System.out.println("ok");
//            }
            String sql1="SELECT * from users;";
            rs = st.executeQuery(sql1);
            while (rs.next()){
                System.out.println("id"+rs.getObject("id"));
                System.out.println("NAME"+rs.getObject("NAME"));
                System.out.println("email"+rs.getObject("email"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,st,rs);
        }
    }
}

12.6、PreparedStatement对象

PreparedStatement可以防止SQL注入，效率变高

PreparedStatement 会将传递进来的参数当做字符，假如说其中存在转义字符会将其转义。

package com.zrz.test03;

import com.zrz.test02.utils.JdbcUtils;

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

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

        try {
            con = JdbcUtils.getConnection();

            String sql = "INSERT INTO `users`(`id`,`NAME`,`PASSWORD`,`email`,`birthday`)" +
                    "VALUES(?,?,?,?,?)";
            //区别
            //使用? 进行占位
            pre = con.prepareStatement(sql);//先预编译sql，不进行执行

            //手动给参数赋值
            pre.setInt(1,4); //id
            pre.setString(2,"ce");
            pre.setString(3,"123212");
            pre.setString(4,"xin");
            // sql.Date 是数据库存储的时间
            // util.Date是java的时间
            pre.setDate(5,new java.sql.Date(new Date().getTime()));

            //执行
            int i = pre.executeUpdate();
            if(i>0){
                System.out.println("ok");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JdbcUtils.release(con,pre,null);
        }
    }
}

12.7、事务

1.开启事务conn.setAutoCommit(false);//开启事务

2.一组事务执行完毕，开启事务

3.可以在catch中显示的定义回滚语句，但是默认失败就会回滚

package com.zrz.test04;

import com.zrz.test02.utils.JdbcUtils;

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

public class TestTransaction1 {
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement st = null;
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            //关闭数据库自动提交,数据库会自动开启事务
            conn.setAutoCommit(false);//开启事务
            String sql1 = "update account set money=money-100 where name ='A'";
            st = conn.prepareStatement(sql1);
            st.executeUpdate();

            String sql2 = "update account set money=money+100 where name ='B'";
            st = conn.prepareStatement(sql2);
            st.executeUpdate();

            //业务完毕，提交事务
            conn.commit();
            System.out.println("ok");

        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            JdbcUtils.release(conn, st, rs);
        }
    }

}

12.8、数据库连接池

  • 数据库连接—-执行完毕—-释放；连接—-释放，十分浪费系统资源。
  • 池化技术：准备一些预先的资源，过来就连接预先准备好的。

l连接数例子：

常用连接数：10
最小连接数：10
最大连接数：100 业务最高承载上线
等待超时：100ms

开源数据源实现

  • DBCP
  • C3P0
  • Druid
三途河畔人
关注
专栏目录
JSP连接MySQL数据库
不迁怒，不贰过。小知识，大智慧。
12-06 6717
JSP连接MySQL数据库：JSP中的MySQL驱动程序配置、 MySQL中创建表、 JSP中SELECT操作、 JavaBean 定义与使用。
Mysql数据库基础语句
m0_54594153的博客
09-11 3333
DDL语句可用于创建数据库对象（库，表，索引）删除数据库和表2.1 创建数据库和表—create#创建一个名为nba的数据库#创建一个名为james的表,（定义id不为空，名字不为空，年龄不为空，成绩可为空默认为“0”，主键为id）# 在任意库中创建nba库中的james表sql语言的分类DDL数据定义语言，用于创建和删除数据库表操作create（创建） drop（删除）DML：数据操作语言，用于管理表中的记录，对数据进行增，删，该，的操作。
MySQL数据库基本操作
Drw_Dcm的博客
09-09 5万+
MySQL数据库基本操作
C++连接mysql数据库
ljjjjjjjjjjj的博客
11-12 4816
首先在本地下载mysql数据库，或者使用在线数据库，在数据库中创建一个表，往表里填入数据，不然无法读取到数据。运行代码前，将libmysql的静态库和动态库拷贝到代码路径，c++工程的包含路径配置成mysql的include路径。
MySQL数据库命令大全
ITjun666的博客
11-06 1万+
MySQL数据库基本流程与命令适合初学者练习!
MySQL数据库安装教程
nw2503079547的博客
08-16 4117
MySQL数据库简易安装教程教学
mysql 数据库备份
桑
05-02 2737
mysqldump 备份 source还原，mysql还原
进入Mysql数据库操作
weixin_68579466的博客
08-09 1万+
独享表空间存储方式使用".ibd"文件来存放数据，且每个表一个".ibd"文件，文件存放在和MyISAM数据相同的位置。另外还有".ibd"和ibdata文件，这两种文件都是用来存放Innodb数据的，之所以有两种文件来存放Innodb的数据（包括索引），是因为Innodb的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据，还是独享表空。每一个MyISAM表都会有一个".MYD"文件与之对应，同样存放于所属数据库的文件夹下，和".frm"文件在一起。...
Xshell操作Mysql数据库
Northofnanshan的博客
11-09 3433
Xshell 操作数据库
MySQL数据库常用命令大全（完整）（表格形式）
YOU__FEI的博客
09-16 2万+
注意：sql语句以分号结尾，分号是英文的分号mysql是不见“;”不执行，“;”表示结束！ \c用来终止一条命令的输入。 本地登录| mysql -uroot -p密码。本地登录（隐藏密码的形式） | mysql -uroot -p 然后回车输入密码。查看有哪些数据库 | show databases;..........
spring+springmvc+mybatis搭建的一个系统附带mysql数据库 完整模板
04-24
在提供的资源中，"invoicemanager.sql"是数据库脚本，用于初始化数据库表结构和数据，可能是系统中的发票管理模块的基础。开发者可以通过导入这个SQL脚本来设置数据库环境，为SSM系统提供数据支持。 "invoice"目录...
Java连接MySQL数据库的超级详细步骤（Windows）
热门推荐
firstlt0217的博客
05-19 11万+
1. 数据准备 1. 下载JDK 下载Java开发工具包JDK，下载地址：https://www.oracle.com/java/technologies/javase-jdk14-downloads.html，下载待用。 2. 下载Mysql Java 连接 MySQL 需要驱动包，下载地址为：http://dev.mysql.com/downloads/connector/j/。 解压后得到 jar 库文件，待用，最终会将红框文件导入相应项目文件中。 3. 下载MySQL 下载地址：https:/
MySQL之复合查询与内外连接
zdlynj的博客
09-30 1220
前面我们讲解的mysql表的查询都是对一张表进行查询，即数据的查询都是在某一时刻对一个表进行操作的。而在实际开发中，我们往往还需要对多个表同时进行查询。我们这里使用的测试表，为雇员信息表(来自Oracle 9i的经典测试表)：EMP员工表，DEPT部门表，SALGRADE工资等级表。
点评项目-2-完善注册登录业务
m0_75138009的博客
09-29 581
需要处理的业务：在网页发送 /user/code 路径下的 post 请求后，我们需要检验手机号后，向合法的手机号发送一个随机生成的电话号。
World of Warcraft [CLASSIC][80][Grandel] Call to Arms: Victory in Wintergrasp
最新发布
qq 117791303
10-02 282
Wintergrasp 冬拥湖 120 VS 120
Spring的热部署工具和数据库密码加盐操作
2301_77053417的博客
09-28 791
加密过程：用户输入的密码+盐值 --> 字符串1，然后使用md5对字符串1加密得到字符串2，然后字符串2+盐值 --> 字符串3，这个字符串3就是存储在数据库中的密文。背景：如果我们的密码明文存储在数据库中，要是被窃取了是非常的危险的，所以我们在数据库中存储的密码不能是明文的，需要进行加密后存储，也就是数据库中存储的是密文。解密过程：取出数据库中的盐值和用户输入的密码得到字符串1，使用md5对字符串1加密得到字符串2，然后验证字符串2和数据库中的密文即可。做好以上的步骤，你项目中的热部署就已经做好了。
Redis事务
zhzjn的博客
09-30 362
Redis事务可以通过MULTI、EXEC、WATCH和DISCARD指令来实现。1. MULTI指令：表示事务的开始，之后的所有指令都会被逐一记录，但不会立即执行。2. EXEC指令：表示事务的执行，会按照记录的指令逐一执行。3. WATCH指令：可以监视一个或多个键的变化，如果在事务执行期间被其他客户端修改，事务会被回滚。4. DISCARD指令：表示事务的撤销，可以放弃之前记录的指令。
【RocketMQ】RocketMQ应用难点
记录一名在读研究生的学习笔记、感悟、开发产物
09-30 883
本文探讨了RocketMQ中消息重复消费的问题及其解决方案，尤其是在CLUSTERING模式下的扩容影响。文章分析了重复消费的原因，如广播模式、负载均衡模式下的多consumerGroup消费、消费者组内的动态变化及网络延迟等，并提出了利用唯一标识进行去重的方法。此外，还讨论了如何选择合适的存储机制以高效处理大量消息标识，如HashMap、MySQL、Redis以及推荐的布隆过滤器等方案。对于防止消息堆积与确保消息不丢失，也给出了相应的策略和技术措施。
IT基础监控范围和对象
MXsoft618的博客
09-29 601
综上所述，监控易的监控范围非常广泛，几乎涵盖了所有主流的IT基础设施以及相关的设备和系统。通过全面、实时、准确的监控功能，监控易为运维团队提供了强大的支持，帮助他们及时发现并解决潜在的问题，确保业务系统的稳定运行和高效运维。除了上述监控范围外，监控易还支持其他扩展监控功能，如被动式设备监控、自定义脚本监控、高级监测点监控等。监控易作为一款由美信时代独立自主研发的分布式一体化集中监控平台，其监控范围极为广泛，几乎涵盖了所有主流的IT基础设施以及相关的设备和系统。
MySQL数据库操作详解
"使用数据库操作-MySQL数据库" 在IT领域，数据库是存储和管理信息的核心工具，而MySQL作为一款流行的开源关系型数据库管理系统，被广泛应用于各种规模的应用中。本资源主要探讨了如何使用MySQL数据库，包括数据库的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值