SQL 数据库操作语言详解

最新推荐文章于 2025-12-20 13:10:13 发布

原创最新推荐文章于 2025-12-20 13:10:13 发布 · 1k 阅读

14 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #开发语言

1. SQL 语言概述

SQL（Structured Query Language）是用于管理关系型数据库的标准语言，主要分为以下几个子语言：

- DQL（数据查询语言）：SELECT - 用于数据查询
- DML（数据操作语言）：INSERT, DELETE, UPDATE - 用于数据操作
- DCL（数据控制语言）：GRANT, REVOKE - 用于权限控制
- DDL（数据定义语言）：CREATE, ALTER, DROP - 用于数据库对象定义
- TCL（事务控制语言）：COMMIT, ROLLBACK - 用于事务控制

2. DDL（数据定义语言）

2.1 创建表

```sql
CREATE TABLE t_student(
`stuid` INT(4) AUTO_INCREMENT PRIMARY KEY, -- 行级约束
`sname` VARCHAR(15) NOT NULL,
`ssex` VARCHAR(1) NOT NULL DEFAULT '男',
`age` INT(3) CHECK(age>=0 AND age<=100),
`email` VARCHAR(30) UNIQUE
);
```

2.2 查看表结构

```sql
-- 查看建表语句
SHOW CREATE TABLE t_student;

-- 查看表结构
DESC t_student;
```

2.3 修改表结构

```sql
-- 添加字段
ALTER TABLE t_student ADD cid INT(4);

-- 添加字段并指定位置
ALTER TABLE t_student ADD cid INT(4) FIRST; -- 添加到第一列
ALTER TABLE t_student ADD cid INT(4) AFTER ssex; -- 添加到指定列后

-- 删除字段
ALTER TABLE t_student DROP cid;

-- 修改字段
ALTER TABLE t_student MODIFY cid INT(5); -- 修改字段类型
ALTER TABLE t_student CHANGE cid class_id INT(4); -- 修改字段名和类型

-- 添加外键约束
ALTER TABLE t_student ADD CONSTRAINT fk_stu_cid
FOREIGN KEY (cid) REFERENCES t_class(cid);
```

2.4 完整性约束

- 主键（PRIMARY KEY）：非空 + 唯一
- 非空（NOT NULL）
- 自增（AUTO_INCREMENT）
- 默认值（DEFAULT）
- 检查（CHECK）
- 唯一（UNIQUE）
- 外键（FOREIGN KEY）

2.5 删除表

```sql
DROP TABLE t_student; -- 删除表及所有数据
```

3. DML（数据操作语言）

3.1 插入数据

```sql
-- 指定字段插入
INSERT INTO t_student(sname, ssex, age, email, cid)
VALUES('李方雷', DEFAULT, 10, '123456', 1);

-- 简写形式（为所有字段赋值）
INSERT INTO t_student VALUES(NULL, '李方雷', DEFAULT, 10, '123456', 1);
```

3.2 更新数据

```sql
-- 更新所有记录（不推荐）
UPDATE t_student SET age = 20;

-- 更新指定记录（推荐）
UPDATE t_student SET age = 20 WHERE stuid = 1;
```

3.3 删除数据

```sql
-- 删除所有记录（不推荐）
DELETE FROM t_student;

-- 删除指定记录（推荐）
DELETE FROM t_student WHERE stuid = 1;
```

3.4 删除方式比较

| 操作 | 类型 | 回滚 | 索引 | 效率 |
|------|------|------|------|------|
| DELETE | DML | 支持 | 不重置 | 低 |
| TRUNCATE | DDL | 不支持 | 重置 | 高 |
| DROP | DDL | 不支持 | 删除 | 最高 |

4. DQL（数据查询语言）

4.1 基本语法结构

```sql
SELECT 字段1, 字段2, ..., 字段n
FROM 表名
WHERE 条件
GROUP BY 分组字段
HAVING 分组条件
ORDER BY 排序字段
LIMIT 分页参数;
```

4.2 执行顺序

1. FROM - 确定表
2. WHERE - 筛选条件
3. GROUP BY - 分组
4. HAVING - 分组条件
5. SELECT - 确定显示内容
6. ORDER BY - 排序
7. LIMIT - 分页

4.3 SELECT 子句详解

```sql
-- 查询所有字段（不推荐）
SELECT FROM emp;

-- 查询指定字段
SELECT empno, ename, sal FROM emp;

-- 使用别名
SELECT empno AS empn, ename AS '员工姓名' FROM emp;

-- 计算字段
SELECT ename, sal12+IFNULL(comm, 0) AS '年薪' FROM emp;

-- 去重查询
SELECT DISTINCT mgr FROM emp WHERE mgr IS NOT NULL;
```

4.4 ORDER BY 子句

```sql
-- 多字段排序
SELECT FROM emp ORDER BY deptno ASC, mgr DESC;
```

4.5 WHERE 子句

```sql
-- 关系运算符
SELECT FROM emp WHERE sal > 2000;
SELECT FROM emp WHERE deptno != 10;

-- 逻辑运算符
SELECT FROM emp WHERE mgr = 7521 AND deptno != 10;

-- 模糊查询
SELECT FROM emp WHERE ename LIKE '%a%'; -- 包含a
SELECT FROM emp WHERE ename LIKE '_a%'; -- 第二个字母是a
SELECT FROM emp WHERE ename LIKE 'as%'; -- 以as开头
SELECT FROM emp WHERE ename LIKE '%a'; -- 以a结尾

-- NULL值查询
SELECT FROM emp WHERE comm IS NULL;
SELECT FROM emp WHERE comm IS NOT NULL;

-- 使用函数
SELECT FROM emp WHERE DATEDIFF(CURDATE(), hiredate) > 15000;
```

4.6 常用函数

字符串函数

```sql
SELECT REPLACE(ename, SUBSTRING(ename, 2, LENGTH(ename)-2), ''), ename FROM emp;
```

时间函数

```sql
-- 系统时间
SELECT CURDATE(), CURTIME(), NOW(), SYSDATE();

-- 日期计算
SELECT DAYOFYEAR(CURDATE()) FROM DUAL;
SELECT DATEDIFF(CURDATE(), '2004-4-23') FROM DUAL;

-- 日期格式化
SELECT DATE_FORMAT(hiredate, '%Y:%m:%d') FROM emp;

-- 日期加减
SELECT DATE_ADD(hiredate, INTERVAL 3 MONTH) FROM emp;
```

流程函数

```sql
SELECT ename, job,
CASE deptno
WHEN 10 THEN '开发部'
WHEN 20 THEN '测试部'
WHEN 30 THEN '运维部'
END AS 部门名称
FROM emp;
```