MySQL笔记

该笔记是在2022年2-4月企业实训时所做，用于复习查阅。

一、mysql语言的分类

​ DDL:数据库定义语言，用于定义数据库对象（数据库，表，列）
​ DML:数据库操作语言，包括对数据库表中数据的增删改操作
​ DCL:数据库控制语言，包含用户权限，安全级别等等
​ DQL:数据库查询语言，包括对数据库表中数据的查询操作

（一）DDL(数据库定义语言)

查询root用户下所有的数据库

show databases;

创建数据库 格式：create database [if not exists] 数据库名;

create database stu506;

使用数据库 格式：use 数据库名;

use stu506;

删除数据库 格式：drop database [if exists] 数据库名;

drop database stu506;

查看所有数据库编码

show variables like '%character%';

查看当前数据库编码

show variables like '%character_set_database%';

修改数据库编码

alter database stu506 character set utf8;

​ DDL中的查看一般都是show
​ 删除一般都是drop
​ 增删改一般都是alter

查看当前使用的数据库中所有的表

show tables;

查看创建某张表的语句

show create table t_test1;

查看表结构

desc t_test2;

删除表

drop table t_test3;

创建学生表

create table student(
	id int primary key auto_increment,
    sname varchar(50),
    sex varchar(10)
);

查看表结构

desc student;

修改表结构（表结构：字段和字段类型）

-- alter table 表名 ...
-- 向表中添加字段 
alter table student add column age int;
-- 修改字段名称  
alter table student change sex gender varchar(50);
-- 修改字段类型  
alter table student modify gender char(2);
-- 删除字段  
alter table student drop age;
-- 修改表名  
alter table student rename to stu;

（二）DML(数据库操作语言)

插入数据

-- 向表中所有字段插入数据  
	-- insert into 表名 values(按照表中字段的顺序以及类型设置值);
    insert into stu values(null,'于谦','男');
    desc stu;
    select * from stu;
-- 向表中指定字段插入数据
    -- insert into 表名(字段1,字段2,...) values(字段值1,字段值2,...);
    insert into stu(sname,gender) values('郭德纲','男');

修改数据

-- update 表名 set 字段1=新字段值1,字段2=新字段值2,... where 条件;
  update stu set sname='岳云鹏' where id=1;
  -- 将表中所有数据全部修改  
  update stu set sname='岳云鹏';
  
  update student set name='郑七',age=20,gender='女' where id=5;

删除数据

-- 将表中数据全部删除  
    -- delete from 表名;
    delete from stu;
    -- 根据条件删除数据 
	-- delete from 表名 where 条件; 
    delete from stu where id=2;

（三）DQL(数据库查询语言)

语法：
select 展示的字段名 from 表名
where condition – 行条件
group by 字段名 – 对结果进行分组
having condition – 分组后的行条件
order by 字段名 – 对结果进行排序
limit m,n; – 结果限定（分页）

--语法总结： （严格执行）
	select-from-where-group by-having-order by-limit

1、基础查询

– 查询所有列

select * from emp;

– 查询指定列

select empno,ename,sal from emp;

as 表示别名 可以省略
ifnull(a,b) 如果a为null，用b来代替
– 查询员工月薪和奖金之和

select *,sal+ifnull(comm,0) as 工资和 from emp;
select *,sal+ifnull(comm,0) 工资总和 from emp;

排序查询

关键字： order by 字段名 [asc] 升序排序
– order by 字段名[desc] 降序排序
– 查询所有的学生记录，按照年龄进行升序排序

select * from student order by age asc;

– 查询所有的学生记录，按照年龄进行降序排序

select * from student order by age desc;

– 查询所有员工信息，按照月薪进行降序排序，如果月薪相同，按照员工编号进行升序排序
/*排序时，不需要我们做判断，数据库会自动识别 */

select * from emp order by sal desc,empno asc;

where后面跟的是条件，说白了就是限制条件（筛选条件）
and 和 表示必须同时满足一些列的条件 &&

