数据库（三）

创建表

create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型，...);见名知意

关于mysql当中的数据类型

int bigint float char定长字符串  varchar 不定长字符串 date日期类型 BLOB二进制大对象（图片，视频 binary large object） CLOB字符大对象（较大文本 character large object）

char 和varchar 的选择 当某个字段中数据长度不发生改变时，如性别、生日 采用char

当一个字段的数据长度不确定，如简介、姓名 采用varchar

BLOB 和CLOB的使用

表名在数据库中一般建议以 t_或者tbl_开始

创建学生表

信息：学号bigint、姓名varchar、性别char、班级编号varchar、生日date

create table t_student( no bigint, name varchar(225), sex char(1), classno varchar(225), birth char(10));

insert语句插入数据

语法格式:insert into 表名(字段名1， 字段名2， 字段名3...) value(值1， 值2， 值3， ...)

要求：字段的数量和值的数量相同，数据类型要对应相同             

insert into t_student(no, name, sex, classno, birth) values (1, 'zhangsan', '1', 'gaosanban' , '1950-10-12');      

insert into t_student( name, sex, classno, birth, no) values ( 'lisi', '1', 'gaosanban' , '1950-10-12', 2);      

insert into t_student(name) values (‘wangwu');//除name字段外，剩下的所有字段自动插入null

drop table if exists t_student;//表存在则删除

create table t_student( no bigint, name varchar(225), sex char(1) default 1, classno varchar(225), birth char(10));//给sex设定默认值

 当一条insert语句执行成功后，表格中必然会多一行记录，即使多的这行记录的某些字段是null，只能使用update进行更新

insert into t_student values(1, 'jack', '0', 'gaosan2ban', '1986-10-23');//字段可以省略不写，但是格式必须正确

一次插入多行数据

insert into t_student (no, name, sex, classno, birth) values (3, 'rose', '1', 'gaosi', '1952-12-14',), (4, 'laote', '1', 'gaosan', '1955-12-14');

表的复制

create table 表名 as select语句 将查询结果当作表创建

create table emp1 as select * from emp;

create table emp1 as select  ename, sal from emp;

将查询结果插入到一张表

insert into dept1 select * from dept;

修改数据

语法格式：update 表名 set 字段名1=值1， 字段名2=值2...where 条件；没有条件整张表数据全部更新

将部门10的loc改为shanghai，将部门名称改为renshibu

update dept set loc='shanghai', dname='renshibu' where deptno = 10;

更新所有记录

update dept set loc = 'x', dname = 'y';

删除数据

delete from 表名 where 条件；没有条件全部删除

删除部门10数据

delete from dept1 where deptno = 10;

删除所有记录

delete from dept1;

怎么删除大表

truncate table emp1;//表被截断，不可回滚，永久丢失

实际开发中对表结构的修改很少，java代码中的sql包括 insert delete update select crud操作

create retrieve update delete

删除表 drop table 表名；//通用

drop table if exists 表名;//oracle不支持

约束constraint

约束在创建表的时候，可以给表的字段添加相应的约束，目的是保证表中数据的合法性 有效性 完整性

非空约束not null 唯一约束unique 主键约束primary key PK，约束字段既不能为null 也不能重复 外键约束foreign key FK 检查约束check oracle数据库有check 约束 mysql没有

非空约束 not null

drop table if exists t_user;

create table t_user( id int, username varchar(255) not null, password varchar(255));

insert into t_user(id, password) values(1, '123');//错误 username 为空

insert into t_user(id, username, password) values(1,'lisi', '123');

唯一性约束unique

字段具有唯一性，不能重复，但可以为null

drop table if exists t_user;

create table t_user( id int, username varchar(225) unique);

insert into t_user values(1, 'lihua');

insert into t_user values(2, 'lihua');//报错

insert into t_user(id) values(3);//没问题

给两个列或者多个列添加 unique

drop table if exists t_user;

create table t_user( id int, usercode varchar(255), username varchar(225), unique(usercode, username));//两列联合唯一 表级约束

insert into t_user values(1, '111', 'zs');

insert into t_user values(2, '111', 'ls');

insert into t_user values(3, '222', 'zs');

drop table if exists t_user;

create table t_user( id int, usercode varchar(255) unique, username varchar(225) unique);

not null 只有列级约束

主键约束

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');

insert into t_user(id, username, email) values(2, 'ls', 'ls@123.com');

insert into t_user(id, username, email) values(3, 'ww', 'ww@123.com');

select * from t_user;

id是主键，主键字段中的数据不能为null 也不能重复 是列级约束

主键相关的术语 主键约束primary key 主键字段id  主键值id 的值

表的设计三范式，第一范式要求任何一张表都应该有主键

主键值是这行记录在这张表中的唯一标识

主键分类

主键字段数量划分：单一主键 复合主键（多个字段联合起来，不建议使用）

主键性质：自然主键（最好是和业务没关系的自然数） 业务主键（主键值和系统业务挂钩，银行卡号，不推荐用）

最好不要拿着和业务挂钩的字段作为主键，业务发生改变，主键也要改变

一张表的主键约束只能由一个

复合主键 primary key(id, username)

mysql提供主键值自增 

id int primary key auto_increment//id自动维护一个的数字，从1开始，以1递增

oracle也提供自增 序列(sequence)

外键约束

外键约束 foreign key 外键字段 外键值

t_student中的classno字段引用t_class表的cno，此时t_student表叫做子表，t_class表叫做父表。删除数据，先删子表，再删父表，添加数据，先加父表，再加子表，建表，先建父表，再建子表。

foreign key(classno) references t_class(cno)

外键可以为null, 外键字段引用其他表的某个字段的时候，被引用字段可以不是主键但必须有唯一性。

存储引擎

 表的存储方式

mysql默认存储引擎InnoDB 默认字符集utf-8

mysql 叫存储引擎 oracle没有特殊名字

查看当前mysql支持的存储引擎 show engines \g

myisam不支持事务，最常用不是默认，三个文件存储数据 格式 数据 索引，可被压缩可转换为只读表节省空间

innoDB 支持事务 行级锁 外键等，mysql默认 最安全，表结构存储在 xxx.frm文件中，数据存储在tablespace这样的表空间中，无法压缩，不能转换为只读，在mysql数据库崩溃后提供数据恢复。支持级联删除和级联更新

memory 不支持事务，数据容易丢失，数据和索引存储在内存中，查询速度最快 以前叫hepa引擎

取得每个部门最高薪水的人员名称

1.取得每个部门最高薪水 select deptno, max(sal) maxsal from emp group by deptno;

2.把上表当作临时表t , 和emp e 连接，条件是 t.deptno = e.deptno and t.maxsal=e.sal

select e.ename, e.sal, e.deptno from ( select deptno, max(sal) maxsal from emp group by deptno) t join emp e on t.deptno = e.deptno and t.maxsal=e.sal;

事务transaction

一个事务是一个完整的逻辑单元，不可再分。要么同时成功，要么同时失败

DML语句和事务相关，事务是为了保证数据的完整性，安全性。原子操作

一件事通常需要多条dml语句完成

事务机制：一个操作完成后，不会向文件保存数据，直到提交或者回滚事务

commit rollback

原子性 一致性 保证多条dml语句同时成功或失败 隔离性 事务A与事务B之间隔离 持久性 最终数据必须持久化到硬盘文件中，事务才结束 ACID

隔离性 事务隔离性存在隔离级别

第一级别：读未提交(read uncommitted) 对方事务没有提交，当前事务可以读取到对方未提交的数据，脏读现象， 读到了脏的数据

第二级别：读已提交（read committed） 不可重复读 解决脏读现象

第三级别：可重复读（repeatable read)解决不可重复问题 读取到的数据是幻象

第四级别：串行化读/序列化读 效率低，需要事务排队

oracle 读已提交 mysql 可重复度

mysql事务默认情况下是自动提交的，执行任意一条dml语句则提交一次。

start transaction 关闭自动提交 

索引

相当于目录，通过目录可以快速地找到相应的资源，查询一张表有两种索引方式

1.全表扫描 2 根据索引检索（效率高）缩小了扫描的范围，不能随意地添加索引，索引是数据库中的对象，维护需要成本，表中数据经常被修改，不适合添加索引

数据量庞大，该字段很少dml操作，该字段经常出现在where 子句中 适合添加索引

主键和具有unique约束的字段自动会添加索引 根据主键查询效率高

查看sql语句的执行计划 explain select, sal from emp where sal=5000;

给sal添加索引 create index emp_sal_index on emp(sal);

创建索引 create index 索引名称 on 表名（字段名）；

删除索引对象 drop index 索引名称 on 表名；

索引采用的数据结构是 B+tree

索引实现原理 缩小扫描范围，索引携带数据在表中的物理地址

索引分类 单一索引 复合索引 主键索引 主键自动添加索引 唯一索引 唯一约束自动添加索引

like模糊查询第一个字符是通配符% 索引失效

视图 view

不同角度看待数据

create view myview as select empno, ename from emp;

drop myview;

只有dql语句才能以视图方式创建

对视图进行增删改查，会影响原表数据，通过视图影响原表数据

视图可以隐藏表的实现细节

DBA命令

将数据库数据导出 mysqldump bjpowernode>D:\bjpowernode.sql -uroot -p123 导出整个库

mysqldump bjpowernode emp>D:\bjpowernode.sql -uroot -p123 导出指定数据库中的指定表

在windows的dos命令窗口执行

导入

create database bjpowernode;

use bjpowernode;

source D:\bjpowernode.sql

数据库设计三范式

设计表的依据，按照三范式设计的表不会出现冗余

第一范式 每一张表都应该有主键，每一个字段原子性不可再分

第二范式 第一范式基础上，所有非主键完全依赖主键，不能产生部分依赖 多对多 三张表 关系表两外键

第三范式 第二范式基础上 所有非主键字段直接依赖主键，不能产生传递依赖

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

实际开发中 以满足客户的需求为主，有时候以冗余换速度

一对一设计 两种方案 主键共享 外键唯一