开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> 数据库学习笔记2——SQL语句 -> 正文阅读

[大数据]数据库学习笔记2——SQL语句

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 表名

注意，这种方式的缺点是：
1. 效率低：在java程序中写这样的代码时，执行代码时*会有转换为字符串的额外开销时间
2. 可读性差：不清楚这个表中所有的字段
示例：

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)