MySQL数据库笔记

一、数据库基础概念

1. 数据库定义  

• 数据库：统一管理、长期存储、有组织的相关数据集合。  
• 特征：  
• 数据按模型组织（冗余小、独立性高、易扩展、可共享）。  

 2. 实际应用  

• 数据存储：高效管理大量结构化数据。  
• 数据管理：增删改查（CRUD）与权限控制。  
• 数据分析：整合数据源，支持决策。  

3. 常见数据库系统  

数据库	特点
MySQL	开源、高性能、易用，适用于Web应用。
Oracle	企业级、高可靠，支持大型应用。
SQL Server	微软开发，集成分析服务（SSAS/SSRS/SSIS）。
Redis	内存数据库，支持多种数据类型（字符串、哈希等），读写速度快，适用于缓存。

4. 工具  

• phpStudy：集成Apache + PHP + MySQL，快速搭建本地开发环境。
•  Navicat：可视化数据库管理工具，支持数据库操作与查询构建。  

二、数据库基础语法

1. MySQL默认数据库  

• information_schema（元数据）  
• mysql（用户权限信息）  
• performance_schema（性能监控）  
• test（测试数据库）  

2. 数据库操作  

sql
-- 查询数据库
SHOW DATABASES;

-- 创建数据库（指定字符集）
CREATE DATABASE db_name CHARACTER SET utf8mb4;

-- 修改字符集
ALTER DATABASE db_name CHARACTER SET utf8mb4;

-- 删除数据库
DROP DATABASE db_name;

-- 使用数据库
USE db_name;

3. 数据类型  

数值类型  

类型	存储（字节）	有符号数值取值范围	无符号数值取值范围
TINYINT	1	-128 ~ 127	0 ~ 255
SMALLINT	2	-32768 ~ 32767	0 ~ 65535
MEDIUMINT	3	-8388608 ~ 8388607	0 ~ 16777215
INT	4	-2147483648 ~ 2147483647	0 ~ 4294967295
BIGINT（整数值超过int时使用）	8	-2^63 ~ 2^63-1	0 ~ 2^64-1

浮点与日期类型  

• FLOAT：占用4个字节，用于表示单精度浮点数值。
• DOUBLE：占用8个字节，用于表示双精度浮点数值。（小数点后的数字更多，内存空间也会更多）。
• DECIMAL：用于表示高精度的小数，其精度和计数方法可以指定，以适应特定的需求。（小数点后位更多，一般银行使用的较多）。

日期和时间类型

类型	大小( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	-838:59:59/838:59:59	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00' 到'9999-12-31 23:59:59	YYYY-MM-DD hh:mm:ss	混合日期和时间值
TIMESTAM	4	1970-01-01 00:00:01' UTC 到 '2038-01-19 03:14:07P	YYYY-MM-DD hh:mm:ss	混合日期和时间值，时间戳

字符串类型

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

4. 数据表操作

sql
--  创建表（CREATE TABLE）
CREATE TABLE 表名 (
    列名1 数据类型 [约束],
    列名2 数据类型 [约束],
    ...
);
-- 修改表（ALTER TABLE）
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束];
--  删除表（DROP TABLE）
DROP TABLE [IF EXISTS] 表名;
-- 插入数据（INSERT INTO）
INSERT INTO 表名 (列1, 列2, ...),VALUES (值1, 值2, ...);
-- 修改数据（UPDATE）
UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2, ...,[WHERE 条件];
-- 删除数据（DELETE）
DELETE FROM 表名 [WHERE 条件];

5. 表约束  

• 主键约束：唯一且非空（`PRIMARY KEY` + `AUTO_INCREMENT`）。  
• 外键约束：关联其他表的主键（`FOREIGN KEY ... REFERENCES`）。  
• 唯一约束：列值唯一（`UNIQUE`），允许空值。  
• 非空约束：列值不能为空（`NOT NULL`）。  

6. 表关系与外键级联  

多对一：通过外键实现，例如订单表关联用户表。  
外键级联操作：主表数据更新/删除时，自动同步子表数据。  

sql
CREATE TABLE 子表 (
  列名 数据类型,
  FOREIGN KEY (外键列) REFERENCES 主表(主键列)
    ON UPDATE CASCADE
    ON DELETE CASCADE
);

 三、数据库常用查询语句  

1. 基础查询  

sql
-- 查询所有列
SELECT * FROM table;

-- 条件查询
SELECT * FROM table WHERE price > 3000;

-- 模糊查询（%匹配任意字符，_匹配单个字符）
SELECT * FROM table WHERE name LIKE '华为%';

2. 聚合函数  

sql
SELECT 
  COUNT(*) AS total, 
  MAX(price) AS max_price,
  AVG(stock) AS avg_stock 
FROM products;

3. 排序与分组  

sql
-- 按价格降序排序
SELECT * FROM products ORDER BY price DESC;

-- 按品牌分组统计库存
SELECT brand, SUM(stock) FROM products GROUP BY brand;

-- 分页查询（每页5条，第2页）
SELECT * FROM products LIMIT 5 OFFSET 5;

 四、表关系及多表查询  

1. 表关系类型  

• 一对一：如用户表与身份证表。  
• 一对多：如部门表与员工表。
•  多对多：通过中间表实现，如学生与课程表。  

2. 连接查询  

内连接（INNER JOIN）  

sql
SELECT a.name, b.order_id 
FROM users a
INNER JOIN orders b ON a.id = b.user_id;

左外连接（LEFT JOIN）  

• 保留左表所有记录，右表无匹配时填充NULL。  

右外连接（RIGHT JOIN）

•  保留右表所有记录，左表无匹配时填充NULL。  

3. UNION与子查询  

sql
 合并查询结果（去重）
SELECT name FROM users
UNION
SELECT name FROM customers;

子查询（查询价格高于平均价的商品）
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

 五、PHP + MySQL  

1. 连接数据库  

php
<?php
$conn = mysqli_connect("localhost", "user", "password", "db_name");
if (!$conn) {
    die("连接失败: " . mysqli_connect_error());
}
?>

2. 执行SQL操作

php
// 查询数据
$result = mysqli_query($conn, "SELECT * FROM products");
while ($row = mysqli_fetch_assoc($result)) {
    echo $row['name'];
}

// 插入数据
mysqli_query($conn, "INSERT INTO products (name, price) VALUES ('手机', 2999)");

// 关闭连接
mysqli_close($conn);