MySql_DQL相关

DQL：数据库查询语言

insert
insert into dept_SSS (deptno,dname) values (50,'后勤部');   //指定字段
insert into dept_sss values (50,'后勤部','苏州');  //全部字段

delete
delete from dept_SSS where deptno=50;

update
update dept_SSS set location='上海' where deptno=40;

select
select empno,ename,from emp_SSS;

ps:默认自动提交
写法顺序
select->from->where
执行顺序(分析)
from(指定表)->where(记录过滤)->select(结构集)select 询查 from 表名;
表中的字段、常量值、表达式、函数，也可以是多个
查询的结果是一个虚拟的表格

一.基础查询

a.distinct:去重
select distinct dname from dept_sss;
b.concat():连接
select concat('a','b')  as result;
ps:如果有null结果为空.
c.+:运算
select  'xxx'+1;
将字符转为数值，失败值为0，有null结果为null。

二.条件查询

<,>,!=,=,<>,>=,<=
and ,&,or,||
ps:

查询薪水大于等于5000并且小于等于10000的员工信息
select ename,salary from emp_SSS where salary<=10000 and salary>=5000;
select ename,salary from emp_SSS where salary between 5000 and 10000;

select ename,position from emp_SSS
where position='Analyst'or position='Manager';
改动:
select ename,position from emp_SSS
where position in('Analyst','Manager');

使用in时列表中有空值对结果没有影响
空值不能用等于或不等于更任何数据进行比较的

三.模糊查询

like, between and ,in, is null, is not null

//查询职位中第二个字符是'a'的员工姓名和职位
select ename,position from emp_SSS where position like '_a%';
//查询哪些人有奖金(不为空)
select bonus,ename from emp_SSS where bonus is not null;

ps:
in(列表项):判断等于列表项中的任意一项,列表项中有空值没有影响
not in(列表项):判断不等于所有项,列表项中有空值有影响(Empty),使用not in时列表项中的空值必须去掉

四.排序

order by
asc为升序 desc为降序, 不写默认为升序asc

将员工的姓名按首字母排序，并列出名字的长度
select ename,length(ename) from emp_sss  order by convert(ename using gbk);

五.常用函数

单行函数
每一行数据处理后返回一个结果
a.数字函数
round(数字,位数):用于对数字进行四舍五入处理保留到小数点后面指定位数

select round(123.456,2) from dual;
select round(123.456) from dual;
truncate(数字,位数):用于对数字进行截取,截取到小数点指定位数
select truncate(123.456,2) from dual;

abs(数字):返回数据的绝对值
rand():返回0-1随机数
sqrt(数字):返回数据的平方根
pow(x,y):返回x的y次方
mod(x,y):返回x除以y的余数
b.去除空格函数
trim(字符串)

//'(  a bc  )'
select concat ('(',trim('  abc '),')');
//左空格 ltrim left
//右空格 rtrim right
select concat ('(',ltrim('  abc '),')');

c.文本处理函数
upper():转为大写
lower():转为小写
select upper(‘abc’);//ABC
length(字符串):返回字符串字节长度

select ename,length(ename)
from emp_SSS;
substring(字符串,起始位置,最大字符数量):返回指定字符串内容
//从第2个字符开始最多返回2个字符串
select ename,substring(ename,2,2) from emp_SSS;

d.日期\时间
date(时间):返回日期部分
time(时间):返回时间部分
select date(now());
adddate():增加日期(天day,周week,月month,年year);
select adddate(now(),interval 3 week);
//第2个参数默认表示’天’
select adddate(now(),3);
date_add():增加日期
//interval 不能省略
select date_add(now(),interval 2 day);
//重要
date_format(时间,格式):格式日期数据
select date_format(now(),’’);
select date_format(now(),’%x–%m–%d’);
select date_format(now(),’%H:%i:%s’);

二、组函数

组函数:单行数据处理返回一个结果
count():求记录数
sum():求和
avg():求平均
max():求最大
min():求最小

//查询员工表中记录数
select count(empno) from emp_SSS;

六.分组

