CMD start, close version, show create statement db,table, set up DB set Word set, date, time, solve table garbled

MySQL Common SQL statements

MySQL Tutorial

To start the MySQL service at the DOS command line:
net start MySQL
To stop the MySQL service at the DOS command line:
net stop MySQL
To view the ports being tapped:
Netstat–na | Findstr 3306
Findstr is used to find if the following port is present.



To create a database user: only the root user (root) has permission to create a new user

CREATE USER user_name1 identified by ' password ',
User_name2 identified by ' password ';
You can create multiple database users at once
To delete a database user:
DROP USER user_name;
Select User:
Select User ();
User's rights control:
Grant Library, table-level permission control: assigning control of a table in a library to a user
GRANT all on Db_name.table_name to uer_name[indentified by ' password '];
To view all character encodings:
Show CHARACTER SET;
==============================================================================
Login to MySQL database: Login to MySQL console at DOS command line
Mysql-uuser_name-p (Enter, enter the password, if direct input is can be explicit)
Enter password:*********
Mysql–h Hostname–u user_name–p
Enter password:*********
Example: Mysql–h 192.168.5.105–uroot–p
Enter password:*******
To view the running environment information:
Use status when entering the MySQL command-line tool or \s to view the running environment information
To create a database:
Create DATABASE db_name;
[Default] CHARACTER set charset_name//Set up the encoding method of the database
[Default]         COLLATE collation_name; Set Sort by Collation_name field
Cannot write as Utf-8,utf8 default proofing for Utf8_general_ci (view by show character set)
CREATE DATABASE db_name CHARACTER SET UTF8 COLLATE utf8_general_ci;
CHARACTER set: Specifies the character set used by the database
COLLATE: Specify how database character sets are compared

To display the database creation statement:
Show CREATE DATABASE db_name;
To delete a database:
DROP DATABASE db_name;
Delete can first determine whether there is, written: DROP DATABASE if EXISTS db_name;
View the instructions for creating the database and view the encoding used by the database:
Show Create Databasedb_name;
To view the database encoding:
Showvariables like ' char% ';
To view the database's current engine:
Show CREATE TABLE table_name;
To modify the database's current engine:
ALTER TABLE table_name engine=myisam| INNODB; ( | Say ' or ', choose one
The database engine you can use depends on how MySQL was compiled at the time of installation. To add a new engine, you must recompile MySQL. By default, MySQL supports three engines: ISAM, MyISAM, and heap. The other two types of InnoDB and Berkley (BDB) are also often available.
To set the database encoding:
setcharacter_set_client=gbk;//can save Chinese
setcharacter_set_results=gbk;//can see Chinese
Back up the database:
Mysqldump–u user name (root) –p password db_name> store path level/filename (file format:. sql)
(Not in the MySQL console, but to exit the console for DOS execution)
Example:
Mysqldump–u root–p******* mydb> D:/mydb.sql;
To recover a database:
Prerequisite: To create an empty database
SOURCE holds path/file name. SQL (performed in MySQL console)
How to save large amounts of data in a table in a database:
First, the data is stored in a text document by the structure of the table (corresponding to the order of the fields);
Then, if a field has no value, fill in null, and note that each field value is separated by a TAB key (/r/n).
Finally, use the command: LOAD DATA local INFILE ' e:/test/pet.txt ' into TABLE pet LINES
Terminatedby ' \ r \ n ';



Terminate end, terminate;

Inserting a database is a garbled reference solution:
1. Database character set to GB2312. (but is not successful display Datato lang bar!)
2. The key is when you create the table:
Create TABLE (field) Default character Set gb2312;
3. Table to create a good case:
Modify table encoding: ALTER TABLE name Defaultcharacter set gb2312;
Modify field Encoding: Altertable table name changecolumn field name character SET gb2312;

==============================================================================

#查看数据库的版本, current date (case-insensitive)
Mysql> selectversion (), Current_date,now ();