[大数据]SQL数据库

目录

1. 数据库相关概念

1.1 什么是数据库

1.2 分类

1.3 数据库服务器、数据库和标的关系

1.4 基本操作

2. SQL语言

2.1 DDL

2.2 DML

2.3 DQL(简单)

3. 数据完整性

3.1 分类

3.2 表的约束

4. 多表设计

4.1 多个表的关系

4.2 对表的基本操作

4.3. DQL(复杂)

1. 数据库相关概念

1.1 什么是数据库

? ? ? ? ① 数据库是一个实体，是能合理保管数据的“仓库”；

? ? ? ? ② 数据库是一种技术和方法，能够合理地组织、维护、严密控制和利用数据。

1.2 分类

关系型数据库：是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。

非关系型数据库(NoSQL)：NoSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，特别是大数据应用难题。数据之间无关系，非常容易扩展。

1.3 数据库服务器、数据库和标的关系

1.4 基本操作

-- 查看当前所有的数据库
show databases;
-- 查看当前工作数据库
select database();
-- 切换数据库
use + 数据库的名字
-- 查看当前数据库下的所有表
show tables;
-- 退出
exit;
quit;
Ctrl + D;

2. SQL语言

是一种操作、定义、管理关系数据库的句法。大多数关系型数据库都支持。包括：

DDL：数据定义语言? ? ? DML：数据操作语言? ? ?DQL：数据查询语言

DCL：数据控制语言? ? ? TPL：事务处理语言? ? ? CCL：指针控制语言

2.1 DDL

创建数据库和表的结构。 INSERT、UPDATE、DELETE

-- 创建数据库
create database test;
-- 修改数据库结构
alter database test character set = UTF8;
-- 删除数据库
drop database test;

-- 创建表
create table heroes(name char(10), equipment char(10),evaluate float);
-- 查看表结构
create table heroes;
desc heroes

-- 修改字段
alter table heroes modify name char(8);                         --只能修改结构
alter table heroes change name username char(8);                --只能修改内容
-- 插入列
alter table heroes add position char(5) default 'mid';
-- 修改表名
alter table heroes rename to hero;
rename table hero to heroes;
-- 删除表中的某一列
alter table heroes drop position;
-- 插入一行到表头
alter table heroes add ID int(4) first;
-- 插入一行到某字段之后
alter table heroes add position char(5) after username;

-- 删除表
drop table heroes;

NOTICE: ① 表：字段 ?= ?列 ? = ?域

?????????????????② char(size):固定长度，浪费空间，但访问速度快；varchar(size):可变长，特点反之

2.2 DML

-- 插入数据 --
-- 输入列名，可插入部分数据
insert into heroes(ID,username,position,equipment,evaluate) values(001,'Yue','FAM','xiapoxiao',9.8);
-- 省略列名，值必须输完整
insert into heroes values(002,'Xing','MID','shengbei',7.6);
-- 插入空值
insert into heroes (ID) values (NULL);
--插入多行
insert into heroes values(003,'Lan','JUD','knife',8.2),(004,'Pig','CON','armor',6.6),(005,'Fei','SUP','baoshi',6.6),(006,'Kai','CON','sword',9);

-- 修改数据 --
update heroes set position='JUG' where username='Lan';
update heroes set position='JUG', equipment='axe' where username = 'Kai';

-- 删除数据 --
delete from heroes where position='CON'
-- 删除空数据（必须用is）
delete from heroes where ID is NULL;

2.3 DQL(简单)

-- 查询某一列
select position from heroes;
-- 查询多列
select username,position from heroes;
-- 查询结果去重显示
select distinct position from heroes;
-- 查询结果做表达式运算
select username,evaluate*10+3 from heroes;           --显示的表为临时表
-- 给查询结果起别名
select username,evaluate*10 as total from heroes;

-- 查询特定数据 --
-- 运用比较运算符
select *from heroes where evaluate > 8;
select *from heroes where evaluate < 8;
select *from heroes where evaluate <> 9;
select *from heroes where evaluate != 9;
select *from heroes where ID in(1,2,3);
-- 模糊查找
select *from heroes where evaluate between 6 and 8;
select *from heroes where position like 'M%';

-- 运用逻辑运算符
-- 查询符合多个条件的数据 and
select *from heroes where position='JUG' and evaluate<9;
-- 查询符合任一条件的数据 or
select *from heroes where position='JUG' or evaluate<9;
-- 查询不符合条件的数据 not
select *from heroes where not position='JUG';
-- 联合查询
select *from heroes where position='MID' or position='JUG' and evaluate <9;

-- 对查询结果排序 -- 
-- 升序
select *from heroes order by evaluate;
-- 降序
select *from heroes order by evaluate desc;
-- 只显示前n行
select *from heroes order by evaluate desc limit 3;
-- 只显示指定的行
select *from heroes order by evaluate desc limit 2 offset 1;

3. 数据完整性

保证插入到数据库中的数据是正确的。

3.1 分类

