preface

Data storage mode

What is a database

Database is an effective technology of data management. It is an ordered collection composed of a batch of data, which is stored in a structured data table. Data tables are related to each other, reflecting the essential relationship between objective things. Database can effectively help an organization or enterprise manage all kinds of information resources scientifically.

Data is the basic object stored in the database. It is a physical symbol arranged and combined in a certain order. Data can take many forms, be it numbers, words, images, or even audio or video, which can be digitized and stored in a computer.

A database is a collection of data, which has a unified structure and is stored in a unified storage medium. It is the integration of various application data and can be shared by various applications.

In daily life, people can directly describe objective things in Chinese, English and other natural languages. In computers, features of interest are abstracted and described in a record.

For example, in the student files, the student information is composed of student number, name, gender, age, native place, contact number and other characteristics, then a record formed by these specific characteristic values is a student’s information data, such as “2016010102, Zhang SAN, male, 26, Shanxi, Computer College, 185********”.

It is worth noting that the descriptive form of data cannot fully express its content and needs to be interpreted. For example, for the student record above, those who understand its meaning will get the following information: Zhang SAN’s student id is 2016010102. He is 26 years old and from Shanxi Province. He is studying in the Computer College. Those who do not understand the semantics cannot understand the meaning. Therefore, data and the interpretation of data are inseparable. The interpretation of data refers to the explanation of the meaning of data, which is also called the semantics of data. Therefore, data is inseparable from its semantics, and data without semantics is meaningless and incomplete.

The storage structure of a database

Physical data Description

Physical data description refers to how data is stored on a storage device. Physical data is actually stored on a storage device. Physical data is also called physical records. According to the location of physical record storage, it can be divided into ordered storage and unordered storage.

In physical data description, data description terms used include the following. Bit: A binary unit called a bit, which can only be 1 or 0. Byte: Eight bits are called a byte and can hold a character corresponding to the ASCII code. Word: a word composed of several bytes. The number of bits in binary a word contains is called the word length. Many computers have different word lengths. For example, a computer’s word length can be 8 bits, 16 bits, 24 bits, 32 bits, etc. Block: The smallest unit of information exchanged between internal and external memory. It is also called a physical block or physical record. The size of each block is usually 256 bytes, 512 bytes, 1024 bytes, etc. Volume: All the useful information that can be loaded by an I/O device. For example, a tape of a tape drive is a volume, and a disk group of a disk device is also a volume. Unordered storage: Data records are stored in the order they were inserted.

Logical data Description

The role of databases in development

From the perspective of database system application, the common operation and application structure of database system are: client/server structure, browser/server structure.

In the Client/Server (C/S) structure, database users (such as DBA, programmer) through the command line Client, graphical interface management tools or applications to connect to the database management system, database management system can query and process all kinds of data stored in the underlying database.

The database consumer interacts directly with command line clients, graphical interface management tools, or applications rather than directly with the database management system.

In this structure, the command line client, graphical interface management tools or applications are called “client” or “foreground”, mainly to complete the interaction tasks with database users; And database management system is called “server” or “background”, mainly responsible for data management. This structure is often referred to as a “C/S” structure.

In client/server mode, the client and server can work on the same computer at the same time. This mode is called “single-machine mode”. It can also run in network mode, that is, the server is installed and deployed on one or more hosts on the network.

For the development of client applications, the language tools are mainly Visual C++, Delphi,.net framework, Visual Basic, Python and so on.

The database can effectively store data, read data, find data is more convenient, in fact, the management software is through the software interface for internal database data add, delete, change, check operation.

Database creation

MySQL character set and character collation rules

Before we create the database, we will talk about the character set and character collation of the database

The character set specifies the encoding format of the data to be stored. A rule for comparing stored data in a specified encoding format, such as case – insensitive: Character set Common character sets are UTF8 and UTF8MB4. In MySQL, UTF8 supports a maximum of 3 bytes of data, and UTF8MB4 supports a maximum of 4 bytes of data. Expression data on mobile terminals is a four-byte character, so utF8MB4 is required for proper storage. Character sorting rules The common character sorting rules are UTF_bin and UTF_general_CI. Bin is binary. A and A are treated differently. For example you run:

select * from table where a = 'a';Copy the code

You can’t find a line in UTf8_bin where a = ‘a’, and utf8_general_ci can be utf8_general_ci faster but less accurate.

So when we create the database, we will set the character set to UTF8MB4 and the character collation to UTF8_general_CI

grammar

CREATEDATABASE Specifies the DATABASE name.Copy the code

Example Create database db_name

//Example Create database db_nameCREATE DATABASE db_name;
//Create database A whose character set is UTF8MB4 and collation is UTF8_general_CICREATE DATABASE A DEFAULT CHARACTER SETUtf8mb4COLLATE utf8_general_ci;//Create database B whose character set is UTF8 and collation is UTF8_general_CICREATE DATABASE B DEFAULT CHARACTER SET utf8 COLLATEUtf8_general_ci;//View the database character setshow variables like '%char%';
Copy the code

Create db_name database in navicat

Deleting a Database

grammar

dropDatabase Specifies the database name.Copy the code

example

Example Delete database db_name

drop database db_name;
Copy the code

Delete XXX database from navicat

Select database

A database and B data are created on our locally installed database software, and they both have A table A, so when we query the data of table A

select * from table whereA;Copy the code

Software can not know which database you need to query at this time, so we need to select the database before querying the database

grammar

Use database name;Copy the code

example

Select DATABASE A

use A;
Copy the code

The data type

Binary type

The bit field type has only true (1) and false (0) values, which are used to hold true and false, or to represent gender male and female

Numeric types

Date and time types

String type

The corresponding type in navicat

Create table

Delete table

Insert data

Query data