企业官网建设流程全解析

5.表的增删改查

CRUD：create(创建)，retrieve(读取) ， update(更新)，delete(删除)

5.1create(创建)

例子：创建一个学生表

5.1.1单行数据+全列插入

插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致

注意：这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。

5.1.2多行数据+指定列插入

插入两条记录，value_list 数量必须和指定列数量以及顺序一致

5.1.3插入否则更新

由于主键或者唯一键对应的值已经存在而导致插入失败

主键冲突

唯一键冲突

正确写法：

-- 0 row affected: 表中有冲突数据，但冲突数据的值和update的值相等

--1 row affected: 表中没有冲突数据，数据被插入

--2 row affected: 表中有冲突数据，并且数据已经被更新

通过mysql函数获取受到影响的数据行数

-- ON DUPLICATE KEY 当发生重复key的时候

5.1.4替换

--主键 或者唯一键 没有冲突，则直接插入；

--主键 或者唯一键 如果冲突，则删除后再插入

-- 1 row affected : 表中没有冲突数据，数据被插入

--2 row affected : 表中有冲突数据，删除后重新插入

5.2.1select 列

5.2.1.1全列查询

通常情况下不建议使用* 进行全列查询

1.查询的列越多，意味着需要传输的数据量越大

2.可能会影响到索引的使用

5.2.1.2指定列的查询

5.2.1.3查询字段为表达式

表达式包含多个字段

6.2.1.4为查询结果指定别名

6.2.1.5结果去重

分数重复了

去重结果

5.2.2where条件

比较运算符：

例：

英语不及格的同学以及英语成绩（<60）

5.2.2.2语文成绩再[80,90]分的同学及语文成绩

5.2.2.3数学成绩式58或者59或者98或者99分的同学及数学成绩

5.2.2.4姓孙的同学及孙某同学

%匹配任意多个（包括0个） 任意字符

_匹配严格的一个任意字符

5.2.2.5语文成绩好于英语成绩的同学

--where 条件中比较运算符的两侧都是字段

5.2.2.6总分在200分以下的同学

5.2.2.7 语文成绩 >80 并且不姓孙的同学

5.2.2.8孙某同学，否则要求总成绩>200并且语文成绩<数学成绩 并且英语成绩>80

5.2.2.9null的查询

5.2.3结果排序

例：

5.2.3.1同学及数学成绩，按数学成绩升序显示

5.2.3.2同学及qq号，按qq号排序显示

NULL视为比任何值都小，升序出现在最上面

NULL视为比任何值都小，降序出现在最下面

5.2.3.3查询同学各门成绩，依次按按数学降序，英语升序，语文升序的方式显示

--多字段排序，排序优先级随书写顺序

5.2.3.4查询同学以及总分，由高到低

order by 子句可以使用列别名

5.2.3.5查询姓孙的同学或姓曹的同学的数学成绩，结果按数学成绩由高到低显示

5.2.4筛选分页结果

建议：对未知表进行查询时，最好加一条LIMIT1，避免因为表中数据过大，查询全表数据导致数据库卡死按id进行分页，每页3条记录，分页显示第1，2，3页

5.3Update

5.3.1将孙悟空同学的数学成绩变更为80分

查看原数据

数据更新

查看更新后数据

5.3.2将曹孟德同学的数学成绩变更为60分，语文成绩变更为70分

5.3.3将总成绩倒数前3的3位同学的数学成绩加上30分

5.3.4将所有同学的语文成绩更新为原来的2倍

5.4Delete

5.4.1删除数据

5.4.1.1删除孙悟空同学的考试成绩

5.4.1.2删除整张数据表（删除整表操作要慎用）

5.4.2截断表

truncate [table] table_name

注意：这个操作慎用

1.只能对整表操作，不能像DELETE一样针对部分数据操作；

2.实际上Mysql不对数据操作，所以比Delete更快，但是truncate在删除数据的时候，并不经过真正的事务，所以无法回滚。

3.会重置AUTO_INCREMENT项

5.5插入查询结果

例：删除表中的重复记录，重复的数据只能有一份

创建原数据表

插入测试数据

创建一张空表 no_duplicate_table，结构和duplicate_table一样

将duplicate_table 的去重数据插入到no_duplicate_table

通过重命名表，实现原子的去重操作

查看最终结果

5.6聚合函数

5.6.1统计班级共有多少同学

5.6.2统计班级收集的qq号有多少

5.6.3统计本次考试的数学成绩分数个数

5.6.4统计数学成绩总分

5.6.5统计平均总分