or 表示或者 || 说白了就是只需要满足其中一个条件就可以

in 表示包含的意思，in里面存储的为一个set集合，不能重复

not in 不包含

is null 为空
is not null 不为空

!= 表示不等

select * from student where gender='female' and age<50;
-- 查询学号为1001或者姓名为lisi的学生记录  
/*or 表示或者 ||    说白了就是只需要满足其中一个条件就可以  */
select * from student where sid=1001 or sname='liSi';
-- 查询学号为1001 1002 1003 1005 1009的记录  
select * from student where sid=1001 or sid=1002 or sid=1003 or sid=1005 or sid=1009;
/*in 表示包含的意思，in里面存储的为一个set集合，不能重复 
所以里面只能存储主键id，查询时没有那么它就不显示*/
select * from student where sid in(1001,1002,1003,1005,1009);
-- 查询学号不为1001 2 3的学生信息 
/*not in 不包含*/ 
select * from student where sid not in(1001,1002,1003);
/*
is null  为空 
is not null 不为空 
*/  
-- 查询年龄为null的学生记录 
select * from student where age is null;
-- 查询年龄不为null的学生记录 
select * from student where age is not null;
-- 查询年龄在20-40之间的学生记录  
select * from student where age>20 and age<40;
/*between m and n 表示在m和n之间*/
select * from student where age between 20 and 40;
-- 查询性别非男的学生记录  
/* != 表示不等  */
select * from student where gender != 'male';
select * from student where not gender = 'male';

模糊查询

关键字 like 单个任意字符_ 多个任意字符%

/* _表示一个字符，字符可以是任意字符*/ 
select * from student where sname like '_____';
-- 查询姓名有五个字母组成并且第五个字母为i的学生记录  
select * from student where sname like '____i';
-- 查询姓名以z开头的学生记录 
/* %表示0-n个任意字符，n可以为无限大 */ 
select * from student where sname like 'z%';
-- 查询姓名中第二个字母为i的学生记录  
select * from student where sname like '_i%';
-- 查询姓名中包含a的学生记录  
select * from student where sname like '%a%';

聚合函数：

​ count(字段名):返回非null值的记录条数
​ sum(字段名):返回指定字段的所有值的和
​ max(字段名):返回指定字段中的最大值
​ min(字段名):返回指定字段中的最小值
​ avg(字段名):返回指定字段中的平均值

分组查询

关键字：group by 字段名;

group by的执行过程:
1.根据group by指定的字段对表中的数据进行分组
2.根据指定的聚合函数将分组后的每组中若干条记录找出一条记录
3.将2中查出来记录与分组字段结合在一起

select deptno,sum(sal) from emp group by deptno;
-- 查询每个部门的部门编号以及每个部门的人数 
select deptno,count(empno) from emp group by deptno;
-- 查询每个部门的部门编号以及部门工资大于1500的人数  
-- 先过滤，后分组   
select deptno,count(empno) from emp where sal>1500 group by deptno;
-- 查询工资总和大于9000的部门编号以及工资和  
-- 先分组求合，然后根据条件进行过滤 
select deptno,sum(sal) total from emp group by deptno having total>9000;

where和having都是用来进行条件过滤的
但是where不能在分组后面使用，也就是说不能再在group by后面使用
如果group by后面需要进行条件过滤，我们需要使用having

分页查询

​ limit m,n
​ m表示查询的起始行（舍弃前m条记录[包括]）
​ n表示查询的总行数

-- 要求对员工表进行分页查询，每页五条记录  
select * from emp limit 0,5;
-- 查询第二页 
select * from emp limit 5,5;
/*limit属于MySQL中的方言，别的数据库没有*/

2、约束

– 约束：

• 主键约束，

• 主键自增约束，

• 非空约束，

• 外键约束，

• 唯一性约束

  – 主键约束和唯一性约束的区别：

  一个表中只能有一个主键约束，而可以有多个唯一性约束

  列级约束：在列名后面直接添加对应的约束，与表一起创建的约束

create table stu1(
		--  主键约束(非空+唯一)     主键自增约束 
	sid int primary key auto_increment,
		--             非空约束  
    sname varchar(50) not null,
    age int,
    gender varchar(10)
);
create table stu1(
		--  主键约束     主键自增约束 
	sid int auto_increment,
		--             非空约束  
    sname varchar(50) not null,
    age int,
    gender varchar(10),
    primary key(sid)
);

-- 表级约束：表已经创建好了，让那后我们对表中列进行添加或者删除约束的操作  
-- DDL
-- 修改表时删除主键自增长  
alter table stu1 change sid sid int;
-- 修改表时添加主键自增长 
alter table stu1 change sid sid int auto_increment;
-- 删除主键约束  
/*
注意：删除主键时，必须要先删除主键自增长约束
*/
alter table stu1 drop primary key;
-- 修改表时添加主键约束  
alter table stu1 add primary key(sid);
-- 添加数据 
/*添加时因为已经声明姓名为非空了，当没有值时，添加失败*/ 
insert into stu1(sid,age,gender) values(1,23,'男');
-- 查询  
select * from stu1;
-- 修改表时删除非空约束   
alter table stu1 modify sname varchar(50);
-- 修改表时添加非空约束，并指定一个默认值   
alter table stu1 modify sname varchar(50) not null default 'admin';

-- 唯一性约束：添加唯一性约束的字段，字段值不能重复的    
create table stu2(
	tid int primary key auto_increment,
    tname varchar(50) unique    -- 唯一性约束   unique
);
desc stu2;
-- 插入数据 
insert into stu2 values(null,'奥迪民');
-- 查询 
select * from stu2;

-- 外键约束   

-- 创建班级表  
-- 主表：外键关联的主键所在的表   
create table t_calss(
	cid int primary key auto_increment,-- 班级编号 
    cname varchar(50)   -- 班级名称
);
select * from t_calss;

delete from t_calss where cid=1;
-- 学生表  
-- 从表：外键所在的表 
/*
我们在设置外键的时候，通常回去关联另外一个表的列
与外键关联的列必须要有主键约束 
反过来说就是只有具有主键约束的列才能关联外键   
*/ 
create table t_stu(
	sid int primary key auto_increment,
    sname varchar(50),
    age int,
    gender varchar(10),
    c_id int,  -- 所在班级编号   
    --     外键名称   给指定字段添加外键约束  关联主表中的主键字段
    constraint fk_cid foreign key(c_id) references t_calss(cid)
);
desc t_stu;
-- 修改表时删除外键约束  
alter table t_stu drop foreign key fk_cid;
-- 修改表时添加外键约束  
alter table t_stu add constraint fk_cid foreign key(c_id) references t_calss(cid)
--  级联删除      级联修改 
on delete cascade on update cascade;
-- 添加信息  
/*如果向子表中添加相关数据，必须在主表中添加完相应的信息，子表中才能添加数据
如果删除主表中的数据，必须先把子表中相关联的数据删除后才能删除成功 */
insert into t_stu values(null,'郭德纲',44,'男',1);
select * from t_stu;

3、高级查询

（1）内连接

内连接其实取得是两个表的交集

-- 查询员工信息以及员工所在的部门名称   
-- 方言版的内连接写法  
select e.*,d.dname from emp e,dept d where  e.deptno=d.deptno;
/*
	内连接，关键字：[inner] join ...on 关联字段等式 
    内连接取两张表中共有的数据  
*/
select e.*,d.* from emp e join  dept d on e.deptno=d.deptno;

（2）外连接

1. 左外连接

-- 左外连接   
/*
	格式：left [outer] join ... on 关联字段等式   
    在关键字左边的叫左表，右边的叫右表 
    左外连接先查询左表（即以左表为主），然后在查询右表  
    右表中满足条件的显示出来，不满足条件的显示null
    
*/
select * from emp left outer join dept on emp.deptno=dept.deptno;

