投稿 资料上传 搜索
您现在的位置是: 首页 > 文章 > 正文

MySQL操作数据表

前言

我们在页面上看到的数据展示和修改等一系列操作,其实是对数据表的查询修改。所以学习基础的程序设计,我们必须掌握数据表的操作!

查询(select)

基本格式如下:

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

名词含义如下:

  • {*|<字段列名>}包含星号通配符的字段列表,表示所要查询字段的名称。
  • <表 1>,<表 2>…,表 1 和表 2 表示查询数据的来源,可以是单个或多个。
  • WHERE <表达式>是可选项,如果选择该项,将限定查询数据必须满足该查询条件。
  • GROUP BY< 字段 >,该子句告诉 MySQL 如何显示查询出来的数据,并按照指定的字段分组。
  • [ORDER BY< 字段 >],该子句告诉 MySQL 按什么样的顺序显示查询出来的数据,可以进行的排序有升序(ASC)和降序(DESC),默认情况下是升序。
  • [LIMIT[<offset>,]<row count>],该子句告诉 MySQL 每次显示查询出来的数据条数。

查询表中所有字段

SELECT 使用“*”查找表中所有字段的数据,或者列出所有字段名,语法格式如下:  
SELECT * FROM 表名;

注意:使用“*”查询时,只能按照数据表中字段的顺序进行排列,不能改变字段的排列顺序。  

查询表中指定的字段

查询表中的某一个字段的语法格式为: 
SELECT < 列名 > FROM < 表名 >;

去重(DISTINCT)

基本语法格式如下:

SELECT DISTINCT <字段名> FROM <表名>;

“字段名”为需要消除重复记录的字段名称,多个字段时用逗号隔开。  

使用 DISTINCT 关键字时需要注意以下几点:
  • DISTINCT 关键字只能在 SELECT 语句中使用。
  • 在对一个或多个字段去重时,DISTINCT 关键字必须在所有字段的最前面。
  • 如果 DISTINCT 关键字后有多个字段,则会对多个字段进行组合去重,也就是说,只有多个字段组合起来完全是一样的情况下才会被去重。

为表指定别名

基本语法格式如下: 
<表名> [AS] <别名>

其中各子句的含义如下:

  • <表名>:数据库中存储的数据表的名称。
  • <别名>:查询时指定的表的新名称。
  • AS关键字可以省略,省略后需要将表名和别名用空格隔开。
注意:表的别名不能与该数据库的其它表同名。字段的别名不能与该表的其它字段同名。在条件表达式中不能使用字段的别名,否则会出现“ERROR 1054 (42S22): Unknown column”这样的错误提示信息。 

为字段指定别名

基本语法格式如下:

<字段名> [AS] <别名>

其中,各子句的语法含义如下:

  • <字段名>:为数据表中字段定义的名称。
  • <字段别名>:字段新的名称。
  • AS关键字可以省略,省略后需要将字段名和别名用空格隔开。

限制查询结果的条数(limit)

LIMIT 是 MySQL 中的一个特殊关键字,用于指定查询结果从哪条记录开始显示,一共显示多少条记录。LIMIT 关键字有 3 种使用方式,即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。  

指定初始位置

基本语法格式如下: 

LIMIT 初始位置,记录数

SELECT * FROM 表名 LIMIT 3,5; //起始位置3,结束为止5

不指定初始位置

基本语法格式如下:  
LIMIT 记录数

SELECT * FROM 表名 LIMIT 4; //4条记录数量

LIMIT和OFFSET组合使用

基本语法格式如下:  
LIMIT 记录数 OFFSET 初始位置

SELECT * FROM tb_students_info LIMIT 5 OFFSET 3; //返回从第 4 条记录开始的行数为 5 的记录

对查询结果排序 (ORDER BY)

其语法格式如下: 

ORDER BY <字段名> [ASC|DESC]

语法说明如下。

  • 字段名:表示需要排序的字段名称,多个字段时用逗号隔开。
  • ASC|DESC:ASC表示字段按升序排序;DESC表示字段按降序排序。其中ASC为默认值。

注意:

  • ORDER BY 关键字后可以跟子查询(关于子查询后面教程会详细讲解,这里了解即可)。
  • 当排序的字段中存在空值时,ORDER BY 会将该空值作为最小值来对待。

单字段排序

SELECT * FROM 表名 ORDER BY 字段名;

