MySQL基礎(chǔ)

1、如果暫停或開啟MySQL服務(wù)？

net stop mysql80(版本號(hào))
net start mysql80(版本號(hào))

2、如何使用cmd命令登錄MySQL窗口

mysql -u 用戶名 -p 密碼

3、登錄時(shí)用到-u \ -p等參數(shù)，如何查看所有參數(shù)意思？

mysql --help

MySQL練習(xí)題

習(xí)題

學(xué)生表：students

創(chuàng)建表

create table if not exists Students (student_id int primary key,student_name varchar(20));

插入數(shù)據(jù)

Truncate table students;
insert into students (student_id, student_name) values ('1', 'Alice');
insert into students (student_id, student_name) values ('2', 'Bob');
insert into students (student_id, student_name) values ('13', 'John');
insert into students (student_id, student_name) values ('6', 'Alex');

科目表：subjects

創(chuàng)建表

create table if not exists subjects (subject_name varchar(20)  primary key);

插入數(shù)據(jù)

Truncate table Subjects;
insert into subjects (subject_name) values ('Math');
insert into subjects (subject_name) values ('Physics');
insert into subjects (subject_name) values ('Programming');

考試表：examinations

創(chuàng)建表

create table if not exists examinations (student_id int, subject_name varchar(20));

插入數(shù)據(jù)

Truncate table Examinations;
insert into examinations (student_id, subject_name) values ('1', 'Math');
insert into examinations (student_id, subject_name) values ('1', 'Physics');
insert into examinations (student_id, subject_name) values ('1', 'Programming');
insert into examinations (student_id, subject_name) values ('2', 'Programming');
insert into examinations (student_id, subject_name) values ('1', 'Physics');
insert into examinations (student_id, subject_name) values ('1', 'Math');
insert into examinations (student_id, subject_name) values ('13', 'Math');
insert into examinations (student_id, subject_name) values ('13', 'Programming');
insert into examinations (student_id, subject_name) values ('13', 'Physics');
insert into examinations (student_id, subject_name) values ('2', 'Math');
insert into examinations (student_id, subject_name) values ('1', 'Math');

要求寫一段SQL，查詢出每個(gè)學(xué)生參加每一門科目測(cè)試的次數(shù)，結(jié)果按student_id和subject_name排序。

select
a.student_id,
a.student_name,
b.subject_name,
count(c.subject_name) as attend_exams
from students as a
join subjects as b
left join examinations as c
on a.student_id=c.student_id and b.subject_name=c.subject_name
group by a.student_id,a.student_name,b.subject_name
order by a.student_id,b.subject_name

image-20200718153354545.png