2. 右外连接

-- 右外连接  
/*
	格式： right [outer] join ... on 关联字段等式 
    在关键字左边的叫左表，右边的叫右表 
    右外连接先查询右表（即以右表为主），然后在查询左表  
    左表中满足条件的显示出来，不满足条件的显示null
*/
select * from emp right outer join dept on emp.deptno=dept.deptno;

（3）自然连接

-- 自然连接   
/*
	特点：自然连接无需给出关联字段等式 ，它会自动找到这个等式 
*/
-- 相当于内连接  
select * from emp natural join dept;
-- 相当于左外连接  
select * from emp natural left join dept;
-- 相当于右外连接  
select * from emp natural right join dept;

（4）子查询(嵌套查询)

/*
	子查询就是嵌套查询，即select中包含select
    如果一条sql语句中存在两个或者两个以上的select 
    那么他就是子查询语句 
*/
-- 查询工资最高的员工的详细信息 
select * from emp where sal=(select max(sal) from emp);

（5）自连接(自己连接自己)

/*
	自链接：自己连接自己  
*/
-- 查询员工的姓名以及它的领导人姓名  
select e1.ename 员工姓名,e2.ename 领导人姓名 from emp e1
inner join emp e2 on e1.mgr=e2.empno;

select * from emp;
select * from dept;
/*
	如何进行三个表或者多个表的查询（多表联查）？
    将两张表内连接查询的结果当作一个新表 
    在去内连接另外一张表，这样可以达成三表联查
    多表联查依次类推   
*/

练习题：

create database test_0228;
use test_0228;

create table class(-- 班级表
	cid int primary key auto_increment,
    caption varchar(20)
);

create table student(-- 学生表
	sid int primary key auto_increment,
    sname varchar(20),
    gender varchar(20),
    class_id int
);

create table teacher(-- 老师表
	tid int primary key auto_increment,
    tname varchar(20)
);

create table course(-- 课程表
	cid int primary key auto_increment,
    cname varchar(20),
    tearch_id varchar(20)
);

create table score(-- 成绩表
	sid int primary key auto_increment,
    student_id int,
    corse_id int,
    number int
);
-- 操作表
-- 1.自行创建测试数据
insert into class values(null,'三年二班');
insert into class values(null,'一年三班');
insert into class values(null,'三年一班');

insert into student values(null,'钢蛋','女',1);
insert into student values(null,'铁锤','女',1);
insert into student values(null,'山炮','女',2);

insert into teacher values(null,'李四');
insert into teacher values(null,'叶平');
insert into teacher values(null,'杨艳');
insert into teacher values(null,'李亚萍');
insert into teacher values(null,'李二钦');

insert into course values(null,'生物',1);
insert into course values(null,'体育',1);
insert into course values(null,'物理',2);

insert into score values(null,1,1,60);
insert into score values(null,1,2,59);
insert into score values(null,2,2,100);
insert into score values(null,1,3,59);

-- 2.查询“生物”课程比“物理”课程成绩高的所有学生的学号；
	-- 测试:
-- select  * from score inner join course on course.cid=score.corse_id;
-- select  student_id,number swscore from score inner join course on course.cid=score.corse_id where cname='生物';
-- select  student_id,number wlscore from score inner join course on course.cid=score.corse_id where cname='物理';
select student_id 学生编号,(select number from score swscbiao inner join course on course.cid=swscbiao.corse_id where cname='生物' and swscbiao.student_id=sc.student_id) as 生物成绩,
(select number from score wlscbiao inner join course on course.cid=wlscbiao.corse_id where cname='物理' and wlscbiao.student_id=sc.student_id) as 物理成绩
from score sc
group by sc.student_id;

