练习SQL

mysql> create table if not exists employee(
    ->  `name`  varchar(20),
    ->  id      int,
    ->  salary  int);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into employee(name, id, salary) values('zhaoyang', 1, 300), ('renweihan', 2, 200), ('pengxiaofen', 3, 100);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from employee;
+-------------+------+--------+
| name        | id   | salary |
+-------------+------+--------+
| zhaoyang    |    1 |    300 |
| renweihan   |    2 |    200 |
| pengxiaofen |    3 |    100 |
+-------------+------+--------+
3 rows in set (0.00 sec)
mysql> select ifnull(
    ->  (select distinct salary
    ->  from employee
    ->  order by salary desc
    ->  limit 1 offset 1), null) as SecondHightestSalary;
+----------------------+
| SecondHightestSalary |
+----------------------+
|                  200 |
+----------------------+
1 row in set (0.01 sec)

mysql> select (
    ->  select distinct salary
    ->  from employee
    ->  order by salary desc
    ->  limit 1 offset 1) as SecondHightestSalary;
+----------------------+
| SecondHightestSalary |
+----------------------+
|                  200 |
+----------------------+
1 row in set (0.00 sec)

mysql> delete from employee where id = 1 and id = 2;
Query OK, 0 rows affected (0.01 sec)

mysql> select (
    ->  select distinct salary
    ->  from employee
    ->  order by salary desc
    ->  limit 1 offset 1) as SecondHightestSalary;
+----------------------+
| SecondHightestSalary |
+----------------------+
|                  200 |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from employee;
+-------------+------+--------+
| name        | id   | salary |
+-------------+------+--------+
| zhaoyang    |    1 |    300 |
| renweihan   |    2 |    200 |
| pengxiaofen |    3 |    100 |
+-------------+------+--------+
3 rows in set (0.00 sec)

mysql> delete from employee where id = 1;
Query OK, 1 row affected (0.04 sec)

mysql> select * from employee;
+-------------+------+--------+
| name        | id   | salary |
+-------------+------+--------+
| renweihan   |    2 |    200 |
| pengxiaofen |    3 |    100 |
+-------------+------+--------+
2 rows in set (0.01 sec)

mysql> delete from employee where id = 2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from employee;
+-------------+------+--------+
| name        | id   | salary |
+-------------+------+--------+
| pengxiaofen |    3 |    100 |
+-------------+------+--------+
1 row in set (0.00 sec)

mysql> select (
    ->  select distinct salary
    ->  from employee
    ->  order by salary desc
    ->  limit 1 offset 1) as SecondHightestSalary;
+----------------------+
| SecondHightestSalary |
+----------------------+
|                 NULL |
+----------------------+
1 row in set (0.00 sec)

mysql>

 第二次更新

 --2009秋季学期开设的所有课程集合；2010春季学期开设的所有课程集合
(select course_id from `section`  where semester = 'fall' and `year` = 2009)   
union all 
(select course_id from `section` where semester = 'Spring' and `year` =2010);  


select name from instructor where salary is not null ; 

select avg(salary) from instructor where dept_name = 'Comp. Sci.'; 

select salary from instructor where dept_name = 'Comp. Sci.';


select * from instructor where dept_name = 'Comp. Sci.';

select count(distinct id) from teaches where semester = 'spring' and `year` = 2010; 

select count(*) from course c ; 

select * from course c ;

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

select avg(salary) from instructor ;

select dept_name, count(distinct id) as instr_count from instructor natural join teaches 
where semester = 'spring' and year = 2010 
group by dept_name ;
-- 任何没有出现在group by子句中的属性如果出现再select子句中的话，它只能出现再聚集函数内部。

select dept_name, avg(salary) as avg_salary
from instructor
group by dept_name 
having avg(salary) > 42000; 
-- 任何没有出现在having子句中，但是没有被聚集的属性必须出现在group by子句中

select course_id, semester, year, sec_id, avg(tot_cred)
from takes natural join student
where `year` = 2009
group  by course_id , semester , `year` , sec_id 
having count(ID) >= 2; 

select distinct course_id from `section` where semester = 'fall' and `year` = 2009 and course_id in (
select course_id from `section` where semester = 'spring' and `year` = 2010)

select distinct course_id from `section` where semester = 'fall' and `year` = 2009 and course_id not in (
select course_id from `section` where semester = 'spring' and `year` = 2010)

select distinct name from instructor where name not in ('mozart', 'einstein');

 第三次更新

select recipeTitle 
from recipes --菜谱
where RecipeID in (
    select RecipeID 
    from recipe_ingredients --食材表
    where recipe_ingredients.IngredientID  in (
        select ingredientID 
        from ingredients inner join ingredient_classes on ingredients.IngredientClassID = ingredient_classes.IngredientClassID
        where ingredient_classes.IngredientClassDescription = 'Seafood'
    )
)

(select ingredientID
from ingredients
where ingredients.IngredientClassID in (
    select IngredientClassID
    from ingredient_classes 
    where ingredient_classes.IngredientClassDescription = 'Seafood'
    )
)


select recipeTitle
from recipes 
where recipes.RecipeID in (
    select RecipeID 
    from (recipe_ingredients inner join ingredients on recipe_ingredients.IngredientID  = ingredients.IngredientID )
        inner join ingredient_classes on ingredients.IngredientClassID = ingredient_classes.IngredientClassID 
    where ingredient_classes.IngredientClassDescription = 'Seafood'
)