MySQL基础大全(看这一篇足够!!!)

server/2024/11/24 0:58:21/

文章目录

  • 前言
  • 一、初识MySQL
  • 二、数据库的基本操作
    • 2.1 数据库创建和删除
    • 2.2 数据库存储引擎
      • 2.2.1 MySQL存储引擎简介
      • 2.2.2 InnoDB存储引擎
      • 2.2.3 MyISAM存储引擎
      • 2.2.4 存储引擎的选择
  • 三、数据表的基本操作
    • 3.1 创建数据表
    • 3.2 修改数据表
    • 3.3 删除数据表
    • 3.4 查看数据表结构
  • 四、数据类型和运算符
    • 4.1 MySQL数据类型介绍
      • 4.1.1 整数类型
      • 4.1.2 浮点数类型和定点数类型
      • 4.1.3 日期和时间类型
      • 4.1.4 字符串类型
      • 4.1.5 二进制类型
    • 4.2 如何选择数据类型
    • 4.3 常见运算符介绍
      • 4.3.1 算术运算符
      • 4.3.2 比较运算符
      • 4.3.3 逻辑运算符
      • 4.3.4 位运算符
      • 4.3.5 运算符优先级
  • 五、MySQL函数
    • 5.1 MySQL函数简介
    • 5.2 数学函数
    • 5.3 字符串函数
    • 5.4 时间和日期函数
    • 5.5 条件判断函数
    • 5.6 系统信息函数
    • 5.7 加密函数
    • 5.8 其他函数
  • 六、数据的增删改查
    • 6.1 基本查询数据
    • 6.2 单表查询数据
    • 6.3 使用集合函数查询
    • 6.4 连接查询
    • 6.5 子查询
    • 6.6 合并查询结果
    • 6.7 为表和字段取别名
    • 6.8 使用正则表达式查询
    • 6.9 数据的新增
    • 6.10 数据的修改
    • 6.11 数据的删除
  • 七、索引
    • 7.1 索引简介
      • 7.1.1 索引的含义和特点
      • 7.1.2 索引的分类
      • 7.1.3 索引的设计原则
    • 7.2 创建索引
    • 7.3 删除索引


前言

本文仅介绍MySQL基础内容


一、初识MySQL

1.1 数据库基础

数据库(DataBase,DB)是一个长期存储在计算机内的,有组织的,有共享的,统一管理的数据集合。它是一个按数据结构来存储和管理数据的计算机软件系统。即数据库包含两层含义:保管数据的“仓库”,以及数据管理的方法和技术。

1.2 数据库技术构成

数据库系统由硬件部分和软件部分共同构成,硬件主要用于存储数据库中的数据,包括计算机,存储设备等。软件部分则主要包括DBMS,支持DBMS运行的操作系统,以及支持多种语言进行应用开发的访问技术等。

1.2.1 数据库系统

数据库系统由三个主要的组成部分:

  • 数据库:用于存储数据的地方
  • 数据库管理系统(DataBase Management System,DBMS):用于管理数据库的软件。是用户创建,管理和维护数据库时所使用的软件,位于用户和操作系统之间,对数据库进行统一管理。DBMS能定义数据存储结构,提供数据的操作机制,维护数据库的安全性,完整性和可靠性。
  • 数据库应用程序:为了提高数据库系统的处理能力所使用的的管理数据库的软件补充。大多情况下,DBMS无法满足对数据管理的要求。数据库应用程序的使用可以满足对数据管理的更高要求,还可以使数据管理过程更加直观和友好。数据库应用程序负责与DBMS进行通信,访问和管理DBMS中存储的数据,允许用户插入,修改,删除DB中的数据。

下图辅助理解:
在这里插入图片描述

DBMS像是一个大管家,他接收到我们撰写的SQL之后,根据SQL语法对SQL进行分析,然后去操作数据库数据库就像是一个仓库,他根据SQL在仓库中查找对应的数据,或是丢弃(删除)某些数据,又或是对数据进行变更,然后将结果返回给用户。

1.2.2 SQL语言

数据库进行查询和修改操作的语言叫做SQL。SQL语言包含以下4个部分:

  • 数据定义语言(DDL):DROP,CREATE,ALTER等语句。
  • 数据操作语言(DML):INSERT,UPDATE,DELETE语句。
  • 数据查询语言(DQL):SELECT语句。
  • 数据控制语言(DCL):CRANT,REVOKE,COMMIT,ROLLBACK等语句。

1.2.3 数据库访问接口

不同的程序设计语言会有各自不同的数据库访问接口,程序语言通过这些接口,执行SQL语句,进行数据库管理。主要的数据库访问接口有:

  • ODBC(Open Database Connectivity):开放数据库互连技术为访问不同的SQL数据库提供了一个共同的接口。ODBC使用SQL作为访问数据的标准。这一接口提供了最大限度的互操作性,一个应用程序可以通过共同的一组代码访问不同的SQL数据库管理系统(DBMS)。
    一个基于ODBC的应用程序对数据库的操作不依赖任何DBMS,不直接与DBMS打交道,所有的数据库操作由对应的DBMS的ODBC驱动程序完成。也就是说,不论是Access,MySQL还是Oracle数据库,均可用ODBC API进行访问。由此可见,ODBC的最大优点是能以统一的方式处理所有的数据库

  • JDBC(Java Database Connectivity):java数据库连接用于Java程序连接数据库的标准方法,是一种用于执行SQL语句的Java API,可以为多种关系数据库提供统一访问,它由一组用Java语言编写的类和接口组成。

  • ADO.NET:微软在.NET框架下开发设计的一组用于和数据源进行交互的面向对象类库。ADO.NET提供了对关系数据,XML和应用程序数据的访问,允许和不同类型的数据源以及数据库进行交互。

  • PDO(PHP Data Object):为PHP访问数据库定义了一个轻量级的,一致性的接口,它提供了一个数据访问抽象层,这样无论使用什么数据库,都可以通过一致性的函数执行查询和获取数据。

1.3 什么是MySQL

MySQL是一个小型关系型数据库管理系统,与其他大型数据库管理系统,例如Oracle,DB2,SQL Server等相比,MySQL规模小,功能有限,但是它体积小,速度快,成本低,且它提供的功能对稍微复杂的应用来说已经够用。

MySQL的优势如下:

  • 速度:运行速度快。
  • 价格:MySQL对多数个人用户来说是免费的。
  • 容易使用:与其他大型数据库的设置和管理相比,其复杂程度较低,易于学习。
  • 可移植性:能够工作在众多不同的系统平台上。
  • 丰富的接口:提供了用于C,Java,Python等语言的API。
  • 支持查询语言:MySQL可以利用标准SQL语法,支持ODBC(开放式数据库连接)的应用程序。
  • 安全性和连接性:十分灵活和安全的权限和密码系统,允许基于主机的验证。连接到服务器时,所有的密码传输均采用加密形式,从而保证了密码安全。并且由于MySQL是网络化的,因此可以在英特网上的任何地方访问,提高数据共享的效率。

二、数据库的基本操作

2.1 数据库创建和删除

  • 创建数据库: CREATE DATABASE database_name;
  • 查看数据库定义:SHOW CREATE DATABASE;或 SHOW CREATE DATABASE\G;
  • 删除数据库:DROP DATABASE database_name

2.2 数据库存储引擎

数据库存储引擎是数据库底层软件组件,数据库管理系统(DBMS)使用数据引擎进行创建,查询,更新和删除数据操作。不同的存储引擎提供不同的存储机制,索引技巧,锁定水平等功能。MySQL的核心就是存储引擎。

2.2.1 MySQL存储引擎简介

MySQL提供了多个不同的存储引擎,包括处理事务安全表的引擎和处理非事务安全表的引擎。在MySQL中,不需要在整个服务器中使用同一种存储引擎,针对具体的要求,可以对每一个表使用不同的存储引擎。show engines;命令查看所有引擎,MySQL支持的存储引擎有以下:
在这里插入图片描述
Support列的值表示某种引擎是否可以使用,YES表示可以使用,NO表示不能使用,DEFAULT表示该引擎为当前默认存储引擎。
Transaction表示是否支持事务,XA表示是否支持分布式的交易处理的XA的规范,Savepoints表示是否支持 保存点。
由此可见MySQL默认搜索引擎是InnoDB。

2.2.2 InnoDB存储引擎

