# 1.显示数据库
show databases;
# 2.创建数据库
# 默认
create database db2;
show databases;
describe db2;
# utf8
create database db1 default charset utf8 collate utf8_general_ci;
# gbk
create database d2 default charset gbk collate gbk_chinese_ci;
# 3.使用数据库
use db1;
# 4.用户管理
# 创建用户
create user 'rain'@'192.168.1.%' identified by '111111';
create user 'breeze'@'%' identified by '111111';
# 删除用户
drop user 'rain'@'192.168.1.%';
# 修改用户
rename user 'rain'@'192.168.1.%' to 'rain'@'192.168.%.%';
# 修改密码
set password for 'breeze'@'%' = '111111';
# 5.权限管理
# 查看权限
show grants for 'breeze'@'%';
# 授权
grant select, insert, update on db1.t1 to 'breeze'@'%';
# 所有权限，除了授权
grant all privileges on db1.* to 'breeze'@'%';
# 取消授权
revoke all privileges on db1.t1 from 'breeze'@'%';
# 6.数据表操作
# 创建表
# engine = innodb:支持事务，原子性操作;myisam 5.5以前默认，现在是innodb
# not null - 不可空; null - 可空
# 创建索引 index ix_name (name)
# auto_increment 表示：自增
## 基于会话级别：
## show session variables like 'auto_inc%'; 查看全局变量
## set session auto_increment_increment=2; 设置会话步长
## 基于全局级别：
## show global variables like 'auto_inc%'; 查看全局变量
## set global auto_increment_increment=2; 设置会话步长
# 改变表的自增开始
# alter table t10 AUTO_INCREMENT=20
# primary key：表示 约束(不能重复且不能为空); 加速查找,可单独写primary key(nid,pid)
# 数据类型
create table db1.t1
(
id int,
name char(10)
) engine = innodb
default charset = utf8;
create table db1.t3
(
nid int not null auto_increment primary key,
name char(10) not null,
age int not null default 18
) engine = innodb
AUTO_INCREMENT = 4
default charset = utf8;
# 外键示例
create table db1.ecolor
(
nid int not null primary key,
name char(16) not null
);
# constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);
create table db1.fruit
(
nid int not null primary key,
smt char(32) null,
color_id int not null,
constraint fk_fruit_color foreign key (color_id) references db1.color (nid)
);
# 清空表
delete
from db1.dcolor;
truncate table db1.dcolor;
# 删除表
drop table db1.color;
# 修改表
# 添加列
alter table db1.t1
add qq int;
# 删除列
alter table db1.t1
drop qq;
# 修改列类型
alter table db1.t1
modify column qq char(9);
# 修改列名称和类型
alter table db1.t1
change qq wechat char(10);
# 添加主键
alter table db1.t1
add primary key (id);
# 删除主键
alter table db1.t1
drop primary key;
# 添加外键
alter table db1.fruit
add constraint fk_fruit_color foreign key (color_id) references color (nid);
# 删除外键
alter table db1.fruit
drop foreign key fk_fruit_color;
# 修改默认值
alter table db1.t1
alter wechat set default 'iloveyou';
# 删除默认值
alter table db1.t1
alter wechat drop default;
# 查看表创建语句
show create table t2;
# 查看表结构
desc t2;
# 创建索引 index unique
create index ix_name on dcolor (name);
# 注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length
# create index ix_extra on in1(extra(32));
# 查看索引
show index from dcolor;
# 删除索引
drop index ix_name on dcolor;
# 7. 表内容操作
# 增
insert into db1.color (nid, name)
VALUES (3, 'black');
insert into db1.dcolor (nid, name)
VALUES (1, 'pink'),
(2, 'white');
insert into db1.dcolor (nid, name)
select nid, name
from db1.color;
# 删
delete
from dcolor;
delete
from db1.color
where nid = 5;
# 改
update db1.color
set name='yellow'
where nid = 1;
update dcolor
set name='red';
# 查
select *
from color;
select *
from color
where nid > 2;
select nid, name as yanse
from color
where nid > 2;
select username, usertype, 123
from db1.userinfo;
# 其他
# 条件
select *
from color
where nid > 1
and name != 'alex';
select *
from color
where nid between 5 and 16;
select *
from color
where nid in (11, 22, 33);
select *
from color
where nid not in (11, 22, 33);
select *
from color
where nid in (select nid from dcolor);
# 通配符
select *
from color
where name like 'gr%'; # gr开头的所有（替代多个字符串）
select *
from color
where name like 'gree_';
# gree开头的所有（替代一个字符）
# 限制
select *
from color
limit 2; # 前2行
select *
from color
limit 1,3; # 从第偏移1行开始的3行
select *
from color
limit 3 offset 1;
# 从偏移1行开始的3行
# 排序
select *
from color
order by nid asc; # 根据 “列” 从小到大排列
select *
from color
order by nid desc; # 根据 “列” 从大到小排列
select *
from color
order by nid desc, name asc;
# 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
# 分组
select name
from color
group by name;
select name, nid
from color
group by name, nid;
select name, nid
from color
where nid > 1
group by name, nid
order by nid desc;
select name, nid, count(*), sum(nid), max(nid), min(nid)
from color
group by name, nid;
select name
from color
group by name
having max(nid) > 1;
# 特别的：group by 必须在where之后，order by之前
# 连表
# 无对应关系则不显示
select color.nid, color.name, dcolor.name
from color,
dcolor
Where color.nid = dcolor.nid;
# 无对应关系则不显示
select color.nid, color.name, dcolor.name
from color
inner join dcolor
on color.nid = dcolor.nid;
#A表所有显示，如果B中无对应关系，则值为null
select color.nid, color.name, dcolor.name
from color
left join dcolor
on color.nid = dcolor.nid;
# B表所有显示，如果A中无对应关系，则值为null
select color.nid, color.name, dcolor.name
from color
right join dcolor
on color.nid = dcolor.nid;
# 组合
# 组合，自动处理重合,去重
select name
from color
union
select name
from dcolor;
#组合，不处理重合，不去重
select name
from color
union all
select name
from dcolor;
create table db1.userinfo
(
id int not null auto_increment primary key,
username char(10) not null,
usertype tinyint not null
) engine = innodb
default charset = utf8;
create table db1.admin
(
id int not null auto_increment primary key,
userid int not null,
pwd char(10) not null,
unique uq_ad_info (userid),
constraint fk_admin_info foreign key (userid) references db1.userinfo (id)
) engine = innodb,
default charset = utf8;
insert into userinfo (username, usertype)
values ('user1', 1),
('user2', 3);
create table db1.host
(
id int not null auto_increment primary key,
hostname char(10) not null
) engine = innodb,
default charset = utf8;
create table user_and_host
(
id int not null auto_increment primary key,
uid int not null,
cid int not null,
unique uq_user_host (uid, cid),
constraint fk_user foreign key (uid) references db1.userinfo (id),
constraint fk_host foreign key (cid) references db1.host (id)
) engine = innodb,
default charset = utf8;
select *
from userinfo
INNER join color on userinfo.id = color.nid;
CREATE TABLE `course`
(
`cid` int(11) NOT NULL AUTO_INCREMENT,
`cname` varchar(32) NOT NULL,
`teacher_id` int(11) NOT NULL,
PRIMARY KEY (`cid`),
CONSTRAINT `fk_course_teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`tid`)
) ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARSET = utf8;
##### 练习题
# 2 查询“生物”课程比“物理”课程成绩高的所有学生的学号；if(ex1,ex2,ex3) ex1真返回ex2
select A.student_id, sw, ty
from (select student_id, num as sw
from score
left join course on score.course_id = course.cid
where course.cname = '生物') as A
left join
(select student_id, num as ty
from score
left join course on score.course_id = course.cid
where course.cname = '体育') as B
on A.student_id = B.student_id
where sw > if(isnull(ty), 0, ty);
# 3 查询平均成绩大于60分的同学的学号和平均成绩；
select student_id, avg(num) as avg_score
from score
group by student_id
having avg(num) > 60;
# 4、查询所有同学的学号、姓名、选课数、总成绩；
select score.student_id, student.sname, sum(score.num) as sum_score, count(score.sid) as count_course
from score
left join
student
ON score.student_id = student.sid
group by score.student_id;
# 5、查询姓“李”的老师的个数；
select count(tid)
from teacher
where tname like '李%';
# 6、查询没学过“李平”老师课的同学的学号、姓名；
select sid, sname
from student
where sid not in
(select distinct student_id
from score
where course_id in
(select cid
from course
left join teacher t on t.tid = course.teacher_id
where tname = '李平老师'));
# 7、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名；
# 答案1
select a.sid, a.sname
from (select student.sid, student.sname
from student
left join score s on student.sid = s.student_id
where s.course_id in (1, 2)) as a
group by a.sid
having count(a.sid) = 2;
# 答案二
select student_id, sname
from (select student_id, course_id from score where course_id = 1 or course_id = 2) as B
left join student on B.student_id = student.sid
group by student_id
HAVING count(student_id) > 1;
# 8、查询学过“李平”老师所教的所有课的同学的学号、姓名；
select a.sid, a.sname
from (select student.sid, student.sname
from student
left join score s on student.sid = s.student_id
where s.course_id in (select cid
from course
left join teacher t on t.tid = course.teacher_id
where t.tname = '李平老师')) as a
group by a.sid
having count(a.sid) = 2;
# 9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
select student.sid, student.sname
from student
inner join
(select b.student_id
from (select student_id, course_id, num from score where course_id = 2) a
right join
(select student_id, course_id, num from score where course_id = 1) b on a.student_id = b.student_id
where if(isnull(a.num), 0, a.num) < b.num) c on student.sid = c.student_id;
#10、查询有课程成绩小于60分的同学的学号、姓名；
select distinct student.sid, student.sname
from student
left join score s on student.sid = s.student_id
where s.num < 60;
select student.sid, student.sname
from student
where sid in (select distinct student_id from score where num < 60);
#11 查询没有学全所有课的同学的学号、姓名；
select student.sid, student.sname
from (select score.student_id from score group by score.student_id having count(course_id) < 4) a
left join
student on a.student_id = student.sid;
select student.sid, student.sname
from student
where sid in
(select student_id from score group by student_id having count(student_id) < (select count(1) from course));
# 12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
select sid, sname
from student
where sid in (
select distinct student_id
from score
where course_id in (select course_id from score where student_id = 1)
and student_id != 1);
# 13、查询至少学过学号为“001”同学所有课的其他同学学号和姓名；
select sid, sname
from student
where sid in (
select distinct student_id
from score
where course_id in (select course_id from score where student_id = 1)
group by student_id
having count(course_id) >= (select count(1) course_id from score where student_id = 1)
and student_id != 1);
# 14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
select sid, sname
from student
where sid in (
select distinct student_id
from score
where course_id in (select course_id from score where student_id = 2)
group by student_id
having count(course_id) = (select count(1) course_id from score where student_id = 2)
and student_id != 2);
# 15、删除学习“叶平”老师课的score表记录；
select *
from score
where course_id in (select cid from course where teacher_id in (select teacher_id from teacher where tname = '李平老师'));
# delete from score where course_id in (select cid from course where teacher_id in (select teacher_id from teacher where tname='叶平'));
# 16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
insert into score (student_id, course_id, num)
select sid, 2, (select avg(num) from score where course_id = 2)
from student
where sid not in (select student_id from score where course_id = 2);
# 17、按平均成绩从低到高 显示所有学生的“生物”、“物理”、“体育”三门的课程成绩，按如下形式显示： 学生ID,生物,物理,体育,有效课程数,有效平均分；
select score.student_id 学生ID,
a.num 生物,
b.num 物理,
c.num 体育,
d.num 美术,
count(score.course_id) 有效课程数,
avg(score.num) 有效平均分
from score
left join
(select student_id, num from score where course_id = (select cid from course where cname = '生物')) as a
on score.student_id = a.student_id
left join
(select student_id, num from score where course_id = (select cid from course where cname = '物理')) as b
on score.student_id = b.student_id
left join
(select student_id, num from score where course_id = (select cid from course where cname = '体育')) as c
on score.student_id = c.student_id
left join
(select student_id, num from score where course_id = (select cid from course where cname = '美术')) as d
on score.student_id = d.student_id
group by score.student_id, a.num, b.num, c.num, d.num;
#答案二
select sc.student_id,
(select num
from score
left join course on score.course_id = course.cid
where course.cname = "生物"
and score.student_id = sc.student_id) as sy,
(select num
from score
left join course on score.course_id = course.cid
where course.cname = "物理"
and score.student_id = sc.student_id) as wl,
(select num
from score
left join course on score.course_id = course.cid
where course.cname = "体育"
and score.student_id = sc.student_id) as ty,
count(sc.course_id),
avg(sc.num)
from score as sc
group by student_id desc;
# 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select course_id, max(num), min(num)
from score
group by course_id;
# 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select course_id, avg(num) as avg_num, sum(if(num > 60, 1, 0)) / count(1) * 100 as percent
from score
group by course_id
order by avg_num, percent desc;
select course_id, avg(num) as avgnum, sum(case when score.num > 60 then 1 else 0 END) / count(1) * 100 as percent
from score
group by course_id
order by avgnum asc, percent desc;
# 20、课程平均分从高到低显示（显示任课老师）；
select course_id, t.tname, avg(num) as avg_num
from score
left join course c on c.cid = score.course_id
left join teacher t on t.tid = c.teacher_id
group by course_id
order by avg_num desc;
select course_id, avg(if(isnull(score.num), 0, score.num)) as avg_num, teacher.tname
from course
left join score on course.cid = score.course_id
left join teacher on course.teacher_id = teacher.tid
group by course_id, tid
order by avg_num desc;
# group by avg_num;13910024543
# 21 查询各科成绩前三名的记录:(不考虑成绩并列情况)
(select sid, student_id, course_id, num
from score
where course_id = 1
group by num desc, sid, student_id, course_id
limit 3)
union all
(select sid, student_id, course_id, num
from score
where course_id = 2
group by num desc, sid, student_id, course_id
limit 3)
union all
(select sid, student_id, course_id, num from score where course_id = 2 group by num desc, sid, student_id, course_id);
select course_id, num
from score s
where (select count(1) from score where course_id = s.course_id and num > s.num) < 3
order by course_id, num desc;
select course_id, num
from (select student_id,
course_id,
num,
(select num
from score s2
where s2.course_id = s1.course_id
group by s2.num
order by s2.num desc
limit 3, 1) as 第三名
from score s1) s3
where s3.num > s3.第三名
order by s3.course_id, s3.num desc;
# 22、查询每门课程被选修的学生数；
select course_id, count(student_id)
from score
group by course_id
order by course_id;
#23、查询出只选修了一门课程的全部学生的学号和姓名；
select sid, sname
from student
where sid in (select student_id from score group by student_id having count(1) = 1);
# 24 查询男生、女生的人数；
select gender, count(1)
from student
group by gender;
# 25、查询姓“张”的学生名单；
select sname
from student
where sname like '张%';
# 26、查询同名同姓学生名单，并统计同名人数；
select sname, count(1) as count
from student
group by sname;
# 27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
# select course_id, avg(if(isnull(num), 0, num)) as avg
select course_id, avg(num) as avg
from score
group by course_id
order by avg asc, course_id desc;
# 28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student_id, sname, avg(if(isnull(num), 0, num))
from score
left join student on score.student_id = student.sid
group by student_id;
# 29、查询课程名称为“物理”，且分数低于60的学生姓名和分数；
select course_id, student_id, (select sname from student where student.sid = student_id) as name, num
from score
where num < 60
and course_id = (select cid from course where cname = '物理');
# 30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select *
from score
where score.student_id = 3
and score.num > 80;
# 31 求选了课程的学生人数
select count(1)
from (select student_id from score group by student_id) as a;
select count(distinct student_id)
from score;
#32查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select sname, num
from score
left join student on score.student_id = student.sid
where score.course_id in (select course.cid
from course
left join teacher on course.teacher_id = teacher.tid
where tname = '张磊老师')
order by num
desc
limit 1;
# 33、查询各个课程及相应的选修人数；
select course.cname, count(1)
from score
left join course on score.course_id = course.cid
group by course_id;
# 34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.course_id, s2.course_id, s1.num, s2.num
from score as s1,
score as s2
where s1.num = s2.num
and s1.course_id != s2.course_id;
#35、查询每门课程成绩最好的前两名；
# 同21
#36检索至少选修两门课程的学生学号；
select student_id
from score
group by student_id
having count(student_id) > 1;
# 37、查询全部学生都选修的课程的课程号和课程名；
select course_id, count(1)
from score
group by course_id
having count(1) = (select count(1) from student);
# 38、查询没学过“张磊老师”老师讲授的任一门课程的学生姓名；
select student_id, s.sname
from score
left join student s on score.student_id = s.sid
where score.student_id not in (select score.student_id from score where course_id in (1))
group by student_id;
select student_id
from score
where course_id = 1;
# 39、查询两门以上不及格课程的同学的学号及其平均成绩；
select score.student_id,avg(score.num) as avg_num from score where score.student_id in
(select ab.student_id from score as ab where ab.num < 60 group by ab.student_id having count(1) > 0) ;
select student_id from score where num < 60 group by student_id having count(1) > 0;
select student_id,count(1) from score where num < 60 group by student_id having count(1) > 1;
# 40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where num< 60 and course_id = 4 order by num desc;
#41、删除“002”同学的“001”课程的成绩；
delete from score where course_id = 1 and student_id = 2
浙公网安备 33010602011771号