MySQL学习记录

最新推荐文章于 2024-08-30 14:18:30 发布

看到论文就头大

最新推荐文章于 2024-08-30 14:18:30 发布

阅读量132

点赞数

文章标签： mysql 数据库学习

本文链接：https://blog.csdn.net/m0_52784892/article/details/128017410

版权

1、初识MySQL

1.1、什么是数据库

存储数据管理数据

1.2、数据库分类

关系型数据库：（行、列） SQL

mysql
Oracle
SQL Server
SQL Lite
通过表与表之间的关系，行和列之间的关系进行数据的存储
学生信息表、考勤表等等之间的表都有你的信息都是有关系的

非关系型数据库：{key：value} NoSQL NotOnly
定位的数据等等

Redis
MongoDB
对象存储通过对象自身的属性来决定

DBMS（数据库管理系统）
用来管理数据

1.3、连接数据库以及一些常用的命令

命令行的情况下：
  --所有的语句都使用；结尾
  show databases; --展示拥有的数据库
  use '数据库名'; --切换到某个数据库
  show tables;    --查看数据库中所有的表
  describe '表名' --查看某个表
  create database '名字';  --创建一个数据库

2、操作数据库

2.1、操作数据库

    create database [if not exists] '名字' --创建一个数据库如果不存在的话
    drop database [if exists] name --删除一个数据库

2.1、数据库的常用类型

比较生疏且常用的
— timestamp 时间戳（1970.1.1 到现在的毫秒数）

2.2、数据库的字段属性（重点）

Unsigned：

无符号的整数
声明了该列不能是负数
zerofill
0填充
不足的数位用0来填充
假设规定填写10位数，你只填写了一位数，那么最终的结果是0000000001

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

2.3、创建数据库表

2.4、数据库引擎

innodb 默认使用
myisam 早起使用
区别：

MYISAM INNODB
事务支持不支持不支持
数据行不支持（支持锁表）锁定行
外键约束不支持支持
全文索引支持不支持
表的空间大小较小较大，约为MYISAM的两倍
MYISAM：节约空间，速度快

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

INNODB: 安全性高，支持事务的处理，多表多用户操作

设置数据库的字符集编码

CHARSET=utf8

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

可以提前在my.ini中配置默认的编码

2.6、修改删除表

修改表

-- 修改表的字段（重命名、修改约束）
alter table teacher modify age varchar(11)   -- 修改约束，将int修改为varchar
alter table teacher change age age1 int(1)  -- 修改约束并重命名，将varchar修改为int
-- change和modify的区别是：能不能重命名

删除表

3、MySQL数据管理

3.1、外键

**学生表：**id、name、age、gradeId（年级）

**年级表：**id、gradeName

通过gradeId这个字段表明两个表之间的关系

删除有外键关系的表的时候，必须要先删除从表再删除主表

3.2、DML语言

3.3、添加

3.4、修改

-- 修改某个区间中的值
update `studnet` set `name`='zhangsan' where id between 2 and 5
-- 通过多个条件定位数据
update `studnet` set `name`='zhangsan' where id=2 and name="lisi"and ......

3.5、删除

delete from `test`  -- 不会影响自增
truncate table `test`  -- 自增会归零

4、DQL查询数据

4.1、DQL

数据查询语言

-- 起别名(字段和表名都可以)
select `student_no` as 学号,`student_name` as 姓名 from student
-- 函数
select concat('姓名：',student_name) as 新名字 from student  -- 那么字段里面的查询的值就变成了：姓名：xxx
-- 发现重复数据，去重
select distinct `student_no` from result
-- 所有的数值类型的数据+1
select `score`+1 as `提分后` from result

4.2、where条件子句

like %（代表0到任意个字符） _（一个字符）

4.3、联表查询

CSDN主页–连表查询

4.4、分页查询

select * from student limit 0,5  --limit [起始值],[页面大小]

4.5、子查询

在where语句中嵌套子查询

select studnetNo,subjectNo,javaScore 
from score
where subjectNo = (
			select subjectNo from subject
  		where subjectName = 'Java'
)
order by javaScore desc

5、MySQL函数

5.1、常用函数

-- 数学运算
select abs(-8)  -- 绝对值
select ceiling(9.4)  -- 向上取整
select floor(9.4)  -- 向下取整
select rand() -- 返回0-1之间的随机数
select sign(-10)  -- 返回一个数的符号
select char_length() -- 返回字符串的长度
select concat('字符串1','字符串2') -- 将字符串拼接
select insert('我爱编程helloword',1,2,'超级热爱')  -- 查询，替换  从字符的第1个位置往后数2个字符进行替换（某个位置开始替换某个长度）
select upper('')  -- 转大写
select lower('')  -- 转小写
instr --  返回第一次出现的子串的索引
replace  --  替换出现的指定字符串 
substr  --  返回指定的子字符串（4,3）从第四个字符串开始截，截三个
SELECT CURRENT_TIME();  -- 获取当前时间
SELECT CURRENT_DATE();  -- 获取当前日期
SELECT LOCALTIME();  -- 获取本地时间

5.2、聚合函数（常用）

