1. What is SQL

Users only care about getting the query results they want; The DBMS gives an efficient execution of the Query. This is what the Query Optimizer does, and how it is executed is called a Query Plan.

Sql (structured query language)

As mentioned in the previous note, SQL is a common standard for writing query statements and can be called a language. Just like Java, c++ and so have version iteration, SQL also has, we often say sql92, sql99 and so are different versions of SQL. It has been updated to SQL2016.

SQL includes four broad categories:

  • DQL data query language, is the most common query statement, select from that set
  • DML data operation language, the increase and deletion of data, insert, delete, update
  • DDL data definition language, create type operations, including the creation of tables, indexes, views, and so on
  • DCL data control language, including authorization, commit, rollback

SQL is based on bags (duplicates) not sets (no duplicates)

2. SQL syntax

Aggregates

Functions that return a single value from a bag of tuples

  • Avg, min, max, sum, count
  • Avg, sum, and count support distinct operations on aggregated columns (select count (DISTINCT name))

Can only be used in the column name output in select as a function operation on the column.

group by

project tuples into subsets and calculate aggregates against each subset.

In the output of select, non-AGG columns must be columns that are group by. Otherwise, it cannot appear after select.

having

Filter the results of AGG once. Equivalent to the WHERE statement for group by.

String operations

  • Like: % matches any substring; _ Matches any single character;
  • Substring, upper, lower, concat

date/time

Output redirection

Insert query result directly into another table as content or create a new table:

CREATE TABLE table_new (
    SELECT cid FROM table_old
);



INSERT INTO table_old2 (
    SELECT cid FROM table_old1
);
Copy the code

Output control

  • order by
  • Asc/DESC can be sorted by multiple columns, and can be used in ascending or descending order
  • limit [offset]

Nested queries

Subqueries can be nested in many places within external queries

  • Not exists
-- find all courses that has no students enrolled in it.    

SELECT * FROM course
WHERE NOT EXISTS (
    SELECT * FROM enrolled
    WHERE course.cid = enrolled.cid
)
Copy the code

Window functions

Format:

SELECT ... FUNCTION_NAME(...) OVER (...)

-- function_name: (aggregation funcs or special funcs)
-- over: means how to "slice" up data
Copy the code

Special window functions:

  • ROW_NUMBER(): Outputs a row number

SELECT *， ROW_NUMBER() OVER() AS row_num

  • RANK() : displays the RANK of the current row in a column
  • Over () : can be used to perform an implicit grouping, such as ROW_NUMBER() over(PARTITION BY CID). Then the output line number is the row number within the group after each group is grouped BY CID.
SELECT *
        ,ROW_NUMBER() OVER (PARTITION BY cid)
FROM enrolled
ORDER BY cid
Copy the code

-- find the student with the highest grade for each course SELECT * FROM ( SELECT * ,RANK() OVER (PARTITION BY cid ORDER  BY grade ASC) AS rank FROM enrolled ) AS ranking WHERE ranking.rank = 1Copy the code

with

Generate a temporary table, “alternative to nested queries and views”.

WITH tempName (col1, col2) AS (
    SELECT 1, 2
)
SELECT col1+col2 FROM tempName
Copy the code

3. Summary

Relational algebra and SQL are both operational languages for relational data models. Relational algebra is more procedural and represents the actual order of operations.

SQL encapsulates relational algebra by writing SQL, the established consensus standard for operating databases, to implement the queries we expect. The DBMS designs its own optimizers to optimize the input SQL to convert it into relational algebraic expressions for execution in the most efficient way possible. The specific execution logic generated after optimization is called a Query Plan.