Basic concepts of database
data
- The symbolic record that describes things is called Data
- Including numbers, words, graphics, images, sounds, archives, etc
- Store in a unified format in the form of "record"
surface
- Organize different records together to form a "table"
- It is used to store specific data
data base
- A database is a collection of tables and a warehouse for storing data
- Interrelated data stored in an organized way
History of database system development
First generation database
- Since 1960s, the first generation of database system has come out. They are database systems of hierarchical model and mesh model, which provide powerful support for unified management and data sharing
Second generation database
- In the early 1970s, the second generation of database relational database began to appear
- In the early 1980s, IBM's relational database system DB2 came out. As the second generation of relational database system, it began to gradually replace the hierarchical and mesh model database, becoming the dominant database and the mainstream of the industry. So far, relational database system still occupies the main position of database application
Third generation database
- Since the 1980s, a variety of new database systems adapted to different fields have emerged, such as engineering database, multimedia database, graphic database, intelligent database, distributed database and object-oriented database, etc., especially object-oriented database system, which is favored by people because of its strong practicability and wide adaptability
- In the late 1990s, a variety of database systems supported the application together. Of course, in business applications, relational database is still the mainstream, but some new elements have been added to the mainstream business database system. For example, the relational object database model supported by Oracle
Introduction to today's mainstream databases
SQL Server (Microsoft products)
- For Windows operating system
- Simple and easy to use
Oracle (Oracle products)
- For all mainstream platforms
- Safe, perfect and complex operation
DB2 (IBM products)
- For all mainstream platforms
- Large, safe and perfect
MySQL (acquired by Oracle)
- Free, open source, small size
relational database
- Relational database system is a database system based on relational model. Its basic concept comes from relational model
- The relational model is based on the theory of relational algebra. The data structure is represented by a simple two-dimensional data table and a simple entity relationship diagram
- The following E-R diagram contains three elements: entity (data object), relationship and attribute
entity
- Also known as an example, it corresponds to "events" or "things" that can be distinguished from other objects in the real world, such as bank customers, bank accounts, etc
attribute
- An entity has a certain property. An entity can have multiple attributes. For example, each entity in the bank customer entity set has attributes such as name, address, telephone, etc
contact
- The correspondence between entity sets is called relation, also called relation. For example, there is a "savings" relationship between a bank customer and a bank account
The collection of all entities and their relationships constitutes a relational database
The storage structure of relational database is two-dimensional table, and the data reflecting things and their relations are saved in the form of table
In each two-dimensional table, each row is called a record, which is used to describe the information of an object; each column is called a field, which is used to describe an attribute of an object
Application of relational database
Relational database
- Oracle，MySQL
- SQLServer,Sybase
- Informix,access
- DB2,FoxPRO
Application example of relational database
- 12306 user information system
- Taobao account information system, Alipay account system, mobile, telecom, Unicom mobile phone signal information system, accounting system, bank account system
- Website user information system
Introduction to non relational database
- Non relational database is also called NoSQL (Not Only SQL). The data stored is not based on the relational model and does not need a fixed table format
- As a supplement of relational database, non relational database is playing an important role in high efficiency and high performance in the network era
- Advantages of non relational database
- The requirement of high concurrent read and write in database
- Efficient storage and access to massive data
- The requirement of high expansibility and high availability of database
Non relational database storage
- Key value method: store, delete and modify data based on key
- Column oriented, which stores related data in the column family
- In document mode, the database consists of a series of data items, each of which has a name and corresponding value
- Graphic mode, entity as vertex, relationship as edge, data saved as a graphic
Non relational database products
-
Memcached is an open-source, high-performance cache system with distributed memory objects, which stores data in key value mode
- Cache data to reduce database pressure and speed up access
- Accelerate dynamic Web applications
- Cached content is stored in memory
- redis also stores data in key value mode. The data is also saved in memory, but it will write data to disk regularly
- Compared with FMemcached, it has the following characteristics
- Memory cache support
- Support persistence
- More data types
- Support cluster and distributed
- Support queue
Example of redis application
- Database front end cache
- session sharing
- When you need to cache more data types than key/value
- When cached data needs to be stored for a long time
Introduction to MySQL database
- MySQL is a popular open source relational database
- Oracle products
- It complies with the GPL protocol and can be used and modified free of charge
- Characteristic
- Excellent performance and stable service
- Open source, no copyright restrictions, low cost
- Multi thread, multi-user
- Based on C/S (client / server) architecture
- Safe and reliable
MySQL business and community
- MySQL Business Edition is developed and maintained by MySQL AB company, and can only be used for a fee
- MySQL Community Edition is developed and maintained by MySQL developers and enthusiasts all over the world. It can be used for free
- Difference between them
- The organization, management and testing of commercial version are more strict and stable
- Commercial version does not comply with GPL
- The commercial version can obtain 7 * 24-hour service, such as fault maintenance and patching, etc
MySQL product camp
- The first camp: 5.0-5.1 camp, which can be said to be the continuation of early products
- The second camp: 5.4-5.7 camp, which better integrates the storage engines of MySQL AB company, community and third-party companies, so as to improve performance
- The third camp: the 6.0-7.1 camp, the MySQL Cluster version, is developed to meet the needs of the database in the new era
- MySQL download website
MySQL installation instance
- Open the Linux system in the VMware virtual machine, download the MySQL compressed package to the host computer, and set the folder share to save the MySQL compressed package, which is convenient for the Linux system to mount and use Download MySQL 5.7
[root@localhost ~]# Yum install GCC gcc-c + + make ncurses ncurses devel bison cmake - Y / / install the environment package //Loaded plug-ins: faststmirror, langpacks base | 3.6 kB 00:00:00 extras | 2.9 kB 00:00:00 ... //Installed: bison.x86_64 0:3.0.4-2.el7 cmake.x86_64 0:2.8.12.2-2.el7 gcc.x86_64 0:4.8.5-39.el7 gcc-c++.x86_64 0:4.8.5-39.el7 ncurses-devel.x86_64 0:5.9-14.20130511.el7_4 //Installed as a dependency: cpp.x86_64 0:4.8.5-39.el7 glibc-devel.x86_64 0:2.17-292.el7 glibc-headers.x86_64 0:2.17-292.el7 kernel-headers.x86_64 0:3.10.0-1062.4.3.el7 libmpc.x86_64 0:1.0.1-3.el7 libstdc++-devel.x86_64 0:4.8.5-39.el7 m4.x86_64 0:1.4.16-10.el7 //Update completed: make.x86_64 1:3.82-24.el7 ncurses.x86_64 0:5.9-14.20130511.el7_4 //Upgraded as a dependency: glibc.x86_64 0:2.17-292.el7 glibc-common.x86_64 0:2.17-292.el7 libgcc.x86_64 0:4.8.5-39.el7 libgomp.x86_64 0:4.8.5-39.el7 libstdc++.x86_64 0:4.8.5-39.el7 ncurses-base.noarch 0:5.9-14.20130511.el7_4 ncurses-libs.x86_64 0:5.9-14.20130511.el7_4 //Complete! [root@localhost ~]# useradd -s /sbin/nologin mysql / / create a MySQL program user [root@localhost ~]# mount.cifs //192.168.100.8/shares /mnt / / mount the MySQL compressed package path in the host to the Linux system Password for root@//192.168.100.8/shares: [root@localhost ~]# cd /mnt / / / enter the mount point directory [root@localhost mnt]# tar zxvf mysql-boost-5.7.20.tar.gz -C /opt / / extract the MySQL package to the opt directory ...... mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/extract_key.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/buckets.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/allocate.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/detail/util.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_map.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_set.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_set_fwd.hpp mysql-5.7.20/boost/boost_1_59_0/boost/unordered/unordered_map_fwd.hpp mysql-5.7.20/boost/boost_1_59_0/boost/timer.hpp [root@localhost mnt]# cd /opt/mysql-5.7.20/ [root@localhost mysql-5.7.20]# cmake \ / / configure mysql > -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ //Specify installation path > -DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \ //Specify mysql.sock. Path > -DSYSCONFDIR=/etc \ //Specify the location of the configuration file > -DSYSTEMD_PID_DIR=/usr/local/mysql \ //Specify pid file as location > -DDEFAULT_CHARSET=utf8 \ //Character set format utf-8 > -DDEFAULT_COLLATION=utf8_general_ci \ > -DWITH_INNOBASE_STORAGE_ENGINE=1 \ //Turn on the storage engine > -DWITH_ARCHIVE_STORAGE_ENGINE=1 \ > -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ > -DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \ > -DMYSQL_DATADIR=/usr/local/mysql/data \ //Specify data storage location > -DWITH_BOOST=boost \ //Association supports c + + runtime > -DWITH_SYSTEMD=1 //Turn on systemd .... -- CMAKE_C_LINK_FLAGS: -- CMAKE_CXX_LINK_FLAGS: -- CMAKE_C_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -- CMAKE_CXX_FLAGS_RELWITHDEBINFO: -O3 -g -fabi-version=2 -fno-omit-frame-pointer -fno-strict-aliasing -DDBUG_OFF -- Configuring done -- Generating done -- Build files have been written to: /opt/mysql-5.7.20 [root@localhost mysql-5.7.20]# Make / / make process (long time, wait patiently) .... [100%] Building CXX object libmysqld/examples/CMakeFiles/mysql_embedded.dir/__/__/client/readline.cc.o Linking CXX executable mysql_embedded [100%] Built target mysql_embedded Scanning dependencies of target mysqltest_embedded [100%] Building CXX object libmysqld/examples/CMakeFiles/mysqltest_embedded.dir/__/__/client/mysqltest.cc.o Linking CXX executable mysqltest_embedded [100%] Built target mysqltest_embedded Scanning dependencies of target my_safe_process [100%] Building CXX object mysql-test/lib/My/SafeProcess/CMakeFiles/my_safe_process.dir/safe_process.cc.o Linking CXX executable my_safe_process [100%] Built target my_safe_process [root@localhost mysql-5.7.20]# make install / / install ... -- Installing: /usr/local/mysql/./COPYING-test -- Installing: /usr/local/mysql/./README-test -- Up-to-date: /usr/local/mysql/mysql-test/mtr -- Up-to-date: /usr/local/mysql/mysql-test/mysql-test-run -- Installing: /usr/local/mysql/mysql-test/lib/My/SafeProcess/my_safe_process -- Up-to-date: /usr/local/mysql/mysql-test/lib/My/SafeProcess/my_safe_process -- Installing: /usr/local/mysql/mysql-test/lib/My/SafeProcess/Base.pm -- Installing: /usr/local/mysql/support-files/mysqld_multi.server -- Installing: /usr/local/mysql/support-files/mysql-log-rotate -- Installing: /usr/local/mysql/support-files/magic -- Installing: /usr/local/mysql/share/aclocal/mysql.m4 -- Installing: /usr/local/mysql/support-files/mysql.server [root@localhost mysql-5.7.20]# cd /usr/local / / / enter the installation directory [root@localhost local]# Chown - R mysql.mysql MySQL / / MySQL directory change users and groups [root@localhost local]# mv /etc/my.cnf /etc/my.cnf.bak / / change the profile name [root@localhost local]# vim /etc/my.cnf / / re edit the configuration file [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 :wq [root@localhost local]# vim /etc/profile ... PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH export PATH //Redeclare environment variables :wq [root@localhost local]# source /etc/profile / / re execute [root@localhost local]# cd /usr/local/mysql/bin / / enter MySQL command directory [root@localhost bin]# Mysqld -- initialize execute -- user = MySQL -- basedir = / usr / local / MySQL -- dataDir = / usr / local / MySQL / data / / initialize the database 2019-12-02T11:51:28.112082Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details). 2019-12-02T11:51:28.263398Z 0 [Warning] InnoDB: New log files created, LSN=45790 2019-12-02T11:51:28.287408Z 0 [Warning] InnoDB: Creating foreign key constraint system tables. 2019-12-02T11:51:28.341284Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 131f32df-14fa-11ea-87e6-000c297265cb. 2019-12-02T11:51:28.342106Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened. 2019-12-02T11:51:28.342452Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option. [root@localhost ~]# cp /usr/local/mysql/usr/lib/systemd/system/mysqld.service /lib/systemd/system/ //Copy MySQL startup script driver to system directory [root@localhost ~]# systemctl enable mysqld.service / / set MySQL to start from scratch Created symlink from /etc/systemd/system/multi-user.target.wants/mysqld.service to /usr/lib/systemd/system/mysqld.service. [root@localhost ~]# systemctl start mysqld.service / / start MySQL [root@localhost ~]# mysqladmin -u root -p password / / set MySQL database password Enter password: //No original password, enter directly New password: //Enter new password Confirm new password: //Enter password again Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety. //Safety tips [root@localhost ~]# mysql -u root -p / / log in to MySQL Enter password: Input password Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.7.20 Source distribution Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> show databases; //view the database +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec)