Conditionals in MySql queries

NOT BETWEEN 10 AND 20
NOT IN (10,11,12)

Create a Foreign Key

FOREIGN KEY (blog_userID) REFERENCES blog_users(userId) ON DELETE CASCADE

MySQL TimeStamps

MySQL TimeStamps can have 2- 14 digits (where 2 = YY and 10 would equal YYMMDDHHSS).

Tips

  • Use indexes in the database tables will increase speed of queries.
  • EXPLAIN shows performance differences.

You should also create separate MySQL users for different types of activity. For example, the administrator user for the site will need SELECT, INSERT, and UPDATE permissions. They may also need DELETE, but it’s best not to allow that unless absolutely necessary. Conversely, almost everything a customer will do on an e-commerce site will only require a MySQL user with SELECT privileges. Browsing and searching the catalog are simple SELECT queries. It may not be until the user starts to complete an order—actually check out—that an INSERT is required. An UPDATE would be needed if they can change their password or other personal information. DELETE permissions would never be appropriate. In theory, you could create three distinct types of MySQL users with specific permissions:

  • Public: SELECT
  • Customer: SELECT, INSERT, UPDATE
  • Admin: SELECT, INSERT, UPDATE, DELETE

Use Fulltext search on fields which are searched

ADD FULLTEXT(product,productDescription) ... MATCH() AGAINST ()

Wildcard Searches

% finds zero or more characters 
- find a single character

LIKE '_frogs%'

MySQL Users

You should also create separate MySQL users for different types of activity. For example, the administrator user for the site will need SELECT, INSERT, and UPDATE permissions. They may also need DELETE, but it’s best not to allow that unless absolutely necessary. Conversely, almost everything a customer will do on an e-commerce site will only require a MySQL user with SELECT privileges. Browsing and searching the catalog are simple SELECT queries. It may not be until the user starts to complete an order—actually check out—that an INSERT is required. An UPDATE would be needed if they can change their password or other personal information. DELETE permissions would never be appropriate. In theory, you could create three distinct types of MySQL users with specific permissions:

  • Public: SELECT
  • Customer: SELECT, INSERT, UPDATE
  • Admin: SELECT, INSERT, UPDATE, DELETE

MySQL Find and Replace

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

update client_table set company_name = replace(company_name, ‘Old Company’, ‘New Company’)

The above statement will replace all instances of ‘Old Company’ to ‘New Company’ in the field of company_name of client_table table.

Grant Table level access


If you want to grand a new user table-level access you need an SQL statement like this one:


INSERT INTO user (Host, User, Password, Select_priv, Insert_priv, Update_priv,Delete_priv, Create_priv, Drop_priv, Reload_priv, Shutdown_priv,Process_priv, File_priv, Grant_priv, References_priv, Index_priv,Alter_priv) VALUES (‘localhost’, ‘juan’, ‘password’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’,‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’, ‘N’)
GRANT delete,insert,select,update on [dbname].* to nobody@localhost identified by -----
FLUSH priviledges

References

How to Use MySQL Foreign Keys for Quicker Database Development

Understanding JOINs in MySQL and Other Relational Databases

MySQL Comparison Tools

Style Guides

Use lowercase names for tables, in the singular form, no abbreviations (unless common standards). The primary key should end in _id.

Foreign key should be the name of the referenced table + the name of the referenced field.

Indexes should be the table name + _ix_ + field names()

ex. person_ix_first_name_last_name

Suffixes to Use on MySQL field names:

  • _id (primary key)
  • _status (relates to a status)
  • _total
  • _num (contains a number)
  • _name contains a name string
  • _seq
  • _date
  • _tally (a count of something)
  • _size
  • _addr (physical of intangible)

SQL Style Guide

SQL Naming Convention

CSV Files

As of this writing, there exists a single usable CSV format that Microsoft Excel can both read and write safely across platforms. Tab delimited UTF-16LE with leading Byte Order Mark.
Using Excel to output into this format to begin with, you have to use the Save As dialog and choose “UTF-16 Unicode Text (.txt)”. Frustratingly, it includes a .txt extension by default which can be confusing for non-technical users. However, if you provide clear instructions to Windows users to save their filename in quotes with a CSV extension such as "example.csv" it is workable.
If you operate an application with a CSV exporter please use this as your default export format. It will save us all many headaches in the long run.

This page contains information I gathered and thought were very useful. See more notes on programming.