MYSQL数据库基础（四多表查询、内外连接、update、约束、）

偏执的太偏执、 2021-11-17 12:42 385阅读 0赞

## 单张表的查询复习： ##

select.. from 表名 where 查询条件 group by 分组 having   通过分组后需要判断的条件 ... order by  ....asc/desc

## 多张表的查询学习： ##

select ... from 表1  ,表2 where 连接条件   group by..  having...order by... asc/desc

连接条件必须是 主表的主键=外表的外键

-- 92标准的表连接查询 where之的条件称为连接条件  N 张表进行表连接查询，连接条件为N-1
    -- 查询员工的编号，姓名，部门名称和部门所在地
    select empno,ename,dname,loc ,d.deptno from emp e,dept d where d.DEPTNO=e.DEPTNO;
    -- 查询每个部门的总人数 ，排除少于4人的部门 ,按人数降序
    select d.deptno, d.dname ,count(*) from emp e, dept d
    where e.deptno is not null   and e.DEPTNO=d.DEPTNO
     group by dname having count(*)>=4 order by count(*) desc;

##   多张表的第二种查询方法： ##

使用join的好处是因为只用where来进行连接，会把条件语句和连接语句放在一起，比较混乱

select ..... .from  表1  inner join 表二   on  连接条件   \[where\] ..\[group by\] ..\[having\]..\[order by\]

select empno,ename,dname,loc,d.deptno from emp e 
    inner join dept d  on e.deptno=d.deptno;
    select d.deptno, d.dname ,count(*) from emp e inner join dept d
     on e.DEPTNO=d.DEPTNO  where e.deptno is not null   
     group by dname having count(*)>=4 order by count(*) desc;

## 外连接查询 ##

select.....from   表一  left/right join   表二   on  连接条件

## 我们要对内连接、外连接做一个比较 ##

内连接：就是取两个比的交集，只比较两个表之间共有的部分。

左外连接：左边大于右边，也就是说 按照左边的情况进行比较，如果右边表没有左边对应的数据，则对应为null

用左边基准表的数据去匹配右表的数据，所以左表的记录是全部会查询出来的，如果右表没有记录对应的话就显示null

右外连接：右边大于左边，以右边表为基准进行比较，右边的基准表的数据全部会出来的，对应不到左边的数据，就显示为null

## 创建表，添加主键 ##

create table  表名（

列名11   数据类型   primary key   auto\_increment ,    --  主键 并且自动增长  并且自动增长的类型必须是 int

列名  数据类型

）；

create table  表名2（

列名1  数据类型，

列名2 数据类型

）；

## 我想要把列名1设置为外键与 第一个表的列名11相关联，列名11为主键，列名1为外键 ##

**对其添加约束，设置外键！**

**alter table 表面2 add constraint   外键名称  foreign key   列名1   references   表名1（列名11）**

## 表中添加数据 ##

insert into 表名 （  ，  ，  ，）values（  ，  ，  ，）

必须要对应相应的数据，第一个括号是列名   第二个括号是数据内容，必须要一 一对应。

## 约束： ##

1：主键约束：一个表只能添加一个主键，并且逐渐不允许重复，不允许为空

--如何为表添加复合主键呢？
    create table stu3(
       stuid int(4),
       stuname varchar(20)
    );
    alter table stu3 add CONSTRAINT pk_stuid_stuname primary key (stuid,stuname);

2：非空约束   not null

select * from stu2;
     
    --需要为表stu2添加一个性别列，要求性别不允许为null
     --第一种添加非空约束的方式，适用于已存在的表 (表中有数据)
    alter table stu2 add gender varchar(2) not null;
    -- gender doesn't hava t default value 性别不允许为null
    insert into stu2 (stuid,stuname) values (2,'李四'); 
    insert into stu2 (stuid,stuname,gender) values (3,'李四','男'); 
    -- 第二种添加非空约束 创建表结构时同时添加
    create table stu4(
      stuid int(4) primary key,
      stuname varchar(20) not null,
      gender varchar(2) not null
    );
    insert into stu4 (stuid,stuname) values (2,'李四'); --无法执行

3：唯一约束   unique

