MySQL老杜课程学习笔记（入门级）(三)

最新推荐文章于 2024-11-12 11:41:10 发布

什么都不懂的研究生

最新推荐文章于 2024-11-12 11:41:10 发布

阅读量97

点赞数

文章标签： mysql 学习笔记

本文链接：https://blog.csdn.net/xiao_bai_cai_o/article/details/132650087

版权

MySQL老杜课程学习笔记（入门级）(三)

1、表的创建

表的创建属于DDL语言，本部分只是为了后边讲解DML语言方便，因此先介绍如何创建表，再介绍具体的DML语言

1.1 创建表

create table 表名(
	字段名1 数据类型,
	字段名2 数据类型,
	字段名3 数据类型,
	....
);

create table 是关键字，字段必须全用括号括起来。表名在数据库当中一般建议以：t_或者tbl_开始，如t_student

MySQL当中字段的常见数据类型：

int 整数型
bigint 长整型
float 浮点型
char 定长字符串
varchar 可变长字符串
date 日期类型
BLOB 二进制大对象（存储图片、视频等流媒体信息） Binary Large OBject
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。） Character Large OBject

char和varchar的选择：

在实际的开发中，当某个字段中的数据长度不发生改变的时候，是定长的，例如：性别、生日等都是采用char。当一个字段的数据长度不确定，例如：简介、姓名等都是采用varchar。这种选择不仅仅取决于是否定长，有时候也要从占用空间的角度考虑。

案例：创建学生表

create table t_student(
	no bigint ,
	name varchar(255),
	sex char(1) default 'm',   //性别只有男女 'm'男 'w'女，且默认值为'm'
	classno varchar(255),
	birth char(10) //出生年月是定长的
);

1.2 表的复制

create table 表名 as select语句;

将查询结果当做表创建出来，实现表的复制。

2、insert into 插入记录（DML）

2.1 语法格式：

insert into 表名(字段名1,字段名2,字段名3,....) values(值1,值2,值3,....)
//要求：字段的数量和值的数量相同，并且数据类型要对应相同。

insert into t_student(no,name,sex,classno,birth) values(1,'zhangsan','1','gaosan1ban', '1950-10-12');

insert into t_student(name,sex,classno,birth,no) values('lisi','1','gaosan1ban', '1950-10-12',2);
//改变字段顺序，也可以完成记录的插入

insert into t_student(name) values('wangwu');
// 除name字段之外，剩下的所有字段自动插入NULL,这是因为字段默认值为NULL。但每个字段可以在创建表时使用default设置默认值，则插入时自动用默认值填充。

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');
//字段名可以省略不写，但是后面的value对数量和顺序都有要求

2.2 一次插入多行记录：

insert into t_student
	(no,name,sex,classno,birth) 
values
	(3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');

2.3 将查询结果插入表中

insert into dept1 select * from dept;

3 update…set… 修改记录（DML）

语法格式：

update 表名 set 字段名1=值1,字段名2=值2... where 条件;

案例：将部门号为10的LOC修改为SHANGHAI，将部门名称修改为RENSHIBU

update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;

当没有where条件限制时，所有记录都将更新：

update dept1 set loc = 'x', dname = 'y';//所有记录的loc改为x，dname改为y

4 delete 删除数据（DML）

语法格式：

delete from 表名 where 条件;

delete from dept1 where deptno = 10;//删除10部门的所有记录

注意：当没有条件限制时，则将会删除所有记录，慎用！！！

delete from dept1;

truncate是快速删除表中所有数据的方法，不记录事务日志，无法回滚，而delete是逐行删除数据的方法，可以记录事务日志，可以回滚。删除数据量庞大的表，delete执行效率很低，且delete删除数据并不会释放空间，因此删除大量数据，且不需要回滚事务时，用truncate

truncate table 表名; //删除表中所有数据,表的定义仍然存在，表所占用空间被释放

drop table 表名;//直接删除表的定义

5 数据定义语言（DDL）

对于表结构的修改，也就是DDL语言，本文不作介绍。

在实际开发中表一旦设计好之后，对表结构的修改是很少的，修改表结构就是对之前的设计进行了否定，会增加代码维护成本。即使需要修改表结构，我们也可以直接使用工具操作。

在此给出一个术语：CRUD操作（增删改查）

Create（增） Retrieve（检索） Update（修改） Delete（删除）

6 约束(Constraint)

6.1 约束介绍

在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的
合法性、有效性、完整性。

常见的约束：

非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
外键约束(foreign key)：强引用（简称FK）
检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。

6.2 非空约束（ not null）

被约束的字段，不能出现NULL，因此在插入记录时，如果没有指定非空默认值，则必须给该字段赋值

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255) not null,
	password varchar(255)
);
insert into t_user(id,password) values(1,'123');
//出错，ERROR 1364 (HY000): Field 'username' doesn't have a default valu

