MySQL Common commands

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.

1, Connection MySQL format: mysql-h host address-u user name-P user Password

1. Connect to MySQL on this machine.
First open the DOS window, then enter the directory Mysql\bin, and then type the command Mysql-u root-p, enter after the prompt you to lose the password. Note that the user name can have a space or no space, but before the password must have no space, or let you re-enter the password.

If you have just installed MySQL, superuser root is no password, so the direct return to enter the MySQL, MySQL prompt is: mysql>

2. Connect to MySQL on the remote host. Assume the remote host IP is: 110.110.110.110, the user name is root, the password is abcd123. Type the following command:
Mysql-h110.110.110.110-u Root-p 123; (Note: You can not add a space between the root and the other)

3. Quit MySQL command: Exit (Enter) 2, change password format: mysqladmin-u user Name----old password password new password

1, add a password to root ab12.
First enter directory Mysql\bin under DOS, and then type the following command
Mysqladmin-u Root-password AB12
Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, then change the root password to djg345.
Mysqladmin-u root-p ab12 Password djg3453, add new users Note: Unlike the above, the following is because it is a command in the MySQL environment, so it is followed by a semicolon as a command terminator

Format: Grant Select on database. * To User name @ login host identified by "password"

1, add a user test1 password for ABC, so that he can log on any host, and all databases have query, insert, modify, delete permissions. First connect to MySQL with the root user, and then type the following command:
Grant Select,insert,update,delete on * * to [[email protected] '%][email protected] '%[/email] ' identified by ' ABC ';

But the added user is very dangerous, you want to like someone to know test1 password, then he can be on any computer on the Internet to log into your MySQL database and your data can do whatever you like, solution see 2.

2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can query, insert, modify, delete operations (localhost refers to the local host, that is, the MySQL database host), so that the user knows Test2 's password, and he cannot access the database directly from the Internet, but only through a Web page on the MySQL host.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by "ABC";

If you do not want to test2 have a password, you can call another command to erase the password.
Grant Select,insert,update,delete on mydb.* to [[E-mail Protected]][email protected][/email] identified by ""; 4.1 Creating a Database Note: Connect to the MySQL server before creating the database

Command: Create databases < database name >

Example 1: Establishing a database named Xhkdb
mysql> CREATE DATABASE xhkdb;

Example 2: Creating a database and assigning users

①create database name;

②grant select,insert,update,delete,create,drop,alter on database name. * To database name @localhost identified by ' password ';

③set PASSWORD for ' database name ' @ ' localhost ' = old_password (' password ');

Execute 3 commands in turn to complete the database creation. Note: The Chinese "password" and "database" are the user's own needs to set. 4.2 Display Database command: Show databases (note: There is a last s)
mysql> show databases;

Note: To modify the default encoding of the database in order to not display the garbled characters. The following is an example of the GBK encoding page:

1. Modify the MySQL configuration file:My.ini inside Modify DEFAULT-CHARACTER-SET=GBK
2. Code Runtime Modification:
①java Code: JDBC:MYSQL://LOCALHOST:3306/TEST?USEUNICODE=TRUE&AMP;CHARACTERENCODING=GBK
②php Code: Header ("content-type:text/html;charset=gb2312");
③c language code: int mysql_set_character_set (mysql * mysql, char * csname);
This function is used to set the default character set for the current connection. The string csname specifies 1 valid character set names. Connection proofing becomes the default proofing for character sets. This function works similarly to the set names statement, but it also sets the value of mysql-> CharSet, which affects the character sets set by the Mysql_real_escape_string (). 4.3 Delete database command: Drop database < DB name >
Example: Delete a database named Xhkdb
mysql> drop Database xhkdb;

Example 1: Delete a database that has been determined to exist
mysql> drop Database drop_database;
Query OK, 0 rows Affected (0.00 sec)

Example 2: Deleting a database with an indeterminate existence
mysql> drop Database drop_database;
ERROR 1008 (HY000): Can ' t drop database ' drop_database '; Database doesn ' t exist
An error occurred and the ' drop_database ' database could not be deleted, and the database does not exist.
Mysql> drop database if exists drop_database;
Query OK, 0 rows affected, 1 Warning (0.00 sec)//generates a warning stating that this database does not exist
mysql> CREATE DATABASE Drop_database;
Query OK, 1 row Affected (0.00 sec)
Mysql> drop database if exists drop_database;//if exists to determine if the databases exist, are not present, and do not produce errors
Query OK, 0 rows Affected (0.00 sec) 4.4 Connection Database command: Use < database name >

For example: If the XHKDB database exists, try to access it:
mysql> use XHKDB;
Screen tip: Database changed

The Use statement can advertise that MySQL uses the Db_name database as the default (current) database for subsequent statements. The database remains as the default database until the end of the segment, or until a different use statement is published:
mysql> use DB1;
Mysql> SELECT COUNT (*) from mytable; # selects from db1.mytable
Mysql> use DB2;
Mysql> SELECT COUNT (*) from mytable; # selects from db2.mytable

using the USE statement to mark a specific current database does not prevent you from accessing tables in other databases. The following example accesses the author table from the DB1 database and accesses the edit table from the DB2 database:
mysql> use DB1;
Mysql> SELECT Author_name,editor_name from Author,db2.editor
WHERE author.editor_id = db2.editor.editor_id;

The USE statement is set up to be compatible with Sybase.

Some netizens asked, how to quit after the connection. In fact, you do not have to quit, use the database, using show databases can query all the database, if you want to jump to another database, with
Use other database names
You can do it. 4.5 The currently selected database command:mysql> select Database ();

The select command in MySQL is similar to print or write in other programming languages, and you can use it to display the results of a string, a number, a mathematical expression, and so on. How do I use the special features of the Select command in MySQL?

1. Display the MySQL version
Mysql> select version ();
+-----------------------+
| Version () |
+-----------------------+
| 6.0.4-alpha-community |
+-----------------------+
1 row in Set (0.02 sec)

2. Display the current time
Mysql> Select Now ();
+---------------------+
| Now () |
+---------------------+
| 2009-09-15 22:35:32 |
+---------------------+
1 row in Set (0.04 sec)

3. Display Date
SELECT DayOfMonth (current_date);
+--------------------------+
| DayOfMonth (current_date) |
+--------------------------+
| 15 |
+--------------------------+
1 row in Set (0.01 sec)

SELECT MONTH (current_date);
+---------------------+
| MONTH (current_date) |
+---------------------+
| 9 |
+---------------------+
1 row in Set (0.00 sec)

SELECT year (current_date);
+--------------------+
| Year (current_date) |
+--------------------+
| 2009 |
+--------------------+
1 row in Set (0.00 sec)

4. Display String
Mysql> Select "Welecome to my blog!";
+----------------------+
| Welecome to my blog! |
+----------------------+
| Welecome to my blog! |
+----------------------+
1 row in Set (0.00 sec)

5. When the calculator uses
Select ((4 * 4)/10) + 25;
+----------------------+
| ((4 * 4)/10) + 25 |
+----------------------+
| 26.60 |
+----------------------+
1 row in Set (0.00 sec)

6. Threaded string
Select CONCAT (F_name, "", L_name)
As Name
From Employee_data
where title = ' Marketing Executive ';
+---------------+
| Name |
+---------------+
| Monica Sehgal |
| Hal Simlai |
| Joseph Irvine |
+---------------+
3 Rows in Set (0.00 sec)
Note: the concat () function is used here to string strings together. In addition, we used the previously learned as to give the result column ' CONCAT (f_name, "", L_name) ' a pseudonym. 5.1 Creating a data Table command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);

