MySQL 基础

2024-11-09 22:52:36

MySQL 基础

SQL 语法

市面上有很多关系型数据库，不管使用哪一个，都是使用 SQL 语言来进行统一操作，SQL 语言是操作关系型数据库的统一标准。SQL 全称 Structured Query Language，结构化查询语言。

SQL 语句有以下几个通用的语法规则：

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

SQL 语句根据其功能主要分为四类：DDL、DML、DQL、DCL

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

DDL

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象(数据库，表，字段)。

数据库操作

连接到 MySQL 数据库以后，一般会先查看当前 MySQL 中的数据库有哪些，是否有需要操作的数据库，操作指令如下

SHOW DATABASES;

我们可以创建数据库，指令如下：

CREATE DATABASE 数据库名;

如果新创建的数据库名已经存在，创建会失败，因此可以使用 IF NOT EXISTS 进行创建来避免失败的出现

CREATE DATABASE IF NOT EXISTS 数据库名;

数据库在创建时可以设置字符集，如果使用 utf8，则字符只能使用三个字节表示，而 MySQL 中有些字符需要四个字节，因此可以使用 utf8mb4（默认字符集），指令如下：

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

当我删除不存在的数据库会失败，因此在删除数据库一般使用如下的指令

DROP DATABASE [IF EXISTS] 数据库名;

切换到创建好的数据库，指令如下：

USE 数据库名;

如果我们忘记当前所使用的数据库是哪一个，可以使用如下指令来获取当前所使用的数据库名：

SELECT DATABASE();

表操作

切换到指定的数据库以后，可以查看此数据库中的所有数据表，命令如下：

SHOW TABLES;

创建一个表的命令如下：

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

比如创建下面结构的一张表，SQL 语句为

一个数据库是会存在很多的表，查询指定表结构使用如下命令：

DESC 表名;

不仅如此，还可以查找指定表的建表语句，通过这个语句查看的建表语句，有部分参数我们在创建表的时候，并未指定也会查询到，因为这部分是数据库的默认值，如存储引擎，字符集等

SHOW CREATE TABLE 表名;

修改已创建表的表名，命令如下：

ALTER TABLE 表名 RENAME TO 新表名;

删除已创建的表与删除已创建的数据库类似，命令如下：

DROP TABLE [IF EXISTS] 表名;

上面删除表的方式会将表和数据一并删除，如果只想删除表中的数据而不删除表，则使用下面的命令：

TRUNCATE TABLE 表名;

数据类型

上面在创建一个表的时候，需要标明每个字段的类型，MySQL 数据库中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型：

数值类型
- TINYINT：小整数值，大小 1 字节，有符号范围是（-128，127），无符号范围是（0，255）
- SMALLINT：大整数值，大小 2 字节，有符号范围是（-32768，32767），无符号范围是（0，65535）
- MEDIUMINT：大整数值，大小 3 字节，有符号范围是（-8388608，8388607），无符号范围是（0，16777215）
- INT/INTEGER：大整数值，大小 4 字节，有符号范围是（-2147483648，2147483647），无符号范围是（0，4294967295）
- BIGINT：极大整数值，大小 8 字节，有符号范围是（-2^ 63，2^ 63 - 1），无符号范围是（0，2^64-1）
- FLOAT：单精度浮点数值，大小 4 字节，有符号范围是（-3.402823466 E+38，3.402823466351 E+38），无符号范围是 0 和 (1.175494351 E-38，3.402823466 E+38)
- DOUBLE：双精度浮点数值，大小 8 字节，有符号范围是（-1.7976931348623157E+308，1.7976931348623157E+308），无符号范围是 0 和 (2.2250738585072014E-308，1.7976931348623157E+308)
- DECIMAL：小数值（精确定点数）有符号范围依赖于M(精度)和D(标度)的值，无符号范围依赖于M(精度)和D(标度)的值
字符串类型
- CHAR：定长字符串（需要指定长度），大小是 0~255 字节
- VACHAR：变长字符串（需要指定长度），大小是 0~65535 字节
- TINYBLOB：不超过 255 个字符的二进制数据，大小是 0~255 字节
- TINYTEXT：短文本字符串，大小是 0~255 字节
- BLOB：二进制形式的长文本数据，大小是 0~65535 字节
- TEXT：长文本数据，大小是 0~65535 字节
- MEDIUMBLOB：二进制形式的中等长度文本数据，大小是 0~16777215 字节
- MEDIUMTEXT：中等长度文本数据，大小是 0~16777215 字节
- LONGBLOB：二进制形式的极大文本数据，大小是 0~4294967295 字节
- LONGTEXT：极大文本数据，大小是 0~4294967295 字节
日期时间类型
- DATE：日期值，格式为 YYYY-MM-DD，大小是 3 字节，范围为 1000-01-01 至 9999-12-31
- TIME：时间值或持续时间，格式为 HH:MM:SS，大小是 3 字节，范围为 -838:59:59 至 838:59:59
- YEAR：年份值，格式为 YYYY，大小是 1 字节，范围为 1901 至 2155
- DATETIME：混合日期和时间值，格式为 YYYY-MM-DD HH:MM:SS，大小是 8 字节，范围为 1000-01-01 00:00:00 至 9999-12-31 23:59:59
- TIMESTAMP：混合日期和时间值，时间戳，格式为 YYYY-MM-DD HH:MM:SS，大小是 4 字节，范围为 1970-01-01 00:00:01 至 2038-01-19 03:14:07

