MySQL

HLng-Z

已于 2023-04-17 15:52:48 修改

阅读量48

点赞数

文章标签： mysql 数据库 java Powered by 金山文档

于 2023-03-20 15:36:13 首次发布

本文链接：https://blog.csdn.net/m0_61066780/article/details/129668436

版权

MySQL基础使用

-- 所有命令;分号结尾
show databases;       --查看所有数据库
use central_database  -- 切换数据库 
show tables           --查看数据库中所有表
describe mange_device --查看表的所有信息

create database central_database -- 创建数据库

show create database central_database -- 查看数据库创建的语句
show createtable mange_device      -- 查看表创建的语句
desc central_database               -- 查看表结构

exit -- 退出连接

数据库的列类型

数值

tinyin 1字节

smallint 2字节

mediumint 3字节

int 4字节

big 8字节

float 4字节

double 8字节

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 进行运算

数据库的字段属性

Unsigned

无符号证书

声明了该列不能声明为负数

zerofill

0填充

自增

通常理解为自增

设置主键必须整数类型

可以自定义设置主键自增的起始值和步长

非空

如果不赋值就报错

Null 如果不填写默认就是null

默认

设置默认的值

/*
id
version
is_delete
gmt_create
gmt_update
*/

建表

createtable if not exists `student` (
    `id` int(4) notnull auto_increment comment 'id',
  `name` varchar(30) notnull default '匿名' comment '姓名'，
  `password` varchar(20) notnull default '123456' comment '密码'，
  `sex` varchar(20) notnull 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

createtable if not exists 表明 (
    `字段名` 列类型 [属性] [索引] [注释],
  `字段名` 列类型 [属性] [索引] [注释],
    `字段名` 列类型 [属性] [索引] [注释],
  primary key(`字段名`)
)[表类型][字符集设置][注释]

数据表类型

INNODB 默认使用

支持事务

行锁

支持外键

不支持全文索引

表空间较大，约为MYISAM的2倍

MYISAM

不支持事务

表锁

不支持外键

全文索引

较小节约空间

常规使用操作

MYISAM 节约空间速度较快

INNODB 安全性高事务处理多表多用户操作

物理空间位置

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

本质是文件存储

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

InnoDB 在数据库表中只有一个*.frm文件还有上级目录下的ibdata1文件

MYISAM对应文件

*.frm - 表结构定义文件

*.MYD 数据文件（data）

*.MYI 索引文件（index）

设置数据库字符集编码

charset=utf8

不设置的话会是mysql默认的字符编码（不支持中文）

在my.ini 中设置默认的编码(不推荐)

character-set-server=utf8

修改删除表操作

-- alter table 旧表名 rename as 表名
altertable teacher rename as tecacher1 -- 修改表名
-- alter table 表名 add 字段 类型
altertable teacher1 add age int(11)  -- 表新增字段
-- alter table 表名 modify 已有字段 要更改的类型
altertable teacher1 modify age varchar(11) -- 修改约束
-- alter table 表名 change 旧字段名 重命名字段 要更改的类型
altertable teacher1 change age age1 int(11) -- 修改字段名
-- alter table 表名 drop 已有字段
altertable teacher1 drop age1  -- 删除字段
-- drop table if exists 表名
droptable if exists teacher1 -- 删表

创建和删除尽量加上判断以免报错

注意点

`` 字段名使用这个包裹

注释 -- 或者 /**/

sql 大小写不敏感建议小写

符号用英文

Mysql 数据管理

外键（了解即可）

a表的某字段要去引用b表的字段

定义外键key 外键在从表

添加约束 references 引用

alter table `student`
ADD CONSTRAINT FK_gradeid FOREIGN KEY( `gradeid` ) REFERENCES `grade` ( `gradeid`);

物理外键不建议使用（阿里规范）

最佳实践

数据库就是淡出的表存数据

程序实现外键

DML（数据库操作语言）

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

insert

update

delete

添加

-- insert into 表名(字段1,字段2,...) value('v1','v2',...)
insert into test(`name`,`age`) value('zhangsan','12')

-- 插入多个
-- insert into 表名(字段1,字段2,...) value('v1','v2',...),...
insert into
    `test`(`name`,`age`)
value
    ('zhangsan','12'),('lisi','15')

注意如果省略字段 value要与表一一对应

修改

-- update `表名` set `列字段`='值' where 条件
update `student` set `name`='wangwu'where id=1
-- update `表名` set `列字段1`='值1',`列字段2`='值2',... where 条件
update `student` set `name`='wangwu',`age`='10',`birthday`=current_time where id=1
where

-- 运算符

<> 或 !=

between2and3 [2,3]

AND -- 多条件

OR -- 或条件

删除

-- delete from 表名 where 条件
deletefrom `student` where id=1

truncate

-- truncate table 表名
truncate table student -- 删除表

相同点:都能删除数据不删除表结构

不同:

truncate 重新设置自增列计数器归零

truncate 不会影响事务

delete删除问题,重启数据库 ,现象

innodb 自增列会重1开始 ( 存在内存中断电即使)

Myisam 继续从上一个自增量开始 (存在文件中,不会丢失)

DDL（数据库定义语言）

select

数据库最核心的语言

高频使用

查询所有字段

-- select 字段 from 表名
select * from student -- 查全部
select `name` ,`age` from student -- 查指定字段
select `name` as 名字 ,`age` as 年龄 from student as s -- 别名
-- 函数拼接 concat(a,b)
select concat('姓名:',name) as 新名字 from student as s -- 函数拼接

有的时候列名字不是那么见名知意我们起别名字段名 as 别名表名 as 别名

去重 distinct

-- select distinct 字段 from 表 
select distinct `stuNo` from `result`

数据库的列(表达式)

select version() -- 查看当前版本(函数)
select100*3-1as 计算结果  --计算(表达式)
select @@auto_increment_increment -- 查看自增的步长(变量)
-- stuNo字段+1
select `stuNo`+1as'提分后'from `result`
数据库中的表达式: 文本值,列,null,函数,计算表达式,系统变量...
-- select 表达式 from 表

where 条件子句

作用:检索符合条件的

逻辑运算符

and &&

or ||

not !

尽量使用英文字母

select
    `stuNo`,`stuRes`
from
    `result` 
where
    stuRes <= 100AND
    stuRes > 95
----------------------------------------
select
    `stuNo`,`stuRes`
from
    `result` 
where
    stuRes between95and100
----------------------------------------
select
    `stuNo`,`stuRes`
from
    `result` 
where
    stuRes != 100
---------------------------------------
select
    `stuNo`,`stuRes`
from
    `result` 
where
    not stuRes = 100

模糊查询: 比较运算符

is null

is not null

between

select
    `stuNo`,`stuName`
from
    `student` 
where
    stuName like'刘%'
---------------名字后面只有一个字的----------------
select
    `stuNo`,`stuName`
from
    `student` 
where
    stuName like'刘_'
---------------名字后面只有两个字的----------------
select
    `stuNo`,`stuName`
from
    `student` 
where
    stuName like'刘__'
-------------------------------
select
    `stuNo`,`stuName`
from
    `student` 
where
    stuName like'%佳%'
-------------------------------
select
    `stuNo`,`stuName`
from
    `student` 
where
    stuNo in(1001,1002,1003);
-------------------------------
select
    `stuNo`,`stuName`
from
    `student` 
where
    Address=""or Address isnull;
-------------------------------
select
    `stuNo`,`stuName`
from
    `student` 
where
    `birthday` isnotnull;

连表查询

join对比

-- join on(判断条件) 连接查询
-- where  等值查询
/*
*如果表中至少有一个匹配 就返回行
*/
select
    s.studentNo,studentName,studentResult
from student as s
inner join result as r
where
    s.studentNo = r.studentNo
---------------------------------------------
/*
*会从左表中返回所有的值 即使右表中没有匹配
*/
select
    s.studentNo,studentName,studentResult
from student s
right join result r
on
    s.studentNo = r.studentNo
---------------------------------------------
/*
*会从右表中返回所有的值 即使左表中没有匹配
*/

select
    s.studentNo,studentName,studentResult
from student s
left join result r
on
    s.studentNo = r.studentNo
---------------------------------------
-- 查询缺考的 studentResult 为null
select
    s.studentNo,studentName,studentResult
from student s
left join result r
on
    s.studentNo = r.studentNo
where
    studentResult isnull
----------------------------------------
-- 3表查询
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
-- 我要查询哪些数据 select...
--从那几个表中查 FROM 表 xx Join 连接的表 on 交叉条件
-- 假设存在一种多张表查询，慢慢来，先查询两张表然后再慢慢增加
From a left join b
From a right join b

自连接

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

场景:树形结构分类表 pid 顶级分类二级分类...

-- 查询父子信息  把一张表拆成两张表
select sp.nameas"父" ,ss.nameas"子"
from sort sp,sort ss
where sp.id = ss.pid

分页和排序

-- desc降序 asc升序
-- limit 起始值 页面大小
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
--------------------- 排序语句
orderby studentResult desc
--------------------- 分页语句
limit0,5

子查询

where(动态条件)

select
    studentNo,subjectNo,studentResult
from result r
inner join subject s
on r.subjectNo = s.subjectNo
where subjectName = "数据库"
----------------------------------
select
    studentNo,subjectNo,studentResult
from result
where subjectNo = (
  select
      studentNo
  from subject
  where subjectName = "数据库"
)

分组和过滤

-- 查询不同课程的平均分，最高分，最低分，平均分大于80
-- 核心:(根据不同的课程分组)
SELECT SubjectName， AVG(StudentResult) As 平均分MAX(StudentResult) AS 最高分,MIN(StudentResult) As 最低分
from result r
INNER JOIN `subject` sub
ON r.subjectNo = sub.`subjectNo`
GROUPBy r.subjectNo-- 通过什么字段来分组
HAVING 平均分>80

常用函数

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("hahaha") -- 字符串长度
select concat("我",'爱','你们') -- 字符串拼接
selectinsert("我爱编程123123",1,2 ,"不爱") --查询 替换

select lower('HelloWord') --小写字母
select upper('HelloWord') --大写字母
select instr("hello" ,'l') -- 返回第一次出现的子串的索引

select replace("hello",'e','o') -- 返回hollo 替换
select substr("hello123123",2,5) -- 返回 ello1  从第二个开始截取5个
select reverse('123') -- 返回321 反转

select current_date()  -- 当前时间
select currdate()  -- 当前时间
select now()  -- 当前时间
select localtime()  -- 当前时间
select sysdate()  -- 系统时间

select system_user()  -- root
select user()  -- root
select version()  -- 版本

聚合函数（常用）

count()

sum()

avg()

max()

min()

-- 都能够统计数据库表中有多少记录
-- count(字段)  会忽略null值
selectcount(studentname) from student -- 指定列
-- count(*)  不会忽略null值  计算行数
selectcount(*) from student
-- count(1)  不会忽略null值  计算行数
selectcount(1) from student

select sum(studentNum) as 总和 from student -- 总数
select avg(studentNum) as 平均分 from student -- 平均分
select max(studentNum) as 最高分 from student -- 最高分
select min(studentNum) as 最低分 from student -- 最低分

MD5加密

MD5 不可逆

MD5破解网站原理背后有一个字典

update test set pwd=md5(pwd)
insertinto `test`(name,pwd) values('zhangsan',md5('123456'))

select小结

select完整的语法


select [all | distinct]
{* | table.* | [table.field1[as aliasl][,table.field2[as alias2]][....]]}
FROM table_name [astable alias]
[left | right | inner join table_name2] -- 联合查询
[ON ...]-- 等值判断
[WHERE ...] -- 指定结果需满足的条件
[GROUPBY ...] -- 指定结果按照哪几个字段来分组
[HAVING] -- 过速分组的记录必须满足的次要条件
[ORDERBY ...] -- 指定查询记录按一个或多个条件排序
[LIMIT {[offset,]row_count | row_countOFFSET offset}];
-- 指定查询的记录从哪条至哪条
-- 注意:[]]括号代表可选的，{}括号代表必选的

