mysql common commands and operations

出1. Login and exit 登 1) Login

Under the DOS command window under the DOS command window under the DOS command window to enter mysql Enter;
Linux Enter the server using Putty to connect to mysql, and then enter: mysql -u username -P password is that it is You can enter the mysql> interface.
2) Exit
Execute exit and press Enter.
3) Change password
mysql -u username-p password password new password

2. Basic database operations

1) Show database

mysql>show databases;
2) Create database
mysql>create database name; //here The name refers to the name of the database that needs to be created.
3) Delete database
Mysql>drop database name; //The name here refers to the name of the database that needs to be deleted.
4) Select the database
Mysql>use databasename; //The databasename here refers to the name of the selected database.
5) Check the currently used database
mysql>select database();

3. Basic operations of the table

Note: Use databasename must be used before all operations on the table; to indicate which database is selected.

1) Display tables s mysql & gt; show tables;
2) Show specific table structure
mysql & gt; description to tableename;
3) Create tables
mysql & gt; create table table (Col1 Type, Col2 Ty per ....); // The tablename here refers to the name of the table to be created.
4) Delete the table
Mysql>drop table tablename; //The tablename here refers to the name of the table to be created.
5) Insert data
into tablename values(col1 value,col2 value....);
6) Query data
select * from tablename where.....;
7) Update data
update tablename set col1 = newvalue where .....;
8) Delete data
delete from tablename where ......;

4. File import
1) Import .sql file command (for example, D:/mysql.sql)

mysql>use databasename;

mysql>source d:/mysql.sql;
2) Use text mode to import data into the database table
mysql>load data local infile "filename" into table tablename;

5. User permission operations
1) Add new users

      grant select on databasename.* to   username@localhost identified by "password"       2) Add all privileges to the user  

          grant all privileges on *.* to   username@localhost identified by "password";  
  3) Add specific operations of the database to the user
Grant select, insert, update on databasename.* to username@localhost identified by "password"
4) Add operation permissions to a certain table in the database to the user
Grant update, delete on databasename.tablename to username@localhost identified by "password"
5) Delete permissions
revoke all privileges on *.* from username@localhost
6) flush privileges;

6. MySQL database backup and migration
1) Remote database backup
mysqldump -h 10.201.10.243 -udiscuz -p discuz >discuz_69.sql
2) Import the backed up database
=> mysql -ushenweiyan -p //Log in to MySQL
                                                                                use using newucdb;