Mysql(2020.12.03--2021.01.15)

一.初识mysql

1.命令行连接

    mysql -uroot -p xxxxxxxxxxxx   --连接数据库
    ---------------------------------------------------
    所有语句都以;结尾
    show databases;   --查看所有数据库
    mysql> use school   --切换数据库(use + 数据库名)
    show tables;   --查看数据库的所有表
    describe student;   --显示该表的信息
    create database westos;   --创建新的数据库
    exit; --退出连接

2.语言(数据库 xx 语言)

• DDL 定义
• DML 操作
• DQL 查询
• DCL 控制

二.操作

2.1 顺序

数据库 -->>数据库的表 -->> 数据库的表的数据

2.2 操作数据库(了解)

1.创建数据库

    CREATE DATABASES [IF NOT EXISTS] test;

2.删除数据库

    DROP DATABASES [IF EXISIT] test;

3.使用数据库

    USE school;

4.查看数据库

    SHOW DATABASES;

2.3数据库的列类型

数值

• tinyint 1个字节
• smallint 2个字节
• int 4个字节 int
• bigint 8个字节 long
• float 4个字节 float
• double 8个字节 double
• decimal 字符串浮点型 金融使用

字符串

• char 固定大小 0~255
• varchar 可变字符串 0~65535
• tinytext 微型文本 0~2^8-1
• text 文本串 0~2^16-1

时间日期

• date YYYY-MM-DD 日期格式
• time HH:MM:SS 时间格式
• datatime YYYY-MM-DD HH:MM:SS 最常用的
• timestamp 时间戳 1970.1.1到现在的毫秒数，常用
• year 年份表示

null

• 没有值，未知
• 注意，不要使用NULL进行运算，结果为NULL

2.4数据库的字段属性(重点)

Usigned：

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

zerofill：

• 0填充
• 不足的位数，使用0来填充，int(3),5,---005=

自增：

• 通常理解为自增，自动的在上一条的基础上 +1(默认)
• 通常用来设置成唯一的主键~index,必须是整数类型
• 自己自定义设计的自增的起始值和步长

非空：

• 如果设置为 not null，如果不赋值，就报错
• NULL，如果不填写值，默认就是null.

默认：

• 设置默认的值
• sex，默认是男，如果不指定的值，则会有默认的值

拓展:

    /*每一个表，都必须有一下五个字段*/
    id           主键
    version      乐观锁
    is_delete    伪删除
    gmt_create   创建时间
    gmt_update   修改时间

2.5 创建数据库表

1.格式：

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

2.实例：

 -- 目标：一个school的studentText表
-- 学号，姓名，性别，生日，地址
CREATE TABLE IF NOT EXISTS `studentTset`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(50) NOT NULL DEFAULT 'xx' COMMENT '姓名',
`sex` VARCHAR(2) NOT NULL DEFAULT '男' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '生日',
`address` VARCHAR(100) NOT NULL DEFAULT ' ' COMMENT '地址',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8 

3.常用命令

    SHOW CREATE DATABASE school --查看创建数据库的语句
    SHOW CREATE TABLE student --查看创建表的语句
    DESC student --显示表结构

2.6 数据表的类型

/*
    INNODB 最常用
    MYISAM 早些年使用
*/

	MYISAM	INNODB
事物支持	NO	YES
数据行锁定	NO	YES
外键约束	NO	YES
全文索引	YES	NO
表空间大小	小	大，约2倍

常规使用操作：

• MYISAM 节约空间，速度较快
• INNODB 安全性高，事物的处理，多表多用户操作

2.7修改删除表

修改

-- 修改表名(ALTER TABLE 旧表名 RENAME AS 新表名)
ALTER TABLE student02 RENAME AS student01
​
-- 增加字段(ALTER TABLE 表名 ADD 字段 数据类型)
ALTER TABLE student01 ADD isSingle VARCHAR(2)
​
-- 修改表的字段(重命名，修改约束)
-- 修改数据类型(ALTER TABLE 表名 MODIFY 字段 新数据类型)
ALTER TABLE student01 MODIFY isSingle INT(2)    
--修改字段名和数据类型(ALTER TABLE 表名 CHANGE 旧字段 新字段 新数据类型)
ALTER TABLE student01 change isSingle Single VARCHAR(3)     
​
-- 删除字段(ALTER TABLE 表名 DROP 字段)
ALTER TABLE student01 DROP Single

删除

--如果表存在，就删除(DROP TABLE IF EXISTS 表名)
DROP TABLE IF EXISTS student1

三.DML

3.1 外键(了解)

方式一:在创建表的时候，增加约束(麻烦，复杂)

KEY `约束名` (列名)
CONSTRAINT `约束名` FOREIGN KEY (`列名`) REFERENCE `被引用表名`(`列名`)

方式二:创建表成功后，添加外键约束

alter table `表名` add constraint `约束名` foreign key(`列名`) reference `被引用表名`(列名)

最佳方法

• 数据库只是单纯的表，只用来存数据，只有行(data)和列(name)
• 使用多张表的数据，用程序去实现外键

3.2 DML语言(重点，背)

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

DML语言:数据库管理语言

• insert
• update
• delete

3.3添加

insert 语法：insert into 表名(列1,列2,...) values(值1,值2,...)。

字段必须一一对应

--插入语句
--语法：insert into `表名`(`列1`,`列2`,...) values(`值1`,`值2`,...)
 -- 单个插入
 INSERT `student`(`id`,`name`,`age`) 
 VALUES('1000','小妹','40')
 
 -- 多个插入
 INSERT `student`(`id`,`name`,`age`) 
 VALUES('1100','李华','19'),('1101','划水','20'),('1102','摸鱼','21')

3.4修改

update语法：update 表名 set 修改列名1 = 修改值1[修改列名2 = 修改值2,...] where [条件]

-- update 修改
-- 语法:update `表名` set 修改列名1 = `修改值1`[修改列名2 = `修改值2`,...] where [条件]
-- 单个修改
UPDATE `student` SET `name` = '小曹' WHERE `age`= 20
​
-- 多个修改
UPDATE `student` SET `name` = '老薛',`age`=0 WHERE `age`> 19 AND `age`< 50

3.5删除

delete 语法:delete from 表名 [where 条件]

--删除数据(不要这样写，会全部删除表数据，但不会删除表)
delete from `表名`
--删除指定数据
delete from `student` where id=1

truncate 命令:完全清空一个数据数表，表的结构和索引约束不会变。

-- 清空student表
truncate `student`

delete 和truncate 区别

• 相同: 都能删除数据，且保留表结构
• 不同: truncate 重新设置 自增列 计数器会归零，且不会影响事务

了解:DELETE 删除问题，重启数据库，现象

• innoDB ：自增列会从1开始(存在于内存中，断电即失)
• MyISAM：继续从上一个子增量开始(存在与文件中，不会丢失)

四.DQL(最核心)

4.1select(最核心)

select 语句：最核心的语句

语法: select 字段名 form 表名

-- 查询所有学生
select * from student
--查询指定字段
select `srudentNo`,`studentName` from student
-- 别名，AS 关键字
select `srudentNo` as 学号,`studentName` as 姓名  from student as s
-- 函数Concat(a,b)，连接字符串
select concat('姓名：' ,studentName) as 新名字 from student

4.2去重

去重 : distinct

作用:去除select查询出来的结果中重复的数据。

SELECT DISTINCT `studentno` FROM result -- 查询且去重

数据库的列(表达式)

select version()  -- 查询系统版本
select 100*3-1 as  -- 用来计算
select @@auto_increment_increment  --查询自增的步长(变量)
​
-- 查看数据+1
select `studentNo`,`studentResult`+1 as '+1后' from result

数据库中的表达式: 文本值，列，null，函数，计算表达式，系统变量.......

select 表达式 from 表

4.3where条件子句

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

逻辑运算符

select `studentNo`,`studentResult` from result where `studentResult`>=92 and `studentResult`<=97;
select `studentNo`,`studentResult` from result where `studentResult`>=92 && `studentResult`<=97;
select `studentNo`,`studentResult` from result where `studentResult` between 92 and 97;

