37, MySQL first knowledge

Before we wrote the code needed to access the information with the file but the file access data is very limited, today we will walk into a new world MySQL

First, the origin of the database

Previously learned, data to be permanently saved, such as user registration of user information, are stored in the file, and the file can only exist on a certain machine. If we do not consider the efficiency of reading data from a file, and assuming that all of our programs are running on a single machine, there is no problem with file access, but the question is: The execution of the program depends on the hardware that hosts it, and the performance of a machine machine is always limited, Vertical scaling is limited by the current level of hardware. So we can only increase the overall performance of our system through horizontal scaling, which requires us to distribute the various components of the program across multiple machines to execute. It is important to note that although we distribute the various components of the program to each machine, the components remain a whole, with the implication that the data for all components is shared. But the components on each machine can only operate the native files, which leads to inconsistent data.

So we thought about separating the data from the application: storing the file in a machine and then accessing the files on the machine through the network, that is, sharing the files on this machine, sharing means competition, the data is unsafe, and the lock processing is required .... In order to remotely access and process files on this shared machine, we have to write an extra piece of code for our program to accomplish the following functions:

1. Remote connection

2. Open File

3. Read and write (locking)

4. Close the file

We write any program need to have this code, so we extract this code, written as a specialized processing software, this is the origin of MySQL and other database management software, but MySQL is not only solve the problem of data sharing, query efficiency, security and a series of problems, in short, Frees programmers from data management and focuses on writing their own program logic.

II. Overview of the database

1. What Is Data

the symbolic records describing things are called data, and the symbols describing things can be either numbers or text, pictures, images, sounds, languages, etc., and the data is represented by many forms, which can be digitized and stored in a computer. To describe a thing in a computer, you need to extract the typical characteristics of this thing, to form a record, which is equivalent to a line of content in a file, such as:

1 egon,male,18,1999, Shandong, computer department, 2017,oldboy

A simple record does not make any sense, if we divide by commas, we define the meaning of each field in turn.

1 Name,sex,age,birth,born_addr,major,entrance_time,school #字段2 egon,male,18,1999, Shandong, Computer department, 2017,oldboy #记录

So we can understand Egon, sex is male, age 18 years old, born in 1999, was born in Shandong, 2017 into the old boy computer department

2. What is a database (db)

The database is the warehouse where the data is stored, except that the warehouse is on a computer storage device, and the data is stored in a certain format.

In the past, people stored data in cabinets, and now the volume of data is large, no longer applicable

Database is a long-term storage in the computer, organized, shareable data can be.

The data in the database is organized, described and stored according to a certain data model, with small redundancy, high data independence and extensibility, and can be shared for various users.

3. What is a database management system (DBMS Management)

after understanding the concept of data and DB, how to organize and store it scientifically, how to efficiently acquire and maintain data is the key, This is the use of a system software---database management system

such as MySQL, Oracle, SQLite, Access, MS SQL Server

MySQL is mainly used for large-scale portals, such as Sogou, Sina, etc., its main advantage is open source code, because the open source of this database is free, he is now the Oracle company's products.
Oracle is mainly used in banks, railways, airports, etc. The database is powerful and the software is expensive. It is also the product of Oracle Corporation.
SQL Server is Microsoft's products, mainly used in large and medium-sized enterprises, such as Lenovo, founder and so on.

4, database server, data management system, database, table and records of the relationship (Key Understanding!!!) ）

Entries: 1 Liu Hailong 324245234 22 (information in multiple fields consists of one record, that is, a line of content in the file)

Table: student,scholl,class_list (i.e. file)

Database: Oldboy_stu (that is, folder)

Database management system: such as MySQL (is a software)

Database server: One computer (high memory requirements)

Summarize:

1) database Server---Run---> Database management software

2) database management software---> Management---> Database

3) Database---Organization---> table, i.e. folder---organization---> Files

4) Table---Storage---> Multiple records, that is, the file---storage---> Multi-line content

5. The development process of database management technology (understanding)

1) Manual Management Phase

Before the mid 1950s, computers were used primarily for scientific calculations.

Hardware level at the time: external memory only tape, card, tape, no disk and other direct access storage devices

The software condition at that time: No operating system, no software to manage the data, the data processing method is batch processing.

Manual management of data has the following characteristics:

1 data not saved: computer is mainly used for scientific calculation, data temporary, temporary input, not save

2 Application Management data: Data to be managed by the application itself, the application needs to process the logical + physical structure of the data, the development burden is very heavy

3 data is not shared: a set of data for only one program, multiple programs involving the same data, must be defined, resulting in a large number of data redundancy

