秒懂MySql之基础使用

￡神魔★判官ぃ 2022-12-29 09:41 88阅读 0赞

> \[版权申明\]非商业目的注明出处可自由转载  
> 博文地址：https://blog.csdn.net/ShuSheng0007/article/details/111500713  
> 出自：shusheng007

### 文章目录 ###

*  概述
 *  数据库与SQL
 *   *  Mysql
 *  使用
 *   *  安装数据库
     *  连接数据库
     *  操作数据库
     *  查询数据
     *  排序数据
     *  过滤数据
 *  函数
 *   *  算术运算
     *  文本处理函数
     *  日期和时间处理函数
     *  数学相关函数
     *  聚集函数
 *  修改数据
 *   *  插入数据
     *  更新数据
     *  删除数据
 *  总结

# 概述 #

IT江湖上形容一个人很菜的时候有时会说：那哥们儿除了怎删改查，毛都不会！偶尔形容工作枯燥无味，毫无技术含量时也会抱怨：整天就是一些增删改查，一点挑战性都没有。可见大家普遍认为数据库的增删改查很容易，但也从侧面说明大部分公司的业务只要增删改查就足够了，就可以混一口饭吃了。

本系列就准备叨逼叨逼一下这项能混饭吃的能力，只要能混上口饭吃就有机会混口**好**饭。本文谈论Mysql最基本的使用方法，必知必会。

# 数据库与SQL #

默认你娃都晓得，不晓得就不要往下看了…

此处就叨逼一句，MySql之类的数据库在移动互联网时代之所以可以大行其道，就是业界最后终于明白了"猛虎架不住群狼"的道理。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NodVNoZW5nMDAwNw_size_16_color_FFFFFF_t_70]

## Mysql ##

MySql是C/S架构，我们平常说的数据库指的就是其S部分，C部分指那些可以连接到S的程序。例如mysql命令行、Navicat、mysql administrator 、脚本语言、使用编程语言构建的访问数据库的程序等。

# 使用 #

## 安装数据库 ##

这不在本文讨论范围内，参见：[Linux如何安装并配置Mariadb/Mysql][Linux_Mariadb_Mysql]

## 连接数据库 ##

使用Mysql的第一步就是要使用客户端程序连接到数据库。一般需要如下信息：

\-主机名（IP，或者域名）  
\-端口号，默认3306  
\-用户名与密码

下面命令使用root账户登陆数据库，随后输入root账户密码即可进入mysql数据库

sudo mysql -u root -p

## 操作数据库 ##

*  查看mysql中的各个数据库

SHOW DATABASES;

输出：

后面两个是我建的数据库，前面的都是mysql内部使用的。

*  创建一个名为learn\_sql的数据库

CREATE DATABASE learn_sql;

*  选择要操作的数据库, 我们选择mysql这个数据库

USE mysql;

输出数据库已经改变了

Database changed

*  查看mysql数据库的表信息

SHOW TABLES;

输出了mysql里面的表

+---------------------------+
    | Tables_in_mysql           |
    +---------------------------+
    | column_stats              |
    ...
    | user                      |
    +---------------------------+

*  查看某一张表的列信息

SHOW COLUMNS FROM user;

输出结果：

+------------------------+-----------------------------------+------+-----+----------+-------+
    | Field                  | Type                              | Null | Key | Default  | Extra |
    +------------------------+-----------------------------------+------+-----+----------+-------+
    | Host                   | char(60)                          | NO   | PRI |          |       |
    | User                   | char(80)                          | NO   | PRI |          |       |
    | Password               | char(41)                          | NO   |     |          |       |
    ...
    +------------------------+-----------------------------------+------+-----+----------+-------+

此命令还有个更拟人化的快捷方式，就好像mysql是一个人一样，我们让它描述一下user表的内容。

DESCRIBE user;

SHOW 还有很多其他命令，我们可以使用如下命令来查看完整列表

HELP SHOW;

下面是其中的一部分

SHOW CHARACTER SET [like_or_where]
    SHOW COLLATION [like_or_where]
    SHOW CREATE DATABASE db_name
    SHOW CREATE EVENT event_name
    SHOW CREATE FUNCTION func_name
    SHOW CREATE PROCEDURE proc_name
    SHOW CREATE TABLE tbl_name
    SHOW CREATE TRIGGER trigger_name
    SHOW CREATE VIEW view_name
    ...

## 查询数据 ##

最常用的就是查询数据，也非常简单

*  查询表中的全部列

SELECT * FROM 表名;

*  查询表中特定的列

SELECT 列名,列名 FROM 表名;

实例：

输入：

SELECT prod_id,prod_name FROM products;

输出：

+---------+----------------+
    | prod_id | prod_name      |
    +---------+----------------+
    | ANV01   | .5 ton anvil   |
    | ANV02   | 1 ton anvil    |
    ...
    +---------+----------------+

*  使用`DISTINCT`关键字过滤重复的行记录

SELECT DISTINCT vend_id,prod_price FROM products;

表示查询products表中，vend\_id与prod\_price都不相同的记录。

*  限制查询的行数

有时查询结果太多，而我们只需要其中的几行可以使用`LIMIT`关键字

SELECT prod_id,prod_name FROM products LIMIT 3;

输出：

+---------+--------------+
    | prod_id | prod_name    |
    +---------+--------------+
    | ANV01   | .5 ton anvil |
    | ANV02   | 1 ton anvil  |
    | ANV03   | 2 ton anvil  |
    +---------+--------------+

上述代码获取查询的前3行，由于行号是基于0的，即第一行为0行。所以前3行业就是从0开始往后数3行。要是我想获取2到4行的记录呢？那就是从1开始往后数3行

SELECT prod_id,prod_name FROM products LIMIT 3 OFFSET 1;

需要注意的点：

1.  SQL 语句必须以 `;`结尾
2.  查询出来的行的顺序是无意义的，不能认为其与表中顺序一致。

## 排序数据 ##

使用`SELECT`查询出来的数据的顺序是无意义的，不能简单的认为和表中的数据顺序一致，假如你期间更新或删除过表中的数据，那么查询出来的数据顺序就可能与表中数据顺序不一致。

*  使用`ORDER BY`排序

SELECT prod_id,vend_id FROM products ORDER BY prod_id;

`ORDER BY` 后面跟着要排序的列名，此列名一般是前面检索出来的列，但是你也可以使用表中未检索的列来排序，例如products表中有一列为prod\_name,它没有在检索的范围里，但是也可以使用它进行排序。

不仅可以使用单列排序，也可以使用多个列排序。

SELECT prod_price,prod_id FROM products ORDER BY prod_price,prod_id;

输出

+------------+---------+
    | prod_price | prod_id |
    +------------+---------+
    |       2.50 | FC      |
    |       2.50 | TNT1    |
    |       3.42 | FU1     |
    |       4.49 | SLING   |
    ...
    +------------+---------+

可以看到，先使用了prod\_price进行了排序，如果存在多行的prod\_price相同，那么就使用prod\_id再次排序。

*  排序方向

排序方向分两种：  
顺序（升序）：使用ASC，其是ascending的缩写。默认使用这种方式，所以这个关键词用的很少。  
逆序（降序）：使用DESC，其是descending的缩写。

SELECT prod_price,prod_id FROM products ORDER BY prod_price DESC;

输出：

+------------+---------+
    | prod_price | prod_id |
    +------------+---------+
    |      55.00 | JP2000  |
    |      50.00 | SAFE    |
    |      35.00 | JP1000  |
    |      14.99 | ANV03   |
    ...
    +------------+---------+

可见，查询结果按照产品价格降序排列了。

## 过滤数据 ##

这部分非常实用，属于日常混饭的家伙事儿，因为正常情况下，数据的的数据是海量的，我们需要根据自己的需要过滤出目标数据，而不可能把所有数据都弄出来。

*  各种`WHERE`子句

先看一个简单的where子句查询实例

SELECT prod_price,prod_id FROM products WHERE prod_price=10.00;

上面的sql语句查询价格等于10.00的商品

+------------+---------+
    | prod_price | prod_id |
    +------------+---------+
    |      10.00 | FB      |
    |      10.00 | TNT2    |
    +------------+---------+

Where操作符除了`=`还有很多

