查询、修改、删除表记录


点击这里,看视频讲解,学习下面内容

我们刚刚学习了插入记录到数据表中, 显然,接下来我们要学习的就是如何查询表记录

查询表记录

前面,我们已经在user表中插入了一批数据。

要查询该表中所以的记录,使用 SQL语句 select 进行查询

如下所示

select * from user;

得到的结果是

+----+----------+-----------+---------------+
| id | username | password  | realname      |
+----+----------+-----------+---------------+
|  1 | byhy1    | password1 | 白月黑羽1     |
|  2 | byhy2    | password2 | 白月黑羽2     |
|  3 | byhy3    | password3 | 白月黑羽3     |
|  4 | byhy4    | password4 | 白月黑羽4     |
|  5 | byhy5    | password5 | 白月黑羽5     |
+----+----------+-----------+---------------+
5 rows in set (0.00 sec)

这样就可以查看user 表中的所有记录的所有列的值。

上面 ,select 语句中的 * 表示 查看所有的列


有的时候,表中的列很多,我们并不想查看所有的列的内容。

这时候,可以指定要查看的列。

如果,这个user表,我们只想查看 id和username 这两列的信息,就可以这样写

select id,username  from user;

得到的结果是

+----+----------+
| id | username |
+----+----------+
|  1 | byhy1    |
|  2 | byhy2    |
|  3 | byhy3    |
|  4 | byhy4    |
|  5 | byhy5    |
+----+----------+
5 rows in set (0.00 sec)

查询过滤


点击这里,看视频讲解,学习下面内容

有的时候,一张数据表里面可能有海量的记录,比如上百万条,甚至上亿条记录(想象一下淘宝的用户表有多少)。

我们有时候只需要查询其中符合某些条件的部分信息。 这就叫过滤查询。

根据进过滤条件查询,我们使用 where从句

过滤条件

比如,上面的user表,我们只需要查询 用户名为 byhy3 的那个用户的信息,就可以这样

select *  from user where username='byhy3';

得到的结果是

+----+----------+-----------+---------------+
| id | username | password  | realname      |
+----+----------+-----------+---------------+
|  3 | byhy3    | password3 | 白月黑羽3     |
+----+----------+-----------+---------------+
1 row in set (0.00 sec)

其中 username='byhy3' 里面的等号就是一个过滤条件的 操作符 ,表示过滤条件是查找记录的username字段值等于byhy3。


如果我们只需要查询 id为 2 的那个用户的信息,就可以这样

select *  from user where id=2;

除了等于条件(使用 =), 还有

  • 不等于(使用 <> 或者 !=)
select *  from user where id!=1;
  • 大于(使用 > )
select *  from user where id>3;
  • 小于(使用 < )
select *  from user where id<3;
  • 大于等于(使用 >= )
select *  from user where id>=3;
  • 小于等于(使用 <= )
select *  from user where id<=3;
  • 包含字符 (使用 like 和 % )
/* 以 byhy 开头*/
select *  from user where username like "byhy%";
/* 以 33 结尾*/
select *  from user where username like "%33";
/* 以 byhy 开头 并且 以 33 结尾*/
select *  from user where username like "byhy%33";
/* 包含 byhy */
select *  from user where username like "%byhy%";
  • 在集合里面 (使用 in)
/* id 在指定集合里面 */
select * from user where id in (1,2,3);

/* usernmae 在指定集合里面 */
select * from user where username  in ("byhy1","byhy2");

并且关系

如果你要查询的记录过滤条件是多个,并且要 同时满足 ,就使用 and 关键字连接多个 过滤条件

比如,我们用下面的语句创建客户表 customer1,并且插入一些数据

CREATE TABLE customer1 (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(150)  NOT NULL,
  `level` int NOT NULL,
  `coin` int NOT NULL,
  PRIMARY KEY (id)
) ;

