11.16 SQL学习

Sshiyitong

已于 2022-11-16 15:52:01 修改

阅读量100

点赞数

文章标签： sql 学习数据库

于 2022-11-16 15:51:46 首次发布

本文链接：https://blog.csdn.net/m0_68575886/article/details/127880560

版权

#11.15

二十四、SQL 函数

SQL 拥有很多可用于计数和计算的内建函数。

SQL Aggregate 函数

SQL Aggregate 函数计算从列中取得的值，返回一个单一的值。

有用的 Aggregate 函数：

AVG() - 返回平均值
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
LAST() - 返回最后一个记录的值
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

SQL Scalar 函数

SQL Scalar 函数基于输入值，返回一个单一的值。

有用的 Scalar 函数：

UCASE() - 将某个字段转换为大写
LCASE() - 将某个字段转换为小写
MID() - 从某个文本字段提取字符，MySql 中使用
SubString(字段，1，end) - 从某个文本字段提取字符
LEN() - 返回某个文本字段的长度
ROUND() - 对某个数值字段进行指定小数位数的四舍五入
NOW() - 返回当前的系统日期和时间
FORMAT() - 格式化某个字段的显示方式

1.SQL AVG() 函数

AVG() 函数

AVG() 函数返回数值列的平均值

语法

SELECT AVG(column_name) FROM table_name;

以 "access_log" 表为例

SQL AVG() 实例

eg.从 "access_log" 表的 "count" 列获取平均值

SELECT AVG(count) AS Countaverage FROM access_log;

运行结果

eg.选择访问量高于平均访问量的 "site_id" 和 "count"

select site_id,count
FROM access_log
where count >(select AVG(count) AS Countaverage FROM access_log);

#注意在上面运行的结果Countaverage只是临时的，无法被检索。

运行结果

2. SQL COUNT() 函数

COUNT() 函数返回匹配指定条件的行数。

SQL COUNT(column_name) 语法

COUNT(column_name) 函数返回指定列的值的数目（NULL 不计入）：

SELECT COUNT(column_name) FROM table_name;

SQL COUNT(*) 语法

COUNT(*) 函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;

SQL COUNT(DISTINCT column_name) 语法

COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目

SELECT COUNT(DISTINCT column_name) FROM table_name;

注释：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。

"access_log" 表

SQL COUNT(column_name) 实例

eg. 计算 "access_log" 表中 "site_id"=3 的总访问量：

SELECT count(count) as nums from access_log
where site_id = 3;

运行结果

SQL COUNT(*) 实例

eg.计算 "access_log" 表中总记录数：

SELECT COUNT(*) AS nums FROM access_log;

运行结果

SQL COUNT(DISTINCT column_name) 实例

eg. 计算 "access_log" 表中不同 site_id 的记录数

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

运行结果

3.FIRST() 函数

FIRST() 函数返回指定的列中第一个记录的值。

SQL FIRST() 语法

SELECT FIRST(column_name) FROM table_name;

只有 MS Access 支持 FIRST() 函数。

MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;

eg. 选取 "Websites" 表的 "name" 列中第一个记录的值：

select name from websites
order by id ASC
limit 1;

运行结果

4. SQL LAST() 函数

LAST() 函数返回指定的列中最后一个记录的值。

SQL LAST() 语法

SELECT LAST(column_name) FROM table_name;

注释：只有 MS Access 支持 LAST() 函数。

MySQL 语法

SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;

eg.选取 "Websites" 表的 "name" 列中最后一个记录的值

select name from websites
order by id DESC
limit 1;

运行结果

5. SQL MAX() 函数

MAX() 函数返回指定列的最大值。

SQL MAX() 语法

SELECT MAX(column_name) FROM table_name;

eg.从 "Websites" 表的 "alexa" 列获取最大值：

select max(alexa) as nums from websites;

运行结果

6. MIN() 函数

MIN() 函数返回指定列的最小值。

SQL MIN() 语法

SELECT MIN(column_name) FROM table_name;

eg.从 "Websites" 表的 "alexa" 列获取最小值：

select min(alexa) as nums from websites;

运行结果

7. SQL SUM() 函数

SUM() 函数返回数值列的总数。

SQL SUM() 语法

SELECT SUM(column_name) FROM table_name;

eg. 查找 "access_log" 表的 "count" 字段的总数

SELECT SUM(count) AS nums FROM access_log;

运行结果

8.SQL GROUP BY 语句

GROUP BY 语句用于结合聚合函数，根据一个或多个列对结果集进行分组。

SQL GROUP BY 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;

GROUP BY 简单应用

eg. 统计 access_log 各个 site_id 的访问量

SELECT site_id, SUM(access_log.count) AS nums
FROM access_log GROUP BY site_id;

运行结果

SQL GROUP BY 多表连接

eg. 统计有记录的网站的记录数量

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log
LEFT JOIN Websites
ON access_log.site_id=Websites.id
GROUP BY Websites.name;

运行结果

10. SQL HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与聚合函数一起使用。

HAVING 子句可以让我们筛选分组后的各组数据。

SQL HAVING 语法

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;

eg.查找总访问量大于 200 的网站

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

运行结果

eg. 查找总访问量大于 200 的网站，并且 alexa 排名小于 200

SELECT websites.name,websites.url,websites.alexa,sum(access_log.count) AS nums
from (access_log inner join websites on access_log.site_id = websites.id)
where websites.alexa<200
group by websites.name
having sum(count)>200;

运行结果

####

where 和having之后都是筛选条件，但是有区别的：

1.where在group by前， having在group by 之后

2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

11. SQL EXISTS 运算符

EXISTS 运算符用于判断查询子句是否有记录，如果有一条或多条记录存在返回 True，否则返回 False。

SQL EXISTS 语法

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SQL EXISTS 实例