<table> 
 <thead> 
  <tr> 
   <th>操作符</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td><code>&lt;&gt;</code>或者<code>!=</code></td> 
   <td>不等于</td> 
  </tr> 
  <tr> 
   <td><code>&gt;</code></td> 
   <td>大于</td> 
  </tr> 
  <tr> 
   <td><code>&gt;=</code></td> 
   <td>大于等于</td> 
  </tr> 
  <tr> 
   <td><code>&lt;</code></td> 
   <td>小于</td> 
  </tr> 
  <tr> 
   <td><code>&lt;=</code></td> 
   <td>小于等于</td> 
  </tr> 
  <tr> 
   <td><code>BETWEEN AND</code></td> 
   <td>在两个指定值之间</td> 
  </tr> 
  <tr> 
   <td><code>IN</code></td> 
   <td>包含在列表中</td> 
  </tr> 
  <tr> 
   <td><code>NOT IN</code></td> 
   <td>不包含在列表中</td> 
  </tr> 
 </tbody> 
</table>

这里展示一下`BETWEEN`与`IN`的用法，其他的都很直观。

查询价格间于12到40的产品

SELECT prod_price,prod_id FROM products WHERE prod_price BETWEEN 12 AND 40;

输出：

+------------+---------+
    | prod_price | prod_id |
    +------------+---------+
    |      14.99 | ANV03   |
    |      13.00 | DTNTR   |
    |      35.00 | JP1000  |
    +------------+---------+

查询由供应商1001或1005提供的产品

SELECT prod_price,prod_id,vend_id FROM products WHERE vend_id IN (1001,1005);

输出：

+------------+---------+---------+
    | prod_price | prod_id | vend_id |
    +------------+---------+---------+
    |       5.99 | ANV01   |    1001 |
    |       9.99 | ANV02   |    1001 |
    |      14.99 | ANV03   |    1001 |
    |      35.00 | JP1000  |    1005 |
    |      55.00 | JP2000  |    1005 |
    +------------+---------+---------+

**note**:值得注意的是，上面的操作符可以用在任何类型上而不仅仅是用在数字上，例如你也可以使用`>`来判断两个字符串的大小。

*  Null检查

我们知道，表的某一列我们可以设置为允许为NULL，那么如何判断某一个列的值是否为Null呢？使用`IS`关键字

SELECT cust_name FROM customers WHERE cust_email IS Null;

输出：

+-------------+
    | cust_name   |
    +-------------+
    | Mouse House |
    | E Fudd      |
    +-------------+

*  组合`WHERE`子句

`where`的条件判断是可以被组合的，使用`AND`与`OR` 关键字，关键是当子句太多时，一定要注意使用`()`，不然不容易判断优先级。

*  `LIKE`关键字与通配符

有时我们需要进行模糊查询，例如检索name列中包含`ben`，或者以`ben`开头，亦或是以`ben`结尾等等条件的数据，就需要用到`LIKE`关键字了。于`LIKE`关键字一起使用的就是通配符，通配符分为两种

`%` 适配任意多个字符  
`_` 适配一个字符

其中`%`通配符使用的比较多，下面举例解释一下这两个通配符

`‘%ss007’` : 以ss007结尾的任意字符串  
`ss007%` :以ss007开头的任意字符串  
`%ss007%`: 包含ss007的任意字符串

`_ ss007` : 以ss007结尾的前面只有一个任意字符的串  
`ss007 _` :以ss007开头的后面只有一个任意字符的串  
`_ ss007 _`: 包含ss007的前后都只有一个任意字符的串

SELECT cust_name FROM customers WHERE cust_name LIKE '%d';

*  正则表达式过滤(`REGEXP`)

mysql 支持使用正则表达式来查询数据，这个功能就牛逼了，正则表达式有多牛逼这个功能就有多牛逼。可惜我们首先需要会写正则表达式，不然就是然并卵。若不懂正则，可以看一下这篇文章 [正则表达式全解][Link 1]

在mysql中使用正则很简单，使用`REGEXP`关键字即可

例如要从products表中查询prod\_name中包含 `1 Ton`或`2 Ton`或`3 Ton`的记录，其中`[123] Ton`就是一个正则表达式。

SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton';

输出：

+-------------+
    | prod_name   |
    +-------------+
    | 1 ton anvil |
    | 2 ton anvil |
    +-------------+

# 函数 #

我们知道，SQL呢是一门标准的数据库编程语言，也有自己的标准化组织致力于使其标准化，一般情况下可以在不同的数据库之间直接迁移，但是函数就大不一样了，这是各家数据库自己可以自由发挥的方面了，相差很大，所以要做好注释便于以后移植，此处仅集中在MySql上谈论。

