Comments are my biggest motivation~Complete MySQL basics (storage engine and graphical management tools), mysql basics

Comments are my biggest motivation ~ MySQL basics are completed (storage engine and graphical management tools), mysql basics

I logged in today and found that there were 3 comments~~ Come on!

This week’s plan is actually far from being achieved, and there are still a lot of things to do next week. . . So. . . Have fun on the weekend~

Today I tried to complete the basic knowledge of MySQL. Has a novice turned into an expert?

1. MySQL

-----Storage Engine (Getting Started)-----

----Introduction

---Intuitive example

mysql> SHOW CREATE TABLE test;
------- ---------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------
| Table | Create Table |
------- -------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------
| test | CREATE TABLE `test` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 |
------- ---- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------
1 row in set (0.04 sec)

ENGINE=InnoDB here is a storage engine

---

MySQL can store data in files/memory using different technologies. This technology is the storage engine (that is, the storage engine is a technology, a storage technology)

The functions of various engines are different due to differences in storage mechanisms, indexing techniques, and locking levels

Also known as table type

---

The main ones supported by MySQL are

MyISAM, InnoDB, Memory, CSV, Archive

----Knowledge Point-Concurrency Processing

Ensure data consistency and integrity when multiple connections modify records

For example, when processing requests from multiple users for the same record - commonly used are shared locks (read locks) (multiple users can read the same resource, and the data will not change during the reading process), Exclusive lock (write lock) (only one user can write to the resource at any time, and writing lock will block other read lock or write lock operations)

When locking, be careful to only target the most correct objects (objects cannot be too large or too small) - table locks (a locking strategy with the least overhead), row locks (the most expensive)

----Knowledge Point-Transaction Processing

Used to ensure the integrity of the database

Meets: ACID (atomicity, consistency, isolation, durability)

----Knowledge Point-Foreign Key

Strategy to ensure data consistency (mentioned before about various keys)

----Knowledge Points-Index

A structure to sort the values of one or more columns in a data table (similar to a directory function)

Achieve fast query

----Characteristics of various engines

MyISAM: 256TB, supports indexes, table locks, and data compression - suitable for situations where there are not many transactions

InnoDB: 64TB, supports transaction security (this is the reason why this engine is often chosen), supports indexes, row locks, and supports foreign keys (also only this engine supports this) - Gao Da Shang

Memory: has storage limit (determined by memory size), supports indexes, table locks

Archive: no storage limit, supports indexes, table locks, and data compression

There are different types of indexes, let’s talk about them.

Others: CSV does not support indexing, Blackhole is used as a relay for data replication (similar to burning after reading)

The first two are the most used.

----Set storage engine

---my.ini modification

default-storage-engine=...

---CREATE TABLE table(

...

An example is as follows

mysql> CREATE TABLE tp1(
-> s1 VARCHAR(20)
-> )ENGINE=MyISAM;
Query OK, 0 rows affected (0.10 sec)

mysql> SHOW CREATE TABLE tp1;
------- ---------------------------------- -------------------------------------------------- -------------
| Table | Create Table |
------- ------------------ -------------------------------------------------- ------------------------
| tp1 | CREATE TABLE `tp1` (
`s1` varchar(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
------- -------------------------- -------------------------------------------------- ---------------
1 row in set (0.00 sec)

mysql> ALTER TABLE tp1 ENGINE=InnoDB;
Query OK, 0 rows affected (0.35 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE tp1;
------- ---------------------------------- -------------------------------------------------- -------------
| Table | Create Table |
------- ------------------ -------------------------------------------------- ------------------------
| tp1 | CREATE TABLE `tp1` (
`s1` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
------- -------------------------- -------------------------------------------------- ---------------
1 row in set (0.00 sec)

-----Graphical management tool-----

----PHPMyAdmin

Since mine is a wamp integrated environment, I can log in directly to phpmyadmin

The normal thing is to go to the phpmyadmin official website to download the version of php that corresponds to your own

After downloading and installing, you have to go to the web page to operate it. After all, it is something placed on the server side

Being able to visually operate databases, data tables, and data seems not very customary, but I personally prefer to use commands. However, visualization is convenient for finding errors and so on. You still need to experience and understand it

----Navicat

There is a Chinese version of the client

----MySQL Workbench

Client provided by the official website

I’m not going to go into details, you can find it online, and you’ll get it if you have a look around

2. AJAX

-----Introduction-----

Chinese name: Asynchronous Javascript and XML (asynchronous request, partial refresh)

is a technology that can update part of a page without reloading the entire web page

Changing the landscape of the web

Preliminary knowledge: CSS, HTML, JS

----Synchronous and asynchronous

Synchronization: (Original technology) After the client requests, the server responds and returns the processing result. The client has been waiting throughout the process - synchronization means that the client and server process simultaneously

Asynchronous: (For example, when filling in the registration information, fill in the email address, and then a green checkmark will appear on the right side of the input box soon) When there is a change on the client, (seems to) immediately send a request to the processing The server side, and then the processor side processes and returns the result. During this process, the client is still performing other operations - asynchronous operations on the client side and server side

Technical difference: the lack of the XMLHttpRequest object - used for data exchange between the background and the server

Process introduction: HTML&CSS to implement the page to express information XHR to perform asynchronous exchange of data JavaScript operates DOM to achieve dynamic partial refresh

----XMLHttpRequest (XHR) object creation

Instantiate XHR: var request=new XMLHttpRequest();

Except IE6 and previous browsers, all browsers support XHR

----HTTP request

http is a rule/protocol for computers to communicate through the network. It is a stateless (no state retained/no memory) protocol

Steps of http request: Establish TCP connection-"Browser "calls" to the server-"Server response-"Server response-"Close connection

Requests and responses have their own formats. For details, you can check "Computer Network" or similar books. You may know about GET/POST and so on, so you can continue temporarily

Note: GET uses URL to transmit data, which is all visible (clear text) and has a character limit. The advantage is that it is simple, safe and can be saved as bookmarks, etc.

There are status codes in the browser after the response that you need to understand properly: mainly look at the first one - 1xx, information type, indicating that it is being processed; 2xx, successful; 3xx, redirection, indicating that the request is unsuccessful and further action is required from the customer; 4xx, Client error means that the request submitted by the client has an error, such as 404 NOT FOUND, which means that the document referenced in the request does not exist; 5xx, server error - helpful for debugging web programs, memorize it