4 Data is not independent: After changes in the logical or physical structure of the data, the application must be modified accordingly and the development burden increased

2) file system phase

The late the 1950s to the mid-60

Hardware level: With direct access to disk, drum and other storage devices

Software level: With the operating system, and the operating system has a dedicated data management software, namely the file system, processing methods are not only batch processing, and can be online real-time processing

File System Management data has the following advantages:

1 data can be stored for a long time: The computer is used for data processing, so the data needs to be kept for a long time, and the operation is increased.

2 File System Management data: File system This software, the data organized into a relatively independent data files, by file name, according to record access. The structure within the record is realized, but the whole is unstructured. and the conversion between the program and the data by the file system provides the access method, is the application and the data has the certain independence, the programmer may not need to consider the physical detail too much.

File System Management data has the following drawbacks:

1 data sharing poor, redundant: A file for an application, different applications have the same data, they must also establish their own files, cannot share the same data, resulting in data redundancy, waste space, and the same data re-storage, management, easy to create data inconsistencies

2 data independence is poor: once the logical structure of the data changes, the application must be modified to modify the definition of the file structure. Changes to the application will also cause changes in the data structure of the file. Therefore, there is a lack of independence between data and procedures. As can be seen, the file system is still a non-resilient, unstructured collection of data that is isolated between files and does not reflect the memory connection between things in the real world.

3) Data System phase

Since the late the 1960s, the computer for the management of the scale of the larger, more and more widely used, the volume of data increased sharply, while a variety of applications, multi-lingual mutual coverage of the sharing of data requirements more and more strong

Hardware level: With large capacity disk, hardware architecture down

Software level: Software price rise (development efficiency must be improved, the programmer must be freed from data management), distributed concept prevails.

Features of the database system:

1 data structuring (e.g. Odboy_stu)

2 data sharing, low redundancy, easy to expand

3 High Data independence

4 data is managed and controlled centrally by the DBMS

A: Security protection of data

B: Integrity check of data

C: Concurrency control

D: Database recovery

Third, MySQL introduction

MySQL is a relational database management system developed by the Swedish MySQL AB company, currently owned by the Oracle company. MySQL's most popular relational database management system, MySQL is one of the best RDBMS (relational database Management system, relational databases management systems) application software in WEB applications.

1. mysql

MySQL is a socekt server
Client software
MySQL comes with
Python module

2. Other types of databases

Sqllite,db2,oracle,access,sql Server,mysql
There are two main types of:
Relationship type: Above, note: SQL statements are common
Non-relational: Mongodb,redis,memcache

Iv. Download and install

1. Download

Website:http://dev.mysql.com/downloads/mysql/

Download down directly is a compressed package

2. Decompression

If you want MySQL to be installed in the specified directory, then move the extracted folder to the specified directory, such as: D:\mysql-5.7.19-winx64

3. Initialization

CD D:\mysql-5.7.19---initialize-insecure

Note: Run the above command with administrator privileges

4. Start the MySQL service

# go to executable directory CD D:\mysql-5.7.19-#  start MySQL service mysqld

5. Start the MySQL client and connect to the MySQL service

The default password is not set to the root account due to the "mysqld--initialize-insecure" command used during initialization

# go to executable directory CD D:\mysql-5.7.19-#  connect MySQL server mysql-u root-#  Prompt Please enter password, direct enter

To this end, the MySQL server has been successfully installed and the client is ready to connect, and in the future to operate MySQL, it is only necessary to repeat the 4, 5 steps. However, in the 4, 5 steps of repeated access to the executable directory is cumbersome, if you want to be easy to operate later, you can do the following operations.

1) Add Environment variables

【右键计算机】--》【属性】--》【高级系统设置】--》【高级】--》【环境变量】--》【在第二个内容框中找到 变量名为Path 的一行，双击】 --> 【将MySQL的bin目录路径追加到变值值中】

This saves the CD folder step

2) Create a MySQL service as a Windows service

The previous step solves some problems, but is not exhaustive, because the current terminal will be stuck while executing "MYSQD" to start the MySQL server, then do the setup to resolve the problem:

Note: The absolute path of the command must be started with MySQL before--install

# To make a Windows service for MySQL, execute this command at the terminal: " D:\mysql-5.7.19-winx64\bin\mysqld " --#  Remove the MySQL Windows service and execute this command at the terminal:"D:\mysql-5.7.19-winx64 \bin\mysqld" --remove

After registering as a service, you only need to execute the following command when you start and close the MySQL service later:

