2021-05-12

Mysql从入门到入坑

---

---

前言

JavaEE：企业级Java开发.

总共分为三个部分：
前端：负责页面和数据的展示。
后端：对内连接数据库，对外连接前端(控制页面的跳转，给前端传数据)。
数据库：存取数据。

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、为什么要学习数据库？

1.岗位需求
2.数据需要存取
3.数据库是所有软件体系最核心的部分

二、什么是数据库？

存储数据和管理数据的数据仓库。

三、数据库的分类

1.关系型数据库：mysql,oracle

特点：通过表和表之间，行和行之间进行数据的存储。
2.非关系型数据库：redis、MongDB

特点：对象存储、通过对象自身的属性来决定。

四、DBMS(数据库管理系统)

数据库的管理软件，能够科学有效的管理我们的数据，最重要的是可视化。Mysql就是一个数据库管理系统。

五、命令行操作Mysql

-u代表输入账号，需要有空格，-p代表输入密码，不能够有空格。

**create database school;**创建数据库
**drop database school;**删除数据库
**show databases;**显示所有的数据库
**use school;**使用数据库
**create table student;**创建表
**show tables;**显示所有的表
**describe student;**显示表的详情
**exit;**关闭数据库
**desc student;**查看表的结构
**show create table student;**查看表的创建语句

六、利用navicat练习

-u代表输入账号，需要有空格，-p代表输入密码，不能够有空格。

七、数据库的数据类型

tinyint 十分小的数据；占一个字节
smallint较小的数据 ；占两个字节
mediumint中等大小的字节；占三个字节
int 标准的 ；占四个字节
bigint较大的数据；占八个字节
float浮点数；占四个字节
double双精度浮点数；占八个字节
decimal字符串形式的浮点数；金融计算的时候，一般使用decimal
char字符串固定大小
varchar字符串可变大小
tinytext 微型文本
text文本串
data yyy-mmm-ddd
time hh-mm-ss
datatime yyy-mmm-ddd hh-mm-ss
timestamp时间戳
year年份表示
null表示空值，未知。一般不使用null进行计算

八、数据库的字段属性

Unsigned:无符号整数，声明了该列不能为负数
zerofill:0填充，不足的位数，使用0进行填充
自增：通常理解为自增，自动在上一条记录上的基础上+1，大于5以上。通常用来设计唯一的主键，必须是整数类型，可以字节设置自增的区间和起始值。
非空：设置为not null 会进行报错
默认：如果不指定该列的值，为默认值，默认值的设置必须加单引号

九、数据库必须存在的字段

id:主键
version乐观锁
is_delete伪删除
create_time创建时间
update_time修改时间

十、数据库创建表的公式

create table if not exists '表名'(

‘字段名’ 列类型（属性）索引 注释,
‘字段名’ 列类型（属性）索引 注释,
‘字段名’ 列类型（属性）索引 注释
)

十一、数据库的引擎

innodb：默认使用 。支持事物（两个sql语句同时执行，要么同时成功，要么同时成功） 、行锁定（查询的时候只锁定一行）、外键。不支持全局索引。表空间较大，十myisam的两倍。安全性高，事物处理，多表多用户操作。
myisam:早些年使用。不支持事物 、行锁定、外键。支持全局索引，表空间较小，表锁定（查询的时候锁定整张表）。节约空间，速度较快。

十二、修改和删除表

修改表的名字
alter table teacher rename teacher1
增加表的字段
alter table teacher add name varchar(10)
修改表的字段类型
alter table teacher modify name int(10)
修改表的字段名
alter table teacher change name name1
删除表的字段
alter table teacher drop name
删除表
drop table if exists school

十三、Mysql数据管理

1.外键（了解即可）

• alter table student add constraint ‘FK_course’ foreign key(course) references teacher(course)

2.DML语言（数据库管理语言）

insert

• insert into teacher(id,name,course) values(10,‘小明’,‘高数’)

update

• update teacher set name = ‘小明’ where id = 8

> 			大于
<			小于
>=			大于等于
<=			小于等于
between....and....闭区间
and 		同时成立
or			或者
!=   <> 	不等于

delete

