Summary of "MySQL" simple commands

View Database

SHOW DATABASES;

Create a database

CREATE database IF not EXISTS name;

Select Database

Use database name;

View data tables in a database

SHOW TABLES;

Deleting a database

DROP database name IF EXISTS;

Create a simple database table

CREATE table IF not EXISTS name (
ID INT unstgnd auto_increment PRIMARY KEY,
Name VARCHAR (255) is not NULL,
Sex TINYINT not NULL DEFAULT 1,
Age TINYINT not NULL DEFAULT 0
) ENGINE = MyISAM DEFAULT Charset=utf8;

Add data

INSERT into table name VALUES (NULL, ' Cendxia ', 1,22);

Querying data

SELECT * from table name;

modifying data

UPDATE Table name SET Field 1 = ' value 1 ', field 1 = ' value 2 ' WHERE condition;

Delete data

DELETE from table name WHERE condition;

Create a new normal user

GRANT permissions on the library name. Table name to ' user name ' @ ' hostname ' identified by ' password '

Query All Users

SELECT User,host from Mysql.user

Delete a normal user

DROP user ' username ' @ ' host name ';

To modify the root user password

SET PASSWORD = PASSWORD (' new password ');

Root user to modify normal user password

SET PASSWORD for ' username ' @ ' host name ' =password (' new password ');

Authorized

GRANT permissions on the library name. Table name to ' user name ' @ ' hostname ' identified by ' password ';
GRANT select,insert,update,delete on Cendxia.user to ' username ' @ ' host name ' identified by ' password ';

View Permissions

SHOW GRANTS for ' username ' @ ' host name ';

Revoke permissions

REVOKE permissions on the library name. Table name from ' username ' @ ' hostname ';

Backup

MYSQLDUMP-UROOT-P Database name > location to save

Restore data

Mysql-uroot-p database name

Create a data table

CREATE table IF not EXISTS name (
Field 1 type (length) property index,
Field 2 type (length) property index,
Field 3 Type (length) property index,
Field 4 type (length) property index,
Field N ...--don't add a comma after the last field
) Engine=myisam DEFAULT Charset=utf8;

Build the Table engine

MyISAM-Fast read speed, no transaction support
InnoDB-Slow Read speed support transactional transaction rollback

Some common properties

Unstgnd unsigned properties
Auto_increment Self-increment property (typically used on the ID field)
Zerofill 0 Fill

String type

Char string type (0-255) character fixed length
VARCHAR variable-length string type, 5.0 before (0-255) characters, 5.0 after version (0-65535) characters

View table Structure

DESC table name; (abbreviated version)
DESCRIBE table name;

View the Build Table statement

SHOW CREATE table table name;

Modify Table Name

ALTER table name RENAME to new table name;

Modify the data type of a field

ALTER Table table name MODIFY field Name data Type Property index;

Modify field names

ALTER Table name change original field name new field Name data Type Property index;

Add Field

ALTER table name ADD field Name data Type Property index;
--[first| Afier Field name]
--(first first adds a field to the front.) Afier field name added after a field)

Delete a field

ALTER table name DROP field name;

Modify the arrangement position of a field

ALTER table name MODIFY field Name Data Type Property index afier field name;

Modify the Table engine

ALTER table name engine= engine name; --myisam or InnoDB

Summary of "MySQL" simple commands