# start the MySQL service  #  close MySQL service net stop MySQL

v. Basic management of MySQL software

1. Start the View

[[email protected] ~]# systemctl start mariadb #启动 [[email protected] ~]# systemctl enable MARIADB # Set boot created symlink from/etc/systemd/system/multi-user.target.wants/mariadb.service to/usr/lib/systemd/system/ Mariadb.service. [[email protected] ~]# PS aux |grep mysqld |grep-v grep #查看进程, mysqld_safe for startup MySQL script file, internal call mysqld command MySQL 3329 0        .0 0.0 113252 1592?        Ss 16:19 0:00/bin/sh/usr/bin/mysqld_safe--basedir=/usrmysql 3488 0.0 2.3 839276 90380? Sl 16:19 0:00/usr/libexec/mysqld--basedir=/usr--datadir=/var/lib/mysql--plugin-dir=/usr/lib64/mysql/plugin--log- Error=/var/log/mariadb/mariadb.log--pid-file=/var/run/mariadb/mariadb.pid--socket=/var/lib/mysql/mysql.sock[[               Email protected] ~]# netstat-an |grep 3306 #查看端口tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN [[email protected] ~]# ll-d/var/lib/mysql #权限不对, startup unsuccessful, note user and groupdrwxr-xr-x 5 mysql mysql 4096 Jul 20 16:2 8/var/lib/mysql

You must reset your password using the ALTER USER statement before executing this statement.

2. Login, set Password

Initial state, administrator root, the password is empty, by default, only allow to set password from native login localhost [[email protected] ~]# mysqladmin-uroot password "123"        set the initial password because the original password is empty, So-P can not use the ' Windows password without quotes ' [[email protected] ~]# mysqladmin-uroot-p "123" password "456" to        Modify the MySQL password, because there is already a password, So you must enter the original password to set the new Password command format: [[email protected] ~]# mysql-h172.31.0.2-uroot-p456[[email protected] ~]# Mysql-uroot-p[[email p Rotected] ~]# mysql                    log in as root user, password is empty

3. Forgot password

Method One: Delete the authorization library MySQL, re-initialize

[Email protected] ~]# rm-rf/var/lib/mysql/mysql #所有授权信息全部丢失!!! [[email protected] ~]# systemctl restart Mariadb[[email protected] ~]# MySQL

Method Two: Skip the authorization library when you start

[Email protected] ~]# vim/etc/my.cnf    #mysql主配置文件 [mysqld]skip-grant-table[[email protected] ~]# systemctl restart Mariadb[[email protected] ~]# mysqlmariadb [(none)]> update mysql.user set Password=password ("123") where user= "root" and host= "localhost"; MariaDB [(None)]> flush privileges; MariaDB [(None)]> \q[[email protected] ~]# #打开/etc/my.cnf Remove skip-grant-table, then restart [[email protected] ~]# Systemctl Restart Mariadb[[email protected] ~]# mysql-u root-p123 #以新密码登录

Windows platform, version 5.7 MySQL, two ways to crack passwords:

Way One Mode two

4. Under Windows, specify the configuration file for the MySQL service

#在mysql的解压目录下, create a new My.ini, and then configure the [mysqld];skip-grant-tablesport=3306character_set_server=utf8# extracted directory Basedir=e:\ Mysql-5.7.19-winx64#data directory Datadir=e:\my_data #在mysqld--initialize, the initial data will be stored in the directory specified here, after initialization, when you start MySQL, will go to this directory to find data [Client]port=3306default-character-set=utf8

Vi. Initial knowledge of SQL statements

With MySQL, this database software can free the programmer from the management of the data, and focus on the programming logic.

MySQL Server software is mysqld to help us manage folders and files, provided that we as users, we need to download MySQL client, or other modules to connect to mysqld, and then use the syntax format specified by the MySQL software to submit their own commands, Implements the management of folders or files. The syntax is the SQL statement

View Code

Vii. Authorization of initial knowledge

#创建用户create user ' egon ' @ ' 1.1.1.1 ' identified by ' 123 ', create user ' Egon ' @ ' 192.168.1.% ' identified by ' 123 '; create user ' eg On ' @ '% ' identified by ' 123 '; #授权: Permissions on the folder, on the file, on a field of the file Grant Select,insert,update,delete on db1.* to ' Alex ' @ '% '; #对文件夹-"Database grant all privileges the db1.* to ' Alex ' @ '% ';      # (All does not contain grant permissions) revoke select on db1.* to ' Alex ' @ '% ';

37, MySQL first knowledge