User Tools

Site Tools


webseite:mysql:basic

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



webseite/mysql/basic.txt · Last modified: 2018/12/20 17:40 by lunetikk