eg.查找总访问量(count 字段)大于 200 的网站是否存在。

select websites.name,websites.url from websites
where exists
(select count from access_log where websites.id = access_log.site_id and count>200);

运行结果

eg. EXISTS 可以与 NOT 一同使用，查找出不符合查询语句的记录

select websites.name,websites.url from websites
where not exists
(select count from access_log where websites.id = access_log.site_id and count>200);

运行结果

12. SQL UCASE() 函数

UCASE() 函数把字段的值转换为大写。

SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name;

eg.从 "Websites" 表中选取 "name" 和 "url" 列，并把 "name" 列的值转换为大写：

SELECT UCASE(name) AS site_title, url
FROM Websites

运行结果

13. SQL LCASE() 函数

LCASE() 函数把字段的值转换为小写。

SQL LCASE() 语法

select Ucase(name) as site_tile,url from websites;

eg.从 "Websites" 表中选取 "name" 和 "url" 列，并把 "name" 列的值转换为小写

select Lcase(name) as site_tile,url from websites;

运行结果

14.MID() 函数

MID() 函数用于从文本字段中提取字符。

SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name;

参数	描述
column_name	必需。要提取字符的字段。
start	必需。规定开始位置（起始值是 1）。
length	可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。

eg. 从 "Websites" 表的 "name" 列中提取前 4 个字符

select mid(name,1,4) as shorttitle from websites;

运行结果

15. LEN() 函数

LEN() 函数返回文本字段中值的长度

SQL LEN() 语法

SELECT LEN(column_name) FROM table_name;

MySQL 中函数为 LENGTH():

SELECT LENGTH(column_name) FROM table_name;

eg.从 "Websites" 表中选取 "name" 和 "url" 列中值的长度：

select length(name),length(url) from websites;

运行结果

16.ROUND() 函数

ROUND() 函数用于把数值字段舍入为指定的小数位数

SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM TABLE_NAME;

参数	描述
column_name	必需。要舍入的字段。
decimals	可选。规定要返回的小数位数。

SQL ROUND() 实例

ROUND(X)：返回参数X的四舍五入的一个整数。

SELECT ROUND(-1.23);

运行结果

SELECT ROUND(-1.58);

运行结果

SELECT ROUND(1.58);

运行结果

ROUND(X,D)：返回参数X的四舍五入的有 D 位小数的一个数字。如果D为0，结果将没有小数点或小数部分。

SELECT ROUND(1.298, 1);

运行结果

SELECT ROUND(1.298, 0);

运行结果

注意：ROUND 返回值被变换为一个BIGINT!

17. NOW() 函数

NOW() 函数返回当前系统的日期和时间。

SQL NOW() 语法

SELECT NOW() FROM table_name;

eg.从 "Websites" 表中选取 name，url，及当天日期

select name,url,NOW() as date
from websites;

运行结果

18. FORMAT() 函数

FORMAT() 函数用于对字段的显示进行格式化。

SQL FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name;

参数	描述
column_name	必需。要格式化的字段。
format	必需。规定格式。

eg.从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;

FROM Websites;

运行结果

快速参考

SQL 语句	语法
AND / OR	SELECT column_name(s) FROM table_name WHERE condition AND\|OR condition
ALTER TABLE	ALTER TABLE table_name ADD column_name datatype or ALTER TABLE table_name DROP COLUMN column_name
AS (alias)	SELECT column_name AS column_alias FROM table_name or SELECT column_name FROM table_name AS table_alias
BETWEEN	SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2
CREATE DATABASE	CREATE DATABASE database_name
CREATE TABLE	CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name2 data_type, ... )
CREATE INDEX	CREATE INDEX index_name ON table_name (column_name) or CREATE UNIQUE INDEX index_name ON table_name (column_name)
CREATE VIEW	CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
DELETE	DELETE FROM table_name WHERE some_column=some_value or DELETE FROM table_name (Note: Deletes the entire table!!) DELETE * FROM table_name (Note: Deletes the entire table!!)
DROP DATABASE	DROP DATABASE database_name
DROP INDEX	DROP INDEX table_name.index_name (SQL Server) DROP INDEX index_name ON table_name (MS Access) DROP INDEX index_name (DB2/Oracle) ALTER TABLE table_name DROP INDEX index_name (MySQL)
DROP TABLE	DROP TABLE table_name
GROUP BY	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name
HAVING	SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value
IN	SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,..)
INSERT INTO	INSERT INTO table_name VALUES (value1, value2, value3,....) or INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,....)
INNER JOIN	SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LEFT JOIN	SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
RIGHT JOIN	SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2 ON table_name1.column_name=table_name2.column_name
FULL JOIN	SELECT column_name(s) FROM table_name1 FULL JOIN table_name2 ON table_name1.column_name=table_name2.column_name
LIKE	SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern
ORDER BY	SELECT column_name(s) FROM table_name ORDER BY column_name [ASC\|DESC]
SELECT	SELECT column_name(s) FROM table_name
SELECT *	SELECT * FROM table_name
SELECT DISTINCT	SELECT DISTINCT column_name(s) FROM table_name
SELECT INTO	SELECT * INTO new_table_name [IN externaldatabase] FROM old_table_name or SELECT column_name(s) INTO new_table_name [IN externaldatabase] FROM old_table_name
SELECT TOP	SELECT TOP number\|percent column_name(s) FROM table_name
TRUNCATE TABLE	TRUNCATE TABLE table_name
UNION	SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
UNION ALL	SELECT column_name(s) FROM table_name1 UNION ALL SELECT column_name(s) FROM table_name2
UPDATE	UPDATE table_name SET column1=value, column2=value,... WHERE some_column=some_value
WHERE	SELECT column_name(s) FROM table_name WHERE column_name operator value