MySQL (2)

发表于 | 分类于

约束：规定数据库表中的数据应该如何存在

主键约束

关键字：primary key
主键：被约束的字段，内容不能重复，不能为null
声明方式：

  1. 声明字段时，添加约束

    create table primarykey01(
    id varchar(32) primary key,                     #只能有一个字段是主键
    content varchar(50)
);

  1. 声明字段之后，在约束区域添加主键

    create table primarykey02(
    id varchar(32),
    content varchar(50),
    constraint primary key (id)            # 可以将多个字段组合在一起，形成联合主键 primary key (字段1,字段2)
);
* 联合主键 ：多个字段组合不重复
    u001 c001  100
    u001 c002  200

  2. 创建表之后，修改表结构添加主键

    create table primarykey03(
    id varchar(32),
    content varchar(50)
);
# 修改表结构
alter table primarykey03 add constraint primary key (id);

  3. 测试数据

    insert into primarykey03(id) values('u001');        #录入，content=null
insert into primarykey03(content) values('xxx');    #录入，id='' 空字符串
insert into primarykey03(content) values('xxx');    #错误
    * 提示：ERROR 1062 (23000): Duplicate entry '' for key 'PRIMARY' # 重复

唯一约束

关键字：unique
唯一约束：被约束的字段不能重复
声明方式：

  1. 声明字段时，添加约束

    create table unique01(
    id varchar(32) unique,
    content varchar(50)
);

  2. 声明字段之后，在约束区域添加主键

    create table unique02(
    id varchar(32),
    content varchar(50),
    constraint unique (id)    
);

  3. 创建表之后，修改表结构添加主键

    create table unique03(
    id varchar(32),
    content varchar(50)
);
# 修改表结构
alter table unique03 add constraint unique (id);

  4. 测试：

    insert into unique03(content) values('xxx');    #录入，id=null
insert into unique03(content) values('xxx');    #录入，id=null，重复不包含null

insert into unique03(id) values('u001');        #录入
insert into unique03(id) values('u001');        #错误，id重复
    * 提示：ERROR 1062 (23000): Duplicate entry 'u001' for key 'id'

非空约束

关键字：not null
非空：被约束的字段内容不能为null

  1. 声明：

    * 在定义字段时，声明字段为非空
    create table notnull01(
        id varchar(32) not null,
        content varchar(50)
    );

  2. 测试：

    insert into notnull01(content) values('xxx');    #错误，
    * 提示：ERROR 1364 (HY000): Field 'id' doesn't have a default value
insert into notnull01(id,content) values('n001','xxx');

  3. 总结：主键 = 唯一 + 非空

mysql 特有的字段，自动增长列

关键字： auto_increment
自动增长列： 被修饰的字段可以进行自动累加，每次录入一条数据自动加1
要求：
  1. 必须是整形
  2. 必须是键（一般是主键）

  3. 举个例子： 

    create table auto(id int primary key auto_increment,content varchar(50));

  4. 关于删除

    delete from auto;  #删除表中数据，但是不能重置自动增长列的记录数；
truncate table auto; # 删除表中数据，并且重置自动增长数列

外键约束

关键字 ： foreign key

多表操作

表的关系
  1. 一对多，多对一
    • 实例：
      • 国家(1) – 城市(*)
      • 学生(1) – 书籍(*)
    • 主键外键描述关系
      • alter table 从表 add constraint foreign key(从表外键) refrences 主表(主表主键);

  2. 多对多

    • 实例
      • 用户(） – 角色()
    • 分析
      • 将多对多 分解成两个一对多
      • 需要中间表，其实就是两张表的从表

  3. 一对一

    • 主表的主键，与从表的外键(唯一),形成主外键的关系
    • 主表的主键，与从表的主键，形成主外键关系
一对多，多对一

  1. 主表 主键

    create table t_student(id varchar(32) primary key,name varchar(50),age int);

  2. 从表 外键

    create table t_book(id varchar(32),title varchar(100),price double(5,2),student_id varchar(32));

  3. 用主键外键关系描述出一对多关系

    alert table t_book add constraint foreign key (student_id) references t_student (id);

  4. 一些操作

    1. 录入数据

      insert into t_book(id,title,price) values('b001','java web',100);#录入：外键的值可以为null
insert into t_book(id,title,price,student_id) values('b001','java web',100,'s001'); #错误，s001在主表中不存在

insert into t_student(id,name,age) values('s001','jack',20); #录入
insert into t_book(id,title,price,student_id) values('b002','android',998,'s001'); #录入
delete from t_student where id = 's001'; #错误，从表中使用s001数据

insert into t_student(id,name,age) values('s002','rose',23);#录入
delete from t_student where id = 's002';#删除
多对多的表

  1. 创建主表 – 学生表

    1
    create table m_student(id varchar(32) primary key,name varchar(50),age int);

  2. 创建从表 – 课程表

    1
    create table m_course(id varchar(32) primary key,content varchar(50),teacher varchar(20));

  3. 创建中间表 –两个外键

    1
    create table m_student_course(student_id varchar(32),course_id varchar(32));

  4. 中间表与学生表 主外键关系

    1
    alter table m_student_course add constraint foreign key (student_id) refrenses m_student (id);

  5. 中间表与课程表 主外键关系

    1
    alter table m_student_course add constraint foreign key(course_id) references m_course (id);

  6. 中间表 生成联合主键

    1
    alter table m_student_course add constraint primary key (student_id,course_id);
连接查询

  1. 笛卡尔积（多个表的乘积）

    select * from table1,table2;

  2. 隐式内连接

    1
    select * from t_book,t_student on t_student.id=t_book.student_id;

  1. 内连接

    1
    2
    select ... from 表1 inner join 表2 on 条件；
    select * from t_book inner join t_student on t_student.id=t_book.student_id;

  1. 左外连接

    1
    2
    3
    4
    #格式：select ... from 表1 lefter outer join 表2 on 条件；
    #查询表1所有的数据，表2内容是否显示，取决于on条件
    #实例：统计书籍的借阅情况
    select * from t_book left outer join t_student on t_student.id=t_book.student_id;
  1. 右外连接(和左外连接正好相反)
一对一的关系

  1. 主表的主键，与从表的外键(唯一),形成主外键的关系

    1
    2
    3
    create table company(id varchar(32) primary key,name varchar(50));
    create table address(id varchar(32),addr varchar(50),company_id varchar(32) unique);
    alter table address add constraint foreign key(company_id) refrences company(id);

  2. 主表的主键，与从表的主键，形成主外键关系