第2章：SQL基础

第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     -- 禁止更新
);

外键约束类型

1. ON DELETE CASCADE：级联删除
2. ON UPDATE RESTRICT：限制更新
3. 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;  -- 第一页