文档章节

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

swift_hua
 swift_hua
发布于 2017/08/03 16:16
字数 1285
阅读 3
收藏 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
成都
程序员
oracle数据库基础语言

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

Romanceling
2016/04/27
53
0
史上最简单的 MySQL 教程

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

qq_35246620
2017/04/26
0
0
Django基础-Model数据库多表操作(一对多)

创建多表外键关联 models.py 注:外键关联后,外键关联字段3名称在数据库中自动添加后缀id,即变为 【字段3id】 setting.py 创建生成表 表数据操作 增加 在views.py中添加视图函数,要提前引...

ZHAO_JH
09/11
0
0
oracle中查询所有外键引用到某张表的记录

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

狮子暴走
2014/03/02
0
0
MySQL学习笔记(三)

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

ws199358
2016/09/08
14
0

没有更多内容

加载失败,请刷新页面

加载更多

Mac OS X下Maven的安装与配置

Mac OS X 安装Maven: 下载 Maven, 并解压到某个目录。例如/Users/robbie/apache-maven-3.3.3 打开Terminal,输入以下命令,设置Maven classpath $ vi ~/.bash_profile 添加下列两行代码,之后...

TonyStarkSir
今天
3
0
关于编程,你的练习是不是有效的?

最近由于工作及Solution项目的影响,我在重新学习DDD和领域建模的一些知识。然后,我突然就想到了这个问题,以及我是怎么做的? 对于我来说,提升技能的项目会有四种: 纯兴趣驱动的项目。即...

问题终结者
今天
4
0
打开eclipse出现an error has occurred see the log file

解决方法: 1,打开eclipse安装目录下的eclipse.ini文件; 2,打开的文本文件最后添加一行 --add-modules=ALL-SYSTEM 3,保存重新打开Eclipse。...

任梁荣
昨天
4
0
搞定Northwind示例数据库,无论哪个版本的SQLServer都受用

Northwind数据库 从这里可以找到突破口: http://social.msdn.microsoft.com/Forums/zh-CN/Vsexpressvb/thread/8490a1c6-9018-40c9-aafb-df9f79d29cde 下面是MSDN: http://msdn2.microsoft......

QQZZFT
昨天
1
0
mysql主从同步,安装配置操作

准备 两台mysql服务,我这里准备了如下: 主库:192.168.176.128 从库:192.168.176.131 如何在Linux上安装mysql服务,请看https://blog.csdn.net/qq_18860653/article/details/80250499 操作...

小致dad
昨天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部