mysql复习-来源考试

mysql复习-

No1 、登录和权限

（一）常用命令
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;

No2、常用查询---来源考试

#1和#2

建表和数据代码如下：

dept表

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;

employee表

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;

#3. 在employee表中，查询每个部门最高工资的员工信息。

select dept_no,max(e_salary) from employee group by dept_no;

#4. 查询员工BLAKE所在部门和部门所在地。

SELECT e.e_name,d.d_name,d.d_location FROM employee as e LEFT JOIN dept as d ON e.dept_no=d.d_no WHERE e.e_name='BLAKE';

#5. 使用连接查询，查询所有员工的部门和部门信息。

SELECT e.e_name,d.d_name,d.d_location FROM employee as e LEFT JOIN dept as d ON e.dept_no=d.d_no;

#6. 在employee表中，计算不同部门的平均工资。

SELECT dept_no,AVG(e_salary) from employee GROUP BY dept_no;

#7. 在employee表中，查询员工姓名以字母’A’或’S’开头的员工的信息。

SELECT * FROM employee WHERE e_name LIKE 'A%' OR e_name LIKE 'S%';
说明：SQL模糊匹配：%表示0到多个字符；_ 下划线表示一个字符。

#8. 在employee表中，将查询记录先按部门编号由高到低排列，再按员工工资由高到低排列。

SELECT * FROM employee ORDER BY dept_no DESC,e_salary DESC;

#9. 在employee表中，查询工资范围在800~2500之间的员工信息。

SELECT * FROM employee WHERE e_salary BETWEEN 800 and 2500;

#10. 在employee表中，查询到目前为止，工龄大于等于10年的员工信息。 (30分)

SELECT * FROM employee WHERE year(DATE(NOW()))-year(hireDate)>=10 ORDER BY hireDate DESC;

3、html课件

查询select基础

（1）一般查询

select * from 表名；

（2）查询指定列

select id, name from 表名；

（3）使用别名查询

select id as 序号, name as 姓名 from 表名；

或者：as可以省略，空格代替

select id 序号, name 姓名 from 表名；

查询的完整格式

SELECT select_expr [,select|_expr,...] [

FROM tb_name

[WHERE 条件判断]

[GROUP BY {col_name | postion} [ASC | DESC], ...]

[HAVING WHERE 条件判断]

[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]

[ LIMIT {[offset,]rowcount | row_count OFFSET offset}]

]

1 、查询select进阶

（一）select 字段

字段前加表名前缀：student.id student.name

字段as别名：name as 姓名（as可以省略，空格即可）

字段使用聚合函数：

为了快速得到统计数据，经常会用到如下5个聚合函数（属于mysql内置函数）

count（*）：select count(*) 查询记录条数

max（age）：select max（age）查询年龄最大值

min（age）：select min（age）查询年龄最小值

sum（age）：select sum（age）查询年龄总和

avg（age）：select avg（age）查询年龄平均值

注意：使用聚合函数时，字段名如果为关键字时，一定要加表名，如：hero.kill

其他常见mysql内置函数：

select concat(12,34,'ab'); 拼接字符串concat(str1,str2...)

left(str,len) 截取字符串返回字符串str的左端len个字符，一个汉字为一个。如：left(name,1) 截取姓。

select floor(2.3); 地板floor(n)，表示不大于n的最大整数

select ceiling(2.3); 天花板ceiling(n)，表示不小于n的最大整数

select round(1.6); 求四舍五入值round(n,d)，n表示原数，d表示小数位置，默认为0。如：select round(1.6666,2) 保留两位小数。格式化输出。

（二）from 表

多表查询：from student，teacher

多表别名：from student as s， teacher as t （as可以省略，空格即可）

（三）where 条件

where后面支持多种运算符，进行条件的处理

比较运算符：id>10 id=10 id !=10

逻辑运算符：id>10 and age=18 | age not in(6, 12) | or

模糊查询：name like “ 张%” | name like “张_ _”

%表示任意多个任意字符

_表示一个任意字符

范围查询：

in 表示在一个非连续的范围内

例10：查询编号是1或3或8的学生

select * from students where id in(1,3,8);

between ... and ...表示在一个连续的范围内

例11：查询编号为3至8的学生

select * from students where id between 3 and 8;

空判断is null： age is null （必须用is来判空，=等号会不会报错，但是查不到null。）

优先级

（1）优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符。

（2）and比or先运算，如果同时出现并希望先算or，需要结合()使用。

（四）order by排序：只能按照整数值排序（注意：order by只能放在where的后面）

order by age （升序，正序）

order by age desc （降序，倒序）

order by age, height （先按照年龄，后按照升高排序。）

（五）limit分页：限制记录条数

limit 3 相当于limit 0, 3

limit 21, 10 取初次查询结果中，选取第21条开始，取10条记录。

注意：limit start，count中，start是从0开始计数的。

（六）group分组：按照某个字段分组后，就只能显示那一列了，还能显示统计数据。

分组已经不能再用where了，如果要使用条件，就用having。

小结：

group by 配 having

from 配 where

join 配 on

select gender from students group by gender;

select gender, count(*) from students group by gender;

select gender, count(*) from students group by gender having gender = 1;

对group分组排序

SELECT live_date,count(live_date) FROM `tvc_live` group by live_date order by live_date desc

延伸：

数据导入出现问题：情形一

删除(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

https://blog.csdn.net/lp1052843207/article/details/77528149?utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.no_search_link&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.no_search_link

posted @ 2018-05-04 00:11 安迪9468 阅读(322) 评论(0) 编辑收藏举报

刷新页面返回顶部

mysql复习-来源考试

公告