MySQL学习记录

  • 开发
  • 24

目录

1.数据库概述

1.1 什么是数据库

数据库简称DB，全称为database。

从字面来看，数据库就是存储各种不同数据类型的数据. 就是一个存储数据的仓库

1.2 为什么要使用数据库

  • 唯一的目的: 持久化保存数据。
  • IO(读写文件数据)虽然也可以实现持久化。但是操作较复杂，数据存储文件，可读性不是很高。
  • 因此，很多开发厂商，站在程序员多样化需求前提下，开发了数据库这样一个软件。可以直观的看到不同类型的数据，以及实现数据持久化操作。

1.3 数据库分类

  • 按存储位置的不同进行分类：
    1. 基于磁盘的存储，MySQL，Oracle，SQLServer等。将数据写入文件，底层是IO.
      优势: 完全保证数据的持久化。
      弊端: 底层是IO实现，读写性能偏低。
    2. 基于内存的存储。将数据存储内存或者缓存。比如Redis。
      优势：查询性能高。
      弊端：不能完全保数据持久化。比如Redis后期可以使用RDB以及AOF进行解决。
  • 按从数据间是否存在关系进行分类：
    1. 关系型数据库：MySQL，Oracle，SQLServer等。表与表，字段与字段，数据与数据之间存在一定的关系。
    2. 非关系型数据库：我们又称为NOSQL(not only sql)。redis，mongodb 等

1.4 DBMS

在上面，我们简单描述了数据库的基本概念和分类。但是对于经常我们所听说到的MySQL、SQLServer、Oracle等，不能直接称为数据库。

他们是一个软件，仅仅是一个服务(服务器)。所以，我们要学习的也不是数据库，而是要学习一个软件。称为数据库管理系统软件。更深一步来说，我们要学习MySQL，他就是一个DBMS，或者说RDBMS(关系型数据库管理系统软件).

RDBMS 即关系数据库管理系统(Relational Database Management System)的特点：

  1. 数据以表格的形式出现
  2. 每行为各种记录名称
  3. 每列为记录名称所对应的数据域
  4. 许多的行和列组成一张表单
  5. 若干的表单组成database

1.5 RDBMS 术语

在我们开始学习MySQL 数据库前，让我们先了解下RDBMS的一些术语：

  • 数据库: 数据库是一些关联表的集合。
  • 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
  • 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。
  • 行：一行（元组，或记录）是一组相关的数据，例如一条用户订阅的数据。
  • 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。
  • 主键：主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
  • 外键：外键用于关联两个表。
  • 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。
  • 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排- 序的一种结构。类似于书籍的目录。
  • 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。

2.MySQL入门

2.1 什么是MySQL

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL特点：

  1. MySQL 数据库是关系型的
  2. MySQL 软件是开源的
  3. 使用 C 和 C++ 编写
  4. MySQ 数据库服务器非常快速、可靠、可扩展且易于使用
  5. 使用非常快速的B树磁盘表（MyISAM）和索引压缩
  6. 使用非常快速的基于线程的内存分配系统

2.2 安装MySQL

  • MySQL安装教程
1. 从MySQL官网下载安装软件  https://dev.mysql.com/downloads/windows/installer/8.0.html(咱们学习用的是8.0.*的版本)
2. 双击打开安装
3. 只需要选择安装  server即可。
4. 记住MySQL端口号:  3306(一般不改)  
5. 记住MySQL的用户名:  root      密码: root(密码是自定义的，自己牢记即可)
6. msi文件安装方式，不能手动选择安装目录路径。默认安装在c盘
7. 对本地磁盘的改动
    C:\Program Files\MySQL   可执行的工具bin、lib、doc....
    C:\ProgramData\MySQL\MySQL Server 8.0 (隐藏目录)  数据库的数据以及mysql的配置  my.ini
  • MySQL配置文件my.ini的部分内容
default-character-set=UTFMB4
character-set-server=    
port=3306
datadir=C:/ProgramData/MySQL/MySQL Server 8.0\Data
default-storage-engine=INNODB
max_connections=151  允许的最大的并发量
  • 验证是否安装成功

打开cmd窗口使用相关指令验证。输入 mysql，没有生效的话需要配置MySQL的环境变量。
需要配置mysql的环境变量: 在path配置安装MySQl目录的bin目录。

3.SQL概述

3.1 SQL简介

SQL (structure query language) 结构化查询语言，专门与DBMS通信的语言，所有RDBMS（关系型数据库 如MySQL，Oracle，SQLserver等）都支持；可使用SQL操作里面的库、表、字段、数据等。

  • SQL分类：
    1. DDL data nation language 数据定义语言。 用来定义数据库对象：库、表、列等
      CREATE DROP ALTER TRUNCATE
    2. DML 数据操作语言。 INSERT DELETE UPDATE (更新)
    3. DQL 数据查询语言。 SELECT
    4. DCL 数据控制语言。 GRANT
    5. TCL 事务控制语言。 COMMIT ROLLBACK

3.2 常用命令

  1. mysql -hip -uroot -p – 在cmd窗口 使用此指令连接数据库服务器
  2. show databases; – 查看mysql所有的数据库
  3. select database(); – 查看目前正在操作的数据库
  4. use 数据库的名称; – 切换要操作的数据库
  5. show tables; – 展示指定的数据库里面所有的表
  6. desc 表名; – 查看指定的表的表结构 属性/列,数据类型，约束
mysql> show databases; -- 查看mysql所有的数据库
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

mysql> select database();-- 查看目前正在操作的数据库
+------------+
| database() |
+------------+
| NULL       |
+------------+

mysql> use mysql -- use 数据库的名称; 切换要操作的数据库
mysql> show tables; -- 展示指定的数据库里面所有的表
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv

mysql> desc user; -- desc 表名; 查看指定的表的表结构   属性/列,数据类型，约束
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv

-- 查询数据  sql----> select
mysql> select * from user; -- 查询指定表里面所有的记录  select * from 表名;
-- * : 通配符 通配表里面所有的字段/列/属性
mysql> select host,user from user; -- 查询指定的表的列
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+

3.3 数据类型

3.3.1 整数类型

  • 可以选择有符号表示 和无符号表示(正数，可以使用unsigned属性修饰)；
  • 对于整数类型，支持指定显示宽度
  • 整数型有个属性auto_increment， 此属性只用于整数类型。 一个表最多只有一个auto_increment 列，标识自增
类型 大小 有符号范围 无符号范围
TINYINT(m) 1 -128-127 0 - 255
SMALLINT(m) 2 -32768-32767 0 - 65535
MEDIUMINT(m) 3 -8388608-8388607 0 - 16777215
INT(m) 4 -2147483648-2147483647 0 - 4294967295
BIGINT(m) 8 -9223372036854775808 - 9223372036854775807 0 - 18446744073709551615
  • m表示SELECT查询结果集中的显示宽度，与取值范围无关。 m最大值11
  • tinyint(1) unsingned ，数值只能是0与1，对应java里面的boolean类型。否则就是对应java语言里面的byte或者int
  • zerodfill属性: int(5) 00100 如果不满5个数字，会在数值之前使用0进行填充。

3.3.2 浮点类型

类型 大小 有符号范围
FLOAT(m,d) 4 -3.402823466E+38 to -1.165494351E-38
DOUBLE(m,d) 7
DECIMAL(m,d) 可变 根据M和D 的值
  • m为数字的总个数，d为小数点之后的个数。
  • 定点数在MySQL内部以字符串形式存放 比浮点数更精确 适合用来存放货币等精度高的数据；
  • 浮点数和定点数都可以在后面加(M,D) M表示显示的位数 D表示小数的位数，当存入的小数位过长时 此三者都会进行四舍五入
  • Decimal(m,d) 定点数 参数m<65 是总个数，d<30且 d<m 是小数位。 BigDecimal
  • decimal在不指定精度时，默认的整数位时10，默认的小数位是0

3.3.3 字符类型

类型 最大长度 描述
CHAR(m) 255字符 定长字符
VARCHAR(m) 65535 变长字符
TEXT 16KB 可变长度。最多存储65535个字符。
LONGTEXT 4G 最多存储2的32次方-1个字符
  • char与varchar 类似都是用来保存MySQL中较短的字符串，主要区别在于存储方式不同。
  • char列的长度固定为创建表时声明的长度 长度可以是0-255中的任意值；
  • varchar为可变长字符串 长度可以指定0-65535间的任意值

