SQL Server Common statements

• 1. Overview
• 2. Query overview
• 3. Single-Table query
• 4. Connection Query
• 5. Related sub-query with exists
• 6. SQL Collection Operations
• 7. Insert operation
• 8. Delete operation
• 9, modify the operation
• 10. Data definition
• 11. View

1. General terms

Cartesian product, primary key, foreign key

Data integrity

• Entity integrity: The primary attribute cannot be null, for example, the school number and course number cannot be empty
• Referential integrity: The foreign key value in the table is empty or the primary key in the reference table
• User-defined integrity: range of values or non-null limits, for example: gender (male and female), age (0-130)

　　

Table Connection

• Natural connection: The connected table has only one column ID, not two columns (a.id and b.id) compared to the equivalent connection (a.id=b.id).
• Semi-connected: compared to the equivalent connection (a.id=b.id), the connected table has only the columns of table A, and the "multiple matches" column of table B is displayed as a row.
• Left OUTER join: Right Join
• Right outer join: Starboard Join
• All-out connection: Full join
• Full internal connection: inner JOIN

The composition of the SQL language

• DDL language: Data definition, defining basic tables, views, indexes;
• DML language: Data manipulation, querying, adding, modifying, deleting
• DCL Language: Permissions

　　

2. Query overview

The query includes: Single table query, connection query, correlated subquery with exists, set operation four. Select...from Common statement execution procedures

Select ...            ⑤    projection from ...              ①    table→ memory where ...             ②    Select tuple Group             ... ③    Group having ...            ④    Select group [{union| ...}         ⑥    Search Sacrifice Results Collection      operation Select ...  ]         ①~⑤order by ...          ⑦    Sort Output

3. Single-Table query

Group by only attributes that appear in the GROUP BY clause can appear in the SELECT clause.

Use the ORDER BY clause to arrange the output of the result of the query in ascending/descending order of the values of one or more columns, ASC in descending order, and the null value as the maximum value.

The difference between having and where

• where determines which tuples are selected to participate in the operation, and is used for tuples in the relationship
• Having to decide which groupings meet the requirements for grouping

4. Connection Query

Connection query includes: Multi-table connection query, single table connection query (self-connected), outer join query, nested query 4 kinds

Connection Condition One

[table name 1.] Column Name 1 comparison operator  [table Name 2.] Column Name 2

Connection Condition Two

[table name 1.] Column Name 1  between [table Name 2.] Column name 2 and [table Name 2.] Column Name 3

The column name in the join condition is called the join field, and the corresponding connection field should be comparable.

Execution procedure: Using the method of table scan, find the first tuple in table 1, then scan table 2 from the beginning, find the tuple that satisfies the condition to be threaded and deposit in the result table, then continue to scan table 2, and so on, until the end of table 2. Then take the second tuple from table 1, repeating the above operations until all the tuples in table 1 have finished processing.

4.1 Single-table connection (self-connected)

Use a table alias to define a table as two different tables to connect to.

Example: Find the number of students who have at least elective courses 2nd and 4th

Select First.snofrom SC as first, SC as SECOND where first. Sno=second. Sno and first.cno= ' s2 ' and second.cno= ' 4 '

4.2 External Connection Query

Outer JOIN queries include: Left JOIN, right join, full join

4.3 Nested queries

• A SELECT statement block can be embedded in the WHERE clause of the SELECT ... where statement structure
• Its top-level query is called an outer query or a parent query, and its underlying query is called an inner query or subquery
• SQL language allows multiple nested queries
• The ORDER BY clause is not allowed in subqueries
• The implementation of nested queries is generally from the inside out, that is, the subquery first, and then use the results of the parent query as a condition

4.3.1 subquery that returns a single value

Example: The student name of the same department as "Liu Li", age

Method One: Select Sname, Sage from student where sdept = (select sdept from student where  Sname = "Liu Li"); method two: Select first. Sname, first. Sage from Student First, Student Secondwhere first. Sdept = SECOND. Sdept and SECOND. Sname = "Liu Li";

