SQL联合多表更新和删除

在 MySQL 3.23 中,你可以使用 LIMIT # 来确保只有给定的记录行数目被更改。
如果一个 ORDER BY 子句被使用(从 MySQL 4.0.0 开始支持),记录行将以指定的次序被更新。这实际上只有连同 LIMIT 一起才有用。

从 MySQL 4.0.4 开始,你也可以执行一个包含多个表的 UPDATE or DELETE 的操作:

UPDATE t1,t2SET t1.price = t2.price
WHERE t1.id = t2.id;

delete FROM `t1` using `t1`
left join `t2` on t1.id= t2.id
where 1 and t2.id is null;

注意:多表 UPDATE 不可以使用 ORDER BY 或 LIMIT。 
第一个多表删除格式从 MySQL 4.0.0 开始被支持。第二个多表删除格式从 MySQL 4.0.2 开始被支持。

仅仅在 FROM 或 USING 子句 之前 列出的表中的匹配记录行被删除。效果就是,你要以从多个表中同时删除记录行,并且同样可以有其它的表用于检索。

在表名后的 .* 仅仅是为了兼容 Access:

DELETE t1,t2 FROM t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

or

DELETE FROM t1,t2 USING t1,t2,t3 WHERE t1.id=t2.id AND t2.id=t3.id

在上面的情况下,我们仅仅从 t1 和 t2 表中删除匹配的记录行。

如果一个 ORDER BY 子句被使用(从 MySQL 4.0.0 开始支持), 记录行将以指定的次序删除。这实际上只有连同 LIMIT 一起才有用。示例如下:

DELETE FROM t1
WHERE name = ‘jcole’
ORDER BY id DESC
LIMIT 1

这将删除匹配 WHERE 子句的,并且最早被插入(通过 id 来确定)的记录行。

DELETE 语句的LIMIT rows 选项是 MySQL 特有的,它告诉服务器在控制权被返回到客户端之前可被删除的最大记录行数目。这可以用来确保一个特定的 DELETE 命令不会占用太长的时间。你可以简单地重复使用 DELETE 命令,直到被影响的记录行数目小于 LIMIT 值。

从 MySQL 4.0 开始,在 DELETE 语句中可以指定多个表,用以从一个表中删除依赖于多表中的特殊情况的记录行。然而,在一个多表删除中,不能使用 ORDER BY 或 LIMIT。
假设有两个表,tab1,tab2,分别有产品名称和产品价格,现在我想用tab2的价格替换tab1的价格,我写的语句是Update tab1 set tab1.产品价格=tab2.产品价格 where tab1.产品名称=tab2.产品名称
结果出现错误,提示为:列前缀 ‘tab2′ 与查询中所用的表名或别名不匹配。请问应该怎样写,

SQL语句是不支持多表同时更新的。

应该这样写 

update tab1 set tab1.产品价格 = (select tab2.产品价格 from tab2 where tab2.产品名称 = tab1.产品名称) where tabl1.产品名称 in (select tab2.产品名称 from tab2)
后面的where tab1.产品名称 in (select tab2.产品名称 from tab2) 这句保证了如果tab1的产品在tab2没有记录时不会出错。
在 开发中,数据库来回换,而有些关键性的语法又各不相同,这是一件让开发人员很头痛的事情.本文总结了Update语句更新多表时在SQL Server,Oracle,MySQL三种数据库中的用法.我也试了SQLite数据库,都没成功,不知是不支持多表更新还是咋的.

在本例中: 

我们要用表gdqlpj中的gqdltks,bztks字段数据去更新landleveldata中的同字段名的数据,条件是当landleveldata 中的GEO_Code字段值与gdqlpj中的lxqdm字段值相等时进行更新.

SQL Server语法:
UPDATE
{
table_name WITH ( < table_hint_limited > [ …n ] )
| view_name
| rowset_function_limited
}
SET
{ column_name = { expression | DEFAULT | NULL }
| @variable = expression
| @variable = column = expression } [ ,…n ]

{ { [ FROM { < table_source > } [ ,…n ] ]

[ WHERE
< search_condition > ] }
|
[ WHERE CURRENT OF
{ { [ GLOBAL ] cursor_name } | cursor_variable_name }
] }
[ OPTION ( < query_hint > [ ,…n ] ) ]

SQL Server示例:
update a
set a.gqdltks=b.gqdltks,a.bztks=b.bztks
from landleveldata a,gdqlpj b
where a.GEO_Code=b.lxqdm

Oracle语法:
UPDATE    updatedtable
SET (col_name1[,col_name2…])=
(SELECT    col_name1,[,col_name2…]
FROM    srctable [WHERE where_definition])

Oracel 示例:
update landleveldata a
set (a.gqdltks, a.bztks)=
(select b.gqdltks, b.bztks    from gdqlpj b
where a.GEO_Code=b.lxqdm)

MySQL语法:
UPDATE table_references
SET col_name1=expr1 [, col_name2=expr2 …]
[WHERE where_definition]

