SQL相关语句总结

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）采纳为国际标准。

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：授予用户某种权限
事务控制语言（Transaction Control Language，TCL）：TCL用于数据库的事务管理。其主要包括：
- START TRANSACTION：开启事务
- ROLLBACK：回滚事务

登录退出相关

1 2	mysql -hlocalhost -uroot -p # 输入密码

退出：quit;

数据库相关

创建数据库：create database zgy_1;
查看所有数据库：show databases;
进入使用数据库：use zgy_1;
删除数据库：drop database test;

表相关

创建表删除表（其中包含了表级约束和列级约束相关操作，外键操作导致删除很麻烦，可以参考下级联操作）：

use mysql_learn;
create table student_4(
    sno int auto_increment,
    sname varchar(5),
    age int,
    sex char(1) default '男',
    classno int,
    birthday date,
    email varchar(20) not null,
    constraint pk_sno primary key (sno),
    constraint ck_age check(age between 10 and 20),
    constraint fk_classno foreign key (classno) references class(classno)
    # 再外键定义之后加上on update cascade on delete cascade级联操作 删除外键的时候子表也会被删除 更新也是
    # 如果是设置级联操作 on update set null on delete set null 对应操作就会制空
);
# 列级别约束添加
alter table student_4 add constraint uq_birthday unique(birthday);  

# 约束的删除
alter table student_4 drop index uq_birthday;
alter table student_4 drop CHECK ck_age;
alter table student_4 drop foreign key fk_classno;

alter table student_4 modify email varchar(20) null;
# 假设删除主键自增
alter table student_4 modify sno int;
# 删除default约束
alter table student_4 modify sex char(1);

# 查看表相关信息
desc student_4;

# 删除表
drop student_4;

修改表相关

# 向表新增数据
insert into class values(1,'一班');
insert into student_4 (sno,sname,age,sex,classno,email) values(001,'zgy',12,'男',1,'1728380670@qq.com');

# 查看表数据
select * from class;
select * from student_4;

# 修改表数据
update student_4 set age = 19 where sno = 1;

# 删除数据
delete from student_4 where sno = 1;

# 修改表
alter table student_4 modify sno int; # modify修改是列的类型的定义，但是不会改变列的名字
alter table student_4 change sno sno1 int; # change修改列名和列的类型的定义

# 新增一列
alter table student_4  parent varchar(20) after name;

# 删除一列
alter table student_4 drop age;

# 删除表
drop table student_4;

单表查询相关

-- where 子句 + 关系运算符
select * from emp where deptno = 10;

-- where 子句 + 逻辑运算符：and 
select * from emp where sal > 1500 and sal < 3000;  -- (1500,3000)
select * from emp where sal > 1500 && sal < 3000; 
select * from emp where sal between 1500 and 3000; -- [1500,3000]

-- where 子句 + 逻辑运算符：or
select * from emp where deptno = 10 or deptno = 20;
select * from emp where deptno = 10 || deptno = 20;
select * from emp where deptno in (10,20);

-- where子句 + 模糊查询：
"%" 百分号通配符: 表示任何字符出现任意次数 (可以是0次)。
"_" 下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符。

-- 查询名字中带A的员工  -- %代表任意多个字符 0,1,2，.....
select * from emp where ename like '%A%' ;
-- -查询第三个字符是A
select * from emp where ename like '__A%' ;

-- 关于null的判断：
select * from emp where comm is null;
select * from emp where comm is not null;
-- 小括号的使用  ：因为不同的运算符的优先级别不同，加括号为了可读性
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500; -- 先and再or  and > or

多表查询相关

交叉连接，自然连接，内连接：

# 交叉连接(笛卡尔积) 
select * 
from emp
cross join dept; -- cross 可以省略不写，mysql中可以，oracle中不可以

# 自然连接：natural join 
-- 优点：自动匹配所有的同名列 ,同名列只展示一次 ，简单
select * 
from emp
natural join dept;

