秒懂MySql之基础使用

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

概述

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

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

数据库与SQL

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

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

Mysql

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

使用

安装数据库

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

连接数据库

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

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

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

sudo mysql -u root -p

操作数据库

• 查看mysql中的各个数据库

SHOW DATABASES;

输出：

+--------------------+
| Database           |
+--------------------+
| blogdb             |
| information_schema |
| mysql              |
| performance_schema |
| sstxdb             |
| sstxdb_dev         |
+--------------------+

后面两个是我建的数据库，前面的都是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操作符除了=还有很多

操作符	说明
<> 或者 !=	不等于
>	大于
>=	大于等于
<	小于
<=	小于等于
BETWEEN AND	在两个指定值之间
IN	包含在列表中
NOT IN	不包含在列表中

这里展示一下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 支持使用正则表达式来查询数据，这个功能就牛逼了，正则表达式有多牛逼这个功能就有多牛逼。可惜我们首先需要会写正则表达式，不然就是然并卵。若不懂正则，可以看一下这篇文章 正则表达式全解

在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)       |
...
+------------------------+

文本处理函数

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

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

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

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

日期和时间处理函数

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

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

获取日期某一个部分的函数
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()等等。

聚集函数

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

常用的有

名称	说明
AVG()	获取某列平均值
COUNT()	获取某列行数
MAX()	获取某列最大值
MIN()	获取某列最小值
SUM	获取某列值之和

实例

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. 聚集函数不其他列一起查询，因为没有意义，聚集函数只返回一行，所以其他列也只能获得第一行的值。

BASE64函数

mysql 提供了base64相关的函数

• TO_BASE64(要转化的列)

  顾名思义，将数据转化为base64

• FROM_BASE64(要转化的列)

  顾名思义，从base64转化为其他类型

实例

SELECT TO_BASE64(stu_name) AS '名称' FROM student WHERE stu_name = 'ShuSheng007'

输出：

U2h1U2hlbmcwMDc=

note : 有时由于你的数据库客户端，例如Navicat的设置，导致查询base64的时候显示blob， 你需要转化成字符串查看

SELECT CONVERT(FROM_BASE64(stu_name) USING utf8) AS stu_name FROM student ;

修改数据

插入数据

• 完整插入一行

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，效率较高。 其会重置自增主键，DELETE不会。

TRUNCATE table;

总结

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