SQL Statement Review
1.Data Definition Construct
1.1 Create table Construct
1.1.1 table creation statement
create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2) ); ----------------------------------- create table r ( A1 D1, A2 D2, ... An,Dn, (integrity-constraint_k) );
1.1.2 integrity constraint
(1)not null
Exclude null values from the domain of the property.
(2)primary key(A1,...,An)
The attributes in the attribute list constitute the main code, which contains two aspects of information:
- Non empty
- No two tuples have the same primary code
(3)foreign key(Am,...,An) references r
Indicates that any tuple in the relationship is in the attribute A m , . . . , A n A_m,...,A_n Am,..., The value on An ， must correspond to the value of a tuple in the relationship r on the main code
(4)Unique
Any two lines cannot be the same.
(5)Default
Give default value
(6)Check
create table department
( dept_name varchar(20),
building varchar(15),
budget decimal(12,2),
primary key(dept_name)
);
create table instructor
( ID char(5),
name varchar(20) not null,
dept_name varchar(20),
salary numeric(8,2),
gender varchar(6),
phone int(11) unique,
age int(3) default 30,
primary key(ID),
foreign key(dept_name) references department, // In the standard sql writing method, the attribute name needs to be listed after the department of mysql
check (gender in ("male", "female"))
);
1.2 Update to Table
1.2.1 add data insert
insert into department values('Biology', "Begonia", 8000);
1.2.2 data deletion
delete from instructor where ID = "00001"; delete from instructor;
1.2.3 delete table drop
drop table instructor;
1.2.4 change Alter
(1) New integrity constraint
alter table r add constraint e_name Foreign key(employee_name) references employee(employee);
(2) New attribute
alter table r add age int(3);
(3) Delete attribute
alter table r drop A;
2. Basic query structure
2.1 single relation query
2.1.1 select
select distinct dept_name from instructor; // Forced weight removal select all dept_name from instructor; // Force no weight removal select dept_name from instructor; // Default no de duplication select ID,name,dept_name, sal* 12 as 'year_sal' from instructor; // as usage select 'A' from instructor; // Returns A of the number of rows of the instructor
2.1.2 where
select name from instructor where salary between 90000 and 100000; // between select name, course_Id from instructor, teaches where (instructor.ID, dept_name) = (teaches.ID, 'Biology');
2.2 multi relation query
(1) Self connection
select S.supervisor from emp_super E, emp_super S where E.person = "Bob" and E.supervisor = S.person;
2.3 string operation
%: match any character
_: One character
select name from instructor where name like '%dar%'; select name from instructor where name = '100\%'; //Escape character select distinct name from instructor order by name desc; // Sort in descending order, ascending by default
2.4 set operation
Automatic weight removal:
Find out which courses start in fall 2009 or spring 2010:
(select course_id from section where year = 2009 and semester = "Fall") union (select course_id from section where year = 2010 and semester = "Spring");
Find out which courses start in autumn 2009 and spring 2010
(select course_id from section where year = 2009 and semester = "Fall") intersect (select course_id from section where year = 2010 and semester = "Spring");
Identify courses that start in the fall of 2009 but not in the spring of 2010
(select course_id from section where year = 2009 and semester = "Fall") except (select course_id from section where year = 2010 and semester = "Spring");
In the above filtering, there will be no duplicate course numbers, because the three operations of intersect, union and except are automatically de duplicated. If there is a course that starts in three periods in the autumn of 2009, it will only appear once. If you want to keep duplicates, you must add (operation name) all, such as the all keyword of union all.
-
Find out the lower value of all faculty salaries than the maximum salary
select distinct salary from instructor I1, instructor I2 where I1.salary < I2.salary;
-
Find out all possible salaries
select distinct salary from instructor;
-
Find out the maximum salary of teaching staff
(select distinct salary from instructor) except (select distinct salary from instructor I1, instructor I2 where I1.salary < I2.salary);
2.5 null value
select name from instructor where salary is null;
2.6 aggregation operation
2.6.1 basic aggregation function
- min
- max
- avg
- count
- sum
Find the average salary of the computer department
select avg(salary) from instructor where dept_name = 'Comp.Sci'; // rename select avg(salary) as avg_sal from instructor where dept_name = 'Comp.Sci';
Find the number of teachers who will teach a course in the spring semester of 2010
select count(distinct(ID)) from teaches where year = 2010 and semester = "Spring"; // Pay attention to weight removal
Find the number of rows in the table
select count(*) from instructor;
2.6.2 grouping and aggregation
Find the average salary of each department
select dept_name, avg(salary) as avg_sal from instructor group by dept_name;
2.6.3 having clause
- Where the where clause cannot be used
select dept_name, avg(salary) as avg_sal from instructor group by dept_name having avg(salary) > 42000;
2.6.4 aggregation of null and Boolean values
(1) Null value
-
null values are ignored when sum is evaluated
-
count evaluates to null and will not be ignored
(2) Boolean value
3. Nested sub query
3.1 collective membership
(1) In and not in
Find out all the courses that started in autumn 2009 and spring 2010 at the same time
- intersect: 1
- Method 2: nested sub query
select course_id from section
where year = 2009 and semester = "Fall" and
course_id in (select course_id from section
where year = 2010 and semester = "Spring");
Identify all courses that start in the fall of 2009 but not in the spring of 2010
- Method 1: except
- Method 2: nested sub query
select course_id from section
where year = 2009 and semester = "Fall" and
course_id not in (select course_id from section
where year = 2010 and semester = "Spring");
Usage of enumeration collection:
select distinct name from instructor where name not in ('Mozart', 'Einstein');
Find out the total number of students: they took the course section taught by the teacher with ID 10101
select count(distinct ID) from takes
where (course_id, sec_id, semester, year) in (select course_id, sec_id, semester, year
from teaches
where teaches.ID = 10101);
select count(distinct T1.ID) from takes T1 natural left outer join teaches T2 on T1.course_id = T2.course_id and T1.sec_id = T2.sec_id and T1.semester = T2.semester and T1.year = T2.year where T2.ID = 10101;
(2)some
Example: find out the names of all teachers who meet the following conditions. Their salary is at least higher than that of a teacher in the Department of Biology.
select name from instructor I1
where salary > some(select salary from instructor I2
where dept_name = "Biology");
// Don't use some
select distinct T1.name from instructor I1, instructor I2
where I1.salary > I2.salary and I2.dept_name = "Biology";
- Some equivalent expressions of some
- = some < ==== > in
- <> some <==\==> not in
(3)all
Equivalent representation of all
- =all ===>in
- ≠all <====> not in
Find the branch with the highest average balance
// ---------all keyword-------------
select branch_name
from account
group by branch_name
having avg(amount) >= all(select avg(amount) as avg_amount
from branch_name from account
group by branch_name);
// ---------Aggregate function ----------- cannot display branch name
select max(avg_amount)
from (select branch_name, avg(amount) as avg_amount
from account
group by branch_name);
(4) Null relation test
- exists structure
The exists structure tests whether there are tuples in the subquery.
The exists structure returns true when it is not empty and false when it is empty as a subquery
not exists is the opposite
Find out all the courses that started in autumn 2009 and spring 2010 at the same time:
select course_id
from section S1
where year = 2009 and semester = "Fall" and
exists(select course_id
from section S2
where S2.semester = "Spring" and S2.year = 2010 and
S2.course_id = S1.course_id);
Note the difference between in structure and exists structure:
- In is for course_ Whether ID is in the search result
- The exists structure is used to judge whether the sub query result is empty
Description of the above query:
- The related name S of the outer query can be used in the query of the where clause. Here, the sub query using the related name of the outer query is called the related sub query.
- Aliases used in subqueries cannot be used in outer queries.
- Aliases used in subqueries cannot be used in other subqueries
not exists structure
Identify all courses that start in the fall of 2009 but not in the spring of 2010:
select course_id
from section S1
where semester = "Fall" and year = 2009 and
not exists(select course_id from section S2 where
S2.semester = "Spring" and S2.year = 2010 and
S1.course_id = S2.course_id);
Identify all students who have taken all courses offered by the Department of Biology.
select distinct ID
from takes T1
where not exists(select course_id
from course
where dept_name = 'Biology'
except
select course_id
from takes T2
where T2.ID = T1.ID);
(5) Duplicate tuple existence test
SQL provides a Boolean function to test whether there is a duplicate tuple unique structure in the result of sub query
- There is no duplicate tuple in the query result, and true is returned
- false if there are duplicate tuples
Find all courses offered up to once in 2009:
select S1.course_id
from section S1
where unique(select S2.course_id
from section S2
where S1.course_id = S2.course_id and
year = 2009);
Unique is not implemented in mysql. The equivalent expression of unique in mysql:
select S1.course_id
from course S1
where 1 >= (select count(S2.course_id)
from section S2
where S1.course_id = S2.course_id and
year = 2009);
Identify all courses that were offered at least twice in 2009
select S1.course_id
from course S1
where not unique(select S2.course_id
from course S2
where S2.course_id = S1.course_id and
S2.year = 2009);
select S1.course_id
from course S1
where 2 <= (select S2.course_id
from course S2
where S2.course_id = S1.course_id and
S2.year = 2009);
3.2 summary of maximum value query method
Example: find the largest account balance
3.2.1 using relational algebra
Π b a l a n c e ( a c c o u n t ) − Π A 1 . b a l a n c e ( σ A 1 . b a l a n c e < A 2 . b a l a n c e ( ρ A 1 ( a c c o u n t ) × ρ A 2 ( a c c o u n t ) ) ) \Pi_{balance}(account) - \Pi_{A_1.balance}(\sigma_{A_1.balance<A_2.balance}(\rho_{A_1}(account) \times \rho_{A2}(account))) Πbalance(account)−ΠA1.balance(σA1.balance<A2.balance(ρA1(account)×ρA2(account)))
Translate the above expression into SQL:
select balance from account except (select A1.balance from account A1, account A2 where A1.balance < A2.balance);
3.2.2 using set operations
(1)except
select balance from account except (select A1.balance from account A1, account A2 where A1.balance < A2.balance);
3.2.3 using nested sub query
(1) all keyword
select balance
from account A
where A.account >= all(select balance
from account A2);
(2) exists keyword
select balance
from account A1
where not exists(select balance from account A2
where A1.balace < A2.balance);
3.2.4 aggregation function
select max(balance) from account;
3.3 subquery in from clause
Find out the average salary of teachers in those departments whose average salary exceeds $42000.
----------- having Clause writing ------------
select avg(salary)
from instructor
group by dept_name
having avg(salary) > 42000;
----------- from Subquery ---------------
select S.avg_sal
from (select avg(salary) as avg_sal from instructor
group by dept_name) as S
where S.avg_sal > 42000;
Find the Department with the largest payroll in all departments:
select dept_name
from instructor
group by dept_name
having sum(salary) >= all(select sum(salary)
from instructor
group by dept_name);
Print the name of each teacher, along with their salary and the average salary of their department.
select I.name, I.salary, A.avg_sal
from instructor I
left outer join (select dept_name, avg(salary) as avg_sal from instructor
group by dept_name) as A
on I.dept_name = A.dept_name;
In addition, the use of the lateral keyword enables variables in the from clause to access external variables
select I.name, I.salary, A.avg_sal
from instructor I, lateral(select avg(salary) as avg_sal
from instructor I2
where I2.dept_name = I.dept_name);
3.4 with Clause
Find the system with the largest budget value:
- Method 1: relational algebra translation
select D.dept_name from department D except (select dept_name from department D1, department D2 where D1.budget < D2.budget);
- Method 2: all keyword
select D.dept_name
from department D1
where D1.budget >= all(select D2.budget
from department D2);
- Method 3: not exists keyword
select D.dept_name
from department D1
where not exists(select D2.budget
from department D2
where D1.budget < D2.budget);
- Method 4: with keyword
with max_budget(value) as
(select max(budget)
from department)
select budget
from department D1, max_budget M
where D1.budget = M.value;
Find all the departments whose total wages are greater than the average of the total wages of all departments:
- having writing
select dept_name,sum(salary)
from instructor
group by dept_name
having sum(salary) > all(select avg(A.sum_sal)
from (select sum(salary) as sum_sal
from instructor
group by dept_name) as A);
- with Clause
with sum_sal(dept_name, value) as (select dept_name, sum(salary) from instructor group by dept_name), avg_sum_sal(value) as (select avg(value) from sum_sal) select sum_sal.dept_name from sum_sal where sum_sal.value > all(select * from avg_sum_sal);
3.5 Scalar Subquery
SQL allows a subquery to appear anywhere an expression that returns a single value can appear, as long as the subquery value returns a single tuple containing a single attribute.
Such a subquery is called a scalar subquery.
List all departments and the number of teachers they have:
// Scalar subquery writing method
select dept_name, (select count(*) from instructor
where department.dept_name = instructor.dept_name) as num_instructors
from department;
select department.dept_name, dept_count
from department, (select dept_name, count(*) from instructor
group by dept_name) as inst_num(dept_name, dept_count)
where department.dept_name = inst_num.dept_name;
4. Modification of the Database
4.1 Deletion
Deleting can only delete the whole tuple, not only the values on some attributes. At the same time, the relationship itself will not be changed.
- Delete tuples in table
delete from instructor;
- Delete some tuples that meet the conditions in the table
delete from instructor where dept_name = "Finance";
delete from instructor
where dept_name in (select dept_name
from department
where building = "Watson");
Example:
Delete all teacher records whose salary is lower than the average salary of the University
delete from instructor
where salary < (select avg(salary)
from instructor);
4.2 Insertion
(1) Insert a single tuple
- The attribute is not written, and the attribute order is consistent with that in the relationship mode
insert into course
values('CS-437', 'Databasa Systems', 'Comp.Sci', 4)
- Write attributes in any order
insert into course(title, course_id, credits, dept_name)
values('Database Systems', 'CS-437', 4, 'Comp.Sci')
(2) Bulk insert tuple set
Want every student who has completed 144 credits in the Music department to become a teacher in the Music department:
insert into instructor select ID, name, dept_name, 18000 from student where dept_name = "Music" and tot_credits > 144;
It should be noted that the insert operation is carried out after the select operation is executed, that is, after screening all qualified students, insert them into the instructor table; This is similar to a delete operation.
(3) insert with null value
Some unassigned attributes in the tuple to be inserted are given null values. Consider the request:
insert into student
values('3003', 'Green', 'Finance', null);
4.3 Updates
Requirement: change some attribute values without changing the whole tuple. Use the update statement.
Tuples to be updated can be found using query statements.
4.3.1 full operation and partial operation
- All operations
update instructor set salary = salary * 1.03;
- Conditional operation
update instructor set salary = salary * 1.03 where salary > 10000; ------------ Write two update ------------ update instructor set salary = salary * 1.05 where salary <= 10000;
- scalar subquery
update student S
set tot_cred = (select sum(credits)
from takes T natural left outer join course
where T.ID = S.ID and
T.grades <> 'F' and
T.grades is not null);
If a student has not finished any class, the above UPDATE statement will tot him_ Cred property value is set to null. Setting it to 0 actually meets our requirements:
update student S
set tot_cred = (select
case
when sum(credits) is not null then sum(credits)
else 0
end
from takes T natural left outer join course
where T.ID = S.ID and
T.grades <> 'F' and
T.grades is not null);
4.3.2 case structure
General structure:
case when pred_1 then result_1 when pred_2 then result_2 ...... when pred_n then result_n else result_0 end
The above requirements can be written as follows:
update instructor set salary = case when salary <= 10000 then salary * 1.05 else salary * 1.05 end
5. Views
The view is not part of the model and is visible to the user as a virtual relationship
5.1 view definition
create view faculty as select ID, name, dept_name from instructor;
Find the data of a department in the view:
select name from faculty where dept_name = "Biology";
Example: calculate the total salary of each department
create view department_total_salary(dept_name, total_salary) as select dept_name, sum(salary) from instructor group by dept_name;
5.2 using views in SQL queries
5.2.1 direct use of views
create view physics_fall_2009(course_id, sec_id, building, room_number) as select course_id, sec_id, building, room_number from course C, section S where C.course_id = S.course_id and C.dept_name = "Physics" and semester = "Fall" and year = 2009;
Find all the Physics courses offered at Watson building in the fall semester of 2009.
select * physics_fall_2009 where building = "watson";
5.2.2 using a view in another view definition
create view physics_fall_2009_watson(course_id, sec_id, room_number) as select course_id, sec_id, room_number from physics_fall_2009 where building = "watson";
6. Integrity Constraints
6.1 not null constraint
name varchar(20) not null, budget numeric(12,2) not null
6.2 unique constraints
unique(A1,A2,...,Am);
unique statement States A i 1 , A j 2 , . . . , A j m A_{i1},A_{j2},...,A_{jm} Ai1,Aj2,..., Ajm # forms a candidate code: no two tuples in the relationship can have the same value on all listed attributes.
Differences between candidate code and master code:
- The candidate attribute value can be null unless it is explicitly declared not null
6.3 check clause
check(P): P is a predicate logic
Example: ensure that the semester is one of fall, winter, spring and summer
create table section
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
building varchar(5),
room_number varchar(7),
time_slot_id varchar(4),
primary key(course_id, sec_id, semester, year),
check(semester in ("Spring", "Summer", "Fall", "Winter"))
);
6.4 referential integrity
6.4.1 referential integrity
Referential Integrity: ensure that the value of a given attribute in one relationship also appears in the value of a specific attribute in another relationship.
6.4.2 external code
(1) Foreign code declaration
dept_name varchar(20) references department();
(2) Handling of violations of referential integrity
Writing method of cascade processing:
create table course (dept_name varchar(20), foreign key(dept_name) references department on delete cascade on update cascade, ... );
Writing method of empty:
create table course (dept_name varchar(20), foreign key(dept_name) references department on delete set null ... );
How to write the default value:
create table course (dept_name varchar(20), foreign key(dept_name) references department on delete set default ... );
6.5 check and assertion
6.5.1 complex check
check(P), predicate P can be a predicate containing any query:
check (time_slot_id in (select time_slot_id from time_slot));
Check time of check clause:
- Detection is required when inserting tuples into a section
- time_ It also needs to be detected when the slot changes
Complex check detection costs a lot
6.5.2 assertion
An assertion is a predicate that indicates the conditions you want the database to meet:
Special forms of assertions:
- Domain constraints
- Referential integrity constraints
Special constraints:
-
For all tuples of student, he is in the attribute tot_ The value on cred must be equal to the sum of credits for all courses successfully completed by the student
create assertion credits_earned_constraint check (not exists (select tot_credits from students S where S.tot_credits <> (select sum(credits) from takes T where S.ID = T.ID and T.grades <> 'F' and T.grades is not null))); -
Each teacher cannot teach in two different classrooms at the same time in the same semester
create assertion teach_time_constraint check (unique (select ID, semester, time_slot_id, year from takes))
7. Build in Type
7.1 built in data types
-
date
date '2005-7-27'
-
time
time '09:00:00'
-
timestamp
timestamp '2005-7-27 09:00:30'
-
interval
8. Index
create index student_ID_index on student(ID);
9. User-Defined Type
9.1 user defined types
create type Dollars as numeric(12,2);
create table department (dept_name varchar(10), building varchar(15), budget Dollars );
- Delete type: drop type
- Modification type: alter type
9.2 domain
Domains: imposing integrity constraints on basic types
create domain person_name varchar(20) not null;
create domain degree_level varchar(10)
constraint degree_level_test
check(value in ('Bachelors','Master','Doctorate'));
10. Authorization
10.1 authorization
grant select on instructior to U1,U2;
The update permission can be authorized on a property or on the whole relationship. If there is no description, the authorization is on the whole relationship:
grant update on instructor(dept_name) to U1,U2,U3;
10.2 withdrawal of authority
revoke select, update(budget) on department from Aimit;
If the role of the withdrawn permission has transferred the permission to others, the withdrawal of permission will become difficult.
10.3 roles
10.3.1 role creation
create role instructor; grant select on takes to instructor;
10.3.2 role transfer
create role dean grant instructor to dean; grant dean to Satoshi;
10.3.3 permissions on views
create view geo_instructor as select * from instructor where dept_name = 'Geology'; grant select on geo_instructor to Amit;
10.3.4 authorization of mode
Only the owner of the schema can make changes to the schema, such as creating or deleting relationship properties, adding or deleting indexes.
grant references (dept_name) on department to Mario;
10.3.5 transfer of authority
Users with certain authorization are allowed to transfer this permission to other users. By default, permission cannot be transferred to other users and roles.
grant select on instructor to Amit with grant option;
10.3.6 withdrawal of authority
revoke indicates the withdrawal of permission
- Cascade indicates cascade retraction
- restrict indicates that cascade retraction is not required
revoke select on department from Amit, Satoshi cascade; revoke select on department from Amit, Satoshi restrict;
11. Declare and call SQL functions and procedures
11.1 functions that return basic types of database
Requirement: given the name of a department, return the number of teachers in the Department. Write the following functions:
create function dept_count(dept_name varchar(20)) returns integer begin declare d_count integer; select count(*) into d_count from instructor where instructor.dept_name = dept_name return d_count; end
Use of functions:
select dept_name, budget from department where dept_count(dept_name) > 12;
Table 11.2 functions
SQL supports functions that return relationships as results, which are called table functions
Example: return all teachers in a given Department
create function instructor_of(dept_name varchar(20))
returns table(
ID varchar(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2)
)
return table(select ID, name, dept_name, salary
from instructor
where instructor.dept_name = dept_name);
Table functions can be used in a query:
select *
from table(instructor_of('Finance'));
11.3 definition of process
create procedure dept_count_proc(in dept_name varchar(20), out d_count integer) begin select count(*) into d_count from instructor where instuctor.dept_name = dept_count_proc.dept_name end
Use of process
declare d_count integer;
call dept_count_proc('Physics', d_count);
11.4 language construction supporting processes and functions
11.4.1 variable declaration
declare d_count integer;
11.4.2 compound statement
Compound statements are enclosed by begin end
- begin atomic ... Ensure that all transactions are executed according to a single end statement
- while and repeat
--- while --- while Boolean expression do Statement sequence; end while --- repeat --- repeat Statement sequence; until Boolean expression end repeat
- for loop
declare n integer default 0; for r as select budget from department where dept_name = 'Music' do set n = n - r.budget end for
- if statement
if Boolean expression then ... elseif then ... else ... end if
- exception handling
declare out_of_classroom_seats condition declare exit handler for out_of_classroom_seats begin sequence of statements end
Example: create a function to register a student on the premise that the classroom can accommodate
create function registerStudent(s_id varchar(5), s_courseid varchar(8), s_secid varchar(8),
s_semester varchar(6), s_year numeric(4,0))
returns integer
begin
declare currEnrol integer;
declare errorMessage varchar(20);
select count(*) into currErol
from takes
where course_id = s_courseid and sec_id = s_secid
and semester = s_semester and year = s_year;
declare limit integer;
select capacity into limit
from classroom natural join section
on classroom.room_number = section.room_number
where course_id = s_course_id and sec_id = s_secid
and semester = s_semester and year = s_year;
if (currEnrol < limit)
begin
insert into takes values
(s_id, s_courseid, s_secid, s_semester, s_year, null);
return(0);
end
-- Otherwise, the upper limit is reached
set errorMessage = ''||''||'';
return (-1);
end;
12. Triggers
12.1 after trigger
12.1.1 insert trigger
create trigger time_slot_check1 after insert on section
referencing new row as nrow
for each row
when (nrow.time_slot_id not in (
select time_slot_id from time_slot))
begin
rollback
end;
12.1.2 delete trigger
create trigger time_slot_check2 after delete on section
referencing old row as orow
for each row
when exists(select * from section
where section.time_slot_id = orow.time_slot_id) and
not exists(select * from time_slot
where orow.time_slot_id = time_slot.time_slot_id)
begin
rollback
end;
12.1.3 update trigger
create trigger time_slot_check3 after update of takes on (grade)
referencing new row as nrow
referencing old row as orow
for each row
when nrow.grade <> 'F' and nrow.grade is not null and (orow.grade = 'F' or orow.grade is null)
begin atomic
update student
set tot_cred = tot_cred + (select credits
from course
where course.course_id = nrow.course_id)
where student.id = nrow.id;
end;
12.2 before trigger
The function of the trigger before the event:
- Avoid illegal updates, inserts, and deletions of additional constraints
Example:
- Insert the teacher into a department, but the name of the Department does not exist in the Department. The trigger can insert the name of the Department in advance to avoid foreign code conflict
- When the inserted score is blank, define a trigger and replace the value with Null
create trigger setnull before insert on instructor referencing new row as nrow for each row when nrow.dept_name not in (select dept_name from department) begin atomic insert into department values (nrow.dept_name, ....); end;