day34

MySQL 表操作

1.1 列类型

• 数字类型

  1. 实际使用中，对于整数类型，一般使用 int 就足够了。

  2. 整数类型后面可以加上 unsigned 例如 int unsigned 表示只取正整数。

  3. 当需要存非常精确的小数时，推荐使用 decimal 类型，因为当 float 存的小数位过长时，会自动进行四舍五入。

  • 整数类型

    • tinyint

      用途：小整数值
      大小：1个字节
      范围（有符号）：（-128，127）
      范围（无符号）：（0，255）
      

    • smallint

      用途：大整数值
      大小：2个字节
      范围（有符号）：（-32768，32767）
      范围（无符号）：（0，65535）
      

    • mediumint

      用途：大整数值
      大小：3个字节
      范围（有符号）：（-8388608，8388607）
      范围（无符号）：（0，16777215）
      

    • int

      用途：大整数值
      大小：4个字节
      范围（有符号）：（-2147483648，2147483647）
      范围（无符号）：（0，4294967295）
      

    • bigint

      用途：极大整数值
      大小：8个字节
      范围（有符号）：（-9233372036854775808，9233372036854775807）
      范围（无符号）：（0，18446744073709551615）
      

  • 浮点型

    • float

      用途：单精度浮点数值
      大小：4个字节
      范围（有符号）：（-3.402823466E+38，-1.175494351E-38），0，（1.175494351E-38，3.402823466E+38）
      范围（无符号）：0，（1.175494351E-38，3.402823466E+38）
      

    • double

      用途：双精度浮点数值
      大小：8个字节
      范围（有符号）：（-1.7976931348623157E+308，-2.2250738585072014E-308），0，（2.2250738585072014E-308，1.7976931348623157E+308）
      范围（无符号）：0，（2.2250738585072014E-308，1.7976931348623157E+308）
      

    • decimal

      用途：小数值
      大小：当decimal(m,d)中，m>d，大小为m+2，否则大小为d+2
      范围（有符号）：取决于m和d的值
      范围（无符号）：取决于m和d的值
      

• 字符串类型

  如果不能确定字符串的长度是多少，一般建议使用 varchar(255) 。

  • char(数值)

    无论插入的字符串长度是多少，永远固定的占用设置的长度。

    用途：存储字符串
    字符长度范围：0-255（一个中文是一个字符，是utf8编码的3个字节）
    存储特点：定长、浪费空间、存取速度快
    
    ***存储时会向右填充空格来补全设置的长度
    例如：char(5)
    当存小于5的字符串时，自动在字符串右边填充空格使其长度变为5，查询时会自动移除填充的空格
    

  • varchar(数值)

    根据插入的字符串长度，缩减所占用的空间，但是会多占用一个字节用来保存字符串的大小。

    用途：存储字符串
    字符长度范围：0-65535（如果大于21845会提示用其他类型，mysql行最大限制为65535字节，字符编码为utf-8）
    存储特点：变长、精准、节省空间，存取速度慢
    
    ***存储时输入的真实数据内容，输入什么存什么
    存储时，会在真实数据前加1-2bytes的前缀，用来表示真实数据的bytes字节数(1-2Bytes最大表示65535个数字，正好符合mysql对row的最大字节限制，即已经足够使用)
    如果真实的数据<255bytes则需要1Bytes的前缀（1Bytes=8bit 2**8最大表示的数字为255）
    如果真实的数据>255bytes则需要2Bytes的前缀（2Bytes=16bit 2**16最大表示的数字为65535）
    
    *** 当存储的数据超过设定的长度时，mysql默认会将数据按照最大长度截取存储，然后给出一个 warning ，
    

