数据库SQL问题
\u4e00\u4e2a\u6570\u636e\u5e93sql\u8bed\u53e5\u95ee\u98981\u3001\u8bf4\u660e\uff1a\u521b\u5efa\u6570\u636e\u5e93
CREATE DATABASE database-name
2\u3001\u8bf4\u660e\uff1a\u5220\u9664\u6570\u636e\u5e93
drop database dbname
3\u3001\u8bf4\u660e\uff1a\u5907\u4efdsql server
--- \u521b\u5efa \u5907\u4efd\u6570\u636e\u7684 device
USE master
EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat'
--- \u5f00\u59cb \u5907\u4efd
BACKUP DATABASE pubs TO testBack
4\u3001\u8bf4\u660e\uff1a\u521b\u5efa\u65b0\u8868
create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..)
\u6839\u636e\u5df2\u6709\u7684\u8868\u521b\u5efa\u65b0\u8868\uff1a
A\uff1acreate table tab_new like tab_old (\u4f7f\u7528\u65e7\u8868\u521b\u5efa\u65b0\u8868)
B\uff1acreate table tab_new as select col1,col2\u2026 from tab_old definition only
5\u3001\u8bf4\u660e\uff1a\u5220\u9664\u65b0\u8868
drop table tabname
6\u3001\u8bf4\u660e\uff1a\u589e\u52a0\u4e00\u4e2a\u5217
Alter table tabname add column col type
\u6ce8\uff1a\u5217\u589e\u52a0\u540e\u5c06\u4e0d\u80fd\u5220\u9664\u3002DB2\u4e2d\u5217\u52a0\u4e0a\u540e\u6570\u636e\u7c7b\u578b\u4e5f\u4e0d\u80fd\u6539\u53d8\uff0c\u552f\u4e00\u80fd\u6539\u53d8\u7684\u662f\u589e\u52a0varchar\u7c7b\u578b\u7684\u957f\u5ea6\u3002
7\u3001\u8bf4\u660e\uff1a\u6dfb\u52a0\u4e3b\u952e\uff1a Alter table tabname add primary key(col)
\u8bf4\u660e\uff1a\u5220\u9664\u4e3b\u952e\uff1a Alter table tabname drop primary key(col)
8\u3001\u8bf4\u660e\uff1a\u521b\u5efa\u7d22\u5f15\uff1acreate [unique] index idxname on tabname(col\u2026.)
\u5220\u9664\u7d22\u5f15\uff1adrop index idxname
\u6ce8\uff1a\u7d22\u5f15\u662f\u4e0d\u53ef\u66f4\u6539\u7684\uff0c\u60f3\u66f4\u6539\u5fc5\u987b\u5220\u9664\u91cd\u65b0\u5efa\u3002
9\u3001\u8bf4\u660e\uff1a\u521b\u5efa\u89c6\u56fe\uff1acreate view viewname as select statement
\u5220\u9664\u89c6\u56fe\uff1adrop view viewname
10\u3001\u8bf4\u660e\uff1a\u51e0\u4e2a\u7b80\u5355\u7684\u57fa\u672c\u7684sql\u8bed\u53e5
\u9009\u62e9\uff1aselect * from table1 where \u8303\u56f4
\u63d2\u5165\uff1ainsert into table1(field1,field2) values(value1,value2)
\u5220\u9664\uff1adelete from table1 where \u8303\u56f4
\u66f4\u65b0\uff1aupdate table1 set field1=value1 where \u8303\u56f4
\u67e5\u627e\uff1aselect * from table1 where field1 like \u2019%value1%\u2019 ---like\u7684\u8bed\u6cd5\u5f88\u7cbe\u5999\uff0c\u67e5\u8d44\u6599!
\u6392\u5e8f\uff1aselect * from table1 order by field1,field2 [desc]
\u603b\u6570\uff1aselect count as totalcount from table1
\u6c42\u548c\uff1aselect sum(field1) as sumvalue from table1
\u5e73\u5747\uff1aselect avg(field1) as avgvalue from table1
\u6700\u5927\uff1aselect max(field1) as maxvalue from table1
\u6700\u5c0f\uff1aselect min(field1) as minvalue from table1
11\u3001\u8bf4\u660e\uff1a\u51e0\u4e2a\u9ad8\u7ea7\u67e5\u8be2\u8fd0\u7b97\u8bcd
A\uff1a UNION \u8fd0\u7b97\u7b26
UNION \u8fd0\u7b97\u7b26\u901a\u8fc7\u7ec4\u5408\u5176\u4ed6\u4e24\u4e2a\u7ed3\u679c\u8868\uff08\u4f8b\u5982 TABLE1 \u548c TABLE2\uff09\u5e76\u6d88\u53bb\u8868\u4e2d\u4efb\u4f55\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868\u3002\u5f53 ALL \u968f UNION \u4e00\u8d77\u4f7f\u7528\u65f6\uff08\u5373 UNION ALL\uff09\uff0c\u4e0d\u6d88\u9664\u91cd\u590d\u884c\u3002\u4e24\u79cd\u60c5\u51b5\u4e0b\uff0c\u6d3e\u751f\u8868\u7684\u6bcf\u4e00\u884c\u4e0d\u662f\u6765\u81ea TABLE1 \u5c31\u662f\u6765\u81ea TABLE2\u3002
B\uff1a EXCEPT \u8fd0\u7b97\u7b26
EXCEPT\u8fd0\u7b97\u7b26\u901a\u8fc7\u5305\u62ec\u6240\u6709\u5728 TABLE1 \u4e2d\u4f46\u4e0d\u5728 TABLE2 \u4e2d\u7684\u884c\u5e76\u6d88\u9664\u6240\u6709\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868\u3002\u5f53 ALL \u968f EXCEPT \u4e00\u8d77\u4f7f\u7528\u65f6 (EXCEPT ALL)\uff0c\u4e0d\u6d88\u9664\u91cd\u590d\u884c\u3002
C\uff1a INTERSECT \u8fd0\u7b97\u7b26
INTERSECT\u8fd0\u7b97\u7b26\u901a\u8fc7\u53ea\u5305\u62ec TABLE1 \u548c TABLE2 \u4e2d\u90fd\u6709\u7684\u884c\u5e76\u6d88\u9664\u6240\u6709\u91cd\u590d\u884c\u800c\u6d3e\u751f\u51fa\u4e00\u4e2a\u7ed3\u679c\u8868\u3002\u5f53 ALL\u968f INTERSECT \u4e00\u8d77\u4f7f\u7528\u65f6 (INTERSECT ALL)\uff0c\u4e0d\u6d88\u9664\u91cd\u590d\u884c\u3002
\u6ce8\uff1a\u4f7f\u7528\u8fd0\u7b97\u8bcd\u7684\u51e0\u4e2a\u67e5\u8be2\u7ed3\u679c\u884c\u5fc5\u987b\u662f\u4e00\u81f4\u7684\u3002
12\u3001\u8bf4\u660e\uff1a\u4f7f\u7528\u5916\u8fde\u63a5
A\u3001left \uff08outer\uff09 join\uff1a
\u5de6\u5916\u8fde\u63a5\uff08\u5de6\u8fde\u63a5\uff09\uff1a\u7ed3\u679c\u96c6\u51e0\u5305\u62ec\u8fde\u63a5\u8868\u7684\u5339\u914d\u884c\uff0c\u4e5f\u5305\u62ec\u5de6\u8fde\u63a5\u8868\u7684\u6240\u6709\u884c\u3002
SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B\uff1aright \uff08outer\uff09 join:
\u53f3\u5916\u8fde\u63a5(\u53f3\u8fde\u63a5)\uff1a\u7ed3\u679c\u96c6\u65e2\u5305\u62ec\u8fde\u63a5\u8868\u7684\u5339\u914d\u8fde\u63a5\u884c\uff0c\u4e5f\u5305\u62ec\u53f3\u8fde\u63a5\u8868\u7684\u6240\u6709\u884c\u3002
C\uff1afull/cross \uff08outer\uff09 join\uff1a
\u5168\u5916\u8fde\u63a5\uff1a\u4e0d\u4ec5\u5305\u62ec\u7b26\u53f7\u8fde\u63a5\u8868\u7684\u5339\u914d\u884c\uff0c\u8fd8\u5305\u62ec\u4e24\u4e2a\u8fde\u63a5\u8868\u4e2d\u7684\u6240\u6709\u8bb0\u5f55\u3002
12\u3001\u5206\u7ec4:Group by:
\u4e00\u5f20\u8868\uff0c\u4e00\u65e6\u5206\u7ec4 \u5b8c\u6210\u540e\uff0c\u67e5\u8be2\u540e\u53ea\u80fd\u5f97\u5230\u7ec4\u76f8\u5173\u7684\u4fe1\u606f\u3002
\u7ec4\u76f8\u5173\u7684\u4fe1\u606f\uff1a\uff08\u7edf\u8ba1\u4fe1\u606f\uff09 count,sum,max,min,avg \u5206\u7ec4\u7684\u6807\u51c6)
\u5728SQLServer\u4e2d\u5206\u7ec4\u65f6\uff1a\u4e0d\u80fd\u4ee5text,ntext,image\u7c7b\u578b\u7684\u5b57\u6bb5\u4f5c\u4e3a\u5206\u7ec4\u4f9d\u636e
\u5728selecte\u7edf\u8ba1\u51fd\u6570\u4e2d\u7684\u5b57\u6bb5\uff0c\u4e0d\u80fd\u548c\u666e\u901a\u7684\u5b57\u6bb5\u653e\u5728\u4e00\u8d77\uff1b
13\u3001\u5bf9\u6570\u636e\u5e93\u8fdb\u884c\u64cd\u4f5c\uff1a
\u5206\u79bb\u6570\u636e\u5e93\uff1a sp_detach_db;\u9644\u52a0\u6570\u636e\u5e93\uff1asp_attach_db \u540e\u63a5\u8868\u660e\uff0c\u9644\u52a0\u9700\u8981\u5b8c\u6574\u7684\u8def\u5f84\u540d
14.\u5982\u4f55\u4fee\u6539\u6570\u636e\u5e93\u7684\u540d\u79f0:
sp_renamedb 'old_name', 'new_name'
\u7528mysql \u811a\u672c\u76f4\u63a5\u53bb\u67e5sql server \u91cc\u8fb9\u7684\u8868\u6570\u636e\u662f\u6ca1\u620f\u7684\uff08\u4ee5\u540e\u53ef\u80fd\u53ef\u4ee5\u5427\uff01\uff01\uff09\u3002\u653e\u5f03\u4f60\u7684\u5ff5\u5934\u3002
sql server \u91cc\u8fb9\u662f\u53ef\u4ee5\u67e5\u8be2mysql \u91cc\u7684\u6570\u636e\u8868\u3002
\u4f60\u53ea\u80fd\u901a\u8fc7\u7b2c\u4e09\u65b9\u7a0b\u5e8f\u53bb\u5b9e\u73b0\u4e86\u3002\uff01\uff01
1:
update 成绩 set 分数=105 where 学生.学号='10001'
2:
delete 成绩 where 学号
in
(select gra.学号 as sno from 成绩 gra, 学生 stu
where gra.学号 = stu.学号
and stu.姓名 like '张%');
3:
create index field_index on 学生(专业)
4:
create view courseView
as
select course.课程号,course.名称,course.学分
from 课程 course,成绩 grade
where course.课程号 = grade.课程号
group by course.课程号
having max(grade.分数)>95
5:
select course.名称,avg(grade.分数)
from 课程 course,成绩 grade
where course.课程号 = grade.课程号
group by course.课程号
6:
select course.课程号, course.名称,course.学分
from 课程 course,成绩 grade
where course.课程号 = grade.课程号
and grade.分数=100
7:
select 学号,姓名
from 学生
where 专业='计算机应用'
and 奖学金>0;
8:
select student.学号, student.姓名,student.专业
from 学生 stu
where stu.学号
not in
(select student.学号
from 学生 student,成绩 grade
where student.学号 = grade.学号
and grade.分数<60)
//存储过程
create procedure calculate ( in inputN int,out result int)
begin
declare
i int default 1;
set result=0;
while i<=inputN do
set result=result+i;
set i=i+1;
end while;
end;
什么数据库？？mysql的还是oracle的或是其他的
绛旓細娉ㄦ剰锛氬皬鍐欑殑瀛楁瘝涓鸿〃鐨勫埆鍛藉悕锛屽瓧娈靛ぇ灏忓啓娌℃湁鍖哄垎锛屼綘瑕佹槸鍖哄垎鐨勮瘽鍖哄垎涓嬶紝sql 鏌ヨ鍣ㄩ噷闈㈠彲浠ヨ缃 鏌ヨ璁㈠崟閲戦鏈楂樼殑瀹㈡埛鍚嶃佽揣鍝佸悕绉般佽喘涔版暟閲忋侀噾棰濆拰涓嬪崟鏃堕棿銆俿elect c.cname 瀹㈡埛鍚 ,p.pname 璐у搧鍚嶇О,o.quantity 璐拱鏁伴噺,max(o.money) 閲戦锛宱.orderdate 涓嬪崟鏃堕棿 from C...
绛旓細鏁版嵁搴鏄湁搴忓舰寮忕殑涓缁勪俊鎭紝鐢ㄤ簬璁块棶锛屽瓨鍌ㄥ拰妫绱㈡暟鎹侱BMS鏄竴涓帶鍒舵暟鎹淮鎶ゅ拰浣跨敤鐨勭▼搴忋傚畠琚涓烘槸绠＄悊鏁版嵁鐨勬枃浠剁鐞嗗櫒銆傛湁鍥涚绫诲瀷鐨凞BMS锛氭渶鏈夌敤鐨凞BMS鏄疪elational DBMS銆傚畠涓烘暟鎹彁渚涗簡涓涓叧绯昏繍绠楃銆SQL鍛戒护鍒嗕负浠ヤ笅绫诲瀷锛氬畠鍖呭惈鏉ヨ嚜涓涓垨澶氫釜琛ㄧ殑琛屽拰鍒楋紝鍙互瀹氫箟涓鸿櫄鎷熻〃銆傚畠娑堣楃殑...
绛旓細銆愮瓟妗堛戯細B SQL鏄叧绯鏁版嵁搴绠＄悊绯荤粺鐨勬牳蹇冿紝鐗瑰埆鏄柊鐨勫ぇ鍨嬫暟鎹簱绠＄悊绯荤粺鏇存槸瀹屽叏鐢盨QL瀹炵幇锛涘畠鍙互鐙珛瀹屾垚鏁版嵁搴撶敓鍛藉懆鏈熶腑鐨勫叏閮ㄦ椿鍔紝濡傚畾涔夊叧绯绘ā寮忋佹彃鍏ユ暟鎹缓绔嬫暟鎹簱銆佹煡璇㈡洿鏂般佺淮鎶ゃ佹暟鎹簱閲嶆瀯銆佹暟鎹簱瀹夊叏鎺у埗绛夛紝涓烘暟鎹簱搴旂敤绯荤粺鐨勫紑鍙戞彁渚涗簡鑹ソ鐨勭幆澧冿紱SQL鏄珮搴﹂潪杩囩▼鍖栫殑璇█锛屽叾涓病...
绛旓細瀹炰緥浣跨敤鐨凱GA鍐呭瓨瓒呭嚭鐨勬儏鍐靛彲鑳戒細瀵艰嚧鍐呭瓨涓嶈冻锛岃繘鑰屽奖鍝嶅疄渚嬬殑姝ｅ父杩愯銆備负浜嗚В鍐宠繖涓闂锛屽彲浠ラ噰鍙栦互涓嬫帾鏂斤細1. 璋冩暣PGA_AGGREGATE_TARGET鍙傛暟锛氬彲浠ラ氳繃澧炲姞鎴栧噺灏慞GA_AGGREGATE_TARGET鍙傛暟鐨勫兼潵璋冩暣PGA鍐呭瓨鐨勪娇鐢ㄩ噺銆傚鏋滃疄渚嬩娇鐢ㄧ殑PGA鍐呭瓨瓒呭嚭浜嗛檺鍒讹紝鍙互鑰冭檻澧炲姞PGA_AGGREGATE_TARGET鐨勫硷紝浠ヤ究涓哄疄渚...
绛旓細1銆侀鍏堬紝杩欐潯sql 浣跨敤浜嗭紙not exists锛夛紝杩欐潯sql鎬濊矾澶т綋鏄繖鏍风殑锛氭煇涓猳rder锛屽彇鍚嶄负R, 濡傛灉10308杩欎釜order涓嬫煇涓鍗曞晢鍝佷笉鍦≧璁㈠崟涓嬬殑璁㈠崟鍟嗗搧涓紝閭ｄ箞R杩欎釜order灏变笉绗﹀悎鏉′欢锛2銆乻ql鎷嗚В鍒嗘瀽锛氶鍏堟槑纭畁ot exists 鐨勬剰鎬濇槸锛氬叾鍚庢病鏈鏁版嵁杩斿洖锛岄偅涔堣select鐨勬暟鎹墠琚姞鍏ュ埌缁撴灉闆嗕腑锛1锛夌涓...
绛旓細--1銆佸埄鐢SQL璇彞鍒涘缓浠ヤ笂涓夊紶琛紝骞舵寚瀹氭瘡寮犺〃鐨勪富閿拰澶栭敭锛--瀛︾敓琛(S)锛歝reate table student(sid varchar2(20) primary key,sname varchar2(20),age number,sex varchar2(4),department varchar2(20),address varchar2(50),birthplace varchar2(50));--閫夎琛(SC)锛歝reate table class(sid...
绛旓細--鍩虹棰 --1.鏌ヨ璇昏呮渶鍠滅埍鐨3绉嶅浘涔﹀垎绫诲彿锛屽嵆鍊熼槄琛ㄤ腑鍑虹幇鏈澶氱殑3绫诲浘涔﹀垎绫诲彿 SELECT TOP 3 [鍒嗙被鍙穄 FROM [鍥句功] JOIN [鍊熼槄] ON [鍥句功].[鍥句功缂栧彿]=[鍊熼槄].[鍥句功缂栧彿]GROUP BY [鍒嗙被鍙穄 ORDER BY COUNT(*) DESC --闄勫姞棰 --1.鏌ヨ灏忎簬20宀佺殑璇昏呮渶鍠滄鐨3绫诲浘涔 SELECT TOP...
绛旓細1. select * from EMPLOYEE where Salary>(select avg(Salary) from EMPLOYEE group by Dnumber)2. select * from EMPLOYEE where ESSN in (select ESSN from WORKS_ON group by Pnumber,ESSN having Count(1)>=3)3. -- 姣忎釜閮ㄩ棬鐨勬墍鏈夎亴宸ュ湪姣忎釜椤圭洰涓殑宸ヤ綔鎬绘椂鏁 Create View DeptPro AS...
绛旓細sql鏁版嵁搴璐ㄧ枒鏄缃敊璇犳垚鐨勶紝瑙ｅ喅鏂规硶涓猴細1銆侀氳繃DBCC CHECKCB('DBName') 鏉ユ娴嬫暟鎹簱寮傚父鐨勫師鍥狅紝濡傛灉鍙互妫娴嬪埌鏁版嵁搴撶殑寮傚父锛屽叾涓孩鑹查儴鍒嗗嵆鏃舵暟鎹洰鍓嶅瓨鍦ㄧ殑闂锛屾垜浠篃鍦ㄦ娴嬬粨鏋滄渶鍚庣湅鍒版暟鎹殑鎬讳綋鐨勯敊璇儏鍐电殑姹囨汇2銆佸鏋滄暟鎹簱鐨勬暣浣撶粨鏋滄病鏈夐棶棰橈紝鍙槸閮ㄥ垎琛ㄧ殑鏁版嵁缁撴瀯銆佺储寮曘佸瓨鍌ㄥ嚭鐜板紓甯...
绛旓細鏁版嵁搴撴ц兘闄愬埗銆佹墿灞曟ч檺鍒躲1銆佹暟鎹簱鎬ц兘闄愬埗锛SQL鏁版嵁搴鍦ㄥ鐞嗗ぇ瑙勬ā鏁版嵁鍜岄珮骞跺彂璁块棶鏃舵ц兘鍙楅檺銆傝繖鏄洜涓篠QL鏁版嵁搴撲娇鐢ㄤ簡澶嶆潅鐨勬煡璇㈣鍙ュ拰浜嬪姟鏈哄埗锛屽鑷磋緝楂樼殑绯荤粺寮閿銆2銆佹墿灞曟ч檺鍒讹細浼犵粺鐨凷QL鏁版嵁搴撳湪闈㈠娴烽噺鏁版嵁鏃跺彲鑳介亣鍒版墿灞曟闂銆傚綋鏁版嵁閲忓闀挎椂锛岄渶瑕佽繘琛屽垎搴撳垎琛ㄦ垨鑰呬娇鐢ㄦ暟鎹簱闆嗙兢鏉...
