The way of programming: the first knowledge of MySQL series

The origin of database management software

Based on what we've learned before, data is stored in a file for permanent preservation, without a doubt that a file can only exist on a single machine.

If we ignore the efficiency of accessing data directly based on the file, and assume that all the components of the program are running on a single machine, there is no problem accessing the data with the file.

Unfortunately, these assumptions are your own, the above assumptions have the following questions ...

1. All components of the program cannot be run on a single machine

# because once the machine hangs, it means that the whole software crashes, and the execution efficiency of the program depends on the hardware that carries it, and the performance of a machine machine is limited, limited by the current level of hardware, the performance of a machine vertical expansion is a limit.  # 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. 

2. Data security issues

# according to the description of 1, we distribute the various components of the program to each machine, but it is necessary to know that the components are still a whole, with the implication that the data of all the components should be 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 (with a socket), that is, sharing the files on the machine, sharing means competition, Data is unsafe and requires lock processing .... 

3. Concurrency

According to the description of 2, we have to write a socket server to manage the files on this machine (database servers), and then write a socket client to complete the following functions:

# 1. Remote connection (concurrent support) # 2. Open File # 3. Read and write (locking) # 4. Close the file

Summarize:

# before we write any program, we need to write a web-based operation of the file on a host program (Socket server and client program), so someone will write such a program as a specialized processing software, which is the origin of MySQL and other database management software, But MySQL solves not only the problem of data sharing, but also the query efficiency, security and a series of problems, in short, the programmer from the data management to free up, focus on their own program logic to write. 

Database overview

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 Yang Sen, male,18,1999, Henan, computer department, 2018,oldboy

A simple record does not make any sense, if we are separated by commas, we define the meaning of each field, which is equivalent to defining the title of the table.

# Field # Record

What are databases (database, abbreviated 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.

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 becomes 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.

Database server, data management system, database, table and records of the relationship (focus on 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:

Database server-: Running database management software

Database management software: Management-Database

Database: A folder for organizing files/tables

Table: A file that is used to hold multiple lines of content/Multiple records

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.

What is MySQL

# MySQL is a software based on the C/s architecture of socket writing # Client Software MySQL comes with: Python modules such as MySQL command, mysqldump command, etc.: Pymysql

Classification of database management software

# There are two main types of: Relationship type: such as Sqllite,db2,oracle,access,sql server,mysql, note: SQL statement general non-relational type: Mongodb,redis,memcache # can be simply understood as:     relational database requires a table structure    non-relational database is key-value stored, no table structure

Download install slightly

Basic MySQL Software Management

Login, set Password

~]#  mysqladmin-uroot password "123"        set initial password because the original password is empty, so-p can not use [[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 a new password  ~]#  mysql-h172.31.0.2-uroot-p456[[email protected] ~]#  mysql-uroot-p[[email protected] ~]< c12>#  mysql                    log in as root user, password is empty

Forgot password

Linux platform, two ways to crack the password

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

Method One: Delete the authorization library MySQL, reinitialize

[[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"  andhost="localhost"; MariaDB [(none)]>flush Privileges; MariaDB [(none)]>\q[[email protected]~]##打开/etc/my.cnf Remove the skip-grant-table and restart[[Email protected] ~]#systemctl Restart mariadb[[Email protected] ~]#mysql-u root-p123 #以新密码登录method Two: Skip the authorization library when you start

method Two: Skip the authorization library when you start

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

# 1 turn off MySQL # 2 executed in cmd: mysqld--skip-grant-tables # 3 executing in cmd: MySQL # 4 Execute the following sql:update mysql.user set Authentication_string=password ("root"  ; flush privileges; # 5 Tskill mysqld #或taskkill-F/pid 7832 # 6 restarting MySQL Way One

Way One

#1. Turn off MySQL, you can kill it with Tskill mysqld#2. In the extract directory, create a new MySQL configuration file My.ini#3. My.ini content, specifying[Mysqld]skip-grant-Tables#4. Start Mysqld#5. Enter MySQL login directly in cmd, then operateUpdate Mysql.user Set Authentication_string=password ("') Where user='Root and host='localhost';flush Privileges;#6. Note The skip-grant-tables in My.ini, and then start MYQSLD, then you can log in with the new passwordMode two

Mode two

Under Windows, specify the configuration file for the MySQL service

Emphasis: Comments in the configuration file can have Chinese, but the configuration item cannot appear in Chinese

#under MySQL's decompression directory, create a new My.ini, and then configure#1. When executing the MYSQLD command, the following configuration will take effect when the MySQL service starts[Mysqld];skip-grant-Tablesport=3306Character_set_server=Utf8default-storage-engine=innodbinnodb_file_per_table=1#Extracted directorybasedir=e:\mysql-5.7.19-Winx64#Data DirectoryDatadir=e:\my_data#when Mysqld--initialize, the initial data will be stored in the directory specified here, after initialization, when starting MySQL, will go to this directory to find data#2. For global configuration of client commands, the following configuration takes effect when the MySQL client command executes[Client]port=3306default-character-set=Utf8user=Rootpassword=123#3. Only for MySQL this client configuration, 2 is the global configuration, and here is only for MySQL this command local configuration[MySQL];p ort=3306;d efault-character-set=Utf8user=Egonpassword=4573#!! If there is no [MySQL], the user's configuration when executing the MySQL command is based on [client]My.ini

View Code

Unified character encoding

#1. Modify the configuration file[Mysqld]default-character-set=UTF8 [Client]default-character-set=UTF8 [Mysql]default-character-set=UTF8#mysql5.5 Above: Modification method has changed[Mysqld]character-set-server=utf8collation-server=Utf8_general_ci[client]default-character-set=Utf8[mysql]default-character-set=UTF8#2. Restart the service#3. View the results of the changes:\sshow variables like'%char%'

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 SQL (structured query Language, which is the structured Queries language)

the SQL language is primarily used to access data, query data, update data, and manage relational database systems, which are developed by IBM. There are 3 types of SQL language:#1, DDL Statement    database Definition language: Database, table, view, index, stored procedure, such as Create DROP ALTER#2, DML Statement    database manipulation language: Inserting data insert, deleting data Delete, updating data update, querying data Select#3, DCL Statement    database Control Language: For example, to control access rights for a user grant, REVOKE

#1. Operating folderadd: Create Database db1 charset UTF8;        Check: show databases;        Change: Alter DATABASE DB1 CharSet latin1; Delete: Drop database db1;#2. Operating FilesFirst switch to folder: Use DB1: CREATE TABLE t1 (id int,name char); Check: Show tables change: ALTER TABLE t1 modify name char (3); ALTER TABLE T1 change name name1 char (2);    Delete: drop table T1; #3. Contents/Records in the action fileAdd: INSERT INTO T1 values (1,'Egon1'), (2,'Egon2'), (3,'Egon3'); Check: Select* fromT1; Change: Update T1 set name='SB'where id=2; Delete: Delete fromT1 where id=1; Empty table: Delete fromT1;#If there is a self-increment ID, the new data is still started as the last one before deletion. truncate table T1; The data volume is large, the deletion speed is faster than the previous one, and starts directly from zero, auto_increment means: self-increment primary key means: constraint (Cannot re- and cannot be empty); speed up the search

View Code

The way of programming: the first knowledge of MySQL series