数据库相关概念

MySQL数据库

关系型数据库（RDBMS）:
    概念：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。
    特点：
        1.使用表存储数据，格式统一，便于维护
        2.使用SQL语言操作，标准同意，使用方便

SQL

SQL通用语法

1.SQL语句可以单行或多行书写，以分号结尾。
2.SQL语句可以使用空格/缩进来增强语句的可读性。
3.MySQL数据库的SQL语句不区分大小写，关键字建议使用大写。
4.注释：
    单行注释： --注释内容 或 #注释内容
    多行注释： /*注释内容*/

SQL分类

DDL:

DDL-数据库操作

查询：
    查询所有数据库：SHOW DATABASES;
    查询当前数据库：SELECT DATABASE();
创建：
    CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
删除：
    DROP DATABASE [IF EXISTS] 数据库名;
使用：
    USE 数据库名;

DDL-表操作-查询

查询当前数据库所有表：
    SHOW TABLES;
查询表结构：
    DESC 表名;
查询指定表的建表语句：
    SHOW CREATE TABLE 表名;

DDL-表操作-创建

CREATE TABLE 表名(
    字段1 字段1类型 [COMMENT 字段1注释],
    字段2 字段2类型 [COMMENT 字段2注释],
    字段3 字段3类型 [COMMENT 字段3注释],
    ......
    字段n 字段n类型 [COMMENT 字段n注释]
) [COMMENT 表注释];

ps:[......]为可选参数，最后一个字段后面没有逗号

DDL-表操作-数据类型

MySQL中的数据类型有很多，主要分为三类：数值类型、字符串类型、日期时间类型。

DDL-表操作-修改&删除

修改：
    添加字段
        ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
    
    修改数据类型
        ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
    修改字段名和字段类型
        ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
    
    修改表名
        ALTER TABLE 表名 RENAME TO 新表名;
    
删除：
    删除字段
        ALTER TABLE 表名 DROP 字段名;
        
    删除表
        DROP TABLE [IF EXISTS] 表名;
    删除指定表，并重新创建该表：
        TRUNCATE TABLE 表名;
    
ps:再删除表时，表中的全部数据也会被删除。

DML：

DML-添加数据

1.给指定字段添加数据：
    INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...);
2.给全部字段添加数据：
    INSERT INTO 表名 VALUES (值1,值2,...);
3.批量添加数据：
    INSERT INTO 表名 (字段名1,字段名2,...) VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
    INSERT INTO 表名 VALUES (值1,值2,...),(值1,值2,...),(值1,值2,...);
    
PS:插入数据时，指定的字段顺序需要与值的顺序是一一对应的。
   字符串和日期型数据应该包含在引号中。
   插入的数据大小，应该在字段的规定范围内。

DML-修改数据

UPDATE 表名 SET 字段名1 =值1,字段名2 =值2,...[WHERE 条件];
PS:修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

DML-删除数据

DELETE FROM 表名 [WHERE 条件]
PS:
    DELETE语句的条件也可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。
    DELETE语句不能删除某一个字段的值(可以使用UPDATE)。

DQL:

DQL-语法

基本查询：
    SELECT
        字段列表
    FROM
        表名列表
条件查询：
    WHERE
        条件列表
聚合函数：（count、max、min、avg、sum）
分组查询：
    GROUP BY
        分组字段列表
    HAVING
        分组后条件列表
排序查询：
    ORDER BY
        排序字段列表
分页查询：
    LIMIT
        分页参数

DQL-基本查询

1.查询多个字段
    SELECT 字段1,字段2,字段3...FROM 表名；
    SELECT * FROM 表名;
2.设置别名
    SELECT 字段1 [AS 别名1],字段2 [AS 别名2] ... FROM 表名;
3.去除重复记录
    SELECT DISTINCT 字段列表 FROM 表名;

DQL-条件查询

1.语法
    SELECT 字段列表 FROM 表名 WHERE 条件列表;
2.条件
    

DQL-聚合函数

1.介绍
    将一列数据作为一个整体，进行纵向计算。
2.常见聚合函数

