select 语句简明教程.docx

一、简单查询 简单的Transact-SQL查询只包括选择列表、FROM子句和WHERE子句。它们分别说明所查询列、查询的表或视图、以及搜索条件等。 例如，下面的语句查询testtable表中姓名为 张三 的nickname字段和email字段。 SELECT nickname,email FROM testtable WHERE name= 张三 (一) 选择列表 选择列表(select_list)指出所查询列，它可以是一组列名列表、星号、表达式、变量(包括局部变量和全局变量)等构成。 1、选择所有列 例如，下面语句显示testtable表中所有列的数据： SELECT * FROM testtable 2、选择部分列并指定它们的显示次序 查询结果集合中数据的排列顺序与选择列表中所指定的列名排列顺序相同。 例如： SELECT nickname,email FROM testtable 3、更改列标题 在选择列表中，可重新指定列标题。定义格式为： 列标题=列名 列名 列标题 如果指定的列标题不是标准的标识符格式时，应使用引号定界符，例如，下列语句使用汉字显示列标题： SELECT 昵称=nickname,电子邮件=email FROM testtable 4、删除重复行 SELECT语句中使用ALL或DISTINCT选项来显示表中符合条件的所有行或删除其中重复的数据行，默认为ALL。使用DISTINCT选项时，对于所有重复的数据行在SELECT返回的结果集合中只保留一行。 5、限制返回的行数 使用TOP n PERCENT选项限制返回的数据行数，TOP n说明返回n行，而TOP n PERCENT时，说明n是表示一百分数，指定返回的行数等于总行数的百分之几。 例如： SELECT TOP 2 * FROM testtable SELECT TOP 20 PERCENT * FROM testtable (二) FROM子句 FROM子句指定SELECT语句查询及与查询相关的表或视图。在FROM子句中最多可指定256个表或视图，它们之间用逗号分隔。 在FROM子句同时指定多个表或视图时，如果选择列表中存在同名列，这时应使用对象名限定这些列所属的表或视图。例如在usertable和citytable表中同时存在cityid列，在查询两个表中的cityid时应使用下面语句格式加以限定： SELECT username,citytable.cityid FROM usertable,citytable WHERE usertable.cityid=citytable.cityid 在FROM子句中可用以下两种格式为表或视图指定别名： 表名 as 别名 表名 别名 例如上面语句可用表的别名格式表示为： SELECT username,b.cityid FROM usertable a,citytable b WHERE a.cityid=b.cityid SELECT不仅能从表或视图中检索数据，它还能够从其它查询语句所返回的结果集合中查询数据。 例如： SELECT a.au_fname,a.au_lname FROM authors a,titleauthor ta (SELECT title_id,title FROM titles WHERE ytd_sales 10000 ) AS t WHERE a.au_id=ta.au_id AND a.title_id=t.title_id 此例中，将SELECT返回的结果集合给予一别名t，然后再从中检索数据。 (三) 使用WHERE子句设置查询条件 WHERE子句设置查询条件，过滤掉不需要的数据行。例如下面语句查询年龄大于20的数据： SELECT * FROM usertable WHERE age 20 WHERE子句可包括各种条件运算符： 比较运算符(大小比较)： 、 =、=、 、 =、 、! 、! =10 AND age 、 =、 =、 、! 和 。 3、自然连接：在连接条件中使用等于(=)运算符比较被连接列的列值，但它使用选择列表指出查询结果集合中所包括的列，并删除连接表中的重复列。 例，下面使用等值连接列出authors和publishers表中位于同一城市的作者和出版社： SELECT * FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city 又如使用自然连接，在选择列表中删除authors 和publishers 表中重复列(city和state)DELETE a.*,p.pub_id,p.pub_name,p.country FROM authors AS a INNER JOIN publishers AS p ON a.city=p.city or a.state=p.state (二)外连接 内连接时，返回查询结果集合中的仅是符合查询条件(WHERE搜索条件或 HAVING 条件)和连接条件的行。而采用外连接时，它返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接时)、右表(右外连接时)或两个边接表(全外连接)中的所有数据行。如下面使用左外连接将论坛内容和作者信息连接起来： SELECT a.*,b.* FROM luntan AS a LEFT JOIN usertable AS b ON a.username=b.username下面使用全外连接将city表中的所有作者以及user表中的所有作者，以及他们所在的城市： SELECT a.*,b.* FROM city as a FULL OUTER JOIN user as b ON a.username=b.username(三)交叉连接 交叉连接不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积，返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。 SELECT type,pub_name FROM titles CROSS JOIN publishers ORDER BY type 例题：CREATE TABLE Student (Sno CHAR(5) NOT NULL UNIQUE,Sname CHAR(20),Ssex CHAR(1),Sage INT,Sdept CHAR(15);插入记录：insert into Student (Sno,Sname,Ssex,Sdept) values(aaa,mary,f,172); 删除记录：delete from Student where Sno = aaa;注：只需要删除一个主键就可以了。其他的记录会相应的删除掉。删除表中一个字段：ALTER TABLE Student DROP column Ssex; 列名;修改表中的那一行数据：原来的记录：Sno Sname Ssex Sdeptaaa mary f 172update Student set Sname=mary1, Ssex=m where Sno=aaa;修改后：Sno Sname Ssex Sdeptaaa mary1 m 172desc倒叙排列：建立索引：create unique index Sno on Student(Sno);索引的一点好处：在查询时候比较方便，在存在的所有记录中查找一个Sno=1的时候！建立索引的表中就直接查找Sno项比较它是否=1找到后查相关的记录就比较快。没有建立索引的需要把所有信息都查找一遍，再在其中找Sno字段，再比较其值=1的相关记录。默认是ASC。按表中哪个字段倒叙排序：select * from Student order by Sno desc;注意：要排序的字段必须是int型。设置成自增长的字段在插入数据的时候不需要插入该字段的值：select * from Student order by Sno desc;原来没有设置成自增长插入数据命令：insert into Student (Sno,Sname,Ssex,Sdept) values(aaa,mary,f,172); 将int型的Sno字段设置成自增长后insert into Student (Sname,Ssex,Sdept) values(mary1,f,172); insert into Student (Sname,Ssex,Sdept) values(mar1y,f,172); insert into Student (Sname,Ssex,Sdept) values(ma1ry,f,172); insert into Student (Sname,Ssex,Sdept) values(m1ary,f,172); 在表中的排序如下:Sno Sname Ssex Sdept1 mary1 f 1722 mar1y f 1723 ma1ry f 1724 m1ary f 172查询表中记录总数：(无字段名字)select count() from usertable;或：(userid 为字段名字，结果是字段的总行数)select count(*) userid from Student;查询字段的平均值：selecet avg(Sno) from Student; select avg(字段名)from 表名;给出查询的字段的平均值取别名:select avg(字段名) as (别名) from (表名);查找指定的字段的其他字段select Sdept,Ssex,Sname from Student where Sno=3; (where Sname=mary1;或则where Sname like mary1;)在between语句查询的都是在and之间的所有值而IN语句则必须是in括号里面的值.select Sno,Ssex,Sname from Student where Sdept between 180 and 190;select Sno,Ssex,Sname from Student where Sdept in (172,190);查询Student表中的所有的名字中的Sno和Ssex值.select Sno,Ssex from Student where Sdept = 170 and Sname like %;注:%之间是把所有的String类型的值like和where条件查询select last_name,salsry,department_id from employees where last_name like G% and salary3000;查询Student表中的所有的名字中间有mary的所有名字对应的的Sno和Ssex值.select Sno,Ssex from Student where Sdept = 170 and Sname like %mary%;注:mary1,1mary,marydsajdh,等.注意:and or not 用的时候,and是用在连接并列条件的2个不同的字段or是用在选择的2个不同的字段之间,not一般用于not in(180,190)之间.order by 和asc|desc的一点不同:order by是按先进先排,desc是先进后排,asc和desc是排列顺序不一样.disctinct条件查询distinct 这个关键字来过滤掉多余的重复记录只保留一条,select distinct name from table 表示将返回table表中name字段不重复的所有字段的集合。注：distinct必须放在开头,select id distinct name from table 是错误的!-表TEST_1：id name1 name2 9 aaa 118 bbb 223 ccc 334 ccc 44 5 bbb 556 ddd 667 eee 77-select distinct(name1) from test_1 结果：name1aaabbbcccdddeeedistinct这个关键字来过滤掉多余的重复记录只保留一条，但往往只用它来返回不重复记录的集合，而不是用它来返回不重记录的所有值。其原因是distinct只能返回它的目标字段，而无法返回其它字段，即上表中只能返回name1字段的所有不重复记录集合。-如果现在想查询出表test_1中所有name1不重复的id和name1字段的记录呢?select distinct name, id from table 结果：name1 idaaa 9 bbb 5bbb 8ccc 3ccc 4ddd 6eee 7并不是我们所想要的结果select distinct name,id from table 作用了两个字段，也就是必须得id与name都相同的才会被排除，即返回去除table表中id和name两个字段同时相同情况下所有集合。-如果现在想查询出表test_1中所有name1不重复的所有字段的记录呢?对于select * from test_1 where name1 in (select distinct name1 from tablename) 等价于select * from test_1 where name1 in (aaa,bbb,ccc,ddd,eee)我们很可能认为它是可行的，但实际上结果却是：id name1 name2 9 aaa 118 bbb 223 ccc 334 ccc 44 5 bbb 556 ddd 667 eee 77正确sql应该是：select * from test_1 inner join (select min(id) as id from test_1 group by name1 order by id ) T on test_1.id = T.idmin(id)或max(id)都行,order by id 可要可不要.这里用内连接实现联合查询，也可以用where语句select * from test_1 ,(select min(id) as id from test_1 group by name1 order by id ) where test_1.id = T.id-*联合查询*查询表中的各个字段的值select Sno,Sname,Ssex,Sdept from Student;多表查询(2个表,publishtable和publishtable,给表起了别名)select u.userid,u.age,u.username,p.publishname from usertable u,publishtable p where u.userid = p.publishid;多表查询(3个表employees，departments和locations表，给表起别名)(从多表中查询出所有姓smith的雇员的名字，所在部门以及部门所在的城市)select e.first_name,e.last_name,d.department_name,l.city from employees e,departments d,locations l where e.department_id = d.department_id and d.location_id = l.location_id and e.last_name = smith;* 联合查询*等值连接/*将books表和表bookstype中的信息联合查询，条件是联系键相等*/select * from books,bookstype where bookstype.typeid = books.typeid内连接/*将books表和表bookstype中的信息联合查询，条件是联系键相等，和等值连接等价*/select * from books inner join bookstype on books.typeid = bookstype.typeid 左外连接/*将books表和表bookstype中的信息联合查询，包括在books表中没有和bookstype表关联的信息*/select * from books left outer join bookstype on bookstype.typeid = books.typeid右外连接/*将bookstype表和books表中的信息联合查询，包括在bookstype表中没有和books表关联的信息*/select * from books right outer join bookstype on bookstype.typeid = books.typeid全连接/*将bookstype表和books表中的信息联合查询，包括在books表中没有和bookstype表关联的信息以及在bookstype表中没有和books表关联的信息*/select * from books full outer join bookstype on bookstype.typeid = books.typeid* 联合查询*多表查询应该注意的几点:多表查询和单表查询的不同在于多表查询在查询某个字段的时候应该带上表名格式是:表名.字段名,表名.字段名select bbs.id,,bbs.dep,bbsr.id, from bbs,bbsr where bbs.id=bbsr.id;一般的在多表查询中的表名取的麻烦用别名来代替如下:select b.id,,b.dep,c.id, from bbs as b ,bbsr as c where b.id=c.id;*注意取别名也可以用空格或则用as.* 分组查询*在select 语句中可以使用group by 子句将行划分成较小的组，然后，使用聚组函数返回每一个组的汇总信息，另外，可以使用having子句限制返回的结果集。group by 子句可以将查询结果分组，并返回行的汇总信息SQLServer 按照group by 子句中指定的表达式的值分组查询结果。分组查询一般是用来进行统计需求的，要进行分组查询必须使用group by子句或having子句，在分组统计时要用到SQL的多行存储函数。在where条件中不能使用多行存储函数.查询语句的select 和group by ,having 子句是聚组函数唯一出现的地方，在where 子句中不能使用聚组函数。在带有group by 子句的查询语句中，在select 列表中指定的列要么是group by 子句中指定的列，要么包含聚组函数.-部门表department：department_id department_name10 开发部20 测试部30 用服部40 财务部50 培训部雇员表employees：(department_id为外键)id employees_name department_id1 cheng 102 zhou 503 liguo 10 4 wang 205 feng 306 ling 307 wu 20 8 tang 209 yang 4010 tan 50Select e.employees_name, d.department_id from employees as e,department as d where e.emplyees_name=%ng and d.department_id=20 order by d.department_id DESC按部门编号统计出表employees中的每个部门的人数(group by分组查询)select department_id, count(*) from employees group by department_id;select e.department_id ,count(e.employees_name) from employees as e where e.department_id in (select d.department_id from department as d)显示出部门人数少于5人的部门编号以及人数( 按照department_id来分组，having count(*)5做为限制条件 )select department_id ,count(*) from employees group by department_id having count(*)5000 group by department_id having count(*)5显示出所有工资大于5000，且部门人数大于5个的部门号，部门最低工资和部门人数select department_id 部门号, min(salary) 最低工资,count(*) 部门人数 from employees where salary 5000 group by department_id having count(*)5;Ex 2 部门号 最低工资 部门人数 80 6100 34 100 6800 6 group by 按照哪个字段来排序查询，一般的group by和having一起使用，注意在查询中只能查询与group by和having有关的字段。 where 子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。 having 子句的作用是筛选满足条件的组，即在分组之后过滤数据，条件中经常包含聚组函数，使用having 条件显示特定的组，也可以使用多个分组标准进行分组。Ex 3 查询每个部门的每种职位的雇员数(待测试.) select deptno,job,count(*) from emp group by deptno,job;* 数据排序*order by子句中可以用asc(升序)和desc(降序)来完成相应的排序，默认时是按找asc(升序)。显示出所有工资大于5000，且部门人数大于5个的部门号，部门最低工资和部门人数,并要求按照部门人数降序排序.select department_id 部门号（记住department_id 和部门号之间要空格）, min(salary) 最低工资,count(*) 部门人数 from employees where salary 5000 group by department_id having count(*)5 order by count(*)desc(注意:当对分组查询后的结果进行排序时，order by 必须在group by之后) 部门号 最低工资 部门人数 80 6100 34 100 6800 30 90 6500 28 70 6300 25* 子查询 *子查询是一个在select查询中含有其他的select语句，子查询通常用在where子句中，即将一个查询结果做为查询的条件.统计表employees中所有工资小于平均工资的人数Select count(*) 人数 from employees where salary (select avg(salary) from employees)select count(*)工资 from employees where salary (Select MIN(Salary) From Employee where Manager_id=004); 注意在多表查询中返回的值应该是一个唯一确定的值，而不应该是多中值。select Employee_id,Last_name,Salary,Manager_id from Employee WHERE Salary(select Salary from Employee where Manager_id=003) ANDLast_name= (select Last_name from Employee where Last_name like mary);注意在any，or，all的区别：Select Salary,Manager_id,Last_name,Employee_idFrom Employee where Salary any (Select Salary From Employee where Manager_id=005); Select Salary,Manager_id,Last_name,Employee_idFrom Employee where Salary All (Select Salary From Employee where Manager_id=005); * 集合运算 *集合运算是将2个或多个子查询结果进行(并union),交(intersect)和减(minus)(并union):select employee_id,empname from emp1 union select employee_id,name from emp2Ex 1:employee_id empname100 king11000 king21000 king310000 king4100000 king51000000 king6注:使用union,重复行只包括一个,当使用union al