Made by Mike_Zhang

1. Abstract

This post assumes that you have already known the basics of relational database model. This post will introduce the basic concepts of SQL¹, some SQL syntax, SQL data types in MySQL, as well as Table in MySQL. Regarding the SQL basic grammar (e.g. Query statements), the next post will have more detailed discussions.

2. SQL Introduction

SQL is a programming language for manipulating data in a relational database as well as various database technologies ¹.

According to Beaulieu¹, “SQL” is NOT an acronym for “Structured Query Language”, and we should say its three letters (i.e. “S”, “Q”, “L”) or the word sequel.
However, I think considering it as an acronym is also appropriate as long as it is reasonable.

Three parts of SQL language:

SQL schema statements
- Define the data types and structures in the database;
SQL data statements
- Manipulate the data in the defined database;
SQL transaction statements
- Begin, end, and roll back transactions.

(This SQL Learning Topic will mainly focus on the first two parts of SQL language.)

[Example]

Using SQL schema statement to create a table named “person”, having person_id, fname, and birth_date as columns where person_id is an primary key of the table.

CREATE TABLE person (person_id SMALLINT UNSIGNED, 
                    fname VARCHAR(20), 
                    birth_date DATE, 
CONSTRAINT pk_person PRIMARY KEY (person_id));

[Example]
Using SQL data statements to modify and insert a person’s data into the person table.

1 2	`INSERT INTO person (person_id, fname, birth_date) VALUE(null, 'Mike', '2001-07-01');`

As well as to retrieve data from the person table.

1
2
3

SELECT fname 
FROM person
WHERE birth_date = '2001-07-01';

Output:

+------------------------+
| fname                  | 
+------------------------+ 
| Mike                   | 
+------------------------+

SQL is a nonprocedural language ¹.

Procedural language, like Java, Python, etc, defines the desired results and the algorithms or processes.

While SQL as a nonprocedural language, only defines the desired results (output), but leaves the algorithms or processes to others, which means that we do not need to concern how to CREATE or SELECT the data.

Data structure or data type is one of the most important concepts in almost all programming language. So do SQL. Before SQL making changes on the database (tables), first we need tables. So we have to define what kind of data we want to store in the tables.

Next we are going to discuss the data types in SQL, defined by the SQL schema statements.

3. MySQL Data Types

Before apply SQL to the database, first we need database and its serves, like MySQL, Oracle Database, SQL Server, DB2, etc. MySQL will be used as the example to implement the SQL statements.

Three major data types in MySQL:

Character Data
Numeric Data
Temporal Data

3.1 Character Data

Character data is stored in fixed-length strings or variable-length strings.

fixed-length strings have right-padded spaces with same numbers of characters;
variable-length strings have NO right-padded spaces with different numbers of characters.

[Example]

Storing string up to 10 characters in a fixed-length string column.

1 2	`c1 CHAR(10) /* fixed-length / c2 VARCHAR(20) / variable-length */`

Maximum length of CHAR is 255 bytes;
Maximum length of VARCHAR is 65,535 bytes.

All string data type in MySQL ²:

CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM, and SET.

Character sets in MySQL:

