MySQL笔记（自用）

已于 2023-04-17 23:01:40 修改
阅读量210 收藏 2
点赞数 1
分类专栏： MySQL 文章标签： mysql 数据库 笔记
于 2023-04-17 23:01:19 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_60532903/article/details/130211084
版权

MySQL笔记

一、SQL（访问和处理数据库语言）

1.1简介

SQL(Structured Query Language)用于访问和处理数据库的标准的计算机语言

1.2语法特点

  • SQL对关键字大小写不敏感

  • SQL语句可以以单行或多行书写，以分号结尾

  • SQL的注释：

    • 单行注释

      • – 【–后面一定要有一个空格】
      • #【#的后面可以没有空格】

      例如：SELECT * FORM emp；–

    • 多行注释

      /* …*/

二、DDL（数据定义语言）

【DDL】数据定义语言，包括：

2.1对数据库的常用操作

功能SQL
查看所有的数据库show ;
创建数据库create database[if not exists] mydb1 [charset=utf8]
切换（选择要操作的）数据库use mydb1;
删除数据库drop database[if exists] mydb1;
修改数据库编码alter database mydb1 character set utf8;
-- 1.DDL操作之数据库操作
#查看所有数据库
show databases;
#创建数据库，数据库名字叫mydb1
CREATE DATABASE mydb1;
-- 如果不存在该数据库则创建
CREATE DATABASE IF NOT EXISTS mydb1;
#选择使用哪一个数据库
use mydb1;
#删除数据库
drop DATABASE mydb1;
-- 如果存在该数据库则删除
drop DATABASE if exists mydb1;
#修改数据库编码为utf8
alter database mydb1 character set utf8;

2.2对表结构的常用操作

2.2.1创建表
  • 格式：
#创建表是构建一张空表，指定这个表的名字，这表有几列，每一列叫什么名字，以及每一列存储的数据类型
create table [if no exists]表名(
	字段名1 类型 [(宽度)] [约束条件] [comment '字段说明'],
	字段名2 类型 [(宽度)] [约束条件] [comment '字段说明'],
	字段名3 类型 [(宽度)] [约束条件] [comment '字段说明'] 
)[表的一些设置];

【字段名】：列的名字

  • 例如:
use mydb1;
create table if not exists student(
	sid int,
	name varchar(20),
    gender varchar(20),
    age int,
    birth date,
    address varchar(20)
);
2.2.2数据类型

  • 数值类型

    类型范围用途
    tinint（-128，127）小整数值
    smallint（-32 768，32 767）大整数值
    mediumint（-8 388 608，8 388 607）大整数值
    int/integer（-2 147 483 648，2 147 483 647）大整数值
    bingint（-9 233 372 036 854 775 808，9 233 372 036 854 775 807）极大整数值
    float（-3.402 823 466 E+38，3.402 823 466 351E+38）单精度浮点数值
    double（-1.797 693 134 862 315 7 E+308，1.797 693 134 862 315 7 E+308）双精度浮点数值
    decimal依赖于M和D的值小数值

  • 字符串类型

    类型用途
    char定长字符串
    varchar变长字符串
    tinyblob不超过255个字符的二进制字符串
    tinytext短文本字符串
    blob二进制的长文本字符串
    text长文本数据
    mediumblob二进制形式的中等长度文本数据
    mediumtext中等长度文本数据
    longblob二进制形式的极大文本数据
    longtext极大文本数据

    【注意】：在MySQL中字符串用单引号，如：‘ 张三 ’

  • 日期类型

    类型范围格式用途
    date100-01-01/9999-12-31YYYY-MMM-DD日期值
    time‘-838：59：59’/‘838：59：59’HH:MM:SS时间值或持续时间
    year1901/2155YYYY年份值
    datetime1000-01-01 00：00：00/9999-12-31 23：59：59YYYY-MM-DD HH:MM:SS混合日期和时间值
    timestamp1970-01-01 00：00：00/2038YYYYMMDD HHMMSS混合日期和时间值，时间戳
2.2.3其他操作
功能SQL
查看当前数据库的所有表名称show tables;
查看指定某个表的创建语句show create table 表名;
查看表结构desc 表名
删除表drop table 表名
#3.查看当前数据库所有的表
show tables;
#4.查看指定表的创建语句
show create table student;

