MySQL联表查询基本操作之left-join常见的坑_Mysql

来源:脚本之家  责任编辑:小易  

1.索引作用在索引列上,除了上面提到的有序查找之外,数据库利用各种各样的快速定位技术,能够大大提高查询效率。特别是当数据量非常大,查询涉及多个表时,使用索引往往能使查询速度加快成千上万倍。例如,有3个未索引的表t1、t2、t3,分别只包含列c1、c2、c3,每个表分别含有1000行数据组成,指为1~1000的数值,查找对应值相等行的查询如下所示。SELECT c1,c2,c3 FROM t1,t2,t3 WHERE c1=c2 AND c1=c3此查询结果应该为1000行,每行包含3个相等的值。在无索引的情况下处理此查询,必须寻找3个表所有的组合,以便得出与WHERE子句相配的那些行。而可能的组合数目为1000×1000×1000(十亿),显然查询将会非常慢。如果对每个表进行索引,就能极大地加速查询进程。利用索引的查询处理如下。(1)从表t1中选择第一行,查看此行所包含的数据。(2)使用表t2上的索引,直接定位t2中与t1的值匹配的行。类似,利用表t3上的索引,直接定位t3中与来自t1的值匹配的行。(3)扫描表t1的下一行并重复前面的过程,直到遍历t1中所有的行。在此情形下,仍然对表t1执行了一个完全扫描,但能够在表t2和t3上进行索引查找直接取出这些表中的行,比未用索引时要快一百万倍。利用索引,MySQL加速了WHERE子句满足条件行的搜索,而在多表连接查询时,在执行连接时加快了与其他表中的行匹配的速度。2.创建索引在执行CREATE TABLE语句时可以创建索引,也可以单独用CREATE INDEX或ALTER TABLE来为表增加索引。1.ALTER TABLEALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。ALTER TABLE table_name ADD INDEX index_name(column_list)ALTER TABLE table_name ADD UNIQUE(column_list)ALTER TABLE table_name ADD PRIMARY KEY(column_list)其中table_name是要增加索引的表名,column_list指出对哪些列进行索引,多列时各列之间用逗号分隔。索引名index_name可选,缺省时,MySQL将根据第一个索引列赋一个名称。另外,ALTER TABLE允许在单个语句中更改多个表,因此可以在同时创建多个索引。2.CREATE INDEXCREATE INDEX可对表增加普通索引或UNIQUE索引。CREATE INDEX index_name ON table_name(column_list)CREATE UNIQUE INDEX index_name ON table_name(column_list)table_name、index_name和column_list具有与ALTER TABLE语句中相同的含义,索引名不可选。另外,不能用CREATE INDEX语句创建PRIMARY KEY索引。3.索引类型在创建索引时,可以规定索引能否包含重复值。如果不包含,则索引应该创建为PRIMARY KEY或UNIQUE索引。对于单列惟一性索引,这保证单列不包含重复的值。对于多列惟一性索引,保证多个值的组合不重复。PRIMARY KEY索引和UNIQUE索引非常类似。事实上,PRIMARY KEY索引仅是一个具有名称PRIMARY的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY,因为一个表中不可能具有两个同名的索引。下面的SQL语句对students表在sid上添加PRIMARY KEY索引。ALTER TABLE students ADD PRIMARY KEY(sid)4.删除索引可利用ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句,DROP INDEX可以在ALTER TABLE内部作为一条语句处理,语法如下。DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY其中,前两条语句是等价的,删除掉table_name中的索引index_name。第3条语句只在删除PRIMARY KEY索引时使用,因为一个表只可能有一个PRIMARY KEY索引,因此不需要指定索引名。如果没有创建PRIMARY KEY索引,但表具有一个或多个UNIQUE索引,则MySQL将删除第一个UNIQUE索引。如果从表中删除了某列,则索引会受到影响。对于多列组合的索引,如果删除其中的某列,则该列也会从索引中删除。如果删除组成索引的所有列,则整个索引将被删除。5.查看索引mysql>show index from tblname;mysql>show keys from tblname;Table表的名称。Non_unique如果索引不能包括重复词,则为0。如果可以,则为1。Key_name索引的名称。Seq_in_index索引中的列序列号,从1开始。Column_name列名称。Collation列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk-a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。Sub_part如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。Packed指示关键字如何被压缩。如果没有被压缩,则为NULL。Null如果列含有NULL,则含有YES。如果没有,则该列含有NO。Index_type用过的索引方法(BTREE,FULLTEXT,HASH,RTREE)。Comment6.什么情况下使用索引表的主关键字自动建立唯一索引如zl_yhjbqk(用户基本情况)中的hbs_bh(户标识编号)表的字段唯一约束ORACLE利用索引来保证数据的完整性如lc_hj(流程环节)中的lc_bh+hj_sx(流程编号+环节顺序)直接条件查询的字段在SQL中用于条件约束的字段如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)select*from zl_yhjbqk where qc_bh=’7001’查询中与其它表关联的字段字段常常建立了外键关系如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)select*from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’查询中排序的字段排序的字段如果通过索引去访问那将大大提高排序速度select*from zl_yhjbqk order by qc_bh(建立qc_bh索引)select*from zl_yhjbqk where qc_bh=’7001’order by cb_sx(建立qc_bh+cb_sx索引,注:只是一个索引,其中包括qc_bh和cb_sx字段)查询中统计或分组统计的字段select max(hbs_bh)from zl_yhjbqkselect qc_bh,count(*)from zl_yhjbqk group by qc_bh什么情况下应不建或少建索引表记录太少如果一个表只有5条记录,采用索引去访问记录的话,那首先需访问索引表,再通过索引表访问数据表,一般索引表与数据表不在同一个数据块,这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出,处理速度显然会比用索引快。如表zl_sybm(使用部门)一般只有几条记录,除了主关键字外对任何一个字段建索引都不会产生性能优化,实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引,而是自动执行全表访问。如:select*from zl_sybm where sydw_bh=’5401’(对sydw_bh建立索引不会产生性能优化)经常插入、删除、修改的表对一些经常处理的业务表应在查询允许的情况下尽量减少索引,如zl_yhbm,gc_dfss,gc_dfys,gc_fpdy等业务表。数据重复且分布平均的表字段假如一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建索引一般不会提高数据库的查询速度。经常和主字段一块查询但主字段索引值比较多的表字段如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况,如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间,从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条,如果再按后面的几个字段索引查询将对性能不产生太大的影响。对千万级MySQL数据库建立索引的事项及提高性能的手段一、注意事项:首先,应当考虑表空间和磁盘空间是否足够。我们知道索引也是一种数据,在建立索引的时候势必也会占用大量表空间。因此在对一大表建立索引的时候首先应当考虑的是空间容量问题。其次,在对建立索引的时候要对表进行加锁,因此应当注意操作在业务空闲的时候进行。二、性能调整方面:首当其冲的考虑因素便是磁盘I/O。物理上,应当尽量把索引与数据分散到不同的磁盘上(不考虑阵列的情况)。逻辑上,数据表空间与索引表空间分开。这是在建索引时应当遵守的基本准则。其次,我们知道,在建立索引的时候要对表进行全表的扫描工作,因此,应当考虑调大初始化参数db_file_multiblock_read_count的值。一般设置为32或更大。再次,建立索引除了要进行全表扫描外同时还要对数据进行大量的排序操作,因此,应当调整排序区的大小。9i之前,可以在session级别上加大sort_area_size的大小,比如设置为100m或者更大。9i以后,如果初始化参数workarea_size_policy的值为TRUE,则排序区从pga_aggregate_target里自动分配获得。最后,建立索引的时候,可以加上nologging选项。以减少在建立索引过程中产生的大量redo,从而提高执行的速度。MySql在建立索引优化时需要注意的问题设计好MySql的索引可以让你的数据库飞起来,大大的提高数据库效率。设计MySql索引的时候有一下几点注意:1,创建索引对于查询占主要的应用来说,索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的,或者说没有添加更为有效的索引导致。如果不加索引的话,那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描,如果一张表的数据量很大而符合条件的结果又很少,那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可,比如性别可能就只有两个值,建索引不仅没什么优势,还会影响到更新速度,这被称为过度索引。2,复合索引比如有一条语句是这样的:select*from users where area=’beijing’and age=22;如果我们是在area和age上分别创建单个索引的话,由于mysql查询每次只能使用一个索引,所以虽然这样已经相对不做索引时全表扫描提高了很多效率,但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引,那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件..www.zgxue.com防采集请勿采集本网。

