What is SQL?

Structured Query Language (SQL), a special purpose programming Language, is a database Query and programming Language used to access data and Query, update and manage relational database systems.

The classification of the SQL

  • DDL: Database definition, and database/table structure: CREATE, DROP, ALTER
  • DCL: Data control language: set user access security
  • DML: data manipulation language: manipulating table data insert UPDATE delete
  • DQL: data query language: select from WHERE

Database operations

operation SQL
Creating a database Create Database Specifies the name of the database
Deleting a Database Drop Database Specifies the database name
Modifying a Database Alter DATABASE character set Character set
Viewing all databases show databases
Viewing database definitions Show create database Specifies the database name
View the database currently in use select database()
Select the database Use Specifies the name of the database

Table structure operation

Create a table

Create table name (column name type (length) constraint, column name 2 type (length) constraint);

The constraints listed

  • Primary key constraint: Primary key
  • The only constraint is unique
  • Non-null constraint: not NULL

Delete table

Drop table table name

Modify the table

operation SQL
Add columns Alter table alter table add constraint on type of column of column name
Modify the column Alter table table name modify column type constraint
Modify the column name Alter table table name change old column name New column name type column constraint
Delete the column Alter TABLE Table name Drop column name
Modify the character set of the table Alter table Table name character set CHARACTER set
Modify the name of the table Rename table Old table name to new table name

See the table

operation SQL
View all table names in the current database show tables
View the definition structure/create statement for the table Show create table Specifies the name of the table
Look at the structure of the table Desc table name

CRUD operations on data in a table

Insert data

  • Insert into values(1, 1); insert into values(1, 2);
  • Insert into values(1, 2);
  • Insert into values(1, 2),(1, 2),(1, 2);

Delete the data

  • Delete from table name [where condition];
  • Truncate TABLE name: delete the table and then rebuild the table.

Update the data

  • Update table_name set table_name = value, table_name = value [where condition];

Query data

Select [distinct] [*] [column name 1, column name 2] FROM TABLE name WHERE group by [column name] having condition filtering order by sort 1 ASC, sort 2 desc;

Relational operator

  • < > is not equal to
  • ! = is not equal to

Logical operator

  • and or not

In the range

  • in

Fuzzy query

  • like
  • _ represents a single character
  • % represents multiple characters

The alias query

  • as

Aggregation function

  • The sum, sum
  • Avg () : average value
  • Count () : indicates the number of statistics
  • Max () : indicates the maximum value
  • Min () : indicates the minimum value

The sorting

  • Order by asC: sort in ascending order
  • Order by [column name] desc: sort in descending order

grouping

  • Group by [column name]

Mind mapping