一、去重
eg:查找有哪些工作岗位?
select distinct job from emp;//去掉重复数据
select ename, distinct job from emp;//这个是错误的,distinct 必须出现在所有字段前面
select distinct deptno,job from emp;//表示deptno与job联合去重
eg:查找岗位的数量?
select count(distinct job) from emp;
二、连接查询
在实际开发中,都是多张表查询。
2.1 链接查询的分类
SQL92 (数据库管理员,DBA有些还在使用)
SQL99 (新的语法)
内连接:
等值连接
非等值连接
自链接
外连接:
左外链接
右外连接
全连接(很少用)
2.2、在表的链接查询有一种现象被称为:笛卡尔积现象
dept表中有四条数据,emp表中有14条数据,笛卡尔积后(emp中的每一条数据都与dept表中的四条数据进行连接)14乘以4 共56条数据
select ename,dname from emp,dept;
上面的语句效率太低,为解决效率问题,建议给表起别名:
select e.ename,d.dname from emp e,dname d;
注意:避免了笛卡尔积现象,不会减少匹配次数,还是匹配56次,只不过会显示有效数据
eg:找出每一个员工的部门名称,要求显示员工名和部门名
select e.ename,d.dname
from emp e,dept d
where e.deptno=d.deptno; (SQL92,以后不用)
2.3 内连接之等值连接
特点:条件是等量关系
eg:找出每一个员工的部门名称,要求显示员工名和部门名(SQL99)
select e.ename,d.dname
from emp e (inter) join dept d
on e.deptno=d.deptno;
inter可以省略
2.4内连接之非等值连接
特点:连接条件的关系是非等量关系
eg:查询员工的姓名,工资及工资等级?
select e.ename,e.sal,s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal;
2.5自连接
一张表看作两张表,进行连接
eg:查询每个员工的领导姓名,显示员工姓名与之对应的领导名
select e.empno,e.ename,e1.ename from emp e join emp e1 on e.mgr=e1.empno;
(这个语句的执行结果 会少了没有上级领导的员工)
2.6外连接
内连接与外连接的区别?
内连接:A表与B表是平等关系,没有主副之分
外连接:一张表是主表,一张表是副表,查询主表里的数据,副表负责匹配主表所需的信息,如果在副表中找不到,则匹配null。
eg:查询每个员工的领导姓名,显示员工姓名与之对应的领导名(每个员工都需要查出来)
使用左外连接:
select e.empno,e.ename,e1.ename from emp e left (outer) join emp e1 on e.mgr=e1.empno;
使用右外连接:
select e.empno,e.ename,e1.ename from emp e1 right (outer) join emp e on e.mgr=e1.empno;
(这个语句的执行结果,所有员工都会显示,没有上级领导的员工,领导的字段为null)
outer 可以省略
三、三表查询
eg: 找出每个员工的部门名称和工资等级
select e.ename,d.dname,s.grade
from emp e join dept d on e.deptno=d.deptno
join salgrade s on e.sal between s.losal and s.hisal;
语法格式:
select xxx
from A
join B
on xxx
join C
on xxx
where xxxx;
eg:找出每个员工的部门名称和工资等级及领导名
select e.ename,d.dname,s.grade,e1.ename as '领导'
from emp e join dept d on e.deptno=d.deptno
join salgrade s on e.sal between s.losal and s.hisal
left join emp e1 on e.mgr=e1.empno;
四、子查询
select语句中嵌套select语句
4.1 在where 子句中嵌套子查询
eg: 查找高于平均工资的员工信息?
select * from emp where sal>(select avg(sal) from emp);
4.2在from子句中嵌套查询
eg:找出每个部门平均工资的工资等级?
第一步:select avg(sal) as avg ,deptno from emp group by deptno;//这是每个部门的平均薪水,这是一张新表。用这个给新表和salgrade表进行连接。
select s.grade,e.deptno ,e.avg
from (select avg(sal) as avg ,deptno from emp group by deptno) e
join salgrade s on e.avg between s.losal and s.hisal;
eg:找出每个部门平均的薪水等级(先找出每个员工的薪水等级,再按照部门求平均等级)
第一步: select s.grade ,e.deptno as dno from emp e join salgrade s on e.sal between s.losal and s.hisal //这句是每名员工的薪资等级
第二步:每个部门的平均等级
select e.deptno as dno ,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
4.3在select后面嵌套子查询
eg:查询每个员工所在的部门名称(使用嵌套子查询)
select e.ename,(select dname from dept d where d.deptno=e.deptno) as dname from emp e;
五、union(可以将查询结果集相加)
eg:找出工作岗位是manager或是salesman的员工?
第一种:select ename,job from emp where job='manager' or job='salesman';
第二种:select ename ,job from emp where job in('manager','salesman');
第三种:
select ename,job from emp where job='manager'
union
select ename,job from emp where job='salesman';
六、limit
limit是mysql特有的,Oracle中有一个相同的机制叫rownum
6.1 语法机制:
limit startIndex,length
startIndex 表示起始位置 从0开始
length表示取几个
eg: 取出工资前5名的员工sele
select ename,sal from emp order by sal desc limit 0,5;
select ename,sal from emp order by sal desc limit 5;//默认startIndex=0
注意:limit 是sql语句中最后执行的
6.2 通用的标准分页sql
假如每页显示3条记录
第一页:limit 0,3
第二页:limit 3,3
第三页:limit 6,3
......
总结规律:每页显示n条数据,则第m页 limit(m-1)*n ,n
七、创建表
7.1 语法格式:
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
.........
)
7.2常见的数据类型
int 整数型
bigint 长整型
float 浮点型
char 定长字符串
varchar 可变长字符串
date 日期类型
blob 二进制大对象 (存储图片,视频等流媒体信息)Binary Large Object
clob 字符大对象(存储较大文本,比如4G的字符串)
char与varchar怎么选择?
当某个数据长度不发生改变的时候,是定长的如:性别,生日等都可用char
当一个字段的数据长度不确定,如简介、姓名等可以用varchar
表名最好以t,tal开头,见名知意
7.3建表
学生信息:学号 长整型,姓名 可变长字符串 ,性别 定长字符串,班级 可变长字符串,生日 定长字符串
create table t_student(
sno bigint,
sname varchar(6),
sex char(1),
classno varchar(255),
brith char(10)
);
八、插入数据
inster into 表名(字段名1,字段名2......) values (值1,值2......);
8.1 对t_student插入数据
注意:字段名全部省略,默认values添加所有
insert into t_student values(1801,"jack","1","1.1","2000-12-12");
删表
drop table if exists t_student;
重建
create table t_student(
sno bigint default 1,//给学号默认值为1,那么在显示表结构的时候可以看到default是1,否则为null
sname varchar(6),
sex char(1),
classno varchar(255),
brith char(10)
);
8.2 一次插入多个值
insert into t_student values(1801,"jack","1","1.1","2000-12-12"),
(1802,"mark","1","1.1","2001-12-12");
九、表的复制
语法:create table 表名 as select 语句;
将查询结果当作表创建出来
9.1将查询结果插入到一张表中
语法:insert into 表名 select 语句;//对于这样的语句有插入要求。
十、修改数据
update
语法格式:update 表名 set 字段名1=值1,字段名2=值2......where 条件;
注意没有条件,整张表全部更新
eg:将部门10的Loc修改为shnaghai,将部门名修改为renshibu
update dept set loc="shanghai",dname="reshibu" where deptno=10;
十一、删除数据
语法:delete from 表名 where 条件;
注意:没有条件全部删除
删除部门10的员工信息?
delete from emp1 where deptno=10;
删除所有数据
delete from emp1;
怎么删除大表?
truncate table empl1;//表被截断,不可回滚,永久丢失
注意:增删改查有一个术语:CRUD
Create Retrieve(检索) Update Delete
十二、创建表约束
(Constraint)约束
12.1什么是约束?
在创建表的时候,可以给某些字段添加约束,添加约束的作用是为了保证表中的数据合法,有效,完整。
12.2常见的约束
非空约束(not null)
唯一约束 (unique)
主键约束(primary key)
外键约束(foreign key)
检查约束(check)
12.3 not null
drop table if exists t_user;
create table t_user(
id bigint,
username varchar(255) not null,
password varchar(255)
);
Insert into t_user(id,password) values (1,'123');
报错:ERROR 1364 (HY000): Field 'username' doesn't have a default value