1. Once a INSERT, SELECT INTO, or a large-capacity copy statement is completed, the last identity value generated by this statement is included in the @@ identity. If this statement does not affect any table with identity, @@ identity returns NULL. If multiple rows are inserted, multiple identifier values are generated, and @@ identity returns the final identification value. If this statement excited one or more triggers that perform an insertion of the identifier value, then the statement is executed immediately after the @@ identity will return the final identifier value generated by the trigger. If the INSERT or SELECT INTO statement fails or a large-capacity copy failed, or the transaction is rolled back, the @@ iDENTITY value will not be restored to the previous settings.
On the last value of the @@ identity column inserted into the table, @@ identity, scope_identity and Ident_current functions are similar.
@@ identity and scope_identity will return the last identification value generated in all tables of the current session. However, scope_identity is only returned in the current scope, and @@ identity is not limited to a specific scope.
2, go to the heavy record
Is there a unique ID in your table?
This can be implemented if there is.
SELECT * FROM Table Where ID in (SELECT MIN (ID) from FROM Table Group By TitleName)
This will be achieved! ! !
3. Query the random finite record in the database
Random 5:
Access:
Select Top 5 * from Tablename Order By RND (ID)
SQL:
Select Top 5 * from Tablename Order by NewID ()
4,
I. Table Structure Definition: Create Table Student (Sno C (8), Sname C (10), Birthday D, Age I, Merried L)
2. Table structure modification:
Add field: ALTER TABLE Student Add Column Telphone C (20)
Modify Field Type: ALTER TABLE Student ALTER Column Name C (20)
3. Create a view: Create View S_View As Select Student.sno, Sname, CName, Grade from Student, Sc, Course
Where student.sno = sc.sno and sc.cno = course.cno
Second, data manipulation (P70)
1. Data Insert: Insert Into Student (SNO, SNAME, SEX, AGE) VALUES ('S01', 'Zhang San', 'Men, 20)
2. Data Delete: Delete from student where sex = 'male'
3. Data modification: Update Student Set Age = 21 Where Sno = 'S01'
Third, authority and role
Grant Grant, recovered Revoke
Fourth, inquiry
Continuous table, selected, projection
Set function: min, max, avg, sum, count
Group GROUP BY Packet Conditions HAVING
Sub-inquiry: ordinary child inquiry, related subsis
Five, example
1. Query the students' students and names of students more than 85 points
SELECT Learner, Name from Student Where Student No. NOT IN (SELECT License)
2. Query the highest results
Select * from student where grades> = all (SELECT grades from sc) or select * from student where score = all (SELECT MAX) from SC
3. Query the supplier of supplies to engineering from J1 and J2 to engineering number J1 and J2
SELECT DISTINCT Supplier from SPJ
WHERE Work No. = 'J1' AND Supply Litex IN
(SELECT Supply Business number from spj where engineering number = 'j2')
4. Search at least the student number of the student number of S3 is the same student number and name
SELECT Learner, Name from Student
WHERE NOT EXISTS
(Select * from SC SCX
WHERE SCX. Learn = 's3' and not exists
(SELECT * from SC SCY
School number = student. Learn and SCY. Courses = SCX. Courses))
Ident_current is not limited by the limitations and sessions, and is limited to the specified table. Ident_current returns an identifier value generated for a particular table in any session and any scope.