3.3.4 日期时间类型

类型 字节 最小值 最大值
date 4 1000-01-01 9999-12-31
datetime 8 1000-01-01 00:00:00 9999-12-31 23:59:59
timestamp 4 1970-01-01 08:00:01 2038年某个时刻
time 3 -838:59:59 838:59:59
year 1 1901 2155

timestamp和datetime的区别：

  1. timestamp的取值范围小，最大到2038年的某个时间，datetime取值范围更大；两者都支持 on update current_timestamp属性 使得日期列可以随着其他列的更新而自动更新为最新时间.
  2. timestamp的插入和查询受到时区的影响 更能反映出实际的日期；datetime只能反映插入时当时的时区
  3. timestamp的属性受到MySQL版本和服务器sqlMode的影响很大

设置自动更新示例：

CREATE TABLE t2(
	c1 int,
    -- 添加了2个属性 使得此纪录其他数据更新时这列的时间也会更新
	c2 TIMESTAMP default CURRENT_TIMESTAMP on UPDATE CURRENT_TIMESTAMP
)

4.DDL

数据定义语言，主要包含:

  1. 创建库/表CREATE
  2. 删除库/表DROP
  3. 更新表结构ALTER

4.1 操作库

  • 创建数据库
    语法: create database 数据库名称 [if not exsits];
CREATE DATABASE mydb;   --数据库一旦创建成功 数据库的名称无法更改的。
  • 删除数据库
    语法：DROP DATABASE mydb 数据库名称
DROP DATABASE mydb; -- 删除数据库   数据无法回滚
  • 修改数据库
    语法：ALTER DATABASE 数据库名称 参数
ALTER DATABASE mydb COLLATE Chinese_PRC_CI_AS;   --修改数据库排序规则

4.2 操作表

  • 创建表
  • 语法:

CREATE TABLE 表名(

列1 数据类型 [约束],
列2 数据类型 [约束],
列3 数据类型 [约束],
…
列n 数据类型 [约束]

);

注意：

  1. 列名必须要唯一，在mysql里面 所有的列名都必须是小写，有多个单词 使用_关联即可。
  2. 表名一般还是小写 不要使用复数 有多个单词 使用_关联即可
  3. 映射的概念:
    表==>实体类 表字段===>类的属性 表字段的数据类型==>属性的数据类型 表每行记录===>类的每个对象
CREATE TABLE student(
   id int,
   stu_name varchar(20),
   stu_age tinyint(2) unsigned,
   stu_score float(4,1),
   birthday  date,
   school_date datetime,
   school_money decimal(6,1) -- 最后一个列不要加逗号
);
  • 删除表
    语法：drop table 表名
DROP TABLE student    -- 数据不会回滚  会一起将表数据全部清除
  • 修改表结构
ALTER TABLE tb_userinfo add address varchar(50);   		-- 1. 新增新的列/字段  address

ALTER TABLE tb_userinfo drop address;					-- 2.删除指定的列

ALTER TABLE tb_userinfo change age user_age tinyint(2) unsigned;	-- 3.修改指定列的列名  age->user_age

ALTER TABLE tb_userinfo change username username char(30);			-- 4.修改指定列的数据类型
ALTER TABLE tb_userinfo modify username varchar(30);

ALTER TABLE tb_userinfo rename userinfo;				-- 5.修改指定表的表名

5.DML

数据操作语言，主要包含：

  1. INSERT 新增
  2. DELETE 删除
  3. UPDATE 修改

5.1 新增记录

新增行记录，可以一次新增一行，也可以批处理新增多行。

  • 新增并赋值所有列
    语法：insert into 表名 values (数据1,数据2…数据n); 数据的数量取决于列的数量 不建议使用。
INSERT INTO userinfo VALUES (1,'张三',20,'1234','男','游戏,代码',100000,'2000-01-01','2022-12-03 12:00:00',null);
  • 新增并赋值指定列
    语法：insert into 表名 (列1,列2…列n) values (数据1,数据2…数据n);
INSERT INTO userinfo (id,username,password,create_time) VALUES (4,'李四','1111',now());

