mysql常用命令(三)

• 选择数据库

  USE database;
  

• 创建表

  CREATE TABLE [IF NOT EXISTS] table_name (
      column_name data_type,
      ...
  );
  

• 查看表

  SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr];
  

• 查看表结构

  SHOW COLUMNS FROM tbl_name;
  

• 插入记录

  INSERT [INTO] tbl_name [(col_name, ...)] VALUES(val, ...);
  

  • 字段是否可以为空：NULL or NOT NULL
  • 自增：AUTO_INCREMENT
  • 主键：PRIMARY KEY
  • 唯一性：UNIQUE KEY
  • 默认值：DEFAULT
  • 外键：FOREIGN KEY (column_name) REFERENCES tbl_name (column_name) [ON DELETE rule];
    
    • CASCADE: 从父表删除或更新自动删除或更新更新子表中的行
    • SET NULL: 从父表更新或删除，自动设置子表NULL，必须子表没有NOT NULL
    • RESTRICT: 拒绝对父表更新或删除
    • NO ACTION: 拒绝对父表更新或删除

• 查询记录

  SELECT expr, ... FROM tbl_name;
  

• 查看索引

  SHOW INDEXS FROM tbl_name[\G];
  

• 修改数据表

  • 添加单列

    ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST | AFTER col_name];
    

  • 添加多列

    ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition, ...);
    

  • 删除列

    ALTER TABLE tbl_name DROP [COLUMN] col_name;
    

  修改列的操作可以同时进行多个，用逗号隔开。

  • 添加主键约束

    ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name, ...);
    

  • 添加唯一约束

    ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type] (index_col_name, ...);
    

  • 添加外键约束

    ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) reference_definition;
    

  • 添加或删除默认约束

    ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT};
    

  • 删除主键约束

    ALTER TABLE tbl_name DROP PRIMARY KEY;
    

  • 删除唯一约束

    ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
    

  • 删除外键约束

    ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
    

  • 修改列定义

    ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST | AFTER col_name];
    

  • 修改列名称

    ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name];
    

  • 修改数据表

    ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;
    
    RENAME TABLE tbl_name TO new_tbl_name [, tbl_name2 TO new_tbl_name2] ...;