闲谈
为了理想，肝一波java web！
文中所需的sql数据库我已经上传到附件了！
前置知识
SQL语句分类
DQL-->查询语句，凡是select语句都是DQL(数据查询语言)
完整语法格式(右边数字代表执行顺序)
select * 5
from * 1
where * 2
group by * 3
having * 4
order by *； 6
DML-->insert delete update，对表中的数据进行增删改(数据操作语言)
DDL-->create drop alter，对表中的数据进行增删改(数据定义语言)
TCL-->commit提交事务，rollback回滚事务
DCL-->grant授权、revoke撤销权限等。
导入数据-->source sql文件路径
desc table_name; 查表结构
select database(); 查看当前使用的数据库
select version(); 查看当前数据库版本
show create table table_name; 查看创建表的语句
查询语句
简单的查询语句
select 字段名1,字段名2,字段名3,...... from table_name;
所有的sql语句都已“;”结尾；sql语句不区分大小写。
select ENAME,SAL * 12 from emp; sql语句可以参与到数学运算
select ENAME,SAL * 12 as yeadsal from emp; sql语句给查询出来的列重命名使用as关键字
条件查询
and&or
and代表且，or代表或；and优先级高于or，想要or先执行可用小括号
语法格式
select 字段名1，字段名2， from table_name where 条件语句；
案例一 ：查询工资等于5000的员工姓名？
-->select ENAME from emp where sal = 5000；
案例二：查询SMITH的工资？
--> select ENAME,SAL from emp where ENAME='SMITH'; 字符串要加单引号
案例三：查询工资不等于3000的
-->select ENAME,SAL from emp where SAL <> 3000; mysql中<>代表不等于的意思
between...and...
案例四：查询工资在1000到3300之间的员工
1.select ename,sal from emp where sal between 1000 and 3300; 使用between语法
2.select ename,sal from emp where sal>=1000 and sal<=3300; 常规语法
is null
案例五：找出津贴为null的员工
select ename,sal,comm from emp where comm is null;
not
案例六：找出津贴不为null的员工
select ename,sal,comm from emp where comm is not null;
in等同于or
案例七：找出工作岗位是MANAGER和SALESMAN的员工
select ename,job,comm from emp where job = 'MANAGER' or job = 'SALESMAN'; 使用of
select ename,job,comm from emp where job in ('MANAGER','SALESMAN'); 使用in
模糊查询like
在模糊查询当中，有两个特殊符号，%代表任意多个字符，_代表任意一个字符
案例八-1：找出名字中带有B的员工
select ename,sal from emp where ename like '%B%'; like之后的字符要加引号，使用%匹配多个字符
案例八-2：找出名字中第二个字母为A的员工
select ename,sal from emp where ename like '%B%'; 使用_匹配一个字符
排序
order by
-->order 排序 by 通过；所以order by就是通过什么排序
-->order by 排序默认是升序的，指定降序为desx，升序为asc；
案例九-1：将员工薪资以升序排列打印出员工的姓名、薪资信息；
select ename,sal from emp order by sal;
案例九-2：将员工薪资以降序排列当薪资一样时以姓名首字母大小写升序排列；
select ename,sal from emp order by sal desc,ename asc; 在执行此条语句时，当第一条排序相等时才会执行第二条排序
案列九-3：将职位为'SALESMAN'的员工以薪资降序排列
select ename,job,sal from emp where job='SALESMAN' order by sal desc;
分组函数
--> 分组函数运算自动忽略null
-->分组函数不能在where之后运算
-->分组函数通常都会和group by联合使用，分组函数在group by执行之后执行
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值
案列十-1：找出工资高于平均工资的员工
-->select ename,sal from emp where sal > (select avg(sal) from emp); 没有使用group by时使用分组函数会自成一组，并且分组函数不能单独在where之后执行
group by
案例十-2：将员工信息按工种分类显示员工的工种、最高薪资？
select job,max(sal) from emp group by job;
案列十-3:找出每个部门每个工种的最高薪资？
select deptno,job,max(sal) from emp group by deptno,job;
having
-->用作条件筛选，当筛选不能使用where语句进行时，使用having进行筛选
案列十-4 找出每个部门平均薪资，并显示平均薪资大于2000的部门
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
单行处理函数
ifnull-->ifnull(可能出现null的函数，处理之后的数据)
distinct 查询去重
连接查询
根据表的连接方式划分，包括:
内连接：
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全连接
笛卡尔积现象：当两张表进行连接查询时，没有任何条件限制，最终查询结果条数为两张表记录条数的乘积
关于别名
select e.ename,d.dname from emp e,dept d; emp e代表之后e就是emp，e.ename就是emp下的ename字段，d.dname同理
等值连接(inner)
案例十一-1：找出每一个员工的部门名称，要求显示员工名称和部门名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno; 这里是sql92语法！加条件之后，他依旧进行了笛卡尔积次查询，知识显示出来的是我们进行限制的
join...on...where
案例十一-1：找出每一个员工的部门名称，要求显示员工名称和部门名
select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno; sql99语法
语法格式
select *
from *
join * 这里应该有个inner，内连接可以省略
on 连接条件
where ...
非等值连接(inner)
-->最大特点：连接条件中的关系是非等值连接
案列十一-2：找出每个员工的工资等级，要求显示员工名、工资、工资等级。
select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
自连接
-->一张表看作两张表。自己连接自己
案例十一-3：找出每个员工的上级领导，要求显示员工名和对应的领导名
select a.ename,b.ename from emp a join emp b on a.mgr=b.empno;
在这张表里，员工的上级领导编号就等于领导的编号！
外连接
语法格式
select *
from *
left/right join *
on *
where *
假设A表和B表进行连接，使用外连接时，AB两张表中有一张表时主表，一张表时副表，主要查询主表中的数据，捎带着查询副表，当副表中的数据没有和主表中的数据匹配上，副表自动模拟出null与之匹配
案列十二-1：找出每个员工的领导，每个员工必须查出来
select e.ename,b.ename from emp e left join emp b on e.mgr=b.empno;
带left左边是主表，带right右边是主表
案列十二-2：找出哪个部门没有员工？
select d.dname,e.deptno from emp e right join dept d on d.deptno=e.deptno where e.deptno is null;
多表连接查询
案例十三-1：找出每一个员工的部门名称以及工资等级。
第一种写法sql92方式 ： select e.ename,d.dname,s.grade from emp e,dept d,salgrade s where e.deptno=d.deptno and (e.sal between s.losal and s.hisal);
第二种写法sql99方式：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 order by grade;
案例十三-2：找出每一个员工的部门名称、工资等级、以及上级领导。
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
left join emp b
on e.mgr=b.ename;
子查询
子查询语法
selct
..(select).
from
..(select).
where
..(select).
from后接select
案例十四-1：找出每个部门平均薪水的薪资等级
select deptno,avg(sal) as avgsal from emp group by deptno; 每个部门的平均薪水
select deptno,t.avgsal,s.grade
from (select deptno,avg(sal) as avgsal from emp group by deptno) as t
join salgrade s
on t.avgsal between s.losal and s.hisal;
联合查询
union
案例十五-1：找出工作岗位是SALESMAN和MANAGER的员工?
select ename,job from emp where job='SALESMAN' union select ename,job from emp where job='MANAGER';
limit分页查询
limit是mysql'特有的，其他数据库中没有的
limit取结果集中的部分数据，这是他的作用
语法
limit startindex，length；
startindex代表起始位置，从0开始，0表示第一条记录
length代表取几个
案例十六-1：取出工资前5名
select ename,sal from emp order by sal desc limit 0,5;
增删改查(GRUD)
建表语法格式
create table table_name(
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型，
....
)
mysql中常见的数据类型
-->int、float、double、char、varchar、date、BLOB、CLOB等等
-->其中BLOB主要存储二进制大对象(图片、视频等流媒体信息)
-->CLOB存储字符大对象(4GB字符串)
案例十七-1：创建表要求如下
创建学生表：
学生信息包括：
学号、姓名、性别、班级编号、生日
学号(bigint)
姓名(varchar)
性别(char)
班级编号(int)
生日(cahr)
create table t_stu(
nu bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
insert into表中插入数据
-->insert语句
-->语法格式： insert into tables_name(字段名1，字段名2，字段名3，....) values(值1，值2，值3.....)
-->字段的数量和值的数量相同，并且数据类型要对应
-->当insert 语句执行成功之后，表格当中必然会多一条记录
案列：insert into t_stu(nu,name,sex,classno,birth) values('1','fge7s','1','3','2021-10-7');
需要注意的是，这里所有的默认字段为null，即insert into t_stu(name) values('ww');
改变默认值的关键字为default，下面案例图中可以看到，不设置values时，默认sex为1；
案例：
create table t_stu2(
nu bigint,
name varchar(255),
sex char(1) default 1,
classno varchar(255),
birth char(10)
);
drop删除表
drop table if exists table_name;
as复制表
-->语法 create table table_name as select语句
-->就是将查询结果当做表复制出来
-->语法：insert into table_name select语句
-->将查询结果插入到表中
update修改表中数据
-->关键字 update
-->语法：update table_name set 字段名=值...where....
-->没有条件整张表数据全部更新
delete删除表中数据
-->语法格式：delete from table_name where 条件；
-->没有条件全部删除
注意delete删除之后是可以回复的，使用truncate table table_name删除无法恢复
表设计
约束（constraint）
-->约束：是在创建表的时候给表中的字段添加约束，添加约束时为了保证表中数据的合法性、有效性、完整性
-->常见约束有：非空约束(not null) 唯一约束(unique) 主键约束(promary key) 外键约束(foreign key)
非空约束(not null) :约束的字段不能为null
唯一约束(unique) ：约束的字段不能重复
主键约束(promary key) ：约束的字段既不能重复也不能为null
外键约束(foreign key)
非空约束案例(nut null)
create table t_stu(
id int,
username varchar(255) not null,
password varchar(255) not null);
唯一性约束案例(unique)
create table t_stu(
id int unique,
username varchar(255) unique,
password varchar(255))
主键约束案例(primary key)
主键值不能为空和null，并且主键约束使用auto_increment之后可以自增长
一张表的主键约束只能有一个
create table t_stu(
id int primary key,
username varchar(255),
password varchar(255))
事务
一个事务是一个完整的业务逻辑单元，不可再分
和事务相关的DML语句只有insert update delete
事务的存在保证数据的完整性安全性
通常一个事务需要多条DML语句完成
练习题
题1：取得每个部门最高薪水的人员名称
select deptno,max(sal) as maxal from emp group by deptno;
+--------+---------+
| deptno | maxal |
+--------+---------+
| 10 | 5000.00 |
| 20 | 3000.00 |
| 30 | 2850.00 |
+--------+---------+
select ename,sal,e.deptno from ( select deptno,max(sal) as maxsal from emp group by deptno) as t,emp as e where t.deptno=e.deptno and t.maxsal=e.sal;
题2：哪些人的薪水在部门的平均薪水之上
select deptno,avg(sal) as avgsal from emp group by deptno;
select e.ename,e.sal,t.* from (select deptno,avg(sal) as avgsal from emp group by deptno) as t join emp e on e.deptno=t.deptno and e.sal>t.avgsal;
题3：取得部门中所有人的而平均薪水等级
select e.deptno,avg(s.grade) from emp e join salgrade s on e.sal between s.losal and s.hisal group by e.deptno;
[复制链接]
发表于 2021-10-9 11:06
