mysql 增删查改 mysql增删改sql

目录

一：方法详解

 1.新增(INSERT)

 2.删除(DELETE)

   3.修改(UPDATE)

   4.查询(SELECT)

二：案例演示

问题展示：

---

一：方法详解

 1.新增(INSERT)

语法：INSERT INTO 表名(列1,列2,...,列N) values(值1,值2,...,值N)

   注：
   ①: 如果数据是字符型，必须使用单引号或者双引号，如："value";
   ②:在缺省列名的情况，插入不能少或多字段值;
   ③: 在插入部分字段时，要指定插入的数据字段并依此插入;

 2.删除(DELETE)

DELETE语法：DELETE FROM 表名 WHERE 条件

   3.修改(UPDATE)

语法：UPDATE 表名 set 列1=值1,列2=值2,... WHERE 条件

   注：
   ①:不推荐使用全表修改所有字段数据，除非有特殊需求; 
   ②: 推荐使用带条件方式修改字段数据;

   4.查询(SELECT)

语法：SELECT * FROM 表名 WHERE 条件

注：

①:不要使用*，而是使用字段名的方式进行查询
②:SQL语句采用大写方式
③:数据库表中不要NULL值，在检索时有NULL值的情况下查询效率很低
④:适当建立索引（重要）
⑤:分库分表（水平，垂直）、读写分离（集群）
⑥:避免子查询、不要使用IS NULL，而是要使用IS NOT NULL
⑦:分页时，查询总记录数，不要使用count(*),使用count(0)

 ⑧:group by 之后，select 后面的值只能是唯一的或者是聚合函数
 ⑨:order by 排序，默认是升序asc，降序desc

二：案例演示

• 表结构设计：

--创建表结构
-- 1.学生表-student
-- sid 学生编号,sname 学生姓名,sage 学生年龄,ssex 学生性别
create table if not exists student
(
    sid   int primary key comment '学生编号',
    sname varchar(20) not null comment '学生名字',
    sage  date        not null comment '学生年龄',
    ssex  varchar(2)  not null comment '学生性别'
) engine = innodb
  charset = utf8mb4
  collate = utf8mb4_bin;

-- 2.教师表-teacher
-- tid 教师编号,tname 教师名称
create table if not exists teacher
(
    tid   int primary key comment '学生编号',
    tname varchar(20) not null comment '学生名字'
) engine = innodb
  charset = utf8mb4
  collate = utf8mb4_bin;

-- 3.课程表-course
-- cid 课程编号,cname 课程名称,tid 教师名称
create table if not exists course
(
    cid   int primary key comment '学生编号',
    cname varchar(20) not null comment '学生名字',
    tid   int         not null
) engine = innodb
  charset = utf8mb4
  collate = utf8mb4_bin;

-- 4.成绩表-score
-- sid 学生编号,cid 课程编号,score 成绩
create table if not exists score
(
    sid   int comment '学生编号',
    cid   int    not null comment '课程编号',
    score double not null
) engine = innodb
  charset = utf8mb4
  collate = utf8mb4_bin;

insert into student
values ('01', '赵雷', '1990-01-01', '男');
insert into student
values ('02', '钱电', '1990-12-21', '男');
insert into student
values ('03', '孙风', '1990-12-20', '男');
insert into student
values ('04', '李云', '1990-12-06', '男');
insert into student
values ('05', '周梅', '1991-12-01', '女');
insert into student
values ('06', '吴兰', '1992-01-01', '女');
insert into student
values ('07', '郑竹', '1989-01-01', '女');
insert into student
values ('09', '张三', '2017-12-20', '女');
insert into student
values ('10', '李四', '2017-12-25', '女');
insert into student
values ('11', '李四', '2012-06-06', '女');
insert into student
values ('12', '赵六', '2013-06-13', '女');
insert into student
values ('13', '孙七', '2014-06-01', '女');

-- 教师表
insert into teacher
values ('01', '张三');
insert into teacher
values ('02', '李四');
insert into teacher
values ('03', '王五');

-- 课程表
insert into course
values ('01', '语文', '02');
insert into course
values ('02', '数学', '01');
insert into course
values ('03', '英语', '03');

