MySQL 基本查询(一)

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

Create(insert)

指定列的单行插入和全列插入

create table students(
id int unsigned primary key auto_increment,
sn int unsigned unique key,
name varchar(30) not null,
qq varchar(30) unique key
);
// 指定列插入
insert into students (sn,name,qq) values(1,'张三','123456789');
// 全列插入
insert into students values(12,2,'李四','12345678');
// into可以省略
insert students values(13,3,'王五','123456'); 

多行全列插入和指定列的多行插入

// 全列插入
insert into students values(15,5,'曹操','12345'),
(16,6,'诸葛亮','123');
// 指定列插入
insert into students (sn,name,qq) values(6,'项羽','125'),(7,'张飞','126');

如果主键存在，要插入替换存在的值

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

// 发生主键冲突,把主键后面的属性都修改了
// 要更新的值不能和其他的主键和唯一键冲突
insert into students values(13,132,'xuyou','111111') on duplicate key update sn=132,name='xuyou',qq='111111';

2. 1行被影响，等价于数据直接插入，2行被影响，表中有冲突数据并且要被update更新，0行被影响，表中的老的数据和update要更新的值是一样的

3. 通过 MySQL 函数获取受到影响的数据行数

select row_count();

-1表示没有行受到影响

1行受到影响

replace

1. 主键 或者 唯一键 没有冲突，则直接插入
2. 主键 或者 唯一键 如果冲突，则删除后再插入

replace into students (name,name,qq) values(222,'张三','123');

3. 1 row affected:表中没有冲突数据，数据被插入
4. 2 row affected:表中有冲突数据，删除后重新插入

基本select

全列查询

1. *，通配符，可以进行全列查询，把所有数据都遍历一遍，公司中不建议使用 * ,因为数据库的数据太多了，全遍历还是不方便查看

select * from 表名

指定列查询

2. 指定列的顺序不需要按定义表的顺序来

select name,math,id from 表名

3. select 可以帮你计算出表达式放到这一列中

4. 可以重命名表达式的名字

5. 也可以不带as重命名

// 多加一列,数学+10分的列
select name 姓名,math 数学,math+10 from exam_result;

6. 对查询结果进行去重

where子句

1. where可以筛选行，只要包含了=的是不可以用来比较NULL的，是不安全的
2. 比较运算符

3. 逻辑运算符

select name,english from exam_result where english < 60;

where子句案例

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

select chinese,name from exam_result where chinese between 80 and 90;
select chinese,name from exam_result where chinese >= 80 and chinese <= 90;

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

1. in，如果是 option 中的任意一个，返回 TRUE(1)

select name,math from exam_result where math = 58 or math = 59 or math = 98 or math = 99;
// in表示只要在括号中存在的就满足条件
select name,math from exam_result where math in(58,59,98,99);

姓孙的同学 及 孙某同学

1. like，模糊匹配。% 表示任意多个（包括 0 个）任意字符；_ 表示任意一个字符

// 把姓孙的都匹配出来
select name from exam_result where name like '孙%';
// 把孙某匹配出来
select name from exam_result where name like '孙_';

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

select name,chinese,english from exam_result where chinese > english;

总分在 200 分以下的同学

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

1. 筛选的条件是先从哪个表中找，再按条件筛选，最后要筛选出对应的属性，第三步才知道total，所以第二步才会报错

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

select name,chinese from exam_result where chinese > 80 and name not like '孙%'; 

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

select name,chinese,math,english,chinese+math+english as 总分 from exam_result where name like '孙_' or (chinese+math+english > 200 and chinese < math and english > 80);

NULL 的查询

1. ’ ‘和NULL是不一样的，’ '是空串

select * from test where name is null;
select * from test where name is not null;

2. NULL和任何数比较都是NULL，NULL是不参与比较的

3. <=> 可以用于NULL比较，0表示假，1表示真

order by 子句

1. asc为升序，desc为降序，默认是asc
2. SELECT … FROM table_name [WHERE …]
   ORDER BY column [ASC|DESC], […];

// 按数学成绩升序排序
select name,math from exam_result where order by math asc;
// 按数学成绩降序排序
select name,math from exam_result where order by math desc;

order by 子句案例

NULL值比任何值都小

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

1. 如果数学成绩相同，按照英语成绩升序的顺序排列，如果数学，英语成绩相同，按照语文升序的顺序排列

select name,math,english,chinese from exam_result where order by math desc,english desc,chinese asc;// 不显示给升序和降序,默认是升序排序
select name,math,english,chinese from exam_result
where order by math;

2. 可以使用别名了，因为是先有别名，再使用别名
   

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

1. order by的执行顺序是比where的顺序要晚

select name,math from exam_result where name like '孙%' or name like '曹%' order by math desc;

筛选分页结果

// 筛选出前5行的数据
select * from exam_result limit 5;

2. 从第二条记录开始筛选

// n表示行数,s表示从下标哪个位置开始筛选
select * from test limit n offset s;

3. 对未知表进行查询时，最好加一条 LIMIT 1，避免因为表中数据过大，查询全表数据导致数据库卡死
4. 也可以进行分页操作，如下图，按三行分1页

5. 需要有数据才能排序，只有数据准备好了，你才需要显示，limit的本质功能是’显示’数据，并且limit的顺序等级是最后的，在排序后
6. 需要排序好后再分页查看，比如要查看5到10名的成绩，则需要排升序，再分页