文章目录
SQL语句分类
- DQL:数据查询语言(凡是带有
select
关键字的都是查询语句)- select ……
- DML:数据操作语言(凡是对表中的数据进行增删改的都是DML)
- DML主要是操作表的数据
- insert…… (增)
- delete…… (删)
- update…… (改)
- DDL:数据定义语言(凡是带有
create
、drop
、alter
都是DDL)- DDL主要操作的是表的结构,不是表中的数据。
- create…… 新建、等同于增
- alter…… 修改
- drop…… 删除
- TCL:事务控制语言
- 事务提交:
commit
- 事务回滚:
rollback
- 事务提交:
- DCL:数据控制语言
- 授权:
grant
- 撤销授权:
revoke
- 授权:
操作sql语句
简单查询
注意:
- mysql是不见英文
;
不执行,;
表示结束!- sql语句不区分大小写,都可以
- 登录自己的mysql账号,查询所有的数据库
mysql> show databases
-> ;
+--------------------+
| Database |
+--------------------+
| gaoxu |
| information_schema |
| javaee-jdbc |
| mysql |
| performance_schema |
| ssm |
| test |
| test2 |
| test3 |
+--------------------+
9 rows in set (0.04 sec)
- 进入目标数据库:
mysql> use test;
Database changed
- 查询该数据库所有的表
mysql> show tables;
+------------------------+
| Tables_in_test |
+------------------------+
| cs_student201926010204 |
| department201926010204 |
| en_event |
| en_people |
| enroll201926010204 |
| re_event_event |
| re_event_people |
| re_people_event |
| re_people_people |
| student201926010204 |
| teach201926010204 |
| teacher201926010204 |
+------------------------+
12 rows in set (0.02 sec)
查询一个字段
-
select 字段名 from 表名
- 注意:select 和 from 都是关键字;字段名和表名都是标识符;这意味着,select 和 from 不可更改,而字段名和表名可以随意更改
-
示例:
mysql> select tno from teach201926010204;
+----------+
| tno |
+----------+
| 2001009 |
| 20011024 |
| 20011024 |
| 2004124 |
| 2007004 |
| 2009007 |
+----------+
6 rows in set (0.00 sec)
########################################
`select`是查询表中数据的关键字,`tno`表示要查询的字段名,`from`表示要查询哪一张表 `teach201926010204`这个是表名
查询多个字段
-
select 字段名1, 字段名2, …… from 表名
- 注意:多个字段名用英文
,
隔开 - 示例:
############################################ 错误示范:字段名不用英文逗号隔开 ############################################ mysql> select tno cno from teach201926010204; +----------+ | cno | +----------+ | 2001009 | | 20011024 | | 20011024 | | 2004124 | | 2007004 | | 2009007 | +----------+ 6 rows in set (0.00 sec) ############################################ 正确示范:字段名使用英文逗号隔开 ############################################ mysql> select tno, cno from teach201926010204; +----------+-----------+ | tno | cno | +----------+-----------+ | 2001009 | H61030008 | | 20011024 | C31010001 | | 20011024 | C31010001 | | 2004124 | H61030006 | | 2007004 | X27030019 | | 2009007 | C24020005 | +----------+-----------+ 6 rows in set (0.00 sec)
- 注意:多个字段名用英文
查询所有字段
-
法一、在查询多个字段的时候把所有的字段都加上:``select 字段名1, 字段名2, ……, 字段名n from 表名
- 示例:
############################################ 查询表的结构,确认表中的所有字段 ############################################ mysql> desc teach201926010204; +---------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+----------+------+-----+---------+-------+ | tno | char(8) | NO | PRI | NULL | | | cno | char(10) | NO | PRI | NULL | | | year_semester | char(6) | NO | PRI | NULL | | | classno | char(1) | NO | PRI | NULL | | +---------------+----------+------+-----+---------+-------+ 4 rows in set (0.00 sec) ############################################ 查询表中的所有数据 ############################################ mysql> select tno, cno, year_semester, classno from teach201926010204; +----------+-----------+---------------+---------+ | tno | cno | year_semester | classno | +----------+-----------+---------------+---------+ | 2001009 | H61030008 | 2019-1 | A | | 20011024 | C31010001 | 2019-1 | A | | 20011024 | C31010001 | 2019-1 | B | | 2004124 | H61030006 | 2019-1 | A | | 2007004 | X27030019 | 2019-2 | B | | 2009007 | C24020005 | 2019-2 | A | +----------+-----------+---------------+---------+ 6 rows in set (0.00 sec)
-
**法二、可以使用 *** :
select * from 表名
- 注意,这种方式的缺点是:
- 效率低:在java程序中写这样的代码时,执行代码时
*
会有转换为字符串的额外开销时间 - 可读性差:不清楚这个表中所有的字段
- 效率低:在java程序中写这样的代码时,执行代码时
- 示例:
mysql> select * from teach201926010204; +----------+-----------+---------------+---------+ | tno | cno | year_semester | classno | +----------+-----------+---------------+---------+ | 2001009 | H61030008 | 2019-1 | A | | 20011024 | C31010001 | 2019-1 | A | | 20011024 | C31010001 | 2019-1 | B | | 2004124 | H61030006 | 2019-1 | A | | 2007004 | X27030019 | 2019-2 | B | | 2009007 | C24020005 | 2019-2 | A | +----------+-----------+---------------+---------+ 6 rows in set (0.00 sec)
- 注意,这种方式的缺点是:
给查询的列起别名
-
select 字段名1 as 别名1, 字段名2 as 别名2…… from 表名
- 使用as关键字起别名!
- 注意:
- 如果别名是英文,则直接输入英文就好,如果别名是中文,要加 英文单引号
''
- 在所有的数据库中,字符串统一使用英文单引号
- 改别名只是将 显示的查询结果的字段名 改个名字,原表中的字段名 还是原来的
- select语句永远都不会对原始数据进行修改的,他只负责查询!!!
- 如果别名是英文,则直接输入英文就好,如果别名是中文,要加 英文单引号
- 示例:
## 将tno 改名为 教师编号 mysql> select tno as '教师编号' from teach201926010204; +----------+ | 教师编号 | +----------+ | 2001009 | | 20011024 | | 20011024 | | 2004124 | | 2007004 | | 2009007 | +----------+ 6 rows in set (0.00 sec) ################################################### as关键字可以省略 ################################################### mysql> select tno "教师编号" from teach201926010204; +----------+ | 教师编号 | +----------+ | 2001009 | | 20011024 | | 20011024 | | 2004124 | | 2007004 | | 2009007 | +----------+ 6 rows in set (0.00 sec) mysql> select tno from teach201926010204; +----------+ | tno | +----------+ | 2001009 | | 20011024 | | 20011024 | | 2004124 | | 2007004 | | 2009007 | +----------+ 6 rows in set (0.00 sec)
使用sql语句进行数学运算
-
select 字段名1 数学运算表达式1, 字段名2 数学运算表达式2,…… from 表名
- 注意:
- 数学运算符包括加减乘除
- 数学运算只是将 显示结果的字段内容进行数学运算 ,原表中的字段对应的内容不会变!
- 示例:
mysql> select * from enroll201926010204; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 15 rows in set (0.01 sec) ############################# 将学号 * 2展示出来 ############################# mysql> select sno as '学号', score * 2 as '成绩' from enroll201926010204; +-------------+------+ | 学号 | 成绩 | +-------------+------+ | 20182401101 | 176 | | 20182401101 | 178 | | 20182401101 | 166 | | 20182401102 | 196 | | 20182401102 | 132 | | 20182401102 | 180 | | 20182401103 | 172 | | 20182401103 | 110 | | 20182401104 | 152 | | 20182401104 | 174 | | 20182401202 | 176 | | 20182709101 | 180 | | 20182709102 | 166 | | 20182911101 | 132 | | 20182911101 | 174 | +-------------+------+ 15 rows in set (0.00 sec)
- 注意:
条件查询
什么是条件查询
- 按照规定的条件查询表中的数据
- 语法格式:
select 查询的字段名 from 表名 where 条件语句
条件语句总结
对于筛选数字属性列所用到的条件语句(包括 整数,浮点数)
操作符 | 作用 | 例子 |
---|---|---|
= | 判断是否相等 | a = b |
!= | 判断是否不相等 | a != b |
> | 判断是否大于 | a > b |
>= | 判断是否大于等于 | a >= b |
< | 判断是否小于 | a < b |
<= | 判断是否小于等于 | a <= b |
is null | 判断是否为空 | a is null |
is not null | 判断是否不为空 | a is not null |
between …… and | 判断是否在两个数之间 | num between a and b |
not between …… and | 判断是否不在两个数之间 | num not between a and b |
in(……) | 判断是否在这个范围内 | num in(a, b) |
not in(……) | 判断是否不在这个范围内 | num not in(a, b) |
-
示例:
=
,!=
,>
,>=
,<
,<=
###################### 举一反三 ###################### mysql> select * -> from enroll201926010204 -> where score >= 70; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 12 rows in set (0.00 sec)
between …… and
,not between …… and
####################################### 使用时注意between a and b 要遵循a < b ####################################### mysql> select * from enroll201926010204 -> where score between 70 and 90; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 11 rows in set (0.00 sec) ####################################### not between and 同理 ####################################### mysql> select * from enroll201926010204 -> where score not between 70 and 90; +-------------+-----------+---------------+---------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+---------+---------+-------+ | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | +-------------+-----------+---------------+---------+---------+-------+ 4 rows in set (0.00 sec)
in(……)
,not in(……)
####################################### 判断目标数是否在in()中 ####################################### mysql> select * from enroll201926010204 -> where score in(76, 90, 98); +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | +-------------+-----------+---------------+----------+---------+-------+ 4 rows in set (0.00 sec) ####################################### not in() ####################################### mysql> select * from enroll201926010204 -> where score not in(76, 90, 98); +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 11 rows in set (0.00 sec)
is null
,is not null
mysql> select * from enroll201926010204 -> where score is null; +-------------+-----------+---------------+------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+------+---------+-------+ | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | +-------------+-----------+---------------+------+---------+-------+ 1 row in set (0.00 sec)
对于筛字符串类型所用到的条件语句
操作符 | 作用 | 例子 |
---|---|---|
= | 判断是否相等 | name = ‘abcd’ |
!= 或者 <> | 判断是否不相等 | name != 'abcd’ name <> ‘abcd’ |
like | 没有用通配符等价于 = | name like ‘abcd’ |
not like | 没有用通配符等价于 != | name not like ‘abcd’ |
% | 通配符,代表匹配0个以上的字符 | “%AT%” 代表AT 前后可以有任意字符,可用于模糊查询 |
下划线 _ | 和% 相似,代表1个字符 | |
in (…) | 判断是否在这个范围内 | name in(‘a’, ‘b’, ‘c’) |
not in(…) | 判断是否不在这个范围内 | name not in(‘a’, ‘b’, ‘c’)- - |
-
示例:
=
,!=
,<>
####################################### = ####################################### mysql> select * from enroll201926010204 -> where cno = 'C31010001'; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 4 rows in set (0.00 sec) ####################################### != ####################################### mysql> select * from enroll201926010204 -> where cno != 'C31010001'; +-------------+-----------+---------------+---------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+---------+---------+-------+ | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | +-------------+-----------+---------------+---------+---------+-------+ 12 rows in set (0.00 sec) ####################################### <> 作用和 != 相同 ####################################### mysql> select * from enroll201926010204 -> where cno <> 'C31010001'; +-------------+-----------+---------------+---------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+---------+---------+-------+ | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | +-------------+-----------+---------------+---------+---------+-------+ 12 rows in set (0.00 sec)
like
,not like
####################################### 没有加通配符的like 如同 = ####################################### mysql> select * from enroll201926010204 -> where cno like 'C'; Empty set (0.00 sec) mysql> select * from enroll201926010204 -> where cno like 'C31010001'; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 4 rows in set (0.00 sec) ####################################### 没有通配符的 not like 如同 != ####################################### mysql> select * from enroll201926010204 -> where cno not like 'C31010001'; +-------------+-----------+---------------+---------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+---------+---------+-------+ | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | +-------------+-----------+---------------+---------+---------+-------+ 12 rows in set (0.00 sec)
%
####################################### 通配符一般和like一起用,可以用于模糊查询 ####################################### mysql> select * from enroll201926010204 -> where cno like '%C31%'; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 5 rows in set (0.00 sec) ####################################### 通配符在前,表示目标字符串前面可以有任意字符 ####################################### mysql> select * from enroll201926010204 -> where cno like '%C31'; Empty set (0.00 sec) ####################################### 通配符在后,表示目标字符串后面可以有任意字符 ####################################### mysql> select * from enroll201926010204 -> where cno like 'C31%'; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 5 rows in set (0.00 sec)
_
####################################### 英文的_表示当前下划线所处的位置可以是任意字符, 但只能是一个字符 ####################################### mysql> select * from enroll201926010204 -> where cno like 'C3101000_'; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 5 rows in set (0.00 sec) mysql> select * from enroll201926010204 -> where cno like '_31010001'; +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 4 rows in set (0.00 sec)
思考题:如何找出目标字符中带有“_” ?是直接使用
select 字段名 from 表名 where 字段名 like '%_%'
吗?答:不是的,按照题目中给出的sql语句会找出表中的所有数据,正确的sql语句为
select 字段名 from 表名 where 字段名 like '%\_%'
,我们通过转义字符\
将_
转义为普通的下划线,从而是他生效in(……)
,not in(……)
####################################### 判断目标数是否 在/不在 in()中 ####################################### mysql> select * from enroll201926010204 -> where cno in('c31010001', 'c31010002'); +-------------+-----------+---------------+----------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+----------+---------+-------+ | 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL | | 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 | | 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 | | 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 | +-------------+-----------+---------------+----------+---------+-------+ 5 rows in set (0.00 sec) mysql> select * from enroll201926010204 -> where cno not in('c31010001', 'c31010002'); +-------------+-----------+---------------+---------+---------+-------+ | sno | cno | year_semester | tno | classno | score | +-------------+-----------+---------------+---------+---------+-------+ | 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 | | 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 | | 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 | | 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 | | 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 | | 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 | | 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 | | 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 | | 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 | | 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 | | 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 | +-------------+-----------+---------------+---------+---------+-------+ 11 rows in set (0.00 sec)
多条件查询
- 语法:
and
,or
关键字and
:表示并且,查询结果必须都满足所有的条件or
:表示或者,查询结果满足其中一个条件即可
- 示例:
########################################
and:同时满足class = 'A' 和score >= 90
########################################
mysql> select * from enroll201926010204
-> where classno = 'A' and
-> score >= 90;
+-------------+-----------+---------------+----------+---------+-------+
| sno | cno | year_semester | tno | classno | score |
+-------------+-----------+---------------+----------+---------+-------+
| 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 |
| 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 |
+-------------+-----------+---------------+----------+---------+-------+
2 rows in set (0.00 sec)
########################################
or:满足class = 'A' 或者score >= 70其中之一
########################################
mysql> select * from enroll201926010204
-> where classno = 'A' or
-> score >= 70;
+-------------+-----------+---------------+----------+---------+-------+
| sno | cno | year_semester | tno | classno | score |
+-------------+-----------+---------------+----------+---------+-------+
| 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 |
| 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 |
| 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 |
| 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 |
| 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 |
| 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 |
| 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 |
| 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 |
| 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 |
| 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 |
| 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 |
| 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 |
| 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 |
+-------------+-----------+---------------+----------+---------+-------+
15 rows in set (0.00 sec)
思考题:如果and和or同时出现的话,执行语句会有优先级吗?
答:and优先级要比or高;会先执行and,再执行or!!
所以:当你的查询语句需要同时带有and和or语句时,请使用英文括号来改变执行顺序,如同数学表达式中使用括号改变执行顺序一般!
排序数据
单一字段排序
- 语法:
order by 字段名 asc/desc
- asc代表升序,desc代表降序,如果不加这些,则mysql默认升序
- 示例:
mysql> select * from enroll201926010204
-> order by score;
+-------------+-----------+---------------+----------+---------+-------+
| sno | cno | year_semester | tno | classno | score |
+-------------+-----------+---------------+----------+---------+-------+
| 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL |
| 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 |
| 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 |
| 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 |
| 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 |
| 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 |
| 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 |
| 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 |
| 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 |
| 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 |
| 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 |
| 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 |
| 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 |
| 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 |
+-------------+-----------+---------------+----------+---------+-------+
16 rows in set (0.00 sec)
mysql> select * from enroll201926010204
-> order by score asc;
+-------------+-----------+---------------+----------+---------+-------+
| sno | cno | year_semester | tno | classno | score |
+-------------+-----------+---------------+----------+---------+-------+
| 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL |
| 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 |
| 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 |
| 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 |
| 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 |
| 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 |
| 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 |
| 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 |
| 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 |
| 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 |
| 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 |
| 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 |
| 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 |
| 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 |
+-------------+-----------+---------------+----------+---------+-------+
16 rows in set (0.00 sec)
mysql> select * from enroll201926010204
-> order by score desc;
+-------------+-----------+---------------+----------+---------+-------+
| sno | cno | year_semester | tno | classno | score |
+-------------+-----------+---------------+----------+---------+-------+
| 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 |
| 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 |
| 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 |
| 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 |
| 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 |
| 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 |
| 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 |
| 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 |
| 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 |
| 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 |
| 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 |
| 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 |
| 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 |
| 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL |
+-------------+-----------+---------------+----------+---------+-------+
16 rows in set (0.00 sec)
多个字段排序
- 语法:
order by 字段名1 asc/desc, 字段名2 asc/desc ……
- 解释:mysql执行多字段排序时,是先将第一个字段进行排序,第一个字段如果出现相等的情况,才会使用第二个字段在第一个字段的基础上排序
- 示例:
mysql> select * from enroll201926010204
-> order by score asc, sno asc;
+-------------+-----------+---------------+----------+---------+-------+
| sno | cno | year_semester | tno | classno | score |
+-------------+-----------+---------------+----------+---------+-------+
| 20182401101 | C31010002 | 2019-1 | NULL | NULL | NULL |
| 20182401103 | H24030008 | 2020-1 | 2001009 | A | 55 |
| 20182401102 | H24030008 | 2020-1 | 2001009 | A | 66 |
| 20182911101 | C24020005 | 2019-2 | 2009007 | A | 66 |
| 20182401104 | H24030006 | 2019-1 | 2004124 | B | 76 |
| 20182401101 | H24030008 | 2020-1 | 2001009 | A | 83 |
| 20182709102 | C24020005 | 2019-2 | 2009007 | A | 83 |
| 20182401103 | H24030006 | 2019-1 | 2004124 | A | 86 |
| 20182401104 | H24030008 | 2020-1 | 2001009 | B | 87 |
| 20182911101 | C31010001 | 2019-1 | 20011024 | B | 87 |
| 20182401101 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401202 | C31010001 | 2019-1 | 20011024 | A | 88 |
| 20182401101 | H24030006 | 2019-1 | 2004124 | A | 89 |
| 20182401102 | X27030019 | 2019-2 | 2007004 | B | 90 |
| 20182709101 | C31010001 | 2019-1 | 20011024 | A | 90 |
| 20182401102 | H24030006 | 2019-1 | 2004124 | A | 98 |
+-------------+-----------+---------------+----------+---------+-------+
16 rows in set (0.00 sec)