MySQL Common commands Summary

MySQL command summary (verbose version)

1. Database-related operations:

1) Create the database:

Mysql> Create Database Temple;

2) View the database:

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| Information_schema |

| MySQL |

| Performance_schema |

| Temple |

| Test |

+--------------------+

5 rows in Set (0.00 sec)

3) Delete the database:

mysql> drop Database Temple;

Query OK, 0 rows Affected (0.00 sec)

4) Use the database:

Mysql> use Temple;

Database changed

2. Related Operations for database tables:

1) Establish the table: (from http://www.cnblogs.com/yunf/archive/2011/04/20/2022193.html)

CREATE TABLE Blog_user

(

User_name Char (a) NOT NULL check (user_name! = "),

User_password Char (a) is not NULL,

User_emial varchar () NOT NULL unique,

Primary KEY (USER_NAME)

) Engine=innodb default Charset=utf8 auto_increment=1;

CREATE TABLE Blog_category

(

Category_name char () NOT NULL check (category_name!= "),

Category_date datetime NOT NULL,

Primary KEY (Category_name)

) Engine=innod default Charset=utf8 auto_increment=1;

CREATE TABLE Blog_article

(

article_id int unsigned NOT NULL auto_increment,

Article_title varchar () NOT NULL unique,

Article_content longtext NOT NULL,

Article_date datetime NOT NULL,

article_readtime int unsigned NOT NULL default 0,

User_name Char (a) is not NULL,

Category_name char () NOT NULL,

Primary KEY (ARTICLE_ID),

Foreign KEY (user_name) references Blog_user (user_name) on the DELETE cascade on UPDATE cascade,

Foreign KEY (Category_name) references Blog_category (category_name) on DELETE cascade on UPDATE cascade

) Engine=innodb default Charset=utf8 auto_increment=1;

E TABLE Blog_comment (

comment_id Int (ten) unsigned not NULL auto_increment,

Comment_content varchar (all) not NULL,

Comment_date datetime not NULL,

article_id Int (ten) unsigned not NULL,

User_name Char (a) is not NULL,

PRIMARY KEY (comment_id),

Foreign KEY (article_id) references blog_article (article_id) on the DELETE cascade on UPDATE cascade,

Foreign KEY (user_name) references Blog_user (user_name) on DELETE cascade on UPDATE cascade

) Engine=innodb default Charset=utf8 auto_increment=1;

CREATE TABLE Blog_photoalbum

(

Photoalbum_name Char (a) NOT null check (photoalbum_name!= "),

Photoalbum_date datetime NOT NULL,

Primary KEY (Photoalbum_name)

) Engine=innodb default Charset=utf8;

CREATE TABLE Blog_photograph

(

Photograph_name varchar (a) NOT null check (photograph_name!= "),

Photograph_date datetime NOT NULL,

Photoalbum_name char () NOT NULL,

Photourl varchar (all) NOT NULL,

Foreign KEY (Photoalbum_name) references Blog_photoalbum (photoalbum_name) on DELETE cascade on UPDATE cascade

) Engine=innodb default Charset=utf8;

2) View Data sheet:

Mysql> Show tables;

+------------------+

| Tables_in_temple |

+------------------+

| blog_article |

| Blog_category |

| blog_comment |

| Blog_photoalbum |

| Blog_photograph |

| Blog_user |

+------------------+

6 rows in Set (0.00 sec)

3) Delete Data sheet

mysql> drop table blog_photoalbum;

Query OK, 0 rows affected (0.01 sec)

4) Edit Data Sheet

To add a column:

Mysql> ALTER TABLE blog_user add column User_phone int (a) not null;

Query OK, 0 rows affected (0.01 sec)

records:0 duplicates:0 warnings:0

Change the column name:

mysql> ALTER TABLE Blog_user change User_phone user_number char (20);

Query OK, 0 rows affected (0.02 sec)

records:0 duplicates:0 warnings:0

To change column properties:

Mysql> ALTER TABLE Blog_user modify User_bumber int (a) not null;

To delete a column:

mysql> ALTER TABLE blog_user drop column user_number;

Query OK, 0 rows affected (0.01 sec)

records:0 duplicates:0 warnings:0

To view the table structure:

mysql> desc Blog_user;

+---------------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+---------------+-------------+------+-----+---------+-------+

| User_name | char (15) | NO | PRI |       NULL | |

| User_password | char (15) |     NO | |       NULL | |

| user_emial | varchar (20) | NO | UNI |       NULL | |

+---------------+-------------+------+-----+---------+-------+

3 Rows in Set (0.00 sec)

3. Related operations of data entry:

1) Insert Entry:

mysql> INSERT INTO Blog_user (User_name,user_emial,user_password) VALUES (' User ', ' [email protected] ', ' Password ');

Query OK, 1 row affected (0.01 sec)

There are other ways, such as

mysql> INSERT into Blog_user values (' 1 ', ' 2 ', ');

2) Update entries

mysql> Update blog_user set user_name= ' test ' where user_name= ' 1 ';

Query OK, 1 row Affected (0.00 sec)

Rows matched:1 changed:1 warnings:0

3) Delete entry

