一、单表查询
-- 通配符*,查询整个表 SELECT * FROM `test_zjx`; -- 过滤条件 SELECT * FROM `test_zjx` WHERE `name`='zhangjx3'; -- 条件 and 且 SELECT * FROM `test_zjx` WHERE `name`='zhangjx3' AND sex=1; -- 条件 or 或 SELECT * FROM `test_zjx` WHERE `name`='zhangjx3' OR id=9; -- 条件 and 和 or 同时存在 SELECT * FROM `test_zjx` WHERE `name`='zhangjx3' AND sex=1 OR id=9; -- 不等于 != SELECT * FROM `test_zjx` WHERE sex!=2; SELECT * FROM `test_zjx` WHERE sex<>2;
二、多表查询
1)两张表查询
注意:不加where过滤条件,就是test_zjx表的5条信息,test_info表的5条信息的笛卡儿积。一共25条
例如,A={a,b}, B={0,1,2},则
A×B={(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}
B×A={(0, a), (0, b), (1, a), (1, b), (2, a), (2, b)}
2)外键
多表关联使用:外键
test_zjx和test_info两张表,test_zjx的主键是id,test_info的外键是memberID。如下是如何设置外键:
3)多张表查询
查询某个老师带的是哪个班?
select * from S,SC,C where S.S# =SC.S# and SC.C# = C.C#
三、连接查询
1)内连接----》INNER JOIN
select * from member a,info b where a.id = b.memberID;------》是隐性内连接
改成INNER JOIN:两个表之间加上INNER JOIN ,where 改为on
--内连接INNER JOIN select * from member a INNER JOIN info b on a.id = b.memberID and `address`='北京';
2)左外连接-----LEFT JOIN
左外连接以左表为主表,主表的所有数据都会显示,右表为从表,从表只有匹配的数据才会显示
语法:select 字段1,字段2 from 表1 LEFT JOIN 表2 on 条件
A、查询所有字段*:
--左外连接 select * from member a LEFT JOIN info b on a.id =b.memberID and `address` = '北京';
B、查询部分字段
select a.id,a.name,a.address,b.memberID,b.id from member a LEFT JOIN info b on a.id =b.memberID and `address` = '北京';
3)右外连接-----RIGHT JOIN
与左连接相反,右外连接以坐表为主表,主表的所有数据都会显示,左表为从表,从表只有匹配的数据才会显示
--右外连接 select a.id,a.name,a.address,b.memberID,b.id from member a RIGHT JOIN info b on a.id =b.memberID and `address` = '北京';
四、查询结果排序(order by)
1)升序:asc
--排序 默认时asc,可以不写 select * from member a LEFT JOIN info b on a.id =b.memberID and `address` = '北京' order by a.id asc;
2) 降序:desc
-- 降序 desc,不可省略
-- 按照前后顺序:a.id升序排序完成,有相同序号的,会依据b.id降序(a.id排序的优先级在前面) select * from member a LEFT JOIN info b on a.id =b.memberID and `address` = '北京' order by a.id asc,b.id desc;