[MySQL] detailed explanation of DDL statement: column type, column constraint and self incrementing column_ Unit03

1, How do computers store characters

1. How to store English characters

There are 128 ASCII characters in total, encoding English letters and their symbols. If a b c is 97 98 99
Latin-1 has 256 in total. It encodes European characters and is compatible with ASCII

2. How to store Chinese characters

GB2312 encodes more than 6000 commonly used Chinese characters and is compatible with ASCII
GBK encodes more than 20000 Chinese characters and is compatible with GB2312
BIG5 Taiwan traditional character code, ASCII compatible
Unicode encodes the commonly used languages in the world's mainstream countries and is compatible with ASCII, including three storage schemes UTF-8 UTF-16 UTF-32

Extension: 🔗

GBK and UTF-8, UTF-16
(1) GBK code, one Chinese character takes up two bytes.
(2) UTF-16 encoding, usually Chinese characters account for two bytes, and Chinese characters in CJKV extended area B, extended area C and extended area D account for four bytes (the Unicode range of general characters is U+0000 to U+FFFF, and the range of these extended parts is greater than U+20000, so two UTF-16 are used).
(3) UTF-8 coding is a variable length coding. Generally, Chinese characters account for three bytes and Chinese characters after expanding area B account for four bytes.

Extension: 🔗

Briefly introduce the differences between GBK and UTF-8 Codes:
(1) GBK Code: refers to Chinese characters. Other characters include simplified Chinese and traditional Chinese characters. There is another character "GB2312", which can only store simplified Chinese characters.
(2) UTF-8 Code: it is a code adopted by the whole country. If your website involves languages from multiple countries, it is recommended that you choose UTF-8 code.

3. Solve mysql Chinese garbled code

MySQL uses latin-1 encoding by default

solve:

(1) The script file is saved as UTF-8

(2) The code of the client connecting to the server is UTF-8

# Cannot add after UTF-
SET  NAMES  UTF8;

(3) The code used by the server to create the database is UTF-8

# Cannot add after UTF-
CREATE  DATABASE  xz  CHARSET=UTF8;

2, Column type

When creating a data table, the data type that the specified column can store

CREATE  TABLE  n1(
   nid  Column type
);

1. Numerical type - quotation marks can be added or not

tinyint micro integer, accounting for 1 byte, range - 128 ~ 127

(positive range 0 ~ 127 negative range - 1 ~ - 128)

🌱Tips:
The maximum number is 7 1s and the minimum number is 7 0s (the maximum number is 8 1s and the minimum number is 8 0s. Since one digit should be reserved as the sign bit, that is, to store the sign, the maximum number is 7 1s and the minimum number is 7 0s)

smallint small integer, accounting for 2 bytes, range - 32768 ~ 32767

(positive range 0 ~ 32767 negative range - 1 ~ - 32768)

int integer, accounting for 4 bytes, range - 2147483648 ~ 2147483647

(positive range 0 ~ 2147483647 negative range - 1 ~ - 2147483648)

Bigint large integer, accounting for 8 bytes, with a large range

float single precision floating-point type takes up 4 bytes, and the range is much larger than int. at the expense of the number after the decimal point, the accuracy will be affected. The maximum value is 3.4E38, that is, the 38th power of 3.4 * 10

Double double double precision floating-point type, accounting for 8 bytes. The range is much larger than bigint, and the accuracy will also be affected

decimal (M,D) fixed-point decimal. The decimal point position will not change, accounting for 8 bytes. M represents the total significant digits, and D represents the significant digits after the decimal point

boolean/bool Booleans usually have only two values, which are true or false. They often store data with only two values, such as gender, online... When used, it will be changed to tinyint, true to 1 and false to 0

true and false are keywords and cannot be quoted

2. Date time type - must be quoted

Date time type must be quoted, otherwise it will be regarded as subtraction operation, 2020-08-03 is 2009, and the defined date type will be disordered as 0000-00-00

Date date type 2020-08-03

time type 15:20:30

datetime date time type 2020-08-03 15:20:30

3. String type - must be quoted

varchar(M) variable length string will hardly waste space, and the data operation speed is relatively slow. The maximum value of M is 65535. It is often used to store strings with variable length, such as article title, content, a person's name, home address

char(M) fixed length strings may generate space waste, data operation speed is relatively fast, M's maximum value is 255, and it is commonly used in fixed length data, such as a person's phone number, ID number...

text(M) large variable length string. The maximum value of M is 2G

