SQL — Create, Update, Delete

  1. Data Manipulation Language (DML): is responsible for adding, deleting, updating, looking up data. A more common way to say it is Create, Read, Update, Delete (CRUD)
CREATE DATABASE [databaseName]
CHARACTER SET [unicode]
COLLATE [sort rule];
  • sort rule: COLLATE command followed by a sort rule, with different sort rules, words can be translated differently. It stores the unicode in a different order, when it gets translated, it will pick up the 1st result in that list of characters
CREATE TABLE [table_name](
[column1_name] [data_type] [not_null] [default],
[column2_name] [data_type] [not_null] [default],
[column3_name] [data_type] [not_null] [default],
PRIMARY KEY ([column_name])
);
  • Column name: if it's a foreign key then the naming convention should be tableName_columnName
  • Length: coming along with type, you can put a length to specify the length on the data type to avoid memory waste
  • Auto Increment: it will increase automatically, you don’t need to assign value to it. We often use it on id
  • Zerofill: fill in the empty value with zero
  • NOTNULL: can’t be null value
  • UNSINGED: can’t be negative value
INSERT INTO [table_name] ([column_name, column_name...])
VALUES('ABC', 12, 23)
UPDATE [table_name]
SET [value]
WHERE [which record]
DELETE FROM [table_name]
WHERE [which record]
ALTER TABLE [table_name]
ADD [column_name] [type]

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store