MySQL——数据表的基础操作

MySQL基础操作

表，数据库，属性名称统一使用全小写+下划线分隔，不要使用驼峰命名。

表数据的增删改查

CURD（Create Updata Retrieve Delete）

新增数据

insert into tb_name(属性名...) values(属性值...);

建表：

create table if not exists stu(
  id int comment '学号',
  name varchar(20) comment '姓名',
  qq_mail varchar(100) comment 'qq邮箱'
);

1）单行插入

insert into stu(id,name) values(1,'Lay');

2）单行全插入

不用列出每个属性名称

insert into stu values(2,'Z','1007@qq.com');

3）多行某几列插入

insert into stu(id,name) values
  (3,'Shirley'),
  (4,'Vivi');

4）多行全插入

insert into stu values
  (5,'Shirley','111@qq.com'),
  (6,'Vivi','222@qq.com');

查询（Retrieve）

建表：

create table if not exists exam_result(
  id int comment '学号',
  name varchar(20) comment '姓名',
  chinese decimal(3,1) comment '语文成绩',
  math decimal(3,1) comment '数学成绩',
  english decimal(3,1) comment '英语成绩'
);
insert into exam_result values
  (1,'Lay',97,98,99),
  (2,'八戒',55,10,20),
  (3,'唐僧',99.9,60.1,90.5),
  (4,'沙和尚',55,98,90.5); 

1）查找指定列

select name, chinese from exam_result;

select得到的数据相当于一张临时表

2）查询字段为表达式

select name, chinese+10 from exam_result;
-- 查找姓名及总成绩
select name, chinese + math + english from exam_result;

3）给查询结果起别名

as关键字，别名加不加单引号都可以

select name, chinese+math+english as 总成绩 from exam_result;
select name, chinese+math+english 总成绩 from exam_result;

4）去重查询

得到去重处理后结果，distinct关键字

若要查询的多列结果包含distinct关键字的话，distinct关键字必须放在第一列前，对多列的组合进行去重，如：Lay，99 ！= Lay，100

select distinct chinese from exam_result;
-- 查询不重复的学生姓名及该学生的语文成绩
select distinct name, chinese from exam_result;

-- 错误写法：select name, distinct chinese from exam_result;
-- 错因：select name有六行，select chinese只有四行，无法拼接成一个表格
select distict 列名称1, 列名称2... from tb_name; -- 组合属性去重

5）按照结果集排序

order by关键字，默认升序asc 升asc（结果从小到大显示）/降desc（从大到小）

5.1）order by 可以使用别名

select name, chinese+math+english as 总成绩 from exam_result
order by 总成绩;

5.2）若结果集中包含 null，null值表示最小,比负数还小。同为null谁的行号在前就先显示谁

insert into exam_result(id,name) values(5,'孙悟空');
insert into exam_result values(6,'白龙马', 0,-1,-2);
insert into exam_result(id,name) values(7,'哪吒');

5.3）针对多个列进行排序，按照书写顺序优先级排序

例1：按总成绩降序排序

select name, chinese+math+english as 总成绩 from exam_result
order by 总成绩 desc;

例2：查询姓名，语文数学成绩，其中按照语文成绩升序，数学成绩降序排列

select name, chinese, math from exam_result
order by chinese asc, math desc;
-- 只有当语文成绩相同时，数学成绩才按降序排序

6）分页查询

limit关键字，限制结果显示行数

offset关键字，结果集显示的起始行数

select name, chinese+math+english as 总成绩 from exam_result
order by 总成绩 desc limit 3;

6.2) limit n offset s

limit n offset s： 查询结果从第s行开始(不包括第s行)，按照前n行输出。（s,s+n]

注：若s大于已有行数，则结果为空 Empty set，表示当前数据表不存在从s开始的n行数据

排序问题求前几，后几，第几，可以使用distinct + order by + limit

例：查询总成绩在3-6名的学生姓名及总成绩

select name, chinese+math+english as 总成绩 from exam_result
order by 总成绩 desc limit 3 offset 2;

7）条件查询where语句

例：查询所有总成绩大于200的学生姓名和成绩

select name, chinese+math+english as 总成绩 from exam_result
where chinese+math+english > 200;

7.1）where分支中不支持别名

错误写法：

select name, chinese+math+english as 总成绩 from exam_result
where 总成绩 > 200;

问：为什么order by支持别名而where不支持呢？
答：因为order by是在select之后执行，即给查出来的结果进行排序。（select查询出的结果对于order by已经可见了）
where在select查询之前进行，条件过滤，select在满足where分支的条件的基础上查询，即根据where给出的条件去查询满足条件的结果。
对where来说此时select的别名还未执行，因此不可用
例：（order by 和 where 的书写顺序与执行顺序无关）

select name, chinese+math+english as 总成绩 from exam_result
where chinese+math+english > 200
order by 总成绩 desc;

7.2）MySQL的null不含在 <, = , <=等比较运算符中

null=null 也不支持，有专门的运算符过滤null值(判空：is null/判非空：is not null)
易错：

select name, chinese+math+english as 总成绩 from exam_result
where chinese+math+english < 200;

即使有null，查询结果不包含空值null

7.3）null + 任何数 都是 null

即：只要有一个值为null，则和为null。因为null本身没有任何意义

insert into exam_result values(8,'小神婆',null,30,15);
insert into exam_result values
  (9,'LayZ',66,77,89),
  (10,'ZhangLay',56,88,32);
-- 例：查询成绩非空的同学的姓名和总成绩
select name, chinese+math+english as 总成绩 from exam_result
where chinese+math+english is not null;

7.4）区间查询

between … and … 区间查询是并且关系

例：查询语文成绩在[50,95]之间的同学姓名和语文成绩

法一： between … and …

select name, chinese from exam_result
where chinese between 50 and 95;

法二：条件语句 和 and

select name, chinese from exam_result
where chinese >= 50 and chinese <= 95;

7.5）包含查询

in (集合) 包含查询是或者关系

例：数学成绩是 10 或 60 或 98 分的同学姓名和数学成绩

法一：in集合

select name, math from exam_result
where math in (10,60,98);

法二：条件语句 和 or

select name, math from exam_result
where math=10 or math=60 or math=98;

AND 和 OR ： and优先级高于or，不用背，根据需要加上括号

7.6）模糊查询 like

%：匹配任意个字符[0,n] _：只能匹配一个字符

a. 查询名字中带龙的学生姓名

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

b. 查询所有名字中龙字出现在第二个位置且名字由三个字符组成的学生姓名

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

修改表数据

update tb_name set 列名 = [表达式-根据这个表达式确定修改的行]

1）修改一行一列

-- 成绩表中白龙马数学成绩+30
update exam_result set math = math+30
where name='白龙马';

2）修改一行多列

-- 成绩表中白龙马数学和英语成绩+30
update exam_result set math=math+30, english = english +30
where name='白龙马';

3）修改多行多列

-- 成绩表中带Lay的同学数学和英语成绩-2
-- 错误：like和=用法混淆
update exam_result set math=math-2, english = english-2
where name='%Lay%';

-- 改正：
update exam_result set math=math-2, english = english-2
where name like '%Lay%';

注意：null不可进行加减乘除，故修改时若牵扯到null的四则运算，则不对该行进行修改

4）修改全表

若update语句不带where条件，则该属性的所有行都受影响

update exam_result set math=math+1;

删除delete

delete from tb_name where 过滤条件;

1）删除一行

delete from exam_result where name='哪吒';

2）删全表

若此时不加where条件，则全表删除

一行行执行delete操作，显示受影响行数

delete from exam_result;

拓展：全表删除truncate

将表中所有数据删除，不显示受影响行数。相当于直接在os上将数据文件的大小置为0

truncate table 表名;

delete 和 turncate 区别：

• delete删除慢，可恢复，可加条件
• turncate删除快，数据不可恢复，不能加条件

drop 和 delete、turncate 区别：

• drop table相当于直接把操作系统上这个文件删了，表都没了
  result where name=‘哪吒’;

### 2）删全表

若此时不加where条件，则全表删除

一行行执行delete操作，显示受影响行数

```mysql
delete from exam_result;

拓展：全表删除truncate

将表中所有数据删除，不显示受影响行数。相当于直接在os上将数据文件的大小置为0

truncate table 表名;

delete 和 turncate 区别：

• delete删除慢，可恢复，可加条件
• turncate删除快，数据不可恢复，不能加条件

drop 和 delete、turncate 区别：

• drop table相当于直接把操作系统上这个文件删了，表都没了
• delete和truncate只是删除表中数据，表还在