mysql> SHOW CHARACTER SET;
+----------+---------------------------------+---------------------+--------+
| Charset  | Description                     | Default collation   | Maxlen |
+----------+---------------------------------+---------------------+--------+
| armscii8 | ARMSCII-8 Armenian              | armscii8_general_ci |      1 |
| ascii    | US ASCII                        | ascii_general_ci    |      1 |
| big5     | Big5 Traditional Chinese        | big5_chinese_ci     |      2 |
| binary   | Binary pseudo charset           | binary              |      1 |
| cp1250   | Windows Central European        | cp1250_general_ci   |      1 |
| cp1251   | Windows Cyrillic                | cp1251_general_ci   |      1 |
| cp1256   | Windows Arabic                  | cp1256_general_ci   |      1 |
| cp1257   | Windows Baltic                  | cp1257_general_ci   |      1 |
| cp850    | DOS West European               | cp850_general_ci    |      1 |
| cp852    | DOS Central European            | cp852_general_ci    |      1 |
| cp866    | DOS Russian                     | cp866_general_ci    |      1 |
| cp932    | SJIS for Windows Japanese       | cp932_japanese_ci   |      2 |
| dec8     | DEC West European               | dec8_swedish_ci     |      1 |
| eucjpms  | UJIS for Windows Japanese       | eucjpms_japanese_ci |      3 |
| euckr    | EUC-KR Korean                   | euckr_korean_ci     |      2 |
| gb18030  | China National Standard GB18030 | gb18030_chinese_ci  |      4 |
| gb2312   | GB2312 Simplified Chinese       | gb2312_chinese_ci   |      2 |
| gbk      | GBK Simplified Chinese          | gbk_chinese_ci      |      2 |
| geostd8  | GEOSTD8 Georgian                | geostd8_general_ci  |      1 |
| greek    | ISO 8859-7 Greek                | greek_general_ci    |      1 |
| hebrew   | ISO 8859-8 Hebrew               | hebrew_general_ci   |      1 |
| hp8      | HP West European                | hp8_english_ci      |      1 |
| keybcs2  | DOS Kamenicky Czech-Slovak      | keybcs2_general_ci  |      1 |
| koi8r    | KOI8-R Relcom Russian           | koi8r_general_ci    |      1 |
| koi8u    | KOI8-U Ukrainian                | koi8u_general_ci    |      1 |
| latin1   | cp1252 West European            | latin1_swedish_ci   |      1 |
| latin2   | ISO 8859-2 Central European     | latin2_general_ci   |      1 |
| latin5   | ISO 8859-9 Turkish              | latin5_turkish_ci   |      1 |
| latin7   | ISO 8859-13 Baltic              | latin7_general_ci   |      1 |
| macce    | Mac Central European            | macce_general_ci    |      1 |
| macroman | Mac West European               | macroman_general_ci |      1 |
| sjis     | Shift-JIS Japanese              | sjis_japanese_ci    |      2 |
| swe7     | 7bit Swedish                    | swe7_swedish_ci     |      1 |
| tis620   | TIS620 Thai                     | tis620_thai_ci      |      1 |
| ucs2     | UCS-2 Unicode                   | ucs2_general_ci     |      2 |
| ujis     | EUC-JP Japanese                 | ujis_japanese_ci    |      3 |
| utf16    | UTF-16 Unicode                  | utf16_general_ci    |      4 |
| utf16le  | UTF-16LE Unicode                | utf16le_general_ci  |      4 |
| utf32    | UTF-32 Unicode                  | utf32_general_ci    |      4 |
| utf8mb3  | UTF-8 Unicode                   | utf8_general_ci     |      3 |
| utf8mb4  | UTF-8 Unicode                   | utf8mb4_0900_ai_ci  |      4 |
+----------+---------------------------------+---------------------+--------+

3.2 Numeric Data

3.2.1 Integer

MySQL integer types ¹:

Type	Singed Range	Unsigned Range	Bytes
`TINYINT`	$-128$ to $127$	$0$ to $255$	1
`SMALLINT`	$-32768$ to $32767$	$0$ to $65535$	2
`MEDIUMINT`	$-8388608$ to $8388607$	$0$ to $16777215$	3
`INT`	$-2147483648$ to $2147483647$	$0$ to $4294967295$	4
`BIGINT`	$-2^{63}$ to $2^{63}-1$	$0$ to $2^{64}-1$	8

3.2.2 Floating-point

MySQL floating-point types ¹:

Type	Range
`FLOAT(p, s)`	$−3.402823466E+38$ to $−1.175494351E-38$ and $1.175494351E-38$ to $3.402823466E+38$
`DOUBLE(p, s)`	$−1.7976931348623157E+308$ to $−2.2250738585072014E-308$ and $2.2250738585072014E-308$ to $1.7976931348623157E+308$

Where,

