IT小白

这个屌丝很懒，什么也没留下！

热门标签

mysql from和where_MYSQL复习笔记5-select-from-where子句

作者：IT小白 | 2024-04-05 01:55:39

踩

mysql 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

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';

+-----------+-------+------------+-------+

+-----------+-------+------------+-------+

| 1 | hat1 | 2000-11-25 | 100 |

+-----------+-------+------------+-------+

返回多条记录

mysql> select * from Product where created > '2008-11-25';

+-----------+-----------+------------+-------+

+-----------+-----------+------------+-------+

| 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';

+-----------+-------+------------+-------+

+-----------+-------+------------+-------+

| 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;

+-----------+-------+------------+-------+

+-----------+-------+------------+-------+

| 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%';

+-----------+-------+------------+-------+

+-----------+-------+------------+-------+

| 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_';

+-----------+-------+------------+-------+

+-----------+-------+------------+-------+

| 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%';

+-----------+-----------+------------+-------+

+-----------+-----------+------------+-------+

| 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);

+-----------+-----------+------------+-------+

+-----------+-----------+------------+-------+

| 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);

+-----------+-----------+------------+-------+

+-----------+-----------+------------+-------+

| 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);

+-----------+-----------+------------+-------+

+-----------+-----------+------------+-------+

| 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;

+----+-------+------+------------+-------------------+------------+------------+-------+

+----+-------+------+------------+-------------------+------------+------------+-------+

| 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;

+----+--------+------+------------+--------------------+------------+---------------+-------+

+----+--------+------+------------+--------------------+------------+---------------+-------+

| 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;

+----+-------+------+----------+-------+------------+---------+-------+

+----+-------+------+----------+-------+------------+---------+-------+

+----+-------+------+----------+-------+------------+---------+-------+

1 row in set (0.00 sec)

mysql> select * from users where email is not null;