FileMaker SQL Reference

Size: px

Start display at page:

Download "FileMaker SQL Reference"

종박 판
5 years ago
Views:

1 FileMaker 14

7 1 1 1 SQL_C_WCHAR

8 SELECTSELECTlast_name SALARY * 1.05 SELECT SELECT [DISTINCT] {* column_expression [[AS] column_alias],...} FROM table_name [table_alias],... [ WHERE expr1 rel_operator expr2 ] [ GROUP BY {column_expression,...} ] [ HAVING expr1 rel_operator expr2 ] [ UNION [ALL] (SELECT...) ] [ ORDER BY {sort_expression [DESC ASC]},... ] [ OFFSET n {ROWS ROW} ] [ FETCH FIRST [ n [ PERCENT ] ] { ROWS ROW } {ONLY WITH TIES } ] [ FOR UPDATE [OF {column_expression,...}] ] column_alias departmentdept SELECT dept AS department FROM emp EMP.LAST_NAME E.LAST_NAMEEEMP DISTINCT SELECT DISTINCT dept FROM emp

9 SELECT SELECT SELECT FROMSELECT FROM table_name [table_alias] [, table_name [table_alias]] table_name table_alias LASTNAMEExecuteSQL SELECT "_LASTNAME" from emp FROM employee ELAST_NAMEE.LAST_NAMESELECT SELECT * FROM employee E, employee F WHERE E.manager_id = F.employee_id

10 INNER JOIN SELECT * FROM Salespeople INNER JOIN Sales_Data ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID LEFT OUTER JOIN SELECT * FROM Salespeople LEFT OUTER JOIN Sales_Data ON Salespeople.Salesperson_ID = Sales_Data.Salesperson_ID 1 RIGHT OUTER JOIN 1 FULL OUTER JOIN WHEREWHERE WHERE expr1 rel_operator expr2 expr1expr2 rel_operatorselect SELECT last_name,first_name FROM emp WHERE salary >= WHERE WHERE expr1 IS NULL WHERE NOT expr2 SELECTWHERE

11 GROUP BY GROUP BY columns columnsselect SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id HAVING HAVING expr1 rel_operator expr2 expr1expr2select rel_operator SELECT dept_id, SUM (salary) FROM emp GROUP BY dept_id HAVING SUM (salary) >

12 UNIONSELECTSELECT ALLUNION ALL SELECT statement UNION [ALL] SELECT statement UNIONSELECT SELECT last_name, salary, hire_date FROM emp UNION SELECT name, pay, birth_date FROM person EMPSALARYRAISESLAST_NAME SELECT SELECT last_name, salary FROM emp UNION SELECT salary, last_name FROM raises ORDER BY ORDER BY {sort_expression [DESC ASC]},... sort_expression ASC last_namefirst_nameselect SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name SELECT emp_id, last_name, first_name FROM emp ORDER BY 2,3 last_nameselectorder BY 2last_name

13 OFFSETFETCH FIRST OFFSETOFFSETSELECT FETCH FIRSTOFFSET OFFSETFETCH FIRSTSELECT OFFSET OFFSETFETCH FIRST OFFSET OFFSET n {ROWS ROW} ] nn ROWSROW FETCH FIRST FETCH FIRST [ n [ PERCENT ] ] { ROWS ROW } {ONLY WITH TIES } ] nn. PERCENTn npercent,. ROWSROW WITH TIESORDER BY WITH TIESORDER BY last_namefirst_name SELECT SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS ONLY

14 ORDER BY SELECT emp_id, last_name, first_name FROM emp ORDER BY last_name, first_name OFFSET 25 ROWS FETCH FIRST 10 ROWS WITH TIES FOR UPDATE FOR UPDATE [OF column_expressions] column_expressions column_expressions SALARY SELECT * FROM emp WHERE salary > FOR UPDATE OF last_name, first_name, salary

