大数据入门学习(数据库)

一、数据库概述

数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以对数据库中的数据进行增加，修改，删除及查询操作。

随着互联网的高速发展，大量的数据在不断的产生，伴随而来的是如何高效安全的存储数据和操作数据，而这一问题成为了信息时代的一个非常大的问题。

使用数据库可以高效的有条理的储存数据：

可以结构化存储大量的数据
可以有效的保持数据的一致性、完整性

二、常见的数据库

关系型数据库
- MYSQL：开源数据库，中型数据库。已被Oracle收购。MYSQL6.x版本开始收费。
- Oracle：收费的大型数据库。
- DB2：IBM公司，收费。常用在银行系统中。
- SQLServer：MicroSoft收费中型数据库。
- SQLite：嵌入式小型数据库，应用在手机端。
非关系型数据库
- Redis：key-value的内存缓存，读写性能极佳
- HBase：列式数据库，目标是高效存储大量数据
- MongoDB：文档型数据库，非常接近关系型数据库
常用数据库
- MYSQL
- Oracle
- Redis
- HBase

三、MYSQL命令

四、SQL语句

1、介绍

结构化查询语言(Structured Query Language)，简称SQL，是关系型数据库管理系统都需要遵循的规范，是数据库认识的语言。

不同的数据库生产商都支持SQL语句，但都有独特的内容。

普通话：各数据库厂商都遵循的ISO标准
方言：数据库特有的关键字

2、分类

数据库定义语言(DDL) -- 不涉及数据
- 用来定义数据库对象：数据库，表，列等。
- 关键字：create、alter、drop等
数据操作语言(DML) --对数据进行增删改
- 用来对数据库中表的记录进行更新。
- 关键字：insert、delete、update等
数据控制语言(DCL) -- 权限
- 用来定义数据库的访问权限和安全级别，及创建用户
数据查询语言(DQL) -- 对数据进行查询
- 用来查询数据库中表的记录
- 关键字：select、from、where等

3、通用语法

单行或多行书写，分号结尾
可使用空格和缩进来增强语句的可读性
MYSQL数据库的SQL语句不区分大小写，但是建议关键字使用大写
同样可以使用/**/的方式完成注释 -- #

MYSQL中常使用的数据类型如下

分类	类型名称	说明
整数类型	tinyint	很小的整数最大到127
	smallint	小的整数
	mediumint	中等大小的整数
	int(integer)	普通大小的整数最大2147483647
	bigint	最大 2^64 -1
小数类型	float	单精度浮点数
	double	双精度浮点数
	decimal(m,d)	压缩严格的定点数 decimal(10,2)
日期类型	year	YYYY 时间范围：1901~2155
	time	HH:MM:SS 时间范围：-838:59:59~838:59:59
	date	YYYY-MM-DD 时间范围：1000-1-1~9999-12-3
	datetime	YYYY-MM-DD HH:MM:SS 时间范围：1000-1-1 00:00:00~9999-12-3 23:59:59
	timestamp	YYYY-MM-DD HH:MM:SS 时间范围：1970-01-01 00:00:01 UTC ~ 2038-1-19 03:14:07 UTC
文本、二进制	char
	varchar

4、DDL之数据库操作(database)

4.1、查看数据库

show databases;

4.2、创建数据库

-- create database 数据库名;
create database mydb;	# 直接创建数据库，如果存在则报错
create database if not exists mydb; # 如果数据库不存在则创建
-- crate database 数据库名 character set  字符集 
crate database mydb character set utf8; # 创建数据库时设置字符集

create database sina default character set utf8mb4 collate utf8mb4_unicode_ci;

4.3、删除数据库

drop database 数据库名

4.4、操作数据库

use 数据库; # 切换到某个数据库。接下来的所有操作就在这个数据库下完成。
select database(); # 查看当前正在使用的数据库

5、DDL之数据库操作(table)

5.1、创建表

create table if not exists 表名(
	字段名 类型[长度] [约束],
    字段名 类型[长度] [约束],
    ......
);

# 类型：varchar int double date timestamp
# 约束：primary key 主键，被主键修饰字段中的数据，不能重复，不能为null

-- 创建分类表
create table if not exists category(
	cid varchar(20) primary key,# 分类ID
    cname varchar(100) # 分类名称
);

5.2、查看数据库中所有表

show tables;

5.3、查看表结构