数据库事务

事务原则:ACID 原子性一致性隔离性持久性 (脏读幻读不可重复读)

-- mysql默认开启事务
set autocommit = 0-- 关闭
set autocommit = 1-- 开启

---测试事务---
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  -- 恢复默认值


-- 了解
savepoint 保存点   -- 设置一个事务保存点
rollback to savepoint 保存点  -- 回滚到保存点
release savepoint 保存点  -- 撤销保存点

MySQL隔离级别--未提交读，提交读，可重复读，序列化

MySQL的事务隔离级别的含义，并用示例说明各个级别会出现的问题：脏读、不可重复读、幻读。

MySQL有四种隔离级别：未提交读，提交读，可重复读，序列化。

事务的隔离级别是Java后端面试题中经常会问到的问题。

索引

帮助mysql高效获取数据的数据结构

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

索引分类

主键索引 (primary key)

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

唯一索引 (unique key)

避免重复的列出现唯一索引可以重复,多个列都可作为唯一索引

常规索引 (key/index)

默认的, index,key关键字来设置

全文索引 (fulltext)

在特定的数据库引擎下才有

快速定位数据

基础用法

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

-- 显示所有的索引信息
SHOW INDEX FROM 表名
-- 增加一个索引 alert table 数据库名.表名 add 索引类型 INDEX `索引名`(`需要在索引的字段`)
alert table test.student add fulltext INDEX `studentName`(`studentName`)
-- explan 分析sql执行情况
explan select * from student

