Creating a user,creating a database and connecting a database in Mysql
Last Updated On
Friday 29th Oct 2021
Create user
CREATE USER 'dbuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL ON *.* TO 'dbuser'@'localhost' WITH GRANT OPTION;
ALTER USER 'dbuser'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
Create database
- Grant permissions to ‘dbname’
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
Connecting to database
mysql://<username>:<password>@<host>:<port>/<db_name>
Show all foreign keys constraint on table
SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME = 'table' group by constraint_name;
Truncate table with foreign key
SET FOREIGN_KEY_CHECKS = 0;
TRUNCATE table1;
SET FOREIGN_KEY_CHECKS = 1;```