jdbc学习过程问题记录
2015-08-18 11:25 himanxu 阅读(157) 评论(0) 编辑 收藏 举报I、String sql = "insert into contacts (name,phone,email,refer_id) "
+"values('xu','020-87662341','yahoo@yahoo.com.cn',"
+"select id from contacts where name=himan)";
DML语句中有子查询语句,编译能通过,但运行时提示有语法错误
1、子查询语句要用括号括起来
2、himan表示字符串要用单引号
更正基本语法之后依然提示有错误,“You cannot specify target table for update in FROM clause”,网上百度了下,原因是MySQL
中不能先select出同一表中的某些值,再update这个表(在同一语句中)。需要先把select出来的值建表,然后在select这个表中的值,并update原表
也即:
Connection conn = DriverManager.getConnection();
Statement stmt = conn.createStatement();
boolean autoCommit = conn.getAutoCommit();
//开启事务
conn.setAutoCommit(false);
sql1 = "create table temp as (select * from contacts)";
stmt.addBatch(sql1);
sql2 = "insert into contacts (name,phone,email,refer_id) "
+"values('xu','020-87662341','yahoo@yahoo.com.cn',"
+"(select id from temp where name='himan'))";
stmt.addBatch(sql2);
sql3 = "delete table temp";
stmt.addBatch(sql3);
conn.commit();
conn.setAutoCommit(autoCommit);
II、 mysql.exe与mysqld.exe的区别:前者是命令行对话框程序,后者是MySQL的服务程序,要使用MySQL就必须启动mysqld.exe程序