数据库

表的列（垂直方向）称为字段，表的行（水平方向）称为记录

DDL（Data definition language数据定义语言）:create/drop/alter

DML（data manipulation language 数据操作语言）:select/insert/update/delete

DCL（data control language 数据控制语言）：commit/rollback/grant（赋予用户操作权限）/revoke（取消用户的操作权限）

u  数据类型的指定

定长字符串：向char（8）类型的列中输入‘abc’的时候，会以‘abc     ’(abc后面有5个半角空格)的形式保存起来。

可变长字符串：Varchar(8),输入‘abc’的时候保存的就是abc

主键（primary key）约束：Create table primary key(table_id)；把table_id指定为主键

u  表的创建

Create table tableName

( id char(4)  not null,

Name varchar(20)  ,

Primary key(id));

u  表的删除

Drop  table  tableName

u  表定义的更新

添加列：Oracle和Sql Server: Alter table tableName add columnName;

Mysql: Alter table tableName add column columnName;

Oracle: Alter table tableName add (columnName, columnName1, columnName2);

删除列：Alter table tableName drop column columnName;（Oracle,sql Server,同上column可不写）

向表table插入数据:mysql：

start transaction

Insert into tableName values(‘hehe’,1,2,3,4,);

Commit;

变更表名：

Rename table tableName to tableN;

查询基础

Select 列名，…

From 表名；

u  在select 语句中使用distinct可以删除重复行。

Sql：单行注释：--单行注释  。 多行注释：/*多行注释*/

u  字符串类型的数据原则上按照字典顺序进行排序。不能与数字的大小顺序混淆

u  希望选取null记录时，需要在条件 null运算符。希望选取不是null的记录时，需要在条件表达式中使用is not null运算符

聚合和排序

u  And运算优先于or运算执行，想要先执行or运算时可以使用括号。

u  Count：计算表中的记录数（行数）

Sum：计算表中数值列的数据合计值

Avg：计算表中任意列中数据的最大值

Max：求出表中任意列中数据的最大值

Min：求出表中任意列中数据的最小值

u  Count函数的结果根据参数的不同而不同。Count（*）会得到包含null的数据行数，而count（<列名>）会得到null之外的数据行数。

u  Max/min函数几乎适用于所有数据类型的列。Sum/avg函数只适用于数值类型的列

u  想要计算值得种类时，可以在count函数的参数中使用distinct

u  在聚合函数（将多行汇总为一行，输入多行输出一行）的参数中使用distinct，可以删除重复数据。

u  聚合键（在group by子句中指定的列）中包含null时，在结果中会以“不确定”行（空行）的形式表现出来。

使用group by子句时，select子句中不能出现聚合键之外的列名。

在 group by 子句中不能使用select子句中定义的别名。

Group by子句结果的显示是无序的。

只有select子句和having子句（以及order by子句）中能使用聚合函数。

u  Having子句要写在group by子句之后

在order by句中可以使用select子句定义的别名，having和group by 不可以

在order by子句中可以使用select子句未使用的列和聚合函数。

数据更新

Insert into 表名（列名，列名）values（值，值）

u  数据备份（复制）：Insert into表（列名，列名）select 列名，列名 from 表

Delete语句删除的对象是记录（行），不是表或者列。（truncate舍弃）

Update 表名 set 列名 = 表达式 where 条件

Commit 提交处理

Rollback  取消处理

不是自动提交，使用delete语句，可以用rollback 取消事务处理，恢复表中的数据

u  事务

ACID：原子性，一致性，隔离性，持久性

复杂查询

Create view 视图名称（列名，列名）

AS

Select语句

多重视图会降低sql的性能，要尽量避免在视图的基础上创建视图

定义视图不能使用order by子句；视图和表一样，数据行都是没有顺序的

l  视图可以被更新的条件：

①       Select 子句中未使用distinct

②       From子句中只有一张表

③       未使用group by 子句

④       未使用having 子句

视图和表需要同时更新，通过聚合得到的视图无法进行更新

l  删除视图

Drop view 视图名（列名，列名）

l  子查询

子查询作为内查询会首先执行

  随着子查询嵌套层数的增加，sql语句会变得越来越难读懂，性能也会越来越差。应避免使用多层嵌套的子查询。

标量子查询：必须且只能返回一行一列的结果（注意：不可以返回多行结果）

Where子句中不能使用聚合函数

l  在细分的组内进行比较时，需要使用关联子查询

函数、谓词、case表达式

u  函数种类

l  算数函数（用来进行数值计算的函数）

①       ABS（数值）：ABS计算绝对值的函数

②       MOD（被除数，除数）：MOD是计算除法余数（剩余）的函数，是modulo的简称（SQLServer不支持）。

③       ROUND（对象数值，保留小数的位数）：ROUND函数用来进行四舍五入操作。

字符串函数（用来进行字符串操作的函数）

①       字符串1||字符串2：||函数，拼接（sqlserver（+）和Mysql（concat）无法使用）

②       Length（字符串）：算出字符串的长度(字符串中包含多少个字符)（sqlserver（len）无法使用）

③       Lower（字符串）：小写转换，只针对英文字母；反之，upper大写转换函数

④       Replace（对象字符串，替换前的字符串，替换后的字符串）：可以将字符串的一部分替换为其他的字符串。

