MySQL Common Commands

1 , connect to this computer MYSQL .
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 , Exit MYSQL command : Exit (Enter)

3, establish a name for XHKDB the database
mysql> CREATE DATABASE xhkdb;

4. Display Database

Command: Show databases (note: There is a last s)
mysql> show databases;

5. Deleting a database

Command: Drop databases < database name >
Example: Delete a database named Xhkdb
mysql> drop Database xhkdb;

6. Connect to the database

Command: Use < database name >

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

7. Create 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 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));

8. Delete Data Sheet

Command: DROP table < table name >

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

9. 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 Myclassvalues (1, ' Tom ', 96.45), (2, ' Joan ', 82.99), (2, ' Wang ', 96.59);

10. Clear the data in the table

mysql> truncate table name;

11. Modify the data in the table

Syntax: Update table name SET field = new value,... WHERE condition
mysql> Update MyClass set name= ' Mary ' where id=1;

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

13. Foreign Key

为已经添加好的数据表添加外键:

语法:alter table 表名 add constraint FK_ID foreign key(你的外键字段名) REFERENCES 外表表名(对应的表的主键字段名);

例: alter table tb_active add constraint FK_ID foreign key(user_id) REFERENCES tb_user(id)

ALTER TABLE TBL_SPROT_TAB add constraint fk_id foreign key (subject_id) REFERENCES Tbl_sprot_fasta (subject_id);

Use conditions for foreign keys:
1. Two tables must be a InnoDB table, the MyISAM table temporarily does not support foreign keys (it is said that later versions may be supported, but at least not currently supported);
2. The foreign key column must be indexed, MySQL 4.1.2 later version will automatically create the index when the foreign key is established, but if the earlier version needs to display the establishment;
3. The columns of the two tables of the foreign-key relationship must be of similar data types, i.e., columns that can be converted to each other, such as int and tinyint, and int and char are not allowed;

The advantage of foreign key: can make two tables association, ensure the consistency of data and realize some cascade operation;

14. 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

15. Export a table
Mysqldump-u user name-P database name Table name > exported file name

16. View the table structure:

DESC table name;

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.

MySQL Common Commands