Create database table

start

Objective: to create a school database
Create student table (column, field)
Student number int, login password varchar (20), name, gender varchar (2), date of birth (DataTime), home address, email

• format

Create table [if not exists] '`（
    `Field name, column type [attribute] [index] [comment],
    ......
    `Field name, column type [attribute] [index] [comment]
）[table type] [table character set setting] [comment]

create table if not exists `student`(
    `id` int(4) not null auto_ Increase comment 'student number',
    `Name ` varchar (30) not null default 'anonymous' comment' name ',
    `PWD ` varchar (20) not null default '123456' comment 'password',
    `Sex ` varchar (2) not null default 'female' comment 'gender',
    `Birthday ` datetime default null comment 'date of birth',
    `Address ` varchar (100) default null comment 'home address',
    `Email ` varchar (50) default null comment 'mailbox',
    primary key (`id`)
)engine=innodb default charset=utf8;

Show create database school -- view the statements that create the database
Show create table student -- view the definition statement of student data table
Desc student -- show table structure

About database engine

• InnoDB is used by default
• MyISAM was used in earlier years

Regular use:

• MyISAM saves space and is fast
• InnoDB has high security, transaction processing, multi table and multi-user operation

The location of existence in physical space

All database files are stored in the data directory, and a folder corresponds to a database.
The essence is the storage of files!

Differences of MySQL engine in physical files

• InnoDB has only one *. Frm file in the database and the ibdata1 file in the superior directory
• MyISAM file
  • *Definition file of. SDI table structure
  • *. MyD data file (data)
  • *. MYI index file (index)

Set character set encoding for database tables

charset=utf8
If it is not set, it will be MySQL’s default character set encoding. MySQL 5.7 is Latin1, which does not support Chinese. MySQL 8.0 starts with utf8mb4.
staymy.cofConfigure the default encoding in

character-set-server=utf8

This work adoptsCC agreementReprint must indicate the author and the link of this article