MySQL 存储引擎 ALTER命令 添加 修改 字段 索引 表结构

菜鸟教程 
菜鸟教程 | MySQL ALTER命令 
MySQL 教程_w3cschool 

存储引擎
MySQL5.5以后默认使用InnoDB存储引擎

1、MyISAM：它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建表。
2、InnoDB：InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。


修改数据库表引擎模式：

ALTER TABLE 表名 ENGINE = InnoDB;

C# Entity Framework 代码中使用事物

1、添加引用System.Transactions
2、using System.Transactions;

public bool CreatePost(Posts data, Dictionary<string, string> dicList)
{
    try
    {
        using (TransactionScope tran = new TransactionScope())
        {
            dbContent.PostService.Add(data);
            dbContent.SaveChanges();
            long id = data.ID;
            foreach (var item in dicList)
            {
                var model = new PostMeta
                {
                    post_id = id,
                    meta_key = item.Key,
                    meta_value = item.Value,
                };
                dbContent.PostMetaService.Add(model);
            }
            dbContent.SaveChanges();
            tran.Complete();
            return true;
        }
    }
    catch (Exception ex)
    {
        Log4netHelper.Tool.LogError("CreatePostNew()", ex.Message);
    }
    return false;
}

*、查询表结构，方式1

SELECT (@row_number:=@row_number + 1) AS 序号
,COLUMN_NAME AS '列名'
,DATA_TYPE AS '数据类型'
,COLUMN_TYPE AS '数据类型1'
,CHARACTER_MAXIMUM_LENGTH '长度'
,COLUMN_COMMENT AS '注释'
FROM INFORMATION_SCHEMA.COLUMNS,(SELECT @row_number:=0) AS t
WHERE TABLE_SCHEMA = 'Asset' AND TABLE_NAME = 'sys_user'
ORDER BY ORDINAL_POSITION ASC;

*、查询表结构，方式2

SET @row_number = 0;
SELECT (@row_number:=@row_number + 1) AS 序号
,COLUMN_NAME AS '字段名称'
,COLUMN_TYPE AS '字段类型'
,CHARACTER_MAXIMUM_LENGTH '长度'
,IF
    (IS_NULLABLE = 'YES', 'N', 'Y') 必填
,IF
    (IS_NULLABLE = 'YES', '是', '否') 允许为空
,IF
    (column_key = 'PRI', 'Y', '')  主键
,COLUMN_COMMENT AS '字段注释'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_schema = 'Asset' AND table_name = 'sys_user'
ORDER BY ORDINAL_POSITION ASC;

*、查询表结构，精细版本

SELECT COLUMN_NAME F_FIELD
,data_type F_DATATYPE
,CHARACTER_MAXIMUM_LENGTH F_DATALENGTH
,NUMERIC_PRECISION 精度
,NUMERIC_SCALE 小数位数
,IF
    (IS_NULLABLE = 'YES', '1', '0') F_ALLOWNULL,
    COLUMN_COMMENT F_FIELDNAME
,IF
    (COLUMN_KEY = 'PRI', '1', '0') F_PRIMARYKEY,
    column_default F_DEFAULTS,CONCAT( upper( COLUMN_NAME ), '(', COLUMN_COMMENT, ')' ) AS 'F_DESCRIPTION' 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE table_schema = 'Asset' AND table_name = 'sys_user'
ORDER BY ORDINAL_POSITION ASC;

*、查询表结构，查询所有表字段

SELECT TABLE_NAME, COLUMN_NAME,DATA_TYPE,COLUMN_TYPE,COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'Asset'
ORDER BY ORDINAL_POSITION ASC;

*、修改表的名字

ALTER TABLE 旧表名 RENAME AS 新表名
alter table old_name rename to new_name
或
rename table old_name to new_name

*、添加表字段

ALTER TABLE table_name ADD column_name data_type
ALTER TABLE 表名 ADD 字段名 列属性
alter table table_name add column_name column_type
ALTER TABLE teachers ADD age INT(11)