desc 表名

5.4、删除表

drop table 表名称

5.5、修改表结构

修改表添加列

# alter table 表名 add 列名 类型[长度] [约束];
alter table category add `desc` varchar(100)

修改列名

# alter table 表名 change 旧列名 新列名 类型[长度] [约束];
alter table category change `desc` `remarks` varchar(30);

删除列

# alter table 表名 drop 列名;
alter table category drop `remarks`;

修改表名

# rename table 表名 to 新表名
alter table category to category2

6、DML数据操作语言

6.1、插入表记录

-- 向表中插入某些字段
insert into 表名 (字段1,字段2,字段3) values (值1,值2,值3)
-- 向表中插入所有字段，字段的顺序为创建表的顺序
insert into 表名 values (值1,值2,值3,...)
-- 插入多行数据  注意每个值之间的逗号
insert into 表名 values (值1,值2,值3,...),(值1,值2,值3,...),(值1,值2,值3,...)

值与字段必须对应，个数相同，类型相同
值的数据大小必须在字段的长度范围内
除了数值类型外，其他的字段类型的值必须使用引号引起。(建议单引号)
如果要插入空值，可以不写字段，或插入null

6.2、更新表记录

-- 更新所有记录的指定字段
update 表名 set 字段名=值,字段名=值;
-- 更新符合条件的记录的指定字段
update 表名 set 字段名=值,字段名=值 where 条件;

列名的类型与修改的值要一致
修改值的时候不能超过最大长度
除了数值类型外，其他的字段类型的值必须使用引号引起

6.3、删除记录

delete from 表名 [where 条件]
-- 或者
truncate table 表名; # 清空表数据

delete是一条一条删除，不清空auto_increment记录数
truncate直接将表删除，重新建表，auto_increment将置零，从新开始
auto_increment：自动增长

7、SQL约束

7.1、主键约束

primary key 约束唯一标识数据库表中的每条记录
- 主键必须包含唯一的值
- 主键列不能包含NULL值
- 每个表中都应该有一个主键，并且每个表只能有一个主键

添加主键约束

-- 方式一
create table if not exists category(
	cid varchar(20) primary key, # primary key 指定主键
    cname varchar(100) # 分类名称
);

-- 方式2
# constraint pk_PersonID 给主键定义名字 没有也可以
create table if not exists Person(
	FirstName varchar(100),
    LastName varchar(100),
    Address varchar(100),
    City varchar(100),
    constraint pk_PersonID primary key(FirstName,LastName) # 联合组件：合在一起做主键
);
或
create table if not exists Person(
	FirstName varchar(100),
    LastName varchar(100),
    Address varchar(100),
    City varchar(100),
    primary key(FirstName,LastName) # 联合组件：合在一起做主键
);

删除主键约束

alter table 表名 drop primary key;

自动增长列

-- 创建分类表
create table if not exists category(
	cid int primary key auto_increment
);

# auto_increment：自动增长
# 只能用在数字列
# 插入数据时，可不指定或传null
insert into mytable (`name`,`age`) value ("张三",23)
insert into mytable (id,`name`,`age`) value (null,"张三",23)
# 可指定开始值
alter table 表名 auto_increment = 100 # 指定起始值从100开始

7.2、非空约束

NOT NULL 约束强制列不接受NULL值

NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新纪录或者更新记录

-- 创建分类表
create table if not exists category(
	cid int NOT NULL,
    cname varchar(20) NOT NULL
);

7.3、唯一约束

UNIQUE 约束唯一标识数据库表中的每条记录

UNIQUE 与 primary key约束均为列或列集合提供了唯一性的保证

primary key 拥有自定义的UNIQUE 约束

请注意，每个表可以有多个 UNIQUE 约束，但每个表只能有一个 primary key 约束

添加唯一约束

-- 创建表时，在字段描述处，声明唯一
create table if not exists category(
	cid int UNIQUE,
    cname varchar(20)
);

7.4、外键约束

FOREIGN KEY 表示外键约束，将在多表中学习。

8、DQL数据查询语言

