学习笔记之 MySQL 学习第一部分

mysql是关系型数据库，在通常开发中也是使用非常广泛的一种数据库。
工作将近十年以来，也是我在工作中主要使用，甚至说可以是唯一使用的关系型数据库。
虽然对mysql的使用已经非常熟练了，但是每次在使用的时候还是会遇到自己忘记或者不知道的知识点，所以我要重新学一遍。
可能这次学习还是不能学的很全面，那就以后再多学几遍就好了。
开始了~

mysql中的数据类型

数值类型

• tinyint
• smallint
• mediumint
• int 或 integer
• bigint
• float
• double
• decimal

日期和时间类型

• date
• time
• year
• datetime
• timestamp

字符串类型

• char
• varchar
• tinyblob
• tinytext
• blob
• text
• mediumblob
• mediumtext
• longblob
• longtext

• 带 blob 字样的是用来存储二进制对象的格式\
• 带 text 字样的是用来存储长文本数据的\
• tiny、无前缀(blob/text)、medium、long 可存储的内容长度依次加长，在实际业务开发中可以根据业务需要进行选择\
• char为定长字符串\
• varchar是变长字符串\

char和varchar的区别

1. 存储方式的区别
   • char
     • char是一种固定长度的数据类型。当你定义一个char类型的列时，比如char(10)，它会占用固定的10个字符的存储空间。
     • 例如，如果你存储一个长度为3的字符串'abc'到char(10)类型的列中，MySQL会在这个字符串后面自动填充7个空格，使这个值的长度达到10个字符。这是因为char类型会始终占用指定的固定长度空间。
   • varchar
     • varchar是可变长度的数据类型。当定义一个varchar类型的列，如varchar(10)，它只会占用实际存储字符串长度所需的空间，再加上1 - 2个字节来记录字符串的长度。
     • 例如，存储'abc'到varchar(10)类型的列中，它只会占用3个字符的空间来存储'abc'，再加上1 - 2个字节来记录长度，而不是像char那样占用固定的10个字符空间。
2. 性能方面的区别
   • char
     • 由于char类型的长度是固定的，在数据的存储和检索过程中，MySQL的处理速度相对较快。因为它不需要额外的操作来计算长度，只要按照固定的长度读取或写入数据就可以了。
     • 当存储的数据长度基本固定，且长度较短时，char类型的性能优势比较明显。例如，存储性别（男/女）这样固定长度为1的字符数据，使用char类型比较合适。
   • varchar
     • varchar类型因为要记录字符串的长度信息，并且其存储长度是可变的，所以在数据的存储和检索过程中，会有一些额外的开销。
     • 但是，当存储的数据长度差异较大，且大部分数据长度小于定义的最大长度时，varchar类型可以节省大量的存储空间。例如，存储用户的评论内容，评论的长度可能从几个字到几百字不等，使用varchar类型就比较合适。
3. 使用场景的区别
   • char
     • 适合存储长度固定的数据，如身份证号码（假设长度固定为18位）、手机号码（假设固定为11位）等。
     • 也适用于存储一些代码类型的数据，如商品编码、员工编号等，这些数据长度通常是固定的，而且对存储空间的敏感度相对较低，更注重数据的快速读取和写入。
   • varchar
     • 适合存储长度不固定的数据，如文章标题、用户的详细地址等。这些数据的长度可能因用户输入等因素而有较大差异，使用varchar类型可以有效地节省存储空间。

枚举与集合类型

• enum 枚举类型
• set 集合类型

空间数据类型

GEOMETRY, POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, GEOMETRYCOLLECTION: 用于存储空间数据（地理信息、几何图形等）。

连接mysql数据库

mysql yourhost -u your_username -p your_password

• 如果你连接的是你本地的数据库，yourhost可以用localhost或者127.0.0.1代替，也可以直接忽略。
• 如果连接的是远程数据库则不能省略
• -p 后边的密码不必要是在输入连接命令的时候一起输入，可以只输入

mysql yourhost -u your_username -p

在输入回车之后，就会要求输入密码，这个时候再输入密码即可。

列出所有数据库

SHOW DATABASES;

选择要使用的数据库

USE your_database;

列出选择的数据库中的表

SHOW TABLES;

退出mysql命令窗口

EXIT;
或者
QUIT;

接下来，用一个名称为study的数据库为例。
在这个数据库中有一个名为user的数据表，表中的字段有：id（主键）、username、email、birthdate、is_active。

创建数据库

create database study;

删除数据库

drop database study;

选择数据库

use study;

创建数据表

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
);

那么，创建user数据表的语句是

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    birthdate DATE,
    is_active BOOLEAN DEFAULT TRUE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ENGINE=InnoDB DEFAULT CHARSET=utf8;

删除数据表

drop table users;

还可以先判定数据表是否存在，再删除

drop table if exists users;

删除数据表中的所有数据并将主键自动索引值重置为1

truncate users;

插入数据

insert into (id,username,email,birthdate,is_active) values (1,'zhouyi','zhouyi@test.com','2024-12-18',true),
(2,'zhangsan','zhangsan@test.com','2024-12-17',true),
(3,'zhaosi','zhaosi@test.com','2024-12-16',false);

在这里插入数据的时候也给为自增的主键id写入了数据，但是因为id为自增字段所有不需要写入，数据库会自动维护这个字段。
如果在插入数据的时候，有一些字段不需要插入数据并且有默认值，则可以省略
比如我们省略email字段，则插入sql为：

insert into (username,birthdate,is_active) values ('王五','2024-12-15',false);

查询数据

查询数据的伪代码是

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number];

参数说明：

• column1, column2, ... 是你想要选择的列的名称，如果使用 * 表示选择所有列。
• table_name 是你要从中查询数据的表的名称。
• WHERE condition 是一个可选的子句，用于指定过滤条件，只返回符合条件的行。
• ORDER BY column_name [ASC | DESC] 是一个可选的子句，用于指定结果集的排序顺序，默认是升序（ASC）。
• LIMIT number 是一个可选的子句，用于限制返回的行数。

MySQL SELECT 语句简单的应用实例：

选择所有列的所有行
SELECT * FROM users;

选择特定列的所有行
SELECT username, email FROM users;

添加 WHERE 子句，选择满足条件的行
SELECT * FROM users WHERE is_active = TRUE;

添加 ORDER BY 子句，按照某列的升序排序
SELECT * FROM users ORDER BY birthdate;

添加 ORDER BY 子句，按照某列的降序排序
SELECT * FROM users ORDER BY birthdate DESC;

添加 LIMIT 子句，限制返回的行数
SELECT * FROM users LIMIT 10;

在 WHERE 子句中，你可以使用各种条件运算符（如 =, <, >, <=, >=, !=），逻辑运算符（如 AND, OR, NOT），以及通配符（如 %）等。

以下是一些进阶的 SELECT 语句实例：

-- 使用 AND 运算符和通配符
SELECT * FROM users WHERE username LIKE 'j%' AND is_active = TRUE;

-- 使用 OR 运算符
SELECT * FROM users WHERE is_active = TRUE OR birthdate < '1990-01-01';

-- 使用 IN 子句
SELECT * FROM users WHERE birthdate IN ('1990-01-01', '1992-03-15', '1993-05-03');

WHERE 子句

WHERE 子句用于在MySQL中过滤查询结果，限定查询条件，限定查询范围，以下是简单的查询示例。

1. 等于查询

SELECT * FROM users WHERE username = 'test';

2. 不等于条件查询

SELECT * FROM users WHERE username != 'runoob';

3. 大于条件

SELECT * FROM products WHERE price > 50.00;

4. 小于条件

SELECT * FROM orders WHERE order_date < '2023-01-01';

5. 大于等于条件

SELECT * FROM employees WHERE salary >= 50000;

6. 小于等于条件

SELECT * FROM students WHERE age <= 21;

7. 组合条件(AND、OR)

SELECT * FROM products WHERE category = 'Electronics' AND price > 100.00;

SELECT * FROM orders WHERE order_date >= '2023-01-01' OR total_amount > 1000.00;

8. 模糊匹配

// 在like条件中，如果匹配内容左右两侧都有通配符(%)则无法命中索引
SELECT * FROM customers WHERE first_name LIKE '%J%';

// 在like条件中，如果匹配内容只有右侧有通配符(%)则可以命中索引
SELECT * FROM customers WHERE first_name LIKE 'J%';

更多说明
* like 子句使用%字符来表示任意字符，也就是前面说的通配符
* 如果在like子句中没有使用%则和等于(=)的效果是一样的
* 在like条件中，如果匹配内容左右两侧都有通配符(%)则无法命中索引
* 在like条件中，如果匹配内容只有右侧有通配符(%)则可以命中索引
* 在like中还可以使用单个字符通配符(_)

9. IN条件

SELECT * FROM countries WHERE country_code IN ('US', 'CA', 'MX');

10. NOT 条件

SELECT * FROM products WHERE NOT category = 'Clothing';

11. BETWEEN 条件

SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

12. IS NULL 条件

SELECT * FROM employees WHERE department IS NULL;

13. IS NOT NULL 条件

SELECT * FROM customers WHERE email IS NOT NULL;

UPDATE 更新

UPDATE 更新操作的伪代码是

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

DELETE 删除

删除数据操作的伪代码是

DELETE FROM table_name
WHERE condition;

ORDER BY 语句

ORDER BY 语句的伪代码如下

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

解释

1. 用于查询结果排序
2. 排序规则分为顺序排序(ASC)和降序排序(DESC)
3. 可以多列排序，每一个排序列都可以分别指定排序规则，当多列排序时起作用的顺序是从左到右。左侧的排序优先级高于右侧
4. 支持使用表达式排序，MySQL会按照计算之后的结果进行排序

分组(GROUP BY)

GROUP BY 分组语句的伪代码是

SELECT column1, aggregate_function(column2)
FROM table_name
WHERE condition
GROUP BY column1;

解释

1. 通常与聚合函数使用(count/sum/avg等)
2. 可以使用多列进行分组，只需要在 GROUP BY 子句中使用逗号分隔即可，也是左侧优先原则