1. Connect to MySQL
Use the installed mysqll command line. By default, the user is the root user. Here, I use the DOS command line to log on.
1. Connect to MySQL on the local machine
Format: mysql-u user name-P User Password
Example: mysql-uroot-padmin
2. connect to a remote MySQL instance
Format: MySQL [-H host address]-u user name-P User Password
Example: mysql-h192.168.24. *-uroot-padmin
3. Exit
Format: Exit
Example: Exit
2. Create a user
1. Add a user named test1 with the password test1 so that the user can log on to any machine and only have the permission to add, delete, modify, and query test.
Format: grant permission on database. * To username @ login host identified by "password"
Example: grant select, insert, update, delete on test. * test1 @ "%" identified by "test1 ".
2. Add a user named Test2 with the password Test2 so that the user can log on locally and have the permission to add, delete, modify, and query all databases.
Format: grant permission on database. * To username @ login host identified by "password"
Example: grant select, insert, update, delete on *. * Test2 @ localhost identified by "Test2 ".
In this way, the data can only be accessed through the web pages on the MySQL host, making the data more secure.
Iii. Database Operations
1. Display commands
Format: Show indicates the project to be displayed (such as databases tables), select indicates the project to be displayed (such as user and version), and desribe indicates
Example:
Display the current database version: Select virsion ();
Display the current user: Select User ();
Show all users: Select User From mysql. user;
Show all databases;
Show tables;
Displays the table details: DESC [Ribe] tablename;
2. Open the database
Format: Use databasename;
Example: Use test;
3. add and delete Databases
Format: Create/drop database databasename
Instance:
Add a database: Create Database test1;
Delete A Database: drop database test1;
4. add, delete, modify, and query tables
Example:
Add a table: Create Table teacher (ID int (3), name char (10) Not null, address varchar (50 ));
Delete A table: Drop table teacher;
Add Table data: insert into Table Name (column name) values (value );
Update table data: Update table name set field 1 = 'A', Field 2 = 'B ';
Delete table data: delete from Teacher [where...]
Import SQL Script: \. File full path or source file full path
Iv. Database Backup
Database backup is a hot topic no matter which database management system it is in. Data first makes the database management systems the same, regardless of the differences. It only indicates whether the function is powerful or not, and whether the encapsulation is complete (we recommend that you do not use the unique features of the database ).
For example, in Oracle, cascade deletion does not need to write the trigger on its own, but auto-incrementing sequence is so troublesome in Oracle.
1. Back up the entire database: mysqldump-uroot-padmin test> C: \ out. SQL
2. Export a table: mysqldump-uroot-padmin test teacher> C: \ out. SQL
V. Conclusion
I believe that with the above part, you can operate MySQL with ease.
In addition, MySQL supports multi-line Input. You only need to enter a ";" in the last line. For example:
Insert into t_user (userid, password, username)
Values ('admin', 'admin', 'zhang san ');