mysql中more语句,MySQL常用语句(一)

关于Show语句

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

show variables 查看系统变量

show variables 显示MySQL系统变量的值

1

2

3

4

5

6

7

8

9

10

11

12

13

14mysql> 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

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

8mysql> 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

8mysql> 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

9mysql> 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)

查询语句

DISTINCT关键字1select distinct vent_id from products //返回不同的vent_id

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

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

LIMIT1select 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不等于操作符: <> 或者!=

空值检查1select pro_name from products where price is null

AND操作符1select 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%';

下划线_通配符

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

使用正则表达式进行匹配

基本字符匹配1select prod_name from products where prod_name regexp '1000';

Like操作符与REGEXP的区别

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

REGEXP在列中进行匹配，如果被匹配的文本在列中出现，REGEXP将找到它并返回行

计算字段

拼接字段

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

1

2

3

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

//结果

ACME (USA)

使用别名

1select 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语句进行对分组进行排序

参考文章