Operation database

Time:2021-1-26

notes

Show databases; -- show all databases, single line comments
/*
hello
world
Multi line comment of SQL
*/

Operation database

Operation database > operation tables in Database > operation data of tables in database
MySQL is case insensitive

Operation database

  1. Create database
create database [if not exists] school;
  1. Delete database
drop database [if exists] school;
  1. Using the database
--Tab key, if the table name or field name is a special character, you need to add``
use `school`;
  1. view the database
show database;

The column type of the database

numerical value

type describe Byte size
tinyint Very small data 1 byte
smallint Smaller data 2 bytes
mediumint Medium sized data 3 bytes
int Standard integer 4 bytes
bigint Big data 8 bytes
float Floating point number 4 bytes
double Double precision 8 bytes (accuracy problem)
decimal Floating point number in string form 2 bytes (half used in financial calculation)

character string

type describe Byte size
char The string has a fixed size, char (3) represents 0 filling bits, independent of storage bits 0~255
varchar Variable string 0 ~ 65535 (commonly used string)
tinytext Micro text 28-1
text Text string 216-1 (save large text)

Time date

java.util.Date

type describe Byte size
date yyyy-MM-dd Date format
time HH : mm : ss Time format
datetime yyyy-MM-dd HH : mm : ss The most commonly used time format
timestamp time stamp Time milliseconds from 1970.1.1 to now
year Year representation

null

  • No value, unknown
  • Note that do not use null for the operation, the result is null

Field properties of database

Unsigned:

  • Signed integer
  • Declare that the column cannot be negative

zerofill:

  • 0 fill
  • The insufficient digits are filled with 0, int (3), 5-005

auto_increment:

  • It is usually understood as self incrementing and automatically gives + 1 on the basis of the previous record (default)
  • It is usually used to design a unique primary key index, which must be an integer type
  • You can customize the starting value and step size of the primary key

Not null and null:

  • If it is set to not null, an error will be reported if it is not assigned a value
  • Null, but do not fill in, the default is null

defalut:

  • Set default
  • Sex, the default value is male, if you do not specify the change column value, there will be a default value!
  • Each table must have the following five fields to indicate the meaning of a record!
ID primary key
`Version ` optimistic lock
is_ Delete pseudo delete
gmt_ Create time
gmt_ Update modification time

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

Recommended Today

Socket communication between Java and python

Code service code, call the corresponding service according to the code Status code, return the status code according to the execution result Using JSON string communication between sockets Java socket client and python socket server Java socket client package com.brewin.hammer.system.util.socket; import java.io.InputStream; import java.io.OutputStream; import java.io.PrintWriter; import java.net.Socket; import java.util.HashMap; import java.util.Map; import com.alibaba.fastjson.JSON; /** […]