SELECT * FROM spfli WHERE carrid = ‘LH‘ AND

 ( CITYFROM = ‘FRANKFURT‘ OR  cityfrom = ‘NEW YORK‘ ).

SELECT * FROM spfli

       WHERE ( carrid = ‘LH‘ AND cityfrom = ‘FRANKFURT‘ ) OR

             ( carrid = ‘LH‘ AND cityfrom = ‘NEW YORK‘ ). 

FORM f_open_sql_sel.
  DATA:lt_sflight TYPE TABLE OF sflight.
  DATA:ls_sflight LIKE LINE OF lt_sflight.
  "查询语句
  "single
  SELECT SINGLE * FROM sflight
    INTO ls_sflight
    WHERE carrid = ‘AA‘.
  "distinct,去重
  SELECT DISTINCT carrid connid planetype FROM sflight
    INTO CORRESPONDING FIELDS OF TABLE lt_sflight
    WHERE carrid = ‘LH‘.
  "查询所有列
  SELECT * FROM sflight INTO TABLE lt_sflight.
  "查询指定列,
  "当列和结构,内表不匹配,需要INTO CORRESPONDING FIELDS OF TABLE
  "当查询列匹配时，直接INTO,INTO TABLE
  SELECT carrid connid planetype FROM sflight INTO CORRESPONDING FIELDS OF TABLE lt_sflight.
  "聚合函数
  TYPES:BEGIN OF s_flight,
          carrid TYPE sflight-carrid,
          connid TYPE sflight-connid,
          seats_max TYPE I,
          seats_min TYPE I,
          seats_avg TYPE I,
          seats_sum TYPE I,
          seats_count TYPE I,
          counts TYPE I,
          counts1 TYPE I,
        END OF s_flight.
  DATA:ls_flight TYPE s_flight.
  DATA:lv_count TYPE I.
  SELECT SINGLE
    carrid
    connid
    max( seatsmax ) as seats_max
    min( seatsmax ) as seats_min
    avg( seatsmax ) as seats_avg
    sum( seatsmax ) as seats_sum
    "distinct必须
    count( DISTINCT seatsmax ) as seats_count
    "两个count相同
    count( * ) as counts
    count(*) as counts1
    FROM sflight
    INTO ls_flight
    GROUP BY carrid connid.

  "动态查询字段
  DATA:t_comp TYPE TABLE OF string.
  APPEND ‘carrid‘ TO t_comp.
  APPEND ‘connid‘ TO t_comp.
  SELECT (t_comp) FROM sflight
    INTO CORRESPONDING FIELDS OF TABLE lt_sflight.
ENDFORM. 

"from
FORM f_open_sql_from.
  DATA:lt_sflight TYPE TABLE OF sflight.
  DATA:ls_sflight LIKE LINE OF lt_sflight.
  "取消自动client
  SELECT * FROM sflight
    CLIENT SPECIFIED
    INTO TABLE lt_sflight
  WHERE mandt = ‘000‘.
  "取消使用sap buffer
  SELECT * FROM sflight
    BYPASSING BUFFER
    INTO TABLE lt_sflight.
  "查询限定条数结果
  SELECT * FROM sflight
    UP TO 2 ROWS
    INTO TABLE lt_sflight.
  "join
  TYPES:BEGIN OF s_flight,
     carrid TYPE sflight-carrid,
     connid TYPE sflight-connid,
     carrname TYPE scarr-carrname,
     url TYPE scarr-url,
     fldate TYPE sflight-fldate,
     END OF s_flight.
  DATA:lt_flight TYPE TABLE OF s_flight.
  DATA:ls_flight LIKE LINE OF lt_flight.
  "默认内连接JOIN = INNER JOIN
  SELECT a~carrid a~carrname a~url b~connid b~fldate
    FROM scarr AS a JOIN sflight AS b
    ON a~carrid = b~carrid
    INTO CORRESPONDING FIELDS OF TABLE lt_flight
    WHERE a~carrid = ‘AA‘
    OR a~carrid = ‘AZ‘.
  "左外连接LEFT JOIN = LEFT OUTER JOIN
  SELECT a~carrid a~carrname a~url b~connid b~fldate
    FROM scarr AS a LEFT JOIN sflight AS b
    ON a~carrid = b~carrid
    INTO CORRESPONDING FIELDS OF TABLE lt_flight
    WHERE a~carrid = ‘AA‘
    OR a~carrid = ‘AZ‘.
ENDFORM. 

