Transferred from: http://huangliangfeixu.blog.163.com/blog/static/18974706220082240923909/
One: SQL bisic
1:sql (structured quary Language) features:
A: Standardization
B: Non-procedural
C: Can be optimized
D: For collection operations
Data types in the 2:ase
A:numberic
B:character
C:date/time
D:lobs
3:convert (varchar, textColumn), if varchar (n) n is not specified then the default is 30
The role of 4:where in SQL
A: Filtering data
B: Make Table connection (SQL92 before)
C: Select Index
The difference between 5:whare and having
The WHERE statement inserts the filtered data into the work table
The Having statement filters the data from the work table to get the final result.
6: Order of execution of a SELECT statement
A:from clause
B:where clause
C:group BY clause
D:select clause
e:having clause
F:order BY clause
7:union VS Union All
A:union will sort two result sets and remove duplicate elements (inefficient, easy to use)
B:union all simply merges two result sets without sorting or removing duplicate elements (good efficiency)
Two: Index and query Parameters
There are three ways to access data in 1:ase
A:clustered Index
B:nonclustered Index
C:table Scan
2:covered Query
A covered query simply gets the data from the index, without having to scan the database tables, which is the quickest way to query the data.
Limit 1: Only valid in Selece
Limit 2: All referenced columns must be in the same nonclustered index
3:functional Index
is supported after ASE15.0, that is, in previous versions of ASE15.0, the following statements are not indexed
SQL code
Select Column1 from table1 where upper (column2) = ' ivanl '
4: How to view the execution plan
SQL code
Set SHOWPLAN on Go your SQL Go Set Showplan off go
5: How to view IO
SQL code
Set STATISTICS IO on SET STATISTICS time on go SQL Go Set STATISTICS IO off SET STATISTICS time off go
6: Suggestions for using index
A: Index with fields that are often used in the WHERE statement
B: The fewer fields that are included in index, the better
C:drop off the useless index
Three: Table Connection
1: What is a table connection
Table joins are querying data from multiple tables, or fetching data multiple times from a table.
(a join is a transanct-sql operation than access rows from Multi-tables or from a single talbe multi-times)
2: Category of Table connections
A:inner Join
B:outer Join
C:cross join (full join)
Full join is not supported in 3:ase but can be simulated by union
SQL code
Select T1.COLU1, t2.column2 from T1, T2 where t1.id *= t2.id Union Select T1.COLU1, t2.column2 from T1, T2 whe Re t1.id =* T2.id
(not recommended for use, poor efficiency)
4:ase supports up to 50 tables in table connection, ASE query optimizer does not do very well, Sybase recommended join Table no more than 4 (-_-~!)
5: There are three ways to implement table joins in a database
A:nested Loop Join
B:merge Join
C:hash Join
(You can use show plan to see what kind of join the database chooses to implement the join statement)
6: Recommendations for table joins:
A: Using Showplan to see the use of the type of join
B: Add index to the join column
C: The join of multiple tables is divided into several small table joins
D: Avoid the production of Cartesian product
Four: Use Case statement
Two forms of the 1:case statement
SQL code
A:case when search_condition then expression [when search_condition then expression] [else exproestion] E nd b:case expression When expression then expression [when exproession then expression] [else expression] End
Use of 2:case
A:decoding column
SQL code
Select cust_id, cust_name case cust_type when ' R ' and ' Relation ' when ' I ' and ' international ' when ' s ' N ' Small ' else ' other ' end as Customer_type
b:conditionally displaying columns or values
SQL code
Select title_id, Total_sales, case when Total_sales > "hight" When Total_sales < "low" Else ' End as ' column '
C:horizontal frequency table and summary calculation
SQL code
Select sum (case type "ADV" then 1 else 0 end) as ADV, sum (case type "cus ' then 1 else 0 end" as Cus from Customer
d:updating on variable conditions
SQL code
Update customer set cust_charge = cust_charte + case cust_ type when ' d ' then 1 when ' C ' then 2 when ' E ' then 3 else 0 end [/code] e:rules and check constraints [code] create table cust_order_info ( order_num Int, order_taker int, order_date char (7) default case when datepart (Dw, getdate ()) between 2 and 6 then ' weekday ' else ' Weekend ' end )
Five: Transactions and Locks
There are two kinds of transaction modes in 1:ase
a:chained Mode
b:unchained Mode (Sybase default)
Unchained mode shows the start of a transaction, chained implicitly begins a transaction
Unchained mode uses ' Commint tran ', ' rollback tran '
Chained mode uses ' commint work ', ' rollback working '
Unchained mode supports nested transactions, chained mode does not support
2:locking schema
A:all pages table, will lock data and index as they is accessed (can have clustered index)
B:A datapages table would lock datpages as they is accessed, index would not is locked (no clustered index)
C:a DataRow table would lock datpages as they is accessed, index would not is locked (no clustered index)
3:locking type
The three most important lock types in the ASE are
A:shared locks (SELECT, fetch)
B:update locks (Fetch, UPDATE, DELETE)
C:exclusive locks (INSERT, UPDATE, DELETE)
4: Isolation Level
A total of four isolation levels in the ASE
A:isolation Level 0 (read uncommited), allow expansion
B:isolation Level 1 (read comminted) (ASE DEFAULT), not allowed to read
C:isolation Level 2 (Repeatable read), Repeatable read
D:isolation Level 3 (SERIALIZABLE), Phantom Read not allowed
SQL code
Set TRANSACTION ISOLATION level {0|1|2|3} or select ... at isolation {0|1|2|3}
5: How to write an efficient transaction
For OLTP transaction
A: Make the transaction as short as possible
B: Use index to randomly access data
C: Use transaction only when necessary
D: Choose the appropriate lock type and isolation level
E: Use optimistic lock
VI: Data processing
1: divided by 0
Using COALESCE () and Nullif ()
Use Nullif () to convert 0 to NULL before using coalesce () to handle null
SQL code
Select COALESCE (Total_sales/nullif (sales,0), 0)
--COALESCE (Ex1, ex2,ex3 ...) Returns the first expression that is not a null
--Nullif (Expre, value) if expre=value, returns null
2: Duplicate data found
SQL code
Select Type, COUNT (*) from table where: Group By type has count (*) > 1
3: Find the most repeated data
SQL code
Select Type, COUNT (*) from table where: Group By type has count (*) = MAX (count (*))
4: Data accumulation
Java code
Select t1.title_id, T1.advice, sum (t2.advice) as cumulative_total from title T1, title T2 where t1.title_id >= t2.t ITLE_ID GROUP by t1.title_id, T1.advice
5:ranking data
SQL code
Select rank = identity (ten), title_id, total_sales into #top from the titles where: ORDER BY total_sales desc GO select * from #top go drop table #top go
6:conver between Julian Date and Gregorian date
SQL code
Select DatePart (yy, @date) *1000+datepart (dy, @date) as Julina_date select DATEADD (DD, juliandate%1000, ' 12/31/' +convert (char (4), juliandate/1000-1)) As Gregorian_date
7: Calculate how many days this month
SQL code
DATEPART (DD, DATEADD (dd,-1--last Day of this month Datead (mm,1--add a month DATEADD (DD --, 1-datepart (Dd,getdate ()--1-today getdate ())))--get today
8: Whether it is a leap year
SQL code
Select datepart (dy, ' 03/01/' | | Convert (char (4), DatePart (Yy,getdate ())))--= 61 is a leap year--= 60 is not a leap year
"Go" SQL Power boost
