MYSQL复习笔记5-select-from-where子句

Date: 20140125
Auth: Jin
参考：http://dev.mysql.com/doc/refman/5.1/zh/sql-syntax.html#select
一、select子句
主要定义需要选取的字段，包括选择selection，投影projection，连接join
（一）选择
1、所有字段
mysql> select * from users;
2、指定字段
mysql> select uname,department,email from users where id>2;
+-------+------------+------------------+
| uname | department | email |
+-------+------------+------------------+
| lily | tech | lily@foxmail.com |
| sum | tech | sum@qq.com |
| jim | market | jim@qq.com |
+-------+------------+------------------+
3、定义字段（列）别名
mysql> select uname as '名字',department as '部门' ,email as '邮箱' from users where id>2;
+--------+--------+------------------+
| 名字 | 部门 | 邮箱 |
+--------+--------+------------------+
| lily | tech | lily@foxmail.com |
| sum | tech | sum@qq.com |
| jim | market | jim@qq.com |
+--------+--------+------------------
4、替换查询结果中的数据
select name,
case
when birthday<'1981' then 'old'
when birthday>'1988' then 'yong'
else 'ok' END YORN
from lee;
SQL Server做法
select id,uname,score=CASE WHEN score < 60 THEN '差' WHEN score >60 and score < 70 then '及格' when score > 70 then '优秀' END
from users;
MYSQL语法
mysql> select id,uname,CASE WHEN score < 60 THEN '差' WHEN score >60 and score < 70 then '及格' when score > 70 and score < 80
then '良好' else '优秀' END as '评级'from users;
+----+--------+--------+
| id | uname | 评级 |
+----+--------+--------+
| 1 | diege | 及格 |
| 2 | hellen | 良好 |
| 3 | lily | 差 |
| 4 | sum | 优秀 |
| 5 | jim | 优秀 |
+----+--------+--------+
5 rows in set (0.01 sec)

5、消除结果集中的重复行 distinct
mysql> select distinct department,uname from users;

6、限制结果集中返回的行数
SQL SERVER top和percent 关键字
MYSQL limit 关键字
mysql> select uname,department from users limit 2;
+--------+------------+
| uname | department |
+--------+------------+
| diege | tech |
| hellen | product |
+--------+------------+

（二）计算，聚合函数
1、计算 + - * %
算术运算
mysql> select 10+2 as '+',10-2 as '-',10*2 as '*',10%2 as '%';
+----+---+----+------+
| + | - | * | % |
+----+---+----+------+
| 12 | 8 | 20 | 0 |
+----+---+----+------+
1 row in set (0.00 sec)
将100分转换为150分值
mysql> select id,uname,score*1.5 as '150分值' from users;
+----+--------+-----------+
| id | uname | 150分值 |
+----+--------+-----------+
| 1 | diege | 99.0 |
| 2 | hellen | 114.0 |
| 3 | lily | 82.5 |
| 4 | sum | 121.5 |
| 5 | jim | 147.0 |
+----+--------+-----------+

2、count 统计总数
mysql> select count(*) from Price;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)

3、聚合函数 min max sum avg
max
mysql> select max(price) from Price;
+------------+
| max(price) |
+------------+
| 498 |
+------------+
1 row in set (0.00 sec)
min
mysql> select min(price) from Price;
+------------+
| min(price) |
+------------+
| 188 |
+------------+
1 row in set (0.00 sec)
sum
mysql> select sum(price) from Price;
+------------+
| sum(price) |
+------------+
| 2728 |
+------------+
avg
mysql> select avg(price) from Price;
+------------+
| avg(price) |
+------------+
| 303.1111 |
+------------+

（三）字段拼接、文本函数
1、拼接字段 CONCAT
SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;
mysql> SELECT DISTINCT CONCAT('User: ',user,'@',host) AS query FROM mysql.user;
+----------------------------+
| query |
+----------------------------+
| User: root@127.0.0.1 |
| User: @localhost |
| User: root@localhost |
| User: @mnt.localdomain |
| User: root@mnt.localdomain |
+----------------------------+
5 rows in set (0.00 sec)

