[SOLVED] Need explanation for these SQL things

[wolfcry044]

I've been reading guide after guide, and I have not found any real down to earth explanations for some things. I've been only learning PHP and SQL for a few days now, so please bear that in mind.
I'll list the things I need help on in here. I just basically need to know what they do, and what they are used for in an easy as possible way to understand. I've learned tons over the past few days, but I'm not good with syntax and stuff yet. I know basically how most things work. I've created applications, hooked up to database, drawn information from it, etc. if you go to [url=http://www.uafclan.com]www.uafclan.com[/url] you will see a few things I've been working on. I've done tons of tutorials, and made lots of little things doing those tutorials.

Anyway, Here's the list of things I would very much like to understand better how they work, what they are for, etc. Thank you so much, this will help me a ton!


1.type:
Example- mediumint, smallint, etc. What are all those for?

2.Collation

3. Attributes

4. NULL

5. Default

6. Extra

7. Index *What's that do?*

8. Primary *Is that the same as Index? what's it do?*

9. Unique [I'm guess that's the default for everything.

10. Length/Values- That is just to determine how long the characters will be right?
If so, is it bad to set them all to 255? I'm not sure if it drains the database if I set them all to 255 or not.

11. Comments- I know what comments are, but what do they do here? Just for reference?

12. Charset

[craygo]

1. type  -> exactly what it says you can read more here http://help.scibit.com/Mascon/masconMySQL_Field_Types.html
2. Collation -> This is the language and character type
3. Attributes -> This is Unsigned http://dev.mysql.com/doc/refman/5.0/en/numeric-types.html
4. NULL -> This is weather or not you want the field to allow null values
5. DEFAULT -> What you want the default value to be if nothing is set at time of insert
6. EXTRA -> This is if you want an auto increment which will give a unique identifier for each row. Highly recommended
7. INDEX -> This will index the column for faster searches
8. PRIMARY -> This is usually given to the auto increment field. This means the field cannot have duplicates.
9. UNIQUE -> This will make the field unique so that there can be no duplicate. This is NOT the default
As an example You can have the primaty field called user_id which is and auto increment field and also have a field called username. Since you do not want the same username for more than one person you can make this field Unique.
10. LENGTH/VALUES -> all depends on what you are storing in the fields. You really do not need an interger field to be 255 long. 11 is usually a good value that will give you numbers up to 99 billion. and as far as text go, set them for what you will be storing. If you don't want user names to be more than 20 characters then set it to 20.
11. COMMENTS -> Yes this is for reference only
12. CHARSET -> Default is latin1, If you have chinese or some other language you would have to change this

Ray

[wolfcry044]

Thank you very much. I appreciate your help!
  One thing: For NULL, could you go into more depth on that? I believe NULL means nothing, but I'm not sure what it's used for still.

[craygo]

Yes it means nothing. Because there is a difference between a space " " and null. In your script if you are trying to find null values you would not use something like " " because a space is something. If you allow null values, then if you insert a row into your database and do not give a value for the column then NULL will be there. If you do not allow NULL values and try to insert a row, and forget to assign a value for that column you will get an error unless you have set a default value for that column. So things work together to keep your table "CLEAN" so to speak.
Ray

[wolfcry044]

That makes sense. [sort of ]  ???
I will record this post for future reference. If anyone else wants to put in any comments about those things too, please do. I could use all the help I can get!  ;D

Thank you so much guys.