Mysql学习

​

Mysql

​

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

数据库管理系统（DataBase Management System，简称DBMS）

• DBMS是操作和管理数据库的大型软件。将来我们只需要操作这个软件，就可以通过这个软件来操纵和管理数据库了。

• 此时又出现一个问题：DBMS这个软件怎么知道要操作的是哪个数据库、哪个数据呢？是对数据做修改还是查询呢？

  • 需要给DBMS软件发送一条指令，告诉这个软件我们要执行的是什么样的操作，要对哪个数据进行操作。而这个指令就是SQL语句

  SQL（Structured Query Language，简称SQL）：结构化查询语言，它是操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准。我们学习数据库开发，最为重要的就是学习SQL语句 。

程序员给数据库管理系统(DBMS)发送SQL语句，再由数据库管理系统操作数据库当中的数据。

​

启动MySQL

​

mysql -u用户名 -p密码 [-h数据库服务器的IP地址 -P端口号]

-h 参数不加，默认连接的是本地 127.0.0.1 的MySQL服务器

-P 参数不加，默认连接的端口号是 3306

关系型数据库（RDBMS）

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

二维表的优点：

• 使用表存储数据，格式统一，便于维护

• 使用SQL语言操作，标准统一，使用方便，可用于复杂查询

我们之前提到的MySQL、Oracle、DB2、SQLServer这些都是属于关系型数据库，里面都是基于二维表存储数据的。

结论：基于二维表存储数据的数据库就成为关系型数据库，不是基于二维表存储数据的数据库，就是非关系型数据库（比如大家后面要学习的Redis，就属于非关系型数据库）。

SQL语句根据其功能被分为四大类：DDL、DML、DQL、DCL

分类	全称	说明
DDL	Data Definition Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

 

​

数据库设计-DDL

​

DDL英文全称是Data Definition Language(数据定义语言)，用来定义数据库对象(数据库、表)。

DDL中数据库的常见操作：查询、创建、使用、删除。

-- 查询所有数据库
show databases ;
​
-- 创建数据库
create database db02;
​
-- 使用数据库
use db02;
​
-- 删除数据库
drop database db02;

DDL中表结构的操作：创建表、查询表、修改表、删除表。

在MySQL数据库当中，提供了以下5种约束：

约束	描述	关键字
非空约束	限制该字段值不能为null	not null
唯一约束	保证字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，如果未指定该字段值，则采用默认值	default
外键约束	让两张表的数据建立连接，保证数据的一致性和完整性	foreign key

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

-- 创建: 基本语法
create table tb_user(
    id int comment 'ID, 唯一标识',
    username varchar(20) comment '用户名',
    name varchar(10) comment '姓名',
    age int comment '年龄',
    gender char(1) comment '性别'
) comment '用户表';
​
-- 创建: 基本语法 (约束)
create table tb_user(
    id int primary key auto_increment comment 'ID, 唯一标识',
    username varchar(20) not null unique comment '用户名',
    name varchar(10) not null comment '姓名',
    age int comment '年龄',
    gender char(1) default '男' comment '性别'
) comment '用户表';
​
-- 图形化界面创建表 tb_emp
create table tb_emp(
    id int primary key auto_increment comment '主键ID',
    username    varchar(20)                  not null comment '用户名',
    password    varchar(32) default '123456' null comment '密码',
    name        varchar(10)                  not null comment '姓名',
    gender      tinyint unsigned             not null comment '性别, 1 男, 2 女',
    image       varchar(300)                 null comment '图像url',
    job         tinyint unsigned             null comment '职位, 1 班主任 , 2 讲师 , 3 学工主管, 4 教研主管',
    entrydate   date                         null comment '入职日期',
    create_time datetime                     not null comment '创建时间',
    update_time datetime                     not null comment '修改时间',
    constraint tb_emp_username_uindex unique (username)
) comment '员工表';
​
-- DDL: 查看表结构
-- 查看: 当前数据库下的表
show tables;
​
-- 查看: 查看指定表结构
desc tb_emp;
​
-- 查看: 数据库的建表语句
show create table tb_emp;
​
​
-- DDL: 修改表结构
-- 修改: 为表 tb_emp 添加字段 qq varchar(11)
alter table tb_emp add qq varchar(11) comment 'QQ';
​
-- 修改: 修改 tb_emp 字段类型 qq varchar(13)
alter table tb_emp modify qq_num varchar(13) comment 'QQ';
​
-- 修改: 修改 tb_emp 字段名 qq 为 qq_num varchar(13)
alter table tb_emp change qq_num qq_num varchar(13) comment 'QQ';
​
-- 修改: 删除 tb_emp 的 qq_num 字段
alter table tb_emp drop column qq_num;
​
-- 修改: 将tb_emp 表名修改为 emp
rename table tb_emp to emp;
​
​
​
-- DDL: 删除表结构
-- 删除: 删除 tb_emp 表
drop table if exists tb_emp;

