sql笔记.docx

sql笔记.docx

《sql笔记.docx》由会员分享，可在线阅读，更多相关《sql笔记.docx（14页珍藏版）》请在冰豆网上搜索。

sql笔记

sql笔记

自己总结的SQL笔记，给友友们分享下，谁有更好的也别吝啬，在这里给大家share下啊！

单行函数：

函数一般在数据上执行，它给数据的转换和处理提供了方便。

不同的DBMS提供的函数不同。

函数可能会带来系统的不可移植性（可移植性:

所编写的代码可以在多个系统上运行）。

加入注释是一个使用函数的好习惯。

大多数SQL实现支持以下类型的函数：

文本处理，算术运算，日期和时间，数值处理。

Null：

空值

空值当成无穷大处理，所有空值参与的运算皆为空。

空值与空值并不相等，因为空值不能直接运算。

如：

prod_price=""这种写法是错的（不要受到corejava的影响）

prod_price=NULL这种写法是错的（不要受到corejava的影响）

prod_priceISNULL这种写法才是对的

DISTINCT不能用于COUNT（*）。

如：

SelectDistinctnameFroms_dept;SelectDistinctdept_id,titleFroms_emp;

文本处理：

TRIM（）/LTRIM（）/RTIRM（）：

去空格。

只能去掉头和尾的空格，中间的不理。

trim（'heoArefdou'）-->heoArefdou

输入：

selecttrim（'heoArefdou'）fromdual;-->：

heoArefdou

LOWER：

转小写

lower（'SQLCourse'）-->sqlcourse

UPPER：

转大写

upper（'SQLCourse'）--－>SQLCOURSE

INITCAP：

首字母转大写，其余转小写

initcap（SQLCourse'）'-->SqlCourse

CONCAT：

合成。

双竖线只能在select语句里面用，这个可用于任何语句。

Concat（'Good','String'）-->GoodString

SUBSTR：

截取。

Substr（'String',1,3）-->Str

第一个数字“1”，表示从第几个开始截取；若要从倒数第几个开始，用负数，如“－2”表示倒数第2个。

上式中第2个数字“3”表示截取多少个。

LENGTH：

统计长度。

Length（'String'）-->6

NVL：

转换空值

日期和时间处理：

Oracle日期格式：

DD-MMM-YYYY（D代表日期date，M代表月month，Y代表年year）

如：

SELECTprod_name（DAY表示完整的星期几，DY显示星期的前三个字母）

FROMProducts

WHEREprod_timeBETWEEN

to_date（’01-JAN-2008’）

ANDto_date（’31-DEC-2008’）;

日期可以进行加减，默认单位是1天。

日期与日期可以相减，得出天数；日期与日期但不能相加。

sysdate－>系统的当天

Months_Between（'01-Sep-95','11-Jan-94'）-->19.774194相差多少个月,Between里面也可以填函数。

Add_months（'11-Jan-94',6）-->11-Jul-94增加多少个月

Next_day（'01-Sep-95','Friday'）-->'08-Sep-95'下一个星期五。

其中的'Friday'可用6替代，因为星期日＝1

Last_day（'01-Sep-95'）-->'30-Sep-95'这个月的最后一天

数值处理：

可以运用于代数，三角，几何

ROUND：

四舍五入

Round（45.925,2）－>45.93Round（45.925,0）－>46Round（45.925,-1）－>50

逗号前一个数是要处理的数据源，后一个参数表示保留多少位小数。

后一参数是负数时，表示舍去小数点前的几位，例3是舍去个位及其后的。

不写后一参数时，默认不保留小数。

TRUNC：

舍去末位。

直接舍去，不会进位。

Trung（45.925,2）－>45.92Trung（45.925,2）－>45.92Trung（45.925,2）－>45.92

日期的舍取：

常用的数值处理函数有：

ABS（）绝对值ABS（-5741.5854）-->5741.5854

PI（）圆周率注意：

oracle中不支持PI（）函数；MYSql支持PI（）函数。

SIN（）正统值Oracle还支持COS（）、ASIN（）、ACOS（）函数

