Optimizing the MySchool Database (iv)

Java training, Android training, iOS training,. NET training, look forward to communicating with you!

Problems encountered with the "Unable to attach database" procedure:

1, the data file itself, with access rights restrictions

----Select the folder where the database files are located----> right-click menu (Properties)----> Security--->user user Group (give Full Control permission)

2. Database version compatibility issues

----database separation is invalid, the entire database (structure + data) is exported to SQL

Select the running database----> Right-click menu (Task-build script)---> Advanced settings----> Set script type to write to schema and data

The so-called "sub-query":

----Other SQL statements that "query statements" are embedded in.

----The responsibility of "variables", you can consider using queries to replace them directly.

---assignment statements

DECLARE @age int

Set @age = (subquery)

Print @age

---The logical statement

DECLARE @i int

Set @i= 18

If @i < (sub-query)

print ' successfully submitted! ‘

Else

print ' failed '

---The other SELECT statements

SELECT * FROM [Admin]

where Len (LOGINPWD) < (subquery)

Note

Subqueries can also use the parent query-related data, in addition to the results of their own independent implementation of the query

Notes in "Multi-Table Search":

1, the main table, should be all forms, the most interrelated table (the table with the most primary foreign key relationship with the other table)

2, in the connection order of inner join, follow the "connection transfer principle", the table to be joined, must be connected with the inner join table, has a direct primary foreign key relationship

"Multi-table check" and "sub-query" interoperability:

1. All "multi-table" can be replaced by "sub-query"

2. But not all "subqueries" can be replaced by "multi-Table Search"

3. Application Scenario:

When the data for select is from multiple forms, it is appropriate to use multi-table search

When the Where condition comes from multiple forms, it is appropriate to use the subquery

Connection symbols for "subquery" and "Parent query":

>, >=, <, <=, =,! =	Subquery: Only one result (one row) can be returned
Inch	Subquery: Returns multiple results (multiple rows and one column)
Exists	Subquery: can return any result

"Not in" app and "paged query":

---Query the entire table, and according to 10 per page split, take out the data on page 4th

declare @pageRowCount int, @page int

Set @pageRowCount = 10--Number of records per page

Set @page = 2-the page number you want

Select Top (@pageRowCount) * from temptable-

where ID not in (select Top ((@page-1) * @pageRowCount) ID from temptable)

Fourth Zhang Ziyi (query (last example)

Select should be to Number = (select COUNT (1) from Student),

Real to Number = (select COUNT (1) from Result

where Result.subjectno = (select Subjectno from Subject where subjectname = ' java ')

and result.examdate = (

Select Max (examdate) from Result

where Subjectno = (select Subjectno from Subject where subjectname = ' java ')

)

),

Number of missing tests = (select COUNT (1) from Student)-

(select COUNT (1) from Result

where Result.subjectno = (select Subjectno from Subject where subjectname = ' java ')

and result.examdate = (

Select Max (examdate) from Result

where Subjectno = (select Subjectno from Subject where subjectname = ' java ')

)

)

-----------------------------------------------------------------------------------------------------------

--Create a "table variable" to do temporary saving of data

Declare @tb table (name varchar (50), study number int, result varchar (10), whether through varchar (10))

INSERT INTO @tb

Select Student.studentname,

Student.studentno,

Case

When A.studentresult was null then ' missing '

Else cast (a.studentresult as varchar (10))

End

Case

When a.studentresult>=60 and then ' is '

Else ' no '

End

From Student

Left Join

(

SELECT * FROM Result

where Result.subjectid = (

Select Subjectid from Subject where subjectname = ' java '

)--java

and result.examdate= (

Select Max (examdate) from Result where Result.subjectid =

(select Subjectid from Subject where subjectname = ' java ')

)--Last time (Java)

) as A on student.studentno = A.studentno

---data query from a staging table

SELECT * FROM @tb

--Statistical results

Select Total Number = (select COUNT (1) from @tb),

by number = (select COUNT (1) from @tb where pass = ' yes '),

Pass rate = CAST (((select COUNT (1) from @tb where is ' is ')/cast ((select COUNT (1) from @tb) as float) * as varchar (20)) + '% '

Optimizing the MySchool Database (iv)