[大数据] 数据库基础

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 数据库基础 -> 正文阅读

[大数据]数据库基础

1. 数据库三大范式

第一范式（1NF）：
无重复的列。当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。

第二范式（2NF）：
属性完全依赖于主键 [ 消除部分子函数依赖 ]。如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。**第二范（2NF）要求数据库表中的每个实例或行必须可以被唯一地区分。**为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键、主码。
【2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分】

第三范式（3NF）：
**属性不依赖于其它非主属性 [ 消除传递依赖 ]。**设R是一个满足第一范式条件的关系模式，X 是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF。第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。
【非主键列是直接依赖于主键，还是直接依赖于非主键列】

注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性；下一层范式都是在上一层范式基础上成立的。

2. SQL语言

2.1 概述

SQL Structuew Query Language(结构化查询语言），一种标准化的语言，允许在数据库上执行CRUD等操作
CRUD --->  Create Read Uodate Delete

2.2 SQL语句分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：新建库、表、列等。常用的语句关键字主要包括 create、drop、alter等。
DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录、数据增删改。常用的语句关键字包括insert、delete、update、select等。
DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别。主要的语句关键字包括 grant、revoke 等。
DQL（Data Query Language）：数据查询语言，用来查询记录、数据查询。

1、SQL语句以 ; 结尾
2、MySQL中关键字不区分大小写

2.3 DDL语句操作数据库

在没有Navicat视图化操作数据库之前，可以通过cmd窗口执行mysql命令

win + R  -- 运行cmd 
mysql -u root -p     --连接Mysql

2.3.1 创建数据库

create database -- 用于创建新的数据库
编码方式:gb2312,utf-8,gbk,iso-8859-1
//create database 数据库名 
CREATE DATABASE mydb1; 
//create database 数据库名 character set 编码方式 
CREATE DATABASE mydb2 character SET GBK; 
//create database 数据库名 set 编码方式 collate 排序规则 
CREATE DATABASE mydb3 character SET GBK COLLATE gbk_chinese_ci;

2.3.2 查看数据库

show databases; --查看当前数据库服务器中所有的数据库
show create database 数据库名;  --查看前面创建的数据库的定义信息

2.3.3 修改数据库

alter database 数据库名 character set 编码方式

2.3.4 删除数据库

drop database 数据库名

2.3.5 其他语句

Select database(); --查看当前使用的数据库
use 数据库名 --切换数据库

2.4 DDL语句操作表

2.4.1 创建表

create table  -- 创建新表

语法：
CREATE TABLE 表名( 
	列名1 数据类型 [约束], 
	列名2 数据类型 [约束], 
	列名n 数据类型 [约束] 
);

多列之间用逗号隔开**，最后一列逗号不能加** ； [约束] 可有可无

常用数据类型：

 int：整型
 double：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；默认支持四舍五入
 char：固定长度字符串类型； char(10) 'aaa ' 占10位
 varchar：可变长度字符串类型； varchar(10) 'aaa' 占3位
 text：字符串类型，比如小说信息；
 blob：字节类型，保存文件信息(视频，音频，图片)；
 date：日期类型，格式为：yyyy-MM-dd；
 time：时间类型，格式为：hh:mm:ss
 timestamp：时间戳类型 yyyy-MM-dd hh:mm:ss 会自动赋值, 
 	TIMESTAMP值不能早于1970或晚于2037
 	4个字节存储
 	值以UTC格式保存
 	时区转换，存储时对当前的时区进行转换，检索时再转换回当前的时区
 datetime：日期时间类型 yyyy-MM-dd hh:mm:ss 
 	支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'
 	8个字节存储
 	实际格式存储
 	与时区无关

2.4.2 其他表操作

drop table 表名; -- 删除表
show tables; -- 当前数据库中的所有表
desc 表名; -- 查看表的字段信息

alter 包含 add drop change modify 四个功能板块
alter table 表名 add 新列名 新数据类型;    -- 在表中增加一列
alter table 表名 change 旧列名 新列名 新的数据类型;  -- 修改表中某一列的列名/数据类型/数据长度
alter table 表名 modify 列名 数据类型;  -- 修改表中的某一列的数据类型/数据长度
alter table 表名 drop 列名; -- 删除表中某一列   ！！！drop 一次只能删除一列
alter table 旧表名 rename 新表名;   -- 修改表名
alter table 表名 character set 编码方式; -- 修改表的编码格式/字符集(gdk)

show create table 表名; -- 查看表格的创建细节

2.5 sql中的运算符

2.5.1 算术运算符

+，-，*，/(除法),求余(%)

2.5.1 赋值运算符

=   赋值方向：从右往左赋值

2.5.1 逻辑运算符

and(并且),or(或者),not（取非）
作用:用于连接多个条件时使用

2.5.1 关系运算符

 >,<,>=,<=,!=(不等于),=(等于),<>(不等于)

2.6 DML操作

DML是对表中的数据进行增、删、改的操作 DDL是对数据库、数据表进行增删查改的操作

2.6.1 插入数据

insert into 表名(列名) values(数据值);    -- 插入数据
insert into 表名(列名) values(第一行数据),(第二行数据),(),(); --同时添加多行数据

> 注意：列名与列值的类型、个数、顺序要一一对应。 
> 参数值不要超出列定义的长度。 如果插入空值，请使用null
> 插入的日期和字符一样，都使用引号括起来。
> 多列和多个列值之间使用逗号隔开 2.列名要和列值一一对应
> 非数值的列值两侧需要加单引号 常见错误: Data too long for column 'stusex' at row 1
> 添加数据的时候可以将列名省略->当给所有列添加数据的时候
> 此时列值的顺序按照数据表中列的顺序执行

2.6.2 修改数据

update 表名 set 列名1=列值1,列名2=列值2 ... WHERE 列名=值

2.6.3 删除数据

delete from 表名 where 列名=值;

truncate 与 delete 区别
1.delete 可以带where条件删除；而truncate只能删除整个表的数据
2.delete是DML，操作时原数据是被放在rollback segment中，支持事务回滚；而truncate是DDL，操作时不进行存储，不支持回滚
3.数据量较大时，两者的删除速度不同；delete每次删除一行并在事务日志中为所删除的每行记录一项，慢且安全；而truncate不支持回滚，所使用的系统和事务日志资源少，删除速度更快
4.随着不断地进行表记录的DML操作，会不断提高表的高水位线（HWM），DELETE操作之后虽然表的数据删除了，但是并没有降低表的高水位，随着DML操作数据库容量也只会上升，不会下降。
所以如果使用DELETE，就算将表中的数据减少了很多，在查询时还是很和DELETE操作前速度一样；而TRUNCATE操作会重置高水位线，数据库容量也会被重置，之后再进行DML操作速度也会有提升。

truncate、delete、drop 区别 
1. truncate、delete 只删除数据不删除表结构（定义）
   drop 将删除表的结构被依赖的约束（constain)，触发器（tigger)，索引（index），依赖于该表的存储过程/函数将保留为invalid状态
