Databases

A database is a system intended to organize, store, and retrieve large amounts of data easily.

MySQL

Mysql: compare subquery to multiple values in where statement

5 Dec, 2023
If you need to compare multiple columns in your subquery, you can use logical operators within the subquery to combine multiple conditions. Be careful and make sure that the number and data types of...
MySQL

MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

5 Dec, 2023
While doing a subquery I got this error: "error: SQLSTATE[42000]: Syntax error or access violation: 1235 This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'". To fix this...
MySQL

Str replace in MySQL

30 Sep, 2023
To perform a string replacement (or substring replacement) in MySQL, you can use the REPLACE function. This function allows you to search for a specified substring within a string and replace it with...
PostgreSQL

Convert interval to number in postgresql

4 Feb, 2021
There is a simple function for when you need to extract count of days from interval. It is returned as as double, but converted to integer in this example. You get interval when you subtract two...
MSSql

Azure MSSQL: Create read only user

26 Jan, 2021
How to create reader / read-only user in azure database.
MSSql

Set smalldatetime default value to current date/time

25 Jan, 2021
This example shows how to set the default value of a smalldatetime column to current date time.
PostgreSQL

Move tables to another schema in Postgres

26 Nov, 2020
This form moves the table into another schema. Associated indexes, constraints, and sequences owned by table columns are moved as well.
PostgreSQL

Trim all whitespace in PostgreSQL

11 Nov, 2020
In PostgreSQL, the trim() function can be used to remove leading and trailing whitespace from a string. However, if you want to remove all whitespace from a string, you can use a combination of the...
PostgreSQL

Compare values between two tables in PostgreSQL

25 Mar, 2020
In PostgreSQL, the EXCEPT operator is used to retrieve distinct rows from the result set of the first query that are not present in the result set of the second query. It effectively performs a set...
PostgreSQL

Clone/Duplicate a Table in PostgreSQL

25 Mar, 2020
This snippet shows you how you can copy a whole table to another one. Useful if you are about to change data in a table and later need to compare it with EXCEPT.
PostgreSQL

PostgreSql return inserted row primary key

28 Nov, 2019
When you have primary keys generated by the database not by the application, then you might need it to be returned. In postgres you have to use RETURNING function in your sql query in order to have...
MySQL

MySql create database with user

20 Feb, 2019
This snippet shows how to create a new database and user with assigning permissions of the newly created database to the user. In this example I use database name as user name for clarity and...
MySQL

MySQL simple update query example

15 Aug, 2018
There are actually two ways of updating in MySQL. This example uses the simpler way. Other way is to define columns and corresponding values sepparately.
MySQL

MySQL: insert/update multiple rows with a single query

16 May, 2018
To insert multiple rows with a single query you can use regular insert with multiple value rows. You can also use update if you have primary key set.
MySQL

Import csv to MySQL FAST

16 May, 2018
Import csv file to MySQL, the fastest way is to use a direct import with query. You have to have the columns created the way you need.
MySQL

MySQL export query as csv directly

8 May, 2018
Example query for direct csv saving from mysql. Using query to import or export data is the fastest method. You can export your query results as csv with custom syntax. You have to use union to join...
MySQL

Arch Linux: "mtop: installing libmariadbclient (10.1.24-1) breaks dependency 'libmysqlclient'"

1 Jun, 2017
Got an error while trying to update the system "mtop: installing libmariadbclient (10.1.24-1) breaks dependency 'libmysqlclient'". The package 'libmariadbclient' dropped its provide for '...
MySQL

MySql error: Incorrect date value: '0000-00-00'

27 Jan, 2017
From version 5.7 MySQL stops supporting zeros value in date / datetime. If you have those values in your database you get errors for queries, like group by. Also you are not able to insert zero dates...
MySQL

Is MySQL Table empty

21 Sep, 2016
If you need to show user different content whether a table is empty or not. So you would need to count table rows for that. Counting every row will iterate through the table rows and is really slow....
MySQL

MySQL: Group by date when using datetime datatype

25 Apr, 2016
Group by date when using datetime datatype. In this snippet the grouping is used to pull out monthly statistics. The query is pretty much self explanatory. Group by in mysql can help you to bring...
MySQL

Show progress of MySQL import

6 Oct, 2015
Have you ever had to do bigger mysql import? If you do, you probably get annoyed by not being able to know how long it will take to complete. There is a command called "pv". Basically it allows a...
MySQL

MySQL InnoDB performance tuning / tweaking

5 Dec, 2014
MySQL configuration that I am using for developing Drupal in localhost. The config file can be found from /etc/mysql/my.cnf under Linux. I would use it for any apache instance tough. You might get...
MySQL

MySQL: Convert table character set to utf8

14 Mar, 2014
Using this the query in this snippet enables you to convert character set to utf8. This works in mysql or mysql like database, drop-in databases like mariadb for an example.
MySQL

MySQL: String Replace

9 Nov, 2013
Snippet showing, how to do string replace in MySQL. In this example we update a field with the same field content except we run it through string replace. This is the same as find & replace in...
MySQL

Drop all tables from database in mysql

8 Nov, 2013
This snippet shows you how to drop all tables from database in mysql. Unsing drop database will just delete the database and later you just recreate it with create database query.
MySQL

SQL Joins - Visual Representation

22 Jul, 2013
Visualization of sql joins is very helpful when you are not wrinting sql every day. This example uses MySQL joins, but same applies to any SQL language (postgresql, mssql, etc...). Visual data and...
MySQL

Command line: Import MySQL database

15 Jul, 2013
To import a MySQL database directly from the command line, you can use the mysql command with the appropriate options. The import process may take a few moments to complete, depending on the size of...
MySQL

Command line: List all MySQL databases

15 Jul, 2013
Want to check the databases you have created using the MySQL Command Line Client? Use mysql command to connect to mysql server and list available databases.
MySQL

MySQL - Get first letters

10 Jul, 2013
This snippet shows you how to select discinct first letters of titles. After getting the first letters you might want to transliterate the characters, you can do it in mysql by custom function where...
MySQL

MySQL - Remove trailing carriage return

5 May, 2013
Sometimes when you import lines from txt files you might get \r in the end of lines. When you think it's easier to remove trailing carriage returns than reimporting then you can use this query.
MySQL

MySQL - Get years of from date field

21 Mar, 2013
If you have a lot of content with timestamps, you might want to get all the years where the content belongs. For an example created. With this query you can get years when some content was created.
MySQL

MySQL Create Database Command Line

3 Jan, 2013
The MySQL command line client provides you with a means of interacting with your MySQL server from the command line. Open terminal window to connect to MySQL server also you may have to do that via...
PostgreSQL

Get tables names in PostgreSQL

17 Feb, 2012
Get tables names in PostgreSQL.
MSSql

Get MSSQL column / field information

17 Feb, 2012
Running the query in this snippet will get MSSQL column information. it fetches and displays information about all columns in the specified table, including details such as column name, data type,...
MSSql

Get all tables from database in MSSQL

17 Feb, 2012
MSSQL query that gets all database tables. To get a list of all tables in a MSSQL database, you can use a SQL query to retrieve the table names from the system objects.
MySQL

Clear Data of MySQL Table

24 May, 2011
How to delete all rows of MySQL table. With reseting primary key auto increment and without reseting it.
MySQL

MySQL Comments in Query

6 May, 2011
How to write comments in MySQL queries.
MySQL

Delete all rows older than x minutes or any time in MySQL

6 May, 2011
This query example shows how to delete rows older than x amount of minutes, hours, days, months or even years. This is a part of MySQL date and time functions.