学习大数据，所需要的SQL基础（2）_大数据开发 面试 sql

单表查询

创建商品表

CREATE table product(
	pid INT PRIMARY KEY,
	pname VARCHAR(20),
	price DOUBLE
);
1
2
3
4
5

插入多条数据

INSERT INTO product(pid,pname,price) VALUES(1,'联想',5000);
INSERT INTO product(pid,pname,price) VALUES(2,'海尔',3000);
INSERT INTO product(pid,pname,price) VALUES(3,'雷神',5000);
INSERT INTO product(pid,pname,price) VALUES(4,'JACK JONES',800);
INSERT INTO product(pid,pname,price) VALUES(5,'真维斯',200);
INSERT INTO product(pid,pname,price) VALUES(6,'花花公子',440);
INSERT INTO product(pid,pname,price) VALUES(7,'劲霸',2000);
INSERT INTO product(pid,pname,price) VALUES(8,'香奈儿',800);
INSERT INTO product(pid,pname,price) VALUES(9,'相宜本草',200);
INSERT INTO product(pid,pname,price) VALUES(10,'面霸',5);
INSERT INTO product(pid,pname,price) VALUES(11,'好想你枣',56);
INSERT INTO product(pid,pname,price) VALUES(12,'香飘飘奶茶',1);
INSERT INTO product(pid,pname,price) VALUES(13,'果9',1);
1
2
3
4
5
6
7
8
9
10
11
12
13

聚合查询

1.聚合查询：纵向查询
2.聚合函数：
sum(列名) ：对指定列进行求和
count(列名)：统计指定列的个数
avg(列名)： 求指定列的平均值
max(l列名)：求指定列的最大值
min(列名)：求指定列的最小值
3.语法格式：
select 聚合函数（列名） from 表名 [where 条件]
统计product表中的总记录数

SELECT COUNT(0) FROM product;
SELECT COUNT(*) FROM product;
SELECT COUNT(1) FROM product;
SELECT COUNT(pid) FROM product;
1
2
3
4

当列出现NULL的时候，统计结构
对表cat进行查询

select count(cid) FROM cats;  #结果为2
select count(cname) FROM cats; #结果为3
select count(0) FROM cats; #结果为3
select count(1) FROM cats; #结果为3
1
2
3
4

其他聚合查询

SELECT SUM(price) '商品价值总和' FROM product;
SELECT AVG(price) '1,3,7价格的平均值' FROM product WHERE pid in(1,3,7);
SELECT MAX(price) '最高价格',MIN(price) '最低价格' FROM product;
1
2
3

分组查询

1.关键字：group by 列名
2.语法
select 聚合函数（列名） from 表名 group by 列名 having 条件
3.注意：
a.分组查询一般都是和聚合函数结合使用
b.分组小技巧：怎么确定按照哪个字段分组
相同的合并为一组
不同的单独为一组
c.where和having的区别
having是用在分组之后进行筛选的
where是在分组之前筛选的
书写sql语句关键字的顺序
select
from
where
group by
having
order by

执行顺序:
from
where
group by
having
select
order by

先定位到要查询哪一个表，然后根据什么条件去查，表确定好了，条件也确定好了，开始利用select从查询，查询得到一个结果，在针对这个结果进行一个排序

查询相同商品的价格总和

SELECT pname,SUM(price) FROM product group by panme;
1

查询相同商品的价格总和并排序
先查询还是先排序 ------- 先查询后排序
我们要先执行分组查询，然后根据分组查询之后的结果进行排序
我们查询之后的伪表价格列叫做 sum(price)
所以我们要按照price排序是无法排序的

#错误示例
SELECT pname,SUM(price) FROM product GROUP BY pname ORDER BY price;
#查询出来的伪表价格列叫啥，我们就按照啥去排序
SELECT pname,SUM(price) FROM product GROUP BY pname ORDER BY SUM(price);
SELECT pname,SUM(price) 'newprice' FROM product GROUP BY pname ORDER BY newprice;
1
2
3
4
5

查询相同商品的价格总和，在展示出价格总和大于等于2000的商品

##错误示例
SELECT pname,SUM(price) `newprice` FROM product WHERE newprice>=2000 GROUP BY pname ;
1
2

报错分析：因为where的执行顺序高于gourp by所以还没分组，就直接筛选出来了
正确示例

SELECT pname,SUM(price) `newprice` FROM product GROUP BY pname HAVING newprice >=2000;
1

分页查询

1.关键字：limit
2.语法：select 列名 from 表名 limit m,n
m 代表的是每页的起始位置
n:代表的是每页显示条数
3.小技巧：
将表中过的每一条数据看作加上了索引，从0开始
4.起始位置快速计算方式：
（当前页-1）* 每页显示条数
5.其他分页参数：
a.每页的起始位置
（当前页-1）* 每页显示条数
b.int curpage = 2 当前页数
c.int pagesize = 5 每页显示数量
d.int startRow = (curPage - 1) * pageSize; – 当前页, 记录开始的位置(行数)计算
e.int totalSize = select count(*) from products; – 记录总数量
f.int totalPage = Math.ceil(totalSize * 1.0 / pageSize); – 总页数
总页数 = (总记录数/每页显示条数)向上取整

##第一页
SELECT * FROM product LIMIT 0,5;
##第er页
SELECT * FROM product LIMIT 5,6;
##第三页
SELECT * FROM product LIMIT 10,5;
1
2
3
4
5
6

命令操作还原

mysql -u root -p 密码 数据库名 < 文件路径
注意 我们利用命令备份出来的sql文件中没有单独创建数据库的语句，所以如果利用命令去还原的话，需要我们自己动手先创建对应的库，命令后不要加分号；

利用点击去操作数据库的备份与还原

利用点击去备份

利用点击去还原

数据库三范式

好的数据库设计对数据的存储性能和后期的程序开发，都会产生重要的影响，建立科学的，规范的数据库就需要满足一些规则来优化数据的设计和存储，这些规则称为范式、

第一范式 确保每列保持原子性

第一范式（1NF）是最基本的范式，如果数据库中的所有字段值都是布克分解的原子值，就说明该数据库表满足了第一范式
第一范式的合理遵循需要根据系统的实际需求来定，比如某些数据库系统中需要用到地址这个属性，本来直接将地址属性设计成一个数据库表的字段就行，但是如果系统经常会访问地址属性中的城市部分，那么就需要将地址这个属性重新拆分成省份、城市、详细地址等多个部分进行存储
这样对地址中某以部分操作的时候将非常方便，这样设计才算满足数据库的第一范式，如下表所示

如果不遵守第一范式，查询出数据还需要进一步处理（查询不方便），遵守第一范式，需要什么字段的数据就查询什么数据（方便查询）

第二范式 确保表中的每行都能唯一分区

第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，既满足第二范式必须先满足第一范式，第二范式要求数据库中的每个实例或行必须可以被唯一的区分，为实现区分通常需要为表加一个列，以存储各个实例的唯一标识

第三范式 3NF非主键关键字不能相互依赖

比如student表，班级编号受人员编号的影响，如果在这个表中在插入班级的班主任，数学老师等信息，你觉得合适吗？肯定是不合适，因为学生有多个，这样就会造成班级有多个，那Z么每个班级的班主任、数学老师都会出现多条数据。而我们理想中的效果应该是一个班级信息对应一个班主任和数学老师，这样更易于我们理解

多表之间的关系

1.一对一：
人和身份证 ---- 人对应一个身份证 ----一个身份证对应一个人
2.一对多：分类表和商品表
a.分主表和从表
分类表中的数据限制着商品表中的数据
所以分类表为主表，商品表为从表
b.关系：
从主表出发王从表看：一个分类对用多个商品 ---- 一对多
反着看： 多个商品属于一个分类 ---- 多对一
c.总结 一对多关系
3.多对多：商品表和订单表
a.关系：
从商品表王订单表看：一个商品可以出现在多个订单中 ---- 一对多
反着看： 一个订单中可以包含多个商品 ------ 一对多
b.总结 多对多

创建外键约束

1.格式：alter table 从表 add [constraint 外键名称（自定义）] foreign key（外键列名） references 主表（主键列名）

一对多的表创建外键约束

## 创建数据库
CREATE DATABASE bigdata_03 CHARSET utf8;
use bigdata_03;

## 创建分类表 主表
CREATE TABLE category(
	cid VARCHAR(32) PRIMARY KEY,
	cname VARCHAR(50)
);
## 商品表 从表
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY,
	pname VARCHAR(50),
	price DOUBLE,
	category_id VARCHAR(32)  -- 外键存储的是主表的主键内容
)
## 添加外键约束
ALTER TABLE products ADD FOREIGN KEY products(category_id) REFERENCES category(cid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

多对多的表创建外键约束

## 创建商品表
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY,
	pname VARCHAR(50),
	price DOUBLE
);
## 创建订单表
CREATE TABLE orders(
	oid VARCHAR(32) PRIMARY KEY,
	totalprice DOUBLE
);
#订单项表 中间表
CREATE TABLE orderitem(
	pid VARCHAR(50),  -- 商品id --外键
	oid VARCHAR(50)   -- 订单id --
);


## 给商品表和订单项表创建外键约束
## 主表： 商品表 从表：订单项表
## 注意如果不自定义一个外键名称的话连续创建两个外键时，就会出现外键冲突
ALTER TABLE orderitem  ADD CONSTRAINT abc FOREIGN KEY orderitem(pid) REFERENCES products(pid);

