#1 India's Top IT Training Institute
×

About Uncodemy

Know more about who we are and what we stand for.

About Us

About Us

Discover our mission to equip individuals with essential IT skills and industry expertise.

Read More →
UnCodeMy Gallery

UnCodeMy Gallery

Explore highlights from our IT training sessions and success stories.

Read More →
Mentors

Mentors

Meet the industry experts who train and guide you through your course.

Read More →
Free Resources

Free Resources

Free tutorials, interview questions, tools and calculators to help you learn.

Explore →
×

Free Resources

Learn, practice and plan your career — bilkul free.

Tutorials

Tutorials

Step-by-step written tutorials to learn any technology at your own pace.

Read More →
Free Counselling

Free Counselling

Book a free video counselling session and pick the right course for you.

Read More →
Online Compiler

Online Compiler

Write, run and test your code online — no setup required.

Read More →
MySQL Tutorial · Data Analytics

How to Create a Database in MySQL (with Examples)

You are starting a library management mini project for your BCA or B.Tech semester. Before any table exists, you need a database to hold them. In MySQL a database (also called a schema; CREATE SCHEMA is an exact synonym) is a named container for tables, views, stored procedures and other objects. Creating one takes a single statement, but a few choices made at this moment, the name and the character set, are tedious to change later.

Step 1: connect to the server

Open a terminal (or the MySQL Command Line Client on Windows) and log in:

mysql -u root -p

If you prefer a GUI, MySQL Workbench works too; its steps are at the end of this page. If MySQL is not installed yet, follow installing MySQL on Windows or installing MySQL on Ubuntu first.

Step 2: create the database

CREATE DATABASE IF NOT EXISTS library_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_0900_ai_ci;

Step 3: confirm it exists

SHOW DATABASES LIKE 'library%';
Database (library%)
library_db
SHOW CREATE DATABASE library_db;
CREATE DATABASE `library_db` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */

The /*!40100 ... */ parts are version comments: MySQL runs the text inside only on server versions at or above the number (4.1.0 and 8.0.16 here). They appear in dumps too, so do not be surprised by them.

Step 4: select it and add a table

Creating a database does not switch you into it. Right after logging in without naming a database, SELECT DATABASE(); returns NULL, and a CREATE TABLE fails with ERROR 1046: No database selected. Select it first:

USE library_db;
SELECT DATABASE();
DATABASE()
library_db
CREATE TABLE books (
  book_id INT AUTO_INCREMENT PRIMARY KEY,
  title   VARCHAR(100) NOT NULL,
  author  VARCHAR(60),
  copies  INT DEFAULT 1
);
INSERT INTO books (title, author, copies) VALUES
  ('Let Us C', 'Yashavant Kanetkar', 5),
  ('Database System Concepts', 'Silberschatz, Korth, Sudarshan', 3);

SHOW TABLES;
SELECT * FROM books;
Tables_in_library_db
books
book_idtitleauthorcopies
1Let Us CYashavant Kanetkar5
2Database System ConceptsSilberschatz, Korth, Sudarshan3

You can also skip USE by qualifying names: SELECT * FROM library_db.books;. Designing tables properly (types, keys, constraints) is the next step: see how to create a table in MySQL.

Step 5: give the project its own user

Your project's code should not log in as root. Create a user that can work only inside this database:

CREATE USER 'library_app'@'localhost' IDENTIFIED BY 'Lib#2026pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON library_db.* TO 'library_app'@'localhost';

More on accounts and privileges in MySQL user management and privileges.

Changing or deleting a database

ALTER DATABASE library_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  -- default for NEW tables only
DROP DATABASE IF EXISTS library_db;                                         -- deletes everything inside

ALTER DATABASE changes the default used by tables created afterwards; existing tables keep their own character set until you ALTER TABLE ... CONVERT TO CHARACTER SET. DROP DATABASE removes every table and row in it immediately and cannot be rolled back. Take a backup first (mysqldump guide).

Using MySQL Workbench instead

  1. Open your connection and click the Create a new schema icon (a cylinder with a plus sign) in the toolbar.
  2. Type the name, pick utf8mb4 and the collation, and click Apply.
  3. Workbench shows the generated CREATE SCHEMA SQL; click Apply again, then Finish.
  4. Double-click the schema in the Navigator to make it the default (the same as USE).

A full tour is in the MySQL Workbench tutorial for beginners.

Naming rules and common mistakes

Become a Job-Ready Data Analyst

Master MySQL, Advanced Excel, Power BI, Tableau and Python with Uncodemy's Data Analytics Training in Noida — live projects, expert mentors and Dedicated Placement Support.

Enroll in Data Analytics Course →