草庐IT

朝花夕拾之MySQL-02

：Concerto 2023-03-28 原文

5 DDL语句

5.1 建表create

  1. 基本语法
create table 表名( 字段名1 数据类型, 字段名2 数据类型, 字段名3 数据类型 );
  1. 数据类型
varchar(最长255) 可变长度的字符串 比较智能，节省空间。 会根据实际的数据长度动态分配空间。 优点：节省空间 缺点：需要动态分配空间，速度慢。 char(最长255) 定长字符串 不管实际的数据长度是多少。 分配固定长度的空间去存储数据。 使用不恰当的时候，可能会导致空间的浪费。 优点：不需要动态分配空间，速度快。 缺点：使用不当可能会导致空间的浪费。 varchar和char我们应该怎么选择？ 性别字段你选什么？因为性别是固定长度的字符串，所以选择char。 姓名字段你选什么？每一个人的名字长度不同，所以选择varchar。 int(最长11) 数字中的整数型。等同于java的int。 bigint 数字中的长整型。等同于java中的long。 float 单精度浮点型数据 double 双精度浮点型数据 date 短日期类型:mysql短日期默认格式：%Y-%m-%d datetime 长日期类型: mysql长日期默认格式：%Y-%m-%d %h:%i:%s clob 字符大对象 最多可以存储4G的字符串。 比如：存储一篇文章，存储一个说明。 超过255个字符的都要采用CLOB字符大对象来存储。 Character Large OBject:CLOB blob 二进制大对象 Binary Large OBject 专门用来存储图片、声音、视频等流媒体数据。 往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等， 你需要使用IO流才行。
  1. 案例
create table t_student( no int, name varchar(32), sex char(1), age int(3), email varchar(255) );

5.2 删表drop

  1. 语法
drop table 表名; drop table if exists 表名;
  1. 案例
drop table if exists t_student;

5.3 alter 更改表结构

  1. 语法
  • 删列
Alter table 【表名】 drop 【列名】
  • 增列
Alter table 【表名】 add 【列名】 【类型】 alter table table1 add transactor varchar(10) not Null;
  • 重命名列
Alter table 【表名】 change 【列名】【新名】
  • 修改表字段
alter table 表名称 change 字段名称 字段类型 [是否允许非空]; alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

6 DML语句

6.1 插入数据insert

  1. 语法
insert into 表名(字段名1,字段名2,字段名3...) values(值1,值2,值3);
  1. 案例
insert into t_student(email,name,sex,age,no) values ('lisi@123.com','lisi','f',20,2); 如果没有给其它字段指定值的话，默认值是NULL。 insert into t_student(no) values(1); 也可以不写值，不过代表全部 insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');
  1. str_to_date
  • 场景：insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990'); // 1990年10月1日 出问题了：原因是类型不匹配。数据库birth是date类型，这里给了一个字符串varchar。
mysql> desc t_user; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(32) | YES | | NULL | | | birth | date | YES | | NULL | | +-------+-------------+------+-----+---------+-------+
  • 用法：
    • 将字符串varchar转换成日期类型date用：str_to_date
    • str_to_date('字符串日期', '日期格式')
insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y');
  • 一些日期格式
mysql的日期格式： %Y 年 （Y要大写！) %m 月 %d 日 %h 时 %i 分 %s 秒
  1. date_format
  • 查询的时候可以以某个特定的日期格式展示吗？
mysql> select id,name,birth from t_user; +------+----------+------------+ | id | name | birth | +------+----------+------------+ | 1 | zhangsan | 1990-10-01 | | 2 | lisi | 1990-10-01 | +------+----------+------------+
  • date_format
    • 这个函数可以将日期类型转换成特定格式的字符串。
    • date_format(日期类型数据, '日期格式')
select id,name,date_format(birth,'%Y/%m/%d') as birth from t_user;

6.2 修改数据update

  1. 语法
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件;
  1. 案例
update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

6.3 删除数据delete

  1. 语法
delete from 表名 where 条件;
  1. 案例
delete from t_user where id = 2;

6.4 其他技巧

  1. insert语句可以一次插入多条记录吗？
语法：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());
  1. 快速创建表？【了解内容】
create table emp2 as select * from emp;
  1. 快速删除表中的数据
  • delete这种删除数据的方式比较慢。在磁盘不会被释放，但是可以回滚！！
delete from dept_bak;
  • truncate这种删除效率比较高，物理删除，不支持回滚！！
用法：truncate table dept_bak; （这种操作属于DDL操作。）
  • 效率快慢的体现
使用delete，也许需要执行1个小时才能删除完! 使用truncate删除表中的数据。只需要不到1秒钟的时间就删除结束

7 约束

7.1 约束类型

1. 非空约束：not null 2. 唯一性约束: unique 3. 主键约束: primary key （简称PK） 4. 外键约束：foreign key（简称FK） 5. 检查约束：check（mysql不支持，oracle支持）

7.2 非空约束

  1. 案例
create table t_vip( id int, name varchar(255) not null // not null只有列级约束，没有表级约束！ );
  1. 约束失效
insert into t_vip(id) values(3); ERROR 1364 (HY000): Field 'name' doesn't have a default value

7.3 唯一性约束

  1. 案例
create table t_vip( id int, name varchar(255) unique, email varchar(255) );
  1. 约束失效
insert into t_vip(id,name,email) values(3,'wangwu','wangwu@123.com'); insert into t_vip(id,name,email) values(4,'wangwu','wangwu@sina.com'); ERROR 1062 (23000): Duplicate entry 'wangwu' for key 'name'
  1. 两个字段联合唯一约束
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'); 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'
  1. 同个字段唯一和非空约束自动转换主键
create table t_vip( id int, name varchar(255) not null unique ); 转换成主键约束

mysql> desc t_vip; +-------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(255) | NO | PRI | NULL | | +-------+--------------+------+-----+---------+-------+ insert into t_vip(id,name) values(1,'zhangsan'); insert into t_vip(id,name) values(2,'zhangsan'); //错误了：name不能重复 insert into t_vip(id) values(2); //错误了：name不能为NULL。

7.4 主键约束PK

  1. 案例
create table t_vip( id int primary key, //列级约束 name varchar(255) ); create table t_vip( id int, name varchar(255), primary key(id) // 表级约束 );
  1. 约束失效
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'); ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' //错误：不能为NULL insert into t_vip(name) values('zhaoliu'); ERROR 1364 (HY000): Field 'id' doesn't have a default value
  1. 复合主键(一般不用)
create table t_vip( id int, name varchar(255), email varchar(255), primary key(id,name) );
  1. 主键类别
自然主键：主键值是一个自然数，和业务没关系。用的多 业务主键：主键值和业务紧密关联，例如拿银行卡账号做主键值。这就是业务主键！
  1. 自增维护主键值
create table t_vip( id int primary key auto_increment, //auto_increment表示自增，从1开始，以1递增！ name varchar(255) );

7.5 外键约束

  1. 案例
t_class是父表 t_student是子表 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
  1. 其他注意点
思考：子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？ 不一定是主键，但至少具有unique约束。 思考：外键可以为NULL吗？ 外键值可以为NULL。
  1. 语法
[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

8 存储引擎

8.1 含义

​ 实际上存储引擎是一个表存储/组织数据的方式。 ​ 不同的存储引擎，表存储数据的方式不同

8.2 指定引擎

在建表的时候可以在最后小括号的")"的右边使用： ENGINE来指定存储引擎。 CHARSET来指定这张表的字符编码方式。 CREATE TABLE `t_student` ( `no` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(255) DEFAULT NULL, `cno` int(11) DEFAULT NULL, PRIMARY KEY (`no`), KEY `cno` (`cno`), CONSTRAINT `t_student_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `t_class` (`classno`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8 create table t_product( id int primary key, name varchar(255) )engine=InnoDB default charset=gbk;

8.3 查看引擎

show engines \G

8.4 引擎类型

  1. MyISAM存储引擎

    使用三个文件表示每个表： 格式文件 — 存储表结构的定义（mytable.frm） 数据文件 — 存储表行的内容（mytable.MYD） 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制 MyISAM存储引擎优势：可被转换为压缩、只读表来节省空间 MyISAM存储的缺点：不支持事务机制，安全性低
  • 案例：此表对应了三个文件，可以通过show create table columns_priv;查看建表的时候采用的Myisam
mysql> show create table columns_priv; columns_priv | CREATE TABLE `columns_priv` ( `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '', `Db` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '', `Table_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `Column_name` char(64) COLLATE utf8_bin NOT NULL DEFAULT '', `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `Column_priv` set('Select','Insert','Update','References') CHARACTER SET utf8 NOT NULL DEFAULT '', PRIMARY KEY (`Host`,`Db`,`User`,`Table_name`,`Column_name`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Column privileges' |
  1. InnoDB存储引擎
它管理的表具有下列主要特征： – 每个 InnoDB 表在数据库目录中以.frm 格式文件表示 – InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。） InnoDB最大的特点就是支持事务： 以保证数据的安全。支持数据库崩溃后自动恢复机制。 InnoDB的缺点： 效率不是很高，并且也不能压缩，不能转换为只读， 不能很好的节省存储空间。
  1. MEMORY存储引擎
MEMORY 存储引擎管理的表具有下列特征： – 在数据库目录内，每个表均以.frm 格式的文件表示。 – 表数据及索引被存储在内存中。（目的就是快，查询快！） – 表级锁机制。 – 不能包含 TEXT 或 BLOB 字段。 MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。 MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中。

9 事务

9.1 事务概述

  1. 什么是事务？
​ 一个事务其实就是一个完整的业务逻辑。是一个最小的工作单元。不可再分。

  1. 事务的范围
​ insert ​ delete ​ update ​ 只有以上的三个语句和事务有关系，其它都没有关系。

  1. 事务实现原理
​ 事务是怎么做到多条DML语句同时成功和同时失败的呢？

InnoDB存储引擎：提供一组用来记录事务性活动的日志文件 事务开启了： insert insert insert delete update update update 事务结束了！ 在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中。 在事务的执行过程中，我们可以提交事务，也可以回滚事务。 提交事务？ 清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中。 提交事务标志着，事务的结束。并且是一种全部成功的结束。 回滚事务？ 将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件 回滚事务标志着，事务的结束。并且是一种全部失败的结束。

9.2 提交事务、回滚事务

  1. 提交事务、回滚事务
提交事务：commit; 回滚事务：rollback;
  1. 关闭mysql的自动提交机制关闭
先执行这个命令：start transaction;
  1. 提交、回滚事务完整例子
  • 回滚事务
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)

9.3 事务的4个特性

1. A：原子性 说明事务是最小的工作单元。不可再分。 2. C：一致性 所有事务要求，在同一个事务当中，所有操作必须同时成功，或者同时失败， 以保证数据的一致性。 3. I：隔离性 A事务和B事务之间具有一定的隔离。 4. D：持久性 事务最终结束的一个保障。事务提交，就相当于将没有保存到硬盘上的数据保存到硬盘上！

9.4 事务的隔离性

  1. 读未提交：read uncommitted
没有提交就读到了。 事务A可以读取到事务B未提交的数据。是最低的隔离级别。 问题： 脏读现象！(Dirty Read)
  1. 读已提交：read committed
提交之后才能读到。 事务A只能读取到事务B提交之后的数据。 优点： 解决了脏读 问题： 不可重复读取数据。 什么是不可重复读取数据呢？ 在事务开启之后，第一次读到的数据是3条，当前事务还没有结束，可能第二次再读取的时候，读到的数据是4条，3不等于4称为不可重复读取。就是没提交一次改变一次。
  1. 可重复读：repeatable read
提交之后也读不到，永远读取的都是刚开启事务时的数据。 事务A开启之后，不管是多久，每一次在事务A中读取到的数据都是一致的.即使事务B将数据已经修改，并且提交了，事务A 读取到的数据还是没有发生改变，这就是可重复读。 优点： 解决了不可重复读取数据。 问题： 可以会出现幻影读。每一次读取到的数据都是幻象。只有两个事务都commit结束后才能查询到 例子：银行执行事务，从13点到15点，期间有人存取，最终执行结果也要仍然是13点的数据，这个时候隔离级别设置为可重复读即可
  1. 序列化/串行化：serializable
这种隔离级别表示事务排队，不能并发！ 优点： 每一次读取到的数据都是最真实的。 问题： 效率最低

9.5 设置隔离级别

  1. 查看隔离级别
mysql> SELECT @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
  1. 设置隔离级别
mysql> set global transaction isolation level REPEATABLE READ;

10 索引

10.1 索引概述

  1. Mysql查询方式
MySQL在查询方面主要就是两种方式： 第一种方式：全表扫描 第二种方式：根据索引检索
  1. 索引原理
在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。 在mysql数据库当中索引也是需要排序的，并且这个所以的排序和TreeSet 数据结构相同。TreeSet（TreeMap）底层是一个自平衡的二叉树！在mysql 当中索引是一个B-Tree数据结构
  1. 索引对象
  • 主键和unique约束都会自动创建索引对象
  • 存在以下条件
    • 条件1：数据量庞大（到底有多么庞大算庞大，这个需要测试，因为每一个硬件环境不同）
    • 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说这个字段总是被扫描。
    • 条件3：该字段很少的DML(insert delete update)操作。（因为DML之后，索引需要重新排序。）

10.2 索引的创建与删除

  1. 创建索引
create index 索引名字 on 表(字段名) create index emp_ename_index on emp(ename);
  1. 删除索引
drop index 索引名字 on 表； drop index emp_ename_index on emp;
  1. 查看SQL语句是否使用索引进行检索
  • 发现查询行数为1，因此是使用了索引
mysql> explain select * from emp where ename = 'king'; +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_ename_index | emp_ename_index | 33 | const | 1 | Using where | +----+-------------+-------+------+-----------------+-----------------+---------+-------+------+-------------+
  • 未使用索引
mysql> explain select * from emp where ename = 'king'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+

10.3 索引失效

  1. 模糊查询
select * from emp where ename like '%T'; mysql> explain select * from emp where ename like '%T'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) 即使ename添加了索引，也是因为模糊查询中以%开头而不走索引

  1. or 查询
mysql> explain select * from emp where ename = 'KING' or job = 'MANAGER'; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 使用or的时候会失效，如果使用or那么要求or两边的条件字段都要有索引，才会走索引

  1. 复合索引
mysql> create index emp_job_sal_index on emp(job,sal); mysql> explain select * from emp where job = 'MANAGER'; +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_job_sal_index | emp_job_sal_index | 30 | const | 3 | Using where | +----+-------------+-------+------+-------------------+-------------------+---------+-------+------+-------------+ mysql> explain select * from emp where sal = 800; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 使用复合索引的时候，没有使用左侧的列查找，索引失效

  1. 在where当中索引列参加了运算以及函数，索引失效。
mysql> create index emp_sal_index on emp(sal); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from emp where sal = 800; +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ | 1 | SIMPLE | emp | ref | emp_sal_index | emp_sal_index | 9 | const | 1 | Using where | +----+-------------+-------+------+---------------+---------------+---------+-------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from emp where sal+1 = 800; +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | emp | ALL | NULL | NULL | NULL | NULL | 14 | Using where | +----+-------------+-------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)

10.4 索引分类

  1. 单一索引：一个字段上添加索引。
  2. 复合索引：两个字段或者更多的字段上添加索引。

11 视图

11.1 视图概述

  1. 含义：站在不同的角度去看待同一份数据

11.2 视图的创建删除

  1. 创建
mysql> create table dept2 as select * from dept; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec) mysql> create view dept2_view as select * from dept2; Query OK, 0 rows affected (0.01 sec) mysql> select * from dept2_view; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 4 rows in set (0.00 sec)
  1. 删除
mysql> drop view dept2_view; Query OK, 0 rows affected (0.00 sec)

11.3 视图的增删改查

  1. 视图的查询
mysql> select * from dept2_view; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+
  1. 视图的插入
mysql> insert into dept2_view(deptno,dname,loc) values (60,'SALES','BEIJING'); Query OK, 1 row affected (0.00 sec) 原表也一起插入了

mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | SALES | CHICAGO | | 40 | OPERATIONS | BOSTON | | 60 | SALES | BEIJING | +--------+------------+----------+ 5 rows in set (0.00 sec)
  1. 视图的删除
mysql> delete from dept2_view; Query OK, 5 rows affected (0.00 sec) 原表也删除了

mysql> select * from dept2; Empty set (0.00 sec)
  1. 视图的更新
mysql> update dept2_view set dname = 'BOSS' where deptno = 30; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 ​ 原表也被更新

mysql> select * from dept2; +--------+------------+----------+ | DEPTNO | DNAME | LOC | +--------+------------+----------+ | 10 | ACCOUNTING | NEW YORK | | 20 | RESEARCH | DALLAS | | 30 | BOSS | CHICAGO | | 40 | OPERATIONS | BOSTON | +--------+------------+----------+ 很有意思

11.4 开发过程中的运用

  1. 场景：
  • 假设有一条非常复杂的SQL语句，而这条SQL语句需要在不同的位置上反复使用。

  • 解决办法：

​ 可以把这条复杂的SQL语句以视图对象的形式新建。后续修改也可只更改视图并且映射到sql的表中

  1. 其他小注意
  • 视图对应的语句只能是DQL语句
  • 但是视图对象创建完成之后，可以对视图进行增删改查等操作。

12 数据的导入导出

12.1 数据导出

注意：在windows的dos命令窗口中：

  1. 导出数据库
mysqldump bjpowernode>D:\bjpowernode.sql -uroot -pxxxxxx
  1. 导出表
mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123456

12.2 数据导入

注意：需要先登录到mysql数据库服务器上。 然后创建数据库：create database bjpowernode; 使用数据库：use bjpowernode 然后初始化数据库：source D:\bjpowernode.sql

13 数据库设计三范式

13.1 数据库范式概述

  1. 什么是数据库设计范式
  • 数据库表的设计依据。教你怎么进行数据库表的设计。
  1. 有哪几个范式呀
  • 第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分。
  • 第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，不要产生部分依赖。
  • 第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，不要产生传递依赖。
  1. 范式作用
  • 可以避免表中数据的冗余，空间的浪费。

13.2 第一范式

  1. 内容：
  • 最核心，最重要的范式，所有表的设计都需要满足
  • 必须有主键，并且每一个字段都是原子性不可再分。
  1. 案例：
  • 场景：下表是学生表，满足第一范式吗
学生编号 学生姓名 联系方式 ------------------------------------------ 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

13.3 第二范式

  1. 内容
  • 建立在第一范式的基础之上
  • 要求所有非主键字段必须完全依赖主键，不要产生部分依赖。
  1. 案例
  • 场景：下表是学生教师表，满足第一或者二范式吗
学生编号 学生姓名 教师编号 教师姓名 ---------------------------------------------------------------- 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

13.4 第三范式

  1. 内容
  • 第三范式建立在第二范式的基础之上
  • 要求所有非主键字典必须直接依赖主键，不要产生传递依赖。
  1. 案例
  • 场景：下表是学生教师表，满足第三范式吗
学生编号（PK） 学生姓名 班级编号 班级名称 --------------------------------------------------------- 1001 张三 01 一年一班 1002 李四 02 一年二班 1003 王五 03 一年三班 1004 赵六 03 一年三班
  • 解答：

    • 满足第一和第二范式，只有一个主键，无复合主键因此没有部分依赖
    • 不满足第三范式，一年一班依赖01，01依赖1001，产生了传递依赖
  • 正确设计:一对多，两张表，多的表加外键！

班级表：一 班级编号(pk) 班级名称 -------------------------------------------------- 01 一年一班 02 一年二班 03 一年三班 学生编号（PK） 学生姓名 班级编号(fk) -------------------------------------------------------------- 1001 张三 01 1002 李四 02 1003 王五 03 1004 赵六 03
MySQL02codelipresql主键数据库设计三范式MySQL数据库

有关朝花夕拾之MySQL-02的更多相关文章

  1. 使用canal同步MySQL数据到ES - 2

    文章目录一、概述简介原理模块二、配置Mysql使用版本环境要求1.操作系统2.mysql要求三、配置canal-server离线下载在线下载上传解压修改配置单机配置集群配置分库分表配置1.修改全局配置2.实例配置垂直分库水平分库3.修改group-instance.xml4.启动监听四、配置canal-adapter1修改启动配置2配置映射文件3启动ES数据同步查询所有订阅同步数据同步开关启动4.验证五、配置canal-admin一、概述简介canal是Alibaba旗下的一款开源项目，Java开发。基于数据库增量日志解析，提供增量数据订阅&消费。Git地址：https://github.co

  2. postman——集合——执行集合——测试脚本——pm对象简单示例02 - 2

    //1.验证返回状态码是否是200pm.test("Statuscodeis200",function(){pm.response.to.have.status(200);});//2.验证返回body内是否含有某个值pm.test("Bodymatchesstring",function(){pm.expect(pm.response.text()).to.include("string_you_want_to_search");});//3.验证某个返回值是否是100pm.test("Yourtestname",function(){varjsonData=pm.response.json

  3. 牛客网专项练习30天Pytnon篇第02天 - 2

    1.在Python3中，下列关于数学运算结果正确的是:（B）a=10b=3print(a//b)print(a%b)print(a/b)A.3，3，3.3333...B.3，1，3.3333...C.3.3333...，3.3333...，3D.3.3333...，1，3.3333...解析：    在Python中，//表示地板除（向下取整），%表示取余，/表示除（Python2向下取整返回3）2.如下程序Python2会打印多少个数:（D）k=1000whilek>1:    print(k)k=k/2A.1000 B.10C.11D.9解析：    按照题意每次循环K/2，直到K值小于等

  4. ruby-on-rails - 无法安装 mysql2 0.3.14 gem - 2

    我看到其他人也遇到过类似的问题，但没有一个解决方案对我有用。0.3.14gem与其他gem文件一起存在。我已经完全按照此处指示完成了所有操作:https://github.com/brianmario/mysql2.我仍然得到以下信息。我不知道为什么安装程序指示它找不到include目录，因为我已经检查过它存在。thread.h文件存在，但不在ruby​​目录中。相反，它在这里:C:\RailsInstaller\DevKit\lib\perl5\5.8\msys\CORE\我正在运行Windows7并尝试在Aptana3中构建我的Rails项目。我的Ruby是1.9.3。$gemin

  5. ruby - 如何使用 ruby​​ mysql2 执行事务 - 2

    我已经开始使用mysql2gem。我试图弄清楚一些基本的事情——其中之一是如何明确地执行事务(对于批处理操作，比如多个INSERT/UPDATE查询)。在旧的ruby-mysql中，这是我的方法:client=Mysql.real_connect(...)inserts=["INSERTINTO...","UPDATE..WHEREid=..",#etc]client.autocommit(false)inserts.eachdo|ins|beginclient.query(ins)rescue#handleerrorsorabortentirelyendendclient.commi

  6. ruby-on-rails - 当我通过 rvm 使用 rails3 时，如何在 ubuntu 上安装 mysql2 gem？ - 2

    我正在尝试绕过rails配置这个极其复杂的迷宫。到目前为止，我设法在ubuntu上设置了rvm(出于某种原因，ruby在ubuntu存储库中已经过时了)。我设法建立了一个Rails项目。我希望我的测试项目使用mysql而不是mysqlite。当我尝试“rakedb:migrate”时，出现错误:“!!!缺少mysql2gem。将其添加到您的Gemfile:gem'mysql2'”当我尝试“geminstallmysql”时，出现错误，告诉我需要为安装命令提供参数。但是，参数列表很大，我不知道该选择哪些。如何通过在ubuntu上运行的rvm和mysql获取rails3？谢谢。

  7. Centos7-yum安装mysql-修改密码-无密码登录-安全配置 - 2

    目录1、yum安装mysql修改密码(1)在mysql里面修改(2)第二种方式，利用mysqladmin修改密码2、没有密码，登录mysql修改密码3、mysql的安全设置1、yum安装mysql在CentOS中默认安装有MariaDB（MySQL的一个分支），安装完成之后可以直接覆盖MariaDB。rpm-qa|grepmariadb查询是否安装了mariadbrpm-e--nodepsmariadb-libs-5.5.60-1.el7_5.x86_64卸载mariadwgethttp://dev.mysql.com/get/mysql57-community-release-el7-11.

  8. ruby - 安装 dm-mysql-adapter 时出错 - 2

    我是Ruby的新手。我安装了DataMapper并且正在尝试安装dm-mysql-adapter-1.0.2gem。但是当我尝试安装时，出现以下错误。我正在使用ubuntu操作系统。vinoth@vinoth-laptop:~/Downloads$geminstalldm-mysql-adapter-1.0.2----with-mysql-lib=/usr/lib/mysql----with-mysql-conf=/usr/bin/mysqlWARNING:Installingto~/.gemsince/home/vinoth/gemsand/home/vinoth/gems/bina

  9. ruby-on-rails - gem 列表中的 mysql2 gem 但获取项目找不到 gem - 2

    我目前正在构建一个需要mysql2gem的RoR项目。我成功安装了gem。因为它出现在我的gem列表中。[root@vc2cmmka035538nsimple_cms]#gemlist***LOCALGEMS***actionmailer(3.2.3)actionpack(3.2.3)activemodel(3.2.3)activerecord(3.2.3)activeresource(3.2.3)activesupport(3.2.14,3.2.3)arel(3.0.2)bigdecimal(1.1.0)builder(3.2.2,3.0.0)bundler(1.1.5)c2c_li

  10. ruby - 如何在 heroku 中使用自己的 mysql 数据库服务器？ - 2

    我想使用托管在我自己服务器上的mysql数据库。我已经更改了DATABASE_URL和SHARED_DATABASE_URL配置变量以指向我的服务器，但它仍在尝试连接到heroku的amazonaws服务器。我该如何解决？ 最佳答案 根据Herokudocumentation，更改DATABASE_URL是正确的方法。Ifyouwouldliketohaveyourrailsapplicationconnecttoanon-Herokuprovideddatabase,youcantakeadvantageofthissamemec

随机推荐