1.数据定义（DDL）

关系型数据库系统支持三级模式结构，其模式，内模式和外模式中的基本对象有模式，表，视图和索引等，因此SQL的数据定义功能包括模式定义，表定义，视图和索引的定义，而mysql和其他的数据库有区别，他的模式就是数据库，因此关于模式的内容我不做解释，有兴趣的同学可以自行了解！

1.1基本表的定义，删除与修改

我们使用 create table <表名> sql语句来创建表，使用drop table <表名> sql语句来删除表，使用 alter table <表名> sql语句来修改表。

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

-- 注释
#只是一个单行注释, -- 也是单行注释
/*多行注释
多行注释*/
-- 创建数据库表
/*create table 表名称(
字段名 数据类型,
字段名 数据类型
);
*/
-- 查看表的结构:展示表的详细信息
-- desc 表名;
-- 查看建表语句
-- show create table 表名;
/*建立一张用来存储学生信息的表
字段包含学号，姓名，性别，年龄，入学日期，班级，email等信息*/
-- DDL
create table t_student(
 sno  int(6),   -- 6，显示长度
 sname varchar(5),
 sex char(1),
 age int(3),
 enterdata date,
 classname varchar(10),
 email varchar(15)
 );
-- 查看表的结构:展示表的详细信息
desc t_student;
-- 查看表中数据-- DQL
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,
  `enterdata` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci */
-- 添加一张表，快速添加，结构和数据跟t_student 都是一致的
create table t_student_2
as
select * from t_student;
-- 添加一张表，快速添加，结构跟t_student一样，数据么有;
create table t_student_3
as
select * from t_student where 1=2;
-- 快速添加，只要部分列，部分数据
create table t_student_4
as
select sno,sname from t_student where sno=2;

然后向表中插如一些数据以供我们学习和操作！（以下操作属于DML

-- DML
-- 在t_student 表中插入数据
-- 整型数据超出位数，系统会自动补全
insert into t_student values (1,'张三','男',18,'2022-5-8','软件一班','123@126.com');
insert into t_student values (1,'张三','男',18,now(),'软件一班','123@126.com');
insert into t_student values (4,'张三','男',18,'2022-5-8','软件一班','123@126.com');
insert into t_student values (1,'李四','男',18,now(),'软件一班','123@126.com');
insert into t_student values (4,'张三','男',18,now(),'python一班','123@126.com');
insert into t_student values (6,'张三','男',18,now(),'python一班','123@126.com');
-- 如果不是全字段插入数据的话，需要加入字段的名字
insert into t_student (sno,sname,enterdata) values (3,'李四','2021-7-5');
-- 修改表中数据
-- 修改一整列
update t_student set sex = '女';
-- 修改确定的
update t_student set sex = '男' where sno = 1;
-- 表名，关键字，字段,内容不区分大小写
UPDATE T_STUDENT SET AGE = 20 WHERE SNO = 1;
-- 删除操作
-- 全表删除
delete from t_student;
-- 加条件删除
delete from t_student where sno = 1;

然后我们进行对表进行删除和修改（注意：对表进行删除和修改属于DDL操作，对表中数据进行删除和修改属于DML操作）。

