MySQL -常用SQL查询语句（创建，更新，查询，删除等）

最新推荐文章于 2023-09-01 17:06:27 发布

九幽神君

最新推荐文章于 2023-09-01 17:06:27 发布

阅读量691

点赞数

分类专栏： MySQL 文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/qq_34161388/article/details/126281586

版权

MySQL 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

**SQL**:与数据库通讯的语言

**MySQL**: DBMS, 用来操作数据库的软件(基于客户机-- 服务器的数据库)

## 简单语句

> 数据库.表名表名.字段名
>
> AND 优先级大于 OR
>
> select 'hello' regexp '0-9' 可以测试正则
>
> select now();

## 计算字段

## 算数计算

| 效果 | 语句 |
| :--------: | :----------------------: |
| 多字段运算 | select **a*b as c** ... |

## 函数(移植性不高, 性能极好)

### 格式函数

### 日期函数

![1547551641758](/home/gavin/.config/Typora/typora-user-images/1547551641758.png)

> 日期格式: yyyy - mm - dd
>
> 如果要的是日期, 使用date()是个好习惯

### 数值处理函数

![1547552859548](/home/gavin/.config/Typora/typora-user-images/1547552859548.png)

### 聚集函数

| 函数 | 说明 |
| :-----------: | :------------------------------------------: |
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| | count(*) **所有行**/ count(字段)**有值**的行 |
| MAX() / MIN() | 返回极值 |
| | 非数值数据,max返回最后一行 |
| SUM() | 返回某列值之和 |

| 高级用法 | 说明 |
| :----------------------------------------------: | :----------------------: |
| select AVG(DISTINCT 字段) as avg_price from... | 查询字段中不同值的平均值 |

## 分组

### group by

> select 字段1, count(*) as 别名 from 表名 group by 字段;
>
> 将数据排序后
>
> + 在group by后使用 **with rollup**可以得到每个汇总的详细信息(可以看到空值)

### having

select 字段 from 表格 group by 字段 having 条件

> having和where的不同
>
> + Where 在数据分组前过滤
> + having 在数据分组后过滤

> where .... group by ... having ...

#### order by 和 group by的差别

![1547557160973](/home/gavin/.config/Typora/typora-user-images/1547557160973.png)

## select 子句顺序

select ... from .. where .. group by .. having .. order by .. limit

## 子查询

### 相关子查询:

> 涉及**外部查询**的子查询
>
> select...from ... where (select ....)
>
> select 放在前面也可以, 看需求
>
> 任何时候列名可能有多义必须使用表名.列名

### 联接

**记得要有where**

```
select 字段1,表二.字段2 from 表一,表二 where 表一.字段1 = 表二.字段2 order by 字段3
```

#### 笛卡儿积(叉联结)

全部配对, 检索出的行的数目是两种表的行数相乘

### 内部联结

**等值联结/内部联结**

> 基于**两张表**的相同测试
>
> on替换where即可, 效果一样(有时性能高于where)

*支持多表联结*, 不过表太多性能不好

```mysql
select 字段1, 字段2 from 表1, 表2 ,表3 where 表1.字段1=表2.字段2 and 表2.字段3 = 表3.字段4
```

```mysql
select 表1.字段1,表2.字段2 from 表1 INNER JOIN 表2 on 表1.字段1 = 表2.字段3
```

### 高级联结(3种)

#### 表别名

> + 表别名只在查询执行中使用
> + 列表名会返回到客户机

#### 自联结

通常快于子查询, 应该**都试一下**, 比较性能

```mysql
select p1.字段1, p1.字段2 from 表1 as p1,表2 as p2 where p1.字段1 = p2.字段1 and p2.字段2 = 值
```

#### 自然联结

> 自然联结排除多次出现的列, 使每个列只返回一次
>
> 对表使用明确的子集来完成

#### 外部联结

> 联结包含了那些不在相关表中的行

```mysql
select 表1.字段1,表2.字段2 from 表1 LEFT OUTER JOIN 表2 on 表1.字段1 = 表2.字段3
```

> 有left outer join 和 right outer join 两种, 使用区别就是前后顺序而已

## 组合查询(UNION)

> 利用UNION 操作符将多条SELECT语句, 将结果组合成一个结果集\

多条 SELECT 语句之间使用UNION 联结即可(n条select语句, n -1 个 UNION )

+ 每个查询必须包含相同的列, 表达式或聚集函数
+ 列数据必须兼容(类型不一定要相同, 但数据库自己要相互转换)
+ UNION 默认去除重复行, 如果**不想去重**可以使用 UNION ALL

+ 只支持在最后使用一条**ORDER BY**

## 全文本搜索

> 为了使用全文本搜索, 必须索引被搜索的列, 而且要随着数据的改变不断的重新索引.
>
> 在对表列进行适当设计后, Mysql会自动进行所有索引和重新索引.

+ 有**较高优先级**的行优先返回 (出现频率, 出现位置等)

+ 速度极快

### 启用全文本搜索支持

+ 创建字段时增加子句: FULLTEXT(字段1,字段2...) --- 这样mysql将会在之后自动维护索引

+ 如果有大量数据需要导入, 可以**在之后指定**, 这样可以节省大量时间

### 进行全文本检索

+ match( ) 指定字段 -- 传递给Match()的值必须和FULLTEXT()定义中的相同

+ against( ) 指定要使用的搜索表达式

```mysql
select 字段 from 表格 where Match(字段) Against ('搜索表达式')
```

### 使用查询拓展

+ 设法放宽全文本搜索结果的范围
+ 首先, 找出与搜索条件匹配的所有行
+ 其次, mysql检查所有匹配行并找出所有有用的词
+ 最后, mysql再次进行全文本搜索, 这次不仅使用原来的条件, 还使用所有有用的词

```mysql
select 字段 from 表 where Match(字段) Against('查询词' with Query EXPANSION )
```

### 布尔文本搜索(boolean mode)

**排列而不排序**

+ 以布尔方式, 可以提供关于如下内容的细节:

+ 要匹配的词
+ 要排斥的词
+ 排列提示
+ 表达式分组
+ 等..

*即使**没有**FULLTEXT索引也可以使用 (不过速度非常缓慢)*

+ 使用方法: 跟在**against字符串**之后使用

against('heavy -rope*' **IN BOOLEAN MODE**)

![1547629350024](/home/gavin/.config/Typora/typora-user-images/1547629350024.png)

> ps:
>
> + 3个或3个以下的字符通常都被忽略
> + mysql带有一个内建的非用词(stopword)列表, 里面出现的词默认被忽略
> + 如果一个词出现在了50%以上的行, 会忽略之
> + 忽略词中出现的单引号(don't 索引为dont)
> + 不具有词分隔符(包括日语和汉语)的语言不能恰当的返回全文本搜索结果

## 数据插入

### 一条/多条插入

+ ```mysql
INSERT INTO 表名(字段1, 字段2, ...) values('值1','值2'), ('值3','值4')...;
insert LOW_PRIORITY INTO -- 可以降低insert语句的优先级(同样支持UPDATE 和 DELETE)--提高性能
```

+ 插入检索出的数据

```mysql
INSERT INTO 表1(字段1, 字段2) SELECT (字段1,字段2) from 表2
```

> 列名不用对应, 行数一样即可一次插入

```mysql
SELECT last_insert_id()
可以返回最后一个AUTO_INCRENENT(),并且可以将它用于后续的MySql语句
```

## 更新和删除数据

**!!! 切记用条件**

+ ```mysql
UPDATE 表名 SET 字段='值1',字段2='值2' WHERE 条件;
```

> 默认会出错会回滚, 如果不想回滚可以使用UPDATE IGNORE 表名

+ ```mysql
DELETE FROM 表格 WHERE 条件
```

TRUNCATE TABLE -- 删除所有表内的行(本质是删除表并重新创建一个)

可先用SELECT 进行测试 !! 保证筛选的是正确的记录

## 创建和操纵表

+ ```mysql
create table user
(
uid int primary key auto_increment,
uname varchar(30) NOT NULL
upassword varchar(30)
);
```

+ ''(两个单引号在非not null列中是允许的)
+ null值是没有值, 不是空值

**多字段做主键**

+ ```mysql
create table user2
(
id int not null,
name varchar(20) not null,
password varchar(20),
primary key(id,name)
);
```

### 常用引擎

默认InnoDB

![1547643085274](/home/gavin/.config/Typora/typora-user-images/1547643085274.png)

+ 如果要提供提交、回滚、崩溃恢复能力的事物安全（ACID兼容）能力，并要求实现并发控制，InnoDB是一个好的选择

+ 如果数据表主要用来插入和查询记录，则MyISAM引擎能提供较高的处理效率

+ 如果只是临时存放数据，数据量不大，并且不需要较高的数据安全性，可以选择将数据保存在内存中的Memory引擎，MySQL中使用该引擎作为临时表，存放查询的中间结果

+ 如果只有INSERT和SELECT操作，可以选择Archive，Archive支持高并发的插入操作，但是本身不是事务安全的。Archive非常适合存储归档数据，如记录日志信息可以使用Archive

### 更新表

+ ALTER TABLE 表名 ADD 字段条件

```mysql
ALTER TABLE 表名 ADD 字段 char(20);
ALTER TABLE 表名 DROP COLUMN 字段;
```

+ 复杂的表结构可以选择重新创建
+ 用新的列布局创建一个新表
+ 使用了INSERT SELECT 语句迁移数据
+ 检验新表
+ 重命名旧表(如果确定可以删除她)
+ 用旧表原来的名字重命名新表
+ 根据需要, 重新创建触发器. 存储过程 . 索引外键

### 删除表

> drop table 表名

### 重命名表

RENAME TABLE 表名1 TO 表名2;

### 视图

> 简化或者增加重用复杂的代码
>
> 保存查询SQL语句
>
> 最好创建一个复用性高的视图
>
> 一般来说, 视图应用于检索, 而不用来更新

+ CREATE VIEW 视图名 as 视图语句创建视图+
+ SHOW CREATE VIEW view-name        查看创建视图的语句
+ DROP VIEW view-name               删除视图
+ CREATE OR REPLACE VIEW               更新视图

例子:

创建一个返回已订购任意产品的所有客户的列表的视图

使用时: `select * from 视图名 where 字段='产品名';`