MySQL-based learning

1, MySQL need to import and export individual data records?

- Where the pilot of the data table structure:
MySQL> create Table CHARBASE into outfile Show "/ DB / MySQL / RS.sql";

- Will need to export the contents stored in a separate text file, here we must note that the location must be stored in mysql user has rights position:
MySQL> SELECT * from CHARBASE order by CHARID DESC limit 15 into outfile "/ DB / MySQL / Record . SQL ";
- (note into outfile can only be used in select statement)

- Create the same table structure:
MySQL> create Database Test;
MySQL> Source RS.sql;

- The file data into the new database:
MySQL> LOAD DATA INFILE '/ DB / MySQL / record.sql' INTO TABLE CHARBASE;

2, how to import and export the contents of a table?

- You can select the entire table of contents, with the load into!
- Mysqldump
- mysqldump-h127.0.0.1-uroot-P DBNAME TableName> table.sql

3, how to backup and restore the whole database?

- Backup and restore MySQL database is the most simple thing, normally we do not need any tools, but to copy out the entire catalog can be! (Note, if it is best to migrate the server copy of mysql databases with the past.)
- finished in the copy rights as long as we can make a simple set:
- chown mysql.mysql / DB-R
- mysql.server restart
- but also you can use mysqldump to backup the entire database.
- mysqldump-h127.0.0.1-uroot-P DBNAME> table.sql

4, how to use mysql command to back up mysql database?

- If it is a small mysql database, of course, you can backup the entire database into a standard SQL script file, then restore.
- mysqldump-h127.0.0.1-uroot-P Test> / DB / MySQL / Test.sql;
- create Database test02;
- use test02;
- Source / DB / MySQL / Test.sql;

5, how to export a database structure, but does not export any data?
- Mysqldump-h127.0.0.1-uroot-p-d RecordServer> / db / mysql / RecordServer.sql;

6, how to view database, table, table structure, building form, building a database SQL script?

MySQL> Show Databases;
MySQL> Show Tables;
MySQL> DESC CHARBASE;
MySQL> Show create Table CHARBASE;
- mysqldump-h127.0.0.1-uroot-P-d RecordServer> / DB / MySQL / RecordServer.sql;

7, MySQL storage time and storage inside the field of IP addresses are usually converted wording, how to view their original format?

MySQL> SELECT CHARID, CREATEIP, CREATETIME, MAC, inet_ntoa (CREATEIP) AS ​​IP, FROM_UNIXTIME (CREATETIME) AS TIME FROM CREATEIP WHERE CHARID = 13,566,952;
+----+----+----+--- ---+------+-------+
| CHARID | CREATEIP | CREATETIME | MAC | IP | TIME |
+----+----+----+-- ----+------+-------+
| 13,566,952 | 2,329,037,786 | 1208804488 | 176,940,240,538,368 | 138.210.75.218 | 2008-04-22 03:01:28 |
+----+ ----+----+------+------+-------+
[Note]: Here come the IP address is wrong, correct it should be reverse, such as the top right should be 218.75.210.138.

8, illustrates how to insert a table, modify or delete a field?

- Insert in a field:
MySQL> ALTER TABLE `RELATION` ADD `COUNTRYWARKILL` int (10) unsigned NOT NULL default '0 'after `ZEROCLEAR`;

- Insert the last field:
MySQL> ALTER TABLE `RELATION` ADD `COUNTRYWARKILL` int (10) unsigned NOT NULL default '0 ';

- Removing a field:
MySQL> Table CHARBASE ALTER DROP ZONE; or:
MySQL> ALTER COLUMN Table CHARBASE DROP ZONE;

- Rename the field:
MySQL> ALTER Table CHARBASE Change ZONE not null default ZONE01 Integer '0 ';

- How to rename a table
mysql> ALTER TABLE CHARBASE RENAME CHARNAME;

9, Linux terminals do not support the MySQL command mode to write Chinese, how to solve?

- Directly in the command execution:
MySQL-e "use RecordServer; SELECT CHARBASE.NAME, RELATION.COUNTRYWARKILL FROM CHARBASE, RELATION WHERE CHARBASE.COUNTRY = 4 AND CHARBASE.NAME LIKE 'ho MeN%'"

- The update will update the Chinese name written statement to a text file, with the source to perform:
MySQL> Source / DB / MySQL / Test.sql;

- Written in shell script:
# / bin / bash
MySQL-e "use RecordServer; UPDATE SET RELATION NAME = 'least powerful 2' WHERE CHARID = 15292694; UPDATE RELATION SET NAME = 'saliva, Mong Kok, Man 2' WHERE CHARID = 14771293 ; "

- Direct execution:
MySQL-umysql-p123456-h192.168.1.50 RecordServer-e "INSERT INTO CHARBASE (ACCID, NAME) VALUES (85,164,210, 'Droney.zhao');"

10, how to insert a table, modify, delete operation?

- Inserts into the table:
MySQL> INSERT INTO CHARBASE (ACCID, NAME) VALUES (85,164,210, 'Droney');

- Clear the table:
MySQL> DELETE from COUNTRY;

- Delete some records in the table:
MySQL> DELETE FROM COUNTRY WHERE ID NOT IN (2,3,4,5,6);
MySQL> DELETE FROM COUNTRY WHERE NAME = 'Droney';
MySQL> DELETE FROM COUNTRY WHERE NAME like '% three Qinlang Zi%';

