// ·µ»Ø¶¥²¿ //G

MySQL ¡¾½ø½×²éѯ¡¿

MySQL

Êý¾ÝÀàÐͽéÉÜ

  • ÕûÐÍ
tinyint, # Õ¼1×Ö½Ú,ÓзûºÅ£º-128~127,ÎÞ·ûºÅλ:0~255
smallint, # Õ¼2×Ö½Ú,ÓзûºÅ£º-32768~32767,ÎÞ·ûºÅλ:0~65535
mediumint,# Õ¼3×Ö½Ú,ÓзûºÅ£º-8388608~8388607,ÎÞ·ûλ:0~16777215
int, # Õ¼4×Ö½Ú,ÓзûºÅ£º-2147483648~2147483647,ÎÞ·ûºÅλ:0~4284967295
bigint,# Õ¼8×Ö½Ú
bool µÈ¼ÛÓÚtinyint(1) ²¼¶ûÐÍ
  • ¸¡µãÐÍ
float([m[,d]]) # Õ¼4×Ö½Ú,1.17E-38~3.4E+38
double([m[,d]]) # Õ¼8×Ö½Ú
decimal([m[,d]]) # ÒÔ×Ö·û´®ÐÎʽ365ÌåÓýͶעµÄ¸¡µãÊý
  • ×Ö·ûÐÍ
char([m]): # ¹Ì¶¨³¤¶ÈµÄ×Ö·û,Õ¼ÓÃm×Ö½Ú
varchar[(m)]: # ¿É±ä³¤¶ÈµÄ×Ö·û,Õ¼ÓÃm+1×Ö½Ú£¬´óÓÚ255¸ö×Ö·û£ºÕ¼ÓÃm+2
tinytext, # 255¸ö×Ö·û(2µÄ8´Î·½)
text, # 65535¸ö×Ö·û(2µÄ16´Î·½)
mediumtext, # 16777215×Ö·û(2µÄ24´Î·½)
longtext, # (2µÄ32´Î·½)
enum(value,value,...) # Õ¼1/2¸ö×Ö½Ú ×î¶à¿ÉÒÔÓÐ65535¸ö³ÉÔ±
set(value,value,...) # Õ¼1/2/3/4/8¸ö×Ö½Ú£¬×î¶à¿ÉÒÔÓÐ64¸ö³ÉÔ±

 

³£ÓÃselectÃüÁî

# ʹÓÃselectÃüÁî²é¿´mysqlÊý¾Ý¿âϵͳÐÅÏ¢£º

#-- ´òÓ¡µ±Ç°µÄÈÕÆÚºÍʱ¼ä
select now();  

#-- ´òÓ¡µ±Ç°µÄÈÕÆÚ
select curdate();  

# -- ´òÓ¡µ±Ç°µÄʱ¼ä
select curtime();  

# -- ´òÓ¡µ±Ç°Êý¾Ý¿â
select database();  

# -- ´òÓ¡MySQL°æ±¾
select version();  

#-- ´òÓ¡µ±Ç°Óû§
select user();  

# --²é¿´ÏµÍ³ÐÅÏ¢
show variables;  
show global variables;  
show global variables like '%version%';  
show variables like '%storage_engine%'; #  ĬÈϵĴ洢ÒýÇæ  

# likeÄ£ºýËÑË÷»¹¿ÉÓû§where×־䣬ÀýÈç
select * from students where stname like '%l%1%2%3%';  
# ³ýÁËlike »¹ÓÐnot like
show engines; # ²é¿´Ö§³ÖÄÄЩ´æ´¢ÒýÇæ

# --²é¿´ÏµÍ³ÔËÐÐ״̬ÐÅÏ¢
show status;  
show global status like 'Thread%';

 

MySQL ÅÅÐò

  • 365betÌåÓýÔÚÏßÃÇÖªµÀ´Ó MySQL ±íÖÐʹÓà SQL SELECT Óï¾äÀ´¶ÁÈ¡Êý¾Ý¡£
  • Èç¹û365betÌåÓýÔÚÏßÃÇÐèÒª¶Ô¶ÁÈ¡µÄÊý¾Ý½øÐÐÅÅÐò£¬365betÌåÓýÔÚÏßÃǾͿÉÒÔʹÓà MySQL µÄ ORDER BY ×Ó¾äÀ´É趨ÄãÏë°´Äĸö×Ö¶ÎÄÄÖÖ·½Ê½À´½øÐÐÅÅÐò£¬ÔÙ·µ»ØËÑË÷½á¹û¡£

Óï·¨

  • ÒÔÏÂÊÇ SQL SELECT Óï¾äʹÓà ORDER BY ×Ӿ佫²éѯÊý¾ÝÅÅÐòºóÔÙ·µ»ØÊý¾Ý£º
# SELECT field1, field2,...fieldN FROM table_name1, table_name2...
# ORDER BY field1 [ASC [DESC][ĬÈÏ ASC]], [field2...] [ASC [DESC][ĬÈÏ ASC]]
  • Äã¿ÉÒÔʹÓÃÈκÎ×Ö¶ÎÀ´×÷ΪÅÅÐòµÄÌõ¼þ£¬´Ó¶ø·µ»ØÅÅÐòºóµÄ²éѯ½á¹û¡£
  • Äã¿ÉÒÔÉ趨¶à¸ö×Ö¶ÎÀ´ÅÅÐò¡£
  • Äã¿ÉÒÔʹÓà ASC »ò DESC ¹Ø¼ü×ÖÀ´ÉèÖòéѯ½á¹ûÊÇ°´ÉýÐò»ò½µÐòÅÅÁС£ ĬÈÏÇé¿öÏ£¬ËüÊÇ°´ÉýÐòÅÅÁС£
  • Äã¿ÉÒÔÌí¼Ó WHERE...LIKE ×Ó¾äÀ´ÉèÖÃÌõ¼þ¡£

°¸Àý

  • asc´ÓСµ½´óÅÅÁУ¬¼´ÉýÐò
  • desc´Ó´óµ½Ð¡ÅÅÐò£¬¼´½µÐò
# ²éѯÄêÁäÔÚ18µ½34ËêÖ®¼äµÄÄÐÐÔ£¬°´ÕÕÄêÁä´ÓСµ½´óµ½ÅÅÐò
# select * from students where (age between 18 and 34) and gender = 1 order by age asc;

# ²éѯÄêÁäÔÚ18µ½34ËêÖ®¼äµÄÅ®ÐÔ£¬Éí¸ß´Ó¸ßµ½°«ÅÅÐò
# select * from students where (age between 18 and 34) and gender = 2 order by height desc;
  • order by ¶à¸ö×Ö¶Î
# ²éѯÄêÁäÔÚ18µ½34ËêÖ®¼äµÄÅ®ÐÔ£¬Éí¸ß´Ó¸ßµ½°«ÅÅÐò, Èç¹ûÉí¸ßÏàͬµÄÇé¿öÏ°´ÕÕÄêÁä´ÓСµ½´óÅÅÐò    
# select * from students where (age between 18 and 34) and gender = 2 order by height desc,age asc;

# ²éѯÄêÁäÔÚ18µ½34ËêÖ®¼äµÄÅ®ÐÔ£¬Éí¸ß´Ó¸ßµ½°«ÅÅÐò, Èç¹ûÉí¸ßÏàͬµÄÇé¿öÏ°´ÕÕÄêÁä´ÓСµ½´óÅÅÐò,Èç¹ûÄêÁäÒ²ÏàͬÄÇô°´ÕÕid´Ó´óµ½Ð¡ÅÅÐò
# select * from students where (age between 18 and 34) and gender = 2 order by height desc,age asc,id desc;
# mysql> select bName,price from books where price  in (50,60,70) order by price asc;  
+--------------------------------------+-------+  
| bName                          | price |  
+--------------------------------------+-------+  
| Illustrator 10ÍêÈ«ÊÖ²á      |    50 |  
| FreeHand 10»ù´¡½Ì³Ì      |    50 |  
| ÍøÕ¾Éè¼ÆÈ«³Ì½Ì³Ì             |    50 |  
| ASPÊý¾Ý¿âϵͳ¿ª·¢ÊµÀýµ¼º½  |    60 |  
| Delphi 5³ÌÐòÉè¼ÆÓë¿Ø¼þ²Î¿¼  |    60 |  
| ASPÊý¾Ý¿âϵͳ¿ª·¢ÊµÀýµ¼º½   |    60 |
ÉýÐò£ºorder by ¡°ÅÅÐòµÄ×ֶΡ± asc ĬÈÏ
# mysql> select bName,price from books where price  in (50,60,70) order by price desc;  
+--------------------------------+-----------------+  
| bName                                | price |  
+--------------------------------+-----------------+  
| ASPÊý¾Ý¿âϵͳ¿ª·¢ÊµÀýµ¼º½     |    60 |  
| Delphi 5³ÌÐòÉè¼ÆÓë¿Ø¼þ²Î¿¼    |    60 |  
| ASPÊý¾Ý¿âϵͳ¿ª·¢ÊµÀýµ¼º½    |    60 |  
| Illustrator 10ÍêÈ«ÊÖ²á           |    50 |  
| FreeHand 10»ù´¡½Ì³Ì           |    50 |  
| ÍøÕ¾Éè¼ÆÈ«³Ì½Ì³Ì                  |    50 |
½µÐò£ºoredr by ¡°ÅÅÐòµÄ×ֶΡ± desc
select bName,price from books where price  in (50,60,70) order by price desc,bName desc;
¶à¸ö×Ö¶ÎÅÅÐò

 

MySQL ·Ö×é

MySQL GROUP BY Óï¾ä

  • GROUP BY Óï¾ä¸ù¾ÝÒ»¸ö»ò¶à¸öÁжԽá¹û¼¯½øÐзÖ×é¡£
  • ÔÚ·Ö×éµÄÁÐÉÏ365betÌåÓýÔÚÏßÃÇ¿ÉÒÔʹÓà COUNT, SUM, AVG,µÈº¯Êý¡£

GROUP BY Óï·¨

# SELECT column_name, function(column_name)
# FROM table_name
# WHERE column_name operator value
# GROUP BY column_name;

ʵÀýÑÝʾ

  • ±¾Õ½ÚʵÀýʹÓõ½ÁËÒÔϱí½á¹¹¼°Êý¾Ý£¬Ê¹ÓÃÇ°365betÌåÓýÔÚÏßÃÇ¿ÉÒÔÏȽ«ÒÔÏÂÊý¾Ýµ¼ÈëÊý¾Ý¿âÖС£
