MySQL基础教程

MySQL

数据库

发布日期: 2021-12-21

更新日期: 2024-07-21

文章字数: 2.5k

阅读时长: 10 分

阅读次数:

MySQL 凭借小巧且开源的优势，是目前 Web 开发中最为流行的关系型数据库之一，本文将对 MySQL 基本操作进行简单介绍。

一、DDL操作

1. 表创建

以下为建表语句中表字段属性常用约束：

关键字	作用
PRIMARY KEY	主键标识，表示该字段值需唯一。
NOT NULL	设置字段不允许空值。
AUTO_INCREMENT	设置自动自动增长，需要字段类型为 INT。
DEFAULT	设置字段缺省时默认值。
COMMENT	设置字段备注信息。

create table <table_name> 
(
    <column-1> <type> PRIMARY KEY NOT NULL AUTO_INCREMENT,
    <column-2> <type> COMMENT '字段2',
    ...
    <column-n> <type>
);

2. 表修改

通过 alter 关键字即实现对表结构的编辑，语法格式如下：

-- 新增列并指定字段类型
alter table <table_name> 
add <column_name> <type>;

-- 修改字段类型
alter table <table_name> 
modify column <column_name> <new_type>;

-- 修改字段属性名
alter table <table_name> 
change column <old_column> <new_column> <type>;

-- 删除字段
alter table <table_name> 
drop <column_name>;

3. 表删除

(1) drop

drop 会删除表的结构及其所依赖的约束、索引等，并将表所占用的空间全释放掉。

drop from <table_name>;

(2) truncate

truncate 只能作用于表，会清空表中的所有行，但表结构及其约束、索引等保持不变。

同时 truncate 会将表的自增值重置，且不会激活与表有关的删除触发器，并使表和索引所占用的空间会恢复到初始大小。

truncate table <table_name>;

4. 字符集

(1) Database

修改数据库的字符集，语法如下：

alter database <db_name> 
default character 
set <old_character_set> 
COLLATE <new_character_set>;

(2) Table

修改表的字符集，语法如下：

ALTER TABLE <table_name> 
CONVERT TO CHARACTER
set <old_character_set> 
COLLATE <new_character_set>;

(3) Column

修改列的字符集，语法如下：

ALTER TABLE <table_name> 
CHANGE <column_name> <column_type>
CHARACTER SET <old_character_set>  
COLLATE <new_character_set>;

二、DML操作

1. 数据新增

当插入新增的记录包含所有字段时，表名后无需跟字段名，当只插入部分数据时需指定字段名。

-- 指定字段插入数据
insert into <table_name> (<column-1>, <column-2>, ... , <column-n>) 
values (<value-1 >, <value-2>, ... , <value-n>);

-- 未指定插入字段时必须新增数据需包含所有字段
insert into <table_name>
values (<value-1 >, <value-2>, ... , <value-n>);

(1) 增量新增

通过基础的 insert 语法可实现数据的新增，但是当数据的主键冲突时则无法执行插入，因而 MySQL 提供了 on duplicate 与 replace into 两种方式实现增量新增。

二者的主要区别如下：

replace Into: 先执行插入，若数据已存在则先删除再插入。

on duplicate: 先执行插入，若数据已存在则根据主键更新。

增量新增默认在插入时根据主键或唯一索引进行判断，若数据存在则更新，否则新增数据。

insert into tb_test(id, name, update_time)
values('1', 'Alex', '2023-02-16 14:41:35')
on duplicate key update
id = values(id),
name = values(name),
update_time = values(update_time)


replace into 
test_db.tb_info(id, name, update_time)
values('1', 'Alex', '2023-02-16 14:41:35')

2. 数据修改

通过 update 关键字修改记录值。

这里分享一个使用技巧，当通过 update 或 delete 增删数据时，为了降低条件错误执行而带来的影响，可通过 limit 关键字限制作用条数，即便满足 where 条件的数据存在多条，语句执行仍仅会对 limit 设置的指定条数生效。

update <table_name> 
set <column-1> = <value-1>, ..., <column-n> = <value-n> 
where <condition>
limit 1;

3. 数据删除

通过 delete 关键字删除记录值，删除并不会清空表的自增值。

在使用 delete 删除数据时，数据并没有真正意义上的从硬盘上移除，而是将删除的数据区域标记为可覆盖，当有新的数据时则可存入该区域。

delete from <table_name> 
where <condition>
limit 1;

4. 数据查询

(1) 基本查询

