MySQL Common command Usage Summary script home finishing version _mysql

first, start and exit
1, into MySQL: Start the MySQL Command line Client (MySQL's DOS interface), directly enter the installation of the password can be. The prompt at this point is:mysql>
2, Exit Mysql:quit or exit
Second, the library operation
1. Create a database
Command: CREATE DATABASE < database name >
For example: Create a database named XHKDB
mysql> CREATE DATABASE xhkdb;
2, display all the database
Command: Show databases (note: Last has an S)
mysql> show databases;
3, delete the database
Command: Drop Database < database name >
For example, to delete a database named Xhkdb
mysql> drop Database xhkdb;
4, connect the database
Command: Use < database name >
For example: If the XHKDB database exists, try to access it:
mysql> use XHKDB;
ScreenTip: Database changed
5, the currently selected (connected) database
Mysql> Select Database ();
6, the current database contains table information:
Mysql> Show tables; (Note: Last has an S)

Third, table operation, the operation should be connected to a database
1, build the table
Command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>];

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));
2. Get the table structure
Command: DESC table name, or Show columns from table name

mysql> desc MyClass;
Mysql> show columns from MyClass;
3, delete the table
Command: DROP table < table name >
For example, to delete a table named MyClass
mysql> drop table MyClass;
4. Inserting data
Command: INSERT into < table name > [< field name 1>[,.. < field name n >]] VALUES (value 1) [, (value N)]
For example, insert two records into table MyClass, two records that say: Tom with a number 1 is 96.45, and the number 2 for Joan is 82.99, and the number 3 for Wang is 96.5.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);
5, the data in the query table
1), Query all lines
Command: Select < Field 1, field 2,...> from < table name > where < expression >
For example: View all data in table MyClass
Mysql> select * from MyClass;
2), query the first few lines of data
For example: View the first 2 rows of data in table MyClass
Mysql> SELECT * from MyClass ORDER by ID limit 0, 2;
6, delete the data in the table
Command: Delete from table name where expression
For example: Delete a record in table MyClass that is numbered 1
Mysql> Delete from MyClass where id=1;
7, modify the data in the table: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;
7. Add fields to the table:
Command: ALTER TABLE name add field type other;
For example, a field passtest is added to the table MyClass, the type is int (4), and the default value is 0
Mysql> ALTER TABLE MyClass add passtest int (4) default ' 0 '
8, change the table name:
Command: Rename table original table name to new name;
For example: In the table MyClass the name is changed to Youclass
Mysql> Rename table MyClass to Youclass;


Update Field Contents
Update table name set field name = new Content
Update table name set field name = Replace (field name, ' old content ', ' new content ');

