表相关操作

一、表介绍

– 数据库是由多个数据表构成的

– 每张数据表存储多个字段

– 每个字段由不同的字段名及记录构成，每个字段有自己的数据结构及约束条件

二、创建表

#语法：
create table 表名(
字段名1 类型[(宽度) 约束条件],
字段名2 类型[(宽度) 约束条件],
字段名3 类型[(宽度) 约束条件]
);

#注意：
1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

 

四 查看表结构

Desc 表名;

五 数据类型

  (1)   数据类型-数值类型

– INT：有符号的和无符号的。有符号大小-2147483648~2147483647，无符号大0~4294967295。 宽度最多为11个数字- int(11)

– TINYINT:有符号的和无符号的。有符号大小-128~127，无符号大小为0~255。宽度最多为4个数 字- tinyint(4)

– SMALLINT:有符号的和无符号的。有符号大小-32768~32767，无符号大小为0~65535。宽度最 多为6个数字- smallint(6)

– MEDIUMINT:有符号的和无符号的。有符号大小-8388608~8388607，无符号大小为0~16777215。 宽度最多为9个数字- mediumint(9)

– BIGINT:有符号的和无符号的。宽度最多为20个数字- bigint(20)

– FLOAT(M,D):只能为有符号的。默认为(10,2)

– DOUBLE(M,D):只能为有符号的。默认为(16,4)

– DECIMAL(M,D):只能为有符号的

（2）数据类型-日期和时间类型

– DATE:YYYY-MM-DD格式，在1000-01-01和9999-12-31之间。例如：1973-12-30


– DATETIME:YYYY-MM-DD HH:MM:SS格式，位于1000-01-01 00:00:00和9999-12-31 23:59:59 之间。例如：1973-12-30 15:30:00


– TIMESTAMP:称为时间戳，在1970-01-01 00:00:00和2037-12-31 23:59:59之间。例如，1973年 12月30日下午15:30，则在数据库中存储为:19731230153000


– TIME: 以HH:MM:SS格式, -838:59:59~838:59:59


– YEAR(2|4): 以2位或4位格式存储年份值。如果是2位，1970~2069；如果是4位，1901~2155。 默认长度为

（3）数据类型-字符串类型

– CHAR(M)：固定长度字符串，长度为1-255。如果内容小于指定长度，右边填充空格。如果不指 定长度，默认为1

– VARCHAR(M)： 可变长度字符串，长度为1-255。定义该类型时必须指定长度

– BLOB 或TEXT：最大长度65535。存储二进制大数据，如图片。不能指定长度。两者区别： BLOB 大小写敏感

– TINYBLOB 和TINYTEXT：最大长度255。不能指定长度。

– MEDIUMBLOB 或MEDIUMTEXT：最大长度16777215 字符

– LONGBLOB 或LONGTEXT：最大长度4294967295 字符

– ENUM：枚举。例如：ENUM(‘A’,’B’,’C’)。NULL 值也可

六、约束条件

– 约束是在表上强制执行的数据检验规则

– 用来保证创建的表的数据完整和正确

 MySQL数据库常用约束条件有以下这些：

主键约束

主键约束：保证表中每行记录都不重复

主键，又称为”主码”，是数据表中一列或多列的组合。 主键约束要求主键列的数据必须是唯一的，并且 不允许为空。

使用主键，能够惟一地标识表中的一条记录，并且可以结合外键来 定义不同数据表之间的关 系，还可以加快数据库查询的速度

--主键分为两种类型：

 -- 单字段主键：
 create table emp(
          depid char(3) primary key,
          depname varchar(20),
          peoplecount int
);

-- 多字段联合主键：
create table emp( depid char(3), 
        depname varchar(20),
        peoplecount int, 
        primary key(depname,depid) );

非空约束

非空约束，指的是字段的值不能为空

– 语法：字段名 字段类型 not null

create table emp(
          depid char(3) primary key, 
          depname varchar(20) not null, 
          peoplecount int
);

唯一性约束

唯一性约束，要求该列的值必须是唯一的

– 允许为空，但只能出现一个空值;
– 一个表中可以有多个字段声明为唯一的; 
– 唯一约束确保数据表的一列或几列不出现重复值; 
– 语法：字段名 数据类型 unique

create table emp(
          depid char(3) primary key,
          depname varchar(20) not null, 
          peoplecount int unique
);

默认约束

默认约束，指定某个字段的默认值

– 如果新插入一条记录时没有为默认约束字段赋值，那么系统就会自动为这个字段赋值为默认约束
设定的值
– 语法： 字段名 数据类型 default 默认值
create table emp(
          depid char(3) primary key, 
          depname varchar(20) not null default '-', 
          peoplecount int unique
);

自增字段

自增字段：一个表只能有一个自增字段，自增字段必须为主键的一部分。默认情况下从1开始自增

例： 创建含各种约束条件的数据表
CREATE TABLE example(id INT PRIMARY KEY AUTO_INCREMENT, -- 创建整数型自增主键
                      name VARCHAR(4) NOT NULL, -- 创建非空字符串字段 
                      math INT DEFAULT 0, -- 创建默认值为0的整数型字段 
                      minmax FLOAT UNIQUE – 创建唯一约束小数型字段 );

练习： 尝试用语言描述以下建表语句

create table fruits(
                  f_id char(10) not null,
                  s_id int not null default 0,
                  f_name char(255) not null,
                  f_price decimal(8,2) not null,
                  primary key(f_id)
 );

 

七、表结构的操作——增删改查

-- 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 (非空且唯一)  :能够唯一区分出当前记录的字段称为主键！
       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