MySQL教程 | 笔记 (包含数据库、表设计，数据库的增删改查操作；数据库优化等知识点)

时间：2024-07-15 15:43:46

SQL简介

一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准

通用语法：

可以单行或者多行书写，以分号结尾；

可以使用空格 /

SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SOL语句不区分大小写。

SOL语句通常被分为四大类:

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

注释

1、单行注释：

-- 注释内容 或 #注释内容(MySQL特有)

2、多行注释：

 /*注释内容 */

3、SQL语句内注释

字段 字段类型 [约束][comment 字段1注释],

数据库设计

  1. 字段数量 该表 包含什么字段： 原型字段（业务需求） + 基础字段（如id、create_time、update_time）
  2. 表设计： 数据类型、字段长度、约束、注释
  3. 数据类型的选择：在业务允许的情况下，只要满足需求，尽可能选择占用磁盘空间少的数据类型
CREATE TABLE 表名 (
    column1 datatype comment 'ID,唯一标示',
    列/字段名 数据类型 [约束] [comment 字段注释],
    ...(最后一行语句不要逗号)
) [comment 表注释] ;

多表设计

根据业务需求以及业务模块中之间关系，分析并设计表结构设计

表结构联系分为：一对一、一对多、多对多

设计步骤：看业务需求，理清各表之间的关系、设计单表，加对应外键 设计多表

一对一

例如：用户 与 身份证信息 的关系

解决：多用于单表拆分 ，将一张表的基础字段放在一张表中，其他字段放在另一张表，以提升效率

实现：

在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)

示例： 身份证信息表 加入 用户信息表 主键ID 作为外键
user id int unsigned not null unique comment '用户ID',
constraint fk_user_id foreign key (user_id) references tb user(id)

一对多

例如：部门 与 员工 就是一对多关系，一个部门有多个员工；部门为父表 员工为子表

实现：

1.1 字段关联：在 员工表(多) 中添加 部门字段(一），关联部门的 ID 主键

1.2 外键约束：保持数据完整性和一致性

语法：
1、创建表时指定
字段名 数据类型 ... [constraint关键字][指定外键名称] foreign key (外键字段名) references 主表(字段名)
示例：
create table students (  
    id int primary key,  
    name varchar(255),  
    class_id int,  
    foreign key (class_id) references classes(id)  
);

2、建完表后，添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);

物理外键：使用foreign key关联表为物理外键

缺点较多：

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

逻辑外键 ：在业务层逻辑中，解决外键关联。

现在 更推荐 的一种方式

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

例如:
在Java中 员工类 和部门类，员工中有部门外键
通过 在员工类中定义部门属性，并在方法中实现 方法来获取对应的 部门对象 并得到对应部门ID

多对多

例如：学生 与 课程的关系；一个学生可以选修多门课程，一门课程也可以供多个学生选择

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

示例： 关联的两个外键分别为 学生id、课程id; 即可解决每个 学生id 与 课程id的一一对应关系
CREATE TABLE tb_student_course (  
    id INT AUTO_INCREMENT PRIMARY KEY COMMENT '主键',  
    student_id INT NOT NULL COMMENT '学生ID',  
    course_id INT NOT NULL COMMENT '课程ID',  
    CONSTRAINT fk_courseid FOREIGN KEY (course_id) REFERENCES tb_course (`id`),  
    CONSTRAINT fk_studentid FOREIGN KEY (student_id) REFERENCES tb_student (`id`)  
) COMMENT '学生课程中间表';

字段类型（数据库）

总介绍

下面的表格展示了常见的数据库数据类型及其在Java中的对应类型，并附上每种数据类型的说明：

数据库类型 Java类型 说明 备注
char String 固定长度的字符串
varchar String 可变长度的字符串
text String 大文本数据
int int 整数
integer int 整数
tinyint byte 小整数
smallint short 小整数
mediumint int 中等大小整数
bigint long 大整数
float float 单精度浮点数 float(5,2)：5表示整个数字长度，2 表示小数位个数
double double 双精度浮点数 double(5,2)：5表示整个数字长度，2 表示小数位个数
decimal java.math.BigDecimal 高精度定点数 decimal(5,2)：5表示整个数字长度，2 表示小数位个数
numeric java.math.BigDecimal 高精度定点数
date java.sql.Date 日期（不包含时间部分）
time java.sql.Time 时间（不包含日期部分）
timestamp java.sql.Timestamp 日期和时间（包含纳秒精度）
datetime java.sql.Timestamp 日期和时间
boolean boolean 布尔值（真/假）
blob byte[] 二进制大对象
clob java.sql.Clob 字符大对象

常用数据类型主要分为三类

1、数值类型

类型名 大小(byte) 有符号(SIGNED)范围 无符号(UNSIGNED)范围 描述
tinyint 1 (-128，127) (0，255) 小整数值
smallint 2 (-32768，32767) (0，65535) 大整数值
mediumint 3 (-8388608，8388607) (0，16777215) 大整数值
int 4 (-2147483648，2147483647) (0，4294967295) 大整数值
bigint 8 (-263，263-1) (0，2^64-1) 极大整数值
float 4 (-3.402823466 E+38，3.402823466351 E+38) 0 和 (1.175494351 E-38，3.402823466 E+38) 单精度浮点数值
double 8 (-1.7976931348623157 E+308，1.7976931348623157 E+308) 0 和 (2.2250738585072014 E-308，1.7976931348623157 E+308) 双精度浮点数值
decimal 小数值(精度更高)

说明：

1、默认为有符号范围，加关键字unsigned表示无符号范围

如：

age tinyint  表示有符号范围(-128，127)
age tinyint unsigned 表示无符号范围 (0，255)

2、字符串类型

类型名称 大小 描述 适用场景
char 0-255 bytes 定长字符串
varchar 0-65535 bytes 变长字符串
tinyblob 0-255 bytes 不超过255个字符的二进制数据 视频、音频适合用blob后缀数据类型，但是实际应用中一般会存储在服务器中，所以很少用
tinytext 0-255 bytes 短文本字符串 text后缀，适合文本
blob 0-65 535 bytes 二进制形式的长文本数据
text 0-65 535 bytes 长文本数据
mediumblob 0-16 777 215 bytes 二进制形式的中等长度文本数据
mediumtext 0-16 777 215 bytes 中等长度文本数据
longblob 0-4 294 967 295 bytes 二进制形式的极大文本数据
longtext 0-4 294 967 295 bytes 极大文本数据

说明：

1、定长 、 变长

如：

定长：char(10): 最多只能存10个字符,不足10个字符(其余会用空字符占位),占用10个字符空间	性能高	浪费空间
变长：varchar(10): 最多只能存10个字符,不足10个字符, 按照实际长度存储	 			 性能低 节省空间

3、日期时间类型

类型 大小(byte) 范围 格式 描述
date 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
time 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续时间
year 1 1901 至 2155 YYYY 年份值
datetime 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
timestamp 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值，时间戳

一、数据库

创建数据库 create

create database 数据库;

数据库带符号时，用反引号即可，如：create database test;;

使用数据库 use

use  database 数据库名;

查询数据库 show

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

删除数据库 drop

drop database 数据库;

二、数据表

约束

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

MySQL主要提供以下约束

约束类型 描述 关键字
非空约束 限制该字段值不能为null not null
唯一约束 保证字段的所有数据都是唯一、不重复的 unique
主键约束 主键是一行数据的唯一标识，要求非空且唯一 primary key （auto_increment 自增）
默认约束 保存数据时，如果未指定该字段值，则采用默认值 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 COMMENT '年龄',
    gender CHAR(1) default '男' COMMENT '性别'
) COMMENT '用户表';

创建表 / 字段 create

CREATE TABLE 表名 (
    column1 datatype comment 'ID,唯一标示',
    列/字段名 数据类型 [约束] [comment 字段注释],
    ...(最后一行语句不要逗号)
) [comment 表注释] ;

添加字段：

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

示例：

CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY comment ‘ID,唯一标示’,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL,
birthdate DATE,
is_active BOOLEAN DEFAULT TRUE
) comment ‘用户表’;

查询表 / 结构 show

  • 查询当前数据库所有表：show tables；
  • 查询表的结构：desc 表名;
  • 查询建表语句：show create table 表名;

删除表 / 字段 drop

drop table 表名;
drop column 字段名;

修改表 / 字段 alter

修改表名: rename table 表名 to 新表名,
修改字段类型：alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];

三、操作数据

插入数据 insert

INSERT INTO 表名 (column1, column2, 列名3, ...) VALUES (value1, value2, 数值3, ...);

示例：

