摘要
首先,在使用MySQL之前,弄清楚SQL和MySQL是什么关系?SQL它是一种用于操作数据库的语言。SQL是用于所有数据库的基本语言。不同数据库之间存在较小的语法更改,但基本的SQL语法基本保持不变。SQL是S tructured Q uery Language 的简短缩写。MySQL是一种RDBMS,它允许保持数据库中存在的数据。简单地说,SQL是一种查询语言,而MYSQL是数据库软件。本文主要记录MySQL语言的使用方法,为大家提供方便。
1.常用操作数据库的指令
功能实现 | 命令 |
1.查看所有的数据库 | show databases; |
2.创建一个叫datamp的数据库 | create database datamp; |
3.删除一个叫datamp的数据库 | drop database datamp; |
4.选中要使用的数据库 | use datamp; |
5.展示数据库中所有的表 | show tables; |
6.创建名为mp_table的表 | create mp_table(字段1 类型,字段2 类型) |
7.删除名为mp_table的表 | drop table mp_table |
8.查看创建库datamp的详细信息 | show create database datamp |
9.查看创建表datamp的详细信息 | show create table datamp |
2.对表操作的命令
功能实现 | 命令 |
1.修改字段类型 | alter table 表名 modify 字段 字段类型 |
2.添加新的字段 | alter table 表名 add 字段 字段类型 |
3.添加字段并指定位置 | alter table 表明 add 字段 字段类型 after 字段 |
4.删除表字段 | alter table 表名 drop 字段名 |
5.修改指定的字段 | alter table 表名 change 原字段名字 新字段名字 字段类型 |
3.对数据操作的命令
功能实现 | 命令 |
1.增添数据 | 方法1:insert into 表名 values(值1,值2…) |
- | 方法2:insert into 表名(字段1,字段2…) values(值1,值2…) |
- | 方法3:insert into 表名(字段1,字段2…) values(值1,值2…),(值1,值2…) … |
2.删除数据 | delect from 表名 where 条件 |
3.更新数据 | update 表名 set 字段1=值1,字段2=值2 where 条件 |
4.查询数据 | 查询所有数据:select * from 表名 |
- | 查询指定数据:select 字段 from 表名 where 条件 |
5.排序 | select 字段 from 表名 order by 字段(desc/asc默认升序) |
6.常用统计函数 | sum,avg,count,max,min |
7. 取数 | select * from 表名 limit 偏移基准,数量 |
4.多表联合查询
功能实现 | 命令 |
内连接 | select username from user inner join goods on user.gid=goods.gid; |
左连接 | select username from user left join goods on user.gid=goods.gid; |
右连接 | select username from user right join goods on user.gid=goods.gid; |
嵌套查询 | select username from user where gid in(select gid from goods) |
5.实战操作汇总(参考知乎SQL 必会50道面试题)
数据表:
学生表:
Student(s_id,s_name,s_birth,s_sex) –学生编号,学生姓名, 出生年月,学生性别
课程表:
Course(c_id,c_name,t_id) – –课程编号, 课程名称, 教师编号
教师表:
Teacher(t_id,t_name) –教师编号,教师姓名
成绩表:
Score(s_id,c_id,s_s_score) –学生编号,课程编号,分数
创建表:
测试数据
–建表
–学生表
CREATE TABLE Student
(s_id
VARCHAR(20),s_name
VARCHAR(20) NOT NULL DEFAULT ‘’,s_birth
VARCHAR(20) NOT NULL DEFAULT ‘’,s_sex
VARCHAR(10) NOT NULL DEFAULT ‘’,
PRIMARY KEY(s_id
)
);
–课程表
CREATE TABLE Course
(c_id
VARCHAR(20),c_name
VARCHAR(20) NOT NULL DEFAULT ‘’,t_id
VARCHAR(20) NOT NULL,
PRIMARY KEY(c_id
)
);
–教师表
CREATE TABLE Teacher
(t_id
VARCHAR(20),t_name
VARCHAR(20) NOT NULL DEFAULT ‘’,
PRIMARY KEY(t_id
)
);
–成绩表
CREATE TABLE Score
(s_id
VARCHAR(20),c_id
VARCHAR(20),s_score
INT(3),
PRIMARY KEY(s_id
,c_id
)
);
插入数据
–插入学生表测试数据
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-05-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-08-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-03-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-07-01’ , ‘女’);
insert into Student values(‘08’ , ‘王菊’ , ‘1990-01-20’ , ‘女’);
–课程表测试数据
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);
–教师表测试数据
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘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);
如图:
1.查找姓“张”老师的数量
select count(t_name) from teacher where t_name like '张%';
2.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号
select a.s_id "s_no",st.s_name,a.s_score "01",b.s_score "02" FROM
(select s_id,c_id,s_score from score WHERE c_id='01')as a
INNER JOIN
(select s_id,c_id,s_score from score WHERE c_id='02')as b
on a.s_id=b.s_id
INNER JOIN student as st on st.s_id = a.s_id
where a.s_score>b.s_score
3.查询平均成绩大于60分的学生的学号和平均成绩
select s_id,avg(s_score) from score GROUP BY s_id HAVING avg(s_score)>60
4.查询所有学生的学号、姓名、选课数、总成绩
select st.s_id,st.s_name,count(sc.s_id),sum(sc.s_score) from student as st
INNER JOIN score as sc on st.s_id=sc.s_id
GROUP BY st.s_id,st.s_name
5.查询没学过“张三”老师课的学生的信息
select * from student
where s_id not in
(select sc.s_id from teacher as t
INNER JOIN course as c on t.t_id=c.t_id
INNER JOIN score as sc on c.c_id=sc.c_id
where t.t_name='张三')
6.查询学过“张三”老师所教的所有课的同学信息
where s_id in
(select sc.s_id from teacher as t
INNER JOIN course as c on t.t_id=c.t_id
INNER JOIN score as sc on c.c_id=sc.c_id
where t.t_name='张三')
7.查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名
方法1:
select * from student
where s_id in
(select a.s_id from
(select s_id,c_id from score where c_id='02')as a
INNER JOIN
(select s_id,c_id from score where c_id='01') as b
on a.s_id=b.s_id
)
方法2:
select * from student
where s_id in
(select s_id from score where c_id='01')
AND s_id in
(select s_id from score where c_id='02')
8.查询学过编号为“01”的课程且没有学过编号为“02”的课程的学生的信息
select * from student
where s_id in
(select s_id from score where c_id='01')
AND s_id NOT in
(select s_id from score where c_id='02')
9.查询课程编号为“02”的总成绩
select sum(s_score) from score
where c_id='02'
10.查询所有课程成绩小于60分的学生的信息
SELECT st.* from
(select s_id,count(s_id)"cnt" from score
where s_score<60
GROUP BY s_id)
as a
INNER JOIN
(select s_id,COUNT(c_id)"cnt2" from score
group BY s_id)
as b
on a.s_id=b.s_id
INNER JOIN
student as st on st.s_id=b.s_id
where a.cnt=b.cnt2
11.查询没有学全所有课的学生的信息
select st.* from student as st
INNER JOIN score as sc
on sc.s_id=st.s_id
GROUP BY st.s_id,st.s_name HAVING COUNT(c_id)<(select COUNT(DISTINCT c_id)from course)
12.查询至少有一门课与学号为“01”的学生所学课程相同的学生信息
select DISTINCT st.*
from student as st
INNER JOIN score as sc on sc.s_id=st.s_id
where sc.c_id in (select c_id from score where s_id='01')
and st.s_id != '01'
13.查询和“01”号同学所学课程完全相同的其他同学的信息
select DISTINCT st.s_id,st.s_name
from student as st
INNER JOIN score as sc on sc.s_id=st.s_id
GROUP BY st.s_id HAVING COUNT(sc.c_id) in (select count(c_id) from score where s_id='01')
and st.s_id != '01'
14.查询没学过"张三"老师讲授的任一门课程的学生信息
select *
from student
where s_id not in
(select s_id from score as sc
INNER JOIN
course as c on c.c_id=sc.c_id
INNER JOIN
teacher as t on t.t_id=c.t_id
where t_name = '张三'
)
15.查询两门及其以上不及格课程的同学的学号,姓名及其平均成绩
select st.* from student as st
INNER JOIN score as sc on st.s_id=sc.s_id
where sc.s_score<60
GROUP BY st.s_id
HAVING COUNT(c_id)>=2
16.检索"01"课程分数小于60,按分数降序排列的学生信息
select st.*,sc.* from student as st
INNER JOIN score as sc on sc.s_id=st.s_id
where sc.c_id='01' and sc.s_score<60
ORDER BY sc.s_score DESC
17.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s_id,avg(s_score),
MAX(case when c_id='01' then s_score else null end)"语文" ,
MAX(case when c_id='02' then s_score else null end)"数学" ,
MAX(case when c_id='03' then s_score else null end)"英语"
from score
group BY s_id
ORDER BY avg(s_score) desc
18.查询各科成绩最高分、最低分和平均分:以如下形式显示:课程ID,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
–及格为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
–体会下avg,sum的使用区别
select c.c_id'课程id',
c.c_name'课程名称',
max(sc.s_score)'最大值',
min(sc.s_score)'最小值',
avg(sc.s_score)'平均值',
sum(case WHEN sc.s_score>=60 then 1 else 0 end)/count(s_id)"及格",
avg(case when sc.s_score>=70 and sc.s_score<80 then 1 else 0 end) "中等",
avg(case when sc.s_score>=80 and sc.s_score<90 then 1 else 0 end) "优良",
avg(case when sc.s_score>=90 then 1 else 0 end) "优秀"
from score as sc
INNER JOIN course as c
on c.c_id=sc.c_id
GROUP BY c.c_id
19.查询学生的总成绩并进行排名
select s_id,sum(s_score) from score
GROUP BY s_id
ORDER BY sum(s_score) DESC
20.查询不同老师所教不同课程平均分从低到高显示
# 以课程为主题
select sc.c_id,c.c_name,avg(sc.s_score)
from score as sc
INNER JOIN course as c
on c.c_id=sc.c_id
GROUP BY sc.c_id
ORDER BY avg(sc.s_score)
# 以老师为主题
select t.t_id,t.t_name,avg(sc.s_score)
from score as sc
INNER JOIN course as c
on c.c_id=sc.c_id
INNER JOIN teacher as t
on t.t_id=c.t_id
GROUP BY t.t_id
ORDER BY avg(sc.s_score)
21.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分数段人数:课程ID和课程名称
select c.c_id,c.c_name,
sum(case when sc.s_score<=100 and sc.s_score>85 then 1 else 0 end)"[100,85)",
sum(case when sc.s_score<=85 and sc.s_score>70 then 1 else 0 end)"[85,70)",
count(CASE when sc.s_score<=70 and sc.s_score>60 then 1 else null end)"[70,60)",
count(CASE when sc.s_score<=60 then 1 else null end)"[60,0)"
from score as sc
INNER JOIN course as c on sc.c_id=c.c_id
GROUP BY c.c_id
22.查询每门课程被选修的学生数
select c_id,count(c_id) from score
group BY c_id
23.查询出只有两门课程的全部学生的学号和姓名
select st.* from score as sc
INNER JOIN student as st
on st.s_id=sc.s_id
GROUP BY st.s_id HAVING COUNT(sc.c_id)=2
24.查询男生、女生人数
select count(s_id)
from student
GROUP BY s_sex
25.查询1990年出生的学生名单
#方法1
select * from student
where YEAR(s_birth)=1990
# 方法2
select * from student
where s_birth like '1990%'
26.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select st.s_id,st.s_name,avg(sc.s_score) from student as st
INNER JOIN score as sc on sc.s_id=st.s_id
GROUP BY st.s_id HAVING avg(sc.s_score)>85
27.查询所有学生的课程及分数情况
select sc.s_id,
max(case WHEN c.c_name="语文" then sc.s_score else null end)"语文",
max(case WHEN c.c_name="数学" then sc.s_score else null end)"数学",
max(case WHEN c.c_name="英语" then sc.s_score else null end)"英语"
from score as sc
INNER JOIN course as c
on c.c_id=sc.c_id
GROUP BY sc.s_id
28.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
select a.*,b.*,c.*
from
(select s_id,c_id,s_score from score where c_id='01')as a
INNER JOIN
(select s_id,c_id,s_score from score where c_id='02')as b
on a.s_id=b.s_id
INNER JOIN
(select s_id,c_id,s_score from score where c_id='03')as c
on b.s_id=c.s_id
WHERE a.s_score=b.s_score and b.s_score=c.s_score
29. 查询选修了全部课程的学生信息
select s_id,count(c_id) from score
GROUP BY s_id
HAVING count(c_id)=(select count(*) from course)
30.查询5月过生日的学生
select * from student
where MONTH(s_birth)=MONTH("2022/5/10")
到此,我完整地复现了MySQL的重要操作,方便大家学习,希望对大家有所帮助。