欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL中关于ORDERBY、DISTINCT、ALTER、LIKE/NOTLIKE、REGEXP/NOTREGEXP、COUNT、MAX的使用介绍

程序员文章站 2022-04-22 08:41:13
排序: SELECT field1, field2,...fieldN table_name1, table_name2... ORDER BY field1, [fiel...

排序:

SELECT field1, field2,...fieldN table_name1, table_name2...
ORDER BY field1, [field2...] [ASC [DESC]]

你可以使用任何字段来作为排序的条件,从而返回排序后的查询结果。
你可以设定多个字段来排序。
你可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下,它是按升序排列。
你可以添加 WHERE...LIKE 子句来设置条件。

对查询的结果去重(DISTINCT):

SELECT DISTINCT species FROM pet

更新表结构:

删除列:

ALTER TABLE pet DROP COLUMN death;

添加列:

ALTER TABLE pet ADD COLUMN id VARCHAR(20) NOT NULL;

修改列描述:

ALTER TABLE pet MODIFY COLUMN id VARCHAR(20) BIGINY NOT NULL;

添加/删除主键:

ALTER TABLE pet ADD/DROP PRIMARY KEY(id);

从txt文件导入数据

mysql> CREATE TABLE pet (name VARCHAR(20), owner VARCHAR(20),
 -> species VARCHAR(20), sex CHAR(1), birth DATE, death DATE);

如 pets.txt:

Chirpy1 Gwen    bird    m   1998-09-11  1995-07-29
Chirpy  Gwen    bird    f   1998-09-11  1995-07-29

mysql> LOAD DATA LOCAL INFILE '/path/pets.txt' INTO TABLE pet;

If you created the file on Windows with an editor that uses \r\n as a line terminator, you should use this statement instead:

    mysql> LOAD DATA LOCAL INFILE '/path/pet.txt' INTO TABLE pet
     -> LINES TERMINATED BY '\r\n';

(On an Apple machine running OS X, you would likely want to use LINES TERMINATED BY '\r'.)

计算针对某个日期字段相对于某个时间点经过的时间(可以是year,month,day,hour,minute,second)

mysql> SELECT name, birth, CURDATE(),
 -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
 -> FROM pet ORDER BY age ;

假设用户表中存储着用户的生日信息(DATE类型),如何过滤出本月或者今天过生日的用户:

SELECT name,owner,MONTH(birth) FROM pet WHERE MONTH(birth)>3;
SELECT name,owner,DAY(birth) FROM pet WHERE DAY(birth)>3;

动态获取

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

mysql> SELECT name, birth FROM pet
 -> WHERE DAY(birth) = DAT(DATE_ADD(CURDATE(),INTERVAL 0 DAY));

你知道以下SQL语句的输出是什么吗?

mysql> SELECT 0 IS NULL, 0 IS NOT NULL, '' IS NULL, '' IS NOT NULL;

是下面这样:
+———–+—————+————+—————-+
| 0 IS NULL | 0 IS NOT NULL | ” IS NULL | ” IS NOT NULL |
+———–+—————+————+—————-+
| 0 | 1 | 0 | 1 |
+———–+—————+————+—————-+

模式匹配(LIKE/NOT LIKE)

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary
number of characters (including zero characters).

To find names beginning with b:

mysql> SELECT * FROM pet WHERE name LIKE 'b%';

To find names ending with fy:

mysql> SELECT * FROM pet WHERE name LIKE '%fy';

To find names containing a w:

mysql> SELECT * FROM pet WHERE name LIKE '%w%';

To find names containing exactly five characters, use five instances of the _ pattern character:

mysql> SELECT * FROM pet WHERE name LIKE '_____';

如果想在模式匹配中使用其它的正则,那么请使用REGEXP and NOT REGEXP operators (or RLIKE and NOT RLIKE, which are synonyms).

. [...] *  use ^ at thebeginning or $ at the end of the pattern

To find names beginning with b, use ^ to match the beginning of the name:

mysql> SELECT * FROM pet WHERE name REGEXP '^b';
mysql> SELECT * FROM pet WHERE name REGEXP BINARY '^b';#大小写敏感的(BINARY)

To find names ending with fy, use $ to match the end of the name:

mysql> SELECT * FROM pet WHERE name REGEXP 'fy$';

To find names containing a w, use this query:

mysql> SELECT * FROM pet WHERE name REGEXP 'w';

To find names containing exactly five characters, use ^ and $ to match the beginning and end of the name, and five instances of . in between:

mysql> SELECT * FROM pet WHERE name REGEXP '^.....$';
also:mysql> SELECT * FROM pet WHERE name REGEXP '^.{5}$';

分组计数:
You can use COUNT() if you want to find out how many pets each owner has:

mysql> SELECT owner, COUNT(*) FROM pet GROUP BY owner;

ONLY_FULL_GROUP_BY参数开启与未开启区别

If the ONLY_FULL_GROUP_BY SQL mode is enabled, an error occurs:

mysql> SET sql_mode = 'ONLY_FULL_GROUP_BY';
Retrieving Information from a Table
265
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
ERROR 1140 (42000): In aggregated query without GROUP BY, expression
#1 of SELECT list contains nonaggregated column 'menagerie.pet.owner';
this is incompatible with sql_mode=only_full_group_by

If ONLY_FULL_GROUP_BY is not enabled, the query is processed by treating all rows as a single
group, but the value selected for each named column is indeterminate. The server is free to select
the value from any row:

mysql> SET sql_mode = '';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT owner, COUNT(*) FROM pet;
+--------+----------+
| owner | COUNT(*) |
+--------+----------+
| Harold | 8 |
+--------+----------+
1 row in set (0.00 sec)

假如有一个商品的数据表,请查找出价格最贵的一件商品的名称以及价格

SELECT name,MAX(price) AS price FROM shop;

also:

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