15 SELECT 'CatDog' FROM Salespeople SELECT 999 FROM Salespeople SELECT DATE ' ' FROM Salespeople SELECT TIME '02:49:03' FROM Salespeople SELECT TIMESTAMP ' :49:03' FROM Salespeople SELECT Company_Name FROM Sales_Data SELECT DISTINCT Company_Name FROM Sales_Data SELECT Amount FROM Sales_Data SELECT DISTINCT Amount FROM Sales_Data SELECT Date_Sold FROM Sales_Data SELECT DISTINCT Date_Sold FROM Sales_Data SELECT Time_Sold FROM Sales_Data SELECT DISTINCT Time_Sold FROM Sales_Data SELECT Timestamp_Sold FROM Sales_Data SELECT DISTINCT Timestamp_Sold FROM Sales_Data SELECT Company_Brochures FROM Sales_Data SELECT GETAS(Company_Logo, 'JPEG') FROM Sales_Data SELECT * FROM Salespeople SELECT DISTINCT * FROM Salespeople 열 SELECT * FROM Salespeople SalespeopleSELECT DISTINCT * FROM SalespeopleSalespeople 1 SELECT * FROM test WHERE c = SELECT * FROM test WHERE c <> 1 SELECTGetAs()

16 GetAS(field name, 'JPEG') SELECTSELECT NULL CAST()SELECT SELECT CAST(Company_Brochures AS VARCHAR) FROM Sales_Data 1 SQL_VARCHARSELECT 1 SELECT 1 SELECT GetAs()DEFAULT DEFAULT SELECT GetAs(Company_Brochures, DEFAULT) FROM Sales_Data GetAs() 1 GetAs()'FILE' SELECT GetAs(Company_Brochures, 'FILE') FROM Sales_Data 1 GetAs()'snd' SELECT GetAs(Company_Meeting, 'snd ') FROM Company_Newsletter

17 1 SELECT GetAs(Company_Logo, 'JPEG') FROM Company_Icons 'GIFf' 'PNTG' 'JPEG' '.SGI' 'JP2 ' 'TIFF' 'PDF ' 'TPIC' 'PNGf' '8BPS' DELETEDELETE DELETE FROM table_name [ WHERE { conditions } ] WHEREWHERE DELETE DELETE FROM emp WHERE emp_id = 'E10001' DELETEWHEREE10001 INSERT 1 1 SELECT INSERT INSERT INTO table_name [(column_name,...)] VALUES (expr,...) column_namevalues column_nameexpr column_name lastdates[4]

18 expr 'Don''t' INSERT INTO emp (last_name, first_name, emp_id, salary, hire_date) VALUES ('Smith', 'John', 'E22345', 27500, DATE ' ') INSERT EMP 삽입할 PutAs() INSERT INTO table_name (container_name) VALUES(? AS 'filename.file extension') PutAs()PutAs(col, 'type') SELECTcolumn_name SELECTINSERT SELECTINSERT INSERT INTO emp1 (first_name, last_name, emp_id, dept, salary) SELECT first_name, last_name, emp_id, dept, salary from emp WHERE dept = 'D050' INSERTSELECT INSERTSELECT SELECTINSERT INSERTUPDATE SELECTINSERT SELECT UPDATEUPDATE UPDATE table_name SET column_name = expr,... [ WHERE { conditions } ]

19 column_name expr 'Don''t' WHERE UPDATE UPDATE emp SET salary=32000, exempt=1 WHERE emp_id = 'E10001' UPDATEWHERE E10001 UPDATE emp SET salary = (SELECT avg(salary) from emp) WHERE emp_id = 'E10001' E10001 업데이트 PutAs() UPDATE table_name SET (container_name) =? AS 'filename.file extension' PutAs()PutAs(col, 'type') CREATE TABLECREATE TABLE CREATE TABLE table_name ( table_element_list [, table_element_list...] ) 1 table_nametable_name

