必须知道的SQL编写技巧,多条件查询不拼字符串的写法

简介:   在做项目中,我们经常遇到复杂的查询方法,要根据用户的输入,判断某个参数是否合法,合法的话才能当作过滤条件,我们通常的做法是把查询SQL赋值给一个字符串变量,然后根据判断条件动态的拼接where条件进行查询。

  在做项目中,我们经常遇到复杂的查询方法,要根据用户的输入,判断某个参数是否合法,合法的话才能当作过滤条件,我们通常的做法是把查询SQL赋值给一个字符串变量,然后根据判断条件动态的拼接where条件进行查询。下面来简单说一下写SQL中遇到的问题和解决办法。

 

 一、不确定字段名,而产生的SQL字符串拼接

   比如,有个公司要做一个系统,要支持多语言,这个时候我们就要将语音信息存储在数据库中。然后,根据客户选择查询对应的语言字段,进行显示。下面我们来模拟这个场景,打开SQL Server,新建SysLanguage表,添加一些语言字段,如English,Chinese,French,这里French不知道是什么,就都设置为Empty。

 

建表的SQL和插入一些简单的数据,SQL如下:

CREATE TABLE SysLanguage(
    Id INT PRIMARY KEY,
    English NVARCHAR(100),
    Chinese NVARCHAR(100),
    French NVARCHAR(100)
)

INSERT INTO SysLanguage VALUES (1, 'Hello', '你好', 'Empty')
INSERT INTO SysLanguage VALUES (2, 'world', '世界', 'Empty')
INSERT INTO SysLanguage VALUES (3, 'Book', '', 'Empty')
INSERT INTO SysLanguage VALUES (4, 'Open', '打开', 'Empty')
INSERT INTO SysLanguage VALUES (5, 'Save', '保存', 'Empty')
INSERT INTO SysLanguage VALUES (6, 'New', '新建', 'Empty')

 那么,考虑到系统性能,我们可能会使用存储过程,来减少网络通信量和提高响应速度。那么,在存储过程中,我们会传入一个参数,然后根据参数来查询指定的语言字段,那么SQL该怎么写呢?

这个时候,我们会想到在C#代码中,我们都是把SQL拼好,然后传给SQL Server执行,我们可以拼接字符串啊,然后有了下面的写法:

DECLARE @sql NVARCHAR(100);
DECLARE @para NVARCHAR(20);
SET @para = 'Chinese';
SET @sql = 'select ID, ' + @para + ' from SysLanguage'
print @sql
exec(@sql)

当然这样写没有任何问题,但是当SQL很复杂的时候,很多的引号和字符转义,还有拼接字符串带来的缺少空格的问题,会令人崩溃!下面来说一种不拼接字符串的写法,代码如下:

DECLARE @para NVARCHAR(20);
SET @para = 'Chinese';
SELECT ID, 
CASE @para 
WHEN 'English' THEN English
WHEN 'Chinese' THEN Chinese 
ELSE
French
END
AS [Language]
FROM
SysLanguage

这里使用了CASE WHEN语句来进行判断,当判断条件太多时,写起来也很累,我们可以写一个Function来进行封装,当然,具体用那种方法,要看具体情况了。

这里说道了CASE WHEN,就不得不提另外一种查询了。先来看下需求:从学生表中查询出男生的人数,女生的人数和学生总人数

看到这个,我们首先想到了SUM函数,但是,然后呢?好像哪里不对?那么,到底该怎么写呢,当然还是用CASE WHEN,来看下代码吧

SELECT SUM(CASE StuSex WHEN 1 THEN 1 ELSE 0 END) AS Boys,
SUM(CASE StuSex WHEN 1 THEN 0 ELSE 1 END) AS Girls,  
SUM(StuID) AS Total
FROM Student

看到代码,感觉是不是很简单啊,就是当时没想到,呵呵!

 

 二、不定查询条件产生的SQL字符串拼接

  习惯了C#代码,突然写起SQL来,感觉还是有点那么不顺手!下面来看看,当有多个查询条件,但是又不确定几个查询条件时,SQL该怎么写?我们经常遇到这种需求,页面上很多控件,要根据用户的输入进行查询,这个时候我们就要判断传入的控件的值是否符合指定条件,符合条件了才能被作为查询条件,否则就忽略这个条件。

拼接字符串的写法相信大家都经常用,这里就不写了,我们之接来看布拼接SQL怎么查询!

比如现在要查询指定条件的学生,用户可能查询所有,或查询男生,或查询指定Id的学生,或……等待,可能很多种情况。下面我们来写布拼接字符串的SQL,代码如下:

DECLARE @ID INT;
DECLARE @Sex BIT;
DECLARE @Name NVARCHAR(50);

SET @ID = -1;
SET @Sex = NULL;
SET @Name = 'Ja';

