添加用户

1. 允许本地访问的用户（127.0.0.1）
create user test1@localhost identified by '123456';  
2. 允许外网IP访问的用户
create user 'test1'@'%' identified by '123456'; 

用户分配权限

授予用户在本地服务器对该数据库的全部权限
grant all privileges on dbname.* to test1@localhost;
授予用户通过外网IP对于该数据库的全部权限
grant all privileges on dbname.* to 'test1'@'%';  
刷新权限
flush privileges; 

查询用户的权限

mysql> show grants for test1;
+---------------------------------------------------+
| Grants for test1@%                                |
+---------------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%`                 |
| GRANT ALL PRIVILEGES ON `MyDB`.* TO `test1`@`%`   |
| GRANT ALL PRIVILEGES ON `common`.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `course`.* TO `test1`@`%` |
| GRANT ALL PRIVILEGES ON `dbname`.* TO `test1`@`%` |
+---------------------------------------------------+
5 rows in set (0.00 sec)

给用户全部权限

mysql> grant all privileges on *.* to 'test1'@'%' ;
Query OK, 0 rows affected (0.04 sec)

撤销用户的全部授权

mysql> revoke all privileges on *.* from 'test1'@'%' ;
Query OK, 0 rows affected (0.09 sec)
(上述权限移除, 如果登录的用户没有重新的登录, 原有的权限不会丢失, 刷新权限也没有用, 这个权限必须重新登录才见到效果)

仅仅给用户某个数据库的查询权限

mysql> grant select on common.* to 'test1'@'%';
Query OK, 0 rows affected (0.04 sec)
mysql> show grants for test1;

+-------------------------------------------+
| Grants for test1@%                        |
+-------------------------------------------+
| GRANT USAGE ON *.* TO `test1`@`%`         |
| GRANT SELECT ON `common`.* TO `test1`@`%` |
+-------------------------------------------+
2 rows in set (0.00 sec)

PDOException: SQLSTATE[HY000] [2002] No such file or directory

找到相应的.sock文件，并设置php.ini文件中的pdo_mysql.default_socket的值为.sock文件的路径。
[root@localhost src]# cat /etc/my.cnf
找到配置 socket      = /tmp/mysql.sock


[root@localhost ~]# whereis php
php: /usr/bin/php /usr/lib64/php /etc/php.ini /etc/php.d /usr/local/php /usr/share/php /usr/share/man/man1/php.1.gz
[root@localhost ~]# vim /etc/php.ini
>/pdo_mysql.default_socket # 搜索这段参数的位置

pdo_mysql.default_socket= /tmp/mysql.sock #注意有的配置为 /tmp/mysqld.sock 根据前面查到的配置来
[root@localhost ~]# service php-fpm restart

• 在对应的字段上拼接 \t 即可

select id,mobile,`name`,concat("\t",idcard) as idcard from users

• 如果用户无授权, 给的是拼接小星星的字符串

select id
,IF(mobile IS NULL,'',CONCAT(LEFT(mobile,3),'******',RIGHT(mobile,2))) AS mobile
,IF(name IS NULL,'',CONCAT(LEFT(name,1),'**')) AS name
,IF(idcard IS NULL,'',CONCAT(LEFT(idcard,3),'******',RIGHT(idcard,2))) AS idcard 
from users

• 将身份证除了前面6位和最后4位显示, 其余替换为*

SELECT INSERT('1234567890abcdefg',7,LENGTH('1234567890abcdefg')-10,REPEAT('*',LENGTH('1234567890abcdefg')-10))

时间范围

• （当数据库字段中存储的是yyyy-MM-dd格式，即date类型；用between and查询参数yyyy-MM-dd格式时，包含头尾，相当于x>=y && x<=z.
• 当是yyyy-MM-dd HH:mm:ss格式，即datetime类型；用between and查询参数yyyy-MM-dd  HH:mm:ss格式时，包含头尾，x>=y && x<=z。参数yyyy-MM-dd格式时，只包含头，相当于x>=y && x<z。）

查询参数格式与数据库类型相对应时，between and包含头尾，否则依情况

case when 自定义排序时的使用

CASE 
    WHEN t2.status = 4
    AND t2.expire_time>UNIX_TIMESTAMP()
    AND t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 DAY)) THEN 4
        WHEN `status` = 2  THEN 3
        WHEN `status` = 3 THEN 2
        WHEN t2.status = 4
        AND t2.expire_time>UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 60 DAY))
        AND t2.expire_time<UNIX_TIMESTAMP(DATE_ADD(NOW(),INTERVAL 1 YEAR)) THEN 1
    ELSE 0
    END sort     
$query->orderBy('sort desc ,t2.expire_time desc,t2.created_at desc');

当colume 与condition 条件相等时结果为result

case colume 
    when condition then result
    when condition then result
    when condition then result
else result
end

当满足某一条件时，执行某一result

case  
    when condition then result
    when condition then result
    when condition then result
else result
end