Web数据库基础知识——Mysql(四)

惊雷无声 · 2025-3-23 11:19:57

四、表关系及多表查询

4.1 一对一表关系

定义：一对一（1:1）表关系是一种较为特殊的表关系，表示一个表中的一条记载与另一个表中的一条记载有唯一的对应关系。这种关系通常用于将一个表的某些列分离到一个新的表中，以便保持数据的清晰性和模块化。
比如，一个人对应一个身份证号。

-- 一对一表关系
CREATE TABLE person(
id int PRIMARY key auto_increment,
name VARCHAR(20)
);
-- 插入数据
INSERT INTO person VALUES (null,"张三"),(null,"李四");
SELECT * from person;
CREATE TABLE card(
id int PRIMARY key auto_increment,
number VARCHAR(20) UNIQUE not null,
pid int UNIQUE,
CONSTRAINT p_c FOREIGN key (pid) REFERENCES person(id)
);
-- 插入数据
INSERT INTO card VALUES (null,"466656",1),(null,"456866",2);
SELECT * from card;

复制代码

4.2 一对多表关系

（见Web数据库基础知识——Mysql(二)的表关系部分）
4.3 多对多表关系

定义：多对多（Many-to-Many）表关系是一种常见的数据关系模式，它形貌了两个实体集之间的一种复杂关系，即一个实体集中的多个元素可以与另一个实体集中的多个元素相关联。
比如，门生——课程。

-- 多对多表关系
-- 学生表
CREATE TABLE students(
id int PRIMARY key auto_increment,
name varchar(20)
)ENGINE=innodb;
-- 引擎默认会有声明不声明都可以
-- 插入数据
INSERT INTO students VALUES (null,"张三"),(null,"李四");
SELECT * FROM students;
-- 课程表
CREATE TABLE course(
id int PRIMARY key auto_increment,
name VARCHAR(20)
);
INSERT INTO course VALUES (null,"语文"),(null,"数学");
SELECT * from course;
-- 中间表
CREATE TABLE stu_c(
id int PRIMARY key auto_increment,
sid int,
cid int,
CONSTRAINT s_c1 FOREIGN KEY (sid) REFERENCES students(id),
CONSTRAINT s_c2 FOREIGN KEY (cid) REFERENCES course(id)
);
INSERT INTO stu_c VALUES (null,1,1),(null,1,2),(null,2,1),(null,2,2);
SELECT * from stu_c;

复制代码

插：开始多表查询之前，准备以下五张表，用作练习。

代码：

-- 创建用户表
CREATE TABLE users(
id int PRIMARY key auto_increment,
name VARCHAR(20),
age int
);
INSERT INTO users VALUES (NULL,'张三',23),
(NULL,'李四',24),(NULL,'王五',25),(NULL,'赵六',26);
-- 创建订单表
CREATE TABLE orderlists(
id int PRIMARY key auto_increment,
number VARCHAR(20) UNIQUE not null,
uid int,
CONSTRAINT uo FOREIGN key (uid) REFERENCES users(id)
);
INSERT INTO orderlists VALUES
(NULL,"hm001",1),(NULL,"hm002",1),
(NULL,"hm003",2),(NULL,"hm004",2),
(null,"hm005",3),(NULL,"hm006",3),
(null,"hm007",null);
-- 创建商品分类表
CREATE TABLE category(
id int PRIMARY key auto_increment,
name VARCHAR(20)
);
INSERT INTO category VALUES (null,"手机数码"),(null,"电脑办公"),
(null,"烟酒茶糖"),(null,"鞋靴箱包");
-- 创建商品表
CREATE TABLE products(
id int PRIMARY key auto_increment,
name VARCHAR(20),
cid int,
CONSTRAINT cp FOREIGN key (cid) REFERENCES category(id)
);
INSERT INTO products VALUES (null,"华为手机",1),
(null,"小米手机",1),(null,"联想电脑",2),
(null,"苹果电脑",2),(null,"中华香烟",3),
(null,"玉溪香烟",3),(null,"计生用品",null);
-- 中间表
CREATE TABLE us_pro(
upid INT PRIMARY KEY AUTO_INCREMENT, -- 中间表id
uid INT, -- 外键字段。需要和用户表的主键产生关联
pid INT, -- 外键字段。需要和商品表的主键产生关联
CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES users(id),
CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES products(id)
);
-- 添加数据
INSERT INTO us_pro VALUES (NULL,1,1);
INSERT INTO us_pro VALUES (NULL,1,2);
INSERT INTO us_pro VALUES (NULL,1,3);
INSERT INTO us_pro VALUES (NULL,1,4);
INSERT INTO us_pro VALUES (NULL,1,5);
INSERT INTO us_pro VALUES (NULL,1,6);
INSERT INTO us_pro VALUES (NULL,1,7);
INSERT INTO us_pro VALUES (NULL,2,1);
INSERT INTO us_pro VALUES (NULL,2,2);
INSERT INTO us_pro VALUES (NULL,2,3);
INSERT INTO us_pro VALUES (NULL,2,4);
INSERT INTO us_pro VALUES (NULL,2,5);
INSERT INTO us_pro VALUES (NULL,2,6);
INSERT INTO us_pro VALUES (NULL,2,7);
INSERT INTO us_pro VALUES (NULL,3,1);
INSERT INTO us_pro VALUES (NULL,3,2);
INSERT INTO us_pro VALUES (NULL,3,3);
INSERT INTO us_pro VALUES (NULL,3,4);
INSERT INTO us_pro VALUES (NULL,3,5);
INSERT INTO us_pro VALUES (NULL,3,6);
INSERT INTO us_pro VALUES (NULL,3,7);
INSERT INTO us_pro VALUES (NULL,4,1);
INSERT INTO us_pro VALUES (NULL,4,2);
INSERT INTO us_pro VALUES (NULL,4,3);
INSERT INTO us_pro VALUES (NULL,4,4);
INSERT INTO us_pro VALUES (NULL,4,5);
INSERT INTO us_pro VALUES (NULL,4,6);
INSERT INTO us_pro VALUES (NULL,4,7);
SELECT * FROM users;
SELECT * FROM orderlists;
SELECT * FROM category;
SELECT * FROM products;
SELECT * FROM us_pro;