20 1 table_element_list field_name field_type [[repetitions]] [DEFAULT expr] [UNIQUE NOT NULL PRIMARY KEY GLOBAL] [EXTERNAL relative_path_string [SECURE OPEN calc_path_string]] 1 field_name LASTNAMECREATE TABLE CREATE TABLE "_EMPLOYEE" (ID INT PRIMARY KEY, "_FIRSTNAME" VARCHAR(20), "_LASTNAME" VARCHAR(20)) 1 CREATE TABLErepetitions EMPLOYEE_ID INT[4] LASTNAME VARCHAR(20)[4] 1 field_typenumericdecimalintdatetime TIMESTAMPVARCHARCHARACTER VARYINGBLOBVARBINARYLONGVARBINARY BINARY VARYINGNUMERICDECIMAL DECIMAL(10,0)TIMETIMESTAMP TIMESTAMP(6)VARCHARCHARACTER VARYING VARCHAR(255) 1 DEFAULTexpr USERUSERNAMECURRENT_USER CURRENT_DATECURDATECURRENT_TIMECURTIMECURRENT_TIMESTAMP CURTIMESTAMPNULL 1 고유하게 1 NOT NULL 1 BLOBVARBINARYBINARY VARYINGfield_type 1 EXTERNAL relative_path_string SECUREOPEN calc_path_string relative_path_string

21 CREATE TABLE T1 (C1 VARCHAR, C2 VARCHAR (50), C3 VARCHAR (1001), C4 VARCHAR (500276)) NOT NULL CREATE TABLE T1NN (C1 VARCHAR NOT NULL, C2 VARCHAR (50) NOT NULL, C3 VARCHAR (1001) NOT NULL, C4 VARCHAR (500276) NOT NULL) CREATE TABLE T2 (C1 DECIMAL, C2 DECIMAL (10,0), C3 DECIMAL (7539,2), C4 DECIMAL (497925,301)) CREATE TABLE T3 (C1 DATE, C2 DATE, C3 DATE, C4 DATE) CREATE TABLE T4 (C1 TIME, C2 TIME, C3 TIME, C4 TIME) CREATE TABLE T5 (C1 TIMESTAMP, C2 TIMESTAMP, C3 TIMESTAMP, C4 TIMESTAMP) CREATE TABLE T6 (C1 BLOB, C2 BLOB, C3 BLOB, C4 BLOB) CREATE TABLE T7 (C1 BLOB EXTERNAL 'Files/MyDatabase/' SECURE) CREATE TABLE T8 (C1 BLOB EXTERNAL 'Files/MyDatabase/' OPEN 'Objects') ALTER TABLE ALTER TABLE ALTER TABLE table_name ADD [COLUMN] column_definition ALTER TABLE table_name DROP [COLUMN] unqualified_column_name ALTER TABLE table_name ALTER [COLUMN] column_definition SET DEFAULT expr ALTER TABLE table_name ALTER [COLUMN] column_definition DROP DEFAULT ALTER TABLE ALTER TABLE Salespeople ADD C1 VARCHAR ALTER TABLE Salespeople DROP C1 ALTER TABLE Salespeople ALTER Company SET DEFAULT 'FileMaker' ALTER TABLE Salespeople ALTER Company DROP DEFAULT SET DEFAULTDROP DEFAULT

22 CREATE INDEXCREATE INDEX CREATE INDEX ON table_name.column_name CREATE INDEX ON table_name (column_name) CREATE INDEX CREATE INDEX CREATE INDEX ON Salespeople.Salesperson_ID DROP INDEXDROP INDEX DROP INDEX ON table_name.column_name DROP INDEX ON table_name (column_name) SELECT PREVENT INDEX CREATION DROP INDEX ON Salespeople.Salesperson_ID SELECTWHEREHAVINGORDER BY 1

23 calcsales_data.invoice_id PRICE * 1.05 Number_Of_Days_In_June 'Don''t' 1 {D ' '} 1 {T '14:35:10'} 1 {TS ' :35:10'} DTTS

