MySQL database structure and operation

1. Database introduction
?1.1 What is a database
As we have seen on the surface, as the name implies, it is used to store data

You can use files to store data, why do you need a database?

File saving data has the following disadvantages:

file security issues
Files are not conducive to data query and management
Files are bad for storing massive amounts of data
It is inconvenient to control files in the program
In order to solve the above problems, experts have designed software that is more conducive to data management—database, which can manage data more effectively. The database can provide remote services, that is, use the database through a remote connection, so it is also called a database server.

?1.2 Database classification
Databases can be roughly divided into relational databases and non-relational databases

Relational database (RDBMS):

A database that uses a relational model to organize data. To put it simply, the relational model refers to the two-dimensional table model, and a relational database is a data organization composed of two-dimensional tables and the connections between them.

Based on standard SQL, only some internal implementations are different. Commonly used relational databases such as:

1. Oracle: Oracle products are suitable for large-scale projects and complex business logic, such as ERP, OA and other enterprise information systems. TOLL. ,The most powerful database in the world, with extremely high requirements on data security and high fees
2. MySQL: belongs to Oracle, not suitable for complex business. Open source is free, so it is very popular.
3. SQL Server: Microsoft’s product, installed and deployed on windows server, suitable for medium and large projects. TOLL.

Typical feature: use “data table” format to organize data

Non-relational database:

(Understanding) does not prescribe SQL-based implementations. Now it refers more to NoSQL databases, such as:

1. Based on key-value pairs (Key-Value): such as memcached, redis
2. Document-based: such as mongodb
3. Based on column family: such as hbase
4. Based on graphics: such as neo4j

So Nosql does not use data tables to organize data

The difference between relational database and non-relational database:

Note: OLTP (On-Line Transaction Processing) refers to online transaction processing, and OLAP (On-Line Analytical Processing) refers to online analytical processing.

?2. The structure of MySQL
?2.1 MySQL server and client
MySQL is a program with a “client-server” structure

Client: The active party is the client
Server: The passive party is the server
A server may provide services to multiple clients at the same time

For example, examples in our life:

We went to the restaurant for dinner, and we asked the boss for a rice bowl with stir-fried shredded pork with green peppers. After a while, the boss brought out a bowl of delicious rice bowls

At this point, we can substitute the nature of this event into the database:

The data sent by the client to the server is called a “request” (a serving of green pepper fried shredded pork with rice)
The data returned by the server to the client is called “response” (a bowl of delicious rice bowl is served)

The MySQL we installed has both a client and a server

MySQL client (default command line client):

In addition, there are some third-party clients (some with a graphical interface)

The MySQL client and MySQL server can be on the same host or on different hosts.

In actual work, it is more common that the client and the server are on different hosts, and in our study, the client and the server are more on the same host (although they are the same host, they still communicate through the network )

For MySQL, storing and managing data is the responsibility of the MySQL server! !

Therefore, the MySQL server is much more complicated than the MySQL client. It can even be considered that the MySQL server is the ontology of MySQL. Most of the time when talking about MySQL, it refers to the MySQL server.

?2.2 How does the MySQL server organize data
A MySQL server can contain multiple “databases”, and the “database” here is actually a “data collection”, which contains some related data

Take Niuke.com as an example:

Question bank: previous years’ real questions from various companies, online programming, special exercises
Interview: various engineer face-to-face experience and detailed topic summary
Job hunting: school recruitment, social recruitment, intern recruitment

It is not necessary to arrange a host for each of the above items, a host, and a MySQL server can be managed, but in order not to let these data affect each other, they are logically separated and placed in different databases

There are many data tables in each database (data collection)
For example, the question bank table: pass rate, leaderboard…

There are many rows in a table, each row is called “a record”
Each row also has many columns, each column represents a different meaning, each column, also known as a “field” (field)

So the synthesis is: database –> data table –> row –> column

Not only MySQL organizes data like this, but as long as it is a relational database, it organizes data according to the above structure

?3. Database operation
?3.1 Create database
create database database name;

Example:

Create a database called java

When we see the above, the database is successfully created

Many people will wonder what 0.00 sec is

sec == “second second

