Database learning record 01

Database learning records

The location and status of the database

Database type

Introduction to Database

SQL is a standard computer language used to access and process databases .

SQL (Structured Query Language: Structured Query Language) is used to manage relational database management systems (RDBMS). The scope of SQL includes data insertion, query, update, and deletion, database schema creation and modification, and data access control.

Relational databases include:MySQL, SQL Server, Access, Oracle, Sybase, DB2, etc. These databases can be written in SQL language;

Non-relational databases: Redise, Hbase, NOSQL, etc. These databases are written in SQL-like languages;

  • SQL executes queries against the database
  • SQL can retrieve data from the database
  • SQL can insert new records in the database
  • SQL can update the data in the database
  • SQL can delete records from the database
  • SQL can create a new database
  • SQL can create new tables in the database
  • SQL can create stored procedures in the database
  • SQL can create views in the database
  • SQL can set permissions for tables, stored procedures, and views

Mysql

MySQL is the most popular relational database management system. In terms of web applications, MySQL is one of the best RDBMS (Relational Database Management System) application software.

What is a database?

Database is a warehouse that organizes, stores and manages data according to data structure.

Each database has one or more different APIs for creating, accessing, managing, searching and copying the saved data.

We can also store data in files, but the speed of reading and writing data in files is relatively slow.

Therefore, now we use relational database management systems (RDBMS) to store and manage large amounts of data. The so-called relational database is a database built on the basis of a relational model, which uses mathematical concepts and methods such as set algebra to process data in the database.

RDBMS is the characteristics of relational database management system (Relational Database Management System):

  • 1. The data appears in the form of a table
  • 2. Each line of various record names
  • 3. Each column is the data field corresponding to the record name
  • 4. Many rows and columns form a form
  • 5. Several forms form a database

Commonly used storage units for computers:

8 bit = 1 Byte (Byte)

1024 B = 1 KB (KiloByte) kilobytes

1024 KB = 1 MB(MegaByte)

1024 MB = 1 GB(GigaByte)

1024 GB = 1 TB (TeraByte) Terabyte

1024 TB = 1 PB (PetaByte)

1024 PB = 1 EB (ExaByte) Exabyte

1024 EB = 1 ZB (ZetaByte)

1024 ZB = 1 YB (YottaByte)

1024 YB = 1 BB (Brontobyte) Per byte

1024 BB = 1 NB (NonaByte)

1024 NB = 1 DB (DoggaByte)

MySQL database

MySQL is a relational database management system developed by the Swedish company MySQL AB and currently belongs to the Oracle company. MySQL is a relational database management system. The relational database stores data in different tables instead of putting all the data in a large warehouse, which increases speed and flexibility.

  • MySQL is open source and is currently part of Oracle's products.
  • MySQL supports large databases. It can handle large databases with tens of millions of records.
  • MySQL uses a standard SQL data language format.
  • MySQL can run on multiple systems and supports multiple languages. These programming languages ​​include C, C++, Python, Java, Perl, PHP, Eiffel, Ruby, Tcl, etc.
  • MySQL has very good support for PHP, which is currently the most popular web development language.
  • MySQL supports large databases, a data warehouse with 50 million records, a 32-bit system table file can support a maximum of 4GB, and a 64-bit system supports a maximum table file of 8TB.
  • MySQL can be customized, using the GPL agreement, you can modify the source code to develop your own MySQL system.

RDBMS terminology

Before we start learning MySQL database, let us first understand some RDBMS terms:

  • Database: A database is a collection of related tables.
  • Data table: A table is a matrix of data. A table in a database looks like a simple spreadsheet.
  • Column: A column (data element) contains the same type of data, such as postal code data.
  • Row: A row (= tuple, or record) is a group of related data, such as a piece of data subscribed by a user.
  • Redundancy : Store twice the data. Redundancy reduces performance but improves data security.
  • Primary key : The primary key is unique. A data table can only contain one primary key. You can use the primary key to query data.
  • Foreign key: A foreign key is used to associate two tables.
  • Composite key : A composite key (composite key) uses multiple columns as an index key, and is generally used for a composite index.
  • Index: Use the index to quickly access specific information in the database table. An index is a structure for sorting the values ​​of one or more columns in a database table. Similar to a catalog of books.
  • Referential integrity: Referential integrity requires that non-existent entities are not allowed in the relationship. Integrity with entity is the integrity constraint condition that the relational model must meet, and the purpose is to ensure the consistency of the data.

MySQL is a relational database (Relational Database Management System). This so-called "relational" can be understood as the concept of "table". A relational database consists of one or several tables, as shown in the figure:

  • Header: the name of each column;
  • Column (col): a collection of data with the same data type;
  • Row: Each row is used to describe the specific information of a record;
  • Value (value): the specific information of the row, each value must be the same as the data type of the column;
  • Key : The value of the key is unique in the current column.

Mysql installation and use

installation

1. Open phpstudy and start Apache and Mysql services;

2. Download the phpMyAdmin graphical management tool; ---Right click to manage

Mysql web management tool

Visit: http://localhost/phpMyAdmin4.8.5/

Database file location

D:\phpstudy_pro\Extensions\MySQL5.7.26\data

Mysql command line management

The mysql command cannot be found by default;

In the directory of Mysql, you can find commands by typing MySQL;

D:\phpstudy_pro\Extensions\MySQL5.7.26\bin

If you want to recognize Mysql in any location, we need to add environment variables;

Steps to add environment variables

1. Copy the absolute path of D:\phpstudy_pro\Extensions\MySQL5.7.26\bin

2. Open the settings and find the system---

3. Click About---find advanced system settings

4. Find environment variables

Method Two

1. Right click on my computer and click on properties;

2. Find the advanced system settings-environment variables

3. Find the location of Path

4. Create a new environment variable path

Note: How to add environment variables in the previous version

Final verification

Save the added environment variables, then reopen a win + R to open CMD, enter MySQL in any location, and an interface will appear, proving that the environment variables have been added successfully;

Verify login