根据对应条件查询指定字段信息。

select <column-1>, ..., <column-n>
from <table_name>
where <condition>;

(2) 虚拟表

当使用 MySQL 相关函数构造数据并不需要数据来源表时，即可使用虚拟表进行查询，语句格式如下：

select '' as <column-1>, ..., '' as <column-n>;

(3) 数据去重

通过 distinct 关键字可实现重复记录过滤，当同时包含多个列时，只有每个列都不相同才会被判定为重复数据。

select 
    distinct <column> 
from <table_name>; 

select 
    distinct <column-1>, ..., <column-n>
from <table_name>;

(4) 区间查询

通过 between 关键字查询指定区间的记录，如下查询字段 column 值 大于 1 小于 3 的记录。

select * from <table_name>
where <column> between 1 and 3;

(5) 范围查询

通过 in 关键字查询指定集合的记录，如下查询字段 column 值为 1 或 3 的记录。

select * from <table_name>
where <column> in (1, 3);

三、高级查询

1. 分页查询

当数据库中数据达到一定量级后，通常我们会使用分页查询。

-- 查询前 n 条数据
SELECT * FROM <table_name> LIMIT <num>;

-- 查询第 <start, end> 条数据
SELECT * FROM <table_name> LIMIT <end> OFFSET <start>;

-- 第二条命令的简化写法
SELECT * FROM <table_name> LIMIT <start>, <end>;

2. 排序查询

通过 Order By 关键字可对查询结果进行排序，未指定排序方式默认为 升序(aesc) ， desc 表示 降序 排列。

-- 升序查询
select * from <table_name>
order by <column>;

-- 降序查询
select * from <table_name>
order by <column> desc;

3. 分组查询

(1) Group By

通过 group By 函数可实现数据的分组效果，同时可达到去重效果。

select <column>
from <table_name>
group by <column>;

需要注意当查询字段中包含聚合函数时则未使用聚合函数的字段必须声明在 group by 中。

-- 如下 id 和 name 必须声明在 group by 之后
select id, name, sum(score) as num 
from <table_name>
group by id, name;

(2) Having

having 用于给结果过滤，而 where 是针对查询结果的前的数据过滤，配合 group by 关键字即可实现便捷的重复数据查询。

如下行命令即先根据 name 字段统计数量，再通过 having 筛选出数量大于 1 的记录，即名称重复的数据。

select name, COUNT(*) as num 
from tb_user
group by name
having num > 1

4. 条件查询

在执行语句查询若需要实现 if else 效果即可通过 case when 语句实现，其基本语法如下：

select 
    case 
        when <condition 1> then <select command>
        ...
        when <condition n> then <select command>
        else <select command>
    end as <alia>
from <table name>;

四、多表查询

当多张表存在关联字段时，即可根据关联字段进行条件查询，准备以下两张数据表：

-- 用户表 tb_users                  -- 关联表 tb_files
---------------------------        ----------------------------- 
|   user_id  |  password  |        |   upload_id  | file_name  |
---------------------------        ----------------------------- 
|    1234    |     11     |        |    1234      |   a.txt    | 
|    4321    |     12     |        |    1234      |   b.txt    | 
---------------------------        |    4567      |   c.txt    | 
                                   -----------------------------

1. 联合查询

通过 union 可以将多个同结构的结果集合并，需要注意每个子结果集必须包含相同的列数、表达式或聚集函数。

默认联合查询消除重复行，可以使用 all 关键字表示不消除重复行。

select * from <table-1>
union all
select * from <table-2>

2. 内连接

只返回两张表中符合条件的数据。

select * from tb_users u 
inner join tb_files f
on u.user_id = f.upload_id

-------------------------------------------------------
|   user_id  |  password  |   upload_id  |  file_name |
-------------------------------------------------------
|    1234    |     11     |     1234      |   a.txt   | 
|    1234    |     11     |     1234      |   b.txt   |
-------------------------------------------------------

3. 右连接

拼接两张表数据，左表不满足的条件的数据为空。

select * from tb_users u 
right join tb_files f
on u.user_id = f.upload_id

-------------------------------------------------------
|   user_id  |  password  |   upload_id   | file_name |
-------------------------------------------------------
|    1234    |     11     |     1234      |   a.txt   | 
|    1234    |     11     |     1234      |   b.txt   |
|    Null    |    Null    |     4567      |   c.txt   |
-------------------------------------------------------

4. 左连接

拼接两张表数据，右表不满足的条件的数据为空。

