MySQL基础总结合集（2万字总结）

MySQL鏄暐锛熸暟鎹簱鍙堟槸鍟ワ紵

MySQL锛欬/h1>

MySQL 鏄渶娴佽鐨勫叧绯诲瀷鏁版嵁搴撶鐞嗙郴缁燂紝鍦 WEB 搴旂敤鏂归潰 MySQL 鏄渶濂界殑 RDBMS(Relational Database Management System锛氬叧绯绘暟鎹簱绠＄悊绯荤粺)搴旂敤杞欢涔嬩竴銆

鏁版嵁搴擄細

鏁版嵁搴擄紙Database锛夋槸鎸夌収鏁版嵁缁撴瀯鏉ョ粍缁囥€佸瓨鍌ㄥ拰绠＄悊鏁版嵁鐨勪粨搴撱€

姣忎釜鏁版嵁搴撻兘鏈変竴涓垨澶氫釜涓嶅悓鐨 API 鐢ㄤ簬鍒涘缓锛岃闂紝绠＄悊锛屾悳绱㈠拰澶嶅埗鎵€淇濆瓨鐨勬暟鎹€

鎴戜滑涔熷彲浠ュ皢鏁版嵁瀛樺偍鍦ㄦ枃浠朵腑锛屼絾鏄湪鏂囦欢涓鍐欐暟鎹€熷害鐩稿杈冩參銆

鎵€浠ワ紝鎴戜滑浣跨敤鍏崇郴鍨嬫暟鎹簱绠＄悊绯荤粺涓€鑸槸鐢ㄦ潵瀛樺偍鍜岀鐞嗗ぇ鏁版嵁閲忋€

鍏崇郴鍨嬫暟鎹簱锛圫QL锛夛細鍏崇郴鍨嬫暟鎹簱鎸囩殑鏄娇鐢ㄥ叧绯绘ā鍨嬶紙浜岀淮琛ㄦ牸妯″瀷锛夋潵缁勭粐鏁版嵁鐨勬暟鎹簱銆

闈炲叧绯诲瀷鏁版嵁搴擄紙NOSQL锛夛細闈炲叧绯诲瀷鏁版嵁搴撳張琚О涓 NoSQL锛圢ot Only SQL )锛屾剰涓轰笉浠呬粎鏄 SQL銆傞€氬父鎸囨暟鎹互瀵硅薄鐨勫舰寮忓瓨鍌ㄥ湪鏁版嵁搴撲腑锛岃€屽璞′箣闂寸殑鍏崇郴閫氳繃姣忎釜瀵硅薄鑷韩鐨勫睘鎬ф潵鍐冲畾锛屽父鐢ㄤ簬瀛樺偍闈炵粨鏋勫寲鐨勬暟鎹€

MySQL瀹夎鍙婇厤缃®

1. 棣栧厛鍏堜笅杞藉埌涓€涓枃浠跺す涓­
2. 涓嬭浇鍚庡湪鐜鍙橀噺涓璓ath璺緞 娣诲姞MySQL鐨刡in鐩綍鐨勮矾寰凕/li>
3. 鍦 mysql-5.7.19-winx64 鏂囦欢澶逛腑 鍒涘缓涓€涓 my.ini 鏂囦欢锛屽唴瀹逛负锛欬/li>
4. [client] port=3306 default-character-set=utf8 [mysqld] #璁剧疆涓鸿嚜宸盡YSQL鐨勫畨瑁呯洰褰 basedir=D:\hspmysql\mysql-5.7.19-winx64\ #璁剧疆涓篗YSQL鐨勬暟鎹洰褰 datadir=E:\mysql-5.7.19-winx64\data\ port=3306 character_set_server=utf8 ##璺宠繃瀹夊叏妫€鏌ワ紝娉ㄩ攢鍚庯紝闇€瑕佽緭鍏ユ纭殑鐢ㄦ埛鍚嶅拰瀵嗙爜鎵嶈兘鍚姩 skip-grant-tables
5. 浣跨敤绠＄悊鍛樼殑韬唤鎵撳紑cmd锛屽苟鍒囨崲鍒板畨瑁呯殑MySQL鐨刡in鐩綍搴曚笅锛屾墽琛 mysqld-install
6. 鍒濆鍖栨暟鎹簱锛歮ysqld --initialize-insecure --user=mysql 姝ゅ彞鎵ц鍚庡氨浼氱敓鎴愪竴涓 data 鐩綍
7. 鍚姩鏈嶅姟 net start mysql 锛堣鐪婱ySQL鏈嶅姟鏄惁鍚姩鍙互鍘讳换鍔＄鐞嗗櫒鐨勬湇鍔℃爮鐪嬶級
8. 鍏抽棴鏈嶅姟 net stop mysql
9. 杩涘叆MySQL绠＄悊缁堢 mysql -u root -p
10. 浣跨敤mysql鏁版嵁搴 锛歶se mysql; 鐒跺悗 UPDATE user SET authentication_string = PASSWORD('鏂扮殑瀵嗙爜') WHERE user = 'dbadmin' AND host = 'localhost'; FLUSH PRIVILEGES; 杩欏彞琛ㄧず鍒锋柊涓€涓嬫潈闄忺/li>
11. quit 鎸囬€€鍑虹鐞嗙粓绔¯

濡傛灉鐪熷湪 5-10姝ラ涓摢閲屽嚭浜嗛潪閿欙紝鍙垹闄ySQL鍚庨噸鏂伴厤缃湇鍔 (sc delete mysql 璇ヨ鍙ュ伐浣滄椂涓€瀹氳鎱庣敤锛侊紒)

4~7姝ヤ竴瀹氳浠ョ鐞嗗憳韬唤杩愯鎺у埗鍙版墠琛孅/h1>

MySQL鐨勬湇鍔★紝榛樿鏄€滃惎鍔ㄢ€濈殑鐘舵€侊紝鍙湁鍚姩浜唌ysql鎵嶈兘鐢ㄣ€傞粯璁ゆ儏鍐典笅鏄€滆嚜鍔ㄢ€濆惎鍔紝鑷姩鍚姩琛ㄧず涓嬩竴娆￠噸鍚搷浣滅郴缁熺殑鏃跺€欒嚜鍔ㄥ惎鍔ㄨ鏈嶅姟銆

鍙互鍦ㄦ湇鍔′笂鐐瑰嚮鍙抽敭锛 鍚姩 閲嶅惎鏈嶅姟 鍋滄鏈嶅姟 ...

杩樺彲浠ユ敼鍙樻湇鍔＄殑榛樿閰嶇疆锛 鏈嶅姟涓婄偣鍑诲彸閿紝灞炴€э紝鐒跺悗鍙互閫夋嫨鍚姩鏂瑰紡锛 鑷姩锛堝欢杩熷惎鍔級 鑷姩 鎵嬪姩 绂佺敤

鍦╳indows鎿嶄綔绯荤粺褰撲腑锛屾€庝箞浣跨敤鍛戒护鏉ュ惎鍔ㄥ拰鍏抽棴mysql鏈嶅姟鍛紵 璇硶锛 net stop 鏈嶅姟鍚嶇О; net start 鏈嶅姟鍚嶇О;

鍏跺畠鏈嶅姟鐨勫惎鍋滈兘鍙互閲囩敤浠ヤ笂鐨勫懡浠ゃ€侟/h1>

浣跨敤鍛戒护琛岀獥鍙ｈ繛鎺ySQL鏁版嵁搴擄細

棣栧厛锛孧ySQL鏄竴绉嶆湇鍔?鍙湪浠诲姟绠＄悊鍣ㄦ湇鍔℃爮涓煡鐪嬪埌MySQL)锛岃€屽彧瑕佹槸涓€涓湇鍔￠兘浼氱洃鍚竴涓鍙

mysql -h 涓绘満IP -P 绔彛 -u 鐢ㄦ埛鍚 -p瀵嗙爜 ( p鍜屽瘑鐮侀棿涓嶈鏈夌┖鏍硷紝濡傛灉-p鍚庨潰娌℃湁鍐欏瘑鐮侊紝鍥炶溅鍚庝細瑕佹眰杈撳叆瀵嗙爜 )

濡傛灉涓嶅啓 -h 涓绘満IP锛岄粯璁ゅ氨鏄繛鎺ュ埌鏈湴

濡傛灉涓嶅啓-P 绔彛锛岄粯璁ゅ氨鏄?306

鍦ㄥ疄闄呭伐浣滀腑寰€寰€浼氫慨鏀圭鍙ｅ彿鑰屼笉鏄娇鐢ㄦ榛樿绔彛鍙凤紝姝ゅ涓轰簡鏂逛究灏变笉鍋氭敼鍔ㄤ簡

MySQL鏁版嵁搴撶殑涓夊眰缁撴瀯

鏁版嵁鍦ㄦ暟鎹簱涓殑瀛樺偍褰㈠紡

浠ヨ〃鏍肩殑褰㈠紡瀛樺偍鏁版嵁

浠讳綍涓€寮犺〃閮芥湁琛屽拰鍒楋細

琛岋紙row锛夛細琚О涓烘暟鎹?璁板綍銆 鍒楋紙column锛夛細琚О涓哄瓧娈点€

姣忎竴涓瓧娈甸兘鏈夛細瀛楁鍚嶃€佹暟鎹被鍨嬨€佺害鏉熺瓑灞炴€с€ 瀛楁鍚嶅彲浠ョ悊瑙ｆ槸涓€涓櫘閫氱殑鍚嶅瓧锛岃鍚嶇煡鎰忓氨琛屻€ 鏁版嵁绫诲瀷锛氬瓧绗︿覆锛屾暟瀛楋紝鏃ユ湡绛夈€

浠€涔堟槸SQL璇彞锛烖/h1>

SQL锛氱粨鏋勫寲鏌ヨ璇█ 绋嬪簭鍛橀渶瑕佸涔燬QL璇彞锛岀▼搴忓憳閫氳繃缂栧啓SQL璇彞锛岀劧鍚嶥BMS璐熻矗鎵цSQL 璇彞锛屾渶缁堟潵瀹屾垚鏁版嵁搴撲腑鏁版嵁鐨勫鍒犳敼鏌ユ搷浣溿€

SQL璇彞鍒嗙被

DQL锛氭暟鎹煡璇㈣瑷€[select鈥︹€ ]

DML锛氭暟鎹搷浣滆瑷€[澧炲姞insert 锛屼慨鏀箄pdate锛屽垹闄elete] 鎿嶄綔鐨勬槸琛ㄤ腑鐨勬暟鎹

DDL锛氭暟鎹畾涔夎瑷€[create銆乨rop銆乤lter] 鎿嶄綔鐨勬槸琛ㄧ殑缁撴瀯

DCL锛氭暟鎹帶鍒惰瑷€[绠＄悊鏁版嵁搴擄紝姣斿锛氭巿鏉僩rant銆佹挙閿€鏉冮檺revoke.... ]

TCL锛氫簨鍔℃帶鍒惰瑷€ [濡傦細浜嬪姟鎻愪氦锛歝ommit; 浜嬪姟鍥炴粴锛歳ollback;]

鏁版嵁搴撱€佹暟鎹簱绠＄悊绯荤粺銆丼QL涔嬮棿鐨勫叧绯狐/h1>

涓夎€呬箣闂寸殑鍏崇郴锛 DBMS閫氳繃鎵цSQL鏉ユ搷浣淒B

鍏堝畨瑁呮暟鎹簱绠＄悊绯荤粺MySQL锛岀劧鍚庡涔燬QL璇彞鎬庝箞鍐欙紝缂栧啓SQL璇彞涔嬪悗锛孌BMS 瀵筍QL璇彞杩涜鎵ц锛屾渶缁堟潵瀹屾垚鏁版嵁搴撶殑鏁版嵁绠＄悊銆

甯哥敤鍛戒护

娉ㄦ剰锛氳繖浜涘懡浠や笉鍖哄垎澶у皬鍐欓兘琛屻€侟/h1>

娉ㄦ剰锛歮ysql鏄笉瑙佲€淃鈥濅笉鎵ц锛屸€淃鈥濊〃绀虹粨鏉燂紒

閫€鍑篹xit; 鎴栬€ quit;

鏌ョ湅mysql鏁版嵁搴撶殑鐗堟湰鍙凤細 select version();

鏌ョ湅mysql涓湁鍝簺鏁版嵁搴擄紵 show databases; 娉ㄦ剰锛氫互鍒嗗彿缁撳熬锛屽垎鍙锋槸鑻辨枃鐨勫垎鍙枫€

mysql榛樿鑷甫浜?涓暟鎹簱銆

鎬庝箞閫夋嫨浣跨敤鏌愪釜鏁版嵁搴撳憿锛 mysql> use test; 琛ㄧず浣跨敤涓€涓悕瀛楀彨鍋歵est鐨勬暟鎹簱銆

鏌ョ湅褰撳墠浣跨敤鐨勬槸鍝釜鏁版嵁搴擄紵 mysql> select database();

