Common SQL statements in SQL Server

Document directory

• 1. Overview
• 2. query Overview
• 3. Single Table query
• 4. Connection Query
• 5. Related subqueries with exists
• 6. SQL collection operations
• 7. insert operations
• 8. delete operation
• 9. Modification Operation
• 10. Data Definition
• 11. View

1. Glossary

Cartesian Product, primary key, foreign key

Data Integrity

• Entity integrity: the primary attribute cannot be null. For example, the student ID and course number in the Course Selection table cannot be empty.
• Integrity of reference: The foreign key value in the table is null or the primary key in the reference table
• User-Defined Integrity: value range or non-empty restrictions, such as: gender (men and women), age (0-130)

　　

Table join

• Natural join: Compared with equijoin (a. id = B. id), the connected table only has one column id, not two columns (a. id and B. id ).
• Semi-join: Compared with equijoin (a. id = B. id), the connected table only has columns in Table A, and is displayed as a row by the "multiple matches" column in table B.
• Left Outer join: left join
• Outer right join: right join
• Full outer join: full join
• Inner join

SQL language composition

• DDL language: defines data and defines basic tables, views, and indexes;
• DML language: data manipulation, query, add, modify, and delete
• DCL Language: Permission

　　

2. query Overview

Queries include: Single-table queries, connection queries, related subqueries with exists, and set operations. Select... from common statement execution process

Select... ⑤ Projection from... ① Table → memory where... ② Select the tuples group... ③ Group having... ④ Select group [{union | ...} 6. select... ] ① ~ ⑤ Order... 7. Sorting output

3. Single Table query

A group by clause can appear in a select clause only when its attributes appear in the group by clause.

The order by clause is used to sort the query results in ascending/descending order based on the values of one or more columns. The Ascending order is ASC, And the descending order is desc. The null values are sorted as the maximum values.

Differences between having and where

• Where determines which tuples are selected for calculation and act on the tuples in the link.
• Having determines which groups meet the requirements and act on the groups

4. Connection Query

Connection queries include multi-Table connection queries, single-Table connection queries (self-join), external connection queries, and nested queries.

Connection condition 1

[Table Name 1.] column name 1 comparison operator [Table name 2.] column name 2

Connection condition 2

[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 connection condition is called the connection field, and the corresponding connection field should be comparable.

Execution Process: use the table scan method to find the first tuples in table 1, and then scan Table 2 from the beginning. Find the qualified tuples that are connected in series and saved to the result table; scan Table 2 again, and so on until the end of table 2. Take the second tuples from table 1 and repeat the preceding operations until all the tuples in Table 1 are processed.

4.1 single table join (self-join)

Use a table alias to define a table as two different tables for join.

For example, find the student ID that takes at least course 2 and course 4.

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, and full join.

4.3 nested Query

• In select... From... A select statement block can be embedded in the where clause of the where statement structure.
• Upper-level queries are called outer queries or parent queries, and lower-level queries are called inner-level queries or subqueries.
• SQL allows multiple nested queries
• The order by clause is not allowed in subqueries.
• Nested queries are implemented from the inside out, that is, subqueries are performed first, and then the results are used for parent queries as conditions.

4.3.1 subqueries that return a single value

For example, ask the Student name in the same department as "Liu Li", age

Method 1: select Sname, Sage from student where Sdept = (select sdept from student where Sname = "Liu Li"); Method 2: select FIRST. sname, FIRST. sage from Student FIRST, Student SECONDwhere FIRST. sdept = SECOND. sdept and second. sname = "Liu Li ";

4.3.2 subqueries that return a group of Values

For example, if you want to take the "C6" course and score more than 90 points

Method 1: select * from student where sno IN (select sno from SC where Cno = "C6" AND Grade> 90); method 2 (connection query): select student. * from student, SCwhere Student. sno = SC. sno AND Cno = "C6" AND Grade> 90;

For example, if the student is younger than a student in a computer system

Method 1: select * from student where sdept! = "CS" AND sage <ANY (select Sage from Student where Sdept = "CS"); Method 2: select * from Studentwhere Sdept! = 'Cs 'AND Sage <(select MAX (Sage) from Student where Sdept = "CS ");

4.3.3 multiple subqueries

For example, ask the name and salary of any employee in the D01 department that is the same as that of 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 = ""));

For example, an employee whose salary is between "Zhang San" and "Li Si"

Select * from teacherwhere Salary> = (select MIN (Salary) from teacher where Tname IN ("Zhang San", "Li Si") AND Salary <= (select MAX (Salary) from teacher where Tname IN ("Zhang San", "Li Si ");

4.3.4 use subqueries in the from statement to define the table name and column name for the query results

For example, calculate the student ID and average score with an average score of over 80.

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), which defines the table name (RA) and column name (Sno, avg_G) for the query)

5. Related subqueries with exists

