SQL 语句

--创建数据
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)