CHAR 与 VACHAR 都可以描述字符串，CHAR 是定长字符串，指定长度多长，就占用多少个字符，和
字段值的长度无关。而 VACHAR 是变长字符串，指定的长度为最大占用长度。相对来说，CHAR 的性
能会更高些。

了解了 MySQL 的基本类型，设计一张员工信息表，要求如下：

编号（纯数字）
员工工号 (字符串类型，长10位)
员工姓名（字符串类型，长度不超过10位）
性别（男/女，存储一个汉字）
年龄（正常人年龄，不可能存储负数）
身份证号（二代身份证号均为18位，身份证中有X这样的字符）
入职时间（取值年月日即可）

表字段操作

如果已创建的表某个字段的类型有误，或者字段名有误，我们都可以进行修改，在表创建完以后还可以添加和删除字段（字段是一条记录）。

添加字段的命令如下：

ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];

如在上面一创建的 emp 表中添加字段 nickname 表示昵称，类型为 varchar(20)

删除字段的命令如下：

ALTER TABLE 表名 DROP 字段;

修改数据类型的命令如下：

ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);

修改字段名和字段类型命令如下：

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 数据类型(长度) [COMMENT 注释] [约束];

DML

DML 全称 Data Manipulation Language（数据操作语言），用来对数据库中表的数据记录进行增删改操作。主要有三个操作：添加数据、修改数据以及删除数据。

添加数据

给指定字段添加数据的语法如下：

INSERT INTO 表名 (字段1, 字段2, 字段3, ...) VALUES (值1, 值2, 值3, ...)

如果是给所有字段添加数据，可以使用如下的语法（每个值要与字段一一对应）：

INSERT INTO 表名 VALUES (值1, 值2, 值3, ...)

一个一个添加数据太过于繁琐，可以一次批量添加数据，语法如下：

INSERT INTO 表名 (字段1, 字段2, 字段3, ...) VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), ... -- 批量添加指定字段的数据
INSERT INTO 表名 VALUES (值1, 值2, 值3, ...), (值1, 值2, 值3, ...), ... -- 批量添加全部字段的数据

注意事项：

插入数据时，指定的字段顺序需要与值的顺序时一一对应的

字符串和日期型数据应该包含在引号中

插入的数据大小，应该在字段的规定范围内

修改数据

修改数据的具体语法如下：

UPDATE 表名 SET 字段1=值1, 字段2=值2, ... [WHERE 条件];

注意事项：修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据

删除数据的语法如下：

DELETE FROM 表名 [WHERE 条件];

注意事项：

DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据

DELETE 语句不能删除某一个字段的值（可以使用 UPDATE，将该字段的值置为 NULL 即可）

DQL

DQL 全称 Data Query Language（数据查询语言），用来查询数据表中的记录。查询的关键字是 SELECT。

在一个正常的业务系统中，查询操作的频次是要远高于增删改的，当我们去访问企业官网、电商网站，在这些网站中我们所看到的数据，实际都是需要从数据库中查询并展示的。而且在查询的过程中，可能还会涉及到条件、排序、分页等操作。

查询的语法结构如下所示，根据语法结构逐步理解查询语句

在学习数据查询操作之前下做如下的准备工作：

drop table if exists emp;

create table emp(
  id int comment '编号',
  workno varchar(10) comment '工号',
  name varchar(10) comment '姓名',
  gender char(1) comment '性别',
  age tinyint unsigned comment '年龄',
  idcard char(18) comment '身份证号',
  workaddress varchar(50) comment '工作地址',
  entrydate date comment '入职时间'
) comment '员工表';

INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate) VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');

基础查询

基础查询是指查询整个表指定字段的所有数据或所有字段的所有数据，语法如下：

SELECT 字段1, 字段2, ... FROM 表名;	-- 查询指定字段的所有数据
SELECT * FROM 表名;	-- 查询所有字段的所有数据

如果查询出来的字段名不好理解，可以为其设置别名，语法如下：

SELECT 字段1 AS 别名1, 字段2 AS 别名2, ... FROM 表名;	-- 查询指定字段的所有数据
SELECT 字段1 别名1, 字段2 别名2, ... FROM 表名;	-- 查询指定字段的所有数据

