MySql--表的基本查询（CRUD）

CRUD : Create( 创建 ), Retrieve( 读取 ) ， Update( 更新 ) ， Delete （删除）

1.insert

基本插入

创建一张表：

-- 创建一张学生表
CREATE TABLE students (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
sn INT NOT NULL UNIQUE COMMENT '学号',
name VARCHAR(20) NOT NULL,
qq VARCHAR(20)
);

单行数据 + 全列插入：

-- 插入两条记录，value_list 数量必须和定义表的列的数量及顺序一致
-- 注意，这里在插入的时候，也可以不用指定id(当然，那时候就需要明确插入数据到那些列了)，那么mysql会使用默认的值进行自增。

INSERT INTO students VALUES (100, 10000, '唐三藏', NULL);
INSERT INTO students VALUES (101, 10001, '孙悟空', '11111');

多行插入

INSERT INTO students (id, sn, name) VALUES
(102, 20001, '曹孟德'),
(103, 20002, '孙仲谋');

一次可以插入多行数据。

插入否则更新

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

可以选择性的进行同步更新操作 语法：

INSERT INTO students (id, sn, name) VALUES (100, 10010, '唐大师')
on duplicate key update sn = 10010, name = '唐大师';

 注意，更新的值不能和其他的主键或唯一键冲突。

 直接替换（REPLACE）

 如果没有冲突就插入，有冲突就替换。

示例：

replace into students (sn, name) VALUES (20001, '曹阿瞒');

 2.select

查询语句是使用MySql时频率最高的语句。其中涉及到单表，多表和子查询。

 基本查找

 基本语法：

SELECT
[DISTINCT] {* | {column [, column] ...}
[FROM table_name]
[WHERE ...]
[ORDER BY column [ASC | DESC], ...]
LIMIT ...

order by 表示我们要根据哪列进行排序，可以选择倒序还是正序。

where表示查询条件。

limit表示我们筛选出来的结果显示多少条数。

创建测试表

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);

插入测试用例

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

 全列查询：

select * from exam_result;

通常情况下，是不建议用 * 查询的：

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

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

指定列查询：

比如id，name列

select id,name from exam_result;

 其实本质也是先把所有的数据查询出来，然后再把指定的列显示出来。

查询字段为表达式：

select语句很特殊，它后面的语句就是要被执行的表达式。

比如 

select 7 * 9;

或者我们要在查询的时候混入一个10

select id,name,10 from exam_result;

 

发现真的混入了一个10。

或者再混入一个 1 + 1

select id,name,1+1 from exam_result;

 

那么我们也可以计算它们的总成绩并命名为total。（语法：在指定列后面直接加 as + （名字）)

select id,name,math + chinese + english as total from exam_result;

