MySQL--05(子查询&视图)

子查询

添加 insert into / create

修改 update / alter

读取 select /show

删除 delete /drop

在一个select 语句中，嵌入另一个select 语句，那么被嵌入的select语句被称之为子查询语句。

• 关系： 子查询是主查询辅助查询，充当条件，要么当作数据源，要么当值

• 子查询分类： 一个数据，返回一列，返回一行

• 标量子查询：子查询的结果返回一个值，作为主查询的条件

MariaDB [shop]> select * from  areas where pid=(select id from areas where area='广州市');
+-----+--------+------+
| id  | area   | pid  |
+-----+--------+------+
| 230 | 越秀区 |   98 |
| 231 | 荔湾区 |   98 |
| 232 | 海珠区 |   98 |
| 233 | 天河区 |   98 |
| 234 | 白云区 |   98 |
| 235 | 黄埔区 |   98 |
| 236 | 番禺区 |   98 |
| 237 | 花都区 |   98 |
| 238 | 南沙区 |   98 |
| 239 | 增城区 |   98 |
| 240 | 从化区 |   98 |
+-----+--------+------+
11 rows in set (0.00 sec)

MariaDB [shop]> select * from student where cls_id=(select  id from class where teacher='孙静香');
+----+--------+--------+
| id | sname  | cls_id |
+----+--------+--------+
|  1 | 谢逊   |      1 |
|  2 | 周大福 |      1 |
|  3 | 高程远 |      1 |
+----+--------+--------+
3 rows in set (0.00 sec)

MariaDB [shop]> select count(id) from student where cls_id=(select id from class where teacher='孙静香');
+-----------+
| count(id) |
+-----------+
|         3 |
+-----------+
1 row in set (0.00 sec)

列子查询；子查询返回一个字段下的所有或者多个值或者一个值，作为主查询条件

MariaDB [shop]> update student set cls_id=(select id from class where teacher='王三多') where sname='谢三逊';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [shop]> select * from student;
+----+--------+--------+
| id | sname  | cls_id |
+----+--------+--------+
|  1 | 谢逊   |      1 |
|  2 | 周大福 |      1 |
|  3 | 高程远 |      1 |
|  4 | 谢三逊 |      2 |
+----+--------+--------+
4 rows in set (0.00 sec)

MariaDB [shop]> select * from student where cls_id in (select id from class where teacher='孙静香' or teacher='王三多');
+----+--------+--------+
| id | sname  | cls_id |
+----+--------+--------+
|  1 | 谢逊   |      1 |
|  2 | 周大福 |      1 |
|  3 | 高程远 |      1 |
|  4 | 谢三逊 |      2 |
+----+--------+--------+
4 rows in set (0.00 sec)

MariaDB [shop]> select id from class where teacher='孙静香' or teacher='王三多';
+----+
| id |
+----+
|  1 |
|  2 |
+----+
2 rows in set (0.00 sec)

行子查询

MariaDB [shop]> create table xs(
    -> id int primary key auto_increment,
    -> sname varchar(20),
    -> height float(3,2),
    -> age tinyint
    -> )engine=innodb default charset=utf8;
Query OK, 0 rows affected (0.02 sec)

MariaDB [shop]> desc xs;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| sname  | varchar(20) | YES  |     | NULL    |                |
| height | float(3,2)  | YES  |     | NULL    |                |
| age    | tinyint(4)  | YES  |     | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

MariaDB [shop]> insert into xs (sname,height,age)values('吴亦凡',1.25,10),('陈兴',1.50,27),('张琦',1.40,12);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

MariaDB [shop]> select max(height),max(age) from xs;
+-------------+----------+
| max(height) | max(age) |
+-------------+----------+
|        1.50 |       33 |
+-------------+----------+
1 row in set (0.00 sec)

MariaDB [shop]> select *  from xs where (height,age)=(select max(height),max(age) from xs);
Empty set (0.00 sec)

MariaDB [shop]> update xs set age=12 where id=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [shop]> select *  from xs where (height,age)=(select max(height),max(age) from xs);
+----+-------+--------+------+
| id | sname | height | age  |
+----+-------+--------+------+
|  2 | 陈兴  |   1.50 |   27 |
+----+-------+--------+------+
1 row in set (0.00 sec)

MariaDB [shop]>

总结：有多步查询，子查询的结果作为主查询的条件，或者数据,

在列查询里 in ：主查询 where 条件 in （子查询）

视图

通俗来讲，视图就是一条select查询的结果集

命令格式：

create view 视图名字 as select语句;

建议 v_xxx

MariaDB [books]> create view v_stu as select s.id as id, s.name as name, jsj.c_name as jsj,yy.c_name as yy from student s left join score jsj on s.id=jsj.stu_id  left join score yy on  s.id=yy.stu_id where jsj.c_name='计算机' and yy.c_name='英语';
Query OK, 0 rows affected (0.01 sec)

MariaDB [books]> show tables;
+-----------------+
| Tables_in_books |
+-----------------+
| books           |
| score           |
| student         |
| v_stu           |
+-----------------+
4 rows in set (0.00 sec)

查看视图

命令格式：show tables;

就是一张虚拟表

使用视图

select * from 视图名;

删除视图

drop view 视图名字

修改视图

create or replace view 视图名字 as select语句;

MariaDB [books]> create or replace view v_stu as select * from student;
Query OK, 0 rows affected (0.01 sec)

MariaDB [books]>  show tables;
+-----------------+
| Tables_in_books |
+-----------------+
| books           |
| score           |
| student         |
| v_stu           |
+-----------------+
4 rows in set (0.00 sec)

视图作用

提高了重性，可以对数据重构，但不影响数据库结构，提高了安全性能，让数据更清晰

外键

在创建表时，添加外键

CREATE TABLE dingdan (
  id int(11) NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  title varchar(50) DEFAULT NULL COMMENT '订单标题',
  add_time datetime DEFAULT NULL COMMENT '下单时间',
  user int(11) DEFAULT NULL COMMENT '用户编号',
  status enum('待付款','待发货','待收货','已退单','已完成') DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;


create table t(
    id int primary key auto_increment comment '主键 ',
    name varchar(20) comment '测试名字',
    oid int,
    foreign key(oid) references dingdan(id)
);

创建表时，添加外键的语法：

foreign key(外键字段) references 外部表名字(外部表主键) 

insert into dingdan (title,add_time,user,status)values('购买苹果手机','2019-04-01 12:00:00', '张三',3);

• 创建表后，添加外键的语法（不指定外键名称）

alter table 表名 add foreign key(本表外键字段) references 外部表名(外部表主键);

• 创建表后，添加外键，指定名称

alter table 表名 add constraint 外键名 foreign key(本表外键字段) references 外部表名(外部表主键);

　　删除外键

MariaDB [shop]> alter table t drop foreign key t_ibfk_1;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: