MYSQL操作のDDL


      ,         : 、 、  ;
         :CREATE、 ALTER、DROP

 .     (DDL)

    1.        
          :CREATE DATABASE     
         :
        CREATE DATABASE mydb1;(  )
        CREATE DATABASE mydb2 CHARACTER SET utf8;   --     
        CREATE DATABASE mydb3 CHARACTER SET gbk COLLATE gbk_chinese_ci ; --               

    2.  
                         
        SHOW DATABASES;

               mydb2        
        SHOW CREATE DATABASE mydb2;

               mydb3   
        DROP DATABASE mydb3;

    3.  
                  ,  mydb2       gbk;
        ALTER DATABASE mydb2 CHARACTER SET gbk;

    4.  
        DROP DATABASE mydb2;

    5.  
                  
        SELECT DATABASE();

             
        USE mydb1;

 .     (DDL)

    1.   
          :
        create table   (
              1     ,
              2     ,
            ...
              n     
        );

              :
            int:  
            double:   ,  double(5,2)    5 ,     2   ,     999.99;
            char:         ; char(10)  'abc       '
            varchar:         ;varchar(10) 'abc'
            text:     ;
            blob:    ;
            date:    ,   :yyyy-MM-dd;
            time:    ,   :hh:mm:ss
            timestamp:      yyyy-MM-dd hh:mm:ss       
            datetime:       yyyy-MM-dd hh:mm:ss

        create table student(
            id int,
            name varchar(20),
            chinese float,
            english float,
            math float
        );

    2.      
          :insert into  [()] values ()

                            ,              ,       。

        insert into student(id,name,chinese,english,math) values(1,'   ',89,78,90);
        insert into student(id,name,chinese,english,math) values(2,'  ',67,98,56);
        insert into student(id,name,chinese,english,math) values(3,'  ',87,78,77);
        insert into student(id,name,chinese,english,math) values(4,'  ',88,98,90);
        insert into student(id,name,chinese,english,math) values(5,'   ',82,84,67);
        insert into student(id,name,chinese,english,math) values(6,'   ',55,85,45);
        insert into student(id,name,chinese,english,math) values(7,'  ',75,65,30);
         
        insert into student(id,name,chinese,english,math) 
                      values(8,'   ',89,78,90),
                            (9,'  ',96,75,95),
                            (10,'  ',81,52,78),
                            (11,'  ',85,72,68);


    3.            
        show tables;

    4.        
        desc student;

    alter  :
    alter table  
    //               ,         
    [add [       ]]|[add]   
    //            ,     。            ,        
    [alter column  ] 
    //            
    [drop column ]  
    //            ,                 
    [drop constraint ]   

    5.      ,   sex,       varchar
        alter table student add age varchar(10);

    6.  age ,     int。  
        alter table student modify age int;

    7.  age ,       。
        alter table student drop age;

    8.    user。
        rename table student to user;

    9.         
        show create table user;

    10.        gbk
        alter table user character set gbk;

    11.  name   username
        alter table user change name username varchar(50);

    12. sid      
    alter table stu add CONSTRAINT PRIMARY key(sid);//

    13.   
        drop table user;

間違いがあれば、訂正してくださいね~