SQL help

[Achtas]

Greetings there, since some around here might know about this then I was wondering if anyone knew a good resource site for SQL.

 

Getting into a project and since there is only one guy who will know how to handle SQL more or less I want to look into it as well to be able to at least help out when needed. I will mostly be dealing with PHP and Java. But yes, my SQL knowledge is very basic so to fully understand everything and make changes to the database, will need some additional info. Found some various sites about, though it is always better to go through the preferred and tested sites.

[Inoue Katsu]

What kind of SQL ?

MySQL ?

Postgresql ?

Oracle ?

Microshit SQL ?

The syntax is mostly the same for all but the devil is in the detail :o

[Achtas]

Ah yes, my bad about the lack of specification. It'll be in postgreSQL.

Edited March 25, 2013 by Achtas

[Inoue Katsu]

mmm ive never actually had to use postgresql, I always found it weird but its -the- open source db for geo stuff.

 

The most used commands are insert, select and delete, you'll want to read up on basic usage for those .. after that 'replace' and 'update'

There are postgresql books as well, but my personal experience is that its easier to tackle a problem with google when it arises then to dig through a book.

Postgress has its own documentation site that *should* help, the mysql one usually gave more questions then answers.

 

If all else fails, go look for a forum about postgresql .. but try and get a general feel for it before even attempting to make a post. Most database forums have a large population of twats who will provide useless answers like 'look in the documentation' or a semi troll post about how silly you are for not knowing the answer.

 

I had to do a lot of digging too for Mercury, discovering commands as I went along .. and constantly asking myself (and then google =p) questions..

If you want to reduce the amount of multi table selects being send from your program, look up 'views'

If you want to do things based on changes in the database, look up 'triggers'

Those are the main 2 time and efficiency savers that I started using more near the end. I'm quite sure the database would have had a way different layout if I had figured those out sooner =p

 

But yea .. google .. postgresql.org.. php.net .. more google .. forums. That goes for both postgresql and php but the php manual is actually very good :)

 

And boooo java! That is satan spawn.

[Achtas]

Yes, Java is, but it is required since I am not going to try and make parts in PHP and ActionScript can go suck it. D: So it will be Java. And yeah, will have to try and make the database as compact as possible considering the data amount being filtered and moved through will be quite considerable.

 

Also thanks for the pointers. xD

Edited March 26, 2013 by Achtas

[Inoue Katsu]

Good database design is key then :-)

Ask if your school has a DBA that can give pointers, and use variables as table names in your code.

I had fixed names initially in Mercury's predecessor and then search/replaced everything by variable names .. that way I could re-design tables while keeping the old one up till the new layout worked properly. And I've had to do that plenty of times :-)

 

And be sure to add plenty of comments, even on your database tables when you plan on needing more then like 20.... and don't re-read your code after a year because you'll be going 'wtf was I thinking!'

[Achtas]

 and don't re-read your code after a year because you'll be going 'wtf was I thinking!'

 

Isn't that a rule with everything pretty much? xD But if all goes well, will have to reread plenty of times and improve. 

[Inoue Katsu]

It is :-)

Although I did once re-write an old 100 line function into like 5 lines that did exactly the same thing.