Collated from book “SQL Must Know must Know”

1.1 Database Basics

The database

A database is a container that holds organized data (in the case of a relational database). Database software is also called database management system (DBMS). A database is a container created and manipulated by a DBMS.

table

A table is a structured file that can be used to store a particular type of data. Each table in the database has a unique name. The characteristics of a table are defined in a schema, which can be used to describe a specific table in a library or the entire database (and its relationships to the tables).

Columns and data types

Column: A field in a table. All tables are composed of one or more columns. Each column has a corresponding data type.

Datatype: Defines which types of data a column can store.

line

The data in a table is stored in rows, with each record saved stored in its own row.

A primary key

Each row in the table should have one or more rows that uniquely identify themselves to facilitate manipulation of specific rows. You should always define a primary key.

Primary key features:

  1. No two rows have the same primary key
  2. Each row must have a primary key value (the primary key cannot be NULL)
  3. The value of the primary key cannot be modified or updated
  4. Primary keys cannot be reused (if a row is deleted from a table, its primary key cannot be assigned to another row)

A primary key is typically defined on a single column of a table, but it is possible to use multiple columns as primary keys. When multiple columns are used as primary keys, all columns must meet the above conditions, and the combination of all column values must be unique.

2 Retrieving Data

2.1 SELECT statement

Ex. :

SELECT prod_name
FROM Products;
Copy the code

SQL statements are case insensitive, but we usually capitalize keywords and lower case column and table names to make our code easy to read and debug. When retrieving multiple columns, separate them with commas. Use the wildcard * to retrieve all columns, but it’s best not to do this unless you really want to retrieve all columns. The advantage of using wildcards is that you can retrieve columns with unknown names.

2.2 Retrieving different values

Search using the DISTINCT keyword, returning only DISTINCT values. The DISTINCT keyword works on all retrieved columns, not just the column that follows it.

2.3 Restricted Results

SQL Server / Access:

SELECT TOP 5 prod_name
FROM Products;
Copy the code

DB2:

SELECT prod_name
FROM Products
FETCH FIRST 5 ROWS ONLY;
Copy the code

Oracle:

SELECT prod_name
FROM Products
WHERE ROWNUM <= 5;
Copy the code

MySQL / MariaDB / PostgreSQL / SQLite:

SELECT prod_name
FROM Products
LIMIT 5;
Copy the code

All of the above code is used to return the first five lines of data.

Use OFFSET to specify where to start retrieving:

SELECT prod_name
FROM Products
LIMIT 5 OFFSET 5;
Copy the code

LIMIT 5 OFFSET 5 instructs MySQL to return 5 rows of data starting with (including) row 5. LIMIT 5 OFFSET 5 can also be abbreviated to LIMIT 5,5.

2.4 annotations

Inline comments

SELECT prod_name -- this is a comment 
FROM Products;
Copy the code

The other has less support for inline comments, where the entire line is used as a comment:

# this is a comment 
SELECT prod_name 
FROM Products;
Copy the code

Multiline comment

Use the / * * /

3 Sort and retrieve data

3.1 Sorting Data

SELECT prod_name 
FROM Products
ORDER BY prod_name;
Copy the code

Use the ORDER BY clause to sort the retrieved data. The ORDER BY clause should be at the end of the SELECT statement. It is not necessary to sort by retrieved columns; it is legal to sort data by non-retrieved columns.

3.2 Sort by multiple columns

Use commas to separate them and sort them in order of precedence.

3.3 Sort by column position

SELECT prod_id, prod_name, prod_price
FROM Products
ORDER BY 1, 2;
Copy the code

Easy to use, but may cause the wrong column name to sort.

3.4 Specifying the Sorting direction

If this parameter is not specified, the order is ascending by default. To sort the order in descending order, you need to specify DESC.

4 Filtering Data

4.1 Use the WHERE clause

Specify search criteria (filter criteria) to extract the desired data. The WHERE clause comes after the FROM clause.

4.2 WHERE clause operator

  • Equal to =
  • Does not equal <>! =
  • < <
  • Less than or equal to <=
  • Not less than! <
  • More than >
  • The value is greater than or equal to >=
  • No more than! >
  • BETWEEN values (including boundaries) BETWEEN… AND …
  • For NULL The value IS NULL