INSERT INTO users (username, email, birthdate, is_active)   
VALUES ('test', 'test@runoob.com', '1990-01-01', true);

一次插入多条数据

INSERT INTO users (username, email, birthdate, is_active) VALUES
('test', 'test@runoob.com', '1990-01-01', true)，('test2', 'test@runoob.com', '1990-01-01', true);

删除数据 delect

删除表中 所有行 / 指定条件的行

DELETE FROM 表名
WHERE 条件;

示例：

删除指定行

DELETE FROM students
WHERE graduation_year = 2021;

修改数据 update

UPDATE 表名
SET column1 = value1, 列名2 = 数值2, ...
WHERE 条件;

示例：

UPDATE employees
SET name = ‘张三’ , salary = 60000
WHERE employee_id = 101;

四、查询数据 select

基本查询

查询返回所有字段： 不推荐该方法：(不直观，性能低；需要什么查什么)

select * from 表名;

查询多个(指定) 字段：

select 字段1,字段2,字段3 from 表名;

起别名

字段起别名：查询(指定) 字段，并将字段名以别名形式返回

select 字段1 [as 别名1],字段2 [as 别名2] from 表名;

如： 
select name as 姓名,entrydate as 入职日期 from tb_emp;   
1、as关键字可以省略； 2、别名中有空格、特殊符号时可以加单引号
select name '姓 名',entrydate 入职日期 from tb_emp;

表起别名：

化简查询语句中重复出现较长的表名

示例：select * from tb_emp e;

去除重复记录查询

select distinct 字段列表 from 表名;

条件查询(where)

语法
条件查询：select 字段列表  from 表名 where 条件列表;

SQL中构建条件的运算符

逻辑运算符 功能
and 或 && 并且(多个条件同时成立)
or 或 || 或者(多个条件任意一个成立)
not 或 ! 非 ，不是
比较运算符 功能
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
!= 不等于
between…and… 在某个范围之内(含最小、最大值)
in(…) 在in之后的列表中的值，多选一
like 模糊匹配 ( _ 匹配单个字符， % 匹配任意个字符 )
is null 是null
is not null 不是null

分组查询(group by)

select  字段列表 聚合函数 from 表名列表 [where 条件列表]  group by  分组字段列表 [having  分组后过滤条件];

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

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

where 与 having区别

1、执行时机不同:where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤。

2、判断条件不同:where不能对聚合函数进行判断，而having可以。

△注意事项：

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

执行顺序：where >聚合函数>having

聚合函数

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

语法：select 聚合函数(字段列表) from 表名;
函数 功能
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和

注意： 聚合函数不对 null 值进行运算，如 统计 需选择非空字段

示例： 统计企业员工数量
方法1：count(字段)
	  select count(id) from tb_emp;  
方法2：count(常量)
	  select count('A') from tb_emp;  
方法3：count(*)  推荐(MySQL做了专门优化)
	  select count(*) fromth tb_emp;

排序查询(order by)

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

排序方式：

ASC：升序(默认值)
DESC：降序

示例：
--1.根据入职时间，对员工进行降序排序-desc
select * from tb_emp order by entrydate DESC;

分页查询(limit)

语法： select 字段列表 from 表名 limit 起始索引,查询记录数; 

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

△注意事项：

起始索引从 0 开始

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

起始索引 与 页码 间计算公式

起始索引 = (页码-1) * 每页展示记录数

示例： 
每页为5条数据的页面，第3页起始索引为 10
 10 = （3-1）* 5

多表查询

普通查询两张表，展示的结果会是笛卡尔积

如：select * from tb_emp,tb_dept

笛卡尔积： A 集合 和 B 集合的所有组合情况

实现： 指定连接条件， 即根据需求消除无效的笛卡尔积

指定连接条件： 员工表中部门id 与 部门表id 一样的 组合
select * from tb_emp,tb_dept where tb_emp.dept_id = tb_dept.id;

多表查询两大类：连接查询、子查询

连接查询

  • 内连接：相当于查询A、B**两张表交集部分（**有关联）数据

    语法、可读性、使用场景不同的两种语法：

​ 隐式内连接：select 字段列表 from表1, 表2 where 条件…;

​ 显式内连接：select 字段列表 from 表1 [inner] join 表2 on 连接条件 …; （更推荐显式，隐式只适合简单查询场景）

  • 外连接：

    • 左外连接：查询左表所有数据(包括两张表交集部分数据) 更常用
    • 右外连接：查询右表所有数据(包括两张表交集部分数据)
    左外连接： select 字段列表 from 表1 left  [outer] join 表2 on 连接条件...;
