MySQL brief note

only export table data , exclude table structure：

mysqldump -uuser -ppassword -t db_name > /dump_name.sql

only export table structure ,exclude table data:

mysqldump -uuser -ppassword -d db_name > /dump_name.sql

only export table structure ,exclude table data（add droop table sentance）：

mysqldump -uuser -ppassword -d –add-drop-table db_name > /dump_name.sql

export a database:

mysqldump -uuser -ppassword db_name > /dump_name.sql

export all databases in mysql:

mysqldump -uuser -ppassword –all-databases > /dump_name.sql

export table hermit in database roamway:

mysqldump -uuser -ppassword roamway hermit > dump_name.sql

export structure of table hermit in database roamway:

mysqldump -uroot -p123456 -d roamway hermit > dump_name.sql

export data of table hermit in database roamway:

mysqldump -uroot -p123456 -t roamway hermit > dump_name.sql

export database db1 and db2 in mysql

mysqldump -uuser -ppassword –databases db1 db2 > /dump_name.sql

drop table email_sms

DROP TABLE IF EXISTS `email_sms`;

set write lock for email_sms

LOCK TABLES `email_sms` WRITE;

 

UNLOCK TABLES;

 

import table structure and data

suppose that there is a need to create a table named action in database test, and insert some data

to table action.

there are 2 setps.

1. build table structure

2.import table data

suppose the table structure in a.sql , table data in b.sql, both a.sql and b.sql in path /home

There are 3 ways to import ：

1. mysql command to import

mysql -uuser -ppassword

mysql>use test;

mysql>source /home/a.sql

mysql>show tables

mysql>show create table action

mysql>source /home/b.sql

mysql>select * from action

 

2. shell command import

mysql -uroot -p test < /home/a.sql

mysql -uroot -p test < /home/b.sql

 

login mysql and switch to database test，checking whether table structure and data exist or not.

mysql>show tables；

mysql>show create table action;

mysql>select * from action;

 

3. create table in mysql directly，and then insert data

mysql -uuser -ppassword

mysql>use test;

mysql>CREATE TABLE `action` （

———————————————

———————————————

）ENGINE=MyISAM AUTO_INCREMENT=13

DEFAULT CHARSET=utf8 COMMENT=’系统行为表’;

 

mysql>INSERT INTO `action` VALUES (

———————————————-

———————————————-

）;

 

login mysql and switch to database test，checking whether table structure and data exist or not.

mysql>show tables；

mysql>show create table action;

mysql>select * from action;

 

delete all records of table action in test database

mysql> use test;

mysql> delete from action;

mysql> select * from action;

 

drop table action in database test

mysql> use test;

mysql> drop table action;

mysql > show tables;

 

delete one record in table action

mysql> use test;

select * from action; ###check all records in table action

mysql> delete from action where id = ’12’; ###delete record in row 12

select * from action; ###check the record which in row 12 exist or not.

 

This record is name and in row 10, update the value of name to 123

mysql> use test;

mysql> select * from action;

mysql> update action set name = ‘123’ where id = ’10’;

mysql> select * from action; ### check whether the record had been changed or not.

 

chack all content of the 10th row in table action.

mysql> use test;

mysql> select * from acton where id = ’10’;

 

in table action, search the 10th row or the row which has the value of name is review.

mysql> use test;

mysql> select * from wp_action where id = ’10’ or name = ‘review’;

 

mysql> use test;

mysql> select title,remark from wp_action where id = ’10’ or name = ‘review’;

 

mysql> use test;

mysql> select remark,rule from action where title = ‘用户登录’ and name = ‘user_login’;

 

mysql> use test;

mysql> select * from wp_action where id = ’10’ or name = ‘review’;

 

mysql> use test;

mysql> select name from action limit 5;

mysql> mysqlselect name from action limit 0,5;

mysql> select name from action order by id asc limit 5;

 

check the first 5 rows of records in table action

mysql> use test;

mysql> select name from action where title = ‘评论’ limit 5;

mysql> select name from action where title = ‘评论’ limit 0,5;

mysql> select name from action where title = ‘评论’ order by id asc limit 5;

(you must put “order by” in lsat，otherwise mistake will happen)

 

check the last 5 rows of records in table action

mysql> use test;

mysql> select * from action where title = ‘评论’ order by id desc limit 5;

 

query amount of records in a specific table

select count(*) from table_name； //in current database

select count(*) from database_name.table_name； //not in current database

 

query how many tables in a database

use destination databae；

show tables;

the result is “x rows in set (y.zm sec）”，and x is on behalf of amount of tables.

or you can execute following command.

select count(*) tables,table_schema from information_schema.tables

where table_schema = ‘destination database’ group by table_schema;

This will display the amount of tables.

 

clear whole table

delete from table_name；

clear data of row 1 in a table

delete from table_name where id=1;