(as 其实可以省略） 

查询结果去重

在指定的列前加上distinct

select distinct math from exam_result;

 

 条件查询

实际查询一般是不会将数据全部查询出来的，一般都会用到条件查询。

比较运算符

 需要注意的是，在跟NULL判断的时候是不安全的，它不像C语言里面NULL可以跟0作比较。在MySql中任何数跟NULL比较都是NULL。甚至NULL 跟NULL比较都是没有意义的

如果非要NULL跟NULL比较，可以用<=>

select NULL<=>NULL;

 

不过就算要比较一个数是否是NULL，也会用 IS NULL 或者 IS NOT NULL。

逻辑运算符

 where字句案例

1.查找数学成绩是 58 或者 59 或者 98 或者 99 分的同学及数学成绩：
可以用or

select math from exam_result where math=58 or math=59 or math=98 or math=99;

但是这样写比较繁琐，我们可以用in

select math from exam_result where math in(58,59,98,99);

2.模糊匹配

a.查找姓孙的同学

select name from exam_result where name like '孙%';

 

b.查找孙某同学，注意只有俩字

select name from exam_result where name like '孙_';

 

where后面也是可以跟表达式的。

查询总分低于200分的同学：

select name,chinese + math + english total from exam_result  where chinese + math + english < 200;

 

但是有一个问题，就是这样写起来感觉很繁琐，我们能不能这样写呢？

select name,chinese + math + english total from exam_result  where total < 200;

 答案是不能的

这里就涉及到SQL语句中执行顺序的问题了。

它的执行顺序依次是：

1.先执行 from exam_result。先告诉mysql从哪个表里面筛选数据。 

2.设置筛选条件，也就是 where字句。

3.最后再执行我们要查询哪些列，并且重命名。

所以我们就能理解为什么在where判断的时候不能使用之前的重命名，这是由执行顺序导致的。

另外我们也不能在筛选的时候对数据进行重命名，这是语法不支持的。

order by 字句 

语法：

-- ASC 为升序（从小到大）

-- DESC 为降序（从大到小）

-- 默认为 ASC

SELECT ... FROM table_name [WHERE ...]

ORDER BY column [ASC|DESC], [...];

 其中NULL参与排序，但是NULL在比较的时候比任何值都要小。

比如分别按数学成绩升序

select name,math from exam_result order by math;

默认asc是升序排序。

降序：

select name,math from exam_result order by math desc;

稍微复杂一点的：

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

其中它的意思是，如果数学成绩相等的话，就按照英语降序，如果英语又相等的话，那么就按照语文升序。 

先看只按数学降序排序

此时唐三藏是在猪悟能前面的，那么我们完整的执行SQL语句

select name,math,english,chinese from exam_result order by math desc,english desc,chinese;

此时猪悟能就跑到前面去了。

另外我们按总分排序的时候可以这样写

select name,math + english + chinese total from exam_result order by total;

我们发现在where的时候不能这样写（不能使用别名），但是在order by这里就可以了。

这里原因归根结底就还是执行顺序问题。

因为order by是先筛选出合适的数据之后，再排序的。

要注意区分把数据筛选出来和把数据显示出来。

分页查询

语法：

-- 起始下标为 0
-- 从 s 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT s, n
-- 从 0 开始，筛选 n 条结果
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n;
;
-- 从 s 开始，筛选 n 条结果，比第二种用法更明确，建议使用
SELECT ... FROM table_name [WHERE ...] [ORDER BY ...] LIMIT n OFFSET s;

 建议：对未知表进行查询的时候，先带一条limit 1，防止表中数据过大，查询全表数据导致数据库卡死。

比如我们想查询前两行数据

select * from exam_result limit 2;

此时，这里的2表示从表的开始位置查多少行。 

或者我们想从第二行开始，往后查三行。

select * from exam_result limit 2,3;

 此时这里逗号前的2表示开始位置，而3表示步长。（下标是从0开始的）

或者还可以这样写

mysql> select * from exam_result limit 3 offset 0;

这里的offset表示的就是从哪里开始，3表示查三行。

所以总结limit的作用就是：

可以作简单的分页查询，每次只需要更改查询的起始位置，就可以每次查询固定行数的数据。

另外limit的本质是显示，它的执行顺序排在order by后面。（在搭配order by使用时）。

3.Update

语法：

UPDATE table_name SET column = expr [, column = expr ...]
[WHERE ...] [ORDER BY ...] [LIMIT ...]

 比如：将孙悟空同学的数学成绩改为80

update exam_result set math=80 where name='孙悟空';

注意要加上where条件判断，不然表中所有人的数学成绩都会被修改成80。

再比如：将成绩倒数的三名同学，数学成绩加上30

update exam_result set math=math+30 order by math+chinese+english asc limit 3;

4.Delete

删除数据

语法;

DELETE FROM table_name [WHERE ...] [ORDER BY ...] [LIMIT ...]

 案例1：删除孙悟空同学的数据

delete from exam_result where name='孙悟空';

案例2：删除成绩倒数第一名的同学的数据

delete from exam_result order by math+chinese+english asc limit 1;

案例3：删除表中所有的数据

首先我们要清楚，delete是删除表中数据的，它不会删除表的结构。

delete from exam_reslut;

这个操作一定要慎用，这里我就不执行了。

另外一定要注意，delete不会影响表的结构，它只删除表的数据。比如，我们就算删除了表中所有的数据，它也不会影响自增主键计数器的值。

截断表

语法：

TRUNCATE [TABLE] table_name

 这个操作要慎用。同时注意它和delete的区别：

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

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

3. 会重置 AUTO_INCREMENT 项。（会影响表结构）

truncate不会将操作记录在日志里。

5.插入查询结果

 语法：

INSERT INTO table_name [(column [, column ...])] SELECT ...

案例：删除表中的重复数据

先创建测试表，并插入数据

CREATE TABLE duplicate_table (id int, name varchar(20));

-- 插入测试数据
INSERT INTO duplicate_table VALUES
(100, 'aaa'),
(100, 'aaa'),
(200, 'bbb'),
(200, 'bbb'),
(200, 'bbb'),
(300, 'ccc');

删除重复数据的思路就是：

先创建另一张表，结构相同。

CREATE TABLE no_duplicate_table LIKE duplicate_table;

然后通过以去重查询原表的方式，将查询的结果插入到新表中。

INSERT INTO no_duplicate_table SELECT DISTINCT * FROM duplicate_table;

然后再更改原表和新表的名字，即可。

RENAME TABLE duplicate_table TO old_duplicate_table,
no_duplicate_table TO duplicate_table;

6.聚合统计

 还是先创建一个测试表

CREATE TABLE exam_result (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20) NOT NULL COMMENT '同学姓名',
chinese float DEFAULT 0.0 COMMENT '语文成绩',
math float DEFAULT 0.0 COMMENT '数学成绩',
english float DEFAULT 0.0 COMMENT '英语成绩'
);