SQRT（）平方根

转化：

TO_CHAR（number,'fmt'）：

把数值转换成字符串

显示数字的命令

9：

正常显示数字；

0：

显示包括0的数值形式，空位强制补0；

$：

以美元符号显示货币；

L：

按当前环境显示相关的货币符号；

.和，：

在固定位置出现“.”点和“，”逗号；不够位时，四舍五入。

例题：

SQL>select'Order'||To_char（id）||

2'wasfilledforatotalof'

3||To_char（total,'fm$9,999,999'）

4froms_ord

5whereship_date='21-SEP-92';

TO_NUMBER（char）：

把字符转换成数字

九、链接

内链接：

严格匹配两表的记录。

外链接分左链接和右链接:

会使用一方表中的所有记录去和另一格表中的记录按条件匹配，空值也会匹配，这个表中的所有记录都会显示，

数据库会模拟出记录去和那些不匹配的记录匹配。

左链接加号在右面

如：

有TABLE1TABLE2

1的一条记录在2里面没有匹配上，那么1里面的记录保留

2的一条记录在1里面没有匹配上，那么2丢弃

右链接正好相反

--例题：

哪些人是领导。

selectdistinctb.id,b.last_namemanager

froms_empa,s_empb

wherea.manager_id=b.id（+）;

左右顺序有区别，这是另外新建一个表，要显示的是第二个表格的内容。

+放在没有匹配行的表一侧，令表格能完整显示出来。

标准写法：

内连接用INNER，左连接用LEFT，右连接用RIGHT。

selectdistinctb.id,b.last_namemanager

froms_empaLEFTjoins_empb

ONa.manager_id=b.id;

十、组函数：

分组允许将数据分为多个逻辑组，以便能对每个组进行聚集计算。

Group：

分组

Groupby：

分组。

（默认按升序对所分的组排序；想要降序要用orderby）可以包括任意数目的列。

如果嵌入了分组，数据将在最后规定的分组上进行汇总。

GROUPBY子句中列出的每个列都必须是检索列或有效的表达式，但不能是聚集函数。

*如果在SELECT中使用表达式，则必须在GROUPBY子句中指定相同的表达式，不能使用别名。

除聚合计算语句外，SELECT语句中的每个列都必须在GROUPBY子句中给出。

如果分组列中具有NULL值，则NULL将作为一个分组返回。

如果列中有多行NULL，它们将分为一组。

Having：

过滤。

分组之后，不能再用where，要用having选择过滤。

Having不能单独存在，必须跟在groupby后面。

WHERE在数据分组前进行过滤，HAVING在数据分组后过滤。

可以在SQL中同时使用WHERE和HAVING，先执行WHERE，再执行HAVING。

聚合函数：

AVG：

平均值（忽略值为NULL的行，但不能用AVG（*））

COUNT：

计数（Count（列）不计算空值；但COUNT（*）表示统计表中所有行数，包含空值）

MAX：

最大值（忽略列值为NULL的行。

但有些DBMS还允许返回文本列中的最大值，

在作用于文本数据时，如果数据按照相应的列排序，则MAX（）返回最后一行。

）

MIN：

最小值（忽略值为NULL的行。

不能用MIN（*）。

一般是找出数值或者日期值的最小值。

但有些DBMS还允许返回文本列中的最小值，这时返回文本最前一行）

SUM：

求和（忽略值为NULL的值。

SUM不能作用于字符串类型，而MAX（），MIN（）函数能。

也不能SUM（*））

子查询：

查询语句的嵌套

可以用于任意select语句里面，但子查询不能出现orderby。

子查询总是从内向外处理。

作为子查询的SELECT语句只能查询单个列，企图检索多个列，将会错误。

如：

找出工资最低的人selectmin（last_name）,min（salary）froms_emp;

或者用子查询selectlast_name,salaryfroms_empwheresalary=（selectmin（salary）froms_emp）;

E-R图：

属性:

E（Entity）-R（Relationship）

*（Mandatorymarked强制的）强制的非空属性