5.2 修改记录

  • 修改记录
    语法：UPDATE 表名 SET 列1=新数据1,列2=新数据3…列n=新数据n WHERE 条件1 AND/OR 条件2
UPDATE userinfo SET user_age=18,gender='男',birthday=now(),update_time=now() WHERE id=4;

5.3 删除记录

  • 删除记录
    语法：DELETE from 表名 where 条件1 and/or 条件2;
DELETE FROM userinfo WHERE username='张三';
DELETE FROM userinfo WHERE id=1001;
DELETE FROM userinfo WHERE user_age IS NULL;
DELETE FROM userinfo WHERE id=1001 OR id = 3;
DELETE FROM userinfo WHERE id IN (1002,4);
  • 清空表数据
    语法：DELETE FROM 表名; 清空表数据(在开启事务的前提下 数据可以回滚的)
DELETE FROM userinfo;    --不加条件，清空表数据

6.DQL

6.1 基本语法

SELECT  DISTINCT
 列名1,列名2....列名n  /*要查询的字段，多个字段用逗号隔开*/
FROM
 表1,表2...表n /*要查询的表名称*/
[WHERE 条件1 AND/OR 条件1] -- 条件过滤
[GROUP BY 列] -- 根据指定的列进行分组 
[HAVING 条件1 AND/OR 条件1] -- 条件过滤(分组之后的数据进行过滤)
[ORDER BY 列1,列2 ASC/DESC] -- 根据列进行排序  默认升序  ASC 降序: DESC
[LIMIT ?/?,?] -- 限定结果集(分页查询);

6.2 基础查询

-- 1.查询所有列
SELECT * FROM stu;
-- 2.查询指定列
SELECT sid,sname,sage FROM stu;

6.3 条件查询

  • 条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用如下运算符及关键字：
    =、!=、<>不等于、<、<=、>、>=；
  • BETWEEN…AND；是否满足一个区间范围 >= <=
  • IN(set)；条件的集合
  • IS NULL；
  • AND； 连接多个条件的查询
  • OR；or 满足其中一个条件就可以
  • NOT；
-- 1、查询学生性别为女，并且年龄50的记录
SELECT * FROM stu WHERE gender='FEMALE' AND age=15;
-- 2、查询学号为S_1001，S_1002，S_1003的记录
SELECT * FROM stu WHERE sid ='s_1001' OR sid ='s_1002' OR sid ='s_1003';
SELECT * FROM stu WHERE sid IN ('s_1001','s_1002','s_1003');

-- 3、查询学号不是S_1001，S_1002，S_1003的记录
SELECT * FROM stu WHERE sid !='s_1001' AND sid <>'s_1002' AND sid !='s_1003';
SELECT * FROM stu WHERE  sid NOT IN ('s_1001','s_1002','s_1003');

-- 4、查询年龄为null的记录
SELECT * FROM stu WHERE age IS NOT NULL;
-- 5、查询年龄在20到40之间的学生记录
SELECT * FROM stu WHERE age>=20 AND age<=40;
SELECT * FROM stu WHERE age BETWEEN 20 AND 40;
-- 6、查询性别非男的学生记录
SELECT * FROM stu WHERE gender !='male' OR gender IS NULL;

6.4 模糊查询

  • 模糊查询，处理字符类型.
  • _:通配任意一个字符(数字，字母，汉字，特殊符号等)
  • %：表示0或多个字符
-- 1、查询姓名由5个字符构成的学生记录 
-- 通配符: 数据  一个字符使用 _  
SELECT * FROM stu WHERE  sname LIKE '_____';
SELECT * FROM stu WHERE  CHAR_LENGTH(sname)=5;
-- 通配不定数量的字符的数据  %
-- 2、查询姓名以“z”开头的学生记录
SELECT * FROM stu WHERE  sname LIKE 'z%';

-- 3、查询姓名中第2个字母为“i”的学生记录
SELECT * FROM stu WHERE  sname LIKE '_i%';
-- 4、查询姓名中包含“a”字母的学生记录
SELECT * FROM stu WHERE  sname LIKE '%a%';

6.5 字段控制查询

  • DISTINCT

  • IFNULL(列名,默认值)

  • AS

