MySQL database application Management

Insert

Test table mysql> Show CREATE TABLE Test\g

CREATE TABLE Test (

ID int (4) NOT NULL auto_increment,

Name Char (a) is not NULL,

Primary KEY (ID)

);

mysql> Insert into test (id,name) value(1, ' Hequan ');

mysql> Select * from test;

mysql> INSERT into Test (name) value (' Hequan '); ID is self-increment, you can insert the name

mysql> INSERT INTO Test value (3, ' Hequna '), (4, ' Hequan '); Do not give columns, insert directly sequentially

Mysqldump-uroot-p123456-b Oldboy >/tmp/oldboy_bak.sql//Backup database backup with Check again

grep-e-V "#|\/|^$|--" /tmp/oldboy_bak.sql

Select from where

mysql> Select id,name from Test where name= ' Hequan ' and/or id=4;

Mysql> Select Id,name from Test limit 0, 2; //Starting from line No. 0, check 2 lines

Mysql> Select Id,name from Test where id>2 and id<4;

Mysql> Select Id,name from Test order by ID Asc/desc;

Multi-Table Query

Mysql> Select Student. Sno,student. Sname,course. Cname,sc. Grade from STUDENT,COURSE,SC where student. Sno=sc. Sno and course. Cno=sc. Cno ORDER by Sno;

mysql> explain select * from test where name= ' Hequan ' \g;//execution process determine if there is a walk index

Possible_keys: NULL

Rows:2

Mysql> CREATE INDEX index_name on test (name);

Possible_keys: index_name

Rows:1

Update

mysql> Update test set name= ' xx ' where id=4 ;

mysql-uroot-p123456 Oldboy </TMP/OLDBOY_BAK.SQL//recovery data, incremental recovery .

Incremental recovery

#log-bin=mysql-bin Open

/application/mysql/data/mysql-bin-hequan.000001

Mysqlbinlog mysql-bin-hequan.000001

mysqladmin-uroot-p123456 Flush-log Cutting Log

mysql-bin-hequan.000002

Mysqlbinlog- D oldboy mysql-bin-hequan.000001 >bin.sql

Delete the wrong statement

mysql-uroot-p123456 Oldboy <bin.sql

Binlog log only the primary database changes

Delete

mysql> Delete from test where id=3; > <

mysql> TRUNCATE TABLE test; Clear table

Change the fields of a table

mysql> alter table test add sex char (4) after name; Add sex//First after name

mysql> rename table test to test1;

mysql> ALTER TABLE test1 Rename to test;

mysql> drop table test;

Garbled

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

Set names Latin1  

CAT/ETC/SYSCONFIG/I18N//System environment

Lang= "ZH_CN. UTF-8 "

VIM/ETC/MY.CNF//server side and Client

[Client]

Default-charater-set=latin1

[Mysqld]

Character-set-server=utf8//5.5 Version

Engine=innodb auto_increment=3 DEFAULT Charset=utf8

This article is from the "what-all" blog, please be sure to keep this source http://hequan.blog.51cto.com/5701886/1773918

MySQL database application Management