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 |
#删除表 drop table 表名; #清空表 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 |
#只复制表结构和表中数据 CREATE TABLE tb2 SELECT * FROM tb1; ps:主键自增/索引/触发器/外键 不会 被复制 #只复制表结构 create table tb2 like tb1; ps: 数据/触发器/外键 不会被复制 |
MySQL table operation