DDL数据定义语言
(一)概述
DDL(Data Definition Language):数据定义语言,用来定义数据库对象,库、表、列等;创建、删除、修改 库,表结构。主要分为操作数据库的DDL和操作表的DDL。
(二)操作数据库的DDL
这里所提到的数据库不同于我们之前提到的MySQL数据库、Oracle数据库等。MySQL数据库中的”数据库”是指一个数据库管理系统(DBMS),而这里所提到的数据库是指用来组织和管理一些对象(表、视图等)的结构,简单可以理解问磁盘上的文件夹。
一个数据库服务器包含多个库。
一个数据库包含多张表。
一张表包含多条记录。
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、查看数据库
- 查看所有数据库
- 语法
SHOW DATABASES;
- 语法
- 查看指定数据库
- 可以查看数据库创建的时的语法(编码、排序规则等)
- 语法:
SHOW CREATE DATABASE 数据库名 ;
3、修改数据库
- 只能修改数据库的字符集和排序规则,不能修改数据库名字
- 语法
ALTER DATABASE 数据库名 CHARACTER SET 字符集 [COLLATE 排序规则];
- 演示:
4、删除数据库
- 语法:
DROP DATABASE 数据库名;
- 演示:
5、使用数据库
- 查看正在使用的数据库:
SELECT DATABASE();
- 切换数据库:
USE 数据库名;
演示:
(三)操作表的DDL
表是一种数据库对象,表由若干个字段(列)组成。是我们在操作数据库时使用最为频繁的数据库对象。
-
创建表:
- 注意:在创建表之前,一定要先使用数据库(也就是说,表一定要创建在某个数据库中)
- 语法:
CREATE TABLE 表名 (字段名1 数据类型,字段名2 数据类型,…,字段名n 数据类型)
建议写成如下格式:
CREATE TABLE 表名 (
字段名1 数据类型,
字段名2 数据类型,
...,
字段名n 数据类型
); - 示例:
数据类型
(1)数值类型
a、整数类型
类型名称 |
字节 |
说明 |
tinyint |
1 |
无符号:0~255 有符号:-128~127 |
smallint |
2 |
无符号:0~65535 有符号:-32768~32767 |
mediumint |
3 |
无符号:0~1677215 有符号:-8388608~8388607 |
int、integer |
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类型:tinyblob、blob、mediumblob和longblob。它们只是可容纳值的最大长度不同。
有4种TEXT类型:tinytext、text、mediumtext和longtext。这些对应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。
修改时区的SQL语句:set time_zone=’+9:00’(设置为东9区)
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 表名
查看创表语句
语法:
SHOW CREATE TABLE 表名;
示例
复制表结构:
语法:
CREATE TABLE 新表名 LIKE 旧表名;
示例:
-
修改表
- 添加新列:
- 语法:
ALTER TABLE 表名 ADD 列名 类型;
- 示例:为学生表添加一个新字段remake,类型为varchar(20)
- 语法:
- 添加新列:
- 修改列类型:
- 语法:
ALTER TABLE 表名 MODIFY 列名 新的类型
- 示例:将student表中的remark字段的改成varchar(100):
- 语法:
- 修改列类型:
- 修改列名:
- 语法:
ALTER TABLE 表名 CHANGE 旧列名 新列名 类型
- 示例:将s1表中的remark字段名改成intro,类型varchar(30)
- 语法:
- 修改列名:
注意:修改列名的时候,也可以一起修改列类型的取值范围,但是不能修改列类型(varchar-->int 是不可以的)。一般情况是不会这样修改列类型的。
- 删除列:
- 语法:
ALTER TABLE 表名 DROP 列名;
- 示例:删除student表中的字段intro
- 语法:
- 删除列:
- 修改表名:
- 语法:
RENAME TABLE 表名 TO 新表名;
- 示例:将学生表student改名成student2
- 语法:
- 修改表名:
- 修改字符集
- 语法:
ALTER TABLE 表名 CHARACTER SET 字符集;
- 示例:将sutden2表的编码修改成gbk
- 语法:
- 修改字符集
注意:即使修改了表的字符集,但是表中的列类型的字符集并不会被修改
- 删除表
- 语法:
DROP TABLE 表名;
- 示例:删除表student2
- 语法:
- 删除表