Database Overview

• Throughout human evolution, numbers, text, and symbols were created to record data. However, as cognitive and creative abilities improved, the volume of data increased, making it a significant challenge to record and accurately retrieve data.
• With the advent of computers, data began to be stored and calculated within computers, leading to the development of database systems.
• Database systems address issues such as persistent storage, optimized read/write operations, and ensuring data validity.
• Currently used databases are mainly divided into two categories:
  • Document-based, such as SQLite, which is a single file, with database replication achieved by copying the file.
  • Server-based, such as MySQL and PostgreSQL, where data is stored in a physical file but requires terminal access via TCP/IP protocol for reading and writing.

E-R Model

• Current physical databases are designed based on the E-R model.
• E stands for entity, R represents relationship.
• An entity is converted into a table in the database.
• Relationships describe the correspondence rules between two entities, including:
  • One-to-one
  • One-to-many
  • Many-to-many
• Relationships are converted into a column in the database table * in relational databases, each row represents an object.

Third Normal Form

• After research and summarizing problems encountered during use, some specifications were proposed for designing databases, known as normal forms.
• First Normal Form (1NF): Columns cannot be split.
• Second Normal Form (2NF): Unique identifier.
• Third Normal Form (3NF): Reference primary key.
• Note: Each subsequent normal form is built upon the previous one.

Installation

• Install

sudo apt-get install mysql-server mysql-client
Then follow the prompts to enter

Managing the Service

• Start

service mysql start

• Stop

service mysql stop

• Restart

service mysql restart

Allowing Remote Connections

• Find the MySQL configuration file and modify it

sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Comment out bind-address=127.0.0.1

• Log in to MySQL and run the following command

grant all privileges on *.* to 'root'@'%' identified by 'mysql' with grant option;
flush privileges;

• Restart MySQL

Data Integrity

• A database is a complete business unit that can contain multiple tables, with data stored in these tables.
• To store data more accurately and ensure its correctness, you can add some mandatory validations when creating a table, including field types and constraints.

Field Types

• MySQL contains many data types, here are some commonly used ones:
• Numbers: int, decimal
• Strings: varchar, text
• Dates: datetime
• Booleans: bit

Constraints

• Primary key
• Not null
• Unique
• Default
• Foreign key

Using a Graphical Interface to Connect

• Download Navicat for Windows.

• Click "Connection" to open a window and fill in the connection details as shown in the figure.

• After a successful connection, the current database will appear under the connection name.

• Double-click the database to edit it.

• When you return to the software next time, double-click to complete the connection and editing operations.

Database Operations

• Right-click on the connection name and select "New Database" to open a window and follow the instructions to fill in.

• Right-click on the database and select "Delete Database" to perform the deletion operation.

Table Operations

• When the database is highlighted, it indicates that the current operation is being performed on this database. You can create a table in the database.

• An entity corresponds to a table, used to store specific structured data.

• Click "New Table", open a window, and fill in the information as instructed.

• The primary key name is usually id, set to int type, unsigned, auto-increment, not null.

• Auto-increment means the value of this field is managed by the MySQL system, and there is no need to manually maintain it, so you don't have to worry about the specific value of this field.

• The varchar type string needs to set the length, i.e., the maximum number of characters it can contain.

• Click "Add Field" to add a new field.

• Click "Save" to define the table name.

Data Operations

• After the table is successfully created, you can see it on the right side. Double-click the table to open a new window, as shown in the figure.

• In this window, you can add, modify, or delete data.

Logical Deletion

• For important data, physical deletion is not desired, as once deleted, the data cannot be recovered.
• Usually, for important data, a column called isDelete of type bit is set to indicate logical deletion.
• For large volumes of non-critical data, physical deletion can be performed.
• The importance of data should be determined based on actual development.

Command Line Connection

• Command line operations are more commonly used in work, so proficiency is required.
• Open the terminal and run the command

mysql -uroot -p
After pressing Enter, enter the password, which is currently set to mysql.

• After a successsful connection, the screen looks like the figure below.

• Exit login

quit or exit

• After a successful exit, the screen looks like the figure below.

• After logging in successfully, enter the following commands to view the results

Check version: select version();
Display current time: select now();

• Note: End statements with a semicolon;

Remote Connection

• In company development, it is common to host the database on a single server, shared by all developers rather than configuring a database on their own computer.
• Run the commend

mysql -hip address -uroot -p

• The -h flag is followed by the IP address of the host to connect to.
• The -u flag is followed by the username for the connection.
• After -p, enter the password.

Database Operations

• Create a database

create database database name charset=utf8;

• Delete a database

drop database database name;

• Switch databases

use database name;

• View the currently selected database

select database();

Show all databases

show databases;

Table Operations

• View all tables in the current database

show tables;

• Create a table
• auto_increment indicates auto-increment

create table table name(column and type);
For example:
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);

Modify table

alter table table name add|change|drop column name type;

Add a field to the table

alter table student add birthday datetime;

Change the name and type of the field (change the gender field to bit type)

alter table student change geneder gender bit;

Modify only the field type (change the gender attribute to int type)

alter table student modify gender int;

• Delete a table

drop table table name;

• View table structure

desc table name;

• Rename a table

rename table old table name to new table name;

• View the creation statement of the table

show create table 'table name';

Data Operations

• Query

select * from table name

• Insert

Full column insertion: insert into table name values(...)
Partial insertion: insert into table name(columns) values(values)
Insert multiple rows at once: insert into table name values(...),(...)...;
or insert into table name(columns) values(values),(...)...;

• The primary key column is auto-incremented, but it needs to be included in full column insertion, typically using 0. After successful insertion, the actual data will be used.
• Update

update table name set column1=value1,... where condition

• Delete

delete from table name where condition

• Logical deletion is essentially an update operation

alter table students add isdelete bit default 0;
If deletion is needed, then
update students set isdelete=1 where ...;

Backup and Restore

Data Backup

• Enter super administrator

sudo -s

• Enter the MySQL library directory

cd /var/lib/mysql

• Run the mysqldump command

mysqldump --uroot --p database name > ~/Desktop/backup file.sql;
Enter the MySQL password as prompted

Data Recovery

• Connect to MySQL and create a database.
• Exit the connection and execute the following command

mysql -uroot --p database name < ~/Desktop/backup file.sql
Enter the MySQL password as prompted

Summary

• Problems solved by databases, E-R model, third normal form
• Graphical interface operations for databases, tables, and data
• Command-line operations for databases, tables, and data