## 给订单表和订单表项创建外键约束
ALTER TABLE orderitem ADD FOREIGN KEY orderitem(oid) REFERENCES orders(oid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

多表查询

建表，添加约束

##分类表
CREATE TABLE category(
	cid VARCHAR(32) PRIMARY KEY,
	cname VARCHAR(50)
);
#商品表
CREATE TABLE products(
	pid VARCHAR(32) PRIMARY KEY,
	pname VARCHAR(50),
	price DOUBLE,
	flag VARCHAR(2), #是否上架，1表示上架，0表示下架
	category_id VARCHAR(32) -- 外键
);
## 添加外键约束
ALTER TABLE products ADD CONSTRAINT products_fk FOREIGN KEY products(category_id) REFERENCES category(cid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

插入数据

 #分类
INSERT INTO category(cid,cname) VALUES('c001','家电');
INSERT INTO category(cid,cname) VALUES('c002','服饰');
INSERT INTO category(cid,cname) VALUES('c003','化妆品');
#商品
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p001','联想',5000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p002','海尔',3000,'1','c001');
INSERT INTO products(pid, pname,price,flag,category_id) VALUES('p003','雷神',5000,'1','c001');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p004','JACK JONES',800,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p005','真维斯',200,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p006','花花公子',440,'1','c002');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p007','劲霸',2000,'1','c002');

INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p008','香奈儿',800,'1','c003');
INSERT INTO products (pid, pname,price,flag,category_id) VALUES('p009','相宜本草',200,'1','c003');
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

交叉查询

1.语法：
select 列名 from 表A,表B
2.注意：交叉查询会出现笛卡尔乘积

## 交叉查询，查询所有商品的具体信息
SELECT COUNT(*) from category,products; 
SELECT * from category,products;  ## 查询结果为笛卡尔积
SELECT * from category c,products p WHERE c.cid = p.category_id;
1
2
3
4

笛卡尔乘积图

内连接查询

1.关键字： inner join on ---- inner可以省略
2.语法：
a.显示内连接：select 列名 from 表A join 表B on 条件
b.隐式内连接：select 列名 from 表A,表B where 条件

查询具体的商品信息 ---- 隐式内连接

SELECT * from category c,products p WHERE c.cid = p.category_id;
1

查询具体的商品信息 – 显示内连接

SELECT * FROM category c JOIN products p ON c.cid = p.category_id;
1

用显示内连接的方式拆线呢化妆品的商品信息

on 条件 where 条件 ---- 选了一个on的条件，再晒了一个where的条件
或者 on 条件 and 条件 两个条件合并成同一个大的条件 一起筛选

SELECT * FROM category c JOIN products p on c.cid = p.category_id where cname = '化妆品';
SELECT * FROM category c JOIN products p on c.cid = p.category_id AND cname = '化妆品';
1
2

外连接

1.关键字：outer join on — outer可以省略
2.语法：
左外连接：left join on
select 列名 from 表A left join 表B on 条件
右外连接：right join on
select 列名 from 表A right join 表B on 条件
3.如何区分左表和右表
在join左边的就是左表
在join右边的就是右表
4.左外连接，右外连接，内连接的区别
左：查询的是和右表的交集，以及左表的全部
右：查询的是和左边的交集，以及右表的全部
内：查询的是两个表的交集

#左外连接
SELECT * FROM category c LEFT JOIN products p on c.cid = p.category_id;
#右外连接
SELECT * FROM category c right JOIN products p on c.cid = p.category_id;
#内连接
SELECT * FROM category c,products p WHERE c.cid = p.category_id;
1
2
3
4
5
6

union联合查询实现全外连接查询（了解）

首先要明确，联合查询不是多变连接查询的一种方式，联合查询是将多余查询语句的查询结果合并成一个结果并去掉重复数据。联合查询是将多余条查询语句的查询结果合并成一个结果，并去掉重复数据。全外连接查询的意思就是将左表和右表的数据都查询出来，然后按照连接条件连接。
只要将两个结果一连接，左表和右表没有交叉的部分也就都查出来了
1.union的语法：
查询语句1 union 查询语句2 union 查询语句3…

## 全外连接
SELECT * FROM category c LEFT JOIN products p on c.cid = p.category_id
UNION
SELECT * FROM category c RIGHT JOIN products p on c.cid = p.category_id;
1
2
3
4

子查询

1.概述：一个查询语句作为另外一条查询语句的条件使用

查询products表中’化妆品’的商品信息

SELECT * FROM products WHERE category_id = (SELECT cid FROM category WHERE cname = '化妆品');
1

子查询作为伪表使用

1.注意：select 查询语句，查询出来的结果也是一个表，只不过是一张伪表，但是伪表也可以作为临时表使用参与查询

查询化妆皮的所有商品信息

#查询化妆品的所有商品信息
SELECT * FROM category c,products p where c.cid = p.category_id AND cname = '化妆品';
SELECT * FROM category WHERE cname = '化妆品';
SELECT * FROM (SELECT * FROM category WHERE cname = '化妆品') c,products p where c.cid = p.category_id AND cname = '化妆品';
1
2
3
4

#查询所有化妆品和家电的商品信息
SELECT * FROM category c,products p WHERE c.cid = p.category_id and(cname = "化妆品" or cname = '家电')
SELECT * FROM (SELECT * FROM category WHERE cname in ('家电','化妆品')) c, products p WHERE c.cid = p.category_id;
1
2
3