3.语法
    SELECT 聚合函数(字段列表) FROM 表名;
    
    注意：NULL值不参与所有聚合函数运算

DQL-分组查询

1.语法
    SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
    
2.where与having区别
    执行时机不同：
        where是分组之前进行过滤，不满足where条件，不参与分组;而having是分组之后对结果进行过滤
    判断条件不同：
        where不能对聚合函数进行判断，而having可以

注意：
    执行顺序：
        where > 聚合函数 > having
    分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义

DQL-排序查询

1.语法
    SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
2.排序方式
    ASC:升序（默认值）
    DESC：降序
    
注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

DQL-分页查询

1.语法
    SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;
    
注意：
    起始索引从0开始，起始索引=（查询页码-1） * 每页显示记录数
    分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT
    如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10

DQL-执行顺序

DCL：

DCL-介绍

DCL英文全称是Data Control Language（数据控制语言），用来管理数据库用户、控制数据库的访问权限

DCL-管理用户

查询用户
    USE mysql;
    SELECT * FROM user;
创建用户
    CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
    ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
    DROP USER '用户名'@'主机名';

注意：
    主机名可以使用%通配
    这类SQL开发人员操作的比较少，主要是DBA(Database Administrator 数据库管理员)使用

DCL-权限控制

查询权限
    SHOW GRANTS FOR '用户名'@'主机名';
授予权限
    GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
    REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意：
    多个权限之间，使用逗号分隔
    授权时，数据库名和表名可以使用*进行通配，代表所有

实践

-- 基础查询
-- 查询指定字段返回
select name, workno, age from emp;

-- 查询所有字段返回
select id, workno, name, gender, age, idcard, workaddress, entrydate from emp;

select * from emp;

-- 查询所有工作地址（取别名）
select workaddress as '工作地址' from emp;

-- 查询公司员工上班地址（不能重复）
select distinct workaddress from emp;

-- 条件查询
-- 查询所有年龄等于18的员工
select * from emp where age = 18;

-- 查询所有年龄小于20的员工
select * from emp where age < 20;

-- 查询所有年龄小于或等于20的员工
select * from emp where age <= 20;

-- 查询所有没有身份证号的员工
select * from emp where idcard is null;

-- 查询所有有身份证的员工
select * from emp where idcard is not null;

-- 查询所有年龄不等于20的员工
select * from emp where age != 20;

select * from emp where age <> 20;

-- 查询所有年龄在15至20岁之间的员工
select * from emp where age >= 15 && age <= 20;

select * from emp where age >= 15 and age <= 20;

select * from emp where age between 15 and 20;

-- 查询所有性别为女且年龄小于25的员工
select * from emp where gender = '女' and age < 25;

-- 查询所有年龄为18、20、40的员工
select * from emp where age = 18 or age = 20 or age = 40;

select * from emp where age in(18, 20, 40);

-- 查询所有名字为两个字的员工
select * from emp where name like '__';
-- 查询所有身份证尾号为X的员工
select * from emp where idcard like '%X';

select * from emp where idcard like '_________________X';

-- 聚合函数
-- 统计企业员工数量
select count(*) from emp;

select count(id) from emp;

-- 统计企业员工平均年龄
select avg(age) from emp;

-- 统计企业员工的最大年龄
select max(age) from emp;

-- 统计企业员工的最小年龄
select min(age) from emp;

-- 统计北京地区员工年龄之和
select sum(age) from emp where workaddress = '北京';

-- 分组查询
-- 根据性别分组，统计男性员工和女性员工的数量
select gender, count(*) from emp group by gender;

-- 根据性别分组，统计男性员工和女性员工的平均年龄
select gender, avg(age) from emp group by gender;

-- 查询年龄小于45的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress, count(*) from emp where age < 45 group by workaddress having count(*) >= 3;

select workaddress, count(*) address_count from emp where age < 45 group by workaddress having address_count >= 3;

-- 排序查询
-- 根据年龄对公司员工进行升序排序
select * from emp order by age asc;

-- 根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc;