5.6.6返回英语最高分

5.6.7返回>70分以上的数学最低分

5.7group by 子句的使用

在select使用group by子句可以对指定列进行分组查询

例：准备工作创建一个雇员信息表（来自oracle 9i的经典测试表）

EMP员工表

DEPT部门表

SALGREADE工资等级表

mysql> DROP TABLE IF EXISTS dept; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE dept ( -> deptno INT PRIMARY KEY, -- 部门编号 -> dname VARCHAR(20), -- 部门名称 -> loc VARCHAR(20) -- 所在地 -> ); Query OK, 0 rows affected (0.04 sec) mysql> mysql> INSERT INTO dept VALUES (10, 'ACCOUNTING', 'NEW YORK'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO dept VALUES (20, 'RESEARCH', 'DALLAS'); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO dept VALUES (30, 'SALES', 'CHICAGO'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO dept VALUES (40, 'OPERATIONS', 'BOSTON'); Query OK, 1 row affected (0.01 sec) mysql> DROP TABLE IF EXISTS emp; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> mysql> CREATE TABLE emp ( -> empno INT PRIMARY KEY, -- 雇员编号 -> ename VARCHAR(20), -- 雇员姓名 -> job VARCHAR(20), -- 职位 -> mgr INT, -- 上级主管编号 -> hiredate DATE, -- 入职日期 -> sal DECIMAL(7,2), -- 月薪 -> comm DECIMAL(7,2), -- 奖金 -> deptno INT, -- 部门编号 -> FOREIGN KEY (deptno) REFERENCES dept(deptno) -> ); Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO emp VALUES (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, NULL, 20); 81-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);Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO emp VALUES (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO emp VALUES (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO emp VALUES (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7788, 'SCOTT', 'ANALYST', 7566, '1987-04-19', 3000, NULL, 20); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO emp VALUES (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO emp VALUES (7876, 'ADAMS', 'CLERK', 7788, '1987-05-23', 1100, NULL, 20); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO emp VALUES (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO emp VALUES (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10); Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM dept; +--------+------------+----------+ | deptno | dname | loc | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec) mysql> SELECT * FROM emp; +-------+--------+-----------+------+------------+---------+---------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +-------+--------+-----------+------+------------+---------+---------+--------+ | 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | NULL | 20 | | 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | | 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | | 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 | NULL | 20 | | 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | | 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850.00 | NULL | 30 | | 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | NULL | 10 | | 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | NULL | 20 | | 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000.00 | NULL | 10 | | 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | | 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | NULL | 20 | | 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | NULL | 30 | | 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | NULL | 20 | | 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | NULL | 10 | +-------+--------+-----------+------+------------+---------+---------+--------+ 14 rows in set (0.00 sec) mysql> SELECT e.ename, e.job, e.sal, d.dname, d.loc -> FROM emp e -> JOIN dept d ON e.deptno = d.deptno; +--------+-----------+---------+------------+----------+ | ename | job | sal | dname | loc | +--------+-----------+---------+------------+----------+ | CLARK | MANAGER | 2450.00 | ACCOUNTING | NEW YORK | | KING | PRESIDENT | 5000.00 | ACCOUNTING | NEW YORK | | MILLER | CLERK | 1300.00 | ACCOUNTING | NEW YORK | | SMITH | CLERK | 800.00 | RESEARCH | DALLAS | | JONES | MANAGER | 2975.00 | RESEARCH | DALLAS | | SCOTT | ANALYST | 3000.00 | RESEARCH | DALLAS | | ADAMS | CLERK | 1100.00 | RESEARCH | DALLAS | | FORD | ANALYST | 3000.00 | RESEARCH | DALLAS | | ALLEN | SALESMAN | 1600.00 | SALES | CHICAGO | | WARD | SALESMAN | 1250.00 | SALES | CHICAGO | | MARTIN | SALESMAN | 1250.00 | SALES | CHICAGO | | BLAKE | MANAGER | 2850.00 | SALES | CHICAGO | | TURNER | SALESMAN | 1500.00 | SALES | CHICAGO | | JAMES | CLERK | 950.00 | SALES | CHICAGO | +--------+-----------+---------+------------+----------+ 14 rows in set (0.00 sec)

如何显示每个部门的平均工资和最高工资

显示每个部门的每种岗位的平均工资和最低工资

显示平均工资低于2000的部门和它的平均工资

统计各个部门的平均工资

having和group by 配合使用，对group by结果进行过滤

注：本文更偏向于数据库增删改查的复习练习和笔记，操作过程更偏向于会用就行。