数据库操作-DML

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

-- DML : 插入数据 - insert
-- 1. 为 tb_emp 表的 username, name, gender 字段插入值
insert into tb_emp(username,name,gender,create_time,update_time) values ('wuji','张无忌',1,now(),now());
​
​
-- 2. 为 tb_emp 表的 所有字段插入值
insert into tb_emp(id, username, password, name, gender, image, job, entrydate, create_time, update_time)
            values (null,'zhiruo','123','周芷若',2,'1.jpg',1,'2010-01-01',now(),now());
​
insert into tb_emp values (null,'zhiruo2','123','周芷若',2,'1.jpg',1,'2010-01-01',now(),now());
​
-- 3. 批量为 为 tb_emp 表的 username , name , gender 字段插入数据
insert into tb_emp(username,name,gender,create_time,update_time) values
              ('weifuwang','韦一笑',1,now(),now()),('xieshiwang','谢逊',1,now(),now());
​
​
​
​
-- DML : 更新数据 - update
-- 1. 将 tb_emp 表的ID为1员工 姓名name字段更新为 '张三'
update tb_emp set name = '张三' , update_time = now() where id = 1;
​
-- 2. 将 tb_emp 表的所有员工的入职日期更新为 '2010-01-01'
update tb_emp set entrydate = '2010-01-01', update_time = now();
​
​
​
​
-- DML : 删除数据 - delete
-- 1. 删除 tb_emp 表中 ID为1的员工
delete from tb_emp where id = 1;
​
-- 2. 删除 tb_emp 表中的所有员工
delete from tb_emp;

数据库操作-DQL

DQL英文全称是Data Query Language(数据查询语言)，用来查询数据库表中的记录。

--  =================== DQL: 基本查询 ======================
-- 1、查询指定字段name,entrydate并返回
select name,entrydate from tb_emp;
​
-- 2、查询返回所有字段
-- 推荐
select id,id, username, password, name, gender, image, job, entrydate, create_time, update_time from tb_emp;
-- 不推荐（不直观，性能低）
select * from tb_emp;
​
-- 3、查询所有员工的name，entrydate,并起别名（姓名，入职日期）
select name as 姓名,entrydate as 入职日期 from tb_emp;
select name '姓  名',entrydate 入职日期 from tb_emp;
​
-- 4、查询已有的员工关联了哪几种职位（不要重复）
select distinct job from tb_emp;

--  =================== DQL: 条件查询 ======================
-- 1. 查询 姓名 为 杨逍 的员工
select * from tb_emp where name = '杨逍';
​
-- 2. 查询 id小于等于5 的员工信息
select * from tb_emp where id <= 5;
​
-- 3. 查询 没有分配职位 的员工信息
select * from tb_emp where job is null ;
​
-- 4. 查询 有职位 的员工信息
select * from tb_emp where job is not null;
​
-- 5. 查询 密码不等于 '123456' 的员工信息
select * from tb_emp where password != '123456';
​
select * from tb_emp where password <> '123456';
​
-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from tb_emp where entrydate >= '2000-01-01'and entrydate <= '2010-01-01';
​
select * from tb_emp where entrydate between '2000-01-01'and '2010-01-01';
​
-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from tb_emp where entrydate between '2000-01-01'and '2010-01-01' and gender = 2;
​
-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from tb_emp where job = 2 or job = 3 or job = 4;
​
select * from tb_emp where job in (2,3,4);
​
-- 9. 查询 姓名 为两个字的员工信息
select * from tb_emp where name like '__';
​
-- 10. 查询 姓 '张' 的员工信息
select * from tb_emp where name like '张%';

--  =================== DQL: 分组查询 ======================
-- 聚合函数:不对null值进行运算
​
-- 1. 统计该企业员工数量----count
-- A.count(字段)
select count(id) from tb_emp;
select count(job) from tb_emp;
​
-- B.count(常量)
select count(1) from tb_emp;
select count('A') from tb_emp;
​
-- C.count(*)----推荐
select count(*) from tb_emp;
​
-- 2. 统计该企业员工 ID 的平均值----avg
select avg(id) from tb_emp;
​
-- 3. 统计该企业最早入职的员工----min
select min(entrydate) from tb_emp;
​
-- 4. 统计该企业最迟入职的员工----max
select max(entrydate) from tb_emp;
​
-- 5. 统计该企业员工的 ID 之和----sum
select sum(id) from tb_emp;

​

分组查询：

​

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

注意事项：

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

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

​
-- 1. 根据性别分组 , 统计男性和女性员工的数量----分组字段gender,聚合函数count(*)
select gender,count(*) from tb_emp group by gender;
​
-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count(*) from tb_emp where entrydate <= '2015-01-01' group by job having count(*) >= 2;

​

where与having区别

​

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

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

​

排序查询:

​

select 字段列表 from 表名 [where 条件] [group by 分组字段名 ]order by 字段1 排序方式1，字段2 排序方式2...;

如果是多字段排序，当第一个字段值相同时，才会对第二个字段进行排序。

--  =================== 排序查询 ======================
-- 1. 根据入职时间, 对员工进行升序排序----asc升序（默认）
select * from tb_emp order by entrydate asc;
select * from tb_emp order by entrydate;
​
-- 2. 根据入职时间, 对员工进行降序排序----desc降序
select * from tb_emp order by entrydate desc;
​
-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
select * from tb_emp order by entrydate,update_time desc;

​

分页查询：

​

select 字段列表 from 表名 limit 起始索引，查询记录数；

--  =================== 分页查询 ======================
-- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;
​
-- 2. 查询 第1页 员工数据, 每页展示5条记录
select * from tb_emp limit 0,5;
select * from tb_emp limit 5;
​
-- 3. 查询 第2页 员工数据, 每页展示5条记录
select * from tb_emp limit 5,5;
​
-- 4. 查询 第3页 员工数据, 每页展示5条记录
select * from tb_emp limit 10,5;
​
-- 起始索引 = （页码 - 1） * 每页展示记录数

 

-- 案例1：按需求完成员工管理的分页查询-根据输入条件，查询第一页数据，每页展示10条记录
-- 输入条件：
        -- 姓名：张
        -- 性别：男
        -- 入职时间：2000-01-01  2015-12-31
-- 1.1根据输入的员工姓名、员工性别、入职时间 搜索满足条件的员工信息
-- 1.2其中员工姓名支持模糊匹配；性别进行精确查询；入职时间进行范围查询。
-- 1.3支持分页查询
-- 1.4对查询的好结果根据最后修改时间按倒序排序。
​
select *
from tb_emp
where name like '%张%'
  and gender = 1
  and entrydate between '2000-01-01' and '2015-12-31'
