YoucanusetheSETENCRYPTIONPASSWORDstatementwithbuilt-inSQL encryptionfunctionsthatuseAdvancedEncryptionStandard(AES)andTriple DES(3DES)encryptiontosecureyoursensitivedata.Whenyouuse encryption,onlythoseuserswhohavethecorrectpasswordwillbeable to read,copy,ormodifythedata.
UsetheSETENCRYPTIONPASSWORDstatementwiththefollowingbuilt-in encryptionand decryptionfunctions:
v ENCRYPT_AES ENCRYPT_AES(data-string-expression [, password-string-expression [, hint-string-expression ]]) v ENCRYPT_TDES ENCRYPT_TDES (data-string-expression [, password-string-expression [, hint-string-expression ]]) v DECRYPT_CHAR DECRYPT_CHAR(EncryptedData [, PasswordOrPhrase]) v DECRYPT_BINARY DECRYPT_BINARY(EncryptedData [, PasswordOrPhrase]) v GETHINT GETHINT(EncryptedData)
UseENCRYPT_AESandENCRYPT_TDEStodefineencrypteddataand use
DECRYPT_CHARand DECRYPT_BINARYtoqueryencrypteddata.Use
GETHINTtodisplaythepasswordhintstring,if set,ontheserver.
YoucanusetheseSQLbuilt-infunctionstoimplementcolumn-levelor cell-levelencryption.
v Usecolumn-levelencryptiontoencryptallvaluesina givencolumnwith thesamepassword.
v Usecell-levelencryptiontoencryptdatawithin thecolumnwithdifferent passwords.
Thefollowingexampleusescolumn-levelencryptiontosecurecreditcard data.
SELECT stock_num, manu_code, description FROM stock WHERE conv_price(unit_price, ex_rate = 1.50, tariff = 50.00) < 1000
Touse column-leveldataencryptionto securecreditcarddata: 1. Createthetable.
create table customer (id char(30), creditcard lvarchar(67)); 2. Inserttheencryptiondata.
a. Setsessionpassword.
SET ENCRYPTION PASSWORD "credit card number is encrypted"; b. Encryptdata.
INSERT INTO customer VALUES
("Alice", encrypt_aes("1234567890123456")); INSERT INTO customer VALUES
("Bob", encrypt_aes("2345678901234567")); 3. Query encryptiondatawith decryptionfunction:
SET ENCRYPTION PASSWORD "credit card number is encrypted"; SELECT id FROM customer
WHERE DECRYPT_CHAR(creditcard) = "2345678901234567"
Formoreinformationonencryptionsecurity,seeIBM Informix:Administrator's Guide.
Formoreinformationonthesyntaxofbuilt-inencryptionanddecryption functions,seeIBM Informix:GuidetoSQLSyntax.
Summary
Thischapterintroducedsamplesyntax andresultsforfunctionsinbasic SELECTstatementstoqueryarelationaldatabaseandtomanipulatethe returneddata.“UsingFunctionsinSELECTStatements”onpage4-2shows howtoperformthefollowingactions:
v Usetheaggregatefunctionsintheprojectionclausetocalculateand retrieve specificdata.
v IncludethetimefunctionsDATE,DAY,MDY,MONTH,WEEKDAY,
YEAR,CURRENT,andEXTENDplustheTODAY,LENGTH,and USER
functionsinyourSELECTstatements.
v UseconversionfunctionsintheSELECTclausetoconvertbetweendate andcharactervalues.
v Usestring-manipulationfunctionsintheSELECTclausetoconvertbetween upperandlowercaselettersortomanipulatecharacterstringsinvarious ways.
“UsingSPLRoutinesinSELECTStatements”onpage4-33showshowto includeSPLroutinesinyourSELECTstatements.
“UsingData EncryptionFunctions(IDS)”onpage4-35showshowtheuseof theSET ENCRYPTIONstatementand built-inencryptionand decryption functionscanpreventuserswhocannotprovideapasswordfromviewingor modifyingsensitivedata.
Chapter
5.
Composing
Advanced
SELECT
Statements
UsingtheGROUPBYandHAVINGClauses. . . 5-2 UsingtheGROUPBYClause . . . 5-3 UsingtheHAVINGClause . . . 5-6 CreatingAdvancedJoins . . . 5-9 Self-Joins. . . 5-9 OuterJoins . . . 5-13 InformixExtensiontoOuterJoinSyntax. . . 5-14 ANSIJoinSyntax . . . 5-14 LeftOuterJoin . . . 5-15 RightOuterJoin(IDS). . . 5-16 SimpleJoin . . . 5-17 SimpleOuterJoinonTwoTables . . . 5-18 OuterJoinforaSimpleJointoaThirdTable . . . 5-19 OuterJoinofTwoTablestoaThirdTable . . . 5-20 JoinsThatCombineOuterJoins . . . 5-21 SubqueriesinSELECTStatements . . . 5-22 CorrelatedSubqueries . . . 5-22 SubqueriesinSELECTStatements . . . 5-23 SubqueriesinaProjectionClause . . . 5-24 SubqueriesinWHEREClauses . . . 5-24 UsingALL. . . 5-25 UsingANY . . . 5-26 Single-ValuedSubqueries. . . 5-27 CorrelatedSubqueries . . . 5-28 UsingEXISTS. . . 5-29 HandlingCollectionsinSELECTStatements(IDS) . . . 5-32 CollectionSubqueries . . . 5-32 OmittingtheItemKeywordinaCollectionSubquery . . . 5-33 SpecifyingtheITEMKeywordinaCollectionSubquery . . . 5-33 Collection-DerivedTables. . . 5-34 SetOperations . . . 5-36 Union . . . 5-36 UsingORDERBYwithUNION . . . 5-38 UsingUNIONALL. . . 5-38 UsingDifferentColumnNames . . . 5-39 UsingUNIONwithMultipleTables . . . 5-40 UsingaLiteralintheProjectionClause . . . 5-41 UsingaFIRSTClause . . . 5-42 Intersection . . . 5-43 Difference . . . 5-44
Summary . . . 5-45
InThisChapter
Thischapterincreasesthescopeofwhatyoucandowith theSELECT statementand enablesyoutoperformmorecomplex databasequeriesand datamanipulation.Chapter2,“ComposingSELECTStatements,”onpage2-1, focusedonfiveoftheclausesintheSELECTstatementsyntax.Thischapter addstheGROUPBYclauseandtheHAVINGclause.YoucanusetheGROUP BYclause withaggregatefunctionstoorganizerowsreturnedbytheFROM clause.Youcanincludea HAVINGclausetoplaceconditions onthevalues thattheGROUPBYclausereturns.
Thischapteralsoextendstheearlier discussionofjoins.Itillustratesself-joins, whichenableyoutojoinatable toitself,and fourkindsofouterjoins,in whichyouapplythekeywordOUTERtotreattwoor morejoinedtables unequally.Italso introducescorrelatedand uncorrelatedsubqueriesandtheir operationalkeywords, showshowtocombinequerieswiththeUNION operator,anddefinesthesetoperationsknown asunion,intersection,and difference.
ExamplesinthischaptershowhowtousesomeoralloftheSELECT statementclauses inyourqueries.Theclauses mustappearinthefollowing order: 1. FROM 2. WHERE 3. GROUPBY 4. HAVING 5. ORDER BY 6. INTOTEMP
Foranexampleofa SELECTstatementthatusesalltheseclausesinthe correctorder,seeFigure5-15onpage5-8.
AnadditionalSELECTstatementclause,INTO,whichyoucanusetospecify programand hostvariablesinSQLAPIs,isdescribedinChapter8,
“ProgrammingwithSQL,”onpage8-1,aswell asinthemanualsthatcome withtheproduct.