The basic part of SQL sentence summary

Create a database

CREATE DATABASE database name

Delete database

DROP DATABASE database name

Create a new table

Create table table name (column type, column type,..)

Create a new table based on an existing table

1. Create table new table name like old table name; 2. Create table new table name as select column 1, column 2... From old table name definition only

Delete tabl

DROP TABLE table name

New column

ALTER TABLE table name add column column type

Add primary key

ALTER TABLE table name add primary key (column)

Delete primary key

ALTER TABLE table name drop primary key (column)

Create an index

Create [unique] index index name on table name (column … .)

Delete index

Drop index index name

Create a view

Create view View name as select statement

Delete View

Drop view View name

Query

Select * from table name

Conditional query

Select * from table name where condition

insert

Insert into table name (column name 1, column name 2) values (value 1, value 2)

Delete

Delete from table name; Note: no condition will delete the contents of the entire table delete from table name where condition

Update

Update table1 set field1=value1 where condition

Fuzzy search

Select * from table name where column like'% value%'

Sort

Select * from table name order by column [desc]; default is ascending order, and desc is reverse order.

Count the number of rows

Select count as alias from table name

Summation

Select sum (column) as alias from table name

Find the average

Select avg (column) as alias from table name

Seek the maximum value

Select max (column) as alias from table name

Find the minimum value

Select min (column) as alias from table name

Modify the name of the database:

Sp_renamedb 'old name', 'new name'

Switch database

Use database name

Left outer connection

Select table name. Column name from A table name LEFT OUT JOIN B table name ON A table name A table column name = B table name .B column name

Right outer connection

Select table name. Column name from A table name RIGHT OUT JOIN B table name ON A table name A table column name = B table name .B column name

Full external connection

Select table name. Column name from A table name FULL OUT JOIN B table name ON A table name A table column name = B table name .B column name

Grouping

Select column name, group name, max (age) from table name group by column name

Database backup

# myslq backup mysqldump-u user name-h host address-p database name > backup file name / / backup entire database mysqldump-u user name-h host address-p database name Table 1 Table 2 > backup file name / / backup table mysqldump-u user name-h host address-p-- all-databases > backup file name / / backup all databases mysqldump-u user name-h host address-p-- databases database 1 Database 2 > backup file name / / backup multiple databases # mysql Import data mysql-u username-p password < Database data file to be imported # mongoDB backup mongodump-h host address-d database name-o backup directory # mongoDB backup recovery mongorestore-h host address: Port-d database name backup directory # DB2 backup Db2 backup database database instance name to backup directory # DB2 backup recovery db2 restore db database instance name from taken at replace existing