DataWhale MySQL 任务二[更新版]

• 题外话, 亲测可以修改csdn markdown的图片居中和大小
• 文章内容来自

1. https://www.runoob.com/mysql/mysql-join.html
2. <sql必知必会>
3. Datawhale 项目中遇到的问题, 自己搜

2.1基础表操作

1. 项目三

1.1 复习了下新建表

• 复习了下新建表之类的,注意属性之间, 没有逗号

USE datawhale_hw;
CREATE TABLE course (
  Student VARCHAR(10) NOT NULL,
  Class VARCHAR(20) NOT NULL,
  PRIMARY KEY(Student));

1.2 更改表属性

• 这个作业很鸡贼, 主键不能有重复, 所以只能新建一列, 然后设置成主键
• • 更改主键的代码链接 https://www.cnblogs.com/mfryf/p/3457481.html
• 新增一列默认放最后, 要放前面要加first
• 注意只有主键才可以设置自增, 所以加了之后要先设置它为主键才能设置属性
  – 更改表, 参照两个链接
  –1. https://www.runoob.com/mysql/mysql-alter.html
  –2. https://www.cnblogs.com/huangxm/p/5736807.html
• 注意自增不能单独设置, 要连着前面的int, not null 一起

ALTER TABLE course
  ADD `Row` INT NOT NULL FIRST;

ALTER TABLE Course 
  DROP PRIMARY KEY;

ALTER TABLE Course
  ADD PRIMARY KEY(`Row`);

ALTER TABLE course
  MODIFY `Row` INT NOT NULL AUTO_INCREMENT;

INSERT INTO course
  (Student, Class)
  VALUES
('A','Math'),
('B','English'),
('C','Math'),
('D','Biology'),
('E','Math'),
('F','Computer'),
('G','Math'),
('H','Math'),
('I','Math'),
('A','Math');

1.3 各种查看

• 各种查看包括属性, 备注, 附上链接 https://www.cnblogs.com/zhangyuhang3/p/6873895.html
• 查看表格
  .
  

1.4 复习查询重复值

• 列出所有超过或等于5名学生的课, 已知重复列为课,

SELECT Class FROM course  
  GROUP BY Class HAVING COUNT(Class) >= 5;

2. 项目四

2.1 要求

项目四：交换工资（难度：简单）

创建一个 salary表，如下所示，有m=男性 和 f=女性的值 。

例如:

id	name	sex	salary
1	A	m	2500
2	B	f	1500
3	C	m	5500
4	D	f	500

交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。

运行你所编写的查询语句之后，将会得到以下表:

id	name	sex	salary
1	A	f	2500
2	B	m	1500
3	C	f	5500
4	D	m	500

2.2 实现

1. 复习建表等

USE datawhale_hw;
CREATE TABLE salary (
  Id INT NOT NULL AUTO_INCREMENT,
  `Name` VARCHAR(50) NOT NULL,
  Sex VARCHAR(10) NOT NULL,
  Salary INT NOT NULL,
  PRIMARY KEY(Id));

INSERT INTO salary
  (`Name`, Sex, Salary)
  VALUES
  ("A","m","2500"),
  ("B","f","1500"),
  ("C","m","5500"),
  ("D","f","500");

2. 尝试使用update

• update只能set一个, 不能相互替换全部数据会变成一样的,
• 修改数据 update

UPDATE salary 
  SET Sex = REPLACE(Sex,'m','f');

SELECT * FROM salary;

• 表更新错了之后, 需要改回, 所以修改表之前, 一定要做好备份呀!
  搜了备份的, 不是很看的懂, 先留着, 有一些是直接复制一个表

3. 复制表

• 全部复制

CREATE TABLE targetTable LIKE sourceTable;
INSERT INTO targetTable SELECT * FROM sourceTable;

若只复制表结构, 只要第一行

4. 更新表多个值

• 错误, 注意 and 和or

UPDATE salary 
  SET Sex = 'm' WHERE Id = 1 AND 3;

SELECT * FROM salary;

