Java学习笔记 (十六) mysql

mySQL

概述

数据库: DataBase(DB), 是存储和管理数据的仓库

数据库管理系统: DataBase Management System(DBMS), 操纵和管理数据库的大型软件

SQL: Structured Query Language, 操作关系型数据库的编程语言, 定义了一套操作关系型数据库统一标准

配置与安装

安装地址: https://dev.mysql.com/downloads/mysql/

参考黑马程序员的这个视频

配置全局变量

初始化: mysqld --initialize-insecure

安装: mysqld -install

卸载: mysqld -remove mysql

启动mysql服务: net start mysql

关闭mysql服务: net stop mysql

更改超级管理员密码: mysqladmin -u root password 1234

登录账户: mysql (-h用户名 -P端口号) -u用户名 -p密码

数据模型

关系型数据库(RDBMS): 建立在关系模型基础上, 由多张相互连接的二维表组成的数据库

特点:

1. 使用表存储数据, 格式统一, 便于维护
2. 使用SQL语言操作, 标准统一, 使用方便, 可用于负责查询

通用语法:

• SQL语句可以单行或多行书写, 以分号结尾
• SQL语句可以使用空格/缩进来增强语句的可读性
• MySQL数据库的SQL语句不区分大小写
• 注释:
  1. 单行注释: -- 注释内容 或 # 注释内容(MySQL特有)
  2. 多行注释: /* 注释内容 */

SQL分类

分类	全称	说明
DDL	Data Definition Language	数据定义语言, 用来定义数据库对象(数据库, 表, 字段)
DML	Data Manipulation Language	数据操作语言, 用来对数据库表中的数据进行增删改
DQL	Data Quary Language	数据查询语言, 用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言, 用来创建数据库用户, 控制数据库的访问权限

DDL

数据库操作

查询:

• 查询所有数据库: show databases;
• 查询当前数据库: select database();

使用:

• 使用数据库: use 数据库名;

创建:

• 创建数据库: create database[if not exists] 数据库名;

删除:

• 删除数据库: drop database[if exists] 数据库名;

注意: 上述语法中的database, 也可以替换成schema, 如: create schema 数据库名;

表结构操作

创建

create table 表名(
	字段1 字段类型 [约束] [comment 字段1注释],
    ...
)

use db01;
create table tb_user(
    id int comment 'ID, 唯一标识',
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表';

约束

概念: 约束是作用于表中字段上的规则, 用于限制存储在表中的数据

目的: 保证数据库中数据的正确性, 有效性和完整性

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一, 不重复的	unique
主键约束	主键是一行数据的唯一标识, 要求非空且唯一	primary key
默认约束	保存数据时, 如果为指定该字段, 则采用默认值	default
外键约束	让两张表的数据建立连接, 保证数据的一致性和完整性	foreign key

create table tb_user
(
    id       int primary key auto_increment comment 'ID, 唯一标识',
    username varchar(20) not null unique comment '用户名',
    name     varchar(10) not null comment '姓名',
    age      int not null comment '年龄',
    gender   char(1) default '男' comment '性别'
) comment '用户表';

数据类型

案例

create table tb_emp(
    id int primary key auto_increment comment 'ID是唯一标识',
    username varchar(20) not null comment '员工姓名',
    name varchar(10) not null comment '员工姓名',
    gender tinyint unsigned not null comment '性别: 1为男, 2为女',
    job tinyint unsigned comment '职位: 1为x, 2为y, 3为z',
    entrydate date comment '入职时间',
    password varchar(32) default '123456' comment '员工密码',
    create_time datetime comment '创建时间',
    update_time datetime comment '创建时间'
)

修改

• 添加字段: alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

• 修改字段类型: alter table 表名 modify 字段名 新数据类型(长度);

• 修改字段名和字段类型: alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];

• 删除字段名和字段类型: alter table 表名 drop column 字段名;

• 修改表名: rename table 表名 to 新表名;

• 删除表: drop table [if exists] 表名;

DML

Data Manipulation Language(数据操作语言), 用来对数据库中表的数据记录进行增删改操作

增INSERT

• 指定字段添加数据: insert into 表名(字段名1, 字段名2) values(值1, 值2);
• 全部字段添加数据: inser into 表名 values(值1, 值2, ...);
• 批量添加数据(指定字段): insert into 表名(字段名1, 字段名2) values (值1, 值2), (值1, 值2);
• 批量添加数据(全部字段): insert into 表名 values(值1, 值2, ...);

