MySQL Common commands

One, connect MySQL

Format: mysql-h host address-u user name-P user Password

1. Example 1: Connect to MySQL on this machine

First open the DOS window, and then enter the directory Mysqlbin, and then type the command mysql-uroot-p, enter after the prompt you to lose the password, if just installed MySQL, superuser root is no password, so directly enter into MySQL, The prompt for MySQL is:mysql>

2. Example 2: Connect to MySQL on a 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-uroot-pabcd123

(Note: You and root can be used without spaces, others are the same)

3. Exit MySQL command: Exit (enter)

Second, change the password

Format: Mysqladmin-u username-P Old password password new password

1, Example 1: Add a password to root ab12. First enter directory Mysqlbin under DOS, and then type the following command

Mysqladmin-uroot-password AB12

Note: Because Root does not have a password at the beginning, the-p old password can be omitted.

2, Example 2: Then change the root password to djg345

MYSQLADMIN-UROOT-PAB12 Password djg345

Three, the Operation skill

1, if you hit the command, enter after the discovery forgot to add a semicolon, you do not have to re-play the command, as long as a semicolon to enter the return on it. In other words, you can break a complete command into a few lines, and then use a semicolon to make the end sign OK.

2. You can use the cursor up and down keys to recall the previous command.

Iv. Display of commands

1. Display the list of databases.

show databases;

Just started with two databases: MySQL and test. MySQL Library is very important it has the MySQL system information, we change the password and the new user, is actually using this library to operate.

2. Display the data table in the library:

use MySQL;//Open the library, learn foxbase must not be unfamiliar with it

Show tables;

3, display the structure of the data table:

describe table name;

4, build the library:

Create database name;

5, build the table:

Use library name;

CREATE TABLE table name (field settings list);

6. Deleting the library and deleting the table:

drop database name;

drop table name;

7. Empty the records in the table:

Delete from table name;

8. Display the records in the table:

SELECT * from table name;

How far build and build tables and instances of inserting data

Drop 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 ' Beijing ',
Year Date
); End of Build table
The following is the Insert field
Insert into teacher values (' ', ' Glchengang ', ' Beijing Xdl ', ' 2016-6-4 ');
Insert into teacher values (' ', ' Jack ', ' Beijing Xdl ', ' 2016-6-4 ');

Note: In the Build table (1), set the ID to a number field of length 3: Int (3) and let it automatically add one to each record: Auto_increment is not null, and makes him the main field primary key
(2) Set name to a character field of length 10
(3) Set address to a character field of length 50, and the default value is Shenzhen. What is the difference between varchar and char, only to wait for a later article to say.
(4) Set year as the Date field.

It is also possible to type the above commands at the MySQL prompt, but it is not easy to debug. You can write the above command as-is to a text file, assume that it is school.sql, then copy it to C: \ and enter directory \mysql\bin in DOS, and 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).

Vi. transferring text data to the database

1, the text data should conform to the format: The field data is separated by the TAB key, the null value is replaced by \ n.
Cases:
3 Rose Beijing Xdl 2016-6-4
4 Mike Beijing Xdl 2016-6-4

2, the data incoming command, load data local infile "file name" into table table name;
Note: You might want to copy the file to the \mysql\bin directory, and use the using command to hit the library that contains the table.

VII. Import and Export

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 the database
A: Common Source command
Go to MySQL Database console,
such as: Mysql-u root-p
Mysql>use Database
Then use the source command, followed by the script file (for example, the. SQL used here)
Mysql>source Wcnc_db.sql

B: Using the mysqldump command
Mysqldump-u Username-p dbname < Filename.sql

C: Using the MySQL command
Mysql-u Username-p-D dbname < Filename.sql

MySQL Common commands