使用:group by 列名

查询每个部门的最高薪水和最低薪水,要求没有部门的不算在内
select deptno,max(salary),min(salary) from emp_SSS
where deptno is not null group by deptno;

having:对分组之后的数据再进行过滤

查询平均薪水大于5000的部门,要求没有部门的不算在内
分析:
分组->再分组后过滤
select deptno,avg(ifnull(salary,0)) from emp_SSS
where deptno is not null group by deptno
having avg(ifnull(salary,0))>5000;
查询哪些职位的人数超过2个,没有职位的不算在内，计算每种职位的平均薪水并且按照平均薪水升序排序
select position,count(*),avg(ifnull(salary,0)) avg_sal from emp_SSS
where position is not null  group by position 
having count(*)>2 order by avg_sal asc;

总结: 写法顺序:

select ->from->where->group by->having->order by

执行顺序:

from(指定表)->where(记录过滤)->group by(分组)->having(分组后再过滤)->select(结果集)->order by(排序)

七.子查询

子查询:一条sql语句中嵌套select查询语句
查询语句
a.非关联子查询
嵌套的子查询是独立语句,不依赖主查询可以单独执行

1.查询最高薪水时谁
分步:
//查询最高薪水值
select max(salary) from emp_SSS;
//根据最高薪水找人
select ename,salary from emp_SSS where salary=50000;
合并:
select ename,salary from emp_SSS 
where salary=(select max(salary) from emp_SSS);

非关联子查询执行过程:[]
先执行嵌套查询(独立SQL语句)->返回查询结果作为主查询条件->再执行主查询
ps:嵌套子查询是一条独立SQL语句,不依赖能单独执行,并且只能执行一次.
多值:>all,=any,in,>any

查询每个部门最高薪水

select deptno,max(salary) from emp_SSS
where deptno is not null group by deptno;
//根据部门,最高薪水找人
select ename,salary,deptno from emp_SSS 
where (deptno,salary)=any();
合并:
select ename,salary,deptno from emp_SSS 
where (deptno,salary)=any(select deptno,max(salary) from emp_SSS
where deptno is not null group by deptno);

ps:
多列:使用比较规则相同
多值:主查询不关心查询返回的字段,只关心查询返回单值还是多值
b.非关联查询
嵌套子查询是独立语句不依赖主查询
执行过程:
执行嵌套子查询(独立)->返回查询结果(单值,多值)->再执行主查询
注意:
a.执行过程()
b.比较符选择
c.多值多列

1.查询哪些员工的薪水比本部门平均薪水低
select ename,salary,deptno from emp_SSS e
where salary<(select avg(ifnull(salary,0)) from emp_SSS
where deptno=e.deptno);

//e.deptno表示动态数据,由主查询传递过来的
关联子查询执行过程:
先执行主查询()->
执行嵌套子查询(依赖数据,执行多次)->
返回查询结果(主查询条件)->
再执行主查询

2.查询哪些人有下属
分析:
找领导
满足该员工的empno等于leader
非关联
select ename,deptno from emp_SSS
where ifnull(empno,'') in(select leader from emp_SSS);

说明:
exists用于判断子查询有没有数据返回,如果满足关系则有数据返回,不满足则没有数据返回
exists不关心子查询返回结果,子查询中select后面写什么都可以,通常直接1表示.

组合查询

组合查询由两条或者两条以上的select语句组成的
,中间使用union分隔

查询10号部门的员工姓名和薪水
select ename,salary,deptno
from emp_SSS
where deptno=10;
//查询薪水大于6000的员工姓名和薪水
select ename,salary,deptno from emp_SSS where salary>6000;

select ename,salary from emp_SSS where deptno=10
union all
select ename,salary from emp_SSS where salary>6000;//不去重
select ename,salary from emp_SSS where deptno=10
union 
select ename,salary from emp_SSS where salary>6000
order by salary;//去重

组合查询的前提条件,两个结果集必须有相同结构(列的个数,顺序,类型相同)
mysql数据库中的分页
limit:限制查询记录数据
语法:
select 字段 from 表 limit 数量;
或
select 字段 from 表 limit 开始,数量;
每页的开始下标int begin=(page-1)*pageSize;
表间关联查询(多表联合查询)

内连接

语法
表1 [inner] join 表2 on 条件

//查询员工姓名和其部门的名字
select ename,dname 
from emp_SSS e inner join dept_SSS d
on e.deptno=d.deptno;

ps:
内连接的结果集中数据一定是两个表中都能找到匹配记录(内连接结果集
保留匹配上的记录,匹配不上的记录被丢掉)

改动:
select ename,dname
from emp_SSS e,dept_SSS d
where e.deptno=d.deptno;

ps:
如果不加where条件,得到笛卡尔积的结果
行=第一个表行数*第二个表行数

外连接

//左外连接,左边的表是驱动表
表1 left outer join 表2 on 条件

//右外连接,右边的表是驱动表
表1 right join 表2 on 条件

分析:要求查询全部员工,员工表应该作为驱动表
select ename,ifnull(dname,'No Dept')
from emp_SSS e left join dept_SSS d 
on e.deptno=d.deptno;//员工表中的数据全部被查询出来

select ename,ifnull(dname,'No Dept')
from dept_SSS e right join emp_SSS d 
on e.deptno=d.deptno;	

外连接特点:
驱动表中数据全部出现在外连接的
结果集中找不到匹配记录时,则匹配一行空行

约束条件

1.主键约束

主键(primary key 简称PK)
不能重复+不能为空

一张表只能有一个主键,主键可以是
一列(常用)或多列组合

两张定义方式:列级和表级

列级:
create table stu_sss(
id int primary key auto_increment,
name varchar(10)
);

insert into stu_sss values(1001,'张三');
insert into stu_sss values

表级:
drop table stu_sss;

create table stu_sss(
id int,
name varchar(10),
constraint stu_sss_id_pk primary key(id)
);

ps:stu_sss_id_pk 主键约束的名字
表名_列名_约束名

//mysql支持主键自增
primary key auto_increment

2.非空约束

非空约束(not null 简称NN)
定义:列级
drop table stu_sss;

create table stu_sss(
id int primary key auto_increment,
name varchar(10) not null
);
insert into stu_sss values(1001,'张三');
insert into stu_sss values(1002,null);

3.唯一约束

唯一约束(unique 简称UK);

列级:
drop table stu_sss;
create table stu_sss(
id int primary key auto_increment,
name varchar(10) not null,
email varchar(20) unique
);
insert into stu_sss values(1001,'张三','123qq.com');
insert into stu_sss values(1002,'张三','123qq.com');
insert into stu_sss values(1003,'张三',null);
insert into stu_sss values(1004,'张三',null);
表级:
drop table stu_sss;
create table stu_sss(
id int,
name varchar(10),
email varchar(20),
constraint email_unq unique(email),
constraint stu_sss_id_pk primary key(id)
);

4.外键约束

外键约束(foreign key 简称FK)
外键约束定义在两张表的两个字段上,
用于保证这两个字段的关系。
部门表(主表) 		 员工表(从表)
deptno(主键)		 empno(主键)
					 deptno(外键)
如果表A的主关键字段是表B中的字段,则
该字段称为表B的外键,则表A为主表,表B为从表
//创建部门表
create table temp_dept(
deptno int primary key auto_increment,
dname varchar(10) not null
);
//创建员工表
create table temp_emp(
empno int primary key auto_increment,
ename varchar(12) not null,
deptno int,
constraint deptno_emp_deptno_fk
foreign key(deptno)
references temp_dept(deptno)
);
insert into temp_dept values(100,'研发部');
insert into temp_emp values (1001,'张三',100);
先有主表的数据记录

ps:表添加外键之后,两个表就建立关系(依赖),
创建表\插入数据时,先有顺序(主表\从表)
外键是用来实现参照完整性,外键约束将两个表紧密的结合起来,主键主要
用于保证数据的一致性和完整性。

5.check 检查约束

mysql没有效果