1．数据
定义：描述事物的符号序列， 数据（Data）是数据库中存储的基本对象。
数据的种类：数字、文字、图形、图像声音及其他特殊符号。
数据举例：学生记录（李明，男，1994，湖南，计算机学院）
值：给出了符合给定型的
2．数据库
数据库——Database，简称DB。
保存有组织的数据的容器（一个或者一组文件）。
表的集合，具有统一的结构形式并存放于统一的存储介质内，是多种应用数据的集成，并可被各个应用程序所共享。按数据所提供的数据模式存放的。
模式
（schema），关于数据库和表的布局及特征的信息
数据库的发展：层次数据库 网络式数据 关系型数据库 非关系型数据库(Redis)
关系型数据库
以由多张能互相联接的二维行列表格组成的数据库。在数据准备时，我们通常要建立表关联来分析。
数据库系统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中的数据存储在表中，表是最常见和最简单的数据存储形式。这个表基本上是一个相关数据条目的集合，它由许多列和行组成。
字段
每个表都被分解成更小的实体，称为字段。所谓的列
记录
记录也称为数据行，即表中存在的每个单独的条目
表间关系
注意：虽然建立的表与表之间的关系,但是这个关系并不会被数据库维护.需要通过外键约束来通知数据库帮助我们维护表间关系
可移植性
portable，能运行在多个系统上的代码成为可移植的
mysq和 redis区别
redis:内存型非关系数据库,数据保存在内存中,速度快
mysql:关系型数据库,数据保存在磁盘中,检索的话,会有一定的lo操作,访问速度相
对慢
数据操作语言（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 列名称
数据库定义语言（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 yanDROP sex2 ;
修改原字段名称及类型 CHANGE
ALTER TABLE table_name CHANGE old_field_name new_field_name field_type;
添加注释 modify column
ALTER TABLE yan MODIFY COLUMN sex2INT 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
数据控制语言（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 SQL 执行过程
性能优化
什么是数据倾斜
from - join - on - where - group by - avg/sum- having - select - distinct - order by - limit
笛卡尔积问题
关联条件字段为非唯一标识字段时，产生笛卡尔积现象
多个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运算符允许在WHERE子句中指定多个值。
IN运算符是多个OR条件的简写。
语法
SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …)/ IN (SELECT STATEMENT);
参数是子查询时，使用 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 运行更快呢，有两个原因
另外如果 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」列上的索引，而且由于没有子查询，也不会生成临时表
用于
选取介于两个值之间的数据范围内的值。
选择给定范围内的值。值可以是数字，文本或日期。
是包含性的：包括开始和结束值，且开始值需小于结束值。
语法
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;要否定BETWEEN运算符的结果，可以添加NOT运算符： NOT BETWEEN
实例
带有日期值的 BETWEEN
SELECT * FROM Orders WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
去重
案例:
说明：
语法
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s);
对于distinct与group by的使用:
1、当对系统的性能高并数据量大时使用group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by
groupby 在连列表中 选择相同字段 不同表时结果可能会不同 题目 33
分 组 合 并 ， 同 时 排 序
SELECT
a.s_id
,GROUP_CONCAT(a.c_id ORDER BY a.c_id SEPARATOR '%' )
FROM Score a
GROUP BY a.s_id
需要进行排序的原因是防止出现这种情况：01 修的课程顺序是：01，02，03；如果有同学修课的顺序是 02，03， 01，虽然顺序不同，但是本质上他们修的课程是相同的 使用排序后都会变成：01，02，03，保证结果相同
group_concat([DISTINCT] 字 段 [Order BY ASC/DESC 排 序 字 段 ] [Separator ’ 分 隔 符 '])
一般 HAVING 是与 GROUP BY 结合使用的，having 是对 group by后的数据进行筛选过滤，必须要有group by才能用having。
但其实它是可以独立使用的,假设有如下表，第一列 seq 叫连续编号，但其实有些编号是缺失的，怎么知道编号是否缺失呢，
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kNurpgiD-1614984588388)(/Users/wu/Library/Application Support/typora-user-images/image-20210119145338396.png)]
SELECT '存在缺失的编号' AS gap
FROM SeqTbl
HAVING COUNT(*) <> MAX(seq);
TRUNCATE 除法
SELECT TRUNCATE(t/100,2) as g from test
Abs() 绝对值
Cos() Mod() Exp() Pi() Rand() Sin() Squrt() Tan()
COUNT()
函数返回符合指定条件的行数
语法:
函数返回指定列的值的数目
SELECT COUNT(column_name) FROM table_name WHERE condition;
表中的记录数
SELECT COUNT(*) FROM table_name;
COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
SELECT COUNT(DISTINCT column_name) FROM table_name;
count(*），count(1)，count(列名)
分类统计
COUNT( CASE WHEN a.s_score > 85 THEN 1 END
stddeev 方差
varance 标准差
percentile 百分位数
sum 总和统计函数
avg 平均值统计函数
min 最小值统计函数
max 最大值统计函数
近似
13/10位时间戳比较
from_unixtime UNIX时间戳转日期函数,10位bigint
unix_timestamp 获取当前UNIX时间戳函数
unix_timestamp 日期转UNIX时间戳函数
unix_timestamp 指定格式日期转UNIX时间戳函数
某天是本年第几周 _week
SELECT WEEK(date_add(‘2012-12-31’,interval 6 day),2
当前的日期 _ CURDATE()
当前的时间 _ CURDATE()
提取日期或日期/时间表达式的日期部分 _DATE() YYYY-MM-DD
日期相减得“天数” _ datediff(date1,date2)
日期比较函数: datediff语法: datediff(string enddate,string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
举例：hive> select datediff(‘2016-12-30’,‘2016-12-29’); 1
日期添加指定的时间间隔 _ DATE_ADD()
日期增加函数: date_add语法: date_add(string startdate, intdays)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
举例：hive>select date_add(‘2016-12-29’,10);2017-01-08
从日期减去指定的时间间隔 _ DATE_SUB()
日期减少函数: date_sub语法: date_sub (string startdate,int days)
返回值: string 说明: 返回开始日期startdate减少days天后的日期。
举例：hive>select date_sub(‘2016-12-29’,10); 2016-12-19
用不同的格式显示日期/时间 _ DATE_FORMAT
提取月 _ month ()
提取年 _ YEAR()
day 日期转天函数
day 日期转天函数
hour 日期转小时函数
minute 日期转分钟函数
second 日期转秒函数
weekofyear 日期转周函数
返回当前的日期和时间 _ now()
| datepart | 缩写 |
|---|---|
| 年 | yy, yyyy |
| 季度 | qq, q |
| 月 | mm, m |
| 年中的日 | dy, y |
| 日 | dd, d |
| 周 | wk, ww |
| 星期 | dw, w |
| 小时 | hh |
| 分钟 | mi, n |
| 秒 | ss, s |
| 毫秒 | ms |
| 微妙 | mcs |
| 纳秒 | ns |
说明:
用于把来自两个或多个表的行结合起来。基于这些表之间的共同字段。
简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地“连接”在主表结果集上。
联结的过程
联结两个表，过程是第一个表的每一行和第二个表的每一行逐条匹配
内连接inner join或join 等值联
说明：内连接可以理解为，两个表中同时满足某条件的数据记录组合。也就是表A和表B中满足条件a.id = b.a_id的所有记录。
当表A中的一条记录对应表B中的多条记录时，会以重复的方式对应多条表B记录出现在结果集中。
当表B中的一条记录对应表A中的多条记录时，会以重复的方式对应多条表A记录出现在结果集中。 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WHx854go-1614984588390)(/Users/wu/Library/Application Support/typora-user-images/image-20210118101547041.png)]
语法
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name=table2.column_name;
案例：
左外连接，会以左边的表A为主表，返回所有行，即使右表B中没有匹配的行。
如果左边的表A在右表B中找不到一条记录，则返回表A所有记录并且表B相应的字段设为null。
如果左边的表A在右表B中找到多条记录，则以相同表A记录和不同表B记录多条显示在结果集中。
语法：
SELECT column_name(s) FROM table RIGHT JOIN table2 ON table1.column_name = table2.column_name;
笛卡尔积查询结果数据量=左边表的数量*右边表的数量
select * from a cross join b;
select * from a, b;
说明：
语法
SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2;
+where : SELECT column_name(s) FROM table1 [WHERE condition] UNION SELECT column_name(s) FROM table2 [WHERE condition];
union all：SELECT column_name(s) FROM table1 UNION ALL SELECT column_name(s) FROM table2;
多数数据库管理系统用+或者||，mysql用concat()函数
select concat(vend_name,’(’,vend_country,’)’) from vendors order by vend_name;
left(str,index) 从左边第index开始截取
right(str,index)从右边第index开始截取
substring(str,index)当index>0从左边开始截取直到结束 当index<0从右边开始截取直到结束 当index=0返回空
substring(str,index,len) 截取str,从index开始，截取len长度
分列x，根据y,并返回第z个字段
SUBSTRING_INDEX(data_1,’ ',1) ;可以根据空格分列data列，提取第一字段substring_index(str,delim,count)，str是要截取的字符串，delim是截取的字段 count是从哪里开始截取(为0则是左边第0个开始，1位左边开始第一个选取左边的，-1从右边第一个开始选取右边的
6、subdate(date,day)截取时间，时间减去后面的day
7、subtime(expr1,expr2) 时分秒expr1-expr2
根据近似的读音去匹配
select cust_name,cust_contact from customers where cust_contact ='Y.Lie'; 返回 empty
select cust_name,cust_contact from customers where soundex(cust_contact) =soundex('Y.Lie'); 返回Y Lee
语法: split(string str, string pat)
返回值: array
说明: 按照pat字符串分割str，会返回分割后的字符串数组
正则表达式使用单个字符串来描述、匹配一系列匹配某个句法规则的字符串。数据分析中，可用于数据筛选、信息提取、替换处理等。
作用：A like B使用描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合表达式B 的正则语法，则为TRUE；否则为FALSE。LIKE主要进行简单正则匹配。
通配符
％ ：百分号表示零个，一个或多个字符
_ ：下划线表示单个字符
注意：
语法:
SELECT column1, column2, … FROM table_nameWHERE columnN LIKE pattern;
使用’％‘和’_'通配符显示不同LIKE运算符的例子：
| LIKE 运算符 | **描述 ** |
|---|---|
| WHERE CustomerName LIKE ‘a%’ | 查找以“a”开头的任何值 |
| WHERE CustomerName LIKE ‘%a’ | 查找以“a”结尾的任何值 |
| WHERE CustomerName LIKE ‘%or%’ | 在任何位置查找任何具有“or”的值 |
| WHERE CustomerName LIKE ‘_r%’ | 在第二个位置查找任何具有“r”的值 |
| WHERE CustomerName LIKE ‘a_%_%’ | 查找以“a”开头且长度至少为3个字符的值 |
| WHERE ContactName LIKE ‘a%o’ | 找到以"a"开头，以"o"结尾的值 |
| WHERE City LIKE ‘[a-c]%’; | 以“a”、“b”或“c”开头的所有客户 |
| WHERE City LIKE ‘[bsp]%’; | 以"b"、"s"或"p"开头 |
| WHERE City LIKE ‘[!bsp]%’; WHERE City NOT LIKE ‘[bsp]%’; | 不以“b”，“s”或“p”开头： |
A RLIKE B ，B中的表达式可以使用JAVA中全部正则表达式，具体正则规则参考java，或者其他标准正则语法
语法: A REGEXP B 描述: 如果字符串A或者字符串B为NULL，则返回NULL；如果字符串A符合正则表达式B的 正则语法，则为TRUE；否则为FALSE。
说明：
coalesce (expression_1, expression_2, …,expression_n)
nvl(app_version,’’)
如果exp1计算结果为null值，则NVL()返回exp2；
如果exp1计算结果不为null值，则NVL()返回exp1；
<窗口函数> over (partition by <用于分组的列名> order by <用于排序的列名>)
select *
from (
select *,row_number() over (partition by 要分组的列名 order by 要排序的列名 desc)
as 列别名 from 原表名
） as 表别名
where ranking <= N；
md5
hash
---注意 vivo_dsp_
--2位尾号 substr(hash(MD5(concat(imei,'com.vivo.browser',substr(imei,-2),'vivo_dsp_哈希 盐'))),-2)
--1位尾号 substr(hash(MD5(concat(imei,'com.vivo.browser',substr(imei,-2),'vivo_dsp_哈希 盐'))),-1)
explode
*LATERAL VIEW的使用：*
侧视图的意义是配合explode（或者其他的UDTF），一个语句生成把单行数据拆解成多行后的数据结果集。其中LATERAL VIEW explode(split(goods_id,’,’))goods相当于一个虚拟表，与原表explode_lateral_view笛卡尔积关联。
from
(
select day, imei, event_id, params['hot_word_json'] as hot_word_json
from bi_browser_dev.ostsdk
where day = '${etl_date}'
and event_id = '00239|006'
) a
lateral view explode(split(regexp_replace(regexp_replace(a.hot_word_json, '\\[|]', ''),'},\\{','}#\\{'), '#')) b as hot_word_json_2
where get_json_object(b.hot_word_json_2, '$.topword') is not null
CREATE TABLE
用于创建数据库中的表，每个表都必须有一个表名
建表语法
CREATE TABLE `da_content_tmp`.`tmp_wjp_event_pstsdk_test_0918`
(
`day` string comment 'day',
`imei` string comment 'imei',
`params` map<string,string> comment '埋点参数',
`event_id` string comment '埋点编号'
)
create table as select
语法
单行：INSERT INTO 表名称 VALUES (值1, 值2,…)；也可以指定所要插入数据的列：INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
多行：不指定列时要确保值的顺序与表中的列顺序相同INSERT INTO table_name VALUES (1行, 2行,3行, …);
insert ignore into
当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉
insert … select … where not exist
根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件
Insert into select 使用另一个表填充一个表
drop table
drop table if EXISTS da_content_tmp.tmp_wjp_test_novel_news_0832;
create table da_content_tmp.tmp_flt_brp_new_model as
select
day,
imei,
model
from
bi_browser_dev.dw_vivo_online_user
where
day = '${etl_date}'
and last = '${etl_date}'
and first_day='${etl_date}'
group by
day,
imei,
model---新在网用户
与聚类函数类似，对记录分组后进行聚合计算，但是不会为每一个组只返回一个值，而是可以返回多个值，为分组中的每条记录都返回特定值。只能用在select 语句中，使用后不能再使用 group by 。
基本结构 ：
<窗口函数> over
（partition by <用于分组的列名>
order by <用于排序的列名>）
<窗口函数>一般有以下两种：
常用函数
set hive.exec.parallel=true;
– 参数控制在同一个sql中的不同的job是否可以同时运行,默认为false.子查询中的sql并无关系,可以并行的跑
set hive.exec.parallel.thread.number = 16;
– 数hive.exec.parallel.thread.number就是控制对于同一个sql来说同时可以运行的job的最大值,该参数默认为8.此时最大可以同时运行8个job.
set hive.auto.convert.join = true;
– Hive的MapJoin机制 适用 在二个要连接的表中，有一个很大，有一个很小
可以用在 select 、group by （不可以重命名）、聚合函数（聚合函数+distinc + case when 基本可以完成所有的 sql 分组）、
统计:
使用 CASE WHEN 如下
SELECT CASE city
WHEN '长沙' THEN '湖南'
WHEN '衡阳' THEN '湖南'
WHEN '海口' THEN '海南'
WHEN '三亚' THEN '海南'
ELSE '其他' END AS district,
SUM(population)
FROM PopTbl
GROUP BY district;
与 in 连用
case when city in ( '长沙','衡阳') then ’湖南‘,
when city in ('南京') then ’江苏‘
SET 数据更新
奇葩的规定
UPDATE Salaries
SET salary = CASE WHEN salary >= 10000 THEN salary * 0.9
WHEN salary < 10000 THEN salary * 1.2
ELSE salary END;
简介：
（Alias）可以为表名称或列名称指定别名。
别名用于为表或表中的列提供临时名称，数据库中的实际表名不会更改
别名通常用于使列名更具可读性。
一个别名只存在于查询期间。
由于执行顺序的原因 select 中的As 不可以在 where \ group by 中出现
需求：SELECT a AS b WHRER b=1; //这样使用会报错，说b不存在。
因为mysql底层跑SQL语句时：where 后的筛选条件在先， as B的别名在后。所以机器看到where 后的别名是不认的，所以会报说B不存在。
这个b只是字段a查询结果的一个别名
如果非要用B做筛选条件的话：解决方案：外边再嵌套一层。
select * from(
select A as B from table
) t
where t.B = XXX – 任意的筛选条件
如果不嵌套，只能用A做筛选条件了
语法
说明
SQL约束用于指定表中数据的规则。作用于数据表中列上的规则，用于限制表中数据的类型。约束的存在保证了数据库中数据的精确性和可靠性。
完整性约束
完整性约束用于保证关系型数据库中数据的精确性和一致性。对于关系型数据库来说，数据完整性由参照完整性（referential integrity，RI）来保证。
有很多种约束可以起到参照完整性的作用，这些约束包括主键约束（Primary Key）、外键约束（Foreign Key）、唯一性约束（Unique Constraint）以及上面提到的其他约束。
如果没有为约束提供一个名字,那么ORACLE会分配一个系统生成的唯一名字,以SYS_开头,你可以使用关键字CONSTRAINTS后面跟随相关的约束名字来为约束指定名字.
语法
1.主键约束
添加:alter table table_name add primary key (字段)
删除:alter table table_name drop primary key
2.非空约束
添加:ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NOT NULL;
注意:添加非空约束的这一列的值不能已经存在空值
删除:ALTER TABLE <数据表名> CHANGE COLUMN <字段名> <字段名> <数据类型> NULL;
3.唯一约束
添加:alter table table_name add unique 约束名（字段）
删除:alter table table_name drop key 约束名
4.自动增长
添加:alter table table_name modify 列名 int auto_increment
删除:alter table table_name modify 列名 int
5.外键约束
添加:alter table table_name add constraint 约束名 foreign key(外键列)
references 主键表（主键列）
删除:
第一步:删除外键
alter table table_name drop foreign key 约束名
第二步:删除索引
alter table table_name drop index 索引名
[^1]:
约束名和索引名一样
6.默认值
添加:alter table table_name alter 列名 set default '值'
删除:alter table table_name alter 列名 drop default
介绍
索引在MySQL中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时。索引能够轻易将查询性能提高好几个数量级。
原理
通过数据结构不断地缩小想要获取数据的范围。相当于字典：先定位到章，然后定位到该章下的一个小节，然后找到页数。
索引的数据结构
不同的存储引擎支持的索引类型也不一样
索引分类
什么时候应当避免使用索引？
注意
一定是为搜索条件的字段创建索引
创建单列索引还是聚簇索引，要看每次查询中，哪些列在作为过滤条件的 WHERE 子句中最常出现。
如果只需要一列，那么就应当创建单列索引。如果作为过滤条件的 WHERE 子句用到了两个或者更多的列，那么聚簇索引就是最好的选择。
在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，
索引能够提高 SELECT 查询和 WHERE 子句的速度，但是却降低了包含 UPDATE 语句或 INSERT 语句的数据输入过程的速度。插入删除更新都很慢，只有查询快
索引的创建与删除不会对表中的数据产生影响
同 UNIQUE 约束一样，索引可以是唯一的。这种情况下，索引会阻止列中（或者列的组合，其中某些列有索引）出现重复的条目
创建
使用 CREATE INDEX index_name ON table_name; 语句，该语句允许对索引命名，指定要创建索引的表以及对哪些列进行索引，还可以指定索引按照升序或者降序排列。
单列索引
单列索引基于单一的字段创建，其基本语法如下所示：
CREATE INDEX index_name ON table_name (column_name);
唯一索引
唯一索引不止用于提升查询性能，还用于保证数据完整性。唯一索引不允许向表中插入任何重复值。其基本语法如下所示：
CREATE UNIQUE INDEX index_name on table_name (column_name);
聚簇索引：
聚簇索引在表中两个或更多的列的基础上建立。其基本语法如下所示：
CREATE INDEX index_name on table_name (column1, column2);
隐式索引由数据库服务器在创建某些对象的时候自动生成。例如，对于主键约束和唯一约束，数据库服务器就会自动创建索引。
添加索引
删除
DROP INDEX
DROP INDEX table_name.index_name;
说明：用于在现有表中添加，删除或修改列。也可以修改数据库（一般不）
语法
修改表名: alter table旧表名 rename to 新表名
修改表类型: alter table表名 modify 列名 数据类型
ALTER TABLE 表名 MODIFY [COLUMN] 字段名 新数据类型 新类型长度 新默认值 新注释;
-- COLUMN关键字可以省略不写
alter table table1 modify column column1 decimal(10,1) DEFAULT NULL COMMENT '注释';
-- 能修改字段类型、类型长度、默认值、注释
增加字段: alter table表名 add[column] column definition[ first/ after某一列名]
删除字段: alter table表名 drop[column列名
字段改名: ALTER TABLE 表名 CHANGE [column] 旧字段名 新字段名 新数据类型;(first; after某一列名
alter table table1 change column1 column1 varchar(100) DEFAULT 1.2 COMMENT '注释'; -- 正常，此时字段名称没有改变，能修改字段类型、类型长度、默认值、注释
alter table table1 change column1 column2 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常，能修改字段名、字段类型、类型长度、默认值、注释
alter table table1 change column2 column1 decimal(10,1) DEFAULT NULL COMMENT '注释' -- 正常，能修改字段名、字段类型、类型长度、默认值、注释
alter table table1 change column1 column2; -- 报错
修改字段排序:[ first/ after某一列名
修改表的字符集为gbk
alter table user character set gbk;
更改字段的数据类型，语法：
SQL Server / MS Access：
ALTER TABLE table_name ALTER COLUMN column_name datatype
MYSQL / Oracle：
ALTER TABLE table_name MODIFY COLUMN column_name datatype
修改数据库字符集：alter database database_name character set gbk;一般情况下不会
在表中插入数据
语法
单行：INSERT INTO 表名称 VALUES (值1, 值2,…)；也可以指定所要插入数据的列：INSERT INTO table_name (列1, 列2,…) VALUES (值1, 值2,…)
多行：不指定列时要确保值的顺序与表中的列顺序相同INSERT INTO table_name VALUES (1行, 2行,3行, …);
insert ignore into
当插入数据时，如出现错误时，如重复数据，将不返回错误，只以警告形式返回。所以使用ignore请确保语句本身没有问题，否则也会被忽略掉
insert … select … where not exist
根据select的条件判断是否插入，可以不光通过primary 和unique来判断，也可通过其它条件
Insert into select 使用另一个表填充一个表
作用：删除表中现有记录。
语法：
DELETE FROM table_name WHERE condition;
DELETE * FROM table_name;
注意
SQL DELETE 语句中的 WHERE 子句！ WHERE子句指定需要删除哪些记录。如果省略了WHERE子句，表中所有记录都将被删除！
处理效率：drop>trustcate>delete
drop删除整个表；trustcate删除全部记录，但不删除表；delete删除部分记录
删除表中的所有行，而不需要删除该表。这意味着表的结构、属性和索引将保持不变
delete不影响所用extent，高水线保持原位置不动；trustcate会将高水线复位。
作用：
UPDATE 语句用于更新表中已存在的记录。还可以使用AND或OR运算符组合多个条件。
语法
UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
注意
WHERE子句指定哪些记录需要更新。如果省略WHERE子句，所有记录都将更新
实例
UPDATE Customers SET ContactName = ‘Alfred Schmidt’, City= ‘Frankfurt’
WHERE CustomerID = 1;
SELECT TOP 子句用于指定要返回的记录数量。在包含数千条记录的大型表上很有用。返回大量记录会影响性能。
注：并不是所有的数据库系统都支持SELECT TOP子句。MySQL支持LIMIT子句来选择有限数量的记录，而Oracle使用ROWNUM。
limit [offset], [rows]
LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)
语法：
用在 SELECT 语句 末尾
一个参数
select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
两个参数
select tid from a limit 50000,200
定义：从一个表中复制数据，然后将数据插入到另一个新表中。用IN子句来复制表到另一个数据库中：
把所有的列都复制到新表中：
SELECT * INTO newtable [IN externaldb] FROM table1;
只复制希望的的列插入到新表中：
SELECT column_name(s) INTO newtable [IN externaldb] FROM table1;
Where之后不能以函数作为条件,比如 count
WHERE 子句 用于过滤记录提取满足指定标准的记录。WHERE子句不仅用于- -
SELECT语法
句法：SELECT [*] FROM [TableName] WHERE [condition1] [AND [OR]] [condition2]…
UPDATE
UPDATE “table_name” SET “column_1” = [new value] WHERE “condition”;
DELETE
DELETE FROM “table_name” WHERE “condition”;
SELECT field1field2, …fieldN table_name1 table_name2,…ORDER BY field1, [field2… ASC|DESC
语法
用于对结果集按照一个列或者多个字段进行排序。
oder by 多个不需要括号
默认按照升序对记录进行排序。如果需要按照降序对记录进行排序，您可以使用 DESC 关键字。
关键字ASC=Ascending 升序 ； 关键字DESC=Descending 降序
Order By +Limit 找出一列中最高/ 最低值
Select prod_price from products ORDER BY prod_price DESC LIMIT 1;
**位置靠后：**可以使用 where like等 但不能放在 where 前，通常是所有关键字中最后一个执行的关键字。
慎用，因为任何查询使用这个关键字后，查询效率都会降低。
可以用字段的位置 来代替 字段名 ORDER BY 2 DESC 代替 ORDER BY avg_score DESC
语法
SELECT column_name,column_nam FROM table_name ORDER BY column_name,column_name ASC|DESC;
作用：
删除全部记录，但不删除表。实际是：将直接删除原来的表， 并重新创建一个表， 因此执行速度比delete快.
注意：
语句
TRUNCATE TABLE table_name
当SQL Schema中有多个数据库时，在开始操作之前，需要选择一个执行所有操作的数据库。
句法
USE DatabaseName;
注意
USE语句的基本语法如下所示,数据库名称在RDBMS中必须是唯一的:
案例：
提供该列数据未指定时所采用的默认值
语法：
CREATE TABLE Persons ( City varchar(255) DEFAULT ‘Sandnes’ )
保证列中的所有数据各不相同
语法：CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE )
唯一标识其他表中的一条行/记录
作用：
CREATE
ALTER (MySQL / SQL Server / Oracle / MS Access：)
Drop:
MySQL：
ALTER TABLE Orders DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access：
ALTER TABLE Orders DROP CONSTRAINT fk_PerOrders
CHECK 约束：此约束保证列中的所有值满足某一条件
作用:
creat
ALTER:MySQL / SQL Server / Oracle / MS Access：
DROP:
SQL Server / Oracle / MS Access：
ALTER TABLE Persons DROP CONSTRAINT chk_Person
MySQL：
ALTER TABLE Persons DROP CHECK chk_Person
索引约束：用于在数据库中快速创建或检索数据
DROP INDEX语句 已用于删除表中的索引：
Access：
DROP INDEX index_name ON table_name
SQL Server：
DROP INDEX table_name.index_name
DB2 / Oracle：
DROP INDEX index_name
MySQL的DROP INDEX语法：
ALTER TABLE table_name DROP INDEX index_name
说明：
在每次插入新记录时自动创建主键字段的值。Auto-increment 会在新记录插入表中时生成一个唯一的数字。 默认情况下，AUTO_INREMENT的起始值为1，每个新记录增加1。新记录，我们不需要为"ID"栏指定值（自动添加唯一值）
语法：
若要以其他值开始AUTO_INREMENT序列，请使用以下SQL语法：
ALTER TABLE Persons AUTO_INCREMENT=100
Access
CREATE TABLE Persons ( ID Integer PRIMARY KEY AUTOINCREMENT ) 默认情况下，AUTOINCREMEN的起始值为1，每个新记录递增 1。指定“ID”栏以10开头，并递增5（ AUTOINCREMENT(10,5)）。
Oracle
使用序列（ sequence ）对象（该对象生成数字序列）创建自动增量（ auto-increment ）字段。
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10
上面的代码创建了一个名为seq_pean的序列( sequence) 对象，它以1开头，以1递增。此对象缓存10个值以提高性能。缓存选项指定要存储多少序列值以提高访问速度。
要在"Persons" 表中插入新记录，我们必须使用nextval函数，该函数从seq_hor序列检索下一个值：
INSERT INTO Persons (ID,FirstName,LastName) VALUES (seq_person.nextval,‘Lars’,‘Monsen’)
上面的SQL语句在 “Persons” 表中插入一个新记录。“ID” 列从 seq_person 序列中分配下一个数字。“FirstName"栏设置为"Lars”，“LastName"栏设置为"Monsen”
实例
CREATE TABLE Persons ( ID int NOT NULL AUTO_INCREMENT）
说明：
视图总是显示最新数据！每当用户查询视图时，数据库引擎就使用视图的 SQL 语句重新构建数据。
CREATE VIEW 语句
CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
更新视图CREATE OR REPLACE VIEW
CREATE OR REPLACE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition
撤销视图：DROP VIEW
DROP VIEW view_name
使用视图与计算字段
select prod_id,quantity,item_price,quantity*item_price AS expanded_price
from orderitems
where order_num =20005;
实例
“Current Product List”(当前产品列表)视图从"Products"表中列出了所有正在使用的产品（未停产的产品）。
CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No
查询上面视图：
SELECT * FROM [Category Sales For 1997] WHERE CategoryName=‘Beverages’
查看数据的创建，获取一条指定了原表的结构、索引等信息的
语法：
查数据库创建语句
SHOW CREATE TABLE
显示数据库
SHOW DATABASES
有哪些数据:
show tables
表的创建信息
show create table tabName;
这张数据表里都有哪些字段（列）
show colums from customers;
描述一下这张数据表
describe customers;
show status;
数据库的创建信息
show create database crashcourse;
安全权限；show grants;
show errors;
show warnings;
以student（sid,sname,birthday,sex）的查看为例。
【方式一】：desc student;
语法：desc 表名;---------------------用于查看表整体结构
【方式二】：describe student;
语法：describe 表名;---------------------用于查看表整体结构；
【方式三】：show columns from student;
语法：show columns from 表名;--------------------------用于查看表整体结构；
【方式四】：show create table student;
语法：show create table 表名;--------------------------用于查看表整体结构；
【方式五】：show full fields from student;
语法：show full fields from 表名;--------------------------------- 用于查看表整体结构;
【方式六】：show fields from student;
语法：show fields from 表名;----------------------------用于查看表整体结构；
【方式七】：desc student sname;
语法：desc 表名 成员名;--------------------------------用于查询表中的一部分；
【方式八】：show index from student;
语法：show index from 表名;------------------------------------用于查看表局部结构；这种显示不是很直观，也不是可以完全显示所有信息。
案例：
#1. 准备表
create table s1(
id int,
name varchar(20),
gender char(6),
email varchar(50)
);
\#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
declare i int default 1;
while(i<3000000)do
insert into s1 values(i,concat('egon',i),'male',concat('egon',i,'@oldboy'));
set i=i+1;
end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
\#3. 查看存储过程
show create procedure auto_insert1\G
\#4. 调用存储过程
call auto_insert1();
什么是触发器
当我执行一个事件时，同时也去执行另外一个事件。触发器是一个特殊的存储过程，不同的是存储过程要用CALL来调用，而触发器不需要使用CALL，也不需要手工启动，只要当一个预定义的事件发生的时候，就会被MYSQL自动调用。
触发器语法
| CREATE TRIGGER <触发器名称> | –触发器必须有名字，最多64个字符，可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象. |
|---|---|
| { BEFORE | AFTER } | –触发器有执行的时间设置：可以设置为事件发生前或后。 |
| { INSERT | UPDATE | DELETE } | –同样也能设定触发的事件：它们可以在执行insert、update或delete的过程中触发。 |
| ON <表名称> | –触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。 |
| FOR EACH ROW | –触发器的执行间隔：FOR EACH ROW子句通知触发器 每隔一行执行一次动作，而不是对整个表执行一次。 |
| <触发器SQL语句> | –触发器包含所要触发的SQL语句：这里的语句可以是任何合法的语句， 包括复合语句，但是这里的语句受的限制和函数的一样。 |
| 类型 | |
| SQL Server 触发器 | |
| SQLite 触发器（Trigger） | BEGIN与END（） 触发器执行的内容写出 BEGIN与END 之间，begin和end间的代码块的语句要写分号，否则不通过。 |
数据库事务的四个特性及含义
Mysql的技术特点是什么？
答：Mysql数据库软件是一个客户端或服务器系统，其中包括：支持各种客户端程序和库的多线程SQL服务器、不同的后端、广泛的应用程序编程接口和管理 工具。
无法登录MySQL会有哪写原因导致？答
CHAR和VARCHAR的区别？
答： CHAR和VARCHAR类型在存储和检索方面有所不同 。CHAR列长度固定为创建表时声明的长度，长度值范围是1到255当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。VARCHAR和CHAR的使用方面在于设计表之初的设计，两者各有千秋，VARCHAR比CHAR更省空间，但效率上略微的低于CHAR。
drop,delete与truncate的区别
答：
方式
drop直接删掉表 truncate删除表中数据，再插入时自增长id又从1开始 delete删除表中数据，可以加where字句。**
恢复
DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。
TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。
内存
表和索引所占空间。当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，而DELETE操作不会减少表或索引所占用的空间。drop语句将表所占用的空间全释放掉。
效率
一般而言，drop > truncate > delete
应用范围。
TRUNCATE 只能对TABLE；DELETE可以是table和view
TRUNCATE 和DELETE只删除数据，而DROP则删除整个表（结构和数据）。
公司的数据库现不支持emoji表情，如何解决？
如果是utf8字符集的话，需要升级至utf8_mb4即可。
描述下过往工作经验中mysql数据库的开发规范。
如何通过Unix shell 登录 MySQL
LIKE声明中的%和_是什么意思？
答：％对应于0个或更多字符，_只是LIKE语句中的一个字符。
如何在Unix和Mysql时间戳之间进行转换？
答： UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的
FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令
列对比运算符是什么？
答：在SELECT语句的列比较中使用=，<>，<=，<，> =，>，<<，>>，<=>，AND，OR或LIKE运算符。
MySQL查询语句中是否区分大小写？
答：要根据操作系统而定。windows中的MySQL查询语句无需区分大小写。但在Linux系统中需要区分大小写。
在Linxu系统中的规则如下：
1、数据库名与表名是严格区分大小写的；
2、表的别名是严格区分大小写的；
3、列名与列的别名在所有的情况下均是忽略大小写的；
4、变量名也是严格区分大小写的；
什么样的对象可以使用CREATE语句创建？
答：DATABASE、EVENT、FUNCTION、INDEX、PROCEDURE
TABLE、TRIGGER、USER 、VIEW
MYSQL支持事务吗？
答：在缺省模式下，MYSQL是autocommit模式的，所有的数据库更新操作都会即时提交，所以在缺省情况下，mysql是不支持事务的。
但是如果你的MYSQL表类型是使用InnoDB Tables 或 BDB tables的话，你的MYSQL就可以使用事务处理,使用SET AUTOCOMMIT=0就可以使MYSQL 允许在非autocommit模式，在非autocommit模式下，你必须使用COMMIT来提交你的更改，或者用ROLLBACK来回滚你的更改。
MYSQL数据表在什么情况下容易损坏？
答：服务器突然断电导致数据文件损坏。 强制关机，没有先关闭mysql 服务等。
超键、候选键、主键、外键
超键：在关系中能唯一标识元组的属性集称为关系模式的超键。一个属性可以为作为一个超键，多个属性组合在一起也可以作为一个超键。超键包含候选 键和主键。
候选键：是最小超键，即没有冗余元素的超键。
主键：数据库表中对储存数据对象予以唯一和完整标识的数据列或属性的组合。一个数据列只能有一个主键，且主键的取值不能缺失，即不能为空值 （Null）。
外键：在一个表中存在的另一个表的主键称此表的外键。
什么是事务？什么是锁？
事务：就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组，如果任何一个语句作失败那么整个操作就被失败，以后操作就会回滚到操作前状态， 或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过ACID 测试，即原子性，一致性，隔离 性和持久性。
锁：在所以的 DBMS 中，锁是实现事务的关键，锁可以保证事务的完整性和并发性。与现实生活中锁一样，它可以使某些数据的拥有者，在某段时间内不 能使用某些数据或数据结构。当然锁还分级别的。
出现Lock wait timeout exceeded; try restarting transaction是什么原因？
是锁等待超时。是当前事务在等待其它事务释放锁资源造成的。通过select * from information_schema.innodb_trx;可以查看到有哪些事务未释放资源。通过kill+线程号可以解决。例如kill+79干掉对应的线程。
Nonrepeatable Read是什么意思？与数据库、MySQL有什么关系？
Nonrepeatable Read – 不可重复读，是一种事务线程安全的问题。在MySQL中仅是一种隔离级别，MySQL的默认隔离级别是 – 可重复读 – Repeatable read
一个6亿的表a，一个3亿的表b，通过外键tid关联，你如何最快的查询出满足条件的第50000到第50200中的这200条数据记录。
解析：此题可以的关键是一个limit。
答：
1、如果A表TID是自增长,并且是连续的,B表的ID为索引
答：select * from a,b where a.tid = b.id and a.tid>500000 limit 200;
2、如果A表的TID不是连续的,那么就需要使用覆盖索引.TID要么是主键,要么是辅助索引,B表ID也需要有索引。
答：select * from b , (select tid from a limit 50000,200) a where b.id = a .tid;
用一条SQL 语句 查询出每门课都大于80 分的学生姓名
| name | kecheng | fenshu |
|---|---|---|
| 张三 | 语文 | 81 |
| 张三 | 数学 | 75 |
| 李四 | 语文 | 76 |
| 李四 | 数学 | 90 |
| 王五 | 语文 | 81 |
| 王五 | 数学 | 100 |
| 王五 | 英语 | 90 |
建表语句：
CREATE TABLE chengji(
name varchar(20),
kecheng varchar(20),
fenshu int
);
INSERT INTO chengji VALUES(‘张三’,‘语文’,81),(‘张三’,‘数学’,75),(‘李四’,‘语文’,76),(‘李四’,‘数学’,90),(‘王五’,‘语文’,81),(‘王五’,‘数学’,100),(‘王五’,‘英语’,90);
SELECT DISTINCT NAME FROM chengji WHERE NAME NOT IN (SELECT DISTINCT NAME FROM chengji WHERE fenshu <=80);
事务
一个事情可以分为多个步骤，这些步骤要么全部成功，要么全部失败，这件事情就可以称之为一个事务。一个sql语句，就是一个事务。四个特性：（ACID：ACID,原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。）
本质: 存储过程本质是函数；
调用CALL存储过程的执行称为调用
CALL productpricing(@pricelow,@pricehigh,@priceaverage) #计算并返回产品最低、最高和平均价格
暂时卡在open ordernumbers;P188
你想要某条语句在事件发生时自动自行
MySQL响应特定语句而自动执行的一条MySQL语句（或位于BEGIN和END之间的一组语句，
包括DELETE，INSERT，UPDATE，其他语句不支持触发器
只有表支持触发器（视图、临时表不支持），每个表最多支持6个触发器（每条INSERT、UPDATE、DELETE的之前和之后），单一触发器不能与多个事件、或多个表关联
创建
CREATE TRIGGER
CREATE TRIGGER newproduct
AFTER INSERT ON products
FOR EACH ROW
SELECT ‘product added’;
每插入一行都显示 ‘product added’
删除
DROP TRIGGER newproduct;
variable，内存中一个特定的位置，用来临时存储数据
所有mysql的变量都要以 @开始
Hadoop的一个数仓工具。提供SQL查询的语音：Hive SQL。Hadoop解决大数据储存和大数据分析，但系统启动慢，计算量小时响应速度相对于MySQL慢。目前通用的方式是：通过 Hive SQL对原数据集中处理，将处理好的数据导入MySQL中，这样前端能快速调用。
环境：mac
安装教程
安装包：第一个
环境变量配置
错误处理
数据库操作
连接 mysql -u root -p
环境变量配置好了，在终端输入mysql -u root -p可以登录mysql，不然必须切换到/usr/local/mysql/bin目录下
创建 create database数据库名
删除 drop database数据库名
查看 show database
使用命令show global variables like ‘port’;查看端口号
查看mysql基本信息：status;
选择use数据库名- show tables
退出：quit或exit
学习网站：
mySQL技能体系
0-9 ↩︎