当前位置:编程问答 > Unix/Linux >

数据库各种查询语句

数据库各种查询语句
 
1、字符串连接查询
 
Oracle数据库
 
SELECT USERID,USERNAME||’:’||USEREMAIL NAMEANDMAIL FROM STUDENT WHERE USERNAME IS NOT NULL AND USERMAIL IS NOT NULL;
 
用CONCAT函数的话,需要注意这个函数只带两个参数,如果有多个字符串连接的时候就需要多次CONCAT
 
SELECT CONCAT(‘工号:’,FNUMBER) FORM T_EMPLOYEE;
 
MS SQL server数据库
 
SELECT AU_ID,AU_FNAME+’ ’+AU_LNAME  ‘NAME’ FROM AUTHORS WHERE STATE=’CA’;
 
2、去掉字段中重复的查询
 
SELECT DISTINCT PID FROM USER;
 
3、查询以ean结尾的4个字母查询
 
SELECT * FROM AUTHORS WHERE AU_FNAME LIKE '_ean';
 
4、查询包含conputer单词的所有书名
 
SELECT * FROM BOOKS WHERE BOOKTITLE LIKE '%computer%';
 
5、查询以de开始且其后字母不为l的所有作者姓氏
 
SELECT * FROM AUTHORS WHERE AU_LNAME LIKE 'de[^l]%';
 
6、多列排序查询
 
SELECT PUB_ID,TYPE,TITLE_ID,PRICE FROM TITLES ORDER BY PUB_ID DESC,TYPE,PRICE;
 
7、查询平均价格和销售总额
 
SELECT TYPE,PUB_ID,AVG(PRICE) AS 'AVG', SUM(YTD_SALES)  'SUM' FROM TITLES GROUP BY TYPE,PUB_ID ORDER BY TYPE,PUB_ID;
 
8、关于WHERE、GROUP BY、HAVING的查询
 
SELECT TYPE FROM TITLES GROUP BY TYPE HAVING COUNT(*)>1 ORDER BY TYPE;
 
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,