多表联查

多表联查

使用多表联查的场景，有些时候数据在不同的表中，这个时候我们就需要用到mysql中的多表联查。

多表联查概念

将两个或两个以上的表按某个条件连接起来，从而选取需要的数据。多表联查是同时查询两个或两个以上的表时使用的。

多表联查分类

1. 内连接查询

   内连接查询使用关键字join或cross join 或 inner join,
   然后通过on连接表与表之间的条件

   注意： 内连接查询只能查询出两个表符合条件的数据

   语法:

   SELECT 字段,字段1,..
   FROM table_name1
   INNER JOIN table_name2 
   ON table_name1.column_name=table_name2.column_name
   

2. 外连接查询

   2.1 左外连接

   左外连接使用关键字left join,
   然后通过on连接表与表之间的条件
   
   注意：left join 会查询出left join左边的表所有的数据，即使右表没有匹配
   
   语法：
   
       SELECT 字段,字段1,...
       FROM table_name1
       LEFT JOIN table_name2 
       ON table_name1.column_name=table_name2.column_name
   

   2.2 右外连接

   左外连接使用关键字right join,
   然后通过on连接表与表之间的条件
   
   注意：  即使左表中没有匹配，也从右表返回所有的行
   
   语法:
   
       SELECT 字段,字段1,....
       FROM table_name1
       RIGHT JOIN table_name2 
       ON table_name1.column_name=table_name2.column_name
   

多表联查例子

//用户表

create table user(
    id int primary key auto_increment,

    name varchar(150) unique not null,

    pass varchar(255) not null 
)engine=innodb default charset=utf8;

//用户详情表

create table user_detail(
    id int primary key auto_increment,
    uid int not null comment '关联用户表的id',
    city varchar(100) not null,
    sex tinyint not null comment '1男 0女'
)engine=innodb default charset=utf8;

//订单表

create table orders(
    id int primary key auto_increment,
    uid int not null comment '关联用户表的id',
    orderno int not null comment '订单号,关联订单详情表id',
    create_time timestamp not null comment '订单下单时间'
)engine=innodb default charset=utf8;

//插入测试数据

insert into user(name,pass) values('jack',md5('123456'));
insert into user(name,pass) values('mary',md5('123456'));
insert into user(name,pass) values('rose',md5('123456'));
insert into user(name,pass) values('test',md5('123456'));
insert into user(name,pass) values('mark',md5('123456'));

insert into user_detail(uid,city,sex) values(1,'广州',1);
insert into user_detail(uid,city,sex) values(2,'上海',0);
insert into user_detail(uid,city,sex) values(3,'北京',0);
insert into user_detail(uid,city,sex) values(4,'北京',1);

insert into orders(uid,orderno,create_time) values(2,3,now());
insert into orders(uid,orderno,create_time) values(4,5,now());

1. 查询出用户姓名，城市，性别

   //实现方式一,这种方式等价于内连接

   select user.id,name,city,sex from user,user_detail where user.id = user_detail.uid;
   

   //实现方式二

   select name,city,sex from user inner join user_detail on user.id=user_detail.uid;
   

   //实现方式三

   select name,city,sex from user  join user_detail on user.id=user_detail.uid;
   
   select name,city,sex from user cross  join user_detail on user.id=user_detail.uid;
   

   !!这几种方式都是一样的!!

2. 查询出用户的姓名，城市。并且性别为1的用户

   select name,city,sex from user inner join user_detail on user.id=user_detail.uid where sex=1;
   

3. 按照城市对用户分组，得到每个城市的所有用户名字

   select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city;
   

4. 按照城市对用户分组，得到每个城市的所有用户名字，并且只要组人数超过1个人的

   select group_concat(name) from user inner join user_detail on user.id=user_detail.uid group by city having count(*) > 1;
   

5. 查询出订单表中的用户的姓名，性别，城市，用户id,以及用户的下单时间（三表联查）

已有什么，求什么？

已有uid,求姓名，性别，用户下单时间

user表，user_detail,orders

    select name,sex,city,u.id,create_time from orders as o inner join user as u on o.uid=u.id inner join user_detail d on u.id = d.uid;

