2024年Web前端最全五小时三万字之一篇博文带你学会SQL【期末数据库稳了】，现在主流web开发工具

2401_84446919

于 2024-05-05 00:15:51 发布

阅读量651

点赞数 21

分类专栏：程序员文章标签：数据库前端 sql

本文链接：https://blog.csdn.net/2401_84446919/article/details/138454165

版权

程序员专栏收录该内容

158 篇文章 1 订阅

订阅专栏

最后

编程基础的初级开发者，计算机科学专业的学生，以及平时没怎么利用过数据结构与算法的开发人员希望复习这些概念为下次技术面试做准备。或者想学习一些计算机科学的基本概念，以优化代码，提高编程技能。这份笔记都是可以作为参考的。

开源分享：【大厂前端面试题解析+核心总结学习笔记+真实项目实战+最新讲解视频】

名不虚传！字节技术官甩出的"保姆级"数据结构与算法笔记太香了

(2)增加一个字段，即给某个表增加一列

alter table 表名称 add 【column】字段名称数据类型; #默认在最后

alter table 表名称 add 【column】字段名称数据类型 after 另一个字段名; #指定特定位置

alter table 表名称 add 【column】字段名称数据类型 first; – 在最前面

(3)删除一个字段，即给某个表删除一列

alter table 表名称 drop 【column】字段名称;

(4)修改字段的数据类型

alter table 表名称 modify 【column】字段名称数据类型;

(5)修改字段的名称

alter table 表名称 change 【column】旧字段名称新字段名称数据类型;

(6)修改字段的位置

alter table 表名称 modify 【column】字段名称数据类型 after 另一个字段名; #指定特定位置

alter table 表名称 modify 【column】字段名称数据类型 first; – 放在最前面 */

– 修改表名称

rename table t_student to t;

alter table t rename t_student;

– 增加一列 add column

alter table t_student add sex char(2);

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后面）-- column 可以省略

alter table t_student add column score double(5,2) after sex;

– 删除一列 – drop column

alter table t_student drop column score; – column 可以省略

– 修改一列 – alter column + 新的列名+新的数据类型（在mysql中不能用该语法） mysql中用modify 和 change

alter table t_student modify column score float(4,1); #modify 修改的是列的类型的定义，但是不会改变列的名字 – column 可以省略

alter table t_student change column score scores double(5,1); # change改变列名和列的类型定义 – column 可以省略,score是旧的列名，scores是新的列名

– 删除表

drop table t_student – drop为DDL操作

– 删除数据：清空数据

truncate t_student; – truncate 属于DDL操作.即保留了这个表的结构，重新创建了这个表，效率高，隐式提交，不能回滚，自增从1开始

delete from t_student; – delete属于DML操作，一条一条的删除，效率低，但可以回滚，删除后自增依旧从以前按序号开始

– 效率drop>truncate>delete

在这里插入图片描述

这里只放两张照片，其他操作请自行验证！

1.1.1.表的完整性约束

数据库的数据完整性是指数据的正确性和数据的相容性。关系数据库的完整性可以分为实体完整性，参照完整性和用户自定义完整性。

实体完整性在创建表时用 primary key 定义，是指在基本表中主属性不能取空值，主键值不能重复。

参照完整性在创建表时用 foreign key 定义哪些列为外码，用 references 指明这些外码参照哪些表的主码。参照完整性是指在基本表中外码可以是空值或者另一个关系竹主码的有效值。

用户自定义完整性就是针对某一具体应用的数据必须满足的语义要求，包括属性上的约束条件和元组上的约束条件，主要有 not null, unique, check, default等。

1.1.1.1.非外建约束

将上面创建的t_student 表删掉，下面重新创建与插入数据：

创建表 – 用列级约束

– 创建表 – 用列级约束

– 表的完整性约束，非外建约束

create table t_student(

sno int(6) primary key auto_increment, – 主键约束与自增

– auto_increment 使得插入的sno自动从1递增，此时sno可以插入空值，如果没有auto_increment，则不能插入空值，因为主键不能为空

sname varchar(5) not null, – 非空约束

sex char(1) default ‘男’ check(sex=‘男’ || sex=‘女’) , – 默认约束与检查约束

age int(3) check(age>=18 && age<=50),

enterdata date,

classname varchar(10),

email varchar(15) unique – 唯一约束

);