order by update_time desc
limit 0,10;
​
-- 案例2：根据需求，完成员工性别信息的统计
-- if(条件表达式,true取值,false取值)
select if(gender = 1,'男性员工','女性员工') 性别,count(*) from tb_emp group by gender;
​
-- 根据需求，完成员工性别信息的统计
-- case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ... end
select
    (case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研主管' else '未分配职位' end)职位,
    count(*)
from tb_emp group by job;

​

多表设计

​

一对多关系实现：在数据库表中多的一方，添加字段，来关联一的一方的主键。

外键约束

物理外键：使用foreign key定义外键关联另一张表。

-- ============================= 内连接 ==========================
-- A. 查询员工的姓名 , 及所属的部门名称 (隐式内连接实现)
select tb_emp.name,tb_dept.name from tb_dept d,tb_emp where tb_dept.id = tb_emp.dept_id;
​
-- 起别名
select e.name , d.name from tb_dept d,tb_emp e where d.id = e.dept_id;
​
​
-- B. 查询员工的姓名 , 及所属的部门名称 (显式内连接实现)
select tb_emp.name,tb_dept.name from tb_emp inner join tb_dept on  tb_dept.id = tb_emp.dept_id;

-- =============================== 外连接 ============================
-- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
select e.name,d.name from tb_emp e left join tb_dept d on e.dept_id = d.id;
​
​
​
-- B. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
select d.name,e.name from tb_emp e right join tb_dept d on e.dept_id = d.id;

-- ========================= 子查询 ================================
-- 标量子查询
-- A. 查询 "教研部" 的所有员工信息
​
-- a,查询“教研部”的部门ID - tb_dept
select id from tb_dept where name = '教研部';
-- b,再查询该部门ID下的员工信息 - tb_emp
select * from tb_emp where dept_id = 2;
​
​
select * from tb_emp where dept_id = (select id from tb_dept where name = '教研部');
​
​
-- B. 查询在 "方东白" 入职之后的员工信息
​
-- a,查询 方东白 的入职时间
select entrydate from tb_emp where name = '方东白';
-- b,查询在 "方东白" 入职之后的员工信息
select * from tb_emp where entrydate > '2012-11-01';
​
select * from tb_emp where entrydate > (select entrydate from tb_emp where name = '方东白');
​
​
​
-- 列子查询
-- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
​
-- a,查询"教研部" 和 "咨询部" 的部门ID - tb_dept
select id from tb_dept where name = '教研部' or name = '咨询部';
-- b,根据部门ID，查询该部门下的员工信息 - tb_emp
select * from tb_emp where dept_id in (3,2);
​
select * from tb_emp where dept_id in (select id from tb_dept where name = '教研部' or name = '咨询部');
​
-- 行子查询
-- A. 查询与 "韦一笑" 的入职日期 及 职位都相同的员工信息 ;
​
-- a,查询 "韦一笑" 的入职日期 及 职位
select entrydate,job from tb_emp where name = '韦一笑';
-- b,查询其入职日期 及 职位都相同的员工信息
select * from tb_emp where entrydate = '2007-01-01' and job = 2;
​
select * from tb_emp where (entrydate,job) = ('2007-01-01',2);
​
select * from tb_emp where (entrydate,job) = (select entrydate,job from tb_emp where name = '韦一笑');
​
​
​
-- 表子查询
-- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门信息
-- a,查询入职日期是 "2006-01-01" 之后的员工信息
select * from tb_emp where entrydate > '2006-01-01';
-- b,查询这部分员工信息及其部门名称 - tb_dept
select e.*,d.name from (select * from tb_emp where entrydate > '2006-01-01') e,tb_dept d where e.dept_id = d.id;

​

事务

​

事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

​

索引

​

索引是帮助数据库高效获取数据的数据结构。

Mysql数据库智齿的索引结构有很多，默认是B+tree。

-- ========================= 索引 ================================
-- 创建：为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);
​
-- 查询：查询tb_emp表的索引信息
show index from tb_emp;
​
-- 删除：删除tb_emp表中的name字段的索引
drop index idx_emp_name on tb_emp;