mysql记录1

 

#连接与断开服务器 
C:/Documents and Settings/Administrator>mysql -u root -p 
Enter password: **** 
 
mysql> quit 
Bye 
 
#--------------------------- 
mysql -u root -p 
Enter password: **** 
 
#---------------------------------------------- 
#输入查询 
#mysql的版本号和当前日期 
SELECT VERSION(), CURRENT_DATE; 
 
#将mysql用作一个简单的计算器 
SELECT SIN(PI()/4), (4+1)*5; 
 
#一行上输入多条语句 
SELECT VERSION(); SELECT NOW(); 
 
#多行语句的例子 
SELECT 
USER() 
, 
CURRENT_DATE; 
 
#输入/c取消已输入的不想执行的命令 
SELECT 
USER() 
/c 
 
#------------------------------------------- 
#创建并使用数据库 
#显示数据库 
SHOW DATABASES; 
 
#使用数据库 
USE test 
 
#创建并选择数据库 
CREATE DATABASE menagerie; 
USE menagerie; 
 
#设置每次启动mysql会话使用menagerie数据库 
mysql -u root -p menagerie 
 
#创建表 
SHOW TABLES; 
CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20), 
species VARCHAR(20), sex CHAR(1), birth DATE, death DATE); 
SHOW TABLES; 
DESCRIBE pet; 
 
 
#将数据装入表中 
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet; 
LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet 
LINES TERMINATED BY '/r/n'; 
 
#pet.txt 
Fluffy    Harold    cat    f    1993-02-04 
Claws    Gwen    cat    m    1994-03-17 
Buffy    Harold    dog    f    1989-05-13 
Fang    Benny    dog    m    1990-08-27 
Bowser    Diane    dog    m    1979-08-31    1995-07-29 
Chirpy    Gwen    bird    f    1998-09-11 
Whistler    Gwen    bird    f    1997-12-09 
Slim    Benny    snake    m    1996-04-29 
 
INSERT INTO pet VALUES ('Puffball','Diane','hamster','f','1999-03-30',NULL); 
INSERT INTO pet VALUES ('Bowser','Diane','dog','m','1979-08-31','1995-07-29'); 
INSERT INTO pet VALUES ('Chirpy','Gwen','bird','f','1998-09-11',NULL); 
 
 
#从表检索信息 
select * from pet; 
UPDATE pet SET birth = '1989-08-30' WHERE name = 'Bowser'; 
 
#选择特殊行 
SELECT * FROM pet WHERE name = 'Bowser'; 
SELECT * FROM pet WHERE birth > '1998-1-1'; 
SELECT * FROM pet WHERE species = 'dog' AND sex = 'f'; 
 
#选择特殊列 
SELECT name, birth FROM pet; 
SELECT owner FROM pet; 
SELECT DISTINCT owner FROM pet; 
 
SELECT name, species, birth FROM pet 
WHERE species = 'dog' OR species = 'cat'; 
 
#分类行 
SELECT name, birth FROM pet ORDER BY birth; 
SELECT name, birth FROM pet ORDER BY birth DESC; 
SELECT name, species, birth FROM pet 
ORDER BY species, birth DESC; 
 
#日期计算 
#确定每个宠物有多大 
SELECT name, birth, CURDATE(), 
(YEAR(CURDATE())-YEAR(birth)) 
- (RIGHT(CURDATE(),5) 5)) 
AS age 
FROM pet; 
#YEAR()提取日期的年部分，RIGHT()提取日期的MM-DD (日历年)部分的最右面 5个字符。 
 
SELECT name, birth, CURDATE(), 
(YEAR(CURDATE())-YEAR(birth)) 
- (RIGHT(CURDATE(), 5) 5)) 
AS age 
FROM pet  ORDER  BY name; 
 
SELECT name, birth, CURDATE(), 
(YEAR(CURDATE())-YEAR(birth)) 
- (RIGHT(CURDATE(), 5) 5)) 
AS age 
FROM pet  ORDER  BY age; 
 
 
#查出下个月过生日的动物 
SELECT name, birth, MONTH(birth)  FROM pet; 
SELECT name, birth  FROM pet  WHERE MONTH(birth) =  5; 
 
SELECT name, birth  FROM pet 
WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL  1 MONTH)); 
 
