SQL 术语/语法/基本操作-必知必会

一、了解SQL

1.1 术语解释

术语	解释	备注
数据库	以某种有组织的方式存储的数据集合	不要把数据库和数据库管理软件（DBMS）混淆
表	表是和种结构化的文件，用于存储某种特定类型的数据	表名在一个数据库中需要唯一
模式	关于数据库和表的布局及特性的信息
列	表中的一个字段	所有表都是由一个或多个列组成的
数据类型	定义了列可以存储哪些数据种类	每个表列都有相应的数据类型
数据类型兼容	有些不同的数据类型在不同的DBMS中有相同的名称，或有些DBMS没有一些类型
行	表中的一个记录	从技术上讲，行才是真正的术语
主键	一列或一组列，其值可以唯一标识表中一一行	主键唯一且不为空
SQL	Structured Query Language（结构化查询语言）	是一种专门用来与数据库沟通的语言

二、操作示例

sql语句不区分大小写。
语句中间可以添加任意空白符。

2.1 检索数据

2.1.1 select

从一个或多个表中检索信息。

-- 检索单列
select <column_name> from <table_name>

-- 检索多列
select <column_name1>, <column_name2> from <table_name>;

-- 检索所有列
slect * from <table_name>;

-- 检索去重
select distinct <column_name> from <table_name>;

限制结果

-- sel server/Access
select top 5 <column_name> from <table_name>;

-- db2
select <column_name> from <table_name> fetch first 5 rows only;

--mysql/mariadb/postgresql/sqlite
select <column_name> from <table_name> limit 5;

-- 同上，当需要返回第五行开始的5行数据时:(第一个行数是检索的行行数，第二个数字是从第几行开始，注意行数从0开始)
select <column_name> from <table_name> limit 5 offset 5;

-- mysql/mariadb/sqlite 可简写 limit
--limit 4 offset 3 可简写为 limit 3, 4
select <column_name> from <table_name> limit 3, 4;

2.2.2 注释

-- 两个短横线之后的内容为行内注释
# 有些DBMS支持井号注释
/*
 * 这里是多行注释
 */

2.2 排序检索数据

关系数据库设计理论认为，如果不明确规定排序顺序，则不应该假定检索出数据的顺序有任何意义。

2.2.1 排序数据

-- order by 子句应该是在select语句的最后一个子句，否则会出现错误
-- order by 子句使用的列也可以是不显示的列
select * from <table_name> order by <col_name>;

-- 多列排序
-- 只有当<col_name1>重复的时候才会对<col_name2>进行排序
select * from <table_name> order by <col_name1>, <col_name2>;

-- 根据列位置数字进行排序，只有当列显示的时候才能用，可与列名混合使用
selct * from <table_name> order by 1, 2;

-- 降序排列使用desc关键字
-- 下例中<col_name1>将按降序排列，而<col_name2>仍是升序(默认)
-- 如对多列进行次序排序，则必须对第一列都指定desc(descending)关键字
select * from <table_name> order by <col_name1> desc, <col_name2>;

2.3 过滤数据

2.3.1 使用where子句

select <col_name> from <table_name> where <col_name> = <value>;
-- 常用where子句操作符
-- 不一定所有DBMS都支持
select * from <table_name> where product_name <> 'ML001';
select * from <table_name> where product_price between 5 and 10;
select * from <table_name> where product_price is null;
=  <>  !=  <  <=  !<  >  >=  !>  between  is null

3.2.2 使用and/or进行高级过滤

select * from product_name = 'ML001' and product_price <= 4;
select * from product_name = 'ML001' or product_name = 'ML001';

-- 在多and和or操作符中，为避免求值错误，可使用括号对操作进行分组
-- 使用圆括号可以消除歧义，推荐使用
select * from <table_name>
    where (product_name = 'ML001' or product_name = 'ML002') and product_price <= 4;

3.3.3 IN和NOT操作符

-- IN操作符后跟由逗号分隔的合法值，这些值必须括的圆括号中
select * from <table_name>
    where product_name in ('ML001', 'ML002') and product_price <= 4;

-- not 要用在要过滤的列前, 用于否定其后跟的条件
select * from <table_name>
    where not product = 'ML001';

2.4用通配符进行过滤

通配符搜索效率较低，不应首先使用。

2.4.1 LIKE 百分号通配符