Number of characters	char(5)	Varchar(5)
a	aA \ 0 \ 0 \ 0 \ 0 A \ 0 \ 0 \ 0 \ 0 A \ 0 \ 0 \ 0 \ 0 A \ 0 \ 0 \ 0 \ 0 (4 spaces need to be added)	aA \ 0 (just add a space)
ab	ab\0\0\0	ab\0
abcde	abcde	abcde
See you on Monday	See you Monday \ 0 \ 0	See you Monday \ 0

Spaces in Mysql are represented by \ 0

Digression:
1TB MB KB byte bit
Each of the above levels is 1024

Bit: the computer can only store 1 or 0, the so-called binary number

1byte = 8bit (bit)

Binary consists of 1 or 0
1 2 3 4 5 decimal number
1 10 11 100 101 binary number (every 1)

Floating point type, float, double, as follows:
123456.789E-1
12345.6789
1234.56789E1
123.456789E2

Fixed point decimal, e.g. 3.14

Select a reasonable column type, for example:

CREATE  TABLE  t1(
Id INT, # For example, Jingdong's commodity number will be very long after years of operation
age TINYINT,
phone  CHAR(11),
sex BOOLEAN
price DECIMAL(7,2), # 99999.99
ctime data
);

ex: script file 02_xuezi.sql, discard first and then create the database Xuezi, set the code to UTF-8, enter the database and create a table laptop for saving commodity data, including number lid, title, price, inventory stockCount, shelfTime, and whether it is isIndex recommended on the home page; Insert several pieces of data and query the data in interactive mode.

#Set client connection server code
SET NAMES UTF8;
#Discard the database if it exists
DROP DATABASE IF EXISTS xuezi;
#Create a new database and set the storage code
CREATE DATABASE xuezi CHARSET=UTF8;
#Enter the database
USE xuezi;
#Create a table to save item data
CREATE TABLE laptop(
  lid INT PRIMARY KEY,
  title VARCHAR(64),
  price DECIMAL(7,2) NOT NULL,  #99999.99
  stockCount SMALLINT,
  shelfTime DATE,
  isIndex BOOLEAN
);
#insert data
INSERT INTO laptop VALUES('4','millet Air','3199','100','2020-8-1',true);
INSERT INTO laptop VALUES('2','alien','56000','12','2020-1-1',false);
INSERT INTO laptop VALUES(1,'ThinkpadE470',3199,6,'2016-10-1',NULL);
INSERT INTO laptop VALUES('3','Dell combustion 7000','4199','200','2019-10-1','130');

3, Column constraint

mysql can perform specific verification on the data to be inserted. It can only be inserted if the conditions are met, otherwise it is considered illegal. For example, it is forbidden to insert duplicate numbers, the gender can only be male or female, and the salary can only be positive

CREATE  TABLE  t1( lid  INT Column constraint );

1. Primary key constraint - primary key

Duplicate values are not allowed on columns that declare primary key constraints. There can only be one primary key constraint in a table. Generally, loading numbered columns will speed up the search of data. When querying, it will be sorted from small to large according to the number. After the primary key constraint is declared, the primary key constraint prohibits the insertion of NULL. If Auto increment column is set_ Increment, NULL can be inserted into the primary key constraint column

NULL means NULL. When inserting data, it means that the value cannot be determined. For example, the price of a commodity cannot be determined. Whether it is recommended by the home page
NULL is a keyword and cannot be quoted.

2. Non NULL constraint - NOT NULL

Inserting NULL is prohibited on columns that declare non NULL constraints

ex: script file xz SQL, discard it first, then create the database xz, enter the database, and set the code to UTF8; Create a family table to save notebook classification, including fid and classification name fname; Insert the following data: 10 Lenovo, 20 Dell, 30 Xiaomi. Create a table latop to save notebook data, including lid, title, price, spec, detail, shelf time, shelfTime, is on sale or not, and its classification number is familyld; Insert several pieces of data