多字段排序

SELECT 字段1,字段2 FROM 表名 ORDER BY 字段1,字段2;

注意:在对多个字段进行排序时,排序的第一个字段必须有相同的值,才会对第二个字段进行排序。如果第一个字段数据中所有的值都是唯一的,MySQL 将不再对第二个字段进行排序。  

默认情况下,查询数据按字母升序进行排序(A~Z),但数据的排序并不仅限于此,还可以使用 ORDER BY 中的 DESC 对查询结果进行降序排序(Z~A)。  

条件查询数据(where)

基本语法格式如下:  

WHERE 查询条件

查询条件可以是:

  • 带比较运算符和逻辑运算符的查询条件
  • 带 BETWEEN AND 关键字的查询条件
  • 带 IS NULL 关键字的查询条件
  • 带 IN 关键字的查询条件
  • 带 LIKE 关键字的查询条件

单一条件的查询语句

单一条件指的是在 WHERE 关键字后只有一个查询条件。 

多条件的查询语句

在 WHERE 关键词后可以有多个查询条件,这样能够使查询结果更加精确。多个查询条件时用逻辑运算符 AND(&&)、OR(||)或 XOR 隔开。

  • AND:记录满足所有查询条件时,才会被查询出来。
  • OR:记录满足任意一个查询条件时,才会被查询出来。
  • XOR:记录满足其中一个条件,并且不满足另一个条件时,才会被查询出来。

模糊查询(like)

基本语法格式如下:

[NOT] LIKE  '字符串'

其中:

  • NOT :可选参数,字段中的内容与指定的字符串不匹配时满足条件。
  • 字符串:指定用来匹配的字符串。“字符串”可以是一个很完整的字符串,也可以包含通配符。

LIKE 关键字支持百分号“%”和下划线“_”通配符。 

通配符是一种特殊语句,主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时,可以使用通配符来代替一个或多个真正的字符。 

带有“%”通配符的查询

“%”是 MySQL 中最常用的通配符,它能代表任何长度的字符串,字符串的长度可以为 0。例如,a%b表示以字母 a 开头,以字母 b 结尾的任意长度的字符串。该字符串可以代表 ab、acb、accb、accrb 等字符串。  

带有“_”通配符的查询

_”只能代表单个字符,字符的长度不能为 0。例如,a_b可以代表 acb、adb、aub 等字符串。  

LIKE 区分大小写

默认情况下,LIKE 关键字匹配字符的时候是不区分大小写的。如果需要区分大小写,可以加入 BINARY 关键字。  

使用通配符的注意事项和技巧

注意事项:
  • 注意大小写。MySQL 默认是不区分大小写的。如果区分大小写,像“Tom”这样的数据就不能被“t%”所匹配到。
  • 注意尾部空格,尾部空格会干扰通配符的匹配。例如,“T% ”就不能匹配到“Tom”。
  • 注意 NULL。“%”通配符可以到匹配任意字符,但是不能匹配 NULL。也就是说 “%”匹配不到 tb_students_info 数据表中值为 NULL 的记录。

使用技巧:
  • 不要过度使用通配符,如果其它操作符能达到相同的目的,应该使用其它操作符。因为 MySQL 对通配符的处理一般会比其他操作符花费更长的时间。
  • 在确定使用通配符后,除非绝对有必要,否则不要把它们用在字符串的开始处。把通配符置于搜索模式的开始处,搜索起来是最慢的。
  • 仔细注意通配符的位置。如果放错地方,可能不会返回想要的数据。

范围查询(BETWEEN AND)

基本语法格式如下:

[NOT] BETWEEN 取值1 AND 取值2

其中:

  • NOT:可选参数,表示指定范围之外的值。如果字段值不满足指定范围内的值,则这些记录被返回。
  • 取值1:表示范围的起始值。
  • 取值2:表示范围的终止值。

BETWEEN AND 和 NOT BETWEEN AND 关键字在查询指定范围内的记录时很有用。例如,查询学生的年龄段、出生日期,员工的工资水平等。  

空值查询 (IS NULL)

MySQL 提供了 IS NULL 关键字,用来判断字段的值是否为空值(NULL)。空值不同于 0,也不同于空字符串。 如果字段的值是空值,则满足查询条件,该记录将被查询出来。如果字段的值不是空值,则不满足查询条件。 

基本语法格式如下:

