MySQL some common command summary

MySQL timestamp to date format
SELECT from_unixtime (add_time, '%y-%m-%d%h:%i:%s ') from ' Wh5_username ' where id=23;

Show process
Show Processlist

Connecting to a database
Mysql-uroot-ppwd.secret-s/data/mysql/mysql_3306.sock

Delete a data table
TRUNCATE TABLE name

Create database with default character set
CREATE DATABASE ' test2 ' DEFAULT CHARACTER SET UTF8 COLLATE utf8_general_ci

Add a database user name password to a database
Grant all on ' database name '. * to ' username ' @localhost identified by ' password ';

Remote Authorization Example:
Grant all privileges the test.* to [e-mail protected] ' 127.41.93.243 ' identified by ' 1234567 ';

Delete User
Use MySQL
Delete from user Where user= ' test ' and host= ' localhost ';

SQL Import
SOURCE Test.sql

To modify an incremented value
ALTER TABLE users auto_increment=0;

modifying fields
ALTER TABLE chatter_users MODIFY COLUMN IP VARCHAR (50);

Modify field names
ALTER TABLE ' test_user ' uc_uid ' type ' tinyint (1) unsigned not NULL DEFAULT ' 0 ';

SQL Export
Export only table structure
Mysqldump-uroot-pdbpasswd-d dbname test>db.sql;
Export table structure and data
MYSQLDUMP-UROOT-PDBPASSWD dbname test>db.sql;
Eg: Comic export statement
mysqldump-uroot-ppwd.secret-h127.0.0.1 yaoyao_test>yaoyao_test20140415.sql;

Allow clients to connect to MySQL operations
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE on ' Test_app '. * to ' manhua_app ' @ ' 214.247.214.90 ' identified by ' jihqle/mu2o ';
Re-authorized a bit, changed the Iptables

Copy table data and table structure
The first method:
Copy entire table
CREATE TABLE new_table SELECT * from old_table;

Copy but not copy data
CREATE TABLE new_table SELECT * from old_table where 0;
There is a problem with the above method: it just builds a table of the result of the SELECT statement. So new_table This table will not have a primary key, index.
You can use this method to improve:
CREATE TABLE new_table (ID int () NOT NULL auto_increment PRIMARY key) SELECT * FROM Old_table
This SQL statement can be implemented to replicate the basic structure, the table's primary key and autogrow, where the new_table ID automatically overrides the old_table ID definition Format

The second method:
The MYTBL table in the production database is quickly copied to mytbl_new,2 commands as follows:
CREATE TABLE mytbl_new like production.mytbl;
INSERT mytbl_new SELECT * from PRODUCTION.MYTBL;
The first command is to create a new data table mytbl_new and copy the MYTBL data table structure.
The second command is to copy the data from the data table Mytbl to the new table mytbl_new.
Note: PRODUCTION.MYTBL is the name of the database that specifies the table to replicate to production. It is optional.
If there is no production. , the MySQL database will assume that MYTBL is in the current operational database.

I use the SECURECRT with the Utf-8 encoding, terminal when Lang=en_us. UTF-8, and after grand classmate reminds, this record note should be utf-8!
Then force the utf-8 to be set on the MySQL command line instead of the default GBK.
SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=28
MSYQL coding problem Reference URL http://blog.chinaunix.net/uid-20677742-id-3133356.html

MySQL export section specifying column data
SELECT field1,field2 from mytable to OUTFILE '/backup/outfile.txt ';

MySQL Find replacement SQL notation
UPDATE ' m_chapter_0 ' SET ' pics ' = replace (' pics ', ' test.jide123.cc:8080 ', ' test.jide123.cc ')
UPDATE ' test_ecms_news_data_1 ' SET ' surl ' = replace (' sURL ', ' vdata.test.org ', ' test.yaoyao.org ');

MySQL some common command summary