SQL基础2

最新推荐文章于 2022-12-09 11:38:54 发布

置顶小_Tao

最新推荐文章于 2022-12-09 11:38:54 发布

阅读量278

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/weixin_42194515/article/details/116142064

版权

SQL 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

Projection投射

SELECT

[ALL|DISTINCT]

{*}

[colonene1 alias1

,colonene2 alias2

,…]

FROM

nomtable[aliastable];

DISTINCT用来实现去重逻辑

*显示表中所有的行

alias字段的别名

取别名

colonne [AS] alias1 ou “Alias1”
可以在别名前添加AS关键字
如果你设置的别名对大小写敏感或者有特殊字符使用双引号

SELECTION选择或约束

SELECT

nomtable.*

FROM

nomtable[aliastable]

WHERE

condition;

含有数字和字母的数据和日期类型的数据需要使用单引号包围环绕

字符型数据对字母的大小写敏感

日期类型的数据对日期格式敏感

where中常用的比较符号

= 相等

！=不相等

>大于

>=大于等于

<小于

<=小于等于

Between…AND介于之间

In在范围里

Like类似

Is Null为空

Any至少为1

All所有

%代表0个或n个任意字符

_占位符代表一个任意字符

++如果不使用占位符就like就会等同于=

like ‘apple’<==> =‘apple’++

排序TRI

可以将结果根据多个列进行ASC或者DESC排序

ORDER BY

{expression|position|alias}[ASC|DESC]

[,{expression|position|alias}[ASC|DESC],…]

先执行select distinct再执行order by，可能会出现distinct删掉了字段就导致order by失败，所以使用distinct且使用order by时 order by的字段要出现在select内

Palmarès排行榜

Oracle不支持limit但是可以通过子查询解决这个问题，这个方案适用于表和视图
SELECT

*

FROM

table1,(select * from table2 where …)alias2

WHERE

table1.colonne1=alias2.colonne2;

这个方法结合ROWNUM可以实现TOP的查询
例如查找工资最高的三个人
SELECT

Empno

,ename

,sal

FROM

(select empno,ename,sal from emp order by sal desc)

WHERE

rownum<4;

在Oracle12c的版本中增强了ORDER BY函数
ORDER BY

{expression|position|alias}[ASC|DESC]

[,{expression|position|alias}[ASC|DESC],…]

[OFFSET offset {ROW|ROWS}]

[FETCH {FIRST|NEXT} [{rowcount|percent PERCENT}]

{ROW|ROWS}{ONLY|WITH TIES}] OFFSET nblignes ROWS

FETCH FIRST 5 ROWS ONLY;

//只获取前五个

OFFSET 3 ROWS FETCH FIRST 5 ROWS ONLY;

//往后偏移三位然后只获取前五个

FETCH FIRST 3 ROWS ONLY;

//获取前三个

FETCH FIRST 9 ROWS WITH TIES;

//获取前九个但是如果有并列第九的都取

FETCH FIRST 30 PERCENT ROWS;

//获取前百分之三十的行

OFFSET 12 ROWS FETCH FIRST 1 ROWS ONLY;

//往后偏移12行然后取第一行

OFFSET (select count(1) from emp)-5 ROWS;

FETCH NEXT 5 ROWS ONLY;

//取最后五行

Jointures连接

谓词连接

SELECT

table1.colonne1

,table2.colonne3

FROM

table1

,table2

WHERE

table1.colonne1=table2.colonne2;

代数的连接IN

SELECT

*

FROM

Clients

WHERE

codeclient IN

(SELECT

codeClient

FROM

Commandes);

Inner join内连接 ON

内连接又称标准连接是根据主键进行连接的
SELECT

table1.colonne1

table2.colonne3

FROM

table1 INNER JOIN table2

ON table1.colonne1=table2.colonne2

建议使用别名替换表名因为多个表可能都有这个字段同时优化写SQL语句的速度

Cross join笛卡尔积连接

SELECT

table1.colonne1

table2.colonne2

FROM

table1 CROSS JOIN table2

一个查询可以有多种写法

SELECT
    DISTINCT
    F.nomfour
