JavaWeb-Day-02

1:约束

1.1概念:

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

例如:我们可以给某列加约束,让其值不能重复,不能为null

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

添加约束可以在添加数据的试试就限制不正确的数据,类似于年龄3000岁,数学成绩-12分这样无效的数据,继而保障数据的完整性

1.2分类 

①:非空约束:关键词 NOT NULL

保障列中所有数据不能有null值

添加约束:

-- 创建表时添加非空约束
CREATE TABLE 表名(
   列名 数据类型 NOT NULL,
   …
);

 

 -- 建完表后添加非空约束
    ALTER TABLE 表名 MODIFY 字段名 数据类型 NOT NULL;

 

删除约束

 ALTER TABLE 表名 MODIFY 字段名 数据类型;

②:唯一约束:关键词 UNIQUE

保障列中所有数据各不相同

-- 创建表时添加唯一约束
CREATE TABLE 表名(
   列名 数据类型 UNIQUE [AUTO_INCREMENT],
   -- AUTO_INCREMENT: 当不指定值时自动增长
   …
); 
CREATE TABLE 表名(
   列名 数据类型,
   …
   [CONSTRAINT] [约束名称] UNIQUE(列名)
); 

-- 建完表后添加唯一约束
ALTER TABLE 表名 MODIFY 字段名 数据类型 UNIQUE;

 

删除约束:

ALTER TABLE 表名 DROP INDEX 字段名;

③:主键约束:关键字 PRIMARY KEY

主键是一行数据的唯一表示,要求非空且唯一一般我们都会给每张表添加一个主键列用来唯一标识数据

添加约束:

CREATE TABLE 表名(

列明 数据类型 PRIMARY KEY

[AUTO_INCREMENT]

-- AUTO_INCREMENT: 当不指定值时自动增长

);

CREATE TABLE 表名(
   列名 数据类型,
   [CONSTRAINT] [约束名称] PRIMARY KEY(列名)
); 

-- 建完表后添加主键约束
ALTER TABLE 表名 ADD PRIMARY KEY(字段名)

删除约束

ALTER TABLE 表名 DROP PRIMARY KEY;

④:检查约束： 关键字是 CHECK

保证列中的值满足某一条件。

注意:MySQL不支持检查约束

⑤:默认约束:关键字是 DEFAULT

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

添加约束:

-- 创建表时添加默认约束
CREATE TABLE 表名(
   列名 数据类型 DEFAULT 默认值,
   …
); 

-- 建完表后添加默认约束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默认值;

删除约束

ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

create table emp(
id int primary key,-- 员工姓名,主键约束非空+唯一,自增
ename varchar(50)not null unique,-- 员工姓名，非空且唯一
joindate Date not null ,-- 入职日期，非空
 salary DOUBLE(7,2) not null,  -- 工资，非空
bonus DOUBLE(7,2) default 0  -- 奖金，如果没有将近默认为0

);
-- 验证主键id是否非空且唯一
insert  into emp(id,ename,joindate ,salary ,bonus  )
values(null,'张三','2022-09-01',1000,1000);
-- Column 'id' cannot be null结果

insert  into emp(id,ename,joindate ,salary ,bonus  )
values(1,'张三','2022-09-01',1000,1000),
(2,'李四','2022-09-01',1000,1000);

-- 验证非空约束
insert into emp(id,ename,joindate ,salary ,bonus)
values(3,null,'2022-09-25',1000,1000)
-- Column 'ename' cannot be null

-- 验证默认约束
INSERT INTO emp(id,ename,joindate,salary)
values(3,'王五','1999-11-11',8800);

-- 注意:默认约束只有在不给值时才会采用默认值,如果给了null,那值就是null值

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

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY auto_increment, -- 员工id，主键且自增长
  ename VARCHAR(50) NOT NULL UNIQUE, -- 员工姓名，非空并且唯一
  joindate DATE NOT NULL , -- 入职日期，非空
  salary DOUBLE(7,2) NOT NULL , -- 工资，非空
  bonus DOUBLE(7,2) DEFAULT 0 -- 奖金，如果没有奖金默认为0
);

接下来给emp添加数据，分别验证不给id列添加值以及给id列添加null值，id列的值会不会自动增长：

