Mysql 学习笔记

1 组内排序取最大

select v.* from vacate v where v.id not in (
select v1.id from vacate v1
INNER JOIN vacate v2 on v1.userId=v2.userId where v1.id<v2.id and v2.userId=2
) and v.userId=2

之前出错因为 没有 在里面添加==2条件，在外面也要加上才行

2 备份查询

select ls.*,rs.regionname,lfs.* from (
select l.id locationid,l.lat,l.lng,l.address,l.description,l.regionid from location l where l.address like '%addres%'
) as ls
INNER JOIN(
select r.id as regionid, r.regionname from region r where FIND_IN_SET(1,r.ParentIds) or r.id=1
) as rs on ls.regionid=rs.regionid
INNER JOIN(
select lf.liftnumber,lf.brand,lf.batchno,lf.installtime,lf.description,lf.modelid,lf.planid, lf.status,lf.locationid from lift lf where lf.liftnumber like '%00%' and (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(lf.installtime))/(24*60*60*365) BETWEEN 0 and 5
) as lfs on lfs.locationid=ls.locationid
where EXISTS (
select mpt.planid,mpt.scheduletype from mtcplan mp INNER JOIN mtcplantask mpt on mp.id=mpt.planid where mpt.planid=lfs.planid and FIND_IN_SET(mpt.scheduletype,'2,6')
)

select ls.*,rs.regionname,GROUP_CONCAT(CONCAT(lfs.brand,lfs.liftnumber,lfs.description) SEPARATOR '@@') from (
select l.id locationid,l.lat,l.lng,l.address,l.description,l.regionid from location l where l.address like '%addres%'
) as ls
INNER JOIN(
select r.id as regionid, r.regionname from region r where FIND_IN_SET(1,r.ParentIds) or r.id=1
) as rs on ls.regionid=rs.regionid
INNER JOIN(
select lf.liftnumber,lf.brand,lf.batchno,lf.installtime,lf.description,lf.modelid,lf.planid, lf.status,lf.locationid from lift lf where lf.liftnumber like '%00%' and (UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(lf.installtime))/(24*60*60*365) BETWEEN 0 and 5
) as lfs on lfs.locationid=ls.locationid
where EXISTS (
select mpt.planid,mpt.scheduletype from mtcplan mp INNER JOIN mtcplantask mpt on mp.id=mpt.planid where mpt.planid=lfs.planid and FIND_IN_SET(mpt.scheduletype,'2,6')
) group by ls.locationid

3

一般数据库连接 后缀
?serverTimezone=UTC&zeroDateTimeBehavior=convertToNull

4 mysql 数值的范围

int 4字节 2* 4*8-1 ==2的31次方
bigint 就是 8字节

 5 linux 搭建5.7的方法mysql

-================================================
使用yum进行安装
wget http://dev.mysql.com/get/mysql57-community-release-el7-8.noarch.rpm

安装
yum localinstall mysql57-community-release-el7-8.noarch.rpm

检查 太慢不需要检查
yum repolist enabled | grep "mysql.*-community.*"

vim /etc/yum.repos.d/mysql-community.repo
修改 需要5.5/5.6/5.7 设置为enable状态

yum install mysql-community-server

systemctl start mysqld

systemctl status mysqld

在/var/log/mysqld.log 生成了密码为root密码
获取临时密码
grep 'temporary password' /var/log/mysqld.log

set password for 'root'@'localhost'=password('MyNewPass4!');
Etybf5fwHK&I
set password for 'root'@'localhost'=password('xxxxxxx');
======================================

检查是否存在mysql
rpm -qa | grep mysql

如果有写在以前的 msyql
rpm -e 已经存在的MySQL全名

UPDATE user SET password=PASSWORD('123456') WHERE user='root';
flush privileges;

grant all privileges on *.* to root@"%" identified by ".";

-----------
新建用户并授权
CREATE USER 'username'@'%' IDENTIFIED BY '11111sssss';
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

source /home/ec2-user/xxxxx.sql

 5 高效的组内排序

2 mysql 性能优化，
组内排序 not exist 速度非常的慢，另外一个非常的快
慢的
select li.* from locationinfo li where not EXISTS
(select * from locationinfo lii where li.userid=lii.userid and lii.id>li.id)

快的
select l.* from (select * from locationinfo where mapType =3 and userid is not null order by lastupdatetime desc

6 新建立了用户后要给新数据库给新用户再次授权时 授权对指定数据库的

新建用户和编辑权限和查看的方法

在 tool -add user 目录下

demo是用户名 demodb是数据库

　　 mysql>grant all privileges on demoDB.* to demo@localhost identified by '1234';

 　　mysql>flush privileges;//刷新系统权限表