MySQL基础

最新推荐文章于 2024-04-26 13:29:49 发布
最新推荐文章于 2024-04-26 13:29:49 发布
阅读量3.5k 收藏 6
点赞数 2
分类专栏： 数据库 文章标签： mysql 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_38762212/article/details/124521463
版权

一、库表的创建、删除和修改

1.1库操作

查看所有库

show databases;

指定使用某个数据库

use 数据库名;

创建数据库

create database [if not exists] 库名 [character set 字符集名];

修改库名

alter database 库名 character set 字符集名;

删除库

drop database [if exists] 库名;

1.2表操作

创建表

create table [if not exists] [数据库名.]数据表名 (
    字段名1 数据类型 约束，
    字段名2 数据类型 约束，
    ...
);

修改表名

alter table 旧表名 rename [to] 新表名

删除表

drop table [if exists] 表名;

复制表

# 复制表结构
create table 表名 like 要复制的表;

# 复制表的结构+数据
create table 表名
select 查询列表
from 要复制的表
[where筛选条件]

1.3列操作

添加列

alter table 表名 add column 列名 类型 [first|after 列名];

ps：最后的first或after表示添加列到某一列的之前或之后，如果不加默认会添加到最后

修改列名

alter table 表名 change column 旧列名 新列名 数据类型;

修改列的数据类型或约束

alter table 表名 modify column 列名 新类型 [新约束];

删除列

alter table 表名 drop column 列名;

二、MySQL约束

2.1常见约束

约束名作用作用域
NOT NULL非空约束，该字段的值必须有值列级、表级
UNIQUE唯一约束，该字段的值不可重复（可以为为null，但不能有两个null）列级、表级
DEFAULT默认约束，若该字段的值不手动插入时会有默认的值列级、表级
CHECK检查约束，MySQL不支持表级
PRIMARY KEY主键约束，代表该字段的值不可重复且不能为null列级、表级
FOREIGN KEY外键约束，代表该字段的值引用了另一表中的字段（一般是键，更一般是主键）表级、列级写上没用
AUTO_INCREMENT自增约束，描述的列必须是一个键列且是整型，一张表最多只有一个自增长列，一般是主键列级、表级

2.2列级约束语法

语法：

create table 表名 (
    字段名 类型 列级约束
);

例如：

# 列级约束只支持默认、非空、主键、唯一
CREATE TABLE student(
    id int PRIMARY KEY ,#主键
    stuName varchar(20) NOT NULL ,#非空
    gender char(1) CHECK (gender = '男' OR gender = '女'),# 检查
    seat int UNIQUE# 唯一
);

2.3表级约束语法

语法：

create table 表名 (
    字段名 类型,
    ...
    字段名 类型,
    [constraint 约束名] 约束类型(字段名)
);

例子：

CREATE TABLE student(
    id int,
    stuName varchar(20),
    gender char(1),
    seat int,
    age int,
    majorid int,

    CONSTRAINT pk PRIMARY KEY(id),#主键
    CONSTRAINT uk UNIQUE KEY(seat), #唯一键
    CONSTRAINT ck CHECK (gender='男' OR gender='女'), # 检查约束
    CONSTRAINT fk_stuiunfo_major FOREIGN KEY(majorid) REFERENCES major(id) #外建
);

2.4主键和唯一键的异同

  1. 一个表最多只有一个主键，但是可以有多个唯一键
  2. 主键不允许为空，唯一键可以为空（但是最多一个空，两个空就和唯一冲突了）
  3. 都具有唯一性
  4. 都支持组合键（不推荐使用，不稳定）

2.5 存在外键时设置级联删除

alter table 从表 add constraint 索引名 foreign key(从表字段)
references 主表(主表字段)
on delete cascade;

2.6 存在外键时设置级联置空

alter table 从表 add constraint 索引名 foreign key(从表字段) 
references 主表(主表字段) 
on delete set null;

2.7查看某个表的约束

方式一：

SELECT * 
FROM information_schema.table_constraints 
WHERE table_name = '表名称';

方式二：

SHOW CREATE TABLE 表名;

2.8查看某个表的索引

SHOW INDEX FROM 表名

三、MySQL数据类型

3.1数值型

1.整形
在这里插入图片描述
注意：

  • 整型都可以设置无符号和有符号，默认为有符号，设置无符号需要使用关键字unsigned
  • 如果超出范围会报异常，并且插入临界值（有些工具会直接报错）
  • 长度可以不指定，会有默认的长度
    • 长度代表显示的最大宽度，如果不够则左边用0填充，但需要搭配zerofill关键字，并且默认变为无符号整型

2.实型

浮点数类型字节范围
FLOAT4
DOUBLE8
定点数类型字节范围
DEC(M,D)
DECIMAL(M,D)		M+2

M和D的意思：

  1. M代表整数部位+小数部位的个数，D代表小数部位

  2. 如果超出范围会报异常，并且插入临界值

  3. M和D都可以省略

    • 定点数M默认为10，D默认为0

  4. 如果精度要求较高则优先考虑使用定点数

3.2字符型

类型最多字符数描述
CHAR(M)MM为0-255之间的整数
M可以省略，默认为1
VARCHAR(M)MM为0～65525之间的整数
BINARY用于保存较短定长二进制
VARBINARY用于保存较短可变二进制
SET用于保存集合
ENUM用于保存枚举
TEXT用于保存较长的文本
BLOB用于保存较长的二进制文本

3.3日期型

日期和时间类型字节最小值最大值描述
DATE41000-01-019999-12-31只保存日期
DATETIME81000-01-01 00:00:009999-12-31 23:59:59保存日期+时间
TIMESTAMP4197001010800012038年的某一时刻保存日期+时间
TIME3-839:59:59839:59:59只保存时间
YEAR119012155只保存年

datetime和timestamp的区别：
timestamp会受到时区的影响，表示的时间会更准确一些

四、DML插入语句

4.1添加数据

方式一：

insert into 表名(字段名1,字段名,……,字段名n)
values (值1,值2,……,值n);

方式二：

insert into set 字段1=值,……,字段n=值;

注意：

  1. 插入时，要求值的类型和字段的类型要一直或者兼容
  2. 字段的个数和顺序不一定与原始表中的的顺序和个数一致，但必须保证值和字段一一对应
  3. 加入表中有可以为null的字段，可以通过两种方式插入null值
    • 字段和值都省略
    • 字段写上，值使用null
    • 字段名可以省略，默认所有列

两种添加数据方式的区别：
方式一支持一次插入多行
-例如

insert into 表名[(字段名,……)]
values (值……),
       (值……),
       (值……),
       ………………,
       (值……);

方式一支持子查询

  • 例如
insert into 表名
查询语句;

4.2修改数据

修改单表的数据

update 表名 
set 字段=新值, 字段=新值, ……
[where筛选条件]

修改多表的数据

update 表1 别名
inner | left | right join 表2 别名
on 连接条件
set 字段=新值, 字段=新值, ...
[where筛选条件]

4.3删除数据

delete删除

删除单表记录

delete from 表
[where筛选条件]
[limit条目数]

级联删除

# 要删除哪个表就把哪个表的别名写在delete后面，如果都要删，就都写
delete 别名1, 别名2
from 表1 别名1
inner|left|right join 表2 别名2
on 连接条件
[where筛选条件]
truncate截断
truncate 表名
delete 和 truncate的区别
  1. 自增长列数值的区别
    • truncate删除后，如果再插入新的数据，自增长列的值从1开始
    • delete删除后，如果再插入新的数据，自增长列的值从断点开始
  2. 筛选条件区别
    • delete可以添加where筛选条件
    • truncate不可以添加筛选条件
  3. 返回值区别
    • delete有返回值（受影响的行数）
    • truncate没有返回值
  4. 回滚区别
    • delete可以回滚
    • truncate不可以回滚
  5. 速度区别（truncate速度比delete快）
    • truncate底层是把表drop掉，然后新建了一张空表
    • delete底层是一行一行的删除数据

五、MySQL常见函数

5.1字符函数

length()

描述：获取参数值的字节个数
例：

length('HelloWorld');

运行结果：

10
concat()

描述：拼接字符
例：

concat('Hello', '___', 'World');

运行结果：

Hello___World
upper()

描述：将字符转换为大写
例：

