MySQL单表操作-优快云博客

在MySQL中，可以通过SQL语句中的DML语⾔操作数据库中表对象，包括：

使⽤INSERT实现数据的插⼊
UPDATE实现数据的更新
使⽤DELETE实现数据的删除
使⽤SELECT查询数据

为了实现表中数据的增，删，改，插入，先在命令⾏创建⼀个表如下：

mysql> create table stu( 
-> sname varchar(20),
-> age tinyint,
-> score tinyint unsigned
-> );

这张 stu 表包含三个字段：sname (姓名)，age (年龄)，和 score (分数)。

一，插入数据

给表中添加数据，语法格式如下：

INSERT INTO table_name(field1,field2,[,...]) values(value1,value2), (value3,value4),...;

使用注意：

插⼊数据时列名列表要与后边的值⼀⼀对应，插⼊的值与指定字段⼀致
可以省略字段参数
可以⼀次插⼊单⾏记录，也可以⼀次插⼊多⾏记录

代码示例：
插入单行数据：

mysql> INSERT INTO stu (sname, age, score) VALUES ('张三', 20, 88);

一次性插入多行数据：

mysql> INSERT INTO stu (sname, age, score) VALUES
    -> ('李四', 22, 92),
    -> ('王五', 19, 76),
    -> ('赵六', 22, 88);

二，更新数据

给表中更新数据，语法格式如下：

UPDATE table_name SET field1 = value1, field2 = value2, ... [, WHERE condition];

使用注意：

SET 子句用于指定要更新的字段和新值。
建议始终使用 WHERE 子句来指定更新的范围，否则将更新表中的所有行。

代码示例：

1.更新特定记录：将名为“张三”的同学的分数更新为90。

mysql> UPDATE stu SET score = 90 WHERE sname = '张三';

2.同时更新多个字段：将名为“王五”的同学的年龄和分数都进行修改。

mysql> UPDATE stu SET age = 20, score = 80 WHERE sname = '王五';

三，删除数据

删除表中数据，语法格式如下：

DELETE FROM 表名 [ WHERE condition ];

使用注意：

与 UPDATE 类似，谨慎使用 DELETE 语句。如果省略 WHERE 子句，将会删除表中的所有数据。

代码示例：

1.删除特定记录：删除名为“马七”的同学的记录。

mysql> DELETE FROM stu WHERE sname = '马七';

2.删除所有分数低于80的记录：

mysql> DELETE FROM stu WHERE score < 80;

四，查询操作

查询数据记录就是从数据库表中获取的数据记录，这种操作时mysql中最基本的操

作，使⽤最频繁的的操作，mysql提供了很多不同的查询⽅法，以满⾜不同的需

求。

select语法完整格式：

SELECT [ALL | DISTINCT]

{ * | table.* | [ table.field1 [ as alias1] [, table.field2 [as alias2]][, …]] }

FROM table_name [ as table_ alias ]

[ left|out|inner join table_name2 ] #联合查询

[ WHERE … ] #指定结果需满⾜的条件

[ GROUP BY …] #指定结果按照哪⼏个字段来分组

[ HAVING …] #过滤分组的记录必须满⾜的次要条件

[ ORDER BY… ] #指定查询记录按⼀个或者多个条件排序

[ LIMIT { [ offset,] row_count | row_count OFFSET offset }] ; #指定查询的记录从哪条⾄哪条

1）不加条件的单表查询

语法格式：SELECT field1,field2,...fieldh FROM table_name；

代码示例：

查询 stu 表中的所有数据：

mysql> SELECT * FROM stu;

只查询姓名和分数：

mysql> SELECT sname, score FROM stu;

2）单表中的条件查询

语法格式：SELECT field1,field2,...fieldh FROM table_name where 条件表达式；

使用 WHERE 子句过滤出满足特定条件的记录。

代码示例：

查询年龄为22岁的学生：

mysql> SELECT * FROM stu WHERE age = 22;

3）带四则运算的查询

mysql在执⾏简单查询时，有时需要实现四则运算即加，减，乘，除，求余（+，-，*，/,%）。

