Skip to content
Author: Tianle Yuan

** Basic Management**⚓︎

Glance of contents⚓︎

To better review SQL management pipeline, I sort the famouse "verbs" in the sequence from 0 - 1:

Create Table⚓︎

Create Table

The CREATE TABLE statement is used to create a new table in a database.

Syntax
SQL
CREATE TABLE table_name (
    column1 datatype;
    column2 datatype;
    column3 datatype;
    ....
);

Then, you will get a table like below:

PersonID LastName FirstName Address City
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) );

Insert Into⚓︎

Insert Into

The INSERT INTO statement is used to insert new records in a table.

Syntax
SQL
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

After the example below, you will get a table like:

PersonID LastName FirstName Address City
123 Tom Skage OR Albany
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'Skage', 'OR', 'Albany');

Select⚓︎

Select

The SELECT statement is used to select data from a database

Syntax
SQL
SELECT column1, column2, ...
FROM table_name;
SQL
SELECT * FROM table_name; -- select all the fields available in the table
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'Skage', 'OR', 'Albany'); SELECT * FROM Marks_family; SELECT PersonID, City FROM Marks_family;

Where⚓︎

Where

The WHERE clause is used to filter records. It is used in statements: SELECT, UPDATE, DELETE, etc.

Syntax
SQL
SELECT column1, column2, ...
FROM table_name
WHERE condition;
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Yong', 'C', 'NC', 'Euguene'); SELECT * FROM Marks_family WHERE City='Albany';

Update⚓︎

Update

The UPDATE statement is used to modify the existing records in a table.

Syntax
SQL
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition; -- if omit this line, all records in the table will be updated
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Yong', 'C', 'NC', 'Euguene'); UPDATE Marks_family SET LastName = 'Changed', FirstName = 'Haha' WHERE PersonID ='123'; -- Out put and check SELECT * FROM Marks_family;

Delete⚓︎

Delete

The DELETE statement is used to delete existing records in a table.

Syntax
SQL
DELETE FROM table_name 
WHERE condition;  -- if omit this line, all records in the table will be deleted
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), --hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (123, 'Tom', 'A', 'OR', 'Albany'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (124, 'Kim', 'B', 'TX', 'Salem'); INSERT INTO Marks_family (PersonID, LastName, FirstName, Address, City) VALUES (125, 'Yong', 'C', 'NC', 'Euguene'); DELETE FROM Marks_family WHERE PersonID = '124'; -- Out put and check SELECT * FROM Marks_family;

Drop Table⚓︎

Drop Table

The DROP TABLE statement is used to drop an existing table in a database.

Syntax
SQL
DROP TABLE table_name;
click to see an example

CREATE TABLE Marks_family ( PersonID int, LastName varchar(255), //hold characters FirstName varchar(255), Address varchar(255), City varchar(255) ); DROP TABLE Marks_family;

Note!

We can also use TRUNCATE TABLE to delete the data inside a table; instead of deleting the table itself.

References:⚓︎

Last update: April 4, 2023 08:21:07
Created: March 21, 2023 00:28:52