6.6 排序

  • 排序。对行记录进行升序或者降序排列。
  • 可以跟单个字段，多个字段，表达式，函数，别名进行排序。
  • 默认是升序 ASC , 降序为DESC.
-- 查询所有学生记录，按成绩进行降序排序
-- 缺省是ASC升序
SELECT * FROM stu ORDER BY age DESC;
-- 查询所有学生记录，首先先按成绩进行降序排序，如果成绩相同，按名字进行升序排序
SELECT * FROM stu ORDER BY age DESC, sid ASC;

-- 查询emp的薪水 降序排列  sal相同  根据comm进行降序排序
-- 多个列排序的话  根据其中一个排序  列的数据相同了 再根据另外一个列排序
-- 一次select里面只能有一个 ORDER BY
SELECT * FROM emp ORDER BY sal DESC,comm DESC;
-- 查询sal>1500 根据sal降序排列  sal相同  根据comm进行降序排序
SELECT * FROM emp WHERE sal>1500 ORDER BY sal DESC,comm DESC;

6.7 聚合函数

  • 还可以称为组函数、分组函数
    用作统计使用，又称为聚合函数或者统计函数或者组函数
  • 聚合函数是用来做纵向运算的函数：
  • COUNT(字段/列)：统计指定列不为NULL的记录行数；一般使用count（*）统计行数
  • MAX(字段/列)：计算指定列的最大值，如果指定列是字符串类型，那么使用字符串排序运算；
  • MIN(字段/列)：计算指定列的最小值，如果指定列是字符串类型，那么使用字符串排序运算；
  • SUM(字段/列)：计算指定列的数值和，如果指定列类型不是数值类型，那么计算结果为0；
  • AVG(字段/列)：计算指定列的平均值，如果指定列类型不是数值类型，那么计算结果为0；
  • SUM、AVG一般处理数值型
  • MAX、MIN、COUNT可以处理任意数据类型
  • 分组函数都忽略了NULL值，可以和DISTINCT搭配使用
  • 注意点：组函数可以出现多个，但是不能嵌套；如果没有GROUP BY 子句，结果集中所有行数作为一组
-- 查询emp表中有佣金的人数,统计指定列不为NULL的记录行数
SELECT count(comm) a FROM emp;
-- SELECT 200+null;
SELECT count(empno) AS '总人数' FROM emp;
SELECT count(*) AS '总人数' FROM emp;
SELECT count(1) AS '总人数' FROM emp;
-- 查询emp表中有佣金的人数
SELECT count(comm) FROM emp;
-- 查询emp表中月薪大于2500的人数：
SELECT * FROM emp WHERE sal>2500;
-- 统计月薪与佣金之和大于2500元的人数： ifnull(表达式1，表达式2)  如果表达式1为null那么取表达式2的值，否则取表达式1的值
SELECT * FROM emp WHERE sal+ifnull(comm,0) >2500;
-- 查询有佣金的人数，以及有领导的人数：
SELECT count(comm) FROM emp WHERE mgr is not null;
-- 查询所有雇员月薪和：
SELECT sum(sal) 总薪资  FROM emp;
-- 查询所有雇员月薪和，以及所有雇员佣金和：
SELECT sum(sal) 总薪资,SUM(comm) 总佣金 FROM emp;
-- 查询所有雇员月薪+佣金和
SELECT sum(sal+ifnull(comm,0)) 总佣金 FROM emp;
SELECT sum(sal)+sum(ifnull(comm,0)) 总佣金 FROM emp;
-- 统计所有员工平均工资
SELECT avg(sal) 平均薪资 FROM emp;
-- 查询最高工资和最低工资
SELECT max(sal),min(sal) FROM emp;

6.8 分组查询

  • 普通分组查询
  • 对行记录进行分组查询。
  • 查询出来的字段要求是 GROUP BY 后的字段，查询字段中可以出现组函数
  • SELECT 后面的字段 是分组字段
  • GROUP BY后面可以跟聚合函数 可以起别名
-- 	查询每个部门的部门编号和每个部门的工资和
SELECT deptno,SUM(sal) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门的人数
SELECT deptno,SUM(sal),COUNT(1) FROM emp GROUP BY deptno;
-- 	查询每个部门的部门编号以及每个部门员工工资大于1500的人数：
SELECT deptno,COUNT(1) FROM emp WHERE sal>1500 GROUP BY deptno;
-- 查询stu  根据gender统计学生的数量
SELECT gender,COUNT(*) FROM stu GROUP BY gender;
  • 分组后过滤
  • 与WHERE的功能一样，都是用来实现条件过滤。
  • WHERE VS HAVING
    1. WHERE是对分组前进行过滤；HAVING是对分组后进行过滤
    2. WHERE是比分组先执行的，HAVING是在分组之后执行的；
    3. WHERE中不能出现分组/聚合函数，HAVING中可以出现
    4. HAVING后面可以跟别名
-- 查询工资总和大于9000的部门编号以及工资和：
SELECT deptno,sum(sal)  FROM emp GROUP BY deptno HAVING sum(sal)>9000;
-- having中使用别名
SELECT deptno,sum(sal) 总薪资 FROM emp GROUP BY deptno HAVING 总薪资>9000;
-- 查询部门员工个数大于3的，having中使用了别名
SELECT COUNT(1) AS cc,deptno FROM emp GROUP BY deptno HAVING cc>3;

6.9 LIMIT

  • 分页查询。限定查询的结果集。
  • 对最后的结果进行限定（mysql独有）
  • SELECT * FROM 表 LIMIT size; 指定从第一条记录开始查询 查size条
  • SELECT * FROM 表 LIMIT ?,size 指定从第n条记录(index)开始查询 查size条
-- 分页展示员工表的数据
-- size:5 每页展示的数据量
-- totalPage=totalCount/size;
-- totalCount = SELECT COUNT(*) FROM emp;  14
-- totalPage=3
-- 用户请求查询第page页的数据
-- 第一页 page=1
-- SELECT * FROM emp WHERE 1=1 LIMIT size;
-- SELECT * FROM emp WHERE 1=1 LIMIT 5; -- 从第1行记录查询 展示size条数据
SELECT * FROM emp WHERE 1=1 LIMIT 0,5;
-- 第二页page=2
-- SELECT * FROM emp WHERE 1=1 LIMIT start,size;
-- start: 查询第几行记录的index  size: 查询的每页展示的数据量
SELECT * FROM emp WHERE 1=1 LIMIT 5,5;
-- 第三页page=3
SELECT * FROM emp WHERE 1=1 LIMIT 10,5;

-- 通用的写法
SELECT * FROM emp WHERE 1=1 LIMIT (page-1)*size ,size;

6.10 多表查询

  • 内连接
  • 多表等值连接的结果是多表的交集部分，N表连接，至少需要N-1个连接条件，没有顺序要求，一般起别名
  • 非等值连接，只要不是等号连接的都是非等值连接
-- 多表关联查询  起别名查询  AS
-- 多表关联查询 有可能出现笛卡尔积的结果。必带条件 where(表与表的关系---> 外键列)
-- 2张  至少1个  3 至少2
-- 1、查询员工信息，要求显示员工号，姓名，月薪，部门名称
-- 笛卡尔积 （a, b） (1,2,3) --(a,1) (a,2) (a,3) (b,1) (b,2) (b,3)--》会生成一个中间表
SELECT
 e.empno,e.ename,e.sal,d.deptno,d.dname,d.loc
FROM
emp AS e,dept AS d
WHERE e.deptno=d.deptno ORDER BY d.deptno;

-- 2、查询员工信息，要求显示：员工号，姓名，月薪，薪水的级别
SELECT
  e.empno,e.ename,e.sal,s.grade
FROM
 emp AS e,salgrade AS s
-- WHERE e.sal>=s.LowSAL AND e.sal<=s.HISAL ORDER BY s.GRADE;
WHERE e.sal BETWEEN s.lowsal AND s.hisal ORDER BY s.GRADE;

-- 3、查询员工信息，要求显示：员工号，姓名，月薪，部门名称,薪水的级别
SELECT
 e.*,d.dname,d.loc,s.grade
