常用的SQL语句--1

1.基本查询：
定义列别名：
select aname 名字 from admininfo;
select aname ‘名字’ from admininfo;
select aname “名字” from admininfo;
select aname as 名字 from admininfo;
使用列计算：
select gname,gamount*gpin as 积 from goodsinfo;
字段别名可以使用在ORDER BY子句中，不能使用在where，group by或having子句中。
查询指定行数据：
select top 3 * from goodsinfo;

去除重复的行/列数据：
select distinct gunit from goodsinfo;
select count(*) from goodsinfo;
distinct是sum（），AVG（） count（）函数的可选关键字。使用它来消除重复的值。

计算数据在结果集中的行号：row_number()
1.在查询结果集中显示行号：select row_number() over(order by Gid desc) as number,gpin,gmount from goodsinfo；--server2008

随机查询n行数据（NEWID（）函数）：
随机查询两行数据：select top 2 * from goodsinfo order by newid();

2.select语句：
使用where语句：
 select * from goodsinfo where Gcid=10001 or/and gpin>10;

通过in运算符给出查询范围：in/not in
select * from goodsinfo where gname in ('苹果','桃子','桔子');
相当于select * from goodsinfo where gname='苹果' or gname='桔子' or gname='桃子'；

使用between/not between and查询数据：
select * from goodsinfo where Gcid between 10001 and 10004;相当于
select * from goodsinfo where Gcid >= 10001 and gcid <= 10004;


利用like并引用通配符、转义字符进行模糊查询：
like谓词只用于下列数据类型：char,nchar,varchar,nvarchar,datetime.
%表示一个或多个字符。
select * from goodsinfo where gname like'%5000';
_表示单个字符串。
select * from goodsinfo where gname like'__________5000';
[]，如[A~F]表示由A到F范围内的任何单个字符。
select * from goodsinfo where gname like'%[0-6]%';
[^],如[^A~F]表示由A到F范围外的任何单个字符。

查询数据字段为空的数据：
 select * from goodsinfo where gname is not null/is null;

使用all/any谓词查询数据：
select * from goodsinfo where gpout > all (select gpin from goodsinfo);
表示大于gpin的最大值的gpout；
select * from goodsinfo where gpout < any (select gpin from goodsinfo);
表示小于gpin的最小值的gpout；


通过exists查询：
select gname from goodsinfo where exists (select gname from admininfo);

select语句：order by 子句排序：
order by gcid ASC/DESC;

在order by中使用表达式：
select * from goodsinfo order by sum(gpin) + gid asc;



3.数值查询：

利用三角函数来计算数据：select sin(30*pi()),sin(2),cos(1),Asin(1),tan(1);

生成随机查寻记录：
select floor(rand()*100);1~100之间的随机数。

判断给定数据的符号：
select sign(10);

绝对值：
select abs(-10);

判断是否是数据:
select isnumeric(ni);

字符串查询：
在空格中去除空格



4.聚合数据检索：

对多列的求和运算：
sum();求和函数。
distinct：指定sum()函数返回唯一值（无重复值累加）的和。

求大于平均值的数据：
select name 名字，salary 工资 from worker where salary>(select avg(salary) from teacher);

最大最小值：
select max（成绩_clounm） from result;
select min（成绩_clounm） from result;

求无重复不为空的记录数
count();
select 班级，count(*)as 学生数量 from class group by 班级；  统计每个班级的人数。


统计某个值出现的次数：

select sum(
          case when 成绩='优' and 迟到早退=1
          then 1
          else 0
        end
)

5.分组统计数据：

分组统计总数据并排序：
select type ,price,advance from title where type like '%cook'
order by type
compute sum(price),sum(advance) by type


使用all查询全部分组：
select name,publicer,sum(yuan) as sum
from book
where publicer = 'jixie'
group by all name,publicer;
使用all关键字的group by包含所有的分组，搜索结果不仅仅是符合where（如publicer = jixie）条件的行。


使用cube生成带有小记和总计的交叉表：包括所有的组合（也包括null）
select 项目，颜色，sum(质量)AS 总质量
from 存货表
group by 项目，颜色 with cube；


使用where子句和having子句指定查询及分组条件：

select id,total from titles group by id HAVING SUM(sales)>4000;
having子句的行为where子句类似，where子句在group by（分组）之前使用，having在group by之后使用。
having可以包含聚集函数，having子句可以引用选择列表中出现的任意项。

使用OVER子句和聚合函数实现分区聚合数据；
使用compute返回数据明细及汇总结果（多个结果集）；
compute by返回分组信息及对应汇总数据；

6.子查询：
子查询时select语内的另一条select语句，通常，语句内可以出现表达式的地方都可以使用子查询。
not in/in select from where all any/some exists having update insert into delete 中使用子查询。

