SQL：条件查询&&处理查询结果

浅浅的花香味﹌ 2022-03-28 03:53 1460阅读 0赞

# 1、条件查询WHERE #

## ①比较查询 ##

SELECT `emp_no`, `from_date`, `salary` FROM `salaries` WHERE `salary` = 60117;
    SELECT `emp_no`, `from_date`, `salary` FROM `salaries` WHERE `salary` < 60117;
     //--------------------------
    SELECT `emp_no`, `from_date`, `salary` FROM `salaries` WHERE `salary` BETWEEN  60117 AND 60300;
    -- 相当于
    SELECT `emp_no`, `from_date`, `salary` FROM `salaries` WHERE `salary` <=  60300  AND `salary` >= 60117;

*  找到对应表–>通过where子句查询出符合指定条件的记录，表中该字段的所有记录都会被比较，然后再选取出SELECT语句指定的列
 *  sql过滤与应用过滤：数据也可以在应用层过滤。但是建议在sql语句中直接进行过滤，这样会具备更好的可伸缩性，而且，如果在客户机上过滤数据，服务器不得不通过网络发送多余的数据，这将导致网络带宽的增加。
 *  where子句必须放在from之后，order by必须放在where之后
 *  between and:
    
     *  包含临界值 \[\]： 注意不同的数据库是不同的处理方法，对于mysql，是双闭区间\[\]
        
         *  两个临界值不要调换顺序  
            ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]

*  对字符串使用不等号时需注意：数据库对字符串型的数据原则上使用字典顺序排序， 比如1, 11, 123, 2, 22, 23, 4, 5。 这时比2大的字符应该是：22, 23, 4, 5
 *  不能对NULL使用比较运算符，只能用IS NULL、IS NOT NULL之类判定

## ②逻辑操作符 ##

可以将多个条件组合起来，用于连接条件表达式

### AND & OR & IN & NOT ###

*  AND  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]
 *  OR  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]  
    or这样的语句不允许在生产环境中出现，应该在前端缓存。

>#条件必须全部满足
    SELECT `emp_no`, `from_date`, `salary` FROM `salaries` 
    WHERE `from_date` = "2000-01-01" AND `salary` < 40000; 
    > ***
    >#条件满足其中之一就可：结果包含了 "2000-01-01"和"1995-01-01"
    SELECT distinct  `from_date` FROM `salaries` 
    WHERE `from_date` = "2000-01-01" OR `from_date` = "1995-01-01"; 
    > ***
    > #in与or的意义相同
    SELECT distinct  `from_date` FROM `salaries` 
    WHERE `from_date` in ("2000-01-01", "1995-01-01"); 
    > ***
    > #not表示对条件取反
    SELECT distinct  `from_date` FROM `salaries` 
    WHERE `from_date` not in ("2000-01-01", "1995-01-01");

*  AND & OR的计算次序

SELECT row_name FROM table_name WHERE row_name = 值1 OR row_name=值2 AND row_name1 > 值3 ;
    	where子句中可以用任意个AND和OR来组织过滤条件，但是AND的优先级更高，因此此句相当于SELECT row_name FROM table_name WHERE row_name = 值1 OR （row_name=值2 AND row_name1 > 值3）。

在where子句中使用圆括号：因为圆括号具有比AND和OR操作符高的计算次序，DBMS会先过滤()内的条件。因此：任何时候使用具有AND和OR操作符的where子句，都应该使用()明确分组操作符，不要依赖默认计算次序，使用()没有什么坏处，它能消除歧义。

*  总结：
    
     *  优先级：()>AND>OR
     *  当AND和OR结合起来用的时候最好用()指明优先级

### 逻辑操作符中的NULL ###

*  SQL中的逻辑运算包含：真、假、不确定这三种逻辑。SQL被称为三值逻辑。
 *  数据库中尽量不要使用NULL，也就是设置NOT NULL约束。
 *  三值逻辑中的AND与OR的真值表。  
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 3]

### NOT ###

- WHERE中的NOT子句有且只有一个功能，就是否定它之后的所有的任何条件   MYSQL支持使用NOT对IN，BETWEEN和EXISTS子句取反，这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。