"into
FORM f_open_sql_into.
  DATA:lt_spfli TYPE TABLE OF spfli.
  DATA:ls_spfli LIKE LINE OF lt_spfli.
  "INTO查询插入内表，删除之前内表记录
  "APPENDING查询插入内表，不删除，追加记录
  SELECT * FROM spfli INTO CORRESPONDING FIELDS OF TABLE lt_spfli.
  SELECT * FROM spfli APPENDING CORRESPONDING FIELDS OF TABLE lt_spfli.
  SELECT * FROM spfli INTO TABLE lt_spfli.
  SELECT * FROM spfli APPENDING TABLE lt_spfli.
  "creating
  "CREATING {READER|LOCATOR FOR { COLUMNS blob1 blob2 ... clob1 clob2 ... } 
  "| { ALL [OTHER] [BLOB|CLOB] COLUMNS } [READER|LOCATOR FOR ...] } 
  "| {(crea_syntax)}  ... 
  "查询blob,clob字段
  "对应类:
  "CL_ABAP_DB_C_READER or CL_ABAP_DB_C_LOCATOR in the case of CLOBs.
  "CL_ABAP_DB_X_READER or CL_ABAP_DB_X_LOCATOR in the case of BLOBs.
ENDFORM.

"where
FORM f_open_sql_where.
  DATA:lt_sflight TYPE TABLE OF sflight.
  DATA:ls_sflight LIKE LINE OF lt_sflight.
  DATA:lt_carr TYPE TABLE OF scarr.
  DATA:ls_carr LIKE LINE OF lt_carr.
  "比较条件
  "=EQ,<>NE,>GT,<LT,>=GE,<=LE
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE carrid EQ ‘AA‘
    AND connid NE ‘0120‘.
  "between,not between a and b
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE seatsmax BETWEEN 500 AND 1000.
  "like,not like,_代表一个字符,%代表任意字符
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE carrid LIKE ‘%Z‘.
  "in,not in,
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE carrid NOT IN (‘AA‘,‘AZ‘).
  "in seltab，是否在range table
  DATA:lr_car TYPE RANGE OF sflight-carrid.
  DATA:lrs_car LIKE LINE OF lr_car.
  lrs_car-low = ‘AA‘.
  lrs_car-high = ‘AZ‘.
  lrs_car-option = ‘BT‘.
  lrs_car-sign = ‘I‘.
  APPEND lrs_car TO lr_car.
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE carrid IN lr_car.
  "is null,is not null是否为空
  "动态条件
  DATA:lv_con_syntax TYPE string.
  DATA:lt_con_syntax TYPE TABLE OF string.
  lv_con_syntax = ‘carrid = ‘‘AA‘‘ AND connid = ‘‘0017‘‘‘.
  APPEND lv_con_syntax TO lt_con_syntax.
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE (lv_con_syntax).
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE (lt_con_syntax).
  "子查询
  "col operator [ALL|ANY|SOME] subquery
  "[NOT] EXISTS subquery
  "[NOT] IN subquery
  "= SOME/ANY只要只查询中一笔记录匹配即可
  "相等于IN subquery
  "= ALL需要所有结果匹配
  "子查询只能返回一条记录,ALL,ANY,SOME可省略
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE carrid = SOME
    ( SELECT carrid FROM scarr
       WHERE  carrid = ‘AA‘).
  "exists subquery,子查询至少返回一条记录为真
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE EXISTS
    ( SELECT * FROM sflight
        WHERE carrid = ‘SQ‘).
  "AND,OR,NOT连接
  SELECT * FROM sflight INTO TABLE lt_sflight
    WHERE NOT carrid = ‘AA‘
    AND seatsmax > 100
    OR seatsocc < 100.
  "for all entry
  "会自动去除重复记录
  SELECT * FROM scarr INTO TABLE lt_carr.
  SELECT * FROM sflight INTO TABLE lt_sflight
    FOR ALL ENTRIES IN lt_carr
    WHERE carrid = lt_carr-carrid.
ENDFORM.

"group
FORM f_open_sql_group.
  TYPES:BEGIN OF s_flight,
          carrid TYPE spfli-carrid,
          connid TYPE spfli-connid,
          count1 TYPE I,
        END OF s_flight.
  DATA:lt_flight TYPE TABLE OF s_flight.
  DATA:ls_flight LIKE LINE OF lt_flight.
  "group by
  SELECT carrid count(*) as count1
    FROM spfli
    INTO (ls_flight-carrid,ls_flight-count1)
    GROUP BY carrid.
    WRITE:/ ls_flight-carrid,ls_flight-count1.
  ENDSELECT.
  
  "having筛选结果
  SELECT SINGLE carrid count(*) as count1
    FROM spfli
    INTO (ls_flight-carrid,ls_flight-count1)
    GROUP BY carrid
    HAVING carrid = ‘AA‘.
  WRITE:/ ls_flight-carrid,ls_flight-count1.