CREATE TABLE product (
	pid INT (11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    category varchar(20) NOT NULL,
	pname VARCHAR (50) NOT NULL,
	pprice DECIMAL (8, 2) NOT NULL,
	pdesc VARCHAR (100) NULL,
	pcount INT (11) NOT NULL DEFAULT '0'
);

8.1、语法

-- distinct：去重
-- *：全部字段
select [distinct] *| 列名,列名 from 表名 [where 条件]

8.2、简单查询

# 查询所有商品
select * from product
# 查询商品名和价格
select pname,pprice from product

8.3、别名查询

# 使用关键字as,as可省略
-- 表别名
select * from product as p
-- 列别名
select pname pn from product

8.4、去重查询

select distinct pname from product

8.5、查询结果是表达式(运算查询)

# 将所有商品价格+10元进行显示
select pprice+10 from product

8.6、条件查询

比较运算符		比较运算符
<	小于	between...and...	显示在某一区间的值(含头含尾)
>	大于	IN(set)	显示在In列表中的值，例：in(100,200)
<=	小于等于	LIKE	模糊查询。“%”代表零个或多个任意字符；“_”代表一个字符。
>=	大于等于		例如：LIKE '张%' ； LIKE ’%涛%‘；LIKE '_a%'
=	等于	IS NULL	为空
<>	不等于	IS NOT NULL	不为空
!=	不等于

逻辑运算符
and	多个条同时成立
or	多个条件任意一个成立
not	不成立，例如：where not (pprice > 0)

# 查询价格小于800
select * from product where pprice < 800
# 查询价格大于800
select * from product where pprice > 800
# 查询价格小于等于800
select * from product where pprice <= 800
# 查询价格大于等于800
select * from product where pprice >= 800
# 查询价格等于800
select * from product where pprice = 800
# 查询价格不等于800
select * from product where pprice <> 800
select * from product where pprice != 800

# 查询商品描述为空
select * from product where pdesc IS NULL
# 查询商品描述不为空
select * from product where pdesc IS NOT NULL

# 查询价格在700到800，包含 700 和 800
select * from product where pprice between 700 and 800

# 模糊查询商品名为花花公子的商品所有信息
select * from product where pname LIKE '%花花公子%'

# 查询商品名为花花公子 并且 价格是800的信息
select * from product where pname = '花花公子' and pprice = 800

# 查询价格是700 或 价格是800的信息
select * from product where pprice = 700 or pprice = 800
select * from product where pprice in (700,800)

8.7、排序查询

select * from 表名 [where 条件] order by 排序字段 asc|desc
-- asc：升序(默认)
-- desc：降序

# 按价格降序排列
select * from produce order by pprice desc
# 按价格降序排列，如果价格相同的按照商品名正序排列。如果没有相同的价格则pname asc 无效
select * from produce order by pprice desc,pname asc

8.8、聚合查询

之前做的查询都是横向查询，它们都是根据条件一行一行的进行判断。

使用聚合函数查询是纵向查询，它是对一列的值进行计算，然后返回一个单一的值。

另外聚合函数会忽略空值。

聚合函数	作用
count()	统计指定列不为NULL的记录行数
sum()	计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0
max()	计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算
min()	计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算
avg()	计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0

# 查询商品表的总条数
select count(*) from product
# 价格大于200的总条数
select count(*) from product where pprice > 200
# 查找价格最高的一条数据
select max(pprice) from product
# 查找价格最低的一条数据
select min(pprice) from product
# 查询价格最大和最小值
select max(pprice),min(pprice) from product
# 求价格和
select sum(pprice) from product
# 求价格平均值
select avg(pprice) from product

8.9、分组查询

分组查询是指使用group by 字句对查询信息进行分组

select 字段1,字段2... from 表名 group by 分组字段 having 分组条件

having与where的区别
- having是在分组后对数据进行过滤；where是在分组前对数据进行过滤
- having后面可以使用分组函数(统计函数);where后边不可以使用分组函数

# 获得各商品类型总条数
select count(*),category from product group by category

-- 执行过程(粗略)：
#		from product：首先确认哪张表
#		group by category：将product表按照商品类型category分成多张临时表
#		临时表执行命令 select count(*) from 临时表，获取到每张临时表的总数量
#		select 挑选要显示的值

# 统计各类商品的个数，且只显示个数大于1的信息
select catagory,count(*) from product group by category having count(*) > 1

8.10、sql语句的执行顺序

from > where > group > 聚合函数 > having > select > distinct > order by > limit

分组之后：select 的后边只能跟分组字段和聚合函数(早期没有限定。8.0版本以后要求严格)

8.11、分页查询

由于数据量很大，显示屏长度有限，因此对数据需要采取分页显示方式。

select * from 表名 limit M,N

-- M：整数。表示从第几条索引开始，计算方式(当前页-1)*每页显示条数
-- N：整数。表示查询多少条数据

select * from product limit 0,5

8.12、insert into select

从一个表复制数据，然后把数据插入到一个已存在的表中

-- 新建表product2
-- 将id,pname,pprice插入到product2中
insert into product2 (p2id,p2name,p2price)
select id,pname,pprice from product

表数据

category

cid	cname
c001	电器
c002	服装
c003	化妆品
c004	食品

product

pid	pname	price	category_id
1	联想	5000	c001
2	海尔	3000	c001
3	雷神	5000	c001
4	杰克琼斯	800	c002
5	真维斯	200	c002
6	花花公子	440	c002
7	香奈儿	800	c003
8	好想你枣	56	c004

9、多表操作

9.1、表与表之间的关系

一对一关系
一对多关系
多对多关系

9.2、外键约束

以上两张表分别是商品分类表(category)和商品信息表(product)。

在商品信息表中存放商品分类cid的信息的一列称为：外键

-- 声明外键约束
alter table 从表名 add [constraint][外键名称] foreign key (从表 外键 字段名) references 主表 (主表主键)

-- [外键名称] 用于删除外键约束的，一般建议"_fk"结尾
alter table 从表名 drop foreign key 外键名称

-- 使用外键的目的：保证数据的完整性

alter table product add constraint product_fk foreign key (category_id) references category (cid)

9.3、一对多操作(存在外键)

添加数据
- 主表：不受影响
- 从表：从表会受到外键列的影响，无法添加主表ID以外的数据，作为外键列数据
删除数据：
- 主表：如果收到从表依赖，则无法删除
  - 若想删除。1：取消外键关联。2：删除从表数据
- 从表：不受影响

10、多表查询

10.1、交叉连接查询

得到两个表的乘积(基本不会使用)

select * from A,B

10.2、内连接查询

使用inner join 关键字，inner 可以省略

-- 隐式内连接
select * from A,B where 条件;
-- 显示内连接
select * from A inner join B on 条件;

# 查询多张表的交集

10.3、外连接查询

使用outer join，outer可以省略

-- 左外连接：将左表数据全部输出，右表如果有对应数据，则输出，没有对应的数据则补NULL
select * from A left outer join B on 条件
select * from A left join B on 条件
-- 右外连接：将右表数据全部输出，左表如果有对应数据，则输出，没有对应的数据则补NULL
select * from A right outer join B on 条件
select * from A right join B on 条件

10.4、子查询

其实就是select的嵌套

作为另一张表：select * from (select * from a)
作为查询条件(一个值)：select * from a where a.id = (select id from b where 条件)
作为查询条件(多个值)：select * from a where a.id in (select id from b)

五、索引

1、概述

索引是MYSQL中一种十分重要的数据库对象。

它是数据库性能调优技术的基础，常用于实现数据的快速检索。

索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

在MYSQL中，通常有两种方式访问数据库表的行数据

顺序访问
- 顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。
- 这种方式比较简单，但是当表中有大量数据的时候，效率非常低下
索引访问
索引访问是通过遍历索引来直接访问表中记录行的方式，使用这种方式的前提是对表建立一个索引
在列上创建索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷的查找到数据
索引储存了指定列数据值的指针，根据指针的排序顺序对这些指针排序

2、索引的分类

根据存储方式不同
- B-树索引
- 哈希索引
根据具体用途不同，MYSQL在逻辑上分为以下3类
- 普通索引：既不是主键也不用值唯一的普通列加即可
- 唯一索引：确定该列值唯一即可
- 主键索引：建表时加主键即可

3、索引的操作

3.1、普通索引

3.1.1、创建索引

方式一：直接创建

create index 索引名 on 表名(列名([length]));

如果是char,varchar类型，length可以小于字段实际长度；

如果是blob和text类型，必须指定length

方式二、修改表结构(添加索引)

alter table 索引名 add index 索引名(列名)

方式三、建表时直接指定

create table mytable(
	id int not null,
    username varchar(20) not null,
    index username_index(username(length))
)

3.1.2、查询索引

# 查看表中所有索引
show index from table_name;
# 查看数据库所有索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名'
# 查看某一个表的索引
select * from mysql.`innodb_index_stats` a where a.`database_name` = '数据库名' 
and a.table_name like '%表名%'

3.1.3、删除索引

drop index [索引名] on 表名

alter table 表名 drop index 索引名

3.2、唯一索引

3.2.1、创建索引

方式一：直接创建

create unique index 索引名 on 表名(列名(length))

方式二：修改表结构(添加索引)

alter table 表名 add unique [索引名](列名(length))

方式三：建表直接指定

create table mytable(
	id int not null,
    username varchar(20) not null,
    unique username_index(username(length))
)

3.2.2、删除索引

drop index [索引名] on 表名

alter table 表名 drop index 索引名

3.3、主键索引

主键索引的操作就是主键约束的操作，具体查看第三点的7.1主键约束

4、索引的使用原则和注意事项

虽然索引可以加快查询速度，提高MYSQL的处理性能，但是过多的使用索引也会造成以下弊端

创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大
当对表中数据进行增删改时，索引也要动态的维护，这样就降低了数据的维护速度
对于那些查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间需求。

六、Mysql开窗函数

1、概述

Mysql在8.0版本增加了对开窗函数的支持，终于可以在mysql中使用开窗函数了。

语法结构：

-- Partition by 分组
-- order by 排序
<开窗函数> over ([Partition by] order by <排序用列清单>)

2、开创函数介绍

Mysql的开窗函数有很多，这里只介绍以下三种。

以下函数都是用于返回结果集的分组内每行的排行，不同的是

row_number()：不管排名是否有相同，都按顺序1，2，3...n
rank()：排名相同的名次一样，同一排名有几个，后边排名就会跳过几次。1，2，2，4，5，5，7
dense_rank()：排名相同的名次一样，且后面名次不跳跃。1，2，2，3，4，4，5，5，6

-- 分组排序打编号
-- Partition by 指定分组字段
select 
empid,ename,depid,salary,
row_number() over (Partition by deptid order by salary desc) as row_number
from employee;

-- 对全表进行排序打编号
select 
empid,ename,depid,salary,
row_number() over (order by salary desc) as row_number
from employee;

-- 查询分组第一
select * from (
	select 
    empid,ename,depid,salary,
    row_number() over (Partition by deptid order by salary desc) as row_number
    from employee;
) t 
where t.row_number = 1

本文内容学习自 8天零基础入门大数据

posted @ 2020-08-06 20:57 Lucky丶zhu 阅读(557) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Lucky丶zhu

大数据入门学习(数据库)

一、数据库概述

二、常见的数据库

三、MYSQL命令

登录

四、SQL语句

1、介绍

2、分类

3、通用语法

4、DDL之数据库操作(database)

4.1、查看数据库

4.2、创建数据库

4.3、删除数据库

4.4、操作数据库

5、DDL之数据库操作(table)

5.1、创建表

5.2、查看数据库中所有表

5.3、查看表结构

5.4、删除表

5.5、修改表结构

修改表添加列

修改列名

删除列

修改表名

6、DML数据操作语言

6.1、插入表记录

6.2、更新表记录

6.3、删除记录

7、SQL约束

7.1、主键约束

添加主键约束

删除主键约束

自动增长列

7.2、非空约束

7.3、唯一约束

添加唯一约束

7.4、外键约束

8、DQL数据查询语言

8.1、语法

8.2、简单查询

8.3、别名查询

8.4、去重查询

8.5、查询结果是表达式(运算查询)

8.6、条件查询

8.7、排序查询

8.8、聚合查询

8.9、分组查询

8.10、sql语句的执行顺序

8.11、分页查询

8.12、insert into select

表数据

9、多表操作

9.1、表与表之间的关系

9.2、外键约束

9.3、一对多操作(存在外键)

10、多表查询

10.1、交叉连接查询

10.2、内连接查询

10.3、外连接查询

10.4、子查询

五、索引

1、概述

2、索引的分类

3、索引的操作

3.1、普通索引

3.1.1、创建索引

方式一：直接创建

方式二、修改表结构(添加索引)

方式三、建表时直接指定

3.1.2、查询索引

3.1.3、删除索引

3.2、唯一索引

3.2.1、创建索引

方式一：直接创建

方式二：修改表结构(添加索引)

方式三：建表直接指定

3.2.2、删除索引

3.3、主键索引

4、索引的使用原则和注意事项