MySQL常用语句(一)

MySQL
MySQL
Catalogue
  1. 1. 关于Show语句
    1. 1.1. show variables 查看系统变量
    2. 1.2. show grants 查看用户权限
    3. 1.3. show columns from TABLES 查看表的列信息
    4. 1.4. show status 显示广泛的服务器状态信息
    5. 1.5. show create table [表] 显示创建数据表的MySQL语句
  2. 2. 查询语句
    1. 2.1. DISTINCT关键字
    2. 2.2. LIMIT
  3. 3. 排序检索
    1. 3.1. ORDER BY
    2. 3.2. 排序方向-DESC关键字
    3. 3.3. ORDER BY 和Limit组合
  4. 4. 过滤数据
    1. 4.1. where子句操作符
    2. 4.2. 空值检查
    3. 4.3. AND操作符
    4. 4.4. OR操作符
    5. 4.5. 计算次序
    6. 4.6. IN操作符
    7. 4.7. NOT操作符
  5. 5. 用通配符进行过滤
    1. 5.1. LIKE操作符
  6. 6. 使用正则表达式进行匹配
    1. 6.1. 基本字符匹配
  7. 7. 计算字段
    1. 7.1. 拼接字段
  8. 8. 使用数据处理函数
    1. 8.1. 常用文本处理函数
    2. 8.2. 日期和时间处理函数
    3. 8.3. 数值处理函数
  9. 9. 聚集函数汇总数据
    1. 9.1. AVG()函数
    2. 9.2. COUNT()函数
    3. 9.3. MAX()函数
    4. 9.4. MIN()函数
    5. 9.5. SUM函数
    6. 9.6. 聚集不同值
  10. 10. 分组数据
    1. 10.1. GROUP BY语句
    2. 10.2. 过滤分组(HAVING关键字)
    3. 10.3. 分组和排序
  11. 11. 参考文章

关于Show语句

show语句可以用来查询数据库，表，列和服务器状态信息，show后面还可以跟like操作符，甚至有的还可以跟where语句

show variables 查看系统变量

show variables 显示MySQL系统变量的值

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show variables like "%char%";
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8mb4                    |
| character_set_connection | utf8mb4                    |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8mb4                    |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.07 sec)

show grants 查看用户权限

1
2
3
4
5
6
7
mysql> show grants;
+--------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                                              |
+--------------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY PASSWORD '*DCC34AC0D1740AF22078E4130604D4AC65207D0E' WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.29 sec)

show columns from TABLES 查看表的列信息

1
2
3
4
5
6
7
8
mysql> show columns from user;
+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
| phone       | varchar(255) | NO   |     | NULL              |                |
+-------------+--------------+------+-----+-------------------+----------------+
9 rows in set (0.09 sec)

show status 显示广泛的服务器状态信息

1
2
3
4
5
6
7
8
mysql> show status;
+-----------------------------------------------+-------------+
| Variable_name                                 | Value       |
+-----------------------------------------------+-------------+
| Aborted_clients                               | 302         |
| Aborted_connects                              | 414         |
| Binlog_cache_disk_use                         | 0           |
 ......

show create table [表] 显示创建数据表的MySQL语句

1
2
3
4
5
6
7
8
9
mysql> show create table user;
| user  | CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `phone` varchar(255) NOT NULL COMMENT '手机号',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 |

1 row in set (0.10 sec)

更多关于show语法，查看官方手册: https://dev.mysql.com/doc/refman/5.7/en/show.html

查询语句

DISTINCT关键字

1
select distinct vent_id from products //返回不同的vent_id

注意: 不能部分使用DISTINCT ,DISTINCT应用于所有列而不仅是前置它的列

1
select distinct vent_id,price from customers//除非两个列都不同，这将检索出所有的列

LIMIT

1
select vent_id from customers limit 5;

而Limit 5,5指示从行5开始的5行，第一个表示开始位置，第二个表示要检索的行数。

MySQL5支持Limit的另一种语法，Limit 4 offset 3.从行3开始取4行

排序检索

ORDER BY

order by子句取一个或多个列的名字，据此对输出进行排序，默认为升序（ASC)

排序方向-DESC关键字

1
2
//以降序排列
select price from products order by price desc

想在多个列上进行排序，必须对每个列指定desc关键字

ORDER BY 和Limit组合

1
2
//保证order by 在from的后面，limit在order by的后面
select price from products order by price desc limit 1;

过滤数据

where子句操作符

1
不等于操作符: <> 或者!=

空值检查

1
select pro_name from products where price is null

AND操作符

1
select prod_id from products where vend_id =1003 and prod_price <= 10

OR操作符

OR操作符指示MySQL检索匹配任一条件的行，而不是同时匹配两个条件

