SQL 基础

SQL (Structured Query Language) 是一种用于管理关系型数据库的标准语言。本文将介绍 SQL 的核心概念和基本语法。

1. 基本概念

什么是 SQL

SQL 是一种用于管理关系型数据库的标准语言，它可以用于创建、查询、更新和删除数据库中的数据。

关系型数据库

关系型数据库是一种基于关系模型的数据库，它使用表格来存储数据，表格由行和列组成。

表格 (Table)：存储数据的基本单位
行 (Row)：表格中的一条记录
列 (Column)：表格中的一个字段
主键 (Primary Key)：唯一标识一条记录的字段
外键 (Foreign Key)：引用其他表格主键的字段

2. 数据类型

数值类型

INT：整数
BIGINT：大整数
FLOAT：单精度浮点数
DOUBLE：双精度浮点数
DECIMAL：精确小数

字符串类型

CHAR：固定长度字符串
VARCHAR：可变长度字符串
TEXT：长文本
BLOB：二进制数据

日期时间类型

DATE：日期
TIME：时间
DATETIME：日期时间
TIMESTAMP：时间戳
YEAR：年份

3. 基本 SQL 语句

CREATE TABLE

用于创建表格。

sql

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT

用于插入数据。

sql

-- 插入单条数据
INSERT INTO users (name, email, age) VALUES ('John Doe', 'john@example.com', 30);

-- 插入多条数据
INSERT INTO users (name, email, age) VALUES
('Jane Smith', 'jane@example.com', 25),
('Bob Johnson', 'bob@example.com', 35);

SELECT

用于查询数据。

sql

-- 查询所有字段
SELECT * FROM users;

-- 查询指定字段
SELECT name, email FROM users;

-- 带条件查询
SELECT * FROM users WHERE age > 25;

-- 排序
SELECT * FROM users ORDER BY age DESC;

-- 限制结果数量
SELECT * FROM users LIMIT 10;

-- 分组
SELECT age, COUNT(*) FROM users GROUP BY age;

-- 连接查询
SELECT users.name, orders.amount FROM users
JOIN orders ON users.id = orders.user_id;

UPDATE

用于更新数据。

sql

-- 更新所有记录
UPDATE users SET age = 31 WHERE id = 1;

-- 更新多条记录
UPDATE users SET age = age + 1 WHERE age > 30;

DELETE

用于删除数据。

sql

-- 删除指定记录
DELETE FROM users WHERE id = 1;

-- 删除所有记录
DELETE FROM users;

4. 高级 SQL 语句

子查询

子查询是嵌套在其他 SQL 语句中的查询。

sql

-- 子查询作为条件
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

-- 子查询作为结果
SELECT (SELECT COUNT(*) FROM users) AS user_count;

连接查询

连接查询用于从多个表格中获取数据。

sql

-- 内连接
SELECT users.name, orders.amount FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- 左连接
SELECT users.name, orders.amount FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- 右连接
SELECT users.name, orders.amount FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- 全连接
SELECT users.name, orders.amount FROM users
FULL JOIN orders ON users.id = orders.user_id;

联合查询

联合查询用于合并多个查询的结果。

sql

-- 联合查询
SELECT name FROM users
UNION
SELECT product_name FROM products;

-- 联合查询（包含重复）
SELECT name FROM users
UNION ALL
SELECT product_name FROM products;

5. 函数

聚合函数

COUNT()：计数
SUM()：求和
AVG()：平均值
MAX()：最大值
MIN()：最小值

sql

-- 计数
SELECT COUNT(*) FROM users;

-- 求和
SELECT SUM(amount) FROM orders;

-- 平均值
SELECT AVG(age) FROM users;

-- 最大值
SELECT MAX(amount) FROM orders;

-- 最小值
SELECT MIN(age) FROM users;

字符串函数

CONCAT()：连接字符串
SUBSTRING()：截取字符串
UPPER()：转换为大写
LOWER()：转换为小写
LENGTH()：字符串长度

sql

-- 连接字符串
SELECT CONCAT(name, ' ', email) FROM users;

-- 截取字符串
SELECT SUBSTRING(name, 1, 3) FROM users;

-- 转换为大写
SELECT UPPER(name) FROM users;

-- 转换为小写
SELECT LOWER(email) FROM users;

-- 字符串长度
SELECT LENGTH(name) FROM users;

日期函数

NOW()：当前日期时间
CURDATE()：当前日期
CURTIME()：当前时间
DATE()：提取日期
TIME()：提取时间
YEAR()：提取年份
MONTH()：提取月份
DAY()：提取日

sql

-- 当前日期时间
SELECT NOW();

-- 当前日期
SELECT CURDATE();

-- 当前时间
SELECT CURTIME();

-- 提取日期
SELECT DATE(created_at) FROM users;

-- 提取时间
SELECT TIME(created_at) FROM users;

-- 提取年份
SELECT YEAR(created_at) FROM users;

-- 提取月份
SELECT MONTH(created_at) FROM users;

-- 提取日
SELECT DAY(created_at) FROM users;

6. 约束

主键约束

主键约束用于唯一标识一条记录。

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
);

外键约束

外键约束用于维护表格之间的关系。

sql

CREATE TABLE orders (
  id INT PRIMARY KEY,
  user_id INT,
  amount DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

唯一约束

唯一约束用于确保字段的值唯一。

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  email VARCHAR(100) UNIQUE
);

非空约束

非空约束用于确保字段的值不为空。

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL
);

默认约束

默认约束用于设置字段的默认值。

sql

