动态sql语句基本语法_SQL命令：基本语法，语句和查询的示例介绍

动态sql语句基本语法

This guide provides a basic, high level description of the syntax for SQL statements.

本指南对SQL语句的语法提供了基本的高级描述。

SQL is an international standard (ISO), but you will find many differences between implementations. This guide uses MySQL as an example. If you use one of the many other Relational Database Managers (DBMS) you’ll need to check the manual for that DBMS if needed.

SQL是国际标准(ISO)，但是您会发现实现之间存在许多差异。 本指南以MySQL为例。 如果您使用许多其他关系数据库管理器(DBMS)之一，则需要查看该DBMS的手册。

我们将涵盖的内容 (What we will cover)

• Use (sets what database the statement will use)
  使用(设置语句将使用的数据库)
• Select and From clauses
  Select和From子句
• Where Clause (and / or, IN, Between, LIKE)
  条款(和/或IN，之间，LIKE)
• Order By (ASC, DESC)
  订购依据(ASC，DESC)
• Group by and Having
  分组并具有

如何使用 (How to use this)

This is used to select the database containing the tables for your SQL statements:

这用于选择包含SQL语句表的数据库：

use fcc_sql_guides_database; -- select the guide sample database

Select和From子句 (Select and From clauses)

The Select part is normally used to determine which columns of the data you want to show in the results. There are also options you can use to show data that is not a table column.

选择部分通常用于确定要在结果中显示数据的哪些列。 还有一些选项可用于显示不是表列的数据。

This example shows two columns selected from the “student” table, and two calculated columns. The first of the calculated columns is a meaningless number, and the other is the system date.

本示例显示了从“学生”表中选择的两列以及两个计算出的列。 计算的第一列是无意义的数字，另一个是系统日期。

select studentID, FullName, 3+2 as five, now() as currentDate
    from student;

Where子句(和/或IN，Between和LIKE) (Where Clause (and / or, IN, Between and LIKE))

The WHERE clause is used to limit the number of rows returned.

WHERE子句用于限制返回的行数。

In this case all five of these will be used is a somewhat ridiculous Where clause.

在这种情况下，将使用所有这五个都是有点荒谬的Where子句。

Compare this result to the above SQL statement to follow this logic.

将此结果与上面SQL语句进行比较，以遵循此逻辑。

Rows will be presented that:

将显示以下行：

• Have Student IDs between 1 and 5 (inclusive)
  学生ID在1到5(含)之间
• or studentID = 8
  或studentID = 8
• or have “Maxmimo” in the name
  或名称中带有“ Maxmimo”

The following example is similar, but it further specifies that if any of the students have certain SAT scores (1000, 1400), they will not be presented:

以下示例是类似的示例，但它进一步指定如果任何学生的SAT分数达到一定(1000、1400)，则不会显示这些分数：

select studentID, FullName, sat_score, recordUpdated
    from student
    where (
		studentID between 1 and 5
		or studentID = 8
        or FullName like '%Maximo%'
		)
		and sat_score NOT in (1000, 1400);

订购依据(ASC，DESC) (Order By (ASC, DESC))

Order By gives us a way to sort the result set by one or more of the items in the SELECT section. Here is the same list as above, but sorted by the students Full Name. The default sort order is ascending (ASC), but to sort in the opposite order (descending) you use DESC, as in the example below:

Order By提供了一种方法，可以按SELECT部分​​中的一个或多个项目对结果集进行排序。 这是与上述相同的列表，但按学生的全名排序。 默认的排序顺序是升序(ASC)，但是要使用相反的顺序(降序)，请使用DESC，如下例所示：

select studentID, FullName, sat_score
    from student
    where (studentID between 1 and 5 -- inclusive
		or studentID = 8
        or FullName like '%Maximo%')
		and sat_score NOT in (1000, 1400)
	order by FullName DESC;

分组依据 (Group By and Having)

Group By gives us a way to combine rows and aggregate data. The Having clause is like the above Where clause, except that it acts on the grouped data.

分组依据为我们提供了一种合并行和汇总数据的方法。 Haven子句与上面的Where子句类似，不同之处在于它对分组的数据起作用。

This data is from the campaign contributions data we’ve been using in some of these guides.

此数据来自我们在其中一些指南中一直使用的广告系列贡献数据。

This SQL statement answers the question: “which candidates recieved the largest number of contributions (not $ amount, but count (*)) in 2016, but only those who had more than 80 contributions?”

该SQL语句回答了以下问题：“哪些候选人在2016年收到的捐款数量最多(不是$金额，而是计数(*))，但只有捐款额超过80的候选人？”

Ordering this data set in a descending (DESC) order places the candidates with the largest number of contributions at the top of the list.

按降序(DESC)排序此数据集，将贡献最大的候选者放在列表的顶部。

select Candidate, Election_year, sum(Total_$), count(*)
    from combined_party_data
    where Election_year = 2016
    group by Candidate, Election_year
    having count(*) > 80
    order by count(*) DESC;

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide. I hope this at least gives you enough to get started. Please see the manual for your database manager and have fun trying different options yourself.

与所有这些SQL事物一样，它们比本入门指南中的内容要多得多。 我希望这至少能给您足够的入门。 请参阅数据库管理员的手册，并尝试自己尝试其他选项，这很有趣。

翻译自: https://www.freecodecamp.org/news/sql-commands-syntax-statements-queries/

动态sql语句基本语法