MYSQL的DDL语言和单表查询

MYSQL的DDL语言和单表查询

Mysql介绍

SQL（Structured Query Language）是一种专门用于管理和操作关系型数据库的标准化语言，通过定义、查询、更新和控制数据，为应用程序提供一致且高效的持久化存储方式。它包含数据定义语言（DDL）、数据操纵语言（DML）、数据控制语言（DCL）和事务控制语言（TCL）等子集，分别负责创建与修改数据库结构、增删改查数据、实施权限管理以及保证事务一致性。作为业界广泛采用的开放标准，SQL 使开发者能够以声明式方式描述数据需求，而不必关心底层存储细节。

MySQL 是基于 SQL 标准构建的开源关系型数据库管理系统，它以轻量、性能优良和跨平台著称，广泛应用于 Web 后端、大数据处理及企业信息化等场景。MySQL 对 SQL 语法进行了兼容与扩展，提供事务支持、索引优化、视图、触发器和存储过程等功能，同时拥有主从复制、分区、集群和高可用架构，为不同规模的应用提供弹性的数据解决方案。借助严谨的 SQL 语言规范和 MySQL 丰富的实现机制，开发者能够以一致的接口完成复杂的数据模型设计与高并发读写操作，实现从原型到生产环境的顺畅迁移。

WampServer安装

安装软件

为了简化本地开发环境的搭建，推荐使用 WampServer。该集成环境在 Apache、PHP 和 MySQL 等组件的配置方面进行了优化，适合初学者和开发者快速部署使用。可通过官网下载地址获取：
🔗 http://www.wampserver.com/en/

也可以通过我的百度网盘分享来下载百度网盘分享的文件：wampserver3.1.9_x64.zip

---

在默认设置下，WampServer 中的 phpMyAdmin 是通过无密码方式访问 MySQL 的。若需启用密码验证机制，可按以下步骤进行配置：

1. 启动 WampServer 并点击系统托盘中的图标进入 phpMyAdmin；

2. 在 phpMyAdmin 页面输入账号root，不需要密码直接登录，添加新用户并为其设置密码（建议至少为 root 账户设置密码，但不推荐在日常操作中直接使用 root）；

3. 关闭 phpMyAdmin 页面并退出 WampServer；

4. 编辑配置文件 config.inc.php（路径通常为 <wamp安装目录>/apps/phpmyadmin*/config.inc.php）：

   • 将：

     $cfg['Servers'][$i]['auth_type'] = 'config';
     

     修改为：

     $cfg['Servers'][$i]['auth_type'] = 'cookie';
     

   • 将：

     $cfg['Servers'][$i]['AllowNoPassword'] = true;
     

     修改为：

     $cfg['Servers'][$i]['AllowNoPassword'] = false;
     

   • 在 <?php ... ?> 中添加一行：

     $cfg['blowfish_secret'] = 'your_custom_secret'; // 可自定义任意字符串
     

完成以上设置后，重启 WampServer，即可启用基于密码的 phpMyAdmin 登录验证。

可能遇到的问题

以下是对 WampServer 配置 phpMyAdmin 过程中常见问题的整理与优化说明：

---

🔧 问题一：修改配置后无法打开 phpMyAdmin 页面

原因可能是浏览器缓存或 tmp 文件干扰。可尝试以下措施：

1. 清除浏览器缓存
   特别是在使用 IE8 或更低版本时，浏览器缓存可能导致配置变更后页面无法正常加载。建议使用现代浏览器（如 Chrome、Firefox 或 Opera）以获得更好的兼容性和调试体验。
2. 清空 WampServer 临时文件
   删除 <wamp安装目录>/tmp 目录下的所有文件，然后重新启动 WampServer，有助于清除被缓存的旧配置状态。

---

🔧 问题二：从一开始就无法访问 phpMyAdmin，提示 “Permission Denied”

此问题多见于 Windows 7 系统，原因在于 Apache 对本地访问的默认限制配置。

解决方法：

1. 打开配置文件：

   <wamp安装目录>/alias/phpmyadmin.conf
   

2. 将其中的访问控制部分：

   Deny from all
   Allow from 127.0.0.1
   

   修改为：

   Allow from all
   

3. 保存后重启 WampServer，使配置生效。

---

完成上述操作后，phpMyAdmin 应该可以正常访问。如果问题仍存在，可进一步检查 Apache 的错误日志或防火墙设置。

DDL语句

DDL（Data Definition Language，数据定义语言）是 SQL 的一个子集，用于定义和管理数据库结构，例如创建、修改和删除表、索引、视图等数据库对象。

---

✅ 常见的 DDL 语句包括：

语句	功能
CREATE	创建数据库对象（如表、视图、索引等）
ALTER	修改已有的数据库对象结构
DROP	删除数据库对象
TRUNCATE	快速清空表中的所有数据（但保留结构）
RENAME	重命名数据库对象（某些数据库支持）

---

📌 示例解释：

示例 1：创建表（CREATE TABLE）

CREATE TABLE EMP (EMPNO INT PRIMARY KEY,ENAME VARCHAR(20),SAL DECIMAL(10,2)
);

含义：

• 创建一个名为 EMP 的表
• 包含三个字段：
  • EMPNO: 整型，主键（唯一且非空）
  • ENAME: 字符串，长度最多 20 个字符
  • SAL: 带两位小数的十进制工资字段

---

示例 2：修改表结构（ALTER TABLE）

ALTER TABLE EMP ADD HIREDATE DATE;

含义：

• 给 EMP 表增加一个 HIREDATE 字段，类型为日期。

---

示例 3：删除表（DROP TABLE）

DROP TABLE EMP;

含义：

• 删除整个 EMP 表，包括表结构和所有数据。

---

示例 4：清空表数据（TRUNCATE TABLE）

TRUNCATE TABLE EMP;

含义：

• 清空 EMP 表中的所有数据，但保留表结构，适用于重置数据。

---

总结：

DDL 语句主要用于创建、修改和删除数据库结构本身，是数据库设计和管理的基础部分。

使用SQL DDL语句建立以下数据库模式，表一：

EMP表（员工表）

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	17-Dec-90	13750		20
7499	ALLEN	SALESMAN	7698	20-FEB-89	19000	6400	30
7521	WARD	SALESMAN	7698	22-FEB-93	18500	4250	30
7566	JONES	MANAGER	7839	02-APR-89	26850		20
7654	MARTIN	SALESMAN	7698	28-SEP-97	15675	3500	30
7698	BLAKE	MANAGER	7839	01-MAY-90	24000		30
7782	CLARK	MANAGER	7839	09-JUN-88	27500		10
7788	SCOTT	ANALYST	7566	19-APR-87	19500		20
7839	KING	PRESIDENT		17-NOV-83	82500		10
7844	TURNER	SALESMAN	7698	08-SEP-92	18500	6250	30
7876	ADAMS	CLERK	7788	23-MAY-96	11900		20
7900	JAMES	CLERK	7698	03-DEC-95	12500		30
7902	FORD	ANALYST	7566	03-DEC-91	21500		20
7934	MILLER	CLERK	7782	23-JAN-95	13250		10
3258	GREEN	SALESMAN	4422	24-Jul-95	18500	2750	50
4422	STEVENS	MANAGER	7839	14-Jan-94	24750		50
6548	BARNES	CLERK	4422	16-Jan-95	11950		50

表二：

DEPT表（部门表）

DEPTNO	DNAME	LOC
10	ACCOUNTING	LONDON
20	RESEARCH	PRESTON
30	SALES	LIVERPOOL
40	OPERATIONS	STAFFORD
50	MARKETING	LUTON

---

✅ 1. 创建数据库

CREATE DATABASE COMPANY;

---

✅ 2. 选择数据库（以 MySQL 为例）

USE COMPANY;

---

✅ 3. 创建 DEPT 表（部门表）

CREATE TABLE DEPT (DEPTNO INT PRIMARY KEY,DNAME VARCHAR(20),LOC VARCHAR(20)
);