• 时间日期类型

  1. 实际使用一般选择 datetime 类型。

  2. 单独插入时间时，需要以字符串的形式，按照对应的格式插入。

  3. 插入年份时，尽量使用4位值。

  ** 4. datetime 和 timestamp 的区别：

  datetime 的日期范围是1001——9999年，timestamp 的日期范围是1970——2038年；
  datetime 的存储与时区无关，timestamp 的存储与时区有关，显示的值也依赖于时区；
  datetime 使用8字节的存储空间，timestamp 的存储空间为4字节，因此，timestamp 比 datetime 的空间利用率更高；
  datetime 的默认值为(null)，timestamp 的字段默认不为空(not null)，默认值为当前时间（current_timestamp）。
  

  • year

    用途：记录年份
    范围：YYYY（1901，2155）
    

  • date

    用途：记录年月日
    范围：YYYY-MM-DD（1000-01-01，9999-12-31）
    

  • time

    用途：记录时分秒
    范围：HH:MM:SS（'-838:59:59'，'838:59:59'）
    

  • datetime

    用途：记录年月日时分秒
    范围：YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59）
    

  • timestamp

    用途：记录年月日时分秒
    范围：YYYY-MM-DD HH:MM:SS（1970-01-01 00:00:00/2037 年某时）
    

• 枚举类型

  • enum

    用途：在给定的范围内选择一个值，例如 enum('值1','值2','值3')
    *** 如果插入的数据不在给定的范围内，会报错
    

• 集合类型

  • set

    用途：在给定的范围可选择多个值，例如 enum('值1','值2','值3')
    例如：插入('值1,值2') /('值2,值3') /('值1,值3') /('值1,值2,值3') 
    
    当插入的数据不在给定的范围内，会给出警告，并且不在范围内的值不会被插入
    例如：插入('值1,值2,值3,值4') # 只会保存 值1,值2,值3 
    

1.2 列约束条件

• not null

  作用：标识该字段不能为空，如果没有给该字段插入数据，则报错
  

• default

  作用：给字段设定默认值，如果该字段没有插入数据则会自动填入设置的默认值
  

• primary key

  作用：用来唯一的约束该字段里面的数据，不能重复，不能为空，一张表中最多只能有一个主键；主键所在的列通常是整数类型。
  当对应的字段，不给值，会自动的被系统触发，系统会从当前字段中已经有的最大值+1操作，得到一个新的不同值，通常和主键搭配使用，作为逻辑主键
  

• auto_increment

  1. 自增长字段必须是整数；

  2. 一张表最多只能有一个自增长。

  作用：标识该字段的值自动增长（整数类型，而且为主键）
  

• foreign key

  作用：当定义外键后，要求外键列数据必须在主表的主键列存在或为null
  

• unique key

  作用：标识该字段的值是唯一的，如果该字段插入的数据已存在，则会报错
  

• unsigned

  作用：标识整数类型为无符号类型，即不为负数
  

• zerofill

  作用：一般跟在整数类型之后，当输入的整数不足设置的长度时，在数据的左侧用0填充到设定的长度
  

1.3 增（创建表）

create table 表名（
	字段名1  列类型 [(宽度) 约束条件]，	# 写完一个字段必须加逗号
	字段名2  列类型 [(宽度) 约束条件]，
	字段名3  列类型 [(宽度) 约束条件]，
	... ...
	字段名n  列类型 [(宽度) 约束条件]	# 最后一个字段不能加逗号
) charset utf8 ;					# 最后以分号结尾
	

实例：

有一个商店shop的数据，记录客户及购物情况，有以下三个表组成：

    商品goods(商品编号goods_id，商品名goods_name, 单价unitprice, 商品类别category, 供应商provider)
    客户customer(客户号customer_id,姓名name,住址address,邮箱email,性别sex，身份证card_id)
    购买purchase(购买订单号order_id,客户号customer_ id,商品号goods_ id,购买数量nums)
    要求：
    每个表的主外键
    客户的姓名不能为空值
    邮箱不能重复
    客户的性别(男，女)

# 创建 shop 数据库，字符编码为utf8
mysql> create database shop charset utf8;
Query OK, 1 row affected (0.01 sec)

# 进入 shop 数据库
mysql> use shop;
Database changed

# 创建 goods 数据表
mysql> create table goods(
    -> goods_id int unsigned auto_increment primary key,
    -> goods_name varchar(32) not null default '',
    -> unitprice decimal(5,2) not null default 0,
    -> category varchar(32) not null default '',
    -> provider varchar(32) not null default ''
    -> )charset utf8;
Query OK, 0 rows affected (0.03 sec)

# 创建 customer 数据表
mysql> create table customer(
    -> customer_id int unsigned auto_increment primary key,
    -> address varchar(256) not null default '',
    -> email varchar(32) unique key not null default '',
    -> gender enum('男','女') not null,
    -> card_id char(18) unique key not null default ''
    -> )charset utf8;
Query OK, 0 rows affected (0.03 sec)

# 创建 purchase 数据表
mysql> create table purchase(
    -> order_id int unsigned auto_increment primary key,
    -> customer_id int unsigned,
    -> goods_id int unsigned,
    -> nums int default 0,
    -> foreign key (customer_id) references customer(customer_id),
    -> foreign key (goods_id) references goods(goods_id)
    -> )charset utf8;
Query OK, 0 rows affected (0.02 sec)

# 查看当前数据库下的数据表
mysql> show tables;
+----------------+
| Tables_in_shop |
+----------------+
| customer       |
| goods          |
| purchase       |
+----------------+
3 rows in set (0.00 sec)

1.4 改

• 修改表名

  alter table 表名 rename 新表名；
  

• 增加字段

  • 直接增加字段（位置始终在最后）

    # 增加一条
    alter table 表名 add 字段名 列类型 [(宽度) 约束条件];
    
    # 增加多条
    alter table 表名 add 字段名1 列类型 [(宽度) 约束条件],
    add 字段名2 列类型 [(宽度) 约束条件],
    ......
    add 字段名n 列类型 [(宽度) 约束条件];
    

  • 指定位置增加字段

    # 新增字段为表的第一个字段
    alter table 表名 add 字段名 列类型 [(宽度) 约束条件] first;
    
    # 新增字段到特定字段之后
    alter table 表名 add 字段名 列类型 [(宽度) 约束条件] after 已存在的字段名；
    

• 修改字段

  # modify
  alter table 表名 modify 原字段名 新列类型 [(宽度) 约束条件]; 
  
  # change
  alter table 表名 change 原字段名 新字段名 新列类型 [(宽度) 约束条件]; 
  

1.5 删

• 删除表

  drop table 表名;
  

• 删除字段

  alter table 表名 drop 字段名;
  

1.6 查

• 查看数据库下的表

  mysql> show tables;
  +----------------+
  | Tables_in_shop |
  +----------------+
  | customer       |
  | goods          |
  | purchase       |
  +----------------+
  3 rows in set (0.00 sec)
  

• 查看表信息

  # 1.查看表创建信息
  mysql> show create table goods\G
  *************************** 1. row ***************************
         Table: goods
  Create Table: CREATE TABLE `goods` (
    `goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `goods_name` varchar(32) NOT NULL DEFAULT '',
    `unitprice` decimal(5,2) NOT NULL DEFAULT '0.00',
    `category` varchar(32) NOT NULL DEFAULT '',
    `provider` varchar(32) NOT NULL DEFAULT '',
    PRIMARY KEY (`goods_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
  1 row in set (0.00 sec)
  
  # 2. 查看表创建信息
  mysql> show create table goods;
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | Table | Create Table
  
  
                                                             |
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | goods | CREATE TABLE `goods` (
    `goods_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `goods_name` varchar(32) NOT NULL DEFAULT '',
    `unitprice` decimal(5,2) NOT NULL DEFAULT '0.00',
    `category` varchar(32) NOT NULL DEFAULT '',
    `provider` varchar(32) NOT NULL DEFAULT '',
    PRIMARY KEY (`goods_id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
  +-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  1 row in set (0.00 sec)
  

1.7 复制表

• 复制表结构+数据

  # 复制时，不会复制 key（主键，外键，索引）
  create table 新表名 select * from 旧表名;
  
  

• 复制表结构

  # 1.
  create table 新表名 select * from 旧表名 where 1=2;  # （可以任意设置设置一个不成立的条件）
  
  # 2. 
  create table 新表名 like 旧表名;