MySQL 3:MySQL数据库基本操作 DQL
数据库管理系统的一个重要功能是数据查询。数据查询不应简单地返回数据库中存储的数据,还应根据需要对数据进行过滤,确定数据的显示格式。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.别名查询.使用的关键字是as(as可以省略的).
-- 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,price+10 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 3&5; -- 位与
select 3|5; -- 位或
select 3^5; -- 位异或
select 3>>1; -- 位左移
select 3<<1; -- 位右移
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 '.ta+b';
SELECT 'stb' REGEXP '.ta+b';-- 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 > result3
ALL
如果与子查询返回的所有值进行比较,则返回 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 > result3
ANY:如果与子查询返回的任何值进行比较,则返回 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 = result3
IN关键字用于判断一条记录的值是否在指定集合中。在 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;
相关文章:

MySQL 3:MySQL数据库基本操作 DQL
数据库管理系统的一个重要功能是数据查询。数据查询不应简单地返回数据库中存储的数据,还应根据需要对数据进行过滤,确定数据的显示格式。MySQL 提供了强大而灵活的语句来实现这些操作。MySQL数据库使用select语句查询数据。 select [all|distinct]<…...
sql语句的优化
sql优化 优化数据访问 查询性能低下最基本的原因是访问的数据太多,大部分性能低下的查询都可以通过减少访问的数据量来优化所以关于低效的查询,需要确认是否检索了大量不需要的数据,以及mysql服务器层是否在分析大量不需要的数据 因为有些查…...
Shell脚本之——自动安装JDK
目录 1.修改主机名 2.创建文件,单独存放Shell脚本 3.编写Shell脚本 4.Shell脚本命令简介 (1)文件头 (2)打印命令 (3)设置全局变量 (4)条件判断 (5)解压 (6)文件重命名 (7)在/etc/profile指定行插入 5.完整脚本内容 6.重启环境变量 7.判断java是否配置…...

大数据---Hadoop安装Hadoop简易版
编写自动安装Hadoop的shell脚本 完整流程: 大数据—Hadoop安装教程(二) 文章目录编写自动安装Hadoop的shell脚本上传压缩包编写shell脚本vim hadoopautoinstall.sh运行上传压缩包 在opt目录下创建连个目录install和soft 将压缩包上传到install目录下 …...
Spring框架中使用到的设计模式以及对应的类(方法)
模板方法--->postProcessBeanFactory,onFresh、initPropertySource装饰器模式--->BeanWrapper委托者模式--->BeanDefinitionParseDelegate策略模式--->ClassPathXmlApplicationContext、FileSystemApplicationContext、XMLBeanDefinitionReader、Proper…...
类和类的定义
6.2 类和类的定义 面向对象最重要的概念就是类(Class)和实例(Instance),必须牢记类是抽象的模板,比如学生类,而实例是根据类创建出来的一个个具体的对象,每个对象都拥有相同的方法&…...

丝绸之路——NFT 系列来袭!
丝绸之路的经历讲述了汉朝时代的一个重要历史事件。该系列中的 NFT 带有中国这段黄金时代令人愉悦的视觉元素,使其成为值得收藏的物品。 NFT 系列介绍 敦煌女神像01(左);汉代士兵(中);敦煌女神像…...

配置CMAKE编译环境:VSCODE + MinGW
一. MinGW安装 MinGW(Minimalist GNU For Windows)是个精简的Windows平台C/C、ADA及Fortran编译器,相比Cygwin而言,体积要小很多,使用较为方便。 MinGW最大的特点就是编译出来的可执行文件能够独立在Windows上运行。 MinGW的组成ÿ…...

六、mybatis与spring的整合
Spring整合Mybaits的步骤 引入依赖 在Spring整合Mybaits的时候需要引入一个中间依赖包mybatis-spring <dependency><groupId>org.mybatis</groupId><artifactId>mybatis</artifactId><version>3.5.5</version> </dependency&g…...

