首先公布一下DAY3的作业答案。
项目三:
mysql> select class
mysql> from courses
mysql> group by class
mysql> having count(distinct student) >= 5;
+-------+
| class |
+-------+
| Math |
+-------+
1 row in set (0.00 sec)
项目四:
mysql> UPDATE `salary` SET `sex` = (
-> CASE `sex`
-> WHEN 'm' THEN 'f'
-> WHEN 'f' THEN 'm'
-> END);
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> 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)
其他解法学习:
mysql> 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)
mysql> update salary
-> set sex=IF(sex='f','m','f');
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> 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)
继续今日DAY4内容
查询数据时,如果表名很长,使用起来不方便,此时,就可以为表取一个别名,用这个别名来代替表的名称 SELECT * FROM 表名 [AS] 别名; 注意,为表指定别名,AS关键字可以省略不写。
mysql> 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)
mysql> select * from salary as ss
mysql> where ss.salary > 2000;
+----+------+-----+--------+
| id | name | sex | salary |
+----+------+-----+--------+
| 1 | A | f | 2500 |
| 3 | C | f | 5500 |
+----+------+-----+--------+
2 rows in set (0.00 sec)
在查询数据时,为了使显示的查询结果更加直观,可以为字段取一个别名 SELECT 字段名 [AS] 别名 [,字段名 [AS] 别名,……] FROM 表名; 注意,为字段指定别名,AS关键字可以省略不写。
mysql> select
mysql> id as user_id,
mysql> name as user_name,
mysql> sex as user_sex,
mysql> salary as user_salary
mysql> from salary;
+---------+-----------+----------+-------------+
| user_id | user_name | user_sex | user_salary |
+---------+-----------+----------+-------------+
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
+---------+-----------+----------+-------------+
4 rows in set (0.00 sec)
2. INNER JOIN、LEFT JOIN、CROSS JOIN、自连接、UNION几种方式区别与联系
inner join:
左右两张表连接字段完全一致
SELECT XXX FROM XXX INNER JOIN XXX ON XXX; 这里 INNER 可以省略
left join:
以左表为全部,去连接右表
cross join:
交叉连接,得到的结果是两个表的乘积,即笛卡尔积.
笛卡尔(Descartes)乘积又叫直积。假设集合A={a,b},集合B={0,1,2},则两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1), (b,2)}。可以扩展到多个集合的情况。类似的例子有,如果A表示某学校学生的集合,B表示该学校所有课程的集合,则A与B的笛卡尔积表示所有可能的选课情况。
笛卡尔积算法针对的是表之间数据行的匹配次数,跟内连接还是外连接无关,至于查询结果与你的查询条件有关系。
自连接:
自连接是连接的一种用法,但并不是连接的一种类型,因为他的本质是把一张表当成两张表来使用。
mysql有时在信息查询时需要进行对自身连接(自连接),所以我们需要为表定义别名。
--自连接用法举例--
CREATE TABLE dept( -- 部门表
deptno INT PRIMARY KEY,-- 部门编号
dname VARCHAR(14),-- 部门名称
loc VARCHAR(13)-- 地点
);
ENGINE=INNODB DEFAULT CHARSET=utf8;
INSERT INTO dept VALUES(10, 'Accounting', 'New York') ;
INSERT INTO dept VALUES(20, 'Research', 'Dallas') ;
INSERT INTO dept VALUES(30, 'Sales', 'Chicago') ;
INSERT INTO dept VALUES(40, 'Operations', 'Boston') ;
INSERT INTO dept VALUES(50, 'Admin', 'Washing') ;<br data-filtered="filtered"><br data-filtered="filtered">
CREATE TABLE emp( -- 员工表
empno INT NOT NULL PRIMARY KEY,-- empno员工号
ename VARCHAR(10), -- ename员工姓名
job VARCHAR(10),-- job工作
mgr INT,-- mgr上级人员编号
hiredate DATETIME,-- 受雇日期
sal DOUBLE,-- 薪金
comm DOUBLE,-- 佣金
deptno INT,-- 部门编号
FOREIGN KEY(deptno) REFERENCES dept(deptno)
);
ENGINE=INNODB DEFAULT CHARSET=utf8;<br data-filtered="filtered"><br data-filtered="filtered">
INSERT INTO emp VALUES(7369, 'Smith', 'Clerk',7902, '1980-12-17',800,0,20) ;
INSERT INTO emp VALUES(7499, 'Allen', 'Salesman',7698,'1981-2-20',1600,300,30) ;
INSERT INTO emp VALUES(7844, 'Turner', 'Salesman',7499, '1981-9-8',1500,0,30) ;
INSERT INTO emp VALUES(7698, 'Tom', 'Manager',0, '1981-9-8',6100,600,40) ;
INSERT INTO emp VALUES(7876, 'Adams', 'Clerk',7900, '1987-5-23',1100,0,20) ;
INSERT INTO emp VALUES(7900, 'James', 'Clerk',7698, '1981-12-3',2400,0,30) ;
INSERT INTO emp VALUES(7902, 'Ford', 'Analyst',7698, '1981-12-3',3000,NULL,20) ;
INSERT INTO emp VALUES(7901, 'Kik', 'Clerk',7900, '1981-12-3',1900,0,30) ;
打开创建好的表:
如果说列出所有员工的姓名及其直接上级的姓名,我们可以通过自连接进行这样的操作:
SELECT e.ename,
(SELECT ename FROM emp d WHERE d.empno=e.mgr) AS boss
FROM emp e;
上面的是一张职员信息表,如果我要查询这张表中的每个职员的上司,那么必须使用自连接来查询.所以为了能实现这个查询,需要给这张表取两个别名,而且查询中所用的所有数据都需要加上表的别名前缀,因为两个表的数据列完全一样。
union:
union 语句:用于将不同表中相同列中查询的数据展示出来;(不包括重复数据)
{A, B} UNION {B, C} = {A, B, C}
SELECT 列名称 FROM 表名称 UNION SELECT 列名称 FROM 表名称 ORDER BY 列名称;
union all:
union all 语句:用于将不同表中相同列中查询的数据展示出来;(包括重复数据)
3. 作业
项目五:组合两张表 (难度:简单)
在数据库中创建表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
项目六:删除重复的邮箱(难度:简单)
编写一个 SQL 查询,来删除 email 表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
+----+---------+
| Id | Email |
+----+---------+
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
+----+---------+
Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 email表应返回以下几行:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | a@b.com |
| 2 | c@d.com |
+----+------------------+
答案明天揭晓。
喜欢就给个好看吧~~