day20 MySQL

一、MySQL概述

数据库：DataBase（DB），存储和管理数据的仓库。

数据库管理系统：DataBase Management System  (DBMS)，操纵和管理数据库的大型软件。

SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库统一标准。

数据模型：

关系型数据库：建立在在关系模型的基础上，由多张表相互连接的二维表组成

SQL简介：

一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

分类：

SQL通用语法

二、数据库的设计-DDL

创建

create table  表名(

字段1  字段类型  [ 约束 ]  [ comment  字段1注释 ] ,

......

字段n  字段类型  [ 约束 ]  [ comment  字段n注释 ]）;

Demo

约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确性、有效性和完整性。

数据类型：数值类型、字符串类型、日期时间类型

Demo

表的操作

Demo

三、数据库的操作-DML

DML英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录进行增、删、改操作。

3.1 添加数据（INSERT）

指定字段：insert into 表名 (字段名1, 字段名2)  values (值1, 值2);

全部字段：insert into 表名 values (值1, 值2, ...);

批量添加数据（指定字段）：insert into 表名 (字段名1, 字段名2)  values (值1, 值2), (值1, 值2);

批量添加数据（全部字段）：insert into 表名 values (值1, 值2, ...), (值1, 值2, ...);

批量的区别就是在value后可以填添加多条数据，用 , 隔开。

Demo

3.2 修改数据（UPDATE）

update  表名  set  字段名1 = 值1 , 字段名2 = 值2 , .... [ where  条件 ] ;

Demo

3.3 删除数据（DELETE）

delete  from  表名  [ where  条件 ];

四、表的操作DQL

4.1 基本查询

查询多个字段：select  字段1, 字段2, 字段3  from   表名;

查询所有字段（通配符）：select  *  from   表名;

设置别名：select  字段1  [ as  别名1 ] , 字段2  [ as  别名2 ]   from   表名;

去除重复记录：select  distinct  字段列表  from   表名;

注意事项：* 号代表查询所有字段，在实际开发中尽量少用（不直观、影响效率）

Demo

4.2 条件查询

条件查询：select  字段列表  from   表名   where   条件列表 ;

--  =================== 基本查询 ======================
-- 1.查询指定字段 name, entrydate 并返回
select name, entrydate
from emp;

-- 2.查询返回所有字段方式一:  写出所有字段     推荐, 效率高, 更直观

select id,
       username,
       password,
       name,
       gender,
       image,
       job,
       entrydate,
       create_time,
       update_time
from emp;

-- 2.查询返回所有字段方式二: * 表示所有字段
select *
from emp;

-- 3.查询所有员工的 name, entrydate, 并起别名(姓名、入职日期)  --- as 关键字可以省略
select name as 姓名, entrydate 入职日期
from emp;
-- 4.查询员工有哪几种职位(不要重复) -- distinct
select distinct job
from emp;


--  =================== 条件查询 ======================
-- 1.查询 姓名 为 杨逍 的员工
select * from emp where name = '杨逍';

-- 2.查询 id小于等于5 的员工信息
select * from emp where id <= 5;

-- 3.查询 没有分配职位 的员工信息  -- 判断 null , 用 is null
select * from emp where job is null;
-- 4.查询 有职位 的员工信息  -- 判断 不是null , 用 is not null
select * from emp where job is not null;

-- 5.查询 密码不等于 '123456' 的员工信息   !=   <>
select * from emp where password != '123456';
select * from emp where password <> '123456';

-- 6.查询入职日期 在 '2005-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息  >= x && <= xx
select * from emp
where entrydate >= '2005-01-01' && entrydate <= '2010-01-01';
select * from emp
where entrydate >= '2005-01-01' and entrydate <= '2010-01-01';
-- between ... and  ..
select * from emp where entrydate between '2005-01-01' and '2010-01-01';

-- 7.查询 入职时间 在 '2005-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from emp
where entrydate between '2005-01-01' and '2010-01-01' and gender = 2;


-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from emp
where job = 2 or job = 3 or job = 4;

select * from emp where job between 2 and 4;

select * from emp where job in (2, 3, 4);

-- 9.查询姓名为两个字的员工信息
select * from emp where name like '__';

-- 10.查询姓 '张' 的员工信息
select * from emp where name like '张%';
select * from emp where name like '张_';

-- 11.查询姓名中包含 '三' 的员工信息   小三子  三哥 唐三
select * from emp where name like '%三%';

-- 扩展: 查询名字包含张, 性别为1, 入职日期在 '2000-01-01' 到 '2010-01-01'
select *
from emp where name like '%张%' and gender = 1
    and entrydate between '2000-01-01' and '2010-01-01';

4.3 分组查询

介绍：将一列数据作为一个整体，进行纵向计算。

语法：select  聚合函数(字段列表)  from   表名 ;

聚合函数使用注意事项：null值不参与所有聚合函数运算。统计数量可以使用：count(*)   count(字段)   count(常量)，推荐使用count(*)。

-- 聚合函数

-- 1.统计该企业员工数量, count(字段)
select count(id) from emp;
-- null值不参与聚合函数运算
select count(job) from emp;
-- B.count(*)
select count(*) from emp;
-- C.count(值)
select count(1) from emp;

-- 2.统计该企业员工 ID 的平均值
select avg(id) from emp;

-- 3.统计该企业最早入职的员工的入职日期
select min(entrydate) from emp;

-- 4.统计该企业最近入职的员工的入职日期
select max(entrydate) from emp;

-- 5.统计该企业员工的 ID 之和
select sum(id) from emp;

分组查询：select  字段列表  from   表名  [ where   条件 ]  group   by  分组字段名  [ having  分组后过滤条件 ];

Where和having的区别：

1.执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2.判断条件不同：where不能对聚合函数进行判断，而having可以。

3.执行顺序：where > 聚合函数 > having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义。

--  =================== 分组查询 ======================
-- 1.根据性别分组, 统计男性和女性员工的数量  -- count   分组查询是不能把 不能分组的字段写上查询里面
select gender, count(gender) from emp
group by gender;

-- 2.查询入职时间在 '2015-01-01' (包含) 以前的员工, 并对结果根据职位分组, 获取员工数量大于等于2的职位
select job, count(job)
    from emp where entrydate <= '2015-01-01'
        group by job having count(job) >= 2;

4.4 排序查询

条件查询：select  字段列表  from   表名   [ where   条件列表 ] [ group by  分组字段 ] order  by  字段1  排序方式1 , 字段2  排序方式2 … ;

Order by 后面可以通过多种字段进行排序，如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

升序：asc

降序：desc

--  =================== 排序查询 ======================
-- order by 字段 asc | desc
-- asc升序
-- desc降序
-- 1.根据入职时间, 对员工进行升序排序
select id, username, password, name, gender, image, job, entrydate,
       create_time, update_time from emp
    order by entrydate asc ;

-- 2.根据入职时间, 对员工进行降序排序
select id, username, password, name, gender, image, job, entrydate,
       create_time, update_time from emp
order by entrydate desc ;

-- 3.根据 入职时间 对公司的员工进行 升序排序, 入职时间相同, 再按照 ID 进行降序排序
select id, username, password, name, gender, image, job, entrydate,
       create_time, update_time from emp
order by entrydate asc,id desc ;

-- 扩展: 查询名字包含张, 性别为1, 入职日期在 '2000-01-01' 到 '2010-01-01', 根据入职时间, 进行倒序排序
select id, username, password, name, gender, image, job, entrydate,
       create_time, update_time from emp
    where name like '%张%' and entrydate between '2000-01-01' and '2010-01-01'
order by entrydate desc ;

4.5 分页查询

分页查询：select  字段列表  from   表名  limit  起始索引, 查询记录数 ;

起始索引从0开始，起始索引 = （查询页码 - 1）* 每页显示记录数。

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT。

如果查询的是第一页数据，起始索引可以省略，直接简写为 limit 10。

--  =================== 分页查询 ======================
-- 1.每页展示10条记录, 查询第1页, 跳过0条, 获取10条
select * from emp limit 0,10;


-- 2.每页展示10条记录, 查询第2页, 跳过10条, 获取10条
select * from emp limit 10,10;

-- 3.每页展示10条记录, 查询第3页, 跳过20条, 获取10条
select * from emp limit 20,10;

两种函数

if(表达式, tvalue, fvalue)：当表达式为true时，取值tvalue；当表达式为false时，取值fvalue

 case  expr  when  value1  then  result1 [when  value2  then  value2 ...] [else  result]  end

案例