【MySQL学习】基础指令全解：构建你的数据库技能

📃个人主页：island1314

🔥个人专栏：MySQL学习

⛺️  欢迎关注：👍点赞 👂🏽留言 😍收藏  💞 💞 💞

引言

下面的操作都是在windows 的操作，此时MySQL在Windows下不区分大小写，但在Linux下默认是区分大小写的。
SQL通用语法：

1. SQL 语句可以单行 或 多行书写，以分号结尾
2. SQL 语句可以使用 空格 / 缩进来增强语句可读性
3. MySQL 数据库的SQL语句不区分大小写，关键字一般建议使用大写
4. 注释：  单行注释： -- 注释内容 或 # 注释内容 （MySQL 特有） 多行注释：/* 注释内容 */

SQL分类

1. 数据库操作（DDL）

输入密码 进入MySql数据库

1.1 建立数据库

create database  [IF NOT EXIST] 数据库名字 [DEFAULT CHARSET 字符集] [COLIATE 排序规则];

注：分号记得打，单词与单词之间至少有一个空格。

由于数据库创建的时候，要求不能重复，此时则可以在创建的时候，加上上面第一个[ ]内 的命令

1.2 查看数据库

1. 查看所有数据库

   SHOW DATABASES;  
   -- 注意：databases 后面带 's'

2. 查看当前数据库

   SELECT DATABASES(数据库名)

1.3 选中数据库

• use 数据库名; 

1.4 删除数据库

DROP DATABASE 数据库名;

2. 常用数据类型

2.1 数值类型

分为整型和浮点型

扩展资料

数值类型可以指定为无符号（unsigned），表示不取负数。

1字节（bytes）= 8bit。
对于整型类型的范围：

1. 有符号范围：-2^（类型字节数*8-1）到2^（类型字节数*8-1）-1，如int是4字节，就是-2^31到2^31-1
2. 无符号范围：0到2^（类型字节数*8）-1，如int就是2^32-1

尽量不使用unsigned，对于int类型可能存放不下的数据，int unsigned同样可能存放不下，与其
如此，还不如设计时，将int类型提升为bigint类型。

2.2 字符类型

注：上面的size表示该类型最多存储几个字符（不是字节）比如：当写了 size = 10 时，不是说当前就立即分配10 个字符的存储空间......，而是先会分配一个比较小的空间，如果不够，再自动扩展，最大的空间不超过10

2.3 时间类型

一般常用：

• 整数：int，long
• 小数：double，decimal
• 字符串：varchar
• 时间日期：datetime

3. 数据表操作（DDL）

针对数据表的操作，前提是选中数据库（use 数据库）

3.1 创建表

CREATE TABLE 表名（列名 类型，列名 类型...）

注：若确实想让表名/列名 和关键字一样，可以使用 反引号 ` （键盘左上角，ESC下面，tab上面的那个按键）来吧表名 列名引起来，此处列名 在前面，类型 在后面。但是也不是所有语言都类型在前的，有些语言类型可以在后面！！！比如C++，Go，Python。

3.2 查看该数据库中的所有表

• show tables;  (查看当前数据库下有那些表，比如我们刚刚使用了use来去使用数据库，则就在当前数据库进行查找)

3.3 查看指定表的结果

desc 表名; 

•  查看表中的列

SHOW COLUMNS FROM 表名;  (查看表中有那些列，更便捷的方式是：describe 表名;) 

案例： 

其中：

• field： 表示字段名字
• Type：表示字段类型，注：此处的（11）表示的是显示的宽度，显示这个int类型时，最多占据 11 个字符的宽度（和存储时候的容量无关）
• Null： 表示是否为空，注：此处写成 YES允许这一列为NULL，也可以写成null、Null
• Key：索引类型
• Default：默认值,是NULL可以通过 default 约束来修改
• Extra：扩充（额外信息）

show create table 表名 \G; -- 查看创建表的sql语句， \G 是格式化显示的意思

案例：

3.4 删除表

drop tables [IF EXISTS] 表名；

• 删除指定表，并且重新创建该表

TRUNCATE TABLE 列名

注：删除表的同时，也会把表里的数据一起删除掉。

3.5 修改表

1. 添加字段

   alter table 表名 add 字段名 类型(长度) [COMMENT 注释] [约束]

   

2. 修改数据类型

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

3. 修改字段名 和 字段类型

   ALTER TABLE 表名 CHANFGE 旧字段名 新字段名 类型(长度)[comment 注释] [约束]

4. 修改表名

   ALTER TABLE 表名 RENAME TO 新表名

4. MySQL 增删改查（DML & DQL）

4.1 增删改（DML）

4.1.1 新增

• insert into 表名 values(值, 值...);
• 指定列插入：insert into 表名（列名, 列名...）values(值,值...); 

 此时我们只插入 name，id 这一列就会被填充为默认值（此处默认值为null）

• 我们还可以一次插入多行记录~~  insert into 表名 values(值, 值...), (值, 值...);
• 

注：上面使用到的 ' '，可以用来表示字符串，或者用 " "，SQL没有 “字符” 这个类型。

• 小知识：（datetime 类型的插入）

若我们插入时间要是当前时刻

insert into student2 select * from student1;

• 插入查询结果

这个用到了下面我们会讲到的查询，查询搭配插入使用，把查询语句的查询结果，作为插入的数值。如将 student1 的查询结果插入到 student2 中。

insert into student2 select * from student1;

注：此处要求查询出来的结果集合， 列数/类型 要和插入的这个表 匹配

4.1.2 修改

update 表名 set 列名 = 值 where 条件

案例：

注：修改NULL数据时，如下：

明明我们有英语成绩为NULL的情况，但是仍然没有匹配到，原因是为什么呢？

在修改列允许Null时，可能会涉及到已存在的数据。如果列属性从不允许Null变为允许Null，可能需要处理现有数据以确保数据的一致性和完整性。

以下是常见的处理的方法：

4.1.3 删除

delete from 表名 where 条件 / order by /limit;

4.2 数据库约束

4.2.1 约束类型

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• DEFAULT - 规定没有给列赋值时的默认值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
  注：一张表里只能有一个 primary key.
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。对于MySQL数据库，对CHECK子句进行分析，但是忽略CHECK子句。

4.2.2 NULL 约束

创建表时，可以指定某列不为空：

4.2.3 UNIQUE：唯一约束

指定id列为唯一的、不重复的：

4.2.4 DEFAULT：默认值约束

指定插入数据时，name列为空，默认值为未命名：

4.2.5 PRIMARY KEY：主键约束

注：一张表只能有且仅有一个主键

小知识：主键自动生成索引

4.2.6 FOREIGN KEY：外键约束

外键用于关联其他表的主键或唯一键

对学生表进行数据插入：

注：当我们想删除父表时，则需要先删除 子表，再删除 父表。

• 指定外界约束时，要求父表这一列，得是主键或者 unique，范例：

4.3 查询(DQL)

1. SELECT 字段列表
2. FROM 表名列表
3. WHERE 条件列表
4. GROUP  BY 分组字段列表
5. HAVIJNG 分组后条件列表
6. ORDER BY 排序字段列表
7. LIMIT 分页参数

执行顺序：②③④⑤①⑥⑦

在查询前，我们先创建一个表。

mysql > --创建学生成绩表
mysql > create table student(id int, name varchar(20), math decimal(3, 1), english decimal(3, 1);

mysql > --插入测试数据
insert into student(id, name, math, english) values
(1, '张三', 78, 90),
(2, '李四', 78, 45),
(3, '王五', 67.9, 45.6),
(4, '赵六', NULL, 67.7),
(5, '孙行者', 76.8, 90.0),
(6, '者行孙', 80.4, 67.3);

 4.2.1 基本查询：SELECT

• 全列查询

 select * from 表名; // 把表中所有行所有列都查询出来

注：其中上面的 * 是通配符，可以代指所有列

• 指定列查询  select 列名, 列名... from 表名;

• 查询字段为表达式 （一边查询，一边计算）

在查询的时候，写作由列名构成的表达式，把这一列的所有行都带入到表达式中

注：但是原始数据不变，这个只是产生的临时数据。因此我们可以发现，查询的时候，是把服务器这里的数据读出来，返回给客户端，并且以临时表的形式进行展示。

SQL 查询的时候可以进行一些简单的统计操作，表达式查询吗，是列和列之间的运算，把每一行都带入到这样的运算中。

• 查询时指定别名

查询的时候给 列 / 表达式 指定别名（给表也能指定别名）

select 表达式 as 别名 from 表名;

• 去重查询

distinct 修饰某个列 / 多个列，值相同的行只保留一个。

 注： 其中 单独的distinct只能放在开头，否则报错，语法错误

4.2.2 排序查询：ORDER BY

查询的时候进行排序，把行进行排序

注：需要明确排序规则~~

1. 针对哪个列作为比较规则
2. 排序的时候式升序还是降序

SELECT 列名 FROM 表名 ORDER BY 列名 ASC/DESC; // asc 升序，desc 降序

如果一个 sql 不加 order by 此时查询的结果数据的顺序是： “不确定的” / “无序”

注：其可以按照表达式进行排序，还可以多个列进行排序。比如数学成绩相同时，再按照语文顺序排序。

4.2.3 条件查询：WHERE

会指定具体的条件，按照条件针对数据进行筛选

SELECT 列名 FROM 表名 WHERE 条件

原理如下：遍历这个表每一行记录，把每一行的数据分别带入到条件中，如果条件成立，这个记录就会被放入结果集合中，不成立，这个记录就 pass

比较运算符：

逻辑运算符：

注：

1. WHERE条件可以使用表达式，但不能使用别名。
2. AND的优先级高于OR，在同时使用时，需要使用小括号()包裹优先执行的部分

案例：

Like 模糊匹配

在模糊匹配前，为了让数据显示更加明显，我们加入几行数据。

insert into student(id, name, math, english) values
(7, '行孙者',67, 90),
(8, '孙明', 83, 45),
(9, '孙', NULL, NULL);

显示结果：

4.2.4 分页查询：LIMIT

由于使用select * 这种方式查询，比较危险，需要保证一次查询，不要查出来的东西太多~~

因此 MYSQL 就使用limit 来限制这次查询最多查出来多少个结果。

select 查询列 from 表名 limit N offset M 
// N 表示这次最多查出几条记录
// M 表示这次查询的 N 条记录， 从第几个下标开始（默认下标从 0 开始）

4.2.5 聚合查询

① 聚和函数

🍅常见的统计总数、计算平局值等操作，可以使用聚合函数来实现，常见的聚合函数有：

案例：（下面是使用的 count）

注：如果当前列 存在 NULL数据，则查询特定列时就不会计算 NULL数据，而直接 select(*) 就会计算所有行。

② 分组 GROUP BY 子句

🍅SELECT 中使用 GROUP BY 子句可以对指定列进行分组查询。需要满足：使用 GROUP BY 进行分组查询时，SELECT 指定的字段必须是“分组依据字段”，其他字段若想出现在SELECT 中则必须包含在聚合函数中。

select column1, sum(column2), .. from table group by column1,column3;

案例：

③ HAVING

🍅GROUP BY 子句进行分组以后，需要对分组结果再进行条件过滤时，不能使用 WHERE 语句，而需要用HAVING

4.2.6 联合查询

① 内连接

📒内连接是最常用的联接类型，它返回两个表中匹配的记录。若表中某些记录在另一个表中没有匹配的记录，这些记录将不会出现在结果集中。

SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;

上面的意思就是查找 表1中 位于 表2 相同的列

 示例：

假设有两个表 employees 和 departments，分别存储员工和部门的信息：

-- employees 表
+----+---------+-------------+
| id | name    | department_id |
+----+---------+-------------+
| 1  | Alice   | 1           |
| 2  | Bob     | 2           |
| 3  | Charlie | NULL        |
+----+---------+-------------+

-- departments 表
+----+-----------+
| id | department|
+----+-----------+
| 1  | HR        |
| 2  | IT        |
+----+-----------+

使用内连接查询所有员工及其对应的部门名称：

select employees.name, departments.department from employees 
inner join departments on employees.department_id = departments.id;

结果：
+---------+-------------+
| name    | department  |
+---------+-------------+
| Alice   | HR          |
| Bob     | IT          |
+---------+-------------+

② 外连接

左外连接 （LEFT JOIN）

📙左连接返回左表中的所有记录以及右表中匹配的记录。如果右表中没有匹配的记录，则结果集中的右表列将包含 NULL。

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

案例：

使用左连接查询所有员工及其部门（即使某些员工没有部门）：

select employees.name, departments.department from employees 
left join departments on employees.department_id = departments.id;

结果显示：
+---------+-------------+
| name    | department  |
+---------+-------------+
| Alice   | HR          |
| Bob     | IT          |
| NULL    | Sales       |
+---------+-------------+

 右外连接 （RIGHT JOIN）

📕右连接与左连接相反，它返回右表中的所有记录以及左表中匹配的记录。如果左表中没有匹配的记录，则结果集中的左表列将包含 NULL。

SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

案例：

select employees.name, departments.department from employees 
right join departments on employees.department_id = departments.id;

结果显示：
+---------+-------------+
| name    | department  |
+---------+-------------+
| Alice   | HR          |
| Bob     | IT          |
| NULL    | Sales       |
+---------+-------------+

全外连接（FULL OUTER JOIN）

📜MySQL 5.7 及更早版本不支持全外连接（FULL OUTER JOIN）。要实现全外连接的效果，可以通过结合使用左连接和右连接并排除重复记录来模拟。

(select employees.name, departments.department
from employees
left join departments on employees.department_id = departments.id)
UNION -- 用到了下面的合并查询内容
(select employees.name, departments.department
from employees
right join departments on employees.department_id = departments.id);

-- 结果显示
+---------+------------+
| name    | department |
+---------+------------+
| Alice   | HR         |
| Bob     | IT         |
| Charlie | NULL       |
+---------+------------+

③ 自连接

自连接是将表与其自身进行联接，通常用于查找表中某个记录与其他记录的关系。

SELECT a.columns, b.columns FROM table a JOIN table b ON a.column = b.column;

 注：不能直接自连接，一般采取取别名的方式。

案例：

select * from employees as e1, employees as e2 where e1.id = e2.department_id;
+------+-------+---------------+------+-------+---------------+
| id   | name  | department_id | id   | name  | department_id |
+------+-------+---------------+------+-------+---------------+
|    1 | Alice |             1 |    1 | Alice |             1 |
|    2 | Bob   |             2 |    2 | Bob   |             2 |
+------+-------+---------------+------+-------+---------------+

④ 子查询

子查询是指嵌入在其他sql语句中的select语句，也叫嵌套查询

1. 单行子查询：返回一行记录的子查询
2. 多行子查询：返回多行记录的子查询

-- 错误范例
select name from employees
where department_id = (select department_id from employees where id = department_id);
ERROR 1242 (21000) : Subquery returns more than 1 row

-- 正确范例

-- 单行子查询
select name from employees
where department_id = (select department_id from employees where id = 1);
+------ - +
| name |
+------ - +
| Alice |
+------ - +

-- 多行子查询
select employees.id, employees.name, departments.department from employees, departments
where employees.id = departments.id;
+------+-------+------------+
| id   | name  | department |
+------+-------+------------+
|    1 | Alice | HR         |
|    2 | Bob   | IT         |
+------+-------+------------+

⑤ 合并查询

-- 错误范例
select id, name from employees where id < 3
union select department from departments;
ERROR 1222 (21000) : The used SELECT statements have a different number of columns 
-- 由上可知：合并的两个sql 的结果集的列，需要匹配，列的个数和类型也需要一致，类名不用一致

-- 正确范例
select name from employees where id < 3
union select department from departments;
-- 结果显示：
+-------+
| name  |
+-------+
| Alice |
| Bob   |
| HR    |
| IT    |
+-------+

6. 数据库用户（DCL）

6.1 用户操作

（1）登录MySQL 主机

mysql -u root -p

（2）查询已存在用户

SELECT USER, HOST FROM MYSQL.user;

（3）创建用户

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

（4）修改用户

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

（5）删除用户

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

 案例：

-- 登录 MySQL 服务器
mysql - u 用户名 - p

-- 查看已存在用户
select user, host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

-- 创建用户 s1, 只能在当前主机 比如我当前主机是 localhost 下访问，密码123456;
create user 's1'@'localhost' identified by '123456';

-- 创建用户 s2，可以在任意主机访问数据库，密码123456
create user 's2'@'%' identified by '123456';

-- 修改用户 s2 的访问密码为1234
alter user 's2'@'localhost' identified by mysql_native_password by '1234';

-- 删除用户
drop user  's1'@'localhost';

-- 退出 MySQL 服务器
exit;

-- 注：主机名可以使用 % 进行通配
-- 获取当前MySQL的主机名
select @@hostname;

 6.2 用户权限

（1）查询权限

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

（2）授予权限

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

（3）撤销权限

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

案例：

-- 查询 s2 的权限
show grants for 's2'@'%';
-- 查询显示
+--------------------------------+
| Grants for s2@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO `s2`@`%` |
+--------------------------------+

-- 授予 s2 权限数据库 student 上表的所有权限
grant all on student.* to 's2'@'%';
-- 查询显示
+-------------------------------------------------+
| Grants for s2@%                                 |
+-------------------------------------------------+
| GRANT USAGE ON *.* TO `s2`@`%`                  |
| GRANT ALL PRIVILEGES ON `student`.* TO `s2`@`%` |
+-------------------------------------------------+

-- 撤销 s2 权限数据库 student 上表的所有权限
revoke all on student.* from 's2'@'%';
-- 查询显示
+--------------------------------+
| Grants for s2@%                |
+--------------------------------+
| GRANT USAGE ON *.* TO `s2`@`%` |
+--------------------------------+

7. 索引

7.1 概念

索引是一种特殊的文件，包含着对数据表里所有记录的引用指针。可以对表中的一列或多列创建索引，并指定索引的类型，各类索引有各自的数据结构实现

7.2 作用

1. 加速数据查询

索引可以显著提高数据检索的速度。没有索引的表需要扫描整个表来找到匹配的记录，这在表数据量大时会非常慢。而有索引的表可以通过索引快速定位到相关记录，从而减少了需要扫描的数据量。

2. 加速排序和过滤操作

索引不仅可以加速数据的检索，还可以优化排序操作（例如，ORDER BY）和过滤操作（例如，WHERE 子句）。当查询包含排序或过滤条件时，索引可以帮助 MySQL 更高效地处理这些操作。

3. 提高查询性能

对于频繁使用的查询条件或连接条件，创建索引可以极大地提高这些查询的性能。例如，基于外键的索引可以加速表之间的联接操作。

4. 避免全表扫描

使用索引后，MySQL 可以避免对整个表进行全表扫描，这可以显著减少查询的时间，尤其是在数据量非常大的情况下。

5. 唯一性约束

索引可以强制唯一性约束。通过创建唯一索引，数据库可以确保索引列中的所有值都是唯一的，这有助于保证数据的完整性。

6. 提高数据完整性

在某些情况下，索引还可以提高数据的完整性。例如，主键索引可以确保表中的每一行都具有唯一的标识符，从而避免重复数据的插入

7.3 如何使用

主要的索引类型

1. 主键索引（PRIMARY KEY）: 主键索引是一种特殊的唯一索引，用于唯一标识表中的每一行。一个表只能有一个主键索引，主键字段的值必须唯一且不能为空。

2. 唯一索引（UNIQUE）: 唯一索引确保索引列中的每个值都是唯一的，可以有多个唯一索引。与主键索引不同，唯一索引的列可以允许空值（NULL）。

3. 普通索引（INDEX）: 普通索引是最常用的索引类型，用于加速数据检索，不强制列值的唯一性。

4. 全文索引（FULLTEXT）: 全文索引用于加速对文本字段的全文检索，如查找包含特定单词的记录。它适用于大量文本数据的搜索。

5. 复合索引（COMPOSITE）: 复合索引是一个索引包含多个列，用于加速基于多个列的查询操作。

注：主键，unique ,外键都会自动生成索引.

查看索引

show index from 表名

创建索引

对于非主键、非唯一约束、非外键的字段，可以创建普通索引

create index 索引名字 on 表名（列名）

 示例：（下述table_name 指表名，column_name指列名）

-- 创建一个普通索引
CREATE INDEX idx_name ON table_name (column_name);

-- 创建一个唯一索引
CREATE UNIQUE INDEX idx_unique_name ON table_name (column_name);

-- 创建一个复合索引
CREATE INDEX idx_composite ON table_name (column1, column2);

-- 创建一个全文索引
CREATE FULLTEXT INDEX idx_fulltext ON table_name (text_column);

删除索引

drop index 索引名 on 表名

注：手动创建的索引可以手动删除，但如果是自动创建的索引（主键/外键，unique），无法被删除!!!

7.4 使用场景及注意事项

要考虑对数据库表的某列或某几列创建索引，需要考虑以下几点：

• 数据量较大，且经常对这些列进行条件查询。
• 该数据库表的插入操作，及对这些列的修改操作频率较低。
• 索引会占用额外的磁盘空间。

满足以上条件时，考虑对表中的这些字段创建索引，以提高查询效率。
反之，如果非条件查询列，或经常做插入、修改操作，或磁盘空间不足时，不考虑创建索引。

注意事项

• 索引会占用空间：索引会占用额外的磁盘空间，尤其是在大型表中，索引的空间开销可能会很大。
• 更新性能：虽然索引能提高查询速度，但插入、更新和删除操作可能会变得较慢，因为每次数据修改时，索引也需要被更新。
• 选择性：索引对于具有高选择性的列（即列中唯一值较多）效果更好。例如，性别字段的选择性低（仅有“男”和“女”），而身份证号的选择性高（每个身份证号唯一）。

8. 函数类型（扩展）

8.1 字符串函数

案例：

-- 输入
select concat(upper('i'), lpad('Never',6,'-'), trim(' -Love '), substring('--You',2,5));
-- 输出
+----------------------------------------------------------------------------------+
| concat(upper('i'), lpad('Never',6,'-'), trim(' -Love '), substring('--You',1,5)) |
+----------------------------------------------------------------------------------+
| I-Never-Love-You                                                                |
+----------------------------------------------------------------------------------+
1 row in set (0.00 sec)

下面函数的使用也和上面使用类似，我们就不一一举例了。

8.2 数值函数

8.3 日期函数

8.4 流程函数

 📖 总结

上面这些也是属于比较基础的内容，大家多敲多练习就会使用了。通过掌握上面的这些指令，我们就可以进行数据库的创建、管理、数据操作和查询等多种操作。

💞 💞 💞那么本篇到此就结束，希望我的这篇博客可以给你提供有益的参考和启示，感谢大家支持！！！祝大家天天开心