📄 sql语法.c
字号:
X
Xmain (argc,argv)
Xint argc;
Xchar *argv[];
X{
Xint x = 0;
XFILE *fd;
Xchar s[1024];
XDDFRUN partree;
X
X fd = fopen ("pt_file","w");
X for (printf ("%d>>", x++); getqu (s) && parse (&partree, s) > 0;
x++)
X {
X fwrite (partree.par_buf, partree.buf_len, 1, fd);
X printf ("%d>>", x);
X }
X}
X
X/* old version; no longer used
Xgetq(s)
Xchar *s;
X{
X for(; (*s=getchar()) != EOF && *s != ';' ; s++);
X if(*s == EOF )
X return 0;
X else
X {
X *++s = '\0';
X return 1;
X }
X}
X*/
X
Xgetqu (s)
Xchar *s;
X{
Xstatic int getqtab [3] [5] =
X {
X {0,1,0,(int)'e',0},
X {1,1,2,1,1},
X {2,0,1,1,1}
X };
Xint tk;
Xint st = 0;
X
X *s = '\0';
X while ( (tk = getqt (s)) != EOF )
X {
X if (tk == 3 && !st)
X {
X return 1;
X }
X else
X st = getqtab[st] [tk];
X }
X return 0;
X}
X
Xgetqt (s)
Xchar *s;
X{
Xint c;
Xint value;
Xchar buf[256];
Xchar *sbuf=buf;
X
X if(isspace( (c=getchar()) ))
X value=0;
X else if( isalpha(c))
X {
X for(*buf=(islower(c)) ?toupper(c):c; isalpha(
(c=getchar()) )
&&
X c != EOF ; *++sbuf = (islower(c)) ?toupper(c):c);
X *++sbuf ='\0';
X if(!strcmp(buf,"END"))
X value=2;
X else if(!strcmp(buf,"STORE"))
X value=1;
X else
X value=4;
X ungetc(c,stdin);
X strcat(s,buf);
X return value;
X }
X else if(c==';')
X value=3;
X else if(c == EOF)
X value = EOF;
X else
X value= 4;
X *sbuf = c;
X *++sbuf ='\0';
X strcat(s,buf);
X return value;
X}
END_OF_tpar.c
if test 2018 -ne `wc -c <tpar.c`; then
echo shar: \"tpar.c\" unpacked with wrong size!
fi
# end of overwriting check
fi
if test -f vtest2 -a "${1}" != "-c" ; then
echo shar: Will not over-write existing file \"vtest2\"
else
echo shar: Extracting \"vtest2\" \(7485 characters\)
sed "s/^X//" >vtest2 <<'END_OF_vtest2'
X#0#
Xcreate domain s# is char5 where s# is not null;
X#1#
Xcreate domain p# is char6 where p# is not null;
X#2#
Xcreate domain j# is char4 where j# is not null;
X#3#
Xcreate domain name is char20;
X#4#
Xcreate domain status, weight, qty is int;
X#5#
Xcreate domain city is char15;
X#6#
Xcreate domain color is char6 where color in ( "green", "red", "blue");
X#7#
Xcreate table s
X primary (s#)
X ( s_name, status, city);
X
X#8#
Xcreate table p
X primary (p#)
X ( p_name, color, weight, city);
X
X#9#
Xcreate table j
X primary (j#)
X ( j_name, city);
X
X#10#
Xcreate table spj
X primary (s#, p#, j#)
X foreign (s# identifies s
X nulls not allowed
X delete of s restricted
X update of s.s# cascades)
X foreign (p# identifies p
X nulls not allowed
X delete of p restricted
X update of p.p# restricted)
X foreign (j# identifies j
X nulls not allowed
X delete of s restricted
X update of j.j# cascades)
X (qty);
X
X#11#
Xcreate unique index sx on s (s#);
X#12#
Xcreate unique index px on p (p#);
X#13#
Xcreate unique index jx on j (j#);
X#14#
Xcreate unique index spjx on spj (s#, p#, j#);
X
X#15#
Xinsert into s from sdate;
X#16#
Xinsert into p from pdate;
X#17#
Xinsert into j from jdate;
X#18#
Xinsert into spj from spjdate;
X
X#19#
Xcreate view sp
X as select s#, p# from spj;
X
X#20#
Xselect s#, status
Xfrom s
Xwhere city = 'Paris';
X
X#21#
Xselect p#
Xfrom sp;
X
X#22#
Xselect unique p#
Xfrom sp;
X
X#23#
Xselect p#, weight * 454
Xfrom p;
X
X#24#
Xselect p#, "Weight in grams =", weight * 454
Xfrom p;
X
X#25#
Xselect *
Xfrom s;
X
X#26#
Xselect s#, s_name, status, city
Xfrom s;
X
X#27#
Xselect s.*
Xfrom s;
X
X#28#
Xselect s#
Xfrom s
Xwhere city = "paris"
Xand status > 20;
X
X#29#
Xselect s#, status
Xfrom s
Xwhere city = "paris"
Xorder by status desc;
X
X#30#
Xselect p#, weight * 454
Xfrom p
Xorder by 2, P#;
X
X#31#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight between 16 and 19;
X
X#32#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight not between 16 and 19;
X
X#33#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight in (12, 16, 17);
X
X#34#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight not in (12, 16, 17);
X
X#35#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere weight = 12 or weight = 16 or weight = 17;
X
X#36#
Xselect p#, pname, color, weight, city
Xfrom p
Xwhere pname like 'C%';
X
X#37#
Xselect s#
Xfrom s
Xwhere status is null;
X
X#38#
Xselect s.*, p.*
Xfrom s, p
Xwhere s.city = p.city;
X
X#39#
Xselect s.*, p.*
Xfrom s, p
Xwhere s.city > p.city;
X
X#40#
Xselect s.*, p.*
Xfrom s, p
Xwhere s.city = p.city
Xand s.status != 20;
X
X#41#
Xselect s.s#, p.p#
Xfrom s, p
Xwhere s.city = p.city;
X
X#42#
Xselect unique s.*, p.*
Xfrom s, sp, p
Xwhere s.s# = sp.s#
Xand sp.p# = p.p#;
X
X#43#
Xselect first.s#, second.s#
Xfrom s first, s second
Xwhere first.city = second.city;
X
X#44#
Xselect first.s#, second.s#
Xfrom s first, s second
Xwhere first.city = second.city
Xand first.s# < second.s#;
X
X#45#
Xselect s#, p#, j#
Xfrom s, p, j
Xwhere not
X (s.city = p.city and p.city = j.city);
X
X#46#
Xselect s#, p#, j#
Xfrom s, p, j
Xwhere s.city != p.city
Xand p.city != j.city
Xand j.city != s.city;
X
X#47#
Xselect unique j#
Xfrom spj spjx
Xwhere not exists
X (select *
X from spj spjy
X where spjy.j# = spjx.j#
X and not exists
X (select *
X from spj spjz
X where spjz.p# = spjy.p#
X and spjz.s# = 'S1'
X )
X );
X
X#48#
Xselect unique j#
Xfrom spj spjx
Xwhere not exists
X (select *
X from spj spjy
X where exists
X (select *
X from spj spja
X where spja.s# = spjy.s#
X and spja.p# in
X (select p#
X from p
X where color = 'red')
X and not exists
X (select *
X from spj spjb
X where spjb.s# = spjy.s#
X and spjb.j# = spjx.j# )));
X
X#49#
Xselect unique s#
Xfrom spj
Xwhere p# in
X ( select p#
X from spj
X where s# in
X (select s#
X from spj
X where p# in
X (select p#
X from p
X where color = "red" )));
X
X#50#
Xselect unique s#
Xfrom spj spjx
Xwhere p# = 'p1'
Xand qty >
X ( select avg(qty)
X from spj spjy
X where p# = 'P1'
X and spjy.j# = spjx.j# );
X
X#51#
Xselect count(unique j#)
Xfrom spj
Xwhere s# = 's1';
X
X#52#
Xselect j#
Xfrom spj
Xwhere p# = 'p1'
Xgroup by j#
Xhaving avg (qty) >
X (select max(qty)
X from spj
X where j# = 'j1');
X
X#53#
Xselect city from s
Xunion
Xselect city from p
Xunion
Xselect city from j
Xorder by 1;
X
X#54#
Xselect p#, 'weight >16 lb'
Xfrom p
Xwhere weight >16
Xunion
Xselect p#, 'supplied by S2'
Xfrom sp
Xwhere s# = 'S2'
Xorder by 2, 1;
X
X#55#
Xselect p.p#, 'Weight in grams =', p.weight * 454, p.color,
X 'Max shipped quantity =', max(sp.qty)
Xfrom p, sp
Xwhere p.p# = sp.p#
Xand p.color in ('red', 'blue')
Xand sp.qty >200
Xgroup by p.p#, p.weight, p.color
Xhaving sum(qty) >350
Xorder by 6, p.p# desc;
X
X#56#
Xselect unique spjx.j#
Xfrom spj spjx
Xwhere exists
X (select *
X from spj spjy
X where spjy.p# = spjx.p#
X and spjy.s# = 's1');
X
X#57#
Xupdate p
Xset color = 'Orange'
XWhere color = 'Red';
X
X#58#
Xdelete
Xfrom j
Xwhere j# not in
X (select j#
X from spj );
X
X#59#
Xcreate table reds (s#);
X
X#60#
Xinsert into red (s#)
X select unique s#
X from spj, p
X where spj.p# = p.p#
X and color = 'Red';
X
X#61#
Xupdate spj
Xset qty = qty *1.1
Xwhere s# in
X (select s#
X from REDS );
X
X#62#
Xdrop table reds;
X
X#63#
Xdelete
Xfrom spj
Xwhere 'Rome' =
X (select city
X from j
X where j.j# = spj.j#);
X
X#64#
Xdelete
Xfrom j
Xwhere city = 'Rome';
X
X#65#
Xinsert
Xinto s (s#, s_name, city)
Xvalues ('S10', 'white', 'New York');
X
X#66#
Xinsert
Xinto s (s#, s_name, status, city)
Xvalues ('S11', 'white', NULL, 'New York');
X
X#67#
Xcreate view spv (s#, p#, qty )
X as select s#, p#, sum (qty)
X from spj
X group by s#, p#;
X
X#68#
Xcreate view jc (j#, city )
X as select unique j.j#, j.city
X from j, spj
X where j.j# = spj.j#
X and (spj.s# = 'S1' or spj.p# = 'P1' );
X
X#69#
Xcreate view jc (j#, city)
X as select j.j#, j.city
X from j
X where j.j# in
X (select j#
X from spj
X where s# = 'S1')
X and j.j# in
X (select j#
X from spj
X where p# = 'P1');
X
X#70#
Xcreate view non_colocated
X as select s#, p#
X from s, p
X where s.city != p.city;
X
X#71#
Xcreate view london_suppliers
X as select s#, s_name, status
X from s
X where city = "London";
X
X#72#
Xgrant select on table s to charly;
X
X#73#
Xgrant select , update(status, city) on table s
X to judy, jack, john;
X
X#74#
Xgrant createtab on database dbx to sharon;
X
X#75#
Xgrant select on table s to u2 with grant option;
X
X#76#
Xrevoke select on table s from u2;
X
X#77#
Xrevoke select, update on table s from u2;
X
X#78#
Xlock table s in exclusive mode;
X
X#79#
Xlock table p in share mode;
X
X#80#
Xstore program backup ( name = "p", fname = "/backup/p" )
Xlock table name in exclusive mode;
Xdump table name to fname;
Xlock table name in share mode;
Xcommit work;
Xend store;
X
X#81#
Xfirstprog ( "p", "/backup/p" );
END_OF_vtest2
if test 7485 -ne `wc -c <vtest2`; then
echo shar: \"vtest2\" unpacked with wrong size!
fi
# end of overwriting check
fi
echo shar: End of shell archive.
exit 0
--
We work in the dark
We do what we can
We give what we have
Our doubt is our passion, and our passion is our task
The rest is the madness of art.
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -