数据库_MySQL: mysql子查询和视图

更过MySQL使用操作，点击查看专栏: MySQL数据库_命令详解

一.子查询

创建database,表格及导入数据

#设置编码
set names gbk;

#选择数据库
create database test1;
use test1;

#创建学生表
create table stu(
id int unsigned primary key auto_increment,
name varchar(45) not null,
age tinyint(3) not null default 0,
sex char(1) not null default '男',
city varchar(10) not null default '',
class_id int not null comment '关联院系表class_id'
)engine=myisam charset=utf8;
insert into stu
values 
(null,'邢飞',18,'男','江西',1),
(null,'杨大传',36,'男','山西',2),
(null,'许娜',28,'女','北京',1),
(null,'王磊',44,'女','上海',3),
(null,'江荣华',32,'女','河南',3),
(null,'赵龙',20,'男','河北',1);  #插入错误就用set names utf8;


#创建院系表（class）
create table class(
id int unsigned primary key auto_increment,
name varchar(45) not null,
room varchar(45) not null,
tel varchar(12) not null
)engine=myisam charset=utf8;

insert into class
values 
(null,'计算机系','行政楼302','010-11111111'),
(null,'数学系','科研楼108','010-22222222'),
(null,'物理系','行政楼305','010-33333333');


#分数表
create table score (
stu_id int unsigned comment '关联学生表ID',
id int unsigned primary key auto_increment comment '分数表ID',
math int unsigned not null comment '数学',
chinese int unsigned not null comment '语文',
english int unsigned not null comment '英语'
)engine=myisam charset=utf8;

#分数数据插入
insert into score
values 
(1,null,80,58,99),
(2,null,11,99,29),
(3,null,32,53,89),
(4,null,99,28,99),
(5,null,77,68,19),
(6,null,33,18,29);

mysql> select * from stu;
+----+-----------+-----+-----+--------+----------+
| id | name      | age | sex | city   | class_id |
+----+-----------+-----+-----+--------+----------+
|  1 | 邢飞      |  18 | 男  | 江西   |        1 |
|  2 | 杨大传    |  36 | 男  | 山西   |        2 |
|  3 | 许娜      |  28 | 女  | 北京   |        1 |
|  4 | 王磊      |  44 | 女  | 上海   |        3 |
|  5 | 江荣华    |  32 | 女  | 河南   |        3 |
|  6 | 赵龙      |  20 | 男  | 河北   |        1 |
+----+-----------+-----+-----+--------+----------+

mysql> select * from class;
+----+--------------+--------------+--------------+
| id | name         | room         | tel          |
+----+--------------+--------------+--------------+
|  1 | 计算机系     | 行政楼302    | 010-11111111 |
|  2 | 数学系       | 科研楼108    | 010-22222222 |
|  3 | 物理系       | 行政楼305    | 010-33333333 |
+----+--------------+--------------+--------------+

mysql> select * from score;
+--------+----+------+---------+---------+
| stu_id | id | math | chinese | english |
+--------+----+------+---------+---------+
|      1 |  1 |   80 |      58 |      99 |
|      2 |  2 |   11 |      99 |      29 |
|      3 |  3 |   32 |      53 |      89 |
|      4 |  4 |   99 |      28 |      99 |
|      5 |  5 |   77 |      68 |      19 |
|      6 |  6 |   33 |      18 |      29 |
+--------+----+------+---------+---------+

1.标量子查询

返回单个值（一行一列）

mysql> select name from stu where id=3;
+--------+
| name   |
+--------+
| 许娜   |
+--------+

2.列子查询

返回多行一列

mysql> select name from stu;
+-----------+
| name      |
+-----------+
| 邢飞      |
| 杨大传    |
| 许娜      |
| 王磊      |
| 江荣华    |
| 赵龙      |
+-----------+

3.行子查询

返回一行多列

mysql> select * from stu where id = 1;
+----+--------+-----+-----+--------+----------+
| id | name   | age | sex | city   | class_id |
+----+--------+-----+-----+--------+----------+
|  1 | 邢飞   |  18 | 男  | 江西   |        1 |
+----+--------+-----+-----+--------+----------+

4.表子查询

返回多行多列

mysql> select * from stu;
+----+-----------+-----+-----+--------+----------+
| id | name      | age | sex | city   | class_id |
+----+-----------+-----+-----+--------+----------+
|  1 | 邢飞      |  18 | 男  | 江西   |        1 |
|  2 | 杨大传    |  36 | 男  | 山西   |        2 |
|  3 | 许娜      |  28 | 女  | 北京   |        1 |
|  4 | 王磊      |  44 | 女  | 上海   |        3 |
|  5 | 江荣华    |  32 | 女  | 河南   |        3 |
|  6 | 赵龙      |  20 | 男  | 河北   |        1 |
+----+-----------+-----+-----+--------+----------+

5.exists和not exists

语法: select 列 form 表名 where exists （子查询）

含义: 判断指定查询语句是否有数据，有则执行主查询，反之则不执行

• 如果有人超过了语文80分就显示所有的学生

mysql> select * from stu where exists (select * from score where chinese > 80); 
+----+-----------+-----+-----+--------+----------+
| id | name      | age | sex | city   | class_id |
+----+-----------+-----+-----+--------+----------+
|  1 | 邢飞      |  18 | 男  | 江西   |        1 |
|  2 | 杨大传    |  36 | 男  | 山西   |        2 |
|  3 | 许娜      |  28 | 女  | 北京   |        1 |
|  4 | 王磊      |  44 | 女  | 上海   |        3 |
|  5 | 江荣华    |  32 | 女  | 河南   |        3 |
|  6 | 赵龙      |  20 | 男  | 河北   |        1 |
+----+-----------+-----+-----+--------+----------+

6.特殊关键词(some,any,all)

some=any查询的结果中,满足任意一个给定的条件即可,相当于or.
all,查询的结果中，满足所有给定的条件,相当于and.

• 查询 ID大于（‘邢飞’, ‘杨大传’, ‘赵龙’）对应的id号的学生信息

mysql> select * from stu where id > any (select id from stu where name i('邢飞', '杨大传', '赵龙'));
+----+-----------+-----+-----+--------+----------+
| id | name      | age | sex | city   | class_id |
+----+-----------+-----+-----+--------+----------+
|  2 | 杨大传    |  36 | 男  | 山西   |        2 |
|  3 | 许娜      |  28 | 女  | 北京   |        1 |
|  4 | 王磊      |  44 | 女  | 上海   |        3 |
|  5 | 江荣华    |  32 | 女  | 河南   |        3 |
|  6 | 赵龙      |  20 | 男  | 河北   |        1 |
+----+-----------+-----+-----+--------+----------+

mysql> select * from stu where id > all (select id from stu where name i('邢飞', '杨大传', '赵龙'));
Empty set (0.00 sec)

二.视图(view)

反复使用的复杂sql语句,可以通过视图进行简化查询.

视图（view）是存在数据库中虚拟的表（只是结构没有数据，数据来源于其他表）

1.创建视图

语法: create view 视图名 as SQL语句

• 查找所有人对应的id,name,classname和room

create view v_stuclass as 
select stu.id,stu.name,class.name as classname,class.room from stu left joiclass ostu.class_id = class.id;

# 因为两个表的name重复,class的名字起别名classname

mysql> show tables;
+-------------------+
| Tables_in_test1 |
+-------------------+
| class             |
| score             |
| stu               |
| v_stuclass        |
+-------------------+  #这里多了一个 v_stuclass 的table

mysql> select * from v_stuclass; 
+----+-----------+--------------+--------------+
| id | name      | classname    | room         |
+----+-----------+--------------+--------------+
|  1 | 邢飞      | 计算机系     | 行政楼302    |
|  3 | 许娜      | 计算机系     | 行政楼302    |
|  6 | 赵龙      | 计算机系     | 行政楼302    |
|  2 | 杨大传    | 数学系       | 科研楼108    |
|  4 | 王磊      | 物理系       | 行政楼305    |
|  5 | 江荣华    | 物理系       | 行政楼305    |
+----+-----------+--------------+--------------+

注意: 视图不保存数据!

一般为了区分统一给视图名字加前缀.

2.修改视图

语法: alter view 视图名 as 新SQL语句

mysql> alter view v_stuclass as select name from stu;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_stuclass;
+-----------+
| name      |
+-----------+
| 邢飞      |
| 杨大传    |
| 许娜      |
| 王磊      |
| 江荣华    |
| 赵龙      |
+-----------+

3.删除视图

语法: drop view [if exists] 视图名;

mysql> drop view v_stuclass;
Query OK, 0 rows affected (0.00 sec)

4.显示视图

语法: show create view 视图名\G