-- 为username name gender字段插入值
insert into tb_emp(username, name, gender,create_time, update_time) values ('wuji','张无忌', 1,now(), now());

-- 为所有字段插入值
insert into tb_emp(id, username, name, gender, job, entrydate, password, create_time, update_time)
    values (2, 'jinmao', '金毛狮王', 1, 2, '2022-02-02','123',now(), now());
insert into tb_emp(id, username, name, gender, job, entrydate, password, create_time, update_time)
values (null, 'jinmao2', '金毛狮王2', 1, 2, '2022-02-02','123',now(), now());

-- 批量插入值
insert into tb_emp
    values (null, 'jinmao3', '金毛狮王3', 1, 2, '2023-02-02','123',now(), now()),(null, 'jinmao3', '金毛狮王3', 1, 2, '2018-02-02','123',now(), now());

注意:

1. 插入数据时, 指定的字段顺序需要与值的顺序是一一对应的
2. 字符串和日期型数据应该包含在引号中
3. 插入的数据大小, 应该在字段的规定范围内

改UPDATE

• 修改数据: update 表名 set 字段名1 = 值1, 字段名2=值2, ... [where 条件];

-- 修改id为1的人的name
update tb_emp set name = '张三', update_time = now() where id = 1;

-- 修改全部值
update tb_emp set entrydate = '2012-12-21' , update_time = now();

• UPDATE语句的条件可以有, 也可以没有, 如果没有条件, 则会修改整张表的所有数据

删DELETE

• 删除数据: delete from 表名 [where 条件];

-- 删除id为5的数据
delete from tb_emp where id = 5;

-- 删除表内所有数据
delete from tb_emp;

注意:

1. DELETE语句的条件可以有, 也可以没有, 如果没有条件, 则会删除整张表的所有数据
2. DELETE语句不能删除某一个字段的值,(如果要操作, 可以使用UPDATE, 将该字段的值置为NULL)

DQL

Data Quary Language

select
	字段列表
from
	表明列表
where
	条件列表
group by
	分组字段列表
having
	分组后条件列表
order by
	排序字段列表
limit
	分页参数

基本查询

• 查询多个字段: select 字段1, 字段2, ... from 表名;
• 查询所有字段: select * from 表名;
• 设置别名: select 字段1 [as 别名1], 字段2 [as 别名2], ... from 表名;
• 去除重复记录: select distinct 字段列表 from 表名;

-- 查询指定字段 name, entrydate 并返回
select name, entrydate from tb_emp;

-- 查询所有字段
-- 推荐
select
       id, username, name, gender, job, entrydate, password, create_time, update_time
from
     tb_emp;

-- 不推荐
select * from tb_emp;

-- 查询所有员工的name, entrydata, 并起别名
select
    name as '姓名', entrydate as '入职日期' -- as可以省略
from
    tb_emp;

-- 查询已有的员工关联了哪几种职位(不要重复)
select distinct job from tb_emp;

条件查询

• 条件查询: select 字段列表 from 表名 where 条件列表;

-- 查询姓名为李四的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where
    name = '李四';

-- 查询id大于10 的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where
     id > 10 ;

-- 查询job为null的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where job is null;

-- 查询有职位的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where job is not null;

-- 查询密码不等于123456的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where password != '123456';
-- where password <> '123456; 也表示不等于

-- 查询入职日期在 xxxx-xx-xx(包含)到xxxx-xx-xx(包含)之间的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where entrydate >= '2022-1-1' and entrydate <= '2022-12-21';
-- where entrydate between '2022-1-1' and '2022-12-21';

-- 查询入职日期在 xxxx-xx-xx(包含)到xxxx-xx-xx(包含)之间且性别为女的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where entrydate >= '2022-1-1' and entrydate <= '2022-12-21' and gender = 2;

-- 查询职位为2, 3或4的员工数据
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
-- where job = 2 or job = 3 or job = 4;
where job in (2,3,4);

-- 查询姓名为两个字的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where name like '__';

-- 查询姓张的员工的员工信息
select
    id, username, name, gender, job, entrydate, password, create_time, update_time
from
    tb_emp
where name like '张%';

分组查询

聚合函数

介绍: 将一系列数据作为一个整体, 进行纵向计算

语法: select 聚合函数(字段列表) from 表名;

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sun	求和

-- 1. 统计该企业员工数量
-- A. count(字段)
select count(id) from tb_emp;

-- B. count(常量)
select count(0) from tb_emp;

-- C. count(*) -- 推荐
select count(*) from tb_emp;

-- 2. 统计该企业最早入职的员工
select min(entrydate) from tb_emp;

-- 3. 统计该企业最迟入职的员工
select max(entrydate) from tb_emp;

-- 4. 统计该企业员工ID的平均值
select avg(id) from tb_emp;

-- 5. 统计该企业员工的ID之和
select sum(id) from tb_emp;

注意:

1. null值不参与所有聚合函数运算
2. 统计数量可以使用: count(*) count(字段) count(常量), 推荐使用count(*)

分组查询

• 分组查询: select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件];

-- 1. 根据性别分组, 统计男性和女性员工的数据 - count(*)
select gender, count(*) from tb_emp group by gender;

-- 2. 先查询入职时间在2022-02-02(包含) 的员工, 并对结果根据职位分组, 获取员工数量大于等于4的职位
select job, count(*) from tb_emp where entrydate <= '2022-02-02' group by job having count(*)>=4;

注意:

1. 分组之后, 查询的字段一般为聚合函数和分组字段, 查询其他字段无任何意义
2. 执行顺序, where> 聚合函数 > having

排序查询

• 排序查询: select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式, 字段2 排序方式2, ...;

排序方式:

• ASC: 升序(默认值)
• DESC: 降序

-- 排序
-- 1. 根据入职时间, 对员工进行升序排序 - ASC
select * from tb_emp order by entrydate ASC;
select * from tb_emp order by entrydate ;-- 默认是升序

-- 2. 根据入职时间, 对员工进行降序排序 -DESC
select * from tb_emp order by entrydate DESC ;

-- 3. 根据入职时间, 对公司的员工进行升序排序, 入职时间相同则按照更新时间排序
select * from tb_emp order by entrydate, update_time desc ;

注意: 多字段, 当第一个字段相同时, 才会进行后一个排序

分页查询

• 分页查询: select 字段列表 from 表名 limit 起始索引, 查询记录数;

-- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;

-- 2. 查询第一页员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;

-- 3. 查询第二页员工数据, 每页展示5条记录
select * from tb_emp limit 5,5;

-- 4. 查询第三页员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;

-- 起始索引计算公式: 起始索引 = (页码 -1 )*每页展示

注意:

1. 起始索引从0开始, 起始索引 = (查询页码 - 1) * 每页显示的记录数
2. 分页查询是数据库的方言, 不同的数据库有不同的实现, MYSQL是LIMIT
3. 如果查询的是第一页数据, 起始索引可以省略, 直接简写为 limit 10

案例

-- 案例1 : 按需求完成员工管理的分页查询, 根据输入条件, 查询第一页数据, 每页展示10条记录
-- 输入条件:
-- 姓名: 张
-- 性别: 男
-- 入职时间:

select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2000-01-01' and '2023-01-01'
order by entrydate DESC
limit 0, 10;

-- 案例2-1: 根据要求, 完成员工性别的统计 - count(*)
-- if(条件表达式, true取值, false取值)
select if(gender = 1, '男性员工', '女性员工')as'性别', count(*) from tb_emp group by gender;


-- 案例2-2: 根据要求, 完成员工职位的统计
-- case 表达式 when 值1 then 结果1 when 值2 then ... else ... end
select case job when 1 then '讲师' when 2 then '班主任' when 3 then '教工主管' when 4 then '学工主管' else '未分配职位' end, count(*)
from tb_emp
group by job;

多表设计

一对多

根据页面原型及需求文档, 完成部门及员工模块的表结构设计

-- 部门表
create table tb_dept
(
    id          int unsigned primary key auto_increment comment 'ID',
    name        varchar(10) not null unique comment '部门名称',
    create_time datetime    not null comment '创建时间',
    update_time datetime    not null comment '创建时间'

) comment '部门表';