delete from teacher where id = 1
delete from teacher			会删除整整张表，不会把自增记录给清楚
truncate							也会删除整张表，会把自增给清零，不会影响事物

3.DQL语言（数据库查询语言，超级重要）

查询全部的字段
select  *  from student
AS 可以给字段和表格起别名
concat可以改变表的结构
select distinct studentno from student 		distinct可以去除重复的 数据

select version()		查询数据库的版本
select 100*3-1			用来加减乘除计算
select studentno+1 from result		可以对字段进行+1操作

模糊查询(%和_的区别，%可以占位很多个字，_只能占位一个字)
is null 如果操作符为空，结果为真
is not null 如果操作符不为空，结果为真
between a and b 如果结果在[a,b],结果为真
like a like b 如果a匹配b,则结果为真
in   a in(a1,a2,a3) 如果a属于a1,a2,a3中的一个，则结果为真

查询所有姓张的同学
select * from student where studentname like '张%'
查询姓张，且只有两个字的同学
select * from student where studentname like '张_'
查询姓张，且只有三个字的同学
select * from student where studentname like '张__'
查询名字中，含有大字的同学
select * from student where studentname like '%大%'
查询1001，1002，1003的学生信息
select * from student where studentno in(1001,1002,1003)
查询地址为空的学生
select * from student where address is null

连接查询
inner join on 两张表中至少有一个匹配，就返回行
left join on  即使右表中没有匹配，也会返回左表中的所有值
right join on 即使左表中没有匹配，也会返回右表中所有的值
三表查询
select s.studentno,studentname,r.subjectno,subjectname from student as s inner join result as r on s.studentno = r.studentno inner join subject as sub on sub.subjectno = r.subjectno

自连接（自己连接自己，把一张表分成两张表）
select a.name,b.name from student as a,student as b where a.id = b.id

**order by** ASC升序，DESC降序

为什么使用分页。
缓解数据库压力，给人更好的的体验。
select * from student limit 0,5
公式：(n-1)*每页的显示个数

子查询
在where语句当中再嵌套一个查询语句
执行顺序是从里到外

3.mysql常用函数

select ABS(-8) 取绝对值
select celing(9.3)向上取整 10
select floor(9.4) 向下取整
select rand() 生成0-1随机数
select sign(10) 判断一个数的符号，负数返回-1，正数返回1
select char_length(aadsdf) 返回字符串的长度
select concat('你','好')拼接字符串
select insert('',1,2,'')替换
select lower()转变为小写字母
select upper()转变为大写字母
重点
select current_date查询当前的时间

4.mysql聚合函数

count()		计数
count(字段) 会忽略所有的null
count(*)    不会忽略null值，计算所有的行数，把所有的行数都走一遍
count(1)	不会忽略null值，计算所有的行数，只走一遍
sum()		求和
AVG()		求平均值
MAX()		求最大值
MIN()		求最小值
group by()对查询到的结果进行分组
having 分组之后再满足的次要条件

十四、数据库密码加密

insert into test values(1,'小明',MD5(pwd))

十五、事物(要么都成功，要么都失败)

原子性：针对同一个事物，要么都完成，要么都不完成。a给b转账，要么都成功，要么都失败。
一致性：针对一个事物，最终的状态是一致的。a给b转账，他们两个的总钱数是一定的。
隔离性：多个用户同时操作的时候，他们之间的事物不会受到影响。 
持久性：事物结束后的数据不随着外界原因导致数据丢失。如果事物没有提交，服务器突然断电，两个人的钱数不变。如果事物提交，服务器断电，两个人的钱数是提交之后的数量。事物一旦提交就不可以逆转。
隔离所导致的问题:
脏读：一个事物读取了另一个事物没有提交的数据。
不可重复读：多次读取同一个事物，结果不一致。
幻读：在一个事物内读取了别人插入的数据，导致前后读取不一致。

事物公式

--mysql默认是事物是开启的
--关闭事物
set autocommit = 0 
--开启事物
set autocommit = 1
--标记一个事物的开启
START TRANSACTION

insert xxx
insert xxx
--提交：持久化，成功
COMMIT
--回滚：回到原来的样子，失败
ROLLBACK
--事物结束
set autocommit = 1

