Базы данных
Изменение данных
insert into таблица [(атрибуты)] values (значения) [, ещё строки]
insert into таблица [(атрибуты)] запрос
insert into Courses (Name, Lecturer) values ('БД', 'Корнеев Г.'), ('ТеорИнф', 'Трифонов П.')
insert into Points select SId, CId, 0 from Students, Courses
update таблица set значения [where условие]
атрибут = выражение
update Students set GroupId = 'M3437' where LastName = 'Иванов'
update Students set PassNo = '1234', PassSeries = '567890' where SId = 1
update Points set points = points + 1 where CId in (select CId from Courses where Name = 'СУБД')
delete from таблица [where условие]
delete from Points where CId in (select CId from Courses where Name = 'СУБД')
truncate таблица -- DDL
truncate Points
merge into таблица [[as] имя1] using данные [[as] имя2] on условие when matched [and ...] then действие1 when not matched [and ...] then действие2
merge into Points using (select * from Courses c, Students s) r on Points.CId = r.CId and Points.SId = r.SId when matched then update set points = points + 1 when not matched then insert (SId, CId, points) values (r.SId, r.CId, 1)
Date
create type Name as object ( FirstName varchar(100), LastName varchar(100) )
create type Points as object (points as int between 0 and 100)
Enroll Date not null
SName Name
CoursePoints Points
primary key (SId, CId)
unique (PassNo, PassSeries)
foreign key (SId) references Students(SId)
check (Points between 0 and 100)
SId in (select SId from Students)
ExpulsionDate is not null or not exists (select * from Points p where p.SId = SId and p.CId = CId and Point < 60)
create assertion NonEmptyGroup check (not exists (select * from Group g where not exists (select * from Students s where s.GId = g.GId)))
foreign key (столбцы) references таблица [(столбцы)] [on delete действие] [on update действие]
foreign key (CId) references Courses on delete cascade on update cascade
foreign key (SId) references Students on delete restrict on update cascade
create trigger имя on объект {before | after | instead of} {insert | update | delete} [объявление ссылок] [for each {row | statement}] действие
referencing {new|old} {row|table} имя
create trigger CascadeCourses on Courses after delete referencing old row c for each row delete from points p where p.CId = c.CId
create trigger AuditPoints on Points after insert, update referencing new row p for each row set p.UpdateTime = current_timestamp()
create view имя(столбцы) as запрос
drop view имя
create view AveragePoints(SId, AvgPoints) as select SId, avg(Points) from Points
create view StudentCourse(FN, LN, N, P) as select s.FirstName, s.LastName, c.Name, p.Points from Students s natural join Points p natural join Courses c
create materialized view имя [ refresh [{fast|complete}] [on {demand|commit}] [start with время] [next время] ] as запрос
refresh materialized view имя
create materialized view AveragePoints refresh next dateadd(day, current_timestamp(), 1) as select SId, avg(Points) from Points group by SId
create user имя [password 'пароль']
alter user имя [password 'пароль']
drop user имя
create user kgeorgiy;
alter user kgeorgiy password '***';
drop user kgeorgiy;
create group имя
alter group имя {add|drop} user пользователь
drop group имя
create group lecturers;
alter group lecturers add user kgeorgiy;
drop group lecturers;
grant {select|insert|update|delete|create |execute|trigger|usage|…|all privileges} on {table|database|view|procedure|function|…} имя to {пользователь|group группа|public} [with grant option]
grant all privileges on Students to group Deans with grant option;
grant select on Students to public;
revoke [grant option for] {select|insert|update|delete|create |execute|trigger|usage|…|all privileges} on {table|database|view|procedure|function|…} имя from {пользователь|group группа|public} [cascade|restrict]
revoke insert on Students from group Deans cascade;
revoke update on Students from public;
alter {table|schema|database|…} owner to пользователь
create view FITPStudents as select * from Students where GId in (select GId from Group where FId = 'ИТиП')
grant all privileges on FITPStudents to FITPDean;
create view PublicStudents as select SId, FirstName, LastName /* No passport info, etc */ from Students
grant select on PublicStudents to public;
create view StudentPoints as select SId, avg(points) from Students natural join Marks
grant select on StudentPoints to public;