文档章节

五、外键、联合查询、子查询、视图

swift_hua
 swift_hua
发布于 2017/08/03 16:16
字数 1285
阅读 3
收藏 0
点赞 0
评论 0

-- 创建外键

create table my_foreign1(
id int primary key auto_increment,
name varchar(20) not null comment '学生姓名',
c_id int comment '班级id',    -- 普通字段


-- 增加外键
foreign key(c_id) references my_class(id)
)charset utf8;

-- 创建表
create table my_foreign2(
id int primary key auto_increment,
name varchar(20) not null comment '学生姓名',
c_id int comment '班级id'    -- 普通字段
)charset utf8;

-- 增加外键
alter table my_foreign2 add
-- 指定外键名
constraint student_class_1
-- 指定外键字段
foreign key(c_id)
-- 引用父表主键
references my_class(id);

-- 删除外键
alter table my_foreign1 drop  foreign key my_foreign1_ibfk_1;

-- 插入数据:外键字段在父表中不存在
insert into my_foreign2 values(null,'张自忠',4); -- 没有4班级
insert into my_foreign2 values(null,'项羽',1); 
insert into my_foreign2 values(null,'刘邦',2); 
insert into my_foreign2 values(null,'韩信',2); 

-- 更新父表记录
update my_class set id = 4 where id = 1; -- 失败: id=1记录已经被学生引用
update my_class set id = 4 where id = 3; -- 可以: 没有引用

-- 插入数据
insert into my_foreign1 values(null,'马超',3);
-- 增加外键
alter table my_foreign1 add foreign key(c_id) references my_class(id);

-- 创建外键: 指定模式: 删除置空,更新级联
create table my_foreign3(
id int primary key auto_increment,
name varchar(20) not null,
c_id int,
-- 增加外键
foreign key(c_id)
-- 引用表
references my_class(id)
-- 指定删除模式
on delete set null
-- 指定更新默认
on update cascade)charset utf8;

-- 插入数据
insert into my_foreign3 values(null,'刘备',1),
(null,'曹操',1),
(null,'孙权',1),
(null,'诸葛亮',2),
(null,'周瑜',2);

-- 更新父表主键
update my_class set id = 3 where id = 1;

-- 删除父表主键
delete from my_class where id = 2;

-- 联合查询
select * from my_class
union -- 默认去重
select * from my_class;

select * from my_class
union all -- 不去重
select * from my_class;

select id,c_name,room from my_class
union all -- 不去重
select name,number,id from my_student;

-- 需求: 男生升序,女生降序(年龄)
(select * from my_student where sex = '男' order by age asc limit 9999999)
union 
(select * from my_student where sex = '女' order by age desc limit 9999999);

-- 标量子查询
select * from my_student where c_id = (select id from my_class where c_name = 'PHP0710');
select * from my_student having c_id = (select id from my_class where c_name = 'PHP0710');

-- 列子查询
select * from my_student where c_id in(select id from my_class);

-- any,some,all
select * from my_student where c_id =any(select id from my_class);
select * from my_student where c_id =some(select id from my_class);
select * from my_student where c_id =all(select id from my_class);

select * from my_student where c_id !=any(select id from my_class); -- 所有结果(null除外)
select * from my_student where c_id !=some(select id from my_class); -- 所有结果(null除外)
select * from my_student where c_id !=all(select id from my_class); -- 2(null除外)


select * from my_student where
age = (select max(age) from my_student)
and
height  = (select max(height) from my_student);

-- 行子查询
select * from my_student where 
-- (age,height)称之为行元素
(age,height) = (select max(age),max(height) from my_student);

-- 表子查询
select * from my_student group by c_id order by height desc;
select * from (select * from my_student order by height desc) as student group by c_id;

-- exists子查询
select * from my_student where 
exists(select * from my_class where id = 1);

select * from my_student where 
exists(select * from my_class where id = 2);

 

-- 视图: 单表+多表
create view my_v1 as 
select * from my_student;

create view my_v2 as 
select * from my_class;

create view my_v3 as 
select * from my_student as s left join my_class c on s.c_id = c.id; -- id重复

-- 多表视图
create view my_v3 as 
select s.*,c.c_name,c.room from my_student as s 
left join my_class c 
on s.c_id = c.id;

-- 查看视图创建语句
show create view my_v3\G

-- 视图使用
select * from my_v1;
select * from my_v2;
select * from my_v3;

-- 修改视图
alter view my_v1 as
select id,name,age,sex,height,c_id from my_student;

-- 删除视图
drop view my_v4;

-- 多表视图插入数据
insert into my_v3 values(null,'itcast0008','张三丰','男',150,180,1,'PHP0326','D306');

