秒懂MySql之基础使用

[版权申明]非商业目的注明出处可自由转载
博文地址: http://shusheng007.top/2021/04/04/1-10/
出自: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 不包含在列表中

这里展示一下BETWEENIN的用法,其他的都很直观。

查询价格间于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的条件判断是可以被组合的,使用ANDOR 关键字,关键是当子句太多时,一定要注意使用(),不然不容易判断优先级。

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

修改数据

插入数据

  • 完整插入一行
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较为高级点的使用知识。

You May Also Like

About the Author: shusheng007

发表评论

邮箱地址不会被公开。