SQL

 

 

 

 

 

 

SQL

MySQL

安装以及破解navicat客户端

安装mysql

常用数据类型

1. 整数int
2. 小数decimal
3. 字符串varchar
4. 日期时间

约束

1. 主键    int类型、无符号、自动递增、唯一的标识一条记录
2. 非空
3. 唯一    此字段的值不允许重复
4. 默认值
5. 外键    维护两个表之间的关联关系

数据库的备份与恢复

1. 备份    鼠标右键点击某个数据库,点击转储SQL文件→结构和数据
2. 恢复    新建一个库→运行sql文件(就是备份好的sql文件)

mysql的语法规范

1. 不区分大小写,单建议关键字大写、表明、列名小写(不好意思,我不接受建议,个人习惯全小写)
2. 每条命令最好用分号结尾
3. 每条命令根据需要,可以进行缩进或换行
4. 注释
   1. 单行注释    #(--)注释文字
   2. 多行注释    /*注释文字

SQL语句

显示数据库及表、版本

show databases;    显示所有的数据库列表

show tables;    显示所有表

show tables from sys;    从具体的某个数据库显示所有的表

select database()    如果使用了use,查询的数据库就是use下的数据库

select version()    查看当前mysql的版本

创建表(create的用法)

auto_increment:    自动增长

primary key:    主键

decimal(5,2):    整数位不能超过3，小数保留两位小数

create table student(
　　id int unsigned primary key auto_increment,
　　age int unsigned,
　　name varchar(10),
　　heght decimal(5,2)
)

删除表(drop的用法)

drop table if exists student    如果存在student表就删除,不存在就不报错

drop table student    如果student存在就删除,不存在就报错

新增数据(insert的用法)

insert into student values(1,18,"吴鹏",172.45)

insert into student value(default,18,"吴鹏",172.45)    如果id设置了自增,可以使用default进行填充

insert into student(name,age) values("吴鹏",18)    给指定字段设置数据

删除修改数据(delete、update的用法)

update student set name='吴鹏' where id=1    修改字段name,条件id=1    需要修改多个字段,字段之间用逗号隔开

delete from student    会删除所有的数据

delete from student where id=2    删除id=2的整条数据 

简单查询(包括条件查询)

实际工作过程中,不要使用通配符*

select * from student    查询所有字段

select name,age from student    查询姓名跟年龄

select distinct sex from student    查询出学生中重复性别

select * from student where age<18    查询年龄小于18的学生

select * from student where age!=18    查询年龄不为18的学生    != 也可以用<>表示

select * from student where age<18 and sex='女'    查询年两小于18的女学生

select * from student where age=18 or sex='女'    查询年龄18的学生或者性别为女的学生 

去重查询

select distinct deptno from dept    查询部门编号(去重)

字符串连接查询

select concat(last_name,first_name) as 姓名 from employee;    查询员工的姓跟名连接并显示为姓名的数据

模糊查询

select * from student where name like '孙%'    查询姓孙的学生

select * from student where name like '孙_'    查询姓孙的学生并且名只有一个字的

select * from student where name like '%鹏'    查询名鹏的学生

select * from student where name like '%鹏%'    查询含有鹏的学生

范围查询

select * from student where name in('吴鹏','吴磊')    查询姓名包含吴鹏或者吴磊的学生

select * from student where age between 10 and 20    查询年龄在10-20之间的学生

空判断

select * from student where age is null    查询学生中年龄为空的学生

select * from student wehre age=' '    查询学生中年龄非空但没填写的学生

select * from student where not age is null    查询学生中都填写了年龄的学生

排序

将行数据按照列1进行排序,如果某些行列1的值相同时,则按照列2排序,以此类推,多个列排序之间用逗号隔开

order by子句可以支持单个字段、多个字段、表达式顿号函数、别名

order by子句一般是放在查询语句的最后main,limit子句除外

select * from student order by age desc    按照年龄降序排列

聚合函数

select count(name) from student    查询学生总数

select max(age) from student where sex='女'    查询女生中最大年龄

select min(age) from student where sex='女'    查询女生中最小年龄

select sun(age) from student    查询全班学生的年龄和

select avg(age) from student    查询全班学生年龄的平均年龄

聚合函数忽略null值

聚合函数可以跟distinct组合使用

select sum(distinct salary) from employee;

count的详细介绍

效率:

1. myisam存储引擎下,count(*)的效率高
2. innodb存储引擎下,count(*)和count(1)的效率差不多,比count(字段)要高一些

所以一般使用count(*)统计行数

和聚合函数一同查询的字段需要使用group by进行分组

分组查询

按照字段分组,表示此字段相同的数据会被放到一个组中,分组后,分组的一句会显示在结果集中,其他列不会显示在结果集中。

如果按照多个字段分组,会先按照第一个字段分组,然后根据分组后的数据,按照第二个字段分组。

select sex,count(*) from student group by sex    按性别分组,查询学生人数

select sex,count(*) from student group by sex having sex='男'    查询男生总人数

对比where与having

where是对from后面指定的表进行数据筛选,属于对原始数据的筛选

having是对group by的结果进行筛选 

分页

select * from student limit 0,3    查询前三行学生信息    0代表索引,表示从第一条数据开始,3表示获取3条数据

select * from student limit 3    查询前三行学生信息    如果只写一个值,那么去掉的那一个值,默认是前面的索引值,也就是0

连接查询

等值连接查询(取的是两张表的交集)

方式一:

　　select * from student,teacher where student.sno=teacher.sno;

方式二(内连接,该方法不会产生笛卡尔积,不会产生临时表性能高)

　　select * from student inner join teacher on student.sno=teacher.sno;

　　select * from course as c1 inner join sc as s1 on c1.CID=s1.CID inner join teacher t1 on c1.TID=t1.TID where c1.Cname='语文';    使用内连查询多张表

左连接查询(查询的两张表取左边的表,对于右表中不存在的数据使用null)    表示join前面的表全部显示出来

　　select * from student left join score on student.studentno=score.studentno;

右连接查询(查询的两张表取右边的表,对于左表中不存在的数据使用null)    表示join后面的表全部显示出来

　　select * from scores right join courses on scores.courseNo=courses.courseNo;

只要在需求里面有包含或者全部的字眼就要考虑,就要考虑使用左连接和右连接

自关联查询

多用于一张表内存有上下级关系的数据,比如:省市县存放于同一张表里,就需要用到自关联查询

select * from area a1,area a2 where a1.id=a2.pid and a1.title='南京';    查找市内所有区县

select * from area a1,area a2,area a3 where a1.id=a2.pid and a1.title='南京' and a2.id=a3.pid ;    查找省内所有区县

标量子查询

select * from scores wehre studentNo=(select studentNo from students where name='王昭君')    查询王昭君的成绩,要求显示成绩

需要注意的是,在使用子查询的过程中,子句中查询的字段如果是多个只能使用in,如果是单个可以使用逻辑判断,并且在使用子查询的过程中查询的条件列要与子句中查询的列要一致。

列级子查询

select * from score wehre studentNo in('002','006')    在查询过程中条件有两个值的时候就不能使用=,就需要使用in

行子查询(用的不多)

select * from students where(sex,age)=(select sex,age from students where sex='男' order by age desc limit 1)    查询男生中年龄最大的学生信息

在使用行子查询的过程中,条件语句只能是一条数据.

表级子查询

select * from scores s inner join (select * from courses where name in('数据库','系统测试')) c on s.courseNo=c.courseNo;    查询数据库和系统测试的课程成绩

数据分表

　　对于多个重复型的数据,需要对表进行分割,将原先包括重复的数据已编号的形式存入另外一张表中。

 函数

select concat(123,'abc')    拼接函数

select length('abc')    查找字符个数

select left('abc',2)    从左边截取2个,也就是ab

select left('abc',2)    从右边截取2个,也就是bc

select substr("春天过去夏天过来",7)    表示从7开始截取

select substr("春天过去夏天过来",1,3)    表示从1截取3个字符

select instr("春天过去夏天过来","过来")    表示返回子串第一次出现的索引,如果找不到就返回0

select trim("a" from "asdfaaasda");    去除指定的前后字母

select trim("    asdfaaasda    ");    去除前后空格

select substring('abc',1,1)    表示从第一个开始截取一个,也就是a   

select lpad("abcd",10,"*") from student    第一个参数表示操作字符串,第二个参数表示位数,填充几位,第三个参数表示填充文本

select replace("你是头猪","猪","蠢猪") from student    替换,将猪换成蠢猪

select truncate(1.667,1)    表示截取1位小数(仅用与数字类型)

upper()    表示字符串全部大写

lower()    表示字符串全部小写

round(n,d)    表示四舍五入,n表示原数,d表示小数位置

ceil()    向上取整,返回>=该参数的最小整数

floor()    向下取整,返回<=该参数的最大整数

PI()     表示圆周率

rand()    表示随机数

pow(x,y)    表示x的y次幂

 日期函数

select current_date()    当前日期

select current_time()    当前时间

select now()    当前日期时间

select date_format(date,format)    将日期转换为字符,参数format可选值如下:

1. %Y    获取年,返回完整年份
2. %y    获取年,返回简写年份
3. %m    获取月,返回月份
4. %d    获取日,返回天值
5. %H    获取时,返回24进制的小时数
6. %h    获取时,返回12进制的小时数
7. %i    获取分,返回分钟数
8. %s    获取秒,返回秒数

select str_to_date("1992-09-21","%Y-%m-%d");    将字符通过指定的格式转换成日期

select datadiff("2017-10-1","2017-9-29");    返回两个日期间的天数

流程控制函数

if流程控制函数 

select last_name,commission_pct,if(commission_pct is null,'没奖金','有奖金')    if流程控制函数,如果第一个参数条件符合表示没奖金,不符合表示有奖金

case结构的流程控制函数

要求查询员工的工资,要求部门号为30,显示工资的1.1倍,部门号为40,显示工资的1.2倍..其他部门原工资

select salary 原始工资,department_id case department_id when 30 then salary*1.1 when 40 then salary*1.2 else salary end as 新工资 from employees;

要求查询员工的工资情况,要求:如果工资>2000,显示A级别,如果工资>1500,显示B级别,否则显示级别

select salary case when salary>2000 then "A级别" when salary>1500 then "B级别" else "C级别" end as 工资级别 from employee;

视图

create view v_students as select * from student    创建一个视图叫v_students,将student查询的数据存放到视图中

使用show tables查看表,也能将所有的视图也列出来

drop view v_students    删除视图

select * from v_students    查询v_students视图表

 事务

输入begin;对一条数据进行修改,所有操作都成功,在输入commit;

输入begin;对一条数据进行修改,任何一步失败,在输入rollback;

 索引(在大量的数据中使用索引查询数据会很快)

show index from student    查看索引

在创建表的时候建立索引(使用key)

create table student(
　　id int unsigned primary key auto_increment,
　　age int unsigned,
　　name varchar(10) unique,
　　heght decimal(5,2),

key(age)

)

在已存在的表中创建索引

create index i_name on test(name(10))    表示在test表中的name字段创建一个名叫i_name的索引

查看数据库日志

1. show variables like 'log%'    是否启用了日志
2. show master status    怎样知道当前的日志
3. show master logs    显示二进制日志的数目

ORACLE

Oracle的数据类型

1. number类型,数字类型,最大长度38位
   1. 经常被定义成number(3),number(6,2)
   2. number(6,2) 表示的是整数位最大4位,小数位2位(8888.88)
2. varchar2类型,表示变长字符串类型(oracle独有的)
   1. 定义格式:varchar2(x),x表示需要定义的长度
   2. varchar2最大长度4000字节
   3. varchar2(20) 表示字符串可以存储20个字节,但是根据数据实际的长度,占用的空间是可以变换的,但最多不能超过20字节
3. char类型,定长字符串
4. 定义格式 char(x) x表示需要定义的长度
   1. char最大长度2000字节
   2. char(20),表示固定占用20个字节
5. long类型
   1. 就是varchar2的加长版,存储变长字符串,最多存储2G,但是long类型有一些限制,每一个表只能有一个long类型字段,并且不能作为主键
   2. 也不能建立索引,也不能出现在查询条件中,所以一般不会使用long
6. clob类型 存储空间巨大,最多可以达到4G
7. Date 日期类型
1. 用来保存日期和时间
2. 表示日期范围,公元前4712年1月1日至公元9999年12月31日

Oracle的常规四种操作

1. 数据库的常用四种操作(增删改属于DML操作,与实务相关,必须经过事务处理才能将数据真正的保存到数据库中)
   1. insert
   2. update
   3. delete
   4. select
2. 事务控制,专门用来维护数据的一致性
   1. 提交 commit
   2. 回滚 rollback
   3. 事务具备4个特性
      1. 原子性(Atomicity) 指事务中包含的操作,要么全部执行,要么全部不做
      2. 一致性(consistency) 指事务中包含的操作必须同时成功或者同时失败.
      3. 隔离性(isolation) 指数据库允许多个并发事务同时对其中的数据进行读写和修改的能力,隔离性可以防止事务并发执行时,由于操作命令交叉执行而导致的数据不一致状态
      4. 持久性(durability) 指当事务结束后,他对数据库中的影响是永久的,及时遇到系统故障的情况下,数据也不会丢失

Oracle的sql语法

创建表

需要注意的是,如果表名(字段)是小写,那么在查询表的时候需要添加双号.否则展现的是大写
create table "classroom"(
id number(4),
name varchar2(255),
age number(4),
adress varchar2(255)
)

default关键字(用于设置字段的默认值)和not null非空约束(用于确保插入数据时,某些字段不能为空值),两种操作都需要在建表的时候设置
create table classroom(
id number(4),
name varchar2(20) not null,
sex char(4) default 'M',
age number(20)
);

约束(constraint)
约束的全称是约束条件,是在数据表上加上数据校验规则,可以保证数据的完整性,当我们添加或者修改数据时,就必须要符合这些规则.
常见的页数

1. 非空约束 not null 插入或者修改数据时,不能有null值
2. 唯一性约束 unique 添加唯一性约束的字段,不能有重复值,但是可以有null值
3. 主键约束 primary key(包含了1跟2) 添加主键约束的字段,不能有重复值,也不能有null值
4. 外键约束 forign key 
5. 检查约束 check 在插入或者修改数据时,参数值要满足check中的约束条件

create table bestemp(
id number(4) primary key,
name varchar2(20) not null,
sex varchar2(3) check(sex='男' or sex='女'),
sal number(7,2),
phone varchar(13) unique
)

修改表名称

前面的表示原表名,后面的表示新表名
rename "classroom" to "classroom2"

修改表结构,追加字段
alter table "classroom" add(birthday varchar2(10))

删除

删除表中字段
alter table "classroom" drop(birthday)

删除表(删除了整张表包括表结构)
drop table beststudent;

删除数据,根据条件删除数据
delete from "classroom" where id=2;

truncate 删除表中所有数据,表结构还存在.不支持事务
truncate table "classroom";

查询

查看当前系统时间
select sysdate from dual;

查看整张表
select * from "classroom";
查看表中指定字段
select id,name from "classroom";

查看表中的数据
select * from "classroom";

dual 伪表,当我们查询的内容与任何一张表都没有关系时,可以使用dual
查询系统日期
select sysdate from dual;

length()用于返回指定内容的长度,查询字符串的长度
select length('sdfsd') from dual;

数据库字符串拼接

拼接两个字段
select concat(firstname,lastname) as 新名字 from classroom;

拼接两个或者多个字段
select id||name||age as 新名字 from classroom;

大小写转换

大写
select upper('helloworld') from dual;

小写
select lower('HELLOWORLD') from dual;

单词的首字母大写,单词之间需要有空格
select initcap('hello world') from dual;

去除空格

去除左右空格
select trim(' acv ') from dual;

去除左空格
select ltrim(' abc ') from dual;

去除右空格
select rtrim(' acx ') from dual;

补位函数

lpad() 左补位 如果name的长度没有10,那么使用字符串a进行补位
select lpad(name,10,'a') from classroom;

rpad() 右补位
select rpad(name,10,'a') from classroom;

截取字符串

substr(a,b,c) 截取字符串    select substr('thinking in java',3,6) from dual;
从指定位置截取字符串,数据中的下标从1开始

1. a表示截取的字符串
2. b表示截取开始索引(包括索引位置)
3. c表示截取的个数

如果第三个参数不设置,或者设置超过了字符串的长度,就直接截取到末尾
select substr('thinking in java',3,9) from dual;

如果第二个参数是负数,就是从倒数第几个开始截取
select substr('thinking in java',-3,9) from dual;

instr(str1,str2,m,n) 查看指定字符串在当前字符串中的位置

1. str1 表示需要操作的字符串
2. str2 表示在str1中寻找的字符
3. m 表示查找str2的起始位置
4. n 表示第几次出现

select instr('thinking in java','in',4,2) from dual;

round、trunc、ceil

round()四舍五入 
round的第二个参数,表示保留几位,如果不写保留整数,如果是负数表示保留整数位
select round(45.567,2) from dual

trunc()和round()很像,但是没有四舍五入的功能
select trunc(45.564,2) from dual

ceil() 表示向上取整和floor() 表示向下取整
select ceil(45.678) from dual;
select floor(45.654) from dual;

时间与日期

查询系统日期
select sysdate from dual;

查询系统时间(精确到毫秒)
select systimestamp from dual;

to_date() 将字符串转成日期类型
select to_date('2019-09-23','YYYY-MM-DD') from dual;

to_char() 将日期转成字符串
select to_char(sysdate,'YYYY-MM-DD') from dual;

将日期转成的字符串带有中文,需要使用双引号
select to_char(sysdate,'YYYY"年"-MM"月"-DD"日"') from dual;

日期做减法运算
select sysdate-to_date('2017-07-07','YYYY-MM-DD') from dual;

last_day() 返回指定日期,所在月份的最后一天
select last_day(sysdate) from dual;

add_months(date,n) 返回指定日期加上N个月后的日期
select add_months(sysdate,2) from dual;

next_day() 查看下一个周几是几月几日 数字7表示周六,数字1表示周天
select next_day(sysdate,2) from dual;

选择出最大值与最小值

least() 从多个参数选出最小值
select least(10,20,30) from dual;

greatest() 从多个参数选出最大值
select greatest(10,20,30) from dual;

null的用法
null就是没有值,不存在的意思,任何数据类型都可以为null,判断某个字段是否为null的时候,不能用等号,null和任何数字运算,结果还是null
显示插入null值
insert into student2 values(1001,'初音未来',null);

隐式插入null值
insert into student2(id,name) values (1002,'森之妖精');

判断字段值是否为null的数据
select * from student where phone is null;

判断字段值不是null数据
select * from student where phone is not null;

null替换
nvl(参数1,参数2) 如果参数1不是null,就直接使用参数1;如果参数1是null,就是用参数2,2个参数的类型要相同
查看smith的薪资加上奖金是多少钱
select ename,job,sal+nvl(comm,0) from emp where ename='SMITH'

查看allen的薪资加上奖金,是多少钱
select ename,job,sal+nvl(comm,0) from emp where empid=7499

nvl2(参数1,参数2,参数3) 
如果参数1不是null,就使用参数2,如果参数1是null,就使用参数3
查询emp表中,看看哪些员工有奖金,哪些员工没有奖金
select ename,job,comm,nvl2(comm,'有奖金','没有奖金') from emp;

别名 as
查看平均工资
select avg(sal) as avgsal from emp;

查看工资总和
select sum(sal) "SumSal" from emp;

使用< > >= <= != <> =

使用and和or
select * from emp where sal>1000 and job='办事员';
select * from emp where sal>1000 or job='办事员';

查询薪资大于1000,并且工作是salesman或者是办事员的员工数据
select * from emp where sal>1000 and (job ='SALESMAN' or job ='办事员');

like通配符
模糊查询使用,支持两个通配符

1. % 表示0-多个字符
2. _ 表示一个字符

查看员工姓名以A开头的员工信息
select * from emp where ename like'A%';

查询名字总第二个字母是A的员工信息
select * from emp where ename like'_A';

查看名字是N结尾的员工信息
select * from emp where ename like'%N';

查看名字中包含A的员工信息
select * from emp where ename like'%A%';

in和not in
查看工作是办事员,salesman,分析师的员工信息
select * from emp where job in('办事员','SALESMAN','分析师');

查看工作不是办事员,salesman,分析师的员工信息
select * from emp where job not in('办事员','SALESMAN','分析师');

between...and...
查询员工薪资1500-3000之间
select * from emp where sal between 1500 and 3000;

any()大于最小的就可以 和 all() 大于最大的才可以
查询薪资大于2000,3500,4000的员工
select * from emp where sal>ANY(2000,3500,4000)
select * from emp where sal>ALL(2000,3500,4000)

在查询条件中使用函数或者运算
select * from emp where ename=upper('king');
select * from emp where sal*12>30000;

去除重复
查看有哪些工作职位
select distinct job from emp;

如果对多列进行去除重复,那么只能保证这几列的组合是不重复的
查看每个部门下都有哪些工作职位
select distinct deptno,job from emp;

order by 对于查询结果按照指定的字段排序

1. 默认asc,升序
2. desc,降序

select * from emp order by sal;

查询30号部门的员工薪资,按照升序排列
select * from emp where deptno=30 order by sal;

如果是多列排序,那么就会先按照order by后面的第一个字段有限排序,当第一个字段出现重复时,在按照第二个字段排序
查看员工信息,按照薪资和部门排序
select * from emp order by sal,deptno;

聚合函数

1. 聚合函数是用来进行统计工作的,可以将多行数据进行统计,然后得出一个结果.
2. 聚合函数忽略null值

查看最大薪资和最小薪资
select max(sal) "最大工资",min(sal) "最小工资" from emp;

查看平均值和总分
select avg(sal) "平均工资",sum(sal) "总和工资" from emp;

统计表中有多少条数据
select count(*) from emp;

group by子句
是为了聚合函数服务的,可以在统计数据时细化分组,它允许将某一个字段值一样的记录看做一组,然后进行统计,每组可以返回一个统计结果

查看每个部门的最高薪资
select max(sal),deptno from emp group by deptno;

group by进行分组时,被分组的字段一定要有重复值,否则分组没有意义.
当select中出现聚合函数,那么不在聚合函数中的其他字段必须出现在group by子句中
group by子句后面可以指定多个字段,会按照多个字段组合相同的数据进行分组
select max(sal),deptno,job from emp group by deptno,job;

having子句
having也是用于添加过滤条件,它的过滤实际是在统计结果之后进行的,所以having是为了统计结果之后进行的,不能独立出现,一般跟在group by子句之后
select avg(sal),deptno from emp group by deptno having avg(sal)>2000;

关联查询
当从多张表查询数据时,我们会建议关联关系,然后在对多张表进行查询工作,重点就是指定这些表中数据的关联关系,必须要指定关联条件,如果不指定,那么会产生笛卡尔积,后果会比较严重.
查询每个员工对应的部门名称

注意在使用别名去进行关联的话,在查询字段的时候也需要加上别名.
select e.name,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno;

查询福特,所在的部门地址和名称
select e.ename,d.dname,d.loc from dept d,emp e where e.ename='福特' and d.deptno=e.deptno

主键和外键(建表的时候主键一般必须要有,外键可以不需要)

主键 primary key
通常每张表的第一个字段就是主键,主键要求在整张表中,记录不能为空,并且不能有重复值,目的是表示每一条记录的唯一性

外键 foreign key
外键是B表中的一个字段,且字段值是来自A表的主键,一般可以形成A表和B表的关联关系

内连接
查询每个员工所在部门的名称
select * from emp e join dept d on(e.deptno=d.deptno);

外连接
使用外连接查询时,以一张表为驱动表(数据要全部显示),该表的数据会全部体现在结果集中,但是来自关联表中的字段由于不满足连接条件没有对应的记录,所以全部显示null
查询所有部门的名称和地址和部门中包含的员工信息

右外联
select e.empid,e.ename,d.* from emp e right outer join dept d on(e.deptno=d.deptno)

左外联
select e.empid,e.ename,d.* from emp e left outer join dept d on(e.deptno=d.deptno)

全外连接
select e.empid,e.ename,d.* from emp e full outer join dept d on(e.deptno=d.deptno)

自连接

当前表的一条记录对应当前表的多条记录,自连接的设置是为了解决同类型数据间有存在上下级关系的树状结构的保存与关联
查询员工的上级领导是谁
select e.empid,e.ename,m.empid,m.ename from emp e,emp m where e.empid=m.mgr;

查询克拉克的上级领导是谁
select m.empid,m.ename from emp e,emp m where e.ename='克拉克' and e.mgr=m.empid;

子查询
在from中出现了子查询,那么久以为这我们要讲查询结果当做一张表去看待,如果在自查寻中出现了函数或者表达式,那么一定要加上别名
有些查询需要建立在另一个查询的结果上进行,name先执行的查询就是子查询

查询和scott相同的部门的员工有谁
select * from emp where deptno=(select deptno from emp where ename='SCOTT');

查询薪资比scott高的员工信息
select sal,ename from emp where sal>(select sal from emp where ename='SCOTT')

查询和salesman相同部门的员工信息,下面的in表示包含
select * from emp where deptno in(select * from emp where job='SALESMAN');

查询员工薪资,高于公司平均薪资的员工信息
select sal,ename from emp where sal>(select avg(sal) as avgsal from emp);

查询员工薪资高于SALESMAN最低薪资的员工薪资(还可以结合any使用)
select ename,sal from emp where sal>(select min(sal) as minsal from emp where job='SALESMAN');

查询员工薪资高于SALESMAN最高薪资的员工薪资(还可以结合聚合函数)
select ename,sal from emp where sal>all(select sal as minsal from emp where job='SALESMAN');

查询员工薪资,高于SALESMAN和办事员的最高薪资员工信息
select ename,sal from emp where sal>(select max(sal) from emp where job in('SALESMAN','办事员'))

查询员工薪资,高于部门平均薪资的员工信息
select e.empid,e.ename,e.sal,e.deptno from emp e,(select avg(sal) avgsal,deptno from emp group by deptno) d where e.sal>d.avgsal and e.deptno=d.deptno; 

查询最低工资高于30号部门最低工资的部门
select deptno,min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno =30)

子查询可以将查询结果集作为表创建出来
如果子查询中出现了函数或者表达式,那么一定要加上别名
create table empdept as (select e.empid,e.ename,e.sal,d.deptno,d.dname from emp e,dept d where e.deptno=d.deptno);

分页查询

需要使用rownum,被称为伪列,通常用来给当前查询出来的结果进行编号的,从1开始计数
select rownum,ename,sal,deptno from emp;

如果需要用rownum作为过滤条件,name就需要先使用rownum做一次子查询,然后在子查询基础之上进行过滤
select * from(select rownum rn,ename,sal,deptno from emp) e where e.rn<3;

也可以直接将rownum作为过滤条件,查询rownum前三条数据(要考虑版本,新的一轮版本支持)
select rownum ,e.* from emp e where rownum<4;

取出1-3页的数据
select * from(select rownum rn,e.* from emp e) e1 where e1.rn between 1 and 3;

分页计算公式
(n-1) * pagesize+1 
n * pagesize
n代表当前页数,第一页N就是1;第二页N就是2,以此类推
pagesize代表每一页显示的最大条目数

按照薪资降序排列,取出薪资前三的员工信息
select * from(select rownum rn,e1.* from emp e1) e2 where e2.rn between 2 and 4;

decode(参数,匹配1,计算1,匹配2,计算2,默认值)
如果没有提供默认值,当没有匹配到结果时,将返回null
查询工作职位如果是salesman的员工,薪资*2;如果是办事员,薪资*3;如果没有匹配到,那么返回原本工资
select ename,job,decode(job,'SALESMAN',sal*2,'办事员',sal*3,sal) newsal,sal from emp;

先按照col1进行分组,然后在分组的基础之上按照col2排序,最后将结果进行编号,这个编号是连续且唯一的,并且是按组算的
使用row_number()over(partition by col1 order by col2)
按每个部门分组,然后按照部门的薪资排序
select row_number()over(partition by deptno order by sal) row_id,ename,deptno,sal from emp;

先按照col1进行分组,然后在分组的基础之上按照col2排序,最后将结果进行编号,这个编号是不连续不唯一的
使用rank()over(partition by col1 order by col2)
select rank()over(partition by deptno order by sal) rank_id,ename,deptno,sal from emp;

视图
是个数据库对象,也称之为虚表,视图看起来是一个表的样子,但它不是一个真实的表,而是一个sql与查询的结果集

创建视图
create view vemp30 as select empid,ename,sal,deptno from emp where deptno=30;

查看视图(如果使用的不是dba用户,那么需要先登录dba用户授权)
grant create view to 用户名(对用户进行创建视图的权限)

如果在创建视图时,使用了别名,那么在该视图中,就会使用别名作为字段名
select * from vemp30;

对视图进行DML操作(插入,修改,删除)
如果是单标视图,就会将基表数据造成污染,如果是多表联合查询视图,就会直接报错

删除视图(删除视图不会影响基表)
drop view vemp30;

read only
当视图被设置为只读模式,那么就不能对他进行DML操作
create or replace view vemp30 as select empid id,ename name,sal,deptno from emp where deptno=30 with read only;

序列 sequence
序列是一个数据库对象,作用是根据指定的规则生成一组数字,常用于给表中主键提供值
创建序列
(seq_emp_empid) emp表示表名 empid表示表中主键列名 1000表示从1000开始,10表示步长
create sequence seq_emp_empid start with 1000 increment by 10;

nextval
作用是产生下一个数字,第一次使用,返回值是start with指定的值,第二次使用就会根据步长来增加数值,一旦获取下一个之后,就不能得到上一个数字了,序列是不可逆的
select seq_emp_empid.nextval from dual;

currval 获取序列最后一次产生的数值,可以多次调用,不会使序列产生下一个数字
需要注意的时,必须在使用过一次nextval之后,才能执行currval
select seq_emp_empid.currval from dual;

删除序列
drop sequence seq_emp_empid;

索引
通常用来提高查询效率,排序效率.数据库管理系统自行维护索引的算法,我们只需要指定何时为某表的某字段添加索引,在查询时,数据库会自动使用索引,无需再查询语句中指定
添加索引操作,为emp表的ename字段添加索引
create index idx_emp_ename on emp(ename)

删除索引
drop index idx_emp_ename;

新增

向表中插入数据
insert into "classroom" (id,name,age,adress) values(1,'吴鹏',18,'南京');

插入数据时,不指定插入字段,就需要把所有的字段全插入数据,需要注意的是在插入的数据中字段值需要使用单引号
insert into "classroom" values(1,'吴磊',29,'常州');

提交后的数据无法在进行回滚

修改

修改数据操作,修改多个数据之间用逗号隔开
update "classroom" set name='袁昆' where id=2;

PL/SQL基础

plsql程序块,允许嵌入sql语句,允许定义变量,使用条件语句和循环语句,能处理异常
编写一个简单的plsql程序块
set serveroutput on 表示允许服务器输出plslq运行结果
begin
dbms_output.put_line('hello');
end;
声明变量
变量名必须用字母开头,长度不能超过30个字符,不能包括减号和空格,不能使用数据库关键字,变量不区分大小写
declare
temp varchar2(10);
sal number(7,2);
begin
temp:='曹操';
sal:=5000;
dbms_output.put_line(temp);
dbms_output.put_line(sal);
end;

v_name emp.ename%type
表示v_name的属性,是来自emp表中ename字段的属性
into:可以将查询到的结果,直接赋值给变量
declare
v_name emp.ename%type;
v_sal emp.sal%type;
begin
select ename,sal into v_name,v_sal form emp where empid=7788;
dbms_output.put_line(v_name);
dbms_output.put_line(v_sal) ;
end;

if分支结构
v_a number(2);
v_b number(2);
begin
v_a:=5;
v_b:=5;
if v_a>v_b then
dbms_output_line('a大于b');
elsif v_a<v_b then
dbms_output_line('a小于b');
else
dbms_output_line('a等于b');
end if;
end;

case分支结构
declare
v_sal emp.sal%type;
begin
只能存入的数据是单条的才能插入
select sal into v_sal from emp where ename='克拉克';
case
when v_sal<2000 then
dbms_output.put_line('薪资低');
when v_sal between 2000 and 4000 then
dbms_output.put_line('薪资还行');
when v_sal>4000 then
dbms_output.put_line('薪资高');
end case;
end;

循环(while)
declare
i number:=1;
begin
while i<=5
loop
dbms_output.put_line(i);
i:=i+1;
end loop;
end;

循环(for)
begin
for j in 1..9
loop
dbms_output.put_line(j);
end loop;
end;

游标(cursor)
就是处理数据的一种方法,为了查看或者处理结果集中的数据,游标提供了在结果集中一次一行或者一次多行浏览数据的能力,可以把游标当做一个指针,他可以指向结果中的任何位置,然后允许用户对指定位置的数据进行处理
创建游标步骤
1.声明游标
cursor 游标名 is select 语句
select语句可以是视图,也可以是多表查询的结果,也可以使用条件过滤
2.打开游标
open 游标的名称
3.提取游标中数据
fetch 游标名 into 变量名
4.关闭游标
close 游标名
declare
取出表中一行的数据
v_emp emp%rowtype;
游标指向deptno=30的数据
cursor emp_cursor is select * from emp where deptno=30;
begin
open emp_cursor;
loop
把游标提取出来的数据全部放入v_emp中
fetch emp_cursor into v_emp;
exit when emp_cursor%notfound;
dbms_output.put_line(v_emp.empid||','||v_emp.ename||","||v_emp.job);
end loop;
close emp cursor;
end;

for循环处理游标
declare
cursor emp_cur is select * form emp;
begin
for v_emp in emp_cur
loop
dbms_output.put_line(v_emp.empid||','||v_emp.ename);
end loop;
end;

存储过程
用于在大型数据库中,一组为了完成特定功能的sql语句,一般部署在数据库服务器上,经过一次编译后再次调用不需要再次编译,用通过指定存储过程的名字并给出参数来执行它
启动输出
set serveroutput on;
创建一个简单存储过程
create or replace procedure hello is
begin
dbms_output.put_line('海螺我的');
end;
调用存储过程
begin
hello();
end;

带参数的存储过程
create or replace procedure update_comm(in_comm in number,in_id in number) is
begin
update emp set comm=in_comm where empid=in_id;
end update_comm;
调用
begin
update_comm(500,7369);
commit;
end;

带返回值的存储过程
create or replace procedure out_name(oname out varchar2,in_id in number) is
begin
select ename into oname from emp where empid=in_id;
end out_name;
declare
oname emp.ename%type;
begin
out_name(oname,7369);
dbms_output.put_line(oname);
end;

触发器
是一个在数据库时间发生时自动执行的plsql程序块,是一个独立的事务,在执行过程中如果发生异常,则整个事务会自动回滚
创建一个emp历史数据表,用来保存被删掉的数据.where后面的条件1=2表示只查询字段
create table emp_his as select * from emp where 1=2;
创建触发器
create or replace trigger del_emp;
事后触发器
after delete on emp referencing OLD as old new as new
行级触发器
for each row
begin
insert into emp_his(empid,ename,job,mgr,sal,hiredate,deptno)
values(:old.empid,:old.ename,:old.job,:old,sal,:old,hiredate,:old.deptno);
end;