预备
MySql安装 参加大佬 https://blog.csdn.net/weixin_44949135/article/details/106661080
MySql安装示例数据 参加大佬 https://blog.csdn.net/kikajack/article/details/80181263
1. 基础
1.1 注释
1.单行注释可以使用#注释符,#注释符后直接加注释内容。格式如下:
#注释内容
2. 单行注释可以使用--注释符,--注释符后需要加一个空格,注释才能生效。格式如下:
-- 注释内容
3. 多行注释使用/* */注释符。/*用于注释内容的开头,*/用于注释内容的结尾。多行注释格式如下:
/*多行注释
第一行注释内容
第二行注释内容
第n行注释内容
*/
1.2 登录
cmd终端登录MySql
mysql -u root -p1234 -- 1234是密码(安装时自己设置的)
MySql终端登录,直接输入密码 1234(安装时自己设置的) 即可
1.3 导入外源数据world;
source D:/world.sql -- 先在本地下载官网示例数据,再导入
1.4 查看数据库
SHOW databases;
1.5使用world 数据
USE world;
1.6 查看world数据下的表
SHOW TABLES;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
1.7 查看表city的表头名称
SHOW COLUMNS FROM city;
# 等价于查看数据表的结构
DESCRIBE city;
#结果如下:
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
2. 简单查询
2.1 对表city的观测数量进行计数
SELECT COUNT(*) FROM city;
2.2 显示表city的所有内容
SELECT * FROM city;
2.3 不重复地显示表city中的Name列
SELECT DISTINCT Name FROM city;
2.4 计算每个Distinct的平均人口数
SELECT District ,AVG(Population) FROM city GROUP BY District;
使用country表
2.5 根据Region分组,查看每组人口最大值,指定最大值列名为p_max
SELECT Region, MAX(Population) AS p_max
FROM country
GROUP BY Region;
2.6 根据Region分组,查看每组人口平均值,指定平均值列名为p_avg
SELECT Region, AVG(Population) AS p_avg
FROM country
GROUP BY Region;
2.7 根据Region分组,查看每组人口平均值,指定平均值列名为p_avg,并按p_avg大小进行排序
SELECT Region ,AVG(Population) AS p_avg
FROM country
GROUP BY Region
ORDER BY p_avg;
2.8根据Region分组,查看每组人口平均值,指定平均值列名为p_avg,选择p_avg 在1000000 到100000000 之间的Region,并按p_avg大小进行排序展示出
SELECT Region ,AVG(Population) AS p_avg
FROM country
GROUP BY Region
HAVING AVG(Population) >1000000 AND AVG(Population) <100000000
# HAVING AVG(Population) BETWEEN 1000000 AND 100000000 -- 上一行使用该命令同理
ORDER BY p_avg;
# ORDER BY AVG(Population); -- 上一行使用该命令同理
#结果如下:
+---------------------------+---------------+
| Region | p_avg |
+---------------------------+---------------+
| Melanesia | 1294400.0000 |
| Caribbean | 1589166.6667 |
| Baltic Countries | 2520633.3333 |
| Nordic Countries | 3452342.8571 |
| Australia and New Zealand | 4550620.0000 |
| Southern Africa | 9377200.0000 |
| Southern Europe | 9644946.6667 |
| Middle East | 10465594.4444 |
| Central Africa | 10628000.0000 |
| Eastern Africa | 12349950.0000 |
| Western Africa | 13039529.4118 |
| Central America | 16902625.0000 |
| Western Europe | 20360844.4444 |
| South America | 24698571.4286 |
| Northern Africa | 24752285.7143 |
| Eastern Europe | 30702600.0000 |
| British Islands | 31699250.0000 |
| Southeast Asia | 47140090.9091 |
| North America | 61926400.0000 |
+---------------------------+---------------+
/* HAVING和WHERE的差别
这里有另一种理解方法,WHERE在数据分组前进行过滤,HAVING在数据分组后进行过滤。
这是一个重要的区别,WHERE排除的行不包括在分组中。
这可能会改变计算值,从而影响HAVING子句中基于这些值过滤掉的分组。
*/
2.9 根据Region分组,查看每组人口平均值,指定平均值列名为p_avg,选择p_avg 在1000000 到100000000 之间的Region,并按p_avg大小进行排序展示出前5行/从第3行开始,展示10行。
SELECT Region ,AVG(Population) AS p_avg
FROM country
GROUP BY Region
HAVING AVG(Population) >1000000 AND AVG(Population) <100000000
ORDER BY p_avg
LIMIT 5;
# LIMIT 2,10; #从第3行开始,展示10行。*** 注意,MySql和Python一样,第一行行序号为0。
SELECT Region ,AVG(Population) AS p_avg
FROM country
GROUP BY Region
HAVING AVG(Population) >1000000 AND AVG(Population) <100000000
ORDER BY p_avg
LIMIT 2,5;
# 结果如下;
+---------------------------+--------------+
| Region | p_avg |
+---------------------------+--------------+
| Baltic Countries | 2520633.3333 |
| Nordic Countries | 3452342.8571 |
| Australia and New Zealand | 4550620.0000 |
| Southern Africa | 9377200.0000 |
| Southern Europe | 9644946.6667 |
+---------------------------+--------------+
MySQL官方客户端 MySQL Workbench 安装 https://zhuanlan.zhihu.com/p/343310548
MySQL Workbench 快捷键
1、执行整篇sql脚本:Ctrl + Shift + Enter
2、执行当前行:Ctrl + Enter
3、注释/取消注释:Ctrl + /
4、格式化sql语句(美化sql语句):Ctrl + B
5、撤销操作:Ctrl + z (返回上一步操作结果)
6、恢复操作:Ctrl + y (恢复下一步操作结果)