12-27週報:MySQLクエリー
54463 ワード
MySQLクエリー
1.よく使うMySQL関数
2.ソートクエリー(order by) order byクエリーの結果をソートします. デフォルトの昇順、desc降順、asc降順.
3.グループクエリー()クエリーのフィールドを祖先化し、フィールド値が同じグループに分けます.ただし、各グループの第1のデータ のみを印刷する.は、まずグループ化してからソートすることができるが、先にソートすることはできない .パケット化後に条件を追加する必要がある場合はhaving を使用する必要がある.
4.サブクエリある文のクエリ結果は、別の文のクエリ条件として を使用する.サブクエリはクエリの内側にある必要があり、サブクエリ全体のプロシージャは()で囲まれている サブクエリは、GROUP BY、ORDER BY、LIMIT、関数などの を含むことができる.サブクエリの外層クエリは、SELECT、INSERT、UPDATE SET、DOなどの であってもよいサブクエリが値を返さない場合に使用する必要があります: 1.>any(サブクエリ結果のいずれかより大きい==最小値より大きい)2.3.>all(サブクエリ結果のすべてのデータ=最大値より大きい)4.5.=any(サブクエリのいずれかの結果に等しい)
5.ページングクエリ(limit)
6.多表連査 inner join(内部接続、または等値接続):2つのテーブルのフィールドが一致するレコードを取得します. left join(左接続):右に一致するデータがなくても、左のテーブルのすべてのデータを取得します. right join(右接続):左側に対応するデータがなくても、右側のすべてのデータを取得します.
7.ファジイクエリ「%」(任意の文字)を使用して「」を使用(任意の文字)
練習問題
1.よく使うMySQL関数
1.
#
rand()// 0~1
SELECT rand();
#
truncate( ， ) ;
SELECT TRUNCATE(money,0) FROM book ;
#
round();
SELECT ROUND(money) FROM book ;
2.
：
SELECT DATE_FORMAT(now(),'%y %m %h:%i:%s')
now();// +
curdate();//
curtime();//
3.
(1):
ifnull( ， / ) ， ‘，’ 。
：
select sal+ifnull(comm,0) from emp;
(2):
case
when then " "
when then " "
when then " "
else " "
end
：
SELECT
ename,
sal,
CASE
WHEN sal<=2000 THEN '1 '
WHEN sal>2000 THEN '2 '
WHEN sal>=3000 THEN '3 '
else ' '
END " "
FROM emp;
4.
(1)min() //
(2)max() //
(3)sum() //
(4)avg() //
(5)count() //
2.ソートクエリー(order by)
order by
SELECT * FROM student order by id ;( )
SELECT * FROM student order by id desc;( )
SELECT * FROM student order by id asc;( )
3.グループクエリー()
SELECT * FROM student GROUP BY sex;
SELECT * FROM student GROUP BY sex ORDER BY id ;
SELECT * FROM student GROUP BY sex HAVING age > 18;
4.サブクエリ
1.SELECT sal FROM emp WHERE ename=' '; //
2.SELECT * FROM emp WHERE sal>1600;//
：
# ' ' 。
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=' ')
(ANY，ALL):
# 。
SELECT `dname` FROM dept WHERE deptno = ANY ( SELECT deptno FROM emp GROUP BY deptno);
# 30 。
SELECT ename ,sal FROM emp WHERE sal> ALL(SELECT sal FROM emp WHERE emp.deptno=30);
5.ページングクエリ(limit)
SELECT * FROM student WHERE id limit 1,1;
（ 0 ）
6.多表連査
SELECT * FROM student INNER JOIN teacher ON s_id = t_id;
SELECT * FROM student LEFT JOIN teacher ON s_id = t_id;
SELECT * FROM student RIGHT JOIN teacher ON s_id = t_id;
7.ファジイクエリ
SELECT * FROM student WHERE name LIKE " % _ "
練習問題
#
CREATE TABLE dept ( deptno INT PRIMARY KEY auto_increment COMMENT ' ', dname VARCHAR ( 14 ), #
loc VARCHAR ( 13 ) #
);#
CREATE TABLE emp (
empno INT PRIMARY KEY auto_increment,#
ename VARCHAR ( 10 ),#
job VARCHAR ( 9 ),#
mgr INT,#
hiredate date,# ，
sal INT,#
comm INT,#
deptno INT NOT NULL,#
FOREIGN KEY ( deptno ) REFERENCES dept ( deptno )
);
INSERT INTO dept
VALUES
( 10, ' ', ' ' );
INSERT INTO dept
VALUES
( 20, ' ', ' ' );
INSERT INTO dept
VALUES
( 30, ' ', ' ' );
INSERT INTO dept
VALUES
( 40, ' ', ' ' );
INSERT INTO emp
VALUES
( 7369, ' ', ' ', 7902, '1980-12-17', 800, NULL, 20 );
INSERT INTO emp
VALUES
( 7499, ' ', ' ', 7698, '1981-02-20', 1600, 300, 30 );
INSERT INTO emp
VALUES
( 7521, ' ', ' ', 7698, '1981-02-22', 1250, 500, 30 );
INSERT INTO emp
VALUES
( 7566, ' ', ' ', 7839, '1981-04-02', 2975, NULL, 20 );
INSERT INTO emp
VALUES
( 7654, ' ', ' ', 7698, '1981-09-28', 1250, 1400, 30 );
INSERT INTO emp
VALUES
( 7698, ' ', ' ', 7839, '1981-05-01', 2850, NULL, 30 );
INSERT INTO emp
VALUES
( 7782, ' ', ' ', 7839, '1981-06-09', 2450, NULL, 10 );
INSERT INTO emp
VALUES
( 7788, ' ', ' ', 7566, '1987-06-13', 3000, NULL, 20 );
INSERT INTO emp
VALUES
( 7839, ' ', ' ', NULL, '1981-11-17', 5000, NULL, 10 );
INSERT INTO emp
VALUES
( 7844, ' ', ' ', 7698, '1981-09-08', 1500, 0, 30 );
INSERT INTO emp
VALUES
( 7876, ' ', ' ', 7788, '1987-06-13', 1100, NULL, 20 );
INSERT INTO emp
VALUES
( 7900, ' ', ' ', 7698, '1981-12-03', 950, NULL, 30 );
INSERT INTO emp
VALUES
( 7902, ' ', ' ', 7566, '1981-12-03', 3000, NULL, 20 );
INSERT INTO emp
VALUES
( 7934, ' ', ' ', 7782, '1983-01-23', 1300, NULL, 10 );
#1． 。
SELECT `dname` FROM dept WHERE deptno = ANY ( SELECT deptno FROM emp GROUP BY deptno);
#2． " " 。
SELECT ename FROM emp WHERE sal >(SELECT sal FROM emp WHERE ename=' ');
#3. 。
SELECT ename,(SELECT ename FROM emp e2 WHERE e1.mgr =e2.empno) mname FROM emp e1 ;
# 4． 。
SELECT ename FROM emp e1 WHERE hiredate<(SELECT hiredate FROM emp e2 WHERE e1.mgr =e2.empno);
# 5． ， 。
SELECT dname ,empno,ename,job,sal FROM dept LEFT JOIN emp ON dept.deptno=emp.deptno ;
# 6． job “ ” 。
SELECT ename ,dname FROM emp LEFT JOIN dept ON dept.deptno=emp.deptno WHERE job=' ';
#7． 1500 。
SELECT job FROM emp GROUP BY emp.job HAVING MIN(emp.sal)>1500;
# 8． " " ， 。
SELECT ename FROM emp LEFT JOIN dept ON dept.deptno=emp.deptno WHERE dept.dname=' ';
# 9． 。
SELECT ename FROM emp e1 WHERE e1.sal> (SELECT AVG(e2.sal) FROM emp e2);
# 10． " " 。
SELECT ename FROM emp e1 WHERE e1.job=(SELECT job FROM emp e2 WHERE ename=' ');
# 11． 30 。
SELECT ename ,sal FROM emp e1 WHERE e1.sal =ANY(SELECT e2.sal FROM emp e2 WHERE e2.deptno=30);
#12． 30 。
SELECT ename ,sal FROM emp WHERE sal> ALL(SELECT sal FROM emp WHERE emp.deptno=30);
#13． 、 。
SELECT dname ,(SELECT COUNT(ename) FROM emp WHERE emp.deptno=dept.deptno),(SELECT AVG(emp.sal) FROM emp WHERE emp.deptno=dept.deptno)FROM dept;
#14． 、 。
SELECT ename , dname , sal FROM emp LEFT JOIN dept ON emp.deptno=dept.deptno ;
#15． 。
SELECT dname, loc ,(SELECT COUNT(ename) FROM emp WHERE dept.deptno=emp.deptno) FROM dept;
# 16． 。
SELECT job,(SELECT MIN(sal) FROM emp e2 WHERE e2.job=e1.job) FROM emp e1 GROUP BY job;
#17．
SELECT dname,(SELECT MIN(sal) FROM emp WHERE emp.job=' 'AND dept.deptno=emp.deptno) FROM dept;
#18． , 。
SELECT ename ,sal*12 FROM emp ORDER BY sal*12 ASC;
#19. emp 3000 （ 3000） 、 、
SELECT empno,ename,sal FROM emp WHERE sal>3000 OR sal=3000;
# 20. ' ' 。
SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename=' ')
# 21. emp 20， 2000 （ 2000） ， ， ， ：
SELECT empno,ename,sal FROM emp WHERE sal>2000 AND emp.deptno=20;
# 22. emp （ ）
SELECT job FROM emp GROUP BY job;
# 23. （comm） 。
SELECT * FROM emp WHERE comm IS NOT NULL
# 24. 800 2500 （ ） 。（ ： and between and）
SELECT * FROM emp WHERE sal>800 AND sal<2500;
# 25. 7521，7900，7782 。（ ： ，or in）
SELECT * FROM emp WHERE empno IN (7521 ,7900 ,7782)
# 26. “ ” ， 1000 （ 1000） 。
SELECT * FROM emp WHERE ename LIKE '% %' AND sal>1000;
# 27. “ ” 。
SELECT * FROM emp WHERE ename LIKE '_ _';
# 28. ， 。
SELECT * FROM emp ORDER BY sal ASC, emp.hiredate DESC
# 29. ， 。 order by convert(name using gbk) asc;
SELECT * FROM emp ORDER BY CONVERT(ename USING gbk) ASC ,sal DESC
# 30. 、 。
SELECT* FROM emp WHERE emp.hiredate = (SELECT MIN(emp.hiredate) FROM emp )
#31. 、 、 ， ， 100.
SELECT ename, sal, comm FROM emp
# 32. 。
SELECT job FROM emp WHERE sal =(SELECT MAX(sal) FROM emp)
#33. emp ， 10 。
SELECT emp.deptno, MAX(sal), MIN(sal) FROM emp GROUP BY deptno HAVING deptno<>10 ;
# 34. 10 。
DELETE FROM emp WHERE deptno=10 AND sal = (SELECT max FROM ( SELECT MAX(sal) max FROM emp e1 WHERE deptno=10) s);
# 35. 30%。
UPDATE emp SET sal=sal*0.7 WHERE sal= (SELECT max FROM (SELECT MAX(sal) max FROM emp) f);
#36. ， ( >=3000 3 ， >2000 2 ， <=2000 1 )
# ：case when ... then ... when ... then ... else ... end
SELECT
ename,
sal,
CASE
WHEN sal<=2000 THEN '1 '
WHEN sal>2000 THEN '2 '
WHEN sal>=3000 THEN '3 '
else ' '
END " "
FROM emp;