ビューの削除

#  
/*
  ：   ，        （                ）
              ，   SQL  
  ：
	create view    
	as
	select   
	from  1
	【    】join  2
	on     
	【where    】
	。。。
	；
	
              ：
	1、        SQL  ：    、distinct、group by、having、union、union all
	2、    
	3、select      
	4、join（    92  ，）
	5、from           
	6、where         from     （from、where      ）
	
       ：
		       		        		    
	  	     view		      （    ）	    ，     
	 	     table		  				    
*/ 
# 、        
-- -------------------------------------------------------
#  1、            
-- ----------      -------------
SELECT 
  `studentname`,
  `majorname` 
FROM
  `student` AS s 
  INNER JOIN `major` AS m 
    ON s.`majorid` = m.`majorid` 
WHERE s.`studentname` LIKE ' %' ;

-- ----------    -----------------
CREATE VIEW s_m            #     
AS
SELECT `studentname`,`majorname`
FROM `student` AS s
INNER JOIN `major` AS m
ON s.`majorid`=m.`majorid`;

SELECT `studentname`,`majorname`
FROM s_m
WHERE `studentname` LIKE ' %';         #    

-- ----------------------------------------------------
#  2、       a    ，        
#  
CREATE VIEW mydeg
AS
SELECT `last_name`,`department_name`,`job_title`
FROM `departments` AS d
INNER JOIN `employees` AS e ON d.`department_id`=e.`department_id`
INNER JOIN `jobs` AS j ON e.`job_id`=j.`job_id`;

#  
SELECT `last_name`,`department_name`,`job_title`
FROM mydeg
WHERE `last_name` LIKE '%a%';

-- --------------------------------------------------
#  2、            
-- -----------      --------------
SELECT av.`department_id`,`grade_level`
FROM `job_grades` AS jg
INNER JOIN(
	SELECT `department_id`,AVG(`salary`) AS avs
	FROM `employees`
	GROUP BY `department_id`) AS av
ON av.avs BETWEEN jg.`lowest_sal` AND jg.`highest_sal`;

-- -----------     -----------------
#  
CREATE VIEW myv2
AS
SELECT `department_id`,AVG(`salary`) AS avs
	FROM `employees`
	GROUP BY `department_id`;

#  	
SELECT myv2.`avs`,g.`grade_level`
FROM myv2
INNER JOIN `job_grades` AS g
ON myv2.`avs` BETWEEN g.`lowest_sal` AND g.`highest_sal`;

-- ---------------------------------------------
#  3、             
SELECT * FROM myv2
WHERE avs=(SELECT MIN(avs) FROM myv2);  #   +  

SELECT d.*,myv2.`avs`
FROM myv2
INNER JOIN `departments` AS d
ON d.`department_id`=myv2.`department_id`
WHERE avs=(SELECT MIN(avs) FROM myv2); 
-- ------------------------------------------
SELECT * FROM myv2 ORDER BY avs LIMIT 1;  #  +  

SELECT d.*,myv2.`avs`
FROM myv2
INNER JOIN `departments` AS d
ON d.`department_id`=myv2.`department_id`
ORDER BY avs LIMIT 1;

-- ---------------------------------------------------------
#  4、               
#    -------      ---------
CREATE VIEW myv3
AS 
SELECT * FROM myv2 ORDER BY avs LIMIT 1;
#    -------     -----------
SELECT d.`department_name`,m.`avs`
FROM myv3 AS m
INNER JOIN `departments` AS d
ON d.`department_id`=m.`department_id`;


# 、     
-- -------------------------------------------------------
/*
   ：
	create or replace view     
	as
	    ;
*/

-- --------     --------------
CREATE OR REPLACE VIEW myv3
AS 
SELECT AVG(`salary`),`job_id`
FROM `employees`
GROUP BY `job_id`;

-- ----------------------------------------------------------
/*
   ：
	alter view     
	as
	    ;
*/
-- --------     --------------
ALTER VIEW myv3
AS 
SELECT * FROM `job_grades`;

SELECT * FROM myv3;


# 、     
-- -------------------------------------------------------
/*
  ：
	drop view   1，  2......;
*/
DROP VIEW myv1,myv2,myv3;


# 、    
-- --------------------------------------------------------
#1、desc
DESC myv3;
#2、show
SHOW CREATE VIEW myv3;


# 、     
-- --------------------------------------------------------
CREATE OR REPLACE VIEW myv1
AS 
SELECT `last_name`,`email`,salary*12*(1+IFNULL(commission_pct,0)) AS "annual salary"
FROM `employees`;

CREATE OR REPLACE VIEW myv1
AS 
SELECT `last_name`,`email`
FROM `employees`;

-- ---------------------------------
#1、  
INSERT INTO myv1 VALUES('  ','[email protected]');

-- ---------------------------------
#2、  
UPDATE myv1 SET`last_name`='   ' WHERE `last_name`='  ';

-- ---------------------------------
#3、  
DELETE FROM myv1 WHERE `last_name`='   ';

-- ---------------------------------

SELECT * FROM myv1;
SELECT * FROM `employees`;