o（Optionalmarked可选的）可选属性（可以有值也可以没有）

#*（Primarymarked）表示此属性唯一且非空

约束：

针对表中的字段进行定义的。

PK：

primarykey（主键约束，PK=UK+NN）保证实体的完整性，保证记录的唯一

主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键，

只有两个字段放在一起唯一标识记录，叫做联合主键（CompositePrimaryKey）。

FK：

foreignkey（外建约束）保证引用的完整性,外键约束,外键的取值是受另外一张表中的主键或唯一值的约束,不能够取其他值,

只能够引用主键会唯一键的值，被引用的表，叫做parenttable（父表），引用方的表叫做childtable（子表）;

childtable（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，

删除记录，要先删除子表记录，后删除父表记录，

要修改记录，如果要修改父表的记录要保证没有被子表引用。

要删表时，要先删子表，后删除父表。

U：

uniquekey（唯一键UK），值为唯一，不能重复。

在有唯一性约束的列，可以有多个空值，因为空值不相等。

NN：

NOTNULL，不能为空。

index（索引）是数据库特有的一类对象，实际应用中一定要考虑索引，view（示图）

数量关系:

一对一关系

多对一关系

一对多关系

多对多关系

范式：

好处：

降低数据冗余；减少完整性问题；标识实体，关系和表

第一范式（Firstnormalform：

1Nf），每一个属性说一件事情。

所有的属性都必须是单值，也就是属性只表示单一的意义。

（记录可以重复，会有大量冗余，没有任何限制）

第二范式（2N范式），最少有一个属性要求唯一且非空PK，其他跟他有关联（记录不可重复，但是数据可能会出现冗余）。

第三范式（3N范式），非主属性只能依赖于主属性,不能依赖于其他非主属性。

（解决数据冗余问题,不能存在推理能得出的数据）

一般情况会做到第三范式。

创建表：

CreateTable表名

（字段名1类型（数据长度）（default...）约束条件,

字段名2类型（数据长度）约束条件）;

建表的名称：

必须字母开头；最多30字符；只能使用“A～Z、a～z、0～9、_、$、#”；

同一目录下不能有同名的表；表名不能跟关键字、特殊含意字符同样。

如：

createtablenumber_1（n1number（2,4）,n2number（3,-1）,n3number）;

createtablet_sd0808（idnumber（12）primarykey,namevarchar（30）notnull）;

MySQL的：

createtablestudent（oidintprimarykey,ACTNOvarchar（20）notnullunique,

BALANCEdouble）;--MySQL的number类型分小类了，Oracle只有number，且MySQL的数值型不用定大小

Oracle的：

createtablet_ad（oidnumber（15）primarykey,

ACTNOvarchar（20）notnullunique,BALANCEnumber（20））;

INSERT：

插入（或添加）行到数据库表中的关键字。

插入方式有以下几种：

插入完整的行；插入行的一部分；插入某些查询的结果。

对于INSERT操作，可能需要客户机/服务器的DBMS中的特定的安全权限。

插入行（方式一）INSERTINTOproductsVALUES（2008,’TV’,222.22,’US’）;

依赖于表中定义的顺序，不提倡使用。

有空值时需要自己补上。

插入行（方式二）INSERTINTOproducts（id,name,price,vend_name）VALUES（2008,’TV’,222.22,’US’）;

依赖于逻辑顺序，会自动补上空值，提倡使用。

插入检索出的数据：

可以插入多条行到数据库表中

INSERTINTOproducts（*,*,*,*）

SELECT*,*,*,*

FROMproducts_copy;

如果这个表为空，则没有行被插入，不会产生错误，因为操作是合法的。

可以使用WHERE加以行过滤。

复制表：

将一个表的内容复制到一个全新的表（在运行中创建，开始可以不存在）

CREATETABLE新表名AS

SELECT*

FROM表名;

INSERTINTO与CREATETABLEASSELECT不同，前者是导入数据，而后者是导入表。

任何SELECT选项和子句都可以使用，包括WHERE和GROUPBY。

