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
-- 查询所有手机号码结尾两位不包含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