"Notes" Database Fundamentals 04

Some basic commands and small exercises for the database

Exercise 1 Eg:

There is a table t_scores, record the results of the match

Date Name Score

2008-8-8 Bayern wins

2008-8-9 Wizards wins

2008-8-9 Lakers win

2008-8-10 Bayern Munich negative

2008-8-8 Bayern Munich negative

2008-8-12 Wizards wins

Required output The following format < note: Precede the Chinese string with n, e.g. n ' Win ' >

Name Winner

Bayern 1 2

Lakers 1 0

Wizards 2 0

Code: 1:

< step-by-step realization >

Select Name,
(
Case Score
When N ' wins ' then 1
else 0
End
) as WINS,
(
Case Score
When N ' negative ' then 1
else 0
End
) as negative
From Scores

Code: 2

Select Name,
SUM (
Case Score
When N ' wins ' then 1
else 0
End
) as WINS,
SUM (
Case Score
When N ' negative ' then 1
else 0
End
) as negative
From Scores
GROUP BY Name

< Implement the required output, as shown in code 2 above. >

Index Learning <index>

Full table Scan: Retrieval of data (select) The least efficient is a full-table scan, one for all.

* * Directory is the index.

* * How indexes are created

----Table-Right--Create an index---add--Select the column in the column that the index contains--OK.

* * Using an index can improve query efficiency but it takes up space, and adding, updating, deleting data requires synchronizing the index, so

Will slow down. Indexes are created only on fields that are frequently retrieved.

(*) Even if an index is created, a full table scan is sometimes required, such as like, function, type conversion, and so on.

Table Connection < join>

Join usage:

There are mainly inner joins and Outer joins:

Most commonly used (the default is inner):

Select < fields to select > from < primary table >

<join mode > < secondary tables > [on <join Rules;]

The main spirit of Inner Join is exclusive, call it exclusivity! That is, the data that does not match the Join rules will be excluded, such as the Supplier Code (SUPPLIERID) of a product that does not appear in the Suppliers table, and this record will be ruled out.

Outer Join:

Select < fields to query > from <left tables >

<left | right> [Outer] Join <right datasheet > on <join rules >

The syntax of Outer can be omitted, for example you can use a LEFT join or right join, in essence, Outer join is inclusive, let it be inclusive! Unlike Inner join's exclusivity, so in Lef The query result of the T Outer join will contain all the data from the left table, and the query of Right Outer join will contain all the data from the left data table in reverse.

Sub-query

A query statement is used as a result set for other SQL statements, just as a query statement that is treated as a result set is called a subquery, as with a normal table. < You can use a subquery in place of a table, instead of >

SELECT * FROM

(SELECT * from Student where age>20)

As name

< A subquery for a column of data can be treated as a single-valued subquery only if it returns and returns only one row >

Select Rom_number () over (order by age DESC) as Romnum

Name,age,chinese,math from Student

<rom_number> cannot be applied in a query in a where statement.

This stage of the database read, and I also mastered a lot of database query, build table, update, delete and other statements, the following should be the study of ADO, this is to connect vs and the database, to achieve the interaction between them. Their understanding of the database is only in the initial stage, there is more knowledge waiting for me to learn, dare not play in the unbridled, to refuel, for me to become a great God earlier.

"Notes" Database Fundamentals 04