SQL必知必会2

已于 2023-08-08 20:31:35 修改
阅读量376 收藏
点赞数
分类专栏： sql 文章标签： sql 数据库 mysql
于 2018-02-28 23:06:14 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xjh163/article/details/79406578
版权

文章目录


在1中我们主要讲了在单个表上的查询，而在2中我们将会扩展到多个表中，以适应更广泛的作业

11.使用子查询

11.1子查询（subquery）

即嵌套在其他查询中是查询

11.2利用子查询进行过滤

SELECT order_num
FROM orderitems
WHERE prod_id='RGAN=01'


SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
										FROM orderitems
										WHERE prod_id='RGAN=01');
在SELECT中，子查询由内向外处理		

SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN(SELECT cust_id
								FROM orders
								WHERE order_num IN (SELECT order_num
																		FROM orderitems
																		WHERE prod_id='RGAN=01'));

作为子查询的SELECT语句只能查询单个列。企图查询多个列将返回错误

11.3作为计算字段使用子查询

从customers表中检索每个顾客的订单数

SELECT cust_name,
				cust_state,
				(SELECT COUNT(*)
				FROM orders
				WHERE orders.cust_id=customers.cust_id) AS orders
				--完全限制列名，告诉SQL比较orders表中的cust_id与customers表中的          
				cust_id相比较
FROM customers
ORDER BY cust_name;

对于这种问题有不止一种解决方案，JION也可以。

12.联结表

12.1联结

12.1.1关系表

举个例子，我们会将供应商信息与其供应的商品分开存放，因为每个供应商可能会有多种商品
1.统一供应商生存的每种商品，其供应信息是相同的，不必对这些信息重复录入
2.供应商信息发生变化，只需改动一次即可
我们的原则就是相同的数据出现多次并不是一件好事，关系型数据库正是基于此建立。
关系表的设计就是把数据分成多个表，一类数据一个表，不同 的表之间通过某些共同的值连接。

12.1.2为什么使用联结表

联结：用一条SELECT语句检索存储在多个表中的数据

12.2常见联结-指定联结的表及联结的方式

SELECT vend_id,prod_name,prod_price
FROM vendors,products
WHERE vendors.vend_id=products.vend_id;

12.2.1WHERE子句的重要性

WHERE子句作为过滤条件，只包含那些匹配给定条件（这里是联结条件）的行
若在联结中没有过滤条件，就会令两个表中的每一行都和另一个表中的每一行匹配，即为笛卡尔积，这是我们应该避免的。

12.2.2内联结

SELECT vend_id,prod_name,prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id=products.vend_id;

12.2.3联结多个表

SELECT prod_name,vend_id,prod_name,prod_price,quantity
FROM orderitems,vendors ,products
WHERE vendors.vend_id=products.vend_id 
AND orderitems.vend_id=products.prod_id
AND order_num=2007;
这个例子显示订单2007中的物品。

联结的表越多，性能下降的越厉害，不要联结不必要的表。一般DBMS对联结的表个数有限制。

返回11章中的例子
SELECT cust_name,cust_contact
FROM customers
WHERE cust_id IN(SELECT cust_id
								FROM orders
								WHERE order_num IN (SELECT order_num
																		FROM orderitems
																		WHERE prod_id='RGAN=01'));
用JOIN 改写
SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND prod_id='RGAN01';

13.创建高级联结

13.1对表使用别名

缩短语句；允许在一条SELECT语句总多次使用相同的表

对上个例子进行改写
SELECT cust_name,cust_contact
FROM customers AS C,orders AS O,orderitems AS OI
WHERE C.cust_id=O.cust_id
AND OI.order_num=O.order_num
AND prod_id='RGAN=01';

13.2使用不同类型的联结

自联结self jion 自然联结natural join 外联结outer join

13.2.1自联结

多次引用同一个表的子查询

