Mysql数据库(2)

最新推荐文章于 2022-10-01 00:00:09 发布

@ljn

最新推荐文章于 2022-10-01 00:00:09 发布

阅读量1k

点赞数

文章标签：数据库开发 java

本文链接：https://blog.csdn.net/m0_62567916/article/details/124229484

版权

1 简单约束

约束：对表中的数据进行限定，保证数据的正确性，有效性，完整性。

(1) 主键约束

特点：主键约束包含：非空和唯一两个功能。一张表只能有一个列作为主键，主键一般用于表中数据的唯一标识。

（2）唯一约束

(3) 非空约束

2 复杂约束

（1 ）外键约束

让表与表之间产生关系，从而保证数据的准确性。

（2 ）外键的级联更新和级联删除

什么是级联更新和级联删除
- 当我想把user用户表中的某个用户删掉，我希望该用户所有的订单也随之被删除

当我想把user用户表中的某个用户id修改，我希望订单表中该用户所属的订单用户编号也随之修改

添加级联更新和级联删除

3 多表设计

（1）一对一

分析
- 人和身份证。一个人只有一个身份证，一个身份证只能对应一个人！

实现原则
- 在任意一个表建立外键，去关联另外一个表的主键

（2）一对多

分析
- 用户和订单。一个用户可以有多个订单！

商品分类和商品。一个分类下可以有多个商品！

实现原则
- 在多的一方，建立外键约束，来关联一的一方主键

（3）多对多

分析
- 学生和课程。一个学生可以选择多个课程，一个课程也可以被多个学生选择！

实现原则
- 需要借助第三张表中间表，中间表至少包含两个列，这两个列作为中间表的外键，分别关联两张表的主键

4 多表查询

-- 创建db6数据库
CREATE DATABASE db6;
-- 使用db6数据库
USE db6;

-- 创建user表
CREATE TABLE USER(
        id INT PRIMARY KEY AUTO_INCREMENT,        -- 用户id
        NAME 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,        -- 订单id
        number VARCHAR(30),                                        -- 订单编号
        uid INT,    -- 外键字段
        CONSTRAINT ou_fk1 FOREIGN KEY (uid) REFERENCES USER(id)
);
-- 添加数据
INSERT INTO orderlist VALUES (1,'bk001',1);
INSERT INTO orderlist VALUES (2,'bk002',1);
INSERT INTO orderlist VALUES (3,'bk003',2);
INSERT INTO orderlist VALUES (4,'bk004',2);
INSERT INTO orderlist VALUES (5,'bk005',3);
INSERT INTO orderlist VALUES (6,'bk006',3);
INSERT INTO orderlist VALUES (7,'bk007',NULL);


-- 商品分类表
CREATE TABLE category(
        id INT PRIMARY KEY AUTO_INCREMENT,  -- 商品分类id
        NAME 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,   -- 商品id
        NAME VARCHAR(30),                    -- 商品名称
        cid INT, -- 外键字段
        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,  -- 中间表id
        uid 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);

（2）多表查询-笛卡尔积查询(了解)

有两张表，获取这两个表的所有组合情况

要完成多表查询，需要消除这些没有用的数据

多表查询格式

(3)多表查询-内连接查询

查询原理

内连接查询的是两张表有交集的部分数据(有主外键关联的数据)

显式内连接
隐式内连接
（4）多表查询-外连接查询
左连接查询：查询左表的全部数据，和左右两张表有交集部分的数据

右外连接：查询右表的全部数据，和左右两张表有交集部分的数据

（5）多表查询-子查询

子查询：查询语句中嵌套了查询语句。我们就将嵌套查询称为子查询！

-- 查询年龄最高的用户姓名
SELECT MAX(age) FROM USER;              
-- 查询出最高年龄
SELECT NAME,age FROM USER WHERE age=26; 
-- 根据查询出来的最高年龄，查询姓名和年龄
SELECT NAME,age FROM USER WHERE age = (SELECT MAX(age) FROM USER);
-- 查询张三和李四的订单信息
SELECT id FROM USER WHERE NAME='张三' OR NAME='李四';   
-- 查询张三和李四用户的id
SELECT number,uid FROM orderlist WHERE uid=1 OR uid=2; 
-- 根据id查询订单
SELECT number,uid FROM orderlist WHERE uid IN (SELECT id FROM USER WHERE NAME='张三' OR NAME='李四');
-- 查询订单表中id大于4的订单信息和所属用户信息
SELECT * FROM USER u,(SELECT * FROM orderlist WHERE id>4) o WHERE u.id=o.uid;