2. delete 是DML，这种操作会放到rollback segement中，事务提交后才生效；如果有相应的tigger，执行的时候将被触发
   truncate、drop 是DDL，操作立即生效，不能回滚，不触发tigger
3. delete 不影响表所占用的extent（内容），HWM保持原位置不动
   drop语句将表的空间全部释放
   truncate 语句缺省情况下将空间释放到minextents个extent，除非使用reuse storage，truncate会将HWM复位
4. 删除速度：drop > truncate > delete
5. 安全性：delete > others  【truncate是高危操作】
6. 删除表  -- drop tableName;
7. 删除数据 
		alter table tableName drop 列名;  --- 删除一列数据
		delete from tableName where [];   --- 一次删除一行符合条件的数据
		delter from tableName; --- 删除一整数据表，一行行进行
		truncate tableName; /truncate table tableName;    --- 删除/截断表中全部数据
8. truncate只作用于表，但truncate不能对有外键约束引用的表使用     
   delete、drop可作用于表、视图等

※ 所有的oracle段(segments，在此，为了理解方便，建议把segment作为表的一个同义词) 都有一个在段内容纳数据的上限，我们把这个上限称为"high water mark"或HWM
※ HWM是一个标记，用来说明已经有多少没有使用的数据块分配给这个segment
※ 在表上使用了truncate命令，则该表的HWM会被重新置为0；而delete只是清空数据不降低HWM

delete 操作的事务回滚
在这里插入图片描述

注意事项： 1. 为空的条件： is null or = ' ' 2. 两个单引号表空字符串

2.7 DCL