SELECT name, birth  FROM pet 
WHERE MONTH(birth) =  MOD(MONTH(CURDATE()),  12) +  1; 
 
 
# NULL值操作 
#不能使用算术比较 操作符例如=、 <或!= >
SELECT  1 =  NULL,  1 <>  NULL,  1 <  NULL,  1 >  NULL; 
 
#在MySQL中， 0或  NULL意味着假而其它值意味着真。布尔运算的默认真值是 1 
SELECT  1  IS  NULL,  1  IS  NOT  NULL; 
SELECT  0  IS  NULL,  0  IS  NOT  NULL,  ''  IS  NULL,  ''  IS  NOT  NULL; 
 
#模式匹配 
#找出以"b"开头的名字 
SELECT *  FROM pet  WHERE name  LIKE  'b%'; 
 
#找出以"fy"结尾的名字 
SELECT *  FROM pet  WHERE name  LIKE  '%fy'; 
 
#找出包含"w"的名字 
SELECT *  FROM pet  WHERE name  LIKE  '%w%'; 
 
#找出正好包含 5个字符的名字，使用"_"模式字符 
SELECT *  FROM pet  WHERE name  LIKE  '_____'; 
 
# ------------------------------------------------------------ 
扩展正则表达式的一些字符是：  
 
·'.'匹配任何单个的字符。 
 
·字符类"[...]"匹配在方括号内的任何字符。 
    例如，"[abc]"匹配"a"、"b"或"c"。为了命名字符的范围，使用一个"-"。 
    "[a-z]"匹配任何字母，而"[ 0- 9]"匹配任何数字。 
 
·" * "匹配零个或多个在它前面的字符。 
    例如，"x*"匹配任何数量的"x"字符，"[ 0- 9]*"匹配任何数量的数字， 
    而".*"匹配任何数量的任何字符。 
 
如果REGEXP模式与被测试值的任何地方匹配，模式就匹配(这不同于 LIKE模式匹配， 
只有与整个值匹配，模式才匹配)。 为了定位一个模式以便它必须匹配被测试值的 
开始或结尾，在模式开始处使用"^"或在模式的结尾用"$"。  
# ------------------------------------------------------------- 
 
#查询只匹配名称首字母的小写'b' 
SELECT *  FROM pet  WHERE name REGEXP  BINARY  '^b'; 
 
#找出以"fy"结尾的名字，使用"$"匹配名字的结尾 
SELECT *  FROM pet  WHERE name REGEXP  'fy$'; 
 
#找出包含一个"w"的名字 
SELECT *  FROM pet  WHERE name REGEXP  'w'; 
 
#找出包含正好 5个字符的名字，使用"^"、"$"和 5个"."匹配名字的开始、结尾和 5个字符 
SELECT *  FROM pet  WHERE name REGEXP  '^.....$'; 
 
#也可以使用"{n}""重复n次"操作符 
SELECT *  FROM pet  WHERE name REGEXP  '^.{5}$'; 
 
 
#计数行 
#计算你拥有动物的总数目或"在pet表中有多少行?" 
SELECT  COUNT(*)  FROM pet; 
 
#检索每个主人有多少宠物 
SELECT owner,  COUNT(*)  FROM pet  GROUP  BY owner; 
#注意：使用 GROUP  BY对每个owner的所有记录分组，没有它，你会得到错误消息 
 
#检索每种动物的数量 
SELECT species,  COUNT(*)  FROM pet  GROUP  BY species; 
 
#检索每种性别的动物数量 
SELECT sex,  COUNT(*)  FROM pet  GROUP  BY sex 
 
#按种类和性别组合的动物数量 
SELECT species, sex,  COUNT(*)  FROM pet  GROUP  BY species, sex; 
 
#若使用 COUNT( )，你不必检索整个表。例如, 前面的查询，当只对狗和猫进行时，应为 
SELECT species, sex,  COUNT(*)  FROM pet 
WHERE species =  'dog'  OR species =  'cat' 
GROUP  BY species, sex; 
 
#仅需要知道已知性别的按性别的动物数目 
SELECT species, sex,  COUNT(*)  FROM pet 
WHERE sex  IS  NOT  NULL 
GROUP  BY species, sex; 
 
#使用 1个以上的表 
#创建event表 
CREATE  TABLE event (name  VARCHAR( 20),  date  DATE, 
type  VARCHAR( 15), remark  VARCHAR( 255)); 
 
