Êý¾Ý¿âSQLÓï¾äʹÓã¨Ò»£©
Êý¾Ý¿âsqlÓï¾äϵÁж¼ÊÇÒÔÏÂÃæµÄ±íΪ»ù´¡µÄ
1. д³ö²½Öè4Öеĸ÷Ïî²Ù×÷µÄSQLÓï¾ä¡£
¢Ù ¸øѧÉú±íÔö¼ÓÒ»ÊôÐÔNation£¨Ãñ×壩£¬Êý¾ÝÀàÐÍΪVarchar£¨20£©£»
alter table Student add Nation varchar(20);
¢Ú ɾ³ýѧÉú±íÖÐÐÂÔöµÄÊôÐÔNation£»
alter table Student DROP COLUMN Nation ;
¢Û Ïò³É¼¨±íÖвåÈë¼Ç¼£¨”2001110”£¬”3”£¬80£©£»
insert into Grade (Sno,Cno,Gmark)values ('2001110','3',80);
¢Ü ÐÞ¸ÄѧºÅΪ”2001110”µÄѧÉúµÄ³É¼¨Îª70·Ö£»
update Grade set Gmark=70 where Sno='2001110';
¢Ý ɾ³ýѧºÅΪ”2001110”µÄѧÉúµÄ³É¼¨¼Ç¼£»
delete from Grade where Sno='2001110';
¢Þ ΪѧÉú±í´´½¨Ò»¸öÃûΪIX_ClassµÄË÷Òý¡£
create index IX_Class on Student(Sno);
SelectÓï¾äʹÓã¨Ò»£©
Íê³ÉÒÔϸ÷Ïî²Ù×÷µÄSQLÓï¾ä£º
¢Ù ÕÒ³öËùÓб»Ñ§ÉúÑ¡ÐÞÁ˵Ŀγ̺ţ»
select distinct Cno from Grade;
¢Ú ÕÒ³ö01311°àŮѧÉúµÄ¸öÈËÐÅÏ¢£»
select * from Student where Clno='01311' and Ssex='Å®';
¢Û ÕÒ³ö01311°à¡¢01312°àµÄѧÉúÐÕÃû¡¢ÐԱ𡢳öÉúÈÕÆÚ£»
select Sname, Ssex,2009-Sage Birthday from Student where Clno='01311' or Clno='01312';
¢Ü ÕÒ³öËùÓÐÐÕÀîµÄѧÉúµÄ¸öÈËÐÅÏ¢£»
select * from Student where Sname like 'Àî%';
¢Ý ÕÒ³öѧÉúÀîÓÂËùÔڰ༶µÄѧÉúÈËÊý£»
select Number from Class where Clno=(select Clno from Student where Sname='ÀîÓÂ');
¢Þ ÕÒ³ö¿Î³ÌÃûΪ²Ù×÷ϵͳµÄƽ¾ù³É¼¨¡¢×î¸ß·Ö¡¢×îµÍ·Ö£»
select avg(Gmark)/ max(Gmark)/ min(Gmark) from Grade where Cno=(select Cno from Course where Cname='²Ù×÷ϵͳ' );
¢ß ÕÒ³öÑ¡ÐÞÁ˿γ̵ÄѧÉúÈËÊý£»
select count(distinct Sno) from Grade ;
¢à ÕÒ³öÑ¡ÐÞÁ˿γ̲Ù×÷ϵͳµÄѧÉúÈËÊý¡£
select count(distinct Sno) from Grade where Cno=(select Cno from Course where Cname='²Ù×÷ϵͳ' );
select Óï¾äʹÓ㨶þ£©
1¡¢Íê³ÉÒÔϸ÷Ïî²Ù×÷µÄSQLÓï¾ä£º
¢Ù ÕÒ
Ïà¹ØÎĵµ£º
SQL Server ·þÎñÓÉÓڵǼʧ°Ü¶øÎÞ·¨Æô¶¯
1.Ö¢×´
ÔÚÖØÐÂÆô¶¯ SQL Server¡¢SQL Executive »ò SQL Server Agent ʱ£¬¿ÉÄÜÎÞ·¨Æô¶¯¸Ã·þÎñ£¬²¢ÏÔʾÒÔÏ´íÎóÐÅÏ¢£º
Error 1069:The service did not start due to a logon failure.
»òÕß
´íÎó 1069£ºÓÉÓڵǼʧ°Ü¶øÎÞ·¨Æô¶¯·þÎñ
2.ÔÒò
SQL Server¡¢SQL Agent »ò SQL Serve ......
create PROCEDURE pagelist
@tablename nvarchar(50),
@fieldname nvarchar(50)='*',
@pagesize int output,--ÿҳÏÔʾ¼Ç¼ÌõÊý
@currentpage int output,--µÚ¼¸Ò³
@orderid nvarchar(50),--Ö÷¼üÅÅÐò
@sort int,--ÅÅÐò·½Ê½£¬1±íʾÉýÐò£¬0±íʾ½µÐòÅÅÁÐ
......
ÔÚPostgreSQL8.1.3ÉÏ¿ÉÒÔwork
1. È¡Êý¾Ý¿â´óС
SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize
2. È¡±í´óС
SELECT pg_size_pretty(pg_total_relation_size('someschema.sometable')) As fulltblsize, pg_size_pretty(pg_relation_size('someschema.sometable')) As justthetblsize
ÔÌù£º
......
SQL SERVER ºÍEXCELµÄÊý¾Ýµ¼Èëµ¼³ö
1¡¢ÔÚSQL SERVERÀï²éѯExcelÊý¾Ý:
-- ======================================================
SELECT *
from OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
ÏÂÃæÊǸö²éÑ ......
µÚ¶þ²¿·Ö
1.ËùÓÐÄÐÉúµÄÐÕÃû¡¢ÄêÁä:
Select Sname£¬Sage
from student;
2.ËùÓÐÄêÁä´óÓÚ20£¬¼ÆËã»ú¿ÆѧϵѧÉúÃûµ¥:
SELECT Sname
from Student
WHERE Sage>20 AND Sdept='CS';
3.³É¼¨´óÓÚ60µÄѧÉúѧºÅ:
SELECT Sno
from SC
WHERE Grade>60;
4.³É¼¨ÔÚ70µ½80Ö®¼äµÄѧÉúѧºÅ:
SELECT Sno
from SC
......