错误1064(42000)，即使在教科书代码中也是如此

vwoqyblh  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(279)

我在学数据库管理。这是我根据教科书示例创建的代码：

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\shirts4mike-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

如果我把auto-increment从所有地方去掉，它仍然会叫

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

求求你，救命！我做错了什么（假设我做错了，考虑到教科书的代码也会出错）？我靠自己，没有导师指导。这很令人困惑。

sqlmysqlmariadb

1条答案

按热度按时间
fafcakar

fafcakar1#

`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值。

赞(0）分享  回复(0）举报  2021-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com