SQL Practice and Tips <2>

Transfer from Http://i.cnblogs.com/EditPosts.aspx?opt=1

A few tips
　

(1) | | Use of:
Select ' (' | | | phone[1,3]| | ') ' PHONE[5,12], from customer where customer_num=106;
Result: (415) 389-8789

(2) Use of Trim: Select Trim (fname) from customer; Remove leading and trailing spaces.
Select Trim (Leading ' 1 ' from phone) from customer;
The return value removes the character ' 1 ' If the phone field starts with the character ' 1 '.

(3) Insert of Serail value: TT has two fields, field C1 is Serail, field C2 is char (8);
INSERT into TT values (0, ' AA ') and insert into TT values (0, ' BB ');
SELECT * from TT; results: 1 AA, 2 BB;
For serial fields, inserting 0 indicates that sequence values are automatically inserted by the database system.

(4) Use of delimiter:
The characters used in the interval between the specified field values in load and unload
The character ' | ' is used by default. such as: Unload to ' next.txt ' delimiter ', ' select * from TT;
But cannot use ' \ ', hex digits, tab or space.

(5) Union and UNION ALL:
Requires the query to return the same name, the same type, the former does not repeat, the latter shows duplicate values.

　

Use sorts or joins in selects
If you use sorts or joins in selects, you can use the temporary table example:
SQL1:
SELECT time_records.*, Case_name
From Time_records, OUTER cases
WHERE time_records.client = "AA1000"
and time_records.case_no = Cases.case_no
ORDER by Time_records.case_no
SQL2:
SELECT time_records.*, Case_name
From Time_records, OUTER cases
WHERE time_records.client = "AA1000"
and time_records.case_no = Cases.case_no
into temp foo;
SELECT * from Foo
ORDER by Case_no
Description: Statement Two is more time-saving than statement one.


　

Selects with not in or not EXISTS clause
Example: SQL1: (The execution of this statement will be time-consuming)
SELECT code from table1
WHERE code not in (SELECT code from table2);
SQL2:
SELECT code, 0 flag from table1 to TEMP Tflag;
UPDATE tflag SET flag = 1
WHERE code in
(SELECT code from table2 WHERE tflag.code = Table2.code);
SELECT * from Tflag WHERE flag = 0;

Note: In most cases statement two is more efficient than statement one, but if both Table1.code and Table2.code are indexed,
The effect may not be obvious.

Use of SET EXPLAIN on
"SET EXPLAIN on" is a SQL command that produces a file sqexplain.out that contains diagnostic information for the SQL command, and ' cost ' indicates the estimated time of the command (sometimes with a large deviation).
Avoid using "OR"
Example: Where a = "B" OR a = "C" will be slower than where a in ("B", "C") also, sometimes using union will be more effective than or.
　

About Indexes
Indexes The fields on the joined and order by, and the fields involved in the WHERE clause can be indexed as much as possible
Use of AND and Bwtween
Cases:
WHERE datecol >= "This/date" and Datecol <= "That/date" will be more than
WHERE datecol between "this/date" and "that/date" slow
To establish a view on the Clac field
CREATE VIEW TST As
SELECT Ship_charge-totval cout
From orders WHERE ship_charge > 0;
should read
CREATE VIEW TST (cout) as
SELECT Ship_charge-totval
From orders WHERE ship_charge > 0;
How do I access data outside the current library?
In the online version, use the following statement
SELECT * FROM [email protected]:owner.tablename
Note: the current library and [email protected] must keep the log consistent, that is, there is a log or no log.
"@server" and "owner." is optional
Some online versions may also use synonymous names, as follows
Eg:create synonym owner1.table1 for [email protected]:owner2. Table2
Querying data from a table that is not in the primary table
Cases:
SQL1:
SELECT * from Invlin WHERE invlin.invoice not in
(SELECT Invmst.invoice from Invmst)
Optimized SQL2
SELECT invmst.*, Invlin.invoice inv_invoice
From Invmst, outer Invlin
WHERE Invmst.invoice = Invlin.invoice
into TEMP lone_invoices;
SELECT <whatever You need>
From Lone_invoices
WHERE (Inv_invoice is NULL)

How do I return a portion of the number of normal return records in a SELECT statement?
Cases:
SQL1:
SELECT FirstName, LastName, City, state
From Bigdatabase
WHERE state = "TX"
Using statements
SELECT FirstName, LastName, City, state
From Bigdatabase
WHERE state = "TX"
and Rowid= (Trunc (rowid/x) *x)
where x is the proportional 1/x of the query portion, attention can only be made to approximate proportions, and does not guarantee randomness.
How to create the same structure as a permanent table
Cases:
SQL1:
CREATE TEMP TABLE mytemp (Prodno like Product.prodno desc-like Product.desc)
SQL2:
SELECT Prodno, desc from product
WHERE ROWID =-1
INSERT into TEMP mytemp
Both methods are correct, which one do you choose?
How to change a sequence value
Cases:
Change the starting value of the automatically generated sequence value (changed from a small sequence value to a large sequence value):
ALTER TABLE tablename MODIFY (ser_col_name SERIAL (New_start_number))
The starting value for automatically generated sequence values is 1 (changed from a large sequence value to a small sequence value):
Change to 1 and then to the specified value&NBSP;
insert into tablename  (Serial_column)  VALUES   (2147483647); &NBSP;
insert into tablename  (Serial_column)  VALUES   (0);  -- back to 1 again! &NBSP;
Alter table tablename modify ( ser_col_name  serial (new_start_number))  &NBSP;
Acceleration Select count (UNIQUE) ? &NBSP;
Example: &NBSP;
SQL1:  Select count (unique xxx)  ...  executed for about 30 minutes. &NBSP;
sql2: select unique xxx ... into  Temp aa; select count (*)  from aa;  performed for 7 minutes   &NBSP;