数据库基础

阅读目录

• 什么是数据库技术
  • 一、数据库基本概念
  • 二、数据库分类
  • 三、基本操作

 

回到顶部

什么是数据库技术

数据库技术所研究的问题就是如何科学地组织和存储数据，如何高效地获取和处理数据。

一、数据库基本概念

（1）数据
所谓数据（Data）是指对客观事物进行描述并可以鉴别的符号，这些符号是可识别的、抽象的。它不仅仅指狭义上的数字，而是有多种表现形式：字母、文字、文本、图形、音频、视频等。现在计算机存储和处理的数据范围十分广泛，而描述这些数据的符号也变得越来越复杂了。

（2）数据库
数据库（Database，DB）指的是以一定格式存放、能够实现多个用户共享、与应用程序彼此独立的数据集合。

（3）数据库管理系统
数据库管理系统（Database Management System，DBMS）是用来定义和管理数据的软件。如何科学的组织和存储数据，如何高效的获取和维护数据，如何保证数据的安全性和完整性，这些都需要靠数据库管理系统完成。目前，比较流行的数据库管理系统有：Oracle、MySQL、SQL Server、DB2等。

（4）数据库应用程序
数据库应用程序（Database Application System，DBAS）是在数据库管理系统基础上，使用数据库管理系统的语法，开发的直接面对最终用户的应用程序，如学生管理系统、人事管理系统、图书管理系统等。

（5）数据库管理员
数据库管理员（Database Administrator，DBA）是指对数据库管理系统进行操作的人员，其主要负责数据库的运营和维护。

（6）最终用户
最终用户（User）指的是数据库应用程序的使用者。用户面向的是数据库应用程序（通过应用程序操作数据），并不会直接与数据库打交道。

（7） 数据库系统
数据库系统（Database System，DBS）一般是由数据库、数据库管理系统、数据库应用程序、数据库管理员和最终用户构成。其中DBMS是数据库系统的基础和核心。

二、数据库分类

数据库经过几十年的发展，出现了多种类型。根据数据的组织结构不同，主要分为网状数据库、层次数据库、关系型数据库和非关系型数据库四种。目前最常见的数据库模型主要是：关系型数据库和非关系型数据库。

https://baike.baidu.com/item/网状数据库/463319?fr=aladdin

http://wjhsh.net/vvlj-p-12750871.html

1. 关系型数据库
   关系型数据库模型是将复杂的数据结构用较为简单的二元关系（二维表）来表示，如图1-4所示。在该类型数据库中，对数据的操作基本上都建立在一个或多个表格上，我们可以采用结构化查询语言（SQL）对数据库进行操作。关系型数据库是目前主流的数据库技术，其中具有代表性的数据库管理系统有：Oracle、DB2、SQL Server、MySQL等。

2. 非关系型数据库NOSQL
   NOSQL（Not Only SQL）泛指非关系型数据库。关系型数据库在超大规模和高并发的web2.0纯动态网站已经显得力不从心，暴露了很多难以克服的问题。NOSQL数据库的产生就是为了解决大规模数据集合多重数据种类带来的挑战，尤其是大数据应用难题。常见的非关系型数据库管理系统有Memcached、MongoDB，redis等。

常见的关系型数据库

1. Oracle
   Oracle数据库是由美国的甲骨文（Oracle）公司开发的世界上第一款支持SQL语言的关系型数据库。经过多年的完善与发展，Oracle数据库已经成为世界上最流行的数据库，也是甲骨文公司的核心产品。
   Oracle数据库具有很好的开放性，能在所有的主流平台上运行，并且性能高、安全性高、风险低；但是其对硬件的要求很高、管理维护和操作比较复杂而且价格昂贵，所以一般用在满足对银行、金融、保险等行业大型数据库的需求上。

2. DB2
   DB2是IBM公司著名的关系型数据库产品。DB2无论稳定性，安全性，恢复性等等都无可挑剔，而且从小规模到大规模的应用都可以使用，但是用起来非常繁琐，比较适合大型的分布式应用系统。

3. SQL Server
   SQL Server是由Microsoft开发和推广的关系型数据库，SQL Server的功能比较全面、效率高，可以作为中型企业或单位的数据库平台。SQL Server可以与Windows操作系统紧密继承，无论是应用程序开发速度还是系统事务处理运行速度，都能得到大幅度提升。但是，SQL Server只能在Windows系统下运行，毫无开放性可言。

4. MySQL
   MySQL是一种开放源代码的轻量级关系型数据库，MySQL数据库使用最常用的结构化查询语言（SQL）对数据库进行管理。由于MySQL是开放源代码的，因此任何人都可以在General Public License的许可下下载并根据个人需要对其缺陷进行修改。
   由于MySQL数据库体积小、速度快、成本低、开放源码等优点，现已被广泛应用于互联网上的中小型网站中，并且大型网站也开始使用MySQL数据库，如网易、新浪等。

5. PostgreSQL

