mysql dml统计分析_MySQL学习之SQL基础（一）DML

DML(data Manipulation language)

INSERT DELETE UPDATE SELECT

INSERT

mysql> desc emp;

+----------+---------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+---------------+------+-----+---------+-------+

| ename | varchar(25) | YES | | NULL | |

| hiredate | date | YES | | NULL | |

| sal | decimal(10,2) | YES | | NULL | |

| deptno | int(2) | YES | | NULL | |

+----------+---------------+------+-----+---------+-------+

4 rows in set (0.00 sec)

mysql>

INSERT INTO emp (ename,hiredate,sal ,deptno) VALUES ('zzx','2000-01-01',100,1);

INSERT INTO emp VALUES('lisa','2003-02-01',400,2);

一次性插入多条记录

INSERT INTO emp VALUES('jack','2018-09-21',12000,1),

('tony','2018-09-21',13000,4)

;

UPDATE

更新一张表

UPDATE emp set sal=11000 where ename='lisa';

多表更新

首先创建一张表部门表

CREATE TABLE dept(

deptno int (3) ,

deptname varchar(25)

);

mysql> desc dept;

+----------+-------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+----------+-------------+------+-----+---------+-------+

| deptno | int(3) | YES | | NULL | |

| deptname | varchar(25) | YES | | NULL | |

+----------+-------------+------+-----+---------+-------+

2 rows in set (0.00 sec)

INSERT dept VALUES(1,'tech');

INSERT INTO dept VALUES (2,'sale'),(3,'fin');

mysql> select * from emp;

+-------+------------+----------+--------+

| ename | hiredate | sal | deptno |

+-------+------------+----------+--------+

| zzx | 2000-01-01 | 100.00 | 1 |

| lisa | 2003-02-01 | 11000.00 | 2 |

| jack | 2018-09-21 | 12000.00 | 1 |

| tony | 2018-09-21 | 13000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.00 sec)

多表更新

UPDATE emp a ,dept b SET a.sal =a.sal * b.deptno WHERE a.deptno=b.deptno;

mysql> SELECT * FROM emp;

+-------+------------+----------+--------+

| ename | hiredate | sal | deptno |

+-------+------------+----------+--------+

| zzx | 2000-01-01 | 100.00 | 1 |

| lisa | 2003-02-01 | 22000.00 | 2 |

| jack | 2018-09-21 | 12000.00 | 1 |

| tony | 2018-09-21 | 13000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.00 sec)

查询表

查询去重，DISTINCT只能显示一列

mysql> SELECT DISTINCT deptno FROM emp;

+--------+

| deptno |

+--------+

| 1 |

| 2 |

| 4 |

+--------+

3 rows in set (0.00 sec)

条件查询

mysql> SELECT * FROM emp;

+-------+------------+----------+--------+

| ename | hiredate | sal | deptno |

+-------+------------+----------+--------+

| zzx | 2000-01-01 | 100.00 | 1 |

| lisa | 2003-02-01 | 22000.00 | 2 |

| jack | 2018-09-21 | 12000.00 | 1 |

| tony | 2018-09-21 | 13000.00 | 4 |

+-------+------------+----------+--------+

mysql> SELECT ename,sal FROM emp WHERE deptno<=1 AND sal > 5000;

+-------+----------+

| ename | sal |

+-------+----------+

| jack | 12000.00 |

+-------+----------+

1 row in set (0.00 sec)

3、排序查询

查询emp排序后第二条之后的三条记录

mysql> SELECT * FROM emp;

+-------+------------+----------+--------+

| ename | hiredate | sal | deptno |

+-------+------------+----------+--------+

| zzx | 2000-01-01 | 100.00 | 1 |

| lisa | 2003-02-01 | 22000.00 | 2 |

| jack | 2018-09-21 | 12000.00 | 1 |

| tony | 2018-09-21 | 13000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.00 sec)

mysql> SELECT * FROM emp order by sal LIMIT 1,3;

+-------+------------+----------+--------+

| ename | hiredate | sal | deptno |

+-------+------------+----------+--------+

| jack | 2018-09-21 | 12000.00 | 1 |

| tony | 2018-09-21 | 13000.00 | 4 |

| lisa | 2003-02-01 | 22000.00 | 2 |

+-------+------------+----------+--------+

3 rows in set (0.00 sec)

聚合

统计emp总人数

mysql> SELECT COUNT(1) FROM emp;

+----------+

| COUNT(1) |

+----------+

| 4 |

+----------+

1 row in set (0.00 sec)

统计各个部门的总人数

mysql> SELECT deptno FROM emp GROUP BY deptno;

+--------+

| deptno |

+--------+

| 1 |

| 2 |

| 4 |

+--------+

3 rows in set (0.00 sec)

mysql> SELECT deptno,COUNT(1) FROM emp GROUP BY deptno;

+--------+----------+

| deptno | COUNT(1) |

+--------+----------+

| 1 | 2 |

| 2 | 1 |

| 4 | 1 |

+--------+----------+

3 rows in set (0.00 sec)

统计各个部门的总人数也要统计总人数

with rollup进行再次汇总

SELECT deptno,COUNT(1) FROM emp GROUP BY deptno WITH rollup;

统计各个部门的总人数大于等于2

使用having进行条件过滤

mysql> SELECT deptno,COUNT(1) FROM emp GROUP BY deptno HAVING COUNT(1)>=2;

+--------+----------+

| deptno | COUNT(1) |

+--------+----------+

| 1 | 2 |

+--------+----------+

1 row in set (0.00 sec)

子查询

查询emp表中部门编号在dept表的员工

| ename | hiredate | sal | deptno |

+-------+------------+----------+--------+

| zzx | 2000-01-01 | 100.00 | 1 |

| lisa | 2003-02-01 | 22000.00 | 2 |

| jack | 2018-09-21 | 12000.00 | 1 |

| tony | 2018-09-21 | 13000.00 | 4 |

+-------+------------+----------+--------+

4 rows in set (0.01 sec)

mysql> SELECT * FROM emp order by sal LIMIT 1,3;^C

mysql> SELECT * FROM dept;

+--------+----------+

| deptno | deptname |

+--------+----------+

| 1 | tech |

| 2 | sale |

| 3 | fin |

+--------+----------+

3 rows in set (0.00 sec)

mysql> SELECT ename ,sal from emp where deptno in (select deptno from dept);

+-------+----------+

| ename | sal |

+-------+----------+

| zzx | 100.00 |

| lisa | 22000.00 |

| jack | 12000.00 |

+-------+----------+

3 rows in set (0.00 sec)

联合

将查询结果合并显示

查询所有的的deptno，去重

SELECT deptno FROM emp UNION

SELECT deptno FROM dept;

查询所有的的deptno，不去重

SELECT deptno FROM emp UNION all

SELECT deptno FROM dept;