ERROR 1064 (42000) (교과서 코드에서도)

ERROR 1064 (42000) (교과서 코드에서도)

데이터베이스 관리를 배우려고 합니다.다음은 교과서 예제를 바탕으로 작성한 코드입니다.

    drop database `Pine_Valley_Furniture_Company`;

CREATE DATABASE `Pine_Valley_Furniture_Company` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

USE `Pine_Valley_Furniture_Company`;

CREATE TABLE IF NOT EXISTS `CUSTOMER` (
  `ID` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT = 100,       -- I made the decision to start the auto-increment from 100 to reserve ids below 100 for special customers (company subsidiaries, government clients) or special placeholder (error) values 
  `Name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,      -- Can't have a nameless customer, so 'NOT NULL'
  `Address` varchar(30) COLLATE utf8_unicode_ci,            -- Address fields are not required, since a customer might do a pick up at the store, so I didn't use 'NOT NULL' for these fields
  `City` varchar(20) COLLATE utf8_unicode_ci,
  `State_` char(2) COLLATE utf8_unicode_ci,                 -- 'state' is a reserved keyword, so I used 'state_' for the column name instead
  `Postalcode` varchar(10) COLLATE utf8_unicode_ci
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `CUSTOMER` (`ID`, `Name`, `Address`, `City`, `State_`, `Postalcode`) VALUES(101, 'Mike Smith', '11 Maple drive', 'Nashua', 'NH', '03060');
INSERT INTO `CUSTOMER` (`ID`, `Name`, `Address`, `City`, `State_`, `Postalcode`) VALUES(102, 'Boston College', '505 Amherst St', 'Boston', 'MA', '03063');
INSERT INTO `CUSTOMER` (`ID`, `Name`, `Address`, `City`, `State_`, `Postalcode`) VALUES(103, 'Annie Chang', '75 Circle ln', 'Annieborough', 'MA', '03555');
INSERT INTO `CUSTOMER` (`ID`, `Name`, `Address`, `City`, `State_`, `Postalcode`) VALUES(104, 'Sam Bond', '1 First drive', 'Pleasantville', 'NH', '03555');
INSERT INTO `CUSTOMER` (`ID`, `Name`, `Address`, `City`, `State_`, `Postalcode`) VALUES(105, 'Mr. Cookie Monster', '606 Market St', 'Celebration', 'FL', '34747');




CREATE TABLE IF NOT EXISTS `ORDER_LINE` (
  `OrderID` int NOT NULL AUTO_INCREMENT=100 PRIMARY KEY,        -- I made the decision to start the auto-increment from 100 to reserve ids below 100 for special orders or special placeholder (error) values 
  `ProductID` int NOT NULL,
  `Orderedquantity` int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `ORDER_LINE` (`OrderID`, `ProductID`, `Orderedquantity`) VALUES(100, 10015, 3);
INSERT INTO `ORDER_LINE` (`OrderID`, `ProductID`, `Orderedquantity`) VALUES(101, 20315, 1);
INSERT INTO `ORDER_LINE` (`OrderID`, `ProductID`, `Orderedquantity`) VALUES(102, 20010, 1);
INSERT INTO `ORDER_LINE` (`OrderID`, `ProductID`, `Orderedquantity`) VALUES(103, 10010, 1);
INSERT INTO `ORDER_LINE` (`OrderID`, `ProductID`, `Orderedquantity`) VALUES(104, 18013, 2);




CREATE TABLE IF NOT EXISTS `ORDER_` (                           -- Named it 'ORDER_' instead of 'ORDER', since ORDER is a reserved keyword
  `OrderID` int NOT NULL AUTO_INCREMENT=100 PRIMARY KEY,        -- I made the decision to start the auto-increment from 100 to reserve ids below 100 for special orders or special placeholder (error) values 
  `CustomerID` int NOT NULL,
  `OrderDate` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `ORDER_` (`OrderID`, `CustomerID`, `OrderDate`) VALUES(101, 101, '2018-09-03T14:25:10.487');
INSERT INTO `ORDER_` (`OrderID`, `CustomerID`, `OrderDate`) VALUES(102, 102, '2018-09-03T15:20:22.988'); -- the first two customers to register also placed the 1st two orders, hence order id and customer id for the 1st two twoples are equal (customer id 101 and order id is 101)
INSERT INTO `ORDER_` (`OrderID`, `CustomerID`, `OrderDate`) VALUES(103, 101, '2018-09-03T16:45:11.883'); -- customer 101 placed another order
INSERT INTO `ORDER_` (`OrderID`, `CustomerID`, `OrderDate`) VALUES(104, 103, '2018-09-03T18:01:19.001');
INSERT INTO `ORDER_` (`OrderID`, `CustomerID`, `OrderDate`) VALUES(105, 104, '2018-09-03T18:26:55.089');




CREATE TABLE IF NOT EXISTS `PRODUCT` (
  `ProductID` int NOT NULL AUTO_INCREMENT=10000 PRIMARY KEY,        -- I made the decision to start the auto-increment from 10000 to reserve ids below 10000 for special products or special placeholder (error) values 
  `Description` varchar(50) COLLATE utf8_unicode_ci,
  `Finish` varchar(20) COLLATE utf8_unicode_ci,
  `StandardPrice` numeric(6,2)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `PRODUCT` (`ProductID`, `Description`, `Finish`, `StandardPrice`) VALUES(10000, 'Small Dining Table', 'Oak', 799.97);
INSERT INTO `PRODUCT` (`ProductID`, `Description`, `Finish`, `StandardPrice`) VALUES(10001, 'Medium Dining Table', 'Oak', 899.97);
INSERT INTO `PRODUCT` (`ProductID`, `Description`, `Finish`, `StandardPrice`) VALUES(10002, 'Large Dining Table', 'Oak', 999.97);
INSERT INTO `PRODUCT` (`ProductID`, `Description`, `Finish`, `StandardPrice`) VALUES(10003, 'Royal Dining Table', 'Oak', 1099.97);
INSERT INTO `PRODUCT` (`ProductID`, `Description`, `Finish`, `StandardPrice`) VALUES(10004, 'Small Dining Table', 'Maple', 799.97);



desc table CUSTOMER;
desc table ORDER_LINE;
desc table ORDER_;
desc table PRODUCT;

drop table CUSTOMER;
drop table ORDER_LINE;
drop table ORDER_;
drop table PRODUCT;

교과서의 예는 다음과 같습니다.

DROP DATABSE `shirts4mike`;
CREATE DATABASE `shirts4mike` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

USE `shirts4mike`;

CREATE TABLE IF NOT EXISTS `products` (
  `sku` int(11) DEFAULT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `img` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  `paypal` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(101, 'Logo Shirt, Red', 'img/shirts/shirt-101.jpg', 18.00, '9P7DLECFD4LKE');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(102, 'Mike the Frog Shirt, Black', 'img/shirts/shirt-102.jpg', 20.00, 'SXKPTHN2EES3J');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(103, 'Mike the Frog Shirt, Blue', 'img/shirts/shirt-103.jpg', 20.00, '7T8LK5WXT5Q9J');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(104, 'Logo Shirt, Green', 'img/shirts/shirt-104.jpg', 18.00, 'YKVL5F87E8PCS');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(105, 'Mike the Frog Shirt, Yellow', 'img/shirts/shirt-105.jpg', 25.00, '4CLP2SCVYM288');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(106, 'Logo Shirt, Gray', 'img/shirts/shirt-106.jpg', 20.00, 'TNAZ2RGYYJ396');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(107, 'Logo Shirt, Teal', 'img/shirts/shirt-107.jpg', 20.00, 'S5FMPJN6Y2C32');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(108, 'Mike the Frog Shirt, Orange', 'img/shirts/shirt-108.jpg', 25.00, 'JMFK7P7VEHS44');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(109, 'Get Coding Shirt, Gray', 'img/shirts/shirt-109.jpg', 20.00, 'B5DAJHWHDA4RC');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(110, 'HTML5 Shirt, Orange', 'img/shirts/shirt-110.jpg', 22.00, '6T2LVA8EDZR8L');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(111, 'CSS3 Shirt, Gray', 'img/shirts/shirt-111.jpg', 22.00, 'MA2WQGE2KCWDS');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(112, 'HTML5 Shirt, Blue', 'img/shirts/shirt-112.jpg', 22.00, 'FWR955VF5PALA');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(113, 'CSS3 Shirt, Black', 'img/shirts/shirt-113.jpg', 22.00, '4ELH2M2FW7272');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(114, 'PHP Shirt, Yellow', 'img/shirts/shirt-114.jpg', 24.00, 'AT3XQ3ZVP2DZG');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(115, 'PHP Shirt, Purple', 'img/shirts/shirt-115.jpg', 24.00, 'LYESEKV9JWE3A');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(116, 'PHP Shirt, Green', 'img/shirts/shirt-116.jpg', 24.00, 'KT7MRRJUXZR34');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(117, 'Get Coding Shirt, Red', 'img/shirts/shirt-117.jpg', 20.00, '5UXJG8PXRXFKE');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(118, 'Mike the Frog Shirt, Purple', 'img/shirts/shirt-118.jpg', 25.00, 'KHP8PYPDZZFTA');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(119, 'CSS3 Shirt, Purple', 'img/shirts/shirt-119.jpg', 22.00, 'BFJRFE24L93NW');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(120, 'HTML5 Shirt, Red', 'img/shirts/shirt-120.jpg', 22.00, 'RUVJSBR9FXXWQ');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(121, 'Get Coding Shirt, Blue', 'img/shirts/shirt-121.jpg', 20.00, 'PGN6ULGFZTXL4');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(122, 'PHP Shirt, Gray', 'img/shirts/shirt-122.jpg', 24.00, 'PYR4QH97W2TSJ');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(123, 'Mike the Frog Shirt, Green', 'img/shirts/shirt-123.jpg', 25.00, 'STDAUJJTSPT54');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(124, 'Logo Shirt, Yellow', 'img/shirts/shirt-124.jpg', 20.00, '2R2U74KWU5RXG');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(125, 'CSS3 Shirt, Blue', 'img/shirts/shirt-125.jpg', 22.00, 'GJG7F8EW3XFAS');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(126, 'Doctype Shirt, Green', 'img/shirts/shirt-126.jpg', 25.00, 'QW2LFRYGU7L4Q');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(127, 'Logo Shirt, Purple', 'img/shirts/shirt-127.jpg', 20.00, 'GFV6QVRMJU7F8');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(128, 'Doctype Shirt, Purple', 'img/shirts/shirt-128.jpg', 25.00, 'BARQMHMB565PN');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(129, 'Get Coding Shirt, Green', 'img/shirts/shirt-129.jpg', 20.00, 'DH9GXABU3P8GS');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(130, 'HTML5 Shirt, Teal', 'img/shirts/shirt-130.jpg', 22.00, '4LZ3EUVCBENE4');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(131, 'Logo Shirt, Orange', 'img/shirts/shirt-131.jpg', 20.00, '7BNDYJBKWD364');
INSERT INTO `products` (`sku`, `name`, `img`, `price`, `paypal`) VALUES(132, 'Mike the Frog Shirt, Red', 'img/shirts/shirt-132.jpg', 25.00, 'Y6EQRE445MYYW');

이러한 스크립트 중 하나를 명령줄 mysql.exe --user=root --password -s < "E:\Database Design and Management\Lab 2\shirts4dl-1.sql"을 통해 실행하려고 하면 스크립트와 교과서 예제 모두에서 오류가 많이 발생합니다.

 ERROR 1064 (42000) at line 7: You have an error in your SQL syntax;
 check the manual that corresponds to your MariaDB server version for
 the right syntax to use near '= 100,   `Name` varchar(25) COLLATE
 utf8_unicode_ci NOT NULL,   `Address` v' at line 2

모든 곳에서 자동 증분을 제거해도 여전히 짖는다.

ERROR 1064 (42000) at line 7: You have an error in your SQL syntax;
check the manual that corresponds to your MariaDB server version for
the right syntax to use near '= 100,   `Name` varchar(25) COLLATE
utf8_unicode_ci NOT NULL,   `Address` v' at line 2

도와주세요!내가 무엇을 잘못하고 있는가(교과서 코드에도 오류가 있는 것을 고려하면 내가 잘못하고 있는가?저 혼자예요, 강사 지도도 없어요.매우 혼란스러워요.

`ID` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT = 100, 

이것은 MySQL이 인식하는 구문이 아닙니다.당신의 교과서에서 초기 AI 값을 그곳에서 선언할 수 있다고 했습니까?

100부터 자동 인크리먼트를 개시하는 경우는, 다음의 순서에 따릅니다.

CREATE TABLE IF NOT EXISTS `CUSTOMER` (
    `ID` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    ... other columns ...
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

테이블을 작성한 후 언제든지 테이블의 AI 값을 변경할 수도 있습니다.

ALTER TABLE `CUSTOMER` AUTO_INCREMENT=200;

테이블에 데이터 행이 있는 경우 AI 값을 이렇게 변경해도 테이블에 존재하는 가장 큰 ID 값보다 낮아지지 않습니다.

언급URL : https://stackoverflow.com/questions/52284942/error-1064-42000-even-in-the-textbook-code