[SQL]: Insert and Retrieve Data

Insert (into table)

INSERT INTO dogs(name, age)
VALUES ('Buzz', 2);

• dogs is a table name
• You can change the order of columns, but order of values must follow along
• Parameter for dogs can be omitted but orders in value parameter have to follow the order of columns when it was created
• Examples below (both are allowed)

INSERT INTO dogs(age, name)
VALUES(3, 'lucky');

INSERT INTO dogs VALUES ("Joy", 2);

Check inserted rows

SELECT * FROM dogs;

• Output:

+-------+------+
| name  | age  |
+-------+------+
| buzz  |    2 |
| lucky |    3 |
| Joy   |    2 |
+-------+------+
3 rows in set (0.00 sec)

Multiple Insert

INSERT INTO dogs
VALUES ('lucky', 10),
       ('joy', 2),
       ('happy', 1);

Sidenote

• Inserting a string(VARCHAR) that contains quotations
  • Escape the quotes with backslash:
  TEXT

  Copy

  "This has \"quotes" in it"

  • Use single quote inside double quotes or vice versa
  TEXT

  Copy

  "This has 'quotes' in it"
    <!-- or -->
    'This has "quotes" in it'

Deleting data

DELETE FROM dogs
WHERE name="luCkY";

DELETE FROM dogs
WHERE age=2;

• name is case Insensitive; luCkY will remove lucky
• Deleting age=2 will affect both rows, so table will be empty after two queries
  • Deletion with age="2" is also allowed although age column is defined as INT