SQL多表操作

• 多表查询

SELECT a.name ,b.id theatre FROM student A ,theatre B
WHERE a.id='2'
AND a.id=b.Studentid;

-- 显示内连接查询  即表与表之间交集的部分
SELECT

	s.`name`,    -- 学生姓名
	s.`age`,     -- 学生年龄
	t.id	     -- 教室id
FROM
    
	student s   -- 表名一

JOIN

	theatre t    -- 表名二

ON

	s.`id` = t.`Studentid`;  -- 关联条件
	
	
-- 隐式内连接查询

SELECT
	s.`name`,    -- 学生姓名
	s.`age`,     -- 学生年龄
	t.id	     -- 教室id
FROM
	student s,
	theatre t
WHERE
	s.`id`=t.`Studentid`;    -- 关联条件
	
	
	
-- 外连接-左外连接  即取另一张表全部信息,以及两张表的交集信息
SELECT

	s.*,        -- 取出整张表的全部信息
	t.`Studentid`  -- 取出两张表有交集的信息

FROM

	student s
	
LEFT OUTER JOIN

	theatre t

ON	
	
	s.`id`=t.`Studentid`;
	
	
-- 外连接-右外连接    	
SELECT

	t.*,
	s.name

FROM

	theatre t
	
RIGHT OUTER JOIN

	student s

ON
	s.id=t.studentid;
	
	
-- 子查询 即嵌套另一个语句进行筛选后再给其进行赋值
SELECT NAME,SPACE FROM theatre WHERE SPACE=(SELECT MAX(SPACE) FROM theatre);	


-- 多行单列显示  通过外键值进行进行两张表之间相互调用
SELECT * FROM theatre WHERE studentid IN(1,2);
SELECT id FROM student WHERE id IN(1,2);
SELECT NAME,number FROM theatre WHERE studentid IN(SELECT id FROM student WHERE id IN(1,2));

-- 子查询多行多列 先根据条件进行筛选表,然后在根据内或者外连接找出两张表的相同点
SELECT * FROM theatre WHERE SPACE>30;

SELECT
       s.`name`,
       s.`age`,
       s.gender,
       t.space

FROM

      student s,
      (SELECT * FROM theatre WHERE SPACE>30) t
      
WHERE

      s.`id`=t.studentid;  
      
      
      
-- 自关联查询   即一张表当成两张表使用 ,条件取表中列名相等的值
SELECT
	t.`id`,
	t.`name`,
	t1.`space`,
	t1.number
FROM
	theatre t
LEFT OUTER JOIN
	theatre t1
ON   
          t.`Studentid`=t1.`id`;

• 多表查询练习

-- 练习一查处student name id age 以及theaterspace

SELECT 
	s.`id`,     -- 查找内容
	s.`name`,
	s.`age`,
	t.space


FROM
	
	student s,   -- 表一
	theatre t    -- 表二


WHERE

       s.`id`=t.`Studentid`;   -- 关联条件
       
       
       
--  练习二 查询student表的用户所有信息, name id age space
-- student信息是否与theater关联都要查出来
-- 使用外连接查询
SELECT

	s.`id`,   --  查询的内容
	s.`name`,
	s.`age`,
	t.space


FROM
	
	student s   -- 表一
	
LEFT OUTER JOIN
	
	theatre t  -- 表二
	
ON

	s.`id`=t.Studentid;  -- 条件
			     -- 因为使用的是左外连接即要显示左边表所以student要写在左边	



-- 右外连接查询  
-- 即查询右边表所有信息 
SELECT

	s.`id`,   -- 查询内容
	s.`name`,
	s.`age`,
	t.`number`

FROM

	student s -- 表一

RIGHT JOIN

	theatre t -- 表二
ON

       s.id=t.`Studentid`; -- 条件
       
       
       
-- 查询年龄大于20用户 ,name age id space
SELECT

	s.`id`,
	s.`name`,
	s.`age`,
	t.`space`
	

FROM

	student s,
	theatre t
	
WHERE  
	s.`id`=t.`Studentid`
	AND
	s.`age`>20;	
	
	
-- 查询四大美女的信息  name  id age space
SELECT

	s.`id`,
	s.`name`,
	s.`age`,
	t.`space`
	

FROM

	student s,
	theatre t
	
WHERE  
	s.`id`=t.`Studentid`
	AND
	s.name IN ('貂蝉','西施','王昭君','杨贵妃');	
	
	
	
-- 创建视图 即将一些重复使用的内容封装在视图内
CREATE VIEW student_theatre AS
SELECT

	s.`id`,
	s.`name`,
	s.`age`,
	t.`space`
	

FROM

	student s,`student`
	theatre t
	
WHERE  
	s.`id`=t.`Studentid`
	AND
	s.`age`>20;	
	
-- 查看视图
SELECT * FROM student_theatre;       



-- 修改视图
UPDATE student_theatre SET NAME='赵六' WHERE NAME = '李四';

-- 修改列名名称
ALTER VIEW student_theatre (id,username,age,SPACE)AS
SELECT

	s.`id`,
	s.`name`,
	s.`age`,
	t.`space`
	

FROM

	student s,
	theatre t
	
WHERE  
	s.`id`=t.`Studentid`
	AND
	s.`age`>20;	
	
	
-- 删除视图前判断即存在则删除
DROP VIEW IF EXISTS student_theatre;
	
	
DROP DATABASE db11;