⑤       Substring（对象字符串 from 截取的起始位置 for 截取的字符数）（PostgreSQL,mysql支持）。Substr（对象字符串 from 截取的起始位置 for 截取的字符数）（oracle，db2专用语法）

l  日期函数（用来进行日期操作的函数）

①  Select current_date（MySQL取得当前日期）

Sqlserver：current_timestamp

Oracle：select current date from dual（临时表）

Db2：select current（半空格）date from sysibm.sysdummy1（临时表）

②  Select current_time（MySQL取得当前时间）

Select cast（current_timestamp as time）as cur_time

Oracle:select current_timestamp from dual;

Db2:select current time from sysibm.sysdummy1

③  Select current_timestamp（mysql取得当前的日期和时间）

Oracle：Select current_timestamp from dual

Db2:select current timestamp from sysibm.sysdummy1

④  Extract（日期元素 from 日期）：截取日期元素（返回是数值类型）

l  转换函数（用来转换数据类型和值得函数）

①  Cast（转换前的值 as 想要转换的数据类型）：类型转换（方便dbms内部处理开发的功能，而不是为了方便用户开发的功能）

②  Coalesce（数据1，数据2，数据3….）：将null转换为其他值

l  函数（用来进行数据聚合的函数）

u  谓词

谓词就是返回值为真值得函数

①  Like——字符串的部分一致查询

dd%（以dd开头的所有字符串）

%ddd%（包含ddd的字符串）

%ddd（选取以字符串ddd结尾的记录）

_（下划线）表示“任意1个字符”

②  Between——范围查询

Between结果中会包含and左右两个临界值。

③  Is null、is not null——判断是否为null

④  In——or的简便用法

⑤  使用子查询作为in谓词的参数

⑥  Not in 和子查询

Select 字段，字段

From 表名

Where 字段 not in（select子查询）

⑦  Exists

通常指定关联子查询作为exist的参数

作为exist参数的子查询中经常会使用select*

使用Not exist 替换not in

l  Case表达式（Oracle：decode    mysql：if）

搜索case表达式

Case when <判断表达式> then <表达式>

When <判断表达式> then <表达式>

When <判断表达式> then <表达式>

.

.

.

Selse <表达式>

End

集合运算

l  表的加法——union（并集）

集合运算符会除去重复的记录

l  集合运算的注意事项

①       作为运算对象的记录的列数必须相同

②       作为运算对象的记录中列的类型必须一致

③       可以使用任何select语句，但order by子句只能在最后使用一次

Union all（包含重复行的集合运算）

Intersect （选取表中公共部分）（交集）

Except（记录的减法）（差集）（oracle：minus  ）

l  联结（以列为单位对表进行联结）

①       内联结——inner join

②       外联结——outer join（left，right）

外联结中使用left、right来指定主表。使用二者所得到的结果完全相同

③       交叉联结——cross join（笛卡尔积）

SQL高级处理

l  窗口函数

窗口函数也称OLAP函数（online analytical processing，对数据库数据进行实时分析处理）（MySQL不支持）

<窗口函数> over （[partition by<列清单>]

Order  by<排序用例清单>）

  能够作为窗口函数使用的函数

①       聚合函数（sum、avg、count、max、min）

Select 字段名，字段名，

Avg（字段名）over （order by 字段 rows 2 preceding）as 字段名

From 表名

?  Rows 2 preceding：截止到之前2行（也就是最靠近的3行）

这样的统计方法称为移动平均（moving average）应用于对股市趋势的实时跟踪当中

?  Following（之后）：截止到当前记录之后2行（最靠近的3行）类似preceding

?  两个order by：over中的order by只是用来决定窗口函数按照什么样的顺序进行计算的，对结果的排列顺序没有影响。要使结果按照某列的升序进行排列，可以在select语句的最后加上order by列名

?  将聚合函数作为窗口使用时，会以当前记录为基准来决定统计对象的记录。

②       Rank、dense_rank、row_number等专用窗口函数

窗口函数兼具分组和排序两种功能

通过partition by 分组后的记录集合称为“窗口”

2  专用窗口函数的种类

ü  Rank 函数

计算排序时，如果存在相同位次的记录，则会跳过之后的位次。（有3条记录排在第1位时：1位、1位、1位、4位）

ü  Dense_rank函数

同样是计算排序，即使存在相同位次的记录，也不会跳过之后的位次。（有3条记录排在第1位时：1位、1位、1位、2位）

ü  Row_number函数

赋予唯一的连续位次（有3条记录排在第1位时：1位、2位、3位、4位）

用于专用窗口函数无需参数，所以通常括号中都是空的

原则上窗口函数只能在select子句中使用

l  Grouping运算符

Grouping运算符包含以下3种

①       Rollup——同时计算出合计值和小计值

Select 字段名，字段名

From 表名

Group by rollup（字段名）（此处MySQL：group by 字段名 with rollup）

Grouping（字段）as 字段名：grouping函数在其参数列的值为超级分组记录（合计行记录）所以产生的null时返回1，其他情况返回0；

Cast（字段 as varchar（16））：为了满足case表达式所有分支的返回值必须一致的规定

使用grouping函数能够简单地分辨出原始数据中的null和超级分组记录中的null。

②       Cube——用数据来搭积木（语法同rollup）

③       Grouping sets——取得期望的积木

SQL基础笔记