mysql 用户函数_mysql 函数学习(常用) 及 用户管理

1、时间函数(组1)

SELECT CURRENT_DATE() AS date, CURRENT_TIME() AS `time`, CURRENT_TIMESTAMP() AS `timestamp`;--输出--+------------+----------+---------------------+--| date | time | timestamp |--+------------+----------+---------------------+--| 2019-04-10 | 12:55:01 | 2019-04-10 12:55:01 |--+------------+----------+---------------------+

2、时间函数(组2)

--把时间拿掉，只显示日期部份

SELECT DATE(NOW()) ASdate;--输出--+------------+--| date |--+------------+--| 2019-04-10 |--+------------+

-- YEAR MONTH DAY HOUR MINUTE SECOND

SELECT DATE_ADD(DATE(NOW()), INTERVAL 1 DAY) AS `after_date`, DATE_ADD(DATE(NOW()), INTERVAL 1 MONTH) AS `after_month`, DATE_ADD(DATE(NOW()), INTERVAL 1 YEAR) AS`after_year`;--输出

+------------+-------------+------------+--| after_date | after_month | after_year |--+------------+-------------+------------+--| 2019-04-11 | 2019-05-10 | 2020-04-10 |--+------------+-------------+------------+

--用法如上面的方法

SELECT DATE_SUB(DATE(NOW()), INTERVAL 1 MONTH) AS`before_month`;--输出--+--------------+--| before_month |--+--------------+--| 2019-03-10 |--+--------------+

--显示两个日期的时间差，结果以天数为单位，如果后面的日期比前面大，那么显示的是负数，否则显示的是正数

SELECT DATEDIFF("2017-5-3","2018-4-8") AS `big`, DATEDIFF("2017-5-3","2016-4-8") AS`small`;--输出--+------+-------+--| big | small |--+------+-------+--| -340 | 390 |--+------+-------+

SELECT DATEDIFF(NOW(), '1949-10-1')/365 AS `year`;--输出--+---------+--| year |--+---------+--| 69.5699 |--+---------+

3、时间函数(组3)

SELECT TIMEDIFF(NOW()+10,NOW()) AS`diff`--输出--+----------+--| diff |--+----------+--| 00:00:10 |--+----------+

SELECT YEAR(NOW()) AS year, MONTH(NOW()) AS month, DAY(NOW()) AS day;--输出--+------+-------+------+--| year | month | day |--+------+-------+------+--| 2019 | 4 | 10 |--+------+-------+------+

UNIX_TIMESTAMP() =>返回当前时间的时间截

FROM_UNIXTIME(时间截，格式)或者用DATE_FORMAT(时间截，格式)用法也是一样的

SELECTUNIX_TIMESTAMP();--输出--+------------------+--| UNIX_TIMESTAMP() |--+------------------+--| 1554909947 |--+------------------+

SELECT FROM_UNIXTIME(1554909947, "%Y-%m-%d %H:%i:%s") AStime;--输出--+---------------------+--| time |--+---------------------+--| 2019-04-10 23:25:47 |--+---------------------+

3、字符串函数

CHARSET(str) 返回字符串对应的字符集

--测试表内容worker表--+----+------+--------+-------+-----------+--| id | name | course | score | fix_score |--+----+------+--------+-------+-----------+--| 1 | aaa | 1,2 | 80 | 10 |--| 2 | bbb | 1,5 | 30 | 20 |--| 3 | ccc | 3,5 | 40 | 30 |--| 4 | ddd | 5,6 | 50 | 40 |--| 5 | eee | 2 | 80 | 50 |--+----+------+--------+-------+-----------+

SELECT CHARSET(`name`) AS `charset` FROM`worker`;--输出--+---------+--| charset |--+---------+--| utf8 |--| utf8 |--| utf8 |--| utf8 |--| utf8 |--+---------+

CONCAT(str1,str2,...)字符串连接函数

SELECT CONCAT(`name`,'分数是',`SCORE`) AS `intro` FROMworker;--输出--+-------------+--| intro |--+-------------+--| aaa分数是80 |--| bbb分数是30 |--| ccc分数是40 |--| ddd分数是50 |--| eee分数是80 |--+-------------+

UCASE(str)，LCASE(str)字母的大小写转换

