Note:

• All statements in SQL should end with a ;.
• Change *...* to your own version.

Start

See all databases

show databases;

Create a database

I name my database as zoo. Check if the database has been created successfully: show databases;

create database *database_name*;

- e.g.
create database zoo;

Select the database you want to use

use *database_name*;

See all tables in the selected database

Now the database has been created. Let's see what is in this database zoo.

show tables;

Create a table and add a few columns

As the image, there is nothing in this zoo. Let's create a table called zoo_keeper to store all the keepers' data.

create table *table_name* (
  *colums_name* *data_type*,
  *colums_name* *data_type*,
  *colums_name* *data_type*,
  *colums_name* *data_type*,
  ......
);

e.g.
create table zoo_keeper(
memberID int,
first_name varchar(255),
last_name varchar(255),
phone_number varchar(255)
);

create table animal(
id int,
name varchar(255),
birthday varchar(255),
age int
);

See the schema of the table

describe *table_name*;

e.g.
describe zoo_keeper;

Remove a database

If you don't want this database anymore, use the statement below.

Note: Removing a database means all data in it would be removed simultaneously. Always make sure to make a backup of those data before you drop the database.

drop database *database_name*;

CRUD

Insert some data into the table

This statement tells your database that you want to insert data into the specific table and what values you want to put into that table. Based on my schema of table zoo_keeper, data type of the first column is an integer, and the others are varchar(255).

insert into *table_name* values (*data and type based on the schema*);

e.g.
insert into zoo_keeper values (202210001, "Jennifer", "C. Soares", "347-923-9780");

insert into animal values (1, "elephant", "1998/02/07", 24);
insert into animal values (2, "elephant", "2000/03/04", 22);
insert into animal values (3, "elephant", "2021/10/08", 1);
insert into animal values (4, "elephant", "2022/06/04", 0);

Note: The zoo keepers' data is the fake data from FAKE NAME GENERATOR.

See all data in the table with select

This statement tells your database that you want to see all data in the specific table.

select * from *table_name*;

e.g.
select * from zoo_keeper;

Choose a specific column of data from the table

Get data from the table and only show the column you want to see.

select *column_name* from *table_name*

e.g.
select name from animal;

Use select + where to create multiple ways to filter data

If you have large data, using select and where to filter data would be much more efficient.

select *column_name* from *table_name* where *condition*;

• e.g. Get the data that the column age is less than 20 from the animal table.

  select * from animal where age < 20;
  

• e.g.

  select * from animal where name = "dolphin";
  

• e.g. Get the data only shows name column and conditions are name is "dolphin" or age is less than 20 from the animal table.

  select * from animal where name = "dolphin" or age < 20;
  

• e.g. Get the data only shows name & birthday column and condition is name is "dolphin" from the animal table.

  select name, birthday from animal where name = "elephant";
  

• e.g. Get the data only shows name & birthday column and conditions are name is "dolphin" and "moose" from the animal table.

  select name, birthday from animal where name = "elephant" or name = "moose";
  

Update data

Oops! I've inserted the zoo keeper Steven's phone number with the wrong one. How to fix it? Just use the statement with update. This statement tells the database to update which table and to put what data in which column. Also, the most important thing is to give the database a specific place to update data on this table.

update *table_name* set *column_name = __data after changing__* where *column_name = value*;

• e.g. Before updating

update zoo_keeper set phone_number = "336-221-1733" where first_name = "Steven";

After updating

Note: If using this statement without where *column_name = value*, the whole column in column_name would be updated with the same data. In my example, if I type update zoo_keeper set phone_number = "336-221-1733", I didn't tell the database where to update this phone number set, so all data in the phone number column has been changed to "336-221-1733".

Remove data from the table

Joseph quit his job and moved to another country, so we have to remove Joseph's data from zoo keeper table.

delete from *table_name* where *column_name = ___*;

e.g.
delete from zoo_keeper where first_name = "Joseph";

Ref: youtu.be/xiUTqnI6xk8