Count() （‘字段’）会忽略所有的null值（*）（1）不会忽略
Sum()
Avg()
Max()
Min()

5.3、数据库级别的MD5加密（拓展）

利用MD5函数

insert into testmd5 values(4,'xiaoming',MD5('123456'))

6、事务

6.1、什么是事务

要么都成功，要么都失败

转账实例：A有800元，B有200元

A要给B转账200（A的钱，从800变为600），B接收A的200（B的钱，从200变为400）

执行两个SQL语句

将一组SQL放到一个批次中去执行

ACID

原子性

要么都成功，要么都失败

一致性

事务前后的数据完整性要保证一致，保证总钱数1000

持久性 – 事务提交

事务一旦提交则不可逆，被持久化到数据库中

隔离性

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

隔离性主要是解决：两个事务同时进行，其中一个事务读取到另一个事务还没提交的数据（正常情况下，没有事务没有提交的数据是不能被读取的）。

事务的隔离级别：

脏读

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

不可重复读

在一个事务内读取表中的某一行数据，多次读取结果不同：页面统计查询值B为200，生成报表的时候B有人转账（200元）进来了，

那么此时读取的结果变成了500元

幻读

是指在一个事务内读取到了别的事务插入到的数据，导致前后读取的数据总量不一致

6.2、事务的提交流程

-- mysql默认开启事务自动提交的
set autocommit = 0 -- 关闭
set autocommit = 1 -- 开启
---------------------------
-- 首先关闭mysql的事务自动提交
set autocommit = 0;
-- 事务开启
start transaction

sql语句1
sql语句2

-- 提交事务：持久化（成功！）
commit
-- 回滚：回到之前的样子（失败）
rollback
-- 事务结束
set autocommit = 1	-- 开启自动提交

7、索引

索引的定义：索引是帮助MySQL高效获取数据的数据结构

7.1、索引的分类

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

主键索引（primary key）
- 唯一标识，主键不可重复，只有一个列作为主键
唯一索引（unique key）
- 避免重读的列出现，唯一索引可以重复，就是说多个列都可以被表示为唯一索引
常规索引（key/index）
- 默认的，用index或者key来设置
全文索引（fulltext）
- 在特定的数据库引擎下才有

-- 显示所有索引信息
-- 增加一个全文索引
explain select * from student; -- 非全文索引（会遍历数据）
explain select * from student where match(studentName) against('刘') -- 直达查询的数据

7.2、索引原则

索引不是越多越好
不要对经常变动的数据加索引
小数据量的表不需要加索引
索引一般加在-常用来查询的字段上面

索引的数据结构

hash类型的索引（不是INNODB默认的，INNODB默认的是BTREE）

8、规范数据库设计

8.1、数据库的设计

8.2、三大范式

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

常用的有三种，第一第二第三范式。

第一范式：

数据表中的每一个字段都是不可分割的原子数据项

举例子：假设某个字段的值为:家庭信息→其中的value是：‘三口人，北京’。

很明显，这是一个还可以在细分的一个字段，能细分成家庭人口和居住地

第二范式：

在满足第一范式的前提下，需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。

举例子：

如图所示：同一个订单可能包括不同的产品，所以订单号不能称为主键，主键必须由“订单号”和“产品号联合组成”，但是由于订单金额和订单时间只与主键的一部分有关，即只跟订单号有关和产品号无关，所以不满足第二范式。所以需要将之拆分。

第三范式：

在第二范式的基础上，确保数据表中的每一列数据都和主键直接相关，而不能间接相关。

上表中满足第二范式，但是班主任性别和年龄的直接依赖是班主任姓名，而不是主键学号。所以需要做出调整。

这样就满足第三范式了

9、JDBC

9.1、SQL注入

什么是SQL注入？（恶意拼接查询）

在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

举个例子：

我们使用JDBC去连接数据库并查询（SQL语句）

那么Java总的SQL语句为：

String sql = "select * from users where `NAME`='"+username+"'and `password`='"+password+"'";
// 假如说这里的username传入的并不是单纯’admin‘而是’or 1=1‘ 这样的话直接就修改了这个sql语句的功能了

防止SQL注入：

过滤输入内容，校验字符串

通过编程语言将用户输入的不合法字符进行过滤和剔除

参数化查询

/**
*参数化查询目前被视作是预防 SQL 注入攻击最有效的方法。参数化查询是指在设计与数据库连接并访问数据时，在需要填入数值或数据的地方，使用参数（Parameter）来给值。
*/
UPDATE myTable SET c1 = ?c1, c2 = ?c2, c3 = ?c3 WHERE c4 = ?c4

9.2、Statement和PreparedStatement

Statement有SQL注入的风险

preparedStatement没有

-- preparedStatement使用的是占位符、预编译、固定写法
Connection conn = null;
PreparedStatement st = null;
String sql = "insert into users(id,'username','password') values (?,?,?)";
st = conn.prepareStatement(sql) //预编译sql
// 手动给参数赋值
st.setInt(1,4);
st.setString(2,'zhangsan');
st.setString(3,'123123');
st.executeUpdate();// 返回值为受影响的行数