explan select * from student where match(studentName) against('刘')

测试

// 生成100w条数据
delimiter $$ -- 写函数之前必须写!
create function mock_data()
RETURNS INT
begin
    declare num int default 1000000;
    declare i int default 0
    where i<num DO
        -- 插入语句
        INISERT INTO app_user(name,email,phone,pwd,age)
        values(concat("用户",i),
               '123123@qq.com',
               concat("18",floor(rand()*((999999999-100000000)+100000000))),
               UUID(),
               floor(rand()*100) );
        set i = i+1;
    end while;
    return i;
end;

select * from app_user where name = "用户9999"--用时 1s左右
explain select * from app_user where name = "用户9999"

-- id_表名_字段名
-- create index 索引名 on 表名(`字段`)
create index id_app_user_name on app_user(`name`)

select * from app_user where name = "用户9999"--用时 0.01s左右
explain select * from app_user where name = "用户9999"

索引在小数据量的时候感觉不大但在大数据的时候区别十分明显

索引原则

索引不是越多越好

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

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

一般价值常用来查询的字段上

索引的数据结构

Hash 类型的索引

Btree innodb 的默认使用

权限管理和备份

sql命令

-- 创建用户
-- creat user 用户名 identified by '密码' 
creat user zhang identified by'123456'
-- 修改当前用户密码
-- set password = password("新密码")
set password = password("111111")
-- 修改指定用户密码
-- set password for zhang = password('新密码')
set password for zhang = password('123456')
-- 用户重命名
-- rename user 原名字 to 新名字
rename user zhang to ZHLng
-- 授予全部权限 除了给别人授权
GRANT ALL privileges on *.* to ZHLng
-- 查询权限
show grant for ZHLng
show grant for root@127.0.0.1
-- 撤销权限
revoke all privileges on *.* from ZHLng 
-- 删除用户
drop user ZHLng

