MySQL学习之DDL操作

目录

数据库的操作

创建

查看

选择

删除

修改

数据类型

表的创建

表的修改

表的约束

主键 PRIMARY KEY

唯一性约束 UNIQUE

非空约束 NOT NULL

外键约束

约束小结

索引

索引分类

常规索引

主键索引

唯一索引

外键索引

优点

缺点

视图

创建

删除

修改

查看

优点

范式

第一范式

第二范式

第三范式

表与表的关系

1:1

1:N

N:N

---

数据库的操作

创建

create database 数据库名 charset utf8;

查看

show databases;
show create databases db;
select database();

选择

use 数据库名;

删除

drop database 数据库名;

修改

alter databases db1 charset utf8;

数据类型

表的创建

首先要对操作的数据有一个基础型的了解,

-- 学号 姓名 性别 出生日期 入学时间 专业 院系 创建时间 -- 学号 int

姓名 varchar

性别 char

出生日期 date

入学时间 date

专业 varchar

院系 varchar

创建时间 timestamp

create table t_student(sno int,sname varchar(40),gender char(1),birthday date,schooltime date,major varchar(255),department varchar(255),createtime timestamp
);

表的修改

-- 根据查询语句创建表
CREATE TABLE STU01 AS SELECT * FROM t_student;
-- 添加两列（一次alter table操作只能添加一列）
alter table t_student add loc timestamp default now();
alter table t_student add phone timestamp default now();
-- 删除一列
alter table t_student drop column phone;
-- 修改一列
alter table t_student modify major varchar(20);
-- 修改列名
alter table t_student change COLUMN  birthday birth DATE;
'''
如果数据类型不同，您需要相应地修改。
假设要将birthday列（原本是DATE类型）修改为VARCHAR(255)类型的birth列，修改的语句如下：
ALTER TABLE t_student CHANGE birthday birth VARCHAR(255);
在 MySQL 中，修改列名通常使用 CHANGE 关键字，而不是 RENAME 。
但需要注意的是，在某些数据库管理工具（如 Navicat 等）中，可能支持使用 RENAME COLUMN 来修改列名，但这并非是 MySQL 原生语法所支持的。
为了保证在纯 MySQL 环境中的兼容性和可移植性，建议使用 CHANGE 来修改列名。
'''
-- 修改表名
rename table t_student to t_s;
-- 删除一张表
drop table t_s;

表的约束

约束指的是我们创建的表,对数据的约束,而不是对创建人的约束

主键 PRIMARY KEY

• 主键值必须唯一标识表中的每一行且不能为空NULL，即表中不可能存在有相同主键值的两行数据

• 主键分为单字段主键和多字段联合主键

• 联合主键不能包含不必要的多余字段。当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的。这是最小化原则。

  create table t_pk01(id int(11) primary key,name varchar(25),deptid int(11),salary float
  );
  ​
  create table t_pk02(tid int(11),cid int(11),salary float
  );
  ​
  -- ALTER TABLE 数据表名 ADD PRIMARY KEY(字段名);
  alter table t_pk02 add primary key(id);
  ​
  -- ALTER TABLE 数据表名 DROP PRIMARY KEY;
  alter table t_pk02 drop primary key;

唯一性约束 UNIQUE

列中的值可以为空但是不能相同

create table tb_unique01(id int(11) primary key,name varchar(22),phoneum varchar(11) unique,location varchar(50)
);
​
-- ALTER TABLE 数据表名 ADD CONSTRAINT 唯一约束名 UNIQUE(列名);
alter table tb_unique01 add
constraint t_unique01_unique_location
unique(location);
​
-- ALTER TABLE 表名 DROP INDEX 唯一约束名;
alter table tb_unique01 drop
index t_unique01_unique_location;

非空约束 NOT NULL

列中的值不能为null

CREATE TABLE tb_null01(
id INT(11) PRIMARY KEY,
name VARCHAR(22) NOT NULL,
location VARCHAR(50)
);
​
-- ALTER TABLE 数据表名 CHANGE COLUMN 字段名 字段名 数据类型 NOT NULL;
-- 将 tb_null01 表中的 location 列的数据类型修改为 VARCHAR(50) ，并设置为不允许为空值。
ALTER TABLE tb_null01 
CHANGE COLUMN location location VARCHAR(50) NOT NULL;
​
-- ALTER TABLE 数据表名 CHANGE COLUMN 字段名 字段名 数据类型 NULL;
-- 再次修改 location 列，将其数据类型仍保持为 VARCHAR(50) ，但设置为允许为空值。
ALTER TABLE tb_null01 
CHANGE COLUMN location location VARCHAR(50) NULL;

外键约束

定义外键时，需要遵守下列规则：

• 主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。

• 必须为主表定义主键。

• 主键不能包含空值，但允许在外键中出现空值。即只要外键的每个非空值出现在指定的主表中，这个外键的内容就是正确的。

• 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或唯一性键。

• 外键中列的数目必须和主表的主键中列的数目相同。

• 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

