数据库学习笔记-2-CSDN博客

本文链接：https://blog.csdn.net/weixin_45938857/article/details/114986395

数据库学习笔记-2

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

一、查询结果集的去重

使用关键字distinct（只能出现在所有字段的最前方）

select distinct job from emp;

二、连接查询

不加限制对两张表进行查询会得到对应字段的笛卡尔积

select ename,dname from emp,dept;
/*使用表的别名（注意from子句别名不需要as）*/
select e.ename,d.dname from emp e,dept d;

其中ename有14条记录，dname有4条记录，就会获得14*4=56条记录

使用别名可以提高查询效率，否则会在两张表中均查询有无指定字段

使用条件进行过滤可以避免笛卡尔积，但底层查询仍然是56次，只是按条件选择展示有效记录

/*SQL92的写法，较少使用*/
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
/*SQL99（inner可省略）*/
select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

SQL99优于SQL92在于将表连接条件（on）与后续过滤（where）分开，结构更清晰

（1）内连接

1.等值连接

条件是等量关系

select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

2.非等值连接

连接条件中的关系是非等量关系

select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

3.自连接

一张表看做两张表，自己连接自己（原理同上）

select a.ename,b.ename from emp a join emp b on a.mgr = b.empno;

（2）外连接

内连接：凡是A表和B表能够匹配上的记录查询出来，两张表没有主副之分，两张表是平等的

外连接：AB两张表中有一张表是主表，一张表是副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出NULL与之匹配

左外连接（左连接）：表示左边的这张表是主表
右外连接（右连接）：表示右边的这张表是主表

外连接的主表数据无论如何都会显示，副表记录未匹配显示null

/*左外连接（outer可省略）*/
select a.ename,b.ename from emp a left outer join emp b on a.mgr = b.empno;
/*替换为右外连接*/
select a.ename,b.ename from emp b right join emp a on a.mgr = b.empno;

select d.* from emp e right join dept d on e.deptno = d.deptno where e.empno is null;

三、多表查询

A join B join C on ...
/*A表和B表先进行表连接，连接之后A表继续和C表进行连接*/

select 
	e.ename '员工',d.dname,s.grade,e1.ename '领导'
from 
	emp e 
join 
	dept d 
on 
	e.deptno = d.deptno 
join 
	salgrade s 
on 
	e.sal between s.losal and s.hisal 
left join 
	emp e1 
on 
	e.mgr = e1.empno;;

四、子查询

1.Where子句

/*错误*/
select avg(sal) from emp;
/*正确*/
select * from emp where sal > (select avg(sal) from emp);

2.from子句

例：找出每个部门平均薪水的等级

设计思路：

select deptno,avg(sal) as avgsal from emp group by deptno;

得到一张deptno,avg(sal)表，此时不妨将其视为真实存在的表t，让t表和salgrade表连接

select 
	t.*,s.grade
from
	t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

最后把t的来源添加进去（t不是一张真实存在的表，只是中间结果）

select 
	t.*,s.grade
from
	(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
	salgrade s
on
	t.avgsal between s.losal and s.hisal;

3.select子句

select 
	e.ename,d.dname
from
	emp e
join
	dept d
on
	e.deptno = d.deptno;
/*可改写为*/
select 
	e.ename,(select d.dname from dept d where e.deptno = d.deptno) as dname 
from 
	emp e;

五、union

将查询结果集相加，适用于不相干的表的查询结果的拼接（列的数量一致）

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';
/*等同于*/
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN';

六、limit

mysql特有，取结果集中的部分数据

limit startIndex, length

/*取前5个*/
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;

limit是sql语句最后执行的一个环节

七、创建表

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

数据类型：
char 定长字符串(String)
varchar 可变长字符串(StringBuffer/StringBuilder)
BLOB 二进制大对象（存储图片、视频等流媒体信息）
CLOB 字符大对象（存储较大文本，比如，可以存储4G的字符串。）

表名在数据库当中一般建议以：t_或者tbl_开始

create table t_student(
	no bigint,
	name varchar(255),
	sex char(1),
	classno varchar(255),
	birth char(10)
);

八、插入数据（增）

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

删除表：

drop table if exists t_student;

设置默认值（插入语句未声明该字段值则赋默认值，不指定则为NULL）：

create table t_student(
	sex char(1) default 1,
);

字段可以省略不写，但是后面的value对数量和顺序都要求匹配

insert into t_student values(1,'jack','0','gaosan2ban','1986-10-23');

一次插入多行数据：

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

将查询结果当做表创建：

create table 表名 as select语句;

create table emp1 as select * from emp;

将查询结果插入到一张表中（表结构需匹配）：

insert into dept1 select * from dept;

九、删除数据（删）

注意：若没有条件则全部删除

delete from 表名 where 条件;

整张表删除（截断，不可回滚，永久丢失）：

truncate table 表名;

十、修改数据（改）

注意：若没有条件则整张表数据全部更新

update 表名 set 字段名1=值1,字段名2=值2... where 条件;
update dept1 set loc = 'SHANGHAI', dname = 'RENSHIBU' where deptno = 10;

十一、约束

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

非空约束(not null)：约束的字段不能为NULL
唯一约束(unique)：约束的字段不能重复
主键约束(primary key,PK)：约束的字段既不能为NULL，也不能重复
外键约束(foreign key,FK)
检查约束(check)：Oracle数据库有check约束，但是mysql没有。

非空约束(not null)：

create table t_user(
	username varchar(255) not null,
);