• 正确: 注意一定要写清字段名

UPDATE salary 
  SET Sex = 'm' WHERE Id = 1 or Id = 3;

SELECT * FROM salary;

5. 替换值

UPDATE table_name SET field=REPLACE(field, 'old-string', 'new-string') 
[WHERE Clause]

6. 列值互换(搜到的, 并非题目要求)

update table a, table b 
set a.filed1= b.field2, a.field2= b.field1
where a.id = b.id

• 注意不要变成一样的, update后面要跟两个列名

• • MSSQL的处理方法
    update table1
    set field_1 = field_2, field_2 = field_1
• • 可是MySQL就不能这样写，不然一列会覆盖另一列记录
    MySQL语句如下
    update table1 a, table1 b
    set a.field_1 = b.field_2, a.field_2 = b.field_1
    where a.id = b.id

7. 正经作业要求, 交换性别 case when

不会做, 搜到的答案

update salary
set sex = 
    case sex
        when 'm' then 'f'
        else 'm'
    end;

2.2 表联结

1.学习内容

MySQL别名
INNER JOIN
LEFT JOIN
CROSS JOIN
自连接
UNION
以上几种方式的区别和联系

• 这篇讲很好:
  https://www.cnblogs.com/BeginMan/p/3754322.html

1. 等值联结 equijion

SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;

2. 内联接

• 注意用inner join 和on

SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;

• 多个表联结

SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Products, Vendors
WHERE Products.vend_id = Vendors.vend_id
AND OrderItems.prod_id = Products.prod_id
AND order_num = 20007;

• 如图

3. 外联结

• 外联结还包括没有关联行的行

• 总是有两种基本的外联结形式：左外联结和右外联结。它们
  之间的唯一差别是所关联的表的顺序。 换句话说， 调整FROM或WHERE
  子句中表的顺序，左外联结可以转换为右外联结。因此， 这两种外联
  结可以互换使用，哪个方便就用哪个

• 左 MySQL LEFT JOIN 会读取左边数据表的全部数据，即便右边表无对应数据

SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;

• 如图

• 右

SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;

• 在使用 OUTER
  JOIN语法时，必须使用 RIGHT或 LEFT关键字指定包括其所有行的表
  （RIGHT指出的是OUTERJOIN右边的表，而LEFT指出的是OUTERJOIN
  左边的表）。上面的例子使用 LEFT OUTER JOIN从 FROM子句左边的表
  （Customers表）中选择所有行。 为了从右边的表中选择所有行，需要使
  用RIGHT OUTER JOIN
• 全外联结（full outer join），它检索两个表中
  的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表
  的不关联的行不同，全外联结包含两个表的不关联的行。

4. Union 组合查询

• MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
• union 没有重复值

SELECT country FROM Websites
UNION
SELECT country FROM apps
ORDER BY country;

• 若想要有重复值 用union all

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;

• 别人总结笔记
  

2. 项目五

• 要求:
  无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：FirstName, LastName, City, State

实现

SELECT person.PersonId, FirstName, LastName, City, State
	FROM person RIGHT OUTER JOIN address
  ON  person.PersonId = address.PersonId;

注意选择适合, 在多个表里都有这个field, 要写明是哪个表里面的

项目六

• 删除重复行

CREATE TABLE email_copy_temp -- 从旧表选择要的部分
	SELECT min(Id), Email FROM email_copy GROUP BY (Email); 
-- 注意有sqlmode问题, 所选field必须在group by 里面, 如果不行, 就在Id先加min, max ,avg之类的

SELECT * FROM email_copy_temp; -- 看下选出来的是什么样的, 其实可以先选择执行select部分,再建表

DROP TABLE email_copy; -- 删掉旧的

ALTER TABLE  email_copy_temp -- 重命名
  RENAME TO  email_copy;

• 结果

- 另附主键问题总结: https://www.cnblogs.com/besty/p/3917592.html

另外练习

sql语句练习50题(Mysql版)
https://blog.csdn.net/fashion2014/article/details/78826299