【SQL笔记】Task1

SQL Task1

本笔记为阿里云天池龙珠计划SQL训练营的学习内容，链接为：https://tianchi.aliyun.com/specials/promotion/aicampsql；

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) #主键，唯一值
	);

drop table product; #删除表

alter table product add column product_name_pinyin varchar(4) not null; #添加列
alter table product add column product_name_pinyin varchar(4) not null default '0000'; #DB2中若设定not null需同时设置默认值


alter table product drop column product_name_pinyin; #删除列

truncate table product; #清空表并重新创建 delete删除表内容

-- 更新
update product
	set regist_date='2009-10-10'; #修改所有注册时间register_date
update product
	set sale_price=sale_price*10
	where product_type='厨房用具'; #修改指定商品的单价
update product
	set regist_date=null
	where product_id='0008'; #更新为null(清空)
	
update product
	set sale_price=sale_price*10,
			purchase_price=purchase_price/2
	where product_type='厨房用具'; #多列更新！
	
-- 插入数据
CREATE TABLE productins
(product_id    CHAR(4)      NOT NULL,
product_name   VARCHAR(100) NOT NULL,
product_type   VARCHAR(32)  NOT NULL,
sale_price     INTEGER      DEFAULT 0,
purchase_price INTEGER ,
regist_date    DATE ,
PRIMARY KEY (product_id)); 

-- 通常的INSERT（全列insert可省略表名后的列清单）
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11');
INSERT INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL);
INSERT INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');
-- 多行INSERT （ DB2、SQL、SQL Server、 PostgreSQL 和 MySQL多行插入）
INSERT INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11'),
															('0003', '运动T恤', '衣服', 4000, 2800, NULL),
															('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20');  
-- Oracle中的多行INSERT
INSERT ALL INTO productins VALUES ('0002', '打孔器', '办公用品', 500, 320, '2009-09-11')
					 INTO productins VALUES ('0003', '运动T恤', '衣服', 4000, 2800, NULL)
           INTO productins VALUES ('0004', '菜刀', '厨房用具', 3000, 2800, '2009-09-20')
	SELECT * FROM DUAL;  #DUAL是Oracle特有（安装时的必选项）的一种临时表A,无实际意义。
	
-- 插入null（创建表时没有not null的列）
INSERT INTO productins (product_id, product_name,product_type, sale_price, purchase_price, regist_date) 
	VALUES ('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20'); 

-- 插入默认值：创建表时
CREATE TABLE productins
(product_id    CHAR(4)      NOT NULL,
product_name   VARCHAR(100) NOT NULL,
product_type   VARCHAR(32)  NOT NULL,
sale_price     INTEGER      DEFAULT 0, #默认值设为0
purchase_price INTEGER ,
regist_date    DATE ,
PRIMARY KEY (product_id)); 
insert into productins (product_id, product_name,product_type, purchase_price, regist_date) 
	values ('0006', '叉子', '厨房用具', NULL, '2009-09-20');  #sale_price默认为0
	
-- 复制表（将表内信息插入其他表）
INSERT INTO productocpy (product_id, product_name, product_type, sale_price, purchase_price, regist_date)
SELECT product_id, product_name, product_type, sale_price, purchase_price, regist_date
FROM Product;

select @@autocommit; #查看事务提交状态 1自动提交 
set autocommit=0; #设置为手动提交
START TRANSACTION; #执行事务 begin
INSERT INTO product VALUES('0001', 'T恤衫', '衣服', 1000, 500, '2009-09-20');
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 INTO product VALUES('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');
INSERT INTO product VALUES('0006', '叉子', '厨房用具', 500, NULL, '2009-09-20');
INSERT INTO product VALUES('0007', '擦菜板', '厨房用具', 880, 790, '2008-04-28');
INSERT INTO product VALUES('0008', '圆珠笔', '办公用品', 100, NULL, '2009-11-11');
COMMIT; #提交事务

-- 练习：
create table `Addressbook`
	(redist_no integer not null,
	 name varchar(128) not null,
	 address varchar(256) not null,
	 tel_no char(10)
	 mail_address char(20)
	 primary key(regit_no)
	);

alter table Addressbook add column postal_code char(8) not null;

drop table Addressbook; #删除不可恢复，需重新创建