Database-table-additions and deletions

Table can be added or deleted to change:
   1. The table describes:
        

Id,name,age,sex is called a field, and the rest, a row of content is called a record.
  2. Create a table:
Grammar:
CREATE TABLE Table name (
Field name 1 type [(width) constraint],
Field Name 2 Type [(width) constraint],
Field Name 3 Type [(width) constraint]
);

#注意:
1. Field names cannot be the same in the same table
2. Width and constraints are optional
3. Field names and types are required
Demonstration:
Create Database db1 charset UTF8;
Use DB1;
CREATE TABLE T1 (
ID int,
Name varchar (50),
Sex enum (' Male ', ' female '),
Age Int (3)
);
Show tables; #查看db1库下所有表名
Desc T1;
Select Id,name,sex,age from T1;
SELECT * from T1;
Select Id,name from T1;

INSERT INTO T1 values
(1, ' Egon ', ' Male ', 18),
(2, ' Alex ', ' female ', 20)
;
INSERT into T1 (ID) values
(4),
(5);
Mysql> select * from T1;
+------+------+--------+------+
| ID | name | sex | Age |
+------+------+--------+------+
| 1 | Egon |   Male | 18 |
| 2 | Alex |   Female | 20 |
| 4 | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL |
+------+------+--------+------+

Note: Do not add commas to the last field in the table
   3. View the table structure:
[db1]> describe T1; #查看表结构, abbreviated to DESC table name
+-------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| ID | Int (11) |     YES | |       NULL | |
| name | varchar (50) |     YES | |       NULL | |
| sex | Enum (' Male ', ' female ') |     YES | |       NULL | |
| Age | Int (3) |     YES | |       NULL | |
+-------+-----------------------+------+-----+---------+-------+

[db1]> Show CREATE TABLE t1\g; #查看表详细结构, can add \g
   4. Modify the table structure:
Grammar:
1. Modify the table name
ALTER Table Table Name
RENAME new table name;
2. Add fields
ALTER Table Table Name
ADD field name data type [integrity constraint ...],
ADD field name data type [integrity constraint ...];
ALTER Table Table Name
ADD field name data type [integrity constraint ...] First;
ALTER Table Table Name
ADD field name data type [integrity constraint ...] After field name;
3. Delete a field
ALTER Table Table Name
DROP field name;
4. Modifying fields
ALTER Table 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 ...];
ALTER Table Table Name
Change old field name new field name new data type [integrity constraint ...];
Demonstration:
1. Modifying the storage Engine
mysql> ALTER TABLE service
Engine=innodb;
2. Add a field
Mysql> ALTER TABLE student10
, add name varchar () NOT NULL,
, add age int (3) is not null default 22;

Mysql> ALTER TABLE student10
Add Stu_num varchar () not NULL after name; After adding the Name field

Mysql> ALTER TABLE student10
Add sex enum (' Male ', ' female ') default ' male ' first; Add to the front
3. Delete a field
Mysql> ALTER TABLE student10
Drop sex;
mysql> ALTER TABLE service
Drop Mac;
4. Modify the field type modify
Mysql> ALTER TABLE student10
-Modify Age int (3);
Mysql> ALTER TABLE student10
-Modify ID int (one) NOT null primary key auto_increment; Modify the primary key
5. Add constraint (increase auto_increment for existing primary key)
Mysql> ALTER TABLE student10 modify ID int (one) NOT null primary key auto_increment;
ERROR 1068 (42000): multiple primary key defined
Mysql> ALTER TABLE student10 modify ID int (one) not NULL auto_increment;
Query OK, 0 rows affected (0.01 sec)
records:0 duplicates:0 warnings:0
6. Add 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 (TEN) not null primary key;
8. Increase primary KEY and auto grow
Mysql> ALTER TABLE Student1
-Modify ID int not NULL primary key auto_increment;
9. Delete primary Key
A. Deleting a self-increment constraint
Mysql> ALTER TABLE student10 modify ID int (one) not null;
B. Deleting a primary key
Mysql> ALTER TABLE student10
--drop primary key;
  5. Copy the table:
1. Duplicate table structure + record (key does not replicate: primary key, foreign key and index)
Mysql> CREATE TABLE New_service select * from service;
2. Copy only the table structure
Mysql> SELECT * from service where 1=2; The condition is false, no records are found.
Empty Set (0.00 sec)
Mysql> CREATE TABLE New1_service SELECT * from service where 1=2; # (Key does not replicate: primary key, foreign key, and index)
Query OK, 0 rows Affected (0.00 sec)
records:0 duplicates:0 warnings:0

mysql> CREATE table t4 like employees; # Copy only the table structure, together with the primary key will be copied ...
 6. Delete the table:
DROP table name;
drop table T1;
drop table t1,t2;

Database-table-additions and deletions