--设置一个保存点
SAVEPOINT 保存点名
ROLLBACK to SAVEPOINT 保存点名
--移除保存点
release SAVEPOINT

十六、索引

索引可以让mysql高效的获取数据

主键索引（primary key）唯一标识，不可以重复，只能有一个
唯一索引（unique key）避免重复的列，唯一索引可以有多个
常规索引（key/index）无需设置，默认是常规索引
全文索引（fulltext）在特定引擎下才有，myisam,能够快速定位数据
索引在小的数据量当中用处不大，只有在大的数据量当中，才能大大缩短时间。
创建索引的两种方法：
create index id_app_user_name on app_user(name);
alter table app_user add index studentname(studentname);
索引原则：
1.索引不是越多越好。
2.不要对经常变动的数据增加索引。
3.小数据量不需要增加索引。
4.索引一般加在查询的字段上。

十七、用户管理和备份

用户管理：点击用户就可以可视化的管理用户权限。

创建用户kuang，密码123456
create user kuang identified by '123456'
修改当前用户密码
set password = password('123456')
修改指定用户密码
set password for kuang = password('123456')
重命名原来用户的名字
rename user kuang to kuang2

用户的备份：
复制mysql文件夹的data文件
使用navicat右键导出数据和结构
使用命令行导出：
Mysqldump -主机 - 用户名 -密码 数据库 表名 物理磁盘位置
使用命令行导入：
先登录
source 具体的文件

十八、规范数据库设计

为什么需要设计数据库?当数据库比较复杂的时候，我们就需要设计了。
糟糕的数据库：
	1.数据冗余，浪费空间。
	2.数据库的插入和删除都比较麻烦（加入了外键，不能够使用）。
	3.程序的性能差。
良好的数据库：
	1.节省内存空间。
	2.保证数据库的完整性。
数据库设计步骤：
	1.分析需求：分析业务和需要处理的数据库需求。
	2.概要设计：设计关系图，e-r图

十九、数据库三大范式

为什么要规范化？
	1.信息重复
	2.更新异常
	3.插入异常
	4.删除异常

第一范式：
	原子性：每一列都是不可以再分的。
第二范式：
	在满足第一范式的情况下，每张表只满足一件事情。
第三范式：
	在满足第一范式和第二范式的情况下，每一列数据都和主键直接相关。
**规范性和性能不可兼得，在需要性能的时候，适当的考虑一下规范性。**

二十、JDBC(重点)

作用：用java操作数据库。

使用jdbc连接数据库
import java.sql.*;
public class test {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        //jdbc连接数据库
        //1.加载驱动
        Class.forName("com.mysql.jdbc.Driver");
        //2.获取链接，用户名，密码 useUnicode支持中文编码，characterEncoding使用utf-8的编码，SSL安全连接，serverTime时区
        String url = "jdbc:mysql://localhost:3306/demo?userUnicode=true&characterEncoding=utf8&userSSL=true&serverTimezone=GMT%2B8";
        String username = "root";
        String password = "123456";
        //连接成功，数据库对象
        Connection connection = DriverManager.getConnection(url, username, password);
        //执行sql的对象statement
        Statement statement = connection.createStatement();
        //创建sql语句
        String sql = "select * from blog";
        ResultSet resultSet = statement.executeQuery(sql);
        while(resultSet.next()){
            System.out.println(resultSet.getObject("id"));
            System.out.println(resultSet.getObject("title"));
            System.out.println(resultSet.getObject("author"));
            System.out.println(resultSet.getObject("create_time"));
            System.out.println(resultSet.getObject("views"));
        }
        connection.close();
        statement.close();
        resultSet.close();
    }
}

二十一、sql注入

sql存在漏洞。
preparedStatement可以进行预编译，放置sql注入

二十二、数据连接池

普通的连接：
	数据库连接--->执行完毕--->释放，十分的浪费资源
池化技术：准备一些预先的资源，过来就连接，预先准备好
	就像银行取钱：普通的连接，一次只能服务一个人。池化技术，可以有多个业务员，一次可以服务多个人。
池化技术可以设置：
	常用的连接数：10个
	最小连接数：10个
	最大连接数：100个
	等待超时：100ms
编写连接池，只需要实现一个接口DataSource
	DBCP
	C3p0
	Druid