20200715 - 數(shù)據(jù)蛙第九期 - 1
學(xué)員作業(yè)
大板揪http://www.itdecent.cn/p/a4da468ce484
喂!嘿嘿！http://www.itdecent.cn/p/8a263f700926
想哭的我戴上喵的面具http://www.itdecent.cn/p/6a71e2c3d49a
徐羽豐http://www.itdecent.cn/p/0455e6551a69
Autumnhttp://www.itdecent.cn/p/52e7b1086ba3
位春燕http://www.itdecent.cn/p/c7ac7ed0e652
mysql基礎(chǔ)
1、如何暫停或開啟mysql服務(wù)？
# 前提：有net命令
net stop mysql # 暫停mysql
net start mysql # 啟動(dòng)mysql
通過(guò) 控制面板的 服務(wù)進(jìn)行暫停
image-20200719144331902
步驟 2)：彈出“計(jì)算機(jī)管理”對(duì)話框，雙擊“服務(wù)和應(yīng)用程序”，用戶可查看計(jì)算機(jī)的服務(wù)狀態(tài)，MySQL 的狀態(tài)為“正在運(yùn)行”，表明該服務(wù)已經(jīng)啟動(dòng)，如圖所示。
image-20200715223419145
注意：由于設(shè)置了 MySQL 為==自動(dòng)啟動(dòng)==，在這里可以看到，服務(wù)已經(jīng)啟動(dòng)，而且啟動(dòng)類型為自動(dòng)。如果沒(méi)有“正在運(yùn)行”字樣，說(shuō)明 MySQL 服務(wù)未啟動(dòng)?？梢灾苯釉凇坝?jì)算機(jī)管理”窗口用菜單命令啟動(dòng)，也可以通過(guò) DOS 命令啟動(dòng) MySQL 服務(wù)。單擊“開始”→“運(yùn)行”，輸入 cmd 命令，按回車鍵，彈出命令提示符界面，輸入 net start mysql，按回車鍵，就能啟動(dòng) MySQL 服務(wù)，停止服務(wù)的命令為 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、登錄時(shí)mysql用到 -u \ -p 等參數(shù)，如何查看所有參數(shù)意思？
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練習(xí)題
習(xí)題
學(xué)生表: Students
| Column Name | Type |
|---|---|
| student_id | int |
| student_name | varchar |
主鍵為 student_id（學(xué)生ID），該表內(nèi)的每一行都記錄有學(xué)校一名學(xué)生的信息。
create table if not exists Students (student_id int , student_name varchar(20));
科目表: Subjects
| Column Name | Type |
|---|---|
| subject_name | varchar |
主鍵為 subject_name（科目名稱），每一行記錄學(xué)校的一門科目名稱。
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));
這張表壓根沒(méi)有主鍵，可能會(huì)有重復(fù)行。
學(xué)生表里的一個(gè)學(xué)生修讀科目表里的每一門科目，而這張考試表的每一行記錄就表示學(xué)生表里的某個(gè)學(xué)生參加了一次科目表里某門科目的測(cè)試。
要求寫一段 SQL 語(yǔ)句，查詢出==每個(gè)學(xué)生參加每一門科目測(cè)試的次數(shù)==，結(jié)果按 student_id 和 subject_name 排序。
查詢結(jié)構(gòu)格式如下所示：
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 |
結(jié)果表需包含所有學(xué)生和所有科目（即便測(cè)試次數(shù)為0）：
Alice 參加了 3 次數(shù)學(xué)測(cè)試, 2 次物理測(cè)試，以及 1 次編程測(cè)試；
Bob 參加了 1 次數(shù)學(xué)測(cè)試, 1 次編程測(cè)試，沒(méi)有參加物理測(cè)試；
Alex 啥測(cè)試都沒(méi)參加；
John 參加了數(shù)學(xué)、物理、編程測(cè)試各 1 次。
答案
三種方法join
cross join
join
inner join
select * from a,b
后面它可以不加 on 條件，不會(huì)報(bào)錯(cuò)
最終形成一個(gè)笛卡爾積
image-20200719152610320
left join
right join
會(huì)報(bào)錯(cuò)
# 1、令學(xué)生表掛接每個(gè)課程;
select *
from students s1
cross join subjects s2;
image-20200715232740271
# 2、再上基礎(chǔ)上，連接考試表;
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、進(jìn)行聚合分類;
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;
# 體會(huì) on 連接條件，及分組的邏輯
image-20200715232902817
# 4、分組計(jì)數(shù)，修正字段
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
