MySQL3:连接查询、子查询及正则表达式查询

1152-张同学

发表文章数:21

首页 » MySQL » 正文

九、连接查询

1.什么是连接查询?

连接查询是将两个或两个以上的表按按某个条件连接起来,从中选取需要的数据。连接查询时同时查询两个或两个以上的表时使用的。当不用的表中存在相同意义的字段时,可以通过该字段连接这个几个表。

2.内连接查询

  • JOIN | CROSS JOIN INNER JOIN

  • 通过ON连接条件

  • 显示两个表中符合连接条件的记录

    -- cms_user的proId对应省份表中的id
    SELECT cms_user.id,username,proName FROM cms_user,provinces
    WHERE cms_user.proId=provinces.id;
    
    -- 查询cms_user表中id,username,email,sex
    -- 查询provinces表proName
    -- 使用INNER JOIN
    SELECT u.id,u.username,u.email,u.sex,p.proName
    FROM cms_user AS u
    INNER JOIN provinces AS P
    ON u.proId=p.id;
    
    -- 使用CROSS JOIN
    SELECT u.id,u.username,u.email,u.sex,p.proName
    FROM cms_user AS u
    CROSS JOIN provinces AS P
    ON u.proId=p.id;
    
    -- 查询cms_user表中的id,username,sex
    -- 查询provinces表中的proName
    -- 条件是cms_user的性别为男的用户
    SELECT u.id,u.username,u.sex,p.proName
    FROM cms_user AS u
    JOIN provinces AS p
    ON u.proId =p.id
    WHERE u.sex='男';
    
    -- 根据proName分组
    SELECT u.id,u.username,u.sex,p.proName,
    COUNT(*) AS totalUsers,
    GROUP_CONCAT(username)
    FROM cms_user AS u
    JOIN provinces AS p
    ON u.proId =p.id
    WHERE u.sex='男'
    GROUP BY p.proName;
    
    -- 对分组结果进行筛选,选出组中用户人数>=2的用户
    SELECT u.id,u.username,u.sex,p.proName,
    COUNT(*) AS totalUsers,
    GROUP_CONCAT(username)
    FROM cms_user AS u
    JOIN provinces AS p
    ON u.proId =p.id
    WHERE u.sex='男'
    GROUP BY p.proName
    HAVING COUNT(*) >=2;
    
    -- 按照id升序排列
    SELECT u.id,u.username,u.sex,p.proName,
    COUNT(*) AS totalUsers,
    GROUP_CONCAT(username)
    FROM cms_user AS u
    JOIN provinces AS p
    ON u.proId =p.id
    WHERE u.sex='男'
    GROUP BY p.proName
    HAVING COUNT(*) >=1
    ORDER BY u.id ASC;
    
    -- 限制显示条数,前两条
    SELECT u.id,u.username,u.sex,p.proName,
    COUNT(*) AS totalUsers,
    GROUP_CONCAT(username)
    FROM cms_user AS u
    JOIN provinces AS p
    ON u.proId =p.id
    WHERE u.sex='男'
    GROUP BY p.proName
    HAVING COUNT(*) >=1
    ORDER BY u.id ASC
    LIMIT 0,2;
    
    -- 查询cms_news表中的id,title
    -- 查询cms_cate表中的cateName
    SELECT n.id,n.title,c.cateName
    FROM cms_news AS n
    JOIN cms_cate AS c
    ON n.cid=c.id;
    
    -- 查询cms_news表中的id,title
    -- 查询cms_admin表中的username,role
    SELECT n.id,n.title,a.username,a.role
    FROM cms_news AS n
    JOIN cms_admin AS a
    ON n.aid=a.id;
    
    -- 查询cms_news表中的id,title
    -- 查询cms_cate表中的cateName
    -- 查询cms_admin表中的username,role
    SELECT n.id,n.title,c.cateName,a.username,a.role
    FROM cms_cate AS c
    JOIN cms_news AS n
    ON n.cid=c.id
    JOIN cms_admin AS a
    ON n.aid=a.id;
    

3.外连接查询

  • 左外连接:LEFT [OUTER] JOIN 显示左表的全部记录及右表符合连接条件的记录

  • 右外连接:RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合条件的记录

    -- 插叙一条不属于provinces表中id的记录
    INSERT cms_user(username,password,regTime,proId)
    VALUES('TEST2','TEST2','1381203974',20);
    

