#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    59
    Rep Power
    0

    Creating Database, Table and Columns with SQL


    I would like to know if some SQL statements are correct or do I need to add more. Please help with Syntax.

    CREATE DATABASE database1;

    CREATE TABLE table1 {field1 type Not Null , field2 type Not Null};

    How to add or remove a Field in an existing Table?

    When adding a Field how easy is it to insert after an existing Field and before another in an existing Table?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    This is all documented: http://dev.mysql.com/doc/refman/5.5/en/sql-syntax.html
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    59
    Rep Power
    0
    It looks like "CREATE DATABASE database1" will work.

    From your site the syntax is unclear:

    CREATE TABLE table1 {column1 varchar(20) Not Null, column2 int(10) Not Null, column3 char(10), Null}

    Are there spaces or commas or other punctuation between columns?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    the best way to know if your syntax is correct is to actually run it

    but i'll give you a head start --
    Originally Posted by Philosophaie
    CREATE TABLE table1 {column1 varchar(20) Not Null, column2 int(10) Not Null, column3 char(10), Null}
    that won't work until you replace the curly braces with parentheses, and remove the last comma which is extraneous
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    59
    Rep Power
    0
    As part of the original question.

    How to add or remove a Field in an existing Table?

    "INSERT COLUMN AFTER"?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    not INSERT -- ALTER TABLE

    remember, da manual is your friend, so if you don't know the ALTER TABLE syntax, please go look it up
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2010
    Posts
    59
    Rep Power
    0
    ALTER TABLE table1 ADD COLUMN column1 column_definition AFTER column_before1

    What is "column_definition" in this case?

    Does that mean "ALTER TABLE table1 ADD COLUMN varchar(50) Not Null AFTER column4"?
    Last edited by Philosophaie; February 15th, 2013 at 05:40 PM.
  14. #8
  15. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,118
    Rep Power
    4274
    Originally Posted by Philosophaie
    Does that mean "ALTER TABLE table1 ADD COLUMN varchar(50) Not Null AFTER column4"?
    what happened when you tested that?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo