查询、修改、删除表记录

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

查询表记录

前面,我们已经在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;

并且关系

如果你要查询的记录过滤条件是多个,并且要 同时满足 ,就使用 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;


  • 视频讲解


查询结果排序

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

比如,上面的查询

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;


  • 视频讲解


修改表记录

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

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';


  • 视频讲解


删除表记录

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

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

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

delete from customer1  where username='cus16';

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

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

delete from customer1;

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

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

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


  • 视频讲解



作业和练习

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

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

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

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

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


扫码分享给朋友,一起学更有动力哦




上一页 下一页