友商KISBOS内部培训课件汇.pptx

KISBOS报表开发(专业版);想篇-软件构造 看篇-基础五步走 做篇-需求分析 练篇-多练习到熟悉精通; ;;数据库的结构包括了表，视图，存储过程等。;视图vw ;存储过程sp 存储过程是一组为了完成特定功能的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。 ;SELECT查询语句(方法1) SELECT [ALL | DISTINCT] column1[,column2]FROM table1[,table2][WHERE conditions][GROUP BY column-list][HAVING conditions][ORDER BY column-list [ASC | DESC] ] ;SELECT查询语句(方法2) SELECT [ALL | DISTINCT] table1.column1,table2.column1, table2.column2 FROM table1 LEFT JOIN table2 on table1.column1=table2. column1 [WHERE conditions][GROUP BY column-list][HAVING conditions][ORDER BY column-list [ASC | DESC] ] ;UPDATE更新语句语法 update tablenameset columnname = newvalue[,nextcolumn = newvalue2...]where columnname OPERATOR value [and|or column OPERATOR value]; 实例操作：专业版中清空manager密码：update t_user set fsid=null where fname=‘manager’ 注意“=”符号和like差别,=是相等，like是模糊查询，上述实例用LIKE可以写成： Update t_user set fsid=null where fname like ‘%mana%’;其他语法： 删除表 drop table tablename 实例：drop table t_item 删除记录delete from “tablename”/*删除整张表记录*/ [WHERE “conditions”] /*删除表中某个条件的记录*/ 实例：delete from t_user where fname=‘张三’ ; SQL语言中函数所代表的意义： ;实例解析 --有这么一张报表的需求。做成台帐的形式，左边是取自于采购入库单，右边取自销售单。两者仅以物料编码来连接 select identity(int,1,1) Fid, ftrantype,case when fnumberin is null then fnumberout else fnumberin end fnumber, case when fnamein is null then fnameout else fnamein end fname, Fsupplynumber,fsupplyname,Fnotein,fbillnoin,fdatein,Fentryidin,Fqtyin,famounin,fempnamein, --fnumberout,fnameout, Fcustnumber,fcustname,fnoteout,fbillnoout,fdateout,fentryidout,fqtyout,famountout,fempnameout into #temp from ( --自定义报表：做成台帐的形式，左边是取自于采购入库单，右边取自销售单。两者仅以物料编码来连接 --采购入库 select h.ftrantype,h.fnumber fnumberin,h.fname fnamein,h.Fcustnumber Fsupplynumber,h.fcustname fsupplyname,h.fnote Fnotein,h.fbillno fbillnoin,h.fdate fdatein,h.fentryid fentryidin,h.fqty Fqtyin ,h.famount famounin,h.Fempname fempnamein, k.fnumber fnumberout,k.fname fnameout,k.Fcustnumber, k.fcustname ,k.fnote fnoteout,k.fbill