select * from t_student;

insert into t_student values (1,‘张三’,‘男’,18,‘2021-9-3’,‘python-1班’,‘zs@126.com’);

insert into t_student (sname,age,enterdata,classname,email)values(‘李四’,18,‘2021-9-4’,‘python-1班’,‘ls@126.com’);

insert into t_student values (null,‘王五’,‘男’,19,‘2021-9-9’,‘python-1班’,‘wu@126.com’);

– email重复报错，但sno依然会自增,所以后面插入的不连续了

insert into t_student values (null,‘王五’,‘男’,19,‘2021-9-9’,‘python-1班’,‘wu@126.com’);

insert into t_student values (null,‘王六’,‘男’,19,‘2021-9-10’,‘python-1班’,‘wl@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),

enterdata 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)

);

select * from t_student;

insert into t_student values (1,‘张三’,‘男’,18,‘2021-9-3’,‘python-1班’,‘zs@126.com’);

insert into t_student (sname,age,enterdata,classname,email)values(‘李四’,18,‘2021-9-4’,‘python-1班’,‘ls@126.com’);

– 或者在表建成之后添加约束

alter table t_student add constraint pk_stu primary key (sno), – pk_stu 主键约束的名字

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)

1.1.1.2.外建约束与外键策略

– 创建子表（学生表）

create table t_student(

sno int(6) primary key auto_increment, – 6，显示长度

sname varchar(5) not null,

classno int(4)

);

– 添加学生信息

insert into t_student values (null,‘张三’,1),(null,‘李四’,1),(null,‘王五’,3);

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, – 6，显示长度

sname varchar(5) not null,

classno int(4),

constraint fk_stu_classno foreign key (classno) references t_class (cno) – 外键约束

);

– 上面的两个错误信息已解决

– 添加学生信息

insert into t_student values (null,‘张三’,1),(null,‘李四’,1),(null,‘王五’,3);

– 删除班级1

delete from t_class where cno=1;

– 学生表删除

drop table t_student;

– 班级表删除

drop table t_class;

– 存在外键约束且约束未设置级联删除时要先删除主表t_student（子表），再删除从表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’,‘r203’),(null,‘大数据001’,‘r416’);

select * from t_class;

– 创建子表（学生表）

create table t_student(

sno int(6) primary key auto_increment, – 6，显示长度

sname varchar(5) not null,

classno int(4),

constraint fk_stu_classno foreign key (classno) references t_class (cno) – 外键约束

);

– 添加学生信息

insert into t_student values (null,‘张三’,1),(null,‘李四’,2),(null,‘王五’,3);

select * from t_student;

– 直接删除班级2：如果直接删除的话不行，因为有外键约束

– 所以要加入外键策略

– 策略一：no action 不允许操作

– 可以先把班级2的学生班级改为null，然后删除班级2

update t_student set classno = null where classno=2;

delete from t_class where cno = 2;

– 策略二：cascade 级联操作：操作主表的时候影响从表的外键信息

– 删除外键

alter table t_student drop foreign key fk_stu_classno;

– 增加带有cascade的外键

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

– 更新操作：-- 班级3的学生的班级也会变为班级5，即t_student 表中的classno也会变成5

update t_class set cno = 5 where cno=3;

– 删除操作 – t_student 表中的classno=5的学生也会被删除

delete from t_class where cno=5;

– 策略三：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、策略二级联操作和策略三的置空操作可以混着使用

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

下面是级联更新与级联删除的示例图片：

在这里插入图片描述

1.2.视图的定义，删除与修改

(一). 视图的概念：视图( view )是一个从单张或多张基础数据表或其他视图中构建出来的虚拟表。同基础表一样,视图中也包含了一系列带有名称的列和行数据,但是数据库中只是存放视图的定义,也就是动态检索数据的查询语句,而并不存放视图中的数据,这些数据依旧存放于构建视图的基础表中,只有当用户使用视图时才去数据库请求相对应的数据,即视图中的数据是在引用视图时动态生成的。因此视图中的数据依赖于构建视图的基础表,如果基本表中的数据发生了变化,视图中相应的数据也会跟着改变。

PS :视图本质上就是:一个查询语句,是一个虚拟的表,不存在的表,你查看视图,其实就是查看视图对应的 sql 语句。

(二). 视图的好处:简化用户操作:视图可以使用户将注意力集中在所关心地数据上,而不需要关心数据表的结构、与其他表的关联条件以及查询条件等。对机密数据提供安全保护,有了视图,就可在设计数据库应用系统时,对不同的用户定义不同的视图,避免机密数据(如,敏感字段“ salary ")出现在不应该看到这些数据的用户视图上。这样视图就自动提供了对机密数据的安全保护功能。

下面我们来实操：

先建立两张表并插入数据：

– dept(部门表)

create table DEPT(

DEPTNO int(2) not null,

DNAME VARCHAR (14),

LOC VARCHAR(13)

);

alter table DEPT add constraint PK_DEPT primary key(DEPTNO);

– empty（员工表）

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);

– 向部门表中插入数据

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’);

– 向员工表中插入数据

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’,2957,null,20);

insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,‘CLARK’,‘MANAGER’,7566,‘1987-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’,‘PRESIDNET’,null,‘1981-11-17’,5000,null,10);

insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,‘TURNER’,‘SALESMAN’,7698,‘1987-0419’,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’,5000,null,10);

insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,‘FORD’,‘ANALYST’,7566,‘1987-12-03’,950,null,30);

insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,null,10);

然后我们进行视图的练习

– 创建视图

– create [or replace ] view <视图名> [属性1，属性2，…] as select子句 [with check option];

– 1.创建单表视图

create view myview01

select empno,ename,job,deptno from emp

where deptno = 20;

– 等价于

create view myview01 (empno,ename,job,deptno) – 字段个数与select子句的字段个数要相同，属性名可以不同

select empno,ename,job,deptno from emp

where deptno = 20;

– 以上两个创建视图SQL对于创建同一名字相同的视图，一个SQL只可以运行一次，再次运行会报错视图已存在

– 使用下面方法可以修改创建视图的SQL，并替换旧视图

create or replace view myview01 – 这样这个sql就可以多次运行，修改视图只需要修改这个SQL，新视图会替换旧视图

select empno,ename,job,deptno from emp

where deptno = 20;

– 在视图中插入数据

– 我们建立视图的时候条件where deptno=20,所以插入dept=20的数据不会出错

insert into myview01 (empno,ename,job,deptno) values (22,‘lili’,‘clerk’,20);

insert into myview01 values (33,‘nana’,‘clerk’,20);

– 那么我们插入dept=30的数据语法上不会有错，但逻辑上我们是不允许插入成功的，而dept=30的这条数据确插入到了emp表中

insert into myview01 values (44,‘feifei’,‘clerk’,30);

select * from emp;

– 为了解决它，我们在建视图示时末尾加上with check option

– with check option 表示对视图进行update,delete,insert操作时都会检查是否满足试图定义时的谓词条件；

create or replace view myview01

select empno,ename,job,deptno from emp

where deptno = 20

with check option ; – 这样只有dept=20的数据才可以插进去

insert into myview01 values (44,‘feiei’,‘clerk’,30);

– 2.创建/替换多表视图

create or replace view myview02

select e.empno,e.ename,e.sal,d.deptno,d.dname

from emp e

join dept d

on e.deptno=d.deptno

where sal >2000

with check option;

– 注意

select e.empno,e.ename,e.sal,d.deptno,d.dname

from emp e

join dept d

on e.deptno=d.deptno

where sal >2000;

– 等价于

select e.empno,e.ename,e.sal,d.deptno,d.dname

from emp e, dept d

where e.deptno=d.deptno and sal >2000; – 在连接查询中有讲到，这种写法通用所有数据库

– 查看视图myview02

select * from myview02;

– 3.创建统计视图 – 带有聚集函数和groupby的叫分组视图，即这儿的统计视图

create or replace view myview03