• Unrelated subquery: The subquery condition does not depend on the parent query that is called irrelevant subquery.
• Related subquery: the query condition of a subquery depends on a property value of an outer parent query called a related subquery. A subquery with exists is a related subquery.
• Exists indicates that there are quantifiers. subqueries with exists do not return data of any records, but only return the logical values "True" or "False"

For example, ask for the names of all students who have taken the C1 course.

Unrelated subquery: select Sname from student where sno IN (select sno from SC where Cno = "C1 "); select Sname from studentwhere exists (select * from SC where student. sno = SC. sno AND Cno = "C1 ");

Related subquery execution process: first obtain the first tuples (Records) of the student table in the outer query, and use the relevant attribute values of the record (given in the inner layer where clause) processing the inner query. If the where clause of the outer layer returns the 'true' value, this tuple is sent to the result table. Then take the next tuple. Repeat the above process until all the records in the outer table are traversed once.

• If you do not care about the specific content of the subquery, select *
• Exists + subquery is used to determine whether the subquery returns tuples.
• If the result set of the subquery is not empty, exists is "True". If the result set of the subquery is empty, exists is "False ".
• Not exists: If the subquery result is null, "TRUE" is returned; otherwise, "FALSE" is returned"

Example: query the names of students who have selected all courses (continued)

select Snamefrom studentwhere not exists (    select *     from Course    where not exists (        select * from SC where         student.sno=SC.sno AND Course.Cno=SC.Cno    ));

For example, query the names of students who have selected at least 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 and type of attributes must be consistent.
• Attribute names can be inconsistent. The attribute names of the first result are used in the final result set.
• By default, duplicate tuples are automatically removed unless explicitly stated ALL
• Order by is placed at the end of the entire statement.

6.1 "Parallel" operation. For example, the system queries students of the computer department or students not older than 19 years old and sorts them by age in descending order.

select * from student where Sdept="CS"UNIONselect * from student where AGE <= 19order by AGE desc

6.2 "Submit" operation. For example, query students of the computer department whose age is not more than 19 years old and sort by age in descending order.

(select * from student where Sdept = "CS") INTERSECT(select * from student where AGE <= 19)order by AGE desc

6.3 "poor" operation, for example: query the students of Elective Course 1 but not elective course 2.

select Sname, Sdeptfrom studentwhere sno IN (    (select sno from SC where Cno="1")    EXCEPT    (select sno from SC where Cno="2")) 

7. insert operations

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

Insert all column values of a known tuples

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

Insert partial column values of a known tuples

insert into SC(Sno，Cno) values ("2003001", "C003");

Insert the subquery result example: Set the relation S_G (Sno, avg_G), and store the student ID and average score of boys with an average score greater than 80 into 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 having avg (GRADE)> 80 );

8. delete operation

Format: delete from table name [where condition];

• You can only operate on the entire tuples. You cannot only delete values in certain attributes.
• Only one link (table) can be used. To delete tuples from multiple links (tables), you must run the DELETE command on each link.

9. Modification Operation

The update statement can operate only 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];

For example, if the job type is SALESMEN, the employee's salary is changed to 110% of the average job wage.

update EMPLOYEEset Salary = (select 1.1 * avg(Salary) from EMPLOYEE where JOB="SALESMEN")where JOB="SALESMEN"; 

For example, increase the age of all students to 1 year.

update student set Sage=Sage+1; 

10. Create a curriculum for Data Definition

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 unique indexes of clustered Indexes

• You cannot create a UNIQUE index for Attribute columns with duplicate values.
• After a UNIQUE index is created for a column, the DBMS automatically checks whether the new record has a duplicate value in the column when a new record is inserted. This is equivalent to adding a UNIQUE constraint.

create UNIQUE INDEX  Stusno ON Student(Sno ASC);

Clustered Index

After a clustering index is created, data in the base table must also be stored in ascending or descending order based on the specified clustering attribute values. That is, the index order of the clustered index is consistent with the physical order recorded in the table.

create CLUSTER INDEX Stusname ON Student(Sname);

Create a clustered index on the Sname column of the Student table, and the records in the Student table will be stored in ascending order of the Sname value. Create clustered index in SQL server. Some DMBS do not support clustered indexes, so you must check the instructions before using them.

• You can create at most one clustered index on a basic table.
• Usage of clustered indexes: for some types of queries, query efficiency can be improved.
• Applicable scope of clustered indexes: The base table is rarely added or deleted, and the variable-length columns are rarely modified.

Delete Index

When an index is deleted, the system deletes the description of the index from the data dictionary.

Drop index [Table name.] <INDEX Name>;

Example: Delete the Stusname index of the Student table

DROP INDEX Student.Stusname;

11. View

Example: Create a view for e-commerce students

create view ec_studentas select sno, sname, age from student where dept="ec"

Delete View

Drop view <VIEW Name>

After a VIEW is deleted, other views exported from this VIEW will also become invalid. you should delete them one by one using the drop view statement.