4.3.2 A subquery that returns a set of values

Example: Students who have enrolled in the "C6" course and have scored more than 90 points

Method One: SELECT * from student where Sno in (select Sno from SC where cno= "C6" and grade>90); method Two (connection query): Select Student.*from Student,scwhere STUDENT.SNO=SC. Sno and cno= "C6" and grade>90;

An example of a student who is younger than a student in a computer system

Method One: SELECT * from student where    sdept!= "CS" and      Sage < No (select Sage from student where sdept= "CS"); Method Two: Sele CT  *from studentwhere      sdept!= ' cs ' and     Sage < (select MAX (Sage) from Student where sdept= "CS");

4.3.3 Multiple subqueries

Example: Seeking the name and salary of employees in the D01 department with the same salary as any employee in the International Trade department

Table structure: Teacher (TNO, Tname, salary, DNO) Department (DNO, dname) query statement: Select Tname,salaryfrom teacherwhere     dno = "D01" and     Salary in (        Select salary from  teacher        where Dno = (select Dno from department where  dname= "Guomao") c11/>);

Example: to seek wages between "Zhang San" and "Li" two employees

Select *from teacherwhere    Salary >= (select MIN (Salary) from teacher where Tname in ("Zhang San", "Company")) and      Salary &L t;= (select MAX (Salary) from teacher where Tname in ("Zhang San", "Si");

4.3.4 using subqueries in the FROM statement to define table names and column names for the query results

Example: To find the average score of more than 80 points of the school number and average score

Select Sno, Avg_gfrom (select Sno, AVG (Grade) from SC Group by Sno) as RA (Sno, Avg_g) where Avg_g > 80;

As RA (Sno, Avg_g), for query as definition table name (RA) and column name (Sno, Avg_g)

5. Related sub-query with exists

• Unrelated subqueries: Query criteria for subqueries are not dependent on parent queries called unrelated subqueries.
• Correlated subquery: A subquery's query condition relies on a property value of an outer parent query called a correlated subquery, and a subquery with exists is a correlated subquery
• exists represents the existence of quantifiers, subqueries with exists do not return any recorded data, only return logical value "True" or "False"

Example: All students who have elective "C1" courses are asked to name them.

Unrelated subqueries:    select Sname from student where Sno in (select Sno from SC where Cno = "C1"); related subqueries Select Sname from student where exists (SELECT * from SC where student.sno=sc.sno and Cno = "C1");

Related subquery Execution procedure: first the first tuple (record) of the student table is taken in the outer query, and the inner query is processed with the related attribute value of the record (given in the in-layer where clause), and if the outer WHERE clause returns a ' TRUE ' value, then this tuple is fed into the resulting table. Then remove a tuple and repeat the process until the record of the outer table is traversed all at once.

• Do not care about the specific content of the subquery, so use SELECT *
• The EXISTS + subquery is used to determine whether the subquery returns a tuple
• exists is "True" when the result set of the subquery is not empty, and exists is "False" when the result set of the subquery is empty.
• NOT exists: Returns a "TRUE" value if the subquery result is empty, otherwise returns "FALSE"

Example: Query the names of students who have enrolled in all courses (cont.)

Select Snamefrom studentwhere NOT EXISTS (    select * from     Course    where isn't exists (        select * from SC where< C5/>student.sno=sc.sno and COURSE.CNO=SC. Cno    ));

Example: The query takes at least the student name of all the courses selected by S1

Select Snamefrom studentwhere NOT EXISTS (    select * from    SC SCX    where         scx.sno= ' s1 ' and not         exists (            SELECT * from            SC SCY            where Student.sno=scy.sno and SCX. Cno=scy. Cno        ));

6. SQL Collection Operations

• The number of attributes must be the same, the corresponding type must be consistent
• Property names can be inconsistent, and the final result set takes the property name of the first result
• The default is to automatically remove duplicate tuples unless you explicitly describe all
• Order BY is placed at the end of the entire statement

6.1 "and" operation, for example: Query Computer department students or students older than 19 years old, and sorted by age.

SELECT * FROM student where sdept= ' CS ' unionselect * from student where age <= 19order by age desc

6.2 "intersection" operation, for example: Query the computer department students and older than 19 years old students, and sorted by age.

(SELECT * from student where sdept = "CS") INTERSECT (SELECT * from student where Age <=) Order BY age Desc

6.3 "Poor" operation, for example: Query elective course 1 but no electives 2 students.

Select Sname, Sdeptfrom studentwhere Sno in (    (select Sno from SC where cno= "1")    EXCEPT    

7. Insert operation

Format: Insert  into  table name [(column name 1,...)]  Values  (column value 1,...)

Insert all column values for a well-known tuple

INSERT into student values ("2003001", "Chen", 18, "male", "e-commerce", "School of Management", "Xuzhou");

Insert a partial column value of a well-known tuple

Insert into SC (SNO,CNO) VALUES ("2003001", "C003");

Insert Subquery Results Example: Set the relationship s_g (sno,avg_g), the average score of more than 80 male students and average scores in the S_g

Insert into S_g (sno,avg_g) (    Select Sno, avg (GRADE) from    SC    where Sno in (select Sno from Student where sex= "male ")    group  by  Sno    have  avg (GRADE) > 80);

8. Delete operation

Format: Delete from table name [where condition];

• You can only operate on an entire tuple, and you cannot delete only values on certain properties
• can only work on one relationship (table), to remove tuples from multiple relationships (tables), you must perform a delete command on each relationship separately

9, modify the operation

The UPDATE statement can only manipulate one table at a time.

Format 1:update table name [alias]set column name = expression, ... [Where condition]; format 2:update table name [alias]set (column name, ...) = (subquery) [Where condition];

Example: The wages of workers for salesmen are changed to 110% of the average wage of the trades

Example: Increase the age of all students by 1 years

10. Data definition Create Curriculum

CREATE TABLE SC (    Sno char (6) NOT NULL,    Cno char (6) NOT NULL,    Grade smallint default NULL) primary KEY (Sno,cno ) foreign key (SNO) references student (SNO) foreign KEY (Cno) references Course (Cno) Check (Grade between 0 and 100);

Common indexes: Unique indexes and clustered index unique indexes

• Cannot create a unique index for an attribute column that already contains duplicate values
• After a unique index is created on a column, the DBMS automatically checks to see if the new record has duplicate values on the column when the new record is inserted. This equates to the addition of a unique constraint

Create UNIQUE INDEX  stusno on Student (Sno ASC);

Clustered index

After the clustered index is established, the data in the base table also needs to be stored in ascending or descending order of the specified clustered attribute value. That is, the index entry order of the clustered index is consistent with the physical order of the records in the table

Create CLUSTER INDEX stusname on Student (Sname);

A clustered index is established on the Sname (name) column of the student table, and the records in the student table are stored in ascending order of the sname values. SQL Server is represented in Create clustered index. Some dmbs do not support clustered indexes, so be sure to check the instructions before using them.

• A maximum of one clustered index can be established on a base table
• Use of clustered indexes: for some types of queries, you can improve query efficiency
• The scope of application of clustered indexes: Few additions and deletions to the base table; few modifications to the variable length column

Delete Index

When you delete an index, a description of the index is deleted from the data dictionary.

DROP INDEX [table name.] < index name >;

Example: Deleting the stusname index of a student table

DROP INDEX Student.stusname;

11. View

Example: Building a view of students in e-Commerce Department

Create VIEW Ec_studentas Select Sno, Sname, age from student where dept= "EC"

Delete a view

DROP View < view name >

When a view is deleted, other views exported by this view will also be invalidated, and users should delete them by using the Drop VIEW statement

SQL Server Common statements