INSERT INTO emp(ename,joindate,salary,bonus) values('赵六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'赵六3','1999-11-11',8800,null);

总结:
/*
 *  1) 非空约束 可以存储重复的值
 *  2) 默认约束
 *     1) 如果给null 则以给定的null为主  
 *     2) 可以存null
 *     3) 唯一约束
 *         1) 数据必须唯一
 *      2) 可以存null
 *      3) 可以存储多个null    
 *     4) 主键约束=非空+唯一
 
 *  5) 主键自动增长
 *        是数据库提供的一个额外额功能
 *     1) 如果不给值,或者给的是null,或0 , 会自增
 *     2) 如果给非0,非null值 ,则以给定为主,如果已存在则 报错
 *     3) 自增的底层是由一个自增的变量维护的,每次+1,如果给定的值> 自增序列号,则自增号变为给定的值
 *        自增序列号不会回退
 *     
 * */

2:外键约束

1) 外键列: 是一个具体的列
2) 外键约束: 是外键列上的约束(简称:外键)
 

-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
    id int primary key auto_increment,
    dep_name varchar(20),
    addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
    id int primary key auto_increment,
    name varchar(20),
    age int,
    dep_id int,
    -- 添加外键 dep_id,关联 dept 表的id主键
    CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)    
);

# 删除外键约束
alter table emp drop FOREIGN key fk_emp_dept;
# 添加外键约束
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

2.1多表查询:

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

# 创建部门表
    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);

2.2内连接

-- 隐式内连接
SELECT 字段列表 FROM 表1,表2… WHERE 条件;

-- 显示内连接
SELECT 字段列表 FROM 表1 [INNER] JOIN 表2 ON 条件;

2.3外连接

-- 左外连接
SELECT 字段列表 FROM 表1 LEFT [OUTER] JOIN 表2 ON 条件;

-- 右外连接
SELECT 字段列表 FROM 表1 RIGHT [OUTER] JOIN 表2 ON 条件;

 2.4内连接和外连接区别

1) 内连接相当于查询 A B 交集数据
2) 左外连接查询的是左表的所有数据和右边表的管理数据

-- 1.查询所有员工姓名及其所属部门名称 没有部门不显示
/*
 * 1) 先搭架子 select * from
 * 2) 分析题干: 确定需要那几张表,写在from 后,求出笛卡尔积
 * 3) 排除无效数据 where (筛选有效数据)
 * 4) 确定那些列
 * */
    select e.NAME ,d.name  from emp e,dept d  where  e.dep_id =d.did 

-- 2.查询所有员工姓名及其所属部门名称(如果员工没有部门也要显示)
    /*
 * 1) 先搭架子 select * from
 * 2) 分析题干: 确定需要那几张表,写在from 后,求出笛卡尔积(内连接)
 * 3) 排除无效数据 where (筛选有效数据)
 * 4) 考虑是否需要改为外连接
 * 5) 确定那些列
 * */
   select * from emp e left join dept d  on  e.dep_id =d.did 

-- 3.查询所有员工姓名及其所属部门名称薪水(如果员工没有部门也要显示) ,薪水>4000 显示
    /*
 * 1) 先搭架子 select * from
 * 2) 分析题干: 确定需要那几张表,写在from 后,求出笛卡尔积(内连接)
 * 3) 排除无效数据 where (筛选有效数据)
 * 4) 考虑是否需要改为外连接
 * 5) 考虑是否需要继续排除数据
 * 6) 确定那些列
 * */
   select * from emp e left join dept d  on e.dep_id =d.did  where  e.salary >4000
   
   

-- 4. 统计每一个部门的人数 显示部门名称及人数(没有人也要显示)
   
 /* 1) 先搭架子 select * from
 * 2) 分析题干: 确定需要那几张表,写在from 后,求出笛卡尔积(内连接)
 * 3) 排除无效数据 where (筛选有效数据)
 * 4) 考虑是否需要改为外连接
 * 5) 考虑是否需要继续排除数据
 *-6) 分组: 把前面步骤的结果当做一张表使用 进行分组
 *     6.1) 确定分组列 : 写到 group by 后 
 *     6.2) 确定返回的列 : select 后面的字段要么出现在 group by 后,要么出现在聚合函数中
 */
   
   select d.name ,count(e.id) from dept d  left join emp e on e.dep_id =d.did   group by d.name