MySQL µÄǶÌײéѯ
×î½üÔÚMySQLÉÏ×öÁ˵㹤×÷£¬·¢ÏֺܶàÈ˶¼ÔÚѯÎÊMySQLǶÌײéѯµÄÎÊÌ⣬Ðí¶àÈ˶¼¾õµÃMySQL²»Ö§³ÖǶÌײéѯ£¬ÆäʵMySQl´Ó4.11°æºóÒѾÍêÈ«Ö§³ÖǶÌײéѯÁË£¬ÄÇôÏÂÃæÎÒ¾ÙЩ¼òµ¥µÄǶÌײéѯµÄÀý×Ó°É£¨Ô´³ÌÐòÀ´×ÔMySQL User Manual£©£º
1. SELECTÓï¾äµÄ×Ó²éѯ:
Óï·¨: SELECT ... from (subquery) AS name ...
ÏÈ´´½¨Ò»¸ö±í:
CREATE TABLE t1 (s1 INT, s2 CHAR(5), s3 FLOAT);
INSERT INTO t1 VALUES (1,'1',1.0);
INSERT INTO t1 VALUES (2,'2',2.0);
ÎÒÃǾͿÉÒÔ½øÐÐÒÔϵÄǶÌײéѯÁË:
SELECT sb1,sb2,sb3
from (SELECT s1 AS sb1, s2 AS sb2, s3*2 AS sb3 from t1) AS sb
WHERE sb1 > 1;
½á¹ûÊÇ£º 2, '2', 4.0.
ÎÒÃÇÖªµÀÏÂÃæÓï¾äÊDz»»áµÃµ½ÕýÈ·½á¹ûµÄ£¬ÒòΪ¶Ô¾¹ýGroup byÅÅÐòµÄ¼¯ºÏ½øÐÐÇó¾ùÖµÊDz»Äܵõ½ÕýÈ·´ð°¸µÄ£º
SELECT AVG(SUM(column1)) from t1 GROUP BY column1
ËùÒÔÎÒÃÇ¿ÉÒÔͨ¹ýÏÂÃæµÄǶÌײéѯʵÏÖͬÑùµÄЧ¹û£º
SELECT AVG(sum_column1)
from (SELECT SUM(column1) AS sum_column1
from t1 GROUP BY column1) AS t1;
2.ÐеÄ×Ó²éѯ£¨Row Subquery£©£º
¿´ÏÂÃæµÄÀý×Ó£º
SELECT * from t1 WHERE ROW(1,2) = (SELECT column1, column2 from t2);
Õâ¸ö²éѯÊÇ·µ»Øcolumn1µÈÓÚcolumn2µÄ½á¹ûÐС£Rowº¯ÊýÖеÄ1ºÍ2Ï൱ÓÚ¹¹Ôì²ÎÊý¡£Ïë±ØBlogjavaÉϵÄͬ־¶ÔÕâЩӦ¸Ã±È½ÏÇå³þ£¬Ò²²»È¥Ïêϸ½éÉÜÁË¡£
3.ʹÓÃExistºÍNot Exist²ÎÊý
ÕâÀïµÄExistºÍNot ExistÓÃ;¼°Ó÷¨ºÍÔÚÆäËûûÓÐʲô´óµÄÇø±ð,ÎҾͼòµ¥¾Ù¼¸¸ö·¶ÀýºÃÁË:
·¶ÀýÒ»: SELECT DISTINCT store_type from Stores
WHERE EXISTS (SELECT * from Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);
·¶Àý¶þ: SELECT DISTINCT store_type from Stores
WHERE NOT EXISTS (SELECT * from Cities_Stores
WHERE Cities_Stores.store_type = Stores.store_type);
·¶ÀýÈý: Õâ¸öÀý×ÓÖÐǶÌ×ʹÓÃÁËNot ExistÓï·¨£¬ÉÔ΢עÒâһϣº
SELECT DISTINCT store_type from Stores S1
WHERE NOT EXISTS (
SELECT * from Cities WHERE NOT EXISTS (
SELECT * from Cities_Stores
Ïà¹ØÎĵµ£º
С½á£º
Ê×ÏÈÒªÕÒµ½mySqlµÄÅäÖÃÎļþ
my.cnf
È»ºóÒª¸Ä¶ÔµØ·½
[client]ºÍ[mysqld]ÏÂͬʱ¼ÓÉÏdefault-character-set=utf8
È»ºó¼ÇµÃÐèÒªÖØÆôMySql.ÇÒ±£Ö¤¿Í»§¶ËµÄÁ¬½Ó·½Ê½Ò²ÊÇutf8.
Ԥף×Ô¼ºÐµÄÒ»Ä꣬¼ªÏéÈçÒ⣬ÐÄÏëʳɣ¡ ......
/*****************by
garcon1986************************************************************/
ͨÓùæÔò£º
Õû
¸öÊý¾Ý¿âÀïµÄÃû×Ö¶¼¾¡Á¿Ê¹ÓÃСд¡£ÕâÑùÄÜÏû³ýÓÉÓÚ´óСд£¨
case-sensitivity
£©´øÀ´
µÄ´íÎó¡£
Mysql
ĬÈÏÇø·Ö´óСд¡£
ʹ
ÓÃÏ»®Ï߶ϿªÃû×Ö£¬Ãû×ÖÖв»ÄÜ
ʹÓÿոñ¡£
Ãû×ÖÖо¡Á¿²»Òª
ʹ
ÓÃÊý ......
/*************************************by
garcon1986*****************************************************/
¶à±í²éѯ£º
CREATE TABLE IF NOT EXISTS contact(
contact_id int(11) NOT NULL AUTO_INCREMENT,
user_name varchar(255),
nom varchar(255),
prenom varchar(255),
mail varchar(64),
passcode cha ......
·½·¨Ò»
ʹÓÃphpmyadmin£¬ÕâÊÇ×î¼òµ¥µÄÁË£¬ÐÞ¸Ämysql¿âµÄuser±í£¬²»¹ý±ðÍüÁËʹÓÃPASSWORDº¯Êý¡£
·½·¨¶þ
ʹÓÃmysqladmin£¬ÕâÊÇÇ°ÃæÉùÃ÷µÄÒ»¸öÌØÀý¡£
mysqladmin -u root -p password mypasswd
ÊäÈëÕâ¸öÃüÁîºó£¬ÐèÒªÊäÈërootµÄÔÃÜÂ룬ȻºórootµÄÃÜÂ뽫¸ÄΪmypasswd¡£
°ÑÃüÁîÀïµÄroot¸ÄΪÄãµÄÓû§Ãû£¬Äã¾Í¿ÉÒ ......