InnoDB事务型数据库的首选引擎,支持事务安全表(ACID),支持行锁定和外键。MySQL5.5.5之后,InnoDB作为默认存储引擎,InnoDB主要特性有:

  • InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句中提供了一个类似Oracle的非锁定读。
  • InnoDB是为处理巨大数据量的最大性能设计。它的CPU效率可能是任何其他基于磁盘关系的数据库引擎所不能匹敌的。
  • InnoDB存储引擎完全与MySQL服务器整合,InnoDB存储引擎为在主内存中缓存数据和索引而维持它自己的缓冲池。InnoDB将他的表和索引放在一个逻辑表空间中,表空间可以包含数个文件(或原始磁盘分区)。这与MyISAM表不同,比如在MyISAM表中每个表被存在分离的文件中。
  • InnoDB支持外键完整性约束。存储表中的数据时,每张表的存储都按主键顺序存放,如果没有显示在表定义时指定主键,InnoDB会为每一行生成一个6字节的ROWID,并以此作为主键。
  • InnoDB被用在众多需要高性能的大型数据库站点上。InnoDB不创建目录,使用InnoDB时,MySQL将在MySQL数据目录下创建一个名为ibdata1的10MB大小的自动扩展数据文件,以及两个名为ib_logfile0和ib_logfile1的5MB大小的日志文件。

2.2.3 MyISAM存储引擎

MyISAM基于ISAM存储引擎,并对其进行扩展。它是在Web,数据仓储和其他应用环境下最常用的存储引擎之一。MyISAM拥有较高的插入,查询 速度,但不支持事务。MyISAM主要特性有:

  • 大文件(达63位文件长度)在支持大文件的文件系统和操作系统上被支持。
  • 当把删除和更新及插入操作混合使用的时候,动态尺寸的行产生更少碎片。这要通过合并被删除的块,以及若下一个块被删除,就扩展到下一块来自动完成。
  • 每个MyISAN表最大索引数是64,这可以通过重新编译来改变。每个索引最大的列数是16个。
  • 最大的键长度是1000字节,这也可以通过编译来改变。对于键长度超过250字节的情况,一个超过1024字节的键将被用上。
  • BOLB和TEXT列可以被索引。
  • NULL值被允许在索引的列中。这个值占每个键的0~1个字节。
  • 所有数字键值以搞字节优先被存储以允许一个更高地索引压缩。
  • 每个 MyISAM类型的表都有一个AUTO_INCREMENT的内部列,当INSERT和UPDATE操作的时候该列被更新,同时AUTO_INCREMENT列将被刷新,所以说,MyISAM类型表的AUTO_INCREMENT列更新比InnoDB类型的AUTO_INCREMENT更快。
  • 可以把数据文件和索引文件放在不同目录。
  • 每个字符列可以有不同的字符集。
  • 有VARCHAR的表可以固定或动态记录长度。
  • VARCHAR和CHAR列可以多达64KB。
    使用MyISAN引擎创建数据库,将生产3个文件,文件的名字以表名字开始,扩展名指出文件类型:frm文件存储表定义,数据文件的扩展名为.MYD,索引文件的扩展名是.MYI。

2.2.4 存储引擎的选择

不同的存储引擎都有各自的特点,以适应不同的需求。

功能InnoDBMyISAMMemoryArchive
存储限制64TB256TBRAMNone
支持事务
支持全文索引
支持树索引
支持哈希索引
支持数据缓存N/A
支持外键

如果要提供提交,回滚和崩溃恢复能力的事务安全(ACID)能力,并要求实现并发控制,InnoDB是个很好的选择。如果数据表 主要用来插入和查询记录,则MyISAM引擎能提供较高的处理效率;如果只是临时存放数据,数据量不大,并且不需要较高的数据安全性,可以选择将数据保存在内存中的Memory引擎,MySQL中使用该引擎作为临时表,存放查询的中间结果。如果只有INSERT和SELECT操作,可以选择Archive引擎,Archive殷勤可以支持高并发的插入操作,但是本身并不是事务安全的。Archive存储引擎非常适合存储归档数据,如记录日志信息。

三、数据表的基本操作

3.1 创建数据表

  • 创建数据表:
    CREATE TABLE <表名>
    (
    字段名1 数据类型[列级别约束条件][默认值],
    字段名2 数据类型[列级别约束条件][默认值],

    [表级别约束条件]
    );
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11),
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT
    );

  • 添加主键约束(创建表时)
    CREATE TABLE tb_emp1
    (
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT
    );

  • 添加主键约束(定义完所有列之后)
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    PRIMARY KEY(id)
    );

  • 多字段联合主键
    PRIMARY KEY[字段1,字段2,…,字段n]
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    PRIMARY KEY(id,mame)
    );

  • 使用外键约束:首先外键是表中的一个字段,它可以不是本表(子表)的主键,但对应另外一个表的主键(父表)。外键的主要作用就是保证数据引用的完整性,定义外键后,不允许删除在另一个表中具有关联关系的行。外键的作用是保持数据一致性,完整性。
    主表(父表):对于两个具有关联关系的表而言,相关联字段中主键所在的那个表即是主表。
    从表(子表):对于两个具有关联关系的表而言,相关联字段中外键所在的那个表即是从表。
    创建外键的语法:[CONSTRAINT<外键名> FOREING KEY 字段名1[,字段名2,…]] REFERENCES<主表名>
    主键列[,主键列2,…]
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25),
    location VARCHAR(50)
    );
    CREATE TABLE tb_emp5
    (
    id INT(11) ,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT
    CONSTRAINT fk_emp_dept1 FORRIGN KEY(deptId) REFERENCES tb_dept1(id);
    );
    以上语句执行成功之后,在表tb_emp5上添加了名称为fk_emp_dept1的外键约束,外键名称为deptId,其依赖于表tb_dept1的主键id。

  • 使用非空约束
    指定字段的值不能为空。
    语法:字段名 数据类型 NOT NULL。
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25) NOT NULL,
    location VARCHAR(50)
    );

  • 使用唯一性约束
    要求该列唯一,允许为空,但只能出现一个空值,唯一约束可以确保一列或者几列不出现重复值。
    语法如下:
    字段名 数据类型 UNIQUE(创建表时)
    [CONSTRAINT<约束名> UNIQUE<字段名>](定义完所有列之后)
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25) UNIQUE,
    location VARCHAR(50)
    );
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25),
    location VARCHAR(50),
    CONSTRAINT STH UNIQUE(name)
    );

  • 使用默认约束
    指定某列的默认值。例如将某表中的年龄一列指定为默认值20。新插入数据时,如果没有指定值,就会默认为20.
    语法:字段名 数据类型 DEFAULT 默认值
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11) ,
    name VARCHAR(25) ,
    location VARCHAR(50) DEFAULT ‘徐州’
    );

  • 设置表的属性值自动增加
    每次新插入记录时,系统自动生成字段的主键值。 一个表只能有一个字段使用AUTO_INCREMENT约束,且该字段必须为主键的一部分。AUTO_INCREMENT约束的字段可以是任何整数类型。
    语法:字段名 数据类型 AUTO_INCREMENT
    例如:
    CREATE TABLE tb_emp1
    (
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(25) ,
    location VARCHAR(50)
    );

3.2 修改数据表

修改表是指修改数据库中已经存在的数据表的结构。MySQL使用ALTER TABLE语句修改表。常用的修改表的操作有:修改表名,修改字段数据类型或字段名,增加和删除字段,修改字段的排列位置,更改表的存储引擎,删除表的外键约束等。

  • 修改表名
    语法:ALTER TABLE<旧表名> RENAME[TO]<新表名>,其中TO为可选参数,使用与否均不影响结果。
    例如:ALTER TABLE tb_dept3 RENAME tb_deptment3;将表tb_dept3改名为 tb_deptment3

  • 修改字段数据类型
    语法:ALTER TABLE<表名> MODIFY<字段名> <数据类型>
    例如:ALTER TABLE tb_dept3 MODIFY name VARCHAR(90);

  • 修改字段名
    ALTER TABLE<表名> CHANGE<旧字段名> <新字段名><新数据类型>
    例如:ALTER TABLE tb_dept3 CHANGE name name2 VARCHAR(10);
    由于不同类型的数据在机器中存储的方式及长度并不相同,修改数据类型可能会影响到数据表中已有的数据记录。因此,当数据表中已有数据时,不要轻易修改数据类型。

  • 添加字段
    语法:ALTER TABLE<表名> ADD <新字段名><数据类型> [约束条件][FIRST|AFTER已存在字段名];
    FIRST是可选参数,其作用是将新添加的字段设置为表的第一个字段;
    AFTER为可选参数,其作用是将新添加的字段添加到指定的“已存在字段名”的后面。
    FIRST和AFTER用于指定新增字段在表中的位置,如果SQL语句中没有这个两个参数,则默认将新添加的字段设置为数据表的最后列。
    例如:ALTER TABLE tb_dept3 ADD sex VARCHAR(2);默认放在最后一列
    ALTER TABLE tb_dept3 ADD sex VARCHAR(2) FIRST;放在最前列
    ALTER TABLE tb_dept3 ADD sex VARCHAR(2) AFTER name;指定为在name列后。

  • 删除字段
    语法:ALTER TABLE<表名> DROP <字段名>;
    例如:ALTER TABLE tb_dept3 DROP sex;

  • 修改字段的排列位置
    语法:ALTER TABLE<表名> MODIFY <字段1><数据类型>FIRST|AFTER<字段2>;
    FIRST是可选参数,其作用是将新添加的字段设置为表的第一个字段;
    AFTER为可选参数,其作用是将新添加的字段添加到指定的“已存在字段名”的后面。
    FIRST和AFTER用于指定新增字段在表中的位置,如果SQL语句中没有这个两个参数,则默认将新添加的字段设置为数据表的最后列。
    例如: ALTER TABLE tb_dept3 MODIFY sex VARCHAR(2) FIRST;放在最前列
    ALTER TABLE tb_dept3 MODIFY sex VARCHAR(2) AFTER name;指定为在name列后。

  • 更改表的存储引
    根据自己的需要,选择不同的引擎,甚至可以为每一张表选择不同的存储引擎。
    语法:ALTER TABLE <表名> ENGINE=<更改后的存储引擎名>
    例如:ALTER TABLE tb_dept3 ENGINE=MyISAM;

  • 删除表的外键约束
    语法:ALTER TABLE <表名> DROP FOREIGN KEY<外键约束名>
    例如:ALTER TABLE tb_dept3 DROP FOREIGN KEY fk_emp_dept;

3.3 删除数据表

  • 删除数据表之前判断是否存在
    语法:DROP TABLE [IF EXISTS] 表1,表2,…,表n;
    例如:DROP TABLE IF EXISTS tb_dept2;

  • 直接删除表
    语法:DROP TABLE 表1;
    例如:DROP TABLE tb_dept2;

  • 删除有外键约束的主表
    ALTER TABLE tb_dept1 DROP FOREIGN KEY fk_emp_dept;

3.4 查看数据表结构

语法:DESCRIBE 表名;或者简写为 DESC 表名;
在这里插入图片描述

查看数据表详细结构语法:SHOW CREATE TABLE <表名\G>;
使用SHOW CREATE TABLE语句,不仅可以查看表创建时候的详细语句,而且还可以查看存储引擎和字符编码。加上参数\G之后,可以使结果更加直观,易于查看。
在这里插入图片描述

四、数据类型和运算符

4.1 MySQL数据类型介绍

MySQL支持多种数据类型,主要有数值类型,日期/时间类型和字符串类型

  • 数值数据类型:包括整数类型TINYINT,SMALLINT,MEDIUMINT,INT,BIGINT。浮点小数数据类型FLOAT和DOUBLE,定点小数类型DECIMAL。
  • 日期/时间类型:包括YEAR,TIME,DATE,DATETIME和TIMESTAMP。
  • 字符串类型:包括CHAR,VARCHAR,BINARY,VARBINARY,BLOB,TEXT,ENUM和SET等。

4.1.1 整数类型

数值型数据类型主要用来存储数字,MySQL提供了多种数值数据类型,不同的数据类型提供不同的取值范围,可以存储的值范围越大,其所需要的存储空间也会越大。

MySQL中的整数型数据类型以及取值范围:

类型名称说明存储需求有符号无符号
TINYINT很小的整数1个字节-128~1270~255
SMALLINT小的整数2个字节32768~327670~65535
MEDIUMINT中等大小的整数3个字节-8388608~83886070~16777215
INT(INTEGER)普通大小的整数4个字节-2147483648~21474836470~4294967295
BIGINT大整数8个字节-9223372036854775808~92233720368547758070~18446744073709551615

当我们创建表和列的时候,给列指定数据类型以及长度,例如year INT(4),括号中显示的11是宽度,宽度和数据类型的取值范围是无关的。显示宽度只是指明MySQL最大可能显示的数字个数,数值的位数小于指定的宽度时会由空格填充;如果插入了大于显示宽度的值,只要该值不超过该类型整数的取值范围,数值依然可以插入,而且能够显示出来。例如,假如向year字段插入一个数值19999,即使数值宽度超过了设定的4宽度,但19999依然在INT类型的取值范围内,所以MySQL依然显示完整的19999,而不是1999。
宽度只是用于显示,并不能限制取值范围和占用空间,如:INT(3)会占用4个字节的存储空间,并且允许的最大值也不会是999,而是INT整型所允许的最大值。
其他整数数据类型也可以在定义表结构时指定所需要的显示宽度,如果不指定,则系统为每一种类型指定默认的宽度值。

4.1.2 浮点数类型和定点数类型

MySQL中使用浮点数和定点数来表示小数。浮点类型有两种:单精度浮点数(FLOAT)和双精度浮点类型(DOUBLE)。定点类型只有一种:DECIMAL。浮点类型和定点类型都可以用(M,N)来表示,M称为精度,表示总共的位数,N称为标度,是表示小数的位数。
MySQL中的浮点数和定点数数据类型以及取值范围:

类型名称说明存储需求有符号无符号
FLOAT单精度浮点数4个字节-3.402823466E+38~-1.175494351E-380和1.175494351E-38~3.402823466E+38
DOUBLE双精度浮点数8个字节-1.7976931348623157E+308~-2.2250738585072014E-3080和2.2250738585072014E-308~1.7976931348623157E+308
DECIMAL(M,D),DEC压缩的“严格”定点数M+2个字节

DECIMAL类型不同于FLOAT和DOUBLE,DOUBLE实际是以串存放的,DECIMAL可能的最大取值范围与DOUBLE一样,但是其有效的取值范围由M和D的值决定。如果改变M而固定D,则其取值范围将随M的变大而变大。
不论是定点还是浮点类型,如果用户指定的精度超出精度范围,则会四舍五入进行处理。
例如:
在这里插入图片描述
![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/22cfbd56f1784c23902fc78e307b1ab3.png在这里插入图片描述
FLOAT和DOUBLE在不指定精度时,默认会按照实际的精度(由计算机硬件和操作系统决定),DECIMAL如不指定精度默认为(10,0)。
浮点数相对于定点数的优点是在长度一定的情况下,浮点数能够表示更大的数据范围;它的缺点就是会引起精度问题。
在MySQL中,定点数以字符串形式存储,在对精度要求比较高的时候(如货币,科学数据等)使用DECIMAL的类型比较好,另外两个浮点数进行减法和比较运算时也容易出问题,所以在使用浮点型时需要注意,并尽量避免做浮点数比较。

4.1.3 日期和时间类型

MySQL中有多种表示日期的数据类型,主要有:DATETIME,DATE,TIMESTAMP,TIME和YEAR。
日期与时间类型:

类型名称日期格式日期范围存储需求
YEARYYYY1901~21551字节
TIMEHH:MM:SS-838:59:59~838:59:593字节
DATEYYYY-MM-DD1000-01-01~9999-12-313字节
DATETIMEYYYY-MM-DD HH:MM:SS1000-01-01 00:00:00~9999-12-31 23:59:598 字节
TIMESTAMPYYYY-MM-DD HH:MM:SS1907-01-01 00:00:01 UTC~2038-01-19 03:14:07 UTC4字节
  • YEAR
    YEAR类型是一个单字节类型用于表示年,在存储时只需要1个字节。可以使用各种格式指定YEAR值,如下所示:
    (1)以4位字符串或者4位数字格式表示的YEAR,范围为‘1901’~‘2155’。输入格式为‘YYYY’或者YYYY,例如,输入‘2010’或2010,插入到数据库的值均为2010。
    (2)以2位字符串或者4位数字格式表示的YEAR,范围为‘00’到‘99’。‘00’~‘69’和‘70’ ~‘99’范围的值分别被转换为2000 ~ 2069和1970 ~ 1999范围的YEAR值。‘0’与‘00’的作用相同。插入超过取值范围的值将被转换为2000
    (3)以2位数字表示的YEAR,范围为1~99。1 ~ 69和70 ~ 99范围的值分别被转换为2001 ~ 2069 和 1970 ~ 1999 范围的YEAR值。注意:在这里0值将被转换为0000,而不是2000.
    PS:两位整数范围与两位字符串范围稍有不同,例如:插入2000年,读者可能会使用数字格式的0表示YEAR,实际上,插入数据库的值为0000,而不是所希望的2000。只有使用字符串格式的‘0’或‘00’,才可以被正确的解释为2000。非法YEAR值将被转换为0000.

下面示例辅助理解:
SQL语句
创建一个表
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述

结果
在这里插入图片描述

  • TIME
    TIME类型用在只需要时间信息的值,在存储时需要3个字节。格式为‘HH:MM:SS’。HH表示小时;MM表示分钟;SS表示秒。TIME类型的取值范围为-838:59:59~838:59:59,小时部分会如此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时),还可能是某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时,或者甚至为负)。可以使用各种格式指定TIME值,如下所示:
    (1)‘D HH:MM:SS’格式的字符串。还可以使用下面任何一种“非严格”的语法:‘HH:MM:SS’,‘HH:MM’,‘D HH:MM’,‘D HH’或‘SS’,这里的D表示日,可以取0~34之间的值。在插入数据库时,D被转换为小时保存,格式为“D*24+HH”。
    (2)‘HHMMSS’格式的,没有间隔的字符串或者HHMMSS格式的数值,假定是有意义的时间。例如:‘101211’被理解成‘10:12:11’,但‘109712’是不合法的,因为分钟部分超过范围,存储时将会报错’。
    PS1:为TIME列分配简写值时应注意:如果没有冒号,MySQL解释值时,假定最右边的两位表示秒。例如‘1112’和1112会被MySQL解释为‘00:11:12’,同样‘12’和12被解释为‘00:00:12’。相反,TIME值中如果使用冒号则被看作当天的时间,也就是说,‘11:12’会被MySQL解释为‘11:12:00’。
    PS2:在使用‘D HH’格式时,小时一定要使用双位数值,如果是小于10的小时数,应在前面加0。