-- 缺点：表名太长
-- 解决：表起别名
select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno
from emp as e -- as 可以省略
natural join dept d;
-- 自然连接 natural join 缺点：自动匹配表中所有的同名列，但是有时候我们希望只匹配部分同名列：
# 解决： 内连接 - using子句：
select * 
from emp e
inner join dept d -- inner可以不写
using(deptno) -- 这里不能写natural join了 ,这里是内连接

-- using缺点：关联的字段，必须是同名的 
-- 解决：  
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;

外连接：

-- inner join - on子句： 显示的是所有匹配的信息 但是会省略掉连接之后左表没有的数据 也会省略掉右表没有的数据
select * 
from emp e
inner join dept d
on e.deptno = d.deptno;

-- 左外连接： left outer join   -- 会保留连接之后左面的那个表咩有的信息，即使不匹配也可以查看出效果 这个左右其实是先出现的表就是左边 不是left 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;

三表连接查询：

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

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 * from emp;
select e1.empno 员工编号,e1.ename 员工姓名,e1.mgr 领导编号,e2.ename 员工领导姓名
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

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

-- 【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)
# 好处：简单   功能强大（一些使用不相关子查询不能实现或者实现繁琐的子查询，可以使用相关子查询实现）
# 缺点：稍难理解

不相关子查询：子查询可以独立运行，称为不相关子查询。先执行子查询，再执行外查询；

# 单行子查询
-- 单行子查询：
-- 查询工资高于平均工资的雇员名字和工资。
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的雇员一样的】雇员信息。
-- 查询部门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)

数据库对象相关

事务相关：

-- 创建账户表：
create table account(
        id int primary key auto_increment,
        uname varchar(10) not null,
        balance double
);
-- 查看账户表：
select * from account;
-- 在表中插入数据：
insert into account values (null,'丽丽',2000),(null,'小刚',2000);
-- 丽丽给小刚 转200元：
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 默认一个DML语句是一个事务，所以上面的操作执行了2个事务。
update account set balance = balance - 200 where id = 1;
update account set balance = balance2 + 200 where id = 2;
-- 必须让上面的两个操作控制在一个事务中：
-- 手动开启事务：
start transaction;
update account set balance = balance - 200 where id = 1;
update account set balance = balance + 200 where id = 2;
-- 手动回滚：刚才执行的操作全部取消：
rollback;
-- 手动提交：
commit;
-- 在回滚和提交之前，数据库中的数据都是操作的缓存中的数据，而不是数据库的真实数据
-- 回滚和提交之后就真的改变了数据库真是数据了

事务并发问题：
- 脏读（Dirty read）其实就是读到了错的数据就是事务没有回滚之前读到错误的数据虽然最后数据库数据没错但是读取操作是错的
  
  当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- 不可重复读（ Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
不可重复度和幻读区别： 不可重复读的重点是修改，幻读的重点在于新增或者删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

视图相关

视图的概念：

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

创建视图：

-- 创建/替换单表视图：
create or replace view myview01
as
select empno,ename,job,deptno 
from emp
where deptno = 20
with check option -- 加入这个语句检查错误
-- 查看视图：
select * from myview01;
-- 在视图中插入数据：插入是可以插入的也会插入到对应表中 但是这里面插入例如depno！=20的数据 就报错了
insert into myview01 (empno,ename,job,deptno) values (9999,'lili','CLERK',20);
insert into myview01 (empno,ename,job,deptno) values (8888,'nana','CLERK',30);
insert into myview01 (empno,ename,job,deptno) values (7777,'feifei','CLERK',30); 
-- > 1369 - CHECK OPTION failed 'mytestdb.myview01'
-- 创建/替换多表视图：
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 ;
select * from myview02;
-- 创建统计视图：
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)
group by e.deptno ;
select * from myview03;
-- 创建基于视图的视图：
create or replace view myview04
as
select * from myview03 where deptno = 20;
select * from myview04;

存储过程相关

