MySql8.0(04)

MySql基礎編
マルチテーブル設計:
異なる種類の情報を格納する際のデータ冗長性の低減
テーブルとテーブルの関連付け方法

       :
1.     (    )
2.    (      ),            
3.         ,     ,      

     :
	  ,  ,  ,  ,   ,  ID,    
	
   :
	  ID,    

--      
	  ,  ,  ,  ID

     

--      
CREATE TABLE grade(id INT PRIMARY KEY AUTO_INCREMENT,
                   NAME VARCHAR(10))
                   
--      
CREATE TABLE student(
               num INT PRIMARY KEY AUTO_INCREMENT,
               NAME VARCHAR(10) NOT NULL,
               sex CHAR(1),
               birthday DATE,
               phone CHAR(11),
               grade_id INT, --     
               reg_time DATETIME
)                  
     
     
--    ,       ,      
--    ,                    
                   
 --    ,           
 --     :              
 ALTER TABLE student ADD CONSTRAINT grade_fk FOREIGN KEY(grade_id)  REFERENCES grade(id)  
 
 
 /*
           
	   :        ,         
	   :         
	   :          
           :          ,            
 */
 
 CREATE TABLE course(id INT PRIMARY KEY AUTO_INCREMENT,
			NAME VARCHAR(10)	
			)
 --                     
 CREATE TABLE student_course(
	stu_num INT,
	course_id INT,
	CONSTRAINT stu_fk FOREIGN KEY(stu_num) REFERENCES student(num),
	CONSTRAINT course_fk FOREIGN KEY(course_id) REFERENCES course(id)
 )

サブクエリ

--    :       (insert  update  delete  select)   select  ,         
--        ,         

--  insert        
INSERT INTO stu SELECT * FROM student 

--  update      
UPDATE student SET sex=' ' WHERE num IN (SELECT num FROM stu WHERE score>80)

--  delete        
DELETE FROM student WHERE num IN(SELECT num FROM stu WHERE score>=90)



--           
-- select  :         (    )
SELECT (
	SELECT ts.num
	FROM student ts
	WHERE ts.num = t.num
),t.name FROM student  t

--  where        
SELECT * FROM student WHERE score IN (SELECT score FROM student WHERE score>60)

--  where                  
SELECT * 
FROM student 
WHERE (num,score)=(
		SELECT MIN(num),MAX(score)
		FROM student
)

--  from               (    )
--           ,          
SELECT *
FROM(
     SELECT COUNT(*)c ,sex
     FROM student
     GROUP BY sex) t
WHERE t.c >= 2