[MySQL] ALTER / VIEW / IF NULL


ALTER - ํ…Œ์ด๋ธ” ๊ด€๋ฆฌ


  • ํ…Œ์ด๋ธ” ์ด๋ฆ„ ๋ณ€๊ฒฝ

      ALTER TABLE ๊ธฐ์กดํ…Œ์ด๋ธ”๋ช…
      RENAME ๋ฐ”๊ฟ€ํ…Œ์ด๋ธ”๋ช…;
    
  • ํ…Œ์ด๋ธ” ์ปฌ๋Ÿผ๋ช… ๋ณ€๊ฒฝ

      ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
      CHANGE COLUMN ๊ธฐ์กด์ปฌ๋Ÿผ๋ช…
          ๋ฐ”๊ฟ€์ปฌ๋Ÿผ๋ช… ๋ฐ์ดํ„ฐํƒ€์ž…;
    
  • ํ…Œ์ด๋ธ” ์ปฌ๋Ÿผ ์ถ”๊ฐ€

      ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
      ADD COLUMN ์ถ”๊ฐ€ํ• ์ปฌ๋Ÿผ๋ช… ํ…Œ์ดํ„ฐํƒ€์ž… [constraint];
    
  • ํ…Œ์ด๋ธ” ์ปฌ๋Ÿผ ์‚ญ์ œ

      ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
      DROP COLUMN ์‚ญ์ œํ• ์ปฌ๋Ÿผ๋ช…;
    
    • ์ฐธ์กฐ ๊ด€๊ณ„๊ฐ€ ์„ค์ •๋œ ์ปฌ๋Ÿผ ์‚ญ์ œ : ON DELETE CASCADE
  • ํ…Œ์ด๋ธ” ์ปฌ๋Ÿผ ์ž๋ฃŒํ˜• ๋ณ€๊ฒฝ

      ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
      CHANGE COLUMN ์ปฌ๋Ÿผ๋ช…
          ์ปฌ๋Ÿผ๋ช… ๋ณ€๊ฒฝํ• ๋ฐ์ดํ„ฐํƒ€์ž…;
    
    
      ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
      MODIFY ์ปฌ๋Ÿผ๋ช… ๋ณ€๊ฒฝํ• ๋ฐ์ดํ„ฐํƒ€์ž…;
    
  • ํ…Œ์ด๋ธ” ์ปฌ๋Ÿผ ์‚ฌ์ด์ฆˆ ๋ณ€๊ฒฝ

      ALTER TABLE table_name MODIFY column_name ๋ฐ์ดํ„ฐํƒ€์ž…(์‚ฌ์ด์ฆˆ);
    
  • ์ œ์•ฝ์กฐ๊ฑด ์ถ”๊ฐ€

    • PK, UNIQUE

        ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
        ADD CONSTRAINT ์ถ”๊ฐ€ํ• ์ œ์•ฝ์กฐ๊ฑด(์ปฌ๋Ÿผ๋ช…);
      
    • FK

        ALTER TABLE FK์ถ”๊ฐ€ํ• ํ…Œ์ด๋ธ”๋ช… ADD FOREIGN KEY(์ปฌ๋Ÿผ๋ช…) 
        REFERENCE ์ฐธ์กฐํ• ํ…Œ์ด๋ธ”๋ช…(์ปฌ๋Ÿผ๋ช…);
      
  • ์ œ์•ฝ์กฐ๊ฑด ์‚ญ์ œ

    • PK, UNIQUE

        ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
        DROP CONSTRAINT ์‚ญ์ œํ• ์ œ์•ฝ์กฐ๊ฑด์ด์žˆ๋Š”์ปฌ๋Ÿผ๋ช…;
      
    • FK

        ALTER TABLE ํ…Œ์ด๋ธ”๋ช…
        DROP FOREIGN KEY ์‚ญ์ œํ• ์ œ์•ฝ์กฐ๊ฑด์ด์žˆ๋Š”์ปฌ๋Ÿผ๋ช…;
      



VIEW


  • SELECT๋ฌธ์„ ๋ณ„๋„์˜ ๋ณ„์นญ์œผ๋กœ ๋ณด๊ด€ํ•ด๋‘๊ณ  ๋ณ„์นญ์„ ํ…Œ์ด๋ธ”์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•˜๋Š” ํ”„๋กœ๊ทธ๋žจ ๋ชจ๋“ˆ, ๊ฐ€์ƒ์˜ ํ…Œ์ด๋ธ”

  • UPDATE, DELETE, INSERT ๋ถˆ๊ฐ€๋Šฅ, SELECT๋งŒ ๊ฐ€๋Šฅ

  • ๋ทฐ ์ƒ์„ฑ

      CREATE VIEW view_name AS(
          SELECT์ฟผ๋ฆฌ
      );
    
  • ๋ทฐ ์‹คํ–‰

      SELECT * FROM view_name;
    
  • ๋ทฐ ์‚ญ์ œ

      DROP VIEW view_name;
    
  • ex)

      create table member(
          num		int		unique auto_increment,
          name	char(10),
          age		int,
          addr	char(20)
      );
    
      insert into member
      (name, age, addr)
      values
      ('A', 25, '์‹ ์‚ฌ๋™'),
      ('B', 34, '์—ฐํฌ๋™'),
      ('C', 32, '์„์ดŒ๋™'),
      ('D', 28, 'ํšŒ๊ธฐ๋™'),
      ('E', 34, 'ํ•ฉ์ •๋™');
    
      create view maxage_view as (
          select name, age, addr from member 
          where age in (
              select max(age) from member
              )
          order by name
      );
    
      select * from maxage_view;
    



IF NULL


  • ํ•ด๋‹น ์ปฌ๋Ÿผ ๊ฐ’์ด NULL์„ ๋ฐ˜ํ™˜ํ•  ๋•Œ ๋‹ค๋ฅธ ๊ฐ’์œผ๋กœ ์ถœ๋ ฅํ•  ์ˆ˜ ์žˆ๋„๋ก ํ•˜๋Š” ํ•จ์ˆ˜

  • SELECT IFNULL(column_name, NULL์ผ๊ฒฝ์šฐ๋Œ€์ฒด๊ฐ’) FROM table_name;
    
    SELECT *,
    IFNULL(column_name, NULL์ผ๊ฒฝ์šฐ๋Œ€์ฒด๊ฐ’) 
    FROM table_name;
    
  • ex)

      create table score(
      subject	char(10),
      score		int
      );
    
      insert into score
      (subject, score)
      values
      ('๊ตญ์–ด', 89),
      ('์˜์–ด', 93),
      ('์ˆ˜ํ•™', null),
      ('ํ•œ๊ตญ์‚ฌ', 97);
    
      select subject as ๊ณผ๋ชฉ, 
      ifnull(score, '๋ฏธ์‘์‹œ') as ์ ์ˆ˜ from score;
    

Categories:

MySQL