IS [NOT] NULL

“NOT”是可选参数,表示字段值不是空值时满足条件。  

分组查询(GROUP BY)

基本语法格式如下:

GROUP BY  <字段名>

“字段名”表示需要分组的字段名称,多个字段时用逗号隔开。 

GROUP BY单独使用

单独使用 GROUP BY 关键字时,查询结果会只显示每个分组的第一条记录。

GROUP BY 与 GROUP_CONCAT() 

GROUP BY 关键字可以和 GROUP_CONCAT() 函数一起使用。GROUP_CONCAT() 函数会把每个分组的字段值都显示出来。 

GROUP BY 与聚合函数

在数据统计时,GROUP BY 关键字经常和聚合函数一起使用。

聚合函数包括 COUNT(),SUM(),AVG(),MAX() 和 MIN()。其中,COUNT() 用来统计记录的条数;SUM() 用来计算字段值的总和;AVG() 用来计算字段值的平均值;MAX() 用来查询字段的最大值;MIN() 用来查询字段的最小值。  

GROUP BY 与 WITH ROLLUP

WITH POLLUP 关键字用来在所有记录的最后加上一条记录,这条记录是上面所有记录的总和,即统计记录数量。 

过滤分组(HAVING)

在 MySQL 中,可以使用 HAVING 关键字对分组后的数据进行过滤。  

基本语法格式如下:

HAVING <查询条件>

HAVING 关键字和 WHERE 关键字都可以用来过滤数据,且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
但是 WHERE 和 HAVING 关键字也存在以下几点差异:

  • 一般情况下,WHERE 用于过滤数据行,而 HAVING 用于过滤分组。
  • WHERE 查询条件中不可以使用聚合函数,而 HAVING 查询条件中可以使用聚合函数。
  • WHERE 在数据分组前进行过滤,而 HAVING 在数据分组后进行过滤 。
  • WHERE 针对数据库文件进行过滤,而 HAVING 针对查询结果进行过滤。也就是说,WHERE 根据数据表中的字段直接进行过滤,而 HAVING 是根据前面已经查询出的字段进行过滤。
  • WHERE 查询条件中不可以使用字段别名,而 HAVING 查询条件中可以使用字段别名。

正则表达式(REGEXP)

正则表达式主要用来查询和替换符合某个模式(规则)的文本内容。例如,从一个文件中提取电话号码,查找一篇文章中重复的单词、替换文章中的敏感语汇等,这些地方都可以使用正则表达式。正则表达式强大且灵活,常用于非常复杂的查询。 

基本语法格式如下:

属性名 REGEXP '匹配方式'

其中,“属性名”表示需要查询的字段名称;“匹配方式”表示以哪种方式来匹配查询。“匹配方式”中有很多的模式匹配字符,它们分别表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。 

选项说明例子匹配值示例
^匹配文本的开始字符'^b' 匹配以字母 b 开头的字符串book、big、banana、bike
$匹配文本的结束字符'st$' 匹配以 st 结尾的字符串test、resist、persist
.匹配任何单个字符'b.t' 匹配任何 b 和 t 之间有一个字符bit、bat、but、bite
*匹配零个或多个在它前面的字符'f*n' 匹配字符 n 前面有任意个字符 ffn、fan、faan、abcn
+匹配前面的字符 1 次或多次'ba+' 匹配以 b 开头,后面至少紧跟一个 aba、bay、bare、battle
<字符串>匹配包含指定字符的文本'fa' 匹配包含‘fa’的文本fan、afa、faad
[字符集合]匹配字符集合中的任何一个字符'[xz]' 匹配 x 或者 zdizzy、zebra、x-ray、extra
[^]匹配不在括号中的任何字符'[^abc]' 匹配任何不包含 a、b 或 c 的字符串desk、fox、f8ke
字符串{n,}匹配前面的字符串至少 n 次'b{2}' 匹配 2 个或更多的 bbbb、bbbb、bbbbbbb
字符串
{n,m}
匹配前面的字符串至少 n 次, 至多 m 次'b{2,4}' 匹配最少 2 个,最多 4 个 bbbb、bbbb

 

查询以特定字符或字符串开头的记录

字符^用来匹配以特定字符或字符串开头的记录。

查询以特定字符或字符串结尾的记录

字符$用来匹配以特定字符或字符串结尾的记录。

替代字符串中的任意一个字符

