Simple Way To Use Pivot In SQL Query
This post intends to help T-SQL developers get started with PIVOT queries. Most business applications will need some sort of PIVOT queries and I am sure many of you must have come across pivoting requirements several times in the past.
Let us have a table name Invoice which has …
I have talked about Database Change Management using Liquibase. Now, I will talk about the integration with Jenkins CI.
As you can see in the example of my previous post. In the pom.xml there are 3 profiles: update,tag and rollback which will be used by Jenkins. The profile approach will be useful in our integration.
I am …
Liquibase is a dabasebase chane management tool. Rather than writing SQL directly against the database to create, update or drop database objects, developers define their desired database changes in XML files.
Any change to database are grouped into "ChangeSet", the best practice is one changeset per modification to make roll back easily. Changes to database can …
1. Install Java
Liquibase 3.x requires Java 1.6+ so lets get that installed first
2. Get the Liquibase Code
I'm just going to install put Liquibase in the home directory for now
3. Install MySQL
4. Install the MySQL connector for Java
5. Create the MySQL database
6. Create a Changelog file
Add the following...
7. Run Liquibase
8. Create a liquibase.properties file
Add the following...
This post will look how to use encryption to secure your database credentials.
Before we can go on to use GPG to encrypt our credentials, we need to get it working. GnuPG comes with almost every *nix operating system, but for this post we’ll be using Ubuntu 16.04 LTS and we’ll presume that it isn’t yet …
I hit a weird problem while I was setting up a query to illustrate a point (blog to be published next week). Let’s take the basis of the problem and explain it. I wanted data with distribution skew, so I ran this query to find out if there was a wide disparity between the …
Analyzing Business Metrics: https://www.codecademy.com/learn/sql-analyzing-business-metrics
SQL joins infografic: http://cd64.de/sql-joins
Sequel Pro: http://www.sequelpro.com/
Find out the IP Address of the Mysql Host
Sample database cart table contains four columns cart_id, user_id, product_id and created_at.
Enable MySQL Event Scheduler
Start MySQL event scheduler by executing following query in PhpMyAdmin or MySQL command prompt.
Create a Event
Here the following event will run everyday and clear/delete 10 days old data from cart table based on time stamp
If you want to modify the …
SQL Server’s query optimizer uses distribution statistics to determine how it’s going to satisfy your SQL query. These statistics represent the distribution of the data within a column, or columns. The Query Optimizer uses them to estimate how many rows will be returned from a query plan.
Distribution statistics are created automatically when you create an …
Schema changes can often lock your database, stall your application, and cause an outage, so you'll want to be careful in how you design the infrastructure to permit database changes.
Achieving a zero-downtime schema deployment can be done with load balancer and a technique called Blue-Green deployment.
"Blue-Green deployment" is a fancy term that basically means you …
L'évolutivité à son meilleur niveau !
Saviez-vous que MariaDB 10 améliore considérablement vos performances ? Et que le nouveau proxy MariaDB MaxScale (pour MariaDB & MySQL) permet une évolutivité simple de votre infrastructure de base de données tout en répondant aux besoins des DBA, des développeurs et des architectes de bases de données ? Savez-vous comment …
This MySQL tutorial explains how to create an AFTER INSERT Trigger in MySQL with syntax and examples.
An AFTER INSERT Trigger means that MySQL will fire this trigger after the INSERT operation is executed.
The syntax to create an AFTER INSERT Trigger in MySQL is:
CREATE TRIGGER trigger_name
ON table_name FOR EACH ROW
Atomic Commit In SQLite
An important feature of transactional databases like SQLite is "atomic commit". Atomic commit means that either all database changes within a single transaction occur or none of them occur. With atomic commit, it is as if many different writes to different sections of the database file occur instantaneously and simultaneously. …
SQLite performs inserts 8-14 times faster then InnoDB / MyISAM
SQLite performs updates 4-8 times faster then InnoDB and as fast as MyISAM
SQLite performs selects 2 times faster than InnoDB and 3 times slower than MyISAM
SQLite requires 2.6 times less disk space than InnoDB and 1.7 times more than MyISAM
Allowing null values or using synchronous=NORMAL makes …
Most users at one time or another have dealt with hierarchical data in a SQL database and no doubt learned that the management of hierarchical data is not what a relational database is intended for. The tables of a relational database are not hierarchical (like XML), but are simply a flat list. Hierarchical data has …
When I was comparing performance of UNION vs MySQL 5.0 index merge algorithm Sinisa pointed out I should be using UNION ALL instead of simple UNION in my benchmarks, and he was right. Numbers would be different but it should not change general point of having optimization of moving LIMIT inside of union clause being …
The simplest way of exporting a table data into a text file is using SELECT...INTO OUTFILE statement that exports a query result directly into a file on the server host.
Exporting Data with the SELECT ... INTO OUTFILE Statement:
The syntax for this statement combines a regular SELECT with INTO OUTFILE filename at the end. The default …