未名手记

SQL中的DQL：SELECT与函数

发布:

修改:

分类:

,

短链接: blog.t725.cn/?p=1678

增删改查是SQL最常见的操作，注意：内容还没有完成。

Contents hide
1 结构化查询语言(Structured Query Language)有6种语言
2 DBeaver-CE
3 增删改查示例语句
3.1 Select的子句与运算符
3.1.1 表之间链接关系
3.1.1.1 提高 Left Join 性能的书写方式
3.1.1.2 另一张名图
3.2 其他示例
3.2.1 提交模式对性能的影响
4 函数
4.1 日期与时间类
4.2 数字/日期字符类
4.3 文本字符类
4.4 行与列的转换
4.5 值的处理：判断、拼接、长度、是否含中文、行号

结构化查询语言(Structured Query Language)有6种语言

  1. 数据查询语言（DQL）：SELECT
  2. 数据操作语言（DML）：INSERT，UPDATE和DELETE
  3. 事务处理语言（TPL）：像BEGIN TRANSACTION、COMMIT和ROLLBACK
  4. 数据控制语言（DCL）：像GRANT(授权)或REVOKE(撤销授权)获得许可
  5. 数据定义语言（DDL）：CREATE、DROP、ALTER、Desc，在数据库中创建/删除/修改/查看：库：Database/表：Table/字段：Column/索引（键）：Index/视图：View
  6. 指针控制语言（CCL）：像DECLARE CURSOR、FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作。

DBeaver-CE

下载：最新版本、6.3.5 Win x64；Oracle 不需要安装 instantclient_11_2，自动从maven下载jdbc。

1、使用阿里云方法：窗口–首选项–连接–驱动–Maven，下载驱动文件>下载配置>驱动>maven 中添加，并移动最上面。

https://maven.aliyun.com/repository/public/

2、关闭SQL补全表别名：窗口–首选项–Editors–SQL补全–insert table aliases (in FROM clause)

3、标题字段名显示备注：窗口–首选项–Editors–结果集–表示–在标题栏中显示列描述

4、单次记录条数：窗口–首选项–Editors–结果集–集数获取大小

5、关闭启动检查更新：窗口–首选项–User Interface–自动检查更新

6、SQL编辑器里自动换行显示：右键–格式–自动换行

7、关闭SQL脚本错误输出窗口：窗口–首选项–Editors–SQL编辑器–在新消息上打开输出查看器

单个“数据库连接”配置：

10、数据库连接–编辑连接–连接设置–初始台：是否自动提交、隔离级别、默认库/模式

11、数据库连接–编辑连接–常规：连接类型（dev/test/prod）、安全性、过滤

12、数据库连接–Connection view–Advanced：简单与自定义很只显示表，其他对像（存储过程==）都不显示。

13、打开 dbeaver.ini 文件，修改或添加以下内容：

-Xms512m
-Xmx2048m

14、SQL编辑器中，F4可以查看表。

增删改查示例语句

Select的子句与运算符

Select … From … Where … [Group by … Order by … ] ;
  • 行数选择：Top/Rownum/Limit
  • 别名：Aliases/As
  • 除重：Distinct
  • 布尔：And、Or、Not
  • 比较运算符：<、=、>、<>、<=、>=
  • 包含/排除：In、Exists、Not In、Not Exists
  • 匹配：Like、Not Like
  • 空值：Is Null、Is Not Null
  • 范围：Between And、Not Between And
  • 聚合/汇总：Group By Having
  • 排序：Order By
-- group/order by 正确写法
select (case o.qty when 1 then '1' else 'N' end) as otype,
       hour(o.create_time) as Htime,
       count(distinct o.id) as odocount
from t_wh_odo o where o.odo_type='2' 
group by otype,Htime having (odocount >5 and odocount<10)
order by odocount;
  • 联合：Union、Union All
  • 通配符：%、_
  • 转义符（查找左匹配 abc’_\ 的内容）：
Like 'abc\'_\%' ESCAPE '\';

表之间链接关系

常用的是Inner与Left

  1. Inner JOIN: 如果表中有全匹配，则返回行
  2. LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
  3. RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
  4. FULL JOIN: 只要其中一个表中存在匹配，就返回行

问：Left Join时，只要仅在左表中私有的记录？如下图，只要2a/3b两条记录。

答：WHERE增加右表IS NULL。

提高 Left Join 性能的书写方式

见：left join 中 on 与 where 理解，及性能影响 – 未名

另一张名图

其他示例