#Set the encoding of the client connection to the server
SET NAMES UTF8;
#Discard the database if it exists
DROP DATABASE IF EXISTS xz;
#Create a new database and set the storage code
CREATE DATABASE xz CHARSET=UTF8;
#Enter the database
USE xz;
#Create a table to save notebook family categories
CREATE TABLE family(
  fid INT PRIMARY KEY,
  fname VARCHAR(8) DEFAULT 'unknown'
);
#insert data
INSERT INTO family VALUES
(10,'association'),
(20,'Dale'),
(30,'millet');
INSERT INTO family VALUES(40,'Huawei');
INSERT INTO family VALUES(50,NULL);
INSERT INTO family VALUES(60,NULL);
INSERT INTO family VALUES(70,DEFAULT);
INSERT INTO family(fid) VALUES(80);
#Create a table to save notebook data
CREATE TABLE laptop(
  lid INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(64) UNIQUE NOT NULL,
  price DECIMAL(7,2) DEFAULT 3000,  #99999.99
  spec VARCHAR(32),
  detail VARCHAR(5000),
  shelfTime DATE,
  isOnsale BOOLEAN,
  familyId INT,
  #Set familyId as the foreign key constraint, and go to the fid in the classification table family to find it. If it does not appear, an error will be reported
  foreign key(familyId) references family(fid)
);
#insert data
INSERT INTO laptop VALUES(1,'millet Air',3799,'Flagship Edition','Detail 1','2020-5-1',1,30);
INSERT INTO laptop VALUES(2,'ThinkpadE470','2899','Entry Edition','Detail 2','2018-1-1',0,10);
INSERT INTO laptop VALUES(3,'Spirit burning 7000','4199','Business Edition','Detail 3','2019-12-1',1,20);
INSERT INTO laptop VALUES(4,'Lingyueyan 8000','5199','Business Edition 2','Details 4','2018-12-1',0,20);
INSERT INTO laptop VALUES(5,'Xiaoxin 500',DEFAULT,DEFAULT,'Details 5','2018-12-1',0,10);
INSERT INTO laptop(lid,title) VALUES(56,'millet Pro');
INSERT INTO laptop(lid,title) VALUES(NULL,'millet Pro2');
INSERT INTO laptop(lid,title) VALUES(NULL,'millet Pro3');

3. unique constraint

On the column with unique constraint declared, duplicate values are not allowed, and NULL or even multiple nulls are allowed to be inserted (because NULL means NULL, which is unknown); Unique constraints may affect the default sorting of queries
Exercise: in the notebook table laptop, set the title as the unique constraint and insert the data test

4. Default value constraint - default

Columns that do not appear apply default values

You can use the default keyword to set the default value. There are two specific application methods

(1) INSERT INTO laptop VALUES(1, 'Xiaomi 1', DEFAULT,...);

#To insert data into all columns in the table, you can directly ignore the column name and directly adopt the INSERT INTO table name values (value...)

#If the corresponding field in the data table has no assignment, the keyword set DEFAULT must be written

#If the DEFAULT constraint is set for this field in the table, the values value corresponding to this field is the DEFAULT value set

#If the DEFAULT constraint is not set for this field in the table, the values value corresponding to this field is the DEFAULT NULL value

(2) INSERT INTO laptop(lid,title) VALUES(2, 'Xiaomi 2';

#Specify column (field) insertion method: columns (fields) that do not appear will be applied with DEFAULT VALUES (NULL by DEFAULT, unless the DEFAULT constraint value is # set). The corresponding VALUES value does not need to be written with DEFAULT

Extension: 🔗

Three ways to insert data into a database

In the database, insert data into the database and use the insert into keyword. There are three ways to insert data into a database.

1) Inserting a single field

INSERT INTO table_name(Listing) VALUES(value);

2) Insert multiple fields

2.1) insert more than two fields

INSERT INTO table_name (Column 1, column 2, column 3,)  VALUES(Value 1,Value 2, value 3);

2.2) insert all fields

INSERT INTO table_name  VALUES(Value 1, value 2, value 3); # The value should be consistent with the field order and type in the table

3) Insert data into a table using a subquery

# It should be noted that table cannot be violated_ Name constraints, and needs and table_ The field of name is consistent or less than it, but not more than it
INSERT INTO table_name Subquery select sentence;

Extension: 🔗

About DEFAULT settings

1) Set default values

Use the DEFAULT keyword to set the DEFAULT value constraint. The specific syntax rules are as follows:
< field name > < data type > Default < default >;

2) Modify the default values in the table

< field name > < data type > Default < modified default value >;

3) Delete default constraint

To delete is to set the default value to null
< field name > < data type > Defau lt;

5. Check constraints

It is also called user-defined constraint, but mysql does not support it, which will affect (reduce) the speed of data insertion and may cause great pressure on the server

CREATE TABLE student(
score TINYINT  CHECK(score>=0  AND  score<=100)
);

6. Foreign key constraints

For columns that declare foreign key constraints, the value range is taken from the primary key column of another table, and NULL can be inserted. The column types of foreign key columns and corresponding primary key columns should be consistent.

Foreign key constraints are often used to establish an association between two tables

Tips:

1) Must be the primary key of another table;

2) The column type must be the same as that of another table;

3) You need to add a comma to write to the next line.

For a column that declares a foreign key constraint, the value must appear on the primary key column of another table. The column types of the two must be consistent. NULL or multiple nulls are allowed

Usage: FOREIGN KEY REFERENCES data table (primary key column)

