Ò׽ؽØÍ¼Èí¼þ¡¢µ¥Îļþ¡¢Ãâ°²×°¡¢´¿ÂÌÉ«¡¢½ö160KB

SQLʵÀý

--1¡¢²éÕÒÔ±¹¤µÄ±àºÅ¡¢ÐÕÃû¡¢²¿ÃźͳöÉúÈÕÆÚ£¬Èç¹û³öÉúÈÕÆÚΪ¿ÕÖµ£¬ÏÔʾÈÕÆÚ²»Ïê,²¢°´²¿ÃÅÅÅÐòÊä³ö,ÈÕÆÚ¸ñʽΪyyyy-mm-dd¡£
select emp_no,emp_name,dept,isnull(convert(char(10),birthday,120),'ÈÕÆÚ²»Ïê') birthday
from employee
order by dept
--2¡¢²éÕÒÓëÓ÷×ÔÇ¿ÔÚͬһ¸öµ¥Î»µÄÔ±¹¤ÐÕÃû¡¢ÐԱ𡢲¿ÃźÍÖ°³Æ
select emp_no,emp_name,dept,title
from employee
where emp_name'Ó÷×ÔÇ¿' and dept in
(select dept from employee
where emp_name='Ó÷×ÔÇ¿')
--3¡¢°´²¿ÃŽøÐлã×Ü£¬Í³¼ÆÃ¿¸ö²¿ÃŵÄ×ܹ¤×Ê
select dept,sum(salary)
from employee
group by dept
--4¡¢²éÕÒÉÌÆ·Ãû³ÆÎª14´çÏÔʾÆ÷ÉÌÆ·µÄÏúÊÛÇé¿ö£¬ÏÔʾ¸ÃÉÌÆ·µÄ±àºÅ¡¢ÏúÊÛÊýÁ¿¡¢µ¥¼ÛºÍ½ð¶î
select a.prod_id,qty,unit_price,unit_price*qty totprice
from sale_item a,product b
where a.prod_id=b.prod_id and prod_name='14´çÏÔʾÆ÷'
--5¡¢ÔÚÏúÊÛÃ÷ϸ±íÖа´²úÆ·±àºÅ½øÐлã×Ü£¬Í³¼ÆÃ¿ÖÖ²úÆ·µÄÏúÊÛÊýÁ¿ºÍ½ð¶î
select prod_id,sum(qty) totqty,sum(qty*unit_price) totprice
from sale_item
group by prod_id
--6¡¢Ê¹ÓÃconvertº¯Êý°´¿Í»§±àºÅͳ¼ÆÃ¿¸ö¿Í»§1996ÄêµÄ¶©µ¥×ܽð¶î
select cust_id,sum(tot_amt) totprice
from sales
where convert(char(4),order_date,120)='1996'
group by cust_id
--7¡¢²éÕÒÓÐÏúÊۼǼµÄ¿Í»§±àºÅ¡¢Ãû³ÆºÍ¶©µ¥×ܶî
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id
group by a.cust_id,cust_name
--8¡¢²éÕÒÔÚ1997ÄêÖÐÓÐÏúÊۼǼµÄ¿Í»§±àºÅ¡¢Ãû³ÆºÍ¶©µ¥×ܶî
select a.cust_id,cust_name,sum(tot_amt) totprice
from customer a,sales b
where a.cust_id=b.cust_id and convert(char(4),order_date,120)='1997'
group by a.cust_id,cust_name
--9¡¢²éÕÒÒ»´ÎÏúÊÛ×î´óµÄÏúÊۼǼ
select order_no,cust_id,sale_id,tot_amt
from sales
where tot_amt=
(select max(tot_amt)
from sales)
--10¡¢²éÕÒÖÁÉÙÓÐ3´ÎÏúÊÛµÄÒµÎñÔ±Ãûµ¥ºÍÏúÊÛÈÕÆÚ
select emp_name,order_date
from employee a,sales b
where emp_no=sale_id and a.emp_no in
(select sale_id
from sales
group by sale_id
having count(*)>=3)
order by emp_name
--11¡¢ÓôæÔÚÁ¿´Ê²éÕÒûÓж©»õ¼Ç¼µÄ¿Í»§Ãû³Æ
select cust_name
from customer a
where not exists
(select *
from sales b
where a.


Ïà¹ØÎĵµ£º

Linq to sqlÔöɾ¸Ä²é

private void button2_Click(object sender, EventArgs e)//Ôö
{
try
{
Customers ct = new Customers();
ct.CustomerID = "test";
ct.CompanyName = "test";
NorthwindDataContext nc = new NorthwindDataContex ......

MySqlµ¼³ösql½Å±¾


mysqlµ¼³ösql½Å±¾ Êղؠ
   ×¢ÒâÊÂÏî:
*±ØÐëÔÚD:\LTSOFT\MYSQL4\bin> mysqldump -u root -p test > c:\a.sql
ÏȵýøÈëmysql°²×°Ä¿Â¼µÄbinÏ£¨¿ÉÒÔ¿´µ½ÀïÃæÓÐmysqldump.exe£©£¬Í¨³£mysqlĬÈϰ²×°ÔÚC:\Program    Files\MySQL\MySQL Server 5.0
* ²»ÄܽøÈëmysql ÃüÁîÀï.
MySQLµ¼³ ......

PL/SQL ѧϰ±Ê¼Ç3

 SQL> var a number
SQL> begin
2 :a :=1000;
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> edit
Wrote file afiedt.buf
1 begin
2 dbms_output.put_line(:a);
3* end;
SQL> /
ͨ¹ýÕâ¸ö´úÂë¿ÉÒÔ¿´³öͨ¹ýpl/sql³õʼ»¯¸³ÖµµÄBind variable¿ÉÒÔ±»ÆäËûPl/sql³ÌÐòµ ......

ÓÃSQLÉú³ÉÁ÷Ë®ºÅ

ÓÃSQLÉú³ÉÁ÷Ë®ºÅ
ת£ºÎ¤½­ÌΠ   ·¢±íÓÚ2010Äê02ÔÂ03ÈÕ 09:38 ÔĶÁ(4) ÆÀÂÛ(0)
 
 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_FillNumberWithZero]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_FillNumberWithZero]
GO
if exists ( ......

SQL»ù´¡

SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢´´½¨Êý¾Ý¿â
CREATE DATABASE database-name
2¡¢É¾³ýÊý¾Ý¿â
drop database dbname
3¡¢ ......
© 2009 ej38.com All Rights Reserved. ¹ØÓÚE½¡ÍøÁªÏµÎÒÃÇ | Õ¾µãµØÍ¼ | ¸ÓICP±¸09004571ºÅ