Mysql--连接查询

  • 内连接查询
    • 　　意义：找到表和表之间的关系或者是桥梁。连接查询是查询两个或者两个以上的表时使用的。
    • 　　JOIN|CROSS JOIN| INNER JOIN    通过ON  连接条件（这三个方式都行）一般用JOIN进行连接，ON后面接连接条件 
      --查询cms_user id，username        省份表中和用户表中都有id
--查询省份表中的  provinces ，proName
SELECT cms_user.id,username,proName FROM cms_user,provinces;--这个查询是不对的，因为两个表中都有id，查询结果不是真正想要的结果，而是形成了笛卡尔积的形式

--cms_user的proId对应于省份表中的id
SELECT cms_user.id,username,proName FROM cms_user,provinces

WHERE cms_user.proId=provinces.id;

--内连接（这个和上面的where的效果一样（先把每个表要查询东西写上，之后再写上条件）
--查询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;   --通过ON来指定连接条件，这里的条件是当user的表中的proid等会省份表中的id

--第一个表要查询的：查询cms_user id,username,sex
--第二个表要查询的：查询provinces prpName
--ON后面的连接条件是：user表的proid等于省份表的id。
--条件：cms_user的性别为男的用户
SELECT u.id,u.username,u.sex,p.proName
FROM cms_user AS u
JOIN provinces AS p
ON u.proId=p.id
WHERE u.sex='男';


--对分组结果进行筛选，根据proname分组，选出组中人数大于等于1的,并且按照用户表中的id进行升序排序,限制显示条数(前两条)
SELECT u.id,u.username,u.sex,p.proName,COUNT(*) AS Totalsum,GROUP_CONCAT(sex)
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;
;

--查询cms_news中的id，title
--查询cms_cate中的cateName
--连接条件：n.cId=c.id
SELECT n.id,n.title,c.cateName --先写要查询的，
FROM cms_news AS n --再写要连接的几个表
JOIN cms_cate AS c
ON n.cId=c.id;--写连接条件

--cms_news id,title
--cms_cate cateName
--cams_admin username ,role
--连接条件：cms——news和cms——cate的条件：n.cId=c.id        cms_news和cams_admin连接条件：
SELECT n.id,n.title,c.cateName,a.username,a.role
FROM  cms_news AS n--三表连接cms_news  和cms_cate，，，cms_admin分别建立连接
JOIN cms_cate AS c
ON n.cId=c.id
JOIN cms_admin AS a
ON n.aId=a.id;
  • 外连接查询
    • 　　如果插入的错误数据（垃圾数据），内连接不能查询到这个垃圾数据。
    • 　　左外连接：LEFT JOIN 先去显示左表的全部记录然后再去右表查询出符合连接条件的记录
    • 　　右外连接: RIGHT JOIN 显示右表的全部记录及左表符合连接条件的记录 
      --左外连接
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;
  • 外键操作 
    • --创建部门表department(主表)
--id depName

