数据库各种查询语句
数据库各种查询语句
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;
