回顾
1 关系型数据
Mysql Oracle DB2 (IBM)
SqlServer(MS Sever)
2 Mysql -u -p
exit
show databases
use ___
show tables
desc table_name
source .sql ---> 导入数据库中
SQL语句 作用查询数据库
语法关键字 select from where order by
select column_name,column_name *
from table_name
where
order by asc desc
查询关键字
1 = > < <> >= <=
2 and or not
3 [not] between xx and xxx
4 [not] in
5 like % _
6 is null is not null
Mysql 函数
1 字符串相关函数
length('')
char_length();
upper();
lower();
left();
rigth();
lpad();
rpad();
instr();
replace();
repeat();
trim();
substring();
concat();
reverse();
2 数字函数
floor(123.456) ---> 截取小数点
123
select floor(123.456);
round(123.4) ----> 四舍五入
select round(123.6);
select round(123.456,2);
特点: 保留小数点后2位,并进行四舍五入
select round(153.456,-2);
特点: 剔除小数点前的2位,并进行四舍五入
truncate() ---- 截取小数
select truncate(12.234,1);
参数1 任意小数
参数2 小数点后保留位数
特例 select truncate(12,2);
12.00
3日期函数
2011-10-19 10:05
NOW() 返回当前的日期和时间
select now();
Current(当前) 缩写 CUR
CURDATE() 返回当前的日期
select curdate();
CURTIME() 返回当前的时间
select curtime();
DATE(时间) 提取是 时间 的日期
TIME(时间) 提取是 时间 的时分秒
select date(now());
select time(now());
select date('2001-10-11 10:30');
select time('2009-10-11 10:31');
Year() 提取对应时间 的年份
Month() 提取对应时间 的月份
select year(now());DA
select month(now());
select month('2011-10-11 13:45');
select extract(HOUR_MINUTE from now());
EXTRACT(字段 From 日期) 返回日期/时间按的单独部分
字段的合法值:
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
DATE_ADD(日期, INTERVAL 数量 字段)
select date_add(now(), interval 10 day);
DATE_SUB(日期, INTERVAL 数量 字段)
select date_sub(now(), interval 8 day);
select datediff(str_to_date("2010-1-1",'%Y-%m-%d'),str_to_date("2013-8-8",'%Y-%m-%d')); 2010-1-1 减去 2013-8-8的天数
DATEDIFF(日期1, 日期2) 日期的间隔差距 日期1-日期2=差距
select datediff(now(),now());
DATE_FORMAT(日期, 格式)
格式字符:
%a 缩写星期名
%b 缩写月名
%c 月,数值
%D 带有英文前缀的月中的天
%d 月的天,数值(00-31)
%e 月的天,数值(0-31)
%f 微秒
%H 小时 (00-23)
%h 小时 (01-12)
%I 小时 (01-12)
%i 分钟,数值(00-59)
%j 年的天 (001-366)
%k 小时 (0-23)
%l 小时 (1-12)
%M 月名
%m 月,数值(01-12)
%p AM 或 PM
%r 时间,12-小时(hh:mm:ss AM 或 PM)
%S 秒(00-59)
%s 秒(00-59)
%T 时间, 24-小时 (hh:mm:ss)
%U 周 (00-53) 星期日是一周的第一天
%u 周 (00-53) 星期一是一周的第一天
%V 周 (01-53) 星期日是一周的第一天,与 %X 使用
%v 周 (01-53) 星期一是一周的第一天,与 %x 使用
%W 星期名
%w 周的天 (0=星期日, 6=星期六)
%X 年,其中的星期日是周的第一天,4 位,与 %V 使用
%x 年,其中的星期一是周的第一天,4 位,与 %v 使用
%Y 年,4 位
%y 年,2 位
SimpleDateFormat
String --- Date
Date ---- String
new SimpleDateFormate('yyyy-MM-dd');
sdf.format(date) --- String
sdf.parse(string) --- date
select now();
select date_format(now(),'%y-%m-%d %h %p');
STR_TO_DATE(字符串, 格式)
数字 10
字符串 ''
字符串 ---- 日期类型的数据
str_to_date('2010-10-11','%Y-%m-%d');
select date_add(str_to_date('2010-10-11','%Y-%m-%d'),interval 10 day);
select datediff(now(),str_to_date('2011-10-01','%Y-%m-%d'));
LAST_DAY(日期) 获得月份所在月的最后一天
select last_day(now());
select last_day(str_to_date('2011-02-10','%Y-%m-%d'));
-------------------------------------------------------------
查询 employees 每个月倒数第三天入职的员工信息
31 -- 29 2
30 -- 28 2
28 -- 26 2
29 -- 27 2
last_day -- hire_date = 2
datediff(last_day-hire_date) = 2
select *
from employees
where datediff(hire_date,last_day(hire_date)) =16;
select datediff(str_to_date("2010-1-1",'%Y-%m-%d'),
str_to_date("2013-8-8",'%Y-%m-%d'));
2010-1-1 减去 2013-8-8的天数
---------------------------------------------------------
别名 alias
列别名
select first_name as name ,salary*12 as anuSalary
from employees ;
表别名
select *
from employees as emp;
select emp.*
from employees as emp;
--------------------------------------------
Mysql ifnull()
Oracle nvl()
思考
10000 0.4
10000 + 10000*0.4 == 14000
算出所有雇员的总月薪
null值 在sql 语句中是不能够进行算术运算
select salary+(salary)*ifnull(commission_pct,0)
from employees ;
ifnull(参数1,参数2)
如果参数1 不是null 返回参数1的数据
如果参数1 是null 返回参数2的数据
select ifnull('suns','sunshuai');
select ifnull(null,'sunshuai');
-------------------------------------------
聚合函数
avg() ---> 计算某一列的平局值
sum() ---> 计算某一列的数值和
max() ---> 计算某一列的最大值
min() ---> 计算某一列的最小值
count() ----> 计算数量(数据条数)
查询所有employees 表中 salary 的平局值
select min(salary) as Sal
from employees;
select count(*)
from employees;
a)* 计算整个表中的行数
select count(commission_pct)
from employees ;
b)列名 计算当前列中非空数据的条数
c)count(distinct 列名) 去除当前列重复数据
并计算数据条数
select count(distinct first_name)
from employees ;
distinct 作用 去出查询的重复数据
select distinct first_name
from employees
where first_name = 'Steven';
-----------------------------------------
聚合函数
avg() sum() max() min() count()
select avg(salary)
from employees ;
查询 每个部门中的 员工平局工资
select count(distinct department_id)
from employees;
分组 group by 分组条件
select department_id,avg(salary)
from employees
group by department_id;
查询每个部门中的最大工资
select department_id,max(salary)
from employees
group by department_id;
group by 注意:1 只有出现在group by 语句后的列名字才可
以出现在 select语句块中
2 如果有一列没有出现在group by
只有书写聚合函数中才可书写这样的列
------------------------------------------
having 条件查询
查询 20 ,30 平局工资
select department_id ,avg(salary)
from employees
where department_id in (20,30)
group by department_id; 20 30
select department_id,avg(salary)
from employees
group by department_id (11)
having department_id in (20,30);
having where 区别
1 having where 执行相同sql时 执行效率不同
where执行效率高
原因: sql语句执行顺序不同
1 sql语句 书写顺序
select from where group by having order by
2 sql语句执行顺序
from --> where--->group by---> having---> order by
having 真正的意图
查询 平局工资 大于 3000
这些部门的平局工资 和 部门编号
select department_id,avg(salary)
from employees
group by department_id
having avg(salary) > 6000;
having 查询中应用了聚合函数的条件判断
那么使用having
sql 执行顺序
from ---> where ---> group by ---having---order by
结论:
where 应用的条件判断 是没有聚合函数使用的
having 应用的条件判断 是有聚合函数使用的
--------------------------------------
分页查询 limit 限制
select * 起始条数 共显示几条
from employees limit 0, 5 第一页
5, 5 第二页
10, 5
-------------------------------------
case when 语句
case
when xxx then xxx
when xxx then xxx
注意 中间没有逗号,
查询员工的信息,当员工的工资 大于 10000 时 显示 high
小于 10000 low
select first_name,salary,case
when salary >= 10000 then 'high'
when salary < 10000 then 'low'
end
from employees;
case
when salary >1000 then 'high'
when salary <1000 then 'low'
end;
select name,case
when sal>2000 then 'high'
when sal<=2000 then 'low'
end
from account;
------------------------------------
回顾
1
sql select
from
where
group by
having
order by
1 语法关键字 书写顺序 含义
2 执行顺序
2 查询关键字
3 函数
数字
字符
日期
ifnull
聚合函数
4 case when
---------------------------------------
子查询
概念 : 在一条sql语句中 嵌套另外的sql 查询语句
where 子查询
a)单值where子查询
查询 工资 大于 平局工资的 这些员工的信息
select avg(salary)
from employees;
select *
from employees
where salary > (select avg(salary)
from employees
);
查询 工资 大于 最大工资
这些员工的信息
select max(salary)
from employees
select *
from employees
where salay > (select max(salary)
from employees);
查询 工资 大于 first_name Lex
这些员工的信息
select salary
from employees
where first_name = 'Lex';
select *
from employees
where salary > (select salary
from employees
where first_name = 'Lex');
b) 多值where子查询
查询 工资 为 最高工资 ,
工资为最低工资
这些员工的信息
select *
from employees
where salary in ((select max(salary)
from employees),
(select min(salary)
from employees ));
from 子查询
select *
from employees;
select last_name,first_name,salary
from employees ;
select temp.last_name
from (select last_name,first_name,salary
from employees) as temp
where temp.first_name = 'Steven';
from 子查询 进行预处理
----------------------------------------
多表联合查询
创建数据库 hr_m
分门别类的存储在多张表中
问题
1 查询 雇员的信息 同时 显示
雇员所在部门的名字 和 位置
4种多表联合操作
1 内连接
select e.ename,d.dname,d.loc
from emp as e,dept as d
where e.deptno = d.deptno ;
SQL 99
inner join
select e.ename,d.dname,d.loc
from emp as e
inner join dept as d
on e.deptno = d.deptno ;
2 左外连接
左外连接关键字
左边这张表的数据全部查询
查询所有雇员信息,部门名,位置
select e.ename,d.dname,d.loc
from emp as e
left outer join dept as d
on e.deptno = d.deptno ;
3 右外连接
查询雇员信息 ,以及所有的部门信息
select e.ename,d.dname,d.loc
from emp as e
right outer join dept as d
on e.deptno = d.deptno ;
select e.ename,d.dname,d.loc
from dept as d
left outer join emp as e
on d.deptno = e.deptno ;
4 全连接 Mysql 不支持
概念 1 连接所有能够连接的数据
2 并且把2张表中不能连接的数据查询出来
select e.ename,d.dname,d.loc
from emp as e
full join dept as d
on e.deptno = d.deptno;
--------------------------------------
查询 每个部门中 工资最高的员工信息
select deptno, max(sal)
from emp
group by deptno ;
select *
from emp
where sal in (select max(sal)
from emp
group by deptno);
deptno max
10 5000
20 3000
30 2850
ename salary deptno
SMITH 300 20 20 3000
SUNS 3000 20 20 3000
SUNS1 100 10 10 5000
SUNS2 5000 10 10 5000
SUNS3 500 30 30 2850
SUNS4 2850 30 30 2850
select e.*
from emp as e
inner join (select deptno, max(sal) as maxSal
from emp
group by deptno) as temp
on e.deptno = temp.deptno
where e.sal = temp.maxSal;