"SQL language" SQL language Basics 01

1, Understand sql1.1 Database Foundation 1.1.1 Database

: A container that holds organized data (usually a file or a set of files) (also understood as a collection that is stored in an organized manner)
Note: Database software is called DBMS (Database management System)

1.1.2 Tables (table)

: A list of structures for a particular type of data (the same type and unique name)
Characteristics:
Defines how data is stored in a table, what data is stored, and how the data is broken down and named, etc. ~
Table name: Makes the table name unique, which is actually a combination of the database name and the table name.
Patterns: Information about attributes such as the layout of databases and tables.

1.1.3 Columns (column)

: A field in the table. All tables are made up of one or more columns
Note:
Each column has a corresponding data type.
Data type (datatype)
: Defines what kinds of data a column can store.

Decomposition data:
It is extremely important to decompose the data into multiple columns correctly.

1.1.4 Line (ROW)

: A record in the table

1.1.5 primary KEY (primary key)

A set (or column) whose value uniquely identifies each row in the table.
　　Tips:
Primary keys should always be defined, although primary keys are not always required, but most database designers
will ensure that each table they create has a primary key for future data manipulation
and management.

　Any column in the table can be a primary key, as long as it meets the following criteria:
1) No two rows have the same primary key value
2) Each row must have a primary key value (primary key column does not allow null values)
3) values in the primary key column are not allowed to be modified or updated
4) Primary key value cannot be reused (if a row is deleted from the table, his primary key cannot be assigned to a new row later)

1.2 What is SQL

SQL is Structured Query Language (abbreviated for Structured Query language). Sql
A language specifically designed to communicate with a database
Advantages:
1) SQL is not a specific language for a particular database vendor, almost all important
DBMS supports SQL
2) Easy to learn
3) can perform very complex and advanced database operations

2. Retrieving data
2.1 SELECT statement

Keywords (Keyword):
Reserved words as part of SQL. The keyword cannot be used as the name of a table or column.
Appendix E lists some of the commonly used reserved words
Tip: SQL is a language, not an application, how to write SQL statements and display output, which varies depending on the application

2.2 Retrieving a single column

Select Pro_name from Products;
The preceding statement uses the SELECT statement to retrieve a column named Pro_name from the Products table.
The FROM keyword expense retrieves data from that table after the required column name follows the Select

　　Tips:
Most SQL statements are delimited by semicolons (;).
SQL statements are case insensitive so select is the same as select. Many developers
Prefer to use uppercase for SQL keywords, and lowercase for column names and table names, making your code easier to read and debug.
However, although SQL is case-insensitive, table names, column names, and values may vary (depending on the configuration of the DBMS)
When the SQL statement is processed, all of the spaces are ignored. SQL can be written as a long line and can be divided into multiple lines

2.3 Retrieving multiple columns

When retrieving multiple columns, the same SELECT statement is still used. The only difference is that multiple column names must be given after the SELECT keyword,
Column names must be separated by commas.

　　Tips :
　　Data Representation :
SQL statements generally return raw, unformatted data. The formatting of the data is a matter of presentation, not a retrieval problem.
Therefore, it is stated in the application that typically displays the data. It is often seldom used directly with the actual retrieved data (no application-provided format)

2.4 Retrieving all Columns

Use the asterisk (*) wildcard character at the location of the actual column name

　　 tip : If given a wildcard character (*), all columns in the table are returned. The order of the columns is generally the physical order in which the columns appear in the table definition
But that's not always the case.
The biggest advantage is retrieving the unknown column

2.5 Retrieving different values (DISTINCT)

Distinct it instructs the database to return only different (unique) values. If you use the DISTINCT keyword, it must be placed before the column name

　 tip :　 distinct cannot be partially used
This keyword is used for all columns, not just the one that follows. All rows will be retrieved unless the specified columns are identical

2.6 Limiting results