MySQL 示例:
update landleveldata a, gdqlpj b
set a.gqdltks= b.gqdltks,
a.bztks= b.bztks
where a.GEO_Code=b.lxqdm

registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister

解决方法,新建类并且继承org.apache.commons.dbcp.BasicDataSource类

接着重新close的方法即可。

/**
 * registered the JDBC driver [com.mysql.jdbc.Driver] but failed to unregister
 * Created by alan.luo on 2017/9/5.
 */

public class BasicDataSourceEx extends BasicDataSource {

    public BasicDataSourceEx(){
        super();
    }


    @Override
    public synchronized void close() throws SQLException {
        DriverManager.deregisterDriver(DriverManager.getDriver(url));
        super.close();
    }
}

 

xml把bean对应的class改成新建的类名就可以了。

<bean id="dataSource" class="com.lanxinbase.system.basic.BasicDataSourceEx" destroy-method="close">
    <property name="driverClassName" value="${db.driver}"/>
    <property name="url" value="${db.url}"></property>
    <property name="username" value="${db.username}"></property>
    <property name="password" value="${db.userpasswd}"></property>
    <property name="initialSize" value="${db.initalsize}"></property>
    <property name="maxActive" value="${db.maxActive}"></property>
    <property name="maxIdle" value="${db.maxIdle}"></property>
    <property name="maxOpenPreparedStatements" value="${db.maxOpens}"></property>
    <property name="maxWait" value="${db.maxWait}"></property>
</bean>

 

MySQL性能优化

max_connections=512:最大连接数,解释哈。
query_cache_type
含义:查询缓冲类型。

影响:为1是使用缓冲,2是除非使用SQL_CACHE才进行缓冲。对于缓冲而言,数据并不是实时的,有一定的延时。但是对于实时性要求不高的查询短时间内多次执行,是不划算的,这个时候就需要缓存。并且缓存中是区分空格和大小写的,如果大小写不一致和空格不一致,也会认为是不同的SQL,不会利用到缓存。虽然不设置查询缓冲,有时可能带来性能上的损失,但有一些SQL语句需要实时地查询数据,或并不经常使用(可能一天就执行一两次),这样就需要把缓冲关了,可以采用临时关闭的方法SELECT SQL_NO_CACHE。

建议:一般设置为1。

query_cache_size=32M
含义:指定MySQL查询结果缓冲区的大小。

影响:如果应用程序有大量读,而且没有应用程序级别的缓存,那么这很有用。不过不要设置太大,因为维护它也需要不少开销,这会导致MySQL变慢。

建议:通常设置为 32-512Mb。设置完之后最好是跟踪一段时间,查看是否运行良好。在一定的负载压力下,如果缓存命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进行调整太低了,就启用它,如果命中率已经不错了,就可以把他调小一点。对于2G的内存,先从16M开始,一倍一倍的增加,直到命中率比较稳定为止。设置后可以使用show variables like ‘%query_cache%';进行查询.

table_cache=256
含义:table高速缓存的数量

影响:当某一连接访问一个表时,MySQL会检查当前已缓存表的数量。如果该表已经在缓存中打开,则会直接访问缓存中的表已加快查询速度;如果该表未被缓存,则会将当前的表添加进缓存并进行查询。

MySQL主从复制原理以及架构

1 复制概述

Mysql内建的复制功能是构建大型,高性能应用程序的基础。将Mysql的数据分布到多个系统上去,这种分布的机制,是通过将Mysql的某一台主机的 数据复制到其它主机(slaves)上,并重新执行一遍来实现的。复制过程中一个服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更 新写入二进制日志文件,并维护文件的一个索引以跟踪日志循环。这些日志可以记录发送到从服务器的更新。当一个从服务器连接主服务器时,它通知主服务器从服 务器在日志中读取的最后一次成功更新的位置。从服务器接收从那时起发生的任何更新,然后封锁并等待主服务器通知新的更新。

请注意当你进行复制时,所有对复制中的表的更新必须在主服务器上进行。否则,你必须要小心,以避免用户对主服务器上的表进行的更新与对从服务器上的表所进行的更新之间的冲突。

1.1 mysql支持的复制类型:

(1):基于语句的复制: 在主服务器上执行的SQL语句,在从服务器上执行同样的语句。MySQL默认采用基于语句的复制,效率比较高。
一旦发现没法精确复制时, 会自动选着基于行的复制。
(2):基于行的复制:把改变的内容复制过去,而不是把命令在从服务器上执行一遍. 从mysql5.0开始支持
(3):混合类型的复制: 默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。

mysql主从复制详解

步骤如下:
1、主从服务器分别作以下操作:
1.1、版本一致
1.2、初始化表,并在后台启动mysql
1.3、修改root的密码

2、修改主服务器master:
#vi /etc/my.cnf
[mysqld]
log-bin=mysql-bin //[必须]启用二进制日志
server-id=222 //[必须]服务器唯一ID,默认是1,一般取IP最后一段

1237

关于深蓝

仅仅是一个对编程很有爱好的人.
Copyright © 2008-2016 lanxinbase.com Rights Reserved. | 粤ICP备14086738号 |