- 浏览: 64243 次
- 性别:
- 来自: 珠海
文章分类
最新评论
-
fy616508150:
看得好有味。。就断了,楼主赶紧的~接下来的ukey是 不是带显 ...
谈谈网银和USB Key (五) -
上帝卖糕的:
报病毒~~
myBase Desktop 5 -
lg_techie:
正在学习和使用Spring,其中一些jar使人感到困惑。感谢! ...
spring需要的一些jar包 -
chenlb:
对,我也很喜欢,想找空学习学习.
只要是java程序员都会喜欢flex的. -
bruce.peng:
每个子页面都定义<mx:state> 吗?我在父类 ...
只要是java程序员都会喜欢flex的.
--创建数据
create database student
on
(--主要文件
name='student_data',
filename='d:\database\student_data.mdf',
size=1mb,
maxsize=3mb,
filegrowth=1
)
log on--逻辑日志文件
(
name=student_log,
filename='d:\database\student_log.ldf',
size=1,
maxsize=2,
filegrowth=10%
)
--修改数据库
alter database student
modify file
(
name='student_data',
maxsize=5
)
alter database student
add file
(--添加次要文件
name='student_data2',
filename='d:\database\student_data2.ndf',
size=2,
maxsize=3,
filegrowth=1
)
--删除数据库
drop database student
create database aa
drop database aa,student
--配置
exec sp_dboption 'pubs','read only','true'
exec sp_dboption 'pubs','read only','false'
--使用T-SQL创建数据库
create database accp
on primary
(name=accp_data,
filename='d:\备课资料\转换课程\sql\accp.mdf',
size=2Mb,
maxsize=3Mb,
filegrowth=10%
)--主要数据文件(必需有,只有一个)
,
(
name=accp_data1,
filename='d:\备课资料\转换课程\sql\accp.ndf',
size=2Mb,
maxsize=3Mb,
filegrowth=10%
)--次要数据文件(可以没有,也可以有多个)
log on
(
name=accp_log,
filename='d:\备课资料\转换课程\sql\accp.ldf',
size=1Mb,
maxsize=2Mb,
filegrowth=10%
)--数据日志文件(必需有,至少一个)
--使用T-SQL删除数据库
drop datebase accp
--使用系统存储过程配置数据库选项
--将数据库pubs设置为只读
exec sp_dboption 'pubs','read only',true
--将数据库pubs设置为自动周期性收缩
exec sp_dboption 'pubs','autoshrink',true
--将数据库pubs设置为只有一个用户
exec sp_dboption 'pubs','single user'
--使用DBCC命令收缩指定的数据库,并预留一定的空间
dbcc shrinkdatabase(pubs,10) --声明decimal类型变量,第一个参数为精确的位数,第二个为精确的小数位
declare @var1 numeric(6,3),@var2 decimal(6,5),@var3 decimal(6,4)
set @var1=123.78456;
set @var2=1.2345678;
set @var3=12.345678;
select '@var1'=@var1,'@var2'=@var2,'@var3'=@var3
--创建用户自定义数据类型
exec sp_addtype Phone,'varchar(11)',null
--创建表
create table book
(
book_id varchar(12) not null,
book_name varchar(20) not null,
book_publish varchar(20),
book_type varchar(10)not null
)
create table borrow
(
borrow_id int primary key identity(1,1),
book_id varchar(12) not null,
reader_id int not null ,
borrow_time datetime not null,
return_time datetime not null
)
create table reader
(
reader_id varchar(10) primary key,
reader_name varchar(20) not null,
reader_age tinyint not null,
reader_address varchar(50) not null
)
--修改表结构,为表添加主键约束
alter table book
add constraint pk_book1 primary key(book_id)
--修改表结构,为表添加唯一约束
alter table book
add constraint pk_book2 unique(book_id)
--修改表结构,为表添加非空和默认约束
alter table book
add book_qty int not null default '1'
--修改表结构,为表添加检查约束
alter table book
add constraint pk_book3 check(book_qty>0)
--为表中列添加标识列约束,只能在创建列时添加
create table book_user
(
bookid int identity(1000,2) primary key,
bookname varchar(12)
)
--查找表中的记录
select * from book_user
--修改表中已有列的数据类型
alter table borrow
alter column reader_id varchar(10) not null
select * from reader
--删除表
drop table book_user
--为表实施外键约束
alter table borrow
add constraint fk_reader
foreign key(reader_id) references reader(reader_id)
alter table borrow
add constraint fk_book
foreign key(book_id) references book(book_id)
--删除约束
alter table book
drop constraint pk_book2
--添加用户数据类型
exec sp_addtype A_name,'varchar(20)','NULL'
--删除用户数据类型
exec sp_droptype A_name
--创建表
create table student
(
s_id varchar(20) primary key,--创建主键
s_name varchar(20) not null,
sex char(2) not null,
age tinyint not null,
phone varchar(20) not null,
address varchar(50)
)
--创建唯一和标识列约束,只能在字段创建时添加
create table aa
(
a_id int identity(1,1),
a_name varchar(10)
)
drop table aa
--添加主键约束
alter table aa
add constraint pk_aa
primary key(a_id)
--添加唯一约束
alter table aa
add constraint uq_name
unique(a_name)
--删除列
alter table aa
drop column a_id
--添加字段,增加标识列约束
alter table aa
add aa_id int identity(1,3)
--唯一标识
create table bb
(
b_id uniqueidentifier default newid(),
b_name varchar(10)
)
--创建成绩表
drop table 成绩
--创建外键约束
create table 成绩
(
编号 int identity(1,1) primary key,
学号 varchar(20) not null,
-- foreign key references student(s_id),--外键约束
课程 varchar(20) default 'vb' ,
成绩 int not null default 60
)
--添加外键约束
alter table 成绩
add constraint fk_学号
foreign key(学号)
references student(s_id)
--添加检查约束
alter table student
add constraint ck_age
check (age>1)
--删除约束
alter table student
drop constraint ck_student
alter table student
drop column age
create table book
(
book_id varchar(12) primary key,
book_name varchar(12) not null,
book_publish varchar(20) not null
)
create table reader
(
reader_id varchar(12) primary key,
reader_name varchar(20) not null,
reader_age int not null
)
create table borrow
(
borrow_id int identity(1000,1),
book_id varchar(12) foreign key(book_id) references book(book_id),
reader_id varchar(12) foreign key(reader_id) references reader(reader_id),
borrow_time datetime default getdate(),
return_time varchar(15) not null
)
--向book表中添加记录
insert book values
('book0001','sql2000','chubanshe')
insert book values
('book0002','sql2000','chubanshe')
select * from book
--向reader表添加记录
insert reader values('reader0001','rose',20)
insert reader values('reader0002','jhon',30)
--向从表中添加记录
insert into borrow(book_id,reader_id,return_time)
values
('book0002','reader0002','未还')
delete from borrow where borrow_time='2003.4.8'
insert borrow(reader_id,book_id,borrow_time,return_time)
select reader_id,book_id,btime,rtime
from table1 where btime>'2003.4.9'
select * from borrow
delete from borrow where book_id='book0002'
select * from book
--内部联接
select a.book_id,a.book_name,b.borrow_time
from book as a inner join borrow as b
on a.book_id=b.book_id
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--右外部联接(引用完整性)
select a.book_id,a.book_name,b.borrow_time
from book as a right outer join borrow as b
on a.book_id=b.book_id
--右外部连接
select btime,b.book_id,b.reader_id
from table1 a right outer join borrow b
on a.book_id=b.book_id
select * from borrow
select * from table1
--更新记录
update table1
set book_id='book0001',reader_id='reader0002'
where book_id='book0002'
select * from table1
update table1
set rtime='未还'
select * from table1
delete from table1 where rtime='未还'
--删除表中的数据
truncate table borrow
truncate table book
delete from book
select * from book
--关系运算符like
select * from authors
where au_lname like 'w%'
--%
--[]
--[^]
--_
--向表中添加记录
insert into book values('book0001','vb','人民出版社','图书',20)
insert into reader values('reader0001','rose',20,'road no.1')
alter table borrow
alter column return_time varchar(12) not null
insert into borrow(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--创建表brw
create table brw
(
book_id varchar(12) not null,
reader_id varchar(20) not null,
borrow_time datetime not null,
return_time varchar(10) not null
)
--向brw表中添加记录
insert into brw(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--将brw表中的记录添加到borrow表中
insert into borrow
select book_id,reader_id,borrow_time,return_time from brw
select * from borrow
--根据条件改更表的记录
update book
set book_name='sql'
from book as a right outer join borrow as b on a.book_id =b.book_id
where a.book_id='book0001'
--删除表中的所有记录
delete from borrow
--删除表中满足条件的记录
delete from borrow where book_id="book0001"
--创建数据库
create database student
on
(
name='student_data',
filename='d:\stu\student_data.mdf',
size=2,
maxsize=4,
filegrowth=1
)
log on
(
name='student_log',
filename='d:\stu\student_log.ldf',
size=1,
maxsize=2,
filegrowth=1
)
--学生信息表
create table student
(
s_id varchar(20) primary key,--主键
s_name varchar(20) not null,--非空
sex varchar(4) not null default '男' check(sex='男' or sex='女'),--非空默认检查
phone varchar(20) not null default 'no phone'
)
--学生成绩表
create table chj
(
ch_id int identity(1,1) primary key,
s_id varchar(20) not null,
course varchar(20) not null,
chj int check(chj>=0 and chj<=100)
)
--添加外键
alter table chj
add constraint fk_s_id
foreign key(s_id)
references student(s_id)
--借阅信息表
create table borrowmessage
(
borrow_index int identity(1,1) primary key,
bookindex varchar(20) not null,
readerindex varchar(20) not null,
borrow_time datetime not null check(borrow_time<=getdate()),--字段级检查
return_time datetime not null
)
--添加记录级检查约束(或表级)
alter table borrowmessage
add constraint ck_time
check(borrow_time <=return_time)
--删除列
alter table borrowmessage
drop column borrow_time
--删除约束
alter table borrowmessage
drop constraint ck_time
--添加列
alter table borrowmessage
add borrow_time datetime
not null default getdate()
--添加数据
insert into student
values('0404s1a2004','dd','男','130000000')
--查询数据
select * from student2
select * from chj
insert chj
values('0404s1a2004','lgc',80)
--基于现有表中的数据,向目的表追加数据
insert student2
select s_id,s_name,sex,phone
from student
where s_id='0404s1a2010'
--修改数据
select * from student2
update student2
set sex='女',s_name='李四'
where s_id='0404s1a2002'
--多个表数据的更新
--内部连接
select s_name,course,chj
from student s inner join chj as c--别名
on s.s_id=c.s_id
--左外部连接
select s_name,course,chj
from student s left outer join chj as c--别名
on s.s_id=c.s_id
--右外部连接(引用完整)
select s_name,course,chj
from student s right outer join chj as c--别名
on s.s_id=c.s_id
select * from chj
--右外部连接(无约束)
select s_name,course,chj
from chj s right outer join student2 as c--别名
on s.s_id=c.s_id
--
create table emp
(
emp_id varchar(10) primary key,
emp_name varchar(10),
leader varchar(10)
)
select * from emp
insert emp
values('5','ee','')
--内部连接
select a.emp_name '员工',b.emp_name as '经理姓名'
from emp a inner join emp b
on a.leader=b.emp_id
delete from emp
where emp_id='1'
truncate table student
select * from student
truncate table chj
delete student
create table book_user
(
Buser_id char(4) primary key,
Buser_name char(10)not null
)
create table user_pa
(
Buser_id char(4) not null,
Buser_pass char(4) not null
)
drop table book_user
insert into book_user values('2','bb')
select * from book_user
truncate table book_user
alter table user_pa
add constraint fk_user foreign key(Buser_id) references book_user(Buser_id)
insert into user_pa values('1','aa')
delete from book_user
select stor_id,ord_num from sales
select stor_id from stores
select a.stor_id,a.ord_num,b.stor_name
from sales as a,stores as b
where a.stor_id=b.stor_id
select stor_id,qty from sales
order by qty desc,stor_id
create table borrow
(
borrow_id int identity(1000,1) primary key,
borrow_name varchar(20) not null
)
insert into borrow(borrow_name) values('sql')
select identity(int,1000,2) as brw_id
into brw
from borrow
select * from brw
alter table brw
add brw_name varchar(10)
insert into brw(brw_name) values('vb')
update brw
set brw_name='sql'
where brw_name is null
select * from brw
select top 10 percent * from sales order by qty desc
select count(*) from sales
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(*) as 'count_J'
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(b.borrow_time)
from book as a left outer join borrow as b
on a.book_id=b.book_id
select * from sales
select stor_id,avg(qty) as 'sum of qty '
from sales
where ord_num='6871'
group by stor_id
having avg(qty)=20
and stor_id='8042'
select avg(qty),stor_id from sales
--where avg(qty)>20
select * from sales
where title_id like '%8%'
--between and
select * from sales
where qty between 20 and 100 and qty<>20 and qty<>100
--in
select * from sales
where qty in (20,30,50)
--or
select * from sales
where qty=20 or qty=30 or qty=50
--time
declare @u_time as varchar(20)
SET @U_time=datepart(mi,getdate())
select '时间'=@u_time
select au_lname +'::'+au_fname as 'name'
from authors
select au_id,au_lname,au_fname,phone,state
from authors
where not state='ca'
order by au_lname desc
--order by
select stor_id,qty from sales
order by qty,stor_id
--查询中的常量
select au_lname+'.'+au_fname '姓名'
from authors
--表的别名
select a.au_lname 姓,au_fname
from authors as a
--生成表查询
select au_lname,au_fname
into authors_copy--新表名
from authors--现有的表名
where state='ca'
select identity(int,1,2) as '标识'
into au
from authors_copy
select * from au
--top
select top 1 percent au_lname,au_fname
from authors
where state='ca'
select top 3 * from sales order by qty desc
select distinct qty from sales
--group by
select top 1 stor_id,min(ord_num) as 最小值,avg(qty) 平均数量
from sales
group by all stor_id
--having avg(qty)>100
order by avg(qty) desc
select stor_id,ord_num,qty
from sales
group by stor_id,ord_num,qty
select stor_id,sum(qty)
from sales
where stor_id=7066
group by stor_id
select stor_id,sum(qty)
from sales
group by stor_id
having stor_id=7066
--like
select au_lname,au_fname
from authors
where au_lname like '_e%e_'
and au_fname like '[^a,e,i,o,u]%'
数据类型 字节大小 范围 说明
整数类型 Bigint 8
Int 4 -2147483648—2147483647
Smallint 2 -32768--32767
Tinyint 1 0-255
Bit 0或1
小数类型 Decimal 与精度有关 -10^38-1—10^38-1 Decimal(12)
或Decimal(12,4)
Numeric 与精度有关 -10^38-1—10^38-1 同上
近似数值类型 Float 8 -1.79E-308—1.79E+308
Real 4 -3.40E-38—3.40E38
货币类型 Money 8 非常大
Smallmoney 4 -214748.3648—214748.3648
日期时间 Datetime 8 1753-1-1---9999-12-3
Smalldatetime 4 1900-1-1---2079-6-6
字符类型 Char <=8000个字符(定长)
Varchar <=8000个字符(不定长)
Text <=2G个字符(不定长)
Nchar <=4000个字符(定长)
Nvarchar <=4000个字符(不定长)
Ntext <=1G个字符(不定长)
二进制类型 Binary <=8000字节(定长)
Varbinary <=8000字节(不定长)
Image <=2^31-1字节(不定长) 声音、图像等
select stor_id,ord_num from sales
select stor_id from stores
select a.stor_id,a.ord_num,b.stor_name
from sales as a,stores as b
where a.stor_id=b.stor_id
select stor_id,qty from sales
order by qty desc,stor_id
create table borrow
(
borrow_id int identity(1000,1) primary key,
borrow_name varchar(20) not null
)
insert into borrow(borrow_name) values('sql')
select identity(int,1000,2) as brw_id
into brw
from borrow
select * from brw
alter table brw
add brw_name varchar(10)
insert into brw(brw_name) values('vb')
update brw
set brw_name='sql'
where brw_name is null
select * from brw
select top 10 percent * from sales order by qty desc
select count(*) from sales
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(*) as 'count_J'
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(b.borrow_time)
from book as a left outer join borrow as b
on a.book_id=b.book_id
select * from sales
select stor_id,avg(qty) as 'sum of qty '
from sales
where ord_num='6871'
group by stor_id
having avg(qty)=20
and stor_id='8042'
select avg(qty),stor_id from sales
--where avg(qty)>20
select * from sales
where title_id like '%8%'
--between and
select * from sales
where qty between 20 and 100 and qty<>20 and qty<>100
--in
select * from sales
where qty in (20,30,50)
--or
select * from sales
where qty=20 or qty=30 or qty=50
--time
declare @u_time as varchar(20)
SET @U_time=datepart(mi,getdate())
select '时间'=@u_time
select au_lname +'::'+au_fname as 'name'
from authors
--使用T-SQL创建数据库
create datebase accp
on primary
(name=accp_mdf,
filename='d:\备课资料\转换课程\sql\accp.mdf',
size=2M,
maxsize=3M,
filegrowth=10%
)--主要数据文件(必需有,只有一个)
(
name=accp_mdf,
filename='d:\备课资料\转换课程\sql\accp.mdf',
size=2M,
maxsize=3M,
filegrowth=10%
)--次要数据文件(可以没有,也可以有多个)
log on
(
name=accp_ldf,
filename='d:\备课资料\转换课程\sql\accp.ldf',
size=1M,
maxsize=2M,
filegrowth=10%
)--数据日志文件(必需有,至少一个)
--使用T-SQL删除数据库
drop datebase accp
--使用系统存储过程配置数据库选项
--将数据库pubs设置为只读
exec sp_dboption 'pubs','read only',true
--将数据库pubs设置为自动周期性收缩
exec sp_dboption 'pubs','autoshrink',true
--将数据库pubs设置为只有一个用户
exec sp_dboption 'pubs','single user'
--使用DBCC命令收缩指定的数据库,并预留一定的空间
dbcc shrinkdatabase(pubs,10)
create table book_user
(
Buser_id char(4) primary key,
Buser_name char(10)not null
)
create table user_pa
(
Buser_id char(4) not null,
Buser_pass char(4) not null
)
drop table book_user
insert into book_user values('2','bb')
select * from book_user
truncate table book_user
alter table user_pa
add constraint fk_user foreign key(Buser_id) references book_user(Buser_id)
insert into user_pa values('1','aa')
delete from book_user
--关系运算符like
select * from authors
where au_lname like 'w%'
--%
--[]
--[^]
--_
--向表中添加记录
insert into book values('book0001','vb','人民出版社','图书',20)
insert into reader values('reader0001','rose',20,'road no.1')
alter table borrow
alter column return_time varchar(12) not null
insert into borrow(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--创建表brw
create table brw
(
book_id varchar(12) not null,
reader_id varchar(20) not null,
borrow_time datetime not null,
return_time varchar(10) not null
)
--向brw表中添加记录
insert into brw(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--将brw表中的记录添加到borrow表中
insert into borrow
select book_id,reader_id,borrow_time,return_time from brw
select * from borrow
--根据条件改更表的记录
update book
set book_name='sql'
from book as a right outer join borrow as b on a.book_id =b.book_id
where a.book_id='book0001'
--删除表中的所有记录
delete from borrow
--删除表中满足条件的记录
delete from borrow where book_id="book0001"
create table book
(
book_id varchar(12) primary key,
book_name varchar(12) not null,
book_publish varchar(20) not null
)
create table reader
(
reader_id varchar(12) primary key,
reader_name varchar(20) not null,
reader_age int not null
)
create table borrow
(
borrow_id int identity(1000,1),
book_id varchar(12) foreign key(book_id) references book(book_id),
reader_id varchar(12) foreign key(reader_id) references reader(reader_id),
borrow_time datetime default getdate(),
return_time varchar(15) not null
)
--向book表中添加记录
insert book values
('book0001','sql2000','chubanshe')
insert book values
('book0002','sql2000','chubanshe')
select * from book
--向reader表添加记录
insert reader values('reader0001','rose',20)
insert reader values('reader0002','jhon',30)
--向从表中添加记录
insert into borrow(book_id,reader_id,return_time)
values
('book0002','reader0002','未还')
delete from borrow where borrow_time='2003.4.8'
insert borrow(reader_id,book_id,borrow_time,return_time)
select reader_id,book_id,btime,rtime
from table1 where btime>'2003.4.9'
select * from borrow
delete from borrow where book_id='book0002'
select * from book
--内部联接
select a.book_id,a.book_name,b.borrow_time
from book as a inner join borrow as b
on a.book_id=b.book_id
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--右外部联接(引用完整性)
select a.book_id,a.book_name,b.borrow_time
from book as a right outer join borrow as b
on a.book_id=b.book_id
--右外部连接
select btime,b.book_id,b.reader_id
from table1 a right outer join borrow b
on a.book_id=b.book_id
select * from borrow
select * from table1
--更新记录
update table1
set book_id='book0001',reader_id='reader0002'
where book_id='book0002'
select * from table1
update table1
set rtime='未还'
select * from table1
delete from table1 where rtime='未还'
--删除表中的数据
truncate table borrow
truncate table book
delete from book
select * from book
--声明decimal类型变量,第一个参数为精确的位数,第二个为精确的小数位
declare @var1 numeric(6,3),@var2 decimal(6,5),@var3 decimal(6,4)
set @var1=123.78456;
set @var2=1.2345678;
set @var3=12.345678;
select '@var1'=@var1,'@var2'=@var2,'@var3'=@var3
--创建用户自定义数据类型
exec sp_addtype Phone,'varchar(11)',null
--创建表
create table book
(
book_id varchar(12) not null,
book_name varchar(20) not null,
book_publish varchar(20),
book_type varchar(10)not null
)
create table borrow
(
borrow_id int primary key identity(1,1),
book_id varchar(12) not null,
reader_id int not null ,
borrow_time datetime not null,
return_time datetime not null
)
create table reader
(
reader_id varchar(10) primary key,
reader_name varchar(20) not null,
reader_age tinyint not null,
reader_address varchar(50) not null
)
--修改表结构,为表添加主键约束
alter table book
add constraint pk_book1 primary key(book_id)
--修改表结构,为表添加唯一约束
alter table book
add constraint pk_book2 unique(book_id)
--修改表结构,为表添加非空和默认约束
alter table book
add book_qty int not null default '1'
--修改表结构,为表添加检查约束
alter table book
add constraint pk_book3 check(book_qty>0)
--为表中列添加标识列约束,只能在创建列时添加
create table book_user
(
bookid int identity(1000,2) primary key,
bookname varchar(12)
)
--查找表中的记录
select * from book_user
--修改表中已有列的数据类型
alter table borrow
alter column reader_id varchar(10) not null
select * from reader
--删除表
drop table book_user
--为表实施外键约束
alter table borrow
add constraint fk_reader
foreign key(reader_id) references reader(reader_id)
alter table borrow
add constraint fk_book
foreign key(book_id) references book(book_id)
--删除约束
alter table book
drop constraint pk_book2
-- ==========================MySql ===========================
---游标和存储过程
/** add the Residential note Other category to all existing investor connections */
DROP PROCEDURE IF EXISTS insertInvestorCategories;
CREATE PROCEDURE insertInvestorCategories()
BEGIN
DECLARE investorId INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
select ic.investor_connection_id
FROM investor_connection ic
where ic.investor_connection_id not in (select icInv.investor_connection_id from investor_connection_investment icInv);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur into investorId;
IF NOT done THEN
insert into investor_connection_category (investor_connection_id, investment_category_id)
values (investorId, (select investment_category_id from investment_category where investment_category_key="LLI_PN_RES_LOAN_TYPE_OTHER"));
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END;
call insertInvestorCategories();
DROP PROCEDURE IF EXISTS insertInvestorCategories;
-- ===================================
DROP PROCEDURE IF EXISTS switchCategoryLoanType;
CREATE PROCEDURE switchCategoryLoanType()
BEGIN
DECLARE categoryId INT DEFAULT 0;
DECLARE loanTypeId INT DEFAULT 0;
DECLARE investmentId INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
select inv.investment_id,p.loan_type_id, inv.investment_category_id
listing_option_id
FROM promissory_note p inner join investment inv on p.investment_id = inv.investment_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur into investmentId, loanTypeId, categoryId;
IF NOT done THEN
update promissory_note promnote
inner join investment invest on promnote.investment_id = invest.investment_id
set promnote.loan_type_id=
(select lli.lookup_list_item_id from lookup_list_item lli
where lli.list_key = (select cat.investment_category_key from investment_category cat
where cat.investment_category_id = categoryId)
)
where invest.investment_id=investmentId;
update investment invest
inner join promissory_note promnote on promnote.investment_id = invest.investment_id
set invest.investment_category_id=
(select cat.investment_category_id from investment_category cat
where cat.investment_category_key = (select lli.list_key from lookup_list_item lli
where lli.lookup_list_item_id = loanTypeId)
)
where invest.investment_id=investmentId;
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END;
call switchCategoryLoanType();
DROP PROCEDURE IF EXISTS switchCategoryLoanType;
-- ================================
-- update table, first need remove the foreign key
set FOREIGN_KEY_CHECKS = 0;
set @oldID := (select sct2.sch_task_id from scheduler_tasks sct2
where sct2.sch_task_name = 'Item Status Notification');
set @maxID := (select max(sct1.sch_task_id) from scheduler_tasks sct1)+1;
update scheduler_tasks_config stc
set stc.sch_task_id = @maxID
where stc.sch_task_id = @oldID;
update scheduler_tasks sct
set sct.sch_task_id = @maxID
where sct.sch_task_name = 'Item Status Notification';
set FOREIGN_KEY_CHECKS = 1;
===添加列 和 注释
alter table `item`
add is_delete INT(1) default 0 comment 'indicates if the item has been deleted from the user I-Page';
======================
-- 忘记密码,重新设置
-- 跳过权限检查启动MySQL
c:\mysql\bin>mysqld -nt --skip-grant-tables
-- 重新打开一个命令窗口,进入c:\mysql\bin目录,设置root的新密码
c:\mysql\bin>mysqladmin -u root flush-privileges password "newpassword"
c:\mysql\bin>mysqladmin -u root -p shutdown
-------------------------------------------
-- oracle 循环插入5W条数据
declare
maxrecords constant int:=50000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into bigdatatest(bigdatatest_id,title,data_type_code)
values(i+10,TO_CHAR('9999'+i),TO_CHAR('9999'+i));
end loop;
dbms_output.put_line(' 成功录入数据! ');
commit;
end;
-------------------------------------------
oracle 数据导出:
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"
上面是常用的导出,对于压缩,既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y
上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)
create database student
on
(--主要文件
name='student_data',
filename='d:\database\student_data.mdf',
size=1mb,
maxsize=3mb,
filegrowth=1
)
log on--逻辑日志文件
(
name=student_log,
filename='d:\database\student_log.ldf',
size=1,
maxsize=2,
filegrowth=10%
)
--修改数据库
alter database student
modify file
(
name='student_data',
maxsize=5
)
alter database student
add file
(--添加次要文件
name='student_data2',
filename='d:\database\student_data2.ndf',
size=2,
maxsize=3,
filegrowth=1
)
--删除数据库
drop database student
create database aa
drop database aa,student
--配置
exec sp_dboption 'pubs','read only','true'
exec sp_dboption 'pubs','read only','false'
--使用T-SQL创建数据库
create database accp
on primary
(name=accp_data,
filename='d:\备课资料\转换课程\sql\accp.mdf',
size=2Mb,
maxsize=3Mb,
filegrowth=10%
)--主要数据文件(必需有,只有一个)
,
(
name=accp_data1,
filename='d:\备课资料\转换课程\sql\accp.ndf',
size=2Mb,
maxsize=3Mb,
filegrowth=10%
)--次要数据文件(可以没有,也可以有多个)
log on
(
name=accp_log,
filename='d:\备课资料\转换课程\sql\accp.ldf',
size=1Mb,
maxsize=2Mb,
filegrowth=10%
)--数据日志文件(必需有,至少一个)
--使用T-SQL删除数据库
drop datebase accp
--使用系统存储过程配置数据库选项
--将数据库pubs设置为只读
exec sp_dboption 'pubs','read only',true
--将数据库pubs设置为自动周期性收缩
exec sp_dboption 'pubs','autoshrink',true
--将数据库pubs设置为只有一个用户
exec sp_dboption 'pubs','single user'
--使用DBCC命令收缩指定的数据库,并预留一定的空间
dbcc shrinkdatabase(pubs,10) --声明decimal类型变量,第一个参数为精确的位数,第二个为精确的小数位
declare @var1 numeric(6,3),@var2 decimal(6,5),@var3 decimal(6,4)
set @var1=123.78456;
set @var2=1.2345678;
set @var3=12.345678;
select '@var1'=@var1,'@var2'=@var2,'@var3'=@var3
--创建用户自定义数据类型
exec sp_addtype Phone,'varchar(11)',null
--创建表
create table book
(
book_id varchar(12) not null,
book_name varchar(20) not null,
book_publish varchar(20),
book_type varchar(10)not null
)
create table borrow
(
borrow_id int primary key identity(1,1),
book_id varchar(12) not null,
reader_id int not null ,
borrow_time datetime not null,
return_time datetime not null
)
create table reader
(
reader_id varchar(10) primary key,
reader_name varchar(20) not null,
reader_age tinyint not null,
reader_address varchar(50) not null
)
--修改表结构,为表添加主键约束
alter table book
add constraint pk_book1 primary key(book_id)
--修改表结构,为表添加唯一约束
alter table book
add constraint pk_book2 unique(book_id)
--修改表结构,为表添加非空和默认约束
alter table book
add book_qty int not null default '1'
--修改表结构,为表添加检查约束
alter table book
add constraint pk_book3 check(book_qty>0)
--为表中列添加标识列约束,只能在创建列时添加
create table book_user
(
bookid int identity(1000,2) primary key,
bookname varchar(12)
)
--查找表中的记录
select * from book_user
--修改表中已有列的数据类型
alter table borrow
alter column reader_id varchar(10) not null
select * from reader
--删除表
drop table book_user
--为表实施外键约束
alter table borrow
add constraint fk_reader
foreign key(reader_id) references reader(reader_id)
alter table borrow
add constraint fk_book
foreign key(book_id) references book(book_id)
--删除约束
alter table book
drop constraint pk_book2
--添加用户数据类型
exec sp_addtype A_name,'varchar(20)','NULL'
--删除用户数据类型
exec sp_droptype A_name
--创建表
create table student
(
s_id varchar(20) primary key,--创建主键
s_name varchar(20) not null,
sex char(2) not null,
age tinyint not null,
phone varchar(20) not null,
address varchar(50)
)
--创建唯一和标识列约束,只能在字段创建时添加
create table aa
(
a_id int identity(1,1),
a_name varchar(10)
)
drop table aa
--添加主键约束
alter table aa
add constraint pk_aa
primary key(a_id)
--添加唯一约束
alter table aa
add constraint uq_name
unique(a_name)
--删除列
alter table aa
drop column a_id
--添加字段,增加标识列约束
alter table aa
add aa_id int identity(1,3)
--唯一标识
create table bb
(
b_id uniqueidentifier default newid(),
b_name varchar(10)
)
--创建成绩表
drop table 成绩
--创建外键约束
create table 成绩
(
编号 int identity(1,1) primary key,
学号 varchar(20) not null,
-- foreign key references student(s_id),--外键约束
课程 varchar(20) default 'vb' ,
成绩 int not null default 60
)
--添加外键约束
alter table 成绩
add constraint fk_学号
foreign key(学号)
references student(s_id)
--添加检查约束
alter table student
add constraint ck_age
check (age>1)
--删除约束
alter table student
drop constraint ck_student
alter table student
drop column age
create table book
(
book_id varchar(12) primary key,
book_name varchar(12) not null,
book_publish varchar(20) not null
)
create table reader
(
reader_id varchar(12) primary key,
reader_name varchar(20) not null,
reader_age int not null
)
create table borrow
(
borrow_id int identity(1000,1),
book_id varchar(12) foreign key(book_id) references book(book_id),
reader_id varchar(12) foreign key(reader_id) references reader(reader_id),
borrow_time datetime default getdate(),
return_time varchar(15) not null
)
--向book表中添加记录
insert book values
('book0001','sql2000','chubanshe')
insert book values
('book0002','sql2000','chubanshe')
select * from book
--向reader表添加记录
insert reader values('reader0001','rose',20)
insert reader values('reader0002','jhon',30)
--向从表中添加记录
insert into borrow(book_id,reader_id,return_time)
values
('book0002','reader0002','未还')
delete from borrow where borrow_time='2003.4.8'
insert borrow(reader_id,book_id,borrow_time,return_time)
select reader_id,book_id,btime,rtime
from table1 where btime>'2003.4.9'
select * from borrow
delete from borrow where book_id='book0002'
select * from book
--内部联接
select a.book_id,a.book_name,b.borrow_time
from book as a inner join borrow as b
on a.book_id=b.book_id
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--右外部联接(引用完整性)
select a.book_id,a.book_name,b.borrow_time
from book as a right outer join borrow as b
on a.book_id=b.book_id
--右外部连接
select btime,b.book_id,b.reader_id
from table1 a right outer join borrow b
on a.book_id=b.book_id
select * from borrow
select * from table1
--更新记录
update table1
set book_id='book0001',reader_id='reader0002'
where book_id='book0002'
select * from table1
update table1
set rtime='未还'
select * from table1
delete from table1 where rtime='未还'
--删除表中的数据
truncate table borrow
truncate table book
delete from book
select * from book
--关系运算符like
select * from authors
where au_lname like 'w%'
--%
--[]
--[^]
--_
--向表中添加记录
insert into book values('book0001','vb','人民出版社','图书',20)
insert into reader values('reader0001','rose',20,'road no.1')
alter table borrow
alter column return_time varchar(12) not null
insert into borrow(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--创建表brw
create table brw
(
book_id varchar(12) not null,
reader_id varchar(20) not null,
borrow_time datetime not null,
return_time varchar(10) not null
)
--向brw表中添加记录
insert into brw(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--将brw表中的记录添加到borrow表中
insert into borrow
select book_id,reader_id,borrow_time,return_time from brw
select * from borrow
--根据条件改更表的记录
update book
set book_name='sql'
from book as a right outer join borrow as b on a.book_id =b.book_id
where a.book_id='book0001'
--删除表中的所有记录
delete from borrow
--删除表中满足条件的记录
delete from borrow where book_id="book0001"
--创建数据库
create database student
on
(
name='student_data',
filename='d:\stu\student_data.mdf',
size=2,
maxsize=4,
filegrowth=1
)
log on
(
name='student_log',
filename='d:\stu\student_log.ldf',
size=1,
maxsize=2,
filegrowth=1
)
--学生信息表
create table student
(
s_id varchar(20) primary key,--主键
s_name varchar(20) not null,--非空
sex varchar(4) not null default '男' check(sex='男' or sex='女'),--非空默认检查
phone varchar(20) not null default 'no phone'
)
--学生成绩表
create table chj
(
ch_id int identity(1,1) primary key,
s_id varchar(20) not null,
course varchar(20) not null,
chj int check(chj>=0 and chj<=100)
)
--添加外键
alter table chj
add constraint fk_s_id
foreign key(s_id)
references student(s_id)
--借阅信息表
create table borrowmessage
(
borrow_index int identity(1,1) primary key,
bookindex varchar(20) not null,
readerindex varchar(20) not null,
borrow_time datetime not null check(borrow_time<=getdate()),--字段级检查
return_time datetime not null
)
--添加记录级检查约束(或表级)
alter table borrowmessage
add constraint ck_time
check(borrow_time <=return_time)
--删除列
alter table borrowmessage
drop column borrow_time
--删除约束
alter table borrowmessage
drop constraint ck_time
--添加列
alter table borrowmessage
add borrow_time datetime
not null default getdate()
--添加数据
insert into student
values('0404s1a2004','dd','男','130000000')
--查询数据
select * from student2
select * from chj
insert chj
values('0404s1a2004','lgc',80)
--基于现有表中的数据,向目的表追加数据
insert student2
select s_id,s_name,sex,phone
from student
where s_id='0404s1a2010'
--修改数据
select * from student2
update student2
set sex='女',s_name='李四'
where s_id='0404s1a2002'
--多个表数据的更新
--内部连接
select s_name,course,chj
from student s inner join chj as c--别名
on s.s_id=c.s_id
--左外部连接
select s_name,course,chj
from student s left outer join chj as c--别名
on s.s_id=c.s_id
--右外部连接(引用完整)
select s_name,course,chj
from student s right outer join chj as c--别名
on s.s_id=c.s_id
select * from chj
--右外部连接(无约束)
select s_name,course,chj
from chj s right outer join student2 as c--别名
on s.s_id=c.s_id
--
create table emp
(
emp_id varchar(10) primary key,
emp_name varchar(10),
leader varchar(10)
)
select * from emp
insert emp
values('5','ee','')
--内部连接
select a.emp_name '员工',b.emp_name as '经理姓名'
from emp a inner join emp b
on a.leader=b.emp_id
delete from emp
where emp_id='1'
truncate table student
select * from student
truncate table chj
delete student
create table book_user
(
Buser_id char(4) primary key,
Buser_name char(10)not null
)
create table user_pa
(
Buser_id char(4) not null,
Buser_pass char(4) not null
)
drop table book_user
insert into book_user values('2','bb')
select * from book_user
truncate table book_user
alter table user_pa
add constraint fk_user foreign key(Buser_id) references book_user(Buser_id)
insert into user_pa values('1','aa')
delete from book_user
select stor_id,ord_num from sales
select stor_id from stores
select a.stor_id,a.ord_num,b.stor_name
from sales as a,stores as b
where a.stor_id=b.stor_id
select stor_id,qty from sales
order by qty desc,stor_id
create table borrow
(
borrow_id int identity(1000,1) primary key,
borrow_name varchar(20) not null
)
insert into borrow(borrow_name) values('sql')
select identity(int,1000,2) as brw_id
into brw
from borrow
select * from brw
alter table brw
add brw_name varchar(10)
insert into brw(brw_name) values('vb')
update brw
set brw_name='sql'
where brw_name is null
select * from brw
select top 10 percent * from sales order by qty desc
select count(*) from sales
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(*) as 'count_J'
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(b.borrow_time)
from book as a left outer join borrow as b
on a.book_id=b.book_id
select * from sales
select stor_id,avg(qty) as 'sum of qty '
from sales
where ord_num='6871'
group by stor_id
having avg(qty)=20
and stor_id='8042'
select avg(qty),stor_id from sales
--where avg(qty)>20
select * from sales
where title_id like '%8%'
--between and
select * from sales
where qty between 20 and 100 and qty<>20 and qty<>100
--in
select * from sales
where qty in (20,30,50)
--or
select * from sales
where qty=20 or qty=30 or qty=50
--time
declare @u_time as varchar(20)
SET @U_time=datepart(mi,getdate())
select '时间'=@u_time
select au_lname +'::'+au_fname as 'name'
from authors
select au_id,au_lname,au_fname,phone,state
from authors
where not state='ca'
order by au_lname desc
--order by
select stor_id,qty from sales
order by qty,stor_id
--查询中的常量
select au_lname+'.'+au_fname '姓名'
from authors
--表的别名
select a.au_lname 姓,au_fname
from authors as a
--生成表查询
select au_lname,au_fname
into authors_copy--新表名
from authors--现有的表名
where state='ca'
select identity(int,1,2) as '标识'
into au
from authors_copy
select * from au
--top
select top 1 percent au_lname,au_fname
from authors
where state='ca'
select top 3 * from sales order by qty desc
select distinct qty from sales
--group by
select top 1 stor_id,min(ord_num) as 最小值,avg(qty) 平均数量
from sales
group by all stor_id
--having avg(qty)>100
order by avg(qty) desc
select stor_id,ord_num,qty
from sales
group by stor_id,ord_num,qty
select stor_id,sum(qty)
from sales
where stor_id=7066
group by stor_id
select stor_id,sum(qty)
from sales
group by stor_id
having stor_id=7066
--like
select au_lname,au_fname
from authors
where au_lname like '_e%e_'
and au_fname like '[^a,e,i,o,u]%'
数据类型 字节大小 范围 说明
整数类型 Bigint 8
Int 4 -2147483648—2147483647
Smallint 2 -32768--32767
Tinyint 1 0-255
Bit 0或1
小数类型 Decimal 与精度有关 -10^38-1—10^38-1 Decimal(12)
或Decimal(12,4)
Numeric 与精度有关 -10^38-1—10^38-1 同上
近似数值类型 Float 8 -1.79E-308—1.79E+308
Real 4 -3.40E-38—3.40E38
货币类型 Money 8 非常大
Smallmoney 4 -214748.3648—214748.3648
日期时间 Datetime 8 1753-1-1---9999-12-3
Smalldatetime 4 1900-1-1---2079-6-6
字符类型 Char <=8000个字符(定长)
Varchar <=8000个字符(不定长)
Text <=2G个字符(不定长)
Nchar <=4000个字符(定长)
Nvarchar <=4000个字符(不定长)
Ntext <=1G个字符(不定长)
二进制类型 Binary <=8000字节(定长)
Varbinary <=8000字节(不定长)
Image <=2^31-1字节(不定长) 声音、图像等
select stor_id,ord_num from sales
select stor_id from stores
select a.stor_id,a.ord_num,b.stor_name
from sales as a,stores as b
where a.stor_id=b.stor_id
select stor_id,qty from sales
order by qty desc,stor_id
create table borrow
(
borrow_id int identity(1000,1) primary key,
borrow_name varchar(20) not null
)
insert into borrow(borrow_name) values('sql')
select identity(int,1000,2) as brw_id
into brw
from borrow
select * from brw
alter table brw
add brw_name varchar(10)
insert into brw(brw_name) values('vb')
update brw
set brw_name='sql'
where brw_name is null
select * from brw
select top 10 percent * from sales order by qty desc
select count(*) from sales
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(*) as 'count_J'
from book as a left outer join borrow as b
on a.book_id=b.book_id
--左外部联接
select count(b.borrow_time)
from book as a left outer join borrow as b
on a.book_id=b.book_id
select * from sales
select stor_id,avg(qty) as 'sum of qty '
from sales
where ord_num='6871'
group by stor_id
having avg(qty)=20
and stor_id='8042'
select avg(qty),stor_id from sales
--where avg(qty)>20
select * from sales
where title_id like '%8%'
--between and
select * from sales
where qty between 20 and 100 and qty<>20 and qty<>100
--in
select * from sales
where qty in (20,30,50)
--or
select * from sales
where qty=20 or qty=30 or qty=50
--time
declare @u_time as varchar(20)
SET @U_time=datepart(mi,getdate())
select '时间'=@u_time
select au_lname +'::'+au_fname as 'name'
from authors
--使用T-SQL创建数据库
create datebase accp
on primary
(name=accp_mdf,
filename='d:\备课资料\转换课程\sql\accp.mdf',
size=2M,
maxsize=3M,
filegrowth=10%
)--主要数据文件(必需有,只有一个)
(
name=accp_mdf,
filename='d:\备课资料\转换课程\sql\accp.mdf',
size=2M,
maxsize=3M,
filegrowth=10%
)--次要数据文件(可以没有,也可以有多个)
log on
(
name=accp_ldf,
filename='d:\备课资料\转换课程\sql\accp.ldf',
size=1M,
maxsize=2M,
filegrowth=10%
)--数据日志文件(必需有,至少一个)
--使用T-SQL删除数据库
drop datebase accp
--使用系统存储过程配置数据库选项
--将数据库pubs设置为只读
exec sp_dboption 'pubs','read only',true
--将数据库pubs设置为自动周期性收缩
exec sp_dboption 'pubs','autoshrink',true
--将数据库pubs设置为只有一个用户
exec sp_dboption 'pubs','single user'
--使用DBCC命令收缩指定的数据库,并预留一定的空间
dbcc shrinkdatabase(pubs,10)
create table book_user
(
Buser_id char(4) primary key,
Buser_name char(10)not null
)
create table user_pa
(
Buser_id char(4) not null,
Buser_pass char(4) not null
)
drop table book_user
insert into book_user values('2','bb')
select * from book_user
truncate table book_user
alter table user_pa
add constraint fk_user foreign key(Buser_id) references book_user(Buser_id)
insert into user_pa values('1','aa')
delete from book_user
--关系运算符like
select * from authors
where au_lname like 'w%'
--%
--[]
--[^]
--_
--向表中添加记录
insert into book values('book0001','vb','人民出版社','图书',20)
insert into reader values('reader0001','rose',20,'road no.1')
alter table borrow
alter column return_time varchar(12) not null
insert into borrow(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--创建表brw
create table brw
(
book_id varchar(12) not null,
reader_id varchar(20) not null,
borrow_time datetime not null,
return_time varchar(10) not null
)
--向brw表中添加记录
insert into brw(book_id,reader_id,borrow_time,return_time)
values('book0001','reader0001','2004-3-17','未还')
--将brw表中的记录添加到borrow表中
insert into borrow
select book_id,reader_id,borrow_time,return_time from brw
select * from borrow
--根据条件改更表的记录
update book
set book_name='sql'
from book as a right outer join borrow as b on a.book_id =b.book_id
where a.book_id='book0001'
--删除表中的所有记录
delete from borrow
--删除表中满足条件的记录
delete from borrow where book_id="book0001"
create table book
(
book_id varchar(12) primary key,
book_name varchar(12) not null,
book_publish varchar(20) not null
)
create table reader
(
reader_id varchar(12) primary key,
reader_name varchar(20) not null,
reader_age int not null
)
create table borrow
(
borrow_id int identity(1000,1),
book_id varchar(12) foreign key(book_id) references book(book_id),
reader_id varchar(12) foreign key(reader_id) references reader(reader_id),
borrow_time datetime default getdate(),
return_time varchar(15) not null
)
--向book表中添加记录
insert book values
('book0001','sql2000','chubanshe')
insert book values
('book0002','sql2000','chubanshe')
select * from book
--向reader表添加记录
insert reader values('reader0001','rose',20)
insert reader values('reader0002','jhon',30)
--向从表中添加记录
insert into borrow(book_id,reader_id,return_time)
values
('book0002','reader0002','未还')
delete from borrow where borrow_time='2003.4.8'
insert borrow(reader_id,book_id,borrow_time,return_time)
select reader_id,book_id,btime,rtime
from table1 where btime>'2003.4.9'
select * from borrow
delete from borrow where book_id='book0002'
select * from book
--内部联接
select a.book_id,a.book_name,b.borrow_time
from book as a inner join borrow as b
on a.book_id=b.book_id
--左外部联接
select a.book_id,a.book_name,b.borrow_time
from book as a left outer join borrow as b
on a.book_id=b.book_id
--右外部联接(引用完整性)
select a.book_id,a.book_name,b.borrow_time
from book as a right outer join borrow as b
on a.book_id=b.book_id
--右外部连接
select btime,b.book_id,b.reader_id
from table1 a right outer join borrow b
on a.book_id=b.book_id
select * from borrow
select * from table1
--更新记录
update table1
set book_id='book0001',reader_id='reader0002'
where book_id='book0002'
select * from table1
update table1
set rtime='未还'
select * from table1
delete from table1 where rtime='未还'
--删除表中的数据
truncate table borrow
truncate table book
delete from book
select * from book
--声明decimal类型变量,第一个参数为精确的位数,第二个为精确的小数位
declare @var1 numeric(6,3),@var2 decimal(6,5),@var3 decimal(6,4)
set @var1=123.78456;
set @var2=1.2345678;
set @var3=12.345678;
select '@var1'=@var1,'@var2'=@var2,'@var3'=@var3
--创建用户自定义数据类型
exec sp_addtype Phone,'varchar(11)',null
--创建表
create table book
(
book_id varchar(12) not null,
book_name varchar(20) not null,
book_publish varchar(20),
book_type varchar(10)not null
)
create table borrow
(
borrow_id int primary key identity(1,1),
book_id varchar(12) not null,
reader_id int not null ,
borrow_time datetime not null,
return_time datetime not null
)
create table reader
(
reader_id varchar(10) primary key,
reader_name varchar(20) not null,
reader_age tinyint not null,
reader_address varchar(50) not null
)
--修改表结构,为表添加主键约束
alter table book
add constraint pk_book1 primary key(book_id)
--修改表结构,为表添加唯一约束
alter table book
add constraint pk_book2 unique(book_id)
--修改表结构,为表添加非空和默认约束
alter table book
add book_qty int not null default '1'
--修改表结构,为表添加检查约束
alter table book
add constraint pk_book3 check(book_qty>0)
--为表中列添加标识列约束,只能在创建列时添加
create table book_user
(
bookid int identity(1000,2) primary key,
bookname varchar(12)
)
--查找表中的记录
select * from book_user
--修改表中已有列的数据类型
alter table borrow
alter column reader_id varchar(10) not null
select * from reader
--删除表
drop table book_user
--为表实施外键约束
alter table borrow
add constraint fk_reader
foreign key(reader_id) references reader(reader_id)
alter table borrow
add constraint fk_book
foreign key(book_id) references book(book_id)
--删除约束
alter table book
drop constraint pk_book2
-- ==========================MySql ===========================
---游标和存储过程
/** add the Residential note Other category to all existing investor connections */
DROP PROCEDURE IF EXISTS insertInvestorCategories;
CREATE PROCEDURE insertInvestorCategories()
BEGIN
DECLARE investorId INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
select ic.investor_connection_id
FROM investor_connection ic
where ic.investor_connection_id not in (select icInv.investor_connection_id from investor_connection_investment icInv);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur into investorId;
IF NOT done THEN
insert into investor_connection_category (investor_connection_id, investment_category_id)
values (investorId, (select investment_category_id from investment_category where investment_category_key="LLI_PN_RES_LOAN_TYPE_OTHER"));
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END;
call insertInvestorCategories();
DROP PROCEDURE IF EXISTS insertInvestorCategories;
-- ===================================
DROP PROCEDURE IF EXISTS switchCategoryLoanType;
CREATE PROCEDURE switchCategoryLoanType()
BEGIN
DECLARE categoryId INT DEFAULT 0;
DECLARE loanTypeId INT DEFAULT 0;
DECLARE investmentId INT DEFAULT 0;
DECLARE done INT DEFAULT 0;
DECLARE cur CURSOR FOR
select inv.investment_id,p.loan_type_id, inv.investment_category_id
listing_option_id
FROM promissory_note p inner join investment inv on p.investment_id = inv.investment_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur into investmentId, loanTypeId, categoryId;
IF NOT done THEN
update promissory_note promnote
inner join investment invest on promnote.investment_id = invest.investment_id
set promnote.loan_type_id=
(select lli.lookup_list_item_id from lookup_list_item lli
where lli.list_key = (select cat.investment_category_key from investment_category cat
where cat.investment_category_id = categoryId)
)
where invest.investment_id=investmentId;
update investment invest
inner join promissory_note promnote on promnote.investment_id = invest.investment_id
set invest.investment_category_id=
(select cat.investment_category_id from investment_category cat
where cat.investment_category_key = (select lli.list_key from lookup_list_item lli
where lli.lookup_list_item_id = loanTypeId)
)
where invest.investment_id=investmentId;
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END;
call switchCategoryLoanType();
DROP PROCEDURE IF EXISTS switchCategoryLoanType;
-- ================================
-- update table, first need remove the foreign key
set FOREIGN_KEY_CHECKS = 0;
set @oldID := (select sct2.sch_task_id from scheduler_tasks sct2
where sct2.sch_task_name = 'Item Status Notification');
set @maxID := (select max(sct1.sch_task_id) from scheduler_tasks sct1)+1;
update scheduler_tasks_config stc
set stc.sch_task_id = @maxID
where stc.sch_task_id = @oldID;
update scheduler_tasks sct
set sct.sch_task_id = @maxID
where sct.sch_task_name = 'Item Status Notification';
set FOREIGN_KEY_CHECKS = 1;
===添加列 和 注释
alter table `item`
add is_delete INT(1) default 0 comment 'indicates if the item has been deleted from the user I-Page';
======================
-- 忘记密码,重新设置
-- 跳过权限检查启动MySQL
c:\mysql\bin>mysqld -nt --skip-grant-tables
-- 重新打开一个命令窗口,进入c:\mysql\bin目录,设置root的新密码
c:\mysql\bin>mysqladmin -u root flush-privileges password "newpassword"
c:\mysql\bin>mysqladmin -u root -p shutdown
-------------------------------------------
-- oracle 循环插入5W条数据
declare
maxrecords constant int:=50000;
i int :=1;
begin
for i in 1..maxrecords loop
insert into bigdatatest(bigdatatest_id,title,data_type_code)
values(i+10,TO_CHAR('9999'+i),TO_CHAR('9999'+i));
end loop;
dbms_output.put_line(' 成功录入数据! ');
commit;
end;
-------------------------------------------
oracle 数据导出:
1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中
exp system/manager@TEST file=d:\daochu.dmp full=y
2 将数据库中system用户与sys用户的表导出
exp system/manager@TEST file=d:\daochu.dmp owner=(system,sys)
3 将数据库中的表inner_notify、notify_staff_relat导出
exp aichannel/aichannel@TESTDB2 file= d:\data\newsmgnt.dmp tables=(inner_notify,notify_staff_relat)
4 将数据库中的表table1中的字段filed1以"00"打头的数据导出
exp system/manager@TEST file=d:\daochu.dmp tables=(table1) query=\" where filed1 like '00%'\"
上面是常用的导出,对于压缩,既用winzip把dmp文件可以很好的压缩。
也可以在上面命令后面 加上 compress=y 来实现。
数据的导入
1 将D:\daochu.dmp 中的数据导入 TEST数据库中。
imp system/manager@TEST file=d:\daochu.dmp
imp aichannel/aichannel@HUST full=y file=file= d:\data\newsmgnt.dmp ignore=y
上面可能有点问题,因为有的表已经存在,然后它就报错,对该表就不进行导入。
在后面加上 ignore=y 就可以了。
2 将d:\daochu.dmp中的表table1 导入
imp system/manager@TEST file=d:\daochu.dmp tables=(table1)
相关推荐
sql语句命令sql语句命令sql语句命令sql语句命令sql语句命令sql语句命令
Oracle Sql语句转换成Mysql Sql语句java 源码,非常简单,只要给定源oracle sql语句地址,和生成目标文件地址运行即可。
可是实现SQL的自动生成,再也不用为调式SQL语句错误而烦恼了!
全面的sql语句sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全sql语句大全
动态拼接sql语句工具类,拼接where后面语句 配合原生jdbc仿动态sql注入 if (ObjectUtil.isNotEmpty(maxLat)&&ObjectUtil.isNotEmpty(minLat)){ sqlParamList.add(new SqlParam("lat",minLat, SqlOpEnum.GE)); ...
SQL 语句大全 SQL 语句大全 SQL 语句大全
非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常好的sql语句非常...
在项目开发的过程中难免需要打印一下自己拼写的SQL语句,尤其是很长的SQL语句,看起来很不舒服,使用该jar包,打印出来的SQL语句已经被格式化,结构很清晰、简洁,更方便我们调试及分析
精妙SQL语句精妙SQL语句精妙SQL语句精妙SQL语句精妙SQL语句
sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明sql语句说明...
功能: 根据表名、where条件,生成导出数据的SQL语句。(包含insert语句。结果可一键执行,利于数据导出、导入) 参数: @tableName nvarchar(100) --表名 ,@sqlWhere nvarchar(500) --where条件(传空时,导出全部...
Visual C++源代码 117 如何直接获取SQL语句聚合函数值Visual C++源代码 117 如何直接获取SQL语句聚合函数值Visual C++源代码 117 如何直接获取SQL语句聚合函数值Visual C++源代码 117 如何直接获取SQL语句聚合函数值...
非常好用的SQL Server 抓取SQL语句工具HOOK 非常好用的SQL Server 抓取SQL语句工具HOOK 非常好用的SQL Server 抓取SQL语句工具HOOK 非常好用的SQL Server 抓取SQL语句工具HOOK
Sql语句收藏 - 天尊阁Sql语句收藏 - 天尊阁Sql语句收藏 - 天尊阁Sql语句收藏 - 天尊阁Sql语句收藏 - 天尊阁Sql语句收藏 - 天尊阁
全面掌握SQL语句 详细的SQL语句介绍 详细的SQL语句介绍 详细的SQL语句介绍 详细的SQL语句介绍
一篇关于SQL语句比较全面的教程。 SQL语句教程(01) SELECT......................................2 SQL语句教程(02) DISTINCT...................................2 SQL语句教程(03) WHERE...........................
Delphi中sql语句的使用总结 Delphi中sql语句的使用总结 Delphi中sql语句的使用总结
《Effective MySQL之SQL语句最优化》是由MySQL专家Ronald Bradford撰著,书 中提供了很多可以用于改进数据库和应用程序性能的最佳实践技巧,并对这些技巧 做了详细的解释。本书希望能够通过一步步详细介绍SQL优化...
经典SQL语句大全(各种sql语句) 各种复杂的SQl 都有,以及SQL语句的灵活运用
全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句全国省市数据库 二级 sql语句