MySQL tips - myantandco/RA-BitnobiPilotJuly2020 GitHub Wiki

Here are some handy SQL snippets. Most are MySQL specific but a few are generic SQL. The ones starting with "Select" can be executed in a Bitnobi SQL element. For the others you need to use the mysql command line.

Rename a database

create the new empty database, then rename each table in turn into the new database. Deleted the old database when done.

CREATE DATABASE new_db;
RENAME TABLE old_db.table TO new_db.table;
DROP DATABASE old_db;

List databases, tables, columns

# show a list of databases .. generic SQL:
show databases;

# list all tables from database 'sakila':
SELECT table_name FROM information_schema.tables WHERE table_type = 'base table' AND table_schema = 'sakila' ;

# list all databases and tables:
SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'base table' ;

# list all database names:
SELECT schema_name FROM information_schema.schemata;

# list all columns in a table:
SELECT column_name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'SchemaNameHere'
  AND table_name = 'TableNameHere';

Count number of columns in a table:

SELECT COUNT(*)
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'SchemaNameHere'
  AND table_name = 'TableNameHere'

Show a table definition

use <database name.;
show create table <table name>;

List the columns in a table that are not nullable

select column_name
from information_schema.columns
where table_schema = 'DatabaseNameHere'
and   table_name = 'TableNameHere'
and is_nullable = 'NO';

List the columns in a table and group by is_nullable value

select is_nullable,GROUP_CONCAT(column_name) column_list
from information_schema.columns
where table_schema = 'SchemaNameHere'
and   table_name = 'TableNameHere'
group by is_nullable;

How to tell if a table has changed:

run the following:

SELECT rows_changed
FROM information_schema.table_statistics
WHERE table_schema = 'mydb' AND table_name='mytable';

This returns a number that increases with each table update, keeping track of it will allow to to detect change.