sql表达式_SQL表达式

sql表达式

SQL expression is a combination of one or more values, operators and SQL functions that results in to a value. These SQL EXPRESSIONs are similar to a formula and they are written in query language. You can also use them to query the database for a specific set of data.

SQL表达式是一个或多个值， 运算符以及产生一个值SQL函数的组合。 这些SQL EXPRESSION与公式相似，并且以查询语言编写。 您还可以使用它们来查询数据库以获取一组特定的数据。

SQL表达式 (SQL Expressions)

SQL expression can be classified into following categories.

SQL表达式可以分为以下几类。

1. Boolean
   布尔型
2. Numeric
   数字
3. Date
   日期

Let us try to understand the types of expression in detail with sample programs.

让我们尝试通过示例程序来详细了解表达式的类型。

SQL布尔表达式 (SQL Boolean Expression)

SQL Boolean Expression fetches data based on the condition that is mentioned as part of the SQL query. It should fetch just single value when the query is executed. Its syntax is given below.

SQL布尔表达式根据在SQL查询中提到的条件获取数据。 执行查询时，它应该仅获取单个值。 其语法如下。

SELECT column 
FROM table_name 
WHERE SINGLE_VALUE_MATCHING_EXPRESSION;

Let us try to understand Boolean expression using a sample table with some data.

让我们尝试使用带有一些数据的样本表来理解布尔表达式。

EmpId	EmpName	EmpAge	EmpSalary
1	John	32	2000
2	Smith	25	2500
3	Henry	29	3000

EmpId	EmpName	EmpAge	薪酬
1个	约翰	32	2000
2	史密斯	25	2500
3	亨利	29	3000

We will consider the Employee table mentioned above as an example.

我们将以上面提到的Employee表为例。

Example Query:

查询示例：

SELECT EmpName FROM Employee WHERE EmpId = 1;

The query above will result in single value which is “John” for our query. SQL Boolean expressions should be used with primary keys to make suer you always get only one results. This is required incase of nested SQL select queries.

上面的查询将产生单个值，对于我们的查询为“ John”。 SQL布尔表达式应与主键一起使用，以使您始终只能获得一个结果。 如果嵌套SQL选择查询是必需的。

select posts.title, posts.author_id from posts, authors 
where posts.author_id = authors.id and 
posts.author_id = (select id from authors where name = 'Pankaj');

Above query will return all the posts name and id where author name is Pankaj. Here nested SQL query should always return only one row otherwise you will get error message as Subquery returns more than 1 row.

上面的查询将返回所有帖子名称和ID，其中作者名为Pankaj。 在此，嵌套SQL查询应始终仅返回一行，否则，由于Subquery returns more than 1 row您将收到错误消息。

SQL数字表达式 (SQL Numeric Expression)

SQL Numeric Expression is used for performing mathematical operation in SQL query. Its syntax is as follows:

SQL数值表达式用于在SQL查询中执行数学运算。 其语法如下：

SELECT NUMERICAL_EXPRESSION as OPERATION_NAME
FROM table_name

NUMERICAL_EXPRESSION is the mathematical formula for function which will be used in the SQL query.

NUMERICAL_EXPRESSION是将在SQL查询中使用的函数的数学公式。

Let’s try to understand Numeric expression using an example.

让我们尝试通过一个例子来理解数值表达式。

SELECT count(*) FROM Employee; -- 3

The query above will result as 3 because the COUNT(*) function will provide the total count of the rows based on the condition in WHERE clause. For example select count(*) from employee where empsalary > 2400; will return 2.

上面的查询结果为3，因为COUNT（*）函数将根据WHERE子句中的条件提供行的总数。 例如， select count(*) from employee where empsalary > 2400; 将返回2。

There are other functions also like sum(), avg(), min(), max() etc. These functions are used for mathematical operations. Let’s see some more example code snippets for SQL numeric expressions.

还有其他函数，例如sum（），avg（），min（），max（）等。这些函数用于数学运算。 让我们再看一些用于SQL数值表达式的示例代码片段。

SELECT sum(EmpSalary) as "Salary" FROM Employee; -- 7500
select min(EmpSalary) from Employee; -- 2000
select max(EmpSalary) from Employee; -- 3000
select sum(EmpSalary) from Employee; -- 7500
select avg(EmpSalary) from Employee; -- 2500.0000

SQL日期表达式 (SQL Date Expression)

SQL Date Expression results in datetime value.

SQL日期表达式产生datetime值。

Let’s try to understand Date expression with some sql statements.

让我们尝试通过一些sql语句了解Date表达式。

SELECT CURRENT_TIMESTAMP; -- 2018-01-20 10:32:37

-- For MYQSL
SELECT now(); -- 2018-01-20 10:32:57

-- For SQL Server
SELECT GetDate();

-- For Oracle DB
select sysdate from Dual; -- 20-JAN-18

The above queries will result in current time of the system. Note that every relational database vendor has specific methods to get current date and timestamp values, so there is a slight chance that one function in a database doesn’t work in another database.

以上查询将导致系统当前时间。 请注意，每个关系数据库供应商都有获取当前日期和时间戳记值的特定方法，因此数据库中的一个功能在另一数据库中不起作用的可能性很小。

That’s all for SQL expressions, we will use these a lot in later sql tutorials and programs.

SQL表达式就这些了，我们将在以后SQL教程和程序中大量使用这些表达式。

Further Reading: SQL Data Types.

进一步阅读： SQL数据类型 。

Reference: Oracle Documentation

参考： Oracle文档

翻译自: https://www.journaldev.com/18233/sql-expressions

sql表达式