sql函数
In day to day usage of SQL, there is a time when we need to manipulate data based on the type of data. For example, in a table which comprises prices we need to get the average or sum of all the prices. In these cases, SQL functions come very handily as they provide a built-in mechanism for such functions.
在SQL的日常使用中,有时需要根据数据类型来操纵数据。 例如,在包含价格的表中,我们需要获取所有价格的平均值或总和。 在这些情况下,SQL函数非常方便,因为它们为此类函数提供了内置机制。
SQL函数 (SQL Functions)
SQL functions are the set of built-in functions to perform a calculation over data that are stored in the table. Let us have a look at the list of most useful SQL functions.
SQL函数是一组内置函数,用于对表中存储的数据执行计算。 让我们看一下最有用SQL函数列表。
- SQL Count – returns the count of rows in a database table. SQL Count –返回数据库表中的行数。
- SQL Max – returns the maximum value from a database table SQL Max –从数据库表返回最大值
- SQL Min – returns the minimum value from a database table SQL Min –从数据库表返回最小值
- SQL Avg – provides the average of a certain table column value SQL Avg –提供特定表列值的平均值
- SQL Sum – provides the sun of a certain table column value SQL Sum –提供某个表列值的最大值
- SQL sqrt – returns the square root of a number. SQL sqrt –返回数字的平方根。
- SQL rand – used to generate a random number using SQL command. SQL rand –用于使用SQL命令生成随机数。
- SQL concat – used for concatenating strings in a SQL command. SQL concat –用于串联SQL命令中的字符串。
- SQL Ucase – converts a field to upper case. SQL Ucase –将字段转换为大写。
- SQL Lcase – converts a field to lower case. SQL Lcase –将字段转换为小写。
Now let’s try to understand all the above-mentioned SQL functions one by one in more detail.
现在,让我们尝试更详细地理解所有上述SQL函数。
We will consider the following table to understand the functions in a better way.
我们将考虑下表以更好地理解功能。
Library
图书馆
IdLibrary | BookTitle | BookQuantity | Author | BookPrice |
---|---|---|---|---|
1 | The Chamber of Secrets | 10 | J K Rowling | 20.99 |
2 | One night at the call center | 13 | Chetan Bhagat | 100.99 |
3 | The God of Small things | 11 | Arundhati Roy | 120.99 |
4 | War and Peace | 5 | Leo Tolstoy | 80.00 |
IdLibrary | 书名 | 图书数量 | 作者 | 书价 |
---|---|---|---|---|
1个 | 密室 | 10 | JK罗琳 | 20.99 |
2 | 在呼叫中心过夜 | 13 | 切坦·巴加特 | 100.99 |
3 | 小物之神 | 11 | 阿伦达蒂·罗伊(Arundhati Roy) | 120.99 |
4 | 战争与和平 | 5 | 列夫·托尔斯泰 | 80.00 |
SQL计数 (SQL Count)
Select count(*) FROM library
Output:
输出:
Count(*) |
---|
4 |
计数(*) |
---|
4 |
![SQL Count example](https://i-blog.csdnimg.cn/blog_migrate/18bc2e6480eece6f3b4a63ec22c32377.png)
SQL Count
SQL计数
In the query above, count(*) returns the total count of the number of rows available in the table.
在上面的查询中,count(*)返回表中可用行数的总数。
SQL最大 (SQL Max)
Select max(BookQuantity) from library
Output:
输出:
max(BookQuantity) |
---|
13 |
max(BookQuantity) |
---|
13 |
![SQL Max example](https://i-blog.csdnimg.cn/blog_migrate/ac36a0ee58f4abdc504dea07d3d240de.png)
SQL Max example
SQL Max示例
In the query above, max(BookQuantity) returns the max value from the column BookQuantity of Library table.
在上面的查询中,max(BookQuantity)从Library表的BookQuantity列返回最大值。
SQL最小值 (SQL Min)
Select min(BookQuantity) from library
Output:
输出:
min(BookQuantity) |
---|
5 |
min(BookQuantity) |
---|
5 |
![SQL Min example](https://i-blog.csdnimg.cn/blog_migrate/f017102e7f66c61f63b549e698fdb500.png)
SQL Min example
SQL Min示例
In the query above, min(BookQuantity) returns the min value from the column BookQuantity of Library table.
在上面的查询中,min(BookQuantity)返回“库”表的BookQuantity列中的最小值。
SQL平均 (SQL Avg)
Select avg(BookQuantity) from library
Output:
输出:
avg(BookQuantity) |
---|
9.7500 |
平均(BookQuantity) |
---|
9.7500 |
![SQL Avg example](https://i-blog.csdnimg.cn/blog_migrate/68ad1711a2239d9e2f3b3affc10a36d8.png)
SQL Avg example
SQL平均示例
In the query above, avg(BookQuantity) returns the average value from the column BookQuantity of Library table.
在上面的查询中,avg(BookQuantity)从“库”表的BookQuantity列中返回平均值。
SQL总和 (SQL Sum)
Select sum(BookQuantity) from library
Output:
输出:
sum(BookQuantity) |
---|
39 |
总和(BookQuantity) |
---|
39 |
![SQL Sum example](https://i-blog.csdnimg.cn/blog_migrate/f064e2b7ea7f607716396b41c1e17e70.png)
SQL Sum example
SQL Sum示例
In the query above, sum(BookQuantity) returns the sum of all the values from the column BookQuantity of Library table.
在上面的查询中,sum(BookQuantity)返回Library表的BookQuantity列中所有值的总和。
SQL Sqrt (SQL Sqrt)
Select sqrt(BookQuantity) from library
Output:
输出:
sqrt(BookQuantity) |
---|
3.1622 |
3.6055 |
3.3166 |
2.2306 |
sqrt(BookQuantity) |
---|
3.1622 |
3.6055 |
3.3166 |
2.2306 |
![SQL square root example](https://i-blog.csdnimg.cn/blog_migrate/f05cc8a3704479752bec5300090f6463.png)
SQL square root example
SQL平方根示例
In the query above, sqrt(BookQuantity) returns the square root for all the values from the column BookQuantity of Library table.
在上面的查询中,sqrt(BookQuantity)返回库表BookQuantity列中所有值的平方根。
SQL Rand (SQL Rand)
Select rand(BookQuantity) from library
Output:
输出:
rand(BookQuantity) |
---|
0.6570 |
0.4076 |
0.9072 |
0.4061 |
兰德(BookQuantity) |
---|
0.6570 |
0.4076 |
0.9072 |
0.4061 |
![SQL rand example](https://cdn.journaldev.com/wp-content/uploads/2018/12/SQL_rand.jpg)
SQL rand example
SQL rand示例
In the query above, rand(BookQuantity) returns the random number for values corresponding to the rows for column BookQuantity of Library table.
在上面的查询中,rand(BookQuantity)返回与库表BookQuantity列的行相对应的值的随机数。
SQL Concat (SQL Concat)
Select concat(BookTitle,'-',Author)from library
Output:
输出:
concat(BookTitle,’-‘,Author) |
---|
The Chamber of Secrets-J K Rowling |
One night at the call center-Chetan Bhagat |
The God of Small things-Arundhati Roy |
War and Peace-Leo Tolstoy |
concat(BookTitle,'-',作者) |
---|
密室-J·K·罗琳 |
在呼叫中心过夜-Chetan Bhagat |
小物之神-阿伦达蒂·罗伊(Arundhati Roy) |
战争与和平-列夫·托尔斯泰 |
![SQL concat example](https://i-blog.csdnimg.cn/blog_migrate/19efa7b00c77228ea1577d67b195e4b7.png)
SQL concat example
SQL concat示例
In the query above, concat(BookTitle,’-‘,Author) returns the concatenated values corresponding to the rows for column BookTitle and Author of Library table.
在上面的查询中,concat(BookTitle,'-',Author)返回与BookTitle列和Library表的Author行对应的串联值。
SQL Ucase (SQL Ucase)
Select ucase(Author)from library
Output:
输出:
ucase(Author) |
---|
J K ROWLING |
CHETAN BHAGAT |
ARUNDHATI ROY |
LEO TOLSTOY |
ucase(作者) |
---|
JK罗琳 |
切坦·巴加特 |
亚伦蒂·罗伊 |
狮子座玩具 |
![SQL ucase example](https://i-blog.csdnimg.cn/blog_migrate/e02f4abda219a68403dd489d2fa8eeee.png)
SQL ucase example
SQL ucase示例
In the query above, ucase(Author) returns the values in upper case for Author column of Library table.
在上面的查询中,ucase(Author)以大写形式返回Library表的Author列的值。
SQL大写 (SQL Lcase)
Select lcase(Author)from library
Output:
输出:
lcase(Author) |
---|
j k rowling |
chetan bhagat |
arundhati roy |
leo tolstoy |
lcase(作者) |
---|
JK罗琳 |
奇坦·巴加特 |
阿伦达蒂罗伊 |
列夫·托尔斯泰 |
![SQL lcase example](https://cdn.journaldev.com/wp-content/uploads/2018/12/SQL_lcase.jpg)
SQL lcase example
SQL lcase示例
In the query above, lcase(Author) returns the values in lower case for Author column of Library table.
在上面的查询中,lcase(Author)返回库表的Author列的小写值。
sql函数