Mysql常用的查询命令

查询表和库的信息

如果忘记正在使用的数据库：

select DATABASE();

输出正在使用的数据库中的所有表的名字：

show tables;

如果你想要知道一个表的结构，可以使用DESCRIBE命令；它显示表中每个列的信息：

DESCRIBE pet;   或者是 Desc pet;

查询语句 select

pet是示例表，内容如下：

查询全部字段，输出按照插入顺序显示

select * from pet;

查询部分指定字段，输出按照插入顺序显示

select name,birth from pet;

ORDER BY关键字 跟据指定的name 字段的值进行排序 不区分大小写

select * from pet ORDER BY name;

区分大小写进行排序 使用 BINANRY 关键字

select * from pet ORDER BY BINARY name;

指定排序结果升序或者是降序 ASC（升序） DESC（降序）

select * from pet ORDER BY name desc;

日期操作 可以使用相关的日期的函数

CURDATE() 获取当前时间 YEAR(CURDATE()) YEAR 函数获取日期参数的年份
RIGHT()提取日期的MM-DD (日历年)部分的最右面5个字符，比较MM-DD值的表达式部分的值一般为1或0，如果CURDATE()的年比birth的年早，则年份应减去1。整个表达式有些难懂，使用alias (age)来使输出的列标记更有意义。

AS 关键字 可以给查询到的字段起一个别名进行显示

MySQL提供几个日期部分的提取函数，例如YEAR( )、MONTH( )和DAYOFMONTH( )。

SELECT name ,birth,CURDATE(),(year(CURDATE())-year(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) as age from pet;

SELECT name ,birth,CURDATE(),(year(CURDATE())-year(birth))-(RIGHT(CURDATE(),5)<RIGHT(birth,5)) as age from pet ORDER BY name;

**DATE_ADD( )**允许在一个给定的日期上加上时间间隔。如果在NOW( )值上加上一个月，然后用MONTH()提取月份，结果产生生日所在月份：

SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

通过DATE_ADD( ) 可以避免出现错误日期，如果当前日期为12月份，则DATE_ADD( )会在加月份加一的时候将13 改成一月份 进入一个循环

字符串拼接

SELECT CONCAT(2,' test');
-> '2 test'

用cast 将数字转化为字符串

SELECT 38.8, CAST(38.8 AS CHAR);
-> 38.8, '38.8'

模式匹配

MySQL提供标准的SQL模式匹配，以及一种基于象Unix实用程序如vi、grep和sed的扩展正则表达式模式匹配的格式。

SQL模式匹配允许你使用**“_”匹配任何单个字符，而“%”匹配任意数目字符(包括零字符)。在 MySQL中，SQL的模式默认是忽略大小写的。下面给出一些例子。注意使用SQL模式时，不能使用=或!=；而应使用LIKE或NOT LIKE**比较操作符。

以“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个字符的名字，使用5个“_”模式字符，：

SELECT * FROM pet WHERE name LIKE '_____';

由MySQL提供的模式匹配的其它类型是使用扩展正则表达式。当你对这类模式进行匹配测试时，使用REGEXP和NOT REGEXP操作符(或RLIKE和NOT RLIKE，它们是同义词)。
其他的几个字符匹配符：
匹配任意个数的a或b字符

SELECT * FROM pet WHERE name RLIKE '[ab]*'

‘.’ 匹配任何单个的字符。
字符类“[…]”匹配在方括号内的任何字符。例如，“[abc]”匹配“a”、“b”或“c”。为了命名字符的范围，使用一个“-”。“[a-z]”匹配任何字母，而“[0-9]”匹配任何数字。
“ * ”匹配零个或多个在它前面的字符。例如，“x*”匹配任何数量的“x”字符，“[0-9]”匹配任何数量的数字，而“.”匹配任何数量的任何字符。

字符串比较

若所有的字符串均相同，则返回STRCMP()，若根据当前分类次序，第一个参数小于第二个，则返回 -1，其它情况返回 1 。

mysql> SELECT STRCMP(‘text’, ‘text2’);

    -> -1

mysql> SELECT STRCMP(‘text2’, ‘text’);

    -> 1

mysql> SELECT STRCMP(‘text’, ‘text’);

    -> 0

常用的查询函数

1、显示某个列的最大值

SELECT MAX(article) AS article FROM shop;
+---------+
| article |
+---------+
|       4 |
+---------+

2、拥有某个列的最大值的行

任务：找出最贵物品的编号、销售商和价格。
可以通过结合子句查询的结果进行查询，

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop);

另一个解决方案是按价格降序排序所有行并用MySQL特定LIMIT子句只得到第一行： LIMIT 1 限制了输出只显示一行

SELECT article, dealer, price
FROM shop
ORDER BY price DESC
LIMIT 1;

3、按类别输出最大值

有四种文章 ，分别输出每种文章中价格最高的 GROUP BY 按组分类

SELECT article, MAX(price) AS price
FROM   shop
GROUP BY article

+---------+-------+
| article | price |
+---------+-------+
|    0001 |  3.99 |
|    0002 | 10.99 |
|    0003 |  1.69 |
|    0004 | 19.95 |
+---------+-------+

4、用户变量声明和使用

可以把查询的结果放入用户变量中；用@变量名 来表示。

SELECT @last := LAST_INSERT_ID();

5、 通过多个关键字搜索

AND 相当于“与” 需要同时满足AND 前后的两个条件
OR 相当于“或” 满座OR 前后的任意一个条件即可
一个比较灵活的例子是寻找两个通过OR组合到一起的关键字：

SELECT field1_index, field2_index FROM test_table
WHERE field1_index = '1' OR  field2_index = '1'

每个SELECT只搜索一个关键字，可以进行优化：
通过UNION 关键字可以达到OR 的相同效果。

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

6、 修改auto_increment的起始值

ALTER TABLE tbl AUTO_INCREMENT = 100;

控制流程表达式

进行if-else if -else 选择控制 case 1 表示输入的参数是1 when 1 表示和1 比较 如果 case的值 是 when 的值 then 输出 ‘one’ 同理 WHEN 2 THEN ‘two’ ELSE ‘more’ END; 如果case的值是2 则输出 ‘two’ 如果都不是 输出 ‘more’

 SELECT CASE 2 WHEN 1 THEN 'one'
 WHEN 2 THEN 'two' ELSE 'more' END;

 SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
 -> 'true'

SELECT CASE BINARY 'B'
WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
-> NULL

IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值，具体情况视其所在语境而定。

SELECT IF(1>2,2,3);

        -> 3