24 1 DATE 'YYYY-MM-DD' 1 TIME 'HH:MM:SS' 1 TIMESTAMP 'YYYY-MM-DD HH:MM:SS 'Paris' 1.05 DATE ' ' { D ' ' } {06/05/2015} {06/05/2015} TIME '14:35:10' { T '14:35:10' } {14:35:10} TIMESTAMP ' :35:10' { TS ' :35:10'} {06/05/ :35:10} {06/05/ :35:10} SELECT column1 / 3.4E+7 FROM table1 WHERE calc < 3.4E-6 * column2

25 last_name'jones ''ROBERT ': hire_datedate ' ' hire_date hire_date - DATE ' ' 29 hire_date - 10 SELECT Date_Sold, Date_Sold + 30 AS agg FROM Sales_Data SELECT Date_Sold, Date_Sold - 30 AS agg FROM Sales_Data = <> > >= < <= LIKE NOT LIKE IS NULL

26 IS NOT NULL BETWEEN IN NOT IN EXISTS ANY ALL SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1' SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Invoice_ID <> 125 SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount > 3000 SELECT Sales_Data.Time_Sold FROM Sales_Data WHERE Sales_Data.Time_Sold < '12:00:00' SELECT Sales_Data.Company_Name FROM Sales_Data WHERE Sales_Data.Company_Name LIKE '%University' SELECT Sales_Data.Company_Name FROM Sales_Data WHERE Sales_Data.Company_Name NOT LIKE '%University' SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount IS NULL SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Amount IS NOT NULL SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Invoice_ID BETWEEN 1 AND 10 SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (50,250,100) SELECT COUNT(Sales_Data.Invoice_ID) AS agg FROM Sales_Data WHERE Sales_Data.INVOICE_ID NOT IN (SELECT Sales_Data.Invoice_ID FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-4')

27 SELECT * FROM Sales_Data WHERE EXISTS (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NOT NULL) SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ANY (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID = 'SP-1') SELECT * FROM Sales_Data WHERE Sales_Data.Amount = ALL (SELECT Sales_Data.Amount FROM Sales_Data WHERE Sales_Data.Salesperson_ID IS NULL) ANDOR salary = AND exempt = 1 NOT (salary = AND exempt = 1) SELECT * FROM Sales_Data WHERE Sales_Data.Company_Name NOT LIKE '%University' AND Sales_Data.Amount > 3000 SELECT * FROM Sales_Data WHERE (Sales_Data.Company_Name LIKE '%University' OR Sales_Data.Amount > 3000) AND Sales_Data.Salesperson_ID = 'SP-1' '-''+'

28 WHERE salary > OR hire_date > (DATE ' ') AND dept = 'D101' WHERE (salary > OR hire_date > DATE ' ') AND dept = 'D101' SELECT AVG(SALARY)AVG(SALARY * 1.07) DISTINCT COUNT (DISTINCT last_name) SUM AVG COUNT SUM(SALARY) AVG(SALARY) COUNT(NAME) COUNTCOUNT COUNT(*)

