数据库
基本概念
1.数据
定义:描述事物的符号序列, 数据(Data)是数据库中存储的基本对象。
数据的种类:数字、文字、图形、图像声音及其他特殊符号。
数据举例:学生记录(李明,男,1994,湖南,计算机学院)
值:给出了符合给定型的
2.数据库
-
数据库——Database,简称DB。
保存有组织的数据的容器(一个或者一组文件)。
表的集合,具有统一的结构形式并存放于统一的存储介质内,是多种应用数据的集成,并可被各个应用程序所共享。按数据所提供的数据模式存放的。
-
模式
(schema),关于数据库和表的布局及特征的信息 -
数据库的发展:层次数据库 网络式数据 关系型数据库 非关系型数据库(Redis)
-
关系型数据库
以由多张能互相联接的二维行列表格组成的数据库。在数据准备时,我们通常要建立表关联来分析。
- 常用的关系型数据库有 SQL Server、MySQL、Oracle、 DB2 等,这个视企业使用为准
- Oracle: 是目前市场占有率最大的数据库, Oracle, 安装起来很繁琐, 而且居然程序文件有 3G 之多… 用起来非常方便, 对于我这样的初学者, 有很简单的配置, 对于要求 很高的企业级应用, 也有很复杂的配置和管理方法, 有很强大的数据字典, 可以说是最实用的数据库 了, 但是查了一下, 价格不菲…
- MySQL: MySQL 是一个很好的关系型数据库, 免费, 而且功能很全, 程序又小, 安装简单, 现在很多 网站都用 MYSQL, 在字段约束上做的差了点儿, 其他的都不错
- 常用的关系型数据库有 SQL Server、MySQL、Oracle、 DB2 等,这个视企业使用为准
-
数据库系统DBS
-
定义
替你访问数据库。实现有组织地、动态地存储大量相关数据,提供数据处理和资源共享服务。
-
构成:
- 数据库(数据)
- 数据库管理系统(软件)
- 数据库管理员(人员)
- 硬件平台:计算机和网络
- 软件平台:操作系统、数据库系统开发工具、接口软件
-
-
应用系统构成:
Database Application System,简称DBAS
组成:DBS+应用软件+应用界面
-
关系数据库管理系统
RDBMS(Relational Database Management System)关系数据库管理系统,是一种基于E.F.Codd提出的关系模型的数据库管理系统。RDBMS是SQL的基础,也是所有现代数据库系统(如MS SQL Server、IBMDB2、Oracle、MySQL和MicrosoftAccess)的基础。
-
SQL
专门用来和数据库通信的语言 。结构化查询语言(Structured Query Language)简称
一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。至于 ORACLE、DB2、Sybase、SQLServer、MySQL、MS Access 等都是数据库,虽然语法上有 差异,但是基本上都是大同小异。
-
表
RDBMS中的数据存储在表中,表是最常见和最简单的数据存储形式。这个表基本上是一个相关数据条目的集合,它由许多列和行组成。
-
字段
每个表都被分解成更小的实体,称为字段。所谓的列
-
记录
记录也称为数据行,即表中存在的每个单独的条目
-
-
表间关系
注意:虽然建立的表与表之间的关系,但是这个关系并不会被数据库维护.需要通过外键约束来通知数据库帮助我们维护表间关系
- 1 对1
- 1对多
- 多对多
-
可移植性
portable,能运行在多个系统上的代码成为可移植的 -
mysq和 redis区别
redis:内存型非关系数据库,数据保存在内存中,速度快
mysql:关系型数据库,数据保存在磁盘中,检索的话,会有一定的lo操作,访问速度相
对慢
语言分类
1. DML
数据操作语言(DML:Data Manipulation Language):其语句包括动词INSERT,UPDATE和DELETE。它们分别用于添加,修改和删除表中的行。也称为动作查询语言。
-
插入 INSERT
INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
有自增时不可以用
-
修改 UPDATE
UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
-
查看 SELECT
SELECT 列名称 FROM 表名称
SELECT *FROM ‘wu’
Select now() 时间
Select Version() 版本
Select user() 用户
-
删除 DELETE
DELETE FROM 表名称 WHERE 列名称
2. DDL
数据库定义语言(DDL):(data definition language)其语句包括动词CREATE和DROP。在数据库中创建新表或删除表(CREAT TABLE 或 DROP TABLE);为表加入索引等。DDL包括许多与人数据库目录中获得数据有关的保留字。它也是动作查询的一部分。
-
创建 CREAT
Create database database1
CREATE TABLE 表名称
(列名称1 数据类型,
列名称2 数据类型,
… )顺序:列名、数据类型、长度、默认、主键、非空、unsigned、自增、zerofil
-
删除 DROP
DROP TABLE 表名称
DROP DATABASE 数据库名称
-
修改字段 ALTER
ALTER TABLE table_name COMMAND_NAME field_name
-
表重命名 RENAME
ALTER TABLE 【表名字】 RENAME 【表新名字】
-
添加字段 ADD
alter table MyClass add passtest int(4) default '0’;
-
删除字段 DROP
ALTER TABLE
yan
DROPsex2
; -
修改原字段名称及类型 CHANGE
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
-
添加注释 modify column
ALTER TABLE
yan
MODIFY COLUMNsex2
INT COMMENT ‘boy=1,girl=0’; -
修改约束 add constraint
-
添加主键约 primary key
alter table 表名 add constraint 主键 (形如:PK_表名) primary key 表名(主键字段);
-
添加外键约束 foreign key
alter table 从表 add constraint 外键(形如:FK_从表_主表) foreign key 从表(外键字段) references 主表(主键字段);
-
-
删除
-
主键约束
alter table 表名 drop primary key;
-
删除外键约束
alter table 表名 drop foreign key 外键(区分大小写);
-
-
视图 VIEW
-
3. DCL
数据控制语言(DCL):用来授予或回收访问数据库的某种特权,并控制 数据库操纵事务发生的时间及。它的语句通过GRANT或REVOKE获得许可,确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
-
GRANT
-
REVOKE
-
COMMIT
编程规范
语法规则
-
SQL语句总是以关键字开始,如SELECT、INSERT、UPDATE、DELETE、DROP、CREATE。
-
SQL语句以分号结尾。
分号是分离数据库系统中每个SQL语句的标准方法,这样您就可以在对服务器的同一请求中执行多个SQL语句。
-
SQL在文本值周围使用单引号(大多数数据库系统也接受双引号)
-
由于数据库类型以及数据储存方式的不同,对应的SQL语法会有区别,但结构基本相同。
-
大小写
不区分;建议 关键字使用大小写,表名列名使用小写,如下
SELECT col_1, col_2, col_3, COUNT(*) FROM tbl_A WHERE col_1 = 'a' AND col_2 = ( SELECT MAX(col_2) FROM tbl_B WHERE col_3 = 100 ) GROUP BY col_1, col_2, col_3
-
命名
-
数据库名称、表名称、字段名,不要用保留字,不能以数字开头必须使用小写字母,并采用下划线分割
如果大小写混合使用,可能存在abc,Abc,ABC等多个表共存,容易导致混乱。建立两个名字一样但大小写不一样的字段。
-
引用表、列名用英文单引号
-
多个列用逗号隔开:
-
标准 SQL 中规定表名的第一个字符应该是字母
-
库名以 d 开头,表名以 t 开头,字段名以 f_ 开头
比如表 t_crm_relation,中间的 crm 代表业务模块名
-
视图以view_开头,事件以event_开头,触发器以trig_开头,存储过程以proc_开头,函数以func_开头_
-
普通索引以idx_col1_col2命名,唯一索引以uk_col1_col2命名(可去掉f_公共部分)。如 idx_companyid_corpid_contacttime(f_company_id,f_corp_id,f_contact_time)
-
-
表名要有意义,且名、表名、字段名禁止超过32个字符
库名、表名、字段名支持最多64个字符,但为了统一规范、易于辨识以及减少传输量,禁止超过32个字符
-
临时库、表名须以tmp加日期为后缀
如 t_crm_relation_tmp0425。备份表也类似,形如 _bak20160425 。
-
-
注释,有单行注释和多行注释,如下
-- 单行注释 -- 从SomeTable中查询col_1 # 从SomeTable中查询col_1 SELECT col_1 FROM SomeTable; /* 多行注释 从 SomeTable 中查询 col_1 */ SELECT col_1 FROM SomeTable;
多行注释很多人不知道,这种写法不仅可以用来添加真正的注释,也可以用来注释代码,非常方便
-
增强可读性:灵活使用空格和缩进来增强可读性——两大法宝空白隔道与垂直间距
- 利用空格保持关键字对齐
- 在等号前后(=)在逗号后(,)单引号前后(’)加上空格
- 子查询缩进并对齐
select name,id,sex from (select * from school_score where class_cd=110) where sex = 'man' and exam_dt = '2016-06-01'; --空白隔道+垂直间距+大小写+缩进 SELECT name, id, sex FROM (SELECT * FROM school_score WHERE class_cd = 110) WHERE sex = 'man' AND exam_dt = '2016-06-01';
-
空格
代码中应该适当留有一些空格,如果一点不留,代码都凑到一起, 逻辑单元不明确,阅读的人也会产生额外的压力,以下分别是是好的与坏的示例
-- 好的示例 SELECT col_1 FROM tbl_A A, tbl_B B WHERE ( A.col_1 >= 100 OR A.col_2 IN ( 'a', 'b' ) ) AND A.col_3 = B.col_3; -- 坏的示例 SELECT col_1 FROM tbl_A A,tbl_B B WHERE (A.col_1>=100 OR A.col_2 IN ('a','b')) AND A.col_3=B.col_3;
hive计算效率
-
Hive SQL 执行过程
-
性能优化
- 使用分区剪裁、列剪裁,分区一定要加
- 少用 COUNT DISTINCT,可用group by 代替 distinct
- 是否存在多对多的关联(避免笛卡尔积)
- 连接表时使用相同的关键词,这样只会产生一个 job
- 减少每个阶段的数据量,只选出需要的,在 join 表前就进 行过滤
- 大表放后面(大表关联小表)
- 谓词下推:where 谓词逻辑都尽可能提前执行,减少下游 处理的数据量
- sort by 代替 order by(局部排序代替全局排序)
什么是数据倾斜
- 大量相同的key被分配到了同一个任务上,造成“一个人累死、 其他人闲死”的状况
常用表操作归纳
sql执行顺序
from - join - on - where - group by - avg/sum- having - select - distinct - order by - limit
常见错误
-
笛卡尔积问题
关联条件字段为非唯一标识字段时,产生笛卡尔积现象
- 子表的笛卡尔积问题
- 一表a通过相同添加风别与多表连接 :a*B*C
-
多个disitinct并行使用
多个disitinct并行使用,相当于group by 两个字段,会造成第一个之后的字段重复
错误写法,正确应该是分开求数量和uv --屏蔽网址数量 总uv 总pv SELECT DAY ,a.is_police ,COUNT( DISTINCT a.url) as `屏蔽网址数量` ,COUNT( DISTINCT imei ) as `总uv` ,sum (pv) as `总pv`
数据类型
通用类
数据类型 | 描述 |
---|---|
CHARACTER(n) | 字符/字符串。固定长度 n。 |
VARCHAR(n) 或 CHARACTER VARYING(n) | 字符/字符串。可变长度。最大长度 n。 |
BINARY(n) | 二进制串。固定长度 n。 |
BOOLEAN | 存储 TRUE 或 FALSE 值 |
VARBINARY(n) 或 BINARY VARYING(n) | 二进制串。可变长度。最大长度 n。 |
INTEGER§ | 整数值(没有小数点)。精度 p。 |
SMALLINT | 整数值(没有小数点)。精度 5。 |
INTEGER | 整数值(没有小数点)。精度 10。 |
BIGINT | 整数值(没有小数点)。精度 19。 |
DECIMAL(p,s) | 精确数值,精度 p,小数点后位数 s。例如:decimal(5,2) 是一个小数点前有 3 位数小数点后有 2 位数的数字。 |
NUMERIC(p,s) | 精确数值,精度 p,小数点后位数 s。(与 DECIMAL 相同) |
FLOAT§ | 近似数值,尾数精度 p。一个采用以 10 为基数的指数计数法的浮点数。该类型的 size 参数由一个指定最小精度的单一数字组成。 |
REAL | 近似数值,尾数精度 7。 |
FLOAT | 近似数值,尾数精度 16。 |
DOUBLE | 近似数值,尾数精度 16。 |
DATE | 存储年、月、日的值。 |
TIME | 存储小时、分、秒的值。 |
TIMESTAMP | 存储年、月、日、小时、分、秒的值。 自动存储记录修改时间 |
INTERVAL | 由一些整数字段组成,代表一段时间,取决于区间的类型。 |
ARRAY | 元素的固定长度的有序集合 |
MULTISET | 元素的可变长度的无序集合 |
XML | 存储 XML 数据 |
类型转化
-
int
-
cast : cast(params[‘duration’] as bigint) < 86400000
运算
运算符
运算符是保留字或主要用于SQL语句的WHERE子句中的字符,用于执行操作
-
算术运算符
运算符 描述 例子 a 的值是:10,变量 b 的值是:20 + 加法,执行加法运算。 a + b 得到 30 - 减法,执行减法运算。 a - b 得到 -10 * 乘法,执行乘法运算。 a * b 得到 200 / 用左操作数除右操作数。 b / a 得到 2 % 用左操作数除右操作数并返回余数。 b % a 得到 0 -
&
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l3XZ8uWM-1644060427824)(D:\Users\72141686\Desktop\资料\自我笔记_wjP.assets\1121855c-48d9-49b7-9bad-708e0a47101c.png)]
-
比较运算符
运算符 描述 = 检查两个操作数的值是否相等,如果是,则条件为真(true)。 != 检查两个操作数的值是否相等,如果值不相等则条件为真(true)。 <> 检查两个操作数的值是否相等,如果值不相等则条件为真(true)。 > 检查左操作数的值是否大于右操作数的值,如果是,则条件为真(true)。 < 检查左操作数的值是否小于右操作数的值,如果是,则条件为真(true)。 >= 检查左操作数的值是否大于或等于右操作数的值,如果是,则条件为真(true)。 <= 检查左操作数的值是否小于或等于右操作数的值,如果是,则条件为真(true)。 !< 检查左操作数的值是否不小于右操作数的值,如果是,则条件变为真(true)。 !> 检查左操作数的值是否不大于右操作数的值,如果是,则条件变为真(true)。
逻辑运算符
运算符 | 描述 |
---|---|
ALL | ALL运算符用于将值与另一个值集中的所有值进行比较。 |
AND | AND运算符允许在SQL语句的WHERE子句中指定多个条件。 |
ANY | ANY运算符用于根据条件将值与列表中的任何适用值进行比较。 |
BETWEEN | BETWEEN运算符用于搜索在给定最小值和最大值内的值。8u7 |
EXISTS | EXISTS运算符用于搜索指定表中是否存在满足特定条件的行。 |
IN | IN运算符用于将值与已指定的文字值列表进行比较。 |
LIKE | LIKE运算符用于使用通配符运算符将值与类似值进行比较。 |
NOT | NOT运算符反转使用它的逻辑运算符的含义。 例如:NOT EXISTS, NOT BETWEEN, NOT IN等等,这是一个否定运算符。 |
OR | OR运算符用于组合SQL语句的WHERE子句中的多个条件。 |
IS NULL | IS NULL运算符用于将值与NULL值进行比较。 |
UNIQUE | 集合并运算 UNIQUE运算符搜索指定表的每一行的唯一性(无重复项)。 |
EXPECT | 集合差运算 |
INTERSECT | 集合交运算 |
IN
-
说明
IN运算符允许在WHERE子句中指定多个值。
IN运算符是多个OR条件的简写。
-
语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …)/ IN (SELECT STATEMENT);
- 不在范围内则 写not in
-
参数是子查询时,使用 EXISTS 代替 IN
如果 IN 的参数是(1,2,3)是值列表时,没啥问题,但如果IN的参数是子查询时回的结果是一样,但是用 EXISTS 的 SQL 会更快:
-- 慢 SELECT * FROM Class_A WHERE id IN (SELECT id FROM CLASS_B); -- 快 SELECT * FROM Class_A A WHERE EXISTS (SELECT * FROM Class_B B WHERE A.id = B.id);
为啥使用 EXISTS 的 SQL 运行更快呢,有两个原因
- 可以用到索引,如果连接列 (id) 上建立了索引,那么查询 Class_B 时不用查实际的表,只需查索引就可以了。
- 如果使用 EXISTS,那么只要查到一行数据满足条件就会终止查询, 不用像使用 IN 时一样扫描全表。在这一点上 NOT EXISTS 也一样
另外如果 IN 后面如果跟着的是子查询,由于 SQL 会先执行 IN 后面的子查询,会将子查询的结果保存在一张临时的工作表里(内联视图),然后扫描整个视图,显然扫描整个视图这个工作很多时候是非常耗时的,而用 EXISTS 不会生成临时表。
当然了,如果 IN 的参数是子查询时,也可以用连接来代替,如下:
-- 使用连接代替 IN SELECT A.id, A.name FROM Class_A A INNER JOIN Class_B B ON A.id = B.id;
用到了 「id」列上的索引,而且由于没有子查询,也不会生成临时表
BETWEEN
-
用于
选取介于两个值之间的数据范围内的值。
选择给定范围内的值。值可以是数字,文本或日期。
是包含性的:包括开始和结束值,且开始值需小于结束值。
-
语法
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;要否定BETWEEN运算符的结果,可以添加NOT运算符: NOT BETWEEN