Mysql

mysql 全部数据导出导入

导出导入数据库导出所有数据库 mysqldump -u root -p --host=127.0.0.1 --all-databases > all_dump.sql 导入所有数据库 mysql -u root -p --host=127.0.0.1 < all_dump.sql 数据库免费软件 dbeaver-community brew install --cask dbeaver-community https://formulae.brew.sh/cask/dbeaver-community navicat-premium-lite brew install --cask navicat-premium-lite https://formulae.brew.sh/cask/navicat-premium-lite

Connect Mysql8 Error

服务以前连接mysql5.7,现在连接mysql8报错 code: 'ER_NOT_SUPPORTED_AUTH_MODE', errno: 1251, sqlState: '08004', sqlMessage: 'Client does not support authentication protocol requested by server; consider upgrading MySQL client' 原因： mysql5.7 认证默认是mysql_native_password，mysql8默认是caching_sha2_password 解决方式：创建数据库时，指明mysql_native_password docker run -d --name mysql8 -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -v mysql8:/var/lib/mysql mysql:8.0 --default-authentication-plugin=mysql_native_password 修改数据库，将用户认证的caching_sha2_password改为mysql_native_password # $ mysql -u root -p use mysql; select host,user,plugin,authentication_string from mysql.user; ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password'; # ... 如果还有其他USER，把其他USER(mysql.sys,mysql.session,mysql.infoschema)也替换 flush privileges; 升级客户端，支持最新的认证 reference: https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

linux安装mariadb并配置phpmyadmin

mariadb 维基百科 MariaDB是MySQL关系数据库管理系统的一个复刻，由社区开发，有商业支持，旨在继续保持在GNU GPL下开源。MariaDB的开发是由MySQL的一些原始开发者领导的，他们担心甲骨文公司收购MySQL后会有一些隐患。 linux现在默认的MySQL是mariadb。安装mariadb $ sudo apt-get install mariadb-server mariadb-client 新建用户授权以root身份输入mariadb，进入mariadb # mariadb > use mysql; # 进入mysql数据库 mysql> CREATE USER 'zhang'@'localhost' IDENTIFIED BY '123456'; # zhang是用户名,123456是密码 mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhang'@'localhost' WITH GRANT OPTION; # 授权 mysql> FLUSH PRIVILEGES; 或者(推荐下边的，可以登录，上边只能本地登录) mysql> CREATE USER 'zhang'@'%' IDENTIFIED BY '123456'; mysql> GRANT ALL PRIVILEGES ON *.* TO 'zhang'@'%' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; 查看user信息 > select user,host,password from user; 至此，可以用新建的用户登录了。 ...

mysql高级排序

表和数据 CREATE TABLE `sqltest` ( `id` int(11) NOT NULL, `name` varchar(45) NOT NULL, `age` int(11) NOT NULL, `score` int(11) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `id_UNIQUE` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `sqltest` VALUES (1,'土豆',18,100),(2,'地瓜',18,100),(3,'李斯',20,60),(4,'韩非',20,60),(5,'荀子',50,90),(6,'君莫笑',22,80),(7,'寒烟柔',21,80),(8,'包子',26,30),(9,'剑圣',30,0); 排名(row_number) 按分数排序，拿到名次(成绩相同排名不并列) SQL: use sqltest; select st.*,(@rownum:=@rownum+1) as rownum from sqltest st,(select @rownum:=0) potato order by score; 结果：解释：设置一个伪列@rownum，从1开始计数，依次加1;(select @rownum:=0) potato ,一定要起别名注意：这是从小到大排；如果需要从大到小的话，order by score desc。排名(Rank) 按分数排序，拿到名次(成绩相同排名并列，有跳数,比如2个100，1个99，排名就是1,1,3) SQL: use sqltest; select st.*,@rownum:=@rownum+1 as rownum, @curnum:=(case when @prescore=st.score then @curnum else @rownum end) as ScoreRank, @prescore:=st.score as prescore from sqltest st,(select @rownum:=0,@curnum:=0,@prescore:=null) potato order by st.score desc; 结果： ...