MySQL Basic instructions

1. Check the character set
SHOW VARIABLES WHERE variable_name like ' character\_set\_% ' OR variable_name like ' collation% ';
2. mysql Character set settings

• System Variables:
–character_set_server: Default internal operation character Set
–character_set_client: The character set used by the client source data
–character_set_connection: Connection Layer Character Set
–character_set_results: Query result Character Set
–character_set_database: The default character set for the currently selected database
–character_set_system: System metadata (field name, etc.) character set
– There are also variables that begin with Collation_ to describe the character Fu She.

3. Some means of detecting character set problem
show CHARACTER SET;
show COLLATION;
show VARIABLES like ' character% ';
show VARIABLES like ' collation% ';
SQL function Hex, LENGTH, Char_length
SQL function CharSet, COLLATION

4. Modify the default character set
(1) The simplest method of modification is to modify the character set key values in the MySQL My.ini file.
such as Default-character-set = UTF8
Character_set_server=utf8
Restart the MySQL service when you are finished modifying it.
(2) MySQL command
Mysql>set Character_set_client=utf8
Mysql>set Character_set_connection=utf8
Mysql>set Character_set_database=utf8
Mysql>set Character_set_results=utf8
Mysql>set Character_set_server=utf8
Mysql>set Collation_connection=utf8
Mysql>set Collation_database=utf8
Mysql>set Collation_server=utf8

5. Display the database in the server
Mysql>show databases;
6. Creating a Database
Mysql>create database mysqldata;
7. Select Database
Mysql>use Mysqldata;
8. View Table
Mysql>show tables;
9. Create a data table
Mysql>create table MyTable (name VARCHAR), sex CHAR (1));
10. Display table Structure
Mysql>describe mytable;
11. Add a record to the table
Mysql>insert into mytable values ("", "" ");
12. Loading data into a database table in text mode
Mysql>load data local infile "d:/mysql.txt" into table mytable;
13. Import. sql file commands
Mysql>use database;
Mysql>source D:/mysql.sql
14. Delete a table
Mysql>drop table mytable;
15. Clear the Table
Mysql>delete from MyTable;
16. Update the data in the table
Mysql>update mytable set sex= "where Name=" "
17. Assigning Permissions to users
The first is to insert the corresponding data row in the user table, set the appropriate permissions, and the other is to create a user with some kind of permission through the grant command.
Grant all on mydb.* to [e-mail protected] identified by "password";
Grant Usage on * * to [e-mail protected] identified by "password";
Grant SELECT, Insert,update on mydb.* to [e-mail protected] identified by "password" l
Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";

18. Global Administrative rights
File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.
Database/data Table/Data column permissions:
Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
INSERT: Adds a table record.
SELECT: Displays/searches the table's records.
UPDATE: Modifies a record that already exists in the table.
Special permissions:
All: Allow to do anything (as root).
USAGE: Allow login only – nothing else is allowed.

Mysqldump is MySQL's own export data tool

The MySQL export database is running under the--cmd--environment.
Root:mysql Database user Name
-P: followed by MySQL database user name password
Mydatabses: Database name
-H: followed by the remote database address
Add-D to export table structure does not add to export data
MySQL only exports all data structures of the database:
To export a local database table structure:
mysqldump-uroot-p123456-d mydatabases > Mysql.sql
Export the table structure of a remote machine (or server):
mysqldump-uroot-p123456-h192.168.0.1-d mysqldatabases > Mysql.sql
Export all table structures for a local database:
mysqldump-uroot-p12345 mydatabases > Mysql.sql
Export all table structures and data for a database of a remote machine (or server):
mysqldump-uroot-p123456-h192.168.0.1 mysqldatabase> Mysql.sql
Import data: Source E:\mysql.sql
The mysqldump command has a--where/-w parameter that sets the criteria for data export, which is basically the same as where in the SQL query command, and with it, we can export the part of the data you need from the database.
The command format is as follows:
Mysqldump-u User name-p password database name table name--where= "filter Condition" > Export file path
Example:

Export sensorid=11 and fieldid=0 data from the Sdata table in the Meteo database to/home/xyx/temp.sql this file
mysqldump-uroot-p123456 Meteo sdata--where= "sensorid=11 and fieldid=0" >/home/xyx/temp.sql

MySQL Basic instructions