MySQL语句操作(对数据表增删改查)

亦凉 2022-12-29 11:18 298阅读 0赞

# 前言 #

在数据表中插入、浏览、修改和删除记录可以在MySQL命令行中使用SQL语句完成。具体用法如下：

# （一）插入记录 INSERT #

在建立一个空的数据库和数据表时，首先需要考虑的是如何向数据表中添加数据，该操作可以用 insert 语句完成

insert into 数据表名(column_name,column_name2, ···）values ( value1, value2, ···)

在 MySQL 中，一次可以同时插入多行记录，各行记录的值清单在 `values` 关键字后以“`，`” 分隔，而标准的SQL语句一次只能插入一行。

【例】向管理员信息表`tb_admin`中插入一条数据信息，如图所示  
![在这里插入图片描述][20201223100338814.png]

# （二）查询数据库记录 SELECT #

`select` 语句是最常用的查询语句，它的使用方式有点复杂，但功能也是很强大。语句如下：

select  [distinct][concat(col 1, ":",col 2) as col] selection_list
    
    from 数据表名			//指定数据表
    where primary_constraint			//查询时需要满足的条件，行必须满足的条件
    group by grouping_columns			//如何对结果进行分组
    order by sorting_cloumns 			//如何对结果进行排序
    having secondary_constraint			//查询时满足的第二条件
    limit count 						//限定输出的查询结果

## ① selection\_list ##

设置查询内容。如果要查询表中所有列，可以将其设置为“`*`”；如果要查询表中某一列或多列，则直接输入列名，并以“`，`”为分隔符。

查询 `books` 数据表中所有列以及查询 `user` 和 `pass` 列。

select * from books;					//查询数据表中所有数据
    select user,pass from books;			//查询数据表中user和pass列的数据

## ② table\_list ##

指定查询的数据表。既可以从一个数据表中查询，也可以从多个数据表中查询，多个数据表之间用“`，`”进行分隔，并且通过 `where` 子句使用连接运算来确定表之间的关系。

## ③ where 条件语句 ##

在使用查询语句时，如要从很多的记录中查询出想要的记录，就需要一个查询的条件。只有设定了查询的条件，查询才有实际的意义。设定查询条件应用的是`where`子句。  
`where`子句的功能非常强大，通过它可以实现很多复杂的条件查询。在使用`where`子句时，需要使用一些比较运算符，常用比较运算符如下表。

<table> 
 <thead> 
  <tr> 
   <th>运算符</th> 
   <th>名称</th> 
   <th>示例</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>=</td> 
   <td>等于</td> 
   <td>id=5</td> 
  </tr> 
  <tr> 
   <td>&gt;</td> 
   <td>大于</td> 
   <td>id&gt;5</td> 
  </tr> 
  <tr> 
   <td>=</td> 
   <td>小于</td> 
   <td>id&lt;5</td> 
  </tr> 
  <tr> 
   <td>&gt;=</td> 
   <td>大于等于</td> 
   <td>id&gt;=5</td> 
  </tr> 
  <tr> 
   <td>&lt;=</td> 
   <td>小于等于</td> 
   <td>id&lt;=5</td> 
  </tr> 
  <tr> 
   <td>!=或&lt;&gt;</td> 
   <td>不等于</td> 
   <td>id!=5</td> 
  </tr> 
  <tr> 
   <td>is null</td> 
   <td>n/a</td> 
   <td>id is null</td> 
  </tr> 
  <tr> 
   <td>is not null</td> 
   <td>n/a</td> 
   <td>id is not null</td> 
  </tr> 
  <tr> 
   <td>between and</td> 
   <td>n/a</td> 
   <td>id between 1 and 15</td> 
  </tr> 
  <tr> 
   <td>in</td> 
   <td>n/a</td> 
   <td>id in (1,2,3)</td> 
  </tr> 
  <tr> 
   <td>not in</td> 
   <td>n/a</td> 
   <td>name not in(shi,li)</td> 
  </tr> 
  <tr> 
   <td>like</td> 
   <td>模式匹配</td> 
   <td>name like(‘shi%’)</td> 
  </tr> 
  <tr> 
   <td>not like</td> 
   <td>模式匹配</td> 
   <td>name not like(‘shi%’)</td> 
  </tr> 
  <tr> 
   <td>regexp</td> 
   <td>正则表达式</td> 
   <td>name正则表达式</td> 
  </tr> 
 </tbody> 
</table>

上表中示例的`id`是记录的编号，`name`是表中的用户名  
【例】用`where`语句，查询`books`表，条件是`type`（类型）为`python`的所有图书

select * from books where type = 'python';

## ④ group by 对结果分组 ##

通过`group by` 可以将数据划分到不同的组中，实现对记录的分组查询。在查询时，所查询的列必须包含在分组的列中，目的是使查询到的数据没有矛盾。在与`avg()`或`sum()`函数一起使用时，`group by` 能发挥最大作用。  
【例】查询books表，按照type进行分组，求每类图书的平均价格

select bookname,avg(price),type from books group by type;

## ⑤ distinct 在结果中去除重复的行 ##

【例】查询 `books` 表，并在结果中去掉类型字段 type中的重复数据

select distinct type from books;

## ⑥ order by 对结果排序 ##

使用`order by` 可以对查询的结果进行升序和降序`(desc)`排序，默认情况下，`order by` 按升序输出结果。降序用`desc`实现。  
对含有 `null` 值的列进行排序时，如果是按升序排列，`null` 值将出现在最前面，如果是按降序排序，`null` 值将出现在最后。  
【例】查询books表中的所有信息，按照id进行降序排序，并且只显示5条记录

select * from books order by id desc limit 5;

## ⑦ like 模糊查询 ##

`like` 属于较常用的比较运算符，它可以实现模糊查询。有两种通配符：“`%`”和下划线“`_`” 。“%”可以匹配一个或多个字符，而“\_”只匹配一个字符。  
【例】查找所有第二个字母是“m”的图书。

select * from books where bookname like('_m%');

## ⑧ concat 联合多列 ##

使用 `concat` 函数可以联合多个字段，构成一个总的字符串。  
【例】把`books`表中的书名`(bookname)`和价格`(price)`合并到一起，构成一个新的字符串。

select id,concat （bookname，“:”,price）as  info,type from books;

其中合并后的字段名为`concat`函数形成的表达式 “`concat （bookname，“:”,price）`”，看上去很复杂，通过`as`关键字给合并字段取一个别名，这样看上去就很清晰。

## ⑨ limit 限定结果行数 ##

`limit` 可以对查询结果的记录条数进行限定，控制它输出的行数。  
【例】查询 `books` 表，按照图书价格降序排列，显示`5`条记录。

select * from books order by price desc limit 5;

使用`limit` 还可以从查询结果的中间部分取值。首先要定义两个参数，参数1是开始读取第一条记录的编号（在查询结果中，第一个结果的记录编号是0，而不是1）；参数2是要查询记录的个数。  
【例】查询 `books` 表，从编号1开始（即从第2条记录），查询4个记录。

select * from books where id limit 1,4;

## ⑩ 使用函数和表达式 ##

在MySQL中，还可以使用表达式来计算各列的值，作为输出结果。表达式还可以包含一些函数。  
【例】计算`books`表中各类图书的总价格。

select sum(price) as total,type from books group by type;

在MySQL数据库进行操作时，有时需要对数据库中的记录进行统计，例如求平均值、最小值、最大值等，这时可以使用MySQL中的统计函数，常用统计函数如下表所示。

<table> 
 <thead> 
  <tr> 
   <th>名称</th> 
   <th>说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>avg（字段名）</td> 
   <td>获取指定列的平均值</td> 
  </tr> 
  <tr> 
   <td>count（字段名）</td> 
   <td>如指定了一个字段，则会统计出该字段中的非空记录。如在前面增加DISTINCT，则会统计不同值的记录，相同的值当做一条记录。如使用count（*）则统计包含空值的所有记录数</td> 
  </tr> 
  <tr> 
   <td>min（字段名）</td> 
   <td>获取指定字段的最小值</td> 
  </tr> 
  <tr> 
   <td>max（字段名）</td> 
   <td>获取指定字段的最大值</td> 
  </tr> 
  <tr> 
   <td>std（字段名）</td> 
   <td>指定字段的标准背离值</td> 
  </tr> 
  <tr> 
   <td>stdtev（字段名）</td> 
   <td>与STD相同</td> 
  </tr> 
  <tr> 
   <td>sum（字段名）</td> 
   <td>指定字段所有记录的总合</td> 
  </tr> 
 </tbody> 
</table>

不仅可以使用函数，也可使用算术运算符、字符串运算符以及逻辑运算符来构成表达式。  
【例】计算图书打八折后的价格

select *,(price * 0.8) as '80%' from books;

# （三）修改记录 UPDATE #

要执行修改的操作可以使用`update`语句，语法如下：

update 数据表名set column_name = new_value1, column_name2 = new_value2, ···where condition

`set` 指出要修改的列和它们给定的值，`where`是可选的，如果给出它将指导记录中哪行应该被更新，否则，所有的记录行都将被更新。  
【例】将表中用户名为`mikasa8`的管理员密码 `111` 修改为`123456`，如图。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pa2FzYTg_size_16_color_FFFFFF_t_70]

注意：更新时一定要保证`where`子句的正确性，一旦`where`子句出错，将会破坏所有改变的数据。

# （四）删除记录 DELETE #

在数据库中，有些数据已经失去意义或者错误时就需要将他们删除，此时可以使用`delete`语句：

delete from 数据表名 where 条件

注意：该语句在执行过程中，如果没有指定`where`条件，将删除所有记录；如果指定了`where`条件，将按照指定的条件进行删除。  
在实际的应用中，执行删除操作时，执行删除的条件一般应该为数据的`id`，而不是具体某个字段值，这样可以避免一些不必要的错误发生。

[20201223100338814.png]: /images/20221120/6a65b8fac9ce4bcea718f31b2058c143.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L01pa2FzYTg_size_16_color_FFFFFF_t_70]: /images/20221120/e50db9afd9d141fe8e0bac64070c2942.png