记录的增删改查

学习目录

• 使用INSERT实现数据的插入
• UPDATE实现数据的更新
• 使用DELETE实现数据的删除
• 使用SELECT查询数据以及
• 单表查询
• 多表查询

1 插入数据--insert

1.插入完整数据
语法一
insert into 表名(字段1,字段2,字段3) values(值1,值2,值3)
实例:
insert into t1(id,name,age) values (1,'alex',23)
语法二
insert into 表名values(值1,值2,值3)
实例:
insert into t1 values (1,'alex',23)

2.指定字段插入数据
语法
insert into 表名(字段2,字段3) values(值2,值3)
实例:
insert into t1(name,age) values('alex',23)

3.插入多条数据
语法
insert into 表名 values(值1,值2,值3),
					   (值4,值5,值6),
					   (值7,值8,值9);
实例
insert into t1 values(1,'alex',73),
						(2,'ailsa',23),
						(3,'wusir',84);

4.插入查询结果
语法
insert into 表名(字段1,字段2,字段3)
				select 字段1,字段2,字段3 from 表2 where 条件;
实例			
insert into emp(id,name) select id,name from emp_bak 
where id = 1;

2 更新数据--update

单独更新一个字段
语法:
	update 表名 set 字段1 = 新值1,
			where 条件;
实例:
	update s1 set name = 'ailsa',
		       where id = 1;

更新多个字段
语法:
	update 表名 set 字段1 = 新值1,
			字段2 = 新值2
			where 条件;
实例:
	update s1 set name = 'ailsa',
		      gender = 'female',
		       where id = 1;

3 删除数据--delete

语法:
	delete from 表名 where 条件;
	delete from s1 where id = 2;
注:
	不加where条件就是清空表,一定要慎重使用delete

清空表：
       delete from t1; # 如果有自增id，新增的数据，仍然是以删除前的最后一样作为起始。
       truncate table t1;# 数据量大，删除速度比上一条快，且直接从零开始，

drop table 表名 是把整个表删除，在数据库中找不到该表，而delete只是把表中的数据清除，表结构还在。

4 查询数据--select

4.1 单表查询

4.1.1 单表查询的完整语法及优先级

语法:
select distinct 字段1,字段2 [,...] from 表名
				where 条件
				group by field
				having 筛选条件
				order by filed
				limit 条数
注:
   group by field 根据什么进行分组,一般是某个字段或多个字段
   order by filed 根据什么进行排序,一般是某个字段或多个字段
   having主要配合group by使用,对分组后的数据进行过滤,里面可以使用聚合函数
   where是针对select查询的过滤,各有区别和用处
   
优先级:
	from
	where
	group by
	select
	distinct
	having
	order by
	limit
解释说明:
	1.先找到表:from
	2.拿着where指定的约束条件,去表中取出符合条件的一条条数据
	3.将取出的数据进行分组group by,如果没有group by,则每行为一组
	4.执行select 查询所指定的字段
	5.若有distinct 则去重
	6.将结果按照条件排序 order by
	7.限制结果的显示条数 limit

4.1.2 准备数据

create table emp(
	id int primary key auto_increment,
	emp_name char(20) not null,
	sex enum("male","female") not null default "male",
	age int(3) unsigned not null default 28,
	hire_date date not null,
	post char(50),
	post_comment char(100),
	salary double(15,2),
	office int,
	depart_id int);
	
