MySQL基础（二）

MySQL
notes
Publish Date:   2023-08-02
Word Count:   2.3k
Read Count:  

MySQL基础（二）

函数

字符串函数

常用函数：

  • concat(s1,s2…sn)
    • 字符串拼接，将s1,s2…sn拼接成一个字符串
  • lower(str)
    • 将字符串str全部转为小写
  • upper(str)
    • 将字符串str全部转为大写
  • lpad(str,n,pad)
    • 左填充，用字符串pad对str左侧进行填充，达到n个字符串长度
  • rpad(str,n,pad)
    • 右填充，用字符串pad对str右侧进行填充，达到n个字符串长度
  • trim(str)
    • 去掉字符串头部和尾部的空格
  • substring(str,start,len)
    • 返回从字符串str从start位置起的len个长度的字符串

数据函数

常用函数：

  • ceil(x)
    • 向上取整
  • floor(x)
    • 向下取整
  • mod(x,y)
    • 返回x/y的摸
  • rand()
    • 返回0-1的随机数
  • round()
    • 求参数x的四舍五入，保留y位小数

日期参数

  • curdate()
    • 返回当前日期
  • curtime()
    • 返回当前时间
  • now()
    • 返回当前日期和时间
  • year(date)
    • 获取指定date的年份
  • month(date)
    • 获取指定date的月份
  • day(date)
    • 获取指定date的日期
  • date_add(date,interval expr type)
    • 返回一个日期/时间值加上一个时间间隔expr后的时间值
  • datediff(date1,date2)
    • 返回起始时间date1和结束时间date2之间的天数

流程函数

  • if(value,t,f)
    • 若value为true，则返回t，否则返回f
  • ifnull(value1,value2)
    • 若value1不为null，返回value1，否则返回value2
  • case when [val1] then [res1]…else [default] end
    • 若val1为true，返回res1,…否则返回default默认值
  • case [expr] when [val1] then [res1]…else [default] end
    • 若expr的值等于val1，返回res1,…否则返回default默认值

约束

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

目的：保证数据库中数据的正确，有效性和完整性。

分类：

  • 非空约束
    • 关键字：NOT NULL
    • 限制该字段的数据不能为null
  • 唯一约束
    • 关键字：UNIQUE
    • 保证该字段的所有数据都是唯一，不重复的
  • 主键约束
    • 关键字：PRIMARY KEY
    • 主键是一行数据的唯一标识，要求非空且唯一
  • 默认约束
    • DEFAULT
    • 保存数据时，若未指定该字段的值，则采用默认值
  • 检查约束（8.0.16版本之后）
    • CHECK
    • 保证字段值满足某一个条件
  • 外键约束
    • FOREIGN KEY
    • 用来让两张表的数据之间建立连接，保证数据的一致性和完整性

注：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

例：

1
2
3
4
5
6
7
CREATE TABLE USER(
	id INT PRIMARY KEY AUTO_INCREMENT COMMENT 'id唯一标识',
	NAME VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
	age INT CHECK (age>0&&age<=120) COMMENT '年龄',
	STATUS CHAR(1) DEFAULT '1' COMMENT '状态',
	gender CHAR(1) COMMENT '性别'
)COMMENT "用户表";

外键约束

外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。

添加外键

1
2
3
4
5
create table 表名(
	字段名 数据类型,
	...
	[constraint] [外键名称] foreign key(外键字段名) references 主表(主表列名
)
1
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名)

删除外键

1
alter table 表名 drop foreign key 外键名称;

删除/更新行为

1
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update 行为 on delete 行为

常见行为：

  • cascade：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，若有，则也删除/更新外键在子表中的记录
  • set null：当在父表中删除对应记录时，首先检查该记录是否有对应外键，若有则设置子表中该外键值为null（这就要求该外键允许取null）

多表设计

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

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

多表查询

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

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

分类：

  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 连接条件;

自连接

自连接查询，可以是内连接查询，也可以是外连接查询。 

1
select 字段列表 from 表a 别名a join 表a 别名b on 条件..;

联合查询

对于union查询，就是把多次查询结果合并，形成新的查询结果集。

使用联合查询的前提条件：列数和字段类型需保持一致。

union all会将全部的数据直接合并在一起，union会对合并之后的数据去重

1
2
3
select 字段列表 from 表a...
union [all]
select 字段列表 from 表b...;

子查询

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

格式：

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

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

根据子查询结果分类：

  1. 标量子查询：子查询返回的结果为单个值(数字，字符串，日期等)，即一行一列
    • 常用操作符：=,<>,>,>=,<,<=等
  2. 列子查询：子查询返回的结果为一列
    • 常用操作符：in,not in，any,some,all等
    • any：子查询返回列表中，有任意一个满足即可
    • some：与any一致
    • all：子查询返回列表的所有值都必须满足
  3. 行子查询：子查询返回的结果为一行
    • 常用操作符：=,<>,in,not in
    • 例如：查询与”张三”的salary和managerid相同的员工信息
      • SELECT * FROM emp WHERE (salary,managerid)=(SELECT salary,managerid FROM emp WHERE NAME=”张三”);
  4. 表子查询：子查询返回的结果为多行多列
    • 常用操作符：in

根据子查询位置分类：where之后，from之后，select之后

事务

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

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

操作

  • 查看事物提交方式

    • select @@autocommit

  • 设置事物提交方式

    • set @@autocommit=0；
    • 0为手动，1为自动

  • 开启事务

    • start transaction;/begin;

  • 若一组操作执行成功，则提交事务

    • commit;

  • 若一组操作至少有一个执行失败，则回滚事务

    • rollback;

四大特性

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

并发事物问题

问题：

  • 脏读
    • 一个事务读到另一个事务还没有提交的数据
  • 不可重复读
    • 一个事务先后读同一条记录，但两次读取的数据不同，称之为不可重复读
  • 幻读
    • 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影

事务隔离级别

事务隔离级别越高，数据越安全，性能越低。

从低到高：

  1. Read uncommitted
    • 脏读 √
    • 不可重复读 √
    • 幻读 √
  2. Read committed
    • 脏读 x
    • 不可重复读 √
    • 幻读 √
  3. Repeatable Read(默认)
    • 脏读 x
    • 不可重复读 x
    • 幻读 √
  4. Serializable
    • 脏读 x
    • 不可重复读 x
    • 幻读 x

查看事务隔离级别：

select @@transaction_isolation;

设置事务隔离级别：

set [session/global] transaction isolation level [Read uncommitted/Read committed/Repeatable Read/Serializable]

Author: ljs
Link: http://example.com/2023/08/02/mysql2/
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 !
MySQL
赏
评论
  TOC