计算次序

在OR和AND操作符一起用时，SQL优先处理AND操作符

IN操作符

IN操作符用来指定条件范围，范围中每个条件都可以进行匹配。IN取合法值的由逗号分隔的清单，全都括在圆括号中。

1
2
//检索1002和1003的产品，效果与OR操作符一样
select pro_name from products where vend_id in (1002,1003)

IN操作符的优点:

  • 在使用长的合法选项清单时，IN操作符的语法更清楚直观
  • 在使用IN,计算的次序更容易管理
  • IN操作符一般比OR操作符更快
  • IN最大优点是可以包含其他select语句，使得能够更动态地建立where子句

NOT操作符

NOT操作符只有一个功能，否定它之后跟的所有条件

1
2
//检索1002,1003以外的数据
select prod_name from products where vend_id not in (1002,1003)

用通配符进行过滤

LIKE操作符

百分号(%)通配符

%表示任意字符出现任意次数

1
2
3
4
5
//检索以jet开头的词，而不管它后面出现什么字符
select prod_id from products where prod_name like 'jet%';

//检索包含jet的词，而不论它前面或后面出现什么字符
select prod_id from products where prod_name like '%jet%';

下划线_通配符

下划线与%用途一样，但是它只匹配单个字符而不是多个字符

使用正则表达式进行匹配

基本字符匹配

1
select prod_name from products where prod_name regexp '1000';

Like操作符与REGEXP的区别

  • Like操作符匹配整个列，如果被匹配的文本在列中出现，Like将不会找到它，相应的行也不会被返回（除非使用通配符）
  • REGEXP在列中进行匹配，如果被匹配的文本在列中出现，REGEXP将找到它并返回行

计算字段

拼接字段

MySQL的SELECT语句中，可使用Concat()函数来拼接每个列

1
2
3
4
select Concat(vent_name,'(',vend_country,')') from vendors;

//结果
ACME (USA)

使用别名

1
select Concat(vent_name,'(',vend_country,')')  as vend_title from vendors;

使用数据处理函数

常用文本处理函数

  • 使用RTrim()函数来去除列值右边的空格
  • 使用Upper()函数来将文本转换为大写
  • 使用Lower()函数来将文本转换为小写
  • 使用Left()函数来返回串左边的字符，比如Left(content,3),第一个参数是字符串，第二个参数是返回的字符串长度

日期和时间处理函数

  • Date() 返回日期时间的日期部分
  • Time() 返回一个日期的时间部分
  • Now() 返回当前日期和时间
  • Month() 返回一个日期的月份部分
  • Year() 返回一个日期的年份部分

注意: MySQL使用的日期格式是yyyy-MM-dd

数值处理函数

  • Abs 返回一个数的绝对值
  • Rand() 返回一个随机数
  • Mod() 返回除操作的余数

聚集函数汇总数据

AVG()函数

AVG()函数返回某列的平均值,忽略列值为NULL的行

1
2
//返回products表中所有产品的平均值
select avg(prod_price) as avg_price from products

COUNT()函数

COUNT()函数返回某列的行数，一般用于计数。

COUNT()函数有两种表现形式:

  • 使用COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值NULL还是非空值
  • 使用COUNT(column)对特定列中具有值的进行计数，不包含空值NULL

MAX()函数

MAX()函数返回指定列中的最大值，要求指定列名

MIN()函数

MIN函数返回指定列中的最小值，要求指定列名

SUM函数

SUM函数返回指定列中的和，忽略NULL值

聚集不同值

  • 对所有行进行计算，指定ALL参数或者不给参数，它是默认
  • 只包含不同的值，指定DISTINCT参数
1
2
//平均值只考虑不同的值
select avg(distinct prod_price) as avg_price from products

分组数据

GROUP BY语句

利用GROUP BY语句创建分组

1
2
//以vend_id进行分组计算
select vend_id,count(*) as nums_prods from products group by vend_id

GROUP BY语句的规定:

  • GROUP BY可以包含任意数目的列
  • GROUP BY子句列出的列必须是检索列或有效的表达式
  • 分组列中如果有NULL值，NULL被分为一组
  • GROUP BY必须在WHERE之后，ORDER BY之前

过滤分组(HAVING关键字)

1
2
3
//过滤出count(*)>=2的那些分组，排除小于2的分组

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2

HAVIING和WHERE的区别: WHERE在分组前进行过滤，HAVING在分组后进行过滤

分组和排序

我们经常发现用GROUP BY分组的数据确实是以分组顺序输出的，但情况不是总是这样的，它并不是SQL规范所要求的。有时候我们应该明确提出ORDER BY语句进行对分组进行排序

参考文章

Bagikan Komentar