目录
数据查询语言（DQL：Data Query Language）： 其语句，也称为“数据检索语句”，用以从表中获得数据，确定数据怎样在应用程序给出。保留字SELECT是DQL（也是所有SQL）用得最多的动词。
1、Mysql单表查询DQL
准备测试表：employee1
create table employee1(
id int primary key AUTO_INCREMENT not null,
name varchar(30) not null,
sex enum('male','female') default 'male' not null,
hire_date date not null,
post varchar(50) not null,
job_description varchar(100),
salary double(15,2) not null,
office int,
dep_id int
);insert into employee1 (name,sex,hire_date,post,job_description,salary,office,dep_id) values
('jack','male','20180202','instructor','teach',5000,501,100),
('tom','male','20180203','instructor','teach',5500,501,100),
('robin','male','20180202','instructor','teach',8000,501,100),
('alice','female','20180202','instructor','teach',7200,501,100),
('tianyun','male','20180202','hr','hrcc',600,502,101),
('harry','male','20180202','hr',NULL,6000,502,101),
('emma','female','20180206','sale','salecc',20000,503,102),
('christine','female','20180205','sale','salecc',2200,503,102),
('zhuzhu','male','20180205','sale',NULL,2200,503,102),
('gougou','male','20180205','sale','',2200,503,102);1.1 简单查询
查询表中所有数据（数据量大时不建议使用）
select * from employee1;
查询指定字段（数据量大时不建议使用）
select name, salary, dep_id from employee1;
1.2 避免重复（DISTINCT）
select post from employee1;
select distinct post from employee1;
1.3 通过四则运算查询
select name, salary, salary*14 from employee1;
select name, salary, salary*14 AS Annual_salary from employee1;
select name, salary, salary*14 Annual_salary from employee1;
1.4 定义显示格式
CONCAT() 函数：用于连接字符串
select concat(name, ' annual salary: ', salary*14) AS Annual_salary from employee1;
1.5 指定条件查询
单条件查询
select name from employee1 where salary=5000;
多条件查询
select name from employee1 where salary>5000 and salary<6000;
1.6 关键字查询
关键字BETWEEN AND
select name,salary from employee1 where salary between 5000 and 15000;
select name,salary from employee1 where salary not between 5000 and 15000;
关键字IS NULL
NULL说明：
①等价于没有任何值、是未知数。
②NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
③对空值做加、减、乘、除等运算操作，结果仍为空。
④比较时使用关键字用“is null”和“is not null”。
⑤排序时比其他数据都小，所以NULL值总是排在最前
select name,job_description from employee1 where job_description is null;
select name,job_description from employee1 where job_description is not null;
select name,job_description from employee1 where job_description='';
关键字IN集合查询
select name, salary from employee1 where salary=4000 or salary=5000 or salary=6000 or salary=9000;
select name, salary from employee1 where salary in (4000,5000,6000,9000);
select name, salary from employee1 where salary not in (4000,5000,6000,9000);
1.7 排序查询
注：
ascending 升序
descending 降序
select name from employee1 order by name;
select name from employee1 order by name desc;
限制次数
select name from employee1 order by name desc limit 3;
select name from employee1 order by name desc limit 1,3;
select name from employee1 order by name desc limit 2,3;
按多列排序
select * from employee1 order by hire_date desc;
select * from employee1 order by hire_date desc,salary asc;
1.8 分组查询
GROUP BY和GROUP_CONCAT()函数一起使用
select dep_id,group_concat(name) from employee1 group by dep_id;
select dep_id,group_concat(name) as emp_members from employee1 group by dep_id;
1.9 模糊查询(通配符)
% 所有字符
select * from employee1 where salary like '%20%';
1.10 正则查询
select * from employee1 where salary regexp '72+';
select * from employee1 where name regexp '^ali';
select * from employee1 where name regexp 'yun$';
select * from employee1 where name regexp 'm{2}';
1.11 函数
count()
max()
min()
avg()
database()
user()
now()
sum()
password()
select max(salary) from employee1;
select min(salary) from employee1;
select avg(salary) from employee1;
select sum(salary) from employee1;
2、Mysql多表查询DQL
多表的连接查询
准备测试表：employee
create table employee(
emp_id int auto_increment primary key not null,
emp_name varchar(50),
age int,
dept_id int
);insert into employee(emp_name,age,dept_id) values
('tianyun',19,200),
('tom',26,201),
('jack',30,201),
('alice',24,202),
('robin',40,200),
('natasha',28,204);select * from employee;
准备测试表：department
create table department(
dept_id int,
dept_name varchar(100)
);insert into department values
(200,'hr'),
(201,'it'),
(202,'sale'),
(203,'fd');select * from department;
2.1 内连接
使用where条件
select employee.emp_id,employee.emp_name,employee.age,department.dept_name
from employee,department
where employee.dept_id = department.dept_id;
使用别名
select a.emp_id,a.emp_name,a.age,b.dept_name from employee a,department b where a.dept_id = b.dept_id;
使用inner join
select a.emp_id,a.emp_name,a.age,b.dept_name from employee a inner join department b on a.dept_id = b.dept_id;
2.2 外连接
左连接
select emp_id,emp_name,dept_name from employee left join department on employee.dept_id = department.dept_id;
右连接
select emp_id,emp_name,dept_name from employee right join department on employee.dept_id = department.dept_id;
2.3 全外连接
select * from employee full join department;
3、复合条件连接查询
3.1 示例一
以内连接的方式查询employee和department表，并且employee6表中的age字段值必须大于25
找出公司所有部门中年龄大于25岁的员工
select emp_id,emp_name,age,dept_name from employee,department where employee.dept_id=department.dept_id and age > 25;
3.2 示例二
以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select emp_id,emp_name,age,dept_name from employee,department where employee.dept_id=department.dept_id order by age asc;
4、子查询
子查询是将一个查询语句嵌套在另一个查询语句中。
内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
还可以包含比较运算符：= 、 !=、> 、<等
4.1 带IN关键字的子查询
查询employee表，但dept_id必须在department表中出现过
select * from employee where dept_id in (select dept_id from department);
4.2 带比较运算符的子查询
运算符：=、!=、>、>=、<、<=、<>
查询年龄大于等于25岁员工所在部门（查询老龄化的部门）
select dept_id,dept_name from department where dept_id in (select distinct dept_id from employee where age >= 25);
4.3 带EXISTS关键字的子查询
EXISTS关键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录，而是返回一个真假值。
True或False，当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
department表中存在dept_id=203，Ture
select * from employee where exists (select * from department where dept_id=203);
296
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
