一、MySql基本概念
1.1MySql分类
3、学习MySQL主要还是学习通用的SQL语句,那么SQL语句包括增删改查,SQL语句怎么分类呢?
DQL(数据查询语言): 查询语句,凡是select语句都是DQL。
DML(数据操作语言):insert delete update,对表当中的数据进行增删改。
DDL(数据定义语言):create drop alter,对表结构的增删改。
TCL(事务控制语言):commit提交事务,rollback回滚事务。(TCL中的T是Transaction)
DCL(数据控制语言): grant授权、revoke撤销权限等。
1.2使用步骤
第一步:登录mysql数据库管理系统
dos命令窗口:
mysql -uroot -p123456
第二步:查看有哪些数据库
show databases; (这个不是SQL语句,属于MySQL的命令。)
第三步:创建属于我们自己的数据库
create database bjpowernode; (这个不是SQL语句,属于MySQL的命令。)
第四步:使用bjpowernode数据
use bjpowernode; (这个不是SQL语句,属于MySQL的命令。)
第五步:查看当前使用的数据库中有哪些表?
show tables; (这个不是SQL语句,属于MySQL的命令。)
第六步:初始化数据
mysql> source D:\course\05-MySQL\resources\bjpowernode.sql
7、查看表结构:mysql>desc emp;
8、表中的数据: mysql> Select * from emp;
补充:
mysql> select database(); 查看当前使用的是哪个数据库
mysql> select version(); 查看mysql的版本号。
\c 命令,结束一条语句。
exit 命令,退出mysql。
查看创建表的语句:
show create table emp;
1.3关于mysql中的数据类型?
很多数据类型,我们只需要掌握一些常见的数据类型即可。
varchar(最长255)
可变长度的字符串
比较智能,节省空间。
会根据实际的数据长度动态分配空间。
优点:节省空间
缺点:需要动态分配空间,速度慢。
char(最长255)
定长字符串
不管实际的数据长度是多少。
分配固定长度的空间去存储数据。
使用不恰当的时候,可能会导致空间的浪费。
优点:不需要动态分配空间,速度快。
缺点:使用不当可能会导致空间的浪费。
varchar和char我们应该怎么选择?
性别字段你选什么?因为性别是固定长度的字符串,所以选择char。
姓名字段你选什么?每一个人的名字长度不同,所以选择varchar。
int(最长11)
数字中的整数型。等同于java的int。
bigint
数字中的长整型。等同于java中的long。
float
单精度浮点型数据
double
双精度浮点型数据
date
短日期类型
datetime
长日期类型
clob
字符大对象
最多可以存储4G的字符串。
比如:存储一篇文章,存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
Character Large OBject:CLOB
blob
二进制大对象
Binary Large OBject
专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候,例如插入一个图片、视频等,
你需要使用IO流才行。
二、简单和连接查询
1:简单的查询语句(SQL):
语法格式:
select 字段名1,字段名2,…… from 表名;
注意:A:给查询结果的列命名
select 字段名1,字段名2 yearsal,…… from 表名;
select 字段名1,字段名2 “yearsal”,…… from 表名;
select 字段名1,字段名2 as yearsal,…… from 表名;
以上三种方式都可以给列命名。
2:条件查询:
语法格式:
select
字段,字段…
from
表名
where
条件;
执行顺序:先from,然后where,最后select
A:注意: but … and … 数字是闭区间,字母是左闭右开
B:in(800, 5000); // in后面的值不是区间,是具体的值。
c: is null 为空, is not null 不为空
D:%代表任意多个字符,_代表任意1个字符。
找出名字中第二个字母是A的?
select ename from emp where ename like ‘A%’;
找出名字中有下划线的?
select name from t_user where name like ‘%\%’;
找出名字中最后一个字母是T的?
select ename from emp where ename like ‘%T’;
3:排序
select select
ename,sal ename,sal
from from
emp emp
order by order by
sal; //默认升序 sal desc; //指定降序
注意:
A: 如果是多个字段
select
ename,sal
from
emp
order by
sal asc , ename asc; //sal在前起主导作用,只有sal相等的时候,才会考虑启用ename排序。
4:数字处理函数:
upper 转换大写
substr 取子串(substr(被截取的字符串,起始下标,截取的长度)
length 取长度
trim去空格
str_to_date将字符串转成日期
data_format 格式化日期
format 设置千分位
round 四舍五入
rand() 生成随机数
ifnull 可以将null 转换成一个具体值。
5:分组函数:
5个: 注意:分组函数在使用时必须先进行分组,然后才能用。
count 计数 如果你没有对数据进行分组。整张表默认为一组。
sum 求和
avg 平均值
max 最大值
min 最小值
注意事项:
A:分组函数自动忽略NULL,不需要提前对NULL进行处理。
B:count(具体字段):表示统计该字段下所有不为NULL的元素的总数。
count(*)统计表当中的总行数。
C:分组函数不能直接在where后面。(因为分组函数在使用的时候必须先分组之后才能使用,
where在执行的时候还没有分组所以where后面不能出现分组函数。)
分组查询(*):(先进行分组,然后对每一组的数据进行操作。)
select
….
from
….
group by
….
重点结论:在一条select语句当中,如果有 group by语句的话,select 后面
只能跟:参加分组的字段,以及分组函数,其他的一律不跟。
D:使用having可以对分完组之后的数据进一步过滤。having不能单独使用,
having不能代替where,having必须和 group by联合使用。
3:关于查询结果集的去重?
mysql> select distinct job from emp; // distinct关键字去除重复记录。
mysql> select ename,distinct job from emp;
以上的sql语句是错误的。
记住:distinct只能出现在所有字段的最前面。
连接查询(*):
1、内连接之等值连接
1:内连接之等值连接:最大特点是:条件是等量关系。
select
e.ename,d.dname
from
emp e
join
dept d
on
e.deptno = d.deptno;
2、内连接之非等值连接
2:内连接之非等值连接:最大的特点是:连接条件中的关系是非等量关系。
select
e.ename,e.sal,s.grade
from
emp e
join
salgrade s
on
e.sal between s.losal and s.hisal;
3、自连接
3:自连接:最大的特点是:一张表看做两张表。自己连接自己。
员工的领导编号 = 领导的员工编号
select
a.ename as ‘员工名’,b.ename as ‘领导名’
from
emp a
join
emp b
on
a.mgr = b.empno;
员工表 领导表
4、外连接
4:外连接:(左外连接/左连接)
select
a.ename ‘员工’, b.ename ‘领导’
from
emp a
left join
emp b
on
a.mgr = b.empno;
外连接:
假设A和B表进行连接,使用外连接的话,AB两张表中有一张表是主表,一张表是副表,主要查询主表中
的数据,捎带着查询副表,当副表中的数据没有和主表中的数据匹配上,副表自动模拟出NULL与之匹配。
5、三张表怎么查询?
2.9、三张表怎么连接查询?
案例:找出每一个员工的部门名称以及工资等级。
表示:A表和B表先进行表连接,连接之后A表继续和C表进行连接。
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;
三、子查询
子查询:
- select语句中嵌套select语句,被嵌套的select语句称为子查询。
- 子查询可以出现在哪里呢?
select
..(select).
from
..(select).
where
..(select).
3:where后面的子查询:
select
ename,sal
from
emp
where
sal >min(sal) 这种是错误的,where后面不能直接使用分组函数
正确的做法:
先查询最低工资是多少<br />select ename,sal from emp where sal>(select min(sal) from emp);
from后面的子查询:
注意:from后面的子查询,可以将子查询的查询结果当做一张临时表。(技巧)
案例:找出每个部门平均薪水的等级。
第一步:找出每个部门平均薪水(按照部门编号分组,求sal的平均值)
select deptno,avg(sal) as avgsal from emp group by deptno;
+————+——————-+
| deptno | avgsal |
+————+——————-+
| 10 | 2916.666667 | 的等级
| 20 | 2175.000000 | 的等级
| 30 | 1566.666667 | 的等级 临时表t
+————+——————-+
第二步:将以上的查询结果当做临时表t,让t表和salgrade s表连接,条件是:t.avgsal between s.losal and s.hisal
select
t.*,s.grade
from
(select deptno,avg(sal) as avgsal from emp group by deptno) t
join
salgrade s
on
t.avgsal between s.losal and s.hisal;
+————+——————-+———-+
| deptno | avgsal | grade |
+————+——————-+———-+
| 30 | 1566.666667 | 3 |
| 10 | 2916.666667 | 4 |
| 20 | 2175.000000 | 4 |
+————+——————-+———-+
四、union和limit
1、union
1:union (可以将查询结果集相加)要求:列数相同,数据类型也得一样。
select ename,job from emp where job in(‘MANAGER’,’SALESMAN’);
select ename,job from emp where job = ‘MANAGER’
union
select ename,job from emp where job = ‘SALESMAN’;
2、limit
取前5个:
select ename,sal from emp order by sal desc limit 0, 5;
limit 起始下标,长度
或者
select ename,sal from emp order by sal desc limit 5;
3、通用的标准分页sql
每页显示3条记录:
第1页:0, 3 起始长度,长度
第2页:3, 3
第3页:6, 3
第4页:9, 3
每页显示pageSize条记录
第pageNo页:limit(pageNo-1)*pageSize , pageSize
4、总结一个完整的DQL语句怎么写
总结一个完整的DQL语句怎么写?
select 5
…
from 1
…
where 2
…
group by 3
…
having 4
…
order by 6
…
limit 7
…;
五、表
1、建表的语法格式
建表的语法格式:(建表属于DDL语句,DDL包括:create drop alter)
create table 表名(字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型);
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型
);
举例:
创建一个学生表?
学号、姓名、年龄、性别、邮箱地址
create table t_student(
no int,
name varchar(32),
sex char(1),
age int(3),
email varchar(255)
);
删除表:
drop table t_student; // 当这张表不存在的时候会报错!
// 如果这张表存在的话,删除
drop table if exists t_student;
2、插入数据
插入数据insert (DML)
语法格式:
insert into 表名(字段名1,字段名2,字段名3…) values(值1,值2,值3);
注意:字段名和值要一一对应。什么是一一对应?
数量要对应。数据类型要对应。
注意:一次可以插入多条记录:
语法:insert into t_user(字段名1,字段名2) values(),(),(),();
一次可以插入多条记录:
insert into t_user(id,name,birth,create_time) values
(1,’zs’,’1980-10-11’,now()),
(2,’lisi’,’1981-10-11’,now()),
(3,’wangwu’,’1982-10-11’,now());
举例:
insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,'zhangsan@123.com');
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);
3、修改
语法格式:
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3… where 条件;
注意:没有条件限制会导致所有数据全部更新。
举例:
update t_user set name = ‘jack’, birth = ‘2000-10-11’ where id = 2;
+———+—————+——————+——————————-+
| id | name | birth | create_time |
+———+—————+——————+——————————-+
| 1 | zhangsan | 1990-10-01 | 2020-03-18 15:49:50 |
| 2 | jack | 2000-10-11 | 2020-03-18 15:51:23 |
+———+—————+——————+——————————-+
update t_user set name = ‘jack’, birth = ‘2000-10-11’, create_time = now() where id = 2;
更新所有?
update t_user set name = ‘abc’;
4、删除数据
删除数据 delete (DML)
语法格式?
delete from 表名 where 条件;
注意:没有条件,整张表的数据会全部删除!
快速删除表中的数据?【truncate比较重要,必须掌握】
truncate语句删除数据的原理?
这种删除效率比较高,表被一次截断,物理删除。
这种删除缺点:不支持回滚。
这种删除优点:快速。
用法:truncate table dept_bak; (这种操作属于DDL操作。)
举例:
delete from t_user where id = 2;
insert into t_user(id) values(2);
delete from t_user; // 删除所有!
快速复制表:(了解)
mysql> create table emp2 as select * from emp;
将查询结果插入到一张表当中?insert相关的!!!(了解)
insert into dept_bak select * from dept;
六、约束
1.约束的概述
13.1、什么是约束?常见的约束有哪些呢?
在创建表的时候,可以给表的字段添加相应的约束,添加约束的目的是为了保证表中数据的
合法性、有效性、完整性。
常见的约束有哪些呢?
非空约束(not null):约束的字段不能为NULL
唯一约束(unique):约束的字段不能重复
主键约束(primary key):约束的字段既不能为NULL,也不能重复(简称PK)
外键约束(foreign key):…(简称FK)
检查约束(check):注意Oracle数据库有check约束,但是mysql没有,目前mysql不支持该约
2.非空约束: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) values(3);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
3.唯一性约束: unique
唯一性约束: unique(name字段虽然被unique约束了,但是可以为NULL。)
唯一性约束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,'wangwu','wangwu@123.com');
select * from t_vip;
insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com');
ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'
name和email两个字段联合起来唯一!!!
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email) // 约束没有添加在列的后面,这种约束被称为表级约束。
);
insert into t_vip(id,name,email) values(1,'zhangsan','zhangsan@123.com');
insert into t_vip(id,name,email) values(2,'zhangsan','zhangsan@sina.com');
select * from t_vip;
name和email两个字段联合起来唯一!!!
insert into t_vip(id,name,email) values(3,'zhangsan','zhangsan@sina.com');
ERROR 1062 (23000): Duplicate entry 'zhangsan-zhangsan@sina.com' for key 'name'
4.主键约束
- 怎么给一张表添加主键约束呢? ```sql drop table if exists t_user; create table t_user( id int primary key, // 列级约束 username varchar(255), email varchar(255) ); insert into t_user(id,username,email) values(1,’zs’,’zs@123.com’);
select * from t_user;
insert into t_user(id,username,email) values(1,’jack’,’jack@123.com’);
* 一张表的主键约束只能有1个。(必须记住)<br />* 使用表级约束方式定义主键:
```sql
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id)
);
insert into t_user(id,username) values(1,'zs');
insert into t_user(id,username) values(2,'ls');
insert into t_user(id,username) values(3,'ws');
insert into t_user(id,username) values(4,'cs');
select * from t_user;
insert into t_user(id,username) values(4,'cx');
- mysql提供主键值自增:(非常重要。) ```sql drop table if exists t_user; create table t_user( id int primary key auto_increment, // id字段自动维护一个自增的数字,从1开始,以1递增。 username varchar(255) ); insert into t_user(username) values(‘a’); insert into t_user(username) values(‘b’); insert into t_user(username) values(‘c’); insert into t_user(username) values(‘d’); insert into t_user(username) values(‘e’); insert into t_user(username) values(‘f’);
select * from t_user;
提示:Oracle当中也提供了一个自增机制,叫做:序列(sequence)对象
主键值建议使用:<br />int<br />bigint<br />char<br />等类型。
不建议使用:varchar来做主键。主键值一般都是数字,一般都是定长的!
<a name="fsi80"></a>
### 5.外键约束
* 关于外键约束的相关术语:<br />外键约束: foreign key<br />外键字段:添加有外键约束的字段<br />外键值:外键字段中的每一个值。<br />两张表(班级表和学生表)【如果放在一张表中造成代码重复,冗杂】<br />t_class 班级表<br />cno(pk) cname<br />--------------------------------------------------------<br />101 北京大兴区经济技术开发区亦庄二中高三1班<br />102 北京大兴区经济技术开发区亦庄二中高三2班
t_student 学生表<br />sno(pk) sname classno(该字段添加外键约束fk)<br />------------------------------------------------------------<br />1 zs1 101<br />2 zs2 101<br />3 zs3 102<br />4 zs4 102<br />5 zs5 102
当classno没有任何约束的时候,可能会出现103班的,但是103班不存在。<br />所以保证都是101和102班的,需要给classno字段添加外键约束。
* 将以上表的建表语句写出来:
t_student中的classno字段引用t_class表中的cno字段,此时t_student表叫做子表。t_class表叫做父表。
顺序要求:<br />删除数据的时候,先删除子表,再删除父表。<br />添加数据的时候,先添加父表,在添加子表。<br />创建表的时候,先创建父表,再创建子表。<br />删除表的时候,先删除子表,在删除父表。
```sql
drop table if exists t_student;
drop table if exists t_class;
create table t_class(
cno int,
cname varchar(255),
primary key(cno)
);
create table t_student(
sno int,
sname varchar(255),
classno int,
primary key(sno),
foreign key(classno) references t_class(cno)
);
insert into t_class values(101,'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx');
insert into t_class values(102,'yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy');
insert into t_student values(1,'zs1',101);
insert into t_student values(2,'zs2',101);
insert into t_student values(3,'zs3',102);
insert into t_student values(4,'zs4',102);
insert into t_student values(5,'zs5',102);
insert into t_student values(6,'zs6',102);
select * from t_class;
select * from t_student;
insert into t_student values(7,'lisi',103);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`bjpowernode`.INT `t_student_ibfk_1` FOREIGN KEY (`classno`) REFERENCES `t_class` (`cno`))
外键值可以为NULL?
外键可以为NULL。外键字段引用其他表的某个字段的时候,被引用的字段必须是主键吗?
注意:被引用的字段不一定是主键,但至少具有unique约束。
七、事务
怎么提交事务,怎么回滚事务?
提交事务:commit; 语句
回滚事务:rollback; 语句(回滚永远都是只能回滚到上一次的提交点!)
怎么将mysql的自动提交机制关闭掉呢?
先执行这个命令:start transaction;
事务包括4个特性?
A:原子性
说明事务是最小的工作单元。不可再分。
C:一致性
所有事务要求,在同一个事务当中,所有操作必须同时成功,或者同时失败,
以保证数据的一致性。
I:隔离性
A事务和B事务之间具有一定的隔离。
教室A和教室B之间有一道墙,这道墙就是隔离性。
A事务在操作一张表的时候,另一个事务B也操作这张表会那样???
D:持久性
事务最终结束的一个保障。事务提交,就相当于将没有保存到硬盘上的数据
保存到硬盘上!
3.5、关于事务之间的隔离性
事务隔离性存在隔离级别,理论上隔离级别包括4个:
第一级别:读未提交(read uncommitted)
对方事务还没有提交,我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读(Dirty Read)现象:表示读到了脏的数据。
第二级别:读已提交(read committed)
对方事务提交之后的数据我方可以读取到。
这种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是:不可重复读。
第三级别:可重复读(repeatable read)
这种隔离级别解决了:不可重复读问题。
这种隔离级别存在的问题是:读取到的数据是幻象。
第四级别:序列化读/串行化读(serializable)
解决了所有问题。
效率低。需要事务排队。
oracle数据库默认的隔离级别是:读已提交。
mysql数据库默认的隔离级别是:可重复读。
演示事务
演示事务:
————————————————-回滚事务————————————————————
mysql> use bjpowernode;
Database changed
mysql> select * from dept_bak;
Empty set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(10,’abc’, ‘tj’);
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(10,’abc’, ‘tj’);
Query OK, 1 row affected (0.00 sec)
mysql> select * from dept_bak;
+————+———-+———+
| DEPTNO | DNAME | LOC |
+————+———-+———+
| 10 | abc | tj |
| 10 | abc | tj |
+————+———-+———+
2 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
Empty set (0.00 sec)
————————————————-提交事务————————————————————
mysql> use bjpowernode;
Database changed
mysql> select * from dept_bak;
+————+———-+———+
| DEPTNO | DNAME | LOC |
+————+———-+———+
| 10 | abc | bj |
+————+———-+———+
1 row in set (0.00 sec)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into dept_bak values(20,’abc
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,’abc
Query OK, 1 row affected (0.00 sec)
mysql> insert into dept_bak values(20,’abc
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from dept_bak;
+————+———-+———+
| DEPTNO | DNAME | LOC |
+————+———-+———+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+————+———-+———+
4 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from dept_bak;
+————+———-+———+
| DEPTNO | DNAME | LOC |
+————+———-+———+
| 10 | abc | bj |
| 20 | abc | tj |
| 20 | abc | tj |
| 20 | abc | tj |
+————+———-+———+
4 rows in set (0.00 sec)
七、索引
提醒1:在任何数据库当中主键上都会自动添加索引对象,id字段上自动有索引,
因为id是PK。另外在mysql当中,一个字段上如果有unique约束的话,也会自动
创建索引对象。
提醒2:在任何数据库当中,任何一张表的任何一条记录在硬盘存储上都有
一个硬盘的物理存储编号。
提醒3:在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式
存在。
创建索引:
mysql> create index emp_ename_index on emp(ename);
给emp表的ename字段添加索引,起名:emp_ename_index
删除索引:
mysql> drop index emp_ename_index on emp;
将emp表上的emp_ename_index索引对象删除。
失效的第1种情况:
select * from emp where ename like ‘%T’;
ename上即使添加了索引,也不会走索引,为什么?
原因是因为模糊匹配当中以“%”开头了!
尽量避免模糊查询的时候以“%”开始。
这是一种优化的手段/策略。
失效的第2种情况:
使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有
索引,才会走索引,如果其中一边有一个字段没有索引,那么另一个
字段上的索引也会实现。
失效的第3种情况:
使用复合索引的时候,没有使用左侧的列查找,索引失效
什么是复合索引?
两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。
create index emp_job_sal_index on emp(job,sal);
失效的第4种情况:
在where当中索引列参加了运算,索引失效。
mysql> create index emp_sal_index on emp(sal);
失效的第5种情况:
在where当中索引列使用了函数
explain select * from emp where lower(ename) = ‘smith’;
后面还有…..
八、视图
什么是视图?
view:站在不同的角度去看待同一份数据。
2.2、怎么创建视图对象?怎么删除视图对象?
表复制:
mysql> create table dept2 as select * from dept;
dept2表中的数据:
mysql> select * from dept2;
+————+——————+—————+
| DEPTNO | DNAME | LOC |
+————+——————+—————+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
+————+——————+—————+
创建视图对象:
create view dept2_view as select * from dept2;
删除视图对象:
drop view dept2_view;
注意:只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;
用视图做什么?
我们可以面向视图对象进行增删改查,对视图对象的增删改查,会导致
原表被操作!(视图的特点:通过对视图的操作,会影响到原表数据。)
//面向视图查询
select * from dept2_view;
// 面向视图插入
insert into dept2_view(deptno,dname,loc) values(60,’SALES’, ‘BEIJING’);
// 查询原表数据
mysql> select * from dept2;
+————+——————+—————+
| DEPTNO | DNAME | LOC |
+————+——————+—————+
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 60 | SALES | BEIJING |
+————+——————+—————+
// 面向视图删除
mysql> delete from dept2_view;
// 查询原表数据
mysql> select * from dept2;
Empty set (0.00 sec)
九、设计三范式
数据库设计范式共有?
3个。
第一范式:要求任何一张表必须有主键,每一个字段原子性不可再分。
第二范式:建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,
不要产生部分依赖。
第三范式:建立在第二范式的基础之上,要求所有非主键字段直接依赖主键,
不要产生传递依赖。
第一范式
最核心,最重要的范式,所有表的设计都需要满足。
必须有主键,并且每一个字段都是原子性不可再分。
学生编号 学生姓名 联系方式
—————————————————————
1001 张三 zs@gmail.com,1359999999
1002 李四 ls@gmail.com,13699999999
1003 王五 ww@163.net,13488888888
以上是学生表,满足第一范式吗?
不满足,第一:没有主键。第二:联系方式可以分为邮箱地址和电话
学生编号(pk) 学生姓名 邮箱地址 联系电话
——————————————————————————
1001 张三 zs@gmail.com 1359999999
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
背口诀:
一对多,两张表,多的表加外键!!!!!!!!!!!!
总结表的设计?
一对多:
一对多,两张表,多的表加外键!!!!!!!!!!!!
多对多:
多对多,三张表,关系表两个外键!!!!!!!!!!!!!!!
一对一:
一对一放到一张表中不就行了吗?为啥还要拆分表?
在实际的开发中,可能存在一张表字段太多,太庞大。这个时候要拆分表。