学习MySQL之单表操作（二）

时间：2022-05-19 19:54:17
##单表操作

##创建表

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的组

所有代码，均为自学时用到的测试与注释，知识细节或知识点不会面面俱到，亦不会有任何讲解，只做为自己学习复习用。

相关文章