Command Line Operations Database advanced

Operation of the library:

View the databases that users can use: show databases;

Creating a database: Create databases School; Building a library School

Open database: Use database_name;

Import SQL file: source sql_file.sql;

Callout: 1, first open a database, with use database_name;

2, then use the source command;

Export database: It is done before entering a database.

This was done in the Cmd.exe into MySQL.

D:\phpstudy\mysql\bin>mysqldump-u root-p Test > Test.sql
Enter Password: * * *

Operation of the table:

Create a table:

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

Reference:

Next I am MySQL on the database aspects of the operation. Note: You must first log in to MySQL, the following actions are performed at the prompt of MySQL, and each command ends with a semicolon.

First, 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. That is

Say you can put a complete command into a few lines to hit, after the end with a semicolon to finish the sign is OK.

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

Second, show the command

1. Displays the list of databases in the current database server: mysql> show DATABASES; Note: MySQL library has a MySQL system

Information, we change the password and add the user, actually is to use this library to operate.

2. Display the data table in the database: mysql> use library name; mysql> show TABLES;

3, display the structure of the data table: mysql> DESCRIBE table name;

4, establish the database: mysql> Create library name;

5, set up the data table: mysql> use library name; mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));

6. Delete database: mysql> drop DB name;

7. Delete Data sheet: mysql> drop table name;

8. Empty the records in the table: mysql> DELETE from table name;

9. Display the records in the table: mysql> SELECT * from table name;

10. Insert a record into the table: mysql> insert INTO table name VALUES ("HyQ", "M");

11. Update table data: mysql-> Update table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';

12. Load data into the data table in text mode: mysql> LOAD data LOCAL INFILE "d:/mysql.txt" into table

Table name;

13. Import. sql file command: mysql> use database name; Mysql> SOURCE D:/mysql.sql;

14, the command line to modify the root password: mysql> UPDATE mysql.user SET password=password (' New password ') WHERE

user= ' root '; mysql> FLUSH privileges;

15. Display the database name of use: mysql> SELECT databases ();

16. Display the current user:mysql> SELECT USER ();

a city 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 ' 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 it is in a text file, assuming that it is school.sql and then copied to c:\\,

and enter directory [Url=file://\\mysql\\bin]\\mysql\\bin[/url] in the DOS state, and type the following command:

Mysql-uroot-p Password < C:\\school.sql If successful, empty a row without any display;

If there is an error, there will be 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

Iv. 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 [Url=file://\\n]\\n[/url].

Cases:

3 Rose Dalian II 1976-10-10

4 Mike Dalian One 1975-12-23

Suppose you save these two sets of data as school.txt files, placed in the C packing directory.

2, the data incoming command, load data local infile "C:\\school.txt" into the table name; Note: You'd better put the file

Copy to the [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory, and first use the using command to hit the library where the table is located.

v. BACKUP DATABASE: ( command executed in DOS [Url=file://\\mysql\\bin]\\mysql\\bin[/url] directory)

1. Export the entire database export file by default is present in the Mysql\bin directory mysqldump-u user name-p database name > Export

FileName 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 parameter mysqldump-uroot-p–default-character-set=latin1–set-charset=gbk–skip-

OPT database_name > Outfile_name.sql

First, connect 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 go to directory Mysql\bin, then type the command Mysql-u root-p,

You will be prompted to lose the password after you enter. Note 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 into MySQL,

The prompt for MySQL 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. The

Type the following command: Mysql-h110.110.110.110-u root-p 123; (Note: You can not add spaces between the root and

As

3. Exit MySQL command: Exit (enter)

Second, change the password. Format: Mysqladmin-u username-P 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 djg345

third, add new users. (Note: Unlike above, the following is because it is a command in the MySQL environment, so it is followed by a semicolon as the life

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

The 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 increased user is very dangerous and you want to

If someone knows Test1 's password, then he can log into your MySQL database on any computer on the Internet and count your

As far as you can, the solution is 2.

2, add a user test2 password for ABC, so that he can only login on localhost, and the database mydb can be queried, inserted,

Modified, deleted operations (localhost refers to the local host, the host where the MySQL database resides), so that the user knows

Test2 password, he also cannot access the database directly from the Internet, only through the Web page on the MySQL host to access.

Grant Select,insert,update,delete on mydb.* to [[email 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 "";

Command Line Operations database advanced