什么是存储过程(Stored Procedure)：其实类似java中的函数和方法，通过前面章节的学习，我们已经知道SQL是一种非常便利的语言。从数据库抽取数据，或者对特定的数据集中更新时，都能通过简洁直观的代码实现。但是这个所谓的“简洁”也是有限制，SQL基本是一个命令实现一个处理，是所谓的非程序语言。在不能编写流程的情况下，所有的处理只能通过一个个命令来实现。当然，通过使用连接及子查询，即使使用SQL的单一命令也能实现一些高级的处理，但是，其局限性是显而易见的。例如，在SQL中就很难实现针对不同条件进行不同的处理以及循环等功能。这个时候就出现了存储过程这个概念，简单地说，存储过程就是数据库中保存(Stored)的一系列SQL命令（Procedure）的集合。也可以将其看作相互之间有关系的SQL命令组织在一起形成的一个小程序。
存储过程的优点

提高执行性能。存储过程执行效率之所高，在于普通的SQL语句，每次都会对语法分析，编译，执行，而存储过程只是在第一次执行语法分析，编译，执行，以后都是对结果进行调用。
可减轻网络负担。使用存储过程，复杂的数据库操作也可以在数据库服务器中完成。只需要从客户端(或应用程序)传递给数据库必要的参数就行，比起需要多次传递SQL命令本身，这大大减轻了网络负担。
可将数据库的处理黑匣子化。应用程序中完全不用考虑存储过程的内部详细处理，只需要知道调用哪个存储过程就可以了

存储过程相关代码：

-- 定义一个没有返回值 存储过程
-- 实现：模糊查询操作：
select * from emp where ename like '%A%';
create procedure mypro01(name varchar(10))
begin
        if name is null or name = "" then
                select * from emp;
        else
    select * from emp where ename like concat('%',name,'%');
        end if;  
end;
-- 删除存储过程：
drop procedure mypro01;
-- 调用存储过程：
call mypro01(null);
call mypro01('R');
-- 定义一个  有返回值的存储过程：
-- 实现：模糊查询操作：
-- in 参数前面的in可以省略不写
-- found_rows()mysql中定义的一个函数，作用返回查询结果的条数
create procedure mypro02(in name varchar(10),out num int(3))
begin
        if name is null or name = "" then
                select * from emp;
        else
    select * from emp where ename like concat('%',name,'%');
        end if;  
        select found_rows() into num;
end;
-- -- 调用存储过程：
call mypro02(null,@num);
select @num;
call mypro02('R',@aaa);
select @aaa;

函数相关

单行函数：

字符串函数：

单行函数的使用：

-- 单行函数包含：
-- 1.字符串函数
select ename,length(ename),substring(ename,2,3) from emp;
-- substring字符串截取，2:从字符下标为2开始，3：截取长度3    （下标从1开始）
-- 2.数值函数
select abs(-5),ceil(5.3),floor(5.9),round(3.14) from dual; -- dual实际就是一个伪表  就只有一行
select abs(-5) 绝对值,ceil(5.3) 向上取整,floor(5.9) 向下取整,round(3.14) 四舍五入;  -- 如果没有where条件的话，from dual可以省略不写
select ceil(sal) from emp;
select 10/3,10%3,mod(10,3) ;
-- 3.日期与时间函数 
select * from emp;
select curdate(),curtime() ; -- curdate()年月日 curtime()时分秒
select now(),sysdate(),sleep(3),now(),sysdate() from dual; -- now(),sysdate() 年月日时分秒
insert into emp values (9999,'lili','SALASMAN',7698,now(),1000,null,30);
-- now()可以表示年月日时分秒，但是插入数据的时候还是要参照表的结构的 就是会自动帮你转换一下
-- 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，那么取值为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;
from emp;
-- 5.JSON函数  
-- 6.其他函数
select database(),user(),version() from dual;

多行函数：

-- 多行函数：
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
select * from emp;
-- 多行函数自动忽略null值
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()针对所有类型   sum(),avg() 只针对数值型类型有效 无效的时候不会报错就是会返回0
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;
-- 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,having:

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