FROM
    Fournisseurs F Inner join produits P
        ON F.nomfour=P.nomfour
            Inner join categories C
                ON F.codecategorie=P.codecategorie
WHERE
    C.nomcategorie="Viande";

SELECT
    DISTINCT
    fournisseurs.nomfour
FROM
    fournisseurs
    ,produits
    ,categories
        ON F.nomfour=P.nomfour
            Inner join categories C
                ON F.codecategorie=P.codecategorie
WHERE
    C.nomcategorie="Viande";

SELECT
    DISTINCT
    Fournisseurs.nomfour
FROM
    fournisseurs
WHERE
    fournisseurs.numfour IN
    (SELECT 
        produits.numfour
    FROM
        produits
    WHERE
        produits.codecategorie IN
        (SELECT 
            categories.codecategorie
        FROM
            categories
        WHERE
            categories.nomcategorie="Viande")
    );

SELECT
    DISTINCT fournisseurs.nomfour
FROM
    fournisseurs
WHERE
    fournisseurs.numfour IN
    (SELECT 
        produits.numfour
    FROM
        produits P inner join categories C
            on P.codecategorie=C.codecategorie
    WHERE
        C.nomcategorie="Viande"
    );

Union合并,intersection相交 et différence差别

union合并会把重复的值删除

union all合并但是会保存所有的重复值

intersection交集属于第一个集合同时也属于第二个集合

minus差集属于第一个集合但是不属于第二个集合

EXPRESSIONS表达式,FONCTIONS函数

一个表达式是一个变量，常量和函数的组合

一个函数是一个例行程序

函数是用来丰富查询和对数据进行操作

可以用在select，where，order by 和group by这些语句内

算数表达式

±*/可用于常量变量和算术函数

字符串表达式

字符串连接符在Oracle中已经是可用的了’||’

SELECT

CLI.nomclient||’->’||CLI.qdrclient

时间表达式

SELECT

SYSDATE

FROM

Dual;

日期可以支持数字运算

‘10-OCT-2012’+3=‘13-OCT-2012’

‘13-OCT-2012’-‘10-OCT-2012’=3

算术函数

ABS(n):n的绝对值

ABS(-23.5)=23.5

MOD(M,N):求余数

MOD(7,2)=1

SIGN(n):判断一个数字是否大于0

SIGN(-23.5)=-1

SIGN(0)=0

SIGN(+23.5)=+1

POWER(M,N)次方

POWER(2,3)=8

SQRT(n):开方

SQRT(16)=4

SQRT(-16)=null

ROUND(N,[M]):截取四舍五入

ROUND(23.462,2)=23.46

ROUND(23.466,2)=23.47

ROUND(1200,-3)=1000

TRUNC(N,[M]):截取

TRUNC(23.462,2)=23.46

TRUNC(23.466,2)=23.46

字符的函数

CHAR(n)返回ASCII码为n的字符

CHAR(65)=‘A’

INITCAP:首字母大写

INITCAP(‘dupont’)=‘Dupont’

LENGTH计算字符串的长度

LENGTH(‘dupont’)=6

LPAD在左边补齐指定字符至指定的长度

LPAD(‘dupont’,10,’|’)=’||||dupont’

LOWER转为小写字符

LOWER(‘DUPONT’)=‘dupont’

RPAD在右边补齐指定字符至指定的长度

RPAD(‘dupont’,10,’|’)=‘dupont||||’

SOUNDEX根据发音来筛选

SOUNDEX(‘dupont’)

SUBSTR(字符串,起始位置,长度)截取字符串

SUBSTR(‘MARTIN’,2,3)=‘ART’

LOWER转为大写字符

LOWER(‘dupont’)=‘DUPONT’

类型转换

Oracle的自动类型转换又称为隐式转换

VARCHAR2 ou CHAR => Number

VARCHAR2 ou CHAR => Date

Number => VARCHAR2

Date => Varchar2

case

在Oracle8.1.7中引入只在SQL中

类似于switch

case expression

WHEN value1 then result1

WHEN value2 then result2

WHEN value3 then result3

End;

NULLIF

ORACLE 9中引入的方法

NULLIF(expression1,expression2)

