create table info_user(
U_id int(11) not null primary key auto_increment,
U_name varchar(100) not null
);
insert into info_user(U_name) values("张三"),("李四"),("王五");
create table info_user_consumer(
Id int(11) not null primary key auto_increment,
C_id int (11) not null,
U_id int(11) not null
);
insert into info_user_consumer(C_id,U_id) values1(1,1),(2,1),(3,2),(4,2),(5,2),(6,3),(7,1);
[子查询充当字段]
查询所有学生信息,但显示班级的名称
select
stuId,
(select claName from classInfo where classInfo.claId = student.classId) as className,
stuName,
age
from student;
查询所有班级信息,但还要显示每个班级的学生个数
select
claId,
claName,
(select count(*) from student where classInfo.claId=student.classId) as stuCount
from classInfo;
查询歌手信息,并显示歌手开演唱会的次数
select
gsId,
gsName,
(select count(*) from ych a where a.gsId=b.gsId) as ychCount
from geshou b;
select * from student
where classId in (select claId from classInfo where claName!='php1303' and claName!='php1307');
select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b);
select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
mysql> select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)),U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| mysql |
| performance_schema |
| phpmyadmin |
| sondemo |
| test |
| webauth |
| xilian168 |
+--------------------+
9 rows in set (0.00 sec)
mysql> use sondemo;
Database changed
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)),U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
| (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) | U_name | C_count |
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
| 3 | ?? | 1 |
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
1 row in set (0.02 sec)
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
| 3 | ?? | 1 |
+------+--------+---------+
1 row in set (0.02 sec)
mysql> select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1052 (23000): Column 'U_id' in field list is ambiguous
mysql> explain select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1052 (23000): Column 'U_id' in field list is ambiguous
mysql> explain select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | info_user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
3 rows in set (1.78 sec)
mysql> select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
| 3 | ?? | 1 |
+------+--------+---------+
1 row in set (0.66 sec)
mysql> explain select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| 1 | PRIMARY | <derived4> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | info_user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 4 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
| 2 | SUBQUERY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
5 rows in set (0.00 sec)
mysql> select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
| 3 | ?? | 1 |
+------+--------+---------+
1 row in set (0.00 sec)
mysql> explain select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | info_user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
3 rows in set (0.00 sec)
mysql>
U_id int(11) not null primary key auto_increment,
U_name varchar(100) not null
);
insert into info_user(U_name) values("张三"),("李四"),("王五");
create table info_user_consumer(
Id int(11) not null primary key auto_increment,
C_id int (11) not null,
U_id int(11) not null
);
insert into info_user_consumer(C_id,U_id) values1(1,1),(2,1),(3,2),(4,2),(5,2),(6,3),(7,1);
[子查询充当字段]
查询所有学生信息,但显示班级的名称
select
stuId,
(select claName from classInfo where classInfo.claId = student.classId) as className,
stuName,
age
from student;
查询所有班级信息,但还要显示每个班级的学生个数
select
claId,
claName,
(select count(*) from student where classInfo.claId=student.classId) as stuCount
from classInfo;
查询歌手信息,并显示歌手开演唱会的次数
select
gsId,
gsName,
(select count(*) from ych a where a.gsId=b.gsId) as ychCount
from geshou b;
select * from student
where classId in (select claId from classInfo where claName!='php1303' and claName!='php1307');
select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b);
select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
mysql> select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2;
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.36 MySQL Community Server (GPL)
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)),U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cdcol |
| mysql |
| performance_schema |
| phpmyadmin |
| sondemo |
| test |
| webauth |
| xilian168 |
+--------------------+
9 rows in set (0.00 sec)
mysql> use sondemo;
Database changed
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)),U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
| (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) | U_name | C_count |
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
| 3 | ?? | 1 |
+-----------------------------------------------------------------------------------------------------------------+--------+---------+
1 row in set (0.02 sec)
mysql> select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
| 3 | ?? | 1 |
+------+--------+---------+
1 row in set (0.02 sec)
mysql> select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1052 (23000): Column 'U_id' in field list is ambiguous
mysql> explain select U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
ERROR 1052 (23000): Column 'U_id' in field list is ambiguous
mysql> explain select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | info_user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
3 rows in set (1.78 sec)
mysql> select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
| 3 | ?? | 1 |
+------+--------+---------+
1 row in set (0.66 sec)
mysql> explain select (select U_id from ((select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b)) U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| 1 | PRIMARY | <derived4> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | info_user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 4 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
| 2 | SUBQUERY | <derived3> | system | NULL | NULL | NULL | NULL | 1 | |
| 3 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
5 rows in set (0.00 sec)
mysql> select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+------+--------+---------+
| U_id | U_name | C_count |
+------+--------+---------+
| 3 | ?? | 1 |
+------+--------+---------+
1 row in set (0.00 sec)
mysql> explain select b.U_id,U_name,C_count from (select U_id,count(C_id) C_count from info_user_consumer group by U_id having C_count<2) b inner join info_user on b.U_id = info_user.U_id;
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | info_user | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | info_user_consumer | ALL | NULL | NULL | NULL | NULL | 7 | Using temporary; Using filesort |
+----+-------------+--------------------+--------+---------------+---------+---------+-------+------+---------------------------------+
3 rows in set (0.00 sec)
mysql>