#装载记录 
LOAD DATA LOCAL INFILE  'event.txt'  INTO  TABLE event; 
# ----event------------------------------------------- 
Fluffy     1995- 05- 15    litter     4 kittens,  3 female,  1 male 
Buffy     1993- 06- 23    litter     5 puppies,  2 female,  3 male 
Buffy     1994- 06- 19    litter     3 puppies,  3 female 
Chirpy     1999- 03- 21    vet    needed beak straightened 
Slim     1997- 08- 03    vet    broken rib 
Bowser     1991- 10- 12    kennel 
Fang     1991- 10- 12    kennel 
Fang     1998- 08- 28    birthday    Gave him a new chew toy 
Claws     1998- 03- 17    birthday    Gave him a new flea collar 
Whistler     1998- 12- 09    birthday    First birthday 
# ---------------------------------------------------- 
#有了一窝小动物时，找出每只宠物的年龄 
SELECT pet.name, 
(YEAR( date)-YEAR(birth)) - (RIGHT( date, 5) 5))  AS age, 
remark 
FROM pet, event 
WHERE pet.name = event.name  AND event. type =  'litter'; 
 
SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species 
FROM pet  AS p1, pet  AS p2 
WHERE p1.species = p2.species  AND p1.sex =  'f'  AND p2.sex =  'm'; 
 
 
#获得数据库和表的信息 
#找出当前选择了哪个数据库 
SELECT  DATABASE(); 
 
#找出当前的数据库包含什么表 
SHOW TABLES; 
 
#表的结构 
DESCRIBE pet; 
 
#在批处理模式下使用mysql 
#shell> mysql < batch-file 
#C:/> mysql -e "source batch-file" 
#shell> mysql -h host -u user -p < batch-file 
#Enter password: ******** 
 
#通过一个分页器在屏幕输出多个查询 
shell> mysql < batch-file | more 
 
#捕捉文件中的输出 
shell> mysql < batch-file > mysql.out 
 
#使用源代码或 /.命令从mysql提示符运行脚本 
mysql> source filename; 
mysql> /. filename; 
 
 
#=========================== 
#一些常见问题的例子 
# ---------------------------------------------- 
#使用数据库表"shop"来储存某个商人（经销商）的每件物品(物品号)的价格。 
#假定每个商人对每项物品有一个固定价格，那么(物品，商人)即为该记录的主关键字。 
CREATE  TABLE shop ( 
article  INT( 4) UNSIGNED ZEROFILL  DEFAULT  '0000'  NOT  NULL, 
dealer   CHAR( 20)                  DEFAULT  ''      NOT  NULL, 
price   DOUBLE( 16, 2)              DEFAULT  '0.00'  NOT  NULL, 
PRIMARY KEY(article, dealer)); 
 
INSERT  INTO shop VALUES 
( 1, 'A', 3. 45),( 1, 'B', 3. 99),( 2, 'A', 10. 99),( 3, 'B', 1. 45), 
( 3, 'C', 1. 69),( 3, 'D', 1. 25),( 4, 'D', 19. 95); 
 
#列的最大值 
SELECT  MAX(article)  AS article  FROM shop; 
 
#拥有某个列的最大值的行 
#找出最贵物品的编号、销售商和价格 
SELECT article, dealer, price 
FROM   shop 
WHERE  price=( SELECT  MAX(price)  FROM shop); 
 
SELECT article, dealer, price 
FROM shop 
ORDER  BY price DESC 
LIMIT  1; 
 
#列的最大值：按组 
SELECT article,  MAX(price)  AS price 
FROM   shop 
GROUP  BY article; 
 
#拥有某个字段的组间最大值的行 
SELECT article, dealer, price 
FROM   shop s1 
WHERE  price=( SELECT  MAX(s2.price) 
               FROM shop s2 
               WHERE s1.article = s2.article); 
 
#使用用户变量 
#找出价格最高或最低的物品 
SELECT @min_price:= MIN(price),@max_price:= MAX(price)  FROM shop; 
SELECT *  FROM shop  WHERE price=@min_price  OR price=@max_price; 
 
#使用外键 
CREATE  TABLE person ( 
    id  SMALLINT UNSIGNED  NOT  NULL AUTO_INCREMENT, 
    name  CHAR( 60)  NOT  NULL, 
    PRIMARY KEY (id) 
); 
  
