因为学习前端所以没有学习的很全面
数据库常用的数据类型:
分类 类型名称 说明
整数类型
tinyInt 很小的整数 (占8位二进制)
smallint 小的整数 (占16位二进制) shor
mediumint 中等大小的整数 (占24位二进制)
int(integer) 普通大小的整数 (占32位二进制) int 小数类型
float 单精度浮点数 , 单精度的浮点型保留的小数位7位。
double 双精度浮点数, 双精度浮点型保留的小数位: 15位
用法一: salary double 最多保留15位小数位
用法二: salary double(5,2) salary总长是5位,其中小数位占2位.
decimal(m,d) 压缩严格的定点数
日期类型
year | YYYY 1901~2155
time | HH:MM:SS -838:59:59~838:59:59
date | YYYY-MM-DD 1000-01-01~9999-12-3 只会保留日期
datetime | YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00~ 9999-12-31 23:59:59 保留日期与时间
timestamp | YYYY-MM-DD HH:MM:SS 19700101 00:00:01 UTC~2038-01-19 03:14:07UTC 时间戳,保留日期与时间。 如果timesamp不插入数据的情况下,默认会使用当前的系统时间作为当前的值。datetime是会使用null值。
文本
CHAR(M) | 固定长度,为0~255之间的整数
VARCHAR(M) | 可变长,为0~65535之间的整数
二进制类型
TINYBLOB | 允许长度0~255字节
BLOB | 允许长度0~65535字节
MEDIUMBLOB 允许⻓度0~167772150字节
LONGBLOB 允许⻓度0~4294967295字节
TINYTEXT 允许⻓度0~255字节
TEXT 允许⻓度0~65535字节
MEDIUMTEXT 允许⻓度0~167772150字节
LONGTEXT 允许⻓度0~4294967295字节
VARBINARY(M) 允许⻓度0~M个字节的变长字节字符串
BINARY(M) 允许⻓度0~M个字节的定长字节字符串
SQL DB DBMS 的关系
DB 数据库(以文件形式存在)
DBMS 数据库管理系统
SQL 数据库语言(高级编程语言)
DQL (数据查询语言):查询语句 凡是select语句都是DML语句(数据的查)
DML (数据操作语言):insert delete updete 对数据增删改
DDL (数据定义语言):creat drop alter 对表当中的结构的增删改
TCL (事务控制语言):commit提交事务,rollback回滚事务
DCL (数据控制语言):grant授权 、revoke撤销权限
mysql 命令
第一步 启动服务 mysql start 或者 mysql -u用户名 -p密码
第二步查看表 show databases;(mysql指令)
第三步 创建自己的数据库 create database bjpowernode;(mysql指令)
第四步 使用bjpowernode数据库 use bjpowernode;
第五步 使用show tables 查看当前实用的数据库中有哪些表;
第六步 使用source 拖拽文件到该处 初始化数据
第七步删除库 drop database bjpowernode;
常用命令
1 查询表中的结构(表中的所有数据) desc 表名称;
2 查看表数据 select * from 表名称;
3 查看当前数据库命令 select database();
4 查看当前mysql版本号 selset version();
5 \c 中止一条语句;
6 退出mysql exit;
7 查看创建表的语句 show create table 表名;
之前的都是数据的准备
*sql语句、
查询语句、
DQL语句
1 查询所有数据 select * form 表名;
2 查询单个字段的数据 select 字段名 from 表名;
3 查询多个字段 select 字段1,字段2 * 12 from tab;(字段可以参与数学运算)
查询多个字段 select 字段1,字段2 * 12 as(可以使用空格代替) 新名称 from tab;(字段可以参与数学运算)
给查完结果重新命名 加 as(可以使用空格代替)
拼接字符串使用单引号
between ... and ... 两个值之间 ,等同于 >= and <= ;
is null 为空 (is not null 不为空)
and 与
or 或
in 包含相当与多个 or语句(not in 不在这个范围内)
not not可以取非,主要用在is或者in中
link 称为模糊查询,支持%或者下划线匹配,
*and优先级大于or
*在数据库当中null 不是一个值 所以不可以使用等号判断,必须使用is null 或者is not null;
1 条件查询语法
select 字段1,字段2 from 表名 where 条件and条件;
2 求边界之间的
select 字段1,字段2 from 表名 where between 边界值1 and 边界值2; between and 是闭区间
3 求值为null的数据
select 字段名 from 表名 where 字段名 is null;
4 使用in语法
select 字段名 from 表名 where 字段名 in(‘值1’,‘值2’);
在模糊查询中必须掌握的的特殊符号
% 表示多个任意字符
_ 表示一个任意字符
1 语法 查找名字中带有i的?
select 字段名 from where 字段名 like ‘%i%’;
2语法 查找出名字的第二个字母为i的?
select 字段名 from where 字段名 like ‘_i’;
3语法 模糊查找名字中带有_的人
select 字段名 from 表名 where 字段名 like ‘%_%’
4语法 查找左后一位为t的数据
select 字段名 from 表名 where 字段名 like ‘%t’
设置排序 order by 关键字 (排序 : 排序方式默认升序)
排序关键字: asc (升序) desc(降序);
简写(排序)(不健壮不建议写)
按照字段顺序选择
select 字段1,字段2 from 表名 order by 1 排序方式;(默认升序)
1语法 按照字段名2升序排序
select 字段名1,字段2 from 表名 order by 字段名2;
select 字段名1,字段2 from 表名 order by 字段名2 asc;
2语法 按照字段名2降序
select 字段名1,字段名2 from 表名 order by 字段名2 desc;
3语法 按照字段名1降序排字段名1相同时按照字段2的降序排()
select 字段名1,字段名2 from 表名 order by 字段名1 desc ,字段名2 asc;
语法1
select * from 表名 where 条件 order by 字段 排序
语法2
select
-> 字段1,字段2,字段3
-> from
-> 表名
-> where 需要模糊查找的字段名 like ‘%i%’
-> order by
-> 根据那个字段名排序 desc;
结果
count 计数
sum 求和 会忽略null
avg 平均值
max 最大值
min 最小值
- 所有的分组函数都说是对某一组数据进行操作的
bas
dept 部门表
deptno --部门编号
dname --部门名称
loc --地址
emp 员工表
empno – 员工编号
ename – 员工姓名 -
job – 岗位
mgr – 直接领导编号
hiredate – 雇佣日期,入职日期
sal – 薪水
comm – 提成
deptno – 部门编号
1 求工资综合和
select sum(sal) from emp;
±---------+
| sum(sal) |
±---------+
| 29025 |
±---------+
2 最高工资
select max(sal) from emp;
±---------+
| max(sal) |
±---------+
| 5000 |
±---------+
3 最低工资
select min(sal) from emp;
±---------+
| min(sal) |
±---------+
| 800 |
±---------+
4 求平均值
select avg(sal) from emp;
±----------+
| avg(sal) |
±----------+
| 2073.2143 |
±----------+
5 求总人数
select count() from emp;
±---------+
| count() |
±---------+
| 14 |
±----------+
解决办法为 ifnull() 可以对空进行预处理
1计算每个员工的年薪
select ename,(sal+ifnull(comm,0)) from emp;
±-------±---------------------+
| ename | (sal+ifnull(comm,0)) |
±-------±---------------------+
| 刘一 | 800 |
| 陈二 | 1900 |
| 张三 | 1750 |
| 李四 | 2975 |
| 王五 | 2650 |
| 赵六 | 2850 |
| 孙七 | 2450 |
| 周八 | 3000 |
| 吴九 | 5000 |
| 郑十 | 1500 |
| 郭十一 | 1100 |
| 钱多多 | 950 |
| 大锦鲤 | 3000 |
| 木有钱 | 1300 |
±-------±---------------------+
select ename,sal from emp where sal > avg(sal); 错误
在sql语法中,where子句中不可直接使用分组函数
count() 与count(字段)的区别
count(字段名) 统计不为空的数量
count() 不是统计总记录的的数量
group by :按照某个字段分组或者某些字段进行分组;
having :按照分组之后的数据进行筛选再次过滤;
在我们的数据中
select 5 取值
…
from 1 查
…
where 2 过滤
…
group by 3 分组
…
having 4 在过滤
…
order by 6 排序
…
分组函数为什么不能在where之后执行
语法优先级先执行 groud by 在执行分组函数
分组函数一般都和groub by联合执行
当一条语句没有group by 的时候会自成一组
1 查询工资大于平均工资的员工
select ename,sal from emp where sal > (select avg(sal) from emp);
+--------+------+
| ename | sal |
+--------+------+
| 李四 | 2975 |
| 赵六 | 2850 |
| 孙七 | 2450 |
| 周八 | 3000 |
| 吴九 | 5000 |
| 大锦鲤 | 3000 |
+--------+------+
2 查询每个部门不同岗位的不同薪资 ?? 可以将多个分子条件看成一个逗号隔开。
select ename,deptno,job,max(sal) from emp group by deptno,job;
±-------±-------±-------±---------+
| ename | deptno | job | max(sal) |
±-------±-------±-------±---------+
| 吴九 | 10 | 总裁 | 5000 |
| 孙七 | 10 | 经理 | 2450 |
| 木有钱 | 10 | 职员 | 1300 |
| 周八 | 20 | 分析师 | 3000 |
| 李四 | 20 | 经理 | 2975 |
| 刘一 | 20 | 职员 | 1100 |
| 陈二 | 30 | 推销员 | 1600 |
| 赵六 | 30 | 经理 | 2850 |
| 钱多多 | 30 | 职员 | 950 |
±-------±-------±-------±---------+
3 查询每个部门最高薪资,大于2500的 ??
解法1 比解法2 速度快 因为1 是过滤掉不合格的后分组 解法2是先过滤在分组在过滤;
解法1 select ename,job,deptno,max(sal) from emp where sal > 2500 group by deptno;
+-------+------+--------+----------+
| ename | job | deptno | max(sal) |
+-------+------+--------+----------+
| 吴九 | 总裁 | 10 | 5000 |
| 李四 | 经理 | 20 | 3000 |
| 赵六 | 经理 | 30 | 2850 |
+-------+------+--------+----------+
解法2 select deptno,max(sal) from emp group by deptno having max(sal)>2900;
+--------+----------+
| deptno | max(sal) |
+--------+----------+
| 10 | 5000 |
| 20 | 3000 |
+--------+----------+
4 求出每个部门平均薪资大于两千的?? 先分组 求出平均薪资在筛选;
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
±-------±----------+
| deptno | avg(sal) |
±-------±----------+
| 10 | 2916.6667 |
| 20 | 2175.0000 |
±-------±----------+
select 总结
一个完整的select查询语句
select …
from …
where …
group by …
having …
order by …
书写顺序不能该
去重 distinct
* distinct 必须在最前边 , 多个字段为联合去重
select distinct 字段 from 表名;
实际开发中都是多表查询
节省空间,避免数据冗余
链接数据库的分类
链接查询的分类
根据表的链接方式划分
内连接
等值链接
非等值链接
自链接
外连接
左连接
右连接
全连接(很少用)
笛卡尔积现象(笛卡尔乘积现象)
在联查表的时候如果没有过滤条件的话,的得到的结果会是两张表的乘积,
关于表中的别名
select e.ename b.dname from emp e, dept d ;
表的别名的好处
1.可读性高
2.执行效率高
如何避免笛卡尔积现象? 添加过滤条件。
思考:避免了笛卡尔积现象,会减少比对次数吗? —————— 不能!
sql92 语法结构链接表与数据判断不清晰
sql99 语法结构更清晰
内链接之等值链接
select
e.A数据名,b.B数据名
from
A表 e
inner join (inner可省略)
B表 b
on
表链接条件
表链接条件为等号的叫等职链接
99语法: select
-> e.ename,d.dname
-> from
-> emp e
-> inner join //inner 可以省略,带上可读性更好
-> dept d
-> on
-> e.deptno = d.deptno;
±-------±-------+
| ename | dname |
±-------±-------+
| 孙七 | 财务部 |
| 吴九 | 财务部 |
| 木有钱 | 财务部 |
| 刘一 | 研发部 |
| 李四 | 研发部 |
| 周八 | 研发部 |
| 郭十一 | 研发部 |
| 大锦鲤 | 研发部 |
| 陈二 | 销售部 |
| 张三 | 销售部 |
| 王五 | 销售部 |
| 赵六 | 销售部 |
| 郑十 | 销售部 |
| 钱多多 | 销售部 |
±-------±-------+
非等值来链接,表链接条件为非等量关系
自链接 一张表看成两张表 自己链接自己叫自链接
select
-> e.ename,d.ename
-> from
-> emp e
-> inner join
-> emp d
-> on
-> e.empno = d.mgr;
±-------±-------+
| ename | ename |
±-------±-------+
| 大锦鲤 | 刘一 |
| 赵六 | 陈二 |
| 赵六 | 张三 |
| 吴九 | 李四 |
| 赵六 | 王五 |
| 吴九 | 赵六 |
| 吴九 | 孙七 |
| 李四 | 周八 |
| 赵六 | 郑十 |
| 周八 | 郭十一 |
| 赵六 | 钱多多 |
| 李四 | 大锦鲤 |
| 孙七 | 木有钱 |
±-------±-------+
内连接
假设A和B两张表进行连接,使用内连接的话,凡是A表和B表能够匹配上的记录都查询出来,这就是内连接。
AB两张表没有主副之分,他是平等的。
外连接
假设A和B两张表进行链接,使用外来链接的话,AB两张表一张是主表一张是副表,主要查询主表中的数据,
捎带着查询副表中的数据,当附表没有匹配项副表会自动模拟出来null与之匹配。
外链接的分类
左外连接:表示左边的这张表是主表 语法 left join
右外连接:表示右边的这张表是主表 语法 right join
左链接有右连接的写法,右链接有左连接的写法
<!-- 左外连接 -->
select
-> a.ename,b.ename
-> from
-> emp a
-> left join (左边为主表,没有匹配项时,副表自动模拟一条为null的值与之匹配)
-> emp b
-> on
-> b.empno = a.mgr;
+--------+--------+
| ename | ename |
+--------+--------+
| 刘一 | 大锦鲤 |
| 陈二 | 赵六 |
| 张三 | 赵六 |
| 李四 | 吴九 |
| 王五 | 赵六 |
| 赵六 | 吴九 |
| 孙七 | 吴九 |
| 周八 | 李四 |
| 吴九 | NULL |
| 郑十 | 赵六 |
| 郭十一 | 周八 |
| 钱多多 | 赵六 |
| 大锦鲤 | 李四 |
| 木有钱 | 孙七 |
+--------+--------+
外连接的特点,主表的数据全部无条件查询出来
查询那个部门没有员工
select b.* from
-> emp a
-> right join
-> dept b
-> on
-> a.deptno = b.deptno
-> where a.ename is null;
±-------±-------±-----+
| deptno | dname | loc |
±-------±-------±-----+
| 40 | 行政部 | 深圳 |
±-------±-------±-----+
三表联查(夺表联查)
语法
select 表1字段,表2字段,表3字段
from
表1 别名a
join
表2 别名b
on
表1与表2的条件
join
表3 别名s
on
表1与表3的条件
select a.ename,d.dname,s.grade
from
emp a
join
dept d
on
a.deptno = d.deptno
join
salgrade s
on
a.sal between s.losal and s.hisal;
±-------±-------±------+
| ename | dname | grade |
±-------±-------±------+
| 刘一 | 研发部 | 1 |
| 陈二 | 销售部 | 3 |
| 张三 | 销售部 | 2 |
| 李四 | 研发部 | 4 |
| 王五 | 销售部 | 2 |
| 赵六 | 销售部 | 4 |
| 孙七 | 财务部 | 4 |
| 周八 | 研发部 | 4 |
| 吴九 | 财务部 | 5 |
| 郑十 | 销售部 | 3 |
| 郭十一 | 研发部 | 1 |
| 钱多多 | 销售部 | 1 |
| 大锦鲤 | 研发部 | 4 |
| 木有钱 | 财务部 | 2 |
±-------±-------±------+
找出每个员工的部门名称,工资等级,以及上级领导
select a.ename as '员工姓名',d.dname as '部门名称',s.grade as '工资等级',c.ename as '部门领导'
-> from
-> emp a
-> join
-> dept d
-> on
-> a.deptno = d.deptno
-> join
-> salgrade s
-> on
-> a.sal between s.losal and s.hisal
-> left join
-> emp c
-> on
-> a.mgr = c.empno;
+----------+----------+----------+----------+
| 员工姓名 | 部门名称 | 工资等级 | 部门领导 |
+----------+----------+----------+----------+
| 刘一 | 研发部 | 1 | 大锦鲤 |
| 陈二 | 销售部 | 3 | 赵六 |
| 张三 | 销售部 | 2 | 赵六 |
| 李四 | 研发部 | 4 | 吴九 |
| 王五 | 销售部 | 2 | 赵六 |
| 赵六 | 销售部 | 4 | 吴九 |
| 孙七 | 财务部 | 4 | 吴九 |
| 周八 | 研发部 | 4 | 李四 |
| 吴九 | 财务部 | 5 | NULL |
| 郑十 | 销售部 | 3 | 赵六 |
| 郭十一 | 研发部 | 1 | 周八 |
| 钱多多 | 销售部 | 1 | 赵六 |
| 大锦鲤 | 研发部 | 4 | 李四 |
| 木有钱 | 财务部 | 2 | 孙七 |
+----------+----------+----------+----------+
查找出每一个员工的工资等级,部门名称并且按照工资等级排序
select
a.ename,b.grade,c.dname
from
emp a
join
salgrade b
on
a.sal
between
b.losal and b.hisal
left join
dept c
on
a.deptno = c.deptno
order by
b.grade;
+--------+-------+--------+
| ename | grade | dname |
+--------+-------+--------+
| 郭十一 | 1 | 研发部 |
| 钱多多 | 1 | 销售部 |
| 刘一 | 1 | 研发部 |
| 王五 | 2 | 销售部 |
| 张三 | 2 | 销售部 |
| 木有钱 | 2 | 财务部 |
| 陈二 | 3 | 销售部 |
| 郑十 | 3 | 销售部 |
| 周八 | 4 | 研发部 |
| 赵六 | 4 | 销售部 |
| 大锦鲤 | 4 | 研发部 |
| 孙七 | 4 | 财务部 |
| 李四 | 4 | 研发部 |
| 吴九 | 5 | 财务部 |
+--------+-------+--------+
子查询
select中嵌套select语句叫做子查询
select
...(select)
from
...(select)
where
...(select)
1、where 后边使用子查询
找出工资大于平均薪资的员工信息
select * from emp where sal>(select avg(sal) from emp);
±------±-------±-------±-----±-----------±-----±-----±-------+
| empno | ename | job | mgr | hiredate | sal | comm | deptno |
±------±-------±-------±-----±-----------±-----±-----±-------+
| 7566 | 李四 | 经理 | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7698 | 赵六 | 经理 | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | 孙七 | 经理 | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | 周八 | 分析师 | 7566 | 1987-06-13 | 3000 | NULL | 20 |
| 7839 | 吴九 | 总裁 | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7902 | 大锦鲤 | 分析师 | 7566 | 1981-12-03 | 3000 | NULL | 20 |
±------±-------±-------±-----±-----------±-----±-----±-------+
2、from后边嵌套子查询
案例:找出每个部门的平均薪水的薪资的薪资等级
(按照部门分组)
select
d.dname,a.sal
from
dept d
join
(select deptno,avg(sal) sal from emp group by deptno) a
on
d.deptno = a.deptno;
+--------+-----------+
| dname | sal |
+--------+-----------+
| 财务部 | 2916.6667 |
| 研发部 | 2175.0000 |
| 销售部 | 1566.6667 |
+--------+-----------+
求每个部门的平均薪资的等级
select
d.dname,d.deptno,avg(c.grade) grade
from
emp a
join
dept d
on
a.deptno = d.deptno
join
salgrade c
on
a.sal between c.losal and c.hisal
group by
d.dname
order by grade desc
;
+--------+--------------+
| dname | avg(c.grade) |
+--------+--------------+
| 研发部 | 2.8000 |
| 财务部 | 3.6667 |
| 销售部 | 2.5000 |
+--------+--------------+
4.union 可以将查询的结果相加
5.limit 分页查询(mysql特有的)
limit 取结果中的部分数据(作用)
语法机制:limit startIndex , length
startIndex 起始位置
length 取的个数(取几个)
select ename,sal from emp limit 0 ,5;可以去掉起始位置,默认从第一个开始。
limit 最后执行
6.创建表
create table 表名(
字段1 数据类型,
字敦2 数据类型,
字敦3 数据类型,
。。。
)
例:create table t_(id int,name varchar(255));
7.添加字段 insert into 表名(字段1,字段2) values (值1,值2)
例:insert into t_(id,name) values (0,'名称');
插入多行
insert into 表名
(字段名1,字段名2)
values
(字段1,字段1),
(字段2,字段2),
(字段3,字段3);
8.表的复制
create table 新表名 select * from 表名;
9.将查询结果插入到一张表中
insert into 新表名 select * from 要查询的表名;
(如果查询结果的字段多于要插入的表的话不会成功)
10. 修改数据
updat 表名 set 字段名1 = 值1,字段名2 = 值2 。。。 where 条件;
注意:没有条件整张表都会被修改!!!
update t_ set name = '小王' where id=0;
11.删除数据
delete from 表明 where 条件e
如果删除数据过多会卡顿(因为没有释放数据的真实空间)
truncate table 表名
truncate table t_;
增:insert 删除: delete 改:update 查:select
创建表加入约束
为了保持表的唯一性,合法性。
– 1.列出至少有一个员工的所有部门。
– 2.列出薪金比"刘一"多的所有员工。
– 3.***** 列出所有员工的姓名及其直接上级的姓名。
– 4.列出受雇日期早于其直接上级的所有员工。
– 5.列出部门名称和这些部门的员工信息,同时列出那些没有员工的部门。
– 6.列出所有job为“职员”的姓名及其部门名称。
– 7.列出最低薪金大于1500的各种工作。
– 8.列出在部门 “销售部” 工作的员工的姓名,假定不知道销售部的部门编号。
– 9.列出薪金高于公司平均薪金的所有员工。
– 10.列出与"周八"从事相同工作的所有员工。
– 11.列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
– 12.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
– 13.列出在每个部门工作的员工数量、平均工资。
– 14.列出所有员工的姓名、部门名称和工资。
– 15.列出所有部门的详细信息和部门人数。
– 16.列出各种工作的最低工资。
– 17.列出各个部门的 经理 的最低薪金。
– 18.列出所有员工的年工资,按年薪从低到高排序。
– 19.查出emp表中薪水在3000以上(包括3000)的所有员工的员工号、姓名、薪水。
– 20.查询出所有薪水在’陈二’之上的所有人员信息。
– 21.查询出emp表中部门编号为20,薪水在2000以上(不包括2000)的所有员工,显示他们的员工号,姓名以及薪水,以如下列名显示:员工编号 员工名字 薪水
– 22.查询出emp表中所有的工作种类(无重复)
– 23.查询出所有奖金(comm)字段不为空的人员的所有信息。
– 24.查询出薪水在800到2500之间(闭区间)所有员工的信息。(注:使用两种方式实现and以及between and)
– 25.查询出员工号为7521,7900,7782的所有员工的信息。(注:使用两种方式实现,or以及in)
– 26.查询出名字中有“张”字符,并且薪水在1000以上(不包括1000)的所有员工信息。
– 27.查询出名字第三个汉字是“多”的所有员工信息。
– 28.将所有员工按薪水升序排序,薪水相同的按照入职时间降序排序。
– 29.将所有员工按照名字首字母升序排序,首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc;
– 30.查询出最早工作的那个人的名字、入职时间和薪水。
– 31.显示所有员工的名字、薪水、奖金,如果没有奖金,暂时显示100.
– 32.显示出薪水最高人的职位。
– 33.查出emp表中所有部门的最高薪水和最低薪水,部门编号为10的部门不显示。
– 34.删除10号部门薪水最高的员工。
– 35.将薪水最高的员工的薪水降30%。
– 36.查询员工姓名,工资和 工资级别(工资>=3000 为3级,工资>2000 为2级,工资<=2000 为1级)
– 语法:case when … then … when … then … else … end