第2章：SQL基础

2.1 SQL语言介绍

2.1.1 SQL发展历史

SQL的起源
SQL标准的演变
不同数据库的SQL实现

2.1.2 SQL语言分类

数据定义语言 (DDL):用于定义和修改数据库的结构，包括数据库、表、索引、视图等对象的创建、修改和删除。
数据操作语言 (DML):用于对数据库中存储的数据进行操作，包括插入、删除、修改等。
数据查询语言 (DQL):用于从数据库中查询数据，包括SELECT语句、聚合函数、子查询等。
数据控制语言 (DCL):用于控制数据库的访问权限和安全性，包括授权、回收权限、创建用户等。

2.2 数据定义语言(DDL)

2.2.1 CREATE DATABASE

语法:CREATE DATABASE <database_name> [CHARACTER SET <character_set>] [COLLATE <collation>]
指定字符集和排序规则，例如UTF-8和utf8mb4_unicode_ci
数据库命名规范，例如使用小写字母、数字和下划线，且以字母开头

2.2.2 CREATE TABLE

表结构定义
列类型和约束
创建复杂表结构

2.2.3.1 表结构定义

语法:CREATE TABLE <table_name> (<column_name> <data_type> [NOT NULL][DEFAULT <value>][AUTO_INCREMENT], ...)
例如创建一个简单的用户表users，包含id、name、email三个列

2.2.3 ALTER TABLE

修改表结构
添加、删除、修改列
重命名表

2.2.3.1 修改表结构

语法:ALTER TABLE <table_name> ADD <column_name> <data_type> [NOT NULL][DEFAULT <value>][AUTO_INCREMENT]
例如添加一个age列到users表中

2.2.4 DROP TABLE

删除表的方法
删除表的注意事项
级联删除
例如删除users表

主键和外键详解

主键（Primary Key）

定义：唯一标识表中每一行的列或列组合
特点：
1. 每个表只能有一个主键
2. 主键值必须唯一
3. 不能包含NULL值

-- 创建带主键的表
CREATE TABLE users (id INT PRIMARY KEY,           -- 单列主键username VARCHAR(50) NOT NULL
);-- 复合主键
CREATE TABLE order_items (order_id INT,product_id INT,PRIMARY KEY (order_id, product_id)  -- 多列组成主键
);-- 自增主键
CREATE TABLE products (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(100)
);

外键（Foreign Key）

定义：引用另一个表主键的列
作用：建立表与表之间的关联关系
特点：
1. 确保数据引用的完整性
2. 可以设置级联操作

-- 创建主表
CREATE TABLE departments (dept_id INT PRIMARY KEY,dept_name VARCHAR(50)
);-- 创建从表，使用外键
CREATE TABLE employees (emp_id INT PRIMARY KEY,emp_name VARCHAR(50),dept_id INT,FOREIGN KEY (dept_id) REFERENCES departments(dept_id)ON DELETE CASCADE      -- 级联删除ON UPDATE RESTRICT     -- 禁止更新
);

外键约束类型

ON DELETE CASCADE：级联删除
ON UPDATE RESTRICT：限制更新
ON DELETE SET NULL：置空关联记录

最佳实践

选择合适的数据类型作为主键
避免使用可变列作为主键
考虑使用自增ID
为外键添加适当约束
注意性能影响

常见问题

主键不能重复
外键必须引用存在的主键
复合主键需要所有列的组合唯一

2.3 数据操作语言(DML)

2.3.1 INSERT

插入单行数据，例如插入一个新的用户记录

2.3.2 UPDATE

更新单行数据
更新多行数据
条件更新

2.3.3 DELETE

删除单行数据
删除多行数据
条件删除
删除与性能

2.4 数据查询语言(DQL)

2.4.1 SELECT基本查询

简单查询语法
查询指定列
去重查询

2.4.2 WHERE条件

比较运算符
逻辑运算符
模糊查询
NULL值处理

2.4.3 ORDER BY排序

升序和降序排序
多列排序
排序性能优化

2.4.4 LIMIT分页

限制返回行数
分页查询
分页性能优化

代码实践

2.1 数据定义语言(DDL)实践

2.1.1 创建数据库

-- 创建新数据库
CREATE DATABASE IF NOT EXISTS bookstore;-- 使用数据库
USE bookstore;

2.1.2 创建表结构

-- 创建图书表
CREATE TABLE books (id INT PRIMARY KEY AUTO_INCREMENT,title VARCHAR(100) NOT NULL,author VARCHAR(50),price DECIMAL(10, 2),stock INT DEFAULT 0,publish_date DATE
);-- 创建作者表
CREATE TABLE authors (id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50) NOT NULL,country VARCHAR(50),birth_date DATE
);

2.1.3 修改表结构

-- 添加新列
ALTER TABLE books 
ADD COLUMN isbn VARCHAR(20) UNIQUE;-- 修改列类型
ALTER TABLE books 
MODIFY COLUMN price DECIMAL(12, 2);-- 删除列
ALTER TABLE books 
DROP COLUMN stock;

2.2 数据操作语言(DML)实践

2.2.1 插入数据

-- 插入单行数据
INSERT INTO authors (name, country, birth_date) 
VALUES ('刘慈欣', '中国', '1963-06-23');-- 批量插入数据
INSERT INTO books (title, author, price, publish_date, isbn) 
VALUES 
('三体', '刘慈欣', 39.80, '2008-01-01', '9787530216835'),
('流浪地球', '刘慈欣', 29.90, '2011-05-01', '9787536693968');

2.2.2 更新数据

-- 更新单行数据
UPDATE books 
SET price = 45.50 
WHERE title = '三体';-- 批量更新
UPDATE books 
SET price = price * 1.1 
WHERE author = '刘慈欣';

2.2.3 删除数据

-- 删除单行数据
DELETE FROM books 
WHERE title = '流浪地球';-- 条件删除
DELETE FROM books 
WHERE price < 20 AND stock = 0;

2.3 数据查询语言(DQL)实践

2.3.1 基本查询

-- 查询所有图书
SELECT * FROM books;-- 查询指定列
SELECT title, author, price FROM books;-- 去重查询
SELECT DISTINCT author FROM books;

2.3.2 条件查询

-- 使用WHERE条件
SELECT * FROM books 
WHERE price > 30;-- 模糊查询
SELECT * FROM books 
WHERE title LIKE '%三体%';-- 多条件查询
SELECT * FROM books 
WHERE author = '刘慈欣' AND price < 50;

2.3.3 排序和分页

-- 按价格升序排序
SELECT * FROM books 
ORDER BY price ASC;-- 多列排序
SELECT * FROM books 
ORDER BY author ASC, price DESC;-- 分页查询（每页10条）
SELECT * FROM books 
ORDER BY id 
LIMIT 0, 10;  -- 第一页