SQL Server SQL高等查询语句计算

Ø 基本常用查询 –select select * from student; –all 查询所有 select
all sex from student; –distinct 过滤重复 select distinct sex from
student; –count 统计 select count(*) from student; select count(sex)
from student; select count(distinct sex) from student; –top 取前N条记录
select top 3 * from student; –alias column name 列重命名 select id as
编号, name ‘名称’, sex 性别 from student; –alias table name 表重命名
select id, name, s.id, s.name from student s; –column 列运算 select
(age + id) col from student; select s.name + ‘-‘ + c.name from classes
c, student s where s.cid = c.id; –where 条件 select * from student
where id = 2; select * from student where id 7; select * from student
where id 3; select * from student where id 3; select * from student
where id = 3; select * from student where id = 5; select * from
student where id ! 3; select * from student where id ! 5; –and 并且
select * from student where id 2 and sex = 1; –or 或者 select * from
student where id = 2 or sex = 1; –between … and … 相当于并且 select
* from student where id between 2 and 5; select * from student where
id not between 2 and 5; –like 模糊查询 select * from student where
name like ‘%a%’; select * from student where name like ‘%[a][o]%’;
select * from student where name not like ‘%a%’; select * from student
where name like ‘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%’; Ø 公式表表达式
查询表的时候,有时候中间表需要重复使用,这些子查询被重复查询调用,不但效率低,而且可读性低,不利于理解。那么公式表表达式可以解决这个问题。
我们可以将公式表表达式视为临时结果集,在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’, ‘E’);–替换字符串 select stuff(‘hello world’, 3,
4, ‘ABC’);–指定位置替换字符串 select replicate(‘abc#’, 3);–重复字符串
select subString(‘abc’, 1, 1), subString(‘abc’, 1, 2), subString(‘hello
Wrold’, 7, 5);–截取字符串 select len(‘abc’);–返回长度 select
reverse(‘sqlServer’);–反转字符串 select left(‘leftString’,
4);–取左边字符串 select left(‘leftString’, 7); select
right(‘leftString’, 6);–取右边字符串 select right(‘leftString’, 3);
select lower(‘aBc’), lower(‘ABC’);–小写 select upper(‘aBc’),
upper(‘abc’);–大写 –去掉左边空格 select ltrim(‘ abc’), ltrim(‘#
abc#’), ltrim(‘ abc’); –去掉右边空格 select rtrim(‘ abc ‘), rtrim(‘#
abc# ‘), rtrim(‘abc’); 6、 安全函数 select current_user; select user;
select user_id(), user_id(‘dbo’), user_id(‘public’),
user_id(‘guest’); select user_name(), user_name(1), user_name(0),
user_name(2); select session_user; select suser_id(‘sa’); select
suser_sid(), suser_sid(‘sa’), suser_sid(‘sysadmin’),
suser_sid(‘serveradmin’); select is_member(‘dbo’),
is_member(‘public’); select suser_name(), suser_name(1),
suser_name(2), suser_name(3); select suser_sname(),
suser_sname(0x01), suser_sname(0x02), suser_sname(0x03); select
is_srvRoleMember(‘sysadmin’), is_srvRoleMember(‘serveradmin’); select
permissions(object_id(‘student’)); select system_user; select
schema_id(), schema_id(‘dbo’), schema_id(‘guest’); select
schema_name(), schema_name(1), schema_name(2), schema_name(3); 7、
系统函数 select app_name();–当前会话的应用程序名称 select cast(2011 as
datetime), cast(’10’ as money), cast(‘0’ as varbinary);–类型转换 select
convert(datetime, ‘2011’);–类型转换 select coalesce(null, ‘a’),
coalesce(‘123’, ‘a’);–返回其参数中第一个非空表达式 select
collationProperty(‘Traditional_Spanish_CS_AS_KS_WS’, ‘CodePage’);
select current_timestamp;–当前时间戳 select current_user; select
isDate(getDate()), isDate(‘abc’), isNumeric(1), isNumeric(‘a’); select
dataLength(‘abc’); select host_id(); select host_name(); select
db_name(); select ident_current(‘student’),
ident_current(‘classes’);–返回主键id的最大值 select
ident_incr(‘student’), ident_incr(‘classes’);–id的增量值 select
ident_seed(‘student’), ident_seed(‘classes’); select
@@identity;–最后一次自增的值 select identity(int, 1, 1) as id into tab
from student;–将studeng表的烈属,以/1自增形式创建一个tab select * from
tab; select @@rowcount;–影响行数 select
@@cursor_rows;–返回连接上打开的游标的当前限定行的数目 select
@@error;–T-SQL的错误号 select @@procid; 8、 配置函数 set datefirst
7;–设置每周的第一天,表示周日 select @@datefirst as ‘星期的第一天’,
datepart(dw, getDate()) AS ‘今天是星期’; select
@@dbts;–返回当前数据库唯一时间戳 set language ‘Italian’; select
@@langId as ‘Language ID’;–返回语言id select @@language as ‘Language
Name’;–返回当前语言名称 select
@@lock_timeout;–返回当前会话的当前锁定超时设置 select
@@max_connections;–返回SQL Server 实例允许同时进行的最大用户连接数
select @@MAX_PRECISION AS ‘Max Precision’;–返回decimal 和numeric
数据类型所用的精度级别 select @@SERVERNAME;–SQL Server
的本地服务器的名称 select @@SERVICENAME;–服务名 select
@@SPID;–当前会话进程id select @@textSize; select
@@version;–当前数据库版本信息 9、 系统统计函数 select
@@CONNECTIONS;–连接数 select @@PACK_RECEIVED; select @@CPU_BUSY;
select @@PACK_SENT; select @@TIMETICKS; select @@IDLE; select
@@TOTAL_ERRORS; select @@IO_BUSY; select @@TOTAL_READ;–读取磁盘次数
select @@PACKET_ERRORS;–发生的网络数据包错误数 select
@@TOTAL_WRITE;–sqlserver执行的磁盘写入次数 select patIndex(‘%soft%’,
‘microsoft SqlServer’); select patIndex(‘soft%’, ‘software SqlServer’);
select patIndex(‘%soft’, ‘SqlServer microsoft’); select
patIndex(‘%so_gr%’, ‘Jsonisprogram’); 10、 用户自定义函数 #
查看当前数据库所有函数 –查询所有已创建函数 select definition,* from
sys.sql_modules m join sys.objects o on m.object_id = o.object_id and
type in(‘fn’, ‘if’, ‘tf’); # 创建函数 if (object_id(‘fun_add’, ‘fn’)
is not null) drop function fun_add go create function fun_add(@num1
int, @num2 int) returns int with execute as caller as begin declare
@result int; if (@num1 is null) set @num1 = 0; if (@num2 is null) set
@num2 = 0; set @result = @num1 + @num2; return @result; end go 调用函数
select dbo.fun_add(id, age) from student; –自定义函数,字符串连接 if
(object_id(‘fun_append’, ‘fn’) is not null) drop function fun_append
go create function fun_append(@args nvarchar(1024), @args2
nvarchar(1024)) returns nvarchar(2048) as begin return @args + @args2;
end go select dbo.fun_append(name, ‘abc’) from student; # 修改函数
alter function fun_append(@args nvarchar(1024), @args2 nvarchar(1024))
returns nvarchar(1024) as begin declare @result varchar(1024);
–coalesce返回第一个不为null的值 set @args = coalesce(@args, ”); set
@args2 = coalesce(@args2, ”);; set @result = @args + @args2; return
@result; end go select dbo.fun_append(name, ‘#abc’) from student; #
返回table类型函数 –返回table对象函数 select name, object_id, type from
sys.objects where type in (‘fn’, ‘if’, ‘tf’) or type like ‘%f%’; if
(exists (select * from sys.objects where type in (‘fn’, ‘if’, ‘tf’) and
name = ‘fun_find_stuRecord’)) drop function fun_find_stuRecord go
create function fun_find_stuRecord(@id int) returns table as return
(select * from student where id = @id); go select * from
dbo.fun_find_stuRecord(2);

1.显示所有数据库
 show databases;//展示所有数据库
2,创建数据管理系统
 create database 数据库名字;
3,删除数据库
 drop database 数据库名字;
4,使用数据库
 use 数据库名字;–>database changed;
5,创建数据表
 creat table 表名(字段名 字段类型(约束),字段名2 字段类型(约束),);
  字段类型有:int(Integer) 整数型;
     float/double 单精度/双精度浮点型
     date 日期类型
     datetime 日期时间类型
     char 定长字符串
     varchar 可变长度字符串
     text 大文本对象,可用于存储超长字符串
     bold 二进制大对象,可用于存储图片等二进制数据
6,查看创建的数据表
 show tables;//仅仅是表名的展示;
7,查看表的结构
 desc 表名//desc student;
8,修改数据表名称
 alter table 表名 rename 新表名;//alter table student rename stu;
9,删除数据表
 drop table 表名;//delete table stu;
10,添加表字段
 alter table 表名 add column 字段名 字段类型;//alter table student add
column phone varchar(20);
 添加带默认值的字段名
 alter table student add column sex varchar() default’男’;
11,修改表字段类型
 alter table 表名 modify 字段名 修改的类型;//alter table student modify
sex varchar(10);
12,删除表字段
 alter table 表名 drop 字段名//alter table student drop phone;
13,约束 NOT NULL:非空约束;
  UNIQUE:唯一约束;
  PRIMARY KEY:主键,唯一标识该记录,非空且唯一;
  FOREIGN KEY:外键,指定该记录从属的主表记录;
   添加非空约束和主键;
    主键添加:alter table student add column id int primary key;
14,创建表数据时设置主键自增 ,以及名字非空;
 create table student(
 id int primary key auto_increment,
 name varchar(20) not null,
 age int,
 birthday date,
 xuefen float(3,1),//3代表一共有三位,1代表小数点后的位数.
 );
15,索引:索引的作用类似于书的目录,一个表可以有多个索引,每个索引都可用于加速该列的查询速度
 创建索引 creat index 索引名称 on 表名(字段名);
    create index nameindex on student(name);//创建指向student name
 删除索引 drop index 索引名称 on 表名;
    create index nameindex on student;
 劣势:当数据进行修改时,索引也需要进行更新,较为麻烦,同时索引信息需要一定的磁盘空间。
16,中文乱码需要键入 set names gbk;//解决名字乱码问题;
17,插入数据 insert into 表名(字段名1,字段名2) values(数据1,数据2);
 不带自定义id的插入学生信息:
  insert into student(name,age,birthday,xuefen)
values(‘科比’,39,’1978-8-30′,1.8);
 展示输入的学生所有信息:
  select * from student;
 展示表名中的部分学生信息:
  select age,xuefen from student;
 自带id的插入学生信息:
  select into student(id,name,age,birthday,xuefen)
values(5,’杜兰特’,29,’1988-9-29′,1.9);
 若此时再进行不带自定义id的形式插入学生信息,则会从自带id的后面进行自增长 
  insert into student(name,age,brithdaty,xuefen)
values(‘艾弗森’,42,’1975-6-7′,3.0);
澳门金沙vip, 若输入的顺序和展示出来表的字段的顺序相同,可省略字段部分;
  insert into student values(2,’汤普森’,’27’,’1990-2-8′,’3.5′);
18,修改数据 update 表名 set 字段1=值1,字段2=值2,where条件,
  没有添加where条件的会修改全部记录,
   uodate student set sex=男;
  添加where 一般指定id;
   update student set xuefen=3.0 where id=4;
19,删除数据 delete from 表名 where 条件;//以行为单位进行整行删除
    delete from student where id=7;
20,查询数据{单表查询,联合查询};
 单表查询:单条件查询,去重查询,查询条件中的运算符,函数查询,分组查询,分页查询,结果排序
   20.1 单条件查询:select * from 表名;(全部元素都会显示出来);
        select * from student;
     展示特殊字段 select 字段1,字段2 from 表名
         select name,age from student;
   查询特殊条件的 select * from 表名 where 条件;
       select * from student where age>30;
   查询多个特殊条件的;条件1 and 条件2;//与
        条件2 or 条件2;//或
   20.2 去重查询:可以使用distinct关键字从查询结果中消除重复数据;
     select distinct 字段1,字段2 from 表名;
     select distinct name,xuefen from
student;name和学分都重复才会去除;
   20.3 查询条件中的运算符;比较运算符=,!=,<>
,>,>=,<,<=;
           逻辑运算符 and:与 or 或 not:非 优先级 not>and>or
      优先级的检测;
      select * from student where 1<>1 and 1=1 or 1=1 or 1=1;
      select * from student where 1=1 or 1=1 or 1=1 and 1<>1;
     使用between and
      between 16 and 20 ;//可以取到16 和20,这两个边界值。
     使用in运算符
      select * from student where age in(10,30,40,29);
      跟表中的年龄进行比对,看有没有能够匹配相等的,有,则显示
     使用like运算符
      模糊运算符,像
      select * from student where name like ‘%张’;
      ‘%’代表无限个字符  ‘%张’是指以张结尾的名字
      ‘%张%’中间有张字的名字 ‘张%’是指以张开头的名字
      ‘__张’以’张’字结尾的三个字的名字,’_’代表一个字符;
     使用is null运算符
   20.4 函数查询
     max:求最大值,min:求最小值,avg:求平均值,sum:求和,count:求数量
     case 函数,类似于switch;
      select max(age) from student;//选择最大年龄;
      最大年龄对应的名字
      select age,max(age) from student;
      //展示max(age)对应的所有信息;
      //select max(age) from student–>返回的是一个最大年龄的表单
      select * from student where age=(select max(age) from student);
     count的应用:
      select count(1) from student;
     case:的应用
      select * ,case
      where age>30 then ‘老年人’
      where age<30 then ‘中年人’
      end
      from student;
     实例1:查询所有大于平均年龄的学生记录
     //平均年龄 select avg(age) from student;
     //select * from student when age>(select avg(age) from
student);
   20.5 分组查询
     group by 分组字段;
      //案例:平均年龄按照男女性别分组
      select avg(age) from student gronp by sex;
      //显示男女性别,同时平均年龄按照男女性别分组
      select age,avg(age) from student group by sex;
     having 分组条件
      //案例,显示男女性别,同时平均年龄按照男女性别分组但是大于平均年龄的要小于3个
      select sex avg(age) from student group by sex having count<3;
     where 和having 的区别
     where用于过滤行,having 用于过滤组,where 子句中不能使用组函数,
      having子句中可以使用组函数
20.6 分页查询 limit x,y;x代表下标,y代表个数
     select * from student limit 0,2;起始下标为0的位置显示两天信息;
20.7 排序查询 order by 字段//默认是升序
     select * from student order by age (asc);//默认按年龄升序排列
     select * from studnet order by age desc;//按年龄降序排列;
      案例1:先按年龄升序排序,再取出年龄最大的两个
       select * from student by age asc limit 0,2;
      案例2:先按年龄升序排序,如果年龄相同再按学分降序排序
       select * from student by age ase,xuefen desc;
  ##分组的顺序优先级:
  select * from student group by () having where order by limit;
21 关联查询
  21.1 连接查询
     交叉连接,使用on子句的连接,左连接,右连接;
      21.1.1 交叉连接
      select * from student cross join class;
      添加别名进行交叉连接选择
      //用于挑选一个学生选择一门课程时的展示数据
      select * from student s cross join class c where s.kid=c.id;
      //挑选所有选了某个课程的学生
      select s.* from studnet s cross join class c where s.kid=c.id and
c.classname=”crossover”;
      21.1.2 on连接
         select * from student s join class c on;
      21.1.3 左连接
        select * from student s left join class c on s.kid=c.id;
   21.1.4 右连接
     select * from student s right join class c on s.kid=c.id;
  21.2 子查询
      1.出现在from语句后当成数据表,
   2.出现在where条件后作为过滤条件的值,
     //案例查询所有选择某门课程中学生年龄最大的;
     //所有选择某门课程的学生
     select * from student s join class c on s.kid=c.kid and
classname=””;
     //筛选年龄最大的
     先进行排序再进行分页
      select * from( select * from student s join class c on
s.kid=c.kid and classname=””) t
      order by t.age desc limit 0,1;