关系型数据库基础
MySQL:是一个小型关系型数据库
Oracle、sqlServer、DB2
一、概念
关系模型:把世界看作是由实体和关系组成。实体是指现实世界中客观存在的事物,事物的特性称为属性。关系就是指实体间的联系。
关系模型数据库:是一种以表做为实体,以主键和外键关系做为联系的一种数据库结构
相类似的实体被存入表中
主键:在关系型数据库表中,用一个唯一的标识符来标识每一行,这个标识符就是主键(Primary Key);
两个特点:不能重复;不能为空
外键:在关系型数据库中,外键(Foreign Key)是用来表达表和表之间关联关系的列
1、三种关系:
1-1、一对一
一条主表记录对应一条从表记录,同时一条从表记录对应一条主表记录
1-2、一对多
一条主表记录对应多条从表记录,同时一条从表记录对应一条主表记录。“多边”的表称为从表,“一边”的表称为主表,外键加在从表中
1-3、多对多
一条主表记录对应多条从表记录,同时一条从表记录对应多条主表记录。在关系型数据库中,利用第三方的中间表描述主表和从表的多对多关系
创建数据库、创建表、及删除数据库、删除表
二、数据库管理系统(DBMS)
数据库本身只是一个用来放数据的容器,放在容器中的数据如何摆放不归容器管理;需要使用数据库管理系统(DBMS)来对数据库中的数据进行管理
1、分类
1-1、本地数据库管理系统
又称桌面型数据库管理系统。在这种模式下,RDBMS与数据库应用程序运行在同一客户端的进程中
1-2、数据库服务器管理系统
RDBMS和数据库应用程序运行在不同的进程中
2、数据库引擎
是数据库镀层软件组织,数据库管理系统(DBMS)使用数据引擎进行创建、查询、更新和删除数据
//查询数据库引擎
show engines
2-1、InnoDB存储引擎
InnoDB是MySQL数据库默认的引擎,事务型数据库的首选引擎,提供提交、回滚、崩溃恢复、并发控制的能力。
2-2、MyISAM存储引擎
MyISAM拥有较高的插入、查询速度,但不支持事务
2-3、MEMORY存储引擎
MEMORY存储引擎将表中的数据存储到内存中,为查询和引用其他表数据提供快速访问。主要用于数据量不大的临时数据
三、结构化查询语言SQL
SQL是结构化查询语言的英文缩写,是一种用于管理关系型数据库,并与数据库中的数据进行通讯的计算机语言
SQL方言:SQL提供所有基本的数据操作,但是不同的RDBMS之间存在一些差别,每个RDBMS实现标准的方法可能有细微的区别
SQL的非过程性质:
分类:
1⃣️数据定义语言(DDL)
创建、修改、删除数据库的内部数据结构
create database 数据库名称; —— 创建数据库
drop database 数据库名称; —— 删除数据库
-- 创建表的语法
CREATE TABLE<表名>
(<列名><列的数据类型>[列的约束]);
-- 创建用户表
CREATE TABLE user_table(
userName varchar(255),
userPassword varchar(255),
userMoney DECIMAL(11,2)
);
-- 删除表
drop table 表名;
-- 添加新列
-- alter table 表名 add 新列名 新列的数据类型 新列的约束
alter table user_table add user_info varchar(255);
-- 修改列
-- alter table 表名 change 旧列名 新列名 新列的数据类型 新列的约束
alter table user_table change user_info user_gender varchar(100);
-- 删除列
-- alter table 表名 drop COLUMN 列名;
alter table user_table drop COLUMN user_gender;
*数据类型:
数据类型是数据的一种分类,是按照数据结构来分类的
1、整数类型
类型 | 大小 | 范围(有符号) | 范围(无符号) | 用途 |
---|---|---|---|---|
TINYINT | 1字节 | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2字节 | (-32 768,32767) | (0,65 535) | 大整数值 |
MEDIUMINT | 3字节 | 大整数值 | ||
INT | 4字节 | 大整数值 | ||
BIGINT | 8字节 | 极大整数 |
2、浮点类型
类型 | 大小 | 精度 | 用途 |
---|---|---|---|
FLOAT | 4字节 | 7位小数 | 单精度浮点数 |
DOUBLE | 8字节 | 15位小数 | 双精度浮点数 |
DECIMAL(M,D) | 17字节 | 30位小数 | 小数值 |
3、字符串类型
类型 | 范围 | 用途 |
---|---|---|
CHAR(n) | 0-255 | 定长字符串 |
VARCHAR(n) | 0-65 535 | 变长字符串 |
TINYTEXT | 0-255 | 短文本字符串 |
TEXT | 0-65 535 | 长文本数据 |
MEDIUMTEXT | 0-16 777 215 | 中等长度文本数据 |
LONGTEXT | 0-4 294 967 295 | 极大文本数据 |
4、日期类型
类型 | 大小 | 格式 | 用途 |
---|---|---|---|
DATE | 3字节 | YYYY-MM-DD | 日期值 |
TIME | 3字节 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1字节 | YYYY | 年份值 |
DATETIME | 8字节 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4字节 | YYYYMMDD HHMMSS | 混合日期和时间值,时间戳 |
5、二进制类型
一般情况下不会将二进制文件的数据放在数据库中,而是会有一个专门存放文件的文件服务器,在数据库中只需要保存文件在文件服务器中的路径
类型 | 大小 | 用途 |
---|---|---|
TINYBLOB | 0-255 | 不超过255个字符的二进制字符串 |
BLOB | 0-65535 | 二进制形式的长文本数据 |
MEDIUMBLOB | 0-16 777 215 | 二进制形式的中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 | 二进制形式的极大文本数据 |
6、枚举类型(ENUM)
在MySQL中,ENUM是一个字符串对象,其值是从列创建时定义的允许值列表中选择的;ENUM枚举类型使用语法:
CREATE TABLE t_employee(
employeeName VARCHAR(50),
edu ENUM('高中','大专','本科')
);
2⃣️*数据查询语言(DQL) *
用于数据库中数据的查询
SQL语句特点、执行顺序
重要:
查询语句的书写顺序
select ===> from ===> where ===> group by ===> having ===> order by ===> limit
查询语句的执行顺序
from ===> where ===> group by ===> having ===> select ===> order by ===> limi
后执行的子句,可以使用前执行子句中的计算结果、定义的别名等
SQL的执行顺序为:
- 执行FROM
- where条件过滤
- GROUP BY分组
- 执行SELECT投影列
- having条件过滤
- 执行ORDER BY排序
- 执行limit语句,返回限定行
1、SQL查询
基础查询
SQL中的查询语句只有一个:SELECT,它可与其它语句配合完成所有的查询功能。SELECT语句的完整语法,可以有6个子句,语法如下:
SELECT 目标表的列名或列表达式集合
FROM 基本表或(和)视图集合
[WHERE 条件表达式]
[GROUP BY 列名集合]
[HAVING 组条件表达式]
[ORDER BY 列名[集合]]
[LIMIT]
投影操作:是查询语句里必须有的字句,关键字为 SELECT 它将选择对表中那些列进行操作,这些列将出现在结果中
-- 将需要查询的列的列名写在 投影列位置,查询多列用 , 分隔
SELECT 投影列1,投影列2,投影列3 FROM 表名 where 过滤条件;
-- *通配符:表示查询表中所有的列
SELECT * FROM 表名 where 过滤条件;
-- SELECT 表名.列名
select employees_table.e_name from employees_table;
-- 别名简写
select e.e_name as eName,
e.e_money
from employees_table as e;
计算列
在数据库管理系统中,我们也可以执行返回计算列的SELECT查询
数据结构中不存在该列数据,但可通过计算得到该数据
代码详见 navicat —> j0426 —> SQL查询
-- 查看到金额乘2后的值
select e.e_money*2 money
from employees_table as e;
-- CONCAT用于字符串拼接
select CONCAT(e.e_money*2,"元") money
from employees_table as e;
2、条件查询
WHERE子句应用搜索条件与SELECT查询获得的数据,搜索条件可以采用不同的形式,但是条件的值都是布尔值
2-1、单条件查询
运算符:= < > <= >= !=
-- select 列1,列2,... from 表名 where 列 = 值;
2-2、多条件查询
运算符:= < > <= >= !=
-- select 列1,列2,... from 表名 where 列 = 值 && 列>值;
3、范围查询
-- select 列1,列2,... from 表名 where 列名 between 值1 and 值2;
4、模糊查询
用于比较相似的值,利用通配符来实现模糊查询
SELECT 列A,列B FROm 表名 WHERE 要查询的列名 LIKE 模式
通配符
"_" 匹配任何单个字符
"%" 匹配零个或任意多个字符
-- 例:查询姓王的员工
select * from 表名 where 列名 like "王%";
-- 若要查询不姓王的员工,在like前加not
select * from 表名 where 列名 not like "王%";
5、处理空数据
-- 查询为空的信息 is null(是空) 或 is not null(不是空)
SELECT *
FROM student_table
WHERE studentName IS NULL;
6、去重查询
-- distinct数据查询去重
SELECT distinct 列名 FROM 表名;
7、返回限定行limit
-- select 列1,列2,列3 from 表名 limit 开始序号,返回行数
-- 开始序列号:从0开始; limit:仅限mysql使用(分页查询常用)
-- 如果从第一行开始返回 0,n 可简写为 limit n
SELECT * FROM t_employee limit 5,8; -- 表示从t_employee表中查询返回从第6行到第8行的三条数据
8、定义集合关系 in()
-- select 列1,列2,列3 from 表名 where 限定的列名 in ();
SELECT * FROM t_employee WHERE f_deptName in ("技术部","销售部");
-- 表示从t_employee表中查询f_deptName列的 技术部和销售部的员工信息
-- not in 取消集合关系
9、排序 order by
-- (默认是升序ASC) SELECT * FROM 表名 order by 列名
SELECT * FROM t_employee ORDER BY f_money; -- 表示从t_employee表中按f_money列查询信息,并按f_money的值升序排列
-- 降序 在最后加上 desc
SELECT * FROM 表名 order by 列名 desc
-- 当排序多列时会先按第一列数据进行排序,完成后,当第一列有相同数据时再按第二列进行排序
-- 例如下行代码:会先按工资降序排列,排序完成后当工资有相同的数据时会再按入职时间进行升序排列
SELECT* FROM t_employee ORDER BY f_money DESC,f_inDate;
10、分组查询 Group by
可以让表中的行进行分组,然后在每个组上应用聚合函数
分组:就是将制定规则中相同的数据进行“小区域”的划分,然后针对若干个“小区域”进行数据处理
语法:
-- 例如:统计每个部门学历是本科的员工数量
select deptName,count(*) enNum from t_employee where edu="本科" group by deptName;
多列分组
只有多列数据都相同时才会被分到一个组
分组语句对投影列的限制:
1、分组列(group by后的列名)
2、聚合函数列
-- 例:按部门和性别分组;只有部门和性别相同才被分到一组
SELECT f_deptName,f_gender,count(*) as emNum FROM t_employee where f_money>=8000 group by f_deptName,f_gender;
-- 聚合函数是在分组后执行的,分组又是在过滤条件(where)后执行,所以通过where来对聚合函数进行过滤都是不行的
-- 如果想对聚合后的数据进行过滤,使用having子句
-- 例:平均工资在8000以上的部门
SELECT f_deptName,avg(f_money) FROM t_employee GROUP BY f_deptName HAVING avg(f_money)>8000;
-- where 和 HAVING 的区别:
-- 1、where运行在分组前,因此不能执行任何聚合函数
-- 2、having时运行在分组后,只能用作聚合函数的过滤
3⃣️数据操作语言(DML)
用于数据库中数据的修改,包括添加、删除、修改等
1、添加
向表格添加符合每列数据类型要求的数据
-- insert into 表名(列名列表)value(列数据); (只能添加单行)
insert into user_table(userName,userPassword,userMoney)value
("易国柱","111111",1897.32);
-- insert into 表名(列名列表)values(列数据); (使用 values 可添加多行数据)
insert into user_table(userName,userPassword,userMoney)values
("易国柱","111111",1897.32),
("易国柱","111111",1897.32),
("易国柱","111111",1897.32),
("易国柱","111111",1897.32);
2、删除
删除满足过滤条件的所有
-- delete from 表名 where 过滤条件
delete from user_table where userPassword="ygz970518";
3、修改
将满足过滤条件的单行数据做单独修
-- update 表名 set 列1=新值1,列2=新值2 where 过滤条件
UPDATE user_table set userPassword="111111",userMoney=49458.00
where userName="易国柱";
4⃣️数据控制语言(DCL)
控制数据库访问权限
四、数据完整性
可靠性+准确性=数据完整性
1、实体完整性
保证每行所代表的实体能互相区别,不能存在两条一摸一样的记录
1-1、实现方式:
1-1.1、主键约束(Primary Key):
主键是表中的列,主键列不能为空,也不能重复,一个表中只能有一个主键,做主键必需满足4个条件:
- 值不能为空
- 值必须为一
- 不能有业务含义
- 值不能发生变动
设置主键约束可以在创建表时指定
-- 建表时添加
create table t_table(
-- 主键自增长、主键自动增长
id INT PRIMARY KEY AUTO_INCREMENT;
)
-- 建表后添加、删除
主键约束
添加:alter table 表名 add primary key (字段);
删除:alter table 表名 drop primary key;
自动增长
添加:alter table 表名 modify 列名 int auto_increment;
删除:alter table 表名 modify 列名 int;
-- 注意:若主键设置了自动增长,在删除时必须先删除自动增长,再删除主键约束
1-1.2、唯一约束(Unique)
唯一约束是指给定列的所有值必须唯一,该列在表中每一行的值必须唯一。它和主键约束的区别在于该列可以为空,并且可以在一张表中给多个列设置唯一约束
-- 建表时添加
create table t_table(
id INT PRIMARY KEY AUTO_INCREMENT,
money DOUBLE UNIQUE -- 唯一约束,同一列值不能重复
);
-- 建表后添加、删除
添加:alter table 表名 add unique 约束名 (字段);
删除:alter table 表名 drop key 约束名;
2、域完整性
2-1、实现方式:
保证指定列的数据有效性
2-1.1、非空约束(not null)
-- 建表时添加
CREATE TABLE user_table(
userId INT PRIMARY KEY AUTO_INCREMENT, -- 增加主键约束,且自动生成数据
userName varchar(255) NOT NULL, -- 非空约束
userBirthday DATE,
userMoney DECIMAL(9,2),
);
-- 建表后添加、删除
添加:alter table 表名 modify 列名 数据类型 not null;
删除:alter table 表名 modify 列名 数据类型 null;
2-1.2、默认约束(Default)
-- 建表时添加
CREATE TABLE user_table(
userId INT PRIMARY KEY AUTO_INCREMENT, -- 增加主键约束,且自动生成数据
userName varchar(255) NOT NULL, -- 非空约束
userBirthday DATE,
userMoney DECIMAL(9,2),
userJob varchar(255) DEFAULT "教师" -- 默认约束
);
-- 建表后添加、删除
添加:alter table 表名 alter 列名 set default '值';
删除:alter table 表名 alter 列名 drop default;
2-1.3、检查约束(Check)
MySQL不支持
CREATE TABLE user_table (
money DECIMAL(9,2) CHECK(money>1000 and money<1500) -- 检查约束:规定值的范围
);
3、引用完整性
从表外键中出现的数据,必须在主表的主键列中出现
3-1、实现方式
3-1.1、外键约束
外键是用来表示表于表之间联系的,是表中的一个列
外键约束是指在外键列上加上一个约束,强制外键列引用的数据是正确的。如果违反约束,则不允许该数据的修改;没有建立外键约束不等于没有外键
-- 创建员工表、部门表
-- 员工表(从表)
CREATE TABLE employees_table(
e_id INT PRIMARY KEY AUTO_INCREMENT,
e_name varchar(255),
e_money DOUBLE,
e_inDate DATE,
e_depeId INT,
foreign key(e_depeId) references department(d_depeId) -- 在创建表时添加外键约束(注意:此时关联的主表应先创建好)
);
-- 部门表(主表)
CREATE TABLE department(
d_depeId INT PRIMARY KEY AUTO_INCREMENT,
d_name varchar(255)
);
INSERT INTO department(d_name) VALUES
("开发部"),("财务部"),("销售部"),("技术部"),("管理部");
-- ALTER TABLE 添加外键约束的表 ADD CONSTRAINT 外键列 FOREIGN KEY(从表外键列列名) REFERENCES 主表表名(主表主键列名)
ALTER TABLE employees_table ADD CONSTRAINT e_depeId FOREIGN KEY (e_depeId) REFERENCES department(d_depeId);
-- 在表创建好后添加外键约束
INSERT INTO employees_table(e_name,e_money,e_inDate,e_depeId)VALUES
("张三",6000,"2000-01-01",5);
-- 删除外键约束下的部门数据
-- 方式一:先删除和部门相关的所有员工信息,再删除部门信息
delete from employees_table where e_depeId=5;
delete from department where d_depeId=5;
-- 方式二:外键置空:先将和部门相关的所有员工外键设置为null,再删除部门信息
update employees_table set e_depeId=null where e_depeId=5;
delete from department where d_depeId=5;
删除:
第一步:删除外键
alter table 表名 drop foreign key 约束名
第二步:删除索引
alter table 表名 drop index 约束名
PS : 约束名和索引名一样
五、条件判断语句
1、if()双分支
-- if(条件,结果为真的数据,结果为假的数据)
SELECT *,if(f_money>6000,"高工资","低工资") FROM t_employee;
-- 表示在t_employee表中工资大于6000的返回高工资,否则返回低工资
2、case-when多分支
-- case when 多分支
-- case
-- WHEN 条件1 then 数据1
-- WHEN 条件2 then 数据2
-- WHEN 条件3 then 数据3
-- ......
-- ELSE 数据n
-- END
-- 例:
UPDATE t_employee set
f_money=f_money+case
WHEN f_deptName="技术部" THEN 500
WHEN f_deptName="管理部" THEN 600
WHEN f_deptName="财务部" THEN 300
ELSE 400
END;
SELECT f_money from t_employee;
-- 代码表示在t_employee表中,技术部工资加500,管理部加600,财务部加300,其余部门加400
六、MySQL函数
1、常用函数
1-1、时间函数
方法 | 作用 |
---|---|
now() | 得到当前时间(年月日 时分秒) |
curdate() | 得到当前时间(年月日) |
year() | 得到指定日期的年份 |
date_add() | 得到之后的时间 |
timestampdiff() | 得到两个日期之间的间隔,小日期在前 |
-- now() 得到当前时间的 年月日 时分秒
SELECT now();
-- curdata() 得到当前时间的 年月日
SELECT curdate();
-- year() 得到指定日期的年份
SELECT year("2011-01-01");
-- date_add()
SELECT DATE_ADD(CURDATE(),INTERVAL 10 day); -- 当前时间的10天后
SELECT DATE_ADD(CURDATE(),INTERVAL 10 month); -- 当前时间的10个月后
SELECT DATE_ADD(CURDATE(),INTERVAL 10 year); -- 当前时间的10年后
-- timestampdiff() 得到两个日期之间的间隔,小日期在前
SELECT timestampdiff(day,"1997-05-18",NOW()); -- 得到 1997-05-18 到当前时间的天数
SELECT timestampdiff(month,"1997-05-18",NOW()); -- 得到 1997-05-18 到当前时间的月数
SELECT timestampdiff(year,"1997-05-18",NOW()); -- 得到 1997-05-18 到当前时间的年数
1-2、数据函数
方法 | 作用 |
---|---|
abs() | 得到绝对值 |
ceiling() | 向上取整 |
floor() | 向下取整 |
rand() | 随机数,返回一个0-1之间的随机数 |
sign() | 符号函数: 负数返回-1,正数返回1,0返回0 |
-- abs()
SELECT ABS(-8); -- 8 /*绝对值*/
-- ceiling()
SELECT CEILING(9.4); -- 10 /*向上取整*/
-- floor()
SELECT FLOOR(9.4); -- 9 /*向下取整*/
-- rand()
SELECT RAND();
-- sign()
SELECT SIGN(-9); -- -1
1-3、字符串函数
方法 | 作用 |
---|---|
char_length(str) | 返回字符串包含的字符数 |
concat(str, str,int) | 用于字符串拼接 |
insert(str1,int,int,str2) | 替换字符串,从指定位置开始替换指定长度 |
lower() | 大写转小写 |
upper() | 小写转大写 |
left(str,int) | 从左边截取 |
right(str,int) | 从右边截取 |
replace(str1,str2,str3) | 替换指定字符串(str2为str1的子串) |
substr(str,int,int) | 截取字符串,开始和长度 |
reverse(str) | 字符串反转 |
-- char_length(str)
SELECT CHAR_LENGTH("我爱编程helloworld"); -- 14
-- concat(str, str,int,...)
SELECT CONCAT("我爱编程helloworld",520,"ok"); -- 我爱编程helloworld520ok
-- insert(str1,int,int,str2)
SELECT insert("我爱编程helloworld",1,2,'超级热爱'); -- 超级热爱编程helloworld 从第一个字符开始,到第二个字符替换为 "超级热爱"
-- lower() 小写转大写
SELECT LOWER("ABCdEfG"); -- abcdefg
-- upper() 大写转小写
SELECT UPPER("aBcDEfg"); -- ABCDEFG
-- left(str,int)
SELECT LEFT("我爱编程helloworld",5); -- "我爱编程," 从左边截取5个字符
-- right(str,int)
SELECT RIGHT("我爱编程,helloworld",5); -- "world" 从右边截取5个字符
-- replace(str1,str2)
SELECT REPLACE("我爱编程helloworld","编程helloworld","java"); -- 我爱java
-- substr(str,int,int)
SELECT SUBSTR("我爱编程helloworld",3,7); -- 编程hello 从字符串第3个位置开始,截取七个字符
-- reverse(str) 反转
SELECT REVERSE("abcdefg"); -- gfedcba
1-4、系统信息函数
SELECT VERSION(); /*版本*/
SELECT USER(); /*用户*/
2、聚合函数
2-1、方法:
方法 | 作用 |
---|---|
count() | 统计单个列的行数量 |
sum() | 获取单个列的合计值 |
avg() | 获取单个列的平均值 |
max() | 获取单个列的最大值 |
min() | 获取单个列的最小值 |
2-2、计算规范:
符号、关键字 | 作用 |
---|---|
* | 计算所有选择的行,包括null值 |
all 列名(默认 等价于只写列名) | 统计所有非null行数 |
distinct 列名 | 统计非null行且不重复行数 |
-- count() 统计行数
SELECT COUNT(*) from 表名; -- 统计表中所有行数量
SELECT COUNT(all 列名A) from 表名; -- 统计表中,A列非null行数量
SELECT COUNT(distinct 列名A) from 表名; -- 统计表中,A列非null且不重复的行数量
-- sum() 统计单列的和
SELECT sum(f_money) FROM t_employee; -- all 累加非空行
SELECT sum(distinct f_money) FROM t_employee; -- distinct 累加非空不重复行
-- avg() 平均值
SELECT avg(f_money) FROM t_employee; -- 非空行的平均值
SELECT sum(f_money)/count(*) FROM t_employee; -- 加上了非空行的平均值
-- max() 最大值, min() 最小值
SELECT max(f_money),min(f_money) FROM t_employee;
七、子查询
在一个查询语句中再嵌套一个查询语句,称为子查询;子查询是潜逃在查询语句里面的查询语句
代码详情见navicat j0428查询
1、select中嵌入子查询
当作列来使用,子查询查出来的结果必须是单行单列
标准结构:
select 列1,列2,(子查询) as 列别名 from 表名;
-- 部门表
CREATE TABLE t_department(
`pk_deptId` INT PRIMARY KEY AUTO_INCREMENT,
`deptName` varchar(255)
);
INSERT INTO t_department(`deptName`)VALUES
("财务部"),("销售部"),("广告部"),("管理部");
INSERT INTO t_employee(`name`,`e_deptId`)VALUES
("张三",1),("李四",4),("王五",2),
("赵六",3),("刘七",1),("甘八",3);
SELECT e.`name`,
(SELECT d.`deptName` FROM t_department as d WHERE e.`e_deptId`=d.`pk_deptId`)
FROM t_employee as e;
2、from中嵌入子查询
把子查询的结果当作临时表来使用,外部查询就是基于临时表进行查询
标准结构:
-- select 列1,列2 from(子查询) as 别名;
SELECT demo.*
FROM (SELECT `name`,e_money
FROM t_employee) as demo
WHERE demo.e_money>=4000
3、where中嵌入子查询
三类情况:
3-1、比较运算符
在where中嵌套的子查询,如果子查询返回单行单列,才能使用
-- 查询和张三同一部门的员工:
-- 1、先查询出张三所在的部门
SELECT e.`e_deptId`
FROM t_employee as e
WHERE e.`name`="张三";
-- 2、再根据张三的部门信息查询出该部门的员工
SELECT e2.*
FROM t_employee as e2
WHERE e2.`e_deptId`=(SELECT e.`e_deptId`
FROM t_employee as e
WHERE e.`name`="张三");
3-2、in 和 not in
子查询返回多行单列,才能使用
-- 查询张三和李四所在部门的员工:
-- 1、先查询出张三和李四所在的部门id集合
SELECT `e_deptId`
FROM t_employee
WHERE `name` in ("张三","李四");
-- 2、根据张三和李四所在的部门id查找员工
SELECT * FROM t_employee WHERE `e_deptId` in
(SELECT `e_deptId`
FROM t_employee
WHERE `name` in ("张三","李四"));
3-3、子查询运算符
1、all 运算符
和子查询的结果逐一比较,必须全部满足时表达式的值才为真
-- 查询比所有张姓员工工资都还要高的员工信息
-- 1、先查询所有张姓员工的工资
SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%"
-- 2、再通过all比较所有张姓员工工资
SELECT *
FROM t_employee
WHERE e_money>all
(SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%")
2、any运算符
子查询的结果逐一比较,只要满足其中一个表达式的值就为真
-- 查询比所有张姓员工工资高的员工信息
-- 1、先查询所有张姓员工的工资
SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%"
-- 2、再通过any比较所有张姓员工工资 (只要比其中一个姓张员工工资高就为真)
SELECT *
FROM t_employee
WHERE e_money>ANY
(SELECT e_money
FROM t_employee
WHERE `name` LIKE "张%") AND `name` NOT LIKE "张%"
3、exists运算符
exists判断子查询中是否存在数据,如果存在,表达式为真,反之为假。not exists相反
-- 查询员工表,显示与张姓员工相同工资的员工信息
-- 1、先查询所有张姓员工的工资
SELECT e.e_money
FROM t_employee e
WHERE e.`name` LIKE "张%"
-- 2、再判断其它员工有无和张姓员工同工资的
SELECT e2.*
FROM t_employee e2
WHERE EXISTS
(SELECT e.e_money
FROM t_employee e
WHERE e.`name` LIKE "张%" and e.e_money=e2.e_money) and `name` NOT LIKE "张%"
3、组合查询
使用union运算符,将两个查询信息组合返回显示
-- 查询张姓员工工资前二的信息
-- 查询李姓员工工资前二的信息
(SELECT *
FROM t_employee
WHERE `name` like "张%"
ORDER BY e_money DESC LIMIT 2)
union -- 将两个查询信息组合
(SELECT *
FROM t_employee
WHERE `name` like "李%"
ORDER BY e_money DESC LIMIT 2);
4、相关子查询
在主查询中,每查询一条记录,需要重新做一次子查询,这种称为相关子查询。相关子查询的执行,依赖于外部查询的数据,外部查询返回一行,子查询就执行一次。
-- 代码示例
SELECT emName, (SELECT d.deptName FROM t_dept d
WHERE e.deptId=d.deptId) deptName FROM t_employee e;
5、非相关子查询
在主查询中,子查询只需要执行一次,子查询结果不再变化。子查询结果供主查询使用,这种查询方式称为非相关子查询
-- 例:查询学生成绩表,显示班上数学成绩最高的同学的信息
select * from t_studentScore
where math=(select max(math) from t_studentScore)
6、联表查询
如果数据来自多个表,那么可以采用联表查询的方式来实现;表连接就是指将多个表联合在一起实现查询结果;表联接采用的是笛卡尔乘积,称之为横向联接
笛卡尔乘积是指将两张表的所有数据相连,最后联接的结果数为两张表数量的乘积
6-1、内联接
是从结果表中删除与其它被连接表中没有匹配行的所有行,所以内联接可能会丢失信息
在数据库中将多表相连需要使用 INNER JOIN,INNER可以省略
select 列1,列2 from 表1 join 表2;
-- 查询广告部的员工信息
SELECT e.*,d.*
FROM t_employee e join t_department d
on e.e_deptId=d.pk_deptId
WHERE d.deptName="广告部"
6-2、外联接
不管有没有匹配,被定义了外联接的表数据都要出现在结果中
6-2.1、左外联接
在LEFT JOIN左边的表就被定义为外联接,那么此表中所有数据都会出现在查询结果中
select e.*,d.* from 表1 as e left join 表2 as d on e.外键列名=d.主键列名;
-- 左边表(e)的数据会全部显示
6-2.2、右外联接
在RIGHT JOIN右边的表就被定义为外联接,那么此表中所有数据都会出现在查询结果中
select e.*,d.* from 表1 as e right join 表2 as d on e.外键列名=d.主键列名;
-- 右边表(d)的数据会全部显示
6-3、自联接
自联接其实就是内联接或外联接的一种特例,同样可以使用INNER JOIN 或 OUTER JOIN;自联接所联接的表是来自于同一张表
-- 查询每位员工姓名及他们直属领导的姓名
SELECT e1.`name`,e2.`name` as leaderName
FROM t_employee e1
LEFT JOIN t_employee e2
ON e1.leaderId=e2.pk_id
八、视图
视图可以看作是SQL语句的封装,可以看作是临时表,将前面创建的数据保存起来,便于后续使用
-- 创建视图 CREATE view 视图名 as 查询SQL语句
CREATE view demo as
SELECT e1.`name`,e2.`name` as leaderName
FROM t_employee e1
LEFT JOIN t_employee e2
ON e1.leaderId=e2.pk_id
-- 删除视图
create view 视图名;