【任务二】
#作业#
项目三:超过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=女性的值 。
例如:
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 |
——实现过程代码&错误总结——
--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 基础 (二)- 表操作
#学习内容#
-
MySQL表数据类型
-
用SQL语句创建表
语句解释 设定列类型 、大小、约束 设定主键 3. 用SQL语句向表中添加数据
语句解释 多种添加方式(指定列名;不指定列名) 4. 用SQL语句删除表
语句解释 DELETE DROP TRUNCATE 不同方式的区别 5. 用SQL语句修改表
修改列名 修改表中数据 删除行 删除列 新建列 新建行
2.2 MySQL 基础 (三)- 表联结
#学习内容#
- MySQL别名
- INNER JOIN
- LEFT JOIN
- CROSS JOIN
- 自连接
- UNION
- 以上几种方式的区别和联系