字符.用来替代字符串中的任意一个字符。 

匹配多个字符

字符*+都可以匹配多个该符号之前的字符。不同的是,+表示至少一个字符,而*可以表示 0 个字符。 

匹配指定字符串

正则表达式可以匹配字符串。当表中的记录包含这个字符串时,就可以将该记录查询出来。指定多个字符串时,需要用|隔开。只要匹配这些字符串中的任意一个即可。 

匹配指定字符串中的任意一个

使用方括号[ ]可以将需要查询的字符组成一个字符集合。只要记录中包含方括号中的任意字符,该记录就会被查询出来。例如,通过“[abc]”可以查询包含 a、b 和 c 等 3 个字母中任意一个的记录。 

匹配指定字符以外的字符

[^字符集合]用来匹配不在指定集合中的任何字符。  

使用{n,}或者{n,m}来指定字符串连续出现的次数

字符串{n,}表示字符串连续出现 n 次;字符串{n,m}表示字符串连续出现至少 n 次,最多 m 次。
例如,a{2,} 表示字母 a 连续出现至少 2 次,也可以大于 2 次;a{2,4} 表示字母 a 连续出现最少 2 次,最多不能超过 4 次。  

插入数据/添加数据(INSERT)

INSERT 语句有两种语法形式,分别是 INSERT…VALUES 语句和 INSERT…SET 语句。 

INSERT…VALUES语句

INSERT INTO <表名> [ <列名1> [ , … <列名n>] ]
VALUES (值1) [… , (值n) ];

语法说明如下。

  • <表名>:指定被操作的表名。
  • <列名>:指定需要插入数据的列名。若向表中的所有列插入数据,则全部的列名均可以省略,直接采用 INSERT<表名>VALUES(…) 即可。
  • VALUES 或 VALUE 子句:该子句包含要插入的数据清单。数据清单中数据的顺序要和列的顺序相对应。

INSERT…SET语句

INSERT INTO <表名>
SET <列名1> = <值1>,
<列名2> = <值2>,

此语句用于直接给表中的某些列指定对应的列值,即要插入的数据的列名在 SET 子句中指定,col_name 为指定的列名,等号后面为指定的数据,而对于未指定的列,列值会指定为该列的默认值。

由 INSERT 语句的两种形式可以看出:

  • 使用 INSERT…VALUES 语句可以向表中插入一行数据,也可以插入多行数据;
  • 使用 INSERT…SET 语句可以指定插入行中每列的值,也可以指定部分列的值;
  • INSERT…SELECT 语句向表中插入其他表的数据。
  • 采用 INSERT…SET 语句可以向表中插入部分列的值,这种方式更为灵活;
  • INSERT…VALUES 语句可以一次插入多条数据。

在 MySQL 中,用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。
当使用单条 INSERT 语句插入多行数据的时候,只需要将每行数据用圆括号括起来即可。  

修改数据/更新数据(UPDATE)

使用 UPDATE 语句修改单个表,基本语法格式如下:  

UPDATE <表名> SET 字段 1=值 1 [,字段 2=值 2… ] [WHERE 子句 ]
[ORDER BY 子句] [LIMIT 子句]

语法说明如下:

  • <表名>:用于指定要更新的表名称。
  • SET 子句:用于指定表中要修改的列名及其列值。其中,每个指定的列值可以是表达式,也可以是该列对应的默认值。如果指定的是默认值,可用关键字 DEFAULT 表示列值。
  • WHERE 子句:可选项。用于限定表中要修改的行。若不指定,则修改表中所有的行。
  • ORDER BY 子句:可选项。用于限定表中的行被修改的次序。
  • LIMIT 子句:可选项。用于限定被修改的行数。

注意:修改一行数据的多个列值时,SET 子句的每个值用逗号分开即可。 

删除数据(DELETE)

基本语法格式如下:  

DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]

语法说明如下:

  • <表名>:指定要删除数据的表名。
  • ORDER BY 子句:可选项。表示删除时,表中各行将按照子句中指定的顺序进行删除。
  • WHERE 子句:可选项。表示为删除操作限定删除条件,若省略该子句,则代表删除该表中的所有行。
  • LIMIT 子句:可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。


注意:在不使用 WHERE 条件的时候,将删除所有数据。  


转载于:

文章标签:
阿里云服务器采购季
给作者打赏,鼓励TA抓紧创作!
评论