外键约束和查询进阶

一、外键约束

1.添加和删除约束

• 添加和删除普通约束

  1）添加约束 ：alter table 表名 add constraint 约束名 约束 字段

  alter table t_students add constraint name_unique unique (stu_name)
  

  2)删除约束：

  alter table s_students drop constraint name_unique
  

• 添加和删除外键约束

  ER图（实体关系图）

  表和表之间的对应：一对一、多对一、多对多

  外键：表中保存另外一张表的主键的字段

  怎么添加外键约束：

  一对一，可以将外键添加到任意表中

  一对多，将外键添加到n所在的一方

  多对多，需要创建一张新的表，新的表中有两个外键

  创建外键约束

  foreign key(外键) reference 依赖表名（依赖表的主键）
  

  表创建好之后添加外键

  alter table t_students add constraint  score_stu foreign key(stu_id) references t_student(stu_id)
  

  删除外键

  alter table t_students drop foreign key score_stu;
  

二、查询进阶

1.对列重命名

select stuname as 姓名 from t_student

2.对查询结果重新赋值

• mysql专有:if(条件，值1，值2)

  select if(stusex,'男','女') from t_student
  

• sql通用 case 字段 when 条件 then 值1 else 值2 end

  select case stusex when 1 then '男' else '女' end from t_student
  

3.对列合进行合并

concat(字段1,字段2)结果跟随括号内顺序

select concat(stuname,stuaddr) from t_student

4.模糊查询 - 查询的时候通过like条件来指定查询对象

• %：任意内容出现任意次数
• _:任意字符出现一次

select stuname from t_student where like stuname like '王%'
select stuname from t_student where like stuname like '王_'

5.排序

select stuname from t_student order by stubirth;   -- 升序排列
select stuname from t_student order by stubirth desc; -- 降序

6.将日期转换成年龄

计算两个日期相差天数：datediff(日期1，日期2)

计算年龄的公式：datediff(curdate(),出生日期) div 365

select stuname,stubirth,datediff(curdate(),stubirth) as age div 365;

7.去重 - 只能获取去重的字段值

select distinct teaid from tb_course;

8.限制和分页 - 获取表中的部分数据

select * from t_student limit 5;   -- 获取前五行记录
select * from t_student limit 4,5  -- 跳过前面四行，获取后面五行记录
select * from t_student limit 5 offset 4 跳过前面四行，获取后面五行

9.聚合

聚合函数：max()/min()/sum/avg()/count()

聚合函数在没有分组的时候使用是没有意义的

select max(score) as max_score from t_record

13.分组 - 将指定指端值作为一个分组

select sid,avg(score) as avg_score from t_record group by(sid)

如果查询中有分组操作，加条件进行筛选的时候用having代替where

select sid,avg(score) as avg_score from t_record group by(sid) having avg_score > 80