一、SQL命令(结构化查询语言 — 数据库简单语法 )
1.简单查询 (英文标点)
查询所有 * 通配符
select * from employees;
查询关键字 -- * 通配符代表所有(列) -- 数据来源 关键字 -- 表名
注意: * 不如 所有的列名 ----- 执行过程中 将 * 转化成 所有的列名 开发不建议 *
指定列查询
select 列名,列明 from 表明
*简单运算 /±
数据列数字类型
select employee_id*10 from employees;
别名 关键字 as 可以省略
select name as cc from user_tb
select 列明 as 别名 (不准中文) from 表名
select 列明 别名 from 表名
合并列 || 对于展示结果的合并
select first_name || last_name as name from employees;
distinct 去重
--部门编号 department_id
-查询所有的部门编号
select distinct department_id from employees;
2.有条件的查询数据
- 关键字 where
select 结果(列) from 表 where 条件
2.1 比较运算 > < >= <= = !=
select * from employees
where salary != 17000;
2.2 逻辑运算 and or not
-- 工资 17000 和 24000
select * from employees where salary = 17000 or salary = 24000;
select * from employees where not salary = 24000;
2.3 谓词
1. 枚举 列举所有的内容 in 、 not in
-- in 查询工资 24000 17000 12000
select * from employees where salary in(24000,17000,12000);
2.区间 (闭) 包含 相当于 >= <=
between ... and ... / not between ... and 取反
select * from employees where salary not between 8000 and 12000;
3. like 像 模糊查询 字符类型 需要使用 '字符'
--like % 代表多位 不确定 _ 代表一位
select * from employees
where first_name like '_a___';
4. 空
--空 is null / is not null 0 是数据 空 null 没有
select * from employees
where department_id is not null;
3.有序(排序) 关键字 order by
注意: 对于查询结果进行排序 (指定排序规则 使用哪一列的数据进行排序)
--排序 默认升序 asc 手动降序 desc;
select * from employees
where salary >5000
order by salary desc;
select * from employees
order by hire_date desc,salary desc; 写在前的列会优先进行排序。
4.函数
4.1内置函数
1.系统当前时间 sysdate
--函数 数据来源于表 内置数据
select sysdate from employees;
--虚表 哑表 满足SQL语句的语法规则 dual 一行一列
select sysdate from dual;
-- 年月日 时分秒 星期
select * from dual; 一行一列
2.to_char(日期,'日期格式') 输出的日期进行格式规范 应用在数据的展示
-- yyyy-mm-dd HH:mi:ss day 星期 仅限数据库
select to_char(sysdate,'yyyy-mm-dd HH:mi:ss day') as shijian from dual;
3.to_date(字符串,格式) 将字符串 转换成 日期格式 数据的存储
注意: 格式 是字符串中的格式
-- 2000-01-01 yyyy-mm-dd 将字符--->时间格式
-- 展示结果是数据库中的格式 跟你的转换格式没有任何关系
select to_date('2000-01-01','yyyy-mm-dd') as shijian from dual;
4.2 组函数
组: 针对一组数据(列中的多个数据)的操作。
min(组数据)
max()
sum()
avg()
count()
length()
lengthb()
select * from employees;
select min(salary) from employees;
select max(salary) from employees;
select avg(salary) from employees;
select sum(salary) from employees;
--有效数据条数 不包含 空 null 主键列
select count(*) from employees;
4.3 case 常见场景 对右多个结果的结果集进行统计
case具有两种格式。简单case函数和case搜索函数。
--简单case函数
case sex
when '1' then '男'
when '2' then '女’
else '其他' end
--case搜索函数
case when sex = '1' then '男'
when sex = '2' then '女'
else '其他' end
实际例子
一张表数据如下
1900-1-1 胜
1900-1-1 胜
1900-1-1 负
1900-1-2 胜
1900-1-2 胜
写出一条SQL语句,使检索结果如下:
胜 负
1900-1-1 2 1
1900-1-2 2 0
我随手建了这样一个表:
create table test(Date varchar(50) null, Result varchar(50) null)
并将上面的数据都插入到表中。
经过一番尝试和修改,终于得到了答案:
select distinct Date,
sum(case Result when '胜' then 1 else 0 end) as '胜',
sum(case Result when '负' then 1 else 0 end) as '负'
from test
group by date
这个语句的意思是 对于胜利的 表示为 1 然后sum 得到结果集 然后别名为 胜
5.分组 group by + 分组条件(列,函数)
--查询每个部门的最大工资 ---按照部门对数据进行分组
select department_id,max(salary) from employees
group by department_id;
--查询1997年 每个月入职人数
-- where 条件 1997 年 where to_char(hire_date,'yyyy') = 1997
-- 入职人数 count(*) group by 按月分组 group by to_char(hire_date,'mm')
-- 按照月份排序 order by order by to_char(hire_date,'mm');
select to_char(hire_date,'mm')as yuefen,count(*) as renshu from employees
where to_char(hire_date,'yyyy') = 1997
group by to_char(hire_date,'mm')
order by to_char(hire_date,'mm');
注意:
1.只有出现在 group by 之后的列才可以出现在 select 关键字之后 ---- 错误:不是group by 表达式
不允许一行一列中的数据出现多种选择
2.允许在select之后使用组函数(单值结果,不存在多个数据冲突)
3.如果gruop by之后使用了内置函数 也是可以书写在select关键字之后的。
6.having 分组之后的条件再判断
平均工资大于5000的部门
select department_id,avg(salary) from employees
group by department_id
having avg(salary)>5000;
注意: 如果判断依据中出现组函数应用,需要使用having其他情况用where。
--查询 1997 年 每个月入职员工数大于 2 人的 月份以及人数按照人数降序排序。
1. 查询1997年
2. 按照月份分组
3. count(*) >2
4. 降序排序
select count(*),to_char(hire_date,'mm')
from employees
where to_char(hire_date,'yyyy')=1997
group by to_char(hire_date,'mm')
having count(*) >2
order by count(*) desc;
关键字顺序 — 单表数据查询操作
select 列名 from 表名(表连接) where 条件 group by 分组 having 组函数判断 order by 排序
子查询
一条SQL语句中去嵌套另外一条SQL语句 (方便书写)
where子查询
-- where 单值子查询 一条SQL语句的结果是另一条SQL语句的执行条件
where = (SQL语句)
select department_name from departments
where department_id = (select department_id from employees
where employee_id = 120);
-- 查询员工名称为Steven 的员工的部门名称
-- where 的多值 子查询
where in (SQL语句)
select department_name from departments
where department_id in (select department_id from employees
where first_name = 'Steven');
from子查询 对数据进行预处理操作
-- SQL语句的执行结果本身也是一张表
-- from 子查询 另一条SQL语句的执行结果 充当一张表的存在
-- select * from (SQL语句)
select first_name || last_name as name from employees;
--StevenKing
select name
from (select first_name || last_name as name from employees)
where name = 'StevenKing';
三、表连接
主键列 : 唯一标识一行数据。 (只有一列)
1.唯一:数据不能重复
2.非空
3.数据类型:无任何要求
外键列 : 表示多张表之间的关联关系
表连接关系 表与表之间的相同的数据的列
表连接: 数据展示有可能涉及多张表的数据级联展示
内连接
1 . inner join 表名 on 连接条件(外键关系)
将两张表中有关联的数据进行连接展示
展示两张表都符合条件的数据
select e.*,d.department_name from employees e
inner join departments d
on e.department_id = d.department_id;
外连接 (outer 可以省略)
外连接右左外连和右外连
左右是相对的概念(没有本质的区别)
1.左外连接 left (outer) join — 107 行
-- 左外连接 将左边表的数据全部展示,右表中的数据可以连接的进行连接展示。
select e.employee_id,e.first_name,e.department_id,d.department_id,d.department_name
from employees e
left join departments d
on e.department_id = d.department_id;
2.右外连接 right(outer) join — 122 行
-- 右外连接 将右边表的数据全部展示,左表中的数据可以连接的进行连接展示。
select e.employee_id,e.first_name,e.department_id,d.department_id,d.department_name
from employees e
right join departments d
on e.department_id = d.department_id;
3.全外连接 (了解) full(outer) join — 123 行
-- 全外连接 所有数据
select e.employee_id,e.first_name,e.department_id,d.department_id,d.department_name
from employees e
full join departments d
on e.department_id = d.department_id;
SQL语言体系
1.DQL 数据查询语言
单表和多表的数据查询
2.DDL 数据定义语言
表 、 定义 修改 删除
2.1 创建表
create table 表名(
列名 数据类型 【约束】,
列名 数据类型 【约束】,
列名 数据类型 【约束】
)
2.2 数据类型
略
2.3 约束
1. 主键约束(PK) primary key 非空,唯一 要求 : 必须加
2. 非空 not null
3. 唯一 unique
4. 检查约束 check (用的不多)
create table t_user3(
id number(12) primary key,
name varchar2(32) not null unique,
sex varchar2(5) check(sex in('男','女')),
iphone varchar2(11) check(length(iphone) =11),
email varchar2(32) check(email like '%@%')
);
5. 外键约束(FK) references 表(列)
注意:
创建外键约束 必须先创建 父表 再 创建 子表
-- 父表 被引用的表
create table clazz(
id integer primary key,
clazz_name varchar2(10)
)
-- 子表 引用另一张表列的表
create table student(
id integer primary key,
name varchar2(32),
clazz_id integer references clazz(id)
)
表删除 drop
drop table 表名;
注意: 有外键约束的情况下需要先删子表 再删父表。
drop table clazz cascade constraints;
级联删除 强制删除父表以及外键约束(不建议随便使用)
表修改 alter
略 大概就是删除列添加一个列那样
注意 一个表的数据列 绝对不能删 但是可以叫 切记
3.DML 数据操作语言
表中数据的增删改 — 确认操作 commit 提交 也是一条SQL语句
3.1 添加 完整的一行数据
insert into 表名(列名1,列名2....) values (数据值1,数据值2....);
insert into t_user values(8,'HUXZ','F',18);
commit;
所插入的数据的个数和表中列的个数相同,且数据位置一一对应。
3.2 删除 完整的一行数据
delete from 表名 where 删除的数据条件;
commit;
条件决定了删除的数据。
delete from 表名;
commit;
删除所有的数据!!! 一条一条反复执行!
truncate table 表名; 高效的删除
1.不需要commit;
2.删除所有数据 --- 删除表,重新创建表。
3.3 修改
update 表名 set 列 = 值,列=值 .... where 条件
commit;
主键可以修改 但是不建议修改!
update t_user set age='',sex='M' where id = 12;
commit
;
4.TCL 事务控制语言
commit; rollback;
事务 :保证数据库业务操作完整性的一种机制。
1.commit:回滚段当中的数据进行提交确认。
2.rollback 将回滚段中的数据进行回滚清空。
控制一组SQL语句要么一起成功要么一起失败,保证业务操作的完整性!
6.数据库中的对象
序列
用于创建唯一数字自增标识的数据库对象。
创建:
create sequence 名称 start with 1
创建序列 从 1开始
使用:
序列名.nextval
insert into t_user values(user_seq.nextval,'20','F',2);
删除:
drop sequence 名称;
索引 index
概念:数据库结构,帮助使用者快速查询数据库数据(提升查询效率)
切记索引对于检索数据的加快的同时对于添加和修改也增加了负载
一个列存在的索引必然符合一定的条件,那么这个列在添加和修改的时候
也必然需要对这个索引进行维护,必然增加了数据库的负担.
一个索引的合理设计必然要符合增删改少而查询多的字段上
create index 名称 on 表名(列)
create index my_index on t_user(name);
使用存在索引列作为条件的时候 会自动提升查询效率。主键自动存在索引!
drop index 名称;