Shulou(Shulou.com)05/31 Report--

This article mainly introduces "MySQL commonly used command operation Daquan". In daily operation, I believe that many people have doubts about the command operation Daquan commonly used in MySQL. The editor consulted all kinds of materials and sorted out simple and easy-to-use operation methods. I hope it will be helpful to answer the doubts of "MySQL commonly used command operation Daquan". Next, please follow the editor to study!

Linux operates mysql database

Mysql-u root-p waits for the password to be entered. The password is not visible. Then enter the password. (root is the user name), and then enter mysql

1. Display the database

Show databases

2. Select a database

Use database name

3. Display the tables in the database

Show tables

4. Display the structure of the data table

Describe table name

5. Display the records in the table

SELECT * FROM table name

6. Build a database

Create databse library name

7. Build a table

Create table table name (list of field settings)

Mysql > create table name (

-> id int auto_increment not null primary key

-> uname char (8)

-> gender char (2)

-> birthday date)

Query OK, 0 rows affected (0.03 sec)

Mysql > show tables

+-+

| | Tables_in_userdb |

+-+

| | name |

+-+

1 row in set (0.00 sec)

Mysql > describe name

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL | auto_increment |

| | uname | char (8) | YES | | NULL |

| | gender | char (2) | YES | | NULL |

| | birthday | date | YES | | NULL |

+-+ +

4 rows in set (0.00 sec)

Note: auto_increment self-increasing

Primary key primary key

8. Add records

Insert into name (uname,gender,birthday) values ('Zhang San', 'male', '1971-10-01')

9. Modify the record

Update name set birthday='1971-01-10 'where uname=' Zhang San'

10. Delete the record

Delete from name where uname=' Zhang San'

11. Delete the table

Drop table table name

12. Delete the library

Drop database library name

13. Back up the database

Mysqldump-u root-p-- opt database name > backup name; / / go to the library directory

14. Restore

Mysql-u root-p database name exported file name

C:\ Users\ jack > mysqldump-uroot-pmysql sva_rec > e:\ sva_rec.sql

16. Export a table, including table structure and data

Mysqldump-u user name-p password database name table name > exported file name

C:\ Users\ jack > mysqldump-uroot-pmysql sva_rec date_rec_drv > e:\ date_rec_drv.sql

3. Export a database structure

C:\ Users\ jack > mysqldump-uroot-pmysql-d sva_rec > e:\ sva_rec.sql

4. Export a table with only table structure

Mysqldump-u user name-p password-d database name table name > exported file name

C:\ Users\ jack > mysqldump-uroot-pmysql-d sva_rec date_rec_drv > e:\ date_rec_drv.sql

5. Import database

Common source commands

Go to the mysql database console

Such as mysql-u root-p

Mysql > use database

Then use the source command, followed by a script file (such as .sql used here)

Mysql > source d:wcnc_db.sql

Common database operations:

Create database gpj; creates a database called gpj

CREATE USER 'xinhua'@'%' IDENTIFIED BY' 123; create a user named xinhua with a password of 123

GRANT ALL ON gpj.* TO 'xinhua'@'%'; distributes the gpj database to xinhua as a user.

Mysql refresh permission command: FLUSH PRIVILEGES; (usually used after database user information is updated)

Another way is to restart the mysql server.

Delete databases and data tables

Mysql > drop database database name

Mysql > drop table datasheet name

Delete account and permissions:

> drop user user name @'%'

> drop user user name @ localhost

Modify mysql root password

Mysql-u root

Mysql > use mysql

Mysql > UPDATE user SET Password = PASSWORD ('newpass') WHERE user =' root'

Mysql > FLUSH PRIVILEGES

Linux operation mysql script

Note that before executing the sql script, if you do not have the database, create the database before entering the database

The sql file that executes mysql under linux

Mysql-uroot-proot

Enter into mysql

Then execute source / var/ftp/pub/sogoodsoft.sql

That's it.

At this point, the study of "the complete Book of Command Operations commonly used in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.