SQL Advanced-(2)

-- Table connection join--

-- Check the name, age and class of all students

-- using subqueries

Select Studentname,datediff (Yyyy,borndate,getdate ()), (select classname from grade where Classid=student.classid) from Student

-- using from multiple tables

Select Studentname,datediff (Yyyy,borndate,getdate ()), classname

From Student,grade

where Student.classid=grade. ClassId

-- using a multiple table join table INNER JOIN another table on How to establish an association ( A field that can be associated is typically a field that creates a primary foreign key relationship )

Select Studentname,datediff (Yyyy,borndate,getdate ()), classname

From Student

Join grade on Student.classid=grade. ClassId

--1. Internal connection: INNER JOIN. found two tables establish relational records for field values that are equal , If the field values are not equal , then abandon it .

-- Check student's name, age, class and grade

Select Student.studentname,datediff (Yyyy,student.borndate,getdate ()), Grade.classname,result.studentresult

From Student

Inner join grade on Student.classid=grade. ClassId

INNER JOIN Result on Student.studentno=result.studentno

where student. classid=2

-- Check the name of the exam subject each learner needs to attend

Select Student.studentno,student.studentname,subject.subjectname

From Student

INNER JOIN Result on Student.studentno=result.studentno

INNER JOIN Subject on Result.subjectid=subject.subjectid

Select Student.studentno,student.studentname,subject.subjectname

From Student

INNER JOIN Subject on Student.classid=subject.classid

SELECT * FROM Phonetype

INNER JOIN Phonenum on Phonenum.ptypeid=phonetype.ptid

-- left and right connection

-- left connection, you can get all the records of the left table, except that if the value of the field in the left table cannot find the corresponding associated record in the right table, then all the fields in the right table will be substituted with null values .

SELECT * from Phonetype LEFT join Phonenum on Phonenum.ptypeid=phonetype.ptid

-- Check the student's information without taking the exam

SELECT * FROM Student

Left join Result on Student.studentno=result.studentno

Where Result.studentno is null and Result.subjectid is NULL

-- Right connection :

SELECT * from Phonetype LEFT join Phonenum on Phonenum.ptypeid=phonetype.ptid

-- Cross connect :

SELECT * FROM Student

Cross Join Grade

-- suggestions for long table connections :

--1. Make sure the fields are from those tables , through the table . the way the fields are determined

--2. viewing These tables has some associations (fields that can establish a primary foreign key relationship), and if there is no association, consider whether you need to use an intermediate other table to correlate

--3. after a multi-table connection, you can get a virtual table that can be added to all fields of the table where conditions

-- Exercise 1: Find out the names, ages and grades of all English-passing students

Select Student.studentname,datediff (Yyyy,student.borndate,getdate ()), Result.studentresult

From Student

INNER JOIN Result on Student.studentno=result.studentno

INNER JOIN Subject on Result.subjectid=subject.subjectid

where subject.subjectname= ' office ' and result.studentresult>=60

-- Exercise 2: Check all the exams ( English score is not null) Student's name, age and score

- -Exercise 3: Find out the names, ages, and grades of all students ( participants and non -candidates ) If you do not take the test show missing Test , if less than the show failed office

Select Student.studentname,datediff (Yyyy,student.borndate,getdate ()),

Case

When Result.studentresult was null then ' no exams '

When result.studentresult>=60 then cast (Result.studentresult as CHAR (3))

Else ' less than lattice '

End

From Student

Left join Result on Student.studentno=result.studentno

INNER JOIN Subject on Result.subjectid=subject.subjectid

where subject.subjectname= ' office '

Types of---variables - Global Variables

Go

Insert into grade values (' Fdasfas ')

SELECT * FROM Student

SELECT @ @IDENTITY--- it gets the value not pointing to the previous statement, but the value of the most recent insert statement

Go

SELECT * FROM Student

SELECT @ @ROWCOUNT

Delete from Student where studentno=12433

SELECT @ @ROWCOUNT

Go

[Email protected] @ERROR get the most recent error number for this statement

SELECT * FROM Stu--incorrect syntax error number not getting

SELECT @ @ERROR-- pin for added deletion and modification , If there is an error then the error number must be greater than 0, if there is no error then it must be 0

Update Student set loginpwd= ' SS ' where studentno=1

Update Student set loginpwd= ' SS ' where studentno=1

SELECT * FROM Student

Go

SELECT @ @ERROR

---view

The essence of---view is a SQL statement --select. it represents a table of partial data or multiple tables of comprehensive data, the structure and data is based on the query of the table on the basis of

--The essence of a view is a virtual table: its operation is basically the same as the operating table.

-- The view does not actually store the data, it stores the SQL statements, and when the execution view gets the data, it is essentially executing the SQL statements in the view to get the data .

SELECT * FROM Student

SELECT * FROM Vw_getstudentresultinfo

-- Create a view from a command statement

-- Syntax:

--go

--create View vw_ custom name

--as

--Select command

--go

-- access to six-class student information

--1. could you write more than one ? Select query? I can only write one piece .

--2. The view can only write select, can not add any update/delete/insert statement

