MySQL-任务二&打卡-DW

【任务二】

#作业#

项目三:超过5名学生的课(难度:简单)

创建如下所示的courses 表 ,有: student (学生) 和 class (课程)。

例如,表:

+---------+------------+

| student | class      |

+---------+------------+

| A       | Math       |

| B       | English    |

| C       | Math       |

| D       | Biology    |

| E       | Math       |

| F       | Computer   |

| G       | Math       |

| H       | Math       |

| I       | Math       |

| A      | Math       |

+---------+------------+

编写一个 SQL 查询,列出所有超过或等于5名学生的课。

应该输出:

+---------+

| class   |

+---------+

| Math    |

+---------+

Note:

学生在每个课中不应被重复计算。

 

——实现过程代码&错误总结——

--1、创建表
CREATE TABLE Course(
student VARCHAR(10),
class VARCHAR(30)
);

--2、插入数据
INSERT INTO Course
VALUES
('A','Math'),
('B','English'),
('C','Math'),
('D','Biology'),
('E','Math'),
('F','Computer'),
('G','Math'),
('H','Math'),
('I','Math'),
('A','Math'),
('A','English')
;

--查询当前表的所有记录
SELECT * FROM Course;
/*
+---------+----------+
| student | class    |
+---------+----------+
| A       | Math     |
| B       | English  |
| C       | Math     |
| D       | Biology  |
| E       | Math     |
| F       | Computer |
| G       | Math     |
| H       | Math     |
| I       | Math     |
| A       | Math     |
| A       | English  |
+---------+----------+
11 rows in set (0.00 sec)
*/
--3、查询数据
/*思路:
首先要考虑,过程中需要,去除重复数据,即同一个人选同一门课程的记录;
方法:按课程名进行分组,对学生进行唯一值筛选,通过这两个条件查询,获得无重复数据的全部记录,再进行修改,用COUNT()函数获取计数,通过>=5获得最终结果;
*/
--1)查询获得无重复数据的课程及学生数,

SELECT class,COUNT(*) FROM Course
GROUP BY class
HAVING DISTINCT student;
/*
HAVING 不能使用DISTINCT
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DISTINCT student' at line 3
*/

SELECT class,COUNT(*) FROM Course
GROUP BY class;
/*
分组查询全部记录并计数,与所求结果不符
+----------+----------+
| class    | COUNT(*) |
+----------+----------+
| Biology  |        1 |
| Computer |        1 |
| English  |        2 |
| Math     |        7 |
+----------+----------+
4 rows in set (0.05 sec)
*/

SELECT DISTINCT class,COUNT(*) FROM Course
GROUP BY class;
/*
SELECT加DISTICT筛选的是统计后class和count的结果唯一,与所求结果不符
+----------+----------+
| class    | COUNT(*) |
+----------+----------+
| Biology  |        1 |
| Computer |        1 |
| English  |        2 |
| Math     |        7 |
+----------+----------+
4 rows in set (0.00 sec)
*/

SELECT DISTINCT class,COUNT(*) FROM Course
GROUP BY class
HAVING COUNT(DISTINCT student);
/*
HAVING中count求唯一值的计数,select加distinct查询count*,与所求结果不符
+----------+----------+
| class    | COUNT(*) |
+----------+----------+
| Biology  |        1 |
| Computer |        1 |
| English  |        2 |
| Math     |        7 |
+----------+----------+
4 rows in set (0.03 sec)
*/

SELECT class,COUNT(*) FROM Course
GROUP BY class
HAVING COUNT(DISTINCT student);
/*
--HAVING中count求唯一值的计数,select查询count*,与所求结果不符
+----------+----------+
| class    | COUNT(*) |
+----------+----------+
| Biology  |        1 |
| Computer |        1 |
| English  |        2 |
| Math     |        7 |
+----------+----------+
4 rows in set (0.00 sec)
*/


SELECT class,COUNT(*) FROM Course
GROUP BY class
HAVING COUNT(DISTINCT(student));
/*
--DISTINCT(字段)
+----------+----------+
| class    | COUNT(*) |
+----------+----------+
| Biology  |        1 |
| Computer |        1 |
| English  |        2 |
| Math     |        7 |
+----------+----------+
4 rows in set (0.03 sec)
*/

SELECT class,COUNT() FROM Course
GROUP BY class
HAVING COUNT(DISTINCT(student));
/*
HAVING中count求唯一值的计数,select查询count,count()不存在会报错
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') FROM Course
GROUP BY class
HAVING COUNT(DISTINCT(student))' at line 1
*/


SELECT class,COUNT(DISTINCT(student)) FROM Course
GROUP BY class
HAVING COUNT(DISTINCT(student));
/*
select的字段count也需要是唯一值
+----------+--------------------------+
| class    | COUNT(DISTINCT(student)) |
+----------+--------------------------+
| Biology  |                        1 |
| Computer |                        1 |
| English  |                        2 |
| Math     |                        6 |
+----------+--------------------------+
4 rows in set (0.00 sec)
*/

--2)查询获得无重复数据且学生数>=5的课程及学生数
SELECT class,COUNT(DISTINCT(student)) FROM Course
GROUP BY class
HAVING COUNT(DISTINCT(student))>=5;
/*
+-------+--------------------------+
| class | COUNT(DISTINCT(student)) |
+-------+--------------------------+
| Math  |                        6 |
+-------+--------------------------+
1 row in set (0.03 sec)
*/

--3)查询获得无重复数据且学生数>=5的课程
SELECT class FROM Course
GROUP BY class
HAVING COUNT(DISTINCT(student))>=5;
/*
+-------+
| class |
+-------+
| Math  |
+-------+
1 row in set (0.00 sec)
*/

 

 

项目四:交换工资(难度:简单)

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

例如:

idnamesexsalary
A   m   2500   
B   f   1500   
C   m   5500   
D   f   500   

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

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

idnamesexsalary
A   f  2500   
B   m   1500   
C   f   5500   
D   m   500   

 

——实现过程代码&错误总结——

--1、创建表
CREATE TABLE Salary(
id INT,
name VARCHAR(10),
sex VARCHAR(10),
salary INT
);

--2、插入记录
INSERT INTO salary
VALUES
(1,'A','m',2500),
(2,'B','f',1500),
(3,'C','m',5500),
(4,'D','f',500)
;

--3、查询当前表所有记录
SELECT * FROM salary;
/*
+------+------+------+--------+
| id   | name | sex  | salary |
+------+------+------+--------+
|    1 | A    | m    |   2500 |
|    2 | B    | f    |   1500 |
|    3 | C    | m    |   5500 |
|    4 | D    | f    |    500 |
+------+------+------+--------+
4 rows in set (0.00 sec)
*/

--4、更新记录

--replace()函数,直接更换字符
UPDATE Salary 
SET sex = REPLACE
(sex,'f','m'),
(sex,'m','f')
;
/*
错误,不能同时更新两条
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(sex,'m','f')' at line 4
*/

UPDATE Salary 
SET sex = 'f' WHERE sex='m'
AND
SET sex = 'm' WHERE sex='f';
/*错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SET sex = 'm' WHERE sex='f'' at line 4
*/

--新思路,通过中间变量赋值,交换字符
UPDATE Salary SET sex = REPLACE 
(sex,'f','x'),
(sex,'m','f'),
(sex,'x','m');
/*错误
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',
(sex,'m','f'),
(sex,'x','m')' at line 2
*/

UPDATE Salary SET sex = REPLACE (sex,'f','x');
UPDATE Salary SET sex = REPLACE (sex,'m','f');
UPDATE Salary SET sex = REPLACE (sex,'x','m');
SELECT * FROM salary;
/*
成功交换,更新只能一次更新一种情况,不能多情况同时更新
+------+------+------+--------+
| id   | name | sex  | salary |
+------+------+------+--------+
|    1 | A    | f    |   2500 |
|    2 | B    | m    |   1500 |
|    3 | C    | f    |   5500 |
|    4 | D    | m    |    500 |
+------+------+------+--------+
4 rows in set (0.00 sec)
*/

--使用SET+WHERE,用中间变量,更换字符
UPDATE Salary 
SET sex = 'x' WHERE sex='f',
SET sex = 'f' WHERE sex='m',
SET sex = 'm' WHERE sex='x';
/*错误,不能同时更新多条
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '
SET sex = 'f' WHERE sex='m',
SET sex = 'm' WHERE sex='x'' at line 2
*/

UPDATE Salary SET sex = 'x' WHERE sex='f';
UPDATE Salary SET sex = 'f' WHERE sex='m';
UPDATE Salary SET sex = 'm' WHERE sex='x';
SELECT * FROM salary;
/*
交换成功,SET+WHERE也只能一次更新一种情况
+------+------+------+--------+
| id   | name | sex  | salary |
+------+------+------+--------+
|    1 | A    | m    |   2500 |
|    2 | B    | f    |   1500 |
|    3 | C    | m    |   5500 |
|    4 | D    | f    |    500 |
+------+------+------+--------+
4 rows in set (0.00 sec)
*/

 

#作业#

项目五:组合两张表 (难度:简单)

在数据库中创建表1和表2,并各插入三行数据(自己造)

 

表1: Person

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| PersonId | int |

| FirstName | varchar |

| LastName | varchar |

+-------------+---------+

PersonId 是上表主键

表2: Address

+-------------+---------+

| 列名 | 类型 |

+-------------+---------+

| AddressId | int |

| PersonId | int |

| City | varchar |

| State | varchar |

+-------------+---------+

AddressId 是上表主键

编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:FirstName, LastName, City, State

——实现过程代码&错误总结——

--1、创建Person表
CREATE TABLE IF NOT EXISTS Person(
PersonId INT,
FirstName VARCHAR(50),
LastName VARCHAR(50),
PRIMARY KEY(PersonId)
);
--注意不要忘记最后的);