## 算术运算 ##

如果列中存放的数据是数字类型，我们就可以在查询是进行算术运算，支持`+`、`-`、`*`、`/`四则运算。

例如下面的代码将产品价格乘以2作为单独的一列prod\_double\_price返回。

SELECT prod_name,prod_price, prod_price*2 AS prod_double_price FROM  products LIMIT 3 ;

输出：

+--------------+------------+-------------------+
    | prod_name    | prod_price | prod_double_price |
    +--------------+------------+-------------------+
    | .5 ton anvil |       5.99 |             11.98 |
    | 1 ton anvil  |       9.99 |             19.98 |
    | 2 ton anvil  |      14.99 |             29.98 |
    +--------------+------------+-------------------+

`AS` 后面那个 `prod_double_price` 称作别名（alias）

*  连接函数Concat()

Concat 是concatenate是简称，就是连接的意思。使用此函数可以将多个字符串连接成一个，下面将供应商的名称与地址连接成一个字段。

SELECT Concat(vend_name,'(',vend_country,')') AS '供应商名称'  FROM vendors;

输出：

+------------------------+
    | 供应商名称             |
    +------------------------+
    | Anvils R Us(USA)       |
    | LT Supplies(USA)       |
    ...
    +------------------------+

## 文本处理函数 ##

