MySQL二

约束

约束的概念

约束是作用于表中列上的规则,用于限制加入表的数据

约束的存在保证了数据库中正确性,有效性和完整性

分类

非空约束 保证列中所有数据不能有null值 not null

唯一约束 保证类中所有数据各不相同 unique

主键约束 主键是一行数据的唯一标识,要求费控且唯一 primary key

检查约束 保证类中的值满足某一条件 check

默认约束 保存数据时,未指定值则采用默认值 default

外键约束 外键用来让俩个表的数据之间建立链接,保证数据的一致性和完整性 foreign key

Tips

MySQL不支持检查约束

自动增长 auto_increment 当列是数字类型并且 唯一约束

外键练习

-- 创建数据库
/*
主键:非null 且唯一,一般情况每张表都有主键,主键只能有一个
        1,数字型,利用自增(企业很少用!!!!)
                    因为主键使用过后,不能再用了,不能回退,容易出现超出针股份的范围!!!!
                    
        
        2,字符串类型,可以利用java中UUID生成不重复的字符串作为主键(企业中使用)
非null 不能为null
唯一: 不能重复, 但是唯一对null没有限制
默认:如果没给值,给的默认值


*/
-- 创建学生表
create table student(
    sid int primary key auto_increment, -- 主键 自增 
    aname varchar(20) not null, -- 非空
    phone varchar(20) unique, -- 唯一  对null无限制
    age int default 15 -- 默认值15
);

-- 插入数据
insert into student values(null,'张无忌',12233234344,19);
-- insert into student values(null,'张无忌',12233234344,19); -- 手机号重复  但是null可以不受影响
insert into student values(null,'张无忌',null,19);
insert into student values(null,'张无忌',null,19);
insert into student values(null,'张无忌',null,19);
-- 自增会把因为手机号不唯一而出现的错误而使得sid自增出现跳过出现错误的sid  上面2该插入一条数据 因为手机号重复而失败,所以少了2
delete from student where phone is null;
select * from student;
insert into student values(null,'张无忌',null,19);
insert into student values(null,'张无忌',null,19);
insert into student values(null,'张无忌',null,19);
-- 删除前面添加的数据  在添加  sid就不从1开始  而是从6开始 因为前面添加了五条数据(包括未插入的数据),so不好用的,
-- 自增 仅在学习期间使用  以后不使用 因为不灵活也不好用  sid会不连贯


-- 外键 约束表与表主键的数据 去除表中冗余数据分清主从表关系  主->从 少对多 
-- 主表
create table classroom (
    cid int primary key auto_increment,
    cname varchar(30) not null,
    loc varchar(20) 

);
-- 从表
create table stu(
    sid int primary key auto_increment,
    sname varchar(30),
    sage int,
    cid int ,
    foreign key(cid) references classroom(cid) 

);

-- 外键限制了主表的删除和修改!!!   限制了从表的添加和修改
-- 先添加主表的数据  才能添加从表 并且如果主表的cid 和从表的cid添要对应
insert into classroom values(null,'一班','405');

insert into stu values(null,'张无忌',18,1);

-- 如何查询系统给的默认的外键名
show create table stu;

-- 删除外键
alter table stu drop foreign key stu_ibfk_1;

-- 练习多对多
-- 创建餐厅表
create table restrant(
    rid int primary key auto_increment,
    rname varchar(20) not null,
    loc varchar(20)
);
-- 添加数据
insert into restrant values(null,'沙县小吃','大巷路101'),
                                                        (null,'傻子张','大巷路102'),
                                                        (null,'牛肉板面','大巷路103');
-- 创建菜单表
create table  dish(
    did int primary key auto_increment,
    dname varchar(20) not null
);
-- 添加数据
insert into dish values(null,'酸辣土豆丝'),
                                                (null,'锅包肉'),
                                                (null,'鱼香肉丝'),
                                                (null,'爆炒包菜'),
                                                (null,'水煮鱼');