SELECT C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_price
FROM customers AS C,orders AS O,orderitems AS OI
WHERE C.cust_id=O.cust_id
AND OI.order_num=O.order_num
AND prod_id='RGAN=01';

13.2.2外联结

SELECT customers.cust_id,orders.order_num
FROM customers LEFT OUTER JOIN orders
ON  customers.cust_id=orders.cust_id;
--左外联结，以左边的表为基准，右边的表多的舍去，少的为NULL

SELECT customers.cust_id,orders.order_num
FROM customers RIGHT OUTER JOIN orders
ON  customers.cust_id=orders.cust_id;
--右外联结，以右边的表为基准

SELECT customers.cust_id,orders.order_num
FROM customers  OUTER JOIN orders
ON  customers.cust_id=orders.cust_id;
--全外联结

13.3使用带聚集函数的联结

聚集函数与联结一起使用
检索所有顾客及每个顾客所下的订单个数

SELECT customers.cust_id
              COUNT(orders.order_num) AS num_ord
FROM custmers INNER JOIN orders
ON customers.cust_id=orders.cust_id
GROUP BY customers.cust_id;

14.组合查询

用UNION将多条SELECT语句组合成一个SELECT语句

14.1组合查询

适用情况
1.在一个查询中从不同的表返回结构
2.对一个表执行多个查询，按一个查询返回数据
任何具有多个WHERE子句的SELECT语句都可以作为一个组合查询

14.2创建组合查询

14.2.1使用UNION

SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')

SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_name='Fun4ALL';
上面的例子用UNION联结
SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
UNION
SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_name='Fun4ALL';

14.2.2UNION的使用规则

1.UNION中的每个查询包含相同的列，表达式或者聚集函数，但是各个列不要求以相同的顺序写出
2.列数据类型必须兼容，类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型和日期类型）

14.2.3包含或取消重复的行

SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
UNION
SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_name='Fun4ALL';
--去掉结果集中重复的行

SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
UNION ALL
SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_name='Fun4ALL';
--直接将两个结果集进行组合

14.2.4对组合查询结果排序

SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_state IN('IL','IN','MI')
UNION
SELECT cust_name,cust_contct,cust_email
FROM customers
WHERE cust_name='Fun4ALL';
ORDER BY cust_name,cust_contct,
--必须位于最后一条SELECT语句之后

15.插入数据INSERT

15.1数据插入

1.插入完整的行
2.插入一部分数据
3.插入某些查询结果
###15.1.1插入完整/部分行

INSERT INTO customers(cust_id,
					 cust_name,
				     cust_adress,
			         cust_name,
		             cust_city,
		             cust_zip)
VALUES('',
	   '',
	   '',
		'',
		'',
		'NULL')

–列名与插入的相应数据要对应，写入的列名可以不用对应原表中的列名顺序，如果对应的列名里没有要插入数据，写入NULL，
–也可以不用对一行的所有列都插入，插入哪些列，就写哪些列。

15.1.3插入检索的数据

INSERT INTO customers(cust_id,
										cust_name,
										cust_adress,
										cust_name,
										cust_city,
										cust_zip)
SELECT cust_id,
			cust_name,
			cust_adress,
	    	cust_name,
			cust_city,
        	cust_zip
 FROM custnew;
 --从custnew表中读出数据并插入customers表中

15.2从一个表复制到另一个表

 SELECT *
 INTO custcopy
 FROM customers;

16.更新和删除表

16.1更新数据UPDATE

1.更新特定行
2.更新所有行
不要省略WHERE子句
更新客户1000005的电子邮件

UPDATE customers
SET cust_email='kim@thetoystore.com'
WHERE cust_id='10000005';

更新多列

UPDATE customers
SET cust_email='kim@thetoystore.com'
        cust_contact='sam robert'
WHERE cust_id='10000005';

删除指定的列

UPDATE customers
SET cust_email=NULL
WHERE cust_id='10000005'

16.2删除数据DELETE

DELETE FROM customers
WHERE cust_id='10000005';