2、文本函数
Upper() 转大写
Lower() 转小写
Left() 返回左边的字符串
Right() 返回右边的字符串
Length() 返回字符串的长度
其他文本函数可以需要时查询
mysql> select LEFT(pname,3) from Product where pname='T-Shirts1';
+---------------+
| LEFT(pname,3) |
+---------------+
| T-S |
+---------------+
1 row in set (0.00 sec)
mysql> select RIGHT(pname,3) from Product where pname='T-Shirts1';
+----------------+
| RIGHT(pname,3) |
+----------------+
| ts1 |
+----------------+
1 row in set (0.00 sec)
mysql> select Length(pname) from Product where pname='T-Shirts1';
+---------------+
| Length(pname) |
+---------------+
| 9 |
+---------------+
1 row in set (0.00 sec)
（四）获取时间
当前日期 curdate()
当前时间curtime()
当前时间日期 now()
当前unix时间 unix_timestamp()
1、当前日期 curdate()
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2014-01-23 |
+------------+
1 row in set (0.00 sec)

2、当前时间 curdate()
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 21:51:32 |
+-----------+
1 row in set (0.00 sec)

3、当前时间日期 now()
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2014-01-23 21:51:53 |
+---------------------+
1 row in set (0.00 sec)

4、当前unix时间 unix_timestamp()
mysql> select unix_timestamp();
+------------------+
| unix_timestamp() |
+------------------+
| 1390485160 |
+------------------+
1 row in set (0.00 sec)
可以将时间日期转换为unix时间
mysql> select unix_timestamp('1999-01-20 21:51:30');
+---------------------------------------+
| unix_timestamp('1999-01-20 21:51:30') |
+---------------------------------------+
| 916840290 |
+---------------------------------------+
1 row in set (0.00 sec)

UNIX时间戳转换为日期用函数FROM_UNIXTIME()
mysql> select FROM_UNIXTIME(916840290);
+--------------------------+
| FROM_UNIXTIME(916840290) |
+--------------------------+
| 1999-01-20 21:51:30 |
+--------------------------+
1 row in set (0.00 sec)

二、where子句 数据过滤
使用AND和OR 组合过滤条件
(一)基本操作
返回一条记录
mysql> select * from Product where pname='hat1';
+-----------+-------+------------+-------+
| productId | pname | created | price |
+-----------+-------+------------+-------+
| 1 | hat1 | 2000-11-25 | 100 |
+-----------+-------+------------+-------+
返回多条记录
mysql> select * from Product where created > '2008-11-25';
+-----------+-----------+------------+-------+
| productId | pname | created | price |
+-----------+-----------+------------+-------+
| 4 | hat4 | 2010-08-08 | 218 |
| 18 | T-Shirts4 | 2013-07-15 | 298 |
| 19 | T-Shirts5 | 2009-04-05 | 398 |
+-----------+-----------+------------+-------+
3 rows in set (0.00 sec)
满足多个条件中一个即可
mysql> select * from Product where pname='hat1' or pname='hat2';
+-----------+-------+------------+-------+
| productId | pname | created | price |
+-----------+-------+------------+-------+
| 1 | hat1 | 2000-11-25 | 100 |
| 2 | hat2 | 2003-11-25 | 88 |
+-----------+-------+------------+-------+
2 rows in set (0.01 sec)

同时满足多个条件
mysql> select * from Product where created >'2001-11-25' and price<100;
+-----------+-------+------------+-------+
| productId | pname | created | price |
+-----------+-------+------------+-------+
| 2 | hat2 | 2003-11-25 | 88 |
+-----------+-------+------------+-------+
1 row in set (0.00 sec)

(二) 不匹配操作【取反】 not
mysql> select * from Price where not price >200;
+-----------+-------+
| productId | price |
+-----------+-------+
| 1 | 200 |
| 2 | 188 |
+-----------+-------+
2 rows in set (0.00 sec)