CREATE TABLE IF NOT EXISTS deparment(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

INSERT deparment(depName) VALUES('教学部'),
('市场部'),
('运营部'),
('督导部');

--创建员工表  子表 employee

CREATE TABLE IF NOT EXISTS employee(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED
)ENGINE=INNODB;

INSERT employee(username,depId) VALUES('KING',1),
('QUEUE',2),
('ZHANGSAN',4),
('LISI',4),
('WANGWU',5);

--查询员工的编号，用户名，部门名称   两个表
--连接条件 ：e.depId=d.id;--内连接查询
SELECT e.id,e.username,d.depName FROM
deparment AS d
JOIN employee AS e
ON e.depId=d.id;

--删除督导部(部门解散了，理论上里面的员工也得解散，但是查询员工表的时候，发现还是有员工在督导部下面)
--这就是如果你对父表做操作的时候，发现子表中有数据和父表相关联，就必须做一些操作，这里外键可以使用
SELECT FROM deparment WHERE proName='督导部';

--创建外键--建表的时候创建
--先创建父表，再创建子表，在子表中建立外键
CREATE TABLE IF NOT EXISTS deparment(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS employee(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES deparment(id)--外键
)ENGINE=INNODB;

-----------------当删除父表中的记录，子表中对应的列也会被相应的删除(有关系的列)-------------
--外键  +ON DELETE CASCADE    级联操作
CREATE TABLE IF NOT EXISTS deparment(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS employee(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES deparment(id)  ON DELETE CASCADE--
)ENGINE=INNODB;


-----------------当更新父表中的记录，子表中对应的列也会被相应的更新(有关系的列)-------------
-----外键  +ON DELETE CASCADE  ON UPDATE CASCADE   删除和更新操作都是级联的操作
CREATE TABLE IF NOT EXISTS deparment(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
depName VARCHAR(20) NOT NULL UNIQUE
)ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS employee(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20) NOT NULL UNIQUE,
depId TINYINT UNSIGNED,
FOREIGN KEY(depId) REFERENCES deparment(id)  ON DELETE CASCADE ON UPDATE CASCADE--
)ENGINE=INNODB;
  • 联合查询
    • 　　查询多个表的查询，并且将这些东西合并到一起
    • --UNION   UNION ALL  
--上面两个的区别是：UNION是查询结果去掉相同的记录，UNION ALL 只是简单的合并，不去掉任何内容
--这个中   必须保证两个表中查询的字段的个数相同
SELECT username FROM cms_user UNION SELECT username FROM employee;--去掉相同哦
  • 子查询
    • 　　将一个查询语句嵌套在另一个查询语句中。内层查询语句的结果可以作为外层查询语句的结果
    • --由NOT IN  或  IN引起的子查询
--查询 id号为1,2,3,4的员工的姓名和id号
SELECT id FROM dparment;
SELECT id,username FROM employee WHERE depId IN(1,2,3,4);
SELECT id,username FROM employee WHERE depId IN(SELECT id FROM deparment);--IN中是第一次哦查询结果，外面是第二次的查询结果

--由比较运算符产生的子查询
--创建学员表奖学金表
CREATE TABLE IF NOT EXISTS student(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR (20) NOT NULL UNIQUE,
score TINYINT UNSIGNED
); 

INSERT student(username,score) VALUES('KING1',95),
('KING2',15),
('KING3',25),
('KING4',35),
('KING5',45),
('KING6',55),
('KING7',65),
('KING8',75);

CREATE TABLE IF NOT EXISTS scholarship(
id TINYINT UNSIGNED KEY AUTO_INCREMENT,
level TINYINT UNSIGNED NOT NULL
);

INSERT scholarship(level) VALUES(90),
(80),
(70),
(60),
(50);

--运算符的子查询
--查询一等奖学金个的学院有
SELECT level FROM scholarship WHERE id=1;
SELECT id,username FROM student WHERE score>=90;
SELECT id,username FROM student WHERE score>=(SELECT level FROM scholarship WHERE id=1);

--EXISTS的子查询（存在    条件为真才能执行）
--查询部门表中第五个部门表中的 素有人
SELECT * FROM deparment WHERE id=5;--这个语句是假的，没有部门五
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM deparment WHERE id=5);--外层的语句不会被执行，为真时才能被执行
SELECT id,username FROM employee WHERE EXISTS(SELECT * FROM deparment WHERE id=4);


--SOME|ANY|ALL的子查询
--得到所有获得奖学金的学生：大于等于最小值即可
SELECT id ,username ,score FROM student WHERE score>=ANY(SELECT level FROM scholarship );--找到等级最小的

--查询所有学员中获得一等奖学金的学员
SELECT id ,username ,score FROM student WHERE score>=ALL(SELECT level FROM scholarship );--找打等级最大的

--查询所有学员中没有获得的学员
SELECT id ,username ,score FROM student WHERE score<=ALL(SELECT level FROM scholarship );--小于最小的

--子查询：将查询的结果插入到新建的表中
CREATE TABLE IF NOT EXISTS test1(
id TINYINT UNSIGNED KEY AUTO_INCREMENT, 
num TINYINT UNSIGNED
);
INSERT test1(id,num)
SELECT id,score FROM student;--先是查询结果 ，然后将结果插入到test1的表中

--创建表时同时将查询结果写入到数据表中，与上面类似
CREATE TABLE IF NOT EXISTS test12(
id TINYINT UNSIGNED KEY AUTO_INCREMENT, 
score TINYINT UNSIGNED
)SELECT id,score FROM student;
  • 正则表达式查询 
    • --         .代表任意一个字符      
--查询用户名已以T开始的用户                                  ^ 匹配字符开始的部分
SELECT * FROM cms_user WHERE username REGEXP 't^';--REGEXP+正则表达式
--用户名以g结尾的用户                                                                                      $匹配字符串结尾的部分 
SELECT * FROM cms_user WHERE username REGEXP 't$';
--查询，包含e或者o或者i或者t的用户名的用户，选择任何一个                       []匹配字符集合中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP '[LTOIS]';
--查询除了e或者o或者i或者t中的其他的任何一个字符                  []匹配字符集合中的任意一个字符
SELECT * FROM cms_user WHERE username REGEXP '[^LTOIS]';
  • 运算符
posted @ 2016-08-13 10:41  Kobe10  阅读(420)  评论(0)  编辑  收藏  举报