MySQL基础

分类(按照功能)

• DDL : 数据定义语言

  • 直接操作表, 库, 视图, 索引等的语句

• DML : 数据操作语言

  • 增, 删 , 改操作

• DQL : 数据查询语言

  • 用于从数据库中查询数据。

• DCL: 数据控制语言

  • 用于控制用户访问权限，管理数据库中的权限控制

创建(DDL)

约束

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

• 

• 代码示例

• create table emp(
      id int unsigned primary key auto_increment comment 'ID,主键',
      username varchar(20) unique not null comment '用户名',
      password varchar(32) not null comment '密码',
      name varchar(10) not null comment '姓名',
      gender tinyint unsigned not null comment '性别, 1:男, 2:女',
      phone char(11) not null comment '手机号',
      job tinyint unsigned null comment '职位, 1:班主任,2:讲师,3:学工主管,4:教研主管,5:咨询师',
      salary int unsigned null comment '薪资',
      image varchar(300) null comment '头像',
      entry_date date null comment '入职日期',
      create_time datetime null comment '创建时间',
      update_time datetime null comment '修改时间',
  )comment '员工表';
  

修改(DML)

INSERT

• 用于插入新数据。

• INSERT INTO 
    emp(username,password,name,gender,phone,job,salary) 
  VALUES 
    ('sunwukong','123454','孙悟空', 1, 12332451234, 1, 10000);
  

UPDATE

• 用于更新已有数据。

• update emp set password = '123456abc' where id = 1;
  

DELETE

• 用于删除数据。

• DELETE FROM emp WHERE id = 1;
  

查询(DQL)

基本查询

• --  =================== DQL: 基本查询 ======================
  -- 1. 查询指定字段 name,entry_date 并返回
  SELECT `name`,entry_date FROM emp;
  
  -- 2. 查询返回所有字段
  SELECT * FROM emp;
  
  -- 3. 查询所有员工的 name,entry_date, 并起别名(姓名、入职日期)
  SELECT `name` as '姓名' , entry_date '入职日期' FROM emp;
  
  -- 4. 查询已有的员工关联了哪几种职位(不要重复)
  SELECT DISTINCT job FROM emp;
  

条件查询

• --  =================== DQL: 条件查询 ======================
  -- 1. 查询 姓名 为 柴进 的员工
  SELECT * FROM emp WHERE `name` = '柴进';
  
  -- 2. 查询 薪资小于等于5000 的员工信息
  SELECT * FROM emp WHERE salary <= 5000;
  
  -- 3. 查询 没有分配职位 的员工信息
  SELECT * FROM emp WHERE job IS NULL;
  
  -- 4. 查询 有职位 的员工信息
  SELECT * FROM emp WHERE job IS NOT NULL;
  
  -- 5. 查询 密码不等于 '123456' 的员工信息
  SELECT * FROM emp WHERE `password` != '123456';
  
  -- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
  SELECT * FROM emp WHERE entry_date >= '2000-01-01' && entry_date <='2010-01-01' && gender =2;
  
  -- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
  SELECT * FROM emp WHERE entry_date BETWEEN '2000-01-01' AND '2010-01-01' && gender =2;
  
  -- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
  SELECT * FROM emp WHERE job IN(2,3,40);
  
  -- 9. 查询 姓名 为两个字的员工信息
  SELECT * FROM emp WHERE `name` LIKE '__';
  
  -- 10. 查询 姓 '李' 的员工信息
  SELECT * FROM emp WHERE `name` LIKE '李%';
  
  -- 11. 查询 姓名中包含 '二' 的员工信息
  SELECT * FROM emp WHERE `name` LIKE '%二%';
  

分组查询

聚合函数

• --  =================== DQL: 分组查询 ======================
  -- 聚合函数
  
  -- 1. 统计该企业员工数量
  -- SELECT * FROM emp;
     SELECT COUNT(*) FROM emp;
  
  -- 2. 统计该企业员工的平均薪资
  SELECT AVG(salary) FROM emp;
  
  -- 3. 统计该企业员工的最低薪资
  SELECT min(salary) FROM emp;
  
  -- 4. 统计该企业员工的最高薪资
  SELECT max(salary) FROM emp;
  
  -- 5. 统计该企业每月要给员工发放的薪资总额(薪资之和)
  SELECT sum(salary) FROM emp;
  

分组

• 分组查询后的分组数据可以当作一个临时且独立的表, 后续的操作就对这个(临时表)进行操作, 不会影响其它组的数据

