A complete Collection of Command Operations commonly used in MySQL
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!
