2022-06-18 13:13 北京科技大学 Java

关注

11-MySQL学习记录

MySQL

数据库分类

关系型数据库
- mysql、oracle、sqlserver、DB2、SQLlite
非关系型数据库
- redis、MongoDB

DBMS-数据库管理系统

数据库管理软件，科学有效的管理数据，维护和获取数据

DDL（设计）、DML（操作）、DQL（查询）、DCL（控制）

数据库级别的MD5加密

UPDATE angel SET `password` = MD5(`password`) ;

简介

MySQL是一个关系型数据库管理系统，体积小，速度快，可做集群，开源

官网：https://www.mysql.com/

alt

命令

连接数据库

mysql -uroot -p123456
#也可以 
mysql -uroot -p   #回车后再敲密码

UPDATE mysql.`user` SET authentication_string=PASSWORD('123456') 
WHERE USER ='root' AND HOST = 'localhost';  ##修改密码
flush privileges;## 刷新权限

操作数据库-增删切查

create database [if not exists] karottes CHARACTER SET utf8 COLLATE utf8_general_ci;	-- 创建数据库
use karottes;   -- 切换数据库
drop database if exists karottes;	-- 删除数据库
show databases;  -- 显示所有的数据库
show create database karottes; -- 查看创建数据的语句  CREATE DATABASE `karottes` /*!40100 DEFAULT CHARACTER SET latin1 */

操作表

