Common mysql statements _ MySQL

Common mysql statements: bitsCN.com

Create a table

Create TABLE emp (
Id INT (10) unsigned not null AUTO_INCREMENT,
Emp_no VARCHAR (10) not null,
Emp_name VARCHAR (50) not null,
Emp_sex CHAR (1) default null,
Emp_salary DOUBLE (10, 2) default NULL,
Birthday Date default null,
Entry_date TIMESTAMP (8 ),
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8

Insert data
Insert into emp (emp_no, emp_name, emp_sex, emp_salary, birthday) values ('20170101', 'zhansan', 'M', 1001, '2017-09-23 ');

Add the table Field number after cityId
Alter table emp ADD number INT (10) after emp_name;

Modify the field name definition of a table
Alter table emp change emp_name emp_namedd VARCHAR (55 );

Modify the field definition position of a table
Alter table emp modify emp_name VARCHAR (20) default 'hangsan ';

Delete table fields
Alter table emp drop emp_name;

Delete table
Drop table emp;

Clear table data
Truncate table emp;

Get table structure
Desc emp;

This is used to optimize the table. This command can merge fragments in the table and eliminate space waste caused by deletion or update. This command only applies to MyISAM BDB and InnoDB.
Optimize table emp;

Creation Process: insert 10000 data records into table emp
Create PROCEDURE createEmpData ()
BEGIN
Set @ x = 1;
Loop1: LOOP
Set @ x = @ x + 1;
If @ x = 10000 THEN
LEAVE loop1;
End IF;
Insert into emp (emp_no, emp_name, emp_sex, emp_salary, birthday) values ('20170101', 'zhansan', 'M', 1001, '2017-09-23 ');
End loop loop1;
END

Call process
Call createEmpData;

Deletion process
Drop PROCEDURE createEmpData;

Author "ylq365"

BitsCN.com