Standard SQL syntax _ MySQL

SQL is currently the most common relational database language. ANSISQL is a standard SQL language developed by the database Committee of the American National Institute of Standards (ANSI). most relational database products support standard SQL languages, but they often have their own SQL dialects. In the hierarchical software structure, SQL is currently the most common relational database language. Ansi SQL is a standard SQL language developed by the database Committee of the American National Institute of Standards (ANSI). most relational database products support standard SQL languages, but they often have their own SQL dialects.

In a hierarchical software structure, a relational database is located at the bottom layer, and its upper-layer applications are called Database customer programs. Taking mysqlas an example, mysql.exe and Java applications are their two customer programs. These customer programs finally communicate with the database through the SQL language.

The full English name of Structured Query Language can be translated into a Structured Query Language. In fact, Structured Query Language not only supports data Query, but also supports data definition, data manipulation, and data control.

SQL language type

Language type description SQL statement

Data Definition Language (DDL), which defines create, drop, and alter statements for tables, views, and indexes in a database.

DML (Data Manipulation Language) Data Manipulation Language, saving, updating, or deleting Data insert, update, and delete statements

DQL (Data Query Language) Data Query Language, queries Data in the database select statement

DCL (Data Control Language) Data Control Language, used to set database user permissions grant and remove statements

Data integrity

When a user inputs data to the database, the user may input incorrect data for various reasons. Ensuring that the input data meets the requirements has become a top concern for database systems, especially multi-user relational database systems. To solve this problem, the concept of data integrity has emerged in the database field. Data Integrity is the specification that must be met by the index Data. it is mainly divided into three types:

Entity Integrity)

Specifies that a row (that is, each record) of a table is a unique entity in the table. Entity integrity is achieved through the primary key of the table.

Domain Integrity)

The columns (fields) in the database table must comply with certain data types or constraints. Such as not null.

Referential Integrity)

Make sure that the foreign key of one table corresponds to the primary key of the other table.

DDL data definition language

Defines tables, views, and indexes in a database. The DDL statements are as follows:

Createtable: create a table.

Crate table MERS (

ID bigint not null,

NAME varchar (15) not null,

AGE int,

Primary key (ID)

);

Crate table ORDERS (

ID bigint not null,

ORDER_NUMBER varchar (15) not null,

PRICE double precision,

CUSTOMER_ID bigint,

Foreign key (CUSTOMER_ID) references MERS (ID)

);

When creating a database schema, DDL statements of all tables are usually stored in the same SQL script file. DDL statements must be defined in the order of the first parent table and the second child table. If the reference relationships between tables change, you must modify the sequence of DDL statements, which increases the difficulty of maintaining SQL script files. To solve this problem, you can define a foreign key in another way.

Crate table MERS (

ID bigint not null,

NAME varchar (15) not null,

AGE int,

Primary key (ID)

);

Crate table ORDERS (

ID bigint not null,

ORDER_NUMBER varchar (15) not null,

PRICE double precision,

CUSTOMER_ID bigint,

Primary key (ID)

);

Alter table ORDERS add constraint FK_CUSTOMER foreign key (mermer_id) references MERS (ID );

Altertable: modify a table.

Droptable: Delete a table and delete all records in the table.

DML data manipulation language

DML is used to insert, update, or delete data to a database. these operations correspond to insert, update, and delete statements respectively.

When these statements are executed, the database system first checks the data integrity. if these statements violate the data integrity, the database system will terminate the execution of SQL statements abnormally.

DQL data query language

The core of SQL is the data query language. The query statement syntax is as follows:

Select target column from basic table (or view) [where condition expression] [group by column name 1 [having condition expression] [order by column name 2 [asc | desc]

Simple query

A simple SQL query statement where the where clause sets the query conditions and the order by clause sets the sorting method of the query results.

(1) query customers between 10 and 50. the query results are first sorted by age in descending order, and then by name in ascending order.

Select * from MERS where age between 18 and 50 order by age desc, name asc;

(2) query customers named "Tom", "Mike", and "Jack.

Select * from MERS where name in ('Tom ', 'Mike', 'Jack ');

(3) the customer who queries the name with the second letter ".

Select * from MERS where name like ''_ a % '';

(4) query the name of a customer whose age is null.

Select name from MERS where age is null;

Note: the expression age = null cannot be used to compare whether age is null, because the value of this expression is neither true nor false, but always null. When the where clause value is null, the query result of the select statement is null.

(5) specify an alias for the table and field in the query statement:

Select name c_name, age c_age from customer c where c. id = 1;

Connection query

The connection syntax of the from clause of the connection query is as follows:

From talbe1 join_type table2 [on (join_condition)] [where (query_condition)]

Table1 and talbe2 indicate the tables involved in the join operation. table1 indicates the left table and table2 indicates the right table. The on clause sets the connection condition. The where clause sets the query condition. join_type indicates the connection type, which can be divided into three types:

Cross join: returns the Cartesian product of all data rows in the join table without the on clause.

Select * from MERS, orders;

Returns the product of the number of records in two tables. If there are 5 records in the MERS table and 7 records in the orders table, 35 records are returned.

Inner join: returns the data rows in the join table that meet the connection and query conditions.

Explicit inner join: use the inner join keyword to set the join conditions in the on clause.

Select c. id, o. customer_id, c. name, o. id roder_id, order_number from customers c inner join orders o on c. id = o. customer_id;

Implicit inner join: does not contain the inner join keyword or the on keyword, and sets the join condition in the where clause.

Select c. id, o. customer_id, c. name, o. id order_id, order_number from customers c, orders o where c. id = o. customer_id;

Outer join: left outer join and right outer join ). Different from the internal connection, the external connection not only returns the rows that meet the connection and query conditions in the connection table, but also returns the left table (when the left outer connection is used) or the right table (when the right outer connection is used) only data rows that meet the query conditions but do not meet the connection conditions.

Select c. id, o. customer_id, c. name, o. id order_id, order_number from customers c left outer join orders o on c. id = o. customer_id;

The query results of the preceding query statement contain not only data that meets the on connection conditions, but also other data rows in the left table of MERS.

Subquery

A subquery is also called a nested query. it is embedded with a select query statement in the select clause or where clause. the following example shows its usage.

1) query customers with more than 3 orders:

Select * customers c where c <= (select count (*) from orders o where c. id = o. customer_id );

2) query all orders of customers named "Mike"

Select * from orders o where o. customer_id in (select id form cutomers where name = 'Mike ');

3) query customers without orders:

Select * from customers c where 0 = (select count (*) from order o where c. id = o. customer_id );

Or

Select * from MERS c where not exists (select * from orders o where c. id = o. customer_id );

4) query the name and age of the customer whose ID is 1 and the total price of all their orders:

Select name, age, (select sum (price) from orders where customer_id = 1) total_price from customers where id = 1;

Total_price is an alias

You can also use the left outer join query to perform the same functions:

Select name, age, sum (price) from customers c left outer join orders o on c. id = o. customer_id where c. id = 1 group by c. id;

If the database does not support subqueries, you can use connection queries to perform the same functions. In fact, all subquery statements can be rewritten as connection query statements.

Joint query

The union query can merge the query results of two query statements, remove duplicate data rows, and return the query results without duplicate data rows. Union keywords are used for joint queries, for example:

Select * from MERS where age <25 union select * from customers where age> = 24;

Report query

Report query performs grouping statistics on data rows. the syntax format is as follows:

[Select…] From... [Where…] [Group... [Having... ] [Order... ]

The group by clause specifies which fields are grouped, and the having clause sets the grouping query conditions. You can use the following SQL aggregate functions in report query.

Count (): Number of statistical records

Min (): minimum value

Max (): calculates the maximum value.

Sum (): sum

Avg (): calculates the average value.

Usage:

1) query the total prices of all orders of each customer by customer group:

Select c. id, c. name, sum (price) from customers c left outer join orders o on c. id = o. customer_id group by c. id;

2) query the total prices of all orders of each customer by customer group, and require the total price of orders to be greater than 100:

Select c. id, c. name, sum (price) from customers c left outer join orders o on c. id = o. customer_id group by c. idhaving (sum (price)> 100 );