Mysql比知必会系列教程(五) --------mysql数据表操作

Mysql数据表操作

创建一个原本没有的数据表：

create table if not exits语句，无需假设它需要用到的数据表是否存在。当存在一个相同的表名时用create table语句创建时就会出错。所以使用 If not exits语句就不会有这些问题了。

临时数据表

如果在数据表创建语句加入temporary关键字，服务器将创建一个临时的数据表，当与服务器连接断开时会自动消失。

create temporary table tab1_name…;

这样做服务器会在客户回话结束时自动删除一个Temporary数据表，但也可以用完后显式的进行删除、一个temporary的数据表允许与一个永久性表名相同，只是在临时表期间不能够访问原来的永久表数据（原来的表被隐藏了，只能访问临时表）。无法创建两个同名的临时表。如果使用连接池则与mysql的连接状态会保持连接，这意味着创建的temporary数据表不一定会在应用程序结束时自动消失、

从其他数据表或查询结果创建数据表

Create table … like 语句

创建一个新数据表作为原始数据表的一份空白副本。将原来的数据表结构复制过来。包括数据列的所有属性，索引内容。不能将原始数据表以外的数据列复制。

这时新的数据表是个空白的数据表需要用insert into selecte 语句进行填充数据，

create table new_table_name like tab1_name;

insert into new_table_name select * from tab1_name;

过滤一部分数据到数据表中

create table new_table_name like tab1_name;

insert into new_table_name select * from tab1_name where xxxx0 ;

create table … select语句

可以从任意一条select语句的查询结果创建新的数据表。可以同时完成创建和填充新的数据，。

复制表数据

复制表数据时创建表

SELECT vale1, value2 into Table2 from Table1

SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中。

要求目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中。示例如下：

select * into notes_note_migrate1 from notes_note where createatutc>=‘2019-7-1’;

复制表数据不创建表(新的表结构必须存在)

Insert into Table2(field1,field2,…) select value1,value2,… from Table1

要求目标表Table2必须存在，由于目标表Table2已经存在，所以我们除了插入源表Table1的字段外，还可以插入常量

提示

MySQL 数据库不支持 SELECT … INTO 语句，但支持 INSERT INTO … SELECT 。

当然你可以使用以下语句来拷贝表结构及数据：

CREATE TABLE 新表
AS
SELECT * FROM 旧表 

删除数据表

Drop table tabe1_name;// 直接删除

数据表创建索引

索引，加快对数据表内容的访问速度的基本手段，尤其是涉及数据表的关联查询。

索引主要有以下几个作用

1. 即上述所说，索引能极大地减少扫描行数
2. 索引可以帮助服务器避免排序和临时表
3. 索引可以将随机 IO 变成顺序 IO

索引分类

唯一索引：不允许索引项出现重复的值，对涉及一个数据列的索引不会包涵重复的值，对于多个数据列的索引（复合索引）就是这几个数据列的值的组合在数据表的范围内不能重复出现重复。
普通（非唯一）索引：允许索引值重复。

FullText索引：用来进行全文检索，只适用于MyISAM数据表

SPATIAL索引：只适用于MyISAM数据表

hash索引：memory数据表的默认索引类型。也可以改用为Btree索引代替这个默认索引。

索引类型：

普通索引：仅加速查询

唯一索引：加速查询 + 列值唯一（可以有null）

主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个 每个数据表中只能有一个 PRIMARY key, 并且不可以为NULL

组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并

全文索引：对文本的内容进行分词，进行搜索

ps.索引合并，使用多个单列索引组合搜索

覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖

可以使用alert table或create index语给数据表加索引。mysql内部会把create index语句映射为alter table 操作。

alert table tab1_name add INDEX index_name

alert table tab1_name add UNIQUE index_name

alert table tab1_name add PRIMARY KEY (index_name)

alert table tab1_name add FullTEXT index_name

对于某个字符数据列的一个前缀编索引是col_name(n),n是代表的是索引包含列值的前n个字节（二进制字符串类型）或前n个字符（非二进制字符串类型）比如：alert table tab1_name add index_name(10),blob类型或text数据类型只能创建前缀索引。索引本身的长度等于构成索引的各个数据列的索引部分的长度总和。如果长度超出则可以使用缩短编排的前缀索引。

删除索引：

删除索引可以用Drop index或alert table语句来完成。

Drop index index_name on tb1_name;

alert table tab1_name drop index index_name;

同样可以使用，show create table或者show index 查询出索引。

索引的优点

索引大大减少了服务器需要扫描的数据量

索引可以帮助服务器避免排序和临时表

索引将随机I/O变为顺序I/O

索引并不是最好的工具，总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效。对于中大行的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价随之增长。

改变数据表的结构

alter table语句

alter table tab1_name action [,action] … ;

每一个action代表一个要修改的数据表字段

alter table mytab1 Modify i mediumint unsigned;

alter table mytab1 change i i mediumint unsigned;

change子句在改变时还可以重新命名。

alter table mytab1 change i k mediumint unsigned;

改变数据列时，同时指定字符集: alter table t modify c char(20) character set

使用alter table重新命名一个数据表。alter table tab_name rename new_tab_name;

alter table语句每次只能重新命名一个数据表，而rename语句可以一次重新命名多个数据表 rename table t1 to tmp,t2 to t1, tmp to t2

获取数据库元数据

各种的show语句

show databases;列出所有的数据库。

show create database db_name;查看给定数据库的 create databases 语句

show tables;show tables from db_name 列出数据表

show create table tb1_name; 查看数据表的 create table 语句

show columns from tab1_name;查看数据表了里面的列

show index from tb1_name; 查看数据列或索引信息

describle tb1_name和 explain tb1_name 语句是show columns from tb1_name语句的同义词。

show table status;show table status from db_name 查看默认数据库或给定数据库表的描述信息。

infomation_schema数据库里的数据

infomation_schema数据库是以sql语言标准为基础。虽然一部分是mysql特有的，优于show语句的可移植性。

infomation_schame数据库可想像为是一个虚拟的数据库是一些由不同的数据库元数据构成的视图。

show tables in infomation_schema;

输出以下结果：

CHARACTER_SETS ：所支持的字符集
COLLATIONS ：每种字符集的排序方式
COLLATION_CHARACTER_SET_APPLICABILITY ：每种排序方式与他的字符集的映射关系信息
COLUMNS ：数据列
COLUMN_PRIVILEGES
ENGINES ：存储引擎
EVENTS ：数据库事件
FILES ： 关于NDB硬盘数据文件的信息。
GLOBAL_STATUS
GLOBAL_VARIABLES ：全局变量
KEY_COLUMN_USAGE ：数据列的约束条件信息
OPTIMIZER_TRACE
PARAMETERS
PARTITIONS ：数据库分区
PLUGINS ：服务插件信息
PROCESSLIST ：服务器内执行的线程信息。
PROFILING
REFERENTIAL_CONSTRAINTS ：外键的信息
ROUTINES ：存储例程（store routine）
SCHEMATA ：数据库
SCHEMA_PRIVILEGES
SESSION_STATUS
SESSION_VARIABLES
STATISTICS ：关于数据表索引特性的信息
TABLES ：数据表
TABLESPACES
TABLE_CONSTRAINTS ： 数据表的约束条件
TABLE_PRIVILEGES
TRIGGERS :触发器
USER_PRIVILEGES ：：全局的权限
VIEWS ：视图
INNODB_LOCKS
INNODB_TRX
INNODB_SYS_DATAFILES
INNODB_FT_CONFIG
INNODB_SYS_VIRTUAL
INNODB_CMP
RDS_EVENTS_THREADS_WAITS_CURRENT
INNODB_CMP_RESET
INNODB_CMP_PER_INDEX
INNODB_CMPMEM_RESET
INNODB_FT_DELETED
INNODB_BUFFER_PAGE_LRU
INNODB_LOCK_WAITS
INNODB_TEMP_TABLE_INFO
INNODB_SYS_INDEXES
INNODB_SYS_TABLES
INNODB_SYS_FIELDS
INNODB_CMP_PER_INDEX_RESET
INNODB_BUFFER_PAGE
INNODB_FT_DEFAULT_STOPWORD
INNODB_FT_INDEX_TABLE
INNODB_FT_INDEX_CACHE
INNODB_SYS_TABLESPACES
INNODB_METRICS
INNODB_SYS_FOREIGN_COLS
INNODB_CMPMEM
INNODB_BUFFER_POOL_STATS
INNODB_SYS_COLUMNS
INNODB_SYS_FOREIGN
INNODB_SYS_TABLESTATS
RDS_PROCESSLIST
INNODB_FT_BEING_DELETED

infomation_schema数据库往往可以通过一些骚操作快速处理一些sql:

比如：
select * from information_schema.INNODB_TRX #查看当前运行的所有事务
select * from information_schema.INNODB_LOCKS # 当前出现的锁
SELECT * from information_schema.INNODB_LOCK_WAITS # 锁等待的对应的关系
快速查出各数据表tabals中的信息：
SELECT
TABLE_NAME,
TABLE_ROWS
FROM
information_schema.TABLES
WHERE
TABLE_SCHEMA = ‘Db名称’;
快速查出数据列的信息：
select table_name,column_name,column_type,column_key,column_comment from information_schema.Columns
where table_schema=‘Db’