MySql

一、数据分析

1、文本传值

  1. <a href="/details.php?title=URL跳转">URL跳转</a>

2、文章表：主键

  • RDBMS术语：主键是唯一的，一个数据表中只能包含一个主键，你可以使用主键来查询数据。
    1. ALTER TABLE `article`
    2. ADD COLUMN `id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '文章主键' FIRST ,
    3. ADD PRIMARY KEY (`id`);

    3、主键传值

    <a href="/details.php?id=1">URL跳转</a>

    4、分类表：主键

    ALTER TABLE `class`
ADD COLUMN `id`  int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键' FIRST ,
ADD PRIMARY KEY (`id`);

    5、分类表：排序字段

    ALTER TABLE `class`
ADD COLUMN `sort`  int(10) UNSIGNED NULL DEFAULT 0 COMMENT '排序' AFTER `name`;

    6、文章表：分类主键

    ALTER TABLE `article`
ADD COLUMN `class`  int(10) UNSIGNED NULL DEFAULT 0 COMMENT '分类ID' AFTER `date`;

二、比较运算符

符号 描述
= 等号
<> 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于 
# `=` 等号
SELECT * FROM `article` WHERE `id` = 6;
# `<>` 不等于
SELECT * FROM `article` WHERE `id` <> 6;
# `>` 大于
SELECT * FROM `article` WHERE `id` > 6;
# `<` 小于
SELECT * FROM `article` WHERE `id` < 6;
# `>=` 大于等于
SELECT * FROM `article` WHERE `id` >= 6;
# `<=` 小于等于
SELECT * FROM `article` WHERE `id` <= 6;

三、逻辑运算符

符号 描述
NOT 逻辑非，取反
AND 逻辑与
OR 逻辑或
XOR 逻辑异或 
# `NOT` 逻辑非
SELECT * FROM `article` WHERE NOT(`id` = 1);
# `AND` 逻辑与
SELECT * FROM `article` WHERE `class` = 2 AND `date` = '2021-02-01';
# `OR` 逻辑或
SELECT * FROM `article` WHERE `class` = 2 OR `date` = '2021-02-01';
# `XOR` 逻辑异或
SELECT * FROM `article` WHERE `id` = 1 XOR `id` = 2;
SELECT * FROM `article` WHERE `id` = 1 XOR `id` = 1;

四、比较运算符

符号 描述
BETWEEN 在两值之间
NOT BETWEEN 不在两值之间
IN 在集合中
NOT IN 不在集合中
LIKE 模糊匹配 
# `BETWEEN` 在两值之间
SELECT * FROM `article` WHERE `id` BETWEEN 1 AND 10;
# `NOT BETWEEN` 不在两值之间
SELECT * FROM `article` WHERE `id` NOT BETWEEN 1 AND 5;
# `IN` 在集合中
SELECT * FROM `article` WHERE `id` IN (1,5);
# `NOT IN`不在集合中
SELECT * FROM `article` WHERE `id` NOT IN (1,5);
# `LIKE` 模糊匹配
SELECT * FROM `article` WHERE `title` LIKE '%PHP%';
SELECT * FROM `article` WHERE `title` LIKE '%PHP_';