当前位置： 首页 > news >正文

微型购物网站建设模板,网站开发怎样手机号验证,网站建设本科毕业设计论文,个人优秀网站欣赏文章目录 前言关系型数据库中数据表之间的关系数据准备数据内容表间关系 基础查询 | 全部查询多表查询分类1 | 连接查询内连接外连接 | 左外连接外连接 | 右外连接自连接 | 自连接自连接 | 联合查询 分类2 | 子查询返回结果分类 | 标量子查询返回结果分类 | 列子查询返回结果分… 文章目录 前言关系型数据库中数据表之间的关系数据准备数据内容表间关系 基础查询 | 全部查询多表查询分类1 | 连接查询内连接外连接 | 左外连接外连接 | 右外连接自连接 | 自连接自连接 | 联合查询 分类2 | 子查询返回结果分类 | 标量子查询返回结果分类 | 列子查询返回结果分类 | 行子查询返回结果分类 | 表子查询查询位置分类 | WHERE查询位置分类 | FROM查询位置分类 | SELECT 前言 多表查询是在Web开发时候比较常用的操作。一般复杂一点的项目都需要进行多表查询。本文以自建数据表完成多表查询的讲解学习。 关系型数据库中数据表之间的关系 一对多关系 案例部门和员工之间关系一个部门对应多个员工一个员工对应一个部门实现在多的一方建立外键指向一的一方的主键 多对多关系 案例学生和课程的关系关系一个学生可以选择多个课程一个课程可以有多个学生实现建立第三张中间表表中至少有两个外键一个关联学生表的主键一个关联课程表的主键 一对一关系 案例用户和用户详细信息的关系 关系一对一关系多用于单表拆分将一张表的基础字段放在一张表中其他详情字段放在另一张表中以提升操作效率 实现在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE)
数据准备 数据内容 根据给出的SQL文件将数据导入MySQL 数据导入操作参照该文章https://blog.csdn.net/qq_45445505/article/details/137027399 看最后一章即可【补充】通过命令行执行sql文件将数据导入到数据库
SQL文件内容 自己新建一个.sql文件将如下内容复制到文件中。该段sql最终将数据存入一个名字叫learn_mysql的数据库

– MySQL dump 10.13 Distrib 5.7.31, for Win64 (x86_64)

– Host: 127.0.0.1 Database: learn_mysql