INSERT INTO customer1 (username,level,coin) VALUES 
    ('cus01',15, 5),
    ('cus02',5,  315),
    ('cus03',3,  215),
    ('cus04',11, 6615),
    ('cus05',22, 115),
    ('cus06',6,  4415),
    ('cus07',7,  315),
    ('cus08',4,  15),
    ('cus09',9,  315),
    ('cus10',44, 45),
    ('cus11',2,  215),
    ('cus12',1,  815),
    ('cus13',15, 13),
    ('cus14',25, 1566),
    ('cus15',12, 125),
    ('cus16',8,  75);

我们 要查询 id > 10 并且等级 level值 小于 15的用户。就可以这样

select *  from customer1 where id > 10 and level<15;

得到的结果是

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
| 11 | cus11    |     2 |  215 |
| 12 | cus12    |     1 |  815 |
| 15 | cus15    |    12 |  125 |
| 16 | cus16    |     8 |   75 |
+----+----------+-------+------+
4 rows in set (0.00 sec)

如果你有更多的过滤条件,就继续在后面加and,比如

select *  from customer1 where id > 10 and level<15 and coin<300;

或者关系

如果你要查询的记录过滤条件是多个, 只要满足其中一个条件即可 ,就使用 or 关键字连接多个 过滤条件

比如,我们 要查询 id > 10 等级 level值 小于 15的用户。就可以这样

select *  from customer1 where id > 10 or level<15;

得到的结果是

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
|  2 | cus02    |     5 |  315 |
|  3 | cus03    |     3 |  215 |
|  4 | cus04    |    11 | 6615 |
|  6 | cus06    |     6 | 4415 |
|  7 | cus07    |     7 |  315 |
|  8 | cus08    |     4 |   15 |
|  9 | cus09    |     9 |  315 |
| 11 | cus11    |     2 |  215 |
| 12 | cus12    |     1 |  815 |
| 13 | cus13    |    15 |   13 |
| 14 | cus14    |    25 | 1566 |
| 15 | cus15    |    12 |  125 |
| 16 | cus16    |     8 |   75 |
+----+----------+-------+------+
13 rows in set (0.00 sec)


如果我们要 查询 id > 8 或者 等级 level值 < 15的用户, 但是 同时还要满足条件coin=315 的。

满足条件的只有下面这3条记录

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
|  2 | cus02    |     5 |  315 |
|  7 | cus07    |     7 |  315 |
|  9 | cus09    |     9 |  315 |
+----+----------+-------+------+

如果你直接这样写

select *  from customer1 where id > 8 or level<15 and coin=315;

运行一下,结果却发现,如下

+----+----------+-------+------+
| id | username | level | coin |
+----+----------+-------+------+
|  2 | cus02    |     5 |  315 |
|  7 | cus07    |     7 |  315 |
|  9 | cus09    |     9 |  315 |
| 10 | cus10    |    44 |   45 |
| 11 | cus11    |     2 |  215 |
| 12 | cus12    |     1 |  815 |
| 13 | cus13    |    15 |   13 |
| 14 | cus14    |    25 | 1566 |
| 15 | cus15    |    12 |  125 |
| 16 | cus16    |     8 |   75 |
+----+----------+-------+------+

原来 or 和 and 同时出现的时候, and 优先级高,所以会先计算 level<15 and coin=315 结果再和 前面的 id > 8 进行 or运算。

怎么办?

可以使用括号,让 or 先运算,如下所示。

select *  from customer1 where (id > 8 or level<15)  and coin=315;

子查询

已知有如下两张数据表,

表名:学生表

id 姓名 性别 电话号码 学历
1 白月黑羽 13000000001 本科
2 紫气一元 13000000002 硕士
3 无形冥主 13000000003 博士
4 拜月魔巫 13000000004 本科

  表名:成绩表
id 姓名 课程 分数
1 白月黑羽 语文 100
2 紫气一元 英语 85
3 无形冥主 语文 100
4 无形冥主 英语 98
5 拜月魔巫 数学 100