CREATE TABLE users (
  id INT PRIMARY KEY,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

7. 索引

索引用于提高查询性能。

创建索引

sql

-- 创建普通索引
CREATE INDEX idx_name ON users(name);

-- 创建唯一索引
CREATE UNIQUE INDEX idx_email ON users(email);

-- 创建复合索引
CREATE INDEX idx_name_age ON users(name, age);

删除索引

sql

DROP INDEX idx_name ON users;

8. 事务

事务是一组原子性的 SQL 操作，要么全部执行，要么全部不执行。

事务的特性

原子性 (Atomicity)：事务是一个不可分割的工作单位
一致性 (Consistency)：事务执行前后，数据库状态保持一致
隔离性 (Isolation)：多个事务并发执行时，互不影响
持久性 (Durability)：事务执行完成后，结果永久保存

事务操作

sql

-- 开始事务
START TRANSACTION;

-- 执行 SQL 语句
INSERT INTO users (name, email) VALUES ('John Doe', 'john@example.com');
UPDATE orders SET amount = 100 WHERE id = 1;

-- 提交事务
COMMIT;

-- 回滚事务
ROLLBACK;

9. 视图

视图是一个虚拟表格，它是基于 SQL 查询结果的可视化表格。

创建视图

sql

CREATE VIEW user_orders AS
SELECT users.id, users.name, orders.amount, orders.created_at
FROM users
JOIN orders ON users.id = orders.user_id;

使用视图

sql

SELECT * FROM user_orders WHERE amount > 100;

删除视图

sql

DROP VIEW user_orders;

10. 存储过程

存储过程是一组预编译的 SQL 语句，它可以被多次调用。

创建存储过程

sql

DELIMITER //
CREATE PROCEDURE get_users()
BEGIN
  SELECT * FROM users;
END //
DELIMITER ;

调用存储过程

sql

CALL get_users();

删除存储过程

sql

DROP PROCEDURE get_users;

11. 触发器

触发器是一种特殊的存储过程，它在特定事件发生时自动执行。

创建触发器

sql

DELIMITER //
CREATE TRIGGER after_user_insert
AFTER INSERT ON users
FOR EACH ROW
BEGIN
  INSERT INTO user_logs (user_id, action) VALUES (NEW.id, 'insert');
END //
DELIMITER ;

删除触发器

sql

DROP TRIGGER after_user_insert;

12. 实践示例

创建数据库和表格

sql

-- 创建数据库
CREATE DATABASE mydb;

-- 使用数据库
USE mydb;

-- 创建用户表
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(100) UNIQUE NOT NULL,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 创建订单表
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  amount DECIMAL(10, 2),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

插入数据

sql

-- 插入用户数据
INSERT INTO users (name, email, age) VALUES
('John Doe', 'john@example.com', 30),
('Jane Smith', 'jane@example.com', 25),
('Bob Johnson', 'bob@example.com', 35);

-- 插入订单数据
INSERT INTO orders (user_id, amount) VALUES
(1, 100.00),
(1, 200.00),
(2, 150.00),
(3, 300.00);

查询数据

sql

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

-- 查询年龄大于 25 的用户
SELECT * FROM users WHERE age > 25;

-- 查询用户及其订单
SELECT users.name, orders.amount, orders.created_at
FROM users
JOIN orders ON users.id = orders.user_id;

-- 统计每个用户的订单总金额
SELECT users.name, SUM(orders.amount) AS total_amount
FROM users
JOIN orders ON users.id = orders.user_id
GROUP BY users.id;

13. 常见问题与解决方案

语法错误

问题：SQL 语句语法错误 解决方案：检查 SQL 语句的语法，确保使用正确的关键字和语法结构。

约束冲突

问题：插入或更新数据时违反约束 解决方案：确保数据符合约束要求，如主键唯一、外键存在等。

性能问题

问题：查询速度慢 解决方案：

创建适当的索引
优化查询语句
避免全表扫描

事务问题

问题：事务未提交或回滚 解决方案：确保事务正确提交或回滚，避免长时间占用锁。

14. 总结

SQL 是一种强大的数据库语言，它可以用于管理关系型数据库的各种操作。通过学习 SQL 的基本概念和语法，我们可以有效地管理和查询数据库中的数据。

SQL 的核心概念包括：

表格、行、列
主键、外键
数据类型
基本 SQL 语句
高级 SQL 语句
函数
约束
索引
事务
视图
存储过程
触发器

通过不断学习和实践，我们可以掌握 SQL 的技能，并将其应用到实际的数据库管理中。

SQL 基础 ​

1. 基本概念 ​

什么是 SQL ​

关系型数据库 ​

2. 数据类型 ​

数值类型 ​

字符串类型 ​

日期时间类型 ​

3. 基本 SQL 语句 ​

CREATE TABLE ​

INSERT ​

SELECT ​

UPDATE ​

DELETE ​

4. 高级 SQL 语句 ​

子查询 ​

连接查询 ​

联合查询 ​

5. 函数 ​

聚合函数 ​

字符串函数 ​

日期函数 ​

6. 约束 ​

主键约束 ​

外键约束 ​

唯一约束 ​

非空约束 ​

默认约束 ​

7. 索引 ​

创建索引 ​

删除索引 ​

8. 事务 ​

事务的特性 ​

事务操作 ​

9. 视图 ​

创建视图 ​

使用视图 ​

删除视图 ​

10. 存储过程 ​

创建存储过程 ​

调用存储过程 ​

删除存储过程 ​

11. 触发器 ​

创建触发器 ​

删除触发器 ​

12. 实践示例 ​

创建数据库和表格 ​

插入数据 ​

查询数据 ​

13. 常见问题与解决方案 ​

语法错误 ​

约束冲突 ​

性能问题 ​

事务问题 ​

14. 总结 ​