数据库学习笔记03 数据表增删改查+方法

最新推荐文章于 2024-08-29 17:00:41 发布

guyueCT

最新推荐文章于 2024-08-29 17:00:41 发布

阅读量211

点赞数 1

分类专栏： Java全栈02 数据库学习笔记文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/qq_40670344/article/details/115657626

版权

Java全栈02 数据库学习笔记专栏收录该内容

5 篇文章 0 订阅

订阅专栏

这篇博客详细介绍了MySQL数据库的基本操作，包括插入数据、删除数据、更新数据和查询数据的多种方法。重点讲解了查询数据的技巧，如WHERE条件、ORDER BY排序、聚合函数、分组、LIMIT分页、LIKE模糊查询、子查询、关联查询及联合查询。还提到了MySQL的一些常用方法，如聚合函数和时间处理函数。

摘要由CSDN通过智能技术生成

一、增--插入数据

如果是插入所有字段的值，并且值的顺序和表中的字段顺序一致，则字段列表可以省略不写。

部分插入时，字段名不能省略。

# 插入数据
INSERT INTO tb_student (CLAZZ_ID,STU_NAME)VALUES (1,'张三');
# 批量插入数据
INSERT INTO tb_student(CLAZZ_ID,STU_NAME)VALUES(1,'aaa'),(1,'bbb'),(1,'ccc');
INSERT INTO tb_clazz(CLAZZ_NAME,STU_NUMBER)VALUES('2class',20),('3class',20),('4class',21);
# 通过查询插入数据--移库、移表
INSERT INTO tb_student2(CLAZZ_ID,STU_NAME) SELECT CLAZZ_ID,STU_NAME FROM tb_student;

二、删--删除数据

# delete--删除,删除之后的主键不会在重复--主键自增
DELETE FROM TB_USER; #删除整个表
DELETE FROM TB_USER WHERE ROW_ID=1;

# 主从表--学生表clazz_id关联班级表row_id
# 主表 班级表
# 从表 学生表
# 被关联的表不能随便删除信息
# 如：班级表不能删除被学生表关联的班级的信息
# 物理删除
DELETE FROM tb_student WHERE ROW_ID=2;
# 逻辑删除
UPDATE tb_student SET ACTIVE_FLAG=0 WHERE ROW_ID=2;
# 使用逻辑删除的库，每个查询语句都要增加判断条件ACTIVE_FLAG=1

三、改--修改数据

# 修改数据
UPDATE tb_student SET STU_AGE=0; #设置所有年龄为0
UPDATE tb_student SET STU_AGE=STU_AGE+1; #年龄自增1
# CURRENT_DATE()--获得当前时间
UPDATE tb_student SET STU_AGE=12,CREATE_DATE=CURRENT_DATE() WHERE STU_NAME='张三';
# CONCAT()--字符串连接
UPDATE tb_student SET STU_NAME=CONCAT(STU_NAME,'a')WHERE STU_NAME='张三';

四、查--查询数据

4.1 简单查询数据

# 查询
# 下列两个查询语句的作用相同，但是语句1比语句2多执行一步：查询表的全部字段的内容
# 故：开发中，必须使用后者
SELECT * FROM tb_student;
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE,STU_SEX,STU_BIRTHDAY,STU_INFO,CREATE_DATE FROM tb_student;

# 去重--存在重名学生
SELECT DISTINCT STU_NAME FROM MS_STUDENT;
# 多字段去重--综合考虑多个字段，只要有一个字段不相同，即为不同记录
SELECT DISTINCT CLASS_ID,STU_NAME FROM MS_STUDENT;

# 别名-- AS 可以省略
SELECT STU_NAME AS STU_NAME1 FROM MS_STUDENT;
SELECT STU_NAME STU_NAME1 FROM MS_STUDENT;
# 常量起别名
SELECT STU_NAME,'张三' '张三三' FROM  MS_STUDENT;

4.2 查询数据 where 条件

与：AND 或：OR

大于：> 小于：< 大于等于：>= 小于等于：<= 不等于：<> 、 !=

between a1 and a2（等价于a1 >= and <= a2) ：数值范围在 [a1,a2] 的数据

判断null值：is null is not null ，不能使用 =null

in (值列表)