Representing time also reflects the operating efficiency of the computer. In other words, why do non-relational databases exist? Because non-relational databases have fewer constraints, are more efficient, and are more suitable for the current popular “distributed systems”

Notice:

create, database are both keywords in SQL, you can’t misspelling, there are spaces in the middle of the word (one or more will do, usually one)
Keywords are case insensitive
Don’t forget the semicolon at the end, it’s an English semicolon
Note: When we create a database without specifying a character set and verification rules, the system uses the default character set: utf8, and the verification rules are: utf8_ general_ ci

The character set describes what kind of characters are supported by the stored data, and the verification rules describe how to compare the current characters (comparison of string size, equality relationship, generally default rules, rarely manual intervention)

?3.2 Display the current database
show databases;

We can see that in addition to the database java created by ourselves, there are also some databases that come with the system

Notice:

Here databases is a plural number, and the output is a lot of databases, don’t forget to add s
There will be many databases that come with the system in the print database

?3.3 Using the database
use database name;

In this way, we have selected the database java to complete

?3.4 Delete database
drop database database name;

Example:

delete the database named java

This completes the deletion of our database, let’s print it out to verify it

At this point, you can see that the java database has indeed been deleted.
illustrate:

After the database is deleted, the corresponding database cannot be seen internally, and all the tables and data in it are deleted
Never delete the database at will! ! ! (Especially do not delete on the company’s production environment server)
Here we expand on how to avoid the harm caused by accidentally deleting the database or minimize the loss?

Permissions: Set a few people to have permission to modify and delete the database
Backup: make a copy of the data and store it in another place
Hard disk data recovery: Since MySQL stores data on the hard disk, it is possible to restore the data on the hard disk if the data in the database is deleted
Here we explain in detail how to recover hard disk data:

For the convenience of management, the operating system divides the entire hard disk into several “disk blocks”, each of which can store certain data, so in fact each file may be composed of data on one or more disk blocks

When the operating system deletes a file, in order to improve the efficiency of the deletion, it does not really erase the previously saved data on the hard disk, but only marks the disk block corresponding to the file as “invalid state”. Therefore, once the database is deleted by mistake, the host must be powered off as soon as possible, so as to prevent the operating system from allocating these marked as invalid disk blocks! ! ! (It may not be 100% recoverable, but there is a high probability that only a part of it can be recovered)

?4. Commonly used data types
MySQL is a relational database. Its typical feature is to organize data in the form of tables. The table is characterized by “special neatness”. Each row and column number are the same, and each column and data are of the same type ), on the one hand, the data type can facilitate us to process the data, on the other hand, it can also check and check the data.

?4.1 Numerical types
Divided into integer and floating point types:

extension:

The numeric type can be specified as unsigned (unsigned), which means that negative numbers are not taken.
1 byte (bytes) = 8bit.
For ranges of integral types:

1. Signed range: -2^(number of bytes of type 8-1) to 2^(number of bytes of type 8-1)-1, if int is 4 bytes, that is -2^31 to 2^31- 1
2. Unsigned range: 0 to 2 ^ (number of type bytes * 8) -1, such as int is 2 ^ 32 – 1
Try not to use unsigned. For data that may not fit in the int type, int unsigned may also not fit in the data. Instead of this, it is better to upgrade the int type to the bigint type during design.

Compared with FLOAT and DOUBLE, DECIMAL can represent decimals more accurately. DECIMAL is equivalent to representing floating-point numbers by means of strings. The advantage is that it can be accurately represented and calculated accurately; but the disadvantage is that it consumes more and stores more space during calculation. Consider using DECIMAL unless there is a special need

?4.2 String type

extension:

VARCHAR (SIZE) is the most commonly used type

For example: varchar(50), this field can store up to 50 characters (not bytes), and it may be shorter. Dynamically adapt to the space according to the length of the stored data. (One byte is fixed at 8 bits, and one character is not. It depends on the specific character encoding. Unicode encoding is used by default in Java, and utf8 is not the same, and there are countless relationships behind it)

65535 bytes -> extension: integer represented by one byte: -128 => 127 –> 0 => 256; integer represented by two bytes: -32768 => 32767 –> 0 => 65535; four Integer represented by bytes: -2.1 billion => 2.1 billion -> 0 => 4.29 billion

