数据库day2_partone

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)