Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 14 of 14

Thread: MySQL resources

  1. #1
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts

    MySQL resources

    While the MySQL documentation is quite extensive it may not explain things in a way that is completely comfortable for all users of this forum. Perhaps too the examples aren't exactly answering our questions.

    If you have a resource you'd like to point people to, be it a tutorial, a piece of software (GUIs for instance), a good book to recommend, etc. post it in this thread.

    Note that from time-to-time, I will pop in and clean the thread or merge some answers so that all the information is easy to find.

    Please don't post in the thread in reply unless you are adding another resource.

    If you come across a link that is out-dated please PM me with a new link if you have one or for me to remove the old link. That way everything in the thread can be current.

    Please note, don't merely link to someone's list of resources, but point to items you use or have found useful.

    Thanks,
    Dave
    Last edited by guelphdad; 08-20-2006 at 01:36 AM.

  • #2
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    The MySQL site:
    MySQL Developer Zone

    Database Normalization:

    One of the most important things of database development is understanding the concepts behind Database Normalization. This means setting up the proper structure of your tables and how they will work together to make your data retrieval as seemless as possible while minimizing data resources necessary to perform the output.

    An intro to database normalization

    Hierarchical Data:

    Managing hierarchical data can be tricky, but once you get an understanding of how to approach it, you will find this an important tool in your database development.

    Managing hierarchical data
    Categories and Subcategories a useful article written on the Adjacency List model.
    Joe Celko's Trees and Hierarchies in SQL for Smarties (I've found this to be a pretty involved book.)

    GUIs:

    Heidi SQL
    Navicat
    MySQL Administrator
    MySQL Query Browser
    phpMyAdmin if you are using PHP as well


    Database Modelling:

    Data Models (note, there are no comments on the designs, just the layouts)

    SQL injection attacks:

    result from unsanitized user input. The article below details one example of how this could be used to gain access to your data by outside users.

    SQL injections

    PHP and MySQL

    An extensive tutorial I found this tutorial to be pretty extensive and quite helpful.

    http://www.apachefriends.org/en/xampp.html (a quick and seemless way to integrate PHP, MySQL, Apache, PERL, PHPMYADMIN on Windows, Solaris, Linux, Mac OS). Note you won't learn how to tune your applications here, but if you are looking to get them set up quickly and jump right in, I personally have found this to be a good set-up on WinXP. Caveat, the discussion boards on the site are in German.

    SQL formatters:

    These allow you to dump in the code you are using for a query and print it out in a much neater form.

    SQLinForm
    Instant SQL Formatter
    Last edited by guelphdad; 04-11-2007 at 09:16 PM.

  • #3
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    An advanced SQL book I've seen recently, if you are looking for more than the basics I have to say you should pick this up.

    SQL Hacks (Tips & Tools for Digging into Your Data), it is an O'Reilly book by Andrew Cumming & Gordon Russell.

    It has 100 tips and covers necessary code tweaks for Oracle, Postgres, MySQL, SQL Server, Access and DB2.

    One of the best books I've seen on the subject.

  • #4
    New to the CF scene
    Join Date
    Jan 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    hi, guelphdad.
    Here's a link to developer site than engaged in developing database administration and management tools for different SQL Server such as MySQL, Oracle, PostgreSQL, SQLite, Firebird and MaxDB.

    SQL Maestro Group

    All programs has graphical user interface and understandable in use.

    In my opinion, quite good programs .
    At present time i use following tools:
    MySQL Maestro and Firebird Maestro.

    But it's my opinion

  • #5
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    1. Web Hacking from the Inside Out
    2. Hackish PHP Pranks & Tricks

    http://www.amazon.com/s/ref=nb_ss_gw...eywords=flenov

  • #6
    New to the CF scene
    Join Date
    Feb 2007
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sticky Post Request: Choosing Field Types

    Hey,

    I know I'm just new here but I reckon a sticky post about the appropriate field types for various uses would be brilliant!!! I haven't found anything on the net yet that both describes the types AND gives advice about what use the type would be ideal for. The below table is what I use as a reference now (can't remember what site I got it off but I certainly didn't make the data myself) but I still don't think I'm using the right field types and attributes for say my table_ID fields, password fields, large blocks of text, html content etc...


  • #7
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts
    There is a lot of confusion on using the GROUP BY clause and specifically when it is and isn't okay to leave columns out of the GROUP BY clause.

    A little heavy lifting here but this article is quite good for those looking to find out more information.

    Unfortunately I see GROUP BY used incorrectly in a lot of forums.

  • #8
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,634
    Thanks
    4
    Thanked 148 Times in 139 Posts

    Why does this query run in mysql 4 but break in mysql 5?

    The difference is a change in syntax after mysql 5 was implemented.

    This will run in mysql 4 but give an error in mysql 5
    Code:
    SELECT
    foo,
    bar,
    qux
    FROM tablea, tableb
    LEFT OUTER JOIN tablec
    ON tablea.somecolumn=tablec.somecolumn
    AND tablea.differentcolumn=tableb.moescolumn
    what mysql did before was look at the columns and try to figure out what you are trying to do. now the syntax conforms more closely to the sql standard.

    in the case above, at the point the left outer join and table c are mentioned, only tables B and C are in scope, thus you can't join on column a at that time.

    you would have to rewrite the query as:


    Code:
    SELECT
    foo,
    bar,
    qux
    FROM tablea, tableb
    WHERE tablea.differentcolumn=tableb.moescolumn
    LEFT OUTER JOIN tablec
    ON tablea.somecolumn=tablec.somecolumn

    but of course the BEST syntax is to eliminate the comma entirely as so:


    Code:
    SELECT
    foo,
    bar,
    qux
    FROM tablea
    INNER JOIN tableb
    ON tablea.differentcolumn=tableb.moescolumn
    LEFT OUTER JOIN tablec
    ON tablea.somecolumn=tablec.somecolumn

  • #9
    New to the CF scene
    Join Date
    Nov 2008
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    A MySQL GUI for Mac OS X that I've been using for yeards

    Used to be named CocoaMySQL recently changed to Sequel Pro

    Sequel Pro is only running on mac OS 10.5, but you can still find CocoaMySQL on the site if you run 10.4 and 10.3

    http://code.google.com/p/sequel-pro/

  • #10
    Banned
    Join Date
    Aug 2009
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I know I'm just new here but I reckon a sticky post about the appropriate field types for various uses would be brilliant!!! I haven't found anything on the net yet that both describes the types AND gives advice about what use the type would be ideal for. The below table is what I use as a reference now (can't remember what site I got it off but I certainly didn't make the data myself) but I still don't think I'm using the right field types and attributes for say my table_ID fields, password fields, large blocks of text, html content etc...

  • #11
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One means of limiting use of MySQL server resources is to set the global max_user_connections system variable to a nonzero value. This limits the number of simultaneous connections that can be made by any given account, but places no limits on what a client can do once connected. In addition, setting max_user_connections does not enable management of individual accounts. Both types of control are of interest to many MySQL administrators, particularly those working for Internet Service Providers.

    In MySQL 5.0, you can limit use of the following server resources for individual accounts:

    The number of queries that an account can issue per hour
    The number of updates that an account can issue per hour
    The number of times an account can connect to the server per hour
    The number of simultaneous connections to the server by an account (available as of MySQL 5.0.3)

  • #12
    wlf
    wlf is offline
    New Coder
    Join Date
    Aug 2012
    Posts
    61
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by guelphdad View Post
    SQL injection attacks:

    result from unsanitized user input. The article below details one example of how this could be used to gain access to your data by outside users.

    SQL injections
    Here is a good one too from wikipedia that shows how nasty SQL injections can be with complete examples.
    It also explains how to protect your database against such things

    http://en.wikipedia.org/wiki/SQL_injection

    I find programming articles on wikipedia good enough however a mysql book is of course preferable.
    A good programmer is someone who looks both ways before crossing a one-way street. Free hosting

  • #13
    New to the CF scene
    Join Date
    Aug 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    MySQL Admin Tool

    Recently dbForge Studio for MySQL has releazed and I really liked the updated version. Their mysql admin tool ( http://www.devart.com/dbforge/mysql/...istration.html ) really simplified for me the process of mysql administration. You can try their free trial for 30-days andmake the decision!

  • #14
    Regular Coder
    Join Date
    Jun 2010
    Posts
    298
    Thanks
    66
    Thanked 8 Times in 8 Posts
    If you are a visual thinker (like me) and MySQL joins confuse the hell out of you (like me), then this link will make your life a lot easier!

    http://www.codeproject.com/Articles/...n-of-SQL-Joins

    And here's the cheat sheet. I have this printed out and pinned up beside my monitor!

    Last edited by XmisterIS; 11-27-2013 at 06:15 PM.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •