SQL 必知必会笔记--完整介绍sql技巧

PS:完整介绍数据处理，表结构操作，视图，事务处理，存储过程，约束，索引，游标，触发，数据库安全等sql技巧

增
1. 插入数据sql：INSERT INTO table_name(list_name1,list_name2,list_name4) VALUES(list_v1,list_v2,list_v4); 　　--插入仅有部分列的行，没有给值的列必须有默认值
2. 插入数据由子查询提供：INSERT INTO table_name SELECT....　　--可同时插入多列
3. 复制表：SELECT * INTO new_table FROM old_table;|CREATE TABLE new_table AS SELECT * FROM odl_table;(mysql语法)　　--不同dbms语法有差异
删
1. 删除行数据：DELETE table_name WHERE...　　--删除完整的行；快速删除TRUNCATE table_name
2. 删除指定列：UPDATE将指定列的值改为NULL　　--NULL值即无值
改
1. 更新数据：UPDATE table_name SET table.list1=v1,table.list2=v2 SELECT/WHERE....　　--可同时更新多行值，由子查询检索出更新的行
2. 用老表数据更新新表：UPDATE table_name FROM new_table;
3. 删除指定列：UPDATE table_name SET table_name.list1=null;　　--将table_name表的列1值更新为null，即删除列1
查
1. 基本结构：SELECT table_name.list_name,.... FROM table_name,.... WHERE ?GROUP BY ?ORDER BY ?UNION SELECT　　--?代表省略条件
2. 上方等效为：SELECT子句+FROM子句+WHERE子句+GROUP BY子句+ORDER BY子句+UNION子句
3. 常见的SELECT类型：检索多个列，DISTINCT,LIMIT（类似游标）
  1. SELECT table_name.list1,table_name.list2...
  2. SELECT DISTINCT list1,list2...　　--检索出来的行去除重有复值的行
  3. SELECT list1,list2 FROM table_name LIMIT 5 OFFSET 3　　--mysql语法，游标，检索出来的数据，打印五行，从第四行开始打印 //limit后的切片在MySQL中可简写成 LIMIT 3,5 其中3是偏移量，5是切片量
  4. SELECT TOP 5 list1,list2 FROM table_name;　　--sql server语法
  5. SELECT list1,list2 FEOM table_name FETCH FIRST 5 ROWS ONLY;　　--db2语法
4. 排序ORDER BY：默认升序，位置在最后，降序时参数只作用desc的前一个，支持多列排序和对非检索列排序FROM函数：查询对象表，查询多个表时涉及到联结；书写sql时使用完全控制语句结构更清晰，table_name.list_name
  1. SELECT list1,list2 FROM table_name ORDER BY list3;
  2. SELECT list1,list2,list3 FROM table_name ORDER BY list3DESC, list2;
5. 过滤WHERE：通过判定符，函数，多条件组合函数，通配符来设定过滤条件
  1. 判定符（=,!=,<,>,<=,>=）
  2. 函数1‘ list_name BETWEEN a AND b’，　函数2‘list_name is NULL’，函数3‘NOT list_name’
  3. 多个过滤条件时：使用'AND,OR,IN'来组合过滤条件；其中IN函数可用于子查询
  4. 通配符：三种通配符‘%，_，[]’；用法WHERE list_name LIKE %i_t_[V,v]　　--任何通配符都无法匹配null,%匹配任意位置任意数量字符；_一个符号匹配一个字符；[v1,v2]使用括号里面的任意一个字符来匹配
  5. in条件可用于多个对象