JavaWeb--JDBC
JDBC1 JDBC概述1.1 JDBC概念1.2 JDBC本质1.3 JDBC好处2 JDBC快速入门2.1 编写代码步骤2.2 具体操作3 JDBC API详解3.1 DriverManager3.2 Connection3.2.1 获取执行对象3.2.2 事务管理3.3 Statement3.3.1 概述3.3.2 代码实现3.4 ResultSet3.4.1 概述3.4.2 代码实现3.5 案例3.6 P…...

大数据框架之Hadoop:入门(四)Hadoop运行模式
Hadoop运行模式包括:本地模式、伪分布式模式以及完全分布式模式。 Hadoop官方网站:http://hadoop.apache.org/ 4.1本地运行模式 4.1.1官方Grep案例 1.创建在hadoop文件夹下面创建一个input文件夹 [roothdp101 hadoop]# mkdir input2.将Hadoop的xml配…...

《爆肝整理》保姆级系列教程python接口自动化(十一)--发送post【data】(详解
简介 前面登录的是传 json 参数,由于其登录机制的改变没办法演示,然而在工作中有些登录不是传 json 的,如 jenkins 的登录,这里小编就以jenkins 登录为案例,传 data 参数,给各位童鞋详细演练一下。 一、…...

【微服务】Nacos注册中心
🚩本文已收录至专栏:微服务探索之旅 👍希望您能有所收获 👍Nacos和Eureka一样也可以充当服务的注册中心,让我们一起看看有何区别? 点击跳转👉【微服务】Eureka注册中心 👍Nacos除了可…...

跟开发打了半个月后,我终于get报bug的正确姿势了
在测试人员提需求的时候,大家经常会看到,测试员和开发一言不合就上BUG。然后开发一下就炸了,屡试不爽,招招致命。 曾经看到有个段子这么写道: 不要对程序员说,你的代码有BUG。他的第一反应是:…...
js万能类型检测Object.prototype.toString.call——定制Object.prototype.toString.call的检测结果
javascript的类型检测 1、typeof typeof操作符可以检测js的基础数据类型,包括number、string、boolean、undefined。因为null在二进制存储的值与object相同,所以typeof检测null会返回object。此为特例 2、instanceof instanceof操作符可以检测某个对…...

激光slam学习笔记2--激光点云数据结构特点可视化查看
背景:不同厂商的激光点云结果存在一定差异,比如有些只有xyz,有些包含其他,如反光率、时间戳、ring等。如何快速判断是个值得学习的点 概要:对于rosbag类型的激光点云,介绍使用rviz快速查看点云结构特点 如…...

SpringBoot笔记【JavaEE】
SpringBoot概念、创建和运行 1.什么是SpringBoot?为什么学习SpringBoot? Spring Boot 就是 Spring 框架的脚⼿架,它就是为了快速开发 Spring 框架⽽诞⽣的。 2.Spring Boot优点 快速集成框架【提供启动添加依赖的功能】内容运行容器【无需…...

目标检测算法之voxelNet与pointpillars对比
算法对比 3D目标检测发展简史 点云目标检测目前发展历经VoxelNet、SECOND、PointPillars、PV-RCNN。 2017年苹果提出voxelnet,是最早的一篇将点云转成voxel体素进行3D目标检测的论文。 然后2018年重庆大学的一个研究生Yan Yan在自动驾驶公司主线科技实习的时候将vo…...

电脑里的连接速度双工模式是什么?怎么设置
双工模式包括全双工、半双工模式。1.半双工1、半双工数据传输允许数据在两个方向上传输,但是,在某一时刻,只允许数据在一个方向上传输,它实际上是一种切换方向的单工通信。所谓半双工就是指一个时间段内只有一个动作发生。早期的对…...

springboot整合单机缓存ehcache
区别于redis的分布式缓存,ehcache是纯java进程内的单机缓存,根据不同的场景可选择使用,以下内容主要为springboot整合ehcache以及注意事项添加pom引用<dependency><groupId>net.sf.ehcache</groupId><artifactId>ehc…...

龙虎榜——20250610
上证指数放量收阴线,个股多数下跌,盘中受消息影响大幅波动。 深证指数放量收阴线形成顶分型,指数短线有调整的需求,大概需要一两天。 2025年6月10日龙虎榜行业方向分析 1. 金融科技 代表标的:御银股份、雄帝科技 驱动…...
树莓派超全系列教程文档--(62)使用rpicam-app通过网络流式传输视频
使用rpicam-app通过网络流式传输视频 使用 rpicam-app 通过网络流式传输视频UDPTCPRTSPlibavGStreamerRTPlibcamerasrc GStreamer 元素 文章来源: http://raspberry.dns8844.cn/documentation 原文网址 使用 rpicam-app 通过网络流式传输视频 本节介绍来自 rpica…...
Admin.Net中的消息通信SignalR解释
定义集线器接口 IOnlineUserHub public interface IOnlineUserHub {/// 在线用户列表Task OnlineUserList(OnlineUserList context);/// 强制下线Task ForceOffline(object context);/// 发布站内消息Task PublicNotice(SysNotice context);/// 接收消息Task ReceiveMessage(…...

高等数学(下)题型笔记(八)空间解析几何与向量代数
目录 0 前言 1 向量的点乘 1.1 基本公式 1.2 例题 2 向量的叉乘 2.1 基础知识 2.2 例题 3 空间平面方程 3.1 基础知识 3.2 例题 4 空间直线方程 4.1 基础知识 4.2 例题 5 旋转曲面及其方程 5.1 基础知识 5.2 例题 6 空间曲面的法线与切平面 6.1 基础知识 6.2…...
第25节 Node.js 断言测试
Node.js的assert模块主要用于编写程序的单元测试时使用,通过断言可以提早发现和排查出错误。 稳定性: 5 - 锁定 这个模块可用于应用的单元测试,通过 require(assert) 可以使用这个模块。 assert.fail(actual, expected, message, operator) 使用参数…...
Neo4j 集群管理:原理、技术与最佳实践深度解析
Neo4j 的集群技术是其企业级高可用性、可扩展性和容错能力的核心。通过深入分析官方文档,本文将系统阐述其集群管理的核心原理、关键技术、实用技巧和行业最佳实践。 Neo4j 的 Causal Clustering 架构提供了一个强大而灵活的基石,用于构建高可用、可扩展且一致的图数据库服务…...
全面解析各类VPN技术:GRE、IPsec、L2TP、SSL与MPLS VPN对比
目录 引言 VPN技术概述 GRE VPN 3.1 GRE封装结构 3.2 GRE的应用场景 GRE over IPsec 4.1 GRE over IPsec封装结构 4.2 为什么使用GRE over IPsec? IPsec VPN 5.1 IPsec传输模式(Transport Mode) 5.2 IPsec隧道模式(Tunne…...
基于Java Swing的电子通讯录设计与实现:附系统托盘功能代码详解
JAVASQL电子通讯录带系统托盘 一、系统概述 本电子通讯录系统采用Java Swing开发桌面应用,结合SQLite数据库实现联系人管理功能,并集成系统托盘功能提升用户体验。系统支持联系人的增删改查、分组管理、搜索过滤等功能,同时可以最小化到系统…...
32单片机——基本定时器
STM32F103有众多的定时器,其中包括2个基本定时器(TIM6和TIM7)、4个通用定时器(TIM2~TIM5)、2个高级控制定时器(TIM1和TIM8),这些定时器彼此完全独立,不共享任何资源 1、定…...

鸿蒙Navigation路由导航-基本使用介绍
1. Navigation介绍 Navigation组件是路由导航的根视图容器,一般作为Page页面的根容器使用,其内部默认包含了标题栏、内容区和工具栏,其中内容区默认首页显示导航内容(Navigation的子组件)或非首页显示(Nav…...