1、配置Mysql
Docker配置Mysql可参考之前写的博文:https://blog.csdn.net/weixin_44691253/article/details/127835697
2、数据库操作
2.1 创建数据库
create database 数据库名称;
刷新数据库连接后,可以看到新创建的数据库 testsql
2.2 删除数据库
drop database 数据库名称;
再次刷新数据库连接,testsql数据库已删除
3、表操作
3.1 创建表
语法:create table 表名(字段1 类型 [约束],字段2 类型 [约束],........);Sql语句:-- 创建一个用户信息表,有这些信息:用户名、密码、电话、邮箱create table userinfo(username varchar(20), #字段名 数据类型pwd varchar(50),tel varchar(15),email varchar(20));
右键表,选择设计表,可以看到表的字段类型和长度跟创建时是一样的。
3.2 删除表
drop table 表名;
把表userinfo删除后,刷新连接,表userinfo已删除
3.3 修改表
为了后面的测试,在把userinfo创建回来。
修改表达用alert语句
3.3.1 向表中添加新字段
-- 向userinfo表中添加一列,字段名:status,类型:intalter table userinfo add status int;
再次查看设计表,看到新增的status字段,类型为int
3.3.2 删除表中的已有字段
-- 删除userinfo表中的列emailalter table userinfo drop email;
再次查看表设计,email字段已被删除
3.3.3 修改表中已有字段的名称
modify不能用来修改字段名,change可以
-- 将userinfo中的列username的名称改成login_namealter table userinfo change username login_name varchar(20);
再次查看表设计,username已经变成login_name。
后续不再截图,执行sql语句后,可自行打开设计表进行查看效果。
3.3.4 修改表中已有字段的类型
-- 将userinfo表中的pwd字段类型修改成intalter table userinfo change pwd pwd int;
3.3.5 同时修改字段名和字段类型
-- 同时修改字段名和数据类型-- pwd重命名为my_pwd,数据类型从int变成varchar(16)alter table userinfo change pwd my_pwd varchar(16);
3.3.6 修改字段在表中的显示位置
-- 将status展示在表中第一列alter table userinfo modify status int first;-- 将status展示在login_name后边:login_name将变成第一列,status成为第2列alter table userinfo modify status int after login_name;
4、数据完整性约束-实体完整性
4.1 主键约束
4.1.1 已有表添加主键约束
每个表有且只能有一个主键约束。
语法:
- 关键字:primary key
- 添加约束语法:
alter table 表名 add constraint 约束名 primary key(字段名); - 约束名: PK_字段
-- 向userinfo表中添加字段uidalter table userinfo add uid int;-- 将uid添加为主键alter table userinfo add constraint PK_uid primary key(uid);
4.1.2 删除表主键约束
语法:
删除约束语法:alter table 表名 drop primary key;
-- 删除表主键约束,注:只删除约束,不会删除字段alter table userinfo drop primary key;
4.1.3 创建表指定主键
create table student(id int primary key,name varchar(20),age int);
4.2 唯一约束
特点:不能重复,可以为空,可以添加多个
- 关键字:unique
- 添加约束语法:alter table 表名 add constraint 约束名 unique(字段名);
- 约束名: UQ_字段
- 删除约束语法:alter table 表名 drop key 约束名;
4.2.1 创建表指定唯一约束
-- 先删除之前创建的student表drop table student;-- 创建student表,指定id_card为唯一约束create table student(id int primary key,name varchar(20),age int,id_card varchar(20) unique);
4.2.2 已有表添加唯一约束
-- 将userinfo表中的login_name添加为唯一约束alter table userinfo add constraint UQ_login_name unique(login_name);
4.2.3 已有表删除唯一约束
-- 删除userinfo表中的唯一约束alter table userinfo drop key UQ_login_name;
4.3 主键自增
- 特点:从1开始,每次自身加1(在oracle中不能使用)
- 关键字: auto_increment
- 只能在创建表的时候添加主键自增的约束,而且必须是主键才可以添加
4.3.1 创建表添加主键约束且自增
-- 先删除之前创建的student表drop table student;-- 创建student表,id作为主键,且自增create table student(id int primary key auto_increment,name varchar(20),age int,id_card varchar(20) unique);
4.3.2 删除表主键自增
-- 删除student表,主键自增:仅删除自增,并没有删除主键alter table student modify id int;-- 删除student表,主键约束:同上述4.1.2alter table student drop primary key;
5、数据完整性约束-域完整性
域完整性约束:保证字段的数据准确的
域完整性包括类型约束、非空约束、默认值
1)非空约束
- 特点:字段不允许为空
- 关键字: not null
2)默认值 - 特点:设置默认的值
- 关键字: default
-- 先删除之前创建的student表drop table student;-- 创建student表,id作为主键并自增;name不能为空;性别默认为Male;id_card为唯一约束create table student(id int primary key auto_increment,name varchar(20) not null,age int not null,sex char(6) default 'Male',id_card varchar(20) unique);
6、数据完整性约束-引用完整性
一张表中通用列的取值必须参考另外一张表的主键
引用完整性有外键约束
1)外键约束
- 特点:设置外键的字段的取值只能参考另一张表中同一个字段的值
- 关键字: foreign key
- 添加外键约束的语法:
alter table 表名1 add constraint 约束名 foreign key(字段名) references 表名2(字段名) - 删除外键的语法:alter table 表名 drop foreign key 约束名;
注意:
1、主外键关联
2、外键关联字段名称可以不一样,但是类型必须一致
6.1 创建表使用外键约束
-- 先删除之前创建的student表drop table student;-- 外键约束:class_id,是classroom表的主键create table student(id int primary key auto_increment,name varchar(20) not null,age int not null,sex char(6) default 'Male',id_card varchar(20) unique,class_id int #添加外键约束);-- 创建classroom表,class_id作为主键create table classroom(class_id int PRIMARY key,class_name varchar(20));
6.2 已有表添加外键约束
- 语法:alter table 表名1 add constraint 约束名 foreign key(字段名)
references 表名2(字段名) - 约束名: FK_字段名
-- 向Student表添加外键class_id,引用自classroom表的主键class_idalter table student add constraint FK_class_id foreign key(class_id) references classroom(class_id);
6.3 删除表的外键约束
注:如果表A有外键约束,是表B中的主键。那么删除表B时,应该先删除表A的外键约束,才能删除表B。例如,要想删除classroom表,要先删除student表的外键约束,才能删掉classroom表。
-- 删除外键:alter table 表名 drop foreign key 约束名;alter table student drop foreign key FK_class_id;
7、增删改查Sql
-- 删除外键:alter table 表名 drop foreign key 约束名;alter table student drop foreign key FK_class_id;--先删除之前创建的classroom表drop table classroom;-- 创建classroom表create table classroom(cid int,cname varchar(20),des varchar(50));
7.1 insert插入数据
语法:insert into 表名[(字段名….)] values(值….)
示例1:
-- 给所有字段添加值,和表中字段顺序一致insert into classroom values(1,'Coding','java');-- 给cname字段添加值,和指定字段的顺序必须一致insert into classroom(cname) values('python');insert into classroom(cid,cname) values(3,'test');-- 添加3条记录(批量插入)insert into classroom values(4,'oracle','oracle_class'), (5,'html','html_class'), (6,'Mysql','Mysql_class');select * from classroom;
示例2:
当两个表结构完全相同时,将一个表中的数据插入到另一个表中
-- 将classroom的值整体复制到classroom1create table classroom1(cid int,cname varchar(20),des varchar(50));insert into classroom1 select * from classroom;-- 查看classroom1的结果select * from classroom1;
7.2 删除表中满足条件的数据
-- 删除表中cname等于python的这条数据delete from classroom where cname='python';select * from classroom;
7.3 删除表中所有数据
-- 删除classroom中的所有数据delete from classroom;select * from classroom;
7.4 truncate和delete的区别
truncate清空表的数据
语法:truncate table 表名;
注意:delete与truncate的区别是什么
delete是逐行删除,truncate是文件级别的清空
delete删除后,自增性会继续执行,不会重置
truncate删除后,自增性重置
7.5 Update更新已有数据
-- 刚才删除了classroom的全部数据,现在插入一条数据insert classroom VALUES (1, 'test1', 'sql_class')
-- 将classroom中cid是1的这条数据,cname修改成test2update classroom set cname='test2' where cid=1; -- cid主键列
-- 插入一条数据insert classroom(cid, des) VALUES (2, 'oracle_class')select * from classroom;
-- 将des是oracle_class的这条数据,cname修改成test1update classroom set cname='test1' where des='oracle_class';select * from classroom;
-- 修改classroom中的所有数据update classroom set des='class_info';select * from classroom;
7.6 Select语句
select 10/3; -- 3.3333 / 会保留小数select 10 div 3; -- 3 div 整除(只会取整个结果的整数部分)select 10 % 3; -- 1 % 取模 取余(取结果的余数)select 10 mod 3; -- 1 mod 取模 取余select 1>0; -- 1 1==>Trueselect 1<0; -- 0 0==>Falseselect 1!=0; -- 1 1==>Trueselect 1<0 and 2>1; -- 0 0==>Falseselect 1<0 or 2>1; -- 1 1==>Trueselect !(1<0); -- 1 1==>True
8、Select语句
员工表:emp
/* Navicat Premium Data Transfer Source Server : mysql001 Source Server Type: MySQL Source Server Version : 50718 Source Host : 127.0.0.1:33506 Source Schema : testsql Target Server Type: MySQL Target Server Version : 50718 File Encoding : 65001 Date: 10/04/2023 21:39:50*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for emp-- ----------------------------DROP TABLE IF EXISTS `emp`;CREATE TABLE `emp`(`EMPNO` int(4) NOT NULL,`ENAME` varchar(10) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,`JOB` varchar(9) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,`MGR` int(4) NULL DEFAULT NULL,`HIREDATE` date NULL DEFAULT NULL,`SAL` int(7) NULL DEFAULT NULL,`COMM` int(7) NULL DEFAULT NULL,`DEPTNO` int(2) NULL DEFAULT NULL,PRIMARY KEY (`EMPNO`) USING BTREE,INDEX `FK_DEPTNO`(`DEPTNO`) USING BTREE,CONSTRAINT `FK_DEPTNO` FOREIGN KEY (`DEPTNO`) REFERENCES `dept` (`DEPTNO`) ON DELETE RESTRICT ON UPDATE RESTRICT) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Records of emp-- ----------------------------INSERT INTO `emp` VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20);INSERT INTO `emp` VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);INSERT INTO `emp` VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);INSERT INTO `emp` VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20);INSERT INTO `emp` VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);INSERT INTO `emp` VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30);INSERT INTO `emp` VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10);INSERT INTO `emp` VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20);INSERT INTO `emp` VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10);INSERT INTO `emp` VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);INSERT INTO `emp` VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20);INSERT INTO `emp` VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30);INSERT INTO `emp` VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20);INSERT INTO `emp` VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);SET FOREIGN_KEY_CHECKS = 1;
部门表:
/* Navicat Premium Data Transfer Source Server : mysql001 Source Server Type: MySQL Source Server Version : 50718 Source Host : 127.0.0.1:33506 Source Schema : testsql Target Server Type: MySQL Target Server Version : 50718 File Encoding : 65001 Date: 10/04/2023 21:40:48*/SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for dept-- ----------------------------DROP TABLE IF EXISTS `dept`;CREATE TABLE `dept`(`DEPTNO` int(2) NOT NULL,`DNAME` varchar(14) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,`LOC` varchar(13) CHARACTER SET latin1 COLLATE latin1_swedish_ci NULL DEFAULT NULL,PRIMARY KEY (`DEPTNO`) USING BTREE) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = DYNAMIC;-- ------------------------------ Records of dept-- ----------------------------INSERT INTO `dept` VALUES (10, 'ACCOUNTING', 'NEW YORK');INSERT INTO `dept` VALUES (20, 'RESEARCH', 'DALLAS');INSERT INTO `dept` VALUES (30, 'SALES', 'CHICAGO');INSERT INTO `dept` VALUES (40, 'OPERATIONS', 'BOSTON');SET FOREIGN_KEY_CHECKS = 1;
8.1 查询全部信息
select * from emp;
8.2 查询部分列信息
select ename,sal from emp;
8.3 查询列(表达式)
SELECT子句中的目标列,可以是属性列,也可以是表达式。比如,sal+sal*0.05就是一个计算表达式。还可以是函数、字符串常量等。例如,lower(ename)
select LOWER(ename),sal+sal*0.05 from emp;
8.4 查询列指定别名
SELECT ename 姓名, SAL 薪水 from emp
8.5 DISTINCT去除重复行
清除数据重复的行,使用DISTINCT关键词。
注意是行。对查询结果进行去重,如果查询结果有很多列,所有列数据都完全相同时,才会被去重。
selectDISTINCT sal, enamefrom emp;
例如两条数据,(3000,SCOTT)和(3000,FORD)就不是重复的数据,不会被去重。
8.6 Where子句
查询满足条件的元组,通过WHERE 子句。多个查询条件中间用AND或者OR连接。AND优先级大于OR
查询条件 | 谓词 |
---|---|
比较 | =、>、=、<=、!=、、!>、!<、NOT+上述比较运算符 |
确定范围 | BETWEEN AND、NOT BETWEEN AND |
确认集合 | IN、NOT IN |
字符匹配 | LIKE、NOT LIKE |
空值 | IS NULL、IS NOT NULL |
多重条件(逻辑运算) | AND、OR、NOT |
8.6.1 比较
-- 查询工资大于2000的员工信息 --比较运算select * from emp where sal > 2000;-- 查询工资在1000-2000之间的员工信息(范围查询) --比较select * from emp where sal>=1000 and sal<=2000;
8.6.2 范围查询:Between and
-- 查询工资在1000-2000之间的员工信息(范围查询) --比较+逻辑运算select * from emp where sal between 1000 and 2000; -- 包括边界-- 查询工资 不在 1000-2000之间的员工信息(范围查询)select * from emp where sal not between 1000 and 2000;
8.6.3 集合查询:IN、Not in
-- 查询员工编号为7521, 7369, 7788的员工信息(集合查询)select * from emp where empno=7521 or empno=7369 or empno=7788;select * from emp where empno in(7521,7369,7788);select * from emp where empno not in(7521,7369,7788);
8.6.4 字符匹配:LIKE、NOT LIKE
字符匹配,使用LIKE和NOT LIKE,后跟匹配串,匹配串可以包含通配符。
通配符 | 含义 |
---|---|
%(百分号) | 代表任意长度的字符串(长度可以为0)。例如a%b,代表以a开头,以b结尾任意长度的字符串。如ab,aaab, acb都满足 |
_(下横线) | 代表任意单个字符。a_b代表以a开头以b结尾的任意长度是3的字符串。 |
-- 查询ename第2个字母是L的select * from emp where ename like "_L%";-- 查询ename第2个字母是L,最后一个字母是N的select * from emp where enamelike "_L%_N";
-- 查询ename是4个字母,且第1个字母是K,第3个字母是Nselect * from emp where ename like "K_N_";
-- 查询ename第2个字母不是L的select * from emp where ename not like "_L%";
注:如果要查询的数据本身包含通配符,要用ESCAPE进行转义,如DB_Design
SELECT CnoFROM CourseWHERE Cname LIKE "DB\_Design" ESCAPE '\'
ESCAPE表示’\‘是换码字符,这样匹配串‘\’后面紧跟的’_’
就不是通配符,转义为普通的’_’字符
其他示例:以DB_开头的,倒数第3个字符是i的课程的Cno
WHERE Cname LIKE "DB\_%i__" ESCAPE '\'
8.6.5 空值查询:IS NULL、IS NOT NULL
-- 查询COMM是空值的select * from emp where COMM is NULL-- 查询COMM不是空值的select * from emp where COMM is NOT NULL
8.6.6 多条件查询:AND、OR
-- 查询COMM为空,并且SAL大于4000的数据select * from emp where COMM is NULL and SAL > 4000-- 查询COMM为空,或者COMM小于500的数据select * from emp where COMM is NULL OR COMM < 500
8.7 ORDER BY子句
用处:根据查询结果的1个或者多个列属性进行升降序排列。默认升序ASC (ASC可省略)。降序为DESC
如果有空值,排序时次序按照具体系统实现来决定。例如升序,含空值的元组最后显示。降序,含空值的元组最先显示。不同系统实现可以不同,只要保持一致即可。
8.7.1 升序ASC
select * from emp where enamelike "_L%" ORDER BY ename ASC;
8.7.2 降序DESC
select * from emp where enamelike "_L%" ORDER BY ename DESC;
8.7.3 limit
limit m,n
m:开始的位置,索引值默认从0开始 n:取值的长度(个数)
limit 0, 100和limit 100等效。
-- 不使用limit时select * from emp where ename not like "K_N_" ORDER BY ename;
-- 从查询的结果中,从索引0开始取结果(包含索引0这条),取2条数据,即前2条数据select * from emp where ename not like "K_N_" ORDER BY ename limit 0,2;-- 从查询的结果中,从索引是3开始取结果(包含索引3这条),取5条数据select * from emp where ename not like "K_N_" ORDER BY ename limit 3,5;
9、聚集函数
当聚合函数有空值时,除COUNT(*)外都是跳过空值只处理非空值。因为COUNT是统计数据条数,所以元组中某一列或者部分列有空值并不影响COUNT的统计结果。
注:只有SELECT子句和GROUP BY的HAVING子句才可以用聚集函数,WHERE子句不能使用。
聚集函数 | SQL | 含义 |
---|---|---|
COUNT(*) | SELECT COUNT(*) FROM 表名 | 统计表中数据条数 |
COUNT(DISTINCT/ALL 列名) | SELECT COUNT(DISTINCT Sno) FROM SC | 统计表中Sno列值的个数且去重 |
SUM(DISTINCT/ALL 列名) | SELECT SUM(Grade) FROM SC | 统计表中Grade列值的总和,必须是数值型 |
AVG(DISTINCT/ALL 列名) | SELECT AVG(Grade) FROM SC | 统计表中Grade列值的平均值,必须是数值型 |
MAX(DISTINCT/ALL 列名) | SELECT MAX(Grade) FROM SC | 统计表中Grade列值的最大值,必须是数值型 |
MIN(DISTINCT/ALL 列名) | SELECT MIN(Grade) FROM SC | 统计表中Grade列值的最小值,必须是数值型 |
-- 查询emp表数据总条数select count(*) from emp; -- 14-- 统计COMM列不是null值的员工人数select count(COMM) from emp; -- 4个 count() 统计的字段的非空值的数量-- 查询emp中员工都在哪些部门:DEPTNO需要去重select count(DISTINCT DEPTNO) from emp;-- 3,因为有3个部门,10,20,30-- 查询emp中员工的薪资总和select SUM(SAL) from emp;-- 29025-- 查询emp中10号部门的员工的薪资最大值select MAX(SAL) from emp where deptno=10 ;-- 5000-- 查询emp中员工的薪资最小值select MIN(SAL) from emp;-- 800-- 查询emp中员工的薪资平均值select AVG(SAL) from emp;-- 2073.2143
10、GROUP BY子句
将查询结果按某一列或者多列的值进行分组,值相等的为一组
WHERE 子句和HAVING短句的区别在于作用对象不同。WHERE作用于基本表和视图。HAVING短句作用于组,从中选择满足条件的组。having后面可接聚合函数,where后不能接聚合函数
注:如果没有对查询结果进行分组,那么聚集函数作用域是整个查询结果。如果有对分组,聚集函数作用域是每个分组。
例如:APP首页的底部有3个btn,点击事件名是一样的,参数值是btn的名字。查询今天点击APP底部btn的数据量。如果没有使用分组,那么将统计的是整个3个btn点击量的总和。采用分组,那将分别统计每个btn的点击量
例子 | 含义 |
---|---|
SELECT Cno, COUNT(Sno) FROM SC GROUP BY Cno | 按课程进行分组查看选课人数 |
SELECT Sno FROM SC GROUP BY Sno HAVING COUNT(*) >3 | 查询选修了3门以上课程的学生学号。HAVING短句给出了选择组的条件,只有满足条件才会被选中。 |
SELECT Sno,AVG(Grade) FROM SC GROUP BY Sno HAVING AVG(Grade) >= 90 | 选择平均成绩大于等于90分的学生学号和平均成绩 |
-- 统计不同部门的员工有多少selectDEPTNO, COUNT(*) from emp GROUP BY DEPTNO
共14个员工,3个来自部门No.10,5个来自部门No.20,30个来自No.30
-- 统计部门人数超过3人的部门编号及员工个数selectDEPTNO, COUNT(*) from emp GROUP BY DEPTNO HAVING COUNT(*) >3
-- 查询平均工资大于2000的部门的编号和平均工资select deptno, avg(sal) from emp group by deptno having avg(sal)>2000;
10.1 where后不能接聚合函数
-- 会报错,Invalid use of group functionselect * from emp deptno where avg(sal)>2000;
11、SQL关联查询
关联查询又叫做连接查询,常见的连接查询可分为:内连接、外连接、自然连接、自连接
查询方式 | 区别 | 备注 |
---|---|---|
等值连接 | 连接运算符是=号 | |
非等值连接 | 连接运算符是比较运算符(大于、等于、小于、小于等于、大于等于、不大于、不小于) | 连接条件中的各连接字段必须是可比的,但名字不必相同。 |
自然连接 | 目标列中重复的属性列去掉 | |
自身连接 | 一个表与其自身进行连接 | 表2是表2的别名 |
11.1 内连接
没有主从表之分,只会将两个表中相等的值列出在结果集中。取的是两边相交的共同部分。
关键字:inner join …on
emp表中并没有在40编号的部门,都是编号10,20,30部门的员工。
dept表中有4个部门:10, 20, 30, 40
两张表相等的值就是,在10,20,30这3个部门的员工,共14个人。
-- 内连接-- 没有主从表之分,只会将两个表中相等的值列出在结果集中select * from emp ORDER BY ename desc;select * from dept ORDER BY dname desc;-- 查询员工的姓名及所在部门名称select ename, dname from emp inner join dept on emp.deptno=dept.deptno ORDER BY ename desc;-- 查询员工编号是7788的员工的姓名及所在部门名称select ename, dname from emp inner join dept on emp.deptno=dept.deptno and empno=7788 ORDER BY ename desc;
11.2 左外连接
left join 前面的是主表,后面的是从表,会遍历主表中的每一个条记录
关键字:left join …on
-- 左外连接-- 主表、从表 left join 前面的是主表,后面的是从表,会遍历主表中的每一个条记录,select * from emp ORDER BY ename desc;select * from dept ORDER BY dname desc;-- 查询所有部门的名称(dname)和该部门对应的员工姓名(ename)select * from dept left join emp on dept.deptno=emp.deptno ORDER BY ename desc
-- 左外连接-- 主表、从表 left join 前面的是主表,后面的是从表,会遍历主表中的每一个条记录,select * from emp ORDER BY ename desc;select * from dept ORDER BY dname desc;-- 查询所有员工的名称(dname)和员工所所在部门的名称(dname)select * from emp left join dept on dept.deptno=emp.deptno ORDER BY ename desc;
从上面两个查询结果可以看出,谁是主表,谁是从表,查询结果的不同。
11.3 右外连接
主表、从表 right join 前面的是从表,后面的是主表,会遍历主表中的每一个条记录
关键词:right join … on
-- 查询所有部门的名称(dname)和该部门对应的员工姓名(ename)-- 调整主表和从表后,使用左外连接和右外连接是一样的效果。下面两种方式都是将dept作为主表,将emp作为从表。select dname, ename from emp right join dept on dept.deptno=emp.deptno ORDER BY ename desc;select dname, ename from dept left join emp on dept.deptno=emp.deptno ORDER BY ename desc;
-- 查询所有员工的名称(dname)和员工所所在部门的名称(dname)。调整主表和从表后,使用左外连接和右外连接是一样的效果。下面两种方式都是将emp作为主表,将dept作为从表。select dname, ename from dept right join emp on dept.deptno=emp.deptno ORDER BY ename desc;select dname, ename from emp left join dept on dept.deptno=emp.deptno ORDER BY ename desc;
11.4 自连接
给当前表起不同的别名,从而实现自身的连接查询
例如:将emp表指定两个别名,分别是e和p。从e表中获取ename并且给列指定别名“员工”;从p表中获取ename并且给列指定别名“上级领导”。
-- 查询所有员工和他上级领导的姓名select * from emp;select e.ename 员工, p.ename 上级领导 from emp e left join emp p on e.mgr=p.empno;
11.5 子查询
嵌套查询,将一个查询结果当做另一个查询的条件或结果集。子查询最接近思考方式,最自然的查询。
分类:单行子查询,多行子查询
-- 子查询-- 单行子查询-- 查询和scott在同一部门的所有员工信息-- 1:scott所在部门编号select deptno from emp where ename='SCOTT';-- 2:找到员工和scott部门编号一致的select * from emp where deptno=(select deptno from emp where ename='SCOTT');-- 多行子查询select * from emp ORDER BY DEPTNO desc;-- 20号部门每个员工的薪水select sal from emp where deptno=20;-- 查询薪水和20号部门员工相等的,但是不在20号部门员工的信息select * from emp where sal in (select sal from emp where deptno=20) and deptno!=20;select * from emp where sal in (select sal from emp where deptno=20) and deptno!=20;