Add, delete, modify and check MySQL database

for form fields

Table of contents

Adding, deleting, modifying and checking table fields

Modify table name

add field

Modify fields

delete field

Addition, deletion and modification of data

adding data

delete data

change the data

Query data

Case number one

CRUD

Modify table name

ALTER TABLE table name RENAME AS new table name

add field

ALTER TABLE table name ADD field name field name column type [] attribute

Modify fields

ALTER TABLE table name CHANGE field name new field name new field name attribute

delete field

ALTER TABLE table name DROP field name

Addition, deletion and modification of data

adding data

INSERT INTO tablename(field1,field2,…,fieldn) VALUES(data1,data2,…,datan);

delete data

DELETE FROM table name WHERE field name = data;

change the data

UPDATE SET fieldname=data FROM tablename WHERE fieldname=data;

Query data

SELECT field name, field name,…, field name FROM table name WHERE field name = data;

Case number one

  1. Build a table

#Create a staff table 
CREATE  TABLE  {{EJS0}} (
 ​​{{EJS1}}  INT ,
 {{EJS2}}  VARCHAR ( 20 ),
 {{EJS3}}  VARCHAR ( 20 ),
 {{EJS4}}  DATE ,
 {{EJS5}}  VARCHAR ( 10 ),
 {{EJS6}}  VARCHAR ( 50 )
);

  1. Field addition, deletion and modification

#Modify employee table 
#Add age column based on employee table 
ALTER  TABLE workers ADD age INT ( 3 );
 #Modify email length to 50 
ALTER  TABLE workers CHANGE email email VARCHAR ( 50 );
 #Delete remark column 
ALTER  TABLE workers DROP remark;
 #Modify the column name to username     
ALTER  TABLE workers CHANGE  {{EJS7}}  {{EJS8}}  VARCHAR ( 20 );

  1. Data addition, deletion and modification

#Add data 
INSERT  INTO workers VALUES ( 1 , 'Zhang Sanfeng' , 'male' , '1367-10-21' , '12321222@qq.com' , 102 );
 INSERT  INTO workers VALUES ( 2 , 'Dharma' , ' male' , '1227-4-15' , '12321222@126.com' , 54 );
 INSERT  INTO workers VALUES ( 3 , 'Mei Chaofeng' , 'female' , '1547-6-1' ,'12321222@163.com', 44 );
 INSERT  INTO workers VALUES ( 4 , ' Trisolaran' , 'Unknown' , '3012-8-15' , '12321222@fly.com' , 2000 );
 INSERT  INTO workers VALUES ( 5 , 'Super Race Asian' , 'Male' , '1985-2-3' , '12321222@sina.com' , 25 );

#Change the username of the row with id 4 to Frieza 
UPDATE workers SET username= 'Fliza'  WHERE  id = 4 ; 
 #Delete the row with age 2000 
DELETE  FROM workers WHERE age= 2000 ;
 #Set the id to The age of the row of 3 is set to 250 
UPDATE workers SET age= 250  WHERE  id = 3 ;
 #Change the username of the row with id 5 to Sun Wukong and the age to 26 
UPDATE workers SET username= 'Sun Wukong' ,age= 26  WHERE  id = 5 ;
 #Query table data 
SELECT * FROM workers

Leave a Comment

Your email address will not be published. Required fields are marked *