--2、插入数据
INSERT INTO Person(PersonId,FirstName,LastName)
VALUES
(01,'Three','Zhang'),
(02,'Four','Li'),
(03,'Two','Wang'),
(04,'One','Wang')
;
--查看数据情况
SELECT * FROM Person;
/*
+----------+-----------+----------+
| PersonId | FirstName | LastName |
+----------+-----------+----------+
|        1 | Three     | Zhang    |
|        2 | Four      | Li       |
|        3 | Two       | Wang     |
|        4 | One       | Wang     |
+----------+-----------+----------+
4 rows in set (0.00 sec)
*/

--3、创建Address表
CREATE TABLE IF NOT EXISTS Address(
AddressId INT,
PersonId INT,
City VARCHAR(30),
State VARCHAR(30),
PRIMARY KEY(AddressId),
FOREIGN KEY(PersonId)
);
/*
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 8
*/
CREATE TABLE IF NOT EXISTS Address(
AddressId INT,
PersonId INT,
City VARCHAR(30),
State VARCHAR(30),
PRIMARY KEY(AddressId),
FOREIGN KEY(PersonId) REFERENCES Person(PersonId)--注意外键需要写被参照表
);

--4、插入数据
INSERT INTO Address(AddressId,PersonId,City,State)
VALUES
(01,03,'BeiJing','ChaoYang'),
(02,01,'ZhongShan',NULL)--可以直接给NULL值
;
INSERT INTO Address(AddressId,PersonId,State)
VALUES(03,02,'HaiDian');--可以通过选择字段赋值,未赋值的字段为空值
--查看数据情况
SELECT * FROM Address;
/*
+-----------+----------+-----------+----------+
| AddressId | PersonId | City      | State    |
+-----------+----------+-----------+----------+
|         1 |        3 | BeiJing   | ChaoYang |
|         2 |        1 | ZhongShan | NULL     |
|         3 |        2 | NULL      | HaiDian  |
+-----------+----------+-----------+----------+
3 rows in set (0.00 sec)
*/

--5、连接表,查询人对应的地址信息
SELECT p.FirstName,p.LastName,a.City,a.State 
FROM Person as p LEFT JOIN Address as a
ON p.PersonId = a.PersonId;
/*
+-----------+----------+-----------+----------+
| FirstName | LastName | City      | State    |
+-----------+----------+-----------+----------+
| Three     | Zhang    | ZhongShan | NULL     |
| Four      | Li       | NULL      | HaiDian  |
| Two       | Wang     | BeiJing   | ChaoYang |
| One       | Wang     | NULL      | NULL     |
+-----------+----------+-----------+----------+
4 rows in set (0.02 sec)
*/

 

项目六:删除重复的邮箱(难度:简单)

编写一个 SQL 查询,来删除 email 表中所有重复的电子邮箱,重复的邮箱里只保留 **Id ***最小 *的那个。

+----+---------+

| Id | Email |

+----+---------+

| 1 | a@b.com |

| 2 | c@d.com |

| 3 | a@b.com |

+----+---------+

Id 是这个表的主键。

例如,在运行你的查询语句之后,上面的 Person表应返回以下几行:

+----+------------------+

| Id | Email |

+----+------------------+

| 1 | a@b.com |

| 2 | c@d.com |

+----+------------------+

——实现过程代码&错误总结——

--1、创建表
CREATE TABLE email2(
id INT,
email VARCHAR(30),
PRIMARY KEY(id)
);
--2、插入数据
INSERT INTO email2
VALUES
(1,'a@a.com'),
(2,'c@d.com'),
(3,'a@a.com')
;
SELECT * FROM email2;
/*
+----+---------+
| id | email   |
+----+---------+
|  1 | a@a.com |
|  2 | c@d.com |
|  3 | a@a.com |
+----+---------+
3 rows in set (0.00 sec)
*/
--3、查询去除重复邮箱保留id最小
/*
思路:重复邮箱,id最小,GROUP BY按照email进行分组,HAVING+MIN()函数取id最小,查询出id和email字段;
*/
SELECT id,email FROM email2
GROUP BY email
HAVING MIN(id);
/*
+----+---------+
| id | email   |
+----+---------+
|  1 | a@a.com |
|  2 | c@d.com |
+----+---------+
2 rows in set (0.01 sec)
*/

2019年5月15日 23点46分

 

 

 

(学习内容,——待填充——)

2.1 MySQL 基础 (二)- 表操作

#学习内容#

  1. MySQL表数据类型

  2. 用SQL语句创建表

    语句解释     设定列类型 、大小、约束     设定主键 3. 用SQL语句向表中添加数据

    语句解释     多种添加方式(指定列名;不指定列名) 4. 用SQL语句删除表

    语句解释     DELETE     DROP     TRUNCATE     不同方式的区别 5. 用SQL语句修改表

    修改列名     修改表中数据     删除行     删除列     新建列     新建行

 

 

2.2 MySQL 基础 (三)- 表联结

#学习内容#

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

 

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值