mysql中注销sql_mysql中的sql基本操作
数据库环境：Windows 10
数据库版本：Mysql 5.7
⼀、请写出下列查询语句并操作
1、链接数据库
mysql -uroot -p
2、显⽰数据库版本
mysql --version;
3、查看当前所有数据库
show databases;
4、创建demo数据库，并设置字符集为utf8
CREATE DATABASE IF NOT EXISTS demo DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;
5、选择使⽤demo数据库
use demo;
6、显⽰时间
select now();
⼆、在demo数据库下创建以下数据表
1、创建学⽣表student，并输⼊对应数据
create table if not exists student(
sid int unsigned primary key auto_increment,
name varchar(40) not null,
gender varchar(10) not null,
class_id int unsigned not null
);
insert into student values('1','张三','⼥','1');
insert into student values('2','李四','⼥','4');
insert into student values('3','王五','男','2');
insert into student values('4','赵六','⼥','3');
insert into student values('5','⽥七','⼥','5');
insert into student values('6','江北','男','10');
insert into student values('7','齐⼋','⼥','8');
insert into student values('8','魏九','⼥','9');
insert into student values('9','⽼⽯','男','7');
insert into student values('10','钱电','⼥','6');
insert into student values('11','张三','男','11');
2、创建教师表teacher，并输⼊对应数据
create table if not exists teacher(
tid int unsigned primary key auto_increment, tname varchar(40) not null
);
insert into teacher values('1','赵⽼师');
insert into teacher values('2','钱⽼师');
insert into teacher values('3','孙⽼师');
3、创建课程表course，并输⼊对应数据
create table if not exists course(
cid int unsigned primary key auto_increment, cname varchar(40) not null,
teacher_id int unsigned not null
);
insert into course values('1','⽣物','1');
insert into course values('2','体育','2');
insert into course values('3','物理','3');
4、创建成绩表score，并输⼊对应数据
create table if not exists score(
student_id int unsigned not null,
course_id int unsigned not null,
number int unsigned not null
)
;
insert into score values('1','1','80');
insert into score values('1','2','90');
insert into score values('1','3','99');
insert into score values('2','1','70');
insert into score values('2','2','60');
insert into score values('2','3','80');
insert into score values('3','1','80');
insert into score values('3','2','80');
insert into score values('3','3','75');
insert into score values('4','1','50');
insert into score values('4','2','30');
insert into score values('4','3','20');
insert into score values('5','1','76');
insert into score values('5','2','87');
insert into score values('6','1','31');
insert into score values('6','3','34');
insert into score values('7','2','89');
insert into score values('7','3','98');
5、查看当前数据库中所有的表
show tables;
6、查询所有授课⽼师的姓名
select tname from teacher;
7、查询班级每种性别有多少⼈
select gender, count(gender) as genderNum from student
group by gender;
8、查询“⽣物”课程⽐“物理”课程成绩⾼的所有学⽣的学号
select A.student_id, shengwu, wuli from (
select student_id, number as shengwu from score
left join course
urse_id = course.cid
where courseame = '⽣物') as A
left join (
select student_id, number as wuli from score
left join course
urse_id = course.cid
where courseame = '物理') as B
on A.student_id = B.student_idmysql中delete语句
where shengwu > if(isnull(wuli),0,wuli);
9、查询平均成绩⼤于60分的同学的学号和平均成绩
select student_id, round(avg(number),2) as avgScore from score group by student_id
having avgScore > 60
order by avgScore desc;
10、查询所有同学的学号、姓名、选课数、总成绩； 语句进化过程
步骤⼀：到⽬标所在的表
学号、姓名，存在于student
选课数、总成绩，存在于score
步骤⼆：两表创建内联
select * from student as st
inner join score as sc
on st.sid = sc.student_id;
步骤三：按照需求筛选显⽰数据
select st.sid, st.name, urse_id), sum(sc.number) from student as st inner join score as sc
on st.sid = sc.student_id
group by st.sid;
11、查询姓“赵”的⽼师的个数
查询teacher表中，姓“赵”⽼师的个数
select count(*) from teacher
where tname like '赵%';
查询score表中，选择姓“赵”⽼师的个数
select count(*) from score as s
inner join teacher as t
urse_id = t.tid
ame = '赵⽼师';
12、查询没学过钱⽼师课的同学的学号、姓名
select sid,name from student
where sid not in (
select student_id from score
where course_id in (
select c.cid from course as c
inner join teacher as t
acher_id = t.tid
ame = '钱⽼师'
)
);
13、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名
select st.sid, st.name from student as st
inner join score as sc
on st.sid = sc.student_id
urse_id in (1,2)
group by st.sid, st.name;
14、查询学过孙⽼师所教的所有课的同学的学号、姓名
select sid, name from student
where sid in (
select student_id from score
where course_id in (
select c.cid from course as c
inner join teacher as t
acher_id = t.tid
ame = '孙⽼师'
)
);
15、查询课程编号“2”的成绩⽐课程编号“1”课程低的所有同学的学号、姓名select sid, name from student
where sid in (
select A.student_id from (
select * from score
where course_id = '2') as A
inner join (
select * from score
where course_id = '1') as B
on A.student_id = B.student_id
where A.number < B.number
);
16、查询有课程成绩⼩于60分的同学的学号、姓名
select st.sid, st.name from student as st
inner join score as sc
on st.sid = sc.student_id
where sc.number < 60
group by st.sid, st.name;
17、查询没有学全所有课的同学的学号、姓名

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。