温故知新-Mysql 基础查询

Docker启动mysql

Docker启动一个 Mysql

docker pull mysql

创建并启动容器有了镜像之后，可以使用以下命令来创建并启动一个 MySQL 容器

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=your_password -v /your/local/data/directory:/var/lib/mysql --name your_mysql_container_name mysql

-d：表示以 “守护进程” 模式运行容器。这意味着容器会在后台运行，而不会占用当前终端或命令提示符的输入输出。
-p 3306:3306：这是端口映射。左边的 “3306” 是主机（你的本地机器）上的端口，右边的 “3306” 是容器内 MySQL 服务运行的端口。这样，就可以通过主机的 3306 端口访问容器内的 MySQL 服务。
-e MYSQL_ROOT_PASSWORD=your_password：设置 MySQL 的 root 用户密码。将 “your_password” 替换为你想要设置的实际密码。这是一个环境变量，用于在容器启动时配置 MySQL。
-v /your/local/data/directory:/var/lib/mysql：这是数据卷挂载。左边的 “/your/local/data/directory” 是主机上的一个本地目录，用于存储 MySQL 的数据文件。右边的 “/var/lib/mysql” 是容器内 MySQL 默认的数据存储目录。通过这种挂载方式，即使容器被删除，数据也会保存在主机的本地目录中，方便数据的持久化。
–name your_mysql_container_name：给容器指定一个名称，将 “your_mysql_container_name” 替换为你想要的容器名称，方便后续对容器进行管理。

docker run -d -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123 -v /Users/mac/docker/mysql:/var/lib/mysql --name mysql1 mysql

docker ps 查看启动状态!

进入mysql

进入容器 bash

mac@MacBook-Pro-2 ~ % docker exec -it 47cdb2c02bf7 /bin/bash

登陆 mysql

bash-5.1# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 9.1.0 MySQL Community Server - GPL

Copyright (c) 2000, 2024, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

查询全部的 DATABASES

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql>

在 MySQL 命令行客户端中新建一个我们自己的数据库（Database or Schemal） MyDataBase

（从 MySQL5.0 开始，官方文档中开始使用 schema 来代替 database 来描述 MySQL 中的数据库。但是，实际上 MySQL 中的数据库仍然可以使用 database 来称呼，两者是等价的）

create schema MyDataBase collate utf8mb4_0900_ai_ci;

collate utf8mb4_0900_ai_ci 指定数据库的字符集

