33_03MySQL系列之七－单表查询、多表查询和子查询

2015-07-06

SELECT语句:

1
2
3

SELECT * FROM tb_name; 
SELECT field1,field2 FROM tb_name; 投影 
SELECT * FROM WHERE  tb_name WHERE qualification;选择

WHERE字句:要查询的关系
   = > < >= <=
逻辑关系:
   AND
   OR
   NOT
   BETWEEN … AND …
   LIKE
       %:任意长度任意字符
       _:任意单个字符
   REGEXP,RLIKE 支持正则表达式
   IN(18,20,22) 离散取值
   IS NULL
   IS NOT NULL
正则表达式:
   .:表示正则表达式语言中的特殊字符
   |:OR
   []:匹配多个字符中的一个

拼接字段:
   concat(拼接):将值连接起来(通过追加的方式)，构成一个单一的更长的值；
   rtrim:去掉值右边的所有的空格

执行全文本搜索
例如:

CREATE TABLE productnotes 
( 
    note_id int not null auto_increment, 
    prod_id char(10) not null, 
    note_data datetime not null, 
    note_text text not null, 
    primary key(note_id), 
    fulltext(note_text) 
    )engine=Maria;

需要注意的是，应该在导入完数据后，进行修改note_text支持全文索引，因为导入数据时还要进行建立索引，会消耗大量时间。

1	alter table productnotes add fulltext(note_text);

建立索引之后，使用两个函数进行全文本搜索，match()指定需要搜索的列，against()指定需要使用的搜索的搜索表达式 

select note_text from productnotes where match(note_text) against('rabbit'); 
查询扩展: 
select note_text from productnotes where match(note_text) against('anvils'); 
select note_text from productnotes where match(note_text) against('anvils' WITH QUERY EXPANSION); 
 
ROLLUP:可将选择出来的数据进行汇总 
    SELECT vend_id,COUNT(*) AS num_prods FROM products GROUP BY vend_id WITH ROLLUP; 
     
SELECT concat(vend_name,' (',vend_country,')') FROM vendors ORDER BY vend_name; 
SELECT concat(rtrim(vend_name),' (',rtrim(vend_country),')') FROM vendors ORDER BY vend_name; 
 
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton' ORDER BY prod_name; 
 
SELECT Name FROM students WHERE Name RLIKE '^[MNY].*$'; 
SELECT Name FROM students WHERE Name LIKE 'y%'; 
SELECT Name FROM students WHERE Name LIKE 'y____'; 
SELECT Name,Age FROM students WHERE Age>=20 AND Age <=25; 
SELECT Name,Age FROM students WHERE Age BETWEEN 20 AND 25; 
SELECT Name,Age FROM students WHERE Age IN(18,20,22);

查询后的语句进行排序:
ORDER BY field_name [DESC|ASC]
DESC:降序
ASC:生序（默认）

1	SELECT Name,Age FROM students WHERE Age>=20 AND Age <=25 ORDER BY Name DESC;

字段别名: AS

1	SELECT Name AS Student_name FROM students;

LIMIT [offset,]count

SELECT * FROM tb_name WHERE Age LIMIT 2;
SELECT * FROM tb_name WHERE Age LIMIT 2,3;

聚合运算:
   AVG()
   SUM()
   MIN()
   MAX()
   COUNT()

计算男同学的平均年龄:     
SELECT AVG(Age) FROM students WHERE Gender='M'; 
计算女同学的平均年龄: 
SELECT AVG(Age) FROM students WHERE Gender='F';

计算男女同学的平均年龄:

GROUP BY:分组 
    HAVING qualification 
        用于将group by选取的规则再次过滤，只能和group by合作使用； 
     
SELECT AVG(Age) FROM students GROUP BY Gender； 
SELECT COUNT(CID1) AS Persion,CID1  FROM students GROUP BY Gender HAVING Persion >=2;

例如:
SELECT sex FROM users GROUP BY 1 HAVING age > 35;

多表查询:

   连接:
       交叉连接:也叫笛卡尔乘积
       自然连接:仅保留具有等值关系的

   SELECT prod_name FROM vendors INNER JOIN products ON vendors.vend_id= products.vend_id WHERE vend_name=’ACME’;
       外连接:
           左外连接:…LEFT JOIN…ON…
           右外连接:…RIGHT JOIN…ON…
       自连接:

1 2	SELECT student.Name,course.Cname FROM students AS s LEFT JOIN courses AS c ON s.CID1=c.CID; SELECT student.Name,course.Cname FROM students AS s RIGHT JOIN courses AS c ON s.CID1=c.CID;

子查询:
比较操作中使用子查询:子查询只能返回单个值；

    SELECT Name FROM students WHERE Age > (SELECT AVG(age)FROM students); 

    IN():使用子查询； 

    SELECT Name FROM students WHERE Age IN (SELECT Age FROM tutors); 

    在FROM中使用子查询； 
    SELECT Name,Age FROM (SELECT Name,Age FROM students)AS t WHERE t.Age>=20; 
     
    UNION:联合查询 

    (SELECT Name,Age FROM students)UNION(SELECT Tname,Age FROM tutors);