关于SQL语句的分类?五种分类
DQL:
数据查询语言(凡是带有select关键字的都是查询语句)
select…
DML:
数据操作语言(凡是对表中数据进行增删改的都是DML)
主要是操作表中的数据。
insert 增
delete 删
update 改
DDL:
数据定义语句(凡是带有create、drop、alter的都是DDL)
主要操作的是表的结构。
create 新建(增)
drop 删除
alter 修改
TCL:
事务控制语言
事务提交:commit
事务回滚:rollback
DCL:
数据控制语言
授权 grant、撤销权限 revoke
条件查询:
条件查询用到where语句,并支持以下运算符:
= 等于
<>或!= 不等于
< 小于
<= 小于等于
> 大于
>= 大于等于
between...and... 两个值之间,等同于 ">= and <="
is null 为null
and 并且
or 或者
in 包含(相当于多个or)(not in :不在这个范围内)
not 可以取非,主要用在 is 或 in 中
like 模糊查询,支持 % 或下划线匹配(%匹配任意多个字符,下划线只匹配一个字符)
单行处理函数常见的有哪些?
特点:输入多行,最终输出多行
lower 转换小写
mysql> select lower(name) as name from t_user;
upper 转换大写
mysql> select upper(name) as name from t_user;
order by 排序(默认参数asc升序)
mysql> select * from t_user order by age;
mysql> select * from t_user order by age asc; //升序
mysql> select * from t_user order by age desc; //降序
substr 取子串(substr(被截取的字符串,起始下标,截取的长度))
length 取长度
concat 字符串拼接
trim 去空格
str_to_date 将字符串转换成日期
dare_format 格式化日期
format 设置千分位
round 四舍五入
rand() 生成随机数
ifnull 可以将null转换成一个具体值
ifnull(NULL,某一个值)
注意:
NULL只要参加运算,最终结果一定是NULL。
为了避免这个现象,需要用ifnull函数。
ifnull函数用法:ifnull(数据,被当作哪个值)
多行处理函数(分组函数)?
特点:输入多行,最终输出一行
count 计数
sum 求和
avg 平均
max 最大
min 最小
注意:
分组函数在使用时必须先进行分组,然后才能使用。
如果没有对数据进行分组,整张表默认为一组。
第一点:
分组函数自动忽略NULL,不需要提前对NULL进行处理
第二点:
count(*) 和 count(具体字段) 有什么区别??
count(*)是统计表中的数据的总行数。(只要有一行数据count则++)
count(具体字段)表示统计当前字段下所有不为NULL的元素总数;
第三点:
分组函数不能够直接用在where子句中。
因为分组函数在使用的时候必须先分组后才能使用。
where执行的时候还没有分组,所以where之后不能出现分组函数。
第四点:
所有的分组函数可以组合起来一起使用。
分组查询:
select...from...group by...
以下关键字的执行顺序:
select...from...where...group by...order by...
1、from
2、where
3、group by
4、select
5、order by
为什么分组函数不能直接使用在where之后???
因为分组函数在使用的时候必须先分组后才能使用。
where执行的时候还没有分组,所以where之后不能出现分组函数。
select sum(sal) from emp;
这个没有分组,为啥sum()函数可以使用呢?
因为select在group by之后执行。
以上语句执行顺序;
from、group by、sum()、select
mysql> select address,sum(age) from t_user group by address;
使用having可以对分完组之后的数据进一部过滤。
having不能单独使用,having不能代替where
优化策略:where和having,优先选择where,where完成不了的再选having。
select
...
from
...
where
...
group by
...
having
...
order by
...
执行顺序:
1、from
2、where
3、group by
4、having
5、select
6、order by
从某中表中查询数据,先经过where条件筛选出有价值的数据,
对这些有价值的数据进行分组,分组之后可以使用having继续筛选。
select查询出来,最后排序输出!
去除重复记录(原表不会被修改,只修改查询结果):
distinct
//只能出现在查询字段之前,表示两个字段联合去重。不能出现在字段之间。
连接查询:
根据表连接方式分类:
- 内连接:
- 等值连接
- 非等值连接
- 自链接
- 外连接:
- 左外连接(左连接)
- 右外连接(右连接)
- 全连接:(不讲)
1、当两张表进行连接查询,没有任何条件限制的时候会发生什么现象???
当两张表进行连接查询,没有任何条件限制的时候,最终查询结果条数,
是两张表的乘积,这种现象被称为笛卡尔积现象。(数学现象)
怎样避免笛卡尔积现象???
连接时增加匹配条件。
注意:通过笛卡尔积现象得出,表的连接次数越多效率越低,应尽量降低表的连接次数。
内连接之等值连接://条件是等值关系
SQL92语法:
select
e.ename,d.dname
from
emp e,dept d
where
e.deptno = d.deptno;
SQL92语法缺点:结构不清晰,表的连接和数据筛选条件都放到了where后面。
SQL99语法:表连接与过滤条件分离
select
e.ename,d.dname
from
emp e
(inner) join //inner 内部的 可以省略
dept d
on
e.deptno = d.deptno;
SQL99语法优点:
表的链接和条件是独立的,连接之后还需要进一步筛选数据,
可以再往后添加where。
内连接之非等值连接:
select
e.ename,s.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
内连接之自连接:一张表看作两张表
例:select
a.ename as '员工名',b.ename as '领导名'
from
emp a
join
emp b
on
a.mgr = b.empno;
外连接:(右外连接)
select
e.name,d.dname
from
emp e
right (outer) join //outer可以省略,带着可读性强;用于区分内连接(inner)和外连接(outer)
dept d
on
e.deptno = d.deptno;
注意:!!!
right代表什么???
表示将join关键字右侧的这张表看作是主表,
主要是为了将这样表中的数据全部查询出来,捎带着关联查询左边的表。
在外连接中,两张表连存在主次关系。
外连接:(左外连接)
select
e.name,d.dname
from
dept d
left join
emp e
on
e.deptno = d.deptno;
带有right的是右外连接,又叫做右连接。
带有left的是左外连接,又叫做左连接。
任何左连接和右连接都可以互换。
思考:外连接的查询结果条数一定是 >= 内连接的查询结果条数???
正确。
多表联查:(三张表、四张表)
语法:
select
...
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件
一条SQL查询语句内连接和外连接可以混合!!!
子查询:
select语句中嵌套select语句,被嵌套的select语句被称为子查询。
子查询可以出现在哪里?
select
..(select)..
from
..(select)..(查询结果看作临时表,并不真实存在)
where
..(select)..
注意:对于select后面的子查询来说,这个子查询只能一次返回一条结果。
union合并查询结果集:
select ename,job from emp where job = 'MANAGER'
union
select ename,job from emp where job = 'SALESMAN'
注意事项:union在进行合并时,要求两个结果集的列数相同。
limit:
将查询结果一部分取出,通常使用在分页查询当中。
完整语法:limit startIndex,length
缺省语法:limit 5;取前5个数据
select
ename,sal
from
emp
order
sal desc
limit
0,5;
分页:
每页现实pageSize条记录
第pageNO页:limit (pageNO - 1) * pageSize , pageSize
创建表、删除表:
设置默认值:
create table t_student(
no int,
name varchar,
sex char(1) default 'm',
age int(3),
email varchar(255)
);
删除:
drop table if exists t_user;
插入数据:(字段与值一一对应)
insert into '表名'(字段名1,...) values (值1,...)
修改数据:
update 表名 set 字段1=值1,字段2=值2...where 条件;
注意:
如果更新语句后没加where条件,则更新整张表。
例:updata t_user set name='abc';
删除数据:
delete from 表名 where 条件;
注意:没有条件,整张表的数据会全部删除。
delete from t_user;
insert插入多条数据:
insert into t_user(id,name,birth,create_time) values
(1,'zhangsan','1998-10-10',now()),
(2,'lisi','1998-10-10',now()),
(3,'wangwu','1990-01-24',now()),
(4,'zhaoliu','1996-09-13',now());
快速创建表(复制):
create table emp2 as select * from emp;
注意:
将一个查询结果当作一张表新建。
这个可以完成表的快速复制。
快速删除数据:
//属于DML语句
delete from 表名; //这种删除数据的方式比较慢!!
delete删除数据的原理:
表中的数据被删除了,但是这个数据在硬盘上的真是存储空间不会被释放!!
这种删除方式缺点:效率低。
这种删除方式优点:支持回滚,数据可以再恢复!!!
truncate语句删除数据的原理:
这种删除效率比较高,表被一次截断,物理删除。
缺点:不支持回滚。
优点:快速。
语法:(属于DDL语句)
truncate table 表名;
对表结构的增删改:
什么是对表结构的修改?
添加字段、删除字段、修改字段。
# 什么是约束?
在创建表的时候,我们可以给表中的字段加上一些约束,来保证这个表中的数据的完整性、有效性!!
约束的作用:保证表中的数据有效。
约束包括哪些?
非空约束 not null
唯一性约束 unique
主键约束 primary key
外键约束 foreign key
检查约束 check(mysql不支持,oracle支持)
唯一性约束:
单个字段分别具有唯一性:(添加在列后面,称为列级约束)
create table t_student(
no int,
name varchar unique,
sex char(1),
age int(3),
email varchar(255) unique
);
使得name和email两个字段联合起来具有唯一性!!!
create table t_student(
no int,
name varchar,
sex char(1),
age int(3),
email varchar(255), //(没有添加在列后面,称为表级约束)
unique(name,email)
);
什么时候使用表级约束??
需要给多个字段联合起来添加某一个约束的时候,需要使用表级约束。
unique 和 not null联用?? 相当于 primary key(主键)
create table 表名(
id int,
name varchar(255) not null unique
);
主键约束:primary key(PK)(表级约束、列级约束都可以)
什么是主键??有什么用??
主键值是每一行记录的唯一标识!!
记住:
任何一张表都应该有主键,没有主键,表无效!!
主键特征:not null + unique (不能为空且不能重复)
例子:
create table t_student(
no int primary key, //列级约束
name varchar,
age int(3),
email varchar(255),
);
create table t_student(
no int,
name varchar,
age int(3),
email varchar(255),
primary key(id,name) //表级约束 复合主键
);
自动维护主键值之主键自增:
create table t_vip (
id int primary key auto_increment, //主键自增
name varchar(255)
);
外键约束:(foreign key,简称:FK)
业务背景:
请设计数据库表,来描述“班级和学生”的信息?
第一种方案:班级和学生存储在一张表中???
t_student
no(pk) name classno classname
----------------------------------------------------------------------------------
1 jack 100 北京市大兴区亦庄镇第二中学高三1班
2 lucy 100 北京市大兴区亦庄镇第二中学高三1班
3 lilei 100 北京市大兴区亦庄镇第二中学高三1班
4 hanmeimei 100 北京市大兴区亦庄镇第二中学高三1班
5 zhangsan 101 北京市大兴区亦庄镇第二中学高三2班
6 lisi 101 北京市大兴区亦庄镇第二中学高三2班
7 wangwu 101 北京市大兴区亦庄镇第二中学高三2班
8 zhaoliu 101 北京市大兴区亦庄镇第二中学高三2班
分析以上方案的缺点:
数据冗余,空间浪费!!!!
这个设计是比较失败的!
第二种方案:班级一张表、学生一张表??
t_class 班级表
classno(pk) classname
------------------------------------------------------
100 北京市大兴区亦庄镇第二中学高三1班
101 北京市大兴区亦庄镇第二中学高三1班
t_student 学生表
no(pk) name cno(FK引用t_class这张表的classno)
----------------------------------------------------------------------
1 jack 100
2 lucy 100
3 lilei 100
4 hanmeimei 100
5 zhangsan 101
6 lisi 101
7 wangwu 101
8 zhaoliu 101
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
classno int primary key,
classname varchar(255)
);
create table t_student(
no int primary key auto_increment,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)
);
当cno字段没有任何约束的时候,可能会导致数据无效。可能出现一个102,但是102班级不存在。
所以为了保证cno字段中的值都是100和101,需要给cno字段添加外键约束。
那么:cno字段就是外键字段。cno字段中的每一个值都是外键值。
注意:
t_class是父表
t_student是子表
删除表的顺序?
先删子,再删父。
创建表的顺序?
先创建父,再创建子。
删除数据的顺序?
先删子,再删父。
插入数据的顺序?
先插入父,再插入子。
思考:子表中的外键引用的父表中的某个字段,被引用的这个字段必须是主键吗?
不一定是主键,但至少具有unique约束。
思考:外键可以为NULL吗?
外键值可以为NULL。
事务:(只有DML语句才有事务(insert,updata,delete)(对数据进行操作),与其他语句无关)
什么是事务?
一个完整的业务逻辑。
什么是一个完整的业务逻辑?
假设转账,从账户向B账户转账10000元,
将A账户的钱减去10000元,(updata语句)
将B账户的钱加上10000元。(updata语句)
这就是一个完整的业务逻辑。
以上的操作是一个最小的工作单元,要么同时成功,要么同时失败,不可再分。
即这两个updata语句要求必须同时成功或者同时失败。
事务是怎么做到多条DML语句同时成功和同时失败的呢?
InnoDB存储引擎:提供一组用来记录事务性活动的日志文件
事务开启了:
insert
insert
insert
delete
update
update
update
事务结束了!
在事务的执行过程中,每一条DML的操作都会记录到“事务性活动的日志文件”中。
在事务的执行过程中,我们可以提交事务,也可以回滚事务。
提交事务?
清空事务性活动的日志文件,将数据全部彻底持久化到数据库表中。
提交事务标志着,事务的结束。并且是一种全部成功的结束。
回滚事务?
将之前所有的DML操作全部撤销,并且清空事务性活动的日志文件
回滚事务标志着,事务的结束。并且是一种全部失败的结束。
MySQL默认支持自动提交事务!!!
即每执行一句DML语句,则提交一次
取消自动提交事务机制:
start transaction;
提交事务:
commit;
回滚事务:
rollback;
# 事务的四大特性:
A:原子性
说明事务是最小的工作单元,不可再分。
C:一致性
所有事务要求,在同一个事务当中,所有操作必须同时成功,或者同时失败,
以保证数据的一致性。
I:隔离性
A事务和B事务之间具有一定的个隔离。
(多线程并发访问)
D:持久性
事务最终结束的保障。事务提交,就相当于将没有保存到硬盘上的数据保存到硬盘上。
重点研究一下事务的隔离性!!!
A教室和B教室中间有一道墙,这道墙可以很厚,也可以很薄。这就是事务的隔离级别。
这道墙越厚,表示隔离级别就越高。
事务和事务之间的隔离级别有哪些呢??4个级别
读未提交:read uncommitted(最低的隔离级别)《没有提交就读到了》
什么是读未提交?
事务A可以读取到事务B未提交的数据。
这种隔离级别存在的问题就是:
脏读现象!(Dirty Read)
我们称读到了脏数据。
这种隔离级别一般都是理论上的,大多数的数据库隔离级别都是二档起步!
读已提交:read committed《提交之后才能读到》
什么是读已提交?
事务A只能读取到事务B提交之后的数据。
这种隔离级别解决了什么问题?
解决了脏读的现象。
这种隔离级别存在什么问题?
不可重复读取数据。
什么是不可重复读取数据呢?
在事务开启之后,第一次读到的数据是3条,当前事务还没有
结束,可能第二次再读取的时候,读到的数据是4条,3不等于4
称为不可重复读取。
可重复读:repeatable read《提交之后也读不到,永远读取的都是刚开启事务时的数据》
什么是可重复读取?
事务A开启之后,不管是多久,每一次事务A读取到的数据都是一致的。
即使事务B将数据已经修改,并且提交了,事务A
读取到的数据还是没有发生改变,这就是可重复读。
可重复读解决了什么问题?
解决了不可重复读取数据。
可重复读存在的问题是什么?
可以会出现幻影读。
每一次读取到的数据都是幻象。不够真实!
早晨9点开始开启了事务,只要事务不结束,到晚上9点,读到的数据还是那样!
读到的是假象。不够绝对的真实。
mysql中默认的事务隔离级别就是这个!!!!!!!!!!!
序列化/串行化:serializable(最高的隔离级别)
这是最高隔离级别,效率最低。解决了所有的问题。
这种隔离级别表示事务排队,不能并发!
synchronized,线程同步(事务同步)
每一次读取到的数据都是最真实的,并且效率是最低的。
什么是视图?
view:站在不同的角度去看待同一份数据。
创建视图对象:
create view dept2_view as select * from dept2;
删除视图对象:
drop view dept2_view;
注意:只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;
数据库设计范式共有三个:
第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,
不要产生部分依赖。(复合主键)
第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,
不要产生传递依赖。
第一范式
最核心,最重要的范式,所有表的设计都需要满足。
必须有主键,并且每一个字段都是原子性不可再分。
学生编号 学生姓名 联系方式
------------------------------------------
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999
1001 王五 ww@163.net,13488888888
以上是学生表,满足第一范式吗?
不满足:第一:没有主键。
第二:联系方式可以分为邮箱地址和电话。
学生编号(pk) 学生姓名 邮箱地址 联系电话
-----------------------------------------------------------
1001 张三 zs@gmail.com 13599999990
1002 李四 ls@gmail.com 13699999999
1003 王五 ww@163.net 13488888888
第二范式:
建立在第一范式的基础之上,
要求所有非主键字段必须完全依赖主键,不要产生部分依赖。
学生编号 学生姓名 教师编号 教师姓名
----------------------------------------------------
1001 张三 001 王老师
1002 李四 002 赵老师
1003 王五 001 王老师
1001 张三 002 赵老师
这张表描述了学生和老师的关系:(1个学生可能有多个老师,1个老师有多个学生)
这是非常典型的:多对多关系!
分析以上的表是否满足第一范式?
不满足第一范式。
怎么满足第一范式呢?修改
学生编号+教师编号(pk) 学生姓名 教师姓名
------------------------------------------------------
1001 001 张三 王老师
1002 002 李四 赵老师
1003 001 王五 王老师
1001 002 张三 赵老师
学生编号 教师编号,两个字段联合做主键,复合主键(PK: 学生编号+教师编号)
经过修改之后,以上的表满足了第一范式。但是满足第二范式吗?
不满足,“张三”依赖1001,“王老师”依赖001,显然产生了部分依赖。
产生部分依赖有什么缺点?
数据冗余了。空间浪费了。“张三”重复了,“王老师”重复了。
为了让以上的表满足第二范式,你需要这样设计:
使用三张表来表示多对多的关系!!!!
学生表
学生编号(pk) 学生名字
------------------------------------
1001 张三
1002 李四
1003 王五
教师表
教师编号(pk) 教师姓名
--------------------------------------
001 王老师
002 赵老师
学生教师关系表
id(pk) 学生编号(fk) 教师编号(fk)
------------------------------------------------------
1 1001 001
2 1002 002
3 1003 001
4 1001 002
背口诀:
多对多怎么设计?
多对多,三张表,关系表两个外键!!!!!!!
第三范式
第三范式建立在第二范式的基础之上
要求所有非主键字典必须直接依赖主键,不要产生传递依赖。
学生编号(PK) 学生姓名 班级编号 班级名称
---------------------------------------------------------
1001 张三 01 一年一班
1002 李四 02 一年二班
1003 王五 03 一年三班
1004 赵六 03 一年三班
以上表的设计是描述:班级和学生的关系。很显然是1对多关系!
一个教室中有多个学生。
分析以上表是否满足第一范式?
满足第一范式,有主键。
分析以上表是否满足第二范式?
满足第二范式,因为主键不是复合主键,没有产生部分依赖。主键是单一主键。
分析以上表是否满足第三范式?
第三范式要求:不要产生传递依赖!
一年一班依赖01,01依赖1001,产生了传递依赖。
不符合第三范式的要求。产生了数据的冗余。
那么应该怎么设计一对多呢?
班级表:一
班级编号(pk) 班级名称
--------------------------------
01 一年一班
02 一年二班
03 一年三班
学生表:多
学生编号(PK) 学生姓名 班级编号(fk)
---------------------------------------------------
1001 张三 01
1002 李四 02
1003 王五 03
1004 赵六 03
背口诀:
一对多,两张表,多的表加外键!!!!!