sql2005的增删改和函数

来源：互联网发布：东莞cnc编程教学编辑：程序博客网时间：2024/06/06 15:57

use sunjob;
create table card(
cardId int identity(1,1),
cardPass varchar(20)
);
select * from card;
insert into card values('0oIi1');

--把i变成1 表O 变成0，这是replace函数的讲解
update card set cardPass = replace(replace(cardPass , 'i', 1), 'o', '0');

create table SellRecord(
Id int identity(1,1) primary key,
ListNumber varchar(10)
);
select * from SellRecord;
--批处理
go
insert into SellRecord values('12-1')
insert into SellRecord values('12-2')
insert into SellRecord values('13-3')
insert into SellRecord values('13-2')
insert into SellRecord values('14-1')
insert into SellRecord values('14-5')
insert into SellRecord values('156-12')
go
--排序
select * from SellRecord order by ListNumber desc ;
select left(ListNumber, 2) from SellRecord;--返回从字符串左边开始指定个数的字符。
--返回字符串中指定表达式的起始位置。
--第一个参数是要查找的字符，第二个参数是从它里面去查找第一个参数的字符，第三个参数是从第二个
--参数的哪个位置查起，第一个字符的位置就是1
select charindex('-', ListNumber,1) from SellRecord;

--stuff 删除指定长度的字符并在指定的起始点插入另一组字符。
--第一个参数是要处理的字符，第二参数是从哪个位置开始，第三个参数是要删除的长度，第四个参数是
--要插入的字符
select stuff(ListNumber, 1, charindex('-', ListNUmber, 1), '') from SellRecord;
select len('12-345');
--convert第一个参数是要转换的类型，第二个参数是参数的表达式
--cast cast(expression as data-type)第一个参数是表达式，中间加as，第二个参数是数据类型
select convert(varchar(10),'123-123');
select cast('19900909' as int);
select * from SellRecord order by
left(ListNumber, charindex('-', ListNumber, 1)-1),
stuff(ListNumber, 1, charindex('-', ListNumber, 1), '');

--别名
select * from SellRecord;

select ListNumber as '数据列' from SellRecord;
select ListNumber '数据列' from SellRecord;
select '数据列'= ListNumber from SellRecord;

--常量列
select id , ListNumber, 学校 = '赛杰' from SellRecord;
select id , ListNumber, '学校' = '赛杰' from SellRecord;
select id , ListNumber, '赛杰' 学校 from SellRecord;

--多列联立查询
use sunjob;
select * from stuInfo;
select (stuNo + '-' + convert(char(12),left(stuEmail,2))) as 活动编号 from stuInfo;
select convert(varchar(10),stuNo)+'-'+stuSex as 活动编号 from stuInfo;
select stuName + stuAddress as '住址' from stuInfo;

--top关键字的使用
select * from stuInfo where stuNo < 7;
select top(4) * from stuInfo;--推荐用这种方法
select top 20 percent * from stuInfo;

--排序
use student;
select * from stuInfo;
select * from stuInfo order by stuPhone desc ;--这是降序
select * from stuInfo order by stuPhone asc ; --这是升序，默认情况下就是升序

select top(2) StuName, stuphone from stuInfo order by stuPhone desc;
--多列排序
select * from stuInfo order by stuSex desc, stuNo asc;

--sql中的函数
select 'caohuan';
--第一个参数是要查找的字符，第二个参数是从这个目标对象去查找，第三个参数是从目标对象的那个位置开始查起，在sql中，
--第一个字符的位置是从1开始，不是从0开始
select charindex('ca', 'caohuan' ,2);
select len('caohuan');
select '   caohuan wenbin ';
--这是删除左端的空格
select ltrim('   caohuan wenbin ');
--这是删除右端的空格
select rtrim('   caohuan wenbin ');
--这是计算字符窜的长度
select len('   caohuan   wenbin ');
select len(rtrim(ltrim('   caohuan   wenbin ')));

