PYTHON3.Mysql_expert.day03

1.外键
     1.创建表设置外键
         create table xxxx(
             xxx xxxx,
             constraint 外键名 foreign key(字段)
             references 主表(主键)
             on delete cascade|restrict|set null
             on update cascade|restrict|set null
         )
     2.修改表增加外键
         alter table xxx
         add constraint 外键名
         foreign key(字段)
         references 主表(主键)
         on delete cascade|restrict|set null
         on update cascade|restrict|set null
     3.查看外键
         show create table xxxx
     4.删除外键
         alter table xxxx
         drop foreign key 外键名
2.连接查询
     1.交叉连接
         select * from A,B where A.bid=B.id
     2.内连接
         连接两张表,将满足条件的数据筛选出来
         select * from A
         inner join B
         on A.bid = B.id
=======================================================
1.连接查询
     1.外连接
         1.左外连接
             1.作用
                 1.左表中所有的数据都会查询出来(即便不满足条件)
                 2.将右表中满足关联条件的数据查询出来
                 3.关联不上的数据关联字段将以null作为填充
             2.语法
                 select 字段
                 from A left join B
                 on 关联条件

       

-- 1左外连接：左表teacher,右表course,关联条件：teacher.course_id = course.id
select * from
teacher left join course
on teacher.course_id = course.id

--  2左外连接：左表course,右表teacher,关联条件：teacher.course_id = course.id
select * from
course left join teacher
on teacher.course_id = course.id

               

2.右外连接
             1.作用
                 1.右表中所有的数据都会查询出来
                 2.将左表中满足关联条件的数据查询出来
                 3.关联不上的数据关联字段将以null作为填充
             2.语法
                 select 字段
                 from A right join B
                 on 关联条件

   

-- 3.右外连接，左表：ｔｅａｃｈｅｒ，右表：ｃｏｕｒｓｅ，关联条件：teacher.course_id = course.id
select * from
teacher right join course
on teacher.course_id = course.id;


select * from
course right join teacher
on teacher.course_id = course.id;

         3.完整外连接
             1.作用
                 将两张表的数据做关联查询,关联得上的则正常显示
                 关联不上的,则以null值填充
             2.语法
                 select * from
                 A full join B
                 on 关联条件

     

-- 4.完整外连接(有误)
select * from
course full join teacher
on full.id = teacher.course_id

-- 5.查看没有参加过考试的同学的信息
select * from
student left join score
on student.id = score.stu_id
where score.score is null;

2.子查询
     1.什么是子查询
         将一个查询的结果作为外侧操作的一个条件出现
     2.语法
         select .... from 表名 where 条件=(select ... )
         select .... from (查询)
     练习3：查询student表中比'张三'年龄大的学员信息
         select * from student where age > (select age from student where name = '张三');

    练习4:

        1.查询考过"齐天大圣"老师所教课程的学员的信息
         2.查询在score表中有成绩的学员的信息
         3.查询"Python基础"课程并且分数在80分以上的
           学员的姓名和毕业院校
         4.查询和"张三"相同班级以及相同专业的同学的信息

    

-- 练习４
-- 1.查询考过齐天大圣老师所教授课程的学员信息
-- 1.1查询该老师所教课程的id
select course_id from teacher where name='齐天大圣';
-- 1.2从course 表中查询出course_id为１的stu_id的值
select stu_id from score where course_id = (select course_id from teacher where name='齐天大圣');
-- 1.3 从student 中查出id在以上查询结果中出现的学员信息
select * from student where id in (
  select stu_id from score where course_id = (
    select course_id from teacher where name='齐天大圣'
  )
);
-- 2.查询在score表中有成绩的学员信息
 select * from student where id in(select stu_id from score);
-- 3.查询‘python基础’课程并且分数在820以上的学员姓名和毕业院校
select name,school from student
  where id in(select stu_id from score
  where course_id = (select id from course where cname = 'python基础') and score > 80
  );
-- 4.查询和‘张三’相同班级及相同专业的同学信息
select * from student where name != '张三' and class_id = (select class_id from student where name = '张三')
and major_id = (select major_id from student where name = '张三');

3.E-R模型
     1.什么E-R模型
         Entity - Relationship 模型 (实体-关系模型)
         在数据库设计阶段一定会使用到
         以图形的方式展示数据库中的表以及表关系
     2.概念
         1.实体 - Entity
             表示数据库中的一个表
             图形表示:矩形框
         2.属性
             表示某实体中的某一特性,即表的字段
             图形表示:椭圆形
         3.关系 - Relationship
             表示实体与实体之间的关联关系
             1.一对一关系(1:1)
                 A表中的一条记录只能关联到B表中的一条记录上
                 B表中的一条记录只能关联到A表中的一条记录上

                在数据库中的实现手段
                 在任意的一张表中增加:
                     1.外键,并引用自另一张表主键
                     2.唯一索引/约束
             2.一对多关系(1:M)
                 A表中的一条记录能够关联到B表中的多条记录
                 B表中的一条记录只能关联到A表中的一条记录

                在数据库中的实现手段
                 在"多"表中增加:
                     1.外键,引用"一"表的主键
             3.多对多关系(M:N)
                 A表中的一条记录能够关联到B表中的多条记录
                 B表中的一条记录能够关联到A表中的多条记录

                在数据库中的实现手段
                 靠第三张关联表,来实现多对多
                     1.创建第三张表
                     2.一个主键,俩外键
                         外键分别引用自关联的两张表的主键

      

-- 1.创建wife表，目的是实现与teacher的一对一关系
-- wife增加外键
create table wife(
id int primary key auto_increment,
name varchar(20) not null,
age int not null,
teacher_id int not null,
constraint fk_teacher_wife foreign key(teacher_id) references teacher(id),
unique (teacher_id)
);

insert into wife(name,age,teacher_id) values('祁夫人',23,1),('吕夫人',76,2);


create table goods(
id int primary key auto_increment,
gname varchar(32) not null,
gprice int not null
);

-- 插入数据
insert into goods(gname,gprice) VALUES
('iphone56',18888),
('ipad43mini',8888),
('华为mate3000',3000);

-- 创建ｓｈｏｐｐｉｎｇchart,teacher与goods对对多之间的第三张关联表
create table shoppingcart(
  id int PRIMARY key auto_increment,
  t_id int,
  g_id int,
  count int DEFAULT 1,
  constraint fk_goods_shoppingcart foreign key(g_id) REFERENCES goods(id),
  constraint fk_teacher_shoppingcart foreign key(t_id) REFERENCES teacher(id)
);

-- 测试
insert into shoppingcart(t_id,g_id)VALUES (1,1),(1,2);
insert into shoppingcart VALUES (null,1,3,15),(null,2,2,8),(null,2,3,1);

4.SQL语句优化
    1.索引:经常select,where,order by 的字段应该建立索引
     2.单条查询语句最后添加 LIMIT 1    , 停止全表扫描
     3.where子句中尽量不使用 != ,否则放弃索引全表扫描
     4.尽量避免null值判断,否则放弃索引全表扫描
     5.尽量避免 or 连接条件,否则放弃索引全表扫描
     6.模糊查询尽量避免使用前置%,否则全表扫描
     7.尽量避免使用in 和 not in,否则全表扫描
     8.尽量避免使用 select * ,使用具体字段代替 *,不要返回用不到的任何字段

select * from A inner join B
on A.bid = B.id

Navicat for MySQL
Power Designer - 数据库建模
Microsoft Visio - ER图