insert into t_user(id,username,password) values(1,'lisi','123');

6.3 唯一性约束（unique）

唯一约束修饰的字段具有唯一性，不能重复，但可以为NULL。

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255) unique //【列级约束】
);
insert into t_user values(1,'zhangsan');
insert into t_user values(2,'zhangsan');
//报错 ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'username'

案例：多个字段联合的唯一性约束，联合约束是将多个字段看做成一个字段进行唯一性约束，而不是对单独每个字段进行约束。

drop table if exists t_user;
create table t_user(
	id int, 
	usercode varchar(255),
	username varchar(255),
	unique(usercode,username) // 多个字段联合起来添加1个约束unique 【表级约束】
);
 
insert into t_user values(1,'111','zs');
insert into t_user values(2,'111','ls');
insert into t_user values(3,'222','zs');
//并未报错，可已顺利插入记录，因为并没有违反唯一性约束
insert into t_user values(4,'111','zs');
//报错，违反唯一性约束。ERROR 1062 (23000): Duplicate entry '111-zs' for key 'usercode'

表级约束可以涉及一个或多个列，而列级约束只针对特定的列，也就说只要约束了两个及以上的列或者约束不是写在某一字段后边，而是声明在所有字段后边，则称为表级约束。另外，not null约束只有列级约束。没有表级约束。

6.4 主键约束（primary key）

主键约束是为了给每个记录有一个唯一的身份标识，所以主键约束的字段必须满足不为空，不重复。一张表的主键约束只能有1个。

drop table if exists t_user;
create table t_user(
	id int primary key, // 列级约束
    username varchar(255),
	email varchar(255)
);
insert into t_user(id,username,email) values(1,'zs','zs@123.com');
insert into t_user(id,username,email) values(1,'jack','jack@123.com');
//主键需要满足唯一性原则。ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

insert into t_user(username,email) values('jack','jack@123.com');
//主键不能为空。ERROR 1364 (HY000): Field 'id' doesn't have a default value

主键相关的术语：

主键约束 : primary key
主键字段 : id字段添加primary key之后，id叫做主键字段
主键值 : id字段中的每一个值都是主键值

主键的分类：

根据主键字段的字段数量来划分：

单一主键（推荐的，常用的。）
复合主键(多个字段联合起来添加一个主键约束)（复合主键不建议使用，因为复合主键违背三范式。）

根据主键性质来划分：

自然主键：主键值最好就是一个和业务没有任何关系的自然数。（这种方式是推荐的）
业务主键：主键值和系统的业务挂钩，例如：拿着银行卡的卡号做主键，拿着身份证号码作为主键。（不推荐用）

最好不要拿着和业务挂钩的字段作为主键。因为以后的业务一旦发生改变的时候，主键值可能也需要随着发生变化，但有的时候没有办法变化，因为变化可能会导致主键值重复。

复合主键，开发中很少用，不需要掌握，仅需了解：

drop table if exists t_user;
create table t_user(
	id int,
	username varchar(255),
	password varchar(255),
	primary key(id,username)
);

mysql提供主键值自增：

drop table if exists t_user;
create table t_user(
	id int primary key auto_increment, // id字段自动维护一个自增的数字，从1开始，以1递增。
	username varchar(255)
);
insert into t_user(username) values('a');
insert into t_user(username) values('b');
insert into t_user(username) values('c');
insert into t_user(username) values('d')
//最终自动生成主键id

提示:Oracle当中也提供了一个自增机制，叫做：序列（sequence）对象。

6.5 外键约束（foreign key…reference）

外键约束的主要作用就是减少数据冗余，节省存储空间，外键约束会引用另一张表的数据，确保数据输入的正确性，限制数据输入范围。

外键约束的相关术语：

外键约束: foreign key
外键字段：添加有外键约束的字段
外键值：外键字段中的每一个值。

由于外键约束在创建表时引用另外一张表，因此存在子表和父表，被外键约束引用的表称为父表。

注意：

想要删除父表数据的时候，先删除子表，再删除父表。

想要添加父表没有的数据的时候，先添加父表，再添加子表。

（注意：此处所指的添加和删除都指开发人员的确有新需求，需要维护子表和父表，或者更确切的说是开发人员主动取“违反”外键约束原则以达到不违反约束原则目的）

创建表的时候，先创建父表，再创建子表。

删除表的时候，先删除子表，在删除父表。

drop table if exists t_student;
drop table if exists t_class;
//先创建父表
create table t_class(
	cno int,
	cname varchar(255),
	primary key(cno)
);
//创建子表
create table t_student(
	sno int,
	sname varchar(255),
	classno int,
	primary key(sno),
	foreign key(classno) references t_class(cno)
);

insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs4',102);
//101 和 102 均未违反外键约束
insert into t_student values(3,'lisi',103);
//103违反外键约束。EROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))