Summary of sql Basic Statements in Mysql Database

In this paper, the basic sql statement of Mysql database is described as an example. Share it for your reference, as follows:

SQL Basic Statement

1. Login exit and shortcut keys:

(1) Shortcut keys:

----Quickly back to the top of the line
ctrl + a

----Back to the end of the line
ctrl + e

----Clear screen
ctrl + l

--End
ctrl + c + Spaces

(2) Connect to the database:

----Display password

mysql -uroot -pmysql

----No password displayed

mysql -uroot -p 
 Enter password 

(3) Exit the database:

quit/exit/ctrl + d

(4) Partial orders:

----Display database version

select version();

----Display time

select now();

2. Database operations:

(1) Create:

--Create a database

create database  Database name  charset=utf8;

(2) Use:

--Use of databases

use  Database name 

(3) View:

---View the database currently in use

select database();

---View all databases

show databases;

---View the Create Database statement

show create database  Database name ;

(4) Delete:

--Delete the database

drop database  Database name ;

(5) Backup and recovery of database (understand):

---Backup

mysql -uroot -p 
 Enter password 

0

--Recovery

mysql -uroot -p  New database name  < xxx.sql

3. Table structure operations:

(1) View:

----View all tables in the current database

show tables;

---View table structure

mysql -uroot -p 
 Enter password 

3

----View table creation statements

mysql -uroot -p 
 Enter password 

4

(2) Create:

---Create a table

mysql -uroot -p 
 Enter password 

5

(3) Modification:

----Add a field

mysql -uroot -p 
 Enter password 

6

----Modify fields (without renaming)

alter table  Table name  modify  Field name   Type   Constraint ;

--Modify the field (rename)

alter table  Table name  change  Old field name   New field name   Type   Constraint ;

---Delete a field

mysql -uroot -p 
 Enter password 

9

(4) Delete:

--Delete table:

drop table  Table name ;

4. Table data manipulation:

(1) View:

----View all fields

select * from  Table name ;

----View the specified field

select  Field name , Field name  from  Table name ;

---View by Criteria

select * from  Table name  where  Condition ;

----Alias fields for viewing

select  Field name  as  Alias  from  Table name ;

(2) Add:

--Full column insert

insert into  Table name  values( Record );

--Partial insertion

insert into  Table name ( Field name , Field name ) values( Record );

--Multiline insertion

insert into  Table name values( Record 1),( Record 2) … ;

(3) Delete:

--Physical deletion

delete from  Table name  where  Condition ;

--Logical deletion

alter table  Table name  add  Delete information field name  bit dafault 0; 
update  Table name  set  Delete information field name =1 where  Condition ;

(4) Modification:

--All modifications

update  Table name  set  Modify information ;

--Modified on a conditional basis

update  Table name  set  Modify information  where  Condition ;

More readers interested in MySQL can check out the topics on this site: "MySQL Query Skills Encyclopedia", "MySQL Common Function Summary", "MySQL Log Operation Skills Encyclopedia", "MySQL Transaction Operation Skills Summary", "MySQL Stored Procedure Skills Encyclopedia" and "MySQL Database Lock Related Skills Summary"

I hope this article is helpful to everyone's MySQL database.