sql 语句练习

use studentmanager
select * from card
drop table card
create table card (
 cid int identity primary key,
 cpassword varchar(20)
)
insert into card values('idfjfdi234')
insert into card values('dfiejf245j')
insert into card values('diodkj0w3d')
insert into card values('23redfdgfo')
insert into card values('oooooooooo')
insert into card values('iiiii11111')
insert into card values('1111111111')
insert into card values('iiiiiiiiii')
insert into card values('0000000000')
insert into card values('edifjei123')
insert into card values('idfjeijklg')

update card set cpassword = replace(cpassword,'i','1')
select replace(replace(cpassword,'i','1'),'o','0') from card
select * into card1 from card
select * from card1
update card1 set cpassword = replace(cpassword,'1','i')
select * from card1

create table test(
 tId int identity primary key,
 num varchar(20) not null
)
insert test values('14-45')
insert test values('14-12')
insert test values('14-4')
insert test values('14-5')
insert test values('14-3')
insert test values('14-105')
insert test values('2-1')
insert test values('2-10')
insert test values('9-10')
insert test values('102-109')
insert test values('102-101')
insert test values('102-145')
insert test values('104-45')

select * from test
/*得到左侧的字符*/
select left(num,charindex('-',num)-1),num from test
/*得到右侧的字符*/
select right(num,len(num)-charindex('-',num)),num from test
/*得到之前的字符的位置*/
select charindex('-',num),num from test
select num from test
order by convert(int,left(num,charindex('-',num)-1)) asc,
   convert(int,right(num,len(num)-charindex('-',num)))asc

转载于:https://www.cnblogs.com/sunshine2011/archive/2011/07/22/2114027.html