PostgreSQL，也称为 Postgres，是一种开源关系数据库，因其可靠性、灵活性和对开放技术标准的支持而享有盛誉。PostgreSQL 支持非关系和关系数据类型。它被称为当今可用的最兼容、最稳定和最成熟的关系数据库之一，并且可以轻松处理复杂的查询。

PostgreSQL 的特性包括：

• 时间点恢复 (PITR) 将数据库还原到特定时间点。
• 使用 pgBackRest 等工具记录对数据库的所有更改的预写日志 (WAL)。
• 用于创建和保留自定义子例程的存储过程。

三、基本操作

登录的命令

mysql -hlocalhost -uroot –p

mysql：bin目录下的文件mysql.exe。mysql是MySQL的命令行工具，是一个客户端软件，可以对任何主机的mysql服务（即后台运行的mysqld）发起连接。

-h：host主机名。后面跟要访问的数据库服务器的地址；如果是登录本机，可以省略

-u：user 用户名。后面跟登录数据的用户名，第一次安装后以root用户来登录，是MySQL的管理员用户

-p: password 密码。一般不直接输入，而是回车后以保密方式输入。

访问数据库

显示MySQL中的数据库列表：show databases; 默认有四个自带的数据库，每个数据库中可以有多个数据库表、视图等对象。

切换当前数据库的命令：use mysql;
MySQL下可以有多个数据库，如果要访问哪个数据库，需要将其置为当前数据库。

该命令的作用就是将数据库mysql（默认提供的四个数据库之一的名字）置为当前数据库

显示当前数据库的所有数据库表：show tables;

退出数据库

退出数据库可以使用quit或者exit命令完成，也可以用\q; 完成退出操作

客服端连接

mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password,

解决问题方法有两种：

方法1.升级navicat驱动；

方法2.把mysql用户登录密码加密规则还原成mysql_native_password.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY ***

SQL语句

我们都知道，数据库管理人员（DBA）通过数据库管理系统（DBMS）可以对数据库（DB）中的数据进行操作，但具体是如何操作的呢？这就涉及到我们本节要讲的SQL语言。

SQL（Structured Query Language）是结构化查询语言的简称，它是一种数据库查询和程序设计语言，同时也是目前使用最广泛的关系型数据库操作语言。在数据库管理系统中，使用SQL语言来实现数据的存取、查询、更新等功能。SQL是一种非过程化语言，只需提出“做什么”，而不需要指明“怎么做”。

SQL是由IBM公司在1974~1979年之间根据E.J.Codd发表的关系数据库理论为基础开发的，其前身是“SEQUEL”，后更名为SQL。由于SQL语言具有集数据查询、数据操纵、数据定义和数据控制功能于一体，类似自然语言、简单易用以及非过程化等特点，得到了快速的发展，并于1986年10月，被美国国家标准协会（American National Standards Institute，ANSI）采用为关系数据库管理系统的标准语言，后为国际标准化组织（International Organization for Standardization，ISO）采纳为国际标准。

https://blog.csdn.net/qq_52797170/article/details/123488849

https://www.e-learn.cn/content/mysql/2337044

SQL语言的五个部分

• 　　数据查询语言（Data Query Language，DQL）：DQL主要用于数据的查询，其基本结构是：

　　　　使用SELECT子句，FROM子句和WHERE子句的组合来查询一条或多条数据。

• 　　数据操作语言（Data Manipulation Language，DML）：DML主要用于对数据库中的数据进行增加、修改和删除的操作，其主要包括：

　　　　INSERT：增加数据
　　　　UPDATE：修改数据
　　　　DELETE：删除数据

• 　　数据定义语言（Data Definition Language，DDL）：DDL主要用针对是数据库对象（表、索引、视图、触发器、存储过程、函数、表空间等）进行创建、修改和删除操作。其 主要包括：

　　　　CREATE：创建数据库对象

　　　　ALTER：修改数据库对象

　　　　DROP：删除数据库对象

• 　　数据控制语言（Data Control Language，DCL）：DCL用来授予或回收访问数据库的权限，其主要包括：

　　　　GRANT：授予用户某种权限

　　　　REVOKE：回收授予的某种权限

• 　　事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。 其主要包括：

　　　　START TRANSACTION：开启事务

　　　　COMMIT：提交事务

　　　　ROLLBACK：回滚事务

　　　　SET TRANSACTION：设置事务的属性

　　注意： DML和DDL的不同

　　数据操作语言(DML)（insert、update、delete）针对表中的数据。

　　而数据定义语言(DDl)（create、alter、drop）针对数据库对象，比如数据库database、表table、索引index、视图view、存储过程procedure、触发器trigger。

1.创建数据库表

##这是一个单行注释
##要创建一个名称为mytestdb数据库，可以执行CREATE DATABASE语句后接数据库名称：mytestdb，如果当前MySQL服务器中没有数据库：mytestdb，则创建成功，如下所示：

