MySQL Basics Quick Start Knowledge Summary (with Mind Map)

Preface

This article is a summary of some basic MySQL knowledge that I have learned by myself during this period of time. I use MySQL 5.7 myself.

1. Basic knowledge of database

1. What is a database?

A database is a warehouse that organizes, stores, and manages data according to a certain data structure. It is an efficient solution for managing large amounts of information.

Database system DBS = database DB + database management system DBMS.

2. Classification of databases

Databases are divided into relational databases and sub-relational databases.

Relational: It is a database built on a relational model. As the name implies, the relational model is a two-dimensional table model used to record the relationship between entities and entity information. Common relational databases include Oracle, MySQL, and SQL Server.

Non-relational database: a database that is not based on a relational model. Mainly MongoDB Redis

3. Common languages ​​for databases

Database System

Structured Query Language SQL

Database

Database Management System DBMS

table
row => record
Column column => field field

4. Common operations of the database

DOS Commands:

Connect to the remote host: mysql -h host name/IP address-P port number-u username-p password Connect to the local host: mysql -u username-p password

Client (visualization software): Navicat

Code:

Web page: PHPMyAdmin

5. MySQL Architecture

C/S architecture:
Server management and storage data
The client sends an operation request

2. Add, delete, modify and query the database

1. Create a database

#Syntax for creating a database create database `database name` charset=utf8/gbk;

Before creating a database, you need to connect to the database first. You can practice with your own local database.

2. Query the database

#Query all databases show databases;
#Query by condition like, where % represents any number of characters, and - represents any one character.
create databases like '%-';
#Query the database creation statement show create database;

3. Modify the database

#Modify the database (only options, that is, character sets, can be modified)
alter database `library name` [new options];

4. Delete the database

#Delete the database (the syntax is simple, but the consequences are serious. Generally, you don't have permission, haha.)
drop database `library name`; 

3. Add, delete, modify and check the table

1. Create a table

#Before creating a table, specify the database use `specify database name`;
#Create table create table `table name`(
`field1` field1 type field1 attribute,
...
`fieldN` fieldN type fieldN attribute); [options]

The options mainly fall into three categories:

1. Character set charset=utf8 / GBK ...
2. Data engine engine = innodb / mysiam
3. Comments comment = ''Comments''
   

Other field types and field attributes are described in detail later.

2. Query Table

#Query all tables show tables;
#Conditional query show tables like '%-';
#Query table structure desc `table name`;
#Query the table creation statement show create table `table name`;

3. Modify the table

#Modify table options alter table `table name` [new options];
#Change the table name rename table `old table name` to `new table name`;
#Modify the fields in the table alter table `table name` change `old field name` `new field name` new field type;
#Add a new field to the end of the field alter table `table name` add `new field name` type attribute;
#Add fields to the corresponding fields and then alter table `table name` add `new field name` type attribute after `corresponding field`
#Add a field to the front alter table `table name` add `new field name` type attribute first;

4. Delete the table

#If the table exists, delete it, otherwise report an error drop table [if exists] `table name`;

IV. Add, delete, modify and check records

1. Insert records

#Insert records insert into `table name`(`field 1`,...`field`) values('value 1',...,'value N');
#When all values ​​are passed in at once, you can omit the fields insert into `table name` values('value 1',...,'value N');
#When multiple records need to be passed in at once, insert into `table name`(`field 1`,...`field`) values
('value 1',...,'value N'),
('value 1',...,'value N'),
...,
('value 1',...,'value N');
#When multiple records need to be passed in at one time and all fields are passed in, insert into `student` values ​​(value list 1), (value list 2), (value list n);

2. Query records (most commonly used)

#Query syntax select [selection] field list as alias from `table name` where conditional expression;

1. Conditional expression:

Logical operators: and or not

Comparison operators: + - * / < > = !=

2. Options:

all: query all, if not specified, all are selected by default

distinct: remove duplicates. Duplicates refer to the data after query, and only when all fields of the record are the same can it be considered duplicates.

as: alias. Set an alias for the queried field for easy reference.

Common aggregation functions: count(), Max(), Min(), Sum(), avg()

3. Join table query

1. Inner join 
#Query the records that meet the connection conditions in the two tables involved in the connection, and filter out those that do not meet the conditions.
select * form `Table 1` inner join `Table 2` on connection condition;
2. Left Outer Join
#The records in the left table that participate in the connection will be queried even if they do not match the connection conditions, and the records in the right table that do not match will be filtered out select * from `Table 1` left join `Table 2` on connection conditions;
3. Right outer join 
#The records in the right table that participate in the connection will be queried even if they do not match the connection conditions, and the records in the left table that do not match will be filtered out select * from `Table 1` right join `Table 2` on connection conditions;

3. Modification records

#Syntax update `table name` set `field` = 'new value' where conditional expression;

4. Delete records

#Syntax delete from `table name` where conditional expression;

5. Field Type

1. Digital

1.1 Integer Type

tinyint: occupies one byte and can represent a total of 256 numbers

Signed: -128~127
Unsigned: 0~255

int: occupies 4 bytes

Signed: -2.1 billion to 2.1 billion Unsigned: 0 to 4.2 billion

1.2 Decimal type

Floating point numbers:

float(M,D): single-precision floating point number
double(M,D): double-precision floating point number

Fixed point number:

decimal(M,D): decimal type where data will not be lost, often used to record currency

2. Text type

1. char(M): fixed-length character, M represents the maximum number of characters. The advantage is fast operation speed. Commonly used for fixed-length characters within 255 characters. For example: ID card, telephone number, etc.
2. varchar(M): variable length character, M represents the maximum number of characters. The advantage is that it saves space. It is often used for characters within 255 characters and with uncertain length.
3. text: Often used in texts with more than 256 characters
   

3. Date and time

1. datetime: fixed date and time
   
2. timestamp: Timestamp: When adding or updating records, it is automatically updated to the current system time, used to record the time of the last modification or the time of the newly inserted record
   

6. Field properties

1. not null: Set the value of this field to not be empty. If it is not written, it can be empty by default.
2. default: Set a default value. If no data is passed in, the default value is used. If data is passed in, the passed value is used.
3. comment: Remarks field in Chinese characters for easy maintenance
4. unique key: unique value The value of this field cannot be repeated, but can be empty
5. primary key
   Used to uniquely identify a record. A table can only have one primary key. It cannot be empty or repeated.
6. auto_increment Automatic growth: When a new record is inserted, it automatically adds 1 to the maximum value of this field. The condition is that the type of this field must be an integer. It is often used with the primary key, but it is not necessary to use it with the primary key.
   

Summarize

This is the end of this article on the quick introduction to MySQL basics. For more relevant MySQL basics content, please search for previous articles on 123WORDPRESS.COM or continue to browse the related articles below. I hope everyone will support 123WORDPRESS.COM in the future!