2.7.1 创建用户

--create user 用户名@指定ip identified by 密码;				//本机用户可登陆
create user test123@localhost identified by 'test123'
--create user 用户名@客户端ip identified by 密码;             //指定IP才能登陆
create user test456@10.4.10.18 identified by 'test456'
--create user 用户名@‘% ’ identified by 密码 				//任意IP均可登陆
create user test789@'%' identified by 'test789'

2.7.2 用户授权

--grant 权限1,权限2,........,权限n on 数据库名.* to 用户名@IP;    //给指定用户授予指定指定数据库指定权限
grant select,insert,update,delete,create on chaoshi.* to 'test456'@'127.0.0.1';
--grant all on 数据库名.* to 用户名@IP 							//给指定用户授予所有数据库所有权限
grant all on 数据库名.* to 'test456'@'127.0.0.1';
--grant all on 数据库名.* to 用户名@IP 						 //给指定用户授予所有数据库所有权限
grant all on 数据库名.* to 'test456'@'127.0.0.1';             -- * 代表数据库中的所有表

2.7.3 其他

//用户权限查询
show grants for 用户名@IP;
//撤销用户权限
revoke 权限1,权限2他,......权限n on 数据库名.* from 用户名@IP;
//删除用户
drop user 用户名@IP:

2.8 DQL

查询返回的结果集是一张虚拟表

SELECT 列名 FROM 表名 [WHERE --> GROUP BY-->HAVING--> ORDER BY]

SELECT 要查询的列名称 
FROM 表名称 
WHERE 限定条件 /*行条件*/ 
GROUP BY grouping_columns /*对结果分组*/ 
HAVING condition /*分组后的行条件*/ 
ORDER BY sorting_columns /*对结果排序*/ 
LIMIT offset_start, row_count /*结果限定*/

2.8.1 简单查询

//查询所有列
select * from 表名;
//查询指定列
select 列名 from 表名;

2.8.2 条件查询

=、!=、<>、<、<=、>、>=; BETWEEN…AND; IN(set); IS NULL; AND; OR;  NOT;

[not] in()  -- 范围查询
not is null;
is not null;      --非空

2.8.3 模糊查询

语法: 列名 like '表达式' //表达式必须是字符串
通配符:
_(下划线): 任意一个字符
%: 任意0~n个字符,'张%'

2.8.4 字段控制查询

// 去除重复记录
select distinct 列名 from 表名;
//两个数值型数据 因为任何东西与NULL相加结果还是NULL，所以结算结果可能会出现NULL
 --- 把NULL转换成数值0的函数 IFNULL(列名,0)
select sal + IFNULL(comm, 0) from 表名;
//给列添加别名
select 列名 [as] 别名 from 表名;

2.8.5 排序

order by 列名 asc/desc
//asc 升序 desc 降序 默认不写的话是升序
// 多列排序：当前面的列的值相同的时候，才会按照后面的列值进行排序

2.8.6 聚合函数

聚合函数是用来做纵向运算的函数：
 COUNT(列名)：统计指定列不为NULL的记录行数；
 MAX(列名)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
 MIN(列名)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
 SUM(列名)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
 AVG(列名)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；

2.8.7 分组查询

注意:如果查询语句中有分组操作，则select后面能添加的只能是聚合函数和被分组的列名

having 与 where 的区别
1.having是在分组后对数据进行过滤 where是在分组前对数据进行过滤
2.having后面可以使用分组函数（统计函数）
  where后面不可以使用分组函数

//多列分组
例子: 查询每个阶段的男生和女生个数（group by 两列） 
select gradeid, sex, count(stuid) from student group by gradeid, sex;

2.8.8 LIMIT

LIMIT用来限定查询结果的起始行，以及总行数。
 limit 开始下标,显示条数；//开始下标从0开始
 limit 显示条数；//表示默认从0开始获取数据

注意: 起始行从0开始，即第一行开始！

2.8.9 分页查询

如果一页记录为10条，希望查看第3页记录应该怎么查呢？
  第一页记录起始行为0，一共查询10行； limit 0,10
  第二页记录起始行为10，一共查询10行；limit 10,10
  第三页记录起始行为20，一共查询10行； limit 20,10
limit (pageindex-1)*pagesize,pagesize;
参数:
    pageIndex 页码值 
    pageSize 每页显示条数