*  NOT不能单独使用，必须和其他查询条件组合起来使用。表示对条件取反。  
    ![在这里插入图片描述][20181207152538427.png]
 *  请不要滥用NOT
 *  IN VS OR：
    
     *  IN更清楚更直观
     *  使用IN时，计算的次序更加容易管理
     *  IN操作符比OR操作符清单执行更快
     *  IN的最大优点是可以包含其他SELECT语句，使得能够更动态的建立WHERE子句。
     *  **in列表的值类型必须一致或兼容**
     *  **in列表中不支持通配符**

## ③使用like通配符 ##

SELECT `emp_no`, `first_name` FROM `employees` WHERE `first_name` LIKE 'Chri%';
    SELECT `emp_no`, `first_name` FROM `employees` WHERE `first_name` LIKE '%ri%';
    SELECT `emp_no`, `first_name` FROM `employees` WHERE `first_name` LIKE 'C%el'; -- 用得少
    SELECT `emp_no`, `first_name` FROM `employees` WHERE `first_name` LIKE '_ristine';

*  特点：
    
     *  一般和通配符搭配使用
        
         *  通配符：
            
             *  % 任意多个字符,包含0个字符
             *  \_ 任意单个字符
 *  使用通配符的技巧：
    
     *  不要过度使用通配符，如果其他操作符能够达到相同的目的，应该使用其他通配符。
     *  在确实需要使用通配符的时候，除非决定有必要，否则不要把它们用在搜索模式的最开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
 *  总结语法：
    
     *  select 查询列表 from 表明 where 筛选条件;
 *  模糊查询
    
     *  like, between and, in, is null/ is not null

## ④空值检查IS NULL ##

>SELECT `cust_id` FROM `customers` WHERE `cust_email` IS NULL;
    > ***
    >SELECT `cust_id` FROM `customers` WHERE `cust_email` IS NOT NULL;

# 2、处理查询结果 #

存储在数据库表中的数据一般不是应用程序需要的格式，一般需要对结果进行拼接、大小写转换、计算总和等处理。  
再数据库服务器上面完成这些操作比在客户机中完成要快很多，因为DBMS是设计来快速有效的完成这种处理的.  
计算字段并不实际存在于数据库表中，计算字段是运行时在select语句内创建的。

## ① 拼接字段 ##

> SELECT CONCAT(`vend_name`, ‘(’, `vend_country`, ‘)’) FROM `vendors` ORDER BY `vend_name`;
> 
> --------------------
> 
> SELECT CONCAT(RTRIM(`vend_name`), ‘(’, LTRIM(`vend_country`), ‘)’) AS vend\_title FROM `vendors` ORDER BY `vend_name`;

注意:多数DBMS使用+或者||来实现拼接，MySQL使用Concat()函数来实现。

## ② 进行算术运算±\*/ ##

包含NULL的运算，其结果也是NULL

> SELECT `prod_id`, `quantity`, `item_price`, `quantity`\*`item_price` AS ‘单价’ FROM `orderitems` WHERE `order_num` = 20005;

### \+的作用：运算符 ###

> SELECT 100+90; 两个操作数都是数值型，则做加法运算  
> SELECT “100”+90; 如果其中一方为字符型，试图将字符型数值转换成数值型，如果转换成功，则做加法运算  
> SELECT “jo”+90; 如果转换失败，则将字符型数值转换成0  
> SELECT NULL + 10; 只要其中有一方为null，那么结果一定是null

## ③IFNULL&ISNULL ##

> SELECT IFNULL(`cust_email`, 0), `cust_email` FROM `customers`;  
> SELECT ISNULL(`cust_email`), `cust_email` FROM `customers`;

*  ifnull (`字段`, newValue1)：如果所查询的字段为空，则设置为newValue1
 *  isnull函数：如果某字段为null，则返回一个1，否则返回0

## ④ 文本处理函数 ##

> concat：连接  
> substr：截取子串  
> upper：大写  
> lower：小写  
> replace：替换  
> length：获取字节长度  
> trim：去除前后空格  
> lpad：左填充  
> rpad：右填充  
> instr：获取子串第一次出现的索引

## ⑤日期和时间处理函数 ##

DATA()日期提取

> SELECT `cust_id`,`order_num` FROM `orders` WHERE `order_date` = ‘2005-09-01’; \#不建议使用
> 
> --------------------
> 
> SELECT `cust_id`,`order_num` FROM `orders` WHERE DATE(`order_date`) = ‘2005-09-01’; \#推荐使用  
> \#DATE表示从order\_date中提取yyyy-mm-dd
> 
> --------------------
> 
> \#查询2005年9月的订单  
> SELECT `cust_id`,`order_num` FROM `orders` WHERE YEAR(`order_date`) = 2005 AND MONTH(`order_date`) = 8;

可以获取指定的部分，年，月，日，小时，分钟，秒

> SELECT YEAR(NOW()) 年, YEAR(‘1998-1-1’) AS ‘1998’;  
> SELECT YEAR(NOW()) 年, MONTH(NOW()) 月, DAY(NOW()) 日, HOUR(NOW()) 时, MINUTE(NOW()) 分, SECOND(NOW()) 秒;  
> SELECT MONTHNAME(NOW()) 月;

now：返回当前系统日期+时间

> SELECT NOW();

curdate 返回当前系统日期，不包含时间

> SELECT CURDATE();

curdate 返回当前系统时间，不包含时间日期

> SELECT CURTIME();

str\_to\_data：将日期格式的字符转换成功指定格式的字符

> SELECT STR\_TO\_DATE(‘1995-11-15’, ‘%Y-%c-%d’);  
> SELECT STR\_TO\_DATE(‘2002 11-15’, ‘%Y %c-%d’);

DATE\_FORMAT:将日期转换成字符

> SELECT DATE\_FORMAT(NOW(), “%y年%m月%d日”);

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效的过滤，并且节省物理存储空间。一般来说，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取，统计和处理这些值。由于这个原因，日期和时间函数在MySQL语言中具有重要作用。  
**推荐，当指定插入删除或者过滤时，日期必须为yyyy-mm-dd\[首选的日期格式\]**  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 4]

## ⑥ 数据类型转换 ##

varchar类型转换int类型或者浮点数

```sql
    select * from gyzd_yysinfo order by cast(yysid as SIGNED INTEGER)

或者

select * from gyzd_yysinfo order by cast(yysid as UNSIGNED INTEGER)

浮点数

select cast("23333.3333" as decimal(9,2));

## 6.11、全文本搜索
    	
    	并非所有的引擎的支持全文本搜索
    	两个最常用的引擎是：MyISAM(支持)，InnoDB(不支持)
    		
    	通配符和正则表达式的缺点：
    		性能：这两个匹配通常要求MySSQL尝试匹配表中所有行，而且这些搜索极少使用表索引--》查询耗时
    		明确控制：很难明确匹配什么和不匹配什么
    		等等
    	全文本搜索优点：
    
    ### 启动全文本搜索	
    ```sql
    >CREATE TABLE productnotes
    (
      note_id    INT           NOT NULL AUTO_INCREMENT,
      prod_id    CHAR(10)      NOT NULL,
      note_date DATETIME       NOT NULL,
      note_text  TEXT          NULL ,
      PRIMARY KEY(note_id),
      FULLTEXT(note_text)      
    ) ENGINE=MYISAM;

*  全文本搜索\[FULLTEXT(v1, v2)\]
    
     *  FULLTEXT表示note\_text可以作为全文本搜索的索引。
     *  在定义之后，MySQL自动维护该索引。在增加，删除更新行时，索引随之自动更新
     *  一般在创建表时启用全文本搜索。最好不要在导入数据时使用FULLTEXT

### 进行全文本搜索 ###

> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(‘raBBit’); \#不区分大小写  
> SELECT `note_text` FROM `productnotes` WHERE `note_text` LIKE ‘%raBBit%’;

LIKE VS 全文本搜索