*、修改表字段（修改列定义）

ALTER TABLE 表名 MODIFY 字段名 列类型[属性]
ALTER TABLE 表名 MODIFY 字段名 [列属性];
ALTER TABLE teachers MODIFY age VARCHAR(11);

*、字段重命名（修改列名称）

ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列属性[属性]
alter table table_name change column_name new_column_name new_column_name_type
ALTER TABLE teachers CHANGE age age1 INT(1)

*、修改字段名

ALTER TABLE 表名 RENAME COLUMN 旧字段名 TO 新字段名;
ALTER TABLE player RENAME COLUMN nick_name TO nick_name_1;

*、删除表字段

ALTER TABLE 表名 DROP 字段名
alter table table_name drop column_name;

*、删除表

-- 语法：DROP TABLE [IF EXISTS] 表名
● IF EXISTS为可选 , 判断是否存在该数据表
● 如删除不存在的数据表会抛出错误

-- 删除表(如果存在再删除)
DROP TABLE IF EXISTS teachers;

*、创建索引 - 主键索引（PRIMARY KEY）

ALTER TABLE tableName ADD PRIMARY INDEX (columnName)

*、创建索引 - 普通索引（KEY / INDEX）

-- 直接创建普通索引
CREATE INDEX index_name ON table_name (columnName)
CREATE INDEX index_name ON table_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);
-- 创建表的时候指定普通索引
CREATE TABLE tableName(......INDEX [indexName] (columeName)
-- 修改表结构添加普通索引
ALTER TABLE table_name ADD INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

*、创建索引 - 唯一索引（UNIQUE KEY）
索引列的值必须唯一，但允许有空值，唯一索引可以有多个

-- 直接创建唯一索引
CREATE UNIQUE INDEX indexName ON tableName(columnName)
-- 创建表的时候指定唯一索引
CREATE TABLE tableName(  ......UNIQUE INDEX [indexName] (columeName));  
-- 修改表结构添加唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX [indexName] (columnName)
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column1 [ASC|DESC], column2 [ASC|DESC], ...);

*、全文索引（FULLText）
只能在文本类型CHAR，VARCHAR, TEXT类型字段上创建此索引

-- 直接创建全文索引
CREATE FULLTEXT INDEX ON tablename (字段名);
-- 添加全文索引
ALTER TABLE table_name ADD FULLTEXT 索引名字 (字段名);
ALTER TABLE table_name ADD FULLTEXT INDEX fulltext_article(title,content);

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ;

-- 使用全文索引的格式
MATCH (columnName) AGAINST ('string')
SELECT * FROM `student` WHERE MATCH(`索引字段名称`) AGAINST('查询字符串')

*、删除索引：

-- 删除索引【使用 DROP INDEX 语句删除索引】
DROP INDEX 索引名 ON 表名;
-- 删除索引【使用 ALTER TABLE 语句删除索引】
ALTER TABLE table_name DROP INDEX index_name;
-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;

*、普通索引，多列索引，唯一索引，全文索引

1、创建索引【在已建表中添加索引】
CREATE INDEX indexName ON tableName (columnName(length));

# 普通索引
create index index_name on t_dept(name);

# 多列索引
create index index_name_no on t_dept(name,no)

# 唯一索引
create unique index index_name on t_dept(name);

# 全文索引
create fulltext index index_name on t_dept(name);

2、创建索引【以修改表的方式添加索引】
ALTER TABLE tableName ADD INDEX indexName(columnName);
# 普通索引
alter table t_dept add index index_name(name);

# 多列索引
alter table t_dept add index index_name_no(name,no);

# 唯一索引
alter table t_dept add unique index index_name(name);

# 全文索引
alter table t_dept add fulltext index_name(name);

3、删除索引
ALTER TABLE table_name DROP INDEX index_name;
DROP INDEX index_name ON table_name;

*
*
*
*
*