If you want to return the first row or a certain number of rows, but this implementation is not the same in a different database
You can use the top keyword in SQL sever and access to limit the maximum number of rows returned
Cases:
SELECT TOP 5 pro_name from Products;
This code uses the SELECT TOP 5 statement to retrieve only the first five rows of data
The Oracle needs to calculate the run based on the RowNum (row counter)
Cases:
SELECT Prod_name
From Prodects
WHERE ROWNUM <=5;
You need to use the limit clause in MySQL
Example: SELECT prod_name
From Products
LIMIT 5;
Limit 5 returns data of no more than five elements
In order to rewind the next 5 rows of data, you need to specify where to start and the number of rows to retrieve
Cases:
SELECT Pro_name
From Products
LIMIT 5 OFFSET 5;
The first 5 words return 5 rows, the second 5 means starting from line 6th (including line sixth)

# # #经常用到在数据库中查询中间几条数据的需求

For example, the following SQL statement:

①selete * from TestTable limit 2, 1;

②selete * FROM TestTable limit 2 offset 1;

　　Attention:

1. Database data calculation is starting from 0

2.offset x is skipping x data, and limit y is selecting y data

3.limit x, Y × means skipping x data, reading Y data

Both are capable of accomplishing the need, but there is a difference between them:

① is starting from the third in the database query, take a data, that is, the third data read, one or two skip

② is to start querying two data from the second data in the database, that is, the second and third articles.

Offset, which is the meaning of the offsets, offset num represents the query starting from num+1.

Not all SQL implementations are the same

2.7 Using annotations

Note Use--(two hyphens) is nested within a row. --The text that follows is a comment, which is more commonly used
There is also a comment # that uses # at the beginning of a line, and this whole line will be commented.
/* Start, go to */end

3. Sort and retrieve data

Order by ..... Order

3.1 Sorting data

Relational database design theory argues that if you do not explicitly specify sequential ordering, you should not assume that the retrieved
The order of the data has any meaning
　　Tips :
The position of the ORDER by clause
When you specify an ORDER BY clause, you should ensure that it is the last note in the SELECT statement.
Typically the column used in the ORDER BY clause will be the column selected for display two. However, sorting data with non-retrieved columns is completely legal.

3.2 Sorting by multiple columns

Cases:

SELECT Prod_id,prod_price,prod_name Frmo Products
ORDER by Prod_price,prodname;
For the output in the above example, the product is sorted by Prod_name only if multiple rows have the same prod_price value, if
All the values in the Prod_price column are unique, they are not sorted by Prod_name.

3.3 Sorting by column position

Cases:
SELECT Prod_id,prod_price,prod_name
From Products
ORDER by 2, 3;
　　tip : This technique cannot be used when sorting based on the column memory that does not appear in the select list

3.4 Specifying the sort direction (DESC)

The sorting of data is not limited to ascending order (from A to Z) This knowledge is sorted by default
Use the ORDER BY clause +desc to sort in descending order
SELECT Prod_id,prod_price,prod_name
Frmo Products
ORDER by Prod_price DESC;

If you intend to sort by multiple columns
SELECT Prod_id,prod_price,prod_name
From Products
ORDER by Prod_price Desc,prod_name;
　　Tips:
The DESC keyword is applied only to the column name immediately preceding it.
If you want to sort descending on more than one column, you must specify the DESC keyword for each column
Desc=descending Descending, asc=ascending ascending (this is the default for the DBMS)
In the dictionary (disctionary) sort order, a is considered to be the same as a, which is the default behavior for most DBMS

4. Filtering data (where) 4.1 using the WHERE clause

The WHERE clause is given after the table name (FROM clause)
When you use both the order by and the WHERE clause, leave the order by in the Where
Example:
SELECT Prod_id,prod_price,prod_name
From Products
WHERE 3<prod_price and Prod_price<10
ORDER by Prod_price Desc,prod_name;

4.2 WHERE clause action symbol

Operator description
= equals
<> Not equal to
< less than
<= less than or equal to
!< not less than
> Greater than
>= greater than or equal to
! = Not greater than
Between between the specified two fingers
Is NULL for null value

4.2.1 Checking a single value

Cases:
SELECT Prod_name,prod_price
From Products
WHERE pro_price<10;

4.2.2 Mismatch Check

SELECT prod_id,prod_price,prod_name,vend_id
From Products
WHERE vend_id <> ' DLL01 ';
Retrieve all products that are not DLL01 manufactured by the Supplier
　　Tip: If you compare a value to a column of type string, you need to limit the quotation marks.
Used to compare with numeric values without quotation marks

