1、[Err] 1093 - You can't specify target table 'sc' for update in FROM clause
查询语句如下:
UPDATE sc
SET score =(
SELECT
avg(sc_temp.score)
FROM
sc
WHERE
course_id =(
SELECT
id
FROM
teacher
WHERE
NAME = 'math_teacher'
)
)
WHERE
course_id =(
SELECT
id
FROM
teacher
WHERE
NAME = 'math_teacher'
)
解决方案
UPDATE sc
SET score =(
SELECT
avg(sc_temp.score)
FROM
(SELECT * FROM sc)sc_temp
WHERE
course_id =(
SELECT
id
FROM
teacher
WHERE
NAME = 'math_teacher'
)
)
WHERE
course_id =(
SELECT
id
FROM
teacher
WHERE
NAME = 'math_teacher'
)
2、Mysql不支持top
SELECT
*
FROM
sc
LIMIT 2,
3
3、
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
查询语句如下:
SELECT
*
FROM
sc
WHERE
student_id IN(SELECT id FROM student LIMIT 10)
解决方案:
SELECT
*
FROM
sc
WHERE
student_id IN(
SELECT
t.id
FROM
(SELECT * FROM student LIMIT 10)AS t
4、1286 - unknown table engine 'innodb'
网上的解决方案有
将innodb_data_home_dir对应目录下的ib_logfile* mv到/tmp或其他目录中
修改my.cnf里面的配置
添加innodb
尝试上面的几种方式都未成功,后来进入mysql 运行show plugins;出现
Can't create/write to file '/tmp/#sql_***.MYI'(errno13)
然后修改chown mysql:mysql /tmp
在my.cnf中添加
tmpdir=/tmp
重启mysql查询正常
5、com.mysql.jdbc.exception.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '' for key 'PRIMARY'
错误原因,表中主键重复