FOREIGN  KEY (Foreign key column)  REFERENCES  Another table(Primary key column)

4, Auto increment

AUTO INCREMENT automatically grows. If AUTO INCREMENT column is set, it only needs to be set to NULL when inserting data, and the current maximum value will be obtained and then inserted by 1.

🌱Tips:

1) Self incrementing columns can only be added to integer type primary key columns;

2) Auto increment column allows manual assignment;

3) After manual assignment, if the intermediate jump is missing, it will continue to increase according to the maximum value;

4) It is easy to use. You can write NULL directly on it, and the number will be automatically added by 1;

Automatic growth: if a column declares a self incrementing column, it does not need to be assigned manually. If it is directly assigned to NULL, it will obtain the current maximum value, and then add 1 to insert

ex: script file 01_tedu.sql, discard it first, and then create the database Tedu, set the code to UTF-8, enter the database, and create a table dept that stores department data, including department number did (primary key, auto increment column), department name dName (unique constraint), and insert the following data:
10 R & D department 20 marketing department 30 operation Department 40 testing department
Create a table emp to save employee data, including ID Eid (primary key, auto increment column), employee name ename, gender sex (default value constraint 1), birthday, salary (non empty constraint), department number deptid (foreign key constraint), and insert several pieces of data.

#Set the encoding of the client connection to the server
SET NAMES UTF8;
#Discard the database if it exists
DROP DATABASE IF EXISTS tedu;
#Create a new database and set the storage code
CREATE DATABASE tedu CHARSET=UTF8;
#Enter the database
USE tedu;
#Create a table to save Department data
CREATE TABLE dept(
  did INT PRIMARY KEY AUTO_INCREMENT,
  dname VARCHAR(8) UNIQUE
);
#insert data
INSERT INTO dept VALUES(10,'R & D department');
INSERT INTO dept VALUES(20,'Marketing Department');
INSERT INTO dept VALUES(30,'Operation Department');
INSERT INTO dept VALUES(40,'Testing department');
#Create a table to save employee data
CREATE TABLE emp(
  eid INT PRIMARY KEY AUTO_INCREMENT,
  ename VARCHAR(8),
  sex BOOLEAN DEFAULT 1,
  birthday DATE,
  salary DECIMAL(7,2) NOT NULL,  #99999.99
  deptId INT,
  FOREIGN KEY(deptId) REFERENCES dept(did)
);
#insert data
INSERT INTO emp VALUES(NULL,'Tom',1,'1990-5-5',6000,20);
INSERT INTO emp VALUES(NULL,'Jerry',0,'1991-8-20',7000,10);
INSERT INTO emp VALUES(NULL,'David',1,'1995-10-20',3000,30);
INSERT INTO emp VALUES(NULL,'Maria',0,'1992-3-20',5000,10);
INSERT INTO emp VALUES(NULL,'Leo',1,'1993-12-3',8000,20);
INSERT INTO emp VALUES(NULL,'Black',1,'1991-1-3',4000,10);
INSERT INTO emp VALUES(NULL,'Peter',1,'1990-12-3',10000,10);
INSERT INTO emp VALUES(NULL,'Franc',1,'1994-12-3',6000,30);
INSERT INTO emp VALUES(NULL,'Tacy',1,'1991-12-3',9000,10);
INSERT INTO emp VALUES(NULL,'Lucy',0,'1995-12-3',10000,20);
INSERT INTO emp VALUES(NULL,'Jone',1,'1993-12-3',8000,30);
INSERT INTO emp VALUES(NULL,'Lily',0,'1992-12-3',12000,10);
INSERT INTO emp VALUES(NULL,'Lisa',0,'1989-12-3',8000,10);
INSERT INTO emp VALUES(NULL,'King',1,'1988-12-3',10000,10);
INSERT INTO emp VALUES(NULL,'Brown',1,'1993-12-3',22000,NULL);

To review the previous unit, you can click 👉 [MySQL] common SQL commands_ Unit02

Tags: MySQL Database SQL Oracle

Posted by jtapoling on Mon, 18 Apr 2022 21:08:05 +0930

Programs Wiki

[MySQL] detailed explanation of DDL statement: column type, column constraint and self incrementing column_ Unit03

1, How do computers store characters

1. How to store English characters

2. How to store Chinese characters

3. Solve mysql Chinese garbled code

2, Column type

1. Numerical type - quotation marks can be added or not

2. Date time type - must be quoted

3. String type - must be quoted

3, Column constraint

1. Primary key constraint - primary key

2. Non NULL constraint - NOT NULL

3. unique constraint

4. Default value constraint - default

5. Check constraints

6. Foreign key constraints

4, Auto increment

Popular Tags