MySQL common instructions

Modify table structure

service mysql start

#Start the MySQL service. mysql55 is the server name specified when installing the MySQL server
net start mysql55
#Stop mysql service
net stop mysql55

Create database

#Create database 							 Set character set 			 Set collation
create database Database name character set gb2312 collate gb2312_chinese_ci;

#View the databases that exist in the database server
show databases;

#Specify to view a database information
show create database Database name;

modify the database

#Modify database character set and collation
alert database Database name character set utf8 collate utf8_bin;

Delete database

drop database Database name;

Create and view data tables

#Use the use statement to set the current database
use Database name;

#View data sheet
show tables;

#Create data table
create table Table name (
	Field definition 1,
    Field definition 2,
    . . . . . . 
    Field definition n
);

#for example
#Primary key: auto_increment: primary key name comment: self increment user ID: comment
create table user (
	uID int(11) primary key auto_increment comment 'user ID', 
    uName varchar(30) not null,
    uPwd varchar(30) not null,
    uSex enum('male','female') default 'male'	#Notice that there is no comma on the last line
);

#View table structure (method 1)
desc Database name.Table name;

#View table structure (mode 2)
desc Database name.Table name\G;

Modify table

#Modify table name
alert table Original table name rename New table name;

#Modify field
alert table Table name change Original field name new field name new data type;

#Modify only field types
alert table Table name modify Field name new data type;

#Modify the arrangement position of fields
alert table Table name modify Field name 1 data type first|after Field name 2;

#Add new field
alert table Table name add Field name data type;

#Delete field
alert table Table name drop Field name;

#Modify the storage engine of the table
alert table Table name engine=Storage engine name;

Copy table

#Copy table structure and data to new table
create table New table name select * from Original table name;

#Copy to new table structure only
create table New table name select * from Original table name where false;

#After mysql 5.0, you can also use like to copy the table structure
create table New table name like Original table name;

#Copy some fields and data of the table to the new table
create table New table name as (select Field 1,Field 2,.....from Original table name);

Delete table

#Delete table
drop table Table name;

#Delete multiple tables
drop table Table name 1,Table name 2;

Modify table data

Insert a single piece of data with insert statement

insert into Table name [(Field list)] values(Value list);

insert into user values(1,'hello world');

#Inserts data into the specified field
INSERT into user(uName,uPwd) values('Zhang San','admin');

Use the REPLACE statement to insert a single piece of data

REPLACE INTO Table name[(Field list)] values(Value list);

REPLACE INTO user(uId,uName,uPwd) values(3,'Zhang San','admin');

INSERT multiple statements using INSERT statements

INSERT INTO Table name[(Field list)] values(Value list 1)[(Value list 2),...(Value list n)];

#Note that the inserted value must correspond to the previous field
INSERT INTO user(uName,uSex,uPwd) values
('Zhang San','male','admin'),
('Li Si','male','admin1'),
('Outlaw maniac','male','admin2');

Insert multiple statements using REPLACE statement

REPLACE INTO user(uID,uName,uSex,uPwd) values
(5,'Zhang Yi','admin1'),
(6,'Zhang Er','admin2'),
(7,'Zhang Wu','admin3');

Insert other table data

INSERT INTO Target data sheet(Field list I)
SELECT Field list II FROM Source data sheet WHERE Conditional expression; 

Other syntax formats for INSERT statements

INSERT INTO Table name
SET Field name 1=Value 1 [Field name 2=Value 2,....]

#The field order can be adjusted
INSERT INTO user
SET uName='admin',
	uPwd='123',
	uSex='male';

Modify data

The UPDATE statement is used to UPDATE data in a data table

UPDATE Table name
SET Field name 1=Value 1,Field name 2=Value 2 ...,Field name n=value n
[where Conditional expression];

#Modify the password of admin
UPDATE user
SET uPwd = '456'
where uName='admin';

#Modify all passwords in the data table
UPDATE user
SET uPwd = '123';

Delete data

Use delete to delete data

DELETE FROM Table name [WHERE Conditional expression];

#Delete record with id 4
DELETE FROM user
WHERE uID=4;

#Delete all data in the table
DELETE FROM user;

Delete data using TRUNCATE statement

TRUNCATE [TABLE] Table name;

#Delete all data in user table
TRUNCATE user;

Both DELETE and TRUNCATE statements can DELETE all data in the table. Their differences are as follows:

• DELETE statement can DELETE data with conditions, and TRUNCATE can only clear records in the table
• When TRUNCATE statement clears the data in the table and then inserts data into the table, the default initial value of the automatically added field starts from 1; When you use the DELETE statement to clear the data in the table and then add a record to the table, the value of the self increment field will be numbered from the maximum value of the field in the record plus 1.
• Each row of records deleted by the DELETE statement will be recorded in the system operation log. When the TRUNCATE statement clears the data, the deletion will not be recorded in the log. If you want to DELETE all the data in the table, the efficiency of the TRUNCATE statement is higher than that of the DELETE statement.