For example, to create a table named MyClass,

Field name	Number Type	Data width	is empty	Whether the primary key	Automatically add	Default value
Id	Int	4	Whether	Primary key	Auto_increment
Name	Char	20	Whether
Sex	Int	4	Whether			0
Degree	Double	16	Is

Mysql> Create TableMyClass (
> ID int (4) NOT NULL PRIMARY KEY Auto_increment,
> Name char () NOT NULL,
> Sex int (4) NOT NULL default' 0 ',
> Degree double (16,2)); 5.3 Delete data Table command: DROP table < table name >

Example: Deleting a table with a table named MyClass
mysql> drop table MyClass;

DROP table is used to cancel one or more tables. You must have drop permissions for each table. All table data and table definitions are canceled, so use this statement with caution!

Note: For a partitioned table, the DROP table permanently cancels the tables definition, cancels the partitions, and cancels all the data stored in those partitions. DROP table also cancels the partition definition (. par) file that is associated with the canceled table.

For tables that do not exist, use the if exists to prevent errors from occurring. When using the if exists, a note is generated for each table that does not exist.

Restrict and cascade can make partitioning easier. Currently, restrict and cascade do not work. 5.4 Table Insert 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, these two records indicate that: 1 is named Tom with a score of 96.45, 2 for the named Joan, and 82.99 for the number 3.
mysql> INSERT INTO MyClass values (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

Note: INSERT into only one record can be inserted into the table at a time. 5.5 Querying the data in a table 1), query all rows
Command: Select < Field 1, field 2,...> from < table name > where < expression >
Example: View all data in table MyClass
Mysql> select * from MyClass;

2), query 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;

Select is typically used with where to query for more accurate and complex data. 5.6 Delete Table Data command: Delete from table name where expression

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

Here is a comparison of the table before and after deleting the data.
Age

FirstName	LastName
Peter	Griffin	35
Glenn	Quagmire	33

The following is an example of PHP code to delete all Lastname= ' Griffin ' records in the "Persons" table:

