One, the basic concept of the database
Simple Application Server
USD1.00 New User Coupon
* Only 3,000 coupons available.
* Each new user can only get one coupon(except users from distributors).
* The coupon is valid for 30 days from the date of receipt.
1.1 Basic concepts of database-1
■ Data
Symbolic records describing things
Including numbers, text, graphics, images, sounds, file records, etc.
Store in a unified format in the form of "record"
■ Table
Organize different records together
Used to store specific data
■ Database
A collection of tables is a warehouse for storing data
A collection of related data stored in a certain organization
Insert picture description here
1.2 Basic concepts of database-2
■ Database Management System (DBMS)
It is a system software that realizes the organization, management and access of database resources
■ Database system
It is a man-machine system consisting of hardware, OS, database, DBMS, application software and database application
Users can operate the database through-DBMS or applications
Insert picture description here
2. History of database system development
■ The first generation database
Since the 1960s, the first generation of database systems came out
It is a database system of hierarchical model and network model
Provides strong support for unified management and sharing of data
■ The second generation database
At the beginning of the 1970s, the second-generation database-the series database began to appear
In the early 1980s, the relational database system DB2 of the old M company came out, and began to gradually replace the database of the hierarchical and mesh model, becoming the mainstream of the industry
So far, relational database systems still occupy the main position of database applications
■ The third generation database
Since the 1980s, new database systems adapting to different fields have emerged continuously. •Object-oriented database system with strong practicability and wide adaptability
In the late 1990s, a situation in which multiple database systems jointly supported applications was formed
Some new elements have been added to the mainstream database system
For example, the "relation-object" database model supported by Oracle
3. Introduction to mainstream databases
3.1 Introduction to current mainstream databases
■ SQL Server (product of Microsoft Corporation)
For Windows operating system
Simple and easy to use
■ Oracle (Oracle company product)
For all Guiliu platforms
Safe, perfect, complicated operation
■ DB2 0BM company product) • For all mainstream platforms
Large, safe and complete
■ MySQL (acquired by Oracle) • Free, open source, small size
Insert picture description here
3.2 Relational Database-1
■ Relational database system is a database system based on relational model
■ The data structure of the relational model uses a simple and easy to understand two-dimensional data table
■ The relational model can be represented by a simple "Entity-Relationship" (E-R) diagram
■ The E-R diagram contains three elements: entity (data object), relationship and attribute
Insert picture description here
3.3 Relational database-2
■ Entity
Also called an instance, it corresponds to an "event" or "thing" that can be distinguished from other objects in the real world
Such as bank, bank account, etc.
■ Properties
A certain characteristic of an entity, an entity can have multiple attributes
For example, each entity in the "bank customer" entity set has attributes such as name, address, phone number, etc.
■ Contact
Correspondence between entity sets is called connection, also called relationship
For example, there is a "savings" relationship between bank customers and bank accounts
■ The collection of all entities and their connections constitutes a relational database
3.4 Relational Database-3
■ The storage structure of the relational database is a two-dimensional table
■ In each two-dimensional table
Each row is called a record, used to describe the information of an object
Each column is called a field, used to describe an attribute of the object
Insert picture description here
3.5 Relational database applications
■ Relational database
Oracle, MySQL
SQLServer, Sybase
Informix, access • DB2, FoxPRO
■ Application examples
12306 User Information System • Taobao Account System
Unicom mobile phone number information system
Bank user account system
Website User Information System
3.6 Introduction to non-relational databases
■ Non-relational database is also called NoSQL (Not Only SQL)
■ Stored data is not based on the relational model and does not require a fixed table format
■ Advantages of non-relational databases
The database can be read and written with high concurrent
Efficient storage and access to massive data
Database has high scalability and high availability
■Commonly used non-relational databases: Redis, mongoDB, etc.
3.7 Introduction to MySQL database
■ A popular open source relational database
■ Oracle products
■ Comply with GPL agreement, free to use and modify
■ Features
Excellent performance and stable service
Open source, no copyright restrictions, low cost
Multi-threaded, multi-user
Based on C/S (client/server) architecture
Safe and reliable
3.8 MySQL Business Edition and Community Edition
■ MySQL Business Edition is developed and maintained by MySQL AB, and you need to pay to use it
■ MySQL Community Edition is developed and maintained by MySQL developers and enthusiasts scattered all over the world, and can be used for free
■ The difference between the two
The commercial version has stricter organization management and testing, and will be more stable than the community version
The commercial version does not comply with the GPL, and the community version is free to use in compliance with the GPL
The commercial version can get 7*24 hours of service, the community version does not
3.9 MySQL product camp
■ The first camp: 5.0-5.1 camp, can be said to be the continuation of early products
■ The second camp: The 5.4-5.7 camp integrates storage engines developed by MySQL AB, the community, and third-party companies to improve performance
■ The third camp: 6.0-7.1 camp, which is the MySQL Cluster version, developed to meet the needs of the new era for database cluster
■Download URL
http://www.dev.mysql.com/downloads
Fourth, MySQL build operation
#########Install mysql######
(1) Install the mysql environment dependency package
[root@localhost ]#
yum -y install \
gcc-c++ \
gcc \
make \
ncurses \
ncurses-devel \
bison \
cmake
(2) Create a running user
[root@localhost ]# useradd -s /sbin/nologin mysql
1
(3), compile and install
###Upload mysql-boost-5.7.20.tar.gz to the opt directory ###
[root@localhost ]# cd /opt
[root@localhost opt ]# tar xf mysql-boost-5.7.20.tar.gz
[root@localhost mysql-5.7.20]# cd /opt/mysql-5.7.20/
cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1
[root@localhost mysql-5.7.20]# make -j3 && make install
(4) Adjust the permissions of the database directory
[root@localhost mysql-5.7.20]# chown -R mysql:mysql /usr/local/mysql/
1
(5), establish adjustment configuration file
[root@localhost mysql-5.7.20]# vi /etc/my.cnf
[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES
[root@localhost mysql-5.7.20]# chown mysql:mysql /etc/my.cnf
(6) Set environment variables
[root@localhost mysql-5.7.20]# echo'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# echo'export PATH' >> /etc/profile
[root@localhost mysql-5.7.20]# source /etc/profile
(7) Initialize the database
[root@localhost mysql-5.7.20]# cd /usr/local/mysql/
[root@localhost mysql]#
bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data
[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
(8) The database is started, closed, and status
[root@localhost mysql]# systemctl enable mysqld
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# systemctl status mysqld
[root@localhost mysql]# cd /usr/local/bin/ ###Go to the bin directory and set the password
(9), set Mysql password
[root@localhost bin]# mysqladmin -u root -p password ###Enter and press Enter, then enter the password abc123, confirm here abc123, this is running under the root account
(10) Log in to the database
[root@localhost bin]# mysql -u root -p ###Knock this command and you are prompted to enter a password, this is the password abc123 just set
mysql> CREATE DATABASE myadm;
mysql> GRANT all ON myadm.* TO'myadm'@'%' IDENTIFIED BY'admin123';
mysql> GRANT all ON myadm.* TO'myadm'@'%' IDE
NTIFIED BY'admin123';
mysql> flush privileges;
(11), then use Navicat for MySQL software to connect to the mysql database
