今天进入开始学习sql语句,本人用的是mysql数据库
-- 单行注释
/* 多行注释
*/
/*
1.明白干的活:根据部门名称 dname 查询该部门下员工的所有信息(8个字段)
2.分析字段所处的表:
dname---dept
8个字段--emp
3.多表查询:
a. select e.*,d.dname
from emp e,dept d --> 笛卡尔积现象。
where e.deptno=d.deptno and dname='SALES'
b.[内连接多表查询]
select e.*,d.dname
from emp e
inner join dept d on e.deptno=d.deptno
where dname='SALES'
c.[外连接多表查询]
内连接的两张表,没有主次之分,所有数据根据关联字段进行等值显示。
外连接的两张表,有主次之分,主表里的数据,一定会都显示出来,
次表里的数据,只显示值相当的。
外连接 分为 左外连接和 右外连接:
在查询语句里,出现的第一个表 为左表,第二个表为右表。
如果使用左外连接进行查询,左表就是主表,
那么左表里的数据就会全部显示出来。
右表只显示值相当的数据。
D. ----关联表中,关联列的名相同:
select e.*,d.dname
from emp e
inner join dept d using (deptno) -- on e.deptno=d.deptno
where dname='SALES'
E.--- 关联表中,关联列的名相同,类型相同,,,
select e.*,d.dname
from emp e
natural join dept d -- inner join dept on e.deptno=d.deptno
where dname='SALES' -- 自然连接
*/
select * from emp where empno=7369
-- 单行函数
-- 多行函数(求总和sum(sal),求平均数 ,求最大值,求最小值,求总个数) ,聚合函数,分组函数
select count(empno) from emp where ename like '%M%' and hiredate>='1981-1-01' and hiredate <= '1981-12-31'
/* 1.数据库的类型:
网状数据库、层次数据库、关系型数据库和非关系型数据库四种
==目前市场上比较主流的是:关系型数据库 和 非关系型数据库
==使用结构化查询语言(SQL)对关系型数据库进行操作
2.关系型数据库:
数据以二维表的形式进行存储,表和表之间可以建立关联关系的。
(1vs1, 1VSN ,nvsn 通过主外键关系建立的;
添加,删除,修改 都是有约束的---)
3.mysql
mysql是一种开放源码的,轻量级的关系型数据库。
优点:体积小,成本低,速度快,开放源码等优点。
4.一款操作 关系型数据库的语言——SQL(结构化查询语言)
SQL=DQL+DML(ok)+TCL+DCL(不讲)+DDL(表,约束)
sql 对数据库里的数据做管理,
sql=DQL(数据的查询语言-查询)
+DML(数据的管理语言-增删改)
+TCL(事务的控制语言 重点)---重点学习java的TCL
+DCL(数据的控制语言-授予,撤回)
+DDL(数据的定义语言-创建,删除,修改一切数据库对象)
5.DDL(数据的定义语言)
5.1 创建一张表(了解)
create table t_student(
sno int(6) primary key auto_increment,
sname varchar(5) not null,
sex char(1) default '男' check(sex='男' || sex='女'),
age int(3) check(age>=18 and age<=50),
enterdate date,
classname varchar(10),
email varchar(15) unique,
);
5.2约束--限制字段的:
【以保证数据库中数据的准确性和一致性,这种机制就是完整性约束】
1.主键约束: 非空+唯一
2.非空约束:
3.唯一约束:
4.检查约束:
5.默认值约束:
6.自增约束:
7.外键约束:
-- 先创建父表:班级表:
create table t_class(
cno int(4) primary key auto_increment,
cname varchar(10) not null,
room char(4)
)
-- 建表后,添加外键约束
alter table t_student add constraint fk_student_cno FOREIGN key(cno) REFERENCES t_class (cno);
-- 建立外键约束后,班级表只能删除 没有学生的班级。
delete from t_class where cno=2
==根据约束的位置,可以分为 表级约束 和 行级约束。
行级约束:直接写在 字段名 后面的约束。
表级约束:脱离建表语句; 写在建表语句的最下方。
-- 删除约束:
alter table t_student drop constraint fk_student_cno
6.1 DML(数据的操作语言):
-- 给表里的所有字段赋值,表名后面就可以不写字段名。
insert into t_student values(null,'张三',default,20,'2023-12-12','定制14班','asd@qq.com');
-- SYSDATE() now()
insert into t_student values(null,'李四','女',22,SYSDATE(),'dz14b','bbb@qq.com');
---一次性添加多条数据
insert into t_student values (null,'张三','男',21,'2023-9-1','java01班','zs3@126.com',1), (null,'张三','男',21,'2023-9-1','java01班','zs4@126.com',3),(),(),()...
6.2 删除语句:
delete from 表名 where 主键=值;
6.3 修改语句:
update 表 set 列名1=新值,列名2=新值,列名3=新值 where 主键=值;
**【面试题】
delete truncate drop 三者之间的区别
drop 全部删除掉:表结构+约束+数据 都删掉
drop DDL语句。
delete 只删除数据(一行一行删除数据)---效率低
truncate 删除数据(删除整张表,重新创建表)---效率高
delete 属于 DML语句;truncate 属于 DDL语句
delete 继续延续下标;truncate 下标从1重新开始
delete 属于 DML语句 可以拥有事务,可以回滚;
truncate 属于 DDL语句 自动提交事务,没办法回滚。
*/
7. DQL数据的查询语言:
查询语句的结构:
select 字段名,字段名,字段名,字段名 (如果是表里所有的字段,可以换成*)
from 表名1, 表名2, 表名3,
where (分组前 数据的筛选条件)字段=值 and/or &&||字段=值
group by 字段名
having 分组后 数据的筛选条件 字段=值 and/or &&||字段=值
order by 字段名,字段名
介绍select 部分:
a. 给字段设置别名
select empno,ename,job,deptno depno from emp;
select concat( empno,'_' , ename) info from emp;
select concat( empno,'_' , ename) as 员工基本信息,hiredate '入职时间' from emp;
b.简单的运算:
-- 显示 所有员工的年薪:
select ename,sal*12 + ifnull(comm,0) 年薪 from emp;
select DISTINCT deptno from emp;
-- 显示 经理的编号:
select DISTINCT mgr from emp order by mgr desc;
介绍where 部分:
等值查询:
-- 例子: 查询部门编号为30,同时mgr编号 为 7361 或者 7788 的员工信息。
select * from emp where deptno=30 and (mgr=7361 or mgr=7788)
select * from emp where deptno=30 and mgr in (7361,7788)
范围查询:
select * from emp where deptno=30 and mgr >=7361 and mgr<=7788;
select * from emp where deptno=30 and mgr between 7361 and 7788;
-- 扩充例子2 多表查询 输出员工姓名,工资,工资等级
select * from emp;
select * from salgrade;
select ename,sal,grade from emp,salgrade
where emp.sal>= salgrade.LOSAL and emp.sal<=salgrade.HISAL;
select ename,sal,grade,LOSAL,HISAL from emp inner join salgrade
on emp.sal>= salgrade.LOSAL and emp.sal<=salgrade.HISAL;
模糊查询: like+% ;_ -->替换 =
% 代表 0位或者多位
_ 代表 1位
-- 例子:查询员工名字里,或者部门名字里带 “发”的 所有员工信息。
select * from emp inner join dept on emp.deptno=dept.deptno
where dname like '%发%' or ename like '%发%';
空值判断:
-- 查询 没有奖金的员工信息:
select * from emp where comm = 0 or comm is null;
select * from emp where comm is not null;
介绍 from 部分:
后面跟表名 别名
inner join on
介绍sql里的函数:
单行函数: n条数据,得到n个结果
1.字符串函数
2.数值函数
3.日期与时间函数
4.流程函数
5.JSON函数
6.其他函数
多行函数/分组函数/聚合函数: 多条数据 ,只得到一个结果
count() :求总个数
sum():求总和
avg():求平均
max(): 求最大值
min(): 求最小值
-- 例子求员工的总个数使用列的别名;工资总和,最大工资,最小工资,平均工资。
select count(empno) 员工总个数,sum(sal) 工资总和,max(sal) 最大工资,min(sal) 最小工资,avg(sal) 平均工资
from emp;
-- 查询 1981年最晚入职的最晚入职的时间。
select max(hiredate)
from emp
where hiredate BETWEEN '1981-01-01' and '1981-12-31'
select DATEDIFF('2024-08-07','2003-1-1')
-- 显示 所有员工的转正日期:
介绍 group by 分组查询
-- 显示 每个部门 最晚的入职时间:
select max(hiredate) from emp group by deptno
-- 查询 每个部门的 最晚入职时间, 最晚入职时间不可晚于 2023-1-1
select max(hiredate)
from emp
group by deptno
having max(hiredate) <'2023-01-01';
-- 1.显示各部门的平均工资,按照平均工资倒叙排列。
select avg(sal) avgsal,deptno
from emp
group by deptno
order by avgsal desc;
-- 2.刨除低于2000的员工,统计各部门的平均工资, 只显示低于3200的平均工资,对显示的数据做正序排列。
select avg(sal) avgsal,deptno
from emp
where sal>=2000
group by deptno
having avgsal<=3200
order by avgsal asc;
-- 多表查询:查询员工编号,姓名,工资,工资等级
-- 1.
select empno,ename,sal,grade
from emp,salgrade
where emp.SAL>=salgrade.LOSAL and emp.sal<= salgrade.HISAL;
-- 2. inner join (推荐使用)
select empno,ename,sal,grade
from emp inner join salgrade
on emp.SAL>=salgrade.LOSAL and emp.sal<= salgrade.HISAL;
-- 3. 查询员工编号,员工姓名,部门编号,部门名称,部门所在地;
select empno,ename,dept.deptno,dname,loc
from emp inner join dept on emp.deptno=dept.deptno;
-- 前提条件:两个表的关联列是同名的。
select empno,ename,dept.deptno,dname,loc
from emp inner join dept using(deptno)
-- 4. 前提条件:两个表的关联列是同名的;类型,长度 都相同
select empno,ename,dept.deptno,dname,loc
from emp natural join dept
-- 内连接查询1-4
两张表之间 没有主次之分,数据相等一起显示;数据不等都不显示。
-- 外连接查询5:
a。两张表之间 有主次之分,主表的所有都会进行显示;
子表的数据 相等的显示,不等的不显示;
b.查询语句中,第一出现的表 叫 左表,左外连接查询的时候,左表就是主表的;
select empno,ename,dept.deptno,dname,loc
from emp left join dept on emp.deptno=dept.deptno;
-- emp 是左表;dept 是右表
-- 子查询:
-- 例子1: 查询跟SMITH123 是同一个部门的所有员工信息。
select deptno from emp where ename='SMITH123'
select * from emp where deptno=20;
-- 不相关子查询
select *
from emp
where deptno=(select deptno from emp where ename='SMITH123')
-- 查询 跟最低工资 在同一个部门下的员工信息。
select *
from emp
where deptno=(select deptno from emp where sal=(select min(sal) from emp))
-- 查询 跟 张发 处于同一个工资等级的 所有员工信息
select *
from emp inner join salgrade s on emp.sal >=s.LOSAL and emp.sal<=s.HISAL
where s.grade =
(select grade
from salgrade
inner join emp on emp.sal>= salgrade.LOSAL and emp.SAL<=salgrade.HISAL
where ename='张发')
-- 查询 跟 SCOTT 同一年入职所有员工信息
select * from emp
where YEAR(hiredate) =(select year(hiredate) from emp where ename='SCOTT')
8.数据库对象:
视图:~ 表 select * from emp;
学习视图的原因?
---保证查看者 在权限范围内,查看对相应的字段。
---可以帮忙 简化复杂的查询语句。
创建视图:
-- 例子1
create view v_empnoAndsal
as
select empno,sal from emp;
select * from v_empnoAndsal where sal>2000
-- 例子2: emp,dept,salgrade 三张表一起关联查询
create view v_info
as
select e.*,d.dname,d.loc,s.*
from emp e inner join dept d on e.deptno=d.deptno
inner join salgrade s on e.sal>=s.LOSAL and e.sal<=s.HISAL
select * from v_info where deptno=30
-- 事务:
-- 使用场合: 事务应用在 业务层,在业务层 做功能的整合。
京东下单---控制层调用业务层的付款方法---17个各个dao层里的方法。
-- 作用: 让多个DML语句做为一个整体,这些DML语句 要不同时成功,要不同时失败;
-- 事务的特征ACID: 一致性,隔离性 ,持久性,原子性
-- 事务的并发问题:
(1)读到了别人事务中没提交的数据 脏数据 ,本次读取 叫 脏读。【接受不了】
(2)读到了别人事务提交后的数据(修改操作), 不可重复读。【可以接受】
(3)读到了别事务提交后的数据(添加/删除操作),幻读 【可以接受】
===》针对以上三种事务的并发问题,mysql数据库给我们准备的解决办法是:”改变事务的隔离级别“