mysql查询语句最常用10个 mysql多表查询sql语句怎么写?
MySQL\u6700\u5e38\u7528\u7684\u51e0\u4e2a\u57fa\u672cSQL\u8bed\u53e5\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
(1)查询表中全部信息:
select * from 表名
(2)查询表中指定列的信息:
select 列1,列2 from 表名
(3)去重:
select distinct 列... from 表名
(4)拼接结果:
select concat(列1,列2) from 表名
(5)设置别名(注意:关键字as可以省略)
select 列 as 别名 from 表名
select 列 别名 from 表名
(6)条件查询:
select 列... from 表名 where 条件
条件中比较运算符:(等于:= 大于:> 大于等于:>= 小于:< 小于等于:<= 不等于:!= 或 <>)
(7)where 列 比较运算符 值
注意:字符串、日期需使用单引号括起来
(8)逻辑运算符(并且:and或&& 或:or 非:not或!)
where 条件1 逻辑运算符 条件2
where not 条件
(9)范围查询:
where 列 between 条件1 and 条件2; //列在这个区间的值where 列 not between 条件1 and 条件2; //不在这个区间where !( 列 between 条件1 and 条件2 ); //同样表示不在这个区间
集合查询(判断列的值是否在指定的集合中):
where 列 in(值1,值2); //列中的数据是in后的值里面的where 列 not in(值1,值2); //不是in中指定值的数据
null值查询(注意:列中值为null不能使用=去查询):
where 列 is null; //查询列中值为null的数据
资料来源 网页链接
绛旓細鐢╟ount鍑芥暟灏卞彲浠鏌ョ湅銆傛瘮濡傝〃鍚嶅彨test锛岃鏌ヨ琛ㄤ腑涓鍏辨湁澶氬皯鏉¤褰曪紝select count(*) from test锛涘鏋滄寜鏉′欢鏌ヨ鐨勮瘽锛屽氨姝e父浣跨敤where鏉′欢鍗冲彲锛宻elect count(*) from test where id=1銆
绛旓細sql鏌ヨ琛ㄤ腑鏁版嵁鎬绘潯锛歋ELECT COUNT(*) FROM 琛ㄥ悕绉般俢ount锛*锛変唬琛ㄧ潃鏁版嵁缁熻鐨勬绘暟銆備緥瀛 鏈緥杩斿洖 "Persons" 琛ㄤ腑鐨勮鏁帮細SELECT COUNT(*) FROM Personsinfo銆傝繑鍥炲ぇ浜 20 宀佺殑浜烘暟锛歋ELECT COUNT(*) FROM Personsinfo WHERE Age>20 鏍规嵁绛涢夋潯浠舵潵缁熻鎬绘暟銆
绛旓細SELECT * FROM 琛ㄥ悕 WHERE 鏉′欢 ORDER BY 瀛楁 DESC LIMIT 100;
绛旓細TABLE 璇彞 鍏蜂綋璇硶锛歍ABLE table_name [ORDER BY column_name] [LIMIT number [OFFSET number]]鍏跺疄浠庤娉曚笂鐪嬶紝鍙互鎺掑簭锛屼篃鍙互杩囨护璁板綍闆嗭紝涓嶈繃姣旇緝绠鍗曪紝娌℃湁 SELECT 閭d箞寮哄ぇ銆傜ず渚 1 绠鍗曠殑寤轰竴寮犲緢灏忕殑琛 y1锛岃褰曟暟涓 10 鏉°傝〃 t1锛屾彃鍏 10 鏉¤褰 mysql-(ytt/3305)->create table ...
绛旓細鏈枃浠嬬粛MySQL鏁版嵁搴撲腑鎵цselect鏌ヨ璇彞锛屾煡璇㈡寚瀹氬垪鐨勬暟鎹紝鍗虫寚瀹氬瓧娈电殑鏁版嵁銆傚啀鏉ュ洖椤句竴涓婼QL璇彞涓殑select璇彞鐨勮娉曪細Select 璇彞鐨勫熀鏈娉曪細Select <鍒楃殑闆嗗悎> from <琛ㄥ悕> where <鏉′欢> order by <鎺掑簭瀛楁鍜屾柟寮> 濡傛灉瑕佹煡璇㈡煇涓〃涓殑鎸囧畾鍒楃殑鎵鏈夋暟鎹紝鍒欐煡璇㈣鍙ュ彲浠ュ啓浣滐細select 鍒楀悕1,...
绛旓細mysql鏀寔澶氫釜搴撲腑涓嶅悓琛ㄧ殑鍏宠仈鏌ヨ锛屼綘鍙互闅忎究閾炬帴涓涓暟鎹簱 鐒跺悗锛宻ql璇彞涓猴細select * from db1.table1 left join db2.table2 on db1.table1.id = db2.table2.id 鍙鐢ㄦ暟鎹簱鍚嶅姞涓"."灏辫兘璋冪敤鐩稿簲鏁版嵁搴撶殑鏁版嵁琛ㄤ簡.鏁版嵁搴撳悕.琛ㄥ悕 ...
绛旓細SELECT substr(target.dateTIme, 1, 7),userInfo.name,typeInfo.typeName,target.num FROM target LEFT JOIN userInfo on userInfo.ID = target.userID LEFT JOIN typeInfo ON typeInfo.ID = target.typeID ORDER BY substr(target.dateTIme, 1, 7) DESC, userInfo.ID, typeInfo.ID 鍗冲彲 ...
绛旓細甯哥敤鍑犵鏁版嵁搴擄紝鍙栧墠10鏉¤褰曠殑sql璇彞鍐欐硶銆俛ccess锛歴elect top (10) * from table1 where 1=1 db2锛歴elect column from table where 1=1 fetch first 10 rows only mysql锛歴elect * from table1 where 1=1 limit 10 sql server:璇诲彇鍓10鏉★細select top (10) * from table1 where 1=1...
绛旓細鏌ヨ鎬诲垎鎴愮哗鍓嶅崄锛mysql璇彞涓猴細select sum(subject) subject,name from student group by name order by subject desc limit 10;娉細select sum(subject) subject,name 杩欏彞鐨勬剰鎬濇槸锛歴um(subject) subject 鎶婂崟绉戞垚缁╁姞鎬荤敤subject 鏉ュ懡鍚嶏紙灏辨槸鎬绘垚缁╋級锛宯ame灏辨槸涓涓瓧娈碉紝杩欓噷鍙湁涓や釜瀛楁銆俫roup...
绛旓細mysql涓鎬庝箞鏌ヨ涓鍛ㄥ唴锛屼笁涓湀鍐 浣跨敤sql璇彞鏌ヨ鏃ユ湡鍦ㄤ竴鍛ㄥ唴鐨勬暟鎹 select * from ShopOrder where datediff(week,ordTime,getdate()-1)=0 //鏌ヨ褰撳ぉ鏃ユ湡鍦ㄤ竴鍛ㄥ勾鐨勬暟鎹 select * from ShopOrder where datediff(day,ordTime,getdate()-1)=0 //鏌ヨ褰撳ぉ鐨勬墍鏈夋暟鎹 SELECT * FROM A ...