Description | MySQL | PostgreSQL |
---|---|---|
Connect to database server | mysql -u username -p | psql -U username |
Import a database | mysql -u username -p databaseName < data.sql | psql -U username databaseName < data.sql |
List databases | SHOW databases; | \l |
Use/Connect to datbase with name | USE databaseName; | \c databaseName |
List tables | SHOW tables; | \dt |
List users | SELECT user, host FROM mysql.user; | \du |
Create a user | CREATE USER username IDENTIFIED BY 'password'; | CREATE ROLE username WITH database LOGIN [PASSWORD 'password'] |
Change password | ALTER USER 'username'@'localhost' IDENTIFIED BY 'newPassword'; FLUSH PRIVILEGES; | ALTER ROLE username WITH PASSWORD 'newPassword' VALID UNTIL 'infinity'; |
Grant user access to database | GRANT ALL PRIVILEGES ON database.* TO username@localhost; | GRANT ALL PRIVILEGES ON DATABASE database TO username; |
Show permissions | SHOW GRANTS FOR 'username'@'localhost'; | |
Allow connections from any IP address | 'username'@'%' | |
Change host for database user | UPDATE mysql.user SET Host='%' WHERE Host='localhost' AND User='username'; UPDATE mysql.db SET Host='%' WHERE Host='localhost' AND User='username'; FLUSH PRIVILEGES; |
|
List table engine | SELECT table_name, table_schema, engine FROM information_schema.tables WHERE table_schema = 'yourDatabaseName'; | |
Shrink size of table | OPTIMIZE TABLE 'tableName'; | VACUUM FULL 'tableName' |
Execute SQL from command line | mysql -u username -p dbname --execute='SHOW TABLES'; | |
Restart SQL server (Ubuntu) | sudo service mysql restart | |
List if field contains numeric | SELECT * FROM tableName WHERE columnName REGEXP '^[0-9]+$'; | |
Display number of connections | SHOW STATUS WHERE variable_name = 'Threads_connected'; |
Last active
January 11, 2022 06:42
-
-
Save michael-milette/526639334895d2d2c46f4df8db76e8ea to your computer and use it in GitHub Desktop.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment