MySQL Basics

Create MySQL Database#

  # mysql -u root -p
  mysql> create database test;

  # mysqladmin -u root -p create test

Data Types#

Numeric: Integer, Float, Boolean, String
Date/Time: Date, Time, DateTime
String: String, Binary, Character Set

Numeric Types#

Type	Size	Range (Signed)	Range (Unsigned)	Usage
tinyint	1	-128~127	0~255	Integer
smallint	2	-32768~32767	0~65535	Integer
mediumint	3	-8388608~8388607	0~16777215	Integer
int	4	-2147483648~2147483647	0~4294967295	Integer
bigint	8	-9223372036854775808~9223372036854775807	0~18446744073709551615	Integer
float	4	-3.402823466E+38~3.402823466E+38	-1.79E+308~1.79E+308	Float
double	8	-1.7976931348623157E+308~1.7976931348623157E+308	-2.22E-308~2.22E-308	Float
decimal	The larger of M+2 and D+2 in DECIMAL(M,D)	Depends on the values of M and D	Depends on the values of M and D	Decimal Value

Date and Time Types#

Type	Size	Range	Format	Usage
date	4	1000-01-01~9999-12-31	yyyy-mm-dd	Date
time	8	-838:59:59~838:59:59	hh:mm	Time
datetime	8	1000-01-01 00:00:00~9999-12-31 23:59:59	yyyy-mm-dd hh:mm	DateTime
timestamp	8	1970-01-01 00:00:00~2038-01-19 03:14:07	yyyy-mm-dd hh:mm	Timestamp

String Types#

Type	Size	Usage
char	0~255	Fixed-length string
varchar	0~65535	Variable-length string
tinytext	0~255	Short text string
text	0~65535	Long text string
mediumtext	0~16777215	Medium-length text string
langtext	0~65535	Very long text string
tinyblob	0-255	Binary string with a maximum length of 255 characters
blob	0~65535	Binary long text string
mediumblob	0~16777215	Medium-length binary string
longblob	0~4294967295	Long-length binary string

Tips:

What is the difference between varchar and char
Difference between binary and varbinary

Similar to char and varchar, the difference is that they contain binary strings instead of non-binary strings. Binary strings are strings composed of 0s and 1s, and can be used to store binary files such as images, videos, and audios. They do not have character sets, and sorting and comparison are based on byte values.
Difference between Blob and Text

Blob is a binary string, while Text is a string.

Create Table#

Example:

```sql
# create table
CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `age` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
```

Not null: Must provide input value
Auto increment: Auto-incrementing
Default: Default value

If you want to automatically write the time, you should use the TIMESTAMP type.

Set DEFAULT to CURRENT_TIMESTAMP. It is best to specify it as NULL.
Primary key: Primary key, can define multiple columns as primary key
Engine: Storage engine, default is InnoDB
Charset: Character set, default is utf8mb4

Example:

root@seele # mysql -u root -p

Enter password:******

mysql> Use test;

Database changed

mysql> CREATE TABLE `test` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(255) NOT NULL,
    `age` int(11) NOT NULL,
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Query OK, 0 rows affected (0.00 sec)

Delete Table & Database#

Delete table
```
 DROP TABLE `test`;
```

Delete database

DROP DATABASE `test`;

mysqladmin -u root -p drop test

Delete data and reset auto-increment

ALTER TABLE `test` AUTO_INCREMENT = 1;
// Set the auto-increment to 1, which can solve the auto-increment issue in some cases