FROM
emp AS e,dept AS d,salgrade AS s
WHERE 
  e.deptno=d.deptno AND e.sal BETWEEN s.lowsal AND s.hisal;
  • 外连接
  • 外连接，有主表有从表，主表肯定会显示完整的内容
  • 左外连接，以左表为主，右表没有的数据使用NULL或者0进行填充。
  • 右外连接，以右表为主，左表 没有的数据使用NULL或者0进行填充。
  • 左外与右外的条件必须使用ON进行关联。
  • ON后面的条件（ON条件）和WHERE条件的区别：
    1. ON条件：是过滤两个链接表笛卡尔积形成中间表的约束条件。
    2. WHERE条件：在没有ON的单表查询中，是限制物理表或者中间查询结果返回记录的约束。
      在两表或多表连 接中是限制连接形成最终中间表的返回结果的约束。
  • 建议：ON只进行外连接操作，WHERE只过滤中间表的记录
-- 外连接
-- 查询员工信息，要求显示员工号，姓名，月薪，部门名称 使用内连接和等值连接等同
SELECT empno,ename,sal,emp.deptno,dname FROM emp INNER JOIN dept on emp.deptno=dept.deptno;
-- 左外连接（左连接） 左边的表内容全部显示，右边的表没有的以null进行填充
SELECT empno,ename,sal,emp.deptno,dname FROM emp LEFT JOIN dept on emp.deptno=dept.deptno;
-- 右外连接，右表内容全部显示，左表没有的以null进行填充
SELECT empno,ename,sal,emp.deptno,dname FROM emp RIGHT JOIN dept on emp.deptno=dept.deptno;

-- SELECT deptno,COUNT(*) FROM emp GROUP BY deptno; 
-- 查询所有部门的名称以及每个部门的员工数量 count
-- 展示部门编号  部门名称 位置  员工数量
SELECT
 d.*,COUNT(*) AS '员工数量'
FROM
dept AS d,emp AS e
WHERE e.deptno=d.deptno
GROUP BY d.deptno;

SELECT
 d.*,COUNT(*) AS '员工数量'
FROM
dept AS d INNER JOIN emp AS e ON e.deptno=d.deptno
WHERE 1=1 AND 2=2
GROUP BY d.deptno;

SELECT
 d.*,COUNT(e.empno) AS '员工数量'
FROM
emp AS e RIGHT JOIN dept AS d   ON e.deptno=d.deptno
GROUP BY d.deptno;
  • 自连接
  • 本表与本表进行关联查询。 把一张表看成多张表进行处理。
  • 通过别名，将同一张表视为多张表；
  • 什么情况下使用自连接；同一张表中某个字段要去关联另外一个字段
-- 1、查询员工信息，员工的老板的名称。 14
-- mgr 
SELECT
 e1.*,e2.empno,e2.ename
FROM
emp AS e1,emp AS e2
WHERE e1.mgr=e2.empno;

-- 外连接
SELECT
 e1.*,e2.empno,e2.ename
FROM
emp AS e1 LEFT JOIN  emp AS e2 ON e1.mgr=e2.empno;

-- 2、查询员工信息  14 展示部门名称 薪资级别 上级领导名
SELECT
 e1.empno,e1.ename,e1.mgr,d.deptno,d.dname,s.grade,e2.empno,e2.ename
FROM
emp AS e1,dept AS d,salgrade AS s,emp AS e2
WHERE
 e1.deptno=d.deptno AND e1.mgr=e2.empno AND e1.sal BETWEEN s.LowSAL AND s.hisal;

-- 外连接与普通关联可以在一起使用
SELECT
 e1.empno,e1.ename,e1.mgr,d.deptno,d.dname,s.grade,e2.empno,e2.ename
FROM
emp AS e1 LEFT JOIN emp AS e2 ON e1.mgr=e2.empno 
,dept AS d ,salgrade AS s
WHERE
  e1.deptno=d.deptno AND e1.sal BETWEEN s.LowSAL AND s.hisal;

6.11 子查询

子查询也可称为嵌套查询。

  • 子查询的作用：查询条件未知的事物.
  • 查询条件已知的问题：例如：查询工资为800的员工信息
  • 查询条件未知的问题：例如：查询工资为20号部门平均工资的员工信息

根据子查询查询的结果不同，分为:

  1. 标量子查询(子查询结果为单个值)
  2. 列子查询(子查询结果为1列)
  3. 行子查询(子查询结果为1行)
  4. 表子查询(子查询结果为多行多了)
  • 标量子查询
