使用阿里的云数据库RDS MySQL 学习SQL——Task01

SQL训练营学习教程链接：https://tianchi.aliyun.com/specials/promotion/aicampsql

本文是Task01的学习过程。

一、根据教程配置好mysql后，先创建了一个chars表

DROP TABLE IF EXISTS `chars`;
/* 数据库编码为 utf8mb4 ，数据库校对规则为 utf8mb4_0900_ai_ci，其中ci是case insensitive,代表对大小写不敏感  */
CREATE TABLE chars(
chr char(3) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
PRIMARY KEY(chr)
)ENGINE = INNODB DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

INSERT INTO chars VALUES ('1'),('10'),('11'),('2'),('22'),('3');

SELECT * FROM chars ;

二、根据教程内容学习的过程

INSERT INTO productins1 VALUES ('0001','钢笔','办公用品','1000','600','2009-09-01'),
('0002','打孔器','办公用品','500','320','2009-09-11'),
('0003','运动T恤','衣服','4000','2800',NULL),
('0004','菜刀','厨房用具','3000','2800','2009-09-21');
SELECT * FROM `productins1` 
 LIMIT 20;
 
UPDATE productins1
    SET rigest_date = '2009-09-10'
  WHERE product_name = '运动T恤';
ALTER TABLE productins1 ADD COLUMN regist_date DATE;

UPDATE productins1 
  SET regist_date = '2009-09-01' WHERE product_id = '0001';
UPDATE productins1 
  SET regist_date = '2009-09-11' WHERE product_id = '0002';
UPDATE productins1 
  SET regist_date = '2009-09-10' WHERE product_id = '0003';
UPDATE productins1 
  SET regist_date = '2009-09-21' WHERE product_id = '0004';

ALTER TABLE productins1 DROP COLUMN rigest_date;

SELECT * FROM productins1
LIMIT 10;

INSERT INTO productcopy(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;

ALTER TABLE productins CHANGE COLUMN purchase_date purchase_price INTEGER; 

INSERT INTO productins(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 productins1;

SELECT * FROM productins;

DROP TABLE productins1;

三、练习题

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)
);

ALTER TABLE addressbook ADD COLUMN post_code CHAR(8) NOT NULL;

DROP TABLE addressbook;

/*删除的表是无法通过语句恢复的，需要重新创建 */
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),
post_code CHAR(8) NOT NULL,
PRIMARY KEY(regist_no)
);