/*
#创建商品表：
pid int
pname varchar(20)
price double
category_id varchar(32)
插入一条数据:
pid=1,pname='联想',price=5000,category_id='c001'
*/
代码如下：
CREATE DATABASE day171;
USE day171;
CREATE TABLE product(
pid int,
pname varchar(20),
price double,
category_id varchar(32)
);
INSERT INTO product VALUES (1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');
INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);
面试题：having 和where 的区别
/*
简单查询
格式:
select 列名1,列名2 ... from 表名 where 条件
练习:
#1.查询所有的商品
#2.查询商品名和商品价格
#3.查询所有的价格(重复的只显示一个)
#4.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
*/
USE day171;
#1.查询所有的商品
SELECT pid,pname,price,category_id FROM product ;
#如果查询表中所有字段，可以使用*代替所有字段的名字
SELECT * FROM product;
#2.查询商品名和商品价格
SELECT pname ,price FROM product;
/*
扩展：
查询显示结果字段名,可以起别名
格式:
字段名 as '别名'
注意:
1.as可以省略
2.''可以省略,但是如果别名中有',就不能省略了
3.表名也是可以起别名的,但是不能写''
*/
SELECT pname AS '商品名称',price AS '商品价格'FROM product;
#as 可以省略（1）
SELECT pname '商品名称',price AS '商品价格' FROM product;
#''可以省略
SELECT pname 商品名称, price AS 商品价格 FROM product;
#表起别名
#注意
#SELECT pname 商品名称,price 商品价格 FROM product AS 'p';#错误的
SELECT pname 商品名称,price 商品价格 FROM product AS p ;#正确的
#as 可以省略（2）
SELECT pname 商品名称,price 商品价格 FROM product p ;#也是正确的
#3.查询所有的价格
SELECT price FROM product ;
#查询所有的价格（重复的只显示一个）
/*
上面的查询方式发现查询结果中有重复数据,2个5000,2个800
重复的只显示一个:
需要使用关键字: distinct
*/
SELECT DISTINCT price FROM product;
#4.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
#select 后面是可以写表达式的
SELECT 10+10,10-10,10/10;
#查询商品名称及价格（显示2次）
SELECT pname , price price FROM product;
#将所有商品的价格+10元进行显示
SELECT pname ,price +10 FROM product;
SELECT pname 商品名称,price 涨价前,price+10 涨价后 FROM product;
/*
条件查询:
select 列名1,列名2 ... from 表名 where 条件
条件:
between ... and ...: 显示在某一区间的值(含头含尾)
in(100,200): 等于100或者等于200
练习:
#查询商品名称为“花花公子”的商品所有信息
#查询价格为800商品
#查询价格不是800的所有商品
#查询商品价格大于60元的所有商品信息
#查询商品价格在200到1000之间所有商品
#查询商品价格是200或800的所有商品
*/
#查询商品名称为“花花公子”的商品所有信息
SELECT * FROM product WHERE pname = '花花公子';
#查询价格为800的商品
SELECT * FROM product WHERE price =800;
#查询价格不是800的所有商品
SELECT * FROM product WHERE price !=800;
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;
#查询商品价格再说200 到100之间所有商品
SELECT * FROM product WHERE price > 200 AND price <1000;
#查询商品价格是200或800的所有商品(两种表示方式)
SELECT * FROM product WHERE price = 200 OR price = 800;
SELECT * FROM product WHERE price IN (200,800);
/*
模糊查询
格式:
使用关键字 like
通配符:
1.%: 代表任意多个字符(0个,1个,2个...)
2._: 有且仅有1个字符
select 列名1,列名2 ... from 表名 where ... like ...
IS NULL: 判断是否为空
IS NOT NULL: 判断是否不为空
练习:
#查询含有'霸'字的所有商品
#查询以'香'开头的所有商品
#查询第二个字为'想'的所有商品
#查询没有分类的商品
#查询有分类的商品
*/
#查询含有‘霸’字的所有商品
SELECT * FROM product WHERE pname LIKE '%霸%';
#查询所有以‘香’字开头的所有商品
SELECT * FROM product WHERE pname LIKE '香%';
#查询第二个字为‘想’的所有商品
SELECT * FROM product WHERE pname LIKE '_想%';
#查询没有分类的商品
SELECT * FROM product WHERE category_id IS NULL ;
#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL ;
SELECT * FROM product WHERE NOT (category_id IS NULL );
/*
排序:
格式:
select ... from 表名 where 条件 order by 排序字段1 ASC|DESC,排序字段2 ASC|DESC;
注意:
1.ASC: 升序,不写默认就是ASC
2.DESC: 降序
3.order by: 一般写在sql语句的最后
练习:
#查询id 为1 3 5 7的商品 并按价格降序排列
#使用价格排序(降序)
#在价格排序(降序)的基础上，以分类排序(降序)
#显示商品的价格(去重复)，并排序(降序)
*/
#查询id为1 3 5 7的商品，并按价格降序排列
SELECT * FROM product WHERE pid IN (1,3,5,7) ORDER BY price DESC ;
#使用价格排序（降序）
SELECT * FROM product ORDER BY price DESC ;
#在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC ,category_id DESC;#价格相同时，按照分类id排序
#显示商品的价格（去重复），并排序（降序）
SELECT DISTINCT price FROM product ORDER BY price DESC;
SELECT pname,price FROM product GROUP BY price ORDER BY price DESC;
/*
聚合函数:
1.count：统计指定列不为NULL的记录行数
2.sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
3.max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
4.min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
5.avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
练习:
#1.查询商品的总条数
#2.查询商品的价格和
#3.查询价格大于200商品的总条数
#4.查询分类为'c001'的所有商品价格的总和
#5.查询分类为'c002'所有商品的平均价格
#6.查询商品的最大价格和最小价格
#7.查询pid为1 3 7 商品价格的平均值
#8.查询pid为1 3 7 14 商品价格的平均值
#9.统计指定列不为NULL的记录行数
*/
-- 1.count：统计指定列不为NULL的记录行数
SELECT COUNT(category_id) FROM product;#最后一个记录category_id的值是null，不统计
-- 2.sum：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
SELECT SUM(pname) FROM product;#pname列是varchar类型,结果0
-- 3.max：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
SELECT MAX（pname）FROM product ;#pname列是varchar类型，结果0
-- 4.min：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
SELECT MIN(pname) FROM product;#pname列是varchar类型，结果0
-- 5.avg：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
SELECT AVG (paname) FROM product ;
#1.查询商品的总条数
SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
#2.查询商品的价格和
SELECT COUNT(price) FROM product;
#3.查询价格大于200商品的总条数
SELECT COUNT(*)FROM product WHERE price >200;
SELECT COUNT(pid) FROM product WHERE price >200;
SELECT COUNT(price) FROM product WHERE price >200;
#4.查询分类为'c001'的所有商品价格的总和
SELECT price FROM product WHERE category_id ='c001';
SELECT SUM(price) FROM product WHERE category_id='c001';
#5.查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id='c002';
#6.查询商品的最大价格和最小价格
#价格可以显示多次
SELECT price,price FROM product;
SELECT MAX(price) AS '最大值',MIN(price) AS '最小值' FROM product;
#7.查询pid为1 3 7 商品价格的平均值
SELECT AVG(price) FROM product WHERE pid IN (1,3,7);#平均值：4000
#插入pid为14的商品
INSERT INTO product(pid,pname,price,category_id) VALUES(14,'卤煮',NULL,NULL);
SELECT * FROM product;
#8.查询pid为1 3 7 14 商品价格的平均值
SELECT AVG(price) FROM product WHERE pid IN(1,3,7,14);#price是null,不参与计算
#9.统计指定列不为NULL的记录行数
SELECT COUNT(category_id) FROM product WHERE category_id IS NOT NULL;
/*
练习:
#查看所有商品的平均价格
#查看所有商品的平均价格,所有商品的平均价格>800的话,就显示出所有商品的平均价格
#查询商品名称为“花花公子”的商品所有信息
注意:
1.where后面不能写聚合函数
2.having后面可以使用聚合函数
3.where 后面不能使用select中定义的别名
SQL语句书写顺序：select ... from... where.... group by... having... order by ...
SQL语句执行顺序：from... where...group by... having.... select ... order by...
*/
#删除price为null的记录
DELETE FROM product WHERE pid = 14;
#查看所有商品的平均价格
SELECT AVG (price) FROM product;
#查看所有商品的平均价格，所有商品的平均价格>800的话，就显示出所有商品的平均价格
SELECT AVG(price) FROM product WHERE AVG (price)>800;#错误，运行不了
SELECT AVG(price) FROM product WHERE price>800;#计算price>800的商品的平均价格，不满足条件
SELECT AVG(price) FROM product HAVING AVG(price)>800;#having后面可以使用聚合函数
#查询商品名称为“花花公子”的商品所有信息
SELECT * FROM product WHERE pname = '花花公子';
SELECT pid,pname,price,category_id FROM product WHERE pname='花花公子';
#起别名
SELECT pid AS 'no',pname AS 'pn',price AS 'pr',category_id AS 'cid'
FROM product WHERE pname = '花花公子';
SELECT pid AS 'no',pname AS 'pn',price AS 'pr',category_id AS 'cid' FROM product WHERE pname='花花公子';
SELECT pid AS 'no',pname AS 'pn',price AS 'pr',category_id AS 'cid' FROM product AS p WHERE p.pname='花花公子';
/*
学生成绩表scores:
sid: 学生成绩的id
score: 成绩
sname: 课程
*/
CREATE TABLE scores (
sid INT PRIMARY KEY,
score INT,
sname VARCHAR(200)
);
INSERT INTO scores(sid,score,sname) VALUES(1,90,'语文');
INSERT INTO scores(sid,score,sname) VALUES(2,70,'数学');
INSERT INTO scores(sid,score,sname) VALUES(3,90,'外语');
INSERT INTO scores(sid,score,sname) VALUES(4,50,'语文');
INSERT INTO scores(sid,score,sname) VALUES(5,90,'数学');
INSERT INTO scores(sid,score,sname) VALUES(6,80,'外语');
INSERT INTO scores(sid,score,sname) VALUES(7,100,'语文');
INSERT INTO scores(sid,score,sname) VALUES(8,20,'数学');
INSERT INTO scores(sid,score,sname) VALUES(9,100,'外语');
INSERT INTO scores(sid,score,sname) VALUES(10,75,'编程');
#查询所有
SELECT sname,score FROM scores;
/*
注意:
1.分组一般要和聚合函数一起使用
2.作为分组的字段,一般建议显示出来,方便查看数据
3.如果聚合函数作为条件,只能使用having
*/
/*
#统计每门课程的平均分
1.分组: 按照课程分组
2.聚合函数: 平均分 avg
*/
SELECT sname, AVG(score) FROM scores GROUP BY sname;
/*
#统计每门课程的平均分,且只显示平均分>70分的信息
1.分组: 按照课程分组
2.聚合函数: 平均分 avg
3.条件: 平均分>70,需要使用到聚合函数,只能使用having
*/
SELECT sname,AVG(score) FROM scores GROUP BY sname HAVING AVG(score)>70;
SELECT 2>1,2<1;#sql: true 1,false 0
/*
分组的练习
统计各个分类商品的个数
统计各个分类商品的个数,且只显示个数大于1的信息
统计各个分类商品的平均价格
统计各个分类商品的平均价格,且只显示平均价格>800的信息
*/
/*
统计各个分类商品的个数
1.分组: 按照商品分类id,category_id
2.聚合函数: 个数 count
*/
#删除category_id的值是null的记录
DELETE FROM product WHERE category_id IS NULL ;
#select category_id,count(*)from product where category_id is not null group by category_id;
SELECT category_id, COUNT(*) FROM product GROUP BY category_id;
/*
统计各个分类商品的个数,且只显示个数大于1的信息
1.分组: 按照商品分类id,category_id
2.聚合函数: 个数 count
3.条件: 个数大于1 用到聚合函数,必须使用having
*/
SELECT category_id,COUNT(*)FROM product GROUP BY category_id HAVING COUNT(*)>1;
SELECT category_id,COUNT(pid) FROM product GROUP BY category_id HAVING COUNT(pid)>1;
/*
统计各个分类商品的平均价格
1.分组: 按照商品分类id,category_id
2.聚合函数: 平均价格 avg
*/
SELECT category_id,AVG(price) FROM product GROUP BY category_id;
/*
统计各个分类商品的平均价格,且只显示平均价格>800的信息
1.分组: 按照商品分类id,category_id
2.聚合函数: 平均价格 avg
3.条件: 平均价格>800 用到聚合函数,必须使用having
*/
SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;
#起别名
SELECT category_id,AVG(price) AS 'ap' FROM product GROUP BY category_id HAVING ap>800;
/*
统计各个分类商品的平均价格
1.分组: 按照商品分类id,category_id
2.聚合函数: 平均价格 avg
*/
SELECT category_id,AVG(price) FROM product GROUP BY category_id;
/*
统计各个分类商品的平均价格,且只显示平均价格>800的信息
1.分组: 按照商品分类id,category_id
2.聚合函数: 平均价格 avg
3.条件: 平均价格>800 用到聚合函数,必须使用having
*/
SELECT category_id,AVG(price) FROM product GROUP BY category_id HAVING AVG(price)>800;
#起别名
SELECT category_id,AVG(price) AS 'ap' FROM product GROUP BY category_id HAVING ap>800;
/*
完成商品分类和商品信息表创建
步骤:
1.创建商品分类表
2.向商品分类表中添加数据
3.创建商品信息表
4.给商品信息表添加外键约束
5.向商品信息表中添加数据
*/
#1.创建商品分类表
CREATE TABLE category(
cid INT PRIMARY KEY,
cname VARCHAR(100)
);
#2.向商品分类表中添加数据
INSERT INTO category(cid,cname) VALUES(1,'家电');
INSERT INTO category(cid,cname) VALUES(2,'饮品');
INSERT INTO category(cid,cname) VALUES(3,'服装');
INSERT INTO category(cid,cname) VALUES(4,'水果');
SELECT * FROM category;
#3.创建商品信息表
CREATE TABLE products(
pid INT PRIMARY KEY,
pname VARCHAR(100),
price INT,
cid INT
);
#插入一条记录
#目前没有添加外键约束,所以cid的值可以随便写
INSERT INTO products(pid,pname,price,cid) VALUES(1,'可乐',3,1000);
SELECT * FROM products;
/*
4.给商品信息表添加外键约束
主表: 商品分类表
主表中的主键: 商品分类表中的cid
从表: 商品信息表
从表中的外键: 商品信息表中的cid
用商品分类表中的cid限制商品信息表中的cid
用主表的主键限制从表的外键
添加外键约束的格式: 通过修改从表表结构的方式
alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表中的主键)
*/
#删除从表中的无效数据
DELETE FROM products;
ALTER TABLE products ADD CONSTRAINT fk_p_cid FOREIGN KEY products(cid) REFERENCES category(cid);
#5.向商品信息表中添加数据
#目前已经添加外键约束,所以cid的值不可以随便写
#INSERT INTO products(pid,pname,price,cid) VALUES(1,'可乐',3,1000);
INSERT INTO products(pid,pname,price,cid) VALUES(1,'冰箱',8000,1);
INSERT INTO products(pid,pname,price,cid) VALUES(2,'彩电',7800,1);
INSERT INTO products(pid,pname,price,cid) VALUES(3,'洗衣机',5000,1);
INSERT INTO products(pid,pname,price,cid) VALUES(4,'娃哈哈',10,2);
INSERT INTO products(pid,pname,price,cid) VALUES(5,'冰红茶',3,2);
/*
商品信息和商品订单的创建
步骤:
1.创建商品订单表
2.向商品订单表添加数据
3.创建中间表
4.为中间表添加2个外键约束
5.向中间表中添加数据
*/
#1.创建商品订单表
CREATE TABLE orders(
oid INT PRIMARY KEY,
totalprice INT
);
#2.向商品订单表添加数据
INSERT INTO orders(oid,totalprice) VALUES (1,15800);
INSERT INTO orders(oid,totalprice) VALUES (2,13000);
INSERT INTO orders(oid,totalprice) VALUES (3,8010);
INSERT INTO orders(oid,totalprice) VALUES (4,13);
INSERT INTO orders(oid,totalprice) VALUES (5,7803);
SELECT * FROM orders;
#3.创建中间表
CREATE TABLE pro_ord(
pid INT,
oid INT
);
/*
4.为中间表添加2个外键约束
（1）.为中间表的pid添加外键约束
主表: 商品信息表
主表中的主键: 商品信息表 中的pid
从表: 中间表
从表中的外键: 中间表中的pid
添加外键约束的格式: 通过修改从表表结构的方式
alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表中的主键)
*/
ALTER TABLE pro_ord ADD CONSTRAINT fk_pro_ord_pid FOREIGN KEY pro_ord(pid) REFERENCES products(pid);
/*（2）.为中间表的oid添加外键约束
主表: 商品订单表
主表中的主键: 商品订单表 中的oid
从表: 中间表
从表中的外键: 中间表中的oid
添加外键约束的格式: 通过修改从表表结构的方式
alter table 从表名 add [constraint 外键约束名称] foreign key 从表名(从表中作为外键的字段) references 主表名(主表中的主键)
*/
ALTER TABLE pro_ord ADD CONSTRAINT fk_pro_ord_oid FOREIGN KEY pro_ord(oid) REFERENCES orders(oid);
#5.向中间表中添加数据
INSERT INTO pro_ord(pid,oid)
VALUES
(1,1),
(1,2),
(1,3),
(2,1),
(2,5),
(3,2),
(4,3),
(4,4),
(5,4),
(5,5);
/*
唯一约束: unique
一个表可以有多个
*/
/*
添加唯一约束方式一:
在定义表结构时,作为唯一约束的字段后面+unique
*/
#删除表
DROP TABLE category;
#创建分类表category
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100)UNIQUE
);
/*
添加唯一约束方式二:
在定义表结构时,已经指定了所有的字段,
在最后使用constraint关键字,添加唯一约束
格式:
[constraint 唯一约束名称] unique(作为唯一的字段名称)
注意:
[]里面的内容是可以省略的,如果要写,不能写[]
[]: 代表的是,里面的内容,作为可选项
*/
#删除表
DROP TABLE category;
CREATE TABLE category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100),
CONSTRAINT un_cname UNIQUE(cname)
);
/*
添加唯一约束方式三:
在定义完毕表结构后,通过修改表结构方式
格式:
alter table 表名 add [constraint 唯一约束名称] unique(作为唯一约束的字段名称)
注意:
[]里面的内容是可以省略的,如果要写,不能写[]
[]: 代表的是,里面的内容,作为可选项
*/
#删除表
DROP TABLE category;
DELETE FROM category;
#创建分类表category
CREATE TABLE category(
cid INT,
cname VARCHAR(100)
);
#通过修改表结构方式，添加唯一约束
ALTER TABLE category ADD CONSTRAINT qun_cname UNIQUE (cname);
/*
删除唯一约束:
格式:
alter table 表名 drop index 字段名或者唯一约束名;
如果指定了唯一约束名称,则必须通过唯一约束名称,来删除唯一约束
*/
ALTER TABLE category DROP INDEX qun_cname;
/*
删除唯一约束:
格式:
alter table 表名 drop index 字段名或者唯一约束名;
如果没有指定唯一约束名称,则必须通过字段名臣删除唯一约束
*/
#给cname添加唯一约束
ALTER TABLE category ADD CONSTRAINT UNIQUE (cname);
#通过字段名，删除该字段上具有的唯一约束
ALTER TABLE category DROP INDEX cname;