/*sql的分类
1.DDL( Data Definition Language) 数据库定义语言（不可滚回）对表进行操作
1.创建表 create table 表名()
约束
/*
1:创建一张表 班级 id name
*/
/* 1：主键约束 primary key 不能为null 列的值不能重复*/
drop table t_class;
create table t_class(
id number,
name varchar2(50)
);
alter table t_class
add constraint t_class_pk primary key(id);
select * from t_class;
insert into t_class(id,name)values(1,'zt1702');
/* 2：唯一键 约束 unique */可以为null ,只能有一个null ，列的值不能重复*/
drop table t_members;
create table t_members(
id number,
name varchar2(50),
pass varchar2(50),
constraint t_members_pk primary key(id),
constraint t_members_uk unique(name)
);
select * from t_members;
insert into t_members(id,name,pass)
values(2,'user1','2222222');
commit;
/* 3：检查 约束 check */ 让列的值在指定的范围内取值
drop table t_emp;
create table t_emp(
empNo number,
name varchar2(50),
sex char(2),
age number
)
alter table t_emp
add constraint t_emp_pk primary key(empNo);
/* constraint t_emp_ck_sex check( sex in('男','女')) */
alter table t_emp
add constraint t_emp_ck_sex check( sex='男'or sex='女');
alter table t_emp
add constraint t_emp_ck_age check( age>=18 and age<=60) ;
insert into t_emp(empNo,name,sex,age)
values (3,'aa','男',88);
/*4.默认值约束default default*/ 让列的值没在赋值就取默认值
drop table t_ztuser;
create table t_ztuser(
id number,
name varchar2(20),
sex char(2), /* default '男' ,*/
constraint t_ztuser_pk primary key(id),
);
alter table t_ztuser
modify sex default '男';
insert into t_ztuser(id,name)values(2,'aaa');
select * from t_ztuser;
/*5.外键约束foreign key */让列的值取另外一张表主键存在的值或者null
create table t_classes(
id number,
name varchar2(50),
constraint t_classes_pk primary key(id)
);
create table t_studentss(
id number,
name varchar2(50),
sex char(2),
tel varchar2(18),
classId number,
constraint t_studentss_pk primary key(id)
);
alter table t_studentss
add constraint t_studentss_fk foreign key(classId)
references t_classes(id);
select * from t_classes;
select * from t_studentss;
insert into t_classes(id,name) values(2,'zt1702');
insert into t_studentss(id,name,sex,tel,classId)
values(4,'s4','男','13453',2);
delete from t_studentss;
如何给表加约束
两种方式添加约束
1. 在创建表的时候
create table c（
列名 数据类型
id number,
.....
constraint 约束名 约束类型（列名）
）
2. 在创建表后
alter table t_class
add constraint t_class pk primary key(id)
默认值约束 default例外
modify sex default ‘男’;
注意表设计的范式
数据库表设计的三大范式
第一范式：列不可再分
第二范式：行可以唯一区分，主键约束
第三范式：表的非主属性不能依赖与其他表的非主属性 外键约束
2. 删除表 drop table 表名;
drop table 表名;
truncate table 表名;
delete from 表名;
之间的区别
drop table 表 属于ddl语句 不需要事务 没有回滚
删除的是表的数据与表结构
truncate table 表名 删除表的所有数据 保留表结构 不能回滚
delete from 属于dml语句 可以回滚 需要事务
删除的是表的数据 可以单条或者多条或者所有
*/
drop table t_scottuser;
/* drop 删除掉之后 不可以回滚的
但oracle10g 提供了一个闪回技术 但不能是管理员账号操作
*/
flashback table t_scottuser to before drop;
*/
truncate table t_scottuser;
3.修改表结构 alter table
alter table 表名
1：添加列
alter table 表名
add( 列名1 数据类型1,
.......
列名n 数据类型n);
2:修改列的数据类型
alter table 表名
modify( 列名1 新的数据类型1,
.......
列名n 新的数据类型n);
3: 修改列的名称
alter table 表名
rename column 列名 to 新的列名;
4:删除列
alter table 表名
drop column 列名;
5:重命名表
rename 表名 to 新的表名;
4.截断表 truncate table 表名；
truncate table 表名 删除表的所有数据 保留表结构 不能回滚
/*2. DML语句 仅仅操作缓冲区的数据（可以回滚）对数据进行操作
都需要跟 事务(commit rollback)
配合使用才能影响表的真实数据
（Data Manipulation Language） 数据库操作语言
1： insert into 表名(列名1,........列名n)
values(值1,........值n) ;
2: update 表名 set 列名1=值1,....列名n=值n
[where 列名x=值x and or 列名x=值x ]
3:delete from 表名
[where 列名x=值x and or 列名x=值x ]
*/
select * from t_scottuser;
insert into t_scottuser(id,name,pass,status,age,birthday)
values(3,'王二','sssa','0',20,null);
commit ; /*rollback;*/
update t_scottuser set age=30 where status='1';
update t_scottuser set pass='admin',birthday=sysdate
where status='1';
delete from t_scottuser where status='1';
/*
4.dcl (数据库事务控制语言)(commit rollback)
5.dql（数据库的查询语言）select
1：select * from 表名 代表所有列
- 代表所有 *
-
- 模糊查询 like
- 通配符 % 代表任意多个字符 可以0个
-
- _代表任意一个字符
oracle 系统函数
1.字符函数
lower(列名) 把这列的值转换成小写
upper(列名) 把这列的值转换成大写
initcap(列名) 把这列首字母转换成大写
length(列名) 获取列里的长度
substr(列名，开始位置，长度) 获取列里的开始位置开始，数量长度的位数.
substr(列名，开始位置) 获取列里的开始位置开始 到末尾
replace(列名，'old','new')
lpad(列名,位数,‘值n’)在左边给列名的值用值n来补齐n位
rpad(列名,位数,‘值n’)在右边给列名的值用值n来补齐n位
字符的拼接
1. ||
- concat(string1,string2)
区别：
CONCAT只能连接两个字符串
--||可以连接多个字符串
eg：
/*
1:dml 数据库查询语言
*/
select ename 原名,lower(ename) 小写名 from emp
/*
:dml 查询名字长度大与5
*/
select * from emp where length(ename)>5;
/*
dml 查询名字前三位
*/
select ename ,substr(ename,1,3)from emp ;
/*
dml 查询名字首字母是M的雇员信息
*/
select * from emp where substr(ename,1,1)='M';
select * from emp where ename like 'M%';
/*
dml 查询名字倒数第二个是T的雇员信息
*/
select * from emp where substr(ename,length(ename)-1,1)='T';
select * from emp where ename like '%T_';
/*
dml 查询名字中的M替换成*
*/
select ename,replace(ename,'M','*')from emp;
/*
dml 查询名字保留前两位，其他替换成一个*
lpad(列名,位数,‘值n’)在左边给列名的值用值n来补齐n位
*/
/*
dml 查询名字保留前两位，其他都替换成*
*/
select ename,substr(ename,1,2),rpad(substr(ename,1,2),length(ename),'*') from emp;
/*
sss是做xxx工作的
*/
select ename||'是做' ||job||'工作的' from emp;
2.数值函数
round（列名）四舍五入
round（列名，n）四舍五入
n>0,对取小数点后n位四舍五入
n<0,对小数点前n位四舍五入
trunc（列名） 截断舍弃小数位
trunc（列名，n） 截断舍弃小数点后n位
eg：/*
四舍五入
*/
select sal,round(sal)from emp;
/*
保留一位小数四舍五入
*/
select sal,round(sal，1)from emp;
select sal,trunc(sal,1)from emp;
/*
3.时间函数
sysdate 当前时间
months_between(时间列1，时间列2） 列2-列1经历的月份数
ADD_MONTHS(date,months) 就可以以得到某一时间之前或之后n个月的时间，
last_day(时间列1) 返回 时间列所在的月份的最后一天的时间
next_day(时间列，1-7之间的值） 1-》星期日
2-》星期一 。。。
7-》星期天
下一个星期 值 后的时间 （往后推，离时间列最近的星期）
eg：
更新数据的两种方式
*/
select * from emp for update;
select e.*,rowid from emp e;
/*
虚表 sysdate 当前时间
*/
select sysdate from dual;
/*
查询雇员入职年限
*/
select ename,trunc(months_between(sysdate,hiredate)/12) from emp;
/*
查询出是所在月份倒数第二天入职的雇员信息
*/
select * from emp where hiredate =last_day(hiredate)-2;
/*
查询出是当前时间后一个小时的时间
*/
select sysdate+1/24 from dual;
/*
下一个星期 值 后的时间 （往后推，离时间列最近的星期）
*/
select next_day(sysdate ,1)from dual;
/*
4.转换函数
to_number(列名) 转换成number类型
to_char（列名) 转换成char 类型
to_date(列名，‘格式’ ) 字符串转换成date类型
转换成number类型
*/
select to_number('11')+11 from dual;
/*
转换成char类型
*/
select to_char(11)||'aa' from dual;
eg:to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')
/*
时间格式输入
*/
create table stu(
name varchar2(20),
birthday date
);
insert into stu (name,birthday)
values('b',to_date('1990-10-10','yyyy-MM-dd'));
select * from stu;
/*
格式化时间 格式
*/
select to_char(sysdate,'yyyy-MM-dd hh24:mi:ss')from dual;
5.通用函数
nvl（列名，值） 如果列名为null则返回所设置的值，否则返回原值
nvl2（列名，值1，值1 ） 如果列名为null则返回所设置的值2，否则返回值1
注：null与任何值做操作都是返回null
case 列名 when 值1 then 操作1
when 值2 then 操作2
。。。
else
默认操作
end；
decode（列名，值1， 返回值1，
值2， 返回值2，
。。。
值n，返回值n，
默认值）
eg：
/*
nvl（列名，值）
*/
select ename ,sal,comm,sal+nvl(comm,0)应发工资 from emp;
/*
第一种方式
给30部门加1000 。。。其他不变
*/
select ename ,sal,deptno,case deptno
when 30 then sal+1000
when 20 then sal+500
else sal
end
from emp;
/*
第二种方式
给30部门加1000 。。。其他不变
*/
select ename ,sal,deptno,decode(deptno,30,sal+1000,
20,sal+500,
sal )
from emp;
去重 distinct(列名)
分组管理
group by 列名1，列名2。。。
那么select 出来的地方只能是group by 后面的列名
或者是分组函数 max（列名） 求最大
min（列名） 求最小
avg（列名） 求平均
sum（列名） 求总和
count（列名）求记录条数
分组函数会自动忽略null值的项
分组函数 对组加条件 having
count（*） 表的总记录条数
count（comm）表中comm不为空的记录数
排序管理
order by 列名 asc（升序排列 默认）
desc（降序排列）
sql语句的执行顺序
from -》where -》select-》group by-》having->order by
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