代码示例：

假设所有学生的分数都增加5分，查看提分后的结果（这不会修改表中的实际数据）：
```
mysql> SELECT sname, score + 5 AS new_score FROM stu;
```

4）带排序功能的查询

如果要设置查询的结果顺序，可以使⽤ORDER BY 实现，其基本格式为：

SELECT field1,field2,...fieldh FROM table_name where CONDITION ORDER BY field1 [ASC|DESC][,field2 [ASC|DESC],]；

使用 ORDER BY 对查询结果进行排序，ASC 为升序（默认），DESC 为降序。

代码示例：

按照分数从高到低排序：

mysql> SELECT * FROM stu ORDER BY score DESC;

5）限制数据记录条数的查询

通过LIMIT关键字可以限制查询结果的数量，其语法形式为：

SELECT field1,field2,...fieldh FROM table_name WHERE CONDITION LIMIT OFFSET_START,ROW_COUNT;

使用 LIMIT 关键字限制返回的记录数量，常用于分页查询。

代码示例：

查询分数最高的两条记录：

mysql> SELECT * FROM stu ORDER BY score DESC LIMIT 2;

6）带统计函数的结果查询

常⽤的语法形式：

语法形式：SELECT function(field1) ，...FROM table_name where condition;

count()函数：统计表中记录的条数
AVG()函数：计算字段值的平均年龄，统计字段中会⾃动忽略NULL值。
SUM()函数：统计字段值的总和，在具体统计时将忽略NULL值。
MAX()函数：查询字段值的最大值
MIN()函数：查询字段值的最小值

代码示例：

mysql> SELECT COUNT(*) AS total_students, AVG(score) AS average_score, MAX(age) AS max_age, MIN(age) as min_age FROM stu;

7）分组查询

在 MySQL 中，GROUP BY 关键字可以根据⼀个或多个字段对查询结果进行分组，通常与聚合函数一起使用。使用 GROUP BY 关键字的语法格式如下：

GROUP BY <字段名>

其中，“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。分组所依据的字段值⼀定要有重复性，否则分组就失去意义。

与having⼀起使用

在mysql中，如果想实现对分组进⾏条件限制，不能通过where 来实现，因为该关键字主要⽤来实现条件限制数据记录，因此mysql提供了having关键字实现条件限制分组数据。

注意：

where是判断数据从磁盘读⼊内存的时候，符合条件放⼊内存，不符合忽略，having查询数据库的结果返回之后进⾏过滤 where后面不能使用聚合函数，但是having可以，having子句中可以使用字段别名, 而where不能使⽤用。

代码示例：

按年龄分组，统计每个年龄段的学生人数：

mysql> SELECT age, COUNT(*) AS num_of_students FROM stu GROUP BY age;

当与having一起使用时，查询平均分超过90分的年龄组：

mysql> SELECT age, AVG(score) as avg_s FROM stu GROUP BY age HAVING avg_s > 90;

五、其他mysql函数简介

LOCALTIME（）：返回当前的日期与时间类似的还有now()
CURRENT_TIME（）返回当前时间
CURRENT_DATE() 返回当前日期
ABS(X) 返回x的绝对值
LENGTH(s) 计算字符串长度函数，返回字符串的字节⻓度
CONCAT(s1,s2...,sn) 合并字符串函数，返回结果为连接参数产⽣的字符串，
参数可以是⼀个或多个
UPPER(s) 将字符串转换为大写
LOWER(s) 将字符串转换为小写
TRIM(s) 去掉字符串 s 开始和结尾处的空格
substring()是专门用来对字符串进⾏切分的函数，注意下标索引从1开始
MD5(str) 为字符串算出⼀个 MD5 128比特校验和
MONTH 获取指定日期中的⽉份
YEAR 获取年份，返回值范围是 1970 -- 2069
VERSION()函数返回数据库的版本号；

更多函数可参考mysql官⽹

https://dev.mysql.com/doc/refman/8.0/en/built-in-function-reference.html