Primary Key（主键） 是数据库表中用于唯一标识每一行记录的字段或字段组合。它是数据库设计中的核心概念，保证每条数据的唯一性和可识别性。

---

✅ 主键的特征：

特征	说明
🔑 唯一性	每条记录的主键值不能重复
🚫 非空性	主键字段不能为 NULL
🌐 通常是 ID	常用如 id、empno、student_no 作为主键
📎 可作为外键	可被其他表引用形成“外键”关联
⚠️ 每张表只能有一个主键	但可以由多个字段组合构成“复合主键”

---

📌 示例：

CREATE TABLE STUDENT (STUDENT_ID INT PRIMARY KEY,NAME VARCHAR(50)
);

这里的 STUDENT_ID 就是主键，不能重复也不能为 NULL，系统会用它来唯一标识每一个学生。

---

📌 复合主键（Composite Primary Key）：

CREATE TABLE COURSE_SELECTION (STUDENT_ID INT,COURSE_ID INT,PRIMARY KEY (STUDENT_ID, COURSE_ID)
);

表示一个学生只能选一次同一门课。

---

总结记忆：

主键就是“身份证号”，用来唯一识别一条数据记录，不能缺、不能重。

VARCHAR(20) 是一种 SQL 数据类型，表示可变长度的字符串字段，最多可以存储 20 个字符。

---

✅ 分解解释：

• VARCHAR：Variable Character（可变字符）
• (20)：最大长度为 20 个字符
• 可变长度：只占用实际字符长度的存储空间（比 CHAR(20) 更节省）

📌 与 CHAR(20) 的区别：

类型	特点	示例存储
CHAR(20)	固定长度，总是占用 20 个字符空间	'Tom' 会补空格到 20 字符
VARCHAR(20)	可变长度，只存实际字符	'Tom' 只占 3 字符

---

✅ 4. 创建 EMP 表（员工表）

CREATE TABLE EMP (EMPNO INT PRIMARY KEY,ENAME VARCHAR(20),JOB VARCHAR(20),MGR INT,HIREDATE DATE,SAL DECIMAL(10,2),COMM DECIMAL(10,2),DEPTNO INT,FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),FOREIGN KEY (MGR) REFERENCES EMP(EMPNO)
);

在数据库中，FOREIGN KEY（外键） 是用来建立两个表之间关联关系的一种约束，确保一个表中的某个字段值必须来自另一个表中某字段的已有值。

---

✅ 外键的作用：

功能	说明
建立表与表之间的引用关系	类似于“员工属于某个部门”，“订单属于某个客户”
保证数据一致性与完整性	外键值必须在关联表中存在
用于构建主表-从表关系	例如：EMP.DEPTNO 必须引用 DEPT.DEPTNO

---

📌 示例解析：

FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

表示当前表（如 EMP）中的 DEPTNO 字段，必须是 DEPT 表中已有的 DEPTNO 值。即：员工只能隶属于已有部门。

FOREIGN KEY (MGR) REFERENCES EMP(EMPNO)

表示员工表中 MGR（经理编号）字段必须引用另一个员工的 EMPNO（员工编号）。即：一个员工的上级必须是系统中已有的另一个员工。

---

🎯 例子说明：

-- DEPT 表（主表）
DEPTNO | DNAME
-------|-------
10     | ACCOUNTING
20     | RESEARCH-- EMP 表（从表）
EMPNO | ENAME | DEPTNO
------|--------|-------
1001  | JACK   | 10     ✅ OK
1002  | LISA   | 30     ❌ 报错（DEPT 表中无 DEPTNO = 30）

---

✅ 总结记忆：

外键 = “我依赖于别人”
FOREIGN KEY (A) REFERENCES B(C) → 表示 A 字段必须等于表 B 中字段 C 的值。

这样就完成了数据库、表结构及其之间的关系定义。

插入数据

✅ 插入 DEPT 表数据（MySQL）

