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

mysql 进阶语法新手必看

news 2026/4/29 15:20:26

数据库关系模型实现示例

一对多关系实现

部门表（主表）和员工表（从表）的创建：

CREATE TABLE dept ( dept_id INT PRIMARY KEY, dept_name VARCHAR(20) ); CREATE TABLE emp ( emp_id INT PRIMARY KEY, emp_name VARCHAR(20), dept_id INT, CONSTRAINT emp_connect FOREIGN KEY (dept_id) REFERENCES dept(dept_id) );

插入测试数据：

INSERT INTO dept VALUES (1001, '研发部'), (1002, '销售部'), (1003, '财务部'), (1004, '后勤部'); INSERT INTO emp VALUES (1, '张三', 1001), (2, '李四', 1001), (3, '王五', 1002);

查询结果示例：

dept表数据： +---------+-----------+ | dept_id | dept_name | +---------+-----------+ | 1001 | 研发部 | | 1002 | 销售部 | | 1003 | 财务部 | | 1004 | 后勤部 | +---------+-----------+ emp表数据： +--------+----------+---------+ | emp_id | emp_name | dept_id | +--------+----------+---------+ | 1 | 张三 | 1001 | | 2 | 李四 | 1001 | | 3 | 王五 | 1002 | +--------+----------+---------+

多对多关系实现

学生表、课程表和中间关系表的创建：

CREATE TABLE student( sid INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(10), gender VARCHAR(10) ); CREATE TABLE class( cid INT PRIMARY KEY AUTO_INCREMENT, cname VARCHAR(20) ); CREATE TABLE stu_class( sid INT, cid INT, score DOUBLE, CONSTRAINT fk_stu_class_sid FOREIGN KEY (sid) REFERENCES student(sid), CONSTRAINT fk_stu_class_cid FOREIGN KEY (cid) REFERENCES class(cid) );

插入测试数据：

INSERT INTO student VALUES (1, '张三', '男'), (2, '李四', '女'), (3, '王五', '男'); INSERT INTO class VALUES (101, 'Java编程'), (102, 'MySQL数据库'), (103, 'Web前端'); INSERT INTO stu_class VALUES (1, 101, 90), (1, 102, 85), (2, 102, 88);

多表关联查询示例：

SELECT s.name, c.cname, sc.score FROM student s JOIN stu_class sc ON s.sid = sc.sid JOIN class c ON sc.cid = c.cid;

查询结果：

+------+--------------+-------+ | name | cname | score | +------+--------------+-------+ | 张三 | Java编程 | 90 | | 张三 | MySQL数据库 | 85 | | 李四 | MySQL数据库 | 88 | +------+--------------+-------+

连接查询示例

隐式内连接（SQL92标准）：

SELECT e.emp_name, d.dept_name FROM emp e, dept d WHERE e.dept_id = d.dept_id;

显式内连接（SQL99标准）：

SELECT e.emp_name, d.dept_name FROM emp e INNER JOIN dept d ON e.dept_id = d.dept_id;

左外连接查询：

SELECT s.name, IFNULL(c.cname, '未选课') AS course FROM student s LEFT JOIN stu_class sc ON s.sid = sc.sid LEFT JOIN class c ON sc.cid = c.cid;

分组统计示例

统计各部门人数：

SELECT d.dept_name, COUNT(e.emp_id) AS emp_count FROM dept d LEFT JOIN emp e ON d.dept_id = e.dept_id GROUP BY d.dept_id;

统计学生选课情况：

SELECT s.name, COUNT(sc.cid) AS course_count, AVG(sc.score) AS avg_score FROM student s LEFT JOIN stu_class sc ON s.sid = sc.sid GROUP BY s.sid;

外键约束验证

尝试插入非法数据：

-- 会失败，因为dept_id 1005不存在 INSERT INTO emp VALUES (10, '测试', 1005);

错误信息：

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

约束管理

删除外键约束：

ALTER TABLE emp DROP FOREIGN KEY emp_connect;

添加外键约束：

ALTER TABLE emp ADD CONSTRAINT emp_connect FOREIGN KEY (dept_id) REFERENCES dept(dept_id);

SQL JOIN 操作总结

左外连接 (LEFT OUTER JOIN)

左外连接返回左表的所有记录，无论右表是否有匹配的记录。如果右表没有匹配的记录，结果中右表的列将显示为 NULL。

SELECT * FROM dept d LEFT OUTER JOIN emp e ON d.dept_id = e.dept_id;

结果示例：

dept_id | dept_name | emp_id | emp_name | ... --------+-----------+--------+----------+----- 1 | HR | 101 | Alice | ... 2 | IT | 102 | Bob | ... 3 | Finance | NULL | NULL | ...

多表左外连接

可以连续使用左外连接关联多个表，语法如下：

SELECT * FROM A LEFT JOIN B ON A.id = B.a_id LEFT JOIN C ON A.id = C.a_id LEFT JOIN D ON A.id = D.a_id LEFT JOIN E ON A.id = E.a_id;

结果示例：

A.id | A.name | B.value | C.value | D.value | E.value -----+--------+---------+---------+---------+--------- 1 | Test1 | Val1 | Val2 | Val3 | Val4 2 | Test2 | NULL | Val5 | NULL | Val6

右外连接 (RIGHT OUTER JOIN)

右外连接返回右表的所有记录，无论左表是否有匹配的记录。如果左表没有匹配的记录，结果中左表的列将显示为 NULL。

SELECT * FROM dept d RIGHT OUTER JOIN emp e ON e.dept_id = d.dept_id;

结果示例：

dept_id | dept_name | emp_id | emp_name | ... --------+-----------+--------+----------+----- 1 | HR | 101 | Alice | ... 2 | IT | 102 | Bob | ... NULL | NULL | 103 | Charlie | ...

全外连接 (UNION of LEFT and RIGHT JOIN)

SQL 标准中没有直接的全外连接语法，但可以通过左外连接和右外连接的 UNION 实现。

SELECT * FROM dept d LEFT OUTER JOIN emp e ON d.dept_id = e.dept_id UNION SELECT * FROM dept d RIGHT OUTER JOIN emp e ON e.dept_id = d.dept_id;

结果示例：

dept_id | dept_name | emp_id | emp_name | ... --------+-----------+--------+----------+----- 1 | HR | 101 | Alice | ... 2 | IT | 102 | Bob | ... 3 | Finance | NULL | NULL | ... NULL | NULL | 103 | Charlie | ...