-- 成绩表
insert into score
values ('01', '01', 80);
insert into score
values ('01', '02', 90);
insert into score
values ('01', '03', 99);
insert into score
values ('02', '01', 70);
insert into score
values ('02', '02', 60);
insert into score
values ('02', '03', 80);
insert into score
values ('03', '01', 80);
insert into score
values ('03', '02', 80);
insert into score
values ('03', '03', 80);
insert into score
values ('04', '01', 50);
insert into score
values ('04', '02', 30);
insert into score
values ('04', '03', 20);
insert into score
values ('05', '01', 76);
insert into score
values ('05', '02', 87);
insert into score
values ('06', '01', 31);
insert into score
values ('06', '03', 34);
insert into score
values ('07', '02', 89);
insert into score
values ('07', '03', 98);

--查看
SELECT * FROM student;
SELECT * FROM score;
SELECT * FROM teacher;
SELECT * FROM course;

问题展示：

• 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数

#方法一
select a.sid '学生编号', a.cid '科目', a.score '成绩', b.cid '科目', b.score '成绩'
from (select * from score where cid = '01') a,
     (select * from score where cid = '02') b
where a.sid = b.sid  and a.score > b.score;
 
 #方法二
 SELECT * FROM score a,score b where a.sid=b.sid and a.cid='01' and  b.cid='02' AND a.score > b.score;

• 查询同时存在" 01 "课程和" 02 "课程的学生

SELECT * FROM score a,score b where a.sid=b.sid and a.cid='01' and  b.cid='02';

• 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 -1)

select a.sid, a.cid, a.score, ifnull(b.cid, -1), ifnull(b.score, -1) from (select * from score where cid = '01') a left join  (select * from score where cid = '02') b on a.sid = b.sid;

• 查询不存在" 01 "课程但存在" 02 "课程的情况

select a.sid, a.cid, a.score, ifnull(b.cid, -1), ifnull(b.score, -1) from (select * from score where cid = '01') a right join  (select * from score where cid = '02') b on a.sid = b.sid;

• 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩

#方法一
select a.sid,a.sname,AVG(score) from student a inner join score s on a.sid = s.sid  GROUP BY a.sid HAVING AVG(score)>=60;
 
#方法二
select a.sid, a.sname, avg(score) from student a,score b where a.sid = b.sid group by a.sid, a.sname having avg(score) >= 60;

• 查询在t_score表存在成绩的学生信息

SELECT a.sid,sname,score FROM score a left JOIN  student  b on a.sid=b.sid

• 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 -1)

select a.sid, a.sname, count(score), ifnull(sum(score), -1) from student a  left join score s on a.sid = s.sid  group by a.sid;

• 查询「李」姓老师的数量

select count(1) from teacher where tname like '李%';

• 查询学过「张三」老师授课的同学的信息

select a.tname '老师名字', b.cid, c.score, d.sname from teacher a, course b,score c,student d where a.tid = b.tid and b.cid = c.cid and c.sid = d.sid and a.tname = '张三';

• 查询没有学全所有课程的同学的信息

select a.sid, a.sname, count(score) '课程数量' from student a left join score s on a.sid = s.sid
group by a.sid having `课程数量` < (select count(1) from course);

• 查询没学过"张三"老师讲授的任一门课程的学生姓名

select * from student where sid not in ( select sid  from teacher a, course b, score c where a.tid = b.tid and b.cid = c.cid  and a.tname = '张三' );

• 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select sid, count(1) '不及格次数',AVG(score) from score where score < 60 group by sid having `不及格次数` > 1;

• 检索" 01 "课程分数小于 60，按分数降序排列的学生信息

select * from student a,score b where a.sid = b.sid and b.cid = '01' and score < 60 order by score desc;

• 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

select a.sid, a.cid, a.score, b.平均成绩 from score a,(select sid, avg(score) '平均成绩' from score  group by sid) b where a.sid = b.sid order by b.`平均成绩` desc;

• 查询各科成绩最高分、最低分和平均分：
•  以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，
•  优秀率及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
• 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select b.cid , b.cname , max(score) '最高分' , min(score)  '最低分' , avg(score) '平均分', count(score) '学习人数' , sum(if(score >= 60, 1, 0)) / count(score) * 100 '合格率', sum(if(score >= 70 and score < 80, 1, 0)) / count(score) * 100 '中等率' , sum(if(score >= 80 and score < 90, 1, 0)) / count(score) * 100 '优良率', sum(if(score > 90, 1, 0)) / count(score) * 100 '优秀率'from score a,
 course b  where a.cid = b.cid group by a.cid;