MySQL(二)

JavaWeb MySQL
notes
Publish Date:   2023-05-05
Word Count:   3.5k
Read Count:  

MySQL（二）

数据库：Database，存储的数据的集合，提供数据存储的服务。

数据库管理系统：Database Manage System，DBMS，数据库管理系统，是位于用户和操作系统之间的一层数据管理软件。

数据库系统管理员：Database Administrator，DBA，负责数据库创建、使用及维护的专门人员。

数据库系统：Database System，DBS，数据库系统管理员、数据库管理系统及数据库组成整个单元。

MySQL数据模型

关系型数据库：RNDMS，建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点

  • 使用表存储数据，格式统一，便于维护
  • 使用SQL语言操作，标准统一，使用方便，可用于复杂查询。

SQL介绍

SQL：Structure Query Language，一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

通用语法

  • SQL语句可以单行或多行书写，以分号结尾
  • SQL语句可以使用空格/缩进来增强语句的可读性
  • MYSQL数据库的SQL语句不区分大小写
  • 注释
    • 单行注释：– 注释内容或 # 注释内容（mysql特有）
    • 多行注释：/* 注释内容 */

分类

  • DDL :Data Definition Language，数据定义语言，用来定义数据库对象（数据库，表，字段）
  • DML: Data Manipulation Language，数据操作语言，用来对数据库表中的数据进行增删改
  • DQL: Data Query Language，数据查询语言，用来查询数据库中表的记录
  • DCL:Data Control Language，数据控制语言，用来创建数据库用户、控制数据库的访问权限

mysql数据类型

数值类型

默认有符号数，后可加unsigned变为无符号数。

  • 整数
    • tinyint,占1byte
    • smallint,占2byte
    • mediumint,占3byte
    • int或integer.占4byte
    • bigint,占8byte
  • 浮点数，后加括号，括号里两个参数，一个是整个数字长度，一个是小数位个数
    • float,占4byte
    • double,占8byte
    • decimal,以字符串形式存储，精度更高，依赖于M（精度）和D（标度）的值
      • M：数字的长度
      • D：小数的长度

字符串类型

  • char:定长字符串，char(n)，最多只能存储n个字符，不足n个字符，占用n个字符空间
    • 浪费空间，性能高（时间快）
  • varchar:定长字符串，varchar(n)，最多只能存储n个字符，不足n个字符，按实际长度存储
    • 节省空间，性能低（时间慢）日期时间类型
  • tinyblob，存储二进制字符串，长度0-255字节
  • blob，存储二进制字符串，长度0-65535字节
  • mediumblob，存储二进制字符串，长度0-1677215字节
  • longblob，存储二进制字符串，长度0-4294967295字节
  • tinytext，文本数据（字符串），0-255
  • text，文本数据（字符串），0-65535
  • mediumtext，文本数据（字符串），0-1677215
  • longtext，文本数据（字符串），0-4294967295

日期类型

  • date：日期，只存储年月日，格式YYYY-MM-DD
  • time：时间，只存储时分秒，格式HH:MM:SS
  • year：年份，格式YYYY
  • datetime：日期+时间，存储年月日时分秒，格式YYYY-MM-DD HH:MM:SS
  • timestamp：日期+时间，时间戳，格式YYYY-MM-DD HH:MM:SS

DDL

数据库操作

数据库操作，以下语法中database，可以替换成schema

查询

查询所有数据库：show databases;

查询当前数据库：select database();

使用

使用数据库：use 数据库名;

创建

创建数据库：create database [if not exits] 数据库名;

删除

删除数据库：drop database [if exits ] 数据库名;

表操作

创建

格式

1
2
3
4
5
create table 表名(
	字段1 字段类型 [约束] [commnet 字段1注释],
	......
	字段n 字段类型 [约束] [commnet 字段n注释]
)[commnet 表注释];

范例:

1
2
3
4
5
6
7
CREATE TABLE db1_1( 
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT '唯一的id', 
	username VARCHAR(20) NOT NULL UNIQUE COMMENT '唯一的用户名', 
	NAME VARCHAR(20) NOT NULL COMMENT '姓名', 
	age INT COMMENT '年龄', 
	gender CHAR(1) DEFAULT '男' COMMENT '性别' 
)COMMENT 'db1';
约束

约束是作用于表中字段上的规则，目的是保证数据库中数据的正确性，有效性和完整性。

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

查询

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

修改

  • 添加字段
    • alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
  • 修改字段类型
    • alter table 表名 modify 字段名 新数据类型(长度);
  • 修改字段名和字段类型
    • alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
  • 删除字段
    • alter table 表名 drop column 字段名;
  • 修改表名
    • rename table 表名 to新表名;

删除

drop table [if exists] 表名;

DML

INSERT

  • 指定字段添加数据
    • insert into 表名(字段名1,字段名2) value(值1,值2);
  • 全部字段添加数据
    • insert into 表名 values(值1,值2…);
  • 批量添加数据（指定字段）
    • insert into 表名(字段名1,字段名2) values(值1,值2),(值1,值2);
  • 批量添加数据（全部字段）
    • insert into 表名 values(值1,值2…),(值1,值2)

注：

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

UPDATE

update 表名 set 字段名1=值1,字段2=值2,…[where 条件];

注：修改语句的条件可以有，也可无，若无，则会修改整张表的数据

DELETE

delete from 表名 [where 条件];

