当前位置： 首页 > java >正文

MYSQL的DDL语言和单表查询

java 2025/5/8 18:54:49

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表（员工表）

EMPNOENAMEJOBMGRHIREDATESALCOMMDEPTNO
7369SMITHCLERK790217-Dec-901375020
7499ALLENSALESMAN769820-FEB-8919000640030
7521WARDSALESMAN769822-FEB-9318500425030
7566JONESMANAGER783902-APR-892685020
7654MARTINSALESMAN769828-SEP-9715675350030
7698BLAKEMANAGER783901-MAY-902400030
7782CLARKMANAGER783909-JUN-882750010
7788SCOTTANALYST756619-APR-871950020
7839KINGPRESIDENT17-NOV-838250010
7844TURNERSALESMAN769808-SEP-9218500625030
7876ADAMSCLERK778823-MAY-961190020
7900JAMESCLERK769803-DEC-951250030
7902FORDANALYST756603-DEC-912150020
7934MILLERCLERK778223-JAN-951325010
3258GREENSALESMAN442224-Jul-9518500275050
4422STEVENSMANAGER783914-Jan-942475050
6548BARNESCLERK442216-Jan-951195050

表二：

DEPT表（部门表）

DEPTNODNAMELOC
10ACCOUNTINGLONDON
20RESEARCHPRESTON
30SALESLIVERPOOL
40OPERATIONSSTAFFORD
50MARKETINGLUTON

✅ 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');

image-20250506113249749

✅ 插入 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);

image-20250506113307895

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

SQL语句

✅ 基础查询

1. 列出员工表中的员工姓名、编号、工资和部门编号
SELECT ENAME, EMPNO, SAL, DEPTNO FROM EMP;

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

image-20250506114854708

2. 列出所有不同的工作种类（不重复）
SELECT DISTINCT JOB FROM EMP;

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

image-20250506114846588

3. 查找工资低于 £20000 的员工姓名
SELECT ENAME FROM EMP WHERE SAL < 20000;

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

image-20250506114839651

4. 查找不是在 10 号部门的 Clerk（文员）
SELECT ENAME FROM EMP WHERE JOB = 'CLERK' AND DEPTNO <> 10;

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

image-20250506114832814

5. 查找职位为 PRESIDENT 的员工姓名
SELECT ENAME FROM EMP WHERE JOB = 'PRESIDENT';

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

image-20250506114825307

6. 查找姓氏以 S 结尾的员工姓名
SELECT ENAME FROM EMP WHERE ENAME LIKE '%S';

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

image-20250506114817763

7. 列出所有有提成（COMM）的员工
SELECT ENAME FROM EMP WHERE COMM IS NOT NULL;

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

image-20250506114808644

8. 按 COMM/SAL 倒序列出所有 SALESMAN
SELECT ENAME, COMM, SAL FROM EMP 
WHERE JOB = 'SALESMAN' 
ORDER BY COMM / SAL DESC;

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

image-20250506114759440

9. 列出没有上司的员工的姓名、职位、工资和提成
SELECT ENAME, JOB, SAL, COMM FROM EMP WHERE MGR IS NULL;

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

image-20250506114752107

10. 列出 30 号部门中工资 ≥ 18000 的销售员
SELECT ENAME FROM EMP 
WHERE JOB = 'SALESMAN' AND DEPTNO = 30 AND SAL >= 18000;

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

image-20250506114744249

✅ 扩展查询

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' 的员工。

image-20250506114733508

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 排序。

image-20250506114725183

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';

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

image-20250506114933174

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.DEPTNOD.DNAMEE.EMPNO
10ACCOUNTING7782
20RESEARCH7902
30SALES7499
40OPERATIONSNULL ✅
50MARKETING4422

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

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

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

image-20250506115239377

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）

image-20250506120035970

EX2(6)：列出上司为 BLAKE 的员工姓名
SELECT E.ENAME 
FROM EMP E 
JOIN EMP M ON E.MGR = M.EMPNO 
WHERE M.ENAME = 'BLAKE';

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

image-20250506120127256

http://www.xdnf.cn/news/4172.html

相关文章：

  • 完全免费的PDF电子发票批量辅助打印工具
  • vue3+ts继续学习
  • js var a=如果ForRemove=true,是“normal“,否则为“bold“
  • 2025-05-06 事业-独立开发项目-记录
  • 软件代码签名证书SSL如何选择？
  • C++复习2
  • Spring Boot之MCP Client开发全介绍
  • 二叉树—中序遍历—非递归
  • 两数之和(暴力+哈希查找)
  • Linux[Makefile]
  • ffmpeg录音测试
  • 爬虫程序中如何添加异常处理？
  • Vi/Vim 编辑器详细指南
  • Facebook如何运用AI实现元宇宙的无限可能？
  • DC-DC降压型开关电源（Buck Converter）设计中，开关频率（f sw​ ）、滤波电感（L）和滤波电容（C out​ ）的关系和取舍
  • uniapp 全局混入：监听路由变化，路由变化即执行
  • 嵌入式openharmony标准鸿蒙系统驱动开发基本原理与流程
  • openssl 生成自签名证书实现接口支持https
  • 【coze】手册小助手（提示词、知识库、交互、发布）
  • C++中指针使用详解（4）指针的高级应用汇总
  • 人工智能对人类的影响
  • 【Hive入门】Hive安全管理与权限控制：审计日志全解析，构建完善的操作追踪体系
  • kubeadm部署k8s
  • openwrt 使用quilt 打补丁（patch）
  • 基于图像处理的道路监控与路面障碍检测系统设计与实现 (源码+定制+开发） 图像处理 计算机视觉 道路监控系统 视频帧分析 道路安全监控 城市道路管理
  • 计算机视觉与深度学习 ｜ 基于数字图像处理的裂缝检测与识别系统（matlab代码）
  • 【Python系列】Python 中的 HTTP 请求处理
  • OpenAI的“四面楚歌”：从营利到非营利，一场关于AGI控制权的革命
  • 信息时代的政治重构：网络空间与主权的未来
  • 搭建spark yarn 模式的集群