2021-08-20

task02

task02有参考大佬们的学习笔记，但也有也会补充自己的学习笔记
1.创建数据库、表
CREATE DATABASE shop;

CREATE TABLE product (
product_id CHAR ( 4 ) NOT NULL,
product_name VARCHAR ( 100 ) NOT NULL,
product_type VARCHAR ( 32 ) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY ( product_id ));
2.表的删除
高危操作，请三思才确认此操作
DROP TABLE < 表名 > ;

删除 product表

DROP TABLE product;
3.列的添加
ALTER TABLE 语句

语法

ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >;

添加一列可以存储 100 位的可变长字符串的 product_name_pinyin 列

ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
添加前
image

添加后
image
4.列的删除
高危操作，请三思才确认此操作

语法

ALTER TABLE < 表名 > DROP COLUMN < 列的定义 >;

• 删除刚刚添加的列 product_name_pinyin
  ALTER TABLE product DROP COLUMN product_name_pinyin;
  5.清空表的内容
  优点：相比 Drop / Delete，Truncate 用来清除数据时，速度最快。
  TRUNCATE TABLE TABLE_NAME;
  测试数据插入
  – 通常的INSERT
  INSERT INTO product VALUES (‘0002’, ‘打孔器’, ‘办公用品’, 500, 320, ‘2009-09-11’);
  INSERT INTO product VALUES (‘0003’, ‘运动T恤’, ‘衣服’, 4000, 2800, NULL);
  INSERT INTO product VALUES (‘0004’, ‘菜刀’, ‘厨房用具’, 3000, 2800, ‘2009-09-20’);

– 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO product VALUES (‘0002’, ‘打孔器’, ‘办公用品’, 500, 320, ‘2009-09-11’),(‘0003’, ‘运动T恤’, ‘衣服’, 4000, 2800, NULL),(‘0004’, ‘菜刀’, ‘厨房用具’, 3000, 2800, ‘2009-09-20’);
image

6.数据的更新

语法：

UPDATE <表名>
SET <列名> = <表达式> [, <列名 2 >=<表达式 2 >…];
WHERE
<条件>;-- 可选，非常重要。
ORDER BY;
<子句>; --可选
LIMIT
<子句>; --可选
使用 update 时要注意添加where 条件，否则将会将所有的行按照语句修改

修改前的数据
image

– 修改所有的注册时间
UPDATE product
SET regist_date = ‘2009-10-10’;
修改后的数据，可见时间栏都修改成2009-10-10
image
– 仅修改部分商品的单价
UPDATE product
SET sale_price = sale_price * 10
WHERE
product_type = ‘厨房用具’;
条件为厨房用具的 sale_price增加10倍了，由千更改成3万
image

使用 UPDATE 也可以将列更新为 NULL（该更新俗称为 NULL 清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可

例如我们将上一次更改的3万重置成空

UPDATE product
SET sale_price = NULL
WHERE
product_type = ‘厨房用具’;
image

多列更新
UPDATE 语句的 SET 子句支持同时将多个列作为更新对象。

将办公用品的sale_price字段修改成80，purchase_price修改成800

UPDATE product set sale_price=50 WHERE product_type=‘办公用品’;
UPDATE product set purchase_price=800 WHERE product_type=‘办公用品’;
上面的写法比较繁琐
image

那我们是否可以整合成一条语句？

UPDATE product
SET sale_price = 50 ,purchase_price = 800
WHERE
product_type = ‘办公用品’;
【作业】
image
CREATE TABLE Addressbook (
regist_no INTEGER NOT NULL,
NAME VARCHAR ( 128 ) NOT NULL,
address VARCHAR ( 256 ) NOT NULL,
tel_no CHAR ( 10 ),
mail_address CHAR ( 20 ),
PRIMARY KEY ( regist_no ));
image
image
ALTER TABLE Addressbook ADD postal_code CHAR(8) NOT NULL;
image

image

DROP TALBE Addressbook;
image

删除操作不可逆，只能安装步骤1进行重新建表
1.SELECT语句
比较简单，一般在线上环境尽可能不select * from 表名 后面还不接where条件。
SELECT <列名> FROM <表名>;
SELECT <列名> FROM <表名> WHERE <条件表达式>;
设定汉语别名时需要使用双引号（"）括起来。
SELECT <列名> AS “别名” FROM <表名>;
插入一行测试数据
INSERT INTO shop.product (product_id, product_name, product_type, sale_price, purchase_price, regist_date) VALUES (‘0005’, ‘笔’, ‘办公用品’, 100, 200, ‘2009-10-11’)
image

使用DISTINCT删除product_type列中重复的数据
SELECT product_type FROM product;
image

SELECT DISTINCT product_type FROM product;
image

参考参考大佬们的笔记，后续继续补充自己的，加油加油。

练习题3.1作答：
SELECT product name, regist_date
FROM product
WHERE regist_date > ‘2009-4-28’

练习题3.2作答：
Line1：找出product表中purchase_price为NULL的所有数据
Line2：找出product表中purchase_price不等于NULL的所有数据
Line3：找出product表中purchase_price大于NULL的所有数据

练习题3.3作答：
第一种：
SELECT product_name, sale_price, purchase_price
FROM product
WHERE sale_price - purchase_price > 500
ORDER BY sale_price;
第二种：
SELECT product_name, sale_price, purchase_price
FROM product
WHERE NOT sale_price - purchase_price <= 500
ORDER BY sale_price;

练习题3.4作答：
SELECT product_name,
product_type,
sale_price * 0.9 AS profit
FROM product
WHERE product_name = ‘办公用品’
OR product_name = ‘厨房用具’
AND ((sale_price* 0.9 - purchase_price ) > 100);

练习题3.5作答：
1、WHERE语句使用在GROUP BY之前，分号放在GROUP BY结尾
2、SUM函数只适用数值类型的列，product_name不是数值类型
3、SUM使用的括号为中文，改成英文括号

练习题3.6作答：
SELECT product_type,
SUM(sale_price) AS sum,
SUM(purchase_price) AS sum
FROM product
GROUP BY product_type
HAVING product_type = ‘办公用品’
OR product_type = ‘衣服’
AND SUM(sale_price) > SUM(purchase_price * 1.5);

练习题3.7作答：
SELECT *
FROM product
ORDER BY regist_date DESC