删除的是内容，而非表

16.3更新和删除的原则

1.一定要注意WHERE子句
2.保证每个表都有主键
3.在UPDATE和DELETE使用WHERE之前，一定要先用SELECT进行测试，保证过滤的是正确的记录，以防编写的WHERE的子句不正确
4.使用强制实施引用完整性的数据库，这样DBMS将不允许删除其数据与表相关联的行。
5.有的DBMS允许数据管理员施加约束，防止执行不带WHERE子句的UPDATE和DELETE.

17.创建和操作表

17.1

1.交互式界面创建
2.SQL语句创建

17.1.1表创建基础CREATE TABLE

1.新表的名字，在关键字CREATE TABLE 之后
2.表列的名字和定义，用逗号分隔

CREATE TABLE products
(
prod_id  CHAR(10)  NOT NULL,
prod_name  CHAR(254)  NOT NULL,
prod_price  DECIMAL(8,2)  NOT NULL,
prod_desc  VARCAR(1000)  NULL
);

指定表名时，若该表名已经存在，则需要先删除该表，而不是简单的覆盖

17.1.2使用NULL值

对于列的属性定义，NOT NULL即不允许该列出现空值，否则将会报错，列默认允许为NULL值。

17.1.3指定默认值

CREATE TABLE products
(
prod_id  CHAR(10)  NOT NULL,
prod_name  CHAR(254)  NOT NULL,
prod_price  DECIMAL(8,2)  NOT NULL DEFAULT 1,
prod_desc  VARCAR(1000)  NULL
);
--在插入的行如果不给出值，自动赋予默认值。

17.2更新表ALTER

ALTER对表的列及表名进行操作，UPDATE对表的行进行操作

ALTER TABLE vendors
ADD vend_phone CHAR(20);

ALTER TABLE vendors
DROP COLUMN  vend_phone;

17.3删除表

DROP TABLE custcopy;

17.4重命名表

RENAME TABL cusucpoy TO custnew;

18.使用视图

18.1视图

视图是虚拟的表，与包含数据的表不一样，视图只包含使用时动态检索数据的查询。
这是12章的一个例子，查询订购了某种商品的顾客

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
AND prod_id='RGAN01';

现在，假如我们可以把整个查询包装成一个名为productcustomers的虚拟表，就可以对上述查询进行简化

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id='RGAN01';

18.1.1为什么使用视图

1.重用SQL语句
2.简化复杂的SQL操作
3.使用表的一部分而不是整个表
4.保护数据，授予用户访问表的特定部分的权限，而非整个数据。
5.更改数据格式额表示。视图可返回与底层表的表示和格式不同的数据
视图是一种查看储存在别处的数据的一种设施，视图本身不含任何数据，只是从别处查询数据，在添加和更改这些表中的数据时，将会返回更改过的 数据

18.1.2视图的规则和限制

1.视图必须唯一命名
2.创建视图的数据没有限制
3.必须有足够的访问权限
4.视图可以嵌套，即可以利用从其他视图检索出的数据来构建视图
5.在视图查询中禁止使用ORDER BY 子句
6.有些DBMS要求对返回的列进行命名，如果列是计算字段，需要使用别名
7.视图不能索引，也不能有关联的触发器或默认值
8.有些DBMS把视图作为只读的查询，这表示可以从视图检索数据，但不能将数据写回底层表

18.2创建视图

CREATE VIEW 同样必须先删除相同的视图，再进行覆盖

18.2.1利用视图简化复杂的联结

对于上述例子，productcustomers怎么创建

CREATE VIEW productcustomers AS
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id=orders.cust_id
AND orderitems.order_num=orders.order_num
--这个视图联结了三个表，返回已订购了任意产品的所有顾客的列表

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id='RGAN01';

18.2.2用视图重新格式化检索出的数据

第7章例子

SELECT RIRIM(vend_name) + ‘（‘+RIRIM(vend_country)+’）’
               AS  vend_title
