###鏁版嵁搴?
涔嬪墠閫氳繃娴佸幓鎿嶄綔鏂囦歡淇濆瓨鏁版嵁搴撶殑寮婄:
1.鎵ц鏁堢巼浣?
2.寮€鍙戞垚鏈珮
3.涓€鑸彧鑳戒繚瀛樺皬閲忔暟鎹?
4.鍙兘淇濆瓨鏂囨湰鏁版嵁
####浠€涔堟槸DB
- DataBase 鏁版嵁搴?浠ｈ〃鏂囦歡闆嗗悎
####浠€涔堟槸DBMS
- DataBaseManagementSystem 鏁版嵁搴撶鐞嗙郴缁?杞歡),鐢ㄤ簬绠＄悊淇濆瓨鏁版嵁鐨勬枃浠堕泦鍚?鐢ㄤ簬鍜岀▼搴忓憳杩涜浜や簰,甯?
瑙佺殑DBMS鏈?Oracle MySQL DB2 SQLServer Sqlite
####SQL
- Structured Query Language:缁撴瀯鍖栨煡璇㈣瑷€,鐢ㄦ埛绋嬪簭鍛樺拰DBMS杩涜浜や簰,鐢ㄤ簬绋嬪簭鍛樺憡璇塂BMS鍒闆簳瀵規暟鎹繘
琛屼粈涔堟搷浣滅殑
###鏁版嵁搴撶殑鍒嗙被(浜嗚В)
- 鍏崇郴鍨嬫暟鎹簱:缁忚繃鏁版嵁鐞嗚楠岃瘉鍙互灏嗙幇瀹炵敓娲諱腑鐨勫悇绉嶅叧绯諱繚瀛樺埌鏁版嵁搴?杩欑灏辯О涓哄叧绯誨瀷鏁版嵁搴?淇濆瓨鏁版嵁浠ヨ〃涓哄崟浣?
- 闈炲叧绯繪暟鎹簱:涓€鑸兘鏄負浜嗚В鍐崇壒瀹氬満鏅殑闂 姣斿:缂撳瓨,楂樺苟鍙戣闂?Redis鏁版嵁搴?浠ey-value褰㈠紡淇濆瓨鏁版嵁)
###甯歌鐨勫叧绯繪暟鎹簱
- MySQL:灞炰簬Oracle鍏徃鐨勪駭鍝?
- Oracle:
- DB2:
- SQLServer:
- Sqlite:
###鎵撳紑瀹㈡埛绔摼鎺ySQL
- 鍦ㄧ粓绔腑鎵ц:mysql -uroot -p 鍥炶濺 濡傛灉鏈夊瘑鐮佸啓瀵嗙爜 鍥炶濺,濡傛灉娌℃湁瀵嗙爜鍒欑洿鎺ュ洖杞?
###鍜屾暟鎹簱鐩稿叧鐨凷QL
###鏌ヨ鎵€鏈夌殑鏁版嵁搴?
show databases;
###鍒涘緩鏁版嵁搴?
create database db1;
###鏌ョ湅鏁版嵁搴撹鎯?
show create database db1;
###鍒涘緩鏁版嵁搴撴寚瀹氬瓧绗﹂泦
create database db2 character set gbk/utf8;
###鍒犻櫎鏁版嵁搴?
drop database db2;
###鍜岃〃鐩稿叧鐨凷QL
###鏌ヨ鎵€鏈夎〃
- show tables;
###鍒涘緩琛?
- create table 琛ㄥ悕(瀛楁1鍚?瀛楁1鐨勭被鍨?瀛楁2鍚?瀛楁2鐨勭被鍨?...);
create table person(name varchar(10),age int);
###鏌ョ湅琛ㄨ鎯?
- show create table 琛ㄥ悕;
###甯歌琛ㄦ椂鎸囧畾琛ㄧ殑寮曟搸鍜屽瓧绗﹂泦
- create table t1(name varchar(10)) engine=myisam charset=gbk;
###琛ㄧ殑寮曟搸
- innodb :鏀寔鏁版嵁搴撶殑楂樼駭鎿嶄綔 濡?澶栭敭,浜嬪姟绛?榛樿寮曟搸
- myisam:鍙敮鎸佸熀纭€鐨勫鍒犳敼鏌ユ搷浣?
###SQL鏍煎紡:
1.鍙互鏈夋崲琛?
2.鏈€鍚庝互";"缁撳熬
3.鍏抽敭瀛椾箣闂撮渶瑕佹湁绌烘牸(鍙互鍐欏涓┖鏍?寤鴻鍐欎竴涓?
###鏌ョ湅琛ㄥ瓧娈?
- desc 琛ㄥ悕;
###鍒犻櫎琛?
- drop table 琛ㄥ悕;
###淇敼鐩稿叧琛?
1.淇敼琛ㄥ悕
rename table 鍘熷悕 to 鏂闆悕;
rename table student to stu;
2.淇敼琛ㄧ殑寮曟搸鍜屽瓧绗﹂泦
- alter table 琛ㄥ悕 engine=myisam/innodb charset=utf8/gbk;
alter table stu engine=myisam charset=gbk;
3.娣誨姞琛ㄥ瓧娈?
- 鏈€鍚庨潰娣誨姞:alter table 琛ㄥ悕 add 瀛楁鍚?瀛楁绫誨瀷;
- 鏈€鍓嶉潰娣誨姞:alter table 琛ㄥ悕 add 瀛楁鍚?瀛楁绫誨瀷 first;
- xxx鐨勫悗闈㈡坊鍔?alter table 琛ㄥ悕 add 瀛楁鍚?瀛楁绫誨瀷 after xxx;
create table hero(name varchar(10));
alter table hero add age int;
alter table hero add id int first;
alter table hero add sal int after name;
4.鍒犻櫎琛ㄥ瓧娈?
- alter table 琛ㄥ悕 drop 瀛楁鍚?
alter table hero drop sal;
5.淇敼琛ㄥ瓧娈電殑鍚嶅瓧鍜岀被鍨?
- alter table 琛ㄥ悕 change 鍘熷瓧娈靛悕 鏂闆瓧娈靛悕 鏂闆瓧娈電被鍨?
alter table hero change name heroname varchar(10);
6.淇敼琛ㄥ瓧娈電殑绫誨瀷鍜屼綅缃?
- alter table 琛ㄥ悕 modify 瀛楁鍚?绫誨瀷 浣嶇疆;
alter table hero modify age int first(after xxx);
###鏁版嵁鐩稿叧
####鎻掑叆鏁版嵁
create table emp(id int,name varchar(10),age int,sal int);
-鍏ㄨ〃鎻掑叆鏁版嵁:
- insert into emp values(1,'tom',18,3000);
-鎸囧畾瀛楁鎻掑叆鏁版嵁:
- insert into emp (name,age ) values('terry',19);
- insert into emp (name ) values('鏉庣櫧');
-鎵歸噺鎻掑叆鏁版嵁:
- insert into emp values(3,'鍒樺',28,6000),(4,'寮犻',20,5000),(5,'鍏崇窘',25,9000);
- insert into emp (name,age) values('鎮熺┖',500),('鍏垝',400),('娌欏儳',300);
###鏌ヨ鏁版嵁
-鏌ヨ鍏ㄩ儴鏁版嵁鐨勫叏閮ㄥ瓧娈典俊鎭?
select * from emp;
-鏌ヨ鎵€鏈夊憳宸ョ殑濮撳悕鍜屽勾榫?
select name,age from emp;
-鏌ヨ骞撮緞鍦?5宀佷竴涓嬬殑鍛樺伐淇℃伅
select * from emp where age<25;
-鏌ヨ宸ヨ祫3000鍧楅挶鐨勫憳宸ュ鍚?骞撮緞,宸ヨ祫
select name,age,sal from emp where sal=3000;
####淇敼鏁版嵁
-淇敼Tom鐨勫伐璧勪負3333
update emp set sal=3333 where name='tom';
-淇敼30宀佷竴涓嬬殑宸ヨ祫涓?666
update emp set sal=6666 where age<30;
-淇敼id绛変簬3鐨勫悕瀛椾負鍚曞竷 骞撮緞涓?5 宸ヨ祫涓?0000
update emp set name='鍚曞竷',age=55,sal=20000 where id=3;
-淇敼宸ヨ祫涓簄ull鐨勫伐璧勪負800
update emp set sal=800 where sal=null;
####鍒犻櫎鏁版嵁
-鍒犻櫎id绛変簬1鐨勫憳宸?
delete from emp where id=1;
-鍒犻櫎骞撮緞鍦?5宀佷互涓嬬殑鍛樺伐
delete from emp where age<25;
-鍒犻櫎鍏ㄩ儴鏁版嵁
delete from emp;
###涓婚敭绾︽潫
- 涓婚敭:鐢ㄤ簬琛ㄧず鏁版嵁鍞竴鎬х殑瀛楁绉頒負涓婚敭
- 绾︽潫:鏄粰琛ㄥ瓧娈墊坊鍔犵殑闄愬埗鏉′歡
- 涓婚敭绾︽潫:闄愬埗涓婚敭瀛楁鍊間笉鑳介噸澶嶅苟涓旈潪绌?鍞竴涓旈潪绌?
create table t1(id int primary key,name varchar(10));
insert into t1 values(1,'Tom');
insert into t1 values(1,'jerry');//澶辮觸,閲嶅
insert into t1 values(null,'ABC')//澶辮觸,涓嶈兘涓簄ull
- 鑷: auto_increment
create table t2(id int primary key auto_increment,name varchar(10));
insert into t2 values(null,'AAA');//
insert into t2 values(2,'AAA');
insert into t2 values(10,'mm');
insert intp t2 values(null,'AAA');
1.褰撳瓧娈靛€間負null鐨勬椂鍊欏€間細鑷繁澧為暱
2.鑷瀛楁鍊間篃鍙互鎵嬪姩璧嬪€?
3.澧為暱瑙勫垯:浠庢浘缁忓嚭鐜拌繃鐨勬渶澶у€煎熀纭€涓?1
4.鑷鏁闆€煎彧澧炰笉鍑?delete娓呯┖琛?鑷鏁闆€煎苟涓嶆竻闆?
###娉ㄩ噴 comment
- 鍒涘緩琛ㄥ０鏄庡瓧娈電殑鏃跺€欑粰瀛楁娣誨姞鐨勪粙缁?
create table t3(id int primary key auto_increment comment '杩欐槸涓€涓富閿?, name varchar(10) comment '杩欐槸鍛樺伐鐨勫鍚?);
###`鍜?
- `:鐢ㄤ簬淇グ琛ㄥ悕鍜屽瓧娈靛悕 鍙互鐪佺暐
create table `t4`(`id` int,`name` varchar(10));
- ':鐢ㄤ簬淇グ瀛楃涓?
###鏁版嵁鍐椾綑
- 濡傛灉琛ㄨ璁′笉澶熷悎鐞?淇濆瓨澶ч噺鏁版嵁鐨勫悓鏃跺彲鑳介殢涔嬩細鍑虹幇澶ч噺閲嶅鏁版嵁,杩欎簺閲嶅鏁版嵁鐨勭幇璞″氨绉頒負鏁版嵁鍐椾綑,閫氳繃鎷嗗垎琛ㄧ殑褰㈠紡瑙ｅ喅鍐椾綑闂
###浜嬪姟
- 浠€涔堟槸浜嬪姟:浜嬪姟鏄暟鎹簱涓墽琛孲QL璇彞鐨勬渶灏忔墽琛屽崟浣?鍙互淇濊瘉浜嬪姟鍐呯殑澶氭潯SQL璇彞瑕佷箞鍏ㄩ儴鎴愬姛,瑕佷箞鍏ㄩ儴澶辮觸.
- 鏌ョ湅鏁版嵁搴撹嚜鍔ㄦ彁浜ょ殑鐘舵€?
show variables like '%autocommit%';
- 鍏蟲帀鑷姩鎻愪氦 0鍏抽棴 1寮€鍚?
set autocommit=0;
pollk-娴嬭瘯杞處:
create table person(id int,name varchar(10),money int);
inert into person values(1,'瓒呬漢',500),(2,'閽㈤搧渚?,10000);
-鍏蟲帀鑷姩鎻愪氦: set autocommit=0;
1.鍏堣瓒呬漢+2000;
update person set money=2500 where id=1;
2.寮€鍚彟涓€涓粓绔?楠岃瘉 姝ゆ椂鏁版嵁搴撴枃浠剁殑鏁版嵁骞舵病鏈夋敼鎺?
3.璁╅挗閾佷緺-2000
update person set money=8000 where id=2;
4.鎵ц鎻愪氦
commit;
-鍥炴粴 rollback;
-灏嗗唴瀛樼殑淇敼鍥炴粴鍒頒笂娆℃彁浜ょ殑鐐?
update person set money=100 where id=1;
rollback;
淇濆瓨鍥炴粴鐐?savepoint
update person set money=100 where id=1;
savepoint s1;
update person set money=200 where id=1;
savepoint s2;
rollback to s1;
###SQL鐨勫垎绫?
####DDL Data Definition Language
- 鏁版嵁瀹氫箟璇█锛屽寘鎷?create,alter,drop,truncate ,涓嶆敮鎸佷簨鍔?
####DML Data Manipulation Language
- 鏁版嵁鎿嶄綔璇█锛?鍖呮嫭 insert,delete,update,select(DQL),鏀寔浜嬪姟
####DQL Data Query Language
- 鏁版嵁鏌ヨ璇█锛屽彧鍖呮嫭select锛屽拰浜嬪姟娌℃湁鍏崇郴鍥犱負骞舵病鏈変慨鏀規暟鎹?
####TCL Transaction Control Language
- 浜嬪姟鎺у埗璇█锛屽寘鎷?commit,rollback, savepoint,rollback to
####DCL Data Control Language
- 鏁版嵁鎺у埗璇█锛岀敤浜庡鐞嗗垎閰嶇敤鎴鋒潈闄愮浉鍏崇殑鎿嶄綔
###truncate
- truncate table 琛ㄥ悕;
- 鍒犻櫎琛ㄥ苟涓斿垱寤轟竴涓柊琛?
- truncate銆乨rop鍜宒elete鐨勫尯鍒細
- delete鐢ㄤ簬鍒犻櫎鏁版嵁锛屼嬌鐢╠elete娓呯┖琛ㄦ椂鑷鏁闆€間笉娓呴浂 鎵ц鏁堢巼鏈€浣?
- drop 鐢ㄤ簬鍒犻櫎琛?鎵ц鏁堢巼鏈€楂?
- truncate 鐢ㄤ簬鍒犻櫎琛ㄥ苟鍒涘緩鏂扮殑绌鴻〃锛屾墽琛屾晥鐜囨瘮delete瑕侀珮锛岃€屼笖鑷鏁闆€間細娓呴浂
###鏁版嵁搴撶殑鏁版嵁绫誨瀷
####鏁存暟
- 甯哥敤鏁存暟鏈?int(m)鍜宐igint(m)锛宮浠ｈ〃鏄劇ず闀垮害蹇呴』鍜寊erofill缁撳悎浣跨敤
create table t_int(num int(10) zerofill);
insert into t_int values(123);
select * from t_int;
####娴偣鏁?
- 甯哥敤娴偣鏁癲ouble(m,d) m浠ｈ〃鎬婚暱搴?d浠ｈ〃灏忔暟闀垮害 23.346 m=5 d=3
- decimal瓒呴珮绮懼害娴偣鏁幫紝搴旂敤鍦烘櫙锛氭秹鍙婅秴楂樼簿搴﹁繍绠楁椂浣跨敤
create table t_double(num double(5,3));
insert into t_double values(23.5678); 鍊間負23.568
insert into t_double values(23.5); 鍊間負23.500
####瀛楃涓?
- char(m): 鍥哄畾闀垮害 m=10 abc 鍗?0锛屾晥鐜囬珮锛屾渶澶?55
- varchar(m):鍙彉闀垮害 m=10 abc 鍗?锛岃妭鐪佺┖闂達紝鏈€澶?5535锛屽鏋滆秴杩?55寤鴻浣跨敤text
- text(m):鍙彉闀垮害 鏈€澶?5535
####鏃ユ湡
- date:鍙兘淇濆瓨骞存湀鏃?
- time:鍙兘淇濆瓨鏃跺垎绉?
- datetime:淇濆瓨骞存湀鏃ユ椂鍒嗙锛岄粯璁ゅ€間負null锛屾渶澶у€?999-12-31
- timestamp(鏃堕棿鎴寵窛绂?9700101 08锛?0锛?0):淇濆瓨骞存湀鏃ユ椂鍒嗙锛岄粯璁ゅ€間負褰撳墠绯葷粺鏃堕棿,鏈€澶у€?038-01-19
create table t_time(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_time values('2018-10-16',null,null,null);
insert into t_time values
(null,'20:06:32','2008-11-22 18:22:11',null);
閰嶇疆URL锛?
jdbc:mysql://localhost:3306/db2?useUnicode=true&characterEncoding=UTF-8
涓夋柟SQL宸ュ叿锛歴qlyog
###is null鍜宨s not null
1.鏌ヨemp琛ㄤ腑娌℃湁涓婄駭棰嗗mgr鐨勫憳宸ョ紪鍙積mpno,濮撳悕ename,宸ヨ祫sal
select empno ,ename,sal from emp where mgr is null;
2.鏌ヨemp琛ㄤ腑娌℃湁濂栭噾comm鐨勫憳宸ュ鍚?宸ヨ祫,濂栭噾
select ename,sal,comm from emp where comm is null;
3.鏌ヨ鏈夊閲戠殑鎵€鏈夊憳宸ヤ俊鎭?
select * from emp where comm is not null;
###鍒悕
1.灏嗘煡璇㈠埌鐨勫憳宸ュ鍚峞namel鏀逛負'濮撳悕'
select ename from emp;
select ename as '濮撳悕' from emp;
select ename '濮撳悕' from emp;
select ename 濮撳悕,sal 宸ヨ祫 from emp;
###鍘婚噸distinct
1.鏌ヨemp琛ㄤ腑鍑虹幇鐨勬墍鏈夎亴浣峧ob
select distinct job from emp;
###姣旇緝杩愮畻绗?>,<,>=,<=,=,!=鍜?lt;>
1.鏌ヨ宸ヨ祫灏忎簬1600鐨勬墍鏈夊憳宸ョ殑濮撳悕鍜屽伐璧?
select ename ,sal from emp where sal<1600;
2.鏌ヨ閮ㄩ棬缂栧彿鏄?0鐨勬墍鏈夊憳宸ュ鍚?鑱屼綅鍜岄儴闂ㄧ紪鍙穌eptno
select ename,job,deptno from emp where deptno=20;
3.鏌ヨ鑱屼綅鏄痬anager鐨勬墍鏈夊憳宸ュ鍚嶅拰鑱屼綅
select ename,job from emp where job='manager';
4.鏌ヨ閮ㄩ棬涓嶆槸10鍙烽儴闂ㄧ殑鎵€鏈夊憳宸ュ鍚嶅拰閮ㄩ棬缂栧彿(涓ょ鏂規硶)
select ename,deptno from emp where deptno<10 or deptno>10;
select ename,deptno from emp where deptno!=10;
5.鏌ヨ鍟嗗搧琛╰_item涓崟浠穚rice绛変簬23鐨勫晢鍝佷俊鎭?
select * from t_item where price=23;
6.鏌ヨ鍟嗗搧琛ㄤ腑鍗曚環涓嶇瓑浜?443鐨勫晢鍝佷俊鎭?
select * from t_item where price!=8443;
###and 鍜?or
- and 绛夋晥浜巎ava涓殑&&
- or 绛夋晥浜巎ava涓殑||
- 鏌ヨ宸ヨ祫澶т簬2000骞朵笖鏄?0鍙烽儴闂ㄧ殑鍛樺伐淇℃伅
select * from emp where sal>2000 and deptno=10;
1.鏌ヨ涓嶆槸10鍙烽儴闂ㄥ苟涓斿伐璧勫ぇ浜庣瓑浜?600鐨勫憳宸ュ鍚?宸ヨ祫,閮ㄩ棬缂栧彿
select ename,sal,deptno from emp where deptno!=10 and sal>=1600;
2.鏌ヨ閮ㄩ棬鏄?0鍙烽儴闂ㄦ垨鑰呬笂绾ч瀵間負7698鐨勫憳宸ュ鍚?鑱屼綅,涓婄駭棰嗗鍜岄儴闂ㄧ紪鍙?
select ename,job,mgr,deptno from emp where deptno=30 or mgr=7698;
3.鏌ヨ鏈変笂绾ч瀵煎苟涓斿伐璧勪綆浜?000,骞朵笖鏄?0鍙烽儴闂ㄧ殑鍛樺伐淇℃伅
select * from emp where mgr is not null and sal<2000 and deptno=20;
###in
1.鏌ヨemp琛ㄤ腑宸ヨ祫鏄?000,1500,3000鐨勫憳宸ヤ俊鎭?
select * from emp where sal=5000 or sal=1500 or sal=3000;
select * from emp where sal in(5000,1500,3000);
###between x and y
1.鏌ヨ宸ヨ祫鍦?000鍒?000涔嬮棿鐨勫憳宸ュ鍚嶅拰宸ヨ祫
select ename,sal from emp where sal>2000 and sal<3000;
select ename,sal from emp where sal between 2000 and 3000;
###妯＄硦鏌ヨ like
- _ :浠ｈ〃鍗曚釜鏈煡瀛楃
- % :浠ｈ〃0鎴栧涓湭鐭ュ瓧绗?
- 涓句緥:
1.浠寮€澶寸殑瀛楃涓? a%
2.浠缁撳熬 %m
3.鍖呭惈x %x%
4.绗簩涓瓧绗︽槸a _a%
5.鍊掓暟绗笁涓瓧姣嶆槸m %m__
6.浠寮€澶村苟涓斿€掓暟绗簩涓瓧姣嶆槸b a%b_
-妗堜緥:
1.鏌ヨ鍚嶅瓧涓寘鍚玜鐨勬墍鏈夊憳宸ュ鍚嶅拰宸ヨ祫:
select ename,sal from emp where ename like '%a%';
2.鏌ヨ鏍囬涓寘鍚浜嬫湰鐨勫晢鍝佹爣棰樺強鍟嗗搧浠鋒牸
select title,price from t_item where title like '%璁頒簨鏈?';
3.鏌ヨ鍗曚環浣庝簬100鐨勮浜嬫湰(title鍖呭惈璁頒簨鏈?
select title from t_item where price<100 and title like '%璁頒簨鏈?';
4.鏌ヨ鍗曚環鍦?0鍒?00涔嬮棿鐨勫緱鍔涘晢鍝?title鍖呭惈寰楀姏)
select title from t_item where price between 50 and 200 and title like '%寰楀姏%';
5.鏌ヨ鍟嗗搧鍒嗙被涓?38鍜?17鐨勫晢鍝佷俊鎭?
select * from t_item where category_id=238 or category_id=917;
6.鏌ヨ鏈夎禒鍝佺殑鍟嗗搧淇℃伅(鍗栫偣sell_point涓寘鍚禒瀛?
select * from t_item where sell_point like '%璧?';
7.鏌ヨ鏈夊浘鐗囩殑image鐨勫緱鍔涘晢鍝佷俊鎭?
select * from t_item where image is not null and title like '%寰楀姏%';
8.鏌ヨ鍜屽緱鍔涙棤鍏崇殑鍟嗗搧淇℃伅(title涓嶅寘鍚緱鍔?
select * from t_item where title not like '%寰楀姏%';
9.鏌ヨ浠鋒牸鍦?0鍒?00浠ュ鐨勫晢鍝佷俊鎭?
select * from t_item where price<50 or price>100;
###鎺掑簭 order by
- order by 鍏抽敭瀛?by 鍚庨潰鍐欐帓搴忕殑瀛楁鍚嶇О 榛樿鏄崌搴?
- asc 鍗囧簭 , desc 闄嶅簭
1.鏌ヨ鎵€鏈夊憳宸ョ殑濮撳悕鍜屽伐璧勬寜鐓у伐璧勫崌搴忔帓鍒?
select ename,sal from emp order by sal ;
2.鏌ヨ10鍙烽儴闂ㄧ殑鎵€鏈夊憳宸ヤ俊鎭?鎸夌収宸ヨ祫闄嶅簭鎺掑垪
select * from emp where deptno=10 order by sal desc ;
3.鏌ヨ鎵€鏈夊甫鐕冨瓧鐨勫晢鍝?鎸夌収浠鋒牸鍗囧簭鎺掑垪
select * from t_item where title like '%鐕?' order by price ;
4.鏌ヨ鎵€鏈塪ell鍟嗗搧鎸夌収鍒嗙被category_id鍗囧簭鎺掑垪
select * from t_item where title like '%dell%' order by category_id ;
5.鏌ヨ鎵€鏈夊憳宸ユ寜鐓ч儴闂ㄥ崌搴忔帓鍒?濡傛灉閮ㄩ棬涓€鑷村垯鎸夌収宸ヨ祫闄嶅簭鎺掑垪
select * from emp order by deptno asc , sal desc;
6.鏌ヨ鎵€鏈夊晢鍝佸垎绫誨拰鍗曚環鎸夌収鍒嗙被闄嶅簭鎺掑垪,濡傛灉鍒嗙被鐩稿悓鍒欐寜鐓т環鏍煎崌搴忔帓鍒?
select category_id,price from t_item order by category_id desc ,price;
###鍒嗛〉鏌ヨ limit
- limit 璺寵繃鐨勬潯鏁?璇鋒眰鐨勬暟閲?姣忛〉鐨勬暟閲?
limit 0,10;
limit 10,10;
绗簲椤電殑20鏉?
limit (5-1)*20,20;
绗叓椤電殑4鏉℃暟鎹?
limit (8-1)*4,4;
1.鏌ヨ鍛樺伐琛ㄤ腑鐨勫伐璧勯檷搴忕殑鍓?鏉?
select * from emp order by sal desc limit 0,5;
2.鏌ヨ鍛樺伐琛ㄤ腑宸ヨ祫闄嶅簭鐨勭3椤電殑4鏉℃暟鎹?
select * from emp order by sal desc limit 8,4;
3.鏌ヨ鍟嗗搧琛ㄤ腑浠鋒牸鍗囧簭鐨勫墠10鏉℃暟鎹?
select * from t_item order by price limit 0,10;
4.鏌ヨ鍟嗗搧琛ㄤ腑浠鋒牸浣庝簬100鍏冪殑鍟嗗搧淇℃伅绗笁椤電殑涓夋潯鏁版嵁
select * from t_item where price<100 order by price limit 6,3;
5.鏌ヨ10鍙烽儴闂ㄥ拰30鍙烽儴闂ㄧ殑鍛樺伐宸ヨ祫鍦ㄥ墠涓夊悕鐨勫憳宸ヤ俊鎭?
select * from emp where deptno=10 or deptno=30 order by sal desc limit 0,3;
###concat() 鍑芥暟
- 鎶奵oncat鍐呴儴鐨勫弬鏁版嫾鎺ュ埌涓€璧?
1.鏌ヨ鍛樺伐濮撳悕鍜屽伐璧?瑕佹眰宸ヨ祫鍗曚綅鏄厓
select ename,concat(sal,'鍏?) 宸ヨ祫 from emp;
###鏁闆€艱绠?+ - * / % (mod(7,2)绛夋晥浜?7%2)
1.鏌ヨ鍟嗗搧琛ㄤ腑姣忎釜鍟嗗搧鐨勫崟浠?搴撳瓨鍙婃€諱環鍊?鍗曚綅*搴撳瓨)
select price,num,price*num from t_item;
2.鏌ヨ鍛樺伐琛ㄤ腑姣忎釜鍛樺伐鐨勫鍚?宸ヨ祫浠ュ強骞寸粓濂?浜斾釜鏈堢殑宸ヨ祫)
select ename,sal,sal*5 骞寸粓濂?from emp;
###鏃ユ湡鐩稿叧鍑芥暟
1.鑾峰彇褰撳墠鐨勫勾鏈堟棩鏃跺垎绉?
select new();
2.鑾峰彇褰撳墠鐨勬棩鏈?current
select curdate();
3.鑾峰彇褰撳墠鐨勬椂闂?
select curtime();
4.浠庡勾鏈堟棩鏃跺垎绉掍腑鎻愬彇骞存湀鏃?
select date(new());
5.浠庡勾鏈堟棩鏃跺垎绉掓彁鍙栨椂鍒嗙
select time(new());
6.浠庡勾鏈堟棩鏃跺垎绉掓彁鍙栨椂闂村垎閲?骞?鏈?鏃?鏃?鍒?绉?
-extract(year from now())
-extract(month from now())
-extract(day from now())
-extract(hour from now())
-extract(minute from now())
-extract(second from now())
select extract(year from now());
-鏌ヨ鍛樺伐琛ㄤ腑鐨勬墍鏈夊憳宸ュ鍚嶅拰鍏ヨ亴鐨勫勾浠?
select ename,extract(year from hiredate) from emp;
7.鏃ユ湡鏍煎紡鍖?date_format(鏃堕棿,鏍煎紡)
now() 2018骞?2鏈?1鏃?18:23:15
-%Y:鍥涗綅骞?2018
-%y:涓や綅骞?18
-%m:涓や綅鏈?05
-%c:涓€浣嶆湀 5
-%d:鏃?
-%H:24灏忔椂
-%h:12灏忔椂
-%i:鍒?
-%s:绉?
select date_format(now(),'%Y骞?m鏈?d鏃?%H鏃?i鍒?s绉?);
鏌ヨ鍟嗗搧鍚嶇О鍜屽晢鍝佷笂浼犳棩鏈?鏍煎紡:x骞磝鏈坸鏃?
select title, DATE_FORMAT(created_time,'%Y骞?m鏈?d鏃?) 鏃ユ湡 from t_item;
-8鎶婇潪鏍囧噯鐨勬棩鏈熷瓧绗︿覆杞垚鏍囧噯鐨勬椂闂存牸寮?str_to_date(鏃堕棿瀛楃涓?鏍煎紡)
14.08.2018 08:00:00
select str_to_date('14.08.2018 08:00:00','%d.%m.%Y %H:%i:%s') 鏍囧噯鍖栨棩鏈?
###ifnull(x,y)鍑芥暟
-age-ifnull(x,18) 濡傛灉x鐨勫€間負null 鍒檃ge=18 濡傛灉涓嶄負null鍒檃ge=x
1.淇敼鍛樺伐琛ㄤ腑濂栭噾涓簄ull鐨勫€間負0
update emp set comm=ifnull(comm,0);
###鑱氬悎鍑芥暟
-鐢ㄤ簬瀵瑰鏉℃暟鎹繘琛岀粺璁?
1.姹傚拰 sum(瀛楁鍚?
-鏌ヨemp涓?0鍙烽儴闂ㄧ殑宸ヨ祫鎬誨拰
select sum(sal) 宸ヨ祫鎬誨拰 from emp where deptno=10;
2.骞沖潎鍊?avg(瀛楁鍚?
-鏌ヨemp琛ㄤ腑鎵€鏈夊憳宸ュ伐璧勭殑骞沖潎鍊?
select avg(sal) 骞沖潎宸ヨ祫 from emp;
-鏌ヨ30鍙烽儴闂ㄧ殑鍛樺伐鐨勬渶楂樺閲?
select max(comm) 鏈€楂樺閲?from emp where deptno=30;
4.鏈€灏忓€?min(瀛楁鍚?
-鏌ヨ鍟嗗搧琛ㄤ腑浠鋒牸鏈€渚垮疁鐨勫晢鍝佺殑鍗曚環
select min(price) 鏈€浣庝環鏍?from t_item ;
5.缁熻鏁伴噺 count(瀛楁鍚? 涓€鑸嬌鐢╟ount(*)
3.鏈€澶у€?max(瀛楁鍚?
###鑱氬悎鍑芥暟缁冧範:
1.缁熻宸ヨ祫鍦?500浠ヤ笂鐨勫憳宸ヤ漢鏁?
select count(*) from emp where sal>2500;
2.鏌ヨ宸ヨ祫鍦?000鍒?000浠ュ唴鐨勬渶澶у閲?
-缁熻30鍙烽儴闂ㄦ湁澶氬皯浜?
select count(*) from emp where deptno=30;
select max(comm) from emp where sal in(1000,3000);
3.鏌ヨ30鍙烽儴闂ㄧ殑鏈€澶у閲?鏈€楂樺伐璧?宸ヨ祫骞沖潎鍊?宸ヨ祫鎬誨拰,骞跺鏌ヨ缁撴灉璧峰埆鍚?
select max(comm) 鏈€澶у閲?max(sal) 鏈€楂樺伐璧?avg(sal) 骞沖潎宸ヨ祫,sum(sal) 宸ヨ祫鎬誨拰 from emp where deptno=30;
4.鏌ヨ浠鋒牸鍦?00鍏冧互鍐呯殑鍟嗗搧鏁伴噺
select count(*) from t_item where price<100;
5.鏌ヨ鍚嶅瓧鍖呭惈a鐨勫憳宸ユ暟閲?
select count(*) from emp where ename like '%a%';
####鍜屽瓧绗︿覆鐩稿叧鍑芥暟
1.鑾峰彇瀛楃涓茬殑闀垮害 char_length(str);
鑾峰彇鎵€鏈夊憳宸ョ殑濮撳悕鍜屽鍚嶇殑瀛楃闀垮害
select ename,char_length(ename) from emp;
2.鑾峰彇瀛楃涓插湪鍙︿竴涓瓧绗︿覆涓嚭鐜扮殑浣嶇疆 instr(str,substr)
select insert('abdcefg','d');
3.鎻掑叆瀛楃涓?insert(str,start,length,newStr)
select insert('abcdefg',3,2,'m');//abmefg
4.杞ぇ鍐?杞皬鍐?
select upper('abc'),lower('NBA');
5.宸﹁竟鎴彇鍜屽彸杈規埅鍙?
select left('abcdefg',2),right('abcdefg',2);
6.鍘諱袱绔┖鐧?
select trim(' a b ');
7. 鎴彇瀛楃涓?
select substring('abcdefg',3,2);
8. 閲嶅 repeat(str,count)
select repeat('ab',2);
9. 鏇挎崲 replace(str,old,new)
select replace('This is mysql','my','your');
10. 鍙嶈漿 reverse(str)
select reverse('abc');
###鍥為【锛?
1. is null 鍜?is not null
2. 鍒悕
3. 鍘婚噸 distinct
4. and 鍜?or
5. 姣旇緝杩愮畻绗?> < >= <= = !=鍜?lt;>
6. in
7. between x and y
8. like _鍗曚釜鏈煡 %0鎴栧涓湭鐭?
9. order by age asc/desc,sal
10. limit 12,3
11. concat(abc,mm)
12. 鏁闆€艱繍绠?+ - * / %鍜?mod()
13. 鏃ユ湡 now() curdate() curtime() date(now()) time(now()) extract(year/month/day/hour/minute/second from now()) date_format(now(),'鏍煎紡') Y y m c d H h i s , str_to_date(鏃堕棿瀛楃涓詫紝鏍煎紡)
14. age=ifnull(x,y) 濡傛灉x=null age=y x锛?null age=x
15. 鑱氬悎鍑芥暟 sum() avg() max() min() count(*)
16. 瀛楃涓?char_length instr insert upper lower left right substring replace repeat reverse trim
###浣滀笟锛?
1.妗堜緥锛氭煡璇㈡病鏈変笂绾ч瀵肩殑鍛樺伐鐨勭紪鍙鳳紝濮撳悕锛屽伐璧?
select empno,ename,sal from emp where mgr is null;
2.妗堜緥锛氭煡璇mp琛ㄤ腑娌℃湁濂栭噾鐨勫憳宸ョ殑濮撳悕锛岃亴浣嶏紝宸ヨ祫锛屼互鍙婂閲?
select enamem,job,sal,comm from emp where comm is null;
3.妗堜緥锛氭煡璇mp琛ㄤ腑鍚湁濂栭噾鐨勫憳宸ョ殑缂栧彿锛屽鍚嶏紝鑱屼綅锛屼互鍙婂閲?
select empno ,ename,job,comm from emp where comm is not null;
4.妗堜緥锛氭煡璇㈠惈鏈変笂绾ч瀵肩殑鍛樺伐鐨勫鍚嶏紝宸ヨ祫浠ュ強涓婄駭棰嗗鐨勭紪鍙?
select ename,sal,mgr from emp where comm is not null;
5.妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧浠モ€楽鈥欏紑澶寸殑鎵€鏈夊憳宸ョ殑濮撳悕
select ename from emp where ename like 's%';
6.妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧鐨勬渶鍚庝竴涓瓧绗︽槸'S'鐨勫憳宸ョ殑濮撳悕
select ename from emp where ename like '%s';
7.妗堜緥锛氭煡璇㈠€掓暟鐨勭2涓瓧绗︽槸鈥楨鈥欑殑鍛樺伐鐨勫鍚?
select ename from emp where ename like '%E_';
8.妗堜緥锛氭煡璇mp琛ㄤ腑鍛樺伐鐨勫€掓暟绗?涓瓧绗︽槸鈥楴鈥欑殑鍛樺伐濮撳悕
select ename from emp where ename like '%N__';
9.妗堜緥锛氭煡璇mp琛ㄤ腑鍛樺伐鐨勫悕瀛椾腑鍖呭惈鈥楢鈥欑殑鍛樺伐鐨勫鍚?
select ename from emp where ename like '%A%';
10.妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧涓嶆槸浠?K'寮€澶寸殑鍛樺伐鐨勬墍鏈変俊鎭?
select * from emp where ename not like 'K%';
11.妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧涓笉鍖呭惈鈥楢鈥欑殑鎵€鏈夊憳宸ョ殑淇℃伅
select * from emp where ename not like '%A%';
12.妗堜緥锛氬仛鏂囧憳鐨勫憳宸ヤ漢鏁幫紙job 涓?鍚湁 CLERK 鐨勶級
select count(job) from emp where job like '%CLERK%';
13.妗堜緥锛氶攢鍞漢鍛?job: SALESMAN 鐨勬渶楂樿柂姘?
select max(sal) from emp where job='SALESMAN';
14.妗堜緥锛氭渶鏃╁拰鏈€鏅氬叆鑱屾椂闂?
select min(HIREdate) 鏈€鏃╁叆鑱屾椂闂?max(HIREdate) 鏈€鏅氬叆鑱屾椂闂?from emp;
15.妗堜緥锛氭煡璇㈢被鍒?163鐨勫晢鍝佹€誨簱瀛橀噺
select sum(num) 鎬誨簱瀛橀噺 from t_item where category_id=163;
16.妗堜緥锛氭煡璇?绫誨埆 163 鐨勫晢鍝?
select * from t_item where category_id=163;
17.妗堜緥锛氭煡璇㈠晢鍝佷環鏍間笉澶т簬100鐨勫晢鍝佸悕绉闆垪琛?
select title from t_item where price<=100;
18.妗堜緥锛氭煡璇㈠搧鐗屾槸鑱旀兂,涓斾環鏍煎湪40000浠ヤ笂鐨勫晢鍝佸悕绉闆拰浠鋒牸
select title,price from t_item where title like '&鑱旀兂&' and price>40000;
19.妗堜緥锛氭煡璇㈠搧鐗屾槸涓夋湪,鎴栦環鏍煎湪50浠ヤ笅鐨勫晢鍝佸悕绉闆拰浠鋒牸
select title,price from t_item where title like '%涓夋湪%' or price<50;
20.妗堜緥锛氭煡璇㈠搧鐗屾槸涓夋湪銆佸箍鍗氥€侀綈蹇冪殑鍟嗗搧鍚嶇О鍜屼環鏍?
select title,price from t_item where title like '%涓夋湪%' or title like '%骞垮崥%' or title like '%榻愬績%';
21.妗堜緥锛氭煡璇㈠搧鐗屼笉鏄仈鎯熾€佹埓灏旂殑鍟嗗搧鍚嶇О鍜屼環鏍?
select title,price from t_item where title not like '%鑱旀兂%' and title not like '%鎴村皵%';
22.妗堜緥锛氭煡鎵懼搧鐗屾槸鑱旀兂涓斾環鏍煎ぇ浜?0000鐨勭數鑴戝悕绉?
select title from t_item where title like '%鑱旀兂%' and price>10000;
23.妗堜緥锛氭煡璇㈣仈鎯蟲垨鎴村皵鐨勭數鑴戝悕绉闆垪琛?
select title from t_item where title like '%鑱旀兂%' or title like '%鎴村皵%';
24.妗堜緥锛氭煡璇㈣仈鎯熾€佹埓灏斻€佷笁鏈ㄧ殑鍟嗗搧鍚嶇О鍒楄〃
select title from t_item where title like '%鑱旀兂%' or title like '%鎴村皵%' or title like '%涓夋湪%';
25.妗堜緥锛氭煡璇笉鏄埓灏旂殑鐢佃剳鍚嶇О鍒楄〃
select title from t_item where title not like '%鎴村皵%';
26.妗堜緥锛氭煡璇㈡墍鏈夋槸璁頒簨鏈殑鍟嗗搧鍝佺墝銆佸悕绉闆拰浠鋒牸
select title,item_type,price from t_item where title like '%璁頒簨鏈?';
27.妗堜緥锛氭煡璇㈠搧鐗屾槸鏈熬瀛楃鏄?鍔?鐨勫晢鍝佺殑鍝佺墝銆佸悕绉闆拰浠鋒牸
select title,item_type,price from t_item where title like '%鍔?%' or title like '%鍔涳紙%';
28.妗堜緥锛氬悕绉頒腑鏈夎仈鎯沖瓧鏍風殑鍟嗗搧鍚嶇О
select title from t_item where title like '%鑱旀兂%';
29.妗堜緥锛氭煡璇㈠崠鐐瑰惈鏈?璧?浜у搧鍚嶇О
select title from t_item where sell_point like '%璧?';
30.妗堜緥锛氭煡璇mp琛ㄤ腑鍛樺伐鐨勭紪鍙鳳紝濮撳悕锛岃亴浣嶏紝宸ヨ祫锛屽苟涓斿伐璧勫湪1000~2000涔嬮棿銆?
select empno,ename,job,sal from emp where sal>=1000 and sal<=2000;
31.妗堜緥锛氭煡璇mp琛ㄤ腑鍛樺伐鍦?0鍙烽儴闂紝骞朵笖鍚湁涓婄駭棰嗗鐨勫憳宸ョ殑濮撳悕锛岃亴浣嶏紝涓婄駭棰嗗缂栧彿浠ュ強鎵€灞為儴闂ㄧ殑缂栧彿
select ename,job,mgr,deptno from emp where deptno=10 and mgr is not null;
32.妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧涓寘鍚?E'锛屽苟涓旇亴浣嶄笉鏄疢ANAGER鐨勫憳宸ョ殑缂栧彿锛屽鍚嶏紝鑱屼綅锛屼互鍙婂伐璧勩€?
select empno,ename,job,sal from emp where ename like '%E%' and job!='MANAGER';
33.妗堜緥锛氭煡璇mp琛ㄤ腑10鍙烽儴闂ㄦ垨鑰?0鍙烽儴闂ㄤ腑鍛樺伐鐨勭紪鍙鳳紝濮撳悕锛屾墍灞為儴闂ㄧ殑缂栧彿
select empno,ename,deptno from emp where deptno=10 or deptno=20;
34.妗堜緥锛氭煡璇mp琛ㄤ腑娌℃湁濂栭噾鎴栬€呭悕瀛楃殑鍊掓暟绗?涓瓧姣嶄笉鏄疶鐨勫憳宸ョ殑缂栧彿锛屽鍚嶏紝鑱屼綅浠ュ強濂栭噾
select empno,ename,job,comm from emp where comm is null or ename not like '%T_';
35.妗堜緥锛氭煡璇㈠伐璧勯珮浜?000鎴栬€呴儴闂ㄧ紪鍙鋒槸30鐨勫憳宸ョ殑濮撳悕锛岃亴浣嶏紝宸ヨ祫锛屽叆鑱屾椂闂翠互鍙婃墍灞為儴闂ㄧ殑缂栧彿
select ename,job,sal,hiredate,deptno from emp where sal>3000 or deptno=30;
36.妗堜緥锛氭煡璇笉鏄?0鍙烽儴闂ㄧ殑鍛樺伐鐨勬墍鏈変俊鎭?
select * from emp where deptno!=30;
37.妗堜緥锛氭煡璇㈠閲戜笉涓虹┖鐨勫憳宸ョ殑鎵€鏈変俊鎭?
select * from emp where comm is not null;
38.妗堜緥锛氭煡璇mp琛ㄤ腑鎵€鏈夊憳宸ョ殑缂栧彿锛屽鍚嶏紝鑱屼綅锛屾牴鎹憳宸ョ殑缂栧彿杩涜闄嶅簭鎺掑垪
select empno,ename,job from emp order by empno desc;
39.妗堜緥锛氭煡璇mp琛ㄤ腑閮ㄩ棬缂栧彿鏄?0鍙鋒垨鑰?0鍙蜂腑锛屾墍鏈夊憳宸ュ鍚嶏紝鑱屽姟锛屽伐璧勶紝鏍規嵁宸ヨ祫杩涜鍗囧簭鎺掑垪
select ename,job,sal from emp where deptno=10 or deptno=30 order by sal;
40.妗堜緥锛氭煡璇mp琛ㄤ腑鎵€鏈夌殑鏁版嵁锛岀劧鍚庢牴鎹儴闂ㄧ殑缂栧彿杩涜鍗囧簭鎺掑垪锛屽鏋滈儴闂ㄧ紪鍙蜂竴鑷達紝鏍規嵁鍛樺伐鐨勭紪鍙瘋繘琛岄檷搴忔帓鍒?
select * from emp order by deptno ,empno desc;
41.妗堜緥锛氭煡璇mp琛ㄤ腑宸ヨ祫楂樹簬1000鎴栬€呮病鏈変笂绾ч瀵肩殑鍛樺伐鐨勭紪鍙鳳紝濮撳悕锛屽伐璧勶紝鎵€灞為儴闂ㄧ殑缂栧彿锛屼互鍙婁笂绾ч瀵肩殑缂栧彿锛屾牴鎹儴闂ㄧ紪鍙瘋繘琛岄檷搴忔帓鍒楋紝濡傛灉閮ㄩ棬缂栧彿涓€鑷存牴鎹伐璧勮繘琛屽崌搴忔帓鍒椼€?
select empno,ename,sal,deptno,mgr from emp where sal>1000 or mgr is null order by deptno desc,sal ;
42.妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧涓笉鍖呭惈S鐨勫憳宸ョ殑缂栧彿锛屽鍚嶏紝宸ヨ祫锛屽閲戯紝鏍規嵁宸ヨ祫杩涜鍗囧簭鎺掑垪锛屽鏋滃伐璧勪竴鑷達紝鏍規嵁缂栧彿杩涜闄嶅簭鎺掑垪
select empno,ename,sal,comm from emp where ename not like '%S%' order by sal ,empno desc;
43.妗堜緥锛氱粺璁mp琛ㄤ腑鍛樺伐鐨勬€繪暟閲?
select count(*) from emp;
44.妗堜緥锛氱粺璁mp琛ㄤ腑鑾峰緱濂栭噾鐨勫憳宸ョ殑鏁伴噺
select count(comm) from emp;
45.妗堜緥锛氭眰鍑篹mp琛ㄤ腑鎵€鏈夌殑宸ヨ祫绱姞涔嬪拰
select sum(sal) from emp;
46.妗堜緥锛氭眰鍑篹mp琛ㄤ腑鎵€鏈夌殑濂栭噾绱姞涔嬪拰
select sum(comm) from emp;
47.妗堜緥锛氭眰鍑篹mp琛ㄤ腑鍛樺伐鐨勫鈎鍧囧伐璧?
select avg(sal) from emp;
48.妗堜緥锛氭眰鍑篹mp琛ㄤ腑鍛樺伐鐨勫鈎鍧囧閲?
select avg(comm) from emp;
49.妗堜緥锛氭眰鍑篹mp琛ㄤ腑鍛樺伐鐨勬渶楂樺伐璧?
select max(sal) from emp;
50.妗堜緥锛氭眰鍑篹mp琛ㄤ腑鍛樺伐缂栧彿鐨勬渶澶у€?
select max(empno) from emp;
51.妗堜緥锛氭煡璇mp琛ㄤ腑鍛樺伐鐨勬渶浣庡伐璧勩€?
select min(sal) from emp;
52.妗堜緥锛氭煡璇mp琛ㄤ腑鍛樺伐鐨勪漢鏁幫紝宸ヨ祫鐨勬€誨拰锛屽鈎鍧囧伐璧勶紝濂栭噾鐨勬渶澶у€鹼紝濂栭噾鐨勬渶灏忓€?骞朵笖瀵矽繑鍥炵殑鍒楄搗鍒悕銆?
select count(*) 鎬諱漢鏁?sum(sal) 宸ヨ祫鎬誨拰,avg(sal) 骞沖潎宸ヨ祫,max(comm) 濂栭噾鏈€澶у€?min(comm) 濂栭噾鏈€灏忓€?from emp;
53.妗堜緥锛氭煡璇mp琛ㄤ腑姣忎釜閮ㄩ棬鐨勭紪鍙鳳紝浜烘暟锛屽伐璧勬€誨拰锛屾渶鍚庢牴鎹漢鏁拌繘琛屽崌搴忔帓鍒楋紝濡傛灉浜烘暟涓€鑷達紝鏍規嵁宸ヨ祫鎬誨拰闄嶅簭鎺掑垪銆?
select deptno,count(*) 浜烘暟,sum(sal) 宸ヨ祫鎬誨拰 from emp group by deptno order by 浜烘暟,宸ヨ祫鎬誨拰 desc;
54.妗堜緥锛氭煡璇㈠伐璧勫湪1000~3000涔嬮棿鐨勫憳宸ヤ俊鎭紝姣忎釜閮ㄩ棬鐨勭紪鍙鳳紝骞沖潎宸ヨ祫锛屾渶浣庡伐璧勶紝鏈€楂樺伐璧勶紝鏍規嵁骞沖潎宸ヨ祫杩涜鍗囧簭鎺掑垪銆?
select deptno,avg(sal) 骞沖潎宸ヨ祫,min(sal) 鏈€浣庡伐璧?max(sal) 鏈€楂樺伐璧?from emp where sal>=1000 and sal<=3000 group by deptno order by 骞沖潎宸ヨ祫;
55.妗堜緥锛氭煡璇㈠惈鏈変笂绾ч瀵肩殑鍛樺伐锛屾瘡涓亴涓氱殑浜烘暟锛屽伐璧勭殑鎬誨拰锛屽鈎鍧囧伐璧勶紝鏈€浣庡伐璧勶紝鏈€鍚庢牴鎹漢鏁拌繘琛岄檷搴忔帓鍒楋紝濡傛灉浜烘暟涓€鑷達紝鏍規嵁骞沖潎宸ヨ祫杩涜鍗囧簭鎺掑垪
select count(job) 姣忎釜鑱屼笟浜烘暟,sum(sal) 宸ヨ祫鎬誨拰,avg(sal) 骞沖潎宸ヨ祫,min(sal) 鏈€浣庡伐璧?from emp where mgr is not null group by job order by 姣忎釜鑱屼笟浜烘暟 desc,骞沖潎宸ヨ祫;
56.妗堜緥锛氭煡璇㈠伐璧勫湪1000~3000涔嬮棿姣忎竴涓憳宸ョ殑缂栧彿锛屽鍚嶏紝鑱屼綅锛屽伐璧?
select empno,ename,job,sal from emp where sal between 1000 and 3000;
57.妗堜緥锛氭煡璇mp琛ㄤ腑濂栭噾鍦?00~2000涔嬮棿鎵€鏈夊憳宸ョ殑缂栧彿锛屽鍚嶏紝宸ヨ祫浠ュ強濂栭噾
select empno,ename,sal,comm from emp where comm between 500 and 2000;
58.妗堜緥锛氭煡璇㈠憳宸ョ殑缂栧彿鏄?369锛?521锛?
select * from emp where empno=7369 or empno=7521;
59.妗堜緥锛氭煡璇mp琛ㄤ腑锛岃亴浣嶆槸ANALYST锛?
select * from emp where job='ANALYST';
60.妗堜緥锛氭煡璇mp琛ㄤ腑鑱屼綅涓嶆槸ANALYST,
select * from emp where job!='ANALYST';
###鏁闆鐩稿叧鍑芥暟
1.鍚戜笅鍙栨暣 floor(num)
select floor(3.84);
2.鍥涜垗浜斿叆 round(num)
select round(3.84);
-round(num,m) m浠ｈ〃灏忔暟浣嶆暟
select round(3.1415936,3);//3.142
3.闈炲洓鑸嶄簲鍏?truncate(num,m)
select truncate(3.1415926,3);//3.141
4.闅忔満鏁?rand(); 0-1 5-10 0-5;
select floor(rand()*6)+5;//5-10
select floor(rand()*6)+3;//3-8
###鍒嗙粍鏌ヨ
-鍒嗙粍鏌ヨ閫氬父鍜岃仛鍚堝嚱鏁扮粨鍚堜嬌鐢?浠ョ粍涓哄崟浣嶈繘琛岀粺璁?
-涓€鑸儏鍐典笅,棰樼洰涓瘡涓獂xx灏卞湪group by 鍚庨潰鍐檟xx
1.鏌ヨ姣忎釜閮ㄩ棬鐨勬渶楂樺伐璧?
select max(sal) from emp group by dep tno;
2.鏌ヨ姣忎釜閮ㄩ棬鐨勫鈎鍧囧伐璧?
select avg(sal) from emp group by deptno;
3.鏌ヨ姣忎釜鍒嗙被涓嬪晢鍝佺殑鏈€浣庝環鏍?
select min(price) from t_item group by category_id;
4.鏌ヨ姣忎釜閮ㄩ棬涓伐璧勫ぇ浜?500鐨勪漢鏁?
select deptno 閮ㄩ棬缂栧彿,count(*) 浜烘暟 from emp where sal>1500 group by deptno;
5.鏌ヨ姣忎釜棰嗗鐨勬墜涓嬩漢鏁?
select mgr 棰嗗, count(*) 浜烘暟 from emp where mgr is not null group by mgr;
6.鏌ヨ姣忎釜鍟嗗搧鍒嗙被鐨勫簱瀛樻暟閲?
select category_id 鍟嗗搧鍒嗙被,sum(num) 搴撳瓨鏁伴噺 from t_item group by category_id;
--濡傛灉闇€瑕佷嬌鐢ㄥ涓瓧娈佃繘琛屽垎缁?鐩存帴鍦╣roup by 鍚庨潰鍐欏涓瓧娈靛悕 閫氳繃閫楀彿鍒嗛殧
--having 鍚庨潰鍙互鍐欐櫘閫氬瓧娈墊潯浠朵篃鍙互鍐欒仛鍚堝嚱鏁?浣嗘槸鎺ㄨ崘鍦╤aving鍚庨潰鍙啓鑱氬悎鍑芥暟
------鍥哄畾鐨勯『搴?select * from 琛ㄥ悕 where .....group by xxx having ..... order by ..... limit...;
1.鏌ヨ姣忎釜閮ㄩ棬涓嬫瘡涓瀵肩殑鎵嬩笅浜烘暟
select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;
2.鏌ヨ姣忎釜閮ㄩ棬鐨勫鈎鍧囧伐璧?瑕佹眰骞沖潎宸ヨ祫澶т簬2000
select avg(sal) a from emp group by deptno having a>2000;
3.鏌ヨ姣忎釜鍒嗙被鐨勫鈎鍧囧崟浠?杩囨護鎺夊鈎鍧囧崟浠蜂綆浜?00鐨?
select category_id,avg(price) a from t_item group by category_id having a>=100;
####缁冧範:
1.鏌ヨ姣忎釜鍒嗙被鍟嗗搧鐨勫簱瀛樻€婚噺,瑕佹眰鎬婚噺楂樹簬19999;
select category_id, sum(num) s from t_item group by category_id having s>19999;
2.鏌ヨ鍒嗙被id涓?38鍜?17鐨勪袱涓垎绫葷殑骞沖潎鍗曚環鍚勬槸澶氬皯
select category_id, avg(price) 骞沖潎鍗曚環 from t_item where category_id=238 or category_id=917 group by category_id;
3.鏌ヨemp琛ㄤ腑姣忎釜閮ㄩ棬鐨勫鈎鍧囧伐璧勯珮浜?000鐨勯儴闂ㄧ紪鍙?閮ㄩ棬浜烘暟,骞沖潎宸ヨ祫,鏈€鍚庢牴鎹鈎鍧囧伐璧勯檷搴忔帓鍒?
select deptno,count(*) 閮ㄩ棬浜烘暟,avg(sal) 骞沖潎宸ヨ祫 from emp group by deptno having 骞沖潎宸ヨ祫>2000 order by 骞沖潎宸ヨ祫 desc;
4.鏌ヨemp琛ㄤ腑宸ヨ祫鍦?000-3000涔嬮棿鐨勫憳宸?姣忎釜閮ㄩ棬鐨勭紪鍙?宸ヨ祫鎬誨拰,骞沖潎宸ヨ祫,瑕佹眰杩囨護鎺夊鈎鍧囧伐璧勪綆浜?000鐨勯儴闂?鎸夌収宸ヨ祫鎬誨拰闄嶅簭鎺掑垪
select deptno,sum(sal) 宸ヨ祫鎬誨拰,avg(sal) 骞沖潎宸ヨ祫 from emp where sal between 1000 and 3000 group by deptno having 骞沖潎宸ヨ祫>2000 order by 宸ヨ祫鎬誨拰 desc;
5.鏌ヨemp琛ㄤ腑涓嶆槸浠寮€澶存瘡涓亴鍛樼殑鍚嶅瓧,浜烘暟,宸ヨ祫鎬誨拰,鏈€楂樺伐璧?杩囨護鎺夊鈎鍧囧伐璧勬槸3000鐨勮亴浣?鏍規嵁浜烘暟鍗囧簭鎺掑簭,濡傛灉涓€鑷存牴鎹伐璧勬€誨拰闄嶅簭鎺掑垪
6.鏌ヨemp琛ㄤ腑姣忓勾鍏ヨ亴鐨勪漢鏁?
select extract(year from hiredate) y,count(*) from emp group by y;
7.鏌ヨemp琛ㄥ伐璧勬渶楂樼殑鍛樺伐淇℃伅
select * from emp where sal=(select max(sal) from emp);
###瀛愭煡璇?
1.鏌ヨemp琛ㄥ伐璧勭殑鏈€楂樼殑鍛樺伐淇℃伅
select max(sal) from emp;
select * from emp where sal=5000;
-瀛愭煡璇?
select * from emp where sal=(select max(sal) from emp);
2.鏌ヨemp琛ㄤ腑宸ヨ祫楂樹簬骞沖潎宸ヨ祫鐨勫憳宸ヤ俊鎭?
3.鏌ヨ鍜孞ones鐩稿悓宸ヤ綔鐨勫憳宸ヤ俊鎭?
select * from emp where job=(select job from emp where ename='jones') and ename!='Jones';
4.鏌ヨ宸ヨ祫鏈€浣庣殑鍛樺伐鐨勬墍鍦ㄩ儴闂ㄥ悓浜嬩俊鎭?
select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
5.鏌ヨ鏈€鍚庡叆鑱岀殑鍛樺伐淇℃伅
select * from emp where hiredate=(select max(hiredate) from emp);
-having 瑕佸啓鍦╣roup by 鍚庨潰
6.鏌ヨking鐨勯儴闂ㄧ紪鍙峰拰閮ㄩ棬鍚嶇О
select deptno,dname from dept where deptno=(select deptno from emp where ename='king');
7.鏌ヨ鏈夊憳宸ョ殑閮ㄩ棬淇℃伅
select * from dept where deptno in(select distinct deptno from emp);
8.鏌ヨ骞沖潎宸ヨ祫鏈€楂樼殑閮ㄩ棬淇℃伅
-寰楀埌姣忎釜閮ㄩ棬鐨勫鈎鍧囧伐璧?
select deptno, avg(sal) from emp group by deptno;
-寰楀埌鏈€楂樼殑骞沖潎宸ヨ祫
select deptno, avg(sal) a from emp group by deptno order by desc limit 0,1;
-閫氳繃鏈€楂樼殑骞沖潎宸ヨ祫寰楀埌瀵瑰簲鐨勯儴闂ㄧ紪鍙?
select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1);
-閫氳繃閮ㄩ棬缂栧彿寰楀埌閮ㄩ棬淇℃伅
select * from dept where deptno=(select deptno from emp group by deptno having avg(sal)=(select avg(sal) a from emp group by deptno order by a desc limit 0,1));
-瀛愭煡璇㈠彲浠ュ啓鍦ㄤ粈涔堜綅缃?
1.鍐欏湪where鎴杊aving鍚庨潰 褰撳仛鏌ヨ鏉′歡鐨勫€?
2.鍐欏湪鍒涘緩琛ㄨ鍙ョ殑鍚庨潰鐨勬椂鍊?鎶婃煡璇㈢粨鏋滀繚瀛樻垚涓€寮犳柊鐨勮〃
create table emp_20 as(select * from emp where deptno=20);
3.鍐欏湪from鐨勫悗闈?褰撴垚涓€涓櫄鎷熻〃 **蹇呴』鏈夊埆鍚?*
select * from emp where deptno=20;
select ename,sal from (select * from emp where deptno=20) newtabte;
###鍏寵仈鏌ヨ
-鍚屾椂鏌ヨ澶氬紶琛ㄧ殑鏁版嵁鎴愪負鍏寵仈鏌ヨ
1.鏌ヨ姣忎竴涓憳宸ョ殑濮撳悕鍜屽搴旂殑閮ㄩ棬鍚嶇О
select e.ename,d.ename from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
2.鏌ヨ鍦╪ew york宸ヤ綔鐨勫憳宸ヤ俊鎭?
select * from emp e,dept d where e.deptno=d.deptno and d.loc='new york';
3.鏌ヨ鍟嗗搧鏍囬鍜屾墍瀵瑰簲鐨勫垎绫誨悕绉?
select i.title,c.name from t_item i,t_item_category c where i.category_id=c.id;
###绗涘崱灏旂Н
-濡傛灉鍏寵仈鏌ヨ涓嶅啓鍏寵仈鍏崇郴鍒欑粨鏋滀負涓ゅ紶琛ㄧ殑涔樼Н,杩欎釜涔樼Н绉頒負绗涘崱灏旂Н
-绗涘崱灏旂Н涓轟竴绉嶉敊璇殑鏌ヨ缁撴灉,鍒囪宸ヤ綔涓笉瑕佸嚭鐜?
###绛夊€艱繛鎺ュ拰鍐呰繛鎺?
- 绛夊€艱繛鎺ュ拰鍐呰繛鎺ユ煡璇㈠埌鐨勫唴瀹逛竴鏍鳳紝閮戒負涓ゅ紶琛ㄤ腑鏈夊叧鑱斿叧绯葷殑鏁版嵁(浜ら泦閮ㄥ垎)
-绛夊€艱繛鎺? select * from A,B where A.x=B.x and A.age=18;
-鍐呰繛鎺? select * from A join B on A.x=B.x where A.age=18;
1.鏌ヨ姣忎竴涓憳宸ョ殑濮撳悕鍜屽搴旂殑閮ㄩ棬鍚嶇О
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;
###澶栬繛鎺?
- 鍐呰繛鎺ュ拰绛夊€艱繛鎺ユ煡璇㈠埌鐨勯兘鏄氦闆嗛儴鍒嗙殑鏁版嵁,澶栭摼鎺ユ煡璇㈠埌鐨勬槸鏌愪竴寮犺〃鐨勫叏閮ㄦ暟鎹?鍙﹀涓€寮犺〃鐨勪氦闆嗘暟鎹?
-宸﹀杩炴帴: select * from A left/right join B on A.x=B.x where A.age=18;
insert into emp (empno,ename) values(10010,'Tom');
1.鏌ヨ鎵€鏈夊憳宸ュ鍚嶅拰瀵瑰簲鐨勯儴闂ㄥ悕绉?
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno;
select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;
###鍏寵仈鏌ヨ鎬葷粨
- 鍏寵仈鏌ヨ鐨勬煡璇㈡柟寮忥細 绛夊€艱繛鎺?鍐呰繛鎺ュ拰澶栬繛鎺?
- 濡傛灉鎯蟲煡璇㈢殑鏁版嵁涓轟袱寮犺〃鐨勪氦闆嗘暟鎹嬌鐢ㄧ瓑鍊艱繛鎺ユ垨鍐呰繛鎺?鎺ㄨ崘)
- 濡傛灉鏌ヨ鐨勬暟鎹槸涓€寮犺〃鐨勫叏閮ㄦ暟鎹拰鍙﹀涓€寮犺〃鐨勪氦闆嗘暟鎹垯浣跨敤澶栬繛鎺?
###浣滀笟锛?
1. 姣忎釜閮ㄩ棬鐨勪漢鏁?鏍規嵁浜烘暟鎺掑簭
select deptno 閮ㄩ棬, count(*) 浜烘暟 from emp group by deptno order by 浜烘暟 desc;
2. 姣忎釜閮ㄩ棬涓紝姣忎釜涓葷鐨勬墜涓嬩漢鏁?
select deptno 閮ㄩ棬,mgr 涓葷, count(*) 浜烘暟 from emp where mgr is not null group by 閮ㄩ棬,涓葷;
--鍙︿竴绉嶆柟娉?
select d.deptno,e.mgr,count(e.ename)
from emp e right join dept d
on e.deptno=d.deptno
where e.mgr is not null
group by e.mgr,d.deptno;
--鏌ヨ姣忎釜閮ㄩ棬鐨勪漢鏁?缁撴灉涓寘鍚?0鍙烽儴闂?
select d.deptno,count(e.name)
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno;
3. 姣忕宸ヤ綔鐨勫鈎鍧囧伐璧?
select job 鑱屼綅, avg(sal) 骞沖潎宸ヨ祫 from emp group by 鑱屼綅;
4. 姣忓勾鐨勫叆鑱屼漢鏁?
select extract(year from hiredate) 骞翠喚, count(*) 浜烘暟 from emp group by 骞翠喚;
5. 灏戜簬绛変簬3涓漢鐨勯儴闂ㄤ俊鎭?
select * from dept where deptno in(select deptno from emp group by deptno having count(*)<=3);
--鍙︿竴绉嶆柟娉?
select d.*,count(e.ename) c
from emp e right join dept d
on e.deptno=d.deptno
group by d.deptno having c<=3;
6. 鎷挎渶浣庡伐璧勭殑鍛樺伐淇℃伅
select * from emp having min(sal)
7. 鍙湁涓€涓笅灞炵殑涓葷淇℃伅
select * from emp where empno in (select mgr from emp where mgr is not null group by mgr having count(*)=1);
8. 骞沖潎宸ヨ祫鏈€楂樼殑閮ㄩ棬缂栧彿
-寰楀埌鏈€楂樼殑骞沖潎宸ヨ祫
select avg(sal) a from emp group by deptno order by a desc limit 0,1;
-閫氳繃骞沖潎宸ヨ祫鍊煎幓鏌ヨ瀵瑰簲鐨勯儴闂ㄧ紪鍙?
select deptno from emp group by deptno having avg(sal)=(select avg(sal) c from emp group by deptno order by c desc limit 0,1);
9. 涓嬪睘浜烘暟鏈€澶氱殑浜猴紝鏌ヨ鍏朵釜浜轟俊鎭?
select * from emp where mgr=(select mgr from emp group by mgr having count(*)=(select count(*) c from emp group by mgr order by c desc limit 0,1));
10. 鎷挎渶浣庡伐璧勭殑浜虹殑淇℃伅
select * from emp having min(sal);
11. 鏈€鍚庡叆鑱岀殑鍛樺伐淇℃伅
select * from emp having max(hiredate);
12. 宸ヨ祫澶氫簬骞沖潎宸ヨ祫鐨勫憳宸ヤ俊鎭?
select * from emp having sal>(select avg(sal) from emp);
13. 鏌ヨ鍛樺伐淇℃伅锛岄儴闂ㄥ悕绉?
select e.*,d.dname from emp e,dept d where e.deptno=d.deptno;
14. 鍛樺伐淇℃伅锛岄儴闂ㄥ悕绉幫紝鎵€鍦ㄥ煄甯?
select e.*,d.dname,d.loc from emp e,dept d where e.deptno=d.deptno;
15. DALLAS 甯傛墍鏈夌殑鍛樺伐淇℃伅
select e.* from emp e join dept d on e.deptno=d.deptno and d.loc='DALLAS';
16. 鎸夊煄甯傚垎缁勶紝璁＄畻姣忎釜鍩庡競鐨勫憳宸ユ暟閲?
select d.loc ,count(e.ename) from emp e right join dept d on e.deptno=d.deptno group by d.loc ;
17. 鏌ヨ鍛樺伐淇℃伅鍜屼粬鐨勪富绠″鍚?
select e1.*,e2.ename 涓葷濮撳悕 from emp e1 join emp e2 on e1.mgr=e2.empno;
18. 鍛樺伐淇℃伅锛屽憳宸ヤ富绠″悕瀛楋紝閮ㄩ棬鍚?
select e1.*,e2.ename,d.ename from emp e1,emp e2,dept d where e1.mgr=e2.empno,e1.deptno=d.deptno;
19. 鍛樺伐鍜屼粬鎵€鍦ㄩ儴闂ㄥ悕
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
20. 妗堜緥锛氭煡璇mp琛ㄤ腑鎵€鏈夊憳宸ョ殑濮撳悕浠ュ強璇ュ憳宸ヤ笂绾ч瀵肩殑缂栧彿锛屽鍚嶏紝鑱屼綅锛屽伐璧?
select e1.ename,e2.empno,e2.ename,e2.job,e2.sal from emp e1,emp e2 where e1.mgr=e2.empno;
21. 妗堜緥锛氭煡璇mp琛ㄤ腑鍚嶅瓧涓病鏈夊瓧姣?K'鐨勬墍鏈夊憳宸ョ殑缂栧彿锛屽鍚嶏紝鑱屼綅浠ュ強鎵€鍦ㄩ儴闂ㄧ殑缂栧彿锛屽悕绉幫紝鍦闆潃
select e.empno,e.ename,e.job,e.deptno,d.dname,d.loc from emp e,dept d where e.ename not like '%k%' and e.deptno=d.deptno;
22. 妗堜緥锛氭煡璇ept琛ㄤ腑鎵€鏈夌殑閮ㄩ棬鐨勬墍鏈夌殑淇℃伅锛屼互鍙婁笌涔嬪叧鑱旂殑emp琛ㄤ腑鍛樺伐鐨勭紪鍙鳳紝濮撳悕锛岃亴浣嶏紝宸ヨ祫
select d.*,e.empno,e.ename,e.job,e.sal from emp e LEFT join dept d on d.deptno=e.deptno;
###琛ㄨ璁′箣鍏寵仈鍏崇郴
####涓€瀵逛竴
- 浠€涔堟槸涓€瀵逛竴鍏崇郴: 鏈堿B涓ゅ紶琛?鍏朵腑A琛ㄧ殑涓€鏉℃暟鎹搴擝琛ㄧ殑涓€鏉℃暟鎹?鍚屾椂B琛ㄧ殑涓€鏉℃暟鎹篃瀵瑰簲A琛ㄤ腑鐨勪竴鏉℃暟鎹?
- 搴旂敤鐨勫満鏅?鐢ㄦ埛琛ㄥ拰鐢ㄦ埛淇℃伅鎵╁睍琛?
-- 鐢ㄦ埛鍚?瀵嗙爜 鏄電О 澶村儚 鎬у埆 鎵嬫満鍙?鍦闆潃 閭....
--澶栭敭:琛ㄤ腑鐢ㄤ簬寤虹珛鍏崇郴鐨勫瓧娈電О涓哄閿?涓€寮犺〃鏈夊彲鑳芥湁澶氫釜澶栭敭,浣嗗彧鑳芥湁涓€涓富閿?
-- 濡備綍寤虹珛鍏崇郴:鍦ㄤ粠琛ㄤ腑娣誨姞澶栭敭鎸囧悜涓昏〃鐨勪富閿?
--缁冧範:鍒涘緩琛ㄤ繚瀛樹互涓嬫暟鎹?
鐢ㄦ埛鍚峸ukong 瀵嗙爜abcd 鏄電О鎮熺┖ 鎬у埆鐢?鍦闆潃澶у攼
wzt admin 姝﹀垯澶?濂?澶ч檰
Superman 123456 瓒呬漢 鐢?閾佸箔
create table user(id int primary key auto_increment,username varchar(10),password varchar(10));
create table userinfo(userid int,nick varchar(10),gender varchar(5), loc varchar(20));
insert into user values(null,'wukong','abcd'),(null,'wzt','admin'),(null,'superman','123456');
insert into userinfo values(1,'鎮熺┖','鐢?,'鑺辨灉灞?),(2,'姝﹀垯澶?,'濂?,'澶ч檰'),(3,'瓒呬漢','鐢?,'閾佸箔');
1.鏌ヨ姣忎釜鐢ㄦ埛瀵瑰簲鐨勬樀绉?
select u.username,ui.nickname from user u join userinfo ui on u.id=ui.id;
2.鏌ヨ瓒呬漢鐨勭敤鎴峰悕
select ui.nickname, u.username from user u join userinfo ui on u.id=ui.id where ui.nickname='瓒呬漢';
3.鏌ヨ鎬у埆鏄敺鐨勭敤鎴峰悕鍜屽瘑鐮?
select u.username,u.pwd from user u join userinfo ui on u.id=ui.id where ui.gavider='鐢?;
4.鏌ヨ鏄惁瀛樺湪 鐢ㄦ埛鍚?wukong 瀵嗙爜:abc 鐨勭敤鎴?鏌ヨ绗﹀悎鏉′歡鐨勬暟鎹潯鏁?
select count(*) from user where username='wukong' and pwd='abc';
####涓€瀵瑰
--浠€涔堟槸涓€瀵瑰:鏈堿B涓ゅ紶琛?A琛ㄤ腑鐨勪竴鏉℃暟鎹搴擝琛ㄤ腑鐨勫鏉℃暟鎹?鍚屾椂B琛ㄤ腑鐨勪竴鏉℃暟鎹搴擜琛ㄤ腑鐨勪竴鏉?
--搴旂敤鍦烘櫙:鐢ㄦ埛琛ㄥ拰閮ㄩ棬琛?鍟嗗搧琛ㄥ拰鍒嗙被琛?
--濡備綍鍒涘緩鍏崇郴:鍦ㄥ鐨勪竴绔坊鍔犲閿寚鍚戝彟澶栦竴寮犺〃鐨勪富閿?
--缁冧範:鍒涘緩emp琛ㄥ拰dept琛?
create table emp(id int primary key auto_increment,name varchar(15),deptno int );
create table dept(id int primary key auto_increment,name varchar(15));
insert into dept values(null ,'绁炰粰'),(null,'濡栨€?);
insert into emp values(null,'鐚叓鎴?,1),(null,'鐧介绮?,2),(null,'铚樿洓绮?,2);
1.鏌ヨ姣忎釜鍛樺伐鐨勫鍚嶅拰瀵瑰簲鐨勯儴闂ㄥ悕绉?
select e.name,d.name from emp e join dept d on e.deptno=d.id;
2.鏌ヨ濡栨€儴鐨勫憳宸ュ鍚?
select d.name, e.name from dept d join emp e on e.deptno=d.id where d.name='濡栨€?;
####澶氬澶?
--浠€涔堟槸澶氬澶?鏈堿B涓ゅ紶琛?A琛ㄤ腑鐨勪竴鏉℃暟鎹搴攄e
--搴旂敤鍦烘櫙:鑰佸笀琛ㄥ拰瀛︾敓琛?
--濡備綍寤虹珛鍏崇郴:閫氳繃绗笁绔犲叧绯昏〃淇濆瓨涓ゅ紶涓昏〃鐨勫叧绯?
--缁冧範:
鍒涘緩鑰佸笀琛?瀛︾敓琛ㄥ拰鍏崇郴琛?
create table teacher(id int primary key auto_increment,name varchar(20));
create table student(id int primary key auto_increment,name varchar(20));
create table t_s(tid int,sid int);
1.寰€浠ヤ笂琛ㄤ腑淇濆瓨鑻嶈€佸笀鐨勫鐢熷皬鍒樺拰灏忎附锛屼紶濂囪€佸笀鐨勫鐢熷皬鍒橈紝灏忕帇鍜屽皬涓?
insert into teacher values(null,'鑻嶈€佸笀');
insert into teacher values(null,'浼犲鑰佸笀');
insert into student values(null,'灏忓垬');
insert into student values(null,'灏忕帇');
insert into student values(null,'灏忎附');
insert into t_s value(1,1);
insert into t_s value(1,2);
insert into t_s value(2,1);
insert into t_s value(2,2);
insert into t_s value(2,3);
2.鏌ヨ姣忎釜瀛︾敓濮撳悕鍜屽搴旂殑鑰佸笀濮撳悕
select s.*,t.name from student s join teacher t join t_s ts on s.id=ts.sid and t.id=ts.tid;
鎴栬€?select s.name,t.name from student s join t_s ts on s.id=ts.sid join teacher t on t.id=ts.tid;
3.鏌ヨ鑻嶈€佸笀鐨勫鐢熼兘鏈夎皝
select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sid where t.name='鑻嶈€佸笀';
4.鏌ヨ灏忎附鐨勮€佸笀鏄皝
select s.name,t.name from student s join t_s ts on s.id=ts.sid join teacher t on t.id=ts.tid where s.name='灏忎附';
###鑷叧鑱?
- 鍦ㄥ綋鍓嶈〃涓坊鍔犲閿?澶栭敭鐨勫€兼寚鍚戝綋鍓嶈〃鐨勪富閿?杩欑鍏寵仈鏂瑰紡绉頒負鑷叧鑱?
create table person(id int primary key auto_increment,name varchar(15),mgr int);
淇濆瓨浠ヤ笅鏁版嵁:濡傛潵->鍞愬儳->鎮熺┖->鐚村唇瀛?
insert into person values(null,'濡傛潵',null),(null,'鍞愬儳',1),(null,'鎮熺┖',2),(null,'鐚村唇瀛?,3);
1.鏌ヨ姣忎釜浜虹殑鍚嶅瓧鍜屽拰涓婄駭鐨勫悕瀛?
select p.name,m.name 涓婄駭 from person p left join person m on p.mgr=m.id;
###琛ㄨ璁℃渚?鏉冮檺绠＄悊
--瀹炵幇鏉冮檺绠＄悊鍔熻兘闇€瑕佸噯澶囦笁寮犱富琛ㄥ拰涓ゅ紶鍏崇郴琛?
--鍒涘緩琛?
create table user(id int,name varchar(10));
create table role(id int,name varchar(10));
create table module(id int,name varchar(10));
create table u_r(uid int,rid int);
create table r_m(rid int,mid int);
--鎻掑叆鏁版嵁:
insert into user values(1,'鍒樺痙鍗?),(2,'鍑ゅ');
insert into role values(1,'鐢鋒父瀹?),(2,'鐢蜂細鍛?),(3,'濂蟲父瀹?),(4,'濂崇鐞嗗憳');
insert into module values(1,'鐢鋒祻瑙?),(2,'鐢峰彂甯?),(3,'濂蟲祻瑙?),(4,'濂沖彂甯?),(5,'濂沖垹甯?);
-淇濆瓨瑙掕壊鍜屾潈闄愮殑鍏崇郴:
insert into r_m values(1,1),(2,1),(2,2),(3,3),(4,3),(4,4),(4,5);
-淇濆瓨鐢ㄦ埛鍜岃鑹茬殑鍏崇郴 鍒樺痙鍗庣敺浼氬憳鍜屽コ娓稿 鍑ゅ:濂崇鐞嗗憳鍜岀敺娓稿
insert into u_r values(1,2),(1,3),(2,1),(2,4);
1.鏌ヨ姣忎釜鐢ㄦ埛鐨勬潈闄愭湁鍝簺
select u.name 鐢ㄦ埛鍚? m.name 鏉冮檺 from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id;
2.鏌ヨ鍑ゅ鐨勬潈闄?
select u.name 鐢ㄦ埛鍚?,m.name 鏉冮檺 from user u join u_r ur on u.id=ur.uid join r_m rm on ur.rid=rm.rid join module m on rm.mid=m.id where u.name='鍑ゅ';
3.鏌ヨ鎷ユ湁鐢鋒祻瑙堟潈闄愮殑鐢ㄦ埛鏈夎皝
select m.name 鏉冮檺,u.name 鐢ㄦ埛鍚?from module m join r_m rm on m.id=rm.mid join u_r ur on rm.rid=ur.rid join user u on ur.uid=u.id where m.name='鐢鋒祻瑙?;
--------------------------------------------------------------------------------------------------
create table trade(tradeyid int primary key auto_increment,tradedate datetime,aomey int,directid int,way varchar(10));
create table direction(id int primary key auto_increment, relationship varchar(15),name varcahr(15),gender varchar(5));
insert into trade values(null,'2018-02-15',150,1,'寰俊绾㈠寘');
insert into trade values(null,'2018-01-11',120,2,'鐜伴噾绾㈠寘');
insert into trade values(null,'2017-08-15',-130,3,'寰俊绾㈠寘');
insert into trade values(null,'2018-05-15',66,4,'鏀粯瀹濈孩鍖?);
insert into trade values(null,'2018-04-15',-88,6,'鐜伴噾绾㈠寘');
insert into trade values(null,'2018-01-15',99,5,'寰俊绾㈠寘');
insert into trade values(null,'2019-02-15',88,7,'鐜伴噾绾㈠寘');
insert into trade values(null,'2018-09-15',166,11,'鏀粯瀹濈孩鍖?);
insert into trade values(null,'2018-04-15',-88,8,'鐜伴噾绾㈠寘');
insert into trade values(null,'2018-01-15',99,9,'寰俊绾㈠寘');
insert into trade values(null,'2017-02-15',88,10,'鐜伴噾绾㈠寘');
insert into trade values(null,'2018-09-15',-23,12,'鏀粯瀹濈孩鍖?);
insert into direction values(null,'浜叉垰','濡瑰','濂?);
insert into direction values(null,'鍚屼簨','鍚屼簨1','濂?);
insert into direction values(null,'鏈嬪弸','鏈嬪弸1','鐢?);
insert into direction values(null,'鍚屼簨','鍚屼簨2','鐢?);
insert into direction values(null,'鏈嬪弸','鏈嬪弸2','濂?);
insert into direction values(null,'浜叉垰','鍝ュ摜','鐢?);
insert into direction values(null,'浜叉垰','寮熷紵','鐢?);
insert into direction values(null,'鏈嬪弸','鏈嬪弸3','濂?);
insert into direction values(null,'鍚屼簨','鍚屼簨3','鐢?);
insert into direction values(null,'浜叉垰','濮愬','濂?);
insert into direction values(null,'鏈嬪弸','鏈嬪弸4','鐢?);
insert into direction values(null,'鍚屼簨','鍚屼簨4','鐢?);
insert into direction values(null,'鏈嬪弸','鏈嬪弸5','濂?);
insert into direction values(null,'浜叉垰','琛ㄥ摜','鐢?);
insert into direction values(null,'浜叉垰','琛ㄥ紵','鐢?);
insert into direction values(null,'浜叉垰','瀚傚瓙','濂?);
insert into direction values(null,'鍚屼簨','鍚屼簨5','鐢?);
3.缁熻浠?018骞存槬鑺?2鏈?5鏃?鍒扮幇鍦ㄦ敹鐩?鏀剁泭=鏀跺叆-鏀嚭) 澶氬皯绾㈠寘?
select sum(aomey) from trade where tradedate>'2018-02-15';
4.鏌ヨ浠?018骞?2鏈?5鏃?鍒扮幇鍦ㄧ孩鍖呭ぇ浜?00鍏冪殑鎵€鏈夊コ鎬т翰鎴氱殑鍚嶅瓧鍜屽搴旂殑绾㈠寘閲戦
select d.name,t.aomey from trade t join direction d on t.directid=d.id where t.aomey>100 and d.relationship='浜叉垰';
select d.name,t.aomey
from trade t join direction d
on t.directid=d.id
where time>str_to_date('2018骞?鏈?5鍙?,'%Y骞?m鏈?d鍙?)
and t.money not between -100
and 100 and d.gender='濂?
and d.relationship='浜叉垰';
5.鏌ヨ缁熻 鐜伴噾 ,鏀粯瀹?寰俊涓変釜骞沖彴鍒嗗埆鏀跺埌鐨勭孩鍚ф€婚噾棰?
select way, sum(aomey) from trade where aomey>0 group by way ;
1. 寤虹珛涓€涓憳宸ヤ俊鎭〃employee锛岃〃涓璱d锛堝憳宸ヤ唬鐮侊級銆乻ex锛堝憳宸ユ€у埆锛夈€乶ame锛堝鍚嶏級銆乨epartmentid锛堥儴闂ㄤ唬鐮侊級銆?
address锛堝湴鍧€锛夈€乥irthdate锛堢敓鏃ワ級銆乸ostcode锛堥偖缂栵級銆乻alary锛堣柂姘達級銆亀orkdate锛堝叆鑱屾棩鏈燂級銆乺emark锛堝娉ㄤ俊鎭級锛?
鍏朵腑postcode銆乺emark 鍙互涓虹┖锛岃柂姘撮渶瑕佷紵number绫誨瀷锛岀敓鏃ャ€佸叆鑱屾棩鏈熶負date绫誨瀷锛屼互鍛樺伐浠ｇ爜涓婚敭
create table employee(
id int primary key auto_increment,
sex char(4),
name vaechar(15),
departmentid int,
address varchar(20),
birthdate date,
postcode int null,
salary int,
workdate date,
remark text null
);
####瑙嗗浘
-鏁版嵁搴撲腑鍖呭惈澶氱瀵矽薄,琛ㄥ拰瑙嗗浘閮芥槸鏁版嵁搴撲腑鐨勫璞?瑙嗗浘鍙互鐞嗚В鎴愪竴寮犺櫄鎷熺殑琛?瑙嗗浘鏈川灏辨槸鍙栦唬浜嗕竴娈祍ql鏌ヨ璇彞
-涓轟粈涔堜嬌鐢ㄨ鍥?鍥犱負鏈変簺鏁版嵁鐨勬煡璇㈤渶瑕佷簺澶ч噺鐨剆ql鏌ヨ璇彞,姣忔涔﹀啓姣旇緝楹葷儲,閫氳繃浣跨敤瑙嗗浘鐩稿綋浜庢妸澶ч噺鐨剆ql鏌ヨ璇彞杩涜淇?
瀛?涓嬫浠庤鍥句腑鏌ヨ灏變笉鐢ㄥ啀娆′功鍐欏ぇ閲弒ql璇彞,浠庤€屾彁楂樺紑鍙戞晥鐜?
-瑙嗗浘鏍煎紡:create view 瑙嗗浘鍚?as (瀛愭煡璇?;
create view v_emp_10 as (select * from emp where deptno=10);
delete from emp where sal=1300;
select * from v_emp_10;
1.鍒涘緩涓€涓病鏈夊伐璧勭殑瑙嗗浘
create view v_emp_nosal as (select empno,ename,comm,mgr from emp);
2.鍒涘緩瑙嗗浘,瑙嗗浘涓樉绀烘瘡涓儴闂ㄧ殑宸ヨ祫鎬誨拰,骞沖潎宸ヨ祫,鏈€楂樺伐璧?鏈€浣庡伐璧?
create view v_emp_sum as (select sum(sal),avg(sal),max(sal),min(sal) from emp group by deptno);
-瑙嗗浘鐨勫垎绫?
1.绠€鍗曡鍥?鍒涘緩瑙嗗浘鐨勬椂鍊欎笉鍖呭惈:鍘婚噸,鍒嗙粍,鍑芥暟,鍏寵仈鏌ヨ鐨勮鍥劇О涓虹畝鍗曡鍥?鍙互瀵矽鍥句腑鐨勬暟鎹繘琛屽鍒犳敼鏌?
2.澶嶆潅瑙嗗浘:鍜岀畝鍗曡鍥劇浉鍙?鍙兘杩涜鏌ヨ鎿嶄綔
-绠€鍗曡鍥劇殑澧炲垹鏀規搷浣?鎿嶄綔鏂瑰紡鍜屾搷浣渢able涓€鏍?
-鎻掑叆鏁版嵁
insert into v_emp_10 (empno,ename,deptno) values (10011,'鎮熺┖',10);
insert into v_emp_10 (empno,ename,deptno) values (10012,'鍏垝',20);(鏁版嵁姹℃煋)
-鏁版嵁姹℃煋:寰€瑙嗗浘涓彃鍏ヤ竴鏉″湪瑙嗗浘涓笉瀹炵幇浣嗘槸鍦ㄥ師琛ㄤ腑鏄劇ず鐨勬暟鎹?绉頒負鏁版嵁姹℃煋
-閫氳繃with check option 鍏抽敭瀛楄В鍐蟲暟鎹薄鏌撻棶棰?
create view v_emp_20 as (select * from emp where deptno=20) with check option;
-娴嬭瘯:
insert into v_emp_20 (empno,ename,deptno) values (10013,'鍒樺',20);//鎴愬姛
insert into v_emp_20 (empno,ename,deptno) values (10014,'鍒樺',30);//澶辮觸
-鍒犻櫎鍜屼慨鏀?鍙兘鎿嶄綔瑙嗗浘涓瓨鍦ㄧ殑鏁版嵁
-鍒悕:濡傛灉鍒涘緩瑙嗗浘鏃朵嬌鐢ㄥ埆鍚?鍒欐搷浣滆鍥炬椂鍙兘浣跨敤鍒悕
create view v_emp_30 as (select ename name from emp where deptno=30);
select * from v_emp_30 where ename='james';
###绾︽潫
-绾︽潫:绾︽潫鏄垱寤鴻〃鏃剁粰瀛楁娣誨姞鐨勯檺鍒舵潯浠?
####闈炵┖绾︽潫 not null
-瀛楁鍊間笉鑳戒負null
create table t1(id int ,age int not null);
insert into t1 values(1,18);//鎴愬姛
insert into t1 values(2,null)//澶辮觸
####鍞竴绾︽潫 unique
-瀛楁鐨勫€間笉鑳介噸澶?
create table t2(id int,age int unique);
insert into t2 values(1,20);//鎴愬姛
insert into t2 values(2,20);//澶辮觸
####涓婚敭绾︽潫
-瀛楁鐨勫€煎敮涓€涓旈潪绌?
-鍒涘緩琛ㄦ椂娣誨姞涓婚敭绾︽潫:create table t3(id int primary key ,age int);
-鍒涘緩琛ㄤ箣鍚庢坊鍔犱富閿害鏉?
create table t3(id int,name varchar(10));
alter table t3 add primary key(id);
-鍒犻櫎涓婚敭绾︽潫
alter table t3 drop primary key;
####鑷
-鏁闆€煎彧澧炰笉鍑?
-浠庡巻鍙叉渶澶у€肩殑鍩虹涓?1
-瀛楁鍊艱祴鍊間負null鐨勬椂鍊欒嚜鍔?1
-浣跨敤dalete 鍒犻櫎鍏ㄨ〃鏁版嵁 鑷鏁闆€間笉鍙?
-浣跨敤truncate 鑷娓呴浂
####榛樿绾︽潫
-缁欏瓧娈墊坊鍔犻粯璁ゅ€?
create table t4(id int,age int default 10);
insert into t4 values(1,20);//榛樿鍊間笉鐢熸晥
insert into t4 values(2,null);//榛樿鍊間笉鐢熸晥
insert into t4 (id) values(3);//榛樿鍊肩敓鏁?
####妫€鏌ョ害鏉?check
-璇硶鏀寔,浣嗘槸娌℃湁鏁堟灉
create table t5(id int,ahe int check(age>10));
insert into t5 values (1,5);//渚濈劧鎴愬姛,check娌℃湁鏁堟灉
####澶栭敭绾︽潫
-澶栭敭绾︽潫鐨勪綔鐢?涓轟簡淇濊瘉涓や釜琛ㄤ箣闂寸殑鍏崇郴姝ｇ‘寤虹珛
1.鎻掑叆鏁版嵁鏃跺閿€煎彲浠ヤ負null,鍙互閲嶅,浣嗘槸涓嶈兘鏄彟澶栦竴寮犺〃涓嶅瓨鍦ㄧ殑鏁版嵁
2.琚緷璧栫殑琛ㄤ笉鑳借鍏堝垹闄?
3.琚緷璧栫殑鏁版嵁涓嶈兘琚厛鍒犻櫎
-濡備綍浣跨敤澶栭敭:
1.鍒涘緩閮ㄩ棬琛?
create table dept(id int primary key auto_increment,name vaechar(10));
2.鍒涘緩鍛樺伐琛?
create table emp(id int primary key auto_increment,name varchar(10),deptid int ,constraint fk_dept foreign key(deptid) references dept(id));
-浠嬬粛:
constraint 绾︽潫鍚嶇О foreign key(澶栭敭瀛楁鍚? references 琛ㄥ悕(瀛楁鍚?
-娴嬭瘯:
insert into dept values(null,'绁炰粰'),(null,'濡栨€?);
insert into dept values(null,'鍏垝',1);//鎴愬姛
insert into dept values(null,'鎮熺┖',1);//鎴愬姛
insert into dept values(null,'瓒呬漢',3);//澶辮觸,娌℃湁3
drop table dept;//澶辮觸
delete from dept where id=2;//鎴愬姛
delete from dept where id=1;//澶辮觸
###绱㈠紩
-浠€涔堟槸绱㈠紩:绱㈠紩鏄暟鎹簱涓彁楂樻煡璇㈡晥鐜囩殑鎶€鏈?绫諱技浜庡瓧鍏哥殑鐩綍
-涓轟粈涔堜嬌鐢ㄧ儲寮?濡傛灉涓嶄嬌鐢ㄧ儲寮曟暟鎹細闆舵暎鐨勪繚瀛樺湪姣忎竴涓鐩樺潡褰撲腑,鏌ヨ鏁版嵁鏃堕渶瑕佹尐涓殑閬嶅巻姣忎竴涓鐩樺潡鏌ユ壘鏁版嵁,濡傛灉鏁版嵁閲忚秴绾уぇ,閬?
鍘嗘瘡涓€涓鐩樺潡鏄歡闈炲父鑰楁椂鐨勪簨浠?娣誨姞绱㈠紩鍚?浼氬皢纾佺洏鍧椾互鏍戠姸缁撴瀯杩涜淇濆瓨,鏌ヨ鏁版嵁鏃朵細浼氭湁鐩殑鎬х殑璁塊棶閮ㄥ垎纾佺洏鍧?鍥犱負璁塊棶鐨勭鐩樺潡
鏁伴噺闄嶄綆鎵€浠ヨ兘璧峰埌鎻愰珮鏌ヨ鏁堢巼鐨勪綔鐢?
-绱㈠紩鏄秺澶氳秺濂戒箞?
涓嶆槸,鍥犱負绱㈠紩浼氬崰纾佺洏绌洪棿,閫氳繃鏌愪釜瀛楁鍒涘緩鐨勭儲寮曞彲鑳芥案杩滅敤涓嶄笂,鍒欒繖涓儲寮曞畬鍏ㄦ病鏈夊瓨鍦ㄧ殑鎰忎箟,鍙渶瑕佸鏌ヨ鏃堕绻佷嬌鐢ㄧ殑瀛楁鍒涘緩绱㈠紩
-鏈夌儲寮曞氨涓€瀹氬ソ鍚?
涓嶄竴瀹?濡傛灉鏁版嵁閲忓皬浣跨敤绱㈠紩鍙嶈€屼細闄嶄綆鏌ヨ鏁堢巼.
-绱㈠紩鐨勫垎绫?浜嗚В)
1.鑱氶泦绱㈠紩(鑱氱皣绱㈠紩):閫氳繃涓婚敭鍒涘緩鐨勭儲寮曚負鑱氶泦绱㈠紩,娣誨姞浜嗕富閿害鏉熺殑琛ㄤ細鑷姩娣誨姞鑱氶泦绱㈠紩,鑱氶泦绱㈠紩鐨勬爲妗╃粨鏋勪腑淇濆瓨浜嗘暟鎹?
2.闈炶仛闆嗙儲寮?閫氳繃闈炰富閿瓧娈靛垱寤虹殑绱㈠紩鍙綔闈炶仛闆嗙儲寮?鏍戞々缁撴瀯涓彧淇濆瓨浜嗘暟鎹墍鍦ㄧ鐩樺潡鐨勫湴鍧€骞舵病鏈夋暟鎹?
-鍒涘緩绱㈠紩鐨勬牸寮?
create index 绱㈠紩鍚?on 琛ㄥ悕(瀛楁鍚峓(瀛楃闀垮害)]);
create index i_item_title on item2(title);
-鍒涘緩瀹屽悗缁х畫鎵ц
select * from item2 where title='100'; //鐪嬩竴涓嬭€楁椂 0.02绉?
-鏌ョ湅绱㈠紩:
show index from item2;
-鍒犻櫎绱㈠紩
drop index 绱㈠紩鍚?on 琛ㄥ悕;
drop index i_item_title on item2;
-澶嶅悎绱㈠紩
閫氳繃澶氫釜瀛楁鍒涘緩鐨勭儲寮曠О涓哄鍚堢儲寮?
-鏍煎紡:create index 绱㈠紩鍚?on 琛ㄥ悕(瀛楁1,瀛楁2);
棰戠箒浣跨敤澶氫釜瀛楁杩涜鏁版嵁鏌ヨ鏃朵負浜嗘彁楂樻煡璇㈡晥鐜囧彲浠ュ垱寤哄鍚堢儲寮?
select * from item2 where title='100' and price<100;
create index i_item_title_price on item(title,price);
-鎬葷粨:
1.绱㈠紩鏄敤浜庢彁楂樻煡璇㈡晥鐜囩殑鎶€鏈?绫諱技鐩綍
2.绱㈠紩浼氬崰鐢ㄧ鐩樼┖闂翠笉鏄秺澶氳秺濂?
3.濡傛灉鏁版嵁閲忓皬鐨勮瘽,娣誨姞绱㈠紩浼氶檷浣庢煡璇㈡晥鐜?
4.灏介噺涓嶈鍦ㄩ绻佹敼鍔ㄧ殑琛ㄤ笂娣誨姞绱㈠紩
###浜嬪姟
-鏁版嵁搴撲腑鎵цsql璇彞鐨勬渶灏忓伐浣滃崟鍏?淇濊瘉浜嬪姟涓殑澶氭潯sql鍏ㄩ儴鎴愬姛鎴栧叏閮ㄥけ璐?
-浜嬪姟鐨凙CID鐗規€?
1.Atomicity:鍘熷瓙鎬? 鏈€灏忎笉鍙媶鍒?淇濊瘉鍏ㄩ儴鎴愬姛鎴栧叏閮ㄥけ璐?
2.Consistency:涓€鑷存€? 浠庝竴涓竴鑷寸姸鎬佸埌鍙﹀涓€涓竴鑷寸姸鎬?
3.Isolation:闅旂鎬? 澶氫釜浜嬪姟涔嬮棿浜掔浉闅旂浜掍笉褰卞搷
4.Durability:鎸佷箙鎬? 浜嬪姟鎻愪氦鍚庢暟鎹寔涔呬繚瀛樺埌鏁版嵁搴撴枃浠朵腑
-浜嬪姟鐩稿叧鎸囦護:
-鏌ョ湅鑷姩鎻愪氦鐘舵€?show variables like '%autocommit%'
-淇敼鑷姩鎻愪氦:set autocommit=0/1;
-鎻愪氦:commit;
-鍥炴粴:rollback;
-淇濆瓨鍥炴粴鐐?savepoint s1;
-鍥炴粴鍒版寚瀹氬洖婊氱偣:rollback to s1;
###group-concat() 鍒嗙粍杩炴帴鍑芥暟
1.鏌ヨ鍛樺伐琛ㄤ腑 姣忎釜閮ㄩ棬鐨勬墍鏈夊憳宸ュ伐璧?
select deptno,group_concat(sal) from emp group by deptno;
2.鏌ヨ鍛樺伐琛ㄤ腑,姣忎釜閮ㄩ棬鐨勫憳宸ュ鍚嶅拰瀵瑰簲鐨勫伐璧?瑕佹眰鏄劇ず鍒頒竴鏉℃暟鎹腑
select deptno,group-concat(ename,sal) from emp group by deptno;
###闈㈣瘯棰?
鏈変釜瀛︾敓琛╯tudent (id,name,subject,score)
淇濆瓨浠ヤ笅鏁版嵁:
寮犱笁 璇枃 66 , 寮犱笁 鏁闆 77 , 寮犱笁 鑻辮 55 , 寮犱笁 浣撹偛 77
鏉庡洓 璇枃 59 , 鏉庡洓 鏁闆 88 , 鏉庡洓 鑻辮 78 , 鏉庡洓 浣撹偛 95
鐜嬩簲 璇枃 75 , 鐜嬩簲 鏁闆 54 , 鐜嬩簲 鑻辮 98 , 鐜嬩簲 浣撹偛 88
create table student(id int primary key auto_increment,name varchar(15),subject varchar(5),score int not null);
insert into student values(null,'寮犱笁','璇枃',66);
insert into student values(null,'寮犱笁','鏁闆',77);
insert into student values(null,'寮犱笁','鑻辮',55);
insert into student values(null,'寮犱笁','浣撹偛',77);
insert into student values(null,'鏉庡洓','璇枃',59);
insert into student values(null,'鏉庡洓','鏁闆',88);
insert into student values(null,'鏉庡洓','鑻辮',78);
insert into student values(null,'鏉庡洓','浣撹偛',95);
insert into student values(null,'鐜嬩簲','璇枃',75);
insert into student values(null,'鐜嬩簲','鏁闆',54);
insert into student values(null,'鐜嬩簲','鑻辮',98);
insert into student values(null,'鐜嬩簲','浣撹偛',88);
1.鏌ヨ姣忎釜浜虹殑骞沖潎鍒?浠庡ぇ鍒闆皬鎺掑簭
select name, avg(score) 骞沖潎鍒?from student group by name order by 骞沖潎鍒?desc;
2.姣忎釜浜虹殑濮撳悕 绉戠洰 鎴愮嘩 涓€琛屾樉绀哄嚭鏉?
select name,group_concat(subject,score) from student group by name;
3.鏌ヨ姣忎釜浜虹殑鏈€楂樺拰鏈€浣庡垎
select name,max(score) 鏈€楂樺垎,min(score) 鏈€浣庡垎 from student group by name;
4.鏌ヨ姣忎釜浜轟笉鍙婃牸鐨勭鐩互鍙婂垎鏁闆拰鍙婃牸鐨勭鐩暟閲?
select name,group_concat(subject,':',score+'') , count(*) from student where score<60 group by name;
####JDBC
- JDBC:Java DataBase Connectivity.java鏁版嵁搴撹繛鎺?瀹為檯涓妀dbc鏄痡ava涓殑涓€濂楀拰鏁版嵁搴撹繘琛屼氦浜掔殑api(application program interface 搴旂敤绋嬪簭缂栫▼鎺ュ彛)
- 涓轟粈涔堜嬌鐢↗DBC:鍥犱負Java绋嬪簭鍛橀渶瑕佽繛鎺ュ绉嶆暟鎹簱,涓轟簡閬垮厤姣忎竴绉嶆暟鎹簱閮藉涔犱竴濂楁柊鐨刟pi,Sun鍏徃鎻愬嚭浜嗕竴涓狫DBC鐨勬帴鍙?鍚勪釜鏁版嵁搴撶殑鍘傚晢鏍規嵁姝?
鎺ュ彛鍐欏疄鐜扮被锛堥┍鍔級锛岃繖鏍穓ava绋嬪簭鍛樺彧闇€瑕佹帉鎻DBC鎺ュ彛鐨勮皟鐢紝鍗沖彲璁塊棶浠諱綍鏁版嵁搴撱€?
###濡備綍浣跨敤JDBC杩炴帴MySQL鏁版嵁搴?
1. 鍒涘緩Maven宸ョ▼
2. 鐧誨綍maven绉佹湇鐨勭綉绔?maven.tedu.cn 澶栫綉 浠撳簱鏈嶅姟
棣栭〉鎼滅儲MySQL 鎵懼埌 5.1.6鐗堟湰 鎶婂潗鏍囧鍒跺埌pom.xml涓?
3. 鍒涘緩Demo01.java绫?娣誨姞main鏂規硶
-閫氳繃浠ヤ笅浠ｇ爜杩炴帴鏁版嵁搴撳苟鎵цsql璇彞
//1.娉ㄥ唽椹卞姩
Class.forName("com.mysql.jdbc.Driver");
//2.鑾峰彇閾炬帴瀵矽薄
Connection conn=DriverManager.getConnection
("jdbc:mysql://localhost:3306/db1",
"root","root");
//3.鍒涘緩SQL鎵ц瀵矽薄
Statement stat=conn.createStatement();
//4.鎵цSQL
String sql="create table jdbc(id int,name varchar(10))";
stat.execute(sql);
System.out.println("鍒涘緩瀹屾垚!");
//5.鍏抽棴璧勬簮
stat.close();
conn.close();
###JUnit Test鍗曞厓娴嬭瘯
-鍦ㄦ棤鍙傛棤杩斿洖鍊肩殑鏂規硶涓婇潰娣誨姞@Test娉ㄨВ锛岄€氳繃鍙抽敭 run as 鎵ц 缁胯壊浠ｈ〃鎵ц鎴愬姛锛岀孩鑹叉墽琛屽け璐?
###鎵цSQL鐨勬柟娉?
-execute();姝ゆ柟娉曞彲浠ユ墽琛屼換鎰忕殑SQl 浣嗘槸鎺ㄨ崘鎵цDDL (鏁版嵁瀹氫箟璇█ create,drop,alter,truncate) 杩斿洖鍊間負boolean鍊?杩斿洖鍊間唬琛ㄧ殑鏄槸鍚︽湁缁撴灉闆?鍙湁鏌ヨ璇彞鏈夌粨鏋滈泦)
-executeUpdate(); 澧炲垹鏀規搷浣滃叏閮ㄤ嬌鐢ㄦ鏂規硶 杩斿洖鍊間負int 琛ㄧず鐢熸晥鐨勮鏁?
-executeQuery(); 姝ゆ柟娉曟墽琛屾煡璇㈡搷浣?杩斿洖ResultSet 閫氳繃while寰幆閬嶅巻
浠ｇ爜濡備笅锛?
//鎵ц鏌ヨ 寰楀埌鐨勭粨鏋滃皝瑁呭湪浜唕esultset涓?
ResultSet rs=stat.executeQuery(sql);
//閬嶅巻缁撴灉闆?
while(rs.next()){
int empno=rs.getInt("empno");
String name=rs.getString("ename");
double sal=rs.getDouble("sal");
System.out.println(empno+","+name+","+sal);
}
-浠嶳esultSet涓幏鍙栨暟鎹殑涓ょ鏂瑰紡:
1. 閫氳繃琛ㄥ瓧娈電殑鍚嶇О鑾峰彇
2. 閫氳繃缁撴灉涓瓧娈電殑浣嶇疆鑾峰彇 锛堜互鏈鏌ヨ鐨勫瓧娈甸『搴忎負鍑嗭紝骞堕潪琛ㄥ瓧娈電殑椤哄簭锛?
####鑷畾涔夋ā鏉誇唬鐮?
window->preferense->java->editor->templates->new
###鏁版嵁搴撹繛鎺ユ睜 DBCP
- 涓轟粈涔堢敤杩炴帴姹狅細濡傛灉娌℃湁杩炴帴姹犱竴涓囨璇鋒眰浼氬搴斾竴涓囨鍜屾暟鎹簱鏈嶅姟鍣ㄧ殑杩炴帴鍜屾柇寮€杩炴帴鎿嶄綔锛屼嬌鐢ㄨ繛鎺ユ睜涔嬪悗鍙互灏嗚繛鎺ユ睜涓殑杩炴帴澶嶇敤锛屼粠鑰屾彁楂樻墽琛屾晥鐜?
- 浣跨敤鏂瑰紡锛?
//鍒涘緩鏁版嵁婧愬璞?
BasicDataSource dataSource =
new BasicDataSource();
//璁劇疆鏁版嵁搴撹繛鎺ヤ俊鎭?
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://localhost:3306/db1");
dataSource.setUsername("root");
dataSource.setPassword("root");
//璁劇疆鍒濆杩炴帴鏁伴噺
dataSource.setInitialSize(3);
//璁劇疆鏈€澶ц繛鎺ユ暟閲?
dataSource.setMaxActive(5);
//浠庤繛鎺ユ睜涓幏鍙栬繛鎺?
Connection conn =
dataSource.getConnection();
System.out.println(conn);
###PreparedStatement棰勫姞杞界殑SQL鎵ц瀵矽薄
- 濂藉锛?
1. 浠ｇ爜鏇寸洿瑙傦紝绠€娲?
2. 鍙互閬垮厤SQL娉ㄥ叆,鍥犱負鍦ㄩ缂栬瘧鏃跺凡缁忔妸sql閫昏緫鍥哄畾閿佹锛屼笉浼氳涔嬪悗鏇挎崲杩涘幓鐨勫€兼敼鍙樺師鏈夐€昏緫
###鎵歸噺鎿嶄綔batch
鍥犱負姣忔sql鐨勬墽琛岄兘闇€瑕佸拰鏁版嵁搴撴湇鍔″櫒杩涜鏁版嵁浼犺緭锛屽鏋滄墽琛岀殑sql澶姣忔鍜屾暟鎹簱杩涜浜や簰娴垂璧勬簮鎵ц鏁堢巼浣庯紝浣跨敤鎵歸噺鎿嶄綔鍙互鎶婂鏉QL璇彞鍚堝苟鍒頒竴娆′氦浜掍腑锛岃繖鏍峰彲浠ユ彁楂樻墽琛屾晥鐜?
- Statement
//娣誨姞鍒版壒閲忔搷浣?
stat.addBatch(sql1);
stat.addBatch(sql2);
stat.addBatch(sql3);
//鎵ц鎵歸噺鎿嶄綔
stat.executeBatch();
- PreparedStatement
stat = conn.prepareStatement(sql);
stat.setString(1, "鍒樺");
stat.setString(2, "aaa");
//娣誨姞鍒版壒閲忓鐞?
stat.addBatch();
stat.setString(1, "鍏崇窘");
stat.setString(2, "bbb");
//娣誨姞鍒版壒閲忓鐞?
stat.addBatch();
stat.setString(1, "寮犻");
stat.setString(2, "ccc");
//娣誨姞鍒版壒閲忓鐞?
stat.addBatch();
//鎵ц
stat.executeBatch();
###浜嬪姟
1. 鍏抽棴鑷姩鎻愪氦
conn.setAutoCommit(false/true);
2. 鎻愪氦
conn.commit();
3. 鍥炴粴
conn.rollback();
- 妗堜緥 鍙傝浠ｇ爜锛欴emo11.java
###鑾峰彇鑷涓婚敭鐨勫€?
rs=stat.getGeneratedKeys();
create table team(id int primary key auto_increment,name varchar(10));
create table player(id int primary key auto_increment,name varchar(10),teamid int);
- 鐞冮槦鍜岀悆鍛樻渚嬩唬鐮佸弬瑙侊細 Demo11.java
####鏁版嵁搴撶殑鍏冩暟鎹拰琛ㄧ殑鍏冩暟鎹?浠ｇ爜鍙傝锛欴emo12.java
create table city(id int primary key auto_increment,cityname varchar(10),Pid int);
create table provice(id int primary key auto_increment,provicename varchar(20),Iid int);
create table user(id int primary key auto_increment,email varchar(32),pwd varchar(16),Pid int,Iid int);
create table information(id int primary key auto_increment,nickname varcahr(12),forumnickname varchar(12),gender varchar(2),birthday date,Pid int,Cid int);
select say 鍗曚環,num 搴撳瓨,say*num 鎬婚噾棰?from t_item;
html涓殑楂樺害鍧嶅锛?
![mysql 表字段資訊從一張表遷移到另一張表_關于資料透視表的介紹和使用[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)