--这是截取字符左端的几个字符
select left('caohuan',2);
--这是截取字符的右端的几个字符
select right('wenbin', 3);
select right(left('caohuanwenbin',3), 1);
--第一个参数是要操作的字符，第二个参数是要代替的字符，第三个是用哪个字符去代替第二个参数要代替的字符
select replace('我爱你','爱', '恨');
--第一个参数是要操作的字符。第二个参数是从几个字符开始，第三个参数是要操作字符的长度，第四个参数是用这个字符去替代从第几个字符开始
--长度为第三个参数长度的，去替换这个长度的字符
select stuff('caohuan', 1, 3, 'wen');

--这是系统的当前日期和时间
select getDate() as '当前时间';

select datediff(yy, '2011-09-09', '2012-08-08');
select datediff(mm, '2011-09-09', '2012-08-08');
select datediff(dd, '2011-09-09', '2012-08-08');
select datediff(dy, '2011-09-09', '2012-08-08');
select datediff(ms, getdate(), '2012-08-08');

--math函数
select ceiling(34.3434);
select floor(34.343);
select round(34.333,0);
select round(23423456.345345,2);
select round(234234256767,-1);

--系统函数
select 1+1;
select 1+'1';
select '1'+'1';
select 1+'1a';
select '12312'+'23423';

select convert(int, '123');
select convert(int, '123aaa');--类型转换错误

select convert(int, '123')+convert(int, '123');

select cast('111aa' as varchar(20));

select convert(int, '234')+cast('123' as int);

select convert(int, '123') + cast('222' as int);
select convert(int, '222') + cast('111' as char);--这样计算的结果是333

use test;
create table card(
cardId int not null identity(1,1) primary key,
cardNumber varchar(20)
);
select * from card;
insert into card values('io01234i1');
--改i改成1，把o改成0
select replace(replace(cardNumber, 'i', 1),'o',0) from card;

update card set cardNumber = replace(replace(cardNumber,'o',0), 'i', 1);

--这是上面的一个概括

select * from stuinform
use student
---------------别名-----------------------------------------------------
select stuNo as 学号,stuname 姓名 from stuinform
select 地址=stuaddress from stuinform where stusex ='男' and stuaddress like '湖南%'
--注意上面不能用地址 like '湖南%'
---------------- 常量列--------------------------------------------------
select 籍贯='湖北随州', '小屋子' 姓名 , '男' as 性别 from stuinform
---注意字段名的 '' 可要可不要，这里的'湖北随州'，'小屋子'，'男'相当于实参，而上面的stuaddress，stuname，stusex相当于形参

-------------------多列联列查询-------top关键字------------------------------------
select top(4) convert(char(6),stuno)+stuname as 个人信息 from stuinform
select top 20 percent convert(char(6),stuno)+stuname as 个人信息 from stuinform union

select convert(char(6),stuno)+stuname as 个人信息 from stuinform union
select stusex+stuaddress from stuinform as 地址
----注意此行--

--------------------排序--asc---desc--------------------------------------------------
select * from stuinform where stuno<>1015 order by stusex desc ,stuaddress asc

----------------------字符函数----------------------------------------------------------
select charindex('ACCP','My accp course')
              --计算一个指定的字符串在另一个字符串中的起始位置   从1开始
select len('sdsfk')             --返回字符长度
select len('我爱中国')           --返回字符长度
select lower('sGJJjje 中国')    --转化为小写字符
select upper('fkdGG 中国')      --转化为大写字符
select ltrim(' 中国sjd ddf ')            select ' 中国sjd ddf '
                          ----比较前两者可得ltrim作用为清除字符左边的空格
select rtrim(' 中国人好 ')             select '中国人好   '

select right('sdfdgf',3)
select left('sfdgfg',5)
           -----从字符串右边或左边返回指定数目的字符
select replace('我是中国人，我说中国','说','爱') as 心里话
    ----REPLACE (<string_expression1>， <string_expression2>， <string_expression3>)
    --用string_expression3 替换在string_expression1 中的子串string_expression2。
select stuff('我是中国人，我爱中国',3,1,'随州')
---用另一子串替换字符串指定位置、长度的子串。
--STUFF (<character_expression1>， <start_ position>， <length>，<character_expression2>)
--如果起始位置为负或长度值为负，或者起始位置大于character_expression1 的长度，则返回NULL 值。
--如果length 长度大于character_expression1 中 start_ position 以右的长度，则character_expression1 只保留首字符。
select left('我爱中国',2)
                   -----LEFT (<character_expression>， <integer_expression>)
                   -----返回character_expression 左起 integer_expression 个字符。
select right('我爱中国',2)
                  ----RIGHT (<character_expression>， <integer_expression>)
                  ----返回character_expression 右起 integer_expression 个字符。

----------------------日期函数-------------------------------------------------------

select getdate() --取得当前的系统日期

select dateadd(mm,2,getdate())
select dateadd(mm,2,'2010-12-19')
--将指定的数值添加到指定的日期部分后的日期

select datediff(yy,'2012-12-21',getdate())
     --两个日期之间的指定日期部分的差别后者减前者
select datename(dw,getdate())
select datename(dd,'01/12/1991')
     --返回日期中指定日期部分的字符串形式
select datepart(mm,'1991/10/24')
      --返回日期中指定日期部分的整数形式

----------------------数学函数--------------------------------------------------------

select abs(-23.4)             --取数值表达式的绝对值
select ceiling(34.5)
select ceiling(-34.5)     --返回大于或等于所给数字表达式的最小整数
select floor(34.5)
select floor(-34.5)
                           --返回小于或等于所给数字表达式的最小整数
select power(2,3)
select power(-2,3)
                           --取数值表达式的幂值
select rand()              --取0-1之间的随机数
select round(45.3423,2)
select round(45.3423,5)
select round(437.3423,-1)
                           --将数值表达式四舍五入为指定精度
select sign(234.6)
select sign(-234.6)
select sign(0)
                            --对于正数返回+1，对于负数返回-1，对于0 则返回0

select sqrt(16) --取浮点表达式的平方根
select 1+1
select 1+'1'
select '1'+'1'
select 'd'+'1'
select 's'+2
select 233-'45'

-----------------------系统函数-------------------------------------------------------

select datalength('中国人A阿德') --返回用于指定表达式的字节数

select current_user                   --返回：你登录的用户名
select host_name()                   --返回当前用户所登录的计算机名字
select system_user                   --返回当前所登录的用户名称
select user_name(4)
select user_name(2)                  --从给定的用户ID返回用户名
select cast('234' as int)+convert(int,'123')

-------------------------案例-------------------------------------------------------
select replace(replace('i204nioi0fgo','i',1),'o',0)
update stuinform set stuaddress=replace(stuaddress,'南','北') where stuaddress like '_南%'
select * from stuinform

create table SellRecord
(
Id int identity(1,1) primary key,
ListNumber varchar(10) ,
)
go --批处理不是sql语句
insert into SellRecord values('12-1')
insert into SellRecord values('12-2')
insert into SellRecord values('13-3')
insert into SellRecord values('13-2')
insert into SellRecord values('14-1')
insert into SellRecord values('14-5')
insert into SellRecord values('156-12')
go

select * from sellrecord

--问题解决
order by
select left('12-3',charindex('-','12-3',1)-1)

select left(ListNumber,charindex('-',ListNumber,1)-1) from sellrecord

--right方法留给你们
select stuff('12-3',1,charindex('-','12-3',1),'')

select stuff(ListNumber,1,charindex('-',ListNumber,1),'') from sellrecord

--排序
select * from sellrecord
select * from sellrecord order by left(ListNumber,charindex('-',ListNumber,1)-1) desc,stuff(ListNumber,1,charindex('-',ListNumber,1),'') desc
select left(listnumber,2)+id as 信息 from se6767rd where listnumber like '12%'