4.2.3 Range Value Check

Between
Cases:
SELECT Prod_id,prod_name,prod_price
From Prudcts
WHERE Prod_price between 3 and 9;
When using between, you must set two values ———— the desired range of low-end and high-side values.
These two values must be separated by the and keyword

4.2.4 Null value Check

When you create a table, the columns of the table can specify whether the columns in it can contain no values, when a column does not contain a value
It is said to contain a null value NULL
Null:
No value (No. value), which contains 0 for the field, and the empty string contains only the spaces that are different

Cases:
SELECT Prod_name
From Products
WHERE Prod_prcie is NULL;
　tip: Individual DBMS-specific operators
NOTE: null and non-matching
When filtering data, be sure to verify that rows with NULL in the filtered column do appear in the return data

The meaning of an and in an SQL statement, or is or satisfies a true

5. Advanced Filtration Method

The high-level filtering method of the WHERE clause and the

5.1 Combining WHERE clauses

SQL allows multiple where clauses, which are used in two ways: AMD and
How to use the OR clause.
Operation symbol (operator)
Keywords used to join or change clauses in a WHERE clause, also known as logical operators
(logical operator)

5.1.1 and operator

The keyword used in the WHERE clause to indicate that rows that satisfy all given criteria are retrieved (
That satisfies multiple lines simultaneously), you can add multiple
Filter criteria, use the AND keyword between each condition
Cases:
SELECT Prod_id,prod_name,prod_price
From Products
WHERE vend_id= ' DLL01 ' and Prod_price <= 4
ORDER by Prod_price DESC;

5.1.2 OR operator

The keyword used in the WHERE clause, and conversely, many DBMS when the first condition is satisfied
Regardless of whether the second condition is satisfied, the corresponding row is retrieved.
Cases:
SELECT prod_id,prod_name,prod_price,vend_id
From Products
WHERE vend_id= ' DLL01 ' OR prod_price between 1 and 4
ORDER by Prod_price DESC, Prod_name;

5.1.3 Order of Evaluation

Example: If a price of 10 and above is required, and the DLL01 or
All products manufactured by BRS01.
SELECT Prod_name, Prod_price
From Products
WHERE vend_id = ' DLL01 ' OR vend_id = ' BRSO1 ' and Prod_price >= 10
ORDER by Prod_price DESC, Prod_name;
+ + output results are:
Prod_name Prod_price
-------------------------------------------------
Bird Bean Bag Toy 3.49
Fish Bean Bag Toy 3.49
Rabbit Bean Bag Toy 3.49
Raggedy Ann 4.99
Inch Teddy Bear 1 1.99

The above result, the returned row has 4 rows with a price less than 10, apparently, the returned row
is not filtered as expected because the and has a higher priority in the evaluation process, the operator is
The characters are incorrectly combined.

The workaround for this problem is to use parentheses to explicitly group the operators
SELECT Prod_name,prod_price
From Products
WHERE vend_id= ' DLL01 ' OR vend_id = ' BRS01 ' and Prod_price >= 10
ORDER by Prod_price DESC, Prod_name;
The output is:
Prod_name Prod_price
----------------------------------------------------
Inch Teddy Bear 11.99

Tips:
Any time you use a WHERE clause with an AND and OR operator,
You should use parentheses to explicitly group the operators. Do not rely too heavily on the default evaluation order.
There is no harm in using parentheses, it can eliminate ambiguity.

5.2 In operator

The in operator specifies the range of conditions, the range, or each condition that can be matched. In takes a group separated by commas,
The legal value that is enclosed in parentheses.

Example: SELECT prod_id,prod_name,prod_price,vend_id
From Products
WHERE Prod_price in (' DLL01 ', ' BRS01 ')
ORDER by Prod_name DESC;

5.3 Not operator

There is only one function in the WHERE clause to negate the keyword of the subsequent condition

Column: SELECT prod_name
From Products
WHERE not vend_id = ' DLL01 '
ORDER by Prod_name;
Match everything other than DLL01.
This example can also be done using the action symbol <>
To give:
SELECT Prod_name
From Prodcuts
WHERE vend_id <> ' DLL01 '
ORDER by Prod_name

