MySQL Common commands and operations, MySQL Common commands and operations

MySQL Common commands and operations, MySQL Common commands and operations

1. logon and exit
1) log on
In windows, log on to the doscommand window as the root user and enter mysql and press Enter;
In linux, enter the server that uses PUTTY to connect to mysql, and then enter the mysql-u username-p password to enter the mysql> interface.
2) quit
Run exit and press Enter.
3) change the password
Mysql-u username-p password New password

2. Basic Database Operations
1) display the database
Mysql> show databases;
2) create a database
Mysql> create database name; // The name here refers to the name of the database to be created.
3) delete a database
Mysql> drop database name; // The name here refers to the name of the database to be deleted.
4) Select a database
Mysql> use databasename; // The databasename here refers to the name of the selected database.
5) view the currently used database
Mysql> select database ();

3. Basic table operations
Note: You must use databasename before all operations on the table.
1) display table
Mysql> show tables;
2) display the specific table structure
Mysql> describe tablename;
3) create a table
Mysql> create table tablename (col1 type, col2 type...); // The tablename here refers to the name of the table to be created.
4) delete a table
Mysql> drop table tablename; // here, tablename refers to the name of the table to be created.
5) insert data
Insert 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 the. SQL FILE command (for example, D:/mysql. SQL)
Mysql> use databasename;
Mysql> source d:/mysql. SQL;
2) import data to database tables in text mode
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 permissions to the user
Grant all privileges on *. * to username @ localhost identified by "password ";
3) add specific database operations to users
Grant select, insert, update on databasename. * to username @ localhost identified by "password"
4) add operation permissions for a 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 backup database
=> Mysql-ushenweiyan-p // log on to MySQL
Enter password:
Mysql> use newucdb;
Mysql> source/home/shenweiyan/mysql-bk/discuzdb_3_2. SQL; // import the discuz database information into the save information of newucdb.