MySQL -- database foundation

database

Just use a file to store data , Why do you need a database ?
File saving data has the following disadvantages ：

• File security issues
• Files are not conducive to data query and management
• Files are not conducive to storing large amounts of data
• It's not convenient to control the file in the program

Database storage media ：

• disk
• Memory

In order to solve the above problems , Design something more conducive to managing data —— database , It can manage data more effectively .

Common databases

• SQL Sever： Microsoft products ,.Net Programmer's favorite , Medium and large scale projects .
• Oracle： Oracle products , Suitable for large projects , Complex business logic , Concurrency is generally not as good as MySQL.
• MySQL： The world's most popular database , It belongs to Oracle , Good concurrency , Not suitable for complex business . Mainly used in e-commerce ,SNS, Forum . For the simple SQL The treatment effect is good .
• PostgreSQL : A relational database developed by the computer department of the University of California, Berkeley , Whether it's for private use , commercial , Or for academic research , Free to use , Modify and distribute .
• SQLite： It's a lightweight database , Abide by ACID Database management system based on RDBMS , It's contained in a relatively small C In the library . Its design goal is embedded , And it has been used in many embedded products , It takes up very low resources , In embedded devices , can
  It only takes a few hundred K That's enough memory .
  H2： It's a use. Java Development of embedded database , It itself is just a class library , It can be directly embedded into the application project .

Basic use

install

Connect

mysql -h 127.0.0.1 -P 3306 -uroot -p

Be careful ：

• -h: Followed by the host , If not -h 127.0.0.1 The default is to connect locally
• -P: Port number , If not -P 3306 Connection by default 3306 Port number
• quit sign out mysql

The server , database , Table relations

Installing a database server , Just installed a database management system program on the machine , This management program can manage multiple databases , Generally, developers will create a database for each application .
To save the data of the entity in the application , Generally, multiple tables will be created in the database , To save data for entities in the program .
database server 、 The relationship between database and table is as follows ：

Use cases

Create database

create database  Database name 

Using a database

use ms1

mysql Clear the screen

system clear

use system+ Orders can be executed Linux command

Create database tables

create table if not exists stu(
 id int, 
 name varchar(5), 
 sex varchar(2) 
 );

Insert data into the table

insert into stu1(id, name, sex) values (1, ' Zhang San ', ' male ');
insert into stu1(id, name, sex) values (2, ' Li Si ', ' Woman ');
insert into stu1(id, name, sex) values (3, ' Wang Wu ', ' male ');

Query the data in the table

show *from  Table name 

MySQL framework

MySQL Is a portable database , It can run on almost all current operating systems , Such as Unix/Linux、Windows、Mac and Solaris. Various systems differ in the underlying implementation , however MySQL Basically, it can ensure the consistency of the physical architecture on each platform .

SQL classification

• DDL【data definition language】 Data definition language , Used to maintain the structure of stored data For instructions : create, drop,alter
• DML【data manipulation language】 Data manipulation language , Used to manipulate data For instructions ： insert,delete,update ,DML There is another one in the DQL
• DQL, Data query language , For instructions ： select
• DCL【DataControl Language】 Data control language , Mainly responsible for authority management and affairs For instructions ： grant,revoke,commit

Storage engine

The storage engine is ： How database management systems store data 、 How to index and update stored data 、 Query data and other technologies .MySQL The core of is the plug-in storage engine , Support for multiple storage engines .

View storage engine

show engines;