The above three stores text data, and BLOB stores binary data. (Text data: the data stored in it is all ASCLL characters, binary data: any data is possible)

?4.3 date type

TIMESTAMP timestamp: take January 1, 1970 at 0:00:00 as the reference time, and calculate the difference in seconds between the current time and the reference time
You can search for timestamps in Baidu, the maximum value is 2.1 billion, and now it has reached 1.6 billion. Once the limit is reached, many codes will become invalid. It is not recommended to use DATATIME.

Summarize the commonly used types: int, bigint, double, decimal, varchar, datetime.

?5. Table operation
?5.1 Create table
create table table name(column name type, column name type…);

Unlike common programming languages, in SQL, the column name is written first, and the type is written after. C++ and Java both write the type first, and the variable name is written last, but like Go and Python, the type is also written after the variable name. of
To create a table, you need to have a database first, and select it

We have created a Java database above, just select the Java database
mysql> use java;
Database changed
create a student table with id and name

mysql> create table student(id int, name varchar(20));
Query OK, 0 rows affected (0.02 sec)

When creating a table, you must clarify the table structure, that is, which columns are there, what type is each column, and what is its name

Notice:

In the same database, there cannot be two tables with the same name
When creating a table, the table name or column name cannot conflict with the keywords of SQL. If you must use it, you need to enclose the table name with backticks`

expand:

When we sometimes create a table statement, it may be more complicated and longer, so it can be written in multiple lines

mysql> create table test(
-> id int,
-> name varchar(20));
Query OK, 0 rows affected (0.01 sec)

But the experience is not very good, multi-line editing, once you press Enter, you can never go back, for example:

mysql> create table test2(
-> id int,
-> name varchar(20),
->

When we realize that there are only two types, when the third carriage return is opened, there is no way to go back

Solution:

You can write the SQL in other editors, and then copy and paste it! ! !
It can also be Notepad, other text editors, etc. The most important thing is to write the SQL statement, and then copy and paste it.

Copy and paste it into our command line

So the test2 table is created (in fact, add a ; to the bad table to exit, and the above operation is actually the same)

5.2 Check which tables are there
On the premise of selecting the database, use show tables; to see which tables are in the current database

mysql> show tables;
+—————-+
| Tables_in_java |
+—————-+
| student |
| test |
| test2 |
+—————-+
3 rows in set (0.00 sec)

5.3 View table structure
desc table name;

View the columns and types in this table

mysql> desc student;
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+——-+————-+——+—–+———+——-+
2 rows in set (0.00 sec)

Each column is a field, Field, indicating how many columns there are in the table
int(11) does not mean occupying 11 bits, int is a fixed 4 bytes, occupying 32 bits, 11 means that when printing numbers, the maximum width of the displayed data is 11 digits, 11 only affects the The display in the client does not affect the maximum storage and calculation of data
When varchar(20) restricts storage, it can store up to 20 characters
Null indicates whether this column can be empty (not filled, optional)
Key will be mentioned later
Default is the default value. Null here means that the default default value is Null (not filled), and the default value can also be manually modified.
Extra default value, which will be explained in detail later

?5.4 Delete table
drop table;

Example operation:

First print all the tables that exist, and then delete test2 and test respectively, you can see that the last print is indeed not there, it is deleted

Notice:

Deleting tables is very dangerous! ! ! The harm is only a lot more than deleting the library! ! !
On the surface, a library contains many tables, and deleting the library will directly take away all the tables (if the library is really deleted, as long as the program is running, as long as it involves database operations, 100% of the errors will be reported!!! The problem can be found in a short time, but if you delete a table, 100 tables, and delete one of the tables, when the program is running at this time, you may not necessarily report an error at the first time!!! It is very likely that the program is “injured” Run”, although the program can run, but the result is wrong, its problems cannot be exposed at the first time)

?5.5 Create database table
Create goods and assign some attributes (product number, name, unit price in cents, category, supplier name)
mysql> create table goods (
-> goods_id int,
-> goods_name varchar(20),
-> unitprice int,
-> category varchar(20),
-> provider varchar(20)
-> );

The numbers in parentheses can be written casually when we are in the learning stage, as long as it is enough
In actual development, it should be determined according to product requirements

Table creation and query: