desc 表名 :显示表中所有的列
1、使用select语句查询表某张表中的所有数据内容
语法:select [distinct]*{col_name1,col_name2,…} from tb_name;
*:表示所有的列 col_name1:列名
例如:查看s_dept表中的所有记录的id和name
select id,name from s_dept;
2、select可以对指定的列的所有值进行算术运算
语法:select col_name 运算符 数字 from tb_name;
注:select不能对原始数据进行修改
例如:查看每位员工的员工id,名字,月薪涨100以后的年薪
select idname,(salsary+100)*12 from s_emp;
3、给查询的列区别名
语法:select old_column [as] new_column_name from tb_name;
例如:查看员工的id,名字和年薪,年薪列名为annual
select id,name,salary*12 as annual from s_emp;
4、使用|| 可以使多列的值或者列和特殊的字符串合并到一列进行显示
语法:select col_name ||‘spe_char’|| col_name from tb_name;
‘spe_char’:如果一个列的值要跟特殊的字符串连接显示,使用该语法。
例如:查看员工的员工id和全名
select id,first_name||last_name from s_emp;
查看员工的员工id,全名和职位名称,全名和职位名称合并成一列,格式为姓名,职位名称
select id,first_name||’ ‘||last_name||’,’||title as name from s_emp;
5、对null值做替换运算
nvl()函数:
语法:select nvl(col_name,change_value) from tb_name;
例如:查看所有员工的id,名字和提成,如果提成为空,显示成0
select id,first_name||last_name,nvl(commission_pct,0) commission_pct from s_emp;
6、使用distinct关键字,可以将显示中重复的记录只西西安市一条
语法:select distinct col_name,col_name… from tb_name;
注:① distinct 关键字只能放在select关键字后面
②如果distinct关键字后面如果出现多列,表示多列联合去重,多列的值都相同的时候才会认为是重复记录
例如:查看所有员工的职位名称和部门id,同职位同部门的值显示一次
select distinct title,dept_id from s_emp;
7、sqlplus命令
登录后可以使用buff(缓存)来存储/执行/修改要执行的sql语句
buff中只能存储一条sql语句,但是可以有很多行,每次放入新的sql语句,会把之前的覆盖掉,每次执行sql语句,都会把这个sql语句放到buff里面
l 查看缓存中的sql语句
a 在定位的哪一行后面追加新的内容
i 在定位的那一行下面插入新的一行
c 替换定位的那一行中的某些字符串 使用:c/老字符串/新字符串
del 删除定位所在的那一行
n 后面加的内容可以重写这一行
! 清屏 !clear windows是$cls
/ 执行缓存sql命令
clear buffer 清空当前缓存的命令
save test.sql buff中的sql语句保存在test.sql文件中
get test.sql 把test.sql中的内容在加载到buff中,但是没有运行
start test.sql 把test.sql中的内容在加载到buff中并且执行
@test.sql 把test.sql中的内容在加载到buff中并且执行
edit file_name 使用系统默认编辑器去编辑文件
spool file_name 将接下来的sql语句以及sql的运行结果保存到文件中
sql1
result1
sql2
result2
…
spool off 关闭spool功能
exit:退出
8、column 使得显示界面好看
是sqlplus命令,
COLUMN last_name HEADING ‘Employee|Name’ FORMAT A15 给last_name取别名为Employee|Name |代表换行 A15表示是15个字节长,_是一个字节长
COLUMN start_data FORMAT A8 NULL ‘Not hired’
如果start_date值为空的话,显示为’Not hired’;
Format后不能直接跟null, 要先a8或a10;
NULL 'Not hired’和nvl类似
COLUMN salary JUSTIFY LEFT FORMAT $99,999.00
salary JUSTIFY LEFT : 仅仅改变列名显示为左齐
. FORMAT $99,999.00: 控制显示格式为前面加 $ 符, “,”为分隔符, 0或9代表数字(通配符),0表示替换对齐数值,位数不足会补足,可以混合使用。
修改语言的代码:alter session set nls_language=english;
alter session set nls_laguage='simplified chinese';
第二章:排序和限制查询(order by)
1、排序 (升序/降序 默认是升序)
语法:select col_name,… from tb_name order by col_name[asc|desc],…
注:order by子句
2、多个列排序
select id,id2 from test order by is,id2 des;
3、限制查询,即指定查询条件进行查询(where)
语法:select col_name,…
from tb_name
where col_name 比较操作表达式
逻辑操作符
col_name 比较操作表达式
…
例如:查看员工工资小于1000的员工id和name
select id,name,salsry from s_emp where salary<1000;
4、sql比较操作符
1、between and:在什么范围之内
例如:select id,name,salary
from s_emp
where salary between 700 and 1500;
2、 in(list):在一个列表中
例如:查看员工1,3,5,7,9员工的工资
select id,name,salary
from s_emp
where id in (1,3,5,7,9);
3、like:模糊查询,值不是精确的值的时候使用 通配符可以替代任何内容的符号
% :1到多个字符 _:当且仅当通配一个字符
例如:查看员工名字以C字母开头的员工的id和工资
select id,last_name,salary
from s_emp
where last_name like’C%’;
查看员工名字长度不小于5,且第四个字母为n的字母的员工id和工资
select id,name,salary
from s_emp
where last_name like ‘__n%’;
4、is null:对null值操作特定义的操作符,不能用=
例如:查看员工提成为空的员工的id和名字
select id,last_name,commission_pct
from s_emp
where commission_pct is null;
5、逻辑操作符
1、当条件多个的时候使用
and:且逻辑
or:或逻辑
not:非逻辑
例如:查看员工部门id为41且职位名称为Stock Clerk(存库管理员)的员工id和名字
select id,last_name ,title
from s_emp
where id=41 and title=‘Stock Clerk’ ;
第三章:单值函数
函数分为 单值函数(字符函数、日期函数、转换函数、数字函数)
分组函数
1、哑表dual 语法:select 1+1 from dual;
2、字符函数
-
lower:把字符转换成小写 例如:select lower(‘HELLO’) from dual
-
upper:把字符转换成大写例如:select upper(‘hello’) from dual
例如: 查询s_emp表中的Ngao的个人信息
select last_name,salary,dept_id from s_emp
where upper(last_name)=‘NGAO’;
- initcap:把字符串首字母大写
例如:把’hELLO’转换为首字母大写,其余字母小写
select initcap(‘hELLO’) from dual;
- concat:把两个字符串连接在一起()类似于||
例如:把’hello’和’world’两个字符串联系在一起,并且起个别名为msg
select concat(‘hello’,‘world’) msg from dual;
- substr:截取字符串
例如:截取’hello’字符串,从第二个开始,截取后面的连续3个字符
select substr(‘hello’,2,3) from dual;
-
length:获取字符长度
例如:获取s_emp表中last_name列的每个值的字符长度
select length(last_name) from s_emp;
-
nvl 替换类中为null的值
2、数字函数
-
round 四舍五入 语法:round(arg1,arg2) 第一个参数表示四舍五入操作的数字 第二个参数表示保留到哪一位
例如:保留小数点后2位 select round(45.923,2) from dual;
保留到个位 (个十百千万…)
select round(45.923,0)
from dual;保留到十位 (个十百千万…)
select round(45.923,-1)
from dual; -
trunc 截取到某一位
trunc(arg1,arg2) 和round的用法一样,但是trunc只舍去不进位
例如: 截取到小数点后面2位 select trunc(45.929,2) from dual
截取到个位 (个十百千万…)select trunc(49.923,0)from dual;
截取到十位 (个十百千万…)select trunc(49.923,-1)from dual;
-
mod 取余
mod(要进行取余操作的数字,参数1和谁取余)
例如:把10和3进行取余
select mod(10,3) from dual;
3、日期函数
- sysdate:表示系统的当前时间
例如:显示当前时间 select sysdata from dual;
注:进行加减操作的时候,单位是天
-
months_between 两个日期之间相差多少个月(单位是月)
例如:30天之后和现在相差多少个月
select months_between(sysdate+30,sysdate) from dual;
-
add_months(日期,往后推的时间) 返回一个日期数据:表示一个时间点,往后推X月的日期
例如:'01-2月-2016’往后推2个月
select add_months(‘01-2月-2016’,2) from dual;
注:这个往后推的时间也可以是负数,表示往前推X月
-
next_day 返回一个日期数据:表示一个时间点后的下一个星期星期几是哪一天
例如:离当前时间最久的下一个星期五是哪一天
select next_day(sysdate,‘星期五’) from dual;
-
last_day:返回一个日期数据:表示一个日期所在月份的最后一天
例如:当前日期所在月份的最后一天
select last_day(sysdate) from dual;
-
round:对日期四舍五入,返回操作后的日期数据
例如:把当前日期四舍五入到月
select round(sysdate,‘MONTH’(‘YEAR’)) from dual;
-
trunc 对日期进行截取
4、类型转换函数
-
to_char: 把日期转换为字符
例如:把当前日期按照指定的格式转换为字符串
select to_char(sysdate,‘yyyy’) from dual;
to_char:把数字转换为字符
例如:fm表示去除结果显示中的开始的空格
select to_char(salary,‘fm$999,999.00’)
from s_emp;
L表示系统本地的货币符号
select to_char(salary,‘fmL$999,999.00’)
from s_emp;
-
to_number :把字符转换为数字
例如 :select to_number(‘1000’) from dual;
-
to_date:把字符转换为日期
例如:select to_date(‘95/5月/25’,‘yy/month/dd’) from dual;
第四章:多表查询
1、笛卡尔积:在数据库中直接查询2张表,那么其查询结果就会产生笛卡尔积 A={a,b} B ={0,1,2} 例如:select * from s_emp,s_dept;
2、连接查询
① 等值连接
利用一张表某列的值和另一张表中某列的值相等的关系,把这2张表联系在一起
例如:查询员工的名字、部门编号、部门名字
select last_name,dept_id,name
from s_emp,s_dept
where s_emp.dept_id=s_dept.id;
对查询结果集的操作
union:获取2个结果集的并集
union all 把两个结果集 合并在一起显示出来
minus :第一个结果集出去第二个结果集和他相同的部分
intersect:获得2个结果集的交际
rownum:伪列
第五章:组函数
group by:在查询 表中数据的时候进行分组的关键字
having:分组后进一步数据筛选的关键字
组函数:avg count max min sum stddev(标准差) variance(方差)
第六章:子查询(嵌套查询)
一个sql语句中嵌套了另一个select语句
例如:查询工资比Simth工资高的员工信息
select last_name,salary
from s_emp
where salary>(
select salary
from s_emp
where last_name=‘Simth’
);
例如:查询所有部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;
练习:查询平均工资比41号部门的平均工资高的部门员工信息
第一步:查出41号部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;
1247.5
第二步:查出比41号部门工资高的部门
select dept_id
from s_emp
group by dept_id
having avg(salary)>1247.5;
31、32、35、50、33、10
第三步:查找员工的信息
select last_name,dept_id,salary
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
)
);
例如:查询平均工资比41号部门的平均工资高的部门中员工的信息,
并且显示出当前部门的平均工资
select s1.last_name,s1.dept_id,s1.salary,temp.avg(salary)
from s_emp s1,select dept_id,avg(salary) temp
from s_emp
group by dept_id
where s1.dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
)
and s1.dept_id=temp.dept_id
);
第七章:运行时参数(&)可以重写输入这个值
where &con(自己可以写这个值)
例如:select last_name,salary,dept_id
from s_emp
where id=&id;
1=1相当于没有条件
define:定义变量
undefine:取消变量
accept:交互式
prompt:可以在用户输入的时候显示一个提示信息
例如:accept name prompt '请输入name变量的值: ’
hide:可以隐藏用户输入的内容不被看见
例如:accept name prompt '请输入name变量的值: ’ hide
第八章:数据建模和数据库设计
数据库设计:数据建模(实体关系图E-R图有实体、属性、关系)
实体-关系图:
第一范式
第二范式
第三范式
第九章:建表
建表格式 :
create table 表名(列名1 数据类型 列级约束);
create table 表名(
列名1 数据类型 列级约束,
表级约束1
);
表名:必须以字母开头,必须是1-30个字符之间的长度,不能和数据库中已有的对象重名,不能是数据库中的关键字只能出现A-Z,a-z,0-9,_,$,#
列中的常用数据类型: char varchar varchar2 number date 其他事类型:BLOB CLOB(大字段类型)
例如1:使用四种列级约束 主键约束 非空约束 唯一约束 check约束
id为主键
name不能为空
email是唯一约束
gender是指定约束 只能为f m
age
brithday date
create table student(
id number primary key,
name varchar2(100) not null,
email varchar2(100) unique,
gender char(1) check(gender in('f','m')),
age number,
brithday date
);
例如2:多表 使用列级约束 声明 外键约束
要求:建立一个顾客表 id作为主键 name不能为空
建立一个订单表 id为主键 content不能为空 顾客id作为外键
create table t_customer(
id number primary key,
name varchar2(200) not null
);
create table t_order(
id number primary key,
content varchar2(200) not null,
customer_id number referencrs(参考) t_customer(id)
);
想要直接删除顾客表必须加级联(cascade)
drop table t_customer cascade constraints;(约束)
例如4:表级约束
create table student(
id number,
name varchar2(20) not null,
age number,
email varchar2(100),
gender char,
primary key(id),
unique(email),
check(gender in('f','m'))
);
注:not null不能声明成表级约束
例如5:使用表级约束 声明 外键约束
create table t_customer(
id number primary key,
name varchar2(20) not all
);
create table t_order(
id number primary key,
contene varchar(200) not null,
customer_id number,
foreign key(customer_id) referencrs t_customer(id)
);
例如6:
使用表级约束 声明 联合唯一约束(必须用表级约束来约束)
create table student(
id number primary key,
class varchar2(50) not null,
name varchar2(20) not null,
unique(class,name)
);
起约束名
每一个后面跟constraint 表名_列名_约束
语法:
给列名起约束名: 列名 数值类型 constraint 表名_列名_约束 约束
给表起约束名: 后面 constraint 表名_列名_约束条件 约束条件(列名)
特殊的建表
建立一个和数据库中一样的表
create table 新表名字
as
select * from 数据库中存在的表名
只有表结构没有数据
create table 新表名
as
select * from 数据库中的表
where 1=2;
只复制几列的数据
create table 新表名
as
select 列名 from 数据库中的表名
DML语句
insert:插入数据
语法:insert into 表名(插入的列表) values(插入的值)
1、默认的是表中的数据按照每一列一次插入
2、可以进行交换位置将数据插入
3、列的值如果是null,那么在插入的时候不能指定这个列
4、unique约束和check约束的值可以为null
5、主键约束和非空约束的值不能为null
6、可以使用运行时参数(values(&id,'&name'))
7、把查询的结果插入表中 前提是查询的列的顺序和插入列中的顺序的数据类型是一致的
update:更新数据
语法:update 表名 set 列表=要修改的值,列表=要修改的值...
where 条件;
delete:删除数据
删除用户信息
语法:delect from 表名 where 条件;
on delete:删除有关系的数据,在一个表中的外键使用了另一个表中的主键,然后删除另一个表中的主键,这时候就需要使用on delete
on delete no action(默认情况:什么不都写)
如果在建外键的时候,不加on delete解释 on delete noaction
on delete cascade 删除外键所在的那条完整的数据
在声明外键列的时候加入on delete cascade语句 删除了表中所关联的那条数据
constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete cascade
on delete set null 删除成功将所关联的那条数据的外键设置为null
声明外键列的时候加入on delete set null语句
constraint order_cid_fk foreign key(customer_id) references t_customer(id) on delete set null
commit:提交事务处理的结果
savepoint:设置回滚的点(保存点)
rollback:回退到哪一步
数据库事务
1、DML语句执行的时候,如果当前有这个事务就会使用这个事务,如果没有这个事务就会创建一个事务
2、只要DML语句才会产生事务,其他语句都不会产生
3、commit/rollback/DDL语句(create drop alter)才可以结束当前事务
4、commit和DDL语句结束事务的方式把这个事务给提交了
第十二章:修改表和约束(alter语句)
1、表中添加一个新列
alter table 表名 add 列名 数据类型;
2、删除表的某列
alter table 表名 drop column 列名;
3、给表中的列添加约束
alter table 表名
add constraint user_name_un
unique(name);
4、删除表中的约束
alter table 表名
drop constraint user_name_un
5、修改表的名字 rename 旧表名 to 新表名
6、修改表中某列的类型(要修改不能的数据类型,必须先将列中的数值设置为空,否则只能改变字符串长度)
alter table 表名 modify (列名 类型)
7、让约束失效:必须知道约束的名字
alter table 表名
disable constraint user_id_pk cascade;
8、让失效的约束再次生效
alter table t_user
enable constraint user_id_pk;
第十三章:序列(sequence)
创建序列:create sequence 序列名
设置属性
increment by :每次加多少
start with :从几开始加
maxvalue :最大值是几
minvalue:最小值是几
cycle:到最大值是否循环 如果循环从1开始
cache:每次在缓存里放多少个值
使用序列:
1、获取序列的下一个值
select 序列表名.nextval
2、查看序列中当前值是多少
select 序列表名.currval
第十四章:视图(view)
简单视图:没有group by语句,没有组函数,查询只有一张表
复杂视图:有group by语句,有组函数,查询多张表
区别:简单视图可以修改原来的数据,复杂视图不能修改原来的数据
创建视图: create or replace view 视图名字 as sql语句
删除视图:drop view 视图名字
可以通过简单视图对原来的表的数据进行删除/更新/插入
with read only语句:只能通过视图查询不能修改数据
with check option语句:通过这个视图修改,但是也必须的通过这个视图显示出来
第十五章:索引
创建索引:
自动创建:当在表中指定了primary Key或者unique约束时会自动创建唯一值索引。
用户创建:语法:create index 索引名 on 表名(列名)
删除索引:drop index 索引名
查看索引:select index_name from user_indexes;
索引结构:B-tree
第十六章:用户的权限控制(Synonym)
1、创建用户 create user 用户名 identitied by 密码;
2、删除用户 drop user 用户名 cascade;
3、赋予权限 grant create 权限 to 用户名;
4、修改密码 passeord 用户名;
alter user 用户名 identified by 密码;
5、赋予某一 个用户某种对象操作的权限 grant 权限 on 对象 to 用户
6、回收权限 revoke 权限 on 对象 from 用户
7、创建同义词(synonym) 隐藏表原来的名字
删除同义词 drop synonym 私有同义词名
drop public synonym 公共同义词
私有同义词
//给表t_user创建一个私有同义词
create synonym my_test
for t_user;
公共同义词
登录 system用户登录 、sqlplus "/as sysdba"
create public synonym my_test2
for test.t_user;
test.t_user表示的是test用户下面的t_user表
让所有人都有查看这个同义词的权限
grant select on my_test2 to public;
8、数据库导入导出
系统终端
导入:imp 根据提示按回车下一步即可
导出:exp 同理