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