注：

  1. delete语句的条件可以有，也可无，若无，则会删除整张表的所有数据
  2. delete语句不能删除某个字段的值（若要操作，可使用update，将该字段的值值为NULL）

DQL

基本查询

  • 查询多个字段
    • select 字段1,字段2,字段3 from 表名;
  • 查询所有字段（通配符）
    • select * from 表名;
    • 注： * 代表所有字段，由于星号不直观且影响效率，所以在实际开发中尽量少用
  • 设置别名
    • select 字段 [as 别名1],字段2 [as 别名2] from 表名;
    • as可以省略
    • 若别名中包括特殊字符例如空格，需要将别名用引号引住
  • 去除重复记录
    • select distinct 字段列表 from 表名;

条件查询

select 字段列表 from 表名 where 条件列表;

比较运算符

1
2
3
4
5
6
7
8
9
10
> 									大于
>=									大于等于
<									小于
<=									小于等于
=									等于
<>或!=							   	不等于
between ... and ...					在某个范围之内(含最小，最大值)
in(...)							在in之后的列表中的值，多选一
like 占位符						  模糊匹配，_匹配单个字符，%匹配任意个字符
is [not] null						（不）是null，不可以用=null

逻辑运算符

1
2
3
and 或 &&							并且，多个条件同时成立
or 或 ||								或者，多个条件中任意一个成立
not 或 !								非，不是

聚合函数

聚合函数是将一列数据作为一个整体，进行纵向计算。

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

函数：

  • count：统计数量
  • max：最大值
  • min：最小值
  • avg：平均值
  • sum：求和

注：

  • null值不参与所有聚合函数运算
  • 统计数量可以用: count(星号) count(字段) count(常量)，推荐使用count(星号)

分组查询

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

having和where的区别

  1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组，而having是分组之后对结果进行过滤。
  2. 判断条件不同：where不能对聚合函数进行判断，having可以

注：

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

排序查询

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

排序方式：

  1. ASC：升序，默认值，可省略
  2. DESC：降序

注意事项：若是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

分页查询

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

注意事项：

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

流程控制函数

  • if(表达式,value1,value2);
    • 当表达式为true，取值value1，当表达式为false。取值value2
  • case expr when value1 then result1 [when value2 then value2…] [else result] end;

多表设计

各个表结构之间存在着各种联系，基本上分为三种。

  1. 一对多（多对一）
    • 实现：在数据库表中多的一方，添加字段（外键），来关联一的主键。
    • 一的表也可称为父表，多的表也可称为子表
  2. 多对多
    • 实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
  3. 一对一
    • 一对一的关系，多用于表单拆分（一张大表单拆成两个小表单），将一张表的基础字段放在一张表中，其他字段放在另一张表中，以提升操作效率
    • 实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（UNIQUE）

外键约束

物理外键

使用foreign key 定义外键关联另外一张表。

缺点：

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

逻辑外键

推荐使用的方式，在业务逻辑中，解决外键关联。

通过逻辑外键，可方便的解决物理外键的缺点。

多表查询

多表查询指从多张表中查询数据。

笛卡尔积：是指在数学中，两个集合的所有组合情况，在多表查询时，需消除无效的笛卡尔积。

分类：

  1. 连接查询
    • 内连接：相当于查询A，B交集部分的数据
    • 外连接
      • 左外连接：查询左表所有数据（包括两张表交集部分数据）
      • 右外连接：查询右表所有数据（包括两张表交集部分数据）
  2. 子查询

连接查询

内连接

  • 隐式内连接
    • 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 连接条件;

子查询

SQL语句中嵌套select语句，成为嵌套查询，又称子查询。

格式：

select * from t1 where column1 = (select column1 from t2…)

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

分类：

  1. 标量子查询：子查询返回的结果为单个值
    • 常用操作符：=,<>,>,>=,<,<=等
  2. 列子查询：子查询返回的结果为一列
    • 常用操作符：in,not in等
  3. 行子查询：子查询返回的结果为一行
    • 常用操作符：=,<>,in,not in
  4. 表子查询：子查询返回的结果为多行多列
    • 常用操作符：in

事务

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

默认mysql的事务是自动提交的，当执行一条DML语句，mysql会立即隐式的提交事务。

操作

  • 开启事务
    • start transaction;/begin;
  • 若一组操作执行成功，则提交事务
    • commit;
  • 若一组操作至少有一个执行失败，则回滚事务
    • rollback;

四大特性

  1. 原子性：事务是不可分割的最小单元，要么全部成功，要么全部失败
  2. 一致性：事务完成时，必须使所有的数据都保持一致状态
  3. 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
  4. 持久性：事务一旦提交或回滚，对数据库中的数据的改变就是永久的。

索引

索引是帮助数据库高效获取数据的数据结构。

优点：

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

缺点：

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

语法

  • 创建索引
    • create [unique] index 索引名 on 表名(字段名…);
  • 查看索引
    • show index from 表名
  • 删除索引
    • drop index 索引名 on 表名;

注意事项：

  • 主键字段，在建表时，会自动创建主键索引
  • 添加唯一约束时，数据库实际上会添加唯一索引
Author: ljs
Link: http://example.com/2023/05/05/jw2/
Reprint policy: All articles in this blog are used except for special statements CC BY 4.0 reprint polocy. If reproduced, please indicate source ljs !
JavaWeb MySQL
赏
评论
  TOC