作业-数据添加、修改、删除

use stum;
insert into students value ('2088001','张三','男','2070-10-01','','','','','','');
insert into students value ('2088002','李四','女',null,'计算机学院','软件工程','','','','');
insert into students value ('2088003','王五','男','2069-11-23','数学学院',null,'党员','','','');
insert into students value ('2088004','赵六',null,null,'音乐学院',null,'北京','','','');
insert into students value ('2088005','田七',null,'2068-05-14',null,'团员',null,null,null,null);
insert into students value ('2088006','周扒','男','2066-05-26','音乐学院','声乐乐器','群众','上海','888888','d://8.jpg');

insert into courses values('c66','大数据','68','3','必修'),
                          ('c77','云计算','102','4','选修'),
                          ('c88','人工智能','90','5','必修'),                          ('c99','信息安全','54',3,null);
show tables ;
insert into score values ('2015007','c01','0'),
                           ('2015007','c03','90'),
                           ('2015008','c06','0'),
                           ('2015009','c02','82');

update students
set sname = '张小三'
where sno = 2088001;

update students
set sbirthday = '2069-8-29',students.sdept = '外国语学院' where sname ='田七';

update courses
set cperiod = cperiod - 8
where credit = 4;

update score
set grade = grade + 5
where grade<60 and cno = 'c02';

update score
set grade =0
where  sno = (select sno from students where sname = '猪八戒');
desc score;
select * from score;

update courses
set ctype ='必修';

delete from courses
where cno = 'c99';

delete from teachers
;
select * from courses;
delete from score
where cno = 'c02'
and grade< 60;

create database test;
use test;
drop table if exists goods;
create table if not exists `goods`
(
    `id`           int(11) not null comment '商品编号',
    `name`         varchar(20) default null comment '商品名称',
    `product_date` date        default null comment '生产日期',
    `price`        float       default null comment '商品价格',
    primary key (`id`)
) engine = innodb
  default charset = utf8;
-- 插入数据：
insert into `goods` (`id`, `name`, `product_date`, `price`)
values (1, '洗衣粉', '2016-03-01', null),
       (2, '肥皂', '2015-12-22', 2),
       (3, '毛巾', '2015-01-20', 9.9),
       (4, '清洗剂', '2016-02-19', 8.6),
       (5, '卫生纸', '2015-11-06', null),
       (6, '牙刷', '2014-11-14', 4.5),
       (7, '牙膏', '2016-07-05', 13),
       (8, '洗面奶', '2016-03-29', 43.8),
       (9, '香水', '2015-09-16', null),
       (10, '口罩', '2015-01-30', 5);
# 1）查询出goods表中所有记录。
select *
from goods;
# 2）查询出goods表中生产日期在5年前的商品(以当前日期为准)。
select *
from goods
where product_date < price * 5;
# 3）查询出goods表中商品名称中带“洗”字的商品。
select *
from goods
where name like '%洗%';
# 4）查询出goods表中商品编号为2,4,6,8的商品。
select *
from goods
where id = '2,4,6,8';
# 5）查询出goods表中price字段值为null的商品。
select *
from goods
where price = null;
# 6）查询出goods表中price字段不为null并且商品编号在4到10范围内的商品。
select *
from goods
where price is not null
  and id between 1 and 10;

use test;
drop table if exists student;
insert into `student` (`id`, `name`, `grade`, `gender`)
values (1, '小明', 80, '男'),
       (2, '小红', 51, '女'),
       (3, '小花', 77, '女'),
       (4, '小华', 78, '男'),
       (5, '小琴', 69, '女'),
       (6, '小伟', 90, '男'),
       (7, '小白', 88, '男'),
       (8, '小建', 65, '男'),
       (9, '小梅', 72, '女'),
       (10, '小超', 55, '男'),
       (11, '小燕', 95, '女'),
       (12, '小康', 81, '男'),
       (13, '小蒙', 72, '女');
-- 插入数据：
create table if not exists `student`
(
    `id`     int(11) not null comment '学生的编号',
    `name`   varchar(20) default null comment '学生的姓名',
    `grade`  float       default null comment '学生的成绩',
    `gender` char(2)     default null comment '性别',
    primary key (`id`)
) engine = innodb
  default charset = utf8;
# 1）  查询出student表中所有学生的姓名和成绩，并按照成绩的降序排列。
select name, grade
from student
order by grade desc;
# 2）  分别查询出student表中男生、女生的平均分。
select avg(grade) male_average_grade
from student
where gender = '男';
select avg(grade) female_average_grade
from student
where gender = '女';
# 3）  查询出student表中的最高分和最低分。
select max(grade) as highest_grade,
       min(grade) as lowest_grade
from student;
# 4）  查询出student表中班级成绩前五名学生信息。
select *
from student
order by grade desc
limit 5;
# 5）  查询student表中成绩在第6名到第10名并且成绩大于70分的学生姓名和成绩。
select name, grade
from student
where grade > 70
order by grade desc
limit 5 offset 5;