作业2-数据表单表查询、简单查询、条件查询-题2

create database test;
use test;

drop table if exists department;

create table if not exists `department`
(

    `d_id`     int(4)      not null unique primary key comment '部门号',

    `d_name`   varchar(20) not null unique comment '部门名称',

    `function` varchar(20) comment '部门职能',

    `address`  varchar(30) comment '工作地点'

);
drop table if exists employee;

create table if not exists employee
(

    `id`      int(4) primary key comment '员工号',

    `name`    varchar(20) not null comment '姓名',

    `sex`     varchar(4)  not null comment '性别',

    `age`     int(4) comment '年龄',

    `d_id`    int(4)      not null comment '部门号',

    `salary`  float comment '工资',

    `address` varchar(50) comment '家庭住址',

    constraint employee_fk foreign key (`d_id`) references `department` (`d_id`)

);
insert into department
values (1001, '人事部', '人事管理', '北京');

insert into department
values (1002, '科研部', '研发产品', '北京');

insert into department
values (1003, '生产部', '产品生产', '天津');

insert into department
values (1004, '销售部', '产品销售', '上海');

insert into employee
values (9001, 'aric', '男', 25, 1002, 4000, '北京市海淀区');

insert into employee
values (9002, 'jim', '男', 26, 1001, 2500, '北京市昌平区');

insert into employee
values (9003, 'tom', '男', 20, 1003, 1500, '湖南省永州市');

insert into employee
values (9004, 'eric', '男', 30, 1001, 3500, '北京市顺义区');

insert into employee
values (9005, 'lily', '女', 21, 1002, 3000, '北京市昌平区');

insert into employee
values (9006, 'jack', '男', 28, 1003, 1800, '天津市南开区');

select *
from employee;
select *
from employee
limit 3,2;
select d_id, d_name, function
from department;
select *
from employee
where age >= 25
  and age <= 30;
SELECT d.d_id      AS 部门号,
       d.d_name    AS 部门名称,
       COUNT(e.id) AS 员工人数
FROM department d
         LEFT JOIN
     employee e ON d.d_id = e.d_id
GROUP BY d.d_id, d.d_name
ORDER BY d.d_id;

select d.d_id   as 部门号,
       d.d_name as 部门名称,
        max(e.salary)
from department d
         left join employee e on d.d_id = e.d_id
group by d.d_id;

select d.d_id   as 部门号,
       d.d_name as 部门名称,
        sum(e.salary)
from department d
         left join employee e on d.d_id = e.d_id
group by d.d_id;

select * from employee order by salary;

select name,age,address from employee where address like '北京%';
select * from employee where name like '_ric';
select * from employee where name like 'l__y';

drop table if exists score;

create table if not exists score (

  id int not null default 1 comment '学生id',

  name varchar(20) not null default '' comment '姓名',

  chinese float not null default 0.0 comment '语文成绩' ,

  english float not null default 0.0 comment '英语成绩' ,

  math float not null default 0.0 comment '数学成绩'

)comment='成绩表';

  insert score (id, name, chinese, english, math) values

(1,'韩信',89,78,90), (2,'张飞',67,98,56),

(3,'宋江',84,78,77), (4,'关羽',88,98,90),

(5,'赵云',82,84,67), (6,'欧阳锋',55,85,45),

(7,'黄蓉',75,65,30),(8,'赵飞燕',60,61,62);

select * from score;

select name , english from score;

select name,(english+math+chinese) as 总分 from score;
select name,(english+math+chinese)*1.60 as 总分 from score where name like '赵%';
select name as 姓名 , math as 数学 from score;
select * from score where name like '黄%';
select * from score where english > 90;

select name,(english+math+chinese) as 总分 from score where (english+math+chinese)>200;
select *
from score where name like '赵%' and id<7;

select *
from score where english > score.chinese;

select * from score where (chinese+score.math+score.english)>200 and math<score.chinese and name like '宋%';
select * from score where english between 80 and 90;
select * from score where math = 89 or math = 90 or math = 91;
select * from score where name like '张%' or name like '宋%';
select * from score where math>80 and chinese > 80;
select * from score order by math;

select * ,(score.chinese+score.math+english) as 总分
from score order by (score.chinese+score.math+english) desc ;

select * ,(score.chinese+score.math+english) as 总分
from score where name like '赵%';
select count(*) as 总人数 from score;

select count(*) from score where math > 80;
select count(*) from score where (chinese+score.math+score.english)>250;

select sum(english) as 英语 ,sum(math) as 数学 ,sum(chinese) as 语文 from score;

select avg(chinese) from score;

select (avg(english)+avg(math)+avg(chinese)) as 总平均分 from score;

select max(chinese+math+english) as max ,min(chinese+math+english) as min from score;