概述

对于中小体量的项目而言,联表查询是再常见不过的操作了,尤其是在做报表的时候。然而校对数据的时候,您发现坑了吗?本篇文章就 mysql 常用联表查询复现常见的坑。

在老版本的MySQL 3.22中,MySQL的单表限大小为4GB,当时的MySQL的存储引擎还是ISAM存储引擎。但是,当出现MyISAM存储引擎之后,也就是从MySQL 3.23开始,MySQL单表最大限制就已经扩大到了64PB了(官方文档

基础环境

一使用SELECT子句进行多表查询 SELECT 字段名 FROM 表1,表2…WHERE 表1.字段=表2.字段 AND 其它查询条件 SELECT a.id,a.name,a.address,a.date,b.math,b.english,b.chinese FROM tb_demo065_tel

建表语句

SELECT A.COLUMN FROM B JOIN SELECT SUM(NUM_QNTY4)AS sumNum,NUM_LINKID FROM RW_STORE_QUNTY GROUP BY NUM_LINKID A ON A.COLUMN=B.COLUMN 拓展资料 SQL JOIN 子句用于把来自两个或多个表的

DROP TABLE IF EXISTS `role`;CREATE TABLE `role` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_name` VARCHAR(50) DEFAULT NULL COMMENT '角色名', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='角色表';insert into `role` VALUES(1, '管理员');insert into `role` VALUES(2, '总经理');insert into `role` VALUES(3, '科长');insert into `role` VALUES(4, '组长');DROP TABLE IF EXISTS `user`;CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `role_id` int(11) NOT NULL COMMENT '角色id', `user_name` VARCHAR(50) DEFAULT NULL COMMENT '用户名', `sex` int(1) DEFAULT 0 COMMENT '性别', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';insert into `user` VALUES(1, 1, 'admin', 1);insert into `user` VALUES(2, 2, '王经理', 1);insert into `user` VALUES(3, 2, '李经理', 2);insert into `user` VALUES(4, 2, '张经理', 2);insert into `user` VALUES(5, 3, '王科长', 1);insert into `user` VALUES(6, 3, '李科长', 1);insert into `user` VALUES(7, 3, '吕科长', 2);insert into `user` VALUES(8, 3, '邢科长', 1);insert into `user` VALUES(9, 4, '范组长', 2);insert into `user` VALUES(10, 4, '赵组长', 2);insert into `user` VALUES(11, 4, '姬组长', 1);

SQL联合主键 Create Table 表名(字段名e799bee5baa6e79fa5e98193e58685e5aeb9313333663065351 Int Not Null, 字段名2 nvarchar(13)Not Null Primary Key(字段名1,字段名2), 字段名3… 字段名N…) 扩展资料

数据如下

首先,往表中 插入Name=赵可是 people 没有其他外键了啊,既然 两张表的id 是相关联的,为何不同时插入数据,取uuid,merge或者 两个SQL,干嘛还要 再查询people的id呢?疑问点:你如何 取people的id 值?

mysql> select * from role;+----+-----------+| id | role_name |+----+-----------+| 1 | 管理员 || 2 | 总经理 || 3 | 科长 || 4 | 组长 |+----+-----------+4 rows in set (0.00 sec)mysql> select * from user;+----+---------+-----------+------+| id | role_id | user_name | sex |+----+---------+-----------+------+| 1 | 1 | admin | 1 || 2 | 2 | 王经理 | 1 || 3 | 2 | 李经理 | 2 || 4 | 2 | 张经理 | 2 || 5 | 3 | 王科长 | 1 || 6 | 3 | 李科长 | 1 || 7 | 3 | 吕科长 | 2 || 8 | 3 | 邢科长 | 1 || 9 | 4 | 范组长 | 2 || 10 | 4 | 赵组长 | 2 || 11 | 4 | 姬组长 | 1 |+----+---------+-----------+------+11 rows in set (0.00 sec)

基本业务

简单信息报表: 查询用户信息

mysql> SELECT -> id, -> user_name AS '姓名', -> ( CASE WHEN sex = 1 THEN '男' WHEN sex = 2 THEN '女' ELSE '未知' END ) AS '性别' -> FROM -> USER;+----+-----------+--------+| id | 姓名 | 性别 |+----+-----------+--------+| 1 | admin | 男 || 2 | 王经理 | 男 || 3 | 李经理 | 女 || 4 | 张经理 | 女 || 5 | 王科长 | 男 || 6 | 李科长 | 男 || 7 | 吕科长 | 女 || 8 | 邢科长 | 男 || 9 | 范组长 | 女 || 10 | 赵组长 | 女 || 11 | 姬组长 | 男 |+----+-----------+--------+

查询每个角色名称及对应人员中女性数量

mysql> SELECT -> r.id, -> r.role_name AS role, -> count( u.sex ) AS sex -> FROM -> role r -> LEFT JOIN USER u ON r.id = u.role_id -> AND u.sex = 2 -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC;+----+-----------+-----+| id | role | sex |+----+-----------+-----+| 1 | 管理员 | 0 || 2 | 总经理 | 2 || 3 | 科长 | 1 || 4 | 组长 | 2 |+----+-----------+-----+4 rows in set (0.00 sec)

假如我们把性别过滤的条件改为 where 操作结果会怎么样呢?

mysql> SELECT -> r.id, -> r.role_name AS role, -> count( u.sex ) AS sex -> FROM -> role r -> LEFT JOIN USER u ON r.id = u.role_id -> WHERE -> u.sex = 2 -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC;+----+-----------+-----+| id | role | sex |+----+-----------+-----+| 2 | 总经理 | 2 || 3 | 科长 | 1 || 4 | 组长 | 2 |+----+-----------+-----+3 rows in set (0.00 sec)

这里可以看到角色数据不完整了。

找出角色为总经理的员工数量

mysql> SELECT -> r.id, -> r.role_name AS role, -> count( u.sex ) AS sex -> FROM -> role r -> LEFT JOIN USER u ON r.id = u.role_id -> WHERE -> r.role_name = '总经理' -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC;+----+-----------+-----+| id | role | sex |+----+-----------+-----+| 2 | 总经理 | 3 |+----+-----------+-----+1 row in set (0.00 sec)

同样将过滤条件由 where 改为 on

mysql> SELECT -> r.id, -> r.role_name AS role, -> count( u.sex ) AS sex -> FROM -> role r -> LEFT JOIN USER u ON r.id = u.role_id -> AND r.role_name = '总经理' -> GROUP BY -> r.role_name -> ORDER BY -> r.id ASC;+----+-----------+-----+| id | role | sex |+----+-----------+-----+| 1 | 管理员 | 0 || 2 | 总经理 | 3 || 3 | 科长 | 0 || 4 | 组长 | 0 |+----+-----------+-----+4 rows in set (0.00 sec)

这里可以看到数据多余了

总结

在 left join 语句中,左表过滤必须放 where 条件中,右表过滤必须放 on 条件中,这样结果才能不多不少,刚刚好。

到此这篇关于MySQL联表查询基本操作之left-join常见坑的文章就介绍到这了,更多相关MySQL联表查询left-join内容请搜索真格学网以前的文章或继续浏览下面的相关文章希望大家以后多多支持真格学网!

查询一个数据库中的所有表sql语句是show tables;显示所有数据库的命令是:show databases;要查看某e69da5e887aae79fa5e9819331333431356639个数据库先要进入数据库使用user<数据库名>命令;进入数据库之后才能查询数据库中有哪些表。使用以下命令即可查出所有表:show tables;扩展资料mysql数据库的基本sql操作命令介绍:1、显示当前数据库服务器中的数据库列表:mysql>SHOW DATABASES;2、建立数据库:mysql>CREATE DATABASE 库名;3、建立数据表:mysql>USE 库名;mysql>CREATE TABLE 表名(字段名 VARCHAR(20),字名 CHAR(1));4、删除数据库:mysql>DROP DATABASE 库名;5、删除数据表:mysql>DROP TABLE 表名;6、将表中记录清空:mysql>DELETE FROM 表名;7、往表中插入记录:mysql>INSERT INTO 表名 VALUES("hyq内容来自www.zgxue.com请勿采集。


  • 本文相关:
  • 超详细mysql left join,right join,inner join用法分析
  • mysql not in、left join、is null、not exists 效率问题记录
  • 深入理解mysql之left join 使用详解
  • mysql在右表数据不唯一的情况下使用left join的方法
  • mysql left join时指定null列返回特定值详解
  • mysql表left join左连接与right join右连接的实例教程
  • 解析mysql left( right ) join使用on与where筛选的差异
  • mysql多个left join连接查询用法分析
  • mysql利用profile分析慢sql详解(group left join效率高于子查询)
  • mysql的left join表连接的进阶学习教程
  • mysql优化之表结构优化的5大建议(数据类型选择讲的很好)
  • mysql union 语法代码示例分析
  • mysql中向表中某字段追加字符串的方法
  • 使用pdo防sql注入的原理分析
  • mysql关于exists的一个bug
  • 使用jmeter连接mysql图文教程
  • mysql中几种数据统计查询的基本使用教程
  • linux下安装与使用mysql详细介绍
  • mysql缓冲和缓存设置详解
  • mysql出现sql error (2013)连接错误的解决方法
  • 怎样用SQL语句查询一个数据库中的所有表
  • 如何使用sql语句查看mysql表上的索引信息
  • sql联合查询语句(两张表)
  • sql 各种连接的使用条件,
  • mysql 级联操作的属性有哪些,以及如何使用?
  • mysql多表查询sql语句怎么写
  • sql 如何把一个查询结果当作一个表来查询?
  • 如何使用SQL语句修改表内的数据
  • SQL多表联合操作的问题
  • 在access中,sql查询具有3种特定形式,包括
  • 网站首页网页制作脚本下载服务器操作系统网站运营平面设计媒体动画电脑基础硬件教程网络安全mssqlmysqlmariadboracledb2mssql2008mssql2005sqlitepostgresqlmongodbredisaccess数据库文摘数据库其它首页mysql超详细mysql left join,right join,inner join用法分析mysql not in、left join、is null、not exists 效率问题记录深入理解mysql之left join 使用详解mysql在右表数据不唯一的情况下使用left join的方法mysql left join时指定null列返回特定值详解mysql表left join左连接与right join右连接的实例教程解析mysql left( right ) join使用on与where筛选的差异mysql多个left join连接查询用法分析mysql利用profile分析慢sql详解(group left join效率高于子查询)mysql的left join表连接的进阶学习教程mysql优化之表结构优化的5大建议(数据类型选择讲的很好)mysql union 语法代码示例分析mysql中向表中某字段追加字符串的方法使用pdo防sql注入的原理分析mysql关于exists的一个bug使用jmeter连接mysql图文教程mysql中几种数据统计查询的基本使用教程linux下安装与使用mysql详细介绍mysql缓冲和缓存设置详解mysql出现sql error (2013)连接错误的解决方法mysql安装图解 mysql图文安装教程can""""t connect to mysql servwindows下mysql5.6版本安装及配置mysql字符串截取函数substring的mysql创建用户与授权方法mysql提示:the server quit withmysql——修改root密码的4种方法mysql日期数据类型、时间类型使用mysql update语句的用法详解mysql 的case when 语句使用说明innodb表select查询顺序mysql视图简介及基本操作教程jdbc连接mysql出现的问题关于mysql调用新手们常犯的11个错误总结工作中常用的mysql语句分享 不用php也可以mysql update多表联合更新的方法小结以数据库字段分组显示数据的sql语句(详细如何彻底删除mysql服务(清理注册表)详解mysql复制表结构、表数据的方法mysql event scheduler: failed to open
    免责声明 - 关于我们 - 联系我们 - 广告联系 - 友情链接 - 帮助中心 - 频道导航
    Copyright © 2017 www.zgxue.com All Rights Reserved