– Server version 5.7.31/*!40101 SET OLD_CHARACTER_SET_CLIENTCHARACTER_SET_CLIENT /; /!40101 SET OLD_CHARACTER_SET_RESULTSCHARACTER_SET_RESULTS /; /!40101 SET OLD_COLLATION_CONNECTIONCOLLATION_CONNECTION /; /!40101 SET NAMES utf8 /; /!40103 SET OLD_TIME_ZONETIME_ZONE /; /!40103 SET TIME_ZONE00:00 /; /!40014 SET OLD_UNIQUE_CHECKSUNIQUE_CHECKS, UNIQUE_CHECKS0 /; /!40014 SET OLD_FOREIGN_KEY_CHECKSFOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS0 /; /!40101 SET OLD_SQL_MODESQL_MODE, SQL_MODENO_AUTO_VALUE_ON_ZERO /; /!40111 SET OLD_SQL_NOTESSQL_NOTES, SQL_NOTES0 /;– – Current Database: learn_mysql –CREATE DATABASE /!32312 IF NOT EXISTS/ learn_mysql /!40100 DEFAULT CHARACTER SET utf8 /;USE learn_mysql;– – Table structure for table course –DROP TABLE IF EXISTS course; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE course (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键ID,name varchar(10) DEFAULT NULL COMMENT 课程名称,PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 COMMENT课程表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table course –LOCK TABLES course WRITE; /!40000 ALTER TABLE course DISABLE KEYS /; INSERT INTO course VALUES (1,Java),(2,PHP),(3,MySQL),(4,Hadoop); /!40000 ALTER TABLE course ENABLE KEYS /; UNLOCK TABLES;– – Table structure for table dept –DROP TABLE IF EXISTS dept; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE dept (id int(11) NOT NULL AUTO_INCREMENT COMMENT ID,name varchar(50) NOT NULL COMMENT 部门名称,PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8 COMMENT部门表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table dept –LOCK TABLES dept WRITE; /!40000 ALTER TABLE dept DISABLE KEYS /; INSERT INTO dept VALUES (1,研发部),(2,市场部),(3,财务部),(4,销售部),(5,总经办),(6,人事部); /!40000 ALTER TABLE dept ENABLE KEYS /; UNLOCK TABLES;– – Table structure for table emp –DROP TABLE IF EXISTS emp; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE emp (id int(11) NOT NULL AUTO_INCREMENT COMMENT ID,name varchar(50) NOT NULL COMMENT 姓名,age int(11) DEFAULT NULL COMMENT 年龄,job varchar(20) DEFAULT NULL COMMENT 职位,salary int(11) DEFAULT NULL COMMENT 薪资,entrydate date DEFAULT NULL COMMENT 入职时间,managerid int(11) DEFAULT NULL COMMENT 直属领导ID,dept_id int(11) DEFAULT NULL COMMENT 部门ID,PRIMARY KEY (id),KEY fk_emp_dept_id (dept_id),CONSTRAINT fk_emp_dept_id FOREIGN KEY (dept_id) REFERENCES dept (id) ) ENGINEInnoDB AUTO_INCREMENT18 DEFAULT CHARSETutf8 COMMENT员工表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table emp –LOCK TABLES emp WRITE; /!40000 ALTER TABLE emp DISABLE KEYS /; INSERT INTO emp VALUES (1,金庸,66,总裁,20000,2000-01-01,NULL,5),(2,张无忌,20,项目经理,12500,2005-12-05,1,1),(3,杨逍,33,开发,8400,2000-11-03,2,1),(4,韦一笑,48,开发,11000,2002-02-05,2,1),(5,常遇春,43,开发,10500,2004-09-07,3,1),(6,小昭,19,程序员鼓励师,6600,2004-10-12,2,1),(7,灭绝,60,财务总监,8500,2002-09-12,1,3),(8,周芷若,19,会计,48000,2006-06-02,7,3),(9,丁敏君,23,出纳,5250,2009-05-13,7,3),(10,赵敏,20,市场部总监,12500,2004-10-12,1,2),(11,鹿杖客,56,职员,3750,2006-10-03,10,2),(12,鹤笔翁,19,职员,3750,2007-05-09,10,2),(13,方东白,19,职员,5500,2009-02-12,10,2),(14,张三丰,88,销售总监,14000,2004-10-12,1,4),(15,俞莲舟,38,销售,4600,2004-10-12,14,4),(16,宋远桥,40,销售,4600,2004-10-12,14,4),(17,陈友谅,42,NULL,2000,2011-10-12,1,NULL); /!40000 ALTER TABLE emp ENABLE KEYS /; UNLOCK TABLES;– – Table structure for table student –DROP TABLE IF EXISTS student; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE student (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键ID,name varchar(10) DEFAULT NULL COMMENT 姓名,Num varchar(10) DEFAULT NULL COMMENT 学号,PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 COMMENT学生表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table student –LOCK TABLES student WRITE; /!40000 ALTER TABLE student DISABLE KEYS /; INSERT INTO student VALUES (1,黛绮丝,2000100101),(2,谢逊,2000100102),(3,殷天正,2000100103),(4,韦一笑,2000100104); /!40000 ALTER TABLE student ENABLE KEYS /; UNLOCK TABLES;– – Table structure for table student_course –DROP TABLE IF EXISTS student_course; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE student_course (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键,studentid int(11) NOT NULL COMMENT 学生ID,courseid int(11) NOT NULL COMMENT 课程ID,PRIMARY KEY (id),KEY fk_courseid (courseid),KEY fk_studentid (studentid),CONSTRAINT fk_courseid FOREIGN KEY (courseid) REFERENCES course (id),CONSTRAINT fk_studentid FOREIGN KEY (studentid) REFERENCES student (id) ) ENGINEInnoDB AUTO_INCREMENT7 DEFAULT CHARSETutf8 COMMENT学生课程中间表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table student_course –LOCK TABLES student_course WRITE; /!40000 ALTER TABLE student_course DISABLE KEYS /; INSERT INTO student_course VALUES (1,1,1),(2,1,2),(3,1,3),(4,2,2),(5,2,3),(6,3,4); /!40000 ALTER TABLE student_course ENABLE KEYS /; UNLOCK TABLES;– – Table structure for table tb_user –DROP TABLE IF EXISTS tb_user; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE tb_user (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键ID,name varchar(10) DEFAULT NULL COMMENT 姓名,age int(11) DEFAULT NULL COMMENT 年龄,gender char(1) DEFAULT NULL COMMENT 1: 男 , 2: 女,phone char(11) DEFAULT NULL COMMENT 手机号,PRIMARY KEY (id) ) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 COMMENT用户基本信息表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table tb_user –LOCK TABLES tb_user WRITE; /!40000 ALTER TABLE tb_user DISABLE KEYS /; INSERT INTO tb_user VALUES (1,黄渤,45,1,18800001111),(2,冰冰,35,2,18800002222),(3,码云,55,1,18800008888),(4,李彦宏,50,1,18800009999); /!40000 ALTER TABLE tb_user ENABLE KEYS /; UNLOCK TABLES;– – Table structure for table tb_user_edu –DROP TABLE IF EXISTS tb_user_edu; /!40101 SET saved_cs_client character_set_client /; /!40101 SET character_set_client utf8 /; CREATE TABLE tb_user_edu (id int(11) NOT NULL AUTO_INCREMENT COMMENT 主键ID,degree varchar(20) DEFAULT NULL COMMENT 学历,major varchar(50) DEFAULT NULL COMMENT 专业,primaryschool varchar(50) DEFAULT NULL COMMENT 小学,middleschool varchar(50) DEFAULT NULL COMMENT 中学,university varchar(50) DEFAULT NULL COMMENT 大学,userid int(11) DEFAULT NULL COMMENT 用户ID,PRIMARY KEY (id),UNIQUE KEY userid (userid),CONSTRAINT fk_userid FOREIGN KEY (userid) REFERENCES tb_user (id) ) ENGINEInnoDB AUTO_INCREMENT5 DEFAULT CHARSETutf8 COMMENT用户教育信息表; /!40101 SET character_set_client saved_cs_client /;– – Dumping data for table tb_user_edu –LOCK TABLES tb_user_edu WRITE; /!40000 ALTER TABLE tb_user_edu DISABLE KEYS /; INSERT INTO tb_user_edu VALUES (1,本科,舞蹈,静安区第一小学,静安区第一中学,北京舞蹈学院,1),(2,硕士,表演,朝阳区第一小学,朝阳区第一中学,北京电影学院,2),(3,本科,英语,杭州市第一小学,杭州市第一中学,杭州师范大学,3),(4,本科,应用数学,阳泉第一小学,阳泉区第一中学,清华大学,4); /!40000 ALTER TABLE tb_user_edu ENABLE KEYS /; UNLOCK TABLES; /!40103 SET TIME_ZONEOLD_TIME_ZONE /;/!40101 SET SQL_MODEOLD_SQL_MODE /; /!40014 SET FOREIGN_KEY_CHECKSOLD_FOREIGN_KEY_CHECKS /; /!40014 SET UNIQUE_CHECKSOLD_UNIQUE_CHECKS /; /!40101 SET CHARACTER_SET_CLIENTOLD_CHARACTER_SET_CLIENT /; /!40101 SET CHARACTER_SET_RESULTSOLD_CHARACTER_SET_RESULTS /; /!40101 SET COLLATION_CONNECTIONOLD_COLLATION_CONNECTION /; /!40111 SET SQL_NOTESOLD_SQL_NOTES */;– Dump completed on 2024-03-25 22:41:27表间关系 数据表字段courseid、namedeptid、nameempid、name、age、job、salary、entrydate、managerid、dept_iddeptstudentid、name、Numstudent_courseid、studentidstudent、courseidcoursetb_userid、name、age、gender、phonetb_user_eduid、degree、major、primaryschool、middleschool、university、useridtb_user 加粗的表示外键 基础查询 | 全部查询

