MySQL Import SQL script Chinese garbled settings and common commands

1. Use database_name;
2. Set names UTF8; (or other required encoding)
3. Source example.sql (SQL file storage path)

MySQL installation directory
Database directory
/var/lib/mysql/
Configuration file
/usr/share/mysql (mysql.server command and configuration file)
Related commands
/usr/bin (Mysqladmin mysqldump and other commands)
Startup scripts
/etc/init.d/mysql (startup script file for MySQL directory)

System Management
Connect to MySQL
Format: mysql-h host address-u user name-P user Password
Example 1: Connect to MySQL on this computer.
[Email protected]:~$ mysql-uroot-pmysql;

Example 2: Connect to MYSQL on a remote host.
[Email protected]:~$ mysql-h 127.0.0.1-uroot-pmysql;

Modify New Password
In Terminal input: Mysql-u user name-p password, enter MySQL.
> Use MySQL;
> Update user Set Password=password (' New password ') where user= ' username ';
> Flush Privileges; #更新权限
> quit; #退出

Add new users
Format: Grant Select on database. * To User name @ Login host identified by ' password '
Example:
Example 1: Add a user test1 password for ABC so that he can log on on any host and have all databases
Permissions to query, insert, modify, delete. First, use the root user to connect to MySQL, and then type the following command:
Mysql>grant select,insert,update,delete On * * to [e-mail protected] identified by ' MySQL ';
Or
Grant all privileges on * * to [email protected] identified by ' MySQL ';
Then refresh the permission settings.
Flush privileges;

Example 2: If you do not want root to have the password operation database "MyDB" in the data table, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by ';

Delete User
[Email protected]:~$ mysql-u user name-p password
Mysql>delete from user where user= ' user name ' and host= ' localhost ';
Mysql>flush privileges;
Delete a user's database
Mysql>drop database dbname;

Database Operations
Show all databases
mysql> show databases; (note: There is a last s)

Create a database
mysql> CREATE DATABASE test;

Connecting to a database
mysql> use test;

View the database currently in use
Mysql> Select Database ();

Table information contained in the current database
Mysql> Show tables; (Note: There is a last s)

Deleting a database
mysql> drop database test;

Table Operations
Note: You should connect to a database by using use < database name > before the operation.
Build table
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);
Example:
Mysql> CREATE TABLE MyClass (
> ID int (4) NOT null primary key auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT null default ' 0 ',
> Degree double (16,2));

Get table structure
Command: DESC table name, or Show columns from table name
Example:
Mysql> describe MyClass
mysql> desc MyClass;
Mysql> show columns from MyClass;

Delete a table
Command: DROP table < table name >
Example: Deleting a table with a table named MyClass
mysql> drop table MyClass;

Inserting data
Command: INSERT into < table name > [(< Field name 1>[,.. < field name n >])] VALUES (value 1) [, (value N)]
Example:
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

Querying data in a table
Querying all Rows
Mysql> select * from MyClass;

Querying the first few rows of data
Example: Viewing the first 2 rows of data in a table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
Or
Mysql> select * from MyClass limit 0, 2;

Delete data from a table
Command: Delete from table name where expression
Example: Deleting a record with number 1 in table MyClass
Mysql> Delete from MyClass where id=1;

modifying data in a table
Command: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;

Add fields to a table
Command: ALTER TABLE name add field type other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '

Change table name
Command: Rename table name to new table name;
For example, change the name of the table MyClass to Youclass
Mysql> Rename table MyClass to Youclass;

Update Field Contents
Command: Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');
For example: Add 4 spaces in front of the article
Update article Set Content=concat (", content);

database Import and Export
To export a database file from a database
Using the "mysqldump" command
First, go to the DOS interface and do the following.
1) Export all databases
Format: mysqldump-u [database user name]-p-a>[save path to backup file]

2) Export data and structure
Format: mysqldump-u [Database user name]-p [database name to be backed up]>[backup file save path]
Example:
Example 1: Export the database mydb to a e:\MySQL\mydb.sql file.
Open start, run, input "cmd" and enter command line mode.
c:\> mysqldump-h localhost-u root-p mydb >e:\mysql\mydb.sql
Then enter the password, wait for an export to succeed, you can check the target file for success.

Example 2: Export mytable from Database mydb to the E:\MySQL\mytable.sql file.
c:\> mysqldump-h localhost-u root-p mydb mytable>e:\mysql\mytable.sql

Example 3: Export the structure of the database mydb to a e:\MySQL\mydb_stru.sql file.
c:\> mysqldump-h localhost-u root-p mydb--add-drop-table >e:\mysql\mydb_stru.sql
Note:-h localhost can be omitted, it is generally used on the virtual host.

3) Export data structure only
Format:
mysqldump-u [Database user name]-p-t [the name of the database to be backed up]>[the save path to the backup file]

4) Export the events in the database
Format: mysqldump-u [database user name]-p-e [Database user name]>[save path to backup file]

5) exporting stored procedures and functions in the database
Format: mysqldump-u [database user name]-p-r [Database user name]>[save path to backup file]

Import the database from an external file
1) Use the "source" command
First go to the MySQL command console, then create the database, and then use the database. Finally, perform the following actions.
Mysql>source [Save path of backup file]

2) Use the "<" symbol
First go to the "MySQL" command console, then create the database, then exit MySQL and enter the DOS interface. Finally, perform the following actions.
Mysql-u Root–p < [save path of backup file]

1: Use the show statement to find out what database currently exists on the server:
Mysql> SHOW DATABASES;
2:2. Create a database Mysqldata
mysql> CREATE DATABASE Mysqldata;
3: Select the database you created
mysql> use Mysqldata; (press ENTER to appear database changed the operation is successful!) )
4: See what tables exist in the current database
Mysql> SHOW TABLES;
5: Create a database table
Mysql> CREATE TABLE MYTABLE (name VARCHAR), sex CHAR (1));
6: Show the structure of the table:
Mysql> DESCRIBE MYTABLE;
7: Add a record to the table
mysql> INSERT INTO MYTABLE values ("HyQ", "M");
8: Loading data into a database table in text mode (for example, D:/mysql.txt)
mysql> LOAD DATA LOCAL INFILE "D:/mysql.txt" into TABLE MYTABLE;
9: Import. sql File command (for example, D:/mysql.sql)
Mysql>use database;
Mysql>source D:/mysql.sql;
10: Delete Table
Mysql>drop TABLE MYTABLE;
11: Clear the table
Mysql>delete from MYTABLE;
12: Update data in table
Mysql>update MYTABLE set sex= "F" where name= ' HyQ ';

Here are some of the management tips that you can inadvertently see on the web using MySQL,
In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started. While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.
The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, the anonymous account should be deleted, the root account password, the following commands can be used:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to restrict the logon terminal used by users, you can update the user's host field in the user table, and you should restart the database service when you make the above changes, and you will be able to log in with a command like this:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is the name of the database to log in to.
In the development and the actual application, the user should not only use the root user to connect the database, although uses the root user to carry on the test to be convenient, but will bring the system the significant security hidden danger, also is not advantageous to the management technology enhancement. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete the data. The user management of MySQL is implemented through the users table, there are two common methods for adding new users, one 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. The common usage of grant is as follows:
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";
Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";
To give this user the ability to manage the permissions on the object, you can add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption to prevent the malicious person from stealing the password. For those who have not used the user should be given clearance, the permission of the user should be in a timely manner to reclaim permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Administrative permissions:
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.

MySQL Import SQL script Chinese garbled settings and common commands