• 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;

    2019-07-09_16-35-40

    2019-07-09_16-37-59
             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;

    练习一        

    练习2

  • 相关阅读:
    省市区多级联动数据组合
    常用字符串函数
    设计模式分类
    计算机网络学习笔记(绪论第一部分)
    关于JAVA核心技术(卷一)读后的思考(继承中的类、子类和超类)
    关于JAVA核心技术(卷一)读后的思考(用户自定义类,静态域和静态方法的思考以及方法参数)
    关于JAVA核心技术(卷一)读后的思考(对象与类,日历的构造)
    微型学生信息管理系统的建立
    PHP异常处理(Exception)
    使用trait中相同方法的优先级问题
  • 原文地址:https://www.cnblogs.com/shengjia/p/11159284.html
Copyright © 2020-2023  润新知