Simple examples on book not working. Help needed !!

[JohnnyAlpha]

I have just started to learn MySQL, and I'm following the book "Sams teach yourself MySQL in 21 days" Seems that I might have made a mistake getting this book since I just noticed that it was published in 2000.

Anyway.. I also noticed that some examples are not working. One of these is the following command which should add a hostname and a database into the mysql main DB.

The problem is that when I enter....

mysql> INSERT INTO host VALUES('localhost','sample_db',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

I get the following error.

ERROR 1136: Column count doesn't match value count at row 1

By questions are....

1-What am I doing wrong ?

2-What does each of the 'Y' represent ?

3-Am I correct in assuming that the columns mentioned in the error are the number of columns in the table called host (in the mysql database) if this is the case, it seems that this table has 6 columns (Field | Type | Null | Key | Default | Extra) so… why do I get the same error even when I reduce the number of entries I was asked to enter (including the 10 'Y') to a total of 6 (as shown hereunder)

INSERT INTO host VALUES('localhost','sample_db','Y','Y','Y','Y');

4-Did anyone use this book and found similar problems ?

5-Any other book you can recommend for a total beginner ?

 

[sleipnir214]

Rather than manipulating the permission tables directly, you can always use a GRANT command:

http://www.mysql.com/doc/en/GRANT.html

If your book doesn't explain what those column values are supposed to do, you definately need to get another book. I've never used a MySQL book, though, so I can't recommend one.

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[JohnnyAlpha]

Thanks for the advice mate... I will follow it but I would still love to know what I was doing wrong. It's just the way I am, why should I by-pass a problem when you can dig a little and maybe find the solution? after all the book presented me with an example. Why should I be happy that it did not work and try to use a different route? What if there was no other route to follow?

Re your comment. My quess is that the book will eventually explain the meanings of the 'Y' but since I like to understand all that is being put in front of me, I decided to write and ask the questions. I still think that the author could have at least put in a small mention.

 

[sleipnir214]

What, exactly, is the difference between asking in Tek-Tips and looking ahead in the book?

Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[webscripter]

The problem is that your table has only a certain amount of fields.

When you use the statement
INSERT INTO host VALUES('localhost','sample_db',
'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');

there aren't enouph fields to enter the data.

I'm guessing that Y stands for yes. It's simply string text, nothing more.

If you're at the mysql prompt in a shell window or msdos window type in the following to see how many fields are in your table.

mysql>describe host;

It will show you how many fields are in the table and the type of data each field will allow.

I have a bunch of commands in a text file that you can play around with. Sometimes the books are pretty boring and you memorize the statements faster just playing with test tables.

###### mysql commands and more for reference ###########

Standard date format is "yyyy-mm-dd".
Standard time format is "hh:mm:ss".
Quotes are required around the standard date and time formats, noted above.
Dates may also be entered as "yyyymmdd" and times as "hhmmss". If entered in this format, values don't need to be quoted.

Numeric values do not need to be quoted. This holds true regardless of the data type a column is formatted to contain (e.g. text, date, time, integer).

Insert Some More Records into the Table
Add this record
mysql> insert into table01 (field01,field02,field03,field04,field05) values
-> (2,'second','another','1999-10-23','10:30:00');

Is it in there?
mysql> select * from table01;


Updating Existing Records
Modify one field at a time
Again, be careful with syntax. Quote marks need to go around text but not around numbers.

mysql> update table01 set field03='new info' where field01=1;

The delete command
mysql> delete from table01 where field01=3;

Update multiple records in one stroke
mysql> update table01 set field05=152901 where field04>19990101;

So, what's up with our data?
mysql> select * from table01;

Adding Fields
...one field at a time
mysql> alter table table01 add column field03 char(20);

...more than one at a time
mysql> alter table table01 add column field04 date, add column field05 time;

Insert a record
mysql> insert into table01 (field01,field02) values (1,'first');

List the fields in a table
mysql> show columns from table01;

List the tables
mysql> show tables;

Create a table
mysql> create table table01 (field01 integer,field02 char(10));

Open the database
mysql> use database01

Instruct MySQL to setup a new database
mysql> create database database01;

Tighten Security
MySQL has good security controls, but the default installation is wide open. So, if you're doing a new installation, let's close things up before we go any further. Of course, change NewRootName and NewPassword, below, to something unique.

mysql> delete from user where Host='%';
mysql> delete from user where User='';
mysql> delete from db where Host='%';
mysql> update user set User='NewRootName', Password=password('NewPassword') where User='root';
mysql> flush privileges;
get version and date
SELECT VERSION(), CURRENT_DATE;SELECT NOW();SELECT USER();
cancel commands by typing \c
for unterminated string:
"> "\c

SHOW DATABASES;
CREATE DATABASE menagerie;
To verify that your table was created the way you expected, use a DESCRIBE statement:
DESCRIBE pet;
Show Columns
mysql> show columns from test;

Example: Rename the table
mysql> ALTER table test RENAME mytest;
Example: Add a column

mysql> ALTER table mytest ADD birthday DATE;

Example: Modify a column

mysql> ALTER table mytest CHANGE
mysql> name newname VARCHAR (25);
Example: Delete a column
mysql> ALTER table mytest DROP newname;

SELECT what_to_select
FROM which_table
WHERE conditions_to_satisfy

data types:
TEXT / BLOB
The text and blob datatypes are used when a string of 255 - 65535 characters is required to be stored. This is useful when one would need to store an article such as the one you are reading. However, there is no end space truncation as with VARCHAR AND CHAR. The only difference between BLOB and TEXT is that TEXT is compared case insensitively, while BLOB is compared case sensitively.


IFNULL(expr1,expr2)
If expr1 is not NULL, IFNULL() returns expr1, else it returns expr2. IFNULL() returns a numeric or string value, depending on the context in which it is used:


You can use REPLACE instead of INSERT to overwrite old rows. REPLACE is the counterpart to INSERT IGNORE in the treatment of new rows that contain unique key values that duplicate old rows: The new rows are used to replace the old rows rather than being discarded.

mysql> UPDATE persondata SET age=age+1;
UPDATE assignments are evaluated from left to right. For example, the following statement doubles the age column, then increments it:

mysql> UPDATE persondata SET age=age*2, age=age+1;
If you set a column to the value it currently has, MySQL notices this and doesn't update it.

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

DELETE [LOW_PRIORITY] [QUICK] FROM table_name
[WHERE where_definition]
[ORDER BY ...]
[LIMIT row_count]

REPLACE works exactly like INSERT, except that if an old record in the table has the same value as a new record on a UNIQUE index or PRIMARY KEY, the old record is deleted before the new record is inserted. See section 6.4.3 INSERT Syntax.

REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
VALUES (expression,...),(...),...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name [(col_name,...)]
SELECT ...
or REPLACE [LOW_PRIORITY | DELAYED]
[INTO] tbl_name
SET col_name=expression, col_name=expression,...



===========================
SHOW COLUMNS FROM tablename;
ADD [COLUMN] create_definition [FIRST | AFTER column_name ]
ADD COLUMN col_name AFTER column2

To ADD column phone:
ALTER TABLE test ADD COLUMN phone VARCHAR(20);

To remove column c:
ALTER TABLE test DROP COLUMN c;

To get column headers
SHOW FIELDS FROM test;

To get values:
SELECT * FROM tablename;

###################### Hope this helps you ##########

Thanks
Tricia
yourkeylady@aol.com

 

[JohnnyAlpha]

Hi Tricia!!

Thanks to your advice I managed to sort out what I was doing wrong (Quite a basic thing after you have someone exlaining)

I would also like to thank you for the rest of the stuff you sent. I am also compiling a similar list for my personal reference.

Once again THANKS and keep it up!

John