select 学号 
from 
(select student_id 学号,(select number from score swscbiao inner join course on course.cid=swscbiao.corse_id where cname='生物' and swscbiao.student_id=sc.student_id) as 生物成绩,
(select number from score wlscbiao inner join course on course.cid=wlscbiao.corse_id where cname='物理' and wlscbiao.student_id=sc.student_id) as 物理成绩
from score sc 
group by sc.student_id) as biao
where biao.生物成绩>biao.物理成绩;

-- 3、查询平均成绩大于60分的同学的学号和平均成绩；
select student_id 学号,avg(number) 平均成绩 
from score 
group by student_id
having 平均成绩>60;

-- 4、查询所有同学的学号、姓名、选课数、总成绩；
-- select student_id from score group by student_id;
select student_id 学号, sname 姓名, count(corse_id),sum(number)
from score inner join student on score.student_id=student.sid
group by student_id;

-- 5、查询姓“李”的老师的个数；
select count(tid) 姓‘李’的老师个数
from teacher
where tname like '李%'; 

-- 6、查询没学过“叶平”老师课的同学的学号、姓名；
select * from teacher;
	-- 四表联查，用方言比较简单
select student.sid 学号,student.sname 姓名
from student,teacher,course,score
where student.sid=score.student_id 
	and course.cid=score.corse_id 
    and teacher.tid=course.tearch_id
    and teacher.tname!='叶平'
group by 学号;

-- 7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
	-- Step1:查询课程为''001''和"002"所有情况：
select * from score where corse_id = 1;
select * from score where corse_id = 2;
	-- Step2：根据步骤一，利用内嵌查询，查询同时修了两门课的学生id：
select t1.student_id from (
	(select s1.student_id from score s1 where s1.corse_id = 1) t1,
	(select s2.student_id from score s2 where s2.corse_id = 2) t2
)where t1.student_id = t2.student_id;
	-- Step3：学生表和步骤二的表联立，得到最终的结果：
select student_id 学号,sname 姓名 from(
	select t1.student_id from (
		(select s1.student_id from score s1 where s1.corse_id = 1) t1,
		(select s2.student_id from score s2 where s2.corse_id = 2) t2
	)where t1.student_id = t2.student_id
) stuid,student
where stuid.student_id=student.sid;

-- 8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select student.sid,sname
from student inner join score on student.sid=score.student_id
where corse_id in(
	-- 获取叶平老师教过的课程ID
	select course.cid
	from teacher inner join course on teacher.tid=course.tearch_id
    where teacher.tname='叶平'
)
group by student_id
having count(score.corse_id)>=(
	-- 获取叶平老师一共教过几门课程
	select count(course.cid)
    from course inner join teacher on teacher.tid=course.tearch_id
    where teacher.tname='叶平'
);

-- 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名； 
select student.sid,sname
from student 
inner join (
	select cor1.student_id,n1,n2
	from (select student_id,number n1 from score where corse_id=1) cor1 
	left join (select student_id,number n2 from score where corse_id=2) cor2 
	on cor1.student_id=cor2.student_id
) cor
on student.sid=cor.student_id
where n1>ifnull(n2,0);

-- 10、查询有课程成绩小于60分的同学的学号、姓名；
select student.sid,sname
from student inner join score on student.sid=score.student_id
where number<60
group by student.sid;

-- 11、查询没有学全所有课的同学的学号、姓名；
select student.sid,sname
from student inner join score on student.sid=score.student_id
group by student.sid
having count(corse_id)<(
	select count(cid)
    from course
);

-- 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
select student.sid,sname
from student inner join score on student.sid=score.student_id
where score.corse_id in (
	select corse_id
    from score
    where student_id=1
) and student.sid!=1
group by student.sid;

-- 13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；
select student.sid,sname
from student inner join score on student.sid=score.student_id and student.sid!=1
where score.corse_id in (
	select score.corse_id
    from score
    where student_id=1
) 
group by student.sid
having count(1)>=(select count(corse_id) from score where student_id=1);

