MySQL summary

MySQL summary

-- Creation
create table <tablename> (A1 D1,
              A2 D2 not null,
              primary key (A1),
              foreign key (A2)refrences <tablename>)

create table temp_account like account

insert into <tablename> value ('','');

drop table <tablename>
delete from <tablename>

alter table <tablename> add A D
alter table <tablename> drop A 

-- Select

Select A1,A2,A3       
from <tablename>
where <predicate>


Select <<all>> <<distinct>> A1,A2,A3       
from <tablename>
where <predicate> //and,or,not

select name, title
from instructor natural join teaches, course
where teaches.course_id = course.course_id;

select name, title
from (instructor natural join teaches) join course using(course_id);

-- renaming using "as" (can be omitted)

select name
from instructor
where name like '%dar%'

select distinct name
from instructor
order by name <<desc>> <<asc>>

select name
from instructor
where salary between 90000 and 100000

-- Tuple comparison
select name, course_id
from instructor, teaches
where (instructor.ID, dept_name) = (teaches.ID, ’Biology’);

-- Set Operations
(select course_id from section where sem = ‘Fall’ and year = 2009)
<<union>> <<intersect>> <<except>> <<minus>> -- use union all to keep duplicates
(select course_id from section where sem = ‘Spring’ and year = 2010)

-- Check For null
select name
from instructor
where salary is null

-- Aggregate Functions
select avg (salary)
from instructor
where dept_name= ’Comp. Sci.’;

select count (distinct ID)
from teaches
where semester = ’Spring’ and year = 2010

select count (*)
from course;

select dept_name, avg (salary)
from instructor
group by dept_name;

select dept_name, avg (salary)
from instructor
group by dept_name
having avg (salary) > 42000;


select distinct course_id
from section
where semester = ’Fall’ and year= 2009 and
course_id  <<not in>> <<in>> (select course_id
        from section
        where semester = ’Spring’ and year= 2010);

select name
from instructor
where salary > <<some>> <<all>> (select salary
    from instructor
    where dept_name = ’Biology’);

select course_id
from section as S
where semester = ’Fall’ and year= 2009 and
exists (select *
    from section as T
    where semester = ’Spring’ and year= 2010
    and S.course_id= T.course_id);

select T.course_id
from course as T
where unique (select R.course_id
    from section as R
    where T.course_id= R.course_id
    and R.year = 2009);

with max_budget (value) as
(select max(budget)
from department)
select budget
from department, max_budget
where department.budget = max_budget.value;

delete from instructor
where dept_name in (select dept_name
from department
where building = ’Watson’);

update instructor
set salary = salary * 1.05
where salary <= 100000;

update instructor
set salary = case
when salary <= 100000 then salary * 1.05
else salary * 1.03
end


natural left outer join
natural right outer join
natural full outer join
inner join


-- View
create view faculty as
select ID, name, dept_name
from instructor


not null
unique
check (semester in (’Fall’, ’Winter’, ’Spring’, ’Summer’))

foreign key (dept_name) references department
on delete cascade <<set null>> <<set default>>
on update <<cascade>>

create type Dollars as numeric (12,2) final

create domain person_name char(20) not null

grant <privilege list>
on <relation name or view name> to <user list>

revoke <privilege list>
on <relation name or view name> from <user list>

grant select on department to Amit with grant option;
revoke select on department from Amit, Satoshi cascade;
revoke select on department from Amit, Satoshi restrict;