Mysql> Delete from Blog_user where user_name= ' 1 ';

Query OK, 1 rows affected (0.01 sec)

4) Find entry

Mysql> SELECT * from Blog_user where user_name= ' U2 ';

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U2 | P2 | E2 |

+-----------+---------------+------------+

1 row in Set (0.00 sec)

Mysql> Select User_password from Blog_user where User_name= ' U2 ';

+---------------+

| User_password |

+---------------+

| P2 |

+---------------+

1 row in Set (0.00 sec)

4. Find sort Related

1) Like

Mysql> SELECT * from Blog_user where user_name like '%2 ';

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U2 | P2 | E2 |

+-----------+---------------+------------+

1 row in Set (0.00 sec)

Mysql>

Mysql> SELECT * from Blog_user where user_name like ' u% ';

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U2 | P2 | E2 |

+-----------+---------------+------------+

1 row in Set (0.00 sec)

2) and OR

Mysql> SELECT * from Blog_user where user_name like ' u% ' and user_name = ' U2 ';

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U2 | P2 | E2 |

+-----------+---------------+------------+

1 row in Set (0.00 sec)

Mysql> SELECT * from Blog_user where user_name like ' u% ' or user_name = ' U3 ';

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U2 | P2 | E2 |

+-----------+---------------+------------+

1 row in Set (0.00 sec)

3) GROUP BY

Mysql> select * from Blog_user;

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U1 | P1 | E1 |

| U10 | P10 | E10 |

| U2 | P2 | E2 |

| U4 | P4 | e4 |

| u40 | P40 | E40 |

| U5 | P1 | e5 |

| U6 | P6 | E6 |

+-----------+---------------+------------+

7 Rows in Set (0.00 sec)

Mysql> select MAX (user_name) from Blog_user Group by User_password;

+----------------+

| Max (user_name) |

+----------------+

| U5 |

| U10 |

| U2 |

| U4 |

| u40 |

| U6 |

+----------------+

6 rows in Set (0.00 sec)

4) Order BY

Mysql> SELECT * from Blog_user order by user_name;

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U1 | P1 | E1 |

| U10 | P10 | E10 |

| U2 | P2 | E2 |

| U4 | P4 | e4 |

| u40 | P40 | E40 |

| U5 | P1 | e5 |

| U6 | P6 | E6 |

+-----------+---------------+------------+

7 Rows in Set (0.00 sec)

Mysql> SELECT * from Blog_user ORDER by user_name Desc;

+-----------+---------------+------------+

| User_name | User_password | user_emial |

+-----------+---------------+------------+

| U6 | P6 | E6 |

| U5 | P1 | e5 |

| u40 | P40 | E40 |

| U4 | P4 | e4 |

| U2 | P2 | E2 |

| U10 | P10 | E10 |

| U1 | P1 | E1 |

+-----------+---------------+------------+

7 Rows in Set (0.00 sec)

MySQL Common commands Summary