Typically, you would use mysql client cli as follows, You could also create a ~/.my.cnf file to set default properties, including connection info, You could even store a password but I wouldn’t recommend storing it in the clear. Also, the above approach is not useful when you have more than one MySQL database across multiple hosts, […]
mysql
python slice and sql every Nth row
I would like to retrieve every Nth row of a SQL table, and I would like this accessed via a python slice function. A python slice allows access to a list (or any object that implements a __getitem__ method) by a start, stop, and step — for example, >>> foo = range(100) >>> foo[5] 5 […]
nvl, ifnull, nullif, isnull, coalesce
I would like to programmatically handle NULL values in a database. If I add or multiply a known value to a NULL, the result is still NULL. This is an appropriate result as a NULL value in a database schema means “unknown” or “nonexistent”, so mathematic functions against known values and NULL should produce NULL. […]
oracle, limit results and pagination
I would like to limit query results in oracle to support pagination. In postgres, mysql, and sqlite you can use the LIMIT and OFFSET modifier, e.g., mysql> SELECT login FROM users ORDER BY login LIMIT 2 OFFSET 70001; +————-+ | login | +————-+ | acrobat11 | | acrobat12 | +————-+ 2 rows in set (0.03 […]
sql, users not in group
I would like to find all users not in a specific group, given the following database schema: CREATE TABLE foobar_users ( user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE foobar_groups ( group_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, groupname VARCHAR(20) UNIQUE NOT NULL ); CREATE TABLE foobar_users_groups ( user_id INT […]
database indexes and optimization
Previously, I discussed database schema normalization. Next, I would like to add indexes and optimize a schema for high-volume production usage. Consider the following schema: CREATE TABLE foobar_users ( user_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(20) NOT NULL ); CREATE TABLE foobar_groups ( group_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, groupname VARCHAR(20) NOT NULL ); […]
php DB adapter, revisited
I would like to call mysql stored procedures as if they were local php functions. For example, let’s use the following mysql stored procedure, DELIMITER // CREATE PROCEDURE `usersByPage`(page_num INT, per_page INT) BEGIN SET @lim_start = (page_num – 1) * per_page; SET @lim_end = per_page; PREPARE stmt FROM ‘SELECT * FROM foobar_users ORDER BY user_id […]
mysqldump, tips and tricks
I want to backup a mysql database. The easiest approach is using mysqldump with its default options, i.e., # mysqldump -u user -h host -ppass db > backup.sql This will dump the full DDL and DML needed to re-create the database. Table locking is enabled by default, although for InnoDB it is recommended to use […]
Denormalization?
Previously, I normalized my tables to Sixth Normal Form (6NF). Now, I want to consider denormalizing, if and when it’s appropriate. Denormalizing is different than an un-normalized schema, which is never, ever, recommended. Strategies for denormalization appear in data-warehousing designs, specifically in OLAP star schemas. However, a snowflake schema is often a viable (and normalized) […]
Normalization, 6NF
Previously, I normalized my tables to Fifth Normal Form (5NF). Now, I want to continue to normalize such that the tables in my schema are in Sixth Normal Form (6NF). 6NF requires that each table satisfies only trivial join dependencies. All of the 5NF tables are already in 6NF, except one (it’s been going unnoticed […]
Normalization, 5NF
Previously, I normalized my tables to Fourth Normal Form (4NF). Now, I want to continue to normalize such that the tables in my schema are in Fifth Normal Form (5NF). 5NF requires that every join dependency in a table is implied by the candidate keys. All of our 4NF tables meet the 5NF requirement, but […]
Normalization, 4NF
Previously, I normalized my tables to Third Normal Form (3NF) and Boyce-Codd Normal Form (BCNF). Now, I want to continue to normalize such that the tables in my schema are in Fourth Normal Form (4NF). 4NF requires for any non-trivial multivalued dependencies X->Y, that X is a superkey. In other words, there are not multiple […]
Normalization, 3NF
Previously, I normalized my table to Second Normal Form (2NF). Now, I want to continue to normalize such that the tables in my schema are in Third Normal Form (3NF). 3NF requires that every non-prime attribute is directly dependent on every candidate key. Fortunately, normalizing to 2NF accomplished this for all but one table, i.e., […]
Normalization, 2NF
Previously, I normalized my table to First Normal Form (1NF). Now, I want to continue to normalize such that the tables in my schema are in Second Normal Form (2NF). 2NF requires that all non-key attributes depend on the whole of the key and not a subset of a compound key. The 1NF example can […]
Normalization, 1NF
I want to normalize a database schema for efficient transactions. I want to make sure all tables in the schema are in First Normal Form (1NF). There is no universal definition of 1NF; some definitions require only atomicity such that it may be impossible to violate 1NF if using a relational database such as MySQL […]