🏖这一篇 我们讨论一下数据库和数据表的相关操作..

1. 数据库相关操作

1.1创建数据库

  • 普通创建

    CREATE {DATABASE|SCHEMA} db_name;

  • 检测数据库名称是否存在，不存在则创建

    CREATE DATABASE [IF NOT EXISTS] db_name;

  • 在创建数据库的同时指定编码方式

    CREATE DATABASE [IF NOT EXISTS] db_name [DEFAULT] CHARACTER SET [=] charset;

  • 注意!

    1. 数据库名称最好有意义
    2. 名称不要包含特殊字符或者是MySQL关键字

1.2 查看当前服务器下全部数据库

SHOW DATABASES|SCHEMAS;

1.3 查看指定数据库的详细信息

SHOW CREATE BATABASE db_name;

1.4 修改制定数据库的编码方式

ALTER DATABASE db_name [DEFAULT] CHARACTER SET [=] charset;

1.5 打开指定数据库

USE db_name;

1.6 得到当前打开的数据库

SELECT DATABASE()|SCHEMA();

1.7 删除指定的数据库

  • 直接删除

    DROP DATABASE db_name;

  • 如果数据库存在则删除

    DROP DATABASE [IF EXISTS] db_name;

2. 数据表操作

2.1 数据表

  • 是数据库最重要的组成部分之一，数据是保存在数据表中
  • 数据表由行（row）和列（column）来组成
  • 每个数据表中至少有一列，行可以有零行一行或者多行组成
  • 表名要求唯一，不要包含特殊字符，最好含义明确

2.2 创建表

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 外键 外键约束

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

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

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

SHOW CREATE TABLE tbl_name;

2.5 查看表结构

DESC tbl_name;
DESCRIBE tbl_name;
SHOW COLUMNS FROM tbl_name;

2.6 删除指定的数据表

DROP TABLE [IF EXISTS] tbl_name;

2.7 表结构相关结构

  1. 添加字段

    ALTER TABLE tbl_name;
ADD 字段名称 字段属性 [完整性约束条件][FIRST|AFTER 字段名称]

  2. 删除字段

    ALTER TABLE tbl_name;
DROP 字段名称

  3. 添加默认值

    ALTER TABLE tbl_name
ALTER 字段名称 SET DEFAULT 默认值;

  4. 删除默认值

    ALTER TABLE tbl_name
ALTER 字段名称 DROP DEFAULT;

  5. 修改字段类型、字段属性

    ALTER TABLE tbl_name
MODIFY 字段名称 字段类型 [字段属性] [FIRST | AFTER 字段名称];

  6. 修改字段名称、字段类型、字段属性

    ALTER TABLE tbl_name
CHANGE 原字段名称 新字段名称 字段类型 字段属性 [FIRST | AFTER 字段名称]

  7. 添加主键

    ALTER TABLE tbl_name
ADD PRIMARY KEY(字段名称);

  8. 删除主键

    ALTER TABLE tbl_name
DROP PRIMARY KEY;

  9. 添加唯一

    ALTER TABLE tbl_name
ADD QNIQUE KEY|INDEX [index_name](字段名称)

  10. 删除唯一

    ALTER TABLE tbl_name
DROP index_name;

  11. 修改数据表名称

    • 通过ALTER然后RENAME的格式
    ALTER TABLE tbl_name
RENAME [TO|AS] new_tbl_name;
    • 直接RENAME
    RENAME TABLE tbl_name TO new_tbl_name;

  12. 修改 AUTO_INCREMENT的值

    ALTER TABLE tbl_name AUTO_INCREMENT=值

3. MYSQL中的数据类型

3.1 数值型

  1. 整数型
    • TINYINT：字节1
      • 有符号值： -128 到 127（-27到27-1）
      • 无符号值：0到255（0到2^8-1）
    • SMALLINT：字节2
      • 有符号值：-32768到32767（-215到215-1）
      • 无符号值：0到65535（0到2^16-1）
    • MEDIUMINT：字节3
      • 有符号值：-8388608到8388607（-231到231-1）
      • 无符号值：0到16777215（0到2^32-1）
    • INT：字节4
      • 有符号值：-231到231-1
      • 无符号值：0到2^32-1
    • BIGINT：字节8
      • 有符号值：-263到263-1
      • 无符号值：0到2^64-1
    • BOOL/BOOLEAN：字节1
      • 等价于TINYINT（1）
      • 0为false，其余为true
  2. 浮点型
    • FLOAT[(M,D)]: 字节4
      • M是总位数，D是小数点后的位置
    • DOUBLE[(M,D)]: 字节8
    • DECIMAL[(M,D)]: M+2

3.2 字符串类型

  • 类型综述
列类型 存储需求
CHAR(M) M个字节，0<=M<=255
VARCHAR(M) L+1字节，其中L<=M且0<=M<=65535
L+1个字节，其中L<2^8
TEXT L+2个字节，其中L<2^16
L+3个字节，其中L<2^24
LONGTEXT L+4个字节，其中L<2^32
ENUM('value1','value2') 1或2个字节，取决于枚举值的个数(最多65535个值)
SET('value1','value2') 1、2、3、4或者8个字节，取决于set成员的数目(最多64个)
  • 一些tips
    • CHAR效率高于VARCHAR,CHAR相当于拿空间换时间，VARCHAR拿时间换空间
    • CHAR默认存储数据的时候，后面会用空格填充到指定长度；而在检索的时候会去掉后面空格；VARCHAR在保存的时候不进行填充，尾部的空格会留下
    • TEXT列不能有默认值，检索的时候不存在大小写转换

3.3 日期时间类型

列类型 存储范围 存储需求
-838:59:59 ~ 838:59:59 3
DATE 1000-01-01 ~ 9999-12-31 3
1000-01-01 00:00:00 ~ 9999-12-31 23:59:59
TIMESTAMP 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 4
YEAR 1901-2155

3.4 二进制类型