69-70连接查询
1 /* 2 SQLyog Ultimate v10.00 Beta1 3 MySQL - 5.7.18-log : Database - girls 4 ********************************************************************* 5 */ 6 7 8 /*!40101 SET NAMES utf8 */; 9 10 /*!40101 SET SQL_MODE=''*/; 11 12 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; 13 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; 14 /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; 15 /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; 16 CREATE DATABASE /*!32312 IF NOT EXISTS*/`girls` /*!40100 DEFAULT CHARACTER SET utf8 */; 17 18 USE `girls`; 19 20 /*Table structure for table `admin` */ 21 22 DROP TABLE IF EXISTS `admin`; 23 24 CREATE TABLE `admin` ( 25 `id` int(11) NOT NULL AUTO_INCREMENT, 26 `username` varchar(10) NOT NULL, 27 `password` varchar(10) NOT NULL, 28 PRIMARY KEY (`id`) 29 ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8; 30 31 /*Data for the table `admin` */ 32 33 insert into `admin`(`id`,`username`,`password`) values (1,'john','8888'),(2,'lyt','6666'); 34 35 /*Table structure for table `beauty` */ 36 37 DROP TABLE IF EXISTS `beauty`; 38 39 CREATE TABLE `beauty` ( 40 `id` int(11) NOT NULL AUTO_INCREMENT, 41 `name` varchar(50) NOT NULL, 42 `sex` char(1) DEFAULT '女', 43 `borndate` datetime DEFAULT '1987-01-01 00:00:00', 44 `phone` varchar(11) NOT NULL, 45 `photo` blob, 46 `boyfriend_id` int(11) DEFAULT NULL, 47 PRIMARY KEY (`id`) 48 ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8; 49 50 /*Data for the table `beauty` */ 51 52 insert into `beauty`(`id`,`name`,`sex`,`borndate`,`phone`,`photo`,`boyfriend_id`) values (1,'柳岩','女','1988-02-03 00:00:00','18209876577',NULL,8),(2,'苍老师','女','1987-12-30 00:00:00','18219876577',NULL,9),(3,'Angelababy','女','1989-02-03 00:00:00','18209876567',NULL,3),(4,'热巴','女','1993-02-03 00:00:00','18209876579',NULL,2),(5,'周冬雨','女','1992-02-03 00:00:00','18209179577',NULL,9),(6,'周芷若','女','1988-02-03 00:00:00','18209876577',NULL,1),(7,'岳灵珊','女','1987-12-30 00:00:00','18219876577',NULL,9),(8,'小昭','女','1989-02-03 00:00:00','18209876567',NULL,1),(9,'双儿','女','1993-02-03 00:00:00','18209876579',NULL,9),(10,'王语嫣','女','1992-02-03 00:00:00','18209179577',NULL,4),(11,'夏雪','女','1993-02-03 00:00:00','18209876579',NULL,9),(12,'赵敏','女','1992-02-03 00:00:00','18209179577',NULL,1); 53 54 /*Table structure for table `boys` */ 55 56 DROP TABLE IF EXISTS `boys`; 57 58 CREATE TABLE `boys` ( 59 `id` int(11) NOT NULL AUTO_INCREMENT, 60 `boyName` varchar(20) DEFAULT NULL, 61 `userCP` int(11) DEFAULT NULL, 62 PRIMARY KEY (`id`) 63 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8; 64 65 /*Data for the table `boys` */ 66 67 insert into `boys`(`id`,`boyName`,`userCP`) values (1,'张无忌',100),(2,'鹿晗',800),(3,'黄晓明',50),(4,'段誉',300); 68 69 /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; 70 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; 71 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; 72 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
连接查询:
含义:又称多表查询,当查询的字段来自于多个表时,就会用到连接查询
笛卡尔乘积现象:表1 有m行,表2有n行,结果=m*n行
产生原因:没有有效的连接条件
如何避免:添加有效的连接条件
连接查询分类:
按年代分类:
sql92标准:仅仅支持内连接
sql99标准(推荐):支持内连接+外连接(左外和右外)+交叉连接
按功能分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接
右外连接
全外连接
SELECT * FROM beauty
SELECT * FROM boys
#查询boys表中的boyname和beauty表中的name
SELECT boyname,name FROM beauty,boys
造成上述结果原因:
拿一张表的记录去挨个匹配另一张表的记录,因为没有什么筛选条件,匹配条件,所以每一条都匹配成功,最终结果是12x4=48条。这种效果被称为笛卡尔乘积现象。
SELECT name,boyName FROM boys,beauty
WHERE beauty.boyfriend_id=beauty.id; #如果boyfriend_id,id前面不加表名会报错:Column 'id' in where clause is ambiguous。因为不知道id属于那张表,需要加表名来限定
#一、sql92标准
#1、等值连接
#案例1:查询女神名和对应的男神名
select name,boyName
from boys,beauty
where beauty.boyfriend_id = boys.id;
#案例2:查询员工名和对应的部门名
select last_name,department_name
from employees,departments
where employees.department_id = departments.department_id;