以下只针对SQL Server,其它数据库类型可能会不支持某些函数或函数名不同。
选择数据:select
select用于选择列,where用于选择行,*表示所有行和列。
select GETDATE();--输出当前日期和时间:2014-12-24 15:59:05.250
select [au_id] from authors; --列出au_id列的数据
select * from persons; --输出所有列的数据
select [au_id] * 10 from authors;
select 'dfd' + 'ad';
对于MySQL,字符串不能使用+来连接,而是使用concat()函数,而且不仅是concat(),如果在算数表达式中使用null,则整个表达式就成了null。
选择其它数据库中的数据:
select * from [my_db].[dbo].[authors]; --列出my_db数据库中authors表中所有列的数据
去除重复:distinct
select distinct [city] from authors; --列出城市,并且去除重复的城市名
select distinct [province],[city] from authors; --列出省份和城市,并且去除省份和城市都相同的重复数据
distinct 会处理空值,它会去掉重复的空值。
条件过滤:where
数值、日期等的比较:=, <>, >, <, >=, <=
select * from titles where [name] <> '张三';--输出所有列的数据,去除名字为张三的那些条记录的数据
select [book_name] from titles where [pages] > 500;--列出页数大于500的图书
select [book_name], [price]*[sales] as 'revence' from titles where revence >= 10000;--列出收入大于10000的图书
select [book_name] from titles where [pubdate] > '2000-01-01';--列出出版日期在2000年以后的图书
逻辑判断与、或、非:and、or、not
select [book_name], [type] from titles where [type]='history' or [type]='computer'; --列出类型为历史或计算机的图书(历史和计算机图书穿插列出)
select [book_name], [type] from titles where [type]='history' union
select [book_name], [type] from titles where [type]='computer'; --列出类型为历史或计算机的图书(先列出所有历史类图书再列出所有计算机图书)
select [book_name] from titles where [price] > 20 and ([type]='history' or [type]='computer'); --列出价格大于20且类型为历史或计算机的图书
ALL、ANY、SOME
可以用 ALL 或 ANY 关键字修改引入子查询的比较运算符,SOME等价于ANY。
比如>ALL() 表示大于每一个值,即为大于最大值,>ANY 表示至少大于一个值,即大于最小值。eg:
--内部查询查找最高定价。外部查询查看所有这些值,并确定定价高于或等于任何产品子类别的最高定价的单个产品
select [Name] from Product where [ListPrice] >= ANY
(select max([ListPrice]) from Product group by [ProductSubcategoryID]);
子查询
子查询就是在查询语句中嵌套另一个查询,子查询可以出现在from后(子查询当成数据表)或where后(子查询作为过滤条件的值),eg:
select * from (select * from student_table) as t where t.teacher > 1;
select * from student_table where teacher_id >(select teacher_id from teacher_table where name = 'leon');
select * from student_table where teacher_id in(select teacher_id from teacher_table);
select * from student_table where teacher_id >any(select teacher_id from teacher_table);
字符串匹配:like、%、_
select [book_name] from titles where [book_name] like '%love%'; --列出书名中包含“love"的图书
select [book_name] from titles where not [book_name] like '%love%'; --列出书名中不包含"love"的图书
select [book_name] from titles where [book_name] like '_s%'; --列出书名中第二个字符为's'的图书
select * from books where name like '\_%'; --对于MySQL,可以省略escape
数值、日期等的范围选择:between...and
select * from emp where sal between 1500 and 3000;--输出工资在1500——3000(包含1500和3000)的记录的所有列
select [book_name] from titles where [pubdate] between '1999-01-01' and '2000-01-01'; --选择在99年出版的图书
select [book_name] from titles where not [pubdate] between '1999-01-01' and '2000-01-01'; --选择所有图书,但不包含99年出版的
规定一个范围:in(...), not in(...)
select [book_name] from titles where [type] in('history', 'computer', 'math');--选择类型为历史或电脑或数学的图书
select [pub_name] from publishers where [pub_id] in(
select [pub_id] from titles where [type]='biography'
); --列出传记类型图书的出版社名(出版社名在publishers表中,图书类型在titles表中,两个表有共同的列pub_id)
exists()用来检查子查询是否至少会返回一行数据,该子查询实际上并不返回任何数据,而是返回True或False
NOT EXISTS 的作用与 EXISTS 正好相反:如果子查询没有返回行,则满足了 NOT EXISTS 中的 WHERE 子句。
--列出Table1表中name1 = Table2表中name2中的所有记录
select * from Table1 where exists(select 1 from Table2 where name2 = Table1.name1)
--列出Table1表中name1 = Table2表中name2中的所有记录的name1字段
select [name1] from Table1 where exists(select 1 from Table2 where name2 = Table1.name1)
select [pub_name] from publishers where exists
(select * from titles where [pub_id]=publishers.[pub_id] and [type]='biography');
--列出传记类型图书的出版社名(出版社名在publishers表中,图书类型在titles表中,两个表有共同的列pub_id)
以上查询语句与下面等效:
select [pub_name] from publishers where [pub_id] in
(select [pub_id] from titles where [type] = 'biography');
检查空值:is null, is not null
空值不能用=、<>、>、<来判断,只能用is null来判断,如
select [book_name] from titles where [price] <> 55; --列出价格不为55的图书,价格为NULL的图书不会被列出
select [book_name] from titles where [price] <> 55 or [price] is null;--列出价格不为55的书,价格为NULL的图书也会列出
指定选择前多少行:top ... *
指定选择前多少百分比: top ... percent *
注释:并非所有的数据库系统都支持 TOP 子句
select top 5 * from authors; --列出表的前五行数据 select top 5 [name] from authors; --列出前五个作者名
select top 5 [name] from emp where [sal] between 1500 and 3000 order by [sal] desc; <span style="font-family: Arial, Helvetica, sans-serif;">--列出工资在1500——3000之间最高的三个人</span><span style="font-family: Arial, Helvetica, sans-serif;"> </span>
select top 50 percent * from authors; --列出表的前%50的数据
指定选择多少行:limit ... *
注释:并非所有的数据库系统都支持 LIMIT 子句
select* from test LIMIT 1,3; //从全表的第二条数据开始取三条内容,即取表中第二到第四条数据
select* from test LIMIT 3; //取全表的前四条,相当于select* from test LIMIT 0,3
select * from test LIMIT 3 OFFSET 1; //跳过表的第一条数据,取结果的第二到第四条,即相当于 select* from test LIMIT 1,3
对数据按指定列进行升序/降序:order by...asc/desc
默认为升序
select [book_name], [price] from titles order by [price]; --按price升序排序
select [book_name], [price], [pages] from titles order by [price], [pages] desc;--按price升序排序,若price相等,再按pages降序排序
select [book_name], [price] from titles order by 2;--按price升序排序
排序时空值会被作为最小值。
创建列或表的别名:as
select [name], [salary]*12 as ''salary_year'' from employees;--列出每个雇员的年薪
select [book_id] as ''ID'', [book_name] from titles where ID > 100; --列出ID大于100的图书
select t1.[book_name], t2.[author] from titles as t1, authors as t2
where t1.[book_id] = t2.[book_id]; --列出图书的相应作者(图书名在titles表中,作者名在authors表中)
其中as可省略,后面的别名也可以用单引号包围,但为了能兼容其它数据库最好用双引号。
使用指定数据库的数据:as
create table new_table(ID int auto_increment primary key) as select name, price from table_test; -- 创建新表new_table,其数据为ID列和tab_test表中ID和name列
选择的数据来自两个或多个表:join
cross join:交叉连接。
natural join:自然连接。
join using:子句连接
join on:子句连接,最常用的的连接方式。
inner join...on:内联结,返回两个表匹配的行,也可以不加inner,例如以下两个表
"Persons" 表:
Id_P | LastName | FirstName | Address | City |
---|---|---|---|---|
1 | Adams | John | Oxford Street | London |
2 | Bush | George | Fifth Avenue | New York |
3 | Carter | Thomas | Changan Street | Beijing |
"Orders" 表:
Id_O | OrderNo | Id_P |
---|---|---|
1 | 77895 | 3 |
2 | 44678 | 3 |
3 | 22456 | 1 |
4 | 24562 | 1 |
5 | 34764 | 65 |
我们想要查询Persons表中人员的所有订单,可以执行以下语句:
select Persons.LastName, Persons.FirstName, Orders.OrderNo from Persons, Orders
where Persons.Id_P = Orders.Id_P;
也可以使用inner join来完成:
select P.LastName, P.FirstName, OrderNo from Persons as P join Orders as O
on P.Id_P = O.Id_P order by P.LastName;
on P.Id_P = O.Id_P order by P.LastName;
查询结果:
LastName | FirstName | OrderNo |
---|---|---|
Adams | John | 22456 |
Adams | John | 24562 |
Carter | Thomas | 77895 |
Carter | Thomas | 44678 |
full join...on:全联结,先返回两个表匹配的行,再返回两个表不匹配的行,即会返回两个表的所有数据
left join...on:左联结,先返回两个表匹配的行,再返回左表不匹配的行,即会返回左表的所有数据
right join...on:右联结,先返回两个表匹配的行,再返回右表不匹配的行,即会返回右表的所有数据
集合运算:并集union、交集intersect、差集minus
对两个结果集进行集合运算,这两个结果集所包含的数据列的数量和数据类型应该相同。
select语句 union/intersect/minus select语句;
select [book_name], [type] from titles where [type]='history' union
select [book_name], [type] from titles where [type]='computer'; --列出类型为历史或计算机的图书(先列出所有历史类图书再列出所有计算机图书)
多条件判断:case [字段名] when 值 then 新值
select [book_name], (case [type]
when 'history' then [price]*1.1
when 'psychology' then [price]*1.2
else [price]
end) as 'new price' from titles;
--列出图书的新价格:将历史类的图书价格提高%10,心理类的图书价格提高%20,其余价格不变
select [book_name], (case
when [sales] <= 100 then '<=100'
when [sales] <= 1000 then '<=1000'
when [sales] <= 10000 then '<=10000'
when [sales] is null then 'unknown'
else '>10000'
end) as 'sales' from titles;
--列出图书的销量范围
T-SQL优化基本常识
1. 在Where条件中尽量不要在=号左边进行函数、运算符、或表达式计算,如Where DATEDIFF(DD,StartTime,GetDate())=6 ;或Where Num/2=100;
2. 在Where中尽量避免出现!=或<>操作符;
3. 在Where中尽量避免对字段进行null值判定;
4. 使用Like关键字进行模糊查找时,不要使用前置百分号,如Like ‘%123%’;
5. 数据库字段的长度尽量的小(保证应用的前提下);
6. 不要使用Selecte*,不要使用*号来查询数据;
7. 尽量避免使用游标,游标的效率是很差的,可以使用While循环来代替;
8. 尽量避免返回大量数据(查询数据(Select)优化,分页处理等);
9. 使用Exists代替in和not in;
部分参考出处:http://blog.csdn.net/willielee/article/details/5815721
http://www.cnblogs.com/anding/p/3254674.html