# select + where 
# 查询年龄大于10岁并且是1班的学生
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student WHERE STU_AGE>10 AND CLAZZ_ID=1;
# 查询年龄大于10岁或者是1班的学生
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student WHERE STU_AGE>10 OR CLAZZ_ID=1;
# 查询年龄不是12岁的学生 两个写法效果相同
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student WHERE STU_AGE!=12;
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student WHERE STU_AGE<>12;
# 查询年龄5-20岁的学生
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student WHERE STU_AGE>=5 AND STU_AGE<=20;
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student WHERE STU_AGE BETWEEN 5 AND 20;
# null值的判断：只能使用is null 或者 is not null ，不能使用 =null
# 查询为null的记录
SELECT ROW_ID,CLAZZ_ID,STU_NAME,CREATE_DATE FROM tb_student WHERE CREATE_DATE IS NULL;
SELECT ROW_ID,CLAZZ_ID,STU_NAME,CREATE_DATE FROM tb_student WHERE CREATE_DATE IS NOT NULL;

4.3 查询数据 order by 排序

order by 排序关键字 [ asc：升序默认 | desc：降序 ]

多条件排序：多个排序条件用 '','' 隔开；排在前面的条件优先级高。

# select + order by 
# 默认升序排序： ORDER BY 排序关键字 (ASC:默认升序)
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student ORDER BY STU_AGE;
# 设置倒序排序： ORDER BY 排序关键字 DESC
SELECT ROW_ID,CLAZZ_ID,STU_NAME,STU_AGE FROM tb_student ORDER BY STU_AGE DESC;
# 多条件排序
SELECT * FROM tb_student ORDER BY STU_AGE DESC,STU_SEX ASC;

4.4 查询数据聚合函数

sum() --求和

avg() --求平均值

max() --最大值

min() --最小值

count() --统计数量

# 聚合函数
# 统计class_id=1的学生数量 , 统计主键--ID更快
SELECT COUNT(ID) FROM MS_STUDENT WHERE CLASS_ID=1;
# 查看最大年龄
SELECT MAX(STU_AGE) AS max_age FROM MS_STUDENT;
# 查看年龄总和
SELECT SUM(STU_AGE) sum_age FROM MS_STUDENT;
# 查看平均年龄
SELECT SUM(STU_AGE)/COUNT(ID) avg_age FROM MS_STUDENT;
SELECT AVG(STU_AGE) avg_age FROM MS_STUDENT;

# 统计学生数--去重统计
SELECT  COUNT(DISTINCT STU_ID) FROM MS_STUDENT_SUBJECT;

4.5 查询数据--分组

1）语法格式：

group by 分组的依据

having 对分组后的数据进行条件判断

# 分组--GROUP BY 关键字
# 查看每一个学生的总成绩:先通过GROUP BY STU_ID分组，再统计分组之后的数据
SELECT STU_ID,SUM(SCORE) FROM MS_STUDENT_SUBJECT GROUP BY STU_ID;
# 统计课程平均分
SELECT SBJ_ID,AVG(SCORE) FROM MS_STUDENT_SUBJECT GROUP BY SBJ_ID;
# 统计出每个班级的学生数量
SELECT CLASS_ID,COUNT(ID) FROM MS_STUDENT GROUP BY CLASS_ID;

# 查看每一个学生的平均成绩，且成绩大于75分
SELECT STU_ID,AVG(SCORE) AS AVG_SCORE FROM MS_STUDENT_SUBJECT 
GROUP BY STU_ID
HAVING AVG_SCORE>75;  # 分组之后的再筛选--使用having

2）比较where与having：

where子句：对表的字段的值进行条件判断。

having子句：对分组 (group by) 后的数据进行条件判断。

4.6 数据查询--限定

限定--分页显示数据，用于大量数据显示的场景。

MySQL分页查询使用limit，不同数据库使用方法不同。

# 限定--分页显示数据，用于大量数据显示的场景
# 查询数据限定下标从0开始，显示5条数据
# 页开始数据的下标：(页号-1)*每页显示数据条数
SELECT * FROM MS_STUDENT LIMIT 0,5; #第一页
SELECT * FROM MS_STUDENT LIMIT 5,5; #第二页
SELECT * FROM MS_STUDENT LIMIT 10,5; #第三页

4.7 模糊查询--like 、 in

模糊查询尽量不使用，效率不高。

like + 通配符：

% (百分号)--代表一个或多个字符的通配符。

_ (下划线)--代表一个字符的通配符。

in (值,值,值...)

# 模糊查询
# like 模糊查询  '%'--百分号：匹配一个或多个任意字符
# '_'--下划线：匹配一个任意字符
# 查询所有姓张的学生，使用通配符：%
SELECT * FROM MS_STUDENT WHERE STU_NAME LIKE '张%';
# 查询所有名为 ‘张某某’ 的学生，使用两个下划线
SELECT * FROM MS_STUDENT WHERE STU_NAME LIKE '张__';
# 查询出班级名称中带07信息的数据
SELECT * FROM MS_CLASS WHERE CLASS_NAME LIKE '%07%';

