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;
IF NOT EXISTSmakes the statement safe to re-run. Without it, a second run fails with ERROR 1007: Can't create database 'library_db'; database exists. With it, MySQL only records a note (code 1007) that you can see withSHOW WARNINGS.utf8mb4stores any Unicode text, including Hindi, Tamil and emoji. It is already the default in MySQL 8, but writing it explicitly protects you on servers configured differently.utf8mb4_0900_ai_ciis the MySQL 8 default collation: accent-insensitive (ai) and case-insensitive (ci), so'Kumar' = 'kumar'is true in comparisons.
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_id | title | author | copies |
|---|---|---|---|
| 1 | Let Us C | Yashavant Kanetkar | 5 |
| 2 | Database System Concepts | Silberschatz, Korth, Sudarshan | 3 |
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
- Open your connection and click the Create a new schema icon (a cylinder with a plus sign) in the toolbar.
- Type the name, pick
utf8mb4and the collation, and click Apply. - Workbench shows the generated
CREATE SCHEMASQL; click Apply again, then Finish. - 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
- Hyphens and spaces.
CREATE DATABASE my-project;fails with a syntax error (1064) because-is read as minus. Use underscores:my_project. Backticks (`my-project`) work, but then every query needs them. - Upper-case names. On Linux, database and table names are case-sensitive by default (they map to directories); on Windows they are not. A project built on a Windows laptop that uses
Library_DBin one file andlibrary_dbin another breaks when deployed to a Linux server. Stick to lowercase. - Reserved words such as
orderorgroupas names. They need backticks everywhere; pick another name. - Old
utf8. In MySQL it means the 3-byteutf8mb3, which cannot store emoji. Always useutf8mb4.