目录
一、DQL
1.1、语法结构
select 字段列表
from 表名或视图
[where 条件]
[group by 字段列表
[having 分组条件]]
[order by 字段 [asc | desc]]
[limit m, n]
语法说明:
(1)使用 select 关键字来做查询;
(2)字段列表是使用英文逗号来进行分割 ;
(3)from关键字后跟表的,可以是多个表名称或视图名称;
(4)group by:用于分组查询;
(5)having:用于结分组查询时进行过滤——条件的筛选;
(6)order by:用于数据显示时的排序;
(7)asc:表示长序,它是默认值;
(8)desc:表示降序;
(9)limit:用于分页显示,它有两个参数,第一个参数是起始值,第二个参数是显示的数量。
1.2、着重号
有些字段或表名可以使用了数据库的关键字或保留,这时我们就可以使用着重号(也叫反引号``)来进行着重标明
示例:
select `name` from t_stu;
1.3、SQL简单使用
示例一:查询员工表中所有行所有列
select * from t_employee;
注意:在实际开发中,不建议在SQL中使用 *号
示例二:查询部分字段
select eid,ename,gender from t_employee;
示例三:查询员工表中性别为男的数据
select eid,ename,gender from t_employee where gender='男';
二、别名查询
2.1、给字段名取别名
select ename as '员工姓名',tel '手机号' from t_employee;
2.2、给表取别名
select eid, ename as name, gender, tel from t_employee as e;
select t_employee.eid, ename as name, gender, tel from t_employee as e;
select e.eid, ename as name, gender, tel from t_employee as e;
三、去重查询(distinct)
select gender from t_employee;
select distinct gender from t_employee;
这个关键字好用,但是要慎用。因为它会做全表扫描,然后再把数据进行去重后再展示出来。比 较耗性能。
注意:这个关键字尽量少用或不用。
四、单表查询
4.1、算术运算符
select eid,ename,gender,tel from t_employee where eid%2=1;
示例二:筛选出eid除以2后等于1的数据
select eid,ename,gender,tel from t_employee where eid div 2=1;
4.2、比较运算符
select * from t_salary where basic_salary != 10000;
示例二: 查询 commission_pct 等于 null 数据
select * from t_salary where commission_pct = null;
select * from t_salary where commission_pct is null;
4.3、逻辑运算符
select eid,ename,gender,birthday from t_employee where gender='男' and birthday < '1990-01-02';
上面的 SQL 语句也可以写成如下形式:
select eid,ename,gender,birthday from t_employee where gender='男' && birthday < '1990-01-02';
示例二:查询职位编号 job_id 是 1 或 2 的员工
select job_id, job_name, description from t_job where job_id=1 or job_id=2;
上面的SQL语句也可以写成如下形式:
select job_id, job_name, description from t_job where job_id=1 || job_id=2;
4.4、范围和集合
select eid, basic_salary from t_salary where basic_salary between 9000 and 12000;
注意:between...and... 查询时,条件是一个闭区间查询。并且后一个值要大于前一个值。
上面的SQL语句和一面这条SQL语句执行结果是一样的。
select eid,basic_salary from t_salary where basic_salary>=9000 and basic_salary<=12000;
示例二:查询 eid 的值为 1、3、5的基本薪资。
select eid,basic_salary from t_salary where eid=1 or eid=3 or eid=5;
上面的SQL语句是可以正确的查询出我们想要的结果。但是这种查询效率很低,我们不推荐SQL 使用 or 查询,因为它会做全表扫描。
select eid,basic_salary from t_salary where eid in (1,3,5);
4.5、模糊查询
select eid,ename,gender,tel from t_employee where ename like '%孙%';
select eid,ename,gender,tel from t_employee where ename like '孙%';
select eid,ename,gender,tel from t_employee where ename like '孙_';
上面的SQL语句表示姓名是以孙开头,并且名字中只有两个字。
4.6、统计查询
select avg(basic_salary) as '平均工资' from t_salary;
select max(basic_salary) as '最高工资' from t_salary;
示例三:获取工资表中最低的工资
select min(basic_salary) as '最低工资' from t_salary;
示例四:获取所有员工的工资总和
select sum(basic_salary) as '工资总和' from t_salary;
示例五:获取员工总数
select count(*) as '员工总数' from t_salary;
4.7、分页查询(limit)
limit offset,pagecount;
(1)offset:它是分页的起始偏移值,它需要计算;
(2)pagecount:显示的记录数
示例一:分页显示员工信息,每页显示 2 名员工。
select eid,ename,gender,tel,birthday from t_employee limit 0, 2;
select eid,ename,gender,tel,birthday from t_employee limit 2, 2;
第三页数据:
select eid,ename,gender,tel,birthday from t_employee limit 4, 2;
从上面的三条SQL语句可以发现,唯一变化的是 offset 这个参数,而这个参数的计算是根据页码和每页显示的记录数来计算而得。它的计算公式为:
4.8、分组查询
示例一:根据员工的职位来分组查询员工信息。
select eid,ename,gender,job_id from t_employee group by job_id;
[42000][1055] Expression #1 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'mydb.t_employee.eid' which is not
functionally dependent on columns in GROUP BY clause; this is incompatible
with sql_mode=only_full_group_by
select eid,ename,gender,job_id from t_employee group by eid,ename,gender,job_id;
select job_id from t_employee group by job_id;
这次的结果确实是按员工的职位来进行分组,但是我们看不到每职位的员工有多少个。
从上面分析过程可以发现:我们的分组字段是可以写在 select 列表中的,还有统计查询的 一些函数也是可以写在分组查询中的 select 列表中的,其它的字段不能写在 select 列表 中。
select dept_id, count(*) from t_employee group by dept_id;
示例三:根据部门编号统计员工人数,要求统计的部门编号大于1
select dept_id, count(*) from t_employee group by dept_id having dept_id > 1;
select dept_id, count(*)
from t_employee
where gender = '男'
group by dept_id
having dept_id in (1,2);
从 SQL 优化角度,我们最好是定义 where 条件来过滤大量的数据后,再使用 having 来进行过滤,这样的话性能就会高很多。
4.9、排序查询
select eid,ename,gender,tel,birthday from t_employee;
select eid,ename,gender,tel,birthday
from t_employee
order by eid desc ;
题目的要求是按员工的编号进行升序显示,因此在 SQL 就应该使用 order by 语句。
select eid,ename,gender,tel,birthday from t_employee
order by eid asc;
从上面两条 SQL 语句执行的结果可以发现,结果是一样的,也就是说,我们在查询时,默认就是按照主键的升序进行显示的。
select eid,ename,gender,tel,birthday
from t_employee
order by eid desc ;
示例二:按员工的基本工资的高低来进行显示
select eid, basic_salary
from t_salary
order by basic_salary desc;
对于排序的字段也是可以有多个的,当有多个时,会优先对第一个进行排序,只有当第一个的值相同时,才会对第二个值进行排序,依次类推。
示例三:按员工的职位高低进行排序,如果职位相同再按员工的编号升序进行排序。
select eid,ename,gender,job_id
from t_employee
order by job_id
desc,eid asc;
五、多表查询
5.1、笛卡尔积
select ename,dname from t_employee,t_department;
从上面的查询结果可以发现,我们在做关联查询时, 要尽量避免出现笛卡尔积的情况出现。
5.2、关联查询
(1)where:适用于所有的关联查询。
(2)on:只能和 join一起使用,只能写在关联条件中,但是它可以和 where 一起使用。
(3)using:只能和 join 一起使用,并且要求两个关联的字段在关联表中名称一致。
示例一:把关联条件写在 where 语句中
select ename,dname
from t_employee,t_department
where dept_id=did;
示例二:把关联条件写在 on 语句中
select ename,dname
from t_employee join t_department on dept_id=did;
两条查询的结果一样。
示例三:查询员工姓名和它的基本工资信息
select ename,basic_salary
from t_employee as e,t_salary as s
where e.eid=s.eid;
select ename,basic_salary
from t_employee e join t_salary ts on e.eid = ts.eid;
select ename,basic_salary
from t_employee join t_salary using (eid);
注意:要想使用 using,那么必须要关联查询相关表中被关联的条件字段名称必须相同。
示例四:查询员工姓名、基本工资和所在部门名称
(1)员工姓名:在 t_employee 表中
(2)基本工资:在 t_salary 表中
(3)部门名称:在 t_deparment 表中
select ename,basic_salary,dname
from t_employee e join t_salary using(eid)
join t_department td on td.did = e.dept_id;
5.3、内连接
select [columns]
from 表1,表2,...
where [condition];
select [columns] from 表1 inner join 表2 on [condition] where [其它条件];
select [columns] from 表1 cross join 表2 on [condition] where [其它条件];
select [columns] from 表1 join 表2 on [condition] where [其它条件];
执行关联查询后的结果为如上图所示的红色区域内的数据。也就是两张表中共同的部分。
select ename,dname
from t_employee e inner join t_department td on td.did = e.dept_id;
select ename,dname
from t_employee e join t_department td on td.did = e.dept_id;
select ename,dname
from t_employee e cross join t_department td on td.did = e.dept_id;
5.4、外连接
5.4.1、左外连接
insert into t_department(dname, description) values('财务部', '发工资部门');
示例一:查询所有部门信息以及部门员工信息
select did,dname,description, ename,gender,tel
from t_department td left join t_employee te on td.did=te.dept_id;
如果以员工表为左表,那么查询的结果就不一样:
select ename,gender,tel,did,dname,description
from t_employee te left join t_department td on te.dept_id=td.did;
上面的示例是返回左表中所有行,如果左表中的行在右表中没有匹配的数据,则会以空值来填充。
select did,dname,description,eid,ename,tel,dept_id
from t_department td left outer join t_employee te on td.did = te.dept_id
where te.dept_id is null;
示例三:查询所有员工信息,以及员工所在的部门信息
insert into t_employee(ename) values('刘备');
5.4.2、右外连接
select eid,ename,gender,did,dname,description
from t_employee e right join t_department d on e.dept_id=d.did;
(2)查询右表在左表中没有的数据。
示例二:查询没有员工的部门信息。
select eid,ename,gender,did,dname,description
from t_employee e right join t_department d on e.dept_id=d.did
where dept_id is null;
5.4.3、全外连接(union)
select column_list
from 表1 left join 表2
on 条件
union
select column_list
from 表1 right join 表2
on 条件
select did,dname,eid,ename,gender,tel
from t_department d left join t_employee te on d.did = te.dept_id
union
select did,dname,eid,ename,gender,tel
from t_department d right join t_employee te on d.did = te.dept_id;
(2)查询所有没有员工的部门和没有部门的员工信息
select did,dname,eid,ename,gender,tel
from t_department d left join t_employee te on d.did = te.dept_id
where te.dept_id is null
union
select did,dname,eid,ename,gender,tel
from t_department d right join t_employee te on d.did = te.dept_id
where te.dept_id is null;
5.5、自连接
update t_employee set mid=1 where eid=7;
select e1.ename, e2.ename
from t_employee e1, t_employee e2
where e1.mid=e2.eid;
上面的方式我们使用隐式的内连接来实现的自连接查询。
select e1.ename, e2.ename
from t_employee e1 join t_employee e2 on e1.mid=e2.eid;
六、子查询
(1)where 型子查询:把子查询作为 where 的条件(条件)
(2)from 型子查询:把子查询作为 from 的临时表(数据)
6.1、where 型子查询
select e.eid,ename,basic_salary
from t_employee e, t_salary s
where e.ename='孙红雷' and e.eid=s.eid;
第二步:查询工资比 12000 高的员工编号
select eid,basic_salary from t_salary
where basic_salary > 12000;
第三步:查询员工编号为 4 和 6 的员工信息
select eid,ename,gender,tel
from t_employee
where eid in (4,6);
根据上面三步的分析可以发现,我们所需要的条件都是另一个 SQL 语句的返回结果中,因此,我们可以使用子查询来实现上面三步的操作。
select eid,ename,gender,tel
from t_employee
where eid in (
select eid
from t_salary
where basic_salary > (
select basic_salary
from t_employee e, t_salary s
where e.ename='孙红雷' and e.eid=s.eid
)
);
select eid,ename, dept_id
from t_employee
where ename in ('孙红雷','鹿晗');
select eid,ename,gender,tel,dept_id
from t_employee
where dept_id in (1,1);
根据上面的分析,我们把第一步查询到的数据作为第二步查询所使用的条件即可。
select eid,ename,gender,tel,dept_id
from t_employee
where dept_id in (
select dept_id
from t_employee
where ename in ('孙红雷','鹿晗')
);
执行结果一样
6.2、from 型子查询
select dept_id, avg(basic_salary)
from t_employee e, t_salary s
where e.eid=s.eid
group by dept_id;
通过上面的SQL语句,我们可以正确得到部门的平均工资。但是有一个小的问题:我们从查询的结果可以看到,平均工资的字段名称为 avg(basic_salary) 。这个名称不便于我们使用,为了便于后续使用,我们经这个字段取一个别名,例如叫 avg_salary。
select dept_id, avg(basic_salary) as avg_salary
from t_employee e, t_salary s
where e.eid=s.eid group by dept_id;
通过这样调整后,后续使用就比较方便了。
select e1.eid, dept_id,basic_salary
from t_employee e1 join t_salary s1
on e1.eid=s1.eid
and dept_id in (1, 2);
第三步:查询比部门平均工资高的员工和基本工资(需要把前面两步进行结合)
select te.eid, te.ename, basic_salary
from t_salary ts join t_employee te
on ts.eid=te.eid
join (
select dept_id, avg(basic_salary) as avg_salary
from t_employee e, t_salary s
where e.eid=s.eid
group by dept_id
) as temp
on te.dept_id=temp.dept_id
where ts.basic_salary > temp.avg_salary;
6.3、exists 型子查询
select eid,ename,gender,dept_id
from t_employee
where dept_id is not null;
第二步:在第一步的基础上来实现最终的功能
select * from t_department
where exists(select * from t_employee);
(2)测试子查询中没有数据
select * from t_department
where exists(select * from t_employee where eid > 10);
根据上面的测试情况可以发现,我们子查询的结果不能为空,如果为空则外层查询就没有数据了;如果子查询不为空,则外层查询就会有数据。
select did,dname,description
from t_department
where exists(
select eid,ename,gender,dept_id
from t_employee
where dept_id is not null
);
上面的结果中包含了没有员工的部门——财务部,这显然不符合我们的题要求,原因在于我们子查询中条件给的不对,因为没有我外部表进行关联,导致条件不符合。
select did,dname,description
from t_department td
where exists(
select eid,ename,gender,dept_id
from t_employee te
where td.did=te.dept_id
);
此时就得到了最终的结果了。
6.4、复制表子查询
6.4.1、复制表结构
create table 表名 like 被复制的表名;
create table t_stu_new like t_stu;
desc t_stu_new;
6.4.2、复制表结构和数据
create table 表名 as (select * from 被复制的表名);
create table t_stu_new_2 as (select * from t_stu);
desc t_stu_new_2;
select * from t_stu_new_2;
6.4.3、复制表结构+数据
insert into 复制后的表名 select * from 被复制的表名;
create table t_stu_new_3 like t_stu;
再向这个表添加数据
insert into t_stu_new_3 select * from t_stu;
select * from t_stu_new_3;
6.4.4、跨数据库复制
语法格式为:
create table 表名 like 被复制数据库名.被复制的表名;
create table 新数据库名.表名 like 被复制数据库名.被复制的表名;
6.4.5、复制表中部分字段和数据
create table 表名 as (
select 要复制的字段列表
from 被复制的表名
where 条件
)
create table t_stu_new_4 as (
select id,name
from t_stu
where id=1
)
desc t_stu_new_4;
再查看数据
lect * from t_stu_new_4;
6.4.6、在创建表的同时定义表的字段
语法格式为:
create table 表名(
字段名 类型(长度) [主键][自增][非空][默认值][注释]
) as (
select 被复制的字段 from 被复制的表名
)
create table t_stu_new_5(
id int(11) primary key auto_increment
) as (
select id,name from t_stu );
desc t_stu_new_5;