下面示例辅助理解:
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句(插入当前数据库所在的系统时间)
在这里插入图片描述
结果
在这里插入图片描述

  • DATE
    DATE类型用在仅需要日期时,没有时间部分,在存储时需要3个字节。日期格式为‘YYYY-MM-DD’,‘YYYY’表示年,‘MM’表示月份,‘DD’表示日。在给DATE类型的字段赋值时,可以使用字符串或者数字类型的数据插入,只要符合DATE的日期格式即可,如下:
    (1)以‘YYYY-MM-DD’或者‘YYYYMMDD’字符串格式表示的日期,取值范围为‘1000-01-01’~‘9999-12-3’。例如,输入‘2012-12-31’或者‘20121231’,插入数据库的值都为2012-12-31.
    (2)以‘YY-MM-DD’或者‘YYMMDD’字符串格式表示的日期,在这里YY表示两位的年值。包含两位年值的日期会令人模糊,因为不知道世纪。MySQL使用以下规则解释两位年值:‘00~69’范围的年值转换为‘2000 ~ 2069’;‘70 ~ 99’范围的年值转换为‘1970 ~ 1999’。例如,输入‘12-12-31’,插入的数据路的日期为2012-12-31;输入‘981231’,插入数据的日期为1998-12-31.
    (3)以YY-MM-DD或者YYMMDD数字格式表示的日期,与前面相似,00-69范围的年值转换为2000~2069;70 ~ 99范围的年值转换为1970 ~ 1999。例如,输入12-12-31插入数据库的日期为2012-12-31;输入981231,插入数据的日期为1998-12-31。
    (4)使用CURRENT_DATE或者NOW(),插入当前系统日期。

下图示例辅助理解:
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述

结果
在这里插入图片描述
这是因为MySQL允许“不严格”语法:任何标点符号都可以作用日期部分之间的间隔符。例如‘98-12-21’,‘98.12.21’,‘98@12@21’是等价的,这些值可以正确的插入到数据库

  • DATETIME
    DATETIME类型用在需要同时 包含日期和时间信息的值,在存储时需要8个字节。日期格式为‘YYYY-MM-DD HH:MM:SS’,‘YYYY’表示年,‘MM’表示月,‘DD’表示日,‘HH’表示小时,‘MM’表示分钟,'SS’表示秒。在给DATETIME类型的字段赋值时,可以使用字符串类型或者数字类型的数据插入,如下所示:
    (1)以‘YYYY-MM-DD HH:MM:SS’或者‘YYYYMMDDHHMMSS’字符串格式表示的值,取值范围为‘1000-01-01 00:00:00’~‘9999-12-3 23:59:59’。
    (2)以‘YY-MM-DD HH:MM:SS’或者‘YYMMDDHHMMSS’字符串格式表示的日期,在这里YY表示两位的年值。‘00~69’范围的年值转换为‘2000 ~ 2069’;‘70 ~ 99’范围的年值转换为‘1970 ~ 1999’。
    (3)以YYYYMMDDHHMMSS或者YYMMDDHHMMSS数字格式表示的日期和时间,规则和上述一致。
    PS:MySQL允许“不严格”语法:任何标点符号都可以作用日期部分之间的间隔符。例如‘98-12-21 11:30:45’,‘98.12.21 11+30+45’,‘98@12@21 113045’是等价的,这些值可以正确的插入到数据库

  • TIMESTAMP
    TIMESTAMP的显示 格式与DATETIME相同,显示宽度固定在19个字符,日期格式为YYYY-MM-DD HH:MM:SS,在存储时需要4个字节。但是TIMESTAMP列的取值范围小于DATETIME的取值范围,为‘1970-01-01 00:00:01’UTC~‘2038-01-19 03:14:07’ UTC,其中UTC(Coordinated Universal Time,为世界标准时间)。
    如果为一个DATETIME或TIMESTAMP对象分配一个DATE值,结果值的时间部分被设置为“00:00:00”,因为DATE值未包含时间信息。如果为一个DATE对象分配一个DATETIME或TIMESTAMP值,结果值的时间部分会被截断。
    TIMESTAMP与DATETIME除了存储字节和支持的范围不同外,还有一个最大的区别就是:DATETIME在存储日期数据时,按实际输入的格式存储,即输入什么就存储什么,与时区无关;而TIMESTAMP值的存储是以UTC(世界标准时间)格式保存的,存储时对当前时区进行转换,检索时再转换回当前时区。即查询时,根据当前时区的不同,显示的时间值是不同的。

下图示例辅助理解:
SQL语句
在这里插入图片描述
结果
在这里插入图片描述
SQL语句
在这里插入图片描述
结果
在这里插入图片描述

数据库插入当前系统时间(当前是东八区)
设置当前时区为东十区
再次查询时,时间以东十区显示(实际数据库中存储的仍是东八区时间,如下所示)
在这里插入图片描述

4.1.4 字符串类型

字符串类型用来存储字符串数据,除了可以存储字符串数据外,还可以存储其他数据,比如图片和声音的二进制数据。字符串可以进行区分或者不区分大小写的串比较,另外,还可以进行模式匹配查找。

MySQL字符串数据类型:

类型名称说明存储需求
CHAR(M)固定长度非二进制字符串M字节,1<=M<=255
VARCHAR(M)变长非二进制字符串L+1字节,在此L<=M和1<=M<=255
TINYTEXT非常小的非二进制字符串L+1字节,在此L<2^8
TEXT小的非二进制字符串L+2字节,在此L<2^16
MEDIUMTEXT中等大小的非二进制字符串L+3字节,在此L<2^24
LONGTEXT大的非二进制字符串L+4字节,在此L<2^32
ENUM枚举类型,只能有一个枚举字符串值1或2个字节,取决于枚举值的数目(最大值65535)
SET一个设置,字符串对象可以有零个或多个SET成员1,2,3,4或8个字节,取决于集合成员的数量(最多64个成员)

VARCHAR,BLOB和TEXT类型是变长类型,对于其存储需求取决于列值的实际长度(在前面的表格中用L表示),而不是取决于类型的最大可能尺寸。例如,一个VARCHAR(10)列能保存最大长度为10个字符的一个字符串,实际的存储需要是字符串的长度L,加上1个字节以记录字符串长度。例如:对于字符“abcd”,L是4而存储要求是5个字节。

4.1.5 二进制类型

MySQL支持两类字符型数据:文本字符串和二进制字符串。

MySQL中的二进制数据类型:

类型名称说明存储需求
BIT(M)位字段类型大约(M+7)/8个字节
BINARY(M)固定长度二进制字符串M个字节
VARBINARY(M)可变长度二进制字符串M+1个字节
TINYBLOB(M)非常小的BLOBL+1字节,在此L<2^8
BLOB(M)小BLOBL+2字节,在此L<2^16
MEDIUMBLOB(M)中等大小的BLOBL+3字节,在此L<2^24
LONGBLOB(M)非常大的BLOBL+4字节,在此L<2^32

4.2 如何选择数据类型

