解决方法 :给予权限,执行 “chmod 775 /usr/local/mysql/ -R” 然后重新启动mysqld。
2、原因:可能进程里已经存在mysql进程;
解决方法:用命令“ps -ef|grep mysqld”查看是否有mysqld进程,如果有使用“kill -9 进程号”杀死,然后重新启动mysqld。
-- 查询分类名称为手机数码的所有商品1.查询分类名为手机数码的ID
SELECT cid FROM category WHERE cname='手机数码'
2.得出ID为1的结果
SELECT * FROM product WHERE cno = (SELECT cid FROM category WHERE cname='手机数码')
子查询练习:
子查询练习及答案:
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2),
comm DECIMAL(7,2),
deptno INT
)
INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20)
INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30)
INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30)
INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20)
INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30)
INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30)
INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10)
INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20)
INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10)
INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30)
INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20)
INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30)
INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20)
INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10)
INSERT INTO emp VALUES(7981,'MILLER','CLERK',7788,'1992-01-23',2600,500,20)
CREATE TABLE dept(
deptno INT,
dname VARCHAR(14),
loc VARCHAR(13)
)
INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK')
INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS')
INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO')
INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON')
--最高工资
SELECT MAX(sal) FROM emp
--最少工资
SELECT MIN(sal) FROM emp
--最高工资的员工信息
SELECT * FROM emp WHERE sal = (SELECT MAX(sal) FROM emp)
--最低工资的员工信息
SELECT * FROM emp WHERE sal = (SELECT MIN(sal) FROM emp)
-- 单行子查询(><>= <= = <>)
1.10号部门的平均工资
SELECT AVG(sal) FROM emp WHERE deptno = 10
2. 高于上面结果员工信息
SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno = 10)
-- 多行子查询(in not in any all)>any >all
-- 查询出比10号部门任何员工薪资高的员工信息
1. 查询出10号部门最高工资
SELECT MAX(sal) FROM emp WHERE deptno = 10
2. 得出结果
SELECT * FROM emp WHERE sal >(SELECT MAX(sal) FROM emp WHERE deptno = 10)
-- 查询出比10号部门任意一个员工薪资高的所有员工信息 : 只要比其中随便一个工资都可以
SELECT sal FROM emp WHERE deptno = 10
-- 多列子查询(实际使用较少) in
-- 和10号部门同名同工作的员工信息
1. 查询出10号部门所有人 名字和工作
SELECT ename,job FROM emp WHERE deptno=10
2. 得出结果
SELECT * FROM emp WHERE (ename,job) IN (SELECT ename,job FROM emp WHERE deptno=10) AND deptno !=10
-- Select后面接子查询
-- 获取员工的名字和部门的名字
SELECT ename,deptno FROM emp
-- from后面接子查询
-- 查询emp表中所有管理层的信息
SELECT DISTINCT mgr FROM emp
-- where 接子查询
-- 薪资高于10号部门平均工资的所有员工信息
1. 10号部门平均工资
SELECT AVG(sal) FROM emp WHERE deptno=10
2. 得出结果
SELECT * FROM emp WHERE sal >(SELECT AVG(sal) FROM emp WHERE deptno=10)
-- having后面接子查询
-- 有哪些部门的平均工资高于30号部门的平均工资
1. 统计所有的部门的平均工资
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno
2. 30号部门的平均工资
SELECT AVG(sal) FROM emp WHERE deptno=30
3.得出结果:
SELECT deptno, AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal) >(SELECT AVG(sal) FROM emp WHERE deptno=30)
-- 列出达拉斯加工作的人中,比纽约平均工资高的人
原文链接: https://blog.csdn.net/mqingo/article/details/84679590
1、单列排序SELECT * FROM test1 ORDER BY date_time
默认升序,降序后面接"DESC"即可。
2、多列排序
SELECT * FROM test1 ORDER BY `status`, date_time DESC
首先按`status`字段排序,若`status`相等,则按data_time排序。
3、自定义排序
SELECT * FROM test1 ORDER BY FIELD(`status`, 3, 2, 4, 1, 5), date_time DESC
使用"FIELD()"函数,可指定顺序。
4、其他条件排序
先按大于等于当前时间升序,再按小于当前时间降序,支持分页。
SELECT * FROM test1 ORDER BY date_time <NOW(), IF(date_time <NOW(), 0, date_time), date_time DESC
附加SQL脚本:
CREATE TABLE `test1` (`id` bigint(20) NOT NULL AUTO_INCREMENT,
`name` varchar(50) NOT NULL,
`date_time` datetime NOT NULL,
`status` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8
INSERT INTO `test1` VALUES
(NULL, '测试1', '2018-03-05 11:09:00', 1),(NULL, '测试2', '2018-03-06 11:09:00', 1),(NULL, 'abc', '2018-03-07 11:09:00', 1),
(NULL, 'def', '2018-04-08 11:09:00', 2),(NULL, '李某某', '2018-04-17 11:09:00', 1),(NULL, '饭某某', '2018-04-20 13:09:00', 2),
(NULL, '赵', '2018-04-20 01:09:00', 4),(NULL, '倩', '2018-04-28 11:09:00', 2),(NULL, 'andy', '2018-04-30 11:09:00', 1),
(NULL, 'tony', '2018-05-08 11:09:00', 4),(NULL, 'tom', '2018-05-07 11:09:00', 3),(NULL, 'bill', '2018-05-18 11:09:00', 3),
(NULL, 'james', '2018-06-07 11:09:00', 4),(NULL, 'anthony', '2018-06-18 11:09:00', 2),(NULL, '盖茨', '2018-04-21 11:09:00', 1),
(NULL, '部长', '2018-04-24 11:09:00', 4),(NULL, '李总', '2018-04-20 11:09:00', 5),(NULL, '张总', '2018-04-29 11:09:00', 2),
(NULL, '王总', '2018-04-19 11:09:00', 3),(NULL, '唐总', '2018-05-01 11:09:00', 2)
参考的这篇文档Mysql排序方式
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)