29 MAX MIN MAX(SALARY) MIN(SALARY) SELECT SUM (Sales_Data.Amount) AS agg FROM Sales_Data SELECT AVG (Sales_Data.Amount) AS agg FROM Sales_Data SELECT COUNT (Sales_Data.Amount) AS agg FROM Sales_Data SELECT MAX (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount < 3000 SELECT MIN (Sales_Data.Amount) AS agg FROM Sales_Data WHERE Sales_Data.Amount > 3000

30 CHR CHR(67) CURRENT_USER DAYNAME RTRIM RTRIM(' ABC ')' ABC' TRIM TRIM(' ABC ')'ABC' LTRIM LTRIM(' ABC')'ABC' UPPER UPPER('Allen')'ALLEN' LOWER LOWER('Allen')'allen' LEFT LEFT('Mattson',3)'Mat' MONTHNAME RIGHT RIGHT('Mattson',4)'tson' SUBSTR SUBSTRING SUBSTR('Conrad',2,3)'onr' 를반환 SUBSTR('Conrad',2)'onrad' SPACE SPACE(5)' ' STRVAL STRVAL('Woltman')'Woltman' 을반환 STRVAL(5 * 3)'15' 를반환 STRVAL(4 = 5)'False' 를반환 STRVAL(DATE ' ') ' ' TIME TIMEVAL USERNAME USER TIME() FilterCURRENT_TIME

31 SELECT CHR(67) + SPACE(1) + CHR(70) FROM Salespeople SELECT RTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT TRIM(SPACE(1) + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT LTRIM(' ' + Salespeople.Salesperson_ID) AS agg FROM Salespeople SELECT UPPER(Salespeople.Salesperson) AS agg FROM Salespeople SELECT LOWER(Salespeople.Salesperson) AS agg FROM Salespeople SELECT LEFT(Salespeople.Salesperson, 5) AS agg FROM Salespeople SELECT RIGHT(Salespeople.Salesperson, 7) AS agg FROM Salespeople SELECT SUBSTR(Salespeople.Salesperson_ID, 2, 2) + SUBSTR(Salespeople.Salesperson_ID, 4, 2) AS agg FROM Salespeople SELECT SUBSTR(Salespeople.Salesperson_ID, 2) + SUBSTR(Salespeople.Salesperson_ID, 4) AS agg FROM Salespeople SELECT SPACE(2) + Salespeople.Salesperson_ID AS Salesperson_ID FROM Salespeople SELECT STRVAL('60506') AS agg FROM Sales_Data WHERE Sales_Data.Invoice = 1 ABS ATAN ATAN2 CEIL CEILING DEG DEGREES DAY DAY(DATE ' ') DAYOFWEEK DAYOFWEEK(DATE ' ') MOD MOD(10,3)

32 EXP FLOOR HOUR INT Int(6.4321) LENGTH LENGTH('ABC') MONTH MONTH(DATE ' ') LN LOG MAX MAX(66,89) MIN MIN(66,89) MINUTE NUMVAL PI RADIANS NUMVAL('123') ROUND ROUND( ,0) ROUND( ,2) ROUND( ,-2) SECOND SIGN SIN SQRT TAN YEAR YEAR(DATE ' ')

33 CURDATE CURRENT_DATE CURTIME CURRENT_TIME CURTIMESTAMP CURRENT_TIMESTAMP TIMESTAMPVAL TIMESTAMPVAL(' :00:00') DATE TODAY DATE() DATEVAL DATEVAL(' ') DATE() CASE WHEN input_expvalue_exp CASE {WHEN THEN...} [ELSE ] SELECT Invoice_ID, CASE Company_Name WHEN 'Exports UK' THEN 'Exports UK Found' WHEN 'Home Furniture Suppliers' THEN 'Home Furniture Suppliers Found' ELSE 'Neither Exports UK nor Home Furniture Suppliers' END, Salesperson_ID FROM Sales_Data CASE {WHEN THEN } [ELSE ] SELECT Invoice_ID, Amount, CASE WHEN Amount > 3000 THEN 'Above 3000' WHEN Amount < 1000 THEN 'Below 3000' ELSE 'Between 1000 and 3000' END, Salesperson_ID FROM Sales_Data

34 COALESCE SELECT Salesperson_ID, COALESCE(Sales_Manager, Salesperson) FROM Salespeople NULLIF SELECT Invoice_ID, NULLIF(Amount, -1), Salesperson_ID FROM Sales_Data

35 CREATE TABLEDEC create table t ("dec" numeric)

38 ,

39 ,

40 , 14 17, , , 9 16

FileMaker 16 SQL 참조

FileMaker 16 SQL 참조 FileMaker 16 SQL 2013-2017 FileMaker, Inc.. FileMaker, Inc. 5201 Patrick Henry Drive Santa Clara, California 95054 FileMaker, FileMaker Go FileMaker, Inc.. FileMaker WebDirect FileMaker Cloud FileMaker,