学习MySQL之单表操作（二）
时间：2021-07-01 10:21:17
帮助过：13人阅读
CREATE TABLE t_employee(
empno INT(
11),
ename VARCHAR(
20),
job VARCHAR(
40),
MGR INT(
11),
Hiredate DATE DEFAULT ‘0000-00-00‘,
sal DOUBLE(
10,
2),
comm DOUBLE(
10,
2),
deptno INT(
11),
UNIQUE INDEX uk_empno(empno) ##使用唯一索引
);
##增
INSERT INTO t_employee VALUE(
7369,
‘SMITH‘,
‘CLERK‘,
7902,
‘1981-03-12‘,
800.00,
NULL,
20),
(7499,
‘ALLEN‘,
‘SALESMAN‘,
7698,
‘1982-03-12‘,
1600,
300,
30),
(7521,
‘WARD‘,
‘SALESMAN‘,
7698,
‘1982-03-12‘,
1250,
500,
30),
(7566,
‘JONES‘,
‘MANAGER‘,
7839,
‘1981-03-12‘,
2975,
NULL,
20),
(7654,
‘MARTIN‘,
‘SALESMAN‘,
7698,
‘1981-03-12‘,
1250,
1400,
30),
(7698,
‘BLAKE‘,
‘MANAGER‘,
7839,
‘1981-03-12‘,
3000,
NULL,
10),
(7902,
‘FORD‘,
‘ANALYST‘,
7566,
NULL,
3000,
NULL,
20),
(7934,
‘CLERK‘,
‘SALESMAN‘,
7782,
‘1989-03-12‘,
1300,
NULL,
10);
(7938,
‘SMILL‘,
‘ANALYST‘,
7722,
‘1986-03-12‘,
1300,
100,
40);
##删
DELETE FROM t_diary;
SELECT * FROM t_diary;
##改
UPDATE t_employee
SET Hiredate
=‘1985-3-12‘ WHERE empno
=7902;
UPDATE t_employee
SET comm
=0.00 WHERE comm
IS NULL;
##查
SELECT * FROM t_employee;
##查1:去除重复项
SELECT DISTINCT job
FROM t_employee ;
##查2：统计
SELECT COUNT(
*)
AS COUNT FROM t_employee;
##查3：计算 (年薪)
SELECT empno,ename,(sal
*12+comm)
AS yearSal
FROM t_employee;
##查4：设置格式显示，可拼接字符串，字段
SELECT ename
AS ‘雇员‘, CONCAT(
‘年薪为‘,sal
*12+comm)
AS ‘年薪‘ FROM t_employee;
##查5：运用关键字 Like 模糊查询,通配符，条件符
SELECT * FROM t_employee
WHERE ename
LIKE ‘_a%‘;
SELECT * FROM t_employee
WHERE ename
NOT LIKE ‘_A%‘;
SELECT * FROM t_employee
WHERE Hiredate
BETWEEN ‘1900-1-1‘ AND ‘1981-3-12‘;
##查6：使用关键字 in 范围查询
SELECT * FROM t_employee
WHERE empno
IN (
7499,
7521,
7902);
SELECT * FROM t_employee
WHERE empno
NOT IN (
7499,
7521,
7902);
##查7：排序查询
SELECT * FROM t_employee
ORDER BY empno
ASC; ##正序
SELECT * FROM t_employee
ORDER BY sal
DESC; ##倒序
SELECT * FROM t_employee
ORDER BY sal
DESC,comm
DESC; ##多字段排序
##查8：限制查询结果的显示条数
/*可用于分页显示
*/
SELECT * FROM t_employee
WHERE comm
=0 LIMIT
2; ##显示
< 查询结果
SELECT * FROM t_employee
WHERE comm
=0 LIMIT
10; ##显示
> 查询结果
##查9：指定查询结果显示的初始位置及结束位置
SELECT * FROM t_employee
ORDER BY sal
ASC,comm
ASC;
SELECT * FROM t_employee
ORDER BY sal
ASC,comm
ASC LIMIT
1,
5; ##从第2条开始，显示5条
##查10：统计函数查询
##如果 被操作的表是空，count返回0，其他函数返回 NULL
SELECT COUNT(empno)
FROM t_employee; ##
COUNT():统计记录条数
SELECT SUM(sal)
FROM t_employee; ##
SUM():统计字段总和
SELECT AVG(sal)
FROM t_employee; ##
AVG():统计字段平均值
SELECT MAX(sal)
FROM t_employee; ##
MAX():统计字段最大值
SELECT MIN(sal)
FROM t_employee; ##
MIN():统计字段最小值
##查11：(单列，多列)分组数据查询（与统计函数一起使用才有价值）
SELECT * FROM t_employee
GROUP BY deptno; ##单列分组
SELECT job
FROM t_employee
GROUP BY job; ##与去除重复项功能相似
SELECT deptno,GROUP_CONCAT(ename)
AS enames,
COUNT(ename)
AS number
FROM t_employee
GROUP BY deptno; ##单列分组与统计总和
SELECT deptno,
AVG(sal)
AS avgsal,GROUP_CONCAT(ename)
AS enmaes,
COUNT(ename)
AS number
FROM t_employee
GROUP BY deptno; ##单列分组、统计总和与平均数
SELECT deptno,Hiredate,
COUNT(ename)
AS enames,GROUP_CONCAT(ename)
number
FROM t_employee
GROUP BY deptno,Hiredate; ##多列分组查询与统计
##查12：使用 HAVING 字句限定分组查询
SELECT deptno,
AVG(sal)
AS avgsal,GROUP_CONCAT(ename)
AS enmaes,
COUNT(ename)
AS number
FROM t_employee
GROUP BY deptno
HAVING AVG(sal)
>2000; ##单列分组、统计总和，与平均工资
>2000的组
所有代码，均为自学时用到的测试与注释，知识细节或知识点不会面面俱到，亦不会有任何讲解，只做为自己学习复习用。
学习MySQL之单表操作（二）
标签：