-- auto-generated definition
create table tb_emp
(
    id          int auto_increment comment 'ID是唯一标识'
        primary key,
    username    varchar(20)                  not null comment '员工姓名',
    name        varchar(10)                  not null comment '员工姓名',
    gender      tinyint unsigned             not null comment '性别: 1为男, 2为女',
    job         tinyint unsigned             null comment '职位: 1为x, 2为y, 3为z',
    entrydate   date                         null comment '入职时间',
    password    varchar(32) default '123456' null comment '员工密码',
    dept_id     int unsigned                      comment '部门id',
    create_time datetime                     not null comment '创建时间',
    update_time datetime                     not null comment '创建时间'
);

一对多关系实现: 在数据库表中多的一方, 添加字段, 来关联一的一方的的主键

现象: 部门数据可以直接删除, 然而还有部分员工归属于该部门下, 此时就出现了数据的不完整, 不一致问题

问题分析: 目前上述的两张表, 在数据库层面, 并未建立关联, 所以是无法保证数据的一致性和完整性的

alter table db02.tb_emp
    add constraint emp_to_dept_id foreign key (dept_id) references tb_dept (id);

物理外键

概念: 使用foreign key定义外键关联另外一张表

缺点:

1. 影响增删改的效率(需要检查外键关系)
2. 仅用于单节点数据库, 不适用于分布式, 集群场景
3. 容易引发数据库的死锁问题, 消耗性能

逻辑外键(推荐使用)

概念: 在业务逻辑中, 解决外键关联

通过逻辑外键, 就可以很方便的解决上述问题

一对一

案例: 用户与身份证信息的关系

关系: 一对一关系, 多用于单表拆分, 将一张表的基础字段放在一张表中, 其他字段放在另一张表中, 以提升操作效率

实现: 在任意一方加入外键, 关联另外一方的主键, 并且设置外键为唯一的(unique)

多对多

案例: 学生与课程的关系

关系: 一个学生可以选修多门课程, 一门课程也可以供多个学生选择

实现: 建立第三张中间表, 中间表至少包含两个外键, 分别关联两方主键

多表查询

指从多张表中查询数据

select * from tb_dept, tb_emp;

笛卡尔积: 笛卡尔乘积指在数学中, 两个集合(A集合和B集合)的所有组合情况

select * from tb_dept, tb_emp where tb_emp.dept_id = tb_dept.id;

连接查询

• 内连接: 相当于查询A B交集部分数据
• 外连接
  • 左外连接: 查询左表所有数据(包括两张表交集部分数据)
  • 右外连接: 查询右表所有数据(包括两张表交集部分数据)

子查询

内连接

隐式内连接: select 字段列表 from 表1, 表2 where 条件 ...;

显式内连接: select 字段列表 from 表1 [inner] join 表2 on 连接条件 ...;

-- 内连接
-- A. 查询员工的姓名, 及所属的部门名称(隐式内连接实现)
select tb_emp.name, tb_dept.name from tb_dept, tb_emp where tb_dept.id = tb_emp.dept_id;

-- 起别名
select e.name, d.name from tb_emp as e ,tb_dept d where e.dept_id = d.id;

-- B. 查询员工的姓名, 及所属的部门名称(显式内连接实现)
select tb_emp.name, tb_dept.name from tb_dept inner join tb_emp on tb_dept.id = tb_emp.dept_id;

外连接

左外连接: select 字段列表 from 表1 left [outer] join 表2 on 连接条件 ...;

右外连接: select 字段列表 from 表1 right [outer] join 表2 on 连接条件 ...;

-- 外连接
-- A. 查询所有员工的姓名, 及所属的部门名称(左外连接)
select e.name, d.name from tb_emp e left join tb_dept d on d.id = e.dept_id;

-- A. 查询所有部门的名称, 及所部门里的员工姓名(左外连接)
select e.name, d.name from tb_emp e right join tb_dept d on d.id = e.dept_id;

子查询

介绍: SQL语句中嵌套select语句, 称为嵌套查询, 又称子查询

形式: select * from t1 where column1 = (select column1 from t2 ...);

子查询外部的语句可以是insert / update / select 的任何一个, 最常见的是select

分类:

• 标量子查询: 子查询返回的结果为单个值
• 列子查询: 子查询返回的结果为一列
• 行子查询: 子查询返回的结果为一行
• 表子查询: 子查询返回的结果为多行多列

标量子查询

子查询返回的结果是单个值(数字, 字符串, 日期等), 最简单的形式

常用的操作符: = <> > >= < <=

