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 YORNfrom 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
maxmysql> select max(price) from Price;+------------+| max(price) |+------------+| 498 |+------------+1 row in set (0.00 sec)minmysql> select min(price) from Price; +------------+| min(price) |+------------+| 188 |+------------+1 row in set (0.00 sec)summysql> select sum(price) from Price; +------------+| sum(price) |+------------+| 2728 |+------------+avgmysql> select avg(price) from Price; +------------+| avg(price) |+------------+| 303.1111 |+------------+ (三)字段拼接、文本函数1、拼接字段 CONCATSELECT 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 likemysql> 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 inmysql> 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)