DDL数据定义语句
DDL:Data Define Language 数据定义语言,用于对数据库和表的管理和操作
一.库的管理
1.1 查看所有数据库
#查看所有数据库名称
SHOW DATABASES;
1.2 切换数据库
#切换到mydb1数据库
USE mydb1;
1.3 创建数据库
#创建数据库,创建一个名为mydb1的数据库。如果这个数据库已经存在,那么会报错。
CREATE DATABASE mydb1;
#在名为mydb1的数据库不存在时创建该库,这样可以避免报错。
CREATE DATABASE IF NOT EXISTS mydb1;
1.4 删除数据库
#删除数据库,删除名为mydb1的数据库。如果这个数据库不存在,那么会报错。
DROP DATABASE mydb1;
#就算mydb1不存在,也不会的报错。
DROP DATABASE IF EXISTS mydb1;
二.数据类型
MySQL与Java一样,也有数据类型。MySQL中数据类型主要应用在列上。
2.1 数值型
int:整型
double/float:浮点数类型,例如double(5,2)表示最多5位,其中必须有2位小数,即最大值为999.99;
decimal:定点数类型,在表示钱方面使用该类型,因为不会出现精度缺失问题;
案例:
#数值型 int double/float decimal
drop table if EXISTS testdb;
create table if not EXISTS testdb(
id int,
salary double(5,3),
d1 float(5,3),
d2 decimal(5,3)
);
insert into testdb(id,salary,d1,d2) values(111,123.456,123.456,123.456); -- 报错
insert into testdb(id,salary,d1,d2) values(222,12.45,12.45,12.45);
insert into testdb(id,salary,d1,d2) values(333,12.4567,12.4567,123.4567);
说明:
①.浮点数和定点数都可以用类型名称后加“(M,D)”的方式来表示。
M:精度 ,该值的整数位+小数位一共显示M位数字
D:标度, 小数位数一共显示D位数字,如果不够后面用0补齐,如果超过,则四舍五入
2.浮点数和定点数的区别:
a) 定点数在MySQL内部以字符串形式存放,比浮点数更精确,适合用于表示货币等精度高的数据;
b) 在不指定精度时,浮点数默认会按照实际的精度来显示,而定点数在不指定精度时,默认M=10,D=0;
2.2 字符型
char:固定长度字符串类型;char(n) n范围是0-255之间的整数
varchar:可变长度字符串类型;varchar(n) n范围是0~65535之间的整数
text:字符串类型;表示存储较长文本
意思 | 格式 | n的解释 | 特点 | 效率 | |
---|---|---|---|---|---|
char | 固定长度字符 | char(n) | 最大的字符个数,可选填,默认:1 | 不管实际存储,开辟的空间都是n个字符 | 高 |
varchar | 可变长度字符 | varchar(n) | 最大的字符个数,必填 | 根据实际存储决定开辟的空间 | 低 |
案例:
#字符型 char varchar
drop table if EXISTS testdb;
create table if not EXISTS testdb(
c1 char(9),
c2 varchar(9)
);
insert into testdb values(' abc ',' abc ');
select * from testdb;
-- char类型会自动去除末尾空格,varchar类型不会去除末尾空格
select concat(c1,'###'),concat(c2,'###') from testdb;
说明:
①存储的列长度
char:列的长度固为创建表时声明的长度
varchar:列的长度为可变,只要不超过声明的长度n
②检索的时候
char:删除了尾部的空格
varchar:保留这些空格
2.3 时间类型
date:日期类型,格式为:yyyy-MM-dd;
time:时间类型,格式为:hh:mm:ss
timestamp/datetime:时间戳类型;日期+时间 此种类型yyyyMMddhhmmss也可
保存范围 | 所占字节 | |
---|---|---|
Datetime | 1900-1-1~xxxx年 | 8 |
Timestamp | 1970-1-1~2038-12-31 | 4 |
案例:
#时间类型 date time timestamp datetime
drop table if EXISTS testdb;
create table if not EXISTS testdb(
d1 date,
d2 time,
d3 timestamp, -- 时间戳
d4 datetime
);
insert into testdb(d1) values('2019-12-12');
insert into testdb(d2) values('15:15:15');
insert into testdb(d3) values('2019-12-12 15:15:15');
insert into testdb(d4) values('2019-12-12 17:15:15');
insert into testdb(d3) values('20191212161616');
insert into testdb(d4) values('20191212161616');
select * from testdb;
2.4 二进制型
Blob:字节类型;可以用来存储图片数据jpg、 音乐mp3 、 视频avi。 在MySQL中 Blob是一个二进制大型对象,可以是存储大量数据的容器,他能容纳不同大小的数据,插入Blob类型的数据必须使用PrepareStatement 因为Blob类型的数据无法使用字符串拼接。
类型 | 大小(字节) |
---|---|
TinyBlob | 255 |
Blob | 65K |
MediumBlob | 16M |
LongBlob | 4G |
三.表的管理
3.1 创建表
语法:
CREATE TABLE [IF NOT EXISTS] 表名(
字段名 字段类型 [字段约束],
字段名 字段类型 [字段约束],
......
字段名 字段类型 [字段约束]
);
案例:
CREATE TABLE stus(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
CREATE TABLE emps(
eid CHAR(6),
ename VARCHAR(50),
age INT,
gender VARCHAR(6),
birthday DATE,
hiredate DATE,
salary DECIMAL(7,2),
resume VARCHAR(1000)
);
3.2 查看表
-- 查看当前数据库中所有表名称
SHOW TABLES;
-- 查看指定表的创建语句
SHOW CREATE TABLE emps;
-- 查看表结构
DESC emps;
3.3 删除表
-- 删除表
DROP TABLE emps;
3.4 修改表结构
-- 修改之 添加列ADD:给stus表添加classname列:
ALTER TABLE stus ADD (classname varchar(100));
alter table stus add COLUMN borndate datetime not null;
-- 修改之 修改列类型MODIFY:修改stus表的gender列类型为CHAR(2):
ALTER TABLE stus MODIFY gender CHAR(2);
-- 修改之 修改列名change:修改stus表的gender列名为sex:
ALTER TABLE stus change gender sex CHAR(2);
-- 修改之 删除列DROP:删除stus表的classname列:
ALTER TABLE stus DROP classname;
-- 修改之 修改表名称RENAME:修改stus表名称为student:
ALTER TABLE stus RENAME TO student;
3.5 复制表
-- 仅仅复制表的结构
CREATE TABLE 新表名 LIKE 表;
-- 复制表的结构+数据
CREATE TABLE 新表名 SELECT * FROM 表;
-- 案例:复制employees表中的last_name,department_id,salary字段到新表emp表,但不复制数据
四.完整性约束
完整性约束是为了表的数据的正确性!如果数据不正确,那么一开始就不能添加到表中。
用于限制表中字段的数据的,从而进一步保证数据表的数据是一致的、准确的、可靠的!
-- 非空约束:not null
用于限制该字段为必填项
-- 默认约束:default
严格来说,并不算约束。用于限制该字段没有显示插入值时,则直接使用默认值。
-- 主键约束:primary key
用于限制该字段值不能重复,设置为主键的字段默认不能为空。
一个表只能有一个主键,也可以是组合主键。
-- 唯一约束:unique
用于限制该字段值不能重复。
主键和唯一键的区别:其中主键字段不能为空,一个表中只能有一个主键;唯一键字段可以为空,一个表中可以有多个唯一键。
-- 检查约束:check
用于限制该字段值必须满足指定条件。
check (age between 1 and 100),检查此时操作的年龄必须在1~100之间,但是MySQL没有检查约束,在mysql中加上不报错,但是没有效果。
-- 外键约束:foreign key
用于限制两个表的关系,要求外键的字段值必须来自主表的关联列。
要求
1.主表的关联列和从表的关联列的数据类型一致,含义一样,命名无要求
2.主表的关联列必须为主键
4.1 主键
当某一列添加了主键约束后,那么这一列的数据就不能重复出现。这样每行记录中其主键列的值就是这一行的唯一标识。例如学生的学号可以用来做唯一标识,而学生的姓名是不能做唯一标识的,因为学习有可能同名。
主键列的值不能为NULL,也不能重复!
指定主键约束使用PRIMARY KEY关键字
4.1.1 创建表:定义列时指定主键
CREATE TABLE stu(
sid CHAR(6) PRIMARY KEY,
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
4.1.2 创建表:定义列之后独立指定主键
CREATE TABLE stu(
sid CHAR(6),
sname VARCHAR(20),
age INT,
gender VARCHAR(10),
PRIMARY KEY(sid)
);
4.1.3 修改表时指定主键
ALTER TABLE stu ADD PRIMARY KEY(sid);
4.1.4 删除主键(只是删除主键约束,而不会删除主键列)
ALTER TABLE stu DROP PRIMARY KEY;
4.2 主键自增长
MySQL提供了主键自动增长的功能!这样用户就不用再为是否有主键是否重复而烦恼了。当主键设置为自动增长后,在没有给出主键值时,主键的值会自动生成,而且是最大主键值+1,也就不会出现重复主键的可能了。
4.2.1 创建表时设置主键自增长(主键必须是整型才可以自增长)
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
age INT,
gender VARCHAR(10)
);
4.2.2 修改表时设置主键自增长
ALTER TABLE stu CHANGE sid sid INT AUTO_INCREMENT;
4.2.3 修改表时删除主键自增长
ALTER TABLE stu CHANGE sid sid INT;
4.3 非空
指定非空约束的列不能没有值,也就是说在插入记录时,对添加了非空约束的列一定要给值;在修改记录时,不能把非空列的值设置为NULL。
指定非空约束:
CREATE TABLE stu(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10) NOT NULL,
age INT,
gender VARCHAR(10)
);
当为sname字段指定为非空后,在向stu表中插入记录时,必须给sname字段指定值,否则会报错:
INSERT INTO stu(sid) VALUES(1);
插入的记录中sname没有指定值,所以会报错!
4.4 唯一
还可以为字段指定唯一约束!当为字段指定唯一约束后,那么字段的值必须是唯一的。这一点与主键相似!
例如给stu表的sname字段指定唯一约束:
CREATE TABLE tab_ab(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(10) UNIQUE
);
INSERT INTO sname(sid, sname) VALUES(1001, ‘zs’);
INSERT INTO sname(sid, sname) VALUES(1002, ‘zs’);
当两次插入相同的名字时,MySQL会报错!
4.5 外键
主外键是构成表与表关联的唯一途径!
外键是另一张表的主键!例如员工表与部门表之间就存在关联关系,其中员工表中的部门编号字段就是外键,是相对部门表的外键。
创建dept表,指定deptno为主键列:
CREATE TABLE dept(
deptno INT PRIMARY KEY,
dname varchar(14),
loc varchar(13)
);
创建t_section表,指定sid为主键列,u_id为相对t_user表的uid列的外键:
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm decimal(7,2),
deptno INT,
CONSTRAINT fk_e_d FOREIGN KEY(deptno) REFERENCES dept(deptno)
)
4.6 表与表之间的关系
**一对一:**例如t_person表和t_card表,即人和身份证。这种情况需要找出主从关系,即谁是主表,谁是从表。人可以没有身份证,但身份证必须要有人才行,所以人是主表,而身份证是从表。
设计从表可以有两种方案:
-
在t_card表中添加外键列(相对t_user表),并且给外键添加唯一约束;
-
给t_card表的主键添加外键约束(相对t_user表),即t_card表的主键也是外键。
**一对多(多对一):**最为常见的就是一对多!一对多和多对一,这是从哪个角度去看得出来的。t_user和t_section的关系,从t_user来看就是一对多,而从t_section的角度来看就是多对一!这种情况都是在多方创建外键!
**多对多:**例如t_stu和t_teacher表,即一个学生可以有多个老师,而一个老师也可以有多个学生。这种情况通常需要创建中间表来处理多对多关系。例如再创建一张表t_stu_tea表,给出两个外键,一个相对t_stu表的外键,另一个相对t_teacher表的外键。
三范式:
第一范式:表中的字段不可再分割
例如:家庭住址(国家省市区县) --》 可在分割成:国家 | 省 | 市 | 区 | 县
第二范式:当表与表之间有多对多的关系时,会建议创建关系表存储
角色 可以拥有多个 权限 ; 每个权限 可以属于多个角色; 角色与权限表是多对多的关系。
设计:t_role 角色表 t_permission 权限表 role_permission 角色和权限关系表 (遵循的原则:角色表和权限表中设计外键,关联关系表中的联合主键)
role_permission 中,设计role_id 和 permission_id字段,可以将其设计成联合主键。
第三范式:当表与表之间有一对多的关系时,会建议主表中设置外键关联从表的主键
用户 可以拥有多个订单 ; 用户与订单 :一对多关系
设计:t_user 用户表 t_order 订单表 ; 将订单表中设置一个外键字段user_id,外键关联用户表的主键