全部查询笛卡尔积列举所有组合情况但有的是无效的

select * from emp, dept; – count102# 筛选出上述操作中有效的数据 select * from emp, dept where emp.dept_id dept.id;多表查询 假设如下两个数据表A、B中间绿色部分表示两者的交集。 关于多表查询可以分为如下两类 连接查询通过连接运算符实现多个表的查询两张表需要有联系通过外键连接子查询把一次查询的结果作为条件放到另一个查询中两张表之间不需要有关联字段 一般来说连接查询的效率要高一点因为子查询走的是笛卡尔积会将两个表中的数据都查询比较一遍 分类1 | 连接查询 内连接 内连接是查询两个表交集部分的数据上图绿色部分主要分为隐式内连接和显式内连接 隐式内连接 SELECT 字段列表 FROM 表1 , 表2 WHERE 条件 … ;显式内连接 SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 … ;查询案例根据员工表和部门表查询所有员工姓名及对应部门

隐式内连接

select emp.name , dept.name from emp , dept where emp.dept_id dept.id;select e.name,d.name from emp e , dept d where e.dept_id d.id; – 为每一张表起别名,简化SQL编写# 显式内连接 select e.name, d.name from emp e inner join dept d on e.dept_id d.id;select e.name, d.name from emp e join dept d on e.dept_id d.id; – 为每一张表起别名,简化SQL编写对表格起了别名之后就只能用别名不能用原有表名来获取表了 外连接 | 左外连接 左外连接是查询表A的所有数据包含两者交集部分 基础语法 SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 … ;查询案例查询emp表中的所有数据和对应的部门信息 select e., d.name from emp e left outer join dept d on e.dept_id d.id;select e., d.name from emp e left join dept d on e.dept_id d.id;外连接 | 右外连接 右外连接是查询表B的所有数据包含两者交集部分 基础语法 SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 … ;查询案例查询dept表的所有数据, 和对应的员工信息 select d., e. from emp e right outer join dept d on e.dept_id d.id;select d., e. from dept d left outer join emp e on e.dept_id d.id;自连接 | 自连接 自己和自己做连接查询。自连接查询必须有一个使用别名自连接可以是内连接也可以是外连接 基础语法同上述的内连接和外连接 查询案例1查询员工及其所属领导两个信息均在emp表中体现

