SQL Fundamentals: Database and Table Creation
COMMENTS IN SQL:
● SINGLE LINE COMMENT: -- (Anything after -- on the same line is ignored.)
● MULTI-LINE COMMENT: /* ... / (Everything between / and */ is ignored.)
-- This is a single-line comment.
/*
This is a
multi-line
comment.
*/
DDL: Data Definition Language
DDL commands are used to define the structure of your database.
● CREATE: Used to create database objects (databases, tables, views, indexes, etc.).
Syntax :
CREATE DATABASE_OBJ database_obj_name;
Good Practice :
CREATE DATABASE_OBJ IF NOT EXISTS database_obj_name;
(This prevents errors if the database already exists.)
Example :
CREATE DATABASE database1;
Syntax for creating a table :
CREATE TABLE table_name (
column1 data_type,
column2 data_type,
...
columnN data_type
);
Create a table named "table1"
CREATE TABLE table1 (
id INT,
name VARCHAR(255),
date_created DATE
);
Example: Creating a "my_store" Database and "customers" Table
CREATE DATABASE my_store;
1. Verify the database creation:
SHOW DATABASES;
2. Use the "my_store" database:
USE my_store;
3. Create the "customers" table with columns: customer_id, customer_name, email
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(255),
email VARCHAR(255)
);
4. Verify the table creation:
(Show all tables in the current database)
SHOW TABLES;
Explanation:
● The CREATE DATABASE command creates a new database.
● SHOW DATABASES lists all databases on the MySQL workbench.
● USE database_name selects a database to work with.
● CREATE TABLE creates a new table within the selected database.
● INT, VARCHAR, and PRIMARY KEY are examples of data types and constraints used
when defining table columns.
● SHOW TABLES lists all tables in the currently selected database.