MySQL提供了大量的数据类型,为了优化存储,提高数据库性能,在任何情况下均应使用最精确的类型。

  1. 整数和浮点数
    如果不需要小数部分,则使用整数来保存数据;如果需要表示小数部分,则使用浮点数类型。对于浮点数琚列,列入的数值会对该列定义的小数位进行四舍五入。
    浮点类型包括FLOAT和DOUBLE类型。DOUBLE类型精度比FLOAT类类型高,因此,如要求存储精度较高时,应选择DOUBLE类型。
  2. 浮点数和定点数
    浮点数FLOAT和DOUBLE相对于定点数DECIMAL的优势是:在长度一定的情况下,浮点数能表示更大的数据范围。但是由于浮点数容易产生误差,因此对精度要求比较高时,建议使用DECIMAL来存储。DECIMAL在MySQL中是以字符串存储的,用于定义货币等精确度要求较高的数据。在数据迁移中,FLOAT(M,D)是非标准SQL定义,数据库迁移可能会出现问题,最好不要这样使用。另外两个浮点数进行减法和比较运算时也比较容易出问题,因此在进行计算的时候,一定要小心。如果进行数值比较,最好使用DECIMAL类型。
  3. 日期和时间类型
    MySQL对于不同种类的日期和时间有很多的数据类型,如果只需要记录年份,则使用YEAR类型即可,如果只记录时间,只需使用TIME类型。
    如果同时需要记录日期和时间,则可以使用TIMESTAMP或者DATETIME类型。由于TIMESTAMP列的取值范围小于DATETIME的取值范围,因此存储范围较大的日期最好使用DATETIME。
    TIMESTAMP也有一个DATETIME不具备的属性。默认情况下,当插入一条记录但并没有指定TIMESTAMP这个列值时,MySQL会把TIMESTAMP列设为当前的时间。因此当需要插入记录同时插入当前时间时,使用TIMESTAMP是方便的,另外TIMESTAMP在空间上比DATETIME更有效。
  4. CHAR和VARCHAR之间的特点与选择
    CHAR是固定长度字符,VARCHAR是可变长度字符;CHAR会自动删除插入数据的尾部空格,VARCHAR不会删除尾部空格。
    CHAR是固定长度,所以它的处理速度比VARCHAR的速度更快,但是它的缺点就是浪费存储空间。所以对存储不大,但在速度上有要求的可以使用CHAR类型,反之可以使用VARCHAR类型来实现。
    对于MyISAM存储引擎最好是使用固定长度的数据类型,这样可以使整个表静态化,从而使数据检索更快,用空间换时间。
    对于InnoDB存储引擎:使用可变长度的数据类型,因为InnoDB数据表的存储格式部分固定长度和可变长度,因此使用CHAR不一定比使用VARCHAR更好,但由于VARCHAR是按照实际的长度存储,比较节省空间,所以对磁盘IO和数据存储总量比较好。
  5. ENUM和SET
  6. BOLB和TEXT
    BLOB是二进制字符串,TEXT是非二进制字符串,两者均可存放大容量的信息。BLOB主要存储图片,音频信息等,而TEXT只能存储纯文本文件。

4.3 常见运算符介绍

运算符连接表达式中各个操作数,其作用是指明对操作数所进行的运算。

4.3.1 算术运算符

用于各类数值运算,包括加(+),减(-),乘(*),除(/),求余(或称模运算,%)。

4.3.2 比较运算符

用于比较的运算符。一个比较运算符的结果总是1,0,或者是null,比较运算符经常在SELECT的查询条件子句中使用,用来查询满足指定条件的记录。

MySQL中的比较运算符

运算符作用
=等于
<=>安全的等于
<>(!=)不等于
<=小于等于
>=大于等于
>大于
IS NULL判断一个值是否为null
IS NOT NULL判断一个值是否不为null
LEAST在有两个或多个参数时,返回最小值
GREATEST在有两个或多个参数时,返回最大值
BETWEEN AND判断一个值是否落在两个值之间
ISNULL与IS NULL作用相同
IN判断一个值是IN列表中的任意一个值
NOT IN判断一个值不是IN列表中的任意一个值
LIKE通配符匹配
REGEXP正则表达式匹配
  • “=”不能用于null值的判断,而“<=>”可以对null进行判断,两者都为NULL时返回值为1.

  • “BETWEEN AND”用法示例:SELECT 4 BETWEEN 4 AND 6;意思是4是否在4~6之间

  • “LEAST|”用法示例:SELECT least(2,4,5),返回2,4,5三个数字中最下的值。

  • 同理“GREATEST”与“LEAST”用法一致。

  • LIKE运算符用来匹配字符串,语法格式为:expr LIKE 匹配条件,LIKE运算符心思进行匹配时,一般使用下面两种通配符:
    (1)“%”匹配任何数目的字符,甚至包括零字符。
    (2)“_”只能匹配一个字符。

  • REGEXP运算符用来匹配较为复杂的字符串,语法格式为:expr REGEXP 匹配条件。REGEXP运算符在进行匹配时,常用的有下面几种通配符:
    (1)‘^‘匹配以该字符后面的字符开头的字符串
    (2)‘$’匹配以该字符后面的字符结尾的字符串
    (3)’.‘匹配任何一个单字符
    (4)’[…]‘匹配在方括号内的任何字符。例如,"[abc]"匹配a,b,c,为了命名字符的范围,使用一个’-’。"[a-z]"匹配任何字母,而“[0- 9]”匹配任何数字。
    (5)''匹配 零个或多个在它面前的字符。例如,"x"匹配任何数量的‘x’字符,“[0-9]”匹配任何数量的数字,而“.”匹配任何数量的任何字符。

4.3.3 逻辑运算符

在MySQL中,逻辑运算符的求值结果为1(TRUE),0(FALSE)和NULL。

MySQL中的逻辑运算符

运算符作用
NOT或者!逻辑非
AND 或者 &&逻辑与
OR逻辑或
XOR逻辑异或

4.3.4 位运算符

位运算符是用来对二进制字节中的位进行测试,移位或者测试处理。

MySQL中的位运算符

运算符作用
l位或
&位与
^位异或
<<位左移
>>位右移
~位取反,反转所有比特

4.3.5 运算符优先级

运算符按照优先级由低到高排列
在这里插入图片描述

五、MySQL函数

5.1 MySQL函数简介

函数表示对输入参数值返回一个具有特定关系的值,MySQL提供了大量丰富的函数,在进行数据库管理以及数据的查询和操作时会经常用到各种函数。通过对数据的处理,数据库功能可以变得更加强大,更加灵活地满足不同用户的需求。

5.2 数学函数

  • 绝对值函数ABS(x)和返回圆周率函数PI()。

  • 平方根函数SQRT(x)和求余函数MOD(x,y)。

  • 获取整数函数 CEIL(x),CEILING(x),FLOOR(x)。
    CEIL(x)与CEILING(x)意义相同,返回大于x的最小整数值。FLOOR(x)返回小于x的最大整数值。返回值都转换为BIGINT类型。

  • 获取随机数函数RAND()和RAND(x)。
    RAND(x)返回一个随机浮点数值v,范围在0到1之间(0<=v<=1.0)。若已指定一个整数参数x,则它被用作种子值,用来产生重复序列。当RAND(x)的参数相同时,将产生相同的随机数,不同的x产生的随机数值不同。

  • 四舍五入函数ROUND(x),ROUND(x,y)和TRUNCATE(x,y)
    (1)ROUND(x,y)返回最接近于参数x的数,其值被保留到小数点后面y位,若y为负值,则将保留x值到小数点左边y位,保留的小数点左边的相应位数直接保存为0,不进行四舍五入。
    例如ROUND(1.38,1)保留小数点后面1位,四舍五入的结果为1.4;ROUND(1.38,0)保留 小数点后面0位,即返回四舍五入后的数值;ROUND(23.38,-1)保留小数点的左边1位,结果即为20,ROUND(232.38,-2)保留小数点左边2位,结果即为200.
    (2)TRUNCATE(x,y)返回被舍去至小数点后y位的数字x,若y的值为0,则结果不带有小数点或不带有小数部分。若y设为负数,则截去(归零)x小数点左起第y位开始后面所有低位的值。
    例如:TRUNCATE(1.31,1)和TRUNCATE(1.99,1)都保留小数点后1位数字,返回值分别为1.3和1.9,TRUNCATE(1.99,0)返回整数部分值1;TRUNCATE(19.99,-1)截去小数点左边第1位后面的值,并将整数部分的1位数字置0,结果为10.

  • 符号函数SIGN(x)
    SING(x)返回参数的符号,x的值为负,零或正时返回结果依次为-1,0或1。

  • 对数运算函数LOG(x)和LOG10(x)。

  • 角度和弧度相互转换的函数RADIANS(x)和DEGREES(x)

  • 正弦函数SIN(x)和反正弦函数ASIN(x)

  • 正切函数TAN(x),反正切函数ATAN(x),余切函数COT(x)

5.3 字符串函数

  • 计算字符串字符数函数CHAR_LENGTH(str)和字符串长度函数LENGTH(str)
  • 合并字符串函数CONCAT(s1,s2,…),CONCAT_WS(x,s1,s2,…)
    CONCAT(s1,s2,…)与CONCAT_WS(x,s1,s2,…)的区别在于如果sn为null的话,前者的返回结果即为null,后者会忽略null的部分,继续拼接合并其他不为null 的值作为结果返回。
  • 替换字符串函数INSERT(s1,x,len,s2)。
  • 字母大小写转换函数LOWER(str)和LCASE(str)。
  • 获取指定长度的字符串函数LEFT(s,n)和RIGHT(s,n)。
  • 填充字符串函数LPAD(s1,len,s2)和RPAD(s1,len,s2)。
  • 删除空格函数LTRIM(s),RTRIM(s)和TRIM(s)。
  • 删除指定字符串函数TRIM(s1 FROM s)
    删除字符串s中两端所有的子字符串s1,中间的s1字符串不删除。
  • 重复生成字符串函数REPEAT(s,n)。
  • 空格函数SPACE(n)和替换函数REPLACE(s,s1,s2)。
  • 比较字符串大小函数STRCMP(s1,s2)
  • 获取子串函数SUBSTRING(s,n,len)和MID(s,n,len)
    如果len小于1,则结果始终未空字符串。
  • 匹配子串开始位置函数
    LOCATE(str1,str),POSITION(str1 IN str),INSTR(str,str1)3个函数作用相同,返回子字符串str1在字符串str中的开始位置。
  • 字符串反转函数REVERSE(s)
  • 返回指定位置的字符串函数
    ELT(N,字符串1,字符串2,字符串3,…,字符串N),若N=1,则返回值为字符串1,若N=2,则返回值为字符串2,依次列推。若N小于1或大于参数的数目,则返回值为NULL。
  • 返回指定字符串位置的函数FIELD(s,s1,s2,…)。
  • 返回子串位置的函数FIND_IN_SET(s1,s2)。
  • 选取字符串的函数MAKE_SET(x,s1,s2,…)。

5.4 时间和日期函数

  • 获取当前日期的函数CURDATE(),CURRENT_DATE(),CURRENT_DATE()+0可以将当前日期值转换为数值型。
  • 获取当前时间的函数CURTIME(),CURRENT_TIME(),CURRENT_TIME()+0可以将当前日期值转换为数值型。
  • 获取当前时间和日期的函数CURRENT_TIMESTAMP(),LOCALTIME(),NOW(),SYSDATE()
  • UNIX时间戳函数
    UNIX_TIMESTAMP(data)若无参数调用,则返回一个Unix时间戳(‘1970-01-01 00:00:00’ GMT之后的秒数)作为无符号整数。若用date来调用UNIX_TIMESTAMP(),它会将参数值以‘1970-01-01 00:00:00’GMT后的秒数的形式返回。date可以是一个DATE字符串,DATETIME字符串,TIMESTAMP或一个当地时间的YYMMDD或YYYYMMDD格式的数字。
    FROM_UNIXTIME(data)函数把UNIX时间戳转换为普通格式的时间,与UNIX_TIMESTAMP(data)函数互为反函数。
  • 返回UTC日期的函数UTC_DATE()。
  • 返回UTC时间的函数UTC_TIME()。
  • 获取月份的函数MONTH(data),MONTHNAME(date)
  • 获取星期的函数DAYNAME(d),DAYOFWEEK(d)和WEEKDAY(d)
  • 获取星期数的函数WEEK(d),WEEKOFYEAR(d)
  • 获取天数的函数DAYOFYEAR(d)和DAYOFMONTH(d)
  • 获取年份,季度,小时,分钟和秒钟函数
    YEAR(date),‘00~69’转换为‘2000 ~ 2069’,‘70 ~ 99’转换为‘1970 ~ 1999’
    QUARTER(date)
    MINUTE(time)
    SECOND(time)
  • 获取日期的指定值的函数EXTRACT(type FROM date)
  • 时间和秒钟转换函数
    TIME_TO_SEC(time)返回已转化为秒的time参数。转换公式为:小时3600+分钟60+秒
    SEC_TO_TIME(seconds)返回被转化为小时,分钟和秒数的seconds参数值
  • 计算日期和时间函数
    DATE_ADD(),ADDDATE(),DATE_SUB(),SUBDATE(),ADDTIME(),SUBTIME(),DATE_DIFF()
  • 将日期和时间格式化函数DATE_FORMAT(date,format)

5.5 条件判断函数

条件判断函数也称为控制流程函数,根据满足的条件的不同,执行相应的流程。

  • IF(expr,v1,v2)函数
  • IFNULL(v1,v2)函数
  • CASE函数

5.6 系统信息函数

  • 获取MySQL版本号,连接数和数据库名的函数
    VERSION()
    CONNECTION_ID()
    SHOW PROCESSLIST或SHOW FULL PROCESSLIST:查看有哪些线程,显示信息更全
    DATABASE()和SCHEMA():查询当前数据库
  • 获取用户名的函数
    USER(),CURRENT_USER,CURRENT_USER(),SYSTEM_USER(),SESSION_USER()
  • 获取字符串的字符集和排序方式的函数
    CHARSET(str)
    COLLATION(str)
  • 获取最后一个自动生成ID值的函数
    LAST_INSERT_ID()

5.7 加密函数

  • 加密函数PASSWORD(str)
  • 加密函数MD5(str)
  • 加密函数ENCODE(str,pswd_str)
  • 解密函数DECODE(crypt_str,pswd_str)

5.8 其他函数

  • 格式化函数FORMAT(x,n)
  • 不同进制的数字进行转换的函数CONV(N,from_base,to_base)
  • IP地址与数字互相转换的函数INET_ATON(expr),INET_NTOA(expr)
  • 加锁函数和解锁函数
    GET_LOCK(str,timeout)
    RELEASE_LOCK(str)
    IS_FREE_LOCAK(str)
    IS_USEE_LOCAK(str)
  • 重复执行指定操作的函数
    BENCHMARK(count,expr)
  • 改变字符集的函数
    CONVERT(…USING…)
  • 改变数据类型的函数
    CAST(x,AS type)
    CONVERT(x,type)

六、数据的增删改查

6.1 基本查询数据

MySQL从数据表中查询数据的基本语句为SELECT语句。SELECT语句的基本格式是:
SELECT {*|<字段列表>}
[FROM<表1>,<表2>…
WHERE<表达式>
GROUP BY<>
HAVING
ORDER BY<>
LIMIT
]

详细解释:

  • {*|<字段列表>}包含星号通配符选字段列表,表示查询的字段,其中字段列至少包含一个字段名称,如果要查询多个字段,多个字段之间用逗号隔开,最后一个字段后不要加逗号。
  • FROM<表1>,<表2>…,表1和表2表示查询数据的来源,可以是单个或者多个
  • WHERE子句是可选项,如果选择该项,将限定查询行必须满足的查询条件
  • GROUP BY<字段>,该子句告诉MySQL如何显示查询出来的数据,并按照指定的字段分组
  • [ORDER BY<字段>],该子句告诉MySQL按什么样的顺序显示查询出来的数据,可以进行的排序有:升序(ASC),降序(DESC)
  • [LIMIT],该子句告诉MySQL每次显示查询出来的数据条数。

6.2 单表查询数据

  • 查询所有字段
    SELECT * FROM 表名;
    SELECT 字段名2,字段名3,字段名4,…,字段名n FROM 表名;

  • 查询单个字段
    SELECT 字段名2 FROM 表名;

  • 查询指定记录(WHERE提供查询条件)
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 查询条件;

  • 查询IN或NOT IN范围内数据
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 字段名 IN();
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 字段名 NOT IN();

  • 带BETWEEN AND的范围查询
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 字段名 BETWEEN 开始值 AND 结束值;

  • 带LIKE的字段匹配查询(用%或_通配符)
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 字段名 LIKE ‘某些字符’;

  • 查询空值
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 字段名 IS NULL;

  • 带ADN的多条件查询
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 条件表达式1 AND 条件表达式2;

  • 带OR的多条件查询
    SELECT 字段名1,字段名2,…,字段名n FROM 表名 WHERE 条件表达式1 OR 条件表达式2;
    某些情况下OR操作符和IN操作符使用后的结果是一样的,他们可以实现相同的功能。但是使用IN操作符使得检索语句更加简洁明了,并且IN执行的速度要快于OR。
    OR可以和AND一起使用,但是在使用时要注意两者的优先级,由于AND的优先级高于OR,因此先对AND两边的操作数进行操作,再与OR中的操作数结合。

  • 查询结果不重复
    -SELECT DISTINCT(字段名) FROM 表名;

  • 对查询结果排序(升序ASC,降序DESC,默认是ASC,不用特意在后面标注)
    SELECT 字段名 FROM 表名 ORDER BY 字段名1,字段名2,字段名n;
    在对多列进行排序的时候,首先排序的第一列必须有相同的列值,才会对第二列进行排序。如果第一列数据中所有值都是唯一的,将不再对二列进行排序。同理后面列名也是一样的。

  • 分组查询
    [GROUP BY 字段][HAVING<条件表达式>]
    (1)GROUP BY关键字通常和聚合函数一起使用,MAX(),MIN(),COUNT(),SUM(),AVG()。
    (2)GROUP_CONCAT()函数可以将每个分组中的各个字符值显示出来。
    (3)HAVING关键字与WHERE关键字都是用来过滤数据的,两者的主要区别在于,HAVING在数据分组之后进行过滤来选择分组,而WHERE在分组之前用来选择记录。另外WHERE排除的记录不再包括在分组中。
    (4)在GROUP BY子句中使用WITH ROLLUP,可以在查询出的分组记录之后增加一条记录,该记录就是计算查询出的所有记录的总和,即统计记录数量。但是ROLLUP不可以与ORDER BY子句一起使用,两个是互斥的。
    (5)GROUP BY可以对多个字段进行分组,MySQL根据多字段的值来进行层次分组,分组层次是从左到右,即先按第一个字段分组,然后在第1个字段值相同的记录中,再根据第2个字段的值进行分组…依次类推。

  • LIMIT限制查询结果的数量
    LIMIT[位置偏移量,]行数
    第一个“位置偏移量”参数指示MySQL从哪一行开始显示,是一个可选参数,如果不指定“位置偏移量”,将会从表中的第一条记录开始(第一条记录的位置偏移量是0,第二条记录的位置偏移量是1…依次类推);第二个参数“行数”指示返回的记录条数。
    带一个参数的LIMIT指定从查询结果的首行开始,唯一的参数表示返回的行数,即“LIMIT n”与“LIMIT 0,n”等价。带两个参数的LIMIT可以返回从任何一个位置开始的指定的行数。