upper('Hello");

运行结果：

HELLO
lower()

描述：将字符转换为小写
例：

lower('HELLO');

运行结果：

hello
subString()/subStr()

描述：截取子字符串
例1：

# 2个参数：截取制定字符串从第n(包含第n个)个开始，一直到结尾的子字符串
subString('abcdefghijklmn', 7);

运行结果：

ghijklmn

例2：

# 3个参数，截取指定字符串从第n个开始，m长度的子字符串
subStr('abcdefghijklmn', 1, 3);

运行结果：

abc
instr

描述：返回字串在字符串中第一次出现的索引位置，如果不存在则返回0
例：

instr('我们都会HelloWorld', 'World');

运行结果：

10
lpad

描述：用指定的字符实现左填充指定长度，如果指定长度小于字符长度，从右边截断
例：

lpad('Hello', 10, '?');

运行结果：

?????Hello
rpad

描述：用指定的字符实现右填充指定长度，如果指定长度小于字符长度，从左边截断
例：

rpad('Hello', 10, '?');

运行结果：

Hello?????
replace

描述：替换字符串指定字符为另一字符
例：

replace('你是电你是光你是唯一的神话', '你', '我');

运行结果：

我是电我是光我是唯一的神话
trim

描述：去掉字符串首尾空格或首尾其他指定字符
例：

trim('    abc.     ');
trim('x' from 'xxxabcxxx');

运行结果：

abc
abc

5.2数学函数

round()

描述：四舍五入

# 四舍五入
select round(-1.55);
# 小数点后保留多少位再四舍五入
select round(1.567, 2);
ceil

描述：向上取整，返回大于等于该参数的最小整数

select ceil(1.19);
select ceil(-1.19);
floor()

描述：向下取整，返回小于等于该参数的最大整数

SELECT FLOOR(9.99);
SELECT FLOOR(-9.99);
truncate()

描述：截断，小数点后保留几位

select truncate(1.65, 1);
mod()

描述：取模/取余

SELECT MOD(10, 3);
SELECT MOD(-10, -3);
SELECT MOD(10, -3);

注意：mod结算的公式为 mod(x, y) = x - x / y * y
简单点就是说，第一个参数是正数结果就是正数，第一个参数是负数结果就是负数
运算时，先按全正数运算，再加负号就可以了

5.3日期函数

now()

描述：返回当前系统日期和时间

select now();
curdate()

描述：返回当前系统时间，不包含时间

select curdate();
curtime()

描述：返回当前系统时间，不包含日期

select curtime();
datediff

描述：计算两个日期之间间隔的天数

select datediff(日期1, 日期2);
获取指定的时间部分
SELECT
    YEAR(NOW()) AS "年",
    MONTH(NOW()) AS "月",
    HOUR(NOW()) AS "时",
    MINUTE(NOW()) AS "分钟",
    SECOND(NOW()) AS "秒";
str_to_date()

描述：将日期格式的字符转换成指定格式的日期

SELECT STR_TO_DATE('1998-1-21', '%Y-%c-%d');
date_format()

描述：将日期转换成字符

SELECT DATE_FORMAT(NOW(), '%y年%m月%d日');
日期格式符
序号格式符功能
1%Y四位的年份
2%y2位的年份
3%m月份(01, 02, … , 12)
4%c月份(1, 2, … , 12)
5%d日(01,02)
6%H小时(24)
7%h小时(12)
8%i分钟(00, 01, … , 59)
9%s秒(00, 01, … , 59)

5.4流程控制函数

5.4.1 分支结构
if函数

双分支

if(表达式1, 表达式2, 表达式3)

执行顺序：如果表达式1成立，则if函数返回表达式2的值，否则返回表达式3的值
应用场景：任何地方

多重分支

if 条件1 then 语句1
elseif 条件2 then 语句2
……
else 语句n
end if;

应用场景：只能在begin end中使用
注意：最后的else也可以省略，如果不需要的话

case函数

类似于switch的结构
作用：一般用于实现等值判断

case 变量 ｜ 表达式 ｜ 字段
when 要判断的值 then 返回的值1
when 要判断的值 then 返回的值2
……
else 要返回的值n
end

类似于多重if 作用：一般用于实现区间判断 ``` case when 要判断的条件1 then 返回的值1 when 要判断的条件2 then 返回的值2 …… else 要返回的值n end ``` 注意： 1. 可以作为表达式，嵌套在其他语句中使用，可以放在任何地方 2. 也可以作为独立的语句去使用，只能放在begin end中，作为独立的语句时，end结束标记要变成end case 3. 如果when中的值满足或条件成立，则执行对应的then后面的语句，并且结束case。如果都不满足，则执行else中的语句或值 4. else可以省略，如果else省略了，并且所有when条件都不成立，则返回null
5.4.2 循环结构
while

作用：先判断后执行
应用场景：begin end中

[标签:]
while 循环条件 do
循环体:
end while
[标签];
loop

作用：没有条件的死循环
应用场景：begin end中

[标签:]
loop
循环体
end loop
[标签];
repeat

作用：先执行后判断
应用场景：begin end中

[标签:]
repeat
循环体
until 结束循环的条件
end repeat
[标签];
控制循环语句

iterate
作用：类似于continue

iterate 标签名;

leave
作用：类似于break

leave 标签名;

六、DQL查询语句

6.1基础查询

# 查询单个/多个字段
SELECT [查询列表]
FROM 表名;
 
# 查询所有字段
SELECT *
FROM 表名;
6.1.1别名

方式一：

SELECT
	字段1 AS “别名1”,
	字段2 AS “别名2”,
	字段3 AS “别名3”
FROM 表名;

方式二：

SELECT
	字段1 “别名1”,
	字段2 “别名2”,
	字段3 “别名3”
FROM 表名;

注：如果别名中有空格，一定要用双引号""包起来，没有空格其实可以不加引号

6.1.2查询去重

关键字：distinct

SELECT DISTINCT 查询列表
FROM 表名;
6.1.3 +号的作用

MySQL中，+号只有一个作用，那就是运算符
例如：SELECT x+y FROM xxx;

  1. 如果x和y两个操作数都为数值型，则做加法运算
  2. 如果x和y两个操作数中，只要其中有一方为字符型，Mysql将视图把字符型数值转换成数值型，如果转换成功则继续做加法运算，如果转失败，则将其中的字符型转换成数值0
  3. 如果x和y两个操作数中，只要其中一方为null，则加法运算的结果一定为null

6.2条件查询

语法：

SELECT 查询列表
FROM 表名
WHERE 筛选条件;
6.2.1筛选条件

筛选条件支持条件表达式、逻辑表达式、模糊查询
条件表达式查询
- >、<、 =、 !=、 <>、 >=、 <=

逻辑表达式查询
- &&、||、 !、 and 、or、 not

模糊查询
like：一般都与通配符配合使用
通配符：

  • %：代表任意多个字符，包含0个字符
  • _：代表任意一个字符

between and
语法：between x and y，意为在x和y之间

  • 使用between and可以提高语句的简洁度
  • 包含临界值，意思就是x到y的闭区间
  • 两个临界值调换顺序的话查询结果会发生变化

in
语法：x in (列表y)，意为x是列表y的其中一个

  • 使用in提高语句简洁度
  • in列表的值类型必须一致或兼容
  • in列表中不支持通配符

is null：用来判断是否是空值，字面意思，为空则真

is not null：用来判断是否不是控制，不为空则真

is null 和 <=>

  • IS NULL 仅仅可以判断null值，可读性较高
  • <=>既可以判断null值，也可以用来判断普通的数值，可读性较差

6.3排序查询

SELECT 查询列表
FROM 表名
[WHERE筛选条件]
ORDER BY 排序列表 ASC | DESC
  1. ASC | DESC
    • ASC：升序排列
    • DESC：降序排列
    • 如果不写，默认是ASC，当然前提得有ORDER BY才行
  2. ORDER BY子句中可以支持 单个字段、多个字段、表达式、函数、别名
  3. ORDER BY子句一般放在查询语句的最后面，除了LIMIT子句，它应该是最后的

6.4分组查询

SELECT 分组函数，列（要求出现在group by的后面）
FROM 表
【WHERE 筛选条件】
GROUP BY 分组的列表
【ORDER By 子句】
【HAVING 筛选条件】

注意：

  • 分组查询中的筛选可以分为两类，分组前筛选和分组后筛选，这两类筛选的数据源是不一样的
    • 筛选前的数据源为已存在表中的原始数据，放在group by前面，关键字为：WHERE
    • 筛选后的数据源为分组后的结果集，放在group by后面，关键字为HAVING
    • 分组函数做条件肯定放在HAVING后面，表中的原始数据做条件肯定放在WHERE后面
  • 能用分组前筛选的，优先考虑使用分组前筛选
  • group by子句支持单个字段分组，也支持多个字段分组（多个字段之间用逗号隔开，没有顺序要求）、表达式和函数
  • 可以添加排序，排序放在整个分组查询最后的位置（也就是LIMIT）

6.5连接查询

连接查询又称为多表查询，当查询的字段来自于多个表的时候，就会用到连接查询

什么是笛卡尔集？

假如表1有m行，表2有n行，查询的结果为m*n行
发生原因：没有有效的连接条件
解决办法：添加有效的连接条件

6.5.1连接查询分类

按年代分类：

sql92：MySQL中只支持内连接
sql99：MySQL中支持所有（内连接+外连接+交叉连接）

按功能分类：

  1. 内连接
    • 等值连接
    • 非等值连接
    • 自连接
  2. 外连接
    • 左外连接
    • 右外连接
    • 全外连接
  3. 交叉连接
6.5.2 SQL92
等值连接
SELECT 【筛选字段】
FROM 
	表1 别名,
	表2 别名
WHERE 连接条件
【group by分组】
【having 分组后筛选】
【order by排序】;

注：

  • 多表连接的结果为多表的交集部分
  • n表连接至少需要（n-1）个连接条件
  • 多表的顺序没有要求
  • 一般需要为表起别名
  • 可以搭配所有查询子句来使用，比如排序、分组、筛选
非等值连接
SELECT 【查询列表】
FROM 
	表1 别名,
	表2 别名
WHERE 连接条件（一般都是xxx between num1 and num2）
【group by分组】
【having 分组后筛选】
【order by排序】;
自连接
SELECT 【查询列表】
FROM
	表1 别名1，
	表1 别名2
WHERE 别名1.xxx = 别名2.xxx
【group by分组】
【having 分组后筛选】
【order by排序】;
6.5.3 SQL99
内连接
  1. 等值连接
SELECT 【查询列表】
FROM 表1 别名1
INNER JOIN 表2 别名2 ON 连接条件
INNER JOIN 表3 别名3 ON 连接条件
...
INNER JOIN 表n 别名n ON 连接条件
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】
  1. 非等值连接
SELECT 查询列表
FROM 表1 别名
INNER JOIN 表2 别名 ON 表1.xxx between 表2.xxx and 表2.xxx
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】
  1. 自连接
SELECT 查询列表
FROM 表1 别名1
INNER JOIN 表1 别名2 ON 连接条件
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】
外连接
SELECT 查询列表
FROM 表1 别名
LEFT|RIGHT OUTER JOIN 表2 别名
ON 表1.xxx = 表2.xxx
【where筛选条件】
【group by分组】
【having筛选条件】
【ordery by排序列表】

应用场景：用于查询一个表中有，另一个表中没有的记录
特点：

  1. 外连接的查询结果为主表中的所有记录
    • 如果从表中有和它匹配的，则显示匹配的值
    • 如果从表中没有和它匹配的，则显示null
    • 外连接查询结果=内连接结果+主表中有而从表中没有的记录
  2. 左外连接，left outer join左边的是主表
  3. 右外连接，right outer join右边的是主表
  4. 全外连接=内连接的结果+表1中有但表2中没有+表2中有但表1中没有的结果
  5. 左外连接和右外连接交换两个表的顺序，可以实现同样的效果
交叉连接
SELECT 查询列表
FROM 表1 别名
CROSS JOIN 表2 别名

???这不是笛卡尔积

6.6子查询

什么是子查询？

嵌套在其他语句内部的select语句称为子查询（内查询）
外面的语句可以是insert、update、delete、select，一般select作为外面的比较多
外面如果为select语句，则称为外查询或主查询

6.6.1子查询分类
  1. 按出现的位置分
    • select后面
      • 仅仅支持标量子查询
    • from后面
      • 表子查询
    • where或having后面
      • 标量子查询
      • 列子查询
      • 行子查询
    • exists后面
      • 标量子查询
      • 列子查询
      • 行子查询
      • 表子查询
  2. 按结果集分
    • 标量子查询（单行子查询）：结果集为一行一列
    • 列子查询（多行子查询）：结果集为多行一列
    • 行子查询：结果集为多行多列
    • 表子查询：结果集任意（是结果集就行）
6.6.2select
标量子查询
# 案例：查询每个部门的员工个数
# 第一种方法
SELECT
    d.*,
    count(employee_id)
FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_id;
# 第二种方法
SELECT
    d.*,
    (
        SELECT count(*)
        FROM employees e
        WHERE e.department_id = d.department_id
    )
FROM departments d;
6.6.3 from后面
表子查询
# 案例：查询每个部门的平均工资的工资等级
SELECT
    ag_dep.*,
    j.grade_level
FROM
    (
        SELECT
            d.department_id,
            d.department_name,
            avg(salary) avg_salary
        FROM employees e
        INNER JOIN departments d ON e.department_id = d.department_id
        GROUP BY d.department_id
    ) ag_dep
INNER JOIN job_grades j ON ag_dep.avg_salary BETWEEN j.lowest_sal AND highest_sal;
6.6.4 where或having后面
标量子查询
# 1.标量子查询
# 案例：谁的工资比Abel高？
USE myemployees;
# 第一步：查询Abel的工资
SELECT salary
FROM employees
WHERE last_name = 'Abel';
# 第二步：查询员工信息，满足salary>第一步的结果
SELECT *
FROM employees
WHERE salary > (
    SELECT salary
    FROM employees
    WHERE last_name = 'Abel'
    );

# 案例：返回job_id与141号员工相同，salary比143号员工多的员工、姓名、job_id和工资
# 第一步：先查出143员工的salary
SELECT salary
FROM employees
WHERE employee_id = 143;
# 第二步：查出141号员工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141;
# 第三步：查询员工信息，满足salary>第一步的结果并且job_id = 第二步的结果
SELECT
    last_name,
    job_id,
    salary
FROM employees
WHERE
    salary > (
        SELECT salary
        FROM employees
        WHERE employee_id = 143
        )
    AND
    job_id = (
        SELECT job_id
        FROM employees
        WHERE employee_id = 141
        );

# 案例：返回公司工资最少的员工的last_name,job_id,salary
SELECT
    last_name,
    job_id,
    salary
FROM employees
WHERE salary = (
    SELECT MIN(salary)
    FROM employees
    );

# 案例：查询最低工资大于50号部门最低工资的部id和其最低工资
SELECT
    department_id,
    MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT MIN(salary)
    FROM employees
    WHERE department_id = 50
    );

