mysqlベース(接続クエリー、トランザクション、ビュー、ストレージ・プロシージャ、関数)

select e.last_name, d.department_id, d.department_name
from departments d, employees e
where e.department_id = d.department_id;

select job_id, location_id
from employees e, departments d
where e.department_id = d.department_id
and e.department_id = 90

select e.last_name, d.department_name, l.location_id, l.city
from employees e, departments d, locations l
where e.department_id = d.department_id
and d.location_id = l.location_id
and e.commission_pct is not null

select department_name, job_title, min(salary)
from departments d, jobs j, employees e
where d.department_id = e.department_id
and e.job_id = j.job_id
group by department_name, job_title

select count(*)     , country_id
from departments d, locations l
where d.location_id = l.location_id
group by country_id
having     >2

select e.last_name employees, e.employee_id "Emp#", m.last_name manager, m.employee_id "Mgr#"
from employees e, employees m
where e.manager_id = m.employee_id

    select last_name, salary
    from employees e
    where e.salary = (
        select min(salary)
        from employees
    )

select e.department_id
from employees e
group by e.employee_id
order by avg(salary)
limit 1

select d.*, ag
from departments d
join (
    select avg(salary) ag, department_id
    from employees
    group by department_id
    order by avg(salary)
    limit 1
) ag_dep
on ag_dep.department_id = d.department_id

select avg(salary), job_id
from employees
group by employee_id
order by avg(salary) desc
limit 1

select avg(salary), department_id
from employees
group by department_id
having avg(salary) > (
    select avg(salary)
    from employees
)

select 
    * 
from
    employees
where employee_id = any(
    select distinct manager_id
    from employees
    where manager_id is not null
)

select min(salary), department_id
from employees
where department_id = (
    select department_id
    from employees
    group by department_id
    order by max(salary)
    limit 1
)

  select 
      last_name, department_id, email, salary
  from 
      employees
  where employee_id = any(
      select manager_id
      from employees
      where department_id = (
          select department_id
          from employees
          group by employee_id
          order by avg(salary) desc
          limit 1
      )
  )

  select 
      last_name, e.department_id, email, salary
  from 
      employees e
  inner join
      departments d 
  on d.manager_id = e.employee_id
  where d.department_id = (
      select department_id
      from employees
      group by employee_id
      order by avg(salary) desc
      limit 1
  )

create or replace view emp_v2
as
select last_name, salary, email
from employees
where phone_number like '011%'

create or replace view emp_v3
as
select max(salary), department_id
from employees
group by department_id
having max(salary)>12000

select d.*
from departments d
inner join emp_v3 e
on d.department_id = e.department_id

 updata     set

 delect from    

 insert into     value()

    create or replace view emp_v
    as 
    select "  " as name

    create or replace view emp_v
    as
    select (select last_name from employees)

    create or replace view emp_v
    as
    select e.*
    from employees
    where employees_id=(
        select manager_id
        from employees
        where manager_id is not null
    )

create table Book(
bid int primary key,
bname varchar(20) unique not null,
price float default 10,
btypeld int,
foreign key(btypeld) references bookType(id) 
)   

set autocommit = 0
inset into book(bid, bname, price, btypeId)
values(...)
commit|rockback;

create or replace view myv1
as
select bname, name
from book b
inner join
bookType bt
on bt.id = b.btypeId
where price>100

create or replace view myv1
as
select bname, price
from book
where price between 90 and 120

drop view myv1

delimiter $
create procedure my_pro1(in username varchar(20), in password int)
begin
    insert into admin(admin.username, admin.password)
    values(username, password);
end $

set names gbk$
call my_pro1("..", "..")

delimiter $
create procedure my_pro2(in id int, out name varchar(20), out phone varchar(20))
begin
    select b.name, b.phone into name, phone
    from beauty b
    where b.id = id;
end $
call my_pro2(1, @name, @phone)

delimiter $
create procedure my_pro3(in birth1 datetime, in birth2 datetime, out result int)
begin
    select datediff(birth1, birth2) into result;
end $

delimiter $
create procedure my_pro4(in mydate datetime, out strDate varchar(20))
begin
    select date_format(mydate, "%y %m %d ") into strDate;
end $

delimiter $
create procedure my_pro5(in name varchar(20), out cpname varchar(50))
begin
    select concat(b.name,"AND", ifnull(bo.boyname, "null")) into cpname
    from boys bo
    right join
    beauty b
    on bo.id = b.boyfriend_id
    where b.name = name;
end $

delimiter $
create procedure my_pro6(in size int, in startindex int)
begin
    select * from beauty limit startindex, size;
end $

delimiter $
create function my_fun1(num1 float, num2 float) returns float
begin
    declare sum float default 0;
    set sum=num1+num2;
    return sum;
end $

select my_fun1(1,3)