MySQL学习纠错笔记

cstttsc

已于 2024-02-22 16:18:06 修改

阅读量873

点赞数 26

文章标签： mysql 学习笔记

于 2024-02-16 14:10:43 首次发布

本文链接：https://blog.csdn.net/cstttsc/article/details/135938945

版权

workbench数据库导入

方法一

File ——> Open SQL Script…
选中路径导入SQL文件
添加指定库名的命令，并点击运行

方法二

Server ——> Data Import
选择导入文件的路径

方法三

mysql -u root -p
create database Demo
use Demo
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: 1366

原因：INSERT INTO内，字段顺序写反了

使用代码:

CREATE TABLE test_primary(
id INT PRIMARY KEY,
last_name VARCHAR(15),
salary DECIMAL(10,2),
email VARCHAR(25)

#CONSTRAINT PK_TEST_ID PRIMARY KEY(id)
);

INSERT INTO test_primary(id,last_name,email,salary)
VALUES(2,'TOM',4600,'123@gmail.com');

报错：Incorrect decimal value: '123@gmail.com' for column 'salary' at row 1

改正：