query
Work with mysql from command line
# mysql
mysql > show databases;
Create db:
CREATE DATABASE tutorial_database;
Create user:
CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123test!';
List user:
SELECT User,Host FROM mysql.user;
Grant ALL Permissions to a MySQL:
GRANT ALL ON *.* TO 'testuser'@'localhost';
Here is a short list of commonly used permissions :
Dumping your database data to a file
To dump the full data to a file use this command:
mysqldump --skip-extended-insert -u admin20154iwg -p developiwg > iwgit-data.sql
-u -> for username
-p -> to prompt the password
To dump the data without temporary data to a file use this command:
mysqldump --skip-extended-insert -u admin20154iwg -p --ignore-table=developiwg.cache% --ignore-table=developiwg.search% --ignore-table=developiwg.watchdog developiwg > iwgit-data.sql
Dumping your database schema without data to a file
From command line:
mysqldump --no-data --tables -u username -p dbname > outputfilename-schema.sql
Autoincrement value in MySQL
ALTER TABLE tbl AUTO_INCREMENT = 5;
Query from multiples columns and tables
SELECT DISTINCT a.entity_id, a.value, b.value
FROM Table_1 AS a
JOIN Table_2 AS b on a.entity_id = b.entity_id
WHERE a.attribute_id = x AND b.attribute_id = y