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

on score.course_id = course.cid

where course.cname = '生物') as A

left join (

select student_id, number as wuli from score

left join course

on score.course_id = course.cid

where course.cname = '物理') as B

on A.student_id = B.student_id

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, count(sc.course_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

on s.course_id = t.tid

where t.tname = '赵老师';

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

on c.teacher_id = t.tid

where t.tname = '钱老师'

)

);

13、查询学过“1”并且也学过编号“2”课程的同学的学号、姓名

select st.sid, st.name from student as st

inner join score as sc

on st.sid = sc.student_id

where sc.course_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

on c.teacher_id = t.tid

where t.tname = '孙老师'

)

);

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、查询没有学全所有课的同学的学号、姓名

select st.sid, st.name from student as st

inner join score as sc

on st.sid = sc.student_id

group by sc.student_id

having count(sc.course_id) < (

select count(*) from course

);

18、查询和“2”号的同学学习的课程完全相同的其他同学学号和姓名

select * from student

where sid in (

select score.student_id from score

join (

select * from score

where student_id = 2) as sc

on sc.course_id = score.course_id

and sc.student_id != score.student_id

group by score.student_id

having count(*) = (

select count(*) from score

where student_id = 2

)

);

19、删除学习孙老师课的SC表记录

delete from score

where course_id in (

select c.cid from course as c

inner join teacher as t

on c.teacher_id = t.tid

where t.tname = '孙老师'

);