DDL数据定义语言

DDL数据定义语言

(一)概述

DDL(Data Definition Language):数据定义语言,用来定义数据库对象,库、表、列等;创建、删除、修改 库,表结构。主要分为操作数据库的DDL和操作表的DDL。

(二)操作数据库的DDL

这里所提到的数据库不同于我们之前提到的MySQL数据库、Oracle数据库等。MySQL数据库中的”数据库”是指一个数据库管理系统(DBMS),而这里所提到的数据库是指用来组织和管理一些对象(表、视图等)的结构,简单可以理解问磁盘上的文件夹。

DDL数据定义语言

一个数据库服务器包含多个库。

一个数据库包含多张表。

一张表包含多条记录。

1、创建数据库

(1)直接创建数据库
  • 在创建数据库时,如果没有使用可选关键字创建,并且数据库已经存在,则会报错;如果使用了 if not exists 关键字创建,则不会报错
  • CREATE DATABASE [IF NOT EXISTS] 数据库名;
(2)指定字符集方式创建数据库
  • 语法:
    CREATE DATABASE  数据库名 CHARACTER SET 字符集;
  • 示例:
    CREATE DATABASE  db1 CHARACTER SET UTF8
(3)指定字符集和排序规则创建数据库
  • 语法:
    CREATE DATABASE 数据库名 CHARACTER SET 字符集 COLLATE 排序规则;
  • 示例:
     CREATE DATABASE db2 CHARACTER SET UTF8 COLLATE utf8_bin;
  • 排序规则说明

    针对不同字符集,MySQL指定了不同的排序规则(可以参考MySQL的帮助文档)。

    例如:utf8字符集中指定的 utf8_general_ci utf8_bin,对数据库中存储数据库的规则就有所不同。

    utf8_general_ci:ci是 case insensitive, 即 "大小写不敏感", a 和 A 会在字符判断中会被当做一样的。

     utf8_bin:将字符串每个字符串用二进制数据编译存储,区分大小写,而且可以存二进制的内容。

2、查看数据库

  1. 查看所有数据库
    • 语法
      SHOW DATABASES;
  2. 查看指定数据库
    • 可以查看数据库创建的时的语法(编码、排序规则等)
    • 语法:
      SHOW CREATE DATABASE 数据库名 ;

3、修改数据库

  • 只能修改数据库的字符集和排序规则,不能修改数据库名字
  • 语法
    ALTER DATABASE 数据库名 CHARACTER SET 字符集 [COLLATE 排序规则];
  • 演示:
    • DDL数据定义语言

      DDL数据定义语言

      DDL数据定义语言

4、删除数据库

  • 语法:
    DROP DATABASE 数据库名;
  • 演示:

    DDL数据定义语言

5、使用数据库

  1. 查看正在使用的数据库:
    SELECT DATABASE();
  2. 切换数据库:
    USE 数据库名;

    演示:

      DDL数据定义语言

(三)操作表的DDL

  • 表是一种数据库对象,表由若干个字段(列)组成。是我们在操作数据库时使用最为频繁的数据库对象。

  • 创建表

    • 注意:在创建表之前,一定要先使用数据库(也就是说,表一定要创建在某个数据库中)
    • 语法:
      CREATE TABLE 表名 (字段名1 数据类型,字段名2 数据类型,…,字段名n 数据类型)
      建议写成如下格式:
      CREATE TABLE 表名 (
      字段名1 数据类型,
      字段名2 数据类型,
      ...,
      字段名n 数据类型
      );
    • 示例:

      DDL数据定义语言DDL数据定义语言

  • 数据类型

  (1)数值类型

    a、整数类型

类型名称

字节

说明

tinyint

1

无符号:0~255          有符号:-128~127

smallint

2

无符号:0~65535        有符号:-32768~32767

mediumint

3

无符号:0~1677215      有符号:-8388608~8388607

intinteger

4

无符号:0~4294967295   有符号:-2147483648~2147483647

bigint

8

无符号:0~18446744073709551615

有符号:-9223372036854775808~9223372036854775807

                        在建表时默认都是有符号的。

                        如要使用无符号的,在建表语句中指定。例如:id int unsigned

    b、近似数类型

      在MySQL中,可以通过浮点数和定点数来表示近似值。

      浮点数

      • MySQL中的浮点数包括float和double两种,定义格式为float(M,D)、double(M,D)。
      • M-表示所存储的值共有M位(M的取值范围是0~255)
      • D-表示小数点后有D位(D的取值范围是0~30)
      • 例如:float(7,4)存储的数据范围时-999.9999~999.9999。

      定点数 :(涉及到计算的最好使用定点数,因为浮点数会有精度的损失)

      • decimal 是MySQL的定点数。相较与浮点数定点数表示的近似值更加精确。定义格式为decimal(M,D)
      • M-表示所存储的值共有M位(M的默认取值是10,取值范围是1~65)
      • D-表示小数点后有D位(D的默认取值是0,取值范围是0~30)

  (2)字符串类型

    MySQL提供了多种针对字符串的存储类型。如下表所示

字符串类型

取值范围和存储需求

char(M)

M为0~255之间的整数

varchar(M)

M为0~65535之间的整数

tinyblob

允许长度0~255字节

blob

允许长度0~65535字节

meduimblob

允许长度0~167772150字节

longblob

允许长度0~4294967295字节

tinytext

允许长度0~255字节

text

允许长度0~65535字节

mediumtext

允许长度0~167772150字节

longtext

允许长度0~4294967295字节

varbinary(M)

允许长度0~M个字节的变长字节字符串