--唯一约束
    create table stu5(
     stuid int(4) primary key,
     stuname varchar(20) not null,
     identify varchar(18) unique
    );
    --添加测试数据
    insert into stu5 values (1,'张三','2202876634');--成功
    insert into stu5 values (2,'张三2','2202876634'); -- entry '2202876634' for key 'identify'
    insert into stu5 (stuid,stuname) values (3,'张三3');
    insert into stu5 (stuid,stuname) values (4,'张三4');
    select * from stu5;
    --唯一约束一个表中可以有N多个，添，必须唯一，不添则为null，而且可以有多个null
    alter table stu5 add phone varchar(11);
    alter table stu5 add constraint uk_phone unique (phone);
    insert into stu5 (stuid,stuname,phone) values (5,'李四','13200000');--成功
    insert into stu5 (stuid,stuname,phone) values (6,'李四2','13200000');-- entry '13200000' for key 'uk_phone'

## 主键约束与unique约束的区别： ##

1：一个表中可以有多个unique约束唯一，但是只能有一个主键。

2：主键不能为空，但是unique可以为空

### 默认约束：  default ###

### 自增      auto\_increment ###

--默认约束
    create table stu1(
     stuid int(4) primary key,
     stuname varchar(20) not null,
     gender varchar(2) default '男'
    );
    --添加测试数据
    insert into stu1 (stuid,stuname) values (1,'张三');
    select * from stu1;
    --修改表结构
    alter table stu1 add phone varchar(11) default '010-888888';   --表里原有数据添入默认值
    insert into stu1 (stuid,stuname,gender) values (2,'张三','女');
    --修改表结构
    alter table stu1 add identify varchar(18) ;
    alter table stu1 modify identify varchar(18) default '2202870000';  --新添加的数据才有默认值
    insert into stu1 (stuid,stuname,gender) values (3,'张三','女');
    --自增列   auto_increment  （要求：必须具有约束主，外，唯一） int类型,通常配合主键一起使用
    --创建一张“具有完整性”约束的表
    字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
    学号是主键 = 不能为空 +  唯一
    姓名不能为空
    性别默认值是男
    年龄范围18---30岁
    Email唯一
    create table student2(
      stuno int(4) primary key auto_increment,
      stuname varchar(20) not null,
      gender varchar(2) default '男',
      age int (4) ,
      hiredate date ,
      clazzid int(4),
      email varchar(20) unique
    );
    create table clazz2(
      clazzid int(4) primary key auto_increment,
      clazzname varchar(20) not null
    );
    alter table student2 add constraint fk_clazzid foreign key (clazzid)
    REFERENCES clazz2 (clazzid);

### DML :数据操作语言  insert,update ,delete    insert :    语法结构       (1)向表中全部列添加测试数据         insert into 表名 values (值列表....); ###

###    (2)向表中的部分列添加测试数据        insert into 表名 (字段列表) values (值列表) ###

select * from clazz2;
    select * from student2;
    --insert 
    -- (1)向表中全部列添加测试数据
    insert into clazz2 values (1,'jn505');  --成功
    -- (2)向表中的部分列添加测试数据
    insert into student2 (stuno,stuname,clazzid) values (1,'张三',1);
    -- 注意事项
      --(1)向表中全部列添加测试数据，要求值的顺序与列的顺序必须完全一致
    --  Incorrect integer value: 'jn509' for column 'clazzid' at row 
        insert into clazz2 values ('jn509',2); --值的顺序与列的顺序不一致
      --(2)向表中部分列添加测试数据时，要求值的个数与列的个数完全致
      --  Column count doesn't match value count at row 1
     insert into student2 (stuno,stuname,clazzid) values (2,'张三');
     insert into student2 (stuno,stuname) values (3,'张三',1);
       --(3)值超范围 
      -- Data too long for column 'clazzname' at row 1
       insert into clazz2 values (3,'abcabcabcabcabcabcabc');
      
      --(4) 主键不允许重复
       --Duplicate entry '1' for key 'PRIMARY'
       
        insert into clazz2 values (1,'jn509');
        insert into clazz2 (clazzname) values ('jn509'); --主键自动增长
        --(5)非空列必须添数据
      -- Field 'stuname' doesn't have a default value
        insert into student2 (gender,age) values ('女',20);
        insert into student2 (gender,age,stuname) values ('女',20,'李四');

### update:修改 表中的数据    语法 :     update 表名 set 列名=值 ,列名=值,...  \[where\].... ###

###  删除语法结构     delete from 表名 \[where\] ###

--修改数据   
      -- 赋值运算符和关系运算符
       -- set 之后的"="为赋值运算符
       -- where 之后的"="为关系运算符 ，等值判断
     update student2 set stuname='王五' ,gender='男' where stuno=3;
    --删除数据  (只删除数据，不能删除表结构)
     delete from student2;   -- （删除表中全部数据）
     delete from clazz2 where clazzid=7;
      
     --比delete删除数据还有快， （只限于删除表中 全部数据 ）
    select * from student;
    truncate table student;