Common MySQL query commands

Mysql> select version (); view the mysql version
Mysql> select current_date (); view the current MySQL date
Mysql> select version (), current_date (); view the mysql version number and current date
Mysql> show databases; displays the existing database
Mysql> use MySQL select to use the database (Use and quit commands do not need to end with a semicolon)
Database changed
Mysql> select database (); displays the currently selected Database
Mysql> show tables; displays tables in the current database
Mysql> select * From dB; displays the table (db) Content
Mysql> describe mytable; displays the table structure.
Or show columns from table name;

Mysql> select
-> User ()
->/C
Mysql>

Perform the following operations:
Mysql> select
-> User ()
->,
-> Now ()
->;
Mysql> select (20 + 5) * 4;
Mysql> select (20 + 5) * 4, sin (PI ()/3 );
Mysql> select (20 + 5) * 4 as result, sin (PI ()/3); (as: Specify the alias as result)

View the number of connections currently occupied by mysql users

Command: Show processlist;
If it is a root account, you can see the current connection of all users. For other common accounts, you can only view the connections you are using.
Show processlist; only the first 100 items are listed. If you want to list them all, use show full processlist;
Mysql> show processlist;

I. Database:

Mysql> Create Database abccs; Create a database
Mysql> Use abccs select database
Mysql> drop database database name; delete database

II. Table:

1. Create a table mytable:

Mysql> Create Table mytable
-> (
-> Name varchar (20 ),
-> Sex char (1 ),
-> Birth date,
-> Birthaddr varchar (20)
-> );
Create an employee birthday table containing the employee name, gender, birth date, and city of birth.
Because the column values of name and birthadd change, varchar is selected and its length is not necessarily 20.
You can choose any length from 1 to 255. If you need to change its font length later, you can use the alter table statement.
Gender can be expressed by only one character: "M" or "F". Therefore, char (1) is used );
The birth column uses the date data type.

2. Query the newly added records:
Mysql> select * From mytable;
Empty set (0.00 Sec) indicates that the table created just now has no records.

3. Add new record:
Mysql> insert into mytable
-> Values
-> (
-> 'Abccs ',
-> 'F ',
-> '2017-07-07 ',
-> 'China'
-> );

4. Load data into a database table in text mode:
It is too troublesome to add new records one by one.
Create an example file named "“mysql.txt", arrange each record in each row according to the table structure, and separate the values with the "tab.
Abccs F 1977-07-07 China
Mary F 1978-12-12 USA
Tom M 1970-09-02 USA
Use this command to load the Upload File named mytable.txt to the table:
Mysql> load data local infile "mytable.txt" into Table mytable;

Data Import command load data local infile "file name" into Table table name;
Note: You 'd better copy the file to the MySQL/bin directory and use the use command to select the database where the table is located.

5. Update record:
Mysql> Update mytable set birth = "1973-09-02" where name = "Tom ";

6. Delete record:
Mysql> Delete from mytable where id = 10; // delete all records with ID = 10;
Mysql> Delete from mytable where id = 10 limit 1; // delete one record with ID = 10;
Mysql> Delete from mytable // delete all records of a table;
Mysql> Delete from T1 where c> 10;
Mysql> drop table tablename1, tablename2 ,...; // Delete an entire table or multiple tables. Be careful when using this operation.

7. Rename a table:
Mysql> alter table T1 rename T2;

8. Modify the table structure of MySQL:

View the MySQL table structure:
Mysql> describe mytable; or use show columns from table name;

Modify Field attributes
Mysql> alter table tablename modify ID int (10) unsigned auto_increment primary key not null

Modify Default Value
Mysql> alter table tablename alter ID default 0

Add primary key to the field
Mysql> alter table tablename add primary key (ID );

Delete primary key
1. alter table tablename drop primary key;
2. Drop primary key on tablename;

Modify Table Data Engine
Mysql> alter table tablename engine = MyISAM (InnoDB );

Add a new field name:
Mysql> alter table mytable add column single char (1 );
Mysql> alter table table add field int (11) unsigned not null

Delete Field
Mysql> alter table T2 drop column C;

Appendix:
To change column A from integer to tinyint not null (same name ),
Change Column B from Char (10) To char (20), rename it, and change from B to C:
Alter table T2 modify a tinyint not null, change B C char (20 );

Add a new timestamp column named D:
Alter table T2 add D timestamp;

Add an index on column D and set column A as the primary key:
Alter table T2 add index (d), add primary key ();

Add a new auto_increment integer column named C:
Alter table T2 add c int unsigned not null auto_increment, add index (C );

Note that we have indexed C because the auto_increment column must be indexed,
In addition, we declare that C is not null because the indexed Column cannot be null.

Create an index using the first 10 characters of the name column:
Create index part_of_name on customer (name (10 ));

 

3. Data backup and recovery:

Export and import data: (the command is executed in the DOS MySQL/bin directory)
Export table
Mysqldump -- opt school> school. SQL
Note: Back up all the tables in the school database to the school. SQL file. School. SQL is a text file,
Select the file name. open the file to see if you have any new discoveries.
Mysqldump -- opt School Teacher Student> school. Teacher. Student. SQL
Note: Back up the teacher and student tables in the school database to school. Teacher. Student. SQL.
.

Import table
MySQL
Mysql> Create Database School;
Mysql> use school;
Mysql> source school. SQL;
(Or change school. SQL to school. Teacher. SQL/school. Teacher. Student. SQL)

Export Database
Mysqldump -- databases db1 DB2> db1.db2. SQL
Note: Back up database DBL and DB2 to the db1.db2. SQL file. db1.db2. SQL is a text file with a file name.
Choose, open it, and you will see new discoveries.
(For example:
Mysqldump-H host-u user-P pass -- databases dbname> file. Dump
Import the database dbname on the host named "user" and "password pass" to file. Dump .)

Import Database
Mysql <db1.db2. SQL

Copy database mysqldump -- all-databases> all-databases. SQL
Note: Back up all databases to a all-databases. SQL file. The all-databases. SQL is a text file with any file name.

Import Database
MySQL
Mysql> drop database;
Mysql> drop database B;
Mysql> drop database c;
...
Mysql> source all-databases. SQL; (or exit MySQL after exiting MySQL mysql <all-databases. SQL)
<

 

Post address: http://hi.baidu.com/chenruijun1988/blog/item/0e923445fd77a088b2b7dcfb.html