(三)表达式比较
= <> != >= <= > <
mysql> select * from Price where price=200;
+-----------+-------+
| productId | price |
+-----------+-------+
| 1 | 200 |
+-----------+-------+
1 row in set (0.00 sec)
mysql> select * from Price where price!=200;
mysql> select * from Price where price<>200;
+-----------+-------+
| productId | price |
+-----------+-------+
| 2 | 188 |
| 3 | 250 |
| 4 | 318 |
| 15 | 290 |
| 16 | 268 |
| 17 | 318 |
| 18 | 398 |
| 19 | 498 |
+-----------+-------+
8 rows in set (0.00 sec)
mysql> select * from Price where price>=398;
+-----------+-------+
| productId | price |
+-----------+-------+
| 18 | 398 |
| 19 | 498 |
+-----------+-------+
2 rows in set (0.01 sec)
mysql> select * from Price where price>250 and price<300;
+-----------+-------+
| productId | price |
+-----------+-------+
| 15 | 290 |
| 16 | 268 |
+-----------+-------+
2 rows in set (0.00 sec)
（四）模式匹配 like
% 代表0个或多个字符
_ 代表单个字符
[] 指定范围 如[a-f],[0-9]或者集合[abcdef]
[^] 指定不属于的范围 [~a-f],[~0-9]
mysql> select * from Product where pname like 'hat%';
+-----------+-------+------------+-------+
| productId | pname | created | price |
+-----------+-------+------------+-------+
| 1 | hat1 | 2000-11-25 | 100 |
| 2 | hat2 | 2003-11-25 | 88 |
| 3 | hat3 | 2008-06-25 | 150 |
| 4 | hat4 | 2010-08-08 | 218 |
+-----------+-------+------------+-------+
4 rows in set (0.00 sec)
mysql> select * from Product where pname like 'hat_';
+-----------+-------+------------+-------+
| productId | pname | created | price |
+-----------+-------+------------+-------+
| 1 | hat1 | 2000-11-25 | 100 |
| 2 | hat2 | 2003-11-25 | 88 |
| 3 | hat3 | 2008-06-25 | 150 |
| 4 | hat4 | 2010-08-08 | 218 |
+-----------+-------+------------+-------+
4 rows in set (0.00 sec)
不区分大小写
not like
mysql> select * from Product where pname not like 'hat%';
+-----------+-----------+------------+-------+
| productId | pname | created | price |
+-----------+-----------+------------+-------+
| 15 | T-Shirts1 | 2004-06-15 | 190 |
| 16 | T-Shirts2 | 2002-11-10 | 168 |
| 17 | T-Shirts3 | 2000-03-19 | 218 |
| 18 | T-Shirts4 | 2013-07-15 | 298 |
| 19 | T-Shirts5 | 2009-04-05 | 398 |
+-----------+-----------+------------+-------+
[] 指定范围 如[a-f],[0-9]或者集合[abcdef] 【没有成功】
转义%
如果我就真的要查%或者_，怎么办呢？使用escape，转义字符后面的%或_就不作为通配符了，注意前面没有转义字符的%和_仍然起通配符作用
select username from gg_user where username like '%xiao/_%' escape '/';

(五)范围比较 between和in
1、in和not in
mysql> select * from Product where productId in (1,3,19);
+-----------+-----------+------------+-------+
| productId | pname | created | price |
+-----------+-----------+------------+-------+
| 1 | hat1 | 2000-11-25 | 100 |
| 3 | hat3 | 2008-06-25 | 150 |
| 19 | T-Shirts5 | 2009-04-05 | 398 |
+-----------+-----------+------------+-------+
3 rows in set (0.00 sec)
mysql> select * from Product where productId not in (1,3,19);
+-----------+-----------+------------+-------+
| productId | pname | created | price |
+-----------+-----------+------------+-------+
| 2 | hat2 | 2003-11-25 | 88 |
| 4 | hat4 | 2010-08-08 | 218 |
| 15 | T-Shirts1 | 2004-06-15 | 190 |
| 16 | T-Shirts2 | 2002-11-10 | 168 |
| 17 | T-Shirts3 | 2000-03-19 | 218 |
| 18 | T-Shirts4 | 2013-07-15 | 298 |
| 20 | Hat8 | 2000-01-01 | 298 |
+-----------+-----------+------------+-------+
7 rows in set (0.00 sec)
mysql> select * from Product where productId in (select productId from Product where price>300);
+-----------+-----------+------------+-------+
| productId | pname | created | price |
+-----------+-----------+------------+-------+
| 19 | T-Shirts5 | 2009-04-05 | 398 |
+-----------+-----------+------------+-------+
1 row in set (0.00 sec)
in的范围 可以是一个select子句，注意需要()

