端州网站建设,公众号平台官网网页版,北京网站优化排名推广,低价网站备案MySQL 多表查询
当两个表查询时#xff0c;从第一张表中取出一行和第二张表的每一行进行组合
返回结果含有两张表的所有列#xff0c;一共返回的记录数第一张表行数*第二张表的行数#xff08;笛卡尔积#xff09;
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集…MySQL 多表查询
当两个表查询时从第一张表中取出一行和第二张表的每一行进行组合
返回结果含有两张表的所有列一共返回的记录数第一张表行数*第二张表的行数笛卡尔积
-- ?显示雇员名,雇员工资及所在部门的名字 【笛卡尔集】
/*1. 雇员名,雇员工资 来自 emp表2. 部门的名字 来自 dept表3. 需求对 emp 和 dept查询 ename,sal,dname,deptno4. 当我们需要指定显示某个表的列是需要 表.列表
*/
SELECT ename,sal,dname,emp.deptnoFROM emp, dept WHERE emp.deptno dept.deptnoselect * from emp;
select * from dept;
select * from salgrade;
-- 小技巧多表查询的条件不能少于 表的个数-1, 否则会出现笛卡尔集
-- ?如何显示部门号为10的部门名、员工名和工资
-- 重复的deptno要标清是哪个表的
SELECT ename,sal,dname,emp.deptnoFROM emp, dept WHERE emp.deptno dept.deptno and emp.deptno 10自连接
自连接是指在同一张表的连接查询[将同一张表看做两张表]。
-- 多表查询的 自连接-- 显示公司员工名字和他的上级的名字-- 员工名字 在emp, 上级的名字的名字 emp
-- 员工和上级是通过 emp 表的 mgr 列关联
-- 自连接的特点 1. 把同一张表当做两张表使用
-- 2. 需要给表取别名 表名 表别名
-- 3. 列名不明确可以指定列的别名 列名 as 列的别名 SELECT worker.ename AS 职员名 , boss.ename AS 上级名FROM emp worker, emp boss -- 同一张表用不同的别名WHERE worker.mgr boss.empno;子查询
子查询是指嵌入在其它 sql 语句中的select 语句也叫嵌套查询
单行子查询是指只返回一行数据的子查询语句
多行子查询指返回多行数据的子查询 使用关键字in
-- 显示与SMITH同一部门的所有员工?
/*1. 先查询到 SMITH的部门号得到2. 把上面的select 语句当做一个子查询来使用
*/
SELECT deptno FROM emp WHERE ename SMITHSELECT * FROM empWHERE deptno (SELECT deptno FROM emp WHERE ename SMITH)-- 如何查询和部门10的工作相同的雇员的
-- 名字、岗位、工资、部门号, 但是不含10号部门自己的雇员./*1. 查询到10号部门有哪些工作2. 把上面查询的结果当做子查询使用
*/select ename, job, sal, deptnofrom empwhere job in (SELECT DISTINCT job FROM emp WHERE deptno 10) and deptno 10 子查询当临时表使用 -- 查找每个部门工资高于本部门平均工资的人的资料
-- 把一个子查询当作一个临时表使用-- 1. 先得到每个部门的 部门号和 对应的平均工资SELECT deptno, AVG(sal) AS avg_salFROM emp GROUP BY deptno-- 2. 把上面的结果当做子查询作为temp表, 和 emp 进行多表查询
SELECT ename, sal, temp.avg_sal, emp.deptnoFROM emp, (SELECT deptno, AVG(sal) AS avg_salFROM emp GROUP BY deptno) temp where emp.deptno temp.deptno and emp.sal temp.avg_sal-- 查找每个部门工资最高的人的详细资料SELECT ename, sal, temp.max_sal, emp.deptnoFROM emp, (SELECT deptno, max(sal) AS max_salFROM emp GROUP BY deptno) temp WHERE emp.deptno temp.deptno AND emp.sal temp.max_sal-- 查询每个部门的信息(包括部门名,编号,地址)和人员数量-- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量 -》 构建一个临时表select count(*), deptno from empgroup by deptno;select dname, dept.deptno, loc , tmp.per_num as 人数from dept, (SELECT COUNT(*) as per_num, deptno FROM empGROUP BY deptno) tmp where tmp.deptno dept.deptno-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句
-- 在多表查询中当多个表的列不重复时才可以直接写列名SELECT tmp.* , dname, locFROM dept, (SELECT COUNT(*) AS per_num, deptno FROM empGROUP BY deptno) tmp WHERE tmp.deptno dept.deptno多行子查询中使用 all 或 any 操作符
-- 显示工资比部门30的所有员工的工资高的员工的姓名、工资和部门号SELECT ename, sal, deptnoFROM empWHERE sal ALL(SELECT sal FROM empWHERE deptno 30)
-- 可以这样写
SELECT ename, sal, deptnoFROM empWHERE sal (SELECT MAX(sal) FROM empWHERE deptno 30) -- 显示工资比部门30的其中一个员工的工资高的员工的姓名、工资和部门号SELECT ename, sal, deptnoFROM empWHERE sal any(SELECT sal FROM empWHERE deptno 30)SELECT ename, sal, deptnoFROM empWHERE sal (SELECT min(sal) FROM empWHERE deptno 30)多列子查询
多列子查序则是指查询返回多个列数据的子查询语句
-- 查询与allen的部门和岗位完全相同的所有雇员(并且不含allen本人)
-- (字段1 字段2 ...) (select 字段 1字段2 from 。。。。)-- 分析: 1. 得到smith的部门和岗位SELECT deptno , jobFROM emp WHERE ename ALLEN-- 分析: 2 把上面的查询当做子查询来使用并且使用多列子查询的语法进行匹配
SELECT * FROM empWHERE (deptno , job) (SELECT deptno , jobFROM emp WHERE ename ALLEN) AND ename ! ALLEN-- 请查询 和宋江数学英语语文
-- 成绩 完全相同的学生
SELECT * FROM studentWHERE (math, english, chinese) (SELECT math, english, chineseFROM studentWHERE name 宋江)在from 子句中使用子查询
-- 查找每个部门工资高于本部门平均工资的人的资料
-- 把一个子查询当作一个临时表使用-- 1. 先得到每个部门的 部门号和 对应的平均工资SELECT deptno, AVG(sal) AS avg_salFROM emp GROUP BY deptno-- 2. 把上面的结果当做子查询, 和 emp 进行多表查询
--
SELECT ename, sal, temp.avg_sal, emp.deptnoFROM emp, (SELECT deptno, AVG(sal) AS avg_salFROM emp GROUP BY deptno) temp where emp.deptno temp.deptno and emp.sal temp.avg_sal-- 查找每个部门工资最高的人的详细资料SELECT ename, sal, temp.max_sal, emp.deptnoFROM emp, (SELECT deptno, max(sal) AS max_salFROM emp GROUP BY deptno) temp WHERE emp.deptno temp.deptno AND emp.sal temp.max_sal-- 查询每个部门的信息(包括部门名,编号,地址)和人员数量-- 1. 部门名,编号,地址 来自 dept表
-- 2. 各个部门的人员数量 -》 构建一个临时表select count(*), deptno from empgroup by deptno;select dname, dept.deptno, loc , tmp.per_num as 人数from dept, (SELECT COUNT(*) as per_num, deptno FROM empGROUP BY deptno) tmp where tmp.deptno dept.deptno-- 还有一种写法 表.* 表示将该表所有列都显示出来, 可以简化sql语句
-- 在多表查询中当多个表的列不重复时才可以直接写列名SELECT tmp.* , dname, locFROM dept, (SELECT COUNT(*) AS per_num, deptno FROM empGROUP BY deptno) tmp WHERE tmp.deptno dept.deptno表复制
有时为了对某个sql语句进行效率测试我们需要海量数据时可以使用此法为表创建海量数据。
-- 表的复制
-- 为了对某个sql语句进行效率测试我们需要海量数据时可以使用此法为表创建海量数据CREATE TABLE my_tab01 ( id INT,name VARCHAR(32),sal DOUBLE,job VARCHAR(32),deptno INT);
DESC my_tab01
SELECT * FROM my_tab01;-- 演示如何自我复制
-- 1. 先把emp 表的记录复制到 my_tab01
INSERT INTO my_tab01 (id, name, sal, job,deptno)SELECT empno, ename, sal, job, deptno FROM emp;-- 2. 自我复制
INSERT INTO my_tab01SELECT * FROM my_tab01;
SELECT COUNT(*) FROM my_tab01;-- 如何删除掉一张表重复记录
-- 1. 先创建一张表 my_tab02,
-- 2. 让 my_tab02 有重复的记录CREATE TABLE my_tab02 LIKE emp; -- 这个语句 把emp表的结构(列)复制到my_tab02desc my_tab02;insert into my_tab02select * from emp;
select * from my_tab02;
-- 3. 考虑去重 my_tab02的记录
/*思路 (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样(2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp(3) 清除掉 my_tab02 记录(4) 把 my_tmp 表的记录复制到 my_tab02(5) drop 掉 临时表my_tmp
*/
-- (1) 先创建一张临时表 my_tmp , 该表的结构和 my_tab02一样create table my_tmp like my_tab02
-- (2) 把my_tmp 的记录 通过 distinct 关键字 处理后 把记录复制到 my_tmp
insert into my_tmp select distinct * from my_tab02;-- (3) 清除掉 my_tab02 记录
delete from my_tab02;
-- (4) 把 my_tmp 表的记录复制到 my_tab02
insert into my_tab02select * from my_tmp;
-- (5) drop 掉 临时表my_tmp
drop table my_tmp;select * from my_tab02;合并查询
有时在实际应用中为了合并多个 select 语句的结果可以使用集合操作符号 union union all。
union all该操作符用于取得两个结果集的并集。当使用该操作符时不会取消重复行union该操作赋与union all相似,但是会自动去掉结果集中重复行
-- 合并查询
SELECT ename,sal,job FROM emp WHERE sal2500 -- 5
SELECT ename,sal,job FROM emp WHERE jobMANAGER -- 3-- union all 就是将两个查询结果合并不会去重
SELECT ename,sal,job FROM emp WHERE sal2500 -- 5
UNION ALL
SELECT ename,sal,job FROM emp WHERE jobMANAGER -- 3-- union 就是将两个查询结果合并会去重
SELECT ename,sal,job FROM emp WHERE sal2500 -- 5
UNION
SELECT ename,sal,job FROM emp WHERE jobMANAGER -- 3mysql 表外连接
前面我们学习的查询是利用where子句对两张表或者多张表形成的笛卡尔积进行筛选
根据关联条件显示所有匹配的记录匹配不上的不显示。 左外连接(如果左侧的表完全显示我们就说是左外连接) 右外连接(如果右侧的表完全显示我们就说是右外连接)
-- 比如列出部门名称和这些部门的员工名称和工作
-- 同时要求 显示出那些没有员工的部门。-- 使用学习过的多表查询的SQL 看看效果如何?SELECT dname, ename, job FROM emp, deptWHERE emp.deptno dept.deptnoORDER BY dname
SELECT * FROM dept;SELECT * FROM emp;-- 创建 stu
/*
id name
1 Jack
2 Tom
3 Kity
4 nono
*/
CREATE TABLE stu (id INT,name VARCHAR(32));
INSERT INTO stu VALUES(1, jack),(2,tom),(3, kity),(4, nono);
SELECT * FROM stu;
-- 创建 exam
/*
id grade
1 56
2 76
11 8*/
CREATE TABLE exam(id INT,grade INT);
INSERT INTO exam VALUES(1, 56),(2,76),(11, 8);
SELECT * FROM exam;-- 使用左连接
-- 显示所有人的成绩如果没有成绩也要显示该人的姓名和id号,成绩显示为空SELECT name, stu.id, gradeFROM stu, examWHERE stu.id exam.id;-- 改成左外连接
SELECT name, stu.id, gradeFROM stu LEFT JOIN examON stu.id exam.id;-- 使用右外连接显示所有成绩如果没有名字匹配显示空)
-- 即右边的表(exam) 和左表没有匹配的记录也会把右表的记录显示出来
SELECT name, stu.id, gradeFROM stu RIGHT JOIN examON stu.id exam.id;-- 列出部门名称和这些部门的员工信息(名字和工作)
-- 同时列出那些没有员工的部门名。5min
-- 使用左外连接实现
SELECT dname, ename, jobFROM dept LEFT JOIN empON dept.deptno emp.deptno-- 使用右外连接实现SELECT dname, ename, jobFROM emp RIGHT JOIN deptON dept.deptno emp.deptnomysql 索引
CREATE INDEX ename_index ON emp (ename) -- 在 ename 上创建索引-- 创建测试数据库 tmp
CREATE DATABASE tmp;CREATE TABLE dept( /*部门表*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT ,
loc VARCHAR(13) NOT NULL DEFAULT
) ;#创建表EMP雇员
CREATE TABLE emp
(empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT , /*名字*/
job VARCHAR(9) NOT NULL DEFAULT ,/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
) ;#工资级别表
CREATE TABLE salgrade
(
grade MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
losal DECIMAL(17,2) NOT NULL,
hisal DECIMAL(17,2) NOT NULL
);#测试数据
INSERT INTO salgrade VALUES (1,700,1200);
INSERT INTO salgrade VALUES (2,1201,1400);
INSERT INTO salgrade VALUES (3,1401,2000);
INSERT INTO salgrade VALUES (4,2001,3000);
INSERT INTO salgrade VALUES (5,3001,9999);DELIMITER $$#创建一个函数名字 rand_string可以随机返回我指定的个数字符串
CREATE FUNCTION rand_string(n INT)
RETURNS VARCHAR(255) #该函数会返回一个字符串
BEGIN
#定义了一个变量 chars_str 类型 varchar(100)
#默认给 chars_str 初始值 abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZDECLARE chars_str VARCHAR(100) DEFAULTabcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ; DECLARE return_str VARCHAR(255) DEFAULT ;DECLARE i INT DEFAULT 0; WHILE i n DO# concat 函数 : 连接函数mysql函数SET return_str CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1RAND()*52),1));SET i i 1;END WHILE;RETURN return_str;END $$#这里我们又自定了一个函数,返回一个随机的部门号
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i FLOOR(10RAND()*500);
RETURN i;
END $$#创建一个存储过程 可以添加雇员
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit 0 把autocommit设置成0#autocommit 0 含义: 不要自动提交SET autocommit 0; #默认不提交sql语句REPEATSET i i 1;#通过前面写的函数随机产生字符串和部门编号然后加入到emp表INSERT INTO emp VALUES ((STARTi) ,rand_string(6),SALESMAN,0001,CURDATE(),2000,400,rand_num());UNTIL i max_numEND REPEAT;#commit整体提交所有sql语句提高效率COMMIT;END $$#添加8000000数据
CALL insert_emp(100001,8000000)$$#命令结束符再重新设置为;
DELIMITER ;SELECT COUNT(*) FROM emp;-- 在没有创建索引时我们的查询一条记录
SELECT * FROM emp WHERE empno 1234567
-- 使用索引来优化一下 体验索引的牛-- 在没有创建索引前 , emp.ibd 文件大小 是 524m
-- 创建索引后 emp.ibd 文件大小 是 655m [索引本身也会占用空间.]
-- 创建ename列索引,emp.ibd 文件大小 是 827m-- empno_index 索引名称
-- ON emp (empno) : 表示在 emp表的 empno列创建索引
CREATE INDEX empno_index ON emp (empno)-- 创建索引后 查询的速度如何SELECT * FROM emp WHERE empno 1234578 -- 0.003s 原来是4.5s-- 创建索引后只对创建了索引的列有效
SELECT * FROM emp WHERE ename PjDlwy -- 没有在ename创建索引时时间4.7sCREATE INDEX ename_index ON emp (ename) -- 在ename上创建索引索引的原理
没有索引会慢是因为全表扫描.
使用索引后形成一个索引的数据结构比如二叉树索引有代价 如下
磁盘占用对dml(update delete insert)语句的效率影响删除或者插入将会对数据结构造成影响可能会重构。 索引的类
主键索引主键自动的为主索引 (类型Primary key)唯一索引(UNIQUE)普通索引(INDEX)全文索引(FULLTEXT)[适用于MyISAM]
一般开发不使用mysql自带的全文索引而是使用全文搜索的框架Solr 和 ElasticSearch ( ES )
create table t1(
id int primary key, -- 主键同时也是索引称为主键索引.name varchar(32));
create table t2(
id int unique, -- id是唯一的同时也是索引称为unique索引.索引使用
1.添加索引( 建小表测试id , name )
create [UNIQUE] index index_name on tbl_name (col_ name [(length)][ASC | DESC],......);
alter table table_name ADD INDEX [index_namel (index_col_name,..)2.添加主键(索引)
ALTER TABLE 表名 ADD PRIMARY KEY(列名...);3.删除索引
DROP INDEX index_name ON tbl_name,
alter table table_name drop index index_name;4.删除主键索引比较特别:
alter table t_b drop primary key;5.查询索引(三种方式)
show index(es) from table_name;
show keys from table_name;
desc table_Name;-- 创建索引
CREATE TABLE t25 (id INT ,name VARCHAR(32));-- 查询表是否有索引
SHOW INDEXES FROM t25;
-- 添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t25 (id);
-- 添加普通索引方式1
CREATE INDEX id_index ON t25 (id);
-- 如何选择
-- 1. 如果某列的值是不会重复的则优先考虑使用unique索引, 否则使用普通索引
-- 添加普通索引方式2
ALTER TABLE t25 ADD INDEX id_index (id)-- 添加主键索引
CREATE TABLE t26 (id INT ,name VARCHAR(32));
ALTER TABLE t26 ADD PRIMARY KEY (id)SHOW INDEX FROM t25-- 删除索引
DROP INDEX id_index ON t25
-- 删除主键索引
ALTER TABLE t26 DROP PRIMARY KEY-- 修改索引 先删除在添加新的索引-- 查询索引
-- 1. 方式
SHOW INDEX FROM t25
-- 2. 方式
SHOW INDEXES FROM t25
-- 3. 方式
SHOW KEYS FROM t25
-- 4 方式
DESC t25注意
较频繁的作为查询条件字段应该创建索引唯一性太差的字段不适合单独创建索引即使频繁作为查询条件更新非常频繁的字段不适合创建索引不会出现在WHERE子句中字段不该创建索引