binary(M)

允许长度0~M个字节的定长字节字符串

enum

允许的值选自一个允许值列表,这个列表最多能存储的值数量为65535个

set

和enum类似,set类型是一个多选字符串数据类型

a、char和varchar

char和varchar两种类型相似,都是MySQL数据库中用来存储较短字符串格式的类型。但这两种类型的主要区别在于存储方式不同,char类型的长度是固定的,而varchar类型的长度是可变的。例如,存储字符串”abc”,char(5)表示存储的字符将占5个字节(包括2个空字符),而varchar(5)只占用3个字节的长度,5只是最大值,当存储的字符小于5个时,按实际长度存储。同时在检索时,char类型会删除尾部的空格,而varchar类型会保留尾部的空格。因为长度的问题,所以导致两种数据类型的数据寻址速度不同,如果字符在10个以内,推荐使用char。(但是在MySql3.10中测试,占的是字符的个数,而不是字节数,如:char(5)存储的字母个数和汉字个数是一样的,都是5个)

 

b、BLOB和TEXT

BLOB是一个二进制大对象,可以容纳可变数量的数据。有4种BLOB类型:tinyblobblobmediumbloblongblob。它们只是可容纳值的最大长度不同。

有4种TEXT类型:tinytext、text、mediumtextlongtext。这些对应4种BLOB类型,有相同的最大长度和存储需求。

因为大文件中的字符可能存在非法,所以推荐使用BLOB,比如ascii字符集下存储一张含有”0XFF”字节的图片,入库过滤。

      

  (3)日期和时间类型

    MySQL中有多种日期和时间类型的数据数据类型。如下表所示

类型

字节

最小值

最大值

date

4

1000-01-01

9999-12-31

datetime

8

1000-01-01 00:00:00

9999-12-31 23:59:59

timestamp

4

1970-01-01 00:00:01.000000

2038-01-19 03:14:07.999999'

time

3

-838:59:59

838:59:59

year

1

1901

2155

对于这些数据类型的选择,有如下使用准则:

1、如果要表示年月日,通常使用date。

2、如果要表示年月日时分秒,通常使用datetime。

3、如果要表示时分秒,通常使用time。

4、如果要表示年,通常使用year。

5、如果要表示当前系统的时间,通常使用timestamp。

  timestamp和datetime

虽然timestamp类型和datetime类型一样可以表示日期时间,但两者存在差别。

1、timestamp类型存储的时间与MySQL数据库系统安装时所选的时区有关,在不同时区下查看的同一批数据将会得出不同的结果。

  查看时区的SQL语句: show variables like ‘time_zone’; 默认与主机一致,显示结果为SYSTEM。

  DDL数据定义语言

  修改时区的SQL语句:set time_zone=’+9:00’(设置为东9区)

  DDL数据定义语言

  1、两者的取值范围不同,datetime的取值范围是1000-01-01 00:00:00到9999-12-31 23:59:59,timestamp的取值范围是1970-01-01 00:00:01.000000到2038-01-19 03:14:07.999999'。

 最常用类型总结:整数 int 、 浮点数 double 、字符串 varchar、日期 date。

  创建表示例

需求:创建employee表,包含字段,编号(id  int)、姓名(emp_name varchar)、性别(gender enum(‘M’,’F’))、入职日期(hire_date date)、薪资(salary double)、简历(resume text)
create table employee(
id int,
emp_name varchar(100),
gender enum(‘M’,’F’),
hire_date date,
salary double,
resume text
)
  • 查看表

    查看所有表:

SHOW TABLES;

    查看表结构:

DESC 表名:

DESCRIBE 表名

DDL数据定义语言

  查看创表语句

  语法:

SHOW CREATE TABLE 表名;

  示例

DDL数据定义语言

  •  复制表结构:

    语法:

CREATE TABLE 新表名 LIKE 旧表名;

    示例:

    DDL数据定义语言

  • 修改表
    • 添加新列:
      • 语法:
        ALTER TABLE 表名 ADD 列名 类型;
      • 示例:为学生表添加一个新字段remake,类型为varchar(20)

        DDL数据定义语言

    • 修改列类型:
      • 语法:
        ALTER TABLE 表名 MODIFY 列名 新的类型
      • 示例:将student表中的remark字段的改成varchar(100):

         DDL数据定义语言

    • 修改列名:
      • 语法:
        ALTER TABLE 表名 CHANGE 旧列名 新列名 类型
      • 示例:将s1表中的remark字段名改成intro,类型varchar(30)

        DDL数据定义语言

        注意:修改列名的时候,也可以一起修改列类型的取值范围,但是不能修改列类型(varchar-->int 是不可以的)。一般情况是不会这样修改列类型的。

    • 删除列:
      • 语法:
        ALTER TABLE 表名 DROP 列名;
      • 示例:删除student表中的字段intro

        DDL数据定义语言

    • 修改表名:
      • 语法:
        RENAME TABLE 表名 TO 新表名;
      • 示例:将学生表student改名成student2

        DDL数据定义语言

    • 修改字符集
      • 语法:
        ALTER TABLE 表名 CHARACTER SET 字符集;
      • 示例:将sutden2表的编码修改成gbk

        DDL数据定义语言

        注意:即使修改了表的字符集,但是表中的列类型的字符集并不会被修改

    • 删除表
      • 语法:
        DROP TABLE 表名;
      • 示例:删除表student2

        DDL数据定义语言

上一篇:mysql操作命令梳理(4)-grant授权和revoke回收权限


下一篇:360手机卫士会影响Widget的运行