JAVA12_01学习总结(MySQL,约束)

今日内容

1. MySql基本查询

--ifnull(字段名称,预期值)
    -如果两个int类型数据求和,其中一个为null,那么结果就是null,使用ifnull来解决

-- 需求--查询姓名和成绩和
-- 创建表
CREATE TABLE test(
    id INT , -- 编号
    NAME VARCHAR(20) , -- 姓名
    age INT , -- 年龄
    math INT , -- 数学成绩
    english INT  -- 英语成绩
)
-- 输入数据
INSERT INTO 
    test 
VALUES 
    (1,'钟离',500,80,NULL) ,
    (2,'胡桃',18,90,100) ;
-- 查询姓名以及数学和英语成绩之和
SELECT 
    NAME '姓名', -- 添加说明,AS可省略
    (math+english) '和' 
FROM 
    test;

 

-- 解决方案,使用ifnull
SELECT
    NAME '姓名',
    (math+IFNULL(english,0)) '和'
FROM
    test;

 

查询年龄是500或者年龄是18的所有信息
    --where条件可以使用
        or 或者
        java语言||
        in(值1,值2.....)

-- where条件可以使用 -or 或者 -java语言|| -in(值1,值2.....)
-- or
SELECT
    *
FROM
    test
WHERE
    age=500 OR age=18 ;
-- ||
SELECT
    *
FROM
    test
WHERE
    age=500 || age=18 ;
-- in
SELECT
    *
FROM
    test
WHERE
    age IN(500,18) ;

 

查询某个字段为null的信息
    ---->is null
查询字段不为null的信息
    ---->is not null

-- 需求:查询英语成绩是null的所有信息
SELECT
    *
FROM
    test
WHERE
    english IS NULL ;

 

查询某个字段不等于某个值 
    ---使用!=
    ---mysql的 <>  

-- 需求:查询年龄不是500岁的所有信息
SELECT
    *
FROM
    test
WHERE
    age != 500 ;
SELECT
    *
FROM
    test
WHERE
    age <> 500 ;

 

2. DQL

2.1 DQL之模糊查询

模糊条件查询
    关键字--like
    语法
        select 字段列表 from 表名 where 字段名称 like '%XX%' --xx是查询条件
            %代表任意多个字符--开发中经常使用
            _代表任意一个字符
            'XX%' 或者 '%XX' 都是可以的

-- 需求:查询表中所有名字里带桃的人的信息
SELECT
    *
FROM
    test
WHERE
    NAME LIKE '%桃%' ;

 

-- 查询学生的姓名是两个字符的人的信息
SELECT
    *
FROM
    test
WHERE
    NAME LIKE '__' ; --两个下划线

 

2.2 DQL之聚合查询

DQL之聚合查询 
    ---结果是单行单例的
        COUNT(字段名称) ---使用最多  统计表的记录
            count里面 字段名称:一般是非业务字段
                --如果字段某个值是null,不会统计
    
        max(列名称):求最大值
        min(列名称):求最小值   
        avg(列名称):求当前列的平均值
        sum(列名称):求当前这列的总和
    聚合函数---完成sql语句嵌套 (单表操作/多表都可以)
    SELECT 
        * 
    FROM
        test 
    WHERE 
    math > 
        (SELECT 
            AVG(math) 
        FROM
            test) ;

2.3 DQL之排序查询

DQL语句之排序查询 
    order by
        单独使用order by
            --select 字段列表 from 表名  order by  字段名称 排序规则;
                排序规则
                    默认asc--升序排序
                       desc--降序
                如果有条件where和order by同时存在
                    --先满足where条件,再排序!

SELECT
    *
FROM
    test
WHERE
    math>70
ORDER BY
    math ASC ,
    age DESC ;

 

2.4 DQL之分组查询

DQL语句之分组查询
    group by
        注意
            1)分组查询里面可以select 查询分组字段 
            2)分组group by后面不能使用聚合函数
        单独使用
            --select 字段列表 from 表名 group by 分组字段名称;
        如果分组查询带where条件
            --where条件和group by同时存在,where条件在group by的前面
            --group by的后面不能使用where,先满足where条件,才参与分组!

2.5 筛选

筛选having
    注意
        having的后面可以跟聚合函数
        having在group by后面 
        where是gruop by的前面

2.6 分页查询

分页查询limit
    语法
        select 字段列表 from 表名 limit 起始行数,每页显示的条数;
            起始行数= (当前页码数-1)*每页显示的条数;
    如果复合查询
        有where条件,还有limit,where在limit前面

3. 约束

约束
    约束用户操作数据库的非法行为

3.1 默认约束

-- 默认约束default
-- 没有插入字段的值默认值null,为了防止这种数据出现,可以在创建表的时候加入默认约束default,当没有插入这个字段,默认约束起作用!
CREATE TABLE testdefault(
    id INT ,
    NAME VARCHAR(20) ,
    sex VARCHAR(3) DEFAULT '女'  -- 性别不填写的话默认女
) ;
INSERT INTO testdefault (id , NAME) VALUES (1 , '钟离') ;
SELECT * FROM testdefault ;

 

3.2 非空约束

-- 非空约束 not null
-- 当前这个值不能为null,不能直接添加数据给一个null
CREATE TABLE testnotnull(
    id INT NOT NULL , -- 序号不能是空,写入值的时候必须赋值
    NAME VARCHAR(20) ,
    sex VARCHAR(3)
);
-- INSERT INTO testnotnull (NAME,sex) VALUES ('钟离','男') ;
-- Field 'id' doesn't have a default value
INSERT INTO testnotnull VALUES (1,'钟离','男') ;
SELECT * FROM testnotnull ;

 

3.3 唯一约束

-- 唯一约束 unique (当前这个值不能重复)
-- 限制id字段/有效身份信息(邮箱/手机号/身份证...)
CREATE TABLE testunique(
    id INT ,
    NAME VARCHAR(20) ,
    number INT UNIQUE -- 这个值输入时不能有重复!
);
-- insert into testunique values (1,'钟离',100773429),(2,'离',100773429) ;
-- Duplicate entry '100773429' for key 'number'
INSERT INTO testunique VALUES (1,'钟离',100773429),(2,'离',100773430) ;
SELECT * FROM testunique ;

 

3.4 主键约束

-- 主键约束primary key 
-- 特点非空且唯一
CREATE TABLE testprimarykey(
    id INT PRIMARY KEY , -- 主键,非空且唯一
    NAME VARCHAR(20) ,
    age INT
);
INSERT INTO testprimarykey VALUES (1,'钟离',15) ;
-- INSERT INTO testprimarykey VALUES (1,'钟',15) ;
-- Duplicate entry '1' for key 'PRIMARY'
-- INSERT INTO testprimarykey VALUES (null,'钟离',15) ;
-- Column 'id' cannot be null

 

3.5 自增长约束

-- 自增长约束 auto_increment
-- 一般自增长约束都是在主键字段上,保证唯一
-- 指定插入数据的值,下次在之前的值上继续自增1
CREATE TABLE testauto_increment(
    id INT PRIMARY KEY AUTO_INCREMENT , -- 一般主键和自增连用
    NAME VARCHAR(20) 
);
INSERT INTO testauto_increment (NAME) VALUES ('钟离');
INSERT INTO testauto_increment (NAME) VALUES ('钟离');
INSERT INTO testauto_increment (NAME) VALUES ('胡桃');
SELECT * FROM testauto_increment ;
​
-- mysql自带函数---查找数据库表中最后一次自增主键的值是多少
SELECT LAST_INSERT_ID();

 

3.6 外键约束

-- foreign key
/*问题:
        1)查询员工的所有信息---部门名称字段 冗余度大(重复度高)
        2)一张表描述了两个事情(员工信息,又有部门信息!)
        
        解决方案
            将这张表拆分两张表
            一张表员工信息
            一张表描述部门信息
*/
-- 创建一张部门表
CREATE TABLE dept(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
    NAME VARCHAR(10) -- 部门名称
);
INSERT INTO dept(NAME) VALUES('往生堂'),('屋漏茶室'),('烟花店');
-- 创建一个员工表
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
    NAME VARCHAR(10) , -- 员工姓名
    gender VARCHAR(3) , -- 性别
    dept_id INT -- 部门编号
);
INSERT INTO employee(NAME,gender,dept_id)
VALUES
('钟离','男',1),
('胡桃','女',1),
('夜阑','女',2),
('申鹤','女',2),
('宵宫','女',3) ;
-- 插入了一个条数据:没有4号部门,但是依然能插入进去!
-- 防止出现问题,添加外键约束---让两个产生关联关系
-- 创建员工表的时候,同时添加外键约束!
/*
    外键作用的表---从表
    另一张表---主表
    创建表的时候添加外键
        constraint (声明)
                外键名
        foriegn key 
                (从表的字段名称)
        references -- (关联)
                主表名(主键字段的名称)
*/
CREATE TABLE employee(
    id INT PRIMARY KEY AUTO_INCREMENT, -- 编号
    NAME VARCHAR(10),  -- 姓名
    gender VARCHAR(3), -- 性别
    dept_id INT ,-- 部门编号
    CONSTRAINT -- 声明
            dept_emp_fk -- 外键名称
    FOREIGN KEY
            (dept_id) -- 作用在指定外键字段上
    REFERENCES -- 关联
            dept(id) -- 主表的主键字段
    
);
-- 有了外键之后,直接修改或者删除主表数据,前提需要让从表的数据跟主表没有关联
-- 这个时候才能修改和删除主表数据!
​
-- 所以我们引出了!!!
​
-- 级联操作cascade
-- 级联删除/级联修改 on delete cascade /on update casade
-- 当修改/删除主表的数据,从表数据随之改动 
​
​
-- 将上面这个语句放在外键的后面
-- 添加外键的同时---添加级联修改和级联删除
ALTER TABLE employee 
ADD CONSTRAINT dept_emp_fk 
FOREIGN KEY (dept_id) 
REFERENCES dept(id) 
ON UPDATE CASCADE 
ON DELETE CASCADE ;
​
-- 直接修改主表数据,
-- 将1号部门改成2号部门,这个时候1号部门的员工,它部门编号变成2
UPDATE dept SET id = 2 WHERE id =1 ;
-- 直接删除主表数据,从表随之变化
-- 将3号部门解散
DELETE FROM dept WHERE id = 3 ;