MySQL Common command collection

All commands are marked with a semicolon ending with a wrong command; exit

2. /usr/local/mysql/bin/mysql-uroot into MySQL command
   Recommendation: Change the environment variable path to increase the absolute path of MySQL (export path=/usr/local/mysql/bin)
   Mysqladmin-uroot password ' 123456 ' to add a password to the root user
4. Password change operation: mysqladmin-uroot p ' 123456 ' password ' 13456 '
6. Password reset
   vi/etc/my.conf mysqld Add a line skip-grant meaning ignore authorization
   Then restart Mysql/etc/init.d/mysql restart
   Go to MySQL open MySQL library use MySQL
   Update user Set Password =password (' 123456 ') where user = ' root '; Set the new password for root to 123456
   After the modification, delete the skip-grant and restart MySQL.
   4. Common commands to connect to MySQL
   mysql-uroot-p12345
   mysql-uroot-p12345-h127.0.0.1-p3306 Remote Connection
   Mysql-uroot-p12345-s/tmp/mysql.sock Sock Connection
   Mysql-uroot-p12345-e "Show Databases" is used directly for shell scripts
   5. Query library show databases;
   6. Switch the library use MySQL;
   7. View the table in the library show tables;
   8. View the fields in the table desc tb_name;
   9. View the statement when the table is built show create TABLE tb_name\g; G is vertical display, do not add chaos
   10. Check the current user select User ();
   11. View the database that is currently in use Select Databases ();
8. Creating a library Create database db1;
   13. Create table use DB1; CREATE TABLE T1 (' id ' int (4), ' Name/char (40) ');
   14. View the current database version select version ();
   15. View the status of the database show status;
9. View each parameter show variables; Show variables like ' max_connect% ';
   17. View queue show processlist; Show full processlist;
   18.mysql create a user and authorize grant authorization to allow identified to be identified as% wildcard
   Grant All on.To ' user1 ' @ ' 127.0.0.1 ' identified by ' passwd '; Authorized User1 user from 127.0.0.1
   Grant Select,update,insert on DB1.To ' user2 ' @ ' 192.168.133.1 ' identified by ' passwd ';
   Grant all on DB1,To ' user3 ' @ '% ' of ' identified by ' passwd ';
   Show grants;
   Show grants for [email protected]; Used to view the authorization status of a specified user
   After Grant finishes, the flush privileges is also executed;
   Example: How many users are currently queried for MySQL?
   Use MySQL;
   Select User,host from user;
   19. Common SQL statements
   Myusam engine Lookup is fast, count the number of rows InnoDB lookup is slow, does not automatically count the number of rows in the resource
   Select Choose Insert Insert update modify drop delete truncate TABLE empty tables (not deleted) DESC view
   Set is used to modify system parameters or to set variables.
   Seletc Count () from Mysql.user; Count (Find a point in the middle of the row and table in the entire table
   SelectFrom mysql.db;View all Content view MYQL DB table All contents can be added \g
   Select db from Mysql.db; View a single field
   Select DB, user from mysql.db; View multiple fields
   SELECT * from mysql.db where host like ' 192.168.% '; Fuzzy query view 192.168 beginning field of MySQL under DB
   Insert Info db1.t1 vlaues (1, ' abc ');
   Update Db1.ti Set name = ' AAA ' WHERE id = 1;
   TRUNCATE TABLE db1.t1; Empty the TI table under the DB1 library
   drop table db1.t1; Delete a Ti table under the DB1 library
   Drop database db1; Delete DB1 Library
11. Backup and restore of Mysql database back to the library table this applies to small databases
    Backup library mysqldump-uroot-p12345 MySQL >/tmp/mysqlbak.sql backup MySQL library to/tmp/mysqbakl.sql
    Recovery library mysql-uroot-p12345 MySQL </tmp/mysqlbak.sql
    Backup table mysqldump-uroot-p12345 MySQL user >/tmp/user.sql
    Restore table mysql-uroot-p12345 mysql </tmp/user.sql just library name
    Back up all libraries mysqldump-uroot-p-A >/tmp/123.sql-a equivalent to all
    Back up table structure only mysqldump-uroot-p12345-d MySQL >/tmp/mysql.sql
    Some of the most important SQL commands
    SELECT-Extract data from the database
    Update-Updates the data in the database
    Delete-Deletes data from the database
    INSERT INTO-inserts new data into the database
    Create database-Creating new databases
    ALTER DATABASE-Modify databases
    CREATE table-Creates a new table
    ALTER TABLE-Change (change) database table
    drop table-Delete tables
    Create index-Creating indexes (search key)
    Drop INDEX-Delete indexes

MySQL Common command collection