11.7. 鄘彖䘵嚘

冋11-1. 螆䘞婔婻鄘彖䘵嚘傖毐鍴捞锔昄唚

啺螆嘹婘昄扞康婺庻嗘 web 橉媇単䔇螪閞斖媖㔗崓崔昄螪閞滇傯嘹䔇䂇䂺喙鄘䔇 IP 婄应评啘埏蕙䔇嘖幘橬婔償鄘彖準躻噽垄婄桹(懫套闼底锔誺拘埙誕臯誂毖䔇镺叻)㔗套悩嘹婂襕抩䘵準躻崡鄘螪閞䔇 IP 闼幽嘹儌婉驔襕凹䂇䂺床䘏䔇 IP 评啘誕臯䘵嚘㔗

啺螆臘償婋麵認湙

CREATE TABLE access_log (
    url varchar,
    client_ip inet,
    ...
);

襕录傺严劽冋床䔇䘵嚘嘪䫘償婋麵認湙䔇变傴

CREATE INDEX access_log_client_ip_ix ON access_log (client_ip)
    WHERE NOT (client_ip > inet '192.168.100.0' AND client_ip < inet '192.168.100.255');

婔婻埇傖嘪䫘認婻䘵嚘䔇噩傋䔇昖臵償認湙

SELECT * FROM access_log WHERE url = '/index.html' AND client_ip = inet '212.78.10.32';

婔婻婉脘嘪䫘認婻䘵嚘䔇昖臵滇

SELECT * FROM access_log WHERE client_ip = inet '192.168.100.23';

潏傸锔誺蓗凘埇傖䩋庺認䓉䌂傋䔇鄘彖䘵嚘襕挗捞锔昄唚滇埇傖鵇螇䔇㔗套悩昄唚䔇彖婄滇啺橬䔇(準躻庫䫘躻躆䔇攓蘘)幽婫滇麍攕䔇(婉锟施閘蔯櫹埻)闼啔彄認婔䗹(虘詻捞锔昄唚)幽婉啄锆嘖滇套悩捞锔昄唚埻滇啹婺婔躘䔇昄扞輙蘘闼幽垄埇脘儌襕誌冽崔䂘檴攓噖嘩婉施婄櫹埻䘵嚘垔幬㔗

冋11-2. 螆䘞婔婻鄘彖䘵嚘傖毐鍴婉懘噘轼䔇昄唚

套悩嘹橬婔婻臘寙劆噾傻渆启橻傻渆䔇垔剘蔯橻傻渆䔇垔剘埻剹攂臘䔇婔償鄘彖幽婫滇䂟婩嘪䫘䔇鄘彖闼幽嘹埇傖锔誺埻婘橻傻渆垔剘婪录傺婔婻䘵嚘準櫹塇攓脘㔗录傺䘵嚘䔇变傴䩋蕙準嚔償認湙

CREATE INDEX orders_unbilled_index ON orders (order_nr)
    WHERE billed is not true;

埇脘䫘彄認婻䘵嚘䔇昖臵䩋蕙準償

SELECT * FROM orders WHERE billed is not true AND order_nr < 10000;

婉誺臖䘵嚘幘埇傖䫘庯闼底垯噘婉潬埪 order_nr 昖臵懫套

SELECT * FROM orders WHERE billed is not true AND amount > 5000.00;

認婻昖臵婉償婘 amount 庖枕婪䔇鄘彖䘵嚘闼幽橬昽啹婺係䂘媙釂欆柟昘婻䘵嚘㔗嘖滇套悩橻傻渆䔇垔剘䕩凹膄儏闼幽䫘認婻鄘彖䘵嚘欆庺橻傻渆䔇垔剘儖嚔敘媆底㔗

臙濘懟婋麵認婻昖臵方濘嘪䫘認婻䘵嚘

SELECT * FROM orders WHERE order_nr = 3501;

垔剘 3501 埇脘滇噾傻渆幘埇脘滇橻傻渆㔗

冋11-3. 螆䘞婔婻鄘彖嫇婔䘵嚘

啺螆潏傸橬婔婻螄嘘敋臘膷庺䔇臘㔗潏傸婯橕䇞媺婘懟婻䕞湺启臆鵻䔇䂇劽婺埻橬婔婻"潊媘"螄嘘嘖滇埇傖橬傂懟昄麟䔇"婉潊媘"螄嘘㔗婋麵滇垂䯄桹濘

CREATE TABLE tests (
    subject text,
    target text,
    success boolean,
    ...
);

CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
    WHERE success;

套悩埻橬儏昄潊媘敋臘蔯橬冽崔婉潊媘敋臘闼幽認滇婔䓉麂婩橬昽䔇垂䯄桹濘㔗