-- 根据年龄对公司员工进行升序排序，年龄相同，再按照入职时间进行降序排序
select * from emp order by age asc, entrydate desc;

-- 分页查询
-- 查询第1页员工数据，每页展示10条记录
select * from emp limit 0, 10;

select * from emp limit 10;

-- 查询第2页员工数据，每页展示10条记录
select * from emp limit 10, 10;

-- -------------------------------- DQL 语句练习 ----------------------------------
-- 查询年龄为20， 21， 22， 23，的女性员工信息
select * from emp where gender = '男'
                    and age between 20 and 23;

-- 查询性别为男，并且年龄在20-40岁（含）以内的姓名为三个字的员工
select * from emp where name like '___'
                    and gender = '男'
                    and age between 20 and 40;

-- 统计员工表中，年龄小于60岁的，男性员工和女性员工的人数
select gender, count(*) from emp where age < 60 group by gender;

-- 查询所有年龄小于等于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，如果年龄相同按入职时间降序排序
select name, age from emp where age <= 35 order by age asc , entrydate desc;

-- 查询性别为男，且年龄在20-40岁（含）以内的前5个员工信息，对查询的结果按年龄升序排序，年龄相同按入职时间升序排序
select * from emp where age between 20 and 40
                    and gender = '男'
                    order by age asc, entrydate asc
                    limit 5;

-- 查询年龄大于15的员工的姓名、年龄，并根据年龄进行升序排序
select name, age from emp where age > 15 order by age asc;

-- DCL
-- 创建用户 itcast, 只能在当前主机localhost访问， 密码123456
create user 'itcast'@'localhost' identified by '123456';

-- 创建用户heima, 可以在任意主机访问数据库，密码123456
create user 'heima'@'%' identified by '123456';

-- 修改用户 heima 的访问密码为 1234
alter user 'heima'@'%' identified with mysql_native_password BY '1234';

-- 删除itcast@localhost用户
drop user 'itcast'@'localhost';

-- 查询权限
show grants for 'heima'@'%';

-- 授予权限
grant all on itcast.* to 'heima'@'%';

-- 撤销权限
revoke all on itcast.* from 'heima'@'%';

函数

函数是指一段可以直接被另一段程序调用的程序或代码

字符串函数

SELECT 函数（参数）;

数值函数

日期函数

流程控制函数

实践

--------------------- 函数演示 ---------------------
-- 字符串函数
-- concat
select concat('Hello', 'MySQL');

-- lower
select lower('Hello');

-- upper
select upper('Hello');

-- lpad
select lpad('01', 5, '-');

-- rpad
select rpad('02', 7, '-');

-- trim
select trim(' Hello MySQL ');

-- substring
select substring('Hello MySQL', 1, 5);

--------------- 案例 ---------------
-- 由于业务需求变更，企业员工的工号统一为5位数，目前不足5位数的全部在前面补0。比如：1号员工的工号应该为00001

update emp set workno = lpad(workno, 5, '0');

-- 数值函数
-- ceil
select ceil(1.5);

-- floor
select floor(1.4);

-- mod
select mod(3, 4);

-- rand
select rand();

-- round
select round(2.3456, 2);

--------------- 案例 ---------------
-- 通过数据库函数生成一个六位数的随机验证码
select lpad(round(rand()*1000000, 0), 6, '0');

-- 日期函数
-- curdate()
select curdate();

-- curtime()
select curtime();

-- now()
select now();

-- YEAR, MONTH, DAY
select YEAR(now());

select MONTH(now());

select DAY(now());

-- date_add
select date_add(now(), INTERVAL 70 DAY);

-- datediff
select datediff('2021-12-01', '2021-10-30');

--------------- 案例 ---------------
-- 查询所有员工的入职天数，并根据入职天数倒序排序
select name, datediff(curdate(), entrydate) as'entrydays' from emp order by entrydays desc;

-- 流程控制函数
-- if
select if(true, 'OK', 'ERROR');
select if(false, 'OK', 'ERROR');


-- ifnull
select ifnull('OK', 'Default');
select ifnull('', 'Default');
select ifnull(null, 'Default');

