文章目录
1.连接查询
1.1 什么是连接查询?
连接查询是将两个或两个以上的表按某个条件连接起来,从中选取需要的数据。
连接查询是同时查询两个或两个以上的表时使用的。
当不同的表中存在相同意义的字段时,可以通过该字段连接这几个表
(找到表和表之间的关系,链接的桥梁)
1.2 内连接查询
①连接我们的表JOIN|CROSS JOIN| INNER JOIN
②通过ON连接条件
③显示两个表中符合连接条件的记录
1.2.1测试内连接查询
--查询cms_user id,username
--从provinces,proName两张表中
SELECT cms_user.id,username,proName FROM cms_user,proviences;
#相当于for循环嵌套,不是我们想要的结果
--cms_user的proId对应省份表中的id,可以解决上面出现的问题
--方法1通过WHERE来等价内连接
SELECT cms_user.id,username,proName FROM cms_user,provinces
WHERE cms_user.proId=provinces.id;
+----+----------+---------+
| id | username | proName |
+----+----------+---------+
| 1 | 张三 | 北京 |
| 2 | 张三丰 | 上海 |
| 3 | 章子怡 | 深圳 |
| 4 | long | 广州 |
| 5 | ring | 上海 |
| 6 | queen | 深圳 |
| 7 | king | 重庆 |
| 8 | blek | 北京 |
| 9 | rose | 上海 |
| 10 | lily | 上海 |
| 11 | john | 上海 |
| 12 | test1 | 北京 |
+----+----------+---------+
--方法2通过 JOIN|CROSS JOIN| INNER JOIN 连接
--第一步明确查询哪些记录
#查询cms_user中的id,username,email,sex
#查询provinces表中的proName
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;
--查询cms_user id,username,sex;
--查询provinces proName;
--条件是cms_user的性别为男的用户,根据proName分组,数出每组人数和显示详细信息
SELECT u.id,GROUP_CONCAT(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='男'
--对分组结果进行筛选,选出组中人数>=1的;
--按照id升序排列,限制显示条数 前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(*)>=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_cate 中的cateName
--查询cms_admin中的username,role
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM
cms_news AS n
JOIN
cms_cate AS c
ON n.cId=c.id
JOIN
cms_admin AS a
ON n.aId=a.id;
1.3外连接查询
1.3.1.简介
左外连接:LEFT [OUTER] JOIN显示左表的全部记录及右表符合连接条件的记录
右外连接:RIGHT [OUTER] JOIN 显示右表的全部记录以及左表符合条件的记录
--内连接的局限
--插入垃圾的数据
INSERT cms_user(username,password,regTime,proId)
VALUES('TEST2','TEST2','1381203974',20);
#省份只有五个,proId=20明显是匹配不到结果的,所以在内连接查询中是查不到新插入的TEST2这个数据的
--测试左外连接
--主表是 cms_user
--在外连接查询中是可以查到新插入的TEST2这个数据的,省份栏显示NULL
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;
+----+----------+-------------+------+---------+
| id | username | email | sex | proName |
+----+----------+-------------+------+---------+
| 1 | 张三 | user@qq.com | 男 | 北京 |
| 2 | 张三丰 | user@qq.com | 女 | 上海 |
| 3 | 章子怡 | user@qq.com | 男 | 深圳 |
| 4 | long | user@qq.com | 女 | 广州 |
| 5 | ring | user@qq.com | 男 | 上海 |
| 6 | queen | user@qq.com | 女 | 深圳 |
| 7 | king | user@qq.com | 男 | 重庆 |
| 8 | blek | user@qq.com | 女 | 北京 |
| 9 | rose | user@qq.com | 男 | 上海 |
| 10 | lily | user@qq.com | 女 | 上海 |
| 11 | john | user@qq.com | 保密 | 上海 |
| 12 | test1 | user@qq.com | 保密 | 北京 |
| 13 | TEST2 | user@qq.com | NULL | NULL |
+----+----------+-------------+------+---------+
--左外连接交换主表
主表是 provinces
查不到TEST2,因为右表cms_user中TEST2(proId=20)不符合条件
SELECT u.id,u.username,u.email,u.sex,p.proName
FROM provinces AS p
LEFT JOIN cms_user AS u
ON u.proId=p.id;
+----+----------+-------------+------+---------+
| id | username | email | sex | proName |
+----+----------+-------------+------+---------+
| 1 | 张三 | user@qq.com | 男 | 北京 |
| 2 | 张三丰 | user@qq.com | 女 | 上海 |
| 3 | 章子怡 | user@qq.com | 男 | 深圳 |
| 4 | long | user@qq.com | 女 | 广州 |
| 5 | ring | user@qq.com | 男 | 上海 |
| 6 | queen | user@qq.com | 女 | 深圳 |
| 7 | king | user@qq.com | 男 | 重庆 |
| 8 | blek | user@qq.com | 女 | 北京 |
| 9 | rose | user@qq.com | 男 | 上海 |
| 10 | lily | user@qq.com | 女 | 上海 |
| 11 | john | user@qq.com | 保密 | 上海 |
| 12 | test1 | user@qq.com | 保密 | 北京 |
|NULL| NULL | NULL | NULL | 重庆 |
+----+----------+-------------+------+---------+
#province是主表所以所有省份都能看到,但是没有对应的省份的值显示为NULL
--测试右外连接
--主表为cms_user,谁在最右边谁是主表
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;
+----+----------+-------------+------+---------+
| id | username | email | sex | proName |
+----+----------+-------------+------+---------+
| 1 | 张三 | user@qq.com | 男 | 北京 |
| 2 | 张三丰 | user@qq.com | 女 | 上海 |
| 3 | 章子怡 | user@qq.com | 男 | 深圳 |
| 4 | long | user@qq.com | 女 | 广州 |
| 5 | ring | user@qq.com | 男 | 上海 |
| 6 | queen | user@qq.com | 女 | 深圳 |
| 7 | king | user@qq.com | 男 | 重庆 |
| 8 | blek | user@qq.com | 女 | 北京 |
| 9 | rose | user@qq.com | 男 | 上海 |
| 10 | lily | user@qq.com | 女 | 上海 |
| 11 | john | user@qq.com | 保密 | 上海 |
| 12 | test1 | user@qq.com | 保密 | 北京 |
| 13 | TEST2 | user@qq.com | NULL | NULL |
1.4外键
保证数据的完整性和一致性,确保垃圾数据不被录入
1.4.1外键的介绍
外键是表的一个特殊字段。
被参照的表是主表,外键所在字段的表为子表,先有主表参照主表中的主要字段来创建外键。
设置外键的原则需要记住,就是依赖于数据库中已存在的表的主键。
外键的作用是建立该表与其父表的关联关系。
父表中对记录做操作时,子表中与之对应的信息也应有相应的改变。
外键的作用保持数据的一致性和完整性
可以实现一一对一或一对多的关系
注意
父表和子表必须使用相同的存储引擎(只能为InnoDB),而且禁止使用临时表。
方法:建表的时候添加外键或者用alter语句来添加或者删除外键
外键列和参照列必须具有相似的数据类型:其中数字的长度或是否有符号位必须相同;而字符的长度则可以不同。
外键列和参照列必须创建索引,如果外键列不存在索引的话,MySQL将自动创建索引当删除外键时,索引是不会被删掉的。
外键约束的参照操作
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。
SET NULL:从父表删除或更新行,并设置子表中的外键列为NULL。如果使用该选项,必须保证子表列没有指定NOT NULL。
RESTRICT:拒绝对父表的删除或更新操作。
NO ACTION :标准SQL的关键字,在MySQL中与 RESTRICT相同
先用内连接方式连接
--创建部门表department(主表);
--id depName
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(子表);
--id ,username ,depId
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),
('张三',3),
('李四',4),
('王五',1);
--利用内连接将员工与所属部门匹配
SELECT e.id,e.username,d.depName FROM
employee AS e
JOIN
department AS d
ON e.depId=d.id;
内连接的局限性
--删除督导部
DELETE FROM department WHERE depName='督导部';
--虽然‘督导部’现在已经没有了,但是查询的时候,李四仍然在4部门,也就是对应的督导部。
--由此,可以看出内连接方式的一致性并不是很好,我们想要达到的效果是先将部门下的员工删除然后再删除部门
mysql> SELECT * FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 1 | king | 1 |
| 2 | queen | 2 |
| 3 | 张三 | 3 |
| 4 | 李四 | 4 |
| 5 | 王五 | 1 |
1.4.2 外键的使用
--先有部门再有员工
--创建部门表department(主表);
--id depName
CREATE TABLE IF NOT EXISTS department(
id TINYINT UNSIGNED AUTO_INCREMENT KEY,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;
INSERT department(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');
加入外键 FOREIGN KEY
--创建员工表employee(子表);
--id ,username ,depId
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),
('张三',3),
('李四',4),
('王五',1);
直接删除主表记录失败
--删除主表中的记录
DELETE FROM department WHERE id=1;
mysql> DELETE FROM department WHERE id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))
先对子表进行操作
--删除employee中的属于1部门的人
DELETE FROM employee WHERE depId=1;
1
接下来在删除 id=1 的部门就能成功了
DELETE FROM department WHERE id=1;
mysql> SELECT * FROM department;
+----+---------+
| id | depName |
+----+---------+
| 2 | 市场部 |
| 4 | 督导部 |
| 3 | 运营部 |
+----+---------+
测试插入错误数据
--depId=11不在范围内,插入失败
INSERT employee(username,depId) VALUES('test',11);
mysql> INSERT employee(username,depId) VALUES('test',11);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cms`.`employee`, CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))
1.4.3 外键的添加删除
--删除员工表
DROP TABLE employee;
--重新创建
CREATE TABLE IF NOT EXISTS cms.employee(
id SMALLINT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id)
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',3),
('queen',2),
('张三',3),
('李四',4),
('王五',2);
--1.删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_fk_dep;
--2.添加外键
删除id=2
DELETE FROM department WHERE id=2;
--添加外键失败
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
--提示错误:
mysql> ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cms`.`#sql-f18_7`, CONSTRAINT `emp_fk_dep` FOREIGN KEY (`depId`) REFERENCES `department` (`id`))
--删除对应的depId=2,成功
mysql> DELETE FROM employee WHERE depId=2;
ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);
--3.测试 CASCADE
删除之前的表,重新创建employee和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(子表);id ,username ,depId
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 ON UPDATE CASCADE
#对删除和更新操作都应用CASCADE
)ENGINE=INNODB;
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
--删除部门表中的第一个部门
DELETE FROM department WHERE id=1;
#department和employee中对应的部分和部分中的员工都被删除了
mysql> SELECT * FROM department;
+----+---------+
| id | depName |
+----+---------+
| 2 | 市场部 |
| 4 | 督导部 |
| 3 | 运营部 |
+----+---------+
3 rows in set (0.00 sec)
mysql> SELECT * FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 2 | queen | 2 |
| 3 | 张三 | 3 |
| 4 | 李四 | 4 |
+----+----------+-------+
3 rows in set (0.00 sec)
--更新id
UPDATE department SET id=id+10;
--4.测试 SET NULL
删除之前的表,重新创建employee和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('教学部'),
('市场部'),
('运营部'),
('督导部');
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; #删除和更新操作都加入SET NULL
INSERT employee(username,depId) VALUES('king',1),
('queen',2),
('张三',3),
('李四',4),
('王五',1);
--删除id=1
DELETE FROM department WHERE id=1;
#king和王五对应的depId变为NULL
mysql> SELECT * FROM employee;
+----+----------+-------+
| id | username | depId |
+----+----------+-------+
| 1 | king | NULL |
| 2 | queen | 2 |
| 3 | 张三 | 3 |
| 4 | 李四 | 4 |
| 5 | 王五 | NULL |
+----+----------+-------+
5 rows in set (0.00 sec)
1.5 联合查询
1.5.1 UNION&UNION ALL简介
UNION和IUNION ALL区别是UNION去掉相同记录,UION ALL是简单的合并到一起。
1.5.2 UNION
SELECT username FROM employee UNION SELECT username FROM cms_user;
#employee中有5条记录,cms_user中有12条记录,但是查询结果是15条,因为有两条记录重复
1.5.3 UNION ALL
SELECT username FROM employee UNION ALL SELECT username FROM cms_user;
#因为UION ALL是简单的合并到一起,所以查询结果可以重复,共有17条
1.5.4 字段个数需要匹配
--字段个数不匹配时会报错
SELECT id,username FROM employee UNION ALL SELECT username FROM cms_user;
mysql> SELECT id,username FROM employee UNION ALL SELECT username FROM cms_user;
ERROR 1222 (21000): The used SELECT statements have a different number of columns
--只要字段个数匹配就行
SELECT id,username FROM employee UNION ALL SELECT username,age FROM cms_user;