【WEEK5】 【DAY3】Introduction to Databases【English Version】

2024.3.27 Wednesday

1. What is a database, and why study databases

1.1. Why study databases

1. Job skill requirements
2. In today’s world, those who control data control the world
3. Methods of storing data
4. How to preserve vast amounts of data in programs and websites for a long time?
5. The database is almost the most core existence in the software system. DBA (database administrator)

1.2. What is a database

1.2.1. Database (DataBase, abbreviated as DB)

1.2.2. Concept: A collection of large amounts of data that is organized and shareable, stored long-term in a computer, and is a data “warehouse”

1.2.3. Function: Save, and securely manage data (such as: addition, deletion, modification, querying, etc.), reduce redundancy…

1.2.4. Overview of databases:

1.2.4.1. Relational Databases (SQL)

Classics include: MySQL, Oracle, SQL Server, SQLite, DB2, etc.
Relational databases establish relationships between tables through foreign keys, such as: student information tables, attendance tables

1.2.4.2. Non-relational Databases (NOSQL) i.e., not only sql

Classics include: Redis, MongoDB, etc.
Non-relational databases typically refer to data stored in the form of objects within the database, with the relationships between objects determined by each object’s own attributes

1.3. Database Management System (DBMS)

Database management software, scientifically organize and store data, efficiently retrieve and maintain data

1.4. Introduction to MySQL

1.4.1. Concept: A popular open-source, free relational database

1.4.2. History: Developed by the Swedish company MySQL AB, now a product of Oracle Corporation.

1.4.3. Features:

1. Free, open-source database
2. Compact, fully featured
3. Convenient to use
4. Can run on Windows or Linux operating systems
5. Suitable for small to medium-sized and even large-scale web applications

1.4.4. Official website: https://www.mysql.com/

1.5. Installation of MySQL

1.6. Installation and Connection of Navicat

1.7. Operations with Navicat premium

1.7.1. Query history: ctrl+L

1.7.2. Create a new table: Right-click on “Tables” to add, Navicat’s column names are in ‘Field’-‘Name’

Change table name: https://blog.csdn.net/2301_76297780/article/details/135304914

1.7.3. View tables, add records

Right-click on a specific table and click “Open Table”, enter the desired content in the blank area

Operate on a table: Click on that table and select the specific operation purpose, such as: Open Table (open a table filled with specific content), Design Table (modify row and column content), Create Table…

1.8. Connecting to the Database

1.8.1. Command-line connection

mysql -u root -proot
or mysql -u root -p
After “Enter password:” appears, type root (the login password of the local machine)
https://blog.csdn.net/qq_41918166/article/details/109455467

1.8.2. View all databases show databases;

All statements must end with a semicolon

1.8.3. Switch to database “school” use school

1.8.4. View all tables in the database show tables;

1.8.5. Display all information in the table describe student;

1.8.6. Create a new table create database westos;

Check it out:

Refresh to display:

1.9. Several basic database operation commands:

update user set password=password('123456') where user='root'; -- Change password
flush privileges; -- Refresh database
show databases; -- Show all databases
use dbname; -- Open a certain database
show tables; -- Display all tables in the mysql database
describe user; -- Display column information of the user table in mysql database
create database name; -- Create a database
use databasename; -- Select a database

exit; -- Exit MySQL
? Command keyword: Seeking help
-- Represents a comment (single line)
/* Multi-line comment
Write whatever you want
*/