Java数据库开发与实战运用---MySQL基础之数据类型与表的管理

 

4.数据表的相关操作

数据表

创建表

CREATE TABLE [IF NOT EXISTS] tbl_name(
    字段名称 字段类型 [完整性约束条件],
    字段名称 字段类型 [完整性约束条件],
	...
)ENGINE=存储引擎 CHARSET=编码方式;
	

    UNSIGNED
        无符号，没有负数，从0开始
    ZEROFILL
        零填充，当数据的显示长度不够的时候可以使用前补0的效果填充至指定长度,字段会自动添加UNSIGNED
    NOT NULL
        非空约束，也就是插入值的时候这个字段必须要给值,值不能为空
    DEFAULT
        默认值，如果插入记录的时候没有给字段赋值，则使用默认值
    PRIMARY KEY
        主键，标识记录的唯一性，值不能重复，一个表只能有一个主键，自动禁止为空
    AUTO_INCREMENT
        自动增长，只能用于数值列，而且配合索引使用,默认起始值从1开始，每次增长1
    UNIQUE KEY
        唯一性，一个表中可以有多个字段是唯一索引，同样的值不能重复，但是NULL值除外
    FOREIGN KEY
        外键约束

查看当前数据库下已有数据表

    SHOW TABLES;
    SHOW [FULL] TABLES [{FROM | IN} db_name]
    [LIKE 'pattern' | WHERE expr]

查看指定数据表的详细信息

   SHOW CREATE TABLE tbl_name;

查看表结构

 DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

删除指定的数据表

    DROP TABLE [IF EXISTS] tbl_name;

表结构相关操作

    添加字段
        ALTER TABLE tbl_name 
ADD 字段名称 字段属性 [完整性约束条件] [FIRST|AFTER 字段名称]
    删除字段
        ALTER TABLE tbl_name
DROP 字段名称
    添加默认值
        ALTER TABLE tbl_name
ALTER 字段名称 SET DEFAULT 默认值;
    删除默认值
        ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT
    修改字段类型、字段属性
        ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称]
    修改字段名称、字段类型、字段属性
        ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]
    添加主键
        ALTER TABLE tbl_name
ADD PRIMARY KEY(字段名称)
    删除主键
        ALTER TABLE tbl_name
DROP PRIMARY KEY;
    添加唯一
        ALTER TABLE tbl_name
ADD UNIQUE KEY|INDEX [index_name] (字段名称)
    删除唯一
        ALTER TABLE tbl_name 
DROP index_name;
    修改数据表名称
        ALTER TABLE tbl_name 
RENAME [TO|AS] new_tbl_name
        RENAME TABLE tbl_name TO new_tbl_name;
    修改AUTO_INCREMENT的值
        ALTER TABLE tbl_name AUTO_INCREMENT=值

6.MySQL存储引擎

    MyISAM存储引擎

        默认MyISAM的表会在磁盘中产生3个文件

•             .frm
•                 表结构文件
•             .MYD
•                 数据文件
•             .MYI
•                 索引文件

        可以在创建的时候指定数据文件和索引文件的存储位置，只有MyISAM表支持
            DATA DIRECORY [=] 数据保存的绝对路径
            INDEX DIRECTORY [=] 索引文件保存的绝对路径
        MyISAM单表最大支持的数据量2的64次方条记录
        每个表最多可以建立64个索引
        如果是复合索引，每个复合索引最多包含16个列，索引值最大长度是1000B
        MyISAM引擎的存储格式
            定长（FIXED 静态）
                是指字段中不包含VARCHAR/TEXT/BLOB
            动态（DYNAMIC）
                只要字段中包含了VARCHAR/TEXT/BLOB
            压缩（COMPRESSED）
                myisampack创建
    InnoDB存储引擎
        设计遵循ACID模型，支持事务，具有从服务崩溃中恢复的能力，能够最大限度保护用户的数据
        支持行级锁，可以提升多用户并发时的读写性能
        支持外键，保证数据的一致性和完整性
        InnoDB拥有自己独立的缓冲池，常用的数据和索引都在缓存中
        对于INSERT、UPDATE、DELETE操作，InnoDB会使用一种change buffering的机制来自动优化，还可以提供一致性的读，并且还能够缓存变更的数据，减少磁盘I/O，提高性能
        创建InnoDB表之后会产生两个文件
            .frm表结构文件
            .ibd,数据和索引存储表空间中
        所有的表都需要创建主键，最好是配合上AUTO_INCREMENT,也可以放到经常查询的列作为主键

MySQL数据操作

添加记录

    INSERT [INTO] tbl_name[(col_name,...)] {VALUE|VALUES}(VALUES...);

    不指定字段名称
        INSERT tbl_name VALUE(value...)
        需要按照建表时的字段顺序给每一个字段赋值
    列出指定字段
        INSERT tbl_name(字段名称,...) VALUES(值,...)
    INSERT ... SET的形式
        INSERT tbl_name SET 字段名称=值,...;
    INSERT ... SELECT
        INSERT tbl_name[(字段名称...)] SELECT 字段名称,... FROM  tbl_name [WHERE 条件]
    一次添加多条记录
        INSERT tbl_name[(字段名称,...)] VALUES(值,...),
(值,....),
(值,...)