select * from tb_users u 
left join tb_files f
on u.user_id = f.upload_id

-------------------------------------------------------
|   user_id  |  password  |   upload_id   | file_name |
-------------------------------------------------------
|    1234    |     11     |     1234      |   a.txt   | 
|    1234    |     11     |     1234      |   b.txt   |
|    4321    |     12     |     Null      |   Null    |
-------------------------------------------------------

5. EXISTS

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

与上述 join 连表查询的不同之处在于当子查询记录集合大于父查询集合时，通过 join 方式查询可能存在笛卡尔积的问题，导致查询效率低下，而 EXISTS 则并无此问题。

下面通过一个具体示例演示 EXISTS 的效果，如下述语句即查询 tb_user 中 username 重复的记录。

SELECT 
    username
FROM 
    tb_user
WHERE EXISTS
(
    SELECT username FROM tb_user 
);

五、常用函数

1. 时间函数

(1) date_format()

通过 date_format() 函数实现时间格式化。

select date_format('2022-06-10 02:17:54', '%Y-%m-%d %H:00:00') as time1;
   
---------------------------
|          time1          |
---------------------------
|   2022-06-10 02:00:00   | 
---------------------------

(2) unix_timestamp

通过 unix_timestamp() 函数可将时间转为时间戳格式。

select unix_timestamp('2022-06-10 02:17:54') as time1;
   
---------------------------
|          time1          |
---------------------------
|       1654798674000     | 
---------------------------

(3) 指定时间

通过 date_add() 函数可实现时间的增减。

-- 指定时间后一小时
select date_add('2022-06-08 17:00:00', interval + 60 minute) as time1;

---------------------------
|          time1          |
---------------------------
|   2022-06-08 18:00:00   | 
---------------------------

2. 字符拼接

(1) concat()

通过 concat() 函数可实现字符串的拼接，需要注意若 concat() 拼接字段为 NULL 拼接后的字段将为空。

若包含空字段可选用 concat_ws() 函数，但其拼接的字段个数必须不少于两个。

select concat('a', 'b') as name;

(2) group_concat()

通常 group_concat() 搭配 group by 实现重复数据的过滤拼接。

select 
    group_concat(id) as ids,
    name,
    gender,
    group_concat(score) as grades
from
    tb_test t1
group by
    name,
    gender;

-- 原数据                                    -- 查询结果
-----------------------------------------    ---------------------------------------
|   id  |  name   |   gender  |  grades |    |  ids  |  name  |  gender  | grades  |
-----------------------------------------    ---------------------------------------
|   1   |  alex   |   male    |    80   |    |  1,2  |  alex  |   male   |  80,90  | 
|   2   |  alex   |   male    |    90   |    |  3,4  |  beth  |  female  |  85,92  | 
|   3   |  beth   |  female   |    85   |    ---------------------------------------
|   4   |  beth   |  female   |    92   |        
-----------------------------------------

需要注意 group_concat() 函数拼接字符存在长度限制，超出部分会自动截断，当拼接内容过长时需要手动修改配置。

-- 设置全局生效
SET GLOBAL group_concat_max_len = 102400;

-- 设置仅当前会话
SET SESSION group_concat_max_len = 102400;

-- 查看配置的长度
show variables like 'group_concat_max_len';

烽火戏诸诸诸侯

https://great-jin.github.io/db/mysql/basis/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源烽火戏诸诸诸侯 !

MySQL

单链表实现插入排序？

通过C++实现单链表的简单插入排序。

2021-12-29 算法

C++ 算法

前后端数据AES加密传输

前后端数据传输实现 AES 加密。

2021-12-13 后端

Vue JAVA

一、DDL操作

1. 表创建

2. 表修改

3. 表删除

(1) drop

(2) truncate

4. 字符集

(1) Database

(2) Table

(3) Column

二、DML操作

1. 数据新增

(1) 增量新增

2. 数据修改

3. 数据删除

4. 数据查询

(1) 基本查询

(2) 虚拟表

(3) 数据去重

(4) 区间查询

(5) 范围查询

三、高级查询

1. 分页查询

2. 排序查询

3. 分组查询

(1) Group By

(2) Having

4. 条件查询

四、多表查询

1. 联合查询

2. 内连接

3. 右连接

4. 左连接

5. EXISTS

五、常用函数

1. 时间函数

(1) date_format()

(2) unix_timestamp

(3) 指定时间

2. 字符拼接

(1) concat()

(2) group_concat()

觉得不错可以请我喝杯咖啡