MySQL Common command operation Daquan

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.