SQL

How to create a slug from the name of an item in MySQL/MariaDB. UPDATE my_table SET slug = LOWER(REGEXP_REPLACE(REPLACE(REPLACE(REPLACE(name, ' ', '-'), 'and', '-'), '[^a-zA-Z0-9-]', ''), '-+', '-'));

First, you will need the mysql.connector. If you are unsure of how to get this setup, refer to How to Install MySQL Driver in Python. Presenting the data let’s take two (2) tables as a demonstration for the code below. Users – Table 1 { id: 1, name: 'Carl', fav: 254}, { id: 2, name: 'Emma', fav: 254}, { id: 3, name: 'John', fav: 255}, { id: 4, name: 'Hayley', fav:}, { id: 5, name: 'Andrew', fav:} Products – Table 2...

The below Python code will create a connection to a MSSQLServer instance, and retrieve data from it back into a variable called tblResults. # use pyodbc for database connection import pyodbc # keep our database credentials in a store secrets = { 'host': '<db_host>', 'Name': '<db_name>', 'username': '<db_username>', 'password': '<db_password>', } # create a connection string conn_str = f"DRIVER={{ODBC Driver 17 for SQL Server}};SERVER=tcp:{secrets['host']};DATABASE={secrets['Name']};UID={secrets['username']};PWD={secrets['password']}" # create a connection to the database conn = pyodbc....

Sometimes you don’t want a SELECT query to run for more than a set amount of time. This is a roundabout way to make your server doesn’t die from slow-running queries. Obviously, you should tweak your database and potentially run your SELECT queries through an EXPLAIN plan first. This allows you to create appropriate indexes and find why things are slow in the first place. But: sometimes you still find yourself wanting to kill queries that may run over a certain amount of time....

This came in very handy for me at one point because I needed to have a certain field in each row 14 characters long, but half of them were only 12 characters long. In MySQL: update `table` set `field` = CONCAT(`field`, '1') where LEN(`field`) = 13; In MSSQL: update `table` set `field` = CONCAT(`field`, '1') where LENGTH(`field`) = 13;

The problem Write a SQL query to get the second highest salary from the Employee table. +----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+ For example, given the above Employee table, the query should return 200 as the second highest salary. If there is no second highest salary, then the query should return null. +---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+ The solution SELECT DISTINCT Salary AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1 The output {"headers": ["SecondHighestSalary"], "values": [[200]]}

If you see lots of disk space quickly disappearing on your MySQL or MariaDB server, then you may want to look into the BinLog directory, it is located at /var/log/mysql/. In my case I saw this: usr@server-1:/var/log/mysql# ll total 5571044 drwxr-s--- 2 x x 4096 Mar 25 18:18 ./ drwxrwxr-x 12 x x 4096 Mar 25 05:25 ../ -rw-rw---- 1 x x 104857748 Mar 23 09:29 mariadb-bin.000301 -rw-rw---- 1 x x 104859422 Mar 23 10:07 mariadb-bin....

So you have a table where you want to delete a bunch of rows, based on a particular column being matched in another table. This is easily achievable with MySQL. DELETE FROM TABLE1 WHERE domain IN ( SELECT domain FROM TABLE2 ) The above SQL will delete all rows in TABLE1 that are found in TABLE2. But what if we need to limit which records are returned from TABLE2? That’s simple too....

There are many occasions where you need to INSERT a record into a MySQL database table, but the record already exists. INSERT INTO queue_domains (domain) VALUES('statvoo.com') This will result in a Duplicate entry 'statvoo.com' for key 'domain' error A nice and simple way to resolve this is to use REPLACE INTO instead. This will look to see if the item already exists and will simply replace it. This is faster than doing a SELECT followed by an INSERT....

SQL (Structured Query Language) for relational databases was maintained and standardised by the American National Standards Institute (ANSI) and the International Organisation for Standardisation (ISO) which is affiliated with the International Electrotechnical Commission (IEC). When a new SQL standard is published by these organisations it is outlined on the ISO website and available for everyone to both reference and use as required. The current version used in most production instances is commonly ISO/IEC 9075-11:2011; however, in December of 2016 a new version was published and is described under ISO/IEC 9075-11:2016 which can be found here: https://www....