30th Day of Class notes DDL, DML, subquery

1. mysql DDL statements
               Case of characters in MySQL
SQL keywords and function names are case-insensitive;
Database, table, index, and view names are case-sensitive depending on the low-level OS and FS;
stored procedures, stored functions, and event schedulers do not differentiate between character case, but triggers are differentiated;
Table aliases are case insensitive;
field character data, type binary, blog, varbinary case-sensitive;

Ddl:create/alter/drop
CREATE {DATABASE | SCHEMA} [IF not EXISTS] Db_name
MariaDB [(None)]> SHOW GLOBAL VARIABLES like '%char% ';
DROP {DATABASE | SCHEMA} [IF EXISTS] Db_name
Table:
Constraints: PRIMARY key, UNIQUE key, FOREIGN key
Index: Special data structure for quick data records (rows)
Keys can be used as indexes; not all indexes are keys;
Index type: b-tree (b + Tree) (left prefix), hash (key-value), R-tree, fulltext
Index Benefits:
Reduce server scan Data volume
After the index is created, the field itself is a sorted result, and you can try to avoid creating temporary tables when sorting
Indexes can convert random I/O to sequential I/O

B-tree (b + Tree) (left prefix) index:
Full value matching: comparison operator =, <=>
Left prefix match: like ' string% '
Column prefix matching:
Match Range Value:
Composite index: The first column must be an exact match, and then the range can be matched.

Clustered index, nonclustered index
Clustered index: Index and data are stored together;
Nonclustered indexes: Indexes and data are stored separately, while data records may not be stored sequentially, but index data are stored in a general order;

MyISAM is a nonclustered index and InnoDB is a clustered index. A table can have only one clustered index, which is generally the primary key index for INNODB.

Precautions :
MyISAM is supported for full-text indexing, but foreign keys are not supported. InnoDB does not support full-text indexing. Now most engines are InnoDB, only InnoDB can set tablespace tablespace tablespace_name [STORAGE {disk| memory| DEFAULT}]

Common commands:
Help Create | Alter | Drop; View related create statement help.
Show global variables like '%char% ';: Display character Set
Help CREATE TABLE: View information about creating a table
Create temporary table test (name char (20));: Creates a temporary table. Temporary table memory cannot exceed 16M, such as hyper-to-disk temporary table, performance is poor.
CREATE TABLE TB1 (name char), Gender enum (' m ', ' f '), primary key (name)); Create a table with a primary key
CREATE TABLE TB2 (name char), Gender enum (' m ', ' f '), primary key (name)) engine ' InnoDB ';: Modifying the storage engine
CREATE TABLE TB5 select * from tb1;: Copy the table structure and data in the TB1 to Tb5, and the properties in the table are not copied.
CREATE table tb3 like TB2;: Copy TB2 table structure creates a new table Tb3 duplicates only the table structure and copies all the properties in the table;
INSERT into TB1 values (' XJ ', ' F '), (' Tan ', ' m '), (' Zhang ', ' m ');: Insert Data
Show index form tables;: Display indexes
Show global variables like '%storage% ';: Displays the storage engine.
Show engines;: View the currently supported storage engines.
Show table status like ' Tb1 ' \g: View the related properties of the table.

2, MySQL language and the use of indexing strategy
Help drop table tbname;: View the command to delete a table.

Common statements:
ALTER TABLE TB3 drop PRIMARY key;: Delete primary key.
ALTER TABLE TB3 Add index (gender);: Add indexes on the gender field. The default is B-tree index.
ALTER TABLE TB3 DROP INDEX name;: Removes the TB3 name in the table.
ALTER TABLE TB3 Rename to TB4; modify TB3 table named TB4.
Drop index index_name on tb_name;

3. Select query Statement DML
Insert into tb_name values, Updata, delete
Is null: field is empty
is not null: Field not empty
GROUP BY: Grouping
Having: Specify post-grouping conditions
Distinct: Removes duplicate values. Select distinct Name Form tb5;

Attention:
Almost all caches, cached content are in Key-value format:
Key: The hash code of the query statement;
Value: The execution result of the query's statement;

Aggregation functions:
AVG (), SUM (), MAX (), MIN (), COUNT (), having a filter on the aggregation result.

Connection query:
Cross join: Cartesian product,
INNER JOIN: Inner connection
Outer join: Outer joins, divided into lift outer join left outer joins and right outside join
SELECT us.user_name,x.fk_role_id from user US LIFT joins User_role x on US. pk_id = x.fk_user_id
Natural join: Equivalent connection

Common statements:
Update tb5 set name= ' xxxx ' where name= ' XJ ';
Update tb5 set gender= ' F ' ORDER by name limit 1;: Only modify the first post sorted with limit 1.
Show global variables like ' query_cache% ';: Query cache size, as long as the value greater than 0 is open cache, 0 means cache is off
SELECT @ @GLOBAL. query_cache_size;: You can also see if the cache is enabled.
Select Current_time ();: Displays the time.
SELECT @ @GLOBAL. Query_cache_type: Three values after query (demand, on, off)
Demand: Cache On demand, user-determined which statements need to be cached,
On: and MySQL decides which query statement results need to be cached.

4, sub-query, view and explain
Federated query:
SELECT statement Union SELECT statement: Combine multiple query results together as a result, the number of fields must be the same.

Views: View, stored SELECT statements
CREATE VIEW view_name as SELECT * from Tabel_name;

Explain: Parses the executed SQL statement. Ken optimizes the database with more analytical results.
Explain select * FROM Tb5\g

30th Day of Class notes DDL, DML, subquery