MySQL Common commands and operations

1. Login and exit
1) Login
Windows directly in the DOS command window with the root user login input MySQL enter;
Linux enter the server using Putty to connect to MySQL, and then enter: Mysql-u user name-p password to enter the Mysql> interface.
2) exit
Execute exit Enter.
3) Change Password
Mysql-u Username-p password Password new password

2. Basic operation of the database
1) Display Database
Mysql>show databases;
2) Create a database
Mysql>create database name; Name here refers to the name of the database that needs to be created.
3) Delete Database
Mysql>drop database name; This name refers to the name of the database that needs to be deleted.
4) Select 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, the basic operation of the table
Note: Use databasename must be used before all operations of the table, indicating which database is selected.
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 Table
Mysql>drop table TableName; The tablename here refers to the name of the table to be created.
5) Inserting 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. sql file command (e.g. D:/mysql.sql)
Mysql>use DatabaseName;
Mysql>source D:/mysql.sql;
2) Import data into a database table in text mode
Mysql>load data local infile "filename" into table tablename;

5, User rights actions
   1) Add new user
       grant Select on databasename.* to [email& Nbsp;protected] identified by "password"
   2) Add all permissions to the user
       grant All privileges on * * to [email protected] identified by "password";
   3) Add specific operations to the database to users
       grant Select, Insert,update on DatabaseName . * to [email protected] identified by "password"
   4) Increase the operation rights of a table in the database to the user
        Grant Update,delete on Databasename.tablename to [email protected] identified by "password"
   5) Delete permissions
       revoke all privileges on * * FROM [email protected] br>   6) flush privileges;

6. MySQL Database backup 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//login MySQL
Enter Password:
mysql> use newucdb;
Mysql> Source/home/shenweiyan/mysql-bk/discuzdb_3_2.sql; Import Discuz database information into NEWUCDB's saved information

MySQL Common commands and operations