Common mysql statements _ MySQL

Source: Internet
Author: User
Common mysql statements:

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 ()
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;

Call process
Call createEmpData;

Deletion process
Drop PROCEDURE createEmpData;

Author "ylq365"
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.