SQL编程-辣椒菠萝

目录

Task00/01：数据库环境搭建及初步了解（2天）1、win下环境安装，编辑器安装；

Task02：基础查询与排序

Task03：复杂一点的查询

Task04：集合运算

Task05：SQL高级处理

Task06：综合练习

---

 

Task00/01：数据库环境搭建及初步了解（2天）
1、win下环境安装，编辑器安装；

2、表结构：

* 列名：数据的项目名称
* 行/记录：一条记录 
* 列/字段：表中存储的数据项目
* 单元格：行和列交汇的地方称为单元格，一个单元格中只能输入一条记录
3、sql语句
* DDL（Data Definition Language，数据定义语言） 用来创建或者删除存储数据用的数据库以及数据库中的表等对象
 ** CREATE: 创建数据库和表等对象
 ** DROP: 删除数据库和表等对象
 ** ALTER: 修改数据库和表等对象的结构 
* DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录 
 ** SELECT: 查询表中的数据
 ** INSERT: 向表中插入新数据
 ** UPDATE: 更新表中的数据
 ** DELETE: 删除表中的数据
* DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定
 ** COMMIT: 确认对数据库中的数据进行的变更 
 ** ROLLBACK: 取消对数据库中的数据进行的变更 
 ** GRANT: 赋予用户操作权限
 ** REVOKE：取消用户的操作权限
4、练习题
练习题1.1
CREATE TABLE addressbook (
  regist_no INT (20) PRIMARY KEY,
  NAME VARCHAR (128) NOT NULL,
  address VARCHAR (256) NOT NULL,
  tel_no CHAR(10),
  mail_address CHAR(20)
)

练习题1.2
ALTER TABLE addressbook ADD COLUMN postal_code CHAR(8) NOT NULL 

练习题1.3
DROP TABLE addressbook

练习题1.4
无法恢复

 

Task02：基础查询与排序

练习题2.1

SELECT product_name 
,regist_date
FROM product 
WHERE  regist_date >'2009-04-28'

 

练习题2.2

①②③无结果  is null

练习题2.3

SELECT product_name,sale_price,purchase_price 
FROM product
WHERE sale_price-purchase_price >=500

练习题2.4

SELECT product_name,
product_type,
sale_price*0.9-purchase_price AS profit
FROM product 
WHERE sale_price*0.9-purchase_price>=100
AND product_type IN('办公用品','厨房用品');

练习题2.5

sum要是数字型、group by在最下面、group by 字段是product_id

练习题2.6

SELECT product_TYPE,SUM(sale_price) AS price,SUM(purchase_price) AS  purchase
FROM product 
GROUP BY product_type 
HAVING  sale - purchase*1.5 >=0

练习题2.7

时间倒序

Task03：复杂一点的查询

练习题3.1

CREATE VIEW  ViewPractice5_1 (product_name,sale_price,regist_date)
AS
SELECT  product_name,sale_price,regist_date
FROM product
WHERE sale_price >=1000
AND regist_date ='2009-09-20' 

练习题3.2

不能执行，视图只能alter或者update

练习题3.3

SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price)FROM product ) AS  sale_price_all
FROM product

练习题3.4

SELECT product_id,
product_name,
product_type,
sale_price,
(SELECT AVG(sale_price)FROM product p2 WHERE p1.product_type=p2.product_type GROUP BY product_type ) AS  avg_sale_price
FROM product  p1

练习题3.5

不对

练习题3.6

①purchase_price 不是500, 2800, 5000的结果

②purchase_price 不是 (500, 2800, 5000, NULL)的结果

没有取到数据

练习题3.7

SELECT count(case when sale_price <=1000 then product_name ELSE NULL END) AS low_price,
COUNT(case when sale_price >1000 AND sale_price <=3000 then product_name ELSE NULL END) AS  mid_price,
COUNT(case when sale_price>3000 then product_name ELSE NULL END) AS high_price
FROM product 

Task04：集合运算

表的加法union和连接之间的差别

内连接、左右连接、以及连接后再聚合的写法

练习题union

SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price<800
  
 UNION
 
SELECT  product_id,product_name,product_type
       ,sale_price,purchase_price
  FROM product 
 WHERE sale_price>1.5*purchase_price;

练习题UNION ALL

SELECT *
	FROM product
	WHERE sale_price/purchase_price < 1.5
	UNION ALL
	SELECT *
	FROM product
	WHERE sale_price < 1000;

练习题join

SELECT  SP.shop_id,SP.shop_name,SP.product_id 
       ,P.product_name, P.product_type, P.purchase_price
  FROM shopproduct  AS SP 
 INNER JOIN product AS P 
    ON SP.product_id = P.product_id
 WHERE P.product_type = '衣服';

练习题join  group by

SELECT SP.shop_id
      ,SP.shop_name
      ,MAX(P.sale_price) AS max_price
  FROM shopproduct AS SP
 INNER JOIN product AS P
    ON SP.product_id = P.product_id
 GROUP BY SP.shop_id,SP.shop_name

Task05：SQL高级处理

练习题5.1

product_id行，截止目前最大的slae_price

练习题5.2

SELECT  product_id
       ,product_name
       ,sale_price
       ,regist_date
       ,sum(sale_price) OVER (partition BY regist_date ORDER BY regist_date) AS slae_sum_price
  FROM product

练习题5.3

①不指定即不会按照某字段进行分组归类

②窗口函数，在order by上做的进一步运算，所以不会报错

Task06：综合练习

练习题6.1