MySQL数据库的多表操作

吾爱主题阅读：109 2024-04-01 23:20:40 评论：0

一、数据库的多表操作

数据库的多表关系：

一对一
一对多
多对一
多对多

二，操作

一对一

建立数据表person和card，设置person数据表id为主键且自增，设置card的id为外键

1 2 3 4 5 6 7 8 9 10 11 12 13 // 创建person表 CREATE TABLE person ( id INT PRIMARY KEY AUTO_INCREMENT, // 主键自增 NAME VARCHAR (20) ); // 创建card表 CREATE TABLE card ( id INT PRIMARY KEY AUTO_INCREMENT, // 主键自增 number VARCHAR (20) UNIQUE NOT NULL ,// 不能为 null pid INT UNIQUE ,// pid唯一 CONSTRAINT cp_fk1 FOREIGN KEY (pid) REFERENCES person(id) // 外键列 );

一对多

创建user数据表和orderlist数据表，这里不再设置uid为唯一值，因此是一对多的关系

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 // 创建 user 表 CREATE TABLE USER ( id INT PRIMARY KEY AUTO_INCREMENT, // 主键自增 NAME VARCHAR (20) ); // 添加数据 INSERT INTO USER VALUES ( NULL , '张三' ),( NULL , '李四' ); // 创建orderlist表 CREATE TABLE orderlist( id INT PRIMARY KEY AUTO_INCREMENT, number VARCHAR (20), uid INT , // 这里没有再设置唯一值 CONSTRAINT out_fk1 FOREIGN KEY (uid) REFERENCES USER (id) // 外键列 ); -- 添加数据 INSERT INTO orderlist VALUES ( NULL , 'hm001' , 1), ( NULL , 'hm002' , 1),( NULL , 'hm003' , 2),( NULL , 'hm004' , 2);

多对多创建student数据表，设置主键，再创建course数据表，创建中间表将两者关联起来

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 // 创建student表 CREATE TABLE student( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (20) ); // 创建course表 CREATE TABLE course ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR (10) ); // 创建中间表 CREATE TABLE stu_course( id INT PRIMARY KEY AUTO_INCREMENT, sid INT , cid INT , CONSTRAINT sc_fk1 FOREIGN KEY (sid) REFERENCES student(id),// 设置外键 CONSTRAINT sc_fk2 FOREIGN KEY (cid) REFERENCES course(id)// 设置外键 );

多表查询-内链查询：

通过规定语法，进行内链查询

1 2 3 4 5 // 标准语法： SELECT 列名 FROM 表名1 [ INNER ] JOIN 表名2 ON 关联条件; -- 查询用户信息和对应的订单信息 SELECT * FROM USER INNER JOIN orderlist ON orderlist.uid = user .id; // 设置别名进行查询 SELECT u. name , u.age, o.number FROM USER u INNER JOIN orderlist o ON o.uid = u.id;

多表查询-隐式内连接：

1 2 3 // 标准语法： SELECT 列名 FROM 表名1,表名2 WHERE 关联条件; // 查询用户姓名，年龄。和订单编号 SELECT u. name , u.age, o.number FROM USER u, orderlist o WHERE o.uid = uid;

多表查询-左外连接：

1 2 3 4 5 标准语法： SELECT 列名 FROM 表名1 LEFT [ OUTER ] JOIN 表名2 ON 条件; // 查询所有用户信息，以及用户对应的订单信息 SELECT u.* o.number FROM USER u LEFT OUTER JOIN orderlist o ON o.uid = u.id;

多表查询-右外连接：

1 2 3 标准语法： SELECT 列名 FROM 表名1 RIGHT [ OUTER ] JOIN 表名2 ON 条件; // 查询所有订单信息，以及订单所属的用户信息 SELECT o.*, u. name FROM USER u RIGHT OUTER JOIN orderlist o ON o.uid = u.id;

多表查询-子查询：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 // 结果是单行单列的 // 标准语法： SELECT 列名 FROM 表名 WHERE 列名=( SELECT 列名 FROM 表名 [ WHERE 条件]); // 查询年龄最高的用户姓名 SELECT NAME ,age FROM USER WHERE age=( SELECT MAX (age) FROM USER ); // 结果是多行单列的 // 标准语法： SELECT 列名 FROM 表名 WHERE 列名 [ NOT ] IN ( SELECT 列名 FROM 表名 [ WHERE 条件]); // 查询张三和李四的订单信息 SELECT * FROM orderlist WHERE uid IN ( SELECT id FROM USER WHERE NAME IN ( '张三' , '李四' )); // 结果是多行多列的 // 标准语法： SELECT 列名 FROM 表名 [别名],( SELECT 列名 FROM 表名 [ WHERE 条件]) [别名] [ WHERE 条件]; // 查询订单表中id大于4的订单信息和所属用户信息 SELECT u. name , o.number FROM USER u, ( SELECT * FROM orderlist WHERE id > 4) o WHERE o.uid=u.id;