£¨×ª£©SQL¾µäÃæÊÔÌ⼯£¨¶þ£©
µÚʮһÌ⣺
Óбístudents(name,class,grade),ÇëÓñê×¼sqlÓï¾äÍê³É
name class grade
ÕÅÈý Êýѧ 81
ÀîËÄ ÓïÎÄ 70
ÍõÎå Êýѧ 90
ÕÅÈý ÓïÎÄ 60
ÀîËÄ Êýѧ 100
ÍõÎå ÓïÎÄ 90
ÍõÎå Ó¢Óï 81
ÒªÇó: ÓÃsqlÓï¾äÊä³ö¸÷ÃŹ¦¿Î¶¼´óÓÚ80·ÖµÄͬѧÐÕÃû?
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('ÕÅÈý','ÓïÎÄ',20)
insert into students values ('ÕÅÈý','Êýѧ',90)
insert into students values ('ÕÅÈý','Ó¢Óï',50)
insert into students values ('ÀîËÄ','ÓïÎÄ',81)
insert into students values ('ÀîËÄ','Êýѧ',60)
insert into students values ('ÀîËÄ','Ó¢Óï',90)
insert into students values ('Íõ¶þ','Êýѧ',81)
insert into students values ('Íõ¶þ','Ó¢Óï',90)
insert into students values ('ÀîÎå','Êýѧ',83)
insert into students values ('ÀîÎå','Ó¢Óï',90)
insert into students values ('ÀîÎå','»¯Ñ§',90)
---Ñ¡³öËùÓгɼ¨´óÓÚ80·ÖµÄѧÉúÐÕÃû-----
------½â·¨Ò»------
select name from students group by name having min(grade)>80
------½â·¨¶þ------
select distinct Name from students where grade >80 and Name not in (select Name from students where grade <80)
------½â·¨Èý------
select distinct name from students where name not in (select name from students where grade <=80 group by name )
-----½â·¨ËÄ-------
select name from students group by name having name not in (select name from students where grade<=80)
µÚÊ®¶þÌ⣺
ÒÑÖªÒ»¸ö±íµÄ½á¹¹Îª£º
ÐÕÃû ¿ÆÄ¿ ³É¼¨
ÕÅÈý ÓïÎÄ 20
ÕÅÈý Êýѧ 30
ÕÅÈý Ó¢Óï 50
ÀîËÄ ÓïÎÄ 70
ÀîËÄ Êýѧ 60
ÀîËÄ Ó¢Óï 90
ÔõÑùͨ¹ýselectÓï¾ä°ÑËû±ä³ÉÒÔϽṹ£º
ÐÕÃû ÓïÎÄ Êýѧ Ó¢Óï
ÕÅÈý 20 30 50
ÀîËÄ 70 60 90
create table students (
name varchar(25),
class varchar(25),
grade int
)
insert into students values ('ÕÅÈý','ÓïÎÄ',20)
insert into students values ('ÕÅÈý','Êýѧ',90)
insert into students values ('ÕÅÈý','Ó¢Óï',50)
insert into students values ('ÀîËÄ','ÓïÎÄ',81)
insert into students values ('ÀîËÄ','Êýѧ',60)
insert into students values ('ÀîËÄ','Ó¢Óï',90)
--½â´ð£º
select A.Name,A.grade as ÓïÎÄ,B.grad
Ïà¹ØÎĵµ£º
Ò»¡¢±íµÄµ¼Èëµ¼³öÓï¾ä¼°Ê±¼ä×Ö·û´®²¿·Ö´¦Àíº¯Êý
µ¼³öÊý¾Ý¿âËùÓбíµÄ½á¹¹ mysqldump -uroot -proot db_name -d > d:/export_db.sql(½áβ²»Ó÷ֺÅ)
µ¼³öÊý¾Ý¿âij¸ö±íµÄ½á¹¹ mysqldump -uroot -proot db_n ......
select *
from (select row_number() over(partition by t.type order by date desc) rn,
t.*
from ±íÃû t)
where rn <= 2;
typeÒª·ÖµÄÀà
date ÅÅÐò ......
SQL Server 2005 ÈÕ־ɾ³ýºÍÈÕÖ¾ÎļþÏÞÖÆ(ת)
2009Äê08ÔÂ04ÈÕ ÐÇÆÚ¶þ ÏÂÎç 04:40
Çå³ýÈÕÖ¾£º
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE szwzcheck ......
SQLÓï¾äÓï·¨
Ŀ¼
13.1. Êý¾Ý¶¨ÒåÓï¾ä
13.1.1. ALTER DATABASEÓï·¨
13.1.2. ALTER TABLEÓï·¨
13.1.3. CREATE DATABASEÓï·¨
13.1.4. CREATE INDEXÓï·¨
13.1.5. CREATE TABLEÓï·¨
13.1.6. DROP DATABASEÓï·¨
13.1.7. DROP INDEXÓï·¨
13.1.8. DROP TABLEÓï·¨
13.1.9. RENAME TABLEÓï·¨
13.2. Êý¾Ý²Ù×÷Óï¾ ......
ÒòΪҪ¸ù¾ÝºÜ¸´ÔӵĹæÔò´¦ÀíÓû§Êý¾Ý£¬ËùÒÔÕâÀïÓõ½Êý¾Ý¿âµÄÓαꡣƽʱ²»ÔõôÓÃÕâ¸ö£¬Ð´ÔÚÕâÀï´¿´âΪ×Ô¼º±¸¸öÍü¡£
--½«Ñ§¼®ºÅÖØ¸´µÄ·ÅÈëÁÙʱ±í tmp_zdsoft_unitive_code(³ý¸ßÖÐѧ¶ÎÍâ)
drop table tmp_zdsoft_unitive_code;
select s.id ,sch.school_code,sch.school_name,s.student_name,s.unitive_code,s.identity_car ......