6. SELECT中对字段/列数据处理：有三种方法，‘字段计算，汇总数据，函数处理’
  1. 字段计算：数值计算和字段拼接；字段可以取别名，计算后的字段亦可
    1. 字段拼接：SELECT list1_name+list2_name AS new_list_name　　--非mysql数据库语法
    2. 字段计算：SELECT list1_name+|-|*|/list2_name AS new_list_name　　--|在这表示或，注意运算符执行的顺序
  2. 汇总数据：使用五个函数实现‘AVG,COUNT,MAX,MIN,SUM’；共同点是五个都只能作用于单列，可以是计算后的字段；且除count外均忽略NULL
    1. AVG求单列平均值
    2. COUNT求该列有多少行
    3. MAX该列最大值，若列值不是数值是，则取最后一行，即行号最大的值
    4. MIN该列最小值，若列值不是数值是，则取第一行，即行号最小的值
    5. SUM求该列所有值之和
    6. 单条SELECT中可以使用多个汇总函数的列亦可使用多个别名
  3. 函数处理：常用的函数有，‘文本处理函数，日期时间处理函数，数值处理函数’；函数除了在SELECT使用在WHERE中亦可使用
    1. 除字段空值函数，TRIM,RTRIM,LTRIM，除字段两边/右边/左边的空格；常见文本处理函数还有‘LOWER,UPPER,LEN,LEFT,RIGHT,SOUNDEX谐音函数’
    2. 日期时间处理函数，各dbms区别很大；DATEPART-SQL server；DATE_PART-postgreSQL；也可通过BETWEEN TO_DATE;YEAR-MYsql
    3. 数值处理函数，用来对数值进行二次计算的，用的较少，相对于上方其他函数迁移性较强
    4. 排名问题相关函数
```
1、领扣：排名相关的问题都使用sql的窗口函数（rank() over (or/gr) as(同/占用位置)dense_rank(同/不占用位置)row_number(顺序)）
```
7. 字段分组GROUP BY：对检索出的字段进行分组，分组可嵌套，位置在WHERE后；GROUP BY 可以结合HAVING使用，HAVING是分组专属过滤函数(
  
  GROUP BY Websites.name HAVING SUM(access_log.count) > 200;
  
  ①分组排序可以用max/min/窗口函数
  
  )
8. 查询分两种：单表查询/多表时跨表查询;跨表查询可通过子查询/联结/UNION实现组合查询;查询除了单条SELECT的查询还有多条SELECT的组合查询(union实现)；组合查询类似WHERE OR的变种，实际合并select的结果集；其中的任意一个select作用对象数量和数据类型要相同；且排序时在最后一条SELECT排序即可;SELECT one1 UNION SELECT one1 ORDER BY...（union会去对结果集去重/union all 不去重）
  1. 子查询实现跨表查询：基本结构，SELECT list_name,list2_name FROM table_name WHERE IN(SELECT list_1,list2 FROM table_name2)　　　　--in函数里面含子查询；将子查询作为母查询的过滤条件，实现跨表查询；可同时嵌套多个子查询；子查询是完整的查询命令，支持查询相关的所有操作
  2. 子查询除可以在WHERE中作为过滤条件；同时也可在SELECT中直接作为查询对象来实现跨表查询
  3. 联结：联结主要讲两类四种,内联；外联；自联，自然联接；注意跨表联结时，必须要有联接条件，且联结条件中两个表要有相同或同类列，如主键和外键
    1. 内联：最基本跨表联结；结构，SELECT table1_name.list_a,table2_name.list_b FROM table1_name,table2_name WHERE table1_name.same_list=table2_name.same_list　　--这里使用了完全控制，即表名.列名；AS可用于表别名
    2. 内联：其他结构，SLECT t1.list_a AS a,t1.list_b AS b,t2.list_c AS c FROM table1_name AS t1 INNER JOIN table2_name AS t1 ON t1.same_list=t2.same_list AND t1.list=value　　--使用了列别名和表别名；INNER JOIN ON是固定搭配,join可省；ON用法及作用和WHERE完全一致
    3. 自联：特殊的内联，将查询的单个通过别名命名为多个表，使用内联来建立这多个表的联接实现查询 select * from table1,table2 where....
    4. 自然联接：特殊的内联；联接条件必须是等价联接条件；且SELECT中查询的列不重复；同列不同表也不行；常用的内联基本都是自然联接
    5. 外联：外联和内联区别在于；内联检索出的是在多个表中有关联的行；而外联既能检索出多个表中有关联的行，也能检索无关联值的行比如空值
      1. 三种外联结构：SELECT table1_name.list_a,table2_name.list_b,table1_name.list_c FROM table1_name LEFT/RIGHT/FULL OUTER JOIN table2_name ON table1_name.same_list=table2_name.same_list　　--LEFT/RIGHT/FULL 和ON是固定搭配；分别是左/右/全外联；注意外联不能用WHERE做过滤条件；且不同外联过滤条件顺序有要求；如这条左外联时，过滤条件table1_name.same一定要在等号左边；右外联反之，outer可省略；
    6. 子查询转为外联时；注意子查询的查询对象和母查询的过滤条件是；外联的联接条件；同时外联可以联接任意数量的表；注意效率；
    7. 内连接/外连接/自/然连接/交叉连接简述：内联查询的结果集是交集/外联的是并集；语法上区别很大inner jon(内)/left,right,full outer join；但最大的区别还是用法不同，内连接只能查询出满足过滤条件的2结果集，外连接还能查询出不满足条件的结果集，比如a.id=b.id 外连接可以查询出这样的结果

