基本查询
- 查询表中所有的数据列结果:select * from 表名;
mysql> select *from student;
+----+----------+-----+-----------+-----------+-------+
| id | name | age | address | tel | hobby |
+----+----------+-----+-----------+-----------+-------+
| 1 | karin | 22 | 河北 | 123456 | NULL |
| 3 | xiaoming | 17 | 上海 | 123 | NULL |
| 4 | lihua | 18 | 广东 | 123456789 | NULL |
| 5 | lili | 12 | 西安 | 123234 | NULL |
| 7 | xiaohong | 19 | 桂林 | 324 | NULL |
| 8 | xiaoqing | 89 | 石家庄 | 213 | NULL |
+----+----------+-----+-----------+-----------+-------+
6 rows in set (0.00 sec)
- 查询某些字段:select
列名1
,列名2
from 表名;
mysql> select `id`,`name` from student;
+----+----------+
| id | name |
+----+----------+
| 1 | karin |
| 3 | xiaoming |
| 4 | lihua |
| 5 | lili |
| 7 | xiaohong |
| 8 | xiaoqing |
+----+----------+
6 rows in set (0.00 sec)
去重 distinct
mysql> select `address` from student;
+-----------+
| address |
+-----------+
| 河北 |
| 上海 |
| 广东 |
| 西安 |
| 桂林 |
| 石家庄 |
| 河北 |
+-----------+
7 rows in set (0.00 sec)
- 默认是all,返回所有查询的数据,distinct去掉重复的数据
mysql> select distinct `address` from student;
+-----------+
| address |
+-----------+
| 河北 |
| 上海 |
| 广东 |
| 西安 |
| 桂林 |
| 石家庄 |
+-----------+
6 rows in set (0.01 sec)
数据库的列(表达式)
- 查询系统版本
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.22 |
+-----------+
1 row in set (0.01 sec)
- 计算结果
mysql> select 100*2-1 as 计算结果;
+--------------+
| 计算结果 |
+--------------+
| 199 |
+--------------+
1 row in set (0.01 sec)
- 查询自增的步长
mysql> select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
| 1 |
+----------------------------+
1 row in set (0.01 sec)
- 学生年龄查询 (过年+1)
mysql> select `age`+1 as 过年后 from student;
+-----------+
| 过年后 |
+-----------+
| 23 |
| 18 |
| 19 |
| 13 |
| 20 |
| 90 |
| 19 |
+-----------+
7 rows in set (0.00 sec)
条件查询
- 查询15~20岁之间的name
and
mysql> select name,`age` from student
-> where age >15 and age <20;
+-----------+-----+
| name | age |
+-----------+-----+
| xiaoming | 17 |
| lihua | 18 |
| xiaohong | 19 |
| xiaoming | 18 |
+-----------+-----+
4 rows in set (0.00 sec)
&&
mysql> select name,`age` from student
-> where age >15 && age <20;
+-----------+-----+
| name | age |
+-----------+-----+
| xiaoming | 17 |
| lihua | 18 |
| xiaohong | 19 |
| xiaoming | 18 |
+-----------+-----+
4 rows in set (0.01 sec)
模糊查询(区间)
mysql> select name ,`age` from student
-> where age between 15 and 20;
+-----------+-----+
| name | age |
+-----------+-----+
| xiaoming | 17 |
| lihua | 18 |
| xiaohong | 19 |
| xiaoming | 18 |
+-----------+-----+
4 rows in set (0.01 sec)
- 查询非18岁的name
!=
mysql> select name ,`age` from student
-> where age!=18;
+----------+-----+
| name | age |
+----------+-----+
| karin | 22 |
| xiaoming | 17 |
| lili | 12 |
| xiaohong | 19 |
| xiaoqing | 89 |
+----------+-----+
5 rows in set (0.01 sec)
not
mysql> select name,`age` from student
-> where not age =18;
+----------+-----+
| name | age |
+----------+-----+
| karin | 22 |
| xiaoming | 17 |
| lili | 12 |
| xiaohong | 19 |
| xiaoqing | 89 |
+----------+-----+
5 rows in set (0.01 sec)
模糊查询
- 查询姓张的学生
like
mysql> select `id`,`name` from student
-> where name like '张%';
+----+-----------+
| id | name |
+----+-----------+
| 4 | 张三 |
| 10 | 张小红 |
+----+-----------+
2 rows in set (0.00 sec)
- 查询姓张的,名字后面有一个字的学生
mysql> select `id`,`name` from student
-> where name like '张_';
+----+--------+
| id | name |
+----+--------+
| 4 | 张三 |
+----+--------+
1 row in set (0.00 sec)
- 查询姓张的,名字后面有两个字的学生
mysql> select `id`,`name` from student
-> where name like '张__';
+----+-----------+
| id | name |
+----+-----------+
| 10 | 张小红 |
+----+-----------+
1 row in set (0.00 sec)
- 查询id 1 2 3 的学生
in
mysql> select `id`,`name` from student
-> where `id` in (1,2,3);
+----+-----------+
| id | name |
+----+-----------+
| 1 | 赵六天 |
| 3 | 王五 |
+----+-----------+
2 rows in set (0.01 sec)
- 查询15~20岁的name
between and
mysql> select name ,`age` from student
-> where age between 15 and 20;
+-----------+-----+
| name | age |
+-----------+-----+
| xiaoming | 17 |
| lihua | 18 |
| xiaohong | 19 |
| xiaoming | 18 |
+-----------+-----+
4 rows in set (0.01 sec)
- 查询在河北的学生
mysql> select `id`,`name` from student
-> where `address` in ('河北');
+----+-----------+
| id | name |
+----+-----------+
| 1 | 赵六天 |
| 9 | 小明 |
+----+-----------+
2 rows in set (0.01 sec)
- 查询hobby不为空的同学
not null
mysql> select `id`,`name` from student
-> where `hobby` is not null;
+----+-----------+
| id | name |
+----+-----------+
| 10 | 张小红 |
+----+-----------+
1 row in set (0.01 sec)
- 查询hobby为空的同学
null
mysql> select `id`,`name` from student
-> where `hobby` is null;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 赵六天 |
| 3 | 王五 |
| 4 | 张三 |
| 5 | 小刘 |
| 7 | 小李子 |
| 8 | 小红 |
| 9 | 小明 |
+----+-----------+
7 rows in set (0.01 sec)
连接查询
常见的Join查询图
- 新建两张表,数据如下:
mysql> select *from student;
+----+-----------+-----+-----------+-----------+--------+
| id | name | age | address | tel | hobby |
+----+-----------+-----+-----------+-----------+--------+
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL |
| 3 | 王五 | 17 | 上海 | 123 | NULL |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL |
| 9 | 小明 | 18 | 河北 | 123 | NULL |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 |
+----+-----------+-----+-----------+-----------+--------+
8 rows in set (0.00 sec)
mysql> select*from studys;
+----+------+---------------+
| id | name | email |
+----+------+---------------+
| 1 | 1 | 12342@qq.com |
| 2 | 5 | 3242@163.com |
| 3 | 2 | 123412@qq.com |
| 4 | 234 | 23423@qq.com |
+----+------+---------------+
4 rows in set (0.00 sec)
- 笛卡尔积
mysql> select *from student,studys;
mysql> select *from student,studys;
+----+-----------+-----+-----------+-----------+--------+----+------+---------------+
| id | name | age | address | tel | hobby | id | name | email |
+----+-----------+-----+-----------+-----------+--------+----+------+---------------+
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 1 | 1 | 12342@qq.com |
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 2 | 5 | 3242@163.com |
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 3 | 2 | 123412@qq.com |
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 4 | 234 | 23423@qq.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 1 | 1 | 12342@qq.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 2 | 5 | 3242@163.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 3 | 2 | 123412@qq.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 4 | 234 | 23423@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 1 | 1 | 12342@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 2 | 5 | 3242@163.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 3 | 2 | 123412@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 4 | 234 | 23423@qq.com |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | 1 | 1 | 12342@qq.com |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | 2 | 5 | 3242@163.com |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | 3 | 2 | 123412@qq.com |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | 4 | 234 | 23423@qq.com |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | 1 | 1 | 12342@qq.com |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | 2 | 5 | 3242@163.com |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | 3 | 2 | 123412@qq.com |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | 4 | 234 | 23423@qq.com |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | 1 | 1 | 12342@qq.com |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | 2 | 5 | 3242@163.com |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | 3 | 2 | 123412@qq.com |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | 4 | 234 | 23423@qq.com |
| 9 | 小明 | 18 | 河北 | 123 | NULL | 1 | 1 | 12342@qq.com |
| 9 | 小明 | 18 | 河北 | 123 | NULL | 2 | 5 | 3242@163.com |
| 9 | 小明 | 18 | 河北 | 123 | NULL | 3 | 2 | 123412@qq.com |
| 9 | 小明 | 18 | 河北 | 123 | NULL | 4 | 234 | 23423@qq.com |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | 1 | 1 | 12342@qq.com |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | 2 | 5 | 3242@163.com |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | 3 | 2 | 123412@qq.com |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | 4 | 234 | 23423@qq.com |
+----+-----------+-----+-----------+-----------+--------+----+------+---------------+
32 rows in set (0.01 sec)
- inner join - 内连接查询
inner join查询交集,也就是两表公有部分结果集。
mysql> select*from student as a
-> inner join studys as b
-> on a.id=b.id;
+----+-----------+-----+---------+-----------+-------+----+------+---------------+
| id | name | age | address | tel | hobby | id | name | email |
+----+-----------+-----+---------+-----------+-------+----+------+---------------+
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 1 | 1 | 12342@qq.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 3 | 2 | 123412@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 4 | 234 | 23423@qq.com |
+----+-----------+-----+---------+-----------+-------+----+------+---------------+
3 rows in set (0.02 sec)
- left join - 左连接查询
left join 返回左表中所有的记录和右表中与连接字段相等的记录,右表中没有的记录补null。
mysql> select *from student as a
-> left join studys as b
-> on a.id=b.id;
+----+-----------+-----+-----------+-----------+--------+------+------+---------------+
| id | name | age | address | tel | hobby | id | name | email |
+----+-----------+-----+-----------+-----------+--------+------+------+---------------+
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 1 | 1 | 12342@qq.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 3 | 2 | 123412@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 4 | 234 | 23423@qq.com |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | NULL | NULL | NULL |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | NULL | NULL | NULL |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | NULL | NULL | NULL |
| 9 | 小明 | 18 | 河北 | 123 | NULL | NULL | NULL | NULL |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | NULL | NULL | NULL |
+----+-----------+-----+-----------+-----------+--------+------+------+---------------+
8 rows in set (0.02 sec)
- right join - 右连接查询
right join 返回右表中所有的记录和左表中与连接字段相等的记录,左表中没有的记录补null。
mysql> select *from student as a
-> right join studys as b
-> on a.id=b.id;
+------+-----------+------+---------+-----------+-------+----+------+---------------+
| id | name | age | address | tel | hobby | id | name | email |
+------+-----------+------+---------+-----------+-------+----+------+---------------+
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 1 | 1 | 12342@qq.com |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | 5 | 3242@163.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 3 | 2 | 123412@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 4 | 234 | 23423@qq.com |
+------+-----------+------+---------+-----------+-------+----+------+---------------+
4 rows in set (0.00 sec)
- left join b.key is null 查询左表独有部分
mysql> select*from student as a
-> left join studys as b
-> on a.id=b.id
-> where b.id is null;
+----+-----------+-----+-----------+--------+--------+------+------+-------+
| id | name | age | address | tel | hobby | id | name | email |
+----+-----------+-----+-----------+--------+--------+------+------+-------+
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | NULL | NULL | NULL |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | NULL | NULL | NULL |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | NULL | NULL | NULL |
| 9 | 小明 | 18 | 河北 | 123 | NULL | NULL | NULL | NULL |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | NULL | NULL | NULL |
+----+-----------+-----+-----------+--------+--------+------+------+-------+
5 rows in set (0.03 sec)
- right join a.key is null查询右表独有
mysql> select*from student as a
-> right join studys as b
-> on a.id=b.id
-> where a.id is null;
+------+------+------+---------+------+-------+----+------+--------------+
| id | name | age | address | tel | hobby | id | name | email |
+------+------+------+---------+------+-------+----+------+--------------+
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | 5 | 3242@163.com |
+------+------+------+---------+------+-------+----+------+--------------+
1 row in set (0.10 sec)
- 全连接查询
mysql> select*from student as a
-> left join studys as b
-> on a.id=b.id
-> union
-> select*from student as a
-> right join studys as b
-> on a.id=b.id;
+------+-----------+------+-----------+-----------+--------+------+------+---------------+
| id | name | age | address | tel | hobby | id | name | email |
+------+-----------+------+-----------+-----------+--------+------+------+---------------+
| 1 | 赵六天 | 22 | 河北 | 123456 | NULL | 1 | 1 | 12342@qq.com |
| 3 | 王五 | 17 | 上海 | 123 | NULL | 3 | 2 | 123412@qq.com |
| 4 | 张三 | 18 | 广东 | 123456789 | NULL | 4 | 234 | 23423@qq.com |
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | NULL | NULL | NULL |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | NULL | NULL | NULL |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | NULL | NULL | NULL |
| 9 | 小明 | 18 | 河北 | 123 | NULL | NULL | NULL | NULL |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | 5 | 3242@163.com |
+------+-----------+------+-----------+-----------+--------+------+------+---------------+
9 rows in set (0.05 sec)
- 单a与独b查询
mysql> select*from student as a
-> left join studys as b
-> on a.id=b.id
-> where b.id is null
-> union
-> select *from student as a
-> right join studys as b
-> on a.id=b.id
-> where a.id is null;
+------+-----------+------+-----------+--------+--------+------+------+--------------+
| id | name | age | address | tel | hobby | id | name | email |
+------+-----------+------+-----------+--------+--------+------+------+--------------+
| 5 | 小刘 | 12 | 西安 | 123234 | NULL | NULL | NULL | NULL |
| 7 | 小李子 | 19 | 桂林 | 324 | NULL | NULL | NULL | NULL |
| 8 | 小红 | 89 | 石家庄 | 213 | NULL | NULL | NULL | NULL |
| 9 | 小明 | 18 | 河北 | 123 | NULL | NULL | NULL | NULL |
| 10 | 张小红 | 18 | 北京 | 123 | 唱歌 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | 5 | 3242@163.com |
+------+-----------+------+-----------+--------+--------+------+------+--------------+
6 rows in set (0.01 sec)