目录
一、去除重复记录
原表数据不修改,是指查询结果去重。
1.distinct关键字
作用:去掉重复的数据行
--去除工作岗位的重复记录
select distinct job from aTable;
--、姓名和工作岗位两个字段联合起来一起去除重复记录
select distinct name,job from aTable;
注意:
- distinct关键字必须出现在所有字段的最前面
--想要去除工作岗位的重复记录
select name,distinct job from aTable;
//错误,distinct关键字必须出现在所有字段的最前面
- distinct关键字可以和分组函数一起使用
--查询工作岗位的总量
select count(distinct job) from aTable;
二、连接查询
1.定义:
跨表查询,即多张表联合起来一起查询数据。
示例:
查询每个员工所在的部门名称 ,有员工表emp和部门表dept如下:
员工表emp
+--------+--------+
| ename | deptno |
+--------+--------+
| SMITH | 20 |
| ALLEN | 30 |
| WARD | 30 |
| JONES | 20 |
| MARTIN | 30 |
| BLAKE | 30 |
| CLARK | 10 |
| SCOTT | 20 |
| KING | 10 |
| TURNER | 30 |
| ADAMS | 20 |
| JAMES | 30 |
| FORD | 20 |
| MILLER | 10 |
+--------+--------+
dept
+--------+------------+----------+
| DEPTNO | DNAME | LOC |
+--------+------------+----------+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+--------+------------+----------+
笛卡尔积现象 :
如果两个表发生连接没有任何条件限制,会发生笛卡尔积现象 ,即显示的数据行的条数是两张表数据行的乘积。
select ename,dname from emp,dept;
//会显示14*4=56条数据行
为避免笛卡尔积现象,加上条件限制 (要求部门编号相同):
select
ename,dname
from
emp,dept
where
emp.deptno = dname.depton;
--为避免歧义,加上表名
//会显示14条数据行,实际上还是配对了56次
提高效率1:
--若改为emp.ename和dept.ename,可以提高运行效率
select
emp.ename,dept.dname
from
emp,dept
where
emp.deptno = dname.depton;
提高效率2:给表起别名(很重要!!!)
显示的数据表里的字段名还是ename和dname,不发生改变。
--给两张表起别名为e和d
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.depton;
//使用的是SQL92语句
表的连接次数越多,效率越低,所以应该尽量避免多张表的连接。
2.分类:
两种分类:
第一种(根据年代分类):
分为SQL92和SQL99
第二种(根据表连接的方式分类):
内连接:等值连接,非等值连接,自连接
外连接:左外连接,右外连接
全连接
3.内连接
特点:
- 将完全能够匹配上on后条件的数据查询出来,不匹配则不查询
- 连接的两张表的地位是平等的,没有主次之分
(1)等值连接
条件是一个等量关系。
示例:查询每个员工所在的部门名称,显示员工名和部门名。
SQL92语法的缺点:结构不清晰,表的连接条件和后期进一步的筛选条件都在where之后。
//两张表emp和dept进行表连接,条件是e.deptno = d.depton;
--SQL92语法:
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.depton;
SQL99语法的优点:表的连接条件是独立的,放在on后,后期进一步的筛选条件放在where后。
--SQL99语法
select
e.ename,d.dname
from
emp e
inner join ---inner可以省略,不省略可读性更好,一眼看出是内连接
dept d
on
e.depyno = d.deptno;
(2)非等值连接
条件不是一个等量关系。
示例:连接emp员工信息表和salgrade工资等级表,找出每个员工的薪资等级,并显示每个员工的名字,薪资和薪资等级。
//两张表emp和salgrade进行表连接,条件是e.salary between s.lowsal and s.highsal;
select
e.ename,e.salary,s.grade
from
emp e
inner join
salagrade s
on
e.salary between s.lowsal and s.highsal;
(3)自连接
一张表中的数据进行内部连接。
示例:在员工信息表中,查询员工的领导名字,并显示对应的员工名和领导名。
select
a.ename as '员工名',b.ename as '领导名'
from
emp a
inner join
emp b
on
a.mgrNo = b.eNo
--员工的领导编号号,领导的员工编号
技巧:把一张表看成两张表来看
4.外连接
特点:
- 有主次之分
- 右连接:则join右边的表作为主表,另一个作为次表。(左连接同)
- 查询时查询主表的所有数据,只是捎带着查询次表,如果主表存在与次表不匹配的数据,次表直接显示为NULL
示例:查询每个员工所在的部门名称,显示员工名和部门名。
//右连接
select
e.ename,d.dname
from
emp e
right outer join
dept d
where
e.deptno = d.depton;
//左连接
select
e.ename,d.dname
from
dept d
left outer join --outer可以省略,不省略可读性更强,一眼看出是外连接
emp e
where
e.deptno = d.depton;
eg:
如果要查询各个员工和其对应的领导,
使用内连接则查询不出老板的数据,
使用外连接则可以查询出老板的数据,老板匹配的领导为NULL。
5.多张表的连接
语法:
select
...
from
a
join
b
on
a与b的连接条件
join
c
on
a与c的连接条件
join
d
on
a与d的连接条件
....
- 一条SQL语句中,内外连接可以混用。
示例1:找出每个员工的部门名称和工资等级,显示员工名、部门名、工资和工资等级。
思路:根据员工信息表emp、工资等级表salgrade和部门表depart三表连接进行查询。
--找出每个员工的部门名称和工资等级,显示员工名、部门名、工资和工资等级
select
e.ename,d.dname,e.salary,s.grade
from
emp e
join
demp d
on
e.depNo = d.depNo --部门编号相同
join
salgrade s
on
e.salary between s.lowMoney and highMoney
示例2:找出每个员工的部门名称、工资等级和上级领导,显示员工名、部门名、工资、工资等级和上级领导名。
思路:根据员工信息表emp、工资等级表salgrade和部门表depart三表连接进行查询,为了避免老板Boss的数据出现NULL,领导名的查询需要外连接。
--找出每个员工的部门名称、工资等级和上级领导,
--显示员工名、部门名、工资、工资等级和上级领导名。
select
eA.ename,d.dname,e.salary,s.grade,eB.ename
from
emp eA
join
dept d
on
eA.depno = d.depno
join
salgrade s
on
eA.salary between s.lowMoney and s.highMoney
right outer join
emp eB
on
eA.manageno = eB.depno;
--员工的领导编号和领导的员工编号
三、子查询
1.定义:
- 嵌套在其他语句中的select语句称为子查询。
- select语句可以嵌套在select、from和where语句中。
2.where语句中的子查询
示例:找出比最低薪资高的员工的姓名和薪资。
方法一:分布查询(效率低)
分步查询:
select
min(salary)
from
emp
--得到最低薪资是800
select
ename,salary
from
emp
where
salary > 800
方法二:使用子查询
--想要实现
select
ename,salary
from
emp
where
salary > min(salary);
//报错,where中不能使用分组函数
//使用子查询
select
ename,salary
from
emp
where
select(salary > min(salary));
3.from语句中的子查询
技巧:可以将from语句后的子查询的查询结果当成一张临时表。
示例:找出每个工作岗位下的平均薪资所处的薪资等级。
//找出每个工作岗位下的平均薪资所处的薪资等级。
--emp员工表,salgrade薪资登记表
1.找出每个工作岗位下的平均薪资(设置为T表)
select
job, avg(salary)
from
emp
group by
job;
化一:select job, avg(salary) from emp group by job;
2.找出各个平均薪资所处的薪资等级
select
T.avgsal,s.grade
from
select (job, avg(salary) as avgsal from emp group by job) as T
--必须给avg(salary)起别名,不然在on语句中会认为avg是关键字而不是字段名
join
salgrade s
on
T.avgsal between emp.lowMoney and highMoney;
4.select语句中的子查询
示例:找出每个员工的部门名称,要求显示员工名和部门名。
--找出每个员工的部门名称,要求显示员工名和部门名。
--使用emp,demp两张表
select
e.name,(select depart from demp as d where d.depno = e.depno) as dname;
from
emp e,demp d;
--select语句后的子查询语句select只能够返回一条结果
select
e.name,(select depart from demp) as dname;
from
emp e,demp d;
//报错,因为子语句select返回了多条结果
注意:select语句后的子查询语句select只能够返回一条结果,多于一条就会报错。
四、union(合并查询结果集)
1.特点:
1.与表连接相比,union的效率更高,在较少匹配次数下还能完成两个结果集的拼接。
2.表连接:每连接一次新表,匹配次数满足笛卡尔积现象,次数成倍上翻。
eg:a,b,c表各有10条记录,
- 使用表连接:查询次数为10*10*10=1000次
- 使用union:a与b连接一个结果,a与c连接一个结果
- 查询次数均为10*10=100
- 拼接结果后查询次数为100+100=200次
示例:查询工作岗位是'manager'或者'salesman'的员工的姓名。
//使用条件查询
select ename,job from emp where job = 'manager' or job = 'salesman';
select ename,job from emp where job in {'manager' ,'salesman'};
//使用union查询
select ename,job from emp where job = 'manager';
union
select ename,job from emp where job = 'salesman';
2.注意事项:
1.两个结果集的列数要相同
2.两个结果集的数据类型最好一致(oracle中会报错)
五、limit
1.作用:
将查询结果集的一部分取出来,通常用于分页查询中。
eg:百度网站默认一页有10条记录,分页是为了提高用户体验。
2.使用:
完整用法:limit startIndex,length; //startIndex是起始下标(从0开始);length是长度
缺省用法:limit n; //取前n组数据
- 在 order by 语句后执行 !!!
示例:按照薪资降序,取出排名前五的员工
//取出前五名
select
ename,salary
from
emp
order by
salary desc
limit
5;
示例:按照薪资降序,取出排名[3-5]的员工
//从(下标2)第三名开始,再取三名(第3到第5)
select
ename,salary
from
emp
order by
salary desc
limit
2,3;
3.分页
在一个页面下,假设每页显示3条记录:
第1页: limit 0,3 [0,1,2]
第2页: limit 3,3 [3,4,5]
第3页: limit 6,3 [6,7,8]
第PageNo页:limit (PageNo-1)*PageSize,PageSize
(PageNo表示页数,PageSize示每一页的记录的条数)
六、DQL语句总结
书写顺序:
select
from
where
group by
having
order by
limit
执行顺序:
from
where
group by
having
select
order by
limit
七、表
1.创建表
- 建表属于DDL语句
- 表名一般以t_或者tbl_开头,可读性强。
- 能够指定默认值
//创建表的语法
create table 表名(
字段名1 数据类型,
字段名2 数据类型 default '指定的默认值',
字段名3 数据类型
);
数据库中的命名规范:
所有标识符都要用小写,单词与单词之间用下划线隔开。
2.删除表
drop table if exists 表名;
3.常见数据类型
varchar 可变长度的字符串(最长255),根据实际的数据长度动态分配空间
char 定长字符串(最长255),定长是多少就分配多少空间
int 整型(最长11)
bigint 长整型
float 单精度浮点型
double 双精度浮点型
date 短日期类型(年月日)
datetime 长日期类型(年月日时分秒)
clob 字符大对象(最多可以存储4G的字符串,eg:一篇文章),超过255的都用clob
blob 二进制大对象,专门用来存储图片、声音、视频等流媒体。在blob上插入数据需要用到IO流
4.insert 语句(插入数据,属于DML)
语法格式:
insert into 表名(字段名1,字段名2,字段名3) values(值1,值2,值3);
注意:
- 字段名和值的数量和数据类型都要一一对应。
- insert语句只要执行成功,就一定会增加一条记录,没有指定值得字段默认值为NULL。
- 可以省略字段名,但是如果省略values必须全部写上
--创建数据库Reina
mysql> create database Reina;
Query OK, 1 row affected (0.01 sec)
--使用数据库Reina
mysql> use Reina;
Database changed
--在数据库Reina中创建表t_student
mysql> create table t_student(
-> name varchar(10),
-> age int(3),
-> sex char(2)
-> );
Query OK, 0 rows affected, 1 warning (0.02 sec)
--显示t_student当前数据
mysql> desc t_student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| age | int | YES | | NULL | |
| sex | char(2) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
--插入数据
mysql> insert into t_student(name,age,sex)
-> values('Yolanda',19,'w');
Query OK, 1 row affected (0.01 sec)
--查询表中的所有数据
mysql> select * from t_student;
+---------+------+------+
| name | age | sex |
+---------+------+------+
| Yolanda | 19 | w |
+---------+------+------+
1 row in set (0.00 sec)
--更改字段的顺序
mysql> insert into t_student(sex,name,age)
-> values('m','Robin',20);
Query OK, 1 row affected (0.00 sec)
--显示表中所有数据
mysql> select * from t_student;
+---------+------+------+
| name | age | sex |
+---------+------+------|
| Yolanda | 19 | w |
| Robin | 20 | m |
+---------+------+------+
2 rows in set (0.00 sec)
5.数据类型之日期
MySQL的日期格式:
date类型:%Y-%m-%d
datetime类型:%Y-%m-%d %h:%i:%
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
str_to_date 函数:
-
作用:将字符串转换成日期类型date;
-
通常只用在insert语句中;
- 语法格式:
str_to_date('字符串日期','日期格式')
--创建表t_user
mysql> create table t_user(
-> id int,
-> name varchar(32),
-> birth date
-> );
Query OK, 0 rows affected (0.01 sec)
--插入数据
mysql> insert into t_user(id,name,birth) values(1000,'Dell','2000-01-01');
Query OK, 1 row affected (0.00 sec)
//如果日期原本就是date类型(年-月-日),可以直接插入
--插入数据
mysql> insert into t_user(id,name,birth) values(1000,'Apple',str_to_date('08-05-1997','%m-%d-%Y'));
Query OK, 1 row affected (0.00 sec)
//日期原本不是date类型,用str_to_date函数转换格式后可以插入
date_format函数:
- 作用:将日期类型转换成特定格式的字符串
- 通常使用在查询日期方面
- 语法格式:
date_format(日期类型数据,'日期格式');
--显示数据
mysql> select * from t_user;
+------+-------+------------+
| id | name | birth |
+------+-------+------------+
| 1000 | Dell | 2000-01-01 |
| 1001 | Apple | 1997-08-05 |
+------+-------+------------+
2 rows in set (0.00 sec)
--转换成自己喜欢的模式
mysql> select id,name,date_format(birth,'%m/%d/%Y') as birth
-> from t_user;
+------+-------+------------+
| id | name | birth |
+------+-------+------------+
| 1000 | Dell | 01/01/2000 |
| 1001 | Apple | 08/05/1997 |
+------+-------+------------+
2 rows in set (0.00 sec)
now函数
作用:
- 获取系统当前时间
- 且获取的时间时datetime类型的
6.update语句
- 属于DML语句
- 语法格式
update 表名 set 字段名1 = 值1,字段名2 = 值2,字段名3 = 值3... where 条件;
注意:
如果没有where的条件限制,会导致所有数据全部更新!!!
所以一定要加上where条件!!!
7.delete语句
- 属于DML语句
- 语法格式
delete from 表名 where 条件;
注意:
如果没有where的条件限制,会导致所有数据全部删除!!!
所以一定要加上where条件!!!