Operation of the MySQL database table

Syntax: CREATE TABLE table name (field name 1 type [(width) constraint], field name 2 Type [(width) constraint], field name 3 Type [(width) constraint]); Note:1. In the same table, the field name cannot be the same as 2 . Width and constraints optional 3. Field names and types are required

# Add a field to a table CREATE Database DB1 charset utf8;use db1;create table T1 (       ID int,     name varchar (50
   
    ),    sex enum (
    '
    male
    ',
    '
    female
    '
    ) ,    Age int (3
    )    
    
    # 
    View all table names under the DB1 library
   

　　Garbled problem

Mysql>CREATE Database db1 charset Latin1;mysql>Use db1;mysql> CREATE table T1 (name varchar (20) ); MySQL> Show CREATE TABLE t1;#Viewing the table, the Discovery table defaults to the same character encoding as the data db1mysql> INSERT INTO T1 values ('Song');#insert error in Chinese because Latin1 does not support ChineseERROR 1366(HY000): MySQL>#Workaround One: Delete library db1, rebuild DB1, character encoding specified as UTF8#workaround two: Modifymysql> ALTER TABLE T1 charset UTF8;#Modifying the encoding of table T1mysql> INSERT INTO T1 values (' song');#Although the T1 code is changed, the T1 field name is still created according to Latin1 encoding .ERROR 1366(HY000): MySQL> ALTER TABLE t1 modify name varchar (20);#you need to redefine the following field namemysql> INSERT INTO T1 values (' song'); MySQL> select * fromT1;#don't forget to change the database encoding to UTF8, so that when you create a table under that database, the default UTF8 is encoded

　　Resolve Encoding issues Permanently

#1. Modify the configuration file[Mysqld]default-character-set=UTF8 [Client]default-character-set=UTF8 [Mysql]default-character-set=UTF8#mysql5.5 Above: Modification method has changed[mysqld] character-set-server=UTF8 Collation-server=utf8_general_ci [client] default-character-set=UTF8 [MySQL] default-character-set=UTF8#2. Restart the service#3. View the results of the changes:\sshow variables like'%char%'

　　View table Structure

describe  table name; show CREATE table  table name \g; # View Detail Table structure

　　Modify Table

Syntax:1. Modify the table name alter the table name RENAME a new table name;2Add field ALTER table name add field name data type [integrity constraint ...], add field name data type [FULL    Conditions of sexual restraint ...];  ALTER table name ADD field name data type [integrity constraint ...]    First;  ALTER table name ADD field name data type [integrity constraint ...]                            After field name; 3Delete field ALTER table name drop field name;4. Modify field ALTER TABLE name MODIFY field name data type [integrity constraint ...];      ALTER Table table name change old field name new field name old data type [integrity constraint condition ...]; ALTER Table name change old field name new data type [integrity constraint ...]; Example:1. Modify the storage engine MySQL>ALTER TABLE serviceEngine=InnoDB;2. add Field MySQL>ALTER TABLE student10Add name varchar (20) notNULL,-Add age int (3) notNull default 22; MySQL>ALTER TABLE student10Add Stu_num varchar (10) notNull after name; //after adding the name field MySQL>ALTER TABLE student10Add Sex enum ('male','female') Default'male'First //add to the front3. Delete field MySQL>ALTER TABLE student10-Drop Sex;mysql>ALTER TABLE service-drop mac;4. Modify the field type Modifymysql>ALTER TABLE student10-Modify Age Int (3); MySQL>ALTER TABLE student10-Modify ID int (11) notNULL primary key auto_increment; //Modify the primary key5add constraints (increase auto_increment for existing primary keys) MySQL> ALTER TABLE student10 modify ID int (11) notNULL primary key auto_increment; ERROR1068 (42000): Multiple primary key Definedmysql> ALTER TABLE student10 modify ID int (11) notNULL auto_increment; Query OK, 0 rows affected (0.01sec) records:0 duplicates:0 warnings:06Add a composite primary key to a table that already exists MySQL>ALTER TABLE Service2-Add primary key (Host_ip,port); 7. Increase the primary key MySQL>ALTER TABLE Student1-Modify name varchar (10) notNULL primary key;8add primary keys and auto-grow MySQL>ALTER TABLE Student1-Modify ID int notNULL primary key auto_increment;9Remove the primary key A. Remove the self-increment constraint MySQL> ALTER TABLE student10 modify ID int (11) notnull; b. Delete primary key MySQL>ALTER TABLE student10--drop primary key;

　　Copying tables

Copy table structure + record (key does not replicate: primary key, foreign key, and index) MySQL  from Service; Copy table structure only MySQL  from service where 1=2;        condition is false, no record is found for empty set (0.00 sec) MySQL from service where 1=2;  Query OK, 0 rows affected (0.00 sec) records:0  duplicates:0  warnings:0mysql> CREATE TABLE t 4 like employees;

　　Delete a table

DROP table name;

Operation of the MySQL database table