In MySQL there are four types of data, namely; Numeric, String, Time / Date and Set Group (set and enum). Here is an explanation of each of these data types.
1. Numeric Data Type
In this type, the data that can be stored is only in the form of numbers. Which can be positive or negative. Here are some types:
| Type | Storage | Minimum Value | Maximum Value |
|-----------|---------|----------------------|----------------------|
| | (Bytes) | (Signed/Unsigned) | (Signed/Unsigned) |
| TINYINT | 1 | -128 | 127 |
| | | 0 | 255 |
| SMALLINT | 2 | -32768 | 32767 |
| | | 0 | 65535 |
| MEDIUMINT | 3 | -8388608 | 8388607 |
| | | 0 | 16777215 |
| INT | 4 | -2147483648 | 2147483647 |
| | | 0 | 4294967295 |
| BIGINT | 8 | -9223372036854775808 | 9223372036854775807 |
| | | 0 | 18446744073709551615 || Type | Storage | Description |
|--------------|--------------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| | (Bytes) | |
| FLOAT(M,D) | 4 | Digunakan untuk menyimpan data berupa bilangan pecahan positif atau negatif presisi tingkat tunggal. (M, ) mewakili panjang bilangan yang ingin ditampilkan, sedangkan ( ,D) mewakili jumlah desimal / angka di belakang koma. Sejumlah floating-point tidak bisa di-unsigned. Contoh: Jika Anda menginputkan nilai 999.00009 ke dalam FLOAT(7,4) maka value yang ditampilkan menjadi 999.0001. |
| DOUBLE(M,D) | 8 | Digunakan untuk menyimpan data berupa bilangan pecahan positif atau negatif presisi tingkat ganda. (M, ) mewakili pangjang bilangan yang ingin ditampilkan, sedangkan ( ,D) mewakili jumlah decimal yang ingin ditampilkan atau penyederhanaan. Sejumlah double floating-point tidak bisa di-unsigned. REAL adalah sinonim untuk DOUBLE. Contoh: s.d.a. |
| BIT(M) | Approximately (M+7)/8 bytes | Digunakan untuk menyimpan nilai bit-field. Suatu BIT(M) memungkinkan penyimpanan nilai M-bit yang berkisar 1-64. Untuk menentukan nilai bit dapat menggunakan notasi b'value', sementara itu 'value' adalah nilai biner (ditulis menggunakan angka 1 atau 0). Contoh: mysql> CREATE TABLE t (b BIT(8)); mysql> INSERT INTO t SET b = b'11111111'; |
| DECIMAL(M,D) | Simak Penjelasan Dibawah ini ! | |DECIMAL and/or NUMERIC is an unpacked floating-point number that cannot be unsigned.
This type is used to maintain a high level of precision and importance, for example monetary data (related to finance or currency).
In MySQL, NUMERIC is implemented as DECIMAL, so statements about DECIMAL also apply to NUMERIC.
DECIMAL and/or NUMERIC value columns are represented using a binary format that packs nine decimal digits (radix 10 or base 10) into 4 bytes. Storage of the integer and fractional portions of the value is determined separately. Each multiple of nine digits requires four bytes, and the "remainder" of the Number requires a fraction of four bytes. The storage required for the "excess digits" or "remainder of the number" is shown in the following table;
| Leftover Digits | Number of Bytes |
|-----------------|-----------------|
| 0 | 0 |
| 1 | 1 |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 3 |
| 6 | 3 |
| 7 | 4 |
| 8 | 4 |Example:
salary DECIMAL(5,2)The SQL standard requires DECIMAL(5,2) for the salary column, meaning it can store any value with five digits and two decimal places, so the values are in the range of -999.99 to 999.99.
2. String Data Type
This type is used to store string-valued data, such as alphanumeric (a mixture of numbers and characters), characters, and numerics without operations, unless converted.
In the table below, M represents the column length declaration for characters, whether binary or nonbinary. While L represents the actual length in bytes of the string value.
| Data Type | Storage Required |
|--------------------------|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| CHAR(M) | M × w bytes, 0 <= M <= 255, di mana w adalah jumlah byte yang diperlukan untuk panjang maksimum karakter di set karakter. Lihat Bagian 15.8.3, "Struktur Fisik Row Tabel InnoDB" untuk informasi tentang kebutuhan penyimpanan tipe data CHAR pada tabel jenis InnoDB. Digunakan untuk menyimpan data string ukuran tetap. Jangkauan: 0 s/d 255 karakter |
| BINARY(M) | M bytes, 0 <= M <= 255 Digunakan untuk menyimpan data string ukuran dinamis. Jangkauan: 0 s/d 255 karakter (versi 4.1), 0 s/d 65.535 (versi 5.0.3) |
| VARCHAR(M), VARBINARY(M) | L + 1 bytes jika kolom nilai memerlukan 0-255 byte, L + 2 bytes jika nilai-nilai dimungkinkan perlu lebih dari 255 byte. |
| TINYBLOB, TINYTEXT | L + 1 bytes, Dimana L < 28 Digunakan untuk menyimpan data text. Jangkauan: 0 s/d 255 karakter (versi 4.1), 0 s/d 65.535 (versi 5.0.3) |
| BLOB, TEXT | L + 2 bytes, Dimana L < 216 Digunakan untuk menyimpan data text. Jangkauan : 0 s/d 65.535 (216 - 1) karakter |
| MEDIUMBLOB, MEDIUMTEXT | L + 3 bytes, Dimana L < 224 Digunakan untuk menyimpan data text. |
| LONGBLOB, LONGTEXT | L + 4 bytes, Dimana L < 232 Digunakan untuk menyimpan data text. |BLOB (Binary)
The blob data type is used to store binary data. This type is usually used to store binary codes from a file or object. BLOB stands for Binary Large Object.
Conclusion
| Data Type | Max Size |
|------------|------------|
| Char | 255 Byte |
| Varchar | 255 Byte |
| Tinytext | 255 Byte |
| Tinyblob | 255 Byte |
| Text | 65535 Byte |
| Blob | 65535 Byte |
| Mediumtext | 1.6 MB |
| Mediumblob | 1.6 MB |
| Longtext | 4.2 GB |
| Longblob | 4.2 GB |3. Time / Date Data Type
This data type stores time information, either in the form of hours or dates. Although the data stored is numeric, the reading is a string, so it needs to be converted if you want to do calculations. Here are the time data types in MySQL.
| Data Type | “Zero” Value | Format |
|-----------|-----------------------|---------------------|
| DATE | '0000-00-00' | YYYY-MM-DD HH:MM:SS |
| TIME | '00:00:00' | YYYY-MM-DD |
| DATETIME | '0000-00-00 00:00:00' | HH:MM:SS |
| TIMESTAMP | '0000-00-00 00:00:00' | YYYY |
| YEAR | 0000 | YYYYMMDDHHMMSS |For TIME, DATETIME, and TIMESTAMP columns, which are created with mySQL versions before 5.6.4 are different from mySQL version 5.6.4. This is because of the advantages of the latest version which allows us to use fractional seconds. Here is the comparison:
| Data Type | Storage Required Before MySQL 5.6.4 | Storage Required as of MySQL 5.6.4 |
|-----------|-------------------------------------|--------------------------------------|
| DATE | 1 byte | 1 byte |
| TIME | 3 bytes | 3 bytes |
| DATETIME | 3 bytes | 3 bytes + fractional seconds storage |
| TIMESTAMP | 8 bytes | 5 bytes + fractional seconds storage |
| YEAR | 4 bytes | 4 bytes + fractional seconds storage |In MySQL 5.6.4, the storage for YEAR and DATE is unchanged. However, TIME, DATETIME, and TIMESTAMP are different. DATETIME is packed more efficiently, requiring 5 - 8 bytes for the nonfractional part, but all three have a fractional part that requires 0 - 3 bytes, depending on the precision of the fractional seconds being stored.
| Fractional Seconds Precision | Storage Required |
|------------------------------|------------------|
| 0 | 0 bytes |
| 1, 2 | 1 byte |
| 3, 4 | 2 bytes |
| 5, 6 | 3 bytes |4. Set Data Types (set and enum)
In addition to the above data types, MySQL also provides other data types. Data types in MySQL may continue to grow along with the development of MySQL versions.
ENUM is a data type that stores a list of choices but only one can be selected / stored. While SET is a data type that is similar to ENUM, but can select more than one.
| Data Type | Description |
|-----------------------------|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| ENUM('value1','value2',...) | 1 atau 2 byte, tergantung pada jumlah nilai enumerasi (65.535 nilai maksimum). Enumerasi adalah pencacahan satu per satu, atau sebuah tipe data yang nilainya hanya terbatas dari pilihan nilai-nilai yang telah didefinisikan terlebih dahulu. Enumerasi merupakan istilah keren untuk daftar, jadi ketika Anda ingin mendefinisikan sebuah ENUM, itu artinya Anda ingin membuat daftar item yang nilainya harus dipilih (atau bisa juga NULL sebagai defaultnya). Misalnya, jika Anda menghendaki bidang inputan berupa "A" atau "B" atau "C", maka Anda perlu mendefinisikan ENUM sebagai ENUM('A','B','C') default NULL (jika perlu). |
| SET('value1','value2',...) | 1, 2, 3, 4, atau 8 byte, tergantung pada jumlah anggota set(himpunan) (64 anggota maksimum). |What is Unique about SQL Language?
The following are unique features that support the ease of learning the SQL language:
- SQL is a non-procedural language; specify "what" information is needed rather than "how" to get that information. In SQL, there is no need to specify a method for accessing data.
- By its very nature, SQL is free-format, meaning that parts of a SQL command do not have to be typed in specific locations on the screen.
- The command structure consists of standard English words, including CREATE TABLE, INSERT, SELECT.
SQL statement examples
Creating a Table
CREATE TABEL Staff(staffNo VARCHAR(5), lName
VARCHAR(15), salary DECIMAL(7,2));Adding New Row
INSERT INTO Staff VALUES('SG16','Brown',8300);Displaying Rows
SELECT staffNo, lName, salary
FROM Staff
WHERE salary > 10000;Reference
- http://www.tutorialspoint.com/mysql/mysql-data-types.htm
- http://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html
- http://dev.mysql.com/doc/refman/5.7/en/bit-type.html
- http://dev.mysql.com/doc/refman/5.7/en/bit-field-literals.html
- http://dev.mysql.com/doc/refman/5.7/en/floating-point-types.html
- http://kbbi.web.id/enumerasi
- http://jagocoding.com/tutorial/248/Enumeration_di_Java
- Ebook: MySQL_From_Beginner_To_Expert -Achmad_Solichin, Achmatim.Net
- http://dev.mysql.com/doc/refman/5.7/en/date-and-time-types.html
Banking Database Record Input Task
Write the following records to each table in the banking database. As an exercise, don't forget before using the database you must give the command use <databasename>.
Fill in the Customer table record
| Idnasabah | Nmnasabah | Alamat |
|-----------|---------------------|-------------------------------|
| 1 | Ali Wardana | Karangkajen Kidul No. 72 |
| 2 | Susiana | Mergangsan Lor RT 02 No. 24 |
| 3 | Jaka Nugraha | Jl. Tamansiswa No. 142 |
| 4 | Fitriana Kusumawati | Jl. Tamansiswa No. 32 |
| 5 | Nilawati | Jl. Diponegoro Kranggan RT 04 |
| 6 | Mulawarman | Karangkajen Kidul RT. 18 |
| 7 | Purwanti | Jl. Tamansiswa No. 59 |
| 8 | Indah Rinawati | Karangkajen Kidul No. 34 |
| 9 | Laila Mirawardani | Jl. Tamansiswa No. 24 |To execute press F5 or the Query Execute button. To see the results you can give the following command:
Select * from nasabahFill in the Branch table record
| Kdcabang | Nmcabang | Alamat |
|----------|-------------------|--------------------------|
| B1 | BPR Cahaya Insani | Karangkajen Kidul No. 12 |
| B2 | BPR Cahaya Mitra | Jl. Tamansiswa No.17 |
| B3 | BPR Cahaya Madani | Mergangsan Lor No. 4 |Fill in the Account table record
| Norek | Kdcabang | Pin | Saldo |
|-------|----------|--------|--------|
| 101 | B1 | 111111 | 250000 |
| 102 | B1 | 222222 | 125000 |
| 103 | B3 | 333333 | 575000 |
| 104 | B1 | 444444 | 300000 |
| 105 | B3 | 555555 | 300000 |
| 106 | B1 | 666666 | 750000 |
| 107 | B2 | 777777 | 525000 |
| 108 | B2 | 888888 | 150000 |
| 109 | B1 | 999999 | 125000 |Contents of the table record Customer_account
| Idnasabah | Norek |
|-----------|-------|
| 1 | 104 |
| 2 | 103 |
| 3 | 105 |
| 3 | 106 |
| 4 | 101 |
| 4 | 107 |
| 5 | 102 |
| 5 | 107 |Fill in the Transaction table record
| Notransaksi | Norek | Idnasabah | jenistran | Tgl | Jml |
|-------------|-------|-----------|-----------|------------|-------|
| 1 | 105 | 3 | Debit | 2003-03-10 | 20000 |
| 2 | 103 | 2 | Debit | 2003-03-10 | 15000 |
| 3 | 101 | 4 | Kredit | 2003-03-12 | 20000 |
| 4 | 106 | 3 | Debit | 2003-03-13 | 20000 |
| 5 | 107 | 5 | Kredit | 2003-03-13 | 35000 |
| 6 | 104 | 1 | Kredit | 2003-03-15 | 15000 |
| 7 | 101 | 4 | Kredit | 2003-03-15 | 35000 |
| 8 | 102 | 5 | Debit | 2003-03-16 | 20000 |
| 9 | 105 | 3 | Kredit | 2003-03-18 | 20000 |
| 10 | 107 | 4 | Debit | 2003-03-19 | 35000 |
| 11 | 103 | 2 | Debit | 2003-03-19 | 15000 |
| 12 | 104 | 1 | Debit | 2003-03-19 | 35000 |
| 13 | 107 | 4 | Kredit | 2003-03-20 | 35000 |
| 14 | 105 | 3 | Debit | 2003-03-21 | 20000 |
| 15 | 104 | 1 | Kredit | 2003-03-22 | 15000 |
| 16 | 101 | 4 | Kredit | 2003-03-22 | 20000 |
| 17 | 103 | 2 | Debit | 2003-03-22 | 15000 |
| 18 | 102 | 5 | Debit | 2003-03-25 | 35000 |
| 19 | 107 | 5 | Debit | 2003-03-26 | 20000 |
| 20 | 106 | 3 | Kredit | 2003-03-27 | 35000 |
| 21 | 103 | 2 | Kredit | 2003-03-28 | 35000 |
| 22 | 105 | 3 | Kredit | 2003-03-28 | 20000 |
| 23 | 102 | 5 | Debit | 2003-03-28 | 35000 |
| 24 | 104 | 1 | Debit | 2003-03-28 | 15000 |
| 25 | 103 | 2 | Debit | 2003-03-29 | 20000 |
| 26 | 101 | 4 | Debit | 2003-03-29 | 20000 |
| 27 | 103 | 2 | Kredit | 2003-03-29 | 20000 |
| 28 | 102 | 5 | Kredit | 2003-03-30 | 35000 |
| 29 | 101 | 4 | Debit | 2003-03-30 | 35000 |
| 30 | 105 | 3 | Debit | 2003-03-30 | 20000 |