Mysql基本使用

最新推荐文章于 2022-06-15 02:25:12 发布

「已注销」

最新推荐文章于 2022-06-15 02:25:12 发布

阅读量136

点赞数

分类专栏： java基础文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_62786471/article/details/120766713

版权

java基础专栏收录该内容

8 篇文章 0 订阅

订阅专栏

将查询结果插入到一张表当中？insert相关的！！！（了解）

一、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 -u root -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 as yearsal,…… from 表名;

B:别名中有中文，建议用单引号。

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后面不能直接使用分组函数

正确的做法：

先查询最低工资是多少

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;

+------+----------+------------+---------------------+

+------+----------+------------+---------------------+

| 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.主键约束

* 怎么给一张表添加主键约束呢？

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个。（必须记住）

* 使用表级约束方式定义主键：

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提供主键值自增：（非常重要。）

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）对象

主键值建议使用：

int

bigint

char

等类型。

不建议使用：varchar来做主键。主键值一般都是数字，一般都是定长的！

5.外键约束

* 关于外键约束的相关术语：

外键约束: foreign key

外键字段：添加有外键约束的字段

外键值：外键字段中的每一个值。

两张表（班级表和学生表）【如果放在一张表中造成代码重复，冗杂】

t_class 班级表

cno(pk) cname

--------------------------------------------------------

101 北京大兴区经济技术开发区亦庄二中高三1班

102 北京大兴区经济技术开发区亦庄二中高三2班

t_student 学生表

sno(pk) sname classno(该字段添加外键约束fk)

------------------------------------------------------------

1 zs1 101

2 zs2 101

3 zs3 102

4 zs4 102

5 zs5 102

当classno没有任何约束的时候，可能会出现103班的，但是103班不存在。

所以保证都是101和102班的，需要给classno字段添加外键约束。

* 将以上表的建表语句写出来：

t_student中的classno字段引用t_class表中的cno字段，此时t_student表叫做子表。t_class表叫做父表。

顺序要求：

删除数据的时候，先删除子表，再删除父表。

添加数据的时候，先添加父表，在添加子表。

创建表的时候，先创建父表，再创建子表。

删除表的时候，先删除子表，在删除父表。

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 |

+--------+-------+------+

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 |

+--------+-------+------+

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 |

+--------+-------+------+

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

1001 王五 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

背口诀：

一对多，两张表，多的表加外键！！！！！！！！！！！！

总结表的设计？

一对多：

一对多，两张表，多的表加外键！！！！！！！！！！！！

多对多：

多对多，三张表，关系表两个外键！！！！！！！！！！！！！！！

一对一：

一对一放到一张表中不就行了吗？为啥还要拆分表？

在实际的开发中，可能存在一张表字段太多，太庞大。这个时候要拆分表。

「已注销」

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql基本使用

一、Mysql基本概念1.1MySql分类3、学习MySQL主要还是学习通用的SQL语句，那么SQL语句包括增删改查，SQL语句怎么分类呢？DQL（数据查询语言）: 查询语句，凡是select语句都是DQL。DML（数据操作语言）：insert delete update，对表当中的数据进行增删改。DDL（数据定义语言）：create drop alter，对表结构的增删改。TCL（事务控制语言）：commit提交事务，rollback回滚事务。(TCL中的T是Tran...
复制链接

扫一扫

专栏目录