MYSQL Common commands

The following is a summary of some common commands used in MySQL.

1. Start and exit

1. Go to MySQL: Start MySQL Command Line Client (MySQL DOS Interface) and enter the password for installation. The prompt is: mysql>

2. exit MySQL: quit or exit

Ii. Database Operations

1. Create a database
Command: create database <database Name>
For example, create a database named phpernote.
Mysql> create database phpernote;

2. display all databases
Command: show databases (Note: The last s is available)
Mysql> show databases;

3. delete a database
Command: drop database <database Name>
For example, delete a database named phpernote.
Mysql> drop database phpernote;

4. Connect to the database
Command: use <Database Name>
For example, if the phpernote database exists, try to access it:
Mysql> use phpernote;
On-screen prompt: Database changed

5. view the currently used database
Mysql> select database ();

6. Table information contained in the current database:
Mysql> show tables; (Note: There is a last s)

7. Export the entire database
Mysqldump-u username-p -- default-character-set = latin1 Database Name> exported file name (the default database encoding is latin1)
Mysqldump-u wcnc-p smgp_rj_wcnc> wcnc. SQL

8. Export a database structure
Mysqldump-u wcnc-p-d-add-drop-table smgp_apps_wcnc> d: wcnc_db. SQL
-D no data-add-drop-table add a drop table before each create statement

9. Import Database
A: common source commands
Go to the mysql Database Console,
For example, mysql-u root-p
Mysql> use Database
Then run the source command. The following parameter is the script file (for example,. SQL used here)
Mysql> source phpernote. SQL
B: Use the mysqldump command.
Mysqldump-u username-p dbname <filename. SQL
C: Use the mysql Command
Mysql-u username-p-D dbname <filename. SQL

10. mysql database authorization
Mysql> grant select, insert, delete, create, drop on *. * (for example, test. */user. */) to username @ localhost identified by 'Password ';
 
For example, to create a user account to access the database, perform the following operations:
Mysql> grant usage ON test. * TO phpernote @ localhost;
In this way, a new user named phpernote is created. This user can only connect to the database from localhost and connect to the test database. Next, we must specify the operations that phpernote users can perform:
Mysql> GRANT select, insert, delete, update ON test. * TO phpernote @ localhost;
This operation enables phpernote to execute SELECT, INSERT, DELETE, and UPDATE operations on tables in the test database.

Iii. Table operations. A database should be connected before operations

1. Create a table
Command: create table <table Name> (<field name 1> <type 1> [,... <field name n> <type n>]);
Mysql> create table MyClass (id int (4) not null primary key auto_increment, name char (20) not null, sex int (4) not null default '0 ', degree double (16, 2 ));

2. Get the table structure
Command: desc table name or show columns from Table Name
Mysql> DESCRIBE phpernote
Mysql> desc phpernote;
Mysql> show columns from phpernote;

3. delete a table
Command: drop table <table Name>
For example, delete a table named phpernote.
Mysql> drop table phpernote;

4. insert data
Command: insert into <Table Name> [(<field name 1> [,... <field name n>])] values (value 1) [, (value n)]
For example, insert two records into the MyClass table. The two records indicate that the result of Tom numbered 1 is 96.45, and the result of Joan numbered 2 is 82.99, wang, numbered 3, scored 96.5.
Mysql> insert into phpernote values (1, 'baidu', 96.45), (2, 'Google ', 82.99), (3, 'taobao', 96.59 );

5. query the data in the table
1) query all rows
Command: select <Field 1, Field 2,...> from <Table Name> where <expression>
For example, view all data in the phpernote table.
Mysql> select * from phpernote;
2) query the first few rows of data
For example, view the first two rows of data in the phpernote table.
Mysql> select * from phpernote order by id limit 0, 2;
Or:
Mysql> select * from phpernote limit 0, 2;

6. Delete table data
Command: delete from table name where expression
For example, delete the record numbered 1 in the phpernote table.
Mysql> delete from phpernote where id = 1;

7. Modify Table data: update table name set field = new value ,... Where condition
Mysql> update phpernote set name = 'Mary 'where id = 1;

8. Add fields to the table:
Command: alter table name, add, other field types;
For example, a passtest field is added to the phpernote table. The type is int (4) and the default value is 0.
Mysql> alter table phpernote add passtest int (4) default '0 ';
Alter table phpernote add column userid int (11) not null primary key auto_increment;

9. Change the table name:
Command: rename table original table name to new table name;
For example, in the phpernote table, change the name to baidu.
Mysql> rename table phpernote to baidu;

10. Update the field content
Update table name set field name = new content
Update table name set field name = replace (field name, 'old content', 'new content ');

Add four spaces before the article
Update article set content = concat ('', content );

Field Type
1. INT [(M)] type: normal Integer type
2. DOUBLE [(M, D)] [ZEROFILL] type: normal size (DOUBLE Precision) floating point number type
3. DATE type: the supported range is 1000-01-01 to 9999-12-31. MySQL displays DATE values in YYYY-MM-DD format, but allows you to assign values to the DATE column using strings or numbers
4. CHAR (M) type: fixed-length string type. When stored, it always fills the Right to the specified length with spaces
5. blob text type, with a maximum length of 65535 (2 ^ 16-1) characters.
6. VARCHAR: variable-length string type

1: Use the SHOW statement to find out the current database on the server:
Mysql> show databases;

2. Create a database named MYSQLDATA
Mysql> Create database mysqldata;

3: select the database you created
Mysql> use mysqldata; (when you press the Enter key to see Database changed, the operation is successful !)

4: view the tables in the current database
Mysql> show tables;

5. Create a database table
Mysql> Create TABLE phpernote (name VARCHAR (20), sex CHAR (1 ));

6: display the table structure:
Mysql> DESCRIBE phpernote; // desc phpernote;

7. Add records to the table
Mysql> insert into phpernote values ("hyq", "M ");

8: load data into database tables in text mode (for example, D:/mysql.txt)
Mysql> load data local infile "D:/mysql.txt" into table phpernote;

9: import the. SQL FILE command (for example, D:/mysql. SQL)
Mysql> use database;
Mysql> source d:/mysql. SQL;

10: delete a table
Mysql> drop TABLE phpernote;

11: Clear the table
Mysql> delete from phpernote;

12: Update table data
Mysql> update phpernote set sex = "f" where name = 'hyq ';

Articles you may be interested in

• Graphic tutorial on common basic commands in Unix/Linux
• Common Windows running commands
• Common search engine commands and common search engine syntax
• Collect typical and practical doscommands
• Common JS function collection (remove spaces, verify email addresses, dates, positive integers, etc)
• Use SecureCRT to upload and download files (using sz and rz commands)
• How to run php files in the command line in windows
• Very practical php pop-up error warning function