Often used MySql commands

• Create a database:
  # mysql>CREATE DATABASE name;
• Delete a database:
  # mysql>DROP DATABASE name;
• Create a table:
  mysql>CREATE TABLE name (primColName int AUTO_INCREMENT PRIMARY KEY NOT NULL, textColName char(30), numberColName int, moneyColName decimal, dateColName date);
• Delete a table:
  mysql>DROP TABLE name;
• Rename a table:
  mysql>ALTER TABLE name RENAME AS otherName;
• Delete all data from a table:
  mysql>DELETE FROM TABLE name;
• Delete some data from a table:
  mysql>DELETE * FROM TABLE name WHERE colName = 'something';
• Insert data into a table:
  mysql>INSERT INTO tableName (numberColName1, textColName2, dateColName3) VALUES (numberValue, 'textValue', 'dateValue');
• Alter data in a table:
  mysql>UPDATE TABLE name SET colName = newValue WHERE colName = otherValue;
• Select data in a table (NULL):
  mysql>SELECT * from table WHERE colName IS NULL;
• Select data in a table (NOT NULL):
  mysql>SELECT * from table WHERE colName IS NOT NULL;
• Select data in a table (with Joker):
  mysql>SELECT * from table WHERE colName LIKE '%searchString%';
• Add a column:
  mysql>ALTER TABLE name ADD colName char(10) AFTER colX;
  Or:
  ALTER TABLE name ADD colName char(10) FIRST;
• Remove a column:
  mysql>ALTER TABLE name DROP colName;
• Change a column type:
  mysql>ALTER TABLE name MODIFY colName INTEGER;
• Rename a column type:
  mysql>ALTER TABLE name CHANGE oldColName newColName INTEGER;
• Import data from a text file:
  For data import to an existing Database you need a file with data in ASCII delimited text-only format.
  mysql>LOAD DATA LOCAL INFILE 'path/to/import.txt' INTO TABLE name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
  Optionally you can add as last argument:
  IGNORE 1 LINES (headerColName1, headerColName2);
• Import data from a database dump file:
  First make sure that your database exists. If you want to replace the whole (old) database by the whole new one, first do a DROP DATABASE name, then a CREATE DATABASE name, then go back to the shell-prompt and type:
  $>mysql -u root -p databaseName < /home/myDir/mySikFileName.mysql;
  Next, grant the necessary rights as root to the user (see below).
• Save data (a whole database) to a dump file:
  $>mysqldump -u root -p --opt databaseName > /home/myDir/mySikFileName.mysql;
• Grant rights to a user:
  # mysql>GRANT ALL ON databaseName.* TO userName@localhost IDENTIFIED BY 'userPwd'
•