PYTHON--11, database, and SQL basics

Common command log engine to view all tables in the library

SHOW TABLE STATUS FROM `center_main_db`;

There is also a more concise, query cmol_system_db library for all tables of the storage engine \

SELECT table_name, table_type, engine FROM information_schema.tables WHERE table_schema = ‘cmol_system_db‘ ORDER BY table_name DESC;

Querying the size of a library

The unit is a byte,%1024 the result in%1024 to M. To query the capacity of the table is to add the data and index together.

select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema=‘数据库名‘;#查询表把最后的table_schema=‘‘替换为table_name=‘表名‘即可。

Querying for all data sizes

select concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘) from tables;

Querying data for a table

select concat(round(sum(DATA_LENGTH/1024/1024),2),‘M‘) from tables where table_schema=’数据库名’ AND table_name=’表名’;

View Index

mysql> show index from tblname;mysql> show keys from tblname;· Table表的名称。· Non_unique如果索引不能包括重复词，则为0。如果可以，则为1。· Key_name索引的名称。· Seq_in_index索引中的列序列号，从1开始。· Column_name列名称。· Collation列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。· Cardinality索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机 会就越大。· Sub_part如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。· Packed指示关键字如何被压缩。如果没有被压缩，则为NULL。· Null如果列含有NULL，则含有YES。如果没有，则该列含有NO。· Index_type用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。· Comment

Create an index

You can create an index when you execute the CREATE TABLE statement, or you can add indexes to the table by using the CREATE INDEX or ALTER TABLE alone.

1.AlterTABLEAlterTable is used to create normal indexes,Unique index or primaryKey index.AlterTABLE table_nameADDINDEX index_name (column_list)AlterTABLE table_nameADDUNIQUE (column_list)AlterTABLE table_nameADD PRIMARYKEY (column_list) where table_name is the name of the table to increase the index, column_list indicates which columns to index, and columns are separated by commas. Index name index_name optional, by default, MySQL assigns a name based on the first indexed column. Other than thatalter table allows you to change multiple tables in a single statement, so you can create multiple indexes at the same time. 2. CREATE index CREATE INDEX to add a normal or unique index to a table . Create Index index_name on table_name (column_list)create UNIQUE INDEX index_name on TABLE_NAME (COLUMN_LIST) table_name, index_name, and column_list have the same meaning as in the ALTER table statement, and the index name is not selectable. In addition, the primary key index cannot be created with the Create index statement . 

Index type

When you create an index, you can specify whether the index can contain duplicate values. If not included, the index should be created as a primary key or a unique index. For single-column uniqueness indexes, this guarantees that a single column does not contain duplicate values. For multi-column uniqueness indexes, the combination of multiple values is guaranteed to be distinct.
The PRIMARY key index is very similar to a unique index. In fact, the PRIMARY key index is only a unique index with the name PRIMARY. This means that a table can contain only one primary KEY because it is not possible to have two indexes with the same name in a table.
The following SQL statement adds a primary key index to the students table on the SID.
ALTER TABLE Students ADD PRIMARY KEY (SID)

PYTHON--11, database, and SQL basics