内连接解决无所属领导的数据不会被查询最高领导没有所属领导

select a.name , b.name from emp a , emp b where a.managerid b.id;查询案例2查询所有员工及其领导的名字 , 如果员工没有领导, 也需要查询出来

左外连接解决任何员工都被查询无所属领导的员工也被查询到

select a.name 员工, b.name 领导 from emp a left join emp b on a.managerid b.id;查询案例3查询所有领导及其下属员工的名字

右外连接解决该SQL会产生冗余数据因为这个领导表本质是员工表里面有的员工没有下属

若是员工表和领导表在两张表存储右外连接就没问题不会产生冗余信息就像emp和dept

select a.name 员工, b.name 领导 from emp a right join emp b on a.managerid b.id;自连接 | 联合查询 联合查询就是多次查询的结果合并起来形成一个新的查询结果集每次查询的结果是独立的。 联合查询的要求每次查询表要获取的列数一致且类型要一致 基础语法 SELECT 字段列表 FROM 表A … UNION [ ALL ] SELECT 字段列表 FROM 表B ….;union all 会将每次查询的结果直接合并在一起不去重union 会对合并之后的数据去重 查询案例将薪资低于 5000 的员工 或 年龄大于 50 岁的员工全部查询出来 方案1多条件查询or连接即可条件查询就是每行数据判断是否满足不会产生重复 select * from emp where salary 5000 or age 50;方案2联合查询将多次查询的结果合并在一起可能出现重复需要去重 select * from emp where salary 5000 union select * from emp where age 50;分类2 | 子查询 基础语法 SELECT * FROM t1 WHERE column1 ( SELECT column1 FROM t2 );子查询的外部语句可以是INSERT、UPDATE、DELETE、SELECT中的任何一个 子查询分类 根据查询结果分类根据子查询位置分类解释标量子查询返回结果是单个的值数字、字符串、日期等列子查询返回结果是一列数据可以是多行行子查询返回结果是一行数据可以是多列表子查询返回的结果是多行多列在WHERE后面在FROM后面在SECELT后面 返回结果分类 | 标量子查询 查询案例 查询 “销售部” 的所有员工信息 查询销售部id查询员工部门id销售部id的员工 select * from emp where dept_id (select id from dept where name 销售部);返回结果分类 | 列子查询 常用操作符 操作符描述IN在指定的集合范围之内多选一NOT IN不在指定的集合范围之内ANY子查询返回列表中有任意一个满足即可SOME与ANY等同使用SOME的地方都可以使用ANYALL子查询返回列表的所有值都必须满足 查询案例 查询 “销售部” 和 “市场部” 的所有员工信息 查询 “销售部” 和 “市场部” 的部门ID根据部门ID, 查询员工信息 select * from emp where dept_id in (select id from dept where name 销售部 or name 市场部);查询案例 查询比 财务部 所有人工资都高的员工信息 查询所有 财务部 人员工资比 财务部 所有人工资都高的员工信息 select * from emp where salary all ( select salary from emp where dept_id (select id from dept where name 财务部) );返回结果分类 | 行子查询 查询案例 查询与 “张无忌” 的薪资相同及直属领导也相同的员工信息 ; 查询 “张无忌” 的薪资及直属领导查询与 “张无忌” 的薪资及直属领导相同的员工信息 select * from emp where (salary,managerid) (select salary, managerid from emp where name 张无忌);返回结果分类 | 表子查询 查询案例 查询与 “鹿杖客” 或 “宋远桥” 的职位和薪资都相同的员工信息 查询 “鹿杖客” , “宋远桥” 的职位和薪资查询与 “鹿杖客” , “宋远桥” 的职位和薪资相同的员工信息 select * from emp where (job,salary) in ( select job, salary from emp where name 鹿杖客 or name 宋远桥 );查询位置分类 | WHERE 上述任一案例 查询位置分类 | FROM 子查询返回的结果是多行多列 查询案例 查询入职日期是 “2006-01-01” 之后的员工信息 , 及其部门信息 入职日期是 “2006-01-01” 之后的员工信息查询这部分员工, 对应的部门信息 select e., d. from (select * from emp where entrydate 2006-01-01) e left join dept d on e.dept_id d.id ;查询位置分类 | SELECT 子查询返回的结果是多行多列 查询案例 显示每个员工的姓名、年龄及其部门名称 内连接实现对于没有部门的员工无法查询出来 select e.name, e.age, d.name from emp e join dept d on e.dept_id d.id ;子查询实现可以查询所有员工即使员工没有部门 select e.name, e.age, (select name from dept d where d.id e.dept_id) as d_name from emp e;