-- 查看数据
select * from t_student;
-- 修改表的结构
-- DDL 
/*
(1)修改表名称
rename table 旧名称 to 新名称;
alter table 旧名称 rename 新名称;
(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
as
select empno,ename,job,deptno from emp
where deptno = 20;
-- 等价于
create view myview01 (empno,ename,job,deptno)  -- 字段个数与select子句的字段个数要相同，属性名可以不同
as
select empno,ename,job,deptno from emp
where deptno = 20;
-- 以上两个创建视图SQL对于创建同一名字相同的视图，一个SQL只可以运行一次，再次运行会报错视图已存在
-- 使用下面方法可以修改创建视图的SQL，并替换旧视图
create or replace view myview01  -- 这样这个sql就可以多次运行，修改视图只需要修改这个SQL，新视图会替换旧视图
as
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 
as
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
as
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
as
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
as
select * from myview03 where deptno=20;
-- 查看视图myview04
select  * from myview04;
-- 修改视图
alter view  myview01
as 
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
);
-- 向三张表中插入数据
-- 向部门表中插入数据
 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);
-- 向薪资表中插入数据
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);
-- 部门表   LOC-位置
select * from dept;
-- 员工表  MGR-上级领导， COMM- 补助
select * from emp;
-- 薪资表
select *from salgrade;
-- 奖金表 
select * from bonus;

2.1.单表查询

-- 对EMP 表查询
select * from emp;
-- 显示部分列
select empno,ename,sal from emp;
-- 显示部分行
select * from emp where sal>2000;
-- 显示部分行部分列
select empno,ename,job,mgr from emp where sal>2000;
-- 起别名
select empno 员工编号,ename 姓名,sal 工资 from emp; -- 省略as和引号
-- as （alias）别名
select empno as 员工编号,ename as 姓名,sal as 工资 from emp; 
-- 在别名中有特殊符号时，引号不可省略
select empno as '员工 编号',ename as "姓名",sal as 工资 from emp; 
 -- 算数运算符
 select empno,ename,sal,sal+1000 as '涨新后',deptno from emp where sal<2000;
 select empno,ename,sal,comm,sal+comm from emp; -- null + int = null
 -- 去重操作
 select distinct job from emp;
 select distinct job,deptno from emp;
 -- 排序
 select * from emp order by sal; -- 默认是升序
 select * from emp order by sal asc;  -- asc 升序
 select * from emp order by sal desc;  -- desc 降序
 select * from emp order by sal，deptno desc; -- 在工资升序的情况下，deptno按照降序排列

2.1.1.where 子句

-- 查看emp表
select * from emp;
-- where 子句 + 关系运算符
select * from emp where deptno = 10;
select * from emp where deptno > 10;
select * from emp where deptno >= 10;
select * from emp where deptno < 10;
select * from emp where deptno <= 10;
select * from emp where deptno <> 10;   -- <>  不等于
select * from emp where deptno != 10;
select * from emp where deptno = 'CLERK';  -- 默认情况下，不区分大小写
select * from emp where binary job = 'clerk'; -- binary 区分大小写
select * from emp where hiredate <'1981-12-25';
-- where 子句 + 逻辑运算符
select * from emp where sal > 1500 and sal < 3000;
select * from emp where sal > 1500 && sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000; -- 闭区间
select * from emp where sal = 1500 or sal = 3000;
select * from emp where sal > 1500 || sal < 3000;
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');
-- where 子句 + 模糊查询
-- 查询名字中带A的员工  --%代表任意多个字符，0,1,2···
select * from emp where ename like '%A%';
select * from emp where ename like '_A%'; --  _代表任意一个字符
select * from emp where ename like '__A%'; -- 两个_
-- 查询名字中不带A的员工
select* from emp where ename not like '%A%';
-- 注意：如果查询的字符串中包含通配符%和_,这时就要使用escape '<换码字符>'对通配符进行转义
-- 假设有个员工叫mit_b,查询这个员工的信息
--select * from emp where ename like 'mit\_b' escape'\';
-- 关于 null 的判断
select * from emp where comm is null;
select * from emp where comm is not null;
-- 小括号的使用  -- and 的优先级别大于or
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500;
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500);
select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

2.1.2.函数

-- 函数的分类
-- lower(ename),upper(ename);  -- 改变每一条结果，每一条数据对应一条结果 -- 单行函数
-- max(sal),min(sal),count(sal),sum(sal),avg(sal); -- 多条数据，最终显示一个结果   -- 多行函数
-- 单行函数包含
    -- 1.字符串函数
    select ename,length(ename),substring(ename,2,3) from emp; 
    -- substring(),提供三个参数，第一个为列名，第二个为从第几个字符截取（下标从一开始），第三个参数为截取位数
    -- 2.数值函数
    select abs(-5) as 绝对值 ,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入 from dual;  -- dual 实际上是一个伪表
    select abs(-5) as 绝对值 ,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件，from dual 可以省略不写
    select ceil(sal) from emp;
     -- 3.日期
     select curdate(),curtime();  -- curdate()年月日   -- curtime()时分秒
     select now(),sysdate(),sleep(3),now(),sysdate() from dual;   -- now()当前时间  -- sysdate()函数执行时间
     -- 4.流程函数
       -- if 相关
       select empno,ename,sal,if (sal >= 2500,'高新','低薪') as '薪资等级' from emp; -- if -else 双分支结构
       select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- 如果comm 为null,则comm取值为0  -- 单分支结构
       select nullif(1,1), nullif(1,2) from dual;  -- 如果value1等于value2,则返回null,否则返回1;
      -- case 相关
      select empno,ename,job,
      case job
       when "CLERK" then "店员"
       when "SALESMAN" then "销售"
       when "MANAGER"  then "经理"
       else "其他"
       end  as '岗位',
      sal from emp;
       -- case 区间判断
      select empno,ename,sal,
       case sal
         when sal<= 1000 then 'A'
         when sal<= 2000 then 'B'
         when sal<= 3000 then 'C'
         else 'D'
         end '工资等级',
       deptno from emp;
    -- 5.json 函数
    -- 6.其他函数
    select database() ,user(),version() from dual;
-- 多行函数(聚集函数) -- 注意聚集函数只能放在select 或者group by的having子句后
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
-- 多行函数自动忽略null值；
select * from emp;
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()针对所有类型，  sum(),avg()只针对数值类型有效
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
-- count() -- 计数
-- 统计表的记录数:方式一
select count(ename) from emp;
select count(*) from emp;
-- 统计表的记录数:方式二
select 1 from dual; -- dual 实际上是一个伪表
select 1 from emp;
select count(1) from emp;

2.1.3.分组group by 和 having

-- 统计各个部门的平均工资；
select deptno,avg(sal) from emp;  -- 字段和多行函数不能同时使用，这要分组
select deptno,avg(sal) from emp group by deptno;  -- 字段和多行函数不能同时使用,除非这个字段属于分组
select deptno,avg(sal) from emp group by deptno order by deptno desc;  
-- 统计各个岗位的平均工资
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;
-- 统计各个部门的平均工资，只显示平均加工资2000以上的
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;
-- 统计各个岗位的平均工资，除了MANAGER
-- where 在分组前过滤，having在分组后过滤
select job,avg(sal) from emp where job != 'MANAGER' group by job;
select job,avg(sal) from emp group by job having job != "MANAGER";
-- 单表查询练习
-- 列出工资最小值小于2000的职位
select job,min(sal) from emp group by job having min(sal)<2000;
-- 列出平均工资大于1200元的部门和工作搭配组合
select job,deptno,avg(sal)
from emp group by job,deptno
having avg(sal)>1200;

注意：面试官总会问你where和having的区别：

他们作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组。WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算），而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数；因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。相反，HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句，但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）

综上所述：

having一般跟在group by之后，执行记录组选择的一部分来工作的。

where则是执行所有数据来工作的。

再者having可以用聚合函数。

2.2.多表查询（连接查询）

-- 双表查询
  select * from emp;
  select * from dept;
  -- 多表查询
  -- 交叉连接 cross join
  select * from emp cross join dept;  -- 7*4 = 28 条 笛卡尔乘积 ，没有实际意义，有理论意义
  select * from emp join dept; -- cross 可以省略不写，mysql中可以，Oracle中不可以
  -- 等值连接（同名列会展示两次）
  select emp.*,dept.* from emp,dept where emp.DEPTNO = dept.DEPTNO;
  -- 自然连接 natural join
  -- 优点：自动匹配所有同名列，同名列只展示一次，即去掉了重复属性
  select * from emp natural join dept;
  -- 等价于
  select emp.*,dept.dname,dept.loc from emp,dept where emp.DEPTNO = dept.DEPTNO; -- （所有数据库通用）
  select empno,ename,sal,dname,loc from emp natural join dept;
  -- 缺点：查询字段的时候么有指定字段所属的数据库表，效率低
  -- 解决：指定表名
  select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno from emp natural join dept;
  -- 如果遇到表名过长的情况，可以给表起别名
  select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno from emp e natural join dept d;
  -- 内连接  -- inner 可以省略
  -- 自然连接的缺点，自动匹配表中所有的同名列，但是有时候我们只希望只匹配部分同名列
  -- 解决：1.using子句  
  select * from emp e  inner join dept d using(deptno);  -- inner 可以省略
  -- 像这种语句，我们都可以用where子句解决，在select后减少重复的字段就行，且这种写法通用性强，适应所有数据库
  -- 而on 子句 ，显示的是所有匹配的信息
  select * from emp e inner join dept d on(e.deptno = d.deptno);
  -- 等价于等值连接（同名列会展示两次）
  select emp.*,dept.* from emp,dept where emp.DEPTNO = dept.DEPTNO;
  select * from emp e inner join dept d on(e.deptno = d.deptno) where sal >3500;
  -- 问题：
  -- 40号部门没有员工，没有显示在查询结果中， 员工scott没有部门，没有显示在查询结果中
  -- 外连接：除了显示匹配的数据以外，还可以显示不匹配的数据 -- outer 可以省略不写
  -- 左外连接：left outer join -- 左边的那个表的信息即使不匹配也可以显示出来
  select * from emp e left outer join dept d on e.deptno = d.deptno;
  -- 右外连接：left outer join -- 右边的那个表的信息即使不匹配也可以显示出来
  select * from emp e right outer join dept d on e.deptno = d.deptno;
  -- 全外连接 ： full outer join -- 这个在mysql 中不支持,在oracle中支持
  -- 解决： 并集
  select * from emp e left outer join dept d on e.deptno = d.deptno
  union -- 并集 去重
  select * from emp e right outer join dept d on e.deptno = d.deptno
  select * from emp e left outer join dept d on e.deptno = d.deptno
  union all -- 并集 不去重
  select * from emp e right outer join dept d on e.deptno = d.deptno;
  -- mysql 中对集合操作比较弱，只支持并集，交集差集不支持（Oracle中支持）
-- 三表查询
-- 查询员工的编号，姓名，薪水，部门编号，部门名称，薪水等级
select * from emp;
select * from dept;
select * from salgrade;
select * from emp e inner join dept d on e.deptno = d.deptno inner join salgrade s on e.sal between s.losal and s.hisal;
-- 自连接 - 自己连接自己
-- 查询员工的编号，姓名，上级编号，上级姓名
select * from emp;
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

下面展示部分运行结果

2.3.嵌套查询

-- 不相关子查询
  -- 引入子查询
  -- 查询所有比“CLARK”工资高的员工工资
  -- 步骤一：查询“CLARK”工资
  select sal from emp where ename = "CLARK";
  -- 步骤二：查询所有工资比1450高的员工
  select * from emp where sal>2450;
  -- 两次命令解决问题，效率低，第二个命令依托于第一个命令
  -- 解决：
  -- 将步骤一与步骤二而合并    ——子查询
  select * from emp where sal>(select sal from emp where ename = "CLARK");
  -- 单行子查询
  -- 查询工资高于平均工资的雇员名字和工资
  select ename,sal from emp where sal > (select avg(sal) from emp);
  -- 查询和“CLARK”同一部门并且比他工资低的雇员的名字和工资
  select ename,sal from emp where deptno = (select deptno from emp where ename = "CLARK") and sal < (select sal from emp where ename = 'CLARK');
  -- 查询职务和SCOTT相同，比SCOTT雇佣时间早的雇员信息
  select * from emp where job = (select job from emp where ename = 'SCOTT') and hiredate < (select hiredate from emp where ename = 'SCOTT');
  -- 多行子查询   -- 里面的查询语句结果为多行
  -- 查询部门20中职务同同部门10的雇员一样的雇员信息
  select * from emp where deptno = 20 and job in (select job from emp where deptno =10);
  select * from emp where deptno = 20 and job = any(select job from emp where deptno =10);
  -- 查询工资比所有的“SALESMAN”都高的雇员的编号，名字和工资。
  select empno,ename,sal from emp where sal > all(select sal from emp where job = "SALESMAN");
    -- 单行子查询写法
  select empno,ename,sal from emp where sal > (select max(sal) from emp where job = "SALESMAN");
  -- 查询工资低于任意一个“CLERK”的工资的员工信息
  select * from emp where sal < any(select sal from emp where job = 'CLERK');
    -- 单行子查询写法
  select * from emp where sal < (select max(sal) from emp where job = 'CLERK');
-- 注意：包含any，all谓词的子查询可以用包含聚集函数的select子句表示，两者等价
-- 相关子查询
  select * from emp where sal = (select max(sal) from emp); -- 不相关子查询
  -- 查询本部门最高工资的员工 
  -- 方法一：通过不相关子查询实现
  select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
  union
  select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
  union
  select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30);
  -- 方法二： 相关子查询
  select * from emp e where sal=(select max(sal) from emp where deptno = e.deptno); 
  -- 查询工资高于其所在岗位的平均工资的那些员工
  select * from emp e where sal>= (select avg(sal) from emp where job = e.job);
-- 带有exists谓词的子查询
select sal from emp where EXISTS (select sal from emp where sal between 800 and 1800);
select sal from emp where not EXISTS (select sal from emp where sal between 800 and 1800);
-- 注意：带有exists谓词的子查询只返回逻辑真假，即若内层结果查询非空，则外层的where子句返回真值，否则返回假值

下面展示部分运行结果：

3.数据操纵（数据更新）（DML）

主要的关键字就三个：insert，update ，delete。

3.1数据的插入，修改，删除

-- 插入
/*
#为所有字段赋值，一次添加一行
insert into 表名称 values(值列表); #要求值的数量、顺序与表结构的数量、顺序一一对应
#为指定字段赋值，一次添加一行
insert into 表名称(字段列表) values(值列表);
#为所有字段赋值，一次添加多行
insert into 表名称 values(值列表),(值列表),(值列表)...;
#为指定字段赋值，一次添加多行
insert into 表名称(字段列表) values(值列表),(值列表),(值列表）...;
*/
-- 修改
#这种是修改所有行的部分字段
/*update 表名称 set 字段名 = 字段值, 字段名 = 字段值 ...; 
#这种是修改部分行(满足条件的行)的部分字段
update 表名称 set 字段名 = 字段值, 字段名 = 字段值 ... where 条件; 
*/
-- 删除
/*
1、删除整张表的数据
(1)delete from 表名称;
(2)truncate 【table】 表名称; -- table 可以省略 -- DDL
注意：(1)和(2)的区别：
-- delete属于DML操作，一条一条的删除，效率低，但可以回滚，删除后自增依旧从以前按序号开始
-- truncate 属于DDL操作.即保留了这个表的结构，重新创建了这个表，效率高，隐式提交，不能回滚，自增从1开始
-- 效率drop(DDL)>truncate(DDL)>delete(DML)
2、删除部分行
delete from 表名称 where 条件; #删除满足条件的行
*/
create table t_student(
 sno  int(6),   -- 6，显示长度
 sname varchar(5),
 sex char(1),
 age int(3),
 enterdata date,
 classname varchar(10),
 email varchar(15)
 );
-- 查看表记录
select * from t_student;
-- DML
-- 在t_student 表中插入数据
-- 整型数据超出位数，系统会自动补全
-- 第一种插入方法
insert into t_student values (1,'张三','男',18,'2022-5-8','软件一班','123@126.com');
insert into t_student values (1,'张三','男',18,now(),'软件一班','123@126.com');
insert into t_student values (4,'张三','男',18,now(),'python一班','123@126.com');
-- 第二种插入方法
insert into t_student values (1,'张三','男',18,'2022-5-8','软件一班','123@126.com'),(1,'张三','男',18,now(),'软件一班','123@126.com'),(4,'张三','男',18,now(),'python一班','123@126.com');
-- 如果不是全字段插入数据的话，需要加入字段的名字
insert into t_student (sno,sname,enterdata) values (3,'李四','2021-7-5');
-- 修改表中数据
-- 修改一整列
update t_student set sex = '女';
-- 修改确定的
update t_student set sex = '男' where sno = 1;
-- 表名，关键字，字段,内容不区分大小写
UPDATE T_STUDENT SET AGE = 20 WHERE SNO = 1;
-- 删除操作
-- 全表删除
delete  from t_student;
-- 加条件删除
delete from t_student where sno = 1;

这块内容较为简单，不多做叙述！

4.数据控制（DCL）

DCL ( Data Control Language )语句:数据控制语言,主要是用来设置/更改数据库用户权限。常用关键字有 GRANT 、 REVOKE 等。

一般人员很少用到 DCL 语句，所以不多叙述，知道 GRANT (授权)，==REVOKE (取消权限)==就行

5.事务

写不动了，这块内容简单上几张图，想要了解的同学自行去查，最著名的就是银行的转账案例了。以及脏读，不可重复读，幻读的区别，同志们自行了解…

6.结语

关于mysql的基础知识就这么多了，只要大家耐心看完跟着敲，我保你数据库的实操部分过关，爆肝5小时，原创不易，请勿摘抄!

如果这篇博文对你有帮助，不妨一键三连，有疑问评论或者私信我，一一解答，事实上掌握数据库的知识还是很重要的，例如网络安全领域里的sql注入，也是非常的有趣，感兴趣的小伙伴可以去了解了解。

学到这你以为已经学完了吗！不，只能说你的数据库学习之路才刚刚开始，还有大量的知识等着你去学，包括断言，触发器，游标对象，存储过程等等！所以一起加油吧！

五小时三万字之一篇博文带你学会SQL【期末数据库稳了】

1.1.1.2.外建约束与外键策略

2.数据查询（DQL）

2.1.单表查询

2.1.1.where 子句

2.1.2.函数

2.1.3.分组group by 和 having

2.3.嵌套查询

3.数据操纵（数据更新）（DML）

3.1数据的插入，修改，删除

热门文章

最新文章

相关课程

相关电子书

相关实验场景