首页 / MYSQL / MySQL中常用操作--子查询
MySQL中常用操作--子查询
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL中常用操作--子查询,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5548字,纯文字阅读大概需要8分钟。
内容图文
![MySQL中常用操作--子查询](/upload/InfoBanner/zyjiaocheng/514/b31e04049d09430d87c89a5ef54f1ea8.jpg)
NOT IN :
mysql> SELECT * FROM stu WHERE score NOT IN (SELECT score FROM scoreLevel); +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 4 | 周星星 | 0 | | 5 | 凌凌漆 | 100 | | 7 | 浩南 | 60 | | 8 | 山鸡 | 50 | | 9 | 孙猴子 | 85 | | 10 | 李四 | 55 | +----+----------+-------+ 8 rows in set (0.00 sec)
=, !=, >, >=, <, <=......
SELECT * FROM stu WHERE score>=(SELECT score FROM scoreLevel WHERE id=1);
mysql> SELECT * FROM stu WHERE score>=(SELECT score FROM scoreLevel WHERE id=1); +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | +----+----------+-------+ 4 rows in set (0.00 sec)
使用关键字EXISTS查询时, 内层查询语句不返回查询的记录, 而是返回一个真假值. 如果内层的查询语句查询到满足条件的语句的记录, 就返回一个真值(true), 否则返回一个假值(false).当返回的值为true时, 外层查询语句将进行查询, 当返回结果为false时, 外层查询语句不进行查询或者查询不出任何记录.
SELECT * FROM stu WHERE EXISTS (SELECT score FROM scoreLevel WHERE id=2);
mysql> SELECT * FROM stu WHERE EXISTS (SELECT score FROM scoreLevel WHERE id=10); Empty set (0.00 sec) mysql> SELECT * FROM stu WHERE EXISTS (SELECT score FROM scoreLevel WHERE id=2); +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 3 | xiaohong | 80 | | 4 | 周星星 | 0 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 7 | 浩南 | 60 | | 8 | 山鸡 | 50 | | 9 | 孙猴子 | 85 | | 10 | 李四 | 55 | +----+----------+-------+ 10 rows in set (0.00 sec)
![MySQL中常用操作--子查询 - 文章图片](/upload/getfiles/0001/2021/4/25/20210425091916306.jpg)
mysql> SELECT * FROM stu WHERE score>=ANY(SELECT score FROM scoreLevel);--表示在 stu 中 >= scoreLevel 中的最小值(70)//SOME 和 ANY效果一样 +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 3 | xiaohong | 80 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 9 | 孙猴子 | 85 | +----+----------+-------+ 6 rows in set (0.00 sec)
mysql> SELECT * FROM stu WHERE score>=ALL(SELECT score FROM scoreLevel);--表示在 stu 中 >= scoreLevel 中的最大值(90) +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | +----+----------+-------+ 4 rows in set (0.00 sec)
CREATE .......SELECT .......:
mysql> CREATE table excellentStudent( -> id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, -> username VARCHAR(100) NOT NULL, -> score TINYINT UNSIGNED NOT NULL -> )ENGINE=INNODB CHARSET=UTF8 SELECT * FROM stu WHERE score>=ALL(SELECT score FROM scoreLevel); Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> SELECT * FROM excellentstudent; +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | +----+----------+-------+ 4 rows in set (0.00 sec)
INSERT.......SELECT.........:
mysql> INSERT excellentStudent(username) SELECT username FROM user; Query OK, 7 rows affected, 1 warning (0.01 sec) Records: 7 Duplicates: 0 Warnings: 1 mysql> SELECT * FROM excellentstudent; +----+----------+-------+ | id | username | score | +----+----------+-------+ | 1 | liub | 95 | | 2 | xiaoming | 100 | | 5 | 凌凌漆 | 100 | | 6 | 达叔 | 90 | | 7 | a | 0 | | 8 | b | 0 | | 9 | c | 0 | | 10 | d | 0 | | 11 | e | 0 | | 12 | f | 0 | | 13 | g | 0 | +----+----------+-------+ 11 rows in set (0.00 sec)
去掉字段的重复值:
SELECT DISTINCT(字段名) FROM 表名;
创建一个与table_name2一样的表结构的table_name1(创建好的table_name1有结构, 但是内容为空.):
CREATE TABLE table_name1 LIKE table_name2;
-
合并查询结果
合并查询结果是将多个SELECT语句的查询结果合并到一起, 使用关键字 UNION 和 UNION ALL.其中, UNION 是将多个表中的记录去重后合并到一起, 而 UNION ALL 只是简单的合并操作.
UNION:
SELECT 字段名称,... FROM tbl_name1
UNION
SELECT 字段名称... FROM tbl_name2;
UNION ALL:
SELECT 字段名称,... FROM tbl_name1
UNION ALL
SELECT 字段名称... FROM tbl_name2;
MySQL中常用操作--子查询
标签:就是 -- 重复 ble 语句 子查询 tin info from
本文系统来源:https://www.cnblogs.com/iceliu/p/11624892.html
内容总结
以上是互联网集市为您收集整理的MySQL中常用操作--子查询全部内容,希望文章能够帮你解决MySQL中常用操作--子查询所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。