SQLserver总结(类型转换函数、联合UNION等)

 

 

SQLserver总结(COUNT(*)、类型转换函数、联合UNION)

 

use wx105;

create table sale

(

proName nvarchar(100),

sales int

);

insert into sale values('傻子瓜子',30);

insert into sale values('青岛啤酒',20);

insert into sale values('北京方便面',10);

insert into sale values('旺仔小馒头',24);

insert into sale values('卫龙',21);

insert into sale values('傻子瓜子',50);

insert into sale values('傻子瓜子',56);

insert into sale values('北京方便面',33);

insert into sale values('旺仔小馒头',14);

insert into sale values('旺仔小馒头',20);

insert into sale values('青岛啤酒',13);

insert into sale values('傻子瓜子',26);

insert into sale values('傻子瓜子',27);

insert into sale values('傻子瓜子',30);

insert into sale values('傻子瓜子',40);

insert into sale values('青岛啤酒',12);

insert into sale values('北京方便面',55);

 

select * from sale;

 

1、--热销商品排名表(按照每种商品的总销售量排序)

select top 3 proName,SUM(sales) from sale group by proName order by SUM(sales) desc;

 

create table stu2

(

id int,

name nvarchar(50),

classId int

);

insert into stu2 values(1,'ksb',105);

insert into stu2 values(2,'ydf',101);

insert into stu2 values(3,'ldf',104);

insert into stu2 values(4,'ydf',101);

insert into stu2 values(5,'ydf',101);

insert into stu2 values(6,'gs',105);

insert into stu2 values(7,'cmy',105);

insert into stu2 values(8,'gmy',105);

insert into stu2 values(9,'gqq',101);

insert into stu2 values(10,'gyy',101);

insert into stu2 values(11,'wsc',104);

insert into stu2 values(12,'zxr',104);

insert into stu2 values(13,'zqz',104);

 

 

select * from stu2;

 

2、--查询每个班的班级id和班级人数

--101  5

--104  4

--105  4

 

 

select

班级编号=classId,

COUNT(*) 人数

 from

stu2

  group by

classId;

select * from stuInfo2;

3、--查询stuInfo2里面的男生和女生各自的人数

select gender,COUNT(*) from stuInfo2 group by gender;

4、--修改stu2表的结构，加一个性别列

alter table stu2 add gender nchar(1) default '男';

select * from stu2;

update stu2 set gender='女' where id>=7 and id<=11;

 

5、--查询查询每个班的班级id和班级男同学的人数

 

select

班级编号=classId,

COUNT(*) 人数

 from

stu2

where gender='男'

  group by

classId;

 

 

6、--查询男同学人数超过2个的班级的编号和男同学个数

 

select

班级编号=classId,

COUNT(*) 人数

 from

stu2

where gender='男'

  group by

classId

having COUNT(*) in (1,3,5);

--having COUNT(*)=1 or COUNT(*)=3 or COUNT(*)=5;

 

select * from stu2;

 

 

7、--统计销售总价超过3000元的商品名称和销售总价，并按销售总价降序排序

select

proName,

 a

from sals

group by 商品名称

having SUM(商品每次销售个数 * 商品销售单价) as a >100

order by adesc;

 

--------------------------------------------类型转换函数----------------------------

1、--select后面可以跟的有：列名、函数、常量。当select后面的这些东西和任何表都没有关系的时候，可以省略from

select * from stu2;

select name,gender,'已婚' from stu2;

select '已婚' from stu2;

select '已婚';

select 100*10 as 成绩;

select 100+'1';--当参与运算的两个参数不是同一类型的时候，数据库会尝试帮我们做类型转换

select 100+'a';--这会报错，因为数据库会尝试把'a'转成int类型，但是无法转

2、--cast()函数：把数据转换成指定的数据类型

3、--我的需求，把100转成字符串后和a连接

select CAST(100 as nvarchar) + 'a';--cast(需要转换的数据  as   需要转换的数据类型)

4、--convert()函数：把数据转换成指定的数据类型

select CONVERT(nvarchar,100)+'b' --这个更常用，因为更完善

select '100'+10;--这行代码，系统会自动转换为下面这行代码

select CONVERT(int,'100') +10;

select 100.101+'999.9999';

select 100.101+convert(float,'999.9999');

select 100.0 + CONVERT(smallInt,'10000');

select '您的班级编号是：'+105;

select '您的班级编号是：'+CONVERT(nvarchar,105);

 

5、--这行代码查的结果出乎意料，发现20086这个最小的数字竟然拍到最上面，这是为什么呢？

--答：因为phone这个列的数据类型不是int，而是varchar,是字符串，字符串排序，会按照从左到右的字符逐个排序

select * from stuInfo order by phone desc;

6、--现在我就想让手机号按照数字类型来排序，怎么做？

--答：把phone的数据类型通过类型转换函数转成int

select * from stuInfo order by CONVERT(bigInt,phone) desc;

 

7、--right()函数：从右边开始，取三个字符,所以最终取出来的数据是字符类型

select RIGHT('100100',4);

select RIGHT(100100,4);

select LEFT('100100',4);

select LEFT(100100,4);

8、--我要取123456中的234字符，怎么取？

select RIGHT(LEFT(123456,4),3);

 

 

9、--把手机号的后三位转成int类型

select name,phone from stuInfo;

select name, CAST(RIGHT(phone,3)  as int) phone from stuInfo;

 

10、--获取当前时间

select GETDATE();

select CONVERT(varchar(7),GETDATE(),20);--20是指定的样式

 

 

 

 

----------------------------联合 UNION-----------------------------

1、--联合，就是把两个表联合在一张表中显示，是上下连接，而不是左右连接，左右连接后面会讲

 

select * from stuInfo union

select * from stuInfo2

create table stuInfo3

(

id int,

name nvarchar(50),

gender nchar(1),

age int,

addres nvarchar(100)

);

insert into stuInfo3 values(3,'沙悟净','男',17,'流沙河');

2、--union，把两个表联合起来，会去除重复数据,并且会重新排序(默认按第一列排序，我们也可以自己写order by)

3、--union all:把两个表联合起来，不会去除两个表中重复的数据,不会重新排序

select * from stuInfo union all

select ' ',' ','总分数：', SUM(score),' ' from stuInfo ;

4、--一般我们只需要考虑两个表的列数一致，一般就没问题了，因为我们一般也是把类似的数据放在一块

select id,name,gender from stuInfo union

select xuehao,name,gender from stuInfo2;