复制代码

4.4 内连接

定义：内连接（INNER JOIN）是MySQL数据库中一种用于结合两个或多个表中记载的方法。当查询的结果只包含那些在所有到场的表中都有匹配值的记载时，就会使用内连接。这意味着，如果此中一个表中没有与另一个表匹配的记载，则该记载不会出现在结果集中。
语法：
SELECT 列名1, 列名2, ... FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名;
代码：

-- 多表查询
-- 内连接——查询多个表的交集，重叠的部分
SELECT * FROM users
INNER JOIN orderlists on users.id = orderlists.uid;
-- 隐式内连接
-- 查询名字，年龄，订单标号
SELECT
u.name,u.age,o.number
from users u,orderlists o
where o.uid=u.id;

复制代码

4.5 左外连接

定义：左外连接返回左表中的所有记载，以及右表中满足连接条件的记载。如果右表中没有匹配的记载，结果集中的右表字段将包含 NULL。
语法：
SELECT 列名1, 列名2, ... FROM 左表 LEFT JOIN 右表 ON 左表.列名 = 右表.列名;
代码：

-- 左外连接
-- 查询所有的用户信息，订单编号
SELECT u.*,o.number
from users u
left OUTER JOIN orderlists o
on o.uid=u.id;

复制代码

4.6 右外连接

定义：右外连接返回右表中的所有记载，以及左表中满足连接条件的记载。如果左表中没有匹配的记载，结果集中的左表字段将包含 NULL。
语法：
SELECT 列名1, 列名2, ... FROM 左表 RIGHT JOIN 右表 ON 左表.列名 = 右表.列名;
代码：

-- 右外连接
-- 查询orderlists所有信息，对应用户名
SELECT o.*,u.name
FROM users u
RIGHT JOIN orderlists o
on o.uid=u.id;

复制代码

4.7 Union查询

定义：用于合并两个或多个 SELECT 语句的结果集，同时去除重复的记载。每个 SELECT 语句必须拥有类似数量的列，列的数据类型也必须兼容。UNION 默认为 UNION ALL，但 UNION ALL 会包含所有的重复记载，而 UNION 会去除重复的记载
代码：

-- Union查询
-- 必须拥有相同数量的列，列的数据类型也必须兼容
-- 查询
SELECT * FROM users
UNION all
SELECT * from products;

复制代码

4.8 子查询

定义：子查询是一种在SQL语言中嵌套查询下层的步伐模块。当一个查询是另一个查询的条件时，这个被嵌套的查询就被称为子查询。子查询也被称为内查询或内部选择，而包含子查询的语句则被称为主查询或外部查询。
代码：

-- 查询年龄最高的用户姓名
SELECT max(age) from users;
SELECT name,age from users where age=26;
-- SELECT name,max(age) from users;——出错
-- 子查询
SELECT name,age from users where age=(SELECT max(age) from users);

复制代码

五、php+mysql

结果图：

5.1 数据库表

CREATE TABLE student_info(
stuid int PRIMARY key auto_increment,
stuname varchar(20),
stusex VARCHAR(10),
age int
);
insert into student_info VALUES
(null,"张三","男",23),
(null,"李四","女",11),
(null,"王五","男",23),
(null,"赵六","女",45);

复制代码

5.2 创建项目

我是用的是小皮，记着要关本机的mysql，否则会报错，我是由于没关本机的mysql报错了，不停在查，最后关了就连上了。

然后就进入到这个目录里面，创建一个文件夹也就是咱们的项目，在这里我创建的是php_mysql。

再然后用phpStorm打开该项目。

