Shulou(Shulou.com)06/01 Report--

This article mainly introduces mysql data types, indexes and other analysis, hoping to supplement and update some knowledge for you, if there are other problems that need to be understood, you can continue to pay attention to my updated articles in the industry information.

1. Mysql data types:

1. Value type:

Integer type: when unsigned is used, only positive values are taken. When the value is not enough for the specified width, spaces are added on the left.

Integer types include: tinyint,smallint,int, etc.

Floating-point type: (5,2) total length and length after decimal point

Including: float,double,decimal(fixed length, do not meet the length requirements input do not enter)

2. Character type:

char: Fixed length, 255 characters, right filled with spaces, high efficiency

varchar: variable length, allocate space according to actual data size, mailbox available varchar

Large text types: text, blob

3. Date and time type:

datetime (8 bytes) timestamp (4 bytes)

Date: date 0001-01-01~9999-12-31

Year: year 1901-2155 When expressed in two digits: 01-69 for 20XX 70-99 for 19XX

Time: HH:MM:SS, takes up three bytes

4. Time function

now() : Gets the system datetime when this function was called

month() ,date() time() : Gets the specified month, date, time

5. Enumeration type: hobby, gender, major

Field name enum(value 1, value 2, value 3) Single choice can be represented by a number

Field name set (value1, value2, value3) Multiple choice

6. Field constraints: restrict how to assign values to fields

null,not null,default

Second, Mysql index

Pros and cons: Takes up physical storage space, slows down writing; speeds up queries

1. General index:index

There can be multiple index fields, whose values can be repeated and null. In the table structure, the value of its key key is displayed as:mul

Create: create table user( id int(3), index(id)); //Create when creating table

create index id on user1 (id); //create index id on existing table

Delete: drop index id on user1; //Delete index id in user1 table

2, unique index: unique index in the key column is represented as: UNI

There can be multiple unique indexes in a table, and the corresponding field values cannot be duplicated. If they are not empty, the function is limited.

Same as primary key.

create unique index hz_id on s2(hz_id);

Delete: drop index hz_id on s2;

III. Primary key, compound primary key, foreign key

1. Primary key:primary key A table can only have one primary key, which is expressed as: PRI

The corresponding field value cannot be empty.

Create: alter table name add primary key (field name);

Delete: alter table name drop primary key;

primary key is used with auto_increment, and the value of the field is automatically incremented

When creating a primary key, add auto_increment to the end, and when deleting a primary key,

First, delete the self-increasing attribute, that is, modify the attribute of the field first.

2. Composite primary key: multiple fields in the table are used as primary keys together, and they should be created together.

alter table s1 add primary key(stu_id,name);

Delete: alter table s1 drop primary key;

3. Foreign key usage rules: storage engine is innodb, type and width are the same, referenced table: preferably primary key

Create: foreign key(A table field name) references B table name (field name)

on update cascade on delete cascade;

Delete foreign key: alter table name drop foreign key constraint name;

Foreign key name query: show create table table name\G, can query

Mysql Storage Engine

What is a storage engine: responsible for performing the actual data I/O operations for the database,

Different storage engines store data differently,

In mysql 5.7, the default storage engine is innodb.

1. View: show create table sys_in; //View the storage engine of a table

show engines; //View supported and default storage engines for the database

2. Modify the default storage engine of the database: vim /etc/my.cnf

default-storage-engine=myisam/innodb

3. Modify the default engine of an existing table:

alter table name engine=innodb;

Difference Between Myisam and Innodb

myisam: Support table-level locking, suitable for reading more and writing less;.frm (table structure), .MYI (index), .MYD (data)

innodb: support row-level locking, suitable for more writes and less reads, support foreign keys, support transactions, transaction rollback,.frm (table structure).ibd (index + data)

5. Related definitions of locks and transactions

Lock type: read lock (shared lock), write lock (exclusive lock)

Lock granularity: table lock, row lock, page lock (memory)

Transaction: the process of connecting and disconnecting an sql operation is called a transaction. Success or failure.

Transaction log file: ib_logfile(sql statement storage location);ibdata1 (data information)

Read the above on mysql data types, indexes and other analysis, I hope to give you some help in the actual application. Due to the limited space of this article, it is inevitable that there will be deficiencies and needs to be supplemented. If you need more professional answers, you can contact our 24-hour pre-sales service on the official website to help you answer your questions at any time.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.