What is a table? Table: logically, each table in the database contains rows and a list that can be 0 rows, but at least one table must also support index query.
What is a table? Table: logically, each table in the database contains rows and a list that can be 0 rows, but at least one table must have a column that also supports index query.
What is a table??
Table: table
Logically, each table contains rows and columns in the database.
The table can be 0 rows, but at least one table must support index query.
Physically, each table is saved on a disk by one or more files.
Each table is stored in the database directory with the corresponding file system format.
Table space: physical unit larger than the table but smaller than the database
Create a table:
Help CREATE TABLE
CREATE [TEMPORARY] TABLE [if not exists] tbl_name (// Create a table
(Create_definition ,...)
[Table_option]...
);
CREATE [TEMPORARY] TABLE [if not exists] tbl_name (// Select the fields of the known table and create
[(Create_definition,...)]
[Table_option]...
SELECT [select_statement]
CREATE [TEMPORARY] TABLE [if not exists] tbl_name (// Create with reference to known tables
{LIKE old_tbl_name | (LIKE old_tbl_name )} Fields are separated by commas (,).
View how to create:
Show create table tbl_name;
---------------------------------------------------------------------------
E.g.
Create table employee (
Id int not null,
Last_name CHAR (30) not null,
First_name CHAR (30) not null,
UNIQUE (id ),
INDEX (last_name, first_name)
);
Create table... SELECT can create a table that is empty or non-empty, depending on what is returned by the SELECT part
Create table... LIKE creates an empty table using the definition of another existing table
Create table tbl_name (
Id int unsigned AUTO_INCREMENT not null,
---------------------------------------------------------------------------
Added the use of N-plus built-in functions
Refer to the MYSQL official manual
Aggregate functions
COUNT (*), MAX (), MIN (), AVG (), SUM ()
Besides functions, you can also use variables.
SET @ num = 10 // SET the variable to 10
Application declaration to use @
========================================================== ==========================================
Modify Table Structure:
Alter table t1 CHANGE a B INTERGER ;//Change the name of an INTERGER column from a to B.
Alter table t1 MODIFY B BIGINIT NOU NULL //Modify the definition and position of a field without changing the field name.
You can use FIRST or AFTER col_name to add columns at a specific position in a table row. By default, columns are added to the end. You can also use FIRST and AFTER in the CHANGE or MODIFY statement.
---------------------------------------------------------------------------
Alter table tbl_name MODIFY col_name col_definition change location
Alter table tbl_name CHANGE col_name col_definition
---------------------------------------------------------------------------
Add a new field
ADD {FIRST | AFTER} col_name col_definition
Add an index:
ADD {INDEX | KEY} [index_name] [index_type] (col ..)
Index_type: BTREE, HASH, RTREE, FULLTEXT
Deletion format:
DROPCol_name ;//Delete Field
Drop primary key ;//Delete primary key
DROP {INDEX | KEY} index_name ;//
INSERT supports inserting multiple rows at a time.
Modify data: DML
UPDATE table_name SET col1 = val1 [,...] [WHERE clause] //You must specify a condition. Otherwise, all columns are updated.
E.g. UPDATE knight set age = 18 where uid = 1
Help TRUNCATE;
SELECT LAST_INSERT_ID ();//Record the last ID row
Truncate table table_name ;//Clear all data in the table and start counting with equal 1
---------------------------------------------------------------------------
Multi-Table query:
How to organize the two tables:
JOIN: joins tables in some way.
Connection Type:
1.Cross join, Cross join Cartesian Product //It is rarely used, but it is applicable to all situations where the results are temporarily stored on the disk in the memory, so it is very slow.
2.Internal Connection, Inner join symmetric JOIN
Both the left and right tables appear and the values are equal based on the equivalence conditions.
If the field names are the same, you must reference the table prefix table_name.col_name.
3.External ConnectionOUTER JOIN
LEFT outer JOIN ON
NULL is displayed on the right on the left //All displayed in the left table, all displayed on the right, and none displayed on the right, NULL
RIGHT outer JOIN [ON] on refers to the condition
Show on the Right of NULL on the left
FULL JOIN
Both show the left table, some right tables, and some right tables.
4.Self-connectionSelf join joins itself
5.UNIONResult combinations
Combine the query results of two tables into one