子查询返回的结果是单个值(数字，字符串，日期等)，最简单的方式。
常用的操作符:
= <> > < >= <=等

-- 1.查询'销售部'的所有员工信息
-- 2.查询smith入职之后的员工信息
-- 3.查询员工的薪资= 20号部门的平均的薪资
  • 列子查询
子查询返回的结果是1列(可以是多行)。
常用操作符:
IN , NOT IN, ANY, SOME, ALL

any: 子查询返回的列表中，有任意一个满足即可。
some: 与any相同，使用some的地方都可以使用any
all: 子查询返回列表的所有值都必须满足。

-- 1. 查询销售部，研发部所有员工信息
SELECT * FROM emp WHERE deptno in 
(SELECT deptno FROM dept WHERE dname='销售部' or dname='研发部' )

-- 2. 查询比财务部所有人工资都高的员工信息
SELECT * FROM emp WHERE sal > ALL (select sal from emp WHERE deptno=(select deptno from dept WHERE dname='财务部'))

-- 3.查询比财务部任意一人工资高的员工信息
SELECT * FROM emp WHERE sal > any (select sal from emp WHERE deptno=(select id from dept WHERE dname='财务部'))
  • 行子查询
子查询返回的结果是一行(可以是多列)。
常用操作符：
=  <>  IN  NOT IN

-- 1. 查询smith薪资与直属上级相同的员工信息。
SELECT sal,mgr FROM emp WHERE ename='smith';
SELECT * FROM emp WHERE sal=8000 AND mgr=7902;
SELECT * FROM emp WHERE (sal,mgr)=(8000,7902);  
SELECT * FROM emp WHERE (sal,mgr)=(SELECT sal,mgr FROM emp WHERE ename='smith');
  • 表子查询
子查询返回的结果是多行多列。
常用操作符: IN

-- 1.查询与smith，ford职位，薪资相同的员工信息。
SELECT sal,job FROM emp WHERE ename='smith' OR ename = 'ford';
SELECT * FROM emp WHERE (sal,job) IN (SELECT sal,job FROM emp WHERE ename='smith' OR ename = 'ford') 

  --2.查询入职日期是‘1981-12-03’之后的员工信息，及其部门信息
SELECT * FROM (SELECT * FROM emp WHERE hiredate>'1981-12-03') AS t1 ,dept AS t2 WHERE t1.deptno=t2.id;
阅读全部

相关推荐

  1. mysql学习记录

    2024-06-07 05:42:02       42 阅读

  7. MySQL学习记录

    2024-06-07 05:42:02       45 阅读

  9. MySQL学习记录

    2024-06-07 05:42:02       25 阅读

最近更新

  3. docker php8.1+nginx base 镜像 dockerfile 配置

    2024-06-07 05:42:02       94 阅读

  4. Could not load dynamic library ‘cudart64_100.dll‘

    2024-06-07 05:42:02       100 阅读

  9. 在Django里面运行非项目文件

    2024-06-07 05:42:02       82 阅读

  19. Python语言-面向对象

    2024-06-07 05:42:02       91 阅读

  20. 如何分清楚常见的 Git 分支管理策略Git Flow、GitHub Flow 和 GitLab Flow

    2024-06-07 05:42:02       85 阅读

热门阅读

  2. minio是什么？minio支持什么语言？

    2024-06-07 05:42:02       30 阅读

  3. 代码随想录训练营第二天 977有序数组的平方 209长度最小的子数组 59螺旋矩阵II

    2024-06-07 05:42:02       34 阅读

  4. NLP入门——数据预处理：编码规范化

    2024-06-07 05:42:02       24 阅读

  15. 运维开发详解

    2024-06-07 05:42:02       29 阅读

  17. vue快速入门

    2024-06-07 05:42:02       32 阅读

  19. js前端怎么封装

    2024-06-07 05:42:02       28 阅读

  20. String，StringBuffer，StringBuilder的区别？

    2024-06-07 05:42:02       29 阅读

  24. 持续总结中！2024年面试必问 20 道 Kafka面试题（九）

    2024-06-07 05:42:02       36 阅读

  28. Day1——一些感想，学习计划和自我激励（不重要，跳过吧）

    2024-06-07 05:42:02       28 阅读