ENDFORM. 

FORM f_open_sql_order.
  DATA:lt_spfli TYPE TABLE OF spfli.
  DATA:ls_spfli LIKE LINE OF lt_spfli.
  "指定排序字段
  SELECT * FROM spfli INTO TABLE lt_spfli
    ORDER BY carrid ASCENDING connid DESCENDING.
  "primary key排序,默认升序
  SELECT * FROM spfli INTO TABLE lt_spfli
    ORDER BY PRIMARY KEY.
  "动态条件
  DATA:lv_dyn_order TYPE string.
  lv_dyn_order = ‘carrid ascending connid descending‘.
  SELECT * FROM spfli INTO TABLE lt_spfli
    ORDER BY (lv_dyn_order).
ENDFORM.

"cursor
FORM f_open_sql_cursor.
  DATA:c1 TYPE cursor.
  DATA:c2 TYPE cursor.
  DATA:lt_spfli TYPE TABLE OF spfli.
  DATA:ls_spfli LIKE LINE OF lt_spfli.
  DATA:lt_scarr TYPE TABLE OF scarr.
  DATA:ls_scarr LIKE LINE OF lt_scarr.
  "开启cursor
  OPEN CURSOR c1 FOR
    SELECT * FROM spfli.
  OPEN CURSOR c2 FOR
    SELECT * FROM scarr.
  "读取cursor到工作区
  FETCH NEXT CURSOR c1 INTO ls_spfli.
  "读取cursor到table，into或append
  FETCH NEXT CURSOR c1 INTO TABLE lt_spfli.
  "FETCH NEXT CURSOR c1 APPENDING TABLE lt_spfli.
  CLOSE CURSOR c1.
  "package size,读取数据条数
  FETCH NEXT CURSOR c2 INTO TABLE lt_scarr PACKAGE SIZE 2.
  CLOSE CURSOR c2.
ENDFORM.

"insert语句
FORM f_open_sql_insert.
  "INSERT
  DATA:ls_scarr TYPE scarr. 
  DATA:lt_scarr TYPE TABLE OF scarr.
  ls_scarr-carrid   = ‘FF‘. 
  ls_scarr-carrname = ‘Funny Flyers‘. 
  ls_scarr-currcode = ‘EUR‘. 
  ls_scarr-url = ‘http://www.funnyfly.com‘. 
  APPEND ls_scarr TO lt_scarr.
  "插入单行
  INSERT INTO scarr VALUES ls_scarr. 
  "插入单行
  INSERT scarr FROM ls_scarr.
  "插入多行
  INSERT scarr FROM TABLE lt_scarr.
  "可重复key
  INSERT scarr FROM TABLE lt_scarr ACCEPTING DUPLICATE KEYS.  
ENDFORM. 

"update语句
FORM f_open_sql_update.
  "update
  DATA:ls_scarr TYPE scarr.
  DATA:lt_scarr TYPE TABLE OF scarr.  
  ls_scarr-carrid   = ‘FF‘. 
  ls_scarr-carrname = ‘Funny Flyers‘. 
  ls_scarr-currcode = ‘EUR‘. 
  ls_scarr-url = ‘http://www.funnyfly.com‘. 
  APPEND ls_scarr TO lt_scarr.
  "方式1:update set c1 = f1 where cond
  UPDATE scarr 
  SET carrname = ls_scarr-carrname
      currcode = ls_scarr-currcode
    WHERE carrid = ls_scarr-carrid.
  "方式2:update from wa
  UPDATE scarr 
    FROM ls_scarr.
  "方式3:update from itab
  UPDATE scarr
    FROM TABLE lt_scarr.
ENDFORM.

"更新
FORM f_open_sql_modify.
  "modify
  DATA:ls_scarr TYPE scarr.
  DATA:lt_scarr TYPE TABLE OF scarr.
  ls_scarr-carrid   = ‘FF‘.
  ls_scarr-carrname = ‘Funny Flyers‘.
  ls_scarr-currcode = ‘USD‘.
  ls_scarr-url = ‘http://www.funnyfly.com‘.
  APPEND ls_scarr TO lt_scarr.
  "单笔更新
  MODIFY scarr FROM ls_scarr.
  "多笔更新
  MODIFY scarr FROM TABLE lt_scarr. 
ENDFORM. 

