"Java" Java Learning path -03-mysql (ii)

MySQL database day02

TIP: In the Windows command line, Chinese input error problem:

The MySQL database needs to know what encoding the client uses to make the correct encoding conversion.
Set names GBK;
Tell the server that you are GBK encoded. (Window client is GBK encoded, Linux client is UTF8 encoded)

First, the data type
Unsigned tags, add unsigned to indicate unsigned.
Zerofill tags, for example: Int (4) Zerofill; displaying less than 4 digits will fill 0, exceeding 4 normal usage.

1. Digital
A.tinyint1 bytes
B.smalint2 bytes
C.int 4 bytes
Int (3) The number in parentheses only affects the query display format, without affecting the range of numbers

D.bigint8 bytes
E.float4 bytes
F.double8 bytes
Double (6,2) does not affect the range, it only affects the display

G.decimal/numeric fixed-point floating-point number, can do a precise operation, generally means "money"
Decimal (6,2) represents an integer 4 bits, a decimal 2 bits, and a total of 6 bits.
Integer out of range, may error, decimal out of range, rounding

2. String
A.char (20) fixed length string
Out-of-length may or may not be truncated (to be determined based on database settings)
If the length is insufficient, it will fill the space
255 maximum
Fixed-length strings are more efficient

B.varchar (20) variable length string
The maximum length is 255, and a byte is required in front to indicate the length
Maximum length of more than 255, two bytes in front to indicate length
A maximum of 65,535 bytes
Typically use varchar to save a string within 255

C.text
65535 bytes;
10 bytes that occupy only the total number of bytes in a table

D.blob
Super-Large Object data
To use a stream to read and write BLOB field data
Usually save text without blobs

3. Date and Time
A.datetime Days and seconds
B.date Month Day
C.time hours and seconds
D.timestamp
Time stamp, usually not used
Month and day time
Max only by 2038
When you modify a row of data, the first timestamp field is automatically updated to the current time of the system
Cannot take null value, fill in null value, will automatically fill in the system current time

Second, the constraint:

Limit the value of a field

1. PRIMARY KEY constraint
Uniquely identifies a row of data
Unique, non-repeating
Non-NULL, cannot take null value
Automatically generate indexes
Typically, instead of using business data as a primary key, use unintentional data

(1) Adding a primary key
A. Adding when creating a new table
Method One:
CREATE TABLE TB1 (
ID int PRIMARY KEY,
...
);
Method Two:
CREATE TABLE TB1 (
ID int,
...
Primary KEY (ID)
);
Method Three: Double primary key (combined primary key), you must use this format to add
CREATE TABLE TB1 (
Name ...
Ip...
...
Primary KEY (NAME,IP)
);

B. Adding when modifying a table:
ALTER TABLE TB1 add primary key (ID);

(2) View primary key
A. Desc tb1;
B. show create TABLE tb1\g;

(3) Delete primary key
A. ALTER TABLE TB1 drop PRIMARY key;
View after deletion: desc tb1;
Show CREATE TABLE tb1;

(4) Self-increment primary key: Auto_increment

A. Add a self-increment primary key
ID int primary KEY auto_increment when building a table;

B. When modified, set the primary key to self-increment (ID is already primary key)
ALTER TABLE TB1
Modify ID int auto_increment;

C. Cancel Auto-increment (does not delete primary KEY constraint)
ALTER TABLE TB1
Modify ID int;

D. Self-increment primary key fills in null value, automatically fills in self-increment

F. View the next value from Value added:
Show create table tb2\g;
....
Auto_increment:xx

G. You can manually insert a specified value, and if the value is the maximum value in the table, the next self-increment will automatically grow from this maximum value. Use the self-increment primary key, regardless of whether the value is continuous. The self-increment primary key cannot be returned.

2, FOREIGN KEY constraint: restricts the value of one field, only the value of the other primary key
(1) Creates a foreign key
a. Creating a foreign key
CREATE table TB2 (
...
x_id ...
...
Foreign KEY (x_id) references tb1 (ID)
);
B. Creating a foreign key when modifying a table
ALTER TABLE TB3
Add foreign key (x_id) references tb1 (ID);

(2) View foreign keys
Show CREATE TABLE tb3;

(3) Delete foreign key:
First you need to see the name of the FOREIGN KEY constraint
ALTER TABLE TB2
Drop FOREIGN key constraint name;

(4) The foreign key will also automatically create the index, delete the foreign key, not automatically delete the foreign key index, if you want to delete the foreign key index:
ALTER TABLE TB2
Drop index name;



3, non-null constraints
(1) Add non-null constraint
A. Adding when creating a table
creat table TB2 (
...
Name varchar () is not NULL,
);
B. Adding when modifying a table:
ALTER TABLE TB2
Modify name varchar (a) not null;

(2) Cancel non-null constraint
ALTER TABLE TB2
Modify name varchar (20);

ALTER TABLE TB2
Modify name varchar (+) null;

4. Unique constraint (unique): Field values cannot be duplicated, allowing duplicate null values
(1) Adding a unique constraint
A. Adding when building a table
CREATE TABLE TB2 (
Username varchar (+) unique NOT NULL,
Email varchar (+) Unique
Addr varchar (255),
Tel varchar (20),
Unique (Addr,tel)
);
B. Adding when modifying a table
ALTER TABLE TB2
Modify Email varchar (+) unique;

ALTER TABLE TB2
Add unique (Addr,tel);

(2) View UNIQUE constraints
A. Method One:
Desc TB2
Show CREATE TABLE TB2

B. Viewing constraints in the system library
Use INFORMATION_SCHEMA--system library, library information
SELECT * FROM Table_constraints
WHERE table_name = ' xxx ';

(3) Remove the unique constraint
ALTER TABLE TB2
Drop index name;

(4) CHECK constraints
You can limit the value of a field by setting its value, such as gender, age range, etc.
MySQL supports checking the syntax of constraints, but without implementing constraints, MySQL does not check constraints

CREATE TABLE Xuesheng (
Gender ...
Age ...
Check (gender= ' male ' or gender= ' female '),
Check (age>8 and age<60)
);
5. Default value
Age int Default 0
To save comment information for a field in a table structure

Third, save the comment information of the field in the table structure (comment)
CREATE TABLE TB2 (
ID int comment ' primary key ID ',
Name varchar (comment ' name ')
)；
Show CREATE table tb2\g can see comments

Iv. relationships between tables and tables
1. One-to-one relationship
Foreign keys with unique constraints
A field that is both a primary key and a foreign key

2, one-to-many relationship
To add a foreign key in a multiparty table

3. Many-to-many relationships
Need an intermediate table, add two foreign key fields, refer to the primary key of the two table, respectively

"Java" Java Learning path -03-mysql (ii)