-- 创建表 karotte
CREATE TABLE [IF NOT EXISTS] karotte(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '号码',
	`name` VARCHAR(32) NOT NULL DEFAULT '卡卡罗特' COMMENT '姓名',
	`password` VARCHAR(16) NOT NULL DEFAULT '000000' COMMENT '密码',
	`sex` CHAR(2) NOT NULL DEFAULT '0' COMMENT '性别',
	`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
	`address` VARCHAR(128) DEFAULT NULL COMMENT '地址',
	`email` VARCHAR(32) DEFAULT NULL COMMENT '邮箱',
	PRIMARY KEY(`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
show create table karotte; -- 查看创建表的语句
desc karotte;  -- 显示表的结构
-- 如果建表的时候没有设置字符集，可以再my.ini文件中新增
character-set-server=utf8
ALTER  TABLE karotte RENAME AS karotter; -- 修改表名称
ALTER  TABLE karotter ADD age INT(4); -- 增加一列
ALTER  TABLE karotter MODIFY age VARCHAR(4);   -- 修改约束，不能重命名
ALTER  TABLE karotter CHANGE age bigage VARCHAR(4);   -- 重命名+修改约束
ALTER  TABLE karotter DROP age; -- 删除列
DROP TABLE IF EXISTS karotter;  -- 删除表

所有的删除和新增sql尽量加上判断

数据库管理语言-DML

INSERT INTO karotter (`name`,`password`,sex,birthday,address,email,age)
VALUES ('贝吉塔','456789','1','2022-06-17 19:33:56','贝吉塔行星','bedgette@163.com','28'),
('布尔玛','4567dsa89','0','2022-06-17 19:33:56','地球','buerma@163.com','26');

UPDATE karotter SET sex = 1,PASSWORD='kaka098' WHERE id = 2;

DELETE FROM karotter WHERE id = 3;
TRUNCATE karotter;
-- ------------------------------------------------------------------------------- 
-- 区别：
	-- TRUNCATE 自增计数器归零
	-- 不会影响事务

操作符

= ：等号
<> != ：不等号
between x and y ： x到y的范围，包含x和y

数据库查询语言-DQL

SELECT distinct CONCAT('赛亚人：',NAME) AS pname FROM karotter 
WHERE id = 2 AND age >= 23 order by pname desc LIMIT 0,2;

SELECT sex,COUNT(1) AS num FROM karotter 
GROUP BY sex HAVING  COUNT(1)>0  
order by COUNT(1) desc 
limit 0,3;

SELECT VERSION(); -- 查询数据库版本

-- is null, is not null
-- like 模糊查询，有可能使索引失效  %在后面则索引生效，否则，前面和两端都会使索引失效
SELECT DISTINCT CONCAT('赛亚人：',NAME) AS pname FROM karotter 
WHERE  NAME LIKE '%布%'

-- field in ( x1,x2,x3)  字段的值为x1,x2,x3三者中的一个

-- 联表查   table0 x (inner/left/right) join table1 y on x.id = y.id

-- 嵌套子查询语句 ===》 select结果作为字段或者条件

-- 聚合函数
-- count(*/1/field) , sum(field), avg(field), max(field),min(field)
-- field的时候，不计算null值

数据库表列类型

数值

tinyint 1个字节
smallint 2个字节
mediumint 3个字节
int 4个字节
bigint 8个字节
float 单精度浮点数 4个字节
double 多精度浮点数 8个字节
decimal 字符串形式的浮点数金融计算的时候一般使用decimal

字符串

char 固定大小 0-255
varchar 可变长度 0-65535
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和任何数据运算结果都是null

数据库表列属性

Unsigned

无符号的整数
声明该列不能为负数

zerofill

不足的位数，用0填充

autoincrement-自增

自动在上一条记录的基础上加一
通常用来设计唯一的主键约束
必须是整数

非空-notNULL

不赋值的话报错

默认-default

如果不赋值，设置默认值为该字段值

每个表都必须有以下五个字段

主键id
version 乐观锁使用
is_delete 逻辑删除
gmt_create 创建时间
gmt_update 更新时间

MySQL函数

官网：https://dev.mysql.com/doc/refman/5.7/en/functions.html

################### 数学函数  #################
SELECT ABS(-9) ;   -- 绝对值
SELECT CEILING(5.2);  -- 向上取整
SELECT FLOOR(7.5); -- 向下取整
SELECT RAND();  -- 0到1的随机数
SELECT SIGN(0);  -- 符号，整数1，负数-1  0返回0
####################  字符串  ##############
SELECT CHAR_LENGTH('醉卧沙场君莫笑');   -- 字符串长度
SELECT CONCAT('3','dg','sa');	-- 连接字符串
SELECT LOWER('DSFSDF');  -- 转小写
SELECT UPPER('dfsfs');  -- 转大写
SELECT INSTR('我是无敌的','无敌');  -- 索引
SELECT REPLACE('我是无敌的','无敌','最***'); -- 替换
SELECT SUBSTR('我真的是服气了啊！',3,2);  -- 截取字符串
SELECT REVERSE('清晨我上马')  -- 反转
#################### 时间日期 #####################
SELECT CURRENT_DATE();  -- 当前日期
SELECT CURDATE();   -- 同上
SELECT NOW();  -- 当前时间
SELECT LOCALTIME();  -- 本地时间
SELECT SYSDATE();   -- 系统时间
SELECT YEAR(NOW());

数据库引擎

MyISAM与InnoDB 的区别

InnoDB支持事务，MyISAM不支持
InnoDB支持外键，而MyISAM不支持。
InnoDB是聚集索引，使用B+Tree作为索引结构，数据文件是和（主键）索引绑在一起的（表数据文件本身就是按B+Tree组织的一个索引结构），必须要有主键，通过主键索引效率很高。但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此，主键不应该过大，因为主键太大，其他索引也都会很大。

alt

MyISAM是非聚集索引，也是使用B+Tree作为索引结构，索引和数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

也就是说：InnoDB的B+树主键索引的叶子节点就是数据文件，辅助索引的叶子节点是主键的值；而MyISAM的B+树主键索引和辅助索引的叶子节点都是数据文件的地址指针。

alt

InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快（注意不能加有任何WHERE条件）
Innodb不支持全文索引，而MyISAM支持全文索引，在涉及全文索引领域的查询效率上MyISAM速度更快高；PS：5.7以后的InnoDB支持全文索引了
MyISAM表格可以被压缩后进行查询操作
nnoDB支持表、行(默认)级锁，而MyISAM支持表级锁
InnoDB的行锁是实现在索引上的，而不是锁在物理行记录上。潜台词是，如果访问没有命中索引，也无法使用行锁，将要退化为表锁。

-- uid PK
-- 无其他索引
update t_user set age=10 where uid=1;             命中索引，行锁
update t_user set age=10 where uid != 1;           未命中索引，表锁
update t_user set age=10 where name='chackca';    无索引，表锁

Innodb存储文件有frm、ibd，而Myisam是frm、MYD、MYI
- Innodb：frm是表定义文件，ibd是数据文件
- Myisam：frm是表定义文件，myd是数据文件，myi是索引文件

事务

事务特性-ACID

原子性-Atomicity：一个事务里面的操作同时成功或失败
一致性-Consistency：能量守恒定律，事务前后的数据完整性保持一致
隔离性-Isolation：多个事务之间有隔离性，不受干扰
持久性-Durability：事务一旦提交不可逆

事务隔离级别

脏读：读了另一个事务未提交的
不可重复读：读已提交，导致多次读取结果不同
虚读（幻读）：读到另一个事务插入的数据，导致前后读取不一致

事务的操作

set autocommit = 0;-- 关闭自动提交  1是开启
start transaction;
.....
commit; -- 提交事务
rollback; -- 回滚事务
savepoint xxx; -- 设置一个事务保存点
rollback to xxx; -- 回滚到保存点
release savepoint xxx; -- 撤销事务保存点

索引

MySQL官方对索引的定义：索引是帮助MYSQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：数据结构。

索引的分类

主键索引（PRIMARY KEY）
- 唯一的标识，主键不可重复，且只能有一列作为主键
唯一索引（UNIQUE KEY）：行值唯一
常规索引（KEY/INDEX）
全文索引（FullText）

show index from tableName;  -- 查看表全部索引

关于索引的数据结构可以看一下下面这篇文章：

http://blog.codinglabs.org/articles/theory-of-mysql-index.html

mysql备份

备份的必要性

保证重要的数据不丢失
用作数据转移

备份方式

直接拷贝物理文件data目录
用可视化工具手动导出备份

mysqldump命令

## 导出
mysqldump -h127.0.0.1 -uroot -p123456 database1 [tablename1 tablename2....] > ./xxx.sql

## 导入的话最好先登录进去
mysql -uroot -p123456
use xxxx;#仅导入表的话需要切换数据库
source ./xxx.sql

三大范式

数据库表中的每一列都是不可再分的原子数据项
数据表中每一条记录可唯一标识，所有非主键字段必须完全依赖于主键，不能部分依赖于主键

2NF设计原则：一张表只表达一个意思, 对于部分依赖于主键的字段，将这部分字段与依赖的部分主键抽取出来建立新表
非主属性之间不能相互依赖，非主属性之间相互独立，确保每列都和主键列直接相关,而不是间接相关

JDBC

步骤

增加驱动
获取connection
获得statement对象
构建sql

执行sql，返回result

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url,username,password);
Statement statement = con.createStatement();
String sql = "select * from xxx";
ResultSet r = statement.executeQuery(sql);
while(r.next()){
    r.getString().sout;
}

statement对象

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

executeUpdate(String sql)
executeQuery(String sql)

sql注入

sql拼接时额外的字符串中存在一些危害数据库的语句或符号。

为了防止sql注入，增加效率，使用PreparedStatement

PreparedStatement

Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection(url,username,password);
String sql = "select * from xxx where id = ? and age > ?";
PreparedStatement pre = con.prepareStatement(sql);
pre.setInt(1,1);
pre.setString(2,"23");
ResultSet r = pre.executeQuery();
while(r.next()){
    r.getString().sout;
}

事务

Connection connection = DriverManager.getConnection(url);
connection.setAutoCommit(false);//开启事务
connection.commit();//提交事务
connection.rollback();//回滚事务
Savepoint savepoint = connection.setSavepoint("保存点");//设置快照点
connection.rollback(savepoint);//回滚到快照点
connection.releaseSavepoint(savepoint);//取消快照点