<?php    $con = mysql_connect ("localhost", "Peter", "abc123");    if (! $con)    {die      (' Could not connect: '. Mysql_error ());    }    mysql_select_db ("my_db", $con);    mysql_query ("DELETE from Persons WHERE lastname= ' Griffin '"); Mysql_close ($con);?>

After this deletion, the table is like this:
Age

FirstName	LastName
Glenn	Quagmire	33

5.7 Modifying data syntax in tables: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;

Example 1: MySQL UPDATE statement for single table:
UPDATE [low_priority] [IGNORE] tbl_name SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition] [ORDER by ...] [LIMIT Row_count]

Example 2: Multi-table UPDATE statement:
UPDATE [low_priority] [IGNORE] table_references SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE Where_definition]

The update syntax can update the columns in the original table row with the new values. The SET clause indicates which columns to modify and which values to give. The WHERE clause specifies which rows should be updated. If there is no WHERE clause, all rows are updated. If an ORDER BY clause is specified, the row is updated in the order specified. The limit clause is used to limit the number of rows that can be updated, given a limit value. 5.8 Add Field command: ALTER TABLETable name Addfield type Other;
For example: Added a field passtest in table MyClass, type int (4), default value of 0
Mysql> ALTER TABLEMyClass AddPasstest Int (4 ) Default' 0 '

Gazzo Primer
Mysql> ALTER TABLE name add index index name (field name 1[, field Name 2 ...]);
Example: mysql> ALTER TABLE employee ADD index emp_name (name);

index of the plus primary keyword
Mysql> ALTER TABLE name add primary key (field name);
Example: mysql> ALTER TABLE employee ADD primary key (ID);

index with unique restriction criteria
Mysql> ALTER TABLE name add unique index name (field name);
Example: mysql> ALTER TABLE employee add unique emp_name2 (cardnumber);

Delete an index
mysql> ALTER TABLE name DROP INDEX name;
Example: Mysql>alter table employee DROP index emp_name;

Add Field:
mysql> ALTER TABLE table_name ADD field_name Field_type;

Modify the original field name and type:
mysql> ALTER TABLE table_name change old_field_name new_field_name field_type;

To delete a field:
MySQL ALTER TABLE table_name DROP field_name;5.9 Modify 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;

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table.

If MySQL encounters any errors in the multi-table renaming, it reverses all renamed tables, returning everything to its original state.

RENAME TABLE was added to MySQL 3.23.23. 6. Backup DATABASE command executes in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] Directory

1. Export the entire database
The export file is present in the Mysql\bin directory by default
Mysqldump-u user name-p database name > exported file name
Mysqldump-u user_name-p123456 database_name > Outfile_name.sql

2. Export a table
Mysqldump-u user name-P database name Table name > exported file name
MYSQLDUMP-U USER_NAME-P database_name table_name > OUTFILE_NAME.SQL

3. Export a database structure
Mysqldump-u user_name-p-d–add-drop-table database_name > Outfile_name.sql
-D No data –add-drop-table add a drop table before each CREATE statement

4. Export with language parameters
mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-opt database_name > Outfile_name.sql

For example, back up the AAA library to file Back_aaa:
[Email protected] root]# Cd/home/data/mysql
[[email protected] mysql]# mysqldump-u root-p--opt AAA > back_aaa7.1 An example of building a library and building a table 1drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (", ' Allen ', ' Dalian One ', ' 1976-10-10′ ');
Insert into teacher values (", ' Jack ', ' Dalian II ', ' 1975-12-23′ ');

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug.
1, you can write the above command as is written in a text file, assuming that school.sql, and then copied to c:\\, and in the DOS state into the directory [Url=file://\\mysql\\bin]\\mysql\\bin[/url], Then type the following command:
Mysql-uroot-p Password < C:\\school.sql
If successful, empty a row without any display, and if there is an error, there is a hint. (The above command has been debugged, you can use it only if you remove//comment).

2, or enter the command line after using mysql> source C:\\school.sql; You can also import the School.sql file into the database.

7.2 An instance of building and building a table 2drop database if exists school; Delete if school is present
Create Database School; Building a library School
Use school; Open Library School
CREATE TABLE teacher//Create tables Teacher
(
ID int (3) auto_increment NOT null primary key,
Name Char (TEN) is not NULL,
Address varchar (+) Default ' Shenzhen ',
Year Date
); End of Build table

The following is the Insert field
Insert into teacher values (' ' ', ' Glchengang ', ' Shenzhen One ', ' 1976-10-10 ');
Insert into teacher values (' ' ', ' Jack ', ' Shenzhen One ', ' 1975-12-23 ');

Note: In the table under construction
1. Set the ID to a numeric field of length 3: Int (3), and let it automatically add one to each record: auto_increment, and cannot be empty: NOT NULL, and let him be the main field primary key.

2. Set name to a character field of length 10

3. Set address as the character field of length 50, and the default value is Shenzhen.

4. Set year as the Date field.

MySQL Common commands