目录
1.4 给查询的列名使用as关键字起别名。(注意:可以去掉as,但注意不要添加 ',')
2.1 什么是条件查询:不是将表中所有数据都查出来,是查询出来符合条件的。
语法格式:select 字段1,字段2,字段3 ··· from 表名 where 条件;
2.2.8 between ... and ... 两个值之间,等同于 >= and <=
2.2.9 is null 为 null(is not null 不为空)
4.2.3 substr 取子串(substr(被截取的字符串, 起始下标, 截取的长段))
4.2.11 case...when...then...when...then...else...end
6.6 使用having可以对分完组之后的数据进一步过滤。(注意:having不能单独使用,having不能代替where,having必须和group by联合使用。)
8.3 当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数是两张表条数的乘积,这种现象被称为:笛卡尔积现象(数学现象)。
11.1 limit将查询结果集的一部分取出来,通常使用在分页查询当中。
11.3 注意:mysql当中limit在order by之后执行
3、DDL(数据定义语言):凡是带有create、drop、alter的都是
一、SQL 语句
1、DQL(数据查询语言)
带有select关键字的
1.1 查询一个字段
select 字段名 from 表名;
例如:查询一个名字?
select dname from dept;
1.2 查询两个字段,或者多个字段
使用逗号隔开 ','
例如:查询部门编号和部门名?
select deptno,dname from dept;
1.3 查询所有字段
第一种方法:可以把每一个字段都写上
select a,b,c,d... from tablenames;
第二种方法:可以使用 * (缺点: 1.效率低;2.可读性差)
select * from dept;
1.4 给查询的列名使用as关键字起别名。(注意:可以去掉as,但注意不要添加 ',')
select deptno,dname as deptname from dept;
注意:只是将显示的查询结果列名显为deptname,原列表名还是叫:dname
记住:select语句是永远都不会进行修改操作的(因为只负责查询)
假设起别名的时候,别名里面有空格?
select deptno,dname as dept name from dept;
DBMS看到上面的语句,进行SQL语句的编译,不符合语法,编译报错。怎么解决?
select deptno,dname as 'dept name' from dept;
select deptno,dname as "dept name" from dept;
注意:在所以的数据库当中,字符串同一使用单引号括起来,单引号是标准,双引号在oracle数据库中用不了,但在mysql中可以使用。当别名为中文,用单引号''括起来
2.条件查询
2.1 什么是条件查询:不是将表中所有数据都查出来,是查询出来符合条件的。
语法格式:
select 字段1,字段2,字段3 ··· from 表名 where 条件;
2.2 都有哪些条件?
2.2.1= 等于
查询薪资等于800的员工姓名和编号?
select emno,ename from emp where sal =800;
2.2.3 !=或<>
查询薪资不等于800的员工姓名和编号?
select emno,ename from emp where sal != 800;
select emno,ename from emp where sal <> 800; // 小于号和大于号组成的不等号
2.2.4 < 小于
2.2.5 <= 小于等于
2.2.6 >大于
2.2.7 >=大于等于
2.2.8 between ... and ... 两个值之间,等同于 >= and <=
查询薪资在2450到3000的员工和编号?
select emno,ename,sal from emp sal between 2450 and 3000;
select emno,ename,sla from emp sla >= 2450 and <= 3000;
注意:使用between and 的时候,必须遵循左小右大。
between and 是闭区间,包括两端的值。
2.2.9 is null 为 null(is not null 不为空)
查询哪些员工的津贴为null?
select emno,ename,sal ,comm from emp where comm is null;
注意:在数据库中的null不能使用等号进行衡量。需要使用is null。因为在数据库中null代表什么也没有,它不是一个值,所以不能使用等号衡量。
查询哪些员工的津贴不为null?
select emno,ename,sal,comm from emp where comm is not null;
2.2.10 and (并且)
查询工作岗位是MANAGER并且工资大于2500的员工信息?
select emno,ename,job,sal from emp where job = 'MANAGER' and sal = 2500;
2.2.11 or(或者)
查询工作岗位MANAGER和SALESMAN的员工?
select emno,ename,job from emp where job = 'MANANGER' or job = 'SALESMAN';
注意:and优先级比or高
查询工资大于2500.,并且部门编号为10或20部门的员工?
select * from emp where sal > 2500 and (deptno = 20
or deptno = 10);
2.2.12 in 包含 (相当于多个 or)
查询工作岗位MANAGER和SALESMAN的员工?
select emno,ename,job from emp where job = 'MANANGER' or job = 'SALESMAN';
select emno,ename,job from emp where job in( 'MANANGER','SALESMAN');
查询薪资是800和3000,5000的员工信息?
select * from emp where sal = 800 or sal = 5000 or sal = 3000;
select * from emp where sla in (800, 3000, 5000); // not in(表示不在这几个值中的数据)。
2.2.13 like(模糊查询,支持%或下划线_匹配)
%匹配任意多个字符;
_匹配一个字符。
找出名字中含有o的员工信息?
select * from emp where ename like '%o%';
找出名字以T结尾的?
select ename from emp where ename like '%T';
找出名字以K开始的?
select ename from emp where ename like 'K%';
找出第二个字母是A的?
select ename from emp where ename like '_A%';
找出第三个字母是R的?
select ename from emp where ename like '__R%';
找出明总中含有"_"的?
select ename from emp where ename like '%_%'; // 错误
select ename from emp where ename like '%\_%'; // 正确 '\'为转义字符。
3.排序
3.1 查询所有员工薪资,然后进行排序?
select ename,sal from emp order by sal; // 默认为升序!
3.2 怎么降序?
select ename,sal from emp order by sal desc;
3.3 指定升序?
select ename,sal from emp order by sal asc;
3.4 可以两个字段排序吗?或者说按照多个字段排序?
查询员工名字和薪资,要求按照薪资升序,员工薪资一样的话,再按照名字升序排序。
select ename, sal from emp order by sal asc, ename asc; // sal在前,起主导地位,只有sal相等的时候,才会考虑启用ename排序。
3.5 了解:按照字段的位置排序
select ename,sal from emp order by 2; // 按照查询结果的第二列sal排序。
找出工资在1250到3000之间的员工信息,要求按照薪资降序排列?
select * from emp where sal between 1250 and 3000 order by sal desc;
4. 数据处理函数
4.1 数据处理函数又称为单行处理函数
单行处理函数的特点:一个输出对于一个输出,和单行处理函数相对的是:多行处理函数。(多行处理函数特点:多个输入,对于一个输出)
4.2 单行处理函数常见的有哪些?
4.2.1 lower 转换小写
select lower(ename) from emp;
4.2.2 upper 转换大写
select ename from emp where ename like 'A%';
select upper(ename) from emp;
4.2.3 substr 取子串(substr(被截取的字符串, 起始下标, 截取的长段))
select substr(ename, 1, 1) as ename from emp;
注意:起始下标从1开始,没有0.
找出员工名字第一个字母是A的员工信息?
select ename from emp where ename like 'A%';
select ename from emp where substr(ename, 1, 1) = 'A';
找出员工名字前两个字?
select substr(ename,1,2) as sub from emp;
4.2.4 concat函数进行字符串的拼接
select concat(empno, ename) from emp;
4,2.5 首字母大写
select upper(substr(name, 1, 1)) from emp;
select substr(name, 2, length(name) -1) from emp;
select concat(upper(substr(name, 1, 1)), substr(name, 2, length(name) -1) )from emp;
4.2.6 length 取长度
select length(ename) enamelength from emp;
4.2.7 trim 去空格
select * from emp where ename = trim(' KING');
4.2.8 round 四舍五入
select round(1236.567, 0) as result from emp;
结果返回: 14条1237(0表示保留整数)
select round(1236.567, 1) as result from emp;
结果返回:14条1236.6(1表示保留一位小数)
select round(1236.567, -1) as result from emp; //保留十位(1240)
select round(1236.567, -2) as result from emp; //保留百位(1200)
select 'abc' as bieming from emp; //正确,select后面直接跟 "字面量/字面值"
select 1000 as num from emp; //正确,select后面直接跟 "字面量/字面值"
select adc as bieming from emp; // 错误,因为会把abc当成一个字段的名字,去emp表中找abc字段。
结论:select后面可以跟某个表的字段名(可以等同看作变量名),也可以跟字面量/字面值(数据)。
4.2.9 rand() 生成随机数
select rand() from emp; //1以内的随机数
select round(rand()*100, 0) from emp; //100以内的随机数
4.2.10 ifnull 可以将null转换成一个具体值
ifnull是空处理函数,只要有null参与的数学运算,最终结果就是null。
select ename,sal + comm as salcomm from emp;
计算每一个员工的年薪?
年薪 = (月薪 + 月补助)* 12
select ename, (sal + comm) * 12 as yearsal from emp;
只要有null参与的数学运算,最终结果就是null。为了避免这个现象,需要使用infull函数。
ifnull函数用法:ifnull(数据, 被当作哪个值)
如果"数据"为null的时候,把这个数据当作哪个值。
补助为null的时候,把补助当作0
select ename, (sal + ifunll(comm, 0)) * 12 as yearsal from emp;
4.2.11 case...when...then...when...then...else...end
当员工的工作岗位是MANANGER的时候,工资上调10%,当工作岗位是SALESMAN的时候,工资上调50%。其他正常。(注意:不修改数据库,只是将查询结果显示为工资上调)
select ename, job,(case job when 'MANANGER' then sal*1.1 when 'SALESMAN' then sal*1.5 else sal end) as newjob from emp;
5. 分组函数(多行处理函数)
特点:输入多行,最终输出一行。
5个:
count 计数
sum 求和
avg 平均值
min 最小值
max 最大值
注意:
第一点:分组函数在使用的时候必须先进行分组,然后才能用。
如果你没有对数据进行分组,整张表默认为一组。
第二点:分组函数自动忽略null,你不需要提前对null进行处理。
第三点:count(具体字段):表示统计该字段下所有不为null的元素的总数。
count(*): 统计表当中的总行数。(只要有一行数据count则++)
因为每一行记录不可能都为null,一行数据中有一列不为null,则这行数据就是有效的
第四点:分组函数不能直接使用在where句子中。
找出比最低工资高的员工信息。
第五点: 所有的分组函数可以组合在一起用。
select sum(sal),min(sal),max(sal),avg(sal),count(*) from emp;
5.1 找出最高工资
select max(sal)from emp;
5.2 找出最低工资
select min(sal) from emp;
5.3 计算工资和
select sum(sal) from emp;
5.4 计算平均工资
select avg(sal) from emp;
5.5 计算工作数量
select count(ename) from emp;
6.分组查询(******)
6.1 什么是分组查询?
在实际的应用中,可能有这样的需求,需要先进行分组,然后对每一组的数据进行操作。这个时候需要使用分组查询,怎么进行分组查询?
select...from...group by...
6.2 将之前的关键字全部组合在一起
select...from...where...group by...order by...
以上关键字的顺序不能颠倒,执行顺序是:
from
where
group by
select
order by
为什么分组函数不能直接使用在where后面?
select ename,sal from emp where sal > min(sal); //报错。
因为分组函数在使用的时候必须先分组之后才能使用。where执行的时候,还没有分组,所以where后面不能出现分组函数。
6.3 找出每一工作岗位的工资和?
实现思路:按照工作岗位分组,然后对工资求和。
select job,sum(sal) from emp group by job;
以上这个语句的执行顺序:
先从emp表中查询数据,根据job字段进行分组,然后对每一组进行sum(sal).
6.4 找出每一个部门的最高薪资?
实现思路是什么?
按照部门编号分组,求每一组的最大值。
select deptno,max(sal) from emp group by deptno;
6.5 找出每一个部门,不同工作岗位的最高薪资?
select deptno,job,max(sal) from emp group by deptno,job;
6.6 使用having可以对分完组之后的数据进一步过滤。(注意:having不能单独使用,having不能代替where,having必须和group by联合使用。)
找出每一个部门最高薪资,要求显示最高薪资大于3000的?
select deptno,max(sal) > 3000 from emp group by deptno;
select deptno,max(sal) from emp where sal>3000 group by deptno;
select deptno,max(sal) from emp group by deptno having max(sal)>3000;
优先策略::
where和having,优先选择where,where实在完成不了了,再选择having。
6.7 where没有办法的?
找出每一个部门平均薪资,要求显示平均薪资高于2500的?
第一步:找出每一个部门的平均薪资
select deptno,max(sal) from emp group by deptno;
第二步:要求显示平均薪资高于2500的
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2500;
大总结:(单表查询)
select...from...where..group by...having...order by...
以上关键字只能按照这个顺序来,不能颠倒。
from
where
group by
having
select
order by
从某一张表中查询数据,先经过where条件筛选出有价值的数据;
对这些有价值的数据进行分组;
分组后可以使用having继续筛选;
select查询出来;
最后排序输出。
找出每一岗位的平均薪资,要求显示平均薪资大于1500的,除MANAGER岗位之外,要求按照平均薪资降序排列?
select job,avg(sal) from emp where job<> 'MANAGER' group by job having avg(sal) > 1500 order by avg(sal) desc;
7.去除重复记录(distinct)
注意:原表数据不会被修改,只是查询结果去重。
select distinct job from emp;
distinct出现在job,deptno两个字段前,表示两个字段联合起来去重。
select distinct job,deptno from emp;
统计工作岗位的数量?
select count(distinct job) from emp;
8.连接查询
8.1 什么是连接查询?
从一个表中单独查询,称为单表查询。
emp表和dept表联合起来查询数据,从emp表中取员工名字,从dept表中取部门名字。这种跨表查询,多张表联合起来查询数据,被称为连接查询。
8.2 连接查询的分类
根据语法的年代分类:
SQL92:1992年出现的语法。
SQL99:1999年出现的语法。
根据表连接的方式分类:
内连接:
等值连接
非等值连接
自连接
外连接:
左外连接(左连接)
右外连接(右连接)
全连接:
8.3 当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数是两张表条数的乘积,这种现象被称为:笛卡尔积现象(数学现象)。
注意:通过笛卡尔积的现象得出,表的连接次数越多效率越低,尽量避免表的连接次数
如何避免笛卡尔积现象?
连接时加条件,满足这个条件的记录被筛选出来!
select ename,dname from emp, dept where emp.deptno = dept.deptno;
// 表起别名
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
思考:最终查询的结果条数是14条,但是匹配的过程中,匹配的次数减少了吗?
答:没有。
8.4 内连接之等值连接
查询每一个员工所在部门名称,并显示员工名和部门名(相当于emp e和dept d表进行连接。条件是:e.deptno = d.deptno
SQL92语法:
select e.ename, d.dname from emp e, dept d where e.deptno = d.deptno;
SQL99语法: //inner可以省略
select e.ename, d.dname from emp e inner join dept d on e.deptno = d.deptno;
SQL92的缺点:
结构不清晰,表的连接条件和后期进一步筛选的条件,都放在了where后面。
SQL99优点:
表连接的条件是独立的,连接之后,如果还需要进一步筛选,再往后继续添加where。
SQL99语法:select...from a join b on a和b的连接条件 where 筛选条件
8.5 内连接之非等值连接
找出每一个员工的薪资等级,要求显示员工名、薪资、薪资等级?
select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal; //条件不是一个等量关系,称为非等值连接。
8.6 内连接之自连接
查询员工的上级领导,要求显示员工名和对应的领导名?
select a.ename as '员工名', b.ename as '领导名' from emp a join a.mgr = b.empno;
技巧:一张表看成两张表。
8.7. 外连接(两表存在主次关系)
右外连接:(outer可以省略)
select e.ename, d.dname from emp e right outer join dept d on e.deptno = d.deptno; // right表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
思考:外连接的查询结果条数一定是 >= 内连接的查询条数?
正确。
查询每一个员工的上级领导,要求显示所有员工的名字和领导名?
select a.ename as '员工名',b.ename as '领导名' from emp a left join emp b on a.mgr = b.empno;
8.8 三张表,四张表怎么连接?
语法:
select...
from a
join b
on a和b的连接条件
join c
on a和c的连接条件
join d
on a和d的连接条件
找出每一员工的部门名称以及工资等级,要求显示员工名、部门名、薪资、薪资等级?
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 s.hisal;
找出每个员工的部门名称以及工资等级,还有上级领导,要求显示员工名、领导名、部门名、薪资、薪资等级?
select e.ename,e.sal,d.dname,s.grade,l.ename
from emp e
join dept d
on e.deptno = d.deptno
join salgarde s
on e.sal between s.losal and s.hisal
left join
emp l
on e.mgr = l.empno;
9 子查询
9.1 什么是子查询?
select语句中嵌套select语句,被嵌套的select语句被称为子查询。
9.2 子查询可以出现在哪里?
select
..(select).
from
..(select).
where
..(select).
9.3 where语句中的子查询
找出比最低工资高的员工姓名和工资?
实现思路:
第一步:查询最低工资是多少?
select min(sal) from emp;
第二步:找出>800的
select ename,sal from emp where sal > 800;
第三步:合并
select ename, sal from emp where sal > (select min(sal) from emp);
9.4 from子句的子查询
技巧:from后面的子查询,可以将子查询的查询结果当成一张临时表。
找出每一个岗位的平均工资的薪资等级。
实现思路:
第一步:找出每一个岗位的平均工资(按照岗位的分组求平均值)
select job, avg(sal) from emp group by job;
第二步:将上面的查询结果当成一张真实存在的表。t表
select * from salgrade;s表
第三步:合并
select t.*,s.grade
from (select job,avg(sal) as avgsal from emp group by job) as t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
10. union合并查询结果集
查询工作岗位是MANAGER和SALESMAN的员工?
select ename,job from emp where job = 'MANANGER' or job = 'SALESMAN';
select ename,job from emp where job in ('MANANGER', 'SALESMAN');
select ename,job from emp where job = 'MANANGER'
union
select ename,job from emp where job = 'SALESMAN';
union的效率要高一些。对于表连接来说,每连接一次新表,则匹配的次数满足笛卡尔积,成倍的翻。。。
但是union可以减少匹配的次数。在减少匹配次数的情况下,还可以完成两个结果集的拼接。
注意事项:
union在进行结果集合并的时候,要求两个结果集的列数相同。
11. limit
11.1 limit将查询结果集的一部分取出来,通常使用在分页查询当中。
分页的作用是为提高用户的体验,因为一次全部都查出来,用户体验差,可以是用户一页一页翻出来看。
11.2 limit怎么用?
完整用法:limit startIndex,length
startIndex是从起始下标,length是长度。起始下标从0开始。
缺省用法:limit 5;这是取前五。
按照薪资升序,取出排名在前五名的员工?
select ename,sal
from emp
order by
sal desc
limit 5'; //取前五
select ename,sal
from emp
order by sal desc
limit 0,5;
11.3 注意:mysql当中limit在order by之后执行
取出工资排名在[3-5]名的员工名?
select ename,sal
from emp
order by
sal desc
limit 2,3;
2表示起始位置从下标2开始,就是第三条记录
3表示长度。
11.4 分页
每一页显示3条记录
第一页:limit 0,3 [0 1 2]
第二页:limit 3,3 [3 4 5]
第三页:limit 6,3 [6 7 8]
第四页:limit 9,3 [9 10 11]
每页显示pagesize条记录
第pageNo页:limit(pageNo - 1)* pageSize , pageSize
public static void main(String[] args) {
// 用户提交过来一个页码,以及每页显示的记录条数
int pageNo = 5; //第五页
int pageSize = 10; //每一页显示10条信息
int startIndex = (pageNo - 1) * pageSize;
String sql = "select ...limiit" + satrtIndex + ", " + pageSize;
}
记公式:
limit (pageNo - 1) * pageSize, pageSize
12.关于DQL语句的大总结:
select
...
from
...
where
...
group by
...
having
...
order by
...
limit
...
执行顺序:
1.from
2.where
3.group by
4.having
5.select
6.order by
7.limit...
13、表的创建(建表)
13.1 建表的语法格式
(建表属于DDL语句,DDL包括:create drop alter)
create table 表名(字段名1 数据类型,字段名2 数据类型,字段名3 数据类型);
表名:建议以t_或者 tbl_开始,可读性强。见名知意。
字段名:见明知意。
表名和字段名都属于标识符。
13.2 关于mysql中的数据类型
常见的数据类型:
varchar(最长255)
可变长度的字符串;
比较智能,节省空间;
会根据实际的数据长度动态分配空间。
优点:节省空间。
缺点:需要动态分配空间,速度慢。
char(最长255)
定长度字符串;
分配固定长度的空间去储存数据。
优点:不需要动态分配空间,速度快。
缺点:使用不当可能会导致空间的浪费。
varchar和char应该如何选择?
性别字段:char
姓名字段:varchar
int(最长11)
数字中的整数型。等同于java的int
bigint
数字中的长整型。等同于java中的long
float
单精度浮点型数据
double
双精度浮点型数据
date
短日期类型
datetime
长日期类型
clob(Character Large OBject)
字符大对象;
最多可以储存4G的字符串;
比如:存储一篇文章,存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
blob(Binary Large OBject)
二进制大对象;
专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,需要使用IO流才行。
13.3 创建一个学生表?
学号、姓名、年龄、性别、邮箱地址
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| no | int(11) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | NULL | |
| age | int(3) | YES | | NULL | |
| email | varchar(255) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
删除表:
drop table t_student; // 当这张表不存在的时候会报错!
drop table if exists t_student; // 如果这张表存在的话,删除。
13.4 插入语句insert(DML)
语法格式:insert into 表名(字段名1,字段名2,字段名3...)values(值1,值2,值3);
注意:字段名和值要一一对应;
insert语句中的"字段名"可以省略;
前面的字段名省略的话,等于都写上了,所有值也要全部写上且不能颠倒顺序。
什么是一一对应?
数量要对应,数据类型要对应。
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
insert into t_student(no,name,sex,age,email) values(1,'lisi','f',20,'lisi@123.com');
注意:insert语句但凡是执行成功了,那么必然会多一条记录。没有给其它字段指定值的话,默认为null。
指定默认值:
create table t_student(
no int,
name varchar(32),
sex char(1) default 'm',
age int(3),
email varchar(255)
);
13.5 insert插入日期
数字格式化:format
格式化数字:format(数字,'格式')
select ename,format(sal,'$999,999') as sal from emp; //千分位
str_to_date:将字符串varchar类型转换成date类型。
语法格式:
str_to_date('字符串日期','日期格式')
mysql的日期格式:
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
date_format:将date类型转换成具有一定格式的varchar字符串类型。这个函数可以将日期类型转换成特定格式的字符串。
语法格式:
dtae_format(日期类型数据,'日期格式')
例如:select id,name,date_fromat(birth, '%m/%d/%Y') as birth from t_user;
注意:数据库中的有一条命名规范:
所有的标识符全部小写,单词与单词之间使用下划线进行衔接。
13.6 date和datetime两个类型的去别?
date是短日期:只包括年月日信息。
datetime是长日期:包括年月日时分秒信息。
例如:
drop table if exists t_user;
create table t_user(
id int;
name varchar(32),
birth date,
create_time datetime
);
备注:id是整数
name是字符串
birth是短日期
create_time是这条记录的创建时间:长日期类型
mysql短日期默认格式:%Y-%m-%d
mysql长日期默认格式:%Y-%m-%d %h:%i:%s
insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01','2023-8-23 12:23:50');
在MySQL当中如何获取系统当前时间?
now()函数,并且获取的时间带有:时分秒信息。
insert into t_user(id,name,birth,create_time) values(1,'zhangsan','1990-10-01',now());
13.7 修改update(DML)
语法格式:
update 表名 set 字段名1 = 值1.字段名2=值2.字段名3=值3...where 条件;
例如:update t_user set name = 'jack',birth = '2000-10-11' where id = 2;
注意:没有条件限制会导致所有数据全部更新。
例如:update t_user set name = 'abc';
13.8 删除数据 delete(DML)
语法格式:
delete from 表名 where 条件;
例如:delete from t_user where id = 2;
注意:没有条件,整张表的数据会全部删除。
例如:delete from t_user; //删除所有
13.9 insert语句一次性插入多条记录
语法格式:
insert into t_user(字段名1, 字段名2,字段名3) values(),(),(),();
例如:insert into t_user(id,name,birth,create_time) values (1, 'zhangsan', '1990-10-11', now()), (2, 'lisi', '1990-10-11', now()), (3, 'wangwu', '1990-10-11', now());
14、快速创建表
create table emp2 as select * from emp;
select table mytable as select empno,ename from emp where job = 'MANAGER';
原理:将一个查询结果当作一张表新建,实现表的快速复制,使得新表创建出来同时将表的数据也复制过来。
15、快速删除表中数据
15.1 delete语句删除dept_bak表中的数据
delete from dept_bak; //这种删除数据的方式比较慢。
delete语句删除数据的原理?
表中数据被删除,但这个数据在硬盘上的真实存储空间不会被释放。
优点:删除效率低。
缺点:支持回滚,可恢复数据。(rollback)
15.2 truncate语句删除数据
truncate table dept_bak;
truncate语句删除数据的原理?
表被一次截断,物理删除。
优点:快速。
缺点:不支持回滚。
16、约束
16.1 什么是约束?
在创建表的时候,可以个表中的字段加上一些约束,来保证表的数据的完整性、有效性。
约束的作用:保证表中数据的有效性。
16.2 约束包括哪些?
非空约束:not null
唯一性约束:unique
主键约束:primary key(简称PK)
外键约束:foreign key(简称FK)
检查约束:check(mysql不支持,Oracle支持)
16.2.1 非空约束:not null
非空约束not null约束的字段不能为null。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null //not null 没有表级约束,只有列级约束
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
insert into t_vip(id,name) values(3);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
16.2.2 唯一性约束:unique
唯一性约束unique玉约束的字段不能重复,但可以为null。
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
不能重复:
insert into t_vip(id,name,email) values(3,'lisi','wangwu@123.com');
ERROR 1062 (23000): Duplicate entry 'lisi' for key 'name'
可以为null:
insert into t_vip(id) values(4);
Query OK, 1 row affected (0.01 sec)
新需求:使name和email两个字段联合起来具有唯一性。
c //约束没有添加在列的后面,这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'lisi','lisi@123.com');
insert into t_vip(id,name,email) values(3,'lisi','lisi@sina.com');
什么时候使用表级约束?
需要给多个字段联合起来添加某一个约束的时候
unique 和 not null 可以联合
例如:
drop table if exists t_vip;
create table t_vip(
id int
name varchar(255) not null unique
);
desc t_vip;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(255) | NO | PRI | NULL | |
+-------+--------------+------+-----+---------+-------+
注意:
mysql中,如果一个字段同时被not null和unique约束的话,该字段自动变成主键字段。(Oracle中不一样)
16.2.3 主键约束(primary key,简称PK)
什么是主键?其作用?
主键值是每一行记录的唯一标识。
主键值是每一行记录的身份证号。
记住:任何一张表都应该有主键,没有主键,表无效。
主键的特征:not null + unique(主键值不能是null,同时也不能重复)。
怎么给一张表添加主键的约束?
drop table if exists t_vip;
create table t_vip(
id int primary kry,
name varchar(255),
);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
insert into t_vip(id,name) values(2,'wangwu');
给多个字段联合起来添加约束?
drop table if exists t_vip;
create table t_vip(
id int primary kry,
email varchar(255),
name varchar(255),
primary key(name,id), //id和name联合起来作主键:复合主键
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(1,'lisi','lisi@123.com');
注意:一张表,主键约束只能添加一个(主键只能有一个)。
主键值建议使用:
int
bigint
char等类型。
不建议使用:varchar来作主键。主键值一般都是数字,一般都是定长。
主键除了单一主键和复合主键以外,还可以这样分类:
自然主键:主键值是一个自然数和业务没有关系。
业务主键:主键值和业务紧密关联,例如以银行号做主键。
在实际的开发中使用自然主键和业务主键哪个多?
自然主键使用比较多,因为主键只要做到不重复就行,不需要有意思,
业务主键不好,因为主键一旦和业务挂钩,那么当业务发生变动时,可能会影响到主键值,所以业务主键不建议使用。
mysql中。有一种机制可以帮助我们自主维护主键值?
drop table if exists t_vip;
create table t_vip(
id int primary key auto_increment,
name varchar(255),
);
insert into t_vip(name) values('zhnagsan');
insert into t_vip(name) values('zhnagsan');
insert into t_vip(name) values('zhnagsan');
insert into t_vip(name) values('zhnagsan');
select * from t_vip;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | zhangsan |
| 3 | zhangsan |
| 4 | zhangsan |
+----+----------+
16.2.4 外键约束(foregin key 简称FK)
外键约束:一种约束。
外键字段:该字段上添加了外键约束。
外键值:外键字段当中的每一个值。
创建表的顺序?
先创建父表,再建子表。
删除数据的顺序?
先删除子表,再删除父表。
插入数据的顺序?
先插入父表,再插入子表。
思考:子表中外键引用的父表中的某个字段,被引用的这个字段必须时主键吗?
不一定是主键,但至少具有unique约束。
测试:外键可以为null吗?
可以。
17、存储引擎
17.1 什么是存储引擎,作用?
实际引擎是一个表存储/组织数据的方式。
不同的存储引擎,表存储数据的方式不同。
17.2 如何给表添加/指定'存储引擎'呢?
show create table t_student;
| t_vip | CREATE TABLE `t_vip` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
在建表的时候可以在最后小括号的“)”的右边使用:
ENGING来指定存储引擎是:InnoDB;
CHARSET来指定这张表的字符编码方式:utf8。
17.3 怎么查看mysql支持哪些存储引擎呢?
命令:show engines \G
当前版本:select version();
+-----------+
| version() |
+-----------+
| 8.0.16 |
+-----------+
17.4 mysql常见的存储引擎
MyISAM:
使用三个文件表示每一个表:
格式文件:存储表结构的定义(mytable.frm)
数据文件:存储表行的内容(mytable.MYD)
索引文件:存储表上的索引(mytable.MYI)
优势:可被转换为压缩、只读表来节省空间。
缺点:不支持事务机制。安全性低。
InnoDB:
每一个InnoDB表在数据库目录中以.frm格式文件表示;
InnoDB表空间tablespace被用于存储表的内容(表空间是一个逻辑名称);
提供一组用来记录事务性活动的日志文件;
提供全ACID兼容;
多版本和行级锁定;
支持外键及引用的完整性,包括级联删除的更新;
这是默认的存储引擎,同时是一个重量级的存储引擎。支持事务,支持数据库崩溃后自动恢复机制。
最大的特点:支持事务。以保证数据的安全,效率不是很高,不能压缩,不能转换为只读,不能很好的节省空间。
MEMORY(以前叫HEAP引擎):
在数据库目录内,每一个表均以.frm格式的文件表示;
表数据及索引被存储在内存中;
表级锁机制;
不能包含 TEXT 、BLOB字段。
优点:查询效率最高,不需要与内存交互。
缺点:不安全,关机后数据消失,因为数据和索引都是在内存当中。
18、事务(重点)
18.1 什么是事务
一个事务就是一个完整的业务逻辑。是一个最小的工作单元,不可再分。(本质是多条DML语句同时成功,或者同时失败)
18.2 只有DML语句才会有事务。
insert
delete
update
因为只有上面的语句是数据库中数据进行增删改的。
假设所有的业务,只要一条DML语句就能完成,还有必要存在业务机制吗?
需要。
二、DML(数据操作语言):凡是对表当中的数据进行增删改的
insert 增
delete 删
update 改
三、DDL(数据定义语言):凡是带有create、drop、alter的都是
create:新建。等同于增
drop:删除
alter:修改
这个增删改和DML不同,这个主要是对表结构进行操作
四、TCL(事务控制语言)
包括事务提交:commit;事务回滚:rollback;
五、DCL(数据控制语言)
包括(授权:grant;撤销权限:revoke等等。)