SQL语句对表数据的操作

数据的操作

表示例 新增数据 整行插入 指定列中插入数据 插入多条数据 整行插入多条数据 指定列中插入多条数据 修改数据 删除数据 查询数据 查询整个表的数据 查询指定字段数据 查询指定字段数据并起别名 查询指定字段数据并去重 where子句 比较运算符 逻辑运算符 模糊查询 范围查询 空与非空判断 常用聚合函数 count()求总数 sum()求和 avg()求平均值 max()求最大值 min()求最小值 自增值问题 物理删除与逻辑删除

表示例

数据的操作以这个表为例子
表结构

新增数据

整行插入

INSERT INTO 表名 VALUES(值1,值2,值3...);

代码示例

INSERT INTO tb1 VALUES(1, '张三', 18, '2002-01-01', '男', 0);

注意: 在整行插入时,即使字段有默认值或自增值,也要赋值,否则会报错

指定列中插入数据

INSERT INTO 表名 (字段1,字段2,字段3...) VALUES(值1,值2,值3...);
代码示例

INSERT INTO tb1 (name, age, BIRTH) VALUES ('李四', 18, '2002-02-02');

注意: 所有指定列必须用括号括起来

插入多条数据

整行插入多条数据

INSERT INTO 表名 VALUES(值1,值2,值3…),(值1,值2,值3…),···,(值1,值2,值3…);
代码示例

INSERT INTO tb1 VALUES(5, 'xx', 18, '2002-01-01', '男', 0),(6, 'xx', 18, '2002-01-01', '男', 0);

指定列中插入多条数据

INSERT INTO 表名 (字段1,字段2,字段3...) VALUES(值1,值2,值3...),(值1,值2,值3...),···,(值1,值2,值3...);
代码示例

INSERT INTO tb1 (name, age, BIRTH) VALUES ('N1', 18, '2002-01-01'),('N2', 18, '2002-01-01');

注意: 插入多条数据需要用逗号分隔开

修改数据

UPDATE 表名 SET 字段1=新值,字段2=新值 [WHERE 条件];
代码示例

将所有性别不为‘男’的人的性别都改为男
UPDATE tb1 SET gender=2 where gender != '男';

注意: 因为我添加的字段gender为枚举型,所以在赋值的时候我使用了索引(1是保密,2是男,3是女),下标从1开始,当然也可以直接赋值gender='男'

删除数据

DELETE FROM 表名 [WHERE 条件];
代码示例

删除所有数据
DELETE FROM tb1;
删除名字为‘N2’的数据
DELETE FROM tb1 WHERE name='N2';

查询数据

查询整个表的数据

SELECT * FROM 表名
代码示例

SELECT * FROM tb1;

查询指定字段数据

SELECT 字段1,字段2... FROM 表名
代码示例

SELECT name,gender FROM tb1;

注意: 在插入数据时字段要用括号括起来,但是在查询的时候如果是一个字段时,可以用括号括起来,但是在查询多个字段时不能添加括号

查询指定字段数据并起别名

SELECT 字段1 as 别名,字段2 as 别名... FROM 表名
代码示例

SELECT name as 姓名,gender 年龄 FROM tb1;

注意: as可以省略,省略时默认是as

查询指定字段数据并去重

SELECT DISTINCT 字段1,字段2 FROM 表名;
代码示例

查询字段name数据并去重
SELECT DISTINCT name FROM tb1;
查询字段name和age数据并去重
SELECT DISTINCT name,age FROM tb1;

注意: 如果是查询多个字段去重时,必须是行与行之间所有字段的值都重复时才能去重,否则只有一个字段的值是重复的话是不去重的

where子句

where子句通常结合增删改查使用,用于做筛选的条件
SELECT DISTINCT 字段1,字段2 FROM 表名;
代码示例

查询id为1的数据
SELECT * FROM tb1 WHERE id=1;

不仅如此,还经常结合运算符使用

比较运算符

运算符 描述
= 等于
> 大于
>= 大于等于
< 小于
<= 小于等于
!=或<> 不等于

逻辑运算符

运算符 描述
x and y 当同时满足x,y时,值为True
x or b 当满足x或y其中一个时,值为True
not x 对x取反

模糊查询

like关键字用来进行模糊查询,结合%和_使用

% 表示多个任意字符 _ 表示一个任意字符

代码示例

查询名字以‘张’开头的数据
SELECT * FROM tb1 WHERE name LIKE '张%';
查询名字以‘张’开头且名字长度为2的数据
SELECT * FROM tb1 WHERE name LIKE '张_';

范围查询

关键字 描述
in 表示在一个非连续的范围内
between…and… 表示在一个连续的范围内

代码示例

查询id是1或3或4的数据
SELECT * FROM tb1 WHERE id in (1,3,4);
查询id在2到4之间的数据
SELECT * FROM tb1 WHERE id BETWEEN 2 and 4;

空与非空判断

关键字 描述
is null 判断空
is not null 判断非空

代码示例

查询年龄为空的数据
SELECT * FROM tb1 WHERE age is NULL;

常用聚合函数

count()求总数

代码示例

求表里总人数
SELECT COUNT(*) FROM tb1;

sum()求和

代码示例

求所有人的年龄和
SELECT SUM(age) FROM tb1;

avg()求平均值

代码示例

求平均年龄
SELECT AVG(age) FROM tb1;
注意: AVG() 函数结果默认保留四位小数,如果想要指定保留几位小数,可以使用round()函数,例如round(AVG(age), 2)保留2位小数

max()求最大值

代码示例

查询最大的年龄
SELECT MAX(age) FROM tb1;

min()求最小值

查询最小的年龄
SELECT MIN(age) FROM tb1;

自增值问题

如果我们删除第五条记录时,下次增加记录时自增值会继续从5递增到6,而不是5
解决方法
可以手动重置自增值

ALTER TABLE tb1 AUTO_INCREMENT=数字

或者可以重启数据库

物理删除与逻辑删除

如果我们使用DELETE FROM 表名 [WHERE 条件];这条语句的话,那么记录将永远消失,这是物理删除 如果想要转为逻辑删除,可以添加字段is_del,默认值为0,0为未删除,1为已删除
来源url
栏目