发表于 2025-02-06 更新于 2024-06-12

MySQL基础

MySQL概述

数据库（DataBase）：存储数据的仓库，数据是有组织的进行储存
数据库管理系统：操纵和管理数据库的大型软件
SQL（Structured Query Language）：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。
关系型数据库：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

1.使用表储存数据，格式统一，便于维护；

2.使用SQL语言操作，标准统一，使用方便。

SQL

SQL通用语法

SQL语句可以单行或多行书写，以分号结尾。
SQL语句可以使用空格/缩进来增强语句的可读性。
MySQL数据库的SQL不区分大小写，关键字建议用大写。
注释：

1.单行注释：’–’或者’#’

2.多行注释：/* */

SQL分类

DDL：数据定义(D:definition)语言，用来定义数据库对象（数据库，表，字段）
DML：数据操作(M:manipulation)语言，用来对数据库表中的数据进行增删改。
DQL：数据查询(Q:query)语言，用来查询数据库中表的记录。

DCL：数据控制(C:control)语言，用来创建数据库用户，控制数据库的访问权限。

DDL

数据库操作

查询

查询所有数据库
1
SHOW DATABASES;
查询当前数据库
1
SELECT DATABASE();
如果执行语句太多找不到当前的数据库了，我们可以用这条语句。
创建

1	CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排列顺序];

其中方括号中的内容能省略。第一个方括号中代表判断，如果不存在名为 XXX的数据库，我们就建立这个据库，否则不建立，防止报错。第二个方括号表示字符集，就是数据库内数据的编码形式。

删除
1
DROP DATABASE [IF EXISTS] 数据库名;
同理上面，如果存在就删除，否则不删除。
使用
1
USE 数据库名;

表操作

查询

查询当前数据库所有表
1
SHOW TABLES;
查询表结构
1
DESC 表名;
查询指定表的建表语句
1
SHOW CREATE TABLE 表名;

创建

CREATE TABLE 表名(
	字段1 字段1类型[COMMENT 字段1注释],
	字段2 字段2类型[COMMENT 字段2注释],
	字段3 字段3类型[COMMENT 字段3注释]
	……
)[COMMENT 表注释];

注意，最后一个字段后面没有括号。方括号中的内容能省略。

示例：

mysql> create table tb(
    -> id int comment '编号',
    -> name varchar(50) comment '姓名',
    -> age int comment '年龄',
    -> gender varchar(1) comment '性别'
    -> )comment '用户表';

这里我们定义了一个名为’tb’的表，分别定义了四个字段。在SQL中，字符串的关键字为varchar。

我们再用SHOW TABLES;语句，输出如下结果：

mysql> desc tb;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int         | YES  |     | NULL    |       |
| name   | varchar(50) | YES  |     | NULL    |       |
| age    | int         | YES  |     | NULL    |       |
| gender | varchar(1)  | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

但是并没有我们一开始写的注释语句。因此我们还需要用SHOW CREATE TABLE 表名;来展示完整信息：

