SQL Learning Notes (i)

SQL Learning Notes (i)

I. Retrieval of data

How to use the SELECT statement to retrieve one or more data columns.

1.1 Retrieving a single column

SELECT Prod_name from Productions;

Note: The retrieved data is not sorted

1.2 Retrieving multiple columns

SELECT Prod_id,prod_name,prod_pricefrom Productions;

NOTE: SQL statements generally return raw, unformatted data.

1.3 Retrieving all Columns

SELECT * from Productions; wildcard * indicates any number of matches of any character.

1.4 Retrieving different values (the same value does not appear)

SELECT DISTINCT Vend_idfrom Productions;

Parse: Returns a unique vend_id line

Warning: If you retrieve more than one column, using the DISTINCT keyword will retrieve anything that has a different value in one column

1.5 Limit return results

Description: Specifies that the first 5 rows are returned

--DB2 SQL statement Select Prod_namefrom Productsfetch first 5 row only; #Oracle (calculate rows based on rownum) select Prod_name from Productswhere rownum<=5; #MySQL, Postgresql,sqliteselect prod_namefrom productslimit 5;

Specify to return 5 rows starting from line 5th

Select Prod_name from Productslimit 5 OFFSET 5, #Mysql, shortcut in MARIADB Select Prod_name from Productslimit 5, 5;

Warning: The first retrieved row is line No. 0, and so on

1.6 Using annotations

Description: Used for comment description or for test debugging

--#/*   */

Second, sort the search data

2.1 Sorting retrieved data

Description: Use the SELECT and ORDER BY clauses to retrieve data as needed.

Theory: relational Database design theory argues that the order of the retrieved data should not be assumed to have any meaning if the order of sorts is not explicitly specified.

Concept: clauses (Clause)

A clause is usually made up of a keyword and data.

SELECT prod_name from Productsorder by Prod_name;

Attention:

The 1.DBMS software will sort the data in alphabetical order on the Prod_name column. 2.ORDER by must be the last, or it will be an error 3. Sorting with non-retrieved columns is also possible

2.2 Sorting by multiple columns

The code will retrieve 3 columns and sort the results by two columns-first by price, then by name

SELECT Prod_id,prod_price,prod_namefrom Productsorder by Prod_price,prod_name;

Understanding: The same prod_price will be sorted by Prod_name

2.3 Sorting by Column position

Description: Not only can sort by column name, but also by column position

In the example, sort by the second column and then the third column.

SELECT Prod_id,prod_price,prod_namefrom Productsorder by 2, 3;

2.4 Specifying the direction of the sort

Description: The default ascending sort, or descending sort, is required to use the DESC keyword.

--Descending sort Select Prod_id,prod_price,prod_namefrom productsorder by Prod_price DESC;

Description: Sorting multiple columns

SELECT prod_id,prod_price,prod_namefrom Productsorder by Prod_price Desc,prod_name;

Attention:

1.DESC applies only to the column names that precede it to sort in descending order and the column name later does not apply

2. z is equivalent to Z by default unless the administrator changes the settings themselves

Third, filtering data

3.1 Using the WHERE clause

Description: Use the WHERE clause of the SELCT statement to specify the search criteria used to retrieve the data that the administrator needs.

SELECT Prod_name,prod_pricefrom Productswhere prod_price=5;

Note: All data types equal to 5 will be output

Tips:

Two ways to filter: SQL Filtering and application filtering

Data can be filtered not only at the data layer but also at the application level. However, this approach is inappropriate. Having the client process data affects the performance of the application and consumes network bandwidth.

Warning: When using both the WHERE clause and the ORDER BY clause, the order by is placed after the WHERE clause.

3.2 WHERE clause operator

= < <=! Not less than > >=!> <> not equals! = is not equal to between between the specified two values is a null null value

3.2.1 Checking a single value

SELECT prod_name,prod_pricefrom productswhere Prod_price <=100;

3.2.2 Mismatch Check

SELECT vend_id,prod_namefrom productswehre vend_id <> ' china007 '

3.2.3 in-range search

SELECT Prod_name,prod_pricefrom Productswhere Prod_price between and 15;

3.2.4 Null value Check

No value (NULL)---No value

Note: It differs from field 0, empty character, or contains a space

Description: Determines that a value is empty and cannot be simply checked = Null,select can use is null to determine which column has a null value.

SELECT Cust_name from Customerswhere Cust_email is NULL;

Reference book: "SQL must know must be"

This article comes from "a bit of meaning!" "Blog, be sure to keep this provenance http://powermichael.blog.51cto.com/12450987/1974431

SQL Learning Notes (i)