# 非法使用标量子查询
SELECT
    department_id,
    MIN(salary)
FROM employees
WHERE department_id IS NOT NULL
GROUP BY department_id
HAVING MIN(salary) > (
    SELECT salary
    FROM employees
    WHERE department_id = 50
    );
列子查询
# 2.列子查询(多行子查询)
# 案例：返回location_id是1400或1700的部门中的所有员工姓名
SELECT last_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id IN (1400, 1700)
    )
ORDER BY last_name;
# 连接查询也可以做
SELECT last_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE d.location_id IN (1400, 1700)
ORDER BY last_name;

# 案例：返回其他部门中比job_id为'IT_PROG'部门任一工资低的员工的员工号、姓名、job_id、salary
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id != 'IT_PROG'
    AND
    salary < ANY(
        SELECT DISTINCT salary
        FROM employees
        WHERE job_id = 'IT_PROG'
        );
# 第二种方式：
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id != 'IT_PROG'
    AND
    salary < (
        SELECT DISTINCT MAX(salary)
        FROM employees
        WHERE job_id = 'IT_PROG'
        );

# 案例：返回其他部门中比job_id为'IT_PROG'部门所有工资都低的员工的员工号、姓名、job_id和salary
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id <> 'IT_PROG'
    AND
    salary < ALL (
        SELECT DISTINCT salary
        FROM employees
        WHERE job_id = 'IT_PROG'
    );