相关函数比较多，此处列出几个典型的，使用时还应该查询相关文档。

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Left(str,len)</td> 
   <td>从左开始截取str串len位</td> 
   <td>Left(‘we love shusheng007’,2) 结果: we</td> 
  </tr> 
  <tr> 
   <td>Right(str,len)</td> 
   <td>从右开始截取str串len位</td> 
   <td>Right(‘we love shusheng007’,2) 结果：07</td> 
  </tr> 
  <tr> 
   <td>Substring(str,pos)</td> 
   <td>从第pos位截取str到末尾</td> 
   <td>Substring(‘we love shusheng007’,8) 结果：shusheng007</td> 
  </tr> 
  <tr> 
   <td>Substring(str,pos,len)</td> 
   <td>从str的第pos位开始截取len长度的字串</td> 
   <td>Substring(‘we love shusheng007’,8,3) 结果：shu</td> 
  </tr> 
  <tr> 
   <td>Substring_index(str,key,count)</td> 
   <td>从str中截取第count个key之前的串</td> 
   <td>Substring_index(‘we love shusheng007’,’ ',2) 结果：we love</td> 
  </tr> 
  <tr> 
   <td>Locate(subStr,Str)</td> 
   <td>返回subStr在Str的起始位置，从1开始</td> 
   <td>Locate(‘love’,‘we love shusheng007’) 结果：4</td> 
  </tr> 
 </tbody> 
</table>

除了上面的还有很多，像Lower(),Upper(),LTrim(),RTrim(),Soundex()等等。

其中Soundex() 这个函数很有意思，它可以比较字符串的发音，而不是字符串本身。例如ben和ban两个不相等，但是他们的发音相近，Soundex(‘ben’)却等于Soundex(‘ban’)

## 日期和时间处理函数 ##

对日期的操作也比较常见，此处列出部分常用函数

<table> 
 <thead> 
  <tr> 
   <th>函数</th> 
   <th>说明</th> 
   <th>示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>Date_Formate(日期，格式)</td> 
   <td>格式化日期</td> 
   <td>DATE_FORMAT(NOW(),’%m-%d-%Y %T’) 结果：12-27-2020 09:38:47</td> 
  </tr> 
  <tr> 
   <td>Date_Add(日期,INTERVAL 间隔 类型)</td> 
   <td>计算日期函数，极度灵活</td> 
   <td>DATE_ADD(NOW(),INTERVAL 3 DAY)结果：3天后</td> 
  </tr> 
  <tr> 
   <td>DateDiff(日期,日期)</td> 
   <td>计算两个日期直接的差值</td> 
   <td>DateDiff(‘2020-01-20’, ‘2020-01-01’)结果：19</td> 
  </tr> 
 </tbody> 
</table>

获取日期某一个部分的函数  
Date(‘2020-11-05 10:30:20’) 结果： 2020-11-05  
Time(‘2020-11-05 10:30:20’) 结果： 10:30:20  
Year(),Month(),Day(),Hour(),Minute(),Second(),DayOfWeek() 你可以顾名思义。

## 数学相关函数 ##

这块用的也比较少，常用的有求绝对值`Abs()`、求余`Mod()`、产生随机数`Rand()`、求平方根`Sqrt()`等等。

## 聚集函数 ##

这块是我们经常使用到的，但是也很简单。顾名思义，聚集函数，就是通过多个数据聚集出一个数据来。

常用的有

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>AVG()</td> 
   <td>获取某列平均值</td> 
  </tr> 
  <tr> 
   <td>COUNT()</td> 
   <td>获取某列行数</td> 
  </tr> 
  <tr> 
   <td>MAX()</td> 
   <td>获取某列最大值</td> 
  </tr> 
  <tr> 
   <td>MIN()</td> 
   <td>获取某列最小值</td> 
  </tr> 
  <tr> 
   <td>SUM</td> 
   <td>获取某列值之和</td> 
  </tr> 
 </tbody> 
</table>

实例

SELECT AVG(prod_price) AS avg_price,MAX(prod_price) AS max_price FROM products;

输出：

+-----------+-----------+
    | avg_price | max_price |
    +-----------+-----------+
    | 16.133571 |     55.00 |
    +-----------+-----------+

实际使用比较简单，只是有一些点需要注意：

1.  除了`COUNT(*)`以外，都会排除值为`NULL`的列。
2.  聚集函数不其他列一起查询，因为没有意义，聚集函数只返回一行，所以其他列也只能获得第一行的值。

# 修改数据 #

## 插入数据 ##

*  完整插入一行

INSERT INTO table VALUES(cv1,cv2,cv3,cv4...);

这种插入方式可以省略列名，但是要求插入的值必须包含表中的所有列，而且必须按照列在表中顺序排列。假设c1列的值有数据库自增生成，但是你也不能省略，必须以NULL代替。极其**不建议**使用这种方式，当表发生变化后，此sql就失效了。

*  指定列插入一行

INSERT INTO table(c2,c3) VALUES(cv2,cv3);

这种插入方式可以控制要插入的列值，省略一些不需要赋值的列。可以省略的列必须可以自己完成填充。例如数据库生成（自增），例如可以为Null，例如存在默认值。

*  插入多行

把插入一行的语句多写几遍就可以完成插入多行了。

但是插入多行还有一种可以提高插入效率的写法：

INSERT INTO table(c2,c3) VALUES(cv21,cv31),(cv22,cv32),(cv23,cv33),...;

*  插入查询出来的数据

这个功能很实用，我们可以使用它从其他表查询数据，然后插入到我们的表中。

INSERT INTO table1 (c2,c3) 
           SELECT c21,c31 
           FROM table2;

## 更新数据 ##

这个操作非常危险，因为一不小心就会将不想更新的数据给覆盖了，所以进行更新操作时，一定要先SELECT 出要更新的数据，确定确实需要更新。

UPDATE table SET column1='cv1',column2=cv2 WHERE ...;

一般情况下,当更新某条数据失败后，整个更新操作都会回滚，如果我们要改变这个行为，让更新继续执行而忽略失败时，可以使用`IGNORE` 关键字

UPDATE IGNORE table SET column1='cv1',column2=cv2 WHERE ...;

## 删除数据 ##

这个操作也非常危险，一不小心就会将不想删除的数据给删除了，那样你只能跑路拉，所以进行删除操作时，一定要先SELECT 出要删除的数据，确定确实需要删除。

DELETE from table WHERE ...;

如果没有`WHERE`子句的话就是删除整张表的数据，如果这真的是你需要的推荐使用`TRUNCATE`，效率较高

TRUNCATE table;

# 总结 #

本文是我读《mysql必知必会》的总结，虽然大部分内容都非常简单易懂，但是却很实用，对于初学SQL的同学非常实用，但是对于专业DBA就显得很low了, 看书花费了一天，写半篇文章却花费了两天，所以说得来易躬行难。下一篇我们谈论一下MySql较为高级点的使用知识。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L1NodVNoZW5nMDAwNw_size_16_color_FFFFFF_t_70]: /images/20221120/ec7c80e9ec894362aaaa9381968b687f.png
[Linux_Mariadb_Mysql]: https://blog.csdn.net/ShuSheng0007/article/details/111470488
[Link 1]: https://blog.csdn.net/ShuSheng0007/article/details/80001586