--DDL(数据定义语言)
--create,alter,drop
create table stuInfo
(
stuNo char(5) primary key,
stuName varchar(10),
stuSex char(2)
)
alter table stuInfo add constraint CK_STUNO
check(stuNo like 'S1[0-9][0-9][0-9]');
--alter table stuSex add constraint SK_STUNO
--check(stuSex like '男' or stuSex like '女');
alter table stuInfo add constraint CK_STUSEX
check(stuSex in ('男' ,'女'))
--课程表
create table course
(
courseId int not null,
couseName varchar(20)
)
alter table course add constraint PK_CIURSEID
PRIMARY KEY(courseId);
--学生成绩表
create table stuScore
(
stuNo char(5),
courseId int,
score int,
)
alter table stuScore add constraint FK_STUNO
foreign key(stuNo) references stuInfo(stuNo);
------建立外键
alter table stuScore add constraint FK_CORSEID
foreign key(courseId) references course(courseId);
---DML数据管理语言 --insert.delete.select.update
insert into stuInfo values('S1001','杭三','男')
insert into stuInfo values('S1002','李四','男')
insert into stuInfo values('S1003','王五','男')
insert into stuInfo values('S1004','小明','女')
insert into stuInfo values('S1005','小李','男')
insert into stuInfo values('S1011','小利','女')
insert into stuInfo values('S1012','王五','男')
insert into stuInfo values('S1009','小明','女')
insert into stuInfo values('S1008','小李','男')
insert into stuInfo values('S1007','小利','女')
select * from stuInfo
--delete from stuInfo
---查出姓李的全部同学名字
select stuName from stuInfo
where stuName like '小明'
---查出男同学的数量
select count(*) from stuInfo
insert into course values(1,'网络');
insert into course values(2,'JAVA');
insert into stuScore values('S1002','2','60')
insert into stuScore values('S1001','1','45')
insert into stuScore values('S1003','1','78')
insert into stuScore values('S1004','2','35')
--查出姓‘李’的全部学员名字
select stuName from stuInfo
where stuName like '李%'
--查出男同学的数量
select count(*) from stuInfo
where stuSex='男';
--查出不及格的同学
select stuName,stuNo from stuInfo where stuNo in (select stuNo from stuScore
where score<60)
create table stuCard
(
cardId int,
passwd char(6)
)
insert into stuCard values(101,'3o6o4')
insert into stuCard values(102,'3i2o23')
insert into stuCard values(103,'3i68o4')
------修改表中的数据把i和o 改成1和0
update stuCard set passwd=replace(replace(passwd,'i','1'),'o','0')
select replace('34o6o4','o','0')
select * from stuCard
select * from stuScore
select * from stuInfo
----内联接
select stuNo,temp.courseId,stuName,score,couseName from
(select aa.stuNo,courseId,stuName,score from
stuInfo aa
inner join
stuScore ss
on aa.stuNo=ss.stuNo) temp
inner join
course cc
on cc.courseId=temp.courseId
-----外联结
select aa.stuNo,courseId,stuName,score from
stuInfo aa
left outer join
stuScore ss
on aa.stuNo=ss.stuNo
---DCL数据控制语言---grant.revoke:权限控制
---返回第二门课前两名成绩信息
select top 2 * from stuScore
where courseId=2
order by score desc;
---返回第二门课
select top 2 * from course
where courseId=1
order by couseName desc;
----返回网络科第一名学员的成绩信息
select top 1 * from stuScore
where courseId=
(select courseId from course
where couseName='网络')
order by score desc;
----返回网络科第一名学员的名字信息
select * from stuInfo
where stuNo=
(select top 1 stuNo from stuScore
where courseId=
(select courseId from course
where couseName='网络')
order by score desc);
---返回百分比
select top 30 percent * from stuInfo
select * from stuInfo
---使用AS来命名
select stuNo as 学号 ,stuName as 姓名 from stuInfo
select courseId as ID号 ,couseName as 课程名 from course
---查询空值
select * from stuScore;
insert into stuScore(stuNo,courseId) values('S1003',1);
select * from stuScore
where score is null;
select * from stuScore
where score is not null;
---查询当前时间
select getdate();
----第二门课的平均分
select avg(score) from stuScore
where courseId=2;
----所有课程平均分
select courseId,avg(score) as 平均分 from stuScore
group by courseId;
select courseId,avg(score) as 平均分 from stuScore
group by courseId
having avg(score)>=45;
select courseId,avg(score) as 平均分 from stuScore
where score<60
group by courseId;
select * from stuScore
update stuScore set score=23 where stuNo='S1003';
---不及格学员的平均分(依课程不同)
select courseId,avg(score) as 平均分 from stuScore
where score<60
group by courseId;
---不及格学员的平均分在40分以下的信息(依课程不同)
select courseId,avg(score) as 平均分 from stuScore
where score<60
group by courseId
having avg(score)<40;
----求出学员的考试平均分
select stuNo,avg(score) from stuScore
group by stuNo;
----求出学员的考试平均分(列出平均成绩在60分以上)
select stuNo,avg(score) as 平均分 from stuScore
group by stuNo
having avg(score)>=60;
----求出学员的考试平均分(列出平均成绩在60分以上的学员名字和平均分)
select stuName,平均分
from stuInfo si,
(select stuNo,avg(score) as 平均分 from stuScore
group by stuNo
having avg(score)>=60) temp
where si.stuNo=temp.stuNo;
-----------------------------------------------------------------
/*建表：
1、employee(员工表)
empno,ename,job,deptno,salary
dept(部门表)
deptno,dname,location
2、查询工资在2000以上的员工信息
查询年收入在25000以上的员工信息
查询'李平'的办公地点
查询10号部门员工的平均工资
查询'研发部'员工的平均工资
查询平均工资2000以下的部门信息
*/------------------------------------
/*select location from dept where deptno=
(select deptno from employee where ename='李平');
select avg(salary) from employee where deptno=1001
select avg(salary) from employee where deptno=(select deptno from dept where dname='策划部');
select d.deptno,dname,location from dept d,
(select deptno,avg(salary) as ww from employee group by deptno having avg(salary)<3000) temp
where d.deptno=temp.deptno*/
create table employee
(
empno char(4) primary key,
ename varchar(10),
job varchar(20),
deptno char(4),
salary smallmoney
);
create table dept
(
deptno char(4) primary key,
dname varchar(20),
location varchar(20)
);
alter table employee add constraint FK_DEPTNO
foreign key(deptno) references dept(deptno);
insert into dept values('1001','策划部','郑州');
insert into dept values('2001','总部','北京');
insert into employee values('0001','张三','经理','1001','6568');
insert into employee values('0002','小丽','助理','1001','1578');
insert into employee values('0003','小明','经理','2001','9357');
insert into employee values('0004','李四','助理','2001','4903');
insert into employee values('0005','王五','普工','1001','1200');
insert into employee values('0006','李平','普工','1001','1000');
select * from employee
where salary>2000;
select salary from employee
where salary*12>25000;
select * from employee
where salary*12>25000;
select * from employee;
select * from dept;
select location from dept where deptno=
(select deptno from employee
where ename='李平');
select empno,ename,e.deptno,dname
from employee e,dept d
where e.deptno=d.deptno and ename='李平';
select avg(salary)
from employee
where deptno=1001;
select deptno,avg(salary)
from employee
--where deptno=1001
group by deptno;
select avg(salary)
from employee
where deptno=
(select deptno from dept where dname='总部');
select d.deptno,dname,location,tt
from dept d,
(select deptno,avg(salary) as tt
from employee
group by deptno
having avg(salary)<4000) tmp
where d.deptno=tmp.deptno
-----------------------------------------------------------------------
/*----创建角色成员----
create role tt;
-----给角色赋给表名称查询的权限-----
grant select on 表名称 to tt;
----把角色赋给用户----
grant tt to hu; */
----向stuinfo添加一列stuno---
alter table stuInfo add seatno int;
-----查询一个人的左右同桌------
begin
declare @seat int
select @seat= seatno from stuInfo where stuno='s102';
select * fromstINfo where seatno=@seat+1 or seatno=@seat-1;
end;
begin
declare @seat int
set @seat= (seatno from stuInfo where stuno='s102');
select * fromstINfo where seatno=@seat+1 or seatno=@seat-1;
end;
---查询SQL最后一个错---
select @@error
--------------------------------
begin
declare @avg int;
select @avg= avg(sclore) from stuScore;
if (@avg>=70)
begin
print '优秀';
select top 3 * from stuScore order by score desc;
end;
else
begin
print '较差';
select top 3 * from stuScore order by score ;
end;
--------------------------------------------------
create table card
(
carId int,
carName varchar(20)
)
select * from course
select * from card
begin
declare @card int;
set @card = 1;
while(@card<=10)
begin
insert into card values(@card,'text'+convert(varchar(6),@card));
set @card= @card+1;
end;
end;
select count(*) from stuScore where score<60;
select * from stuScore where score<60
begin
declare @o int;
select @o=count(*) from stuScore where score<60;
while(@o>0)
begin
update stuScore set score=score+2;
select @o=count(*) from stuScore where score<60;
end;
update stuScore set Score=100 where score>100;
end;
-----------------------------------------------------
create table bank
(
cusname varchar(20),
curmoney money
)
alter table bank add constraint CK_CURMONEY check (curmoney>0);
insert into bank values('张三',1000);
insert into bank values('李四',1);
select * from bank;
begin transaction
declare @errorsum int;
set @errorsum = 0;
update bank set curmoney=curmoney-999 where cusname='张三';
set @errorsum = @errorsum + @@error;
update bank set curmoney=curmoney+999 where cusname='李四';
set @errorsum = @errorsum + @@error;
if (@errorsum<>0)
begin
print '转账失败';
rollback transaction;
end;
else
begin
print '转账成功';
commit transaction;
end;
ALTER DATABASE bank SET READ_COMMITTED_SNAPSHOT OFF;
select @@trancount;
begin transaction
insert into bank values('王六',2000);
commit transaction;
rollback transaction;
-----------------------------------------------------------------
select * from stuScore;
insert into stuScore values('S1011',1,35);
insert into stuScore values('S1012',2,76);
insert into stuScore values('S1011',1,65);
insert into stuScore values('S1009',2,23);
insert into stuScore values('S1008',1,34);
insert into stuScore values('S1007',1,87);
insert into stuScore values('S1006',1,97);
insert into stuScore values('S1005',1,74);
select top 6 * from
(select top 10 * from stuScore order by score desc) A order by score asc
select * from stuScore where
-------------------------------------------------------------------
------输入一个id显示对应的分数------
create function func2(@sno char(5))
returns int
as
begin
declare @we int;
set @we=(select score from stuScore where stuNo=@sno);
return @we
end;
select dbo.func2('S1002');
---------------------------------------
------修改列名----
exec sys.sp_rename 'test4.id','no','colum'
----修改表名=-----
exec sys.sp_rename 'test4','ttt'
drop proc proc1
create procedure proc1
as
begin
declare @avg int;
set @avg = (select avg(score) from stuScore);
print '笔试平均分'+convert(varchar(5),@avg);
select * from stuScore where score<60;
end;
exec proc1
-------------------------------------
create procedure proc2 @lab int,@wri int
as
begin
select * from stuScore where labEcam
end;
exec proc2 50,70
------------------------------
create proc proc3 @fname varchar,@calc int output
as
begin
set @calc=(select count(*) from stuInfo
where stuName like +'李');
end;
----------------------------触发器-------------------
create table tab1
(
tabno char(5),
tabname varchar(20)
)
drop table log1
create table log1
(
tabno char(5),
tabname varchar(20),
opp varchar(20),
logdate datetime
)
drop table log1
create table log2
(
tabno char(5),
tabname varchar(20),
opp varchar(20),
logdate datetime,
)
drop trigger login2
create trigger login2
on tab1
for insert,delete
as
begin
if exists (select * from inserted) and not exists (select * from deleted)
begin
insert into log2(tabno,tabname) select * from inserted
update log2 set opp='insert',logdate=getdate()
insert into log1 select * from log2
end;
if exists (select * from inserted) and not exists (select * from deleted)
begin
insert into log2(tabno,tabname) select * from deleted
update log2 set opp='insert',logdate=getdate()
insert into log1 select * from log2
end;
end;
insert into tab1 values(2,'李四')
select * from tab1
select * from log1
select * from log2
create trigger login1
on tab1
for insert,delete,update
as
begin
if exists (select * from inserted) and not exists (select * from deleted)
begin
insert into log2(tabno,tabname,opp,logdate) select * from inserted;
update log2 set opp='insert', logtime=getdate();
insert into log1 select * from log2;
end;
if exists (select * from inserted) and not exists (select * from deleted)
begin
insert into log2(tabno,tabname,opp,logdate) select * from deleted;
update log2 set opp='delete', logtime=getdate();
insert into log1 select * from log2;
end;
end;
insert into table2 select * from table1 -----把1中的内容复制到2中
select * from stuInfo
-----------------------------------------------------------------------
select * from stuScore
select * from (select row_number() over(order by score desc) rr,
* from stuScore ) yy where rr>=5 and rr<=10;
select row_number() over(order by score desc), * from stuScore
alter table stuInfo add seatno int;
(select * from stuScore order by score desc)
------------视图创建------
create view vw_qqq
as
select stuNo,temp.courseId,stuName,score,couseName from
(select aa.stuNo,courseId,stuName,score from
stuInfo aa
inner join
stuScore ss
on aa.stuNo=ss.stuNo) temp
inner join
course cc
on cc.courseId=temp.courseId
select * from vw_qqq
--------数据库完整备份语法---------------------
use text
go
exec sp_addumpdevice 'disk','备份','g:\备份'
backup database text to 备份
-------------还原数据库的语法-----------
use master
go
restore database text from disk='g:\备份'
backup database text to disk='g:\beifen1.bak'
--------------------------------------------------------------------
---------------完整还原备份---------------------
backup database AdventureWorks to disk='g:\beifen2.bak'
select * from dbo.xiaoxi
insert into dbo.xiaoxi values(1,'xiaoli')
------------备份日志----------
backup log AdventureWorks to disk='g:\beifenog.bak'
insert into dbo.xiaoxi values(4,'xiaoming')
-----差异备份--------
backup database AdventureWorks to disk='g:\beifenchayi.bak' with differential
------------删除数据库---------------------
drop database AdventureWorks
-----------还原完整备份-----------
restore database AdventureWorks from disk='g:\beifen2.bak' with recovery
restore database AdventureWorks from disk='g:\beifenog.bak' with recovery
---------------去掉正在还原图标--------------------
restore database AdventureWorks from disk='g:\beifen2.bak'
restore database AdventureWorks from disk='g:\beifenog.bak' with norecovery
restore database AdventureWorks from disk='g:\beifenchayi.bak' with recovery
create table xiao1
(
xiano char(5),
xianame varchar(10)
)
backup database xiao to disk='g:\xiao1.bak'
insert into xiao1 values(1,'zhangsan')
backup log xiao to disk='g:\xiaolog.bak'
insert into xiao1 values(2,'lisi')
backup database xiao to disk='g:\xiaochayi.bak' with differential
restore database xiao from disk='g:\xiao1.bak' with norecovery
restore database xiao from disk='g:\xiaolog.bak' with recovery
select * from xiao1
backup database xiao to disk='g:\xiao2.bak'
insert into xiao1 values(3,'xiaoming')
backup log xiao to disk='g:\xiaolog2.bak'
insert into xiao1 values(5,'lisi1')
backup database xiao to disk='g:\xiaochayi2.bak' with differential
restore database xiao from disk='g:\xiao2.bak' with norecovery
restore log xiao from disk='g:\xiaolog2.bak' with recovery
restore database xiao from disk='g:\xiaochayi.bak' with recovery
----------------------------------------------------------------------
alter database test
add filegroup fg1
alter database test
add file
(name=tt1,
filename='f:\t\fg1_1.ndf',
size=10mb)
to filegroup fg1
create table table2
(id int,)
---------------完整还原备份---------------------
backup database AdventureWorks to disk='g:\beifen2.bak'
select * from dbo.xiaoxi
insert into dbo.xiaoxi values(1,'xiaoli')
------------备份日志----------
backup log AdventureWorks to disk='g:\beifenog.bak'
insert into dbo.xiaoxi values(4,'xiaoming')
-----差异备份--------
backup database AdventureWorks to disk='g:\beifenchayi.bak' with differential
------------删除数据库---------------------
drop database AdventureWorks
-----------还原完整备份-----------
restore database AdventureWorks from disk='g:\beifen2.bak' with recovery
restore database AdventureWorks from disk='g:\beifenog.bak' with recovery
---------------去掉正在还原图标--------------------
restore database AdventureWorks from disk='g:\beifen2.bak'
restore database AdventureWorks from disk='g:\beifenog.bak' with norecovery
restore database AdventureWorks from disk='g:\beifenchayi.bak' with recovery
create table xiao1
(
xiano char(5),
xianame varchar(10)
)
backup database xiao to disk='g:\xiao1.bak'
insert into xiao1 values(1,'zhangsan')
backup log xiao to disk='g:\xiaolog.bak'
insert into xiao1 values(2,'lisi')
backup database xiao to disk='g:\xiaochayi.bak' with differential
restore database xiao from disk='g:\xiao1.bak' with norecovery
restore database xiao from disk='g:\xiaolog.bak' with recovery
select * from xiao1
backup database xiao to disk='g:\xiao2.bak'
insert into xiao1 values(3,'xiaoming')
backup log xiao to disk='g:\xiaolog2.bak'
insert into xiao1 values(5,'lisi1')
backup database xiao to disk='g:\xiaochayi2.bak' with differential
restore database xiao from disk='g:\xiao2.bak' with norecovery
restore database xiao from disk='g:\xiaolog2.bak' with recovery
restore database xiao from disk='g:\xiaologchayi2.bak' with recovery
-------------------触发器示例----------------------
create or replace trigger tri_emp1
after insert or delete or update
on emp
for each row
begin
if inserting then
insert into empLog values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,
:new.sal,:new.comm,:new.deptno,'插入',sysdate);
elsif deleting then
insert into empLog values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,
:old.sal,:old.comm,:old.deptno,'删除',sysdate);
else
begin
insert into empLog values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,
:old.sal,:old.comm,:old.deptno,'更新前',sysdate);
insert into empLog values(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,
:new.sal,:new.comm,:new.deptno,'更新后',sysdate);
end;
end if;
end;
-------------------------过程函数示例--------------------------
create or replace function myfunc
return varchar2
as
v_orderId varchar2(12);
v_num int;
v_max varchar2(12);
begin
select count(*) into v_num from t_order where substr(orderId,3,8)=to_char(sysdate,'yyyymmdd');
if(v_num=0) then
v_orderId := 'DD'||to_char(sysdate,'yyyymmdd')||'01';
else
begin
select max(orderId) into v_max from t_order;
v_orderId:='DD'||to_char(sysdate,'yyyymmdd')||lpad(to_number(substr(v_max,11,2))+1,2,'0');
end;
end if;
return v_orderId;
end;
create or replace procedure myproc(p_orderId out varhcar2)
as
v_num int;
v_max varchar2(12);
begin
select count(*) into v_num from t_order where substr(orderId,3,8)=to_char(sysdate,'yyyymmdd');
if(v_num=0) then
p_orderId := 'DD'||to_char(sysdate,'yyyymmdd')||'01';
else
begin
select max(orderId) into v_max from t_order;
p_orderId:='DD'||to_char(sysdate,'yyyymmdd')||lpad(to_number(substr(v_max,11,2))+1,2,'0');
end;
end if;
end;
445
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
