Mysql操作指南

/ 数据库

概述

非关系型数据库

非关系型数据库并没有统一的存储结构标准,现常见结构有键值、文档、JSON类型等,对高性能需求设计

  1. 灵活的数据类型:支持多种数据模型,例如键值对、文档型、列族型和图形数据库
  2. 无固定的表结构:不需要预定义固定的表结构,适应非结构化或者半结构化数据
  3. 高性能读写:少关系型,注重高性能读写能力,适用于大规模数据和高并发访问模型

关系型数据库

数据按照类别进行存储,每个类别存储到一个容器中,,表和表之间可以建立关系,可以进行关联操作,性能相对一般

  1. 结构化数据模型:数据以表格形式存储,具有固定的结构
  2. ACID事务:通过强大的事务支持,保证数据的原子性、一致性、隔离性和持久性
  3. 丰富查询语句:支持SQL语句,能够进行复杂的关联数据查询
  4. 数据一致性:数据的关系和约束确保数据的一致性和完整性

特点总结

关系型数据库适用于需要强一致性和复杂查询的场景

非关系型数据库适用于需要高性能、灵活性和分布式处理场景

数据库的选择

  1. 绝大部分应用采用混合模式,二者结合使用
  2. 程序主体数据一般存储在关系型数据库(主)
  3. 程序缓存数据和高并发数据存储到非关系型数据库(辅)

关系型数据库存储设计规则(模型)

数据库管理系统概述

概述

数据库管理系统(DataBase Management System,DBMS

指一种操作和管理数据库的大型软件,用户通过数据库管理系统操作数据库中的数据

常见关系型数据库管理系统

安装官网

MySQL_Downloads (https://www.mysql.com/downloads/)

MySQL启动

客户端连接

结构化查询SQL

数据库操作方式

结构化查询语句(SQL)

SQL:Structure Query Language (结构化查询语言)

SQL被国际化标准组织(ISO)采纳为关系型数据库语言的国际标准,可以使用SQL命令进行关系型数据库操作

SQL概述

SQL包括了所有对数据库的操作,主要是由数据定义、数据操纵、数据查询、数据控制、事务控制等SQL语言的使用规定组成

DDL——>DML——>DQL——>TCL——>DCL

基础指令

SQL语句以分号结尾,可以逐行书写

数据定义DDL

数据库操作

表操作-创建&查询

查询

创建

CREATE TABLE 表名(

字段1 字段1类型 COMMENT 字段1注释,

字段2 字段2类型 COMMENT 字段2注释,

字段3 字段3类型 COMMENT 字段3注释,

……

字段n 字段n类型 COMMENT 字段n注释

) COMMENT 表注释;

注意:[…]为可选参数,最后一个字段后面没有逗号

表操作-修改

表操作-删除

注意:在删除表时,表中的全部数据也都会被删除

数据操作DML

概述

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

DML-添加数据

注意:

DML-修改数据

数据查询DQL

概述

Data Query Language ,数据查询语言,用来查询数据库中表的记录

DQL-基本查询

DQL-条件查询

DQL-聚合函数

将数据作为一个整体,进行纵向运算

注意:null值不参与所有聚合函数的运算

DQL-分组查询

DQL-排序查询

注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行升序

DQL-分页查询

数据控制DCL

Data Control Language 数据控制语言,用来管理数据库用户、控制数据库访问权限

DCL-管理用户

DCL-权限控制

MySQL中定义了很多种权限,常用的有以下几种

权限 说明
ALL,ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 修改数据库/表

函数

SELECT 聚合函数(字段列表) FROM 表名;

字符串函数

函数 功能
CONCAT(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个长度字符串

数值函数

函数 功能
CEIL(x) 向上取整
FLOOR(x) 向下取整
MOD(x,y) 返回x/y的模
RAND() 返回0~1内的随机数
ROUND(x,y) 求参数x的四舍五入的值,保留y位小数

日期函数

函数 功能
CURDATE() 返回当前日期
CURTIME() 返回当前时间
NOW() 返回当前日期和时间
YEAR(date) 获取指定date的年份
MONTH(date) 获取指定date的月份
DAY(date) 获取指定date的日期
DATE_ADD(date,INRTERVAL expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值
DATEDIFF(date1,date2) 返回起始时间date1和结束时间date2之间的天数

流程函数

在SQL语句中实现条件筛选,从而提高语句的效率

函数 功能
IF(value,t,f) 如果value的值为true,则返回t,否则返回f
IFNULL(value1,value2) 如果value1不为空,返回value1,否则返回value2
CASE WHEN [val1] THEN [res1]…ELSE[default] END 如果val1为true,返回res1,…否则返回default默认值
CASE[expr] WHEN [val1] THEN [res1] …[default] END 如果expe的值等于val1,返回res1,否则返回default默认值

约束

概述

约束 描述 关键字
非空约束 限制该字段的数据不能为null NOT NULL
唯一约束 保证该字段的所有数据都是唯一的、不重复的 UNIQUE
主键约束 主键是一行数据的唯一标识,要求非空且唯一 PRIMARY KEY (自增AUTO_INCREMENT)
默认约束 保存数据时,如果未指定该字段的值,则采用默认值 DEFAULT
检查约束 保证字段满足某一个条件 CHECK
外键约束 用来让两张表的数据之间建立联系,保证数据的一致性和完整性 FOGEIGN KEY

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

外键约束

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

多表查询

多表关系

项目开发中,在进行数据库表结构设计时,会根据业务需求及业务模板之间的关系,分析并设计表结构,由于业务之间相互关联,所以各个表结构之间也存在着各种联系,基本上分为三种:

多表查询概述

内连接

内连接查询的是两张表交集的部分

外连接

自连接

自连接查询时,可以是内连接查询也可以是外连接查询

联合查询

对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集

SELECT 字段列表 FROM 表A

UNION[ALL]

SELECT 字段列表 FROM 表B…;

子查询

标量子查询

列子查询

行子查询

子查询返回的结果是一行(可以是多列),这种子查询称为行子查询

常用的操作符:=、<>、IN、NOT IN

表子查询

子查询返回的结果是多行多列,这种子查询称为表子查询

常用的操作符:IN

事务

概述

相关操作

四大特性

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

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

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

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

并发事务问题

问题 描述
脏读 一个事务读到另外一个事务还没有提交的数据
不可重复读 一个事务先后读取同一条数据,但两次读取的数据不同,称之为 不可重复读
幻读 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,有发现这行数据已经存在了

事务隔离级别

隔离级别 脏读 不可重复读 幻读
Read uncommitted
Read committed ×
Repeatable Read(默认) × ×
Serializable × × ×