瀵煎叆涓€涓嬫彁鍓嶅噯澶囧ソ鐨勬暟鎹細 bjpowernode.sql 鏄彁鍓嶄负缁冧範鍑嗗鐨勬暟鎹簱琛ㄣ€ 鎬庝箞灏唖ql鏂囦欢涓殑鏁版嵁瀵煎叆鍛紵(鍙互灏嗘枃浠舵嫋鍏ource绌烘牸 鍚? 瑕佸湪浣跨敤鏌愪釜鏁版嵁搴撳悗鎵嶈兘浣跨敤璇ヨ鍙 mysql> source D:\course\03-MySQL\document\bjpowernode.sql

娉ㄦ剰锛氳矾寰勪腑涓嶈鏈変腑鏂囷紒锛侊紒锛

涓嶇湅琛ㄤ腑鐨勬暟鎹紝鍙湅琛ㄧ殑缁撴瀯锛屾湁涓€涓懡浠わ細 desc 琛ㄥ悕;

绠€鍗曟煡璇↑/h1>

鏌ヨ涓€涓瓧娈碉紵

select 瀛楁鍚 from 琛ㄥ悕;

鏌ヨ涓や釜瀛楁锛屾垨鑰呭涓瓧娈垫€庝箞鍔烇紵 浣跨敤閫楀彿闅斿紑鈥?鈥

鏌ヨ鎵€鏈夊瓧娈碉紵

浣跨敤锛歴elect * from 琛ㄥ悕;

鍒妸鎶婅繖绉嶅啓娉曞啓鍒 Java 绋嬪簭涓紝鍥犱负鍒版椂杩樿鎶 * 鍏堣浆鎹㈡垚瀛楁

缂虹偣锛氣憼鏁堢巼浣 鈶″彲璇绘€у樊銆

鍦ㄥ疄闄呭紑鍙戜腑涓嶅缓璁紝鍙互鑷繁鐜╂病闂銆 浣犲彲浠ュ湪DOS鍛戒护绐楀彛涓兂蹇€熺殑鐪嬩竴鐪嬪叏琛ㄦ暟鎹彲浠ラ噰鐢ㄨ繖绉嶆柟寮忋€

缁欐煡璇㈢殑鍒楄捣鍒悕锛烖/h1>

浣跨敤 as 鍏抽敭瀛楋紝鍙互鐪佺暐 select deptno,dname as deptname from dept;

鍋囪璧峰埆鍚嶇殑鏃跺€欙紝鍒悕閲岄潰鏈夌┖鏍硷紝鎬庝箞鍔烇紵 mysql> select deptno,dname dept name from dept; DBMS鐪嬪埌杩欐牱鐨勮鍙ワ紝杩涜SQL璇彞鐨勭紪璇戯紝涓嶇鍚堣娉曪紝缂栬瘧鎶ラ敊銆 鎬庝箞瑙ｅ喅锛 select deptno,dname 'dept name' from dept; //鍔犲崟寮曞彿 select deptno,dname "dept name" from dept; //鍔犲弻寮曞彿

娉ㄦ剰锛氬湪鎵€鏈夌殑鏁版嵁搴撳綋涓紝瀛楃涓茬粺涓€浣跨敤鍗曞紩鍙锋嫭璧锋潵锛 鍗曞紩鍙锋槸鏍囧噯锛屽弻寮曞彿鍦╫racle鏁版嵁搴撲腑鐢ㄤ笉浜嗐€備絾鏄湪mysql 涓彲浠ヤ娇鐢ㄣ€

鍐嶆寮鸿皟锛氭暟鎹簱涓殑瀛楃涓查兘鏄噰鐢ㄥ崟寮曞彿鎷捣鏉ャ€傝繖鏄爣鍑嗙殑銆 鍙屽紩鍙蜂笉鏍囧噯銆侟/h1>

瀛楁鍙互浣跨敤鏁板琛ㄨ揪寮廃/h1>

select ename,sal*12 from emp;

鏄剧ず鏃跺瓧娈靛悕鏄 sal*12 杩欐牱鏃朵竴鑸彇涓埆鍚 select ename,sal*12 year_sal from emp;

鏉′欢鏌ヨ

浠€涔堟槸鏉′欢鏌ヨ锛烖/h1>

鏌ヨ鍑烘潵绗﹀悎鏉′欢鐨 鈥 璇硶鏍煎紡锛 鈥 select 鈥 瀛楁1,瀛楁2,瀛楁3.... 鈥 from 鈥 琛ㄥ悕 鈥 where 鈥 鏉′欢;

閮芥湁鍝簺鏉′欢锛烖/h1>

= 绛変簬鏌ヨ钖祫绛変簬800鐨勫憳宸ュ鍚嶅拰缂栧彿锛 select empno,ename from emp where sal = 800;

<> 鎴 != 涓嶇瓑浜庢煡璇㈣柂璧勪笉绛変簬800鐨勫憳宸ュ鍚嶅拰缂栧彿锛 select empno,ename from emp where sal != 800;

鍚岀悊杩樻湁锛?lt;=锛?lt;锛?gt;=锛?gt;

between 鈥 and 鈥? 涓や釜鍊间箣闂? 绛夊悓浜 >= and <=

鏌ヨ钖祫鍦?450鍜?000涔嬮棿鐨勫憳宸ヤ俊鎭紵鍖呮嫭2450鍜?000锛

娉ㄦ剰锛 浣跨敤between and鐨勬椂鍊欙紝蹇呴』閬靛惊宸﹀皬鍙冲ぇ銆 between and鏄棴鍖洪棿锛屽寘鎷袱绔殑鍊笺€侟/h1>

is null 琛ㄧず涓 null锛坕s not null 涓嶄负绌猴級

鏌ヨ鍝簺鍛樺伐鐨勬触璐?琛ュ姪涓簄ull锛 select empno,ename,sal,comm from emp where comm = null;锛 鍍忚繖鏍风敤锛濇槸鏌ヤ笉鍒扮殑 锛 Empty set (0.00 sec)

娉ㄦ剰锛氬湪鏁版嵁搴撳綋涓璶ull涓嶈兘浣跨敤绛夊彿杩涜琛￠噺銆傞渶瑕佷娇鐢╥s null 鍥犱负鏁版嵁搴撲腑鐨刵ull浠ｈ〃浠€涔堜篃娌℃湁锛屽畠涓嶆槸涓€涓€硷紝鎵€浠ヤ笉鑳戒娇鐢 绛夊彿琛￠噺銆侟/h1>

姝ｇ‘鐨勶細select empno,ename,sal,comm from emp where comm is null;

and 骞朵笖鏌ヨ宸ヤ綔宀椾綅鏄疢ANAGER骞朵笖宸ヨ祫澶т簬2500鐨勫憳宸ヤ俊鎭紵

select empno,ename,job,sal from emp where job = 'MANAGER' and sal > 2500;

or 鎴栬€匋/h1>

鏌ヨ宸ヤ綔宀椾綅鏄疢ANAGER鎴朣ALESMAN鐨勫憳宸ワ紵

select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

and鍜宱r鍚屾椂鍑虹幇鐨勮瘽锛屾湁浼樺厛绾ч棶棰樺悧锛燂紙寮€鍙戜腑濡備笉璁板緱浼樺厛绾э紝鍔犲皬鎷彿锛夋煡璇㈠伐璧勫ぇ浜?500锛屽苟涓旈儴闂ㄧ紪鍙蜂负10鎴?0閮ㄩ棬鐨勫憳宸ワ紵 select * from emp where sal > 2500 and deptno = 10 or deptno = 20; 鍒嗘瀽浠ヤ笂璇彞鐨勯棶棰橈紵 and浼樺厛绾ф瘮or楂樸€ 浠ヤ笂璇彞浼氬厛鎵цand锛岀劧鍚庢墽琛宱r銆 浠ヤ笂杩欎釜璇彞琛ㄧず浠€涔堝惈涔夛紵 鎵惧嚭宸ヨ祫澶т簬2500骞朵笖閮ㄩ棬缂栧彿涓?0鐨勫憳宸ワ紝鎴栬€?0閮ㄩ棬鎵€鏈夊憳宸ユ壘鍑烘潵銆 姝ｇ‘鍋氭硶锛歴elect * from emp where sal > 2500 and (deptno = 10 or deptno = 20);

in 鍖呭惈锛岀浉褰撲簬澶氫釜 or 锛坣ot in 涓嶅湪杩欎釜鑼冨洿涓級鏌ヨ宸ヤ綔宀椾綅鏄疢ANAGER鍜孲ALESMAN鐨勫憳宸ワ紵 select empno,ename,job from emp where job = 'MANAGER' or job = 'SALESMAN'; select empno,ename,job from emp where job in('MANAGER', 'SALESMAN');

娉ㄦ剰锛歩n涓嶆槸涓€涓尯闂淬€俰n鍚庨潰璺熺殑鏄叿浣撶殑鍊笺€侟/h1>

鏌ヨ钖祫鏄?00鍜?000鐨勫憳宸ヤ俊鎭紵 select ename,sal from emp where sal = 800 or sal = 5000; select ename,sal from emp where sal in(800, 5000); //杩欎釜涓嶆槸琛ㄧず800鍒?000閮芥壘鍑烘潵銆

not in 琛ㄧず涓嶅湪杩欏嚑涓€煎綋涓殑鏁版嵁銆俿elect ename,sal from emp where sal not in(800, 5000, 3000);

not 鍙互鍙栭潪锛屼富瑕佺敤鍦 is 鎴 in 涓 is null is not null in not in

like 绉颁负妯＄硦鏌ヨ锛屾敮鎸?鎴栦笅鍒掔嚎鍖归厤 %鍖归厤浠绘剰澶氫釜瀛楃 涓嬪垝绾匡細浠绘剰涓€涓瓧绗︺€ 锛?鏄竴涓壒娈婄殑绗﹀彿锛宊 涔熸槸涓€涓壒娈婄鍙凤級

鎵惧嚭鍚嶅瓧涓惈鏈塐鐨勶紵 mysql> select ename from emp where ename like '%O%';

鎵惧嚭鍚嶅瓧浠缁撳熬鐨勶紵 select ename from emp where ename like '%T';

鎵惧嚭鍚嶅瓧浠寮€濮嬬殑锛 select ename from emp where ename like 'K%';

鎵惧嚭绗簩涓瓧姣忔槸A鐨勶紵 select ename from emp where ename like '_A%';

鎵惧嚭绗笁涓瓧姣嶆槸R鐨勶紵 select ename from emp where ename like '__R%';

鎵惧嚭鍚嶅瓧涓湁鈥淿鈥濈殑锛 select name from t_student where name like '%_%'; //杩欐牱涓嶈銆

姝ｇ‘锛 select name from t_student where name like '%\_%'; // \杞箟瀛楃銆

瀵规煡璇㈢粨鏋滄帓搴廃/h1>

鎺掑簭 order by锛 榛樿鏄崌搴忥紒锛侊紒锛堻/h1>

鏌ヨ鎵€鏈夊憳宸ヨ柂璧勶紝鎺掑簭锛 select ename,sal from emp order by sal;

鎬庝箞闄嶅簭锛堝悗闈㈠姞涓奷esc锛夛紵

鎸囧畾闄嶅簭锛 select ename,sal from emp order by sal desc;

鍙互涓や釜瀛楁鎺掑簭鍚楋紵鎴栬€呰鎸夌収澶氫釜瀛楁鎺掑簭锛 鏌ヨ鍛樺伐鍚嶅瓧鍜岃柂璧勶紝瑕佹眰鎸夌収钖祫鍗囧簭锛屽鏋滆柂璧勪竴鏍风殑璇濓紝 鍐嶆寜鐓у悕瀛楀崌搴忔帓鍒椼€ select ename,sal from emp order by sal desc, ename asc; // sal鍦ㄥ墠锛岃捣涓诲锛屽彧鏈塻al鐩哥瓑鐨勬椂鍊欙紝鎵嶄細鑰冭檻鍚敤ename鎺掑簭銆

浜嗚В鍐呭锛氭牴鎹瓧娈电殑浣嶇疆涔熷彲浠ユ帓搴 select ename,sal from emp order by 2; // 2琛ㄧず绗簩鍒椼€傜浜屽垪鏄痵al 鎸夌収鏌ヨ缁撴灉鐨勭2鍒梥al鎺掑簭銆

浜嗚В涓€涓嬶紝涓嶅缓璁湪寮€鍙戜腑杩欐牱鍐欙紝鍥犱负涓嶅仴澹€ 鍥犱负鍒楃殑椤哄簭寰堝鏄撳彂鐢熸敼鍙橈紝鍒楅『搴忎慨鏀逛箣鍚庯紝2灏卞簾浜咟/h1>

缁煎悎涓€鐐圭殑妗堜緥锛 鎵惧嚭宸ヨ祫鍦?250鍒?000涔嬮棿鐨勫憳宸ヤ俊鎭紝瑕佹眰鎸夌収钖祫闄嶅簭鎺掑垪銆 select ename,sal from emp where sal between 1250 and 3000 order by sal desc;

浠ヤ笂璇彞鐨勬墽琛岄『搴忓繀椤绘帉鎻★細 绗竴姝ワ細from 绗簩姝ワ細where 绗笁姝ワ細select 绗洓姝ワ細order by

鏁版嵁澶勭悊鍑芥暟

鏁版嵁澶勭悊鍑芥暟鍙堣绉颁负鍗曡澶勭悊鍑芥暟

鍗曡澶勭悊鍑芥暟鐨勭壒鐐癸細涓€涓緭鍏ュ搴斾竴涓緭鍑恒€

鍜屽崟琛屽鐞嗗嚱鏁扮浉瀵圭殑鏄細澶氳澶勭悊鍑芥暟銆傦紙澶氳澶勭悊鍑芥暟鐗圭偣锛氬涓緭鍏ワ紝瀵瑰簲1涓緭鍑猴紒锛

甯歌鐨勫崟琛屽鐞嗚鏁°

lower 杞崲灏忓啓select lower(ename) as ename from emp;

+--------+
| ename  |
+--------+
| smith  |
| allen  |
| ward   |
| jones  |
| martin |
| blake  |
| clark  |
| scott  |
| king   |
| turner |
| adams  |
| james  |
| ford   |
| miller |
+--------+           

14涓緭鍏ワ紝鏈€鍚庤繕鏄?4涓緭鍑恒€傝繖鏄崟琛屽鐞嗗嚱鏁扮殑鐗圭偣銆侟/h1>

upper 杞崲澶у啓select upper(name) as name from t_student;

substr 鍙栧瓙涓诧紙substr( 琚埅鍙栫殑瀛楃涓? 璧峰涓嬫爣,鎴彇鐨勯暱搴?锛塻elect substr(ename, 1, 1) as ename from emp; 娉ㄦ剰锛氳捣濮嬩笅鏍囦粠1寮€濮嬶紝娌℃湁0.

LEFT (string2 ,length )浠 string2 涓殑宸﹁竟璧峰彇 length 涓瓧绗

RIGHT (string2 ,length ) 浠 string2 涓殑鍙宠竟璧峰彇 length 涓瓧绗

鎵惧嚭鍛樺伐鍚嶅瓧绗竴涓瓧姣嶆槸A鐨勫憳宸ヤ俊鎭紵 绗竴绉嶆柟寮忥細妯＄硦鏌ヨ select ename from emp where ename like 'A%'; 绗簩绉嶆柟寮忥細substr鍑芥暟 select ename from emp where substr(ename,1,1) = 'A';

瀛︾敓鍚嶅瓧棣栧瓧姣嶅ぇ鍐欙紵

select concat(upper(substr(name,1,1)),substr(name,2,length(name) - 1)) as result from t_student;

娉ㄦ剰锛氬瓧绗︿覆鎷兼帴涓€瀹氳鐢╟oncat鑰屼笉鑳界敤+

鍍忚繖鏍蜂笉琛岋細select upper(substr(name,1,1)) + substr(name,2,length(name) - 1) from t_student;

concat鍑芥暟杩涜瀛楃涓茬殑鎷兼帴select concat(empno,ename) from emp;

鍙互鎷兼帴澶氫釜concat(鈥︹€︼紝鈥︹€︼紝鈥︹€?

length 鍙栭暱搴elect length(ename) enamelength from emp;

trim 鍘讳袱杈圭┖鏍稽/h1>

select * from emp where ename = trim(' KING');

RTrim鍒囬櫎鍙宠竟鐨勭┖鏍硷紝LTrim鍒囬櫎宸﹁竟鐨勭┖鏍稽/h1>

str_to_date 灏嗗瓧绗︿覆杞崲鎴愭棩鏈 date_format 鏍煎紡鍖栨棩鏈 format 璁剧疆鍗冨垎浣

锛堝悗闈㈣锛

涓€涓寮傜殑鐜拌薄

select 'abc' as bieming from emp; // select鍚庨潰鐩存帴璺熲€滃瓧闈㈤噺/瀛楅潰鍊尖€

+---------+
        | bieming |
        +---------+
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        | abc     |
        +---------+           

select abc from emp; ERROR 1054 (42S22): Unknown column 'abc' in 'field list' 杩欐牱鑲畾鎶ラ敊锛屽洜涓轰細鎶奱bc褰撳仛涓€涓瓧娈电殑鍚嶅瓧锛屽幓emp琛ㄤ腑鎵綼bc瀛楁鍘讳簡銆

缁撹锛歴elect鍚庨潰鍙互璺熸煇涓〃鐨勫瓧娈靛悕锛堝彲浠ョ瓑鍚岀湅鍋氬彉閲忓悕锛夛紝涔熷彲浠ヨ窡瀛楅潰閲?瀛楅潰鍊硷紙鏁版嵁锛夈€俿elect 21000 as num from dept;// 21000涔熸槸琚綋鍋氫竴涓瓧闈㈤噺/瀛楅潰鍊笺€

+-------+
    | num   |
    +-------+
    | 21000 |
    | 21000 |
    | 21000 |
    | 21000 |
    +-------+           

round(瑕佹搷浣滅殑鏁帮紝 淇濈暀鍑犱綅灏忔暟) 鍥涜垗浜斿叆

select round(21000.4, 0) as num from dept; //缁撴灉鍜屼笂闈竴鏍

select round(1236.567, -1); // 淇濈暀鍒板崄浣嶃€侟/h1>

rand() 鐢熸垚闅忔満鏁 鍦 0 鈮 闅忔満鏁 鈮 1.0 select floor(rand()*100); // 100浠ュ唴鐨勯殢鏈烘暟

濡傛灉浣跨敤 rand(seed) 杩斿洖闅忔満鏁? 鑼冨洿 0 鈮 闅忔満鏁 鈮 1.0, 濡傛灉 seed 涓嶅彉锛屽娆℃墽琛岃繑鍥炵殑缁撴灉涔熸槸涓嶅彉鐨

ifnull鏄┖澶勭悊鍑芥暟銆備笓闂ㄥ鐞嗙┖鐨勶紝鍙互灏 null 杞崲鎴愪竴涓叿浣撳€

鍦ㄦ墍鏈夌殑鏁版嵁搴撳綋涓紝鍙鏈塶ull鍙備笌浜嗘暟瀛﹁繍绠楋紝鏈€缁堢粨鏋滃氨鏄疦ULL

璁＄畻姣忎釜鍛樺伐鐨勫勾钖紵

select ename, (sal + ifnull(comm, 0))*12 year_sal from emp;

case..when..then..when..then..else..end 褰撳憳宸ョ殑宸ヤ綔宀椾綅鏄疢ANAGER鐨勬椂鍊欙紝宸ヨ祫涓婅皟10%锛屽綋宸ヤ綔宀椾綅鏄疭ALESMAN鐨勬椂鍊欙紝宸ヨ祫涓婅皟50%,鍏跺畠姝ｅ父銆 锛堟敞鎰忥細涓嶄慨鏀规暟鎹簱锛屽彧鏄皢鏌ヨ缁撴灉鏄剧ず涓哄伐璧勪笂璋冿級 select ename, job, sal as oldsal, (case job when 'MANAGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newsal from emp;

select 鍚庡彲浠ヤ笉鎺rom 褰撲綔绠€鍗曠殑娴嬭瘯宸ュ叿

绛夊悓浜庢帴浜唂rom dual;

dual涓 浜氬厓琛? 绯荤粺琛 鍙互浣滀负娴嬭瘯琛ㄤ娇鐢

select round(3.14, 3);

+----------------+
| round(3.14, 3) |
+----------------+
|          3.140 |
+----------------+           

select concat('13','57','9');

+-----------------------+
| concat('13','57','9') |
+-----------------------+
| 13579                 |
+-----------------------+           

澶氳澶勭悊鍑芥暟

浜斾釜鍒嗙粍鍑芥暟

澶氳澶勭悊鍑芥暟涔熷彨鍒嗙粍鍑芥暟

澶氳澶勭悊鍑芥暟鐨勭壒鐐癸細杈撳叆澶氳锛屾渶缁堣緭鍑轰竴琛屻€

5涓細 count 璁℃暟 sum 姹傚拰 avg 骞冲潎鍊 max 鏈€澶у€ min 鏈€灏忓€

娉ㄦ剰锛 鍒嗙粍鍑芥暟鍦ㄤ娇鐢ㄧ殑鏃跺€欏繀椤诲厛杩涜鍒嗙粍锛岀劧鍚庢墠鑳界敤銆 濡傛灉浣犳病鏈夊鏁版嵁杩涜鍒嗙粍锛屾暣寮犺〃榛樿涓轰竴缁勩€侟/h1>

鎵惧嚭鏈€楂樺伐璧勶紵 mysql> select max(sal) from emp;

鎵惧嚭鏈€浣庡伐璧勶紵 mysql> select min(sal) from emp;

璁＄畻宸ヨ祫鍜岋細 mysql> select sum(sal) from emp;

璁＄畻骞冲潎宸ヨ祫锛 mysql> select avg(sal) from emp;

璁＄畻鍛樺伐鏁伴噺锛 mysql> select count(ename) from emp;

璁＄畻鍛樺伐鏁伴噺锛 mysql> select count(ename) from emp;

娉ㄦ剰锛氬鏋渃ount涓～鐨勬槸琛ㄨ揪寮忕殑璇濓紝瑕佸姞涓 or null锛 鍥犱负 Mysql 涓 count() 鍑芥暟鐨勪竴鑸敤娉曟槸缁熻瀛楁闈炵┖鐨勮褰曟暟锛屽埄鐢ㄨ繖涓壒鐐规潵杩涜鏉′欢缁熻锛屽鏋滃瓧娈垫槸 NULL 灏变笉浼氱粺璁★紝浣嗘槸 false 鏄細琚粺璁″埌鐨凕/h1>

鍒嗙粍鍑芥暟鍦ㄤ娇鐢ㄧ殑鏃跺€欓渶瑕佹敞鎰忓摢浜涳紵

鍒嗙粍鍑芥暟鑷姩蹇界暐NULL锛屼綘涓嶉渶瑕佹彁鍓嶅NULL杩涜澶勭悊銆侟/h1>

mysql> select sum(comm) from emp;

+-----------+
    | sum(comm) |
    +-----------+
    |   2200.00 |
    +-----------+           

鍒嗙粍鍑芥暟涓璫ount(*) 鍜 count(鍏蜂綋瀛楁) 鏈変粈涔堝尯鍒紵

count(鍏蜂綋瀛楁)锛氳〃绀虹粺璁¤瀛楁涓嬫墍鏈変笉涓篘ULL鐨勫厓绱犵殑鎬绘暟銆 count(*)锛氱粺璁¤〃褰撲腑鐨勬€昏鏁般€傦紙鍙鏈変竴琛屾暟鎹甤ount鍒?+锛 姣忎竴琛岃褰曚笉鍙兘閮戒负NULL(涓嶅瓨鍦ㄤ竴琛屽叏涓簄ull鐨勮〃)锛屼竴琛屾暟鎹腑鏈変竴鍒椾笉涓篘ULL锛屽垯杩欒鏁版嵁灏辨槸鏈夋晥鐨勩€

mysql> select count(*) from emp;
+----------+
| count(*) |
+----------+
|       14 |
+----------+
鈥
mysql> select count(comm) from emp;
+-------------+
| count(comm) |
+-------------+
|           4 |
+-------------+           

鍒嗙粍鍑芥暟涓嶈兘鐩存帴浣跨敤鍦╳here瀛愬彞涓­

鎵惧嚭姣旀渶浣庡伐璧勯珮鐨勫憳宸ヤ俊鎭€ select ename,sal from emp where sal > min(sal); 琛ㄩ潰涓婃剰鎬濇劅瑙夋槸娌￠棶棰橈紝杩愯涓€涓嬪彂鐜帮細 ERROR 1111 (HY000): Invalid use of group function ????????????????????????????????????????????????????????????????????? 鍒嗙粍鍑芥暟鍦ㄤ娇鐢ㄧ殑鏃跺€欏繀椤诲厛杩涜鍒嗙粍锛岃€屽垎缁勭殑group by 鎵ц椤哄簭鏄湪where鍚庣殑锛屼篃灏辨槸璁╁垎缁勫嚱鏁 min 鍏堜簬鍒嗙粍浜嗭紝鎵€浠ヤ細鎶ラ敊

鎵€鏈夌殑鍒嗙粍鍑芥暟鍙互缁勫悎璧锋潵涓€璧风敤

select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;

+----------+----------+----------+-------------+----------+
| sum(sal) | min(sal) | max(sal) | avg(sal)    | count(*) |
+----------+----------+----------+-------------+----------+
| 29025.00 |   800.00 |  5000.00 | 2073.214286 |       14 |
+----------+----------+----------+-------------+----------+           

鍒嗙粍鏌ヨ

鍦ㄥ疄闄呯殑搴旂敤涓紝鍙兘鏈夎繖鏍风殑闇€姹傦紝 闇€瑕佸厛杩涜鍒嗙粍锛岀劧鍚庡姣忎竴缁勭殑鏁版嵁杩涜鎿嶄綔銆 杩欎釜鏃跺€欐垜浠渶瑕佷娇鐢ㄥ垎缁勬煡璇紝鎬庝箞杩涜鍒嗙粍鏌ヨ鍛紵 select ... from ... group by ... 璁＄畻姣忎釜閮ㄩ棬鐨勫伐璧勫拰锛 璁＄畻姣忎釜宸ヤ綔宀椾綅鐨勫钩鍧囪柂璧勶紵 鎵惧嚭姣忎釜宸ヤ綔宀椾綅鐨勬渶楂樿柂璧勶紵 ....

灏嗕箣鍓嶇殑鍏抽敭瀛楀叏閮ㄧ粍鍚堝湪涓€璧凤紝浠栦滑鐨勬墽琛岄『搴忥紵 select ... from ... where ... group by ... order by ...

浠ヤ笂鍏抽敭瀛楃殑椤哄簭涓嶈兘棰犲€掞紝闇€瑕佽浣忋€傛墽琛岄『搴忔槸浠€涔堬紵 1.from 2.where 3.group by 4.select 5.order by

涓轰粈涔堝垎缁勫嚱鏁颁笉鑳界洿鎺ヤ娇鐢ㄥ湪where鍚庨潰锛 select ename,sal from emp where sal > min(sal);//鎶ラ敊銆 鍥犱负鍒嗙粍鍑芥暟鍦ㄤ娇鐢ㄧ殑鏃跺€欏繀椤诲厛鍒嗙粍涔嬪悗鎵嶈兘浣跨敤銆 where鎵ц鐨勬椂鍊欙紝杩樻病鏈夊垎缁勩€傛墍浠here鍚庨潰涓嶈兘鍑虹幇鍒嗙粍鍑芥暟銆

select sum(sal) from emp; 鈥 杩欎釜娌℃湁鍒嗙粍锛屼负鍟um()鍑芥暟鍙互鐢ㄥ憿锛 鈥 鍥犱负select鍦╣roup by涔嬪悗鎵ц銆

鎵惧嚭姣忎釜宸ヤ綔宀椾綅鐨勫伐璧勫拰锛 瀹炵幇鎬濊矾锛氭寜鐓у伐浣滃矖浣嶅垎缁勶紝鐒跺悗瀵瑰伐璧勬眰鍜屻€ select job,sum(sal) from emp group by job;

+-----------+----------+
| job       | sum(sal) |
+-----------+----------+
| ANALYST   |  6000.00 |
| CLERK     |  4150.00 |
| MANAGER   |  8275.00 |
| PRESIDENT |  5000.00 |
| SALESMAN  |  5600.00 |
+-----------+----------+
浠ヤ笂杩欎釜璇彞鐨勬墽琛岄『搴忥紵
鍏堜粠emp琛ㄤ腑鏌ヨ鏁版嵁銆
鏍规嵁job瀛楁杩涜鍒嗙粍銆
鐒跺悗瀵规瘡涓€缁勭殑鏁版嵁杩涜sum(sal)           

閲嶇偣缁撹锛 鍦ㄤ竴鏉elect璇彞褰撲腑锛屽鏋滄湁group by璇彞鐨勮瘽锛 select鍚庨潰鍙兘璺燂細鍙傚姞鍒嗙粍鐨勫瓧娈碉紝浠ュ強鍒嗙粍鍑芥暟銆 鍏跺畠鐨勪竴寰嬩笉鑳借窡銆侟/h1>

鎵惧嚭鈥滄瘡涓儴闂紝涓嶅悓宸ヤ綔宀椾綅鈥濈殑鏈€楂樿柂璧勶紵

涓や釜瀛楁鑱斿悎鍒嗙粍select deptno, job, max(sal) from emp group by deptno, job;

浣跨敤having瀵瑰垎缁勫悗鐨勬暟鎹繘涓€姝ヨ繃婊｜/h1>

having涓嶈兘鍗曠嫭浣跨敤锛宧aving涓嶈兘浠ｆ浛where锛宧aving蹇呴』鍜実roup by鑱斿悎浣跨敤銆

鎵惧嚭姣忎釜閮ㄩ棬鏈€楂樿柂璧勶紝瑕佹眰鏄剧ず鏈€楂樿柂璧勫ぇ浜?000鐨勶紵

select deptno, max(sal) from emp group by deptno having max(sal) > 3000;

鍗曡〃鏌ヨ澶ф€荤粨

select 鈥 ... 鈥 from 鈥 ... 鈥 where 鈥 ... 鈥 group by 鈥 ... 鈥 having 鈥 ... 鈥 order by 鈥 ...

鎵ц椤哄簭锛

1.from

2.where

3.group by

4.having

5.select

6.order by

鎵惧嚭闄ANAGER宀椾綅涔嬪锛屾瘡涓矖浣嶇殑骞冲潎钖祫锛岃姹傛樉绀哄钩鍧囪柂璧勫ぇ浜?500鐨勶紝 瑕佹眰鎸夌収骞冲潎钖祫闄嶅簭鎺掋€

select job, avg(sal) from emp where job != 'MANAGER' group by job having avg(sal) > 1500 order by avg(sal);

distinct 鍏抽敭瀛桙/h1>

鎶婃煡璇㈢粨鏋滃幓闄ら噸澶嶈褰曘€恉istinct銆 娉ㄦ剰锛氬師琛ㄦ暟鎹笉浼氳淇敼锛屽彧鏄煡璇㈢粨鏋滃幓閲嶃€ 鍘婚噸闇€瑕佷娇鐢ㄤ竴涓叧閿瓧锛歞istinct

distinct鍙兘鍑虹幇鍦ㄦ墍鏈夊瓧娈电殑鏈€鍓嶆柟锛岃〃绀哄鍚勫瓧娈佃仈鍚堣捣鏉ョ殑鏌ヨ缁撴灉鍘婚噸

杩欐牱缂栧啓鏄敊璇殑锛岃娉曢敊璇€

select ename,distinct job from emp;

缁熻涓€涓嬪伐浣滃矖浣嶇殑鏁伴噺锛焥elect count(distinct job) from emp;

杩炴帴鏌ヨ

浠€涔堟槸杩炴帴鏌ヨ锛烖/h1>

浠庝竴寮犺〃涓崟鐙煡璇紝绉颁负鍗曡〃鏌ヨ銆 鈥 澶氬紶琛ㄨ仈鍚堣捣鏉ユ煡璇㈡暟鎹紝琚О涓鸿繛鎺ユ煡璇€

鏍规嵁璇硶鐨勫勾浠ｅ垎绫伙細 SQL92锛?992骞寸殑鏃跺€欏嚭鐜扮殑璇硶 SQL99锛?999骞寸殑鏃跺€欏嚭鐜扮殑璇硶 鎴戜滑杩欓噷閲嶇偣瀛︿範SQL99.(杩欎釜杩囩▼涓畝鍗曟紨绀轰竴涓猄QL92鐨勪緥瀛?

鏍规嵁琛ㄨ繛鎺ョ殑鏂瑰紡鍒嗙被锛 鍐呰繛鎺ワ細 绛夊€艰繛鎺 闈炵瓑鍊艰繛鎺 鑷繛鎺

澶栬繛鎺ワ細 鈥 宸﹀杩炴帴锛堝乏杩炴帴锛 鈥 鍙冲杩炴帴锛堝彸杩炴帴锛

浠€涔堟槸绗涘崱灏旂Н鐜拌薄

褰撲袱寮犺〃杩涜杩炴帴鏌ヨ锛屾病鏈変换浣曟潯浠堕檺鍒剁殑鏃跺€欙紝鏈€缁堟煡璇㈢粨鏋滄潯鏁帮紝鏄 涓ゅ紶琛ㄦ潯鏁扮殑涔樼Н锛岃繖绉嶇幇璞¤绉颁负锛氱瑳鍗″皵绉幇璞°€傦紙绗涘崱灏斿彂鐜扮殑锛岃繖鏄竴涓暟瀛︾幇璞°€傦級

鎬庝箞閬垮厤绗涘崱灏旂Н鐜拌薄

杩炴帴鏃跺姞鏉′欢锛屾弧瓒宠繖涓潯浠剁殑璁板綍琚瓫閫夊嚭鏉ワ紒

琛ㄨ捣鍒悕銆傚緢閲嶈銆傛晥鐜囬棶棰樸€俿elect e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

娉ㄦ剰锛氶€氳繃绗涘崱灏旂Н鐜拌薄寰楀嚭锛岃〃鐨勮繛鎺ユ鏁拌秺澶氭晥鐜囪秺浣庯紝灏介噺鍑忓皯琛ㄧ殑杩炴帴娆℃暟銆侟/h1>

鍐呰繛鎺¥

涓ゅ紶琛ㄨ繛鎺ユ椂娌℃湁涓绘鍏崇郴鐨勮繛鎺ョО涓哄唴杩炴帴

绛夊€艰繛鎺¥

妗堜緥锛氭煡璇㈡瘡涓憳宸ユ墍鍦ㄩ儴闂ㄥ悕绉帮紝鏄剧ず鍛樺伐鍚嶅拰閮ㄩ棬鍚嶏紵 emp e鍜宒ept d琛ㄨ繘琛岃繛鎺ャ€傛潯浠舵槸锛歟.deptno = d.deptno

SQL92璇硶锛 select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;

sql92鐨勭己鐐癸細缁撴瀯涓嶆竻鏅帮紝琛ㄧ殑杩炴帴鏉′欢锛屽拰鍚庢湡杩涗竴姝ョ瓫閫夌殑鏉′欢锛岄兘鏀惧埌浜唚here鍚庨潰銆

SQL99璇硶锛 inner 鍙互鐪佺暐锛屼笉鐪佺暐鍙鎬т細濂戒竴浜 select e.ename,d.dname from emp e inner join dept d on e.deptno = d.deptno;

sql99浼樼偣锛氳〃杩炴帴鐨勬潯浠舵槸鐙珛鐨勶紝杩炴帴涔嬪悗锛屽鏋滆繕闇€瑕佽繘涓€姝ョ瓫閫夛紝鍐嶅線鍚庣户缁坊鍔爓here

闈炵瓑鍊艰繛鎺¥

妗堜緥锛氭壘鍑烘瘡涓憳宸ョ殑钖祫绛夌骇锛岃姹傛樉绀哄憳宸ュ悕銆佽柂璧勩€佽柂璧勭瓑绾э紵

select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

鑷繛鎺¥

鍐呰繛鎺ヤ箣鑷繛鎺 妗堜緥锛氭煡璇㈠憳宸ョ殑涓婄骇棰嗗锛岃姹傛樉绀哄憳宸ュ悕鍜屽搴旂殑棰嗗鍚嶏紵

emp a 鍛樺伐琛ㄣ€乪mp b 棰嗗琛

select a.ename 鍛樺伐, b.ename 棰嗗 from emp a join emp b on a.mgr = b.empno;

澶栬繛鎺¥

涓ゅ紶琛ㄨ繛鎺ユ椂鏈変富娆″叧绯

鍙?澶?杩炴帴

// outer鏄彲浠ョ渷鐣ョ殑锛屽甫鐫€鍙鎬у己銆 select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno;

right浠ｈ〃浠€涔堬細琛ㄧず灏唈oin鍏抽敭瀛楀彸杈圭殑杩欏紶琛ㄧ湅鎴愪富琛紝涓昏鏄负浜嗗皢 杩欎富琛ㄧ殑鏁版嵁鍏ㄩ儴鏌ヨ鍑烘潵锛屾崕甯︾潃鍏宠仈鏌ヨ宸﹁竟鐨勮〃銆?濡傛灉姝よ〃娌℃湁瀵瑰簲鍜屼富琛ㄦ弧瓒 on 鍚庢潯浠剁殑 ,閭ｆ琛ㄤ細鑷姩琛ュ厖 NULL ) 鍦ㄥ杩炴帴褰撲腑锛屼袱寮犺〃杩炴帴锛屼骇鐢熶簡涓绘鍏崇郴銆

宸?澶?杩炴帴

宸﹀杩炴帴鍚岀悊

浠讳綍涓€涓彸杩炴帴閮芥湁宸﹁繛鎺ョ殑鍐欐硶銆 浠讳綍涓€涓乏杩炴帴閮芥湁鍙宠繛鎺ョ殑鍐欐硶銆侟/h1>

妗堜緥锛氭煡璇㈡瘡涓憳宸ョ殑涓婄骇棰嗗锛岃姹傛樉绀烘墍鏈夊憳宸ョ殑鍚嶅瓧鍜岄瀵煎悕锛

select a.ename '鍛樺伐', b.ename '棰嗗' from emp a left join emp b on a.mgr = b.empno;

涓夊紶琛ㄨ繛鎺ュ涓?鍥涘紶琛ㄥ悓鐞?

璇硶锛 select ... from a join b on a鍜宐鐨勮繛鎺ユ潯浠 join c on a鍜宑鐨勮繛鎺ユ潯浠

妗堜緥锛氭壘鍑烘瘡涓憳宸ョ殑閮ㄩ棬鍚嶇О浠ュ強宸ヨ祫绛夌骇锛 瑕佹眰鏄剧ず鍛樺伐鍚嶃€侀儴闂ㄥ悕銆佽柂璧勩€佽柂璧勭瓑绾э紵

select e.ename, d.dname, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

涓€鏉QL涓唴杩炴帴鍜屽杩炴帴鍙互娣峰悎銆傞兘鍙互鍑虹幇锛

妗堜緥锛氭壘鍑烘瘡涓憳宸ョ殑閮ㄩ棬鍚嶇О浠ュ強宸ヨ祫绛夌骇锛岃繕鏈変笂绾ч瀵硷紝 瑕佹眰鏄剧ず鍛樺伐鍚嶃€侀瀵煎悕銆侀儴闂ㄥ悕銆佽柂璧勩€佽柂璧勭瓑绾э紵

select e.ename '鍛樺伐', u.ename '棰嗗', d.dname, e.sal, s.grade from emp e left join emp u on e.mgr = u.empno join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

浠€涔堟槸瀛愭煡璇紵

select璇彞涓祵濂梥elect璇彞锛岃宓屽鐨剆elect璇彞绉颁负瀛愭煡璇€

瀛愭煡璇㈤兘鍙互鍑虹幇鍦ㄥ摢閲屽憿锛烖/h1>

select 鈥 ..(select). 鈥 from 鈥 ..(select). 鈥 where 鈥 ..(select).

where瀛愬彞涓殑瀛愭煡璇↑/h1>

妗堜緥锛氭壘鍑烘瘮鏈€浣庡伐璧勯珮100鐨勫憳宸ュ鍚嶅拰宸ヨ祫锛

select 鈥 ename,sal 鈥 from 鈥 emp 鈥 where 鈥 sal > min(sal);

ERROR 1111 (HY000): Invalid use of group function 鈥 where瀛愬彞涓笉鑳界洿鎺ヤ娇鐢ㄥ垎缁勫嚱鏁般€

姝ｇ‘锛歴elect e.ename, e.sal from emp e where e.sal > (select min(sal)+1000 from emp);

from瀛愬彞涓殑瀛愭煡璇↑/h1>

妗堜緥锛氭壘鍑烘瘡涓矖浣嶇殑骞冲潎宸ヨ祫鐨勮柂璧勭瓑绾с€

select a.*, s.grade from (select job, avg(sal) avg_sal from emp group by job) a join salgrade s on a.avg_sal between s.losal and s.hisal;

瀛愭煡璇腑鐨刟vg(sal)涓€瀹氳璧蜂釜鍒悕鎵嶈兘鍦ㄥ唴杩炴帴涓娇鐢¨

select鍚庨潰鍑虹幇鐨勫瓙鏌ヨ(浜嗚В鍗冲彲)

妗堜緥锛氭壘鍑烘瘡涓憳宸ョ殑閮ㄩ棬鍚嶇О锛岃姹傛樉绀哄憳宸ュ悕锛岄儴闂ㄥ悕锛 select e.ename,e.deptno,(select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;

select e.ename,e.deptno,(select dname from dept) as dname from emp e; //閿欒锛欵RROR 1242 (21000): Subquery returns more than 1 row

娉ㄦ剰锛氬浜巗elect鍚庨潰鐨勫瓙鏌ヨ鏉ヨ锛岃繖涓瓙鏌ヨ鍙兘涓€娆¤繑鍥?鏉＄粨鏋滐紝 澶氫簬1鏉★紝灏辨姤閿欎簡銆傦紒

Union鍏抽敭瀛桙/h1>

union浣滅敤锛氬悎骞舵煡璇㈢粨鏋滈泦

妗堜緥锛氭煡璇㈠伐浣滃矖浣嶆槸MANAGER鍜孲ALESMAN鐨勫憳宸ワ紵

select ename,job from emp where job = 'MANAGER' or job = 'SALESMAN';

select ename,job from emp where job in('MANAGER','SALESMAN');

select ename,job from emp where job = 'MANAGER' union select ename,job from emp where job = 'SALESMAN';

鍍忎笅闈㈣繖鏍锋槸閿欒鐨勶細union鍦ㄨ繘琛岀粨鏋滈泦鍚堝苟鐨勬椂鍊欙紝瑕佹眰涓や釜缁撴灉闆嗙殑鍒楁暟鐩稿悓銆 select ename,job from emp where job = 'MANAGER' union select ename from emp where job = 'SALESMAN';

// MYSQL鍙互缁撴灉闆嗗悎骞舵椂鍒楀拰鍒楃殑鏁版嵁绫诲瀷涓嶄竴鑷 锛宱racle璇硶涓ユ牸 锛屼笉鍙互锛屽叾缁撴灉闆嗗悎骞舵椂鍒楀拰鍒楃殑鏁版嵁绫诲瀷涔熻涓€鑷淬€ select ename,job from emp where job = 'MANAGER' union select ename,sal from emp where job = 'SALESMAN';

limit鍏抽敭瀛桙/h1>

limit浣滅敤锛氬皢鏌ヨ缁撴灉闆嗙殑涓€閮ㄥ垎鍙栧嚭鏉ャ€傞€氬父浣跨敤鍦ㄥ垎椤垫煡璇㈠綋涓€ 鐧惧害榛樿锛氫竴椤垫樉绀?0鏉¤褰曘€ 鍒嗛〉鐨勪綔鐢ㄦ槸涓轰簡鎻愰珮鐢ㄦ埛鐨勪綋楠岋紝鍥犱负涓€娆″叏閮ㄩ兘鏌ュ嚭鏉ワ紝鐢ㄦ埛浣撻獙宸€ 鍙互涓€椤典竴椤电炕椤电湅銆

瀹屾暣鐢ㄦ硶锛歭imit startIndex, length startIndex鏄捣濮嬩笅鏍囷紝length鏄暱搴︺€ limit璧峰涓嬫爣浠?寮€濮嬨€俿ubstr鐨勮捣濮嬩笅鏍囦粠1寮€濮

缂虹渷鐢ㄦ硶锛歭imit 5; 杩欐槸鍙栧墠5.

select ename,sal from emp order by sal desc limit 0,5;

娉ㄦ剰锛歮ysql褰撲腑limit鍦╫rder by涔嬪悗鎵ц锛侊紒锛侊紒锛侊紒

鍙栧嚭宸ヨ祫鎺掑悕鍦╗3-5]鍚嶇殑鍛樺伐锛 select ename,sal from emp order by sal desc limit 2, 3; 2琛ㄧず璧峰浣嶇疆浠庝笅鏍?寮€濮嬶紝灏辨槸绗笁鏉¤褰曘€ 3琛ㄧず闀垮害銆

閫氱敤鍒嗛〉鏂规硶锛欬/h1>

姣忛〉鏄剧ず3鏉¤褰 绗?椤碉細limit 0,3 [0 1 2] 绗?椤碉細limit 3,3 [3 4 5] 绗?椤碉細limit 6,3 [6 7 8] 绗?椤碉細limit 9,3 [9 10 11]

姣忛〉鏄剧ずpageSize鏉¤褰 绗琾ageNo椤碉細limit (pageNo - 1) * pageSize , pageSize

DQL璇彞鎬荤粨

鍏充簬DQL璇彞鐨勫ぇ鎬荤粨锛 select ... from ... where ... group by ... having ... order by ... limit ...

鎵ц椤哄簭锛 1.from 2.where 3.group by 4.having 5.select 6.order by 7.limit..

DDL鍙婂父瑙佹暟鎹被鍨婞/h1>

create

寤鸿〃鐨勮娉曟牸寮忥細

create table 琛ㄥ悕(瀛楁鍚? 鏁版嵁绫诲瀷, 瀛楁鍚? 鏁版嵁绫诲瀷, 瀛楁鍚? 鏁版嵁绫诲瀷);

create table 琛ㄥ悕( 瀛楁鍚? 鏁版嵁绫诲瀷, 瀛楁鍚? 鏁版嵁绫诲瀷, 瀛楁鍚? 鏁版嵁绫诲瀷 );

琛ㄥ悕锛氬缓璁互t_ 鎴栬€ tbl_寮€濮嬶紝鍙鎬у己銆傝鍚嶇煡鎰忋€ 瀛楁鍚嶏細瑙佸悕鐭ユ剰銆 琛ㄥ悕鍜屽瓧娈靛悕閮藉睘浜庢爣璇嗙銆

drop

鍒犻櫎涓€涓暟鎹簱

drop database [if exists] 搴撳悕; #if exists 濡傛灉搴撳瓨鍦ㄦ墠鍒犻櫎

鍒犻櫎涓€寮犺〃锛

drop table 琛ㄥ悕;

Alter

瀵硅〃缁撴瀯鐨勪慨鏀

浠€涔堟槸瀵硅〃缁撴瀯鐨勪慨鏀癸紵 娣诲姞涓€涓瓧娈碉紝鍒犻櫎涓€涓瓧娈碉紝淇敼涓€涓瓧娈碉紒锛侊紒

绗竴锛氬湪瀹為檯鐨勫紑鍙戜腑锛岄渶姹備竴鏃︾‘瀹氫箣鍚庯紝琛ㄤ竴鏃﹁璁″ソ涔嬪悗锛屽緢灏戠殑 杩涜琛ㄧ粨鏋勭殑淇敼銆傚洜涓哄紑鍙戣繘琛屼腑鐨勬椂鍊欙紝淇敼琛ㄧ粨鏋勶紝鎴愭湰姣旇緝楂樸€ 淇敼琛ㄧ殑缁撴瀯锛屽搴旂殑java浠ｇ爜灏遍渶瑕佽繘琛屽ぇ閲忕殑淇敼銆傛垚鏈槸姣旇緝楂樼殑銆 杩欎釜璐ｄ换搴旇鐢辫璁′汉鍛樻潵鎵挎媴锛

绗簩锛氱敱浜庝慨鏀硅〃缁撴瀯鐨勬搷浣滃緢灏戯紝鎵€浠ユ垜浠笉闇€瑕佹帉鎻★紝濡傛灉鏈変竴澶 鐪熺殑瑕佷慨鏀硅〃缁撴瀯锛屼綘鍙互浣跨敤宸ュ叿锛侊紒锛侊紒

淇敼琛ㄧ粨鏋勭殑鎿嶄綔鏄笉闇€瑕佸啓鍒癹ava绋嬪簭涓殑銆傚疄闄呬笂涔熶笉鏄痡ava绋嬪簭鍛樼殑鑼冪暣銆

甯歌鏁版嵁绫诲瀷

varchar(鏈€闀?55) 鍙彉闀垮害鐨勫瓧绗︿覆 姣旇緝鏅鸿兘锛岃妭鐪佺┖闂淬€ 浼氭牴鎹疄闄呯殑鏁版嵁闀垮害鍔ㄦ€佸垎閰嶇┖闂淬€

浼樼偣锛氳妭鐪佺┖闂 鈥 缂虹偣锛氶渶瑕佸姩鎬佸垎閰嶇┖闂达紝閫熷害鎱€

char(鏈€闀?55) 瀹氶暱瀛楃涓 涓嶇瀹為檯鐨勬暟鎹暱搴︽槸澶氬皯銆 鍒嗛厤鍥哄畾闀垮害鐨勭┖闂村幓瀛樺偍鏁版嵁銆 浣跨敤涓嶆伆褰撶殑鏃跺€欙紝鍙兘浼氬鑷寸┖闂寸殑娴垂銆

浼樼偣锛氫笉闇€瑕佸姩鎬佸垎閰嶇┖闂达紝閫熷害蹇€ 鈥 缂虹偣锛氫娇鐢ㄤ笉褰撳彲鑳戒細瀵艰嚧绌洪棿鐨勬氮璐广€

varchar鍜宑har鎴戜滑搴旇鎬庝箞閫夋嫨锛 鎬у埆瀛楁浣犻€変粈涔堬紵鍥犱负鎬у埆鏄浐瀹氶暱搴︾殑瀛楃涓诧紝鎵€浠ラ€夋嫨char銆 濮撳悕瀛楁浣犻€変粈涔堬紵姣忎竴涓汉鐨勫悕瀛楅暱搴︿笉鍚岋紝鎵€浠ラ€夋嫨varchar銆

int(鏈€闀?1) 鏁板瓧涓殑鏁存暟鍨嬨€傜瓑鍚屼簬java鐨刬nt銆

bigint 鏁板瓧涓殑闀挎暣鍨嬨€傜瓑鍚屼簬java涓殑long銆

float(鏁板瓧a, 鏁板瓧b) 鍏朵腑鏁板瓧a琛ㄧず鏈夋晥鏁板瓧锛屾暟瀛梑琛ㄧず灏忔暟浣 鍗曠簿搴︽诞鐐瑰瀷鏁版嵁

double(鏁板瓧a, 鏁板瓧b) 鍏朵腑鏁板瓧a琛ㄧず鏈夋晥鏁板瓧锛屾暟瀛梑琛ㄧず灏忔暟浣 鍙岀簿搴︽诞鐐瑰瀷鏁版嵁

date 鐭棩鏈熺被鍨

datetime 闀挎棩鏈熺被鍨

clob 瀛楃澶у璞 鏈€澶氬彲浠ュ瓨鍌?G鐨勫瓧绗︿覆銆 姣斿锛氬瓨鍌ㄤ竴绡囨枃绔狅紝瀛樺偍涓€涓鏄庛€ 瓒呰繃255涓瓧绗︾殑閮借閲囩敤CLOB瀛楃澶у璞℃潵瀛樺偍銆 Character Large OBject:CLOB

blob 浜岃繘鍒跺ぇ瀵硅薄 Binary Large OBject 涓撻棬鐢ㄦ潵瀛樺偍鍥剧墖銆佸０闊炽€佽棰戠瓑娴佸獟浣撴暟鎹€ 寰€BLOB绫诲瀷鐨勫瓧娈典笂鎻掑叆鏁版嵁鐨勬椂鍊欙紝渚嬪鎻掑叆涓€涓浘鐗囥€佽棰戠瓑锛岄渶瑕佷娇鐢↖O娴佹墠琛屻€

妗堜緥锛

鍒涘缓涓€涓鐢熻〃锛 瀛﹀彿銆佸鍚嶃€佸勾榫勩€佹€у埆銆侀偖绠卞湴鍧€ create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) );

鏁版嵁绫诲瀷鍚庢嫭鍙峰唴鐨勬暟瀛楄〃绀哄唴瀹圭殑闀垮害,int(3) 琛ㄧず鏈€澶?浣嶆暟

鍒犻櫎琛細 drop table t_student; // 褰撹繖寮犺〃涓嶅瓨鍦ㄧ殑鏃跺€欎細鎶ラ敊锛

鍍忎笅闈㈣繖鏍峰垹灏变笉浼氭姤閿 drop table if exists t_student;

DML

鎻掑叆鏁版嵁insert 锛圖ML锛堻/h1>

璇硶鏍煎紡锛 insert into 琛ㄥ悕(瀛楁鍚?,瀛楁鍚?,瀛楁鍚?...) values(鍊?,鍊?,鍊?);

娉ㄦ剰锛氬瓧娈靛悕鍜屽€艰涓€涓€瀵瑰簲銆

娉ㄦ剰锛歩nsert璇彞浣嗗嚒鏄墽琛屾垚鍔熶簡锛岄偅涔堝繀鐒朵細澶氫竴鏉¤褰曘€ 娌℃湁缁欏叾瀹冨瓧娈垫寚瀹氬€肩殑璇濓紝榛樿鍊兼槸NULL銆

娉ㄦ剰锛氬瓧娈靛悕鍙互鐪佺暐锛岀渷鐣ヤ簡鐨勮瘽绛変簬閮藉啓浜嗕笖鎸夐『搴忓啓鐨

娉ㄦ剰锛氭暟鎹簱涓殑鏈変竴鏉″懡鍚嶈鑼冿細 鎵€鏈夌殑鏍囪瘑绗﹂兘鏄叏閮ㄥ皬鍐欙紝鍗曡瘝鍜屽崟璇嶄箣闂翠娇鐢ㄤ笅鍒掔嚎杩涜琛旀帴銆侟/h1>

淇敼update

璇硶鏍煎紡锛 update 琛ㄥ悕 set 瀛楁鍚?=鍊?,瀛楁鍚?=鍊?,瀛楁鍚?=鍊?... where 鏉′欢;

娉ㄦ剰锛氭病鏈夋潯浠堕檺鍒朵細瀵艰嚧鎵€鏈夋暟鎹叏閮ㄦ洿鏂般€

鍒犻櫎鏁版嵁 delete

璇硶鏍煎紡锛 鈥 delete from 琛ㄥ悕 where 鏉′欢;

娉ㄦ剰锛氭病鏈夋潯浠讹紝鏁村紶琛ㄧ殑鏁版嵁浼氬叏閮ㄥ垹闄わ紒

delete from t_user where id = 2;

delete from t_user; // 鍒犻櫎鎵€鏈夛紒

蹇€熷垱寤轰竴寮犺〃

mysql> create table emp2 as select * from emp;

鍘熺悊锛 灏嗕竴涓煡璇㈢粨鏋滃綋鍋氫竴寮犺〃鏂板缓锛岃繖涓彲浠ュ畬鎴愯〃鐨勫揩閫熷鍒躲€傝〃鍒涘缓鍑烘潵锛屽悓鏃惰〃涓殑鏁版嵁涔熷瓨鍦ㄤ簡銆

create table mytable as select empno,ename from emp where job = 'MANAGER';

灏嗘煡璇㈢粨鏋滄彃鍏ュ埌涓€寮犺〃褰撲腑锛焛nsert鐩稿叧鐨

create table dept_bak as select * from dept;

insert into dept_bak select * from dept; //寰堝皯鐢紒

蹇€熷垹闄よ〃涓暟鎹®

蹇€熷垹闄よ〃涓殑鏁版嵁锛熴€恡runcate姣旇緝閲嶈锛屽繀椤绘帉鎻°€

//鍒犻櫎dept_bak琛ㄤ腑鐨勬暟鎹 delete from dept_bak; //杩欑鍒犻櫎鏁版嵁鐨勬柟寮忔瘮杈冩參銆

mysql> select * from dept_bak; Empty set (0.00 sec)

delete璇彞鍒犻櫎鏁版嵁鐨勫師鐞嗭紵(DML鎿嶄綔) 琛ㄤ腑鐨勬暟鎹鍒犻櫎浜嗭紝浣嗘槸杩欎釜鏁版嵁鍦ㄧ‖鐩樹笂鐨勭湡瀹炲瓨鍌ㄧ┖闂翠笉浼氳閲婃斁锛侊紒锛?鐩稿綋浜庢妸琛ㄤ腑鏍煎瓙鍐呭鎿︽帀锛屼絾涓嶄細鎶婃牸瀛愬垹鎺夛紝杩樺崰浜嗙┖闂? 杩欑鍒犻櫎缂虹偣鏄細鍒犻櫎鏁堢巼姣旇緝浣 杩欑鍒犻櫎浼樼偣鏄細鏀寔鍥炴粴锛屽悗鎮斾簡鍙互鍐嶆仮澶嶆暟鎹紒锛侊紒

truncate璇彞鍒犻櫎鏁版嵁鐨勫師鐞嗭紵 杩欑鍒犻櫎鏁堢巼姣旇緝楂橈紝琛ㄨ涓€娆℃埅鏂紝鐗╃悊鍒犻櫎銆?鐩稿綋浜庢妸鍗犵殑閭ｇ墖绌洪棿缁欐埅浜? 杩欑鍒犻櫎缂虹偣锛氫笉鏀寔鍥炴粴銆 杩欑鍒犻櫎浼樼偣锛氬揩閫

鐢ㄦ硶锛歵runcate table dept_bak; 锛堣繖绉嶆搷浣滃睘浜嶥DL鎿嶄綔銆傦級

澶ц〃闈炲父澶э紝涓婁嚎鏉¤褰曪紵锛燂紵锛 鍒犻櫎鐨勬椂鍊欙紝浣跨敤delete锛屼篃璁搁渶瑕佹墽琛屽緢涔呮墠鑳藉垹闄ゅ畬锛佹晥鐜囪緝浣庛€ 鍙互閫夋嫨浣跨敤truncate鍒犻櫎琛ㄤ腑鐨勬暟鎹€傚彧闇€瑕佷笉鍒?绉掗挓鐨勬椂闂村氨鍒犻櫎缁撴潫銆傛晥鐜囪緝楂樸€ 浣嗘槸浣跨敤truncate涔嬪墠锛屽繀椤讳粩缁嗚闂鎴锋槸鍚︾湡鐨勮鍒犻櫎锛屽苟璀﹀憡鍒犻櫎涔嬪悗涓嶅彲鎭㈠锛

truncate鏄垹闄よ〃涓殑鏁版嵁锛岃〃杩樺湪锛

鍒犻櫎琛ㄦ搷浣渄rop table 琛ㄥ悕; 浼氭妸琛ㄧ粨鏋勫拰琛ㄤ腑鐨勬暟鎹竴璧烽兘鍒犱簡

鍥涘ぇ绾︽潫

浠€涔堟槸绾︽潫锛烖/h1>

绾︽潫瀵瑰簲鐨勮嫳璇崟璇嶏細constraint 鍦ㄥ垱寤鸿〃鐨勬椂鍊欙紝鎴戜滑鍙互缁欒〃涓殑瀛楁鍔犱笂涓€浜涚害鏉燂紝鏉ヤ繚璇佽繖涓〃涓暟鎹殑 瀹屾暣鎬с€佹湁鏁堟€э紒锛侊紒

绾︽潫鐨勪綔鐢ㄥ氨鏄负浜嗕繚璇佽〃涓殑鏁版嵁鏈夋晥锛侊紒

绾︽潫鍖呮嫭鍝簺锛烖/h1>

闈炵┖绾︽潫锛歯ot null 鈥 鍞竴鎬х害鏉烔 unique 鈥 涓婚敭绾︽潫: primary key 锛堢畝绉癙K锛 鈥 澶栭敭绾︽潫锛歠oreign key锛堢畝绉癋K锛 鈥 妫€鏌ョ害鏉燂細check锛坢ysql涓嶆敮鎸侊紝oracle鏀寔锛

闈炵┖绾︽潫锛歯ot null

闈炵┖绾︽潫not null绾︽潫鐨勫瓧娈典笉鑳戒负NULL銆 drop table if exists t_vip; create table t_vip( id int, name varchar(255) not null );

insert into t_vip(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value

鍞竴鎬х害鏉烔 unique

鍞竴鎬х害鏉焨nique绾︽潫鐨勫瓧娈典笉鑳介噸澶嶏紝浣嗘槸鍙互涓篘ULL銆 drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, email varchar(255) ); insert into t_vip(id,name,email) values(3,'wangwu',' [email protected] ');

insert into t_vip(id,name,email) values(4,'wangwu',' [email protected] '); ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'

name瀛楁铏界劧琚玼nique绾︽潫浜嗭紝浣嗘槸鍙互涓篘ULL銆備笖鍙互鏈夊涓汉鐨勬槸null

鏂伴渶姹傦細name鍜宔mail涓や釜瀛楁鑱斿悎璧锋潵鍏锋湁鍞竴鎬э紒锛侊紒锛 drop table if exists t_vip; create table t_vip( id int, name varchar(255) unique, // 绾︽潫鐩存帴娣诲姞鍒板垪鍚庨潰鐨勶紝鍙仛鍒楃骇绾︽潫銆 email varchar(255) unique ); 杩欏紶琛ㄨ繖鏍峰垱寤烘槸涓嶇鍚堟垜浠ヤ笂鈥滄柊闇€姹傗€濈殑銆 杩欐牱鍒涘缓琛ㄧず锛歯ame鍏锋湁鍞竴鎬э紝email鍏锋湁鍞竴鎬с€傚悇鑷敮涓€銆

姝ｇ‘鍋氭硶锛

drop table if exists t_vip; 鈥 create table t_vip( 鈥 id int, 鈥 name varchar(255), 鈥 email varchar(255), 鈥 unique(name,email) // 绾︽潫娌℃湁娣诲姞鍦ㄥ垪鐨勫悗闈紝杩欑绾︽潫琚О涓鸿〃绾х害鏉熴€ 鈥 );

闇€瑕佺粰澶氫釜瀛楁鑱斿悎璧锋潵娣诲姞鏌愪竴涓害鏉熺殑鏃跺€欙紝闇€瑕佷娇鐢ㄨ〃绾х害鏉

not null鍙湁鍒楃骇绾︽潫锛屾病鏈夎〃绾х害鏉燂紒

鍦╩ysql褰撲腑锛屽鏋滀竴涓瓧娈靛悓鏃惰not null鍜寀nique绾︽潫鐨勮瘽锛岃瀛楁鑷姩鍙樻垚涓婚敭瀛楁銆傦紙娉ㄦ剰锛歰racle涓笉涓€鏍凤紒锛堻/h1>

涓婚敭绾︽潫锛坧rimary key锛岀畝绉癙K锛堻/h1>

涓婚敭绾︽潫鐨勭浉鍏虫湳璇紵

涓婚敭绾︽潫锛氬氨鏄竴绉嶇害鏉熴€ 鈥 涓婚敭瀛楁锛氳瀛楁涓婃坊鍔犱簡涓婚敭绾︽潫锛岃繖鏍风殑瀛楁鍙仛锛氫富閿瓧娈 鈥 涓婚敭鍊硷細涓婚敭瀛楁涓殑姣忎竴涓€奸兘鍙仛锛氫富閿€笺€

浠€涔堟槸涓婚敭锛熸湁鍟ョ敤锛烖/h1>

涓婚敭鍊兼槸姣忎竴琛岃褰曠殑鍞竴鏍囪瘑銆傗€ 涓婚敭鍊兼槸姣忎竴琛岃褰曠殑韬唤璇佸彿锛侊紒锛

璁颁綇锛氫换浣曚竴寮犺〃閮藉簲璇ユ湁涓婚敭锛堜笖浠呮湁涓€涓級锛屾病鏈変富閿紝琛ㄦ棤鏁堬紒锛

涓婚敭鐨勭壒寰侊細not null + unique锛堜富閿€间笉鑳芥槸NULL锛屽悓鏃朵篃涓嶈兘閲嶅锛侊級

涓婚敭鍙互浣跨敤琛ㄧ骇绾︽潫

琛ㄧ骇绾︽潫涓昏鏄粰澶氫釜瀛楁鑱斿悎璧锋潵娣诲姞绾︽潫

鍑犱釜瀛楁鑱斿悎璧锋潵鐨勪富閿彨 澶嶅悎涓婚敭

鍦ㄥ疄闄呭紑鍙戜腑涓嶅缓璁娇鐢ㄥ鍚堜富閿€傚缓璁娇鐢ㄥ崟涓€涓婚敭

涓婚敭鍊煎缓璁娇鐢細 int bigint char 绛夌被鍨嬨€

涓嶅缓璁娇鐢細varchar鏉ュ仛涓婚敭銆備富閿€间竴鑸兘鏄暟瀛楋紝涓€鑸兘鏄畾闀跨殑锛

涓婚敭闄や簡锛氬崟涓€涓婚敭鍜屽鍚堜富閿箣澶栵紝杩樺彲浠ヨ繖鏍疯繘琛屽垎绫伙紵

鑷劧涓婚敭锛氫富閿€兼槸涓€涓嚜鐒舵暟锛屽拰涓氬姟娌″叧绯汇€ 鈥 涓氬姟涓婚敭锛氫富閿€煎拰涓氬姟绱у瘑鍏宠仈锛屼緥濡傛嬁閾惰鍗¤处鍙峰仛涓婚敭鍊笺€傝繖灏辨槸涓氬姟涓婚敭锛

鍦ㄥ疄闄呭紑鍙戜腑浣跨敤涓氬姟涓婚敭澶氾紝杩樻槸浣跨敤鑷劧涓婚敭澶氫竴浜涳紵 鈥 鑷劧涓婚敭浣跨敤姣旇緝澶氾紝鍥犱负涓婚敭鍙鍋氬埌涓嶉噸澶嶅氨琛岋紝涓嶉渶瑕佹湁鎰忎箟銆 鈥 涓氬姟涓婚敭涓嶅ソ锛屽洜涓轰富閿竴鏃﹀拰涓氬姟鎸傞挬锛岄偅涔堝綋涓氬姟鍙戠敓鍙樺姩鐨勬椂鍊欙紝 鈥 鍙兘浼氬奖鍝嶅埌涓婚敭鍊硷紝鎵€浠ヤ笟鍔′富閿笉寤鸿浣跨敤銆傚敖閲忎娇鐢ㄨ嚜鐒朵富閿€

鍦╩ysql褰撲腑锛 鏈変竴绉嶆満鍒讹紝鍙互甯姪鎴戜滑鑷姩缁存姢涓€涓富閿€ drop table if exists t_vip; create table t_vip( id int primary key auto_increment, name varchar(255) );

auto_increment 琛ㄧず鑷锛屼粠1寮€濮嬶紝浠?閫掑锛

澶栭敭绾︽潫锛坒oreign key锛岀畝绉癋K锛堻/h1>

澶栭敭绾︽潫娑夊強鍒扮殑鐩稿叧鏈锛 澶栭敭绾︽潫锛氫竴绉嶇害鏉燂紙foreign key锛 澶栭敭瀛楁锛氳瀛楁涓婃坊鍔犱簡澶栭敭绾︽潫 澶栭敭鍊硷細澶栭敭瀛楁褰撲腑鐨勬瘡涓€涓€笺€

涓氬姟鑳屾櫙锛
        璇疯璁℃暟鎹簱琛紝鏉ユ弿杩扳€滅彮绾у拰瀛︾敓鈥濈殑淇℃伅锛
鈥
        绗竴绉嶆柟妗堬細鐝骇鍜屽鐢熷瓨鍌ㄥ湪涓€寮犺〃涓紵锛燂紵
        t_student
        no(pk)      name        classno         classname
        -------------------------------------------------------------------------------
        1           jack        100         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        2           lucy        100         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        3           lilei       100         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        4           hanmeimei   100         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        5           zhangsan    101         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        6           lisi        101         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        7           wangwu      101         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        8           zhaoliu     101         鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        鍒嗘瀽浠ヤ笂鏂规鐨勭己鐐癸細
            鏁版嵁鍐椾綑锛岀┖闂存氮璐癸紒锛侊紒锛
            杩欎釜璁捐鏄瘮杈冨け璐ョ殑锛
        
        绗簩绉嶆柟妗堬細鐝骇涓€寮犺〃銆佸鐢熶竴寮犺〃锛燂紵
        
        t_class 鐝骇琛
        classno(pk)         classname
        ------------------------------------------------------
        100                 鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
        101                 鍖椾含甯傚ぇ鍏村尯浜﹀簞闀囩浜屼腑瀛﹂珮涓?鐝
    
        t_student 瀛︾敓琛
        no(pk)          name            cno(FK寮曠敤t_class杩欏紶琛ㄧ殑classno)
        ----------------------------------------------------------------
        1               jack            100
        2               lucy            100
        3               lilei           100
        4               hanmeimei       100
        5               zhangsan        101
        6               lisi            101
        7               wangwu          101
        8               zhaoliu         101
鈥
        褰揷no瀛楁娌℃湁浠讳綍绾︽潫鐨勬椂鍊欙紝鍙兘浼氬鑷存暟鎹棤鏁堛€傚彲鑳藉嚭鐜颁竴涓?02锛屼絾鏄?02鐝骇涓嶅瓨鍦ㄣ€
        鎵€浠ヤ负浜嗕繚璇乧no瀛楁涓殑鍊奸兘鏄?00鍜?01锛岄渶瑕佺粰cno瀛楁娣诲姞澶栭敭绾︽潫銆
        閭ｄ箞锛歝no瀛楁灏辨槸澶栭敭瀛楁銆俢no瀛楁涓殑姣忎竴涓€奸兘鏄閿€笺€侟/code>           

娉ㄦ剰锛氱悊瑙ｅ嵆璁颁綇

鍒犻櫎琛ㄧ殑椤哄簭锛 鍏堝垹瀛愶紝鍐嶅垹鐖躲€

鍒涘缓琛ㄧ殑椤哄簭锛 鍏堝垱寤虹埗锛屽啀鍒涘缓瀛愩€

鍒犻櫎鏁版嵁鐨勯『搴忥紵 鍏堝垹瀛愶紝鍐嶅垹鐖躲€

鎻掑叆鏁版嵁鐨勯『搴忥紵 鍏堟彃鍏ョ埗锛屽啀鎻掑叆瀛愩€

create table t_class (
    classno int primary key,
    classname varchar(255);
);
create table t_student (
    no int primary key auto_increment,
    name varchar(255),
    cno int,
    foreign key(cno) references t_class(classno)
);           

鎬濊€冿細瀛愯〃涓殑澶栭敭寮曠敤鐨勭埗琛ㄤ腑鐨勬煇涓瓧娈碉紝琚紩鐢ㄧ殑杩欎釜瀛楁蹇呴』鏄富閿悧锛 涓嶄竴瀹氭槸涓婚敭锛屼絾鑷冲皯鍏锋湁unique绾︽潫銆

鍗筹細澶栭敭寮曠敤鐨勫瓧娈典笉涓€瀹氭槸涓婚敭锛屼絾涓€瀹氳鏈塽nique绾︽潫

娴嬭瘯锛氬閿€煎彲浠ヤ负NULL鍚楋紵 澶栭敭鍙婊¤冻鑷冲皯鍚湁unique绾︽潫鍗冲彲锛岃€屾湁unique淇グ鐨勫瓧娈靛瓧娈靛彲浠ヤ负NULL(鑰屼笖鍙互鏈夊涓狽ULL)锛屾墍浠ュ閿€煎綋鐒跺彲浠ヤ负NULL銆

瀛樺偍寮曟搸

浠€涔堟槸瀛樺偍寮曟搸锛屾湁浠€涔堢敤鍛紵

瀛樺偍寮曟搸鏄疢ySQL涓壒鏈夌殑涓€涓湳璇紝鍏跺畠鏁版嵁搴撲腑娌℃湁銆傦紙Oracle涓湁锛屼絾鏄笉鍙繖涓悕瀛楋級 鈥 瀹為檯涓 瀛樺偍寮曟搸鏄竴涓 琛ㄥ瓨鍌?缁勭粐鏁版嵁 鐨勬柟寮 鈥 涓嶅悓鐨勫瓨鍌ㄥ紩鎿庯紝琛ㄥ瓨鍌ㄦ暟鎹殑鏂瑰紡涓嶅悓銆

鎬庝箞缁欒〃娣诲姞/鎸囧畾鈥滃瓨鍌ㄥ紩鎿庘€濆憿锛烖/h1>

鍦ㄥ缓琛ㄧ殑鏃跺€欏彲浠ュ湪鏈€鍚庡皬鎷彿鐨?)"鐨勫彸杈逛娇鐢細 ENGINE鏉ユ寚瀹氬瓨鍌ㄥ紩鎿庛€ CHARSET鏉ユ寚瀹氳繖寮犺〃鐨勫瓧绗︾紪鐮佹柟寮忋€

缁撹锛 mysql榛樿鐨勫瓨鍌ㄥ紩鎿庢槸锛欼nnoDB mysql榛樿鐨勫瓧绗︾紪鐮佹柟寮忔槸锛歶tf8

寤鸿〃鏃舵寚瀹氬瓨鍌ㄥ紩鎿庯紝浠ュ強瀛楃缂栫爜鏂瑰紡銆 create table t_product( id int primary key, name varchar(255) )engine=InnoDB default charset=gbk;

鎬庝箞鏌ョ湅mysql鏀寔鍝簺瀛樺偍寮曟搸鍛紵

鍛戒护锛 show engines \G

MySQL鏀寔9澶у瓨鍌ㄥ紩鎿庯紝涓嶅悓鐗堟湰鏀寔寮曟搸涓嶅悓锛岄€氳繃涓婇潰鎸囦护鍙互鐪嬪嚭褰撳墠鐗堟湰鍝簺鏀寔

鍏充簬mysql甯哥敤鐨勫瓨鍌ㄥ紩鎿庣殑浠嬬粛

MyISAM瀛樺偍寮曟搸锛 瀹冪鐞嗙殑琛ㄥ叿鏈変互涓嬬壒寰侊細 浣跨敤涓変釜鏂囦欢琛ㄧず姣忎釜琛細 鏍煎紡鏂囦欢 鈥 瀛樺偍琛ㄧ粨鏋勭殑瀹氫箟锛坢ytable.frm锛 鏁版嵁鏂囦欢 鈥 瀛樺偍琛ㄨ鐨勫唴瀹癸紙mytable.MYD锛 绱㈠紩鏂囦欢 鈥 瀛樺偍琛ㄤ笂绱㈠紩锛坢ytable.MYI锛夛細绱㈠紩鐢ㄤ簬缂╁皬鎵弿鑼冨洿锛屾彁楂樻煡璇㈡晥鐜囩殑涓€绉嶆満鍒躲€ 鍙杞崲涓哄帇缂┿€佸彧璇昏〃鏉ヨ妭鐪佺┖闂

娉ㄦ剰锛 瀵逛簬涓€寮犺〃鏉ヨ锛 涓婚敭鎴栬€呭姞鏈塽nique绾︽潫鐨勫瓧娈典笂浼氳嚜鍔ㄥ垱寤虹储寮 銆

MyISAM瀛樺偍寮曟搸鐗圭偣锛 鍙杞崲涓哄帇缂┿€佸彧璇昏〃鏉ヨ妭鐪佺┖闂达紒

MyISAM涓嶆敮鎸佷簨鍔℃満鍒讹紝瀹夊叏鎬т綆銆侟/h1>

InnoDB瀛樺偍寮曟搸 杩欐槸mysql榛樿鐨勫瓨鍌ㄥ紩鎿庯紝鍚屾椂涔熸槸涓€涓噸閲忕骇鐨勫瓨鍌ㄥ紩鎿庛€ InnoDB鏀寔浜嬪姟锛屾敮鎸佹暟鎹簱宕╂簝鍚庤嚜鍔ㄦ仮澶嶆満鍒躲€ InnoDB瀛樺偍寮曟搸鏈€涓昏鐨勭壒鐐规槸锛氶潪甯稿畨鍏ㄣ€

瀹冪鐞嗙殑琛ㄥ叿鏈変笅鍒椾富瑕佺壒寰侊細 鈥 姣忎釜 InnoDB 琛ㄥ湪鏁版嵁搴撶洰褰曚腑浠?frm 鏍煎紡鏂囦欢琛ㄧず 鈥 InnoDB 琛ㄧ┖闂 tablespace 琚敤浜庡瓨鍌ㄨ〃鐨勫唴瀹癸紙琛ㄧ┖闂存槸涓€涓€昏緫鍚嶇О銆傝〃绌洪棿瀛樺偍鏁版嵁鍜岀储寮曘€傦級

鈥 鎻愪緵涓€缁勭敤鏉ヨ褰曚簨鍔℃€ф椿鍔ㄧ殑鏃ュ織鏂囦欢 鈥 鈥 鐢 COMMIT(鎻愪氦)銆丼AVEPOINT 鍙奟OLLBACK(鍥炴粴)鏀寔浜嬪姟澶勭悊 鈥 鈥 鎻愪緵鍏 ACID 鍏煎 鈥 鈥 鍦 MySQL 鏈嶅姟鍣ㄥ穿婧冨悗鎻愪緵鑷姩鎭㈠ 鈥 鈥 澶氱増鏈紙MVCC锛夊拰琛岀骇閿佸畾 鈥 鈥 鏀寔澶栭敭鍙婂紩鐢ㄧ殑瀹屾暣鎬э紝鍖呮嫭绾ц仈鍒犻櫎鍜屾洿鏂

InnoDB鏈€澶х殑鐗圭偣灏辨槸锛氭敮鎸佷簨鍔★紝浠ヤ繚璇佹暟鎹殑瀹夊叏銆

鏁堢巼涓嶆槸寰堥珮锛屽苟涓斾篃涓嶈兘鍘嬬缉锛屼笉鑳借浆鎹负鍙锛屼笉鑳藉緢濂界殑鑺傜渷瀛樺偍绌洪棿銆

MEMORY瀛樺偍寮曟搸锛 浣跨敤 MEMORY 瀛樺偍寮曟搸鐨勮〃锛屽叾鏁版嵁瀛樺偍鍦ㄥ唴瀛樹腑锛屼笖琛岀殑闀垮害鍥哄畾锛 杩欎袱涓壒鐐逛娇寰 MEMORY 瀛樺偍寮曟搸闈炲父蹇€

MEMORY 瀛樺偍寮曟搸绠＄悊鐨勮〃鍏锋湁涓嬪垪鐗瑰緛锛 鈥 鍦ㄦ暟鎹簱鐩綍鍐咃紝姣忎釜琛ㄥ潎浠?frm 鏍煎紡鐨勬枃浠惰〃绀恒€ 鈥 琛ㄦ暟鎹強绱㈠紩琚瓨鍌ㄥ湪鍐呭瓨涓€傦紙鐩殑灏辨槸蹇紝鏌ヨ蹇紒锛 鈥 琛ㄧ骇閿佹満鍒躲€ 鈥 涓嶈兘鍖呭惈 TEXT 鎴 BLOB 瀛楁銆

MEMORY 瀛樺偍寮曟搸浠ュ墠琚О涓篐EAP 寮曟搸銆

MEMORY寮曟搸浼樼偣锛氭煡璇㈡晥鐜囨槸鏈€楂樼殑銆備笉闇€瑕佸拰纭洏浜や簰銆 MEMORY寮曟搸缂虹偣锛氫笉瀹夊叏锛屽叧鏈轰箣鍚庢暟鎹秷澶便€傚洜涓烘暟鎹拰绱㈠紩閮芥槸鍦ㄥ唴瀛樺綋涓€

浜嬪姟

涓€涓簨鍔″叾瀹炲氨鏄竴涓畬鏁寸殑涓氬姟閫昏緫銆傛槸涓€涓渶灏忕殑宸ヤ綔鍗曞厓銆備笉鍙啀鍒嗐€

璇村埌鏈川涓婏紝涓€涓簨鍔″叾瀹炲氨鏄鏉ML璇彞鍚屾椂鎴愬姛锛屾垨鑰呭悓鏃跺け璐ワ紒

浠€涔堟槸涓€涓畬鏁寸殑涓氬姟閫昏緫锛
    鍋囪杞处锛屼粠A璐︽埛鍚態璐︽埛涓浆璐?0000.
    灏咥璐︽埛鐨勯挶鍑忓幓10000锛坲pdate璇彞锛
    灏咮璐︽埛鐨勯挶鍔犱笂10000锛坲pdate璇彞锛
    杩欏氨鏄竴涓畬鏁寸殑涓氬姟閫昏緫銆
鈥
    浠ヤ笂鐨勬搷浣滄槸涓€涓渶灏忕殑宸ヤ綔鍗曞厓锛岃涔堝悓鏃舵垚鍔燂紝瑕佷箞鍚屾椂澶辫触锛屼笉鍙啀鍒嗐€
    杩欎袱涓猽pdate璇彞瑕佹眰蹇呴』鍚屾椂鎴愬姛鎴栬€呭悓鏃跺け璐ワ紝杩欐牱鎵嶈兘淇濊瘉閽辨槸姝ｇ‘鐨勩€侟/code>           

鍙湁DML璇彞鎵嶄細鏈変簨鍔¤繖涓€璇达紝鍏跺畠璇彞鍜屼簨鍔℃棤鍏筹紒锛侊紒insert delete update 鍙湁浠ヤ笂鐨勪笁涓鍙ュ拰浜嬪姟鏈夊叧绯伙紝鍏跺畠閮芥病鏈夊叧绯汇€

鍥犱负 鍙湁浠ヤ笂鐨勪笁涓鍙ユ槸鏁版嵁搴撹〃涓暟鎹繘琛屽銆佸垹銆佹敼鐨勩€ 鍙浣犵殑鎿嶄綔涓€鏃︽秹鍙婂埌鏁版嵁鐨勫銆佸垹銆佹敼锛岄偅涔堝氨涓€瀹氳鑰冭檻瀹夊叏闂銆

鏁版嵁瀹夊叏绗竴浣嶏紒锛

浜嬪姟鏄€庝箞鍋氬埌澶氭潯DML璇彞鍚屾椂鎴愬姛鍜屽悓鏃跺け璐ョ殑鍛紵

InnoDB瀛樺偍寮曟搸锛氭彁渚涗竴缁勭敤鏉ヨ褰曚簨鍔℃€ф椿鍔ㄧ殑鏃ュ織鏂囦欢

浜嬪姟寮€鍚簡锛 insert insert delete update update 浜嬪姟缁撴潫浜嗭紒

鍦ㄤ簨鍔＄殑鎵ц杩囩▼涓紝姣忎竴鏉ML鐨勬搷浣滈兘浼氳褰曞埌鈥滀簨鍔℃€ф椿鍔ㄧ殑鏃ュ織鏂囦欢鈥濅腑銆 鍦ㄤ簨鍔＄殑鎵ц杩囩▼涓紝鎴戜滑鍙互鎻愪氦浜嬪姟锛屼篃鍙互鍥炴粴浜嬪姟銆

鎻愪氦浜嬪姟锛 娓呯┖浜嬪姟鎬ф椿鍔ㄧ殑鏃ュ織鏂囦欢锛屽皢鏁版嵁鍏ㄩ儴褰诲簳鎸佷箙鍖栧埌鏁版嵁搴撹〃涓€ 鎻愪氦浜嬪姟鏍囧織鐫€锛屼簨鍔＄殑缁撴潫銆傚苟涓旀槸涓€绉嶅叏閮ㄦ垚鍔熺殑缁撴潫銆

鍥炴粴浜嬪姟锛 灏嗕箣鍓嶆墍鏈夌殑DML鎿嶄綔鍏ㄩ儴鎾ら攢锛屽苟涓旀竻绌轰簨鍔℃€ф椿鍔ㄧ殑鏃ュ織鏂囦欢 鍥炴粴浜嬪姟鏍囧織鐫€锛屼簨鍔＄殑缁撴潫銆傚苟涓旀槸涓€绉嶅叏閮ㄥけ璐ョ殑缁撴潫銆

鎬庝箞鎻愪氦浜嬪姟锛屾€庝箞鍥炴粴浜嬪姟锛烖/h1>

鎻愪氦浜嬪姟锛歝ommit; 璇彞 鍥炴粴浜嬪姟锛歳ollback; 璇彞锛堝洖婊氭案杩滈兘鏄彧鑳藉洖婊氬埌涓婁竴娆＄殑鎻愪氦鐐癸紒锛

浜嬪姟瀵瑰簲鐨勮嫳璇崟璇嶆槸锛歵ransaction

娴嬭瘯涓€涓嬶紝鍦╩ysql褰撲腑榛樿鐨勪簨鍔¤涓烘槸鎬庢牱鐨勶紵 mysql榛樿鎯呭喌涓嬫槸鏀寔鑷姩鎻愪氦浜嬪姟鐨勩€傦紙鑷姩鎻愪氦锛 浠€涔堟槸鑷姩鎻愪氦锛 姣忔墽琛屼竴鏉ML璇彞锛屽垯鎻愪氦涓€娆★紒

杩欑鑷姩鎻愪氦瀹為檯涓婃槸涓嶇鍚堟垜浠殑寮€鍙戜範鎯紝鍥犱负涓€涓笟鍔￠€氬父鏄渶瑕佸鏉ML璇彞鍏卞悓鎵ц鎵嶈兘瀹屾垚鐨勶紝涓轰簡淇濊瘉鏁版嵁鐨勫畨鍏紝蹇呴』瑕佹眰鍚屾椂鎴愬姛涔嬪悗鍐嶆彁浜わ紝鎵€浠ヤ笉鑳芥墽琛屼竴鏉 灏辨彁浜や竴鏉°€

鎬庝箞灏唌ysql鐨勮嚜鍔ㄦ彁浜ゆ満鍒跺叧闂帀鍛紵 寮€鍚簨鍔℃椂鍏堟墽琛岃繖涓懡浠わ細start transaction;

浜嬪姟鐨勫洓涓壒鎬§

浜嬪姟鍖呮嫭4涓壒鎬э紵

A锛氬師瀛愭€ 璇存槑浜嬪姟鏄渶灏忕殑宸ヤ綔鍗曞厓銆備笉鍙啀鍒嗐€傛墍鏈夋搷浣滃繀椤诲悓鏃舵垚鍔燂紝鎴栬€呭悓鏃跺け璐ャ€

C锛氫竴鑷存€ 涓€涓簨鍔″湪鎵ц鍓嶅悗锛屾暟鎹簱閮藉繀椤诲浜庢纭殑鐘舵€侊紝婊¤冻瀹屾暣鎬х害鏉熴€備篃灏辨槸璇翠簨鍔′笉鑳界牬鍧忔暟鎹簱鐨勫畬鏁存€т互鍙婁笟鍔￠€昏緫鐨勪竴鑷存€с€

渚嬪锛

涓氬姟閫昏緫涓€鑷存€э細A缁橞杞处锛屾棤璁烘槸鍚︽搷浣滄垚鍔燂紝涓よ€呯殑璐︽埛浣欓涔嬪拰搴旇鏄笉鍙樼殑銆

鏁版嵁搴撳畬鏁存€э細鏁版嵁搴撶殑绾︽潫鍏崇郴搴旇鏄纭殑锛屼緥濡傚敮涓€绱㈠紩锛屼富閿瓑銆

I锛氶殧绂绘€ 鍚屾椂鎵ц鐨勪簨鍔℃槸鐩镐簰闅旂鐨勶紝瀹冧滑涓嶈兘鐩镐簰鍐掔姱銆

D锛氭寔涔呮€ 浜嬪姟鏈€缁堢粨鏉熺殑涓€涓繚闅溿€備簨鍔℃彁浜わ紝灏辩浉褰撲簬灏嗘病鏈変繚瀛樺埌纭洏涓婄殑鏁版嵁 淇濆瓨鍒扮‖鐩樹笂锛

4涓殧绂荤骇鍒«

璇绘湭鎻愪氦锛歳ead uncommitted锛堟渶浣庣殑闅旂绾у埆锛屾病鏈夋彁浜ゅ氨璇诲埌浜嗭級

浠€涔堟槸璇绘湭鎻愪氦锛 鈥 浜嬪姟A鍙互璇诲彇鍒颁簨鍔鏈彁浜ょ殑鏁版嵁銆 鈥 杩欑闅旂绾у埆瀛樺湪鐨勯棶棰樺氨鏄細 鈥 鑴忚鐜拌薄 锛?Dirty Read) 鈥 鎴戜滑绉拌鍒颁簡鑴忔暟鎹€ 鈥 杩欑闅旂绾у埆涓€鑸兘鏄悊璁轰笂鐨勶紝澶у鏁扮殑鏁版嵁搴撻殧绂荤骇鍒兘鏄簩妗ｈ捣姝ワ紒

璇诲凡鎻愪氦锛歳ead committed锛堟彁浜や箣鍚庢墠鑳借鍒帮級

浠€涔堟槸璇诲凡鎻愪氦锛 鈥 浜嬪姟A鍙兘璇诲彇鍒颁簨鍔鎻愪氦涔嬪悗鐨勬暟鎹€ 鈥 杩欑闅旂绾у埆瑙ｅ喅浜嗕粈涔堥棶棰橈紵 鈥 瑙ｅ喅浜嗚剰璇荤殑鐜拌薄銆 鈥 杩欑闅旂绾у埆瀛樺湪浠€涔堥棶棰橈紵 鈥 涓嶅彲閲嶅璇诲彇鏁版嵁銆 鈥 浠€涔堟槸涓嶅彲閲嶅璇诲彇鏁版嵁鍛紵 鈥 鍦ㄤ簨鍔″紑鍚箣鍚庯紝绗竴娆¤鍒扮殑鏁版嵁鏄?鏉★紝褰撳墠浜嬪姟杩樻病鏈 鈥 缁撴潫锛屽彲鑳界浜屾鍐嶈鍙栫殑鏃跺€欙紝璇诲埌鐨勬暟鎹槸4鏉★紝3涓嶇瓑浜? 鈥 绉颁负涓嶅彲閲嶅璇诲彇銆

杩欑闅旂绾у埆鏄瘮杈冪湡瀹炵殑鏁版嵁锛屾瘡涓€娆¤鍒扮殑鏁版嵁鏄粷瀵圭殑鐪熷疄銆 鈥 oracle鏁版嵁搴撻粯璁ょ殑闅旂绾у埆鏄細read committed

鍙噸澶嶈锛歳epeatable read锛堟彁浜や箣鍚庝篃璇讳笉鍒帮紝姘歌繙璇诲彇鐨勯兘鏄垰寮€鍚簨鍔℃椂鐨勬暟鎹級

浠€涔堟槸鍙噸澶嶈鍙栵紵 鈥 浜嬪姟A寮€鍚箣鍚庯紝涓嶇鏄涔咃紝姣忎竴娆″湪浜嬪姟A涓鍙栧埌鐨勬暟鎹 鈥 閮芥槸涓€鑷寸殑銆傚嵆浣夸簨鍔灏嗘暟鎹凡缁忎慨鏀癸紝骞朵笖鎻愪氦浜嗭紝浜嬪姟A 鈥 璇诲彇鍒扮殑鏁版嵁杩樻槸娌℃湁鍙戠敓鏀瑰彉锛岃繖灏辨槸鍙噸澶嶈銆 鈥 鍙噸澶嶈瑙ｅ喅浜嗕粈涔堥棶棰橈紵 鈥 瑙ｅ喅浜嗕笉鍙噸澶嶈鍙栨暟鎹€ 鈥 鍙噸澶嶈瀛樺湪鐨勯棶棰樻槸浠€涔堬紵 鈥 浼氬嚭鐜板够褰辫銆 鈥 姣忎竴娆¤鍙栧埌鐨勬暟鎹兘鏄够璞°€備笉澶熺湡瀹烇紒

鏃╂櫒9鐐瑰紑濮嬪紑鍚簡浜嬪姟锛屽彧瑕佷簨鍔′笉缁撴潫锛屽埌鏅氫笂9鐐癸紝璇诲埌鐨勬暟鎹繕鏄偅鏍凤紒 鈥 璇诲埌鐨勬槸鍋囪薄銆備笉澶熺粷瀵圭殑鐪熷疄銆

mysql涓粯璁ょ殑浜嬪姟闅旂绾у埆灏辨槸杩欎釜锛侊紒

搴忓垪鍖?涓茶鍖栵細serializable锛堟渶楂樼殑闅旂绾у埆锛堻/h1>

杩欐槸鏈€楂橀殧绂荤骇鍒紝鏁堢巼鏈€浣庛€傝В鍐充簡鎵€鏈夌殑闂銆 鈥 杩欑闅旂绾у埆琛ㄧず浜嬪姟鎺掗槦锛屼笉鑳藉苟鍙戯紒 鈥 synchronized锛岀嚎绋嬪悓姝ワ紙浜嬪姟鍚屾锛 鈥 姣忎竴娆¤鍙栧埌鐨勬暟鎹兘鏄渶鐪熷疄鐨勶紝骞朵笖鏁堢巼鏄渶浣庣殑銆

浠€涔堟槸绱㈠紩锛烖/h1>

绱㈠紩鏄湪鏁版嵁搴撹〃鐨勫瓧娈典笂娣诲姞鐨勶紝鏄负浜嗘彁楂樻煡璇㈡晥鐜囧瓨鍦ㄧ殑涓€绉嶆満鍒躲€備竴寮犺〃鐨勪竴涓瓧娈靛彲浠ユ坊鍔犱竴涓储寮曪紝褰撶劧锛屽涓瓧娈佃仈鍚堣捣鏉ヤ篃鍙互娣诲姞绱㈠紩銆 绱㈠紩鐩稿綋浜庝竴鏈功鐨勭洰褰曪紝鏄负浜嗙缉灏忔壂鎻忚寖鍥磋€屽瓨鍦ㄧ殑涓€绉嶆満鍒躲€

MySQL鍦ㄦ煡璇㈡柟闈富瑕佸氨鏄袱绉嶆柟寮忥細 绗竴绉嶆柟寮忥細鍏ㄨ〃鎵弿 绗簩绉嶆柟寮忥細鏍规嵁绱㈠紩妫€绱€

鍦╩ysql鏁版嵁搴撳綋涓储寮曚篃鏄渶瑕佹帓搴忕殑锛屽苟涓旇繖涓储寮曠殑鎺掑簭鍜孴reeSet 鏁版嵁缁撴瀯鐩稿悓銆俆reeSet锛圱reeMap锛夊簳灞傛槸涓€涓嚜骞宠　鐨勪簩鍙夋爲锛佸湪mysql 褰撲腑绱㈠紩鏄竴涓狟-Tree鏁版嵁缁撴瀯銆

閬靛惊宸﹀皬鍙冲ぇ鍘熷垯瀛樻斁銆傞噰鐢ㄤ腑搴忛亶鍘嗘柟寮忛亶鍘嗗彇鏁版嵁銆

鎻愰啋1锛氬湪浠讳綍鏁版嵁搴撳綋涓富閿笂閮戒細鑷姩娣诲姞绱㈠紩瀵硅薄銆傚彟澶栧湪mysql褰撲腑锛屼竴涓瓧娈典笂濡傛灉鏈塽nique绾︽潫鐨勮瘽锛屼篃浼氳嚜鍔ㄥ垱寤虹储寮曞璞°€侟/h1>

鎻愰啋2锛氬湪浠讳綍鏁版嵁搴撳綋涓紝浠讳綍涓€寮犺〃鐨勪换浣曚竴鏉¤褰曞湪纭洏瀛樺偍涓婇兘鏈 涓€涓‖鐩樼殑鐗╃悊瀛樺偍缂栧彿銆

鎻愰啋3锛氬湪mysql褰撲腑锛岀储寮曟槸涓€涓崟鐙殑瀵硅薄锛屼笉鍚岀殑瀛樺偍寮曟搸浠ヤ笉鍚岀殑褰㈠紡 瀛樺湪锛屽湪MyISAM瀛樺偍寮曟搸涓紝绱㈠紩瀛樺偍鍦ㄤ竴涓?MYI鏂囦欢涓€傚湪InnoDB瀛樺偍寮曟搸涓 绱㈠紩瀛樺偍鍦ㄤ竴涓€昏緫鍚嶇О鍙仛tablespace鐨勫綋涓€傚湪MEMORY瀛樺偍寮曟搸褰撲腑绱㈠紩 琚瓨鍌ㄥ湪鍐呭瓨褰撲腑銆備笉绠＄储寮曞瓨鍌ㄥ湪鍝噷锛岀储寮曞湪mysql褰撲腑閮芥槸涓€涓爲鐨勫舰寮 瀛樺湪銆傦紙鑷钩琛′簩鍙夋爲锛欱-Tree锛

浠€涔堟潯浠朵笅锛屾垜浠細鑰冭檻缁欏瓧娈垫坊鍔犵储寮曞憿锛烖/h1>

鏉′欢1锛 鏁版嵁閲忓簽澶 锛堝埌搴曟湁澶氫箞搴炲ぇ绠楀簽澶э紵杩欎釜闇€瑕佹祴璇曪紝鍥犱负姣忎竴涓‖浠剁幆澧冧笉鍚岋級 鈥 鏉′欢2锛 璇ュ瓧娈电粡甯稿嚭鐜板湪where鐨勫悗闈 锛屼互鏉′欢鐨勫舰寮忓瓨鍦紝涔熷氨鏄杩欎釜瀛楁鎬绘槸琚壂鎻忋€ 鈥 鏉′欢3锛 璇ュ瓧娈靛緢灏戠殑DML (insert delete update)鎿嶄綔銆傦紙鍥犱负DML涔嬪悗锛岀储寮曢渶瑕侀噸鏂版帓搴忋€傦級

寤鸿涓嶈闅忔剰娣诲姞绱㈠紩锛屽洜涓虹储寮曚篃鏄渶瑕佺淮鎶ょ殑锛屽お澶氱殑璇濆弽鑰屼細闄嶄綆绯荤粺鐨勬€ц兘銆 寤鸿閫氳繃涓婚敭鏌ヨ锛屽缓璁€氳繃unique绾︽潫鐨勫瓧娈佃繘琛屾煡璇紝鏁堢巼鏄瘮杈冮珮鐨勩€

绱㈠紩鎬庝箞鍒涘缓锛熸€庝箞鍒犻櫎锛熻娉曟槸浠€涔堬紵

鍒涘缓绱㈠紩锛 mysql> create index emp_ename_index on emp(ename); 缁檈mp琛ㄧ殑ename瀛楁娣诲姞绱㈠紩锛岃捣鍚嶏細emp_ename_index

鍒犻櫎绱㈠紩锛 mysql> drop index emp_ename_index on emp; 灏唀mp琛ㄤ笂鐨別mp_ename_index绱㈠紩瀵硅薄鍒犻櫎銆

鏌ョ湅鏄惁浣跨敤绱㈠紩

mysql> explain select * from emp where ename = 'KING';

type=ALL 璇存槑娌℃湁浣跨敤绱㈠紩锛屽鏋滄槸 typr=ref 鍒欎娇鐢ㄤ簡绱㈠紩

绱㈠紩澶辨晥

澶辨晥鐨勭1绉嶆儏鍐碉細 select * from emp where ename like '%T';

ename涓婂嵆浣挎坊鍔犱簡绱㈠紩锛屼篃涓嶄細璧扮储寮曪紝涓轰粈涔堬紵 鍘熷洜鏄洜涓烘ā绯婂尮閰嶅綋涓互鈥?鈥濆紑澶翠簡锛 灏介噺閬垮厤妯＄硦鏌ヨ鐨勬椂鍊欎互鈥?鈥濆紑濮嬨€ 鍦∕ySQL8寮€濮嬪ソ鍍忎細璧扮储寮曚簡 杩欐槸涓€绉嶄紭鍖栫殑鎵嬫/绛栫暐銆

澶辨晥鐨勭2绉嶆儏鍐碉細 浣跨敤or鐨勬椂鍊欎細澶辨晥锛屽鏋滀娇鐢╫r閭ｄ箞 瑕佹眰or涓よ竟鐨勬潯浠跺瓧娈甸兘瑕佹湁 绱㈠紩锛屾墠浼氳蛋绱㈠紩 锛屽鏋滃叾涓竴杈规湁涓€涓瓧娈垫病鏈夌储寮曪紝閭ｄ箞鍙︿竴涓 瀛楁涓婄殑绱㈠紩涔熶細澶辨晥銆傛墍浠ヨ繖灏辨槸涓轰粈涔堜笉寤鸿浣跨敤or鐨勫師鍥犮€

澶辨晥鐨勭3绉嶆儏鍐碉細 浣跨敤澶嶅悎绱㈠紩鐨勬椂鍊欙紝娌℃湁浣跨敤宸︿晶鐨勫垪鏌ユ壘锛岀储寮曞け鏁 浠€涔堟槸澶嶅悎绱㈠紩锛 涓や釜瀛楁锛屾垨鑰呮洿澶氱殑瀛楁鑱斿悎璧锋潵娣诲姞涓€涓储寮曪紝鍙仛澶嶅悎绱㈠紩銆

create index emp_job_sal_index on emp(job,sal);

explain select * from emp where sal = 800; 娌¤蛋绱㈠紩

澶辨晥鐨勭4绉嶆儏鍐碉細 鍦╳here褰撲腑绱㈠紩鍒楀弬鍔犱簡杩愮畻锛岀储寮曞け鏁堛€

create index emp_sal_index on emp(sal);

explain select * from emp where sal+1 = 800; 娌¤蛋绱㈠紩

澶辨晥鐨勭5绉嶆儏鍐碉細 鍦╳here褰撲腑绱㈠紩鍒椾娇鐢ㄤ簡鍑芥暟 explain select * from emp where lower(ename) = 'smith';

绱㈠紩鐨勭被鍒«

鍗曚竴绱㈠紩锛氫竴涓瓧娈典笂娣诲姞绱㈠紩銆 澶嶅悎绱㈠紩锛氫袱涓瓧娈垫垨鑰呮洿澶氱殑瀛楁涓婃坊鍔犵储寮曘€

涓婚敭绱㈠紩锛氫富閿笂娣诲姞绱㈠紩銆 鍞竴鎬х储寮曪細鍏锋湁unique绾︽潫鐨勫瓧娈典笂娣诲姞绱㈠紩銆 .....

娉ㄦ剰锛氬敮涓€鎬ф瘮杈冨急鐨勫瓧娈典笂娣诲姞绱㈠紩鐢ㄥ涓嶅ぇ銆侟/h1>

浠€涔堟槸瑙嗗浘锛烖/h1>

view:绔欏湪涓嶅悓鐨勮搴﹀幓鐪嬪緟鍚屼竴浠芥暟鎹€

鎬庝箞鍒涘缓瑙嗗浘瀵硅薄锛熸€庝箞鍒犻櫎瑙嗗浘瀵硅薄锛烖/h1>

琛ㄥ鍒讹細
mysql> create table dept2 as select * from dept;
鈥
dept2琛ㄤ腑鐨勬暟鎹細
mysql> select * from dept2;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+           

鍒涘缓瑙嗗浘瀵硅薄锛 create view dept2_view as select * from dept2;

鍒犻櫎瑙嗗浘瀵硅薄锛 drop view dept2_view;

娉ㄦ剰锛氬彧鏈塂QL璇彞鎵嶈兘浠iew鐨勫舰寮忓垱寤恒€俢reate view view_name as 杩欓噷鐨勮鍙ュ繀椤绘槸DQL璇彞;

瑙嗗浘浣滅敤

鎴戜滑鍙互闈㈠悜瑙嗗浘瀵硅薄杩涜澧炲垹鏀规煡锛屽瑙嗗浘瀵硅薄鐨勫鍒犳敼鏌ワ紝浼氬鑷 鍘熻〃琚搷浣滐紒锛堣鍥剧殑鐗圭偣锛氶€氳繃瀵硅鍥剧殑鎿嶄綔锛屼細褰卞搷鍒板師琛ㄦ暟鎹€傦級

//闈㈠悜瑙嗗浘鏌ヨ select * from dept2_view;

// 闈㈠悜瑙嗗浘鎻掑叆 insert into dept2_view(deptno,dname,loc) values(60,'SALES', 'BEIJING');

select * from dept2;

瑙嗗浘瀵硅薄鍦ㄥ疄闄呭紑鍙戜腑鍒板簳鏈変粈涔堢敤锛熸柟渚匡紝绠€鍖栧紑鍙戯紝鍒╀簬缁存姢

鍋囪鏈変竴鏉￠潪甯稿鏉傜殑SQL璇彞锛岃€岃繖鏉QL璇彞闇€瑕佸湪涓嶅悓鐨勪綅缃笂鍙嶅浣跨敤銆 姣忎竴娆′娇鐢ㄨ繖涓猻ql璇彞鐨勬椂鍊欓兘闇€瑕侀噸鏂扮紪鍐欙紝寰堥暱锛屽緢楹荤儲锛屾€庝箞鍔烇紵 鍙互 鎶婅繖鏉″鏉傜殑SQL璇彞浠ヨ鍥惧璞＄殑褰㈠紡鏂板缓銆 鍦ㄩ渶瑕佺紪鍐欒繖鏉QL璇彞鐨勪綅缃洿鎺ヤ娇鐢ㄨ鍥惧璞★紝鍙互澶уぇ绠€鍖栧紑鍙戙€ 骞朵笖鍒╀簬鍚庢湡鐨勭淮鎶わ紝鍥犱负淇敼鐨勬椂鍊欎篃鍙渶瑕佷慨鏀逛竴涓綅缃氨琛岋紝鍙渶瑕 淇敼瑙嗗浘瀵硅薄鎵€鏄犲皠鐨凷QL璇彞銆

鎴戜滑浠ュ悗闈㈠悜瑙嗗浘寮€鍙戠殑鏃跺€欙紝浣跨敤瑙嗗浘鐨勬椂鍊欏彲浠ュ儚浣跨敤table涓€鏍枫€ 鍙互瀵硅鍥捐繘琛屽鍒犳敼鏌ョ瓑鎿嶄綔銆傝鍥句笉鏄湪鍐呭瓨褰撲腑锛岃鍥惧璞′篃鏄 瀛樺偍鍦ㄧ‖鐩樹笂鐨勶紝涓嶄細娑堝け銆

濡備笅闈㈣繖鏍凤紝灏辨槸灏嗕竴鏉QL璇彞浠ヨ鍥剧殑褰㈠紡鍒涘缓鍑烘潵

create view 
        emp_dept_view
    as
        select 
            e.ename,e.sal,d.dname
        from
            emp e
        join
            dept d
        on
            e.deptno = d.deptno;           

娉ㄦ剰锛 鍒涘缓瑙嗗浘瀵瑰簲鐨勮鍙ュ彧鑳芥槸DQL璇彞銆 浣嗘槸瑙嗗浘瀵硅薄鍒涘缓瀹屾垚涔嬪悗锛屽彲浠ュ瑙嗗浘杩涜澧炲垹鏀规煡绛夋搷浣溿€侟/h1>

CRUD锛 澧炲垹鏀规煡锛屽張鍙仛锛欳RUD銆 CRUD鏄湪鍏徃涓▼搴忓憳涔嬮棿娌熼€氱殑鏈銆備竴鑸垜浠緢灏戣澧炲垹鏀规煡銆 涓€鑸兘璇碈RUD銆

C:Create锛堝锛 R:Retrieve锛堟煡锛氭绱級 U:Update锛堟敼锛 D:Delete锛堝垹锛

Java绋嬪簭鍛樺繀椤讳細鐨凞BA甯哥敤鍛戒护锛

鏁版嵁鐨勫鍏ュ拰瀵煎嚭锛堟暟鎹殑澶囦唤锛夆€ 鍏跺畠鍛戒护浜嗚В涓€涓嬪嵆鍙€傦紙杩欎釜B绔欏珫鐨勫煿璁棩蹇楁枃妗ｇ暀鐫€锛屼互鍚庡繕浜嗭紝鍙互鎵撳紑鏂囨。澶嶅埗绮樿创銆傦級

鏁版嵁瀵煎嚭锛 娉ㄦ剰锛氬湪windows鐨刣os鍛戒护绐楀彛涓細 mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123456

鍙互瀵煎嚭鎸囧畾鐨勮〃鍚楋紵 mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

鏁版嵁瀵煎叆锛 娉ㄦ剰锛氶渶瑕佸厛鐧诲綍鍒癿ysql鏁版嵁搴撴湇鍔″櫒涓娿€ 鐒跺悗鍒涘缓鏁版嵁搴擄細create database bjpowernode; 浣跨敤鏁版嵁搴擄細use bjpowernode 鐒跺悗鍒濆鍖栨暟鎹簱锛歴ource D:\bjpowernode.sql 璇ヨ鍙ュ悗涓嶉渶瑕佽窡鍒嗗彿

濡傛灉澶囦唤鐨勬槸琛ㄥ垯涓嶉渶瑕佸垱寤烘暟鎹簱锛岀洿鎺ヤ娇鐢ㄩ渶瑕佸鍏ヨ〃鐨勬暟鎹嵆鍙¯

鏁版嵁搴撹璁¤寖寮忓叡鏈?涓ª

绗竴鑼冨紡锛氳姹備换浣曚竴寮犺〃蹇呴』鏈変富閿紝姣忎竴涓瓧娈靛師瀛愭€т笉鍙啀鍒嗐€

绗簩鑼冨紡锛氬缓绔嬪湪绗竴鑼冨紡鐨勫熀纭€涔嬩笂锛岃姹傛墍鏈夐潪涓婚敭瀛楁瀹屽叏渚濊禆涓婚敭锛 涓嶈浜х敓閮ㄥ垎渚濊禆銆

绗笁鑼冨紡锛氬缓绔嬪湪绗簩鑼冨紡鐨勫熀纭€涔嬩笂锛岃姹傛墍鏈夐潪涓婚敭瀛楁鐩存帴渚濊禆涓婚敭锛 涓嶈浜х敓浼犻€掍緷璧栥€

璁捐鏁版嵁搴撹〃鐨勬椂鍊欙紝 鎸夌収浠ヤ笂鐨勮寖寮忚繘琛岋紝鍙互閬垮厤琛ㄤ腑鏁版嵁鐨勫啑浣欙紝绌洪棿鐨勬氮璐广€

绗竴鑼冨紡

鏈€鏍稿績锛屾渶閲嶈鐨勮寖寮忥紝鎵€鏈夎〃鐨勮璁￠兘闇€瑕佹弧瓒炽€ 蹇呴』鏈変富閿紝骞朵笖姣忎竴涓瓧娈甸兘鏄師瀛愭€т笉鍙啀鍒嗐€

瀛︾敓缂栧彿 瀛︾敓濮撳悕 鑱旂郴鏂瑰紡
------------------------------------------
1001        寮犱笁      [email protected],1359999999
1002        鏉庡洓      [email protected],13699999999
1001        鐜嬩簲      [email protected],13488888888
鈥
浠ヤ笂鏄鐢熻〃锛屾弧瓒崇涓€鑼冨紡鍚楋紵
    涓嶆弧瓒筹紝绗竴锛氭病鏈変富閿€傜浜岋細鑱旂郴鏂瑰紡鍙互鍒嗕负閭鍦板潃鍜岀數璇
鈥
瀛︾敓缂栧彿(pk) 瀛︾敓濮撳悕   閭鍦板潃            鑱旂郴鐢佃瘽
----------------------------------------------------
1001        寮犱笁      [email protected]    1359999999
1002        鏉庡洓      [email protected]    13699999999
1003        鐜嬩簲      [email protected]      13488888888           

绗簩鑼冨紡

4.4銆佺浜岃寖寮忥細 寤虹珛鍦ㄧ涓€鑼冨紡鐨勫熀纭€涔嬩笂锛 瑕佹眰鎵€鏈夐潪涓婚敭瀛楁蹇呴』瀹屽叏渚濊禆涓婚敭锛屼笉瑕佷骇鐢熼儴鍒嗕緷璧栥€

瀛︾敓缂栧彿 瀛︾敓濮撳悕 鏁欏笀缂栧彿 鏁欏笀濮撳悕

1001 寮犱笁 001 鐜嬭€佸笀 1002 鏉庡洓 002 璧佃€佸笀 1003 鐜嬩簲 001 鐜嬭€佸笀 1001 寮犱笁 002 璧佃€佸笀

杩欏紶琛ㄦ弿杩颁簡瀛︾敓鍜岃€佸笀鐨勫叧绯伙細锛?涓鐢熷彲鑳芥湁澶氫釜鑰佸笀锛?涓€佸笀鏈夊涓鐢燂級 杩欐槸闈炲父鍏稿瀷鐨勶細澶氬澶氬叧绯伙紒

鍒嗘瀽浠ヤ笂鐨勮〃鏄惁婊¤冻绗竴鑼冨紡锛 涓嶆弧瓒崇涓€鑼冨紡銆

鎬庝箞婊¤冻绗竴鑼冨紡鍛紵淇敼

瀛︾敓缂栧彿+鏁欏笀缂栧彿(pk) 瀛︾敓濮撳悕 鏁欏笀濮撳悕

1001 001 寮犱笁 鐜嬭€佸笀 1002 002 鏉庡洓 璧佃€佸笀 1003 001 鐜嬩簲 鐜嬭€佸笀 1001 002 寮犱笁 璧佃€佸笀

瀛︾敓缂栧彿 鏁欏笀缂栧彿锛屼袱涓瓧娈佃仈鍚堝仛涓婚敭锛屽鍚堜富閿紙PK: 瀛︾敓缂栧彿+鏁欏笀缂栧彿锛 缁忚繃淇敼涔嬪悗锛屼互涓婄殑琛ㄦ弧瓒充簡绗竴鑼冨紡銆備絾鏄弧瓒崇浜岃寖寮忓悧锛 涓嶆弧瓒筹紝鈥滃紶涓夆€濅緷璧?001锛屸€滅帇鑰佸笀鈥濅緷璧?01锛屾樉鐒朵骇鐢熶簡閮ㄥ垎渚濊禆銆 浜х敓閮ㄥ垎渚濊禆鏈変粈涔堢己鐐癸紵 鏁版嵁鍐椾綑浜嗐€傜┖闂存氮璐逛簡銆傗€滃紶涓夆€濋噸澶嶄簡锛屸€滅帇鑰佸笀鈥濋噸澶嶄簡銆

涓轰簡璁╀互涓婄殑琛ㄦ弧瓒崇浜岃寖寮忥紝浣犻渶瑕佽繖鏍疯璁★細 浣跨敤涓夊紶琛ㄦ潵琛ㄧず澶氬澶氱殑鍏崇郴锛侊紒锛侊紒 瀛︾敓琛

瀛︾敓缂栧彿(pk) 瀛︾敓鍚嶅瓧

1001 寮犱笁 鈥 1002 鏉庡洓 鈥 1003 鐜嬩簲 鈥

鏁欏笀琛

鏁欏笀缂栧彿(pk) 鏁欏笀濮撳悕

001 鐜嬭€佸笀

002 璧佃€佸笀

瀛︾敓鏁欏笀鍏崇郴琛

id(pk) 瀛︾敓缂栧彿(fk) 鏁欏笀缂栧彿(fk)

1 1001 001 2 1002 002 3 1003 001 4 1001 002

鑳屽彛璇€锛 澶氬澶氭€庝箞璁捐锛 澶氬澶氾紝涓夊紶琛紝鍏崇郴琛ㄤ袱涓閿紒

绗笁鑼冨紡

绗笁鑼冨紡寤虹珛鍦ㄧ浜岃寖寮忕殑鍩虹涔嬩笂 鈥 瑕佹眰鎵€鏈夐潪涓婚敭瀛楀吀蹇呴』鐩存帴渚濊禆涓婚敭锛屼笉瑕佷骇鐢熶紶閫掍緷璧栥€侟/h1>

瀛︾敓缂栧彿锛圥K锛 瀛︾敓濮撳悕 鐝骇缂栧彿 鐝骇鍚嶇О

1001 寮犱笁 01 涓€骞翠竴鐝 1002 鏉庡洓 02 涓€骞翠簩鐝 1003 鐜嬩簲 03 涓€骞翠笁鐝 1004 璧靛叚 03 涓€骞翠笁鐝

浠ヤ笂琛ㄧ殑璁捐鏄弿杩帮細鐝骇鍜屽鐢熺殑鍏崇郴銆傚緢鏄剧劧鏄?瀵瑰鍏崇郴锛 涓€涓暀瀹や腑鏈夊涓鐢熴€侟/h1>

鍒嗘瀽浠ヤ笂琛ㄦ槸鍚︽弧瓒崇涓€鑼冨紡锛 婊¤冻绗竴鑼冨紡锛屾湁涓婚敭銆

鍒嗘瀽浠ヤ笂琛ㄦ槸鍚︽弧瓒崇浜岃寖寮忥紵 婊¤冻绗簩鑼冨紡锛屽洜涓轰富閿笉鏄鍚堜富閿紝娌℃湁浜х敓閮ㄥ垎渚濊禆銆備富閿槸鍗曚竴涓婚敭銆

鍒嗘瀽浠ヤ笂琛ㄦ槸鍚︽弧瓒崇涓夎寖寮忥紵 绗笁鑼冨紡瑕佹眰锛氫笉瑕佷骇鐢熶紶閫掍緷璧栵紒 涓€骞翠竴鐝緷璧?1锛?1渚濊禆1001锛屼骇鐢熶簡浼犻€掍緷璧栥€ 涓嶇鍚堢涓夎寖寮忕殑瑕佹眰銆備骇鐢熶簡鏁版嵁鐨勫啑浣欍€

閭ｄ箞搴旇鎬庝箞璁捐涓€瀵瑰鍛紵

鐝骇琛細涓€

鐝骇缂栧彿(pk) 鐝骇鍚嶇О

01 涓€骞翠竴鐝 02 涓€骞翠簩鐝 03 涓€骞翠笁鐝

瀛︾敓琛細澶

瀛︾敓缂栧彿锛圥K锛 瀛︾敓濮撳悕 鐝骇缂栧彿(fk)

1001 寮犱笁 01 1002 鏉庡洓 02 1003 鐜嬩簲 03 1004 璧靛叚 03

鑳屽彛璇€锛 涓€瀵瑰锛屼袱寮犺〃锛屽鐨勮〃鍔犲閿紒锛

琛ㄧ殑璁捐鎬荤粨

鎬荤粨琛ㄧ殑璁捐锛

涓€瀵瑰锛 涓€瀵瑰锛屼袱寮犺〃锛屽鐨勮〃鍔犲閿

澶氬澶氾細 澶氬澶氾紝涓夊紶琛紝鍏崇郴琛ㄤ袱涓閿

涓€瀵逛竴锛 涓€瀵逛竴鏀惧埌涓€寮犺〃涓笉灏辫浜嗗悧锛熶负鍟ヨ繕瑕佹媶鍒嗚〃锛 鍦ㄥ疄闄呯殑寮€鍙戜腑锛屽彲鑳藉瓨鍦ㄤ竴寮犺〃瀛楁澶锛屽お搴炲ぇ銆傝繖涓椂鍊欒鎷嗗垎琛ㄣ€ 涓€瀵逛竴鎬庝箞璁捐锛 娌℃湁鎷嗗垎琛ㄤ箣鍓嶏細涓€寮犺〃 t_user id login_name login_pwd real_name email

1 zhangsan 123 寮犱笁 zhangsan@xxx 鈥 2 lisi 123 鏉庡洓 lisi@xxx

杩欑搴炲ぇ鐨勮〃寤鸿鎷嗗垎涓轰袱寮狅細 t_login 鐧诲綍淇℃伅琛 id(pk) login_name login_pwd

1 zhangsan 123 鈥 2 lisi 123

t_user 鐢ㄦ埛璇︾粏淇℃伅琛

id(pk) real_name email login_id(fk+unique)

100 寮犱笁 zhangsan@xxx 1 200 鏉庡洓 lisi@xxx 2

鍙ｈ瘈锛氫竴瀵逛竴锛屽閿敮涓€

鏁版嵁搴撹璁″疄闄呬腑瑕佹敞鎰忕殑

鏁版嵁搴撹璁′笁鑼冨紡鏄悊璁轰笂鐨勩€

瀹炶返鍜岀悊璁烘湁鐨勬椂鍊欐湁鍋忓樊銆侟/h1>

鏈€缁堢殑鐩殑閮芥槸涓轰簡婊¤冻瀹㈡埛鐨勯渶姹傦紝 鏈夌殑鏃跺€欎細鎷垮啑浣欐崲鎵ц閫熷害銆

鍥犱负鍦╯ql褰撲腑锛岃〃鍜岃〃涔嬮棿杩炴帴娆℃暟瓒婂锛屾晥鐜囪秺浣庛€傦紙绗涘崱灏旂Н锛

鏈夌殑鏃跺€欏彲鑳戒細瀛樺湪鍐椾綑锛屼絾鏄负浜嗗噺灏戣〃鐨勮繛鎺ユ鏁帮紝杩欐牱鍋氫篃鏄悎鐞嗙殑锛 骞朵笖瀵逛簬寮€鍙戜汉鍛樻潵璇达紝sql璇彞鐨勭紪鍐欓毦搴︿篃浼氶檷浣庛€侟/h1>

鍘熸枃閾炬帴锛歨ttps://blog.csdn.net/qq_61557294/article/details/126925880?utm_source=tuicool&utm_medium=referral