CREATE DATABASE IF NOT EXISTS mytestdb;
/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student(
	sno int(6), -- 6显示长度 
	sname varchar(5), -- 5个字符
	sex char(1),
	age int(3),
	enterdate date,
	classname varchar(10),
	email varchar(15)
);

-- 查看表的结构：展示表的字段详细信息
desc t_student;

-- 查看表中数据：
select * from t_student;

-- 查看建表语句：
show create table t_student;
/*
CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdate` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/

字段类型

1、整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度，例如，INT(4)。显示宽度并不限制可以在列内保存的值的范围，也不限制超过列的指定宽度的值的显示，即如果实际有7位，就会显示7位。

主键自增:不使用序列，通过auto_increment，要求是整数类型。

2、浮点数类型

需要注意的是与整数类型不一样的是，浮点数类型的宽度不会自动扩充。

score double(4,1)--小数部分为1位，总宽度4位，并且不会自动扩充。超过这个范围存不下。

3、字符串类型

BLOB类型用于存储多媒体数据，比如音频、视频、图像等

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

CHAR和VARCHAR表示的是字符的个数，而不是字节的个数。

select substr("hello world",1) -- mysql中的substr的下标从1开始，获取从1号字符及之后的所有字符组成的字符串

select length("hello world") -- mysql中支持length

4、日期时间类型

对日期数据进行格式化：

select DATE_FORMAT(now(),'%Y-%m-%d-%H-%i-%S');
SELECT DATE_FORMAT('2011-09-20:08:30:45', '%Y-%m-%d %H:%i:%S');
SELECT DATE_FORMAT('2011-09-20 08:30:45', '%Y-%m-%d %H:%i:%s');

获取后一天的日期数据：

获取前一天的日期数据：

select DATE_SUB(curdate(),INTERVAL 1 DAY); -- 昨日日期
select DATE_SUB(curdate(),INTERVAL -1 DAY); -- 明日日期
-- curdate()是当前的日期，不包含时间，now()和sysdate()既包含日期和时间。

TIMESTEMP类型的数据指定方式与DATETIME基本相同，两者的不同之处在于以下几点:

(1)数据的取值范围不同，TIMESTEMP类型的取值范围更小。
(2)如果我们对TIMESTAMP类型的字段没有明确赋值，或是被赋与了NULL值，MySQL会自动将该字段赋值为系统当前的日期与时间。
(3)TIMESTEMP类型还可以使用CURRENT_TIMESTAMP来获取系统当前时间。
(4)TIMESTEMP类型有一个很大的特点，那就是时间是根据时区来显示的。例如，在东八区插入的TIMESTEMP数据为2017-07-11 16:43:25，东七区显示时，时间部分就变成了15:43:25，在东九区显示时，时间部分就变成了17:43:25。

2.增加，修改，删除数据

-- 在t_student数据表中插入数据:  采用的是全字段插入方式，即表中每个字段都有对应的数据
insert into t_student values (1,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (10010010,'张三','男',18,'2022-5-8','软件1班','123@126.com');
insert into t_student values (2,'张三','男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (2,"张三",'男',18,'2022.5.8','软件1班','123@126.com');
insert into t_student values (7,"张三",'男',18,now(),'软件1班','123@126.com');
insert into t_student values (9,"易烊千玺",'男',18,now(),'软件1班','123@126.com');
insert into t_student (sno,sname,enterdate) values (10,'李四','2023-7-5');

-- 修改表中数据
update t_student set sex = '女' ;
update t_student set sex = '男' where sno = 10 ;
UPDATE T_STUDENT SET AGE = 21 WHERE SNO = 10;
update t_student set CLASSNAME = 'java01' where sno = 10 ;
update t_student set CLASSNAME = 'JAVA01' where sno = 9 ;
update t_student set age = 29 where classname = 'java01';
-- 删除操作：
delete from t_student where sno = 2;

3.修改，删除数据库中的表

-- 查看数据：
select * from t_student;

-- 修改表的结构：
-- 增加一列：
alter table t_student add score double(5,2) ; -- 5:总位数  2：小数位数 
update t_student set score = 123.5678 where sno = 1 ;

-- 增加一列（放在最前面）
alter table t_student add score double(5,2) first;

-- 增加一列（放在sex列的后面）
alter table t_student add score double(5,2) after sex;

-- 删除一列：
alter table t_student drop score;

-- 修改一列：
alter table t_student modify score float(4,1); -- modify修改是列的类型的定义，但是不会改变列的名字
alter table t_student change score score1 double(5,1); -- change修改列名和列的类型的定义

-- 删除表：
drop table t_student;

为防止不符合规范的数据存入数据库,在用户对数据进行插入、修改、删除等操作时，MySQL提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是完整性约束。

MySQL中主要支持以下几种完整性约束，如表所示。其中Check约束是MySQL8中提供的支持。

4.表的完整性约束-非外键约束

/*
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息

约束：
建立一张用来存储学生信息的表
字段包含学号、姓名、性别，年龄、入学日期、班级，email等信息
【1】学号是主键 = 不能为空 +  唯一 ，主键的作用：可以通过主键查到唯一的一条记录【2】如果主键是整数类型，那么需要自增
【3】姓名不能为空
【4】Email唯一
【5】性别默认值是男
【6】性别只能是男女
【7】年龄只能在18-50之间
*/
-- 创建数据库表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);