# 查看年龄=18，20，22，24, IN (值,值,值...)
SELECT * FROM MS_STUDENT WHERE STU_AGE=18 OR STU_AGE=20 OR STU_AGE=22 OR STU_AGE=24;
SELECT * FROM MS_STUDENT WHERE STU_AGE IN (18,20,22,24);

4.8 多表查询--子查询

子查询：嵌套查询，一个查询语句的结果是另一个查询语句的条件的值。

子查询的语句必须放在小括号内。--小括号内的内容优先执行

子查询的结果处理：

结果只有一个值，则主查询使用 ''='' 来判断。

结果有多个值，则主查询使用 ''in'' 来判断。

#3.查询显示不存在雇员的所有部门号。
SELECT DEPTNO FROM DEPT WHERE DEPTNO NOT IN
(SELECT DEPTNO FROM EMP GROUP BY DEPTNO); #子查询

4.9 多表查询--关联查询

关联：两个或两个以上的表连接成一个数据源。常用： left join 表名 on 关联条件

注意：关联之后，并不是形成一个新的数据表，而只是一种"内存形态"。

实例：

# 关联查询
# 左关联，一般从表左关联主表
# 所有数据以左表的信息为主
# 查看学生的班级名称
SELECT
  S.ID,
  S.STU_NAME,
  C.CLASS_NAME
FROM
  MS_STUDENT S
  LEFT JOIN MS_CLASS C
    ON S.CLASS_ID = C.ID #条件
    
# 右关联，所有数据以右表的信息为主
SELECT
  S.ID,
  S.STU_NAME,
  C.CLASS_NAME
FROM
  MS_STUDENT S
  RIGHT JOIN MS_CLASS C
    ON S.CLASS_ID = C.ID #条件

# inner join 查询得到左右表都存在的数据
SELECT
  S.*,
  C.*
FROM
  MS_STUDENT S
  INNER JOIN MS_CLASS C
    ON S.CLASS_ID = C.ID #条件
    
   
# full join --mysql不支持 
SELECT
  S.*,
  C.*
FROM
  MS_STUDENT S
  FULL JOIN MS_CLASS C
    ON S.CLASS_ID = C.ID #条件
    
 # 多表关联
 # 查看学生id、学生姓名、班级名、课程id、课程名、课程分数
 # 关联超过三个join则效率太低
SELECT
  SS.STU_ID,
  ST.STU_NAME,
  CC.CLASS_NAME,
  SS.SBJ_ID,
  SU.SBJ_NAME,
  SS.SCORE
FROM
  MS_STUDENT_SUBJECT SS
  LEFT JOIN MS_STUDENT ST
    ON SS.STU_ID = ST.ID
  LEFT JOIN MS_SUBJECT SU
    ON SS.SBJ_ID = SU.ID
  LEFT JOIN MS_CLASS CC
    ON ST.CLASS_ID = CC.ID

4.10 联合查询

#联合查询--UNION
SELECT CLASS_NAME FROM MS_CLASS 
UNION # 自动去重
SELECT CLASS_NAME FROM MS_CLASS 

SELECT CLASS_NAME FROM MS_CLASS 
UNION ALL# 不去重
SELECT CLASS_NAME FROM MS_CLASS

五、MySQL的方法

常用方法：

聚合函数：count()、max()、min()、avg()、sum()

rand()：随机数[0,1)生成

concat(字符串列表) ：字符串连接

current_data(): 当前日期

current_time(): 当前时间

current_timestamp()：当前时间戳

adddate() : 时间计算等

unix_timestamp(日期)：将日期转成时间戳

from_timestamp(时间戳)：将时间戳转成日期

另外还有更多MySQL方法

guyueCT

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库学习笔记03 数据表增删改查+方法

一、增--插入数据如果是插入所有字段的值，并且值的顺序和表中的字段顺序一致，则字段列表可以省略不写。部分插入时，字段名不能省略。# 插入数据INSERT INTO tb_student (CLAZZ_ID,STU_NAME)VALUES (1,'张三');# 批量插入数据INSERT INTO tb_student(CLAZZ_ID,STU_NAME)VALUES(1,'aaa'),(1,'bbb'),(1,'ccc');INSERT INTO tb_clazz(CLAZZ_...
复制链接

扫一扫