数据库之数据表操作

基础操作

1.创建表（类似于一个excel表）

    create table tab_name(
        字段 
        field1 type  [完整性约束条件],
        field2 type,
        ...
        fieldn type
    )[character set xxx];

         -- 创建一个员工表employee

         create table employee(
            id int primary key auto_increment ,
            name varchar(20),
            gender bit default 1,   -- gender char(1)  default 1   -----    或者 TINYINT(1) 
            birthday date,
            entry_date date,
            job varchar(20),
            salary double(4,2) unsigned,
            resume text    -- 注意，这里作为最后一个字段不加逗号
          );

/* 约束:
primary key/key (非空且唯一) :能够唯一区分出当前记录的字段称为主键！
unique 唯一
not null 非空
auto_increment 主键字段必须是数字类型。
外键约束 foreign key */

– 2.查看表信息

   desc tab_name 查看表结构
   show columns from tab_name  查看表结构
   show tables 查看当前数据库中的所有的表
   show create table tab_name    查看当前数据库表建表语句 

– 3.修改表结构
– (1)增加列(字段)

  alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;
  alter table user add addr varchar(20) not null unique first/after username;
  #添加多个字段
  alter table users2 
        add addr varchar(20),
        add age  int first,
        add birth varchar(20) after name;

– (2)修改一列类型

  alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］;
  alter table users2 modify age tinyint default 20;
  alter table users2 modify age int  after id;

– (3)修改列名

  alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
  alter table users2 change age Age int default 28 first;

– (4)删除一列

  alter table tab_name drop [column] 列名;
  -- 思考：删除多列呢？删一个填一个呢？
  alter table users2 
        add salary float(6,2) unsigned not null after name,
        drop addr;    

– (5)修改表名

  rename table 表名 to 新表名;

– (6)修该表所用的字符集

  alter table student character set utf8;

– 4.删除表

drop table tab_name;

—5 添加主键，删除主键

alter table tab_name add primary key(字段名称,...) 
alter table users drop primary key;

eg:
mysql> create table test5(num int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
create table test(num int primary key auto_increment);
-- 思考，如何删除主键？
alter table test modify id int;   -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句
alter table test drop primary key;-- 仅仅用这句也无法直接删除主键

– 唯一索引
alter table tab_name add unique [index|key] 索引名称

alter table users add unique(name)-- 索引值默认为字段名show create table users;
alter table users add unique key user_name(name);-- 索引值为user_name

-- 添加联合索引
alter table users add unique index name_age(name,age);#show create table users;

-- 删除唯一索引
alter table tab_name drop {index|key} index_name

记录操作

写入记录
　在写入记录时，要注意的是，如果没有省略的字段，则按照参数顺序直接赋值即可；如果有省略的字段，则字段名和参数值都需要写出来
　INSERT [INTO] tbl_name [(col_name,...)] VALUES(VAL,...)

查找记录
SELECT EXPR,...FROM tbl_name

记录约束
空值

NULL 字段值可以为空
NOT NULL 字段值禁止为空

首先，创建一个数据表tb2，username字段禁止为空，而age字段可以为空。插入记录时，如果username字段为空，则提示错误

主键
　每张数据表只能存在一个主键(PRIMARY KEY)，主键保证记录的唯一性，且自动为NOT NULL

主键可以写为KEY或PRIMARY KEY

自动编号
自动编号(AUTO_INCREMENT)必须与主键组合使用，默认情况下，起始值为1，每次增量为1

唯一
　唯一约束(UNIQUE KEY)是指选定的记录中不可以存在相同值的情况，这样可以保证记录的唯一性，唯一约束的字段可以为空值NULL，每张数据表可以存在多个唯一约束
　　下面记录中对username字段进行唯一约束限制，添加username为’Tom’记录后，不允许再添加username为’Tom’记录

默认值
当插入记录时，如果没有明确为字段赋值，则自动赋予默认值(DEFAULT)

外键
　外键约束(FOREIGN KEY)用来保持数据一致性和完整性，实现一对一或一对多的关系
　外键列是指加入(FOREIGN KEY)的列，外键列参照的那一列叫做参照列，外键列和参数列必须具有相似的数据类型。其中数字的长度或是否有符号位必须相同；而字符的长度则可以不同
　　索引是一种特殊的文件，在InnoDB数据表上的索引是表空间的一个组成部分，它们包含着对数据表中所有记录的引用指针。外键列和参照列必须创建索引，如果参照列不存在索引的话，MySQL将自动创建索引
　　子表指有外键列的表，子表所参照的表叫做父表。父表和子表必须使用相同的存储引擎，而且禁止使用临时表。数据表的存储引擎只能是InnoDB
　　所以，首先需要修改mysql配置文件my.ini中的默认存储引擎
　　default-storage-engine = INNODB
创建父表provices，参照列为id

　　
　　创建子表users，外键列为pid
　　
　　父表provices的参照列id为主键列，主键在创建的同时，会自动创建索引
　　查看父表provices的索引
　　
-----------增加外键和删除外键---------

ALTER TABLE student  ADD CONSTRAINT abc
                     FOREIGN KEY(charger_id)
                     REFERENCES  classcharger(id);


ALTER TABLE student DROP FOREIGN KEY abc;　　

外键约束的参照操作中，一共存在四个选项。用来设置更新父表时，子表是否也进行相应操作

1、CASCADE: 从父表删除或更新且自动删除或更新子表中匹配的行

2、SET NULL: 从父表删除或更新行，并设置子表中的外键列为NULL。如果使用该选项，必须保证子表列没有指定NOT NULL

3、RESTRICT: 拒绝对父表的删除或更新操作

4、NO ACTION: 标准SQL的关键字，在MYSQL中与RESTRICT相同

创建父表provinces

　创建子表users1，并设置选项cascade
　
在父表中插入记录’A’、‘B’、‘C’

在子表中，插入名字’a1’、‘a2’、‘a3’、‘a4’

删除父表中id为2的记录，并查看删除后父表和子表的结果

列操作

添加单列
ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];

添加多列
ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);
　添加多列只能位于最后

删除单列
ALTER TABLE tb1_name DROP [COLUMN] col_name

删除多列
ALTER TABLE tb1_name DROP col1_name, DROP col2_name, ...

约束操作

添加主键约束
ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] PRIMARY KEY [index_type](index_col_name,...)

在未添加主键前，表users2有’username’、'pid’和’id’三个field

向字段’id’添加主键约束

添加唯一约束
唯一约束与主键约束的不同之处在于，唯一约束可以存在多个字段，而主键约束只能有一个
ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]

添加外键约束
ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

添加或删除默认约束
ALTER TABLE tb1_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

修改列

修改列定义指修改列的类型或位置– MODIFY

ALTER TALBE tb1_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

下面将字段’id’的位置调整到第一个

下面，将字段’id’的类型由smallint转换为tinyint

[注意]在由大范围的类型转换为小范围类型时，可能会造成数据丢失

修改列名称CHANGE

使用下面的CHANGE语法，比MODIFY语法更加强大，可以在修改列名称的同时，修改列类型
ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

数据表更名RENAME

方法一
ALTER TABLE tb1_name RENAME [TO|AS] new_tb1_name

方法二
使用RENAME方法可以为多张数据表更名
RENAME TABLE tb1_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...