2、between 和 not between
mysql> select * from users where score between 50 and 70;
+----+-------+------+------------+-------------------+------------+------------+-------+
| id | uname | sex | birthday | email | department | comment | score |
+----+-------+------+------------+-------------------+------------+------------+-------+
| 1 | diege | | 1990-12-31 | diege@foxmail.com | tech | a good boy | 66 |
| 3 | lily |
| 1990-12-31 | lily@foxmail.com | tech | a good boy | 55 |
+----+-------+------+------------+-------------------+------------+------------+-------+
2 rows in set (0.00 sec)

mysql> select * from users where score not between 50 and 70;
+----+--------+------+------------+--------------------+------------+---------------+-------+
| id | uname | sex | birthday | email | department | comment | score |
+----+--------+------+------------+--------------------+------------+---------------+-------+
| 2 | hellen | | 1990-12-31 | diege1@foxmail.com | product | a good boy | 76 |
| 4 | sum |
| 1980-02-11 | sum@qq.com | tech | a good worker | 81 |
| 5 | jim |
| 1985-02-11 | jim@qq.com | market | a good newer | 98 |
+----+--------+------+------------+--------------------+------------+---------------+-------+

(六)空值比较
mysql> select * from users where email is null;
+----+-------+------+----------+-------+------------+---------+-------+
| id | uname | sex | birthday | email | department | comment | score |
+----+-------+------+----------+-------+------------+---------+-------+
| 6 | abing | | NULL | NULL | tech | NULL | 76 |
+----+-------+------+----------+-------+------------+---------+-------+
1 row in set (0.00 sec)

mysql> select * from users where email is not null;
+----+--------+------+------------+--------------------+------------+---------------+-------+
| id | uname | sex | birthday | email | department | comment | score |
+----+--------+------+------------+--------------------+------------+---------------+-------+
| 1 | diege | | 1990-12-31 | diege@foxmail.com | tech | a good boy | 66 |
| 2 | hellen | | 1990-12-31 | diege1@foxmail.com | product | a good boy | 76 |
| 3 | lily |
| 1990-12-31 | lily@foxmail.com | tech | a good boy | 55 |
| 4 | sum |
| 1980-02-11 | sum@qq.com | tech | a good worker | 81 |
| 5 | jim |
| 1985-02-11 | jim@qq.com | market | a good newer | 98 |
+----+--------+------+------------+--------------------+------------+---------------+-------+
注意不是 email not null,而是 email is not null; is关键字还是要的

(七)子查询
mysql> select productId,price from (select * from Price where price>200) as tmp_tb where productId<15;
+-----------+-------+
| productId | price |
+-----------+-------+
| 3 | 250 |
| 4 | 318 |
+-----------+-------+
2 rows in set (0.01 sec)
通过子查询产生一个临时表

三、From子句
select 查询对象由From子句指定
1、单个表或者视图
2、多个表或者视图
mysql> select * from Price,Product;
mysql> select * from Price as a,Product as b where a.productId=b.productId;
3、rowset_fucntion 行集函数
行集函数返回一个表或视图
4、user_define_function 表值函数
5、子查询
mysql> select productId,price from (select * from Price where price>200) as tmp_tb where productId<15;
+-----------+-------+
| productId | price |
+-----------+-------+
| 3 | 250 |
| 4 | 318 |
+-----------+-------+
2 rows in set (0.01 sec)