6.3 使用集合函数查询

  • COUNT()函数
    (1)COUNT(*)计算表中总的行数,不管某列有数值或者为空值。
    (2)COUNT(字段名)计算指定列下总的行数,计算时将忽略空值的行。
  • SUM()函数
    SUM()函数是求总和的函数,忽略列值为NULL的行。
  • AVG()函数
    AVG()函数通过计算返回的行数和每一行数据的和,求得指定列数据的平均值。
  • MAX()函数
    MAX()函数返回指定列中的最大值
  • MIN()函数
    MIN()返回查询列中的最小值。

6.4 连接查询

  • 内连接查询
    INNER JOIN

  • 外连接查询
    (1)LEFT JOIN(左连接):返回包括左表中的所有记录和右表中连接字段相等的记录。
    (2)RIGHT JOIN(右连接):返回包括右表中的所有记录和右表中连接字段相等的记录。

6.5 子查询

子查询指一个查询语句嵌套在另一个查询语句内部的查询。子查询可以添加到SELECT,UPDATE,DELETE语句中,而且可以进行多层嵌套。

  • 带ANY,SOME关键字的查询
    ANY关键字接在一个比较操作符的后面,表示若与子查询返回的任何值比较为TRUE,则返回TRUE。
    例如:SELECT num1 FROM tb1 WHERE num1 > ANY(SELECT num2 FROM tbl2)
    在子查询中,返回的是tbl2表的所有num2列结果,然后将tbl1中的num1列的值与之进行比较,只要大于num2列的任意一个数即为符合条件的结果。
  • 带ALL关键字的查询
    ALLA关键字与ANY和SOME不同,使用ALL时需要同时满足所有内层查询的条件。
    例如:SELECT num1 FROM tb1 WHERE num1 > ALL(SELECT num2 FROM tbl2)
    在子查询中,返回的是tbl2的所有num2列结果,然后将tbl1中的num1列的值与之进行比较,返回tbl1表中比tbl2表num2列所有值都大的值。
  • 带EXISTS关键字的子查询
    (1)EXISTS关键字后面的参数是一个任意的子查询,系统对子查询进行运算以判断它是否返回行,如果至少返回一行,那么EXISTS的结果为true,此时外层查询语句将进行查询,如果子查询没有返回任何行,那么EXISTS返回的结果是false,此时外层语句将不进行查询。
    (2)NOT EXISTS与EXISTS使用方法相同,返回的结果相反。子查询如果至少返回一行,那么NOT EXISTS的结果为false,此时外层查询语句将不进行查询;如果子查询没有返回任何行,那么NOT EXISTS返回的结果为true,此时外层语句将进行查询。
    (3)EXISTS和NOT EXISTS的结果只取决于是否会返回行,而不取决于这些行的内容,所以子查询输入列表通常是无关紧要的。
    举例:SELECT * FROM fruits WHERE EXISTS(SELECT s_name FROM suppliers WHERE s_id = 109)
    查询suppliers表中是否存在s_id=109的供应商,如果存在,则查询fruits表中的记录
  • 带IN关键字的子查询
    IN关键字进行子查询时,内层查询语句仅仅返回一个数据列,这个数据列里的值将提供给外层查询语句进行比较操作

提示:所有有关子查询的操作过程,先执行最内层子查询,再执行外层查询,最内层子查询的结果作为外部查询的比较条件。

6.6 合并查询结果

UNION或者UNION ALL
两者区别:UNION ALL 的功能是不删除重复行,加上ALL 关键字语句执行时所需要的资源少,所以尽可能的使用它,因此知道有重复行但是想保留这些行,确定查询结果中不会有重复数据或者不需要去掉重复数据的时候,应当使用UNION ALL 以提高查询效率。

6.7 为表和字段取别名

表名 [AS] 表别名
列名 [AS] 列别名

6.8 使用正则表达式查询

正则表达式常用字符匹配列表:

选项说明列子匹配值示例
^匹配文本的开始字符'^b’匹配以字母b开头的字符串book,big,banana,bike
$匹配文本的结束字符'st$'匹配以st结尾的字符串test,resist,persist
.匹配任何单个字符'b.t’匹配以任何b和t之间有一个字符bit,bat,but,bite
*匹配零个或多个在它前面的字符'f*n’匹配字符n前面有任意个字符ffn,fan,faan,abcn
+匹配前面的字符1次或多次'ba+'匹配以b开头后面紧跟至少有一个aba,bay,bare,battle
<字符串>匹配包含指定的字符串的文本‘fa’fan,afa,faad
[字符集合]匹配字符集中的任何一个字符'[xz]'匹配x或者zdizzy,zebra,x-ray,extra
[^]匹配不在括号中的任何字符[^abc]'匹配任何不包含a,b,c的字符串desk,fox,f8ke
字符串{n,}匹配前面的字符串至少n次b{2}匹配2个或更多的bbbb,bbbb,bbbbbbb
字符串{n,m}匹配前面的字符串至少n次,至多m次。如果n为0,此参数为可选参数b[2,4]匹配最少2个,最多4个bbb,bbb,bbbb

SQL举例:
(1)查询字符“^”匹配以特定字符串或者字符串开头的文本。
SELECT * FROM 表名 WHERE 列名 REGEXP ‘^b’;(查询以b开头的数据)

(2)查询“$”匹配以特定字符或者字符串结尾的文本。

SELECT * FROM 表名 WHERE 列名 REGEXP ‘y$’;(查询以y结尾的记录)

(3)用符号“.”来替代字符串中的任意一个字符
SELECT * FROM 表名 WHERE 列名 REGEXP ‘a.g’;
查询语句中“a.g”指定匹配字符中要有字母a和g,且两个字母之间包含单个字符,并不限定匹配的字符的位置和所在查询字符串的总长度。
(4)使用“”和“+”来匹配多个字符
星号“
”匹配前面的字符任意多次,包括0次。加好“+”匹配前面的字符至少一次。
SELECT * FROM 表名 WHERE 列名 REGEXP ‘^ba*’;(必须是以b开头,a可有可无,因为星号是包含零次的)
SELECT * FROM 表名 WHERE 列名 REGEXP ‘^ba+';(必须是以b开头,且a字母必须出现一次)
(5)匹配指定字符串
使用“|”匹配多个字符串
SELECT * FROM 表名 WHERE 列名 REGEXP 'on|ap
’;(查询包含字符串“on”或者“ap”的记录)
(6)匹配指定字符中的任意一个
使用方括号[]指定一个字符集合,只匹配其中任意一个字符,即为所查找的文本
SELECT * FROM 表名 WHERE 列名 REGEXP ‘[ot]';(查询字段中包含字母o或者t的记录)
匹配集合写法:例如:[1-9]匹配1到9之间的任意数字,[a-z]匹配a~z之间的任意字母
(7)匹配指定字符以外的字符
“[^字符集合]”匹配不在指定集合中的任何字符
SELECT * FROM 表名 WHERE 列名 REGEXP ‘[^a-p1-9]’;(匹配出字母a-p和数字1-9之外的记录)
(8)使用{n,}或者{n,m}来指定字符串连续出现的次数
子字符串{n,}表示至少匹配n次前面的字符:“字符串{n,m}”表示匹配前面的字符串不少于n次,不多于m次。例如,a{2,}表示字母a连续出现至少2次,也可以大于2次;a{2,4}表示字母a连续出现最少2次,最多不超过4次。
SELECT * FROM 表名 WHERE 列名 REGEXP 'x{2,}
’;

6.9 数据的新增

