Create database table

Time:2021-1-27

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
MYISAM INNODB
Transaction support New version support support
Data row locking I won’t support it support
Foreign key constraints I won’t support it support
Full text index support Support after MySQL 5.6
Table space size less Larger, about 2 times

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

Recommended Today

[data structure] – infix expression evaluation

Title Requirements Input infix expression from keyboard, build stack of operands and operators, calculate and output evaluation result of expression.Basic requirements: implement +, -, *, / four binary operators and (); the range of operands is 0 to 9.Improve the requirements: implement +, – two unary operators (that is, positive and negative signs); the operands […]