- 2019.05.13: 先出个ddl版, 交个打卡作业, 项目二还没做…后面更新完整的精美版, 先把遇到的坑写下来…
- 2019.05.16: 更新版把项目二完成了
1. MySQL 安装
制作my.ini
- 用txt, 后面改文件名和后缀
- 注意路径和版本(文件名))改成自己的
未记录临时密码, 重新安装
- 删掉解压后文件夹, 任务管理中把mysql相关服务停掉
配置环境变量
- 修改path
- 新建HOME_MYSQL
Navicat连接
- 2581错误, 最后用了这个方法:
https://blog.csdn.net/forain2012/article/details/81390397
2. 项目一 新建库, 新建表, 查询重复项
-
新建库和新建表, 在cmd里面写的
-
后面摸索了下navicat, 在navicat里面写的, 找了些快捷键什么的, 后面补上网页
-
navicat 快捷键 https://www.cnblogs.com/huaxingtianxia/p/5691759.html
-
插入一行写法
> INSERT INTO email
-> (Email)
-> VALUES
-> ("a@b.com");
Query OK, 1 row affected
- 插入两行的方式
> INSERT INTO email
-> (Email)
-> VALUES
-> ("c@d.com"), ("a@b.com");
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
- 找出重复
> SELECT Email, COUNT(*) AS Repetitions
-> FROM
-> Email
-> GROUP BY Email;
+---------+-------------+
| Email | Repetitions |
+---------+-------------+
| a@b.com | 2 |
| c@d.com | 1 |
+---------+-------------+
2 rows in set
- 显示重复
> SELECT * FROM email
-> WHERE Email IN (
-> SELECT Email FROM email GROUP BY Email HAVING COUNT(Email) > 1 );
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 3 | a@b.com |
+----+---------+
2 rows in set
- 显示一行重复行
SELECT Email FROM email
GROUP BY Email HAVING COUNT(Email) > 1;
项目二
- 新建表并插入数据
USE datawhale_hw;
CREATE TABLE country (
`Name` VARCHAR(10) NOT NULL,
Continent VARCHAR(10) NOT NULL,
Area INT NOT NULL,
Population INT NOT NULL,
Gdp INT NOT NULL,
PRIMARY KEY(`Name`));
# 这里发现字符长度设置太短了, 就改了下属性
ALTER TABLE country
MODIFY COLUMN `Name` varchar(50) NOT NULL,
MODIFY Continent varchar(50) NOT NULL;
INSERT INTO country
(`Name`, continent, area, population, gdp)
VALUES
("Afghanistan","Asia","652230","25500100","20343000"),
("Albania","Europe","28748", "2831741", "12960000"),
("Algeria","Africa","2381741","37100000","188681000"),
("Andorra","Europe","468","78115","3712000"),
("Angola","Africa","1246700","20609294","100990000");
小结
- 从中午开始装了mysql, 走了很多弯路…一一上网搜到了, 后面还是要写成文
- 开始阅读 <SQL必知必会>, 浏览了一半, 要总结下, 代码规范很重要!
- 熟悉mavicat, 在什么地方写语句, 之类的, 花了不少时间, 也要总结一下: 全部在
ctrl
+q
快捷键下的查询编辑器里面写, 可以改, 可以保存, 后面复制也很方便