查询出来的数据可能会存在很多重复的内容，可以使用 DISTINCT‘ 关键字来修饰字段去除重复，语法如下：

SELECT DISTINCT 字段列表 FROM 表名;

条件查询

条件查询是指在基础查询的基础上进行一些条件过滤，把符合的数据记录查询出来，语法如下：

SELECT 字段列表 FROM 表名 WHERE 条件列表;

MySQL 中常用的比较运算符如下图所示：

常用的逻辑运算符如下图所示：

聚合函数

聚合函数是将字段作为计算对象，对该字段的所有数据进行计算，常见的聚合函数如下：

基本使用语法如下：

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

注意：NULL 是不参与所有聚合函数运算符的。

分组查询

前面查询获取的数据是指定条件下的数据记录，为了方便统计，可以对这些记录进行分组，使用 GROUP BY 关键字。

基本语法如下：

SELECT 字段列表 FROM 表名 [WHERE 条件列表] GROUP BY 分组字段名1, 分组字段名2, ... [HAVING 分组后过滤条件];

WHERE 和 HAVING 的区别

执行时机不同：WHERE 是在分组之前进行过滤，不满足 WHERE 条件是不会参与分组；而 HAVING 是分组之后对结果进行过滤

判断条件不同：WHERE 不能对聚合函数进行判断，而 HAVING 可以

执行顺序：WHERE > 聚合函数 > HAVING

分组之后，查询的字段一般为分组字段和聚合函数，查询其他字段无任何意义，执行查询其他字段的语法可能还会报错。

排序查询

排序在日常开发中是非常常见的一个操作，有升序排序，也有降序排序。基本语法如下：

SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2, ...;

排序方式只有两种：升序（ASC 默认）和降序（DESC）

分页查询

分页查询是指在前面查询的结果中，选取指定范围的数据，起始索引从 0 开始，使用 LIMIT 关键字，语法如下：

SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

执行顺序

上述标题的顺序是查询语句的编写顺序，而查询语句的执行顺序略有不同，如下图所示

验证如下：

查询年龄大于 15 的员工姓名、年龄，并根据年龄进行升序排序

select name, age from emp where age > 15 order by age;

在查询时，我们给 emp 表起一个别名 e，然后在 select 和 where 中使用该别名

select e.name, e.age from emp e where e.age > 15 order by age;

执行上述 SQL 语句后，我们看到依然可以正常的查询到结果，此时就说明： from 先执行，然后 where 和 select 执行。那 where 和 select 到底哪个先执行呢?

我们可以给 select 后面的字段起别名，然后在 where 中使用这个别名，然后看看是否可以执行成功。

select e.name ename, e.age eage from emp e where eage> 15 order by age;

执行上述 SQL 出错了，由此我们可以得出结论：from 先执行，然后执行 where，再执行 select 。

接下来看下面的语句

select e.name ename, e.age eage from emp e where eage> 15 order by eage;

执行成功，说明 order by 是在 select 语句之后执行的。

综上所述，我们可以看到 DQL 语句的执行顺序为： from … where … group by … having … select … order by … limit …

DCL

DCL 全称 Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限。一般数据库管理员用的多，开发人员只需了解即可。

管理用户

查询用户

select * from mysql.user;

其中 Host 代表当前用户访问的主机，如果为 localhost，仅代表只能够在当前本机访问，是不可以远程访问的，使用 % 表示所有网段可以访问。User代表的是访问该数据库的用户名。在 MySQ L中需要通过 Host 和 User 来唯一标识一个用户。

创建用户

CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

修改密码

ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';

删除用户

DROP USER '用户名'@'主机名';

权限控制

MySQL 定义了很多种权限，常用的权限如下图所示：

查询权限

SHOW GRANTS FOR '用户名'@'主机名';

授予权限

GRANT 权限列表 ON 数据库.表名 TO '用户名'@'主机名';

撤销权限

REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

函数

与编程语言中的函数是一样的作用，MySQL 中主要包含四类函数：字符串函数、数值函数、日期函数、流程函数

字符串函数

MySQL 内置了很多字符串函数，如下图所示：

字符串函数使用实例：将 workno 统一为 5 位数，不足五位数的在前面补 0，如 1 号就是 00001

数值函数

常见的数值函数如下所示

通过数据库的函数，生成一个六位数的随机验证码：首先获取随机数 rand() ——> 变为六位数的整数乘 1000000 ——> 没有小数 round(x, 0) ——> 可能生成的小于六位数，用 0 补齐 lpad(x, 6

码农公寓

MySQL 基础

SQL 语法

DDL

数据库操作

表操作

数据类型

表字段操作

DML

添加数据

修改数据

删除数据

DQL

基础查询

条件查询

聚合函数

分组查询

排序查询

分页查询

执行顺序

DCL

管理用户

权限控制

函数

字符串函数

数值函数

相关文章