MySQL3:连接查询、子查询及正则表达式查询

-- 左外连接,以左表为主
-- cms_user为主表
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM cms_user AS u
LEFT JOIN provinces AS P
ON u.proId=p.id;

-- 右外连接,以右表为主
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
RIGHT JOIN cms_user AS u
ON u.proId=p.id;

MySQL3:连接查询、子查询及正则表达式查询

4.外键

  • 外键时表的一个特殊字段。被参照的表是主表,外键所在的字段的表为子表。设置外键的原则需要记住,就是依赖与数据库中已存在的表的主键。外键的作用是建立该表与其父表的关联关系。父表中对记录做操作时,子表中与之对应的信息也应有相应的改变。
  • 外键的作用保持数据的一致性和完整性
  • 可以实现一对一或一对多的关系
  • 注意
    • 父表和子表必须使用相同的存储引擎,而且禁止使用临时表
    • 数据表的存储引擎只能为InnoDB
    • 外键列和参照列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
    • 外键列和参照列必须创建索引。如果外键列不存在索引的话,MySQL将自动创建索引
  • 外键约束的参照操作
    • CASCADE:从父表删除或者更新且自动删除或更新子表中匹配的行
    • SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL
    • RESTRICT:拒绝对父表的删除或更新操作
    • NO ACTION:标准SQL的关键字,在MySQL中与RESTRICT相同
-- 创建部门表department(主表)
-- 先有主表,再有子表,在本例中体现为现有部门,再有员工
-- 增加外键
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

-- 创建员工表employee(子表)
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('knight',3),
('saber',4),
('archer',1);

-- 增加了外键的约束后,无法直接从主表中删除部门
DELETE FROM department WHERE id=1;
-- 报错:Cannot delete or update a parent row: a foreign key constraint fails (`spike`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))
-- 要先删除想要删除的部门中的员工
DELETE FROM employee WHERE depId=1; 
DELETE FROM department WHERE id=1;

-- 受外键约束,当往employee表中添加department表中不存在的部门id时,系统报错
INSERT employee(username,depId) VALUES('TEST',11);
-- 报错:Cannot add or update a child row: a foreign key constraint fails 
-- 报错的原因为
-- 1、添加的外键列与另一个表的唯一索引列(一般是主键)的数据类型不同
-- 2、要添加外键的表类型与另一个表的存储引擎是不是都为innodb引擎
-- 3、设置的外键与另一个表中的唯一索引列(一般是主键)中的值不匹配
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(20)
)ENGINE=INNODB;

INSERT department(depName) VALUES('市场部'),
('教学部'),
('研发部'),
('客服部');

-- 通过CONSTRAINT命名外键名称
CREATE TABLE IF NOT EXISTS employee(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
emp_name VARCHAR(20) NOT NULL UNIQUE,
dep_id TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(dep_id) REFERENCES department(id)
)ENGINE=INNODB;

INSERT employee(emp_name,dep_id) VALUES('KING',1),
('QUEEN',2),
('KNIGHT',3),
('ARCHER',4),
('ASSASIN',1);
  • 增加外键后,向子表(employee)中添加主表REFERENCES department(id)之外的值时,系统报错;
  • 报错:Cannot add or update a child row: a foreign key constraint fails
INSERT employee(emp_name,dep_id) VALUES('saber',5);
-- 删除外键后,才可插入
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
INSERT employee(emp_name,dep_id) VALUES('saber',10);

-- 删除外键后,方可删除主表的值(部门)
DELETE FROM department WHERE id BETWEEN 5 and 9;

-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(dep_id) REFERENCES department(id);

-- 无法添加外键,因为子表(employee)中仍包含在主表id范围外的dep_id值
-- 删除条记录后,则可添加外键
DELETE FROM employee WHERE id BETWEEN 6 AND 9;
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(dep_id) REFERENCES department(id);
-- CASCADE:从父表删除或者更新且自动删除或更新子表中匹配的行
-- ON DELETE
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depid TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE CASCADE
)ENGINE=INNODB;

INSERT employee(username,depid) VALUES('king',1),
('queen',2),
('knight',3),
('saber',4),
('archer',1);

-- 从主表中删除id为1的值,同时子表中depId为1的员工信息也自动被删除
DELETE FROM department WHERE id=1;

-- ON UPDATE
DROP TABLE employee;
CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depid TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON UPDATE CASCADE
)ENGINE=INNODB;