SET NAMES utf8;
SET FOREIGN_KEY_CHECKS =0;
-- ----------------------------
-- Table structure for`employee_tbl`
-- ----------------------------
DROP TABLE IF EXISTS `employee_tbl`;
CREATE TABLE `employee_tbl`(
¡¡¡¡`id`int(11) NOT NULL,
¡¡¡¡`name`char(10) NOT NULL DEFAULT '',
¡¡¡¡`date` datetime NOT NULL,
¡¡¡¡`singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'µÇ¼´ÎÊý',
  ¡¡¡¡PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of `employee_tbl`
-- ----------------------------BEGIN;
INSERT INTO `employee_tbl` VALUES ('1','СÃ÷','2016-04-22 15:25:33','1'),
('2','СÍõ','2016-04-20 15:25:47','3'),('3','СÀö','2016-04-19 15:26:02','2'),
('4','СÍõ','2016-04-07 15:26:14','4'),('5','СÃ÷','2016-04-11 15:26:40','4'),
('6','СÃ÷','2016-04-04 15:26:54','2');
COMMIT;
SET FOREIGN_KEY_CHECKS =1;
  • µ¼Èë³É¹¦ºó£¬Ö´ÐÐÒÔÏ SQL Óï¾ä£º
# mysql>set names utf8;
# mysql> SELECT * FROM employee_tbl;
+----+--------+------------------+--------+
| id | name | date              | singin |
+----+--------+------------------+--------+
|1   |СÃ÷   |2016-04-2215:25:33 |1|
|2   |СÍõ   |2016-04-2015:25:47 |3|
|3   |СÀö   |2016-04-1915:26:02 |2|
|4   |СÍõ   |2016-04-0715:26:14 |4|
|5   |СÃ÷   |2016-04-1115:26:40 |4|
|6   |СÃ÷   |2016-04-0415:26:54 |2|
+----+--------+------------------+--------+
6 rows inset(0.00 sec)
  • ½ÓÏÂÀ´365betÌåÓýÔÚÏßÃÇʹÓà GROUP BY Óï¾ä ½«Êý¾Ý±í°´Ãû×Ö½øÐзÖ×飬²¢Í³¼Æ365ÌåÓýͶעÈËÓжàÉÙÌõ¼Ç¼£º
# mysql> SELECT name, COUNT(*) FROM   employee_tbl GROUP BY name;
+--------+----------+
| name  | COUNT(*)|
+--------+----------+
|СÀö¡¡¡¡|¡¡¡¡1|
|СÃ÷¡¡¡¡|¡¡¡¡3|
|СÍõ¡¡¡¡|¡¡¡¡2|
+--------+----------+
3 rows inset(0.01 sec)

ʹÓà WITH ROLLUP

  • WITH ROLLUP ¿ÉÒÔʵÏÖÔÚ·Ö×éͳ¼ÆÊý¾Ý»ù´¡ÉÏÔÙ½øÐÐÏàͬµÄͳ¼Æ£¨SUM,AVG,COUNT¡­£©¡£
  • ÀýÈç365betÌåÓýÔÚÏßÃǽ«ÒÔÉϵÄÊý¾Ý±í°´Ãû×Ö½øÐзÖ×飬ÔÙͳ¼Æ365ÌåÓýͶעÈ˵ǼµÄ´ÎÊý£º
# mysql> SELECT name, SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------+--------------+
| name | singin_count |
+--------+--------------+
|СÀö¡¡¡¡|¡¡¡¡2|
|СÃ÷¡¡¡¡|¡¡¡¡7|
|СÍõ¡¡¡¡|¡¡¡¡7|
| NULL  |¡¡¡¡16|
+--------+--------------+
4 rows inset(0.00 sec)
  • ÆäÖмǼ NULL 365ÌåÓýͶעËùÓÐÈ˵ĵǼ´ÎÊý¡£
  • 365betÌåÓýÔÚÏßÃÇ¿ÉÒÔʹÓà coalesce À´ÉèÖÃÒ»¸ö¿ÉÒÔÈ¡´ú NUll µÄÃû³Æ£¬coalesce Óï·¨£º
# select coalesce(a,b,c);
  • ²ÎÊý˵Ã÷£ºÈç¹ûa==null,ÔòÑ¡Ôñb£»Èç¹ûb==null,ÔòÑ¡Ôñc£»Èç¹ûa!=null,ÔòÑ¡Ôña£»Èç¹ûa b c ¶¼Îªnull £¬Ôò·µ»ØΪnull£¨Ã»ÒâÒ壩¡£
  • ÒÔÏÂʵÀýÖÐÈç¹ûÃû×ÖΪ¿Õ365betÌåÓýÔÚÏßÃÇʹÓÃ×ÜÊý´úÌ棺
# mysql> SELECT coalesce(name,'×ÜÊý'), SUM(singin)as singin_count FROM  employee_tbl GROUP BY name WITH ROLLUP;
+--------------------------+--------------+
| coalesce(name,'×ÜÊý')| singin_count |
+--------------------------+--------------+
|СÀö¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡|¡¡¡¡2|
|СÃ÷¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡|¡¡¡¡7|
|СÍõ¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡|¡¡¡¡7|
|×ÜÊý¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡¡|¡¡¡¡16|
+--------------------------+--------------+
4 rows inset(0.01 sec)

 

MySQL Á¬½Ó²éѯ

  • ÔÚÇ°¼¸Õ½ÚÖУ¬365betÌåÓýÔÚÏßÃÇÒѾ­Ñ§»áÁËÈçºÎÔÚÒ»ÕűíÖжÁÈ¡Êý¾Ý£¬ÕâÊÇÏà¶Ô¼òµ¥µÄ£¬µ«ÊÇÔÚÕæÕýµÄÓ¦ÓÃÖо­³£ÐèÒª´Ó¶à¸öÊý¾Ý±íÖжÁÈ¡Êý¾Ý¡£
  • ±¾Õ½Ú365betÌåÓýÔÚÏßÃǽ«Ïò´ó¼Ò½éÉÜÈçºÎʹÓà MySQL µÄ JOIN ÔÚÁ½¸ö»ò¶à¸ö±íÖвéѯÊý¾Ý¡£
  • Äã¿ÉÒÔÔÚ SELECT, UPDATE ºÍ DELETE Óï¾äÖÐʹÓà Mysql µÄ JOIN À´ÁªºÏ¶à±í²éѯ¡£

JOIN °´ÕÕ¹¦ÄÜ´óÖ·ÖΪÈçÏÂÈýÀࣺ

  • INNER JOIN£¨ÄÚÁ¬½Ó,»òµÈÖµÁ¬½Ó£©£º»ñÈ¡Á½¸ö±íÖÐ×Ö¶ÎÆ¥Åä¹ØϵµÄ¼Ç¼¡£
  • LEFT JOIN£¨×óÁ¬½Ó£©£º»ñÈ¡×ó±íËùÓмǼ£¬¼´Ê¹ÓÒ±íûÓжÔӦƥÅäµÄ¼Ç¼¡£
  • RIGHT JOIN£¨ÓÒÁ¬½Ó£©£º Óë LEFT JOIN Ïà·´£¬ÓÃÓÚ»ñÈ¡ÓÒ±íËùÓмǼ£¬¼´Ê¹×ó±íûÓжÔӦƥÅäµÄ¼Ç¼¡£

ʾÀý£º

  • ÒÔÒ»¸ö¹²Í¬µÄ×ֶΣ¬ÇóÁ½ÕÅ±íµ±ÖзûºÏÌõ¼þµÄ²¢¼¯¡£ ͨ¹ý¹²Í¬×ֶΰÑÕâÁ½ÕűíÁ¬½ÓÆðÀ´¡£

³£ÓõÄÁ¬½Ó£º

  • ÄÚÁ¬½Ó£º¸ù¾Ý±íÖеĹ²Í¬×ֶνøÐÐÆ¥Åä
  • ÍâÁ¬½Ó·ÖÁ½ÖÖ£º×óÍâÁ¬½Ó¡¢ÓÒÍâÁ´½Ó¡£

ÄÚÁ¬½Ó£º

  • Óï·¨£º

# select ×ֶΠ from ±í1 inner join ±í2  on ±í1.×Ö¶Î=±í2.×Ö¶Î
  • ÄÚÁ¬½Ó£º¸ù¾Ý±íÖеĹ²Í¬×ֶνøÐÐÆ¥Åä
# ²âÊÔ
select a.bname,a.price,b.btypename from books a inner join category b on a.btypeid=b.btypeid;  
# ʵ¼ÊʹÓÃÖÐinner¿ÉÊ¡ÂÔµô  
# ¸úWHERE ×Ó¾ä½á¹ûÒ»Ñù  
select a.bname,a.price,b.btypename from books a, category b where a.btypeid=b.btypeid;
  • ÍâÁ¬½Ó (·ÖΪ×óÍâÁ¬½Ó£»ÓÒÍâÁ¬½Ó)

            

# ×óÁ¬½Ó£º select ×ֶΠfrom a±í left join b±í on Á¬½ÓÌõ¼þ
# a±íÊÇÖ÷±í£¬¶¼ÏÔʾ¡£
# b±í´Ó±í
# Ö÷±íÄÚÈÝÈ«¶¼ÓУ¬´Ó±íÄÚûÓеÄÏÔʾnull¡£
Select a.bname,a.price,b.btypename from books a left join category b on a.btypeid=b.btypeid; 
# ÓÒÁ¬½Ó£ºselect ×ֶΠfrom a±í right join b±í on Ìõ¼þ
# a±íÊÇ´Ó±í£¬
# b±íÖ÷±í£¬¶¼ÏÔʾ¡£
Select a.bname,b.* from books a right join category b on a.btypeid=b.btypeid;  
# ÓÒÁ¬½Ó£¬¿ÉÒÔ¶à±íÁ¬½Ó  
inner join ... on
select ... from ±íA inner join ±íB;
select * from students inner join classes;

# ²éѯ ÓÐÄܹ»¶ÔÓ¦°à¼¶µÄѧÉúÒÔ¼°°à¼¶ÐÅÏ¢
select * from students inner join classes on students.cls_id = classes.id;

# °´ÕÕÒªÇóÏÔʾÐÕÃû¡¢°à¼¶
select students.name,classes.name from students inner join classes on students.cls_id = classes.id;

# ¸øÊý¾Ý±íÆðÃû×Ö
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

# ²éѯ ÓÐÄܹ»¶ÔÓ¦°à¼¶µÄѧÉúÒÔ¼°°à¼¶ÐÅÏ¢£¬ÏÔʾѧÉúµÄËùÓÐÐÅÏ¢ students.*£¬Ö»ÏÔʾ°à¼¶Ãû³Æ classes.name.
select s.*,c.name from students as s inner join classes as c on s.cls_id = c.id;

# ÔÚÒÔÉϵIJéѯÖУ¬½«°à¼¶ÐÕÃûÏÔʾÔÚµÚ1ÁÐ
select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id;

# ²éѯ ÓÐÄܹ»¶ÔÓ¦°à¼¶µÄѧÉúÒÔ¼°°à¼¶ÐÅÏ¢, °´Õհ༶½øÐÐÅÅÐò
select c.xxx s.xxx from students as s inner join clssses as c on .... order by ....;
select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name;

# µ±Ê±Í¬Ò»¸ö°à¼¶µÄʱºò£¬°´ÕÕѧÉúµÄid½øÐдÓСµ½´óÅÅÐò
select c.name,s.* from students as s inner join classes as c on s.cls_id = c.id order by c.name,id asc;

# left join
# ²éѯÿλѧÉú¶ÔÓ¦µÄ°à¼¶ÐÅÏ¢
select * from students left join classes on students.cls_id = classes.id;

# right join
select * from students right join classes on students.cls_id = classes.id;

# ²éѯûÓжÔÓ¦°à¼¶ÐÅÏ¢µÄѧÉú
# Óï¾ä
select ... from xxx as s left join xxx as c on..... where .....
select ... from xxx as s left join xxx as c on..... having .....

# ÁÐ
select * from students left join classes on students.cls_id = classes.id where classes.name is null;
#(×¢Òâ)²»½¨ÒéʹÓà select * from students left join classes on students.cls_id=classes.id having classes.id is null;
# right join   on
# ½«Êý¾Ý±íÃû×Ö»¥»»Î»Öã¬ÓÃleft joinÍê³É

Ó¦ÓÃʾÀý
Ó¦ÓÃʾÀý

 

MySQL NULL Öµ´¦Àí

  • 365betÌåÓýÔÚÏßÃÇÒѾ­ÖªµÀ MySQL ʹÓà SQL SELECT ÃüÁî¼° WHERE ×Ó¾äÀ´¶ÁÈ¡Êý¾Ý±íÖеÄÊý¾Ý,µ«Êǵ±ÌṩµÄ²éѯÌõ¼þ×Ö¶ÎΪ NULL ʱ£¬¸ÃÃüÁî¿ÉÄܾÍÎÞ·¨Õý³£¹¤×÷¡£

ΪÁË´¦ÀíÕâÖÖÇé¿ö£¬MySQLÌṩÁËÈý´óÔËËã·û:

  • IS NULL: µ±ÁеÄÖµÊÇ NULL,´ËÔËËã·û·µ»Ø true¡£
  • IS NOT NULL: µ±ÁеÄÖµ²»Îª NULL, ÔËËã·û·µ»Ø true¡£
  • <=>: ±È½Ï²Ù×÷·û£¨²»Í¬ÓÚ = ÔËËã·û£©£¬µ±±È½ÏµÄµÄÁ½¸öÖµÏàµÈ»òÕ߶¼Îª NULL ʱ·µ»Ø true¡£
  1. ¹ØÓÚ NULL µÄÌõ¼þ±È½ÏÔËËãÊDZȽÏÌØÊâµÄ¡£Äã²»ÄÜʹÓà = NULL »ò != NULL ÔÚÁÐÖвéÕÒ NULL Öµ ¡£
  2. ÔÚ MySQL ÖУ¬NULL ÖµÓëÈκÎÆäËüÖµµÄ±È½Ï£¨¼´Ê¹ÊÇ NULL£©ÓÀÔ¶·µ»Ø NULL£¬¼´ NULL = NULL ·µ»Ø NULL ¡£
  3. MySQL Öд¦Àí NULL ʹÓà IS NULL ºÍ IS NOT NULL ÔËËã·û¡£
# ×¢Ò⣺
select * , columnName1+ifnull(columnName2,0) from tableName;
# columnName1£¬columnName2 Ϊ int ÐÍ£¬µ± columnName2 ÖУ¬ÓÐֵΪ null ʱ£¬columnName1+columnName2=null£¬ 
ifnull(columnName2,0) °Ñ columnName2 ÖÐ null ֵתΪ 0

°¸Àý

  •  ÅпÕis null
# ²éѯÉí¸ßΪ¿ÕµÄÐÅÏ¢
select * from students where height is null;

# ÅзǿÕis not null
select * from students where height is not null;

 

MySQL ÕýÔò±í´ïʽ

  • ÔÚÇ°ÃæµÄÕ½Ú365betÌåÓýÔÚÏßÃÇÒѾ­Á˽⵽MySQL¿ÉÒÔͨ¹ý LIKE ...% À´½øÐÐÄ£ºýÆ¥Åä¡£
  • MySQL ͬÑùÒ²Ö§³ÖÆäËûÕýÔò±í´ïʽµÄÆ¥Å䣬 MySQLÖÐʹÓà REGEXP ²Ù×÷·ûÀ´½øÐÐÕýÔò±í´ïʽƥÅä¡£
  • Èç¹ûÄúÁ˽âPHP»òPerl£¬ÄÇô²Ù×÷ÆðÀ´¾Í·Ç³£¼òµ¥£¬ÒòΪMySQLµÄÕýÔò±í´ïʽƥÅäÓëÕâЩ½Å±¾µÄÀàËÆ¡£
  • ϱíÖеÄÕýÔòģʽ¿ÉÓ¦ÓÃÓÚ REGEXP ²Ù×÷·ûÖС£
ģʽÃèÊö
^ Æ¥ÅäÊäÈë×Ö·û´®µÄ¿ªÊ¼Î»Öá£Èç¹ûÉèÖÃÁË RegExp ¶ÔÏóµÄ Multiline ÊôÐÔ£¬^ ҲƥÅä '\n' »ò '\r' Ö®ºóµÄλÖá£
$ Æ¥ÅäÊäÈë×Ö·û´®µÄ½áÊøλÖá£Èç¹ûÉèÖÃÁËRegExp ¶ÔÏóµÄ Multiline ÊôÐÔ£¬$ ҲƥÅä '\n' »ò '\r' ֮ǰµÄλÖá£
. Æ¥Åä³ý "\n" Ö®ÍâµÄÈκε¥¸ö×Ö·û¡£ÒªÆ¥Åä°üÀ¨ '\n' ÔÚÄÚµÄÈκÎ×Ö·û£¬ÇëʹÓÃÏó '[.\n]' µÄģʽ¡£
[...] ×Ö·û¼¯ºÏ¡£Æ¥ÅäËù°üº¬µÄÈ365betÌåÓýÔÚÏßâÒ»¸ö×Ö·û¡£ÀýÈ磬 '[abc]' ¿ÉÒÔÆ¥Åä "plain" ÖÐµÄ 'a'¡£
[^...] ¸ºÖµ×Ö·û¼¯ºÏ¡£Æ¥Åäδ°üº¬µÄÈ365betÌåÓýÔÚÏßâ×Ö·û¡£ÀýÈ磬 '[^abc]' ¿ÉÒÔÆ¥Åä "plain" ÖеÄ'p'¡£
p1|p2|p3 Æ¥Åä p1 »ò p2 »ò p3¡£ÀýÈ磬'z|food' ÄÜÆ¥Åä "z" »ò "food"¡£'(z|f)ood' ÔòÆ¥Åä "zood" »ò "food"¡£
* Æ¥ÅäÇ°ÃæµÄ×Ó±í´ïʽÁã´Î»ò¶à´Î¡£ÀýÈ磬zo* ÄÜÆ¥Åä "z" ÒÔ¼° "zoo"¡£* µÈ¼ÛÓÚ{0,}¡£
+ Æ¥ÅäÇ°ÃæµÄ×Ó±í´ïʽһ´Î»ò¶à´Î¡£ÀýÈ磬'zo+' ÄÜÆ¥Åä "zo" ÒÔ¼° "zoo"£¬µ«²»ÄÜÆ¥Åä "z"¡£+ µÈ¼ÛÓÚ {1,}¡£
{n} n ÊÇÒ»¸ö·Ç¸ºÕûÊý¡£Æ¥ÅäÈ·¶¨µÄ n ´Î¡£ÀýÈ磬'o{2}' ²»ÄÜÆ¥Åä "Bob" ÖÐµÄ 'o'£¬µ«ÊÇÄÜÆ¥Åä "food" ÖеÄÁ½¸ö o¡£
{n,m} m ºÍ n ¾ùΪ·Ç¸ºÕûÊý£¬ÆäÖÐn <= m¡£×îÉÙÆ¥Åä n ´ÎÇÒ×î¶àÆ¥Åä m ´Î¡£

ʾÀý

  • Á˽âÒÔÉϵÄÕýÔòÐèÇóºó£¬365betÌåÓýÔÚÏßÃǾͿÉÒÔ¸ù¾Ý×Ô¼ºµÄÐèÇóÀ´±àд´øÓÐÕýÔò±í´ïʽµÄSQLÓï¾ä¡£ÒÔÏÂ365betÌåÓýÔÚÏßÃǽ«Áгö¼¸¸öСʵÀý(±íÃû£ºperson_tbl )À´¼ÓÉî365betÌåÓýÔÚÏßÃǵÄÀí½â£º
# ²éÕÒname×Ö¶ÎÖÐÒÔ'st'Ϊ¿ªÍ·µÄËùÓÐÊý¾Ý£º
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^st';

# ²éÕÒname×Ö¶ÎÖÐÒÔ'ok'Ϊ½áβµÄËùÓÐÊý¾Ý£º
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'ok$';

# ²éÕÒname×Ö¶ÎÖаüº¬'mar'×Ö·û´®µÄËùÓÐÊý¾Ý£º
mysql> SELECT name FROM person_tbl WHERE name REGEXP 'mar';

# ²éÕÒname×Ö¶ÎÖÐÒÔÔªÒô×Ö·û¿ªÍ·»òÒÔ'ok'×Ö·û´®½áβµÄËùÓÐÊý¾Ý£º
mysql> SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

 

MySQL ÊÂÎñ

MySQL ÊÂÎñÖ÷ÒªÓÃÓÚ´¦Àí²Ù×÷Á¿´ó£¬¸´ÔӶȸߵÄÊý¾Ý¡£±ÈÈç˵£¬ÔÚÈËÔ±¹ÜÀíϵͳÖУ¬Äãɾ³ýÒ»¸öÈËÔ±£¬Äã¼ÈÐèҪɾ³ýÈËÔ±µÄ»ù±¾×ÊÁÏ£¬Ò²ÒªÉ¾³ýºÍ¸ÃÈËÔ±Ïà¹ØµÄÐÅÏ¢£¬ÈçÐÅÏ䣬ÎÄÕµȵȣ¬ÕâÑù£¬ÕâЩÊý¾Ý¿â²Ù×÷Óï¾ä¾Í¹¹³ÉÒ»¸öÊÂÎñ£¬ÏêϸÁ˽â¿ÉÒÔ¿´Ò»ÏÂÕâƪ¡¾³£Ê¶Óë½ø½×¡¿£¡

  • ÔÚ MySQL ÖÐÖ»ÓÐʹÓÃÁË Innodb Êý¾Ý¿âÒýÇæµÄÊý¾Ý¿â»ò±í²ÅÖ§³ÖÊÂÎñ¡£
  • ÊÂÎñ´¦Àí¿ÉÒÔÓÃÀ´Î¬»¤Êý¾Ý¿âµÄÍêÕûÐÔ£¬±£Ö¤³ÉÅúµÄ SQL Óï¾äҪôȫ²¿Ö´ÐУ¬ÒªÃ´È«²¿²»Ö´ÐС£
  • ÊÂÎñÓÃÀ´¹ÜÀí insert,update,delete Óï¾ä

Ò»°ãÀ´Ëµ£¬ÊÂÎñÊDZØÐëÂú×ã4¸öÌõ¼þ£¨ACID£©£º£ºÔ­×ÓÐÔ£¨Atomicity£¬»ò³Æ²»¿É·Ö¸îÐÔ£©¡¢Ò»ÖÂÐÔ£¨Consistency£©¡¢¸ôÀëÐÔ£¨Isolation£¬ÓֳƶÀÁ¢ÐÔ£©¡¢³Ö¾ÃÐÔ£¨Durability£©¡£

  • Ô­×ÓÐÔ£ºÒ»¸öÊÂÎñ£¨transaction£©ÖеÄËùÓвÙ×÷£¬ÒªÃ´È«²¿Íê³É£¬ÒªÃ´È«²¿²»Íê³É£¬²»»á½áÊøÔÚÖмäij¸ö»·½Ú¡£ÊÂÎñÔÚÖ´Ðйý³ÌÖз¢Éú´íÎ󣬻ᱻ»Ø¹ö£¨Rollback£©µ½ÊÂÎñ¿ªÊ¼Ç°µÄ״̬£¬¾ÍÏñÕâ¸öÊÂÎñ´ÓÀ´Ã»ÓÐÖ´ÐйýÒ»Ñù¡£
  • Ò»ÖÂÐÔ£ºÔÚÊÂÎñ¿ªÊ¼Ö®Ç°ºÍÊÂÎñ½áÊøÒÔºó£¬Êý¾Ý¿âµÄÍêÕûÐÔûÓб»ÆÆ»µ¡£Õâ365ÌåÓýͶעдÈëµÄ×ÊÁϱØÐëÍêÈ«·ûºÏËùÓеÄÔ¤Éè¹æÔò£¬Õâ°üº¬×ÊÁϵľ«È·¶È¡¢´®ÁªÐÔÒÔ¼°ºóÐøÊý¾Ý¿â¿ÉÒÔ×Ô·¢ÐÔµØÍê³ÉÔ¤¶¨µÄ¹¤×÷¡£
  • ¸ôÀëÐÔ£ºÊý¾Ý¿âÔÊÐí¶à¸ö²¢·¢ÊÂÎñͬʱ¶ÔÆäÊý¾Ý½øÐжÁдºÍÐ޸ĵÄÄÜÁ¦£¬¸ôÀëÐÔ¿ÉÒÔ·ÀÖ¹¶à¸öÊÂÎñ²¢·¢Ö´ÐÐʱÓÉÓÚ½»²æÖ´Ðжøµ¼ÖÂÊý¾ÝµÄ²»Ò»Ö¡£ÊÂÎñ¸ôÀë·ÖΪ²»Í¬¼¶±ð£¬°üÀ¨¶ÁδÌá½»£¨Read uncommitted£©¡¢¶ÁÌá½»£¨read committed£©¡¢¿ÉÖظ´¶Á£¨repeatable read£©ºÍ´®Ðл¯£¨Serializable£©¡£
  • ³Ö¾ÃÐÔ£ºÊÂÎñ´¦Àí½áÊøºó£¬¶ÔÊý¾ÝµÄÐ޸ľÍÊÇÓÀ¾ÃµÄ£¬¼´±ãϵͳ¹ÊÕÏÒ²²»»á¶ªÊ§¡£
# ÔÚ MySQL ÃüÁîÐеÄĬÈÏÉèÖÃÏ£¬ÊÂÎñ¶¼ÊÇ×Ô¶¯Ìá½»µÄ£¬¼´Ö´ÐÐ SQL Óï¾äºó¾Í»áÂíÉÏÖ´ÐÐ COMMIT ²Ù×÷¡£
¡¡Òò´ËÒªÏÔʽµØ¿ªÆôÒ»¸öÊÂÎñÎñÐëʹÓÃÃüÁî BEGIN »ò START TRANSACTION£¬»òÕßÖ´ÐÐÃüÁî SETAUTOCOMMIT=0£¬
  ÓÃÀ´½ûֹʹÓõ±Ç°»á»°µÄ×Ô¶¯Ìá½»¡£

ÊÂÎñ¿ØÖÆÓï¾ä£º

  • BEGIN »ò START TRANSACTION ÏÔʽµØ¿ªÆôÒ»¸öÊÂÎñ£»
  • COMMIT Ò²¿ÉÒÔʹÓà COMMIT WORK£¬²»¹ý¶þÕßÊǵȼ۵ġ£COMMIT »áÌá½»ÊÂÎñ£¬²¢Ê¹ÒѶÔÊý¾Ý¿â½øÐеÄËùÓÐÐ޸ijÉΪÓÀ¾ÃÐԵģ»
  • ROLLBACK Ò²¿ÉÒÔʹÓà ROLLBACK WORK£¬²»¹ý¶þÕßÊǵȼ۵ġ£»Ø¹ö»á½áÊøÓû§µÄÊÂÎñ£¬²¢³·ÏúÕýÔÚ½øÐеÄËùÓÐδÌá½»µÄÐ޸ģ»
  • SAVEPOINT identifier£¬SAVEPOINT ÔÊÐíÔÚÊÂÎñÖд´½¨Ò»¸ö±£´æµã£¬Ò»¸öÊÂÎñÖпÉÒÔÓжà¸ö SAVEPOINT£»
  • RELEASE SAVEPOINT identifier ɾ³ýÒ»¸öÊÂÎñµÄ±£´æµã£¬µ±Ã»ÓÐÖ¸¶¨µÄ±£´æµãʱ£¬Ö´ÐиÃÓï¾ä»áÅ׳öÒ»¸öÒì³££»
  • ROLLBACK TO identifier °ÑÊÂÎñ»Ø¹öµ½±ê¼Çµã£»
  • SET TRANSACTION ÓÃÀ´ÉèÖÃÊÂÎñµÄ¸ôÀ뼶±ð¡£InnoDB ´æ´¢ÒýÇæÌṩÊÂÎñµÄ¸ôÀ뼶±ðÓÐREAD UNCOMMITTED¡¢READ COMMITTED¡¢REPEATABLE READ ºÍ SERIALIZABLE¡£

MYSQL ÊÂÎñ´¦ÀíÖ÷ÒªÓÐÁ½ÖÖ·½·¨£º

  • Óà BEGIN, ROLLBACK, COMMITÀ´ÊµÏÖ
    • BEGIN ¿ªÊ¼Ò»¸öÊÂÎñ
    • ROLLBACKÊÂÎñ»Ø¹ö
    • COMMITÊÂÎñÈ·ÈÏ
  • Ö±½ÓÓà SET À´¸Ä±ä MySQL µÄ×Ô¶¯Ìύģʽ:
    • SET AUTOCOMMIT=0 ½ûÖ¹×Ô¶¯Ìá½»
    • SET AUTOCOMMIT=1¿ªÆô×Ô¶¯Ìá½»
# mysql> use RUNOOB;
Database changed
# mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # ´´½¨Êý¾Ý±í
Query OK, 0 rows affected (0.04 sec)
 
# mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
# mysql> begin;  # ¿ªÊ¼ÊÂÎñ
Query OK, 0 rows affected (0.00 sec)
 
# mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
# mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
# mysql> commit; # Ìá½»ÊÂÎñ
Query OK, 0 rows affected (0.01 sec)
 
# mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
rows in set (0.01 sec)
 
# mysql> begin;    # ¿ªÊ¼ÊÂÎñ
Query OK, 0 rows affected (0.00 sec)
 
# mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
# mysql> rollback;   # »Ø¹ö
Query OK, 0 rows affected (0.00 sec)
 
# mysql>   select * from runoob_transaction_test;   # ÒòΪ»Ø¹ö365betÌåÓýÔÚÏßÊý¾ÝûÓвåÈë
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
rows in set (0.01 sec)
 
# mysql>

ÊÂÎï²âÊÔ
ÊÂÎï²âÊÔ
<?php
$dbhost = 'localhost:3306';  // mysql·þÎñÆ÷Ö÷»úµØÖ·
$dbuser = 'root';            // mysql̞
$dbpass = '123456';          // mysqlÓû§ÃûÃÜÂë
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Á¬½Óʧ°Ü: ' . mysqli_error($conn));
}
// ÉèÖñàÂ룬·ÀÖ¹ÖÐÎÄÂÒÂë
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'RUNOOB' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // ÉèÖÃΪ²»×Ô¶¯Ìá½»£¬ÒòΪMYSQLĬÈÏÁ¢¼´Ö´ÐÐ
mysqli_begin_transaction($conn);            // ¿ªÊ¼ÊÂÎñ¶¨Òå
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // Åжϵ±Ö´ÐÐʧ°Üʱ»Ø¹ö
}
 
if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // ÅжÏÖ´ÐÐʧ°Üʱ»Ø¹ö
}
mysqli_commit($conn);            //Ö´ÐÐÊÂÎñ
mysqli_close($conn);
?>

PHPÖÐʹÓÃÊÂÎïʾÀý
PHPÖÐʹÓÃÊÂÎñʵÀý

 

MySQL ALTERÃüÁî

  • µ±365betÌåÓýÔÚÏßÃÇÐèÒªÐÞ¸ÄÊý¾Ý±íÃû»òÕßÐÞ¸ÄÊý¾Ý±í×Ö¶Îʱ£¬¾ÍÐèҪʹÓõ½MySQL ALTERÃüÁî¡£
# root@host# mysql -u root -p password;
Enter password:*******
# mysql> use RUNOOB;
Database changed
# mysql> create table testalter_tbl
    -> (
    -> i INT,
    -> c CHAR(1)
    -> );
Query OK, 0 rows affected (0.05 sec)
# mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| i     | int(11) | YES  |     | NULL    |       |
| c     | char(1) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

ɾ³ý£¬Ìí¼Ó»òÐ޸ıí×Ö¶Î

# ÈçÏÂÃüÁîʹÓÃÁË ALTER ÃüÁî¼° DROP ×Ó¾äÀ´É¾³ýÒÔÉÏ´´½¨±íµÄ i ×ֶΣº
mysql> ALTER TABLE testalter_tbl  DROP i;

# Èç¹ûÊý¾Ý±íÖÐֻʣÓàÒ»¸ö×Ö¶ÎÔòÎÞ·¨Ê¹ÓÃDROPÀ´É¾³ý×ֶΡ£
# MySQL ÖÐʹÓà ADD ×Ó¾äÀ´ÏòÊý¾Ý±íÖÐÌí¼ÓÁУ¬ÈçÏÂʵÀýÔÚ±í testalter_tbl
# ÖÐÌí¼Ó i ×ֶΣ¬²¢¶¨ÒåÊý¾ÝÀàÐÍ:
mysql> ALTER TABLE testalter_tbl ADD i INT;
  • Ö´ÐÐÒÔÉÏÃüÁîºó£¬i ×ֶλá×Ô¶¯Ìí¼Óµ½Êý¾Ý±í×ֶεÄĩβ¡£
# mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)
# Èç¹ûÄãÐèÒªÖ¸¶¨ÐÂÔö×ֶεÄλÖ㬿ÉÒÔʹÓÃMySQLÌṩµÄ¹Ø¼ü×Ö FIRST (É趨λµÚÒ»ÁÐ)£¬ AFTER ×Ö¶ÎÃû£¨É趨λÓÚij¸ö×Ö¶ÎÖ®ºó£©¡£
  • ³¢ÊÔÒÔÏ ALTER TABLE Óï¾ä, ÔÚÖ´Ðгɹ¦ºó£¬Ê¹Óà SHOW COLUMNS ²é¿´±í½á¹¹µÄ±ä»¯£º
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT FIRST;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT AFTER c;
# FIRST ºÍ AFTER ¹Ø¼ü×Ö¿ÉÓÃÓÚ ADD Óë MODIFY ×Ӿ䣬365betÌåÓýÔÚÏßÈç¹ûÄãÏëÖØÖÃÊý¾Ý±í×ֶεÄλÖþÍÐèÒªÏÈʹÓà DROP ɾ³ý×Ö¶ÎÈ»ºóʹÓà 
  ADD À´Ìí¼Ó×ֶβ¢ÉèÖÃλÖá£

ÐÞ¸Ä×Ö¶ÎÀàÐͼ°Ãû³Æ

  • Èç¹ûÐèÒªÐÞ¸Ä×Ö¶ÎÀàÐͼ°Ãû³Æ, Äã¿ÉÒÔÔÚALTERÃüÁîÖÐʹÓà MODIFY »ò CHANGE ×Ó¾ä ¡£
# ÀýÈ磬°Ñ×ֶΠc µÄÀàÐÍ´Ó CHAR(1) ¸ÄΪ CHAR(10)£¬¿ÉÒÔÖ´ÐÐÒÔÏÂÃüÁî:
mysql> ALTER TABLE testalter_tbl MODIFY c CHAR(10);

# ʹÓà CHANGE ×Ó¾ä, Óï·¨ÓкܴóµÄ²»Í¬¡£ ÔÚ CHANGE ¹Ø¼ü×ÖÖ®ºó£¬½ô¸ú×ŵÄÊÇÄãÒªÐ޸ĵÄ×Ö¶ÎÃû£¬È»ºóÖ¸¶¨ÐÂ×Ö¶ÎÃû¼°ÀàÐÍ¡£³¢ÊÔÈçÏÂʵÀý£º
mysql> ALTER TABLE testalter_tbl CHANGE i j BIGINT;
mysql> ALTER TABLE testalter_tbl CHANGE j j INT;

ALTER TABLE ¶Ô Null ÖµºÍĬÈÏÖµµÄÓ°Ïì

  • µ±ÄãÐÞ¸Ä×Ö¶Îʱ£¬Äã¿ÉÒÔÖ¸¶¨ÊÇ·ñ°üº¬Öµ»òÕßÊÇ·ñÉèÖÃĬÈÏÖµ¡£
# ÒÔÏÂʵÀý£¬Ö¸¶¨×ֶΠj Ϊ NOT NULL ÇÒĬÈÏֵΪ100 ¡£
mysql> ALTER TABLE testalter_tbl 
    -> MODIFY j BIGINT NOT NULL DEFAULT 100;
# Èç¹ûÄã²»ÉèÖÃĬÈÏÖµ£¬MySQL»á×Ô¶¯ÉèÖøÃ×Ö¶ÎĬÈÏΪ NULL

ÐÞ¸Ä×Ö¶ÎĬÈÏÖµ

# mysql> ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
# mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | 1000    |       |
+-------+---------+------+-----+---------+-------+
rows in set (0.00 sec)
Äã¿ÉÒÔʹÓà ALTER À´ÐÞ¸Ä×ֶεÄĬÈÏÖµ£¬³¢ÊÔÒÔÏÂʵÀý£º
# mysql> ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
# mysql> SHOW COLUMNS FROM testalter_tbl;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c     | char(1) | YES  |     | NULL    |       |
| i     | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
rows in set (0.00 sec)
Changing a Table Type:
ÄãÒ²¿ÉÒÔʹÓà ALTER ÃüÁî¼° DROP×Ó¾äÀ´É¾³ý×ֶεÄĬÈÏÖµ£¬ÈçÏÂʵÀý£º
  • ÐÞ¸ÄÊý¾Ý±íÀàÐÍ£¬¿ÉÒÔʹÓà ALTER ÃüÁî¼° TYPE ×Ó¾äÀ´Íê³É¡£³¢ÊÔÒÔÏÂʵÀý£¬365betÌåÓýÔÚÏßÃǽ«±í testalter_tbl µÄÀàÐÍÐÞ¸ÄΪ MYISAM £º
  • ×¢Ò⣺²é¿´Êý¾Ý±íÀàÐÍ¿ÉÒÔʹÓà SHOW TABLE STATUS Óï¾ä¡£
# mysql> ALTER TABLE testalter_tbl ENGINE = MYISAM;
# mysql>  SHOW TABLE STATUS LIKE 'testalter_tbl'\G
# *************************** 1. row ****************
           Name: testalter_tbl
           Type: MyISAM
     Row_format: Fixed
           Rows: 0
 Avg_row_length: 0
    Data_length: 0
Max_data_length: 25769803775
   Index_length: 1024
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2007-06-03 08:04:36
    Update_time: 2007-06-03 08:04:36
     Check_time: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

Ð޸ıíÃû

  • Èç¹ûÐèÒªÐÞ¸ÄÊý¾Ý±íµÄÃû³Æ£¬¿ÉÒÔÔÚ ALTER TABLE Óï¾äÖÐʹÓà RENAME ×Ó¾äÀ´ÊµÏÖ¡£
  • ³¢ÊÔÒÔÏÂʵÀý½«Êý¾Ý±í testalter_tbl ÖØÃüÃûΪ alter_tbl£º
# mysql> ALTER TABLE testalter_tbl RENAME TO alter_tbl;
  • ALTER ÃüÁ¿ÉÒÔÓÃÀ´´´½¨¼°É¾³ýMySQLÊý¾Ý±íµÄË÷Òý£¬¸Ã¹¦ÄÜ¿´ÏÂÃæÕ½ÚÖнéÉÜ¡£

 

MySQL Ë÷Òý

  • MySQLË÷ÒýµÄ½¨Á¢¶ÔÓÚMySQLµÄ¸ßЧÔËÐÐÊǺÜÖØÒªµÄ£¬Ë÷Òý¿ÉÒÔ´ó´óÌá¸ßMySQLµÄ¼ìË÷Ëٶȡ£
  • ´ò¸ö±È·½£¬Èç¹ûºÏÀíµÄÉè¼ÆÇÒʹÓÃË÷ÒýµÄMySQLÊÇÒ»Á¾À¼²©»ùÄáµÄ»°£¬ÄÇôûÓÐÉè¼ÆºÍʹÓÃË÷ÒýµÄMySQL¾ÍÊÇÒ»¸öÈËÁ¦ÈýÂÖ³µ¡£
  • ÄúºÓï×ÖµäµÄĿ¼ҳ£¨Ë÷Òý£©´ò±È·½£¬365betÌåÓýÔÚÏßÃÇ¿ÉÒÔ°´Æ´Òô¡¢±Ê»­¡¢Æ«ÅÔ²¿Ê×µÈÅÅÐòµÄĿ¼£¨Ë÷Òý£©¿ìËÙ²éÕÒµ½ÐèÒªµÄ×Ö¡£
  • Ë÷Òý·Öµ¥ÁÐË÷ÒýºÍ×éºÏË÷Òý¡£µ¥ÁÐË÷Òý£¬¼´Ò»¸öË÷ÒýÖ»°üº¬µ¥¸öÁУ¬Ò»¸ö±í¿ÉÒÔÓжà¸öµ¥ÁÐË÷Òý£¬µ«Õâ²»ÊÇ×éºÏË÷Òý¡£×éºÏË÷Òý£¬¼´Ò»¸öË÷Òý°üº¬¶à¸öÁС£
  • ´´½¨Ë÷Òýʱ£¬ÄãÐèҪȷ±£¸ÃË÷ÒýÊÇÓ¦ÓÃÔÚ SQL ²éѯÓï¾äµÄÌõ¼þ(Ò»°ã×÷Ϊ WHERE ×Ó¾äµÄÌõ¼þ)¡£
  • ʵ¼ÊÉÏ£¬Ë÷ÒýÒ²ÊÇÒ»ÕÅ±í£¬¸Ã±í±£´æÁËÖ÷¼üÓëË÷Òý×ֶΣ¬²¢Ö¸ÏòʵÌå±íµÄ¼Ç¼¡£
  • ÉÏÃ涼ÔÚ˵ʹÓÃË÷ÒýµÄºÃ´¦£¬µ«¹ý¶àµÄʹÓÃË÷Òý½«»áÔì³ÉÀÄÓá£Òò´ËË÷ÒýÒ²»áÓÐËüµÄȱµã£ºËäÈ»Ë÷Òý´ó´óÌá¸ßÁ˲éѯËٶȣ¬Í¬Ê±È´»á½µµÍ¸üбíµÄËٶȣ¬Èç¶Ô±í½øÐÐINSERT¡¢UPDATEºÍDELETE¡£ÒòΪ¸üбíʱ£¬MySQL²»½öÒª±£´æÊý¾Ý£¬»¹Òª±£´æÒ»ÏÂË÷ÒýÎļþ¡£
  • ½¨Á¢Ë÷Òý»áÕ¼ÓôÅÅÌ¿Õ¼äµÄË÷ÒýÎļþ¡£

´´½¨ÆÕͨË÷Òý

# ÕâÊÇ×î»ù±¾µÄË÷Òý£¬ËüûÓÐÈκÎÏÞÖÆ¡£ËüÓÐÒÔϼ¸ÖÖ´´½¨·½Ê½£º
CREATE INDEX indexName ON mytable(username(length)); 
# Èç¹ûÊÇCHAR£¬VARCHARÀàÐÍ£¬length¿ÉÒÔСÓÚ×Ö¶Îʵ¼Ê³¤¶È£»Èç¹ûÊÇBLOBºÍTEXTÀàÐÍ£¬±ØÐëÖ¸¶¨ length¡£

Ð޸ıí½á¹¹(Ìí¼ÓË÷Òý)

# ALTER table tableName ADD INDEX indexName(columnName)
# ´´½¨±íµÄʱºòÖ±½ÓÖ¸¶¨
CREATE TABLE mytable(  
 ID INT NOT NULL,   
 username VARCHAR(16) NOT NULL,  
 INDEX [indexName] (username(length))  
 );
  
# ɾ³ýË÷ÒýµÄÓï·¨
DROP INDEX [indexName] ON mytable;

´´½¨Î¨Ò»Ë÷Òý

  • ËüÓëÇ°ÃæµÄÆÕͨË÷ÒýÀàËÆ£¬²»Í¬µÄ¾ÍÊÇ£ºË÷ÒýÁеÄÖµ±ØÐëΨһ£¬µ«ÔÊÐíÓпÕÖµ¡£Èç¹ûÊÇ×éºÏË÷Òý£¬ÔòÁÐÖµµÄ×éºÏ±ØÐëΨһ¡£ËüÓÐÒÔϼ¸ÖÖ´´½¨·½Ê½£º
# CREATE UNIQUE INDEX indexName ON mytable(username(length)) 

# Ð޸ıí½á¹¹
ALTER table mytable ADD UNIQUE [indexName] (username(length))

# ´´½¨±íµÄʱºòÖ±½ÓÖ¸¶¨
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
UNIQUE [indexName] (username(length))  
);

ʹÓÃALTER ÃüÁîÌí¼ÓºÍɾ³ýË÷Òý

  • ÓÐËÄÖÖ·½Ê½À´Ìí¼ÓÊý¾Ý±íµÄË÷Òý£º
    • ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):¸ÃÓï¾äÌí¼ÓÒ»¸öÖ÷¼ü£¬ÕâÒâζ×ÅË÷ÒýÖµ±ØÐëÊÇΨһµÄ£¬ÇÒ²»ÄÜΪNULL
    • ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):ÕâÌõÓï¾ä´´½¨Ë÷ÒýµÄÖµ±ØÐëÊÇΨһµÄ£¨³ýÁËNULLÍ⣬NULL¿ÉÄÜ»á³öÏÖ¶à´Î£©¡£
    • ALTER TABLE tbl_name ADD INDEX index_name (column_list):Ìí¼ÓÆÕͨË÷Òý£¬Ë÷ÒýÖµ¿É³öÏÖ¶à´Î¡£
    • ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):¸ÃÓï¾äÖ¸¶¨ÁËË÷ÒýΪ FULLTEXT £¬ÓÃÓÚÈ«ÎÄË÷Òý¡£
#ÒÔÏÂʵÀýΪÔÚ±íÖÐÌí¼ÓË÷Òý¡£
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

# Ä㻹¿ÉÒÔÔÚ ALTER ÃüÁîÖÐʹÓà DROP ×Ó¾äÀ´É¾³ýË÷Òý¡£³¢ÊÔÒÔÏÂʵÀýɾ³ýË÷Òý:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;

ʹÓà ALTER ÃüÁîÌí¼ÓºÍɾ³ýÖ÷¼ü

  • Ö÷¼üÖ»ÄÜ×÷ÓÃÓÚÒ»¸öÁÐÉÏ£¬Ìí¼ÓÖ÷¼üË÷Òýʱ£¬ÄãÐèҪȷ±£¸ÃÖ÷¼üĬÈϲ»Îª¿Õ£¨NOT NULL£©¡£ÊµÀýÈçÏ£º
# mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
# mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

# ÄãÒ²¿ÉÒÔʹÓà ALTER ÃüÁîɾ³ýÖ÷¼ü£º
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
# ɾ³ýÖ÷¼üʱֻÐèÖ¸¶¨PRIMARY KEY£¬µ«ÔÚɾ³ýË÷Òýʱ£¬Äã±ØÐëÖªµÀË÷ÒýÃû¡£

ÏÔʾË÷ÒýÐÅÏ¢

  • Äã¿ÉÒÔʹÓà SHOW INDEX ÃüÁîÀ´Áгö±íÖеÄÏà¹ØµÄË÷ÒýÐÅÏ¢¡£¿ÉÒÔͨ¹ýÌí¼Ó \G À´¸ñʽ»¯Êä³öÐÅÏ¢¡£
# ³¢ÊÔÒÔÏÂʵÀý:
mysql> SHOW INDEX FROM table_name; \G
........

 

MySQL ÁÙʱ±í

  • MySQL ÁÙʱ±íÔÚ365betÌåÓýÔÚÏßÃÇÐèÒª±£´æһЩÁÙʱÊý¾ÝʱÊǷdz£ÓÐÓõÄÁÙʱ±íÖ»ÔÚµ±Ç°Á¬½Ó¿É¼û£¬µ±¹Ø±ÕÁ¬½Óʱ£¬Mysql»á×Ô¶¯É¾³ý±í²¢ÊÍ·ÅËùÓпռ䡣
  • ÁÙʱ±íÔÚMySQL 3.23°æ±¾ÖÐÌí¼Ó£¬Èç¹ûÄãµÄMySQL°æ±¾µÍÓÚ 3.23°æ±¾¾ÍÎÞ·¨Ê¹ÓÃMySQLµÄÁÙʱ±í¡£²»¹ýÏÖÔÚÒ»°ãºÜÉÙÓÐÔÙʹÓÃÕâôµÍ°æ±¾µÄMySQLÊý¾Ý¿â·þÎñÁË¡£
  • MySQLÁÙʱ±íÖ»ÔÚµ±Ç°Á¬½Ó¿É¼û£¬Èç¹ûÄãʹÓÃPHP½Å±¾À´´´½¨MySQLÁÙʱ±í£¬ÄÇÿµ±PHP½Å±¾Ö´ÐÐÍê³Éºó£¬¸ÃÁÙʱ±íÒ²»á×Ô¶¯Ïú»Ù¡£
  • Èç¹ûÄãʹÓÃÁËÆäËûMySQL¿Í»§¶Ë³ÌÐòÁ¬½ÓMySQLÊý¾Ý¿â·þÎñÆ÷À´´´½¨ÁÙʱ±í£¬ÄÇôֻÓÐÔڹرտͻ§¶Ë³ÌÐòʱ²Å»áÏú»ÙÁÙʱ±í£¬µ±È»ÄãÒ²¿ÉÒÔÊÖ¶¯Ïú»Ù¡£

ʾÀý

mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
row in set (0.00 sec)
ÒÔÏÂչʾÁËʹÓÃMySQL ÁÙʱ±íµÄ¼òµ¥ÊµÀý£¬ÒÔϵÄSQL´úÂë¿ÉÒÔÊÊÓÃÓÚPHP½Å±¾µÄmysql_query()º¯Êý
  • µ±ÄãʹÓàSHOW TABLESÃüÁîÏÔʾÊý¾Ý±íÁбíʱ£¬Ä㽫ÎÞ·¨¿´µ½ SalesSummary±í¡£
  • Èç¹ûÄãÍ˳öµ±Ç°MySQL»á»°£¬ÔÙʹÓàSELECTÃüÁîÀ´¶ÁÈ¡Ô­ÏÈ´´½¨µÄÁÙʱ±íÊý¾Ý£¬ÄÇÄã»á·¢ÏÖÊý¾Ý¿âÖÐûÓиñíµÄ´æÔÚ£¬ÒòΪÔÚÄãÍ˳öʱ¸ÃÁÙʱ±íÒѾ­±»Ïú»ÙÁË¡£

ɾ³ýMySQL ÁÙʱ±í

  • ĬÈÏÇé¿öÏ£¬µ±Äã¶Ï¿ªÓëÊý¾Ý¿âµÄÁ¬½Óºó£¬ÁÙʱ±í¾Í»á×Ô¶¯±»Ïú»Ù¡£µ±È»ÄãÒ²¿ÉÒÔÔÚµ±Ç°MySQL»á»°Ê¹ÓàDROP TABLE ÃüÁîÀ´ÊÖ¶¯É¾³ýÁÙʱ±í¡£
mysql> CREATE TEMPORARY TABLE SalesSummary (
    -> product_name VARCHAR(50) NOT NULL
    -> , total_sales DECIMAL(12,2) NOT NULL DEFAULT 0.00
    -> , avg_unit_price DECIMAL(7,2) NOT NULL DEFAULT 0.00
    -> , total_units_sold INT UNSIGNED NOT NULL DEFAULT 0
);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO SalesSummary
    -> (product_name, total_sales, avg_unit_price, total_units_sold)
    -> VALUES
    -> ('cucumber', 100.25, 90, 2);

mysql> SELECT * FROM SalesSummary;
+--------------+-------------+----------------+------------------+
| product_name | total_sales | avg_unit_price | total_units_sold |
+--------------+-------------+----------------+------------------+
| cucumber     |      100.25 |          90.00 |                2 |
+--------------+-------------+----------------+------------------+
row in set (0.00 sec)
mysql> DROP TABLE SalesSummary;
mysql>  SELECT * FROM SalesSummary;
ERROR 1146: Table 'RUNOOB.SalesSummary' doesn't exist
ÒÔÏÂÊÇÊÖ¶¯É¾³ýÁÙʱ±íµÄʵÀý£º

 

MySQL ¸´ÖƱí

Èç¹û365betÌåÓýÔÚÏßÃÇÐèÒªÍêÈ«µÄ¸´ÖÆMySQLµÄÊý¾Ý±í£¬°üÀ¨±íµÄ½á¹¹£¬Ë÷Òý£¬Ä¬ÈÏÖµµÈ¡£ Èç¹û½ö½öʹÓÃCREATE TABLE ... SELECT ÃüÁÊÇÎÞ·¨ÊµÏֵġ£

  • ʹÓàSHOW CREATE TABLE ÃüÁî»ñÈ¡´´½¨Êý¾Ý±í(CREATE TABLE) Óï¾ä£¬¸ÃÓï¾ä°üº¬ÁËÔ­Êý¾Ý±íµÄ½á¹¹£¬Ë÷ÒýµÈ¡£
  • ¸´ÖÆÒÔÏÂÃüÁîÏÔʾµÄSQLÓï¾ä£¬ÐÞ¸ÄÊý¾Ý±íÃû£¬²¢Ö´ÐÐSQLÓï¾ä£¬Í¨¹ýÒÔÉÏÃüÁî ½«ÍêÈ«µÄ¸´ÖÆÊý¾Ý±í½á¹¹¡£
  • Èç¹ûÄãÏ븴ÖƱíµÄÄÚÈÝ£¬Äã¾Í¿ÉÒÔʹÓàINSERT INTO ... SELECT Óï¾äÀ´ÊµÏÖ¡£

ʵÀý

  • ³¢ÊÔÒÔÏÂʵÀýÀ´¸´ÖƱí runoob_tbl ¡£
# mysql> SHOW CREATE TABLE runoob_tbl \G;
*************************** 1. row ***************************
       Table: runoob_tbl
Create Table: CREATE TABLE `runoob_tbl` (
  `runoob_id` int(11) NOT NULL auto_increment,
  `runoob_title` varchar(100) NOT NULL default '',
  `runoob_author` varchar(40) NOT NULL default '',
  `submission_date` date default NULL,
  PRIMARY KEY  (`runoob_id`),
  UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE=InnoDB 
row in set (0.00 sec)

ERROR:
No query specified
²½ÖèÒ»£º»ñÈ¡Êý¾Ý±íµÄÍêÕû½á¹¹
# mysql> CREATE TABLE `clone_tbl` (
  -> `runoob_id` int(11) NOT NULL auto_increment,
  -> `runoob_title` varchar(100) NOT NULL default '',
  -> `runoob_author` varchar(40) NOT NULL default '',
  -> `submission_date` date default NULL,
  -> PRIMARY KEY  (`runoob_id`),
  -> UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (1.80 sec)
²½Öè¶þ£ºÐÞ¸ÄSQLÓï¾äµÄÊý¾Ý±íÃû£¬²¢Ö´ÐÐSQLÓï¾ä
# Ö´ÐÐÍêµÚ¶þ²½Öèºó£¬Ä㽫ÔÚÊý¾Ý¿âÖд´½¨ÐµĿË¡±í clone_tbl¡£ Èç¹ûÄãÏ뿽±´Êý¾Ý±íµÄÊý¾ÝÄã¿ÉÒÔʹÓà INSERT INTO... SELECT Óï¾äÀ´ÊµÏÖ¡£

mysql> INSERT INTO clone_tbl (runoob_id,
    ->                        runoob_title,
    ->                        runoob_author,
    ->                        submission_date)
    -> SELECT runoob_id,runoob_title,
    ->        runoob_author,submission_date
    -> FROM runoob_tbl;
Query OK, 3 rows affected (0.07 sec)
Records: 3  Duplicates: 0  Warnings: 0

# Ö´ÐÐÒÔÉϲ½Öèºó£¬Ä㽫ÍêÕûµÄ¸´ÖÆ±í£¬°üÀ¨±í½á¹¹¼°±íÊý¾Ý¡£
²½ÖèÈý£º

 

MySQL ÐòÁÐʹÓÃ

  • MySQL ÐòÁÐÊÇÒ»×éÕûÊý£º1, 2, 3, ...£¬ÓÉÓÚÒ»ÕÅÊý¾Ý±íÖ»ÄÜÓÐÒ»¸ö×Ö¶Î×ÔÔöÖ÷¼ü£¬ Èç¹ûÄãÏëʵÏÖÆäËû×Ö¶365betÌåÓýÔÚÏ߲ʵÏÖ×Ô¶¯Ôö¼Ó£¬¾Í¿ÉÒÔʹÓÃMySQLÐòÁÐÀ´ÊµÏÖ¡£

ʹÓà AUTO_INCREMENT

  • MySQL ÖÐ×î¼òµ¥Ê¹ÓÃÐòÁеķ½·¨¾ÍÊÇʹÓà MySQL AUTO_INCREMENT À´¶¨ÒåÁС£

ʵÀý

  • ÒÔÏÂʵÀýÖд´½¨ÁËÊý¾Ý±í insect£¬ insect ±íÖÐ id ÎÞÐèÖ¸¶¨Öµ¿ÉʵÏÖ×Ô¶¯Ôö³¤¡£
mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, # type of insect
    -> date DATE NOT NULL, # date collected
    -> origin VARCHAR(30) NOT NULL # where collected
);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO insect (id,name,date,origin) VALUES
    -> (NULL,'housefly','2001-09-10','kitchen'),
    -> (NULL,'millipede','2001-09-10','driveway'),
    -> (NULL,'grasshopper','2001-09-10','front yard');
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> SELECT * FROM insect ORDER BY id;
+----+-------------+------------+------------+
| id | name        | date       | origin     |
+----+-------------+------------+------------+
|  1 | housefly    | 2001-09-10 | kitchen    |
|  2 | millipede   | 2001-09-10 | driveway   |
|  3 | grasshopper | 2001-09-10 | front yard |
+----+-------------+------------+------------+
3 rows in set (0.00 sec)

»ñÈ¡AUTO_INCREMENTÖµ

  • ÔÚMySQLµÄ¿Í»§¶ËÖÐÄã¿ÉÒÔʹÓà SQLÖеÄLAST_INSERT_ID( ) º¯ÊýÀ´»ñÈ¡×îºóµÄ²åÈë±íÖеÄ×ÔÔöÁеÄÖµ¡£
  • ÔÚPHP»òPERL½Å±¾ÖÐÒ²ÌṩÁËÏàÓ¦µÄº¯ÊýÀ´»ñÈ¡×îºóµÄ²åÈë±íÖеÄ×ÔÔöÁеÄÖµ¡£
# ʹÓà mysql_insertid ÊôÐÔÀ´»ñÈ¡ AUTO_INCREMENT µÄÖµ¡£ ʵÀýÈçÏ£º

$dbh->do ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')");
my $seq = $dbh->{mysql_insertid};
PERLʵÀý
PHP ͨ¹ý mysql_insert_id ()º¯ÊýÀ´»ñÈ¡Ö´ÐеIJåÈëSQLÓï¾äÖÐ AUTO_INCREMENTÁеÄÖµ¡£

mysql_query ("INSERT INTO insect (name,date,origin)
VALUES('moth','2001-09-14','windowsill')", $conn_id);
$seq = mysql_insert_id ($conn_id);
PHPʵÀý

ÖØÖÃÐòÁÐ

  • Èç¹ûÄãɾ³ýÁËÊý¾Ý±íÖеĶàÌõ¼Ç¼£¬²¢Ï£Íû¶ÔÊ£ÏÂÊý¾ÝµÄAUTO_INCREMENTÁнøÐÐÖØÐÂÅÅÁУ¬ÄÇôÄã¿ÉÒÔͨ¹ýɾ³ý×ÔÔöµÄÁУ¬È»ºóÖØÐÂÌí¼ÓÀ´ÊµÏÖ¡£ ²»¹ý¸Ã²Ù×÷Òª·Ç³£Ð¡ÐÄ£¬Èç¹ûÔÚɾ³ýµÄͬʱÓÖÓÐмǼÌí¼Ó£¬ÓпÉÄÜ»á³öÏÖÊý¾Ý»ìÂÒ¡£²Ù×÷ÈçÏÂËùʾ£º
# mysql> ALTER TABLE insect DROP id;
# mysql> ALTER TABLE insect
    -> ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
    -> ADD PRIMARY KEY (id);
  • ÉèÖÃÐòÁеĿªÊ¼Öµ
  • Ò»°ãÇé¿öÏÂÐòÁеĿªÊ¼ÖµÎª1£¬µ«Èç¹ûÄãÐèÒªÖ¸¶¨Ò»¸ö¿ªÊ¼Öµ100£¬ÄÇ365betÌåÓýÔÚÏßÃÇ¿ÉÒÔͨ¹ýÒÔÏÂÓï¾äÀ´ÊµÏÖ£º
# mysql> CREATE TABLE insect
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> PRIMARY KEY (id),
    -> name VARCHAR(30) NOT NULL, 
    -> date DATE NOT NULL,
    -> origin VARCHAR(30) NOT NULL
)engine=innodb auto_increment=100 charset=utf8;
# »òÕßÄãÒ²¿ÉÒÔÔÚ±í´´½¨³É¹¦ºó£¬Í¨¹ýÒÔÏÂÓï¾äÀ´ÊµÏÖ£º
# mysql> ALTER TABLE t AUTO_INCREMENT = 100;

 

MySQL ´¦ÀíÖظ´Êý¾Ý

  • ÓÐЩ MySQL Êý¾Ý±íÖпÉÄÜ´æÔÚÖظ´µÄ¼Ç¼£¬ÓÐЩÇé¿ö365betÌåÓýÔÚÏßÃÇÔÊÐíÖظ´Êý¾ÝµÄ´æÔÚ£¬µ«ÓÐʱºò365betÌåÓýÔÚÏßÃÇÒ²ÐèҪɾ³ýÕâЩÖظ´µÄÊý¾Ý¡£
  • ±¾Õ½Ú365betÌåÓýÔÚÏßÃǽ«Îª´ó¼Ò½éÉÜÈçºÎ·ÀÖ¹Êý¾Ý±í³öÏÖÖظ´Êý¾Ý¼°ÈçºÎɾ³ýÊý¾Ý±íÖеÄÖظ´Êý¾Ý¡£

·ÀÖ¹±íÖгöÏÖÖظ´Êý¾Ý

  • Äã¿ÉÒÔÔÚ MySQL Êý¾Ý±íÖÐÉèÖÃÖ¸¶¨µÄ×Ö¶ÎΪ PRIMARY KEY£¨Ö÷¼ü£© »òÕß UNIQUE£¨Î¨Ò»£© Ë÷ÒýÀ´±£Ö¤Êý¾ÝµÄΨһÐÔ¡£
  • ÈÃ365betÌåÓýÔÚÏßÃdz¢ÊÔÒ»¸öʵÀý£ºÏ±íÖÐÎÞË÷Òý¼°Ö÷¼ü£¬365betÌåÓýÔÚÏ߸ñíÔÊÐí³öÏÖ¶àÌõÖظ´¼Ç¼¡£
CREATE TABLE person_tbl
(
    first_name CHAR(20),
    last_name CHAR(20),
    sex CHAR(10)
);
# Èç¹ûÄãÏëÉèÖñíÖÐ×ֶΠfirst_name£¬last_name Êý¾Ý²»ÄÜÖظ´£¬Äã¿ÉÒÔÉèÖÃË«Ö÷¼üģʽÀ´ÉèÖÃÊý¾ÝµÄΨһÐÔ£¬ Èç¹ûÄãÉèÖÃÁËË«Ö÷¼ü£¬
  ÄÇôÄǸö¼üµÄĬÈÏÖµ²»ÄÜΪ NULL£¬¿ÉÉèÖÃΪ NOT NULL¡£ÈçÏÂËùʾ£º

CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   PRIMARY KEY (last_name, first_name)
);
  • Èç¹û365betÌåÓýÔÚÏßÃÇÉèÖÃÁËΨһË÷Òý£¬ÄÇôÔÚ²åÈëÖظ´Êý¾Ýʱ£¬SQL Óï¾ä½«ÎÞ·¨Ö´Ðгɹ¦,²¢Å׳ö´í¡£
  • INSERT IGNORE INTO Óë INSERT INTO µÄÇø±ð¾ÍÊÇ INSERT IGNORE »áºöÂÔÊý¾Ý¿âÖÐÒѾ­´æÔÚµÄÊý¾Ý£¬Èç¹ûÊý¾Ý¿âûÓÐÊý¾Ý£¬¾Í²åÈëеÄÊý¾Ý£¬Èç¹ûÓÐÊý¾ÝµÄ»°¾ÍÌø¹ýÕâÌõÊý¾Ý¡£ÕâÑù¾Í¿ÉÒÔ±£ÁôÊý¾Ý¿âÖÐÒѾ­´æÔÚÊý¾Ý£¬´ïµ½ÔÚ¼ä϶ÖвåÈëÊý¾ÝµÄÄ¿µÄ¡£
  • ÒÔÏÂʵÀýʹÓÃÁË INSERT IGNORE INTO£¬Ö´Ðк󲻻á³ö´í£¬Ò²²»»áÏòÊý¾Ý±íÖвåÈëÖظ´Êý¾Ý£º
# mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 1 row affected (0.00 sec)
# mysql> INSERT IGNORE INTO person_tbl (last_name, first_name)
    -> VALUES( 'Jay', 'Thomas');
Query OK, 0 rows affected (0.00 sec)
  • INSERT IGNORE INTO µ±²åÈëÊý¾Ýʱ£¬ÔÚÉèÖÃÁ˼ǼµÄΨһÐÔºó£¬Èç¹û²åÈëÖظ´Êý¾Ý£¬½«²»·µ»Ø´íÎó£¬Ö»ÒÔ¾¯¸æÐÎʽ·µ»Ø¡£ ¶ø REPLACE INTO Èç¹û´æÔÚ primary »ò unique ÏàͬµÄ¼Ç¼£¬ÔòÏÈɾ³ýµô¡£ÔÙ²åÈëмǼ¡£
  • Áí365ÌåÓýͶעÉèÖÃÊý¾ÝµÄΨһÐÔ·½·¨ÊÇÌí¼ÓÒ»¸ö UNIQUE Ë÷Òý£¬ÈçÏÂËùʾ£º
CREATE TABLE person_tbl
(
   first_name CHAR(20) NOT NULL,
   last_name CHAR(20) NOT NULL,
   sex CHAR(10),
   UNIQUE (last_name, first_name)
);

ͳ¼ÆÖظ´Êý¾Ý

  • ÒÔÏÂ365betÌåÓýÔÚÏßÃǽ«Í³¼Æ±íÖÐ first_name ºÍ last_nameµÄÖظ´¼Ç¼Êý£º

 

# mysql> SELECT COUNT(*) as repetitions, last_name, first_name
    -> FROM person_tbl
    -> GROUP BY last_name, first_name
    -> HAVING repetitions > 1;

 

  • ÒÔÉϲéѯÓï¾ä½«·µ»Ø person_tbl ±íÖÐÖظ´µÄ¼Ç¼Êý¡£ Ò»°ãÇé¿öÏ£¬²éѯÖظ´µÄÖµ£¬ÇëÖ´ÐÐÒÔϲÙ×÷£º
    • È·¶¨ÄÄÒ»Áаüº¬µÄÖµ¿ÉÄÜ»áÖظ´¡£
    • ÔÚÁÐÑ¡ÔñÁбíʹÓÃCOUNT(*)ÁгöµÄÄÇЩÁС£
    • ÔÚGROUP BY×Ó¾äÖÐÁгöµÄÁС£
    • HAVING×Ó¾äÉèÖÃÖظ´Êý´óÓÚ1¡£
  • ¹ýÂËÖظ´Êý¾Ý
# Èç¹ûÄãÐèÒª¶ÁÈ¡²»Öظ´µÄÊý¾Ý¿ÉÒÔÔÚ SELECT Óï¾äÖÐʹÓà DISTINCT ¹Ø¼ü×ÖÀ´¹ýÂËÖظ´Êý¾Ý¡£
# mysql> SELECT DISTINCT last_name, first_name
    -> FROM person_tbl;
# ÄãÒ²¿ÉÒÔʹÓà GROUP BY À´¶ÁÈ¡Êý¾Ý±íÖв»Öظ´µÄÊý¾Ý£º
# mysql> SELECT last_name, first_name
    -> FROM person_tbl
    -> GROUP BY (last_name, first_name);
  • ɾ³ýÖظ´Êý¾Ý
# Èç¹ûÄãÏëɾ³ýÊý¾Ý±íÖеÄÖظ´Êý¾Ý£¬Äã¿ÉÒÔʹÓÃÒÔϵÄSQLÓï¾ä£º

# mysql> CREATE TABLE tmp SELECTlast_name, first_name, sex FROM person_tblGROUP BY (last_name, first_name, sex);
# mysql> DROP TABLE person_tbl;
# mysql> ALTER TABLE tmp RENAME TO person_tbl;
# µ±È»ÄãÒ²¿ÉÒÔÔÚÊý¾Ý±íÖÐÌí¼Ó INDEX£¨Ë÷Òý£© ºÍ PRIMAY KEY£¨Ö÷¼ü£©ÕâÖÖ¼òµ¥µÄ·½·¨À´É¾³ý±íÖеÄÖظ´¼Ç¼¡£·½·¨ÈçÏ£º

# mysql> ALTER IGNORE TABLE person_tbl
    -> ADD PRIMARY KEY (last_name, first_name);

 

MySQL µ¼³öµ¼ÈëÊý¾Ý

  • MySQLÖÐÄã¿ÉÒÔʹÓÃSELECT...INTO OUTFILEÓï¾äÀ´¼òµ¥µÄµ¼³öÊý¾Ýµ½365ÌåÓýͶעÎļþÉÏ¡£
  • ʹÓà SELECT ... INTO OUTFILE Óï¾äµ¼³öÊý¾Ý
# ÒÔÏÂʵÀýÖÐ365betÌåÓýÔÚÏßÃǽ«Êý¾Ý±í runoob_tbl Êý¾Ýµ¼³öµ½ /tmp/runoob.txt ÎļþÖÐ:
mysql> SELECT * FROM runoob_tbl 
    -> INTO OUTFILE '/tmp/runoob.txt';

# Äã¿ÉÒÔͨ¹ýÃüÁîÑ¡ÏîÀ´ÉèÖÃÊý¾ÝÊä³öµÄÖ¸¶¨¸ñʽ£¬ÒÔÏÂʵÀýΪµ¼³ö CSV ¸ñʽ£º
mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
# ÔÚÏÂÃæµÄÀý×ÓÖУ¬Éú³ÉÒ»¸öÎļþ£¬¸÷ÖµÓöººÅ¸ô¿ª¡£ÕâÖÖ¸ñʽ¿ÉÒÔ±»Ðí¶à³ÌÐòʹÓá£
SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

SELECT ... INTO OUTFILE Óï¾äÓÐÒÔÏÂÊôÐÔ:

  • LOAD DATA INFILEÊÇSELECT ... INTO OUTFILEµÄÄæ²Ù×÷£¬SELECT¾ä·¨¡£ÎªÁ˽«Ò»¸öÊý¾Ý¿âµÄÊý¾ÝдÈëÒ»¸öÎļþ£¬Ê¹ÓÃSELECT ... INTO OUTFILE£¬ÎªÁ˽«Îļþ¶Á»ØÊý¾Ý¿â£¬Ê¹ÓÃLOAD DATA INFILE¡£
  • SELECT...INTO OUTFILE 'file_name'ÐÎʽµÄSELECT¿ÉÒ԰ѱ»Ñ¡ÔñµÄÐÐдÈëÒ»¸öÎļþÖС£¸ÃÎļþ±»´´½¨µ½·þÎñÆ÷Ö÷»úÉÏ£¬Òò´ËÄú±ØÐëÓµÓÐFILEȨÏÞ£¬²ÅÄÜʹÓôËÓï·¨¡£
  • Êä³ö²»ÄÜÊÇÒ»¸öÒÑ´æÔÚµÄÎļþ¡£·ÀÖ¹ÎļþÊý¾Ý±»´Û¸Ä¡£
  • ÄãÐèÒªÓÐÒ»¸öµÇ½·þÎñÆ÷µÄÕ˺ÅÀ´¼ìË÷Îļþ¡£·ñÔò SELECT ... INTO OUTFILE ²»»áÆðÈκÎ×÷Óá£
  • ÔÚUNIXÖУ¬¸ÃÎļþ±»´´½¨ºóÊǿɶÁµÄ£¬È¨ÏÞÓÉMySQL·þÎñÆ÷ËùÓµÓС£ÕâÒâζ×Å£¬ËäÈ»Äã¾Í¿ÉÒÔ¶ÁÈ¡¸ÃÎļþ£¬µ«¿ÉÄÜÎÞ·¨½«Æäɾ³ý¡£

µ¼³ö±í×÷ΪԭʼÊý¾Ý

  • mysqldump ÊÇ mysql ÓÃÓÚת´æ´¢Êý¾Ý¿âµÄʵÓóÌÐò¡£ËüÖ÷Òª²úÉúÒ»¸ö SQL ½Å±¾£¬ÆäÖаüº¬´ÓÍ·ÖØд´½¨Êý¾Ý¿âËù±ØÐèµÄÃüÁî CREATE TABLE INSERT µÈ¡£
  • ʹÓàmysqldump µ¼³öÊý¾ÝÐèҪʹÓà--tab Ñ¡ÏîÀ´Ö¸¶¨µ¼³öÎļþÖ¸¶¨µÄĿ¼£¬¸ÃÄ¿±ê±ØÐëÊÇ¿ÉдµÄ¡£
#ÒÔÏÂʵÀý½«Êý¾Ý±í runoob_tbl µ¼³öµ½ /tmp Ŀ¼ÖУº
$ mysqldump -u root -p --no-create-info \
            --tab=/tmp RUNOOB runoob_tbl
password ******

µ¼³ö SQL ¸ñʽµÄÊý¾Ý

$ mysqldump -u root -p RUNOOB runoob_tbl > dump.txt
password ******
ÒÔÉÏÃüÁî´´½¨µÄÎļþÄÚÈÝÈçÏ£º

-- MySQL dump 8.23
--
-- Host: localhost    Database: RUNOOB
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `runoob_tbl`
--

CREATE TABLE runoob_tbl (
  runoob_id int(11) NOT NULL auto_increment,
  runoob_title varchar(100) NOT NULL default '',
  runoob_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (runoob_id),
  UNIQUE KEY AUTHOR_INDEX (runoob_author)
) TYPE=MyISAM;

--
-- Dumping data for table `runoob_tbl`
--

INSERT INTO runoob_tbl 
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO runoob_tbl 
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

# Èç¹ûÄãÐèÒªµ¼³öÕû¸öÊý¾Ý¿âµÄÊý¾Ý£¬¿ÉÒÔʹÓÃÒÔÏÂÃüÁ
$ mysqldump -u root -p RUNOOB > database_dump.txt
password ******

# Èç¹ûÐèÒª±¸·ÝËùÓÐÊý¾Ý¿â£¬¿ÉÒÔʹÓÃÒÔÏÂÃüÁ
$ mysqldump -u root -p --all-databases > database_dump.txt
password ******
--all-databases Ñ¡ÏîÔÚ MySQL 3.23.12 ¼°ÒÔºó°æ±¾¼ÓÈë¡£
# ¸Ã·½·¨¿ÉÓÃÓÚʵÏÖÊý¾Ý¿âµÄ±¸·Ý²ßÂÔ¡£
µ¼³ö SQL ¸ñʽµÄÊý¾Ýµ½Ö¸¶¨Îļþ£¬ÈçÏÂËùʾ£º
# Èç¹ûÄãÐèÒª½«Êý¾Ý¿½±´ÖÁÆäËûµÄ MySQL ·þÎñÆ÷ÉÏ, Äã¿ÉÒÔmysqldump ÃüÁîÖÐÖ¸¶¨Êý¾Ý¿âÃû¼°Êý¾Ý±í¡£

# ÔÚÔ´Ö÷»úÉÏÖ´ÐÐÒÔÏÂÃüÁ½«Êý¾Ý±¸·ÝÖÁ dump.txt ÎļþÖÐ:
$ mysqldump -u root -p database_name table_name > dump.txt
password *****

#Èç¹ûÍêÕû±¸·ÝÊý¾Ý¿â£¬ÔòÎÞÐèʹÓÃÌض¨µÄ±íÃû³Æ¡£
# Èç¹ûÄãÐèÒª½«±¸·ÝµÄÊý¾Ý¿âµ¼Èëµ½MySQL·þÎñÆ÷ÖУ¬¿ÉÒÔʹÓÃÒÔÏÂÃüÁʹÓÃÒÔÏÂÃüÁîÄãÐèҪȷÈÏÊý¾Ý¿âÒѾ­´´½¨£º
$ mysql -u root -p database_name < dump.txt
password *****

# ÄãÒ²¿ÉÒÔʹÓÃÒÔÏÂÃüÁµ¼³öµÄÊý¾ÝÖ±½Óµ¼Èëµ½Ô¶³ÌµÄ·þÎñÆ÷ÉÏ£¬µ«ÇëÈ·±£Á½Ì¨·þÎñÆ÷ÊÇÏàͨµÄ£¬ÊÇ¿ÉÒÔÏ໥·ÃÎʵģº
$ mysqldump -u root -p database_name \
       | mysql -h other-host.com database_name
# ÒÔÉÏÃüÁîÖÐʹÓÃÁ˹ܵÀÀ´½«µ¼³öµÄÊý¾Ýµ¼Èëµ½Ö¸¶¨µÄÔ¶³ÌÖ÷»úÉÏ¡£
½«Êý¾Ý±í¼°Êý¾Ý¿â¿½±´ÖÁÆäËûÖ÷»ú

MySQL µ¼ÈëÊý¾Ý

  • mysql ÃüÁîµ¼Èë
# ʹÓà mysql ÃüÁîµ¼ÈëÓï·¨¸ñʽΪ£º
mysql -uÓû§Ãû    -pÃÜÂë    <  Òªµ¼ÈëµÄÊý¾Ý¿âÊý¾Ý(runoob.sql)

# ʵÀý£º
# mysql -uroot -p123456 < runoob.sql
# ÒÔÉÏÃüÁ½«±¸·ÝµÄÕû¸öÊý¾Ý¿â runoob.sql µ¼Èë¡£
  • source ÃüÁîµ¼Èë
# source ÃüÁîµ¼ÈëÊý¾Ý¿âÐèÒªÏȵǼµ½Êý¿âÖնˣº

mysql> create database abc;      # ´´½¨Êý¾Ý¿â
mysql> use abc;                  # ʹÓÃÒÑ´´½¨µÄÊý¾Ý¿â 
mysql> set names utf8;           # ÉèÖñàÂë
mysql> source /home/abc/abc.sql  # µ¼È뱸·ÝÊý¾Ý¿â
  • ʹÓà LOAD DATA µ¼ÈëÊý¾Ý
  • MySQL ÖÐÌṩÁËLOAD DATA INFILEÓï¾äÀ´²åÈëÊý¾Ý¡£ ÒÔÏÂʵÀýÖн«´Óµ±Ç°Ä¿Â¼ÖжÁÈ¡Îļþ dump.txt £¬½«¸ÃÎļþÖеÄÊý¾Ý²åÈëµ½µ±Ç°Êý¾Ý¿âµÄ mytbl ±íÖС£
# mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;
  • Èç¹ûÖ¸¶¨LOCAL¹Ø¼ü´Ê£¬Ôò±íÃ÷´Ó¿Í»§Ö÷»úÉÏ°´Â·¾¶¶ÁÈ¡Îļþ¡£Èç¹ûûÓÐÖ¸¶¨£¬ÔòÎļþÔÚ·þÎñÆ÷ÉÏ°´Â·¾¶¶ÁÈ¡Îļþ¡£
  • ÄãÄÜÃ÷È·µØÔÚLOAD DATAÓï¾äÖÐÖ¸³öÁÐÖµµÄ·Ö¸ô·ûºÍÐÐβ±ê¼Ç£¬µ«ÊÇĬÈϱê¼ÇÊǶ¨Î»·ûºÍ»»Ðзû¡£
  • Á½¸öÃüÁîµÄ FIELDS ºÍ LINES ×Ó¾äµÄÓï·¨ÊÇÒ»ÑùµÄ¡£Á½¸ö×Ӿ䶼ÊÇ¿ÉÑ¡µÄ£¬µ«ÊÇÈç¹ûÁ½¸öͬʱ±»Ö¸¶¨£¬FIELDS ×Ó¾ä±ØÐë³öÏÖÔÚ LINES ×Ó¾ä֮ǰ¡£
  • Èç¹ûÓû§Ö¸¶¨Ò»¸ö FIELDS ×Ӿ䣬ËüµÄ×Ó¾ä £¨TERMINATED BY¡¢[OPTIONALLY] ENCLOSED BY ºÍ ESCAPED BY) Ò²ÊÇ¿ÉÑ¡µÄ£¬²»¹ý£¬Óû§±ØÐëÖÁÉÙÖ¸¶¨ËüÃÇÖеÄÒ»¸ö¡£
# mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
       -> FIELDS TERMINATED BY ':'
       -> LINES TERMINATED BY '\r\n';
  • LOAD DATAĬÈÏÇé¿öÏÂÊÇ°´ÕÕÊý¾ÝÎļþÖÐÁеÄ˳Ðò²åÈëÊý¾ÝµÄÈç¹ûÊý¾ÝÎļþÖеÄÁÐÓë²åÈë±íÖеÄÁв»Ò»Ö£¬ÔòÐèÒªÖ¸¶¨ÁеÄ˳Ðò¡£
  • È磬ÔÚÊý¾ÝÎļþÖеÄÁÐ˳ÐòÊÇ a,b,c£¬µ«ÔÚ²åÈë±íµÄÁÐ˳ÐòΪb,c,a£¬ÔòÊý¾Ýµ¼ÈëÓï·¨ÈçÏ£º
# mysql> LOAD DATA LOCAL INFILE 'dump.txt' 
        -> INTO TABLE mytbl (b, c, a);
  • ʹÓà mysqlimport µ¼ÈëÊý¾Ý
    • mysqlimport ¿Í»§¶ËÌṩÁË LOAD DATA INFILEQL Óï¾äµÄÒ»¸öÃüÁîÐнӿڡ£mysqlimport µÄ´ó¶àÊýÑ¡ÏîÖ±½Ó¶ÔÓ¦ LOAD DATA INFILE ×Ӿ䡣
# ´ÓÎļþ dump.txt Öн«Êý¾Ýµ¼Èëµ½ mytbl Êý¾Ý±íÖÐ, ¿ÉÒÔʹÓÃÒÔÏÂÃüÁ
$ mysqlimport -u root -p --local mytbl dump.txt
password *****

# mysqlimport ÃüÁî¿ÉÒÔÖ¸¶¨Ñ¡ÏîÀ´ÉèÖÃÖ¸¶¨¸ñʽ,ÃüÁîÓï¾ä¸ñʽÈçÏ£º
$ mysqlimport -u root -p --local --fields-terminated-by=":" \
   --lines-terminated-by="\r\n"  mytbl dump.txt
password *****

# mysqlimport Óï¾äÖÐʹÓà --columns Ñ¡ÏîÀ´ÉèÖÃÁеÄ˳Ðò£º
$ mysqlimport -u root -p --local --columns=b,c,a \
    mytbl dump.txt
password *****
  • mysqlimportµÄ³£ÓÃÑ¡Ïî½éÉÜ
Ñ¡ÏÄÜ
-d or --delete ÐÂÊý¾Ýµ¼ÈëÊý¾Ý±íÖÐ֮ǰɾ³ýÊý¾ÝÊý¾Ý±íÖеÄËùÓÐÐÅÏ¢
-f or --force ²»¹ÜÊÇ·ñÓöµ½´íÎó£¬mysqlimport½«Ç¿ÖƼÌÐø²åÈëÊý¾Ý
-i or --ignore mysqlimportÌø¹ý»òÕߺöÂÔÄÇЩÓÐÏàͬΨһ ¹Ø¼ü×ÖµÄÐУ¬ µ¼ÈëÎļþÖеÄÊý¾Ý½«±»ºöÂÔ¡£
-l or -lock-tables Êý¾Ý±»²åÈë֮ǰËøס±í£¬ÕâÑù¾Í·ÀÖ¹ÁË£¬ ÄãÔÚ¸üÐÂÊý¾Ý¿âʱ£¬Óû§µÄ²éѯºÍ¸üÐÂÊܵ½Ó°Ïì¡£
-r or -replace Õâ¸öÑ¡ÏîÓ룭iÑ¡ÏîµÄ×÷ÓÃÏà·´£»´ËÑ¡ÏÌæ´ú ±íÖÐÓÐÏàͬΨһ¹Ø¼ü×ֵļǼ¡£
--fields-enclosed- by= char Ö¸¶¨365ÌåÓýͶעÎļþÖÐÊý¾ÝµÄ¼Ç¼ʱÒÔʲôÀ¨ÆðµÄ£¬ ºÜ¶àÇé¿öÏ Êý¾ÝÒÔË«ÒýºÅÀ¨Æ𡣠ĬÈϵÄÇé¿öÏÂÊý¾ÝÊÇûÓб»×Ö·ûÀ¨ÆðµÄ¡£
--fields-terminated- by=char Ö¸¶¨¸÷¸öÊý¾ÝµÄÖµÖ®¼äµÄ·Ö¸ô·û£¬ÔÚ¾äºÅ·Ö¸ôµÄÎļþÖУ¬ ·Ö¸ô·ûÊǾäºÅ¡£Äú¿ÉÒÔÓôËÑ¡ÏîÖ¸¶¨Êý¾ÝÖ®¼äµÄ·Ö¸ô·û¡£ ĬÈϵķָô·ûÊÇÌø¸ñ·û£¨Tab£©
--lines-terminated- by=str ´ËÑ¡ÏîÖ¸¶¨365ÌåÓýͶעÎļþÖÐÐÐÓëÐÐÖ®¼äÊý¾ÝµÄ·Ö¸ô×Ö·û´® »òÕß×Ö·û¡£ ĬÈϵÄÇé¿öÏÂmysqlimportÒÔnewlineΪÐзָô·û¡£ Äú¿ÉÒÔÑ¡ÔñÓÃÒ»¸ö×Ö·û´®À´Ìæ´úÒ»¸öµ¥¸öµÄ×Ö·û£º Ò»¸öÐÂÐлòÕßÒ»¸ö»Ø³µ¡£
  • mysqlimport ÃüÁî³£ÓõÄÑ¡ÏÓÐ -v ÏÔʾ°æ±¾£¨version£©£¬ -p ÌáʾÊäÈëÃÜÂ루password£©µÈ¡£
# Ê×ÏÈlinux ϲ鿴mysqlÏà¹ØĿ¼
[root@op-workorder bin]# whereis mysql
 mysql: /usr/bin/mysql /usr/lib64/mysql /usr/include/mysql

# µ¼³öÊý¾Ý¿âÓÃmysqldumpÃüÁî
cd /home/work/mysql/bin # ÏÈcdµ½mysqlµÄÔËÐз¾¶Ï£¬ÔÙÖ´ÐÐÒ»ÏÂÃüÁî¡¡

# 1. µ¼³öÊý¾ÝºÍ±í½á¹¹
¡¡¡¡¡¡./mysqldump -uroot -p bsp > bsp.sql
¡¡¡¡¡¡mysqldump -uÓû§Ãû -pÃÜÂë Êý¾Ý¿âÃû > Êý¾Ý¿âÃû.sql

# 2. Ö»µ¼³ö±í½á¹¹
¡¡¡¡¡¡mysqldump -uÓû§Ãû -pÃÜÂë -d Êý¾Ý¿âÃû > Êý¾Ý¿âÃû.sql
     mysqldump -uroot -p -d dbname > dbname .sql

# 3. µ¼ÈëÊý¾Ý¿â
¡¡¡¡# 1¡¢Ê×ÏȽ¨¿ÕÊý¾Ý¿â¡¡
¡¡¡¡¡¡¡¡mysql>create database bsp charset utf8;
¡¡¡¡# 2¡¢µ¼ÈëÊý¾Ý¿â£¨·½·¨Ò»£©
¡¡¡¡¡¡¡¡¡¡# Ñ¡ÔñÊý¾Ý¿â
¡¡¡¡¡¡¡¡¡¡¡¡mysql>use bsp;
¡¡¡¡     # µ¼ÈëÊý¾Ý£¨×¢ÒâsqlÎļþµÄ·¾¶£©
¡¡¡¡¡¡¡¡¡¡¡¡mysql>source /home/work/project/bsp/bsp.sql
¡¡¡¡# 3¡¢µ¼ÈëÊý¾Ý¿â£¨·½·¨¶þ£©
¡¡¡¡¡¡¡¡ mysql -uÓû§Ãû -pÃÜÂë Êý¾Ý¿âÃû < Êý¾Ý¿âÃû.sql
¡¡¡¡# 4¡¢Ö´Ðе¼³öÊý¾Ý¿â±¨´íÎÊÌâ½â¾ö
# ±¨´í£ºmysqldump: Couldn't execute 'SHOW VARIABLES LIKE 'gtid\_mode'': Table 
      'performance_schema.session_variables' doesn't exist (1146)
1£©mysql_upgrade -u root -p --force #¸üÐÂ
2£©service mysql restart #restart mysql service
3£©mysqldump -u root -p test > test.sql #ÖØб¸·ÝÊý¾Ý

 

MySQL º¯Êý

  • MySQL ÓкܶàÄÚÖõĺ¯Êý£¬ÒÔÏÂÁгöÁËÕâЩº¯ÊýµÄ˵Ã÷¡£

  • MySQL ×Ö·û´®º¯Êý

º¯ÊýÃèÊöʵÀý
ASCII(s) ·µ»Ø×Ö·û´® s µÄµÚÒ»¸ö×Ö·ûµÄ ASCII Âë¡£

·µ»Ø CustomerName ×ֶεÚÒ»¸ö×ÖĸµÄ ASCII Â룺

SELECT ASCII(CustomerName) AS NumCodeOfFirstChar
FROM Customers;
CHAR_LENGTH(s) ·µ»Ø×Ö·û´® s µÄ×Ö·ûÊý

·µ»Ø×Ö·û´® RUNOOB µÄ×Ö·ûÊý

SELECT CHAR_LENGTH("RUNOOB") AS LengthOfString;
CHARACTER_LENGTH(s) ·µ»Ø×Ö·û´® s µÄ×Ö·ûÊý

·µ»Ø×Ö·û´® RUNOOB µÄ×Ö·ûÊý

SELECT CHARACTER_LENGTH("RUNOOB") AS LengthOfString;
CONCAT(s1,s2...sn) ×Ö·û´® s1,s2 µÈ¶à¸ö×Ö·û´®ºÏ²¢ÎªÒ»¸ö×Ö·û´®

ºÏ²¢¶à¸ö×Ö·û´®

SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;
CONCAT_WS(x, s1,s2...sn) ͬ CONCAT(s1,s2,...) º¯Êý£¬µ«ÊÇ365ÌåÓýͶע×Ö·û´®Ö®¼äÒª¼ÓÉÏ x£¬x ¿ÉÒÔÊÇ·Ö¸ô·û

ºÏ²¢¶à¸ö×Ö·û´®£¬²¢Ìí¼Ó·Ö¸ô·û£º

SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!")AS ConcatenatedString;
FIELD(s,s1,s2...) ·µ»ØµÚÒ»¸ö×Ö·û´® s ÔÚ×Ö·û´®Áбí(s1,s2...)ÖеÄλÖÃ

·µ»Ø×Ö·û´® c ÔÚÁбíÖµÖеÄλÖãº

SELECT FIELD("c", "a", "b", "c", "d", "e");
FIND_IN_SET(s1,s2) ·µ»ØÔÚ×Ö·û´®s2ÖÐÓës1Æ¥ÅäµÄ×Ö·û´®µÄλÖÃ

·µ»Ø×Ö·û´® c ÔÚÖ¸¶¨×Ö·û´®ÖеÄλÖãº

SELECT FIND_IN_SET("c", "a,b,c,d,e");
FORMAT(x,n) º¯Êý¿ÉÒÔ½«Êý×Ö x ½øÐиñʽ»¯ "#,###.##", ½« x ±£Áôµ½Ð¡Êýµãºó n 룬×îºóһλËÄÉáÎåÈë¡£

¸ñʽ»¯Êý×Ö "#,###.##" ÐÎʽ£º

SELECT FORMAT(250500.5634, 2);     -- Êä³ö 250,500.56
INSERT(s1,x,len,s2) ×Ö·û´® s2 Ìæ»» s1 µÄ x λÖÿªÊ¼³¤¶ÈΪ len µÄ×Ö·û´®

´Ó×Ö·û´®µÚÒ»¸öλÖÿªÊ¼µÄ 6 ¸ö×Ö·ûÌ滻Ϊ runoob£º

SELECT INSERT("google.com", 1, 6, "runnob");  -- Êä³ö£ºrunoob.com
LOCATE(s1,s) ´Ó×Ö·û´® s ÖлñÈ¡ s1 µÄ¿ªÊ¼Î»ÖÃ

»ñÈ¡ b ÔÚ×Ö·û´® abc ÖеÄλÖãº

SELECT LOCATE('st','myteststring');  -- 5

·µ»Ø×Ö·û´® abc ÖÐ b µÄλÖãº

SELECT LOCATE('b', 'abc') -- 2
LCASE(s) ½«×Ö·û´® s µÄËùÓÐ×Öĸ±ä³ÉСд×Öĸ

×Ö·û´® RUNOOB ת»»ÎªÐ¡Ð´£º

SELECT LCASE('RUNOOB') -- runoob
LEFT(s,n) ·µ»Ø×Ö·û´® s µÄÇ° n ¸ö×Ö·û

·µ»Ø×Ö·û´® runoob ÖеÄÇ°Á½¸ö×Ö·û£º

SELECT LEFT('runoob',2) -- ru
LOWER(s) ½«×Ö·û´® s µÄËùÓÐ×Öĸ±ä³ÉСд×Öĸ

×Ö·û´® RUNOOB ת»»ÎªÐ¡Ð´£º

SELECT LOWER('RUNOOB') -- runoob
LPAD(s1,len,s2) ÔÚ×Ö·û´® s1 µÄ¿ªÊ¼´¦Ìî³ä×Ö·û´® s2£¬Ê¹×Ö·û´®³¤¶È´ïµ½ len

½«×Ö·û´® xx Ìî³äµ½ abc ×Ö·û´®µÄ¿ªÊ¼´¦£º

SELECT LPAD('abc',5,'xx') -- xxabc
LTRIM(s) È¥µô×Ö·û´® s ¿ªÊ¼´¦µÄ¿Õ¸ñ

È¥µô×Ö·û´® RUNOOB¿ªÊ¼´¦µÄ¿Õ¸ñ£º

SELECT LTRIM("    RUNOOB") AS LeftTrimmedString;-- RUNOOB
MID(s,n,len) ´Ó×Ö·û´® s µÄ n λÖýØÈ¡³¤¶ÈΪ len µÄ×Ó×Ö·û´®£¬Í¬ SUBSTRING(s,n,len)

´Ó×Ö·û´® RUNOOB ÖÐµÄµÚ 2 ¸öλÖýØÈ¡ 3¸ö ×Ö·û£º

SELECT MID("RUNOOB", 2, 3) AS ExtractString; -- UNO
POSITION(s1 IN s) ´Ó×Ö·û´® s ÖлñÈ¡ s1 µÄ¿ªÊ¼Î»ÖÃ

·µ»Ø×Ö·û´® abc ÖÐ b µÄλÖãº

SELECT POSITION('b' in 'abc') -- 2
REPEAT(s,n) ½«×Ö·û´® s Öظ´ n ´Î

½«×Ö·û´® runoob Öظ´Èý´Î£º

SELECT REPEAT('runoob',3) -- runoobrunoobrunoob
REPLACE(s,s1,s2) ½«×Ö·û´® s2 Ìæ´ú×Ö·û´® s ÖеÄ×Ö·û´® s1

½«×Ö·û´® abc ÖеÄ×Ö·û a Ì滻Ϊ×Ö·û x£º

SELECT REPLACE('abc','a','x') --xbc
REVERSE(s) ½«×Ö·û´®sµÄ˳Ðò·´¹ýÀ´

½«×Ö·û´® abc µÄ˳Ðò·´¹ýÀ´£º

SELECT REVERSE('abc') -- cba
RIGHT(s,n) ·µ»Ø×Ö·û´® s µÄºó n ¸ö×Ö·û

·µ»Ø×Ö·û´® runoob µÄºóÁ½¸ö×Ö·û£º

SELECT RIGHT('runoob',2) -- ob
RPAD(s1,len,s2) ÔÚ×Ö·û´® s1 µÄ½áβ´¦Ìí¼Ó×Ö·û´® s2£¬Ê¹×Ö·û´®µÄ³¤¶È´ïµ½ len

½«×Ö·û´® xx Ìî³äµ½ abc ×Ö·û´®µÄ½áβ´¦£º

SELECT RPAD('abc',5,'xx') -- abcxx
RTRIM(s) È¥µô×Ö·û´® s ½áβ´¦µÄ¿Õ¸ñ

È¥µô×Ö·û´® RUNOOB µÄĩβ¿Õ¸ñ£º

SELECT RTRIM("RUNOOB     ") AS RightTrimmedString;   -- RUNOOB
SPACE(n) ·µ»Ø n ¸ö¿Õ¸ñ

·µ»Ø 10 ¸ö¿Õ¸ñ£º

SELECT SPACE(10);
STRCMP(s1,s2) ±È½Ï×Ö·û´® s1 ºÍ s2£¬Èç¹û s1 Óë s2 ÏàµÈ·µ»Ø 0 £¬Èç¹û s1>s2 ·µ»Ø 1£¬Èç¹û s1<s2 ·µ»Ø -1

±È½Ï×Ö·û´®£º

SELECT STRCMP("runoob", "runoob");  -- 0
SUBSTR(s, start, length) ´Ó×Ö·û´® s µÄ start λÖýØÈ¡³¤¶ÈΪ length µÄ×Ó×Ö·û´®

´Ó×Ö·û´® RUNOOB ÖÐµÄµÚ 2 ¸öλÖýØÈ¡ 3¸ö ×Ö·û£º

SELECT SUBSTR("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING(s, start, length) ´Ó×Ö·û´® s µÄ start λÖýØÈ¡³¤¶ÈΪ length µÄ×Ó×Ö·û´®

´Ó×Ö·û´® RUNOOB ÖÐµÄµÚ 2 ¸öλÖýØÈ¡ 3¸ö ×Ö·û£º

SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO
SUBSTRING_INDEX(s, delimiter, number) ·µ»Ø´Ó×Ö·û´® s µÄµÚ number ¸ö³öÏֵķָô·û delimiter Ö®ºóµÄ×Ó´®¡£
Èç¹û number ÊÇÕýÊý£¬·µ»ØµÚ number ¸ö×Ö·û×ó±ßµÄ×Ö·û´®¡£
Èç¹û number ÊǸºÊý£¬·µ»ØµÚ(number µÄ¾ø¶ÔÖµ(´ÓÓÒ±ßÊý))¸ö×Ö·ûÓұߵÄ×Ö·û´®¡£		 
SELECT SUBSTRING_INDEX('a*b','*',1) -- a
SELECT SUBSTRING_INDEX('a*b','*',-1)    -- b
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX('a*b*c*d*e','*',3),'*',-1)    -- c
TRIM(s) È¥µô×Ö·û´® s ¿ªÊ¼ºÍ½áβ´¦µÄ¿Õ¸ñ

È¥µô×Ö·û´® RUNOOB µÄÊ×β¿Õ¸ñ£º

SELECT TRIM('    RUNOOB    ') AS TrimmedString;
UCASE(s) ½«×Ö·û´®×ª»»Îª´óд

½«×Ö·û´® runoob ת»»Îª´óд£º

SELECT UCASE("runoob"); -- RUNOOB
UPPER(s) ½«×Ö·û´®×ª»»Îª´óд

½«×Ö·û´® runoob ת»»Îª´óд£º

SELECT UPPER("runoob"); -- RUNOOB
  • MySQL Êý×Öº¯Êý
º¯ÊýÃûÃèÊöʵÀý
ABS(x) ·µ»Ø x µÄ¾ø¶ÔÖµ¡¡¡¡

·µ»Ø -1 µÄ¾ø¶ÔÖµ£º

SELECT ABS(-1) -- ·µ»Ø1
ACOS(x) Çó x µÄ·´ÓàÏÒÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT ACOS(0.25);
ASIN(x) Çó·´ÕýÏÒÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT ASIN(0.25);
ATAN(x) Çó·´ÕýÇÐÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT ATAN(2.5);
ATAN2(n, m) Çó·´ÕýÇÐÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT ATAN2(-0.8, 2);
AVG(expression) ·µ»ØÒ»¸ö±í´ïʽµÄƽ¾ùÖµ£¬expression ÊÇÒ»¸ö×Ö¶Î

·µ»Ø Products ±íÖÐPrice ×ֶεÄƽ¾ùÖµ£º

SELECT AVG(Price) AS AveragePrice FROM Products;
CEIL(x) ·µ»Ø´óÓÚ»òµÈÓÚ x µÄ×îСÕûÊý¡¡ 
SELECT CEIL(1.5) -- ·µ»Ø2
CEILING(x) ·µ»Ø´óÓÚ»òµÈÓÚ x µÄ×îСÕûÊý¡¡ 
SELECT CEIL(1.5) -- ·µ»Ø2
COS(x) ÇóÓàÏÒÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT COS(2);
COT(x) ÇóÓàÇÐÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT COT(6);
COUNT(expression) ·µ»Ø²éѯµÄ¼Ç¼×ÜÊý£¬expression ²ÎÊýÊÇÒ»¸ö×ֶλòÕß * ºÅ

·µ»Ø Products ±íÖÐ products ×Ö¶Î×ܹ²ÓжàÉÙÌõ¼Ç¼£º

SELECT COUNT(ProductID) AS NumberOfProducts FROM Products;
DEGREES(x) ½«»¡¶Èת»»Îª½Ç¶È¡¡¡¡ 
SELECT DEGREES(3.1415926535898) -- 180
n DIV m Õû³ý£¬n Ϊ±»³ýÊý£¬m Ϊ³ýÊý

¼ÆËã 10 ³ýÓÚ 5£º

SELECT 10 DIV 5;  -- 2
EXP(x) ·µ»Ø e µÄ x ´Î·½¡¡¡¡

¼ÆËã e µÄÈý´Î·½£º

SELECT EXP(3) -- 20.085536923188
FLOOR(x) ·µ»ØСÓÚ»òµÈÓÚ x µÄ×î´óÕûÊý¡¡¡¡

СÓÚ»òµÈÓÚ 1.5 µÄÕûÊý£º

SELECT FLOOR(1.5) -- ·µ»Ø1
GREATEST(expr1, expr2, expr3, ...) ·µ»ØÁбíÖеÄ×î´óÖµ

·µ»ØÒÔÏÂÊý×ÖÁбíÖеÄ×î´óÖµ£º

SELECT GREATEST(3, 12, 34, 8, 25); -- 34

·µ»ØÒÔÏÂ×Ö·û´®ÁбíÖеÄ×î´óÖµ£º

SELECT GREATEST("Google", "Runoob", "Apple");   -- Runoob
LEAST(expr1, expr2, expr3, ...) ·µ»ØÁбíÖеÄ×îСֵ

·µ»ØÒÔÏÂÊý×ÖÁбíÖеÄ×îСֵ£º

SELECT LEAST(3, 12, 34, 8, 25); -- 3

·µ»ØÒÔÏÂ×Ö·û´®ÁбíÖеÄ×îСֵ£º

SELECT LEAST("Google", "Runoob", "Apple");   -- Apple
LN ·µ»ØÊý×ÖµÄ×ÔÈ»¶ÔÊý

·µ»Ø 2 µÄ×ÔÈ»¶ÔÊý£º

SELECT LN(2);  -- 0.6931471805599453
LOG(x) ·µ»Ø×ÔÈ»¶ÔÊý(ÒÔ e Ϊµ×µÄ¶ÔÊý)¡¡¡¡ 
SELECT LOG(20.085536923188) -- 3
LOG10(x) ·µ»ØÒÔ 10 Ϊµ×µÄ¶ÔÊý¡¡¡¡ 
SELECT LOG10(100) -- 2
LOG2(x) ·µ»ØÒÔ 2 Ϊµ×µÄ¶ÔÊý

·µ»ØÒÔ 2 Ϊµ× 6 µÄ¶ÔÊý£º

SELECT LOG2(6);  -- 2.584962500721156
MAX(expression) ·µ»Ø×ֶΠexpression ÖеÄ×î´óÖµ

·µ»ØÊý¾Ý±í Products ÖÐ×ֶΠPrice µÄ×î´óÖµ£º

SELECT MAX(Price) AS LargestPrice FROM Products;
MIN(expression) ·µ»Ø×ֶΠexpression ÖеÄ×îСֵ

·µ»ØÊý¾Ý±í Products ÖÐ×ֶΠPrice µÄ×îСֵ£º

SELECT MIN(Price) AS LargestPrice FROM Products;
MOD(x,y) ·µ»Ø x ³ýÒÔ y ÒÔºóµÄÓàÊý¡¡

5 ³ýÓÚ 2 µÄÓàÊý£º

SELECT MOD(5,2) -- 1
PI() ·µ»ØÔ²ÖÜÂÊ(3.141593£©¡¡¡¡ 
SELECT PI() --3.141593
POW(x,y) ·µ»Ø x µÄ y ´Î·½¡¡

2 µÄ 3 ´Î·½£º

SELECT POW(2,3) -- 8
POWER(x,y) ·µ»Ø x µÄ y ´Î·½¡¡

2 µÄ 3 ´Î·½£º

SELECT POWER(2,3) -- 8
RADIANS(x) ½«½Ç¶Èת»»Îª»¡¶È¡¡¡¡

180 ¶Èת»»Îª»¡¶È£º

SELECT RADIANS(180) -- 3.1415926535898
RAND() ·µ»Ø 0 µ½ 1 µÄËæ»úÊý¡¡¡¡ 
SELECT RAND() --0.93099315644334
ROUND(x) ·µ»ØÀë x ×î½üµÄÕûÊý 
SELECT ROUND(1.23456) --1
SIGN(x) ·µ»Ø x µÄ·ûºÅ£¬x ÊǸºÊý¡¢0¡¢ÕýÊý·Ö±ð·µ»Ø -1¡¢0 ºÍ 1¡¡ 
SELECT SIGN(-10) -- (-1)
SIN(x) ÇóÕýÏÒÖµ(²ÎÊýÊÇ»¡¶È)¡¡¡¡ 
SELECT SIN(RADIANS(30)) -- 0.5
SQRT(x) ·µ»ØxµÄƽ·½¸ù¡¡¡¡

25 µÄƽ·½¸ù£º

SELECT SQRT(25) -- 5
SUM(expression) ·µ»ØÖ¸¶¨×ֶεÄ×ܺÍ

¼ÆËã OrderDetails ±íÖÐ×ֶΠQuantity µÄ×ܺͣº

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
TAN(x) ÇóÕýÇÐÖµ(²ÎÊýÊÇ»¡¶È) 
SELECT TAN(1.75);  -- -5.52037992250933
TRUNCATE(x,y) ·µ»ØÊýÖµ x ±£Áôµ½Ð¡Êýµãºó y λµÄÖµ£¨Óë ROUND ×î´óµÄÇø±ðÊDz»»á½øÐÐËÄÉáÎåÈ룩 
SELECT TRUNCATE(1.23456,3) -- 1.234
  • MySQL ÈÕÆÚº¯Êý
º¯ÊýÃûÃèÊöʵÀý
ADDDATE(d,n) ¼ÆËãÆðʼÈÕÆÚ d ¼ÓÉÏ n ÌìµÄÈÕÆÚ 
SELECT ADDDATE("2017-06-15", INTERVAL 10 DAY);
->2017-06-25
ADDTIME(t,n) ʱ¼ä t ¼ÓÉÏ n ÃëµÄʱ¼ä 
SELECT ADDTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:16 (Ãë)
CURDATE() ·µ»Øµ±Ç°ÈÕÆÚ 
SELECT CURDATE();
-> 2018-09-19
CURRENT_DATE() ·µ»Øµ±Ç°ÈÕÆÚ 
SELECT CURRENT_DATE();
-> 2018-09-19
CURRENT_TIME ·µ»Øµ±Ç°Ê±¼ä 
SELECT CURRENT_TIME();
-> 19:59:02
CURRENT_TIMESTAMP() ·µ»Øµ±Ç°ÈÕÆÚºÍʱ¼ä 
SELECT CURRENT_TIMESTAMP()
-> 2018-09-19 20:57:43
CURTIME() ·µ»Øµ±Ç°Ê±¼ä 
SELECT CURTIME();
-> 19:59:02
DATE() ´ÓÈÕÆÚ»òÈÕÆÚʱ¼ä±í´ïʽÖÐÌáÈ¡ÈÕÆÚÖµ 
SELECT DATE("2017-06-15");    
-> 2017-06-15
DATEDIFF(d1,d2) ¼ÆËãÈÕÆÚ d1->d2 Ö®¼äÏà¸ôµÄÌìÊý 
SELECT DATEDIFF('2001-01-01','2001-02-02')
-> -32
DATE_ADD(d£¬INTERVAL expr type) ¼ÆËãÆðʼÈÕÆÚ d ¼ÓÉÏÒ»¸öʱ¼ä¶ÎºóµÄÈÕÆÚ 
SELECT ADDDATE('2011-11-11 11:11:11',1)
-> 2011-11-12 11:11:11    (ĬÈÏÊÇÌì)

SELECT ADDDATE('2011-11-11 11:11:11', INTERVAL 5 MINUTE)
-> 2011-11-11 11:16:11 (TYPEµÄÈ¡ÖµÓëÉÏÃæÄǸöÁгöÀ´µÄº¯ÊýÀàËÆ)
DATE_FORMAT(d,f) °´±í´ïʽ fµÄÒªÇóÏÔʾÈÕÆÚ d 
SELECT DATE_FORMAT('2011-11-11 11:11:11','%Y-%m-%d %r')
-> 2011-11-11 11:11:11 AM
DATE_SUB(date,INTERVAL expr type) º¯Êý´ÓÈÕÆÚ¼õÈ¥Ö¸¶¨µÄʱ¼ä¼ä¸ô¡£

Orders ±íÖÐ OrderDate ×ֶμõÈ¥ 2 Ì죺

SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
DAY(d) ·µ»ØÈÕÆÚÖµ d µÄÈÕÆÚ²¿·Ö 
SELECT DAY("2017-06-15");  
-> 15
DAYNAME(d) ·µ»ØÈÕÆÚ d ÊÇÐÇÆÚ¼¸£¬Èç Monday,Tuesday 
SELECT DAYNAME('2011-11-11 11:11:11')
->Friday
DAYOFMONTH(d) ¼ÆËãÈÕÆÚ d ÊDZ¾Ôµĵڼ¸Ìì 
SELECT DAYOFMONTH('2011-11-11 11:11:11')
->11
DAYOFWEEK(d) ÈÕÆÚ d ½ñÌìÊÇÐÇÆÚ¼¸£¬1 ÐÇÆÚÈÕ£¬2 ÐÇÆÚÒ»£¬ÒÔ´ËÀàÍÆ 
SELECT DAYOFWEEK('2011-11-11 11:11:11')
->6
DAYOFYEAR(d) ¼ÆËãÈÕÆÚ d ÊDZ¾ÄêµÄµÚ¼¸Ìì 
SELECT DAYOFYEAR('2011-11-11 11:11:11')
->315
EXTRACT(type FROM d) ´ÓÈÕÆÚ d ÖлñÈ¡Ö¸¶¨µÄÖµ£¬type Ö¸¶¨·µ»ØµÄÖµ¡£
type¿ÉȡֵΪ£º
  • MICROSECOND
  • SECOND
  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • MINUTE_SECOND
  • HOUR_MICROSECOND
  • HOUR_SECOND
  • HOUR_MINUTE
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • YEAR_MONTH
 
SELECT EXTRACT(MINUTE FROM '2011-11-11 11:11:11') 
-> 11
FROM_DAYS(n) ¼ÆËã´Ó 0000 Äê 1 Ô 1 ÈÕ¿ªÊ¼ n ÌìºóµÄÈÕÆÚ 
SELECT FROM_DAYS(1111)
-> 0003-01-16
HOUR(t) ·µ»Ø t ÖеÄСʱֵ 
SELECT HOUR('1:2:3')
-> 1
LAST_DAY(d) ·µ»Ø¸ø¸ø¶¨ÈÕÆÚµÄÄÇÒ»Ô·ݵÄ×îºóÒ»Ìì 
SELECT LAST_DAY("2017-06-20");
-> 2017-06-30
LOCALTIME() ·µ»Øµ±Ç°ÈÕÆÚºÍʱ¼ä 
SELECT LOCALTIME()
-> 2018-09-19 20:57:43
LOCALTIMESTAMP() ·µ»Øµ±Ç°ÈÕÆÚºÍʱ¼ä 
SELECT LOCALTIMESTAMP()
-> 2018-09-19 20:57:43
MAKEDATE(year, day-of-year) »ùÓÚ¸ø¶¨²ÎÊýÄê·Ý year ºÍËùÔÚÄêÖеÄÌìÊýÐòºÅ day-of-year ·µ»ØÒ»¸öÈÕÆÚ 
SELECT MAKEDATE(2017, 3);
-> 2017-01-03
MAKETIME(hour, minute, second) ×éºÏʱ¼ä£¬²ÎÊý·Ö±ðΪСʱ¡¢·ÖÖÓ¡¢Ãë 
SELECT MAKETIME(11, 35, 4);
-> 11:35:04
MICROSECOND(date) ·µ»ØÈÕÆÚ²ÎÊýËù¶ÔÓ¦µÄ΢ÃëÊý 
SELECT MICROSECOND("2017-06-20 09:34:00.000023");
-> 23
MINUTE(t) ·µ»Ø t ÖеķÖÖÓÖµ 
SELECT MINUTE('1:2:3')
-> 2
MONTHNAME(d) ·µ»ØÈÕÆÚµ±ÖеÄÔ·ÝÃû³Æ£¬Èç November 
SELECT MONTHNAME('2011-11-11 11:11:11')
-> November
MONTH(d) ·µ»ØÈÕÆÚdÖеÄÔ·ÝÖµ£¬1 µ½ 12 
SELECT MONTH('2011-11-11 11:11:11')
->11
NOW() ·µ»Øµ±Ç°ÈÕÆÚºÍʱ¼ä 
SELECT NOW()
-> 2018-09-19 20:57:43
PERIOD_ADD(period, number) Ϊ Äê-Ô ×éºÏÈÕÆÚÌí¼ÓÒ»¸öʱ¶Î 
SELECT PERIOD_ADD(201703, 5);   
-> 201708
PERIOD_DIFF(period1, period2) ·µ»ØÁ½¸öʱ¶ÎÖ®¼äµÄÔ·ݲîÖµ 
SELECT PERIOD_DIFF(201710, 201703);
-> 7
QUARTER(d) ·µ»ØÈÕÆÚdÊǵڼ¸¼¾½Ú£¬·µ»Ø 1 µ½ 4 
SELECT QUARTER('2011-11-11 11:11:11')
-> 4
SECOND(t) ·µ»Ø t ÖеÄÃëÖÓÖµ 
SELECT SECOND('1:2:3')
-> 3
SEC_TO_TIME(s) ½«ÒÔÃëΪµ¥Î»µÄʱ¼ä s ת»»ÎªÊ±·ÖÃëµÄ¸ñʽ 
SELECT SEC_TO_TIME(4320)
-> 01:12:00
STR_TO_DATE(string, format_mask) ½«×Ö·û´®×ª±äΪÈÕÆÚ 
SELECT STR_TO_DATE("August 10 2017", "%M %d %Y");
-> 2017-08-10
SUBDATE(d,n) ÈÕÆÚ d ¼õÈ¥ n ÌìºóµÄÈÕÆÚ 
SELECT SUBDATE('2011-11-11 11:11:11', 1)
->2011-11-10 11:11:11 (ĬÈÏÊÇÌì)
SUBTIME(t,n) ʱ¼ä t ¼õÈ¥ n ÃëµÄʱ¼ä 
SELECT SUBTIME('2011-11-11 11:11:11', 5)
->2011-11-11 11:11:06 (Ãë)
SYSDATE() ·µ»Øµ±Ç°ÈÕÆÚºÍʱ¼ä 
SELECT SYSDATE()
-> 2018-09-19 20:57:43
TIME(expression) ÌáÈ¡´«Èë±í´ïʽµÄʱ¼ä²¿·Ö 
SELECT TIME("19:30:10");
-> 19:30:10
TIME_FORMAT(t,f) °´±í´ïʽ f µÄÒªÇóÏÔʾʱ¼ä t 
SELECT TIME_FORMAT('11:11:11','%r')
11:11:11 AM
TIME_TO_SEC(t) ½«Ê±¼ä t ת»»ÎªÃë 
SELECT TIME_TO_SEC('1:12:00')
-> 4320
TIMEDIFF(time1, time2) ¼ÆËãʱ¼ä²îÖµ 
SELECT TIMEDIFF("13:10:11", "13:10:10");
-> 00:00:01
TIMESTAMP(expression, interval) µ¥¸ö²ÎÊýʱ£¬º¯Êý·µ»ØÈÕÆÚ»òÈÕÆÚʱ¼ä±í´ïʽ£»ÓÐ2¸ö²ÎÊýʱ£¬½«²ÎÊý¼ÓºÍ 
SELECT TIMESTAMP("2017-07-23",  "13:10:11");
-> 2017-07-23 13:10:11
TO_DAYS(d) ¼ÆËãÈÕÆÚ d ¾àÀë 0000 Äê 1 Ô 1 ÈÕµÄÌìÊý 
SELECT TO_DAYS('0001-01-01 01:01:01')
-> 366
WEEK(d) ¼ÆËãÈÕÆÚ d ÊDZ¾ÄêµÄµÚ¼¸¸öÐÇÆÚ£¬·¶Î§ÊÇ 0 µ½ 53 
SELECT WEEK('2011-11-11 11:11:11')
-> 45
WEEKDAY(d) ÈÕÆÚ d ÊÇÐÇÆÚ¼¸£¬0 365ÌåÓýͶעÐÇÆÚÒ»£¬1 365ÌåÓýͶעÐÇÆÚ¶þ 
SELECT WEEKDAY("2017-06-15");
-> 3
WEEKOFYEAR(d) ¼ÆËãÈÕÆÚ d ÊDZ¾ÄêµÄµÚ¼¸¸öÐÇÆÚ£¬·¶Î§ÊÇ 0 µ½ 53 
SELECT WEEKOFYEAR('2011-11-11 11:11:11')
-> 45
YEAR(d) ·µ»ØÄê·Ý 
SELECT YEAR("2017-06-15");
-> 2017
YEARWEEK(date, mode) ·µ»ØÄê·Ý¼°µÚ¼¸ÖÜ£¨0µ½53£©£¬mode ÖÐ 0 365ÌåÓýͶעÖÜÌ죬1365ÌåÓýͶעÖÜÒ»£¬ÒÔ´ËÀàÍÆ 
SELECT YEARWEEK("2017-06-15");
-> 201724
  • MySQL ¸ß¼¶º¯Êý
º¯ÊýÃûÃèÊöʵÀý
BIN(x) ·µ»Ø x µÄ¶þ½øÖƱàÂë

15 µÄ 2 ½øÖƱàÂë:

SELECT BIN(15); -- 1111
BINARY(s) ½«×Ö·û´® s ת»»Îª¶þ½øÖÆ×Ö·û´® 
SELECT BINARY "RUNOOB";
-> RUNOOB
 
CASE expression
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
   ...
    WHEN conditionN THEN resultN
    ELSE result
END
 CASE 365ÌåÓýͶעº¯Êý¿ªÊ¼£¬END 365ÌåÓýͶעº¯Êý½áÊø¡£Èç¹û condition1 ³ÉÁ¢£¬Ôò·µ»Ø result1, Èç¹û condition2 ³ÉÁ¢£¬Ôò·µ»Ø result2£¬µ±È«²¿²»³ÉÁ¢Ôò·µ»Ø result£¬¶øµ±ÓÐÒ»¸ö³ÉÁ¢Ö®ºó£¬ºóÃæµÄ¾Í²»Ö´ÐÐÁË¡£ 
SELECT CASE 
¡¡¡¡WHEN 1 > 0
¡¡¡¡THEN '1 > 0'
¡¡¡¡WHEN 2 > 0
¡¡¡¡THEN '2 > 0'
¡¡¡¡ELSE '3 > 0'
¡¡¡¡END
->1 > 0
CAST(x AS type) ת»»Êý¾ÝÀàÐÍ

×Ö·û´®ÈÕÆÚת»»ÎªÈÕÆÚ£º

SELECT CAST("2017-08-29" AS DATE);
-> 2017-08-29
COALESCE(expr1, expr2, ...., expr_n) ·µ»Ø²ÎÊýÖеĵÚÒ»¸ö·Ç¿Õ±í´ïʽ£¨´Ó×óÏòÓÒ£© 
SELECT COALESCE(NULL, NULL, NULL, 'runoob.com', NULL, 'google.com');
-> runoob.com
CONNECTION_ID() ·µ»Ø·þÎñÆ÷µÄÁ¬½ÓÊý 
SELECT CONNECTION_ID();
-> 4292835
CONV(x,f1,f2) ·µ»Ø f1 ½øÖÆÊý±ä³É f2 ½øÖÆÊý 
SELECT CONV(15, 10, 2);
-> 1111
CONVERT(s USING cs) º¯Êý½«×Ö·û´® s µÄ×Ö·û¼¯±ä³É cs 
SELECT CHARSET('ABC')
->utf-8    

SELECT CHARSET(CONVERT('ABC' USING gbk))
->gbk
CURRENT_USER() ·µ»Øµ±Ç°Óû§ 
SELECT CURRENT_USER();
-> guest@%
DATABASE() ·µ»Øµ±Ç°Êý¾Ý¿âÃû 
SELECT DATABASE();   
-> runoob
IF(expr,v1,v2) Èç¹û±í´ïʽ expr ³ÉÁ¢£¬·µ»Ø½á¹û v1£»·ñÔò£¬·µ»Ø½á¹û v2¡£ 
SELECT IF(1 > 0,'ÕýÈ·','´íÎó')    
->ÕýÈ·
IFNULL(v1,v2) Èç¹û v1 µÄÖµ²»Îª NULL£¬Ôò·µ»Ø v1£¬·ñÔò·µ»Ø v2¡£ 
SELECT IFNULL(null,'Hello Word')
->Hello Word
ISNULL(expression) Åжϱí´ïʽÊÇ·ñΪ NULL 
SELECT ISNULL(NULL);
->1
LAST_INSERT_ID() ·µ»Ø×î½üÉú³ÉµÄ AUTO_INCREMENT Öµ 
SELECT LAST_INSERT_ID();
->6
NULLIF(expr1, expr2) ±È½ÏÁ½¸ö×Ö·û´®£¬Èç¹û×Ö·û´® expr1 Óë expr2 ÏàµÈ ·µ»Ø NULL£¬·ñÔò·µ»Ø expr1 
SELECT NULLIF(25, 25);
->
SESSION_USER() ·µ»Øµ±Ç°Óû§ 
SELECT SESSION_USER();
-> guest@%
SYSTEM_USER() ·µ»Øµ±Ç°Óû§ 
SELECT SYSTEM_USER();
-> guest@%
USER() ·µ»Øµ±Ç°Óû§ 
SELECT USER();
-> guest@%
VERSION() ·µ»ØÊý¾Ý¿âµÄ°æ±¾ºÅ 
SELECT VERSION()
-> 5.6.34

 

MySQL ÔËËã·û

  • ½éÉÜ MySQL µÄÔËËã·û¼°ÔËËã·ûµÄÓÅÏȼ¶¡£ MySQL Ö÷ÒªÓÐÒÔϼ¸ÖÖÔËËã·û£º
    • ËãÊõÔËËã·û
    • ±È½ÏÔËËã·û
    • Âß¼­ÔËËã·û
    • λÔËËã·û

ËãÊõÔËËã·û

  • MySQL Ö§³ÖµÄËãÊõÔËËã·û°üÀ¨:
# ÔËËã·û   # ×÷ÓÃ
+         # ¼Ó·¨
-         # ¼õ·¨
*         # ³Ë·¨
/ »ò DIV  # ³ý·¨
% »ò MOD  # È¡Óà
  • ÔÚ³ý·¨ÔËËãºÍÄ£ÔËËãÖУ¬Èç¹û³ýÊýΪ0£¬½«ÊÇ·Ç·¨³ýÊý£¬·µ»Ø½á¹ûΪNULL¡£
# 1¡¢¼Ó
mysql> select 1+2;
+-----+
| 1+2 |
+-----+
|   3 |
+-----+

# 2¡¢¼õ
mysql> select 1-2;
+-----+
| 1-2 |
+-----+
|  -1 |
+-----+

# 3¡¢³Ë
mysql> select 2*3;
+-----+
| 2*3 |
+-----+
|   6 |
+-----+

# 4¡¢³ý
mysql> select 2/3;
+--------+
| 2/3    |
+--------+
| 0.6667 |
+--------+

# 5¡¢ÉÌ
mysql> select 10 DIV 4;
+----------+
| 10 DIV 4 |
+----------+
|        2 |
+----------+

# 6¡¢È¡Óà
mysql> select 10 MOD 4;
+----------+
| 10 MOD 4 |
+----------+
|        2 |
+----------+

±È½ÏÔËËã·û

  • SELECT Óï¾äÖеÄÌõ¼þÓï¾ä¾­³£ÒªÊ¹ÓñȽÏÔËËã·û¡£Í¨¹ýÕâЩ±È½ÏÔËËã·û£¬¿ÉÒÔÅжϱíÖеÄÄÄЩ¼Ç¼ÊÇ·ûºÏÌõ¼þµÄ¡£±È½Ï½á¹ûΪÕ棬Ôò·µ»Ø 1£¬Îª¼ÙÔò·µ»Ø 0£¬±È½Ï½á¹û²»È·¶¨Ôò·µ»Ø NULL¡£
·ûºÅÃèÊö±¸×¢
= µÈÓÚ  
<>, != ²»µÈÓÚ  
> ´óÓÚ  
< СÓÚ  
<= СÓÚµÈÓÚ  
>= ´óÓÚµÈÓÚ  
BETWEEN ÔÚÁ½ÖµÖ®¼ä >=min&&<=max
NOT BETWEEN ²»ÔÚÁ½ÖµÖ®¼ä  
IN ÔÚ¼¯ºÏÖÐ  
NOT IN ²»ÔÚ¼¯ºÏÖÐ  
<=> Ñϸñ±È½ÏÁ½¸öNULLÖµÊÇ·ñÏàµÈ Á½¸ö²Ù×÷Âë¾ùΪNULLʱ£¬ÆäËùµÃֵΪ1£»¶øµ±Ò»¸ö²Ù×÷ÂëΪNULLʱ£¬ÆäËùµÃֵΪ0
LIKE Ä£ºýÆ¥Åä  
REGEXP »ò RLIKE ÕýÔòʽƥÅä  
IS NULL Ϊ¿Õ  
IS NOT NULL ²»Îª¿Õ   
# mysql> select 2=3;
+-----+
| 2=3 |
+-----+
|   0 |
+-----+
# mysql> select NULL = NULL;
+-------------+
| NULL = NULL |
+-------------+
|        NULL |
+-------------+
1¡¢µÈÓÚ
# mysql> select 2<>3;
+------+
| 2<>3 |
+------+
|    1 |
+------+
2¡¢²»µÈÓÚ
# Óë = µÄÇø±ðÔÚÓÚµ±Á½¸ö²Ù×÷Âë¾ùΪ NULL ʱ£¬ÆäËùµÃֵΪ 1 ¶ø²»Îª NULL£¬¶øµ±Ò»¸ö²Ù×÷ÂëΪ NULL ʱ£¬ÆäËùµÃֵΪ 0¶ø²»Îª NULL¡£
# mysql> select 2<=>3;
+-------+
| 2<=>3 |
+-------+
|     0 |
+-------+
# mysql> select null=null;
+-----------+
| null=null |
+-----------+
|      NULL |
+-----------+      
# mysql> select null<=>null;
+-------------+
| null<=>null |
+-------------+
|           1 |
+-------------+
3¡¢°²È«µÈÓÚ
# mysql> select 2<3;
+-----+
| 2<3 |
+-----+
|   1   |
+-----+
4¡¢Ð¡ÓÚ
# mysql> select 2<=3;
+------+
| 2<=3 |
+------+
|    1   |
+------+
5¡¢Ð¡ÓÚµÈÓÚ
# mysql> select 2>3;
+-----+
| 2>3 |
+-----+
|   0   |
+-----+

# mysql> select 2>=3;
+------+
| 2>=3 |
+------+
|    0  |
+------+
6¡¢´óÓÚ¡¢´óÓÚµÈÓÚ
# mysql> select 5 between 1 and 10;
+--------------------+
| 5 between 1 and 10 |
+--------------------+
|                  1 |
+--------------------+
7¡¢BETWEEN
# mysql> select 5 in (1,2,3,4,5);
+------------------+
| 5 in (1,2,3,4,5) |
+------------------+
|                1 |
+------------------+

# mysql> select 5 not in (1,2,3,4,5);
+----------------------+
| 5 not in (1,2,3,4,5) |
+----------------------+
|                    0 |
+----------------------+
8¡¢IN¡¢NOT IN
# 
mysql> select null is NULL;
+--------------+
| null is NULL |
+--------------+
|            1 |
+--------------+
mysql> select 'a' is NULL;
+-------------+
| 'a' is NULL |
+-------------+
|           0 |
+-------------+
9¡¢IS NULL
# mysql> select null IS NOT NULL;
+------------------+
| null IS NOT NULL |
+------------------+
|                0 |
+------------------+        

# mysql> select 'a' IS NOT NULL;
+-----------------+
| 'a' IS NOT NULL |
+-----------------+
|               1 |
+-----------------+
10¡¢IS NOT NULL
# mysql> select '12345' like '12%';
+--------------------+
| '12345' like '12%' |
+--------------------+
|                  1 |
+--------------------+

# mysql> select '12345' like '12_';
+--------------------+
| '12345' like '12_' |
+--------------------+
|                  0 |
+--------------------+
11¡¢LIKE
# mysql> select 'beijing' REGEXP 'jing';
+-------------------------+
| 'beijing' REGEXP 'jing' |
+-------------------------+
|                       1 |
+-------------------------+

# mysql> select 'beijing' REGEXP 'xi';
+-----------------------+
| 'beijing' REGEXP 'xi' |
+-----------------------+
|                     0 |
+-----------------------+
12¡¢REGEXP

Âß¼­ÔËËã·û

  • Âß¼­ÔËËã·ûÓÃÀ´Åжϱí´ïʽµÄÕæ¼Ù¡£Èç¹û±í´ïʽÊÇÕ棬½á¹û·µ»Ø 1¡£Èç¹û±í´ïʽÊǼ٣¬½á¹û·µ»Ø 0¡£
#ÔËËã·ûºÅ # ×÷ÓÃ
NOT »ò ! # Âß¼­·Ç
AND      # Âß¼­Óë
OR       # Âß¼­»ò
XOR      # Âß¼­Òì»ò
# mysql> select 2 and 0;
+---------+
| 2 and 0 |
+---------+
|       0 |
+---------+
   
# mysql> select 2 and 1;   
+---------+     
| 2 and 1 |      
+---------+      
|       1 |      
+---------+
1¡¢Óë
# mysql> select 2 or 0;
+--------+
| 2 or 0 |
+--------+
|      1 |
+--------+

# mysql> select 2 or 1;
+--------+
| 2 or 1 |
+--------+
|      1 |
+--------+

# mysql> select 0 or 0;
+--------+
| 0 or 0 |
+--------+
|      0 |
+--------+

# mysql> select 1 || 0;
+--------+
| 1 || 0 |
+--------+
|      1 |
+--------+
2¡¢»ò
# mysql> select not 1;
+-------+
| not 1 |
+-------+
|     0 |
+-------+

# mysql> select !0;
+----+
| !0 |
+----+
|  1 |
+----+
3¡¢·Ç
# mysql> select 1 xor 1;
+---------+
| 1 xor 1 |
+---------+
|       0 |
+---------+

# mysql> select 0 xor 0;
+---------+
| 0 xor 0 |
+---------+
|       0 |
+---------+

# mysql> select 1 xor 0;
+---------+
| 1 xor 0 |
+---------+
|       1 |
+---------+

# mysql> select null or 1;
+-----------+
| null or 1 |
+-----------+
|         1 |
+-----------+

# mysql> select 1 ^ 0;
+-------+
| 1 ^ 0 |
+-------+
|     1 |
+-------+
4¡¢Òì»ò

λÔËËã·û

  • λÔËËã·ûÊÇÔÚ¶þ½øÖÆÊýÉϽøÐмÆËãµÄÔËËã·û¡£Î»ÔËËã»áÏȽ«²Ù×÷Êý±ä³É¶þ½øÖÆÊý£¬½øÐÐλÔËË㡣ȻºóÔÙ½«¼ÆËã½á¹û´Ó¶þ½øÖÆÊý±ä»ØÊ®½øÖÆÊý¡£
# ÔËËã·ûºÅ    # ×÷ÓÃ
&            # °´Î»Óë
|            # °´Î»»ò
^            # °´Î»Òì»ò
!            # È¡·´
<<           # ×óÒÆ
>>           # ÓÒÒÆ
# 1¡¢°´Î»Óë
mysql> select 3&5;
+-----+
| 3&5 |
+-----+
|   1 |
+-----+

# 2¡¢°´Î»»ò
mysql> select 3|5;
+-----+
| 3|5 |
+-----+
|   7 |
+-----+

# 3¡¢°´Î»Òì»ò
mysql> select 3^5;
+-----+
| 3^5 |
+-----+
|   6 |
+-----+

# 4¡¢°´Î»È¡·´
mysql> select ~18446744073709551612;
+-----------------------+
| ~18446744073709551612 |
+-----------------------+
|                     3 |
+-----------------------+

# 5¡¢°´Î»ÓÒÒÆ
mysql> select 3>>1;
+------+
| 3>>1 |
+------+
|    1 |
+------+

# 6¡¢°´Î»×óÒÆ
mysql> select 3<<1;
+------+
| 3<<1 |
+------+
|    6 |
+------+

ÔËËã·ûÓÅÏȼ¶

  • ×îµÍÓÅÏȼ¶Îª£º :=

  • ×î¸ßÓÅÏȼ¶Îª£º !¡¢BINARY¡¢ COLLATE¡£

 

¾ÛºÏº¯Êý

# ×ÜÊý£ºcount
# ²éѯÄÐÐÔÓжàÉÙÈË£¬Å®ÐÔÓжàÉÙÈË
select count(*) from students where gender = 1;
select count(*) from students where gender = 2;
# ×î´óÖµ£ºmax
# ²éѯ×î´óµÄÄêÁä
select max(age) from students;

# ²éѯŮÐÔµÄ×î¸ß Éí¸ß
select max(height) from students where gender = 2;

# ×îСֵ£ºmin
select min(height) from students where gender = 2;
# ÇóºÍ£ºsum
# ¼ÆËãËùÓÐÈ˵ÄÄêÁä×ܺÍ
select sum(age) from students;
# ƽ¾ùÖµ£ºavg
# ¼ÆËãƽ¾ùÄêÁä
select avg(age) from students;

# ¼ÆËãƽ¾ùÄêÁä sum(age)/count(*)

# ËÄÉáÎåÈë round(123.23 , 1) ±£Áô1λСÊý
# ¼ÆËãËùÓÐÈ˵Äƽ¾ùÄêÁ䣬±£Áô3λСÊý
select round(avg(age),3) from students;

# ¼ÆËãÄÐÐÔµÄƽ¾ùÉí¸ß ±£Áô2λСÊý
select round(avg(height),2) from students where gender = 1;
# ²éѯÀàÐÍ cate_name Ϊ '³¬¼¶±¾' µÄÉÌÆ·Ãû³Æ name ¡¢¼Û¸ñ price ( where )
select name,price from goods where cate_name = "³¬¼¶±¾";

# ÏÔʾÉÌÆ·µÄÖÖÀà
# 1 ·Ö×éµÄ·½Ê½( group by ) 
select cate_name from goods group by cate_name;

# 2 È¥Öصķ½·¨( distinct )
select distinct cate_name from goods;

# ÇóËùÓеçÄÔ²úÆ·µÄƽ¾ù¼Û¸ñ avg ,²¢ÇÒ±£ÁôÁ½Î»Ð¡Êý( round )
select round(avg(price),2) from goods;

# ÏÔʾ ÿÖÖÀàÐÍ cate_name (ÓÉ´Ë¿ÉÖªÐèÒª·Ö×é)µÄ ƽ¾ù¼Û¸ñ
select cate_name,avg(price) from goods group by cate_name;

#²éѯ ÿÖÖÀàÐÍ µÄÉÌÆ·ÖÐ ×î¹ó max ¡¢×î±ãÒË min ¡¢Æ½¾ù¼Û avg ¡¢ÊýÁ¿ count
select max(price),min(price),avg(price),count(*) from goods group by cate_name;

# ²éѯËùÓм۸ñ´óÓÚ Æ½¾ù¼Û¸ñ µÄÉÌÆ·£¬²¢ÇÒ°´ ¼Û¸ñ½µÐò ÅÅÐò order desc
# 1 ²éѯƽ¾Ö¼Û¸ñ(avg_price)
select avg(price) as avg_price from goods;

# 2 ʹÓÃ×Ó²éѯ
select * from goods where price > (select avg(price) as avg_price from goods) order by price desc;

# ²éѯÿÖÖÀàÐÍÖÐ×î¹óµÄµçÄÔÐÅÏ¢(ÄÑ)
# 1 ²éÕÒ Ã¿ÖÖÀàÐÍ ÖÐ ×î¹óµÄ max_price ¼Û¸ñ
select cate_name,max(price) as max_price from goods group by cate_name;

# 2 ¹ØÁª²éѯ inner join ÿÖÖÀàÐÍ ÖÐ×î¹óµÄÎïÆ·ÐÅÏ¢
select * from goods inner join(select cate_name,max(price) as max_price from goods group by cate_name) as max_price_goods
on goods.cate_name=max_price_goods.cate_name and goods.price=max_price_goods.max_price;

# ´´½¨"ÉÌÆ··ÖÀà"±í
# µÚÒ»²½    ´´½¨±í (ÉÌÆ·ÖÖÀà±í goods_cates )
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
# µÚ¶þ²½    ͬ²½ ÉÌÆ··ÖÀà±í Êý¾Ý ½«ÉÌÆ·µÄËùÓÐ (ÖÖÀàÐÅÏ¢) дÈëµ½ (ÉÌÆ·ÖÖÀà±í) ÖÐ °´ÕÕ ·Ö×é µÄ·½Ê½²éѯ goods ±íÖеÄËùÓÐ ÖÖÀà(cate_name)
select cate_name from goods group by cate_name;

# (×¢Òâ) °Ñ²éѯ³öÀ´µÄ ½á¹û дÈë goods_cates ±íÀïÈ¥ ( insert into ) Ö»²åÈëname
insert into goods_cates(name) (select cate_name from goods group by cate_name);

# µÚÈý²¿ ͬ²½ ÉÌÆ·±í Êý¾Ý ͨ¹ý goods_cates Êý¾Ý±íÀ´¸üРgoods ±í
# ÒòΪҪͨ¹ý goods_cates±í ¸üРgoods ±í 365betÌåÓýÔÚÏßÒª°ÑÁ½¸ö±íÁ¬½ÓÆðÀ´
select * from goods inner join goods_cates on goods.cate_name = goods_cates.name;

# °Ñ ÉÌÆ·±í goods ÖÐµÄ cate_name È«²¿Ìæ»»³É ÉÌÆ··ÖÀà±íÖÐµÄ ÉÌÆ·id ( update ... set )
update (goods inner join goods_cates on goods.cate_name = goods_cates.name) set goods.cate_name = goods_cates.id;

# µÚËIJ¿ Ð޸ıí½á¹¹
# ²é¿´±í½á¹¹(×¢Òâ Á½¸ö±íÖÐµÄ Íâ¼üÀàÐÍÐèÒªÒ»ÖÂ)
desc goods;

#Ð޸ıí½á¹¹ alter table ×Ö¶ÎÃû×Ö²»Í¬ change,°Ñ cate_name ¸Ä³É cate_id int unsigned not null
alter table goods change cate_name cate_id int unsigned not null;

# ´´½¨ ÉÌÆ·Æ·ÅƱí goods_brands
# µÚÒ»²½ ´´½¨ "ÉÌÆ·Æ·ÅƱí" ±í
# µÚ365ÌåÓýͶע·½Ê½ ÏÈ´´½¨±í
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null);
# ²åÈëÊý¾Ý brand_name(·Ö×é)
# °´ÕÕ ·Ö×é µÄ·½Ê½²éѯ goods ±íÖеÄËùÓÐ ÖÖÀà(brand_name)
select brand_name from goods group by brand_name;

# (×¢Òâ) °Ñ²éѯ³öÀ´µÄ ½á¹û дÈë goods_brands ±íÀïÈ¥ ( insert into ) Ö»²åÈëname
insert into goods_brands(name) (select brand_name from goods group by brand_name);

# µÚ¶þÖÖ·½Ê½ ´´½¨±íµÄͬʱ²åÈëÊý¾Ý(Á˽â,²»½¨ÒéʹÓÃ)
create table goods_brands (
id int unsigned primary key auto_increment,
name varchar(40) not null) select brand_name as name from goods group by brand_name;
# µÚ¶þ²½ ͬ²½Êý¾Ý
# ͨ¹ýgoods_brandsÊý¾Ý±íÀ´¸üÐÂgoodsÊý¾Ý±í g.brand_name=b.id
update (goods inner join goods_brands on goods.brand_name = goods_brands.name) set goods.brand_name = goods_brands.id;

# µÚÈý²½ Ð޸ıí½á¹¹
# ͨ¹ýalter tableÓï¾äÐ޸ıí½á¹¹ brand_id int unsigned not null
alter table goods change brand_name brand_id int unsigned not null;

# Íâ¼üµÄʹÓÃ(Á˽â)
# ´´½¨Íâ¼ü
# -- Ïògoods±íÀï²åÈëÈ365betÌåÓýÔÚÏßâÒ»ÌõÊý¾Ý
insert into goods (name,cate_id,brand_id,price) values('ÀÏÍõÅÆÍÏÀ­»ú', 10, 10,'6666');

# Ô¼Êø Êý¾ÝµÄ²åÈë ʹÓà Íâ¼ü foreign key
alter table goods add foreign key (brand_id) references goods_brands(id);
alter table goods add foreign key (cate_id) references goods_cates(id);
alter table goods add foreign key(brand_id) references goods_brands(id);

# ʧ°ÜÔ­Òò ÀÏÍõÅÆÍÏÀ­»ú delete
delete from goods where name="ÀÏÍõÅÆÍÏÀ­»ú";
# ´´½¨±íµÄͬʱÉèÖÃÍâ¼ü (×¢Òâ goods_cates ºÍ goods_brands Á½¸ö±í±ØÐëÊÂÏÈ´æÔÚ)
create table goods(
id int primary key auto_increment not null,
name varchar(40) default '',
price decimal(5,2),
cate_id int unsigned,
brand_id int unsigned,
is_show bit default 1,
is_saleoff bit default 0,
foreign key(cate_id) references goods_cates(id),
foreign key(brand_id) references goods_brands(id)
);

# ÈçºÎÈ¡ÏûÍâ¼üÔ¼Êø
#ÐèÒªÏÈ»ñÈ¡Íâ¼üÔ¼ÊøÃû³Æ,¸ÃÃû³Æϵͳ»á×Ô¶¯Éú³É,¿ÉÒÔͨ¹ý²é¿´±í´´½¨ÓïÀ´»ñÈ¡Ãû³Æ
show create table goods;

# »ñÈ¡Ãû³ÆÖ®ºó¾Í¿ÉÒÔ¸ù¾ÝÃû³ÆÀ´É¾³ýÍâ¼üÔ¼Êø
alter table goods drop foreign key goods_ibfk_1;
alter table goods drop foreign key goods_ibfk_2;

# ×ܽá:ÔÚʵ¼Ê¿ª·¢ÖÐ,ºÜÉÙ»áʹÓõ½Íâ¼üÔ¼Êø,»á¼«´óµÄ½µµÍ±í¸üеÄЧÂÊ
sqlÇ¿»¯ÑÝÁ·( goods ±íÁ·Ï°)

Ч¹ûͼMySQL µÄ½éÉܵ½Õâƪ»ù±¾¾ÍËãÍêÊÂÁË£¬ºóÆÚ365betÌåÓýÔÚÏß»á¼ÌÐø¸üУ¬¹Ø×¢²»ÃÔ·Ŷ£¬¶à¶àÖ§³Ö£¡£¡£¡

posted @ 2020-03-25 20:42  Talent¡¢Ã¯?ï  ÔĶÁ(83)  ÆÀÂÛ(0)  ±à¼­  ÊÕ²Ø