select e.deptno,d.dname,avg(sal),min(sal),count(1)

from emp e join dept d

using(deptno) – 在连接查询中讲过，在mysql中using子句消除部分通名列，把你要消除的同名列放到using()括号中

group by e.deptno;

– 查看视图myview03

select * from myview03;

– 4.创建基于视图的视图

create or replace view myview04

select * from myview03 where deptno=20;

– 查看视图myview04

select * from myview04;

– 修改视图

alter view myview01

select * from emp where deptno = 20;

– 查看视图

SELECT * FROM myview01;

– 删除视图

drop view myview01;

下面贴部分照片

在这里插入图片描述

1.3.索引的建立与删除

索引的介绍：索引是一种数据结构，例如B-Tree，这种数据结构是需要额外的写入和存储为代价来提高表上数据检索的速度。一旦建立了索引后，数据库中查询优化器使用索引来快速定位数据，然后就无需扫描表中给定查询的每一行了。

其中。当使用主键或唯一键创建表时，MySQL会自动创建名为PRIMARY的特殊索引, 该索引称为聚簇索引。PRIMARY索引是比较特殊的，这个索引本身与数据一起存储在同一个表中。另外除PRIMARY索引之外的其他索引称为二级索引或非聚簇索引。

（注意：通常，创建表的时候就能为表创建索引。但是要为列或一组列添加索引，可以使用 CREATE INDEX 索引名 ON 表名 <列名> 语句

– 通常，创建表的时候就能为表创建索引。例如，以下语句创建一个新表，并创建了是由两列c2和c3组成的索引。

CREATE TABLE t(

c1 INT PRIMARY KEY,

c2 INT NOT NULL,

c3 INT NOT NULL,

c4 VARCHAR(10),

INDEX (c2,c3)

);

– 但是要为列或一组列添加索引，可以使用CREATE INDEX语句

#对t_student表的sno建立索引之前

– 我们要查看MySQL如何在内部执行此查询，可以在SELECT语句的开头添加EXPLAIN子句，

explain select * from t_student where sno = 6;

– 为之前的t_student表的sno字段添加名为index_1的索引

create index index_1 on t_student(sno);

– 等价于

alter table t_student add index index_1(sno);

– 修改索引

– 将t_student 表的索引名index_1 改为 index_2

alter table t_student rename index index_1 to index_2;

– 删除索引

alter table t_student drop index index_2;

我们要查看MySQL如何在内部执行此查询，可以在SELECT语句的开头添加EXPLAIN子句，会得到下面的结果：

在这里插入图片描述

可以看到MySQL必须扫描包含7行的整个表，以查找sno = 7的人，当我们为这个表的sno字段建立索引之后，结果如下：

在这里插入图片描述

这时候看到，MySQL只需要在键列中指示的sno索引中找到1行而不扫描整个表，大大节省了时间。

事实上，关于索引的知识点还有很多（对于mysql我们自己创建索引不声明则默认为B_TREE类型索引），这里只写索引基础，感兴趣的同学可以自行去百度。

坚持住！！💗💗💗

在这里插入图片描述

2.数据查询（DQL）

==========================================================================

数据查询是数据库的核心操作，在这一块内容较多，主要的关键字有：select，where，group by, having, order by 等。

在学习这块内容之前，我们先创建四张比较经典的表，如下：

– 准备四张表:dept(部门表)，empty（员工表），salgrade(薪资等级表)，bonus（奖金表）

– dept(部门表)

create table DEPT(

DEPTNO int(2) not null,

DNAME VARCHAR (14),

LOC VARCHAR(13)

);

alter table DEPT add constraint PK_DEPT primary key(DEPTNO);

– empty（员工表）

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);

– salgrade(薪资等级表)

create table SALGRADE(

GRADE int primary key,

LOSAL double(7,2),

HISAL double(7,2)

);

– bonus（奖金表）

CREATE TABLE bonus (

ENAME varchar(10) DEFAULT NULL,

JOB varchar(9) DEFAULT NULL,

SAL double(7,2) DEFAULT NULL,

COMM double(7,2) DEFAULT NULL

);

– 向三张表中插入数据