-- 查询成绩是91或97的学生
select `studentNo`,`studentResult` from result where `studentResult`=91 or `studentResult`=97;

--查询成绩不是100的学生
select `studentNo`,`studentResult` from result where `studentResult`!= 100 

模糊查询: 比较运算符

--查询姓刘的同学 ==like==
-- % (1个或多个字符)，_(一个字符)
select `studentNo`,`studentName` from student where studentName like '刘%';

--查询1000，1001，1002号学员   =in=具体的值
select `studentNo`,`studentName` from student where studentName in (1000,1001,1002);

4.4联表查询

--inner join
select s.studentNo,studentName,subjectNo from student as s inner join result r on s.studentNo = r.studentNo
--left join
select s.studentNo,studentName,subjectNo from student as s left join result r on s.studentNo = r.studentNo
--right join
select s.studentNo,studentName,subjectNo from student as s right join result r where s.studentNo = r.studentNo

1.where : 等值查询

2.join （表名） on （条件）: 连接查询

--查询参加了考试的学生的（学号，姓名，科目名，分数）:
select s.studentNo,studentName,subjectNo,studentResult
from student s
right join result r
on s.studentNo = r.studentNo
inner join subject sub
where r.subjectNo = sub.subjectNo

4.5自连接

用法: 把一张表拆分为两张表

select a.categoryName as '父栏目',b.categoryName as '字栏目' from category as a,categoryName as b where a.categoryName = b.pid

4.6 分页和排序

排序: order by 列名 顺序

-- 排序:升序ASC,降序DESC
-- order by 通过字段排序，怎么排
-- 查询的结果根据成绩 降序排序
select s.studentNo,studentName,subjectNo,studentResult
from student s
right join result r
on s.studentNo = r.studentNo
order by studentResult ASC

分页: limit 起始值下标,每页个数

select s.studentNo,studentName,subjectNo,studentResult
from student s
right join result r
on s.studentNo = r.studentNo
order by studentResult ASC
limit 0,5

4.7子查询

........

4.8分组

group by : 分组

having ：次要分组

--查询不同课程的平均分，最高分，最低分，平均分> 80
-- 根据不同课程分组
select subjectName,AVG(studentResult) as 平均分,MAX(studentResult) as 最大值,MIN(studentResult) as 最小值
from result r 
INNER JOIN `subject` sub 
ON r.subjectNo = sub.subjectNo 
group by r.subjectNo 
having 平均分 > 80

五.mysql函数

5.1常用函数(不常用)

-- =====数学函数=====
select abs(-8) --绝对值
select ceiling(9.4)  --向上取整
select floor(9.4)  --向下取整
select rand() --返回0~1之间的随机数
select sign(10) --返回一个数的符号，负数为-1，整数为1
-- ====字符串函数====
select char_length('dasjckhaisukudasc')  --字符串长度
select concat('a','b','cc') --字符串拼接 
select insert('abcdefg',1,3,'hijklmn') -- 查询替换
select lower('asdwLiMn') --转换小写
select upper('asdwLiMn') --转换大写
select reverse('abcdefg') --逆置，反转
-- ===时间==
select now()  --当前时间
-- ==系统==
select user() --用户
select version() -- 当前版本

5.2 聚合函数(常用)

六.事务

将一组sql放在一个批次中执行

6.1事务原则：ACID原则

事务原则:ACID原则:原子性，一致性，隔离性，持久性

原子性（Atomicity）

要么都成功，要么都失败

一致性（Consistency）

事务前后数据的完整性必须保持一致。

隔离性（Isolation）

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

持久性（Durability）

持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来即使数据库发生故障也不应该对其有任何影响

6.2隔离导致 的问题

脏读：

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

不可重复读：

在一个事务内读取表中的某一行数据，多次读取结果不同。（这个不一定是错误，只是某些场合不对）

虚读(幻读)

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

6.3一般步骤

