mysql/mariadb学习记录——查询
程序员文章站
2022-04-24 10:11:28
连接查询：同时设计两个及以上的表的查询 连接条件或连接谓词：用来连接两个表的条件一般格式： [<表名1>]<列名1> <比较运算符> [<表名2>]<列名2> [<表名1>]<列名1> between [<表名2>]<列名2> and [<表名2>]<列名3> 等值连接： 连接运算符为= 查询每个学 ......
连接查询：同时设计两个及以上的表的查询
连接条件或连接谓词：用来连接两个表的条件一般格式：
[<表名1>]<列名1> <比较运算符> [<表名2>]<列名2>
[<表名1>]<列名1> between [<表名2>]<列名2> and [<表名2>]<列名3>
等值连接：
连接运算符为=
查询每个学生以及选修课程的情况
mysql> select student.*, sc.* from student,sc where student.sno=sc.sno; +-------+--------+------+------+-------+-------+-----+-------+ | sno | sname | ssex | sage | sdept | sno | cno | grade | +-------+--------+------+------+-------+-------+-----+-------+ | 95001 | 李勇 | 男 | 20 | CS | 95001 | 1 | 92 | | 95001 | 李勇 | 男 | 20 | CS | 95001 | 2 | 85 | | 95001 | 李勇 | 男 | 20 | CS | 95001 | 3 | 88 | | 95002 | 刘晨 | 女 | 19 | IS | 95002 | 2 | 90 | | 95002 | 刘晨 | 女 | 19 | IS | 95002 | 3 | 80 | | 95004 | 张立 | 男 | 20 | IS | 95004 | 2 | 65 | | 95004 | 张立 | 男 | 20 | IS | 95004 | 3 | NULL | | 95004 | 张立 | 男 | 20 | IS | 95004 | 4 | NULL | | 95005 | 张三 | 男 | 23 | CS | 95005 | 2 | 84 | | 95005 | 张三 | 男 | 23 | CS | 95005 | 4 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 1 | 87 | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 2 | 80 | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 3 | 90 | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 4 | 95 | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 5 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 6 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 96001 | 7 | 86 | | 97001 | 李四 | 男 | 26 | EN | 97001 | 4 | NULL | | 97001 | 李四 | 男 | 26 | EN | 97001 | 5 | NULL | +-------+--------+------+------+-------+-------+-----+-------+
自身连接：一个表与自己进行连接
需要给表起别名以示区别，且必须使用前缀
//查询每门课的间接先修课 mysql> select first.cno,second.cpno from course first,course second where first.cpno=second.cno; +-----+------+ | cno | cpno | +-----+------+ | 3 | 5 | | 1 | 7 | | 4 | NULL | | 7 | NULL | | 5 | 6 | +-----+------+ 5 rows in set (0.05 sec) //查询没门课先修课的名称 mysql> select FIRST.cno,FIRST.cname,FIRST.cpno,SECOND.cname from course FIRST,course SECOND where FIRST.cpno=SECOND.cno; +-----+--------------+------+--------------+ | cno | cname | cpno | cname | +-----+--------------+------+--------------+ | 1 | 数据库 | 5 | 数据结构 | | 3 | 信息系统 | 1 | 数据库 | | 4 | 操作系统 | 6 | 数据处理 | | 5 | 数据结构 | 7 | C语言 | | 7 | C语言 | 6 | 数据处理 | +-----+--------------+------+--------------+
外连接：
外连接操作以指定表为连接主体，将主体中不满足连接条件的元组一并输出
左外连接：
列出左边关系中所有的元组；
右外连接：
列出右边关系中所有的元组；
//左外连接 outer可去除 mysql> select student.sno,sname,ssex,sage,sdept,cno,grade from student left outer join sc on(sc.sno=student.sno); +-------+--------+------+------+-------+------+-------+ | sno | sname | ssex | sage | sdept | cno | grade | +-------+--------+------+------+-------+------+-------+ | 12001 | bgg | M | 26 | CS | NULL | NULL | | 94001 | 山寨 | 男 | 29 | CS | NULL | NULL | | 95001 | 李勇 | 男 | 20 | CS | 1 | 92 | | 95001 | 李勇 | 男 | 20 | CS | 2 | 85 | | 95001 | 李勇 | 男 | 20 | CS | 3 | 88 | | 95002 | 刘晨 | 女 | 19 | IS | 2 | 90 | | 95002 | 刘晨 | 女 | 19 | IS | 3 | 80 | | 95003 | 王敏 | 女 | 19 | MA | NULL | NULL | | 95004 | 张立 | 男 | 20 | IS | 2 | 65 | | 95004 | 张立 | 男 | 20 | IS | 3 | NULL | | 95004 | 张立 | 男 | 20 | IS | 4 | NULL | | 95005 | 张三 | 男 | 23 | CS | 2 | 84 | | 95005 | 张三 | 男 | 23 | CS | 4 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 1 | 87 | | 96001 | 刘军 | 男 | 30 | IS | 2 | 80 | | 96001 | 刘军 | 男 | 30 | IS | 3 | 90 | | 96001 | 刘军 | 男 | 30 | IS | 4 | 95 | | 96001 | 刘军 | 男 | 30 | IS | 5 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 6 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 7 | 86 | | 96004 | 芙蓉 | 女 | 32 | CH | NULL | NULL | | 97001 | 李四 | 男 | 26 | EN | 4 | NULL | | 97001 | 李四 | 男 | 26 | EN | 5 | NULL | +-------+--------+------+------+-------+------+-------+ 23 rows in set (0.05 sec) //右外连接 outer可去除 mysql> select student.sno,sname,ssex,sage,sdept,cno,grade from student right outer join sc on(sc.sno=student.sno); +-------+--------+------+------+-------+-----+-------+ | sno | sname | ssex | sage | sdept | cno | grade | +-------+--------+------+------+-------+-----+-------+ | 95001 | 李勇 | 男 | 20 | CS | 1 | 92 | | 95001 | 李勇 | 男 | 20 | CS | 2 | 85 | | 95001 | 李勇 | 男 | 20 | CS | 3 | 88 | | 95002 | 刘晨 | 女 | 19 | IS | 2 | 90 | | 95002 | 刘晨 | 女 | 19 | IS | 3 | 80 | | 95004 | 张立 | 男 | 20 | IS | 2 | 65 | | 95004 | 张立 | 男 | 20 | IS | 3 | NULL | | 95004 | 张立 | 男 | 20 | IS | 4 | NULL | | 95005 | 张三 | 男 | 23 | CS | 2 | 84 | | 95005 | 张三 | 男 | 23 | CS | 4 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 1 | 87 | | 96001 | 刘军 | 男 | 30 | IS | 2 | 80 | | 96001 | 刘军 | 男 | 30 | IS | 3 | 90 | | 96001 | 刘军 | 男 | 30 | IS | 4 | 95 | | 96001 | 刘军 | 男 | 30 | IS | 5 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 6 | NULL | | 96001 | 刘军 | 男 | 30 | IS | 7 | 86 | | 97001 | 李四 | 男 | 26 | EN | 4 | NULL | | 97001 | 李四 | 男 | 26 | EN | 5 | NULL | +-------+--------+------+------+-------+-----+-------+ 19 rows in set (0.05 sec)
多表连接：两个以上的表进行连接
mysql> select student.sno,sname,cname,grade from student,sc,course where student.sno=sc.sno and sc.cno=course.cno; +-------+--------+--------------+-------+ | sno | sname | cname | grade | +-------+--------+--------------+-------+ | 95001 | 李勇 | 数据库 | 92 | | 96001 | 刘军 | 数据库 | 87 | | 95001 | 李勇 | 高等数学 | 85 | | 95002 | 刘晨 | 高等数学 | 90 | | 95004 | 张立 | 高等数学 | 65 | | 95005 | 张三 | 高等数学 | 84 | | 96001 | 刘军 | 高等数学 | 80 | | 95001 | 李勇 | 信息系统 | 88 | | 95002 | 刘晨 | 信息系统 | 80 | | 95004 | 张立 | 信息系统 | NULL | | 96001 | 刘军 | 信息系统 | 90 | | 95004 | 张立 | 操作系统 | NULL | | 95005 | 张三 | 操作系统 | NULL | | 96001 | 刘军 | 操作系统 | 95 | | 97001 | 李四 | 操作系统 | NULL | | 96001 | 刘军 | 数据结构 | NULL | | 97001 | 李四 | 数据结构 | NULL | | 96001 | 刘军 | 数据处理 | NULL | | 96001 | 刘军 | C语言 | 86 | +-------+--------+--------------+-------+
排序：
select [ ] from <表名> order by <列名> [desc/asc]；
mysql> select sno,sname from student order by sno; //默认为升序 +-------+--------+ | sno | sname | +-------+--------+ | 12001 | bgg | | 94001 | 山寨 | | 95001 | 李勇 | | 95002 | 刘晨 | | 95003 | 王敏 | | 95004 | 张立 | | 95005 | 张三 | | 96001 | 刘军 | | 96004 | 芙蓉 | | 97001 | 李四 | +-------+--------+ mysql> select sno,sname from student order by sno asc; //asc 修饰为升序 +-------+--------+ | sno | sname | +-------+--------+ | 12001 | bgg | | 94001 | 山寨 | | 95001 | 李勇 | | 95002 | 刘晨 | | 95003 | 王敏 | | 95004 | 张立 | | 95005 | 张三 | | 96001 | 刘军 | | 96004 | 芙蓉 | | 97001 | 李四 | +-------+--------+ 10 rows in set (0.05 sec) mysql> select sno,sname from student order by sno desc; //desc 修饰为降序 +-------+--------+ | sno | sname | +-------+--------+ | 97001 | 李四 | | 96004 | 芙蓉 | | 96001 | 刘军 | | 95005 | 张三 | | 95004 | 张立 | | 95003 | 王敏 | | 95002 | 刘晨 | | 95001 | 李勇 | | 94001 | 山寨 | | 12001 | bgg | +-------+--------+
mysql> select sno,sname from student order by sno desc,sname asc; //先按sno降序 再按sname升序
+-------+--------+
| sno | sname |
+-------+--------+
| 97001 | 李四 |
| 96004 | 芙蓉 |
| 96001 | 刘军 |
| 95005 | 张三 |
| 95004 | 张立 |
| 95003 | 王敏 |
| 95002 | 刘晨 |
| 95001 | 李勇 |
| 94001 | 山寨 |
| 12001 | bgg |
+-------+--------+
10 rows in set (0.05 sec)
选择的between操作（在mysql/mariadb中是闭区间）:
select * from <表名> where <列名> between 'a' and 'b';
mysql> select * from student where sno between '94001' and '96001'; +-------+--------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-------+--------+------+------+-------+ | 94001 | 山寨 | 男 | 29 | CS | | 95001 | 李勇 | 男 | 20 | CS | | 95002 | 刘晨 | 女 | 19 | IS | | 95003 | 王敏 | 女 | 19 | MA | | 95004 | 张立 | 男 | 20 | IS | | 95005 | 张三 | 男 | 23 | CS | | 96001 | 刘军 | 男 | 30 | IS | +-------+--------+------+------+-------+
//not between and
mysql> select * from student where sno not between '94001' and '96001';
+-------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 12001 | bgg | M | 26 | CS |
| 96004 | 芙蓉 | 女 | 32 | CH |
| 97001 | 李四 | 男 | 26 | EN |
+-------+--------+------+------+-------+
mysql模糊查询（LIKE）关键字：
% 替代一个或多个字符；
_ 仅替代一个字符；
[charlist] 字符列中的任何单一字符；
[!charlist] 或 [^charlist] 不在字符列中的任何单一字符；
//查询sno以95开头的学生信息 mysql> select * from student where sno like '95%'; +-------+--------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-------+--------+------+------+-------+ | 95001 | 李勇 | 男 | 20 | CS | | 95002 | 刘晨 | 女 | 19 | IS | | 95003 | 王敏 | 女 | 19 | MA | | 95004 | 张立 | 男 | 20 | IS | | 95005 | 张三 | 男 | 23 | CS | +-------+--------+------+------+-------+ 5 rows in set (0.05 sec) //查询sno以01结尾的学生信息 mysql> select * from student where sno like '%01'; +-------+--------+------+------+-------+ | sno | sname | ssex | sage | sdept | +-------+--------+------+------+-------+ | 12001 | bgg | M | 26 | CS | | 94001 | 山寨 | 男 | 29 | CS | | 95001 | 李勇 | 男 | 20 | CS | | 96001 | 刘军 | 男 | 30 | IS | | 97001 | 李四 | 男 | 26 | EN | +-------+--------+------+------+-------+ 5 rows in set (0.05 sec)//查询sno以01结尾的学生信息
//not like
mysql> select * from student where sno not like '%01';
+-------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 95002 | 刘晨 | 女 | 19 | IS |
| 95003 | 王敏 | 女 | 19 | MA |
| 95004 | 张立 | 男 | 20 | IS |
| 95005 | 张三 | 男 | 23 | CS |
| 96004 | 芙蓉 | 女 | 32 | CH |
+-------+--------+------+------+-------+
IN操作符：
IN操作符允许在WHERE子句中规定多个值;
// 查询 sdept为MA，CS的信息
mysql> select * from student where sdept in ('MA','CS');
+-------+--------+------+------+-------+
| sno | sname | ssex | sage | sdept |
+-------+--------+------+------+-------+
| 12001 | bgg | M | 26 | CS |
| 94001 | 山寨 | 男 | 29 | CS |
| 95001 | 李勇 | 男 | 20 | CS |
| 95003 | 王敏 | 女 | 19 | MA |
| 95005 | 张三 | 男 | 23 | CS |
+-------+--------+------+------+-------+
2018-05-01 20:24:33
下一篇： 奇垒氏为什么会被被康熙灭全族？真相是什么