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

数据库 专栏收录该内容
254 篇文章 1 订阅

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,是双闭区间[]
      • 两个临界值不要调换顺序
        在这里插入图片描述
  • 对字符串使用不等号时需注意:数据库对字符串型的数据原则上使用字典顺序排序, 比如1, 11, 123, 2, 22, 23, 4, 5。 这时比2大的字符应该是:22, 23, 4, 5
  • 不能对NULL使用比较运算符,只能用IS NULL、IS NOT NULL之类判定

②逻辑操作符

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

AND & OR & IN & NOT

  • AND
    在这里插入图片描述
  • OR
    在这里插入图片描述
    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子句中可以用任意个ANDOR来组织过滤条件,但是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的真值表。
    在这里插入图片描述

NOT

- WHERE中的NOT子句有且只有一个功能,就是否定它之后的所有的任何条件   MYSQL支持使用NOT对IN,BETWEEN和EXISTS子句取反,这与多数其他DBMS允许使用NOT对各种条件取反有很大的差别。
  • NOT不能单独使用,必须和其他查询条件组合起来使用。表示对条件取反。
    在这里插入图片描述

  • 请不要滥用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[首选的日期格式]
在这里插入图片描述

⑥ 数据类型转换

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;
  • 2
    点赞
  • 0
    评论
  • 19
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 黑客帝国 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值