"删除
FORM f_open_sql_delete.
  "modify
  DATA:ls_scarr TYPE scarr.
  DATA:lt_scarr TYPE TABLE OF scarr.
  ls_scarr-carrid   = ‘FF‘.
  ls_scarr-carrname = ‘Funny Flyers‘.
  ls_scarr-currcode = ‘USD‘.
  ls_scarr-url = ‘http://www.funnyfly.com‘.
  APPEND ls_scarr TO lt_scarr.
  "删除，delete from target where
  DELETE FROM scarr WHERE carrid = ‘FF‘.
  "delete target from wa/itab
  DELETE scarr FROM ls_scarr.
  DELETE scarr FROM TABLE lt_scarr.
ENDFORM. 

"BLOB读写
FORM f_open_sql_lob_insert.
  "写入流
  DATA:lt_blob TYPE TABLE OF demo_blob_table.
  DATA:ls_blob TYPE demo_blob_table WRITER FOR COLUMNS picture.
  DATA:lo_stmnt TYPE REF TO cl_abap_sql_changing_stmnt.
  "读取图片资源
  "文件名，MIME
  DATA:lv_file TYPE C LENGTH 255 VALUE ‘/SAP/PUBLIC/TOM_TEST/pic.jpg‘.
  DATA:lo_mime_api TYPE REF TO if_mr_api.
  DATA:lv_xstring TYPE xstring.
  DATA:lv_x1024 TYPE x LENGTH 1024.
  DATA:lt_x1024 LIKE TABLE OF lv_x1024.
  "状态
  DATA:lv_subrc TYPE sy-subrc.

  "mime repository api对象
  lo_mime_api = cl_mime_repository_api=>get_api( ).
  "mime中图片
  lo_mime_api->get(
    EXPORTING
       i_url = lv_file
    IMPORTING
       e_content = lv_xstring
  ).
  DO.
    IF xstrlen( lv_xstring ) > 1024.
      lv_x1024 = lv_xstring(1024).
      APPEND lv_x1024 TO lt_x1024.
      SHIFT lv_xstring BY 1024 PLACES LEFT IN BYTE MODE.
    ELSE.
      APPEND lv_xstring TO lt_x1024.
      EXIT.
    ENDIF.
  ENDDO.

  TRY.
    ls_blob-name = lv_file.
    INSERT demo_blob_table FROM ls_blob.
    IF sy-subrc = 4.
      lv_subrc = 4.
    ELSE.
      lo_stmnt = ls_blob-picture->get_statement_handle( ).
      LOOP AT lt_x1024 INTO lv_x1024.
        lv_xstring = lv_x1024.
        ls_blob-picture->write( lv_xstring ).
      ENDLOOP.
      ls_blob-picture->close( ).

      IF lo_stmnt->get_db_count( ) <> 1.
        lv_subrc = 4.
      ENDIF.
    ENDIF.
  CATCH cx_stream_io_exception cx_close_resource_error.
    lv_subrc = 4.
  ENDTRY.
  IF lv_subrc <> 0.
    MESSAGE ‘insert error‘ TYPE ‘E‘.
  ELSE.
    MESSAGE ‘insert success‘ TYPE ‘S‘.
  ENDIF.
ENDFORM.

"读取图片
FORM f_open_sql_lob_read.
  "字节读取流
  DATA:lo_reader TYPE REF TO cl_abap_db_x_reader.
  "文件名，MIME
  DATA:lv_file TYPE C LENGTH 255 VALUE ‘/SAP/PUBLIC/TOM_TEST/pic.jpg‘.
  DATA:lv_xstring TYPE xstring.
  DATA:lv_x1024 TYPE x LENGTH 1024.
  DATA:lt_x1024 LIKE TABLE OF lv_x1024.

  SELECT SINGLE picture FROM demo_blob_table INTO lo_reader
    WHERE name = lv_file.
  "读取数据
  WHILE lo_reader->data_available( ) = ‘X‘.
    APPEND lo_reader->read( 1024 ) TO lt_x1024.
  ENDWHILE.
  lo_reader->close( ).

  "显示图片
  DATA:html_str TYPE string.
  DATA:ext_data   TYPE cl_abap_browser=>load_tab.
  DATA:ext_line   TYPE cl_abap_browser=>load_tab_line.
  html_str = ‘<html><body><img src="PIC.GIF" ></body></html>‘.
  ext_line-name = ‘PIC.GIF‘.
  ext_line-type = ‘image‘.
  GET REFERENCE OF lt_x1024 INTO ext_line-dref.
  APPEND ext_line TO ext_data.
  cl_abap_browser=>show_html(
    EXPORTING
      html_string = html_str
      format      = cl_abap_browser=>landscape
      size        = cl_abap_browser=>small
      data_table  = ext_data
      check_html  = ‘ ‘ ).
ENDFORM.