CREATE  TABLE shirt ( 
    id  SMALLINT UNSIGNED  NOT  NULL AUTO_INCREMENT, 
    style ENUM( 't-shirt',  'polo',  'dress')  NOT  NULL, 
    color ENUM( 'red',  'blue',  'orange',  'white',  'black')  NOT  NULL, 
    owner  SMALLINT UNSIGNED  NOT  NULL REFERENCES person(id), 
    PRIMARY KEY (id) 
); 
  
INSERT  INTO person VALUES ( NULL,  'Antonio Paz'); 
  
SELECT @last := LAST_INSERT_ID(); 
  
INSERT  INTO shirt VALUES 
( NULL,  'polo',  'blue', @last), 
( NULL,  'dress',  'white', @last), 
( NULL,  't-shirt',  'blue', @last); 
  
INSERT  INTO person VALUES ( NULL,  'Lilliana Angelovska'); 
  
SELECT @last := LAST_INSERT_ID(); 
  
INSERT  INTO shirt VALUES 
( NULL,  'dress',  'orange', @last), 
( NULL,  'polo',  'red', @last), 
( NULL,  'dress',  'blue', @last), 
( NULL,  't-shirt',  'white', @last); 
  
SELECT *  FROM person; 
SELECT *  FROM shirt; 
 
SELECT s.*  FROM person p, shirt s 
  WHERE p.name  LIKE  'Lilliana%' 
    AND s.owner = p.id 
    AND s.color <>  'white'; 
 
SHOW  CREATE  TABLE shirt/G; 
 
#根据两个键搜索 
SELECT field1_index, field2_index  FROM test_table 
WHERE field1_index =  '1'  OR  field2_index =  '1'; 
 
SELECT field1_index, field2_index 
     FROM test_table  WHERE field1_index =  '1' 
UNION 
SELECT field1_index, field2_index 
     FROM test_table  WHERE field2_index =  '1'; 
 
#根据天计算访问量 
CREATE  TABLE t1 (year YEAR( 4), month  INT( 2) UNSIGNED ZEROFILL, 
             day  INT( 2) UNSIGNED ZEROFILL); 
INSERT  INTO t1 VALUES( 2000, 1, 1),( 2000, 1, 20),( 2000, 1, 30),( 2000, 2, 2), 
            ( 2000, 2, 23),( 2000, 2, 23); 
 
