MySQL学习纠错笔记

workbench数据库导入     

方法一                                                                                              

1. File ——> Open SQL Script…

2. 选中路径导入SQL文件

3. 添加指定库名的命令 ，并点击运行

方法二

1. Server ——> Data Import

2. 选择导入文件的路径

方法三

1. mysql -u root -p

2. create database Demo

3. use Demo

4. source D:/Demo.sql;

---

报错

Error Code: 1064

原因：未使用FROM指定目标表格

使用代码:

SELECT customer_id AS cst_id

报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'customer_id AS cst_id' at line 1

Error Code: 1054

原因：别名后添加；符号

使用代码:

SELECT customer_id AS cst_id ；

报错:Unknown column 'customer_id' in 'field list'

Error Code: 1096

原因：未使用FROM指定目标表格

使用代码:

SELECT *

报错:No tables used

Error Code: 1146

原因：customers后加了；

使用代码:

SELECT LEAST(first_name,last_name),LEAST(LENGTH(last_name),LENGTH(last_name)) FROM customers；

报错：Table 'sql_store.customers；' doesn't exist

Error Code: 1064

原因：单引号打错了

使用代码:

SELECT last_name,first_name,city
FROM customers
#WHERE city='Waltham' OR city='Orlando' OR city='Nashville'
WHERE city IN ('Waltham'，'Orlando'，'Nashville')

报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '，'Orlando'，'Nashville')' at line 4

Error Code: 1052

原因：数据表无关联

使用代码:

SELECT customer_id,order_id
FROM customers,orders

报错:Column 'customer_id' in field list is ambiguous

改正：

SELECT customer_id,product_id
FROM customers,products
WHERE customers.`customer_id` = products.`product_id`

Error Code: 1146

原因：

使用代码:

SELECT E.'office_id',O.'address',E.'salary',SUM(E.'salary'),AVG(E.'salary')
FROM employees AS E JOIN offices AS O
ON E.'office_id' = O.'office_id'
GROUP BY E.'office_id'
ORDER BY AVG(E.'salary') DESC

报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''office_id',O.'address',E.'salary',SUM(E.'salary'),AVG(E.'salary') FROM employee' at line 1

Error Code: 1264

原因：超出整型数据使用范围

使用代码:

INSERT INTO test_int1(f1)
VALUES(128);

报错:Out of range value for column 'f1' at row 1

Error Code: 1064

原因：最后一个字段后面加了逗号

使用代码:

CREATE TABLE test_bit1(
F1 BIT,
F2 BIT(5),
F3 BIT(64),
) ;

报错：You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5

改正：

CREATE TABLE test_bit1(
F1 BIT,
F2 BIT(5),
F3 BIT(64)
) ;

Error Code: 3140

原因：

使用代码:

 CREATE TABLE test_json(
    js JSON
    );
    INSERT INTO test_json (js)
    VALUES('("name":"tom","age":18,"address":("province":"beijing","city","beijing"))');      

报错：Invalid JSON text: "Invalid value." at position 0 in value for column 'test_json.js'.

改正：

 

 

Error Code: 1175

原因：

使用代码:

    UPDATE test1
    SET email = NULL
    WHERE id = 1 ;

报错：You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.  To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.

---

单引号

Error Code: 1054

原因：字段没加单引号

使用代码：

SELECT *

FROM customers

WHERE city = Chicago

报错：Error Code: 1054. Unknown column 'Chicago' in 'where clause'

字段被识别为常数

原因：字段加了单引号

使用代码：

SELECT last_name , 'points' , 'points'*12 AS “预计积分” 

FROM customers  

总结：单引号——引用字符串常量时使用，非字符串常量会被判定为常数。

---

WHERE

原因：在select里使用>1000过滤points

使用代码：

SELECT last_name , points > 1000

FROM customers

解决方法：使用WHERE

SELECT last_name , points

FROM customers

WHERE points > 1000

---

增改删

原因：着重号使用错误

使用代码：

SELECT *
FROM emp;
INSERT INTO emp 
VALUES (7,`赵灵儿`,22,4)(8,`李逍遥`,22,4);

解决方法：

SELECT *
FROM emp;
INSERT INTO emp 
VALUES (7,'赵灵儿',22,4)(8,'李逍遥',22,4);

总结：着重号——字段名或表名的命名与其他保留字、关键字、数据库系统或常用方法发生冲突时使用。