-- 创建中间表 添加外键
create table restrant_dish(
    rid int,
    did int,
    price double(5,2),
    foreign key (rid) references restrant(rid), -- foreign key 后面需要设置外键的列需要加括号
    foreign key (did) references dish(did)
);
-- 添加数据
insert into restrant_dish values(1,1,12.00),
                                                                (1,3,22.00),
                                                                (2,2,45.00),
                                                                (2,4,14.00),
                                                                (3,5,38.00),
                                                                (3,3,18.00);
-- 联合主键  中间表是解决多对多之间的限制过用户繁琐而创建的

多表查询

隐式内连接(表前面已经建好了)

selectt 列 from 表1,表2 where 条件

-- 查询所有菜谱
 select *
 from 
 restrant t1,
 restrant_dish t2,
 dish t3
 where 
 t1.rid = t2.rid and t2.did = t3.did;

显式内连接

select 列 from 表1 inner join 表2 on 条件

SELECT
    * 
FROM
    restrant t1
    INNER JOIN restrant_dish t2 ON t1.rid=t2.rid
    INNER JOIN dish t3 ON t2.did = t3.did;

外连接(左右连接)

左外连接(左表的全部以及交集)

Select 列 from 表1 left outer join 表2 on 条件

右外连接(右表的全部以及交集)

Select 列 from 表1 right outer join 表2 on 条件

insert into restrant values(null,'北舞渡','大行路');
-- 左连接
select * from 
restrant  t1
left outer join 
restrant_dish t2 on
t1.rid = t2.rid
left outer join 
dish t3
on 
t2.did = t3.did;
-- 右连接
select * from 
restrant  t1
right outer join 
restrant_dish t2 on
t1.rid = t2.rid
left outer join 
dish t3
on 
t2.did = t3.did;

子查询

# 创建部门表
    CREATE TABLE dept(
        did INT PRIMARY KEY AUTO_INCREMENT,
        dname VARCHAR(20)
    );

    # 创建员工表
    CREATE TABLE emp (
        id INT PRIMARY KEY AUTO_INCREMENT,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dep_id INT,
        FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外键，关联部门表(部门表的主键)
    );
    -- 添加部门数据
    INSERT INTO dept (dNAME) VALUES ('研发部'),('市场部'),('财务部'),('销售部');
    -- 添加员工数据
    INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES
    ('孙悟空','男',7200,'2013-02-24',1),
    ('猪八戒','男',3600,'2010-12-02',2),
    ('唐僧','男',9000,'2008-08-08',2),
    ('白骨精','女',5000,'2015-10-07',3),
    ('蜘蛛精','女',4500,'2011-03-14',1),
    ('小白龙','男',2500,'2011-02-14',null); 

• 概念

  ==查询中嵌套查询，称嵌套查询为子查询。==

  什么是查询中嵌套查询呢？我们通过一个例子来看：

  需求：查询工资高于猪八戒的员工信息。

  来实现这个需求，我们就可以通过二步实现，第一步：先查询出来 猪八戒的工资

  select salary from emp where name = '猪八戒'
  

  第二步：查询工资高于猪八戒的员工信息

  select * from emp where salary > 3600;
  

  第二步中的3600可以通过第一步的sql查询出来，所以将3600用第一步的sql语句进行替换

  select * from emp where salary > (select salary from emp where name = '猪八戒');
  

  这就是查询语句中嵌套查询语句。

• 子查询根据查询结果不同，作用不同

  • 子查询语句结果是单行单列，子查询语句作为条件值，使用 = != > < 等进行条件判断
  • 子查询语句结果是多行单列，子查询语句作为条件值，使用 in 等关键字进行条件判断
  • 子查询语句结果是多行多列，子查询语句作为虚拟表

• 案例

  • 查询 '财务部' 和 '市场部' 所有的员工信息

    -- 查询 '财务部' 或者 '市场部' 所有的员工的部门did
    select did from dept where dname = '财务部' or dname = '市场部';
    
    select * from emp where dep_id in (select did from dept where dname = '财务部' or dname = '市场部');
    

  • 查询入职日期是 '2011-11-11' 之后的员工信息和部门信息

    -- 查询入职日期是 '2011-11-11' 之后的员工信息
    select * from emp where join_date > '2011-11-11' ;
    -- 将上面语句的结果作为虚拟表和dept表进行内连接查询
    select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where t1.dep_id = dept.did;