SQL 基础与进阶：增删改查、查询优化及约束

数据过滤

-- WHERE 子句
SELECT * FROM TEST_PERSONAL WHERE age > 18;

数据排序

-- ORDER BY 排序 (ASC-升，DESC-降)
SELECT * FROM TEST_PERSONAL ORDER BY age ASC;

逻辑运算符

-- AND & OR 运算符
SELECT * FROM TEST_PERSONAL WHERE name='张三' AND city='北京';

去重

-- SELECT DISTINCT
SELECT DISTINCT age, name, city FROM TEST_PERSONAL;

数据更新

-- UPDATE 更新值
UPDATE TEST_PERSONAL SET name='也也', city='黔江' WHERE id = 11;

数据删除

-- DELETE 删除语句
DELETE FROM TEST_PERSONAL WHERE id > 3 AND id < 10;

进阶部分

限制行数

-- SELECT TOP - 返回限制行数
SELECT TOP n column1, column2, ... FROM table_name;

-- 返回前 5 行
SELECT TOP 5 * FROM table_name;

-- 返回前 10%
SELECT TOP 10 PERCENT * FROM table_name;

-- 结合 ORDER BY 返回排序后限制数据
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;

模糊查询

-- LIKE-WHERE 子句中对字符串进行模糊查询
SELECT column1, column2, ... FROM table_name WHERE column_name LIKE pattern;

-- % 匹配任意数量字符（包括零个）
-- _ 匹配单个字符

-- 查询某字符串开头
SELECT * FROM employees WHERE last_name LIKE 'Sm%';

-- 查询某字符串结尾
SELECT * FROM products WHERE product_name LIKE '%Phone';

-- 查询包含某字符串
SELECT * FROM customers WHERE email LIKE '%@gmail.com';

-- 精确长度与模式
SELECT * FROM users WHERE phone LIKE '___-____-____';

-- 单字符匹配
SELECT * FROM cities WHERE city_code LIKE 'B_3';

别名

-- 列别名
SELECT column_name AS alias_name FROM table_name;

-- 表别名
SELECT t.column1, t.column2 FROM table_name AS t;

区间数据

-- BETWEEN-区间数据 (包含边界值)
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

-- 查找年龄在 20-30 间的员工
SELECT name, age FROM employees WHERE age BETWEEN 20 AND 30;

合并

-- UNION-合并结果集 (自动清除重复行)
SELECT name, department FROM employees UNION SELECT name, department FROM contractors ORDER BY department;

-- UNION ALL-合并两张表所有数据
SELECT product_name FROM sales_2024 UNION ALL SELECT product_name FROM sales_2025;

约束

NOT NULL-约束不能为空

-- 创建表时定义 NOT NULL
CREATE TABLE users (
    id INT PRIMARY KEY,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    age INT
);

-- 修改已有的表添加约束 (MySQL)
-- MODIFY COLUMN 用于修改表中已有列的定义
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL;

UNIQUE-值唯一

CREATE TABLE users ( email VARCHAR(100) UNIQUE );

PRIMARY KEY-主键

-- 列级定义
CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(50) );

-- 或表级定义（支持多列主键）
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    PRIMARY KEY (order_id, product_id)
);

CHECK-条件检查

CREATE TABLE products (
    price DECIMAL(10,2) CHECK (price > 0),
    age INT CHECK (age BETWEEN 0 AND 150)
);