-- 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
select C.sid, C.sname from (
	select student_id, count(corse_id) from score
	where corse_id in (
		select corse_id from score where student_id = 2
	) and student_id != 2
	group by student_id
	having count(corse_id) = (select count(corse_id) from score where student_id=2)
) as A
inner JOIN (
	select student_id, count(*) from score
	group by student_id
	having count(*) = (select count(corse_id) from score where student_id=2)
) as B
on A.student_id=B.student_id
inner join student as C
on A.student_id=C.sid;

-- 15、删除学习“叶平”老师课的SC表记录；
delete from score
where corse_id in (
	select course.cid
    from course
    left join teacher
    on course.teacher_id=teacher.tid
    where teacher.tname='叶平'
);
-- 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩； 
insert into score(student_id,corse_id,number)
select sid, 2, (select avg(number) from score where corse_id=2) from student
where sid not in (
	select student_id from score where corse_id=2
);
-- 17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
select sc.student_id,
(select number from score left join course on score.corse_id=course.cid where course.cname="语文" and student_id=sc.student_id) as "语文",
(select number from score left join course on score.corse_id=course.cid where course.cname="数学" and score.student_id=sc.student_id) as "数学",
(select number from score left join course on score.corse_id=course.cid where course.cname="英语" and score.student_id=sc.student_id) as "英语",
count(sc.corse_id) as countt, 
avg(sc.number) as avgg
from score as sc
group by sc.student_id
order by avgg asc;
-- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select corse_id, cname, max(number) as max_number, min(number) as min_number
from score, course
where score.corse_id=course.cid
group by corse_id;
-- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select sc.corse_id, avg(sc.number),
(select count(corse_id) from score where corse_id=sc.corse_id and number>=60)/count(sc.corse_id) as bb
from score as sc
group by sc.corse_id
order by avg(sc.number), bb desc;

-- 20、课程平均分从高到低显示（现实任课老师）；
SELECT if(isnull(avg(number)), 0, avg(number)) as avgnum, tname FROM `score`
left join course on score.corse_id=course.cid
right join teacher on course.tearch_id=teacher.tid
group by corse_id
ORDER BY avgnum desc;
-- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
 select score.sid,score.corse_id,score.number,T.first_num,T.second_num 
 from score left join (
    select
        sid,
        (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 0,1) as first_num,
        (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 3,1) as second_num
    from
        score as s1
) as T
on score.sid =T.sid
where score.number <= T.first_num and score.number >= T.second_num;
-- 22、查询每门课程被选修的学生数；
select corse_id, count(1) from score
GROUP BY corse_id;
-- 23、查询出只选修了一门课程的全部学生的学号和姓名；
select student.sid, student.sname from student
right join score on student.sid = score.student_id
GROUP BY score.student_id
having count(score.corse_id)=1;
-- 24、查询男生、女生的人数；
select gender, count(sid) from student
group by gender;
-- 25、查询姓“张”的学生名单；
select sname from student where sname like "张%";
-- 26、查询同名同姓学生名单，并统计同名人数；
select left(sname, 1) as lname, count(sid) from student
group by lname;
-- 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
	-- if的作用：如果number为空值的将其设置为0同时也参与平均值计算
	-- 如果直接写 avg(number) 则number为空值的不参与平均值计算
select corse_id, avg(if(isnull(number), 0, number)) as avgnum from score 
group by corse_id
ORDER BY avgnum, corse_id desc;
-- 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student.sid, student.sname, avg(if(isnull(score.number), 0, score.number)) as avgnum from score
left join student on score.student_id=student.sid
GROUP BY student_id
HAVING avgnum>85;
-- 29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select student.sname, number from score left join student
on score.student_id=student.sid
left join course on score.corse_id=course.cid
where course.cname="数学" and score.number<60;
-- 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 
select student.sid, student.sname from score 
left join student on score.student_id=student.sid
where corse_id=3 and number>80;
-- 31、求选了课程的学生人数
select count(DISTINCT student_id) from score;

