1, SQL language classification:
1. DDL: database definition language; It is used to establish and delete databases, tables, views, etc.
DDL includes create, alter, drop, etc.
2. DML: database manipulation language: used for adding, deleting, modifying and querying.
DML includes insert, delete and update.
3. DCL: database control language; Rights management and transaction management of database objects.
DCL includes commit, rollback, grant, etc.
4. DQL: data query language; Query is the basic function of database.
DQL uses select to query records in database tables.
2, DDL:
1. DDL database:
There are two main operations of DDL on database
1) . create database:
CREATE DATABASE Database name
2) . delete database:
DROP DATABASE Database name
2. Data sheet of DDL:
In DDL, there are three main operations on data tables: create, modify and delete.
When creating a data table, the information to be defined mainly includes: table name, field name and field type
.
1) . MySQL table creation syntax
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create_definition,...)] [table_options] [select_statement]
TEMPORARY: indicates to create a TEMPORARY table, which will disappear automatically after the current session ends
IF NOT EXISTS: before creating a table, first judge whether the table exists. It is created only when the table does not exist
create_definition: the key part of the table creation statement, which is used to define the attributes of each column in the table
table_options: configuration options of the table, such as the default storage engine and character set of the table
select_statement: create a table through a select statement
2) , operation table already exists
For existing tables, you can use the alter command to add, modify, delete fields, or delete tables.
#Add field sex of type VARCHAR(1)
ALTER TABLE contacts ADD sex VARCHAR(1);
#Modify the type of field sex to tinyint
ALTER TABLE contacts MODIFY sex tinyint;
#Delete field sex
ALTER TABLE contacts DROP COLUMN sex;
#Delete contacts table
DROP TABLE contacts;
3, DML:
1. INSERT data:
In MySQL, use the insert into statement to insert data into the data table.
INSERT insert a single piece of data:
INSERT INTO table_name (field1, field2, ..., fieldN) VALUES (value1, value2, ..., valueN);
INSERT insert multiple pieces of data:
INSERT INTO table_name (field1, field2, ..., fieldN) VALUES (valueA1, valueA2, ..., valueAN), (valueB1, valueB2, ..., valueBN), ..., (valueN1, valueN2, ..., valueNN);
matters needing attention:
1. If the field is character type, the value must use single quotation marks or double quotation marks, such as "value"; If the value itself has single or double quotation marks, escape is required
2. If you want to add data to all columns, the insert into statement can not specify columns, that is
INSERT INTO table_name VALUES (value1, value2, ..., valueN);
2. Modify data (update):
In MySQL, use the update statement to modify the data in the data table.
update syntax:
UPDATE table_name SET field1=newValue1, field2=newValue2 [WHERE Clause]
matters needing attention:
1. You can update one or more fields at the same time
2. You can specify the scope of update through the where clause. If there is no where, all records in the data table will be updated
3. delete data:
In MySQL, use the delete statement to delete the data in the data table.
delete syntax:
DELETE FROM table_name [WHERE Clause]
matters needing attention:
1. You can specify the range of deletion through the where clause. If there is no where clause, all records in the data table will be deleted
4, DQL:
1. Conditional query (where)
Single condition query
#where clause syntax used in SQL statements
SELECT column_name FROM table_name WHERE column_name operator value
Multi condition query
#and, or operator syntax
SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3
2. distinct:
distinct is used to return the unique different value of the column in the query (de duplication); If you want to get all the information of a column when querying data
Different values, you can use distinct.
#distinct syntax
SELECT DISTINCT column_name, column_name FROM table_name;
3. Group query:
#group by
group by means that the data is grouped according to a certain rule. It must be used in conjunction with the aggregation function, logarithm
After grouping data, count, sum, avg, max, min and other operations can be performed.
#group by syntax
SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name
explain:
1.
aggregate_function represents an aggregate function.
2.
group by can group one or more columns.
#group_concat syntax
group_concat([distinct] column_name [order by column_name asc/desc ] [separator 'Separator'])
group_concat is used together with group by to splice the values of a column according to the specified separator. MySQL defaults to
The separator for is a comma
4. Use of having
The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with aggregate functions. HAVING clause
To filter the grouped data.
#having grammar
SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
5. Sorting and pagination of query results
order Use of by
In SQL, use order By sorts the query result set by one or more columns.
#order by syntax
SELECT column_name1, column_name2 FROM table_name1, table_name2 ORDER BY column_name, column_name [ASC|DESC]
explain:
1.
ASC means in ascending order and DESC means in descending order.
2.
By default, columns are arranged in ascending order.
Use of limit
In the SELECT statement, the LIMIT clause is used to restrict the number of records to be returned. Usually, the LIMIT is used to realize paging.
#limit syntax
SELECT column_name1, column_name2 FROM table_name1, table_name2 LIMIT [offset,] row_count
explain:
1.
Offset specifies the offset of the first row to return. The offset of the first row is 0, not 1.
2.
row_count specifies the maximum number of rows to return.
5. Self join query
Self join is a special table join, which means that the interconnected tables are physically the same table, but logically they are multiple tables. since
Connections are usually used for data in tables. There are hierarchical structures, such as area tables, menu tables, commodity classification tables, etc.
#Self join syntax
SELECT A.column, B.column FROM table A, table B WHERE A.column = B.column;
6. Use of IN and LIKE
#The where clause uses in syntax
SELECT column_name FROM table_name WHERE column_name IN(value1, value2, ...)
#The where clause uses the like syntax
SELECT column_name FROM table_name WHERE column_name LIKE '%value%'
explain:
1. The% in the LIKE clause is similar to the * in a regular expression and matches any 0 or more characters
2. In the LIKE clause_ Match any single character
3. If there are no% and in the LIKE clause, It is equivalent to the effect of operator =
7. Subquery in, exists
#Subquery in syntax
SELECT column_name FROM table_name WHERE column_name IN( SELECT column_name FROM table_name [WHERE] );
#Subquery exists
EXISTS is a Boolean operator in a subquery that tests whether an internal query returns any rows. Put the data of the main query into the sub query
Do conditional verification and decide whether to retain the data result of the main query according to the verification result (TRUE or FALSE).
#The where clause uses the exists syntax
SELECT column_name1 FROM table_name1 WHERE EXISTS (SELECT * FROM table_name2 WHERE condition);
