阿里云天池龙珠计划,让全世界学习者公平学习优质资源。
一、SQL训练营
12天24小时,6个任务,从MySQL基础到进阶操作,从零入门SQL。
参与学习的方式:自学
现在打卡学习的任务序号:Task01
练习:
cmd→
C:\Windows\system32>cd C:\mysql-8.0.27-winx64\bin
mysql -uroot -p;
create database ali_SQL_task01;
use ali_SQL_task01;
create table Addressbook(regist_no int 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 postal_code char(8) not null;
附:安装MySQL,配置环境
mysql8.0.27的安装及配置 - 彻夜不眠的星星 - 博客园 (cnblogs.com)
现在打卡学习的任务序号:Task02
create database ali;
use ali;
create table task2(product_id char(4) not null,product_name varchar(10) not null,product_type varchar(10) not null,sale_price int,purchase_price int,regist_date date,primary key(product_id));
insert task2 values('3','运动T恤','衣服',4000,2800,null),
('8','圆珠笔','办公用品',100, null,'2009-11-11'),
('6','叉子','厨房用具',500,null,'2009-9-20'), 注意只能插入null或者0,不能直接插入空字符串‘’
('1','T恤衫','衣服',1000,500,'2009-9-20'),
('4','菜刀','厨房用具',3000,2800,'2009-9-20'),
('2','打孔器','办公用品',500,320,'2009-9-11'),
('5','高压锅','厨房用具',6800,5000,'2009-1-15'),
('7','擦菜板','厨房用具',880,790,'2008-4-28');
select product_name,regist_date from task2 where regist_date >'2009-4-28';
如果用字段名=null查询,数据均为empty:因为判断NULL用is null 或者 is not null;而判断空字符串' ' ,用 =' ' 或者 <>' '。因此
SELECT * FROM product WHERE purchase_price = NULL;
SELECT * FROM task2 WHERE purchase_price = NULL;
SELECT * FROM task2 WHERE purchase_price <> NULL;
均得出Empty set (0.00 sec)
而SELECT * FROM task2 WHERE purchase_price is null;
SELECT * FROM task2 WHERE purchase_price is not null;
select*from task2 where purchase_price<>'' 意思是购价不为空的信息,与上条得出同样结果
返回均有数据。注意空值的''之间是没有空格的。
select product_name,sale_price,purchase_price from task2 where (sale_price - purchase_price>=500);
select product_name,sale_price,purchase_price from task2 where sale_price-500>=purchase_price;
select product_name,product_type,sale_price*0.9-purchase_price AS profit from task2 where sale_price*0.9-purchase_price >100 and (product_type='办公⽤品' or product_type='厨房⽤具');
涨知识:字符型字段 product_name 不可以进⾏ SUM 聚合;PRIMARY KEY (shop_id, product_id) -- 指定主键,由于单独使用商店编号(shop_id)或者商品编号(product_id)不能区分表中每一行数据,因此指定了 2 列作为主键(primary key)对商店和商品进行组合,用来唯一确定每一行数据。
select product_type,sum(sale_price),sum(purchase_price) from task2 group by product_type having sum(sale_price) >sum(purchase_price*1.5);
select *from task2 order by regist_date desc, sale_price;如果是空字符串,排序是升序在前,如果是null,排序是升序在后。
现在打卡学习的任务序号:Task03
create view ViewPractice5_1 (product_name,sale_price,regist_date) as select product_name,sale_price,regist_date from task2 where sale_price >=1000 and regist_date = '2009-9-20';
select product_id,product_name,product_type,sale_price,(select avg(sale_price) from task2)as sale_price_all from task2;
create view AvgPriceByType as select product_id, product_name, product_type,sale_price,(select avg(sale_price)from task2 as t1 where task2.product_type =t1.product_type group by task2.product_type)as avg_sale_price from task2;
select sum(case when sale_price <=1000 then 1 else