# 第二种方式
SELECT
    employee_id,
    last_name,
    job_id,
    salary
FROM employees
WHERE
    job_id <> 'IT_PROG'
    AND
    salary < (
        SELECT DISTINCT MIN(salary)
        FROM employees
        WHERE job_id = 'IT_PROG'
    );
行子查询
# 3.行子查询（结果集一行多列/多行多列）
# 案例：查询最小的员工编号并且工资最高的员工信息
SELECT *
FROM employees
WHERE (employee_id,salary) = (
    SELECT
        MIN(employee_id),
        MAX(salary)
    FROM employees
    );
6.6.5 exists后面
/*
exists：检查是否存在集合中，1为true，0为falase
语法：
exists(完整的查询语句)
结果：1或0
*/
SELECT exists(
    SELECT *
    FROM employees
    WHERE salary = 3000000
           );

# 案例：查询有员工部门名
SELECT department_name
FROM departments d
WHERE exists(
    SELECT *
    FROM employees e
    WHERE e.department_id = d.department_id
          );

# 案例：查询没有女朋友的男生信息
SELECT * FROM beauty;
SELECT b.*
FROM boys b
WHERE b.id NOT IN (
    SELECT boyfriend_id
    FROM beauty
    );

SELECT b.*
FROM boys b
WHERE NOT exists(
    SELECT boyfriend_id
    FROM beauty g
    WHERE g.boyfriend_id = b.id
    );

