图片 2

Oracle用一个表的列更新另一个表对应记录的列,mysql各种约束

mysql各种约束

constraint 外键别名 foreign key(a,b) references 外部表名(a,b);数据类型要保持一致
not null
unique
primary key(主键名)
auto_increment一个表只能有一个字段使用此约束且该字段必须为主键的一部分,约束的字段可以是任何整数类型。
zerofill默认自动添加无符号
unsigned
default

constraint 外键别名 foreign
key(a,b) references 外部表名(a,b);数据类型要保持一致not
nulluniqueprimary key(主键名)auto_increment一个表只能有…

Oracle用一个表的列更新另一个表对应记录的列

  1. 只支持相等JOIN。

  2. 多表连接当使用不同的列进行JOIN时,会产生多个MR作业。

  3. 最后的表的数据是从流中读取,而前面的会在内存中缓存,因此最好把最大的表放在最后。

SELECT /*+ STREAMTABLE(a)
*/ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON
(c.key = b.key1)//暗示

 

  1. JOIN在WHERE子句前进行处理。

这几天搞一个小程序,需要将一个表中几个列的内如批量复制到另一个表相应字段中。执行下面语句: 

SELECT a.val, b.val FROM a
LEFT OUTER JOIN b ON (a.key=b.key)

update ZWSZ Z set
Z.WJLB = 
(select G.WJLB from RMWJGS G WHERE G.DWCODE=Z.DWDM AND G.ZWCODE=Z.ZWMC
)

  WHERE
a.ds=’2009-07-07′ AND b.ds=’2009-07-07′

  可总是不成功,看了看提示:“单行子查询返回多于一个行”。原来是两个表的记录并不是一对一的关系,一个表中的记录数大于另一个表,即不关联的记录,所以才会失败图片 1

SELECT a.val, b.val FROM a
LEFT OUTER JOIN b

  按照提示,查询条件中加上“ rownum < 2
”,成功图片 2

ON (a.key=b.key AND
b.ds=’2009-07-07′ AND a.ds=’2009-07-07′)//先过滤

——————————————————————————————————————————

  1. JOIN是左结合的:

随便贴一张网友的妙文:

SELECT a.val1, a.val2,
b.val, c.val

  今天有这样一个需求:因为要加快查询速度,避免表的联合查询的效率问题,所以要把一个表的几个字段填到另一个表的几个字段上去。 
一开始做法如下: 
update tbl1 a, tbl2
b    set a.col1=b.col1,a.col2=b.col2   where a.key=b.key
   
  这个语句在 MySQL 上是可以运行的,但在 ORACLE
上无法执行。在网上查了一个发现可以这样: 
update tbl1 a
    set a.col1=(select b.col1 from tbl2 b where a.key=b.key),
        a.col2=(select b.col2 from tbl2 b where a.key=b.key)
  但我感觉这样的效率好像比较低,写得SQL又很长,更新一行数据要写两个嵌套,直观上感觉
“select xxx from tbl2 b where a.key=b.key”
语句好像被执行了两遍(实际我没有测试验证)。于是接着在网上搜,查到一个比较好看的写法,如下:

FROM a JOIN b ON (a.key =
b.key) LEFT OUTER JOIN c ON (a.key = c.key)

update tbl1 a
   set (a.col1, a.col2) = (select b.col1, b.col2
                              from tbl2 b
                              where a.key = b.key)

—> FROM c LEFT OUTER
JOIN a ON (c.key = a.key) LEFT OUTER JOIN b ON (c.key =
b.key).

这种写法感觉还比较满意。但注意了,经过测试,这种语法在
MySQL 中是不合法的。

 

今天继续使用最后这个SQL的时候,也发现了一些缺点:如果 tbl1.key 的值在
tbl2.key 中没有此值是,这个更新的两个字段 tbl1.col1 和 tbl1.col2
字段会被更新为空值(null)。这个结果是我不想看到的。也是没办法解决的。如果想解决的话,也可以在最后加入一个条件,如下表中加大字体的部分:

  1. 只有一个大表时,可以使用map端连接:

update tbl1 a
   set (a.col1, a.col2) = (select b.col1, b.col2
                              from tbl2 b
                              where a.key = b.key)
   where a.key in(select key from tbl2)

SELECT /*+ MAPJOIN(b) */
a.key, a.value

但这种做法肯定会导致性能严重下降。所以,我写一个更复杂的方法来解决这个问题,现在没时间。。。

  FROM a join b on a.key
= b.key

  1.将小表的数据变成hashtable广播到所有的map端,将大表的数据进行合理的切分

  2. map阶段用大表的数据一行一行的去探测小表的hashtable

  hive.auto.convert.join=true:自动判断大表和小表

  hive.smalltable.filesize=25M:当超过时使用reduce端连接

  hive.join.emit.interval=1000

  //
hive.mapjoin.size.key=10000

  hive.mapjoin.cache.numrows=10000

  1. BUCKET MAP JOIN:在JOIN列上分桶,桶的数目成倍数关系,设置hive.optimize.bucketmapjoin=true

SELECT /*+ MAPJOIN(b) */
a.key, a.value

  FROM a join b on a.key =
b.key

Bucket
Map Join 执行计划分两步,第一步先将小表做map
操作变成hashtable 然后广播到所有大表的map端,大表的map端接受了number_buckets 个小表的hashtable并不需要合成一个大的hashtable,直接可以进行map
操作,map 操作会产生number_buckets 个split,每个split
的标记跟小表的hashtable 标记是一样的, 在执行projection 操作的时候,只需要将小表的一个hashtable 放入内存即可,然后将大表的对应的split
拿出来进行判断,所以其内存限制为小表中最大的那个hashtable 的大小.

  1. SORT-MERGE JOIN:在JOIN列上排序并分桶,且有相同的桶数目

set
hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

set
hive.optimize.bucketmapjoin = true;

set
hive.optimize.bucketmapjoin.sortedmerge = true;

  1. SKEW JOIN:处理数据倾斜

hive.exec.reducers.bytes.per.reducer:默认处理1G数据

hive.optimize.skewjoin=false

hive.skewjoin.key=100000
:(处理的总记录数/reduce个数)的2-4倍

内连接:SELECT sales.*,things.*
FROM sales JOIN things ON (sales.id=things.id)

SELECT sales.*,things.*
FROM sales LEFT/RIGHT/FULL OUTER JOIN things ON (sales.id=things.id)

半连接: 右手边的表只能出现在JOIN条件里,不能出现在SELECT和WHERE里。

SELECT * FROM things LEFT
SEMI JOIN sales ON (sales.id=things.id)

=SELECT * FROM things
WHERE things.id in (SELECT id FROM sales)