p for precision, indicating the total number of digits to the left and right of the decimal point;
s for scale, indicating the number of digits to the right of the decimal point.

3.3 Temporal Data

This is the data type for storing date and time values.

MySQL temporal types ¹:

Type	Format	Range
`DATE`	YYYY-MM-DD	$1000-01-01$ to $9999-12-31$
`DATETIME`	YYYY-MM-DD HH:MI:SS	$1000-01-01 00:00:00.000000$ to $9999-12-31 23:59:59.999999$
`TIMESTAMP`	YYYY-MM-DD HH:MI:SS	$1000-01-01 00:00:00.000000$ to $9999-12-31 23:59:59.999999$
`YEAR`	YYYY	$1901$ to $2155$
`TIME`	HHH:MI:SS	$-838:59:59.000000$ to $838:59:59.000000$

Date format components ¹:

Component	Definition	Range
`YYYY`	Year	$1000$ to $9999$
`MM`	Month	$01$ to $12$
`DD`	Day	$01$ to $31$
`HH`	Hour	$00$ to $23$
`HHH`	Hours (elapsed)	$-838$ to $838$
`MI`	Minute	$00$ to $59$
`SS`	Second	$00$ to $59$

4. MySQL Table

After the definition of the above data types, we can define the table by using these data types.

[Example]
We are going to design a table storing personal information, including

info	Type	Values
person_id	`SMALLINT`
first_name	`VARCHAR(20)`
last_name	`VARCHAR(20)`
eye_color	`CHAR(2)`	`BR`, `BL`, `GR`
birth_date	`DATE`
street	`VARCHAR(40)`
city	`VARCHAR(40)`
state	`VARCHAR(40)`
country	`VARCHAR(40)`
postal_code	`VARCHAR(20)`

This design is based on the concept of normalization, which will be discussed in the further posts.

4.1 Creating Tables

Using SQL Schema Statements to create tables.

mysql> CREATE TABLE person (
    person_id SMALLINT UNSIGNED, 
    fname VARCHAR(20), 
    lname VARCHAR(20), 
    eye_color ENUM('BR','BL','GR'), 
    birth_date DATE, 
    street VARCHAR(20), 
    city VARCHAR(20), 
    state VARCHAR(20), 
    country VARCHAR(20), 
    post_code VARCHAR(20), 
    CONSTRAINT pk_person PRIMARY KEY (person_id) /* primary key constraint */
);

Turn on the auto-increment feature primary key column.

1	`mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;`

describe or desc command can be used to check the table definition.

mysql> desc person;

+------------+----------------------+------+-----+---------+----------------+
| Field      | Type                 | Null | Key | Default | Extra          |
+------------+----------------------+------+-----+---------+----------------+
| person_id  | smallint unsigned    | NO   | PRI | NULL    | auto_increment |
| fname      | varchar(20)          | YES  |     | NULL    |                |
| lname      | varchar(20)          | YES  |     | NULL    |                |
| eye_color  | enum('BR','BL','GR') | YES  |     | NULL    |                |
| birth_date | date                 | YES  |     | NULL    |                |
| street     | varchar(20)          | YES  |     | NULL    |                |
| city       | varchar(20)          | YES  |     | NULL    |                |
| state      | varchar(20)          | YES  |     | NULL    |                |
| country    | varchar(20)          | YES  |     | NULL    |                |
| post_code  | varchar(20)          | YES  |     | NULL    |                |
+------------+----------------------+------+-----+---------+----------------+
10 rows in set (0.00 sec)

Finally, the person table is created. Now we can manipulate the table.

4.2 Modifying Tables

4.2.1 Inserting

Three components in the insert statement:

The name of the table to insert into;
The name of the columns to insert into;
The values to insert into the columns.

There is no need to provide data to every column for each inserted data unless the column defined as not null.
For the auto_increment primary key, just provide a null value for increasing automatically.

Using SQL data statements to insert data into the table.

1
2
3

mysql> INSERT INTO [table_name] 
        ([col_name_1], ..., [col_name_n]) 
        VALUES ([value1], ..., [value_n]);

