SQL查询语句 select用法

关键字select查询
//* --返回所有字段
关键字as --可以省略
// --字段别名，可以使用as “编号”，也可以直接使用“姓名”
// --字段可以直接加减乘除
// select 常数 from 表名 --直接返回表常数的行数 常数显示行数列
// select 常数 --只返回一行常数
//distinct --表示唯一，如果是一列，对这一列进行去重复
//如果是多列，则多列去重复

select * from teacher;
select teacher_id "编号",teacher_name as "姓名",teacher_age-20 as "工龄",teachar_tec "课程" from teacher;

select 7 from teacher;
select 3;

select teacher_name, teacher_age from teacher;   --一列去重复
select distinct teacher_name,teacher_age from teacher; --二列组合去重复
select teacher_name,distinct teacher_age from teacher; --语法错误
select teacher_name, teacher_age from teacher distinct; --语法错误

关键字：between 在两个数之前

select * from teacher where teacher_age between 25 and 35;
select * from teacher where teacher_age >=25 and teacher_age <=35;
select * from teacher where not teacher_age < 25 or teacher_age >35;

关键字:in 表示包含某个孤立的值
关键字：not 表示非
关键字：and 表示和
关键字：or表示或
运算符：=等于 ！=不等于 <>不等于

select * from teacher where teacher_age in(25,35);
select * from teacher where teacher_age =25  or teacher_age = 35;
select * from teacher where teacher_age not in(25,35);
select * from teacher where teacher_age != 25 and teacher_age != 35;
select * from teacher where teacher_age <> 25 and teacher_age <> 35;

关键字：top N 表示前N个
关键字：percent N 表示百分之N

select top 3 * from teacher ;
select top 50 percent * from teacher;

关键字：order by 按顺序排列
关键字：desc 降序排列
关键字：asc 升序排列

select top 5 * from teacher where teacher_age >20 order by teacher_age desc;
select top 5 * from teacher where teacher_age >=20 order by teacher_age asc;

关键字：NULL 表示空值，任何值类型与NULL值运算后，结果是NULL，不要与NULL参与运算
关键字：is和is not --null不能使用=、！=和<>符号进行比较，只能使用is,is not进行对比
函数：isnull(字段，值)，可以用isnull(teacher_tec,0)来用0来代替null的值

use test;
select * from teacher;
insert into teacher values(1007,'单于张',36,NULL);
select teacher_name,teachar_tec from teacher where teachar_tec is null;
select teacher_name,teachar_tec from teacher where teachar_tec is not null;
select teacher_name,teachar_tec from teacher where teachar_tec = null; 
select teacher_name,teachar_tec from teacher where teachar_tec <> null;
select teacher_name,teachar_tec from teacher where teachar_tec != null;
--null不能参与= != <>
select teacher_name as "姓名",isnull(teachar_tec,0) as "课程" from teacher where teachar_tec is null;
--可以使用isnull(字段,值)这个函数来显示出来
select teacher_name as "姓名",isnull(teachar_tec,'英语') as "课程" from teacher where teachar_tec is null;

关键字：like 模糊查询
通配符号：
% 表示任意多个字符，当要表示%使用另一个符号进行替换 比如like ‘\%’ escape ‘\’
关键字：escape 表示排除
_ 表示1个字符可以是任意字符（占位一个字符）
[a-z]表示从a到z字符的任意一个
[^a-z]表示不包含a-z的字符中任意一个
[a,z]表示一个字符是方括号里的任意一个字符

select * from teacher where teacher_name like '%张_%';
select * from teacher where teacher_name like '%张\%%' escape '\';
select * from teacher where teacher_name like '%$%张' escape '*';

insert into teacher values(1012,'abc',36,NULL);
insert into teacher values(1013,'cab',36,NULL);
insert into teacher values(1014,'dba',36,NULL);
insert into teacher values(1015,'fine',36,NULL);

select * from teacher where teacher_name like '[a-c]%';
select * from teacher where teacher_name like '[^a-c]%';
select * from teacher where teacher_name like '[a,c]%';

–聚合函数
–多行函数 多行返回值为多行的函数
–单行函数 多行返回值为单行的函数
–单行函数和多行函数不可以混用,混用后会出错
–lower()小写
–upper()大写
–max()最大值
–min()最小值
–agv()平均值
–count()个数 返回记录非空值(NULL)个数

select count(teacher_age) from teacher;
select count(distinct teacher_age) from teacher;
select count(teachar_tec) from teacher;  --NULL会被count()函数所忽略
select max(teacher_age) as '最大年龄',min(teacher_age)as '最小年龄',count(*)as'所有行数' from teacher;

关键词：group by 分组排序

select teachar_tec'课程',avg(teacher_age)'教师平均年龄' from teacher group by teachar_tec order by avg(teacher_age) desc;
select teachar_tec'课程',avg(teacher_age)'教师平均年龄',teacher_age'年龄' from teacher group by teachar_tec,teacher_age order by teacher_age desc;

关键词：having 对分组信息进行过滤

select teachar_tec'课程',avg(teacher_age)'教师平均年龄' from teacher group by teachar_tec  having avg(teacher_age) >32 order by avg(teacher_age) desc;
select teachar_tec'课程',avg(teacher_age)'教师平均年龄',teacher_age'年龄' from teacher group by teachar_tec,teacher_age having teacher_age >30 order by teacher_age desc ;

where和having区别
–都是对记录进行过滤,保留过滤后的记录
–where对没有分组的记录进行过滤
–having是对分组后的记录进行过滤 （过滤条件只能原始字段名，不能使用别名，否则会报错）
–where必须写在having之前
–having使用之前使用group by，如果没有使用group by，则将所有的记录则单独成一组进行处理。

select 语句顺序（重要）
select top N 字段1，字段2
into table2 查询的结果存入一个新表
from tableA
join tableB
on 链接条件
join table C
on 链接条件2
where 过滤条件
group by 分组条件
having 按组过滤条件，必须出现在分组信息中
order by 排序输出
内链接：inner join … on过滤条件
外链接：left 【right】join … on 过滤条件
外链接的链接是如果左边表的条件和右边表的条件一致，则列出右表的记录，如果右表的条件与左边和条件不一致时，则输出null进行填充