数据存储学习——SQL复习

前言

        决定研究生方向是搞数据存储了，师兄给了MySQL的一些进阶深入了解优化方面的东西，觉得有点看不懂orz，想着时间还有，干脆就从头开始捋一遍，先SQL，再MySQL->HBase->Hive->Redis等。SQL复习就简单的看了下菜鸟教程上的SQL教程，个人感觉还是不错的，这篇博客主要还是记录上自己不是很熟悉的知识点以供以后复盘，全部教程链接在这里：http://www.runoob.com/sql/sql-tutorial.html

SQL知识部分汇总

一、SQL简介

        Structured Query Language　结构化查询语言，是用于访问和处理数据库的标准的计算机语言，数据库包括MySQL、SQL Server、Access、Oracle、Sybase、DB2 等关系型数据库。

        功能：查询、插入、更新、删除数据；创建数据库、表、存储过程、视图；设置表、存储过程和视图的权限等

        对于数据类型，需要注意不同数据库系统的数据类型可能有所不同，在具体使用的时候要去查看文档确保正确使用！

二、SQL命令

以下介绍SQL的几种基本的常用的命令，还是要注意不同的数据库系统的细节语句可能会有所差异，具体使用具体查文档，有用到例子的基本上都是以MySQL为例。

1. SELECT * FROM TABLE_NAME; | SELECT COLUMN_NAME(s）FROM TABLE_NAME;

         后面可以跟where过滤子句以及order by排序、group by分组、having聚合函数下过滤条件等

     2. UPDATE TABLE_NAME SET COLUMN_NAME=NEW_VALUE WHERE CONDITION；

         需要注意的是，使用update一定要小心谨慎使用不加where过滤子句的情况，随意使用的话会导致表内数据都被更新且很难恢复，所以定要谨慎！

     3. DELETE COLUMN_NAME FROM TABLE_NAME WHERE CONDITION;

         需要注意的同update，不加where子句的情况要谨慎使用！

     4. INSERT INTO TABLE_NAME VALUES(COLUMN_VALUE1，...); | INSERT INTO TABLE_NAME(COLUMN_NAME1，...) VALUES(COLUMN_VALUE1，...);

     5. CREATE/ALTER/DROP DATABASE;

         一个数据库系统中可以有多个数据库，一个数据库下可以有多个数据表。

     6. CREATE TABLE TABLE_NAME(COLUMN_NAME1 DATATYPE CONSTRTAINT,...);

         当需要使用自增列时，在那个列后面加上AUTO INCREMENT即可，一般初始值是1，递增值是1，在MySQL中可以使用alter table table_name auto_increment=value;来修改初始值为value

     7. ALTER TABLE 在已有的表中添加、删除或修改列

• 添加新列：ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE (CONSTRAINT);
• 删除某列：ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME; 有些数据库系统不支持
• 修改某列：ALTER TABLE TABLE_NAME MODIFY COLUMN COLUMN_NAME DATA_TYPE (CONSTRAINT); 以MySQL为例。Microsoft系统下的SQL Server/MS Access等为alter table table_name alter column column_name data_type (constraint);

      8. DROP TABLE;删掉表结构及表数据，不会触发trigger不可逆

      9. CREATE/DROP INDEX；以MySQL为例，删除索引：ALTER TABLE TABLE_NAME DROP INDEX INDEX_NAME;

          索引：对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。目的就是加快检索表中数据，亦即能协助信息搜索者尽快的找到符合限制条件的记录ID的辅助数据结构。

      10. CREATE/DROP VIEW; 创建视图，永远显示最新数据

            CREATE OR REPLACE VIEW VIEW_NAME AS SELECR COLUMN_NAME(s) FROM TABLE_NAME WHERE CONDITION;

            视图作用：隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系；供了一个统一访问数据的接口，可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限；加强了安全性，使用户只能看到视图所显示的数据；可以被嵌套，一个视图中可以嵌套另一个视图。

三、SQL的一些重要知识点

1. AS 别名的使用   

   一般使用在：查询中涉及超过一个表；查询中使用了函数；列名称很长或者可读性差；需要把两个列或者多个列结合在一起。 

   SELECT COLUMN_NAME AS ALIAS_NAME FROM TABLE_NAME; 列别名

   SELECT COLUMN_NAME FROM TABLE_NAME AS ALIAS_NAME; | SELECT ALIAS_NAME.COLUMN_NAME FROM TABLE_NAME AS ALIAS_NAME; 表别名

   也可以将多个列结合在一起创建一个别名。

   CONCAT(COLUMN_NAME1, ’, ’, COLUMN_NAME2, ‘, ‘, ...) AS ALIAS_NAME 多个列间值用’, ‘隔开

2. CONSTRAINT 约束的使用

    一般SQL上的约束有NOT NULL、UNIQUE、PRIMARY KEY、FOREIGN KEY、CHECK、DEFAULT，约束可以在create表的时候添加，也可以在表创建后alter添加到某列上。

     一张表中只能有一个PRIMARY KEY，可以有多个UNIQUE & NOT NULL

     以MySQL为例alter DEFAULT约束：ALTER TABLE TABLE_NAME ALTER COLUMN_NAME SET DEFAULT DEFAULT_VALUE;

3. JOIN 连接的使用

    用于把来自两个或多个表的行结合起来。在select中使用，可以看做是由SELECT语句指定的列组成的新表

   INNER JOIN = JOIN

   SELECT COLUMN_NAME(s) FROM TABLE_NAME1 INNER JOIN TABLE_NAME2 ON TABLE_NAME1.COLUMN_NAME1=TABLE_NAME2.COLUMN_NAME2;

   ON 和 WHERE的区别：on是生成临时表的条件，where是生成临时表后对显示结果集的过滤

   LEFT JOIN = LEFT OUTER JOIN 返回左表所有值，右表不匹配的值为NULL

   RIGHT JOIN = RIGHT OUTER JOIN 返回右表所有值，左表不匹配的值为NULL

   FULL OUTER JOIN  MySQL不支持

4. UNION/UNION ALL 集成select结果集

    SELECT COLUMN_NAME(s) FROM TABLE_NAME1 UNION SELECT COLUMN_NAME(s) FROM TABLE_NAME2;

    其中两个表的COLUMN_NAME的类型需要相似，默认union结果集唯一，如果允许结果中有重复数据，使用union all

    可以对最后的结果进行排序，使用order by，但需要注意order by后面的列名和第一个select的column_name保持一致！

5. INSERT INTO SELECT & SELECT INTO的使用

•     insert into select 从一个表复制数据，然后把数据插入到一个已存在的表中，且目标表中任何已存在的行都不会受影响，MySQL支持.只复制表数据，表结构可一样可不一样。

   INSERT INTO TABLE_NAME2 SELECT * FROM TABLE_NAME1; 目标表和原表结构一样

   INSERT INTO TABLE_NAME2(COLUMN_NAME,...)  SELECT COLUMN_NAME(s) FROM TABLE_NAME1;两个表结构不同

•    select into 从一个表复制数据，然后把数据插入到另一个新表中，一定复制表结构、可以复制表数据。MySQL不支持，但可以用CREATE TABLE NEW_TABLE_NAME AS SELECT * FROM OLD_TABLE_NAME;

  create table table_name_new as select * from table_name_old; 复制表结构及其所有/全部数据

  create table table_name_new as select * from table_name_old where 1=2; | create table table_name_new like table_name_old; 只复制表结构

 SELECT * INTO newtable FROM table1 WHERE 1=0; 通过另一种模式创建一个新的空表。只需要添加促使查询没有数据返回的 WHERE 子句

6. DROP、TRUNCATE & DELETE的使用

• DROP：删除表内容和结构，释放空间，没有备份表之前要慎用。依赖于该表的存储过程/函数将保留, 但是变为 invalid 状态。ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚，操作不触发trigger
• TRUNCATE:删除表的内容，表的结构存在，可以释放空间,没有备份表之前要慎用，删除的数据不可恢复。ddl, 操作立即生效, 原数据不放到 rollback segment 中, 不能回滚，操作不触发trigger
• DELETE：删除表的内容，表的结构还存在，不释放空间，可以回滚恢复(如果有回滚机制)。dml, 这个操作会放到 rollback segement 中, 事务提交之后才生效，如果有相应的trigger，执行的时候将会被触发。

执行速度：DROP > TRUNCATE > DELETE

具体使用：删除部分数据用delete（加上where过滤子句），删除表用drop，保留表只删数据且与事务无关用truncate。

7. REGEXP、NOT REGEXP的使用

   以MySQL为例，模糊查询的操作符有like、not like、regexp、not regexp。使用like是直接将后面的字符当做普通字符来看待的，里面%表示多个字符（可以为0个），_表示一个字符；如果后面是[charlist]、[^charlist]通配符且需要其有效而非被看做普通字符，需要将操作符换为正则表达式regexp、not regexp。

   select * from persons WHERE City LIKE '[b]eijing'; 将查出 city 为 [b]eijing 的行，而不是 city 为 beijing 的行。

   select * from persons WHERE City REGEXP '[b]eijing' ; 结果为city为beijing的行。

总结

       SQL的函数这里没有总结，这个感觉有点像很多语言的API一样，需要用到的时候再查看文档就可以啦~

       其实很多东西虽然以前课本或者学习视频中学过，但是时间已过不太用可能就忘了，另外知道或者了解一部分不代表学会了，很多东西还是要时常复习，升入学习，更要在实际应用中灵活运用才是。

       别轻待任何一个知识，谦虚学习，自己不懂的东西还多的很哩(╯▽╰)