0
CREATE DATABASE `menu`;

USE menu;

DROP TABLE IF EXISTS `fish`;

SET @saved_cs_client = @@character_set_client;

SET character_set_client = utf8;

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’,
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
LOCK TABLES `fish` WRITE;

INSERT INTO `fish` VALUES 
(1,’catfish’,’8.50’),(2,’catfish’,’8.50’),(3,’tuna’,’8.00’),
(4,’catfish’,’5.00’),(5,’bass’,’6.75’),(6,’haddock’,’6.50’),
(7,’salmon’,’9.50’),(8,’trout’,’6.00’),(9,’tuna’,’7.50’),
(10,’yellowfin tuna’,’12.00’),(11,’yellowfin tuna’,’13.00’),(12,’tuna’,’7.50’);

UNLOCK TABLES;

I'm currently reading this book on how to use MySQL with python and would like to know why this query from the book is not currently working.( MySQL version 5.6 and workbench 6.2 in Windows 7)

Things I have tried

  1. Typing in the code by hand
  2. Copying and pasting from the pdf into notepad++, workbench and notepad
  3. I copied the file into notepad and saved it in different encoding such as UTF-9

It is probably something simple I am missing, but I am new to this.

The error message get when running the query.

11:25:50 CREATE TABLE fish ( ID int(11) NOT NULL auto_increment, NAME varchar(30) NOT NULL default ‘’, PRICE decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (ID) ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1

Error Code: 1064. 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 '‘’, PRICE decimal(5,2) NOT NULL default ‘0.00’, PRIMARY KEY (ID) ) E' at line 3 0.000 sec

  • 1
    You say it's not working, but you don't include an error message in your question? I imagine it is due to lots of different quotation marks being used – Philᵀᴹ Jan 15 '15 at 17:24
  • Hello @Phil I edited the query and added the error message. – user1238097 Jan 15 '15 at 17:32
  • You've got multiple different quotation marks, as I said. Replace them all with ' – Philᵀᴹ Jan 15 '15 at 17:34
  • 2
    String constants need to be enclosed with ' (straight single quotes), not with (some "curly" single quote) – a_horse_with_no_name Jan 15 '15 at 17:35
1

Your problem is the quotes you are using for the default of NAME.

Here is your original create table from the question

CREATE TABLE `fish` (
`ID` int(11) NOT NULL auto_increment,
`NAME` varchar(30) NOT NULL default ‘’, <<--- LOOK HERE
`PRICE` decimal(5,2) NOT NULL default ‘0.00’,
PRIMARY KEY (`ID`)
) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;

You gave default ‘’ and the mysql client does not understand it.

You need to express it with standard single quotes like this : default ''

I just noticed that a_horse_with_no_name's comment already says this.

Don't change anything else. Leave all other quotes as is.

Give it a Try !!!

  • It worked after trying your code. The quotes where were I was going wrong. – user1238097 Jan 15 '15 at 18:31
0

Try this:

CREATE DATABASE 'menu';

    USE menu;

    DROP TABLE IF EXISTS 'fish';

    SET @saved_cs_client = @@character_set_client;

    SET character_set_client = utf8;

    CREATE TABLE 'fish' (
    'ID' int(11) NOT NULL auto_increment,
    'NAME' varchar(30) NOT NULL default '',
    'PRICE' decimal(5,2) NOT NULL default '0.00',
    PRIMARY KEY ('ID')
    ) ENGINE=MyISAM AUTO_INCREMENT=27 DEFAULT CHARSET=latin1;
    SET character_set_client = @saved_cs_client;
    LOCK TABLES 'fish' WRITE;

    INSERT INTO 'fish' VALUES 
    (1,'catfish','8.50'),(2,'catfish','8.50'),(3,'tuna','8.00'),
    (4,'catfish','5.00'),(5,'bass','6.75'),(6,'haddock','6.50'),
    (7,'salmon','9.50'),(8,'trout','6.00'),(9,'tuna','7.50'),
    (10,'yellowfin tuna','12.00'),(11,'yellowfin tuna','13.00'),(12,'tuna','7.50');

    UNLOCK TABLES;

Use ' to string my friends.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.