MySQL One: primary knowledge database

First, 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. But the reality is not so simple, for the following reasons;

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

2. Data security issues

3. Concurrency

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.

II. Overview of the database

1. What Is Data

The symbol records describing things are called data, the symbols describing things can be either numbers or words, pictures, images, sounds, languages, etc., data from a variety of forms, they can be digitized into a computer, describe a thing in a computer, you need to extract the typical characteristics of this thing, to form a record, is equivalent to a line of content in a file.

2. What is a database (db)

The database is the warehouse where the data is stored, but the warehouse is in the computer storage device, and the data is stored in a certain format, in the past, people stored in the file cabinet, the data is now large, is 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 obtain and maintain data efficiently is the key, which uses 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:

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. The development process of database management technology (understanding)

Slightly

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. What is MySQL?

MySQL is a software based on the C/s architecture of socket writing

Client software

MySQL comes with: such as MySQL command, mysqldump command, etc.

Python modules: such as Pymysql

2. Database management software Classification

There are two main types of:

Relationship type: As Sqllite,db2,oracle,access,sql server,mysql, NOTE: SQL statements are common

Non-relational: Mongodb,redis,memcache

Can be simply understood as:

relational databases require a table structure

The non-relational database is key-value stored, without a table structure

Iv. Download and install

Refer to another blog: http://blog.51cto.com/10630401/1976486

V. Basic management of MySQL software

1. Start the View

View under Linux Platform

Ps-ef |grep MySQL

2, login, set the password

Initial state, administrator root, password is empty, default to only allow log on from native localhost

1) Set Password

Mysqladmin-uroot password "123" Set initial password because the original password is empty, so-P can not

Mysqladmin-uroot-p "123" password "456" to modify the MySQL password, because it already has a password, so you must enter the original password to set a new password

2) Login

mysql-h172.31.0.2-uroot-p456

Mysql-uroot-p

MySQL log in as root user, password is empty

3. Forgot password

Linux platform, two ways to crack the password

1) Delete the authorization library MySQL, re-initialize

Rm-rf/var/lib/mysql/mysql #所有授权信息全部丢失!!!

Service mysqld Start

Mysql

2) Skip Authorization Library at startup

vim/etc/my.cnf #mysql主配置文件

[Mysqld]

Skip-grant-table

Service mysqld Start

Mysql

Update Mysql.user set Password=password ("123") where user= "root" and host= "localhost";

Flush privileges;

\q

Open/etc/my.cnf Remove skip-grant-table, then restart

Service mysqld Start

[Email protected] ~]# mysql-u root-p123 #以新密码登录

4. Unified character encoding

1) Modify the configuration file

[Mysqld]

Default-character-set=utf8

[Client]

Default-character-set=utf8

[MySQL]

Default-character-set=utf8

ps:mysql5.5 Above: Modification method has changed

[Mysqld]

Character-set-server=utf8

Collation-server=utf8_general_ci

[Client]

Default-character-set=utf8

[MySQL]

Default-character-set=utf8

2) Restart Service

3) View the results of the changes:

Show variables like '%char% '

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 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: 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 user access rights grant, REVOKE

Introduction to SQL:

1. Operations folder (database)

Add: Create Database db1 charset UTF8;

Check: show databases;

Change: Alter DATABASE DB1 CharSet latin1;

Delete: Drop database db1;

2. Operation file (table)

Switch to the folder first: Use DB1

Add: 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 file (record)

Add: 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;

Clear the table:

Delete from T1; #如果有自增id, the new data is still the same as the beginning of 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 says: self-increment

Primary key means: constraint (cannot be repeated and cannot be empty); Accelerated Lookup

MySQL One: primary knowledge database