• 外连接的实例

  1. 列出所有用户的所有信息(包括name,pass,sex,city)

  //使用左连接实现

  select u.id,name,pass,sex,city from user as u left join user_detail as d on u.id = d.uid;
  

  //使用右连接实现

  select u.id,name,pass,sex,city from user_detail as d right join user as u on u.id = d.uid;
  

---

MySQL子查询

• 子查询定义

子查询就是把一个查询嵌套在另一个查询中。

子查询可以包含普通select可以包括任何子句，比如：distinct,group by, order by,limit,join等

下图为子查询说明图：

• 注意

  1. 子查询先执行里面的SQL语句，再执行外面SQL语句。
  2. 子查询的效率比较低，一般建议使用join替换子查询
  3. 子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句再临时表中查询记录。查询完毕后，MySQL需要撤销这些临时表。因此，子查询的速度会受到一定的影响。如果查询的数据量比较大，这种影响就会随之增大。

• 使用子查询原则

  1. 一个子查询必须放在圆括号中
  2. 将子查询放在比较条件的右边，这样可以增加SQL可读性

子查询分类

1. where 型子查询(重点掌握)

   where型子查询把内层查询结果当作外层查询的比较条件

   例如：

   SELECT * FROM user WHERE id in (SELECT uid FROM user_detail );

2. exists/not exists型子查询

   EXISTS关键字表示存在。使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值，如果内层查询语句查询到满足条件的记录，只要子查询中至少返回一个值，则EXISTS语句的值就为True。就返回true，否则返回false。当返回的值为true时，外层查询语句将进行查询，否则不进行查询

3. 使用IN/NOT IN的子查询

   SELECT * FROM user WHERE id not in (SELECT uid FROM user_detail );
   

4. 使用比较运算符的子查询 ( = > < >= <= != )

   SELECT * FROM user WHERE id  = (SELECT uid FROM user_detail limit 1 );
   

子查询实例

//球员信息表
CREATE TABLE players(
playerno int NOT NULL comment ‘球员编号’,
name CHAR(15) NOT NULL,
initials CHAR(3) NOT NULL comment ‘名字首字母’,
birth_date DATE,
sex CHAR(1) NOT NULL,
joined SMALLINT NOT NULL comment ‘参入球队年份’,
street VARCHAR(30) NOT NULL comment ‘所在街道’,
houseno CHAR(4) comment ‘房子编号’,
postcode CHAR(6) comment ‘邮政编码’,
town VARCHAR(30) NOT NULL comment ‘小镇名’,
phoneno CHAR(13) comment ‘电话号码’,
leagueno CHAR(4) comment ‘在球队中的号码’,
PRIMARY KEY (playerno)
)engine=innodb default charset=utf8;

//有过罚款记录的球员信息表

CREATE  TABLE penalties(
    paymentno      int   NOT NULL,
    playerno      int    NOT NULL comment '球员编号',
    payment_date   DATE  NOT NULL comment '罚款时间',
    amount         DECIMAL(7,2) NOT NULL comment '罚款金额',
    PRIMARY KEY    (paymentno)
)engine=innodb default charset=utf8;

//插入测试数据