-- 32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select student.sname, number from score
left join student on score.student_id=student.sid
where corse_id in (
	select cid from course right join teacher on course.tearch_id=teacher.tid where tname="杨艳"
)
order by NUMber desc
limit 1;
-- 33、查询各个课程及相应的选修人数；
select course.cname, count(student_id) from score
left join course on score.corse_id=course.cid
group by course.cid;
-- 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select s1.student_id, s2.corse_id, s1.corse_id, s1.number 
from score as s1, score as s2
where s1.number = s2.number and s1.corse_id != s2.corse_id and s1.student_id=s2.student_id
group by s1.student_id;
-- 35、查询每门课程成绩最好的前两名；
select score.sid,score.corse_id,score.number,T.first_num,T.second_num 
from score left join (
    select
        sid,
        (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 0,1) as first_num,
        (select number from score as s2 where s2.corse_id = s1.corse_id order by number desc limit 1,1) as second_num
    from
        score as s1
) as T
on score.sid =T.sid
where score.number <= T.first_num and score.number >= T.second_num;
-- 36、检索至少选修两门课程的学生学号；
select student_id from score group by student_id having count(corse_id)>=2;
-- 37、查询全部学生都选修的课程的课程号和课程名；
select corse_id, course.cname from score 
left join course on score.corse_id=course.cid
group by corse_id
having count(student_id)=(select count(1) from student);
-- 38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
select sname from student
where sid not in (
	select distinct student_id from score
	where corse_id in (
		select cid from course
		left join teacher on teacher.tid = course.tearch_id
		where teacher.tname="叶平"
	)
);
-- 39、查询两门以上不及格课程的同学的学号及其平均成绩；
select student_id, avg(number) from score
where number<60
group by student_id
having count(number)>1;
-- 40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score
where corse_id=4 and number<60
order by number desc;
-- 41、删除“002”同学的“001”课程的成绩；
delete from score where student_id=2 and corse_id=1;

DCL(数据库控制语言)

创建用户

语法：

CREATE USER 用户名@地址 IDENTIFIED BY '密码';
CREATE USER user1@localhost IDENTIFIED BY '123';
CREATE USER user2@'%' IDENTIFIED BY '123';

给用户授权

语法：

GRANT 权限1, … , 权限n ON 数据库.* TO 用户名

GRANT CREATE,ALTER,DROP,INSERT,UPDATE,DELETE,SELECT ON stu506.* TO user1@localhost;
GRANT ALL ON stu506.* TO user2@localhost;

撤销授权

语法：

REVOKE权限1, … , 权限n ON 数据库.* FORM 用户名

REVOKE CREATE,ALTER,DROP ON stu506.* FROM user1@localhost;

查看用户权限

语法：

SHOW GRANTS FOR 用户名

SHOW GRANTS FOR user1@localhost;

删除用户

语法：

DROP USER 用户名
DROP USER user1@localhost;

修改用户密码

语法：

USE stu506;
UPDATE USER SET PASSWORD=PASSWORD(‘密码’) WHERE User=’用户名’ and Host=’IP’;
FLUSH PRIVILEGES;
UPDATE USER SET PASSWORD=PASSWORD('1234') WHERE User='user1' and Host=’localhost’;
FLUSH PRIVILEGES;

二、常用数据库类型

数值类：整数和浮点

​ int 整型
​ double(M,D) 浮点型 M：表示有效数字最大位数，D：表示小数位数 一定要注意：M>=D

​ decimal(M,D) 泛型型，一般使用在钱方面

字符串类型：

​ char(n) 固定长度字符串，n表示最长的字符个数
​ varchar(n) 可变长字符串类型
​ text 长文本类型
​ blob 字节类型，常用于存储图片，音频等等

日期类型：

​ date 日期类型 格式：yyyy-MM-dd
​ time 时间类型 格式：HH:mm:ss
​ datetime 日期时间类型 格式：yyyy-MM-dd HH:mm:ss
​ timestamp 时间戳类型 会自动更新时间

