How to work with data types

[Pages:9]Chapter 8

How to work with data types

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 1

Objectives

Applied 1. Code queries that convert data from one data type to another.

Knowledge 1. Describe the data that can be stored in any of the character,

numeric, date/time, and large object data types. 2. Describe ENUM and SET data types.

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 2

MySQL data type categories

? Character ? Numeric ? Date and time ? Large Object (LOB) ? Spatial ? JSON

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 3

The character types

Type

Bytes

CHAR(M)

Mx4

VARCHAR(M) L+1

How the character types work with utf8mb4

Data type

CHAR(2) CHAR(10) VARCHAR(10) VARCHAR(20) VARCHAR(20) VARCHAR(20)

Original value

'CA' 'CA' 'CA' 'California' 'New York' "Murach's MySQL"

Value stored

'CA'

`CA

'

'CA' 'California'

'New York' "Murach's MySQL"

Bytes used

8 40 3 11 9 15

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 4

Terms to know about character types

? Latin1 character set ? utf8mb3 character set ? utf8mb4 character set ? Unicode standard

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 5

The integer types

Type

BIGINT INT MEDIUMINT SMALLINT TINYINT

Bytes

8 4 3 2 1

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 6

How the UNSIGNED and ZEROFILL attributes work

Data type

Original value

INT

99

INT

-99

INT UNSIGNED

99

INT UNSIGNED

-99

INT ZEROFILL

99

INT(4) ZEROFILL

99

Value stored

99 -99

99 None

99 99

Value displayed

99 -99

99 None 0000000099 0099

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 7

The fixed-point type

Type

DECIMAL(M, D)

Bytes

Vary

The floating-point types

Type

DOUBLE FLOAT

Bytes

8 4

How the fixed-point and floating-point types work

Data type Original value

DECIMAL(9,2) 1.2

DECIMAL(9,2) 1234567.89 DECIMAL(9,2) -1234567.89

DECIMAL(18,9) 1234567.89

DOUBLE

1234567.89

FLOAT

1234567.89

Value stored

1.20 1234567.89 -1234567.89 1234567.890000000 1234567.89 1234570

Bytes used

5 5 5 8 8 4

? 2019, Mike Murach & Associates, Inc.

Murach's MySQL 3rd Edition

C8, Slide 8

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download