20200715 - 数据蛙第九期 - 1

学员作业

mysql基础

1、如何暂停或开启mysql服务？

# 前提：有net命令
net stop mysql  # 暂停mysql
net start mysql # 启动mysql

通过 控制面板的 服务进行暂停

image-20200719144331902

步骤 2)：弹出“计算机管理”对话框，双击“服务和应用程序”，用户可查看计算机的服务状态，MySQL 的状态为“正在运行”，表明该服务已经启动，如图所示。

image-20200715223419145

注意：由于设置了 MySQL 为==自动启动==，在这里可以看到，服务已经启动，而且启动类型为自动。如果没有“正在运行”字样，说明 MySQL 服务未启动。可以直接在“计算机管理”窗口用菜单命令启动，也可以通过 DOS 命令启动 MySQL 服务。单击“开始”→“运行”，输入 cmd 命令，按回车键，弹出命令提示符界面，输入 net start mysql，按回车键，就能启动 MySQL 服务，停止服务的命令为 net stop mysql。

2、如何用cmd命令登录mysql窗口

C:\Users\Administrator>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3、登录时mysql用到 -u \ -p 等参数，如何查看所有参数意思？

mysql --help
mysql  Ver 14.14 Distrib 5.7.17, for Win64 (x86_64)
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                      'rehash' to get table and field completion, but startup
                      and reconnecting may take a longer time. Disable with
                      --disable-auto-rehash.
                      (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                      No automatic rehashing. One has to use 'rehash' to get
                      table and field completion. This gives a quicker start of
                      mysql and disables rehashing on reconnect.
  --auto-vertical-output
                      Automatically switch to vertical output mode if the
                      result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                      (Enables --silent.)
  --bind-address=name IP address to bind to.
  --character-sets-dir=name
                      Directory for character set files.
  --column-type-info  Display column type information.
  -c, --comments      Preserve comments. Send comments to the server. The
                      default is --skip-comments (discard comments), enable
                      with --comments.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       This is a non-debug version. Catch this and exit.
  -T, --debug-info    This is a non-debug version. Catch this and exit.
  -D, --database=name Database to use.
  --default-character-set=name
                      Set the default character set.
  --delimiter=name    Delimiter to be used.
  --enable-cleartext-plugin
                      Enable/disable the clear text authentication plugin.
  -e, --execute=name  Execute command and quit. (Disables --force and history
                      file.)
  -E, --vertical      Print the output of a query (rows) vertically.
  -f, --force         Continue even if we get an SQL error.
  --histignore=name   A colon-separated list of patterns to keep statements
                      from getting logged into syslog and mysql history.
  -G, --named-commands
                      Enable named commands. Named commands mean this program's
                      internal commands; see mysql> help . When enabled, the
                      named commands can be used from any line of the query,
                      otherwise only from the first line, before an enter.
                      Disable with --disable-named-commands. This option is
                      disabled by default.
  -i, --ignore-spaces Ignore space after function names.
  --init-command=name SQL Command to execute when connecting to MySQL server.
                      Will automatically be re-executed when reconnecting.
  --local-infile      Enable/disable LOAD DATA LOCAL INFILE.
  -b, --no-beep       Turn off beep on error.
  -h, --host=name     Connect to host.
...
  -p, --password[=name]
                      Password to use when connecting to server. If password is
                      not given it's asked from the tty.
  -W, --pipe          Use named pipes to connect to server.
  -P, --port=#        Port number to use for connection or 0 for default to, in
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
                      /etc/services, built-in default (3306).
...
  -q, --quick         Don't cache result, print it row by row. This may slow
                      down the server if the output is suspended. Doesn't use
                      history file.
  -u, --user=name     User for login if not current user.
  -U, --safe-updates  Only allow UPDATE and DELETE that uses keys.
  -U, --i-am-a-dummy  Synonym for option --safe-updates, -U.
  -v, --verbose       Write more. (-v -v -v gives the table output format).
  -V, --version       Output version information and exit.
  -w, --wait          Wait and retry if connection is down.
  --connect-timeout=# Number of seconds before connection timeout.
  --max-allowed-packet=#
                      The maximum packet length to send to or receive from
                      server.

mysql练习题

习题

学生表: Students

Column Name	Type
student_id	int
student_name	varchar

主键为 student_id（学生ID），该表内的每一行都记录有学校一名学生的信息。

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

科目表: Subjects

Column Name	Type
subject_name	varchar

主键为 subject_name（科目名称），每一行记录学校的一门科目名称。

Create table If Not Exists Subjects (subject_name varchar(20));

考试表: Examinations

Column Name	Type
student_id	int
subject_name	varchar

Create table If Not Exists Examinations (student_id int, subject_name varchar(20));

这张表压根没有主键，可能会有重复行。

学生表里的一个学生修读科目表里的每一门科目，而这张考试表的每一行记录就表示学生表里的某个学生参加了一次科目表里某门科目的测试。

要求写一段 SQL 语句，查询出==每个学生参加每一门科目测试的次数==，结果按 student_id 和 subject_name 排序。

查询结构格式如下所示：

Students table:

student_id	student_name
1	Alice
2	Bob
13	John
6	Alex

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 table:

subject_name
Math
Physics
Programming

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 table:

student_id	subject_name
1	Math
1	Physics
1	Programming
2	Programming
1	Physics
1	Math
13	Math
13	Programming
13	Physics
2	Math
1	Math

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');

Result table:

student_id	student_name	subject_name	attended_exams
1	Alice	Math	3
1	Alice	Physics	2
1	Alice	Programming	1
2	Bob	Math	1
2	Bob	Physics	0
2	Bob	Programming	1
6	Alex	Math	0
6	Alex	Physics	0
6	Alex	Programming	0
13	John	Math	1
13	John	Physics	1
13	John	Programming	1

结果表需包含所有学生和所有科目（即便测试次数为0）：

Alice 参加了 3 次数学测试, 2 次物理测试，以及 1 次编程测试；

Bob 参加了 1 次数学测试, 1 次编程测试，没有参加物理测试；

Alex 啥测试都没参加；

John 参加了数学、物理、编程测试各 1 次。

答案

三种方法join

cross join
join
inner join
select * from a,b 
后面它可以不加 on 条件，不会报错
最终形成一个笛卡尔积

image-20200719152610320

left join 
right join
会报错

# 1、令学生表挂接每个课程;
select * 
from students s1
cross join subjects s2;

image-20200715232740271

# 2、再上基础上，连接考试表;
select * 
from students s1
cross join subjects s2
left join examinations s3
on s1.student_id = s3.student_id and s2.subject_name = s3.subject_name;

image-20200715232809847

# 3、进行聚合分类;
select *
from students s1
cross join subjects s2
left join examinations s3
on s1.student_id = s3.student_id and s2.subject_name = s3.subject_name
group by s1.student_id,s2.subject_name;
# 体会 on 连接条件，及分组的逻辑

image-20200715232902817

# 4、分组计数，修正字段
select s1.student_id, s1.student_name, s2.subject_name, count(s3.subject_name) as attended_exams
from students s1
cross join subjects s2
left join examinations s3
on s1.student_id = s3.student_id and s2.subject_name = s3.subject_name
group by s1.student_id,s2.subject_name;

image-20200715232940275

20200715 - 数据蛙第九期 - 题目答案

20200715 - 数据蛙第九期 - 题目答案

20200715 - 数据蛙第九期 - 1

学员作业

mysql基础

mysql练习题

习题

答案