MySQL database additions and deletions change statements

#增加数据到xsb表

1. Inserting data into the table

Insert into XSB (XH,XM,NL,XB)

VALUES (' 004 ', ' Xiao Wang ', 18, ' Male ');

If you want to insert data from all the fields in a table, you can omit the writing of the field names, but the data entry must match the order of the fields in the table.

Cases:

INSERT INTO XSB

Valuse (' 005 ', ' Xiao Wang ', 18, ' Male ');

2. If you want to insert more than one data at a time, as follows:

Insert into XSB (XH,XM,XB,NL,JG,SFZH,ZCRQ)
VALUES (' 029 ', ' Xiao Wang ', ' Male ', 18, ' Hebei province Hanhan ', ' 123456789123456729 ', ' 2017-02-08 03:00:00 '),
(' 030 ', ' Xiao Wang ', ' Male ', 18, ' Hebei province Hanhan ', ' 123456789923456730 ', ' 2017-02-08 03:00:00 ');

3. Select....union can also be used, for example:

Insert into XSB (XH,XM,XB,NL,JG,SFZH,ZCRQ)
Select ' 033 ', ' Xiao Wang ', ' Male ', 18, ' Hebei province Hanhan ', ' 123456789123456733 ', ' 2017-02-08 03:00:00 '
Union
Select ' 032 ', ' Xiao Wang ', ' Male ', 18, ' Hebei province Hanhan ', ' 123456789923456732 ', ' 2017-02-08 03:00:00 ';

4. Using the Insert Select to add the data in table A to the existing B table, note that the table A is consistent with the B table format type;

Insert into B (XM,XB,NL) #表b的字段名

Select Xm1,xb1,nl1 #表b的字段名

From a

where xh= ' 003 ' or xh= ' 004 ';

#删除数据从数据库

1. Format

Delete from where condition

Delete

From XSB

Where xm= ' Xiao Wang ';

2. If you want to delete all the data in the table,

Delete from XSB;

Or

TRUNCATE TABLE XSB;

This removes only the data in the table and does not break the table's structure;

drop table XSB;

This will delete the entire table;

#更改表内数据

1. Change the data in the table using the Update...set command;

Update XSB

Data for set changes

Where condition

Cases:

Update XSB

Set NL=20,

xb= ' Women '

Where Xm= ' Zhang San ';

2. If you want to modify the field name or the constraint type of the field name in the table,

#修改表名:

ALTER TABLE XSB rename XSB2;

#修改字段名称

ALTER TABLE XSB change field name new field name type;

#查看表内结构

Desc XSB;

#修改类型约束

ALTER TABLE XSB change field name new field name type constraint;

#增加新字段

ALTER TABLE XSB add field name;

#删除字段

ALTER TABLE XSB drop column field name;

MySQL database additions and deletions change statements