SQL自我总结

插入

 

INSERT INTO

T_PICTURE  (PICTURE_ID,PICTURE_PATH,PICTURE_FRONT_PATH,PICTURE_STATE)

VALUES(S_PICTURE.NEXTVAL,'$target_path','$indexPath','可用') ;

 

 

删除

 

DELETE FROM T_PICTURE WHERE PICTURE_ID = 1;

 

 

修改

 

UPDATE

T_PICTURE

SET PICTURE_TYPE='剧照'               //多个,隔开如（set 字段1=值1,字段2=值2）

WHERE MOVIE_ID='$arg_movieId'

AND PICTURE_ID='$arg_posterId'";

 

 

查询

 

 SELECT ORDER_NO,BOOKING_METHOD,TICKET_TYPE_LIST,PRICE,TICKET_COUNT,

                PAY_WAY,SERVER_WAY,SEATS,PHONE,FEE,TKT_SINGLE_PRICE_LIST,TKT_FAVORABLE_FLG

FROM

T_ORDER_ARGUMENT

WHERE

T_ORDER_ARGUMENT.ORDER_ARGUMENT_ID =

(SELECT MAX(ORDER_ARGUMENT_ID) FROM T_ORDER_ARGUMENT WHERE T_ORDER_ARGUMENT.ADD1='$arg_oid')

 

 

 

 

php插入获取插入后的id

 

$sql = "INSERT INTO

             T_PICTURE  (PICTURE_ID,PICTURE_PATH,PICTURE_FRONT_PATH,PICTURE_STATE)

             VALUES(S_PICTURE.NEXTVAL,'$target_path','$indexPath','可用') RETURNING PICTURE_ID INTO :id  

            ";

            $stid = oci_parse(dbUtil::getDbConn(), $sql); 

 

            oci_bind_by_name($stid, ':id', $pictureId, 24);
            oci_execute($stid);

 

首先RETURNING PICTURE_ID INTO :id  将要获取的主键PICTURE_ID 映射到id，

然后oci_bind_by_name($stid, ':id', $pictureId, 24); 取出id对应的PICTURE_ID值赋值到$pictureId变量中，

所以$pictureId即为刚插入的主键PICTURE_ID值了

 

 

php 查询分页

 

 

$pageStar = ($arg_pageNow - 1) * $arg_pageSize +1;

$pageEnd = $arg_pageNow * $arg_pageSize;

 

 

 

 SELECT *

                   FROM (SELECT  HOT_MOVIE_ID,HOT_MOVIE_CACHE_NAME,PICTURE_PATH,DIRECTOR,MOVIE_TYPE,SHOWING_TIME,DATEINDATE_IN,PLAYER,MOVIE_LANGUAGE,HOT_MOVIE_CACHE_AREA, ROWNUM AS rownumber

                   FROM V_HOT_LOCATION

                   WHERE HOT_MOVIE_CACHE_AREA = '$arg_location'

                   AND HOT_MOVIE_CACHE_YEAR = '$currentYear')

                   WHERE rownumber BETWEEN '$pageStar' AND '$pageEnd'

                   ORDER BY HOT_MOVIE_ID DESC

 

 

 

php 查询总数

 

 

 $sql = "

             SELECT  COUNT(*) AS NUM_ROWS

                    FROM V_HOT_LOCATION

                    WHERE HOT_MOVIE_CACHE_AREA = '$arg_location'

                    AND HOT_MOVIE_CACHE_YEAR = '$currentYear'

                    AND PICTURE_TYPE ='mobile_FP_240x320'

                    ";

 

              $stid = oci_parse(dbUtil::getDbConn(),$sql);

              oci_define_by_name($stid, 'NUM_ROWS', $movieTotal);

              oci_execute($stid);

              oci_fetch($stid);

 

 

时间格式

 

to_char(SHOW_DATE,'yyyy-mm-dd') AS SHOW_DATE