【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“SQL学习笔记”一文，供大家参考学习
?本笔记以orcal数据库为例,其它数据库略有不同.
一、写子句顺序
Select[ALL|DISTINCT]column_name[,column_name]...
From{table_name|view_name}
[,{table_name|view_name}]...
[Wheresearch_conditions]
[GroupBycolumn_name[,column_name]
[Havingsearch_condition]]
[OrderBy{column_name|select_list_number}[ASC|DESC]
[,{column_name|select_list_number}[ASC|DESC]]...];--最后
二、常用SQL:
1.建表前检查语句:
MySQL的:droptableifexists表名DEFAULTCHARACTERSETutf8;
SQLServer的:IFEXISTS(SELECT1FROMsysobjectsWHEREname='表名'ANDtype='U')DROPTABLE表名;
CREATETABLE表名(IDINTPRIMARYKEY,UserIDCHAR(15),ContactPersonIDINT);
Oracle的:createorreplacetable表名...;--直接写建表语句
2.建表语句:createtable表名(memidint,pointsnumeric(10,1)default0,
primarykey(`memid`,`courseid`),
FOREIGNKEY(`memid`)REFERENCES表名2(`memid`)ondeletecascadeonupdatecascade,
CHECK(points>=0andpoints<=100));
3.复制表:CREATETABLE新表名ASSELECT*FROM旧表名;--仅复制数据,没复制表结构(自增主键等不会复制)
CREATETABLE新表名like旧表名;--使用旧表创建新表,复制表结构(数据不会复制)
4.插入语句:INSERTINTO表名(id,name,price,vend_name)VALUES(11,'TV',222,'US'),(22,'ss',12.22,'kk');
INSERTINTO表名(id,name,price,vend_name)SELECTid,name,price,vendFROM表名2;
5.更新语句:UPDATE表名SETcolumn_name=expression,prod_name='NEWCOMPUTER'[WHERE];
UPDATE表1,表2SET表2.column_name=expression,表1.prod_name='NEWCOMPUTER'[WHERE];
6.删除语句:DELETEFROM表名WHEREsearch_conditions;
7.清空表格:TRUNCATETABLE表名;
8.修改表结构
修改字段:ALTERTABLE表名Modifycol_namevarchar(100);
添加字段:ALTERTABLE表名Addcol_namevarchar(100)defaultNULLCOMMENT'匯款帳號或者匯款人'aftercol_name0;
减少字段:AlterTable表名Drop(column[,column]…);
添加约束:AlterTABLE表名AddFOREIGNKEY(column1)REFERENCES表名2(column2);--添加非空约束时,要用Modify语句
删除约束:ALTERTABLE表名DropFOREIGNKEY表名_ibfk_1;
AlterTable表名DropCONSTRAINTcolumn;
添加主键:Altertable表名addprimarykey(col);
删除主键:Altertable表名dropprimarykey(col);
唯一约束:ALTER[IGNORE]TABLE表名ADDUNIQUEINDEX(column[,column]…);--IGNORE:删除重复;没这个则重复时报错
9.创建索引:create[unique]index索引名on表名(column[,column]…);
删除索引:dropindex索引名;
10.创建视图:createview视图名asselectstatement;
删除视图:dropview视图名;
三、注
意事项:
大小写不敏感，即不区分大小写。提倡关键字大写，便于阅读和调试。
SQL语句是由简单的英语单词构成；这些英语单词称为关键字/保留字，不做它用。SQL由多个关键字构成。
SQL语句由子句构成，有些子句是必须的，有些是可选的。
在处理SQL语句时，其中所有的空格都被忽略(空格只用来分开单词，连续多个空格当一个用)。
SQL语句可以在一行上写出，建议多行写出，便于阅读和调试。
多条SQL语句必须以分号分隔。多数DBMS不需要在单条SQL语句后加分号，但特定的DBMS可能必须在单条SQL语句后加分号。
SQL语句的最后一句以“；”号结束。不同的数据库会有不同的结束符号。(go也会作结束符)
{}大括号包起来的单字或词组，表示至少从中选一个。
[]中括号包起来的部分，表示可选可不选的。
()小括号，表示一定要输入的。与大括号、中括号不同。
｜表示最多只能从选项中选取一个。
，表示可按需选择多个选项，并且这些选项之间必须以逗号隔开。
...表示可以重复地使用同样的语法部分。
!在SQL环境下执行Unix命令。
四、兼顾各数据库的SQL语句
1.自增列:
Oracle:建立Sequence
MySQL:createtabletest_t(idintprimarykeyAUTO_INCREMENT,namevarchar(80));--AUTO_INCREMENT是自增关键字
SQLServer:createtabletest_t(idintprimarykeyidentity(1,1),namevarchar(80));--identity(1,1)是自增函数
access:createtabletest_t(idIntegerprimarykeyCounter(1,1),namevarchar(80));
通用的:使用表自身的自增列的最大值+1，如:
insertintotest_t(id,name)values((selectnvl(max(id),0)+1fromtest_t),'holer');--这里id是表的自增列
2.伪列(序号):
SELECT(SELECTCount(表名.aa)ASAutoNumFROMxlhWHERE(表名.aa<=表名_tem.aa))AS序号,表名.aa
FROM表名AS表名_temINNERJOIN表名ON表名_tem.aa=表名.aaORDERBY表名.aa;
Rownum:纬列。内存里排序的前N个。
在where语句中，可以用＝1，和<=N或
N。
因为这是内存读取，没有1就丢弃再新建1。只能从1开始。需要从中间开始时，需二重子rownum语句需取别名。
经典应用:Top-nAnalysis(求前N名或最后N名)
Select[查询列表],Rownum
From(Select[查询列表(要对应)]
From表
OrderbyTop-N_字段)
WhereRownum<=N--不写这行则全部显示并排名。
SQLserver的用法:
SelecttopN查询列表
From表
OrderbyTop-N_字段
分页显示:
--取工资第5～10名的员工(二重子rownum语句，取别名)
select*From(
selectid,last_name,salary,Rownumrn
From(Selectid,last_name,salary
froms_emp
orderbysalarydesc)
whererownum<=10)
wherernbetween5and10;
三、常用简单语句:
clearscreen:清屏
edit:编辑刚才的一句。
desc/describe:(列出所有列名称)
用法:DESCRIBE[schema.]object[@db_link]
dual:亚表(虚表)，临时用。如:descdual；／fromdual；
rollback:回溯，回溯到上次操作前的状态，把这次事务操作作废，只有一次(DDL和DCL语句会自动提交，不能回溯)。
可以用commit语句提交，这样就回溯不回了。
setpauseon\off:设置分屏(设置不分屏)
setpause"pleaseputanenterkey"且setpauseon:设置带有提示的分屏
oerrora904:查看错误
setheadoff:去掉表头
setfeedoff:去掉表尾
保存在oracle数据库中的所有操作细节:
spooloracleday01.txt:开始记录
spooloff:开始保存细节
SQLserver的变量:
申明变量:declare@iint设变量值:set@i=0
DECLARE@sqlNVARCHAR(4000)SET@sql='SELECTMEMID,NAME'
SET@sql=@sql+',ISNULL(STR(AVG(S.POINTS)),''0'')AS''平均成绩'''
SET@sql=@sql+'FROMTB_MEMBER'
PRINT@sql
EXEC(@sql)
update表名set字段名=@i,@i=@i+1--递增效果
另一递增效果:identity(1,1)--前参数是从多少开始，后参数是增量
四、SELECT语句:选择操作、投影操作。
select:从一个或多个表中检索一个或多个数据列。包含信息:想选择什么表，从什么地方选择。必须要有From子句。(最常用)
当从多张表里查询的时候，会产生笛卡尔积；可用条件过滤它。
当两个表有相同字段时必须加前缀，列名前需加表名和“.”，如“s_emp.id”。
1、用法:SELECTcolumns,prod2,prod3<列>FROMTable1,table2<表名>分号结束
如:selectidfroms_emp;
selectlast_name,namefroms_emp,s_deptwheres_emp.dept_id=s_dept.id;--列表每人所在部门
SELECT*FROMProducts;--检索所有列。
数据太多时，最好别使用上句，会使DBMS降低检索和应用程序的性能。(*通配符)
2、对数据类型的列(字段)可进行运算(如加减乘除)。
3、对列起别名:有直接起别名，加AS起别名，用双引号起别名等三种方法
(单引号，引起字符串；双引号，引起别名。起别名有符号，或者区分大小写时，必须用双引号)
多表查询时，可给表起别名。(给列起别名，列<空格>列别名；给表起别名，表<空格>表别名；)。
如:Selectfirst_nameEMPLOYEES,12*(salary+100)ASMONEY,manager_id"ID1"Froms_empE;
4、字段的拼接,可
用双竖线(双竖线只能用于select语句里)。不同的DBMS可能使用不同的操作符;拼接的字段同样可以起别名。
如:Selectfirst_name||''||last_name||','||title"Employees"Froms_emp;
排他锁:Selectid,salaryFroms_empwhereid=1ForUpdate;
可以阻止他人并发的修改，直到你解锁。
如果已有锁则自动退出:Selectid,salaryFroms_empwhereid=1ForUpdateNoWait;
FORUPDATE:可以再加OF精确到某格。如:...ForUpdateOFsalary...
注意要解锁。
五、ORDERBY子句，排序
Orderby:按某排序列表(默认升序asc,由低到高;可加desc,改成降序由高到低)
检索返回数据的顺序没有特殊意义，为了明确地排序用SELECT语句检索出的数据，可使用ORDERBY子句。
ORDERBY子句取一个或多个列的名字。
对空值，按无穷大处理(升序中，空值排最后；降序中排最前)。
1、用法:Selectprod_id,prod_price,prod_nameFromProductsOrderByprod_price,prod_name;
(从左到右执行排序，先排price)
ORDERBY子句中使用的列将是为显示所选择的列，但是实际上并不一定要这样，用非检索的列排序数据是完全合法的。
为了按多个列排序，列名之间用逗号分开。
2、支持按相对列位置进行排序。位置从1开始。
输入SELECTprod_id,prod_price,prod_name
FROMProducts
ORDERBY2，3--(2指price，3指name)
3、升序、降序。默认是升序(asc，从小到大排序)，想降序时用desc。
如:SELECTprod_id,prod_price,prod_nameFROMProductsORDERBYprod_priceDESC;
注意:DESC关键字只应用到直接位于其前面的列名。如果想在多个列上进行排序，必须对每个列指定DESC关键字。
升序是默认的，可不写，但降序必须写。
六、WHERE子句，选择、过滤
其后只能跟逻辑语句，返回值只有ture或false
如:selectlast_name,salaryfroms_empwheresalary＝1000;--找出工资1000的人
WHERE子句操作符:
1、逻辑比较运算符
＝等于
！=不等于，还有(<>^=这两个同样表示不等于)
>大于
>=大于等于
<小于
<=小于等于
2、SQL比较运算符
between...and...:在两者之间。(BETWEEN小值AND大值)
NOTbetween...and...:指定不包含的范围。
如:selectlast_name,salaryfroms_empwheresalarybetween1000and1500；
--工资1000到1500的人，包括1000和1500。
in(列表):在列表里面的。in的括号里可包含次查询，即select子句
如:selectlast_name,dept_idfroms_empwheredept_idin(41,42);第41、42部门的人
like:包含某内容的。模糊查询
可以利用
通配符创建比较特定数据的搜索模式，通配符只能用于文本，非文本数据类型不能使用通配符。
通配符在搜索模式中任意位置使用，并且可以使用多个通配符。
通配符%表示任何字符出现任意次数；还能代表搜索模式中给定位置的0个或多个字符。下划线匹配单个任意字符。
如:selecttable_namefromuser_tableswheretable_namelike'S\_%'escape'\';
'找出“S_“开头的,由于下划线有任意字符的含义，故需另外定义转移符。
但习惯用“\”，为方便其它程序员阅读和检测，一般不改用其它的。
like'M%':M开头的like'_a%':第二个字符是a的like'%a%'所有含a的
(“_”表示一个任意字符；“%”表示任意多个任意字符。)
如果将值与串类型的进行比较，则需要限定引号；用来与数值列进行比较时，不用引号。
isnull:是空。(NULL表示不包含值。与空格、0是不同的。)
如:SELECTprod_name,prod_priceFROMProductsWHEREprod_priceISNULL;
七、高级检索(逻辑运算符):
通常我们需要根据多个条件检索数据。可以使用AND或OR、NOT等连接相关的条件
计算次序可以通过圆括号()来明确地分组。不要过分依赖默认计算次序，使用圆括号()没有坏处，它能消除二义性。
and:条件与
如SELECTprod_id,prod_price,prod_nameFROMProductsWHEREprod_price<4ANDvend_id=‘DELL’
or:条件或(注:and的优先级比or更高，改变优先级可用括号)
如SELECTprod_id,prod_price,prod_nameFROMProductsWHEREprod_price<4ORvend_id=‘DELL’
not:条件非。否定它之后所跟的任何条件
否定的SQL比较运算符:NOTBETWEEN；NOTIN；NOTLIKE；ISNOTNULL:
(注意，按英语习惯用isnot，而不是notis)
NOT与IN在一起使用时，NOT是找出与条件列表不匹配的行。
IN列表里有NULL时不处理，不影响结果；用NOTIN时，有NULL则出错，必须排除空值再运算。
in:选择列表的条件
使用IN操作符的优点:在长的选项清单时,语法直观；计算的次序容易管理；
比OR操作符清单执行更快；最大优点是可以包含其它SELECT语句，使用能够动态地建立WHERE子句。
如SELECTprod_id,prod_price,prod_nameFROMProductsWHEREvend_idIN(‘DELL’,’RBER’,’TTSR’);
SELECTau_nameFROMauthorsWHEREau_idNOTIN(SELECTau_idFROMtitleauthorsWHEREroyaltyshare<.50);#找出版税不小于50%的作者.
八、单行函数:
函数一般在数据上执行，它给数据的转换和处理提供了方便。不同的DBMS提供的函数不同。
函数可能会带来系统的不可移植性(可移植性:所编写的代码
可以在多个系统上运行)。
加入注释是一个使用函数的好习惯。
大多数SQL实现支持以下类型的函数:文本处理，算术运算，日期和时间，数值处理。
Null:空值
空值当成无穷大处理，所有空值参与的运算皆为空。
空值与空值并不相等，因为空值不能直接运算。
如:prod_price=""这种写法是错的(不要受到corejava的影响)
prod_price=NULL这种写法是错的(不要受到corejava的影响)
prod_priceISNULL这种写法才是对的
NVL:处理空值，把空值转化为指定值。可转化为日期、字符、数值等三种(注意:转化时，两参数必须要同类型)
在SQLserver里用"ISNULL(需转的数据,转成什么)"函数代替。
遇到数值要把空转换成字符串的，需先把数值转成字符串类型。
如:NVL(date,'01-JAN-95')NVL(title,'NOTitleYet')NVL(salary,0)
错误写法:
Selectlast_name,title,salary*commission_pct/100COMMFroms_emp;--没提成的人没法显示工资
正确写法:
Selectlast_name,title,salary*NVL(commission_pct,0)/100COMMFroms_emp;--把提成是空值的转化为0
注意:在oracle中的NVL，在SQLserver中用ISNULL。格式相同。
DISTINCT:过滤重复
把重复的行过滤掉；多个字段组合时，只排除组合重复的。
DISTINCT必须使用列名前，不能使用计算或者表达式。
所有的聚合函数都可以使用。如果指定列名，则DISTINCT只能用于COUNT(列名)，DISTINCT不能用于COUNT(*)。
如:SelectDistinctnameFroms_dept;
SelectDistinctdept_id,titleFroms_emp;
注意:Distinct配合字段使用时，无法在字段列表里指定非唯一的单元或运算值。配合聚合函数使用时，需把distinct放到聚合函数的括号中，而仅对此函数有效。
文本处理:
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.mana
ger_id=b.id(+);
左右顺序有区别，这是另外新建一个表，要显示的是第二个表格的内容。
+放在没有匹配行的表一侧，令表格能完整显示出来。
标准写法:内连接用INNER，左连接用LEFT，右连接用RIGHT。
selectdistinctb.id,b.last_namemanager
froms_empaLEFTjoins_empb
ONa.manager_id=b.id;
JOIN:连结数据库
以一个select命令取得及运用多个数据表里的数据。
语法:SELECTselect_listFROMtable_1,table_2[,table_3]...
WHERE[table_1.]columnjoin_operator[table_2.]column
如:找出身兼编辑和作者的人SELECTed_nameFROMeditors,authorsWHEREed_id=au_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(*))
当AVG(*)与SUM(*)/COUNT(*)不相等时，是NULL在作怪。
子查询:查询语句的嵌套
可以用于任意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,不能为空。
DEFAULT:制定默认值，当使用者没输入时自动补上。
如:createtabletb_score(memidint,courseidint,pointsnumeric(10,1)default0);
CHECK:指定特定字段里能输入的数据。限定数据范围的方法之一。
如:createtabletb_test(memidint,sexvarchar(5),pointsnumeric(10,1),
CHECK(points>=0andpoints<=100andsexin('man','women')));
REFERENCES:该字段的数据值必须存在于被参考的主键表格里，否则拒绝输入。
数量关系:一对一关系
多对一关系
一对多关系
多对多关系
创建用户组表:
createtablet_group(idintnotnull,namevarchar(30),primarykey(id));#只为下面举例用。
外键约束方式:
1.级联(cascade)方式
createtablet_user(useridintnotnull,namevarchar(30),groupidint,
primarykey(userid),/*定义这表的主键*/
foreignkey(groupid)referencest_group(id)ondeletecascadeonupdatecascade
/*上句:定义外键，groupid是t_group表的id字段的外键。而且这边groupid的值对
应那边的id。
当t_group表的id字段被删除或修改，这里相应的groupid跟着被删除或修改。级联删除、级联修改。
插入时，如果是t_group表的id字段没有的值，无法插入。参照完整性约束不符。
比如insertintot_uservalues(3,'dai',3);如果t_group表的id没有一个是3的，则插入不成功。*/
);
--建完表后的修改写法(效果同上):
ALTERTABLEt_useraddFOREIGNKEY(userid)REFERENCESt_group(id)ondeletecascadeonupdatecascade;
--刪除外键(key后面的名称得看具体情况)
ALTERTABLEt_userdropFOREIGNKEYt_user_ibfk_1;
2.置空(setnull)方式
createtablet_user(useridintnotnullprimarykey,namevarchar(30),groupidint,
foreignkey(groupid)referencest_group(id)ondeletesetnullonupdatesetnull
/*插入时，同上。如果是t_group表的id字段没有的值，无法插入。参照完整性约束不符。
当t_group表的id字段被删除或修改，这里相应的groupid被设为null。*/
);
3.禁止(noaction/restrict)方式
createtablet_user(idintnotnullprimarykey,namevarchar(30),groupidint,
foreignkey(groupid)referencest_group(id)ondeletenoactiononupdatenoaction
/*插入时，还是同上。如果参照完整性约束不符则无法插入。
当t_group表的id字段被删除或修改，参照这里相应的groupid，如果这里有引用，则主表不能删除或修改*/
);
外键的定义语法:
[CONSTRAINTsymbol]FOREIGNKEY[id](index_col_name,...)
REFERENCEStbl_name(index_col_name,...)
[ONDELETE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]
[ONUPDATE{RESTRICT|CASCADE|SETNULL|NOACTION|SETDEFAULT}]
该语法可以在CREATETABLE和ALTERTABLE时使用，如果不指定CONSTRAINTsymbol，MYSQL会自动生成一个名字。
ONDELETE、ONUPDATE表示事件触发限制，可设参数:
RESTRICT(限制外表中的外键改动)
CASCADE(跟随外键改动)
SETNULL(设空值)
SETDEFAULT(设默认值)
NOACTION(无动作,默认的,作用同RESTRICT)
范式:
好处:降低数据冗余；减少完整性问题；标识实体，关系和表
第一范式(Firstnormalform:1Nf)，无重复的列
每一个属性表示一件事情。所有的属性都只表示单一的意义，即实体中的某个属性不能有多个值或者不能有重复的属性。
如果出现重复的属性，就可能需要定义一个新的实体，新的实体由重复的属性构成，新实体与原实体之间为一对多关系。
在第一范式(1NF)中表的每一行只包含一个实例的信息。
说明:第一范式(1NF)是对关
系模式的基本要求，不满足第一范式(1NF)的数据库就不是关系数据库。
第二范式(2N范式)，属性完全依赖于主键
最少有一个属性要求唯一且非空PK，其它跟他有关联。即要求数据库表中的每个实例或行必须可以被惟一地区分。
为实现区分通常需要为表加上一个列，以存储各个实例的惟一标识。
第三范式(3N范式)，非主属性只能依赖于主属性,不能依赖于其它非主属性。
任何字段不能由其他字段派生出来，它要求字段没有冗余。(解决数据冗余问题,不能存在推理能得出的数据)
第四范式(4N范式)，在一个多对多的关系中，独立的实体不能存放在同一个表格中
表不能包含一个实体的两个或多个相互独立的多值因子
第五范式(5N范式)，表必须可以分解为更小的表,除非那些表在逻辑上拥有与原始表相同的主键
说明:第二范式(2NF)是在第一范式(1NF)的基础上建立起来的，即满足第二范式(2NF)必须先满足第一范式(1NF)。
满足第三范式(3NF)必须先满足第二范式(2NF)。其它范式以此类推，即必须先满足前面的所有范式。
一般情况会做到第三范式。
创建表: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));
建立数据库:CREATEDATABASEdatabase_name;
定义特定使用者:CREATESCHEMA;
INSERT:插入(或添加)行到数据库表中的关键字。
插入方式有以下几种:插入完整的行；插入行的一部分；插入某些查询的结果。
对于INSERT操作，可能需要客户机/服务器的DBMS中的特定的安全权限。
插入行(方式一)INSERTINTOtableNameVALUES(2008,’TV’,222.22,’US’);
依赖于表中定义的顺序，不提倡使用。有空值时需要自己补上。
插入行(方式二)INSERTINTOtableName(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表名
SETcolumn_name=expression,
prod_name=‘NEWCOMPUTER’
[WHEREsearch_conditions];
--UPDATE语句中可以使用子查询，使得能用SELECT语句检索出的数据更新列数据。也可以将一个列值更新为NULL。
如果没有用where，则指定列的所有单元格都会设成指定值。
删除数据DELETE语句
DELETEFROMtable_name
WHEREsearch_conditions;
全行删除，不要省略WHERE，注意安全。
DELETE不需要列名或通配符。删除整行而不是删除列。DELETE是删除表的内容而不是删除表。
如果想从表中删除所有内容，可以使用TRUNCATETABLE语句(清空表格)，它更快。
数字字典表:
Sequence:排列。存储物理地址
Index:索引。依附于表，为提高检索速度。
index(索引)是数据库特有的一类对象，实际应用中一定要考虑索引，view(示图)
复合索引compositeindexes;唯一性索引uniqueindexes;群集索引clusteredindex;
CREATE[UNIQUE]INDEXindex_nameONtable_name(column_name);
DROPINDEXindex_name;
View:视图。看到表的一部分数据。
限制数据访问。简化查询。数据独立性。本质上是一个sql查询语句。
Create[orRelace][Force|noForce]View视图名
[(alias[,alias]…)]别名列表
Assubquery
[WithCheckOption[Constraint……]]
[WithReadOnly]
注意:有些DBMS不允许分组或排序视图，不能有Orderby语句。可以有Select语句。
删除视图:DROPVIEW视图名
如:createviewHoler_view1AS
selectmemid,name,classname,points
fromtb_member
wheretype='student';
select*fromHoler_view1;
dropviewHoler_view1;
Union:合并表
Select…UnionSelect…把两个Select语句的表合并。
要求两表的字段数目和类型按顺序对应。合并后的表，自动过滤重复的行。
Intersect:交
。同上例，把两个Select表相交。
Minus:减。把相交的内容减去。
notexists除运算。
EXISTS:对其后面的查询语句进行“存在性检查”。有传回至少一倏记录，则为“真”。
NOTEXISTS:上式的反相。
在WHERE子句里的EXISTS关键词会检查是否有数据符合次查询的限制条件。
格式:
StartofSELECT,INSERT,UPDATE,DELETEstatement;orsubquery
WHEREexpressioncomparision_operatorEXISTS(subquery)
[EndofSELECT,INSERT,UPDATE,DELETEstatement;orsubquery]
修改表结构:AlterTable
添加字段(列):
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。清空后不释放内存。
事务(交易)Transaction
[begintransactionstatement]
SQLstatement
SQLstatement
rollbacktransactionstatement
SQLstatement
committransactionstatement
SQLserver创建临时表:
selectidentity(int,1,1)asorder_num,*intotemfromtb_member
select*fromtem--"*"需要对应上行的"*"，即需对应列
droptabletem
ORACLE设置环境变量:
ORACLE_SID=oral10g\--变局部变量
exportORACLE_SID--变全局变量
unsetORACLE_SID--卸载环境变量
ORACLE_HOME=...--安装路径；直接用一句语句也可以，如下
exportORACLE_HOME=/oracledata/.../bin:
修改MySQL的字符集:
安装目录下找到“my.ini”，设置“default-character-set=utf8”重启MySQL生效
可设成:gbk,gb2312,big5,utf8
通过MySQL命令行修改:
mysql>setcharacter_set_client=utf8;
mysql>setcharacter_set_connection=utf8;
mysql>setcharacter_set_database=utf8;
mysql>setcharacter_set_results=utf8;
mysql>setcharacter_set_server=utf8;
mysql>setcharacter_set_system=utf8;
mysql>setcollation_connection=utf8;
mysql>setcollation_database=utf8;
mysql>setcollation_server=utf8;
查看其字符集:showvariableslike'character%';
修改数据库的字符集
mysql>usemydb
mysql>alterdatabasemydbcharactersetutf8;
创建数据库指定数据库的字符集
mysql>createdatabasemydbcharactersetutf8;--ft_running_status
查看某表的字符集:showcreatetable表名;
修改某表的字符集:ALTERTABLE表名DEFAULTCHARSETutf8;
SQLServer创建用户
--建立登录帐号
sp_addloginuserName,password,userdatabase
sp_AddUser'useName'
--授权访问
sp_grantdbaccessuserName
--指定权限
grant{all|Insert|......}touserName
grantselect,insert,deleteontableNametouserNameOrGroupName--指定到某张表
SQLServer修改字符集
alterdatabasetest01collateChinese_PRC_CI_AI--改成简体中文(默认unicode编码)
SQLServer查看版本
1.运行：select@@Version;或者:print@@version;--8.00.2039表示安装了SP4，8.00.760表示安装了SP3
2.在添加或删除程序中查看SQLServer的支持信息，可直接查看到版本号
3.在安装目录下(默认是“C:\ProgramFiles\MicrosoftSQLServer”)的“MSSQL\Binn\sqlserver.exe”右键可查看
MySql用户创建、授权以及删除
mysql>CREATEUSER用户名IDENTIFIEDBY'密码';--填上想要的用户名密码即可
上面建立的用户可以在任何地方登陆。
如果要限制在固定地址登陆，比如localhost登陆:
mysql>CREATEUSER用户名@localhostIDENTIFIEDBY'密码';
--localhost可换上任意ip地址，“%”表示任意地址
若需要授权，用grant:
格式:grantselecton数据库.*to用户名@登录主机identifiedby"密码";
如:mysql>GRANTALLPRIVILEGESON*.*TO用户名@登录主机;
如:mysql>grantselect,insert,update,deleteon*.*toutest1@"%"Identifiedby"abc";
修改密码:
mysql>grantallprivilegeson数据库.*toutest1@localhostidentifiedby'mimi';
flush:
mysql>flushprivileges;
查看用户信息:
mysql>selecthost,userfrommysql.user;
注:创建用户时，如果提示“table'user'isreadonly”，则需要在控制台运行:
"安装目录下\bin\mys
qladmin"-u<用户名>-p<密码>flush-tables
查看oracle表结构:
1.在SQLPLUS中，直接用DESC[ribe]tablename即可。
可要是在外部应用程序调用查看ORACLE中的表结构时，这个命令就不能用了。
只能用下面的语句代替:
2.看字段名与数据类型
select*fromuser_tab_columnsWHERETABLE_name=upper('表名');//查看全部列
查看某些列
selectcolumn_name,data_type,data_length,DATA_PRECISION,DATA_SCALE
fromall_tab_columnswheretable_name=upper('表名');
3.可以通过user_constraints查看所有约束
select*fromuser_constraintswheretable_name=upper('表名');
查看主键约束:
select*fromuser_constraintswhereconstraint_type='P'andTABLE_name=upper('表名');
在系统表:all_tables/user_tables中有所有表的信息
在系统表:all_tab_columns/user_tab_columns中有所有表的字段信息
select*fromtab/dba_tables/dba_objects/cat;
看用户建立的表:
selecttable_namefromuser_tables;//当前用户的表
selecttable_namefromall_tables;//所有用户的表
selecttable_namefromdba_tables;//包括系统表
select*fromuser_indexes//可以查询出所有的用户表索引
查所有用户的表在all_tables
主键名称、外键在all_constraints
索引在all_indexes
但主键也会成为索引，所以主键也会在all_indexes里面。
具体需要的字段可以DESC下这几个view，dba登陆的话可以把all换成dba
1、查找表的所有索引（包括索引名，类型，构成列）:
selectt.*,i.index_typefromuser_ind_columnst,user_indexesiwheret.index_name=i.index_nameandt.table_name=i.table_nameandt.table_name=要查询的表
2、查找表的主键（包括名称，构成列）:
selectcu.*fromuser_cons_columnscu,user_constraintsauwherecu.constraint_name=au.constraint_nameandau.constraint_type='P'andau.table_name=要查询的表
3、查找表的唯一性约束（包括名称，构成列）:
selectcolumn_namefromuser_cons_columnscu,user_constraintsauwherecu.constraint_name=au.constraint_nameandau.constraint_type='U'andau.table_name=要查询的表
4、查找表的外键（包括名称，引用表的表名和对应的键名，下面是分成多步查询）:
select*fromuser_constraintscwherec.constraint_type='R'andc.table_name=要查询的表
查询外键约束的列名:
select*fromuser_cons_columnsclwherecl.constraint_name=外键名称
查询引用表的键的列名:
select*fromuser_cons_columnsclwherecl.constraint_name=外键引用表的键名
5、查询表的所有列及其属性
selectt.*,c.COMMENTSfromuser_tab_columnst,user_col_commentscwheret.table_name=c.table_nameandt.column_name=c.column_nameandt.table_name=
要查询的表
查看MySQL表结构:
desc表名;
describe表名;
showcolumnsfrom表名;
showcreatetable表名;
select*frominformation_schema.columnswheretable_name='表名';
查看SQLSERVER表结构:
SELECT*fromuser_cons_columns;
selectCOLUMN_NAMEfromall_cons_columnsa,all_constraintsb
wherea.CONSTRAINT_NAME=b.CONSTRAINT_NAMEandb.CONSTRAINT_TYPE='P'ANDb.TABLE_NAME='你的表名';
MySQL注释符号:
#单行注释
--单行注释
/*...*/多行注释
MySQL的大小写的:
MySQL的查询默认是不区分大小写的如:
select*fromtable_namewherealike'a%'
select*fromtable_namewherealike'A%'
效果是一样的。
要让mysql查询区分大小写，可以:
select*fromtable_namewherebinaryalike'a%'
select*fromtable_namewherebinaryalike'A%'
也可以在建表时，加以标识
createtabletable_name(avarchar(20)binary);
MySQL查询时使用变量:
如果查询时需要用变量,而又不希望用存储过程,可以直接使用临时变量(仅本次连结有效)
变量以“@”开头,赋值时用“:=”符号;事先可以不需声明而直接使用,只是初始值为空
如:(注意:第一次使用时,值为空,故需要用ifnull函数)
Selectopenaccount,iodate,amtasinAmt,0asoutAmt,@a:=ifnull(@a,0)+amtas_sum
Fromev_cashwheretype=1andopenaccount={?OpenAccount}Andiodatebetween'{?StartDate}'and'{?EndDate}'
union
Selectopenaccount,iodate,0asinAmt,amtasoutAmt,remark,@a:=@a-amtas_sum
Fromev_cashwheretype=2andopenaccount={?OpenAccount}Andiodatebetween'{?StartDate}'and'{?EndDate}'
MySQL存储过程:
一个存储过程包括名字，参数列表，以及可以包括很多SQL语句的SQL语句集。
在这里对局部变量，异常处理，循环控制和IF条件句有新的语法定义。
在5.0以上版本可用存储过程，检查版本可用语句:SHOWVARIABLESLIKE'version';或者SELECTVERSION();
CREATEPROCEDUREprocedure1/*name存储过程名*/
(INparameter1INTEGER)/*parameters参数*/
BEGIN/*startofblock语句块头*/
DECLAREvariable1CHAR(10);/*variables变量声明,一定要在開頭的語句*/
IFparameter1=17orparameter1>50THEN/*startofIFIF条件开始*/
SETvariable1='birds';/*assignment赋值*/
SETvariable1='ddd';/*assignment操作語句2,這裡只為模擬*/
ELSE
SETvariable1='beasts';/*assignment赋值*/
ENDIF;/*endofIFIF结束*/
INSERTINTOta
ble1VALUES(variable1);/*statementSQL语句*/
END/*endofblock语句块结束*/
最简单的存储过程:
CREATEPROCEDUREp1()SELECT*FROMtableName;
呼叫它:CALLp1();
注意:
1.存储过程名对大小写不敏感。
2.在同一个数据库不能给两个存储过程取相同的名字，否则会导致重载。MySQL还不支持重载(希望以后会支持。)
3.可以采取“数据库名.存储过程名”，如“db5.p1”。存储过程名可以分开，它可以包括空格符，其长度限制为64个字符
4.但注意不要使用MySQL内建函数的名字，否则将会出错。
PickaDelimiter选择分隔符:
DELIMITER///*也可以用“|”或“@”符号*/
如果以后要恢复使用“;”(分号)作为分隔符，只需输入:DELIMITER;//
用法如(使用“$”作为分隔符):
DELIMITER$
dropprocedureifexistsff$
CREATEPROCEDURE`ff`()
BEGIN
declareiinteger;#临时变量
seti=1;
#循环
whilei<=10do
begin
#操作
seti=i+1;#递增量
end;
endwhile;
END$
DELIMITER;$#恢复分号作分隔符
MySQL存储过程影响的行数:
selectFOUND_ROWS();#select读取的行数
selectROW_COUNT();#updatedeleteinsert等操作所影响的行数
注意:只能在存储过程中使用，仅能读取上一次的影响行数
mysql执行字符串的sql语句:
mysql>PREPAREstmt1FROM'SELECTSQRT(POW(?,2)+POW(?,2))AShypotenuse';
mysql>SET@a=3;
mysql>SET@b=4;
mysql>EXECUTEstmt1USING@a,@b;
没参数的:
mysql>PREPAREstmt1FROM'SELECT*fromarticalinfo';
mysql>EXECUTEstmt1
执行完的sql删除的时候用下面的语句:
mysql>DEALLOCATEPREPAREstmt1;
mysql创建表时:
CREATETABLEtableName(
`item1`dateNOTNULL,
`item2`varchar(50)defaultNULL,
`item3`int(10)unsignedNOTNULL,
PRIMARYKEY(`item2`)
)ENGINE=MyISAMDEFAULTCHARSET=utf8ROW_FORMAT=DYNAMIC;
#ENGINE=MyISAM表示不支持事务，ENGINE=InnoDB支持事务。DEFAULTCHARSET=utf8默认字符集
windows控制台执行MySQL:
运行cmd，输入:绝对路径的MySQL安装目录\bin\mysql.exe-h192.168.0.133-uroot-p13726402698
有mysql的环境变量时可运行cmd，输入:mysql-h192.168.0.133-uroot-p13726402698
参数说明:-h地址(不输入这个时，默认本机localhost)，-u用户名，-p密码
在安装目录下，写一个“mysql-startup.cmd”的文件，内容为“"bin\mysql"-uroot-proot-h127.0.0.1”,双击运行即可
mysql
備份與恢復用法
运行cmd，输入:绝对路径的MySQL安装目录\bin\mysqldump.exe-h192.168.0.133-uroot-prootftc>D:\ftc.sql
有mysql的环境变量时可运行cmd，输入:mysqldump-h192.168.0.133-uroot-prootftc>D:\ftc.sql
参数说明:-h地址(不输入这个时，默认本机localhost)，-u用户名，-p密码，数据库名称>导出文件的路径和名称
在安装目录下，写一个“mysqldump.cmd”的文件，内容为“"bin\mysqldump"-h127.0.0.1-uroot-prootftc>ftc.sql”,双击运行即可将“ftc”数据库导出到当前目录下
详细说明:
1.導出整個數據庫:mysqldump-u用戶名-p密码数据库名称>导出文件的路径和名称
2.導出一個表:mysqldump-u用戶名-p密码数据库名称表名>导出文件的路径和名称
3.導出一個数据库結構:mysqldump-u用戶名-p密码-d--add-drop-table数据库名称>导出文件的路径和名称
-d沒有數據--add-drop-table在每個create語句之前增加一個droptable
導入數據庫:
mysql>use数据库;
mysql>source导出文件的路径和名称
如:mysql>sourced:\wcnc_db.sql
MYSQL的事务处理
1、用begin,rollback,commit来实现
begin开始一个事务
rollback事务回滚
commit事务确认
2、直接用set来改变mysql的自动提交模式
MYSQL默认是自动提交的，也就是你提交一个QUERY，它就直接执行！我们可以通过
setautocommit=0禁止自动提交
setautocommit=1开启自动提交
但注意当你用setautocommit=0的时候，你以后所有的SQL都将做为事务处理，直到你用commit确认或rollback结束，注意当你结束这个事务的同时也开启了个新的事务！按第一种方法只将当前的作为一个事务！
MYSQL中只有INNODB和BDB类型的数据表才能支持事务处理！其他的类型是不支持的！（切记！）
判断空值及转换:
下面，如果orders表的字段price为null的话，用0.0替换
SQLServer:selectisnull(price,0.0)fromorders
Oracle:selectnvl(price,0.0)fromorders
MySQL:selectifnull(price,0.0)fromorders
通用:selectif(priceisnull,0.0,price)fromorders
另外，判断条件的isnotnull，isnull都一样
select*fromorderswherepriceisnotnull
实例:
1.多重查询(基于MySQL)
selectcount(*)asallNums,sum(isFillIn)asFillin,sum(if(98Nums=3or(98Nums=2andit102!=0),1,0))as98Num3
from(
selectcaseitem298when0then1else0endisFillIn,caseitem298when-2then1else0endisSuspend,
if(isnull(left(a.item250,1))orifnull(item298,-2)<>0,0,left(a.item250,1))as98Nums
FROMft_running_statusASawherea.years=98"
)f
select'期初餘額'ast
ype,(a.amt+b.amt)asinAmt
from(
(selectif(sum(amt)isnull,0,sum(amt))asamtfromev_cashwheretype=1andiodate<'2010/06/26')asa,
(selectif(sum(amt)isnull,0,sum(amt))asamtfromev_atmwheretdate<'2010/06/26')asb
)
2.找领导:(member表,manager_id表示领导id),
selectid,last_namefrommemberoutwhereexists(select'x'frommemberwheremanager_id=out.id);
或者(效率低点):selectid,last_namefrommemberoutwhereidin(selectmanager_idfrommember);
3.IF条件(仅MySQL测试过,其他未测试)
--如果表1里面有custid='TW00'的资料,则只查询此一笔资料;否则查询所有资料
select*from表1whereif(exists(select'V'from表1wherecustid='TW00'),custid='TW00',1=1)
4.将纵列改成横排:
表tb_score:createtabletb_score(memidvarchar(20),classidvarchar(20),pointsnumeric(10,2))
selectdistinctmemid,
(selectpointsfromtb_scorewhereclassid='1001'andmemid=s.memid)JAVA,
(selectpointsfromtb_scorewhereclassid='1002'andmemid=s.memid)SQL,
(selectpointsfromtb_scorewhereclassid='1003'andmemid=s.memid)JSP,
(selectpointsfromtb_scorewhereclassid='1004'andmemid=s.memid)C
fromtb_scores
5.case用法:
selectMEM_ID,NAME,AVGPOINT,
(casetypewhen'student'then'学生'when'teacher'then'老师'else'其它'end)'type',
(casewhen(sex='m')then'男'else'女'end)as'sex'
fromtb_member
case+count:count里可用条件语句
selectcount(*)as'总数',count(casewhenitem248<=79then1end)as'79前'
fromtable1whereitem248isnotnullanditem298=0;
6.一次性更新多笔记录:
updateft_running_statusasfsetf.item11=(select(casee.countrywhen2then_utf8'美國'
when3then_utf8'加拿大'when4then_utf8'其他'else_utf8'本國'end)
fromenterpriseasewheree.id=f.item5)
7.多表更新
--更新東琳有,中壢也有的貨品,改貨品數量(多表同时update,方便复杂的条件)
updateev_inventorya,ev_inventorybseta.nowqty=a.nowqty+b.nowqty
wherea.wid='中壢'anda.pid=b.pidandb.wid='東琳';
8.update的select子查詢裡面使用自身表(MySQL)
--建立臨時表,因為同一個表沒法在update的select子查詢裡面使用
droptableifEXISTStem_inventory;
CREATETABLEtem_inventoryASSELECT*FROMev_inventorywherewid='東琳'orwid='中壢';
--更新東琳有,而中壢沒有的貨品,直接改倉庫ID即可
updateev_inventoryaseta.wid='中壢'wherea.wid='東琳'anda.pidnotin(
selectb.pidfromtem_inventorybwhereb.wid='中壢'
);
--刪除臨時表
droptableifEXISTStem_inventory;
9.MySQL查询及删除重复记录的方法
1、查找表中多余的重复记录，重复
记录是根据单个字段(pId)来判断(查询出所有重复的资料)
select*from表1
wherepIdin(selectpIdfrom表1groupbypIdhavingcount(*)>1);
2、删除表中多余的重复记录，重复记录是根据单个字段(pId)来判断，只留有rowid最小的记录
deletefrom表1
wherepIdin(selectpIdfrom表1groupbypIdhavingcount(*)>1)
andpIdnotin(selectmin(pId)from表1groupbypIdhavingcount(*)>1);
--建立唯一键来限制也可以,只是会改变表结构
ALTERIGNORETABLE資料表ADDUNIQUEINDEX(字段1,字段2);
3、查找表中多余的重复记录(多个字段)
select*from表1a
where(a.pId,a.seq)in(selectpId,seqfrom表1groupbypId,seqhavingcount(*)>1);
4、删除表中多余的重复记录(多个字段)，只留有rowid最小的记录
deletefrom表1a
where(a.pId,a.seq)in(selectpId,seqfrom表1groupbypId,seqhavingcount(*)>1)
androwidnotin(selectmin(rowid)from表1groupbypId,seqhavingcount(*)>1);
/*用临时表来做重复查询和删除操作;方便提高效率以及解决不能同表子查询删除的情况*/
--原本查询重复的SQL
select*frommusicawhere(a.moid,a.seqno)in
(selectmoid,seqnofrommusicgroupbymoid,seqnohavingcount(*)>1);
--上面SQL太慢，換用临时表來做
droptableifexiststem;
createtabletemas
selectmin(oid)asoid,moid,seqnofrommusicgroupbymoid,seqnohavingcount(*)>1;
selecta.*frommusica,temtwherea.moid=t.moidanda.seqno=t.seqno;
--刪除错误资料
deletefrommusicwhere(moid,seqno)in
(selectmoid,seqnofromtem)andoidnotin(selectoidfromtem);
droptabletem;
10.随机取值
1、SQLserver
selecttop10*fromc_cy_adminorderbynewid();
2、Access
SELECTtop10*FROM表1orderbyrnd(id);
3、MySQL
SELECT*FROMalbumorderbyrand()limit10;
