MySQL之增删改查

前言：以下是MySQL最基本的增删改查语句，很多IT工作者都必须要会的命令，也是IT行业面试最常考的知识点，由于是入门级基础命令，所有所有操作都建立在单表上，未涉及多表操作。

前提：在进行“增删改查”的操作之前，先建立一个包含数据表student的数据库

1、“增”——添加数据

1.1 为表中所有字段添加数据

1.1.1 INSERT 语句中指定所有字段名

语法：INSERT INTO 表名（字段名1，字段名2，…）

　　　　　　   VALUES（值1，值2，…）；
举例：
INSERT INTO student(id,name,grade)VALUES(1,'zhangshan',98);

使用SELECT * FROM student;命令查看结果为：
image.jpeg

表示数据已经成功插入。

1.1.2 INSERT语句中不指定字段名

若不指定字段名，则添加的值的顺序应和字段在表中的顺序完全一致。

语法：INSERT INTO 表名 VALUES(值11，值2，…）；

举例：INSERT INTO student VALUES (2,'lisi',62);

使用SELECT * FROM student;命令查看结果为：

image.jpeg

1.2 为表的指定字段添加数据

为指定字段添加数据，即只向部分字段添加值，而其他字段的值为表定义时的默认值。

语法：INSERT INTO 表名（字段1，字段2，…）

　　   VALUES(值1，值2，…）

举例：INSERT INTO student(id,name) VALUES(3,'wangwu');

使用SELECT * FROM student;命令查看结果为：

image.jpeg

从结果中可以看出，新记录的grade字段值为NULL，是因为添加时为指明grade的值，系统会自动添加默认值。

1.3 INSERT语句的其他写法

语法：INSERT INTO 表名

　　  SET 字段名1=值1[,字段名2=值2，…]

举例：INSERT INTO student

　　  SET id=4，name='zhaoliu',grade=72;

使用SELECT * FROM student;命令查看结果为：

image.jpeg

1.4 同时添加多条数据

语法：INSERT INTO 表名[(字段名1，字段名2，…)]

　　　VALUES （值1，值2，…），（值1，值2，…），

　　　…

　　　（值1，值2，…）

举例：INSERT INTO student VALUES

　　　　(5，‘lilei’,99),

　　　　(6,'hanmeimei',87),

　　　　(8,'poly',76);

使用SELECT * FROM student;命令查看结果为：

image.jpeg

2、“删”——删除数据

语法：DELETE FROM 表名 [WHERE 条件表达式

2.1 删除部分数据

即删除指定的部分数据，需要使用WHERE子句来指定删除记录的条件。

举例：删除student表中的id值为7的记录

命令：DELETE  FROM student 

　　　WHERE id=7;

使用SELECT * FROM student;命令查看结果为：

image.jpeg

可见id为7的记录已被成功删除。

2.2 删除全部数据

若 DELETE 语句中没有使用WHERE语句，则会将表中所有记录都删除。

语法：DELETE FROM 表名

举例：删除student表中的所有记录

命令：DELETE FROM student；

使用SELECT * FROM student;命令查看结果为：

image.jpeg

可见student表中记录为空，说明表中所有数据已被成功删除。

2.2 删除全部数据的另一种方法——TRUNCATE

语法：TRUNCTE [TABLE ] 表名

举例：TRUNCATE TABLE student；即可删除student表

注意：

（1）DELETE 后面可以跟WHERE子句指定删除部分记录，TRUNCATE只能删除整个表的所有记录

（2）使用TRUNCATE语句删除记录后，新添加的记录时，自动增长字段（如本文中student表中的 id 字段）会默认从1开始，而使用DELETE删除记录后，新添加记录时，自动增长字段会从删除时该字段的的最大值加1开始计算（即原来的id最大为5，则会从6开始计算）。所以如果是想彻底删除一个表的记录而且不会影响到重新添加记录，最好使用TRUNCATE来删除整个表的记录。

3、“改”——更新数据

更新数据指对表中现存的数据进行修改。

语法：UPDATE 表名

　　　　　　SET 字段名1=值1，[ ，字段名2=值2，…]

　　　　　　[ WHERE 条件表达式 ]

在执行后面的语句之前，先使用INSERT语句往student表中插入以下数据：

image.jpeg

3.1 UPDATE 更新部分数据

指更新指定表中的指定记录，使用WHERE 子句来指定。

举例：将student表中id值为1=记录，将其name字段的值改为‘caocao’，grade字段的值改为50。

先查询之前的记录：SELECT * FROM student WHERE id=1;

显示为：

image.jpeg
命令：UPDATE student 

　　　　SET name=‘caocao’,grade=50

　　　　WHERE id=1;

使用SELECT * FROM student;命令查看结果为：

image.jpeg

可见表中数据已被成功更新。

注意：还可以使用其他WHERE条件表达式，如：id > 4;

3.2 UPDATE 更新全部数据

在UPDATE 语句中若不使用WHERE 子句，则会将表中所有记录的指定字段都进行更新。

 举例：更新student表中全部记录，将grade字段都更新为80

命令：UPDATE student

　　　　SET grade=80；

使用SELECT * FROM student;命令查看结果为：

image.jpeg

可以看出所有数据已被成功更新。

4、“查”——之单表查询

MySQL从数据表中查询数据最基本的语句是SELECT语句，在前面的“增删查”已经使用过：SELECT * FROM 表名，也就是查询指定数据表中的所有数据。下面将对SELECT语句进行详细介绍。

在进行后面的操作之前我们先建立一个新的数据表student2，如下：

CREATE TABLE student2
　　(
　　　　id  INT(3) PRIMARY KEY AUTO_INCREMENT,
　　　　name  VARCHAR(20) NOT NULL,
　　　　grade FLOAT,gender CHAR(2)
　　);

此处的PRIMARY KEY表示将该字段设为主键，AUTO_INCREMENT表示将该字段设为开始值是 1,每条新记录自动递增 1,，所以在插入数据时不需为该字段设值；NOT NULL表示将该字段限制为非空值（此处不作详细讲解）

再向student2表中插入如下数据：

INSERT INTO student2(name,grade,gender)
　　　　　　　　VALUES ('songjiang',40,'男'),('wuyong',100,'男'),
('qinming',90,'男'),('husanniang',88,'女'),('sunerniang',66,'女'),
('wusong',86,'男'),('linchong',92,'男'),('yanqing',90,NULL);
image.jpeg

注意：若因为此处插入的数据包含了中文而导致无法插入，可将“男/女”改为“man/woman”，或者将字段编码改为utf-8（方法自行百度，此处不做讲解）。

4.1 简单查询

4.1.1 查询所有字段

语法：SELECT 字段名1，字段名2，…

　　　FROM 表名

举例：查询student2表中的所有记录

命令：SELECT id,name,grade ,gender

　　    FROM student2;

结果：

image.jpeg

注意：字段顺序可以更改，如：

SELECT id,grade,gender ,name

　　   　　 FROM student2;

则显示的结果也会作出对应的调整：

image.jpeg

4.1.2 在SELECT语句中使用（‘ * ’）通配符代替所有字段

语法：SELECT * FROM 表名；

在此前已多次使用此命令，所以此处不作赘述。

4.1.3 查询指定的部分字段

语法：SELECT 字段名1，字段名2，… FROM 表名；

举例：查询student2表中的name字段和gender字段

命令：SELECT name,gender FROM student2;

结果：

image.jpeg

从结果中可以看到只显示了name和gender两个字段。

4.2 按条件查询

4.2.1 带关系运算符的查询

语法：SELECT 字段名1，字段名2，…

 　　　FROM 表名

　　    WHERE 条件表达式

在WHERE子句中可以使用如下关系运算符：

<colgroup style="margin: 0px; padding: 0px;"><col style="margin: 0px; padding: 0px;"><col style="margin: 0px; padding: 0px;"></colgroup>
|

关系运算符

image.png
举例：查询student2表中id为4的人的id和name字段

命令：SELECT id，name FROM student2  WHERE id=4;

结果：

image.jpeg
举例：查询student2表中grade大于80的人的name和grade字段

命令：SELECT name FROM student2 WHERE grade>80;

结果：

image.jpeg

4.2.2 带 IN 关键字的查询

IN关键字用于判断某个字段的值是否在指定集合中，若在，则该字段所在的记录将会被查询出来.

语法：SELECT * | 字段名1，字段名2，…

　　　FROM 表名

　　　WHERE 字段名 [ NOT ]  IN （元素1，元素2，…）

举例：查询student2表中id值为1,2,3的记录

命令：SELECT * FROM student2 WHERE  id IN （1,2,3）；

结果：

image.jpeg

注意：NOT IN 与 IN 相反，查询的是不在指定范围内的记录。

4.2.3 带 BETWEEN AND 关键字的查询

BETWEEN AND 用于判断某个字段的值是否在指定范围之内，若在，则该字段所在的记录会被查询出来，反之不会。

语法：SELECT * | { 字段名1，字段名2，… }

　　　FROM  表名

　　    WHERE 字段名 [ NOT ] BETWEEN  值1  AND  值2；

举例：查询student2表中id值在2~5之间的人的id和name

命令：SELECT id,name FROM students WHERE id BETWEEN 2 AND 5;

结果：

image.jpeg

注意：NOT BETWEEN AND 表示查询指定范围外的记录。

4.2.4 空值查询

在数据表中有些值可能为空值（NULL），空值不同于0，也不同于空字符串，需要使用 IS NULL 来判断字段的值是否为空值。

语法：SELECT * | 字段名1，字段名2，…

　　　FROM 表名

　　　WHERE 字段名 IS [ NOT ] NULL

举例：查询student2表中gender值为空值的记录。

命令：SELECT * FROM student2 WHERE gender IS NULL;

结果：

image.jpeg

注意：IS NOT NULL 关键字用来查询字段不为空值的记录。

4.2.5 带 DISTINCT 关键字的查询

很多表中某些字段的数据存在重复的值，可以使用DISTINCT关键字来过滤重复的值，只保留一个值。

语法：SELECT DISTINCT 字段名 FROM 表名；

举例：查询student2表中gender字段的值，结果中不允许出行重复的值。

命令：SELECT DISTINCT gender FROM student2;

结果：

image.jpeg

注意：DISTINCT 关键字还可作用于多个字段，则只有多个字段的值都完全相同时才会被认作是重复记录。

4.2.6 带 LIKE 关键字的查询

语法：SELECT * | 字段名1，字段名2，…

　　　FROM 表名

　　　WHERE 字段名 [ NOT ] LIKE ‘匹配字符串’;

（1）百分号（%）通配符

匹配任意长度的字符串，包括空字符串。例如，字符串“ c% ”匹配以字符 c 开始，任意长度的字符串，如“ ct ”，“ cut ”，“ current ”等；字符串“ c%g ”表示以字符 c 开始，以 g 结尾的字符串；字符串“ %y% ”表示包含字符“ y ”的字符串，无论“ y ”在字符串的什么位置。

举例1：查询student2表中name字段以字符“ s ”开头的人的id,name

命令：SELECT id,name FROM student2  WHERE name LIKE "S%";

结果：

image.jpeg

举例2：查询student2表中name字段以字符“ w ”开始，以字符“ g ”结尾的人的id，name。

命令：SELECT id,name FROM student2 WHERE name LIKE 'w%g';

结果：

image.jpeg

举例3：查询student2表中name字段不包含“ y ”的人的id，name。

命令：SELECT id,name FROM student2 WHERE name NOT LIKE '%y%';

结果：

image.jpeg

（2）下划线（_）通配符

下划线通配符只匹配单个字符，若要匹配多个字符，需要使用多个下划线通配符。例如，字符串“ cu_ ”匹配以字符串“ cu ”开始，长度为3的字符，如“ cut ”，“ cup ”；字符串“ c__l”匹配在“ c ”和“ l ”之间包含两个字符的字符串，如“ cool ”。需要注意的是，连续的“”之间不能有空格，例如“M _QL”只能匹配“My SQL”，不能匹配“MySQL”。

举例：查询在student2表中name字段值以“ wu ”开始，以“ ong ”结束，并且中间只有一个字符的记录。

命令：SELECT * FROM student2 WHERE name LIKE 'wu_ong';

结果：

image.jpeg

注意：若要查询的字段值本来就含有“ % ”或者“ _ ”,则要用“ \ ”进行转义，如要查询本身含有“ % ”的字符串，命令应改为 “ %%%”。

4.2.7 带 AND 关键字的多条件查询

在使用SELECT语句查询数据时，优势为了使查询结果更加精确，可以使用多个查询条件，如使用 AND 关键字可以连接两个或多个查询条件。

语法：SELECT * | 字段名1，字段名2，…

　　　FROM 表名

　　　WHERE 条件表达式1 AND 条件表达式2 [ … AND 条件表达式 n ];

举例：查询student2表中 id 字段小于5，并且 gender 字段值为“ 女 ”的人的id和name

命令：SELECT id,name FROM student2 WHERE id<5 AND gender='女';

结果：

image.jpeg

4.2.8 带 OR 关键字的多条件查询

与 AND 关键字不同，OR 关键字只要满足任意一个条件就会被查询出来

语法：SELECT * | 字段名1，字段名2，…

　　　FROM 表名

　　　WHERE 条件表达式1 OR 条件表达式2 [ … OR 条件表达式 n ];

举例：查询student2表中 id 字段小于3，或者 gender 字段值为“ 女 ”的人的id，name和gender

命令：SELECT id,name ,gender FROM student2 WHERE id<3 OR gender='女';

结果：

image.jpeg

4.2.9 OR 和 AND 一起使用的情况

OR 和 AND 一起使用的时候，AND 的优先级高于 OR，因此二者一起使用时，会先运算 AND 两边的表达式，再运算 OR 两边的表达式。

举例：查询student2表中gender值为“女”或者gender值为“男”并且grade字段值为100的人的记录

命令：SELECT * FROM student2 WHERE gender='女' OR gender='男' AND grade=100;

结果：

image.jpeg

4.3 高级查询

4.3.1 聚合函数

image.png

（1）COUNT()函数：统计记录的条数

语法：SELECT COUNT(*) FROM 表名

举例：查询student2表中一共有多少条记录

命令：SELECT COUNT(*) FROM student2;

结果：

image.jpeg

（2）SUM()函数：求出表中某个字段所有值的总和

语法：SELECT  SUM(字段名) FROM 表名；

举例：求出student2表中grade字段的总和

命令：SELECT SUM(grade) FROM student2;

结果：

image.jpeg

（3）AVG()函数：求出表中某个字段所有值的平均值

语法：SELECT AVG(字段名) FROM 表名；

举例：求出student2表中grade字段的平均值

命令：SELECT AVG(grade) FROM student2;

结果：

image.jpeg

（4）MAX()函数：求出表中某个字段所有值的最大值

语法：SELECT MAX(字段名) FROM 表名；

举例：求出student2表中所有人grade字段的最大值

命令：SELECT MAX(grade) FROM student2;

结果：

image.jpeg

（5）MIN()函数：求出表中某个字段所有值的最小值

语法：SELECT MIN(字段名) FROM 表名；

举例：求出student2表中所有人grade字段的最小值

命令：SELECT MIN(grade) FROM student2;

结果：

image.jpeg

4.3.2 对查询结果进行排序

语法：SELECT 字段名1，字段名2，…

　　　FROM 表名

　　　ORDER BY 字段名1 [ ASC | DESC ],字段名2 [ ASC | DESC ]…

在该语法中指定的字段名是对查询结果进行排序的依据，ASC表示升序排列，DESC 表示降序排列，默认情况是升序排列。

举例1：查出student2表中的所有记录，并按照grade字段进行升序排序

命令：SELECT * FROM student2

　　　ORDER BY grade;

结果：

image.jpeg

举例2：查出student2表中的所有记录，并按照grade字段进行降序排序

命令：SELECT * FROM student2

　　　ORDER BY grade DESC;

结果：

image.jpeg

4.3.3 分组查询

在对表中数据进行统计的时候，可以使用GROUP BY 按某个字段或者多个字段进行分组，字段中值相同的为一组，如男生分为一组，女生分为一组。

语法：SELECT  字段名1，字段名2，…

　　　FROM 表名

　　　GROUP BY 字段名1，字段名2，… [ HAVING 条件表达式 ];

（1）单独使用 GROUP BY 进行分组

单独使用GROUP BY 关键字，查询的是每个分组中的一条记录

举例：查询student2表中的数据，按照gender字段进行分组。

命令：SELECT * FROM student2 GROUP BY gender;

结果：

image.jpeg

注意：若在此2例中键入命令之后出现“#1055”错误，无法完成分组查询，是由于MySQL版本升级到了5.7，可自行百度“MySQL 1055错误”参考解决办法。

（2） GROUP BY 和聚合函数一起使用

GROUP BY 和聚合函数一起使用，可以统计出某个或者某些字段在一个分组中的最大值、最小值、平均值等。

举例：将student2表按照gender字段进行分组查询，计算出每组共有多少个人。

命令：SELECT COUNT(*) ,gender FROM student2 GROUP BY gender;

结果：

image.jpeg

（2） GROUP BY 和 HAVING 关键字一起使用

HAVING关键字和WHERE关键字的作用相同，区别在于HAVING 关键字可以跟聚合函数，而WHERE 关键字不能。通常HAVING 关键字都和GROUP BY一起使用，用于对分组后的结果进行过滤。

举例：将student2表按照gender字段进行分组查询，查询出grade字段值之和小于300的分组

命令：SELECT sum(grade),gender FROM student2 GROUP BY gender HAVING SUM(grade) < 300;

结果：

image.jpeg

4.3.4 使用 LIMIT 限制查询结果的数量

语法：SELECT 字段名2，字段名2，…

　　　FROM 表名

　　　LIMIT [ OFFSET ,] 记录数

在此语法中，LIMIT 后面可以跟两个参数，第一个参数“ OFFSET ”表示偏移量，如果偏移量为0，则从查询结果的第一条记录开始，偏移量为1则从查询结果中的第二条记录开始，以此类推。OFFSET为可选值，默认值为0，第二个参数“记录数”表示指定返回查询记录的条数。

举例1：查询student2表中的前四条记录。

命令：SELECT * FROM student LIMIT 4;

结果：

image.jpeg

举例2：查询student2表中grade字段从第五位到第八位的人（从高到低）

命令：SELECT * FROM student2 ORDER BY grade DESC LIMIT 4,4;

结果：

image.jpeg

4.3.5 函数（列表）

见MySQL之常用函数

4.4 为表和字段取别名

4.4.1 为表取别名

在进行查询操作时，如果表名很长使用起来不方便，可以为表取一个别名来代替表的名称。

语法：SELECT * FROM 表名 [ AS ] 别名；

举例：为student2表起一个别名s，并查询student2表中gender字段值为“女”的记录

命令：SELECT * FROM student2 AS s WHERE s.gender='女';

结果：

image.jpeg

4.4.1 为字段取别名

语法：SELECT 字段名 [ AS ] 别名 [ ,字段名 [AS] 别名，…]  FROM 表名 ；

举例：查询student2表中的所有记录的name和gender字段值，并未这两个字段起别名stu_name和stu_gender

命令：SELECT name AS stu_name,gender AS stu_gender FROM student2;

结果：

image.jpeg
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 201,784评论 5赞 474
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 84,745评论 2赞 378
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 148,702评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,229评论 1赞 272
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,245评论 5赞 363
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,376评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,798评论 3赞 393
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,471评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,655评论 1赞 295
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,485评论 2赞 318
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,535评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,235评论 3赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,793评论 3赞 304
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,863评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,096评论 1赞 258
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,654评论 2赞 348
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,233评论 2赞 341

推荐阅读更多精彩内容

  • MySql增删改查语句
    前言：以下是MySQL最基本的增删改查语句，很多IT工作者都必须要会的命令，也是IT行业面试最常考的知识点，由于是...
    莓是草莓的莓阅读 3,967评论 0赞 0
  • MySQL之增删改查
    SQL语言一共分为四大类：DDL（数据库定义语言）、DML（数据库操纵语言）、DQL（数据库查询语言）、DCL（数...
    lily_5945阅读 266评论 0赞 2
  • mysql增删改查（035）
    一.MySQL 中的约束 1.约束类型 非空约束(not null) 唯一性约束(unique) ...
    珂and超阅读 327评论 0赞 0
  • 6、mysql增删改查
    6、mysql增删改查 6.1、创建表 ^: ENGINE--指定存储引擎 InnoD 为默认引擎 ; BDE...
    knock阅读 239评论 0赞 0
  • 你说
    夜莺2517阅读 127,707评论 1赞 9