(1)语法:INSERT INTO 表名(列名)VALUES (数值);或者INSERT INTO 表名 VALUES (数值);保证数值插入顺序和字段名一致,就不用再写字段名。
(2)INSERT INTO person(name,age,info) VALUES(‘HSUDH’,28,‘SHUAHD’),(‘HSUQW’,21,‘AHD’),(‘H’,47,‘WEQ’)

INSERT INTO person(name,age,info) VALUES(‘HSUDH’,28,‘SHUAHD’);INSERT INTO person(name,age,info) (‘HSUQW’,21,‘AHD’)效率更高。因为MySQL执行单条INSERT语句插入多行数据,比使用多条INSERT语句快。所以在插入多条记录时,最好选择使用单条INSERT语句的方式插入。

6.10 数据的修改

(1)语法:UPDATE 表名 SET 列名1=value1,列名2=value2,列名3=value3,…,列名n=valuen WHERE (查询条件)

6.11 数据的删除

(1)语法:DELETE FROM 表名 [WHERE (查询条件)]
(2)如果想删除表中的所有记录,还可以使用TRUNCATE TABLE语句,TRUNCATE将直接删除原来的表并重新创建一个表,其语法结构为TRUNCATE TABLE table_name。TRUNCATE 直接删除而不是删除记录,因此执行速度比DELETE快。

七、索引

7.1 索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构,使用索引可提高数据库中特定数据的查询速度。

7.1.1 索引的含义和特点

索引是一个单独的,存储在磁盘上的数据库结构,他们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行,所有MySQL列类型都可以被索引,对相关列使用索引是提高查询操作速度的最佳途径。
例如:数据库中有2万条记录,现在要执行这样一个查询:SELECT * FROM 表名 WHERE num = 10000。如果没有索引,必须遍历整个表,直到num等于10000的这一行被找到为止;如果在num列上创建索引,MySQL不需要任何扫描,直接在索引里面找10000,就可以得知这一行的位置。
索引是在存储引擎中实现的,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。根据存储引擎定义每个表的最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的限制。MySQL中索引的存储类型有两种:BTREE和HASH,具体和表的存储引擎相关;MyISAM和InnoDB存储引擎只支持BTREE索引;MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

索引的优点:

  • 通过创建唯一索引,可以保证数据库表中每一行数据的唯一性。
  • 可以大大加快数据的查询速度,这也是创建索引的最主要原因。
  • 在实现数据的参考完整性方面,可以加速表和表之间的连接。
  • 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排除的时间。

索引的缺点:

  • 创建索引和维护索引好费时间,并且随着数据量的增加所耗费的时间也会增加。
  • 缩阴需要站磁盘空间,除了数据表占数据空间之外,每个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加,删除和修改的时候,索引也要动态的维护,这样就降低了数据的维护速度。

7.1.2 索引的分类

(1)普通索引和唯一索引
普通索引是MySQL中基本索引类型,允许在定义索引的列中插入重复值和空值。
唯一索引,索引列的值必须唯一,但允许空值。如果是组合索引,则列值组合必须唯一。
主键索引是一种特殊的唯一索引,不允许有空值。

(2)单列索引和组合索引
单列索引即一个索引只包含单个列,一个表可以有多个单列索引。
组合索引是指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会使用、使用组合索引时遵循最左前缀集合。

(3)全文索引
全文索引类型为FULLTEXT,在定义索引的的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。MySQL中只有MyISAM存储引擎支持全文索引。

(4)空间索引
空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有4种,分别是:GEOMTRY,POINT,LINESTRING,POLYGON。MySQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类似的语法创建空间索引。创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MyISAM的表中创建。

7.1.3 索引的设计原则

  • 索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间,而且会有影响INSERT,DELETE,UPDATE等语句的性能,因为当表中的数据更改的同时,索引也会进行调整和更新。
  • 避免对经常更新的表进行过多的索引,并且索引中的列尽可能少。而堆经常用于查询的字段应该创建索引,但要避免添加不必要的字段。
  • 数据量小的表最好不要使用索引,由于数据少,查询花费的时间可能比遍历索引的时间还要短,索引可能不会产生优化效果。
  • 在条件表达式中经常用到的不同值比较多的列上建立索引,在不同值少的列上不要建立索引。比如学生表的性别字段。
  • 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引需能确保定义的列的数据完整性,以提高查询速度。
  • 在频繁进行排序或分组的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引。

7.2 创建索引

(1)语法:
CREATE TABLE table_name[col_name_data_type]
[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length]) [ASC|DESC]

UNIQUE,FULLTEXT,SPATIAL为可选参数,分别表示唯一索引,全文索引和空间索引;INDEX和KEY为同义词,两者作用相同,用来指定创建索引;col_name为需要创建索引的字段列,index_name指定索引的名称,为可选参数,如果不指定,MySQL默认col_name为索引值,length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度;ASC或DESC指定升序户降序的索引值存储。

提示:组合索引遵从“最左前缀”,利用索引中最左边的列集来匹配行,这样的列表称为最左前缀。例如这有由id,name,age3个字段组合的索引,索引行中按id/name/age的顺序存放,索引可以由下面字段组合:(id,name,age),(id,name)或id。如果列不构成索引最左面的前缀,MySQL不能使用局部索引,如age或者(name,age)组合则不能使用索引。

(2)查看某一个查询语句是否使用索引
explain SELECT查询语句\G;

(3)查看整个表的索引结构
SHOW INDEX FROM 表名\G

7.3 删除索引

ALTER TABLE 表名 DROP INDEX 索引名;
DROP INDEX 索引名 ON 表名;
提示:添加了AUTO_INCREMENT约束字段的唯一索引不能被删除。


http://www.ppmy.cn/server/144403.html

相关文章

day03(单片机高级)RTOS

目录 RTOS(实时操作系统) 裸机开发模式 轮询方式 前后台&#xff08;中断方式&#xff09; 改进&#xff08;前后台&#xff08;中断&#xff09;&#xff09;定时器 裸机进一步优化 裸机的其他问题 RTOS的概念 什么是RTOS 为什么要使用 RTOS RTOS的应用场景 RTOS的…

【Linux学习】【Ubuntu入门】1-6 ubuntu文件系统结构

/ &#xff1a;根目录 /bin&#xff1a;存放二进制可执行文件&#xff0c;这些命令在单用户模式下也能够使用。可以被root 和一般的账号使用。 /boot&#xff1a;Ubuntu 内核和启动文件&#xff0c;比如vmlinuz-xxxx。gurb引导装载程序。 /dev&#xff1a;设备驱动文件 /et…

Java学习笔记--数组常见算法:数组翻转,冒泡排序,二分查找

目录 一&#xff0c;数组翻转 二&#xff0c;冒泡排序 三&#xff0c;二分查找&#xff08;一尺之锤&#xff0c;日取其半&#xff0c;万世不竭&#xff09; 一&#xff0c;数组翻转 1.概述:数组对称索引位置上的元素互换&#xff0c;最大值数组序号是数组长度减一 创建跳板…

PHP 数组

数组能存放多个值&#xff0c;需要在array()这个函数里存放&#xff0c;如果需要获取并使用&#xff0c;还需要用一个变量来存储。 <?php $color array("喜欢","黄色","跑车"); echo 我 . $color[0] . $color[1] . 的 . $color[2]; …

深入理解Rust的模式匹配

文章目录 基础用法匹配各种类型模式匹配赋值提取绑定值解构Option 高级用法添加匹配守卫范围匹配切片模式变量绑定使用..进行模糊匹配使用_进行值忽略固定某个字段的匹配多分支匹配 其它匹配方式if let匹配方式使用matches!宏进行匹配 在Rust编程语言中&#xff0c;模式匹配是一…

1、HCIP之RSTP协议与STP相关安全配置

目录 RSTP—快速生成树协议 STP STP的缺点&#xff1a; STP的选举&#xff08;Listening状态中&#xff09;&#xff1a; RSTP P/A&#xff08;提议/同意&#xff09;机制 同步机制&#xff1a; 边缘端口的配置&#xff1a; RSTP的端口角色划分&#xff1a; ensp模拟…

C++设计模式行为模式———中介者模式

文章目录 一、引言二、中介者模式三、总结 一、引言 中介者模式是一种行为设计模式&#xff0c; 能让你减少对象之间混乱无序的依赖关系。 该模式会限制对象之间的直接交互&#xff0c; 迫使它们通过一个中介者对象进行合作。 中介者模式可以减少对象之间混乱无序的依赖关系&…

蓝桥杯模拟

【问题描述】 如果一个数 p 是个质数&#xff0c;同时又是整数 a 的约数&#xff0c;则 p 称为 a 的一个质因数。 请问 2024 有多少个质因数。 【答案提交】 这是一道结果填空的题&#xff0c;你只需要算出结果后提交即可。本题的结果为一个整数&#xff0c;在提交答案时只…