Mysql query not working

Question

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

Answer 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 !!!

Answer 2

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.