6.7分页查询

select 查询列表
from 表
limit [offset], size;

offset 代表 起始的条目索引，默认从0开始
size 代表 要显示的条目数

公式：

# 假如要显示的页数为page，每一页条目数为size
select 查询列表
from 表
limit (page-1)*size, size

6.8连接查询

union联合查询：将多条查询语句的结果合并成一个结果
应用场景：要查询的结果来自于多个表，且多个表之间没有直接的连接关系，但查询的的信息一致时，可以使用联合查询

# 去重的联合查询
查询语句1
union
查询语句2
union
……
union
查询语句n

# 不去重的联合查询
查询语句1
union all
查询语句2
union all
……
union all
查询语句n

注：

  1. 要求多条语句的查询列是一样的（都是n列）
  2. 要求多条查询语句查询的的每一列的类型和顺序是一致的（名字对名字，编号对编号等等，否则查询出的数据无意义）
  3. 使用union关键字默认是去重的，如果想要包含重复项，可以使用union all

七、事务

7.1 概述

事务的含义：一条或多条SQL语句组成执行单元，这一组SQL要么都执行，要么都不执行

事务的特点：

  1. 原子性：一个事务是不可再分割的整体，要么都执行，要么都不执行
  2. 一致性：一个事务可以使数据从一个一致状态切换到另一个一致状态
  3. 隔离性：一个事务不受其他事务的干扰和影响，多个事务是相互隔离的
  4. 持久性：一个事务一旦提交，则永久的持久化到本地

7.2 事务的使用步骤

  1. 开启事务
set autocommit = 0;
start transaction; # 可以不写
  1. 编写一组逻辑SQL语句
  2. 结束事务

如何设置回滚点：

在逻辑SQL语句中设置回滚点，savepoint 回滚点名
然后 rollback 回滚点名 就可以回滚到指定回滚点

7.3 事务的并发问题

  1. 事务的并发是如何发生的？

多个事务同时操作同一个数据库的同一个数据的时候，就会发生并发

  1. 并发问题有哪些？
    • 脏读：一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“更新的数据”
    • 不可重复读：一个事务多次读取同一数据，结果不一样
    • 幻读：一个事务读取了其他事务还没有提交的数据，只是读到的是其他事务“插入的数据”
  2. 如何解决并发问题？

通过设置隔离级别来解决这些并发问题

  1. 隔离级别有哪些？
    • read uncommitted：读未提交
    • read committed：读已提交
    • repeatable：可重复读
    • serializable：串行化

各个隔离级别能解决的问题：

隔离级别脏读不可重复读幻读
read uncommitted：读未提交不能解决不能解决不能解决
read committed：读已提交可以解决不能解决不能解决
repeatable：可重复读可以解决可以解决可以解决
serializable：串行化可以解决可以解决可以解决

7.4 设置隔离级别

每启动一个 mysql 程序, 就会获得一个单独的数据库连接。每个数据库连接都有一个全局变量 @@tx_isolation, 表示当前的事务隔离级别
Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。
查看当前的隔离级别：SELECT @@tx_isolation;
查看全局的隔离级别：SELECT @@global.tx_isolation;

设置当前MySQL连接的隔离级别，语句如下：

set tx_isolation ='隔离级别';

设置数据库系统的全局隔离级别，语句如下：

set global tx_isolation ='隔离级别';

八、视图

视图是MySQL5.1出现的新特性，本身是一个虚拟表，它的数据来自于表，通过查看时动态生成
视图的好处：

  1. 简化SQL语句
  2. 提高了SQL复用性
  3. 保护了基表的数据，提高了安全性

8.1 创建视图

# 方式一：
create or replace 视图名
as
查询语句;

# 方式二：
alter view 视图名
as
查询语句;

8.2 删除视图

drop view 视图1,视图2, ... ;

8.3 查看视图

# 方式一：
desc 视图名;

# 方式二：
show create view 视图名;

8.4 视图的使用

视图支持insert、update、delete、select
但是视图一般是用来查询的，而不是用来更新的，所以具备以下特点的视图都不允许被更新

  • 包含分组函数、group by、distinct、having、union
  • 包含连接查询
  • 包含常量视图
  • where后的子查询用到了from中的表
  • 用到了不可更新的视图

8.5 视图和表的对比

关键字是否占用物理空间使用范围
视图view占用较小，只保存sql逻辑一般用于查询
表table占用较大，需要保存实际的数据增删改查

九、变量

系统变量是由系统提供的，不需要自定义

9.1 变量的分类

  • 全局变量