-- 数值类型  
-- DDL   创建表  
create table t_test1(
-- 列名 数据库类型 主键  自增长
	id int primary key auto_increment, -- 员工ID
    salary decimal(7,2), -- 员工工资 
    t_dab double(3,1)     --  创建表最后不需要逗号 
);

-- 新增 
/*小数类型，如果插入的值小于规定小数位数会自动使用
0来补充，如果插入的值大于规定的小数位数会自动进行
四舍五入为规定的小数位数*/
insert into t_test1 values(null,15000,45.789);
-- 报错原因：因为salary字段值超出有效位数的范围 
insert into t_test1 values(null,150000,5.3);
-- 查询 
select * from t_test1;

-- 字符串类型   
create table t_test2(
	id char(6) primary key,  -- id
    t_name varchar(50),   -- 姓名
    gender blob, -- 性别
    inter text  -- 自我介绍  
);
-- 添加 
/*blob类型不包含字符集编码格式，所以当我们插入文本 
时显示的时乱码*/ 
insert into t_test2 values('123456','郭德纲','男','我是来自北京德云社');
/*在字符串类型的字段中插入数值类型，先将数值转换为字符串 
然后在进行插入 */
insert into t_test2 values(12345,'尼古拉是赵四','男','我是来自北京德云社');
-- 报错：超出字段的最大长度  
insert into t_test2 values(12345678,'尼古拉是赵四','男','我是来自北京德云社');
-- 查询 
select * from t_test2;

-- 日期类型  
create table t_test3(
	id int primary key auto_increment,
    t_date date,
    t_time time,
    t_dt datetime,
    t_ts timestamp
);
-- 添加 
insert into t_test3 values(null,now(),now(),now(),now());
insert into t_test3 values(null,now(),now(),now(),now());
-- 修改
/*一旦对表中某条记录进行修改，如果记录中有时间戳类型的字段， 
那么时间会进行自动更新*/  
update t_test3 set t_time=now() where id=2;
-- 查询 
select * from t_test3;


-- DDL
-- 查看当前使用的数据库中所有的表  
show tables;
-- 查看创建某张表的语句  
show create table t_test1;
-- 查看表结构  
desc t_test2;
-- 删除表  
drop table t_test3;

-- DDL
-- 创建学生表  
create table student(
	id int primary key auto_increment,
    sname varchar(50),
    sex varchar(10)
);
-- 查看表结构  
desc student;
-- 修改表结构（表结构：字段和字段类型）
-- alter table 表名 ...
-- 向表中添加字段 
alter table student add column age int;
-- 修改字段名称  
alter table student change sex gender varchar(50);
-- 修改字段类型  
alter table student modify gender char(2);
-- 删除字段  
alter table student drop age;
-- 修改表名  
alter table student rename to stu;

数据库的备份与还原

1、 找到MySQL安装路径中的MySQL server文件夹下面

有个bin包，打开找到MySQLdump，然后把这个路径复制下来

我们需要在环境变量配置，在环境变量中的path变量中

在后面添加（不要删除path里面的任何东西）

1）

2）

3）

4）

5）

1. 数据库的备份

1）打开cmd dos命令行窗口，在里面输入：

Mysqldump -u用户名 -p密码 数据库名[表名]>要导出路径

【例如：E:\文件名.sql】

2. 数据库的还原

**1）**创建新的数据库用来还原数据

Create database 数据库名;

这一步可以在视图工具中操作也可以在cmd命令行中操作。如果你在cmd命令行中操作，那么你必须先进入MySQL的运用程序。

进入MySQL运用程序的方式：

Mysql -u用户名 -p密码;

**2）**使用命令行来使用创建好的数据库

格式：

use 数据库名;

**3）**将导出的数据库信息还原进去

格式：

source 导入的路径

[列如：E：\abc.sql]