6. Filter with wildcard characters
6.1 Liek operator

wildcard character (wildcard)
A portion of the character used to match a value

Search mode (searching pattern)
A search condition consisting of a literal value, a wildcard, or a combination of both

predicate (predicate)
When is the operator not an operator? When she is a predicate. Technically, like.
is a predicate, not an operator. Although the final result is the same, there should be some understanding of this term

Note: Wildcard searches can only be used with text fields (strings), non-text data types
fields cannot be searched by using a wildcard character

6.1.100 semicolon (%) wildcard character

The most commonly used wildcard character is the percent sign (%), and the% percent symbol indicates the number of occurrences of any character
Example:
SELECT Prod_id,prod_name
From Products
WHERE prod_name like ' fish% '
Executing this statement will retrieve any fish that begins with the
The word.
Note NULL:
Wildcard% looks like it can match anything, but there's an exception.
is the null clause where prod_name like '% ' does not match the product
A row with a name of NULL

6.1.2 Underscore (_) wildcard character

The underscore uses the same as%, but it matches only a single character, not multiple characters
That is, add a few underscores to match several characters
Example:
SELECT * FROM Products
WHERE prod_name like ' __ inch Tedy Bear '

6.1.3 square brackets ([]) wildcard characters

The square brackets ([]) wildcard character is used to specify a character set
She must match a character in the specified position (the position of the wildcard)

Description: Collection is not always supported
Not all DBMS support is used to create a collection []
。 Only Microsoft's access and SQL Sever support collections.
Example: Find all contacts whose names start with J or M!
SELECT Cust_contact
From Customers
WHERE cust_contact like ' [jm]% '
ORDER by Cust_contact;
Output:
Cust_contact
--------------
Jim Jones
John Smith
Michelle Green

[JM] matches any one of the characters in parentheses, it can only match a single character
The% wildcard after [JM] matches any number of characters after the first character, returning the desired result

This wildcard can be negated with a prefix character ^ (caret).
Liezi:
Query matches any character except J and M start with any contact name
SELECT Cust_contact
From Customers
WHERE cust_contact like ' [^jm]% '
ORDER by Cust_custact;

Description: A negative collection in Access
Need to use! And not ^

Note: It is also possible to use the not operator to derive similar results.
^ 's only advantage is that syntax can be simplified when multiple where clauses are used
Example:
SELECT Cust_contact
From Customers
WHERE not cust_contact like ' [JM] '
ORDER by Cust_contact;

6.2 Tips for using wildcard characters

Do not use wildcard characters excessively. If other operators can achieve the same purpose
Other operators should be used

When you do need to use wildcards, try not to use them at the beginning of the search pattern.
Put the wildcard character at the beginning, the slowest search.

Pay close attention to the position of the wildcard character. If misplaced, you may not return the data you want!

7. Create a calculated field
7.1 Calculated fields

Data stored in a database table is generally not the format required by the application

Fields (field)
Basically the same as the columns (column), often used interchangeably
However, database columns are generally called columns, and term fields are usually used with calculated fields

From a client (such as an application), the data for the calculated field is returned in the same way as the other columns

tip: The format of the client and server
Many of the transformations and formatting work that can be done within a SQL statement can be
Complete directly within the client. In general, however, the operation is done on the database server
Much faster than on the client.

7.2 Stitching Fields

　　Stitching (concatenate)
Join values Together (append one value to another value) to form a single value

Set up: Create a two-column title
Vendors table contains vendor name and address information, if you want to generate a vendor report
The location of the vendor needs to be listed in the formatted name (location)
This report requires a value, and the data in the table is stored in two columns: Vend_name
and Vend_country. In addition, you need to enclose the vend_country in parentheses.

In the SELECT statement in SQL, you can use a special operator to stitch together two columns.
Depending on the DBMS you are using, this operator can be used with a plus (+) or two vertical bar (| | ) indicates

