数据库知识进阶
1.数据库基础
数据类型
1.数字类型
bight -2^63--2^62-1
int -2^31--2^31-1
smallint -2^15--2^15-1
tinyint 0--255
float -1.79E+308--2.23E-308以及2.23E-308--1.79E+308
2.时间类型
time 12:35:29.123456
date 2007-05-08
smalldatetime 2007-05-08 12:35:00
datetime 2007-05-08 12:35:00.123
datetime2 2007-05-08 12:35:00.1234567
3.字符串类型
char[(n)] 固定长度。n用于定义字符串长度，并且它必须为1-8000之间的值
varchar [(n|max)] 可变长度。n用于定义字符串长度，并且它可以为1-8000之间的值
nchar[(n)] 固定长度的Unicode字符串1数据。n用于定义字符串长度，并且它必须为1-4000之间的值
nvarchar[(n)] 可变长度的Unicode字符串1数据。n用于定义字符串长度，并且它可以为1-4000之间的值
2.sql基本语法
2.1 mysql基本语法
1 创建数据库: CREATE DATABASE [DATABAE_NAME];
例: CREATE DATABASE StudentData;
2 删除数据库: DROP DATABASE [DATABAE_NAME];
例: DROP DATABASE StudentData;
3 创建表:
CREATE TABLE table _name(
column_name column_data_type,
column_name column_data_type,
column_name column_data_type
...
);
例: CREATE TABLE Students(
id INT NOT NULL,
age INT NOT NULL,
name VARCHAR(255),
major VARCHAR(255),
PRIMARY KEY ( id ));
4 删除表: DROP TABLE [table_name];
例: DROP TABLE Students;
5 插入数据: INSERT INTO table_name VALUES (column1，column2，...);
例: INSERT TINTO students VALUES (1，18，‘Mumu '， "Java');
6 查询数据: SELECT column_name,column name, ...FROM table_name WHERE conditions;
例: SELECT name, ageFROM studentswHERE id = 1;
SELECT name, majorFROM students WHERE name LIKE " %om%" ;
7 更新数据: UPDATE table_name SET column_name = value，...WHERE conditions;
例: UPDATE students SET age=20 WHERE id=1;
8 删除数据: DELETE FROM table_name wHERE conditions;
例: DELETE FROM students WHERE id=1;
2.2 mysql复杂查询
1.了解关键字
| 关键字 | 说明 | 关键字 | 说明 | 关键字 | 说明 |
|---|---|---|---|---|---|
| insert | 新增 | create | 创建 | grant | 授权 |
| delete | 删除 | drop | 删表 | revoke | 撤销权限 |
| update | 修改 | alter | 修改表结构 | commit | 事务提交 |
| select | 查询 | truncate | 删除 | rollback | 事务回滚 |
| as | 设置别名 | distinct | 去重 | where | 判断条件 |
| between and | 范围查询 | like | 模糊查询 | in | 范围查询 |
| group by | 分组 | join | 表连接 | order by | 排序 |
| having | 分组条件判断 | limit | 条数限制 | from | 查自 |
| exists | 是否存在 | default | 设置默认值 | offset | 跳过 |
| and | 条件连接 | or | 条件或 | union | 结果组合 |
| rename | 重命名 | add | 添加字段 | index | 索引 |
| view | 视图 | function | 函数 | procedure | 存储过程 |
2.函数使用
| 函数名 | 作用 | 举例 |
|---|---|---|
| AVG(某字段) | 返回某字段的平均值 | -- |
| COUNT(某字段) | 返回某字段的行数 | -- |
| MAX(某字段) | 返回某字段的最大值 | -- |
| MIN(某字段) | 返回某字段的最小值 | -- |
| SUM(某字段) | 返回某字段的和 | -- |
| CONCAT(str1, str1...strn) | 字符串连接 | SELECT CONCAT('My','S','QL');返回：MySQL |
| INSERT(str,pos,len,newstr) | 字符串替换 | -- |
| LOWER(str) | 将字符串转为小写 | SELECT LOWER('MySQL');返回：mysql |
| UPPER(str) | 将字符串转为大写 | SELECT UPPER('MySQL'); 返回：MYSQL |
| SUBSTRING(str,num,len) | 字符串截取 | SELECT SUBSTRING('JavaMySQLOracle',5,5);返回：MySQL |
| CEIL(x) | 返回大于或等于数值x的最小整数 | SELECT CEIL(2.3)返回：3 |
| FLOOR(x) | 返回小于或等于数值x的最大整数 | SELECT FLOOR(2.3)返回：2 |
| RAND() | 返回0~1间的随机数 | SELECT RAND()返回：0.5525468583708134 |
| CURDATE() | 获取当前日期 | SELECT CURDATE(); 返回：2022-08-08 |
| CURTIME() | 获取当前时间 | SELECT CURTIME(); 返回：19:19:26 |
| NOW() | 获取当前日期和时间 | SELECT NOW(); 返回：2022-08-08 19:19:26 |
| WEEK(date) | 返回日期date为一年中的第几周 | SELECT WEEK(NOW()); 返回：26 |
| YEAR(date) | 返回日期date的年份 | SELECT YEAR(NOW()); 返回：2022 |
| HOUR(time) | 返回时间time的小时值 | SELECT HOUR(NOW()); 返回：9 |
| MINUTE(time) | 返回时间time的分钟值 | SELECT MINUTE(NOW()); 返回：43 |
| DATEDIFF(date1,date2) | 返回日期参数date1和date2之间相隔的天数 | SELECT DATEDIFF(NOW(), '2008-8-8’); 返回：2881 |
| ADDDATE(date,n) | 计算日期参数date加上n天后的日期 | SELECT ADDDATE(NOW(),5); 返回：2016-09-02 09:37:07 |
3.多表连接
1.子查询： 嵌套查询。查询里面嵌套了小的查询语句。
例 ：
select sum(cj) from cjb where kch=(select kch from kcb where kcm='java语言');
说明: select kch from kcb where kcm='java语言'该语句作为条件嵌套进主查询语句中,这种形式叫子查询
2.关联查询: 将多张表联接成一张大的表，然后从这张大表里去查询特定的数据。
例: select kcb.kch,kcm,xh,cj from kcb,cjb where kcb.kch=cjb.kch;
说明: 两张表结合查询 使用where条件进行连接 去处理笛卡尔积,这种形式叫关联查询
3.多张表关联查询
例: select xm,jg,cjb.kch,kcm,cj from xsb,cjb,kcb where xsb.xh=cjb.xh and kcb.kch=cjb.kch and jg='北京';
说明: 两张表或更多张表结合查询 使用where条件进行连接 去处理笛卡尔积,这种形式叫多表查询
4.别名 当表名较长时，通常我们习惯为表取别名，以精简sql语句的长度。
例: select kch,kcm,xh,cj from kcb a,cjb b where a.kch=b.kch;
说明: 别名加在from子句 表名后即可。可以用as 也可以不用
5.内连接、外连接
内连接: 只返回满足关联条件的结果集。（最典型的联接运算，通常会用到 = <>之类的运算符 ）inner join on
外连接
左外连接（left join...on）： 以左表为主表,不满足条件则补空
右外联接（right join...on）： 以右表为主表 不满足条件则补空
全外联接 没有主表 两边不满足条件都补空
注意： mysql不支持全外联接full join。但是可以通过集合union来实现。（将左外和右外中间加个union连起来）
例:
#左外联接来实现：
SELECT xsb.xh,xm,kch,cj from xsb left join cjb on xsb.xh=cjb.xh;
#右外联接来实现：
SELECT xsb.xh,xm,kch,cj from cjb right join xsb on xsb.xh=cjb.xh;
#全外联接：全外联接通常使用full join。但是mysql不支持full join。别的关系型数据库（例如oracle）就是支持的。
SELECT xsb.xh,xm,kch,cj from xsb left join cjb on xsb.xh=cjb.xh
union
SELECT xsb.xh,xm,kch,cj from xsb right join cjb on xsb.xh=cjb.xh;
4.sql优化
1.适合建立索引的条件：
1. 在join的on条件字段，
2. where的条件字段，
3. order by，group by,distinct 涉及的字段进行所以的建立。
4. 使用列的类型小的创建索引
5. 使用最频繁的列放到联合索引的左侧\
2不适合建立索引
1. where中使用不到的字段，
2. 数据量小的表最好不要使用索引，
3. 有大量重复数据的列上不要建立索引，
4. 避免对经常更新的表创建过多的索引，
5. 不建议用无序的值作为索引，删除不再使用或者很少使用的索引，
6. 不要定义冗余或重复的索引；
3.全表扫描问题,以及处理方式
1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
2.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
如：
select id from t where num=10 or num=20
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
4.in 和 not in 也要慎用，否则会导致全表扫描，如：
select id from t where num in(1,2,3)
对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
5.下面的查询也将导致全表扫描：
select id from t where name like '%abc%'
6.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
如：
select id from t where num/2=100
应改为:
select id from t where num=100*2
7.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where substring(name,1,3)='abc'--name以abc开头的id
应改为:
select id from t where name like 'abc%' \
8.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
9.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
10.不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(...)
11.很多时候用 exists 代替 in 是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)
12.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
13.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，
因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
14.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。
这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
15.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
16.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
17.避免频繁创建和删除临时表，以减少系统表资源的消耗。
18.临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。 19.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
20.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。 21.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。 22.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
23.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。
在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
24.尽量避免大事务操作，提高系统并发能力。
25.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
3.sql索引
索引是帮助MySql高效获取数据的数据结构\
3.1 索引的优势劣势
优势
1.添加所以可以提高数据检索的效率,降低数据库的IO成本。
2. 通过索引列对数据进行排序,降低数据查询的成本,降低CPU的消耗。
劣势
1.索引占据空间。
2.更新表的同时,索引会重排序,所以INSERT、 UPDATE、 DELET操作比较慢。
3.2 索引底层数据结构
Hash索引
哈希索引是一种基于哈希表的索引结构，可以结合java的hashmap来理解
实现原理：对索引列计算哈希值,然后把记录映射到哈希槽中，然后指向对应记录行的地址。因此，在查询的时候只要正确匹配到索引列，就能在O(1)的时间复杂度内查到记录。
附图示例，左边是哈希槽，右边是数据列：
存在的问题：
1.哈希索引不支持排序
2.哈希索引不支持部分列索引查找,只支持等值查询，无法提供范围查询功能
3.hash冲突问题 哈希索引的查找效率是非常高的，大多数时候都能在O(1)的时间内找到记录，除非哈希冲突很高。
二叉树
二叉树是每个节点最多有两个子树的有序树,二叉查找树也称为有序二叉查找树;
任意节点左子树不为空,则左子树的值均小于根节点的值
任意节点右子树不为空,则右子树的值均大于根节点的值
任意节点的左右子树也分别是二叉查找树
存在的问题:
如下图，可以看出，二叉树出现单边增长时，二叉树变成了“链”，这样查找一个数的时候，速度并没有得到很大的优化。
平衡二叉树(AVL)
又称AVL树，指的是左子树上的所有节点的值都比根节点的值小，而右子树上的所有节点的值都比根节点的值大，且左子树与右子树的高度差最大为1
存在的问题:
旋转耗时
红黑树
红黑树（Red Black Tree） 是一种自平衡二叉查找树，是在计算机科学中用到的一种数据结构，典型的用途是实现关联数组。 红黑树有如下特点:
0.节点是红色或者黑色
1.根节点是黑色
2.每个叶子的节点都是黑色的空节点(NULL)
3.每个红色节点的两个子节点都是黑色的。
4.从任意节点到其每个叶子的所有路径都包含相同的黑色节点。
存在的问题:
红黑树虽然和二叉树相比，一定程度上缓解了单边过长的问题，但是它依旧存储高度问题。 假设现在数据量有100万，那么红黑树的高度大概为 100,0000 = 2^n， n大概为 20。那么，至少要20次的磁盘IO，这样，性能将很受影响。如果数据量更大，IO次数更多，性能损耗更大。所以红黑树依旧不是最佳方案。
B-Tree
b-tree 是一种树形结构的数据结构,其叶节点具有相同的深度。节点中的元素从左向右递增排序,所有的元素不重复,B-Tree有以下几种特点：
1.d为大于1的一个正整数，称为BTree的度；
2.h为一个正整数，称为BTree的高度；
3.key和指针互相间隔，节点两端是指针；
4.叶子节点具有相同的深度，叶子节点的指针为空，节点中数据索引(下图中的key)从左往右递增排列。
存在的问题:
1.深度问题
2.范围查找问题
B+Tree
B+Tree是一种进化的树结构,比b树有更高的查询性能,其叶子节点包含所有索引字段,叶子节点用双向指针相连，提高区间访问性,非叶子节点不存储data,只存储索引(冗余),可以放更多的索引,b+tree有以下特点:
1.非叶子节点不存储data，只存储索引，可以存放更多索引。
2.叶子节点不存储指针。
3.顺序访问指针，提高区间访问性能。
4.非叶子节点中的索引最终还是会在叶子节点上存储一份，也就是叶子节点会包含非叶子节点上的所有索引。
5.一个父节点，它的左侧子节点都小于父节点的值，右侧的子节点都大于等于父节点的值。
6.每一层节点从左往右都是递增排列，无论是数值型还是字符型。
基于其特点,B+tree很好的解决了其他数据结构存在的问题,所以很多数据库都是使用的b+tree索引结构,比如mysql.