mysql备份

命令行

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

-- 登录状态下导入
-- source 文件位置
use school -- 切换数据库
source D:/a.sql-- 执行导入
-- 未登录
mysql -u用户名 -p密码 库名< 备份文件

定期备份

数据库设计

糟糕的数据库设计:

数据冗余浪费空间

数据库插入和删除麻烦异常(屏蔽物理外键)

程序性能差

良好的数据库设计

节省内存空间

保证数据完整性

方便我们开发系统

软件开发中关于设计库的设计

分析需求: 分析业务和需要处理的数据库的需求

概要设计: 设计关系图E-R图

数据库设计规范

三大范式

第一范式

原子性:不可再分

第二范式

每张表只描述一件事情

第三范式

确保每张表的每一列数据都和主键直接相关,而不能间接相关

规范性和性能问题

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

考虑商业的需求和目标,数据库的性能更加重要

在规范性能的问题的时候,适当的考虑一下规范性

故意给某些表增加冗余的字段(减少连表查询)

故意增加一些计算列(从大数据量降低为小数据量的查询:索引)

JDBC

数据库驱动

步骤总结:

加载驱动

连接数据库

获得执行sql的对象

获得返回值

释放连接

url

// 协议://localhost:3306/数据库名?参数?参数...
// jdbc:mysql://localhost:3306/数据库名?参数?参数...

// jdbc:oracle:thin:@localhost:1521:sid

SQL注入

select * from username where name="zhangsan"or1=1and password=123456or1=1