三.mysql数据库常用进阶命令

系列文章目录

二.mysql数据库常用基础命令

---

1.管理用户

代码如下（示例）：

查询用户：use mysql;
	     select * from user；
创建用户 create user '用户名'@'主机名'identified by '密码';

修改用户密码 alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

删除用户 drop user '用户名'@'主机名';

权限控制：查询权限 show grants for '用户名'@'主机名';

授予权限 grant 权限列表 on 数据库名 ，表名 to '用户名'@'主机名';

撤销权限 revoke 权限列表 on 数据库名，表名 from '用户名'@'主机名';

---

2. where

代码如下（示例）：

like

	'm%aa_'  查询m开头且包含aa且后有一位随机值的行

regexp

	A到H开头
		where column regexp '^[A-H]'
	不以A到H开头
		where column regexp '^[^A-H]'
	所有以GFS开头
		where name regexp'^[GFS]'

in

	WHERE column_name IN (value1,value2,...);
	select * from 读者 where 姓名 in ('李明','刘伟');

between

	select * from 图书 where 价格 between 40 and 70;
	介于 'A' 和 'H' 之间字母开始的所有网站
		WHERE name BETWEEN 'A' AND 'H';
	不介于 'A' 和 'H' 之间字母开始的所有网站 加个 not
	 介于 '2016-05-10' 和 '2016-05-14' 之间的所有访问记录
		WHERE date BETWEEN '2016-05-10' AND '2016-05-14';

---

3.having

代码如下（示例）：

1.where在group by前， having在group by 之后

2.聚合函数（avg、sum、max、min、count），不能作为条件放在where之后，但可以放在having之后

例1.select Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
inner jion Websites
on access_log.site_id=Websites.id)
group by Websites.name
having SUM(access_log.count) > 200;
	
例2.select Websites.name, SUM(access_log.count) AS nums FROM Websites
inner jion access_log
on Websites.id=access_log.site_id
where Websites.alexa < 200 
group by Websites.name
having SUM(access_log.count) > 200;

---

4.exists 存在判断

代码如下（示例）：

如果有一条或多条记录存在返回 True，否则返回 False

例：select Websites.name, Websites.url 
	from Websites 
	where exists (
	select count from access_log where Websites.id = access_log.site_id and	count > 200
	);
	
可以在exists 前加not

---

5. 视图 create view

代码如下（示例）：

创建视图

create view view_name as
select column(s)
from table_name
where condition

查询视图

	select * from view_name;

更新视图

	在创建修改

删除视图

	drop view view_name

作用

---

6.函数

代码如下（示例）：

计算

AVG() - 返回平均值
	select avg(column_name) as avg_cloumn_name from table_name
COUNT() - 返回行数
FIRST() - 返回第一个记录的值
	也可以limit 1;
LAST() - 返回最后一个记录的值
	也可以倒序 limit1
MAX() - 返回最大值
MIN() - 返回最小值
SUM() - 返回总和

窗口

1. 创建窗口

2. 参数含义 
   WINDOW w AS (PARTITION BY t_category_id ORDER BY t_price DESC)
   
   window_name：为窗口设置一个别名，用来标识窗口。方便窗口的复用。
   ·PARTITION BY子句：指定窗口函数按照哪些字段进行分组。分组后，窗口函数可以在每个分组			中分别执行。
   ·ORDER BY子句：指定窗口函数按照哪些字段进行排序。执行排序操作使窗口函数按照排序后的数据记录的顺序进行编号。
   
   ·FRAME子句：为分区中的某个子集定义规则，可以用来作为滑动窗口使用。
   

3. 

序号函数

表内 字段分类 再排序（
1序号并列(不连续)，rank()
2序号并列(连续)  dense_rank()
3.不并列排序）row_number()

分布函数

percent_rank() 排序百分比
函数表达式 (rank - 1) / (rows - 1)
cume_dist() 累计分布百分比

前后函数

lag(expr,n)与前第n行的差值
lead(expr,n)与后第n行的差值

首尾函数

first_value(expr) 获得分区 再字段排序  expr字段的第一个值
last_value(expr) 类上

其他函数

nth_value(expr,n) 类首位函数 ，取第ng个值
ntile(n) over 分区 order by 排序 再分为n个桶 记录桶编号

参考案例详见：

其他

ucase() 函数把字段的值转换为大写
		select ucase(column_name) from table_name;
		
		用于 SQL Server 的语法
			select upper(column_name) from table_name;
			
lcase() 函数把字段的值转换为小写。

mid() 函数用于从文本字段中提取字符
		select mid(column_name,start[,length]) from table_name;
		
		例：提取前 4 个字符：
				select mid(name,1,4) AS ShortTitle
				from Websites;
		Oracle 中没有 MID 函数，有 substr 函数有类似功能:
				select substr(("列名"，a,b) from <table_name>;
				SELECT substr(name,1,4) AS ShortTitle
				FROM Websites;
len() 函数返回文本字段中值的长度

round() 函数用于把数值字段舍入为指定的小数位数
		select round(column_name,decimals) from TABLE_NAME;
		
format() 函数用于对字段的显示进行格式化。
		select format(column_name,format) from table_name;
		select name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date from Websites;

---