if exists (select * from sysobjects where name= ' vw_getstudentbyclassid_6 ')

Drop View Vw_getstudentbyclassid_6

Go

CREATE VIEW Vw_getstudentbyclassid_6

As

Select Top 2 * from Student ORDER by Studentname

Go

-- Query the view

SELECT * from Vw_getstudentbyclassid_6 ORDER by Studentname

-- changes to the view can be deleted on the view to add and modify operations, and the operation will directly affect the physical table, so generally do not set up to do these operations, because the nature of the views of the purpose is to query

Update vw_getstudentbyclassid_6 Set classhour = ten, subjectname= ' C # ', Classname= ' 111 ' where subjectid=2

Delete from Vw_getstudentbyclassid_6 where studentno=3

-- The addition of the view to delete and modify operations can only pin for a single table, if more than one table is involved, then the operation will not succeed

SELECT * FROM Vw_getstudentresultinfo

Delete from Vw_getstudentresultinfo where studentno=6

Update Vw_getstudentresultinfo set studentresult=55, Subjectname= ' C # ' where studentno=6

--create Proc[edure]

--procedure

-- Sub-query --

-- One query contains another query

-- sub-query as condition --

--1. contained in () the query that executes first is the subquery statement , which is called the parent query statement that contains the subquery.

--2. when a subquery is introduced, only one expression can be specified in the selection list , That means the subquery is a condition. ( with relational operators ) when a subquery is never able to appear in multiple columns

--3. the subquery returns more than one value. When subqueries follow in =,! =,<,<=,> ,>= , or a subquery is not allowed when used as an expression, It means that if the subquery follows the relational operator, You must ensure that the subquery returns only a single value , and if you really need to do this, you can use the keyword in/not in

-- search for information on students older than Lin Si

--4. that have subqueries SQL The columns returned by the statement are only related to the parent query, and the columns in the subquery are only judged as conditions

--1. Check out the age of Lynn first

Select Borndate from Student where Studentname= ' Lynn '

-- Returns a row of multi-column values ---- as conditional forever error

SELECT * from Student where borndate< (SELECT * from Student where studentname= ' Lynn ')

-- Returns a column of values for multiple rows

SELECT * from Student where borndate< (select Borndate from Student)

--Query The student information of class ID"=3 "

Select ClassID from grade where classid<=3

Select Studentno,studentname from Student where ClassId not in (select ClassId from grade where classid<=3)

-- Sub-query Gets the value of a column - - least used

--Check the student's office Exam results and test date , showing The student 's name

Select (select Studentname from student where studentno=10), studentresult,examdate from Result where studentno=10 and SUBJ Ectid= (select Subjectid from Subject where subjectname= ' office ')

--3. query to get a result set, you can use a subquery to return a single virtual table

-- Check the test scores of all students with class ID 1

--1 to find out the student number of class ID 1

Select Studentno from Student where classid=1

-- Check the students ' scores for the assigned student number

SELECT * from the Result where Studentno in (select Studentno from Student where classid=1)

-- pagination

Select Top 5 * from Student

Select Top 5 * from Student where Studentno not in (select Top 5 studentno from Student ORDER by Studentno)

--row_number can return a row number for each row of the query, which is similar to an identity column, never duplicates, and it is contiguous by default , and This function can add a new column to the result set

The--over description is sorted on that field because the result set is displayed differently by different fields

Select Row_number () over (order by Studentno) as id,* from Student

-- If the subquery is a result set, you must add an alias for it

SELECT * FROM (select Row_number () over (order by Studentno) as id,* from Student) as Temp

where Temp.id>=6 and id<=10

-- use row_number () over ( specify sort field ) to implement paging , which can add a new column to the result set

Select Row_number () over (order by Studentno) as id,* from Student

SELECT * FROM (select Row_number () over (order by Studentno) as id,* from Student

) as temp where id>0 and id<=5

--Check the students who are older than "Liao Yang" to show their information

SELECT * from Student where borndate< (select Borndate from Student where studentname= ' Liao Yang ')

-- enquire about the courses offered in class two

SELECT * from Subject where classid= (select ClassId from grade where Classname= ' two-period class ')

--Check thehighest score and lowest score for the most recentofficeExam Results

Select Subjectid from Subject where subjectname= ' office '

Select MAX (examdate) from the Result where subjectid= (select Subjectid from Subject where subjectname= ' office ')

Select Max (Studentresult), MIN (Studentresult) from Result where subjectid=

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

and examdate=

(select MAX (examdate) from Result where subjectid=

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

SELECT * FROM Result

SELECT * FROM Subject

-- classification of sub-queries :

--1. stand-alone sub-query : Sub-queries that can be run separately

--2. correlated subqueries: Columns in a parent query are referenced in a subquery

-- inquire about the students who took the exam

Select distinct Studentno from Result

SELECT * from Student where Studentno in (select distinct Studentno from Result)

SELECT * from Student where studentno= (select distinct Studentno from Result where Result.studentno=student.studentno)

Select Studentno from Result where studentno=33

SELECT * from Student where studentno= (select Studentno from Result where studentno=33)

SQL Advanced-(2)