SQL语言入门

创建数据库

单行注释:##

多行注释:/* */

create table t_student(

sno int(6),

sname varchar(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;

数据库表列类型

整数类型

MySQL支持选择在该类型关键字后面的括号内指定整数值的显示宽度。显示宽度并不限制可以在列内保从的值的范围，也不限制超过列的指定宽度的值的显示

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

浮点数类型

与整数类型不一样的是，浮点数类型的宽度不会自动扩充。 score double(4,1)

score double(4,1)--小数部分为1位，总宽度4位，并且不会自动扩充。

字符串类型

CHAR和VARCHAR类型相似，均用于存于较短的字符串，主要的不同之处在于存储方式。CHAR类型长度固定，VARCHAR类型的长度可变。

因为VARCHAR类型能够根据字符串的实际长度来动态改变所占字节的大小，所以在不能明确该字段具体需要多少字符时推荐使用VARCHAR类型，这样可以大大地节约磁盘空间、提高存储效率。

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

日期和时间类型

写入当前时间 now(),sysdate(),CURRENT_DATE()

添加数据

查看表数据

select * from t_student;

在数据库表中插入数据

insert into t_student values (1,'zhangsan','nan',18,'200-5-8','ruanjian','123@qq.com');

ps:字符串不区分单引号和双引号

char varchar是字符的个数，不是字节的个数，可以使用binary,varbinary表示定长和不定长的字节个数

如果不是全字段插入数据，需加入字段的名字

insert into t_student (sno,sname,enterdate) values (10,'lisi','2023-7-5');

修改，删除数据

修改表中数据

update t_student set sex = 'nv' where sno=10;

删除数据库

delete from t_student where sno = 2;

ps:关键字，表名字段名不区分大小写

默认情况下，内容不区分大小写

删除操作from关键字不可缺少

修改，删除数据库加限制条件

修改，删除数据库表

修改表的结构

增加一列

alter table t_student add score double(5,2);//5:总位数 2：小数位数

删除一列

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;

非外键约束

主键的作用

可以通过主键查到唯一的一条记录

主键约束

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

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

非空约束

规定了一张表中指定的某个字段的值不能为空（NULL）。设置了非空约束的字段，在插入的数据为NULL时，数据库会提示错误，导致数据无法插入。

无论是单个字段还是多个字段非空约束的添加只能使用列级约束（非空约束无表级约束）

为一存在在表中的字段添加非空约束

alter table student8 modify stu_sex varchar(1) not null;

删除非空约束

alter table student8 modity stu_sex varchar(1) null;

唯一约束

它规定了一张表中指定的某个字段的值不能重复，即这一字段的每个值都是唯一的。如果想要某个字段的值不重复，那么就可以为该字段添加为唯一约束。

无论单个字段还是多个字段唯一约束的添加均可使用列级约束和表级约束

检查约束

用来限制某个字段的取值范围，可以定义为列级约束，也可以定义为表级约束。MySQL8开始支持检查约束。

默认值约束

默认值约束（DEFAULT）用来规定字段的默认值。如果某个被设置为DEFAULT约束的字段没插入具体值，那么该字段的值将会被默认值填充。

默认值约束的设置与非空约束一样，也只能使用列级约束。

字段值自动增加约束

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

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

为已存在表中的字段添加自增约束

alter table student11 modify stu_id int(10) auto_increment;

使用alter table 语句删除自增约束

alter table student11 modify stu_id int(10);

外键约束

创建父表

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

添加学生表，添加外键约束

cretae table t_student(

sno int(6) primary key auto_increment,

sname varchar(5) not null,

classno int(4),

constraint fk_stu_classno foreign key (classno) references t_class(cno)

)

在创建表之后添加外键约束

alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno)

删除有外键约束的班级表

1.将学生对应的班级改位null

update t_student set classno = null where classno = 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

更新

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;

策略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 ;

应用场合：

朋友圈删除，点赞。留言都删除 -- 级联操作

解散班级，对应的学生 置为班级为null就可以了，-- set null

delete和truncate的区别：

从最终的结果来看，虽然使用TRUNCATE操作和使用DELETE操作都可以删除表中的全部记录，但是两者还是有很多区别的，其区别主要体现在以下几个方面：

(1)DELETE为数据操作语言DML；TRUNCATE为数据定义语言DDL。

(2) DELETE操作是将表中所有记录一条一条删除直到删除完；TRUNCATE操作则是保留了表的结构，重新创建了这个表，所有的状态都相当于新表。因此，TRUNCATE操作的效率更高。

(3)DELETE操作可以回滚；TRUNCATE操作会导致隐式提交，因此不能回滚（在第十章中会讲解事务的提交和回滚）。

(4)DELETE操作执行成功后会返回已删除的行数（如删除4行记录，则会显示"Affected rows：4"）；截断操作不会返回已删除的行量，结果通常是"Affected rows：0"。DELETE操作删除表中记录后，再次向表中添加新记录时，对于设置有自增约束字段的值会从删除前表中该字段的最大值加1开始自增；TRUNCATE操作则会重新从1开始自增。

数据查询

最简单的sql语句

对emp表查询

select * from emp;

显示部分行

select empno,ename,,sal from emp;

显示部分行:where子句

select * from emp where sal > 2000;

显示部分列，部分行

select empno,ename,job,mgr from emp where sal > 2000;

起别名

select empno c,ename b,sal a from emp;

as alias别名

select empno as c,ename as b,sal as a from emp;

在别名中有特殊符号的时候，""或者''不可以省略不写

select empno '1',ename '2',sal '3' from emp;

算术运算符

select empno,ename,sal,sal+1000,deptno from emp where sal < 2500;

select empno,ename,sal,comm,sal+comm from emp;

去重操作

select distinct job from emp;

排序

select * from emp order by sal; //默认情况下是按照升序排列的

select * from emp order by sal desc; //desc降序

select *from emp order by sal asc, deptno desc; //在sal升序的情况下，按deptno降序

where子句

关系运算符

select * from emp where deptno = 10;

逻辑运算符 and

select * from emp where sal > 1500 and sal < 3000; -- (1500,3000)

逻辑运算符 or

select * from emp where deptno = 10 or deptno = 20;

模糊查询

select * from emp where ename like '%A%' ;

关于null的判断

select * from emp where comm is null;

小括号的使用 ：因为不同的运算符的优先级别不同，加括号为了可读性

select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or and > or

函数的分类

函数的功能:分装了一定的一些功能，直接拿过来使用，可以实现一定的功能

注意:函数没有改变数据自身的值，而是在真实数据的上面进行加工处理，展示新的结果而已

单行函数

单行函数是指对每一条记录输入值进行计算，并得到相应的计算结果，然后返回给用户，也就是说，每条记录作为一个输入参数，经过函数计算得到每条记录的计算结果。

常用的单行函数主要包括字符串函数、数值函数、日期与时间函数、流程函数以及其他函数。

字符串函数

select ename,length(ename),substring(ename,2,3) from emp;

substring字符串截取，2:从字符下标为2开始，3:截取长度(下标从1开始)

数值函数

select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual;

dual实际就是一个伪表

select abs(-5) "1",ceil(5.3) '2',floor(5.9) '3',round(3.14) '4';

如果没有where条件的话，from dual可以省略不写

select ceil(sal) from emp;

select 10/3,10%3,mod(10,3);

日期与时间函数

select curdate(),curtime();

select now(),sysdate(),sleep(3),now(),sysdate() from dual;

insert into emp values (9999,'lili','SALASMAN',6532,now(),1000,null,30);

流程函数

if相关

select empno,ename,sal,if(sal>=2500,"gao","di") as "dengji" from emp;

select empno,ename,sal,comm,sal+ifnull(comm,0) from emp;

如果comm是null，那么取值问0

select nullif(1,1),nullif(1,2) from dual; -- 如果value1等于value2，则返回null，否则返回value1

case相关：

case等值判断

select empno,ename,job,

case job

when 'CLERK' then '店员'

when 'SALESMAN' then '销售'

when 'MANAGER' then '经理'

else '其他'

end '岗位',

sal from emp;

case区间判断:

select empno,ename,sal,

case

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;

多行函数

多行函数是指对一组数据进行运算，针对这一组数据（多行记录）只返回一个结果，也称为分组函数。

除了多行函数(max,min,count,sum,avg)都是单行函数

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

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

多行函数自动忽略null值

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

max(),min(),count()针对所有类型 sum(),avg() 只针对数值型类型有效

count --计数

统计表的记录数：方式1：

select * from emp;

select count(ename) from emp;

select count(*) from emp;

统计表的记录数：方式2

select 1 from dual;

select 1 from emp;

select count(1) from emp;

group by:用来进行分组

select * from emp;

统计各个部门的平均工资

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以上的 - 分组以后进行二次筛选 having

select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;

select deptno,avg(sal) 平均工资 from emp group by deptno having 平均工资 > 2000;

select deptno,avg(sal) 平均工资 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语句总结

select column, group_function(column)

from table

[where condition]

[group by group_by_expression]

[having group_condition]

[order by column];

注意：顺序固定，不可以改变顺序

select语句的执行顺序

from--where -- group by– select - having- order by

单表查询练习：

单表查询练习：

-- 列出工资最小值小于2000的职位

select job,min(sal)

from emp

group by job

having min(sal) < 2000 ;

-- 列出平均工资大于1200元的部门和工作搭配组合

select deptno,job,avg(sal)

from emp

group by deptno,job

having avg(sal) > 1200

order by deptno;

-- 统计[人数小于4的]部门的平均工资。

select deptno,count(1),avg(sal)

from emp

group by deptno

having count(1) < 4;

-- 统计各部门的最高工资，排除最高工资小于3000的部门。

select deptno,max(sal)

from emp

group by deptno

having max(sal) < 3000;

99语法:交叉连接

多表查询引入：

实际开发中往往需要针对两张甚至更多张数据表进行操作，而这多张表之间需要使用主键和外键关联在一起，然后使用连接查询来查询多张表中满足要求的数据记录。

一条SQL语句查询多个表，得到一个结果，包含多个表的数据。效率高。在SQL99中，连接查询需要使用join关键字实现。

提供了多种连接查询的类型： cross natural using on

交叉连接（CROSS JOIN）是对两个或者多个表进行笛卡儿积操作，所谓笛卡儿积就是关系代数里的一个概念，表示两个表中的每一行数据任意组合的结果。

查询员工的编号，姓名，部门编号：

select * from emp;

select empno,ename,deptno from emp;

-- 查询员工的编号，姓名，部门编号,部门名称：

select * from emp; -- 14条记录

select * from dept; -- 4条记录

-- 多表查询 ：

-- 交叉连接：cross join

select *

from emp

cross join dept; -- 14*4 = 56条 笛卡尔乘积 ： 没有实际意义，有理论意义

select *

from emp

join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以

-- 自然连接：natural join

-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单

select *

from emp

natural join dept;

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;

-- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：

-- 解决： 内连接 - using子句：

select *

from emp e

inner join dept d -- inner可以不写

using(deptno) -- 这里不能写natural join了 ,这里是内连接

-- using缺点：关联的字段，必须是同名的

-- 解决： 内连接 - on子句：

select *

from emp e

inner join dept d

on (e.deptno = d.deptno);

-- 多表连接查询的类型： 1.交叉连接 cross join 2. 自然连接 natural join

-- 3. 内连接 - using子句 4.内连接 - on子句

-- 综合看：内连接 - on子句

select *

from emp e

inner join dept d

on (e.deptno = d.deptno)

where sal > 3500;

-- 条件：

-- 1.筛选条件 where having

-- 2.连接条件 on,using,natural

-- SQL99语法 ：筛选条件和连接条件是分开的

99语法:外连接查询

inner join - on子句： 显示的是所有匹配的信息

select *

from emp e

inner join dept d

on e.deptno = d.deptno;

select * from emp;

select * from dept;

-- 问题：

-- 1.40号部分没有员工，没有显示在查询结果中

-- 2.员工scott没有部门，没有显示在查询结果中

-- 外连接：除了显示匹配的数据之外，还可以显示不匹配的数据

-- 左外连接： left outer join -- 左面的那个表的信息，即使不匹配也可以查看出效果

select *

from emp e

left outer join dept d

on e.deptno = d.deptno;

-- 右外连接： right outer join -- 右面的那个表的信息，即使不匹配也可以查看出效果

select *

from emp e

right outer join dept d

on e.deptno = d.deptno;

-- 全外连接 full outer join -- 这个语法在mysql中不支持，在oracle中支持 -- 展示左，右表全部不匹配的数据

-- scott ，40号部门都可以看到

select *

from emp e

full outer join dept d

on e.deptno = d.deptno;

-- 解决mysql中不支持全外连接的问题：

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中支持）

-- outer可以省略不写

三连接查询

-- 查询员工的编号、姓名、薪水、部门编号、部门名称、薪水等级

select * from emp;

select * from dept;

select * from salgrade;

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

from emp e

right outer join dept d

on e.deptno = d.deptno

inner join salgrade s

on e.sal between s.losal and s.hisal

不相关子查询

单行子查询

-- 查询工资高于平均工资的雇员名字和工资。

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

多行子查询：

-- 【1】查询【部门20中职务同部门10的雇员一样的】雇员信息。

-- 查询雇员信息

select * from emp;

-- 查询部门20中的雇员信息

select * from emp where deptno = 20;-- CLERK,MANAGER,ANALYST

-- 部门10的雇员的职务：

select job from emp where deptno = 10; -- MANAGER,PRESIDENT,CLERK

-- 查询部门20中职务同部门10的雇员一样的雇员信息。

select * from emp

where deptno = 20

and job in (select job from emp where deptno = 10)

-- > Subquery returns more than 1 row

select * from emp

where deptno = 20

and job = any(select job from emp where deptno = 10)

-- 【2】查询工资比所有的"SALESMAN"都高的雇员的编号、名字和工资。

-- 查询雇员的编号、名字和工资

select empno,ename,sal from emp

-- "SALESMAN"的工资：

select sal from emp where job = 'SALESMAN'

-- 查询工资比所有的"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');

-- 【3】查询工资低于任意一个"CLERK"的工资的雇员信息。

-- 查询雇员信息

select * from emp;

-- 查询工资低于任意一个"CLERK"的工资的雇员信息

select *

from emp

where sal < any(select sal from emp where job = 'CLERK')

and job != 'CLERK'

-- 单行子查询：

select *

from emp

where sal < (select max(sal) from emp where job = 'CLERK')

and job != 'CLERK'

相关子查询

【1】不相关的子查询引入：

不相关的子查询：子查询可以独立运行，先运行子查询，再运行外查询。

相关子查询：子查询不可以独立运行，并且先运行外查询，再运行子查询

【2】不相关的子查询优缺点：

好处：简单 功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）

缺点：稍难理解

【3】sql展示：

-- 【1】查询最高工资的员工 （不相关子查询）

select * from emp where sal = (select max(sal) from emp)

-- 【2】查询本部门最高工资的员工 （相关子查询）

-- 方法1：通过不相关子查询实现：

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)

-- 缺点：语句比较多，具体到底有多少个部分未知

-- 方法2： 相关子查询

select * from emp e where sal = (select max(sal) from emp where deptno = e.deptno) order by deptno

-- 【3】查询工资高于其所在岗位的平均工资的那些员工 （相关子查询）

-- 不相关子查询：

select * from emp where job = 'CLERK' and sal >= (select avg(sal) from emp where job = 'CLERK')

union ......

-- 相关子查询：

select * from emp e where sal >= (select avg(sal) from emp e2 where e2.job = e.job)