MySQL 数据库模式语法行为异常

Question

我正在尝试编写一个 MySQL 数据库(称为 security_db.sql)，在其中概述了一些有关保安工作的表格。代码本身如下。抱歉，格式很奇怪。

drop database if exists security;

create database security;

use security;


drop table if exists caller;

drop table if exists number;

drop table if exists officer;

drop table if exists suspect;

drop table if exists report;

drop table if exists car;

drop table if exists writeup;

drop table if exists activitylog;

drop table if exists ticket;


create table caller

(

firstname varchar(64),

lastname varchar(64),

cellnumber int unsigned not null primary key,

location varchar(64),

);

insert into caller (firstname, lastname, cellnumber, location)

values (`Braydon`, `Rekart`, `2174088935`, `Stoddard`);

create table report

(

incidentreport varchar(64) not null primary key,

);
insert into report (incidentreport) values (`Testing this database.`);

reate table activitylog

(

report text not null,

date int not null primary key,

location varchar(64) not null,

);

insert into activitylog (report, date, location) values (`Testing this 

database`, `112233`, `OlinMahan`);

create table number

(

securityphone int not null primary key,

);

insert into number (securityphone) values (`1217666666`);

create table suspect

(
suspectfirstname varchar(64) not null,

suspectlastname varchar(64) not null,

weight int,

height int,

suspectid int not null primary key,

);

insert into suspect (suspectfirstname, suspectlastname, suspectid)

values (`Braydon`, `Rekart`, `3`);

create table writeup

(
recipientfirstname varchar(64) not null,

recipientlastname varchar(64) not null,

whogaveit varchar(64) not null,

reason text not null,

writeupid int not null primary key,

reason text not null,

writeupid int not null primary key,

);

insert into writeup (recipientfirstname, recipientlastname,

whogaveit, reason, writeupid) values (`Braydon`, `Rekart`, `Karson`, `Late`,

 `45`);

create table officer

(

officerid int not null primary key,

position varchar(64) not null,

firstname varchar(64) not null,

lastname varchar(64) not null,

);

insert into officer (officerid, position, firstname, lastname)

values (`1`, `Crewhead`, `Braydon`, `Rekart`);

create table car

(

make varchar(64),

color varchar(64),

model varchar(64),

licenseplatenumber varchar not null primary key,

);

insert into car (licenseplatenumber) values (`N33D4SP33D`);

create table ticket
(
ticketnumber int primary key,

writerfirstname varchar(64) not null,

writerlastname varchar(64) not null,

recipientfirstname varchar(64) not null,

recipientlastname varchar(64) not null,

recipientfirstname varchar(64) not null,

recipientlastname varchar(64) not null,

writerid int,
);

insert into ticket (ticketnumber, writerfirstname, writerlastname, 
recipientfirstname, recipientlastname)

values (`3`, `Braydon`, `Rekart`, `Karson`, `Gragert`);

它给我的错误如下。

mysql> use security

Database changed

mysql> source security.db

Query OK, 0 rows affected (0.01 sec)

Query OK, 1 row affected (0.00 sec)

Database changed

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

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 ')' at line 7

ERROR 1146 (42S02): Table 'security.caller' doesn't exist

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 ')' at line 4

ERROR 1146 (42S02): Table 'security.report' doesn't exist

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 ')' at line 6

ERROR 1146 (42S02): Table 'security.activitylog' doesn't exist

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 ')' at line 4

ERROR 1146 (42S02): Table 'security.number' doesn't exist

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 ')' at line 8

ERROR 1146 (42S02): Table 'security.suspect' doesn't exist

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 ')' at line 8

ERROR 1146 (42S02): Table 'security.writeup' doesn't exist

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 ')' at line 7

ERROR 1146 (42S02): Table 'security.officer' doesn't exist

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 'not null primary key, )' at line 6

ERROR 1146 (42S02): Table 'security.car' doesn't exist 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 ')' at line 9

ERROR 1146 (42S02): Table 'security.ticket' doesn't exist

无论我如何摆弄语法，我似乎都无法弄清楚。我觉得这完全是菜鸟错误，但我需要帮助。谢谢。

Answer 1

在每个创建表语句中的最后一个字段定义后面都有一个额外的逗号，因此不会创建任何表，也不会成功插入。

示例:

...
location varchar(64) not null, <- this comma

);

您还对要错误插入的值使用反引号。反引号用于括起对象标识符，例如表或字段名称。字符串值应该用单引号或双引号括起来，数字值不应括起来。

示例:

insert into caller (firstname, lastname, cellnumber, location)
values ('Braydon', 'Rekart', 2174088935, 'Stoddard');