INSERT INTO dept (DEPTNO, DNAME, LOC) VALUES (10, 'ACCOUNTING', 'LONDON');
INSERT INTO dept (DEPTNO, DNAME, LOC) VALUES (20, 'RESEARCH', 'PRESTON');
INSERT INTO dept (DEPTNO, DNAME, LOC) VALUES (30, 'SALES', 'LIVERPOOL');
INSERT INTO dept (DEPTNO, DNAME, LOC) VALUES (40, 'OPERATIONS', 'STAFFORD');
INSERT INTO dept (DEPTNO, DNAME, LOC) VALUES (50, 'MARKETING', 'LUTON');

---

✅ 插入 EMP 表数据（MySQL）

INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL, '1983-11-17', 82500, NULL, 10);
INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839, '1988-06-09', 27500, NULL, 10);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782, '1995-01-23', 13250, NULL, 10);
INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, '1989-04-02', 26850, NULL, 20);
INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 19500, NULL, 20);
INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566, '1991-12-03', 21500, NULL, 20);
INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, '1990-12-17', 13750, NULL, 20);
INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788, '1996-05-23', 11900, NULL, 20);
INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1990-05-01', 24000, NULL, 30);
INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1989-02-20', 19000, 6400, 30);
INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, '1993-02-22', 18500, 4250, 30);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1997-09-28', 15675, 3500, 30);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1992-09-08', 18500, 6250, 30);
INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698, '1995-12-03', 12500, NULL, 30);
INSERT INTO EMP VALUES (4422, 'STEVENS', 'MANAGER', 7839, '1994-01-14', 24750, NULL, 50);
INSERT INTO EMP VALUES (3258, 'GREEN', 'SALESMAN', 4422, '1995-07-24', 18500, 2750, 50);
INSERT INTO EMP VALUES (6548, 'BARNES', 'CLERK', 4422, '1995-01-16', 11950, NULL, 50);

---

⚠️ 插入顺序已按外键依赖排序，确保不会触发 FOREIGN KEY 约束错误。

SQL语句

✅ 基础查询

---

1. 列出员工表中的员工姓名、编号、工资和部门编号

SELECT ENAME, EMPNO, SAL, DEPTNO FROM EMP;

📘 解释：简单列出指定字段，不涉及筛选或排序。

---

2. 列出所有不同的工作种类（不重复）

SELECT DISTINCT JOB FROM EMP;

📘 解释：使用 DISTINCT 消除重复。

---

3. 查找工资低于 £20000 的员工姓名

SELECT ENAME FROM EMP WHERE SAL < 20000;

📘 解释：通过 WHERE SAL < 20000 筛选员工。where为筛选条件

---

4. 查找不是在 10 号部门的 Clerk（文员）

SELECT ENAME FROM EMP WHERE JOB = 'CLERK' AND DEPTNO <> 10;

📘 解释：限定职业为 CLERK，排除 DEPTNO = 10。

---

5. 查找职位为 PRESIDENT 的员工姓名

SELECT ENAME FROM EMP WHERE JOB = 'PRESIDENT';

📘 解释：直接按职位查询。

---

6. 查找姓氏以 S 结尾的员工姓名

SELECT ENAME FROM EMP WHERE ENAME LIKE '%S';

📘 解释：LIKE 模糊匹配 %S 表示以 S 结尾。

---

7. 列出所有有提成（COMM）的员工

SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;

📘 解释：使用 IS NOT NULL 找出 COMM 有值的员工。

---

8. 按 COMM/SAL 倒序列出所有 SALESMAN

SELECT ENAME, COMM, SAL FROM EMP 
WHERE JOB = 'SALESMAN' 
ORDER BY COMM / SAL DESC;

📘 解释：先筛选销售人员，再以提成与工资比排序。

---

9. 列出没有上司的员工的姓名、职位、工资和提成

SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE MGR IS NULL;

📘 解释：MGR IS NULL 表示该员工无上司。

---

10. 列出 30 号部门中工资 ≥ 18000 的销售员

SELECT ENAME FROM EMP 
WHERE JOB = 'SALESMAN' AND DEPTNO = 30 AND SAL >= 18000;