SELECT * FROM Student
WHERE (@ID = -1 OR StuID = @ID)
AND (@Sex IS NULL OR StuSex = @Sex)
AND (@Name IS NULL OR StuName LIKE '%' + @Name + '%')

这里声明三个变量,当做传入的参数,传入的参数都有默认值,或者为NULL,我们在where添加里面用了OR添加判断,首先判断@ID = -1,如果条件成立了,OR就不会再判断OR后面的条件,到这里就(@ID = -1 OR StuID = @ID)就返回一个TRUE,相当于WHERE TRUE AND ……,所以就会忽略这个查询条件继续走下面的过滤判断。这样写,比拼接字符串好的多吧!

 

最后来说一下数据库中N字符的作用,说白了就是将后面的字符串内容,转换成Unicode编码,来写段SQL测试一下吧

DECLARE @a NVARCHAR(20)
DECLARE @b NVARCHAR(20)
SET @a = N'中文'
SET @b = '中文'
IF(@a = @b)
PRINT '数值相等'
ELSE
PRINT '不相等'

输出结果如下:

在中文版的系统中,是相等的,在英文版的系统中,@b会显示乱码。大家可以测试一下!

 

 作者:雲霏霏

 博客地址:http://www.cnblogs.com/yunfeifei/

 声明:本博客原创文字只代表本人工作中在某一时间内总结的观点或结论,与本人所在单位没有直接利益关系。非商业,未授权,贴子请以现状保留,转载时必须保留此段声明,且在文章页面明显位置给出原文连接。

 

目录
相关文章
|
4月前
|
SQL JSON 关系型数据库
【SQL编程】MySQL 5.7.28 版本使用 SQL 直接解析 JSON 字符串(判断是否是合法JSON类型+文本深度+文本长度+值类型+keys获取+值获取+不同深度数据获取)
【SQL编程】MySQL 5.7.28 版本使用 SQL 直接解析 JSON 字符串(判断是否是合法JSON类型+文本深度+文本长度+值类型+keys获取+值获取+不同深度数据获取)
50 0
|
4月前
|
SQL JSON Apache
Apache Flink SQL目前还不支持直接解析JSON字符串并将其转换为预期的数据类型
Apache Flink SQL目前还不支持直接解析JSON字符串并将其转换为预期的数据类型
157 1
|
4月前
|
SQL XML Java
记一次在mybatis中使用String字符串作为sql语句 in关键字 后面参数的事故
记一次在mybatis中使用String字符串作为sql语句 in关键字 后面参数的事故
66 0
|
8月前
|
SQL
sql server从某个字符开始截取后面的字符串
sql server从某个字符开始截取后面的字符串
130 0
|
10月前
|
SQL 存储 数据库连接
【批量添加】-拼接sql字符串
【批量添加】-拼接sql字符串
100 0
|
12月前
|
SQL
01 反引号“`”的使用-ES6模板字符串&SQL区分关键字
1 在SQL中反引号的作用-区分关键字 当属性名与SQL关键字冲突时使用反引号将属性名扩起来,用于区分关键字
94 0
|
SQL 前端开发 Java
【SQL开发实战技巧】系列(十二):三问(如何对字符串字母去重后按字母顺序排列字符串?如何识别哪些字符串中包含数字?如何将分隔数据转换为多值IN列表?)
如何将分隔数据转换为多值IN列表、如何按字母顺序排列字符串、如何对字符串字母去重后按字母顺序排列字符串、如何删除字符串中的字符保留数字。【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。这篇文章还是介绍的字符串处理案例,还是那句话,这些操作太太太常见了。后面还会写,而且是更麻烦更难的需求。
【SQL开发实战技巧】系列(十二):三问(如何对字符串字母去重后按字母顺序排列字符串?如何识别哪些字符串中包含数字?如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列(十):从拆分字符串、替换字符串以及统计字符串出现次数说起
本篇文章讲解的主要内容是:***遍历拆分字符串为单个字符、字符串中包含引号如何转译(q-quote特性)、计算字符在字符串中出现的次数、使用translate从字符串中快速删除替换不需要字符的巧妙写法、使用正则表达式regexp_replace将字符和数字数据分离、使用正则表达式regexp_like查询只包含数字或字母型的数据***
【SQL开发实战技巧】系列(十):从拆分字符串、替换字符串以及统计字符串出现次数说起
|
SQL 关系型数据库 MySQL
MySQL——sql语句处理时间——时间、字符串、时间戳互相转换
MySQL——sql语句处理时间——时间、字符串、时间戳互相转换
116 0
|
SQL Oracle 关系型数据库
【已解决】Error querying database. Cause: java.sql.SQLDataException: ORA-01861: 文字与格式字符串不匹配
【已解决】Error querying database. Cause: java.sql.SQLDataException: ORA-01861: 文字与格式字符串不匹配
1661 0