MySQL table operation

Tables in MySQL

A table is a structured file that can be used to store data of a particular type. A record in the table has a corresponding caption, which is called a table field.

Various operations for tables in MySQL are created

12345

CREATE TABLE 表名(字段名1类型[(宽度) 约束条件],字段名2 类型[(宽度) 约束条件],字段名3类型[(宽度) 约束条件])ENGINE=innodb DEFAULT CHARSET utf8;

Create TableStudent (IDint  not NULLAuto_incrementPrimary Key, namevarchar( -) not NULL, Ageint  not NULL, sex enum ('male','female') not NULL default 'male', SalaryDouble(Ten,2) not NULL) Engine=InnoDBdefaultCharSet=Utf8;ps: not NULL: Indicates that this column cannot be empty auto_increment: represents self-growth, each growth by default+1Note: Self-growth can only be added on primary key or unique index fieldsPrimary Key: Indicates the primary key (unique and not empty) engine=InnoDB: Represents the storage engine that specifies the current tabledefaultCharSet UTF8: Set the default encoding set for a table

Create a table

primary key, a special unique index that does not allow null values, and if the primary key uses a single column, its value must be unique, and if it is multiple columns, its combination must be unique. Create Tabletb1 (Nidint  not NULLAuto_incrementPrimary Key, Numint NULL) orCreate Tabletb1 (Nidint  not NULL, Numint  not NULL,                Primary Key(nid,num))

PRIMARY Key

self-increment, if you set the self-increment column for a column, you do not have to set this column when inserting data, and the default will be self-increment (only one self-increment in the table)Create Tabletb1 (Nidint  not NULLAuto_incrementPrimary Key, Numint NULL) orCreate Tabletb1 (Nidint  not NULLauto_increment, Numint NULL,                Index(NID)) Note:1, for self-increment columns, must be an index (with a primary key). 2, for self-increment, you can set the step and start values show session variables like 'auto_inc%'; SetSession Auto_increment_increment=2; SetSession Auto_increment_offset=Ten; Show global Variables like 'auto_inc%'; SetGlobal Auto_increment_increment=2; SetGlobal Auto_increment_offset=Ten;

Self-incrementQuerying data in a table

1234567891011

#查询表数据select 字段(多个以","间隔) from 表名;例:  select name,sex from student;或者: select * from student;#实际工作中不建议使用*  #查看表结构desc 表名;例: desc student;#查看创建表信息show create table student;

Delete a table

12345

#删除表droptable 表名; #清空表truncate table表名;

Modify Table

1234567891011121314151617

#添加表字段alter table 表名 add 字段名 类型 约束;例如: alter table student add age int not null default 0 after name;ps: after name 表示在name字段后添加字段 age.  #修改表字段方式一: alter table student modify 字段 varchar(100) null;方式二: alter table student change 旧字段 新字段 int not null default 0;ps:二者区别:change 可以改变字段名字和属性modify只能改变字段的属性   #删除表字段 :alter table student drop 字段名; #更新表名称:rename table 旧表名 to 新表名;

  #添加主键:  alter  table  student primary  key   alter  table  student drop  primary  key  ;p S: If the primary key is self-growing, the above method is not allowed to execute, Please remove the primary key self-growth property before removing the primary key  alter  table  Student Modify ID int  not  null , primary  key  

Update primary KEY operation

Alter Table Add CONSTRAINT Foreign Key REFERENCES  altertabledropforeignkey

FOREIGN Key Update operation

Alter Table Alter Set default  - ; #删除默认值: Alter Table Alter Drop default;

Default value Update actionCopying tables

1234567

#只复制表结构和表中数据CREATETABLE tb2 SELECT * FROM tb1;ps:主键自增/索引/触发器/外键 不会 被复制   #只复制表结构create table tb2 liketb1;ps: 数据/触发器/外键 不会被复制

MySQL table operation