====== MySQL Basics ======

==== Data Definition Language (DDL) ====

^^^
|  CREATE  |  create new objects in db  |
|  ALTER  |  alters / change the structure of the db  |
|  DROP  |  delete objects from the db  |

==== Data Manipulation Language (DML) ====

^^^
|  SELECT  |  retrieve data from db  |
|  INSERT  |  insert data into a table  |
|  UPDATE  |  updates existing data in a table  |
|  DELETE  |  deletes all records from a table, space for the records remain  |

===== Basics =====
^  Code  ^  Function  ^
|  CREATE TABLE Personal (Lastname CHAR(50) Firstname CHAR(50) Rank VARCHAR(30) Salary (INT));  |  Create table  |
|  INSERT INTO Personal (Lastname, Firstname) VALUES ('Doe', 'John')  |  Insert in table  |
|  UPDATE Personal SET Salary=3200 WHERE Lastname = "Doe";  |  Change the salary of a person with the lastname Doe  |
|  DELETE FROM Personal WHERE Lastname = "Doe" AND Firstname = "John";  |  Remove John Doe  |
|  DROP TABLE Personal;  |  Remove table  |
|  ALTER TABLE Personal ADD Gender CHAR (1);  |  Add a new column Gender  |
|  SELECT Lastname, Firstname FROM Personal ORDER BY Lastname;  |  Order the output by lastname  |
|  SELECT Rank FROM Personal GROUP BY Rank;  |  Shows the ranks grouped  |
|  SELECT COUNT(*) FROM table;  |  Counts all entries  |
|  SELECT * FROM table WHERE field LIKE '%text%';  |  Select all columns for a field with the value "text"  |
|  SELECT a.name, a.age, b.address FROM table1 a, table2 b WHERE a.id = b.id;  |  You can use this instead of inner join  |


===== Code examples =====
^  Code  ^  Function  ^
|  SELECT * FROM Table WHERE Cell LIKE 'test'  |  select all rows in Table where Cell contains 'test'  |
|  SELECT IdX FROM Table WHERE Cell LIKE '$Var'  |  select IdX from Table where Cell contains variable Var  |
|  SELECT * FROM Table ORDER BY IdX DESC LIMIT 0,10  |  select all rows limited by 10, order everything by IdX and from newest top to oldest bottom  |
|  SELECT * FROM Table WHERE Cell LIKE '%$Var%'  |  select all rows from table where cell is almost like variable Var  |
|  UPDATE `DB`.`Table` SET `Cell1` = '$Var1' WHERE `Table`.`Cell2`='$Var2'  |  update Table Cell2 to Var2 where Cell1 is Var1  |
|  INSERT INTO Table (Cell1, Cell2) VALUES ('Text', '$Var1')  |  add new row into Table with cell1 as Text and Cell2 as Var1  |

\\
\\
~~DISCUSSION:closed~~