-- 单表视图插入数据: 视图不包含所有不允许为空字段(学号)
insert into my_v1 values(null,'张无忌',68,'男',174,2);

-- 单表视图插入数据
insert into my_v2 values(2,'PHP0326','D306');

-- 多表视图删除数据
delete from my_v3 where id = 1;

-- 单表视图删除数据
delete from my_v2 where id = 4;

-- 多表视图更新数据
update my_v3 set c_id = 3 where id = 5;

-- 视图: age字段限制更新
create view my_v4 as 
select * from my_student where age > 30 with check option;
-- 表示视图的数据来源都是年龄大于30岁:where age > 30决定
-- with check option: 决定通过视图更新的时候,不能将已经得到的数据age > 30的改成小于30的

-- 将视图可以查到的数据改成小于30
update my_v4 set age = 29 where id = 1;

-- 可以修改数据让视图可以查到: 可以改,但是无效果
update my_v4 set age = 32 where id = 6;


-- 获取所有班级中最高的一个学生
create view my_v5 as 
select * from my_student order by height desc;

select * from my_v5 group by c_id;

-- 指定算法为临时表
create algorithm=temptable view my_v6 as 
select * from my_student order by height desc;

select * from my_v6 group by c_id;

-- 创建myisam表
create table my_myisam(
id int)charset utf8 engine = myisam;

-- 单表数据备份
select * into outfile 'D:/server/temp/student.txt' from my_student;
select * into outfile 'D:/server/temp/class.txt' from my_class;

-- 指定备份处理方式
select * into outfile 'D:/server/temp/class1.txt'
-- 字段处理
fields 
enclosed by '"' -- 数据使用双引号包裹
terminated by '|' -- 使用竖线分隔字段数据
-- 行处理
lines
starting by 'START:'

from my_class;

-- 还原数据
load data infile 'D:/server/temp/class1.txt'
into table my_class
-- 字段处理
fields 
enclosed by '"' -- 数据使用双引号包裹
terminated by '|' -- 使用竖线分隔字段数据
-- 行处理
lines
starting by 'START:';

-- SQL 备份
mysqldump -uroot -proot mydatabase my_student > D:/server/temp/student.sql

-- 整库备份
mysqldump -uroot -proot mydatabase > D:/server/temp/mydatabase.sql

-- 还原数据:mysql客户端还原
mysql -uroot -proot mydatabase < D:/server/temp/student.sql

-- SQL 指令还原SQL备份
source D:/server/temp/student.sql;

© 著作权归作者所有

共有 人打赏支持
swift_hua
粉丝 0
博文 33
码字总数 20868
作品 0
成都
程序员
Mysql知识总结(二)

一、数据库操作 (1)查看当前系统上已有的数据库: mysql> SHOW DATABASES;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema ......

Linux秋 ⋅ 2014/04/09 ⋅ 0

mysql复杂操作

1.联合查询—union: 把多条select语句的结果合并到一起的操作。 (select * from table1 order by days limit 3,2) union [all] (select * from table2 order by days limit 5) 使用场合:获取......

笨小熊 ⋅ 2015/06/29 ⋅ 0

oracle数据库基础语言

数据定义语言(DDL)【CREATE】创建;【ALTER】修改;【DROP】删除;【TRUNCATE】;数据库操作语言(DML)【INSERT】插入;【UPDATE】更新;【DELETE】删除;数据查询语言(DQL):【SELECT】事...

Romanceling ⋅ 2016/04/27 ⋅ 0

史上最简单的 MySQL 教程

温馨提示:本系列博文已经同步到 GitHub,如有需要的话,欢迎大家到「mysql-tutorial」进行和操作! 1 前言   数据库(Database)是按照数据结构来组织、存储和管理数据的仓库,它产生于距...

qq_35246620 ⋅ 2017/04/26 ⋅ 0

MySql基本的多表操作

多表操作 交叉查询(笛卡尔积查询): #A表中的每一条记录都对应B表中的所有记录,所以,A和B的交叉查询的条目数即为:A条目数*B条目数 语法: select 列名 from A cross join B where 条件 ...

Lunqi ⋅ 2015/08/08 ⋅ 0

oracle中查询所有外键引用到某张表的记录

oracle中查询所有外键引用到某张表的记录 其实查找这篇文章 我主要用的sql 是这句 select * from user_cons_columns t where t.constraint_name like 'FK_CTS_CONT_REINS__CRR'; 起因: 系统...

狮子暴走 ⋅ 2014/03/02 ⋅ 0

SQL(结构化查询语句)

