1. Folders (libraries)

Add:

create database database name charset utf8 (specify the encoding method of the library);

Change:

alter database database name charset gbk; (change encoding)

Check:

show databases; # Look up the library names of all libraries
show create database Database name; # View the information of a library separately

Delete:

drop database database name

2. Documents (tables)

use Database name; # First switch folders
select database(); # View the current folder

Add:

create table Table name(id int,name char);

Change:

alter table table name modify name char(16);

alter table old_name rename new_name; modify table name
 Rename to rename, or rename to 

alter table person modify name varchar(20);# modify field data type
 Here, the name after the modification is the field name, and we change the original varchar(25) to varchar(20) 

alter table person change old_name new_name varchar(25);# modify field name
 It should be noted that regardless of the change of data type, the following data types should be written. 
If you don't modify the data type, you just need to write the original data type. 
tips: We can also use change to modify, just write the same field name after that. 

Alter table person adds sex boolean; # adds fields without integrity constraints
 The sex here follows only the data type without integrity constraints 

alter table person add age int not null; # add fields with integrity constraints
 An age field is added to the location, followed by not null integrity constraints. 

alter table person add num int primary key first;  add fields to the header
 By default, adding fields is added to the end of the table, and adding first after adding statements can be added to the header of the table. 

alter table person add birth date after name;  add fields at specified locations
 A new field is added here after the name field. 
tps: The sorting of fields in a table does not affect the table, but a more reasonable sorting makes it easier to understand the table 

alter table person drop sex; # delete field
 Like deleting tables or databases earlier, drop is also required here. 
The difference is that to delete a field, alter table is used to follow the table name. 

alter table person modify id int first; # modify field to first position
 first has been mentioned earlier, but here's the point to note that data types are written after fields. 

alter table person modify name varchar(25) after id; # modify field to specified location
 We put the name field after the id, where varchar(25) is written in full, but varchar is not. 
It is recommended that desc table be used before the above steps. 

alter table user rename person; # storage engine for modifying tables
 There are more contents than the characteristics of each storage engine. 
Don't forget to use the show create table statement to view the changes. Section 3 has written usage. 
tips: Do not modify the storage engine easily if there is a lot of data in the table 

alter table score add constraint fk foreign key(stu_id) references student(id); # add foreign key of table
 You can just use add here. For the following syntax, see the foreign key settings in Section 4. 

alter table student3 drop foreign key fk; # delete table foreign key constraints
 Because the basic table structure description cannot display foreign keys, it is best to use show create table to view tables before doing this. 
The fk here is the foreign key just set up.
Note that if you want to delete associated tables, you must first delete foreign keys. 
After deleting the foreign key, the original key becomes a normal key.
As for the deletion of tables, there is writing in Section 3 and setting foreign keys in Section 4. If the foreign keys are not set when creating tables, you can use the above method.

Check:

show tables; # View all table names under the current library
show create table Table name; # View Table Details
desc Table name; # View table structure

Delete:

drop table table table name;

3. One line of the document

Add:

insert into database name. table name values(1,'la'),(2,'lala'),(3,'lalala');

Change:

update library name. Table name set name ='sb'where id > 1;

Check:

select id,name from library name. Table name;  Check ID and name
 select * from library name. Table name;  Check all

Delete:

delete from library name. table name where name='qwe';
truncate library name, table name,  When the amount of data is large, using this method, the problem of self-increasing can also be deleted quickly.

create table t1(id int)engine=innodb;
create table t2(id int)engine=myisam;
create table t3(id int)engine=blackhole;
create table t4(id int)engine=memory;

4. Complete grammar for creating tables

A complete syntax for creating tables
 create table table table name(
Field name 1 type [(width) constraint],
Field name 2 type [(width) constraint],
Field Name Type 3 [(Width) Constraints]
);

# Explanation:
Type: What type of data must be passed in order to use a restricted field
 Constraints: Constraints are additional constraints that are added to the type

# Note:
1. In the same table, field names can't be the same
 2. Width and constraints are optional, field names and types are required
 3. No commas after the last field

create database db37;

5. Integer types

1,Effect: id Number, Number, Age, Grade
2,Classification:
tinyint(**)
int (*****)
bigint(***)

3,Test: Default integers are signed
create table t1(x tinyint);
insert into t1 values(128),(-129);

create table t2(x tinyint unsigned);
insert into t2 values(-1),(256);

create table t3(x int unsigned);
#4294967295
insert into t3 values(4294967296);

create table t4(x int(12) unsigned);
insert into t4 values(4294967296123);


4,Emphasis: For integers, the width after data type is not storage restriction, but display restriction
    //So in creating representations, if fields are of integer type, there is no need to specify the display width at all.
    //The default display width is wide enough to display the full original stored data.

# When displaying, not enough 8 bits are filled with 0, and if more than 8 bits are displayed normally.
create table t5(x int(8) unsigned zerofill);
insert into t5 values(4294967296123);
insert into t5 values(1);

6. Supplementary sql_mode

# View sql_mode
mysql> show variables like "%sql_mode%";
+----------------------------+---------------------+
| Variable_name              | Value               |
+----------------------------+---------------------+
| binlogging_impossible_mode | IGNORE_ERROR        |
| block_encryption_mode      | aes-128-ecb         |
| gtid_mode                  | OFF                 |
| innodb_autoinc_lock_mode   | 1                   |
| innodb_strict_mode         | OFF                 |
| pseudo_slave_mode          | OFF                 |
| slave_exec_mode            | STRICT              |
| sql_mode                   | STRICT_TRANS_TABLES |
+----------------------------+---------------------+
8 rows in set (0.00 sec)

#Modify sql_mode to strict mode: In this mode, if the data inserted exceeds the limit, an immediate error will be reported.
mysql> set global sql_mode="strict_trans_tables";

7. Floating-point type

Function: Store height, weight, salary
//Classification:
float (*****)
double (**)
decimal (**)

//Test:
#Similarities
#1. For all three, they can store 30 decimal places.
#Difference:
1,Precision ranking ranges from low to high: float,double,decimal
2,float and double Integer-to-digit ratio for type storage decimal More


create table t9(x float(255,30));
create table t10(x double(255,30));
create table t11(x decimal(65,30));

insert into t9 values(1.111111111111111111111111111111);
insert into t10 values(1.111111111111111111111111111111);
insert into t11 values(1.111111111111111111111111111111);


mysql> select * from t9;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111164093017600000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t10;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111200000000000000 |
+----------------------------------+
1 row in set (0.00 sec)

mysql> select * from t11;
+----------------------------------+
| x                                |
+----------------------------------+
| 1.111111111111111111111111111111 |
+----------------------------------+
1 row in set (0.00 sec)

8. Character type

1,Function: Name, Address, Information Describing Classes

2,Classification:
char Fixed length
varchar Variable length

3,test:The width limit of a character is the number of characters.
create table t12(x char(4)); # Errors are reported when more than four characters are exceeded, and when less than four characters are filled in with spaces to form four characters.
create table t13(y varchar(4));# If more than 4 characters, the error will be reported. If less than 4 characters, several characters will be saved.

insert into t12 values('hello');
insert into t13 values('hello');

insert into t12 values('a'); #'a    '
insert into t13 values('a'); #'a'

set global sql_mode="strict_trans_tables,PAD_CHAR_TO_FULL_LENGTH";
select char_length(x) from t12; #4
select char_length(y) from t13; #1

# Be careful:
//For char type, mysql stores data in hard disk when it stores it with blank space.
//But the space at the end is automatically removed when the result is read out, because the space at the end is useless in the following scenarios
mysql> select * from t14 where name="lxx"; # The name field is explicitly equal to a value, and filling in spaces after that value is useless.


mysql> select * from t14 where name like "lxx"; # The name field fuzzily matches a value that is useful to fill in spaces

# Contrast char with varchar

name char(5)
# Disadvantage: Wasting space
# Advantages: Fast access
egon alex lxx  wxx  yx

name varchar(5)
# Disadvantage: Slow access
# Advantages: Save space
(1bytes+egon)(1bytes+alex)(1bytes+lxx)

9. Date type

1,Role: Time-related
2,Classification:
date: 1999-01-27
time: 11:11:11
datetime:1999-01-27 11:11:11
year:1999
3,test
create table student(
    id int,
    name char(16),
    born_year year,
    birth date,
    class_time time,
    reg_time datetime
);
insert into student values(1,'egon','2000','2000-01-27','08:30:00','2013-11-11 11:11:11');

10. Enumeration and Collection Types

Role and classification:
Enumerate enum, choose one more
 set, multi-choice and multi-choice

test

create table teacher(
    id int,
    name char(16),
    sex enum('male','female','others'),
    hobbies set('play','read','music','piao')
);

11.not null and default

#not null + default

create table t15(
    id int,
    name char(16) not null,
    sex enum('male','female','other') not null default "male"
);

alter table t15 modify name char(16) not null;


insert into t15(id,name) values
(1,'egon1'),
(2,'egon2'),
(3,'egon3');

12.unique

#Unique: Restrict the value of a field to be unique

#Uniqueness
create table t16(
    id int unique,
    name char(16)
);


# Joint Union
create table server(
    id int unique,
    ip char(15),
    port int,
    unique(ip,port)
);

13.primary

#Primary key: From a constraint perspective alone, primary key is equivalent to not null unique
#Emphasis (******)
1,There must be, and only one primary key in a table
2,There should be one in every table. id Fields, and should id Fields make primary keys


create table t17(
    id int primary key,
    name char(16),
    age int,
    sex char(6)
)engine=innodb;


#composite keys
create table t19(
    ip char(15),
    port int,
    primary key(ip,port)
);


# primary key auto_increment
create table t20(
    id int primary key auto_increment,
    name char(16)
)engine=innodb;

# Notes on auto_increment:
1,Usually with primary key It's used together, and it's usually for id Field addition
2,auto_incremnt It can only be defined as key(unique key，primary key)The field addition of ____________