*  LIKE不对结果排序
 *  全文本搜索对结果排序，等级高的先返回
 *  全文本搜索根据索引搜索，相当快

> \#不包含raBBit的rank=0，包含的会根据文本算出等级  
> SELECT `note_text`, MATCH(`note_text`) AGAINST(‘raBBit’) AS rank FROM `productnotes`;

### 查询扩展：增加找到相关匹配的机会 ###

> \#查询所有提到anvils的注释  
> \#1、全文本搜索：返回包含anvils  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(‘anvils’); \#不区分大小写  
> \#2、查询扩展：返回包含使用全文本扩展查询出来的句子中的某些单词的行  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(‘anvils’ WITH QUERY EXPANSION);

### 布尔文本搜索\[不太懂\] ###

即使没有fulltext索引也可以用，但是很慢

> \#查询包含heavy的所有行  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(‘heavy’ IN BOOLEAN MODE);  
> \#至少包含rabbit或者heavy中的一个的行  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(‘rabbit heavy’ IN BOOLEAN MODE);  
> \#包含rabbit heavy的行。是匹配rabbit heavy一个词  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(’“rabbit heavy”’ IN BOOLEAN MODE);  
> \#查询包含heavy但是不包含任意以extre开始的词的行  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(‘heavy -extre\*’ IN BOOLEAN MODE);  
> SELECT `note_text` FROM `productnotes` WHERE MATCH(`note_text`) AGAINST(’+rabbit, +gua\*’ IN BOOLEAN MODE);

## 事务 ##

*  事务时需要在同一个处理单元中执行地一系列更新处理地集合。比如A给B转账。B增加地同时必须A减少。
 *  DBMS地事务遵循ACID特性，也就是
    
     *  原子性\[要么全部执行要么全部不执行\]
     *  一致性\[如果事务不满组数据库提前设置地约束，比如NULL约束就会回滚，事务不会被执行
     *  隔离性\[事务之间不会相互嵌套；在事务没有提交之前，其他事务看不到新添加地记录地\]\]
     *  持久性\[事务结束后，DBMS能够保证该时间点的数据状态会被保存的特性，即使系统故障导致数据丢失hi，也能通过一定手段恢复。最常见的方法就是将事务的执行记录保存到磁盘等存储介质中，。当发生故障时，可以通过日志恢复到故障发生前的状态\]
 *  事务并没有标准地开始执行，随着DBMS地不同而不同。

> –MySQL

START TRANSACTION;
    
        -- 运动T恤的销售单价下调1000日元
        UPDATE Product
           SET sale_price = sale_price - 1000
         WHERE product_name = '运动T恤';
    
        -- T恤的销售单价上浮1000日元
        UPDATE Product
           SET sale_price = sale_price + 1000
         WHERE product_name = 'T恤';
    
    COMMIT;

> –SQL Server, PostgreSQL

BEGIN TRANSACTION;
    
        -- 运动T恤的销售单价下调1000日元
        UPDATE Product
           SET sale_price = sale_price - 1000
         WHERE product_name = '运动T恤';
    
        -- T恤的销售单价上浮1000日元
        UPDATE Product
           SET sale_price = sale_price + 1000
         WHERE product_name = 'T恤';
    
    COMMIT;

> Oracle, DB2

-- 运动T恤的销售单价下调1000日元
        UPDATE Product
           SET sale_price = sale_price - 1000
         WHERE product_name = '运动T恤';
    
        -- T恤的销售单价上浮1000日元
        UPDATE Product
           SET sale_price = sale_price + 1000
         WHERE product_name = 'T恤';
    
    ROLLBACK;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: /images/20220328/a8d589d04b07413c8882403477d2f92c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]: /images/20220328/e535812702f0443ab710e681145839a1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 2]: /images/20220328/09d789ad5600485dbe5659b3a17297b4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 3]: /images/20220328/10e7289c7d2945a4b37db4737b0efeac.png
[20181207152538427.png]: /images/20220328/8967d5f65162431d8815de62c6e5dd0d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 4]: /images/20220328/83bd2ec535c849719e2ed0d34b2ad90b.png