SELECT UCASE(`name`), LCASE(`name`) FROM`worker`;--输出--+---------------+---------------+--| UCASE(`name`) | LCASE(`name`) |--+---------------+---------------+--| AAA | aaa |--| BBB | bbb |--| CCC | ccc |--| DDD | ddd |--| EEE | eee |--+---------------+---------------+

LEFT(str，len)，RIGHT(str，len)分别表示从左边或者是右边的字符串取出len个字符

SELECT LEFT(`name`, 1) AS `left`,RIGHT(`name`, 1) AS `right` FROM`worker`;--输出--+------+-------+--| left | right |--+------+-------+--| a | a |--| b | b |--| c | c |--| d | d |--| e | e |--+------+-------+

LENGTH(str) 表示计算字符串的长度(注意：是计算字节的长度而非字符的长度与默认的编码有关系)

SELECT LENGTH(`name`) FROMworker;SELECT LENGTH(`name`) FROM worker WHERE `id`=1;--输出--+----------------+--| LENGTH(`name`) |--+----------------+--| 3 |--| 3 |--| 3 |--| 3 |--| 3 |--+----------------+--+----------------+--| LENGTH(`name`) |--+----------------+--| 3 |--+----------------+

REPLACE(str，str_find，str_repl) 表示在str这个字符串中找到str_find字符，并且把它替换成str_repl这个字符

SELECT REPLACE(`name`,"b", "haha") FROM `worker`; --不会改变原有的数据--输出--+-----------------------------+--| REPLACE(`name`,"b", "haha") |--+-----------------------------+--| aaa |--| hahahahahaha |--| ccc |--| ddd |--| eee |--+-----------------------------+

SELECT REPLACE(`name`, "a", "are you ok???") AS `name` FROM `worker` WHERE `id`= 1;--输出--+-----------------------------------------+--| name |--+-----------------------------------------+--| are you ok???are you ok???are you ok??? |--+-----------------------------------------+

SUBSTRING(str，position，len) 表示从str的position这个位置取len个字符，如果没有指定len那么表示取完字符串，position是从1开始计算的

SELECT SUBSTRING(`name`, 2, 2) AS abs FROM`worker`;--输出--+------+--| abs |--+------+--| aa |--| bb |--| cc |--| dd |--| ee |--+------+

SELECT SUBSTRING(`name`, 1) AS abs FROM`worker`;--输出--+------+--| abs |--+------+--| aaa |--| bbb |--| ccc |--| ddd |--| eee |--+------+

LTRIM(str)，RTRIM(str)，TRIM(str)分别表示去除左，右以及全部的空格

字符串综合案例，要求显示名字首字母小写，其他大写的

SELECT CONCAT(LCASE(LEFT(`name`,1)), UCASE(SUBSTRING(`name`,1))) AS `name` FROM`worker`;--输出--+------+--| name |--+------+--| aAAA |--| bBBB |--| cCCC |--| dDDD |--| eEEE |--+------+

4、数学函数

ABS(num) 表示求数字绝对值

SELECT ABS(`fix_score`) AS num FROM`worker`;--输出--+-----+--| num |--+-----+--| 10 |--| 20 |--| 30 |--| 40 |--| 50 |--+-----+

CEILING(num) 表示向上取整，  FLOOR(num) 表示向下取整，ROUND(num1，num2) 表示进行四舍五入，num2表示小数点的位数

SELECT CEILING(`num`) AS `ceil`, FLOOR(`num`) AS `floor` FROM`worker`;--输出--+------+-------+--| ceil | floor |--+------+-------+--| 1 | 0 |--| 35 | 34 |--| 235 | 234 |--| 99 | 98 |--| 223 | 222 |--+------+-------+

FROMAT(num，len)格式化数字，len表示保留几位小数

SELECT FORMAT(`num`,2) AS `formate` FROM`worker`;--输出--+---------+--| formate |--+---------+--| 0.34 |--| 34.12 |--| 234.12 |--| 98.90 |--| 222.12 |--+---------+

MOD(num1，num2) 表示求num1 % num2的余数

SELECT MOD(10, 2) AS `num1`, MOD(10, 3) AS`num2`;--输出--+------+------+--| num1 | num2 |--+------+------+--| 0 | 1 |--+------+------+

RAND() 表示返回0-1的随机数

SELECT RAND() AS `rand1`, ROUND(RAND()) AS`rand2`;--输出--+--------------------+-------+--| rand1 | rand2 |--+--------------------+-------+--| 0.3990743340915761 | 0 |--+--------------------+-------+

5、mysql的流程控制语句

注意： 以上只改变数据的显示，而不会改变原数据

--表内容--+----+------+--------+-------+-----------+------+--| id | name | course | score | fix_score | num |--+----+------+--------+-------+-----------+------+--| 1 | aaa | 1,2 | 80 | 10 | 0.00 |--| 2 | bbb | 1,5 | 30 | 20 | 0.00 |--| 3 | ccc | 3,5 | 40 | 30 | 0.00 |--| 4 | ddd | 5,6 | 50 | 40 | 0.00 |--| 5 | eee | 2 | 80 | 50 | 0.00 |--+----+------+--------+-------+-----------+------+

SELECT SUM(IF(`score`>50, 1, 0)) AS `count` FROM`worker`;--输出--+-------+--| count |--+-------+--| 2 |--+-------+

--需求，给分数小于等30的num加20，30-50加30，50-90加50,不改变原表

SELECT `name`,(CASE WHEN `score`<=30 THEN `num`+20 WHEN 30

6、mysql的其他函数

7、mysql用户管理

mysql用户都保存在mysql数据库的user表中，具体如下:

说明

(1)  Host表示该用户可以登录的ip, 如果是localhost表示只能本机登录，不能远程登录,注意在实际开发中，我们只能让用户本机登录mysql

(2)  User字段；用户名 ，说明在mysql中，一个完整的用户名是有User和Host组成比如 'root'@'localhost';

(3)  Password:用户密码, 使用password函数加密的.

mysql用户创建

基本语法/ 指令

create user '用户名'@'主机名' identified by '密码';

(1) 这是创建一个用户是   '用户名'@'主机名'

(2) identified by 后面可以指定密码

--表示创建一个用户名为yf密码为password的账户

CREATE USER "yf"@"localhost" IDENTIFIED BY "password";

mysql用户删除

基本语法

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

--删除指定用户

DROP USER "yf"@"localhost";

修改密码

修改自己的密码

set password = password('密码');

--修改密码，把现在密码改成test

SET PASSWORD = PASSWORD("test")

修改其它用户的密码

set password for '用户名'@'主机名'= password('密码');

给mysql用户赋权限

当我们新创建一个用户时，该用户除了可以登录，没有其它的权限.

在mysql中，权限一览表:

基本语法

grant 权限列表 ON 数据库.表(数据对象) TO '用户名'@'主机名'  [identified by '密码'];    数据对象包括 表  视图  函数 存储过程  触发器

(1)  权限列表可以是 select , update , insert ,delete ...., 如果你希望将所有的权限都给某个用户，则可以写 all， 比如:

也可以一次给多个权限

grant select , delete , update to 数据库.表'hsp'@'localhost' ;

(1)  数据库.表:

a、 可以将某个库的某个表给某个用户使用,比如 itbull.emp

b、 如果希望将某个库的所有的表都给用户，则可以 数据库.*, 比如itbull.*

c、 如果你希望将所有库的所有表都给用户，可以写成 *.*

(2)  对identified by '密码' 的说明

回收mysql用户的权限

基本语法

revoke 权限列表  ON  数据库.表名  from '用户名'@'主机名 ';

说明:

revoke 的权限列表和 数据库.表名的用法和前面 grant 是一样。

举例说明

查询mysql用户的权限

基本语法

show grants for '用户名'@'主机名';

让权限生效

如果我们给某个用户赋权限后，发现没有生效，则可以使用下面的指令，让权限立即管用.

用户管理的注意事项

在创建用户的时候，如果不指定Host, 则为% , %表示表示所有IP都有连接权限

说明，这样创建用户很危险，该用户可以远程登录，所有不要这样使用.

a、还可以这样创建用户

create user  'xxx'@'192.168.0.%'  表示 xxx用户在 192.168.0.*的ip可以登录mysql

b、在删除用户的时候，如果 host 不是 %, 需要明确指定  '用户'@'host'， 如果host是 %, 则删除用户不需要指定host

8、其他需要掌握的重要函数：

GROUP_CONCAT， INTERSET，MINUS