| tb    | CREATE TABLE `tb` (
  `id` int DEFAULT NULL COMMENT '编号',
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int DEFAULT NULL COMMENT '年龄',
  `gender` varchar(1) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |

可以看到语句的末端ENGINE字样，这里我们将会在后续章节提到。包含表的字符集，排列顺序等等信息。

数据类型

SQL中的数据类型主要分为三类：数值类型、字符串类型、日期时间类型。

1.数值类型
- 整数类型
  - 迷你整型：tinyint，使用1个字节存储整数，最多存储256个整数（-128~127）
  - 短整型：smallint，使用2个字节存储整数；
  - 中整型：mediumint，使用3个字节存储整数；
  - 标准整型：int，使用4个字节存储整数；
  - 大整型：bigint，使用8个字节存储。
  对于无符号的数值需要用unsigned来修饰整形。
- 小数类型
  - 单精度：float，使用4个字节存储，精度范围为6-7位有效数字；
  - 双精度：double，使用8个字节存储，精度范围为14-15位有效数字。
  - 定点型：decimal，能够保证精度的小数。定点数的储存模式不是固定长度，所以数据越大占用的储存空间越长。规定每9个数字使用4个字节存储。
    
    定点型可以指定整数部分长度和小数部分长度。比如decimal(4,2)表示数字部分长为4，小数部分长为2，比如99.99就是符合上面描述的小数。
    
    对于上面的例子，特别的：如果小数部分长度小于2，那么就会自动补零，比如99.1就是99.10；如果小数部分长度大于2，将四舍五入截断处理，99.123就是99.12.
2.字符串类型
- 定长型：char，指定固定长度的储存空间储存字符串，定长里储存的数据不能超过指定长度，但是能小于它。
  
  定长的访问效率较高，但是空间利用率较低。比如我想指定一个大于六位但小于十位的密码字符串，对于定长型我们只能设置长度为10的char，按如果搜友用户都输入六位字符串，但是他们的储存长度依旧为10，这样就造成了空间浪费。下面我们引入变长型。
- 变长型：varchar，根据实际储存的数据变化储存空间，长度最大值理论上为65535.此外变长需要额外产生1-2个字节，用来记录实际数据的长度：若数据长度小于256个，多1个字节；若数据长度大于256个，多2个字节。
  
  变长字符串在读取时需要进行长度计算，所以访问效率没有定长高。但是他能更好利用储存空间。
- 文本字符串：text/blob，专门用来储存较长的文本。文本字符串通常在超过255个字符时使用。
  - text：普通字符
    - tinytext：迷你文本，不超过2 ^ 8 -1个字符
    - text：普通文本，不超过 2 ^ 16 - 1个字符
    - mediumtext：中型文本，不超过 2 ^ 24 - 1 个字符
    - longtext：长文本，不超过 2 ^ 32 - 1 个字符（4G）
  - blob：二进制字符（与text类似）
    - tinyblob
    - blob
    - mediumblob
    - longblob
  文本字符串会自动根据文本长度选择适合的具体类型，一般在文本超过255个字符时，都会使用text.
3.日期时间类型
- 年：year，使用一个字节储存年份，year能够表示的年份范围是1901-2155年（256年）
- 时间戳：timestamp，基于格林威治时间的时间记录，使用四个字节储存，表示范围是1971年1月1日0时0分0秒-2155年12月31日23是59分59秒。
  
  格式：YYYY-MM-DD HH:II::SS 或者 YYYY-MM-DD HH:II::SS
- 日期：date，使用三个字节储存数据，表示范围为1001-01-01~9999-12-31.
  
  格式：YYYY-MM-DD
- 日期时间：datetime，用来综合储存日期和时间，使用八个字节储存，表示范围为1000-01-01 00:00:00 到9999-12-31 23:59:59
  
  格式：YYYY-MM-DD HH:II:SS
- 时间：time，表示范围为 -838:59:59 - 838:59:59
  
  格式：HH:II:SS
- 修改
  
  添加字段
  1
  ALTER TABLE 表名 ADD 字段名类型 [COMMENT 注释];
  修改字段名和字段类型
  1
  ALTER TABLE 表名 CHANGE 旧字段名新字段名类型 [COMMENT 注释];
  删除字段
  1
  ALTER TABLE 表名 DROP 字段名;
  修改表名
  1
  ALTER TABLE 表名 RENAME TO 新表名;
  删除表
  1
  DROP TABLE [IF EXISTS] 表名;
  删除指定表，并重新创建表
  1
  TRUNCATE TABLE 表名;
  删除后该表所有的数据都没了，然后又创建了一张名字一样的表。
DML
添加数据
- 给指定字段添加数据
  1
  INSERT INTO 表名(字段名1,字段名2,....)VALUES(值1,值2,....);
- 给全部字段添加数据
  1
  INSERT INTO 表名 VALUES(值1,值2,....);
- 批量添加数据
  1
  2
  INSERT INTO 表名(字段名1,字段名2,....)VALUES(值1,值2,....)(值1,值2,....)...(值1,值2,....);
  INSERT INTO 表名 VALUES(值1,值2,....)(值1,值2,....)...(值1,值2,....);
  插入数据时，指定的字段顺序要与值的顺序是一一对应的。
  
  字符串和日期型数据应包含在引号中。
删除数据
1
DELETE FROM 表名 [WHERE 条件];
如果不加条件，就会删除整张表的所有数据。

delete语句不能删除某一字段的值。

修改数据
1
UPDATE 表名 SET 字段名1=值1,字段名2=值2,....[WHERE 条件];
如果不加条件，那么就会修改所有数据。

DQL
- 基本查询
  - 查询多个字段
  1
  2
  SELECT 字段1,字段2,字段3...FROM 表名;
  SELECT * FROM 表名;
  在开发中，尽量不要写*，因为不直观，而且效率低。
  - 设置别名
  1
  SELECT 字段1[AS 别名1],[AS 别名2]...FROM 表名;
  注：AS可以省略。
  - 去除重复记录
  1
  SELECT DISTINCT 字段列表 FROM 表名;
- 条件查询
  1
  SELECT 字段列表 FROM 表名 WHERE 条件列表;
  其中条件列表中的大部分比较运算符与我们学习过的语言相同，这里只罗出不同的比较运算符。
  - BETWEEN…AND… 在某个范围之内（含最小、最大值）
    
    最小值在前，最大值在后，写反了的话就查不到数据了。
  - IN(…) 在in之后列表中的值，多选一。
    
    比如 age in(10,20,30)，意思是只要年龄满足其中一个就可以。
  - LIKE 占位符模糊匹配（’_’匹配单个字符，’%’匹配任意字符）
    
    比如 like ‘__’，有两个下划线，说明只要是两个字符的都满足条件。
    
    比如 like ‘%a’，表示前面有多少个字符都无所谓，只要最后一个字符是a就满足。
  - <> 不等于
- 聚合函数
  
  将一列数据作为一个整体，纵向计算。
  
  常见聚合函数：
  - count：统计数量
  - max：最大值
  - min：最小值
  - avg：平均值
  - sum：求和
  1
  SELECT 聚合函数(字段列表) FROM 表名;
  注意：null值是不参与聚合函数的计算的。
- 分组查询
  1
  SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
  WHERE和HAVING区别
  
  1.执行时机不同：where是分组之前进行过滤，如果不满足where条件，不参与分组；而having是分组之后对结果进行过滤。
  
  2.判断条件不同：where不能对聚合函数进行判断，而having可以。
  
  来看下面一条代码
  1
  select gender,count(*) from table group by gender;
  从table表中按照性别进行分组，字段列表为全部字段，统计男性和女性数量，同时我们也要在字段列表中加上一个gender，不然不知道分组统计的两个数量到底哪个是男哪个是女。
  1
  select gender,avg(age) from table group by gender;
  同理，我们按照性别划分，统计男性和女性的平均年龄，用到avg聚合函数。
  1
  select workaddress,count(*) address_count from table where age<45 group by workaddress having address_count>=3;
  这段代码较为复杂，我们来仔细分析以下。
  
  首先where是分组前的判断条件，只有年龄小于45岁才能进入分组。
  
  然后依据workaddress（地址）划分，最终我们得到不同地区下的人员的数量统计。注意后面的having是分组后的过滤条件，意思是人员数要大于等于3可以。
  
  其中address_count是count(*)的别名。
  
  注意：
  - 执行顺序：where>聚合函数>having
  - 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。
- 排序查询
  1
  SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2;
  排序方式
  - ASC：升序（默认）
  - DESC：降序
  如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。
- 分页查询
  1
  SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
  注意
  - 起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数。
  - 分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中的是LIMIT
  - 如果查询的是第一页数据，起始索引可以省略，直接简写为limit10.
1
2
3
select * from table limit 0,10;
select * from table limit 10;
select * from table limit 10,10;
对于上面三条代码，第一条表示查询第一页员工数据，每页展示10条数据，所以起始索引为（1-1）*10=0，当然第一页可以省略，直接简写为limit10.

第三条为从第二页查询，每页展示10条，起始索引为（2-1）*10=10.

DCL

MySQL基础

MySQL概述

SQL

SQL通用语法

SQL分类

DDL

数据库操作

表操作

DML

添加数据

删除数据

修改数据

DQL

DCL