然后就开始写代码了。
index.php

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>学生信息系统</title>
</head>
<body>
<h1 align="center">学生信息</h1>
<form action="" method="post" name="indexf">
<p align="center"><input type="button" value="新增" name="inbut" onclick="location.href='insert.php'"></p>
<p align="center"><input type="text" name="sel"><input type="submit" value="搜索" name="selsub"></p>
<table align="center" border="1px" cellspacing="0px" width="800px">
<tr>
<th>学号</th>
<th>学生姓名</th>
<th>学生性别</th>
<th>学生年龄</th>
<th>操作</th>
</tr>
<?php
// phpinfo();
//开启缓存传数据到另一个界面
session_start();
//连接数据库之前必须要做的 localhost可以修改数据库账户密码连接哪个数据库数据库端口3306
$link=mysqli_connect('localhost','root','root','aaa','3306');
//如果连接不上数据库就提示失败
if (!$link){
exit('数据库连接失败！');
}
//判断用户是不是点击了搜索按钮
if (empty($_POST['selsub'])){
//如果没有点击查询所有结果
$res=mysqli_query($link,"select * from student_info order by stuid");
}else{
//点击了搜索按钮首先要拿到搜索框中的信息根据搜索框中的信息去查询用or连接或者满足任意一个都可以
$sel=$_POST['sel'];
$res=mysqli_query($link,"select * from student_info where stuid like '%$sel%' or stuname like '%$sel%' or
stusex like '%$sel%' or age like '%$sel%'");
}
//遍历结果集取出全部结果
while ($row=mysqli_fetch_array($res)){
echo '<tr align="center">';
//学号主键不会重复 [0]id [1]name [2]sex [3]age
echo "<td>$row[0]</td><td>$row[1]</td><td>$row[2]</td><td>$row[3]</td>
<td>
<input type='submit' name='upsub$row[0]' value='修改'>
<input type='submit' name='delsub$row[0]' value='删除'>
</td>";
echo '</tr>';
//点击修改按钮出现文本框
if (!empty($_POST["upsub$row[0]"])){
echo '<tr align="center">';
echo "<td>$row[0]</td>
<td><input type='text' name='upsn'></td>
<td><input type='text' name='upss'></td>
<td><input type='text' name='upsa'></td>
<td><input type='submit' value='确认修改' name='upsubs$row[0]'></td>";
echo '</tr>';
}
//判断是否点击了确认修改按钮
if (!empty($_POST["upsubs$row[0]"])){
//取值
$upsn=$_POST["upsn"];
$upss=$_POST["upss"];
$upsa=$_POST["upsa"];
//根据取值去写修改语句
mysqli_query($link,"update student_info set stuname='$upsn',stusex='$upss',age='$upsa' where stuid=$row[0]");
//修改完成之后刷新本页面跳转 #
header("location:#");
}
//判断是否点击了删除按钮
if (!empty($_POST["delsub$row[0]"])){
//写删除语句
/* mysqli_query($link,"delete from student_info where stuid=$row[0]");
//本页面跳转
header("location:#");*/
//把$row[0]放入缓存再把 $_SESSION['del']传到新页面
$_SESSION['del']=$row[0];//sid
//弹窗
echo '<script>
if (confirm("是否删除？")==true){
location.href="delete.php";
}
</script>';
}
}
?>
</table>
</form>
</body>
</html>

复制代码

insert.php

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>新增学生</title>
</head>
<body>
<h1 align="center">学生信息</h1>
<form action="" method="post" name="inf">
<p align="center">学生姓名：<input type="text" name="sn"></p>
<p align="center">学生性别：<input type="text" name="ss"></p>
<p align="center">学生年龄：<input type="text" name="sa"></p>
<p align="center"><input type="submit" name="insub" value="新增"></p>
</form>
<?php
$link=mysqli_connect('localhost','root','root','aaa','3306');
if (!$link){
exit('数据库连接失败！');
}
//判断是否点击了新增按钮
if (!empty($_POST["insub"])){
//取值
$sn=$_POST["sn"];
$ss=$_POST["ss"];
$sa=$_POST["sa"];
//点击了就新增根据文本框里面的值进行匹配数据库语句进行新增
mysqli_query($link,"insert into student_info(stuname,stusex,age) values ('$sn','$ss','$sa')");
//新增成功后跳转首页
header('location:index.php');
}
?>
</body>
</html>

复制代码

delete.php

<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>删除学生</title>
</head>
<body>
<?php
$link=mysqli_connect('localhost','root','root','aaa','3306');
if (!$link){
exit('数据库连接失败！');
}
//开启缓存
session_start();
$del=$_SESSION['del'];//sid
mysqli_query($link,"delete from student_info where stuid=$del");
header("location:index.php");
?>
</body>
</html>

复制代码

最后就可以实现结果图展示的功能了，包罗插入即新增、删除，搜索等功能。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

用户名		自动登录	找回密码
密码			立即注册

Web数据库基础知识——Mysql(四)

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云