MySQL基础

最新推荐文章于 2024-09-19 07:00:00 发布
最新推荐文章于 2024-09-19 07:00:00 发布
阅读量4.4k 收藏 8
点赞数 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 函数名;
仲景
关注
专栏目录
Oracle-Oracle基础（一）
bk_huangzongbo的博客
11-30 581
Oracle-Oracle基础（一）
mysql 建表语句示例_MySQL Create Table语句和示例
热门推荐
culuo4781的博客
07-26 1万+
mysql 建表语句示例 In this article, I am going to explain the MySQL CREATE TABLE statement with examples. The following syntax contains basic statements to create a table in MySQL. 在本文中，我将通过示例解释MySQL C...
MySQL数据表操作-创建数据表(CREATE TABLE)
Johnny's Lab
01-27 3373
语法 CREATE TABLE 命令语法比较多，其主要是由表创建定义（create-definition）、表选项（table-options）和分区选项（partition-options）所组成的 CREATE [临时表] TABLE <表名> ([表定义选项])[表选项][分区选项]; CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name (create_definition,...) [table_options]
MySQL示例：创建数据库与表
最新发布
weixin_43298211的博客
09-19 484
需要登录到MySQL服务器。一旦数据库创建完成，你可以选择该数据库并开始创建表。子句确保如果这个数据库已经存在，那么命令不会报错而是简单地返回。是你想要创建的数据库的名字。
sqlserver不能直接create table as select ......
一点一竖的专栏
03-30 1892
在sqlserver 下想复制一张表的，想到oracle下直接create table xxx as select * from ....即可。 但是结果却是错误的，baidu一下发现。 sqlserver的语法是 : select * into tablenew from tableold 转自：http://blog.itpub.net/24237320/viewspace-10
create table select和 insert into table select
WangIcter的专栏
04-07 386
要求目标表不存在，因为在插入时会自动创建。
MySQL基础PDF版
05-16
2. **MySQL基础** - **DBMS**：数据库管理系统（DataBase Management System），如MySQL，用于科学地组织和存储数据，高效地获取和维护数据。 - **SQL语句分类**：主要包括DDL（数据定义语言）、DML（数据操作语言...
康师傅mysql基础pdf
01-28
康师傅mysql基础pdf可能是一份教程或教材，涵盖了MySQL的基础知识，包括安装、配置、基本操作以及一些核心概念。在这里，我们将深入探讨MySQL的一些关键点。 1. **安装与配置**: MySQL的安装通常涉及下载安装包，...
超详细的MySQL基础入门教程 从0讲到数据库操作 共22页.pdf
08-21
2 连接 MYSQL 1 2.1 命令行 1 2.2 Navicate 2 2.3 例 1：连接到本机上的 MYSQL 2 2.4 例 2：连接到远程主机上的 MYSQL 3 2.5 退出 MYSQL 命令： exit （回车） 4 3 修改密码 4 3.1 例 1：给 root 加个密码 ab12 。 4...
mysql mysql基础介绍
09-24
MySQL 基础介绍 MySQL 是一种流行的开源关系型数据库管理系统（RDBMS），它使用 SQL（Structured Query Language）作为其操作语言。MySQL 具有高性能、可靠性和易于使用的特点，支持大量的并发用户连接，可以处理...
MySQL创建数据表的三种方式
qq_39075021的博客
01-09 6072
目录 1. 常规 create table 方式 2. create table2 like table1 方式 3. 根据查询 table1 的结果集来创建表 table2 方式 1. 常规 create table 方式 CREATE TABLE [if not exists] table_name ( column_name1 data_type(size), column_name...
数据库常用操作一
LunaQMY的博客
03-18 201
登录mysql mysql -u root -p "密码" 查看database show databases; 创建新的database create database "数据库名"; 删除某个database drop database "数据库名"; 进入当前database use "使用的数据库名"; 在database中导入备份文件 source .sql文件路径 查看ta...
create table 使用select查询语句创建表的方法分享
weixin_30699463的博客
06-20 631
转自:http://www.maomao365.com/?p=6642 摘要:下文讲述使用select查询语句建立新的数据表的方法分享 ---1 mysql create table `新数据表名` select * from `旧数据表名`; -------------------------------- ---2 oracle creat...
MySQL 复制表
我弟是个程序员
04-28 4258
复制表结构 只复制表结构到新表： -- 语法： create table 新表 select * from 旧表 where 1=2 CREATE TABLE bb SELECT * FROM article WHERE 1=2; 或者 -- 语法： create table 新表 like 旧表 CREATE TABLE aa LIKE article; 复制表结构和数...
MySQL创建表
weixin_46020795的博客
09-04 395
创建表的三种方式 直接创建 create table if not exists 表名 将table1的部分拿来创建table2 create table if not exists table2 ( ...... ) select 列名，列名 from table1 -- CREATE TABLE 新表SELECT * FROM 旧表 -- 完整拷贝过来， 区别： 　　通过 as 创建出来的新表缺少源表的索引信息，只有表结构相同，没有索引。 　　通过 like 创建出来的新表包含源
mysql 中create table
expect521的博客
07-06 2510
需求：创建一张表，设置默认值，创建表时注释每个字段的含义 示例： 1. 创建表 create table t_test( id int NOT NULL COMMENT 'ID', username varchar(25) DEFAULT NULL COMMENT '姓名', password varchar(25) DEFAULT NULL COMMENT '密码', sex varcha...
MySQL - 创建表的三种方法详解及练习
weixin_40968325的博客
12-15 7231
CHARSET为utf8，ENGINE为INNODB。先往实例2创建的actor表中插入几条数据。创建一个actor表，包含如下列信息。创建一个actor表，包含如下列信息。
Mysql基础
namu_L的博客
06-20 2501
DML （数据库操作语言）DDL (数据库定义语言)DQL （数据库查询语言）DCL （数据库控制语言）
评论 2
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值