Database Introduction

What is a database

Official definition:

Database is organized, described and stored data according to a certain data model, with small redundancy, high data independence and scalability, and as a data set shared by various users

However, I prefer my own understanding:

A database, as its name suggests, is a warehouse of data

In fact, there are many related concepts, for example…

Let’s say you have a factory that produces TV sets, and you need a warehouse to store them, and you need a warehouse manager for Uncle Wang…

So why do you need a warehouse?

  1. Storage of television sets for statistics and management (Data management)
  2. Find a Mr. Wang to prevent TV theft (Data persistence)

The TV here is DATA.

The repository for the TV set is the DB.

Uncle Wang here is a DBA.

You ask :” Lao Wang, how many TV sets have we got in stock today?”

Uncle Wang said: “wait a minute, mister, I use management software to check…”

The management software here is DBMS(database management system), common DMS: MySQL, Oracle, SQL Server

There is a formula:

DBS = DBA manages the DB using the DBMS

Database system is the database administrator to use database management system to manage the database

What is data persistence

In simple terms, it is to find a way not to lose data.

You might wonder, what data is stored in a computer and can be lost?

The answer is: maybe yes, maybe no… Where do you store it

There are two concepts that need to be introduced here: memory, hard disk

Memory features, fast speed, very fast, power data loss

Hard disk features, large capacity, very large, power off data is not lost

To put it simply, memory is small and fast, and hard disk is slow and large. The price of a 4000GB (4TB) hard disk is approximately equal to the price of 16GB of memory, 4000:16

So, to persist data, you have to store it on hard disk

If you are using a DATABASE management system, it will do the data persistence automatically

What is a mysql

The most famous database management system DBMS is the management software used by Lao Wang in front

Of course, DBMS is not only MySQL, why MySQL can stand out in many DBMS?

  1. Small size, fast operation
  2. Cost-effective, because it is free…
  3. Facilitate the transplantation of various operating systems
  4. Good support for SQL statements
  5. Very fast

Mysql installation

Here only the Windows system of the compression package installation method, because this way is the simplest, the most popular

MySQL5.7官网 download address

Cdn.mysql.com//Downloads/…

MySQL installation

1. Unpack the

Unzip the package to D:\test_mysql_57.

2. Add environment variables

Add the path to the bin directory () to the environment variable, as this folder contains msyql.exe(command line tool-client) and msyqlD.exe (server)

Click OK to close all the popovers

3. Configuration file

Create configuration file: D:\test_mysql_57\my.ini

Create the data store folder: D:\test_mysql_57\data

The following

[client]
port=3306 # Default port number
[mysql]
default-character-set=utf8 # Default character set

[mysqld]
port=3306
basedir="D:/test_mysql_57/" # mysql path
datadir="D:/test_mysql_57/data/" Mysql data path
character-set-server=utf8 # Default character set
default-storage-engine=innodb The default database engine
Copy the code
4. Install the mysql service using the CLI

Windows +X open command line (administrator),

Enter the command: mysqld install connection name –defaults-file=” configuration file path”

mysqld install mysql3306 --defaults-file="D:\test_mysql_57\my.ini" 
Copy the code

5. Initialize mysql

Enter the following command

mysqld  --initialize
Copy the code

6. Start the mysql

Enter the following command

net start mysql3306
Copy the code
7. Reset the password

Find the default password from the file

D:\test_mysql_57\data\USER-20170622RQ.err

Log in to the mysql database using the default password and reset the password

mysql -h localhost -u root -p
Copy the code

SET PASSWORD = PASSWORD('Your new password');
ALTER USER 'root'@'localhost' PASSWORD EXPIRE NEVER;
flush privileges;
Copy the code

8. Use the new password, log in again, and have fun typing commands

Start/close/restart mysql

Net start mysql;

Net stop mysql;

In Windows, you cannot restart the vm directly. You can only stop the VM and then start it.

Basic Concepts of Database

The database

A repository for data, so it’s essentially a container

The data table

Columns and rows form a two-dimensional table to store data

field

A column is called a field

record

A row is called a record

data

Each cell stores one data

conclusion

A database has tables, and tables have rows and columns, and rows store data, and columns describe data

Table relationships

One-on-one (1:1)

User table and user details table

User Details table, whose fields include: name, gender, age, height, weight, native place and place of residence, etc

One-to-many/many-to-one (1:N/N:1)

Country table, city table

Many-to-many (N, N)

Student schedule, class schedule

Intermediate tables are required to establish relationships

paradigm

Introduction to the

What is a paradigm

Design specifications for data table compliance

What’s the point?

Is to solve the problem of data storage and optimization

The characteristics of

  • Not a must
  • More and more strict
  • Backwards compatible

content

  • Each column (each field) in the data table must be the smallest unit that cannot be split, that is, to ensure atomicity of each column
  • If 1NF is met, all columns in the table must depend on the primary key, and no column must depend on the primary key
  • You must first satisfy the second normal Form (2NF), which requires that each column in the table is only directly, not indirectly, related to the primary key

talk like a human being

  • Each field stores only one data
  • A list only does one thing
  • Do not add fields that do not belong to this table

The inverse of standardization

Redundancy and efficiency

Conforming to the paradigm, the data is not easily repeatable, but is cumbersome to query

Buy more hard drives for speed…

Quick jump

  • [MySQL Light Speed Introduction]000 introduction & directory
  • MySQL > create database, create table, add data