One . Storage engine
Innodb
at present 5.1 after MySQL Version default storage engine
Support transaction (support transactions), Row lock (row-level locking), Foreign key (foreign key)
Because of the support above More secure data

When building the watch innodb Two files will be generated
One is a table structure file
One is to store data files
MyIsam
5.1 Before version MySQL Default storage engine for
Query speed is faster than Innodb Be quick
Three files will be generated during table creation
One is a table structure file
One is the index file
Index. First, you understand it as a catalog of books , Can help you query data faster
One is to store data files
memory
Store data in memory
When creating a table, there is only one table structure file
blackhole
Any written data will disappear
When creating a table, there is only one table structure file

mysql> show engines;

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it
disappears) | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables |
NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign
keys | YES | YES | YES |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

Two , Complete syntax for creating tables
create table Table name (
Field name 1 type [( width ) constraint condition ],
Field name 2 type [( width ) constraint condition ],
Field name 3 type [( width ) constraint condition ]
);
Be careful :
1. Field name and field type are required Parameters in brackets are optional
2. Field names cannot be duplicate in the same table
3. Comma is not allowed after the last field
create table t6(
id int,
name char,
);

width :
Guidelines for using databases : Try to make it work as little as possible

Restrictions on data storage
char(1) Only one character can be saved
If it is over mysql Will automatically intercept it for you
1. When inserting mysql Automatic interception
2. Direct error reporting (mysql Strict mode )

alter table t5 modify name char not null;
not null This field cannot be null

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

insert into t1 values(1);
insert into t2 values(2);
insert into t3 values(3);
insert into t4 values(4);

select * from t1; #
select * from t2;
select * from t3; # Turn off the server data and it will disappear
select * from t4; # What to write , What will disappear

## Building tables
mysql> create table t5(id int,name char(2),password int,play char(6));
Query OK, 0 rows affected (0.31 sec)

mysql> desc t5;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(2) | YES | | NULL | |
| password | int(11) | YES | | NULL | |
| play | char(6) | YES | | NULL | |
+----------+---------+------+-----+---------+-------+

## stay t5 Insert data in table
mysql> insert into t5 values(4,'hang',12344213789456,' Pink is healthy and comfortable ');
Query OK, 1 row affected, 3 warnings (0.07 sec)

mysql> select * from t5;
+------+------+------------+--------------------+
| id | name | password | play |
+------+------+------------+--------------------+
| 1 | zg | 2147483647 | ballll |
| 1 | ha | 2147483647 | balsdf |
| 3 | zg | 2147483647 | ballll |
| 4 | ha | 2147483647 | Pink health |
+------+------+------------+--------------------+
stay id=4 Time , We can see char The number in brackets limits the number of characters , No matter the letters or Chinese characters or other characters, one character is counted as one character , Set when I customize table fields name
char(2), Actual name ‘hang’ yes 4 Character , The number of characters we set is intercepted ;password

# Field can be null
mysql> desc t5;
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | char(2) | YES | | NULL | |
| password | int(11) | YES | | NULL | |
| play | char(6) | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
mysql> insert into t5 value(5,'an',2344789456,'NULL');
Query OK, 1 row affected, 1 warning (0.05 sec)

mysql> select * from t5;
+------+------+------------+--------------------+
| id | name | password | play |
+------+------+------------+--------------------+
| 1 | zg | 2147483647 | ballll |
| 1 | ha | 2147483647 | balsdf |
| 3 | zg | 2147483647 | ballll |
| 4 | ha | 2147483647 | Pink health |
| 5 | an | 2147483647 | NULL |
+------+------+------------+--------------------+

# A field in the change table cannot be empty , After setting ,
alter table t5 modify name char not null;
not null This field cannot be null if you insert null again , The space where this field will be inserted will be cleared .

mysql> alter table t5 modify play char not null;
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> update t5 set play=null where id=1;
Query OK, 2 rows affected, 2 warnings (0.06 sec)
Rows matched: 2 Changed: 2 Warnings: 2

mysql> select * from t5;
+------+------+------------+------+
| id | name | password | play |
+------+------+------------+------+
| 1 | z | 2147483647 | |
| 1 | h | 2147483647 | |
| 3 | z | 2147483647 | b |
| 4 | h | 2147483647 | powder |
| 5 | a | 2147483647 | N |
| 6 | z | 12345 | |
+------+------+------------+------+
6 rows in set (0.00 sec)

Type and constraints in brackets
Type constrains the storage type of data
Constraints are additional constraints based on types

# Be careful :
1. In the same table , Field name cannot be the same
2. Width and constraints optional , Field name and type are required
3. Comma cannot be added after the last field !

# supplement :
# 1. Width refers to the limitation of stored data
create table userinfo(name char);
insert into userinfo values('jason');
"""
1. Database version without security mode , Can store data but only one j
2. The latest database version can't be stored with direct error prompt :Data too long for column 'name' at row 1
"""

# 2. Preliminary understanding of constraints >>> null And not null
# Set up not null after ,char Only accepted by default 1 Character , Namely ,char(1), Extra characters will be cleared out .
create table t1(id int,name char not null);
insert into t1 values(1,'j'); # Normal storage
insert into t1 values(2,null); # Report errors

# summary Difference between types and constraints
# type : What kind of data type must a restricted field be stored in
# constraint condition : The constraint is to add an additional constraint to the type

 

Technology
©2019-2020 Toolsou All rights reserved,
Huawei 2021 session Hardware Engineer Logical post (FPGA) Super detailed surface !!!Vue-element-admin upgrade ui edition virtual machine VMware Download and install the most detailed tutorial !C++ Move constructor and copy constructor sound of dripping water java Backstage interview pygame Realize full screen mode and adjustable window size mysql Database setting character set configuration modification my.ini file (windows)30 What's the experience of being a junior programmer at the age of 20 C++ Multithreading programming ( Summary of common functions and parameters )python_ cherry tree