Add 4 spaces before the article
Update article Set Content=concat (", content);

field Type
1. int[(M)] Type: normal size integer type
2. double[(m,d)] [Zerofill] Type: normal size (double precision) floating-point number type
3. Date Date Type: The scope of support is from 1000-01-01 to 9999-12-31. MySQL displays date values in YYYY-MM-DD format, but allows you to assign values to date columns using strings or numbers
4. CHAR (M) Type: fixed-length string type, when stored, always fill the right to the specified length with a space
5. BLOB text type with a maximum length of 65535 (2^16-1) characters.
6. VARCHAR Type: variable-length string type
use of the mysqldump command

Backing up and exporting databases
Mysqldump-h database_ip-u username-p--opt databasename > Backup-file.sql
Export database table structure only
Mysqldump-h database_ip-d-u username-p databasename >database_structure.sql
Export only one table in the database
Mysqldump--opt--add-drop-table-u username-p databasename tablename > Dump.sql
Use the--password parameter if you do not want to manually enter your password
Mysqldump-h database_ip-u Username--password=123456--opt databasename > Backup-file.sql
Mysqldump-h database_ip-d-u Username--password=123456 databasename >database_structure.sql


MySQL commands use
Save query results to a file
Select title from book into outfile '/tmp/outfile.txt ';
Find redundant records in a table, and duplicate records are judged by a field (Peopleid)
SELECT * from people where Peopleid into (select Peopleid from People GROUP by
Peopleid having count (Peopleid) > 1);
Do not duplicate records in a query table (excluding duplicate records)
SELECT * from phome_ecms_wma where title is (select DISTINCT title from phome_ecms_wma);
Deletes a duplicate record in a table, which is judged by a field (title)
Select *,count (distinct title) into outfile '/tmp/table.bak ' to phome_ecms_wma Group by title;
Delete from phome_ecms_wma;
LOAD DATA INFILE '/tmp/table.bak ' REPLACE into table phome_ecms_wma character set UTF8;
Query Database Current encoding
Mysql> Show variables like "character_set%";
Modify a table field type
Mysql> ALTER TABLE table_name change last_action last_action datetime not NULL default ' 0000-00-00 00:00:00 ';
Add a new field to a table
mysql> ALTER TABLE host ADD Ks_mac VARCHAR (100);
Remove a field from a table
mysql> ALTER TABLE table_name DROP field_name;
Renaming tables
Mysql>alter table T1 rename T2;
To index a field
Mysql> ALTER TABLE tablename ADD index index name (field name 1[, field Name 2 ...]);
Mysql> ALTER TABLE tablename Add index Emp_name (name);
Index of the Primary keyword
Mysql> ALTER TABLE TableName ADD PRIMARY key (ID);
Index with unique restriction criteria
Mysql> ALTER TABLE tablename add unique emp_name2 (cardnumber);
Delete an index
Mysql>alter table tablename DROP index emp_name;
Remote access MySQL settings
Mysql> GRANT all privileges in database_test.* to root@192.168.1.9 identified by ' 123456 ';
mysql> FLUSH privileges;

1. Use the show statement to find out what database is currently on the server
mysql> show databases;
2. Create a database Mysqldata
mysql> CREATE DATABASE MyData;
3, select the database you created
mysql> use MyData;
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 the record to the table
mysql> INSERT INTO mytable values ("Test", "M");
8. Loading data into a database table (e.g. d:\mysql.txt) in text mode
mysql> Load Data local infile "d:/mysql.txt" into table mytable;
9. Import. sql file commands (e.g. D:\mysql.sql)
Mysql>use database;
Mysql>source D:/mysql.sql;
10, delete the table
Mysql>drop table mytable;
11, Empty the table
Mysql>delete from MyTable;
12, update the data in the table
Mysql>update mytable set sex= "F" where name=test;
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, where the anonymous account deletion, the root account set the password, you can use the following command:
=======================================
mysql> use MySQL;
Mysql> Delete from User where user= "";
mysql> Update User set Password=password (newpassword) where user=root;
=======================================
If you want to limit the logon terminals used by the user, you can update the host field for the corresponding user in the users table, restart the database service after making the above changes, and at this point you may be able to log on to the following similar commands:
=======================================
Shell> mysql-uroot-p;
Shell> Mysql-uroot-pnewpassword;
shell> MySQL mydb-uroot-p;
shell> MySQL Mydb-uroot-pnewpassword;
=======================================
The command parameters above are part of the common parameters, and the details refer to the documentation. The mydb here is the name of the database to log on to.
In the development and practical applications, users should not only use root to connect the database, although the use of root users to test is very convenient, but it will bring significant security risks to the system, but also not conducive to the improvement of management technology. 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 data. MySQL user management is implemented through the user table, there are two ways to add new users, one is to insert the corresponding data rows in the user table, set the appropriate permissions, and the second is to create a user with some kind of permission through the grant command. One of the common uses of grant is as follows:
================================================================
Mysql> Grant all on mydb.* to newusername@hostname identified by "password";
Mysql> grant usage on *.* to newusername@hostname identified by "password";
Mysql> Grant Select,insert,update on mydb.* to newusername@hostname identified by "password";
Mysql> Grant Update,delete on MyDB. TestTable to Newusername@hostname identified by "password";
================================================================
To give this user the ability to manage his or her permissions on the object, 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, in case the malicious person steals the password. For those who have not used the user should be given clearance, permissions of the user should be timely recall permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation. The following are explanations of common permissions:
=============================================
Global Administrative permissions
File: Read and write files on the MySQL server.
PROCESS: Displays or kills a service thread belonging to another user.
RELOAD: Overload access Control table, refresh log, etc.
SHUTDOWN: Turn off MySQL service.
Database/datasheet/data column permissions
Alter: Modify existing data tables (for example, add/Remove Columns) and indexes.
Create: Create a new database or datasheet.
Delete: Deletes a table record.
Drop: Deletes a datasheet or database.
Index: Create or delete indexes.
INSERT: Adds a record of the table.
SELECT: Displays/searches the records of the table.
UPDATE: Modifies records that already exist in the table.
Special permissions
All: Allow to do anything (like root).
USAGE: Only allow login-nothing else allowed.
=============================================
MySQL common Operation Basic operation, the following are MySQL5.0 under the test by first note, remember at the end of each command plus; (semicolon)
1. Export the entire database
Mysqldump-u user name-p--default-character-set=latin1 Database name > exported file name (database default encoding is latin1)
Mysqldump-u wcnc-p SMGP_APPS_WCNC > Wcnc.sql
2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
Mysqldump-u wcnc-p SMGP_APPS_WCNC users> wcnc_users.sql
3. Export a database structure
Mysqldump-u wcnc-p-d–add-drop-table SMGP_APPS_WCNC >d:wcnc_db.sql
-D No data –add-drop-table add a drop table before each CREATE statement
4. Import Database
Common source Commands
Enter the MySQL database console,
such as Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (such as the. SQL used here)
Mysql>source D:wcnc_db.sql