mysql> CREATE DATABASE IF NOT EXISTS MyDataBase;
Query OK, 1 row affected (0.03 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| MyDataBase         |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

创建 users 数据表

CREATE TABLE IF NOT EXISTS users
(
    id            INT AUTO_INCREMENT PRIMARY KEY,
    username      VARCHAR(50)  NOT NULL,
    password      VARCHAR(255) NOT NULL,
    email         VARCHAR(100),
    register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS：表示如果表 users 不存在则创建它，避免重复创建时报错。
id INT AUTO_INCREMENT PRIMARY KEY：定义了一个名为 id 的字段，类型为整型（INT），设置了自增（AUTO_INCREMENT）属性，并且将其作为主键（PRIMARY KEY），这样每条记录都会有一个唯一的递增 ID。
username VARCHAR(50) NOT NULL：定义 username 字段为可变长字符串类型（VARCHAR），最长可容纳 50 个字符，并且不允许为空（NOT NULL），用于存储用户名。
password VARCHAR(255) NOT NULL：类似地，用于存储用户密码，长度最长可达 255 个字符且不能为空。
email VARCHAR(100)：定义用于存储用户邮箱的字段，长度可达 100 个字符，可为空（用户可能不填邮箱）。
register_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP：定义了一个时间戳类型（TIMESTAMP）的字段，用于记录用户注册时间，默认值为当前时间（CURRENT_TIMESTAMP），即用户注册时若未指定时间，会自动记录当时的时间。

插入数据（增）

-- 插入单条数据示例
INSERT INTO users (username, password, email)
VALUES ('user1', 'password1', 'user1@example.com');

-- 插入多条数据示例
INSERT INTO users (username, password, email)
VALUES 
    ('user2', 'password2', 'user2@example.com'),
    ('user3', 'password3', 'user3@example.com');

INSERT INTO 指定要插入数据的表名（这里是users表）。
括号内列出要插入值的字段名，需注意如果表中有自增主键（如id字段）以及设置了默认值的字段（如register_time），通常可以省略这些字段，数据库会自动处理其值。
VALUES 后面跟着对应字段的值，插入多条数据时按照相应格式依次罗列各条记录的值即可。

查询数据（查）

-- 查询所有用户信息
SELECT * FROM users;

-- 查询特定字段的用户信息，比如只查询用户名和邮箱
SELECT username, email FROM users;

-- 根据条件查询，例如查询用户名是'user1'的用户信息
SELECT * FROM users WHERE username = 'user1';

-- 使用多个条件查询，如查询用户名是'user1'且邮箱是'user1@example.com'的用户信息
SELECT * FROM users WHERE username = 'user1' AND email = 'user1@example.com';

-- 排序查询结果，比如按照注册时间升序排序所有用户信息
SELECT * FROM users ORDER BY register_time ASC;

-- 分页查询，每页显示10条记录，查询第1页的用户信息
SELECT * FROM users LIMIT 10 OFFSET 0;

SELECT * 表示选择所有字段，也可以像 SELECT username, email 这样指定具体要查询的字段。
WHERE 子句用于添加筛选条件，来获取符合特定条件的记录。
ORDER BY 用于对查询结果按照指定字段进行排序，ASC 表示升序，DESC 表示降序。
LIMIT 结合 OFFSET 用于实现分页查询，LIMIT 指定每页显示的记录数，OFFSET 指定从第几条记录开始查询（偏移量）。

更新数据（改）

-- 更新用户名是'user1'的用户的密码为'new_password1'
UPDATE users
SET password = 'new_password1'
WHERE username = 'user1';

-- 同时更新多个字段，比如更新用户名是'user2'的用户的邮箱和密码
UPDATE users
SET email = 'new_email2@example.com', password = 'new_password2'
WHERE username = 'user2';

UPDATE 指定要更新数据的表名。
SET 后面跟着要更新的字段及其新值，多个字段更新时用逗号隔开。
WHERE 子句用于确定要更新哪些记录，如果省略 WHERE 条件，则会更新表中的所有记录，所以使用时要特别谨慎。

删除数据（删）

-- 删除用户名是'user1'的用户记录
DELETE FROM users WHERE username = 'user1';

-- 删除所有用户记录（谨慎使用，一般有确认机制等，因为这会清空整个表）
DELETE FROM users;

DELETE FROM 指定从哪个表中删除数据。
WHERE 子句同样用于限定要删除的具体记录，如果没有 WHERE 条件，就会把表中的所有记录都删除掉，操作时要小心谨慎确保符合预期。

练习查询

查询全部

SELECT * FROM users;

查询部分字段

SELECT username,email FROM users;

简单列别名

SELECT username as 用户名,email as 邮箱 FROM users;

表达式别名

SELECT id+10 as 用户ID FROM users;

-- 对用户名和邮箱进行拼接，并将拼接后的结果列名设置为'user_info'
SELECT username, CONCAT(username, '@', email) AS user_info FROM users;

where条件查询

SELECT id,users.username from users where id > 10;

条件可以是 and 连接的多个

SELECT id,users.username from users where id > 10 and username = 'user13';

LIKE 做模糊查询

-- 查询username 1结尾的数据
SELECT id,users.username from users where username like '%1';

通过 in 来指定一个集合

SELECT id,users.username from users where username in ('user1','user2','user12');

反之not in

SELECT id,users.username from users where username not in ('user1','user2','user12');

通过 between and 来指定一个区间

SELECT id,users.username from users where id between 8 and 10;

limit 实现分页

-- 查询第1页 pageSize = 10 pageNum = 1
SELECT id,users.username from users limit 10 offset 0;

-- 简化写法 limit offset,size
SELECT id,users.username from users limit 0,10;

-- 查询第2页 pageSize = 10 pageNum = 2
SELECT id,users.username from users limit 10 offset 10;

-- 简化写法  limit offset,size
SELECT id,users.username from users limit 10,10;

通过 order by 来指定排序的列

SELECT id,users.username from users order by id desc limit 0,10;

-- 先根据score降序，如果score相同，就根据 age 升序
SELECT username,score,age from users order by score desc,age asc;

GROUP BY分组

-- 增加 level字段
ALTER TABLE users
    ADD COLUMN `level` TINYINT DEFAULT 1;

-- 更新所有用户记录的level字段为1 - 5之间的随机整数
UPDATE users
SET `level` = FLOOR(RAND() * 5 + 1);

-- 根据level分组，然后计算每组的平均值，然后平均值降序排列
SELECT avg(score) as 'score平均值',level FROM users group by level order by 'score平均值' desc;

分组之后可以对结果再次进行过滤，但这时候不是用 where 了，而是用 having：

SELECT count(*) as 每组人数,level FROM users group by level having 每组人数 > 3;

count 计算数量

SELECT count(*) as 每组人数,level FROM users group by level;

distinct 去重

SELECT  level from users;

上面查出每一条的 level 可以通过 distinct 去重

SELECT distinct level from users;

聚合函数：用于对数据的统计，比如 AVG、COUNT、SUM、MIN、MAX。

SELECT 
    AVG(score) as 平均分,
    MIN(score) as 最低分, 
    MAX(score) as 最高分, 
    count(*) as 人数, 
    SUM(age) as 总年龄
from users

字符串函数：用于对字符串的处理，比如 CONCAT、SUBSTR、LENGTH、UPPER、LOWER。

SELECT LENGTH(username)              as 名字长度,
       UPPER(SUBSTR(username, 1, 4)) as 大写前缀,
       substr(users.username, 5)     as 名字后缀数字,
       concat(username, email)       as 名称邮箱
from users;

CONCAT(str1, str2,...)，其中 str1、str2 等为要连接的字符串或字符串表达式（如字段名）。
SUBSTR(str, pos)：从字符串 str 的第 pos 个位置开始提取，直到字符串末尾。
SUBSTR(str, pos, len)：从字符串 str 的第 pos 个位置开始提取长度为 len 的子字符串。

数值函数：用于对数值的处理，比如 ROUND、CEIL、FLOOR、ABS、MOD。

ROUND 四舍五入
CEIL 向上取整
FLOOR 向下取整
ABS 绝对值
MOD 取模

SELECT ROUND(1.234567, 2), CEIL(1.234567), FLOOR(1.234567), ABS(-1.234567), MOD(5, 2);

日期函数：对日期、时间进行处理，比如 DATE、TIME、YEAR、MONTH、DAY

SELECT YEAR('2023-06-01 22:06:03'),
       MONTH('2023-06-01 22:06:03'),
       DAY('2023-06-01 22:06:03'),
       DATE('2023-06-01 22:06:03'),
       TIME('2023-06-01 22:06:03');

YEAR(‘2023-06-01 22:06:03’)	MONTH(‘2023-06-01 22:06:03’)	DAY(‘2023-06-01 22:06:03’)	DATE(‘2023-06-01 22:06:03’)	TIME(‘2023-06-01 22:06:03’)
2023	6	1	2023-06-01	22:06:03

条件函数：根据条件是否成立返回不同的值，比如 IF、CASE

select username, if(score >=60, '及格', '不及格') from users;

username	if(score >=60, ‘及格’, ‘不及格’)
user1	不及格
user2	不及格
user3	及格
user4	不及格
user5	不及格
user6	不及格
user7	不及格
user8	及格
user9	不及格
user10	及格
user11	及格
user12	不及格
user13	不及格

case when 匹配不同条件

SELECT username, score, CASE WHEN score >= 90 THEN '优秀' WHEN score >= 60 THEN '良好' ELSE '差' END AS '档次'
FROM users;

username	score	档次
user1	51	差
user2	47	差
user3	80	良好
user4	59	差
user5	56	差
user6	56	差
user7	38	差
user8	90	优秀
user9	56	差
user10	92	优秀
user11	60	良好
user12	27	差
user13	54	差

if 和 case 函数和 js 里的 if、swtch 语句很像，很容易理解。

if 函数适合单个条件，case 适合多个条件。

系统函数：用于获取系统信息，比如 VERSION、DATABASE、USER。

VERSION 用于获取当前 MySQL 数据库服务器的版本信息
DATABASE 用于获取当前所在的数据库名称。如果还没有使用 USE 语句切换到具体的数据库，那么返回值可能为空（不同客户端表现可能略有差异）。
USER 用于获取当前登录到 MySQL 服务器的用户名信息，它返回的格式通常是 '用户名@主机名'，能清楚地显示是哪个用户从哪台主机连接过来的。

select VERSION(), DATABASE(), USER()

VERSION()	DATABASE()	USER()
9.1.0	mydatabase	root@172.17.0.1

其他函数：NULLIF、COALESCE、GREATEST、LEAST。

NULLIF：如果相等返回 null，不相等返回第一个值。

select NULLIF(1,1), NULLIF(1,2);

NULLIF(1,1)	NULLIF(1,2)
null	1

COALESCE：返回第一个非 null 的值：

select COALESCE(null, 1), COALESCE(null, null, 2);

COALESCE(null, 1)	COALESCE(null, null, 2)
1	2

GREATEST、LEAST：返回几个值中最大最小的。

select GREATEST(1,2,3),LEAST(1,2,3,4);

GREATEST(1,2,3)	LEAST(1,2,3,4)
3	1

类型转换函数：转换类型为另一种，比如 CAST、CONVERT、DATE_FORMAT、STR_TO_DATE。

select greatest(1, '123',3);

greatest(1, ‘123’,3)
3

最大的为 3 因为没有把 ‘123’ 当做数字,我们可以转换其为整数

select greatest(1, CAST('123' as signed ),3);

select greatest(1, CONVERT('123' , signed ),3);

greatest(1, CONVERT(‘123’ , signed ),3)
123

这里可以转换的类型有这些：

signed：整型；
unsigned：无符号整型
decimal：浮点型；
char：字符类型；
date：日期类型；
time：时间类型；
datetime：日期时间类型；
binary：二进制类型

STR_TO_DATE 和 DATE_FORMAT

SELECT DATE_FORMAT('2022-01-01', '%Y年%m月%d日');

DATE_FORMAT(‘2022-01-01’, ‘%Y年%m月%d日’)
2022年01月01日

SELECT STR_TO_DATE('2023-06-01', '%Y-%m-%d');

STR_TO_DATE(‘2023-06-01’, ‘%Y-%m-%d’)
2023-06-01

写 sql 的时候，我们有的时候用单双引号，有的时候用反引号，有的时候不加引号：

当作字符串值用的时候，需要加单引号或者双引号。当作表名、列名用的时候，用反引号或者不加引号。

目录CONTENT