sql 随笔

来源：互联网发布：铣刀移动网络销售编辑：程序博客网时间：2024/05/16 13:38

select count(1) ROWNUMS from (SELECT FID,UNIT_NAME FROM VW_BS_UNIT WHERE FID='101001' ) a 正确执行，（dbutil）取得object[]对象对象里存了一个数字 1
select count(1） from (SELECT FID,UNIT_NAME FROM VW_BS_UNIT WHERE FID='101001' ) a
正确执行，（dbutil）取得object[]对象对象里存了一个数字 1
select count(1） from (SELECT FID,UNIT_NAME FROM VW_BS_UNIT WHERE FID='101001' )
错误：Every derived table must have its own alias（每一个派生表必须有它自己的别名）

SELECT t1.roleId FROM m_user t1 WHERE t1.id=31
SELECT priId,priName FROM(SELECT t2.id,t2.name,t3.roleId,t3.privilegeId,
t4.id AS priId,t4.name AS priName,t4.parentId
FROM m_role t2 LEFT JOIN m_role_privilege t3 ON t2.id=1
LEFT JOIN m_privilege t4 ON t3.privilegeId=t4.id ) AS id
-----------------------
增删改
execute 执行sql返回boolean值
executeUpdate 执行返回影响行数

----------------------
isnull(expr)

如expr为null，那么isnull()的返回值为1，否则返回值为0。

ifnull(expr1,expr2)

假如expr1不为NULL，则IFNULL()的返回值为expr1; 否则其返回值为expr2。

nullif(expr1,expr2)

如果expr1=expr2成立，那么返回值为NULL，否则返回值为expr1。

----------------------
mysql:

DATE_FORMAT(T11.SENDTIME,'%Y-%m-%d %H:%i:%s') AS SENDTIME

CASE IFNULL(T12.INDEXID,'0') WHEN '0' THEN 'FALSE' ELSE 'TRUE' END AS HAVEATTACH

mssql:

CONVERT(char(21),T11.SENDTIME, 120) AS SENDTIME

CASE ISNULL(T12.INDEXID,'0') WHEN '0' THEN 'FALSE' ELSE 'TRUE' END AS HAVEATTACH

orcle:

TO_CHAR(T11.SENDTIME,'yyyy-mm-dd hh24:mi:ss') AS SENDTIME

"DECODE(T12.INDEXID,NULL,'FALSE','TRUE') AS HAVEATTACH

----------------------
获取所有表名

select table_name,table_type from information_schema.tables where
table_schema='asdb' and table_type='base table';

获取视图名
select table_name from information_schema.views where
table_schema='asdb'

1 0