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,
---------------------------------------------------------------------------
Single Table query:
View the engine used by the current table:
SHOW ENGINES;
AUTO_INCREMENT = value specifies the Growth value
Create a table and specify the index type
USING {BTREE | HASH}
Directly use CREATE to CREATE an index
Create index index_name ON tbl_name (col ..)
Create a table and specify a storage engine
Create table user (id INT) ENGINE = InnoDB;
Alter table user ENGINE = MyISAM;
Set Default Engine
Set global storage_engine = engine_name;
Set session storage_engine = engine_name;
Query selected data:
Field names are case insensitive
SELECT column1, col2 FROM table_name// Select a specified field and display project Projection
The order given by the field is the display order.
SELECT * FROM table_name // Query all fields in the table
Select filter conditions:
WHERE // Specify the row to be selected
The query conditions are as follows:
>,<, = ,! =, LIKE, BETWEEN... AND..., IN, is null (NULL), IS NOT NULL
LIKE // Fuzzy match
% Match any character in length
_ Match any length of any single character
BETWEEN... AND ..// Match specific conditions
IN // Display the rows that match the condition in a specific set
REGEXP (RLIKE )// Supports Regular Expression Pattern Matching
AND (&), OR (|), NOT (!) XOR exclusive or
Sort the data after filtering:
ORDERCol_name {ASC | AESC}
ASC // Sort in ascending order {default}
DESC // Sort in descending order
Which Rows can be queried only?
LIMITN // Show the first N rows
Limit n, M // Displays a total of M rows starting from row N
DISTINCT// Merge the same result options
E.g. select distingct field_name FROM tbl_name
Grouping results: aggregate statistics on the number of rows After grouping
GROUP// The selected row is grouped with the same field value.
COUNT (*)// Statistical data
Filter group by results // Filter data After grouping
HAVING
The field names in the table can be displayed and selected differently. AS// Change display name
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 ;// Delete Index
Delete data:
Delete form table [WHERE cloue];
Rename a table:
Rename is to create a new table, copy the old table data, and then delete the old table.
1. alter table old_name rename to new_name;
2. RENAME TABLEOld_name TONew_name;
Insert data:
INSERT Table_name (col1, col2) VALUES (Val1, val2)// The inserted value can also be a field.
REPLACE // Duplicate value replacement to avoid errors
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. Inner 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
