[Note] database basics 04 and note database 04

[Note] database basics 04 and note database 04
Some basic commands and exercises of the database

Exercise 1 eg:

There is a table T_Scores, which records the Competition Results

Date Name Score

Bayern wins

-9 qicai wins

Lakers victory

-10 Bayern

Bayern

-12 qicai wins

Output the following format: <Note: add N before the Chinese string, for example, N 'sheng'>

Name wins/loses

Bayern 1 2

Lakers 1 0

Qicai 2 0

Code: 1:

<Step by Step>

Select Name,
(
Case Score
When N 'sheng' then 1
Else 0
End
) As wins,
(
Case Score
When n'ned' then 1
Else 0
End
) As negative
From Scores

Code: 2

Select Name,
SUM (
Case Score
When N 'sheng' then 1
Else 0
End
) As wins,
SUM (
Case Score
When n'ned' then 1
Else 0
End
) As negative
From Scores
Group by Name

<Output of implementation requirements, as shown in Code 2 above.>

Index learning <index>

Full table scan: the most efficient data retrieval (select) method is full table scan.

** A directory is an index.

** How to create an index

---- Table-right-click-create index-add-select the columns included in the index in the column-OK.

** Using indexes can improve query efficiency but occupy space. You must synchronize indexes when adding, updating, and deleting data. Therefore

Will speed down. Create an index only on frequently retrieved fields.

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

Table connection <Join>

Join usage:

Inner Join and Outer Join:

The most common (Inner by default ):

Select <field to be selected> From <main data table>

<Join method> <secondary data table> [On <Join rule>]

The main spirit of Inner Join is exclusive. Call it exclusive! That is, materials that do not match the Join rule will be excluded. For example, the supplier code of a Product (SupplierId) in the Product is not displayed in the Suppliers data table, this record will be excluded.

Outer Join:

Select <field to be queried> From <Left table>

<Left | Right> [Outer] Join <Right table> On <Join rule>

Outer In the syntax can be omitted. For example, you can use Left Join or Right Join. In essence, Outer Join is inclusive! Different from the exclusive nature of Inner Join, the query results in Left Outer Join will contain information about all Left tables. If you look back, Right Outer Join queries will contain information about all Right tables.

Subquery

A query statement is used as a result set for other SQL statements. Like a common table, a query statement used as a result set is called a subquery. <all tables can be replaced by subqueries>

Select * from

(Select * from Student where Age> 20)

As name 

<The subquery of a column of data can be regarded as a single-value subquery only when only one row is returned.>

Select rom_number () over (order by Age DESC) as romnum

Name, Age, Chinese, Math from Student

<Rom_number> cannot be applied to Where statement queries.

* ******* After reading the database at this stage, I have mastered a lot of database query, table creation, update, deletion, and other statements. The following should be ADO. NET learning, this is to connect vs and the database to achieve interaction between the two. My understanding of the database is still in its infancy, and more knowledge is waiting for me to learn and dare not play it freely. I want to cheer up and become a great God for me.