一,引言:
今天在做练手项目时,用SQLyog创建数据表时遇到了错误,在SQLyog中该表显示正常创建,但是在cmd命令窗框打开mysql命令行查看表结构或是查询数据都会报错
二,详解:
1,情况详解:
SQLyog查询到的表详情:
/*列信息*/-----------
Field Type Collation Null Key Default Extra Privileges Comment
--------------- -------- --------------- ------ ------ ------- ------ ------------------------------- --------------------
ordernum char(16) utf8_general_ci NO PRI (NULL) select,insert,update,references 订单编号
code char(24) utf8_general_ci NO (NULL) select,insert,update,references 账号
typeno int (NULL) NO MUL (NULL) select,insert,update,references 账号类型编号
bookname char(24) utf8_general_ci NO (NULL) select,insert,update,references 书名
bookno char(24) utf8_general_ci NO MUL (NULL) select,insert,update,references 书籍编号
lendtiem date (NULL) NO (NULL) select,insert,update,references 借书时间
repaytime date (NULL) NO (NULL) select,insert,update,references 还书时间
overdue int (NULL) NO (NULL) select,insert,update,references 逾期
penalty int (NULL) NO (NULL) select,insert,update,references 罚款
penaltystatusno int (NULL) NO MUL (NULL) select,insert,update,references 罚款状态编号
statusno int (NULL) NO MUL (NULL) select,insert,update,references 状态编号
operator char(24) utf8_general_ci NO (NULL) select,insert,update,references 操作员
/*索引信息*/--------------
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression
------ ---------- -------- ------------ --------------- --------- ----------- -------- ------ ------ ---------- ------- ------------- ------- ------------
order 0 PRIMARY 1 ordernum A 0 (NULL) (NULL) BTREE YES (NULL)
order 1 statusid 1 statusno A 0 (NULL) (NULL) BTREE YES (NULL)
order 1 bookno 1 bookno A 0 (NULL) (NULL) BTREE YES (NULL)
order 1 typeno 1 typeno A 0 (NULL) (NULL) BTREE YES (NULL)
order 1 penalty 1 penaltystatusno A 0 (NULL) (NULL) BTREE YES (NULL)
/*DDL 信息*/------------
CREATE TABLE `order` (
`ordernum` char(16) NOT NULL COMMENT '订单编号',
`code` char(24) NOT NULL COMMENT '账号',
`typeno` int NOT NULL COMMENT '账号类型编号',
`bookname` char(24) NOT NULL COMMENT '书名',
`bookno` char(24) NOT NULL COMMENT '书籍编号',
`lendtiem` date NOT NULL COMMENT '借书时间',
`repaytime` date NOT NULL COMMENT '还书时间',
`overdue` int NOT NULL COMMENT '逾期',
`penalty` int NOT NULL COMMENT '罚款',
`penaltystatusno` int NOT NULL COMMENT '罚款状态编号',
`statusno` int NOT NULL COMMENT '状态编号',
`operator` char(24) NOT NULL COMMENT '操作员',
PRIMARY KEY (`ordernum`),
KEY `statusid` (`statusno`),
KEY `bookno` (`bookno`),
KEY `typeno` (`typeno`),
KEY `penalty` (`penaltystatusno`),
CONSTRAINT `bookno` FOREIGN KEY (`bookno`) REFERENCES `bookno` (`bookno`),
CONSTRAINT `penalty` FOREIGN KEY (`penaltystatusno`) REFERENCES `penalty` (`penaltystatusno`),
CONSTRAINT `statusid` FOREIGN KEY (`statusno`) REFERENCES `status` (`statusno`),
CONSTRAINT `typeno` FOREIGN KEY (`typeno`) REFERENCES `type` (`typeno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;在cmd查询时遇到的错误:
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| bookinfo |
| bookno |
| order |
| penalty |
| species |
| status |
| type |
| user |
+----------------+
8 rows in set (0.00 sec)
mysql> desc order;
ERROR 1064 (42000): 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 'order' at line 1
mysql>2,原因:
因为order是order by排序的关键字,在查询时会把order当成关键字,初学者要注意mysql关键字的使用.尽量避免使用关键字.
