MYSQL入门-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_49811843/article/details/146503095

一、mysql数据类型

1. 数值类型（Numeric）

类型	字节数	说明
整数类型
`TINYINT`	1	-128 到 127（无符号 0 到 255）
`SMALLINT`	2	-32,768 到 32,767（无符号 0 到 65,535）
`MEDIUMINT`	3	-8,388,608 到 8,388,607（无符号 0 到 16,777,215）
`INT` / `INTEGER`	4	-2,147,483,648 到 2,147,483,647（无符号 0 到 4,294,967,295）
`BIGINT`	8	-9,223,372,036,854,775,808 到 9,223,372,036,854,775,807（无符号 0 到 18,446,744,073,709,551,615）
小数类型
`FLOAT(M,D)`	4	单精度浮点数（M 表示总位数，D 表示小数位数）
`DOUBLE(M,D)` / `REAL`	8	双精度浮点数
`DECIMAL(M,D)` / `NUMERIC`	变长	精确浮点数（M 总位数，D 小数位数，存储为字符串）

注意：整数类型可以加 UNSIGNED 关键字，使其只存储非负数，范围翻倍。

2. 字符串类型（String）

类型	字节数	说明
`CHAR(M)`	0-255	固定长度字符串（M 取值 0-255）
`VARCHAR(M)`	0-65535	可变长度字符串（M 取值 0-65535，取决于 `row format` 和 `max row size`）
`TEXT` 系列		存储大文本
`TINYTEXT`	255	最大 255 字节
`TEXT`	65,535	最大 64KB（16bit 存储长度）
`MEDIUMTEXT`	16,777,215	最大 16MB（24bit 存储长度）
`LONGTEXT`	4,294,967,295	最大 4GB（32bit 存储长度）
`BLOB` 系列		存储二进制大对象
`TINYBLOB`	255	类似 `TINYTEXT`，用于二进制
`BLOB`	65,535	类似 `TEXT`，用于二进制
`MEDIUMBLOB`	16,777,215	类似 `MEDIUMTEXT`，用于二进制
`LONGBLOB`	4,294,967,295	类似 `LONGTEXT`，用于二进制
`BINARY(M)`	0-255	固定长度二进制
`VARBINARY(M)`	0-65535	可变长度二进制

注意：CHAR 和 VARCHAR 适用于短字符串，TEXT 适用于长文本；BLOB 适用于存储图片、音频等二进制数据。

3. 日期和时间类型（Date & Time）

类型	字节数	说明
`DATE`	3	存储日期，格式：`YYYY-MM-DD`
`DATETIME`	8	存储日期时间，格式：`YYYY-MM-DD HH:MM:SS`
`TIMESTAMP`	4	存储 Unix 时间戳（1970-01-01 以来的秒数），受时区影响
`TIME`	3	存储时间，格式：`HH:MM:SS`
`YEAR`	1	存储年份，格式：`YYYY`

区别：

DATETIME 适用于存储完整时间，不受时区影响。

TIMESTAMP 适用于自动更新的时间戳，受时区影响。

4. JSON 数据类型

类型	说明
`JSON`	存储 JSON 格式数据，MySQL 5.7+ 支持

特点：

允许存储有效的 JSON 数据，可用 JSON 函数处理。

支持索引，但查询效率较 VARCHAR 低。

适用于存储半结构化数据，如日志、配置等。

二、数据库基础操作

1. 数据库管理

1.1. 连接 MySQ

mysql -u 用户名 -p

然后输入密码进入 MySQL。

1.2. 查看所有数据库

SHOW DATABASES;

1.3. 创建数据库

CREATE DATABASE mydb charset utf8;

指定字符集：

CREATE DATABASE mydb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

创建一个名为 mydb 的数据库，并指定字符集和排序规则

小结：

utf8mb4 和 utf8 的区别：

1. utf8（伪 UTF-8）

存储范围：最多支持 3 字节 的 UTF-8 编码字符。

不支持 4 字节的 Unicode 字符（如某些中文罕见字、Emoji、部分特殊符号）。

会导致存储问题：

例如 utf8 存储 😂（U+1F602，4 字节）时会报错：

INSERT INTO test_table (col) VALUES ('😂');

错误： Incorrect string value。

2. utf8mb4（真正的 UTF-8）

存储范围：完整支持 1-4 字节 的 UTF-8 编码字符。

可存储 任何 Unicode 字符，包括 Emoji、古文字、特殊符号等。

MySQL 5.5.3 及以上版本推荐使用 utf8mb4，取代 utf8。

1.4. 选择数据库

USE mydb;

1.5. 删除数据库

DROP DATABASE mydb;

2. 表操作

2.1. 查看当前数据库的所有表

SHOW TABLES;