? 实体完整性：规定表中的一行在表中是唯一的实体，避免重复数据浪费空间。

? 域完整性：保证列符合特定的数据类型或约束。

? 参照完整性：保证一个表的外键和另一个表的外键对应，关系中不允许引用不存在的实体。

3.2 表的约束

? 主键约束:? ?primary key不允许为空，不允许重复，唯一

? 唯一约束:? ?unique

? 外键约束:? ?foreign key

4. 多表设计

4.1 多个表的关系

• 一对多：节约空间，避免数据冗余
• 多对多：节约空间，避免数据冗余
• 一对一：方便扩展成一对多、多对多

4.2 对表的基本操作

-- 创建表 --
-- 英雄表
mysql> create table heroes(
    -> ID int auto_increment,
    -> name char(6),
    -> position char(3),
    -> primary key(ID)
    -> )
    -> ;
-- 装备表
mysql> create table equipment(                                                     
    -> ID int auto_increment primary key,
    -> name char(10) not NULL,
    -> price int,
    -> hero_ID int,
    -> foreign key (hero_ID) references heroes(ID)
    -> );

select* from equipment;
+----+--------+-------+---------+
| ID | name   | price | hero_ID |
+----+--------+-------+---------+
|  1 | knife  |   250 |       3 |
|  2 | hat    |  2300 |       2 |
|  3 | wujin  |  2140 |       1 |
|  4 | poxiao |  3400 |       1 |
|  5 | baoshi |   300 |       4 |
+----+--------+-------+---------+

select *from heroes;
+----+-------+----------+
| ID | name  | position |
+----+-------+----------+
|  1 | Yue   | FAM      |
|  2 | Xing  | MID      |
|  3 | Jing  | JUG      |
|  4 | Qiao  | SUP      |
|  5 | Yun   | JUG      |
|  6 | Waner | MID      |
|  7 | Bai   | JUG      |
+----+-------+----------+

-- 不允许引用不存在的实体
insert into equipment values(1,'knife',250,10);

-- 不允许删除被外键关联的表 --->违背参照完整性
delete from heroes where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key 
constraint fails (`test`.`equipment`, CONSTRAINT `equipment_ibfk_1` 
FOREIGN KEY (`hero_ID`) REFERENCES `heroes` (`ID`))

-- 可以删除表中没有被外键关联的数据
delete from heroes where id=7;

-- 复制表 --
create table hero1 select* from heroes;         -- 只能复制表中数据
create table hero2 like heroes;                 -- 只能复制表中结构

-- 将列设为主键
alter table hero1 modify ID int primary key;

-- 删除主键
alter table hero1 drop primary key;

-- 添加外键
alter table equipment1 add foreign key(hero_ID) references heroes(ID);

-- 删除外键
alter table equipment1 drop foreign key equipment1_ibfk_1;    --外键名

4.3. DQL(复杂)

多表查询语言

• 连接查询?交叉连接(cross join) 不带on子句，返回连接表中所有数据行的笛卡儿积

? ? ? ? ? ? ? ? ? ? ? ?内连接(inner join)? 返回连接表中符合连接条件及查询条件的数据行。

??????????????? ? ? ? ?外连接 分为左外连接(left outer join)? 右外连接(right outer join)

• 联合查询
• 报表查询

4.3.1 连接查询

-- 交叉连接cross join
select * from equipment cross join heroes;

-- 内连接查询(可省略inner)
select * from equipment inner join heroes on  equipment.hero_ID = heroes.ID;

-- 给表起别名
select* from equipment as e join heroes as h on e.hero_ID = h.ID;

-- 隐式内连接(不使用inner join和on，使用where限定查询条件)
select * from equipment, heroes where equipment.hero_ID = heroes.ID;

-- 左外连接查询
select * from heroes left outer join equipment on equipment.hero_ID = heroes.ID;

-- 右外连接查询
select * from heroes right outer join equipment on equipment.hero_ID = heroes.ID;

-- 子查询(嵌套查询) 输出结果为多行时报错
select * from heroes where ID = (select ID from heroes where position like'%P');

NOTICE: 对多个表进行复杂查询，与两个表是否设置外键、主键、没有关联

4.3.2 联合查询

-- 查询结果去重
select ID from heroes where ID in (1,2,3) union select ID from heroes 
where position like '%G';

-- 查询结果不去重
select ID from heroes where ID in (1,2,3) union all select ID from heroes 
where position like '%G';

4.3.3 报表查询

-- 统计函数配合报表查询
-- 常用统计函数 count() min() max() sum() avg() --

-- 查询统计量
select count(ID), ID from heroes group by ID;
-- 查询最大/最小数据 
select min(price), max(price) from equipment;
-- 查询某项总和
select sum(price) from equipment;
-- 查询某项均值
select avg(price) from equipment;

-- 使用having语句
select count(price),price from equipment group by price having price < 1000;

-- 使用报表查询后对临时表再次查询
select count(price),price from equipment group by price having count(price)>1;