7.连接查询：
实现两表的笛卡尔积查询（cross join）:
将两表中的关联数据全部查询出。
select userinfo.*,profession.* from userinfo cross jion profession;

mysql> select admininfo.aid,admininfo.aname,goodsinfo.gname from admininfo cross join goodsinfo;

在where子句中设置连接条件：
select name,sex,year,tcname from
student,course,teacher where student.autonumb = course.numb and course.autonumb = teacher.numb;

使用inner join 实现内连接连接：
mysql> select admininfo.aid,admininfo.aname,goodsinfo.gname from admininfo inner join goodsinfo;
没有使用on或where的inner join和cross join效果相同。
也和使用and或“,”的多表查询结果相同。

mysql> select admininfo.aid,admininfo.aname,goodsinfo.gname from admininfo inner
 join goodsinfo on admininfo.aid=goodsinfo.gid;此处on作用和where相同。

select a.员工姓名,a.基本工资,b.员工姓名,b.智本公子
from a and b
where a.基本工资>b.基本工资；
或者：
select a.员工姓名,a.基本工资,b.员工姓名,b.基本公子
from 员工表 a inner jion 工资表 b
on a.基本工资>b.基本工资；

多表内连接（inner jion的嵌套）：
select name,sex,year,tcname
 from(student inner jion course on student.autonumb =course.numb)
inner jion teacher on course.autobuumb = teacher.numb;


使用left outer join以左表为依据查询匹配数据（左外连接）：以右匹配左。
mysql> select admininfo.aid,admininfo.aname,goodsinfo.gname from goodsinfo
left outer join admininfo on admininfo.aid=goodsinfo.gid;

使用right outer join以左表为依据查询匹配数据（右外连接）：以左匹配右。
mysql> select admininfo.aid,admininfo.aname,goodsinfo.gname from goodsinfo
right outer join admininfo on admininfo.aid=goodsinfo.gid;

使用full join查询两表匹配的所有数据：
使用left outer join以左表为依据查询匹配数据（左外连接）：以右匹配左。
mysql> select admininfo.aid,admininfo.aname,goodsinfo.gname from goodsinfo
full join admininfo on admininfo.aid=goodsinfo.gid;


多表外连接查询（外连接嵌套）
left join 是left outer join的简写，left join默认是outer属性的。 Inner Join Inner Join 逻辑运算符返回满足第一个（顶端）输入与第二个（底端）输入联接的每一行。这个和用select查询多表是一样的效果，所以很少用到； outer join则会返回每个满足第一个（顶端）输入与第二个（底端）输入的联接的行。它还返回任何在第二个输入中没有匹配行的第一个输入中的行。关键就是后面那句，返回的多一些。所以通常意义上的left join就是left outer join


8.组合查询：

使用union组合多个结果集（并集）：
select * from 期中考试成绩表
where 总分>520
union
select * from 期末考试成绩表
where 总分>520；
注意：union的两个表列的数量和数据类型要兼容，即相同的表结构。
      结果集的列名取自第一个表的列名；
      组合查询时，默认从最后一个表中删除重复行的数据，使用all关键字，保留重复。

使用union all 组合保留重复数据。

组合后的查询数据排序：
(select..) union (select...)...order by...;

两个结果集的交集（intersect）
交集指两个结果集中完全相同的所有行。
(select..) intersect (select...)...order by...;

求两个结果集的差集（except）：
交集的补集；
(select..) except (select...)...order by...;




9.交叉表，递归与分布式查询：
使用case语句指定分类注释：
如果对成绩判断是否优秀并在备注中标识出：
select name,score,备注= case when score<80 then '及格'
                             when score>80 then '优秀'
                             else '__'
                        end
 from score;




 
10.优化操作

创建索引加快数据引用：

非聚集、唯一、聚集索引

数据按存储结构的不同将索引分为两类：聚集索引（ClusteredIndex）和非聚集索引（NonclusteredIndex）。

聚集索引：表中数据行的物理存储顺序与索引顺序完全相同。索引有上下两层，上层为索引页，包含表的索引页面，用于数据检索；下层位数据页，包含实际的数据页面，存放表中数据。在表的某一列创建索引时，表中的数据会按列的索引顺序重新排列，并对表进行修改。每个表中只能创建一个聚集索引。聚集索引要创建在经常查询或按顺序访问的列。

非聚集索引：
不改变表中数据行的物理存储位置，数据与索引分开存储，通过索引带有的指针与表中的数据发生联系。非聚集索引与课本中的索引类似。数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。索引中的项目按索引键值存储，而表中信息按另一种顺序存储。

一个表中可包括多个非聚集索引。可以为表中查找数据时常用的每个列创建非聚集索引。
一个表最多可以建249个个非聚集索引。