可利用联接从多个表插入数据。

不管从多少个表中检索数据，数据都只能插入到单个表中。

更新数据UPDATE语句

需要提供以下信息：

要更新的表；列名和新值；确定要更新的哪些行的过滤条件。

UPDATE表名

SETvend_name=‘HP’,

prod_name=‘NEWCOMPUTER’

WHEREvend_name=‘IBM’;

--UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。

也可以将一个列值更新为NULL。

删除数据DELETE语句

DELETE

FROMproducts

WHEREprod_name=‘COMPUTER’;

全行删除，不要省略WHERE，注意安全。

DELETE不需要列名或通配符。

删除整行而不是删除列。

DELETE是删除表的内容而不是删除表。

如果想从表中删除所有内容，可以使用TRUNCATETABLE语句（清空表格），它更快。

数字字典表：

Sequence:

排列。

存储物理地址

Index:

索引。

依附于表，为提高检索速度。

View:

视图。

看到表的一部分数据。

限制数据访问。

简化查询。

数据独立性。

本质上是一个sql查询语句。

Create[orRelace][Force|noForce]View视图名

[（alias[,alias]…）]别名列表

Assubquery

[WithCheckOption[Constraint……]]

[WithReadOnly]

注意：

有些DBMS不允许分组或排序视图，不能有Orderby语句。

可以有Select语句。

删除视图：

DROPVIEW视图名

Rownum:

纬列。

内存里排序的前N个。

在where语句中，可以用＝1，和<=N或N。

因为这是内存读取，没有1就丢弃再新建1。

只能从1开始。

需要从中间开始时，需二重子rownum语句需取别名。

经典应用：

Top-nAnalysis（求前N名或最后N名）

Select[查询列表],Rownum

From（Select[查询列表（要对应）]

From表

OrderbyTop-N_字段）

WhereRownum<=N

分页显示：

--取工资第5～10名的员工（二重子rownum语句，取别名）

selectrn,id,last_name,salary

From（selectid,last_name,salary,Rownumrn

From（Selectid,last_name,salary

froms_emp

orderbysalarydesc）

whererownum<=10）

wherernbetween5and10;

Union：

合并表

Select…UnionSelect…把两个Select语句的表合并。

要求两表的字段数目和类型按顺序对应。

合并后的表，自动过滤重复的行。

Intersect：

交。

同上例，把两个Select表相交。

Minus：

减。

把相交的内容减去。

notexists除运算。

添加字段（列）：

AlterTable表名

Add（columndataype[Defaultexpr][NotNull]

[,columndatatype]…）;

添加有非空限制的字段时，要加Default语句

字段名字不可以直接改名，需要添加新字段，再复制旧字段后删除旧字段。

添加约束：

AlterTable表名

Add[CONSTRAINTconstraint]type（column）;

添加非空约束时，要用Modify语句。

查看约束名时，可以违反约束再看出错提示；或者查看约束字典descuser_constraints

减少字段：

AlterTable表名

Drop（column[,column]…）;

删除约束：

AlterTable表名

DropCONSTRAINTcolumn;

或：

AlterTable表名

DropPrimaryKeyCascade;

暂时关闭约束，并非删除：

AlterTable表名

DisableCONSTRAINTcolumnCascade;

打开刚才关闭的约束：

AlterTable表名

EnableCONSTRAINTcolumn;

修改字段：

AlterTable表名

Modify（columndataype[Defaultexpr][NotNull]

[,columndatatype]…）;

修改字段的类型、大小、约束、非空限制、空值转换。

删除表：

会删除表的所有数据，所有索引也会删除，约束条件也删除，不可以rollback恢复。

DropTable表名[CascadeConstraints];

加[CascadeConstraints]把子表的约束条件也删除；但只加[Cascade]会把子表也删除。

改表名：

Rename原表名To新表名;

清空表格：

TRUNCATETABLE表名;

相比Delete,TruncateTable清空很快，但不可恢复。

清空后释放内存。

Delete删除后可以rollback。

清空后不释放内存。