DQL数据查询语句的学习
数据库执行DQL语言不会对数据库中的数据发生任何改变,而是让数据库发送查询结果到客户端(查询返回的结果其实是一张虚拟表)
语法:
SELECT 列名 FROM 表名【WHERE --> GROUP BY -->HAVING–> ORDER BY】
1.基础查询
a).查询所有列
mysql> select * from student;
+------+----------+------+--------+
| id | name | age | gender |
+------+----------+------+--------+
| 1 | aaaa | 19 | female |
| 2 | bbbbbbbb | 20 | male |
| 3 | cc | 15 | male |
| 4 | ddd | 16 | female |
| 5 | eee | 20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)
b).查询指定列
( *星号代表的是对应表的所有字段,如果我们要查询我们仅需要的字段,那我们在select的后面加上对应字段名就可以了,多个字段逗号隔开.) 实例如下:
mysql> select id,name,gender from student;
+------+----------+--------+
| id | name | gender |
+------+----------+--------+
| 1 | aaaa | female |
| 2 | bbbbbbbb | male |
| 3 | cc | male |
| 4 | ddd | female |
| 5 | eee | female |
+------+----------+--------+
5 rows in set (0.00 sec)
2.条件查询
主要结合where的使用
between…and: 介于…和…之间
and:逻辑与
or:逻辑或
in / not in:类似于Python中的成员运算符
is / is not: 类似于Python中的身份运算符 , 常用语判断null值, 如:name is null
a).查询性别为女,并且年龄为20的记录
mysql> select * from student where gender='female' and age=20;
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 5 | eee | 20 | female |
+------+------+------+--------+
1 row in set (0.00 sec)
b).查询编号为1或者姓名为ddd的记录
mysql> select * from student where id='1' or name='ddd';
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | aaaa | 19 | female |
| 4 | ddd | 16 | female |
+------+------+------+--------+
2 rows in set (0.00 sec)
c).查询编号分别为1,2,3的记录
mysql> select * from student where id in('1','2','3');
+------+----------+------+--------+
| id | name | age | gender |
+------+----------+------+--------+
| 1 | aaaa | 19 | female |
| 2 | bbbbbbbb | 20 | male |
| 3 | cc | 15 | male |
+------+----------+------+--------+
3 rows in set (0.00 sec)
d).查询编号不为1,2,3的记录
mysql> select * from student where id not in('1','2','3');
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 4 | ddd | 16 | female |
| 5 | eee | 20 | female |
+------+------+------+--------+
2 rows in set (0.00 sec)
e).查询年龄在15~20之间的记录
mysql> select * from student where age between 15 and 20;
+------+----------+------+--------+
| id | name | age | gender |
+------+----------+------+--------+
| 1 | aaaa | 19 | female |
| 2 | bbbbbbbb | 20 | male |
| 3 | cc | 15 | male |
| 4 | ddd | 16 | female |
| 5 | eee | 20 | female |
+------+----------+------+--------+
5 rows in set (0.00 sec)
3.模糊查询
where 子句中=表示精准查询
like:一般情况下结合where子句使用
通配符:
_: 匹配任意一个字符,短横线个数表示字符个数
%:匹配0~n个字符【n大于等于1】
演示:
a).查询姓名由4个字符组成的记录
mysql> select * from student where name like '____';
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | aaaa | 19 | female |
+------+------+------+--------+
1 row in set (0.00 sec)
b).查询以a开头的记录
mysql> select * from student where name like 'a%';
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 1 | aaaa | 19 | female |
+------+------+------+--------+
1 row in set (0.01 sec)
c).查询姓名中包含b的记录
mysql> select * from student where name like '%b%';
+------+----------+------+--------+
| id | name | age | gender |
+------+----------+------+--------+
| 2 | bbbbbbbb | 20 | male |
+------+----------+------+--------+
1 row in set (0.00 sec)
d).查询姓名中第2个字母为c的记录
mysql> select * from student where name like '_c%';
+------+------+------+--------+
| id | name | age | gender |
+------+------+------+--------+
| 3 | cc | 15 | male |
+------+------+------+--------+
1 row in set (0.00 sec)