数据库学习之多表操作(三)
2023-09-14 08:56:58 时间
数据库学习之多表操作
目录
外键
——什么是外键
——增加与删除外键
INNODB支持的ON语句
多表查询之连接查询
——内连接:inner join
——外连接:left join与rihgt join
——全连接:full join
多表查询之复合条件连接查询
多表查询之子查询
以下内容为扩展和演示:http://www.cnblogs.com/yuanchenqi/articles/6357507.html
外键
什么是外键
外键是用来关联两张表的。 切记:作为外键一定要和关联主键的数据类型保持一致。 关联外键的格式:[ADD CONSTRAINT charger_fk_stu]FOREIGN KEY (charger_id) REFERENCES ClassCharger(id)
--外键约束对子表的含义: 如果在父表中找不到候选键,则不允许在子表上进行insert/update --外键约束对父表的含义: 在父表上进行update/delete以更新或删除在子表中有一条或多条对 -- 应匹配行的候选键时,父表的行为取决于:在定义子表的外键时指定的 -- on update/on delete子句
每一个班主任会对应多个学生 , 而每个学生只能对应一个班主任 先准备两张表:
父表: CREATE TABLE ClassCharger( id TINYINT PRIMARY KEY auto_increment, name VARCHAR (20), age INT , is_marriged boolean ); INSERT INTO ClassCharger (name,age,is_marriged) VALUES ("冰冰",29,0), ("丹丹",30,0), ("歪歪",27,0), ("姗姗",20,0), ("小雨",21,0); 子表: CREATE TABLE Student( id INT PRIMARY KEY auto_increment, name VARCHAR (20), charger_id TINYINT, --切记:作为外键一定要和关联主键的数据类型保持一致 FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ) ENGINE=INNODB; INSERT INTO Student(name,charger_id) VALUES ("alvin1",2), ("alvin2",4), ("alvin3",1), ("alvin4",3), ("alvin5",1), ("alvin6",3), ("alvin7",2);
现在我们有5位老师,7位同学。
如果我们现在对老师的表进行操作,比如删除某位老师,按道理来说,如果这位老师有管理的学生,就不能直接删除,除非将他的学生删除或者划到其他老师管理下。
如果你没有关联的话,就没有约束的对老师表做任何操作。
但是现在我们把老师表的id和学生表的charger_id关联起来,你的操作就会受到限制。
增加外键和删除外键
ALTER TABLE student ADD CONSTRAINT abc FOREIGN KEY(charger_id) REFERENCES classcharger(id); ALTER TABLE student DROP FOREIGN KEY abc;
INNODB支持的ON语句
-----------------innodb支持的四种方式--------------------------------------- -----cascade方式 在父表上update/delete记录时,同步update/delete掉子表的匹配记录 -----外键的级联删除:如果父表中的记录被删除,则子表中对应的记录自动被删除-------- FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE CASCADE ------set null方式 在父表上update/delete记录时,将子表上匹配记录的列设为null -- 要注意子表的外键列不能为not null FOREIGN KEY (charger_id) REFERENCES ClassCharger(id) ON DELETE SET NULL ------Restrict方式 :拒绝对父表进行删除更新操作(了解) ------No action方式 在mysql中同Restrict,如果子表中有匹配的记录,则不允许对父表对应候选键 -- 进行update/delete操作(了解)
多表查询之连接查询
-- 准备两张表 -- company.employee -- company.department create table employee( emp_id int auto_increment primary key not null, emp_name varchar(50), age int, dept_id int ); insert into employee(emp_name,age,dept_id) values ('A',19,200), ('B',26,201), ('C',30,201), ('D',24,202), ('E',20,200), ('F',38,204); create table department( dept_id int, dept_name varchar(100) ); insert into department values (200,'人事部'), (201,'技术部'), (202,'销售部'), (203,'财政部');
内连接:inner join
查询两张表中都有的关联数据,相当于利用条件从笛卡尔积结果中筛选出了正确的结果。
外连接:left join与rihgt join
(1)左外连接:在内连接的基础上增加左边有右边没有的结果 select * from employee left join department on employee.dept_id = department.dept_id; (2)右外连接:在内连接的基础上增加右边有左边没有的结果 select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id; (3)全外连接:在内连接的基础上增加左边有右边没有的和右边有左边没有的结果 -- mysql不支持全外连接 full JOIN -- mysql可以使用此种方式间接实现全外连接 select * from employee RIGHT JOIN department on employee.dept_id = department.dept_id UNION select * from employee LEFT JOIN department on employee.dept_id = department.dept_id;
全连接:full join(笛卡尔积查询)
多表查询之复合条件连接查询
查询员工年龄大于等于25岁的部门 SELECT DISTINCT department.dept_name FROM employee,department WHERE employee.dept_id = department.dept_id AND age>25; 以内连接的方式查询employee和department表,并且以age字段的升序方式显示 select employee.emp_id,employee.emp_name,employee.age,department.dept_name from employee,department where employee.dept_id = department.dept_id order by age asc;
多表查询之子查询
子查询是将一个查询语句嵌套在另一个查询语句中。 内层查询语句的查询结果,可以为外层查询语句提供查询条件。 子查询中可以包含:IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字 还可以包含比较运算符:= 、 !=、> 、<等
1. 带IN关键字的子查询 查询employee表,但dept_id必须在department表中出现过 select * from employee where dept_id IN (select dept_id from department);
2. 带比较运算符的子查询 =、!=、>、>=、<、<=、<> 查询员工年龄大于等于25岁的部门 select dept_id,dept_name from department where dept_id IN (select DISTINCT dept_id from employee where age>=25);
3. 带EXISTS关键字的子查询 EXISTS关字键字表示存在。在使用EXISTS关键字时,内层查询语句不返回查询的记录。 而是返回一个真假值。Ture或False 当返回Ture时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询 select * from employee WHERE EXISTS (SELECT dept_name from department where dept_id=203); department表中存在dept_id=203,Ture select * from employee WHERE EXISTS (SELECT dept_name from department where dept_id=205); -- Empty set (0.00 sec) ps: create table t1(select * from t2);
相关文章
- 使用YII框架的migrate迁移数据库
- 数据库学习之让索引加快查询速度(四)
- 数据库学习之数据库常用操作(一)
- [SQLite] SQLite学习手册(数据库和事务)
- spring学习笔记(20)数据库事务并发与锁详解
- 【DATAGUARD 学习】使用duplicate 创建物理standby 数据库
- 【华为云技术分享】Entity Framework Core 捕获数据库变动
- phalcon 连接多个数据库 phalcon multi-database
- MySQL数据库学习笔记(一)----MySQL 5.6.21的安装和配置(setup版)
- MySQL数据库学习笔记(四)----MySQL聚合函数、控制流程函数(含navicat软件的介绍)
- MySQL数据库学习笔记(九)----JDBC的ResultSet接口(查询操作)、PreparedStatement接口重构增删改查(含SQL注入的解释)
- Redis学习(5)-Jedis(Java操作redis数据库技术)
- 什么是数据库-数据库详解
- Oracle 11gR2学习之二(创建数据库及OEM管理篇)
- Python数据库模块学习之MySQLdb
- 用Aliyun E-MapReduce集群的sqoop工具和数据库同步数据如何配置网络
- 数据库的硬迁移和mysql 5.5.38源码安装
- Android学习---通过内容提供者(ContentProvider)操作另外一个应用私有数据库的内容
- Android学习---如何创建数据库,SQLite(onCreate,onUpgrade方法)和SQLiteStudio的使用
- Java -- JDBC 学习--获取数据库链接
- 【学习总结】SQL的学习-1-初识数据库与sql
- .NET Core采用的全新配置系统[7]: 将配置保存在数据库中
- AI:人工智能领域主要方向(技术和应用)、与机器学习/深度学习的关系、数据科学关键技术与知识发现/数据挖掘/统计学/模式识别/神经计算学/数据库的关系(几张图理清之间的暧昧关系)
- DBMS/Database:数据库管理的简介、安装(注意事项等)、学习路线(基于SQLSever深入理解SQL命令语句综合篇《初级→中级→高级》/几十项代码案例集合)之详细攻略
- Java学习路线-44:JDBC数据库开发进阶
- Java学习路线-40:Java数据库编程基础操作
- SQLyog图形化l数据库的操作和学习
- 【数据库通关之路】 MySQL 全路线学习知识点梳理(下)
- 【数据库通关之路】 MySQL 全路线学习知识点梳理(中)
- mysql直接操作数据库表里的json字段的单个值?答案在这里
- MySQL数据库从入门到实战应用(学习笔记二)