3.DDL数据定义语句

最新推荐文章于 2024-08-23 09:39:07 发布

what's your name.

最新推荐文章于 2024-08-23 09:39:07 发布

阅读量682

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_45874347/article/details/121376866

版权

MySQL 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

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，外键关联用户表的主键

what's your name.

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
打赏
0
评论
3.DDL数据定义语句

DDL数据定义语句DDL：Data Define Language 数据定义语言，用于对数据库和表的管理和操作一.库的管理1.1 查看所有数据库#查看所有数据库名称SHOW DATABASES;　1.2 切换数据库#切换到mydb1数据库USE mydb1;1.3 创建数据库#创建数据库，创建一个名为mydb1的数据库。如果这个数据库已经存在，那么会报错。CREATE DATABASE mydb1;#在名为mydb1的数据库不存在时创建该库，这样可以避免报错。CREATE D
复制链接

扫一扫