2.2. 创建表

CREATE TABLE users (

id INT PRIMARY KEY AUTO_INCREMENT, #自动递增，无需手动输入

name VARCHAR(50) NOT NULL, # 非空

age INT,

email VARCHAR(100) UNIQUE, # 唯一

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

--自动记录数据插入时间，用于创建时间戳

);

-------添加列注释，方便知道每一列代表什么：COMMENT

CREATE TABLE users (

id INT PRIMARY KEY COMMENT "编号",

name VARCHAR(50) NOT NULL COMMENT "姓名",

age INT COMMENT "年龄",

email VARCHAR(100) UNIQUE COMMENT "电子邮件",

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

--自动记录数据插入时间，用于创建时间戳

);

2.3. 查看表结构

DESC users;

2.4. 修改表

添加字段

ALTER TABLE users ADD phone VARCHAR(20);

修改字段类型

ALTER TABLE users MODIFY phone BIGINT;

删除字段

ALTER TABLE users DROP COLUMN phone;

重命名表

ALTER TABLE users RENAME TO customers;

删除列

alter table users add drop hobby;

修改列名

alter table users change column hobby hob int(10);

修改列数据类型

alter table users modify column hobby char(10);

修改表名

alter table users rename to MDD;

alter table MDD rename to users;

2.5. 删除表

DROP TABLE users;

3. 数据操作

3.1. 插入数据

INSERT INTO users (name, age, email) VALUES ('张三', 25, 'zhangsan@example.com');

批量插入：

INSERT INTO users (name, age, email) VALUES

('李四', 30, 'lisi@example.com'),

('王五', 28, 'wangwu@example.com'),

('麦当当', 26, 'MDD@example.com'),

('肯德基', 28, 'kfc@example.com'),

('德克士', 27, 'decos@example.com'),

('华莱士', 33, 'hualaishi@example.com'),

('汉堡王', 18, 'hbw@example.com')

;

3.2. 查询数据

查询所有数据

SELECT * FROM users;

查询指定列

SELECT

name,

email

FROM users;

条件查询

逻辑运算符:

1.between 最小值 and 最大值

select * from users where age between 20 and 27; （判断age介于20和27之间的值）
作用和使用><是一样的

select * from users where age > 20 and age < 27;

2.null

select * from users where age is null;（判断这一列有空值）

3.in （取值范围）

select * from student where age in(2,5); （选取表中所有年龄等于2、5 的数据）

4.like （通配符，适用于模糊查询）

select * from users where name like '%麦当当%'; 查找名字中包含麦当当的人

select * from users where name like '麦当当%'; 查找名字中麦当当开头的人

select * from users where name like '%麦当当'; 查找名字中含麦当当结尾的人

5.as (重命名列明)

select

        name as NM (name列更名为NM)

from users

where name like '%麦当当%'; 查找名字中包含麦当当的人

6.union all 合并两个或多个select语句结果集

select

      id

from users

where st.name = '张三'

union all

select

        id

from users

where st.name = '李四'

注意：union all 上下合并的内容列明需要一一对应

排序

SELECT * FROM users ORDER BY age DESC;

DESC：降序

ASC: 升序

去重

select distinct

name

from users; --去除users表中重复的名字，并且只展示名字列

限制结果数量

SELECT * FROM users LIMIT 5; --只展示前五行

3.3. 更新数据

UPDATE users SET age = 26 WHERE name = '张三'; 把张三的年龄改成26岁

3.4. 删除数据

DELETE FROM users WHERE name = '张三';

删除所有数据（不推荐，数据不可恢复）：

DELETE FROM users;

或者：

TRUNCATE TABLE users;

--TRUNCATE 比 DELETE 快，但无法回滚。

4. 高级查询

4.1. 统计数量

COUNT：计算指定列或表达式的行数，可以用于统计记录数量。例如：SELECT COUNT(*) FROM table_name; 将返回表中的记录数。

SUM：计算指定列或表达式的总和。例如：SELECT SUM(salary) FROM employees; 将返回 employees 表中 salary 列的总和。

AVG：计算指定列或表达式的平均值。例如：SELECT AVG(age) FROM students; 将返回 students 表中 age 列的平均值。

MAX：返回指定列或表达式的最大值。例如：SELECT MAX(price) FROM products; 将返回 products 表中 price 列的最大值。

MIN：返回指定列或表达式的最小值。例如：SELECT MIN(quantity) FROM inventory; 将返回 inventory 表中 quantity 列的最小值。

4.2. 分组统计

SELECT

age,

COUNT(*)

FROM users

GROUP BY age; 按年龄分组，看每个年龄有多少人

高阶写法：开窗函数

SELECT

age,

COUNT(*)over(partition by age) as age_count

FROM users