sql·Ö×é²éѯÎÊÌâ
Çé¾°Ò»£º
±íÖÐÊý¾Ý
name score
aaa 11
aaa 19
bbb 12
bbb 18
ccc 19
ddd 21
ÆÚÍû²éѯ½á¹ûÈçÏÂ
name score
aaa 30
bbb 30
ccc 19
ddd 21
Sql´úÂë
---¼ì²é±íÊÇ·ñ´æÔÚ
if exists(select * from sysobjects where name='testSum')
drop table testSum
go
---´´½¨±í
create table testSum
(
tid int primary key identity(1,1),
tname varchar(30) null,
tscor int null
)
go
insert into testSum (tname,tscor)
select 'aaa',11
union all
select 'aaa',19
union all
select 'bbb',12
union all
select 'bbb',18
union all
select 'ccc',19
union all
select 'ddd',21
---²éѯÓï¾ä
select tname ,sum(tscor) from testSum group by tname
---Ö»²éѯtscor×ܺÍΪ30µÄ
select tname ,sum(tscor) from testSum group by tname having sum(tscor)=30
---¼ì²é±íÊÇ·ñ´æÔÚ
if exists(select * from sysobjects where name='testSum')
drop table testSum
go
---´´½¨±í
create table testSum
(
tid int primary key identity(1,1),
tname varchar(30) null,
tscor int null
)
go
insert into testSum (tname,tscor)
select 'aaa',11
union all
select 'aaa',19
union
Ïà¹ØÎĵµ£º
--SQL ËÙ²éÊÖ²á
/*******************************************/
SELECT
--ÓÃ;£º´ÓÖ¸¶¨±íÖÐÈ¡³öÖ¸¶¨ÁеÄÊý¾Ý
--Óï·¨£º
SELECT column_name(s) from table_name
--Ö÷Òª×Ö¾ä¿ÉժҪΪ:
SELECT select_list [INTO new_table]
from table_source
[WHERE search_condition]
[GROUP BY group_by_expression]
[HAVING s ......
1.ÏÞÖÆ·µ»Ø¼Ç¼µÄÐÐÊý£¨4-10ÐУ©
select rownum,c1 from t144 where rownum<10
minus
select rownum,c1 from t144 where rownum<5
2.ÅúÁ¿²åÈë
insert into tablename(select * from othertable)
»ò
insert into (column1,column2,,,,,,,)tablename(select column1,column2,,,,,,,from othertable)
3,·Ö×éº¯Ê ......
ת×ÔCSDNÂÛ̳£ºchaihuoniu
ÏÂÁÐÓï¾ä²¿·ÖÊÇMssqlÓï¾ä£¬²»¿ÉÒÔÔÚaccessÖÐʹÓá£
SQL·ÖÀࣺ
DDL—Êý¾Ý¶¨ÒåÓïÑÔ(CREATE£¬ALTER£¬DROP£¬DECLARE)
DML—Êý¾Ý²Ù×ÝÓïÑÔ(SELECT£¬DELETE£¬UPDATE£¬INSERT)
DCL—Êý¾Ý¿ØÖÆÓïÑÔ(GRANT£¬REVOKE£¬COMMIT£¬ROLLBACK)
Ê×ÏÈ,¼òÒª½éÉÜ»ù´¡Óï¾ä£º
1¡¢ËµÃ÷£º´´½¨Êý¾ ......
insert into mysql.user
SELECT '%', 'admin', PASSWORD('admin'), Select_priv, Insert_priv, Update_priv,
Delete_priv, Create_priv, Drop_priv, Reload_priv,
Shutdown_priv, Process_priv, File_priv, Grant_priv, References_priv,
Index_priv, Alter_priv, Show_db_priv, Super_priv, ......
SQL> select * from dept where deptno=&tt;
Enter value for tt:20
DEPTNO DNAME LOC
------ -------------- -------------
10 ACCOUNTING NEW YORK
ÕâÑùÎÞÂÛÊäÈë¶àÉÙ´ÎttµÄÖµ£¬ÏµÍ³Ö»½¨Á¢Ò»¸öÖ´Ðмƻ®£¬Ì ......