mysql错误及解决方案

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'

错误原因，表中主键重复