--mysql是默认开启事务自动提交的
set autocommit = 0  --关闭
set autocommit = 1 -- 开启
​
-- 事务开启
set autocommit = 0
start transaction
​
insert xx
insert xx
--提交
commit
--回滚
rollback
-- 事务结束
set autocommit = 1 
​
--作为了解
savepoint 保存点名  --设置一个保存点
rollback to savepoint 保存点名  --回滚到保存点
release rollback  --删除保存点

6.4案例分析

-- ======模拟互相转账======
CREATE DATABASE IF NOT EXISTS shop
USE shop
​
CREATE TABLE `account`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
​
INSERT INTO `account`(`name`,`money`)
VALUES('划水',10000.00),('摸鱼',2000.00),('起飞',100.00)
​
DELETE FROM `account` WHERE `name`='划水' OR `name`='摸鱼'
​
-- 事务开始 (划水->200->摸鱼，划水->500->起飞，摸鱼->1000->划水)
SET autocommit = 0
START TRANSACTION
UPDATE `account` SET money=money-200 WHERE `name`='划水';
UPDATE `account` SET money=money+200 WHERE `name`='摸鱼';
​
UPDATE `account` SET money=money-500 WHERE `name`='划水';
UPDATE `account` SET money=money+600 WHERE `name`='起飞';
​
UPDATE `account` SET money=money-1000 WHERE `name`='摸鱼';
UPDATE `account` SET money=money+900 WHERE `name`='划水';
COMMIT -- 提交
ROLLBACK  -- 回滚
​
SET autocommit = 1
​

七.索引

定义:index,帮助mysql高效获取数据的数据结构

索引在小数据量的时候，用处不大，但是大数据时，十分明显。。

7.1分类

• 主键索引(primary key)

  • 唯一的表示，主键不可重复，只能有一个列作为主键

• 唯一索引(unique key)

  • 避免重复的列出现，唯一索引可以重复，多个列都可以标识

• 常规索引(key / index)

  • 默认的，index、key 关键字来设置

• 全文索引(fulltext)

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

• 快速定位数据

7.2创建索引

-- 索引的使用
-- 1.在创建表的时候给字段增加索引
-- 2.创建完毕后，增加索引
​
-- 显示所有索引信息
show index from student
​
-- 增加一个全文索引
alter table school.student add fulltext index studentName(studentName);
​
-- explain  分析sql执行的状况
explain select * from student  -- 非全文索引
explain select * from student where match(studentName) against('刘');

7.3案例分析

-- 查找名字为‘用户543856’的人
SELECT * FROM `app_user` WHERE `name`='用户543856'  --  0.357 sec 
EXPLAIN SELECT * FROM `app_user` WHERE `name`='用户543856'
-- 创建索引  id_表名_字段名
-- create index 索引名 on 表(字段)
CREATE INDEX id_app_user_age ON `app_user`(age);
​
-- 创建索引后
SELECT * FROM `app_user` WHERE age=20  -- 0.002 sec
EXPLAIN SELECT * FROM `app_user` WHERE age=20

八.用户管理和备份

8.1.用户管理

1.sqlyog 可视化管理

2.命令行

-- 创建用户  CREATE USER 用户名 IDENTIFIED BY '密码'
create user heng identidied by '123456'
-- 修改密码(当前用户)
set password = password('111111')
-- 修改密码(指定用户)
set password for heng = password('111111')
-- 重命名  rename user 旧名 to 新名
rename user heng to heng2
-- 用户权限设置
-- all privilegse 所有权限(比root用户少一个给别人授权的权限)
grant all privilegse on *.* to heng2
-- 查询用户权限
show gants for heng2

8.2备份

• 直接拷贝物理文件

• 在sqlyog这种可视化工具中备份

• cmd中备份(mysqldump)

  -- 备份 
  -- mysqldump -h主机 -u用户名 -p密码 数据库 表名[表2，表3...] > 物理磁盘位置/文件名[文件名2，...]
  ​
  -- 导入备份文件
  -- 登陆的情况下: source 备份文件
  

九.规范数据库设计

9.1为什么需要设计

当数据库比较复杂时，就需要设置了

糟糕的数据库设计：

• 数据冗余，浪费空间
• 数据库的插入和删除都会麻烦，异常[屏蔽物理外键]
• 程序性能差

