捷得林

create database [IF NOT EXISTS] westos;

drop database [IF EXISTS] westos;

use westos;

show databases;

CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
  `name` varchar(220) NOT NULL DEFAULT '匿名' COMMENT '姓名',
  `pwd` varchar(220) NOT NULL COMMENT '密码',
  `sex` char(2) NOT NULL DEFAULT '女' COMMENT '性别',
  `day` datetime DEFAULT NULL COMMENT '出生日期',
  `address` varchar(220) NOT NULL DEFAULT '匿地址' COMMENT '地址',
  `email` varchar(220) NOT NULL COMMENT '邮箱',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

--修改表名称
alter table teacher rename as teacher1;
--增加表字段
alter table teacher add name varchar(220);
--修改表字段 (重命名:修改约束)
alter table teacher modify name varchar(220); --修改约束
alter table teacher change name teacher_name varchar(220); --字段重命名

---删除表(如果存在再删除)
drop table if exists teacher;

INSERT INTO westos.student (id, name, pwd, sex, day, address, email) VALUES (1, '狗蛋', '12345678', '男', '2021-12-08 13:34:44', '安徽合肥', '5104@sohu.com')

-- 一定要指定条件 where
update student set address ='安徽合肥市' where name='狗蛋';

select id, name, pwd, sex, day, address, email from student where name='狗蛋';

DELETE FROM westos.student WHERE id = 2;

show create table student; --查看建表语句
desc student;   --查看表结构

select distinct studentno  from result   --distinct

--查询考试成绩在90分以上100分一下
select studentno ,studentresult from result where studentresult >= 90 && result.studentresult<=100;
--区间查询
--between      a between b and c A在BC之间为true
select studentno,studentresult from result where studentresult between 95 and 100;
--除了1000号之外所有学生的成绩
select studentno ,studentresult from result where result.studentno<>1000;
select studentno ,studentresult from result where result.studentno!=1000;
select studentno ,studentresult from result where not result.studentno =1000;

--开头是姓刘的  %(代表0到任意一个字符)  _代表一个字符
select studentno, studentname from student where student.studentname like '刘%';
--后面是一个字符开头是刘的人
select studentno, studentname from student where student.studentname like '刘_';
--左右匹配带刘的都展示
select studentno, studentname from student where student.studentname like '%刘%';

--查询某个区间  他是一个或者多个具体的值
select studentno,studentname from student where studentno in (1001,1002,1003);

select studentno,studentname from student where address is null;
select studentno,studentname from student where address is not null;

select s.studentno, studentname, subjectno, studentresult
from student s
         inner join
     result r
where s.studentno = r.studentno;

#左查询
select s.studentno, studentname, subjectno, studentresult
from student s
         left join result r
                   on s.studentno = r.studentno;

#右查询
select s.studentno, studentname, subjectno, studentresult
from student s
         right join
     result r
     on s.studentno = r.studentno;


select s.studentno, studentname, subjectname, studentresult
from student s
         right join result r
                    on r.studentno = s.studentno
         inner join subject sub
                    on r.subjectno = sub.subjectno;

--desc 降序  esc升序
select r.studentno, studentname, subjectname, studentresult
from result r
         left join student s
        on r.studentno = s.studentno
    inner join subject sub
        on r.subjectno = sub.subjectno
where sub.subjectname like '%C%'
order by studentresult desc

select s.studentno,studentname,subjectname,studentresult
from student s
inner join result r
    on s.studentno = r.studentno
inner join subject sub
    on sub.subjectno=r.subjectno
where sub.subjectname like '%C%' && r.studentresult > 30
order by r.studentresult desc
limit 0,10

--子查询
select s.studentno,studentname
from student s
inner join result r on s.studentno = r.studentno
where r.studentresult >=30 and subjectno = (
    select subjectno from subject where subjectname = 'C语言-1'
);

select s.studentno,studentname
from student s
inner join result r on s.studentno = r.studentno
inner join subject s2 on r.subjectno = s2.subjectno
where r.studentresult>=30 && s2.subjectname='C语言-1'

select subjectname,max(studentresult),min(studentresult),avg(studentresult)
from result r
inner join subject s on r.subjectno = s.subjectno
group by r.subjectno ----通过什么来分组
having avg(studentresult)>=70  ----分组过后用having来再次筛选数据 而不是where
;

select count(address)from student; --会去掉null值
select count(1)from student; --不忽略null值
select count(*)from student; --不忽略null值
select count(studentname) from student where studentname ='铁蛋';

select sum(studentresult) 总分 from result;
select avg(studentresult) 平均 from result;
select max(studentresult) 最高 from result;
select min (studentresult) 最低 from result;