右外连接： select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;

子查询

能用连接查询替代子查询就用连接查询（执行效率、查询计划的优化有优势）

SOL语句中嵌套select语句，称为嵌套查询，又称子查询

形式：select * from tA where column1 = ( select columnl from tB …);

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

方法：把查询拆分，先写好一个查询，在把这个查询当做一个子查询，通过 修改子查询 嵌入另一个查询作为一个结果

根据返回结果，将子查询分类：

标量子查询：子查询返回的结果为单个值

select from tb_emp where dept_id = (select id from tb_dept where name '教研部');

列子查询：子查询返回的结果为一列

查询"教研部”和"咨询部”的所有员工信息
select from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');

行子查询：子查询返回的结果为一行

査询与 “韦一笑”的入职日期及职位都相同的员工信息
select * from tb_emp where entrydate = (select entrydate from tb_emp where name = '韦一笑')
and job = (select job from tb_emp where name = '韦一笑');
优化表达：
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');

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

査询入职日期是"2006-01-01”之后的员工信息，及其部门名称

五、数据库优化

索引（最有效）

需求产生：当表中数据越大，查询效率越低，可以通过建立索引来提高查询效率

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

优缺点 ：

  • 优点

    1、提高数据【查询】的效率，降低数据库的I0成本。

    2、通过索引列对数据进行排序，降低数据【排序】的成本，降低CPU消耗。

  • 缺点 (在优点面前，以下缺点已经可以忽略)

    1、索引会占用【存储空间】。
    2、索引大大提高了查询效率，同时却也降低了 insert、update、delete 的效率。(因为进行这些操作后又要重新维护 查询所需的数据结构 )

​ 注意事项：为什么可以忽略缺点——现在的企业服务器的磁盘空间足够大 ；在实际业务中查询占80%以上

语法

创建索引
create [unique] index 索引名 on 表名(字段名,字段名2... );  (可同时为 多个字段或仅一个字段建立)

查看索引
show index from 表名;

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

示例： 为 表tb_sku 中 sn 字段建立索引
create index idx_sku_sn on tb_sku(sn);

注意事项 ： 表中的主键、带唯一约束的字段 都会分别自带 主键索引、 唯一索引

结构

MySQL数据库支持的索引结构有很多，如：Hash索引、B+Tree索引、Full-Text索引等。我们平常所说的索引，如果没有特别指明，都是指默认的 B+Tree（多路平衡搜索树）结构组织的索引。

B+树 特点：高效自平衡排序树，数据全存叶子，索引分层管理，支持快速查找与范围遍历。

SQL优化

分库分表

六、常见其他问题

事务

事务 是一组操作的集合，它是一个不可分割的工作单位。这些 操作要么同时成功，要么同时失败

事务四大特性：

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

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

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响

持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的的独立环境下运行

场景示例：学工部 整个部门解散了，该部门及部门下的员工都需要删除了

正常操作分两个单独的步骤：删除学工部，删除学工部的员工，可能出现异常：数据不一致

实现：

开启事务：start transaction; / begin;
提交事务：commit;
回滚事务：rollback;

示例：
-- 开启事务
start transaction

-- 删除部门
delete from tb dept where id = 3;
-- 删除部门下的员工    
delete from tb emp where dept id=3;

-- 提交事务
commit;
-- 回滚事务 （一部分操作失败时，执行回滚事务可以让先前的操作被取消 包含该事务中成功和失败的操作）
rollback ;

MySQL 处理重复数据

可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY（主键） 或者 UNIQUE（唯一） 索引来保证数据的唯一性

函数

MySQL中提供了一些便捷的函数

1、系统时间 now()

insert into tb emp (username, create time, update time) values (‘wuji’, now(),now() );

2、流程控制函数 if

if(条件表达式，表达式为true时的取值，表达式为false时的取值)

示例：
select if(gender = 1, '男性员工','女性员工'),count(*) from tb_emp group by gender;

3、流程控制函数 case

case 表达式 when 值l then 结果1 when 值2 then 结果2 ... else ... end

示例：
select
	(case job when 1 then '班主任' when 2 then '讲师'  else '未分配职位' end)
	count(*)
from tb_emp group by job;

相关文章