表结构操作+视图（上方都是对数据的操作；下方是对表结构操作；本人日常工作基本都是通过可视化工具操作的😆）

库操作
- 查：show databases
- 增：create database name
- 删：drop database name
增
- 通过CREATE TABLE实现：基本结构，CREATE TABLE table_name(list1_name TYPE NOT NULL/NULL DEFAULT value/主键/外键, list2_name... )　　--TYPE是数据类型，DEFAULT设定默认值
- 上方命令说明：常用type有，‘CHAR,TEXT,INT,FLOAT/DECIMAL,DATE,TIME,DATETIME,SAMLLDATETIME’，即’字符串，长文本，整数，浮点数/精度可变浮点数，日期，时间，日期时间，日期时间精确到分‘
- 日期时间相关的数据：实际业务很多情况需要将系统时间作为默认值；可用DEFAULT CURRENT_DATE()，这是mysql语法，其他有区别
- 通过CREATE复制表：利用子查询可在创建表时，复制其他表内容；CREATE TABLE table_name AS SELECT....　　
删
- 通过DROP TABLE实现：结构，DROP TABLE table_name；　　--区分DELETA，deleta是清除表中内容，不删表结构
改
- 更改表结构一般涉及：增加列，删除列，增加列约束，重命名列
- 通过ALTER TABLE实现：结构，ALTER TABLE table_name ADD/DROP COLUMN list_name TYPE ‘NULL/NOT NULL DEFAULT default_value 约束‘　　--ADD/DROP COLUMN是啊、添加/删除列；’‘里面的配置，看实际需要选用　　
- 列名重命名：修改：alter table tablename change oldname newname type ;修改属性：alter table tablename change oldname oldname type ;重命名字段:alter table tablename change oldname newname integer;
查
- 查看表结构：DESC table_name
- 查看表数据：SHOW table_name

视图

- 视图:是虚拟的表，数据来源于SELECT子查询；用来格式化数据，对访问的数据做限定；除了不能和其他表进行联结；和正常的表功能大体一致
- 通过CREATE VIEW 实现：结构，CREATE VIEW view_name AS SELECT...；这就创建了视图，通过正常的sql语句可实现调用；如果调用视图的sql中本身就含有WHERE；且创建视图的子函数也具有过滤条件；两个过滤条件是可以合并的

存储过程+事物处理

存储过程
- 是什么：存储过程本质是对一条或多条的sql语句进行封装保存，复杂的多条sql语句被封装后，方便下次使用；
- 怎么样使用：分两种情况；创建存储过程和执行存储过程
  - 执行存储过程：EXECUTE procedure_name(param)　　
  - 各dbms创建存储过程语法差异较大，本书未讲mysql；点击查看MySQL存储过程
事物处理
- 是什么：事物处理通过确保成批的sql语句要么完全执行；要么完全不执行来确保数据库的完整性；通过管理管理确保SQL能正常执行；一旦出错有容错机制等每条语句
- 怎么样实现：通过四个功能函数实现，’启动，提交，回退，保留点‘；基本结构，启动事务》sql语句》每条语句后的回退》在回退命令前可能需要设置保留点》提交

游标+约束+索引+触发+数据库权限管理