• -- 分组
  -- 1. 根据性别分组 , 统计男性和女性员工的数量
  -- SELECT * FROM emp GROUP BY gender;
  SELECT gender , COUNT(*) AS count FROM emp GROUP BY gender;
  
  -- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
  -- SELECT * FROM emp WHERE entry_date <= '2015-01-01' GROUP BY job;
  SELECT 
    job, COUNT(*) as count  -- 分组查询需要配合聚合函数使用, 字段一般为分组字段, 
    						  -- GROUP BY job (可以先使用 * 作为占位, 后续再修改)
  FROM 
    emp 
  WHERE 
    entry_date <= '2015-01-01' 
  GROUP BY 
    job 
  HAVING  -- 使用在where字段后, 可以对聚合函数进行筛选
    count >= 2;
  

排序查询

•   --  =================== 排序查询 ======================
  -- 1. 根据入职时间, 对员工进行升序排序
  SELECT * FROM emp ORDER BY entry_date ASC;
  
  -- 2. 根据入职时间, 对员工进行降序排序
  SELECT * FROM emp ORDER BY entry_date DESC;
  
  -- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
  SELECT * FROM emp 
  ORDER BY          -- 后续可以写多个排序条件, 用逗号隔开
    entry_date ASC,
    update_time DESC;
  

分页查询

• --  =================== 分页查询 ======================
  -- 1. 从起始索引0开始查询员工数据, 每页展示10条记录
  SELECT * FROM emp LIMIT 0,10;
  
  -- 2. 查询 第1页 员工数据, 每页展示5条记录
  SELECT * FROM emp LIMIT 0,5;     -- (页面数 - 1) * 页面展示数量  (1 - 1) * 5 = 0;
  
  -- 3. 查询 第2页 员工数据, 每页展示5条记录
  SELECT * FROM emp LIMIT 5,5;
  
  -- 4. 查询 第3页 员工数据, 每页展示5条记录
  SELECT * FROM emp LIMIT 10,5;
  

表拆分

• 水平拆分

  • 对表的行 行 拆分, 一般用于数据量过大场景, 分布式存储等场景

• 垂直拆分

  • 对表的列 (字段) 拆分 , 一般因为列数过多：当表中的列数非常多，某些列频繁查询，某些列访问较少时，垂直拆分有助于优化性能。

• 逻辑拆分

  • 逻辑拆分是根据业务需求，将一个表拆分成多个逻辑上独立的子表。逻辑拆分通常不要求在物理上拆分数据表，而是通过数据库视图或业务逻辑来进行拆分。

  • 应用场景

    • 按用户角色拆分：例如，系统中有管理员、普通用户、访客等不同角色，可能需要将不同角色的用户信息拆分为不同的逻辑表。
    • 按地区拆分：在全球化系统中，可以将不同地区的用户数据拆分到不同的表中，每个表对应不同的地区。

表关系

• 外键约束

  • 特点 :

    • 主表（一方）：部门 (dept)

    • 从表（多方）：员工 (emp)

      • 外键字段 (dept_id) 通常放在从表 (emp) 中，指向主表 (dept) 的主键 (id)。

  • 语句 :

    • ALTER TABLE emp
      ADD CONSTRAINT fk_emp_dept FOREIGN KEY dept_id REFERENCES dept.id;
      

  • 物理外键约束

    • 使用SQL语句添加外键约束 (一般不使用)

  • 逻辑外键约束

    • 只是在从表中添加主表映射字段, 不添加约束

• 一对一

  • 一对一关系表示一个表中的一行记录只能与另一个表中的一行记录关联。

• 一对多

  • 一对多关系表示一个表中的一行记录可以与另一个表中的多行记录关联。通常在数据库设计中，这种关系是最常见的，适用于大多数业务场景。

  • 假设有两个表：dept 和 emp。每个部门可以有多个员工，但每个员工只能属于一个部门。

• 多对多

  • 多对多关系表示两个表之间的多行记录相互关联。在数据库中，实现多对多关系通常需要通过一个中间表来间接建立连接。

  • 示例：

    • 假设有两个表：Student 和 Course。一个学生可以选修多门课程，同时每门课程可以有多个学生报名。

    • -- Student 表
      CREATE TABLE Student (
          student_id INT PRIMARY KEY,
          student_name VARCHAR(100)
      );
      
      -- Course 表
      CREATE TABLE Course (
          course_id INT PRIMARY KEY,
          course_name VARCHAR(100)
      );
      
      -- Student_Course 中间表
      CREATE TABLE Student_Course (
          student_id INT,
          course_id INT,
          PRIMARY KEY (student_id, course_id),
      );
      

多表查询

基于连接的查询

• 内连接（inner join）

  • 查交集, 将 emp 表中的 dept_id 字段与 dept 字段表中的 id 进行遍历比对, 只返回两张表中同时使用了两个字段的数据. 若dept中没有 id = 9 的部门值, 但 emp 表中没有部门对应的员工( 有部门, 但没有员工) , 则不返回 id = 9 的信息

  • -- ============================= 内连接 ==========================
    -- A. 查询所有员工的ID, 姓名 , 及所属的部门名称 (隐式、显式内连接实现)
    SELECT emp.id, emp.name, dept.name FROM emp , dept WHERE emp.dept_id = dept.id;
    SELECT emp.id, emp.name, dept.name FROM emp JOIN dept ON emp.dept_id = dept.id;
    
    -- B. 查询 性别为男, 且工资 高于8000 的员工的ID, 姓名, 及所属的部门名称 (隐式、显式内连接实现)
    SELECT emp.id, emp.name, dept.name 
    FROM emp, dept WHERE emp.dept_id = dept.id 
    AND emp.gender = 1 && emp.salary > 8000;
    
    SELECT emp.id, emp.name, dept.name -- 查询后需要展示的数据
    FROM emp JOIN dept ON emp.dept_id = dept.id -- 从哪里查询
    WHERE emp.gender = 1 && emp.salary > 8000; -- 查什么数据(筛选)
    

• 外连接

  • 左外连接（left join）

    • 查编写的第一张表中的全部数据, 若第二张表中无对应值, 则返回 null. 根据emp.dept_id = 1 从 dept表中查询, 但dept表中没有 id = 1 的部门, 则返回dept.name = null;

    • -- =============================== 左外连接 ============================
      -- A. 查询员工表 所有 员工的姓名, 和对应的部门名称 (左外连接)
      SELECT emp.id, emp.name, dept.name FROM emp RIGHT LEFT JOIN dept ON emp.dept_id = dept.id;
      
      -- B. 查询工资 高于8000 的 所有员工的姓名, 和对应的部门名称 (左外连接)
      SELECT emp.id, emp.name, dept.name 
      FROM emp LEFT JOIN dept ON emp.dept_id = dept.id 
      WHERE emp.gender = 1 && emp.salary > 8000; 
      

  • 右外连接（right join）

    • 查编写的第二张表中的全部数据, 若第一张表中无对应值, 则返回 null. 根据 dept.id = 9 从 emp 表中查询, 但 emp 表中没有 dept_id = 9 的员工, 则返回 emp.id = null, emp.name = null;

    • -- =============================== 右外连接 ============================
      -- A. 查询部门表 所有 部门的名称, 和对应的员工名称 (右外连接)
      SELECT emp.id, emp.name, dept.name FROM emp RIGHT JOIN dept ON emp.dept_id = dept.id;
      

基于子查询 (嵌套查询) 的查询

• 标量子查询

  • 返回单个值，用在查询的字段中。(一般配合聚合函数使用, 或者limit(1)使用)

  • -- 标量子查询
    -- A. 查询 最早入职 的员工信息
    SELECT * FROM emp WHERE entry_date = (SELECT MIN(entry_date) FROM emp);
    

• 列子查询

  • 返回一列多个值，常用于 IN、ANY 等操作符中。

  • -- 列子查询
    -- A. 查询 "教研部" 和 "咨询部" 的所有员工信息
    SELECT * 
    FROM emp JOIN dept ON emp.dept_id = dept.id 
    WHERE dept_id IN (SELECT id FROM dept WHERE name IN ('教研部','咨询部'))
    

• 行子查询

  • 返回一行数据，常用于与某个值进行比较。

  • -- 行子查询
    -- A. 查询与 "李忠" 的薪资 及 职位都相同的员工信息 ;
    SELECT * 
    FROM tb_emp  
    WHERE (salary, job) IN (SELECT salary, job FROM tb_emp WHERE name = '金庸')
    

• 表子查询

  • 返回一张表数据，通常用于 FROM 子句中，作为临时表使用

  • -- 表子查询
    -- A. 查询入职日期是 "2006-01-01" 之后的员工信息 , 及其部门名称
    SELECT t1.*, dept.name
    FROM (SELECT * FROM emp WHERE entry_date > '2006-01-01') AS t1 JOIN dept ON t1.dept_id = dept.id;