要查询 考试成绩有100分的学生的电话号码

可以使用如下SQL语句

SELECT 电话号码 FROM 学生表
WHERE 姓名 IN (
  SELECT 姓名 FROM 成绩表 WHERE 分数=100
);

过滤条件 是 姓名在一个集合中,但是这个集合不是预先就知道的,而是 另外一个select查询 的结果。

另外一个select 术语叫 子查询 (subquery)。

MySQL执行 语句时,会先执行子查询,获取分数为100分的学生姓名,放入集合中。 然后再执行外层查询。


可以使用下面的SQL语句来创建表和记录,验证一下

CREATE TABLE `学生表` (
    id INT AUTO_INCREMENT,
    姓名 VARCHAR(10) NOT NULL,
    性别 VARCHAR(1) NOT NULL,
    电话号码 VARCHAR(15) NOT NULL,
    学历     VARCHAR(15) NOT NULL,
    PRIMARY KEY (id)
);

CREATE TABLE `成绩表` (
    id INT AUTO_INCREMENT,
    姓名 VARCHAR(255) NOT NULL,
    课程 VARCHAR(15) NOT NULL,
    分数 INT NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO 学生表 (姓名,性别,电话号码,学历) VALUES 
    ('白月黑羽','男', '13000000001', '本科'),
    ('紫气一元','男', '13000000002', '硕士'),
    ('无形冥主','男', '13000000003', '博士'),
    ('拜月魔巫','女', '13000000004', '本科');
    
    
INSERT INTO 成绩表 (姓名,课程,分数) VALUES 
    ('白月黑羽',  '语文', 100),
    ('紫气一元',  '英语', 85),
    ('无形冥主',  '语文', 100),
    ('无形冥主',  '英语', 98),
    ('拜月魔巫',  '数学', 100);

执行查询结果如下

mysql> SELECT 电话号码 FROM 学生表
    -> WHERE 姓名 IN (SELECT 姓名 FROM 成绩表 WHERE 分数=100);
+--------------+
| 电话号码     |
+--------------+
| 13000000001  |
| 13000000003  |
| 13000000004  |
+--------------+
3 rows in set (0.00 sec)

多表连接查询

上面的示例中,我们使用 子查询得到 考试成绩有100分的学生的电话号码

其实,还可以使用 连接查询 ,如下

SELECT 电话号码 FROM 学生表
INNER JOIN 成绩表 ON  学生表.姓名 = 成绩表.姓名
WHERE 成绩表.分数=100;

INNER JOIN 语句必须紧跟在 FROM 语句 后面,表示连接哪张表,连接条件是什么。

本例中,连接的是 成绩表, 连接条件是 相同的姓名

MySQL 在执行上面的SQL语句,会依次

  • 查询 学生表 里面的每条记录

  • 针对这条记录,在 成绩表中 找 姓名 字段 和 本记录中姓名字段相同 的记录

    如果找到 ,将两条记录连接起来,形成一条包含两条记录内容的新的记录行,存在内存中

    如果找不到,忽略这条记录

  • 比较 成绩表中这条记录的 分数 字段是否等于100 ,如果相等,就放入结果集中 否则忽略这条记录


本例中的连接查询 INNER JOIN 是 内连接,是最常用的一种连接查询。

还有3 种外连接查询:RIGHT OUTER JOIN、LEFT OUTER JOIN、FULL OUTER JOIN,因为不是特别常用,这里我们暂不介绍,感兴趣的朋友可以自行网上查阅相关资料。

查询结果去重

前面示例中,我们创建了这样的一张 成绩表

+----+--------------+--------+--------+
| id | 姓名         | 课程   | 分数   |
+----+--------------+--------+--------+
|  1 | 白月黑羽     | 语文   |    100 |
|  2 | 紫气一元     | 英语   |     85 |
|  3 | 无形冥主     | 语文   |    100 |
|  4 | 无形冥主     | 英语   |     98 |
|  5 | 拜月魔巫     | 数学   |    100 |
+----+--------------+--------+--------+

如果我们想查看 有哪些学科 学生考试得过满分 ,

可以写这样的SQL语句

select 课程  from 成绩表 where 分数=100;

结果如下

+--------+
| 课程   |
+--------+
| 语文   |
| 语文   |
| 数学   |
+--------+

可以发现 语文 重复了两次。

因为有 两条记录 语文考试成绩为 100分。

但是我们现在的目的是 : 只要看有哪些学科,当然不希望重复。

这时,可以使用关键字 distinct

如下

select distinct 课程  from 成绩表 where 分数=100;

就可以去掉结果中重复的内容了。

查询结果排序

点击这里,看视频讲解,学习下面内容

我们经常需要,查询一个结果,并且按照某个字段的值进行排序。

比如,上面的查询

select *  from customer1 where id > 10;

结果需要安装 coin 的值 由小到大排序,怎么办?

这时,可以使用 order by

比如

select *  from customer1 where id > 10 
order by coin;

表示查询结果记录 根据 coin 字段的值 升序(ASC) 排列。 所谓升序,就是从小到大。

缺省就是按照升序排列,当然你也可以指定是升序,像这样

select *  from customer1 where id > 10 
order by coin asc;

如果你想 反过来,降序排列,则使用 desc ,像这样

select *  from customer1 where id > 10 
order by coin desc;

有时候,你希望 显示记录 先根据 coin排列 ,coin相同的再根据 level 进行排序,就可以这样 写

select *  from customer1 where id > 10 
order by coin, level;

你希望 显示记录 先根据 coin 升序 排列 ,coin相同的再根据 level 降序 排序,就可以这样 写

select *  from customer1 where id > 10 
order by coin, level desc;

查询结果分页

有的时候,SQL语句查询的结果可能有成千上万条记录(想象一下淘宝搜索 手机)。如果一下子全部返回,会耗费大量的CPU和内容,也是不需要的。

这时候,我们的SQL语句可以指定 分页 ,每次只返回一页数据。

我们执行下面的SQL语句再插入一些数据到学生表中

INSERT INTO 学生表 (姓名,性别,电话号码,学历) VALUES 
    ('白月黑羽101','男', '13100000101', '本科'),
    ('白月黑羽102','男', '13100000102', '本科'),
    ('白月黑羽103','男', '13100000103', '本科'),
    ('白月黑羽104','男', '13100000104', '本科'),
    ('白月黑羽105','男', '13100000105', '本科'),
    ('白月黑羽106','男', '13100000106', '本科'),
    ('白月黑羽107','男', '13100000107', '本科'),
    ('白月黑羽108','男', '13100000108', '本科'),
    ('白月黑羽109','男', '13100000109', '本科'),
    ('白月黑羽110','男', '13100000110', '本科'),
    ('白月黑羽111','男', '13100000111', '本科'),
    ('白月黑羽112','男', '13100000112', '本科'),
    ('白月黑羽113','男', '13100000113', '本科'),
    ('白月黑羽114','男', '13100000114', '本科'),
    ('白月黑羽115','男', '13100000115', '本科'),
    ('白月黑羽116','男', '13100000116', '本科'),
    ('白月黑羽117','男', '13100000117', '本科'),
    ('白月黑羽118','男', '13100000118', '本科'),
    ('白月黑羽119','男', '13100000119', '本科'),
    ('白月黑羽120','男', '13100000120', '本科'),
    ('白月黑羽121','男', '13100000121', '本科'),
    ('白月黑羽122','男', '13100000122', '本科');

然后,如果我们设置每页最多5条记录,返回第2页就可以这样写

select * from 学生表
limit 5 offset 1;

limit 后面的数值 指定最多显示多少条记录, offset 后面的数值 指定了当前显示第几页,第1页是0,第2页是1…

记录数量、平均值、最大值、最小值

有的时候,我们要获取符合某种条件的记录 有多少条。

比如 ,我们想知道 学生表中 姓名以 白月黑羽 开头的 有多少条记录。

可以使用 SQL函数 count ,如下所示

select count(*) from 学生表 where 姓名 like "白月黑羽%";

count是 SQL 函数,用来返回记录数量。

常用的SQL函数,还有

  • sum

    计算某列数字的总和,比如

    select sum(分数) from 成绩表;
    

    就可以获取成绩表中所有记录分数 得分总和

  • avg

    计算某列数字的平均,比如

    select avg(分数) from 成绩表;
    

    就可以获取成绩表中所有记录平均之后的得分

  • max

    得到某列数字的最大值,比如

    select max(分数) from 成绩表;
    

    就可以获取成绩表中所有记录的最高得分

  • miin

    得到某列数字的最小值,比如

    select min(分数) from 成绩表;
    

    就可以获取成绩表中所有记录的最低得分

修改表记录


点击这里,看视频讲解,学习下面内容

我们经常需要更新表记录信息,比如一个用户更新了他的头像,消费后余额更新等等。

MySQL更新记录使用的是 update 语句。

前面我们曾经创建过customer1 表,如果我们要更新其中用户名为 cus16 这条记录,把coin值设置为 100,就可以这样写

update customer1 set coin=100 where username='cus16';

注意, 这里也用到了 where 从句,限制了更新的 只是 username为cus16的这些记录。

否则,如果没有where 从句,像这样

update customer1 set coin=100;

就会更新该表中所有的记录,把所有的记录的coin 全部设置为 100

where 从句也是用来,过滤要更新的记录的, 用法和select里面where 从句的用法是一样的。


如果我们要更新多个列里面的内容,比如要把username为cus16的记录,把coin值设置为 100,level值设置为 30,就可以这样写

update customer1 set coin=100,level=30 where username='cus16';

另外SQL语句中间是可以换行的,像下面这样,也是可以的

update customer1 
set 
  coin=100,
  level=30 
where username='cus16';
自学我们的教程时,应该找个同桌 一起学习,互相交流督促。

如果自学速度慢、难点搞不定,欢迎来报 实战班 学习。
白月黑羽 1对1连线 讲解答疑,大量练习实战,学习效果是自学没法比的。还有商业项目实战,可以写入简历。

点击这里查看实战班介绍 咨询微信:18502556834

删除表记录


点击这里,看视频讲解,学习下面内容

我们有时也需要删除表记录。

MySQL删除记录使用的是 delete from 语句。

前面我们曾经创建过customer1 表,如果我们要删除其中cus16这条记录,可以这样写

delete from customer1  where username='cus16';

注意, 这里也用到了where 从句,限制了 删除的 只是 username为cus16的这些记录。

否则,如果没有where 从句,像这样

delete from customer1;

就会删除该表中所有的记录。

删除表中所有的记录,但是表还在,这个和删除表 drop table 是不同的。

where 从句也是用来过滤要删除的记录的, 用法和select里面where 从句的用法是一样的。


同时修改记录

如果有多个客户端同时修改一个记录,会不会导致覆盖问题呢?

Mysql 有锁机制,会保证当一个修改正在进行时,另外的修改会等待前面的操作完成。

所以不必担心这种情况。 客户端程序也无需写任何检测是否有修改正在进行的代码。

作业和练习

前面的作业,我们创建了Prac库,在其中添加user 表 和 medicine表,并且添加了一些记录。

请用SQL语句 ,执行下面的操作

  • 在user表中查询 注册日期在 2019年1月2日之后的 用户记录,并且查询结果安装注册日期升序排列,注册日期相同再根据 等级 降序排列。

  • 用SQL语句 修改 medicine 表,将其中 有效期截止时间 在 2019年1月2日之前的药品 有效状态 改为 0

  • 用SQL语句 删除 medicine 表中, 有效期截止时间 在 2019年1月2日之前的药品


上一页
下一页