mysql复习-来源考试
(一)常用命令
1、登录mysql
mysql -h localhost -u root -p
2、重启mysql
service mysql restart
延伸:
service XXX start 启动服务
service XXX stop 停止服务
service XXX restart 重启服务
service XXX status 查看服务状态
3、更改root密码
(1)mysqladmin -u root password '123' -p
(2)输入旧密码:XXXMMM
(3)刷新权限:flush privileges;
延伸:刷权
2中情况需要刷新权限:①修改密码;②手工改表
重启mysql有刷新权限的功能。
4、添加新mysql的用户
root用户登入
grant 权限列表 on 库.表 to “用户名”@“访问主机” identified by “密码”
4个例子:
(1)创造一个mysql用户andy1,密码123,权限全开,可以在任何ip登录,访问任何数据库
grant all privileges on *.* to andy1@'%' identified by '123';
(2)创造一个mysql用户andy2,密码123,权限全开,可以在任何ip登录,只能访问数据库python_test下的所有表
grant all privileges on python_test.* to andy2@'%' identified by '123';
(3)创造一个mysql用户andy3,密码123,权限全开,可以在任何ip:10.1.2.XX和10.1.50.XX网段登录,只能访问数据库python_test下的所有表
grant all privileges on python_test.* to andy3@'10.1.2.%' identified by '123';
grant all privileges on python_test.* to andy3@'10.1.50.%' identified by '123';
说明:用命令新建用户,不用刷权。
5、删除mysql 用户
方法1:命令删(强烈推荐)
(1)root登入mysql
(2)drop user "andy1"@"%"
%表示任何IP位置可以登录mysql,IP必须与创建时的一致,否则无法删除。
方法2:表中删,需要刷权
(1)root登入mysql
(2)进入mysql库
(3)进入user表,db表
(4)删除and1、%的相关记录,从表中删除
(5)刷新权限:flush privileges;
延伸:查看用户名和登录IP限制
root登入mysql
use mysql
select host, user from user;
(二)常见问题:
1、root远程登录mysql不成功。可能是没有远程接入的权限。
设置远程登录权限:改表法
(1)root用户本地登入:mysql -u root -p
(2)进入mysql库:use mysql
(3)进入user表:select user, host from user;
(4)设置IP登入权限:update user set host="%" where user="root";
(5)刷新权限:flush privileges;
2、如何查看mysql有多少用户
(1)root登入mysql:mysql -u root -p
(2)进入mysql库:use mysql
(3)进入user表:select user, host from user;
3、如何限制mysql用户的登入IP段
创造一个mysql用户andy3,密码123,权限全开,可以在任何ip:10.1.2.XX和10.1.50.XX网段登录,只能访问数据库python_test下的所有表
grant all privileges on python_test.* to andy3@'10.1.2.%' identified by '123';
grant all privileges on python_test.* to andy3@'10.1.50.%' identified by '123';
相等于新建2个不同网段的同名用户
延伸:mysql支持同名用户多人同时使用。
4、如何查询最后登入mysql的实际IP
tracert mysql所在IP地址
结果中最靠近mysql所在IP地址的IP为最后到达mysql的IP
5、如何远程mysql
mysql -h mysql所在IP地址 -u root -p
延伸:小技巧——通用建表语句:id、创建时间、更新时间、其他
DROP TABLE IF EXISTS `douban_awards_page`; CREATE TABLE `douban_awards_page` ( `id` int UNSIGNED NOT NULL AUTO_INCREMENT, `media_id` bigint UNSIGNED NOT NULL COMMENT '媒资id', `media_html` mediumtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL COMMENT '网页内容', `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = DYNAMIC;
DROP TABLE IF EXISTS `dept`; CREATE TABLE `dept` ( `d_no` int(11) NOT NULL AUTO_INCREMENT, `d_name` varchar(50) DEFAULT NULL, `d_location` varchar(100) DEFAULT NULL, PRIMARY KEY (`d_no`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of dept -- ---------------------------- INSERT INTO `dept` VALUES ('10', 'ACCOUNTING', 'ShangHai'); INSERT INTO `dept` VALUES ('20', 'RESEARCH ', 'BeiJing '); INSERT INTO `dept` VALUES ('30', 'SALES ', 'ShenZhen '); INSERT INTO `dept` VALUES ('40', 'OPERATIONS ', 'FuJian '); SET FOREIGN_KEY_CHECKS=1;
DROP TABLE IF EXISTS `employee`; CREATE TABLE `employee` ( `e_no` int(11) NOT NULL, `e_name` varchar(100) NOT NULL, `e_gender` char(2) NOT NULL, `dept_no` int(11) NOT NULL, `e_job` varchar(100) NOT NULL, `e_salary` smallint(6) NOT NULL, `hireDate` date DEFAULT NULL, KEY `dept_no` (`dept_no`), CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`d_no`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of employee -- ---------------------------- INSERT INTO `employee` VALUES ('1001', 'SMITH', 'm', '20', 'CLERK', '800', '2005-11-12'); INSERT INTO `employee` VALUES ('1002', 'ALLEN', 'f', '30', 'SALESMAN', '1600', '2003-05-12'); INSERT INTO `employee` VALUES ('1003', 'WARD', 'f', '30', 'SALESMAN', '1250', '2003-05-12'); INSERT INTO `employee` VALUES ('1004', 'JONES', 'm', '20', 'MANAGER', '2975', '1998-05-18'); INSERT INTO `employee` VALUES ('1005', 'MARTIN', 'm', '30', 'SALESMAN', '1250', '2001-06-12'); INSERT INTO `employee` VALUES ('1006', 'BLAKE', 'f', '30', 'MANAGER', '2850', '1997-02-15'); INSERT INTO `employee` VALUES ('1007', 'CLARK', 'm', '10', 'MANAGER', '2450', '2002-09-12'); INSERT INTO `employee` VALUES ('1008', 'SCOTT', 'm', '20', 'ANALYST', '3000', '2003-05-12'); INSERT INTO `employee` VALUES ('1009', 'KING', 'f', '10', 'PRESIDENT', '5000', '1995-01-01'); INSERT INTO `employee` VALUES ('1010', 'TURNER', 'f', '30', 'SALESMAN', '1500', '1997-10-12'); INSERT INTO `employee` VALUES ('1011', 'ADAMS', 'm', '20', 'CLERK', '1100', '1999-10-05'); INSERT INTO `employee` VALUES ('1012', 'JAMES', 'm', '30', 'CLERK', '950', '2008-06-15'); SET FOREIGN_KEY_CHECKS=1;
说明:SQL模糊匹配:%表示0到多个字符;_ 下划线表示一个字符。
![](https://img2020.cnblogs.com/blog/552284/202005/552284-20200503120014253-1871463481.png)
删除(6)就正常了。
=========================
mysql 高级用法
1、学科内排名
rank() over,dense_rank() over,row_number() over的区别
https://www.cnblogs.com/scwbky/p/9558203.html
2、hive高级:列转行
collect_list和collect_set
https://blog.csdn.net/weixin_35414260/article/details/113906282
https://www.cnblogs.com/cc11001100/p/9043946.html
3、数值转字串
concat_ws("、",collect_set(xxx) )
https://blog.csdn.net/qq_35211818/article/details/78977276
待验证:
sql分类查询取前3