INSERT INTO players VALUES (2, 'Everett', 'R', '1948-09-01', 'M', 1975, 'Stoney Road','43', '3575NH', 'Stratford', '070-237893', '2411');
INSERT INTO players VALUES (6, 'Parmenter', 'R', '1964-06-25', 'M', 1977, 'Haseltine Lane','80', '1234KK', 'Stratford', '070-476537', '8467');
INSERT INTO players VALUES (7, 'Wise', 'GWS', '1963-05-11', 'M', 1981, 'Edgecombe Way','39', '9758VB', 'Stratford', '070-347689', NULL);
INSERT INTO players VALUES (8, 'Newcastle', 'B', '1962-07-08', 'F', 1980, 'Station Road','4', '6584WO', 'Inglewood', '070-458458', '2983');
INSERT INTO players VALUES (27, 'Collins', 'DD', '1964-12-28', 'F', 1983, 'Long Drive','804', '8457DK', 'Eltham', '079-234857', '2513');
INSERT INTO players VALUES (28, 'Collins', 'C', '1963-06-22', 'F', 1983, 'Old Main Road','10', '1294QK', 'Midhurst', '010-659599', NULL);
INSERT INTO players VALUES (39, 'Bishop', 'D', '1956-10-29', 'M', 1980, 'Eaton Square','78', '9629CD', 'Stratford', '070-393435', NULL);
INSERT INTO players VALUES (44, 'Baker', 'E', '1963-01-09', 'M', 1980, 'Lewis Street','23', '4444LJ', 'Inglewood', '070-368753', '1124');
INSERT INTO players VALUES (57, 'Brown', 'M', '1971-08-17', 'M', 1985, 'Edgecombe Way','16', '4377CB', 'Stratford', '070-473458', '6409');
INSERT INTO players VALUES (83, 'Hope', 'PK', '1956-11-11', 'M', 1982, 'Magdalene Road','16A', '1812UP', 'Stratford', '070-353548', '1608');
INSERT INTO players VALUES (95, 'Miller', 'P', '1963-05-14', 'M', 1972, 'High Street','33A', '5746OP', 'Douglas', '070-867564', NULL);
INSERT INTO players VALUES (100, 'Parmenter', 'P', '1963-02-28', 'M', 1979, 'Haseltine Lane','80', '6494SG', 'Stratford', '070-494593', '6524');
INSERT INTO players VALUES (104, 'Moorman', 'D', '1970-05-10', 'F', 1984, 'Stout Street','65', '9437AO', 'Eltham', '079-987571', '7060');
INSERT INTO players VALUES (112, 'Bailey', 'IP', '1963-10-01', 'F', 1984, 'Vixen Road','8', '6392LK', 'Plymouth', '010-548745', '1319');

INSERT INTO penalties VALUES (1,  6, '1980-12-08',100);
INSERT INTO penalties VALUES (2, 44, '1981-05-05', 75);
INSERT INTO penalties VALUES (3, 27, '1983-09-10',100);
INSERT INTO penalties VALUES (4,104, '1984-12-08', 50);
INSERT INTO penalties VALUES (5, 44, '1980-12-08', 25);
INSERT INTO penalties VALUES (6,  8, '1980-12-08', 25);
INSERT INTO penalties VALUES (7, 44, '1982-12-30', 30);
INSERT INTO penalties VALUES (8, 27, '1984-11-12', 75);

实例

1. 获取和100号球员性别相同的球员号码(where型子查询)

   select sex from players where playerno = 100;
   
   
   select playerno from  players where sex = 'M'
   
   
   select playerno from  players where sex =  (select sex from players where playerno = 100)
   

2. 获取和100号球员性别相同并且居住在同一城市的球员号码。(where型子查询)

   select sex from players where playerno = 100;

   select town from players where playerno = 100;

   select playerno from players where sex = ‘M’ and city = ‘Stratford’;

   select playerno from players where sex = (select sex from players where playerno = 100) and town = (select town from players where playerno = 100);

   select playerno   from players   where (sex, town) = (  
       select sex, town from players  where playerno = 100
   );   
   

3. 获取到所有罚过款的球员信息(重点)

   select playerno from penalties;//1,2,3,4

   select * from players where playerno in (1,2,3,4);

   select * from players where playerno in ( select playerno from penalties);
   

4. 获取最老球员的号码，名字及生日。（即出生日期数值小于或等于所有其它球员的球员）

   select playerno, name, birth_date  from players  where birth_date <= all   
   (select birth_date  from players); 
   

5. 获取那些至少支付了一次罚款的球员的名字和首字母。(exists型子查询)

   select name, initials  from players where exists   
   (select * from penalties  where playerno = players.playerno);
   

6. 当存在Wise球员就查询出所有球员数据

   select name, initials  from players where exists  (select name from players where name ='Wise');
   

7. 查询出被罚款超过50的球员名字与电话

   select name, phoneno from players where playerno in ( select playerno from penalties where amount > 50);