插入数据
insert into emp(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('huahua','male',18,'20170301','teacher',7300.33,401,1), #以下是教学部
('weiwei','male',78,'20150302','teacher',1000000.31,401,1),
('lala','male',81,'20130305','teacher',8300,401,1),
('zhangsan','male',73,'20140701','teacher',3500,401,1),
('liulaogen','male',28,'20121101','teacher',2100,401,1),
('aal','female',18,'20110211','teacher',9000,401,1),
('zhugelang','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)

4.1.3 简单查询

# 查询所有的字段信息
select * from emp;

# 查询指定字段信息
select id,emp_name from emp;

#避免重复distinct
select distinct post from emp;

# 通过四则运算查询[+ - * /]
select emp_name,salary*12 from emp;
为字段重命名
select emp_name,salary*12 as annual_salary from emp;
注: as 可以省略
	
# 定义显示格式
1. concat()用于连接字符串
select concat("姓名: ",emp_name," 年薪: ",salary*12) as annual_salary from emp;
2.concat_ws() 第一个参数为分隔符
select concat_ws(":",emp_name,salary*12) as annual_salary from emp;

# 结合case语句
select (case 
	when emp_name = "jingliyang" then emp_name
	when emp_name = "alex" then concat(emp_name,"_sb")
	else
	concat(emp_name,"_NB")
	end	
) as new_name
from emp;

4.1.4 where

where 字句中可以使用:
1.比较运算符:> <  >= <=  <>  !=

2.between...and ,between 80 and 100,在80到100之间,闭区间

3.in("a","b","c") 是否属于括号内的元素,满足其中一个就可以

4.like "%好" 

​	通配符:

​	% 表示任意字符(0个到n个)

​	_ 表示一个字符

5.逻辑运算符:多条件可以使用逻辑运算符 and or not

实例

# 单条件查询
select emp_name,post from emp where post="sale";

# 多条件查询
select emp_name,salary from emp where post = "teacher" and salary > 10000;

# between...and
select emp_name,salary from emp where salary between 1000 and 5000;

# is null判断是否为空,不能用=,而是用is
select emp_name,post_comment from emp where post_comment is  null;

select emp_name,post_comment from emp where post_comment ="";
ps:
	查询都没有结果,我们手动修改一下数据
	update emp set post_comment="" where id = 1;
	
注:空字符串与null是不一样的,有兴趣可以自己查一查

# in
select emp_name,salary from emp where salary=3000 or salary=3500 or salary=4000 or salary=9000;

select emp_name,salary from emp where salary in (3000,3500,4000,9000);

select emp_name,salary from emp where salary not in (3000,3500,4000,9000);

# like

通配符 %
select * from emp where emp_name like "a%";
通配符 _
select * from emp where emp_name like "zhang___";

4.1.5 group by

# 单独使用group by关键字分组
select post from emp group by post;
注:
	使用group by,查询字段必须是分组字段,想要获取其他信息,可以借助聚合函数,
        一般情况下,group by是和聚合函数配合使用的.
	错误实例:
	select id,post,emp_namefrom emp group by post;
        # 分组的字段跟查询字段要保持一致，不然数据对应不上
# 与group_concat()函数搭配使用
select post,group_concat(emp_name) from emp group by post;
# 与聚合函数一起使用
select post,count(id) count_num from emp group by post;

聚合函数
count 计数
sum 求和
avg 求平均
max 最大值
min 最小值

4.1.6 having

where 与 having的区别
where 是针对分组之前的字段内容进行过滤,而having是针对分组后的
实例:
select emp_name,post from emp where salary>5000;
错误实例:
select post,group_concat(emp_name) 人员 from emp group by  post having salary>1000;# 会报错,因为分组之后就没有salary这个字段了,所以查不到;
# 正确实例
select post,group_concat(emp_name) 人员 from emp group by  post having post="teacher";

4.1.7 order by

升序 order by 字段 asc(默认升序,可以不写)
降序 order by 字段 desc

# 单列排序
select id,emp_name,post,salary from emp order by salary desc;

# 多列排序,越前面的优先级越高
select id,emp_name,age,post,salary from emp order by age,salary desc;
# 先按照年纪排序,年纪相同的话按照salary从高到低排序

4.1.8

# 显示前三条,默认从0开始
select id,emp_name,post from emp limit 3;
# 从0开始,先查出第一条,然后包含这条再往后查5条
select id,emp_name,post from emp limit 0,5;
# 从第3开始,即先查出第4条,然后包含这条再往后查7条
select id,emp_name,post from emp limit 3,7;

4.1.9 使用正则

# ^ 以什么开头
select id,emp_name,post from emp where emp_name regexp "^a";
# $ 以什么结尾
select id,emp_name,post from emp where emp_name regexp "zhang$";
# 匹配指定字符内容
select id,emp_name,post from emp where emp_name regexp "i{1}";

4.2 多表查询

数据准备

create database day04;
create table dep(
	id int primary key,
	name char(20)
);
create table emp(
	id int primary key auto_increment,
	name char(20),
	sex enum("male","female") not null default "male",
	age int,
	dep_id int
);

# 插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('ailsa','male',18,200),
('lala','female',48,201),
('huahua','male',38,201),
('zhangsan','female',28,202),
('zhaosi','male',18,200),
('shenteng','female',18,204)
;

4.2.1 多表联查

1.交叉连接:笛卡尔积 把表合并,没有任何条件

A表中的每一条记录都与B表相连,没有任何指定条件
select * from emp,dep;

2.内连接:join

两张表公共的部分,必须同时有,没有就不显示
select emp.id,emp.name,emp.age,dep.name from emp join dep on emp.dep_id = dep.id;

3.外连接之左连接

以左表为主表,根据左表数据匹配右表,左表的数据是全的,而右表若匹配不上则显示null
select emp.id,emp.name,emp.age,dep.name from emp left join dep on emp.dep_id = dep.id;

4.外连接之右连接

以右表为主表,根据右表数据匹配左表,右表数据是全的,而左表若匹配不上则为null
select emp.id,emp.name,emp.age,dep.name from emp right join dep on emp.dep_id = dep.id;

5.全外连接

把左右两边的数据都显示完整,匹配不上的则显示为null,在mysql中没有真正的全外连接,可以使用union来实现
select emp.id,emp.name,emp.age,dep.name from emp left join dep on emp.dep_id = dep.id
union
select emp.id,emp.name,emp.age,dep.name from emp right join dep on emp.dep_id = dep.id;

#注 union与union all的区别：union会去掉相同的纪录

4.2.2 符合条件的多表连查

 #示例1：以内连接的方式查询emp和dep表，并且emp表中的age字段值必须大于25,
即找出年龄大于25岁的员工以及员工所在的部门
select emp.name,dep.name from emp inner join dep on emp.dep_id = dep.id
    where age > 25;

#示例2：以内连接的方式查询emp和dep表，并且以age字段的升序方式显示
select emp.id,emp.name,emp.age,dep.name from emp join dep on emp.dep_id = dep.id
    where age > 25
    order by age;

4.2.3 子查询

1.子查询是将一个查询语句嵌套在另一个查询语句中
2.内层查询语句的查询结果,可以为外层查询语句提供查询条件
3.子查询中可以包含: in   not in  any   all  exists  not exists 等关键字
4.还可以包含比较运算符: = != > <等

1.带in关键字的子查询

# 1.查询平均年龄在25岁以上的部门名
select id,name from dep where id in (select dep_id from emp group by dep_id having avg(age) > 25);

#2.查看技术部员工姓名
子查询方式
select id,name from emp where dep_id = (select id from dep where name="技术");
连表方式
select emp.id,emp.name,dep.name from emp left join dep on emp.dep_id=dep.id
where dep.name="技术";

# 3.查看不足1人的部门名(子查询得到的是有人的部门id)-->换句话说就是查询没有人的部门
select id,name from dep where id not in(select dep_id from emp group by dep_id having count(id)>=1 );

2.带比较运算符的子查询

比较运算符: =、!=、>、>=、<、<=、<>

# 1.查询大于所有人平均年龄的员工与年龄
select name,age from emp where age>(select avg(age) from emp);
# 2.查询大于部门内平均年龄的员工姓名,年龄
select name,age from emp t1 join (select dep_id,avg(age) avg_age from emp group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;

3.带exists关键字的子查询

exists关键字表示存在,在使用exists关键字时,内层查询不返回查询的记录,而是返回一个真假值,True或False

当返回True时,外层查询语句将进行查询;当返回值为False时,外层查询语句不进行查询

# dep表中是否存在dep_id=203
select * from emp where exists (select id from dep where id=203);

# dep表中是否存在dep_id=205
select * from emp where exists (select id from dep where id=205);

5 SQL多表查询练习题

1 准备数据

CREATE TABLE class (
  cid int(11) NOT NULL AUTO_INCREMENT,
  caption varchar(32) NOT NULL,
  PRIMARY KEY (cid)
) ENGINE=InnoDB CHARSET=utf8;

INSERT INTO class VALUES
(1, '三年二班'), 
(2, '三年三班'), 
(3, '一年二班'), 
(4, '二年九班');

CREATE TABLE teacher(
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) NOT NULL,
  PRIMARY KEY (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO teacher VALUES
(1, '张磊老师'), 
(2, '李平老师'), 
(3, '刘海燕老师'), 
(4, '朱云海老师'), 
(5, '李杰老师');

CREATE TABLE course(
  cid int(11) NOT NULL AUTO_INCREMENT,
  cname varchar(32) NOT NULL,
  teacher_id int(11) NOT NULL,
  PRIMARY KEY (cid),
  KEY fk_course_teacher (teacher_id),
  CONSTRAINT fk_course_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO course VALUES
(1, '生物', 1), 
(2, '物理', 2), 
(3, '体育', 3), 
(4, '美术', 2);

CREATE TABLE student(
  sid int(11) NOT NULL AUTO_INCREMENT,
  gender char(1) NOT NULL,
  class_id int(11) NOT NULL,
  sname varchar(32) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_class (class_id),
  CONSTRAINT fk_class FOREIGN KEY (class_id) REFERENCES class (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES
(1, '男', 1, '理解'), 
(2, '女', 1, '钢蛋'), 
(3, '男', 1, '张三'), 
(4, '男', 1, '张一'), 
(5, '女', 1, '张二'), 
(6, '男', 1, '张四'), 
(7, '女', 2, '铁锤'), 
(8, '男', 2, '李三'), 
(9, '男', 2, '李一'), 
(10, '女', 2, '李二'), 
(11, '男', 2, '李四'), 
(12, '女', 3, '如花'), 
(13, '男', 3, '刘三'), 
(14, '男', 3, '刘一'), 
(15, '女', 3, '刘二'), 
(16, '男', 3, '刘四');

CREATE TABLE score (
  sid int(11) NOT NULL AUTO_INCREMENT,
  student_id int(11) NOT NULL,
  course_id int(11) NOT NULL,
  num int(11) NOT NULL,
  PRIMARY KEY (sid),
  KEY fk_score_student (student_id),
  KEY fk_score_course (course_id),
  CONSTRAINT fk_score_course FOREIGN KEY (course_id) REFERENCES course (cid),
  CONSTRAINT fk_score_student FOREIGN KEY (student_id) REFERENCES student(sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO score VALUES
(1, 1, 1, 10),
(2, 1, 2, 9),
(5, 1, 4, 66),
(6, 2, 1, 8),
(8, 2, 3, 68),
(9, 2, 4, 99),
(10, 3, 1, 77),
(11, 3, 2, 66),
(12, 3, 3, 87),
(13, 3, 4, 99),
(14, 4, 1, 79),
(15, 4, 2, 11),
(16, 4, 3, 67),
(17, 4, 4, 100),
(18, 5, 1, 79),
(19, 5, 2, 11),
(20, 5, 3, 67),
(21, 5, 4, 100),
(22, 6, 1, 9),
(23, 6, 2, 100),
(24, 6, 3, 67),
(25, 6, 4, 100),
(26, 7, 1, 9),
(27, 7, 2, 100),
(28, 7, 3, 67),
(29, 7, 4, 88),
(30, 8, 1, 9),
(31, 8, 2, 100),
(32, 8, 3, 67),
(33, 8, 4, 88),
(34, 9, 1, 91),
(35, 9, 2, 88),
(36, 9, 3, 67),
(37, 9, 4, 22),
(38, 10, 1, 90),
(39, 10, 2, 77),
(40, 10, 3, 43),
(41, 10, 4, 87),
(42, 11, 1, 90),
(43, 11, 2, 77),
(44, 11, 3, 43),
(45, 11, 4, 87),
(46, 12, 1, 90),
(47, 12, 2, 77),
(48, 12, 3, 43),
(49, 12, 4, 87),
(52, 13, 3, 87);

2 练习题

1、查询所有的课程的名称以及对应的任课老师姓名

2、查询学生表中男女生各有多少人

3、查询物理成绩等于100的学生的姓名

4、查询平均成绩大于八十分的同学的姓名和平均成绩

5、查询所有学生的学号，姓名，选课数，总成绩

6、 查询姓李老师的个数

7、 查询没有报李平老师课的学生姓名

8、 查询物理课程比生物课程高的学生的学号

9、 查询没有同时选修物理课程和体育课程的学生姓名

10、查询挂科超过两门(包括两门)的学生姓名和班级

11、查询选修了所有课程的学生姓名

12、查询李平老师教的课程的所有成绩记录

13、查询全部学生都选修了的课程号和课程名

14、查询每门课程被选修的次数

15、查询只选修了一门课程的学生姓名和学号

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）

17、查询平均成绩大于85的学生姓名和平均成绩

18、查询生物成绩不及格的学生姓名和对应生物分数

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名

20、查询每门课程成绩最好的前两名学生姓名

答案

建议先自己做，然后再对照答案哦，特殊说明一下，SQL写法有很多种，所以只要结果一致，不必纠结是否一模一样。

1、查询所有的课程的名称以及对应的任课老师姓名
select c.cid,c.cname,t.tname from course  c left join teacher t on c.teacher_id = t.tid ;

2、查询学生表中男女生各有多少人
select gender,count(1) 人数 from student group by gender;

3、查询物理成绩等于100的学生的姓名
select sname,num from student  s left join score s1 on s.sid = s1.student_id
left join course c on s1.course_id = c.cid
where c.cname = "物理" and s1.num = 100;

4、查询平均成绩大于八十分的同学的姓名和平均成绩
select sname,avg(num) 平均成绩 from student s left join score s1 on s.sid = s1.student_id group by sname having avg(num)>=80;

5、查询所有学生的学号，姓名，选课数，总成绩
select s.sid,s.sname,count(1) 课程数,sum(num) 总成绩 from student s left join score s1 on s.sid = s1.student_id
where num > 0
group by s.sid,s.sname ;

6、 查询姓李老师的个数
select count(1) 个数 from teacher where tname like "李%";

7、 查询没有报李平老师课的学生姓名
select sname from student where sname not in
(select distinct sname from student s 
left join score s1 on s.sid = s1.student_id
left join course c on s1.course_id = c.cid
left join teacher t on t.tid = c.teacher_id
where t.tname = "李平老师");

8、 查询物理课程比生物课程高的学生的学号
select t1.student_id from 
(select student_id,num from score s1
left join course c on s1.course_id = c.cid
where cname = "物理") t1
join 
(select student_id,num from score s1
left join course c on s1.course_id = c.cid
where cname = "生物") t2
on t1.student_id = t2.student_id
where t1.num > t2.num;

9、 查询没有同时选修物理课程和体育课程的学生姓名
# 第一种写法
select * from student where sid in(
select student_id from score where course_id in (select cid from course where cname = "物理" or cname = "体育")
group by student_id having count(student_id) = 1
);
# 第二种写法
select * from student where sid in(
select student_id from score s left join course c on s.course_id = c.cid
where cname = "物理" or cname = "体育"
group by student_id having count(student_id) = 1
);

10、查询挂科超过两门(包括两门)的学生姓名和班级
select s.sname,count(1) 个数 from student s
join score s1 on s.sid = s1.student_id
where num < 60 group by sname having count(1)>=2;
11 、查询选修了所有课程的学生姓名
select distinct sname,s1.student_id from student  s join score s1 on s.sid=s1.student_id
group by sname,s1.student_id having count(1) >= (select count(cid) from course);
11、查询选修了所有课程的学生姓名
select sname from student s
join score s1 on s.sid = s1.student_id
group by sname having count(student_id) =
(select count(1) from course);

12、查询李平老师教的课程的所有成绩记录
select s.student_id,c.cname,s.num from score s
left join course c on s.course_id = c.cid
left join teacher t on t.tid = c.teacher_id
where t.tname = "李平老师";
 
13、查询全部学生都选修了的课程号和课程名
select course_id,cname from score s
left join course c on s.course_id = c.cid
group by course_id
HAVING count(1) = (select count(1) from student);

14、查询每门课程被选修的次数(每门课有多少学生选择)
select cname,count(1) 次数 from score s
left join course c on s.course_id = c.cid
group by cname;
	
15、查询只选修了一门课程的学生姓名和学号
select student_id,sname from score s
join student s1 on s.student_id = s1.sid
group by student_id,sname
having count(1) = 1;

16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
select distinct num from score order by num desc;

17、查询平均成绩大于85的学生姓名和平均成绩
select sname,avg(num) 平均成绩 from score s
join student s1 on s.student_id = s1.sid
group by sname
having avg(num)>85;

18、查询生物成绩不及格的学生姓名和对应生物分数
select sname,num from score s
left join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid
where cname = "生物" and num < 60;

19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
select sname,avg(num) 平均成绩 from score s
left join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid
left join teacher t on c.teacher_id = t.tid
where tname = "李平老师"
group by sname 
order by avg(num) desc 
limit 1;

# 第二种方法
select sname,avg(num) 平均成绩 from score s
left join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid
left join teacher t on c.teacher_id = t.tid
where tname = "李平老师"
group by sname
having avg(num)=
(select max(平均成绩) from (select sname,avg(num) 平均成绩 from score s
left join student s1 on s.student_id = s1.sid
left join course c on s.course_id = c.cid
left join teacher t on c.teacher_id = t.tid
where tname = "李平老师"
group by sname) t);

20、查询每门课程成绩最好的前两名学生姓名
# 拿到第一名 union 第二名
select student_id,s.course_id,num from score s
join 
(select max(num) max_num,course_id from score group by course_id) t
on s.course_id = t.course_id
where num=max_num
union
select student_id,s.course_id,max(num) second_num from score s
join 
(select max(num) max_num,course_id from score group by course_id) t
on s.course_id = t.course_id
where num<max_num
group by s.course_id;