-- 1位老师对应N个学生 teacher表为主表，student表为外键表 tid为外键
create table t_teacher(tid int(10) PRIMARY KEY,tname VARCHAR(40)
);
CREATE table t_student(sid int(10) PRIMARY KEY,sname varchar(40),tid int(10),CONSTRAINT fk_teacher_student_tid FOREIGN KEY(tid) references t_teacher(tid) on delete cascade  
);
​
INSERT into t_teacher VALUES('001','唐老师');
INSERT into t_teacher VALUES("002",'杜老师');
INSERT into t_teacher VALUES('003','唐老师');
INSERT into t_student VALUES('111','图图','001');
INSERT into t_student VALUES('112','莉莉','002');
INSERT into t_student VALUES('113','小欧','003');
INSERT into t_student VALUES('114','麦吉','003');
INSERT into t_student VALUES('115','丁倩','003');
-- 错误数据，因为主表主键没有d
insert into student values('5','xiaohu','d');
​
-- 修改表
ALTER TABLE 数据表名 ADD CONSTRAINT 外键名 FOREIGN KEY(列名) REFERENCES 主表名(列名);

索引

在MySQL中，索引（index）也叫做“键（key）”，它是存储引擎用于快速找到记录的一种数据结构。

索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响就愈发重要。

索引优化应该是对查询性能优化最有效的手段。

-- CREATE INDEX indexName ON mytable(username); 
Create Index index_dept_dname on dept(dname); -- B-tree
Drop Index index_dept_dname on dept;

索引分类

常规索引

常规索引，也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

主键索引

主键索引（Primary Key），也简称主键。它可以提高查询效率，并提供唯一性约束。一张表中只能有一个主键。被标志为自动增长的字段一定是主键，但主键不一定是自动增长。一般把主键定义在无意义的字段上（如：编号），主键的数据类型最好是数值。

唯一索引

唯一索引（Unique Key），可以提高查询效率，并提供唯一性约束。一张表中可以有多个唯一索引。

外键索引

外键索引（Foreign Key），简称外键，它可以提高查询效率，外键会自动和对应的其他表的主键关联。外键的主要作用是保证记录的一致性和完整性。

索引是数据库中一块独立的空间，专门存储索引值的一棵B-树,我们可以通过B-树快速的定位到要查找的数据

特别注意:尽量不要对重复数据的列添加索引,比如：性别

优点

• 索引可以让MySQL快速地查找到我们所需要的数据，但这并不是索引的唯一作用。

• 索引大大减少了MySQL服务器需要扫描的数据量。

• 索引可以帮助服务器避免排序和临时表。

• 索引可以将随机I/O变为顺序I/O。

缺点

• 影响数据库的增删改速度

视图

MySQL 视图（View）是一种虚拟存在的表，同真实表一样，视图也由列和行构成，但视图并不实际存在于数据库中。

行和列的数据来自于定义视图的查询中所使用的表，并且还是在使用视图是动态生成的。

创建

-- CREATE VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
CREATE VIEW v_swordsman AS
( SELECT tid, NAME FROM t_teacher ) UNION ALL
( SELECT sid, NAME FROM t_student );

删除

DROP VIEW IF EXISTS <视图名1> [ , <视图名2> …]

修改

ALTER VIEW <视图名> AS <SELECT语句>
-- <视图名>：指定视图的名称。该名称在数据库中必须是唯一的，不能与其他表或视图同名。
-- <SELECT 语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。

查看

-- 查询视图的数据 
SELECT * 
FROM
v_swordsman
​
-- DESCRIBE 视图名；查看视图的列结构信息
DESCRIBE v_swordsman
​
-- SHOW CREATE VIEW 视图名;获取创建视图的具体定义语句
SHOW CREATE VIEW v_swordsman

优点

1) 定制用户数据，聚焦特定的数据

2) 简化数据操作

3) 提高数据的安全性

4) 共享所需数据

5) 更改数据格式

6) 重用 SQL 语句

范式

创建表需要遵循的规范

第一范式

【不可再分割原则】数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组

第二范式

【数据库中每一行数据必须依赖于主键】每一个行数据都要有主键，主键是一行数据的唯一性标识

第三范式

【非主键的列必须要全部依赖于主键】表中非主键的列要完全依赖于主键，不能出现部分属性依赖于其他属性，当出现传递依赖的时候要将非依赖于主键的列专门创建一张表进行管理

表与表的关系

为了维护两张表的关系，根据两张表的对应关系可以分为：

1:1

两张表中的数据 1条只和1条对应

关联方案:

让关联数据主键值相同

在一张表中存放另外一张表的主键

1:N

A表中的一条数据有可能对应B表中的多条记录。比如 1老师：N学生

关联方案

在N方的表中设计一个1方的主键列，也称之为外键关联

N:N

A表中的一条数据有可能对应B表中的多条记录，同时B表中的一条数据有可能对应A表中的多条记录

比如 1学生：N课程，1课程：N学生

关联方案

s学生表 c课程表

一般都会创建第三章表专门管理学生与课程的关系

sid,cid 作为联合主键管理信息

id ,sid ,cid 以ID作为这张表的单独主键