服务器层面上的，必须拥有super权限才能为系统变量赋值，作用于整个服务器，也就是针对所有的连接/会话

  • 局部变量

服务器为每一个连接的客户端都提供了系统变量，作用于当前的连接/会话

9.1.1 查看系统变量
# 如果没有显式的声明global或session，则默认是session
show [global|session] variables;
9.1.2 查看指定系统变量的值
select @@[blobal|session].变量名;
9.1.3 为系统变量赋值
# 如果没有显式的声明global或session，则默认是session
# 方式一
set [global|session] 变量名=值
# 方式二
set @@global.变量名=值
set @@变量名=值

9.2 自定义变量

9.2.1 用户变量

作用域：针对于当前会话/连接 生效
位置：可以在begin and中，也可以在begin and外

声明和赋值
# 方式一：
set @变量名=值;`或者`set @变量名:=值;

# 方式二：
或者`select @变量名:=值;
更新值
set @变量名=值;
set @变量名:=值;
select @变量名:=值;
select 值 into @变量名 from 表;
使用变量
select @变量名;
9.2.2 局部变量

作用域：仅仅在定义它的begin and中有效
位置：只能放在begin end中，而且只能放在第一句

声明和赋值
	declare 变量名 类型 [default 默认值]
更新值
set 变量名=值;
set 变量名:=值;
select 变量名:=值;
select 值 into 变量名 from 表;
使用变量
select 变量名;

十、存储过程和函数

二者都类似于Java中的方法，将一组完成特定功能的逻辑语句包装起来，对外暴露名称
好处：简单、安全、性能高

10.1 存储过程

10.1.1 创建存储过程
create procedure 存储过程名(参数模式 参数名 参数类型)
begin
    存储过程体
end

参数模式：

  • in：输入
  • out：输出
  • inout：即做输入也做输出

注：

  • 存储过程体的每一条sql语句都需要用分号结尾
  • 如果存储过程体仅仅只有一句话，begin end可以省略
  • 存储过程体中的每条sql语句的结尾要求必须加分号
  • 存储过程的结尾可以使用delimiter重新设置
    • 语法：delimiter 结束标记
10.1.2 调用存储过程
call 存储过程体(实参列表)
10.1.3 删除存储过程
# 语法：
drop procedure 存储过程名
# 例如
DROP PROCEDURE p1;
DROP PROCEDURE p2,p3;
10.1.4 查看存储过程的信息
DESC 存储过程名;
SHOW CREATE PROCEDURE  存储过程名;

10.2 函数

函数是一组先编译好的SQL语句的集合，可以理解成批处理语句
好处：

  • 提高代码的重用性
  • 简化操作
  • 减少了编译次数并且减少了和数据库服务器的连接次数，提高了效率
10.2.1 创建函数
CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
	函数体
 [RETURN 返回值]
END

注意：

  1. 参数列表包含两部分：参数名和参数类型
  2. 函数体：一定要有return语句，如果没有会报错；如果return语句没有放在函数体的最后也不报错，但不建议
  3. 如果函数体中仅有一条语句，则可以省略begin end
  4. 使用delimiter语句可以设置结束标记
  5. 如果有返回值千千万万记得要return
10.2.2 调用函数
SELECT 函数名(参数列表)
10.2.3 查看函数
SHOW CREATE FUNCTION 函数名;
10.2.4 删除函数
DROP FUNCTION 函数名;
仲景
关注
  • 2
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
专栏目录
MySQL基础.pdf
01-14
读完本文档： 能够理解数据库的概念 能够安装MySQL数据库 能够启动,关闭及登录MySQL 能够使用SQL语句操作数据库 能够使用SQL语句操作表结构 能够使用SQL语句进行数据的添加修改和删除的操作 能够使用SQL语句简单查询数据
create table select from
weixin_30895603的博客
09-14 751
CREATE TABLE new_table AS SELECT * FROM old_table 转载于:https://www.cnblogs.com/kakaisgood/p/9644914.html
Oracle-Oracle基础（一）
bk_huangzongbo的博客
11-30 525
Oracle-Oracle基础（一）
如何在MySQL中创建表
m0_65012566的博客
03-11 410
创建表可能是数据库开发中最流行的任务。在本文中，我们将回顾创建MySQL表的方法。当您需要通过脚本专门创建表时，第一种方法很有用。例如，您想在非常特定的时间创建一个表，但由于此时不在，您无法手动创建。因此，您可以使用Windows Scheduler调度创建 表过程–这是表创建查询的有益之处。第二种方法非常适合现场创建表格。在许多情况下，它也可以更加方便，因为它非常直观。有许多工具提供了这种功能，我们将看一看其中的一个——dbForge Studio for MySQL。让我们详细了解这两种方法。
mysql基础
06-03
mysql基础
create table as select from和 insert into select from的用法
热门推荐
程序员的笔记
06-13 4万+
复制表(含数据)： create table table_name2 as select * from table_name1 复制表(不含数据)： create table table_name2 as select * from table_name1where 1=2 只复制表数据：insert into table_name2 select * from table_name1 ...
create table select from 和 insert into table select from区别
★【World Of Moshow 郑锴】★
10-06 3万+
create table select from 和 insert into table select from都是用来复制表，两者的主要区别为： create table select from 要求目标表不存在，因为在插入时会自动创建。 insert into table select from 要求目标表存在，相当于复制插入。 备份表数据： create table tmp_arti...
CREATE TABLE 表名 AS SELECT 语句用法详解
xuehongyou的博客
09-23 3万+
这里写自定义目录标题新表不存在,创建新表新表已经存在 新表不存在,创建新表 1.创建新表并且携带数据 create table new_table as select * from exist_table 2.创建新表不携带数据 create table new_table as select * from exist_table where 1=2 注意:复制只会复制表的结构和数据,原始表中的索引,主键等都不会复制。 新表已经存在 1.全量复制旧表
mysql select table_MySQL中的CREATE TABLE……SELECT语句
weixin_39895862的博客
01-19 7499
在MySQL中，可使用CREATE TALBE. . .SELECT语句将查询结果转存到一个新表中。创建新表create table tabname(col1 type1 [not null] [primary key],col2type2 [not null],..)根据已有的表创建新表create table tab_new like tab_old(使用旧表创建新表)create table...
MySQL建表(create table)命令详解
weixin_46089363的博客
05-08 2万+
MySQL建表(create table)命令详解 create table命令 强调：使用建表命令之前必须使用use命令选择表所在的数据库。create table命令的格式如下： create table tablename( col_name1 type auto_increment, col_name2 type not null|null default val, col_name3 type unique, … , primary key(col_name), index idx_name(
Mysql 基础操作知识专题
Benjamin CSDN博客
06-03 1960
一、Mysql基础操作 1.1 mysql 表复制 1.2 mysql索引 1.3 mysql视图
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf
MySQL基础.docx
03-16
mysql基础部分内容，适用于小白学习。主要包括mysql基本概念、单行函数、常见函数、连接查询、分组查询、联合查询、分页查询、子查询、事务、视图、存储过程、函数、流程控制、
mysql基础知识
01-28
这个是我自己总结的mysql的基本知识，里面从数据库的增删到表的增删，sql查询，事务等知识，简单通俗易懂，值得下载
Mysql_数据库事务
最新发布
weixin_69252724的博客
04-26 1106
事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于化学当中的原子（一种不可分割的最小单位）。往通俗的讲就是，事务就是一个整体，里面的内容要么都执行成功，要么都不成功。不可能存在部分执行成功而部分执行不成功的情况。就是说如果单元中某条sql语句一旦执行失败或者产生错误，那么整个单元将会回滚(返回最初状态)。
一文整理完MySQL关系型数据库相关知识
打代码是一天，不打代码也是一天
04-23 1108
在开发中，数据库是专门用来存取数据的软件。数据库的职责就是管理数据的。根据存取数据的类型分为关系型数据库和非关系型数据库数据存储后呈现出来的效果类似上图的，是关系型数据库数据存储后呈现出来的效果类似上图的，是非关系型数据库。
MySQL 全表扫描成本计算
ZL4120505的博客
04-20 917
private:......IO 成本：从磁盘或内存读取数据页的成本。CPU 成本：访问记录需要消耗的 CPU 成本。导入成本：这一项直到 MySQL 8.0.29 都还没有被使用，先忽略。内存成本：这一项指的是占用内存字节数，计算 MRR（Multi Range Read）方式读取数据的成本时才会用到，也先忽略。全表扫描的成本就只剩 IO 成本、CPU 成本这两项了。全表扫描成本 =io_cost+ 1.1 +cpu_cost+ 1。
Python 将Influxdb时序数据写入mysql库时遇到的问题
zhuzhuxiazst的专栏
04-24 773
influxdb数据库查询结果集合，返回到dataframe类
【MySQL】数据库操作指南：数据类型篇
青竹雾色间.的博客
04-24 781
✨人生如寄，多忧何为 ✨。
mysql 基础教程 pdf
01-15
MySQL基础教程PDF是一本介绍MySQL数据库基础知识的电子书。MySQL是一个开源的关系型数据库管理系统，被广泛应用于Web应用程序和企业级软件开发中。这本教程通过详细讲解MySQL的基本概念、语法和常用操作，帮助读者快速入门并掌握MySQL数据库的使用。 首先，教程会介绍MySQL的安装和配置过程，包括如何下载和安装MySQL服务器，以及如何进行数据库的初始化和设置。接着，教程会详细介绍MySQL中的SQL语言，包括数据的插入、查询、更新和删除等操作。读者可以学习到如何创建和管理数据库、表和索引，以及如何使用SQL语句进行数据的增删改查。 除了基本的SQL操作，教程还会涉及到MySQL的高级特性和技巧。例如，如何使用事务和锁定进行数据的并发控制，以及如何优化数据库的性能和查询速度。此外，教程还会介绍一些常见的MySQL工具，如命令行工具和图形化界面工具，以及如何使用这些工具进行数据库的管理和维护。 总而言之，MySQL基础教程PDF是一本全面而系统的学习MySQL数据库的指南。无论是初学者还是已有一定MySQL经验的开发人员，都可以通过这本教程来进一步提升自己的MySQL数据库技能。同时，这本教程还提供了大量的实例和练习题，帮助读者巩固所学的知识并拓展应用领域。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值