mysql笔记----持续更新

DISTINCT  嵌套使用

	'2021-02' AS '年月',
	count(DISTINCT uid) AS '充值人数',
	count(1) AS '充值次数',
	sum(`price`) AS '充值金额',
	count(DISTINCT pay_uid),
	count(DISTINCT IF(uid = pay_uid, null, pay_uid)) AS '好友充值人数',
 sum(IF(uid = pay_uid, 0, 1)) AS '好友充值次数',
 sum(IF(uid = pay_uid, 0, `price`)) AS '好友充值金额'

nickname like 'ha123%'  0.3s 支持大小写 不支持中间搜索
MATCH(nickname) AGAINST ('ha123') 在   0.5s  不支持大小写 支持中间搜索

 

重启mysql

service mysql restart

------------------------查找mysql配置文件  linux-------------------------------------

 /usr/local/mysql/bin/mysqld --verbose --help |grep -A 1 'Default options'

一般在

/etc/my.cnf

-----------------------报错1055---------------------------------------

mysql   [Err] 1055 - Expression #1 o********************

set @@GLOBAL./usr/local/mysql/bin/mysqld --verbose --help |grep -A 1 'Default options'='';
set sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';

 

------------------------------------------------------

mysql遇上锁了 

 show  processlist;
SELECT * FROM information_schema.processlist;

kill 672735;

 

进入数据库

mysql -uroot -p

 

mysql重启服务

service mysql restart

 

mysql修改密码

set password for root@localhost = password('212544+-*');

 

//获取刚刚插入的id
LAST_INSERT_ID()+1


//获取数字时间   (linux和unix的时间) 参数可以选 '2018-01-03'
 UNIX_TIMESTAMP()


//获取时间
now()


//获取时间在一年的周数 参数是'2018-01-03'
YEARWEEK()


//将(linux和unix的数字时间) 转普通时间
FROM_UNIXTIME()

 

mysql给指定数据库添加用户 数据库分配用户

use mysql;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
ON db_name.*
TO 'user'@'host'
IDENTIFIED BY '密码';

/*立即生效*/
flush privileges

 

备份数据库

mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql

mysqldump -uroot ttt > /home/ttt.sql