Summary of common database commands

1. Create database:

mysql> create database test ;                         // Create a database in mysql. The ID of the database is test.
[root@host] # mysqladmin -u root -p create test; / / create a database under linux. The database ID is test.

2. Delete database:

mysql> drop  database test ;    // Delete the database in mysql. The ID of the database is test.
[root@host] # Mysqladmin - U root - P drop test; / / delete the database under linux. The database ID is test.

3. Create data table:

root@host# mysql -u root -p               
Enter password:*******
mysql> use RUNOOB;                                 //Select database
Database changed
mysql> CREATE TABLE runoob_tbl(        //The name of the data table is runoob_tbl
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)      //

4. Delete data table:

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> DROP TABLE runoob_tbl                  //Delete data table runoob_tbl
Query OK, 0 rows affected (0.8 sec)

5.select Query:

select * from runoob_tbl ;            //Query table runoob_tbl

select ip,username from runoob_tbl ; //Query runoob_ ip and username fields in TBL table

select ip from runoob_tbl  where username="woniu" ; //In runoob_ ip of query field username = "woniu" in TBL table

select ip from runoob_tbl  where username="woniu" order by ip desc ; //In runoob_ ip of query field username = "woniu" in TBL table, in reverse order of ip

6. Common commands

show databases;                  //Show all libraries 
use  test;                       //Select the database. The ID of the database is test
show  tables;                   //Display all the tables in the library. To use the selected database first
show create table  runoob_tbl;  //View table structure 
show  index  from                //Displays detailed index information for the data table, including the PRIMARY KEY.
quit/exit                        //Exit mysql

7. Fuzzy query -- like

mysql> select  * from runoob_tbl where username like '% wugui'; / / in runoob_ Get runoob in TBL table_ All records in the author field ending with wugui

% a '/ / data ending in a
 'a%' / / data starting with a
 '% a%' / / data with a

Query information that starts with a mysql field.

SELECT * FROM runoob_tbl  WHERE name LIKE 'mysql%';

The query contains information for mysql fields.

SELECT * FROM runoob_tbl  WHERE name LIKE '%mysql%';

Query for information ending in mysql fields.

SELECT * FROM runoob_tbl  WHERE name LIKE '%mysql';

8.msyql Insert millions of data

insert into warn_message(username,strength,ip,port,clienttype,logtime,country,warn_type) select username,strength,ip,port,clienttype,logtime,country,warn_type from warn_message;

9. Modify the default encoding format of the data table

alter table Table name convert to character set utf8(latin1);

10. Clear table data

truncate table table name

Reference resources

Tags: MySQL Database mysqladmin Linux

Posted on Thu, 14 May 2020 08:30:58 -0700 by baldwinw