简单查询
1数据库介绍
数据库分类
- 关系型数据库:oracle, mysql, db2, sql server, sqllite等
- 非关系型数据库:redis, hbase等
了解
DBMS:
database management system数据库管理系统RDBMS:
relational database management system关系型数据库管理系统
2sqlplus简单使用
登录
C:\Users\inmeditation>sqlplus
SQL*Plus: Release 11.2.0.1.0 Production on 星期日 11月 10 19:00:28 2019
Copyright (c) 1982, 2010, Oracle. All rights reserved.
请输入用户名: scott
输入口令:
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
查看当前登录用户
SQL> show user;
USER 为 "SCOTT"
查看,设置搜索后结果行的长度
SQL> show linesize
linesize 80
SQL> set linesize 500;
SQL> show linesize;
linesize 500
查看,设置搜索后结页面的长度
SQL> show pagesize;
pagesize 14
SQL> set pagesize 100;
SQL> show pagesize;
pagesize 100
注意,行长和页长再次登录会恢复默认值
查看表结构
SQL> desc emp;
名称 是否为空? 类型
------ -------- --------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
更改当前用户的密码
SQL> alter user scott identified by scottpw;
用户已更改。
通过交互方式更改当前用户密码
SQL> passw
更改 SCOTT 的口令
旧口令:
新口令:
重新键入新口令:
口令已更改
切换到超级用户
SQL> conn sys as sysdba
输入口令:
已连接。
SQL> conn system/sys
已连接。
超级用户下解锁scott用户
SQL> alter user scott account unlock;
用户已更改。
超级用户下更改scott账户的密码
SQL> alter user scott identified by scott;
用户已更改。
退出当前数据库账号
SQL> disc
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
SQL>
退出数据库
SQL> exit
C:\Users\inmeditation>
SQL> quit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
C:\Users\inmeditation>
3简单查询
1 表的概念:
- Excel中的表格就是表。竖着叫一列.横着是一行
- 表中也是一样,其中每列叫做列或者字段,每行叫做记录
查询表中所有信息
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- -------------------- ------------------ ---------- -------------- ---------- ---------- ----------
7777 S_HH%GGH CLERK 7902 20
7369 SMITH CLERK 7902 17-12月-80 800 20
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 30
7521 WARD SALESMAN 7698 22-2月 -81 1250 500 30
7566 JONES MANAGER 7839 02-4月 -81 2975 20
查询单个列信息:姓名,入职时间,部门
SQL> select ename,hiredate,deptno from emp;
ENAME HIREDATE DEPTNO
-------------------- -------------- ----------
S_HH%GGH 20
SMITH 17-12月-80 20
ALLEN 20-2月 -81 30
WARD 22-2月 -81 30
JONES 02-4月 -81 20
2 特殊的列:rowid和rownum
rowid
- 数据库中唯一的一个无规律字符串, rowid能唯一的确定一条数据.
- 每条数据都有一个rowid,唯一且不为空.
rownum:伪列,用于过滤数据
可以写作rownum<5, rownum<=6,rownum=1
但用rownum=2或者rownum>3,这种写法是不能运行的
则这个数字必须是小于等于1的整数,否则无法查出数据.
SQL> select ename,rowid,rownum from emp;
ENAME ROWID ROWNUM
-------------------- ------------------ ----------
S_HH%GGH AAAR3sAAEAAAACWAAA 1
SMITH AAAR3sAAEAAAACXAAA 2
ALLEN AAAR3sAAEAAAACXAAB 3
WARD AAAR3sAAEAAAACXAAC 4
JONES AAAR3sAAEAAAACXAAD 5
MARTIN AAAR3sAAEAAAACXAAE 6
BLAKE AAAR3sAAEAAAACXAAF 7
3 别名
表别名
就是给表另取一个名字,这个名字可以代替表名
SQL> select p.empno,p.ename from emp p;
EMPNO ENAME
---------- --------------------
7777 S_HH%GGH
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
列别名
关键字:as,可以省略
SQL> select p.empno as 员工编号,p.ename 员工姓名 from emp p;
员工编号 员工姓名
---------- --------------------
7777 S_HH%GGH
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
条件查询where
1 not
取反
查看工资大于2000的员工姓名
SQL> select ename,sal from emp
2 where not sal<2000;
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
SCOTT 3000
KING 5000
FORD 3000
2 like
模糊查询 | 模糊匹配字符串
占位符
- % 表示零个或者若干个任意字符
- _ 表示一个任意字符
查询名字不是以S开头的所有员工姓名
SQL> select ename from emp
2 where ename not like 'S%';
ENAME
--------------------
ALLEN
WARD
JONES
MARTIN
查询名字以S开头的所有员工姓名,且名字长度为5
SQL> select ename from emp
2 where ename like 'S____';
ENAME
--------------------
SMITH
SCOTT
占位符在使用时,应该尽量避免在字符串的前面和后面都使用%
escape定义转义字符
查询名字中带有_的员工信息
SQL> select ename from emp
2 where ename like '%\_%'escape '\';
ENAME
--------------------
S_HH%GGH
查找emp表中ename列同时有%和_，且不挨在一起的数据;
SQL> select ename from emp
2 where ename like '%/%%_/_%' escape '/'
3 or ename like '%/_%_/%%' escape '/';
ENAME
--------------------
S_HH%GGH
3 is null
查询字段值是空值的数据
查看有佣金的员工姓名
SQL> select ename from emp
2 where comm is not null;
ENAME
--------------------
ALLEN
WARD
MARTIN
TURNER
4 in
后面跟一个集合或者子查询,用来查询条件列值是否在后面集合中
查询10号和20号部门之外的员工姓名和部门
SQL> select ename,deptno from emp
2 where deptno not in(10,20);
ENAME DEPTNO
-------------------- ----------
ALLEN 30
WARD 30
MARTIN 30
BLAKE 30
order by
1排序
- asc:表示升序,默认排序方式,从小到大
- desc:表示降序
SQL> select ename,sal from emp
2 order by sal desc;
ENAME SAL
-------------------- ----------
S_HH%GGH
KING 5000
FORD 3000
SCOTT 3000
JONES 2975
BLAKE 2850
CLARK 2450
多列排序
如果出现两个或者两个以上的列 排序时,先按第一个列进行排序
如果第一列的值相等,那么安照下一个排序列进行排序
查询员工信息,按照部门编号升序排序,如果部门相同按照工资降序排序
SQL> select ename,sal,deptno from emp
2 order by deptno,sal desc;
ENAME SAL DEPTNO
-------------------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
S_HH%GGH 20
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
ADAMS 1100 20
也可以写为
SQL> select ename,sal,deptno from emp
2 order by 3,2 desc;
ENAME SAL DEPTNO
-------------------- ---------- ----------
KING 5000 10
CLARK 2450 10
MILLER 1300 10
S_HH%GGH 20
FORD 3000 20
SCOTT 3000 20
JONES 2975 20
ADAMS 1100 20
group by
按照某一列数据进行分组
语法: select 列名 from 表名 group by 列名;
select后可以跟的内容
- 是 group by的列,即分组列;
- 经过聚合函数处理过的数据.
- SUM()--求和
- MIN()--取最小值
- MAX()--取最大值
- AVG()--平均值
- COUNT()--求数量
按照不同的组号来分组,并统计每组人数,最高工资和最低工资等信息
SQL> select deptno,count(*),sum(sal),avg(sal),max(sal),min(sal)
2 from emp
3 group by deptno;
DEPTNO COUNT(*) SUM(SAL) AVG(SAL) MAX(SAL) MIN(SAL)
---------- ---------- ---------- ---------- ---------- ----------
30 6 9400 1566.66667 2850 950
20 6 10875 2175 3000 800
10 3 8750 2916.66667 5000 1300
多列分组
多列分组时,多个分组列的值同时相等时,才属于同一个组
SQL> select deptno,job,count(*),max(sal)
2 from emp
3 group by deptno,job;
DEPTNO JOB COUNT(*) MAX(SAL)
---------- ------------------ ---------- ----------
20 CLERK 3 1100
30 SALESMAN 4 1600
20 MANAGER 1 2975
30 CLERK 1 950
10 PRESIDENT 1 5000
30 MANAGER 1 2850
10 CLERK 1 1300
10 MANAGER 1 2450
20 ANALYST 2 3000
having
对分组后的数据进行筛选
查询各个部门的部门人数,和平均工资,要求部门的平均工资大于2000
SQL> select deptno,count(*),avg(sal)
2 from emp
3 group by deptno
4 having avg(sal)>2000;
DEPTNO COUNT(*) AVG(SAL)
---------- ---------- ----------
20 6 2175
10 3 2916.66667
having和where的区别
-
having
- 后面跟条件表达式
- having是对分组后的数据进行筛选
- 后面可以跟聚合函数,它比where后执行
-
where
- 条件表达式,它后面可以跟普通函数,不能使用聚合函数,它是分组前对数据的筛选,优先执行
补充
执行顺序:
- where
- group by
- having
- select
- order by
case when:
根据分支的不同返回不同的值
两种写法
1. select deptno,
case deptno
when 10 then '部门1'
when 20 then '部门2'
when 30 then '部门3'
else '其他部门' end
from emp;
2.select deptno, case
when deptno=30 then '部门3'
when deptno=20 then '部门2'
when deptno=10 then '部门1'
else '其他部门' end
from emp;
查询emp表,将职位为ANALYST,CLERK和SALESMAN的显示为工人,MANAGER和PRESIDENT显示为管理员;
SQL> select ename,case
2 when job in('ANALYST','CLERK','SALESMAN') then '工人'
3 when job in('MANAGER','PRESIDENT') then '管理员'
4 else '其他人' end
5 from emp;
ENAME CASEWHENJOBIN('ANA
-------------------- ------------------
S_HH%GGH 工人
SMITH 工人
ALLEN 工人
WARD 工人
JONES 管理员
MARTIN 工人
BLAKE 管理员
CLARK 管理员
另一种写法
SQL> select ename ,case
2 when job='ANALYST' or job='CLERK' or job='SALESMAN' then '工人'
3 when job='MANAGER' or job='PRESIDENT' then '管理员'
4 else '其他人' end
5 from emp;
ENAME CASEWHENJOB='ANALY
-------------------- ------------------
S_HH%GGH 工人
SMITH 工人
ALLEN 工人
WARD 工人
JONES 管理员
MARTIN 工人
BLAKE 管理员
CLARK 管理员
查询emp表,查询出sal<1000, sal在1000-3000,和sal大于3000的人数;
SQL> select case
2 when sal<1000 then '小于1000'
3 when sal>=1000 and sal<=3000 then '1000-3000'
4 else '大于3000' end as "范围", count(1) 数量
5 from emp
6 where sal>=0
7 group by case
8 when sal<1000 then '小于1000'
9 when sal>=1000 and sal<=3000 then '1000-3000'
10 else '大于3000' end;
范围 数量
-------------------- ----------
小于1000 2
1000-3000 11
大于3000 1
另一种写法
SQL> select sum(case when sal<1000 then 1 else 0 end) "工资小于1000",
2 sum(case when sal>=1000 and sal<=3000 then 1 else 0 end) "工资在1000-3000",
3 sum(case when sal>3000 then 1 else 0 end) "工资大于3000"
4 from emp;
工资小于1000 工资在1000-3000 工资大于3000
------------ --------------- ------------
2 11 1
