数据库查询

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;