INSERT employee(username,depid) VALUES('king',2),
('queen',2),
('knight',3),
('saber',4),
('archer',4);

-- 主表id和子表depId同时+10
UPDATE department SET id=id+10;

-- ------------------------------------------------------------------------------

DROP TABLE department,employee;

-- SET NULL:从父表删除或者更新且自动删除或更新子表中匹配的行
-- ON DELETE ON UPDATE
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

CREATE TABLE IF NOT EXISTS employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depid TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES department(id) ON DELETE SET NULL ON UPDATE SET NULL
)ENGINE=INNODB;

INSERT employee(username,depid) VALUES('king',1),
('queen',2),
('knight',3),
('saber',4),
('archer',1);

-- 从主表中删除id为1的值
DELETE FROM department WHERE id=1;

MySQL3:连接查询、子查询及正则表达式查询

5.联合查询

  • UNION
  • UNION ALL
  • UNION和UNION ALL区别是UNION去掉相同记录,UNION ALL是单纯的合并到一起
-- 联合查询
SELECT username FROM cms_user UNION SELECT username FROM employee;
-- 联合的数据要有管联系,不推荐如下骚操作
SELECT id,username FROM cms_user UNION SELECT username,id FROM employee;

MySQL3:连接查询、子查询及正则表达式查询

十、子查询

1.什么是子查询

子查询是将一个查询语句嵌套在另一个查询语句中。内层查询语句的查询结果,可以作为外层查询语句的条件。

2.引发子查询的情况?

  • 使用[NOT] IN的子查询

    -- 子查询
    DROP TABLE department,,employee;
    
    CREATE TABLE IF NOT EXISTS department(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    depName VARCHAR(20) NOT NULL UNIQUE
    )ENGINE=INNODB;
    
    INSERT department(depName) VALUES('教学部'),
    ('市场部'),
    ('运营部'),
    ('督导部');
    
    CREATE TABLE IF NOT EXISTS employee(
    id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    depid TINYINT UNSIGNED
    )ENGINE=INNODB;
    
    INSERT employee(username,depid) VALUES('king',1),
    ('queen',2),
    ('knight',3),
    ('saber',4),
    ('archer',1);
    
    -- 查询department表中的id
    SELECT id FROM department;
    -- 根据如上操作,得到1,2,3,4
    SELECT id,username FROM employee WHERE depid IN(1,2,3,4)
    
    SELECT id,username FROM employee WHERE depid IN(SELECT id FROM department);
    SELECT id,username FROM employee WHERE depid NOT IN(SELECT id FROM department);
    
    
  • 使用比较运算符的子查询

    -- 创建学院表student
    CREATE TABLE IF NOT EXISTS student(
    id TINYINT UNSIGNED AUTO_INCREMENT KEY,
    username VARCHAR(20) NOT NULL UNIQUE,
    score TINYINT UNSIGNED
    );
    
    INSERT student(username,score) VALUES('KING',95),
    ('KING1',25),
    ('KING2',35),
    ('KING3',45),
    ('KING4',55),
    ('KING5',65),
    ('KING6',75),
    ('KING7',85),
    ('KING8',95);
    
    -- 奖学金表scholarship
    CREATE TABLE IF NOT EXISTS scholarship(
    id TINYINT UNSIGNED KEY AUTO_INCREMENT,
    level TINYINT UNSIGNED
    );
    
    INSERT scholarship(level) VALUES(90),(80),(70);
    
    -- 查询获得1等奖学金的学员有那些
    SELECT id,username FROM student WHERE score >= (SELECT level FROM scholarship WHERE id=1);
    
  • 使用[NOT] EXISTS的子查询

    • EXISTS + True | NOT EXISTS + False,则执行前面的SELECT语句
    -- 查询部门表中部门5的员工情况
    -- 由于部门5,不存在,所有查询结果为空
    SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=5);
    SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM department WHERE id=4);
    SELECT id,username FROM employee WHERE NOT EXISTS(SELECT * FROM department WHERE id=5);
    
  • 使用ANY|SOME或者ALL的子查询

    关键字 运算符 ANY SOME ALL
    >、>= 最小值 最小值 最大值
    <、<= 最大值 最大值 最小值
    = 任意值 任意值
    <>、|= 任意值
    -- 查询所有获得奖学金的学员
    SELECT id, username,score FROM student WHERE score >=ANY(SELECT level FROM scholarship);
    
    -- 查询所有获得一等奖学金的学员
    SELECT id,username,score FROM student WHERE score >=ALL(SELECT level FROM scholarship);
    
    -- 查询学员表中没有获得奖学金的学员
    SELECT id,username,score FROM student WHERE score <=ALL(SELECT level FROM scholarship);
    
    -- =ANY | SOME相当于IN
    SELECT id,username,score FROM student WHERE score=ANY(SELECT level FROM scholarship);
    SELECT id,username,score FROM student WHERE score IN (SELECT level FROM scholarship);
    
    -- <>相当于NOT IN
    SELECT id,username,score FROM student WHERE score NOT IN(SELECT level FROM scholarship);
    SELECT id,username,score FROM student WHERE score <> ALL(SELECT level FROM scholarship);
    