CREATE TABLE `student` (
  `sid` int DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `gender` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `birth` date DEFAULT NULL,
  `address` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3

#5.查看表结构
desc student;
#6.删除表
drop table student;

2.3修改表结构

2.3.1修改表结构格式

  • 添加列

    格式：

    alter table 表名 add 列名 类型(长度) [约束];

    例子：

    #为student表添加一个新的字段为：系别dept 类型varchar（20）
alter table student add dept varchar(20);

  • 修改列名和类型

    格式：

    alter table 表名 change 旧列名 新列名 类型(长度) [约束];

    例子：

    #将student表的dept字段更换为department varchar（30）
alter table student change dept department varchar(30);

  • 修改表删除列

    格式：

    alter table 表名 drop 列名;

    例子：

    #删除student表中department这列
alter table student drop department;

  • 修改表名

    格式：

    rename table 表名 to 新表名;

    例子：

    #修改student改名为stu
rename table student to sti;

三、DML（数据操作语言）

DML是指数据操作语言，用来对数据库中表的数据记录进行更新

关键字：（增删改）

  • 插入insert
  • 删除delete
  • 更新update

3.1数据插入

格式：

insert into 表(列名1，列名2，列名3...)
       values (值1，值2，值3...)，
              (值1，值2，值3...)，
              ...;-- 向表中插入某些行的数据
insert into 表 values (值1，值2，值3...)，
                      (值1，值2，值3...)，
                      ...;-- 向表中插入所有列的数据

列子：

insert into student(sid,name,gender,age,birth,adddress,score) values(1001,'男',18,'1996-12-23','北京',83.5);
insert into student values(1001,'男',18,'1996-12-23','北京',83.5);

3.2数据修改

格式：

update 表名 set 字段名=值，字段名=值...;
update 表名 set 字段名=值，字段名=值... where 条件;

列子：

#将所有学生的地址改为重庆
update student set address = '重庆' ;
#将id为1004的学生地址改为北京
update student set address = '北京' where id = 1004;
#将id为1005的学生的地址修改为北京，成绩修改为100
update student set address = '北京',score = 100 where id = 1005;

3.3数据删除

格式：

delete from 表名 [where 条件];
truncate table 表名;
truncate 表名;

例子：

#删除sid为1004的学生数据
delete from student where sid = 1004;
#删除表所有数据
delete from student;
#清空表数据
truncate table student;
truncate student;

【注意】：delete只删除内容，而truncate类似于删除表后再创建表

四、mysql约束

约束（constraint）：表中数据的限制条件【在创建表的时候使用】

常用约束：

  • 主键约束（primary key）PK
  • 自增长约束（auto_increment）
  • 非空约束（not null）
  • 唯一性约束（unique）
  • 默认约束（default）
  • 零填充约束（zerofill）
  • 外键约束（foreign key）FK

4.1主键约束

4.1.1概念

  1. MySQL主键约束是一个列或多个列的组合，其值能唯一地标识表中的每一行，方便在RDBMS中尽快的找到某一行

  2. 主键约束 ≈ 唯一约束+非空约束

    主键约束列不允许重复，也不允许出现空值

  3. 每个表最多只允许一个主键

  4. 主键约束的关键字是primary key

  5. 当创建主键的约束时，系统默认会在所在的列和列组合上建立对应的唯一索引

4.1.2添加单列主键

方法一：在定义字段的同时指定主键

格式：

create table 表名(
	...
    <字段名> <数据类型>primary key
    ...
);

例子：

create table emp1(
	eid int primary key,
    name varchar(20)
);

方法二：定义字段后再指定主键

格式：

create table 表名(
	...
    <字段名> <数据类型>primary key
    ...
    [constraint <约束名>] primary key [字段名]
);

例子：

create table emp2(
	eid int primary key,
    name varchar(20),
    constraint pk1 primary key(id) -- constraint pk1 可省略
);
4.1.3添加多列联合主键

【注意】：当主键是由多个字段组成时，不能直接在字段后面声明主键约束；联合主键也是一个主键

格式：

create table 表名(
	...
    primary key(字段1,字段2,...,字段n)
);

例子：

create table enp3(
	name varchar(20),
    deptid int,
    primary key(name,deptid)
);
4.1.4通过修改表结构添加主键

格式：

create table 表名(
	...
);
alter table <表名> add primary key(字段列表);

例子：

create table emp4(
	eid int,
    name varchar(20),
    deptid int,
    salsry double
);
alter table emp4 add primary key(eid);
4.1.5删除主键

格式：

alter table <数据表名> drop primary key;

例子：

#删除单列主键
alter table emp1 drop primary key;
#删除联合主键
alter table emp5 drop primary key;

4.2自增长约束

4.2.1概念和特点
  1. 通常和主键放一块，功能是实现主键的自增长
  2. 方法：通过给字段添加auto_increment属性来实现主键自增长
  3. 默认初始值为1，每增一条记录，字段值自动加1
  4. auto_increment约束的字段必须具备not null属性
  5. auto_increment约束的字段只能是整数类型
  6. auto_increment约束字段的最大值受该字段的数据类型约束
4.2.2添加自增长约束

格式：

字段名 数据类型 auto_increment

例子：

create table user1(
	id int primary key auto_increment,
    name varchar(20)
);
4.2.3指定自增字段初始值

方法一：创建表时指定

create table uesr2(
	id int primary key auto_increment,
    name varchar(20)
)auto_increment=100;

方法二：创建表之后指定

create table user3(
	id int primary key auto_increment,
    name varchar(20)
);
alter table user3 auto_increment = 100;
4.2.4delete和truncate删除后自增列的变化
  • delete数据后自动增长从断点开始
  • truncate数据后自动增长从1（默认起始值）开始

4.3非空约束

功能：指定字段的值不能为空

4.3.1添加非空约束

格式：

#方法一
<字段名><数据类型>not null;
#方法二
alter table 表名 modify 字段 类型 not null;

例子：

#方式一：创建表时指定
create table user6(
	id int,
    name varchar(20) not null,
    address varchar(20) not null
);

#方法二：创建表之后指定
create table user7(
	id int,
    name varchar(20),
    address varchar(20)
);
alter table user7 modify name varchar(20)not null;
alter table user7 modify address varchar(20)not null;
4.3.2删除非空约束

格式：

alter table 表名 modify 字段 类型;

例子：

alter table user7 modify name varchar(20);
alter table user7 modify address varchar(20);

4.4唯一约束

4.4.1概念

所有记录中字段的值不能重复出现

4.4.2添加唯一约束

格式：

#方法一
<字段名><数据类型> unique
#方法二
alter table 表名 add constraint 约束名 unique(列);

例子：

#创建表时指定
create table user8(
	id int,
    name varchar(20),
    phonenumber varchar(20) unique -- 指定唯一约束
);

#创建表后指定
create table user9(
	id int,
    name varchar(20),
    phonenumber varchar(20)
);
alter table user9 add constraint unique1 unique(phonenumber)
4.4.3删除唯一约束

格式：

alter table <表名> drop index <唯一约束名>

例子：

alter table user9 drop index unique_1;

4.5默认约束

4.5.1概念

指定某列的默认值

4.5.2添加默认约束

格式：

#方法一
<字段名><数据类型> default <默认值>;
#方法二
alter table 表名 modify 列名 类型 default 默认值;

#创建表时指定
create table user10(
	id int,
    name varchar(20),
    address varchar(20) default '北京' -- 指定默认约束
);

#创建表之后指定
create table user11(
	id int,
    name varchar(20),
    address varchar(20) 
);
alter table user11 modify address varchar(20) default '深圳';
4.5.3删除默认约束

格式：

alter table <表名> change column <字段名> <类型> default null;

例子：

alter table user11 modify address varchar(20) default null;

4.6零填充约束

  1. 插入数据时，当该字段的值的长度小于定义的长度时，会在该值的前面补上相应的0
  2. zerofil默认为int（10）
  3. 使用zerofill时，默认会自动加unsigned（无符号）属性【数值范围是原范围的两倍】

  • 操作

    create table user12(
	id int zerofill,-- 零填充约束
    name varchar(20)
);

  • 删除

    alter table user12 modify id int;

五、DQL（基本查询）

格式：

select
	[all|distinct]
	<目标列的表达式1> [别名],
	<目标列的表达式2> [别名]...
from <表名或视图名> [别名]，<表名或视图名> [别名]...
[where<条件表达式>]
[group by <列名>]
[having <条件表达式>]
[order by <列名> [asc|desc]]
[limit <数字或者列表>];

简化格式：

select *| 列名 from 表 where 条件

5.1数据准备

第一步：创建数据库和表

-- 创建数据库
create database if not exist mydb2;
-- 创建表
create table product(
	pid int primary key auto_increment,-- 商品编号
    pname varchar(20) not null,-- 商品名字
    price double,-- 商品价格
    category_id varchar(20)-- 商品所属分类  
);

第二步：添加数据

insert into product values(null,'海尔洗衣机',5000,'c001');
insert into product values(null,'美的冰箱',3000,'c001');

5.2简单查询

-- 查询所有的商品
select * from product;
-- 查询商品名和商品价格
select pname,price from product;
-- 别名查询.使用的关键字是as（as可省略）
#表别名
select * from product as p; -- p是别名
#列别名
select pname as pn from product;--pn是列别名
-- 去掉某列的重复值
select distinct price from product;
-- 去掉一模一样的数据
select distinct * from product;
-- 查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示
select pname,price+10 newprice from product;-- 显示的价格是新价格 别名为newprice

5.3运算符

  • 算术运算符

    算术运算符说明
    +加法运算
    -减法运算
    *乘法运算
    / 或 DIV除法运算，返回商
    % 或 MOD除法运算，返回余数

  • 比较运算符

    比较运算符说明
    =等于
    < 和 <=小于 和 小于等于
    > 和 >=大于 和 大于等于
    <=>两个操作码均为NULL时，所得值为1；当一个操作码为NULL时，所得值为0
    <> 或 !=不等于
    IS NULL 或 ISNULL判断一个值是否为NULL
    IS NOT NULL判断一个值是否不为NULL
    LEAST当有两个或多个参数时，返回最小值
    GREATEST当有两个或多个参数时，返回最大值
    BETWEEN AND判断一个值是否落在两个值之间
    IN判断一个值是IN列表中的任意一个值
    NOT IN判断一个值不是IN列表中的任意一个值
    LIKE通配符匹配
    REGEXP正则表达式匹配
    #查询商品价格在200到1000之间所有商品
select * from product where price between 200 and 1000;
select * from product where price >= 200 and price <= 1000;
select * from product where price >= 200 && price <= 1000;

#查询商品价格是200或800的所有商品
select * from product where price in(200,800);

#查询含有‘箱’字的所有商品
-- %用来匹配任意字符
select * from product where pname like '箱%';-- 只要第一个字是‘箱’就行
select * from product where pname like '%箱%';-- 只要中间出现‘箱’就行
select * from product where pname like '%箱';-- 只要最后一个字是‘箱’就行

#查询第二个字为‘的’所有商品
-- _用来匹配单个字符
select * from product where pname like '_的%';

#查询category_id为null的商品
select * from product where category_id is null;

#使用least求最小值
-- 如果求最小值时，有个值为null，则不会比较，结果直接为null
select least(10,5,20) as small_number;
#使用greatest求最大值
-- 如果求最大值时，有个值为null，则不会比较，结果直接为null
select greatest(10,30,20) as big_number;

  • 逻辑运算符

    逻辑运算符说明
    NOT 或者 I逻辑非
    AND 或者 &&逻辑与
    OR 或者 ||逻辑或
    XOR逻辑异或

  • 位运算符

    位运算符说明
    |按位或
    &按位与
    ^按位异或
    <<按位左移
    >>按位右移
    ~按位取反，反转所有比特

5.4排序查询

（order by）

格式：

select
	字段名1,字段名2,......
from 表名
order by 字段名1 [asc|desc],字段名2 [asc|desc]......

特点

  1. asc表示升序，desc表示降序，如果不写默认升序

  2. order by用于子句中可以支持单个字段，多个字段，表达式，函数，别名

    字段必须为数值类型 或者 英文和数字的字符串类型

  3. order by子句，放在查询语句的最后面，LIMIT子句除外

#使用价格降序
select * from product order by price desc;
#在价格降序的基础上，以分类降序
select * from product order by price desc,category_id desc;
#显示商品的价格（去重）并且降序
select distinct price from product order by price desc;

5.5聚合查询

5.5.1聚合函数
聚合函数作用
count（）统计指定列不为NULL的记录行数
sum（）计算指定列的数值和【若指定列类型不是数值类型，那么计算结果为0】
max（）计算指定列的最大值【若指定列是字符串类型，那么使用字符串排序运算】
min（）计算指定列的最小值【若指定列是字符串类型，那么使用字符串排序运算】
avg（）计算指定列的平均值【若指定列类型不是数值类型，那么计算结果为0】
#查询商品的总行数
select count(pid) from product;
select count(*) from product;

#查询价格大于200商品的总条数
select count(pid) from product where price > 200;

#查询分类为‘c001’的所有商品的总和
select sum(price) from product where category_id = 'c001';

#查询商品的最大和最小价格
select max(price) as max_price,min(price) as min_price from product;
5.5.2NULL值的处理

  1. count函数对null值的处理

    若参数为*，则统计所有记录的个数

    若参数为某字段，则不统计含null值的记录个数

  2. sum、avg、max和min函数对null值的处理

    忽略null值的存在

5.6分组查询

（group by）

格式：

select 字段1,字段2... from 表名 group by 分组字段 having 分组条件;

例子：

#统计各个分类商品的个数
select category_id,count(pid) from product group by category_idi;

【注意】：分组之后，select后边只可以跟分组字段和聚合操作

关键字having（作用类似于where）

SQL书写顺序：select -> from -> where -> group by -> having ->order by ->limit

SQL执行顺序:from -> where -> group by -> count -> having -> select ->order by

5.7分页查询

格式：

#显示前n条
select 字段1,字段2... from 表明 limit n 
#分页显示
select 字段1,字段2... from 表明 limit m,n
-- m（整数）：表示从第几条索引开始，计算方式（当前页-1）*每页显示条数
-- n（整数）：表示查询多少条数据

例子：

#查询product表的前5条记录
select * from product limit 5
#从第4条开始显示，显示5条
select * from product limit 3,5

5.8INSERT INTO SELECT语句

功能：将一张表的数据导入到另一张表中

格式：

#方法一
insert into Table2(fidle1,fidle2,...) select value1,value2,... from Table1;
#方法二
insert into Table2 select * from Table1;

【注意】目标表Table2必须存在，所以要先创建目标表

练习中出现的点

  • ifnull（comn，0）

    【如果comn的值为null，则当作0，不为null（comn是一个参数）】

  • 题目要求求”每个“、”各个“的时候 -----> 分组操作

5.9正则表达式

正则表达式：描述一种字符串匹配的规则

关键字：REGEXP

模式描述
^匹配输入字符串的开始位置
$匹配输入字符串的结束位置
·匹配除”\n“之外的任何单个字符
[…]字符集合。匹配所包含的任意一个字符 例如，‘ [abc] ’可以匹配 ”plain“ 中的 ‘a’
[^…]负值字符集合。匹配未包含的任意字符 例如，’ [ ^ abc] ’ 可以匹配 ”plain“ 中的 ‘p’
p1|p2|p3匹配p1或p2或p3例如，‘z | food’ 能匹配” z “ 或 ”food“’（z|f）ood‘能匹配” zood “或” food “
*匹配前面的子表达式零次或多次。例如，zo*能匹配”z“以及”zoo“【 * 等价于{0，}】
+匹配前面的子表达式一次或多次。例如，zo+能匹配”zo“以及”zoo“【 + 等价于{1，}】
{n}n(非负整数)。匹配确定的n次例如，’ o{2} ‘不能匹配“Bob”中的o，但是能匹配“food”中的o
{n，m}m（非负整数）、n（非负整数）【n <= m】，最少匹配n次并且最多匹配m次

六、多表操作

6.1多表关系

  • 一对一关系
  • 一对多/多对一关系
  • 多对多关系

6.2外键约束

6.2.1特点
  • 定义一个外键时，需要遵守下列规则
  1. 主表必须存在于数据库中，或者是当前正在创建的表
  2. 必须为主表定义主键
  3. 主键不能包含空值，但允许在外键中出现空值
  4. 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键
  5. 外键中列的数目必须和主表的主键中列的数目相同
  6. 外键中列的数据类型必须和主表的主键中对应列的数据类型相同
6.2.2操作

方法一：在创建表时设置外键约束

​ 关键字：foreign key

格式：

[constraint <外键名>] foreign key 字段名 [,字段名2,...] references <主表名> 主键列1 [,主键列,...]

例子：

#创建数据库
create database mydb3;
use mydb3;
#创建部门表（主表）
create table if not exists dept(
	deptno varchar(20) primary key, -- 部门号
    name varchar(20) -- 部门名字
);
#创建员工表（从表）
create table if not exists emp(
	eid varchar(20) primary key, -- 员工编号
    ename varchar(20), -- 员工名字
    age int, -- 员工年龄
    dept_id varchar(20), -- 员工所属部门
    -- 外键约束
    constraint emp_fk foreign key (dept_id) references dept (deptno) -- emp_fk自己取名的外键名
);

方法二：在创建表时设置外键约束

格式：

alter table <数据表名> add constaint <外键名> foreign key(<列名>) references <主表名> (<列名>);

例子：

#创建部门表（主表）
create table if not exists dept(
	deptno varchar(20) primary key, -- 部门号
    name varchar(20) -- 部门名字
);
#创建员工表（从表）
create table if not exists emp(
	eid varchar(20) primary key, -- 员工编号
    ename varchar(20), -- 员工名字
    age int, -- 员工年龄
    dept_id varchar(20), -- 员工所属部门
);
-- 外键约束
alter table emp add constraint dept_fk foreign key(dept_id) references dept(deptno);
6.2.3在外键约束下的数据操作

  1. 数据插入

    #1.添加主表数据
insert into dept values('1001','销售部');
insert into dept values('1002','开发部');
......
#2.添加从表数据（外键列的值必须依赖主表的主键列）
insert into emp values('7','张三',20,'1002 ');
......

  2. 数据删除

    【注意】：

    1. 主表的数据被从表依赖时，不能删除，否则可以删除
    2. 从表的数据可以随便删除
    delete from dept where deptno = '1001'; -- 可以删除
delete from dept where deptno = '1002'; -- 不可以删除
delete from emp where eid = '7'; -- 可以删除
delete from emp; -- 可以删除
6.2.4删除外键约束

外键一旦删除，就会解除主表和从表间的关联关系

格式：

alter table <表名> drop foreign key <外键约束名>;

例子：

alter table emp drop foreign key dept_id_fk;
6.2.5外键约束的多对多关系
#3.创建中间表student_course/score（从表）
create table score(
	sid int,
    cid int,
    score double
);
#4.建立外键约束（2次）
alter table score add foreign key (sid) references student(sid); 
alter table score add foreign key (cid) references course(cid);
#5.给学生表添加数据
insert into student values(1,'张三',18,'女')，(2,'李四',20,'男')，(3,'王五',19,'男');
#6.给课程表添加数据
insert into course values(1,'语文'),(2,'数学'),(3,'英语');
#7.给中间表添加数据
insert into score values(1,1,78),(1,2,75),(2,1,88),(2,3,90),(3,2,80),(3,3,65);

【注意】：修改和删除时，中间从表可以随便删除和修改，但是两边的主表受从表依赖的数据不能删除或修改

6.3多表联合查询

  • 数据准备

    【注意】：外键约束对多表查询并无影响

6.3.1交叉连接查询

特点：

  1. 笛卡尔积：一张表的每一行和另外一张表的任意一行进行匹配
  2. 假设A表有m行数据，B表有n行数据，则返回m*n行数据
  3. 会产生很多冗余的数据，后期的其他查询可以在该集合的基础上进行条件筛选

格式：

select * from 表1,表2,表3...;

例子：

-- 交叉连接查询
select * from dept,emp;
6.3.2内连接查询

特点：

  1. 求多张表的交集

格式：

#隐式内连接
select * from A,B where 条件;
#显式内连接（inner可省略）
select * from A inner join B on 条件;

例子：

#查询每个部门的所属员工
-- 隐式
select * from dept,emp where dept.deptno = emp.dept_id;
-- 显式
select * from dept inner join emp on dept.deptno = emp.dept_id;
6.3.3外连接查询

【outer可省略】

  • 左外连接：left outer join

    select * from A 
    left outer join B on 条件1
    left outer join C on 条件2;

    把A表中数据全部输出，B表中有的数据输出，没有的补null

  • 右外连接：right outer join

    select * from A 
	right outer join B on 条件1
	right outer join C on 条件2;

    把B表中数据全部输出，A表中有的数据输出，没有的补null

  • 满外连接：full outer join【union】

    select * from A full outer join B on 条件

    #使用union（将两个查询结果上下拼接，并且查重）
select * from A 
	left outer join B on 条件1
union
    right outer join B on 条件1;
    
#使用union all（将两个查询结果上下拼接）
select * from A 
	left outer join B on 条件1
union
    right outer join B on 条件1;

    把A、B中数据全部输出

#查询哪些部门有员工，哪些部门没有员工
use mydb3;
select * from dept a left outer join emp b on a.deptno = b.dept_id;
#查询哪些员工有对应的部门，哪些没有
select * from dept a right outer join emp b on a.deptno = b.dept_id;
#使用union关键字实现满外连接
select * from dept 
	left outer join emp on 条件1
union
    right outer join emp on 条件1;
6.3.4子查询

子查询就是select嵌套的查询

6.3.4.1基础子查询

子查询返回的数据类型有四种

  1. 单行单列：返回的是一个具体列的内容，可理解为一个单值数据
  2. 单行多列：返回一行数据中多个列的内容
  3. 多行单列：返回多行记录之中同一列的内容，相当于给出了一个操作范围
  4. 多行多列：查询返回的结果是一张临时表
#查询年龄最大的员工信息，显示信息包括员工号、员工名字、员工年龄
-- 1.查询最大年龄
select max(age) from emp;
-- 2.让每一个员工的年龄和最大年龄进行比较，相等则满足
select * from emp where age = (select max(age) from emp);
#查询研发部和销售部的员工信息，包含员工号、员工名字
-- 方式1-关联查询
select * from dept a join emp b on a.deptno = b.dept_id and (name = '研发部' or name = '销售部');
-- 方式2-子查询
-- 2.1先查询研发部和销售部的部门号->1001 1002
select deptno from dept where name = '研发部' or name = '销售部';
-- 2.2查询哪个员工的部门号是1001或1001
select * from emp where dept_id in (select deptno from dept where name = '研发部' or name = '销售部');
6.4.3.2子查询关键字

  1. ALL关键字

    格式：

    select...from...where c > all(查询语句)
-- 等价于 -->
select...from...where c > result1 and c >result2 and c >result3

    特点：

    • ALL与子查询返回的所有值比较为true，则返回true
    • ALL可与=、>、<、>=、<=、<>结合使用

    例子：

    #查询年龄大于‘1003’部门所有年龄的员工信息
select * from dept where age > all(select age from emp where dept_id = '1003');

  2. ANY关键字

    格式：

    select...from...where c > any(查询语句)
-- 等价于 -->
select...from...where c > result1 or c >result2 or c >result3

    特点：

    • ANY与子查询返回的任意值比较为true，则返回true
    • ANY可与=、>、<、>=、<=、<>结合使用
    • ANY与SOME作用相同

  3. SOME关键字【同ANY】

  4. IN关键字

    格式：

    select...from...where c > in(查询语句)
-- 等价于 -->
select...from...where c = result1 or c =result2 or c =result3

    特点：

    • 用于判断某个记录的值，是否在指定的集合中
    • NOT IN 可以将条件反过来

    例子：

    #查询有粉饼和销售部的员工信息，包含员工号、员工名字
select eid,ename,t.name from emp where dept_id in(select deptno from dept where name = '研发部' or name = '销售部');

  5. EXISTS关键字

    格式：

    select ...from ...where exists(查询语句)

    特点：

    • 只要查询语句至少返回一条数据，则EXISTS（）的结果为”true“
    • EXISTS关键字效率比IN高，数据量大的时候推荐使用EXISTS关键字

    例子：

    #查询公司是否有大于60岁的员工，有则输出
select * from emp where exists (select * from emp where age>60);-- 错误 全表输出
select * from emp a where exists (select * from emp where a.age>60);-- 正确
6.3.5表自关联

对表自身进行关联查询

【注意】：自关联时表必须给表起别名

格式：

select 字段列表 from 表1 a, 表1 b where 条件;
或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;

例子：

#创建表，并且建立自关联约束
create table t_sanguo(
	eid int primary key,
    ename varchar(20),
    manager_id int,
    foreign key (manger_id) references t_sanguo(eid)
);
#查询每个三国人物及他的上级信息，如：关羽 刘备
select * from t_sanguo a,t_sanguo b where a.manager_id = b.eid;
#查询所有人物及上级
select a.ename,b.ename from t_sanguo a left join t_sanguo b on a.manager_id = b.eid;
#查询所有人物、上级、上上级
select
	a.ename,b.ename,c.ename
from t_sanguo a
	left join t_sanguo b on a.manager_id = e.eid
	left join t_sanguo c on b.manager_id = c.eid;

七、MySQL函数

  • 聚合函数
  • 数学函数
  • 字符串函数
  • 日期函数
  • 控制流函数
  • 窗口函数

7.1聚合函数

除了之前的count、sum、min、max、avg这些聚合函数

还有 group_concat（）函数：该函数用户实现行的合并

格式：

group_concat{[distinct] 字段名 [order by 排序字段 asc/desc [separator '分隔符']}

【注意】：

  1. 使用distinct可以排除重复值
  2. 如果需要对结果中的值进行排序，可以使用order by子句
  3. separator是一个字符串值，默认为逗号

例子：

#将所有员工的名字合并成一行
select gruop_concat(emp_name) from emp;

#指定分隔符合并
select department,gruop_concat(emp_name separator ';') from emp group by deperment;

7.2数学函数

数学函数描述实例
abs(x)返回x的绝对值select abs（-1）–>返回1
ceil(x)返回>=x的最小整数【向上取整】select ceil（1.5）–>返回2
floor(x)返回<=x的最大整数【向下取整】select floor（1.5） -->返回1
greatest（expr1，expr2，expr3，…）返回列表中的最大值select greatest（3，12，34，8，15） -->返回34
least（expr1，expr2，expr3，…）返回列表中的最小值select least（3，12，34，8，15） -->返回3
max(expression)返回字段expression中的最大值# 返回数据表products中字段price的最大值 select max（price）largest_price from products;
min(expression)返回字段expression中的最小值# 返回数据表products中字段price的最小值 select min（price）largest_price from products;
mod(x,y)返回x除以y以后的余数select mod（5，2）–>返回1
pi( )返回圆周率（3.141593）select pi（）–>返回3.141593
pow（x，y）返回x的y次方select pow（2，3） -->返回8
rand()返回0到1的随机数select rand（）–>返回0.94865 #想要返回0到100之间的随机数 select rand（）*100
round(x)返回离x最近的整数（遵循四舍五入）select round（1.6686）–>返回2
round(x,y)返回指定位数的小数（遵循四舍五入）select round（1.6686，2）–>返回1.67
truncate(x,y)返回数值x保留到小数点后y位的值（不会四舍五入）select truncate（1.6686，2）–>返回1.66

【expr】：可以是数字，也可以是字符串

【expressio】：字段

拿拿林
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
mysql笔记 mysql笔记
04-24
mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记 mysql笔记
MySQL笔记.md
11-23
MySQL笔记
mysql数据的添加，修改，主键，删除
changan
09-26 416
mysql数据的添加，修改，主键，删除。
mysql笔记手写_MySQL自用笔记
weixin_39628380的博客
02-04 333
MySQL##本单元目标一、为什么要学习数据库二、数据库的相关概念DBMS、DB、SQL三、数据库存储数据的特点四、初始MySQLMySQL产品的介绍MySQL产品的安装 ★MySQL服务的启动和停止 ★MySQL服务的登录和退出 ★MySQL的常见命令和语法规范五、DQL语言的学习 ★基础查询 ★条件查询 ★排序查询 ★常见函数...
MySql自用笔记
weixin_45577451的博客
03-17 52
1.SQL语句可以单行多行书写，以;结尾。2.SQL语句可以使用空格缩进增强可读性。3.不区分大小写，关键字建议大写。
MySQL学习笔记（自用）
xinshou_zhu的博客
05-28 589
这篇文章记录的是MySQL进阶相关的一些知识点索引是帮助MySQL高效获取数据的一种数据结构这里主要记录两种索引：B+树索引和hash索引。在MySQL中，支持hash索引的是Memory存储引擎。而InnoDB中具有自适应hash功能，hash索引是InnoDB存储引擎根据B+Tree索引在指定条件下自动构建的。为什么InnoDB存储引擎选择使用B+tree索引结构?A. 相对于二叉树，层级更少，搜索效率高；
【MySQL】自用笔记
m0_51556396的博客
02-28 74
MySQL自用笔记
笔记自用自用
a347635191的博客
03-03 868
目录Javajvm内存模型一.运行时数据区二.类变量、实例变量、局部变量分别存储在什么地方三.类加载四.垃圾回收五.jvm调优Java内存模型一.线程之间如何进行通信二.重排序和内存屏障三.原子性、有序性、可见性四.本地内存和主内存交互的八大操作HashMap一.为什么HashMap底层数组的长度是2的幂次数二.HashMap的hash值是hashCode吗三.HashMap的树化条件四.HashMap的putVal方法线程池一.线程池的优点二.线程池的原理三.线程池的拒绝策略四.线程池的关闭和回收五.预置
Mysql学习笔记（自用）
m0_62053268的博客
10-20 377
Mysql学习笔记（自用）
MySQL复习笔记
weixin_44381223的博客
03-16 316
/UNION 操作符必须由两个或多个 SELECT 语句组成，每个 SELECT 语句的列数和对应位置的数据类型必须相同。// [ ]：表示括号内所列字符中的一个（类似正则表达式）。//MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合，并去除重复的行。//查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。// _：表示任意单个字符。//在分组的列上我们可以使用 COUNT, SUM, AVG,等函数。
MYSQL自用笔记1——主键和外键
Rye_dt的博客
10-25 452
小记一下
MySQL学习笔记.md
12-07
观看b站：《老杜带你学_mysql入门基础》，所做的随堂笔记，杜老师讲的十分通俗易懂，respect！
MySQL笔记MySQL笔记
08-03
MySQL 存储引擎笔记 MySQL 是一个功能强大且广泛应用的关系型数据库管理系统，它的架构可以分为四层：连接层、服务层、引擎层和存储层。其中，引擎层是 MySQL 的核心组件之一，负责数据的存储和提取。 1. 存储引擎...
mysql笔记mysql笔记
11-19
mysql笔记
Windows10 MySQL（8.0.37）安装与配置
最新发布
XLB
06-16 301
新建变量：%MYSQL_HOME%\bin 点击确定，也可以单独在path设置成一条：C:\MySQL\mysql-8.0.37-winx64\bin即可。C:\Mysql\mysql-8.0.37-winx64 （安装路径）工具栏搜索：高级系统设置，或者鼠标右键此电脑>属性>高级系统设置。保存，将my.txt文件后缀改为.ini。编辑my.txt文件，输入以下内容。系统变量找到：Path 点击编辑。解压文件，解压到你想要的位置。(没错就是要点三个确定)新建my.txt文件。点击确定，确定，确定。
深入理解 MySQL 查询分析工具 EXPLAIN 的使用
良月柒
06-14 1101
EXPLAIN语句用于显示 MySQL 如何执行 SELECT 语句。它会生成一个查询执行计划，详细说明每一步的执行过程，包括表的访问顺序、使用的索引、表之间的连接方式等。这些信息对于优化查询性能至关重要。MySQL 的EXPLAIN工具是优化 SQL 查询性能的利器。通过理解和分析查询的执行计划，我们可以识别潜在的性能瓶颈，并采取相应的优化措施。无论是添加索引、调整查询结构，还是优化连接方式，EXPLAIN都提供了关键性的指导信息。
MySQl基础----Linux下搭建mysql软件及登录和基本使用（附实操图超简单一看就会）
溟洵的博客
06-10 803
本章非常基础包括如何在Linux上搭建（==当然上面的SQL语句你在其他能执行SQL语句的软件上也能正常执行，并非一定要在Linux环境下==）和mysql的基本使用
论述MySQL的同异步复制
2301_78925669的博客
06-10 1430
同异步复制
csdn mysql笔记
08-02
在CSND上，MySQL笔记是指关于MySQL数据库的学习和使用的笔记和教程。这些笔记包含了MySQL数据库的基本概念、安装配置、SQL语句的使用、数据表的设计和管理、索引使用、数据备份和恢复等方面的知识。学习MySQL笔记...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值