子查询
-
单行子查询:返回单行单列
-
多列子查询:返回单行多列(使用成对比较)
-
多行子查询:返回多行单列
-
相关子查询:子查询使用了外部SQL的某些表或列(但是外部SQL不可使用子查询中的表和列)
-
嵌套子查询
-
关联子查询
-
查询 在 Asia工作的员工信息
-- 方法1 select id , first_name,title,salary from s_emp -- exists返回的是true 或者 false 所以select后面的可以随便写 where EXISTS ( select 200 from s_dept d join s_region r on d.region_id = r.id where d.id = s_emp.dept_id and r.name = 'Asia'); -- 方法2 select id , first_name,title,salary from s_emp where EXISTS ( select 200 from s_dept d join s_region r on d.region_id = r.id and r.name = 'Asia' where d.id = s_emp.dept_id ); +----+------------+----------------------+---------+ | id | first_name | title | salary | +----+------------+----------------------+---------+ | 14 | Mai | Sales Representative | 1525.00 | | 23 | Radha | Stock Clerk | 795.00 | | 9 | Antoinette | Warehouse Manager | 1300.00 | | 22 | Eddie | Stock Clerk | 800.00 | +----+------------+----------------------+---------+
-
嵌套子查询:在子查询内部可以继续嵌套子查询
select id,first_name,title,salary from s_emp e where exists (select 1 from s_dept d where d.id = e.dept_id and d.region_id = (select id from s_region where name ='Asia'));
-
DML语句 数据操纵语言
-
DML语句涉及:INSERT / UPDATE / DELETE
-
查询事物提交方式:1为自动提交
select @@autocommit; +--------------+ | @@autocommit | +--------------+ | 1 | +--------------+
-
关闭事物自动提交
set autocommit = 0;
-
回滚事物:为了撤销删除的数据
rollback;
-
-
CASH语句
-
语法
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE
-
查询所有 将其性别翻译为 男(1) 或 女(2)
select id,name CASE when gender = 1 THEN '男' when gender = 2 THEN '女' else '未知' end as 性别 from table;
连接查询
-
内连接(全部都是满足连接条件的连接)
select * from t_class c join t_major m on c.major_id = m.id;
-
左外连接(除了全部都是满足连接条件的连接,还有不满足条件的数据)
join之前那张表中不满足条件的被保留下来 select * from t_class c left outer join t_major m on c.major_id = m.id;
-
右外连接
join之后那张表中不满足条件的被保留下来 select * from t_class c left outer join t_major m on c.major_id = m.id;
-
全外连接(MYSQL不支持)
-
非标准写法
select * from t_class c ,t_major m where m.id = c.major_id;