百分号代码任意字符出现任意次数，NULL除外

select <col_name> from <table_name> where <col_name> like 'Fish%';

2.4.2 下划线通配符

只那匹配单个字符。
注：DB2不支持_。MS Access使用?而不是 _。

select * from <table_name>
    where name like '_3';

2.4.3 方括号通配符

方括号用于指定一个字符集（只有 MS ACCESS/MS SQLSERVER支持集合）

２.5 创建计算字段

计算字段并不真实存在于数据库中，而是在使用SELECT语句时创建的。
数据库列称为列，术语字段则通常与计算字段一块使用。

2.6 拼接(CONCATENATE)字段

-- Access/SQL Server 使用 +
select vend_name + '-' + vend_country
    from vendors
    order by vend_name;

-- DB2/Oracle/PostgreSQL/SQLite/Open Office Base 使用 ||
select vend_name || '-' || vend_country
   from vendors
   order by vend_name;

-- mysql/mariadb 使用 concat
select Concat(vend_name, '-', vend_country)
    from vendors
    order by vend_name;

-- 如果在检索的时候字段出现了不需要的空格，可以使用TRIM/RTRIM/LTRIM函数对其去除
select trim(vend_name) from vendors; 

2.6 使用别名

-- 使用as指定别名，as关键字可省略但不推荐
-- 别名也叫导出列
select vend_name as '名字' from vendors;

2.7 执行算术计算

`` 下述命令可以从表中检索出a,b,a*b三列
select a,b,a*b form <table_name>;

-- 如果想在使用前测试，可以使用不加from的sql语句检验结果
select 3*2;    -- 6
select trim('  abc  ');    -- abc
select Now();      --返回当前时间

2.8 使用函数

不同DBMS之间的函数实现是有差异的，比如：

-- 提取字符串的组成部分
-- Access 使用 MID()
-- DB2/Oracle/PostgreSQL/SQLite 使用 SUBSTR()
-- MySQL/SQL Server  使用 SUBSTRING()

-- 数据类型转换
-- Access/Oracle 使用多个函数，每种类型的函数有一个函数
-- DB2/PostgreSQL 使用 CAST()
-- MariaDB/MySQL/SQL Server 使用 CONVERT()

-- 取当前日期
-- Access 使用 NOW()
-- DB2/PostgreSQL 使用 CURRENT_DATE
-- MariaDB/MySQL 使用 CURDATE()
-- Oracle 使用 SYSDATE;
-- SQL Server 使用 GETDATE()
-- SQLite 使用 DATE()

常用的文本处理函数：

函数	说明
LEFT()	返回字符串左边的字符
LENGTH()(也使用DATALENGTH()或LEN())	返回字符串的长度
LOWER()(Access使用LCASE())	转小写
LTRIM()	去左边的空格
RIGHT()	返回字符串右边的字符
RTRIM()	去右边空格
SOUNDEX()	返回字符串的SOUNDEX值
UPPER()(Access使用UCASE())	将字符串转换为大写

常用的数值处理函数：

函数	说明
ABS()	返回一个数年绝对值
COS()	返回一个角度的余弦
EXP()	返回一个数的指数值
PI()	返回圆周率
SIN()	返回一个角度的正统
SQRT()	返回一个数的平方根
TAN()	返回一个数的正切

常用的聚集函数：

函数	说明
AVG()	返回某列的平均值
COUNT()	返回某列的行数
MAX()	返回某列的最大值
MIN()	返回某列的最小值
SUM()	返回某列值的和

注：count()函数会忽略指定列的值为空的行，但如果指定的是星号，则不忽略。

group 和 having 函数：

-- group 是对数据进行分组
-- where 在数据分组前进行过滤，having 在数据分组后进行过滤
-- where 排除的行不参与计算，会影响计算结果，having不会

-- 当不指定 gropu by 时,having和where作用基本相同，使用having时应结合 group by 子句
-- where 子句则用于标准的行级过滤

select子句顺序：

子句	说明	是否必须使用
select	要返回的列或表达式	是
from	从中检索数据的表	仅在从表中选择数据时使用
where	行级过滤	否
group by	分组说明	仅在按组计算聚集时使用
having	组级过滤	否
order by	输出排序顺序	否

2.9子查询

在select语句中，子查询总是从内向外处理。
作为了查询的SELECT语句只能查询单个列。