c数据库笔记(1)
重点内容 在p77 && p78
XXX.sql 这种文件被称为sql脚本文件在 在执行sql脚本文件的时候里面的SQL语句会全部被执行,执行的方法 source +绝对路径
1.数据库基础及常用的命令
mysql数据库自带了四个数据库
1.1常用命令
-
查看数据库
show databases;
-
使用数据库
use 数据库名称
-
创建数据库
create databases 数据库名称
-
查看数据库中有哪些表
show tables
-
查看表中的数据
desc 表名;
-
删除数据库
drop databases if exists 数据库名称
1.2 基本概念
1.2.1 表的概念
数据库中最基本的单位是表
任何一张表中都有行和 行被称为 记录 列被称为记录
1.2.2 sq语句分类
SQL语句有很多,最好进行分门别类,这样更容易记忆。 分为: DQL: 数据查询语言(凡是带有select关键字的都是查询语句) select... DML: 数据操作语言(凡是对表当中的数据进行增删改的都是DML) insert delete update insert 增 delete 删 update 改 这个主要是操作表中的数据data。 DDL: 数据定义语言 凡是带有create、drop、alter的都是DDL。 DDL主要操作的是表的结构。不是表中的数据。 create:新建,等同于增 drop:删除 alter:修改 这个增删改和DML不同,这个主要是对表结构进行操作。 TCL: 不是王牌电视。 是事务控制语言 包括: 事务提交:commit; 事务回滚:rollback; DCL: 是数据控制语言。 例如:授权grant、撤销权限revoke....
1.3 简单查询语句
-
查询表中的数据
SELECT *from 表名;
-
查看表结构
desc 表名;
-
查看当前使用的数据库
select database();
2.函数
单行处理函数(‘处理完一行在处理另外一行)
多行处理函数(多条记录得出一个结果)
-
常见的单行处理函数
lower 转换大小写
select lower(ename) as name from emp;
upper 转换大写
select upper(ename) from emp;
substr 取子串 substr(被截取的字符串,起始下标,截取的长度)
需要注意起始下标是从1开始 select substr(ename, 1, 5) as name from emp; select ename,sal from emp where substr(ename,1,1)='A';
length 取长度
select ename as name,length(ename) as len from emp
trim 去空格:去除前后空白的
select trim(ename) as name from emp; select *from emp where ename=trim(' king');
concat 进行字符串拼接
select concat(ename,job) as nameandjob from emp;
str_to_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位
round 四舍五入 round(数值,保留几位)
//生成100以内的随机数 select round(rand()*100,0) from emp;
rand() 生成随机数
select rand() from emp;
ifnull 可以将null转换成为一个具体的值 空处理函数
ifnull(数据,数据被当做哪个值)
在所有的数据库中只要有null参与的数据运算 最后的结果都是null
select ename ,sal+comm as salcomm from emp; //返回的结果里面有null 这合理吗? 这不合理 //如果comm为null的话当做0 来处理 select ename ,(sal+ifnull(comm,0))*12 from emp;
case..when..than..when..than..else..end
当什么时候怎么做 当什么时候怎么做 否则 怎么做
select job,ename as name,sal as oldsal, (case job when 'CLERK' than sal*1.2 when 'SALESMAN' than sal*1.5 else sal end) as newsal from emp;
-
常见的多行处理函数(分组函数)
输入多行最终输出一行
注意:如果没有对数据进行分组整张表默认为一组
1.分组函数自动忽略null 不需要提起对null进行处
count 计数、
count(*) :统计表中的总函数 只要有一行数据conunt就会自己计算
count(ename)找ename里面不为空的字段
//计算员工数量 select count(ename) from emp;
目录
select count(comm) from emp;//只统计comm里面不为null的 select count(*) from emp;//统计一共有几行
sum 求和
//计算员工工资总数 select sum(sal) from emp;
avg 求平均值
//计算员工平均工资 select avg(sal) from emp; select (sum(sal)/count(ename)) as avgsal from emp;
max 最大值
select max(sal),ename from emp;
min 最小值
select min(sal) from emp;
3查询
3.1简单查询
select 字段名 from 表名; 其中要注意: select和from都是关键字。 字段名和表名都是标识符。
强调: 对于SQL语句来说,是通用的, 所有的SQL语句以“;”结尾。 另外SQL语句不区分大小写,都行。
-
查询一个字段
查询emp表中的姓名 select ename from emp;
-
查询多个字段
select 字段1,字段2 from 表; select ename,job from emp;
-
查询所有字段
不建议在开发中使用
select *from emp;
-
给查询的列起别名
使用as关键字来起别名
只是给查询到的表起别名 对于原表没有任何影响
注意:在给列起别名的时候不能有空格 如果非要加空格就需要加引号括起来
在所有的数据库中字符串都是使用单引号括起来的 双引号是不标准的
如果别名是中文的话 别名需要使用单引号括起来
select ename as name from emp; //带有空格的别名需要用引号括起来 select ename as 'bb name' from emp; //别名是中文 select ename as '姓名' from emp;
-
数学表达式
字段可以使用数学表达式 可以参与数学运算
//根据员工的月薪资计算出年薪 select ename,sal*12 from emp; select ename,sal*12 as yearsal from emp;
3.2条件查询
不是将表中的所有的数据都查处啦 只是查询出符合条件的字段
条件查询语法
select
字段1,字段2,......
from
表名
where
条件
都有哪些条件
-
=(等于) !=(不等于) <>(不等) < (小于) >(大于) >=(大于等于) <=(小于等于)
select sal as'工资',ename as '姓名' from emp where sal=3000; select sal as'工资',ename as '姓名' from emp where sal!=3000; select sal as'工资',ename as '姓名' from emp where sal<3000; select sal as'工资',ename as '姓名' from emp where sal>3000; select sal as'工资',ename as '姓名' from emp where sal<=3000; select sal as'工资',ename as '姓名' from emp where sal>=3000;
-
between and(两个值之间)
使用between and 的时候必须左小右大的 是包括两边的值的
select ename as '姓名',sal as '工资' from emp where sal between 1500 and 30000;
-
is null && is not null
查询某个列是不是null 在数据库中 null是真的表示啥也没有
is not null 表示不为空
select ename,sal,comm from emp where comm is null; select ename,sal,comm from emp where comm is not null;
-
and 并且 && or或者
满足两个或者多个条件
and 和 or 同时出现时的优先级问题
and 的优先级比or高 会先执行and再执行or
select ename,sal,job from emp where job='MANAGER' and sal >=1500; select ename,job,sal from emp where job='MANAGER' or job='CLERK'; //查询工资大于两千五 并且部门编号为10或者20的 select job,deptno,ename from emp where sal>=2500 and (deptno=10 or deptno =20);
-
in && not in
不是表达的区间而是具体的值
not in 就是in 的取反
//找出工资为800 5000 和3000的 select sal,ename from emp where sal in(800,5000,3000); //找出工资不为800 5000 和3000的 select sal,ename from emp where sal not in(800,5000,3000);
-
like 模糊查询
% 匹配多个字符
_任意一个字符
反斜杠+ _ 表示转义
b//找出名字里面o的 select ename from emp where ename like '%o%'; //找出名字是t结尾的 select ename from emp where ename like '%t'; //找名字为s开头的 select ename from emp where ename like 's%'; //找出名字里面有下换线的 select ename from emp where ename like '%\_%'
3.3排序
-
升序
关键字order by
select sal,ename from emp order by sal;
-
降序
关键字desc
select sal,ename from emp order by sal desc;
-
指定升序
关键字asc
select sal,ename from emp order by sal asc;
-
多条件排序
border by 条件a,条件b
//先按照工资排工资一样按照名字排 select sal,ename from emp order by sal asc,ename desc;
-
字段位置排序
按照第二列排,开发中不建议这样写 因为数据 的位置会发生变化 不健壮
order by 2;
select sal,ename from emp order by 2;
-
小练习(找出工资咋1250到3000之间的员工要求按照工资的降序排)
select ename as '姓名',sal as '工资' from emp where sal between 1250 and 3000 order by sal desc;
3.4.分组查询 联合查询 再分组 去重
在实际的应用中 可能有这样的需求 需要先进行分组 然后对每一组的数据进行操作 这个时候需要我们使用分组查询
分组函数在使用的时候先分组在使用
重点:在一条select 语句中 如果有group by 语句的话,select后面只能跟参加分组的字段,以及分组的函数,其他的一律不能跟。
-
基本语法
(4)select ... (1)from ... (2)where ... (3)group by ... hoving ... (5)order by ...
-
执行顺序
1.from
2.where
3.group by
4.having
5.select
6.order by
从某张表中查询数据 先经过where条件筛选出有价值而的数据
对这些有价值的数据进行分组
分组之后可以使用having 继续筛选
select 查询出来
最后排序输出
-
找出每个岗位的工资和?
实现思路:按照工作岗位进行分组 然后对工资进行求和
select sum(sal),job from emp group by job order by sal asc;
-
找出每个部门的最高薪资
设计思路:按照部门分组,然后找最大值
select deptno,max(sal) from emp group by deptno;
-
两个字段联合分组
select ...
from ...
group by ...,...
order by ...;
-
找出每个部门的不同岗位的最高薪资
select max(sal),deptno from emp group by deptno,job;
-
找出每个部门的最高薪资 要求显示工资大于3000的
select max(sal) as maxsal,job,deptno from emp where sal>3000 group by deptno order by sal asc;
-
having 再筛选
要和group by 配合使用
group by ...
hoving .....
找出每个部门的平均薪资 要求显示平均薪资高于2500的 select avg(sal) as avgsal,deptno from emp group by deptno having avg(sal)>2500 order by sal desc;
-
要求找出每个岗位的平均薪资 要求显示平均薪资大于1500的 除managre以外 要求按照平均薪资降序排
select avg(sal) as avgsal,job from emp where job!='MANAGER' group by job having avg(sal)>1500 order by sal desc;
-
去除重复记录distinct
去除查询结果中的重复记录
只能是出现在所有的字段的前面
distinct 出现在多个字段之前 表示多个字段联合去除重复
select distinct job from emp;
统计工作岗位的数量
select count(distinct job) from emp;
3.5 连接查询(很重要)
多张表联合起来查询数据 被称为连接查询
-
分类
内连接
等值连接
非等值连接
自连接
外连接
左外连接
右外连接
全连接
-
笛卡尔积现象
当两张表进行连接查询时,没有任何的添加显示,最终的查询结果条数 是两张表条数的积,这种现象被称为笛卡尔积现象(笛卡尔发现的,这是一个数学现象)
如何避免笛卡尔积现象:在连接查询时,满足条件的记录被筛选出来
最终的查询结果条数还是14条,但是匹配的过程中匹配的次数没有减少,只是进行了筛选
表的连接的次数越多 查询的效率就越低 尽量降低表的连接次数
select emp.ename,dept.dname from emp,dept where emp.deptno=dept.deptno; //当员工表的部门编号和结构表的部门编号相同
-
给表起别名
select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
3.5.1内连接等值连接
条件是等值关系 所以被称为是等值连接2
-
92语法
表的连接不清晰
select e.ename,d.dname from emp e,dept d where d.deptno =e.deptno;
-
99语法
表的连接和后续的筛选条件是分离的
基本语法如下
注意:inner是表示内连接带着会更好一些 一眼就能看出来是内连接
select e.ename,d.dname from emp e inner join dept d on e.DEPTNO = d.DEPTNO;
3.5.2内连接之非等值连接
条件不是一个等量关系 称为非等值连接
//找出每个员工的工资等级 要求显示员工的姓名 薪资 工号 薪资等级 select e.ename, e.sal,s.grade from emp e inner join salgrade s on e.sal between s.losal and s.hisal; //e表里面的工资介于最高和最低工资之间
3.5.3内连接之自连接
把一张表看成是两张表 然后从两张表中找出需要对应的数据
-
查询员工的上级领导 要求显示员工名和对应的领导名
select a.ename as'员工名',b.ename as'领导名' from emp a inner Join emp b on a.mgr = b.empno; //员工的领导编号等于领导的员工编号
3.5.4右/左外连接
right 代表将join关键字右边的这张表看成是主表,主要是把这张表的数据都查出来 捎带的查一下左边的表
在外连接中 两张表是有主次关系的
带有right的是右外连接 又叫做右连接
带有left的是左外连接 又叫座左连接
outer 也是可以省略的 带着的话可读性强一些
外连接的查询结果条数一定都是大于等于内连接的查询结果条数
//主要是把dept表中的内容查出来 捎带的查emp表的内容 //右连接写法 select e.ename,d.dname from emp e right outer join dept d on e.deptno = d.deptno; //左连接写法 select e.ename,d.dname from dept d left outer join emp e on e.deptno = d.deptno;
-
查询每个员工的上级领导,要求显示所有员工的名字和领导名
select a.ename as '员工',b.ename as '老板名' from emp a left join emp b on a.mgr=b.empno;
3.5.5 多表连接
语法:
select 。。。
from a
join b
on a和b的连接条件
join c
on a和c的连接条件
join d
on a和d的连接条件
一条SQL语句中内连接和外连接混合
-
找出每个员工的部门名称 以及工资等级 要求显示员工名 部门名 薪资和薪资等级
select e.ename ,e.sal ,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 HISAL;
3.6子查询
select 语句中嵌套select语句 被嵌套的select被称为子查询
子查询可以出现的位置
select .....(select),
from ......(select),
where ......(select),
3.6.1 where语句中的子查询
-
案例:找出比最低工资高的员工的姓名和工资
实现思路:1,找最低工资 select min(sal) from emp; 2.找出大于800的 select ename,sal from emp where sal>800; 3.合并 select ename,sal from emp where sal>(select min(sal) from emp);
3.6.2 from语句中的子查询
from 语句中的子查询 可以将子查的查询结果当做一张临时表。
-
案例找出每个岗位的平均工资的工资等级
思路:1,找出每个岗位的平均工资(按照岗位分组求平均值) select job,avg(sal) from emp group by job; 2.把第一张表看成是一种表 t select *From salgrade; t表和s表进行表连接:条件是 avg(sal) between s. LOSAL and HISAL select t.*,s.grade from (select job,avg(sal) as avgasl from emp group by job) t join salgrade s on t.avgasl between s.losal and s.hisal; //注意:一定要给那张临时表起的数据起别名 不然会引起一些错误 //例如:avg(sal)as avgsal
3.6.3 select 语句后面的子查询
了解即可
-
案例:找出每个员工的部门名称,要求显员工名 部门名
累了懒得写
3.7 union合并查询结果集
union 的效率要给一些 union可以减少匹配的次数 在减少查询次数的情况下 还可以减少匹配次数
-
案例:查询工作岗位是SALESMAN 和MANAGER
select ename, job from emp where job='SALESMAN' or job='MANAGER'; //写法二 select ename, job from emp where job in('SALESMAN','MANAGER'); //使用union select ename, job from emp where job='SALESMAN' union select ename, job from emp where job='MANAGER';
3.8 limit 查询结果集的一部分取出来
把查询结果集的一部分取出来,通常使用在分页查询中
limit可以用来提高用户的使用体验
用法 limit 起始下标,长度(下标是从0 开始的)
注意:limit在order by 执行之后执行的
-
按照薪资的降序排,找出排在前五的员工
select sal from emp order by sal desc limit 0,5; //找出排名三到五的员工 从下标二开始 总共找三个 select sal,ename from emp order by sal asc limit 2,3; //找出工资排名在5-9的员工 select ename,sal from emp order by sal asc limit 4,5;
3.9 通用分页
分页
-
每页显示三条记录 应该怎么做
第一页:limit 0,3;
第二页:limit 3,3;
。。。。。。。。。
规律:(需要几页-1)*每页要显示数据的条数
公式:limit(需要的页数-1)* 每页需要显示的数据条数,数据的条数;
//查询员工的工资排名两个一页 select ename,sal from emp order by sal asc limit 5 ,2;
3.10 日期&&数字格式化
format(数字,"格式");---数字格式化
date_format('字符串','日期格式')
select ename,format(sal,'$999.999') as sal from emp;
3.11 DQL 大总结
select .... from .... group by ... having ... order by ... limit ....
4.表
4.1数据类型
数值类型
MySQL 支持所有标准 SQL 数值数据类型。
这些类型包括严格数值数据类型(INTEGER、SMALLINT、DECIMAL 和 NUMERIC),以及近似数值数据类型(FLOAT、REAL 和 DOUBLE PRECISION)。
关键字INT是INTEGER的同义词,关键字DEC是DECIMAL的同义词。
BIT数据类型保存位字段值,并且支持 MyISAM、MEMORY、InnoDB 和 BDB表。
作为 SQL 标准的扩展,MySQL 也支持整数类型 TINYINT、MEDIUMINT 和 BIGINT。下面的表显示了需要的每个整数类型的存储和范围。
类型 大小 范围(有符号) 范围(无符号) 用途 TINYINT 1 Bytes (-128,127) (0,255) 小整数值 SMALLINT 2 Bytes (-32 768,32 767) (0,65 535) 大整数值 MEDIUMINT 3 Bytes (-8 388 608,8 388 607) (0,16 777 215) 大整数值 INT或INTEGER 4 Bytes (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值 BIGINT 8 Bytes (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值 FLOAT 4 Bytes (-3.402 823 466 E+38,-1.175 494 351 E-38),0,(1.175 494 351 E-38,3.402 823 466 351 E+38) 0,(1.175 494 351 E-38,3.402 823 466 E+38) 单精度 浮点数值 DOUBLE 8 Bytes (-1.797 693 134 862 315 7 E+308,-2.225 073 858 507 201 4 E-308),0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 0,(2.225 073 858 507 201 4 E-308,1.797 693 134 862 315 7 E+308) 双精度 浮点数值 DECIMAL 对DECIMAL(M,D) ,如果M>D,为M+2否则为D+2 依赖于M和D的值 依赖于M和D的值 小数值 日期和时间类型
表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值,当指定不合法的MySQL不能表示的值时使用"零"值。
TIMESTAMP类型有专有的自动更新特性,将在后面描述。
类型 大小 ( bytes) 范围 格式 用途 DATE 3 1000-01-01/9999-12-31 YYYY-MM-DD 日期值 TIME 3 '-838:59:59'/'838:59:59' HH:MM:SS 时间值或持续时间 YEAR 1 1901/2155 YYYY 年份值 DATETIME 8 1000-01-01 00:00:00/9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值 TIMESTAMP 4 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒,北京时间 2038-1-19 11:14:07,格林尼治时间 2038年1月19日 凌晨 03:14:07 YYYYMMDD HHMMSS 混合日期和时间值,时间戳 字符串类型
字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。
类型 大小 用途 CHAR 0-255 bytes 定长字符串 VARCHAR 0-65535 bytes 变长字符串 TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串 TINYTEXT 0-255 bytes 短文本字符串 BLOB 0-65 535 bytes 二进制形式的长文本数据 TEXT 0-65 535 bytes 长文本数据 MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据 MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据 LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据 LONGTEXT 0-4 294 967 295 bytes 极大文本数据 注意:char(n) 和 varchar(n) 中括号中 n 代表字符的个数,并不代表字节个数,比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似,但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR,不同的是它们包含二进制字符串而不要非二进制字符串。也就是说,它们包含字节字符串而不是字符字符串。这说明它们没有字符集,并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象,可以容纳可变数量的数据。有 4 种 BLOB 类型:TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型:TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型,可存储的最大长度不同,可根据实际情况选择。-
-
常用类型:
varchar 可变长的字符串 会根据实际的数据长度来分配动态空间
char 定长的字符串
int 相当于java中的int
bigint 长整形
float:单精度浮点型
double:双精度浮点型
date 短日期类型
datetime 长日期类型
clob 字符大对象 最多可以存储4G的字符串 一般用来存储文章,说明什么的
blob 二进制大对象 专门用来存储图片 视频 声音 等流媒体视频
4.2表的创建
create table 表名(
字段名1,数据类型,
字段名2,数据类型,
字段名3,数据类型
);
//定义一个学生信息表 学号 姓名 年龄 邮箱地址 性别 create table student( id int, name varchar(32), age int(3), sex char(1) default 'm',//default 指定一个默认值 m email varchar(255) );
-
快速建表( 表的复制)
create table 表名 as 查询语句的结果
意思就是把查询的结果当做一张表新建 完成表的复制 表创建了 数据也复制出来了
create table student2 as select *from student; //也可以把一列的查询结果复制出来 create table student3 as select id,name,age,sex,email from student where id=01 or id =02;
4.3 删除表
drop table 表名;
如果表存在的话就删除
drop table if exists 表名 ;
drop table if exists t_student;
4.4 插入数据
insert into 表名(字段1,字段2,字段3.......) values(值1,值2,值3.......) 字段是可以省略的 省略的话就是代表都写上 且不能颠倒顺序
insert 一旦执行成功 就会多一条记录 没有给字段指定值的话 默认值是null
insert into student(id,name,age,sex,email) values(1,'张三',19,'男','1233@qq.com'); //一次插入多条记录 insert into student(id,name,age,sex,email) values (01,'张三',19,'男','123@qq.com'), (02,'李四',19,'女','124@qq.com'), (03,'王五',20,'男','222@qq.com'), (04,'赵六',19,'女','1666@qq.com');
4.5 插入日期&&日期格式化
str_to_date :将字符串varchar转换成date类型 通常使用在插入insert方面
格式 :str_to_date('字符串日期','日期格式') 如果格式是%y-%m-%d就不用转换了
%Y 年 注意是大写
%m 月
%d 天
%h 时
%i 分
%s 秒
date_formatt:将date类型转换成为具有一定格式的varchar字符串类型
格式:date_format(日期类型,'格式')
-
date 和 datetime 两个类型的区别
date 是短日期 只包括年月日信息
datetime是长日期 包括年月日时分秒信息
-
获取当前时间
now()函数
创建一个user表 create table user( id int, name varchar(5), birth date ); 创建一个详细一点的表 create_time datetime 表示当前创建表的时间 create table user2( id int, name varchar(5), birth date, create_time datetime );
插入数据(短日期) 1.需要函数做类型转换 insert into user(id,name,birth) values( 1,'张三',str_to_date('01-01-1999','%d-%m-%Y') ); 2.固定格式不需要用类型转换 insert into user(id,name,birth) values( 2,'lisi','1999-01-01' ); 插入数据(user2 长日期)不适用str_to_date函数 insert into user2(id,name,birth,create_time) values( 1,'战三','1999-03-03','2021-01-01 13:13:00' ); insert into user2(id,name,birth,create_time) values( 2,'战四','1999-03-03',now() );
日期格式化 select id,name,date_format(birth,'%Y/%m/%d')as birth from user; select id,name,birth from user;//查询的结果是和上面的一样的 mysql会自动把相应的日期转换成对应的字符串格式
4.7 修改update(dml)
语法格式:update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3..... where 条件;
注意:没有条件限制会导致所有的数据全部更新。
update user2 set id=1,name='张三',birth='2000-01-01',create_time=now() where id=1;
4.8 删除
语法:delete from 表名 where 条件;
表中的数据被删除了,但是这个数据在硬盘上存储空间不会被释放 优点是可以恢复是支持回滚的,但是执行的效率比较底
-
普通删除
//删除整张表的内容 delete from user2; //删除指定的内容 delete from user2 where id=1;
-
快速删除表中的数据(比较重要)
语法:truncate table 表名;
truncate 语句删除的效率比较高 表被一次截断 物理删除 优点是快速 但是缺点是 不支持回滚 删除了就没了
这种删除方法适用于数据特别大的表
truncate table student;
4.9 修改表结构(对表结构进行增删改查)
需要使用alter 了解即可
5.约束
约束:constraint
在创建表的时候对表中的数据加上一些约束来保证数据的完整性 有效性
5.1 非空约束(not null)
约束的字段不能为null 在字段的后面加 从此这个字段不能为空如果为空就报错
not null 只有列级约束没有表级约束
create table vip( id int not null, name varchar(5) not null ); //正常插入是没问题的 insert into vip(id,name) values(1,'lisa'),(2,'张三'); //如果被非非空约束约束的字段为空的话 insert into vip(id) values (1); //会报错Field 'name' doesn't have a default value
5.2 唯一约束 (unique)
约束的字段不能重复但是可以为null
约束加到列后面的叫列级约束
给多个字段联合起来添加约束叫表级约束
需要给多个字段联合起来添加约束要用到表级约束
//各自唯一 create table vip2( id int not null unique, //添加唯一约束 name varchar(5) unique, //添加唯一约束 sex varchar(1) not null, email varchar(50) unique not null ); //插入数据 insert into vip2(id,name,sex,email) values (1,'战三','男','123@qq.com'), (2,'战三','男','13232@11111');//添加了两个战三 是重复的所以会报错 错误类型:Duplicate entry '战三' for key 'vip2.name'
-
联合唯一
name 和 email 两个字段联合起来具有唯一性
类似于名字可以一样但是身份证号码不能是一样的
create table vip3( id int not null, name varchar(5), email varchar(20), unique(name,email) ); insert into vip3(id,name,email) values (1,'战三','123@qq.com'), (4,'战三','124@qq.com');
5.3 主键约束 (primary key) 重要
什么是主键:主键值就是一条记录的唯一标识 相当于人身份证号
相关的术语:
主键约束:就是一种约束
主键字段:该字段上添加了主键约束,这样的字段就叫做主键字段
主键值:主键字段中的每一个值叫做主键值
主键的特征:不能为空同时具有唯一性如果一个字段同时被not null 和unique同时约束的话就默认为主键约束
多个字段联合起来添加主键叫做复合主键 在开发中不建议使用复合主键
一张表主键约束只能有一个,主键值一般都是定长的建议使用int bigint char
任何一张表都应该有主键 没有主键的话就是一张废表
-
给表加上主键约束
//使用列级约束添加约束 create table vip4( id int primary key, name varchar(5) unique ); //表级约束添加主键,复合主键 create table vip5( id int, name varchar(4), primary key(id,name) ); insert into vip4(id,name) values(1,'战三'),(2,'张三'),(2,'wang');//为空和重复都会报错
-
自然主键&&业务主键
主键值是一个自然数,跟业务没有关系
业务主键:主键和业务紧密联合 比如银行卡号 不建议使用业务主键
-
自动维护主键
auto_increment 自增 从1 开始以1自增
drop table if exists vip4;//删除表 create table vip6( id int primary key auto_increment, name varchar(4) ); insert into vip6(name) values('张三'); insert into vip6(name) values('张三'); insert into vip6(name) values('张三'); //主键会自己添加
5.4 外键约束(foreign key)
相关术语:
外键约束:一种约束(foreign key)
外键字段:添加了外键约束的字段
外键值:外键字段中的一个值
创建表的时候有一个子表 一个父表
-
创建一个学生信息表(子表) 和 班级表(父表)
create table class( classid int primary key, classname varchar(255) ); create table student( studentid int primary key auto_increment, name varchar(255), cid int, foreign key(cid) references class(classid) ); //像两个表中添加数据 insert into class(classid,classname) values (1,'计算机一班'), (2,'计算机二班'); insert into student(name,cid) values ('张三',1),('李四',2),('王五',1),('赵六',2);
5.5 检查约束 (check)
6.存储引擎
了解就行 不了解也行 看心情
1、MyISAM引擎
这种引擎是mysql最早提供的,这种引擎又可以分为静态MyISAM、动态MyISAM 和压缩MyISAM三种:
(1)静态MyISAM:如果数据表中的各数据列的长度都是预先固定好的,服务器将自动选择这种表类型。因为数据表中每一条记录所占用的空间都是一样的,所以这种表存取和更新的效率非常高。当数据受损时,恢复工作也比较容易做。
(2)动态MyISAM:如果数据表中出现varchar、xxxtext或xxxBLOB字段时,服务器将自动选择这种表类型。相对于静态MyISAM,这种表存储空间比较小,但由于每条记录的长度不一,所以多次修改数据后,数据表中的数据就可能离散的存储在内存中,进而导致执行效率下降。同时,内存中也可能会出现很多碎片。因此,这种类型的表要经常用optimize table 命令或优化工具来进行碎片整理。
(3)压缩MyISAM:以上说到的两种类型的表都可以用myisamchk工具压缩。这种类型的表进一步减小了占用的存储,但是这种表压缩之后不能再被修改。另外,因为是压缩数据,所以这种表在读取的时候要先时行解压缩。
但是,不管是何种MyISAM表,目前它都不支持事务,行级锁和外键约束的功能。
2、MyISAM Merge引擎
这种类型是MyISAM类型的一种变种。合并表是将几个相同的MyISAM表合并为一个虚表。常应用于日志和数据仓库。
3、InnoDB引擎
InnoDB表类型可以看作是对MyISAM的进一步更新产品,它提供了事务、行级锁机制和外键约束的功能。
4、memory(heap)引擎
这种类型的数据表只存在于内存中。它使用散列索引,所以数据的存取速度非常快。因为是存在于内存中,所以这种类型常应用于临时表中。
5、archive引擎
这种类型只支持select 和 insert语句,而且不支持索引。常应用于日志记录和聚合分析方面。
7.事务(重要)
事务就是一个完整的业务逻辑
本质上就是多条dml语句同时或者同时失败
什么是一个完整的业务逻辑 例如转账:a向b转100元 1.a账户减少100,2b账户增加100这就是一个简单的业务逻辑,上面的操作必须保证要么同时成功要么同时失败,不能在细分了。
只有dml语句跟事务有关系,insert delete update只有这三个是设计到事务其他的就不用管
在事务的执行过程中,每一条dml语句都会记录到事务性活动日志文件中,在事务的执行过程中我们可以提交事务也可以回滚事务
-
提交事务
清空事务性活动的日志文件,将数据全部持久化到数据库表中
提交事务标志着事务的结束,而且是一种全部成功的结束
//添加数据后、 insert into vip values(1,'ha'); //提交 commit;
-
回滚事务
rollback;
将之前的所有的dml操作全部撤销,清空事务性活动的日志文件,回滚事务结束意味着事务的结束 而且是一种全部都失败的结束。
//首先开启事务回滚机制 start transaclion; //然后使用dml语句来增删改 //事务回滚 rollback; //完成事务回滚
-
mysql是有自动提交机制的,每运行一条dml语句就进行一次提交,使用start transaction命令来关闭自动提交机制。
-
四个特性
A.原子性:事务是最小的工作单元,不可以在分
C.一致性:所有的事务要求在同一个事务当中,所有的事务必须同时成功,或者同时失败,以保证事务的一致性
I.隔离性 A事务和B事务之间有一定的隔离 来保证安全性
D.持久性:事务最终结束的一个保障,事务提交就相当于把没有保存到硬盘上的数据保存到硬盘上。
8.索引(index)
在mysql中主键上和unique 上都会自动添加索引
索引是在数据库的表的字段上添加的,是为了提高查询效率而存在的一种机制,一张表的一个字段可以添加索引,当然连接起来也可以添加索引,索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。
如果没有在字段上添加索引,在查找字段的时候就会一条一条的找,全表扫描,效率特别慢,索引相当于一本书的目录,可以缩小扫描范围,就是扫描某个区间。
-
索引的实现原理
B+树
8.1创建索引
什么条件下回添加索引
1,数据量庞大
2,数据经常被调用,经常出现在where后面
3,该字段有很少的dml语句
//创建索引 create index ename_index on emp(ename); 给emp表的ename添加索引,起名ename_index
8.2删除索引
drop index 索引名称 on 表名;
drop index emp_ename_index on emp; //将emp表上的emp_ename_index 索引删除
8.3 查看索引
explain select *from 表名 where 条件;
explain select *from emp; //查看emp表上有没有添加索引
8.4 索引失效
p118-p122
8.5 索引的分类
-
单一索引
一个字段上添加索引
-
复合索引
两个或多个字段上添加索引
-
主键索引
在主键上添加索引
-
唯一性索引
唯一性比较弱的字段上添加索引
9.视图(view)
view 站在不同的角度去看待同一份数据
9.1 创建视图对象
create view 视图名 as 查询结果;
视图后面的语句只能是select语句
create view emp_view as select *from vip;
9.2 删除视图对象
drop view 视图名;
drop view emp_view;
9.3 对视图对象的增删改查
对视图对象的增删改查 会导致原表的数据被修改
-
查询视图
select *from 视图名;
select *From vip_view;
-
对视图进行插入
insert into 视图名(字段1.字段2.字段3....) values (值1,值2,值3);
insert into vip_view(id,name) values(4,'wa'); //对视图插入数据 原表的数据也会被改变 也会被插入一条数据
-
面向视图删除
delete from 视图名;
delete from vip_view; //原表也会被删除
-
面向视图更新
update 视图名 set 改的东西 where 条件l
update vip2_view set
10.DBA的常用命令
10.1 新建用户
11 存储过程
creata table cunchu( id int primary key, name varchar(20), sex varchar(1) ); insert into cunchu(id,name,sex) values(001,'张三','男'); insert into cunchu(id,name,sex) values(002,'张四','女'); insert into cunchu(id,name,sex) values(003,'张五','男'); ##创建存储过程 ##更改默认结束语句为$$ delimiter $$ ##判断存储过程是否存在 存在的话就删除 drop procedure if exists pro_dome1; ##创建存储过程 ## (in 输入参数 参数类型) create Procedure pro_dome(in v_id int) begin select *from cunchu where id=v_id; end $$ ## 默认结束符改回来 delimiter; ## 调用存储过程 call Pro_dome(001);