Primary Knowledge Database

The origin of a 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

#因为这台机器一旦挂掉则意味着整个软件的崩溃, and the execution efficiency of the program depends on the hardware that hosts it, and the performance of a machine machine is always limited, limited by the current level of hardware, there is a limit to the vertical expansion of a machine's performance.

#于是我们只能通过水平扩展来增强我们系统的整体性能, this requires that we distribute the various components of the program across multiple machines to execute.

2. Data security issues

#根据1的描述, we distribute the various components of the program to each machine, but we need to know that the components are still 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. #于是我们想到了将数据与应用程序分离: The file stored in a machine, and then a number of machines through the network to access the file on the machine (with a socket), that is, sharing the file on this machine, sharing means competition, data insecurity, need to 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 # #. Read and write (locking) #4. close File

Summarize:

#我们在编写任何程序之前, all need to write a network operation on a host file on the program (Socket server and client program), so someone will write such a program is a specialized processing software, this 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.

Two database overview

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 are separated by commas, we define the meaning of each field, which is equivalent to defining the title of the table.

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

4 The relationship between database server, data management system, database, table and record (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:

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

5 Development History of database management technology (understanding)

A human 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

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

Three Data System stages

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

Three 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就是一个基于socket编写的C/s Architecture software # Client software MySQL comes with: such as MySQL command, mysqldump command and other Python modules: such as Pymysql

Classification of database management software

#分两大类: Relational: As Sqllite,db2,oracle,access,sql server,mysql, NOTE: SQL statements are generic non-relational: mongodb,redis,memcache# can be simply understood as:    Relational database requires a table structure    non-relational database is Key-value stored, no table structure

Four download installation

Linux version

#二进制rpm包安装yum-y install mysql-server MySQL

1. Unpack the tar package CD/SOFTWARETAR-XZVF mysql-5.6.21-linux-glibc2.5-x86_64.tar.gzmv mysql-5.6.21-linux-glibc2.5-x86_64 mysql-5.6.212. Adding users and Groups Groupadd mysqluseradd-r-g MySQL mysqlchown-r mysql:mysql mysql-5.6.213. Installing the database Su MYSQLCD mysql-5.6.21/scripts./mysql_install_db--user=mysql--basedir=/software/mysql-5.6.21--datadir=/software/ Mysql-5.6.21/data4. configuration file CD/SOFTWARE/MYSQL-5.6.21/SUPPORT-FILESCP MY-DEFAULT.CNF/ETC/MY.CNFCP mysql.server/etc/ Init.d/mysqlvim/etc/init.d/mysql #若mysql的安装目录是/usr/local/mysql, you can omit this step to modify the two change values in a file basedir=/software/ MYSQL-5.6.21DATADIR=/SOFTWARE/MYSQL-5.6.21/DATA5. Configuring environment Variables Vim/etc/profileexport mysql_home= "/software/mysql-5.6.21 "Export path=" $PATH: $MYSQL _home/bin "source/etc/profile6. Add a self-starting service chkconfig--add Mysqlchkconfig MYSQL  on7. Start mysqlservice mysql start8. Log in to MySQL and change password and configure remote access mysqladmin-u root password ' your_password ' #修改root用户密码mysql-u root      -P #登录mysql, you need to enter the password mysql>grant all privileges on * * to ' root ' @ '% ' identified by ' Your_password ' with GRANT OPTION; #Allow root users to remotely access Mysql>flush privileges; #刷新权限

1. Unzip the tar zxvf mariadb-5.5.31-linux-x86_64.tar.gz mv Mariadb-5.5.31-linux-x86_64/usr/local/mysql//Must be so, Many scripts or executable programs have direct access to this directory 2. Permissions Groupadd mysql//add MySQL group useradd-g mysql mysql//add MySQL user and belong to MySQL group Chown mysql:mysql-rf/us R/local/mysql//Set user and User group attribution for MySQL directory. chmod +x-rf/usr/local/mysql//Grant executable permission 3. Copy the configuration file cp/usr/local/mysql/support-files/my-medium.cnf/etc/my.cnf//Copy the default MySQL configuration file to/etc Directory 4. Initialize/usr/local/mysql/scripts/mysql_install_db--user=mysql//Initialize database Cp/usr/local/mysql/support-files/mysql.serve R/etc/init.d/mysql//Copy MySQL service program to system directory chkconfig MySQL on//add MySQL to system service and set to boot service MySQL start//start M Ysql5. environment variable Configuration vim/etc/profile//Edit profile, add MySQL executable path to System Pathexport Path=/usr/local/mysql/bin: $PATH source/etc/profile// Make path effective. 6. account password mysqladmin-u root password ' yourpassword '//Set root account and password mysql-u root-p//log in with root user mysqluse MySQL//switch to MySQL database 。 Select User,host,password from user; View system permissions drop user ' @ ' locAlhost '; Delete the unsafe account drop user [email protected] ':: 1 ';d ROP User [email protected];select user,host,password from user; Check the system permissions again to ensure that unsecured accounts are deleted.  Flush privileges; Refresh Permissions 7. Some of the necessary initial configuration 1) modify the character set for utf8vi/etc/my.cnf under [client] Add default-character-set = UTF8 add character_set_server under [mysqld] = UTF82) Increased error log vi/etc/my.cnf added under [mysqld]: Log-error =/usr/local/mysql/log/error.loggeneral-log-file =/usr/local/ MYSQL/LOG/MYSQL.LOG3) is set to case-insensitive, and Linux defaults are case-sensitive. Vi/etc/my.cnf added under [mysqld]: lower_case_table_name=1 modified restart: #service mysql restart

Window version

#1, download: mysql Community Server 5.7.16http://dev.mysql.com/downloads/mysql/#2, unzip if you want to install MySQL in the specified directory, then move the extracted folder to the specified directory , such as: c:\mysql-5.7.16-winx64#3, add environment variable "right-click Computer"-"attribute"-"Advanced system Settings"-"Advanced"-"Environment variable"-"in the Second content box find the variable named path of a row, double-click"- "Append MySQL Bin directory path to variable value, split" #4, initialize mysqld--initialize-insecure#5, start MySQL service mysqld # start MySQL service Start the MySQL client and connect to the MySQL service mysql-u root-p # Connect to the MySQL server

Basic management of five 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

After installing MySQL, after landing, regardless of running any command, always prompt this Mac MySQL error you must reset your password using ALTER USER statement before executing t His statement. Workaround: Step 1:set PASSWORD = PASSWORD (' Your new PASSWORD '); step 2:alter USER ' root ' @ ' localhost ' PASSWORD EXP IRE never;step 3:flush privileges;

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 [[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 protected] ~]# mysql
   
     Log in to the root user and the password is empty
   

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

[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:

#1 shutdown mysql#2 is executed in cmd: mysqld--skip-grant-tables#3 is executed in cmd: Mysql#4 performs the following sql:update Mysql.user set authentication_string= Password (") where user = ' root '; flush privileges; #5 Tskill mysqld #或taskkill-F/pid 7832#6 restart MySQL

#1. Turn off MySQL and you can kill it with Tskill mysqld. Under Unzip directory, create a new MySQL configuration file my.ini#3. My.ini content, specify [mysqld]skip-grant-tables#4. Start mysqld#5. In cmd, enter MySQL login directly, then operate update 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, and then you can sign in with the new password

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

#在mysql的解压目录下, create a new My.ini, and then configure # #. The following configuration takes effect when the mysqld command is executed, that is, when the MySQL service is started [mysqld];skip-grant-tablesport=3306character_set_server= utf8default-storage-engine=innodbinnodb_file_per_table=1# extracted directory Basedir=e:\mysql-5.7.19-winx64#data directory datadir=e:\ My_data #在mysqld--initialize, the initial data is stored in the directory specified here, and after initialization, when MySQL is started, it will go to this directory to find data #. For the global configuration of the 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;default-character-set=utf8user=egonpassword =4573#!!! If there is no [MySQL], the user's configuration when executing the MySQL command is based on [client]

5. Unified character encoding

#1. Modify configuration file [Mysqld]default-character-set=utf8 [Client]default-character-set=utf8 [mysql]default-character-set=utf8# mysql5.5 above: Modification method changed [Mysqld]character-set-server=utf8collation-server=utf8_general_ci[client] Default-character-set=utf8[mysql]default-character-set=utf8#2. Restart Service # #. View the results of the changes: \sshow variables like '%char% '

Six 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 languages: #1, DDL Statement    database Definition language: Databases, tables, views, indexes, stored procedures, such as Create DROP alter#2, DML statement    database manipulation languages: Insert data inserts, delete data deletes, Updated data update, query data select#3, DCL statement    Database Control Language: for example, to control user access rights grant, REVOKE

#1. Operation Folder        add: Create Database db1 charset UTF8;        Check: show databases;        Change: Alter DATABASE DB1 charset latin1;        Delete: Drop database db1; #2. The action file is    first switched to the 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/Record in action file        : INSERT into T1 values (1, ' Egon1 '), (2, ' Egon2 '), (3, ' Egon3 ');        Check: SELECT * from T1;        Change: Update t1 set name= ' SB ' where id=2;        Delete: delete from T1 where id=1;        Empty table:            delete from T1; #如果有自增id, the new data is still the same 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 be repeated and cannot be empty); Accelerated Lookup

Primary Knowledge Database