Description: + with | |
Access and SQL Sever use the + sign. Db2,orcale,postgresql,sqlite and open Office Base using | |
Liezi:
SELECT vend_name+_ ' (' +vend_country+ ') '
From vendors
ORDER by Vend_name;
Output:
(No column name)
---------------------------------------
Bear Enporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Fuball INC (USA)
Jouets et ours (France)

The following is the same statement, but with a | | Grammar
SELECT Vend_name | | ' (' | | | vend_country | | ') '
From vendors
ORDER by Vend_name;
Output as above ~

Here's how to use
Statements to use when MY SQL or mariadb:
SELECT Concat (Vend_name, ' (', Vend_country, ') ')
From vendors
ORDER by Vend_name;

As you can see from the above output, the SELECT statement returns the containing
A column of four elements (calculated field).
Look at the output returned by the SELECT statement above. Two columns combined into one calculated field are filled with spaces,
Many databases (not all) hold text values that are populated with column widths, but actually
You don't need these blanks for the results you want. So format the data for proper return
, these spaces must be removed. This can be used
SQL RTrim () function to complete the
Example:
SELECT RTRIM (vend_name) + ' (' +rtrim (vend_country) + ') '
From vendors
ORDER by Vend_name;
Output:
---------------------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

The RTRIM () function removes all the spaces to the right of the value. Each column is sorted.

　　Description : Trim function
Most DBMS support RTrim () remove the space to the right of the character
LTRIM () Remove the space to the left of the character
TRIM () strip the left and right sides of the string

Using aliases (alias):
Cause: The SELECT statement is a good way to stitch the address fields. But the name of the new computed column is actually not.
It's just a value. R If you look at the results in the SQL query tool only, there's nothing wrong with that.
However, an unnamed column cannot be used in a client application because the client has no way to reference it
and using aliases can make the SQL statement shorter.

SQL supports aliases, which are alternate names for a field or value. Use the keyword as to give
Example:
SELECT RTRIM (vend_name) + ' (' +rtrim (vend_country) ') '
As Vend_title
From vendors
ORDER by Vend_name;

Output:
Vend_title
--------------------
Bear Emporium (USA)
Bears R Us (USA)
Doll House Inc. (USA)
Fun and Games (England)
Furball Inc. (USA)
Jouets et ours (France)

The following is the same statement, but using a | | Syntax
SELECT RTRIM (vend_name) | | ' (' | | RTRIM (vend_country) | | ') '
As Vend_title
From vendors
ORDER by Vend_name;

Here are the statements used in MySQL MARIADB
SELECT Concat (Vend_name, ' (', Vend_country, ') ')
As Vend_title
From vendors
ORDER by Vend_name;

Use aliases to make SQL statements shorter
Example: alias instance of a table
The following SQL statement selects the record accessed by the Novice tutorial. We use the "Websites" and "access_log" tables,
and specify table aliases "W" and "a" for them separately (by using aliases to make SQL shorter):

Instance
SELECT W.name, W.url, A.count, a.date
From Websites as W, Access_log as a
WHERE A.site_id=w.id and W.name= "rookie Tutorial";

Description: As is usually optional
Hint: Other uses of aliases
Aliases also have other uses. Common uses include
When the actual table column name contains an illegal character (such as a space)
Rename it and expand it when the original name is ambiguous or is easily misunderstood.
Aliases are sometimes also referred to as export columns (derived volumn) are the same thing

7.3 Performing arithmetic calculations

Another common use of calculated fields is to perform arithmetic calculations on the retrieved data.
Example:
SELECT prod_id,
Quantity
Item_price
, Quantity*item_price as Expanded_price
From OrderItems
WHERE order_num=20008;
The output is as follows:
prod_id Quantity Item_price Expanded_price
---------- ----------- ------------ -----------------
RGAN0154.990024.9500
BR03511.990059.9500
BNBG01103.490034.9000
BNBG02103.490034.9000
BNBG03103.490034.9000

The basic arithmetic operators are as follows: parentheses can be used to distinguish precedence
Operator description
+ Plus
-Minus
* Multiply
/except

　　tip: How to test a calculation
The SELECT statement provides a good method for testing, verifying functions, and computing. Although
Select is typically used to retrieve data from a table, but omitting the FROM clause is simply
Access and process expressions.

"SQL language" SQL language Basics 01