-- 标量子查询
-- A. 查询教研部的所有员工信息
-- a. 查询部门id
-- b. 查询对应的员工信息
select e.* from tb_emp e where e.dept_id = (select d.id from tb_dept d where d.name = '教研部') ;

-- B. 查询方东百之后入职的员工信息
-- a. 查询方东百的入职时间
-- b. 查询对应的员工信息
select e.* from tb_emp e where e.entrydate>(select  e.entrydate from tb_emp e where e.name = '张无忌' );

列子查询

子查询返回的结果是一列(可以是多行)

常用的操作符: in , not in等

-- 列子查询
-- A. 查询教研部和咨询部的所有员工信息
-- a. 查询对应的id
-- b. 查询对应的员工信息
;
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

行子查询

子查询返回结果是一行(可以是多列)

常用的操作符 : = , <> , in , not in

-- 行子查询
-- A. 查询与小昭工作和性别都相同的员工
-- a. 查询小昭的工作和性别
-- b. 查询对应的员工信息

select * from tb_emp where (job,gender) = (select job, gender from tb_emp where name = '小昭') ;

表子查询

子查询返回的结果是多行多列, 常作为临时表

常用的操作符: in

-- 表子查询
-- A. 查询入职日期是 2012-12-31 之后的员工信息, 及其部门名称
-- a. 查询入职日期是 2012-12-31 之后的员工信息
-- b. 查询部门名称
select * from tb_emp where entrydate > '2012-12-31';
select e.*,d.name from(select * from tb_emp where entrydate > '2012-12-31') e, tb_dept d where e.dept_id = d.id ;

事务

场景: 学工部整个部门解散, 该部门及部门下的员工都需要删除

-- 删除部门
delete from tb_dept where id = 1;

-- 删除部门下的员工
delete from tb_emp where dept_id = 1;

问题: 如果删除部门成功了, 而删除该部门的员工时失败了, 就造成了数据的不一致

介绍

概念: 事务是一组操作的集合, 是一个不可分割的工作单位, 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求, 即这些操作要么同时成功, 要么同时失败

注意事项: 默认MySQL的事务是自动提交的, 也就是说, 当执行一条DML语句, MySQL会立即隐式的提交事务

操作

开启事务: start transaction; / begin;

提交事务: commit;

回滚事务: rollback;

-- 事务
-- 开启事务
start transaction ;

-- 删除部门
delete from tb_dept where id = 3;

-- 删除部门下的员工
delete from tb_emp where dept_id = 3;

-- 提交事务
commit ;

-- 回滚事务
rollback ;

四大特性

原子性: 事务是不可分割的最小单元, 要么全部成功, 要么全部失败

一致性: 事务完成时, 必须使所有的数据都保持一致状态

隔离性: 数据库系统提供的隔离机制, 保证事务在不受外部并发操作影响的独立环境下运行

持久性: 事务一旦提交或回滚, 它对数据库中的数据的改变就是永久的

索引

介绍

索引(index) 是帮助数据库高效获取数据的数据结构

优点:

1. 提高数据查询的效率, 降低数据库的IO成本
2. 通过索引列对数据进行排序, 降低数据排序的成本, 降低CPU消耗

缺点:

1. 索引会占用存储空间
2. 索引大大提高了查询效率, 同时却也降低了insert, update, delete的效率

数据结构

MySQL数据库支持的索引结构有很多, 如: Hash索引, B+Tree索引, Full-Text索引等, 我们平常所说的索引, 如果没有特别指明, 都是默认的B+Tree结构组织的索引

问题: 大数据量的情况下, 二叉搜索树层级深

B+Tree(多路平衡搜索树)

特点:

1. 每一个节点可以存储多个key(有n个key, 就有n个指针)
2. 所有的数据都存储在叶子节点, 非叶子节点仅用于索引数据
3. 叶子节点形成了一颗双向链表, 便于数据的排序及区间范围查询

语法

创建索引: creat额[unique] index 索引名 on 表名(字段名,...);

查看索引: show index from 表名;

删除索引: drop index 索引名 on 表名;

-- 索引
-- 创建索引
create index idx_emp_name on tb_emp(name);

-- 查询索引
show index from tb_emp;

-- 删除索引
drop index idx_emp_name on tb_emp;

注意:

1. 主键字段, 在建表时, 会自动创建主键索引
2. 添加唯一约束时, 数据库实际上会添加唯一索引