-- 查询结果插入新表
SELECT t.group_name,count(t.id) as bieming,sum(t.qty),avg(t.qty),max(t.id),min(t.id)
 INTO table_backup
 FROM table_name t join table_name2 n on t.id=n.id
 WHERE t.qty between 99 and 1000 
	   and t.name like '%x/_x%'  escape '/' 
	   and t.ctiy in ('X','Y','Z') 
	   and t.address is not null 
	   AND EXISTS (select 1 from table_name3 e where t.accid=e.accid)
 GROUP BY t.group_name having count(t.name)>1
 ORDER BY bieming desc
;

-- 插入
INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...);
-- 更新
UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value;
-- 删除
DELETE FROM table_name WHERE some_column=some_value;

--创建视图
CREATE OR REPLACE FORCE VIEW view_name (id,c1,c2) AS SELECT id,c1,c2 FROM table_name;

提交模式对性能的影响

-- “自动提交”模式 MySQL
select @@autocommit; -- 查询当前模式
SET AUTOCOMMIT = 0  -- 设置“非自动提交”模式
SET AUTOCOMMIT = 1  -- 设置“自动提交”模式
-- 如果你插入了1000条数据，mysql会commit1000次的，如果我们把autocommit关闭掉，通过程序来控制，只要一次commit就可以了。

函数

Case：多条件判断

-- 简单Case函数
CASE sex WHEN '1' THEN '男' WHEN '2' THEN '女' ELSE '其他' END

-- Case搜索函数，注意：Case函数只返回第一个符合条件的值，剩下的Case部分将会被自动忽略。
CASE WHEN SEX='1' OR SEX='2' THEN '正常' ELSE '异常' END

查询结果插入表中

-- 自动创建表并插入数据
CREATE TABLE New_table_name as SELECT * FROM table_name where 1=2;
SELECT seq.nextval,c1,c2 INTO table_name1 FROM table_name;

-- 插入数据到已有表中
INSERT INTO table_name1 (id,c1,c2) SELECT seq.nextval,c1,c2 from table_name;

日期与时间类

日期比较

-- 日期比较 MySQL/SQLServer
t.create_time>'2021-01-30 12:30:59'
-- 日期比较 Orcale
t.create_time>to_date('2021-01-30 12:30:59','yyyy-mm-dd hh24:mi:ss')

日期转换

日期计算

日期提取 MySQL

-- 小时
hour(o.create_time)

/*
DATE_FORMAT(date,'format')
格式	描述
%Y	年，4 位
%y	年，2 位
%m	月(00-12)
%d	天(00-31)
%H	小时 (00-23)
%i	分钟(00-59)
%s	秒(00-59)
%j	年的天 (001-366)
%w	周的天（0=星期日, 6=星期六）
%T	时间, 24-小时 (hh:mm:ss)
*/

DATE_FORMAT(o.create_time,'%Y%m%d')  
-- 结果:20250307

日期提取 Oracle

to_char(a.invalidDate,'YYYYMMDD')
trunc(SYSDATE)

数字/日期字符类

--将数据类型 varchar 转换为 numeric 时出错。解决办法：用isnumeric(字段名)判断后，再转换。
isnumeric(字段名)=1 为数值，isnumeric(字段名)=0为文本

文本字符类

字符替换

replace(a.name,'仓库','')

字符查找

字符截取

SUBSTRING(addreno,1,2)='CC'

行与列的转换

行转列 MySQL

select a,group_concat(concat_ws('|',d.area_code,d.td) order by concat_ws('|',d.area_code,d.td) Separator '/') as b
from xx where x=x group by a;

行转列 oracle

值的处理：判断、拼接、长度、是否含中文、行号

值判断

-- 值判断 Oracle
NVL(E1, E2) -- 如果E1为NULL，则函数返回E2，否则返回E1本身
NVL2(E1, E2, E3) -- 如果E1不为null，则返回E2；如果E1为NULL，则函数返回E3
decode(条件，值1，返回值1，值2，返回值2，...值n,返回值n，缺省值) 

-- 值判断 MySQL
IFNULL(expr1,expr2) -- 如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。
IF(expr1,expr2,expr3) -- 如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

-- 值判断 SQLserver 
ISNULL( E1, E2) -- 如果E1为NULL，则函数返回E2，否则返回E1本身

值拼接

-- 值拼接 Oracle
t.name||':'||t.address

-- 值拼接 MySQL
CONCAT
CONCAT_WS
GROUP_CONCAT

-- 值拼接 SQLserver

值长度，是否含中文

-- 字符串长度与检测 MySQL
-- length(b.prodarea)=length(a.prodarea)   lengthB(b.prodarea)=lengthB(a.prodarea)
-- 以用length(‘string’)=lengthb(‘string’)判断字符串是否含有中文。

显示行号

-- 行号处理 oracle
rownum
-- 行号处理 mysql 自定义变量显示行号@rowno，简洁而实用
SELECT @rowno:=@rowno+1 as rowno,r.* from grade_table r ,(select @rowno:=0) t;

页： 1 2

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注