如果两个表达式相等就返回null，如果不相等就返回expression1

第一个表达式应该非空
case

WHEN expression1!=expression2 then expression1

End;

COALESCE

ORACLE 9中引入的方法

COALESCE(expression1,expression2,…)

哪个表达式非空就返回哪个表达式

case

WHEN expression1!=expression2 then expression1

End;

NVL

ORACLE 9中引入的方法

NVL(expression1,expression2)

如果第一个为空就返回第二个，如果非空就返回第一个

正则表达式

Oracle10g的时候被引入的基于POSIX可移植操作系统接口

正则表达式的句法

.匹配除了换行符之外的所有单字符

^匹配输入字符串的开始位置

$匹配输入字符串的结束位置<br> ^a.b$ (正则表达式)等效于a_b(like)

^a.*b$(正则表达式)等效于a%b(like)

*代表零次或者多次

?代表零次或者一次

+代表一次或者多次

{m}代表m次

{m,}代表最少m次

{m,n}代表最少m次最多n次

\n表示重复前面的表达式n次

类型	描述
[:alpha:]	字母字符
[:lower:]	小写字母字符
[:upper:]	大写字母字符
[:digit:]	数字字符
[:alnum:]	数字字母混合
[:space:]	空字符
[:punct:]	标点字符
[:cntrl:]	控制字符
[:print:]	输出字符
c	大小写敏感
i	大小写不敏感
m	多行字符串
n	.字符自动区分一行
\|	或的意思可选择
()	和或一起使用用来分组
[char]	可选字符的列表

regexp_like(待分析的字符串，查找的模式)

regexp_like(待分析的字符串，查找的模式，可选参数)

REGEXP_LIKE(testcol,'[[:alpha:]]');//含有一个字符

REGEXP_LIKE(testcol,'[[:alpha:]]{7}');//含有至少7个字符

REGEXP_LIKE(testcol,'[[:alpha:]]{5,7}');//含有5到7个字符

REGEXP_LIKE(testcol,'^ab*');//以一个a另个或多个b开头的字符串

REGEXP_LIKE(testcol,'^..a.');//含有一个a在第三位处

REGEXP_LIKE(testcol,'([az])\1','i');//含有两个字符a或者z，对大小写不敏感

REGEXP_LIKE(testcol,'([az])\1','i');//含有两个字符a或者z，对大小写不敏感

REGEXP_LIKE(testcol,'^Ste(v|ph)en$');//Steven或Stephen

REGEXP_LIKE(testcol,'.*@.*\.(com|fr)');//邮箱地址

regexp_replace()

REGEXP_REPLACE(testcol,'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1)\2-\3');//111.222.3333->(111)222-3333

REGEXP_REPLACE(testcol,'.','\1');//Steven->S t e v e n

REGEXP_REPLACE(adresse_mail,'(.*)\.(.*)@(.*)','\2.\1@\3');
//nom.prenom@domain.com=>prenom.nom@domain.com

聚合函数

SUM求和

AVG求平均值

MAX求最大值

MIN求最小值

COUNT求行数直接用*,求不同的值的个数用distinct

VARIANCE求方差

STDDEV求均方根偏差

聚合GROUP BY

运行顺序

先WHERE
然后GROUP BY
接着聚合函数
再是Having
最后是输出

注意事项

聚合函数一般返回都是一个值

所有select中选中的非计算的列都应该出现在group by中

group by的聚合列不一定要出现在select中

group by可以由一系列的列组成从主要的到次要的

从Oracle10G开始默认对结果不排序，但是可以使用order by进行指定排序

如果对聚合后的列有限制可以使用HAVING做处理

小_Tao

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
SQL基础2

Projection投射SELECT [ALL|DISTINCT] {*} [colonene1 alias1 ,colonene2 alias2 ,…]FROM nomtable[aliastable];DISTINCT用来实现去重逻辑*显示表中所有的行alias字段的别名取别名colonne [AS] alias1 ou “Alias1”可以在别名前添加AS关键字如果你设置的别名对大小写敏感或者有特殊字符使用双引号SELECTION选择或约束SELECT nomt
复制链接

扫一扫

专栏目录