SQL Magic

Get all column names of a database table

SELECT COLUMN_NAME 
	FROM INFORMATION_SCHEMA.COLUMNS 
		WHERE 
    TABLE_SCHEMA = 'schema_name'
		AND TABLE_NAME = 'table_name'

or

SELECT *
	FROM TABLE
	WHERE 1=2

Get total number of tables in a database schema

SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'database_name';
-- so something like:
SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'blog';
-- to see all the tables as a list, run:
show tables;

Access MySQL shell from the terminal

mysql -h HOSTNAME -P PORT_NUMBER -u USERNAME -p
# example input being:
mysql -h 127.0.0.1 -P 3306 -u test_user -p
# then enter password as prompted

List all the foreign key constraints in a database

SELECT RefCons.constraint_schema,
		RefCons.table_name,
		RefCons.referenced_table_name,
		RefCons.constraint_name,
		KeyCol.column_name
FROM information_schema.referential_constraints as RefCons
JOIN information_schema.key_column_usage KeyCol ON RefCons.constraint_schema = KeyCol.table_schema
		AND RefCons.table_name = KeyCol.table_name
		AND RefCons.constraint_name = KeyCol.constraint_name
WHERE RefCons.constraint_schema = 'DATABASE_NAME';

List all unique constraints on a table

SELECT CONSTRAINT_NAME
FROM information_schema.TABLE_CONSTRAINTS
WHERE table_name = 'content_model' AND constraint_type = 'UNIQUE';

Drop and then re-add an altered foreign key index

-- drop an index
ALTER TABLE post_comment
	 DROP CONSTRAINT post_comment_post_id_foreign;
	 
-- re-add (with alterations)
ALTER TABLE post_comment
ADD CONSTRAINT post_comment_post_id_foreign
    FOREIGN KEY (post_id)
    REFERENCES posts
        (id)
    ON DELETE CASCADE ON UPDATE NO ACTION;

Using SQL to run Laravel migration schema dump

Sometimes needed if Laravel .env has the database provider listed as a clustered database. Don't know why, but the schema dump does not appear to run.

mysql -h db_host -u db_user -p -D database_name < ./database/schema/mysql-schema.dump
# So (run from the application's root directory, wherever you'd run Artisan commands from):
mysql -h 172.0.0.1 -u blog_user -p -D blog < ./database/schema/mysql-schema.dump

Query to get related records with same id in a given column

Consider this table: | student_id | class_id | | ---------- | -------- | | 1 | 4 | | 1 | 6 | | 1 | 1 | | 1 | 6 | | 1 | 2 | | 2 | 3 | | 2 | 2 | | 2 | 4 |

How can you get the class_ids given a single class_id?

SELECT class_students1.class_id 
	FROM class_students AS class_students1 
	JOIN class_students AS class_students2 ON class_students1.student_id = class_students2.student_id 
			WHERE class_students2.class_id = 2;
-- returns this table:
| student_id |
|------------|
| 4          |
| 6          |
| 1          |
| 6          |
| 2          |

We join the table with itself, matching the student_ids.