SQL概述 SQL会话 用户利用SQL命令与关系型数据库进行交互时发生的事情,当用户与数据库建立会话时,会话就被创建了。用Connect user@database 命令可以申请连接数据库,用Disconnect命令可以...

寻找爱玩Tom ⋅ 2014/10/06 ⋅ 6

MySQL学习笔记(三)

五、多表查询 主、从表: 如果表A的主关键字(primary key)是表B的字段,则该字段称为表B的外键。 外键:用来实现参照完整性的,不的外键约束方式将可以使两张表紧密的结合起来,特别 修改或...

ws199358 ⋅ 2016/09/08 ⋅ 0

HAWQ取代传统数仓实践(十一)——维度表技术之维度合并

有一种合并维度的情况,就是本来属性相同的维度,因为某种原因被设计成重复的维度属性。例如,在销售订单示例中,随着数据仓库中维度的增加,我们会发现有些通用的数据存在于多个维度中。客户...

wzy0623 ⋅ 2017/05/31 ⋅ 0

Oracle数据基础(三)

一. 视图,序列,索引 视图 1. 什么是视图 视图(VIEW)也被称作虚表,是一组数据的逻辑表示 视图对应于一条SELECT语句,结果集被赋予一个名字,即视图名字 视图本身并不包含任何数据,它只包...

Turnsole1 ⋅ 01/07 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

LVM

LVM: 硬盘划分分区成物理卷->物理卷组成卷组->卷组划分逻辑分区。 1.磁盘分区: fdisk /dev/sdb 划分几个主分区 输入t更改每个分区类型为8e(LVM) 使用partprobe生成分区的文件:如/dev/sd...

ZHENG-JY ⋅ 23分钟前 ⋅ 0

彻底删除Microsoft Office的方法

参照此链接彻底删除Office https://support.office.com/zh-cn/article/%e4%bb%8e-pc-%e5%8d%b8%e8%bd%bd-office-9dd49b83-264a-477a-8fcc-2fdf5dbf61d8?ui=zh-CN&rs=zh-CN&ad=CN......

Kampfer ⋅ 38分钟前 ⋅ 0

大盘与个股之间关系

大盘走多:积极出手 顺势加码 大盘走空: 少量出手 退场观望 大盘做头:逆势减码 少量操作 大盘做底 : 小量建仓 小量试单

guozenhua ⋅ 40分钟前 ⋅ 0

Day16 LVM(逻辑卷管理)与磁盘故障小案例

lvm详解 简述 LVM的产生是因为传统的分区一旦分区好后就无法在线扩充空间,也存在一些工具能实现在线扩充空间但是还是会面临数据损坏的风险;传统的分区当分区空间不足时,一般的解决办法是再...

杉下 ⋅ 46分钟前 ⋅ 0

rsync实现多台linux服务器的文件同步

一、首先安装rsync,怎样安装都行,rpm,yum,还是你用源码安装都可以。因为我用的是阿里云的ESC,yum install rsync就ok了。 二、配置rsync服务 1.先建立个同步数据的帐号 123 groupadd r...

在下头真的很硬 ⋅ 59分钟前 ⋅ 0

前端基础(三):函数

字数:1685 阅读时间:5分钟 函数定义 在最新的ES规范中,声明函数有4中方法: -函数声明 -函数表达式 -构造函数Function -生成器函数 1.函数声明 语法: function name([param[, param2 [....

老司机带你撸代码 ⋅ 今天 ⋅ 0

Java虚拟机的Heap监狱

在Java虚拟机中,我是一个位高权重的大管家,他们都很怕我,尤其是那些Java 对象,我把他们圈到一个叫做Heap的“监狱”里,严格管理,生杀大权尽在掌握。 中国人把Stack翻译成“栈”,把Hea...

java高级架构牛人 ⋅ 今天 ⋅ 0

Spring MVC基本概念

只写Controller

颖伙虫 ⋅ 今天 ⋅ 0

微软重金收购GitHub的背后逻辑原来是这样的

全球最大的开发者社区GitHub网站花落谁家的问题已经敲定,微软最终以75亿美元迎娶了这位在外界看来无比“神秘”的小家碧玉。尽管此事已过去一些时日,但整个开发者世界,包括全球各地的开源社...

linux-tao ⋅ 今天 ⋅ 0

磁盘管理—逻辑卷lvm

4.10-4.12 lvm 操作流程: 磁盘分区-->创建物理卷-->划分为卷组-->划分成逻辑卷-->格式化、挂载-->扩容。 磁盘分区 注: 创建分区时需要更改其文件类型为lvm(代码8e) 分区 3 已设置为 Linu...

弓正 ⋅ 今天 ⋅ 0

没有更多内容

加载失败,请刷新页面

加载更多

下一页

返回顶部
顶部