老区建设网站,如何建设自己网站首页,上海网页设计公司选择兴田德润,信用网站建设数据库管理系统的一个重要功能是数据查询。数据查询不应简单地返回数据库中存储的数据#xff0c;还应根据需要对数据进行过滤#xff0c;确定数据的显示格式。MySQL 提供了强大而灵活的语句来实现这些操作。MySQL数据库使用select语句查询数据。
select [all|distinct]…数据库管理系统的一个重要功能是数据查询。数据查询不应简单地返回数据库中存储的数据还应根据需要对数据进行过滤确定数据的显示格式。MySQL 提供了强大而灵活的语句来实现这些操作。MySQL数据库使用select语句查询数据。
select [all|distinct]目标列的表达式1 [别名],目标列的表达式2 [别名]...
from 表名或视图名 [别名],表名或视图名 [别名]...
[where条件表达式]
[group by 列名
[having 条件表达式]]
[order by 列名 [asc|desc]]
[limit 数字或者列表];select *| 列名 from 表 where 条件;
一、数据准备
-- 创建数据库
create database if not exist mydb2;
use mydb2;
-- 创建商品表
create table product(pid int primary key auto_increment, -- 商品编号pname varchar(20) not null , -- 商品名字price double, -- 商品价格category_id varchar(20) -- 商品所属分类
);insert into product values(null,海尔洗衣机,5000,c001);
insert into product values(null,美的冰箱,3000,c001);
insert into product values(null,格力空调,5000,c001);
insert into product values(null,九阳电饭煲,200,c001);
insert into product values(null,啄木鸟衬衣,300,c002);
insert into product values(null,恒源祥西裤,800,c002);
insert into product values(null,花花公子夹克,440,c002);
insert into product values(null,劲霸休闲裤,266,c002);
insert into product values(null,海澜之家卫衣,180,c002);
insert into product values(null,杰克琼斯运动裤,430,c002);insert into product values(null,兰蔻面霜,300,c003);
insert into product values(null,雅诗兰黛精华水,200,c003);
insert into product values(null,香奈儿香水,350,c003);
insert into product values(null,SK-II神仙水,350,c003);
insert into product values(null,资生堂粉底液,180,c003);insert into product values(null,老北京方便面,56,c004);
insert into product values(null,良品铺子海带丝,17,c004);
insert into product values(null,三只松鼠坚果,88,null);二、简单查询
-- 1.查询所有的商品.
select * from product;
-- 2.查询商品名和商品价格.
select pname,price from product;
-- 3.别名查询.使用的关键字是asas可以省略的.
-- 3.1表别名:
select * from product as p;
-- 3.2列别名
select pname as pn from product;
-- 4.去掉重复值.
select distinct price from product;
-- 5.查询结果是表达式运算查询将所有商品的价格10元进行显示.
select pname,price10 from product;-- 查询商品名称为“海尔洗衣机”的商品所有信息
select * from product where pname 海尔洗衣机;-- 查询价格为800商品
select * from product where price 800;
三、运算符
数据库中的表结构建立之后表中数据的意义就已经确定了。 通过MySQL算子的运算可以获得表结构之外的另一种数据。比如student表中有一个birth字段表示学生的出生年份。 并使用MySQL的算术运算符用当前年份减去学生的出生年份得到该学生的实际年龄数据。MySQL 支持 4 个运算符算术运算符、比较运算符、逻辑运算符、位运算符。
算数运算符 算术运算符 说明 加法运算 - 减法运算 * 乘法运算 / 或 DIV 除法运算返回商 % 或 MOD 求余运算返回余数
select 6 2;
select 6 - 2;
select 6 * 2;
select 6 / 2;
select 6 % 2;-- 将每件商品的价格加10
select name,price 10 as new_price from product;
-- 将所有商品的价格上调10%
select pname,price * 1.1 as new_price from product;
比较运算符 比较运算符 说明 等于 和 小于和小于等于 和 大于和大于等于 安全的等于两个操作码均为NULL时其所得值为1而当一个操作码为NULL时其所得值为0 或! 不等于 IS NULL 或 ISNULL 判断一个值是否为 NULL IS NOT NULL 判断一个值是否不为 NULL LEAST 当有两个或多个参数时返回最小值 GREATEST 当有两个或多个参数时返回最大值 BETWEEN AND 判断一个值是否落在两个值之间 IN 判断一个值是IN列表中的任意一个值 NOT IN 判断一个值不是IN列表中的任意一个值 LIKE 通配符匹配 REGEXP 正则表达式匹配
-- 使用least求最小值
select least(10, 20, 30); -- 10
select least(10, null , 30); -- null-- 使用greatest求最大值
select greatest(10, 20, 30);
select greatest(10, null, 30); -- null-- 查询含有‘裤字的所有商品
select * from product where pname like %裤%;-- 查询以海开头的所有商品
select * from product where pname like 海%;-- 查询第二个字为蔻的所有商品
select * from product where pname like _蔻%;-- 查询category_id为null的商品
select * from product where category_id is null;-- 查询category_id不为null分类的商品
select * from product where category_id is not null;-- 查询价格不是800的所有商品
select * from product where price ! 800;
select * from product where price 800;
select * from product where not(price 800);-- 查询商品价格大于60元的所有商品信息
select * from product where price 60;-- 查询商品价格在200到1000之间所有商品
select * from product where price 200 and price 1000;
select * from product where price between 200 and 1000;逻辑运算符 逻辑运算符 说明 NOT 或者 ! 逻辑非 AND 或者 逻辑与 OR 或者 || 逻辑或 XOR 逻辑异或
-- 查询商品价格是200或800的所有商品
select * from product where price 200 or price 800;
select * from product where price in (200,800);
位运算符 位运算符 说明 | 按位或 按位与 ^ 按位异或 按位左移 按位右移 ~ 按位取反反转所有比特
位运算符是对二进制数执行计算的运算符。位运算首先将操作数转换成二进制数进行位运算然后将计算结果从二进制数转换回十进制数。
select 35; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 31; -- 位左移
select 31; -- 位右移
select ~3; -- 位取反四、排序查询
如果需要对读取到的数据进行排序我们可以使用MySQL的order by子句来设置你想要排序的字段和方法然后返回搜索结果。
select 字段名1字段名2……
from 表名
order by 字段名1 [asc|desc]字段名2[asc|desc]……asc代表升序desc代表降序不写默认升序。order by 在子句中使用支持单字段、多字段、表达式、函数、别名。order by子句放在查询语句的末尾除了 LIMIT 子句。
-- 1.使用价格排序(降序)
select * from product order by price desc;
-- 2.在价格排序(降序)的基础上以分类排序(降序)
select * from product order by price desc,category_id asc;
-- 3.显示商品的价格(去重复)并排序(降序)
select distinct price from product order by price desc;五、聚合查询
之前做的查询都是水平查询根据条件逐行判断而使用聚合函数的查询是垂直查询计算某一列的值然后返回单个值 此外聚合函数会忽略 Null 值。 聚合函数 作用 count() 统计指定列不为NULL的记录行数 sum() 计算指定列的数值和如果指定列类型不是数值类型那么计算结果为0 max() 计算指定列的最大值如果指定列是字符串类型那么使用字符串排序运算 min() 计算指定列的最小值如果指定列是字符串类型那么使用字符串排序运算 avg() 计算指定列的平均值如果指定列类型不是数值类型那么计算结果为0
-- 1 查询商品的总条数
select count(*) from product;
-- 2 查询价格大于200商品的总条数
select count(*) from product where price 200;
-- 3 查询分类为c001的所有商品的总和
select sum(price) from product where category_id c001;
-- 4 查询商品的最大价格
select max(price) from product;
-- 5 查询商品的最小价格
select min(price) from product;
-- 6 查询分类为c002所有商品的平均价格
select avg(price) from product where category_id c002;如果count函数的参数是星号*则统计所有记录的条数。 而如果参数是某个字段则不统计包含空值的记录数。sum 和 avg 函数忽略空值的存在就好像记录不存在一样。max 和 min 函数忽略空值的存在。
-- 创建表
create table test_null( c1 varchar(20), c2 int
);-- 插入数据
insert into test_null values(aaa,3);
insert into test_null values(bbb,3);
insert into test_null values(ccc,null);
insert into test_null values(ddd,6);-- 测试
select count(*), count(1), count(c2) from test_null;
select sum(c2),max(c2),min(c2),avg(c2) from test_null;
在 SQL 中COUNT(*) 和 COUNT(1) 表示的是对表中的行数进行统计的结果。两者的区别在于
COUNT(*) 会统计所有行不管这些行的其他列是否为 NULL。
COUNT(1) 也是统计行数但它对每一行的第一个列进行计数因此如果该列为 NULL则不会被统计在内。
因此如果你希望统计表中所有行不管其他列是否为 NULL那么使用 COUNT(*) 是最合适的如果希望只统计不为 NULL 的行数那么使用 COUNT(1) 更合适。
六、分组查询
分组查询是指使用group by字句对查询信息进行分组。
select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;例如
-- 1 统计各个分类商品的个数
select category_id, count(*) from product group by category_id ;如果要进行分组SELECT子句后只能出现分组字段和聚合函数其他字段不能出现。
1. 分组之后的条件筛选having
如果在分组后过滤统计结果必须使用having而不是where。where 子句用于过滤由 FROM 子句中指定的操作产生的行。group by 子句用于对 WHERE 子句的输出进行分组。having 子句用于从分组结果中过滤行
select 字段1,字段2… from 表名 group by 分组字段 having 分组条件;例如
-- 2.统计各个分类商品的个数,且只显示个数大于4的信息
select category_id ,count(*) from product group by category_id having count(*) 1;七、分页查询
分页查询在项目开发中很常见。由于数据量大显示屏长度有限需要分页显示数据。比如有30条数据每页显示5条第一页显示1-5条第二页显示6-10条。
-- 方式1-显示前n条
select 字段1字段2... from 表明 limit n
-- 方式2-分页显示
select 字段1字段2... from 表明 limit m,n
-- m: 整数表示从第几条索引开始计算方式 当前页-1*每页显示条数
-- n: 整数表示查询多少条数据例如
-- 查询product表的前5条记录
select * from product limit 5;-- 从第4条开始显示显示5条
select * from product limit 3,5;八、INSERT INTO SELECT
要将数据从一个表导入到另一个表使用 INSERT INTO SELECT 语句。
insert into Table2(field1,field2,…) select value1,value2,… from Table1;
insert into Table2 select * from Table1;要求目标表Table2必须存在。
九、SELECT INTO FROM
要将数据从一个表导入到另一个表有两个选项SELECT INTO 和 INSERT INTO SELECT。
SELECT value1, value2 into Table2 from Table1;要求目标表Table2不存在因为插入时会自动创建表Table2并将Table1中的指定字段数据复制到Table2中。
十、正则表达式
正则表达式regular expression描述了一个字符串匹配规则。 正则表达式本身是一个字符串。 用这个字符串来描述和定义匹配规则匹配一系列满足一定语法规则的字符串。 . 在开发中通常使用正则表达式来检索和替换符合特定规则的文本。 MySQL 支持通过 REGEXP 关键字进行字符串匹配的正则表达式。如果匹配则返回1否则返回0。 模式 描述 ^ 匹配输入字符串的开始位置。 $ 匹配输入字符串的结束位置。 . 匹配除 \n 之外的任何单个字符。 [...] 字符集合。匹配所包含的任意一个字符。例如 [abc] 可以匹配 plain 中的 a。 [^...] 负值字符集合。匹配未包含的任意字符。例如 [^abc] 可以匹配 plain 中的p。 p1|p2|p3 匹配 p1 或 p2 或 p3。例如z|food 能匹配 z 或 food。(z|f)ood 则匹配 zood 或 food。 * 匹配前面的子表达式零次或多次。例如zo* 能匹配 z 以及 zoo。* 等价于{0,}。 匹配前面的子表达式一次或多次。例如zo 能匹配 zo 以及 zoo但不能匹配 z。 等价于 {1,}。 {n} n 是一个非负整数。匹配确定的 n 次。例如o{2} 不能匹配 Bob 中的 o但是能匹配 food 中的两个 o。 {n,m} m 和 n 均为非负整数其中n m。最少匹配 n 次且最多匹配 m 次。
-- ^ 在字符串开始处进行匹配
SELECT abc REGEXP ^a;-- $ 在字符串末尾开始匹配
SELECT abc REGEXP a$;
SELECT abc REGEXP c$;-- . 匹配任意字符
SELECT abc REGEXP .b;
SELECT abc REGEXP .c;
SELECT abc REGEXP a.;-- [...] 匹配括号内的任意单个字符
SELECT abc REGEXP [xyz];
SELECT abc REGEXP [xaz];-- [^...] 注意^符合只有在[]内才是取反的意思在别的地方都是表示开始处匹配
SELECT a REGEXP [^abc];
SELECT x REGEXP [^abc];
SELECT abc REGEXP [^a];-- a* 匹配0个或多个a,包括空字符串。 可以作为占位符使用.有没有指定字符都可以匹配到数据SELECT stab REGEXP .ta*b;
SELECT stb REGEXP .ta*b;
SELECT REGEXP a*;-- a 匹配1个或者多个a,但是不包括空字符
SELECT stab REGEXP .tab;
SELECT stb REGEXP .tab;-- a? 匹配0个或者1个a
SELECT stb REGEXP .ta?b;
SELECT stab REGEXP .ta?b;
SELECT staab REGEXP .ta?b;-- a1|a2 匹配a1或者a2
SELECT a REGEXP a|b;
SELECT b REGEXP a|b;
SELECT b REGEXP ^(a|b);
SELECT a REGEXP ^(a|b);
SELECT c REGEXP ^(a|b);-- a{m} 匹配m个aSELECT auuuuc REGEXP au{4}c;
SELECT auuuuc REGEXP au{3}c;-- a{m,n} 匹配m到n个a,包含m和nSELECT auuuuc REGEXP au{3,5}c;
SELECT auuuuc REGEXP au{4,5}c;
SELECT auuuuc REGEXP au{5,10}c;-- (abc) abc作为一个序列匹配不用括号括起来都是用单个字符去匹配如果要把多个字符作为一个整体去匹配就需要用到括号所以括号适合上面的所有情况。
SELECT xababy REGEXP x(abab)y;
SELECT xababy REGEXP x(ab)*y;
SELECT xababy REGEXP x(ab){1,2}y;十一、多表关系
在实际开发中一个项目通常需要很多表才能完成。例如一个商城项目需要类别、产品和订单等多个表。并且这些表中的数据之间存在一定的关系。 多个MySQL表之间的关系可以概括为一对一、一对多/多对一关系、多对多。
一对一关系一个学生只有一张身份证 一张身份证只能对应一个学生。在任一表中添加一个唯一的外键指向对方的主键以确保一对一的关系。一般一对一关系很少见遇到一对一关系最好合并表。 一对多/多对一关系部门和员工。 一个部门有多个员工一个员工只能对应一个部门。 实现原理是多边建立外键单边指向主键。 多对多关系学生和课程。一个学生可以选择多门课程一门课程也可以被很多学生选择。多对多关系的实现需要借助第三个中间表。中间表至少包含两个字段。多对多关系拆分为一对多关系。中间表至少要有两个外键这两个外键指向原来两张表的主键。 十二、 外键约束
MySQL外键约束FOREIGN KEY是表的一个特殊字段常与主键约束一起使用。 对于有关联关系的两张表关联字段中主键所在的表为主表父表外键所在的表为副表子表。
外键用于建立主表和从表的关联关系为两表的数据建立联系约束两表数据的一致性和完整性。 比如一个水果摊里面只有苹果、桃子、李子、西瓜4种水果。 然后你来水果摊买水果只能选择苹果、桃子、李子、西瓜不能买其他水果。 1. 定义外键
定义外键时需要遵循以下规则主表必须已经存在于数据库中或者是当前正在创建的表。必须为主表定义主键。主键不能包含空值但外键中允许空值。 即只要外键的每一个非空值都出现在指定的主键中则外键的内容就是正确的。在主表的表名后指定列名或列名组合。 该列或列的组合必须是主表的主键或候选键。外键的列数必须与主表的主键的列数相同。外键中列的数据类型必须与主表主键中对应列的数据类型相同。
在 create table 语句中通过 foreign key 关键字来指定外键具体的语法格式如下
[constraint 外键名] foreign key 字段名 [字段名2…] references 主表名 主键列1 [主键列2…];
例如
create database mydb3;
use mydb3;
-- 创建部门表
create table if not exists dept(deptno varchar(20) primary key , -- 部门号name varchar(20) -- 部门名字
);create table if not exists emp(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int, -- 员工年龄dept_id varchar(20), -- 员工所属部门constraint emp_fk foreign key (dept_id) references dept (deptno) –- 外键约束
); 修改表时也可以添加外键约束但添加外键约束的前提是从表外键列的数据必须与主表主键列的数据一致或者是没有数据。
alter table 数据表名 add constraint 外键名 foreign key(列名) references
主表名 (列名);
例如
-- 创建部门表
create table if not exists dept2(deptno varchar(20) primary key , -- 部门号name varchar(20) -- 部门名字
);
-- 创建员工表
create table if not exists emp2(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int, -- 员工年龄dept_id varchar(20) -- 员工所属部门);
-- 创建外键约束
alter table emp2 add constraint dept_id_fk foreign key(dept_id) references dept2 (deptno); 3. 在外键约束下的数据操作
数据插入 -- 1、添加主表数据-- 注意必须先给主表添加数据
insert into dept values(1001,研发部);
insert into dept values(1002,销售部);
insert into dept values(1003,财务部);
insert into dept values(1004,人事部);-- 2、添加从表数据-- 注意给从表添加数据时外键列的值不能随便写必须依赖主表的主键列
insert into emp values(1,乔峰,20, 1001);
insert into emp values(2,段誉,21, 1001);
insert into emp values(3,虚竹,23, 1001);
insert into emp values(4,阿紫,18, 1002);
insert into emp values(5,扫地僧,35, 1002);
insert into emp values(6,李秋水,33, 1003);
insert into emp values(7,鸠摩智,50, 1003);
insert into emp values(8,天山童姥,60, 1005); -- 不可以-- 3、删除数据/*注意1主表的数据被从表依赖时不能删除否则可以删除2: 从表的数据可以随便删除*/
delete from dept where deptno 1001; -- 不可以删除
delete from dept where deptno 1004; -- 可以删除
delete from emp where eid 7; -- 可以删除
4. 删除外键约束
当表中不再需要外键约束时需要将其从表中删除。一旦外键被删除主从表的关系就会解除。
alter table 表名 drop foreign key 外键约束名;例如
alter table emp2 drop foreign key dept_id_fk;5. 多对多关系
在多对多关系中A表中的一行对应B表中的多行B表中的一行对应A表中的多行需要新增一个中间表来建立多对多关系。 -- 学生表和课程表(多对多)
-- 1 创建学生表student(左侧主表)
create table if not exists student(sid int primary key auto_increment,name varchar(20),age int,gender varchar(20)
);
-- 2 创建课程表course(右侧主表)
create table course(cid int primary key auto_increment,cidname varchar(20)
);-- 3创建中间表student_course/score(从表)
create table score(sid int,cid int,score double
);-- 4建立外键约束(2次)alter table score add foreign key(sid) references student(sid);
alter table score add foreign key(cid) references course(cid);-- 5给学生表添加数据
insert into student values(1,小龙女,18,女),(2,阿紫,19,女),(3,周芷若,20,男);
-- 6给课程表添加数据
insert into course values(1,语文),(2,数学),(3,英语);
-- 7给中间表添加数据
insert into score values(1,1),(1,2),(2,1),(2,3),(3,2),(3,3);十三、多表联合查询
多表查询就是同时查询两个或多个表因为有时候用户在查看数据的时候需要展示的数据来自于多个表。多表查询分为以下几类
交叉连接查询产生笛卡尔积
select * from A,B;
内连接查询(使用的关键字 inner join -- inner可以省略)
-- 隐式内连接SQL92标准
select * from A,B where 条件;
-- 显示内连接SQL99标准
select * from A inner join B on 条件;外连接查询(使用的关键字 outer join -- outer可以省略)
-- 左外连接left outer join
select * from A left outer join B on 条件;
-- 右外连接right outer join
select * from A right outer join B on 条件;
-- 满外连接: full outer join
select * from A full outer join B on 条件;子查询select的嵌套。 表自关联将一张表当成多张表来用。
1. 准备查询数据
外键约束对多表查询并无影响。
use mydb3;-- 创建部门表
create table if not exists dept3(deptno varchar(20) primary key , -- 部门号name varchar(20) -- 部门名字
);-- 创建员工表
create table if not exists emp3(eid varchar(20) primary key , -- 员工编号ename varchar(20), -- 员工名字age int, -- 员工年龄dept_id varchar(20) -- 员工所属部门
);-- 给dept3表添加数据
insert into dept3 values(1001,研发部);
insert into dept3 values(1002,销售部);
insert into dept3 values(1003,财务部);
insert into dept3 values(1004,人事部);-- 给emp表添加数据
insert into emp3 values(1,乔峰,20, 1001);
insert into emp3 values(2,段誉,21, 1001);
insert into emp3 values(3,虚竹,23, 1001);
insert into emp3 values(4,阿紫,18, 1001);
insert into emp3 values(5,扫地僧,85, 1002);
insert into emp3 values(6,李秋水,33, 1002);
insert into emp3 values(7,鸠摩智,50, 1002);
insert into emp3 values(8,天山童姥,60, 1003);
insert into emp3 values(9,慕容博,58, 1003);
insert into emp3 values(10,丁春秋,71, 1005);
2. 交叉连接查询
交叉连接查询返回被连接的两个表所有数据行的笛卡尔积。笛卡尔积可以理解为一张表的每一行去和另外一张表的任意一行进行匹配。假如A表有m行数据B表有n行数据则返回m*n行数据。笛卡尔积会产生很多冗余的数据后期的其他查询可以在该集合的基础上进行条件筛选。
select * from 表1,表2,表3….; 例如
-- 交叉连接查询
select * from dept3,emp3;
输出 3. 内连接查询
内连接查询求多张表的交集。 --隐式内连接SQL92标准
select * from A,B where 条件;
--显示内连接SQL99标准
select * from A inner join B on 条件;
例如
-- 查询每个部门的所属员工
select * from dept3,emp3 where dept3.deptno emp3.dept_id;
select * from dept3 inner join emp3 on dept3.deptno emp3.dept_id;-- 查询研发部和销售部的所属员工
select * from dept3,emp3 where dept3.deptno emp3.dept_id and name in( 研发部,销售部);
select * from dept3 join emp3 on dept3.deptno emp3.dept_id and name in( 研发部,销售部);-- 查询每个部门的员工数,并升序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno emp3.dept_id group by deptno order by total_cnt;select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno emp3.dept_id group by deptno order by total_cnt;-- 查询人数大于等于3的部门并按照人数降序排序
select deptno,count(1) as total_cnt from dept3,emp3 where dept3.deptno emp3.dept_id group by deptno having total_cnt 3 order by total_cnt desc;select deptno,count(1) as total_cnt from dept3 join emp3 on dept3.deptno emp3.dept_id group by deptno having total_cnt 3 order by total_cnt desc;4. 外连接查询
外连接分为左外连接left outer join、右外连接right outer join、全外连接full outer join。注意oracle中有full join但是mysql对full join的支持不好。 我们可以使用union来达到目的。
--左外连接left outer join
select * from A left outer join B on 条件;
--右外连接right outer join
select * from A right outer join B on 条件;
--满外连接: full outer join
select * from A full outer join B on 条件;-- 外连接查询
-- 查询哪些部门有员工哪些部门没有员工
use mydb3;
select * from dept3 left outer join emp3 on dept3.deptno emp3.dept_id;-- 查询哪些员工有对应的部门哪些没有
select * from dept3 right outer join emp3 on dept3.deptno emp3.dept_id;-- 使用union关键字实现左外连接和右外连接的并集
select * from dept3 left outer join emp3 on dept3.deptno emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno emp3.dept_id;
5. 子查询
子查询是指在一个完整的查询语句中嵌套几个功能不同的小查询共同完成一个复杂查询的一种写法。 通俗一点就是包含select嵌套的query。
子查询可以返回四种类型的数据
单行单列返回特定列的内容可以理解为单值数据。 单行多列返回一行数据中多列的内容。 多行单列返回多行记录中同一列的内容相当于给了一个操作范围。 多行多列查询返回的结果是临时表。
-- 查询年龄最大的员工信息显示信息包含员工号、员工名字员工年龄
select eid,ename,age from emp3 where age (select max(age) from emp3);-- 查询年研发部和销售部的员工信息包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno,name from dept3 where name 研发部 or name 销售部) ;-- 查询研发部20岁以下的员工信息,包括员工号、员工名字部门名字
select eid,age,ename,name from (select * from dept where name 研发部 )t1,(select * from emp3 where age 20)t2在子查询中有一些常用的逻辑关键字可以为我们提供更丰富的查询功能。 主要关键字如下ALL关键字、ANY关键字、SOME关键字、IN关键字、EXISTS关键字。
select …from …where c all(查询语句)
--等价于
select ...from ... where c result1 and c result2 and c result3ALL
如果与子查询返回的所有值进行比较则返回 true。ALL可以与、、、、、组合使用表示等于、大于、大于等于、小于、小于等于、不等于所有数据。ALL大于号表示指定列中的值必须大于子查询集的每个值即必须大于子查询集的最大值 如果是小于号则小于子查询集合的最小值。 同理可推导出其他比较运算符。
-- 查询年龄大于‘1003’部门所有年龄的员工信息
select * from emp3 where age all(select age from emp3 where dept_id 1003);
-- 查询不属于任何一个部门的员工信息
select * from emp3 where dept_id ! all(select deptno from dept3);
ANY和SOME
select …from …where c any(查询语句)
--等价于
select ...from ... where c result1 or c result2 or c result3ANY如果与子查询返回的任何值进行比较则返回 true。ANY 可与 、、、、、 组合使用表示等于、大于、大于或等于、小于、小于或等于和不等于以下任何一项数据。ANY 表示指定列中的值必须大于子查询中的任意值即必须大于子查询集合中的最小值。同理可推导出其他比较运算符。SOME与ANY功能相同SOME可以理解为ANY的别名。
-- 查询年龄大于‘1003’部门任意一个员工年龄的员工信息
select * from emp3 where age all(select age from emp3 where dept_id 1003’);IN
select …from …where c in(查询语句)
--等价于
select ...from ... where c result1 or c result2 or c result3IN关键字用于判断一条记录的值是否在指定集合中。在 IN 关键字前加上 not 可以反转条件。
-- 查询研发部和销售部的员工信息包含员工号、员工名字
select eid,ename,t.name from emp3 where dept_id in (select deptno from dept3 where name 研发部 or name 销售部);
EXISTS
select …from …where exists(查询语句)如果子查询“有数据结果”至少返回一行数据EXISTS()的结果为“true”执行外层查询。如果子查询有“no data result”没有返回数据则EXISTS()的结果为“false”外层查询将不会执行。EXISTS后面的子查询不返回任何实际数据只返回true或false当返回为true时where条件成立。EXISTS 关键字比 IN 关键字更有效。所以在实际开发中尤其是数据量大的时候建议使用EXISTS关键字。
6. 自关联查询
MySQL在查询信息时有时需要对表本身进行关联查询即一个表与自己关联一个表作为多个表使用。表自关联时必须给表取别名。
select 字段列表 from 表1 a , 表1 b where 条件;
--或者
select 字段列表 from 表1 a [left] join 表1 b on 条件;例如
-- 创建表,并建立自关联约束
create table t_sanguo(eid int primary key ,ename varchar(20),manager_id int,foreign key (manager_id) references t_sanguo (eid) -- 添加自关联约束
);-- 添加数据
insert into t_sanguo values(1,刘协,NULL);
insert into t_sanguo values(2,刘备,1);
insert into t_sanguo values(3,关羽,2);
insert into t_sanguo values(4,张飞,2);
insert into t_sanguo values(5,曹操,1);
insert into t_sanguo values(6,许褚,5);
insert into t_sanguo values(7,典韦,5);
insert into t_sanguo values(8,孙权,1);
insert into t_sanguo values(9,周瑜,8);
insert into t_sanguo values(10,鲁肃,8);-- 进行关联查询
-- 1.查询每个三国人物及他的上级信息如: 关羽 刘备
select * from t_sanguo a, t_sanguo b where a.manager_id b.eid;