良好的数据库设计：

• 节省内存空间
• 保证数据库的完整性
• 方便开发

软件开发中，关于数据库的设计

• 分析需求: 分析业务和需要处理的数据库的需求哦
• 还要设计：设计E-R图

设计数据库步骤(实例：个人博客):

• 收集信息，分析需求

  • 用户表(用户登陆注销，个人信息，写博客，创建分类)
  • 分类表(文章分类，谁创建的)
  • 文章表(文章信息)
  • 评论表
  • 友链表
  • 自定义表(系统信息，每个关键字)

• 标识实体(把需求落地到每个字段)

• 标识实体之间的关系

  • 写博客: user --> blog
  • 创建分类: user --> category
  • 关注: user --> user
  • 友链: links
  • 评论: user-->user-->blog

9.2三大范式

第一范式(1NF):原子性。保证每一列不可再分

第二范式(2NF):在1NF下，每张表只做一件事。确保表中的每一列都和主键相关，不能只与主键的一部分(主要针对联合主键)

第三范式(3NF)：在2NF下，确保表中的每一列都和主键直接相关，而不能间接相关。

9.3规范性和性能问题

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

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

十.JDBC(重点！！)

10.1步骤总结：

• 加载驱动

  Class.forName("com.mysql.cj.jdbc.Driver");
  

• 连接数据库 connection对象

  Connection connection = DriverManager.getConnection(url, username, password);
  

• 获得执行sql的对象 statement对象

  Statement statement = connection.createStatement();
  

• 获得返回的结果集 resultSet对象

  String sql = "select * from users";
  ResultSet resultSet = statement.executeQuery(sql);  //返回的结果集
  

• 释放连接 xx.close()

  resultSet.close();
  statement.close();
  connection.close();
  

10.2.对象解释

DriverManager

//固定写法，加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");

URL

//mysql默认端口号:3306
//格式:  协议://主机地址：端口号/数据库名?参数1&参数2&参数3
​
String url = "jdbc:mysql://localhost:3306/jdbcstudy?useUnicode=true&characterEncoding=utf8&useSSL=true";
​

statement ->执行sql的对象，prepareStatement -> 执行sql的对象(更安全)

 String sql = "select * from users";
 
 statement.execute(sql)  //执行任何的sql，但效率较低
 statement.executeUpdate(sql)  //更新、插入、删除，返回一个受影响的行数
 statement.executeQuery(sql)  //查询操作

resultSet 查询的结果集

获得指定的数据类型

resultSet.getObject()  //在不知道类型的情况下使用
//如果知道类型：
resultSet.getString()
resultSet.getInt()
resultSet.getDoulbe()
....

释放资源 xx.close()

resultSet.close()
statement.close()
connection.close()  //非常占内存，用完关闭

10.3.statement对象(不安全)

增，删，改

Statement st = conn.createStatement();
String sql = "....";
int num = st.excuteUpdate(sql);
if(num > 0){
    System.out.println("操作成功！");
}

查

Statement st = conn.createStatement();
String sql = "....";
ResultSet rs = st.excuteQuery(sql);
while(rs.next()){
    遍历
}

10.4.prepareSatement(更安全)

10.5.SQL注入问题

SQL注入即是指web应用程序对用户输入数据的合法性没有判断或过滤不严，攻击者可以在web应用程序中事先定义好的查询语句的结尾上添加额外的SQL语句，在管理员不知情的情况下实现非法操作，以此来实现欺骗数据库服务器执行非授权的任意查询，从而进一步得到相应的数据信息。

10.5.IDEA连接数据库

1.IDEA连接mysql

2.IDEA操作事务

代码实现

1.开启事务 conn.setAutoCommit(false); //关闭自动提交，开启事务

2.一组事务执行完毕，提交事务

3.在catch语句中显示的定义，回滚

10.6.数据库连接池

数据库链接-----执行完毕-----释放

连接---释放，很浪费资源

池化技术: 准备一些预先的资源，用时直接用

最小连接数: 10

最大连接数: 15

等待超时 ： 100ms

C3P0,DBCP