SELECT year,month,BIT_COUNT(BIT_OR( 1< AS days  FROM t1 
        GROUP  BY year,month; 
 
#使用AUTO_INCREMENT 
CREATE  TABLE animals ( 
     id MEDIUMINT  NOT  NULL AUTO_INCREMENT, 
     name  CHAR( 30)  NOT  NULL, 
     PRIMARY KEY (id) 
 ); 
  
INSERT  INTO animals (name) VALUES  
    ( 'dog'),( 'cat'),( 'penguin'), 
    ( 'lax'),( 'whale'),( 'ostrich'); 
  
SELECT *  FROM animals; 
 
 
CREATE  TABLE animals ( 
    grp ENUM( 'fish', 'mammal', 'bird')  NOT  NULL, 
    id MEDIUMINT  NOT  NULL AUTO_INCREMENT, 
    name  CHAR( 30)  NOT  NULL, 
    PRIMARY KEY (grp,id) 
); 
  
INSERT  INTO animals (grp,name) VALUES  
    ( 'mammal', 'dog'),( 'mammal', 'cat'), 
    ( 'bird', 'penguin'),( 'fish', 'lax'),( 'mammal', 'whale'), 
    ( 'bird', 'ostrich'); 
  
SELECT *  FROM animals  ORDER  BY grp,id; 
 
#以AUTO_INCREMENT值开始而不是 1 
ALTER  TABLE tbl AUTO_INCREMENT =  100; 
 
#查找所有未分发的孪生项 
SELECT 
    CONCAT(p1.id, p1.tvab) +  0  AS tvid, 
    CONCAT(p1.christian_name,  ' ', p1.surname)  AS Name, 
    p1.postal_code  AS Code, 
    p1.city  AS City, 
    pg.abrev  AS Area, 
     IF(td.participation =  'Aborted',  'A',  ' ')  AS A, 
    p1.dead  AS dead1, 
    l.event  AS event1, 
    td.suspect  AS tsuspect1, 
    id.suspect  AS isuspect1, 
    td.severe  AS tsevere1, 
    id.severe  AS isevere1, 
    p2.dead  AS dead2, 
    l2.event  AS event2, 
    h2.nurse  AS nurse2, 
    h2.doctor  AS doctor2, 
    td2.suspect  AS tsuspect2, 
    id2.suspect  AS isuspect2, 
    td2.severe  AS tsevere2, 
    id2.severe  AS isevere2, 
    l.finish_date 
FROM 
    twin_project  AS tp 
     /* For Twin 1 */ 
     LEFT JOIN twin_data  AS td  ON tp.id = td.id 
               AND tp.tvab = td.tvab 
     LEFT JOIN informant_data  AS id  ON tp.id = id.id 
               AND tp.tvab = id.tvab 
     LEFT JOIN harmony  AS h  ON tp.id = h.id 
               AND tp.tvab = h.tvab 
     LEFT JOIN lentus  AS l  ON tp.id = l.id 
               AND tp.tvab = l.tvab 
     /* For Twin 2 */ 
     LEFT JOIN twin_data  AS td2  ON p2.id = td2.id 
               AND p2.tvab = td2.tvab 
     LEFT JOIN informant_data  AS id2  ON p2.id = id2.id 
               AND p2.tvab = id2.tvab 
     LEFT JOIN harmony  AS h2  ON p2.id = h2.id 
               AND p2.tvab = h2.tvab 
     LEFT JOIN lentus  AS l2  ON p2.id = l2.id 
               AND p2.tvab = l2.tvab, 
    person_data  AS p1, 
    person_data  AS p2, 
    postal_groups  AS pg 
WHERE 
     /* p1 gets main twin and p2 gets his/her twin. */ 
     /* ptvab is a field inverted from tvab */ 
    p1.id = tp.id  AND p1.tvab = tp.tvab  AND 
    p2.id = p1.id  AND p2.ptvab = p1.tvab  AND 
     /* Just the screening survey */ 
    tp.survey_no =  5  AND 
     /* Skip if partner died before 65 but allow emigration (dead=9) */ 
    (p2.dead =  0  OR p2.dead =  9  OR 
     (p2.dead =  1  AND 
      (p2.death_date =  0  OR 
       (((TO_DAYS(p2.death_date) - TO_DAYS(p2.birthday)) /  365) 
        >=  65)))) 
     AND 
    ( 
     /* Twin is suspect */ 
    (td.future_contact =  'Yes'  AND td.suspect =  2)  OR 
     /* Twin is suspect - Informant is Blessed */ 
    (td.future_contact =  'Yes'  AND td.suspect =  1 
                                AND id.suspect =  1)  OR 
     /* No twin - Informant is Blessed */ 
    (ISNULL(td.suspect)  AND id.suspect =  1 
                         AND id.future_contact =  'Yes')  OR 
     /* Twin broken off - Informant is Blessed */ 
    (td.participation =  'Aborted' 
      AND id.suspect =  1  AND id.future_contact =  'Yes')  OR 
     /* Twin broken off - No inform - Have partner */ 
    (td.participation =  'Aborted'  AND ISNULL(id.suspect) 
                                   AND p2.dead =  0)) 
     AND 
    l.event =  'Finished' 
     /* Get at area code */ 
     AND SUBSTRING(p1.postal_code,  1,  2) = pg.code 
     /* Not already distributed */ 
     AND (h.nurse  IS  NULL  OR h.nurse= 00  OR h.doctor= 00) 
     /* Has not refused or been aborted */ 
     AND  NOT (h.status =  'Refused'  OR h.status =  'Aborted' 
     OR h.status =  'Died'  OR h.status =  'Other') 
ORDER  BY 
    tvid; 
 
 
#显示孪生对状态的表 
SELECT 
        t1.event, 
        t2.event, 
         COUNT(*) 
FROM 
        lentus  AS t1, 
        lentus  AS t2, 
        twin_project  AS tp 
WHERE 
         /* We are looking at one pair at a time */ 
        t1.id = tp.id 
         AND t1.tvab=tp.tvab 
         AND t1.id = t2.id 
         /* Just the screening survey */ 
         AND tp.survey_no =  5 
         /* This makes each pair only appear once */ 
         AND t1.tvab= '1'  AND t2.tvab= '2' 
GROUP  BY 
        t1.event, t2.event;