2.sql语句

1、查询语句---------------------------------------------------------------------------------------------------------------------------/*(添加别名)*/select orderdate as "订单日期",order# as "订单编号" from orders; SELECT title as "书名", retail-cost*1.1 as "利润" FROM books; /*（消除查询结果相同的记录）*/ SELECT unique category,title FROM books; select distinct category,title from books; ------------------------------------------------------------------/*（连接两个字符串，中间可以加下划线或空格） */ SELECT city||'_'||state as “别名” FROM customers; /*||chr(10)||在SQLPLUS中执行换行，换行前一个字段不能设立别名*/ Select firstname||'_'||lastname ||chr(10)|| address ||chr(10)|| ||' '|| state ||' '|| zip from customers; /*（日期的两种处理方法，预设他的日期格式） */ SELECT ORDER# , ORDERDATE FROM ORDERS WHERE ORDERDATE > TO_DATE('2008-01-01','YYYY-MM-DD'); select * from books where pubdate=to_date('2006-01-21','yyyy-mm-dd'); select title,to_char(pubdate,'yyyy-mm-dd') from books where pubdate=to_date('2006-01-21','yyyy-mm-dd'); -------------------------------------------------------------------/*（不等于的两种写法）*/ select title from books where title <>'HB'; select title from books where title !='HB'; ------------------------------------------------------------------select title,RETAIL from books where RETAIL BETWEEN 22 AND 80; (not between … and) ------------------------------------------------------------------/*（in包括在几种范围内）*/ SELECT * FROM CUSTOMERS WHERE STATE IN ('GA','FL'); SELECT * FROM CUSTOMERS WHERE STATE NOT IN ('GA','FL'); ------------------------------------------------------------------/*（like的使用方法，%代表多个字符，_代表一个字符 not like）*/ SELECT * FROM CUSTOMERS WHERE CUSTOMER# LIKE '100%'; SELECT * FROM CUSTOMERS WHERE CUSTOMER# LIKE '10_4'; SELECT FIRSTNAME FROM CUSTOMERS WHERE FIRSTNAME LIKE '_E%A'; select * from customers where state='FL' or firstname like 'A%'; select * from customers where state='FL' and firstname like 'B%'; select * from customers where state<>'FL' and firstname like 'B%'; ------------------------------------------------------------------/*（not,and,or的使用规则，优先级not,and,or）*/1/6cityselect * from books where (category='COMPUTER' OR CATEGORY='FAMILY LIFE') and (PUBID='1' OR PUBID='3') and (NOT RETAIL<45); -------------------------------------------------------------------/*（null的使用）*/ SELECT *from orders where shipdate is null; SELECT *from orders where shipdate is not null; select * from orders order by shipdate nulls first; /*（按升序排列时，将空值放在前面）*/------------------------------------------------------------------/*（用别名可以用来排序，但是不能用来选择，并且要加双引号）*/ select title,(retail-cost) as "profit" from (retail-cost)>10 order by "profit" ; /*（两个字符来进行排序问题）*/ select state,city from customers order by state,city desc; --------------------------------------------------------------------books whereSELECT DISTINCT 列名称 FROM 表名称在表中，可能会包含重复值。

这并不成问题，不过，有时您也许希望仅仅列出不同（distinct）的值。

关键词 DISTINCT 用于返回唯一不同的值。

SELECT * FROM Persons WHERE(FirstName='Thomas' ORFirstName='William')ANDLastName='Carter'ORDER BY 语句ORDER BY 语句用于根据指定的列对结果集进行排序。

ORDER BY 语句默认按照升序对记录进行排序。

如果您希望按照降序对记录进行排序，可以使用 DESC 关键字。

(ASC正序)以字母顺序显示公司名称（Company），并以数字顺序显示顺序号（OrderNumber）：SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumberSELECT TOP number|percent column_name(s) FROM table_name不同的数据库系统不一样 sqlserver,access select top 10 * from tablename order by num desc;2/6mysql select * from tablename order by num desc limit 10; oracleselect * from books where rownum < = 10 order by retail desc ;SELECT TOP 50 PERCENT * FROM Persons（上面的 "Persons" 表中选取 50% 的记录）(从上面的 "Persons" 表中选取居住的城市以 "A" 或 "L" 或 "N" 开头的人) SELECT * FROM PersonsWHERE City LIKE '[ALN]%'(从上面的 "Persons" 表中选取居住的城市不以 "A" 或 "L" 或 "N" 开头的人) SELECT * FROM Persons WHERE City LIKE '[!ALN]%' (我们希望从上表中选取姓氏为 Adams 和 Carter 的人) SELECT * FROM PersonsWHERELastName IN ('Adams','Carter')(以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人,数据库不同对端点值的处理不 同) SELECT * FROM Persons WHERE LastName SELECT * FROM Persons WHERE LastNameBETWEEN'Adams'AND'Carter'NOTBETWEEN 'Adams' AND 'Carter'插入语句：INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing') INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees') 更新语句： UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'删除语句： DELETE FROM Person WHERE LastName = 'Wilson'2、多表连接/*（传统的连接方法）*/3/6selecttitle,namefrombooks,publisherwherebooks.pubid=publisher.pubid; /*（join … using 连接两个字段完全相同的表，也可以用 natural join）*/ select firstname ,order# from orders join customers using(customer#) ; /*natural join连接两个表*/ select firstname,order#,orderdate from orders natural join customers order by firstname; /*join on连接两个表*/ select firstname,order#,orderdate from orders join customers on orders.customer#=customers.customer# order by firstname; ------------------------------------------------------------------/*（多表连接中可以用表的别名）*/ select firstname,title from books b join orderitems oi on b.isbn=oi.isbn join orders o on oi.order#=o.order# join customers c on o.customer#=c.customer# order by firstname; -------------------------------------------------------------------/*(多表连接，注意连接的顺序)*/ select fname||'_'||lname,name,title from books join publisher on books.pubid=publisher.pubid join bookauthor on books.isbn=bookauthor.isbn join author on bookauthor.authorid=author.authorid; --------------------------------------------------------------------/*(between and 不等连接,查询在两个范围之间的记录）*/ select title,gift from books join promotion on books.retail between minretail and maxretail; --------------------------------------------------------------------/*（自我表连接查询，要对表进行编号 c为原来的客户表r为引申出来的推荐人表）*/ select c.firstname||'_'||stname as "客户名" , r.firstname||'_'||stname as "介绍人" from customers c join customers r on r.customer#=c.referred; -------------------------------------------------------------------/*(左/右连接。