再插入一些测试数据

INSERT INTO exam_result (name, chinese, math, english) VALUES
('唐三藏', 67, 98, 56),
('孙悟空', 87, 78, 77),
('猪悟能', 88, 98, 90),
('曹孟德', 82, 84, 67),
('刘玄德', 55, 85, 45),
('孙权', 70, 73, 78),
('宋公明', 75, 65, 30);

 

 查记录的个数

select count(*) from exam_result;

其中count()括号里面不是*也可以

 

示例2：我们想查询表中记录中有多少条有效的数学成绩

select count(math) from exam_result;

 

注意：NULL是参与计数的。

 示例3：如果我们想统计不重复的数据，可以加distinct，但是要注意distinct所加在的位置。

select count(distinct math) from exam_result;

 示例4：统计数学成绩总分

select sum(math) from exam_result;

案例4：统计数学平均分

select avg(math) from exam_result;

 

案例5：统计数学成绩最高的（统计最低的同理）

select max(math) from exam_result;

 

案例6：统计数学成绩大于80分的最小值

select min(math) from exam_result where math>80;

 

 

7.分组聚合统计 

分组的目的是为了分组之后，方便聚合统计。

group by 子句 

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

语法：

select column1, column2, .. from table group by column;

 

分组聚合案例

 浅看一下员工信息表

EMP 员工表

DEPT 部门表

SALGRADE 工资等级表

在emp那里，有一个部门编号，虽然没有外键之实，但是已经有外键之名了。只是在Mysql中加外键其实是会影响效率的，我们想起到外键约束的效果，可以在上层---也就是我们使用java或者C++这样的语言连接数据库时，在这里判断不符合外键约束的操作。

员工薪资表 

 

插入了测试数据

 

部门表

同样插入数据后

 

 

还有薪资等级表

同样有测试数据

 

示例1：显示每个部门的平均工资和最高工资。

select deptno,max(sal) 最高,avg(sal) 平均 from emp group by deptno;

分组（分表），其实就是把一张表按照条件在逻辑上拆成了多个子表，然后分别对各自的子表进行聚合统计。

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

据需求要先按部门分组，再按岗位分组，最后再聚合。

select deptno,job,avg(sal) 平均,min(sal) 最低 from emp group by deptno, job;

一般在分组聚合查询时，select后面只能跟两类，一类是跟分组有关的列，比如deptno和job，另一类就是聚合函数，其他的select后面一般不能跟，比如ename（员工姓名），如果查询的话就会报错。

案例3：显示平均工资低于2000的部门和它的最低工资。

这个需求比较复杂：

1.首先我们需要先统计出每一个部门的平均工资。（也就是结果先聚合出来）

2.再进行判断。（对聚合出来的结果进行判断）

我们用having来对聚合后的结果进行判断。

select deptno,avg(sal) deptavg from emp group by deptno having myavg<2000;

 这个having经常跟group by搭配使用，作用是对分组进行筛选，跟where有点像。

虽然having和where都是条件筛选，但是它们完全不一样。

比如将刚刚的案例having换成where就会报错。

之前也提到过，分组其实就是分表，将原先的一张表，按筛选条件分成了多张表。

同理，我们每次用where条件筛选的时候，每次筛选出来的结果在逻辑上也是一张表。

比如：查每个部门的每种工作的平均工资低于2000，但是SMITH要离职了， 所以他不参与统计。

select deptno,job avg(sal) myavg from emp where ename!='SMITH' group by deptno,job having myavg < 2000;

 总结where和having的区别：

1.where是对具体的任意列进行条件筛选。

2.having是对分组聚合之后的结果进行条件筛选。

所以它们条件筛选的阶段是不同的。

总结

基本的表查询就到这里。

到现在，我们已经认识到了：

  不是只有从磁盘上的表结构导入到MySQL而形成的表才叫做表， 中间根据条件筛选出来的，包括最终结果，都可以认为是逻辑上的表。