FROM vendors
ORDER BY vend_name;

现在，假设我们经常需要这个格式化的结果，不必每次在需要的时候都进行这种拼接，而是创建一个视图，使用它即可。把此语句转化为视图。

CREATE VIEW vendorlocations AS
SELECT RIRIM(vend_name) + ‘（‘+RIRIM(vend_country)+’）’
               AS  vend_title
FROM vendors;

SELECT * FROM vendorlocations;
--结果与第七章相同

18.2.3用视图过滤不想要的数据

CREATE VIEW customersemaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

SELECT *
FROM customersemaillist;

18.2.4使用视图与计算字段

第7章例子

SELECT prod_id,
               quality,
               item_price,
               quantity*item_price AS expanded_price
FROM orderitems
WHERE order_num=20008;

利用视图重构
CREATE VIEW orderitemexpanded AS
SELECT order_num,
               prod_id,
               quality,
               item_price,
               quantity*item_price AS expanded_price
FROM orderitems;

SELECT *
FROM orderitemexpanded
WHERE order_num=20008;
用数据说话
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
SQL 必知必会
m0_59761642的博客
09-20 588
SQL语法与案例
SQL必知必会笔记（二）
zhangzhangchenchu的博客
01-29 263
#过滤数据（where having） SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=4 GROUP BY vend_id HAVING COUNT(*) >=2; #过滤数据（group by having） SELECT vend_id,COUNT(*) AS num_prods ...
如何使用GROUP BY和聚合函数（如SUM, AVG）来查询orders表中每个客户的订单总额和平均订单额
最新发布
chengede98的博客
02-27 955
使用GROUP BY和聚合函数（如SUMAVG）来查询orders表中每个客户的订单总额和平均订单额是一个常见的数据库操作。以下是一个详细的解释和示例，但请注意，由于具体的数据库表结构和字段可能有所不同，所以你可能需要根据实际情况调整SQL查询。
SQL中的非重复计数（进阶）
qq_33909788的博客
10-29 1285
当您需要从数据库中获取有关客户数量的信息时，常见的需求是按照不同的条件进行不重复计数。例如，您可能需要计算购买过某个产品的客户数量，或者按照不同的地理位置计算客户数量。在SQL Server中，您可以使用COUNT函数和DISTINCT关键字来实现这些需求。本文将介绍如何使用SQL Server进行按客户不重复计数，以及如何按条件统计不同口径的不重复计数。我们将提供SQL查询示例来帮助您快速理解和应用这些技术。
SQL-每日一题【1045.买下所有产品的客户】
lucky_1314520的博客
07-29 711
CustomerProduct表：写一条 SQL 查询语句，从Customer表中查询购买了Product表中所有产品的客户的 id。
利用sql语句来统计用户登录数据的实践
cooldream2009的博客
11-29 1436
当需要对用户登录情况进行统计时，SQL是一个非常强大的工具。通过SQL，可以轻松地从数据库中提取和汇总数据，并以适合分析和报告的方式进行呈现。
MySQL 实验二-1——数据基本查询（单表）
此人很懒
10-14 457
10．查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序排列；10．查询全体会员的会员帐号，姓名和年龄并按家庭地址升序排列，同一地址中的会员按年龄降序排列；11．查询会员帐号为‘liuzc’的会员所购买的商品号和订购日期，并按订购日期升序排列；13．查询2005年8月6日前，所有商品的订购总量，要求输出商品号和订购总量；16．查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量。16．查询各类商品的最高购买数量，要求输出最高数量大于10的商品号和最高数量。
SQL必知必会
10-22
SQL必知必会（第4版） (图灵程序设计丛书 80) - [美]Ben Forta.mobi
SQL 必知必会.pdf
01-16
2. SQL 数据类型 SQL 语言支持多种数据类型，包括： * 数字类型：int、smallint、tinyint、float、double 等。 * 字符串类型：varchar、char、text 等。 * 日期类型：date、datetime、timestamp 等。 * 布尔类型：...
SQLServer编程必知必会
07-23
资源名称：SQL Server编程必知必会内容简介：SQL Server是世界上最受欢迎的数据库管理系统之一。本书是作者继经典畅销书《SQL必知必会》之后，应众多读者的请求编写的，专门针对SQL Server T-SQL用户。书中继承了...
SQL.zip_SQL必知必会
09-24
"SQL.zip_SQL必知必会"这个压缩包文件显然包含了关于SQL的基础知识和核心概念，帮助学习者掌握SQL语言的基本用法和技巧。以下是基于"SQL必知必会.txt"文件可能涵盖的一些关键知识点的详细说明： 1. **SQL概述**：...
SQL必知必会 第4版
03-17
《SQL必知必会》第四版是一本深受程序员和数据分析师喜爱的经典教程，它全面而深入地介绍了SQL语言，帮助读者掌握查询、管理和分析数据库的关键技能。这本书以其清晰的讲解和丰富的实例，使得无论是初学者还是有经验...
Mysql查询
qq_67308031的博客
04-13 1797
文章目录1.创建 goods表2.查询数据表查询倍数（*几倍数）使用AS建别名查询表中为**null的用户# ** or **查询goods表中tid等于3或gdPrice小于等于50的商品类别id、商品名称和价格查询good表中gdprice不大于50的商品名称查询goods表中gdcity值为“长沙”或“北京”，且gdprice小于等于50的商品名称#or and一起用容易出错所以要加括号 and优先于or查询goods表中gdprice在100到500的商品名称模糊查询SELECT 列 from 表
SQL学习笔记11- 子查询
weixin_48635062的博客
03-16 891
子查询进行过滤 各订单的物品的表包含在OrderItems表中，顾客的ID存储在Orders表中，顾客的信息包含在Customers表中。如果要列出订购物品RGAN01的所有顾客，应该怎么检索？ 下面给出步骤： 1.检索包含物品RGAN01的所有订单编号 2.检索具有前一步骤列出的订单编号的所有顾客ID 3.检索前一步骤返回的所有顾客ID的顾客信息 我们可以分3个SELECT来查询 SELECT order_num FROM OrderItems WHERE order_num='RGAN0
数据库查询所有顾客的订单编号、顾客编+号、顾客姓名、订单时间怎么写命令语...
weixin_42599908的博客
02-10 1018
下面是一种可能的写法： SELECT orders.order_id, customers.customer_id, customers.name, orders.order_time FROM orders INNER JOIN customers ON orders.customer_id = customers.customer_id 这条查询会从 orders 表和 customers ...
MySQL GROUP BY与聚合函数--作业记录
weixin_49345320的博客
03-25 1366
GROUP BY <字段名> 其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开 GROUP BY单独使用 单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。 例 1 下面根据 tb_students_info 表中的 sex 字段进行分组查询，SQL 语句和运行结果如下： mysql> SELECT.
SQL快速入门 | 春松客服
Chatopera
04-27 1129
目录1.SQL简介SQL介绍SQL组件SQL历史MySQL历史前提准备2.SQL基本语法3.延伸阅读开源智能客服系统 1.SQL简介 SQL介绍 Structured Query Language简称SQL，是一种操作数据库的语言。它包括数据库创建，删除，获取行，修改行等。SQL是结构化查询语言，它是一种用于存储，处理和检索关系数据库中存储的数据的计算机语言。 SQL是关系数据库系统的标准语言。所...
odps sql 日期函数处理
热门推荐
用数据说话
04-06 2万+
select to_char(‘2019-12-21 07:05:26’,‘yyyymmdd hhmm’) select to_char(‘2019-12-21 07:05:26’,‘yyyymmdd hhmi’) odps 删除某列 用新的表付覆盖 CREATE TABLE temp_DEVICE_STATUS_FRONT_APP_1_delete_column AS SELECT did...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值