Basic data query of relational database sql: Sub-query, group query, fuzzy query

Objective

On a relational database commonly used SQL statement syntax is mainly relational database general structure, this paper discusses the relational database query SQL syntax.

Grammar Review

SELECT [ALL|DISTINCT] <目标列表达式>[,<目标列表达式>]…  FROM <表名或视图名>[,<表名或视图名>]…  [WHERE <条件表达式>]  [GROUP BY <列名> [HAVING <条件表达式>]]  [ORDER BY <列名> [ASC|DESC]…]

The order of SQL query statements: SELECT, from, WHERE, GROUP by, having, order by. SELECT, from is required, and the HAVING clause can only be used with GROUP by.

Preparatory work

1. Creating databases and data tables
2. Inserting basic data
3. This article takes SQL Server as an example to introduce

--Create student TablesCREATE TABLEStudents (Id int  not NULL PRIMARY KEY, Namevarchar( -) not NULL,Class varchar( the) not NULL, Gendervarchar(Ten)NULL, ageint NULL, Phonevarchar( the)NULL, [Address]varchar( -)NULL)--Create a curriculumCREATE TABLECourses (Id int  not NULL PRIMARY KEYIDENTITY (1,1), Namevarchar( -) not NULL);--Create a score tableCREATE TABLEScores (Id int PRIMARY KEYIDENTITY (1,1), SIdint  not NULLCidint  not NULL, Gradesdecimal(5,2) not NULL, ispassed bit not NULL);--Insert Student table base dataINSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016001,' Xiao Ming ',' Class One ',' man ', -,' 18817716611 ',' Beijing ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016002,' Dragons ',' Class One ',' man ', +,' 18817716622 ',' Tianjin ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016003,' Xiao Wang ',' Class two ',' man ', -,' 18817716633 ',' Beijing ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016004,' Tingting ',' Class One ',' Woman ', -,' 18817716644 ',' Jinan ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016005,' Zhang San ',' Class One ',' man ', +,' 18817716655 ',' Beijing ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016006,' Xiao Zhao ',' Class One ',' man ', -,' 18817716666 ',' Beijing ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016007,' Lili ',' Class two ',' Woman ', -,' 18817716677 ',' Beijing ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016008,' Floral ',' Class One ',' Woman ', +,' 18817716688 ',' Shenyang ');INSERT  intoStudents (Id, Name,Class, Gender,age,phone,[address])VALUES(2016009,' quiet ',' Class two ',' Woman ', -,' 18817716699 ',' Beijing ');--Insert curriculum base dataINSERT  intoCourses (Name)VALUES(' Chinese ');INSERT  intoCourses (Name)VALUES(' Math ');INSERT  intoCourses (Name)VALUES(' English ');--Insert score table base dataINSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016001,1, -,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016001,2, -,0);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016001,3, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016002,1, -,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016002,2, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016002,3, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016003,1, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016003,3,102,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016004,1, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016004,2, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016004,3, -,0);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016005,1, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016005,2, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016005,3, the,0);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016006,2, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016006,3, -,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016007,1, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016007,2, $,0);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016007,3, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016008,1, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016008,3, the,0);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016009,1, the,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016009,2,104,1);INSERT  intoScores (sid,cid,grades,ispassed)VALUES(2016009,3, the,1);

Simple query

Simple queries only need SELECT , FROM and WHERE 3 keywords can be implemented.

SELECT * FROM Students;SELECT * FROM Students WHERE Class='一班';SELECT * FROM Students WHERE Class='一班' AND Age = 20;

Aliases/renaming

Grammar

SELECT 字段名1 [ASASFROM <表名>

As can be omitted
A single/double quote is required when the alias contains a non-letter and an underscore or a keyword

Example

SELECTIdAS'学号''姓名',ClassFROM Students;

subquery (nested query)

A subquery, also called a nested query, refers SELECT to a query statement that can be embedded in another SELECT query statement. Multilevel nesting is allowed in SQL, and subqueries are very much used in practice.
connection Queries : queries involving two or more tables are queried for the connection.

--查询二班学生成绩SELECTFROMWHEREIN(SELECTIdFROMWHERE Class='二班')

Aggregate function Query

aggregate function : A function that returns a single value as a collection of values for input.
SQL has predefined 5 aggregate functions: AVG (average), min (minimum), Max (max), sum (sum), count (count).
Specific databases will also be predefined for some other commonly used functions, such as String aggregation functions, time aggregation functions ....

SELECTAVG(Age),MAX(Age),MIN(Age),SUM(Age),COUNT(IdFROM Students;

Group queries

Use GROUP BY clauses to group queries
Note: When grouping queries, follow the GROUP BY list of all query fields after the clause

--根据班级分组查询各班平均年龄、最大 年龄、最小年龄、年龄总和、班级人数SELECTClass,AVG(Age),MAX(Age),MIN(Age),SUM(Age),COUNT(IdFROMGROUPBYClass;

HAVING clause

If you need to limit the data before grouping, you can use the HAVING clause
The HAVING clause can only be used with GROUP by

--根据班级分组查询各班平均年龄并且班级人数大于3人SELECTClass,AVGFROMGROUPBYClassHAVINGCOUNT(Id)>3;

The difference between a HAVING clause and where

Where statement before the group BY statement, SQL calculates the where statement before grouping;
Having a statement after the group BY statement, SQL calculates the having statement after grouping.

Fuzzy query

Grammar

SELECTFROMWHERELIKE'<通配符>'

Fuzzy queries are implemented by keyword LIKE and wildcard characters.
_: Any single character (a ' _ ' matches only one character, multiple characters using multiple _)
%: Any string that contains 0 or more characters (matches any content)
[]: Specifies the range of characters (matches only characters within [])
[^]: Characters not in the specified range (matches only characters except [])
Wildcard characters in SQL can be used in a mix

• Single character match

  SELECTFROMWHERELIKE'小_';SELECTFROMWHERELIKE'188177166__';

• Any character match

  SELECTFROMWHERELIKE'188177166__';SELECTFROMWHERELIKE'%';SELECTFROM Students;

  These three SQL statements query the same results only for this table, because the blind cat encountered dead rats. (Less data, just the result of the query is the whole content)

• In-scope queries

  -- 查询所有手机号码结尾两位包含1和2的信息SELECTFROMWHERELIKE'188177166[12][12]';

• Do not query in scope

-- 查询所有手机号码结尾两位不包含1、3、5、7的信息SELECTFROMWHERELIKE'188177166[^1357][^1357]';

About SQL query is here to end, and later updates related content.
If you feel a problem, welcome to discuss it with you.

Basic data query of relational database sql: Sub-query, group query, fuzzy query