3.将查询结果写入到数据表

INSERT [INTO] tbl_name [(col_name)] SELECT

CREATE TABLE test1(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
num TINYINT UNSIGNED
);

-- 先获得查询结果,再将查询结果插入test1中
INSERT test1(id,num)
SELECT id,score FROM student;

4.创建数据表同时将查询结果写入到数据表

CREATE TABLE IF [ IF NOT EXISTS ] tbl_name [(create_definition, …)] select_statement

-- 创建的字段最好与插入的信息字段名称一致,否则会在表内产生新的字段
CREATE TABLE IF NOT EXISTS test2(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
score TINYINT UNSIGNED
)SELECT id, score FROM student;

SHOW CREATE TABLE employee;
DESC employee;
SELECT * FROM test2;
SELECT * FROM scholarship;

十一、正则表达式查询

1. REGEXP’匹配方式’

2.常用匹配方式

模式字符 含义
^ 匹配字符开始的部分
$ 匹配字符串结尾的部分
. 代表字符串中的任意一个字符,包括回车和换行
[字符合集] 匹配字符集合中的任何一个字符
[^字符合集] 匹配除了字符集合以外的任意字符
S1 | S2 | S3 匹配S1、S2、S3中的任意一个字符
* 代表0个1个或者多个其前的字符
+ 代表1个或者多个其前的字符
String{N} 字符串出现N次
字符串{M,N} 字符串至少出现N次,最多N次
-- ^匹配字符开始的部分
-- 查询用户名以t开始的用户,不区分大小写
SELECT * FROM cms_user WHERE username REGEXP'^T';

-- ¥匹配字符开始的部分
-- 查询用户名以g结尾的用户,不区分大小写
SELECT * FROM cms_user WHERE username REGEXP'g$';

-- . 代表任意字符
SELECT * FROM cms_user WHERE username REGEXP 'R..G';
SELECT * FROM cms_user WHERE username LIKE 'R__G';

-- [字符集合] 匹配用户名中含有[lto]的用户信息
SELECT * FROM cms_user WHERE username REGEXP '[LTO]';

-- [^字符集合] 除了字符集合中的内容
-- 如果字符串中还包含[^]意外的字母,则依然可以被筛选出来,lily不会被筛选出来
SELECT * FROM cms_user WHERE username REGEXP '[^lily]';
-- ttt不会被晒出出来
INSERT cms_user(username,password,regTime,proId) VALUES('ttt','111',20200717,2);
SELECT * FROM cms_user WHERE username REGEXP '[^LTO]';

-- 匹配ng、qu、te中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP 'NG|QU|TE';

-- 代表0个1个或者多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP 'QUE*';

-- 代表1个或者多个其前的字符
SELECT * FROM cms_user WHERE username REGEXP 'QUE+';

-- 代表QUE出现1次到3次
SELECT * FROM cms_user WHERE username REGEXP 'QUE{1,3}';

未经允许不得转载:作者:1152-张同学, 转载或复制请以 超链接形式 并注明出处 拜师资源博客
原文地址:《MySQL3:连接查询、子查询及正则表达式查询》 发布于2020-07-17

分享到:
赞(0) 打赏

评论 抢沙发

评论前必须登录!

  注册



长按图片转发给朋友

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏

Vieu3.3主题
专业打造轻量级个人企业风格博客主题!专注于前端开发,全站响应式布局自适应模板。

登录

忘记密码 ?

您也可以使用第三方帐号快捷登录

Q Q 登 录
微 博 登 录