-- 添加数据：
--  1048 - Column 'sname' cannot be null 不能为null
-- 3819 - Check constraint 't_student_chk_1' is violated. 违反检查约束
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- 1062 - Duplicate entry '1' for key 't_student.PRIMARY' 主键重复
-- > 1062 - Duplicate entry 'ls@126.com' for key 't_student.email' 违反唯一约束
insert into t_student values (2,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (3,'露露','男',21,'2023-9-1','java01班','ls@127.com');
-- 如果主键没有设定值，或者用null.default都可以完成主键自增的效果
insert into t_student (sname,enterdate) values ('菲菲','2029-4-5');
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','xm@126.com');
insert into t_student values (default,'小刚','男',21,'2023-9-1','java01班','xg@126.com');
-- 如果sql报错，可能主键就浪费了，后续插入的主键是不连号的，我们主键也不要求连号的
insert into t_student values (null,'小明','男',21,'2023-9-1','java01班','oo@126.com');
-- 查看数据：
select * from t_student;




-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6) auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15),
				constraint pk_stu primary key (sno),  -- pk_stu 主键约束的名字
				constraint ck_stu_sex check (sex = '男' || sex = '女'),
				constraint ck_stu_age check (age >= 18 and age <= 50),
				constraint uq_stu_email unique (email)
);

-- 添加数据：
insert into t_student values (1,'张三','男',21,'2023-9-1','java01班','zs@126.com');
-- > 3819 - Check constraint 'ck_stu_sex' is violated.
-- > 3819 - Check constraint 'ck_stu_age' is violated.
-- > 1062 - Duplicate entry 'zs@126.com' for key 't_student.uq_stu_email'
insert into t_student values (3,'李四','男',21,'2023-9-1','java01班','zs@126.com');

-- 查看数据：
select * from t_student;


-- 删除表：
drop table t_student;
-- 创建数据库表：
create table t_student(
        sno int(6), 
        sname varchar(5) not null, 
        sex char(1) default '男',
        age int(3),
        enterdate date,
        classname varchar(10),
        email varchar(15)
);

-- > 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key
-- 错误的解决办法：就是auto_increment去掉


-- 在创建表以后添加约束：
alter table t_student add constraint pk_stu primary key (sno) ; -- 主键约束
alter table t_student modify sno int(6) auto_increment; -- 修改自增条件
alter table t_student add constraint ck_stu_sex check (sex = '男' || sex = '女');
alter table t_student add constraint ck_stu_age check (age >= 18 and age <= 50);
alter table t_student add constraint uq_stu_email unique (email);

-- 查看表结构：
desc t_student;

5.表的完整性约束-外键约束

-- 先创建父表：班级表：
create table t_class(
	cno int(4) primary key auto_increment,
	cname varchar(10) not null,
	room char(4)
);

-- 添加班级数据：
insert into t_class values (null,'java001','r803');
insert into t_class values (null,'java002','r416');
insert into t_class values (null,'大数据001','r103');

-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');

-- 查询班级表：
select * from t_class;
-- 学生表删除：
drop table t_student;
-- 创建子表,学生表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)  -- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
);

-- 添加学生信息：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);

-- 查看学生表：
select * from t_student;



-- 出现问题：
-- 1.添加一个学生对应的班级编码为4：
insert into t_student values (null,'丽丽',4);
-- 2.删除班级2：
delete from t_class where cno = 2;
-- 出现问题的原因：
-- 因为你现在的外键约束，没用语法添加进去，现在只是逻辑上认为班级编号是外键，没有从语法上定义


-- 解决办法，添加外键约束：
-- 注意：外键约束只有表级约束，没有列级约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
				constraint fk_stu_classno foreign key (classno) references t_class (cno)
);

create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4)
);
-- 在创建表以后添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)


-- 上面的两个问题都解决了：
-- 添加学生信息：
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2);

