PYTHON3.Msql_expert.day02

1.聚合函数
     count(id)   不为null的id数
2.分组 + 聚合
     group by
     eg.select country,countid) from sanguo group by country;
3.分组筛选
     having
     eg.select country,count(id) as c_n from sanguo group by country having c_n > 2;
4.distinct()去重
5.索引
     主键索引
     唯一索引
     普通索引
     查看索引 show index from 表名
     删除索引 drop index 索引名 on 表名
=========================================================================
需求：
     1.老师信息：老师姓名，老师年龄，性别，爱好
     2.课程信息：课程名称，课时
     3.学院信息：学员信息，学员年龄，性别，毕业院校，班级，专业
     4.考试信息：某位学员的某一门课考了多少分
1.表关系
     1.外键 - Foreign Key
         作用：约束当前表的某列值必须取自于另一张表的主键列值
         外键所在的列称为“外键列”
         外键所在的表称为“外键表”或“子表”
         被外键列所引用的表称为“主表”或“主键表”
     2.语法
       1.在创建表的同时指定外键
             create table Course(
                 id int primary Key auto_increment,
                 cname varchar(20)
             )
             create table Teacher(
                 id int primary key auto_increment,
                 tname varchar(20),
                 course int ,
                 constraint fk_course_teacher foreign key(course) references Course(id) ------->增加外键的实现
             )
            语法：
                 create table xxx(
                     字段类型，
                     ...,
                     constraint 外键名 foreign key(字段) references 主键表(主键列)
                 )

      2.对已有表增加外键
           alter table 表名 add constraint 外键名 foreign key(字段) references 主键表(主键)
        3.删除外键
             alter table 表名 drop foreign key 外键名
         4.查看外键：show create table tablename

-- 单行注释
-- 创建一张course表：id,cname,cduration
create table course(
id int PRIMARY KEY auto_increment,
cname VARCHAR(30) not null,
cduration int not null
);
-- 插入测试书据
insert into course (cname,cduration)
values ('python基础',20),
('python高级',15),
('weg基础',9),
('python web',15),
('爬虫',10),
('书据分析＆人工智能',20);


-- 创建teacher表:id,age,genger,hobby,coures
-- course_id 是外键，引用course表的主键id
CREATE TABLE teacher(
  id int PRIMARY KEY  auto_increment,
  name VARCHAR (30) not null,
  age int not null,
  genger VARCHAR (2) not null,
  hobby VARCHAR (50) not null,
  course_id int,
  -- 外键约束
  constraint fk_course_teacher foreign key(course_id) references course(id)
);

-- 向teacher表中插入主键
INSERT INTO teacher VALUES
(null,'齐天大圣',28,'M','看书',1),
(null,'吕泽',30,'M','自拍',2),
(null,'赵萌萌',18,'F','帅哥',3);


-- 创建major表：id,m_name
CREATE TABLE major(
  id int PRIMARY KEY auto_increment,
  m_name VARCHAR (30) not null
);

-- 向major中插入书据
INSERT INTO major(m_name) VALUES('AID'),('UID'),('JSD'),('web');

-- 创建student表：id,name,age,gender,school,class_id,major_id
CREATE TABLE student(
  id INT PRIMARY KEY auto_increment,
  name VARCHAR (30) NOT NULL,
  age INT NOT NULL,
  gender CHAR (2) NOT  NULL,
  school VARCHAR (100) NOT NULL,
  class_id INT NOTc NULL,
  major_id INT NOT NULL
);

-- 更新Student表，增加外键关系在major_id上，引用自major表的主键id
ALTER TABLE student
add CONSTRAINT fk_major_student FOREIGN key(major_id) REFERENCES major(id);

-- 插入student表内容
INSERT INTO student VALUES(null,'张三',18,'M','哈佛大学',5,1),
(null,'李斯',19,'M','麻省理工',4,1),
(null,'王五',26,'F','蓝翔',4,3),
(null,'左左',19,'F','五道口技校',4,1);

-- 创建Classinfo表：id,classname,status
CREATE TABLE classinfo(
id INT  PRIMARY KEY auto_increment,
classname VARCHAR (10) NOT NULL,
status INT NOT NULL
);

-- 在classinfo中插入书据
INSERT INTO classinfo VALUES (null,1901,0),(null,1902,1),(null,1903,1),(null,1904,1),(null,1905,1);*/

-- 修改student表结构,增加外键class_id,引用自classinfo表的主键id
alter TABLE student add CONSTRAINT  fk_classinfo_student FOREIGN KEY (class_id) REFERENCES classinfo(id);



-- 创建score表：id,stu_id,course_id,score
CREATE TABLE score(
id INT PRIMARY KEY auto_increment,
stu_id INT NOT NULL ,
course_id INT NOT NULL ,
score INT NOT NULL
);
ALTER TABLE score add CONSTRAINT fk_student_score FOREIGN KEY(stu_id) REFERENCES student(id);
ALTER TABLE score add CONSTRAINT fk_course_score FOREIGN KEY(course_id) REFERENCES course(id);


INSERT INTO score(stu_id,course_id,score) VALUES
(1,1,98),(1,2,85),(1,3,96),
(2,1,69),(2,2,80),(2,4,65),
(3,2,76),(3,3,88),(3,4,75);


-- 删除score表中的fk_student_score外键
ALTER  TABLE  score drop FOREIGN KEY fk_student_score;

     3.级联操作
        1.语法：
             alter table 表名
             add constraint 外键名
             foreign key(字段)
             references 主键表(主键)
             on delete 级联删除
             on update 级联操作

-- 增加score表中的stu_id增加外键并设置级联操作 
ALTER TABLE score add CONSTRAINT fk_student_score FOREIGN KEY (stu_id) REFERENCES student(id) on DELETE CASCADE on UPDATE CASCADE

         2.级联操作取值
             1.cascade
                 数据级联删除、更新
             2.restrict (默认值)
                 子表中有关联数据那么主表中就不允许做删除、更新
             3.set null
                 主表删除数据时，子表中的相关数据会设置为null
     4.表连接查询
         1.交叉连接 -笛卡尔积
             eg.查询teacher和course表中的数据
                  select * from teacher,course;

         2.内连接
             在关联的两张表中把满足某些条件的数据筛选出来
             语法:
                select 字段,... ...
                 form 表1
                 inner join表2 ---->inner内部 join连接
                 on 条件
                 inner join 表3
                 on 条件

-- 使用内连接查询teacher和course表中的书据(姓名，年龄，课程，名称，课时)
SELECT t.name,t.age,c.cname,c.cduration
FROM teacher AS t
INNER JOIN course AS c
ON t.course_id = c.id

             练习：
                 1.查询学员的姓名，年龄，所在班级名称及专业名称
                 2.查询学员的姓名，毕业院校，所在班级，考试科目，考试成绩

-- 1.查询学员的姓名，年龄，所在班级名称及专业名称
SELECT s_t.name,s_t.age,class.classname,m.m_name
from student as s_t
INNER JOIN classinfo as class ON s_t.class_id = class.id
INNER JOIN  major as m ON s_t.major_id = m.id
where class.classname = '1902';


-- 2.查询学员的姓名，毕业院校，所在班级，考试科目，考试成绩
SELECT s_t.name,s_t.school,c.classname,cou.cname,sc.score
FROM student AS s_t
INNER JOIN classinfo as c on s_t.class_id = c.id
INNER JOIN score as sc ON s_t.id = sc.stu_id
INNER JOIN course as cou ON sc.course_id = cou.id;