多表的设计关联查询

最新推荐文章于 2024-03-26 06:12:25 发布

吃橘子的Crow

最新推荐文章于 2024-03-26 06:12:25 发布

阅读量1k

点赞数

文章标签：数据库 sql

本文链接：https://blog.csdn.net/dfdbb6b/article/details/126668900

版权

1.数据库设计范式

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的
规则。于是存在3种范式

第一范式:要求列保持原子性特征(不可拆分)

第二范式:要有主键,要求其他字段依赖于主键(有一个列能够保持唯一),便于查找.
第三范式:消除传递依赖,方便理解,消除数据冗余.

2. 外键

外键:引用另外一个数据表的某条记录。

外键的类型因与主键类型保持一致,数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来的。

约束规则:

ALTER TABLE 表名 ADD CONSTRAINT 名称(自定义) FOREIGN KEY (表中的相关列) REFERENCES 相关表格(相关列)

类如:

CREATE TABLE basketmember(
编号 INT PRIMARY KEY AUTO_INCREMENT,
名字 VARCHAR(10),
生日 DATE,
身高 DOUBLE(3,2) CHECK(身高>1.8),
体重 INT,
位置 VARCHAR(5)
)
INSERT INTO basketmember(名字,生日,身高,体重,位置)
VALUES('德怀恩.韦德','1982-1-17',1.93,96,'null'),
      ('勒布朗.詹姆斯','1984-12-30',2.03,113,'前锋'),
      ('科比.布莱恩特','1978-8-23',1.92,99,'后卫'),
      ('德科.诺维斯基','1978-6-19',2.13,111,'null'),
      ('艾里斯.保罗','1985-5-6',1.82,79,'后卫'),
      ('托尼.帕克','1982-5-17',1.87,83,'后卫'),
      ('凯文.加内特','1981-7-14',2.12,113,'null'),
      ('保罗.皮尔斯','1977-10-13',2.00,106,'前锋'),
      ('迈克尔.乔丹','1963-2-17',1.98,98,'前锋'),
      ('德怀特-霍华德','1985-12-18',2.10,120,'中锋'),
      ('姚明','1980-9-12',2.29,140,'中锋'),
      ('沙奎尔.奥尼尔','1972-3-6',2.15,147,'中锋')  
      ALTER TABLE basketmember ADD COLUMN id INT
      
      CREATE TABLE basketid(
      id INT PRIMARY KEY AUTO_INCREMENT,
      身体素质 DOUBLE(3,1),
      投篮 DOUBLE(3,1),
      控球 DOUBLE(3,1)
      )
      DROP TABLE basketid
      
      INSERT INTO basketid(身体素质,投篮,控球)
      VALUES(9.5,9.5,9.0),
      (10.0,9.5,9.0),
      (9.5,10.0,9.5),
      (7.5,9.5,7.5),
      (9.0,8.5,10.0),
      (8.5,8.5,9.5),
      (8.5,8.0,8.0),
      (7.5,8.5,8.5),
      (10.0,10.0,10.0),
      (10.0,7.5,7.5),
      (9.0,9.0,7.5),
      (9.0,7.5,7.0)
     
     ALTER TABLE basketmember ADD CONSTRAINT id_fk FOREIGN KEY (id) REFERENCES basketid(id)

如果两张表格关联成功后会出现下面一个图标

1、当主表中没有对应的记录时，不能将记录添加到从表
2、不能更改主表中的值而导致从表中的记录孤立
3、从表存在与主表对应的记录，不能从主表中删除该行
4、删除主表前，先删从表

3.多表关联查询

1.笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n(如果没有特定的条件约束,就会发生笛卡尔现象)

2.关联查询:

内连接:满足两张表的交集求出来 语句:

select 结果 from表1 inner join 表2 on 表1.column1 = 表2.column2

左外关联: 把前一个表的全部元素全部打印出来语句:

select 结果 from表1 left join 表2 on 表1.column1 = 表2.column2

右外连接:把后一个表的全部元素全部打印出来语句:

select 结果 from表1right join 表2 on 表1.column1 = 表2.column2

3.子查询:

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或
外查询.

分类：

按子查询出现的位置：select后面：支持标量子查询 from后面：支持表子查询 where：支持标量子查询，列子查询
按功能、结果集的行列数不同：标量子查询（结果集只有一行一列）列子查询（结果集只有一列多行）表子查询（结果集一般为多行多列）

例题:

 CREATE TABLE dept(
     id INT PRIMARY KEY AUTO_INCREMENT,
     dept_name VARCHAR(10),
     dept_decs VARCHAR(10),
     dept_date DATE
     
     )
     DROP TABLE dept
     CREATE TABLE member(
id INT PRIMARY KEY AUTO_INCREMENT,
name_m VARCHAR(10),
gender VARCHAR(10),
age VARCHAR(10),
money VARCHAR(10),
deptid INT
 )
 
 DROP TABLE member
 INSERT INTO member(name_m,gender,age,money,deptid)
 VALUES('王五','男',25,3000,1),
 ('李明','男',23,2500,1),
 ('王小二','男',23,2356,2),
 ('陈发','男',22,3600,2),
 ('小明','男',21,3100,3),
 ('苏奇','男',24,2800,3),
 ('王丽','女',19,1800,1),
 ('李芳','女',18,1900,2)

 1. SELECT m.id,m.name_m,m.gender,m.age,m.money,d.dept_name FROM member m INNER JOIN dept d ON m.deptid=d.id
  
 2. SELECT * FROM member ORDER BY money DESC LIMIT 1
  
 3. SELECT name_m,gender,age,money,deptid FROM member m INNER JOIN dept d ON m.deptid=d.id WHERE money IN(SELECT MAX(money)FROM member GROUP BY deptid)
  
 4.select SUM(money)FROM member WHERE gender='男'
  
 SELECT AVG(money)FROM member WHERE gender='男'
  
 SELECT MAX(money)FROM member WHERE gender='男'
  
 5. SELECT*,d.dept_name FROM member ,dept d WHERE money>(SELECT AVG(money)FROM member)
  
 6. SELECT name_m,gender FROM member WHERE name_m LIKE '王%'
  
 7. SELECT name_m,age FROM member ORDER BY age DESC LIMIT 3
 
 8. SELECT dept_name,SUM(money)FROM member m INNER JOIN dept d ON m.deptid=d.id GROUP BY deptid
  
 9. SELECT dept_name,COUNT(deptid) FROM member m INNER JOIN dept d ON m.deptid=d.id GROUP BY deptid