[Example]

mysql> INSERT INTO person 
        (person_id, fname, lname, eye_color, birth_date) 
        VALUES (null, 'Mike','Zhang','BR','2001-07-01');

mysql> INSERT INTO person 
        (person_id, fname, lname, eye_color, birth_date) 
        VALUES(null, 'Wengyu','Zhang','BR','2001-07-01');

Using SELECT to check the table:

mysql> SELECT * FROM person;
+-----------+--------+-------+-----------+------------+--------+------+-------+---------+-----------+
| person_id | fname  | lname | eye_color | birth_date | street | city | state | country | post_code |
+-----------+--------+-------+-----------+------------+--------+------+-------+---------+-----------+
|         1 | Mike   | Zhang | BR        | 2001-07-01 | NULL   | NULL | NULL  | NULL    | NULL      |
|         2 | Wengyu | Zhang | BR        | 2001-07-01 | NULL   | NULL | NULL  | NULL    | NULL      |
+-----------+--------+-------+-----------+------------+--------+------+-------+---------+-----------+

4.2.2 Updating

Using SQL data statements to update data into the table.

mysql> UPDATE [table_name] 
        SET [col_name_1] = [value_1], 
        ..., 
        [col_name_n] = [value_n] 
        WHERE [condition];

[Example]

mysql> UPDATE person
        SET street = 'FT15',
        city = 'HK'
        WHERE person_id = 1; /* set the condition with primary key */

Using SELECT to check the table:

mysql> SELECT * FROM person3;
+-----------+--------+-------+-----------+------------+--------+------+-------+---------+-----------+
| person_id | fname  | lname | eye_color | birth_date | street | city | state | country | post_code |
+-----------+--------+-------+-----------+------------+--------+------+-------+---------+-----------+
|         1 | Mike   | Zhang | BR        | 2001-07-01 | FT15   | HK   | NULL  | NULL    | NULL      |
|         2 | Wengyu | Zhang | BR        | 2001-07-01 | NULL   | NULL | NULL  | NULL    | NULL      |
+-----------+--------+-------+-----------+------------+--------+------+-------+---------+-----------+

4.2.3 Deleting

Using SQL data statements to update data into the table.

1 2	`mysql> DELETE FROM [table_name] WHERE [condition];`

[Example]

1 2	`mysql> DELETE FROM person WHERE person_id = 2;`

Using SELECT to check the table:

mysql> SELECT * FROM person3;
+-----------+-------+-------+-----------+------------+--------+------+-------+---------+-----------+
| person_id | fname | lname | eye_color | birth_date | street | city | state | country | post_code |
+-----------+-------+-------+-----------+------------+--------+------+-------+---------+-----------+
|         1 | Mike  | Zhang | BR        | 2001-07-01 | FT15   | HK   | NULL  | NULL    | NULL      |
+-----------+-------+-------+-----------+------------+--------+------+-------+---------+-----------+

References

¹. A. Beaulieu, LEARNING SQL: master sql fundamentals. S.L.: O’reilly Media, Inc, Usa, 2020. ↩

². “MySQL :: MySQL 8.0 Reference Manual :: 11.3.1 String Data Type Syntax,” dev.mysql.com. https://dev.mysql.com/doc/refman/8.0/en/string-type-syntax.html (accessed Aug. 09, 2022). ↩

‌

Outro

In the next post, we will discuss SQL’s Query statement, which is the SELECT statement, as well as filtering.

初次接触SQL，相关的知识会继续学习，继续更新.
最后，希望大家一起交流，分享，指出问题，谢谢！

原创文章，转载请标明出处
Made by Mike_Zhang

感谢你的支持

Programming > SQL

#学习 #SQL

SQL Learning 1 - Intro, Data Types & Table

https://ultrafish.io/post/sql-learning-1/

Author

Mike_Zhang

Posted on

August 9, 2022

Licensed under

SQL Learning 2 - Query Introduction Previous

康威生命游戏实现 - Conway's Game of Life Implementation Next