📘 解释：多条件联合筛选。

---

✅ 扩展查询

---

EX2(1)：查找在 Luton 地点工作的员工姓名和工资

SELECT E.ENAME, E.SAL 
FROM EMP E 
JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
WHERE D.LOC = 'LUTON';

📘 解释：通过联表查找 LOC = 'LUTON' 的员工。

---

EX2(2)：联接 DEPT 和 EMP 表，并按部门编号排序

SELECT E.ENAME, D.DEPTNO, D.DNAME 
FROM EMP E 
JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
ORDER BY D.DEPTNO;

📘 解释：显示员工与部门信息并按 DEPTNO 排序。

---

EX2(3)：列出在 SALES 部门工作的销售员姓名

SELECT E.ENAME 
FROM EMP E 
JOIN DEPT D ON E.DEPTNO = D.DEPTNO 
WHERE D.DNAME = 'SALES' AND E.JOB = 'SALESMAN';

📘 解释：两个表联合筛选部门名与职位。

---

EX2(4)：列出没有员工的部门

SELECT D.DNAME 
FROM DEPT D 
LEFT JOIN EMP E ON D.DEPTNO = E.DEPTNO 
WHERE E.EMPNO IS NULL;

📘 解释：使用 LEFT JOIN + IS NULL 筛选无人部门。

✅ 为什么这样写是对的？

场景：

我们要找的是：在 DEPT 表中，没有任何员工关联的部门。

分析：

• LEFT JOIN 会保留 DEPT 表中的所有记录；
• 如果某个部门在 EMP 表中找不到匹配员工，它右边 EMP 表的字段（如 EMPNO）就是 NULL；
• 所以，WHERE E.EMPNO IS NULL 就能精准选出“没有员工的部门”。

---

✅ 举个例子：

假设 DEPT 表中有部门 10, 20, 30, 40, 50
而 EMP 表中只有部门 10, 20, 30, 50 的员工

执行 LEFT JOIN 后：

D.DEPTNO	D.DNAME	E.EMPNO
10	ACCOUNTING	7782
20	RESEARCH	7902
30	SALES	7499
40	OPERATIONS	NULL ✅
50	MARKETING	4422

就能通过 WHERE E.EMPNO IS NULL 把 40 号部门筛出来。

---

❗ 所以不能用 INNER JOIN，也不能直接 JOIN（默认是 INNER JOIN），因为：

• INNER JOIN 只保留“左右两边都匹配成功”的数据；
• 不会保留“没有员工”的部门。

---

EX2(5)：列出工资超过其经理的员工及其经理信息

SELECT A.ENAME, A.SAL, B.ENAME, B.SAL
FROM EMP A, EMP B
WHERE A.MGR = B.EMPNOAND A.SAL > B.SAL;

📘 解释：员工和经理来自同一张表，用自连接实现。

---

✅ 正确性分析：

部分	解释
FROM emp2021280369 A, emp2021280369 B	自连接 EMP 表，一张表表示员工（A），一张表示经理（B）
A.MGR = B.EMPNO	表示 A 的上司是 B
A.SAL > B.SAL	员工的工资高于经理的工资
SELECT A.ENAME, A.SAL, B.ENAME, B.SAL	分别列出员工与其经理的姓名和工资

---

🔄 可读性更强的写法（使用显式 JOIN）：

SELECT E.ENAME AS Employee, E.SAL AS Emp_Salary,M.ENAME AS Manager, M.SAL AS Mgr_Salary
FROM EMP E
JOIN EMP M ON E.MGR = M.EMPNO
WHERE E.SAL > M.SAL;

• JOIN 替代逗号分隔方式，更推荐用于正式开发；
• 添加别名让字段含义更清晰（Employee vs Manager）

---

EX2(6)：列出上司为 BLAKE 的员工姓名

SELECT E.ENAME 
FROM EMP E 
JOIN EMP M ON E.MGR = M.EMPNO 
WHERE M.ENAME = 'BLAKE';

📘 解释：同样使用自连接，查找以 BLAKE 为上司的员工。