- Update some records in the table:
MySQL> UPDATE SET EXP = 500 COUNTRY WHERE ID IN (2,3,4,5,6);

11, how to rename a table?
mysql> ALTER TABLE CHARBASE RENAME CHARNAME;

12, how to query field the same value of 1 records the number of users, will have such a record only the number of> 9 fields displayed?
SELECT CHARID, COUNT (*) FROM FRIEND WHERE WHICH = 1 GROUP BY CHARID HAVING COUNT (WHICH)> 9;

13, how to modify the mysql user password?
MySQL> use MySQL;
MySQL> Update user set password = password ('123456 ') where user =' MySQL ';
MySQL> FLUSH PRIVILEGES;
or:
mysqladmin-h192.168.1.1-uusername-poldpassword password newpassword

- How to add, delete a MySQL user?
- Add the following method can be recommended, or use the above method to set permissions to add new users.
MySQL> INSERT INTO `user` VALUES ('192 .168.76.250 ',' Droney ', PASSWORD ('123456'), 'Y' , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', ' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ', ",", ",", '0', '0 ', '0' );
MySQL> FLUSH PRIVILEGES;

14, mysql user login password forget how to do or IP restrictions?

- Need to re-initialize the database.
su - dbadmin-c "/ usr / local / MySQL / bin / mysql_db_install"

mv / db / mysql / mysql / db / mysql / mysql_bak & & / usr / local / mysql / bin / mysql_db_install

chown mysql.mysql / db /-R & & mysql.server restart

15, how to view the mysql database code?
mysql> show variables like 'character_set_%';

16, how to set up a table and insert a record?
MySQL> CREATE TABLE `serverlist` (
`ID` int (10) unsigned NOT NULL AUTO_INCREMENT,
`NAME` varchar (32) NOT NULL default ",
PRIMARY KEY (`ID`)
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

mysql> INSERT INTO `SERVERLIST` VALUES (1, 'Droney');

17, how to set access permissions for MySQL?

- The following segment is set only allow 192.168.1.0 to connect, and add dbadmin user, password 123456, and delete all the mysql user password is blank.
MySQL> GRANT ALL PRIVILEGES ON *.* TO dbadmin @ '192 .168.1.% ' IDENTIFIED BY '123456 'WITH GRANT OPTION;
MySQL> DELETE from mysql.user where password = "";
MySQL> flush privileges;

- Other settings demonstration:
MySQL> Grant All on *.* to dbadmin @ localhost identified by 'password';
MySQL> Grant All on dbname .* to dbadmin @% identified by 'password';
MySQL> Grant SELECT, INSERT on dbname. tablename to dbadmin@192.168.1.% identified by 'password';

18, how to add, delete a MySQL user?

- Add the following method can be recommended, or use the above method to set permissions to add new users.
MySQL> INSERT INTO `user` VALUES ('192 .168.76.250 ',' Droney ', PASSWORD ('123456'), 'Y' , 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', ' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ',' Y ', ",", ",", '0', '0 ', '0' );
MySQL> FLUSH PRIVILEGES;

19, if using mysqldump to export the data appears garbled how to do?
iconv-c-f UTF-8-t GB2312 library filename> file name of the new library

20, forgot the mysql password, I do not want to re-initial mysql database, how can I do?

- Kill the old mysql process, with the following command to start mysql, so mysql can not require a password, no IP restrictions are easily landed!
mysql> / usr / local / mysql / bin / mysqld_safe-skip-grant-tables &

21, how to view build thread mysql:
MySQL> Show processlist;

22, other common statement:

mysql> status You can view the status mysql

mysql> tee history.txt operate after all in the mysql command will be stored in history.txt file;

mysql> notee; cancel the operation of the command into a file;

mysql> system clear; can call in mysql shell under the temporary file system commands to operate the system;

- Backup the entire database (to be simple and quick with a number of cp)
mysqldump-h192.168.1.1-uroot-P SuperServer> / SuperServer.sql;

- Export the entire database structure
mysqldump-h192.168.1.1-uroot-p-d SuperServer> / SuperServer.sql;

- Export table structure
mysql> show create table CHARBASE;

- Export table structure and contents of
mysqldump-h192.168.1.1-uroot-p-d SuperServer CHARBASE> / SuperServer.sql;

- Export table structure built script
mysql> show create table CHARBASE;

- Results of a query directly to a file
mysql> select * from mysql.user into outfile 'aaa.sql';

23 to modify the table structure:

- Create a field NAME51, the location of the field after the move DSTZONE
SQL> ALTER TABLE `CHARBASE` add `NAME51` varchar (33) NOT NULL default ";
SQL> ALTER TABLE `CHARBASE NAME51` `` `Change` NAME51 varchar (33) NOT NULL default "AFTER` DSTZONE `;

24 Creating an index:

create index ID on TEMP (CHARID);

25 Advanced Query:

CREATE TABLE `C` (
`ID` varchar (100) NOT NULL default ",
`NAME` varchar (100) NOT NULL default "
) ENGINE = MyISAM DEFAULT CHARSET = latin1;

update A, B set B. ID = A. ID where A.name = B.name;

insert into B (ID, NAME) select ID, NAME from A;

insert into B (ID, NAME, AGE) select ID, NAME, 25 from A;

insert into C (NAME) VALUES ('zhaohang'),(' zhouzhou'),(' xiaoxiao');

26 set by the field increment from the starting value:

Alter tableName auto_increment = 4000;