** 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
SQLCREATE 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
SQLINSERT 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
SQLSELECT column1, column2, ...
FROM table_name;
SQLSELECT * 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
SQLSELECT 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
SQLUPDATE 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
SQLDELETE 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
SQLDROP 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