-- case when then else end
select
    name,
    (case workaddress when '北京' then '一线城市' when '上海' then '一线城市' else '二线城市' end) as '工作地址'
from emp;

--------------- 案例 ---------------
-- 统计班级各个学员的成绩，展示的规则如下：
-- >=85，展示优秀
-- >=60，<85，展示及格
-- 否则展示不及格
create table score(
    id int comment 'ID',
    name varchar(20) comment '姓名',
    math int comment '数学',
    english int comment '英语',
    chinese int comment '语文'
)comment '学员成绩表';

insert into score(id, name, math, english, chinese) VALUES (1, 'Tom', 67, 88, 95), (2, 'Rose', 23, 66, 90), (3, 'Jack', 45, 78, 97);

select
    id,
    name,
    (case when math >= 85 then '优秀' when math >= 60 then '及格' else '不及格' end) '数学',
    (case when english >= 85 then '优秀' when english >= 60 then '及格' else '不及格' end) '英语',
    (case when chinese >= 85 then '优秀' when chinese >= 60 then '及格' else '不及格' end) '语文'
from score;

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据
目的：保证数据库中数据的正确、有效性和完整性
分类：

注意：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束

案例

---------- 约束演示 ----------
create table  user(
    id int primary key auto_increment comment '主键',
    name varchar(10) not null unique comment '姓名',
    age int check ( age > 0 && age <= 120 ) comment '年龄',
    status char(1) default '1' comment '状态',
    gender char(1) comment '性别'
) comment '用户表';

-- 插入数据
insert into user(name, age, status, gender) values ('Tom1', 19, '1', '男'), ('Tom2', 25, '2', '女');
insert into user(name, age, status, gender) values ('Tom3', 34, '3', '男');

-- insert into user(name, age, status, gender) values (null, 19, '1', '男');
-- insert into user(name, age, status, gender) values ('Tom3', 34, '3', '男');

insert into user(name, age, status, gender) VALUES ('Tom4', 80, '1', '男');
-- insert into user(name, age, status, gender) VALUES ('Tom5', -1, '1', '男');

insert into user(name, age, gender) VALUES ('Tom5', 120, '男');

外键约束

概念：外键用来让两张表的数据之间建立连接，从而保证数据的一致性和完整性。
语法：
    添加外键
        CREATE TABLE 表名{
            字段名 数据类型,
            ...
            [CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
        };
        ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY(外键字段名) REFERENGES 主表(主表列名);
    删除外键
        ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;

删除/更新行为

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE;

多表查询

多表关系

概述：项目开发中在进行数据库表结构设计时，会根据业务需求及业务模块之间的关系，分析并设计表结构，由于业务之间相互关联，所以各个表结构之间也存在着各种联系，基本上分为三种：
一对多（多对一）
多对多
一对一

一对多（多对一）

案例：部门与员工的关系
关系：一个部门对应多个员工，一个员工对应一个部门
实现：在多的一方建立外键，指向一的一方的主键

多对多

案例：学生与课程的关系
关系：一个学生可以选修多门课程，一门课程也可以供多个学生选择
实现：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键

---------- 多表关系演示 ----------
-- 多对多 --
create table student(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '姓名',
    no varchar(10) comment '学号'
) comment '学生表';

insert into student values (null, '黛绮丝', '2000100101'), (null, '谢逊', '2000100102'), (null, '殷天正', '2000100103'), (null, '韦一笑', '2000100104');

create table course(
    id int auto_increment primary key comment '主键ID',
    name varchar(10) comment '课程名称'
) comment '课程表';

insert into course values (null, 'Java'), (null, 'PHP'), (null, 'MySQL'), (null, 'Hadoop');

create table student_course(
    id int auto_increment comment '主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course (id),
    constraint fk_studentid foreign key (studentid) references student (id)
) comment '学生课程中间表';

insert into student_course values (null, 1, 1), (null, 1, 2), (null, 1, 3), (null, 2, 2), (null, 2, 3), (null, 3, 4);

一对一

案例：用户与用户详情的关系
关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率
实现：在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的(UNIQUE)