游标：SELECT检索出来的对象称为结果集；游标能对结果集进行操作，使用游标应用程序能根据需要滚动或浏览结果集中的数据
- 作用：游标标记为只读，使数据只能读，不能编辑或删除；控制可以执行的定向操作（比如只展示多少行的数据）；规定访问范围，通过游标指定可访问的对象
- 创建/声明游标：结构，DECLARE cursos_name CURSOR FOR SELECT...　　--FOR 后面跟select语句；提供结果集
- 打开游标：OPEN CURSOR cursor_name
- 使用游标：FETCH cursor_name INTO cursor_named　　--这是Oracle的语法，表名从cursor_name游标检索，检索出来的数据放置在定义变量cursor_named中
- 关闭游标：CLOSE cursor_name　　-- 这是db2,Oracle的语法

约束：dbms通过在数据库表上施加约束来保证引用完整行，保证只在表中插入合法数据；数据库数据是存储在多个表中；每个表存储相应的数据；利用主键和外键建立从一个表到另一个表的引用
几种约束方法：主键；外键；唯一约束；检查约束(通常在可视化工具里面就可设定)

主键PEIMARY KEY：是表中用来标识行数据的；值唯一且确定的列
外键REFERENCES：用来关联其他表的列；要求该列和被关联列的主键键值；完全一致；但是外键的值可重复，且不一定完整包含被关联列的所有键值
唯一约束UNIQUE：创建/修改表结构时，在列中加入唯一约束作为约束条件；则该列不可录入重复值
检查约束CHECK：创建/修改表结构时，在列中加入检查约束作为约束条件；则不满足条件的值不能录入
实例1：CREATE TABLE peoducts(prod_id CHAR(20) NOT NULL PRIMARY KEY ,order_id CHAR(20) NOT NULL REFERENCES order(order_id),prod_price CHAR(20) NOT NULL UNIQUE CHECK (prod_price >=10);
实例2:ALTER TABLE peoducts ADD CONSTRAINT PRIMARY KEY(prod_id) ,ADD CONSTRAINT REFERENCES order(order_id),ADD CONSTRAINT UNIQUE(prod_price),ADD CONSTRAINT CHECK(prod_price>=10);

索引：一般在可视化工具中实现，对录入库中的数据按一定顺序存储，一般含存储顺序和位置及内容，这样在检索数据是无需遍历整个列，只需要根据索引找到需要值的位置
- CREATE INDEX index_name ON table_name(lie_name);
触发器：

触发器是特殊的存储过程；和存储过程不同，常规存储过程只是对sql语句存储；触发器可关联特定的表的特定操作；在这些表执行相关操作时；触发存储好的sql语句（可对插入数据，更新新老数据，删除数据进行处理）
结构： CREATE TRIGGER +trigger_name +作用对象和作用事件+触发存储过程; --具体语法各dbms差的很大不详细说明

枚举 :字段取定义的范围内的任意值

语法：enum,创建create table (lie1 int not null,lie2 enum(value1,value2))
增加数据：insert into table (lie2) value(value1)/（1）可以通过索引添加
修改表结构：alter table tablename change lie2(value3)
缺点：改枚举数据需要更新整个表的数据，扩展成本高，无法关联其他表数据，不是规范语法；建议少用

用法：实际操作

条件语法：IF(LIE=X,Y,Q)/CASE LIE WHEN X THEN Y ELSE Q END

应用：行列转换/更新时过滤出要更新的对象

SUM(CASE `month` WHEN "Mar" THEN revenue END) AS "Mar_Revenue",
set lie=CASE `month` WHEN "Mar" THEN revenue END

分组：分组可理解成建了一张虚拟表，将分组的多个列合并成了一个列，只有分组的字段是合并在一起的，其他列是未合并的，如下：group by id

+------+---------+-------+
| id   | revenue | month |
+------+---------+-------+
|      | 8000    | Jan   |
| 1    | 7000    | Feb   |
|      | 6000    | Mar   |
+------+---------+-------+
| 2    | 9000    | Jan   |
+------+---------+-------+
| 3    | 10000   | Feb   |
+------+---------+-------+

- 由图可知，为何不能在select时，单独对其他列操作了，这是其他列输入是多个参数的，如果要操作就需要聚合函数来进行参数传递，因为聚合函数支持多个输入，输出一个；
- 结合聚合函数来对分组的对象进行遍历，传值给条件结构，可实现，行列转换及表数据的格式化

其他：mysql相关操作

查看版本：mysqld -v
服务启/停/重启:systemctl start/stop/restart mysqld
服务状态：systemctl status mysqld
服务是否在运行：systemctl is-active mysqld
设置/关闭开机自启动：systemctl enable/disable mysqld/systemctl is-enabled mysqld(是否是开机自启动)
root的初始随机密码：grep 'temporary password' /var/log/mysqld.log
登陆mysql：mysql -u username -h localhost(省) -p
创建用户：create user 'username'@'localhost' identified by 'passwd'
修改用户密码：alter user 'username'@'localhost' identified by 'passwd'
修改当前用户密码：set password=password('passwd')
删除用户：drop user 'username'@'localhost
修改用户权限：grant all privileges on '.' to 'username'@'%' identified by 'passwd'(给用户所有的权限)
删除用户权限：REVOKE <privilege> ON <database>.<table> FROM '<user>'@'<host>'

SQL复杂用法：窗口函数

场景：用来解决分组排序；TOPn问题；

特点：分组排序时，分组后的记录不会像group by一样变少；

模型：

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

支持两类窗口函数，专用窗口函数和聚合函数，如下：

1）专用窗口函数，比如rank, dense_rank, row_number等

2）聚合函数，如sum. avg, count, max, min等

注意以上语法，可以省略分组，或者省略排序；都是可以的；即分组后操作/排序吼操作的场景

实例：链接：https://pan.baidu.com/s/1dIZRk6Qcly9I5mKRQ2vGLw 密码：lgt8

sql生成测试数据：创建临时表，用Python生成测试数据，通过load date导入临时表，在用临时表将数据插入源表，要使得数据变得更随机更新数据update table set lie1=v1

分组排序实例：https://www.zhihu.com/tardis/zm/art/92654574?source_id=1003

窗口函数详解：https://zhuanlan.zhihu.com/p/84708880

博客内容来源于sql必知必会的读后感；入门书籍比较全面的讲解了sql,零基础的朋友推荐读书，阅读博客没有效果。

posted @ 2018-06-08 13:22 丶gavin 阅读(583) 评论(2) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

丶gavin

2018减肥20斤；不减不改名

SQL 必知必会笔记--完整介绍sql技巧

PS:完整介绍数据处理，表结构操作，视图，事务处理，存储过程，约束，索引，游标，触发，数据库安全等sql技巧

目录

书写顺序/执行顺序

数据处理

增：插入数据+复制表

删：删除行数据+删除指定列数据

改：更新数据

查：基本结构+相关函数+多表查询+多条查询语句 表结构操作+视图

存储过程+事物处理

游标+约束+索引+触发+数据库权限管理

书写顺序/执行顺序：

数据处理：

增

删

改

查

表结构操作+视图（上方都是对数据的操作；下方是对表结构操作；本人日常工作基本都是通过可视化工具操作的😆）

库操作

增

删

改

查

视图

存储过程+事物处理

存储过程

事物处理

游标+约束+索引+触发+数据库权限管理

游标：SELECT检索出来的对象称为结果集；游标能对结果集进行操作，使用游标应用程序能根据需要滚动或浏览结果集中的数据

约束：dbms通过在数据库表上施加约束来保证引用完整行，保证只在表中插入合法数据；数据库数据是存储在多个表中；每个表存储相应的数据；利用主键和外键建立从一个表到另一个表的引用

几种约束方法：主键；外键；唯一约束；检查约束(通常在可视化工具里面就可设定)

索引：一般在可视化工具中实现，对录入库中的数据按一定顺序存储，一般含存储顺序和位置及内容，这样在检索数据是无需遍历整个列，只需要根据索引找到需要值的位置

触发器：

枚举 :字段取定义的范围内的任意值

用法：实际操作

其他：mysql相关操作

SQL复杂用法：窗口函数

博客内容来源于sql必知必会的读后感；入门书籍比较全面的讲解了sql,零基础的朋友推荐读书，阅读博客没有效果。

公告

查：基本结构+相关函数+多表查询+多条查询语句　　表结构操作+视图