Linux operation MySQL Database
Mysql-u root-p Wait for the password to be entered, the password is not visible. then enter the password. (Root is the user name), then go to MySQL
1. Display Database
show databases;
2. Select 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 the Library
Create DATABSE library name;
7. Build a table
CREATE TABLE table name (field settings list);
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-increment
Primary KEY Primary Key
8. Add record
INSERT into name (Uname,gender,birthday) VALUES (' Zhang San ', ' Male ', ' 1971-10-01 ');
9, change the record
Update name set birthday= ' 1971-01-10 ' where Uname= ' Zhang San ';
10. Delete Records
Delete from name where Uname= ' Zhang San ';
11. Delete a table
DROP table Name
12. Delete Library
drop database name;
13. Backing Up the database
Mysqldump-u root-p--opt database name > backup name; Go to library Directory
14. Recovery
Mysql-u ROOT-P Database name < backup name; Database must exist at restore time, can be an empty database
15. Export the entire database
Mysqldump-u User name-p password 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 a 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 the database
Common source Commands
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 D:wcnc_db.sql
Common Database operations:
Create Database GPJ; Create 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 '% '; Divide the GPJ database to Xinhua this user
deleting databases and data tables
Mysql>drop database name;
Mysql>drop Table datasheet Name
Delete accounts and permissions:
>drop user username @ '% ';
>drop user username @ localhost;
Modify the 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 you execute the SQL script, if you do not have the database, you need to create the database before entering the database
SQL file to execute MySQL under Linux
Mysql-uroot-proot
Go to MySQL
then execute the Source/var/ftp/pub/sogoodsoft.sql;
Can.
MySQL Common command operation Daquan