MySQL数据库——数据库CRUD之基本DML增删改表操作及DQL查表操作

Winter_world

发布于 2020-09-25 10:57:40

9880

发布于 2020-09-25 10:57:40

文章被收录于专栏：技术小屋-未分类技术小屋-未分类

1 DML增删改表中的数据（重要）

1.1 添加数据

语法：insert into 表名（列名1,列名2,...,列名n）values(值1,值2,...,值n);
注意事项：1）列名和值要一一对应； 2）若表名后不定义列名，则默认给所有列添加值； 3）除了数字类型，其他类型需要使用引号（单双均可）引起来；

1.2 删除数据

语法：delete from 表名[where 条件]；
注意事项：1）若不加条件，则删除表中所有记录； 2）若要删除所有记录，有两种方法： --delete from 表名；但不推荐使用，效率低，有多少记录就会执行多少次操作； --truncate table 表名; 推荐使用，效率高，先删除表，在创建一张一样的表；

1.3 修改数据

语法：update 表名 set 列名1=值1, 列名2=值2,...,[where 条件]；
注意事项：1）若不加任何条件，则将表中记录全部修改；--很危险的操作

2 DQL查询表中的记录（重要）

2.1 语法

select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件 order by 排序 limit 分页限定

2.2 基础查询

1）多个字段的查询

语法：select 字段名1,字段名2,...,字段名n from 表名；
注意：要查询所有字段，可以使用 * 代替字段列表；

2）去除重复：加 distinct 关键字

3）计算列：一般可以使用四则运算来计算列的值，一般只会进行数值型的计算； ifnull(表达式1，表达式2)，表达式1表示哪个字段需要判断是否为null，表达式2表示如果该字段为null后的替换值；

4）起别名：as关键字或者省略as，直接在字段名后空格 +新名字

【举例】：

CREATE TABLE student (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','
杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩
',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99,99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65);

SELECT *FROM student;

SELECT NAME, age FROM student;
SELECT address FROM student;
-- 去除重复结果集
SELECT DISTINCT address FROM student;
-- 计算math 和english的分数之和
SELECT NAME,math,english,math+english FROM student;
-- 如果有NULL参与的运算，计算结果都为NULL
SELECT NAME,math,english,math+ IFNULL(english,0) FROM student;
-- 起别名
SELECT NAME,math,english,math+ IFNULL(english,0) AS 总分 FROM student;
SELECT NAME,math 数学,english 英语,math+ IFNULL(english,0) 总分 FROM student;

2.3 条件查询

1）where子句后跟条件

2）运算符：

>、 <、 <=、 >=、 =、 <>
BETWEEN...AND
IN（集合）
LIKE
IS NULL
and 或 &&
or 或 ||
not 或 |

【举例】：条件语句的使用

-- 查询年龄大于等于20岁
SELECT *FROM student WHERE age >= 20;
-- 查询年龄等于20岁
SELECT *FROM student WHERE age = 20;
-- 查询年龄不等于20岁
SELECT *FROM student WHERE age != 20;
SELECT *FROM student WHERE age <> 20;
-- 查询年龄大于等于20，小于等于30
SELECT *FROM student WHERE age >= 20 AND age <= 30;
SELECT *FROM student WHERE age BETWEEN 20 AND 30;
-- 查询20岁、19岁、25岁的人员信息
SELECT *FROM student WHERE age=20 OR age=19 OR age=25;
SELECT *FROM student WHERE age IN(20,19,25);
-- 查询 英语成绩为空的
SELECT *FROM student WHERE english = NULL; -- NULL不能使用=或!= 进行判断
SELECT *FROM student WHERE english IS NULL; 
-- 查询 英语成绩不为空的
SELECT *FROM student WHERE english IS NOT NULL;

3）LIKE模糊查询

模糊查询，首先了解占位符的概念：_表示单个任意字符，%表示多个任意字符。

【举例】：模糊查询的使用

-- 查询班级中姓马的人
SELECT *FROM student WHERE NAME LIKE "马%";
-- 查询第二个字是化的人
SELECT *FROM student WHERE NAME LIKE "_化%";
-- 查询姓名是三个字的人
SELECT *FROM student WHERE NAME LIKE "___";
-- 查询姓名中包含德的人
SELECT *FROM student WHERE NAME LIKE "%德%";

2.4 排序查询

语法：order by 子句，如order by 字段1,排序方式1,字段2,排序方式2...
排序方式：ASC，升序，默认的； DESC：降序；
注意：若有多个排序条件，当前面的条件值一样时，才会判断第二条件；

SELECT *FROM student ORDER BY math ASC; -- 默认排序方式为升序 
SELECT *FROM student ORDER BY math DESC;

-- 按照数学成绩升序排名，若成绩一样，则按照英语成绩排名
SELECT *FROM student ORDER BY math ASC,english ASC;

2.5 聚合函数

聚合函数是将一列数据作为一个整体，进行纵向的计算，MySQL中的常用聚合函数如下：

count：计算个数 1）一般选择非空的列：主键； 2）count(*)；
max：计算最大值
min：计算最小值
sum：计算和
avg：计算平均值

-- 计算个数
SELECT COUNT(english)FROM student;
SELECT COUNT(IFNULL(english,0))FROM student;
SELECT COUNT(id)FROM student;
-- 计算最大值,最小值
SELECT MAX(math)FROM student;
SELECT MIN(math)FROM student;
-- 计算累加和
SELECT SUM(english)FROM student;
-- 计算平均值
SELECT AVG(english)FROM student;

注意：聚合函数的计算排除NULL值；解决方案：1）选择不包含非空的列进行计算； 2）IFNULL 函数；

2.6 分组查询

语法：group by 分组字段
注意：1）分组之后查询的字段：分组字段、聚合函数，不能写其他字段（没有意义）； 2）where和having的区别？ *where在分组之前进行限定，若不满足条件，不参与分组，having在分组之后进行限定，若不满足结果，不会被查询出来； *where后不可以跟聚合函数，having可以进行聚合函数的判断；

【举例】：分组查询的使用

-- 按照性别分组，查询男、女同学的平均分,人数
SELECT sex, AVG(math),COUNT(id) FROM student GROUP BY sex;

-- 按照性别分组，查询男、女同学的平均分,人数，分数低于70的不参与分组
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex;

-- 按照性别分组，查询男、女同学的平均分,人数，分数低于70的不参与分组，分组之后人数要大于2人
SELECT sex, AVG(math),COUNT(id) FROM student WHERE math > 70 GROUP BY sex HAVING COUNT(id)>2;
-- 另一写法，写别名
SELECT sex, AVG(math),COUNT(id) 人数 FROM student WHERE math > 70 GROUP BY sex HAVING 人数>2;

2.7 分页查询

语法：limit 开始的索引，每页查询的条数；
公式：开始的索引 = (当前页码-1)*每页显示的条数；
注意：limit是一个MySQL的“方言”，不同数据库实现的方式不一样；

-- 每页显示3条记录
SELECT * FROM student LIMIT 0,3; -- 第1页
SELECT * FROM student LIMIT 3,3; -- 第2页
-- 公式：开始的索引 = (当前页码-1)*每页显示的条数；
SELECT * FROM student LIMIT 6,3; -- 第3页

本文为博主原创文章，转载请注明出处！

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-06-19 ，如有侵权请联系 cloudcommunity@tencent.com 删除

count