SQL

MySQL Resources

MySQL is a free, open-source relational database that has been used in thousands of websites, large and small. This is a contributed collection of resource links on MySQL. Feel free to add your favorites.

This article was originally posted on the UCLA Programmers Wiki.

PostgreSQL Resources

See Also:

PostgreSQL official website:

Modified versions:

Tuning Guides:

Graphical Admin Tools:

Web-based Admin Tools:

Why doesn't mysqlshow work for databases or tables with underscores in their names?

mysqlshow has a tricky feature that interprets SQL wildcard characters (*,?,%,_) as wildcards if they appear in the last argument you give it. While the first 3 characters don’t get used much (if at all) in naming databases, the underscore is a common word separator and could cause problems if you try to use mysqlshow. Instead of showing the details of a database or table, if the object of interest has an underscore in its name, mysqlshow will treat it as a search and display only the name of the one object it finds.

The trick to getting mysqlshow to display details is to add another argument at the end. mysqlshow only interprets wildcards in its last argument, so adding another one will tell the command to interpret the underscores literally and not as expressions for a search.

To get the database underscores_are_great to show its tables, type:
mysqlshow underscores_are_great %
The ‘%’ on the end is a wildcard which will show all tables, which is what you want it to do.

This works for displaying individual tables too, just add a ‘%’ as a final argument.

See what else mysqlshow is good for

What is mysqlshow good for?

mysqlshow is a command-line tool included with standard MySQL distributions, similar to mysqladmin or other tools. mysqlshow is used to show summary information about databases and tables.

Here is its basic usage:
mysqlshow [OPTIONS] [database [table [field]]]

There are four basic modes:

There is also a search mode, which is activated if the last argument contains an SQL wildcard (*,?,%,_). In this mode, all objects at the level of the last argument are searched. For example, mysqlshow ucla c% matches all tables in ucla starting with the letter ‘c’.

Numerous options are available:

Gotchas:

Also see the mysqlshow entry in the MySQL manual.

How can I search/replace strings in MySQL?

MySQL lets you replace all occurrences of a character or string of characters in a table with some other character or string.

UPDATE table SET field1 = REPLACE(field1, 'replace_that', 'with_this'), field2 = REPLACE(field2, 'and_that', 'with_this')

As an example, let’s say that you have a pets table and you want to change every cat into a dog:

UPDATE pets SET species = REPLACE(species, 'cat', 'dog')

This feature is also useful for transposing characters from one encoding to another. Here’s how to change Windows-style CRLF line endings into Unix-style LF line endings:

UPDATE users SET bio = REPLACE(bio, CONCAT(CHAR(13), CHAR(10)), CHAR(10))

Of course, you don’t have to replace all occurrences in a table if you don’t want to. Just supply a WHERE clause and only those rows will be affected.

Microsoft Access, OpenOffice and MySQL

Using ODBC it is possible to connect Microsoft Access (or OpenOffice) to a MySQL database. Access or OpenOffice can even be used as “front end” to MySQL.

The “MySQL Connector/OBDC” drivers are available at: <http://dev.mysql.com/doc/refman/5.0/en/connector-odbc.html>

Installation information and documentation is available for Windows, Mac OS X and Unix platforms.

WindowsXP: http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-installation-binary-windows.html

MacOS X: http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-installation-binary-macosx.html

SQL joins

Please add other helpful links:
SQL joins: a visual explanation
http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

Get rid of default annoyances in MySQL Workbench

By default, if you make any changes to table rows, there is an annoying 2-step confirmation dialog when you click Apply. It also prevents you from doing many mass UPDATEs and DELETEs (this is called ‘Safe Updates’).

Safe Updates is a good idea in principle, since it stops you from running a DELETE without a WHERE clause, for example. But at the same time it prevents you from running an UPDATE/DELETE with a WHERE based on a column other than an ID, which I find myself doing sometimes.

The confirmation dialog seems utterly pointless. You already have to click the ‘Apply’ button in the lower right corner to get your query to run, anyway.

To turn off these options:

  1. In the menu, go to Edit > Preferences…
  2. Click on the ‘SQL Queries’ tab
  3. In the ‘General’ section, uncheck ‘Safe Updates’
  4. In the ‘Query Results’ section, uncheck ‘Confirm Data Changes’

These instructions are for version 5.2.47.

Who uses PostgreSQL at UCLA?

The purpose of this page is to create a directory of people who are using PostgreSQL and what advanced features they are using. Please try to mention how you have found certain features useful so that you can give other people ideas on how to better utilize the database.

Jason Fong
Database’s purpose: collecting large sets of data, and querying data (usually big long queries) to discover trends or other interesting results
Total database size: approximately 500 GB
Largest table: approximately 500 million rows

Advanced features in use:

Why NoSQL Matters

Useful survey of NoSQL (and SQL) technologies and a summary of the philosophy…

http://blog.heroku.com/archives/2010/7/20/nosql

Taken from email by Jose Hales-Garcia to Campus Web Publishers List