Базы данных
Изменение данных
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 = '4537' 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 условие]
truncate таблица -- DDL
delete from Points where CId = in (select CId from Courses where Name = 'СУБД')
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 not null or not exists (select * from Points p where p.SId = SId, 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 on Course after delete referencing old row c for each row delete from points p where p.CId = c.CId
create trigger on Points after insert, update referencing new row p for each row set p.time = now()
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, now(), 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 priviledges} on {table|database|view|procedure|function|…} имя to {пользователь|group группа|public} [with grant option]
grant all priviledges 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 priviledges} 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 priviledges 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;