-- 删除班级1：
-- 2.删除班级2：
insert into t_student values (null,'张三',3),(null,'李四',3),(null,'王五',3);
-- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classno` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
delete from t_class where cno = 3;

6.外键策略

-- 学生表删除：
drop table t_student;
-- 班级表删除：
drop table t_class;

-- 注意：先删除主表，再删除从表。

-- 先创建父表：班级表：
create table t_class(
	cno int(4) primary key auto_increment,
	cname varchar(10) not null,
	room char(4)
);
-- 可以一次性添加多条记录：
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');

-- 添加学生表，添加外键约束：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
				constraint fk_stu_classno foreign key (classno) references t_class (cno)
);
-- 可以一次性添加多条记录：
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',3);

-- 查看班级表和学生表：
select * from t_class;
select * from t_student;



-- 删除班级2：如果直接删除的话肯定不行因为有外键约束：
-- 加入外键策略：
-- 策略1：no action 不允许操作
-- 通过操作sql来完成：
-- 先把班级2的学生对应的班级 改为null 
update t_student set classno = null where classno = 2;
-- 然后再删除班级2：
delete from t_class where cno = 2;

-- 策略2：cascade 级联操作：操作主表的时候影响从表的外键信息：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;
-- 试试更新：
update t_class set cno = 5 where cno = 3;
-- 试试删除：
delete from t_class where cno = 5;

-- 策略3：set null  置空操作：
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加外键约束：
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;

-- 试试更新：
update t_class set cno = 8 where cno = 1;

-- 注意：
-- 1. 策略2 级联操作  和  策略2 的  删除操作  可以混着使用：

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null ;

表的完整性约束

　　为了防止不符合规范的数据存入数据库，在用户对数据进行插入、修改、删除等操作时，MySQL数据库管理系统提供了一种机制来检查数据库中的数据是否满足规定的条件，以保证数据库中数据的准确性和一致性，这种机制就是约束。

约束从作用上可以分为两类：

　　(1) 表级约束：可以约束表中任意一个或多个字段。

　　(2) 列级约束：只能约束其所在的某一个字段。

（1）、主键约束

　　主键约束（PRIMARY KEY，缩写PK），是数据库中最重要的一种约束，其作用是约束表中的某个字段可以唯一标识一条记录。因此，使用主键约束可以快速查找表中的记录。就像人的身份证、学生的学号等等，设置为主键的字段取值不能重复（唯一），也不能为空（非空），否则无法唯一标识一条记录。

　　主键可以是单个字段，也可以是多个字段的组合。对于单字段主键的添加可以使用表级约束，也可以使用列级约束；而对于多字段主键的添加只能使用表级约束。

/*创建表student11*/  create   table student11 (        stu_id int(10) primary key,        stu_name varchar(3),        stu_sex varchar (1) );

（2）、非空约束

　　非空约束（NOT NULL，缩写NK）规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）。

-- 为已存在表中的字段添加非空约束 alter table student8 modify stu_sex varchar(1) not null;  -- 使用ALTER TABLE语句删除非空约束  alter table student8 modify stu_sex varchar(1) null;

（3）、 唯一约束

　　唯一约束（UNIQUE，缩写UK）比较简单，它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。无论是单个字段还是多个字段唯一约束的添加均可以使用列级约束和表级约束。

（4）、 默认值约束

　　默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。默认值约束的设置与非空约束一样，也只能使用列级约束。

（5）、字段值自动增加约束

　　自增约束（AUTO_INCREMENT）可以使表中某个字段的值自动增加。一张表中只能有一个自增长字段，并且该字段必须定义了约束（该约束可以是主键约束、唯一约束以及外键约束），如果自增字段没有定义约束，数据库则会提示“Incorrect table definition; there can be only one auto column and it must be defined as a key”错误。

　　由于自增约束会自动生成唯一的ID，所以自增约束通常会配合主键使用，并且只适用于整数类型。一般情况下，设置为自增约束字段的值会从1开始，每增加一条记录，该字段的值加1。

7.DDL和DML的一些其它操作

-- 创建表：
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        sex char(1) default '男' check(sex='男' || sex='女'),
        age int(3) check(age>=18 and age<=50),
        enterdate date,
        classname varchar(10),
        email varchar(15) unique
);
-- 添加数据：
insert into t_student values (null,'张三','男',21,'2023-9-1','java01班','zs@126.com');
insert into t_student values (null,'李四','男',21,'2023-9-1','java01班','ls@126.com');
insert into t_student values (null,'露露','男',21,'2023-9-1','java01班','ll@126.com');

-- 查看学生表：
select * from t_student;

-- 添加一张表：快速添加：结构和数据跟t_student 都是一致的
create table t_student2
as
select * from t_student;

select * from t_student2;


-- 快速添加，结构跟t_student一致，数据没有：
create table t_student3
as
select * from t_student where 1=2;

select * from t_student3;

-- 快速添加：只要部分列，部分数据：
create table t_student4
as
select sno,sname,age from t_student where sno = 2;

select * from t_student4;

-- 删除数据操作 :清空数据
delete from t_student;
truncate table t_student;
 -- 删除所有数据时，更推荐使用该方式，效率高

DQL语言基本查询

#创建部门和员工表
#下面的SQL就是创建了四张表：-- 单行注释
-- 下面的SQL就是创建了四张表：-- 单行注释
-- ctrl+shift+R 运行快捷键  

/*
  多行注释
*/
create table DEPT
(
  DEPTNO int(2) not null,
  DNAME  VARCHAR(14),
  LOC    VARCHAR(13)
);
alter table DEPT
  add constraint PK_DEPT primary key (DEPTNO);
create table EMP
(
  EMPNO    int(4) primary key,
  ENAME    VARCHAR(10),
  JOB      VARCHAR(9),
  MGR      int(4),
  HIREDATE DATE,
  SAL      double(7,2),
  COMM     double(7,2),
  DEPTNO   int(2)
);
alter table EMP
  add constraint FK_DEPTNO foreign key (DEPTNO)
  references DEPT (DEPTNO);
create table SALGRADE
(
  GRADE int primary key,
  LOSAL double(7,2),
  HISAL double(7,2)
);
create table BONUS
(
  ENAME VARCHAR(10),
  JOB   VARCHAR(9),
  SAL   double(7,2),
  COMM  double(7,2)
);
commit;
insert into DEPT (DEPTNO, DNAME, LOC)
values (10, 'ACCOUNTING', 'NEW YORK');
insert into DEPT (DEPTNO, DNAME, LOC)
values (20, 'RESEARCH', 'DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC)
values (30, 'SALES', 'CHICAGO');
insert into DEPT (DEPTNO, DNAME, LOC)
values (40, 'OPERATIONS', 'BOSTON');
commit;
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7839, 'KING', 'PRESIDENT', null, '1981-11-17', 5000, null, 10);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, null, 30);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, null, 20);
insert into EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
values (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, null, 10);
commit;
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (1, 700, 1200);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (2, 1201, 1400);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (3, 1401, 2000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (4, 2001, 3000);
insert into SALGRADE (GRADE, LOSAL, HISAL)
values (5, 3001, 9999);
commit;

基本查询

-- 对员工表进行操作：
-- 简单的查询：
-- 查看员工表所有数据：
select * from emp;-- *代表查询所有字段
#运行方式1：选中+右键-- 运行异选中
#运行方式2：ctrl+shift+r
-- 查看员工表的员工名字，员工编号，工资。（部分字段）
select ename,empno,sal from emp;
-- 不区分大小写：
SELECT ENAME,EMPNO,SAL FROM EMP;-- 对于关键字，字段名，表名 这些不区分大小写。
-- 使用算术表达式：
select ename,empno,sal,sal*12+50000 from emp;
-- 使用别名：
select ename 员工姓名,empno 员工编号,sal 月工资,sal*12+50000 年薪 from emp;
select ename '员工姓名',empno '员工编号',sal '月工资',sal*12+50000 '年薪' from emp;-- 单引号可以
select ename "员工姓名",empno "员工编号",sal "月工资",sal*12+50000 "年薪" from emp;-- 双引号可以
select ename as 员工姓名,empno as 员工编号,sal as 月工资,sal*12+50000  as 年薪 from emp;-- 中间加上as可以
select ename as '员工 姓名',empno as '员工+编号',sal as '（月工资）',sal*12+50000  as '年薪' from emp;
-- 如果你的别名中有特殊符号，那么单引号或者双引号必须加上，但是你的别名中要是没有特殊符号，那么单双引号都可以省略不写。
-- 去重 distinct
-- 查看有哪些职位：
select distinct job from emp;
-- 查看有几个职位：
select count(distinct job) 职位个数 from emp;
select distinct job,sal from emp;-- 组合起来是不重复的

-- 查看员工的名字，工资：按照工资排序
select ename,sal from emp;
select ename,sal from emp order by sal;-- 默认升序
select ename,sal from emp order by sal asc;-- 升序 asc
select ename,sal from emp order by sal desc;-- 降序 desc
select * from emp order by sal asc,hiredate desc;-- 先按照sal升序排列，在sal相同的情况下，按照hiredate降序排列

where条件查询

select * from emp;
#where简单查询：
select * from emp where sal>2500;
select * from emp where sal=2500;
select * from emp where sal<2500;
select * from emp where sal!=2450;
select * from emp where sal<>2450;
select ename,sal ,deptno,job,hiredate from emp where hiredate<'1982-01-23';  -- <代表的是早于82年的数据
select * from emp where job='MANAGER';
select * from emp where job="MANAGER";
select * from emp where job='manager';-- mysql中在精准查询的时候，发现是忽略大小写查询的
select * from emp where  BINARY job='MANAGER';-- 想要精准的查大小写的话，要在前面加上关键词：binary
-- AND
select * from emp where sal>1000 and sal<3000;
select * from emp where sal>=1000 and sal<=3000;
select * from emp where sal>=1000 && sal<=3000;
select * from emp where sal between 1000 and 3000;-- 等效sal>=1000 and sal<=3000;
select * from emp where sal not between 1000 and 3000;
-- OR
select * from emp where deptno=30 and deptno=10;-- 查询不到数据
select * from emp where deptno=30 or deptno=10;
select * from emp where deptno=30 || deptno=10;
select * from emp where deptno in (30,10);
select * from emp where deptno not in (30,10);
-- 模糊查询：
-- 查询名字中带字母S的数据：
select * from emp where ename='S';-- 精准查询，查询名字为S的
select * from emp where ename='%S%';-- 模糊查询   %0-n个字符  中间用=号不行
select * from emp where ename  like '%o%';-- 查询忽略大小写
select * from emp where binary ename like '%O%';-- 关注大小写，前面加上binary
-- 查询名字中第二个字母是O的：
select * from emp where binary ename like '_O%';--  _代表一个字符
-- 查询名字中第三个字母是O的：
select * from emp where binary ename like '__O%';
-- sql执行顺序
select * from emp where job='salesman' or job='manager' and deptno=10;
select * from emp where (job='salesman' or job='manager') and deptno=10;
select * from emp where job='salesman' or (job='manager' and deptno=10);-- 等价这个
-- 结论：and的运算的优先级别高于or，但是不要去记忆，我们写sql直接用()来控制优先顺序即可

函数

（1）、字符串函数 （String StringBuilder）

函数	描述
CONCAT(str1, str2, ···, strn)	将str1、str2···strn拼接成一个新的字符串
INSERT(str, index, n, newstr)	将字符串str从第index位置开始的n个字符替换成字符串newstr
LENGTH(str)	获取字符串str的长度
LOWER(str)	将字符串str中的每个字符转换为小写
UPPER(str)	将字符串str中的每个字符转换为大写
LEFT(str, n)	获取字符串str最左边的n个字符
RIGHT(str, n)	获取字符串str最右边的n个字符
LPAD(str, n, pad)	使用字符串pad在str的最左边进行填充，直到长度为n个字符为止
RPAD(str, n, pad)	使用字符串pad在str的最右边进行填充，直到长度为n个字符为止
LTRIM(str)	去除字符串str左侧的空格
RTRIM(str)	去除字符串str右侧的空格
TRIM(str)	去除字符串str左右两侧的空格
REPLACE(str, oldstr, newstr)	用字符串newstr替换字符串str中所有的子字符串oldstr
REVERSE(str)	将字符串str中的字符逆序
STRCMP(str1, str2)	比较字符串str1和str2的大小
SUBSTRING(str, index, n)	获取从字符串str的index位置开始的n个字符

（2）、数值函数 （Math）

函数	描述
ABS(num)	返回num的绝对值
CEIL(num)	返回大于num的最小整数（向上取整）
FLOOR(num)	返回小于num的最大整数（向下取整）
MOD(num1, num2)	返回num1/num2的余数（取模）
PI()	返回圆周率的值
POW(num, n)/POWER(num, n)	返回num的n次方
RAND(num)	返回0~1之间的随机数
ROUND(num, n)	返回x四舍五入后的值，该值保留到小数点后n位
TRUNCATE(num, n)	返回num被舍去至小数点后n位的值

（3）、日期与时间函数1. 数值函数 （Date、DateFormat、Calendar）

函数	描述
CURDATE()	返回当前日期
CURTIME()	返回当前时间
NOW()	返回当前日期和时间
SYSDATE()	返回该函数执行时的日期和时间
DAYOFYEAR(date)	返回日期date为一年中的第几天
WEEK(date)/WEEKOFYEAR(date)	返回日期date为一年中的第几周
DATE_FORMAT(date, format)	返回按字符串format格式化后的日期date
DATE_ADD(date, INTERVAL expr unit)/ADDDATE(date, INTERVAL expr unit)	返回date加上一个时间间隔后的新时间值
DATE_SUB(date, INTERVAL expr unit)/SUBDATE(date, INTERVAL expr unit)	返回date减去一个时间间隔后的新时间值
DATEDIFF(date1, date2)	返回起始日期date1与结束日期date2之间的间隔天数

（4）、其他函数

函数	描述
DATABASE()	返回当前数据库名
VERSION()	返回当前MySQL的版本号
USER()	返回当前登录的用户名
INET_ATON(IP)	返回IP地址的数字表示
INET_NTOA	返回数字代表的IP地址
PASSWORD(str)	实现对字符串str的加密操作
FORMAT(num, n)	实现对数字num的格式化操作，保留n位小数
CONVERT(data, type)	实现将数据data转换成type类型的操作

-- dual伪表：
select 1+1 计算结果 from dual;
-- 字符函数：
select LENGTH('abc') from dual;
select LOWER('ABC') from dual;
select upper('abc') from dual;
select REPLACE('abcaig','a','o') from dual;
select substring('abcdef',4,2) from dual;
-- 数值函数：
select abs(-4) from dual;
select ceil(9.1) from dual;
select floor(10.9) from dual;
select mod(10,3) from dual;
select pow(3,2) from dual;
select rand() 

-- 流程函数：
SELECT if(1 > 10, 'a', 'b')
FROM dual;
SELECT *
FROM emp;
SELECT if(sal > 800, '这个人工资大于800', '这个人工资小于等于800')
FROM emp;
SELECT ename,
    job,
    sal,
    comm,
    sal + comm
FROM emp;
SELECT ename,
    job,
    sal,
    comm,
    sal + ifnull(comm, 0)
FROM emp;
SELECT NULLIF(13, 139)
FROM dual;
SELECT ename,
CASE
        job
        WHEN 'SALESMAN' THEN '销售'
        WHEN 'CLERK' THEN '职员'
        WHEN 'MANAGER' THEN '经理'
        ELSE '其他职位'
    END '岗位'
FROM emp;
SELECT ename,
    job,
    sal,
CASE
        WHEN sal >= 500
        AND sal <= 800 THEN '工资最低的人'
        WHEN sal > 800
        AND sal <= 2750 THEN '工资稍微高一点点'
        ELSE '工资比较高'
    END  '工资等级'
FROM emp;

-- 多行函数：sum, avg, count, max, min
-- 用一句话将所有多行函数表示：
SELECT  MAX(sal)
       ,MIN(sal)
       ,SUM(sal)
       ,AVG(sal)
       ,SUM(sal)/COUNT(*)
       ,SUM(sal)/COUNT(1)
       ,COUNT(*)
       ,COUNT(1)
FROM emp;

SELECT  MAX(ename)
       ,MIN(ename)
       ,SUM(ename)
       ,AVG(ename)
       ,COUNT(ename)
FROM emp; -- max, min, count可以修饰所有类型 -- sum, avg 仅限于修饰 数值类型 -- count:计数： -- 统计有多少个员工：

SELECT  count(ename)
FROM emp;

SELECT  count(job)
FROM emp;

SELECT  count(comm)
FROM emp;-- 计数 null值不会被计数 -- 最好不要用普通字段来统计全表的数据的个数，因为不知道哪个字段中有null值

SELECT  *
FROM emp;

SELECT  count(*)
FROM emp;

SELECT  1
FROM emp;

SELECT  count(1)
FROM emp;-- 效率高 直接计算1的个数

SELECT  *
FROM emp; -- 写sql判断是否正确：

SELECT  avg(sal)
FROM emp;-- 对

SELECT  avg(sal), ename
FROM emp;-- 不对

SELECT  avg(sal), lower(ename)
FROM emp;-- 不对 -- 结论：多行函数不能跟普通字段和单行函数一起使用 -- 计算部门个数：

SELECT  deptno
FROM emp;

SELECT  count(deptno)
FROM emp;

SELECT  count(distinct deptno)
FROM emp;

单行函数:每一条记录输入值进行计算，并得到相应的计算结果，并返回给用户，即每条记录作为一个输入参数，经过函数计算得到每 条记录的计算结果。 常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

select ename,job,lower(ename),upper(ename),lower(job) from emp;-- 大小写转换

https://blog.csdn.net/qq_46921806/article/details/119950585

https://blog.csdn.net/weixin_45269353/article/details/121641850

多行函数：指对一组数据进行运算，针对这一组数据（多行记录）只返回一个结果，也称为分组函数。
COUNT() 统计表中记录的数目
SUM() 计算指定字段值的总和
AVG() 计算指定字段值的平均值
MAX() 统计指定字段值的最大值
MIN() 统计指定字段值的最小值

select max(sal),min(sal),count(sal),sum(sal),avg(sal) from emp;

group by 和 having

-- group by 分组
select deptno,avg(sal) from emp group by deptno; -- 先分 按deptno 分组 然后进行 取平均值
​
 -- 先按 deptno 分组 然后取平均值 然后按照deptno 进行排序升序
select deptno,avg(sal) from emp group by deptno order by deptno asc; 
-- 先按 deptno 分组 然后取平均值 然后按照deptno 进行排序降序
select deptno,avg(sal) from emp group by deptno order by deptno desc;    
     
select job,avg(sal) from emp group by job;  -- 先按job分组  然后sal取平均值
select job,lower(job),avg(sal) from emp group by job;  -- 先按job分组  然后sal取平均值 job小写
-- havign 分组后二次筛选
-- 先按照 deptno 分组 然后取平均值大于 2000的数据
select deptno,avg(sal) from emp group by deptno having avg(sal)> 2000; 
​
select deptno,avg(sal) as '平均工资' from emp group by deptno having 平均工资>2000; -- 起了别名
 -- 升序排列
select deptno,avg(sal) as '平均工资' from emp group by deptno having 平均工资>2000 order by deptno asc; 
-- 降序排列
select deptno,avg(sal) as '平均工资' from emp group by deptno having 平均工资>2000 order by deptno desc; 

-- 统计各个岗位的平均工资 除了MANAGER
-- 方式1
select job,avg(sal) from emp where job != 'MANAGER'group by job;
        
-- 方式2
select job,avg(sal) from emp group by job having job != 'MANAGER';
 -- where 在分组前进行过滤      having 则是在分组后进行过滤

 -- select 语句总结
--          【1】select语句总结
--              select column, group_function(column) 
--              from table 
--              [where condition]                       -- where 进行第一次过滤
--              [group by  group_by_expression]          -- 分组
--              [having group_condition]                 --  二次过滤
--              [order by column];                      -- 进行排序
-- 
--              注意：顺序固定，不可以改变顺序
-- 
--              【2】select语句的执行顺序
--              from--where -- group  by–  select   -  having-   order  by