MYSQL数据库语法补充2

一，数据库设计范式(原则)

数据库设计三大范式:

第一范式: 保证列的原子性(列不可再分)  反例:联系方式(手机,邮箱,qq)  正例: 手机号,qq,邮箱.

第二范式: 要有主键,其他列依赖于主键列,因为主键是唯一的,依赖了主键,这行数据就是唯一的.

第三范式: 多表关联时,在一个表中,只关联另一个表的主键即可,不需要关联另一个表的非主键列.

二，多表设计：

在项目开发的过程中需要用到很多的数据,把不同的数据存储在不同的表中(分类存储),但数据(表)之间又存在关系

外键:使用外键引用另一个数据表的某条数据,外键列类型与主键列类型保持一致

数据表之间的关联/引用关系是依靠具体的主键(primary key)和外键建立起来的.

举例1：

学生信息与专业信息表

-- 设计 专业信息表,  学生与专业信息表有关系
create table major(
    id int primary key auto_increment,
    name varchar(10),
    major_desc varchar(50)   
)

下列为在初始定义表时没有添加外键

后补加外键

-- 修改表 添加列   najorid 称为外键列, 外键列只能与另一个表的主键关联
alter table student add column majorid int
-- 修改表 删除列
alter table student drop column majorid

-- 修改表,为外键列添加外键约束,保证数据的完整性
                --  添加约束               约束名字                    外键列       与 哪个表的主键列关联          
alter table student add constraint fk_student_major_on_majorid foreign key(majorid) references major(id)
-- 删除外键约束
alter table student drop foreign key fk_student_major_on_majorid

也可以直接创建带有外键约束的学生表

create table student(
 id int primary key not null auto_increment,-- 设置主键
 num int,-- 学号
 name varchar(10),-- 学生姓名
 majorid int,-- 专业外键
 constraint fk_student_major_on_majorid foreign key(majorid) references major(id) -- 添加外键约束
)

约束名规则:fk_foreigntable_primarytable_on_foreigncolumn 其中foreign指含有外键的表,primarytable指外键需要关联的主键所在的表,foreigncolumn指外键列
 

举例2：

学生选课案例 多对多关系

create table course(
   id int primary key auto_increment,
   name varchar(20)
)
-- 创建学生选课表(学生和课程的关系表)
create table student_course(
  studentid int,
  courseid int,
  constraint fk_student_course_student_on_studentid foreign key(studentid) references student(id),
  constraint fk_student_course_course_on_corseid foreign key(courseid) references course(id)
)

三，关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n
        发生原因：没有有效的连接条件
        如何避免：添加有效的连接条件

按照功能分类：

内连接

外连接

        左外连接

        右外连接

子连接

  -- 笛卡尔乘积现象 由于两张表关联没有关联条件,用第一张中的每行与关联表中的每行进行关联
  select s.num,s.name,s.gender,s.majorid,m.id from student s,major m
  -- 解决办法,添加关联条件
  -- 内连接  只把满足条件的数据筛选出来
  -- 写法1(推荐写法)
  select s.num,s.name,s.gender,m.name from student s inner join major m on s.majorid = m.id
  
  -- 写法2:
  select s.num,s.name,s.gender,m.name from student s,major m where s.majorid  = m.id
  
  /*
     写sql方法分析
     1.根据题目分析出数据来源自哪些表
     2.关联条件 哪两个列作为条件关联
     3.确定查询结果
  */
  -- 
  select s.num,s.name,s.gender,m.name from student s inner join major m on  s.majorid = m.id
  
  -- 外连接--左外连接  不管条件是否成立,都会把左边表中的数据全部查询出来,右边表只会查询出满足条件的数据
  select s.num,s.name,s.gender,m.name from student s left join major m on s.majorid = m.id
  
 -- 外连接--右外连接,不管条件是否成立,都会把右边表中的数据全部查询出来,左边表只会查询出满足条件的数据
  select s.num,s.name,s.gender,m.name from student s right join major m on s.majorid = m.id
  
  

四，子查询

含义:出现在其他语句中的select语句,称为子查询或内查询.
一般子查询多用于查询语句(查询语句中再出现一个查询)
分类:
按子查询的位置:
    select后面:支持表量子查询
    from后面:支持表子查询
    where后面:支持标量子查询,列子查询
按功能,结果集的行数不同:
    标量子查询(结果集只有一行一列)
    列子查询(结果集只有一行多列)
    表子查询(结果集一般为多行多列)

-- 标量子查询(查询结果只有一行一列)  适用于select和where后面
select s.num,s.name,(select m.name from major m where m.id = s.majorid) from  student s

select * from student where height = (select max(height) from student) 

-- 列查询(结果集只有一列多行)
select * from student where height in(select height from student where height>1.60 and height<1.99)

-- 表子查询  把一个查询的结果当做一张表,为另一个查询提供数据
-- 一次查询处理不完, 写一个查询进行处理
 select * from (select name,count(*)c from student  group by name )t where t.c>1