本文讲解了学习网络安全之前必须了解的数据库内容。
篇幅略长，建议先收藏。
数据库基础概念
数据库是什么
-
是统一管理的、长期储存在计算机内的、有组织的相关数据的集合。
-
数据库的基本 特征包括：
- 数据按一定的数据模型组织、描述和储存；
- 数据间联系密切、冗余度较小；
- 数据独立性较高；
- 易扩展；
- 可为各种用户共享
数据库实际应用
- 数据储存、数据管理、数据分析
常用的数据库软件
| 特性 | MySQL | Oracle | SQL Server | Redis |
|---|---|---|---|---|
| 类型 | 关系型 | 关系型 | 关系型 | 内存键值型(NoSQL) |
| 特点 | 轻量快速 | 企业级功能全 | 微软系集成好 | 超高性能缓存 |
| 适用场景 | 中小Web应用 | 大型关键系统 | 企业Windows应用 | 缓存/实时数据 |
| 成本 | 免费/低 | 昂贵（甲骨文公司） | 中高 | 免费/低 |
本次学习的工具组合
工具
- phpStudy主要用于快速搭建和管理本地
- PHP开发环境及MySQL数据库 Navicat则是一款功能强大的数据库管理工具，用于高效地管理和操作MySQL数据库
PHPStudy
- phpStudy是一个集成了Apache、PHP和MySQL的开发环境，用于在 Windows操作系统上搭建和管理PHP开发环境。
- 它提供了一个简单的安装包，可以一键安装和配置Apache、PHP和 MySQL，使开发者能够在本地快速搭建PHP开发环境。
Navicat
- Navicat可以连接到MySQL服务器，进行数据库的创建、修改、删除等 操作。
- 它支持数据表的创建、修改、删除以及数据的增删改查等操作。 Navicat还提供了可视化的查询构建工具，使得查询操作更加直观和方便。
数据库基础语法
mysql四个默认的数据库（了解）
1. information_schema
-
作用：存储数据库元数据（metadata）
-
内容：包含所有数据库、表、列、权限等结构信息
-
特点：
- 虚拟数据库（不占用实际磁盘空间）
- 只读，不能修改
- 所有用户都可访问（权限允许时）
2. mysql
-
作用：存储MySQL系统核心数据
-
内容：
- 用户账户和权限信息
- 存储过程、事件定义
- 时区信息等
-
特点：
- 实际存储的数据库
- 只有管理员有完整权限
- 对MySQL正常运行至关重要
3. performance_schema
-
作用：收集数据库服务器性能数据
-
内容：
- 服务器事件
- 查询执行统计
- 资源使用情况
-
特点：
- 主要用于性能监控和优化
- 默认部分启用（可配置）
- 对性能有轻微影响
4. sys
-
作用：简化performance_schema的视图集合
-
内容：
- 预定义的性能监控视图
- 常用诊断查询
-
特点：
- MySQL 5.7+版本新增
- 方便管理员快速查看性能问题
- 基于information_schema和performance_schema构建
sql基础语法
代码使用位置
- 选中新建查询 -> 写sqp语句 -> 选中写的语句（建议） -> 点击运行
注释
-
-- 表示注释（注意--后面有空格）
MySQL数据类型
数值类型
-
类型 存储（字节） 有符号数值取值范围 无符号数值取值范围 TINYINT 1 -128 ~ 127 0 ~ 255 SMALLINT 2 -32768 ~ 32767 0 ~ 65535 MEDIUMINT 3 -8388608 ~ 8388607 0 ~ 16777215 INT 4 -2147483648 ~ 2147483647 0 ~ 4294967295 BIGINT（整数值超过 int时使用） 8 -263 ~ 263-1 0~ 264-1
浮点数类型
- FLOAT：占用4个字节，用于表示单精度浮点数值。
- DOUBLE：占用8个字节，用于表示双精度浮点数值。（小数点后的数字更多，内存空间也会更多）
- DECIMAL：用于表示高精度的小数，其精度和计数方法可以指定，以适应特定的需求。（小数点后 位更多，一般银行使用的较多）
日期和时间类型
-
类型 大小( bytes) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 -838:59:59/838:59:59 HH:MM:SS 时间值或持 续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00’ 到 '9999-12-31 23:59:59 YYYY-MM-DD
hh:mm:ss混合日期和 时间值 TIMESTAMP 4 1970-01-01 00:00:01’ UTC 到 '2038-01-19 03:14:07 YYYY-MM-DD
hh:mm:ss混合日期和 时间值，时 间 UTC解释
UTC表示协调世界时，是全球通用的标准时间基准，用于协调不同时区的时间。
要通过 UTC 时间计算北京时间，只需将 UTC 时间 加上 8 小时（因为北京位于东八区，且中国没有夏令时）
经度为零的国家直接使用UTC±0（即不进行任何时区偏移）作为官方时间
字符串类型
-
类型 大小 用途 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串 TINYBLOB 0-255 bytes 不超过255 个字符的二进制字符串 TINYTEXT 0-255 bytes 短文本字符串 BLOB 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 bytes 极大文本数据
数据库的增删查改
查询数据库
-
-- 查询所有数据库 show databases；-- 注意这里databases后面有ssql语句以分号结束，且不区分大小写
新建数据库
-
create database 数据库名; -- 创建数据库 这里的database后面没有s create database [if not exists] 数据库名; -- 判断，如果不存在则创建语法讲解中[ ]代表可选内容
删除数据库
-
drop database 数据库名;
使用数据库
-
作用是将后面的表和数据操作的范围固定在该数据库内。
-
use 数据库名; -- 如果查询的数据库不对时，用该语句确定使用哪个数据库作为当前数据库
数据库对字符集的操作
什么是字符集
-
字符
字符（Character）是各种文字和符号的总称，包括各国家文字、标点符号、图形符号、数 字等。
-
字符集
字符集（Character Set）在MySQL中是指一套用于存储、处理和检索文本数据的字符和编码规则。它定义了数据库中可以使用的字符集合，以及这些字符如何映射到数字编码上。（mysql默认使用utf-8编码）
-
使用
SHOW VARIABLES LIKE 'character_set%';查看数据库内可以设置的字符集
-
常用字符集比对表
字符集 编码方式 支持语言 存储效率 兼容性 ASCII 单字节(7位) 英文 最高 无扩展 ISO-8859-1 单字节 西欧语言 高 兼容ASCII GBK 双字节 简体中文 中 不兼容 Big5 双字节 繁体中文 中 不兼容 UTF-8(unicode) 变长(1-4字节) 全球语言 英文高效，中文一般 兼容ASCII
创建数据库，并指定字符集
-
create database 数据库名 character set （字符集）;字符集在SQL语言中表示方法：
utf-8 : utf8
GBK : gbk
修改数据库的字符集
-
alter database 数据库名 character set 字符集;
查看数据库的字符集
-
SHOW CREATE DATABASE 数据库名;
表的增删查改
数据库服务器、数据库和表的关系
创建表
-
语法：
CREATE TABLE 表名 ( -- 这里是小括号列1 数据类型 [约束], --这里是逗号列2 数据类型 [约束],... );示例：
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, -- MySQL自增主键name VARCHAR(50) NOT NULL, -- 非空约束email VARCHAR(100) UNIQUE, -- 唯一约束created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
查看表的信息
-
语法
-- 查看表的结构信息 desc 表名; -- desccription的缩写desc-- 查看所有表的信息 show tables; -
Field：字段名
Type：类型
Null：yes表示可以是空，no表示不能是null
Key：约束
Defalut：默认值
Extra：其他信息
修改表结构
-
语法
-- 修改表名 alter table users rename to user2-- 添加列 ALTER TABLE 表名 ADD 列名 数据类型;-- 删除列 ALTER TABLE 表名 DROP COLUMN 列名;-- 修改列类型 ALTER TABLE 表名 MODIFY 列名 新数据类型;-- 重命名列（不同数据库语法不同） ALTER TABLE 表名 RENAME COLUMN 旧列名 TO 新列名; -- PostgreSQL/SQL Server ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型; -- MySQL -
示例
alter table users rename to user2 alter table user2 add age int; alter table user2 drop COLUMN email; alter table user2 MODIFY age smallint; alter table user2 CHANGE name new_name varchar(200);desc user2； -
原先表的结构
更改后表的结构
删除表
-
语法
DROP TABLE 表名; -- 删除表（不可恢复） DROP TABLE [if EXISTS] 表名; --如果存在则删除 TRUNCATE TABLE 表名; -- 清空表数据（保留表结构，性能优于 DELETE）
数据的增删查改
插入数据（Insert）
-
语法
-- 插入单行 INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...);-- 插入多行 INSERT INTO 表名 (列1, 列2, ...) VALUES (值1, 值2, ...), -- 这里的值是一行的值(值1, 值2, ...); -
示例
-- 插入单行 -- 插入指定列 INSERT into users1 (name, age, email, created_at) VALUES ('zhangsan',25,'123@123','2020-12-1 12:12:12'); -- 默认插入全部列（不用写列名） INSERT into users1 VALUES ('2', 24, 'zhangsan','123@123','2020-12-1 12:12:12');-- 插入多行（以下代码写成一行也是可以的） insert into users1 (name, age, email, created_at) Value ('zc',null,'1@1','2020-12-1 12:12:12'),('ls',null,'2@2','2020-12-1 12:12:12'),('ll',null,'3@3','2020-12-1 12:12:12');select * from users1;-- 查看users1中的所有数据 -
结果
修改数据（更新数据）
-
语法
UPDATE 表名 SET 列1=新值1, 列2=新值2 WHERE 条件; -- 上面所有可以写成一行 -
示例
-- 示例（名为张三的年龄） update users1 set age=99 where name='zhangsan'; select * from users1; -
结果
删除数据（Delete）
-
语法
-- 删除符合条件的行 DELETE FROM 表名 WHERE 条件;-- 清空表（对比逐行删除） DELETE FROM 表名; -- 逐行删除（可回滚） TRUNCATE TABLE 表名; -- 快速清空（不可回滚） -
示例
delete from users1 where id=1; select * from users1; -
结果
表约束
表约束的含义
- 通过限制可以插入到表中的数据类型和格式，来保护数据的完整性和一致性。约束还可以防止无效的数据进入数据库，从而避免数据错误和应用程序故障。
常见表约束
主键约束primary key
-
用于确保表中的每一行都有可以唯一识别自己的一列或多列。
-
主键约束确保：
- 主键列的每个值都唯一
- 主键列不能NULL
-
主键的创建
CREATE TABLE users ( id INT PRIMARY KEY, -- MySQL主键name VARCHAR(50) ); -
主键的删除
alter table users DROP PRIMARY KEY; -
建表后主键的添加设置
alter table users modify id int PRIMARY KEY;
主键自增约束auto_increment
-
用于为表中的某一列（通常是主键列）自动生成唯 一的递增值。
-
主键自增约束的创建
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, -- MySQL自增主键name VARCHAR(50) );-- 插入数据时，可以将自增列的值设为null,数据库会自动编号自增 insert into users VALUES （null,'张三'）这里表结构extra多了auto_increment
-
删除主键自增约束
alter table user1 modify id int; -- 对主键还存在 -- 首先需要移除自增属性，然后再删除主键约束 alter table users DROP PRIMARY KEY; -
建表后主键自增约束的添加设置
alter table users modify id int AUTO_INCREMENT;
唯一约束unique
-
列中的唯一，但是允许为多个空值，一张表可以有多个唯一约束，用于保证不同列或组合的值唯一。(类似座位，可以不坐人，但是座位号唯一)
-
唯一约束的创建
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, -- MySQL自增主键name VARCHAR(50) ,email VARCHAR(100) UNIQUE, -- 唯一约束 ); -
唯一约束的删除
alter table users drop index email; -
建表后唯一约束的添加
alter table users modify email varchar(100) unique;
非空约束not null
-
非空约束，用于约束该字段的值不能为空。
-
非空约束的创建
CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT, -- MySQL自增主键name VARCHAR(50) NOT NULL, -- 非空约束email VARCHAR(100) UNIQUE, -- 唯一约束created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -
非空约束的删除
alter table users modify name varchar(50); -
建表后非空约束的添加
alter table users modify name varchar(50) not null; -
如果类型为字符串的列设置了非空约束，当向该列插入数据为null时，会自动转化为空字符，而非数据库null标志
mysql多对一的表关系
-
多对一（Many-to-One）
这意味着在一个表中，可以有多个记录与另一个表中的单个记录相关联。换句话说，一个表中的某一列的值可以重复，这些重复的值都指向另一个表中的唯一记录
-
这常用于表示两个表之间的连接，其中一个表中的多行可以与另一个表中的单行相关联。这种关系通常通过外键（Foreign Key） 来实现
-
E-R图：记录不同表之间存在的关系（例如用户列表与订单详情列表之间的关系）
菱形：关系
矩形：表明
椭圆形：字段名
id：一般为主键
-
下面的外键约束讲解都是用的一对多的例子
外键约束
什么是外键
- 外键约束（Foreign Key Constraint）是用来维护数据库表之间数据一致性和完整性的一种机制。它指定了一个表中的列（或列的组合）必须在另一个表的主键或唯一键列中有对应的值。通过外键约束，可以确保在一个表（称为子表或从表）中的值在另一个表（称为父表或主表）中已经存在，从而防止孤立数据的产生（主表中必须存在对应的从表外键值）
- 说人话就是：从表的外键和主表的主键建立多对一的关系
外键语法
创建外键
-
语法
-- 创建外键元素 CREATE TABLE 子表名 (列名1 数据类型,列名2 数据类型,...CONSTRAINT 外键名 FOREIGN KEY (本表外键列名) REFERENCES 父表名 (主表的主键列名) )ENGINE=INNODB;-- 声明默认存储引擎,以防外键联系建立不起来 -- constraint 限制 -
举例：创建顾客与订单列表创建外键约束
-- 创建顾客表 CREATE TABLE custom (id int PRIMARY KEY auto_increment,name VARCHAR(20) not NULL )ENGINE=INNODB;-- 声明默认存储引擎,以防外键联系建立不起来 -- 插入数据 insert into custom VALUES (null,'zhangsan'),(null,'lisi'); select * from custom;-- 创建订单列表（在此创建外键） create table orderlist (id int PRIMARY KEY auto_increment,number VARCHAR(30) not null,uid int, -- 外键元素CONSTRAINT c_o FOREIGN KEY (uid) REFERENCES custom (id) -- 外键约束 )ENGINE=INNODB;-- c_o指custom和order外键名 -- 插入数据（从表的外键值必须要在主表的主键值中存在） insert into orderlist VALUES (null,'hm01',1),(null,'hm02',1),(null,'hm03',2),(null,'hm04',2); select * from orderlist; desc orderlist;创建结果
custom表
orderlist表
输入
insert into orderlist VALUES (null,'hm05',3);，由于主表中的主键只有1和2，想插入uid为3 的数据会报错：> 1452 - Cannot add or update a child row: a foreign key constraint fails (test.orderlist, CONSTRAINT c\_o FOREIGN KEY (uid) REFERENCES custom (id) ON DELETE CASCADE ON UPDATE CASCADE)。表示主表中不存在主键值为3的数据，因此从表不能添加外键值为3的数据。删除主键用户
delete from custom where name='李四';，会报错：1451 - Cannot delete or update a parent row: a foreign key constraint fails (test.orderlist, CONSTRAINT c\_o FOREIGN KEY (uid) REFERENCES custom (id))。因为会让从表的外键没有主表的主键去对应。
删除外键约束
-
语法
-- 删除外键元素 alter table 从表名 drop foreign key 从表的外键名; -
删除外键约束举例
alter table orderlist drop foreign key c_o;
建表完成后添加外键
-
语法
alter table 从表名 add constraint 外键名另取 foreign key (本表外键名) references 主表名(主键名); -
举例
alter table orderlist add constraint c_o foreign key (uid) references custom(id);
外键级联操作
-
是指当对主键表（也称为父表或引用表）中的数据进行更新或删除时，自动对包含外键的表（也称为子表或从属表）中的相关数据进行相应的更新或删除。（两张表的数据联动更新或者删除）
-
语法
alter table 从表名 add constraint 外键名另取 foreign key (本表外键名) references 主表名(主键名) on update cascade on delete cascade; -
更新举例
-- 需要先删除原有的外键约束 alter table orderlist drop foreign key c_o; -- 创建外键级联 alter table orderlist add constraint c_o foreign key (uid) references custom(id) on update cascade on delete cascade; -- 更新主表 update custom set id=3 where id=2;未更新前的custom和orderlist
更新后的custom和orderlist
-
删除举例
delete from custom where id=3;原来是不能删除的，级联后，删除主表主键id=3的数据后，从表也会对应删除外键值为3的所有数据
删除后的custom和orderlist
数据库常用查询语句
基本查询
基本查询语法
-
-- 查询表数据 SELECT 列名1,列名2... FROM 表名;
基本查询拓展
-
创建查询用例数据库
-- 创建db1数据库 CREATE DATABASE db1;-- 使用db1数据库 USE db1;-- 创建数据表 CREATE TABLE product(id INT, -- 商品编号 NAME VARCHAR(20), -- 商品名称 price DOUBLE, -- 商品价格 brand VARCHAR(10), -- 商品品牌 stock INT, -- 商品库存 insert_time DATE );-- 添加时间-- 添加数据 INSERT INTO product VALUES (1,'华为手机',3999,'华为',23,'2088-03-10'),(2,'小米手机',2999,'小米',30,'2088-05-15'),(3,'苹果手机',5999,'苹果',18,'2088-08-20'),(4,'华为电脑',6999,'华为',14,'2088-06-16'),(5,'小米电脑',4999,'小米',26,'2088-07-08'),(6,'苹果电脑',8999,'苹果',15,'2088-10-25'),(7,'联想电脑',7999,'联想',NULL,'2088-11-11'); -
基础查询拓展举例
select * from product; -- 查看全部表 select name,price,brand from product; -- 查看这三列的全部表 select distinct brand from product; -- 查看品牌这一列表，并且去除重复项-- 下列+10操作不是真的在数据库加10，而是将+10以后的结果呈现在表上，而数据库本身的数据还是原来的值 select name,stock+10 from prodect; -- 查询名称和库存，并将库存加10,但是库存为null时，未执行+10的操作 select name,ifnull(stock,0)+10 from product; -- ifnull(stock,0)+10 代表：先进行ifnull值判断，如果stock为null,则将stock赋值为0，然后+10；如果不为null，直接+10 -- 用了ifnull(stock,0)后，展示的列名也会变为ifnull(stock,0)，此时可以起一个别名 select name,ifnull(stock,0)+10 as getsum from product; -- as getsum就是将该列名设置别名为getsum
条件查询
条件查询语法
-
-- 条件查询 SELECT 列名1,列名2... FROM 表名 WHERE 条件;-- 三段式： SELECT -- 查什么列名1,列名2... FROM -- 查谁表名 WHERE -- 条件是条件;
条件查询举例
-
-- 查询库存大于20的数据 select * from product where stock>20; select * from product where stock=20; -- 注意这里是单等号表示判断-- 区间查询 查询金额在4000到6000之间的数据 select * from product where price>=4000 and price<=6000; select * from product where price between 4000 and 6000; -- between 4000 and 6000这限定了金额范围在4000至6000 select * from product where stock=14 or stock=30 or stock=23; -- 库存等于这三个数的筛出来 select * from product where stock in(14,30,23); -- in操作符：允许在括号里指定值，会在括号里选择值并返回数据，与上面or的效果一样-- 查询是否为null 查询库存是否为null select * from product where stock is null; -- 要用is，不能用等于号 select * from product where stock is not null;
模糊查询
-
模糊查询语法
-- 模糊查询 SELECT 列名1,列名2... FROM 表名 WHERE 列名 LIKE ' ';（%：匹配零个或多个字符； _：匹配单个字符。） -- 举例说明 要查小米手机 %小米% 匹配小米两个字符前后可以有任意个字符的数据，即所有包含“小米”的数据 小米__ 匹配小米前面无字符，后面有两个字符的数据 _小米% 匹配前面多一个字符，后面任意字符的数据 ____ 匹配定长字符（4个字符） -
模糊查询举例
select * from product where name like '小米%'; --查询小米开头的商品信息 select * from product where name like '_为%'; -- 匹配第二个字为'为'的商品信息 select * from product where name like '____'; -- 查询定长字符，即名字为四个字符的商品信息 select * from product where name like '%电脑%'; -- 查询名字中包含电脑的
聚合查询
-
聚合查询方法
COUNT(列名)：计算行数。 MAX(列名)：返回数值列中的最大值。 MIN(列名)：返回数值列中的最小值。 SUM(列名)：计算数值列的总和。 AVG(列名)：计算数值列的平均值。 -
聚合查询举例
select count(*) from product;-- *号，通配符，匹配所有数据 select max(price) from product; -- 查询价格的最大值 select min(stock) from product; -- 查询库存的最小值 select sum(stock) from product; --查询库存总量 select avg(price) from product where brand='小米'; -- 查询小米品牌产品的价格平均值
排序查询
-
排序查询语法
-- 三段式 SELECT 列名 -- select确定显示的内容 FROM 表名 ORDER BY 列名 DESC\ASC; -- asc为升序，desc为降序； -- ascend 上升，dscend 下降 -- ascending order 升序，dscending order 降序 -
排序查询方法
select * from product order by stock asc;-- 按照库存升序-- 排序可添加条件 注意 where 放在 order by 的前面 select * from product where name like '%手机%' order by price desc;-- 查name包含手机，且按照金额降序排序-- 如果排序值相同时，添加其他排序条件 -- 按照price升序排列，相同时按照库存降序排列 select * from producut order by price asc, stock desc;-- 用,分割两个排序条件即可
分组查询
-
分组查询语法
SELECT 列名 FROM 表名 GROUP BY 列名; -- group by 意思就是看依照哪一个进行分组 -
分组查询举例
select brand,sum(price) from product group by brand;-- 按照品牌分组，获取不同品牌的总金额举例结果：先俺品牌分组，再对金额进行求和
-- 加条件进行品牌分组并对金额求和 注意where在group by前面 select brand,sum(price) from product where price>4000 group by brand;-- 上述情况下继续加条件，只显示总金额大于7000 select brand,sum(price) as getsum from product where price>4000 group by brand having getsum>7000; -- having语句，用于过滤分组之后的将结果，放在group by之后-- 无意义的叠加语句，只是举个例子了解一下 -- 上述情况下继续排序，对总金额大于7000的进行排序 select brand,sum(price) as getsum from product where price>4000 group by brand having getsum>7000 order by getsum desc;
分页查询
-
数量多时，进行分页查询，一段一段的看
SELECT * FROM 表名 LIMIT 偏移量, 每页数量; -- 偏移量一般设置为(页码-1)*每页数量 -
分页查询举例
-- 查第一页，每页数据量为3,则偏移量为(1-1)*3 select * from product LIMIT 0,3;-- 查第二页，每页数据量为3，则偏移量为(2-1)*3 select * from product LIMIT 3,3;-- 如果偏移量设为1，那么显示的数据会从2开始 select * from product LIMIT 1,3;偏移量为1，每页数量为3时，分组查询的结果
表关系及多表查询
一对一表关系
一对一关系概念
-
一对一（1:1）表关系是一种较为特殊的表关系，表示一个表中的一条记录与另一个表中的一条记录有唯 一的对应关系。这种关系通常用于将一个表的某些列分离到一个新的表中，以便保持数据的清晰性和模块化。
-
应用举例：提高查询的效率，将常用的列差分出来成新的表
-
-
一对一关系语法
-
CONSTRAINT 外键名 FOREIGN KEY (本表外键列) REFERENCES 主表(主键)-- 例如 CREATE TABLE card(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idnumber VARCHAR(20) UNIQUE NOT NULL, -- 身份证号pid INT UNIQUE, -- 外键列（为了保证一对一的关系，将该列设置唯一约束即可）CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) );
一对一关系举例
-
用户和卡片的一对一关系
-- 创建db3数据库 CREATE DATABASE db3; -- 使用db3数据库 USE db3; -- 创建person表 CREATE TABLE person(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idNAME VARCHAR(20) -- 姓名 ); -- 添加数据 INSERT INTO person VALUES (NULL,'张三'),(NULL,'李四'); -- 创建card表 CREATE TABLE card(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idnumber VARCHAR(20) UNIQUE NOT NULL, -- 身份证号pid INT UNIQUE, -- 外键列（为了保证一对一的关系，将该列设置唯一约束）CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) ); -- 添加数据 INSERT INTO card VALUES (NULL,'12345',1),(NULL,'56789',2);
一对多表关系
- 见表约束章节
多对多表关系
多对多关系的概念
-
多对多（Many-to-Many）表关系是一种常见的数据关系模式，它描述了两个实体集之间的 一种复杂关系，即一个实体集中的多个元素可以与另一个实体集中的多个元素相关联。
-
应用举例：学生可课程之间的选择关系，sid为学生数据表的外键，cid为课程数据表的外键
-
-
-
由此可见，多对多关系需要用另外一张表来维护多对多关系
多对多关系语法
-
CONSTRAINT 表1外键名 FOREIGN KEY (表1外键名) REFERENCES 表1(主键), -- 添加外键约束 CONSTRAINT 表2外键名 FOREIGN KEY (表2外键名) REFERENCES 表2(主键) -- 添加外键约束
多对多关系举例
-
-- 创建student表 CREATE TABLE student(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idNAME VARCHAR(20) -- 学生姓名 );-- 添加数据 INSERT INTO student VALUES (NULL,'张三'),(NULL,'李四');-- 创建course表-- 创建course表 CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idNAME VARCHAR(10)-- 课程名称 );-- 添加数据 INSERT INTO course VALUES (NULL,'语文'),(NULL,'数学');-- 创建中间表 CREATE TABLE stu_course(id INT PRIMARY KEY AUTO_INCREMENT, -- 主键idsid INT, -- 用于和student表中的id进行外键关联cid INT, -- 用于和course表中的id进行外键关联CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id), -- 添加外键约束CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id) -- 添加外键约束 ); -- 添加数据 INSERT INTO stu_course VALUES (NULL,1,1),(NULL,1,2),(NULL,2,1),(NULL,2,2);
表关系语法总结
- 表关系都要创建外键，且创建外键的语法一样
- 一对一要求在从表上创建的外键和主表的主键相关联，且外键需要唯一约束
- 一对一要求在从表上创建的外键和主表的主键相关联，不要求外键唯一
- 多对多两张表没有主表从表之分，建立两表的多对多关系需要另外一张关系表，在这张关系表上创建两张表各自的外键，最后给关系表写数据即能体现两表的关系
多表查询
数据准备
-
创建数据库
CREATE DATABASE db4;-- 创建db4数据库 USE db4;-- 使用db4数据库 -
创建用户表
-- 创建user表 CREATE TABLE USER(id INT PRIMARY KEY AUTO_INCREMENT, -- 用户idNAME VARCHAR(20), -- 用户姓名age INT -- 用户年龄 );-- 添加数据 INSERT INTO USER VALUES (1,'张三',23); INSERT INTO USER VALUES (2,'李四',24); INSERT INTO USER VALUES (3,'王五',25); INSERT INTO USER VALUES (4,'赵六',26);
-
创建订单表
-- 订单表 CREATE TABLE orderlist(id INT PRIMARY KEY AUTO_INCREMENT, -- 订单idnumber VARCHAR(30), -- 订单编号uid INT, -- 外键字段-- 订单表与用户表创建多对一关系：uid是多，USER(id)是一CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id) );-- 添加数据 INSERT INTO orderlist VALUES (1,'hm001',1); INSERT INTO orderlist VALUES (2,'hm002',1); INSERT INTO orderlist VALUES (3,'hm003',2); INSERT INTO orderlist VALUES (4,'hm004',2); INSERT INTO orderlist VALUES (5,'hm005',3); INSERT INTO orderlist VALUES (6,'hm006',3); INSERT INTO orderlist VALUES (7,'hm007',NULL);
-
创建商品分类表
-- 商品分类表 CREATE TABLE category(id INT PRIMARY KEY AUTO_INCREMENT, -- 商品分类idNAME VARCHAR(10) -- 商品类名 );-- 添加数据 INSERT INTO category VALUES (1,'手机数码'); INSERT INTO category VALUES (2,'电脑办公'); INSERT INTO category VALUES (3,'烟酒茶糖'); INSERT INTO category VALUES (4,'鞋靴箱包');
-
创建商品表
-- 商品表 CREATE TABLE product(id INT PRIMARY KEY AUTO_INCREMENT, -- 商品idNAME VARCHAR(30), cid INT, -- 外键字段-- 商品表与商品目录表创建多对一的关系：cid是多，category(id)是一CONSTRAINT cp_fk1 FOREIGN KEY (cid) REFERENCES category(id) );-- 添加数据 INSERT INTO product VALUES (1,'华为手机',1); INSERT INTO product VALUES (2,'小米手机',1); INSERT INTO product VALUES (3,'联想电脑',2); INSERT INTO product VALUES (4,'苹果电脑',2); INSERT INTO product VALUES (5,'中华香烟',3); INSERT INTO product VALUES (6,'玉溪香烟',3); INSERT INTO product VALUES (7,'计生用品',NULL);
-
创建商品表与用户表之间的多对多关系表
-- 中间表 CREATE TABLE us_pro(upid INT PRIMARY KEY AUTO_INCREMENT, -- 中间表iduid INT, -- 外键字段。需要和用户表的主键产生关联pid INT, -- 外键字段。需要和商品表的主键产生关联CONSTRAINT up_fk1 FOREIGN KEY (uid) REFERENCES USER(id),CONSTRAINT up_fk2 FOREIGN KEY (pid) REFERENCES product(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);
内连接
内连接的含义
-
内连接（INNER JOIN）是MySQL数据库中一种用于结合两个或多个表中记录的方法。如果其中一个表中没有与另一个表匹配的记录，则该记录不会出现在结果集中。
-
说人话就是查两个表有交集有关联的部分
内连接的语法
-
SELECT 列名1, 列名2, ... FROM 表1 INNER JOIN 表2 -- ON 表1.列名 = 表2.列名; -- ON后面接条件，一般为外键
内连接举例
-
查询user表和orderlist表，其为多对一的关系
内连接查询结果理论上应该如下图
-
内连接查询语法
-- 正常语法（结果见后面查询结果） select * from user inner join orderlist on user.id=orderlist.uid; -- on后面接的是连接两个数据库的条件-- 使用别名 select * from user u inner join orderlist o on u.id=o.uid; -- on后面接的是连接两个数据库的条件 -
内连接查询结果
隐式内连接举例
-
-- 查询某几列 select u.name,u.age,o.number -- 根据列名需求反馈结果 from user u inner join orderlist o on u.id=o.uid; -- 条件查询 隐式内连接 select u.name,u.age,o.number from user u,orderlist o where u.id=o.uid; -
查询结果-隐式内连接和内连接的查询结果是一样的，因为where条件查询使用外键做了查询条件
左外连接
左外连接的含义
-
左外连接返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有 匹配的记录，结果集中的右表字段将包含NULL。
-
如图
左外连接的语法
-
语法： SELECT 列名1, 列名2, ... FROM 左表 LEFT JOIN 右表 ON 左表.列名= 右表.列名;
左外连接举例
-
目标：查询user表所有数据对应的订单信息
-
还是针对user表和orderlist表，其为多对一的关系
-
左外连接查询语法
select u.*,o.number -- 注意这里列名的写法，学习记忆 from user u left [outer] join orderlist o -- outer为可选项，运行时删除[] on u.id=o.uid; -- onh 后面就是主键和外键 -
左外连接查询结果
这里通过赵六可以发现 ，如果右表中没有 匹配的记录，结果集中的右表字段将包含NULL。
右外连接
右外连接的含义
-
右外连接返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，结果集中的左表字段将包含NULL。
-
如图
右外连接的语法
-
语法： SELECT 列名1, 列名2, ... FROM 左表 RIGHT JOIN 右表 ON 左表.列名= 右表.列名;
右外连接举例
-
目标：查询所有订单信息及对应的用户信息（与左外连接反过来）
-
-
右外连接查询语法
select o.*,u.name -- 最后查询的结果会按照这个列名顺序排列 from user u right [outer] join orderlist o on u.id=o.uid; -- 依然是依照主键和外键进行关联 -
右外连接查询结果
union查询(联合查询)
union查询的含义
- 用于合并两个或多个SELECT 语句的结果集，同时去除重复的记录。
- 使用条件：每个SELECT 语句必须拥有 相同数量的列，列的数据类型也必须兼容。
- 注意事项：UNION 默认为UNION ALL，但UNION ALL 的结果会包含所有的 重复记录，而UNION 的结果会去除重复的记录
union查询的举例
-
选表：因为使用union的查询必须要求每个SELECT 语句必须拥有相同数量的列，列的数据类型也必须兼容。所以选择orderlist表和category表
-
union查询语法
select numer from orderlist -- 只要求select后的列数量相同且类型兼容 union all select name from category; -
union查询结果
子查询
子查询的含义
- 子查询是一种在SQL语言中嵌套查询下层的程序模块。当一个查询是另一个查询的条件时，这个被嵌套的查询就被称为子查询。子查询也被称为内查询或内部选择，而包含子查询的语句则被称 为主查询或外部查询
- 简单理解就是：将一个查询的结果作为另一个查询的一部分。就是嵌套
子查询举例
子查询举例1
-
查询user中年纪最大的用户名是谁
-- 如果这样查 select max(age) from user; -- 结果只会有一个26 ，不知道名字是谁-- 如果这样写 select name,max(age) from user; -- 结果是张三，26.但是张三实际年龄为23，这说明查询结果中名字和年龄并没有关联-- 应该这样写，结果才是对的 select name,age from user where age=(select max(age) from user); -
查询结果
子查询举例2
- 查询张三和李四的订单详情。（需要订单列表的外键找到哪些订单是张三和李四的,所以需要先知道张三李四的id）
-
-- 先查询张三李四的id select id from user where name in ('张三','李四'); 或者 select id from user where name='张三' or name='李四'; -- 查询结果张三为1和李四为2-- 再根据uid与id的关系查询订单信息 select * from orderlist where uid in (1,2);-- 使用子查询 select * from orderlist where uid in (select id from user where name in ('张三','李四')); 
select语句可以放在where后面作为条件的范围
也可以放在from后面作为数据内容选择的范围
php+mysql实验
实验目标
-
创建如下图所示的网站
实验前置知识
mysql拓展
-
在PHP中，与SQL数据库进行交互通常涉及使用数据库扩展库，如PDO（PHP Data Objects）或MySQLi。这些库提供了与数据库建立连接、执行查询和处理结果的方法。
-
phpinfo();该函数可以看到php的详细信息，在其中可以看到我们需要的扩展 mysqli。如果没有找到mysqli拓展，打开phpstudy -> 其他选项菜单 -> php拓展及设置 -> php拓展，在其中勾选mysqli。
环境准备
-
打开phpstudy根目录：打开phpstudy -> 其他选项菜单-> 网站根目录 -> 新近文件夹php_mysql（该文件夹当作项目文件夹）
-
打开navicat创建数据库：打开navicat -> 新建数据库(图形化创建) -> 数据库名：myschool -> 其他不用选使用默认 -> 确定 -> 新建表名students -> 表格式如下图(保存) -> 注意：主键stuid需要勾选自增，在下方勾选 （保存）
这里的int长度为11表示的是显示宽度，而非存储宽度，存储宽度依然是4
-
添加数据：打开表 -> 直接输入下图内容(tab键创建新的行) -> 保存(ctrl+S)
-
新建php项目：打开phpstorm -> 新建php空项目 -> 指定路径为刚才在phpstudy根目录中创建的文件夹php_mysql。
代码书写
-
php_mysql项目
-
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><!--上一行代码onclick="location.href='insert.php'" 的含义是定义事件onclick（点击后）跳转至本地的insert.php文件去--><p align="center"><input type="text" name="sel"><input type="submit" value="搜索" name="selsub"></p> //submit按钮再点击后，会重新提交当前php文件<table align="center" border="1px" cellspacing="0px" width="800px"><!--table是创建表格--><tr> <!--一个<tr></tr>代表表格的一行--><th>学号</th> <!--一个<th></th>代表表头--><th>学生姓名</th> <!--表头下面可跟每一列的内容，用<td></td>框起来--><th>学生性别</th><th>学生年龄</th><th>操作</th></tr><?php//启用session_start()后，会生成$_SESSION超全局变量//可通过$_SESSION数组读写会话数据，如$_SESSION['del']=$row[0];//这么做的目的是，要通过全局缓存将删除的数据传送给delete.php文件中去//开启缓存 传数据到另一个界面session_start();//首先要用php连接数据库,mysqli_connect参数为数据库连接地址（本机），用户名，密码，数据库名，数据库端口号（见pphpstudy->其他菜单选项->mysql工具->设置）$link=mysqli_connect('localhost','root','root','myschool','3306');if(!$link){exit('数据库连接失败');}//判断用户是否点击了搜索按钮，empty():若未点击是空，则返回trueif(empty($_POST['selsub'])){//如果没有点击搜索，则查询显示所有结果//mysqli_query()用于执行sql函数，参数如下$res=mysqli_query($link,"select * from students order by stuid");}else{$sel=$_POST['sel']; //通过$_POST拿到文本框（name="sel"）的数据$res=mysqli_query($link,"select * from students where stuid like '%$sel%' or stuname like '%$sel%' orstusex like '%$sel%' or age like '%sel%'"); //双引号里面用单引号}//遍历结果集，取出全部结果//mysqli_fetch_array($res)用来接收刚才取到的数据库里的数据,返回数组while($row=mysqli_fetch_array($res)){ //数组形式echo '<tr align="center">';//学号 主键 不会重复 [0]id [1]name [2]sex [3]ageecho "<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>';}//判断是否点击了确认修改按钮//小问题，为什么while已经运行完了，点击按钮事件后，仍然会重新判断if?//我理解的是php是描述性语言，不是过程性语言，点击按钮后，这里就会自动更改判断结果了if (!empty($_POST["upsubs$row[0]"])){//取值$upsn=$_POST["upsn"];$upss=$_POST["upss"];$upsa=$_POST["upsa"];//根据取值去写修改语句mysqli_query($link,"update students set stuname='$upsn',stusex='$upss',age='$upsa' where stuid=$row[0]");//修改完成之后 刷新 本页面跳转 #header("location:#");}//判断是否点击了删除按钮if (!empty($_POST["delsub$row[0]"])){//写删除语句/* mysqli_query($link,"delete from students where stuid=$row[0]");//本页面跳转header("location:#");*///把$row[0]放入缓存 再把 $_SESSION['del']传到新页面//上面的语句也可以直接删除，但是不能避免手滑，数据不安全，为了安全应该添加确认$_SESSION['del']=$row[0];//sid//弹窗 这里使用javascript应该是为了使用这个确认弹窗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"><!--method="post"代表连接php的方式--><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> //submit按钮再点击后，会重新提交当前php文件 </form> <?php //首先要用php连接数据库,mysqli_connect参数为数据库连接地址（本机），用户名，密码，数据库名，数据库端口号（见pphpstudy->其他菜单选项->mysql工具->设置） //新增的数据要放入数据库 $link=mysqli_connect('localhost','root','root','myschool','3306'); if(!$link){exit('数据库连接失败'); } //判断用户是否点击了搜索按钮，empty():若未点击是空，则返回true if(!empty($_POST['insub'])){//通过$_POST去取值$sn=$_POST["sn"];$ss=$_POST["ss"];$sa=$_POST["sa"];//执行sql语句,向数据库加数据,id不用管，会自增mysqli_query($link,"insert into students(stuname,stusex,age) values ('$sn','$ss','$sa')");//新增完需要跳转回去,跳转到本地的index.php//header()函数用于向客户端发送原始的http头部信息，控制页面跳转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','myschool','3306'); if (!$link){exit('数据库连接失败！'); } //开启缓存 session_start(); $del=$_SESSION['del'];//sid mysqli_query($link,"delete from students where stuid=$del"); header("location:index.php"); ?> </body> </html>
