MySQL learning path - MySQL statement DML


1. Understand what a storage engine is and why it is needed.

2. Master the advantages, disadvantages and applicable scenarios of common storage engines Innodb and MyISAM.

3. Master DML statements, such as adding INSERT, deleting and TRUNCATE, and updating UPDATE.

4. Understand logical deletion, physical deletion and how to select deletion methods in different business scenarios.

1, Database storage engine

        (1) MySQL supports many storage engines, of which InnoDB and BDB support transaction security. The detailed query is as follows:

                Press the win+r key on the keyboard, enter cmd, and enter mysql in the input box  - uroot -p, then enter the password, open it, and then enter show engines; You can view the supported engines, or directly enter show engines in DataGrid; Run to view. As shown below

Note: InnoDB is the default storage engine of MySQL

        (2) The comparison of storage engines is shown in the following table:

Storage restrictions64TB256TByesnothing
Transaction securitysupport///
Support indexsupportsupportsupport/
Lock particleRow lockWatch lockWatch lockRow lock
data compression/support/support
Support for foreign keyssupport///

        Lock particle note:

                 Row lock: lock the data of a row, that is, when you edit this row, others cannot edit this row. Only after editing is completed can others edit it  

                 Table lock: lock the whole table, that is, when you edit this table, others cannot edit it. You can edit it only after you finish editing  

2, Database DML operation    ★

        Case problems in MySQL: 1. In the window system, it is not case sensitive (configurable), and in the Linux system, it is strictly case sensitive  . Where column names are case insensitive.

         Code part

# insert database
create database if not exists date;  # datebase database, table data table
use date;  # Use database
create table if not exists student(
    uid int(5) zerofill primary key auto_increment,  # The id length is 5, and the primary key grows automatically. I don't understand here. In the previous article, zero fill means zero filling, that is, input 1 becomes 00001 and is stored in the database
    name varchar(255),
    telephone char(11) unique,
    age int(3) not null,
    sex tinyint  # 0 male 1 female

# The above code was introduced in the previous article:

# How to insert data
insert into date.student values (0, 'June', 13921254893, 18, 0);  # Full column insert
insert into date.student (name, age) values ('Anne', 18);  # The default insertion is to insert the data (name and age) in parentheses after date.student
insert into date.student (name, age) values ('Andy', 18), ('Joke', 17);  # When inserting multiple pieces of information, name and age are still the limiting conditions. The inserted information is separated by commas, that is, Andy and Joke are inserted
# The same is true for inserting multiple full column data, as follows:
insert into date.student values (0, 'June', 13921254893, 18, 0),
                                (0, 'Ross', 15711235487, 15, 1),
                                (0, 'Fred', 14523832361, 17, 0);  # If you need to add later, the end of the last semicolon indicates completion. The efficiency of this method of inserting data is higher than that of the first full column insertion

# About updating data tables
update date.student set name='Karl' where name='Andy';  # Change all Andy in the data table to Karl. Special note: if you do not join where, you will be prompted. Click Execute to replace all the data in the name column with Karl (use with caution)

# Basic format of display / query data: select the column to be displayed, where * indicates all from database names and table names 
select * from date.student;  # Displayed data, format: select * (this * indicates all, and can also be specified, such as name. Entering name indicates all the data whose column name is name) from database name. Table name
select as Name, s.age as AGE from date.student as s;
# The Name under query date is t_ · data table of student and named s; s. The meaning of Name as Name is to display the Name column in the data table student, and Name it as Name

# About deleting
delete from date.student where sex=0;  # Delete all data whose gender is male, that is, delete all data (the whole row) in the database that meets the following conditions of where. Special attention: without adding where warning, click Execute to confirm and delete all data (use with caution)
truncate table date.student;  # Delete all the data in the table without adding where. Clear all the data directly without warning, and the efficiency is higher than that of delete
drop table date.student;  # Whether there is data or not, the whole table will be deleted
Import and export; Using tools to import and export table data
 Export: right click the database or table, select Dump Date to File, and select the file location to export to
 Import: right click Import Date from File on the library or table and select the imported database (if the table does not exist, it will be created automatically, and if it exists, data will be inserted. Pay attention to the data format)
Alt + left mouse button can select multiple rows to modify at the same time, and then press esc to exit

Bit is the smallest calculation unit of the computer, i.e. 0 or 1. Each binary digit accounts for 1 bit, and byte is composed of 8, i.e. B. the conversion unit is as follows
 8 bits (byte)=1 byte (b) 1024b = 1KB 1MB = 1024KB 1GB = 1024MB 1t = 1024g 1PB = 1024t
 In general utf-8 coding, a Chinese character occupies 3 bytes; Under gbk coding, a Chinese character takes up 2 bytes.
utf-8 is different from utf8mb4. utf-8 can store up to 3 bytes, while utf8mb4 can store up to 4 bytes. For example, emoji expression is a special Unicode encoding

3, Physical and logical deletion

        (1) Physical deletion

                Completely deleting the database is called physical deletion. After deletion, it cannot be recovered, such as delete and truncate. For some important data, it is recommended to adopt logical deletion to prevent data from being found

        (2) Logical deletion

                Logical deletion is essentially an update operation. For important data tables, an isDelete field is added, which generally defaults to 0 (meaning not deleted). This field logically indicates whether the data has been deleted. The real situation is that the data still exists in the database.

                The code is as follows

create table if not exists date.student(
    id int not null auto_increment primary key ,
    age int,
    name char(20),
    isDelete bit default 0  # bit binary byte. isDelete adds another data type. The default value is 0, indicating that it has not been deleted
)engine=innodb default charset=utf8;

Supplement: INT[(M)] [UNSIGNED] [ZEROFILL] M is 11 by default, and 10 is M in the above statement, which refers to the maximum display width and the maximum value is 255; The int type is stored in the database with a length of 4 bytes  

That is, the int type can not give the width, such as age int. if the width is given, it will be supplemented, such as age int(5). When you enter 1, the storage will be stored as 00001, and the previous 0 will not be displayed. However, vachar and char must give the width, that is, name char(20). If the width is not given, the program will report an error.

int signed integer range is - 2147483648 ~ 2147483647
int unsigned integer range is 0 ~ 4294967295
In mysql, the rounding method is to round if it is greater than 5 and remove if it is less than 5, that is, 2.11 keeps one decimal place as 2.1; 2.15 keep one decimal place as 2.1; 2.17 keep one decimal place as 2.2.

Tags: Database MySQL

Posted on Sat, 18 Sep 2021 18:47:36 -0400 by AL-Kateb