‘ja%‘;
select * from student
where name
not like ‘%[j,n]%‘;
select * from student
where name
like ‘%[j,n,a]%‘;
select * from student
where name
like ‘%[^ja,as,on]%‘;
select * from student
where name
like ‘%[ja_on]%‘;
--in 子查询
select * from student
where id
in (
1,
2);
--not in 不在其中
select * from student
where id
not in (
1,
2);
--is null 是空
select * from student
where age
is null;
--is not null 不为空
select * from student
where age
is not null;
--order by 排序
select * from student
order by name;
select * from student
order by name
desc;
select * from student
order by name
asc;
--group by 分组
--按照年龄进行分组统计
select count(age), age
from student
group by age;
--按照性别进行分组统计
select count(
*), sex
from student
group by sex;
--按照年龄和性别组合分组统计，并排序
select count(
*), sex
from student
group by sex, age
order by age;
--按照性别分组，并且是id大于2的记录最后按照性别排序
select count(
*), sex
from student
where id
> 2 group by sex
order by sex;
--查询id大于2的数据，并完成运算后的结果进行分组和排序
select count(
*), (sex
* id) new
from student
where id
> 2 group by sex
* id
order by sex
* id;
--group by all 所有分组
--按照年龄分组，是所有的年龄
select count(
*), age
from student
group by all age;
--having 分组过滤条件
--按照年龄分组，过滤年龄为空的数据，并且统计分组的条数和现实年龄信息
select count(
*), age
from student
group by age
having age
is not null;
--按照年龄和cid组合分组，过滤条件是cid大于1的记录
select count(
*), cid, sex
from student
group by cid, sex
having cid
> 1;
--按照年龄分组，过滤条件是分组后的记录条数大于等于2
select count(
*), age
from student
group by age
having count(age)
>= 2;
--按照cid和性别组合分组，过滤条件是cid大于1，cid的最大值大于2
select count(
*), cid, sex
from student
group by cid, sex
having cid
> 1 and max(cid)
> 2;
--Ø 嵌套子查询
-- 子查询是一个嵌套在select、insert、update或delete语句或其他子查询中的查询。任何允许使用表达式的地方都可以使用子查询。子查询也称为内部查询或内部选择，而包含子查询的语句也成为外部查询或外部选择。
--# from (select … table)示例
--将一个table的查询结果当做一个新表进行查询
select * from (
select id, name
from student
where sex
= 1
) t where t.id
> 2;
--上面括号中的语句，就是子查询语句（内部查询）。在外面的是外部查询，其中外部查询可以包含以下语句：
-- 1、 包含常规选择列表组件的常规select查询
-- 2、 包含一个或多个表或视图名称的常规from语句
-- 3、 可选的where子句
-- 4、 可选的group by子句
-- 5、 可选的having子句
--# 示例
--查询班级信息，统计班级学生人生
select *, (
select count(
*)
from student
where cid
= classes.id)
as num
from classes
order by num;
--# in, not in子句查询示例
--查询班级id大于小于的这些班级的学生信息
select * from student
where cid
in (
select id
from classes
where id
> 2 and id
< 4
);
--查询不是班的学生信息
select * from student
where cid
not in (
select id
from classes
where name
= ‘2班‘
)
--in、not in 后面的子句返回的结果必须是一列，这一列的结果将会作为查询条件对应前面的条件。如cid对应子句的id；
--# exists和not exists子句查询示例
--查询存在班级id为的学生信息
select * from student
where exists (
select * from classes
where id
= student.cid
and id
= 3
);
--查询没有分配班级的学生信息
select * from student
where not exists (
select * from classes
where id
= student.cid
);
--exists和not exists查询需要内部查询和外部查询进行一个关联的条件，如果没有这个条件将是查询到的所有信息。如：id等于student.id；
--# some、any、all子句查询示例
--查询班级的学生年龄大于班级的学生的年龄的信息
select * from student
where cid
= 5 and age
> all (
select age
from student
where cid
= 3
);
select * from student
where cid
= 5 and age
> any (
select age
from student
where cid
= 3
);
select * from student
where cid
= 5 and age
> some (
select age
from student
where cid
= 3
);
--Ø 聚合查询
--1、 distinct去掉重复数据
select distinct sex
from student;
select count(sex),
count(
distinct sex)
from student;
--2、 compute和compute by汇总查询
--对年龄大于的进行汇总
select age
from student
where age
> 20 order by age
compute sum(age)
by age;
--对年龄大于的按照性别进行分组汇总年龄信息
select id, sex, age
from student
where age
> 20 order by sex, age
compute sum(age)
by sex;
--按照年龄分组汇总
select age
from student
where age
> 20 order by age, id
compute sum(age);
--按照年龄分组，年龄汇总，id找最大值
select id, age
from student
where age
> 20 order by age
compute sum(age),
max(id);
--compute进行汇总前面是查询的结果，后面一条结果集就是汇总的信息。compute子句中可以添加多个汇总表达式，可以添加的信息如下：
-- a、 可选by关键字。它是每一列计算指定的行聚合
-- b、 行聚合函数名称。包括sum、avg、min、max、count等
-- c、 要对其执行聚合函数的列
-- compute by适合做先分组后汇总的业务。compute by后面的列一定要是order by中出现的列。
--3、 cube汇总
--cube汇总和compute效果类似，但语法较简洁，而且返回的是一个结果集。
select count(
*), sex
from student
group by sex
with cube;
select count(
*), age,
sum(age)
from student
where age
is not null group by age
with cube;
--cube要结合group by语句完成分组汇总
--Ø 排序函数
-- 排序在很多地方需要用到，需要对查询结果进行排序并且给出序号。比如：
-- 1、 对某张表进行排序，序号需要递增不重复的
-- 2、 对学生的成绩进行排序，得出名次，名次可以并列，但名次的序号是连续递增的
-- 3、 在某些排序的情况下，需要跳空序号，虽然是并列
--基本语法
--排序函数 over([分组语句] 排序子句[desc][asc])
--排序子句 order by 列名, 列名
--分组子句 partition by 分组列, 分组列
--# row_number函数
--根据排序子句给出递增连续序号
--按照名称排序的顺序递增
select s.id, s.name, cid, c.name, row_number()
over(
order by c.name)
as number
from student s, classes c
where cid
= c.id;
--# rank函数函数
--根据排序子句给出递增的序号，但是存在并列并且跳空
--顺序递增
select id, name, rank()
over(
order by cid)
as rank
from student;
--跳过相同递增
select s.id, s.name, cid, c.name, rank()
over(
order by c.name)
as rank
from student s, classes c
where cid
= c.id;
--# dense_rank函数
--根据排序子句给出递增的序号，但是存在并列不跳空
--不跳过，直接递增
select s.id, s.name, cid, c.name, dense_rank()
over(
order by c.name)
as dense
from student s, classes c
where cid
= c.id;
--# partition by分组子句
--可以完成对分组的数据进行增加排序，partition by可以与以上三个函数联合使用。
--利用partition by按照班级名称分组，学生id排序
select s.id, s.name, cid, c.name, row_number()
over(partition
by c.name
order by s.id)
as rank
from student s, classes c
where cid
= c.id;
select s.id, s.name, cid, c.name, rank()
over(partition
by c.name
order by s.id)
as rank
from student s, classes c
where cid
= c.id;
select s.id, s.name, cid, c.name, dense_rank()
over(partition
by c.name
order by s.id)
as rank
from student s, classes c
where cid
= c.id;
--# ntile平均排序函数
--将要排序的数据进行平分，然后按照等分排序。ntile中的参数代表分成多少等分。
select s.id, s.name, cid, c.name,
ntile(5)
over(
order by c.name)
as ntile
from student s, classes c
where cid
= c.id;
--Ø 集合运算
--操作两组查询结果，进行交集、并集、减集运算
--1、 union和union all进行并集运算
--union 并集、不重复
select id, name
from student
where name
like ‘ja%‘
union
select id, name
from student
where id
= 4;
--并集、重复
select * from student
where name
like ‘ja%‘
union all
select * from student;
--2、 intersect进行交集运算
--交集（相同部分）
select * from student
where name
like ‘ja%‘
intersect
select * from student;
--3、 except进行减集运算
--减集（除相同部分）
select * from student
where name
like ‘ja%‘
except
select * from student
where name
like ‘jas%‘;
--Ø 公式表表达式
--查询表的时候，有时候中间表需要重复使用，这些子查询被重复查询调用，不但效率低，而且可读性低，不利于理解。那么公式表表达式可以解决这个问题。
--我们可以将公式表表达式（CET）视为临时结果集，在select、insert、update、delete或是create view语句的执行范围内进行定义。
--表达式
with statNum(id, num)
as
(
select cid,
count(
*)
from student
where id
> 0
group by cid
)
select id, num
from statNum
order by id;
with statNum(id, num)
as
(
select cid,
count(
*)
from student
where id
> 0
group by cid
)
select max(id),
avg(num)
from statNum;
--Ø 连接查询
--1、 简化连接查询
--简化联接查询
select s.id, s.name, c.id, c.name
from student s, classes c
where s.cid
= c.id;
--2、 left join左连接
--左连接
select s.id, s.name, c.id, c.name
from student s
left join classes c
on s.cid
= c.id;
--3、 right join右连接
--右连接
select s.id, s.name, c.id, c.name
from student s
right join classes c
on s.cid
= c.id;
--4、 inner join内连接
--内连接
select s.id, s.name, c.id, c.name
from student s
inner join classes c
on s.cid
= c.id;
--inner可以省略
select s.id, s.name, c.id, c.name
from student s
join classes c
on s.cid
= c.id;
--5、 cross join交叉连接
--交叉联接查询，结果是一个笛卡儿乘积
select s.id, s.name, c.id, c.name
from student s
cross join classes c
--where s.cid = c.id;
--6、 自连接（同一张表进行连接查询）
--自连接
select distinct s.
* from student s, student s1
where s.id
<> s1.id
and s.sex
= s1.sex;
--Ø 函数
--1、 聚合函数
--max最大值、min最小值、count统计、avg平均值、sum求和、var求方差
select
max(age) max_age,
min(age) min_age,
count(age) count_age,
avg(age) avg_age,
sum(age) sum_age,
var(age) var_age
from student;
--2、 日期时间函数
select dateAdd(
day,
3,
getDate());
--加天
select dateAdd(
year,
3,
getDate());
--加年
select dateAdd(hour,
3,
getDate());
--加小时
--返回跨两个指定日期的日期边界数和时间边界数
select dateDiff(
day,
‘2011-06-20‘,
getDate());
--相差秒数
select dateDiff(second,
‘2011-06-22 11:00:00‘,
getDate());
--相差小时数
select dateDiff(hour,
‘2011-06-22 10:00:00‘,
getDate());
select dateName(
month,
getDate());
--当前月份
select dateName(minute,
getDate());
--当前分钟
select dateName(weekday,
getDate());
--当前星期
select datePart(
month,
getDate());
--当前月份
select datePart(weekday,
getDate());
--当前星期
select datePart(second,
getDate());
--当前秒数
select day(
getDate());
--返回当前日期天数
select day(
‘2011-06-30‘);
--返回当前日期天数
select month(
getDate());
--返回当前日期月份
select month(
‘2011-11-10‘);
select year(
getDate());
--返回当前日期年份
select year(
‘2010-11-10‘);
select getDate();
--当前系统日期
select getUTCDate();
--utc日期
--3、 数学函数
select pi();
--PI函数
select rand(
100),
rand(
50),
rand(),
rand();
--随机数
select round(
rand(),
3),
round(
rand(
100),
5);
--精确小数位
--精确位数，负数表示小数点前
select round(
123.456,
2),
round(
254.124,
-2);
select round(
123.4567,
1,
2);
--4、 元数据
select col_name(
object_id(
‘student‘),
1);
--返回列名
select col_name(
object_id(
‘student‘),
2);
--该列数据类型长度
select col_length(
‘student‘,
col_name(
object_id(
‘student‘),
2));
--该列数据类型长度
select col_length(
‘student‘,
col_name(
object_id(
‘student‘),
1));
--返回类型名称、类型id
select type_name(type_id(
‘varchar‘)), type_id(
‘varchar‘);
--返回列类型长度
select columnProperty(
object_id(
‘student‘),
‘name‘,
‘PRECISION‘);
--返回列所在索引位置
select columnProperty(
object_id(
‘student‘),
‘sex‘,
‘ColumnId‘);
--5、 字符串函数
select ascii(
‘a‘);
--字符转换ascii值
select ascii(
‘A‘);
select char(
97);
--ascii值转换字符
select char(
65);
select nchar(
65);
select nchar(
45231);
select nchar(
32993);
--unicode转换字符
select unicode(
‘A‘),
unicode(
‘中‘);
--返回unicode编码值
select soundex(
‘hello‘),
soundex(
‘world‘),
soundex(
‘word‘);
select patindex(
‘%a‘,
‘ta‘),
patindex(
‘%ac%‘,
‘jack‘),
patindex(
‘dex%‘,
‘dexjack‘);
--匹配字符索引
select ‘a‘ + space(
2)
+ ‘b‘,
‘c‘ + space(
5)
+ ‘d‘;
--输出空格
select charIndex(
‘o‘,
‘hello world‘);
--查找索引
select charIndex(
‘o‘,
‘hello world‘,
6);
--查找索引
select quoteName(
‘abc[]def‘),
quoteName(
‘123]45‘);
--精确数字
select str(
123.456,
2),
str(
123.456,
3),
str(
123.456,
4);
select str(
123.456,
9,
2),
str(
123.456,
9,
3),
str(
123.456,
6,
1),
str(
123.456,
9,
6);
select difference(
‘hello‘,
‘helloWorld‘);
--比较字符串相同
select difference(
‘hello‘,
‘world‘);
select difference(
‘hello‘,
‘llo‘);
select difference(
‘hello‘,
‘hel‘);
select difference(
‘hello‘,
‘hello‘);
select replace(
‘abcedef‘,
‘e‘,
‘
