mysql基础(三)_多表查询

1 多表查询

• 连接查询的定义：
  连接查询是两个或两个以上的表按某个条件连接起来，从中选取需要的数据，连接查询是查询两个或者两个以上的表时使用的，当不同的表中存在相同意义的字段时，可以通过该字段连接这个表。

1.1 内连接查询

1. 查询cms_user表中id,username,email,sex；查询provinces表proName

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM cms_user AS u

INNER JOIN provinces AS p

ON u.proId=p.id;

2. – 查询cms_user id,username,sex
– 查询provinces proName
– 条件是cms_user的性别为男的用户
– 根据proName分组
– 对分组结果进行筛选,选出组中人数>=1的
–　　按照id升序排列
– 限制显示条数 前2条`

SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS totalUsers,GROUP_CONCAT(username)

FROM cms_user AS u

JOIN

provinces AS p

ON u.proId=p.id

WHERE u.sex='男'

GROUP BY p.proName

HAVING COUNT(*)>=1

ORDER BY u.id ASC

LIMIT 0,2;

1.2 外连接查询

-- 插入错误的数据

INSERT cms_user(username,password,regTime,proId)

VALUES('TEST2','TEST2','1381203974',20);

两表信息：

• 左外连接

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM cms_user AS u

LEFT JOIN provinces AS p

ON u.proId=p.id;

• 右外连接

SELECT u.id,u.username,u.email,u.sex,p.proName

FROM cms_user AS u

 RIGHT JOIN provinces AS p

ON u.proId=p.id;

1.3 外键操作

• 外键关系到两个或多个表，先说两个的，分别为主表和子表，一般在子表中加入FOREIGN KEY(子表字段) REFERENCES 主表名(主表中与子表字段有关的字段)，在创建表时设置了外键时，默认情况下，需要先删除子表中的数据才能删除主表中的数据，保证了数据表的完整性，代码演示如下：
  先删除主表内容时会报错，需要先删除子表含有的数据时，才能删主表
  
  正确做法：先删子表，再删主表
  
  

1.4 添加删除外键操作

1. 删除外键

ALTER TABLE employee DROP FOREIGN KEY employee_ibfk_1;

2. 添加外键

ALTER TABLE employee ADD CONSTRAINT emp_fk_dep FOREIGN KEY(depId) REFERENCES department(id);

1.5 联合查询

1. 使用字段UNION的联合查询，重复的数据除外

SELECT username FROM employee UNION SELECT username FROM cms_user;

2. 使用字段UNION ALL的联合查询，所有数据都列出来包括重复

SELECT username FROM employee UNION ALL SELECT username FROM cms_user;

1.6 子查询

1. 查询获得1等奖学金的学员有

SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);

2. 查询所有获得奖学金的学员

SELECT id,username,score FROM student WHERE score>=ANY(SELECT level FROM scholarship);

3. 查询所有学员中获得一等奖学金的学员

SELECT * FROM student WHERE score>=(SELECT level FROM scholarship where id = 1);

4. 查询学员表中没有获得奖学金的学员

SELECT * FROM student WHERE score <ALL(SELECT level FROM scholarship where id = 3);

1.7 正则表达式查询

1. ^匹配字符开始的部分,查询用户名以t开始的用户

SELECT * FROM cms_user WHERE username REGEXP '^t';

2. $匹配字符串结尾的部分,查询用户名以g结尾的用户

SELECT * FROM cms_user WHERE username REGEXP 'g$';

3. .代表任意字符

SELECT * FROM cms_user WHERE username REGEXP 'r..g';

4. [字符集合] [lto]

SELECT * FROM cms_user WHERE username REGEXP '[lto]';

5. [^字符集合] 除了字符集合中的内容

SELECT * FROM cms_user WHERE username REGEXP '[^l]';

6. SELECT * FROM cms_user WHERE username REGEXP ‘[a-k]’;
   

7. SELECT * FROM cms_user WHERE username REGEXP ‘ng|qu|te’;
   

8. SELECT * FROM cms_user WHERE username REGEXP ‘que+’;
   

9. SELECT * FROM cms_user WHERE username REGEXP ‘que{1,3}’;