Usage of standard SQL language

Original link: http://www.ifyao.com/2015/05/18/%E6%A0%87%E5%87%86%E7%9A%84sql%E8%AF%AD%E8%A8%80%E4%BD%BF%E7%94%A8%E6%96% b9%e6%b3%95%e5%8f%8a%e5%a4%9a%e8%a1%a8%e8%bf%9e%e6%8e%a5/

usage of standard SQL language

The SQL language is currently the most common relational database language. ANSI SQL refers to the standard SQL language developed by the U.S. National Standards Office (ANSI) database committee, and most relational database products support the standard SQL language, but they also tend to have their own SQL dialect.

In a layered software structure, the relational database is at the bottom, and its upper-level application is called the database's client program. In MySQL, for example, Mysql.exe and Java applications are its two client programs. These client programs eventually communicate with the database through the SQL language.

The English full name of SQL (Structured query Language) can be translated into a structured query language, but in fact it has data query function, data definition, data manipulation and data control function.

Types of SQL languages

Language type	Describe	SQL statements
DDL (Data Definition Language)	Data definition language, defining tables, views, and indexes in a database	Create, drop, and ALTER statements
DML (Data manipulation Language)	Data manipulation languages, saving, updating, or deleting data	Insert, UPDATE, and DELETE statements
DQL (Data Query Language)	Data query Language, querying the data in the database	SELECT statement
DCL (Data Control Language)	Data Control language for setting permissions for database users	Grant and remove statements

Data Integrity

When a user enters data into a database, it is possible for users to enter incorrect data for various reasons. It is the primary concern of the database system, especially the multi-user relational database system, to ensure the input data conform to the regulations. In order to solve this problem, the concept of data integrity appeared in the database domain. Data integrity (Integrity) refers to the specification that data must conform to, which is divided into three main categories:

Solid Integrity (entity Integrity)

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

Domain Integrity (Integrity)

A column (that is, a field) of a database table must conform to a specific data type or constraint. such as NOT NULL.

referential integrity (referential Integrity)

Ensure that the foreign key of one table corresponds to the primary key of another table.

DDL data Definition Language

Used to define tables, views, indexes, and so on in a database. The relevant DDL statements are as follows:

• CreateTable: Create a table.

Crate Table CUSTOMERS (

ID bigint not null,

NAME varchar (not null),

Age int,

Primary key (ID)

);

Crate Table ORDERS (

ID bigint not null,

Order_number varchar () not null,

Price double precision,

customer_id bigint,

Foreign Key (customer_id) references CUSTOMERS (ID)

);

When creating a database schema, it is common for all table DDL statements to be placed in the same SQL script file, and DDL statements must be defined in the order of the child tables after the table of the father. If the reference relationship between tables changes, the order of the DDL statements must be modified, which increases the difficulty of maintaining the SQL script file. To solve this problem, you can define a foreign key in a different way.

Crate Table CUSTOMERS (

ID bigint not null,

NAME varchar (not null),

Age int,

Primary key (ID)

);

Crate Table ORDERS (

ID bigint not null,

Order_number varchar () not null,

Price double precision,

customer_id bigint,

Primary key (ID)

);

ALTER TABLE ORDERS add constraint Fk_customer foreign key (customer_id) references CUSTOMERS (ID);

• Altertable: Modifies a table.
• Droptable: Deletes a table and deletes all records in the table.

DML Data Manipulation Language

DML is used to insert, update, or delete data to the database, which correspond to insert, UPDATE, and DELETE statements, respectively.

When these statements are executed, the database system performs a data integrity check, and if those statements violate data integrity, the database system terminates the execution of the SQL statement abnormally.

DQL data Query Language

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

Select destination column from base 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 condition, and the ORDER BY clause sets how the query results are sorted.

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

SELECT * FROM customers where the age between and the order by age desc,name ASC;

(2) Check with the customer named "Tom", "Mike" and "Jack".

SELECT * FROM Customers where name in (' Tom ', ' Mike ', ' Jack ');

(3) The second letter of the queried name is the customer of "a".

SELECT * FROM customers where name is like ' _a% ';

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

Select name from customers where is null;

Note: You cannot use the expression Age=null to compare whether age is null, because the value of this expression is neither true nor false, but is always null. When the WHERE clause evaluates to Null,select, the query result of the query statement is empty.

(5) Specify aliases for tables and fields in query statements:

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

Connection Query

The connection syntax format for the FROM clause of a connection query is:

From Talbe1 join_type table2 [On (Join_condition)] [WHERE (query_condition)]

Table1 and talbe2 represent tables that participate in the join operation, Table1 to the left table, and table2 to the right table. The ON clause sets the join condition, WHERE clause sets the query condition, Join_type represents the connection type, can be divided into 3 kinds:

• Cross join: Returns the Cartesian product of all data rows in the join table without an ON clause.

Select * from Customers,orders;

Returns the product of the number of records in two tables. If there are 5 records in the Customers and 7 records in the Orders table, the result returns 35 records.

• INNER JOIN (INNER JOIN): Returns the data rows in the join table that meet the join criteria and query criteria.

Explicit inner joins: Use the INNER JOIN keyword to set the join condition 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 joins: does not contain the INNER JOIN keyword and the on keyword, setting 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 joins: Divided into left outer join (outer join), right outer join (outer join). Unlike an inner join, an outer join returns not only data rows that meet the join condition and query criteria in the join table, but also data rows that meet the query criteria but do not meet the join criteria in the left table (when the left outer join) or the right table (when the right outer joins).

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_i D

The query results for the above query statements contain not only data that conforms to the on join condition, but also other data rows in the customers left table.

Sub-query

A subquery is also called a nested query, which is an example of the use of a SELECT clause in a SELECT or a WHERE clause.

1) to inquire customers with more than 3 orders:

SELECT * Customers c where c<= (select COUNT (*) from orders o where c.id=o.customer_id);

2) Check all orders for a customer named "Mike"

SELECT * FROM Orders o where o.customer_id in (select ID form cutomers where name= ' Mike ');

3) Inquiries for customers without orders:

SELECT * FROM Customers C where 0= (select COUNT (*) from Order o where c.id=o.customer_id);

Or

SELECT * FROM customers C where NOT EXISTS (SELECT * FROM Orders o where c.id=o.customer_id);

4) Check the name, age and Total price of all orders for the customer with ID 1:

Select Name,age, (select SUM (Price) from the orders where customer_id=1) Total_price from customers where id=1;

Total_price is an alias

You can also perform the same function with a LEFT OUTER join query:

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, the same functionality can be accomplished by connecting queries. In fact, all subquery statements can be rewritten as connection query statements.

Federated Queries

A federated query merges the query results of two query statements, removes the duplicate rows from them, and returns the results of the query without repeating rows of data. Union queries use the Union keyword, for example:

SELECT * FROM Customers where AGE<25 Union SELECT * FROM Customers where age>=24;

Report Query

The report query groups the data rows in the following syntax format:

[Select ...] From ... [Where ...] [GROUP By ... [Having ...] [Order BY ...]

Where the GROUP BY clause specifies which fields to group, the HAVING clause sets the group query criteria. The following SQL aggregation functions can be used in report queries.

COUNT (): Number of statistical record bars

Min (): To find the minimum value

Max (): Ask for maximum value

SUM (): Sum

AVG (): Averaging

Usage:

1) According to the customer group, the total price of all orders for each customer is queried:

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) According to the customer group, the total price of all orders for each customer is queried, and the total price of the order is 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.id have (sum (PRI CE) >100);

Usage of standard SQL language