sql笔记.docx
- 文档编号:29753728
- 上传时间:2023-07-26
- 格式:DOCX
- 页数:14
- 大小:60.31KB
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或
因为这是内存读取,没有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。
清空后不释放内存。
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- sql 笔记