数据SQL语言的深入应用

1，SQLite 别名：可以把表或者列重命名为另一个名字。
e.g. select A.staff_nu, A.staff_name, B.dept_name as deptname from
t_staff as A, t_dept as B where A.dept_nu = B.dept_nu;
2，SQLite 自连接语句
e.g. select A.staff_nu,A.staff_name, B.lead_nu, B.lead_name from t_staff as A left join
t_dept as B on A.lead_nu = B.lead_nu;
3，select 内置函数:avg(),sum(),count(),max(),min()
e.g. select avg(staff_age) from t_stu where dept_nu = 1;
4，SQLite 分页语句limit offset
e.g. select * from t_stu limit 4 offset 3;
5，SQLiet 子查询

a.嵌套子查询： select * from t_staff where staff_pay <
(select staff_pay from t_staff where staff_name = ‘张三’);

b.in子查询：select * from t_staff where staff_pay in
(select staff_pay from t_staff where staff_name = ‘张三’);

c.exists 子查询: select * from t_staff where exists
(select staff_pay from t_staff where t_staff.dept_nu = t_dept.dept_nu);

6，SQLite虚拟表
e.g. select * from t_staff where (select * from t_staff where staff_sex = ‘男’)t1
where staff_age > 25;

7，SQLite 并集操作union,union all(重复的列一起显示)
e.g. select * from t_staff where staff_sex = ‘男’
union
select * from t_staff where staff_sex = ‘女’；